From 2faea5f7b345ba0d9319d4466261b52c7bff0325 Mon Sep 17 00:00:00 2001 From: Karl 'vollmerk' Vollmer Date: Mon, 23 Oct 2006 01:47:18 +0000 Subject: re-wroked stats which means major db update, some stats dealies may still not work correctly --- lib/class/object_count.class.php | 92 ----------------------- lib/class/stats.class.php | 154 +++++++++++++++++++++++++++++++++++++++ lib/class/update.class.php | 114 +++++++++++++++++++++++++++++ lib/class/user.class.php | 111 +++++++--------------------- lib/general.lib.php | 50 ++++++------- lib/init.php | 10 +-- lib/preferences.php | 11 ++- 7 files changed, 331 insertions(+), 211 deletions(-) delete mode 100644 lib/class/object_count.class.php create mode 100644 lib/class/stats.class.php (limited to 'lib') diff --git a/lib/class/object_count.class.php b/lib/class/object_count.class.php deleted file mode 100644 index 013a2e08..00000000 --- a/lib/class/object_count.class.php +++ /dev/null @@ -1,92 +0,0 @@ -$description); - - error_results($array,1); - $this->error_state = 1; - - return true; - - } // add_error - - - /*! - @function has_error - @discussion returns true if the name given has an error, - false if it doesn't - */ - function has_error($name) { - - $results = error_results($name); - - if (!empty($results)) { - return true; - } - - return false; - - } // has_error - - /*! - @function print_error - @discussion prints out the error for a name if it exists - */ - function print_error($name) { - - if ($this->has_error($name)) { - echo "
" . error_results($name) . "
\n"; - } - - } // print_error - -} //end error class -?> diff --git a/lib/class/stats.class.php b/lib/class/stats.class.php new file mode 100644 index 00000000..ba97badf --- /dev/null +++ b/lib/class/stats.class.php @@ -0,0 +1,154 @@ +validate_type($type); + $oid = sql_escape($oid); + $user = sql_escape($user); + + $sql = "INSERT INTO object_count (`object_type`,`object_id`,`date`,`user`) " . + " VALUES ('$type','$oid','$date','$user')"; + $db_results = mysql_query($sql,dbh()); + + } // insert + + /** + * get_top + * This returns the top X for type Y from the + * last conf('stats_threshold') days + */ + function get_top($count,$type) { + + $count = intval($count); + $type = $this->validate_type($type); + $date = time() - (86400*conf('stats_threshold')); + + /* Select Top objects counting by # of rows */ + $sql = "SELECT object_id,COUNT(id) AS `count` FROM object_count" . + " WHERE object_type='$type' AND date >= '$date'" . + " GROUP BY object_id ORDER BY COUNT(object_id) DESC LIMIT $count"; + $db_results = mysql_query($sql, dbh()); + + $results = array(); + + while ($r = mysql_fetch_assoc($db_results)) { + $results[] = $r; + } + + return $results; + + } // get_top + + /** + * get_user + * This gets all stats for atype based on user with thresholds and all + * If full is passed, doesn't limit based on date + */ + function get_user($count,$type,$user,$full='') { + + $count = intval($count); + $type = $this->validate_type($type); + $user = sql_escape($user); + + /* If full then don't limit on date */ + if ($full) { + $date = '0'; + } + else { + $date = time() - (86400*conf('stats_threshold')); + } + + /* Select Objects based on user */ + $sql = "SELECT object_id,COUNT(id) AS `count` FROM object_count" . + " WHERE object_type='$type' AND date >= '$date' AND user = '$user'" . + " GROUP BY object_id ORDER BY COUNT(object_id) DESC LIMIT $count"; + $db_results = mysql_query($sql, dbh()); + + $results = array(); + + while ($r = mysql_fetch_assoc($db_results)) { + $results[] = $r; + } + + return $results; + + } // get_user + + /** + * validate_type + * This function takes a type and returns only those + * which are allowed, ensures good data gets put into the db + */ + function validate_type($type) { + + switch ($type) { + case 'artist': + return 'artist'; + break; + case 'album': + return 'album'; + break; + case 'genre': + return 'genre'; + break; + case 'song': + default: + return 'song'; + break; + } // end switch + + } // validate_type + +} //Stats class +?> diff --git a/lib/class/update.class.php b/lib/class/update.class.php index ae45cffc..c3fdac68 100644 --- a/lib/class/update.class.php +++ b/lib/class/update.class.php @@ -298,6 +298,11 @@ class Update { '- Added vote tables to allow users to vote on localplay.
'; $version[] = array('version' => '333000','description' => $update_string); + + $update_string = '- Added new preferences for playback Allow config options, moved out of config file.
' . + '- Reworked object_count to store 1 ROW per stat, allowing for Top 10 this week type stats. This can take a very long time.
'; + + $version[] = array('version' => '333001','description' => $update_string); return $version; @@ -1879,5 +1884,114 @@ class Update { } // update_333000 + /** + * update_333001 + * This adds a few extra preferences for play types. This is still a stopgap + * for the roill based permissions that I hope to add in the next release + * Re-work the stats so that they are time specific, this will drastically + * increase the number of rows so we need to make it more efficient as well + */ + function update_333001() { + + /* Add in the allow preferences for system */ + $sql = "INSERT INTO preferences (`name`,`value`,`description`,`level`,`type`,`catagory`) " . + " VALUES ('allow_downsample_playback','0','Allow Downsampling','100','boolean','system')"; + $db_results = mysql_query($sql, dbh()); + + $sql = "INSERT INTO preferences (`name`,`value`,`description`,`level`,`type`,`catagory`) " . + " VALUES ('allow_stream_playback','1','Allow Streaming','100','boolean','system')"; + $db_results = mysql_query($sql, dbh()); + + $sql = "INSERT INTO preferences (`name`,`value`,`description`,`level`,`type`,`catagory`) " . + " VALUES ('allow_democratic_playback','0','Allow Democratic Play','100','boolean','system')"; + $db_results = mysql_query($sql, dbh()); + + $sql = "INSERT INTO preferences (`name`,`value`,`description`,`level`,`type`,`catagory`) " . + " VALUES ('allow_localplay_playback','0','Allow Localplay Play','100','boolean','system')"; + $db_results = mysql_query($sql, dbh()); + + $sql = "INSERT INTO preferences (`name`,`value`,`description`,`level`,`type`,`catagory`) " . + " VALUES ('stats_threshold','7','Statistics Day Threshold','25','integer','interface')"; + $db_results = mysql_query($sql,dbh()); + + /* Fix every users preferences */ + $sql = "SELECT * FROM user"; + $db_results = mysql_query($sql, dbh()); + + $user = new User(); + $user->fix_preferences('-1'); + + while ($r = mysql_fetch_assoc($db_results)) { + $user->fix_preferences($r['username']); + } // while results + + /* Store all current Stats */ + $sql = "SELECT * FROM object_count"; + $db_results = mysql_query($sql, dbh()); + + $results = array(); + + /* store in an array */ + while ($result = mysql_fetch_assoc($db_results)) { + $results[] = $result; + } // while results + + /* Alter the Table drop count and switch username to int */ + $sql = "TRUNCATE TABLE `object_count`"; + $db_results = mysql_query($sql,dbh()); + + $sql = "ALTER TABLE `object_count` DROP `count`"; + $db_results = mysql_query($sql, dbh()); + + $sql = "ALTER TABLE `object_count` CHANGE `userid` `user` INT ( 11 ) UNSIGNED NOT NULL"; + $db_results = mysql_query($sql,dbh()); + + /* We do this here because it's more important that they + * don't re-run the update then getting all their stats + */ + $this->set_version('db_version','333001'); + + // Prevent the script from timing out + set_time_limit(0); + + /* Foreach through the old stuff and dump it back into the fresh table */ + foreach ($results as $row) { + + /* Reset */ + $i=0; + + /* One row per count */ + while ($row['count'] > $i) { + + $object_type = sql_escape($row['object_type']); + $object_id = sql_escape($row['object_id']); + $date = sql_escape($row['date']); + $username = sql_escape($row['userid']); + if (!isset($cache[$username])) { + $tmp_user = get_user_from_username($row['userid']); + $username = $tmp_user->username; + $cache[$username] = $tmp_user; + } + else { + $tmp_user = $cache[$username]; + } + // FIXME:: User uid reference + $user_id = $tmp_user->uid; + + + $sql = "INSERT INTO `object_count` (`object_type`,`object_id`,`date`,`user`) " . + " VALUES ('$object_type','$object_id','$date','$user_id')"; + $db_results = mysql_query($sql, dbh()); + + $i++; + + } // end while we've got stuff + + + } // end foreach + + + } // update_333001 + } // end update class ?> diff --git a/lib/class/user.class.php b/lib/class/user.class.php index ba5c6af9..490a1b88 100644 --- a/lib/class/user.class.php +++ b/lib/class/user.class.php @@ -30,6 +30,7 @@ class User { //Basic Componets var $id; + var $uid; // HACK ALERT var $username; var $fullname; var $access; @@ -55,6 +56,7 @@ class User { $this->username = sql_escape($username); $info = $this->_get_info(); $this->id = $this->username; + $this->uid = $info->id; $this->username = $info->username; $this->fullname = $info->fullname; $this->access = $info->access; @@ -158,50 +160,47 @@ class User { */ function get_favorites($type) { - $sql = "SELECT * FROM object_count" . - " WHERE count > 0" . - " AND object_type = '$type'" . - " AND userid = '" . $this->username . "'" . - " ORDER BY count DESC LIMIT " . conf('popular_threshold'); - $db_result = mysql_query($sql, dbh()); + $web_path = conf('web_path'); + + $stats = new Stats(); + $results = $stats->get_user(conf('popular_threshold'),$type,$this->uid,1); $items = array(); - $web_path = conf('web_path'); - while ($r = @mysql_fetch_object($db_result) ) { + foreach ($results as $r) { /* If its a song */ if ($type == 'song') { - $data = new Song($r->object_id); - $data->count = $r->count; + $data = new Song($r['object_id']); + $data->count = $r['count']; $data->format_song(); $data->f_name = $data->f_link; $items[] = $data; } /* If its an album */ elseif ($type == 'album') { - $data = new Album($r->object_id); - $data->count = $r->count; + $data = new Album($r['object_id']); + $data->count = $r['count']; $data->format_album(); $items[] = $data; } /* If its an artist */ elseif ($type == 'artist') { - $data = new Artist($r->object_id); - $data->count = $r->count; + $data = new Artist($r['object_id']); + $data->count = $r['count']; $data->format_artist(); $data->f_name = $data->link; $items[] = $data; } /* If it's a genre */ elseif ($type == 'genre') { - $data = new Genre($r->object_id); - $data->count = $r->count; + $data = new Genre($r['object_id']); + $data->count = $r['count']; $data->format_genre(); $data->f_name = $data->link; $items[] = $data; } - } // end while + } // end foreach return $items; @@ -414,77 +413,23 @@ class User { } // update_last_seen - /*! - @function update_user_stats - @discussion updates the playcount mojo for this - specific user - */ + /** + * update_user_stats + * updates the playcount mojo for this specific user + */ function update_stats($song_id) { $song_info = new Song($song_id); - $user = $this->username; - $dbh = dbh(); + //FIXME:: User uid reference + $user = $this->uid; if (!$song_info->file) { return false; } - $time = time(); - - // Play count for this song - $sql = "UPDATE object_count" . - " SET date = '$time', count=count+1" . - " WHERE object_type = 'song'" . - " AND object_id = '$song_id' AND userid = '$user'"; - $db_result = mysql_query($sql, $dbh); - - $rows = mysql_affected_rows(); - if (!$rows) { - $sql = "INSERT INTO object_count (object_type,object_id,date,count,userid)" . - " VALUES ('song','$song_id','$time','1','$user')"; - $db_result = mysql_query($sql, $dbh); - } - - // Play count for this artist - $sql = "UPDATE object_count" . - " SET date = '$time', count=count+1" . - " WHERE object_type = 'artist'" . - " AND object_id = '" . $song_info->artist . "' AND userid = '$user'"; - $db_result = mysql_query($sql, $dbh); - - $rows = mysql_affected_rows(); - if (!$rows) { - $sql = "INSERT INTO object_count (object_type,object_id,date,count,userid)" . - " VALUES ('artist','".$song_info->artist."','$time','1','$user')"; - $db_result = mysql_query($sql, $dbh); - } - - // Play count for this album - $sql = "UPDATE object_count" . - " SET date = '$time', count=count+1" . - " WHERE object_type = 'album'" . - " AND object_id = '".$song_info->album."' AND userid = '$user'"; - $db_result = mysql_query($sql, $dbh); - - $rows = mysql_affected_rows(); - if (!$rows) { - $sql = "INSERT INTO object_count (object_type,object_id,date,count,userid)" . - "VALUES ('album','".$song_info->album."','$time','1','$user')"; - $db_result = mysql_query($sql, $dbh); - } - - // Play count for this genre - $sql = "UPDATE object_count" . - " SET date = '$time', count=count+1" . - " WHERE object_type = 'genre'" . - " AND object_id = '" . $song_info->genre."' AND userid='$user'"; - $db_results = mysql_query($sql, $dbh); - - $rows = mysql_affected_rows(); - if (!$rows) { - $sql = "INSERT INTO object_count (`object_type`,`object_id`,`date`,`count`,`userid`)" . - " VALUES ('genre','" . $song_info->genre."','$time','1','$user')"; - $db_results = mysql_query($sql, $dbh); - } - + $stats = new Stats(); + $stats->insert('song',$song_id,$user); + $stats->insert('album',$song_info->album,$user); + $stats->insert('artist',$song_info->artist,$user); + $stats->insert('genre',$song_info->genre,$user); } // update_stats @@ -562,7 +507,7 @@ class User { /* Calculate their total Bandwidth Useage */ $sql = "SELECT song.size FROM object_count LEFT JOIN song ON song.id=object_count.object_id " . - "WHERE object_count.userid='$this->id' AND object_count.object_type='song'"; + "WHERE object_count.userid='$this->uid' AND object_count.object_type='song'"; $db_results = mysql_query($sql, dbh()); while ($r = mysql_fetch_assoc($db_results)) { diff --git a/lib/general.lib.php b/lib/general.lib.php index f933ef94..d9823a40 100644 --- a/lib/general.lib.php +++ b/lib/general.lib.php @@ -535,13 +535,14 @@ function get_random_songs( $options, $matchlist) { $artists_where = ltrim($artists_where," OR"); $query = "SELECT song.id,song.size FROM song WHERE $artists_where ORDER BY RAND()"; } - elseif ($options['random_type'] == 'unplayed') { - $uid = $GLOBALS['user']->id; - $query = "SELECT song.id,song.size FROM song LEFT JOIN object_count ON song.id = object_count.object_id " . - "WHERE ($where) AND ((object_count.object_type='song' AND userid = '$uid') OR object_count.count IS NULL ) " . - "ORDER BY CASE WHEN object_count.count IS NULL THEN RAND() WHEN object_count.count > 4 THEN RAND()*RAND()*object_count.count " . - "ELSE RAND()*object_count.count END " . $options['limit']; - } // If unplayed +/* TEMP DISABLE */ +// elseif ($options['random_type'] == 'unplayed') { +// $uid = $GLOBALS['user']->id; +// $query = "SELECT song.id,song.size FROM song LEFT JOIN object_count ON song.id = object_count.object_id " . +// "WHERE ($where) AND ((object_count.object_type='song' AND user = '$uid') OR object_count.count IS NULL ) " . +// "ORDER BY CASE WHEN object_count.count IS NULL THEN RAND() WHEN object_count.count > 4 THEN RAND()*RAND()*object_count.count " . +// "ELSE RAND()*object_count.count END " . $options['limit']; +// } // If unplayed else { $query = "SELECT id,size FROM song WHERE $where ORDER BY RAND() " . $options['limit']; } @@ -606,47 +607,44 @@ function cleanup_and_exit($playing_id) { */ function get_global_popular($type) { - /* Select out the most popular based on object_count */ - $sql = "SELECT object_id, SUM(count) as count FROM object_count" . - " WHERE object_type = '$type'" . - " GROUP BY object_id" . - " ORDER BY count DESC LIMIT " . conf('popular_threshold'); - $db_result = mysql_query($sql,dbh()); - - $items = array(); + $stats = new Stats(); + $count = conf('popular_threshold'); $web_path = conf('web_path'); - - while ( $r = @mysql_fetch_object($db_result) ) { + + /* Pull the top */ + $results = $stats->get_top($count,$type); + + foreach ($results as $r) { /* If Songs */ if ( $type == 'song' ) { - $song = new Song($r->object_id); + $song = new Song($r['object_id']); $artist = $song->get_artist_name(); $text = "$artist - $song->title"; /* Add to array */ $items[] = "
  • id\" title=\"". scrub_out($text) ."\">" . - scrub_out(truncate_with_ellipse($text, conf('ellipse_threshold_title')+3)) . " ($r->count)
    • "; + scrub_out(truncate_with_ellipse($text, conf('ellipse_threshold_title')+3)) . " (" . $r['count'] . ") "; } // if it's a song /* If Artist */ elseif ( $type == 'artist' ) { - $artist = get_artist_name($r->object_id); + $artist = get_artist_name($r['object_id']); $items[] = "
  • object_id\" title=\"". scrub_out($artist) ."\">" . - scrub_out(truncate_with_ellipse($artist, conf('ellipse_threshold_artist')+3)) . " ($r->count)
    • "; + scrub_out(truncate_with_ellipse($artist, conf('ellipse_threshold_artist')+3)) . " (" . $r['count'] . ") "; } // if type isn't artist /* If Album */ elseif ( $type == 'album' ) { - $album = new Album($r->object_id); + $album = new Album($r['object_id']); $items[] = "
  • object_id\" title=\"". scrub_out($album->name) ."\">" . - scrub_out(truncate_with_ellipse($album->name,conf('ellipse_threshold_album')+3)) . " ($r->count)
    • "; + scrub_out(truncate_with_ellipse($album->name,conf('ellipse_threshold_album')+3)) . " (" . $r['count'] . ") "; } // else not album elseif ($type == 'genre') { - $genre = new Genre($r->object_id); + $genre = new Genre($r['object_id']); $items[] = "
  • object_id\" title=\"" . scrub_out($genre->name) . "\">" . - scrub_out(truncate_with_ellipse($genre->name,conf('ellipse_threshold_title')+3)) . " ($r->count)
    • "; + scrub_out(truncate_with_ellipse($genre->name,conf('ellipse_threshold_title')+3)) . " (" . $r['count'] . ") "; } // end if genre - } // end while + } // end foreach if (count($items) == 0) { $items[] = "
  • " . _('Not Enough Data') . "
    • \n"; diff --git a/lib/init.php b/lib/init.php index a3beaf4b..8b7621b9 100644 --- a/lib/init.php +++ b/lib/init.php @@ -72,15 +72,8 @@ if (!$results = read_config($configfile,0)) { exit(); } - -//FIXME: Until we have a config updater force stream as allowed playback method -if (!$results['allow_stream_playback']) { - $results['allow_stream_playback'] = "true"; -} - - /** This is the version.... fluf nothing more... **/ -$results['version'] = '3.3.3-Alpha1 (Build 004)'; +$results['version'] = '3.3.3-Alpha1 (Build 005)'; $results['raw_web_path'] = $results['web_path']; $results['web_path'] = $http_type . $_SERVER['HTTP_HOST'] . $results['web_path']; @@ -177,6 +170,7 @@ if (conf('ratings')) { // Classes require_once(conf('prefix') . '/lib/class/localplay.class.php'); +require_once(conf('prefix') . '/lib/class/stats.class.php'); require_once(conf('prefix') . '/lib/class/catalog.class.php'); require_once(conf('prefix') . '/lib/class/stream.class.php'); require_once(conf('prefix') . '/lib/class/playlist.class.php'); diff --git a/lib/preferences.php b/lib/preferences.php index 81957c67..bf995e4c 100644 --- a/lib/preferences.php +++ b/lib/preferences.php @@ -247,6 +247,10 @@ function create_preference_input($name,$value) { case 'no_symlinks': case 'use_auth': case 'access_control': + case 'allow_stream_playback': + case 'allow_downsample_playback': + case 'allow_democratic_playback': + case 'allow_localplay_playback': case 'demo_mode': case 'condPL': case 'direct_link': @@ -262,6 +266,7 @@ function create_preference_input($name,$value) { elseif ($value == 'localplay') { $is_local = 'selected="selected"'; } else { $is_stream = "selected=\"selected\""; } echo "\n"; break; case 'playlist_type': -- cgit