root/trunk/db/postgresql.sql

CREATE TABLE %prefix%categories (
  category_id SERIAL PRIMARY KEY,
  category_name VARCHAR(255) NOT NULL,
  category_order INTEGER NOT NULL
);

CREATE TABLE %prefix%forums (
  forumid SERIAL PRIMARY KEY,
  forumtitle VARCHAR(255) NOT NULL,
  forumdescription VARCHAR(255) NOT NULL,
  forum_category INTEGER  NOT NULL,
  forum_order INTEGER  NOT NULL
);

CREATE TABLE %prefix%messages (
  messageid SERIAL PRIMARY KEY,
  messagesender INTEGER NOT NULL,
  messagereceipient INTEGER NOT NULL,
  messagetitle VARCHAR(255) NOT NULL,
  message TEXT NOT NULL,
  messagesenderflag INTEGER NOT NULL,
  messagereceipientflag INTEGER NOT NULL,
  messagetime TIMESTAMP NOT NULL
);

CREATE TABLE %prefix%online (
  onlinememberid INTEGER NOT NULL,
  onlinetime INTEGER NOT NULL,
  onlinesession VARCHAR(255) NOT NULL,
  onlineposted INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE %prefix%polls (
  pollid SERIAL PRIMARY KEY,
  pollchoices TEXT NOT NULL,
  pollvotes TEXT NOT NULL,
  pollvoters TEXT NOT NULL
);

CREATE TABLE %prefix%posts (
  postid SERIAL PRIMARY KEY,
  topic INTEGER NOT NULL,
  posteremail VARCHAR(255) NOT NULL,
  poster VARCHAR(255) NOT NULL,
  post TEXT NOT NULL,
  ip VARCHAR(16) NOT NULL,
  attachment VARCHAR(255) NOT NULL,
  postedbymember INTEGER NOT NULL,
  edited TIMESTAMP DEFAULT NULL,
  editedby VARCHAR(255) DEFAULT NULL,
  posttime TIMESTAMP NOT NULL
);

CREATE TABLE %prefix%topics (
  topicid SERIAL PRIMARY KEY,
  forum INTEGER NOT NULL,
  topictitle VARCHAR(255) NOT NULL,
  closed INTEGER NOT NULL DEFAULT 0,
  pinned INTEGER NOT NULL DEFAULT 0,
  poll INTEGER NOT NULL DEFAULT 0,
  topic_views INTEGER NOT NULL DEFAULT 0,
  posts INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE %prefix%groupmemberships (
    member INTEGER NOT NULL,
    usergroup INTEGER NOT NULL
);

CREATE TABLE %prefix%usergroups (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  public INTEGER NOT NULL DEFAULT 0,
  status INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE %prefix%members (
  memberid SERIAL PRIMARY KEY,
  membername VARCHAR(255) NOT NULL,
  memberemail VARCHAR(255) NOT NULL,
  memberemailhidden INTEGER NOT NULL DEFAULT 0,
  memberhomepage VARCHAR(255) NULL,
  memberavatar VARCHAR(255) NOT NULL,
  memberpassword VARCHAR(255) NOT NULL,
  memberposts INTEGER NOT NULL DEFAULT 0,
  location VARCHAR(255) NULL,
  icq VARCHAR(255) NULL,
  aim VARCHAR(255) NULL,
  yahoo VARCHAR(255) NULL,
  msn VARCHAR(255) NULL,
  jabber VARCHAR(255) NULL,
  signature VARCHAR(255) NULL,
  lastvisit TIMESTAMP NULL,
  boardstyle VARCHAR(32) NULL,
  addressbook TEXT NULL,
  birthday DATE DEFAULT NULL,
  messagenotification_email INTEGER NOT NULL DEFAULT 0,
  messagenotification_popup INTEGER NOT NULL DEFAULT 1,
  registered TIMESTAMP NOT NULL,
  salt VARCHAR(16) NULL,
  lang VARCHAR(16) NULL,
  attachments INTEGER NOT NULL DEFAULT 0,
  timezone VARCHAR(8) NULL,
  encoffset INTEGER NULL,
  banned INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE %prefix%mails (
  id INTEGER NOT NULL PRIMARY KEY,
  receipient VARCHAR(255) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  body TEXT NOT NULL
);

CREATE TABLE %prefix%moderators (
    forum INTEGER NOT NULL,
    member INTEGER NOT NULL
);

CREATE TABLE %prefix%subscriptions (
    topic INTEGER NOT NULL,
    member INTEGER NOT NULL
);

CREATE TABLE %prefix%rights (
    page VARCHAR(255) NOT NULL,
    usergroup INTEGER NOT NULL,
    r INTEGER NOT NULL,
    w INTEGER NOT NULL,
    PRIMARY KEY (page,usergroup)
);

CREATE TABLE %prefix%rights_default (
    page VARCHAR(255) NOT NULL,
    usergroup INTEGER NOT NULL,
    r INTEGER NOT NULL,
    w INTEGER NOT NULL,
    PRIMARY KEY (page,usergroup)
);

CREATE TABLE %prefix%logins (
    ip VARCHAR(16) NOT NULL,
    ts INTEGER NOT NULL
);

CREATE TABLE %prefix%adminlog (
    member INTEGER NOT NULL,
    ts TIMESTAMP NOT NULL,
    input_vars TEXT
);

CREATE TABLE %prefix%groupleaders (
  usergroup INTEGER NOT NULL,
  leader INTEGER NOT NULL
);

CREATE INDEX idx_forums ON %prefix%forums (forum_category ASC);
CREATE INDEX idx_topics ON %prefix%topics (forum ASC);
CREATE INDEX idx_posts_topic ON %prefix%posts (topic DESC, posttime ASC);
CREATE INDEX idx_posts_poster ON %prefix%posts (poster ASC, posttime DESC);
CREATE INDEX idx_posts_postedbymember ON %prefix%posts (postedbymember ASC, posttime DESC);
CREATE INDEX idx_members_membername ON %prefix%members (membername ASC);
CREATE INDEX idx_members_memberposts ON %prefix%members (memberposts DESC);
CREATE INDEX idx_members_registered ON %prefix%members (registered ASC);
CREATE INDEX idx_messagesender_messagetime ON %prefix%messages (messagesender ASC, messagetime DESC);
CREATE INDEX idx_messagereceipient_messagetime ON %prefix%messages (messagereceipient ASC, messagetime DESC);
CREATE INDEX idx_messages_receipient_flag_time ON %prefix%messages (messagereceipient ASC, messagereceipientflag DESC, messagetime DESC);
CREATE INDEX idx_messages_sender_flag_time ON %prefix%messages (messagesender ASC, messagesenderflag DESC, messagetime DESC);
CREATE INDEX idx_moderators_forum ON %prefix%moderators (forum ASC);
CREATE INDEX idx_moderators_member ON %prefix%moderators (member ASC);
CREATE INDEX idx_subscriptions_topic ON %prefix%subscriptions (topic ASC);
CREATE INDEX idx_subscriptions_member ON %prefix%subscriptions (member ASC);
CREATE INDEX idx_usergroups_name ON %prefix%usergroups (name ASC);