function rsstool_write_ansisql($a, $rsscache_category, $table_suffix = NULL, $db_conn = NULL)
{
$sql_update = 0;
$rsscache_engine = 1;
$p = '';
$rsstool_table = rsscache_tablename('rsstool', $table_suffix);
$keyword_table = rsscache_tablename('keyword', $table_suffix);
$p .= '-- -----------------------------------------------------------' . "\n" . '-- RSStool - read, parse, merge and write RSS and Atom feeds' . "\n" . '-- -----------------------------------------------------------' . "\n" . "\n" . '-- DROP TABLE IF EXISTS ' . $rsstool_table . ';' . "\n" . '-- CREATE TABLE ' . $rsstool_table . ' (' . "\n" . '-- rsstool_url_crc32 int(10) unsigned NOT NULL default \'0\',' . "\n" . '-- rsstool_site text NOT NULL,' . "\n" . '-- rsstool_dl_url text NOT NULL,' . "\n" . '-- rsstool_dl_url_crc32 int(10) unsigned NOT NULL default \'0\',' . "\n" . '-- rsstool_title text NOT NULL,' . "\n" . '-- rsstool_title_crc32 int(10) unsigned NOT NULL default \'0\',' . "\n" . '-- rsstool_desc text NOT NULL,' . "\n" . '-- rsstool_date bigint(20) unsigned NOT NULL default \'0\',' . "\n" . '-- rsstool_dl_date bigint(20) unsigned NOT NULL default \'0\',' . "\n" . '-- rsstool_keywords text NOT NULL,' . "\n" . '-- rsstool_media_duration bigint(20) unsigned NOT NULL default \'0\',' . "\n" . '-- rsstool_image text NOT NULL,' . "\n" . '-- rsstool_event_start bigint(20) unsigned NOT NULL default \'0\',' . "\n" . '-- rsstool_event_end bigint(20) unsigned NOT NULL default \'0\',' . "\n" . '-- UNIQUE KEY rsstool_url_crc32 (rsstool_url_crc32),' . "\n" . '-- ) TYPE=MyISAM;' . "\n" . "\n";
$p .= '' . '-- DROP TABLE IF EXISTS ' . $rsstool_table . ';' . "\n" . '-- CREATE TABLE IF NOT EXISTS ' . $keyword_table . ' (' . "\n" . '-- rsstool_url_crc32 int(10) unsigned NOT NULL,' . "\n" . '-- rsstool_keyword_crc16 smallint(5) unsigned NOT NULL,' . "\n" . '-- PRIMARY KEY (rsstool_url_crc32,rsstool_keyword_crc16),' . "\n" . '-- KEY rsstool_keyword_16bit (rsstool_keyword_crc16)' . "\n" . '-- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;' . "\n" . "\n";
$items = count($a['item']);
for ($i = 0; $i < $items; $i++) {
if ($a['item'][$i]['link'] != '') {
// rsstool_table
$p .= 'INSERT IGNORE INTO ' . $rsstool_table . ' (' . ' rsstool_dl_url,' . ' rsstool_dl_url_crc32,' . ' rsstool_dl_date,' . ' rsstool_site,' . ' rsstool_url,' . ' rsstool_url_crc32,' . ' rsstool_date,' . ' rsstool_title,' . ' rsstool_title_crc32,' . ' rsstool_desc,' . ' rsstool_keywords,' . ' rsstool_related_id,' . ' rsstool_media_duration,' . ' rsstool_image,' . ' rsstool_user,' . ' rsstool_event_start,' . ' rsstool_event_end';
// HACK: rsscache category
if ($rsscache_engine == 1) {
$p .= ', tv2_category, tv2_moved';
}
$p .= ' ) VALUES (' . ' \'' . misc_sql_stresc($a['item'][$i]['rsscache:dl_url'], $db_conn) . '\',' . ' \'' . $a['item'][$i]['rsscache:dl_url_crc32'] . '\',' . ' \'' . $a['item'][$i]['rsscache:dl_date'] . '\',' . ' \'' . misc_sql_stresc($a['item'][$i]['rsscache:site'], $db_conn) . '\',' . ' \'' . misc_sql_stresc($a['item'][$i]['link'], $db_conn) . '\',' . ' \'' . $a['item'][$i]['rsscache:url_crc32'] . '\',' . ' \'' . $a['item'][$i]['pubDate'] . '\',' . ' \'' . misc_sql_stresc($a['item'][$i]['title'], $db_conn) . '\',' . ' \'' . $a['item'][$i]['rsscache:title_crc32'] . '\',' . ' \'' . misc_sql_stresc($a['item'][$i]['description'], $db_conn) . '\',' . ' \'' . misc_sql_stresc($a['item'][$i]['media_keywords'], $db_conn) . '\',' . ' ' . sprintf("%u", misc_related_string_id($a['item'][$i]['title'])) . ',' . ' \'' . $a['item'][$i]['media_duration'] * 1 . '\',' . ' \'' . $a['item'][$i]['image'] . '\',' . ' \'' . $a['item'][$i]['user'] . '\',' . ' \'' . $a['item'][$i]['event_start'] * 1 . '\',' . ' \'' . $a['item'][$i]['event_end'] * 1 . '\'';
// HACK: rsscache category
if ($rsscache_engine == 1) {
$p .= ', \'' . $rsscache_category . '\', \'' . $rsscache_category . '\'';
}
$p .= ' );' . "\n";
// UPDATE rsstool_table
$p .= '-- just update if row exists' . "\n";
if ($sql_update == 0) {
$p .= '-- ';
}
$p .= 'UPDATE ' . $rsstool_table . ' SET ' . ' rsstool_title = \'' . misc_sql_stresc($a['item'][$i]['title'], $db_conn) . '\',' . ' rsstool_title_crc32 = \'' . $a['item'][$i]['title_crc32'] . '\',' . ' rsstool_desc = \'' . misc_sql_stresc($a['item'][$i]['description'], $db_conn) . '\'' . ' WHERE rsstool_url_crc32 = ' . $a['item'][$i]['rsscache:url_crc32'] . ';' . "\n";
// keyword_table
$a = explode(' ', $a['item'][$i]['media_keywords']);
for ($j = 0; isset($a[$j]); $j++) {
if (trim($a[$j]) != '') {
$p .= 'INSERT IGNORE INTO ' . $keyword_table . ' (' . ' rsstool_url_crc32,' . ' rsstool_keyword_crc16' . ' ) VALUES (' . ' ' . $a['item'][$i]['url_crc32'] . ',' . ' ' . misc_crc16($a[$j]) . ' );' . "\n";
}
}
}
}
return $p;
}
function rsscache_sql_keyword_func($any = NULL, $require = NULL, $exclude = NULL, $table_suffix = NULL)
{
$debug = 0;
// DEBUG
// if ($debug == 1)
// echo 'any: '.$any.'<br>require: '.$require.'<br>exclude: '.$exclude.'<br>';
$rsstool_table = rsscache_tablename('rsstool', $table_suffix);
$keyword_table = rsscache_tablename('keyword', $table_suffix);
// HACK: merge any and require since result is sorted by number of matches
$q = $any . ' ' . $require;
$p = '';
$p .= ' (' . ' SELECT' . ' SQL_CACHE rsstool_url_crc32, COUNT(*) AS rsscache_rows' . ' FROM ' . $keyword_table . ' WHERE ' . $keyword_table . '.rsstool_keyword_crc16' . ' IN ( ';
$s = misc_get_keywords($q, 0);
// isalnum()
$a = explode(' ', $s);
$a = misc_array_unique_merge($a);
$func = 'misc_crc16';
// 0xffff keywords
// $func = 'misc_crc24'; // 0xffffff keywords
// $func = 'crc32'; // 0xffffffff keywords
$a = array_map($func, $a);
$p .= implode(', ', $a);
$p .= ' )' . ' GROUP BY rsstool_url_crc32' . ' ORDER BY rsscache_rows DESC' . ' LIMIT 256' . ' )' . ' AS temp' . ' JOIN ' . $rsstool_table . ' ON ' . $rsstool_table . '.rsstool_url_crc32 = temp.rsstool_url_crc32';
// DEBUG
// echo $p;
//exit;
return $p;
}
function rsscache_tablename_by_category($table_prefix, $c = NULL)
{
$category = NULL;
if ($c) {
$category = config_xml_by_category($c);
// DEBUG
//echo '<pre><tt>';
//print_r ($category);
//exit;
if ($category) {
if (isset($category['rsscache:table_suffix'])) {
return rsscache_tablename($table_prefix, $category['rsscache:table_suffix']);
}
}
}
return rsscache_tablename($table_prefix, NULL);
}