function rsscache_sql($c, $q, $f, $v, $start, $num, $v_segments = NULL) { /* $c == category $q == query $f == function $v == video (rsstool_url_crc32) LIMIT $start, $num $table_suffix is optional and set in config.xml */ global $rsscache_sql_db, $rsscache_isnew, $rsscache_root, $rsscache_enable_search, $rsscache_related_search, $rsscache_use_dl_date, $rsscache_max_results, $rsscache_time, $rsscache_item_ttl; global $rsscache_debug_sql; $debug = $rsscache_debug_sql; // $debug = 1; $category0_xml = config_xml(); // DEBUG //echo '<pre><tt>'; //print_r ($category0_xml); //exit; $c = 'c64'; $category_xml = config_xml_by_category($c); $c = $category_xml['category']; // safety // DEBUG //echo '<pre><tt>'; //echo $c; //print_r ($category_xml); //exit; if ($f == 'stats') { return rsscache_sql_stats($rsscache_sql_db, $c); } if ($f == 'extern') { return rsscache_sql_extern($c, $q, $f, $v, $start, $num); } $rsstool_table = rsscache_tablename_by_category('rsstool', $c); $sql_query_s = ''; $sql_query_s .= '' . 'SELECT' . ' rsstool_url,' . ' ' . $rsstool_table . '.rsstool_url_crc32,' . ' rsstool_title,' . ' rsstool_desc,' . ' rsstool_dl_date,' . ' rsstool_date,' . ' tv2_moved,' . ' rsstool_event_start,' . ' rsstool_event_end,' . ' rsstool_media_duration,' . ' rsstool_keywords,' . ' rsstool_user,' . ' rsstool_related_id'; // direct if ($v) { $sql_query_s .= ' FROM ' . $rsstool_table; $sql_query_s .= ' WHERE ( rsstool_url_crc32 = ' . $v . ' )' . ' LIMIT 1'; $d = rsscache_sql_query($sql_query_s); $d = rsscache_sql_normalize($d); return $d; } // related search if ($rsscache_related_search && $f == 'related') { $sql_query_s .= ' FROM ' . $rsstool_table; $a = array(); // category if ($c) { $a[] = 'tv2_moved = \'' . $c . '\''; } // $a[] = 'rsstool_related_id = '.misc_related_string_id ($q); // super fast $a[] = 'rsstool_related_id = ' . sprintf("%u", $q); // super fast // $a[] = 'rsstool_related_id = '.$q; // super fast if (isset($a[0])) { $sql_query_s .= ' WHERE ( ' . implode(' AND ', $a) . ' )'; } // we sort related by title for playlist $sql_query_s .= ' ORDER BY rsstool_title ASC'; // limit $sql_query_s .= ' LIMIT ' . $rsscache_max_results; $d = rsscache_sql_query($sql_query_s); $d = rsscache_sql_normalize($d); return $d; } // keyword search if ($rsscache_enable_search && $q) { if ($v_segments) { if (trim($v_segments) != '') { $q .= ' +part'; } } $b = rsscache_sql_query2boolean($q, $c); $sql_query_s .= ' FROM ' . rsscache_sql_keyword_func($b['any'], $b['require'], $b['exclude'], isset($category_xml['rsscache:table_suffix']) ? $category_xml['rsscache:table_suffix'] : NULL); } else { $sql_query_s .= ' FROM ' . $rsstool_table; } // category if ($c) { $a[] = 'tv2_moved = \'' . $c . '\''; } // functions if ($f == 'new') { $a[] = 'rsstool_dl_date > ' . ($rsscache_time - $rsscache_isnew) . ''; } if ($rsscache_item_ttl > 0) { $a[] = 'rsstool_date > ' . ($rsscache_time - $rsscache_item_ttl); } if ($f == '0_5min') { $a[] = 'rsstool_media_duration BETWEEN 0 AND 301'; } else { if ($f == '5_10min') { $a[] = 'rsstool_media_duration BETWEEN 299 AND 601'; } else { if ($f == '10_30min') { $a[] = 'rsstool_media_duration BETWEEN 599 AND 1801'; } else { if ($f == '30_60_min') { $a[] = 'rsstool_media_duration BETWEEN 1799 AND 3601'; } else { if ($f == '60_min' || $f == '1_h') { $a[] = 'rsstool_media_duration > 3599'; } } } } } if (isset($a[0])) { $sql_query_s .= ' WHERE ( ' . implode(' AND ', $a) . ' )'; } // sort // if ($f == 'score') // $sql_query_s .= ' ORDER BY tv2_score ASC'; // else if ($f == 'new' || $rsscache_use_dl_date) { $sql_query_s .= ' ORDER BY rsstool_dl_date DESC'; } else { $sql_query_s .= ' ORDER BY rsstool_date DESC'; } // limit $sql_query_s .= ' LIMIT ' . $start . ',' . min($num, $rsscache_max_results); $d = rsscache_sql_query($sql_query_s); $d = rsscache_sql_normalize($d); return $d; }
// (manual) maintenance $start = 0; //$start = 177470; // emulive //$start = 1125066; // video //613327 //677000 //742400 $start = $argv[1]; $num = 50; for ($i = $start; $i < $rows; $i += $num) { // current num echo $i . "\n"; $sql_query_s = 'SELECT rsstool_title,rsstool_desc,rsstool_url,rsstool_url_crc32,rsstool_keywords' . ' FROM rsstool_table' . $table_suffix . ' LIMIT ' . $i . ',' . $num . ';'; // $db->sql_write ($sql_query_s, $debug); // $r = $db->sql_read (0 /* $debug */); rsscache_sql_query($sql_query_s); for ($j = 0; isset($r[$j]); $j++) { // current row // echo ($i + $j)."\n"; // current row // rsscache_update_normalize ($r[$j]); // normalize // rsscache_update_keywords ($r[$j]); // update keywords // rsscache_update_related_id ($r[$j]); // update related id // rsscache_update_crc32 ($r[$j]); // update crc32 checksums // rsscache_update_dead_links ($r[$j]['rsstool_url'], $r[$j]['rsstool_url_crc32']); // check dead link // youtube specific if (strstr($r[$j]['rsstool_url'], '.youtube.')) { // youtube_thumbnail ($r[$j]['rsstool_url']); // get (missing) youtube thumbnails } } if ($j < $num) { break;