[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Librefm-commits] [1103] test out Scrobble_Track table
From: |
Clint Adams |
Subject: |
[Librefm-commits] [1103] test out Scrobble_Track table |
Date: |
Sat, 02 May 2009 02:27:53 +0000 |
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();
[Prev in Thread] |
Current Thread |
[Next in Thread] |
- [Librefm-commits] [1103] test out Scrobble_Track table,
Clint Adams <=