Implement patch #3476011: PostgreSQL tables can not be initialized

3 files changed, 262 insertions, 0 deletions

diff --git a/data/tables-postgresql.sql b/data/tables-postgresql.sql
new file mode 100644
index 0000000..b0ada11
--- /dev/null
+++ b/data/tables-postgresql.sql
@@ -0,0 +1,259 @@
+-- Semantic Scuttle - Tables creation SQL script
+-- ! Dont forget to change table names according to $tableprefix defined in config.php !
+
+--
+-- Table structure for table "sc_bookmarks"
+--
+
+CREATE SEQUENCE bIds
+  INCREMENT BY 1
+  NO MAXVALUE
+  NO MINVALUE
+  CACHE 1;
+
+CREATE TABLE sc_bookmarks (
+  bId integer DEFAULT nextval('bIds'::text) PRIMARY KEY,
+  uId integer NOT NULL,
+  bIp varchar(40) DEFAULT NULL,
+  bStatus smallint NOT NULL,
+  bDatetime timestamp with time zone DEFAULT now() NOT NULL,
+  bModified timestamp with time zone DEFAULT now() NOT NULL,
+  bTitle varchar(255) DEFAULT '' NOT NULL,
+  bAddress varchar(1500) DEFAULT '' NOT NULL,
+  bDescription text,
+  bPrivateNote text,
+  bHash varchar(32) DEFAULT '' NOT NULL,
+  bVotes integer NOT NULL,
+  bVoting integer NOT NULL,
+  bShort varchar(16) DEFAULT NULL
+);
+
+CREATE INDEX sc_bookmarks_usd ON sc_bookmarks (uId, bStatus, bDatetime);
+CREATE INDEX sc_bookmarks_hui ON sc_bookmarks (bHash, uId, bId);
+CREATE INDEX sc_bookmarks_du ON sc_bookmarks (bDatetime, uId);
+
+--
+-- Table structure for table "sc_bookmarks2tags"
+--
+
+CREATE SEQUENCE b2tIds
+  INCREMENT BY 1
+  NO MAXVALUE
+  NO MINVALUE
+  CACHE 1;
+
+CREATE TABLE sc_bookmarks2tags (
+  id integer DEFAULT nextval('b2tIds'::text) PRIMARY KEY,
+  bId integer NOT NULL,
+  tag varchar(100) DEFAULT '' NOT NULL
+);
+
+CREATE UNIQUE INDEX sc_bookmarks2tags_tag_bId on sc_bookmarks2tags (tag, bId);
+CREATE INDEX sc_bookmarks2tags_bId on sc_bookmarks2tags (bId);
+
+--
+-- Table structure for table "sc_commondescription"
+--
+
+CREATE SEQUENCE cdIds
+  INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+CREATE TABLE sc_commondescription (
+  cdId integer DEFAULT nextval('cdIds'::text) PRIMARY KEY,
+  uId integer NOT NULL,
+  tag varchar(100) DEFAULT '' NOT NULL,
+  bHash varchar(32) DEFAULT '' NOT NULL,
+  cdTitle varchar(255) DEFAULT '' NOT NULL,
+  cdDescription text,
+  cdDatetime timestamp with time zone DEFAULT now() NOT NULL
+);
+
+CREATE UNIQUE INDEX sc_commondescription_tag_timestamp on sc_commondescription (tag, cdDatetime);
+CREATE UNIQUE INDEX sc_commondescription_bookmark_timestamp on sc_commondescription (bHash, cdDatetime);
+
+--
+-- Table structure for table "sc_searchhistory"
+--
+
+CREATE SEQUENCE shIds
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+CREATE TABLE sc_searchhistory (
+  shId integer DEFAULT nextval('shIds'::text) PRIMARY KEY,
+  shTerms varchar(255) NOT NULL DEFAULT '',
+  shRange varchar(32) NOT NULL DEFAULT '',
+  shDatetime timestamp with time zone DEFAULT now() NOT NULL,
+  shNbResults integer NOT NULL,
+  uId integer NOT NULL
+);
+
+--
+-- Table structure for table "sc_tags"
+--
+
+CREATE SEQUENCE tIds
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+CREATE TABLE sc_tags (
+  tId integer DEFAULT nextval('tIds'::text) PRIMARY KEY,
+  tag varchar(100) NOT NULL DEFAULT '',
+  uId integer NOT NULL,
+  tDescription text
+);
+
+CREATE UNIQUE INDEX sc_tags_tag_uId on sc_tags (tag, uId);
+
+--
+-- Table structure for table "sc_tags2tags"
+--
+
+CREATE SEQUENCE ttIds
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+CREATE TABLE sc_tags2tags (
+  ttId integer DEFAULT nextval('ttIds'::text) PRIMARY KEY,
+  tag1 varchar(100) NOT NULL DEFAULT '',
+  tag2 varchar(100) NOT NULL DEFAULT '',
+  relationType varchar(32) NOT NULL DEFAULT '',
+  uId integer NOT NULL
+);
+
+CREATE UNIQUE INDEX sc_tags2tags_tag1_tag2_uId on sc_tags2tags (tag1, tag2, relationType, uId);
+
+--
+-- Table structure for table "sc_tagscache"
+--
+
+CREATE SEQUENCE tcIds
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+CREATE TABLE sc_tagscache (
+  tcId integer DEFAULT nextval('tcIds'::text) PRIMARY KEY,
+  tag1 varchar(100) NOT NULL DEFAULT '',
+  tag2 varchar(100) NOT NULL DEFAULT '',
+  relationType varchar(32) NOT NULL DEFAULT '',
+  uId integer NOT NULL DEFAULT '0'
+);
+
+CREATE UNIQUE INDEX sc_tagscache_tag1_tag2_type_uId on sc_tagscache (tag1, tag2, relationType, uId);
+
+--
+-- Table structure for table "sc_tagsstats"
+--
+
+CREATE SEQUENCE tstIds
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+CREATE TABLE sc_tagsstats (
+  tstId integer DEFAULT nextval('tstIds'::text) PRIMARY KEY,
+  tag1 varchar(100) NOT NULL DEFAULT '',
+  relationType varchar(32) NOT NULL DEFAULT '',
+  uId integer NOT NULL,
+  nb integer NOT NULL,
+  depth integer NOT NULL,
+  nbupdate integer NOT NULL
+);
+
+CREATE UNIQUE INDEX sc_tagsstats_tag1_type_uId on sc_tagsstats (tag1, relationType, uId);
+
+--
+-- Table structure for table "sc_users"
+--
+
+CREATE SEQUENCE uIds
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+CREATE TABLE sc_users (
+  uId integer DEFAULT nextval('uIds'::text) PRIMARY KEY,
+  username varchar(25) NOT NULL DEFAULT '',
+  password varchar(40) NOT NULL DEFAULT '',
+  uDatetime timestamp with time zone DEFAULT now() NOT NULL,
+  uModified timestamp with time zone DEFAULT now() NOT NULL,
+  name varchar(50) DEFAULT NULL,
+  email varchar(50) NOT NULL DEFAULT '',
+  homepage varchar(255) DEFAULT NULL,
+  uContent text,
+  privateKey varchar(33) DEFAULT NULL
+);
+
+CREATE UNIQUE INDEX privateKey on sc_users (privateKey);
+
+--
+-- Table structure for table "sc_users_sslclientcerts"
+--
+
+CREATE SEQUENCE ids
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+CREATE TABLE sc_users_sslclientcerts (
+  id integer DEFAULT nextval('ids'::text) PRIMARY KEY,
+  uId integer NOT NULL,
+  sslSerial varchar(32) DEFAULT '' NOT NULL,
+  sslClientIssuerDn varchar(1024) DEFAULT '' NOT NULL,
+  sslName varchar(64) DEFAULT '' NOT NULL,
+  sslEmail varchar(64) DEFAULT '' NOT NULL
+);
+
+--
+-- Table structure for table "sc_version"
+--
+
+CREATE TABLE sc_version (
+  schema_version integer NOT NULL
+);
+
+--
+-- Table structure for table "sc_votes"
+--
+
+CREATE TABLE sc_votes (
+  bId integer NOT NULL,
+  uId integer NOT NULL,
+  vote integer NOT NULL
+);
+
+CREATE UNIQUE INDEX bid_2 on sc_votes (bId, uId);
+CREATE INDEX bid on sc_votes (bId);
+CREATE INDEX uid on sc_votes (uId);
+
+--
+-- Table structure for table "sc_watched"
+--
+
+CREATE SEQUENCE wIds
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+CREATE TABLE sc_watched (
+  wId integer DEFAULT nextval('wIds'::text) PRIMARY KEY,
+  uId integer NOT NULL,
+  watched integer NOT NULL
+);
+
+CREATE INDEX sc_watched_uId on sc_watched (uId);
diff --git a/doc/ChangeLog b/doc/ChangeLog
index e5ca326..adb30bb 100644
--- a/doc/ChangeLog
+++ b/doc/ChangeLog
@@ -14,6 +14,7 @@ ChangeLog for SemantiScuttle
 - Fix bug #3431742: open_basedir problems with /etc/ config files
 - Fix bug #3436624: Wrong URL for Delicious API when importing
 - Implement request #3403609: fr_CA translation update
+- Implement patch #3476011: PostgreSQL tables can not be initialized
 
 
 0.98.3 - 2011-08-09
diff --git a/doc/INSTALL.txt b/doc/INSTALL.txt
index fbd44f9..953dc63 100644
--- a/doc/INSTALL.txt
+++ b/doc/INSTALL.txt
@@ -21,6 +21,8 @@ Installation instructions
 
    on the shell ("semanticscuttle" being the database name)
 
+   If you run PostgreSQL, use ``data/tables.postgresql.sql``.
+
 3. Copy ``data/config.php.dist`` to ``data/config.php`` and modify it as
    necessary. See configuration_ for more information.
 4. Make the cache directory writable by your web server.