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