add workaround for psql 8.3 stricter typechecking on SUBSTRING() (refs #201)

12 files changed, 36 insertions, 23 deletions

diff --git a/backend.php b/backend.php
index a7606c5b7..47c5529cd 100644
--- a/backend.php
+++ b/backend.php
@@ -359,11 +359,11 @@
 			print "<div class='infoBoxContents'>";
 
 			$result = db_query($link, "SELECT login,
-				SUBSTRING(last_login,1,16) AS last_login,
+				".SUBSTRING_FOR_DATE."(last_login,1,16) AS last_login,
 				access_level,
 				(SELECT COUNT(int_id) FROM ttrss_user_entries 
 					WHERE owner_uid = id) AS stored_articles,
-				SUBSTRING(created,1,16) AS created
+				".SUBSTRING_FOR_DATE."(created,1,16) AS created
 				FROM ttrss_users 
 				WHERE id = '$uid'");
 				
diff --git a/functions.php b/functions.php
index 6f5e0288d..9dbe9b270 100644
--- a/functions.php
+++ b/functions.php
@@ -8,6 +8,12 @@
 
 	require_once 'config.php';
 
+	if (DB_TYPE == "pgsql") {
+		define('SUBSTRING_FOR_DATE', 'SUBSTRING_FOR_DATE');
+	} else {
+		define('SUBSTRING_FOR_DATE', 'SUBSTRING');
+	}
+
 	/**
 	 * Return available translations names.
 	 * 
@@ -329,7 +335,7 @@
 		}
 
 		$result = db_query($link, "SELECT feed_url,id,
-			SUBSTRING(last_updated,1,19) AS last_updated,
+			".SUBSTRING_FOR_DATE."(last_updated,1,19) AS last_updated,
 			update_interval FROM ttrss_feeds WHERE owner_uid = '$user_id'
 			ORDER BY $q_order");
 
@@ -2605,7 +2611,7 @@
 		$old_counters = $_SESSION["fctr_last_value"];
 
 /*		$result = db_query($link, "SELECT id,last_error,parent_feed,
-			SUBSTRING(last_updated,1,19) AS last_updated,
+			".SUBSTRING_FOR_DATE."(last_updated,1,19) AS last_updated,
 			(SELECT count(id) 
 				FROM ttrss_entries,ttrss_user_entries 
 				WHERE feed_id = ttrss_feeds.id AND 
@@ -2616,7 +2622,7 @@
 
 		$query = "SELECT ttrss_feeds.id,
 				ttrss_feeds.title,
-				SUBSTRING(ttrss_feeds.last_updated,1,19) AS last_updated, 
+				".SUBSTRING_FOR_DATE."(ttrss_feeds.last_updated,1,19) AS last_updated, 
 				last_error, 
 				COUNT(ttrss_entries.id) AS count 
 			FROM ttrss_feeds 
@@ -3259,10 +3265,10 @@
 						ttrss_entries.id,ttrss_entries.title,
 						updated,
 						unread,feed_id,marked,published,link,last_read,
-						SUBSTRING(last_read,1,19) as last_read_noms,
+						".SUBSTRING_FOR_DATE."(last_read,1,19) as last_read_noms,
 						$vfeed_query_part
 						$content_query_part
-						SUBSTRING(updated,1,19) as updated_noms,
+						".SUBSTRING_FOR_DATE."(updated,1,19) as updated_noms,
 						author
 					FROM
 						ttrss_entries,ttrss_user_entries,ttrss_feeds
@@ -3291,10 +3297,10 @@
 					updated,
 					unread,feed_id,
 					marked,link,last_read,				
-					SUBSTRING(last_read,1,19) as last_read_noms,
+					".SUBSTRING_FOR_DATE."(last_read,1,19) as last_read_noms,
 					$vfeed_query_part
 					$content_query_part
-					SUBSTRING(updated,1,19) as updated_noms
+					".SUBSTRING_FOR_DATE."(updated,1,19) as updated_noms
 					FROM
 						ttrss_entries,ttrss_user_entries,ttrss_tags
 					WHERE
@@ -3533,7 +3539,7 @@
 				ttrss_user_entries.ref_id,
 				link,
 				SUBSTRING(content, 1, 120) AS excerpt,
-				SUBSTRING(last_updated,1,19) AS last_updated
+				".SUBSTRING_FOR_DATE."(last_updated,1,19) AS last_updated
 			FROM 
 				ttrss_user_entries,ttrss_entries,ttrss_feeds 
 			WHERE 
@@ -4165,7 +4171,7 @@
 			$age_qpart = getMaxAgeSubquery();
 
 			$result = db_query($link, "SELECT ttrss_feeds.*,
-				SUBSTRING(last_updated,1,19) AS last_updated_noms,
+				".SUBSTRING_FOR_DATE."(last_updated,1,19) AS last_updated_noms,
 				(SELECT COUNT(id) FROM ttrss_entries,ttrss_user_entries
 					WHERE feed_id = ttrss_feeds.id AND unread = true
 						AND $age_qpart
@@ -4497,7 +4503,7 @@
 		}
 
 		$result = db_query($link, "SELECT title,link,content,feed_id,comments,int_id,
-			SUBSTRING(updated,1,16) as updated,
+			".SUBSTRING_FOR_DATE."(updated,1,16) as updated,
 			(SELECT icon_url FROM ttrss_feeds WHERE id = feed_id) as icon_url,
 			num_comments,
 			author
@@ -5338,7 +5344,7 @@
 
 		// We search for feed needing update.
 		$result = db_query($link, "SELECT ttrss_feeds.feed_url,ttrss_feeds.id, ttrss_feeds.owner_uid,
-				SUBSTRING(ttrss_feeds.last_updated,1,19) AS last_updated,
+				".SUBSTRING_FOR_DATE."(ttrss_feeds.last_updated,1,19) AS last_updated,
 				ttrss_feeds.update_interval 
 			FROM 
 				ttrss_feeds, ttrss_users, ttrss_user_prefs
diff --git a/mobile/functions.php b/mobile/functions.php
index fe9dab4ed..b1085b9c5 100644
--- a/mobile/functions.php
+++ b/mobile/functions.php
@@ -113,7 +113,7 @@
 			}
 
 			$result = db_query($link, "SELECT ttrss_feeds.*,
-				SUBSTRING(last_updated,1,19) AS last_updated_noms,
+				".SUBSTRING_FOR_DATE."(last_updated,1,19) AS last_updated_noms,
 				(SELECT COUNT(id) FROM ttrss_entries,ttrss_user_entries
 					WHERE feed_id = ttrss_feeds.id AND 
 					ttrss_user_entries.ref_id = ttrss_entries.id AND
@@ -601,7 +601,7 @@
 
 		$result = db_query($link, "SELECT title,link,content,feed_id,comments,int_id,
 			marked,published,
-			SUBSTRING(updated,1,16) as updated,
+			".SUBSTRING_FOR_DATE."(updated,1,16) as updated,
 			(SELECT icon_url FROM ttrss_feeds WHERE id = feed_id) as icon_url,
 			num_comments,
 			author
diff --git a/modules/pref-feed-browser.php b/modules/pref-feed-browser.php
index e33103501..bdbb930d4 100644
--- a/modules/pref-feed-browser.php
+++ b/modules/pref-feed-browser.php
@@ -16,7 +16,7 @@
 
 			$result = db_query($link, "SELECT 
 					feed_url,site_url,
-					SUBSTRING(last_updated,1,19) AS last_updated 
+					".SUBSTRING_FOR_DATE."(last_updated,1,19) AS last_updated 
 				FROM ttrss_feeds WHERE id = '$id' AND
 					auth_login = '' AND auth_pass = '' AND private IS NOT true");
 
diff --git a/modules/pref-feeds.php b/modules/pref-feeds.php
index 9b718ace6..43be1cbe3 100644
--- a/modules/pref-feeds.php
+++ b/modules/pref-feeds.php
@@ -804,7 +804,7 @@
 		if ($_GET["slat"] == "true") {
 			$show_last_article_info = true;
 			$show_last_article_checked = "checked";
-			$show_last_article_qpart = ", (SELECT SUBSTRING(MAX(updated),1,16) FROM ttrss_user_entries,
+			$show_last_article_qpart = ", (SELECT ".SUBSTRING_FOR_DATE."(MAX(updated),1,16) FROM ttrss_user_entries,
 				ttrss_entries WHERE ref_id = ttrss_entries.id
 				AND feed_id = F1.id) AS last_article";
 		} else if ($feeds_sort == "last_article") {
diff --git a/modules/pref-users.php b/modules/pref-users.php
index 4872a0d8f..399fc5e1e 100644
--- a/modules/pref-users.php
+++ b/modules/pref-users.php
@@ -197,7 +197,7 @@
 
 		$result = db_query($link, "SELECT 
 				id,login,access_level,email,
-				SUBSTRING(last_login,1,16) as last_login
+				".SUBSTRING_FOR_DATE."(last_login,1,16) as last_login
 			FROM 
 				ttrss_users
 			ORDER BY $sort");
diff --git a/sanity_check.php b/sanity_check.php
index 3dc6eaaf7..3735d6c4f 100644
--- a/sanity_check.php
+++ b/sanity_check.php
@@ -2,7 +2,7 @@
 	require_once "functions.php";
 
 	define('EXPECTED_CONFIG_VERSION', 15);
-	define('SCHEMA_VERSION', 34);
+	define('SCHEMA_VERSION', 35);
 
 	if (!file_exists("config.php")) {
 		print __("<b>Fatal Error</b>: You forgot to copy 
diff --git a/schema/ttrss_schema_mysql.sql b/schema/ttrss_schema_mysql.sql
index 9edb4b12c..f2daa69fb 100644
--- a/schema/ttrss_schema_mysql.sql
+++ b/schema/ttrss_schema_mysql.sql
@@ -205,7 +205,7 @@ create table ttrss_tags (id integer primary key auto_increment,
 
 create table ttrss_version (schema_version int not null) TYPE=InnoDB;
 
-insert into ttrss_version values (34);
+insert into ttrss_version values (35);
 
 create table ttrss_enclosures (id serial not null primary key,
 	content_url text not null,
diff --git a/schema/ttrss_schema_pgsql.sql b/schema/ttrss_schema_pgsql.sql
index 7ea72cd75..c35781a69 100644
--- a/schema/ttrss_schema_pgsql.sql
+++ b/schema/ttrss_schema_pgsql.sql
@@ -18,6 +18,7 @@ drop table ttrss_feed_categories;
 drop table ttrss_users;
 drop table ttrss_themes;
 drop table ttrss_sessions;
+drop function SUBSTRING_FOR_DATE(timestamp, int, int);
 
 begin;
 
@@ -184,7 +185,7 @@ create index ttrss_tags_owner_uid_index on ttrss_tags(owner_uid);
 
 create table ttrss_version (schema_version int not null);
 
-insert into ttrss_version values (34);
+insert into ttrss_version values (35);
 
 create table ttrss_enclosures (id serial not null primary key,
 	content_url text not null,
@@ -317,4 +318,6 @@ create table ttrss_sessions (id varchar(250) unique not null primary key,
 
 create index ttrss_sessions_expire_index on ttrss_sessions(expire);
 
+create function SUBSTRING_FOR_DATE(timestamp, int, int) RETURNS text AS 'SELECT SUBSTRING(CAST($1 AS text), $2, $3)' LANGUAGE 'sql';
+
 commit;
diff --git a/schema/versions/mysql/35.sql b/schema/versions/mysql/35.sql
new file mode 100644
index 000000000..ad112a021
--- /dev/null
+++ b/schema/versions/mysql/35.sql
@@ -0,0 +1 @@
+update ttrss_version set schema_version = 35;
diff --git a/schema/versions/pgsql/35.sql b/schema/versions/pgsql/35.sql
new file mode 100644
index 000000000..e624d66b3
--- /dev/null
+++ b/schema/versions/pgsql/35.sql
@@ -0,0 +1,3 @@
+create function SUBSTRING_FOR_DATE(timestamp, int, int) RETURNS text AS 'SELECT SUBSTRING(CAST($1 AS text), $2, $3)' LANGUAGE 'sql';
+
+update ttrss_version set schema_version = 35;
diff --git a/xml-rpc.php b/xml-rpc.php
index 07f423cbd..0117657d0 100644
--- a/xml-rpc.php
+++ b/xml-rpc.php
@@ -175,7 +175,7 @@
 		if (authenticate_user($link, $login, $pass)) {
 
 			$result = db_query($link, "SELECT 
-				id, feed_url, cat_id, title, SUBSTRING(last_updated,1,19) AS last_updated
+				id, feed_url, cat_id, title, ".SUBSTRING_FOR_DATE."(last_updated,1,19) AS last_updated
 					FROM ttrss_feeds WHERE owner_uid = " . 
 				$_SESSION["uid"]);
 
@@ -341,7 +341,7 @@
 
 			$query = "SELECT title,link,content,feed_id,comments,int_id,
 				marked,unread,
-				SUBSTRING(updated,1,16) as updated,
+				".SUBSTRING_FOR_DATE."(updated,1,16) as updated,
 				author
 				FROM ttrss_entries,ttrss_user_entries
 				WHERE	id = '$article_id' AND ref_id = id AND owner_uid = " . $_SESSION["uid"] ;