diff options
| -rwxr-xr-x | scripts/createdb.mysql | 10 | ||||
| -rwxr-xr-x | scripts/createtables.mysql | 235 | ||||
| -rwxr-xr-x | scripts/gddb.pm | 344 | ||||
| -rwxr-xr-x | scripts/gdgentools.pm | 1606 | ||||
| -rwxr-xr-x | scripts/gdimport.pl | 254 | ||||
| -rwxr-xr-x | scripts/gdio.pm | 349 | ||||
| -rwxr-xr-x | scripts/gdparams.pm | 336 | ||||
| -rwxr-xr-x | scripts/gdserv.pm | 3305 | ||||
| -rwxr-xr-x | scripts/gdsoundcard.pm | 149 | ||||
| -rwxr-xr-x | scripts/gdupdate.pm | 509 | ||||
| -rwxr-xr-x | scripts/genres.txt | 224 | ||||
| -rwxr-xr-x | scripts/languages.txt | 45 | ||||
| -rwxr-xr-x | scripts/make-db | 8 | ||||
| -rwxr-xr-x | scripts/make-empty-db | 24 | ||||
| -rwxr-xr-x | scripts/make-tables | 26 | ||||
| -rwxr-xr-x | scripts/musictypes.txt | 4 | ||||
| -rwxr-xr-x | scripts/myhash.pm | 29 | ||||
| -rwxr-xr-x | scripts/sources.txt | 6 |
18 files changed, 7463 insertions, 0 deletions
diff --git a/scripts/createdb.mysql b/scripts/createdb.mysql new file mode 100755 index 0000000..8b9ea42 --- /dev/null +++ b/scripts/createdb.mysql @@ -0,0 +1,10 @@ +/* Creates DB and opens it to any user */ +/* Run this mysql macro as root! */ + +DROP DATABASE IF EXISTS GiantDisc; +CREATE DATABASE GiantDisc; +use GiantDisc; +grant all privileges on GiantDisc.* to music@'%'; +grant all privileges on GiantDisc.* to music@localhost; +grant all privileges on GiantDisc.* to apache@localhost with grant option; + diff --git a/scripts/createtables.mysql b/scripts/createtables.mysql new file mode 100755 index 0000000..5969ce2 --- /dev/null +++ b/scripts/createtables.mysql @@ -0,0 +1,235 @@ +-- MySQL dump 8.21 +-- +-- Host: localhost Database: GiantDisc +--------------------------------------------------------- +-- Server version 3.23.49 + +-- +-- Current Database: GiantDisc +-- + +--CREATE DATABASE /*!32312 IF NOT EXISTS*/ GiantDisc; + +USE GiantDisc; + +-- +-- Table structure for table 'album' +-- + +drop table if exists album; +CREATE TABLE album ( + artist varchar(255) default NULL, + title varchar(255) default NULL, + cddbid varchar(20) NOT NULL default '', + coverimg varchar(255) default NULL, + covertxt mediumtext, + modified date default NULL, + genre varchar(10) default NULL, + PRIMARY KEY (cddbid), + KEY artist (artist(10)), + KEY title (title(10)), + KEY genre (genre), + KEY modified (modified) +) TYPE=MyISAM; + +-- +-- Table structure for table 'genre' +-- + +drop table if exists genre; +CREATE TABLE genre ( + id varchar(10) NOT NULL default '', + id3genre smallint(6) default NULL, + genre varchar(255) default NULL, + freq int(11) default NULL, + PRIMARY KEY (id) +) TYPE=MyISAM; + +-- +-- Table structure for table 'language' +-- + +drop table if exists language; +CREATE TABLE language ( + id varchar(4) NOT NULL default '', + language varchar(40) default NULL, + freq int(11) default NULL, + PRIMARY KEY (id) +) TYPE=MyISAM; + +-- +-- Table structure for table 'musictype' +-- + +drop table if exists musictype; +CREATE TABLE musictype ( + musictype varchar(40) default NULL, + id tinyint(3) unsigned NOT NULL auto_increment, + PRIMARY KEY (id) +) TYPE=MyISAM; + +-- +-- Table structure for table 'player' +-- + +drop table if exists player; +CREATE TABLE player ( + ipaddr varchar(255) NOT NULL default '', + uichannel varchar(255) NOT NULL default '', + logtarget int(11) default NULL, + cdripper varchar(255) default NULL, + mp3encoder varchar(255) default NULL, + cdromdev varchar(255) default NULL, + cdrwdev varchar(255) default NULL, + id int(11) NOT NULL default '0', + PRIMARY KEY (id) +) TYPE=MyISAM; + +-- +-- Table structure for table 'playerstate' +-- + +drop table if exists playerstate; +CREATE TABLE playerstate ( + playerid int(11) NOT NULL default '0', + playertype int(11) NOT NULL default '0', + snddevice varchar(255) default NULL, + playerapp varchar(255) default NULL, + playerparams varchar(255) default NULL, + ptlogger varchar(255) default NULL, + currtracknb int(11) default NULL, + state varchar(4) default NULL, + shufflepar varchar(255) default NULL, + shufflestat varchar(255) default NULL, + pauseframe int(11) default NULL, + framesplayed int(11) default NULL, + framestotal int(11) default NULL, + anchortime bigint(20) default NULL, + PRIMARY KEY (playerid,playertype) +) TYPE=HEAP; + +-- +-- Table structure for table 'playlist' +-- + +drop table if exists playlist; +CREATE TABLE playlist ( + title varchar(255) default NULL, + author varchar(255) default NULL, + note varchar(255) default NULL, + created timestamp(8) NOT NULL, + id int(10) unsigned NOT NULL auto_increment, + PRIMARY KEY (id) +) TYPE=MyISAM; + +-- +-- Table structure for table 'playlistitem' +-- + +drop table if exists playlistitem; +CREATE TABLE playlistitem ( + playlist int(11) NOT NULL default '0', + tracknumber mediumint(9) NOT NULL default '0', + trackid int(11) default NULL, + PRIMARY KEY (playlist,tracknumber) +) TYPE=MyISAM; + +-- +-- Table structure for table 'playlog' +-- + +drop table if exists playlog; +CREATE TABLE playlog ( + trackid int(11) default NULL, + played date default NULL, + id tinyint(3) unsigned NOT NULL auto_increment, + PRIMARY KEY (id) +) TYPE=MyISAM; + +-- +-- Table structure for table 'recordingitem' +-- + +drop table if exists recordingitem; +CREATE TABLE recordingitem ( + trackid int(11) default NULL, + recdate date default NULL, + rectime time default NULL, + reclength int(11) default NULL, + enddate date default NULL, + endtime time default NULL, + repeat varchar(10) default NULL, + initcmd varchar(255) default NULL, + parameters varchar(255) default NULL, + atqjob int(11) default NULL, + id int(11) NOT NULL default '0', + PRIMARY KEY (id) +) TYPE=MyISAM; + +-- +-- Table structure for table 'source' +-- + +drop table if exists source; +CREATE TABLE source ( + source varchar(40) default NULL, + id tinyint(3) unsigned NOT NULL auto_increment, + PRIMARY KEY (id) +) TYPE=MyISAM; + +-- +-- Table structure for table 'tracklistitem' +-- + +drop table if exists tracklistitem; +CREATE TABLE tracklistitem ( + playerid int(11) NOT NULL default '0', + listtype smallint(6) NOT NULL default '0', + tracknb int(11) NOT NULL default '0', + trackid int(11) NOT NULL default '0', + PRIMARY KEY (playerid,listtype,tracknb) +) TYPE=MyISAM; + +-- +-- Table structure for table 'tracks' +-- + +drop table if exists tracks; +CREATE TABLE tracks ( + artist varchar(255) default NULL, + title varchar(255) default NULL, + genre1 varchar(10) default NULL, + genre2 varchar(10) default NULL, + year smallint(5) unsigned default NULL, + lang varchar(4) default NULL, + type tinyint(3) unsigned default NULL, + rating tinyint(3) unsigned default NULL, + length smallint(5) unsigned default NULL, + source tinyint(3) unsigned default NULL, + sourceid varchar(20) default NULL, + tracknb tinyint(3) unsigned default NULL, + mp3file varchar(255) default NULL, + condition tinyint(3) unsigned default NULL, + voladjust smallint(6) default '0', + lengthfrm mediumint(9) default '0', + startfrm mediumint(9) default '0', + bpm smallint(6) default '0', + lyrics mediumtext, + bitrate varchar(10) default NULL, + created date default NULL, + modified date default NULL, + backup tinyint(3) unsigned default NULL, + id int(11) NOT NULL auto_increment, + PRIMARY KEY (id), + KEY title (title(10)), + KEY mp3file (mp3file(10)), + KEY genre1 (genre1), + KEY genre2 (genre2), + KEY year (year), + KEY lang (lang), + KEY type (type), + KEY rating (rating), + KEY sourceid (sourceid), + KEY artist (artist(10)) +) TYPE=MyISAM; + diff --git a/scripts/gddb.pm b/scripts/gddb.pm new file mode 100755 index 0000000..88f5bea --- /dev/null +++ b/scripts/gddb.pm @@ -0,0 +1,344 @@ +################################################## +# +# GiantDisc mp3 Jukebok +# +# © 2000, Rolf Brugger +# +################################################## + + +### +### GiantDisc: Common database routines (mainly db modification) +### + +package gddb; + +use strict; + +### Init constants + + +#BEGIN{ +#} + +############################################################ +### TOOL ROUTINES ### +############################################################ + +############################################################ + +sub sql_escape_squotes{ + ### escapes single quotes of the string passed as argument + # + # Parameter: string to be quoted + # Returns: quoted string + + # Usually you would use $dbh->quote instead, except if you don't want + # to add single quotes around the string + + my $sqlstring = $_[0]; + $sqlstring =~ s/'/\\'/g; + return $sqlstring; +} + + +############################################################ +### +sub field_where_clause{ # returns a string with a SQL-where clause + # for a text field like artist or title. If + # keyword is empty, an empty string is returned + # + # - Multiple keywords are space separated + # - Field begins are matched using the * wildcard + + # Examples: + # field="artist" keyword="abc" + # -> "AND artist LIKE + # + # field="artist" keyword="abc xyz" + # -> "AND artist LIKE "%abc%" AND artist LIKE "%xyz%" " + # + # field="artist" keyword="abc*" (everything after * is ignored) + # -> "AND artist LIKE "abc%" " + # + + my ($fieldname,$keywords) = @_; + $keywords = sql_escape_squotes($keywords); + my $cmd = ""; + + if ($keywords ne ""){ + if($keywords =~ m/\*/ ){ + ### wildcard expression + my @words = split (/\*/, $keywords ); + $cmd.=" AND $fieldname LIKE '$words[0]%' "; + } + else{ ### non wildcard expression + my @words = split (/ /, $keywords ); + my $current; + while($current = shift(@words)){ + $cmd.=" AND $fieldname LIKE '%$current%' "; + } + } + } + return $cmd; +} + +############################################################ +### +my $get_all=0; +my $get_tracks=1; +my $get_streams=2; + +sub attrib_where_clause{ # returns a string with a SQL-where clause + # for genre, year, language, type and rating + # field names are prefixed by 'tracks.' that the clauses can also be + # used in JOIN queries. + + my ($get_what, $genre1,$genre2,$yearfrom,$yearto,$lang,$type,$rating) = @_; + + my $tmpcmd; + my $cmd=" "; + + ### Genre + $tmpcmd="0 "; # false OR ... + if ($genre1 ne ""){ + $tmpcmd.="OR tracks.genre1 LIKE '$genre1%' OR tracks.genre2 LIKE '$genre1%' ";}; + if ($genre2 ne ""){ + $tmpcmd.="OR tracks.genre1 LIKE '$genre2%' OR tracks.genre2 LIKE '$genre2%' ";}; + if (length($tmpcmd)>3){ # genre query not empty? + $cmd .= " AND ($tmpcmd) "; + } + + ### Year + if(length($yearfrom)==4){ $cmd.=" AND tracks.year >= ".$yearfrom;} + if(length($yearto)==4){ $cmd.=" AND tracks.year <= ".$yearto;} + + ### Language + if ($lang ne ""){ $cmd.=" AND tracks.lang = '$lang' ";}; + + ### type + if ($type ne ""){ $cmd.=" AND tracks.type = '$type' ";}; + + ### rating + if ($rating ne ""){ + if ($rating == 0) { + $cmd.=" AND tracks.rating = $rating "; + } + else{ + $cmd.=" AND tracks.rating >= $rating "; + } + } + + ### track/stream/all + if($get_what==$get_tracks){ + $cmd.=" AND mp3file NOT LIKE 'http://%' "; + } + if($get_what==$get_streams){ + $cmd.=" AND mp3file LIKE 'http://%' "; + } + + return $cmd; +} + +############################################################ +### +sub track_where_clause{ # returns the where clause without keyword "WHERE" + my ($get_what, $artist,$title,$genre1,$genre2,$yearfrom,$yearto, + $lang,$type,$rating, + $ordercmd) = @_; + + my $where=" 1 "; # true AND ... + + ### Artist + $where .= field_where_clause("artist",$artist); + ### Title + $where .= field_where_clause("title",$title); + ### genre, etc ... + $where.= attrib_where_clause($get_what, $genre1,$genre2,$yearfrom,$yearto,$lang,$type,$rating); + + return $where; +} + +############################################################ + +sub track_order_clause{ # returns the order clause with keyword "ORDER BY" + my ($get_what, $artist,$title,$genre1,$genre2,$yearfrom,$yearto,$lang,$type,$rating, + $ordercmd) = @_; + + my $order = ""; + + if(length($ordercmd)>1){ + $order = "ORDER BY "; + if ($ordercmd =~ m/random/ ){ + $order .= "RAND() "; + } + elsif($ordercmd =~ m/year/ ){ + $order .= "year "; + } + elsif($ordercmd =~ m/recd/ ){ + $order .= "created "; + } + elsif($ordercmd =~ m/modd/ ){ + $order .= "modified "; + } + elsif($ordercmd =~ m/recmod/ ){ + $order .= "GREATEST(created,modified) "; + } + + if($ordercmd =~ m/-inv/ ){ + $order .= " DESC "; + } + } + + return $order; +} + +############################################################ +### +sub album_where_clause{ + my ($artist,$title,$genre1,$genre2,$yearfrom,$yearto, + $lang,$type,$rating, + $ordercmd) = @_; + + my $where=" 1 "; # true AND ... + ### Album: Artist + $where .= gddb::field_where_clause("album.artist",$artist); + ### Album: Title + $where .= gddb::field_where_clause("album.title",$title); + ### Track: genre, etc ... + $where.= gddb::attrib_where_clause(1, $genre1,$genre2,$yearfrom,$yearto,$lang,$type,$rating); + +#print "ALBUM WHERE: $where\n"; + return $where; +} + +############################################################ + +sub album_order_clause{ # returns the order clause with keyword "ORDER BY" + my ($artist,$title,$genre1,$genre2,$yearfrom,$yearto,$lang,$type,$rating, + $ordercmd) = @_; + + my $order = ""; + + if(length($ordercmd)>1){ + $order = "ORDER BY "; + if ($ordercmd =~ m/random/ ){ + $order .= "RAND() "; + } + elsif($ordercmd =~ m/year/ ){ + $order .= "tracks.year "; + } + elsif($ordercmd =~ m/recd/ ){ + $order .= "album.modified "; + } + elsif($ordercmd =~ m/modd/ ){ + $order .= "album.modified "; + } + elsif($ordercmd =~ m/recmod/ ){ + $order .= "album.modified "; + } + + if($ordercmd =~ m/-inv/ ){ + $order .= " DESC "; + } + } + + return $order; +} + +############################################################ +### DB CREATION & UPDATE ### +############################################################ + +############################################################ +### Creates/updates a new track record + # If $id is empty, a new record is created. Otherwise, the record + # is updated. + # Returns the (new) id + +sub insert_track_record{ + my ($dbh,$artist,$title,$genre1,$genre2,$year, + $lang,$type,$rating,$length,$source,$sourceid, + $tracknb,$mp3file,$condition,$voladjust, + $lengthfrm,$startfrm,$bpm,$bitrate, + $created,$id, ### these two fields are only defined on update! + ) = @_; + + if(length($artist)==0){$artist="-";}; + if(length($title)==0) {$title="-";}; + if(length($year)<4) {$year="0";}; + if(length($type)==0) {$type="NULL";}; + if(length($rating)==0){$rating="NULL";}; + if(length($length)==0){$length="0";}; + if(length($source)==0){$source="0";}; + if(length($tracknb)==0){$tracknb="0";}; + if(length($condition)==0){$condition="0";}; + if(length($voladjust)==0){$voladjust="0";}; + if(length($lengthfrm)==0){$lengthfrm="0";}; + if(length($startfrm)==0) {$startfrm="0";}; + if(length($bpm)==0) {$bpm="0";}; + if(length($bitrate)==0) {$bitrate="128";}; + if(length($created)==0) {$created="CURDATE()";}; + + + my $sqlcmd; + $sqlcmd = + "artist=".$dbh->quote($artist).", " # quote adds single quotes around the string! + ."title=".$dbh->quote($title).", " + ."genre1='$genre1', " + ."genre2='$genre2', " + ."year = $year, " + ."lang ='$lang', " + ."type = $type, " + ."rating=$rating, " + ."length=$length, " + ."source=$source, " + ."sourceid='$sourceid', " + ."tracknb=$tracknb, " + ."mp3file='$mp3file', " + ."condition=$condition, " + ."voladjust=$voladjust, " + ."lengthfrm=$lengthfrm, " + ."startfrm=$startfrm, " + ."bpm=$bpm, " + ."bitrate='$bitrate', "; + + if(length($id)==0){ + ### INSERT a new record + $sqlcmd = "INSERT tracks SET ".$sqlcmd + ."created=CURDATE() "; + } + else{ + ### REPLACE an existing record + $sqlcmd = "UPDATE tracks SET ".$sqlcmd + ."created='$created', " + ."modified=CURDATE() " + ."WHERE id=$id "; + } + + #print("SQL: $sqlcmd \n"); + my $sth = $dbh->prepare($sqlcmd); + $sth->execute; + + if(length($id)==0){ ### if new record created + $id = $sth->{mysql_insertid}; + } + $sth->finish; + return $id; +} + + + + + +############################################################ +### QUERIES ### +############################################################ +# + + + + + +#### end +1; diff --git a/scripts/gdgentools.pm b/scripts/gdgentools.pm new file mode 100755 index 0000000..3522302 --- /dev/null +++ b/scripts/gdgentools.pm @@ -0,0 +1,1606 @@ +package gdgentools; + +################################################## +# +# GiantDisc mp3 Jukebox +# +# © 2000, Rolf Brugger +# +################################################## + + + +### General tool routines + +use lib '/home/andi/muggle/import'; +use gdparams; +use IO::Socket; + + +### Constants +my $pl_list = 0; # play list +my $rp_list = 1; # rip list +my $co_list = 2; # compression list +my $cr_list = 3; # cd recording list + + + + + +#use lib '/usr/local/bin'; +#BEGIN{;} +#END{;} + + +############################################################ +### Shuffle routine +sub fisher_yates_shuffle {# generate a random permutation of @array in place + my $array = shift; + my $i; + for ($i = @$array; --$i; ) { + my $j = int rand ($i+1); + next if $i == $j; + @$array[$i,$j] = @$array[$j,$i]; + } +} +# USAGE: +# fisher_yates_shuffle( \@array ); # permutes @array in placesub query_random_artists{ + + + + + +############################################################ +### barix exstreamer routines + +sub exstreamer_command{ + # sends a command string to the exstreamer at 'playerhost', without + # waiting for a result that is sent back by the exstreamer + + my ($playerhost, $command) = @_; + my $port = 12302; # default exstreamer tcp command port + my $sock = new IO::Socket::INET( PeerAddr => $playerhost, + PeerPort => $port, + Proto => 'tcp'); + die "Error: could not open socket $opt_h:$port. $!\n" unless $sock; + + ### send the command + print $sock $command."\n"; + close($sock); +} + + +sub exstreamer_command_res{ + # sends a command string to the exstreamer at 'playerhost'. + # It returns the first line that is sent back by the exstreamer (last \n chopped off) + + my ($playerhost, $command) = @_; + my $port = 12302; # default exstreamer tcp command port + my $sock = new IO::Socket::INET( PeerAddr => $playerhost, + PeerPort => $port, + Proto => 'tcp'); + die "Error: could not open socket $opt_h:$port. $!\n" unless $sock; + + ### send a command + print $sock $command."\n"; + my $res = <$sock>; + close($sock); + chop $res; + return $res; +} + + +############################################################ +### Playlist routines + + +# creates new playstate record. If one with the same playerid/audiochannel +# exists, it is overwritten. +sub pll_new_playstate{ + my ($dbh, $playerid, $audiochannel, $playertype, $snddevice, $playerapp, $playerparams, $ptlogger) = @_; + $dbh->do("REPLACE INTO playerstate SET " + ."playerid=$playerid, " + ."audiochannel=$audiochannel, " + ."playertype=$playertype, " + ."snddevice='$snddevice', " + ."playerapp='$playerapp', " + ."playerparams='$playerparams', " + ."ptlogger='$ptlogger', " + ."currtracknb=0, " + ."state='st', " # stopped + ."framesplayed=0, " + ."framestotal=0 " + ); +} + +# deletes playstate record +sub pll_del_playstate{ + my ($dbh, $playerid, $audiochannel) = @_; #parameters: database handle, player id, sound out it + $dbh->do("REPLACE FROM playerstate " + ."WHERE playerid=$playerid AND audiochannel=$audiochannel "); +} + + +# Returns th specified player-type +sub pll_get_playertype{ + my ($dbh, $playerid, $audiochannel) = @_; #parameters: database handle, player id, sound out it + + my $sth = $dbh->prepare( + "SELECT playertype,ipaddr,snddevice FROM player,playerstate " + ."WHERE player.id=playerstate.playerid " + ." AND player.id=$playerid " + ." AND playerstate.audiochannel=$audiochannel"); + + my $nbrec = $sth->execute; + #print("$nbrec playstates found (should be exactly 1)\n"); + + my ($playertype,$playerhost,$snddevice); + + if($row = $sth->fetchrow_hashref){ + $playertype = $row->{playertype}; + $playerhost = $row->{ipaddr}; + $snddevice = $row->{snddevice}; + } + else{ + ### This case should not happen! make the best out of it + print ("ERROR: can't get playertype for player $playerid channel $audiochannel\n"); + print (" no player/playerstate record found\n"); + } + $sth->finish; + + return ($playertype,$playerhost,$snddevice); +} + + +# Returns the current playstate +sub pll_get_playstate{ + my ($dbh, $playerid, $audiochannel) = @_; #parameters: database handle, player id, sound out it + + my $sth = $dbh->prepare( + "SELECT * FROM playerstate " + ."WHERE playerid=$playerid AND audiochannel=$audiochannel" ); + + my $nbrec = $sth->execute; + #print("$nbrec playstates found (should be exactly 1)\n"); + + my $currtracknb = 0; + my $state = "st"; + my $shufflestat = ""; + + if($row = $sth->fetchrow_hashref){ + $currtracknb = $row->{currtracknb}; + $state = $row->{state}; + $framesplayed= $row->{framesplayed}; + $shufflestat = $row->{shufflestat}; + } + else{ + ### This case should not happen! make the best out of it + #pll_new_playstate($dbh, $playerid, $audiochannel, "", "", ""); + print ("ERROR: can't get playstate for player $playerid channel $audiochannel\n"); + print (" no playerstate record found\n"); + } + $sth->finish; + + return ($currtracknb, $state, $framesplayed, $shufflestat); +} + + +# Returns +sub playerdefinition{ + my ($dbh, $playerid, $audiochannel) = @_; #parameters: database handle, player id, sound out it + + my $sth = $dbh->prepare( + "SELECT playertype, ipaddr, snddevice FROM player,playerstate " + ."WHERE player.id=$playerid AND playerstate.playerid=$playerid AND audiochannel=$audiochannel"); + + my $nbrec = $sth->execute; + #print("$nbrec playstates found (should be exactly 1)\n"); + + my ($playertype, $playerhost, $sounddevice); + my $playertype = 0; + my $playerhost = "localhost"; + my $sounddevice = "/dev/dsp"; + + if($row = $sth->fetchrow_hashref){ + $playertype = $row->{playertype}; + $playerhost = $row->{ipaddr}; + $sounddevice = $row->{snddevice}; + } + else{ + ### This case should not happen! + print ("ERROR: can't get player definition for player $playerid channel $audiochannel\n"); + print (" no playerstate record found\n"); + } + $sth->finish; + + return ($playertype, $playerhost, $sounddevice); +} + + +# Returns the process id of the player (that was previously stored in the DB) +sub pll_get_player_processid{ + my ($dbh, $playerid, $audiochannel) = @_; #parameters: database handle, player id, sound out it + my $sth = $dbh->prepare( + "SELECT processid FROM playerstate " + ."WHERE playerid=$playerid AND audiochannel=$audiochannel" ); + my $nbrec = $sth->execute; + my $playerpid = -1; + if($row = $sth->fetchrow_hashref){ + $playerpid = $row->{processid}; + } + else{ + ### This case should not happen! make the best out of it + print ("ERROR: can't get playstate for player $playerid channel $audiochannel\n no playerstate record found\n"); + } + $sth->finish; + return ($playerpid); +} + + + +# Writes the playstate to the playstate record +# Parameters: +# currtracknb +# state (one of: pl, st, in, ff, Ff, rw, Rw) [in=pause(interrupted)] +sub pll_write_playstate{ + my ($dbh, $playerid, $audiochannel, $currtracknb, $state) = @_; + my $retval = $dbh->do( + "UPDATE playerstate " + ."SET currtracknb=$currtracknb, state='$state' " + ."WHERE playerid=$playerid AND audiochannel=$audiochannel"); +} + + +# Writes the players process id to the playstate record +# The programs 'gdplayd.pl' and 'gdplaytmsim.pl' write their process id into +# the DB. This is necessary to efficiently stop playing or rewinding. +sub pll_write_player_processid{ + my ($dbh, $playerid, $audiochannel, $playerpid) = @_; + my $retval = $dbh->do( + "UPDATE playerstate " + ."SET processid=$playerpid " + ."WHERE playerid=$playerid AND audiochannel=$audiochannel"); +} + +#sub pll_clear_player_processid{ +# my ($dbh, $playerid, $audiochannel, $playerpid) = @_; +# if ($gdparms::multiclients){ +##print "clearing pid \n"; +# my $retval = $dbh->do( +# "UPDATE playerstate " +# ."SET processid=0 " +# ."WHERE playerid=$playerid AND audiochannel=$audiochannel"); +# } +#} + + +### Shuffle Play Parameters +sub pll_write_shuffleparameters{ + my ($dbh, $playerid, $audiochannel, $parameterstring, $statisticsstring) = @_; + print("writing shuffleparamstring: '$parameterstring'\n"); + my $retval = $dbh->do( + "UPDATE playerstate " + ."SET shufflepar =".$dbh->quote($parameterstring).", " + ." shufflestat=".$dbh->quote($statisticsstring)." " + ."WHERE playerid=$playerid AND audiochannel=$audiochannel"); +} + + + +####################################################################### +### Stop playing +# +# Stopping to play means killing the specifig playing-deamon 'gdplayd.pl' +# and all the sub-processes it has spawned. +# +# As long as only one instance of a GiantDisc server is running on a +# machine, we can just blindly kill all potentially spawned processes by +# name (see routine 'kill_all_play_processes'). +# +# If we have more than one server instance on the same machine, this blindly +# killing method doesn't work anymore. Stopping to play on one instance would +# terminate the play processes of all other server instances. It is therefore +# necessary to specifically kill the involved processes. I have tried many +# (really a lot of) approaches during 2 years - nothing really worked well. +# There were 2 main problems: +# - killing the processes was far too slow +# - multiple instances of gdplayd.pl and it sub-processes appeared leading +# to locked soundcards etc. ( This especially happened, when playing was +# quickly stopped and restarted, or after fast sequences of 'play next' +# commands. +# +# The currently adopted however method seems to work fine, it is robust and +# efficient enough. It is explained in detail below, see routines plmsg_... +# + + +### tested and not well working: Killall and Killfam! +### Comment: Proc::Killall is terribly slow (much slower than system("killall...")) +### using Proc::Killfam is unstable because of overlapping playing +### commands, that inevitably lead to multiple instances of gdplayd.pl +### (mainly because pid of gdplayd.pl is known too late?) +### Finally - Proc::Killfam is too slow too! + + +sub stop_play_processes{ + + my ($dbh, $playerid, $audiochannel) = @_; + my $new_kill_player = 1; +# if ($gdparms::multiclients){ + if ($new_kill_player){ + my $playerpid = pll_get_player_processid($dbh, $playerid, $audiochannel); + + if ($playerpid >0){ + ### get all child processes of $playerpid + my $chprocs = `pstree $playerpid -p |tr -d '\012'`; # get process id's, on one text line + my @chpids = split /\(/, $chprocs; + shift @chpids; # pop off first element + foreach $elem (@chpids){ + $elem =~ s/\).*//; + } + + if (scalar(@chpids)>0){ + #print " specifically killing ".join (",",@chpids)."\n"; + system "kill ".join (" ",@chpids); + } + } + } + else{ + kill_all_play_processes(); + } +} + + +sub kill_all_play_processes{ + #print "killing blindly\n"; + system("killall -q gdplayd.pl; " + ."killall -q mpg123; " + ."killall -q ogg123; " + ."killall -q flac; " + ."killall -q rawplay; " + ."killall -q gdplaytmsim.pl; " + ."killall -q gdstream.pl"); + + #killall and killfam are terribly slow! +} + + +####################################################################### +### IPC Message routines to make sure, that a stop operation really kills +# all player processes. +# Method: +# 1) the server starts the player 'gdplayd.pl' in background +# 2) right after starting gdplayd.pl, the server waits for a message of +# gdplayd.pl +# 3) in the init phase of gdplayd.pl, it writes it's own process-id to the +# database and sends then a message to the message queue +# 4) the server receives the message and can continue to process requests +# from the palm +# 5) when the server should now stop playing, it is 100% sure, that the +# correct process-id o the playing process is in the db. +# Killing it (and it's subprocesses) stops playing. + +use IPC::SysV qw(IPC_CREAT S_IRWXU ftok); +use IPC::Msg; + +# Die folgenden Konstanten kennzeichnen die Message-Queue im System. +# RENDEZVOUS muss der Name einer _existierenden_ Datei sein! +# Nur die unteren 8 Bits von RVID sind wichtig und muessen !=0 sein! +# ftok(RENDEZVOUS, RVID) erzeugt einen immer identischen Schluessel, +# so lange die Datei RENDEZVOUS nicht neu angelegt wurde. + +use constant RENDEZVOUS => "/etc"; +#use constant RVID => 121; + +sub rendevous_id{ + my ($playerid, $audiochannel) = @_; +#print "p=$playerid, chn=$audiochannel -> rdvid=".($playerid*16 + $audiochannel + 1)."\n"; + if ($playerid>15 || $audiochannel>15){ + print "\n WARNING:\n "; + print "too many palyers/audiochannels\n"; + print " p=$playerid, chn=$audiochannel -> rdvid=".($playerid*16 + $audiochannel + 1)."\n\n"; + } + return $playerid*16 + $audiochannel + 1; +} +sub plmsg_newqueue{ + ### creates messae queue RENDEVOUS, if it doesn't already exist + my ($playerid, $audiochannel) = @_; + my $rdvid = rendevous_id($playerid, $audiochannel); + use vars qw($msg); + $msg = new IPC::Msg(ftok(RENDEZVOUS, $rdvid), S_IRWXU | IPC_CREAT); + +} + +sub plmsg_send{ + ### appends a message to the queue + my ($playerid, $audiochannel) = @_; + my $rdvid = rendevous_id($playerid, $audiochannel); + my $msg = new IPC::Msg(ftok(RENDEZVOUS, $rdvid), 0); + my ($prio, $text)=(1,"player started");# = @MESSAGES[$i,$i+1]; + $msg->snd($prio, $text, 0); +} + +sub plmsg_waitrcv{ + my ($playerid, $audiochannel) = @_; + ### pulls one message from the message queue, waits until one message is there + my $rdvid = rendevous_id($playerid, $audiochannel); + my $msg = new IPC::Msg(ftok(RENDEZVOUS, $rdvid), 0); + my $buflen = 256; + $prio = $msg->rcv($buf, $buflen, 0, 0); +# print "Found: ($buf, $prio)\n"; +} + + +sub pl_start_playd_and_wait{ +# starts playing-deamon and waits until it has written it's pid in the db + my ($dbhost, $playerid, $audiochannel) = @_; +#print "--> start gdplayd.pl\n"; + system("gdplayd.pl $dbhost $playerid $audiochannel & "); +#print "--> wait for gdplayd.pl to be started\n"; + plmsg_waitrcv($playerid, $audiochannel); +#print "--> message received\n"; +} + + +####################################################################### +####################################################################### +# +# Basic Play Controls (also used as API) + + +sub pl_play{ +# starts playing + my ($dbh, $playerid, $audiochannel) = @_; + my ($trackind, $state, $frame, $shufflestat) = pll_get_playstate($dbh, $playerid, $audiochannel); + if(tracklist_get_item($dbh, $playerid, 0, $trackind) < 1){$trackind=1;} # playstate error + stop_play_processes($dbh, $playerid, $audiochannel); + pll_write_playstate($dbh, $playerid, $audiochannel, $trackind, "pl"); + pl_start_playd_and_wait($gdparms::dbhost, $playerid, $audiochannel); +# system("gdplayd.pl $gdparms::dbhost $playerid $audiochannel & "); +} + +sub pl_play_at{ +# starts playing at specified position (seconds) + my ($dbh, $playerid, $audiochannel, $songpos_sec) = @_; +# my ($songpos_sec) = @_; + my ($trackind, $state, $frame, $shufflestat) = pll_get_playstate($dbh, $playerid, $audiochannel); + if(tracklist_get_item($dbh, $playerid, 0, $trackind) < 1){$trackind=1;} # playstate error + stop_play_processes($dbh, $playerid, $audiochannel); + my $startframe = $songpos_sec * frames_per_second(); + pll_write_playtime_only($dbh, $playerid, $audiochannel, $startframe); + pll_write_playstate($dbh, $playerid, $audiochannel, $trackind, "pl"); + pl_start_playd_and_wait($gdparms::dbhost, $playerid, $audiochannel); +# system("gdplayd.pl $gdparms::dbhost $playerid $audiochannel & "); +} + +sub pl_stop{ +# stops playing and reset playtime-state + my ($dbh, $playerid, $audiochannel) = @_; + my ($trackind, $state, $frame, $shufflestat) = pll_get_playstate($dbh, $playerid, $audiochannel); + my($played, $total) = pll_get_playtime($dbh, $playerid, $audiochannel); + stop_play_processes($dbh, $playerid, $audiochannel); + pll_write_playstate($dbh, $playerid, $audiochannel, $trackind, "st"); + pll_write_playtime($dbh, $playerid, $audiochannel, 0, $total); +} + +sub pl_pause{ +# stops playing and preserve playtime-state + my ($dbh, $playerid, $audiochannel) = @_; + my ($trackind, $state, $frame, $shufflestat) = pll_get_playstate($dbh, $playerid, $audiochannel); + my($played, $total) = pll_get_playtime($dbh, $playerid, $audiochannel); + stop_play_processes($dbh, $playerid, $audiochannel); + pll_write_playstate($dbh, $playerid, $audiochannel, + $trackind, "in"); # state: interrupted +} + +sub pl_rw{ +# similar as pause + my ($dbh, $playerid, $audiochannel) = @_; + my ($trackind, $state, $frame, $shufflestat) = pll_get_playstate($dbh, $playerid, $audiochannel); + my ($played, $total) = pll_get_playtime($dbh, $playerid, $audiochannel); + stop_play_processes($dbh, $playerid, $audiochannel); + pll_write_playstate($dbh, $playerid, $audiochannel, + $trackind, "rw"); # state: rw + + ### continuously write current playtime to db (every second) + system("gdplaytmsim.pl $gdparms::dbhost $playerid $audiochannel & "); +} + +sub pl_ff{ +# similar as pause + my ($dbh, $playerid, $audiochannel) = @_; + my ($trackind, $state, $frame, $shufflestat) = pll_get_playstate($dbh, $playerid, $audiochannel); + my ($played, $total) = pll_get_playtime($dbh, $playerid, $audiochannel); + stop_play_processes($dbh, $playerid, $audiochannel); + pll_write_playstate($dbh, $playerid, $audiochannel, + $trackind, "ff"); # state: ff + + ### continuously write current playtime to db (every second) + system("gdplaytmsim.pl $gdparms::dbhost $playerid $audiochannel & "); +} + +sub pl_goto{ + ### makes a new track the current track + # sets the current playtime-posititon to zero + # the rest of the playstate is preserved + + use integer; + my ($dbh, $playerid, $audiochannel, $newind) = @_; +# my ($newind) = @_; # the new index (must be valid) + + stop_play_processes($dbh, $playerid, $audiochannel); + + my ($trackind, $state, $frame, $shufflestat) = pll_get_playstate($dbh, $playerid, $audiochannel); + pll_write_playstate($dbh, $playerid, $audiochannel, $newind, $state); + pll_write_playtime($dbh, $playerid, $audiochannel, 0, 0); + if($state eq "pl"){ # restart player + pl_start_playd_and_wait($gdparms::dbhost, $playerid, $audiochannel); +# system("gdplayd.pl $gdparms::dbhost $playerid $audiochannel & "); + } + else{ # new 'current' track: load it's total length + my $trackid = tracklist_get_item($dbh, $playerid, 0, $newind); + if (length($trackid)>0) { + my $sth = $dbh->prepare("SELECT * FROM tracks WHERE id = $trackid"); + my $cnt = $sth->execute; + if ($cnt > 0){ + my $row = $sth->fetchrow_hashref; + pll_write_playtime($dbh, $playerid, $audiochannel, 0, $row->{length}*frames_per_second()); + } + else{ + pll_write_playtime($dbh, $playerid, $audiochannel, 0, 0); + } + $sth->finish; + } + else{ + pll_write_playtime($dbh, $playerid, $audiochannel, 0, 0); + } + } +} + +sub pl_next{ + my ($dbh, $playerid, $audiochannel) = @_; + my ($trackind, $state, $frame, $shufflestat) = pll_get_playstate($dbh, $playerid, $audiochannel); + my $listlen = tracklist_get_nb_items($dbh, $playerid, $pl_list); + if ($trackind < $listlen) {$trackind++;} + pl_goto($dbh, $playerid, $audiochannel, $trackind); +} + +sub pl_prev{ + my ($dbh, $playerid, $audiochannel) = @_; + my $frames5sec = 5*frames_per_second(); # nb frames in 5 sec + my ($trackind, $state, $frame, $shufflestat) = pll_get_playstate($dbh, $playerid, $audiochannel); + my ($played, $total) = pll_get_playtime($dbh, $playerid, $audiochannel); + if ($trackind>0 && $played<$frames5sec){$trackind--;} + pl_goto($dbh, $playerid, $audiochannel, $trackind); +} + +####################################################################### +####################################################################### + + + + + +####################################################################### +# +# Playtime routines: +# The current position is always saved in the field 'framesplayed' +# If the player app can't continuously write this field, another realtime +# app has to write the current playtime (and ff, rew position) + +# mp3 constants +my $samplesPerFrame = 1152; +my $samplesPerSecond = 44100; + +sub frames_per_second{ + use integer; + return $samplesPerSecond/$samplesPerFrame; # = 38.281 +} +sub samples_per_frame{ + return $samplesPerFrame; +} +sub samples_per_second{ + return $samplesPerSecond; +} + + +# Writes the playtime to the playtime record. +# Parameters: played, total (units: frames) +sub pll_write_playtime{ + my ($dbh, $playerid, $audiochannel, $played, $total) = @_; + my $retval = $dbh->do( + "UPDATE playerstate " + ."SET framesplayed=$played, framestotal=$total " + ."WHERE playerid=$playerid AND audiochannel=$audiochannel"); +} + +sub pll_write_playtime_only{ # like pll_write_playtime without changing 'framestotal' + my ($dbh, $playerid, $audiochannel, $played) = @_; + my $retval = $dbh->do( + "UPDATE playerstate " + ."SET framesplayed=$played " + ."WHERE playerid=$playerid AND audiochannel=$audiochannel"); +} + + +# Returns the current playtime (frames played and total frames) +sub pll_get_playtime{ + my ($dbh, $playerid, $audiochannel) = @_; + my $played = 0; + my $total = 0; + + use integer; + + my $sth = $dbh->prepare( + "SELECT * FROM playerstate " + ."WHERE playerid=$playerid AND audiochannel=$audiochannel" ); + + my $nbrec = $sth->execute; + #print("$nbrec playtime found (should be exactly 1)\n"); + + if($row = $sth->fetchrow_hashref){ + $total = $row->{framestotal}; + $played = $row->{framesplayed}; + } + $sth->finish; + + return ($played, $total); +} + + +sub seconds_to_hm{ + my ($seconds) = @_; + my ($hours, $minutes); + use integer; # switch to int math + $hours = $seconds / 3600; + $minutes = ($seconds % 3600)/60; + return sprintf("%ih%02im", $hours, $minutes); +} + +sub seconds_to_sm{ + my ($seconds) = @_; + my ($minutes, $sec); + use integer; # switch to int math + $minutes = $seconds / 60; + $sec = $seconds % 60; + return sprintf("%i:%02i", $minutes, $sec); +} + + +### is the string a mp3-file or a mp3-stream? +sub is_mp3stream{ + my ($mp3filename) = @_; + return ($mp3filename =~ /^http:\/\/.*/); # matches "http://" at the beginning? +} + + +####################################################################### + +# Returns the player parameter ($snddevice, $playerapp, $playerparams, $ptlogger, $shufflepar) +sub pll_get_playparams{ + my ($dbh, $playerid, $audiochannel) = @_; + my ($snddevice, $playerapp, $playerparams, $ptlogger); + + my $sth = $dbh->prepare( + "SELECT * FROM playerstate " + ."WHERE playerid=$playerid AND audiochannel=$audiochannel" ); + my $nbrec = $sth->execute; + + if($row = $sth->fetchrow_hashref){ + $snddevice = $row->{snddevice}; + $playerapp = $row->{playerapp}; + $playerparams = $row->{playerparams}; + $ptlogger = $row->{ptlogger}; + $shufflepar = $row->{shufflepar}; + } + $sth->finish; + + return ($snddevice, $playerapp, $playerparams, $ptlogger, $shufflepar); +} + + +############################################################ +# Returns the main player parameters +# ($ipaddr, $uichannel, $logtarget, $cdripper, $mp3encoder, $cdromdev, $cdrwdev) +sub pll_get_mainparams{ + my ($dbh, $playerid) = @_; + my ($ipaddr, $uichannel, $logtarget, $cdripper, $mp3encoder, $cdromdev, $cdrwdev); + + my $sth = $dbh->prepare( + "SELECT * FROM player " + ."WHERE id=$playerid" ); + my $nbrec = $sth->execute; + + if($row = $sth->fetchrow_hashref){ + $ipaddr = $row->{ipaddr}; + $uichannel = $row->{uichannel}; + $logtarget = $row->{logtarget}; + $cdripper = $row->{cdripper}; + $mp3encoder= $row->{mp3encoder}; + $cdromdev = $row->{cdromdev}; + $cdrwdev = $row->{cdrwdev}; + } + $sth->finish; + + return ($ipaddr, $uichannel, $logtarget, $cdripper, $mp3encoder, $cdromdev, $cdrwdev); +} + + +############################################################ +### General tracklist functions (table tracklistitem) +# +# 'tracknb' always starts with 0 and is contiguous +# ex: 0,1,2,3 is legal, 0,1,2,4,5 is illegal +# + +sub tracklist_get_nb_items{ + # returns the of tracks in the specified tracklist + my ($dbh, $playerid, $listtype) = @_; + my $listlen = 0; + my $sth = $dbh->prepare( + "SELECT COUNT(*) FROM tracklistitem " + ."WHERE playerid=$playerid AND listtype=$listtype" ); + $sth->execute; + my @row; + if(@row = $sth->fetchrow_array){ + $listlen = $row[0]; + } + $sth->finish; + return $listlen; +} + +sub tracklist_append_list{ + # appends a list of trackid's to the specified tracklist. + # Parameters: dbh, playerid, listtype, trackids... + my ($dbh) = shift(@_); + my ($playerid) = shift(@_); + my ($listtype) = shift(@_); + + my $curritem = tracklist_get_nb_items($dbh, $playerid, $listtype); + + while($trackid = shift(@_)){ + $dbh->do("INSERT INTO tracklistitem SET " + ."playerid=$playerid, " + ."listtype=$listtype, " + ."tracknb=$curritem, " + ."trackid=$trackid "); + $curritem++; + } +} + + +# moves the specified list chunk down by one +sub tracklist_move_chunk_up{ + my ($dbh, $playerid, $listtype, $first, $last) = @_; + # "shift" specified items up (to higher index) by 1 + # have to do increment item by item because order is important + my $sth = $dbh->prepare( + "SELECT * FROM tracklistitem " + ."WHERE playerid=$playerid AND listtype=$listtype AND tracknb>=$first AND tracknb<=$last " + ."ORDER BY tracknb DESC" ); # order: highest index first! + $sth->execute; + my $row; + while($row = $sth->fetchrow_hashref){ + $dbh->do( + "UPDATE tracklistitem " + ."SET tracknb=tracknb+1 " + ."WHERE playerid=$playerid AND listtype=$listtype AND tracknb=".$row->{tracknb} ); + } + $sth->finish; +} + + +# removes the specified list item from the list (item index starts with 0) +sub tracklist_del_item{ + my ($dbh, $playerid, $listtype, $trackindex) = @_; + $dbh->do( + "DELETE FROM tracklistitem " + ."WHERE playerid=$playerid AND listtype=$listtype AND tracknb=$trackindex" ); + +### should write a routine 'tracklist_move_chunk_down' (like tracklist_move_chunk_up) + # "shift" following items down by 1 + # have to do decrement item by item because order is important + my $sth = $dbh->prepare( + "SELECT * FROM tracklistitem " + ."WHERE playerid=$playerid AND listtype=$listtype AND tracknb>$trackindex " + ."ORDER BY tracknb" ); + $sth->execute; + my $row; + while($row = $sth->fetchrow_hashref){ + $dbh->do( + "UPDATE tracklistitem " + ."SET tracknb=tracknb-1 " + ."WHERE playerid=$playerid AND listtype=$listtype AND tracknb=".$row->{tracknb} ); + } + $sth->finish; +} + +# removes the all list items from 0 to 'trackindex' +sub tracklist_del_upto_item{ + +### VERY INEFFICIENT IMPLEMENTATION!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! + + my ($dbh, $playerid, $listtype, $trackindex) = @_; + print("deleting up to $trackindex\n"); + my $counter=0; + while ($counter < $trackindex){ + tracklist_del_item($dbh, $playerid, $listtype, 0); + $counter++; + } +} + + +# reorders the specified list item in the list (item index starts with 0) +# -> 'destpos' must be lower than 'srcpos'! +sub tracklist_reorder_item{ + my ($dbh, $playerid, $listtype, $srcpos, $destpos) = @_; + + ### "move" src-item to a save location + my $savepos = -1000; # a bit too hacky? + $dbh->do( + "UPDATE tracklistitem " + ."SET tracknb=$savepos " + ."WHERE playerid=$playerid AND listtype=$listtype AND tracknb=$srcpos" ); + + # "shift" following items up by 1 + tracklist_move_chunk_up($dbh, $playerid, $listtype, $destpos, ($srcpos)-1); + + ### "move" src-item from save location to destination + $dbh->do( + "UPDATE tracklistitem " + ."SET tracknb=$destpos " + ."WHERE playerid=$playerid AND listtype=$listtype AND tracknb=$savepos" ); +} + + + +# empties the specified tracklist +sub tracklist_delete{ + my ($dbh, $playerid, $listtype) = @_; + $dbh->do( + "DELETE FROM tracklistitem " + ."WHERE playerid=$playerid AND listtype=$listtype" ); +} + + +# gets the trackid of the specified list item (item index starts with 0) +# If an error occurs, 0 is returned. +sub tracklist_get_item{ + my ($dbh, $playerid, $listtype, $trackindex) = @_; + my $trackid = 0; + my $sth = $dbh->prepare( + "SELECT * FROM tracklistitem " + ."WHERE playerid=$playerid AND listtype=$listtype AND tracknb=$trackindex" ); + my $nbrec = $sth->execute; + if($row = $sth->fetchrow_hashref){ + $trackid = $row->{trackid}; + } + $sth->finish; + return $trackid; +} + + +# Returns the current playlist (list of track ID's) +sub tracklist_get_all{ + my ($dbh, $playerid, $listtype) = @_; + my @playlist=(); + + my $sth = $dbh->prepare( + "SELECT * FROM tracklistitem " + ."WHERE playerid=$playerid AND listtype=$listtype " + ."ORDER BY tracknb" ); + my $nbrec = $sth->execute; + while($row = $sth->fetchrow_hashref){ + $trackid = $row->{trackid}; + push @playlist, $row->{trackid}; + } + $sth->finish; + + return @playlist; +} + + + +############################################################ +### id3tag functions + +### returns the value of a id3 tag or other mp3 parameters for the specified file +### Possible tagcodes are + +### NEW version: + +# %a Artist [string] +# %b Number of corrupt audio frames [integer] +# %c Comment [string] +# %C Copyright flag [string] +# %e Emphasis [string] +# %E CRC Error protection [string] +# %f Filename without the path [string] +# %F Filename with the path [string] +# %g Musical genre [string] +# %G Musical genre [integer] +# %l Album name [string] +# %L MPEG Layer [string] +# %m Playing time: minutes only [integer] +# %n Track [integer] +# %O Original material flag [string] +# %o Stereo/mono mode [string] +# %p Padding [string] +# %Q Sampling frequency in Hz [integer] +# %q Sampling frequency in KHz [integer] +# %r Bit Rate in KB/s (type and meaning +# affected by -r option) +# %s Playing time: seconds only [integer] +# (usually used in conjunction with # %m) +# %S Total playing time in seconds [integer] +# %t Track Title [string] +# %u Number of good audio frames [integer] +# %v MPEG Version [float] +# %y Year [string] +# %% A single percent sign + +sub get_mp3info{ + my ($tagcode, $filename) = @_; + my $base = gdparams::gdbase(); + my $res = `mp3info -p "$tagcode" "$filename"`; + + ### Error cases + if ($tagcode eq "%S" && length($res)==0){ + my $res2 = `mpg123 -v -t -n 0 "$filename" 2> $base/tmp/gdinfo.tmp;grep Frame $base/tmp/gdinfo.tmp`; + $res2 =~ m/.*\[(.+):(.+).(.+)\].*/; + my $min = $1; + my $sec = $2; + $res = $min*60 + $sec; + print("playlength fixed to $res\n"); + } + if($tagcode eq "%r" && ($res eq "Variable")) + { #check for Variable Bitrate +# $res="Var".`mp3info -p %r -r m "$filename"`; ### don't predeed wit 'wav' - it messes up bitrate calculations of streamer + $res=`mp3info -p %r -r m "$filename"`; + return $res; + } + if ($tagcode eq "%r" && ($res <= 0 )){ + $res = "128"; + } + + return $res; +} + + +############################################################ +### audio metadata with ogg support. +# +# standard ogg tags according to +# http://www.xiph.org/ogg/vorbis/doc/v-comment.html +# -> ARTIST TITLE ALBUM TRACKNUMBER YEAR GENRE COMMENT +# and + +### typical output of ogginfo +#>ogginfo file.ogg +#filename=file.ogg +# +#serial=6039 +#header_integrity=pass +#ALBUM=ob die Engel auch Beine haben +#TITLE=Die Schampullamaschine +#ARTIST=Zentriert ins Antlitz +#DATE=2002 +#TRACKNUMBER=7 +#GENRE=Industrial +#ORGANIZATION=- +#COMMENT=ZIA Ogg Vorbis 1.0 Final +#vendor=Xiph.Org libVorbis I 20020717 +#version=0 +#channels=2 +#rate=44100 +#bitrate_upper=none +#bitrate_nominal=128000 +#bitrate_lower=none +#stream_integrity=pass +#bitrate_average=126915 +#length=50.248005 +#playtime=0:50 +#stream_truncated=false +# +#total_length=50.248005 +#total_playtime=0:50 + +sub oggfile_title{ + my($audiofile) = @_; + $line = `ogginfo "$audiofile" |grep --ignore-case ^title=`; + chop $line; + return substr($line, 6); +} +sub oggfile_artist{ + my($audiofile) = @_; + $line = `ogginfo "$audiofile" |grep --ignore-case ^artist=`; + chop $line; + return substr($line, 7); +} +sub oggfile_album{ + my($audiofile) = @_; + $line = `ogginfo "$audiofile" |grep --ignore-case ^album=`; + chop $line; + return substr($line, 6); +} +sub oggfile_year{ + my($audiofile) = @_; + $line = `ogginfo "$audiofile" |grep --ignore-case ^date=`; + chop $line; + $line = substr($line, 5); + if ($line =~ /\D*(\d*).*/){ + return $1; + } + else { + return 0; + } +} +sub oggfile_lengthsec{ + my($audiofile) = @_; + $line = `ogginfo "$audiofile" |grep --ignore-case ^length=`; + chop $line; + my $lengthsec = substr($line, 7); + return int($lengthsec); +} +sub oggfile_bitrate{ + my($audiofile) = @_; + $line = `ogginfo "$audiofile" |grep --ignore-case ^bitrate_nominal=`; + chop $line; + my $bitrate = substr($line, 16); + return (int(($bitrate/4000)+0.5))*4; # round to modulo 4 +} +sub oggfile_tracknumber{ + my($audiofile) = @_; + $line = `ogginfo "$audiofile" |grep --ignore-case ^tracknumber=`; + chop $line; + $line = substr($line, 12); + return int($line); +} +sub oggfile_genre{ + return ""; +} + +###~CU~ #FLAC_BEGIN + +############################################################ +### FLAC metadata functions +### Autor: Christian Uebber +### +### Uses metaflac (see http://flac.sourceforge.net/) +### for extracting VORBIS_COMMENT metadata. +### +### Future versions may also support extracting +### cue-sheet information. +### + +sub flacfile_title{ + my($audiofile) = @_; + $line = `metaflac --show-vc-field=title "$audiofile"`; + chop $line; + return substr($line, 6); +} + +sub flacfile_artist{ + my($audiofile) = @_; + $line = `metaflac --show-vc-field=artist "$audiofile"`; + chop $line; + return substr($line, 7); +} +sub flacfile_album{ + my($audiofile) = @_; + $line = `metaflac --show-vc-field=album "$audiofile"`; + chop $line; + return substr($line, 6); +} + +sub flacfile_year{ + my($audiofile) = @_; + $line = `metaflac --show-vc-field=date "$audiofile"`; + chop $line; + $line = substr($line, 5); + if ($line =~ /\D*(\d*).*/){ + return $1; + } + else { + return 0; + } +} + +sub flacfile_lengthsec{ + my($audiofile) = @_; + $line = `metaflac --show-total-samples "$audiofile"`; + chop $line; + return int($line/44100); ## Please verify (theoretically correct) +} + +sub flacfile_bitrate{ + my($audiofile) = @_; + $line = `metaflac --show-sample-rate "$audiofile"`; + chop $line; + return (int($line/1000)); # respect maximum field length +# return ($line/1000); # respect maximum field length +# return (int($line)/100); # respect maximum field length +} + +### Alternative: +#sub flacfile_type{ +# my($audiofile) = @_; +# $line = `metaflac --show-sample-rate "$audiofile"`; +# chop $line; +# if ($line="44100"){ +# return "cda"; +# } +# else { +# return ""; +# } +#} + +sub flacfile_tracknumber{ + my($audiofile) = @_; + $line = `metaflac --show-vc-field=tracknumber "$audiofile"`; + chop $line; + $line = substr($line, 12); + return int($line); +} +sub flacfile_genre{ + my($audiofile) = @_; + $line = `metaflac --show-vc-field=genre "$audiofile"`; + chop $line; + return substr($line, 6); +} + +sub flacfile_tracknumber{ + my($audiofile) = @_; + $line = `metaflac --show-vc-field=tracknumber "$audiofile"`; + chop $line; + $line = substr($line, 12); + return int($line); +} + +###~CU~ #FLAC_END + +############################################################ + +sub audiofile_title{ + my($audiofile) = @_; # must be full filename with path! + my $ftype = audio_filetype($audiofile); + my $title = ""; + if ($ftype eq "mp3"){ + $title = get_mp3info("%t", $audiofile); + } + elsif ($ftype eq "ogg"){ + $title = oggfile_title($audiofile); + } + elsif ($ftype eq "flac"){ ###~CU~ + $title = flacfile_title($audiofile); + } + + if (length($title) > 0){ + return $title; + } + else { + use File::Basename; + return basename($audiofile); + } +} + + +sub audiofile_artist{ + my($audiofile) = @_; # must be full filename with path! + my $ftype = audio_filetype($audiofile); + my $artist = ""; + if ($ftype eq "mp3"){ + $artist = get_mp3info("%a", $audiofile); + } + elsif ($ftype eq "ogg"){ + $artist = oggfile_artist($audiofile); + } + elsif ($ftype eq "flac"){ + $artist = flacfile_artist($audiofile); ###~CU~ + } + + if (length($artist) > 0){ + return $artist; + } + else { + use File::Basename; + return basename($audiofile); + } +} + + +sub audiofile_album{ + my($audiofile) = @_; # must be full filename with path! + my $ftype = audio_filetype($audiofile); + if ($ftype eq "mp3"){ + return get_mp3info("%l", $audiofile); + } + elsif ($ftype eq "ogg"){ + return oggfile_album($audiofile); + } + elsif ($ftype eq "flac"){ + return flacfile_album($audiofile); ###~CU~ + } + else { + return "Album name"; + } +} + + +sub audiofile_year{ + my($audiofile) = @_; # must be full filename with path! + my $ftype = audio_filetype($audiofile); + if ($ftype eq "mp3"){ + return get_mp3info("%y", $audiofile); + } + elsif ($ftype eq "ogg"){ + return oggfile_year($audiofile); + } + elsif ($ftype eq "flac"){ + return flacfile_year($audiofile); ###~CU~ + } + else { + return 1990; + } +} + + +sub audiofile_lengthsec{ + my($audiofile) = @_; # must be full filename with path! + my $ftype = audio_filetype($audiofile); + if ($ftype eq "mp3"){ + return get_mp3info("%S", $audiofile); + } + elsif ($ftype eq "ogg"){ + return oggfile_lengthsec($audiofile); + } + elsif ($ftype eq "flac"){ + return flacfile_lengthsec($audiofile); ###~CU~ + } + else { + return 0; + } +} + + +sub audiofile_bitrate{ + my($audiofile) = @_; # must be full filename with path! + my $ftype = audio_filetype($audiofile); + if ($ftype eq "mp3"){ + return get_mp3info("%r", $audiofile); + } + elsif ($ftype eq "ogg"){ + return oggfile_bitrate($audiofile); + } + elsif ($ftype eq "flac"){ + return flacfile_bitrate($audiofile); # ###~CU~ flac: return sampling rate / please check alternative + } + else { + return 128; + } +} + + +sub audiofile_tracknumber{ + my($audiofile, $default_tracknb) = @_; # must be full filename with path! + my $ftype = audio_filetype($audiofile); + my $tracknb=0; + if ($ftype eq "mp3"){ + $tracknb = get_mp3info("%n", $audiofile); + } + elsif ($ftype eq "ogg"){ + $tracknb = oggfile_tracknumber($audiofile); + } + elsif ($ftype eq "flac"){ + $tracknb = flacfile_tracknumber($audiofile); + } + + if($tracknb > 0){ + return $tracknb; + } + else { + return $default_tracknb; + } +} + + +sub audiofile_genre{ + ### extracts id3 gerne code or genre string (depends on filetype) + ### returns GD-genre code + my($dbh, $audiofile) = @_; # must be full filename with path! + my $ftype = audio_filetype($audiofile); + if ($ftype eq "mp3"){ + my $id3genre = get_mp3info("%G", $audiofile); + return genre_id3togd($dbh, $id3genre); + } + elsif ($ftype eq "ogg"){ + my $genrestring = oggfile_genre($audiofile); + return genre_stringtogd($dbh, $genrestring); + } + elsif ($ftype eq "flac"){ + my $genrestring = flacfile_genre($audiofile); + return genre_stringtogd($dbh, $genrestring); + } + else { + return ""; + } +} + + +############################################################ + +sub audio_filetype{ + ### the filetype is derived form the filename extension + my($audiofile) = @_; + + if ($audiofile =~ /[Mm][Pp]3$/){ + return "mp3"; + } + if ($audiofile =~ /ogg$/){ + return "ogg"; + } + if ($audiofile =~ /flac$/){ + return "flac"; + } +} + + + +############################################################ + +sub get_bitrate_str{ + # returns format/bitrate in kBit/s (ex. "mp3 128", "ogg 112") + # takes as argument the audio file name with its full path. + my ($audiofile) = @_; + my $bitrate = audiofile_bitrate($audiofile); + my $ftype = audio_filetype($audiofile); + return "$ftype $bitrate"; +} + +sub bitrate_str_to_format_param{ + # gets a bitrate string like "mp3 128" and returns array ("mp3", "128") + my ($bitratestr) = @_; + my ($audiofmt, $audioparam) = split ' ', $bitratestr; # split "mp3 128" + if (length($audiofmt)<2){ # something went wrong + $audiofmt = "mp3"; # set reasonable default values + $audioparam = 128; + } + return ($audiofmt, $audioparam); +} + + +sub get_full_audiofile_path{ + # returns the full path of an audio file in a 00, 01, 02, ... directory + # or an empty string if the file doesn't exist + my ($audiofile) = @_; + my $base = gdparams::gdbase(); + my $fname = `ls $base/[0-9][0-9]/$audiofile`; # get full path + chop($fname); + return $fname; +} + +############################################################ +### Returns the first music directory with enough space + # to save ripped files. If no large enough directory is found + # an empty string is returned. + # Parameter: minimum free space in Mbytes + +sub get_ripdirectory{ + my ($minfreeMbytes) = @_; + my $base = gdparams::gdbase(); + + ### Get mp3 directories + my @mdir = gdparams::mp3dirs(); + + ### Get an mp3 directory with enough space left (1GB) + my $i=0; + my @dfres; + my $mbfree; + my $ripdir=""; + + while($i < @mdir){ + if (-d "$base/$mdir[$i]"){ + @dfres = split / +/, `df -m $base/$mdir[$i]|tail -1`; + $mbfree = $dfres[3]; + #print "$base/$mdir[$i] has $mbfree MB free \n"; + if($mbfree > $minfreeMbytes){ + $ripdir = $mdir[$i]; + last; # break + } + } + else{print "$base/$mdir[$i] is not a directory or does not exist\n";} + $i++; + } + #print("Rip directory: $ripdir \n"); + return $ripdir; +} + + +############################################################ +### import booklet/cover images +sub import_cover_images{ + # imports the jpeg images in a directory and associates them to an album + # the images are imported in lexical order. + # Naming scheme: trxx(cd-id)-(num).jpg, where num is an automatically + # incremented counter. The file imgxx(cd-id)-00.jpg is the front cover, + # the other are the following pages in a booklet. + + # Parameters: 1) dbh, + # 2) full directory path, + # 3) cd-id (like 0x10ac77e0, xx00001496) + # 4) test? (if set to 1, only show what would be done) + + my ($dbh, $fullpath, $cdid, $test) = @_; + + my ($sourcefile, $targetfile); + my $base = gdparams::gdbase(); + print "import images at $fullpath for id $cdid\n"; + + if (length($cdid)<10){ + print "Error (import_cover_images): illegal format of cdid '$cdid'\n"; + return; + } + + opendir INBOX, "$fullpath"; + my @imgfilelist = sort (grep /\.jpg$/, readdir INBOX); + closedir INBOX; + + #print "imgs: ".join (":",@imgfilelist)."\n"; + if (scalar(@imgfilelist) > 0){ + + ### Get a directory with enough space left (1GB) + my $ripdir=get_ripdirectory($gdparams::minfreehdspace); + + if($ripdir ne ""){### put image-file in music directory + my $imgnum = 0; + + foreach $sourcefile (@imgfilelist){ + $targetfile = sprintf ("$base/$ripdir/img%s-%02ld.jpg", $cdid, $imgnum); + if ($test){ + print "test: move '$fullpath/$sourcefile' to '$targetfile'\n"; + } + else{ + print "move '$fullpath/$sourcefile' to '$targetfile'\n"; + system "mv \"$fullpath/$sourcefile\" \"$targetfile\""; + if ($imgnum == 0){ + + my $sqlcmd = ("UPDATE album SET coverimg='img$cdid-00.jpg' WHERE cddbid = SUBSTRING('$cdid',3)"); + #print"\$dbh->do($sqlcmd);\n" + $dbh->do($sqlcmd); + } + } + $imgnum += 1; + }# end foreach + } + else{ + print("Not enough space left on disc \n"); + } + } + else{ + print "no jpeg images found in $fullpath \n"; + } +} + + +############################################################ +### common tool routines + +### returns highest number of imported (=not recorded from audio-CD, =no +### CDDB-ID associated) mp3 tracks. +### Parameter: database handle. +sub last_imported_tracknb{ + my ($dbh) = @_; + + my $mp3num; + my $trkseth = $dbh->prepare('select mp3file from tracks '. # get last record + 'where mp3file like "trxx________%" order by mp3file desc limit 1'); +# 'where mp3file like "trxx%" order by mp3file desc limit 1'); + my $nbmp3files = $trkseth->execute; + if ($nbmp3files > 0){ + $tracks = $trkseth->fetchrow_hashref; + $tracks->{mp3file} =~ m/trxx([0-9]*)\.\w/; # extract number + $mp3num = $1; + } + else{ + $mp3num = 0; + } + $trkseth->finish; + return $mp3num; +} + + +### Translates an id3 genre (numeric) to a GiantDisc genre code (string) +### Parameters: database handle, id3-genre. +### Returns: GiantDisc genre code (or empty string, if no match found) +sub genre_id3togd{ + my ($dbh, $id3genre)= @_; + + my $gdcode = ""; + if(length($id3genre)>0 && $id3genre >= 0){ + my $genseth = $dbh->prepare('select * from genre where id3genre="'. $id3genre . '" '); + my $nbgenres = $genseth->execute; + if ($nbgenres > 0){ + my $genres = $genseth->fetchrow_hashref; + $gdcode = $genres->{id}; + } + $genseth->finish; + } + #print("Translating id3:\"$id3genre\" to gdgenre \"$gdcode\" \n\n"); + return $gdcode; +} + +### Translates a genre string to a GiantDisc genre code (string) +### Parameters: database handle, genre string. +### Returns: GiantDisc genre code (or empty string, if no match found) +sub genre_stringtogd{ + my ($dbh, $genrestring)= @_; + + my $gdcode = ""; + if(length($genrestring)>0){ + my $genseth = $dbh->prepare( # get best=shortest match + "SELECT id,length(genre) AS len FROM genre " + ."WHERE genre like \"%".$genrestring."%\" ORDER BY len"); +# 'select id, from genre where genre="'. $genrestring . '" '); + my $nbgenres = $genseth->execute; + if ($nbgenres > 0){ + my $genres = $genseth->fetchrow_hashref; + $gdcode = $genres->{id}; + } + $genseth->finish; + } + #print("Translating genrestring:\"$genrestring\" to gdgenre \"$gdcode\" \n\n"); + return $gdcode; +} + + + +############################################################################### + +sub iso2ascii{ + # converts all non-ascii characters in the passed string as good + # as possible to ascii characters, and returns the result + + my ($str) = @_; + + $str =~ tr/\xc0-\xc6/A/; + $str =~ tr/\xc7/C/; + $str =~ tr/\xc8-\xcb/E/; + $str =~ tr/\xcc-\xcf/I/; + $str =~ tr/\xd0\xd1/DN/; + $str =~ tr/\xd2-\xd8/O/; + $str =~ tr/\xd9-\xdc/U/; + $str =~ tr/\xdd\xde\xdf/YTs/; + + $str =~ tr/\xe0-\xe6/a/; + $str =~ tr/\xe7/c/; + $str =~ tr/\xe8-\xeb/e/; + $str =~ tr/\xec-\xef/i/; + $str =~ tr/\xf0\xf1/dn/; + $str =~ tr/\xf2-\xf8/o/; + $str =~ tr/\xf9-\xfc/u/; + $str =~ tr/\xfd\xfe\xff/yts/; + + $str =~ tr/\xa0-\xff/_/; + + return $str; +} + +sub ascii2filename{ + # converts all ascii characters in the passed string as good + # as possible to ascii characters that are allowed in filenames, + #and returns the result + + my ($str) = @_; + + $str =~ tr/\//-/; # translate / to - + $str =~ tr/\x2f/_/; + $str =~ s/"/''/g; + + return $str; +} + +############################################################################### + + +1; +# diff --git a/scripts/gdimport.pl b/scripts/gdimport.pl new file mode 100755 index 0000000..6c8f548 --- /dev/null +++ b/scripts/gdimport.pl @@ -0,0 +1,254 @@ +#!/usr/bin/perl + +################################################## +# +# GiantDisc mp3 Jukebox +# +# © 2000, Rolf Brugger +# +################################################## + + +# Import script for mp3 tracks. +# +# Non interactive batch import of mp3 tracks. Mp3 informations (title, artist, etc) +# are read from the id3-tags. Well defined id3-tags are mandatory for a +# inclusion of mp3 tracks and a proper update of the database. + + +use lib '/home/andi/muggle/import'; +use gdparams; +use gdgentools; +use gddb; +use DBI; +use Cwd; # get current working dir + +### main + #print ("Args are: @ARGV \n"); + if ($#ARGV < 0){ + print "\nusage: gdimport.pl [options] audiofiles... \n"; + print "\nimports the specified audio files into the GiantDisc system.\n"; + print "It reads the associated metadata (id3/vorbis tags) of each file and \n"; + print "creates a track record for each of it. \n"; + print "Other than in GD, files are not copied!!\n"; + print "\n"; + print "Currently supported file formats: mp3, ogg vorbis, flac.\n"; + print "\n"; + print "Instead of a set of audio files a directory can be specified. In\n"; + print "this case, all audio tracks in the directory are imported. This is\n"; + print "particularly useful to import a bunch of albums with the following\n"; + print "command typed in the shell:\n"; + print "\n"; + print " find . -type d -print -exec gdimport.pl -a {} \\;\n"; + print "\n"; + print "Options:\n"; + print " -t Test. Just display what would be done, whithout changing\n"; + print " the database or copying/moving/linking files\n"; + print " It is recommended to test an import session before really doing it\n"; + print "\n"; + print " -a Interpret the audio files as tracks of an album. An album record\n"; + print " is created and the tracks are imported as usual.\n"; + print " Note: - The first audio file must have a valid album metadata tag\n"; + print " (i.e. id3 tag) to get the album title.\n"; + print " - only one album can be imported at a time\n"; + print " - default values: rating=-, type=1, source=CD.\n"; + print "\n"; + print " -i cddbid importdir\n"; + print " Import all jpg images of the directory 'importdir' and associate\n"; + print " them to the album specified by 'cddbid' (include prefix '0x', 'xx'!). \n"; + print "\n"; + print "\n"; + exit; + } + + #exit; + ### Init constants/variables + my ($fileformat, $cddbid); + my $makealbum = 0; + my $album_created = 0; + my $linkfiles = 1; + my $movefiles = 0; + my $moveimages = 0; + my $test = 0; + my $tracknb = 1; + my ($dbh, $trifile); + my $base = gdparams::gdbase(); # music files base + + ### Open database connection + print ("### Open database connection\n"); + $dbh = DBI->connect("DBI:mysql:GiantDisc", "music", undef) + or die "unable to connect to GiantDisc db"; + + ### init other variables + my $fileid = gdgentools::last_imported_tracknb($dbh); + my @trackrow; + + + ### extract all options first + my $shifted=1; + while ($shifted){ + if ($ARGV[0] eq "-a"){ + print "set option make album\n"; + $makealbum = 1; + shift @ARGV; + } + elsif ($ARGV[0] eq "-l"){ + print "set option link files\n"; + $linkfiles = 1; + shift @ARGV; + } + elsif ($ARGV[0] eq "-m"){ + print "set option move files\n"; + $movefiles = 1; + shift @ARGV; + } + elsif ($ARGV[0] eq "-i"){ + print "import booklet images\n"; + $moveimages = 1; + shift @ARGV; + $cddbid = shift @ARGV; + } + elsif ($ARGV[0] eq "-t"){ + print "set option TEST\n"; + $test = 1; + shift @ARGV; + } + else { + $shifted = 0; + } + } + + if ($moveimages){ + $dirname = $ARGV[0]; + if (-d $dirname){ + # ok. It's a directory + gdgentools::import_cover_images($dbh, $dirname, $cddbid, $test); + } + else{ + print "Error importing images: '$dirname' is not a directory\n"; + } + exit; + } + + + my @musicfiles; + my $dirname=""; + ### Is the first file argument a directory? + if (-d $ARGV[0]){ + # It's a directory -> read the audio filenames in it + $dirname = $ARGV[0]; + opendir CURDIR, $dirname; + @musicfiles = grep /[\.f](mp3$)|(ogg$)|(flac$)/, readdir CURDIR; # match .mp3, .ogg, flac -- Thanks to Merlot! +# @musicfiles = grep /[\.f][Mmol][Ppga][c3g]$/, readdir CURDIR; # match .mp3, .ogg, flac + @musicfiles = sort @musicfiles; + closedir CURDIR; + chdir $dirname; + } + else{ + # the remaining arguments seem to be audio filenames + @musicfiles = @ARGV; + } + + ### Loop through audio files + while ($curfile = shift @musicfiles){ + $fileformat = gdgentools::audio_filetype($curfile); + print "Importing $fileformat file: $curfile\n"; + $fileid += 1; +# $audiofile = sprintf("trxx%08ld.%s", $fileid, $fileformat); + $audiofile = $dirname ."/". $curfile; + + # get info from metatags + $trackrow->{title} = gdgentools::audiofile_title($curfile); + $trackrow->{artist} = gdgentools::audiofile_artist($curfile); + $trackrow->{genre2} = ""; + $trackrow->{year} = gdgentools::audiofile_year($curfile); + $trackrow->{lang} = "-"; + $trackrow->{type} = 1; # medium + $trackrow->{rating} = 0; + $trackrow->{length} = gdgentools::audiofile_lengthsec($curfile); + $trackrow->{source} = 0; # CD + if(!$album_created){ + if($makealbum && !$album_created){ + $trackrow->{sourceid} = sprintf("%08ld", $fileid); # fake CDDB-ID + $albumtitle = gdgentools::audiofile_album($curfile); + if (length($albumtitle)<1){ + $albumtitle = $dirname; + } + } + else{ + $trackrow->{sourceid} = ""; # no CDDB-ID available + } + } + $trackrow->{tracknb} = gdgentools::audiofile_tracknumber($curfile, $tracknb); + $trackrow->{mp3file} = $audiofile; + $trackrow->{condition} = 0; # OK + $trackrow->{voladjust} = 0; + $trackrow->{lengthfrm} = 0; + $trackrow->{startfrm} = 0; + $trackrow->{bpm} = 0; + $trackrow->{bitrate} = $fileformat." ".gdgentools::audiofile_bitrate($curfile); + $trackrow->{created} = ""; + $trackrow->{modified} = ""; + $trackrow->{id} = ""; # a new one will be automatically generated + + # get genre if available and translate to gd-genre + $trackrow->{genre1} = gdgentools::audiofile_genre($dbh, $curfile); + + if (length($trackrow->{title})>0 && length($trackrow->{artist})>0){ + ### create track record + + print "Insert track in database:$trackrow->{artist} - $trackrow->{title} \n"; + if (!$test){ + if (-e $curfile){ + $trid = gddb::insert_track_record($dbh, + $trackrow->{artist},$trackrow->{title}, + $trackrow->{genre1},$trackrow->{genre2},$trackrow->{year}, + $trackrow->{lang},$trackrow->{type},$trackrow->{rating}, + $trackrow->{length},$trackrow->{source},$trackrow->{sourceid}, + $trackrow->{tracknb},$trackrow->{mp3file}, + $trackrow->{condition},$trackrow->{voladjust}, + $trackrow->{lengthfrm},$trackrow->{startfrm},$trackrow->{bpm}, + $trackrow->{bitrate}, + $trackrow->{created},$trackrow->{id}); + } + else{ + print "Error: file ".$trackrow->{mp3file}." dows not exist. Skipping\n"; + } + } + else{ + print "TEST>".join ':', " art",$trackrow->{artist}," tit",$trackrow->{title}, ," gen1",$trackrow->{genre1}," gen2",$trackrow->{genre2}," year",$trackrow->{year}, " lang",$trackrow->{lang}," tp",$trackrow->{type}," ratng",$trackrow->{rating}, " len",$trackrow->{length}," src",$trackrow->{source}," cddbid",$trackrow->{sourceid}, " trnb",$trackrow->{tracknb}," file",$trackrow->{mp3file}, " cond",$trackrow->{condition}," vol",$trackrow->{voladjust}, " lenfrm",$trackrow->{lengthfrm}," strt",$trackrow->{startfrm}," bpm",$trackrow->{bpm}, " bitrate",$trackrow->{bitrate}, " created",$trackrow->{created}," id",$trackrow->{id},"\n\n"; + } + + ### create album record + if ($makealbum && !$album_created && length($albumtitle)>0){ + print "Insert album in database:$trackrow->{artist} - $albumtitle \n"; + if (!$test){ + $dbh->do("INSERT INTO album (artist,title,modified,cddbid) " + ."VALUES (".$dbh->quote($trackrow->{artist}) + .",".$dbh->quote($albumtitle) + .",CURDATE() " + .",'".$trackrow->{sourceid}."') "); + } + $album_created = 1; + } + + ### put mp3file to target directory + $tracknb++; + } + else{ + print "No title or artist available for $curfile (skipped)\n"; + } + + }#end loop + print("### close database connection\n"); + $dbh->disconnect; + + exit; + + + +############################################################################### + + + + diff --git a/scripts/gdio.pm b/scripts/gdio.pm new file mode 100755 index 0000000..0fd6e2f --- /dev/null +++ b/scripts/gdio.pm @@ -0,0 +1,349 @@ +################################################## +# +# GiantDisc mp3 Jukebox +# +# © 2000, Rolf Brugger +# +################################################## + +package gdio; + +#use lib '/home/music/bin'; + +use IO::Socket; +use strict; + +# GiantDisc IO routines + +### Constants & global variables +my $no_mode = 0; +my $rs232_mode = 1; +my $tcpip_srv_mode = 2; +my $tcpip_cli_mode = 3; + +my $commMode = $no_mode; + + +### Serial communication parameters: +my $serdev; +my $serbaud; + +### TCP/IP communication parameters +my $gdsocket; +my $connection; + +undef $connection; + +################################################################# +### initialization routines: + +sub serio_init{ + ### Init serial port + my $logtarget; + ($logtarget, $serdev, $serbaud) = @_; + + print("### Init serial port $serdev at $serbaud bps\n"); + + # Config serial port: + # - disable echo + # - enable hardware flow control + system("stty $serbaud -echo crtscts <$serdev"); + open (SERIN, "<$serdev"); + open (SEROUT, ">$serdev"); + + ### Alternative? + #sysopen (SERIN, $serdev, O_RDRW | O_NODELAY | O_NOCTTY) + #or die "Can't open $serdev: $!"; + #my $ofh = select(SEROUT); $| = 1; + #select($ofh); + + $commMode = $rs232_mode; +} + + + +sub tcpip_srv_init{ + my ($logtarget, $tcpiphost, $tcpipport) = @_; + + print "Open socket on $tcpiphost at port $tcpipport as server\n"; + $gdsocket = new IO::Socket::INET( LocalHost => $tcpiphost, + LocalPort => $tcpipport, + Proto => 'tcp', + Listen => 1, + Reuse => 1); + + die "could not open socket: $!\n" unless $gdsocket; + + $commMode = $tcpip_srv_mode; +} + +sub tcpip_cli_init{ + my ($logtarget, $tcpiphost, $tcpipport) = @_; + + print "Open socket on $tcpiphost at port $tcpipport as client\n"; + $gdsocket = new IO::Socket::INET( PeerAddr => $tcpiphost, + PeerPort => $tcpipport, + Proto => 'tcp'); + + die "could not open socket: $!\n" unless $gdsocket; + + $commMode = $tcpip_cli_mode; +} + +################################################################# + +sub serio_getline { # returns one line + my $line = <SERIN>; + return $line; +} + +sub putline_nack { # sends one line, without waiting for acknowledge (currently not used) + my ($line)=@_; + print (SEROUT "$line\n"); +} + +sub serio_putline +### The soutine returns true, if the client does not want to receive +### more lines (acknowledged with a 'stop' command). +### +### If the routines has to wait too long for an acknowledge (>20sec), the +### transmission is aborted. Such situations could be caused by crashed +### clients or broken transmission lines. +{ + my ($line)=@_; + my $ackstring; + print (SEROUT "$line\n"); + ### wait for acknowledge (max 20 sec) + eval{ + local $SIG{ALRM} = sub { die "ACK timeout" }; # set Alarm event handler + alarm 20; # 20 sec timeout + + $ackstring=gdio::getline(); + chop($ackstring); + + alarm 0; # deactivate Alarm + }; + if ($@ and $@ =~ /ACK timeout/ ) { + print ("\nACKNOWLEDGE TIMEOUT\n\n"); + $ackstring = "s"; #abort current transmission + }; + + if($ackstring eq "s"){ + print ("\nACK-stop received\n"); + return 1; # stop transmission + } + else{ + return 0; + } +} + +################################################################# +# +# If we experienced and error reading for the socket, then +# $connection would be set to "0" and so we need to wait for the +# palm client to reconnect. +# +# Thanks to Viktor for the tcpip code snippets + +sub tcpip_srv_check_connection{ + my $timeout; + print "connection open? ... "; + if ( ! defined $connection ) { + print "no\n"; + print "Server accepting socket ... \n"; + $connection = $gdsocket->accept(); + $timeout = $connection->timeout(); + #print "Server Connection time out is [$timeout] \n"; + $timeout = $connection->timeout(60); + #print "Server Connection time out set to [$timeout] \n"; + } + else{ + print "yes\n"; + } + + return $connection; +} + +# +# If we have an error reading the input assume client has +# disconnected. We close the socket and return a "NULL" command +# +sub tcpip_srv_getline{ + print "server listening ...\n"; + my $line; + if ( $line = <$connection> ) { + #print "line recieved: [$line] \n"; + return $line; + } + else { + print "Client disconnected ...\n"; + close ($connection) ; + undef $connection; + return "NULL\n"; + } +} + +sub tcpip_cli_getline{ + print "listening ...\n"; + my $line; + if ( $line = <$gdsocket> ) { + #print "line recieved: [$line] \n"; + return $line; + } +} + + +sub tcpip_srv_getline_SINGLECHAR{ ### just for tests ... + print "server listening ...\n"; + my $line=""; + my $cbuf; + while ( read $connection, $cbuf, 1 ) { + print "char recieved: [$cbuf] \n"; + $line .= $cbuf; + last if ($cbuf eq "\n"); + } + print "line recieved: [$line] \n"; + return $line; +} + + +sub tcpip_srv_putline{ +# There is currently a problem with this routine. +# The Palm client should be able to interrupt the transmission, if too many +# lines are sent to it. When the client shuts down the socket, the server does +# not directly recognize this. The 'if (print $connection $line."\n" )' is never +# false, unless the Palm is turned off. +# This has probably to do with the fact, that there is already sent data in a +# buffer? +# +# The current workaround is, that the Palm receives the supernumerous lines, but +# doesn't store them. Not very efficient ... :-( + + my ($line)=@_; + #print "Sending to socket: \"$line\"\n"; +#if (defined($connection->connected())){ +#print "WE ARE CONNECTED\n";} +#else{ +#print "WE ARE _NOT_ CONNECTED\n";} + + if (print $connection $line."\n" ) { + return 0; #don't stop + } + else{ + print "Client disconnected ...\n"; + close ($connection) ; + undef $connection; + return 1; #stop transmission + } +} + + +sub tcpip_cli_putline_NOACK{ + my ($line)=@_; + #print "Sending to socket: \"$line\"\n"; + + if (print $gdsocket $line."\n" ) { + return 0; #don't stop + } + return 0; +} +sub tcpip_cli_putline +{ + my ($line)=@_; + my $ackstring; + print $gdsocket $line."\n"; + ### wait for acknowledge (max 20 sec) + eval{ + local $SIG{ALRM} = sub { die "ACK timeout" }; # set Alarm event handler + alarm 20; # 20 sec timeout + + $ackstring=<$gdsocket>; + chop($ackstring); + + alarm 0; # deactivate Alarm + }; + if ($@ and $@ =~ /ACK timeout/ ) { + print ("\nACKNOWLEDGE TIMEOUT\n\n"); + $ackstring = "s"; #abort current transmission + }; + + if($ackstring eq "s"){ + print ("\nACK-stop received\n"); + return 1; # stop transmission + } + else{ + return 0; + } +} + + + +#sub close_connection +#{ +# print "Closing Connection - $connection \n"; +# close($connection) +#} + +################################################################# +### public routines: + +sub check_connection +{ + if ($commMode == $rs232_mode){ + return 1; + } + elsif ($commMode == $tcpip_srv_mode){ + return tcpip_srv_check_connection(); + } + elsif ($commMode == $tcpip_cli_mode){ + return 1; + } + else{ + print "Error: unknown communication mode\n"; + exit; + } +} + +sub putline +{ + if ($commMode == $rs232_mode){ + return serio_putline(@_); + } + elsif ($commMode == $tcpip_srv_mode){ + return tcpip_srv_putline(@_); + } + elsif ($commMode == $tcpip_cli_mode){ + return tcpip_cli_putline(@_); + } + else{ + print "Error: unknown communication mode\n"; + exit; + } +} + +sub getline +{ + if ($commMode == $rs232_mode){ + return serio_getline(); + } + elsif ($commMode == $tcpip_srv_mode){ + return tcpip_srv_getline(); + } + elsif ($commMode == $tcpip_cli_mode){ + return tcpip_cli_getline(); + } + else{ + print "Error: unknown communication mode\n"; + exit; + } +} + + +################################################################# + +END{ + print ("io modul finished\n"); +} + +# +1; diff --git a/scripts/gdparams.pm b/scripts/gdparams.pm new file mode 100755 index 0000000..35aa319 --- /dev/null +++ b/scripts/gdparams.pm @@ -0,0 +1,336 @@ +################################################## +# +# GiantDisc mp3 Jukebox +# +# © 2000, Rolf Brugger +# +################################################## + +package gdparams; + +#use lib '/usr/local/bin'; +use strict; +use Getopt::Long; + +my @mp3dirs; + +############################################################ +### Global variables (accessible from anywhere) + +# IMPORTANT ERROR! all modules refer to gdparms::varname instead +# of gdparams::varname +# ... however, it works ... I just don't know why :-/ + + +#my ($dbhost, $shutdowncmd, $extmp3player, $defrecbitrate, +# $systemonline); + + +### Global constants +my $minfreehdspace = 1000; # minimal space required for a directory, + # that it can be used to record and + # compress a cd (in MB) + + +############################################################ +### + +sub get_configfile_params{ + ### Parameters: Call-by-reference + + my ($dbhost, + $commmode, + $serialdevice, $serialspeed, + $tcpiphost, $tcpipport, + $playerhost, $playertype, $snddevice, + $playerapp, $mp3playerparams, $oggplayerparams, $ptlogger, + $mp3encoder, $logtarget, $shutdowncmd, + $extmp3player, $defrecbitrate, $systemonline + ) = @_; + + + ### Read configuration file im home directory + open (CONF, "< ".gdbase()."/.gdconfig") + or die "Error: could not open configuration file .gdconfig\n"; + + my $line; + while(<CONF>){ + $line = $_; + chop $line; + $line =~ tr/\r/ /; + # \w word-char + # \w non-word-char + # \s whitespace-char + # \S non-whitespace-char + + if($line =~ m/^dbhost.*=\s*(\S+)/i ){ + $$dbhost = $1; + } + + if($line =~ m/^commmode.*=\s*([0-9]+)/i ){ + $$commmode = $1; + } + + if($line =~ m/^serialdevice.*=\s*([\w\/]+)/i ){ + $$serialdevice = $1; + } + + if($line =~ m/^serialspeed.*=\s*([0-9]+)/i ){ + $$serialspeed = $1; + } + + if($line =~ m/^tcpiphost.*=\s*(\S+)/i ){ + $$tcpiphost = $1; + } + + if($line =~ m/^tcpipport.*=\s*([0-9]+)/i ){ + $$tcpipport = $1; + } + + if($line =~ m/^playerhost.*=\s*(\S+)/i ){ + $$playerhost = $1; + } + + if($line =~ m/^playertype.*=\s*([0-9]+)/i ){ + $$playertype = $1; + } + + if($line =~ m/^playerapp.*=\s*(\S+)/i ){ + $$playerapp = $1; + } + + if($line =~ m/^playerparams.*=\s*(.*)$/i ){ # match anything to end of line + print "\nWARNING: as of v1.20 the option 'playerparams' has been replaced by\n"; + print " 'mp3playerparams' and 'oggplayerparams'. Update .gdconfig accordingly\n\n"; + #$$playerparams = $1; + } + if($line =~ m/^mp3playerparams.*=\s*(.*)$/i ){ # match anything to end of line + $$mp3playerparams = $1; + } + if($line =~ m/^oggplayerparams.*=\s*(.*)$/i ){ # match anything to end of line + $$oggplayerparams = $1; + } + + if($line =~ m/^shutdowncmd.*=\s*(.*)$/i ){ # match anything to end of line + $$shutdowncmd = $1; + } + + if($line =~ m/^extmp3player.*=\s*(.*)$/i ){ # match anything to end of line + $$extmp3player = $1; + } + + if($line =~ m/^sounddevice.*=\s*(\S+)/i ){ + $$snddevice = $1; + } + + if($line =~ m/^mp3encoder.*=\s*(\w+)/i ){ + $$mp3encoder = $1; + } + + if($line =~ m/^logtarget.*=\s*(\w+)/i ){ + if ($1 eq "stdout" || $1 eq "logfile" || $1 eq "devnull"){ + $$logtarget = $1; + } + } + + if($line =~ m/^ptlogger.*=\s*(\S+)/i ){ + $$ptlogger = $1; + } + + if($line =~ m/^defrecbitrate.*=\s*(.+)/i ){ # match anything to end of line + $$defrecbitrate = $1; + } + + if($line =~ m/^systemonline.*=\s*([0-9])/i ){ + $$systemonline = $1; + } + + } + + close (CONF); +} + + +############################################################ +### + +sub get_otherclients_params{ + ### Parameters: Call-by-reference + + my ($keymap # type: reference to an empty hash + )= @_; + + + ### Read configuration file im home directory + open (CONF, "< ".gdbase()."/.gdconfig") + or die "Error: could not open configuration file .gdconfig\n"; + + my $line; + while(<CONF>){ + $line = $_; + chop $line; + $line =~ tr/\r/ /; + # \w word-char + # \w non-word-char + # \s whitespace-char + # \S non-whitespace-char + + if($line =~ m/^keymap.*=\s*(\S+)\s*-\s*(\S+)\s*$/i ){ + print ("keymap: key: $1, val: $2\n"); + $$keymap{$1} = $2; + } + + + } + + close (CONF); +} + + +############################################################ +### translate logtarget string to integer +sub logtarget_to_int{ + my ($logtargetstr) = @_; + if ($logtargetstr eq "devnull") {return 0;} + if ($logtargetstr eq "logfile") {return 1;} + if ($logtargetstr eq "stdout" ) {return 2;} + return 2; # default +} + + +############################################################ +### + +sub get_commandline_params{ + + my ($dbhost, + $commmode, + $serialdevice, $serialspeed, + $tcpiphost, $tcpipport, + $playerhost, $playertype, $snddevice, + $playerapp, + $mp3playerparams, $oggplayerparams, + $ptlogger, + $mp3encoder, $logtarget) = @_; + # ARGV passed implicitly + + + my $help; + $Getopt::Long::autoabbrev=1; + GetOptions( + "help" => \$help, + "dbhost:s" => $dbhost, # $dbhost is already a reference + "commmode:i" => $commmode, + "serialdevice:s" => $serialdevice, + "serialspeed:i" => $serialspeed, + "tcpiphost:s" => $tcpiphost, + "tcpipport:i" => $tcpipport, + "playertype:i" => $playertype, + "playerhost:s" => $playerhost, + "sounddevice:s" => $snddevice, + "mp3playerparams=s" => $mp3playerparams, + "oggplayerparams=s" => $oggplayerparams, + "ptlogger:s" => $ptlogger, + "mp3encoder:s" => $mp3encoder, + "logtarget:s" => $logtarget + ); + + if ($help){ + print <<EOF; +gdd.pl: GiantDisc Server Script +Option possible values + +--dbhost (string) address or hostname of mysql database server + Default value: localhost + +--commmode (number) Communication mode between Palm and server. + 1: serial line RS232 + 2: generic TCP/IP (server - accepts incoming connections) + 3: generic TCP/IP (client - connects to server) + Default value: 1 + +--serialdevice (string) Device, where Palm is connected to. + Only used if --commmode 1 + Default value: /dev/ttyS0 + +--serialspeed (number) Communication speed over serial line in + Bits per second. + Only used if --commmode 1 + Default value: 19200 + +--tcpiphost (string) Host, that accepts socket connections. + Only used if --commmode 2 or 3 + Default value: localhost + +--tcpipport (number) Port used for TCP/IP communication mode. + Only used if --commmode 2 or 3 + Default value: 26468 + +--playertype (number) main audio out device type + 0: soundcard (first audio channel) + 20: network attached audio streaming device + Default value: 0 + +--playerhost (string) address or hostname where the audio stream + is sent to. + playertype 0: host of soundcard (not used) + playertype 20: audio stream decoder host + Default value: localhost + +--sounddevice (string) Device where audio stream is sent to. + playertype 0: soundcard device (not used) + playertype 20: port number of TCP stream + Default value: 2020 + +--mp3playerparams (string) Optional parameters to the mp3 or ogg +--oggplayerparams (string) decoder. Example: "--buffer 1024" to + increase the audio output buffer size of + "mpg123". + Default value: + +--ptlogger (string) The application that updates at least + once per second the playtime of the + currently played track (needed by Palm + to display the playtime). + Default value: gdplatimefilter + +--mp3encoder lame, Application to be used to encode ripped + notlame, wav files into mp3 format. + l3enc Default value: lame + +--logtarget stdout, Target, where log-messages of the + logfile, server script should be sent to. + or devnull 'stdout' sends to standard out, 'logfile' + sends to a log file in ~music/tmp and + devnull supresses all logging. + Default value: stdout + +EOF + exit 0; + } +} + + +############################################################ +### + +sub gdbase{ # returns base path of gd files + # The path must not end with a slash! + return "/home/music"; +} + +sub mp3dirs{ # returns list of mp3 directories + # Scans the music directory for 2-digit directories + + opendir MUSICDIR, gdbase() or die "ERROR: can't scan music directory\n"; + @mp3dirs = grep /^[0-9][0-9]$/ , readdir MUSICDIR; + closedir MUSICDIR; + #print "MP3dirs: ",@mp3dirs, "\n"; + return ( @mp3dirs ); +} + + + + +1; +# diff --git a/scripts/gdserv.pm b/scripts/gdserv.pm new file mode 100755 index 0000000..4dbd3fe --- /dev/null +++ b/scripts/gdserv.pm @@ -0,0 +1,3305 @@ +################################################## +# +# GiantDisc mp3 Jukebox +# +# © 2000, Rolf Brugger +# +################################################## + +package gdserv; + +use lib '/home/andi/muggle/bin'; +use gdio; +use myhash; # incremental hash calculation +use gddb; # db modification routines +use gdparams; # global variables, constants and parameters +use gdgentools; # general tools +use gdupdate; +use gdsoundcard; + +use DBI; +use strict; + +use FileHandle; # to set auto flush on pipe +use File::Basename; + + +### Init constants +my $shrtFieldLen = 25; + +my $pl_list = 0; # play list +my $rp_list = 1; # rip list +my $co_list = 2; # compression list +my $cr_list = 3; # cd recording list + + +# player ouput constants +my $audio0 = 0; +my $audio1 = 1; +my $speaker = 10; +my $monitor = 11; + +# Player Types +my $soundcard0 = 0; # main sound output of first soundcard +my $soundcard1 = 1; # alternative sound output on second s9undcard (not supported) +my $mp3streamer = 20; # mp3 streamer (barix exstreamer) + +# Audio out channel +my $audchannel0 = 0; +my $audchannel1 = 1; + +# Currently, there is only audio channel 0 supported, and it can be mapped to +# either $soundcard0 or $mp3streamer + + + + + + + + + + + + +### Global variables +my $dbhost; # IP of database host (better use var in gdparams!?) +my $dbh; +my $playerid; +#my $playertype; replaced by audiochannel + +my $rippipe_open=0; + +my $random_result_table; #gobal variable that holds random result list +my $random_result_type; #gobal variable: what's in the table? + + +BEGIN{ +} + +############################################################ + +# Initialization +sub init_server{ + my $logtarget; + ($dbhost, $logtarget) = @_; + + ### Open database connection + if($logtarget==2){ #stdout + print ("### Open database connection\n"); + } + + #print "dbhost is: $dbhost\n"; + $dbh = DBI->connect("DBI:mysql:GiantDisc:".$dbhost, "music", undef) + or die "unable to connect to GiantDisc db"; + + + if (gdupdate::db_check_update_132($dbh)){ + print "\n Update to database structure version 1.32 required\n"; + print " please run command 'gdupdatedb.pl'\n\n"; + exit; + } + + ### fix records (bug in recording routines prior to version 0.92) + #gdupdate::fix_leading_slash_bug($dbh); + + ### UPDATE DATABASE to new versions +# gdupdate::db_update_094($dbh); +# gdupdate::db_update_095($dbh); +# gdupdate::db_update_096($dbh); +# gdupdate::db_update_097($dbh); +# gdupdate::db_update_111($dbh); +# gdupdate::db_update_112($dbh); +# gdupdate::db_update_114($dbh); +# gdupdate::db_update_131($dbh); +} + + +sub init_player{ +# Checks, if a player record with the same ipaddr and uichannel exists. +# If it doesn't exist, a new record is created. +# It returns the new generated player-ID +# +# +# + my ($ipaddr, $uichannel, $logtarget, $cdripper, + $mp3encoder, $cdromdev, $cdrwdev) = @_; + $playerid = 0; #default + my $sth = $dbh->prepare( + "SELECT * FROM player " + ."WHERE ipaddr='$ipaddr' AND uichannel='$uichannel'" ); + $sth->execute; + + my $row; + if($row = $sth->fetchrow_hashref){ + # matching player record found + $playerid = $row->{id}; + } + else{ + # get highest id in db + my $sth2 = $dbh->prepare( + "SELECT * FROM player ORDER BY id DESC LIMIT 1" ); + $sth2->execute; + if($row = $sth2->fetchrow_hashref){ + # matching player record found + $playerid = $row->{id}+1; + } + #else playerid has default value 0 + $sth2->finish; + + ### Create player record + my $retval = $dbh->do("INSERT INTO player SET " + ."ipaddr ='$ipaddr', " + ."uichannel ='$uichannel', " + ."id = $playerid, " + ."logtarget = $logtarget, " + ."cdripper ='$cdripper', " + ."mp3encoder='$mp3encoder', " + ."cdromdev ='$cdromdev', " + ."cdrwdev ='$cdrwdev'"); + } + $sth->finish; + + ### delete all tracklists (riplist, burnlist and comprlist) of this player + $dbh->do("DELETE FROM tracklistitem WHERE playerid=$playerid AND listtype=$rp_list"); #riplist + $dbh->do("DELETE FROM tracklistitem WHERE playerid=$playerid AND listtype=$co_list"); #compressionlist + $dbh->do("DELETE FROM tracklistitem WHERE playerid=$playerid AND listtype=$cr_list"); #cdrecordlist + + return $playerid; +} + + +sub init_playerstate{ +# +# global var: playerid + + my ($playerhost, $snddevice, $playerapp, $ptlogger, $playertype); + my ($mp3playerparams, $oggplayerparams); + ($playerhost, $playertype, $snddevice, $playerapp, $mp3playerparams, $oggplayerparams, $ptlogger) = @_; + my $playerparams = join '\t', $mp3playerparams, $oggplayerparams; + my $audiochannel = $audchannel0; + gdgentools::pll_new_playstate($dbh, $playerid, $audiochannel, + $playertype, $snddevice, $playerapp, $playerparams, $ptlogger); + + ### create message queue for this player-id/audio-channel + gdgentools::plmsg_newqueue($playerid, $audiochannel); + + ### init soundcard driver + gdsoundcard::sndc_init($playertype, $playerhost, $audiochannel); +} + + + +sub open_rippipe{ + ### Open recording pipe + open(RIPPIPE, "| gdripcd.pl $dbhost $playerid $audchannel0") + or die "can't fork RIP-PIPE\n"; + autoflush RIPPIPE 1; + $rippipe_open=1; +} + +sub close_rippipe{ + ### close recording pipe + close(RIPPIPE); + $rippipe_open=0; +} + +############################################################ +### TOOL ROUTINES ### +############################################################ + +############################################################ + +sub truncstr{ +### truncstr (string, length, noellipsis) + # If the string is longer than 'length' characters, it is truncated and + # '..' is appended => max length is 'length'+2 + if (length($_[0]) > $_[1]){ + $_[0] = substr ($_[0], 0, $_[1]); + if(!$_[2]){ # add ellipsis + $_[0] .= ".."; + } + } +} + + +############################################################ +### DB CREATION & UPDATE ### +############################################################ + +############################################################ +### Creates/updates an album record +sub replace_album_record{ + my ($artist,$title,$cddbid) = @_; + + my $base = gdparams::gdbase(); + + if(length($artist)==0){$artist="-";}; + if(length($title)==0) {$title="-";}; + if(length($cddbid)==0){print("ERROR: no CDDB-ID defined!\n");}; + my $sqlcmd = + "REPLACE INTO album " + ."(artist,title,cddbid,modified) " + ."VALUES " + ."(".$dbh->quote($artist) + .",".$dbh->quote($title) + .",'$cddbid', CURDATE()) "; + + #print("$sqlcmd \n"); + $dbh->do($sqlcmd); +} + + +############################################################ +### +sub record_cd_track{ + my ($artist,$title,$genre1,$genre2,$year, + $lang,$type,$rating,$length,$source,$sourceid, + $tracknb,$audiofile,$condition,$voladjust, #$lengthfrm,$startfrm,$bpm, + $created,$id,$bitrate + ) = @_; + + my $base = gdparams::gdbase(); + + ### Get an audio directory with enough space left (1GB) + my $ripdir=gdgentools::get_ripdirectory($gdparams::minfreehdspace); #1000 MBytes should be available + + my $trid; + ### create a new track record + if($ripdir ne ""){ + my ($audiofmt, $audioparam) = gdgentools::bitrate_str_to_format_param($bitrate); # split "mp3 128" + $trid = gddb::insert_track_record($dbh,$artist,$title,$genre1,$genre2,$year, + $lang,$type,$rating,$length,$source,$sourceid, + $tracknb,"tr0x".$sourceid."-".$tracknb.".".$audiofmt, + $condition,$voladjust,0,0,0, #$lengthfrm,$startfrm,$bpm, + $bitrate, + $created,$id); + + unlink glob("$base/??/tr0x".$sourceid."-".$tracknb.".".$audiofmt); # delete old audio file + ### send rip command to pipe + if(!$rippipe_open){open_rippipe();} + print ("printing to RIPPIPE: tr=$tracknb id=$trid " + ."br=$bitrate len=$length " + ."dir=$base/$ripdir " + ."file=tr0x$sourceid art=$artist tit=$title\n"); + gdgentools::tracklist_append_list($dbh, $playerid, $rp_list, $trid); + ### action is "rip" + print (RIPPIPE "rip\t$tracknb\t$trid\t$bitrate\t$length\t" + ."$base/$ripdir\ttr0x$sourceid\t$artist\t$title\n"); + } + else{ + print("Not enough space left on disc \n"); + } +} + + +############################################################ +### +sub import_audio_track{ +# creates a new record and moves the specified audio-file to a +# music directory which has enough space left + + my ($artist,$title,$genre1,$genre2,$year, + $lang,$type,$rating,$length,$source,$sourceid, + $tracknb,$audiofile,$condition,$voladjust,#$lengthfrm,$startfrm,$bpm, + $created,$id,$bitrate + ) = @_; + + my $base = gdparams::gdbase(); + + ### Get an audio directory with enough space left (1GB) + my $ripdir=gdgentools::get_ripdirectory($gdparams::minfreehdspace); + + my $trid; + ### create a new track record + if($ripdir ne ""){ + ### put audio-file in music directory + + my $origaudiofile = readlink "$base/inbox/$audiofile"; + + if(!$rippipe_open){open_rippipe();} + + ### action is "move" + #format: "move, sourcefile, targetfile, linkname + print ("printing to RIPPIPE: move\t$origaudiofile\t$base/$ripdir/$audiofile\t$base/inbox/$audiofile\n"); + print (RIPPIPE "move\t$origaudiofile\t$base/$ripdir/$audiofile\t$base/inbox/$audiofile\n"); + + ### create track record + $trid = gddb::insert_track_record($dbh,$artist,$title,$genre1,$genre2,$year, + $lang,$type,$rating,$length,$source,$sourceid, + $tracknb,$audiofile,$condition,$voladjust, + 0,0,0, #$lengthfrm,$startfrm,$bpm, + $bitrate, + $created,$id); + + } + else{ + print("Not enough space left on disc \n"); + } +} + + + +############################################################ +### +# these variables are st by the routine, that sends track details to the +# client. The value in there gives a hint, if the frequencies should be recalculated +my ($trk_last_id, $trk_last_lang, $trk_last_genre1, $trk_last_genre2); + +sub update_track_record{ + my ($artist,$title,$genre1,$genre2,$year, + $lang,$type,$rating,$length,$source,$sourceid, + $tracknb,$audiofile,$condition,$voladjust,#$lengthfrm,$startfrm,$bpm, + $created,$id,$bitrate + ) = @_; + + my ($trid, $audiofpath); + + ### always recalculate bitrate and track length + $audiofpath = gdgentools::get_full_audiofile_path($audiofile); + if(gdgentools::is_mp3stream($audiofile) || (length($audiofpath)>0)){ + if(length($audiofpath)>0){ ## real mp3 file exists + $bitrate = gdgentools::get_bitrate_str("$audiofpath"); + $length = gdgentools::audiofile_lengthsec("$audiofpath"); + } + else{ + $length = 0; + } + + #print("UPDATING: $artist,$title,$genre1,$genre2,$year," + # ."$lang,$type,$rating,$length,$source,$sourceid," + # ."$tracknb,$audiofile,$condition,$voladjust,$created,$id\n"); + $trid = gddb::insert_track_record($dbh,$artist,$title,$genre1,$genre2,$year, + $lang,$type,$rating,$length,$source,$sourceid, + $tracknb,$audiofile,$condition,$voladjust, + 0,0,0,#$lengthfrm,$startfrm,$bpm, + $bitrate, + $created,$id); + + if ( $id != $trk_last_id + || $lang ne $trk_last_lang){ ### language changed? + system("killall -q gdtablefreq.pl"); + system("nice gdtablefreq.pl --dbhost $dbhost --trklanguage&"); +# system("nice gdtablefreq.pl --dbhost $dbhost --trklanguage --verbose&"); + } + if ( $id != $trk_last_id + || $genre1 ne $trk_last_genre1 + || $genre2 ne $trk_last_genre2){ ### genre changed? + # recalculate genre frequencies + system("killall -q gdtablefreq.pl"); + if (length($sourceid)>=8){ + system("nice gdtablefreq.pl --dbhost $dbhost --trkgenre --albgenre $sourceid&"); +# system("nice gdtablefreq.pl --dbhost $dbhost --trkgenre --albgenre $sourceid --verbose&"); + } + else{ + system("nice gdtablefreq.pl --dbhost $dbhost --trkgenre&"); +# system("nice gdtablefreq.pl --dbhost $dbhost --trkgenre --verbose&"); + } + } + } + else{ + print("Warning: can't update record (id=$id) because the associated mp3file does not exist, and no mp3 streaming url is specified\n"); + } +} + + + +sub recalc_table_frequencies{ + system("killall -q gdtablefreq.pl"); + system("nice gdtablefreq.pl --dbhost $dbhost --trkgenre --albgenre&"); +} + +############################################################ +### QUERIES ### +############################################################ + + +############################################################ +### +sub do_tr_query{ # returns a query handle + + my $where = gddb::track_where_clause(@_); + my $order = gddb::track_order_clause(@_); + + #print("WHERE clause: $where $order\n"); + my $sth = $dbh->prepare( "SELECT * FROM tracks WHERE $where $order" ); + + my $rv = $sth->execute; + print("$rv records found\n"); + return $sth; +} + +############################################################ +### +sub do_album_query{ # does an album query and returns a query handle + ### +# EXAMPLE: +# SELECT album.* FROM tracks JOIN album +# WHERE album.cddbid=tracks.sourceid AND tracks.genre1='mdh' +# AND album.title like "%de%" +# GROUP BY album.cddbid; + +# my ($artist,$title,$genre1,$genre2,$yearfrom,$yearto, +# $lang,$type,$rating) = @_; + + my $tmpcmd; + + my $where = gddb::album_where_clause(@_); + my $order = gddb::album_order_clause(@_); + +#print "alb-order: $order\n\n"; + if (length($order)<5){ + $order = "ORDER BY album.artist"; + } + + my $sth = $dbh->prepare( +# " SELECT album.* FROM tracks JOIN album " + " SELECT album.artist AS artist, album.title AS title, " + ."album.cddbid AS cddbid, album.genre AS genre, tracks.year AS year " + ."FROM tracks JOIN album " + ."WHERE album.cddbid=tracks.sourceid AND $where " + ."GROUP BY album.cddbid $order" + ); + + $sth->execute; + return $sth; +} + +############################################################ +### +sub do_playlist_query{ # does a playlist query and returns a query handle + ### + my ($author,$title,$genre1,$genre2,$yearfrom,$yearto, + $lang,$type,$rating) = @_; + + my $where=" 1 "; # true AND ... + + ### Artist + $where .= gddb::field_where_clause("author",$author); + + ### Title + $where .= gddb::field_where_clause("title",$title); + + my $sth = $dbh->prepare( + 'SELECT * FROM playlist ' + ."WHERE $where" + ); + + $sth->execute; + return $sth; +} + +############################################################ +### +sub get_track{ # returns a query handle + my ($trackid) = @_; + + my $sth = $dbh->prepare( + 'SELECT * FROM tracks ' + ."WHERE id = $trackid" + ); + + $sth->execute; + return $sth; +} + +############################################################ +### +sub do_tracks_of_album_query{ # returns a query handle + my ($albumid) = @_; + my $sth = $dbh->prepare( + 'SELECT * FROM tracks ' + ."WHERE sourceid = \"$albumid\" ORDER BY tracknb" + ); + + $sth->execute; + return $sth; +} + +############################################################ +### +sub do_tracks_of_playlist_query{ # returns a query handle + my ($playlistid) = @_; + my $sth = $dbh->prepare( + "SELECT * FROM playlistitem " + ."WHERE playlist = $playlistid ORDER BY tracknumber" + ); + + $sth->execute; + return $sth; +} + + +############################################################ +### LISTINGS ### +############################################################ + +############################################################ +### send tracks +sub send_tracks_v1{ # send a record set to the serial line + # NEW VERSION + my ($sth) = @_; + my ($row); + my $stop=0; + while($row = $sth->fetchrow_hashref){ + $stop = send_track($row); + last if ($stop); + } + if (!$stop){ + gdio::putline ("");} # End of result + $sth->finish; +} +sub send_track{ + my ($row)= @_; + my ($stop); + truncstr($row->{artist}, $shrtFieldLen); + truncstr($row->{title}, $shrtFieldLen); + print ("$row->{id}\t"); + print ("$row->{artist}\t"); + print ("$row->{title}\t"); + print ("$row->{length}\t"); + print ("$row->{genre1}\t"); + #print ("$row->{genre2}\t"); + print ("$row->{year}\t"); + print ("$row->{type}\t"); + print ("$row->{rating}\n"); + $stop = gdio::putline("$row->{id}\t$row->{artist}\t$row->{title}\t" + ."$row->{length}\t$row->{genre1}\t" + ."$row->{year}\t$row->{type}\t$row->{rating}"); + return $stop +} +#sub send_tracks_v0{ # send a record set to the serial line +# # OLD VERSION: will be obsolete when all +# # routines are ported to 'send_tracks' +# my ($sth) = @_; +# my ($row); +# my $stop=0; +# while($row = $sth->fetchrow_hashref){ +# truncstr($row->{artist}, $shrtFieldLen); +# truncstr($row->{title}, $shrtFieldLen); +# print ("$row->{id}\t"); +# print ("$row->{artist}\t"); +# print ("$row->{title}\t"); +# print ("$row->{length}\n"); +# $stop = gdio::putline("$row->{id}\t$row->{artist}\t$row->{title}\t" +# ."$row->{length}\t$row->{tracknb}"); +# last if ($stop); +# } +# if (!$stop){ +# gdio::putline ("");} # End of result +# $sth->finish; +#} + +############################################################ +### Query tracks & List +sub query_tracks_list{ #query tracks records and list them + my $sth = do_tr_query(1, @_); # perform database query + send_tracks_v1($sth); +} + +sub query_streams_list{ #query tracks records and list them + my $sth = do_tr_query(2, @_); # perform database query + send_tracks_v1($sth); +} + +############################################################ +### Generic Query tracks & List +sub generic_track_query{ #query tracks records and list them + my ($where) = @_; + #print("WHERE clause: $where\n"); + my $sth = $dbh->prepare( "SELECT * FROM tracks $where" ); + my $rv = $sth->execute; + print("$rv records found\n"); + send_tracks_v1($sth); +} + +### Query a single tracks & List +#sub query_single_track{ #query one trackfor a given id +# my ($trackid) = @_; +# my $sth = $dbh->prepare( "SELECT * FROM tracks WHERE id='$trackid'" ); +# $sth->execute; +# send_tracks_v0($sth); +#} + + +############################################################ +### send artists +sub send_artist_line_v1{ # send a single record to the serial line + my ($artist) = @_; + my $stop=0; + print ("\t"); # send an empty ID + truncstr($artist, 10+$shrtFieldLen, 1); # dont send ellipsis + print ("$artist\n"); + $stop = gdio::putline("\t$artist"); + return $stop; +} + +#sub send_artist_line_v0{ # send a single record to the serial line +# my ($artist) = @_; +# my $stop=0; +# print ("ESCn\t"); # ESC n: name (=artist) +# print ("0\t"); # send an empty ID to be compatible with album record format +# truncstr($artist, 10+$shrtFieldLen, 1); # dont send ellipsis +# print ("$artist\t"); +# print (" \n"); # send an empty title to be compatible with album record format +# $stop = gdio::putline("\en\t0\t$artist\t "); +# return $stop; +#} + +############################################################ +### Query artist & List +sub query_artists_list{ #query track records and list their distinct artists + #my ($artist,$title,$genre1,$genre2,$yearfrom,$yearto, + # $lang,$type,$rating) = @_; + + ### Prepare Query + my $where = gddb::track_where_clause(1, @_); + + #print("WHERE clause: $where\n"); + + generic_artists_query( + "SELECT DISTINCT artist FROM tracks WHERE $where ORDER BY artist" ); +} + + +sub generic_artists_query{ #query track records and list their distinct artists + ### Prepare Query + my ($querystr) = @_; + my $sth = $dbh->prepare( $querystr ); + my $rv = $sth->execute; + #print("$rv artists found\n"); + ### Send results + my $row; + my $stop=0; + while($row = $sth->fetchrow_hashref){ + $stop = send_artist_line_v1($row->{artist}); + last if ($stop); + } + if (!$stop){ + gdio::putline ("");} # End of result + $sth->finish; +} + + +############################################################ +### Query albums + + +#sub query_single_album{ + # query one albums with its tracks and list them + # the album is specified by the 'albumid'. +# my ($albumid) = @_; +# my $albsth; +# $albsth = $dbh->prepare( +# " SELECT * FROM album WHERE cddbid='$albumid'"); +# $albsth->execute; +# send_albums_v0(1, $albsth); # 1: with tracks +#} + + +sub generic_album_query{ + # query one albums with its tracks and list them + # the album is specified by the 'albumid'. + my ($querystr) = @_; + my $albsth; + my $albsth = $dbh->prepare( $querystr ); + $albsth->execute; + send_albums_v1($albsth); +} + + +############################################################ +### Query albums & list +sub query_albums{ + # query albums and list them + my $albsth = do_album_query(@_); # perform database query + send_albums_v1($albsth); +} + + +############################################################ +### Query albums at random +sub query_random_albums{ + # prepares a ramdom query operation + # 1) performs an album query + # 2) selects album-ids and store them in a gobally accessible array + # 3) returns the number of albums found + my ($artist,$title,$genre1,$genre2,$yearfrom,$yearto, + $lang,$type,$rating) = @_; + my $nbRecords; + + my $where = gddb::album_where_clause(@_); +# my $where=" 1 "; # true AND ... + ### Album: Artist +# $where .= gddb::field_where_clause("album.artist",$artist); + ### Album: Title +# $where .= gddb::field_where_clause("album.title",$title); + ### Track: genre, etc ... +# $where.= gddb::attrib_where_clause($genre1,$genre2,$yearfrom,$yearto,$lang,$type,$rating); + + my $sth = $dbh->prepare( + " SELECT album.cddbid,(ASCII(album.cddbid)*0)+RAND() AS randnum " + ."FROM tracks JOIN album " + ."WHERE album.cddbid=tracks.sourceid AND $where " + ."GROUP BY album.cddbid ORDER BY randnum" + ); # ORDER BY RAND() does not work in version 3.22 + $nbRecords = $sth->execute; + + ### store result in global array + $random_result_table = $sth->fetchall_arrayref; + $random_result_type = "alb"; + # my $i; for $i( 0 .. $#{$random_result_table}){print ($random_result_table->[$i][0]."-".$random_result_table->[$i][1].",");} print ("\n"); + $sth->finish; + + ### Send number of records + print ("random album query: $nbRecords albums found\n"); + gdio::putline ("$nbRecords\n"); + gdio::putline (""); # End of result +} + + +############################################################ +### Query tracks at random +sub query_random_tracks{ + # prepares a ramdom query operation + # 1) performs a track query + # 2) selects track-ids and store them in a gobally accessible array + # 3) returns the number of tracks found + my $nbRecords; + + my $where = gddb::track_where_clause(@_); + my $sth = $dbh->prepare( + " SELECT id,(id*0)+RAND() AS randnum FROM tracks " + ."WHERE $where ORDER BY randnum" + ); # ORDER BY RAND() does not work in version 3.22 + $nbRecords = $sth->execute; + + ### store result in global array + $random_result_table = $sth->fetchall_arrayref; + $random_result_type = "trk"; +# my $i; for $i( 0 .. $#{$random_result_table}){print ($random_result_table->[$i][0]."-".$random_result_table->[$i][1].",");} print ("\n"); + $sth->finish; + + ### Send number of records + print ("random album query: $nbRecords tracks found\n"); + gdio::putline ("$nbRecords\n"); + gdio::putline (""); # End of result +} + + +############################################################ +### Query artists at random +sub query_random_artists{ + # prepares a ramdom query operation + # 1) performs a track query + # 2) selects track-ids and store them in a gobally accessible array + # 3) returns the number of tracks found + my ($artist,$title,$genre1,$genre2,$yearfrom,$yearto, + $lang,$type,$rating) = @_; + my $nbRecords; + + my $where = gddb::track_where_clause(1, @_); + my $sth = $dbh->prepare( + " SELECT DISTINCT artist FROM tracks WHERE $where" + ); # can't "ORDER BY randnum" here! + $nbRecords = $sth->execute; + + ### store result in global array + $random_result_table = $sth->fetchall_arrayref; + $random_result_type = "art"; +# my $i; for $i( 0 .. $#{$random_result_table}){print ($random_result_table->[$i][0]."-".$random_result_table->[$i][1].",");} print ("\n"); + $sth->finish; + ### randomize it - because SQL query can't do it + shuffle_random_table(); + + ### Send number of records + print ("random artist query: $nbRecords artists found\n"); + gdio::putline ("$nbRecords\n"); + gdio::putline (""); # End of result +} + + +############################################################ +### send next slice of random result table to the palm client +sub collect_ids{ + my ($position,$nbRecords,$idIdent) = @_; + my ($whereclause, $i); + $whereclause = "0"; + # if $position < $#{$random_result_table} + for $i( $position .. ($position+$nbRecords-1)){ + #print ($random_result_table->[$i][0]."\n"); + $whereclause .= " OR $idIdent='".$random_result_table->[$i][0]."'"; + } + return $whereclause; +} + +sub shuffle_random_table{ + my ($i, $j, $element); + for $i( 0 .. $#{$random_result_table}){ + $j = int rand ($#{$random_result_table}); +# print ("exchange $i and $j\n"); + $element = $random_result_table->[$i][0]; + $random_result_table->[$i][0] = $random_result_table->[$j][0]; + $random_result_table->[$j][0] = $element; + } +} + +sub get_random_result{ + # prepares a ramdom query operation + # 1) performs an album query + # 2) selects album-ids and store them in a gobally accessibe array + # 3) randomizes the order of the album-ids + # 4) returns the number of albums found + my ($position,$nbRecords) = @_; + + my ($whereclause, $sth); + if ($random_result_type eq "alb"){ + $whereclause = collect_ids ($position,$nbRecords, "tracks.sourceid=album.cddbid AND album.cddbid"); + ### Perform the query + #print("SELECT album.artist AS artist, album.title AS title, " + # ."album.cddbid AS cddbid, album.genre AS genre, tracks.year AS year " + # ."FROM tracks JOIN album " + # ."WHERE $whereclause" + # ."GROUP BY album.cddbid\n"); + + $sth = $dbh->prepare(" SELECT album.artist AS artist, album.title AS title, " + ."album.cddbid AS cddbid, album.genre AS genre, tracks.year AS year " + ."FROM tracks JOIN album " + ."WHERE $whereclause" + ."GROUP BY album.cddbid"); + $sth->execute; + send_albums_v1($sth); + $sth->finish; + } + elsif ($random_result_type eq "pll"){ + $whereclause = collect_ids ($position,$nbRecords, "id"); + ### Perform the query + print("SELECT * FROM playlist WHERE $whereclause\n"); + $sth = $dbh->prepare("SELECT * FROM playlist WHERE $whereclause"); + $sth->execute; + my $prow; + while($prow = $sth->fetchrow_hashref){ + print("$prow->{id}\t$prow->{author}\t$prow->{title}\n"); + gdio::putline("$prow->{id}\t$prow->{author}\t$prow->{title}"); + } + gdio::putline (""); # End of result + $sth->finish; + } + elsif ($random_result_type eq "trk"){ + $whereclause = collect_ids ($position,$nbRecords, "id"); + ### Perform the query + print("SELECT * FROM tracks WHERE $whereclause\n"); + $sth = $dbh->prepare("SELECT * FROM tracks WHERE $whereclause"); + $sth->execute; + send_tracks_v1($sth); + $sth->finish; + } + elsif ($random_result_type eq "art"){ + ### no additional query necessary with artists + my $i; + for $i( $position .. ($position+$nbRecords-1)){ + truncstr($random_result_table->[$i][0], 10+$shrtFieldLen); + #print ($random_result_table->[$i][0]."\n"); + send_artist_line_v1($random_result_table->[$i][0]); + } + gdio::putline (""); # End of result + } + else{ + print ("Error: random_result_type '$random_result_type' not properly initialized"); + } +} + + +############################################################ +### Query albums with or without tracks & list +#sub send_albums_v0{ +# # sends the albums (specified by the record set handle '$albsth') +# # if $withTracks is true, the according tracks are sent too +# +# my ($withTracks, $albsth) = @_; +# my ($arow, $trow); # album and track row +# my $trksth; +# my $stop=0; +# +# while($arow = $albsth->fetchrow_hashref){ +# truncstr($arow->{artist}, $shrtFieldLen); +# truncstr($arow->{title}, $shrtFieldLen); +# print ("ESCa\t"); +# print ("$arow->{cddbid}\t"); +# print ("$arow->{artist}\t"); +# print ("$arow->{title}\n"); +# # print album (preceded by ESC a) +# $stop = gdio::putline("\ea\t$arow->{cddbid}\t$arow->{artist}\t$arow->{title}"); +# +# if ($withTracks){ +# ### get tracks of the album +# my $trksth = do_tracks_of_album_query($arow->{cddbid}); +# while($trow = $trksth->fetchrow_hashref){ +# truncstr($trow->{artist}, $shrtFieldLen); +# truncstr($trow->{title}, $shrtFieldLen); +# print ("$trow->{id}\t"); +# print ("$trow->{artist}\t"); +# print ("$trow->{title}\t"); +# print ("$trow->{length}\n"); +# # print track +# $stop = gdio::putline("$trow->{id}\t$trow->{artist}\t$trow->{title}\t" +# ."$trow->{length}\t$trow->{tracknb}"); +# last if ($stop); +# } +# } +# last if ($stop); +# } +# if (!$stop){ +# gdio::putline ("");} # End of result +# $albsth->finish; +#} + +sub send_albums_v1{ + # sends the albums (specified by the record set handle '$albsth') + # if $withTracks is true, the according tracks are sent too + + my ($albsth) = @_; + my ($arow, $trow); # album and track row + my $trksth; + my $stop=0; + + while($arow = $albsth->fetchrow_hashref){ + truncstr($arow->{artist}, $shrtFieldLen); + truncstr($arow->{title}, $shrtFieldLen); + print ("$arow->{cddbid}\t"); + print ("$arow->{artist}\t"); + print ("$arow->{title}\t"); + print ("\t$arow->{genre}\t$arow->{year}\n"); # length,genre,year + # print album + $stop = gdio::putline("$arow->{cddbid}\t$arow->{artist}\t$arow->{title}\t\t$arow->{genre}\t$arow->{year}"); + last if ($stop); + } + if (!$stop){ + gdio::putline ("");} # End of result + $albsth->finish; +} + +############################################################ +### Query playlists & list +sub query_saved_playlists_list{ + # query playlists and list them + # + # List format: id, author, title + + my $pllsth = do_playlist_query(@_); # perform database query + my ($prow, $trow); # playlist and track row + my ($trksth, $trow, $irow, $plisth); + my $stop=0; + + while($prow = $pllsth->fetchrow_hashref){ + truncstr($prow->{artist}, $shrtFieldLen); + truncstr($prow->{title}, $shrtFieldLen); + print ("$prow->{id}\t"); + print ("$prow->{author}\t"); + print ("$prow->{title}\n"); + + $stop = gdio::putline("$prow->{id}\t$prow->{author}\t$prow->{title}"); + last if ($stop); + } + if (!$stop){ + gdio::putline ("");} # End of result + $pllsth->finish; +} + + + +############################################################ +### Query playlists at random +sub query_random_playlists{ + # prepares a ramdom query operation + # 1) performs an playlist query + # 2) selects playlist-ids and store them in a gobally accessible array + # 3) returns the number of playlists found + my ($artist,$title,$genre1,$genre2,$yearfrom,$yearto, + $lang,$type,$rating) = @_; + my $nbRecords; + + my $where=" 1 "; # true AND ... + + ### Artist + $where .= gddb::field_where_clause("author",$artist); + ### Title + $where .= gddb::field_where_clause("title",$title); + + my $sth = $dbh->prepare( + 'SELECT id FROM playlist ' + ."WHERE $where ORDER BY RAND()" + ); + + $nbRecords = $sth->execute; + + ### store result in global array + $random_result_table = $sth->fetchall_arrayref; + $random_result_type = "pll"; + # my $i; for $i( 0 .. $#{$random_result_table}){print ($random_result_table->[$i][0]."-".$random_result_table->[$i][1].",");} print ("\n"); + $sth->finish; + + ### Send number of records + print ("random album query: $nbRecords albums found\n"); + gdio::putline ("$nbRecords\n"); + gdio::putline (""); # End of result +} + + +sub tracks_of_saved_playlist{ + # lists tracks of a saved playlist + + my ($plid) = @_; + + my ($trksth, $irow, $plisth, $row); + #my $stop=0; + + + ### get tracks of the playlist + $plisth = do_tracks_of_playlist_query($plid); + while($irow = $plisth->fetchrow_hashref){ + $trksth = $dbh->prepare("SELECT * FROM tracks WHERE id=".$irow->{trackid}); + $trksth->execute; + if($row = $trksth->fetchrow_hashref){ + send_track($row); + } + $trksth->finish; + } + $plisth->finish; + gdio::putline (""); # End of result +} + + + +############################################################ +### Query tracks and send result to Palm client +sub simple_track_query{ + my ($query_cmd) = @_; + my ($sth,$row); + my $stop=0; + + my $sth = $dbh->prepare($query_cmd); + my $rv = $sth->execute; + print("$rv records found\n"); + + while($row = $sth->fetchrow_hashref){ + truncstr($row->{artist}, $shrtFieldLen); + truncstr($row->{title}, $shrtFieldLen); + print ("$row->{id} $row->{artist} $row->{title}\t $row->{length}\n"); + $stop = gdio::putline("$row->{id}\t$row->{artist}\t$row->{title}\t" + ."$row->{length}\t$row->{tracknb}"); + last if ($stop); + } + if (!$stop){ + gdio::putline ("");} # End of result + $sth->finish; +} + + + +############################################################ +### Play + Playlist commands ### +############################################################ + + +############################################################ +### New Playlist commands + +sub pl_play{ gdgentools::pl_play ($dbh, $playerid, $audchannel0);} +sub pl_play_at{gdgentools::pl_play_at($dbh, $playerid, $audchannel0, @_);} +sub pl_stop{ gdgentools::pl_stop ($dbh, $playerid, $audchannel0);} +sub pl_pause{ gdgentools::pl_pause ($dbh, $playerid, $audchannel0);} +sub pl_rw{ gdgentools::pl_rw ($dbh, $playerid, $audchannel0);} +sub pl_ff{ gdgentools::pl_ff ($dbh, $playerid, $audchannel0);} +sub pl_goto{ gdgentools::pl_goto ($dbh, $playerid, $audchannel0, @_);} +sub pl_prev{ gdgentools::pl_prev ($dbh, $playerid, $audchannel0);} +sub pl_next{ gdgentools::pl_next ($dbh, $playerid, $audchannel0);} + + + +sub pl_append_tracks{ +# appends tracks to the playlist file +# Parameters: list of track id's + gdgentools::tracklist_append_list($dbh, $playerid, $pl_list, @_); +} + +sub pl_new_list{ +# Sets a new playlist. First, playing is stopped, then the current playlist +# is emptied, the new playlist is written and the current track index is set +# to zero. +# An example, where this routine is used is the shuffle function (Palm +# randomizes playlist order and sends new playlist). +# Parameters: list of track id's + #my $tracklist = @_; + pl_empty(); + pl_append_tracks(@_); +} + +sub pl_empty{ +# empties (deletes) the playlist. The playstate is also reset + my $base = gdparams::gdbase(); + # stop playing + gdgentools::stop_play_processes($dbh, $playerid, $audchannel0); + # reset list+state + gdgentools::tracklist_delete($dbh, $playerid, 0); + gdgentools::pll_write_playstate($dbh, $playerid, $audchannel0, 0, "st"); + gdgentools::pll_write_playtime($dbh, $playerid, $audchannel0, 0, 0); + gdgentools::pll_write_shuffleparameters($dbh, $playerid, $audchannel0, "", ""); +} + + +sub pl_remove_one{ +# Removes the specified playlist item (index) from the playlist +# If the index corresponds to the currently played track, playing is +# stopped first. +# It the index is lower than the the index of the currently played track, +# then nothing is done. +# Parameter: index of the item to be removed + + my ($delitem) = @_; + my ($trackind, $state, $frame, $shufflestat) = gdgentools::pll_get_playstate($dbh, $playerid, $audchannel0); + if ($delitem >= $trackind){ + if (($delitem == $trackind) && $state ne "st" && $state ne "in"){ + gdgentools::stop_play_processes($dbh, $playerid, $audchannel0); + gdgentools::pll_write_playstate($dbh, $playerid, $audchannel0, $trackind, "st"); + } + + ### delete it + gdgentools::tracklist_del_item($dbh, $playerid, 0, $delitem); + } +} + + + +sub pl_reorder_one{ +# Reorders the playlist. +# The specified playlist item (src) is moved to a new location (dest) +# src and dest must be higher than the currently played track. They +# therefore don't interfere with the track playing process. + + my ($srcpos, $destpos) = @_; + ### move it + gdgentools::tracklist_reorder_item($dbh, $playerid, 0, $srcpos, $destpos); +} + + +############################################################ +### Shuffle play (query only) +sub pl_calc_shufflestats{ # query tracks and send back statistics + my $where = gddb::track_where_clause(1, @_); + my $sth = $dbh->prepare( + "SELECT SUM(length) AS playlen, COUNT(*) AS nbtracks " + ."FROM tracks WHERE $where" ); + $sth->execute; + my ($row, $nbtracks, $playlen); + if($row = $sth->fetchrow_hashref){ + $nbtracks = $row->{nbtracks}; + $playlen = $row->{playlen}; + } + else{ + print("Shuffle play: query error!\n"); + } + $sth->finish; + return ($nbtracks, $playlen); +} + + + +sub pl_check_shuffleplay{ # query tracks and send back statistics + my ($nbtracks, $playlen) = pl_calc_shufflestats(@_); + + if($nbtracks > 10){ + gdio::putline("$nbtracks tracks found. Total play length is " + . gdgentools::seconds_to_hm($playlen) + .". Play them in random order?"); + } + else{ + gdio::putline("Sorry! $nbtracks tracks are not enough to be played in shuffle mode."); + } + gdio::putline (""); +} + + + +sub pl_start_shuffleplay{ + pl_empty(); # stop playing, empty playlist and clear playstate + my ($nbtracks, $playlen) = pl_calc_shufflestats(@_); + my $shuffleparam = join ("\t",@_); ### Attention: does not join trailing empty parameters! + if (length($shuffleparam)==0){$shuffleparam ="\t\t";} ### join does not work on empty parameters -> cheat an empty parameter string! + gdgentools::pll_write_shuffleparameters($dbh, $playerid, $audchannel0, + $shuffleparam, "$nbtracks Tr [".gdgentools::seconds_to_hm($playlen)."]"); + gdgentools::pl_play($dbh, $playerid, $audchannel0); # start playing - the player script knows how to handle the shuffle play parameters +} + + +############################################################ + + +# send current playlist to the client (tracklist with ID, artist, title, length) +sub pl_reload_playlist{ + my @playlist = gdgentools::tracklist_get_all($dbh, $playerid, 0); + my ($stop, $trackid, $sth, $trackrow, $row); + + while($trackid = shift @playlist){ + $sth = $dbh->prepare("SELECT * FROM tracks WHERE id=$trackid"); + $sth->execute; + + if($row = $sth->fetchrow_hashref){ + $stop = send_track($row); +# truncstr($row->{artist}, $shrtFieldLen); +# truncstr($row->{title}, $shrtFieldLen); +# print ("$row->{id}\t$row->{artist}\t$row->{title}\t$row->{length}\n"); +# gdio::putline("$row->{id}\t$row->{artist}\t$row->{title}\t" +# ."$row->{length}\t$row->{tracknb}"); + last if ($stop); + } + $sth->finish; + } + if (!$stop){ + gdio::putline ("");} # End of result +} + + +# send current playstate to the client +sub pl_get_curr_playstate{ + # sends: pll-length, current-index, current-id, playtime, tracklength, state + use integer; + +print "get playstate for channel ".$gdgentools::audchannel1."\n"; + my ($trackind, $state, $frame, $shufflestat) = + gdgentools::pll_get_playstate($dbh, $playerid, $audchannel0); + my ($played, $total) = gdgentools::pll_get_playtime($dbh, $playerid, $audchannel0); + my @playlist = gdgentools::tracklist_get_all($dbh, $playerid, 0); + my $playlistlen = @playlist; + my $pltime = $played/gdgentools::frames_per_second(); + my $pllen = $total/gdgentools::frames_per_second(); + + #print ("Sending state: pl-len:$playlistlen ind:$trackind id:$playlist[$trackind] ptime:$pltime ptot:$pllen state:$state\n"); + gdio::putline ("$playlistlen\t$trackind\t$playlist[$trackind]\t$pltime\t$pllen\t$state\t$shufflestat"); + gdio::putline (""); # End of result +} + + + + +################################################################ +### Export Playlist: + +sub pl_export_empty{ + my $base = gdparams::gdbase(); + + unlink <$base/outbox/*>; +} + +sub pl_export_audiolinks{ +# exports the current playlist as links to mp3-files to the outbox + my $base = gdparams::gdbase(); + my ($trackid, $row, $tracknum, $trh, $trkrow, $linkname, $mp3fp); + my ($audiofmt, $audioparam); + + ### run through playlist + my $sth = $dbh->prepare( + "SELECT * FROM tracklistitem " + ."WHERE playerid=$playerid AND listtype=$pl_list " + ."ORDER BY tracknb " ); + my $nbrec = $sth->execute; + while($row = $sth->fetchrow_hashref){ + $trackid = $row->{trackid}; + + ### get track details + $trh = $dbh->prepare("SELECT * FROM tracks WHERE id=$trackid"); + $trh->execute; + if($trkrow = $trh->fetchrow_hashref){ + ####$trackid = $row->{trackid}; + ### create link + $mp3fp = `ls $base/??/$trkrow->{mp3file}`; # get full filename and path + ($audiofmt, $audioparam) = gdgentools::bitrate_str_to_format_param($trkrow->{bitrate}); # split "mp3 128" + chop($mp3fp); + $linkname = gdgentools::ascii2filename( + gdgentools::iso2ascii($trkrow->{artist}." - ".$trkrow->{title})); + #print("exporting link: "."$mp3fp --- $base/outbox/$linkname.mp3"."\n"); + symlink ("$mp3fp", "$base/outbox/$linkname.$audiofmt"); + } + $trh->finish; + + } + $sth->finish; +} + + + +################################################################ +### Upload Playlist to mp3 Player: + +sub pl_upload_to_player_info{ +print("EXTMP3 is:".$gdparms::extmp3player."\n"); + if (length($gdparms::extmp3player)>0){ + pl_export_empty(); + pl_export_audiolinks(); +print("CMD: gdupload2player.pl --$gdparms::extmp3player --info\n"); + my $resline = `gdupload2player.pl --$gdparms::extmp3player --info`; + gdio::putline ($resline); + } + gdio::putline (""); # End of result +} + +sub pl_upload_to_player{ + my ($addtrks) = @_; + + system( "gdupload2player.pl --$gdparms::extmp3player $addtrks&"); +} + + + + +################################################################ +### Saved Playlists: + +sub pl_save_playlist{ +# saves the playlist with the specified name +# Parameters: title, author, note, followed by list of track id's + my $title = shift(@_); + my $author = shift(@_); + my $note = shift(@_); + my ($playlistid, $trackid, $tracknum); + + my $nbtracks = @_; + if (length($title)>0 && $nbtracks>0){ + ### check if playlist with that name already exists + my $sth = $dbh->prepare("SELECT * FROM playlist WHERE title = ".$dbh->quote($title)); + my $count = $sth->execute; + if ($count > 0){ + ### A playlist with this name exists already, get it's ID + my $row = $sth->fetchrow_hashref; + $playlistid = $row->{id}; + my $plh = $dbh->prepare( + "SELECT * FROM playlistitem WHERE playlist = ".$playlistid + ." ORDER BY tracknumber DESC LIMIT 1"); + $count = $plh->execute; + if ($count>0){ + $row = $plh->fetchrow_hashref; + $tracknum = $row->{tracknumber} +1; + } + else{ + $tracknum = 1; + } + $plh->finish; + + } + else{ + ### A playlist with this name does not exist, create it + my $sqlcmd = + "INSERT INTO playlist (title,author,note,created) " + ."VALUES (".$dbh->quote($title).",".$dbh->quote($author) + .",'$note',CURDATE()) "; + $dbh->do($sqlcmd); + $playlistid = $dbh->{'mysql_insertid'}; + $tracknum = 1; + } + $sth->finish; + + + ### Append tracks to playlist $playlistid starting with number $tracknum + while($trackid = shift(@_)){ + #print("appending track-ID $trackid as itemnb $tracknum\n"); + my $sqlcmd = + "INSERT INTO playlistitem (playlist,tracknumber,trackid) " + ."VALUES ($playlistid,$tracknum,$trackid) "; + $dbh->do($sqlcmd); + $tracknum++; + } + } + else{ + print("Can't save playlist: title or playlist are empty\n"); + } +} + + +sub pl_delete_playlist{ +# deletes the playlist with the specified id + my ($playlistid) = @_; + my $sqlcmd; + + ### Delete all associated tracks + $sqlcmd = ("DELETE FROM playlistitem WHERE playlist = ".$playlistid); + $dbh->do($sqlcmd); + + ### Delete playlist + $sqlcmd = ("DELETE FROM playlist WHERE id = ".$playlistid); + $dbh->do($sqlcmd); +} + + + +############################################################ +### Play mp3 file commands + +### directly play a track (in inbox, before trimming, etc) +sub cmd_direct_play_mp3_track{ +#parameters: filename of mp3 file (without path) +# startframe (optional) +# nbframes (optional) + + my ($audiofile, $startframe, $nbframes) = @_; + my $base = gdparams::gdbase(); + + ### kill early, for /dev/dsp to recover + gdgentools::stop_play_processes($dbh, $playerid, $audchannel0); + system("sleep 1s"); # sleep a second + + if($startframe > 0){ + $startframe = "-k ".$startframe; + } + else{ + $startframe = "-k 0"; + } + if($nbframes > 0){ + $nbframes = "-n ".$nbframes; + } + else{ + $nbframes = ""; + } + + print("\nplaying inbox $audiofile $startframe $nbframes\n"); + # play it in inbox or in 00, 01 ... + if ( gdgentools::audio_filetype($audiofile) eq "mp3"){ + system( "mpg123 $startframe $nbframes $base/[0-9i][0-9n]*/$audiofile&"); + } + elsif(gdgentools::audio_filetype($audiofile) eq "ogg"){ + system( "ogg123 $base/[0-9i][0-9n]*/$audiofile&"); + } +} + + +### Command stop playing +sub cmd_stop_playing{ # + gdgentools::kill_all_play_processes(); +# gdgentools::stop_play_processes($dbh, $playerid, $audchannel0); + print("Playing stopped\n"); +} + +############################################################ +### Play CD track commands + +### Command stop playing cd +sub cmd_stop_playing_cd{ # + system("cdplay stop"); +} + +### Command play CD track +sub cmd_play_cd_track{ # + my ($tracknb) = @_; + system("cdplay play $tracknb"); +} + + +############################################################ +### Listings ### +############################################################ + +############################################################ +### List used genres +sub list_used_genres{ #get all used genres, count and list them + + my $sth = $dbh->prepare( + 'SELECT id, genre, freq ' + .'FROM genre ' + .'ORDER BY genre ' + ); + + $sth->execute; + my (@row, $stop); + + # "no genre" first + print ("\t\t1\t0\t0\n"); + gdio::putline ("\t\t1\t0\t0"); + + while(@row = $sth->fetchrow_array){ + print ("$row[0]\t$row[1]\t$row[2]\t0\t0\n"); + $stop = gdio::putline ("$row[0]\t$row[1]\t$row[2]\t0\t0"); + last if ($stop); + } + gdio::putline (""); # End of result + $sth->finish; +} + +############################################################ +### List used languages +sub list_used_languages{ #get all used languages, count and list them + my $sth = $dbh->prepare( + 'SELECT id, language, freq ' + .'FROM language ' + .'ORDER BY freq DESC ' + ); + + my $rv = $sth->execute; + my (@row, $stop); + + # "no language" first + print ("\t\t1\n"); + gdio::putline ("\t\t1"); + + while(@row = $sth->fetchrow_array){ + print ("$row[0]\t$row[1]\t$row[2]\n"); + $stop = gdio::putline ("$row[0]\t$row[1]\t$row[2]"); + last if ($stop); + } + gdio::putline (""); # End of result + $sth->finish; +} + +############################################################ +### List music types +sub list_music_types{ + my $sth = $dbh->prepare('SELECT * FROM musictype ORDER BY id '); + my $rv = $sth->execute; + my ($row, $stop); + + while($row = $sth->fetchrow_hashref){ + print ("$row->{musictype}\n"); + $stop = gdio::putline ("$row->{musictype}"); + last if ($stop); + } + gdio::putline (""); # End of result + $sth->finish; +} + +############################################################ +### List music sources +sub list_music_sources{ + my $sth = $dbh->prepare('SELECT * FROM source ORDER BY id '); + my $rv = $sth->execute; + my ($row, $stop); + + while($row = $sth->fetchrow_hashref){ + print ("$row->{source}\n"); + $stop = gdio::putline ("$row->{source}"); + last if ($stop); + } + gdio::putline (""); # End of result + $sth->finish; +} + + +############################################################ +### Calculate language hash +sub language_hash{ # returns the hash value of the language + # table +# my $sth = $dbh->prepare( # get used languages only +# 'SELECT language.id, language.language, COUNT(*) AS freq ' +# .'FROM tracks,language ' +# .'WHERE tracks.lang=language.id ' +# .'GROUP BY language.id ' +# .'ORDER BY freq DESC ' +# ); + + my $sth = $dbh->prepare( + 'SELECT id, language, freq ' + .'FROM language ' + .'ORDER BY freq DESC ' + ); + + my $rv = $sth->execute; + my @row; + my $newval; + + my $hashval = 0; + + while(@row = $sth->fetchrow_array){ + ### calc hash value + $newval = ord(chop($row[0]))<<8; + $newval += ord(chop($row[0])); + $hashval = myhash::addvaltohash($hashval, $newval); + } + $sth->finish; + +# my $sth = $dbh->prepare( # get all languages alphabetically +# 'SELECT language.id ' +# .'FROM language ' +# .'ORDER BY language.id ' +# ); +# +# my $rv = $sth->execute; +# my @row; +# +# while(@row = $sth->fetchrow_array){ +# ### calc hash value +# $newval = ord(chop($row[0]))<<8; +# $newval += ord(chop($row[0])); +# $hashval = myhash::addvaltohash($hashval, $newval); +# } +# $sth->finish; + + return $hashval; +} + + +############################################################ +### Get language hash +sub get_language_hash{ # sends the language hash val to th client + # + + my $hashval = language_hash(); + + print("language hash value = $hashval \n"); + gdio::putline ("$hashval"); # send hash value + gdio::putline (""); # End of result +} + + +############################################################ +### calculate genre hash value +sub genre_hash{ # returns hash value of the language + # table + +# my $sth = $dbh->prepare( # get used genres only +# 'SELECT genre.id,genre.genre,COUNT(*) AS freq ' +# .'FROM tracks,genre ' +# .'WHERE tracks.genre1=genre.id OR tracks.genre2=genre.id ' +# .'GROUP BY genre.id ' +# .'ORDER BY freq ' +# ); + + my $sth = $dbh->prepare( + 'SELECT id, genre, freq ' + .'FROM genre ' + .'ORDER BY freq ' + ); + + my $rv = $sth->execute; + my @row; + my $newval; + + my $hashval = 0; + + while(@row = $sth->fetchrow_array){ + ### calc hash value + $newval = ord(chop($row[0]))<<16; + $newval += ord(chop($row[0]))<<8; + $newval += ord(chop($row[0])); + $hashval = myhash::addvaltohash($hashval, $newval); + } + $sth->finish; + +# my $sth = $dbh->prepare( # get all genres alphabetically +# 'SELECT genre.id ' +# .'FROM genre ' +# .'ORDER BY genre.id ' +# ); +# +# my $rv = $sth->execute; +# +# while(@row = $sth->fetchrow_array){ +# ### calc hash value +# $newval = ord(chop($row[0]))<<8; +# $newval += ord(chop($row[0])); +# $hashval = myhash::addvaltohash($hashval, $newval); +# } +# +# $sth->finish; + + return $hashval; +} + + + +############################################################ +### Get genre hash +sub get_genre_hash{ # send genre hash value to client + # + + my $hashval = genre_hash(); + + print("genre hash value = $hashval \n"); + gdio::putline ("$hashval"); # send hash value + gdio::putline (""); # End of result +} + + +############################################################ +### calculate types hash value +sub types_hash{ # returns hash value of the + # musictype table + + my $sth = $dbh->prepare('SELECT * FROM musictype ORDER BY id '); + my $rv = $sth->execute; + my $row; + my $hashval = 0; + my $newval; + + while($row = $sth->fetchrow_hashref){ + ### calc hash value + $newval = ord(chop($row->{musictype}))<<16; + $newval += ord(chop($row->{musictype}))<<8; + $newval += ord(chop($row->{musictype})); + $hashval = myhash::addvaltohash($hashval, $newval); + } + $sth->finish; + + return $hashval; +} + +############################################################ +### Get types hash +sub get_types_hash{ # send types hash value to client + # + + my $hashval = types_hash(); + + print("types hash value = $hashval \n"); + gdio::putline ("$hashval"); # send hash value + gdio::putline (""); # End of result +} + + +############################################################ +### calculate sources hash value +sub sources_hash{ # returns hash value of the + # source table + + my $sth = $dbh->prepare('SELECT * FROM source ORDER BY id '); + my $rv = $sth->execute; + my $row; + my $hashval = 0; + my $newval; + + while($row = $sth->fetchrow_hashref){ + ### calc hash value + $newval = ord(chop($row->{source}))<<16; + $newval += ord(chop($row->{source}))<<8; + $newval += ord(chop($row->{source})); + $hashval = myhash::addvaltohash($hashval, $newval); + } + $sth->finish; + + return $hashval; +} + +############################################################ +### Get sources hash +sub get_sources_hash{ # send sources hash value to client + # + + my $hashval = sources_hash(); + + print("sources hash value = $hashval \n"); + gdio::putline ("$hashval"); # send hash value + gdio::putline (""); # End of result +} + +############################################################ +### Get all hash +sub get_all_hashes{ # send hash values to client + # of all dynamic tables: + # language, genre, type + + my $languagehash = language_hash(); + my $genrehash = genre_hash(); + my $typeshash = types_hash(); + my $sourceshash = sources_hash(); + + print("hash values: $languagehash $genrehash $typeshash $sourceshash\n"); + gdio::putline ("$languagehash\t$genrehash\t$typeshash\t$sourceshash"); + gdio::putline (""); # End of result +} + + +############################################################ +### List all languages ordered +sub list_all_languages_ordered{ + #get all languages and list them ordered by their usage count +# my $sth = $dbh->prepare( +# 'SELECT language.id, language.language, ' +# .' (COUNT(*)-1) AS freq ' +# .'FROM language LEFT JOIN tracks ' +# .'ON tracks.lang=language.id ' +# .'GROUP BY language.id ' +# .'ORDER BY freq DESC, language.language ASC ' +# ); + my $sth = $dbh->prepare( + 'SELECT id, language, freq ' + .'FROM language ' + .'ORDER BY freq DESC, language ASC ' + ); + + my $rv = $sth->execute; + my (@row, $stop); + + while(@row = $sth->fetchrow_array){ + #if (length($row[2])==0){$row[2]=0;} + print ("$row[0]\t$row[1]\t$row[2]\n"); + $stop = gdio::putline ("$row[0]\t$row[1]\t$row[2]"); + last if ($stop); + } + gdio::putline (""); # End of result + $sth->finish; +} + + +############################################################ +### List all genres ordered directly +sub list_all_genres_ordered{ + # get all genres and list them ordered alphabetically by genre + # fields: id, genre, freq + + ### Get all genre entries + my $sthA = $dbh->prepare( + 'SELECT id, genre FROM genre ORDER BY genre.id ' + ); + my $totcnt = $sthA->execute; + + ### Get used entries only with count +# my $sthB = $dbh->prepare( +# 'SELECT genre.id,genre.genre,COUNT(*) AS freq ' +# .'FROM tracks,genre ' +# .'WHERE tracks.genre1=genre.id OR tracks.genre2=genre.id ' +# .'GROUP BY genre.id ORDER BY genre.id ' +# ); + my $sthB = $dbh->prepare( + 'SELECT id, genre, freq ' + .'FROM genre ' + .'ORDER BY id ' + ); + + my $usedcnt = $sthB->execute; + +### The following code should actually go to 'gdtablefreq.pl' ! + + ### Merge frequencies of list B into list A (both lists must be ordered by id!) + # -> build "hierarchical sum": freq(node) = sum-of each freq(subnode) + proper freq + my $rowB = $sthB->fetchrow_hashref; + my ($i, $rowA, @listA, $rec); + while ($rowA = $sthA->fetchrow_hashref){ + $rec = {}; + $rec->{"id"} = $rowA->{id}; + $rec->{genre} = $rowA->{genre}; + $rec->{freq} = 0; # init freq counter + if($rowA->{id} eq $rowB->{id}){ + # copy frequency of B to A + $rec->{freq} = $rowB->{freq}; + $rowB = $sthB->fetchrow_hashref; # get next entry from B + } + push @listA, $rec; + } + + recalc_genre_freq_arrayofhash(@listA); + + @listA = sort { $gdserv::a->{genre} cmp $gdserv::b->{genre} } @listA; + + # "no genre" first: freq=1 + print ("\t\t1\n"); + gdio::putline ("\t\t1"); + + my $stop; + for $i (0 .. $#listA){ + print ("$listA[$i]{id}\t$listA[$i]{genre}\t$listA[$i]{freq}\n"); + $stop = gdio::putline + ("$listA[$i]{id}\t$listA[$i]{genre}\t$listA[$i]{freq}"); + last if ($stop); + } + gdio::putline (""); # End of result + $sthA->finish; + $sthB->finish; +} + + +############################################################ +sub recalc_genre_freq_arrayofhash{ + ### new version: works on array of hashes (id, genre, freq) + # requires that the array is ordered by id! + + ### first pass, get maximal genre id length + my $arrlen = @_; + my $maxlen = 0; + my $i; + + for $i( 0 .. $arrlen ){ + if(length($_[$i]{id}) > $maxlen){ + $maxlen = length($_[$i]{id}); + } + } + ### next passes: cumulate freq of ID with maxlen to ID with maxlen-1 + my ($parent, $ID); + while ($maxlen > 1){ + for $i( 0 .. $arrlen ){ + # 0:id, 1:genre, 2:freq + if(length($_[$i]{id}) == $maxlen){ + ### add freq of this item to freq of parent item + $ID = $_[$i]{id}; + chop($ID); + $parent=0; + while($parent<$arrlen && $_[$parent]{id} ne $ID){ + $parent++; + } + if($_[$parent]{id} eq $ID){ + $_[$parent]{freq} += $_[$i]{freq}; + } + else{print("ERROR: cant' find parent ID $ID \n");exit;} + } + } + $maxlen--; + } +} + + +############################################################ +### Modify genres table + +############################################################ +### Modify Genres: Delete +sub gen_delete{ + ### Delete a genre and move all it's tracks to the supergenre + my($genreid) = @_; # the ID of the genre to be deleted + + my $targetid = $genreid; + chop ($targetid); + print ("Delete genre id $genreid translate it to $targetid\n"); + + my ($retval1, $retval2, $gensdel); + $retval1 = $dbh->do('UPDATE tracks SET genre1="'.$targetid.'" WHERE genre1 LIKE "'.$genreid.'%"'); + $retval2 = $dbh->do('UPDATE tracks SET genre2="'.$targetid.'" WHERE genre2 LIKE "'.$genreid.'%"'); + $gensdel= $dbh->do('DELETE FROM genre WHERE id LIKE "'.$genreid.'%"'); + print("$gensdel genre entries deleted. $retval1 + $retval2 track records changed to supergenre.\n"); + + if($retval1 eq '0E0'){$retval1=0;} + if($retval2 eq '0E0'){$retval2=0;} + gdio::putline ("$gensdel genre entries deleted. ".($retval1+$retval2)." track records changed."); + gdio::putline (""); # End of result +} + + +############################################################ +### find a free genre for a given prefix. It returns only the suffix. +sub gen_new_suffix{ + my ($prefix) = @_; + my $suffix = "a"; + ### search for a new id + my ($sth, $count); + while($suffix ne "z"){ + $sth = $dbh->prepare("SELECT * FROM genre WHERE id = '".$prefix.$suffix."'"); + $count = $sth->execute; + $sth->finish; + last if ($count eq '0E0'); + $suffix++; + } + return $suffix; +} + +############################################################ +### Modify Genres: Move +sub gen_move{ + ### Move a genre and all its subgenres to a new location in the hierarchy + my($genreidFrom, $genreidTo) = @_; # the two IDs + + print ("Move genre $genreidFrom to $genreidTo\n"); + + my $suffix = gen_new_suffix($genreidTo); # new subgenre in target genre + + if($suffix eq "z"){ + print ("No genre moved! You can't put more than 26 subgenres in a genre. \n"); + gdio::putline ("No genre moved! You can't put more than 26 subgenres in a genre."); + } + elsif( (length($genreidFrom) <= length($genreidTo)) + && (substr($genreidTo, 0, length($genreidFrom)) eq $genreidFrom) + ){ + print ("No genre moved! Target is a child of Source. \n"); + gdio::putline ("No genre moved! Target is a child of Source."); + } + else{ + ### replace all genre prefixes "$genreidFrom" by "$genreidTo.$suffix" + ### in tracks and genre table + + my $retval1 = $dbh->do( + 'UPDATE tracks SET genre1=CONCAT("'.$genreidTo.'","'.$suffix.'",SUBSTRING(genre1, '.(length($genreidFrom)+1).')) ' + .'WHERE genre1 LIKE "'.$genreidFrom.'%"'); + my $retval2 = $dbh->do( + 'UPDATE tracks SET genre2=CONCAT("'.$genreidTo.'","'.$suffix.'",SUBSTRING(genre2, '.(length($genreidFrom)+1).')) ' + .'WHERE genre2 LIKE "'.$genreidFrom.'%"'); + my $retval3 = $dbh->do( + 'UPDATE genre SET id=CONCAT("'.$genreidTo.'","'.$suffix.'",SUBSTRING(id, '.(length($genreidFrom)+1).')) ' + .'WHERE id LIKE "'.$genreidFrom.'%"'); + gdio::putline (($retval1+$retval2)." track genres, $retval3 genre IDs moved."); +# gdio::putline (" track genres moved."); + } + gdio::putline (""); # End of result +} + + +############################################################ +### Modify Genres: Rename +sub gen_rename{ + ### Rename an existing genre + my($genreid, $newgenrename) = @_; # the ID and the new name of the genre + print ("Add genre $newgenrename to $genreid\n"); + + my $retval = $dbh->do("UPDATE genre SET genre=".$dbh->quote($newgenrename)." WHERE id = '".$genreid."'"); + + gdio::putline ("Genre renamed to $newgenrename."); + gdio::putline (""); # End of result +} + + +############################################################ +### Modify Genres: Add +sub gen_add{ + ### Add a new genre + my($genreid, $newgenrename) = @_; # the ID of the genre where the new genre is added + print ("Add genre $newgenrename to $genreid\n"); + + my $suffix = gen_new_suffix($genreid); + + if($suffix eq "z"){ + print ("No genre added! You can't put more than 26 subgenres in a genre. \n"); + gdio::putline ("No genre added! You can't put more than 26 subgenres in a genre."); + } + else{ + my $retval = $dbh->do("INSERT INTO genre " + ."(id, id3genre, genre) " + ."VALUES('".$genreid.$suffix."',NULL,".$dbh->quote($newgenrename).")"); + gdio::putline ("Genre $newgenrename added."); + } + gdio::putline (""); # End of result +} + + + + +############################################################ +### Modify music types table + +############################################################ +### returns the number of records in the musictype table +sub typ_nb_records{ + my ($sth, $count); + $sth = $dbh->prepare("SELECT * FROM musictype"); + $count = $sth->execute; + $sth->finish; + return $count; +} + +############################################################ +### Modify Types: Add +sub typ_add{ + ### Add a new type + my($newtypename) = @_; + print ("Add type $newtypename\n"); + + my $index = typ_nb_records() + 1; + + my $retval = $dbh->do("INSERT INTO musictype " + ."(musictype, id) VALUES(".$dbh->quote($newtypename).",'".$index."')"); + gdio::putline ("Music type $newtypename added."); + gdio::putline (""); # End of result +} + +############################################################ +### Modify Types: Delete +sub typ_delete{ + ### Delete a type + + my($delid) = @_; # the ID of the type to be deleted + my $lastid = typ_nb_records() - 1; + + # sequence of id-numbers must be contiguous: -> "move" lastid over delid, + # overwriting delid. + + # Note: track.type starts from 0, musictype.id starts from 1 + # Bad, I know, but it's too late to change it. I ignore the num value + # of musictype.id, and only use it to define an order. + print ("Delete type id $delid, put $lastid to its place\n"); + + ### modify tracks table + my ($retval1, $retval2, $typsdel); + $retval1 = $dbh->do('UPDATE tracks SET type= 0 WHERE type = '.$delid); + $retval2 = $dbh->do('UPDATE tracks SET type='.$delid.' WHERE type = '.$lastid); + + ### modify musictypes table + $typsdel= $dbh->do('DELETE FROM musictype WHERE id ='.($delid+1)); + $retval2= $dbh->do('UPDATE musictype SET id='.($delid+1).' WHERE id = '.($lastid+1)); + + print("Type deleted. Type of ".$retval1." tracks set to NULL.\n"); + + if($retval1 eq '0E0'){$retval1=0;} + if($retval2 eq '0E0'){$retval2=0;} + gdio::putline ("Type deleted. Type of ".$retval1." tracks set to NULL."); + gdio::putline (""); # End of result +} + + +############################################################ +### Send complete track +sub send_track_row{ + # Sends a complete track row obained from a SELECT * FROM tracks + # query. + # The parameter is a track row (record) obtained by a + # $trackset->fetchrow_hashref command + + my ($trackrow) = @_; + ## if ($nbtrks > 0){ + print( + $trackrow->{artist}.", " + .$trackrow->{title}.", " + .$trackrow->{genre1}.", " + .$trackrow->{genre2}.", " + .$trackrow->{year}.", " + .$trackrow->{lang}.", " + .$trackrow->{type}.", " + .$trackrow->{rating}.", " + .$trackrow->{length}.", " + .$trackrow->{source}.", " + .$trackrow->{sourceid}.", " + .$trackrow->{tracknb}.", " + .$trackrow->{mp3file}.", " + .$trackrow->{condition}.", " + .$trackrow->{voladjust}.", " + .$trackrow->{created}.", " + .$trackrow->{modified}.", " + .$trackrow->{id}.", " + .$trackrow->{bitrate}.", " + .$trackrow->{haslyrics} + ."\n" + ); + + gdio::putline ( + $trackrow->{artist}."\t" + .$trackrow->{title}."\t" + .$trackrow->{genre1}."\t" + .$trackrow->{genre2}."\t" + .$trackrow->{year}."\t" + .$trackrow->{lang}."\t" + .$trackrow->{type}."\t" + .$trackrow->{rating}."\t" + .$trackrow->{length}."\t" + .$trackrow->{source}."\t" + .$trackrow->{sourceid}."\t" + .$trackrow->{tracknb}."\t" + .$trackrow->{mp3file}."\t" + .$trackrow->{condition}."\t" + .$trackrow->{voladjust}."\t" + .$trackrow->{created}."\t" + .$trackrow->{modified}."\t" + .$trackrow->{id}."\t" + .$trackrow->{bitrate}."\t" + .$trackrow->{haslyrics} + ); + + # These variables give a hint, if the frequencies should be recalculated (when a track is updated) + $trk_last_id = $trackrow->{id}; + $trk_last_lang = $trackrow->{lang}; + $trk_last_genre1 = $trackrow->{genre1}; + $trk_last_genre2 = $trackrow->{genre2}; +} + + + +############################################################ +### Set CDDB_get parameters +my %cddb_config; +# following variables just need to be declared if different from defaults +$cddb_config{CDDB_HOST}="freedb.freedb.org"; # set cddb host +$cddb_config{CDDB_PORT}=8880; # set cddb port +$cddb_config{CDDB_MODE}="cddb"; # set cddb mode: cddb or http +$cddb_config{CD_DEVICE}="/dev/cdrom"; # set cd device +# user interaction welcome? +$cddb_config{input}=0; # 1: ask user if more than one possibility + # 0: no user interaction + + +############################################################ +### Get and return id and toc of currently inserted CD +sub get_cd_diskid{ + use CDDB_get; + # get id and track lengths + my $diskid=CDDB_get::get_discids($cddb_config{CD_DEVICE}); +# $track[0]{cddbid} = sprintf "%lx", $diskid->[0]; # get hex notation + $diskid->[0] = sprintf "%08lx", $diskid->[0]; # get hex notation + return $diskid; +} + +############################################################ +### Get and return directory of currently inserted CD +sub get_cd_directory{ +# returns array of hashes: index 0: cddbid; index 1..: length (in seconds) +# index 0: {cddbid (hex, without '0x')}, {title}, {artist} +# other indexes: {tracklength-sec}, {title}, {artist} +# +# title and artist are only defined, if a cddb entry was found +# +# 1) if the system has internet access, a matching record is +# searched at freedb.org +# 2) searches for a matching cddb record in ~/cddb/* +# 3) If a record couldn't be found, an array with default strings +# is returned +# + + use CDDB_get; + #use Net::Ping; #can't use it because it requires root privileges + + my @track; + my $diskid = get_cd_diskid(); + my $base = gdparams::gdbase(); + $track[0]{artist} = "Artist"; + $track[0]{title} = "Album Title"; + $track[0]{cddbid} = $diskid->[0]; + my $nbtracks = $diskid->[1]; + my $toc = $diskid->[2]; + my $cddbid = $diskid->[0]; + my ($lengthsec); + my $i=1; + while ($i <= $nbtracks) {#i<total incl. lead-out + $lengthsec = ($toc->[$i] ->{min}*60+$toc->[$i] ->{sec}) + -($toc->[$i-1]->{min}*60+$toc->[$i-1]->{sec}); + $track[$i]{track}= $i; + $track[$i]{length}= $lengthsec; + $track[$i]{artist}=" "; # some default values + $track[$i]{title} ="CD Track $i"; + $i++; + } + + +#print "\nBEGIN ping\n"; +#my $p = Net::Ping->new("icmp"); +#print "freedb.freedb.org is alive.\n" if $p->ping("freedb.freedb.org", 2); +#$p->close(); +#print "END ping\n"; + + if ($gdparms::systemonline){# Are we online? + ################################################################## + ### We are online, Try to get matching freedb entry + my %cd=CDDB_get::get_cddb(\%cddb_config); + + if(defined $cd{title}) { + $track[0]{artist} = $cd{artist}; + $track[0]{title} = $cd{title}; + #$track[0]{year} = $cd{year}; + ### Add track info to @track + my $i=1; + while ($i <= $nbtracks) { + #print "$i: $cd{track}[$i-1] \n"; + $track[$i]{artist} = $cd{artist}; + $track[$i]{title} = $cd{track}[$i-1]; + # ...{genre} = $cd{cat}; + $i++; + } + } + else{ + print "freedb: NOT FOUND cddbid: $cd{id}\n"; + } + } + else{ + ################################################################## + ### We are offline, try to find a cddb record on the local machine + my $cddb_file = `find $base/cddb -name $cddbid -print`; + $cddb_file = (split /\s+/, $cddb_file)[0]; # get first match only + if(length($cddb_file) > 0){ + ### a cddb-file was found - get it + my @cddblst = get_cddb_rec_offline("$cddb_file"); + $i = 0; + while ($i <= $nbtracks){ + $track[$i]{artist} = $cddblst[$i]{artist}; + $track[$i]{title} = $cddblst[$i]{title}; + $i++; + } + } + } + return @track; +} + + +#sub get_cd__directory___OLD___{ +#sub get_cd__directory{ +# returns array of hashes: index 0: cddbid; index 1..: length (in seconds) +# my $tmpout="/tmp/cdda2wav.index.out"; +# my $nbtracks = 0; +# my @track; +# my $discid; +# my $line; +# +# system("rm $tmpout"); +# system("cdda2wav -D /dev/cdrom -N -d1 &> $tmpout"); +# open(TMPOUT, $tmpout); +# while(<TMPOUT>){ +# $line = $_; +# if($line=~m/total tracks/){ +# $line=~m/total tracks:([0-9]*)/; +# $nbtracks = $1; +# } +# while($line=~m/[0-9]*\.\(\s*[0-9]*:[0-9]*\.[0-9]*\)/){ +# $line=~m/([0-9]*)\.\(\s*([0-9]*):([0-9]*)\.[0-9]*\)/; +# $track[$1]{track}= $1; +# $track[$1]{length}= ($2*60)+$3; +# +# #push @track, $rec; +# $line=~s/[0-9]*\.\(\s*[0-9]*:[0-9]*\.[0-9]*\)/xx/; +# } +# if($line=~m/CDDB discid:\s*[0-9a-z]*/){ +# $line=~m/CDDB discid:\s*([0-9a-z]*)/; +# #print "Disc ID: $1\n"; +# $discid=$1; +# $discid =~ m/0x(\S*)/; # remove 0x +# $track[0]{cddbid}= $1; +# } +# } +# close(TMPOUT); +# return @track; +#} + + + +############################################################ +### Read the specified cddb-file and return its content +sub get_cddb_rec_offline{ +# returns array: index 0: album artist, album title; +# index 1..: track title; +# examples: -> separator: '-' or '/' +# DTITLE=Die Aerzte - Die Bestie in Menschengestalt +# DTITLE=MC Lyte / Ain't No Other + + my ($cddb_file) = @_; + + my @track; + my $line; + + open(CDDBF, $cddb_file) or die "can't open file $cddb_file\n"; + while(<CDDBF>){ + $line = $_; + chop($line); + if($line=~m/DTITLE=.+/){ + if ($line=~m/DTITLE=(.+) \/ (.+)/){ + $track[0]{artist}= $1; + $track[0]{title} = $2; + } + elsif($line=~m/DTITLE=(.+) \- (.+)/){ + $track[0]{artist}= $1; + $track[0]{title} = $2; + } + elsif($line=~m/DTITLE=(.+)/){ + $track[0]{artist}= $1; + $track[0]{title} = ""; + } + } + + if($line=~m/TTITLE([0-9]+)=(.+)/){ + $track[$1+1]{artist}= $track[0]{artist}; + $track[$1+1]{title} = $2; + } + } + close(CDDBF); + + return @track; + +} + +############################################################ +### List directory of currently inserted CD plus informations from DB +sub list_cd_directory_info{ + # takes no parameter or the cddb-id + + my ($cddbid) = @_; + + my @track; + my $nbtracks; + my $i; + my $nbtrks; + my $nbalbs; + my $trseth; # track set handle + my @row; + my $trackrow; + my $base = gdparams::gdbase(); + my $cddb_file=""; + my @cddblst; + + ######################################################### + ### Set default values + @track = get_cd_directory(); + $nbtracks = @track - 1; + + ######################################################### + ### Send album record first + $trseth = $dbh->prepare( + 'SELECT * FROM album WHERE cddbid ="'. $cddbid . '" '); + $nbalbs = $trseth->execute; + if($nbalbs > 0){ + print("Matching album in database found"); + $trackrow = $trseth->fetchrow_hashref; + $trackrow->{cddbid} = $cddbid; + print("$trackrow->{artist}\t$trackrow->{title}\t$cddbid\n"); + gdio::putline("$trackrow->{artist}\t$trackrow->{title}\t$cddbid"); + } + else{ + if(defined($track[0]{title})){ + ### a matching online freedb entry was found + print("$track[0]{artist}\t$track[0]{title}\t$track[0]{cddbid}\n"); + gdio::putline("$track[0]{artist}\t$track[0]{title}\t$track[0]{cddbid}"); + } + else{ + print "Error: can't read CD directory at all\n"; + } + } + $trseth->finish; + + ######################################################### + ### Send each track + for ($i=1; $i<=$nbtracks; $i++){ + ### get first track with given cddb-id and track number + + if (length($cddbid)>=8){ + $trseth = $dbh->prepare( + 'SELECT * FROM tracks ' + .'WHERE sourceid ="'. $cddbid . '" ' + .' AND tracknb = '.$i ); + $nbtrks = $trseth->execute; + $trackrow = $trseth->fetchrow_hashref; + } + + if ((length($cddbid)<8) || ($nbtrks < 1)){ + ### set default values + $nbtrks = 0; + $trackrow->{artist} = $track[$i]{artist}; + $trackrow->{title} = $track[$i]{title}; + $trackrow->{genre1} = ""; + $trackrow->{genre2} = ""; + $trackrow->{year} = ""; + $trackrow->{lang} = ""; + $trackrow->{type} = 0; + $trackrow->{rating} = 0; + $trackrow->{length} = $track[$i]{length}; + $trackrow->{source} = 0; + $trackrow->{sourceid} = $track[0]{cddbid}; + $trackrow->{tracknb} = $i; + $trackrow->{mp3file} = ""; + $trackrow->{condition}= 0; + $trackrow->{voladjust}= 0; + #$trackrow->{created} = + #$trackrow->{modified} = + $trackrow->{bitrate} = $gdparms::defrecbitrate; + $trackrow->{haslyrics}=""; + } + + ### send all fields + send_track_row($trackrow); + + $trseth->finish; + }# end for + + gdio::putline (""); # End of result +} +############################################################ +### List directory of currently inserted CD plus informations from DB +sub list_inbox_album_directory{ + # takes as parameter the album directory (without /home/music/inbox prefix) + + my ($path) = @_; + + my ($curfile); + my $base = gdparams::gdbase(); + my $fullpath = "$base/inbox/albums/$path"; + print "import album at $fullpath\n"; + + unlink <$base/inbox/trxx*>; + unlink <$base/inbox/tmp-album-dir>; + system "cd \"$fullpath\"; rm -f trxx*"; + opendir INBOX, "$fullpath"; + my @inboxfile = readdir INBOX; + closedir INBOX; + + ### generate new mp3-filename (get highest trxx... filename) + my $fileid = gdgentools::last_imported_tracknb($dbh); + $fileid += 1; # next available id + my $cddbid = sprintf("%08ld", $fileid); + my $tracknb = 1; + + ######################################################### + ### Send album record first + + print "$path\t$path\t$cddbid\n"; + gdio::putline("$path\t$path\t$cddbid"); + #print "ln -s \"$fullpath\" \"$base/inbox/tmp-album-dir\"\n"; + system "ln -s \"$fullpath\" \"$base/inbox/tmp-album-dir\""; + + ######################################################### + ### Send each track + foreach $curfile (@inboxfile){ + ### check if file is audio format + if ( gdgentools::audio_filetype($curfile) eq "mp3" + || gdgentools::audio_filetype($curfile) eq "ogg" + || gdgentools::audio_filetype($curfile) eq "flac"){ + send_inbox_track_info($curfile, $fileid, "$cddbid", $tracknb, "$fullpath"); + $fileid += 1; + $tracknb += 1; + } + }# end foreach + + gdio::putline (""); # End of result +} + + + +############################################################ +### List directory of current inbox plus informations from the metatags +sub list_inbox_directory_info{ + + my $base = gdparams::gdbase(); + + unlink <$base/inbox/trxx*>; + opendir INBOX, "$base/inbox"; + my @ibfile = readdir INBOX; + closedir INBOX; + + my ($curfile); + + + ### generate new mp3-filename (get highest trxx... filename) + my $fileid = gdgentools::last_imported_tracknb($dbh); + + ### Send each track + foreach $curfile (@ibfile){ + ### check if file is mp3 format + if ( gdgentools::audio_filetype($curfile) eq "mp3" + || gdgentools::audio_filetype($curfile) eq "ogg" + || gdgentools::audio_filetype($curfile) eq "flac"){ + $fileid += 1; #next available id + send_inbox_track_info($curfile, $fileid, "", 1, "$base/inbox"); + } + }# end foreach + + gdio::putline (""); # End of result +} + +############################################################ +### the file $curfile must be valid audio file +# if it is a audiofile, all available metadata and other track +# informations are sent to the server +sub send_inbox_track_info{ + + my ($curfile, $fileid, $cddbid, $tracknb, $path) = @_; + my ($id3genre, $title, $audiofile, $trackrow); + + my $base = gdparams::gdbase(); + + ### check if file has a legal audio format + + if ( gdgentools::audio_filetype($curfile) eq "mp3" + || gdgentools::audio_filetype($curfile) eq "ogg" + || gdgentools::audio_filetype($curfile) eq "flac"){ + print ("\ncurrentfile is: $curfile \n"); + + $audiofile = sprintf("trxx%08ld.%s", $fileid, gdgentools::audio_filetype($curfile)); + symlink "$path/$curfile", "$base/inbox/$audiofile"; + + # get info from meta tags + $trackrow->{title} = gdgentools::audiofile_title("$path/$curfile"); # takes filename as default of no metatag present + $trackrow->{artist} = gdgentools::audiofile_artist("$path/$curfile"); + $trackrow->{genre2} = ""; + $trackrow->{year} = gdgentools::audiofile_year("$path/$curfile"); + $trackrow->{lang} = "-"; + $trackrow->{type} = 1; # medium + $trackrow->{rating} = 0; + $trackrow->{length} = gdgentools::audiofile_lengthsec("$path/$curfile"); + $trackrow->{source} = 0; # CD + $trackrow->{sourceid} = $cddbid; # could also be empty + $trackrow->{tracknb} = $tracknb; + $trackrow->{mp3file} = $audiofile; + $trackrow->{condition} = 0; # OK + $trackrow->{voladjust} = 0; + $trackrow->{created} = ""; + $trackrow->{modified} = ""; + $trackrow->{id} = ""; # a new one will be automatically generated + $trackrow->{bitrate} = gdgentools::get_bitrate_str("$path/$curfile"); + + # get genre if available and translate to gd-genre + $trackrow->{genre1} = gdgentools::audiofile_genre($dbh, "$path/$curfile"); +# $id3genre = gdgentools::audiofile_genre("$path/$curfile"); +# $trackrow->{genre1} = gdgentools::genre_id3togd($dbh, $id3genre); + + ### send all fields + $trackrow->{haslyrics} = ""; + send_track_row($trackrow); + + }# end if is mp3file +} + +############################################################ +### import booklet/cover images +sub import_cover_img{ + # imports the jpeg images in a directory and associates them to an album + # the images are imported in lexical order. + # Naming scheme: trxx(cd-id)-(num).jpg, where num is an automatically + # incremented counter. The file imgxx(cd-id)-00.jpg is the front cover, + # the other are the following pages in a booklet. + + # Parameters: 1) full directory path, 2) cd-id (like 0x10ac77e0, xx00001496) + + gdgentools::import_cover_images($dbh, @_); +} + + +############################################################ +### Get one track and send all information details from DB +sub get_track_details{ + # takes as parameter the track-ID + + my ($trackid) = @_; + my $base = gdparams::gdbase(); + + + my $sth = $dbh->prepare("SELECT * FROM tracks WHERE id=$trackid"); + $sth->execute; + my $trackrow = $sth->fetchrow_hashref; + + if(length($trackrow->{mp3file})>0){ + if(length($trackrow->{lyrics})>0){ + $trackrow->{haslyrics} = "1"; + } + } + ### send all fields + send_track_row($trackrow); + + $sth->finish; + + gdio::putline (""); # End of result +} + + +############################################################ +### Delete specified track record and mp3 file +sub delete_track{ +# deletes the playlist with the specified id + my ($trackid) = @_; + my $sqlcmd; + my $base = gdparams::gdbase(); + + ### Get track record + my $sth = $dbh->prepare("SELECT * FROM tracks WHERE id=$trackid"); + $sth->execute; + my $trackrow = $sth->fetchrow_hashref; + my $trackfile = $trackrow->{mp3file}; + truncstr($trackrow->{artist}, $shrtFieldLen); + truncstr($trackrow->{title}, $shrtFieldLen); + print("Deleting ($trackrow->{id}) $trackrow->{artist}, $trackrow->{title}\n"); + + ### Delete track record + $sqlcmd = ("DELETE FROM tracks WHERE id = ".$trackid); +#print" \$dbh->do($sqlcmd);\n"; + $dbh->do($sqlcmd); + + ### Delete mp3 file +#print("rm $base/[0-9][0-9]/$trackfile \n"); + if (length($trackfile)>4){ + system("rm $base/[0-9][0-9]/$trackfile"); + } + $sth->finish; + +} + + +############################################################ +### Delete specified album and all associated track records and mp3 files +sub delete_album_tracks{ +# deletes the playlist with the specified id + my ($albumid) = @_; + + if(length($albumid)>=6) { + ### Delete all associated tracks + ### get tracks of the album + my $trksth = do_tracks_of_album_query($albumid); + my $trow; + while($trow = $trksth->fetchrow_hashref){ + delete_track($trow->{id}); + } + $trksth->finish; + + ### Delete album record + print("Deleting Album $albumid\n"); + my $sqlcmd = ("DELETE FROM album WHERE cddbid = '".$albumid."'"); +#print"\$dbh->do($sqlcmd);\n" + $dbh->do($sqlcmd); + } +} + + +############################################################ +### Get one track and send lyrics (or empty text) +sub get_track_lyrics{ + # takes as parameter the track-ID + + my ($trackid) = @_; + + my $sth = $dbh->prepare("SELECT * FROM tracks WHERE id=$trackid"); + $sth->execute; + my $trackrow = $sth->fetchrow_hashref; + + ### insert one space into empty lines (protocol requires non empty content lines) + my $lyrics = $trackrow->{lyrics}; + + ### send lyrics + my @lyrlines = split /\n/, $trackrow->{lyrics}; + chomp @lyrlines; + my $line; + print ("Sending lyrics: ".scalar(@lyrlines)." lines\n"); + foreach $line (@lyrlines){ # line end is a \015\012 sequence + $line =~ s/\012$//; # chop off very last newline, if there is one + $line =~ s/\015$//; # chop off very last newline, if there is one + if (length($line)==0) {$line = " ";} + gdio::putline ($line); + } + gdio::putline (""); # End of result + + $sth->finish; +} + +############################################################ +### Update the lyrics of a track +sub update_track_lyrics{ + # parameters: track-ID, lyrics lines + + my $trackid = shift @_; + my $lyrics = join "\n", @_; + + print("Lyrics recieved for $trackid:\n$lyrics\n"); + $dbh->do("UPDATE tracks " + ."SET lyrics=".$dbh->quote($lyrics) + ."WHERE id=$trackid"); + +} + + +############################################################ +### Trim mp3 file +sub trim_mp3_file{ + # parameters: track-ID, lyrics lines + + my ($mp3file, $startframe, $endframe) = @_; + my ($mp3fpath, $mp3directory, $undofname); + my $base = gdparams::gdbase(); + + $mp3fpath = gdgentools::get_full_audiofile_path($mp3file); + $mp3directory = dirname($mp3fpath); + $undofname = $mp3directory."/TrimUndo-".$mp3file; + + + print ("rm $mp3directory/TrimUndo-*\n"); # remove old undofile + system("rm $mp3directory/TrimUndo-*"); + print ("mv $mp3fpath $undofname\n"); # save original mp3file + system("mv $mp3fpath $undofname"); + + print ("gdmp3cutter $undofname $startframe $endframe $mp3fpath\n"); + #system("gdmp3cutter $undofname $startframe $endframe $mp3fpath"); + ### continuously send results, line by line + my ($res, $resline); + open CMDRES, "gdmp3cutter $undofname $startframe $endframe $mp3fpath |"; + #autoflush CMDRES 1; + while($resline = <CMDRES>){ + chop($resline); + print ("\"$resline\"\n"); + gdio::putline ($resline); + } + + print ("END\n"); + gdio::putline (""); # End of result +} + +############################################################ +### Get one album and send all information details from DB +sub trim_mp3_file_undo{ # restores the trimmed mp3 file (if possible) + # parameters: track-ID, lyrics lines + + my ($mp3file) = @_; + my ($mp3fpath, $mp3directory, $undofname); + + + my $base = gdparams::gdbase(); + my $undofname = `ls $base/[0-9][0-9]/TrimUndo-$mp3file`; # get full path + chop($undofname); + + $mp3directory = dirname($undofname); + $mp3fpath = $mp3directory."/".$mp3file; + + #gdio::putline ("mv $undofname $mp3fpath"); + print ("mv $undofname $mp3fpath \n"); # restore original mp3file + system("mv $undofname $mp3fpath"); + + gdio::putline (""); # End of result +} + + +############################################################ +### Get one album and send all information details from DB +sub get_album_details{ + # takes as parameter the cddb-ID + + my ($albumid) = @_; + + if (length($albumid) > 8) { ### sometimes, a distorted album id preceeded by 4 trashy characters + # is passed to this routine (reason is unknown). + # this is just a workaround sent in by Frank (slightly modified by me) + print "warning: albumid preceeded by trash! fixing it.\n"; + ($albumid) = substr($albumid,-8,8); + } + + my $base = gdparams::gdbase(); + + + my $sth = $dbh->prepare("SELECT * FROM album WHERE cddbid=\"$albumid\""); + $sth->execute; + my $row; + if($row = $sth->fetchrow_hashref){ + ### send all fields + print("$row->{artist}\n"); + print("$row->{title}\n"); + gdio::putline ("$row->{artist}"); + gdio::putline ("$row->{title}"); + } + gdio::putline (""); # End of result + + $sth->finish; +} + +############################################################ +### check currently inserted CD and compare to GDDB +sub check_current_cd{ + +# This routine finds all distinct cddb-ID's in the GDDB that have +# the same stem as the cddb-ID of the currently inserted CD. +# The routine is needed to solve the problem of potentially ambiguous +# cddb-ID's. It is usually called before grabbing a CD. + +# my @track = get_cd_directory(); + #$track[0]{cddbid} =~ m/0x(\S*)/; + #my $cddbid = $track[0]{cddbid}; + my $diskid = get_cd_diskid(); + my $cddbid = $diskid->[0]; + + ### Get all cddb-id's with root $cddbid + my $sth = $dbh->prepare( + 'SELECT DISTINCT sourceid ' + .'FROM tracks ' + .'WHERE sourceid LIKE "'. $cddbid. '%" ' + .'ORDER BY sourceid ' + ); + + my $rv = $sth->execute; + + ### Get first track for each distinct cddb-id + my $trseth; # track set handle + my $row; + my $trackrow; + while($row = $sth->fetchrow_hashref){ + ### get first track with current cddb-id + + $trseth = $dbh->prepare( + 'SELECT artist, title FROM tracks ' + .'WHERE sourceid ="'. $row->{sourceid} . '" ORDER BY tracknb '); + $trseth->execute; + $trackrow = $trseth->fetchrow_hashref; + + print("$row->{sourceid}\t$trackrow->{artist}, $trackrow->{title}\n"); + gdio::putline ("$row->{sourceid}\t$trackrow->{artist}, " + ."$trackrow->{title}"); + $trseth->finish; + } + if($rv > 0){ ### propose also new unambiguous key + ### bulletproof is to take biggest extension instead of $rv!!! + + print ("$cddbid.$rv\tNone of these: New CD\n"); + gdio::putline("$cddbid.$rv\tNone of these: New CD"); + } + gdio::putline (""); # End of result + + $sth->finish; +} + +############################################################ +### read inbox album directories +sub list_inbox_album_directories{ + +# This routine finds all directoies in ~music/inbox/albums that +# contain mp3 or ogg files. Each directory is considered as an +# album + + my $base = gdparams::gdbase(); + + my @filelist = `ls -R1 $base/inbox/albums`; + my ($curfile, $directory); + + $directory=""; + + foreach $curfile (@filelist){ + chop($curfile); + if ($curfile =~ m/\/inbox\/albums\/(.*):/ ){ + #print "$curfile is a directory\n"; + $directory = $1; + } + if(length($directory)>0 and + ( gdgentools::audio_filetype($curfile) eq "ogg" + or gdgentools::audio_filetype($curfile) eq "mp3" + or gdgentools::audio_filetype($curfile) eq "flac") + ){ + # we have a audio file in a valid directory (directory not yet printed) + print "$directory\n"; + gdio::putline "$directory"; + $directory = ""; # mark as printed + } + } + + gdio::putline (""); # End of result + +} + + +############################################################ +### Open/close the CD tray +sub open_cd_tray{ + system("eject"); +} +sub close_cd_tray{ + system("eject -t"); +} + +############################################################ +### Get rip state +sub get_rip_state{ # send CD rip status to client + my $base = gdparams::gdbase(); + # get first item of rip-list + my $trid = gdgentools::tracklist_get_item($dbh, $playerid, $rp_list, 0); + if($trid != 0){ + my $sth = $dbh->prepare("SELECT * FROM tracks WHERE id=$trid"); + my $rv = $sth->execute; + my ($wavf, $track, $wavfsize); + if($track = $sth->fetchrow_hashref){ + if($track->{mp3file} =~ /(.*)\.\w+/){ + $wavf = `ls $base/[0-9][0-9]/$1.wav`; + chop($wavf); + } + else{print("getripstate: can't find wavfile\n");} + truncstr($track->{artist}, 20); + truncstr($track->{title}, 20); + print ("$track->{tracknb}. $track->{artist} - $track->{title}\n"); + gdio::putline ("$track->{tracknb}. $track->{artist} - $track->{title}"); + + $wavfsize = (-s "$wavf"); + my $wavtotsize = $track->{length} * 44100 * 4; # totalSize = length(sec)*sampRate*bytesPerSample + $wavtotsize++; # prevent from divison by zero + #print("length: $track->{length}, fsize: $wavfsize, soll: $wavtotsize\n"); + my $percent = sprintf("%.2f%%", ($wavfsize * 100) / $wavtotsize); + print ("$percent percent\n"); + gdio::putline ("$percent"); + my $queuelen = gdgentools::tracklist_get_nb_items($dbh, $playerid, $rp_list); + $queuelen--; + print ("$queuelen tracks queued\n"); + gdio::putline ("$queuelen tracks queued"); + } + else{ + print ("ERROR: can't find track in database\n"); + gdio::putline ("ERROR: can't find track in database"); + } + $sth->finish; + } + gdio::putline (""); # End of result +} + + +############################################################ +### Get compress state +sub get_compress_state{ # send track compress status to client + my $base = gdparams::gdbase(); + # get first item of compression-list + my $trid = gdgentools::tracklist_get_item($dbh, $playerid, $co_list, 0); +#print("top of compression list: track $trid\n"); + if($trid != 0){ + my $sth = $dbh->prepare("SELECT * FROM tracks WHERE id=$trid"); + my $rv = $sth->execute; + my ($track, $audiofile, $currfilesize, $totalfilesize, $percent, $queuelen, $datarate); + my ($audiofmt, $audioparam); + if($track = $sth->fetchrow_hashref){ + $audiofile = `ls $base/[0-9][0-9]/$track->{mp3file}`; + chop($audiofile); + truncstr($track->{artist}, 20); + truncstr($track->{title}, 20); + print ("$track->{tracknb}. $track->{artist} - $track->{title}\n"); + gdio::putline ("$track->{tracknb}. $track->{artist} - $track->{title}"); + + $currfilesize = (-s "$audiofile"); + ($audiofmt, $datarate) = gdgentools::bitrate_str_to_format_param($track->{bitrate}); # split "mp3 128" + if ($audiofmt eq "mp3" || $audiofmt eq "ogg"){ + ### datarate is bitrate + $totalfilesize = ($track->{length} * $datarate * 1000) / 8; # totalSize = length*bitrate/BitsPerByte + } + if ($audiofmt eq "flac"){ + ### datarate is sampling rate + $totalfilesize = ($track->{length} * $datarate * 1010 * 2); # totalSize = length*bitrate*BytesPerByte + } + $totalfilesize++; # prevent from divison by zero + #print("file: $audiofile, length: $track->{length}, bitrate: $datarate, fsize: $currfilesize, soll: $totalfilesize\n"); + $percent = sprintf("%.2f%%", ($currfilesize * 100) / $totalfilesize); + print ("$percent\n"); + gdio::putline ("$percent"); + + $queuelen = gdgentools::tracklist_get_nb_items($dbh, $playerid, $co_list); + $queuelen--; + print ("$queuelen wav-files queued\n"); + gdio::putline ("$queuelen wav-files queued"); + } + $sth->finish; + } + gdio::putline (""); # End of result +} + + +############################################################ +### Misc commands ### +############################################################ + +############################################################ +### command burn audio CD +sub pl_burn_cd{ # Arguments in @_ are the + + my ($trackid, $sth); + my $mp3list; + my $row; + my $base = gdparams::gdbase(); + + $mp3list = ""; + + ### Write track tiles+artists to file (for booklet printing etc.) + open(RECLIST, ">$base/tmp/gdburnlist.txt"); + my $trkcnt = 1; + + foreach $trackid (@_){ + $sth = get_track($trackid); # perform database query + + $row = $sth->fetchrow_hashref; + $sth->finish; + print("add to CD $base/??/$row->{mp3file} ($row->{title}) \n"); + print(RECLIST "$trkcnt. $row->{artist} $row->{title} \n"); + $mp3list = $mp3list . " " . "$base/??/" . $row->{mp3file}; + $trkcnt ++; + } + close(RECLIST); + print("gdburn.sh " . $mp3list . "\n"); + system("gdburn.sh " . $mp3list . "&"); +} + + + +############################################################ +### Update browse directory (external script) +sub update_browse_directory{ + my $base = gdparams::gdbase(); + system("$base/bin/gdmakelinks.pl -s &"); # -s silent +} + +### Update browse directory (external script) +sub export_id3_tags{ + my $base = gdparams::gdbase(); +# system("cd $base; $base/bin/gdexportdb.pl --id3 &"); + system("cd $base; $base/bin/gdexportdb.pl --metatags &"); +} + +### Shut down entire server (external script) +sub shut_down_server{ + use vars qw($shutdowncmd); + print("Executing $gdparms::shutdowncmd \n"); + system($gdparms::shutdowncmd); +# system("/usr/sbin/usershutdown -h now &"); +} + + + +############################################################ +### General Query Command + +### General database query +sub general_db_query{ + my ($dbquery) = @_; + my ($sth, @row, $rowstr); + + ### Get # tracks + $sth = $dbh->prepare($dbquery); + $sth->execute; + if(@row = $sth->fetchrow_array){ + $rowstr = join "\t", @row; + print "$rowstr\n"; + gdio::putline ($rowstr); + } + gdio::putline (""); # End of result + $sth->finish; +} + +sub general_db_query_count{ # needed because older mysql-versions have a bug with COUNT(DISTINCT ... + my ($dbquery) = @_; + my ($sth, @row, $rowstr, $nbrec); + + ### Get # tracks +#print "Database Query(count): $dbquery\n"; + $sth = $dbh->prepare($dbquery); + $nbrec = $sth->execute; + print "$nbrec\n"; + gdio::putline ($nbrec); + gdio::putline (""); # End of result + $sth->finish; +} + + +############################################################ +### Soundcard/Volume Commands +sub set_volume{ + my ($vol) = @_; + gdsoundcard::sndc_set_volume(gdgentools::playerdefinition($dbh, $playerid, $audchannel0), $vol); +} + +sub get_volume{ + my $vol = gdsoundcard::sndc_get_volume(gdgentools::playerdefinition($dbh, $playerid, $audchannel0)); + + gdio::putline ($vol); + gdio::putline (""); # End of result +} + +sub save_volume{ + gdsoundcard::sndc_save_volume(gdgentools::playerdefinition($dbh, $playerid, $audchannel0)); +} +sub inc_volume{ + gdsoundcard::sndc_inc_volume(gdgentools::playerdefinition($dbh, $playerid, $audchannel0)); +} +sub dec_volume{ + gdsoundcard::sndc_dec_volume(gdgentools::playerdefinition($dbh, $playerid, $audchannel0)); +} + +############################################################ +### General Shell Command + +### General shell command +sub general_sh_command{ + my ($shcommand) = @_; + system($shcommand); +} + +### General shell command and send back result +sub general_sh_command_res{ + my ($shcommand) = @_; + my ($res, $resline); + $res = `$shcommand`; + my @reslines = split /\n/, $res; + while ($resline = shift @reslines){ + print ("\"$resline\"\n"); + gdio::putline ($resline); + } + gdio::putline (""); # End of result +} + +sub general_sh_command_res_continuous{ +# continuously sends results, line by line + my ($shcommand) = @_; + my ($res, $resline); + open CMDRES, "$shcommand |"; + #autoflush CMDRES 1; + while($resline = <CMDRES>){ + chop($resline); + print ("\"$resline\"\n"); + gdio::putline ($resline); + } + gdio::putline (""); # End of result +} + +### General shell command and send back result +sub gd_basedir{ + my $base = gdparams::gdbase(); + print ("$base\n"); + gdio::putline ($base); + gdio::putline (""); # End of result +} + + + +############################################################ +### Database and Disc statistics +sub full_statistics{ + my $base = gdparams::gdbase(); + my ($sth, $row, $msg); + + gdio::putline ("DB statistics"); + ### Get # tracks + $sth = $dbh->prepare("SELECT COUNT(*) AS cnt FROM tracks"); + $sth->execute; + if($row = $sth->fetchrow_hashref){ + $msg = " ".$row->{cnt}." Tracks"; + print "$msg \n"; + gdio::putline ($msg); + } + $sth->finish; + + ### Get # albums + $sth = $dbh->prepare("SELECT COUNT(*) AS cnt FROM album"); + $sth->execute; + if($row = $sth->fetchrow_hashref){ + $msg = " ".$row->{cnt}." Albums"; + print "$msg \n"; + gdio::putline ($msg); + } + $sth->finish; + + gdio::putline (" "); + gdio::putline ("Disc statistics"); + gdio::putline (" (dir: used / free)"); + + ### Get mp3 directories and check each directory + my @mdir = gdparams::mp3dirs(); + my $i=0; + my (@dfres, $totused, $totfree); + + $totused=0; $totfree=0; + while($i < @mdir){ + if (-d "$base/$mdir[$i]"){ + @dfres = split / +/, `df -m $base/$mdir[$i]|tail -1`; + $msg = " ".$mdir[$i].": ".$dfres[2]."M / ".$dfres[3]."M"; + print "$msg \n"; + gdio::putline ($msg); + $totused += $dfres[2]; + $totfree += $dfres[3]; + } + else{print "$base/$mdir[$i] is not a directory or does not exist\n";} + $i++; + } + + $msg = " tot: ".$totused."M / ".$totfree."M"; + print "$msg \n"; + gdio::putline ($msg); + + gdio::putline (""); # End of result + + ### "Side Effect": print player-id and -type + print "\nStatus: playerid=$playerid, audiochannel=$audchannel0\n"; + +} + + +############################################################ + +sub server_alive_test{ # Ping + print ("Ping: GiantDisc Server alive\n"); + gdio::putline ("GiantDisc Server alive"); + gdio::putline (""); # End of result +} + + +sub serial_test{ + my $i; + for($i=0; $i<5; $i++){ + print("$i "); + gdio::putline(" 123456789 123456789 123456789 123456789 123456789 123456789 123456789"); + } + gdio::putline (""); # End of result + print("\n"); +} + + +END{ + ### close database connection + print("### close database connection\n"); + $dbh->disconnect; + + if($rippipe_open){close_rippipe();} +} +# +1; +# diff --git a/scripts/gdsoundcard.pm b/scripts/gdsoundcard.pm new file mode 100755 index 0000000..519181f --- /dev/null +++ b/scripts/gdsoundcard.pm @@ -0,0 +1,149 @@ +################################################## +# +# GiantDisc mp3 Jukebox +# +# © 2003, Rolf Brugger +# +################################################## + +package gdsoundcard; + +# +# soundcard drivers to control volume etc +# +# +# + +#use lib '/usr/local/bin'; +use strict; + +############################################################ +### Constants & global variables + + + +############################################################ + +sub sndc_init{ +### initialize + +# actually not used yet + + my ($playertype, $playerhost, $sounddevice) = @_; + + if ($playertype == 0){ # local oss soundcard + ; + } + + elsif ($playertype == 20){ # exstreamer + ; + } + else{ + print "Warning: unknown player type $playertype\n"; + } +} + + + +############################################################ +### Volume routines +# 0 <= volume <= 100 + + +sub sndc_set_volume{ +### Set volume + + my ($playertype, $playerhost, $sounddevice, $volume) = @_; + + if ($playertype == 0){ # local oss soundcard + system "aumix -v$volume"; + } + + elsif ($playertype == 20){ # exstreamer + use integer; + my $cmd = "v=".($volume/5)."\n"; + gdgentools::exstreamer_command($playerhost, $cmd); + } + else{ + print "Warning: unknown player type $playertype\n"; + } +} + + +sub sndc_get_volume{ +### Get volume +# returns the currently set volume. + + my ($playertype, $playerhost, $sounddevice) = @_; + my $volume = 0; + + if ($playertype == 0){ # local oss soundcard + my ($shcommand) = @_; + my ($res, $resline, @reslines); + $res = `aumix -vq`; + @reslines = split /\n/, $res; + $resline = shift (@reslines); + if ($resline =~ m/\D*(\d+).*/){ + $volume = $1; + } + else{print "Warning: Get volume - can't match aumix output\n";} + } + + elsif ($playertype == 20){ # exstreamer + my $cmd = "v=\n"; # cmd get volume + my $res = gdgentools::exstreamer_command_res($playerhost, $cmd); + if ($res =~ m/\<.*\>(\d+)\<.*\>/){ + $volume = ($1)*5; + } + else{ + $volume = 50; + } + } + else{ + print "Warning: unknown player type $playertype\n"; + } + return $volume; +} + + +sub sndc_save_volume{ +### Save default volume + + my ($playertype, $playerhost, $sounddevice, $volume) = @_; + + if ($playertype == 0){ # local oss soundcard + system "aumix -S"; + } + elsif ($playertype == 20){ # exstreamer + # the exstreamer always saves the volume setting in its flash rom + ; + } + else{ + print "Warning: unknown player type $playertype\n"; + } +} + +############################################################ + +sub sndc_inc_volume{ +### Increases volume by 5% + my ($playertype, $playerhost, $sounddevice) = @_; + my $volume = sndc_get_volume($playertype, $playerhost, $sounddevice); + $volume += 5; + if ($volume>100){$volume=100;} + sndc_set_volume($playertype, $playerhost, $sounddevice, $volume); +} + +sub sndc_dec_volume{ +### decreases volume by 5% + my ($playertype, $playerhost, $sounddevice) = @_; + my $volume = sndc_get_volume($playertype, $playerhost, $sounddevice); + $volume -= 5; + if ($volume<0){$volume=0;} + sndc_set_volume($playertype, $playerhost, $sounddevice, $volume); +} + +############################################################ + +1; +# diff --git a/scripts/gdupdate.pm b/scripts/gdupdate.pm new file mode 100755 index 0000000..8242c6c --- /dev/null +++ b/scripts/gdupdate.pm @@ -0,0 +1,509 @@ +################################################## +# +# GiantDisc mp3 Jukebox +# +# © 2000-2003, Rolf Brugger +# +################################################## + + +# Package for database modifications and consistency checks related +# to version updates + + +package gdupdate; + +use strict; + + +BEGIN{ +} + + +############################################################################### +### Version 1.32 +############################################################################### + +sub db_check_update_132{ + my ($dbh) = @_; + my ($sth, $count, $res); + + my $update = 0; + + ### new field 'audio channel' + $res = $dbh->do("SHOW COLUMNS FROM playerstate LIKE 'audiochannel'"); + if ($res < 1){ + $update = 1; + } + + $res = $dbh->do("SHOW COLUMNS FROM playerstate LIKE 'processid'"); + if ($res < 1){ + $update = 1; + } + + + return $update; +} + + +sub db_update_132{ + my ($dbh) = @_; + my ($sth, $count, $res); + + ### usage frequencies + $res = $dbh->do("SHOW COLUMNS FROM playerstate LIKE 'audiochannel'"); + if ($res < 1){ + print("Update table playerstate to version 1.32\n"); + print("rename index column playertype into audiochannel\n"); + $dbh->do("ALTER TABLE playerstate CHANGE playertype audiochannel INT NOT NULL"); + print("Adding field playertype to table playerstate\n"); + $dbh->do("ALTER TABLE playerstate ADD COLUMN playertype INT AFTER audiochannel"); + } + + ### player process id + $res = $dbh->do("SHOW COLUMNS FROM playerstate LIKE 'processid'"); + if ($res < 1){ + print("Update table playerstate to version 1.12\n"); + print("Adding field processid to table playerstate\n"); + $dbh->do("ALTER TABLE playerstate ADD COLUMN processid INT AFTER audiochannel"); + } + +} + + +############################################################################### +### Version 1.31 +############################################################################### + +sub db_check_update_131{ + my ($dbh) = @_; + my ($sth, $count, $res); + + my $update = 0; + + ### usage frequencies + $res = $dbh->do("SHOW COLUMNS FROM album LIKE 'genre'"); + if ($res < 1){ + $update = 1; + } + + ### Optimizations + my $row; + $sth = $dbh->prepare("SHOW TABLE STATUS FROM GiantDisc LIKE 'playerstate'"); + $count = $sth->execute; + if($row = $sth->fetchrow_hashref){ + if ($row->{Type} ne "HEAP"){ + $update = 1; + } + } + $sth->finish; + + + my ($dbh, $table, $column, $indexspec) = @_; + + my ($sth, $count, $row); + $sth = $dbh->prepare("SHOW index FROM tracks"); + $count = $sth->execute; + while($row = $sth->fetchrow_hashref){ + last if ($row->{Key_name} eq "artist"); + } + if($row->{Key_name} eq "artist"){ + #print "index artist exists\n"; + ; + } + else{ + print "Alert: no additional indexes defined\n"; + print " consider upgrading the db-structure with 'gdupdatedb.pl'\n"; + } + $sth->finish; + + + return $update; +} + + +sub db_update_131{ + my ($dbh) = @_; + my ($sth, $count, $res); + + ### usage frequencies + $res = $dbh->do("SHOW COLUMNS FROM album LIKE 'genre'"); + if ($res < 1){ + print("Update table album to version 1.31\n"); + print("Adding field genre to table album\n"); + $dbh->do("ALTER TABLE album ADD COLUMN genre VARCHAR(10) AFTER modified"); + } + + ### Optimizations + my $row; + $sth = $dbh->prepare("SHOW TABLE STATUS FROM GiantDisc LIKE 'playerstate'"); + $count = $sth->execute; + if($row = $sth->fetchrow_hashref){ + if ($row->{Type} ne "HEAP"){ + print "Set table 'playerstate' to type HEAP\n"; + $dbh->do("ALTER TABLE playerstate TYPE=HEAP"); + } + } + $sth->finish; + + + test_and_add_index($dbh, "tracks", "artist", "artist(artist(10))"); + test_and_add_index($dbh, "tracks", "title", "title(title(10))"); + test_and_add_index($dbh, "tracks", "genre1", "(genre1)"); + test_and_add_index($dbh, "tracks", "genre2", "(genre2)"); + test_and_add_index($dbh, "tracks", "year", "(year)"); + test_and_add_index($dbh, "tracks", "lang", "(lang)"); + test_and_add_index($dbh, "tracks", "type", "(type)"); + test_and_add_index($dbh, "tracks", "rating", "(rating)"); + test_and_add_index($dbh, "tracks", "sourceid","(sourceid)"); + test_and_add_index($dbh, "tracks", "mp3file", "mp3file(mp3file(10))"); + + test_and_add_index($dbh, "album", "artist", "artist(artist(10))"); + test_and_add_index($dbh, "album", "title", "title(title(10))"); + test_and_add_index($dbh, "album", "genre", "(genre)"); + test_and_add_index($dbh, "album", "modified", "(modified)"); + +} + +sub test_and_add_index +{ + my ($dbh, $table, $column, $indexspec) = @_; + + my ($sth, $count, $row); + $sth = $dbh->prepare("SHOW index FROM $table"); + $count = $sth->execute; + while($row = $sth->fetchrow_hashref){ + last if ($row->{Key_name} eq $column); + } + if($row->{Key_name} eq $column){ + #print "index $column exists\n"; + ; + } + else{ + print "creating index $column\n"; + $dbh->do("ALTER TABLE $table ADD INDEX $indexspec"); + } + $sth->finish; +} + + +############################################################################### +### Version 1.14 +############################################################################### + +sub db_update_114{ + my ($dbh) = @_; + my ($sth, $count, $res); + + ### bitrate + $sth = $dbh->prepare("SELECT id FROM tracks WHERE length(bitrate)<4"); + $count = $sth->execute; + if ($count > 0){ + print("Update table tracks to version 1.14\n"); + print("enlarging field bitrate to 10 characters\n"); + $dbh->do("ALTER TABLE tracks MODIFY COLUMN bitrate VARCHAR(10)"); + + ### add prefix "mp3 " to all bitrate fields of mp3 tracks + $res=print("add prefix \"mp3 \" to all bitrate fields of mp3 tracks\n"); + $dbh->do("UPDATE tracks SET bitrate=CONCAT('mp3 ',bitrate) " + ."WHERE length(bitrate)<4 AND ( mp3file LIKE '%.mp3'" + ." OR mp3file LIKE 'http%')"); + print "mp3 track records updated\n"; + $dbh->do("UPDATE tracks SET bitrate=CONCAT('ogg ',bitrate) " + ."WHERE length(bitrate)<4 AND mp3file LIKE '%.ogg'"); + + ### check result + $sth = $dbh->prepare("SELECT id FROM tracks WHERE length(bitrate)<4"); + $count = $sth->execute; + if ($count > 0){ + print "\n"; + print "Warning: some track records could not be properly translated.\n"; + print " You might have tracks in your database with no audio-\n"; + print " file associated!\n\n"; + } + } + $sth->finish; +} + + + +############################################################################### +### Version 1.12 +############################################################################### + +sub db_update_112{ + my ($dbh) = @_; + my $res; + + ### add player process id -> killing pid with killall and killfam is unstable and too slow! + #$res = $dbh->do("SHOW COLUMNS FROM playerstate LIKE 'processid'"); + #if ($res < 1){ + # print("Update table playerstate to version 1.12\n"); + # print("Adding field processid to table playerstate\n"); + # $dbh->do("ALTER TABLE playerstate ADD COLUMN processid INT AFTER snddevice"); + #} + + ### usage frequencies + $res = $dbh->do("SHOW COLUMNS FROM language LIKE 'freq'"); + if ($res < 1){ + print("Update table language to version 1.12\n"); + print("Adding field freq to table language\n"); + $dbh->do("ALTER TABLE language ADD COLUMN freq INT AFTER language"); + } + $res = $dbh->do("SHOW COLUMNS FROM genre LIKE 'freq'"); + if ($res < 1){ + print("Update table genre to version 1.12\n"); + print("Adding field freq to table genre\n"); + $dbh->do("ALTER TABLE genre ADD COLUMN freq INT AFTER genre"); + } +#else {print("-- column modified exists -> DB needs not be updated\n");} +} + + + +############################################################################### +### Version 1.11 +############################################################################### + +sub db_update_111{ + my ($dbh) = @_; + my $res; + + ### Table recordingitem + $res = $dbh->do("SHOW TABLES LIKE 'recordingitem'"); + if ($res < 1){ + print("recordingitem does not exist (upgrading...)\n"); + $dbh->do( + "create table recordingitem(" + ."trackid int," + ."recdate date," + ."rectime time," + ."reclength int," + ."enddate date," + ."endtime time," + ."repeat varchar(10)," + ."initcmd varchar(255)," + ."parameters varchar(255)," + ."atqjob int," + ."id int not null," + ."primary key(id)" + .")"); + } +#else{print("-- recordingitem does exist\n");} +} + + + +############################################################################### +### Version 0.97 +############################################################################### + +sub db_update_097{ + my ($dbh) = @_; + my $res; + + ### shuffle parameter + $res = $dbh->do("SHOW COLUMNS FROM playerstate LIKE 'shufflepar'"); + if ($res < 1){ + print("Update table album to version 0.97\n"); + print("Adding modified field shufflepar,shufflestat to table playerstate\n"); + $dbh->do("ALTER TABLE playerstate ADD COLUMN shufflepar varchar(255) AFTER state"); + $dbh->do("ALTER TABLE playerstate ADD COLUMN shufflestat varchar(255) AFTER shufflepar"); + } +#else {print("-- column modified exists -> DB needs not be updated\n");} +} + + + +############################################################################### +### Version 0.96 +############################################################################### + +sub db_update_096{ + my ($dbh) = @_; + my $res; + + ### album modification time + $res = $dbh->do("SHOW COLUMNS FROM album LIKE 'modified'"); + if ($res < 1){ + print("Update table album to version 0.96\n"); + print("Adding modified field to table album\n"); + $dbh->do("ALTER TABLE album ADD COLUMN modified date AFTER covertxt"); + } +#else {print("-- column modified exists -> DB needs not be updated\n");} +} + + +############################################################################### +### Version 0.95 +############################################################################### + +sub db_update_095{ + my ($dbh) = @_; + my $res; + + ### anchortime + $res = $dbh->do("SHOW COLUMNS FROM playerstate LIKE 'anchortime'"); + if ($res < 1){ + print("Update table playerstate to version 0.95\n"); + print("Adding anchortime field to table playerstate\n"); + $dbh->do("ALTER TABLE playerstate ADD COLUMN anchortime bigint AFTER framesremain"); + } +#else {print("-- column anchortime exists -> DB needs not be updated\n");} + + ### framestotal + $res = $dbh->do("SHOW COLUMNS FROM playerstate LIKE 'framestotal'"); + if ($res < 1){ + print("Update table playerstate to version 0.95\n"); + print("Renaming framesremain field to framestotal\n"); + $dbh->do("ALTER TABLE playerstate CHANGE framesremain framestotal INT"); + } +#else {print("-- column framestotal exists -> DB needs not be updated\n");} +} + + +############################################################################### +### Version 0.94 +############################################################################### + +sub check_new_mp3info080{ +# This routine checks if the version of mp3info is at least 0.8.0, which +# is required as of GD-version 0.94 +# A warning message is printed if mp3info should be updated + + my $infostr = `mp3info`; + if ($infostr =~ /MP3Info\D*([0-9]*).([0-9]*).([0-9]*)/){ + if ($2 < 8){ + print ("\n\n"); + print ("Warning: The Version of 'mp3info' on your system is $1.$2.$3\n"); + print (" At least version 0.8.0 is required. You can get it\n"); + print (" from http://www.ibiblio.org/mp3info\n"); + print ("\n\n"); + exit(0); + } + } + else{ + print ("Warning: could not extract version number of 'mp3info'\n"); + print (" 'mp3info' is not installed?\n"); + exit(0); + } +} + + + +sub db_update_094{ + my ($dbh) = @_; + my $res; + + ### Bitrate + $res = $dbh->do("SHOW COLUMNS FROM tracks LIKE 'bitrate'"); + if ($res < 1){ + print("Update table tracks to version 0.94\n"); + print("Adding bitrate field to table tracks\n"); + $dbh->do("ALTER TABLE tracks ADD COLUMN bitrate CHAR(4) AFTER lyrics"); + + ### Update records + my $base = gdparams::gdbase(); + my ($sth, $count, $row, $fname, $bitrate); + $sth = $dbh->prepare("SELECT * FROM tracks WHERE bitrate IS NULL OR bitrate=''"); + $count = $sth->execute; + print("I have to update the bitrate of $count records\n"); + while($row = $sth->fetchrow_hashref){ + $fname = `ls $base/[0-9][0-9]/$row->{mp3file}`; # get full path + chop($fname); + $bitrate = gdgentools::get_bitrate_str($fname); + print("Set bitrate of $row->{artist}/$row->{title} to $bitrate\n"); + $dbh->do( "UPDATE tracks SET bitrate='$bitrate' WHERE id=$row->{id}"); + + } + $sth->finish; + } +#else {print("-- column bitrate exists -> DB needs not be updated\n");} + + + ### Table player + $res = $dbh->do("SHOW TABLES LIKE 'player'"); + if ($res < 1){ + print("player does not exist (upgrading...)\n"); + $dbh->do( + "create table player( " + ."ipaddr varchar(255) not null," + ."uichannel varchar(255) not null," + ."logtarget int," + ."cdripper varchar(255)," + ."mp3encoder varchar(255)," + ."cdromdev varchar(255)," + ."cdrwdev varchar(255)," + ."id int not null," + ."primary key(id)" + .")"); + } +#else{print("-- player does exist\n");} + + + ### Table playerstate + $res = $dbh->do("SHOW TABLES LIKE 'playerstate'"); + if ($res < 1){ + print("playerstate does not exist (upgrading...)\n"); + $dbh->do( + "create table playerstate(" + ."playerid int not null," + ."playertype int not null," + ."snddevice varchar(255)," + ."playerapp varchar(255)," + ."playerparams varchar(255)," + ."ptlogger varchar(255)," + ."currtracknb int," + ."state varchar(4)," + ."pauseframe int, " + ."framesplayed int," + ."framesremain int," + ."primary key(playerid, playertype)" + .")"); + } +#else{print("-- playerstate does exist\n");} + + + ### Table tracklistitem + $res = $dbh->do("SHOW TABLES LIKE 'tracklistitem'"); + if ($res < 1){ + print("tracklistitem does not exist (upgrading...)\n"); + $dbh->do( + "create table tracklistitem(" + ."playerid int not null," + ."listtype smallint not null," + ."tracknb int not null," + ."trackid int not null," + ."primary key(playerid, listtype, tracknb)" + .")"); + } +#else{print("-- tracklistitem does exist\n");} + +} + +############################################################################### +### Version 0.92 +############################################################################### + +sub fix_leading_slash_bug +### removes leading / in the column tracks.mp3file +{ + my ($dbh) = @_; + my $numrec = $dbh->do( "UPDATE tracks SET mp3file=SUBSTRING(mp3file,2) " + ."WHERE mp3file LIKE '/%'"); + if ($numrec>0){ + print("fix_leading_slash_bug: $numrec records fixed!\n\n"); + } +} + + + +############################################################################### + +END{ + ; +} + + +# +1; diff --git a/scripts/genres.txt b/scripts/genres.txt new file mode 100755 index 0000000..2a734f0 --- /dev/null +++ b/scripts/genres.txt @@ -0,0 +1,224 @@ +b 20 Alternative +ba 40 Alternative General +bb \N Art Rock +bc 90 Avant Rock +be \N Experimental +bh 6 Grunge +bi \N Indie +bm 12 Unclassifiable +bn \N Crossover +c \N Books & Spoken +ca \N Short Stories +cb 57 Comedy +cc 77 Musicals/Broadway +cd \N Poetry +ce \N Cabaret / Satire +cf \N Religion +cg 101 Spoken Word +ch \N Stories/Fairytales +ci \N Radio Play +cia \N Literary Radio Play +cib \N Thriller +e 32 Classical +ea 104 Chamber Music +eaa 105 Sonata +eb \N Classical General +ec \N Contemporary +eca \N Contemp. Crossover +ecb \N Electronic Classical +ecc \N Experimental Classical +ecd \N Minimal Music +ed \N Film Music +ee 33 Instrumental +ef \N Period Music +efa \N Baroque +efb \N Medieval +efc \N Renaissance +efd \N Romantic +efda \N 19th Century +eg \N Solo Instruments +ega \N Guitar +egb \N Percussion +egc \N Piano +eh 106 Symphonic +ei 28 Classic Vocal +eia 97 Choral +eib \N Ensembles +eic 103 Opera +ej \N Baroque +f 2 Country +fa \N Alternative Country +fb 89 Bluegrass +fd \N Country Blues +fe \N Country General +fg \N Country and Western +fh 80 Folk +fha \N Irish Folk +fi \N Rockabilly +g 98 Easy Listening +gb \N Lounge +gc \N Love Songs +gca 116 Ballads +gd \N Mood Music +ge 10 New Age +gf \N Soft Rock +gfa \N Acoustic Rock +gg \N Schlager +gh \N Soft Pop +gi 45 Meditative +gj \N Pair Dance +gja \N Walz +gjb \N Tango +h 102 Songs/Chansons +ha \N Singer-Songwriter +hb 65 Children's Music +i 52 Electronic +ia 34 Acid +ib 26 Ambient +ic \N Breakbeat/Breaks +ica \N Breakbeat +icb \N Darkside +icc 63 Jungle +icd \N Ragga +ice 27 Trip Hop +id 3 Dance +ie \N Drum n' Bass +if \N Electronica +ig \N Envir. Soundscapes +ih \N Experimental Elect. +iha \N Minimal Experimental +ihb 39 Noise +ii 37 Game Soundtracks +ij 35 House +ija \N Acid House +ijb \N Funk House +ijc \N Hard House +ijd \N Progressive House +ik 19 Industrial +il 18 Techno +ilb \N Dub +ild \N Goa +ile \N Hardcore Techno +ilf \N Illbient +ilg \N Minimal +ilh 25 Old Skool Techno +ili 68 Rave +ilj 31 Trance +im 44 Space Music +j \N Hip Hop/Rap +ja 7 Hip Hop +jb 15 Rap +jbb 61 Christian Rap +jbd \N Hardcore Rap +jbf 59 Gangsta +k \N Blues/R&B +ka 0 Blues +kaa \N Acoustic Blues +kab \N Blues Rock +kac \N Blues Vocalist +kae \N Electric Blues +kag \N Jazz Blues +kb 38 Gospel +kc \N Improvised +kd 14 R&B +ke 42 Soul +kea \N Sweet Soul +l 8 Jazz +la 73 Acid Jazz +lb 85 Bebop +lc \N Dancefloor Jazz +lf 30 Jazz Fusion +lh \N Jazz Vocals +lj \N Ragtime +lk \N Smooth Jazz +ll 83 Swing +lla 96 Big Band +llb 76 Retro-Swing +lm \N Cool Jazz +ln \N Ethno Jazz +lna \N African Ethno Jazz +lnb \N Arab Ethno Jazz +lnc \N Cuban Jazz +lnd \N Latin Jazz +lne \N Far East Jazz +lo \N Modern Jazz +lp \N New Orleans Brass +m \N Pop & Rock +ma \N Country Rock +mb 5 Funk +mba \N Acid Funk +mc 9 Metal +mcc 22 Death Metal +mcd \N Doom Metal +mcf \N Hard Core Metal +mcg \N Heavy Metal +mck \N Thrash/Speed Metal +md 13 Pop +mda 99 Acoustic +mdb \N Synthesizer Pop +mdd \N Latin Pop +mdg 123 A capella/Pop Vocals +mdh \N Neue Deutsche Welle +mdi \N Disco +mdj \N Dance Pop +mdja \N Twist +mdk \N Doo-Wop +me 43 Punk +mea 121 Hardcore/Punk Rock +meb 71 Lo-Fi/Garage +mec \N Old School Punk +med 21 Ska +mf 17 Rock +mfb \N Acid Rock +mfe 81 Folk Rock +mfg \N Groove Rock +mfh \N Guitar Rock +mfha 1 Classic Rock +mfhb \N Improv Rock +mfhc 47 Instrumental Rock +mfhf \N Surf Rock +mfj 66 New Wave +mfk 67 Psychedelic +mfl 78 Rock & Roll +mfm 94 Symphonic Rock +mg \N Rock En Espanol +n \N World +na 16 Reggae +nb \N Steel Drums +nc \N World Popular +nca \N African Pop +ncb \N Oriental Pop +ncc \N Scandinavian Ethnopop +ncd \N Asian Pop +nce \N Arabian Pop +ncf \N European Ethnopop +ncg \N Latin Pop +nch \N Caribbean Pop +nd 82 World Traditions +nda \N African +ndaa \N Mali Blues +ndb \N Arabic +ndc \N Asian +ndd \N Bossa Nova +nde \N Caribbean +ndf 88 Celtic +ndg 53 European Folk/Pop +ndga \N Jodel +ndh \N France +ndi \N Germany +ndj \N India +ndk \N Ireland +ndl 86 Latin +ndlb \N Flamenco +ndlc \N Mambo +ndld \N Mariachi +ndle \N Meringue +ndlg \N Salsa +ndlh 114 Samba +ndm 64 Native American +ndn \N Quebecois +ndo \N Russian +ndp \N South/Cent. American +ndq \N Spain +ndr 113 Tango + NULl diff --git a/scripts/languages.txt b/scripts/languages.txt new file mode 100755 index 0000000..68dfdec --- /dev/null +++ b/scripts/languages.txt @@ -0,0 +1,45 @@ +- Instrumental +CHde Swiss German +af Afrikaans +ar Arabic +bg Bulgarian +bn Bengali; Bangla +bo Tibetan +cs Czech +da Danish +de German +el Greek +en English +eo Esperanto +es Spanish +fi Finnish +fr French +hi Hindi +hu Hungarian +is Icelandic +it Italian +iw Hebrew +ja Japanese +ku Kurdish +la Latin +lt Lithuanian +lv Latvian, Lettish +nl Dutch +no Norwegian +pl Polish +pt Portuguese +rm Rhaeto-Romance +ro Romanian +ru Russian +sh Serbo-Croatian +sk Slovak +sl Slovenian +sq Albanian +sr Serbian +sv Swedish +ta Tamil +th Thai +tr Turkish +vi Vietnamese +zh Chinese +zu African diff --git a/scripts/make-db b/scripts/make-db new file mode 100755 index 0000000..ec25531 --- /dev/null +++ b/scripts/make-db @@ -0,0 +1,8 @@ +#!/bin/sh + +export SCRIPTDIR=/home/andi/muggle/import +cd $SCRIPTDIR +echo "creating db" +mysql < createdb.mysql + +# diff --git a/scripts/make-empty-db b/scripts/make-empty-db new file mode 100755 index 0000000..7157539 --- /dev/null +++ b/scripts/make-empty-db @@ -0,0 +1,24 @@ +#!/bin/sh + +export SCRIPTDIR=/home/andi/muggle/import +cd $SCRIPTDIR + +echo "creating db" +mysql < createdb.mysql + +echo "creating tables" +mysql < $SCRIPTDIR/createtables.mysql + +echo "reading genres" +echo " use GiantDisc; load data local infile '$SCRIPTDIR/genres.txt' into table genre;"| mysql --local-infile=1 + + +echo "reading languages" +echo "use GiantDisc; load data local infile '$SCRIPTDIR/languages.txt' into table language;" | mysql --local-infile=1 + +echo "reading musictypes" +echo "use GiantDisc; load data local infile '$SCRIPTDIR/musictypes.txt' into table language;" | mysql --local-infile=1 + + +echo "reading sources" +echo "use GiantDisc; load data local infile '$SCRIPTDIR/sources.txt' into table language;" | mysql --local-infile=1 diff --git a/scripts/make-tables b/scripts/make-tables new file mode 100755 index 0000000..1dc9c01 --- /dev/null +++ b/scripts/make-tables @@ -0,0 +1,26 @@ +#!/bin/sh + +################################################## +# +# GiantDisc mp3 Jukebox +# +# © 2000-2002, Rolf Brugger +# +################################################## + +cd /home/andi/muggle/import + +echo "creating tables" +mysql < createtables.mysql + +echo "reading genres" +mysql --local-infile=1 < readgenres.mysql + +echo "reading languages" +mysql --local-infile=1 < readlanguages.mysql + +echo "reading musictypes" +mysql --local-infile=1 < readmusictypes.mysql + +echo "reading sources" +mysql --local-infile=1 < readsources.mysql diff --git a/scripts/musictypes.txt b/scripts/musictypes.txt new file mode 100755 index 0000000..50ca2c3 --- /dev/null +++ b/scripts/musictypes.txt @@ -0,0 +1,4 @@ +soft/slow +medium +groovy +hard diff --git a/scripts/myhash.pm b/scripts/myhash.pm new file mode 100755 index 0000000..5d8f1bd --- /dev/null +++ b/scripts/myhash.pm @@ -0,0 +1,29 @@ +package myhash; + + +################################################## +# +# GiantDisc mp3 Jukebox +# +# © 2000, Rolf Brugger +# +################################################## + +#use lib '/usr/local/bin'; +#BEGIN{;} +#END{;} + + +############################################################ +### +sub addvaltohash{ # gets a current hashval and a new elment + # returns new hashval + my ($hashval,$newelement) = @_; + + return (($hashval << 5) ^ ($hashval >> 27)) ^ $newelement; + # (^ is bitwise EXOR) +} + + +1; +# diff --git a/scripts/sources.txt b/scripts/sources.txt new file mode 100755 index 0000000..b900632 --- /dev/null +++ b/scripts/sources.txt @@ -0,0 +1,6 @@ +cd +radio +vinyl +tape +tv +video |