/**
* Create/update schema in database.
*/
public static function update_schema()
{
global $wpdb;
$sql = "\n\n CREATE TABLE " . Track::table_name() . " (\n id int(11) NOT NULL AUTO_INCREMENT,\n stream_title varchar(200) NOT NULL,\n track_key varchar(200) NOT NULL,\n artist varchar(100) NOT NULL,\n title varchar(100) NOT NULL,\n play_count int(11) NOT NULL,\n vote_count int(11) NOT NULL,\n vote_total int(11) DEFAULT NULL,\n vote_average double DEFAULT NULL,\n PRIMARY KEY (id),\n UNIQUE KEY (track_key),\n KEY (vote_average),\n KEY (play_count),\n KEY (vote_count),\n KEY (vote_total)\n ) CHARACTER SET utf8;\n\n CREATE TABLE " . Play::table_name() . " (\n id int(11) NOT NULL AUTO_INCREMENT,\n time_utc datetime NOT NULL,\n track_id int(11) NOT NULL,\n stream_title varchar(200) NOT NULL,\n PRIMARY KEY (id),\n KEY (track_id),\n KEY (time_utc),\n CONSTRAINT " . Play::table_name() . "_ibfk_1 FOREIGN KEY (track_id) REFERENCES " . Track::table_name() . " (id)\n ) CHARACTER SET utf8;\n\n CREATE TABLE " . Vote::table_name() . " (\n id int(11) NOT NULL AUTO_INCREMENT,\n time_utc datetime NOT NULL,\n track_id int(11) NOT NULL,\n stream_title varchar(200) NOT NULL,\n value tinyint(4) NOT NULL,\n nick varchar(30) NOT NULL,\n user_id varchar(150) NOT NULL,\n is_authed bit(1) NOT NULL,\n deleted tinyint(4) NOT NULL DEFAULT '0',\n comment varchar(200) NULL,\n PRIMARY KEY (id),\n KEY (track_id),\n KEY (time_utc),\n KEY (nick),\n CONSTRAINT " . Vote::table_name() . "_ibfk_1 FOREIGN KEY (track_id) REFERENCES " . Track::table_name() . " (id)\n ) CHARACTER SET utf8;\n\n ";
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
dbDelta($sql);
}
public static function playlist($start_time, $end_time)
{
global $wpdb;
if (!$start_time && !$end_time) {
return NULL;
}
$cond_parm = array();
if ($start_time) {
$cond[] = 'p.time_utc >= %s';
$cond_parm[] = $start_time;
}
if ($end_time) {
$cond[] = 'p.time_utc < %s';
$cond_parm[] = $end_time;
}
/* echo "<pre>" . $wpdb->prepare (
"
SELECT p.time_utc, t.artist, t.title, (
SELECT SUM(value) FROM ".Vote::table_name()." pv
WHERE pv.deleted=0
AND pv.time_utc >= p.time_utc AND pv.time_utc < p.time_utc + INTERVAL 30 MINUTE
) vote_total
FROM ".Play::table_name()." p
LEFT JOIN ".Track::table_name()." t on t.id=p.track_id
WHERE " . implode( ' AND ', $cond ) . "
ORDER BY p.time_utc ASC
"
, $cond_parm
); exit; */
return $wpdb->get_results($wpdb->prepare("\n SELECT p.time_utc, t.artist, t.title, (\n SELECT SUM(value) FROM " . Vote::table_name() . " pv\n WHERE pv.deleted=0 AND pv.track_id=p.track_id\n AND pv.time_utc >= p.time_utc AND pv.time_utc < p.time_utc + INTERVAL 30 MINUTE\n ) vote_total\n FROM " . Play::table_name() . " p\n LEFT JOIN " . Track::table_name() . " t on t.id=p.track_id\n WHERE " . implode(' AND ', $cond) . "\n ORDER BY p.time_utc ASC\n ", $cond_parm), ARRAY_A);
}
/**
* Was this 'track_id' played in the last 60 minutes?
* @param int $track_id
* @return boolean
*/
public static function is_recently_played($track_id)
{
global $wpdb;
$count = $wpdb->get_var($wpdb->prepare("\n SELECT count(*) FROM " . Play::table_name() . "\n WHERE track_id=%d\n AND timestampdiff(minute, time_utc, utc_timestamp()) < 60\n ", $track_id));
return $count > 0;
}
