From 21a279179a5e496082f4e43c5a2a5e10d90c0bdb Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Mon, 30 Oct 2023 21:10:09 +0100
Subject: Ready for year 2038 (#5570)

* Ready for year 2038
Fix https://github.com/FreshRSS/FreshRSS/discussions/5569
Requires PHP on a 64-bit platform to take advantage of it.
https://en.wikipedia.org/wiki/Year_2038_problem

* Allows dates past 2038
Rework of https://github.com/FreshRSS/FreshRSS/pull/3259
https://github.com/FreshRSS/FreshRSS/issues/3258

* Auto alter columns

* Changelog
---
 app/SQL/install.sql.mysql.php  | 21 ++++++++++++++++-----
 app/SQL/install.sql.pgsql.php  | 21 ++++++++++++++++-----
 app/SQL/install.sql.sqlite.php | 10 +++++-----
 3 files changed, 37 insertions(+), 15 deletions(-)

(limited to 'app/SQL')

diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
index cb12fa179..b79450943 100644
--- a/app/SQL/install.sql.mysql.php
+++ b/app/SQL/install.sql.mysql.php
@@ -24,7 +24,7 @@ CREATE TABLE IF NOT EXISTS `_feed` (
 	`name` VARCHAR(191) NOT NULL,
 	`website` TEXT CHARACTER SET latin1 COLLATE latin1_bin,
 	`description` TEXT,
-	`lastUpdate` INT(11) DEFAULT 0,	-- Until year 2038
+	`lastUpdate` BIGINT DEFAULT 0,
 	`priority` TINYINT(2) NOT NULL DEFAULT 10,
 	`pathEntries` VARCHAR(65535) DEFAULT NULL,
 	`httpAuth` VARCHAR(1024) DEFAULT NULL,
@@ -47,8 +47,8 @@ CREATE TABLE IF NOT EXISTS `_entry` (
 	`author` VARCHAR(65535),
 	`content_bin` MEDIUMBLOB,	-- v0.7
 	`link` VARCHAR(32768) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
-	`date` INT(11),	-- Until year 2038
-	`lastSeen` INT(11) DEFAULT 0,	-- v1.1.1, Until year 2038
+	`date` BIGINT,
+	`lastSeen` BIGINT DEFAULT 0,
 	`hash` BINARY(16),	-- v1.1.1
 	`is_read` BOOLEAN NOT NULL DEFAULT 0,
 	`is_favorite` BOOLEAN NOT NULL DEFAULT 0,
@@ -74,8 +74,8 @@ CREATE TABLE IF NOT EXISTS `_entrytmp` (	-- v1.7
 	`author` VARCHAR(65535),
 	`content_bin` MEDIUMBLOB,
 	`link` VARCHAR(32768) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
-	`date` INT(11),
-	`lastSeen` INT(11) DEFAULT 0,
+	`date` BIGINT,
+	`lastSeen` BIGINT DEFAULT 0,
 	`hash` BINARY(16),
 	`is_read` BOOLEAN NOT NULL DEFAULT 0,
 	`is_favorite` BOOLEAN NOT NULL DEFAULT 0,
@@ -112,3 +112,14 @@ SQL;
 $GLOBALS['SQL_DROP_TABLES'] = <<<'SQL'
 DROP TABLE IF EXISTS `_entrytag`, `_tag`, `_entrytmp`, `_entry`, `_feed`, `_category`;
 SQL;
+
+$GLOBALS['SQL_UPDATE_YEAR_2038'] = <<<'SQL'
+ALTER TABLE `_entry`	-- v1.23
+	MODIFY COLUMN `date` BIGINT,
+	MODIFY COLUMN `lastSeen` BIGINT DEFAULT 0;
+ALTER TABLE `_entrytmp`
+	MODIFY COLUMN `date` BIGINT,
+	MODIFY COLUMN `lastSeen` BIGINT DEFAULT 0;
+ALTER TABLE `_feed`
+	MODIFY COLUMN `lastUpdate` BIGINT DEFAULT 0;
+SQL;
diff --git a/app/SQL/install.sql.pgsql.php b/app/SQL/install.sql.pgsql.php
index 4f7f1cf89..e08d0e1ef 100644
--- a/app/SQL/install.sql.pgsql.php
+++ b/app/SQL/install.sql.pgsql.php
@@ -21,7 +21,7 @@ CREATE TABLE IF NOT EXISTS `_feed` (
 	"name" VARCHAR(191) NOT NULL,
 	"website" VARCHAR(32768),
 	"description" TEXT,
-	"lastUpdate" INT DEFAULT 0,
+	"lastUpdate" BIGINT DEFAULT 0,
 	"priority" SMALLINT NOT NULL DEFAULT 10,
 	"pathEntries" VARCHAR(65535) DEFAULT NULL,
 	"httpAuth" VARCHAR(1024) DEFAULT NULL,
@@ -42,8 +42,8 @@ CREATE TABLE IF NOT EXISTS `_entry` (
 	"author" VARCHAR(65535),
 	"content" TEXT,
 	"link" VARCHAR(32768) NOT NULL,
-	"date" INT,
-	"lastSeen" INT DEFAULT 0,
+	"date" BIGINT,
+	"lastSeen" BIGINT DEFAULT 0,
 	"hash" BYTEA,
 	"is_read" SMALLINT NOT NULL DEFAULT 0,
 	"is_favorite" SMALLINT NOT NULL DEFAULT 0,
@@ -70,8 +70,8 @@ CREATE TABLE IF NOT EXISTS `_entrytmp` (	-- v1.7
 	"author" VARCHAR(65535),
 	"content" TEXT,
 	"link" VARCHAR(32768) NOT NULL,
-	"date" INT,
-	"lastSeen" INT DEFAULT 0,
+	"date" BIGINT,
+	"lastSeen" BIGINT DEFAULT 0,
 	"hash" BYTEA,
 	"is_read" SMALLINT NOT NULL DEFAULT 0,
 	"is_favorite" SMALLINT NOT NULL DEFAULT 0,
@@ -101,3 +101,14 @@ SQL;
 $GLOBALS['SQL_DROP_TABLES'] = <<<'SQL'
 DROP TABLE IF EXISTS `_entrytag`, `_tag`, `_entrytmp`, `_entry`, `_feed`, `_category`;
 SQL;
+
+$GLOBALS['SQL_UPDATE_YEAR_2038'] = <<<'SQL'
+ALTER TABLE `_entry`	-- v1.23
+	ALTER COLUMN "date" SET DATA TYPE BIGINT,
+	ALTER COLUMN "lastSeen" SET DATA TYPE BIGINT;
+ALTER TABLE `_entrytmp`
+	ALTER COLUMN "date" SET DATA TYPE BIGINT,
+	ALTER COLUMN "lastSeen" SET DATA TYPE BIGINT;
+ALTER TABLE `_feed`
+	ALTER COLUMN "lastUpdate" SET DATA TYPE BIGINT;
+SQL;
diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php
index 1369bad25..2796b63e9 100644
--- a/app/SQL/install.sql.sqlite.php
+++ b/app/SQL/install.sql.sqlite.php
@@ -22,7 +22,7 @@ CREATE TABLE IF NOT EXISTS `feed` (
 	`name` VARCHAR(191) NOT NULL,
 	`website` VARCHAR(32768),
 	`description` TEXT,
-	`lastUpdate` INT(11) DEFAULT 0,	-- Until year 2038
+	`lastUpdate` BIGINT DEFAULT 0,
 	`priority` TINYINT(2) NOT NULL DEFAULT 10,
 	`pathEntries` VARCHAR(65535) DEFAULT NULL,
 	`httpAuth` VARCHAR(1024) DEFAULT NULL,
@@ -43,8 +43,8 @@ CREATE TABLE IF NOT EXISTS `entry` (
 	`author` VARCHAR(65535),
 	`content` TEXT,
 	`link` VARCHAR(32768) NOT NULL,
-	`date` INT(11),	-- Until year 2038
-	`lastSeen` INT(11) DEFAULT 0,	-- v1.1.1, Until year 2038
+	`date` BIGINT,
+	`lastSeen` BIGINT DEFAULT 0,
 	`hash` BINARY(16),	-- v1.1.1
 	`is_read` BOOLEAN NOT NULL DEFAULT 0,
 	`is_favorite` BOOLEAN NOT NULL DEFAULT 0,
@@ -69,8 +69,8 @@ CREATE TABLE IF NOT EXISTS `entrytmp` (	-- v1.7
 	`author` VARCHAR(65535),
 	`content` TEXT,
 	`link` VARCHAR(32768) NOT NULL,
-	`date` INT(11),
-	`lastSeen` INT(11) DEFAULT 0,
+	`date` BIGINT,
+	`lastSeen` BIGINT DEFAULT 0,
 	`hash` BINARY(16),
 	`is_read` BOOLEAN NOT NULL DEFAULT 0,
 	`is_favorite` BOOLEAN NOT NULL DEFAULT 0,
-- 
cgit v1.2.3