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;
}
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 sql_stresc($s)
{
// return mysql_escape_string ($s); // deprecated
// return mysql_real_escape_string ($s, $this->conn);
return misc_sql_stresc($s, $this->conn);
}
