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
|
# -*- coding: utf-8 -*-
CREATES = {
"sqlite": [ """
CREATE TABLE "list" (
name VARCHAR(255) NOT NULL,
display_name TEXT,
PRIMARY KEY (name)
);""", """
CREATE TABLE "thread" (
list_name VARCHAR(255) NOT NULL,
thread_id VARCHAR(255) NOT NULL,
date_active DATETIME NOT NULL,
PRIMARY KEY (list_name, thread_id)
);""", """
CREATE TABLE "email" (
list_name VARCHAR(255) NOT NULL,
message_id VARCHAR(255) NOT NULL,
sender_name VARCHAR(255) NOT NULL,
sender_email VARCHAR(255) NOT NULL,
subject TEXT NOT NULL,
content TEXT NOT NULL,
date DATETIME NOT NULL,
in_reply_to VARCHAR(255), -- How about replies from another list ?
message_id_hash VARCHAR(255) NOT NULL,
thread_id VARCHAR(255) NOT NULL,
"full" BLOB NOT NULL,
archived_date DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (list_name, message_id),
FOREIGN KEY (list_name, thread_id)
REFERENCES thread(list_name, thread_id) ON DELETE CASCADE
);""", """
CREATE TABLE "attachment" (
list_name VARCHAR(255) NOT NULL,
message_id VARCHAR(255) NOT NULL,
counter INTEGER NOT NULL,
content_type VARCHAR(255) NOT NULL,
encoding VARCHAR(50),
name VARCHAR(255),
size INTEGER NOT NULL,
content BLOB NOT NULL,
PRIMARY KEY (list_name, message_id, counter),
FOREIGN KEY (list_name, message_id)
REFERENCES email(list_name, message_id) ON DELETE CASCADE
);""",
'CREATE INDEX "ix_email_list_name" ON "email" (list_name);',
'CREATE INDEX "ix_email_date" ON "email" (date);',
'CREATE UNIQUE INDEX "ix_email_list_name_message_id_hash" ON "email" (list_name, message_id_hash);',
'CREATE INDEX "ix_email_subject" ON "email" (subject);',
'CREATE INDEX "ix_email_thread_id" ON "email" (thread_id);',
'CREATE INDEX "ix_thread_date_active" ON "thread" (date_active);',
],
"postgres": [ """
CREATE TABLE "list" (
name VARCHAR(255) NOT NULL,
display_name TEXT,
PRIMARY KEY (name)
);""", """
CREATE TABLE "thread" (
list_name VARCHAR(255) NOT NULL,
thread_id VARCHAR(255) NOT NULL,
date_active TIMESTAMP WITH TIME ZONE NOT NULL,
PRIMARY KEY (list_name, thread_id)
);""", """
CREATE TABLE "email" (
list_name VARCHAR(255) NOT NULL,
message_id VARCHAR(255) NOT NULL,
sender_name VARCHAR(255) NOT NULL,
sender_email VARCHAR(255) NOT NULL,
subject TEXT NOT NULL,
content TEXT NOT NULL,
date TIMESTAMP WITH TIME ZONE NOT NULL,
in_reply_to VARCHAR(255), -- How about replies from another list ?
message_id_hash VARCHAR(255) NOT NULL,
thread_id VARCHAR(255) NOT NULL,
"full" BYTEA NOT NULL,
archived_date TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (list_name, message_id),
FOREIGN KEY (list_name, thread_id)
REFERENCES thread(list_name, thread_id) ON DELETE CASCADE
);""", """
CREATE TABLE "attachment" (
list_name VARCHAR(255) NOT NULL,
message_id VARCHAR(255) NOT NULL,
counter INTEGER NOT NULL,
content_type VARCHAR(255) NOT NULL,
encoding VARCHAR(50),
name VARCHAR(255),
size INTEGER NOT NULL,
content BYTEA NOT NULL,
PRIMARY KEY (list_name, message_id, counter),
FOREIGN KEY (list_name, message_id)
REFERENCES email(list_name, message_id) ON DELETE CASCADE
);""",
'CREATE INDEX "ix_email_list_name" ON "email" USING btree (list_name);',
'CREATE INDEX "ix_email_date" ON "email" USING btree (date);',
'CREATE UNIQUE INDEX "ix_email_list_name_message_id_hash" ON "email" USING btree (list_name, message_id_hash);',
'CREATE INDEX "ix_email_subject" ON "email" USING btree (subject);',
'CREATE INDEX "ix_email_thread_id" ON "email" USING btree (thread_id);',
'CREATE INDEX "ix_thread_date_active" ON "thread" USING btree (date_active);',
],
}
def get_db_type(store):
database = store.get_database()
return database.__class__.__module__.split(".")[-1]
|
