tables-postgresql.sql

-- Semantic Scuttle - Tables creation SQL script
-- ! Dont forget to change table names according to $tableprefix defined in config.php !


DROP TABLE IF EXISTS sc_bookmarks2tags; 
DROP TABLE IF EXISTS sc_commondescription;
DROP TABLE IF EXISTS sc_searchhistory; 
DROP TABLE IF EXISTS sc_tags; 
DROP TABLE IF EXISTS sc_tags2tags;
DROP TABLE IF EXISTS sc_tagscache;
DROP TABLE IF EXISTS sc_tagsstats;
DROP TABLE IF EXISTS sc_users_sslclientcerts;
DROP TABLE IF EXISTS sc_version;
DROP TABLE IF EXISTS sc_votes;
DROP TABLE IF EXISTS sc_watched;
DROP TABLE IF EXISTS sc_bookmarks;
DROP TABLE IF EXISTS sc_users;


DROP SEQUENCE IF EXISTS  bIds;
DROP SEQUENCE IF EXISTS  b2tIds;
DROP SEQUENCE IF EXISTS  cdIds;
DROP SEQUENCE IF EXISTS  shIds;
DROP SEQUENCE IF EXISTS  tIds;
DROP SEQUENCE IF EXISTS  ttIds;
DROP SEQUENCE IF EXISTS  tcIds;
DROP SEQUENCE IF EXISTS  tstIds;
DROP SEQUENCE IF EXISTS  uIds;
DROP SEQUENCE IF EXISTS  ids;
DROP SEQUENCE IF EXISTS  vIds;
DROP SEQUENCE IF EXISTS  wIds;



--
-- 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) DEFAULT '',
  uDatetime timestamp with time zone DEFAULT now(),
  uModified timestamp with time zone DEFAULT now(),
  name varchar(50) DEFAULT NULL,
  email varchar(50) DEFAULT NULL,
  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_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 REFERENCES sc_users (uId),
  tDescription text
);

--
-- 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 REFERENCES sc_users (uId),
  bIp varchar(40) DEFAULT NULL,
  bStatus smallint NOT NULL,
  bDatetime timestamp with time zone DEFAULT now(),
  bModified timestamp with time zone DEFAULT now(),
  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 REFERENCES sc_bookmarks(bId), 
  tId integer REFERENCES sc_tags(tId) 
);

CREATE UNIQUE INDEX sc_bookmarks2tags_tag_bId on sc_bookmarks2tags (tId, 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 REFERENCES sc_users (uId),
  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 REFERENCES sc_users (uId)
);


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 REFERENCES sc_users (uId)
);

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 REFERENCES sc_users (uId),
  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_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 REFERENCES sc_users (uId),
  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 SEQUENCE vIds
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;



CREATE TABLE sc_votes (
  id integer DEFAULT nextval('vIds'::text) PRIMARY KEY,
  bId integer REFERENCES sc_bookmarks (bId),
  uId integer REFERENCES sc_users (uId),
  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 REFERENCES sc_users (uId),
  watched integer NOT NULL
);

CREATE INDEX sc_watched_uId on sc_watched (uId);