forked from technion/maia_mailguard
-
Notifications
You must be signed in to change notification settings - Fork 0
/
maia-pgsql.sql
919 lines (815 loc) · 42.2 KB
/
maia-pgsql.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
-- $Id: maia-pgsql.sql 1568 2011-07-01 01:12:12Z rjl $
--
-- PLEASE NOTE: WHEN EDITING THIS FILE, USE iso-8859-1
--
--
-- MAIA MAILGUARD LICENSE v.1.0
--
-- Copyright 2004 by Robert LeBlanc <[email protected]>
-- David Morton <[email protected]>
-- All rights reserved.
--
-- PREAMBLE
--
-- This License is designed for users of Maia Mailguard
-- ("the Software") who wish to support the Maia Mailguard project by
-- leaving "Maia Mailguard" branding information in the HTML output
-- of the pages generated by the Software, and providing links back
-- to the Maia Mailguard home page. Users who wish to remove this
-- branding information should contact the copyright owner to obtain
-- a Rebranding License.
--
-- DEFINITION OF TERMS
--
-- The "Software" refers to Maia Mailguard, including all of the
-- associated PHP, Perl, and SQL scripts, documentation files, graphic
-- icons and logo images.
--
-- GRANT OF LICENSE
--
-- Redistribution and use in source and binary forms, with or without
-- modification, are permitted provided that the following conditions
-- are met:
--
-- 1. Redistributions of source code must retain the above copyright
-- notice, this list of conditions and the following disclaimer.
--
-- 2. Redistributions in binary form must reproduce the above copyright
-- notice, this list of conditions and the following disclaimer in the
-- documentation and/or other materials provided with the distribution.
--
-- 3. The end-user documentation included with the redistribution, if
-- any, must include the following acknowledgment:
--
-- "This product includes software developed by Robert LeBlanc
-- <[email protected]>."
--
-- Alternately, this acknowledgment may appear in the software itself,
-- if and wherever such third-party acknowledgments normally appear.
--
-- 4. At least one of the following branding conventions must be used:
--
-- a. The Maia Mailguard logo appears in the page-top banner of
-- all HTML output pages in an unmodified form, and links
-- directly to the Maia Mailguard home page; or
--
-- b. The "Powered by Maia Mailguard" graphic appears in the HTML
-- output of all gateway pages that lead to this software,
-- linking directly to the Maia Mailguard home page; or
--
-- c. A separate Rebranding License is obtained from the copyright
-- owner, exempting the Licensee from 4(a) and 4(b), subject to
-- the additional conditions laid out in that license document.
--
-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDER AND CONTRIBUTORS
-- "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
-- LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
-- FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
-- COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
-- INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
-- BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
-- OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
-- ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
-- TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
-- USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
-- [users] contains maiad's per-address settings, and
-- links e-mail addresses to Maia users.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
priority INTEGER DEFAULT '7' NOT NULL,
policy_id INTEGER DEFAULT '1' NOT NULL, -- policy.id
email BYTEA NOT NULL UNIQUE,
maia_user_id INTEGER NOT NULL, -- maia_users.id
maia_domain_id INTEGER NOT NULL -- maia_domains.id
);
CREATE UNIQUE INDEX users_idx_email ON users(email);
CREATE INDEX users_idx_maia_user_id ON users(maia_user_id);
-- [mailaddr] contains a list of sender e-mail addresses
-- referenced by users' whitelists and blacklists.
CREATE TABLE mailaddr (
id SERIAL PRIMARY KEY,
priority INTEGER DEFAULT '7' NOT NULL,
email BYTEA NOT NULL UNIQUE
);
CREATE UNIQUE INDEX mailaddr_idx_email ON mailaddr(email);
-- [wblist] contains the whitelist and blacklist records, on
-- a per-user (not per-address) basis.
CREATE TABLE wblist (
rid INTEGER NOT NULL, -- maia_users.id
sid INTEGER NOT NULL, -- mailaddr.id
wb CHAR(1) NOT NULL,
PRIMARY KEY (rid,sid)
);
-- [policy] contains maiad's policy settings, which
-- Maia applies on a per-address basis (i.e. each e-mail address
-- is assigned its own unique policy record). Each domain also
-- has a set of policy defaults, and the system default policy
-- is stored as the '@.' user's policy record.
CREATE TABLE policy (
id SERIAL PRIMARY KEY,
policy_name VARCHAR(255),
virus_lover CHAR(1) DEFAULT 'Y',
spam_lover CHAR(1) DEFAULT 'Y',
banned_files_lover CHAR(1) DEFAULT 'Y',
bad_header_lover CHAR(1) DEFAULT 'Y',
bypass_virus_checks CHAR(1) DEFAULT 'Y',
bypass_spam_checks CHAR(1) DEFAULT 'Y',
bypass_banned_checks CHAR(1) DEFAULT 'Y',
bypass_header_checks CHAR(1) DEFAULT 'Y',
discard_viruses CHAR(1) DEFAULT 'N',
discard_spam CHAR(1) DEFAULT 'N',
discard_banned_files CHAR(1) DEFAULT 'N',
discard_bad_headers CHAR(1) DEFAULT 'N',
spam_modifies_subj CHAR(1) DEFAULT 'N',
spam_quarantine_to VARCHAR(64) DEFAULT NULL,
spam_tag_level NUMERIC(7,3) DEFAULT '999',
spam_tag2_level NUMERIC(7,3) DEFAULT '999',
spam_kill_level NUMERIC(7,3) DEFAULT '999'
);
-- [maia_config] contains Maia's configuration settings, as set
-- and modified by the super-administrator.
CREATE TABLE maia_config (
id INTEGER DEFAULT '0' PRIMARY KEY,
enable_user_autocreation CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
enable_false_negative_management CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
enable_stats_tracking CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
enable_virus_scanning CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
enable_spam_filtering CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
enable_banned_files_checking CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
enable_bad_header_checking CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
enable_charts CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
enable_spamtraps CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
enable_stats_reporting CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
enable_address_linking CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
enable_privacy_invasion CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
enable_username_changes CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
internal_auth CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
system_default_user_is_local CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
user_virus_scanning CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
user_spam_filtering CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
user_banned_files_checking CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
user_bad_header_checking CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
admin_email VARCHAR(255),
expiry_period INTEGER DEFAULT '30', -- days
ham_cache_expiry_period INTEGER DEFAULT '5', -- days
reminder_threshold_count INTEGER DEFAULT '100', -- items
reminder_threshold_size INTEGER DEFAULT '500000', -- bytes
reminder_template_file VARCHAR(255) DEFAULT 'reminder.tpl',
reminder_login_url VARCHAR(255),
newuser_template_file VARCHAR(255) DEFAULT 'newuser.tpl',
smtp_server VARCHAR(255) DEFAULT 'localhost',
smtp_port INTEGER DEFAULT '10025',
currency_label VARCHAR(15) DEFAULT '$',
bandwidth_cost NUMERIC(14,2) DEFAULT '0.0' NOT NULL,
chart_ham_colour VARCHAR(32) DEFAULT '#DDDDB7',
chart_spam_colour VARCHAR(32) DEFAULT '#FFAAAA',
chart_virus_colour VARCHAR(32) DEFAULT '#CCFFCC',
chart_fp_colour VARCHAR(32) DEFAULT '#C4CA73',
chart_fn_colour VARCHAR(32) DEFAULT '#FF7575',
chart_suspected_ham_colour VARCHAR(32) DEFAULT '#FFFFB7',
chart_suspected_spam_colour VARCHAR(32) DEFAULT '#FFCCCC',
chart_wl_colour VARCHAR(32) DEFAULT '#eeeeee',
chart_bl_colour VARCHAR(32) DEFAULT '#888888',
chart_background_colour VARCHAR(32) DEFAULT '#B0ECFF',
chart_font_colour VARCHAR(32) DEFAULT '#3D3D50',
chart_autogeneration_interval INTEGER DEFAULT '60', -- minutes
banner_title VARCHAR(255) DEFAULT 'Maia Mailguard',
use_icons CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
use_logo CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
logo_url VARCHAR(255) DEFAULT 'http://www.maiamailguard.com/',
logo_file VARCHAR(255) DEFAULT 'images/maia-logotoolbar.gif',
logo_alt_text VARCHAR(255) DEFAULT 'Maia Mailguard Home Page',
virus_info_url VARCHAR(255) DEFAULT 'http://www.google.com/search?q=%%VIRUSNAME%%+virus+information',
virus_lookup VARCHAR(20) DEFAULT 'google',
primary_report_server VARCHAR(255) DEFAULT 'maia.renaissoft.com',
primary_report_port INTEGER DEFAULT '443',
secondary_report_server VARCHAR(255),
secondary_report_port INTEGER DEFAULT '443',
reporter_sitename VARCHAR(255),
reporter_username VARCHAR(50),
reporter_password VARCHAR(50),
size_limit INTEGER DEFAULT '1000000',
oversize_policy CHAR(1) DEFAULT 'B' NOT NULL, -- 'P', 'B'
sa_score_set INTEGER DEFAULT '0' NOT NULL,
key_file VARCHAR(255) DEFAULT 'blowfish.key'
);
-- [maia_languages] contains a list of the installed languages
-- and their ISO-639 two-letter abbreviations.
CREATE TABLE maia_languages (
id SERIAL PRIMARY KEY,
language_name VARCHAR(100) NOT NULL, -- e.g. 'English'
abbreviation CHAR(2) NOT NULL UNIQUE, -- e.g. 'en'
installed CHAR(1) DEFAULT 'N' NOT NULL
);
CREATE UNIQUE INDEX maia_languages_idx_abbreviation ON maia_languages(abbreviation);
-- [maia_users] contains mail filter settings that apply to
-- e-mail recipients who have registered with Maia. The
-- user_level is stored as one of (U)ser, (A)dministrator,
-- or (S)uper-Administrator.
CREATE TABLE maia_users (
id SERIAL PRIMARY KEY,
user_name VARCHAR(255) NOT NULL UNIQUE,
user_level CHAR(1) DEFAULT 'U' NOT NULL, -- 'U', 'A', 'S'
reminders CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
charts CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
primary_email_id INTEGER DEFAULT '0' NOT NULL, -- users.id
language VARCHAR(10) DEFAULT 'en' NOT NULL,
charset VARCHAR(20) DEFAULT 'ISO-8859-1' NOT NULL,
spamtrap CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
password VARCHAR(32), -- 32-byte MD5 hash
auto_whitelist CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
items_per_page INTEGER DEFAULT '50' NOT NULL,
spam_quarantine_sort CHAR(2) DEFAULT 'XA' NOT NULL, -- [XDFS][AD]
virus_quarantine_sort CHAR(2) DEFAULT 'DA' NOT NULL, -- [DFS][AD]
header_quarantine_sort CHAR(2) DEFAULT 'DA' NOT NULL, -- [DFS][AD]
attachment_quarantine_sort CHAR(2) DEFAULT 'DA' NOT NULL, -- [DFS][AD]
ham_cache_sort CHAR(2) DEFAULT 'XD' NOT NULL, -- [XDFS][AD]
discard_ham CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
theme_id INTEGER DEFAULT '1' NOT NULL,
quarantine_digest_interval INTEGER DEFAULT '0' NOT NULL,
last_digest_sent TIMESTAMP,
truncate_subject INTEGER DEFAULT '20' NOT NULL,
truncate_email INTEGER DEFAULT '20' NOT NULL
);
CREATE UNIQUE INDEX maia_users_idx_user_name ON maia_users(user_name);
CREATE UNIQUE INDEX primary_email_idx ON maia_users(primary_email_id);
-- [maia_domains] contains mail filter settings that apply to
-- entire e-mail domains as defaults for users who are not
-- registered with Maia.
CREATE TABLE maia_domains (
id SERIAL PRIMARY KEY,
domain VARCHAR(255) NOT NULL UNIQUE,
reminders CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
charts CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
enable_user_autocreation CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
language VARCHAR(10) DEFAULT 'en' NOT NULL,
charset VARCHAR(20) DEFAULT 'ISO-8859-1' NOT NULL,
routing_domain VARCHAR(255) DEFAULT '' NOT NULL,
transport VARCHAR(255) DEFAULT ':' NOT NULL
);
CREATE UNIQUE INDEX maia_domains_idx_domain ON maia_domains(domain);
CREATE INDEX maia_domains_idx_routing_domain ON maia_domains(routing_domain);
-- [maia_domain_admins] is a one-to-many mapping of domains to
-- users with administrator privileges (domains can have
-- multiple administrators).
CREATE TABLE maia_domain_admins (
domain_id INTEGER NOT NULL, -- maia_domains.id
admin_id INTEGER NOT NULL, -- maia_users.id
PRIMARY KEY (domain_id, admin_id)
);
-- [maia_stats] is a table of per-user statistics that keeps
-- track of the total number of mail items of each type,
-- along with total sizes and superlatives (e.g. largest,
-- smallest, oldest, newest, etc.).
CREATE TABLE maia_stats (
user_id INTEGER DEFAULT '0' NOT NULL, -- maia_users.id
-- suspected ham
oldest_suspected_ham_date TIMESTAMP,
newest_suspected_ham_date TIMESTAMP,
smallest_suspected_ham_size INTEGER DEFAULT '0' NOT NULL,
largest_suspected_ham_size INTEGER DEFAULT '0' NOT NULL,
total_suspected_ham_size BIGINT DEFAULT '0' NOT NULL,
lowest_suspected_ham_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
highest_suspected_ham_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
total_suspected_ham_score NUMERIC(14,3) DEFAULT '0.0' NOT NULL,
total_suspected_ham_items INTEGER DEFAULT '0' NOT NULL,
-- ham
oldest_ham_date TIMESTAMP,
newest_ham_date TIMESTAMP,
smallest_ham_size INTEGER DEFAULT '0' NOT NULL,
largest_ham_size INTEGER DEFAULT '0' NOT NULL,
total_ham_size BIGINT DEFAULT '0' NOT NULL,
lowest_ham_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
highest_ham_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
total_ham_score NUMERIC(14,3) DEFAULT '0.0' NOT NULL,
total_ham_items INTEGER DEFAULT '0' NOT NULL,
-- mail from whitelisted senders
oldest_wl_date TIMESTAMP,
newest_wl_date TIMESTAMP,
smallest_wl_size INTEGER DEFAULT '0' NOT NULL,
largest_wl_size INTEGER DEFAULT '0' NOT NULL,
total_wl_size BIGINT DEFAULT '0' NOT NULL,
total_wl_items INTEGER DEFAULT '0' NOT NULL,
-- mail from blacklisted senders
oldest_bl_date TIMESTAMP,
newest_bl_date TIMESTAMP,
smallest_bl_size INTEGER DEFAULT '0' NOT NULL,
largest_bl_size INTEGER DEFAULT '0' NOT NULL,
total_bl_size BIGINT DEFAULT '0' NOT NULL,
total_bl_items INTEGER DEFAULT '0' NOT NULL,
-- suspected spam
oldest_suspected_spam_date TIMESTAMP,
newest_suspected_spam_date TIMESTAMP,
smallest_suspected_spam_size INTEGER DEFAULT '0' NOT NULL,
largest_suspected_spam_size INTEGER DEFAULT '0' NOT NULL,
total_suspected_spam_size BIGINT DEFAULT '0' NOT NULL,
lowest_suspected_spam_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
highest_suspected_spam_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
total_suspected_spam_score NUMERIC(14,3) DEFAULT '0.0' NOT NULL,
total_suspected_spam_items INTEGER DEFAULT '0' NOT NULL,
-- false positives (i.e. rescued ham)
oldest_fp_date TIMESTAMP,
newest_fp_date TIMESTAMP,
smallest_fp_size INTEGER DEFAULT '0' NOT NULL,
largest_fp_size INTEGER DEFAULT '0' NOT NULL,
total_fp_size BIGINT DEFAULT '0' NOT NULL,
lowest_fp_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
highest_fp_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
total_fp_score NUMERIC(14,3) DEFAULT '0.0' NOT NULL,
total_fp_items INTEGER DEFAULT '0' NOT NULL,
-- false negatives (i.e. reported spam)
oldest_fn_date TIMESTAMP,
newest_fn_date TIMESTAMP,
smallest_fn_size INTEGER DEFAULT '0' NOT NULL,
largest_fn_size INTEGER DEFAULT '0' NOT NULL,
total_fn_size BIGINT DEFAULT '0' NOT NULL,
lowest_fn_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
highest_fn_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
total_fn_score NUMERIC(14,3) DEFAULT '0.0' NOT NULL,
total_fn_items INTEGER DEFAULT '0' NOT NULL,
-- confirmed spam
oldest_spam_date TIMESTAMP,
newest_spam_date TIMESTAMP,
smallest_spam_size INTEGER DEFAULT '0' NOT NULL,
largest_spam_size INTEGER DEFAULT '0' NOT NULL,
total_spam_size BIGINT DEFAULT '0' NOT NULL,
lowest_spam_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
highest_spam_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
total_spam_score NUMERIC(14,3) DEFAULT '0.0' NOT NULL,
total_spam_items INTEGER DEFAULT '0' NOT NULL,
-- viruses
oldest_virus_date TIMESTAMP,
newest_virus_date TIMESTAMP,
smallest_virus_size INTEGER DEFAULT '0' NOT NULL,
largest_virus_size INTEGER DEFAULT '0' NOT NULL,
total_virus_size BIGINT DEFAULT '0' NOT NULL,
total_virus_items INTEGER DEFAULT '0' NOT NULL,
-- mail with invalid headers
oldest_bad_header_date TIMESTAMP,
newest_bad_header_date TIMESTAMP,
smallest_bad_header_size INTEGER DEFAULT '0' NOT NULL,
largest_bad_header_size INTEGER DEFAULT '0' NOT NULL,
total_bad_header_size BIGINT DEFAULT '0' NOT NULL,
total_bad_header_items INTEGER DEFAULT '0' NOT NULL,
-- mail containing banned file attachments
oldest_banned_file_date TIMESTAMP,
newest_banned_file_date TIMESTAMP,
smallest_banned_file_size INTEGER DEFAULT '0' NOT NULL,
largest_banned_file_size INTEGER DEFAULT '0' NOT NULL,
total_banned_file_size BIGINT DEFAULT '0' NOT NULL,
total_banned_file_items INTEGER DEFAULT '0' NOT NULL,
-- oversized items
oldest_oversized_date TIMESTAMP,
newest_oversized_date TIMESTAMP,
smallest_oversized_size INTEGER DEFAULT '0' NOT NULL,
largest_oversized_size INTEGER DEFAULT '0' NOT NULL,
total_oversized_size BIGINT DEFAULT '0' NOT NULL,
total_oversized_items INTEGER DEFAULT '0' NOT NULL,
PRIMARY KEY (user_id)
);
-- [maia_mail] stores mail items of five types:
-- Suspected (S)pam, (V)iruses, Banned (F)ile Attachments,
-- (B)ad Headers, and Suspected (H)am.
CREATE TABLE maia_mail (
id SERIAL PRIMARY KEY,
received_date TIMESTAMP NOT NULL,
size INTEGER NOT NULL,
sender_email BYTEA NOT NULL,
envelope_to BYTEA NOT NULL,
subject VARCHAR(255) NOT NULL,
contents BYTEA NOT NULL,
score NUMERIC(7,3), -- only supplied for (S)pam
autolearn_status VARCHAR(15) DEFAULT 'unavailable' NOT NULL -- 'ham', 'spam', 'no', 'disabled', 'failed', 'unavailable'
);
CREATE INDEX maia_mail_idx_received_date ON maia_mail(received_date);
CREATE INDEX maia_mail_idx_sender_email ON maia_mail(sender_email);
CREATE INDEX maia_mail_idx_subject ON maia_mail(subject);
CREATE INDEX maia_mail_idx_score ON maia_mail(score);
-- [maia_mail_recipients] is a one-to-many mapping of
-- mail items to recipients. These records
-- are deleted when a recipient rescues an item, or the
-- item is deleted.
CREATE TABLE maia_mail_recipients (
mail_id INTEGER NOT NULL, -- maia_mail.id
recipient_id INTEGER NOT NULL, -- maia_users.id
type CHAR(1) NOT NULL, -- 'S', 'V', 'F', 'B', 'H', 'L', 'W'
token CHAR(64) NOT NULL,
PRIMARY KEY (mail_id, recipient_id)
);
CREATE UNIQUE INDEX token_idx ON maia_mail_recipients(token);
CREATE INDEX maia_mail_recipients_idx_type ON maia_mail_recipients(type);
CREATE INDEX maia_mail_recipients_idx_mail_id ON maia_mail_recipients(mail_id);
CREATE INDEX maia_mail_recipients_idx_recipient_id ON maia_mail_recipients(recipient_id);
-- [maia_viruses] contains a list of the "official" names of
-- viruses that have been detected by the virus scanners.
-- These are the names that will be displayed in stats
-- tables and charts.
CREATE TABLE maia_viruses (
id SERIAL PRIMARY KEY,
virus_name VARCHAR(255) NOT NULL UNIQUE,
count INTEGER DEFAULT '0' NOT NULL
);
CREATE UNIQUE INDEX maia_viruses_idx_virus_name ON maia_viruses(virus_name);
-- [maia_virus_aliases] is a one-to-many mapping of viruses
-- to aliases for those viruses, as detected by other
-- virus scanners.
CREATE TABLE maia_virus_aliases (
virus_id INTEGER NOT NULL, -- maia_viruses.id
virus_alias VARCHAR(255) NOT NULL,
PRIMARY KEY (virus_id, virus_alias)
);
-- [maia_viruses_detected] is a one-to-many mapping of
-- mail items to viruses found. These
-- entries are deleted when a mail item is rescued
-- or deleted.
CREATE TABLE maia_viruses_detected (
mail_id INTEGER NOT NULL, -- maia_mail.id
virus_id INTEGER NOT NULL, -- maia_viruses.id
PRIMARY KEY (mail_id, virus_id)
);
-- [maia_sa_rules] contains a list of all the SpamAssassin
-- rules installed on the system, along with their
-- text descriptions and score values.
CREATE TABLE maia_sa_rules (
id SERIAL PRIMARY KEY,
rule_name VARCHAR(255) NOT NULL UNIQUE,
rule_description VARCHAR(255) DEFAULT '' NOT NULL,
rule_score_0 NUMERIC(7,3) DEFAULT '1.0' NOT NULL,
rule_score_1 NUMERIC(7,3) DEFAULT '1.0' NOT NULL,
rule_score_2 NUMERIC(7,3) DEFAULT '1.0' NOT NULL,
rule_score_3 NUMERIC(7,3) DEFAULT '1.0' NOT NULL,
rule_count INTEGER DEFAULT '0' NOT NULL
);
CREATE UNIQUE INDEX maia_sa_rules_idx_rule_name ON maia_sa_rules(rule_name);
-- [maia_sa_rules_triggered] is a one-to-many mapping of
-- mail items to SpamAssassin rules triggered
-- by that mail item. These entries are deleted when a
-- mail item is rescued or deleted.
CREATE TABLE maia_sa_rules_triggered (
mail_id INTEGER NOT NULL, -- maia_mail.id
rule_id INTEGER NOT NULL, -- maia_sa_rules.id
rule_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
PRIMARY KEY (mail_id, rule_id)
);
-- [maia_banned_attachments_found] is a one-to-many mapping of
-- mail items to file attachments that were found in that
-- mail item. These entries are deleted when a mail item
-- is rescued or deleted.
CREATE TABLE maia_banned_attachments_found (
mail_id INTEGER NOT NULL, -- maia_mail.id
file_name VARCHAR(255) NOT NULL,
file_type VARCHAR(20) DEFAULT 'Unknown' NOT NULL,
PRIMARY KEY (mail_id, file_name)
);
-- [maia_stats_history] is a table used to store snapshots of the
-- more relevant items from the [maia_stats] table at (H)ourly,
-- (D)aily, (M)onthly, and (Y)early intervals. Entries are
-- automatically expired, so this table has a more or less
-- fixed size of ((24 + 31 + 12 + 1/year) * users) rows.
--
CREATE TABLE maia_stats_history (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL, -- maia_users.id
type CHAR(1) DEFAULT 'H' NOT NULL, -- 'H', 'D', 'M', 'Y'
taken_at TIMESTAMP NOT NULL,
total_ham_items INTEGER DEFAULT '0' NOT NULL,
total_ham_size BIGINT DEFAULT '0' NOT NULL,
total_spam_items INTEGER DEFAULT '0' NOT NULL,
total_spam_size BIGINT DEFAULT '0' NOT NULL,
total_virus_items INTEGER DEFAULT '0' NOT NULL,
total_virus_size BIGINT DEFAULT '0' NOT NULL,
total_fp_items INTEGER DEFAULT '0' NOT NULL,
total_fp_size BIGINT DEFAULT '0' NOT NULL,
total_fn_items INTEGER DEFAULT '0' NOT NULL,
total_fn_size BIGINT DEFAULT '0' NOT NULL,
total_banned_file_items INTEGER DEFAULT '0' NOT NULL,
total_banned_file_size BIGINT DEFAULT '0' NOT NULL,
total_bad_header_items INTEGER DEFAULT '0' NOT NULL,
total_bad_header_size BIGINT DEFAULT '0' NOT NULL,
total_wl_items INTEGER DEFAULT '0' NOT NULL,
total_wl_size BIGINT DEFAULT '0' NOT NULL,
total_bl_items INTEGER DEFAULT '0' NOT NULL,
total_bl_size BIGINT DEFAULT '0' NOT NULL,
total_oversized_items INTEGER DEFAULT '0' NOT NULL,
total_oversized_size BIGINT DEFAULT '0' NOT NULL
);
-- [maia_themes] stores information about each of the installed
-- user-selectable themes.
--
CREATE TABLE maia_themes (
id SERIAL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
path VARCHAR(30) NOT NULL
);
-- maia_tokens stores temporary MD5 authentication tokens for various systems.
CREATE TABLE maia_tokens (
id SERIAL PRIMARY KEY,
token_system VARCHAR(32) NOT NULL ,
token CHAR(64) NOT NULL ,
data bytea NOT NULL ,
expires TIMESTAMP NOT NULL
);
CREATE UNIQUE INDEX token_system_key ON maia_tokens (token,token_system);
CREATE INDEX token_system ON maia_tokens ( token_system );
CREATE INDEX expires ON maia_tokens ( expires );
-- schema_info contains the schema version status of the current database. Software upgrades will use this value
-- to upgrade the database appropriately. Note: this table is constructed to adhere to the pattern set by
-- RubyOnRails' Migrations, for future compatability
CREATE TABLE schema_info (
version INTEGER
);
INSERT INTO schema_info VALUES(15);
-- [awl] is SpamAssassin 3.x's Auto-WhiteList database
--
CREATE TABLE awl (
username VARCHAR(100) NOT NULL DEFAULT '',
email BYTEA NOT NULL DEFAULT '',
ip VARCHAR(40) NOT NULL DEFAULT '',
count BIGINT NOT NULL DEFAULT '0',
totscore FLOAT NOT NULL DEFAULT '0',
signedby varchar(255) NOT NULL default '',
lastupdate timestamp NOT NULL,
PRIMARY KEY (username, email, signedby, ip)
);
CREATE FUNCTION awl_stamp() RETURNS trigger AS $awl_stamp$
BEGIN
NEW.lastupdate := current_timestamp;
RETURN NEW;
END;
$awl_stamp$ LANGUAGE plpgsql;
CREATE TRIGGER awl_stamp BEFORE INSERT OR UPDATE ON awl
FOR EACH ROW EXECUTE PROCEDURE awl_stamp();
-- [bayes_expire] is SpamAssassin 3.x's Bayes database expiry reference
--
CREATE TABLE bayes_expire (
id INTEGER NOT NULL DEFAULT '0',
runtime INTEGER NOT NULL DEFAULT '0'
) WITHOUT OIDS;
CREATE INDEX bayes_expire_idx1 ON bayes_expire (id);
-- [bayes_global_vars] is SpamAssassin 3.x's Bayes database global variable list
--
CREATE TABLE bayes_global_vars (
variable VARCHAR(30) NOT NULL DEFAULT '',
value VARCHAR(200) NOT NULL DEFAULT '',
PRIMARY KEY (variable)
) WITHOUT OIDS;
-- [bayes_seen] is SpamAssassin 3.x's Bayes database token reference
--
CREATE TABLE bayes_seen (
id INTEGER NOT NULL DEFAULT '0',
msgid VARCHAR(200) NOT NULL DEFAULT '',
flag CHAR(1) NOT NULL DEFAULT '',
PRIMARY KEY (id, msgid)
) WITHOUT OIDS;
-- [bayes_token] is SpamAssassin 3.x's Bayes database token list
--
CREATE TABLE bayes_token (
id INTEGER NOT NULL DEFAULT '0',
token BYTEA NOT NULL DEFAULT '',
spam_count INTEGER NOT NULL DEFAULT '0',
ham_count INTEGER NOT NULL DEFAULT '0',
atime INTEGER NOT NULL DEFAULT '0',
PRIMARY KEY (id, token)
) WITHOUT OIDS;
CREATE INDEX bayes_token_idx1 ON bayes_token (token);
-- [bayes_vars] is SpamAssassin 3.x's Bayes database variable scorecard
--
CREATE TABLE bayes_vars (
id SERIAL NOT NULL,
username VARCHAR(200) NOT NULL DEFAULT '',
spam_count INTEGER NOT NULL DEFAULT '0',
ham_count INTEGER NOT NULL DEFAULT '0',
token_count INTEGER NOT NULL DEFAULT '0',
last_expire INTEGER NOT NULL DEFAULT '0',
last_atime_delta INTEGER NOT NULL DEFAULT '0',
last_expire_reduce INTEGER NOT NULL DEFAULT '0',
oldest_token_age INTEGER NOT NULL DEFAULT '2147483647',
newest_token_age INTEGER NOT NULL DEFAULT '0',
PRIMARY KEY (id)
) WITHOUT OIDS;
CREATE UNIQUE INDEX bayes_vars_idx1 ON bayes_vars (username);
CREATE OR REPLACE FUNCTION greatest_int (integer, integer)
RETURNS INTEGER
IMMUTABLE STRICT
AS 'SELECT CASE WHEN $1 < $2 THEN $2 ELSE $1 END;'
LANGUAGE SQL;
CREATE OR REPLACE FUNCTION least_int (integer, integer)
RETURNS INTEGER
IMMUTABLE STRICT
AS 'SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END;'
LANGUAGE SQL;
CREATE OR REPLACE FUNCTION put_tokens(INTEGER,
BYTEA[],
INTEGER,
INTEGER,
INTEGER)
RETURNS VOID AS '
DECLARE
inuserid ALIAS FOR $1;
intokenary ALIAS FOR $2;
inspam_count ALIAS FOR $3;
inham_count ALIAS FOR $4;
inatime ALIAS FOR $5;
_token BYTEA;
new_tokens INTEGER := 0;
BEGIN
for i in array_lower(intokenary, 1) .. array_upper(intokenary, 1)
LOOP
_token := intokenary[i];
UPDATE bayes_token
SET spam_count = greatest_int(spam_count + inspam_count, 0),
ham_count = greatest_int(ham_count + inham_count, 0),
atime = greatest_int(atime, inatime)
WHERE id = inuserid
AND token = _token;
IF NOT FOUND THEN
-- we do not insert negative counts, just return true
IF NOT (inspam_count < 0 OR inham_count < 0) THEN
INSERT INTO bayes_token (id, token, spam_count, ham_count, atime)
VALUES (inuserid, _token, inspam_count, inham_count, inatime);
IF FOUND THEN
new_tokens := new_tokens + 1;
END IF;
END IF;
END IF;
END LOOP;
IF new_tokens > 0 AND inatime > 0 THEN
UPDATE bayes_vars
SET token_count = token_count + new_tokens,
newest_token_age = greatest_int(newest_token_age, inatime),
oldest_token_age = least_int(oldest_token_age, inatime)
WHERE id = inuserid;
ELSIF new_tokens > 0 AND NOT inatime > 0 THEN
UPDATE bayes_vars
SET token_count = token_count + new_tokens
WHERE id = inuserid;
ELSIF NOT new_tokens > 0 AND inatime > 0 THEN
UPDATE bayes_vars
SET newest_token_age = greatest_int(newest_token_age, inatime),
oldest_token_age = least_int(oldest_token_age, inatime)
WHERE id = inuserid;
END IF;
RETURN;
END;
' LANGUAGE 'plpgsql';
-- Database initialization script
-- Create a permissive system default policy and create the '@.' domain
INSERT INTO policy VALUES (DEFAULT, 'Default', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', 'N', NULL, 999, 999, 999);
INSERT INTO users VALUES (DEFAULT, 0, 1, '@.', 1, 1);
INSERT INTO maia_users (id, user_name, primary_email_id, reminders, discard_ham) VALUES (DEFAULT, '@.', 1, 'N', 'Y');
INSERT INTO maia_domains (id, domain, routing_domain) VALUES (DEFAULT, '@.', '*');
-- Instantiate a default system configuration
INSERT INTO maia_config (id) VALUES (0);
-- Load the ISO-639 language names and abbreviations
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('aa', 'Afar');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ab', 'Abkhazian');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('af', 'Afrikaans');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('am', 'Amharic');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ar', 'ﺔﻴﺐﺮﻌﻠﺍ ');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('as', 'Assamese');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ay', 'Aymara');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('az', 'Azцri');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ba', 'Bashkir');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('be', 'Беларуская');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('bg', 'Български');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('bh', 'Bihari');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('bi', 'Bislama');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('bn', 'Bengali');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('bo', 'Tibetan');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('br', 'Brezhoneg');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ca', 'Català');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('co', 'Corsican');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('cs', 'Četina');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('cy', 'Cymraeg');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('da', 'Dansk');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('de', 'Deutsch');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('dz', 'Bhutani');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('el', 'Ελληνικά');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('en', 'English');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('eo', 'Esperanto');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('es', 'Español');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('et', 'Eesti');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('eu', 'Euskaraz');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('fa', 'Persian');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('fi', 'Suomeksi');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('fj', 'Fiji');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('fo', 'Føroyskt');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('fr', 'Français');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('fy', 'Frysk');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ga', 'Gaeilge');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('gd', 'Gàidhlig');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('gl', 'Galician');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('gn', 'Guarani');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('gu', 'Gujarati');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('gv', 'Ghaelg');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ha', 'Hausa');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('he', 'עברית');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('hi', 'Hindi');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('hr', 'Hrvatski');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('hu', 'Magyar');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('hy', 'Armenian');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ia', 'Interlingua');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('id', 'Bahasa Indonesia');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ie', 'Interlingue');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ik', 'Inupiak');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('is', 'Íslenska');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('it', 'Italiano');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('iu', 'Inuktitut');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ja', 'Nihongo');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('jw', 'Javanese');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ka', 'Georgian');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('kk', 'Kazakh');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('kl', 'Kalaallísut');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('km', 'Cambodian');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('kn', 'Kannada');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ko', 'Korean');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ks', 'Kashmiri');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ku', 'Kurmancî (Kurdî)');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ky', 'Kirghiz');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('la', 'Latina');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('lb', 'Lëtzebuergesch');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ln', 'Lingala');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('lo', 'Laothian');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('lt', 'Lietuvių');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('lv', 'Latvieu');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mg', 'Malagasy');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mi', 'Maori');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mk', 'Македонски');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ml', 'Malayalam');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mn', 'Mongolian');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mo', 'Moldavian');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mr', 'Marathi');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ms', 'Malay');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mt', 'Malti');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('my', 'Burmese');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('na', 'Nauru');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ne', 'Nepali');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('nl', 'Nederlands');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('no', 'Norsk');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('oc', 'Occitan');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('om', 'Oromo');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('or', 'Oriya');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('pa', 'Punjabi');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('pl', 'Polski');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ps', 'Pashto');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('pt', 'Português');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('qu', 'Quechua');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('rm', 'Rumantsch');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('rn', 'Kirundi');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ro', 'Română');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ru', 'Русский');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('rw', 'Kinyarwanda');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sa', 'Sanskrit');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sd', 'Sindhi');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('se', 'Davvisámegiella');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sg', 'Sangho');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sh', 'Serbo-Croatian');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('si', 'Sinhalese');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sk', 'Slovenčina');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sl', 'Slovenski');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sm', 'Samoan');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sn', 'Shona');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('so', 'af Soomaali');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sq', 'Shqip');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sr', 'Српски');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ss', 'Siswati');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('st', 'Sesotho');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('su', 'Sundanese');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sv', 'Svenska');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sw', 'Kiswahili');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ta', 'Tamil');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('te', 'Telugu');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tg', 'Tajik');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('th', 'Thai');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ti', 'Tigrinya');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tk', 'Turkmen');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tl', 'Tagalog');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tn', 'Setswana');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('to', 'Tonga');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tr', 'Türkçe');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ts', 'Tsonga');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tt', 'Tatar');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tw', 'Twi');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ug', 'Uighur');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('uk', 'Украïнська');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ur', 'Urdu');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('uz', 'Uzbek');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('vi', 'Tiếng Việt');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('vo', 'Volapuk');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('wo', 'Wolof');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('xh', 'Xhosa');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('yi', 'Jiddiš');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('yo', 'Yoruba');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('za', 'Zhuang');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('zh', 'Zhōng-wén');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('zu', 'Zulu');
-- Mark the default language (English) as "installed"
UPDATE maia_languages SET installed = 'Y' WHERE abbreviation = 'en';
-- Insert default themes
INSERT INTO maia_themes VALUES (1, 'Ocean Surf', 'ocean_surf');
INSERT INTO maia_themes VALUES (2, 'Desert Sand', 'desert_sand');
-- delaying this theme till 1.1
--INSERT INTO maia_themes VALUES (3, 'DGM', 'dgm');
-- Set SpamAssassin global variables
INSERT INTO bayes_global_vars VALUES ('VERSION', '3');
-- End of database initialization script