1-- Copyright (C) 2005 Internet Connection, Inc. 2-- Copyright (C) 2006-2014 NFG Net Facilities Group BV. 3-- 4-- This program is free software; you can redistribute it and/or 5-- modify it under the terms of the GNU General Public License 6-- as published by the Free Software Foundation; either 7-- version 2 of the License, or (at your option) any later 8-- version. 9-- 10-- This program is distributed in the hope that it will be useful, 11-- but WITHOUT ANY WARRANTY; without even the implied warranty of 12-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 13-- GNU General Public License for more details. 14-- 15-- You should have received a copy of the GNU General Public License 16-- along with this program; if not, write to the Free Software 17-- Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. 18-- 19 20BEGIN TRANSACTION; 21 22PRAGMA auto_vacuum = 1; 23 24CREATE TABLE dbmail_aliases ( 25 alias_idnr INTEGER PRIMARY KEY, 26 alias TEXT NOT NULL, 27 deliver_to TEXT NOT NULL, 28 client_idnr INTEGER DEFAULT '0' NOT NULL 29); 30CREATE INDEX dbmail_aliases_index_1 ON dbmail_aliases(alias); 31CREATE INDEX dbmail_aliases_index_2 ON dbmail_aliases(client_idnr); 32 33CREATE TABLE dbmail_authlog ( 34 id INTEGER PRIMARY KEY, 35 userid TEXT, 36 service TEXT, 37 login_time DATETIME, 38 logout_time DATETIME, 39 src_ip TEXT, 40 src_port INTEGER, 41 dst_ip TEXT, 42 dst_port INTEGER, 43 status TEXT DEFAULT 'active', 44 bytes_rx INTEGER DEFAULT '0' NOT NULL, 45 bytes_tx INTEGER DEFAULT '0' NOT NULL 46); 47 48CREATE TABLE dbmail_users ( 49 user_idnr INTEGER PRIMARY KEY, 50 userid TEXT NOT NULL, 51 passwd TEXT NOT NULL, 52 client_idnr INTEGER DEFAULT '0' NOT NULL, 53 maxmail_size INTEGER DEFAULT '0' NOT NULL, 54 curmail_size INTEGER DEFAULT '0' NOT NULL, 55 encryption_type TEXT DEFAULT '' NOT NULL, 56 last_login DATETIME DEFAULT '1979-11-03 22:05:58' NOT NULL 57); 58CREATE UNIQUE INDEX dbmail_users_1 ON dbmail_users(userid); 59 60CREATE TABLE dbmail_mailboxes ( 61 mailbox_idnr INTEGER PRIMARY KEY, 62 owner_idnr INTEGER DEFAULT '0' NOT NULL, 63 name TEXT BINARY NOT NULL, 64 seq INTEGER DEFAULT '0' NOT NULL, 65 seen_flag BOOLEAN default '0' not null, 66 answered_flag BOOLEAN default '0' not null, 67 deleted_flag BOOLEAN default '0' not null, 68 flagged_flag BOOLEAN default '0' not null, 69 recent_flag BOOLEAN default '0' not null, 70 draft_flag BOOLEAN default '0' not null, 71 no_inferiors BOOLEAN default '0' not null, 72 no_select BOOLEAN default '0' not null, 73 permission BOOLEAN default '2' 74); 75CREATE INDEX dbmail_mailboxes_1 ON dbmail_mailboxes(name); 76CREATE INDEX dbmail_mailboxes_2 ON dbmail_mailboxes(owner_idnr); 77CREATE UNIQUE INDEX dbmail_mailboxes_3 ON dbmail_mailboxes(owner_idnr,name); 78CREATE INDEX dbmail_mailbox_4 ON dbmail_mailboxes(seq); 79 80CREATE TRIGGER fk_insert_mailboxes_users_idnr 81 BEFORE INSERT ON dbmail_mailboxes 82 FOR EACH ROW BEGIN 83 SELECT CASE 84 WHEN (new.owner_idnr IS NOT NULL) 85 AND ((SELECT user_idnr FROM dbmail_users WHERE user_idnr = new.owner_idnr) IS NULL) 86 THEN RAISE (ABORT, 'insert on table "dbmail_mailboxes" violates foreign key constraint "fk_insert_mailboxes_users_idnr"') 87 END; 88 END; 89CREATE TRIGGER fk_update1_mailboxes_users_idnr 90 BEFORE UPDATE ON dbmail_mailboxes 91 FOR EACH ROW BEGIN 92 SELECT CASE 93 WHEN (new.owner_idnr IS NOT NULL) 94 AND ((SELECT user_idnr FROM dbmail_users WHERE user_idnr = new.owner_idnr) IS NULL) 95 THEN RAISE (ABORT, 'update on table "dbmail_mailboxes" violates foreign key constraint "fk_update2_mailboxes_users_idnr"') 96 END; 97 END; 98CREATE TRIGGER fk_update2_mailboxes_users_idnr 99 AFTER UPDATE ON dbmail_users 100 FOR EACH ROW BEGIN 101 UPDATE dbmail_mailboxes SET owner_idnr = new.user_idnr WHERE owner_idnr = OLD.user_idnr; 102 END; 103CREATE TRIGGER fk_delete_mailboxes_users_idnr 104 BEFORE DELETE ON dbmail_users 105 FOR EACH ROW BEGIN 106 DELETE FROM dbmail_mailboxes WHERE owner_idnr = OLD.user_idnr; 107 END; 108 109 110CREATE TABLE dbmail_subscription ( 111 user_id INTEGER NOT NULL, 112 mailbox_id INTEGER NOT NULL 113); 114CREATE UNIQUE INDEX dbmail_subscriptioin_1 ON dbmail_subscription(user_id, mailbox_id); 115 116CREATE TRIGGER fk_insert_subscription_users_idnr 117 BEFORE INSERT ON dbmail_subscription 118 FOR EACH ROW BEGIN 119 SELECT CASE 120 WHEN (new.user_id IS NOT NULL) 121 AND ((SELECT user_idnr FROM dbmail_users WHERE user_idnr = new.user_id) IS NULL) 122 THEN RAISE (ABORT, 'insert on table "dbmail_subscription" violates foreign key constraint "fk_insert_subscription_users_idnr"') 123 END; 124 END; 125CREATE TRIGGER fk_update1_subscription_users_idnr 126 BEFORE UPDATE ON dbmail_subscription 127 FOR EACH ROW BEGIN 128 SELECT CASE 129 WHEN (new.user_id IS NOT NULL) 130 AND ((SELECT user_idnr FROM dbmail_users WHERE user_idnr = new.user_id) IS NULL) 131 THEN RAISE (ABORT, 'update on table "dbmail_subscription" violates foreign key constraint "fk_update1_subscription_users_idnr"') 132 END; 133 END; 134CREATE TRIGGER fk_update2_subscription_users_idnr 135 AFTER UPDATE ON dbmail_users 136 FOR EACH ROW BEGIN 137 UPDATE dbmail_subscription SET user_id = new.user_idnr WHERE user_id = OLD.user_idnr; 138 END; 139CREATE TRIGGER fk_delete_subscription_users_idnr 140 BEFORE DELETE ON dbmail_users 141 FOR EACH ROW BEGIN 142 DELETE FROM dbmail_subscription WHERE user_id = OLD.user_idnr; 143 END; 144 145CREATE TRIGGER fk_insert_subscription_mailbox_id 146 BEFORE INSERT ON dbmail_subscription 147 FOR EACH ROW BEGIN 148 SELECT CASE 149 WHEN (new.mailbox_id IS NOT NULL) 150 AND ((SELECT mailbox_idnr FROM dbmail_mailboxes WHERE mailbox_idnr = new.mailbox_id) IS NULL) 151 THEN RAISE (ABORT, 'insert on table "dbmail_subscription" violates foreign key constraint "fk_insert_subscription_mailbox_id"') 152 END; 153 END; 154CREATE TRIGGER fk_update1_subscription_mailbox_id 155 BEFORE UPDATE ON dbmail_subscription 156 FOR EACH ROW BEGIN 157 SELECT CASE 158 WHEN (new.mailbox_id IS NOT NULL) 159 AND ((SELECT mailbox_idnr FROM dbmail_mailboxes WHERE mailbox_idnr = new.mailbox_id) IS NULL) 160 THEN RAISE (ABORT, 'update on table "dbmail_subscription" violates foreign key constraint "fk_update1_subscription_mailbox_id"') 161 END; 162 END; 163CREATE TRIGGER fk_update2_subscription_mailbox_id 164 AFTER UPDATE ON dbmail_mailboxes 165 FOR EACH ROW BEGIN 166 UPDATE dbmail_subscription SET mailbox_id = new.mailbox_idnr WHERE mailbox_id = OLD.mailbox_idnr; 167 END; 168CREATE TRIGGER fk_delete_subscription_mailbox_id 169 BEFORE DELETE ON dbmail_mailboxes 170 FOR EACH ROW BEGIN 171 DELETE FROM dbmail_subscription WHERE mailbox_id = OLD.mailbox_idnr; 172 END; 173 174 175CREATE TABLE dbmail_acl ( 176 user_id INTEGER NOT NULL, 177 mailbox_id INTEGER NOT NULL, 178 lookup_flag BOOLEAN default '0' not null, 179 read_flag BOOLEAN default '0' not null, 180 seen_flag BOOLEAN default '0' not null, 181 write_flag BOOLEAN default '0' not null, 182 insert_flag BOOLEAN default '0' not null, 183 post_flag BOOLEAN default '0' not null, 184 create_flag BOOLEAN default '0' not null, 185 delete_flag BOOLEAN default '0' not null, 186 deleted_flag BOOLEAN default '0' not null, 187 expunge_flag BOOLEAN default '0' not null, 188 administer_flag BOOLEAN default '0' not null 189); 190CREATE INDEX dbmail_acl_1 ON dbmail_acl(user_id); 191CREATE INDEX dbmail_acl_2 ON dbmail_acl(mailbox_id); 192CREATE UNIQUE INDEX dbmail_acl_3 ON dbmail_acl(user_id, mailbox_id); 193 194CREATE TRIGGER fk_insert_acl_user_id 195 BEFORE INSERT ON dbmail_acl 196 FOR EACH ROW BEGIN 197 SELECT CASE 198 WHEN (new.user_id IS NOT NULL) 199 AND ((SELECT user_idnr FROM dbmail_users WHERE user_idnr = new.user_id) IS NULL) 200 THEN RAISE (ABORT, 'insert on table "dbmail_acl" violates foreign key constraint "fk_insert_acl_user_id"') 201 END; 202 END; 203CREATE TRIGGER fk_update1_acl_user_id 204 BEFORE UPDATE ON dbmail_acl 205 FOR EACH ROW BEGIN 206 SELECT CASE 207 WHEN (new.user_id IS NOT NULL) 208 AND ((SELECT user_idnr FROM dbmail_users WHERE user_idnr = new.user_id) IS NULL) 209 THEN RAISE (ABORT, 'update on table "dbmail_acl" violates foreign key constraint "fk_update1_acl_user_id"') 210 END; 211 END; 212CREATE TRIGGER fk_update2_acl_user_id 213 AFTER UPDATE ON dbmail_users 214 FOR EACH ROW BEGIN 215 UPDATE dbmail_acl SET user_id = new.user_idnr WHERE user_id = OLD.user_idnr; 216 END; 217CREATE TRIGGER fk_delete_acl_user_id 218 BEFORE DELETE ON dbmail_users 219 FOR EACH ROW BEGIN 220 DELETE FROM dbmail_acl WHERE user_id = OLD.user_idnr; 221 END; 222 223CREATE TRIGGER fk_insert_acl_mailbox_id 224 BEFORE INSERT ON dbmail_acl 225 FOR EACH ROW BEGIN 226 SELECT CASE 227 WHEN (new.mailbox_id IS NOT NULL) 228 AND ((SELECT mailbox_idnr FROM dbmail_mailboxes WHERE mailbox_idnr = new.mailbox_id) IS NULL) 229 THEN RAISE (ABORT, 'insert on table "dbmail_acl" violates foreign key constraint "fk_insert_acl_mailbox_id"') 230 END; 231 END; 232CREATE TRIGGER fk_update1_acl_mailbox_id 233 BEFORE UPDATE ON dbmail_acl 234 FOR EACH ROW BEGIN 235 SELECT CASE 236 WHEN (new.mailbox_id IS NOT NULL) 237 AND ((SELECT mailbox_idnr FROM dbmail_mailboxes WHERE mailbox_idnr = new.mailbox_id) IS NULL) 238 THEN RAISE (ABORT, 'update on table "dbmail_acl" violates foreign key constraint "fk_update1_acl_mailbox_id"') 239 END; 240 END; 241CREATE TRIGGER fk_update2_acl_mailbox_id 242 AFTER UPDATE ON dbmail_mailboxes 243 FOR EACH ROW BEGIN 244 UPDATE dbmail_acl SET mailbox_id = new.mailbox_idnr WHERE mailbox_id = OLD.mailbox_idnr; 245 END; 246CREATE TRIGGER fk_delete_acl_mailbox_id 247 BEFORE DELETE ON dbmail_mailboxes 248 FOR EACH ROW BEGIN 249 DELETE FROM dbmail_acl WHERE mailbox_id = OLD.mailbox_idnr; 250 END; 251 252 253 254CREATE TABLE dbmail_physmessage ( 255 id INTEGER PRIMARY KEY, 256 messagesize INTEGER DEFAULT '0' NOT NULL, 257 rfcsize INTEGER DEFAULT '0' NOT NULL, 258 internal_date DATETIME default '0' not null 259); 260 261CREATE TABLE dbmail_messages ( 262 message_idnr INTEGER PRIMARY KEY, 263 mailbox_idnr INTEGER DEFAULT '0' NOT NULL, 264 physmessage_id INTEGER DEFAULT '0' NOT NULL, 265 seen_flag BOOLEAN default '0' not null, 266 answered_flag BOOLEAN default '0' not null, 267 deleted_flag BOOLEAN default '0' not null, 268 flagged_flag BOOLEAN default '0' not null, 269 recent_flag BOOLEAN default '0' not null, 270 draft_flag BOOLEAN default '0' not null, 271 unique_id TEXT NOT NULL, 272 status BOOLEAN unsigned default '0' not null 273); 274CREATE INDEX dbmail_messages_1 ON dbmail_messages(mailbox_idnr); 275CREATE INDEX dbmail_messages_2 ON dbmail_messages(physmessage_id); 276CREATE INDEX dbmail_messages_3 ON dbmail_messages(seen_flag); 277CREATE INDEX dbmail_messages_4 ON dbmail_messages(unique_id); 278CREATE INDEX dbmail_messages_5 ON dbmail_messages(status); 279CREATE INDEX dbmail_messages_6 ON dbmail_messages(mailbox_idnr,status); 280CREATE INDEX dbmail_messages_7 ON dbmail_messages(mailbox_idnr,status,seen_flag); 281CREATE INDEX dbmail_messages_8 ON dbmail_messages(mailbox_idnr,status,recent_flag); 282 283CREATE TRIGGER fk_insert_messages_physmessage_id 284 BEFORE INSERT ON dbmail_messages 285 FOR EACH ROW BEGIN 286 SELECT CASE 287 WHEN (new.physmessage_id IS NOT NULL) 288 AND ((SELECT id FROM dbmail_physmessage WHERE id = new.physmessage_id) IS NULL) 289 THEN RAISE (ABORT, 'insert on table "dbmail_messages" violates foreign key constraint "fk_insert_messages_physmessage_id"') 290 END; 291 END; 292CREATE TRIGGER fk_update1_messages_physmessage_id 293 BEFORE UPDATE ON dbmail_messages 294 FOR EACH ROW BEGIN 295 SELECT CASE 296 WHEN (new.physmessage_id IS NOT NULL) 297 AND ((SELECT id FROM dbmail_physmessage WHERE id = new.physmessage_id) IS NULL) 298 THEN RAISE (ABORT, 'update on table "dbmail_messages" violates foreign key constraint "fk_update1_messages_physmessage_id"') 299 END; 300 END; 301CREATE TRIGGER fk_update2_messages_physmessage_id 302 AFTER UPDATE ON dbmail_physmessage 303 FOR EACH ROW BEGIN 304 UPDATE dbmail_messages SET physmessage_id = new.id WHERE physmessage_id = OLD.id; 305 END; 306CREATE TRIGGER fk_delete_message_physmessage_id 307 BEFORE DELETE ON dbmail_physmessage 308 FOR EACH ROW BEGIN 309 DELETE FROM dbmail_messages WHERE physmessage_id = OLD.id; 310 END; 311 312 313CREATE TRIGGER fk_insert_messages_mailbox_idnr 314 BEFORE INSERT ON dbmail_messages 315 FOR EACH ROW BEGIN 316 SELECT CASE 317 WHEN (new.mailbox_idnr IS NOT NULL) 318 AND ((SELECT mailbox_idnr FROM dbmail_mailboxes WHERE mailbox_idnr = new.mailbox_idnr) IS NULL) 319 THEN RAISE (ABORT, 'insert on table "dbmail_messages" violates foreign key constraint "fk_insert_messages_mailbox_idnr"') 320 END; 321 END; 322CREATE TRIGGER fk_update1_messages_mailbox_idnr 323 BEFORE UPDATE ON dbmail_messages 324 FOR EACH ROW BEGIN 325 SELECT CASE 326 WHEN (new.mailbox_idnr IS NOT NULL) 327 AND ((SELECT mailbox_idnr FROM dbmail_mailboxes WHERE mailbox_idnr = new.mailbox_idnr) IS NULL) 328 THEN RAISE (ABORT, 'update on table "dbmail_messages" violates foreign key constraint "fk_update1_messages_mailbox_idnr"') 329 END; 330 END; 331CREATE TRIGGER fk_update2_messages_mailbox_idnr 332 AFTER UPDATE ON dbmail_mailboxes 333 FOR EACH ROW BEGIN 334 UPDATE dbmail_messages SET mailbox_idnr = new.mailbox_idnr WHERE mailbox_idnr = OLD.mailbox_idnr; 335 END; 336CREATE TRIGGER fk_delete_messages_mailbox_idnr 337 BEFORE DELETE ON dbmail_mailboxes 338 FOR EACH ROW BEGIN 339 DELETE FROM dbmail_messages WHERE mailbox_idnr = OLD.mailbox_idnr; 340 END; 341 342 343 344CREATE TABLE dbmail_messageblks ( 345 messageblk_idnr INTEGER PRIMARY KEY, 346 physmessage_id INTEGER DEFAULT '0' NOT NULL, 347 messageblk TEXT NOT NULL, 348 blocksize INTEGER DEFAULT '0' NOT NULL, 349 is_header BOOLEAN DEFAULT '0' NOT NULL 350); 351CREATE INDEX dbmail_messageblks_1 ON dbmail_messageblks(physmessage_id); 352CREATE INDEX dbmail_messageblks_2 ON dbmail_messageblks(physmessage_id, is_header); 353 354CREATE TRIGGER fk_insert_messageblks_physmessage_id 355 BEFORE INSERT ON dbmail_messageblks 356 FOR EACH ROW BEGIN 357 SELECT CASE 358 WHEN (new.physmessage_id IS NOT NULL) 359 AND ((SELECT id FROM dbmail_physmessage WHERE id = new.physmessage_id) IS NULL) 360 THEN RAISE (ABORT, 'insert on table "dbmail_messageblks" violates foreign key constraint "fk_insert_messageblks_physmessage_id"') 361 END; 362 END; 363CREATE TRIGGER fk_update1_messageblks_physmessage_id 364 BEFORE UPDATE ON dbmail_messageblks 365 FOR EACH ROW BEGIN 366 SELECT CASE 367 WHEN (new.physmessage_id IS NOT NULL) 368 AND ((SELECT id FROM dbmail_physmessage WHERE id = new.physmessage_id) IS NULL) 369 THEN RAISE (ABORT, 'update on table "dbmail_messageblks" violates foreign key constraint "fk_update1_messageblks_physmessage_id"') 370 END; 371 END; 372CREATE TRIGGER fk_update2_messageblks_physmessage_id 373 AFTER UPDATE ON dbmail_physmessage 374 FOR EACH ROW BEGIN 375 UPDATE dbmail_messageblks SET physmessage_id = new.id WHERE physmessage_id = OLD.id; 376 END; 377CREATE TRIGGER fk_delete_messageblks_physmessage_id 378 BEFORE DELETE ON dbmail_physmessage 379 FOR EACH ROW BEGIN 380 DELETE FROM dbmail_messageblks WHERE physmessage_id = OLD.id; 381 END; 382 383 384CREATE TABLE dbmail_auto_replies ( 385 user_idnr INTEGER PRIMARY KEY, 386 reply_body TEXT, 387 start_date DATETIME DEFAULT '1980-01-01 22:05:58' NOT NULL, 388 stop_date DATETIME DEFAULT '1980-01-01 22:05:58' NOT NULL 389); 390CREATE TRIGGER fk_insert_auto_replies_user_idnr 391 BEFORE INSERT ON dbmail_auto_replies 392 FOR EACH ROW BEGIN 393 SELECT CASE 394 WHEN (new.user_idnr IS NOT NULL) 395 AND ((SELECT user_idnr FROM dbmail_users WHERE user_idnr = new.user_idnr) IS NULL) 396 THEN RAISE (ABORT, 'insert on table "dbmail_auto_replies" violates foreign key constraint "fk_insert_auto_replies_user_idnr"') 397 END; 398 END; 399CREATE TRIGGER fk_update1_auto_replies_user_idnr 400 BEFORE UPDATE ON dbmail_auto_replies 401 FOR EACH ROW BEGIN 402 SELECT CASE 403 WHEN (new.user_idnr IS NOT NULL) 404 AND ((SELECT user_idnr FROM dbmail_users WHERE user_idnr = new.user_idnr) IS NULL) 405 THEN RAISE (ABORT, 'update on table "dbmail_auto_replies" violates foreign key constraint "fk_update1_auto_replies_user_idnr"') 406 END; 407 END; 408CREATE TRIGGER fk_update2_auto_replies_user_idnr 409 AFTER UPDATE ON dbmail_users 410 FOR EACH ROW BEGIN 411 UPDATE dbmail_auto_replies SET user_idnr = new.user_idnr WHERE user_idnr = OLD.user_idnr; 412 END; 413CREATE TRIGGER fk_delete_auto_replies_user_idnr 414 BEFORE DELETE ON dbmail_users 415 FOR EACH ROW BEGIN 416 DELETE FROM dbmail_auto_replies WHERE user_idnr = OLD.user_idnr; 417 END; 418 419CREATE TABLE dbmail_auto_notifications ( 420 user_idnr INTEGER PRIMARY KEY, 421 notify_address TEXT 422); 423 424CREATE TRIGGER fk_insert_auto_notifications_user_idnr 425 BEFORE INSERT ON dbmail_auto_notifications 426 FOR EACH ROW BEGIN 427 SELECT CASE 428 WHEN (new.user_idnr IS NOT NULL) 429 AND ((SELECT user_idnr FROM dbmail_users WHERE user_idnr = new.user_idnr) IS NULL) 430 THEN RAISE (ABORT, 'insert on table "dbmail_auto_notifications" violates foreign key constraint "fk_insert_auto_notifications_user_idnr"') 431 END; 432 END; 433CREATE TRIGGER fk_update1_auto_notifications_user_idnr 434 BEFORE UPDATE ON dbmail_auto_notifications 435 FOR EACH ROW BEGIN 436 SELECT CASE 437 WHEN (new.user_idnr IS NOT NULL) 438 AND ((SELECT user_idnr FROM dbmail_users WHERE user_idnr = new.user_idnr) IS NULL) 439 THEN RAISE (ABORT, 'update on table "dbmail_auto_notifications" violates foreign key constraint "fk_update1_auto_notifications_user_idnr"') 440 END; 441 END; 442CREATE TRIGGER fk_update2_auto_notifications_user_idnr 443 AFTER UPDATE ON dbmail_users 444 FOR EACH ROW BEGIN 445 UPDATE dbmail_auto_notifications SET user_idnr = new.user_idnr WHERE user_idnr = OLD.user_idnr; 446 END; 447CREATE TRIGGER fk_delete_auto_notifications_user_idnr 448 BEFORE DELETE ON dbmail_users 449 FOR EACH ROW BEGIN 450 DELETE FROM dbmail_auto_notifications WHERE user_idnr = OLD.user_idnr; 451 END; 452 453 454 455 456 457CREATE TABLE dbmail_pbsp ( 458 idnr INTEGER PRIMARY KEY, 459 since DATETIME default '0' not null, 460 ipnumber TEXT NOT NULL 461); 462CREATE UNIQUE INDEX dbmail_pbsp_1 ON dbmail_pbsp(ipnumber); 463CREATE INDEX dbmail_pbsp_2 ON dbmail_pbsp(since); 464 465CREATE TABLE dbmail_sievescripts ( 466 owner_idnr INTEGER DEFAULT '0' NOT NULL, 467 name TEXT NOT NULL, 468 script TEXT, 469 active BOOLEAN default '0' not null 470); 471CREATE INDEX dbmail_sievescripts_1 ON dbmail_sievescripts(name); 472CREATE INDEX dbmail_sievescripts_2 ON dbmail_sievescripts(owner_idnr); 473CREATE UNIQUE INDEX dbmail_sievescripts_3 ON dbmail_sievescripts(owner_idnr,name); 474 475CREATE TRIGGER fk_insert_sievescripts_owner_idnr 476 BEFORE INSERT ON dbmail_sievescripts 477 FOR EACH ROW BEGIN 478 SELECT CASE 479 WHEN (new.owner_idnr IS NOT NULL) 480 AND ((SELECT user_idnr FROM dbmail_users WHERE user_idnr = new.owner_idnr) IS NULL) 481 THEN RAISE (ABORT, 'insert on table "dbmail_sievescripts" violates foreign key constraint "fk_insert_sievescripts_owner_idnr"') 482 END; 483 END; 484CREATE TRIGGER fk_update1_sievescripts_owner_idnr 485 BEFORE UPDATE ON dbmail_sievescripts 486 FOR EACH ROW BEGIN 487 SELECT CASE 488 WHEN (new.owner_idnr IS NOT NULL) 489 AND ((SELECT user_idnr FROM dbmail_users WHERE user_idnr = new.owner_idnr) IS NULL) 490 THEN RAISE (ABORT, 'update on table "dbmail_sievescripts" violates foreign key constraint "fk_update1_sievescripts_owner_idnr"') 491 END; 492 END; 493CREATE TRIGGER fk_update2_sievescripts_owner_idnr 494 AFTER UPDATE ON dbmail_users 495 FOR EACH ROW BEGIN 496 UPDATE dbmail_sievescripts SET owner_idnr = new.user_idnr WHERE owner_idnr = OLD.user_idnr; 497 END; 498CREATE TRIGGER fk_delete_sievescripts_owner_idnr 499 BEFORE DELETE ON dbmail_users 500 FOR EACH ROW BEGIN 501 DELETE FROM dbmail_sievescripts WHERE owner_idnr = OLD.user_idnr; 502 END; 503 504 505-- 506-- store all headers by storing all headernames and headervalues in separate 507-- tables. 508-- 509 510CREATE TABLE dbmail_headername ( 511 id INTEGER PRIMARY KEY, 512 headername TEXT NOT NULL DEFAULT '' 513); 514 515CREATE UNIQUE INDEX dbmail_headername_1 on dbmail_headername (headername); 516 517CREATE TABLE dbmail_headervalue ( 518 id INTEGER NOT NULL PRIMARY KEY, 519 hash TEXT NOT NULL, 520 headervalue BLOB NOT NULL, 521 sortfield TEXT NOT NULL, 522 datefield DATETIME 523); 524CREATE INDEX dbmail_headervalue_1 ON dbmail_headervalue(hash); 525CREATE INDEX dbmail_headervalue_2 ON dbmail_headervalue(sortfield); 526CREATE INDEX dbmail_headervalue_3 ON dbmail_headervalue(datefield); 527 528CREATE TABLE dbmail_header ( 529 physmessage_id INTEGER NOT NULL, 530 headername_id INTEGER NOT NULL, 531 headervalue_id INTEGER NOT NULL 532); 533 534CREATE UNIQUE INDEX dbmail_header_1 ON dbmail_header(physmessage_id,headername_id,headervalue_id); 535 536CREATE TRIGGER fk_insert_header_physmessage_id 537 BEFORE INSERT ON dbmail_header 538 FOR EACH ROW BEGIN 539 SELECT CASE 540 WHEN (new.physmessage_id IS NOT NULL) 541 AND ((SELECT id FROM dbmail_physmessage WHERE id = new.physmessage_id) IS NULL) 542 THEN RAISE (ABORT, 'insert on table "dbmail_header" violates foreign key constraint "fk_insert_header_physmessage_id"') 543 END; 544 END; 545 546CREATE TRIGGER fk_update_header_physmessage_id 547 BEFORE UPDATE ON dbmail_header 548 FOR EACH ROW BEGIN 549 SELECT CASE 550 WHEN (new.physmessage_id IS NOT NULL) 551 AND ((SELECT id FROM dbmail_physmessage WHERE id = new.physmessage_id) IS NULL) 552 THEN RAISE (ABORT, 'update on table "dbmail_header" violates foreign key constraint "fk_update_header_physmessage_id"') 553 END; 554 END; 555 556CREATE TRIGGER fk_update2_header_physmessage_id 557 AFTER UPDATE ON dbmail_physmessage 558 FOR EACH ROW BEGIN 559 UPDATE dbmail_header SET physmessage_id = new.id WHERE physmessage_id = OLD.id; 560 END; 561 562CREATE TRIGGER fk_delete_header_physmessage_id 563 BEFORE DELETE ON dbmail_physmessage 564 FOR EACH ROW BEGIN 565 DELETE FROM dbmail_header WHERE physmessage_id = OLD.id; 566 END; 567 568CREATE TRIGGER fk_insert_header_headername_id 569 BEFORE INSERT ON dbmail_header 570 FOR EACH ROW BEGIN 571 SELECT CASE 572 WHEN (new.headername_id IS NOT NULL) 573 AND ((SELECT id FROM dbmail_headername WHERE id = new.headername_id) IS NULL) 574 THEN RAISE (ABORT, 'insert on table "dbmail_header" violates foreign key constraint "fk_insert_header_headername_id"') 575 END; 576 END; 577 578CREATE TRIGGER fk_update_header_headername_id 579 BEFORE UPDATE ON dbmail_header 580 FOR EACH ROW BEGIN 581 SELECT CASE 582 WHEN (new.headername_id IS NOT NULL) 583 AND ((SELECT id FROM dbmail_headername WHERE id = new.headername_id) IS NULL) 584 THEN RAISE (ABORT, 'update on table "dbmail_header" violates foreign key constraint "fk_update_header_headername_id"') 585 END; 586 END; 587 588CREATE TRIGGER fk_update2_header_headername_id 589 AFTER UPDATE ON dbmail_headername 590 FOR EACH ROW BEGIN 591 UPDATE dbmail_header SET headername_id = new.id WHERE headername_id = OLD.id; 592 END; 593 594CREATE TRIGGER fk_delete_header_headername_id 595 BEFORE DELETE ON dbmail_headername 596 FOR EACH ROW BEGIN 597 DELETE FROM dbmail_header WHERE headername_id = OLD.id; 598 END; 599 600CREATE TRIGGER fk_insert_header_headervalue_id 601 BEFORE INSERT ON dbmail_header 602 FOR EACH ROW BEGIN 603 SELECT CASE 604 WHEN (new.headervalue_id IS NOT NULL) 605 AND ((SELECT id FROM dbmail_headervalue WHERE id = new.headervalue_id) IS NULL) 606 THEN RAISE (ABORT, 'insert on table "dbmail_header" violates foreign key constraint "fk_insert_header_headervalue_id"') 607 END; 608 END; 609 610CREATE TRIGGER fk_update_header_headervalue_id 611 BEFORE UPDATE ON dbmail_header 612 FOR EACH ROW BEGIN 613 SELECT CASE 614 WHEN (new.headervalue_id IS NOT NULL) 615 AND ((SELECT id FROM dbmail_headervalue WHERE id = new.headervalue_id) IS NULL) 616 THEN RAISE (ABORT, 'update on table "dbmail_header" violates foreign key constraint "fk_update_header_headervalue_id"') 617 END; 618 END; 619 620CREATE TRIGGER fk_update2_header_headervalue_id 621 AFTER UPDATE ON dbmail_headervalue 622 FOR EACH ROW BEGIN 623 UPDATE dbmail_header SET headervalue_id = new.id WHERE headervalue_id = OLD.id; 624 END; 625 626CREATE TRIGGER fk_delete_header_headervalue_id 627 BEFORE DELETE ON dbmail_headervalue 628 FOR EACH ROW BEGIN 629 DELETE FROM dbmail_header WHERE headervalue_id = OLD.id; 630 END; 631 632 633 634-- Threading 635 636-- support fast threading by breaking out In-Reply-To/References headers 637-- these fields contain zero or more Message-Id values that determine the message 638-- threading 639 640CREATE TABLE dbmail_referencesfield ( 641 physmessage_id INTEGER NOT NULL, 642 id INTEGER NOT NULL PRIMARY KEY, 643 referencesfield TEXT NOT NULL DEFAULT '' 644); 645 646CREATE UNIQUE INDEX dbmail_referencesfield_1 on dbmail_referencesfield (physmessage_id, referencesfield); 647-- FOREIGN KEY (physmessage_id) 648-- REFERENCES dbmail_physmessage(id) 649-- ON UPDATE CASCADE ON DELETE CASCADE 650 651CREATE TRIGGER fk_insert_referencesfield_physmessage_id 652 BEFORE INSERT ON dbmail_referencesfield 653 FOR EACH ROW BEGIN 654 SELECT CASE 655 WHEN (new.physmessage_id IS NOT NULL) 656 AND ((SELECT id FROM dbmail_physmessage WHERE id = new.physmessage_id) IS NULL) 657 THEN RAISE (ABORT, 'insert on table "dbmail_referencesfield" violates foreign key constraint "fk_insert_referencesfield_physmessage_id"') 658 END; 659 END; 660CREATE TRIGGER fk_update1_referencesfield_physmessage_id 661 BEFORE UPDATE ON dbmail_referencesfield 662 FOR EACH ROW BEGIN 663 SELECT CASE 664 WHEN (new.physmessage_id IS NOT NULL) 665 AND ((SELECT id FROM dbmail_physmessage WHERE id = new.physmessage_id) IS NULL) 666 THEN RAISE (ABORT, 'update on table "dbmail_referencesfield" violates foreign key constraint "fk_update1_referencesfield_physmessage_id"') 667 END; 668 END; 669CREATE TRIGGER fk_update2_referencesfield_physmessage_id 670 AFTER UPDATE ON dbmail_physmessage 671 FOR EACH ROW BEGIN 672 UPDATE dbmail_referencesfield SET physmessage_id = new.id WHERE physmessage_id = OLD.id; 673 END; 674CREATE TRIGGER fk_delete_referencesfield_physmessage_id 675 BEFORE DELETE ON dbmail_physmessage 676 FOR EACH ROW BEGIN 677 DELETE FROM dbmail_referencesfield WHERE physmessage_id = OLD.id; 678 END; 679 680-- Table structure for table `dbmail_replycache` 681 682CREATE TABLE dbmail_replycache ( 683 to_addr TEXT NOT NULL default '', 684 from_addr TEXT NOT NULL default '', 685 handle TEXT NOT NULL default '', 686 lastseen datetime NOT NULL default '0000-00-00 00:00:00' 687); 688 689CREATE UNIQUE INDEX dbmail_replycache_1 on dbmail_replycache (to_addr,from_addr, handle); 690 691-- 692-- Add tables and columns to hold Sieve scripts. 693 694 695CREATE TABLE dbmail_usermap ( 696 login TEXT NOT NULL, 697 sock_allow TEXT NOT NULL, 698 sock_deny TEXT NOT NULL, 699 userid TEXT NOT NULL 700); 701 702CREATE UNIQUE INDEX usermap_idx_1 ON dbmail_usermap(login, sock_allow, userid); 703 704 705 706 707-- Create the user for the delivery chain 708INSERT INTO dbmail_users (userid, passwd, encryption_type) 709 VALUES ('__@!internal_delivery_user!@__', '', 'md5'); 710-- Create the 'anyone' user which is used for ACLs. 711INSERT INTO dbmail_users (userid, passwd, encryption_type) 712 VALUES ('anyone', '', 'md5'); 713-- Create the user to own #Public mailboxes 714INSERT INTO dbmail_users (userid, passwd, encryption_type) 715 VALUES ('__public__', '', 'md5'); 716 717COMMIT; 718 719 720-- support faster FETCH commands by caching ENVELOPE information 721 722CREATE TABLE dbmail_envelope ( 723 physmessage_id INTEGER NOT NULL, 724 id INTEGER NOT NULL PRIMARY KEY, 725 envelope TEXT NOT NULL DEFAULT '' 726); 727 728CREATE UNIQUE INDEX dbmail_envelope_1 on dbmail_envelope (physmessage_id); 729CREATE UNIQUE INDEX dbmail_envelope_2 on dbmail_envelope (physmessage_id, id); 730 731CREATE TRIGGER fk_insert_envelope_physmessage_id 732 BEFORE INSERT ON dbmail_envelope 733 FOR EACH ROW BEGIN 734 SELECT CASE 735 WHEN (new.physmessage_id IS NOT NULL) 736 AND ((SELECT id FROM dbmail_physmessage WHERE id = new.physmessage_id) IS NULL) 737 THEN RAISE (ABORT, 'insert on table "dbmail_envelope" violates foreign key constraint "fk_insert_envelope_physmessage_id"') 738 END; 739 END; 740CREATE TRIGGER fk_update1_envelope_physmessage_id 741 BEFORE UPDATE ON dbmail_envelope 742 FOR EACH ROW BEGIN 743 SELECT CASE 744 WHEN (new.physmessage_id IS NOT NULL) 745 AND ((SELECT id FROM dbmail_physmessage WHERE id = new.physmessage_id) IS NULL) 746 THEN RAISE (ABORT, 'update on table "dbmail_envelope" violates foreign key constraint "fk_update1_envelope_physmessage_id"') 747 END; 748 END; 749CREATE TRIGGER fk_update2_envelope_physmessage_id 750 AFTER UPDATE ON dbmail_physmessage 751 FOR EACH ROW BEGIN 752 UPDATE dbmail_envelope SET physmessage_id = new.id WHERE physmessage_id = OLD.id; 753 END; 754CREATE TRIGGER fk_delete_envelope_physmessage_id 755 BEFORE DELETE ON dbmail_physmessage 756 FOR EACH ROW BEGIN 757 DELETE FROM dbmail_envelope WHERE physmessage_id = OLD.id; 758 END; 759 760 761 762-- 763-- 2.3.x additions 764-- 765 766DROP TABLE IF EXISTS dbmail_mimeparts; 767CREATE TABLE dbmail_mimeparts ( 768 id INTEGER NOT NULL PRIMARY KEY, 769 hash TEXT NOT NULL, 770 data BLOB NOT NULL, 771 size INTEGER NOT NULL 772); 773 774CREATE INDEX dbmail_mimeparts_1 ON dbmail_mimeparts(hash); 775 776DROP TABLE IF EXISTS dbmail_partlists; 777CREATE TABLE dbmail_partlists ( 778 physmessage_id INTEGER NOT NULL, 779 is_header BOOLEAN DEFAULT '0' NOT NULL, 780 part_key INTEGER DEFAULT '0' NOT NULL, 781 part_depth INTEGER DEFAULT '0' NOT NULL, 782 part_order INTEGER DEFAULT '0' NOT NULL, 783 part_id INTEGER NOT NULL 784); 785 786CREATE INDEX dbmail_partlists_1 ON dbmail_partlists(physmessage_id); 787CREATE INDEX dbmail_partlists_2 ON dbmail_partlists(part_id); 788CREATE UNIQUE INDEX message_parts ON dbmail_partlists(physmessage_id, part_key, part_depth, part_order); 789 790-- ALTER TABLE ONLY dbmail_partlists 791-- ADD CONSTRAINT dbmail_partlists_part_id_fkey FOREIGN KEY (part_id) REFERENCES dbmail_mimeparts(id) ON UPDATE CASCADE ON DELETE CASCADE; 792 793DROP TRIGGER IF EXISTS fk_insert_partlists_mimeparts_id; 794CREATE TRIGGER fk_insert_partlists_mimeparts_id 795 BEFORE INSERT ON dbmail_partlists 796 FOR EACH ROW BEGIN 797 SELECT CASE 798 WHEN (new.part_id IS NOT NULL) 799 AND ((SELECT id FROM dbmail_mimeparts WHERE id = new.part_id) IS NULL) 800 THEN RAISE (ABORT, 'insert on table "dbmail_partlists" violates foreign key constraint "fk_insert_partlists_mimeparts_id"') 801 END; 802 END; 803DROP TRIGGER IF EXISTS fk_update_partlists_mimeparts_id; 804CREATE TRIGGER fk_update_partlists_mimeparts_id 805 BEFORE UPDATE ON dbmail_partlists 806 FOR EACH ROW BEGIN 807 SELECT CASE 808 WHEN (new.part_id IS NOT NULL) 809 AND ((SELECT id FROM dbmail_mimeparts WHERE id = new.part_id) IS NULL) 810 THEN RAISE (ABORT, 'update on table "dbmail_partlists" violates foreign key constraint "fk_update_partlists_mimeparts_id"') 811 END; 812 END; 813DROP TRIGGER IF EXISTS fk_update2_partlists_mimeparts_id; 814CREATE TRIGGER fk_update2_partlists_mimeparts_id 815 AFTER UPDATE ON dbmail_mimeparts 816 FOR EACH ROW BEGIN 817 UPDATE dbmail_partlists SET part_id = new.id WHERE part_id = OLD.id; 818 END; 819DROP TRIGGER IF EXISTS fk_delete_partlists_mimeparts_id; 820CREATE TRIGGER fk_delete_partlists_mimeparts_id 821 BEFORE DELETE ON dbmail_mimeparts 822 FOR EACH ROW BEGIN 823 DELETE FROM dbmail_partlists WHERE part_id = OLD.id; 824 END; 825 826-- ALTER TABLE ONLY dbmail_partlists 827-- ADD CONSTRAINT dbmail_partlists_physmessage_id_fkey FOREIGN KEY (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE; 828 829DROP TRIGGER IF EXISTS fk_insert_partlists_physmessage_id; 830CREATE TRIGGER fk_insert_partlists_physmessage_id 831 BEFORE INSERT ON dbmail_partlists 832 FOR EACH ROW BEGIN 833 SELECT CASE 834 WHEN (new.physmessage_id IS NOT NULL) 835 AND ((SELECT id FROM dbmail_physmessage WHERE id = new.physmessage_id) IS NULL) 836 THEN RAISE (ABORT, 'insert on table "dbmail_partlists" violates foreign key constraint "fk_insert_partlists_physmessage_id"') 837 END; 838 END; 839DROP TRIGGER IF EXISTS fk_update_partlists_physmessage_id; 840CREATE TRIGGER fk_update_partlists_physmessage_id 841 BEFORE UPDATE ON dbmail_partlists 842 FOR EACH ROW BEGIN 843 SELECT CASE 844 WHEN (new.physmessage_id IS NOT NULL) 845 AND ((SELECT id FROM dbmail_physmessage WHERE id = new.physmessage_id) IS NULL) 846 THEN RAISE (ABORT, 'update on table "dbmail_partlists" violates foreign key constraint "fk_update_partlists_physmessage_id"') 847 END; 848 END; 849DROP TRIGGER IF EXISTS fk_update2_partlists_physmessage_id; 850CREATE TRIGGER fk_update2_partlists_physmessage_id 851 AFTER UPDATE ON dbmail_physmessage 852 FOR EACH ROW BEGIN 853 UPDATE dbmail_partlists SET physmessage_id = new.id WHERE physmessage_id = OLD.id; 854 END; 855DROP TRIGGER IF EXISTS fk_delete_partlists_physmessage_id; 856CREATE TRIGGER fk_delete_partlists_physmessage_id 857 BEFORE DELETE ON dbmail_physmessage 858 FOR EACH ROW BEGIN 859 DELETE FROM dbmail_partlists WHERE physmessage_id = OLD.id; 860 END; 861 862CREATE TABLE dbmail_keywords ( 863 keyword TEXT NOT NULL, 864 message_idnr INT NOT NULL 865); 866CREATE UNIQUE INDEX dbmail_keywords_1 ON dbmail_keywords(keyword,message_idnr); 867 868DROP TRIGGER IF EXISTS fk_insert_dbmail_keywords_dbmail_messages_message_idnr; 869CREATE TRIGGER fk_insert_dbmail_keywords_dbmail_messages_message_idnr 870 BEFORE INSERT ON dbmail_keywords 871 FOR EACH ROW BEGIN 872 SELECT CASE 873 WHEN (new.message_idnr IS NOT NULL) 874 AND ((SELECT message_idnr FROM dbmail_messages WHERE message_idnr = new.message_idnr) IS NULL) 875 THEN RAISE (ABORT, 'insert on table "dbmail_keywords" violates foreign key constraint "fk_insert_dbmail_keywords_dbmail_messages_message_idnr"') 876 END; 877 END; 878 879DROP TRIGGER IF EXISTS fk_update_dbmail_keywords_dbmail_messages_message_idnr; 880CREATE TRIGGER fk_update_dbmail_keywords_dbmail_messages_message_idnr 881 BEFORE UPDATE ON dbmail_keywords 882 FOR EACH ROW BEGIN 883 SELECT CASE 884 WHEN (new.message_idnr IS NOT NULL) 885 AND ((SELECT message_idnr FROM dbmail_messages WHERE message_idnr = new.message_idnr) IS NULL) 886 THEN RAISE (ABORT, 'update on table "dbmail_keywords" violates foreign key constraint "fk_update_dbmail_keywords_dbmail_messages_message_idnr"') 887 END; 888 END; 889 890DROP TRIGGER IF EXISTS fk_update2_dbmail_keywords_dbmail_messages_message_idnr; 891CREATE TRIGGER fk_update2_dbmail_keywords_dbmail_messages_message_idnr 892 AFTER UPDATE ON dbmail_messages 893 FOR EACH ROW BEGIN 894 UPDATE dbmail_keywords SET message_idnr = new.message_idnr WHERE message_idnr = OLD.message_idnr; 895 END; 896 897DROP TRIGGER IF EXISTS fk_delete_dbmail_keywords_dbmail_messages_message_idnr; 898CREATE TRIGGER fk_delete_dbmail_keywords_dbmail_messages_message_idnr 899 BEFORE DELETE ON dbmail_messages 900 FOR EACH ROW BEGIN 901 DELETE FROM dbmail_keywords WHERE message_idnr = OLD.message_idnr; 902 END; 903 904 905DROP TABLE IF EXISTS dbmail_filters; 906CREATE TABLE dbmail_filters ( 907 id INTEGER PRIMARY KEY, 908 user_id INTEGER NOT NULL, 909 headername TEXT NOT NULL, 910 headervalue TEXT NOT NULL, 911 mailbox TEXT NOT NULL 912); 913 914CREATE UNIQUE INDEX dbmail_filters_index_1 ON dbmail_filters(user_id, id); 915CREATE TRIGGER fk_insert_filters_users_user_idnr 916 BEFORE INSERT ON dbmail_filters 917 FOR EACH ROW BEGIN 918 SELECT CASE 919 WHEN (new.user_id IS NOT NULL) 920 AND ((SELECT user_idnr FROM dbmail_users WHERE user_idnr = new.user_id) IS NULL) 921 THEN RAISE (ABORT, 'insert on table "dbmail_filters" violates foreign key constraint "fk_insert_filters_users_user_idnr"') 922 END; 923 END; 924 925CREATE TRIGGER fk_update_filters_users_user_idnr 926 BEFORE UPDATE ON dbmail_filters 927 FOR EACH ROW BEGIN 928 SELECT CASE 929 WHEN (new.user_id IS NOT NULL) 930 AND ((SELECT user_idnr FROM dbmail_users WHERE user_idnr = new.user_id) IS NULL) 931 THEN RAISE (ABORT, 'update on table "dbmail_filters" violates foreign key constraint "fk_update_filters_users_user_idnr"') 932 END; 933 END; 934 935CREATE TRIGGER fk_update2_filters_users_user_idnr 936 AFTER UPDATE ON dbmail_users 937 FOR EACH ROW BEGIN 938 UPDATE dbmail_filters SET user_id = new.user_idnr WHERE user_id = OLD.user_idnr; 939 END; 940 941CREATE TRIGGER fk_delete_filters_users_user_idnr 942 BEFORE DELETE ON dbmail_users 943 FOR EACH ROW BEGIN 944 DELETE FROM dbmail_filters WHERE user_id = OLD.user_idnr; 945 END; 946 947CREATE VIEW dbmail_fromfield AS 948 SELECT physmessage_id,sortfield AS fromfield 949 FROM dbmail_messages m 950 JOIN dbmail_header h USING (physmessage_id) 951 JOIN dbmail_headername n ON h.headername_id = n.id 952 JOIN dbmail_headervalue v ON h.headervalue_id = v.id 953WHERE n.headername='from'; 954 955CREATE VIEW dbmail_ccfield AS 956 SELECT physmessage_id,sortfield AS ccfield 957 FROM dbmail_messages m 958 JOIN dbmail_header h USING (physmessage_id) 959 JOIN dbmail_headername n ON h.headername_id = n.id 960 JOIN dbmail_headervalue v ON h.headervalue_id = v.id 961WHERE n.headername='cc'; 962 963CREATE VIEW dbmail_tofield AS 964 SELECT physmessage_id,sortfield AS tofield 965 FROM dbmail_messages m 966 JOIN dbmail_header h USING (physmessage_id) 967 JOIN dbmail_headername n ON h.headername_id = n.id 968 JOIN dbmail_headervalue v ON h.headervalue_id = v.id 969WHERE n.headername='to'; 970 971CREATE VIEW dbmail_subjectfield AS 972 SELECT physmessage_id,headervalue AS subjectfield 973 FROM dbmail_messages m 974 JOIN dbmail_header h USING (physmessage_id) 975 JOIN dbmail_headername n ON h.headername_id = n.id 976 JOIN dbmail_headervalue v ON h.headervalue_id = v.id 977WHERE n.headername='subject'; 978 979CREATE VIEW dbmail_datefield AS 980 SELECT physmessage_id,datefield,sortfield 981 FROM dbmail_messages m 982 JOIN dbmail_header h USING (physmessage_id) 983 JOIN dbmail_headername n ON h.headername_id = n.id 984 JOIN dbmail_headervalue v ON h.headervalue_id = v.id 985WHERE n.headername='date'; 986 987 988 989