From 8158c57a8e3a40338045e0b809bca42f1e703ca5 Mon Sep 17 00:00:00 2001 From: Andrew Dolgov Date: Wed, 7 Sep 2005 14:31:21 +0100 Subject: [PATCH] update schema, more mysql work --- backend.php | 36 +++++++++++++++++++++++------------- db.php | 14 +++++++++++--- functions.js | 4 +++- functions.php | 8 ++++++-- opml.php | 25 ++++++++++++++----------- tt-rss.js | 19 +++++++++++-------- ttrss_schema_mysql.sql | 18 +++++++++--------- 7 files changed, 77 insertions(+), 47 deletions(-) diff --git a/backend.php b/backend.php index 82e07e6d..b4181069 100644 --- a/backend.php +++ b/backend.php @@ -288,11 +288,16 @@ "; } - // FIXME: check for null value here - - $result = db_query($link, "SELECT *,SUBSTRING(last_updated,1,16) as last_updated_s, - EXTRACT(EPOCH FROM NOW()) - EXTRACT(EPOCH FROM last_updated) as update_timeout - FROM ttrss_feeds WHERE id = '$feed'"); + if (DB_TYPE == "pgsql") { + $result = db_query($link, + "SELECT *,SUBSTRING(last_updated,1,16) as last_updated_s, + EXTRACT(EPOCH FROM NOW()) - EXTRACT(EPOCH FROM last_updated) as update_timeout + FROM ttrss_feeds WHERE id = '$feed'"); + } else { + $result = db_query($link, + "SELECT *,SUBSTRING(last_updated,1,16) as last_updated_s + FROM ttrss_feeds WHERE id = '$feed'"); + } if ($result) { @@ -356,10 +361,15 @@ $limit_query_part = "LIMIT " . $limit; } + if (DB_TYPE == "pgsql") { + $extract_epoch_qpart = " + ,EXTRACT(EPOCH FROM last_read) AS last_read_ts, + EXTRACT(EPOCH FROM updated) AS updated_ts"; + } + $result = db_query($link, "SELECT - id,title,updated,unread,feed_id,marked,link, - EXTRACT(EPOCH FROM last_read) AS last_read_ts, - EXTRACT(EPOCH FROM updated) AS updated_ts + id,title,updated,unread,feed_id,marked,link + $extract_epoch_qpart FROM ttrss_entries WHERE @@ -388,12 +398,12 @@ alt=\"Updated\">"; } - if ($line["unread"] == "t") { + if ($line["unread"] == "t" || $line["unread"] == "1") { $class .= "Unread"; ++$num_unread; } - if ($line["marked"] == "t") { + if ($line["marked"] == "t" || $line["marked"] == "1") { $marked_pic = " $\"Reset$ "; } else { @@ -648,7 +658,7 @@ "; + onclick=\"javascript:feedEditSave()\" value=\"Save\">"; } else { print " @@ -668,7 +678,7 @@ print " All feeds: "; + class=\"button\" onclick=\"gotoExportOpml()\" value=\"Export OPML\">"; } @@ -710,7 +720,7 @@ if (!WEB_DEMO_MODE) { - $regexp = db_escape_string($_GET["reg_exp"]); + $regexp = db_escape_string($_GET["regexp"]); $match = db_escape_string($_GET["match"]); $result = db_query($link, diff --git a/db.php b/db.php index cfb289e0..d739aa9b 100644 --- a/db.php +++ b/db.php @@ -20,7 +20,7 @@ function db_escape_string($s) { if (DB_TYPE == "pgsql") { return pg_escape_string($s); } else { - return mysql_escape_string($s); + return mysql_real_escape_string($s); } } @@ -32,6 +32,14 @@ function db_query($link, $query) { } } +function db_query_2($query) { + if (DB_TYPE == "pgsql") { + return pg_query($query); + } else if (DB_TYPE == "mysql") { + return mysql_query($link); + } +} + function db_fetch_assoc($result) { if (DB_TYPE == "pgsql") { return pg_fetch_assoc($result); @@ -43,9 +51,9 @@ function db_fetch_assoc($result) { function db_num_rows($result) { if (DB_TYPE == "pgsql") { - return pg_num_rows($link, $query); + return pg_num_rows($result); } else if (DB_TYPE == "mysql") { - return mysql_num_rows($link, $query); + return mysql_num_rows($result); } } diff --git a/functions.js b/functions.js index ebb1bb8f..540d7b89 100644 --- a/functions.js +++ b/functions.js @@ -263,4 +263,6 @@ function gotoMain() { document.location.href = "tt-rss.php"; } - +function gotoExportOpml() { + document.location.href = "opml.php?op=Export"; +} diff --git a/functions.php b/functions.php index bea443c2..742e026f 100644 --- a/functions.php +++ b/functions.php @@ -171,10 +171,14 @@ $entry_guid = db_escape_string($entry_guid); + if (DB_TYPE == "pgsql") { + $extract_ts_qpart = ",EXTRACT(EPOCH FROM updated) as updated_timestamp"; + } + $result = db_query($link, " SELECT - id,last_read,no_orig_date,title,feed_id,content_hash, - EXTRACT(EPOCH FROM updated) as updated_timestamp + id,last_read,no_orig_date,title,feed_id,content_hash + $extract_ts_qpart FROM ttrss_entries WHERE diff --git a/opml.php b/opml.php index f3dd34ef..d932d8bd 100644 --- a/opml.php +++ b/opml.php @@ -8,20 +8,22 @@ } require_once "config.php"; - require_once "functions.php"; + require_once "db.php"; - $link = pg_connect(DB_CONN); + $link = db_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME); - pg_query($link, "set client_encoding = 'utf-8'"); + if (DB_TYPE == "pgsql") { + pg_query($link, "set client_encoding = 'utf-8'"); + } if ($op == "Export") { print ""; print "" . date("r", time()) . ""; print ""; - $result = pg_query("SELECT * FROM ttrss_feeds ORDER BY title"); + $result = db_query($link, "SELECT * FROM ttrss_feeds ORDER BY title"); - while ($line = pg_fetch_assoc($result)) { + while ($line = db_fetch_assoc($result)) { $title = $line["title"]; $url = $line["feed_url"]; @@ -32,24 +34,25 @@ } function startElement($parser, $name, $attrs) { + if ($name == "OUTLINE") { - $title = pg_escape_string($attrs['TEXT']); - $url = pg_escape_string($attrs['XMLURL']); + $title = db_escape_string($attrs['TEXT']); + $url = db_escape_string($attrs['XMLURL']); if (!$title || !$url) return; print "Feed $title ($url)... "; - $result = pg_query("SELECT id FROM ttrss_feeds WHERE + $result = db_query_2("SELECT id FROM ttrss_feeds WHERE title = '$title' OR feed_url = '$url'"); - if (pg_num_rows($result) > 0) { + if (db_num_rows($result) > 0) { print " Already imported.
"; } else { - $result = pg_query("INSERT INTO ttrss_feeds (title, feed_url) VALUES + $result = db_query_2("INSERT INTO ttrss_feeds (title, feed_url) VALUES ('$title', '$url')"); print "Done.
"; @@ -125,6 +128,6 @@ } - pg_close($link); + db_close($link); ?> diff --git a/tt-rss.js b/tt-rss.js index 2015797c..1d1c793a 100644 --- a/tt-rss.js +++ b/tt-rss.js @@ -58,16 +58,18 @@ function feedlist_callback() { } */ +function checkActiveFeedId() { -function refetch_callback() { + var actfeedid = frames["feeds-frame"].document.getElementById("ACTFEEDID"); - if (xmlhttp_rpc.readyState == 4) { + if (actfeedid) { + active_feed_id = actfeedid.innerHTML; + } +} - var actfeedid = frames["feeds-frame"].document.getElementById("ACTFEEDID"); +function refetch_callback() { - if (actfeedid) { - active_feed_id = actfeedid.innerHTML; - } + if (xmlhttp_rpc.readyState == 4) { document.title = "Tiny Tiny RSS"; notify("All feeds updated."); @@ -253,6 +255,7 @@ function resetSearch() { } function search() { + checkActiveFeedId(); if (active_feed_id) { viewfeed(active_feed_id, 0, ""); } else { @@ -315,9 +318,9 @@ function init() { updateFeedList(false, false); document.onkeydown = hotkey_handler; - setTimeout("timeout()", 1800*1000); - scheduleFeedUpdate(true); +// setTimeout("timeout()", 1800*1000); +// scheduleFeedUpdate(true); var content = document.getElementById("content"); diff --git a/ttrss_schema_mysql.sql b/ttrss_schema_mysql.sql index 91fde947..348cb242 100644 --- a/ttrss_schema_mysql.sql +++ b/ttrss_schema_mysql.sql @@ -5,7 +5,7 @@ create table ttrss_feeds (id integer not null auto_increment primary key, title varchar(200) not null unique, feed_url varchar(250) unique not null, icon_url varchar(250) not null default '', - last_updated timestamp default null) TYPE=InnoDB; + last_updated datetime default '') TYPE=InnoDB; insert into ttrss_feeds (title,feed_url) values ('Footnotes', 'http://gnomedesktop.org/node/feed'); insert into ttrss_feeds (title,feed_url) values ('Freedesktop.org', 'http://planet.freedesktop.org/rss20.xml'); @@ -28,19 +28,19 @@ insert into ttrss_feeds (title,feed_url) values ('Technocrat.net', 'http://syndication.technocrat.net/rss'); create table ttrss_entries (id integer not null primary key auto_increment, - feed_id integer not null, - updated timestamp not null, + feed_id integer not null references ttrss_feeds(id), + updated datetime not null, title varchar(250) not null, guid varchar(250) not null unique, link varchar(250) not null, content text not null, content_hash varchar(250) not null, - last_read timestamp, - marked bool not null default 'false', - date_entered timestamp not null, - no_orig_date bool not null default 'false', + last_read datetime, + marked bool not null default 0, + date_entered datetime not null, + no_orig_date bool not null default 0, comments varchar(250) not null default '', - unread bool not null default 'true') TYPE=InnoDB; + unread bool not null default 1) TYPE=InnoDB; drop table if exists ttrss_filters; drop table if exists ttrss_filter_types; @@ -56,7 +56,7 @@ insert into ttrss_filter_types (id,name,description) values (3, 'both', 'Title or Content'); create table ttrss_filters (id integer primary key auto_increment, - filter_type integer not null, + filter_type integer not null references ttrss_filter_types(id), reg_exp varchar(250) not null, description varchar(250) not null default '') TYPE=InnoDB; -- 2.39.5