Changeset 3431 in subversion


Ignore:
Timestamp:
Mar 27, 2010 3:40:43 AM (3 years ago)
Author:
alec
Message:
  • contactgroups DDL for postgres
Location:
trunk/roundcubemail
Files:
3 edited

Legend:

Unmodified
Added
Removed

  • trunk/roundcubemail/SQL/postgres.initial.sql

    r3333 r3431  
    1919CREATE TABLE users ( 
    2020    user_id integer DEFAULT nextval('user_ids'::text) PRIMARY KEY, 
    21     username character varying(128) DEFAULT ''::character varying NOT NULL, 
    22     mail_host character varying(128) DEFAULT ''::character varying NOT NULL, 
    23     alias character varying(128) DEFAULT ''::character varying NOT NULL, 
     21    username varchar(128) DEFAULT '' NOT NULL, 
     22    mail_host varchar(128) DEFAULT '' NOT NULL, 
     23    alias varchar(128) DEFAULT '' NOT NULL, 
    2424    created timestamp with time zone DEFAULT now() NOT NULL, 
    2525    last_login timestamp with time zone DEFAULT now() NOT NULL, 
    26     "language" character varying(5), 
     26    "language" varchar(5), 
    2727    preferences text DEFAULT ''::text NOT NULL 
    2828); 
    … …  
    3838 
    3939CREATE TABLE "session" ( 
    40     sess_id character varying(40) DEFAULT ''::character varying PRIMARY KEY, 
     40    sess_id varchar(40) DEFAULT '' PRIMARY KEY, 
    4141    created timestamp with time zone DEFAULT now() NOT NULL, 
    4242    changed timestamp with time zone DEFAULT now() NOT NULL, 
    43     ip character varying(41) NOT NULL, 
     43    ip varchar(41) NOT NULL, 
    4444    vars text NOT NULL 
    4545); 
    … …  
    6767CREATE TABLE identities ( 
    6868    identity_id integer DEFAULT nextval('identity_ids'::text) PRIMARY KEY, 
    69     user_id integer NOT NULL REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     69    user_id integer NOT NULL 
     70        REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
    7071    del smallint DEFAULT 0 NOT NULL, 
    7172    standard smallint DEFAULT 0 NOT NULL, 
    72     name character varying(128) NOT NULL, 
    73     organization character varying(128), 
    74     email character varying(128) NOT NULL, 
    75     "reply-to" character varying(128), 
    76     bcc character varying(128), 
     73    name varchar(128) NOT NULL, 
     74    organization varchar(128), 
     75    email varchar(128) NOT NULL, 
     76    "reply-to" varchar(128), 
     77    bcc varchar(128), 
    7778    signature text, 
    7879    html_signature integer DEFAULT 0 NOT NULL 
    … …  
    101102CREATE TABLE contacts ( 
    102103    contact_id integer DEFAULT nextval('contact_ids'::text) PRIMARY KEY, 
    103     user_id integer NOT NULL REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     104    user_id integer NOT NULL 
     105        REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
    104106    changed timestamp with time zone DEFAULT now() NOT NULL, 
    105107    del smallint DEFAULT 0 NOT NULL, 
    106     name character varying(128) DEFAULT ''::character varying NOT NULL, 
    107     email character varying(128) DEFAULT ''::character varying NOT NULL, 
    108     firstname character varying(128) DEFAULT ''::character varying NOT NULL, 
    109     surname character varying(128) DEFAULT ''::character varying NOT NULL, 
     108    name varchar(128) DEFAULT '' NOT NULL, 
     109    email varchar(128) DEFAULT '' NOT NULL, 
     110    firstname varchar(128) DEFAULT '' NOT NULL, 
     111    surname varchar(128) DEFAULT '' NOT NULL, 
    110112    vcard text 
    111113); 
    112114 
    113115CREATE INDEX contacts_user_id_idx ON contacts (user_id, email); 
     116 
     117-- 
     118-- Sequence "contactgroups_ids" 
     119-- Name: contactgroups_ids; Type: SEQUENCE; Schema: public; Owner: postgres 
     120-- 
     121 
     122CREATE SEQUENCE contactgroups_ids 
     123    INCREMENT BY 1 
     124    NO MAXVALUE 
     125    NO MINVALUE 
     126    CACHE 1; 
     127 
     128-- 
     129-- Table "contactgroups" 
     130-- Name: contactgroups; Type: TABLE; Schema: public; Owner: postgres 
     131-- 
     132 
     133CREATE TABLE contactgroups ( 
     134    contactgroup_id integer DEFAULT nextval('contactgroups_ids'::text) PRIMARY KEY, 
     135    user_id integer NOT NULL 
     136        REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     137    changed timestamp with time zone DEFAULT now() NOT NULL, 
     138    del smallint NOT NULL DEFAULT 0, 
     139    name varchar(128) NOT NULL DEFAULT '' 
     140); 
     141 
     142CREATE INDEX contactgroups_user_id_idx ON contactgroups (user_id, del); 
     143 
     144-- 
     145-- Table "contactgroupmembers" 
     146-- Name: contactgroupmembers; Type: TABLE; Schema: public; Owner: postgres 
     147-- 
     148                                             
     149CREATE TABLE contactgroupmembers ( 
     150    contactgroup_id integer NOT NULL 
     151        REFERENCES contactgroups(contactgroup_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     152    contact_id integer NOT NULL 
     153        REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     154    created timestamp with time zone DEFAULT now() NOT NULL, 
     155    PRIMARY KEY (contactgroup_id, contact_id) 
     156); 
    114157 
    115158-- 
    … …  
    131174CREATE TABLE "cache" ( 
    132175    cache_id integer DEFAULT nextval('cache_ids'::text) PRIMARY KEY, 
    133     user_id integer NOT NULL REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
    134     cache_key character varying(128) DEFAULT ''::character varying NOT NULL, 
     176    user_id integer NOT NULL 
     177        REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     178    cache_key varchar(128) DEFAULT '' NOT NULL, 
    135179    created timestamp with time zone DEFAULT now() NOT NULL, 
    136180    data text NOT NULL 
    … …  
    158202CREATE TABLE messages ( 
    159203    message_id integer DEFAULT nextval('message_ids'::text) PRIMARY KEY, 
    160     user_id integer NOT NULL REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     204    user_id integer NOT NULL 
     205        REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
    161206    del smallint DEFAULT 0 NOT NULL, 
    162     cache_key character varying(128) DEFAULT ''::character varying NOT NULL, 
     207    cache_key varchar(128) DEFAULT '' NOT NULL, 
    163208    created timestamp with time zone DEFAULT now() NOT NULL, 
    164209    idx integer DEFAULT 0 NOT NULL, 
    165210    uid integer DEFAULT 0 NOT NULL, 
    166     subject character varying(128) DEFAULT ''::character varying NOT NULL, 
    167     "from" character varying(128) DEFAULT ''::character varying NOT NULL, 
    168     "to" character varying(128) DEFAULT ''::character varying NOT NULL, 
    169     cc character varying(128) DEFAULT ''::character varying NOT NULL, 
     211    subject varchar(128) DEFAULT '' NOT NULL, 
     212    "from" varchar(128) DEFAULT '' NOT NULL, 
     213    "to" varchar(128) DEFAULT '' NOT NULL, 
     214    cc varchar(128) DEFAULT '' NOT NULL, 
    170215    date timestamp with time zone NOT NULL, 
    171216    size integer DEFAULT 0 NOT NULL, 

  • trunk/roundcubemail/SQL/postgres.update.sql

    r3333 r3431  
    4949DROP INDEX identities_user_id_idx; 
    5050CREATE INDEX identities_user_id_idx ON identities (user_id, del); 
     51 
     52CREATE SEQUENCE contactgroups_ids 
     53    INCREMENT BY 1 
     54    NO MAXVALUE 
     55    NO MINVALUE 
     56    CACHE 1; 
     57                 
     58CREATE TABLE contactgroups ( 
     59    contactgroup_id integer DEFAULT nextval('contactgroups_ids'::text) PRIMARY KEY, 
     60    user_id     integer         NOT NULL 
     61        REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     62    changed     timestamp with time zone DEFAULT now() NOT NULL, 
     63    del         smallint        NOT NULL DEFAULT 0, 
     64    name        varchar(128)    NOT NULL DEFAULT '' 
     65); 
     66 
     67CREATE INDEX contactgroups_user_id_idx ON contactgroups (user_id, del); 
     68 
     69CREATE TABLE contactgroupmembers ( 
     70    contactgroup_id     integer NOT NULL 
     71        REFERENCES contactgroups(contactgroup_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     72    contact_id          integer NOT NULL 
     73        REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     74    created timestamp with time zone DEFAULT now() NOT NULL, 
     75    PRIMARY KEY (contactgroup_id, contact_id) 
     76); 

  • trunk/roundcubemail/config/db.inc.php.dist

    r3425 r3431  
    6363$rcmail_config['db_sequence_contacts'] = 'contact_ids'; 
    6464 
     65$rcmail_config['db_sequence_contactgroups'] = 'contactgroups_ids'; 
     66 
    6567$rcmail_config['db_sequence_cache'] = 'cache_ids'; 
    6668 
Note: See TracChangeset for help on using the changeset viewer.