function rsscache_update_related_id($r)
{
global $db;
global $table_suffix;
$id = misc_related_string_id($r['rsstool_title']);
$sql_query_s = 'UPDATE rsstool_table' . $table_suffix . ' SET' . ' rsstool_related_id = ' . sprintf("%u", $id) . '' . ' WHERE rsstool_url_crc32 = ' . $r['rsstool_url_crc32'] . ';';
// $sql_query_s = 'UPDATE rsstool_table'.$table_suffix
// .' SET'
// .' rsstool_related_id = CASE '.sprintf ("%u", $id).''
// .' WHEN 1 THEN \'value\''
// .' WHEN 1 THEN \'value\''
// .' WHEN 1 THEN \'value\''
// .' END'
// .' WHERE rsstool_url_crc32 IN ( '.$r['rsstool_url_crc32'].' );';
//UPDATE rsstool_table'.$table_suffix
// SET myfield = CASE other_field
// WHEN 1 THEN 'value'
// WHEN 2 THEN 'value'
// WHEN 3 THEN 'value'
// END
//WHERE id IN (1,2,3)
// DEBUG
// echo $sql_query_s."\n";
$db->sql_write($sql_query_s, 0);
}
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;
}
