[Librefm-commits] [1103] test out Scrobble_Track table

[Clint Adams]

Revision: 1103
          http://svn.sv.gnu.org/viewvc/?view=rev&root=librefm&revision=1103
Author:   clint
Date:     2009-05-02 02:27:52 +0000 (Sat, 02 May 2009)
Log Message:
-----------
test out Scrobble_Track table

Modified Paths:
--------------
    trunk/gnukebox/install.php
    trunk/gnukebox/nowplaying/1.2/index.php
    trunk/gnukebox/scrobble-utils.php
    trunk/gnukebox/submissions/1.2/index.php

Modified: trunk/gnukebox/install.php
===================================================================
--- trunk/gnukebox/install.php  2009-05-01 21:06:27 UTC (rev 1102)
+++ trunk/gnukebox/install.php  2009-05-02 02:27:52 UTC (rev 1103)
@@ -167,7 +167,8 @@
                mbid VARCHAR(36),
                source VARCHAR(6),
                rating CHAR(1),
-               length INTEGER)");
+               length INTEGER,
+               stid INTEGER)");
 
        $mdb2->query("CREATE TABLE Scrobble_Sessions(
                username VARCHAR(64) REFERENCES Users(username),
@@ -226,6 +227,14 @@
                        expires INTEGER NOT NULL DEFAULT 0,
                        PRIMARY KEY(username,session))");
 
+       $res = $mdb2->exec("CREATE TABLE Scrobble_Track(
+                       id SERIAL PRIMARY KEY,
+                       artist VARCHAR(255) NOT NULL,
+                       album VARCHAR(255),
+                       name VARCHAR(255) NOT NULL,
+                       mbid VARCHAR(36),
+                       track INTEGER NOT NULL)");
+
        $res = $mdb2->exec("CREATE VIEW Free_Scrobbles AS SELECT s.* FROM 
Scrobbles s INNER JOIN Track t on lower(s.artist)=lower(t.artist) and 
lower(s.track)=lower(t.name) where t.streamable=1");
 
 

Modified: trunk/gnukebox/nowplaying/1.2/index.php
===================================================================
--- trunk/gnukebox/nowplaying/1.2/index.php     2009-05-01 21:06:27 UTC (rev 
1102)
+++ trunk/gnukebox/nowplaying/1.2/index.php     2009-05-02 02:27:52 UTC (rev 
1103)
@@ -56,7 +56,7 @@
 if($album != 'NULL') {
        createAlbumIfNew($artist, $album);
 }
-createTrackIfNew($artist, $album, $track, $mbid);
+getTrackCreateIfNew($artist, $album, $track, $mbid);
 
 //Expire old tracks
 $mdb2->query("DELETE FROM Now_Playing WHERE expires < " . time());

Modified: trunk/gnukebox/scrobble-utils.php
===================================================================
--- trunk/gnukebox/scrobble-utils.php   2009-05-01 21:06:27 UTC (rev 1102)
+++ trunk/gnukebox/scrobble-utils.php   2009-05-02 02:27:52 UTC (rev 1103)
@@ -97,16 +97,16 @@
        }
 }
 
-function createTrackIfNew($artist, $album, $track, $mbid) {
+function getTrackCreateIfNew($artist, $album, $track, $mbid) {
        global $mdb2;
 
        $track = NoSpamTracks($track);
        $artist = NoSpamTracks($artist);
 
        if($album) {
-       $res = $mdb2->query("SELECT name FROM Track WHERE lower(name) = " . 
(strtolower($track)) . " AND lower(artist) = " . (strtolower($artist)) . " AND 
lower(album) = " . strtolower($album));
+       $res = $mdb2->query("SELECT id FROM Track WHERE lower(name) = " . 
(strtolower($track)) . " AND lower(artist) = " . (strtolower($artist)) . " AND 
lower(album) = " . strtolower($album));
        } else {
-       $res = $mdb2->query("SELECT name FROM Track WHERE lower(name) = " . 
(strtolower($track)) . " AND lower(artist) = " . (strtolower($artist)) . "AND 
album IS NULL");
+       $res = $mdb2->query("SELECT id FROM Track WHERE lower(name) = " . 
(strtolower($track)) . " AND lower(artist) = " . (strtolower($artist)) . "AND 
album IS NULL");
        }
        if(PEAR::isError($res)) {
                die("FAILED " . $res->getMessage() . "\n");
@@ -122,9 +122,37 @@
                if(PEAR::isError($res)) {
                        die("FAILED " . $res->getMessage() . "\n");
                }
+               return getTrackCreateIfNew($artist, $album, $track, $mbid);
+       } else {
+               return $res->fetchOne(0);
        }
 }
 
+function getScrobbleTrackCreateIfNew($artist, $album, $track, $mbid, $tid) {
+       global $mdb2;
+
+       $res = $mdb2->query("SELECT id FROM Scrobbles_Track WHERE name = " . 
(strtolower($track)) . " AND artist = " . (strtolower($artist)) . " AND album = 
" . strtolower($album) . " AND mbid = " . strtolower($mbid));
+       if(PEAR::isError($res)) {
+               die("FAILED " . $res->getMessage() . "\n");
+       }
+
+       if(!$res->numRows()) {
+               // Create new track
+               $res = $mdb2->exec("INSERT INTO Scrobbles_Track (name, artist, 
album, mbid, track) VALUES ("
+                       . strtolower($track) . ", "
+                       . strtolower($artist) . ", "
+                       . strtolower($album) . ", "
+                       . strtolower($mbid) . ","
+                       . strtolower($tid) . ")");
+               if(PEAR::isError($res)) {
+                       die("FAILED " . $res->getMessage() . "\n");
+               }
+               return getScrobbleTrackCreateIfNew($artist, $album, $track, 
$mbid, $tid);
+       } else {
+               return $res->fetchOne(0);
+       }
+}
+
 function scrobbleExists($username, $artist, $track, $time) {
        global $mdb2;
 

Modified: trunk/gnukebox/submissions/1.2/index.php
===================================================================
--- trunk/gnukebox/submissions/1.2/index.php    2009-05-01 21:06:27 UTC (rev 
1102)
+++ trunk/gnukebox/submissions/1.2/index.php    2009-05-02 02:27:52 UTC (rev 
1103)
@@ -108,7 +108,8 @@
        if($album != 'NULL') {
                createAlbumIfNew($artist, $album);
        }
-       createTrackIfNew($artist, $album, $track, $mbid);
+       $tid = getTrackCreateIfNew($artist, $album, $track, $mbid);
+       $stid = getScrobbleTrackCreateIfNew($artist, $album, $track, $mbid, 
$tid);
 
        $exists = scrobbleExists($username, $artist, $track, $time);
 
@@ -123,7 +124,8 @@
                . $mbid . ", "
                . $source . ","
                . $rating . ","
-               . $length . ")";
+               . $length . ",";
+               . $stid . ")";
 
        $actualcount++;
        }
@@ -135,7 +137,7 @@
                for($j = 0; $j < $actualcount; $j++) {
 
        // Scrobble!
-               $sql = "INSERT INTO Scrobbles (username, artist, album, track, 
time, mbid, source, rating, length) VALUES " . $rowvalues[$j];
+               $sql = "INSERT INTO Scrobbles (username, artist, album, track, 
time, mbid, source, rating, length, stid) VALUES " . $rowvalues[$j];
                $res =& $mdb2->exec($sql);
                if(PEAR::isError($res)) {
                    $msg = $res->getMessage() . " - " . $res->getUserInfo();