summaryrefslogtreecommitdiff
path: root/scripts/gdupdate.pm
diff options
context:
space:
mode:
Diffstat (limited to 'scripts/gdupdate.pm')
-rwxr-xr-xscripts/gdupdate.pm509
1 files changed, 509 insertions, 0 deletions
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;
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-15 10:08:47 +0000