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, 0 insertions, 344 deletions
diff --git a/scripts/gddb.pm b/scripts/gddb.pm
deleted file mode 100755
index 88f5bea..0000000
--- a/scripts/gddb.pm
+++ /dev/null
@@ -1,344 +0,0 @@
-##################################################
-#
-# 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 11:05:48 +0000