Changeset 110

Show
Ignore:
Timestamp:
04/26/08 16:08:11 (7 months ago)
Author:
pragma
Message:

Revised DB scripts

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • trunk/sql/postgres.sql

    r81 r110  
    1 -- 
    2 -- PostgreSQL database dump 
    3 -- 
    4  
    5 -- Started on 2007-09-16 23:41:07 Eastern Daylight Time 
    6  
    7 SET client_encoding = 'UTF8'; 
    8 SET check_function_bodies = false; 
    9 SET client_min_messages = warning; 
    10  
    11 -- 
    12 -- TOC entry 1612 (class 0 OID 0) 
    13 -- Dependencies: 4 
    14 -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres 
    15 -- 
    16  
    17 COMMENT ON SCHEMA public IS 'Standard public schema'; 
    18  
    19  
    20 SET search_path = public, pg_catalog; 
    21  
    22 SET default_tablespace = ''; 
    23  
    24 SET default_with_oids = false; 
    25  
    26 -- 
    27 -- TOC entry 1233 (class 1259 OID 20723) 
    28 -- Dependencies: 1592 4 
    29 -- Name: avatar; Type: TABLE; Schema: public; Owner: postgres; Tablespace:  
    30 -- 
    31  
    32 CREATE TABLE avatar ( 
    33     username character varying(32), 
    34     mimetype character varying(256), 
    35     name text, 
    36     id integer DEFAULT nextval(('avatar_id_seq'::text)::regclass) 
     1CREATE TABLE avatar 
     2( 
     3  id serial NOT NULL,   
     4  name text, 
     5  username varchar(32), 
     6  mimetype varchar(256),   
     7  CONSTRAINT forum_pkey PRIMARY KEY (id), 
     8  CONSTRAINT forum_id_key UNIQUE (id) 
    379); 
    3810 
    39  
    40 ALTER TABLE public.avatar OWNER TO postgres; 
    41  
    42 -- 
    43 -- TOC entry 1234 (class 1259 OID 20729) 
    44 -- Dependencies: 4 
    45 -- Name: avatar_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres 
    46 -- 
    47  
    48 CREATE SEQUENCE avatar_id_seq 
    49     INCREMENT BY 1 
    50     NO MAXVALUE 
    51     NO MINVALUE 
    52     CACHE 1; 
    53  
    54  
    55 ALTER TABLE public.avatar_id_seq OWNER TO postgres; 
    56  
    57 -- 
    58 -- TOC entry 1228 (class 1259 OID 20679) 
    59 -- Dependencies: 1569 1570 1571 1572 4 
    60 -- Name: forum; Type: TABLE; Schema: public; Owner: postgres; Tablespace:  
    61 -- 
    62  
    63 CREATE TABLE forum ( 
    64     id integer DEFAULT nextval(('forum_id_seq'::text)::regclass) NOT NULL, 
    65     name text, 
    66     created integer, 
    67     modified integer, 
    68     description text, 
    69     projectid character varying(32), 
    70     locked boolean DEFAULT false, 
    71     hidden boolean DEFAULT false, 
    72     categoryid integer DEFAULT 0, 
    73     rank serial NOT NULL 
     11CREATE TABLE forum 
     12( 
     13  id serial NOT NULL, 
     14  name text, 
     15  created int4, 
     16  modified int4, 
     17  description text, 
     18  projectid varchar(32), 
     19  locked bool DEFAULT false, 
     20  hidden bool DEFAULT false, 
     21  categoryid int4 DEFAULT 0, 
     22  rank serial NOT NULL, 
     23  CONSTRAINT forum_pkey PRIMARY KEY (id), 
     24  CONSTRAINT forum_id_key UNIQUE (id, name) 
    7425); 
    7526 
    76  
    77 ALTER TABLE public.forum OWNER TO postgres; 
    78  
    79 -- 
    80 -- TOC entry 1243 (class 1259 OID 1115442) 
    81 -- Dependencies: 4 
    82 -- Name: forum_category; Type: TABLE; Schema: public; Owner: postgres; Tablespace:  
    83 -- 
    84  
    85 CREATE TABLE forum_category ( 
    86     id serial NOT NULL, 
    87     projectid character varying(32), 
    88     description text, 
    89     rank serial NOT NULL 
     27CREATE TABLE forum_category 
     28( 
     29  id serial NOT NULL, 
     30  projectid varchar(32), 
     31  description text, 
     32  rank serial NOT NULL, 
     33  CONSTRAINT forum_category_pkey PRIMARY KEY (id) 
    9034); 
    9135 
    92  
    93 ALTER TABLE public.forum_category OWNER TO postgres; 
    94  
    95 -- 
    96 -- TOC entry 1226 (class 1259 OID 20675) 
    97 -- Dependencies: 4 
    98 -- Name: forum_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres 
    99 -- 
    100  
    101 CREATE SEQUENCE forum_id_seq 
    102     INCREMENT BY 1 
    103     NO MAXVALUE 
    104     NO MINVALUE 
    105     CACHE 1; 
    106  
    107  
    108 ALTER TABLE public.forum_id_seq OWNER TO postgres; 
    109  
    110 -- 
    111 -- TOC entry 1237 (class 1259 OID 1115372) 
    112 -- Dependencies: 4 
    113 -- Name: forum_watch; Type: TABLE; Schema: public; Owner: postgres; Tablespace:  
    114 -- 
    115  
    116 CREATE TABLE forum_watch ( 
    117     id serial NOT NULL, 
    118     forumid integer, 
    119     username text 
     36CREATE TABLE forum_watch 
     37( 
     38  id serial NOT NULL, 
     39  forumid int4, 
     40  username text, 
     41  CONSTRAINT forum_watch_pkey PRIMARY KEY (id) 
    12042); 
    12143 
    122  
    123 ALTER TABLE public.forum_watch OWNER TO postgres; 
    124  
    125 -- 
    126 -- TOC entry 1229 (class 1259 OID 20689) 
    127 -- Dependencies: 1574 1575 1576 1577 4 
    128 -- Name: message; Type: TABLE; Schema: public; Owner: postgres; Tablespace:  
    129 -- 
    130  
    131 CREATE TABLE message ( 
    132     id integer DEFAULT nextval(('message_id_seq'::text)::regclass) NOT NULL, 
    133     topicid integer, 
    134     created integer, 
    135     modified integer DEFAULT 0, 
    136     author text, 
    137     body text, 
    138     avatarid integer DEFAULT 0, 
    139     modifiedby character varying(32), 
    140     modcount integer DEFAULT 0 
     44CREATE TABLE message 
     45( 
     46  id serial NOT NULL, 
     47  topicid int4, 
     48  created int4, 
     49  modified int4 DEFAULT 0, 
     50  author text, 
     51  body text, 
     52  avatarid int4 DEFAULT 0, 
     53  modifiedby varchar(32), 
     54  modcount int4 DEFAULT 0, 
     55  CONSTRAINT message_pkey PRIMARY KEY (id) 
    14156); 
    14257 
    143  
    144 ALTER TABLE public.message OWNER TO postgres; 
    145  
    146 -- 
    147 -- TOC entry 1225 (class 1259 OID 20673) 
    148 -- Dependencies: 4 
    149 -- Name: message_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres 
    150 -- 
    151  
    152 CREATE SEQUENCE message_id_seq 
    153     INCREMENT BY 1 
    154     NO MAXVALUE 
    155     NO MINVALUE 
    156     CACHE 1; 
    157  
    158  
    159 ALTER TABLE public.message_id_seq OWNER TO postgres; 
    160  
    161 -- 
    162 -- TOC entry 1232 (class 1259 OID 20719) 
    163 -- Dependencies: 4 
    164 -- Name: moderators; Type: TABLE; Schema: public; Owner: postgres; Tablespace:  
    165 -- 
    166  
    167 CREATE TABLE moderators ( 
    168     forumid integer NOT NULL, 
    169     username character varying(25) NOT NULL 
     58CREATE TABLE moderators 
     59( 
     60  forumid int4 NOT NULL, 
     61  username varchar(25) NOT NULL 
    17062); 
    17163 
    172  
    173 ALTER TABLE public.moderators OWNER TO postgres; 
    174  
    175 -- 
    176 -- TOC entry 1231 (class 1259 OID 20705) 
    177 -- Dependencies: 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 4 
    178 -- Name: profile; Type: TABLE; Schema: public; Owner: postgres; Tablespace:  
    179 -- 
    180  
    181 CREATE TABLE profile ( 
    182     id integer DEFAULT nextval(('profile_id_seq'::text)::regclass) NOT NULL, 
    183     sig text DEFAULT ''::text, 
    184     username character varying(25) NOT NULL, 
    185     email character varying(255), 
    186     regdate integer DEFAULT 0 NOT NULL, 
    187     lastvisit integer DEFAULT 0 NOT NULL, 
    188     defaultavatarid integer DEFAULT 0, 
    189     bio text, 
    190     logindate integer DEFAULT 0, 
    191     timezone character varying(8), 
    192     viewemail boolean DEFAULT true, 
    193     isactive boolean DEFAULT false, 
    194     isexpert boolean DEFAULT false, 
    195     posts integer DEFAULT 0 
     64CREATE TABLE profile 
     65( 
     66  sig text, 
     67  username varchar(25) NOT NULL, 
     68  email varchar(255), 
     69  regdate int4 NOT NULL DEFAULT 0, 
     70  lastvisit int4 NOT NULL DEFAULT 0, 
     71  defaultavatarid int4 DEFAULT 0, 
     72  bio text, 
     73  logindate int4 DEFAULT 0, 
     74  timezone varchar(8), 
     75  viewemail bool DEFAULT true, 
     76  isactive bool DEFAULT false, 
     77  isexpert bool DEFAULT false, 
     78  posts int4 DEFAULT 0 
    19679); 
    19780 
    198  
    199 ALTER TABLE public.profile OWNER TO postgres; 
    200  
    201 -- 
    202 -- TOC entry 1227 (class 1259 OID 20677) 
    203 -- Dependencies: 4 
    204 -- Name: profile_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres 
    205 -- 
    206  
    207 CREATE SEQUENCE profile_id_seq 
    208     INCREMENT BY 1 
    209     NO MAXVALUE 
    210     NO MINVALUE 
    211     CACHE 1; 
    212  
    213  
    214 ALTER TABLE public.profile_id_seq OWNER TO postgres; 
    215  
    216 -- 
    217 -- TOC entry 1235 (class 1259 OID 1106501) 
    218 -- Dependencies: 1593 4 
    219 -- Name: project; Type: TABLE; Schema: public; Owner: postgres; Tablespace:  
    220 -- 
    221  
    222 CREATE TABLE project ( 
    223     id character varying(32) NOT NULL, 
    224     name text NOT NULL, 
    225     active boolean DEFAULT false NOT NULL 
     81CREATE TABLE project 
     82( 
     83  id varchar(32) NOT NULL, 
     84  name text NOT NULL, 
     85  active bool NOT NULL DEFAULT false 
    22686); 
    22787 
    228  
    229 ALTER TABLE public.project OWNER TO postgres; 
    230  
    231 -- 
    232 -- TOC entry 1241 (class 1259 OID 1115394) 
    233 -- Dependencies: 4 
    234 -- Name: project_watch; Type: TABLE; Schema: public; Owner: postgres; Tablespace:  
    235 -- 
    236  
    237 CREATE TABLE project_watch ( 
    238     id serial NOT NULL, 
    239     username text, 
    240     projectid character varying(32) 
     88CREATE TABLE project_watch 
     89( 
     90  id serial NOT NULL, 
     91  username text, 
     92  projectid varchar(32), 
     93  CONSTRAINT project_watch_pkey PRIMARY KEY (id) 
    24194); 
    24295 
    243  
    244 ALTER TABLE public.project_watch OWNER TO postgres; 
    245  
    246 -- 
    247 -- TOC entry 1230 (class 1259 OID 20697) 
    248 -- Dependencies: 1578 1579 1580 1581 4 
    249 -- Name: topic; Type: TABLE; Schema: public; Owner: postgres; Tablespace:  
    250 -- 
    251  
    252 CREATE TABLE topic ( 
    253     id integer DEFAULT nextval(('topic_id_seq'::text)::regclass) NOT NULL, 
    254     forumid integer, 
    255     subject text, 
    256     leadmessageid integer DEFAULT 0, 
    257     views integer DEFAULT 0, 
    258     "type" character varying(16) DEFAULT ''::character varying 
     96CREATE TABLE topic 
     97( 
     98  id serial NOT NULL, 
     99  forumid int4, 
     100  subject text, 
     101  leadmessageid int4 DEFAULT 0, 
     102  views int4 DEFAULT 0, 
     103  "type" varchar(16) DEFAULT '', 
     104  CONSTRAINT topic_pkey PRIMARY KEY (id) 
    259105); 
    260106 
    261  
    262 ALTER TABLE public.topic OWNER TO postgres; 
    263  
    264 -- 
    265 -- TOC entry 1224 (class 1259 OID 20671) 
    266 -- Dependencies: 4 
    267 -- Name: topic_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres 
    268 -- 
    269  
    270 CREATE SEQUENCE topic_id_seq 
    271     INCREMENT BY 1 
    272     NO MAXVALUE 
    273     NO MINVALUE 
    274     CACHE 1; 
    275  
    276  
    277 ALTER TABLE public.topic_id_seq OWNER TO postgres; 
    278  
    279 -- 
    280 -- TOC entry 1239 (class 1259 OID 1115386) 
    281 -- Dependencies: 4 
    282 -- Name: topic_watch; Type: TABLE; Schema: public; Owner: postgres; Tablespace:  
    283 -- 
    284  
    285 CREATE TABLE topic_watch ( 
    286     id serial NOT NULL, 
    287     topicid integer, 
    288     username text 
     107CREATE TABLE topic_watch 
     108( 
     109  id serial NOT NULL, 
     110  topicid int4, 
     111  username text, 
     112  CONSTRAINT topic_watch_pkey PRIMARY KEY (id) 
    289113); 
    290114 
    291  
    292 ALTER TABLE public.topic_watch OWNER TO postgres; 
    293  
    294 -- 
    295 -- TOC entry 1247 (class 1259 OID 1115554) 
    296 -- Dependencies: 4 
    297 -- Name: touched_topic; Type: TABLE; Schema: public; Owner: postgres; Tablespace:  
    298 -- 
    299  
    300 CREATE TABLE touched_topic ( 
    301     id serial NOT NULL, 
    302     username character varying(32), 
    303     topicid integer 
     115CREATE TABLE touched_topic 
     116( 
     117  id serial NOT NULL, 
     118  username varchar(32), 
     119  topicid int4, 
     120  CONSTRAINT touched_topic_pkey PRIMARY KEY (id) 
    304121); 
    305  
    306  
    307 ALTER TABLE public.touched_topic OWNER TO postgres; 
    308  
    309 -- 
    310 -- TOC entry 1222 (class 1259 OID 20364) 
    311 -- Dependencies: 4 
    312 -- Name: trac_cookies; Type: TABLE; Schema: public; Owner: postgres; Tablespace:  
    313 -- 
    314  
    315 CREATE TABLE trac_cookies ( 
    316     envname text NOT NULL, 
    317     cookie text NOT NULL, 
    318     username text NOT NULL, 
    319     ipnr text NOT NULL, 
    320     unixtime integer NOT NULL 
    321 ); 
    322  
    323  
    324 ALTER TABLE public.trac_cookies OWNER TO postgres; 
    325  
    326 -- 
    327 -- TOC entry 1223 (class 1259 OID 20369) 
    328 -- Dependencies: 4 
    329 -- Name: trac_permissions; Type: TABLE; Schema: public; Owner: postgres; Tablespace:  
    330 -- 
    331  
    332 CREATE TABLE trac_permissions ( 
    333     envname text NOT NULL, 
    334     username text NOT NULL, 
    335     groupname text NOT NULL 
    336 ); 
    337  
    338  
    339 ALTER TABLE public.trac_permissions OWNER TO postgres; 
    340  
    341 -- 
    342 -- TOC entry 1221 (class 1259 OID 20357) 
    343 -- Dependencies: 4 
    344 -- Name: trac_users; Type: TABLE; Schema: public; Owner: postgres; Tablespace:  
    345 -- 
    346  
    347 CREATE TABLE trac_users ( 
    348     envname text NOT NULL, 
    349     username text NOT NULL, 
    350     "password" text NOT NULL, 
    351     email text 
    352 ); 
    353  
    354  
    355 ALTER TABLE public.trac_users OWNER TO postgres; 
    356  
    357 -- 
    358 -- TOC entry 1603 (class 2606 OID 20688) 
    359 -- Dependencies: 1228 1228 1228 
    360 -- Name: forum_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:  
    361 -- 
    362  
    363 ALTER TABLE ONLY forum 
    364     ADD CONSTRAINT forum_id_key UNIQUE (id, name); 
    365  
    366  
    367 -- 
    368 -- TOC entry 1605 (class 2606 OID 20686) 
    369 -- Dependencies: 1228 1228 
    370 -- Name: forum_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:  
    371 -- 
    372  
    373 ALTER TABLE ONLY forum 
    374     ADD CONSTRAINT forum_pkey PRIMARY KEY (id); 
    375  
    376  
    377 -- 
    378 -- TOC entry 1608 (class 2606 OID 20696) 
    379 -- Dependencies: 1229 1229 
    380 -- Name: message_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:  
    381 -- 
    382  
    383 ALTER TABLE ONLY message 
    384     ADD CONSTRAINT message_pkey PRIMARY KEY (id); 
    385  
    386  
    387 -- 
    388 -- TOC entry 1610 (class 2606 OID 20704) 
    389 -- Dependencies: 1230 1230 
    390 -- Name: topic_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:  
    391 -- 
    392  
    393 ALTER TABLE ONLY topic 
    394     ADD CONSTRAINT topic_pkey PRIMARY KEY (id); 
    395  
    396  
    397 -- 
    398 -- TOC entry 1601 (class 2606 OID 20363) 
    399 -- Dependencies: 1221 1221 1221 
    400 -- Name: trac_users_envname_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:  
    401 -- 
    402  
    403 ALTER TABLE ONLY trac_users 
    404     ADD CONSTRAINT trac_users_envname_key UNIQUE (envname, username); 
    405  
    406  
    407 -- 
    408 -- TOC entry 1606 (class 1259 OID 1097439) 
    409 -- Dependencies: 1229 
    410 -- Name: message_modified_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:  
    411 -- 
    412  
    413 CREATE INDEX message_modified_idx ON message USING btree (modified); 
    414  
    415  
    416 -- 
    417 -- TOC entry 1613 (class 0 OID 0) 
    418 -- Dependencies: 4 
    419 -- Name: public; Type: ACL; Schema: -; Owner: postgres 
    420 -- 
    421  
    422 REVOKE ALL ON SCHEMA public FROM PUBLIC; 
    423 REVOKE ALL ON SCHEMA public FROM postgres; 
    424 GRANT ALL ON SCHEMA public TO postgres; 
    425 GRANT ALL ON SCHEMA public TO PUBLIC; 
    426  
    427  
    428 -- Completed on 2007-09-16 23:41:08 Eastern Daylight Time 
    429  
    430 -- 
    431 -- PostgreSQL database dump complete 
    432 -- 
    433  

  • trunk/tracforums/models/profile.py

    r105 r110  
    1111    tablename = Tablenames.profile, 
    1212    columns = { 
    13         "id": ORMKey(type="int", auto_increment = True), #TODO: drop this column 
    1413        "sig": ORMColumn(), 
    1514        "username": ORMColumn(), 

  • trunk/tracforums/templates/tracforums/notify/projectchanged.cs

    r78 r110  
     1Project Changed 

  • trunk/tracforums/templates/tracforums/notify/topicchanged.cs

    r78 r110  
     1Topic Changed