CREATE TABLE prefix_Addon ( a_name VARCHAR(20) NOT NULL DEFAULT '', a_ver SMALLINT NOT NULL DEFAULT 0, a_fullname VARCHAR(80) NOT NULL DEFAULT '', a_descr VARCHAR(255) NOT NULL DEFAULT '', PRIMARY KEY(a_name, a_ver) ); CREATE TABLE prefix_AdminEntry ( ad_name VARCHAR(40) NOT NULL DEFAULT '', ad_category VARCHAR(20) NOT NULL DEFAULT '', ad_url VARCHAR(128) NOT NULL DEFAULT '', ad_sortfield SMALLINT NOT NULL DEFAULT 0, PRIMARY KEY(ad_name) ); CREATE INDEX SORTFIELD ON prefix_AdminEntry (ad_sortfield); CREATE TABLE prefix_Article ( a_tid INTEGER NOT NULL, a_author VARCHAR(64) NOT NULL DEFAULT '', a_authormail VARCHAR(48) NOT NULL DEFAULT '', a_origin VARCHAR(128) NOT NULL DEFAULT '', a_originurl VARCHAR(128) NOT NULL DEFAULT '', a_disc_tid INTEGER NULL DEFAULT 0, PRIMARY KEY(a_tid) ); CREATE TABLE prefix_BadWord ( w_id SERIAL, w_bad CHAR(32) NOT NULL DEFAULT '', w_good CHAR(32) NOT NULL DEFAULT '', PRIMARY KEY(w_id) ); CREATE TABLE prefix_Bookmark ( uid INTEGER NOT NULL DEFAULT 0, tid INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(uid, tid) ); CREATE INDEX XIF40Bookmark ON prefix_Bookmark (tid); CREATE TABLE prefix_Category ( ct_id SERIAL, ct_name VARCHAR(32) NOT NULL DEFAULT '', ct_sortfield INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(ct_id) ); CREATE INDEX XPKSort ON prefix_Category (ct_sortfield); CREATE TABLE prefix_Code ( sid CHAR(32) NOT NULL DEFAULT '', code CHAR(8) NOT NULL DEFAULT '', time INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(sid) ); CREATE TABLE prefix_Download ( dl_tid INTEGER NOT NULL DEFAULT 0, dl_url VARCHAR(128) NOT NULL DEFAULT '', dl_homepage VARCHAR(128) NOT NULL DEFAULT '', dl__downloads INTEGER NOT NULL DEFAULT 0, dl_disc_tid INTEGER NOT NULL DEFAULT 0, dl_size VARCHAR(8) NOT NULL DEFAULT '', PRIMARY KEY(dl_tid) ); CREATE INDEX XPKDownload ON prefix_Download (dl_tid); CREATE TABLE prefix_File ( file_id SERIAL, file_size INTEGER NOT NULL DEFAULT 0, file_type VARCHAR(64) NOT NULL DEFAULT '', file_name VARCHAR(32) NOT NULL DEFAULT '', PRIMARY KEY(file_id) ); CREATE TABLE prefix_Forum ( f_id SERIAL, f_tpid SMALLINT NOT NULL DEFAULT 0, f_ctid INTEGER NOT NULL DEFAULT 0, f_title VARCHAR(60) NOT NULL DEFAULT '', f_descr VARCHAR(255) NOT NULL DEFAULT '', f_status SMALLINT NOT NULL DEFAULT 0, f_lview SMALLINT NOT NULL DEFAULT 0, f_lread SMALLINT NOT NULL DEFAULT 0, f_sortfield INTEGER NOT NULL DEFAULT 0, f_lpost SMALLINT NOT NULL DEFAULT 0, f_ltopic SMALLINT NOT NULL DEFAULT 0, f_ledit SMALLINT NOT NULL DEFAULT 0, f_lhtml SMALLINT NOT NULL DEFAULT 0, f_lattach SMALLINT NOT NULL DEFAULT 0, f_lvote SMALLINT NOT NULL DEFAULT 0, f_lmoderate SMALLINT NOT NULL DEFAULT 0, f_lip SMALLINT NOT NULL DEFAULT 0, f_lpoll SMALLINT NOT NULL DEFAULT 0, f_lsticky SMALLINT NOT NULL DEFAULT 0, f_rate SMALLINT NOT NULL DEFAULT 0, f_bcode SMALLINT NOT NULL DEFAULT 0, f_nostats SMALLINT NOT NULL DEFAULT 0, f_rules TEXT NOT NULL DEFAULT '', f_smiles SMALLINT NOT NULL DEFAULT 0, f_attachpics SMALLINT NOT NULL DEFAULT 0, f_selfmod SMALLINT NOT NULL DEFAULT 0, f_parent INTEGER NOT NULL DEFAULT 0, f_text TEXT NOT NULL DEFAULT '', f_url VARCHAR(255) NOT NULL DEFAULT '', f_nonewpic VARCHAR(20) NOT NULL DEFAULT '', f_newpic VARCHAR(20) NOT NULL DEFAULT '', f_premoderate SMALLINT NOT NULL DEFAULT 0, f_lnid INTEGER NOT NULL DEFAULT 0, f_downloads INTEGER NOT NULL DEFAULT 0, f_update INTEGER NOT NULL DEFAULT 0, f__tcount INTEGER NOT NULL DEFAULT 0, f__pcount INTEGER NOT NULL DEFAULT 0, f__lastpostid INTEGER NOT NULL DEFAULT 0, f__startpostid INTEGER NOT NULL DEFAULT 0, f__premodcount INTEGER NOT NULL DEFAULT 0, f__views INTEGER NOT NULL DEFAULT 0, f_hidden SMALLINT NOT NULL DEFAULT 0, f_nosubs SMALLINT NOT NULL DEFAULT 0, PRIMARY KEY(f_id) ); CREATE INDEX ctid ON prefix_Forum (f_ctid); CREATE INDEX lview ON prefix_Forum (f_lview); CREATE INDEX sortfield1 ON prefix_Forum (f_sortfield); CREATE INDEX parent ON prefix_Forum (f_parent); CREATE INDEX tpid ON prefix_Forum (f_tpid); CREATE TABLE prefix_ForumType ( tp_id SMALLINT NOT NULL DEFAULT 0, tp_title VARCHAR(40) NOT NULL DEFAULT '', tp_library VARCHAR(20) NOT NULL DEFAULT '', tp_template VARCHAR(20) NOT NULL DEFAULT '', tp_modlib VARCHAR(20) NOT NULL DEFAULT '', tp_searchable SMALLINT NOT NULL DEFAULT 0, tp_container SMALLINT NOT NULL DEFAULT 0, tp_menu SMALLINT NOT NULL DEFAULT 0, tp_external SMALLINT NOT NULL DEFAULT 0, PRIMARY KEY(tp_id) ); CREATE TABLE prefix_ForumView ( uid INTEGER NOT NULL DEFAULT 0, fid INTEGER NOT NULL DEFAULT 0, fv_count INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (fid,uid) ); CREATE INDEX uids ON prefix_ForumView (uid); CREATE TABLE prefix_Language ( ln_id SERIAL, ln_name VARCHAR(20) NOT NULL DEFAULT '', ln_file VARCHAR(20) NOT NULL DEFAULT '', ln_locale VARCHAR(20) NOT NULL DEFAULT '', ln_charset VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY(ln_id) ); CREATE TABLE prefix_LastVisit ( uid INTEGER NOT NULL DEFAULT 0, fid INTEGER NOT NULL DEFAULT 0, lv_time1 INTEGER NOT NULL DEFAULT 0, lv_time2 INTEGER NOT NULL DEFAULT 0, lv_markall INTEGER NOT NULL DEFAULT 0, lv_markcount INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(uid, fid) ); CREATE INDEX XIF62LastVisit ON prefix_LastVisit (fid); CREATE TABLE prefix_LogSession ( sid_id SERIAL, sid CHAR(32) DEFAULT '', uo_ip INTEGER NULL, uo_maxuid INTEGER NULL, uo_curid INTEGER NULL, uo_lasttime INTEGER NULL, PRIMARY KEY (sid_id) ); CREATE INDEX timekey ON prefix_LogSession (uo_lasttime); CREATE TABLE prefix_LogEntry ( uo_id INTEGER NOT NULL DEFAULT 0, uo_tid INTEGER NOT NULL DEFAULT 0, uo_fid INTEGER NOT NULL DEFAULT 0, uo_action VARCHAR(20) NOT NULL DEFAULT '', uo_module VARCHAR(20) NOT NULL DEFAULT '', uo_mode SMALLINT NOT NULL DEFAULT 0, uo_time INTEGER NOT NULL DEFAULT 0 ); CREATE INDEX sid ON prefix_LogEntry (uo_id); CREATE INDEX timekey2 ON prefix_LogEntry (uo_time); CREATE TABLE prefix_PersonalMessage ( pm_id SERIAL, pm__box SMALLINT NOT NULL DEFAULT 0, pm__owner INTEGER NULL, pm__correspondent INTEGER NOT NULL DEFAULT 0, pm__senddate INTEGER NOT NULL DEFAULT 0, pm__readdate INTEGER NOT NULL DEFAULT 0, pm_text TEXT NOT NULL, pm_signature SMALLINT NOT NULL DEFAULT 0, pm_smiles SMALLINT NOT NULL DEFAULT 0, pm__html SMALLINT NOT NULL DEFAULT 0, pm_bcode SMALLINT NOT NULL DEFAULT 0, pm_pair INTEGER NULL DEFAULT 0, pm_subj VARCHAR(80) NOT NULL DEFAULT '', PRIMARY KEY(pm_id) ); CREATE INDEX owner ON prefix_PersonalMessage (pm__owner, pm__box); CREATE TABLE prefix_Photo ( ph_id SERIAL, ph_tid INTEGER NULL, ph_key CHAR(8) NULL DEFAULT '', PRIMARY KEY(ph_id) ); CREATE UNIQUE INDEX Photo_TID ON prefix_Photo (ph_tid); CREATE TABLE prefix_Poll ( pl_id SERIAL, pl_tid INTEGER NOT NULL DEFAULT 0, pl_title VARCHAR(60) NOT NULL DEFAULT '', pl_enddate INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(pl_id) ); CREATE INDEX PL_topicid ON prefix_Poll (pl_tid); CREATE TABLE prefix_PollVariant ( pv_id SERIAL, pv_plid INTEGER NOT NULL DEFAULT 0, pv_text VARCHAR(80) NOT NULL DEFAULT '', pv_count INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(pv_id) ); CREATE INDEX XIF47PollVariant ON prefix_PollVariant (pv_plid); CREATE TABLE prefix_Post ( p_id SERIAL, p_tid INTEGER NOT NULL DEFAULT 0, p_uid INTEGER NOT NULL DEFAULT 0, p_text TEXT NOT NULL, p__modcomment TEXT NULL, p__time INTEGER NOT NULL DEFAULT 0, p__edittime INTEGER NOT NULL DEFAULT 0, p_signature SMALLINT NOT NULL DEFAULT 0, p__smiles SMALLINT NOT NULL DEFAULT 0, p__bcode SMALLINT NOT NULL DEFAULT 0, p__html SMALLINT NOT NULL DEFAULT 0, p_attach INTEGER NOT NULL DEFAULT 0, p_uname VARCHAR(32) NOT NULL DEFAULT '', p__ip INTEGER NOT NULL DEFAULT 0, p_title VARCHAR(64) NOT NULL DEFAULT '', p__premoderate SMALLINT NOT NULL DEFAULT 0, fulltext_idx tsvector, PRIMARY KEY(p_id) ); CREATE INDEX tid ON prefix_Post (p_tid, p__premoderate); CREATE INDEX time ON prefix_Post (p__time); CREATE INDEX uid ON prefix_Post (p_uid); CREATE INDEX idx_fulltext_idx ON prefix_Post USING gist(fulltext_idx); CREATE TRIGGER tg_fulltext_prefix_Post BEFORE UPDATE OR INSERT ON prefix_Post FOR EACH ROW EXECUTE PROCEDURE tsearch2(fulltext_idx, p_text, p_title); CREATE TABLE prefix_Smile ( sm_code CHAR(8) NOT NULL DEFAULT '', sm_file CHAR(20) NULL DEFAULT '', sm_show SMALLINT NOT NULL DEFAULT 1, PRIMARY KEY(sm_code) ); CREATE INDEX XPKSmile ON prefix_Smile (sm_code); CREATE TABLE prefix_StyleSet ( st_id SERIAL, st_name VARCHAR(40) NOT NULL DEFAULT '', st_file VARCHAR(20) NOT NULL DEFAULT '', st_show SMALLINT NOT NULL DEFAULT 0, st_parent VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY(st_id) ); CREATE TABLE prefix_Subscription ( uid INTEGER NOT NULL DEFAULT 0, tid BIGINT NOT NULL DEFAULT 0, fid INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(uid, tid, fid) ); CREATE INDEX uid_idx ON prefix_Subscription (uid); CREATE INDEX tid_idx ON prefix_Subscription (tid); CREATE TABLE prefix_Topic ( t_id SERIAL, t_fid INTEGER NOT NULL DEFAULT 0, t_title VARCHAR(60) NOT NULL DEFAULT '', t_descr VARCHAR(255) NOT NULL DEFAULT '', t__views INTEGER NOT NULL DEFAULT 0, t__sticky SMALLINT NOT NULL DEFAULT 0, t__stickypost SMALLINT NOT NULL DEFAULT 0, t__status SMALLINT NOT NULL DEFAULT 0, t__rate SMALLINT NOT NULL DEFAULT 0, t__pcount INTEGER NOT NULL DEFAULT 0, t__startpostid INTEGER NOT NULL DEFAULT 0, t__lastpostid INTEGER NOT NULL DEFAULT 0, t__ratingsum INTEGER NOT NULL DEFAULT 0, t__ratingcount INTEGER NOT NULL DEFAULT 0, fulltxt_idx tsvector, PRIMARY KEY(t_id) ); CREATE INDEX XIF32Topic ON prefix_Topic (t_fid); CREATE INDEX T_stickykey ON prefix_Topic (t__sticky); CREATE INDEX idx_title ON prefix_Topic USING gist(fulltxt_idx); CREATE TRIGGER tg_fulltxt_prefix_Topic BEFORE UPDATE OR INSERT ON prefix_Topic FOR EACH ROW EXECUTE PROCEDURE tsearch2(fulltxt_idx, t_title,t_descr); CREATE TABLE prefix_TopicRate ( tid INTEGER NOT NULL DEFAULT 0, uid INTEGER NOT NULL DEFAULT 0, tr_value SMALLINT NOT NULL DEFAULT 0, PRIMARY KEY(tid, uid) ); CREATE INDEX XIF42TopicRate ON prefix_TopicRate (uid); CREATE TABLE prefix_TopicView ( tid INTEGER NOT NULL DEFAULT 0, uid INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(tid, uid) ); CREATE INDEX XIF35TopicView ON prefix_TopicView (uid); CREATE TABLE prefix_UGroup ( g_id SERIAL, g_title VARCHAR(20) NOT NULL DEFAULT '', g_setlevel SMALLINT NOT NULL DEFAULT 0, g_ljoin SMALLINT NOT NULL DEFAULT 0, g_lview SMALLINT NOT NULL DEFAULT 0, g_lautojoin SMALLINT NOT NULL DEFAULT 0, g_descr VARCHAR(255) NOT NULL DEFAULT '', g_allowquit SMALLINT NOT NULL DEFAULT 0, PRIMARY KEY(g_id) ); CREATE TABLE prefix_UGroupAccess ( gid INTEGER NOT NULL DEFAULT 0, fid INTEGER NOT NULL DEFAULT 0, ga_level SMALLINT NULL, PRIMARY KEY(gid, fid) ); CREATE INDEX XIF44GroupAccess ON prefix_UGroupAccess (fid); CREATE INDEX XIF51GroupAccess ON prefix_UGroupAccess (ga_level); CREATE TABLE prefix_UGroupMember ( gid INTEGER NOT NULL DEFAULT 0, uid INTEGER NOT NULL DEFAULT 0, gm_status SMALLINT NOT NULL DEFAULT 0, PRIMARY KEY(gid, uid) ); CREATE INDEX XIF28GroupMember ON prefix_UGroupMember (uid); CREATE TABLE prefix_User ( u_id SERIAL, u__email VARCHAR(48) NOT NULL DEFAULT '', u_lnid INTEGER NOT NULL DEFAULT 0, u_stid INTEGER NOT NULL DEFAULT 0, u__level SMALLINT NOT NULL DEFAULT 0, u__name VARCHAR(32) NOT NULL DEFAULT '', u__active SMALLINT NOT NULL DEFAULT 0, u__password VARCHAR(32) NOT NULL DEFAULT '', u_usesignature SMALLINT NOT NULL DEFAULT 0, u_showmail SMALLINT NOT NULL DEFAULT 0, u_usesmiles SMALLINT NOT NULL DEFAULT 0, u_signature VARCHAR(255) NOT NULL DEFAULT '', u_gender SMALLINT NOT NULL DEFAULT 0, u_sformat VARCHAR(20) NOT NULL DEFAULT '', u_lformat VARCHAR(20) NOT NULL DEFAULT '', u_location VARCHAR(40) NOT NULL DEFAULT '', u_bday SMALLINT NOT NULL DEFAULT 0, u_bmonth SMALLINT NOT NULL DEFAULT 0, u_byear SMALLINT NOT NULL DEFAULT 0, u_bmode SMALLINT NOT NULL DEFAULT 0, u_tperpage SMALLINT NOT NULL DEFAULT 0, u_mperpage SMALLINT NOT NULL DEFAULT 0, u_homepage VARCHAR(128) NOT NULL DEFAULT '', u_showavatars SMALLINT NOT NULL DEFAULT 0, u_avatartype SMALLINT NOT NULL DEFAULT 0, u__avatar VARCHAR(128) NOT NULL DEFAULT '', u__pavatar_id INTEGER NOT NULL DEFAULT 0, u__photo_id INTEGER NOT NULL DEFAULT 0, u_encrypted SMALLINT NOT NULL DEFAULT 0, u__regdate INTEGER NOT NULL DEFAULT 0, u__profileupdate INTEGER NOT NULL DEFAULT 0, u__newpassword VARCHAR(32) NOT NULL DEFAULT '', u__title VARCHAR(48) NOT NULL DEFAULT '', u_timeregion SMALLINT NOT NULL DEFAULT 0, u_icq INTEGER NOT NULL DEFAULT 0, u_interests VARCHAR(255) NOT NULL DEFAULT '', u_hidden SMALLINT NOT NULL DEFAULT 0, u__key VARCHAR(12) NOT NULL DEFAULT '', u_detrans SMALLINT NOT NULL DEFAULT 0, u_nomails SMALLINT NOT NULL DEFAULT 0, u_sortposts SMALLINT NOT NULL DEFAULT 0, u_multilang SMALLINT NOT NULL DEFAULT 0, u_diary VARCHAR(128) NOT NULL DEFAULT '', u_timelimit SMALLINT NOT NULL DEFAULT 0, u_aol VARCHAR(32) NOT NULL DEFAULT '', u_yahoo VARCHAR(32) NOT NULL DEFAULT '', u_msn VARCHAR(64) NOT NULL DEFAULT '', u_jabber VARCHAR(64) NOT NULL DEFAULT '', u_extform SMALLINT NOT NULL DEFAULT 1, u_aperpage SMALLINT NOT NULL DEFAULT 0, u__rating INTEGER NOT NULL DEFAULT 0, u__warnings INTEGER NOT NULL DEFAULT 0, u__pmcount INTEGER NOT NULL DEFAULT 0, u__pmtime INTEGER NOT NULL DEFAULT 0, u__warntime INTEGER NOT NULL DEFAULT 0, u__lastlogin INTEGER NOT NULL DEFAULT 0, u_pmnotify SMALLINT NOT NULL DEFAULT 1, u_realname VARCHAR(255) NOT NULL DEFAULT '', PRIMARY KEY(u_id) ); CREATE UNIQUE INDEX email ON prefix_User (u__email); CREATE UNIQUE INDEX name_idx ON prefix_User (u__name); CREATE INDEX level ON prefix_User (u__level); CREATE TABLE prefix_UserAccess ( uid INTEGER NOT NULL DEFAULT 0, fid INTEGER NOT NULL DEFAULT 0, ua_level SMALLINT NOT NULL DEFAULT 0, PRIMARY KEY(uid, fid) ); CREATE INDEX XIF37UserAccess ON prefix_UserAccess (fid); CREATE INDEX XIF50UserAccess ON prefix_UserAccess (ua_level); CREATE TABLE prefix_UserLevel ( l_level SMALLINT NOT NULL DEFAULT 0, l_title VARCHAR(48) NOT NULL DEFAULT '', l_minpost INTEGER NULL DEFAULT 0, l_custom SMALLINT NOT NULL DEFAULT 0, PRIMARY KEY(l_level) ); CREATE INDEX Minpost ON prefix_UserLevel (l_minpost, l_custom); CREATE TABLE prefix_UserRating ( uid INTEGER NOT NULL DEFAULT 0, ur_rated INTEGER NOT NULL DEFAULT 0, ur_time INTEGER NOT NULL DEFAULT 0, ur_value SMALLINT NOT NULL DEFAULT 0, PRIMARY KEY(uid, ur_rated, ur_time) ); CREATE TABLE prefix_UserStat ( uid INTEGER NOT NULL DEFAULT 0, fid INTEGER NOT NULL DEFAULT 0, us_count INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (fid,uid) ); CREATE INDEX uids_us ON prefix_UserStat (uid); CREATE TABLE prefix_UserWarning ( uw_id SERIAL, uw_uid INTEGER NOT NULL DEFAULT 0, uw_value SMALLINT NOT NULL DEFAULT 0, uw_warner INTEGER NOT NULL DEFAULT 0, uw_validtill INTEGER NOT NULL DEFAULT 0, uw_comment VARCHAR(255) NOT NULL DEFAULT '', PRIMARY KEY(uw_id, uw_uid) ); CREATE INDEX XIF64UserWarning ON prefix_UserWarning (uw_uid); CREATE TABLE prefix_Vote ( pvid INTEGER NOT NULL, uid INTEGER NOT NULL DEFAULT 0, tid INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(pvid, uid) ); CREATE INDEX Topics_v ON prefix_Vote (tid); CREATE TABLE prefix_Search ( sr_id SERIAL, sr_text VARCHAR(255) NOT NULL DEFAULT '', sr_mode SMALLINT NOT NULL DEFAULT 0, sr_type SMALLINT NOT NULL DEFAULT 0, sr_starttime INTEGER NOT NULL DEFAULT 0, sr_endtime INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(sr_id) ); CREATE TABLE prefix_SearchResult ( srid INTEGER NOT NULL DEFAULT 0, srpid INTEGER NOT NULL DEFAULT 0, relevancy FLOAT NOT NULL DEFAULT 0.0, PRIMARY KEY (srid,srpid) );