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_update_keywords($r) { global $db; // update keywords using the (updated) misc_get_keywords() $t = $r['rsstool_keywords']; if (trim($t) == '') { $t = $r['rsstool_title'] . ' ' . $r['rsstool_desc']; // $t = $r['rsstool_title']; } $t = misc_get_keywords($t, 0); // isalnum $a = explode(' ', $t); for ($j = 0; isset($a[$j]); $j++) { if (trim($a[$j]) != '') { $sql_query_s = 'INSERT IGNORE INTO keyword_table (' . ' rsstool_url_crc32,' . ' rsstool_keyword_crc16' . ' ) VALUES (' . ' ' . $r['rsstool_url_crc32'] . ',' . ' ' . misc_crc16($a[$j]) . ' );' . "\n"; // DEBUG // echo $sql_query_s."\n"; $db->sql_write($sql_query_s, 0); } } }
function rsstool_write_ansisql($xml, $tv2_category, $db_conn = NULL) { $sql_update = 0; $tv2_engine = 0; $p = ''; $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" . '-- UNIQUE KEY rsstool_title_crc32 (rsstool_title_crc32),' . "\n" . '-- FULLTEXT KEY rsstool_title (rsstool_title),' . "\n" . '-- FULLTEXT KEY rsstool_desc (rsstool_desc)' . "\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_crc32 int(10) unsigned NOT NULL,' . "\n" . '-- rsstool_keyword_crc24 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($xml->item); for ($i = 0; $i < $items; $i++) { // 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_media_duration,' . ' rsstool_image,' . ' rsstool_event_start,' . ' rsstool_event_end'; // HACK: tv2 category if ($tv2_engine == 1) { $p .= ', tv2_category, tv2_moved'; } $p .= ' ) VALUES (' . ' \'' . misc_sql_stresc($xml->item[$i]->dl_url, $db_conn) . '\',' . ' \'' . $xml->item[$i]->dl_url_crc32 . '\',' . ' \'' . $xml->item[$i]->dl_date . '\',' . ' \'' . misc_sql_stresc($xml->item[$i]->site, $db_conn) . '\',' . ' \'' . misc_sql_stresc($xml->item[$i]->url, $db_conn) . '\',' . ' \'' . $xml->item[$i]->url_crc32 . '\',' . ' \'' . $xml->item[$i]->date . '\',' . ' \'' . misc_sql_stresc($xml->item[$i]->title, $db_conn) . '\',' . ' \'' . $xml->item[$i]->title_crc32 . '\',' . ' \'' . misc_sql_stresc($xml->item[$i]->desc, $db_conn) . '\',' . ' \'' . misc_sql_stresc($xml->item[$i]->media_keywords, $db_conn) . '\',' . ' \'' . $xml->item[$i]->media_duration * 1 . '\',' . ' \'' . $xml->item[$i]->image . '\',' . ' \'' . $xml->item[$i]->event_start * 1 . '\',' . ' \'' . $xml->item[$i]->event_end * 1 . '\''; // HACK: tv2 category if ($tv2_engine == 1) { $p .= ', \'' . $tv2_category . '\', \'' . $tv2_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($xml->item[$i]->title, $db_conn) . '\',' . ' rsstool_title_crc32 = \'' . $xml->item[$i]->title_crc32 . '\',' . ' rsstool_desc = \'' . misc_sql_stresc($xml->item[$i]->desc, $db_conn) . '\'' . ' WHERE rsstool_url_crc32 = ' . $xml->item[$i]->url_crc32 . ';' . "\n"; // keyword_table $a = explode(' ', $xml->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_crc32,' . ' rsstool_keyword_crc24,' . ' rsstool_keyword_crc16' . ' ) VALUES (' . ' ' . $xml->item[$i]->url_crc32 . ',' . ' ' . sprintf("%u", crc32($a[$j])) . ',' . ' ' . sprintf("%u", misc_crc24($a[$j])) . ',' . ' ' . misc_crc16($a[$j]) . ' );' . "\n"; } } } return $p; }