diff options
Diffstat (limited to 'app/SQL/install.sql.pgsql.php')
| -rw-r--r-- | app/SQL/install.sql.pgsql.php | 56 |
1 files changed, 40 insertions, 16 deletions
diff --git a/app/SQL/install.sql.pgsql.php b/app/SQL/install.sql.pgsql.php index b80fbf1e7..e68e6f3be 100644 --- a/app/SQL/install.sql.pgsql.php +++ b/app/SQL/install.sql.pgsql.php @@ -1,5 +1,5 @@ <?php -define('SQL_CREATE_DB', 'CREATE DATABASE %1$s ENCODING \'UTF8\';'); +define('SQL_CREATE_DB', 'CREATE DATABASE "%1$s" ENCODING \'UTF8\';'); global $SQL_CREATE_TABLES; $SQL_CREATE_TABLES = array( @@ -10,16 +10,16 @@ $SQL_CREATE_TABLES = array( 'CREATE TABLE IF NOT EXISTS "%1$sfeed" ( "id" SERIAL PRIMARY KEY, - "url" varchar(511) UNIQUE NOT NULL, + "url" VARCHAR(511) UNIQUE NOT NULL, "category" SMALLINT DEFAULT 0, "name" VARCHAR(255) NOT NULL, "website" VARCHAR(255), - "description" text, + "description" TEXT, "lastUpdate" INT DEFAULT 0, "priority" SMALLINT NOT NULL DEFAULT 10, "pathEntries" VARCHAR(511) DEFAULT NULL, "httpAuth" VARCHAR(511) DEFAULT NULL, - "error" smallint DEFAULT 0, + "error" SMALLINT DEFAULT 0, "keep_history" INT NOT NULL DEFAULT -2, "ttl" INT NOT NULL DEFAULT 0, "attributes" TEXT, -- v1.11.0 @@ -27,9 +27,9 @@ $SQL_CREATE_TABLES = array( "cache_nbUnreads" INT DEFAULT 0, FOREIGN KEY ("category") REFERENCES "%1$scategory" ("id") ON DELETE SET NULL ON UPDATE CASCADE );', -'CREATE INDEX %1$sname_index ON "%1$sfeed" ("name");', -'CREATE INDEX %1$spriority_index ON "%1$sfeed" ("priority");', -'CREATE INDEX %1$skeep_history_index ON "%1$sfeed" ("keep_history");', +'CREATE INDEX "%1$sname_index" ON "%1$sfeed" ("name");', +'CREATE INDEX "%1$spriority_index" ON "%1$sfeed" ("priority");', +'CREATE INDEX "%1$skeep_history_index" ON "%1$sfeed" ("keep_history");', 'CREATE TABLE IF NOT EXISTS "%1$sentry" ( "id" BIGINT NOT NULL PRIMARY KEY, @@ -48,11 +48,14 @@ $SQL_CREATE_TABLES = array( FOREIGN KEY ("id_feed") REFERENCES "%1$sfeed" ("id") ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE ("id_feed","guid") );', -'CREATE INDEX %1$sis_favorite_index ON "%1$sentry" ("is_favorite");', -'CREATE INDEX %1$sis_read_index ON "%1$sentry" ("is_read");', -'CREATE INDEX %1$sentry_lastSeen_index ON "%1$sentry" ("lastSeen");', +'CREATE INDEX "%1$sis_favorite_index" ON "%1$sentry" ("is_favorite");', +'CREATE INDEX "%1$sis_read_index" ON "%1$sentry" ("is_read");', +'CREATE INDEX "%1$sentry_lastSeen_index" ON "%1$sentry" ("lastSeen");', -'INSERT INTO "%1$scategory" (id, name) SELECT 1, \'%2$s\' WHERE NOT EXISTS (SELECT id FROM "%1$scategory" WHERE id = 1) RETURNING nextval(\'%1$scategory_id_seq\');', +'INSERT INTO "%1$scategory" (id, name) + SELECT 1, \'%2$s\' + WHERE NOT EXISTS (SELECT id FROM "%1$scategory" WHERE id = 1) + RETURNING nextval(\'"%1$scategory_id_seq"\');', ); global $SQL_CREATE_TABLE_ENTRYTMP; @@ -74,15 +77,36 @@ $SQL_CREATE_TABLE_ENTRYTMP = array( FOREIGN KEY ("id_feed") REFERENCES "%1$sfeed" ("id") ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE ("id_feed","guid") );', -'CREATE INDEX %1$sentrytmp_date_index ON "%1$sentrytmp" ("date");', +'CREATE INDEX "%1$sentrytmp_date_index" ON "%1$sentrytmp" ("date");', -'CREATE INDEX %1$sentry_feed_read_index ON "%1$sentry" ("id_feed","is_read");', //v1.7 +'CREATE INDEX "%1$sentry_feed_read_index" ON "%1$sentry" ("id_feed","is_read");', //v1.7 +); + +global $SQL_CREATE_TABLE_TAGS; +$SQL_CREATE_TABLE_TAGS = array( +'CREATE TABLE IF NOT EXISTS "%1$stag" ( -- v1.12 + "id" SERIAL PRIMARY KEY, + "name" VARCHAR(63) UNIQUE NOT NULL, + "attributes" TEXT +);', +'CREATE TABLE IF NOT EXISTS "%1$sentrytag" ( + "id_tag" SMALLINT, + "id_entry" BIGINT, + PRIMARY KEY ("id_tag","id_entry"), + FOREIGN KEY ("id_tag") REFERENCES "%1$stag" ("id") ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY ("id_entry") REFERENCES "%1$sentry" ("id") ON DELETE CASCADE ON UPDATE CASCADE +);', +'CREATE INDEX "%1$sentrytag_id_entry_index" ON "%1$sentrytag" ("id_entry");', ); global $SQL_INSERT_FEEDS; $SQL_INSERT_FEEDS = array( -'INSERT INTO "%1$sfeed" (url, category, name, website, description, ttl) SELECT \'http://freshrss.org/feeds/all.atom.xml\', 1, \'FreshRSS.org\', \'http://freshrss.org/\', \'FreshRSS, a free, self-hostable aggregator…\', 86400 WHERE NOT EXISTS (SELECT id FROM "%1$sfeed" WHERE url = \'http://freshrss.org/feeds/all.atom.xml\');', -'INSERT INTO "%1$sfeed" (url, category, name, website, description, ttl) SELECT \'https://github.com/FreshRSS/FreshRSS/releases.atom\', 1, \'FreshRSS @ GitHub\', \'https://github.com/FreshRSS/FreshRSS/\', \'FreshRSS releases @ GitHub\', 86400 WHERE NOT EXISTS (SELECT id FROM "%1$sfeed" WHERE url = \'https://github.com/FreshRSS/FreshRSS/releases.atom\');', +'INSERT INTO "%1$sfeed" (url, category, name, website, description, ttl) + SELECT \'https://freshrss.org/feeds/all.atom.xml\', 1, \'FreshRSS.org\', \'https://freshrss.org/\', \'FreshRSS, a free, self-hostable aggregator…\', 86400 + WHERE NOT EXISTS (SELECT id FROM "%1$sfeed" WHERE url = \'https://freshrss.org/feeds/all.atom.xml\');', +'INSERT INTO "%1$sfeed" (url, category, name, website, description, ttl) + SELECT \'https://github.com/FreshRSS/FreshRSS/releases.atom\', 1, \'FreshRSS @ GitHub\', \'https://github.com/FreshRSS/FreshRSS/\', \'FreshRSS releases @ GitHub\', 86400 + WHERE NOT EXISTS (SELECT id FROM "%1$sfeed" WHERE url = \'https://github.com/FreshRSS/FreshRSS/releases.atom\');', ); -define('SQL_DROP_TABLES', 'DROP TABLE IF EXISTS "%1$sentrytmp", "%1$sentry", "%1$sfeed", "%1$scategory"'); +define('SQL_DROP_TABLES', 'DROP TABLE IF EXISTS "%1$sentrytag", "%1$stag", "%1$sentrytmp", "%1$sentry", "%1$sfeed", "%1$scategory"'); |