From 0324df6f889f18500cc8d201fdc2845f3e4d1acf Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Mon, 30 Oct 2023 20:47:50 +0100 Subject: SQL increase length of VARCHAR fields (#5756) * SQL increase length of VARCHAR fields Increase length of all fields, keeping the limits for: * Unique indexes on UTF-8: 191 bytes for MySQL; * Unique indexes on ASCII: 767 bytes for MySQL; * Max URL for external tools: 32768 characters; * Max VARCHAR: 65535 bytes for MySQL; Follow-up of https://github.com/FreshRSS/FreshRSS/pull/5038 Fix https://github.com/FreshRSS/FreshRSS/issues/4986 * Fix length test --- app/SQL/install.sql.pgsql.php | 30 +++++++++++++++--------------- 1 file changed, 15 insertions(+), 15 deletions(-) (limited to 'app/SQL/install.sql.pgsql.php') diff --git a/app/SQL/install.sql.pgsql.php b/app/SQL/install.sql.pgsql.php index 2149fc247..4f7f1cf89 100644 --- a/app/SQL/install.sql.pgsql.php +++ b/app/SQL/install.sql.pgsql.php @@ -6,7 +6,7 @@ SQL; $GLOBALS['SQL_CREATE_TABLES'] = <<<'SQL' CREATE TABLE IF NOT EXISTS `_category` ( "id" SERIAL PRIMARY KEY, - "name" VARCHAR(255) UNIQUE NOT NULL, + "name" VARCHAR(191) UNIQUE NOT NULL, "kind" SMALLINT DEFAULT 0, -- 1.20.0 "lastUpdate" BIGINT DEFAULT 0, -- 1.20.0 "error" SMALLINT DEFAULT 0, -- 1.20.0 @@ -18,13 +18,13 @@ CREATE TABLE IF NOT EXISTS `_feed` ( "url" VARCHAR(32768) NOT NULL, "kind" SMALLINT DEFAULT 0, -- 1.20.0 "category" INT DEFAULT 0, -- 1.20.0 - "name" VARCHAR(255) NOT NULL, + "name" VARCHAR(191) NOT NULL, "website" VARCHAR(32768), "description" TEXT, "lastUpdate" INT DEFAULT 0, "priority" SMALLINT NOT NULL DEFAULT 10, - "pathEntries" VARCHAR(511) DEFAULT NULL, - "httpAuth" VARCHAR(511) DEFAULT NULL, + "pathEntries" VARCHAR(65535) DEFAULT NULL, + "httpAuth" VARCHAR(1024) DEFAULT NULL, "error" SMALLINT DEFAULT 0, "ttl" INT NOT NULL DEFAULT 0, "attributes" TEXT, -- v1.11.0 @@ -37,18 +37,18 @@ CREATE INDEX IF NOT EXISTS `_priority_index` ON `_feed` ("priority"); CREATE TABLE IF NOT EXISTS `_entry` ( "id" BIGINT NOT NULL PRIMARY KEY, - "guid" VARCHAR(760) NOT NULL, - "title" VARCHAR(255) NOT NULL, - "author" VARCHAR(255), + "guid" VARCHAR(767) NOT NULL, + "title" VARCHAR(65535) NOT NULL, + "author" VARCHAR(65535), "content" TEXT, - "link" VARCHAR(1023) NOT NULL, + "link" VARCHAR(32768) NOT NULL, "date" INT, "lastSeen" INT DEFAULT 0, "hash" BYTEA, "is_read" SMALLINT NOT NULL DEFAULT 0, "is_favorite" SMALLINT NOT NULL DEFAULT 0, "id_feed" INT, -- 1.20.0 - "tags" VARCHAR(1023), + "tags" VARCHAR(65535), "attributes" TEXT, -- v1.20.0 FOREIGN KEY ("id_feed") REFERENCES `_feed` ("id") ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE ("id_feed","guid") @@ -65,18 +65,18 @@ INSERT INTO `_category` (id, name) CREATE TABLE IF NOT EXISTS `_entrytmp` ( -- v1.7 "id" BIGINT NOT NULL PRIMARY KEY, - "guid" VARCHAR(760) NOT NULL, - "title" VARCHAR(255) NOT NULL, - "author" VARCHAR(255), + "guid" VARCHAR(767) NOT NULL, + "title" VARCHAR(65535) NOT NULL, + "author" VARCHAR(65535), "content" TEXT, - "link" VARCHAR(1023) NOT NULL, + "link" VARCHAR(32768) NOT NULL, "date" INT, "lastSeen" INT DEFAULT 0, "hash" BYTEA, "is_read" SMALLINT NOT NULL DEFAULT 0, "is_favorite" SMALLINT NOT NULL DEFAULT 0, "id_feed" INT, -- 1.20.0 - "tags" VARCHAR(1023), + "tags" VARCHAR(65535), "attributes" TEXT, -- v1.20.0 FOREIGN KEY ("id_feed") REFERENCES `_feed` ("id") ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE ("id_feed","guid") @@ -85,7 +85,7 @@ CREATE INDEX IF NOT EXISTS `_entrytmp_date_index` ON `_entrytmp` ("date"); CREATE TABLE IF NOT EXISTS `_tag` ( -- v1.12 "id" SERIAL PRIMARY KEY, - "name" VARCHAR(63) UNIQUE NOT NULL, + "name" VARCHAR(191) UNIQUE NOT NULL, "attributes" TEXT ); CREATE TABLE IF NOT EXISTS `_entrytag` ( -- cgit v1.2.3