Changeset 5105 in subversion


Ignore:
Timestamp:
Aug 22, 2011 6:30:46 AM (21 months ago)
Author:
alec
Message:
  • Update DDL (with cache_* tables) for all DB engines
Location:
branches/devel-mcache/roundcubemail/SQL
Files:
6 edited

Legend:

Unmodified
Added
Removed

  • branches/devel-mcache/roundcubemail/SQL/mssql.initial.sql

    r4567 r5105  
    55        [created] [datetime] NOT NULL , 
    66        [data] [text] COLLATE Latin1_General_CI_AI NOT NULL  
     7) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
     8GO 
     9 
     10CREATE TABLE [dbo].[cache_index] ( 
     11        [user_id] [int] NOT NULL , 
     12        [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , 
     13        [sort_field] [varchar] (8) COLLATE Latin1_General_CI_AI NOT NULL , 
     14        [changed] [datetime] NOT NULL , 
     15        [data] [text] COLLATE Latin1_General_CI_AI NOT NULL  
     16) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
     17GO 
     18 
     19CREATE TABLE [dbo].[cache_thread] ( 
     20        [user_id] [int] NOT NULL , 
     21        [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , 
     22        [changed] [datetime] NOT NULL , 
     23        [data] [text] COLLATE Latin1_General_CI_AI NOT NULL  
     24) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
     25GO 
     26 
     27CREATE TABLE [dbo].[cache_messages] ( 
     28        [user_id] [int] NOT NULL , 
     29        [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , 
     30        [uid] [int] NOT NULL , 
     31        [changed] [datetime] NOT NULL , 
     32        [data] [text] COLLATE Latin1_General_CI_AI NOT NULL  
     33        [seen] [char](1) NOT NULL , 
     34        [deleted] [char](1) NOT NULL , 
     35        [answered] [char](1) NOT NULL , 
     36        [forwarded] [char](1) NOT NULL , 
     37        [flagged] [char](1) NOT NULL , 
     38        [mdnsent] [char](1) NOT NULL , 
    739) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
    840GO 
    … …  
    5486GO 
    5587 
    56 CREATE TABLE [dbo].[messages] ( 
    57         [message_id] [int] IDENTITY (1, 1) NOT NULL , 
    58         [user_id] [int] NOT NULL , 
    59         [del] [tinyint] NOT NULL , 
    60         [cache_key] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , 
    61         [created] [datetime] NOT NULL , 
    62         [idx] [int] NOT NULL , 
    63         [uid] [int] NOT NULL , 
    64         [subject] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL , 
    65         [from] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL , 
    66         [to] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL , 
    67         [cc] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL , 
    68         [date] [datetime] NOT NULL , 
    69         [size] [int] NOT NULL , 
    70         [headers] [text] COLLATE Latin1_General_CI_AI NOT NULL , 
    71         [structure] [text] COLLATE Latin1_General_CI_AI NULL  
    72 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
    73 GO 
    74  
    7588CREATE TABLE [dbo].[session] ( 
    7689        [sess_id] [varchar] (32) COLLATE Latin1_General_CI_AI NOT NULL , 
    … …  
    101114GO 
    102115 
     116ALTER TABLE [dbo].[cache_index] WITH NOCHECK ADD  
     117         PRIMARY KEY CLUSTERED  
     118        ( 
     119                [user_id],[mailbox],[sort_field] 
     120        ) ON [PRIMARY]  
     121GO 
     122 
     123ALTER TABLE [dbo].[cache_thread] WITH NOCHECK ADD  
     124         PRIMARY KEY CLUSTERED  
     125        ( 
     126                [user_id],[mailbox] 
     127        ) ON [PRIMARY]  
     128GO 
     129 
     130ALTER TABLE [dbo].[cache_messages] WITH NOCHECK ADD  
     131         PRIMARY KEY CLUSTERED  
     132        ( 
     133                [user_id],[mailbox],[uid] 
     134        ) ON [PRIMARY]  
     135GO 
     136 
    103137ALTER TABLE [dbo].[contacts] WITH NOCHECK ADD  
    104138        CONSTRAINT [PK_contacts_contact_id] PRIMARY KEY  CLUSTERED  
    … …  
    126160        ( 
    127161                [identity_id] 
    128         )  ON [PRIMARY]  
    129 GO 
    130  
    131 ALTER TABLE [dbo].[messages] WITH NOCHECK ADD  
    132          PRIMARY KEY  CLUSTERED  
    133         ( 
    134                 [message_id] 
    135162        )  ON [PRIMARY]  
    136163GO 
    … …  
    163190 
    164191CREATE  INDEX [IX_cache_created] ON [dbo].[cache]([created]) ON [PRIMARY] 
     192GO 
     193 
     194ALTER TABLE [dbo].[cache_index] ADD  
     195        CONSTRAINT [DF_cache_index_changed] DEFAULT (getdate()) FOR [changed] 
     196GO 
     197 
     198CREATE  INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY] 
     199GO 
     200 
     201ALTER TABLE [dbo].[cache_thread] ADD  
     202        CONSTRAINT [DF_cache_thread_changed] DEFAULT (getdate()) FOR [changed] 
     203GO 
     204 
     205CREATE  INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY] 
     206GO 
     207 
     208ALTER TABLE [dbo].[cache_messages] ADD  
     209        CONSTRAINT [DF_cache_messages_changed] DEFAULT (getdate()) FOR [changed] 
     210        CONSTRAINT [DF_cache_messages_seen] DEFAULT (0) FOR [seen], 
     211        CONSTRAINT [DF_cache_messages_deleted] DEFAULT (0) FOR [deleted], 
     212        CONSTRAINT [DF_cache_messages_answered] DEFAULT (0) FOR [answered], 
     213        CONSTRAINT [DF_cache_messages_forwarded] DEFAULT (0) FOR [forwarded], 
     214        CONSTRAINT [DF_cache_messages_flagged] DEFAULT (0) FOR [flagged], 
     215        CONSTRAINT [DF_cache_messages_mdnsent] DEFAULT (0) FOR [mdnsent], 
     216GO 
     217 
     218CREATE  INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY] 
    165219GO 
    166220 
    … …  
    216270GO 
    217271 
    218 ALTER TABLE [dbo].[messages] ADD  
    219         CONSTRAINT [DF_messages_user_id] DEFAULT (0) FOR [user_id], 
    220         CONSTRAINT [DF_messages_del] DEFAULT (0) FOR [del], 
    221         CONSTRAINT [DF_messages_cache_key] DEFAULT ('') FOR [cache_key], 
    222         CONSTRAINT [DF_messages_created] DEFAULT (getdate()) FOR [created], 
    223         CONSTRAINT [DF_messages_idx] DEFAULT (0) FOR [idx], 
    224         CONSTRAINT [DF_messages_uid] DEFAULT (0) FOR [uid], 
    225         CONSTRAINT [DF_messages_subject] DEFAULT ('') FOR [subject], 
    226         CONSTRAINT [DF_messages_from] DEFAULT ('') FOR [from], 
    227         CONSTRAINT [DF_messages_to] DEFAULT ('') FOR [to], 
    228         CONSTRAINT [DF_messages_cc] DEFAULT ('') FOR [cc], 
    229         CONSTRAINT [DF_messages_date] DEFAULT (getdate()) FOR [date], 
    230         CONSTRAINT [DF_messages_size] DEFAULT (0) FOR [size] 
    231 GO 
    232  
    233 CREATE  INDEX [IX_messages_user_id] ON [dbo].[messages]([user_id]) ON [PRIMARY] 
    234 GO 
    235  
    236 CREATE  INDEX [IX_messages_cache_key] ON [dbo].[messages]([cache_key]) ON [PRIMARY] 
    237 GO 
    238  
    239 CREATE  INDEX [IX_messages_uid] ON [dbo].[messages]([uid]) ON [PRIMARY] 
    240 GO 
    241  
    242 CREATE  INDEX [IX_messages_created] ON [dbo].[messages]([created]) ON [PRIMARY] 
    243 GO 
    244  
    245272ALTER TABLE [dbo].[session] ADD  
    246273        CONSTRAINT [DF_session_sess_id] DEFAULT ('') FOR [sess_id], 
    … …  
    285312GO 
    286313 
    287 ALTER TABLE [dbo].[messages] ADD CONSTRAINT [FK_messages_user_id] 
     314ALTER TABLE [dbo].[cache_index] ADD CONSTRAINT [FK_cache_index_user_id] 
     315    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id]) 
     316    ON DELETE CASCADE ON UPDATE CASCADE 
     317GO 
     318 
     319ALTER TABLE [dbo].[cache_thread] ADD CONSTRAINT [FK_cache_thread_user_id] 
     320    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id]) 
     321    ON DELETE CASCADE ON UPDATE CASCADE 
     322GO 
     323 
     324ALTER TABLE [dbo].[cache_messages] ADD CONSTRAINT [FK_cache_messages_user_id] 
    288325    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id]) 
    289326    ON DELETE CASCADE ON UPDATE CASCADE 

  • branches/devel-mcache/roundcubemail/SQL/mssql.upgrade.sql

    r4733 r5105  
    108108DELETE FROM [dbo].[cache] 
    109109GO 
     110 
     111 
     112DROP TABLE [dbo].[messages] 
     113GO 
     114CREATE TABLE [dbo].[cache_index] ( 
     115        [user_id] [int] NOT NULL , 
     116        [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , 
     117        [sort_field] [varchar] (8) COLLATE Latin1_General_CI_AI NOT NULL , 
     118        [changed] [datetime] NOT NULL , 
     119        [data] [text] COLLATE Latin1_General_CI_AI NOT NULL  
     120) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
     121GO 
     122 
     123CREATE TABLE [dbo].[cache_thread] ( 
     124        [user_id] [int] NOT NULL , 
     125        [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , 
     126        [changed] [datetime] NOT NULL , 
     127        [data] [text] COLLATE Latin1_General_CI_AI NOT NULL  
     128) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
     129GO 
     130 
     131CREATE TABLE [dbo].[cache_messages] ( 
     132        [user_id] [int] NOT NULL , 
     133        [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , 
     134        [uid] [int] NOT NULL , 
     135        [changed] [datetime] NOT NULL , 
     136        [data] [text] COLLATE Latin1_General_CI_AI NOT NULL  
     137        [seen] [char](1) NOT NULL , 
     138        [deleted] [char](1) NOT NULL , 
     139        [answered] [char](1) NOT NULL , 
     140        [forwarded] [char](1) NOT NULL , 
     141        [flagged] [char](1) NOT NULL , 
     142        [mdnsent] [char](1) NOT NULL , 
     143) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
     144GO 
     145 
     146ALTER TABLE [dbo].[cache_index] WITH NOCHECK ADD  
     147         PRIMARY KEY CLUSTERED  
     148        ( 
     149                [user_id],[mailbox],[sort_field] 
     150        ) ON [PRIMARY]  
     151GO 
     152 
     153ALTER TABLE [dbo].[cache_thread] WITH NOCHECK ADD  
     154         PRIMARY KEY CLUSTERED  
     155        ( 
     156                [user_id],[mailbox] 
     157        ) ON [PRIMARY]  
     158GO 
     159 
     160ALTER TABLE [dbo].[cache_messages] WITH NOCHECK ADD  
     161         PRIMARY KEY CLUSTERED  
     162        ( 
     163                [user_id],[mailbox],[uid] 
     164        ) ON [PRIMARY]  
     165GO 
     166 
     167ALTER TABLE [dbo].[cache_index] ADD  
     168        CONSTRAINT [DF_cache_index_changed] DEFAULT (getdate()) FOR [changed] 
     169GO 
     170 
     171CREATE  INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY] 
     172GO 
     173 
     174ALTER TABLE [dbo].[cache_thread] ADD  
     175        CONSTRAINT [DF_cache_thread_changed] DEFAULT (getdate()) FOR [changed] 
     176GO 
     177 
     178CREATE  INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY] 
     179GO 
     180 
     181ALTER TABLE [dbo].[cache_messages] ADD  
     182        CONSTRAINT [DF_cache_messages_changed] DEFAULT (getdate()) FOR [changed] 
     183        CONSTRAINT [DF_cache_messages_seen] DEFAULT (0) FOR [seen], 
     184        CONSTRAINT [DF_cache_messages_deleted] DEFAULT (0) FOR [deleted], 
     185        CONSTRAINT [DF_cache_messages_answered] DEFAULT (0) FOR [answered], 
     186        CONSTRAINT [DF_cache_messages_forwarded] DEFAULT (0) FOR [forwarded], 
     187        CONSTRAINT [DF_cache_messages_flagged] DEFAULT (0) FOR [flagged], 
     188        CONSTRAINT [DF_cache_messages_mdnsent] DEFAULT (0) FOR [mdnsent], 
     189GO 
     190 
     191CREATE  INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY] 
     192GO 
     193 
     194ALTER TABLE [dbo].[cache_index] ADD CONSTRAINT [FK_cache_index_user_id] 
     195    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id]) 
     196    ON DELETE CASCADE ON UPDATE CASCADE 
     197GO 
     198 
     199ALTER TABLE [dbo].[cache_thread] ADD CONSTRAINT [FK_cache_thread_user_id] 
     200    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id]) 
     201    ON DELETE CASCADE ON UPDATE CASCADE 
     202GO 
     203 
     204ALTER TABLE [dbo].[cache_messages] ADD CONSTRAINT [FK_cache_messages_user_id] 
     205    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id]) 
     206    ON DELETE CASCADE ON UPDATE CASCADE 
     207GO 

  • branches/devel-mcache/roundcubemail/SQL/postgres.initial.sql

    r4603 r5105  
    6868    identity_id integer DEFAULT nextval('identity_ids'::text) PRIMARY KEY, 
    6969    user_id integer NOT NULL 
    70         REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     70        REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
    7171    changed timestamp with time zone DEFAULT now() NOT NULL, 
    7272    del smallint DEFAULT 0 NOT NULL, 
    … …  
    179179    cache_id integer DEFAULT nextval('cache_ids'::text) PRIMARY KEY, 
    180180    user_id integer NOT NULL 
    181         REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     181        REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
    182182    cache_key varchar(128) DEFAULT '' NOT NULL, 
    183183    created timestamp with time zone DEFAULT now() NOT NULL, 
    … …  
    189189 
    190190-- 
    191 -- Sequence "message_ids" 
    192 -- Name: message_ids; Type: SEQUENCE; Schema: public; Owner: postgres 
    193 -- 
    194  
    195 CREATE SEQUENCE message_ids 
    196     INCREMENT BY 1 
    197     NO MAXVALUE 
    198     NO MINVALUE 
    199     CACHE 1; 
    200  
    201 -- 
    202 -- Table "messages" 
    203 -- Name: messages; Type: TABLE; Schema: public; Owner: postgres 
    204 -- 
    205  
    206 CREATE TABLE messages ( 
    207     message_id integer DEFAULT nextval('message_ids'::text) PRIMARY KEY, 
    208     user_id integer NOT NULL 
    209         REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
    210     del smallint DEFAULT 0 NOT NULL, 
    211     cache_key varchar(128) DEFAULT '' NOT NULL, 
    212     created timestamp with time zone DEFAULT now() NOT NULL, 
    213     idx integer DEFAULT 0 NOT NULL, 
    214     uid integer DEFAULT 0 NOT NULL, 
    215     subject varchar(128) DEFAULT '' NOT NULL, 
    216     "from" varchar(128) DEFAULT '' NOT NULL, 
    217     "to" varchar(128) DEFAULT '' NOT NULL, 
    218     cc varchar(128) DEFAULT '' NOT NULL, 
    219     date timestamp with time zone NOT NULL, 
    220     size integer DEFAULT 0 NOT NULL, 
    221     headers text NOT NULL, 
    222     structure text, 
    223     CONSTRAINT messages_user_id_key UNIQUE (user_id, cache_key, uid) 
    224 ); 
    225  
    226 CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx); 
    227 CREATE INDEX messages_created_idx ON messages (created); 
     191-- Table "cache_index" 
     192-- Name: cache_index; Type: TABLE; Schema: public; Owner: postgres 
     193-- 
     194 
     195CREATE TABLE cache_index ( 
     196    user_id integer NOT NULL 
     197        REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     198    mailbox varchar(255) NOT NULL, 
     199    sort_field varchar(8) NOT NULL, 
     200    changed timestamp with time zone DEFAULT now() NOT NULL, 
     201    data text NOT NULL, 
     202    PRIMARY KEY (user_id, mailbox, sort_field) 
     203); 
     204 
     205CREATE INDEX cache_index_changed_idx ON cache_index (changed); 
     206 
     207-- 
     208-- Table "cache_thread" 
     209-- Name: cache_thread; Type: TABLE; Schema: public; Owner: postgres 
     210-- 
     211 
     212CREATE TABLE cache_thread ( 
     213    user_id integer NOT NULL 
     214        REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     215    mailbox varchar(255) NOT NULL, 
     216    changed timestamp with time zone DEFAULT now() NOT NULL, 
     217    data text NOT NULL, 
     218    PRIMARY KEY (user_id, mailbox) 
     219); 
     220 
     221CREATE INDEX cache_thread_changed_idx ON cache_thread (changed); 
     222 
     223-- 
     224-- Table "cache_messages" 
     225-- Name: cache_messages; Type: TABLE; Schema: public; Owner: postgres 
     226-- 
     227 
     228CREATE TABLE cache_messages ( 
     229    user_id integer NOT NULL 
     230        REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     231    mailbox varchar(255) NOT NULL, 
     232    uid integer NOT NULL, 
     233    changed timestamp with time zone DEFAULT now() NOT NULL, 
     234    data text NOT NULL, 
     235    seen smallint NOT NULL DEFAULT 0, 
     236    deleted smallint NOT NULL DEFAULT 0, 
     237    answered smallint NOT NULL DEFAULT 0, 
     238    forwarded smallint NOT NULL DEFAULT 0, 
     239    flagged smallint NOT NULL DEFAULT 0, 
     240    mdnsent smallint NOT NULL DEFAULT 0, 
     241    PRIMARY KEY (user_id, mailbox, uid) 
     242); 
     243 
     244CREATE INDEX cache_messages_changed_idx ON cache_messages (changed); 

  • branches/devel-mcache/roundcubemail/SQL/postgres.update.sql

    r4733 r5105  
    9898TRUNCATE messages; 
    9999TRUNCATE cache; 
     100 
     101DROP SEQUENCE messages_ids; 
     102DROP TABLE messages; 
     103 
     104CREATE TABLE cache_index ( 
     105    user_id integer NOT NULL 
     106        REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     107    mailbox varchar(255) NOT NULL, 
     108    sort_field varchar(8) NOT NULL, 
     109    changed timestamp with time zone DEFAULT now() NOT NULL, 
     110    data text NOT NULL, 
     111    PRIMARY KEY (user_id, mailbox, sort_field) 
     112); 
     113 
     114CREATE INDEX cache_index_changed_idx ON cache_index (changed); 
     115 
     116CREATE TABLE cache_thread ( 
     117    user_id integer NOT NULL 
     118        REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     119    mailbox varchar(255) NOT NULL, 
     120    changed timestamp with time zone DEFAULT now() NOT NULL, 
     121    data text NOT NULL, 
     122    PRIMARY KEY (user_id, mailbox) 
     123); 
     124 
     125CREATE INDEX cache_thread_changed_idx ON cache_thread (changed); 
     126 
     127CREATE TABLE cache_messages ( 
     128    user_id integer NOT NULL 
     129        REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     130    mailbox varchar(255) NOT NULL, 
     131    uid integer NOT NULL, 
     132    changed timestamp with time zone DEFAULT now() NOT NULL, 
     133    data text NOT NULL, 
     134    seen smallint NOT NULL DEFAULT 0, 
     135    deleted smallint NOT NULL DEFAULT 0, 
     136    answered smallint NOT NULL DEFAULT 0, 
     137    forwarded smallint NOT NULL DEFAULT 0, 
     138    flagged smallint NOT NULL DEFAULT 0, 
     139    mdnsent smallint NOT NULL DEFAULT 0, 
     140    PRIMARY KEY (user_id, mailbox, uid) 
     141); 
     142 
     143CREATE INDEX cache_messages_changed_idx ON cache_messages (changed); 

  • branches/devel-mcache/roundcubemail/SQL/sqlite.initial.sql

    r4567 r5105  
    22 
    33--  
    4 -- Table structure for table `cache` 
     4-- Table structure for table cache 
    55--  
    66 
    … …  
    1010  cache_key varchar(128) NOT NULL default '', 
    1111  created datetime NOT NULL default '0000-00-00 00:00:00', 
    12   data longtext NOT NULL 
     12  data text NOT NULL 
    1313); 
    1414 
    … …  
    122122-- -------------------------------------------------------- 
    123123 
    124 --  
    125 -- Table structure for table messages 
    126 --  
     124-- 
     125-- Table structure for table cache_index 
     126-- 
    127127 
    128 CREATE TABLE messages ( 
    129   message_id integer NOT NULL PRIMARY KEY, 
    130   user_id integer NOT NULL default '0', 
    131   del tinyint NOT NULL default '0', 
    132   cache_key varchar(128) NOT NULL default '', 
    133   created datetime NOT NULL default '0000-00-00 00:00:00', 
    134   idx integer NOT NULL default '0', 
    135   uid integer NOT NULL default '0', 
    136   subject varchar(255) NOT NULL default '', 
    137   "from" varchar(255) NOT NULL default '', 
    138   "to" varchar(255) NOT NULL default '', 
    139   "cc" varchar(255) NOT NULL default '', 
    140   "date" datetime NOT NULL default '0000-00-00 00:00:00', 
    141   size integer NOT NULL default '0', 
    142   headers text NOT NULL, 
    143   structure text 
     128CREATE TABLE cache_index ( 
     129    user_id integer NOT NULL, 
     130    mailbox varchar(255) NOT NULL, 
     131    sort_field varchar(8) NOT NULL, 
     132    changed datetime NOT NULL default '0000-00-00 00:00:00', 
     133    data text NOT NULL, 
     134    PRIMARY KEY (user_id, mailbox, sort_field) 
    144135); 
    145136 
    146 CREATE UNIQUE INDEX ix_messages_user_cache_uid ON messages (user_id,cache_key,uid); 
    147 CREATE INDEX ix_messages_index ON messages (user_id,cache_key,idx); 
    148 CREATE INDEX ix_messages_created ON messages (created); 
     137CREATE INDEX ix_cache_index_changed ON cache_index (changed); 
     138 
     139-- -------------------------------------------------------- 
     140 
     141-- 
     142-- Table structure for table cache_thread 
     143-- 
     144 
     145CREATE TABLE cache_thread ( 
     146    user_id integer NOT NULL, 
     147    mailbox varchar(255) NOT NULL, 
     148    changed datetime NOT NULL default '0000-00-00 00:00:00', 
     149    data text NOT NULL, 
     150    PRIMARY KEY (user_id, mailbox) 
     151); 
     152 
     153CREATE INDEX ix_cache_thread_changed ON cache_thread (changed); 
     154 
     155-- -------------------------------------------------------- 
     156 
     157-- 
     158-- Table structure for table cache_messages 
     159-- 
     160 
     161CREATE TABLE cache_messages ( 
     162    user_id integer NOT NULL, 
     163    mailbox varchar(255) NOT NULL, 
     164    uid integer NOT NULL, 
     165    changed datetime NOT NULL default '0000-00-00 00:00:00', 
     166    data text NOT NULL, 
     167    seen smallint NOT NULL DEFAULT '0', 
     168    deleted smallint NOT NULL DEFAULT '0', 
     169    answered smallint NOT NULL DEFAULT '0', 
     170    forwarded smallint NOT NULL DEFAULT '0', 
     171    flagged smallint NOT NULL DEFAULT '0', 
     172    mdnsent smallint NOT NULL DEFAULT '0', 
     173    PRIMARY KEY (user_id, mailbox, uid) 
     174); 
     175 
     176CREATE INDEX ix_cache_messages_changed ON cache_messages (changed); 

  • branches/devel-mcache/roundcubemail/SQL/sqlite.update.sql

    r4733 r5105  
    221221DROP TABLE contacts_tmp; 
    222222 
     223 
    223224DELETE FROM messages; 
    224225DELETE FROM cache; 
    225226CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id); 
     227 
     228DROP TABLE messages; 
     229 
     230CREATE TABLE cache_index ( 
     231    user_id integer NOT NULL, 
     232    mailbox varchar(255) NOT NULL, 
     233    sort_field varchar(8) NOT NULL, 
     234    changed datetime NOT NULL default '0000-00-00 00:00:00', 
     235    data text NOT NULL, 
     236    PRIMARY KEY (user_id, mailbox, sort_field) 
     237); 
     238 
     239CREATE INDEX ix_cache_index_changed ON cache_index (changed); 
     240 
     241CREATE TABLE cache_thread ( 
     242    user_id integer NOT NULL, 
     243    mailbox varchar(255) NOT NULL, 
     244    changed datetime NOT NULL default '0000-00-00 00:00:00', 
     245    data text NOT NULL, 
     246    PRIMARY KEY (user_id, mailbox) 
     247); 
     248 
     249CREATE INDEX ix_cache_thread_changed ON cache_thread (changed); 
     250 
     251CREATE TABLE cache_messages ( 
     252    user_id integer NOT NULL, 
     253    mailbox varchar(255) NOT NULL, 
     254    uid integer NOT NULL, 
     255    changed datetime NOT NULL default '0000-00-00 00:00:00', 
     256    data text NOT NULL, 
     257    seen smallint NOT NULL DEFAULT '0', 
     258    deleted smallint NOT NULL DEFAULT '0', 
     259    answered smallint NOT NULL DEFAULT '0', 
     260    forwarded smallint NOT NULL DEFAULT '0', 
     261    flagged smallint NOT NULL DEFAULT '0', 
     262    mdnsent smallint NOT NULL DEFAULT '0', 
     263    PRIMARY KEY (user_id, mailbox, uid) 
     264); 
     265 
     266CREATE INDEX ix_cache_messages_changed ON cache_messages (changed); 
Note: See TracChangeset for help on using the changeset viewer.