From 39b44229ad11f8544a876dd750c479947f783ca8 Mon Sep 17 00:00:00 2001 From: Christian Weiske Date: Fri, 20 Jan 2012 14:24:33 +0100 Subject: Implement patch #3476011: PostgreSQL tables can not be initialized --- data/tables-postgresql.sql | 259 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 259 insertions(+) create mode 100644 data/tables-postgresql.sql (limited to 'data/tables-postgresql.sql') 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); -- cgit v1.2.3