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
|
CREATE TABLE "user" (
"id_user" INTEGER PRIMARY KEY AUTOINCREMENT,
"username" VARCHAR(12) NOT NULL UNIQUE,
"realname" VARCHAR(100) NOT NULL,
"password" VARCHAR(255) NOT NULL,
"phone_number" VARCHAR(15) NOT NULL UNIQUE,
"level" TEXT NOT NULL DEFAULT 'user',
CHECK ("level" IN ('admin','user'))
);
INSERT INTO "user" VALUES(1, 'kalkun', 'Kalkun SMS', '$2y$10$sIXe0JiaTIOsC7OOnox5t.deuJwZoawd5QKpQlSNfywziTDHpmmyy', '+123456', 'admin');
CREATE TABLE "user_settings" (
"id_user" INTEGER PRIMARY KEY NOT NULL,
"theme" VARCHAR(10) NOT NULL DEFAULT 'blue',
"signature" VARCHAR(50) NOT NULL,
"permanent_delete" TEXT NOT NULL DEFAULT 'false',
"paging" INTEGER NOT NULL DEFAULT 10,
"bg_image" VARCHAR(50) NOT NULL,
"delivery_report" TEXT NOT NULL DEFAULT 'default',
"language" VARCHAR(20) NOT NULL DEFAULT 'english',
"conversation_sort" TEXT NOT NULL DEFAULT 'asc',
"country_code" VARCHAR(2) NOT NULL DEFAULT 'US',
CHECK ("permanent_delete" IN ('true','false')),
CHECK ("delivery_report" IN ('default','yes','no')),
CHECK ("conversation_sort" IN ('asc','desc'))
);
INSERT INTO "user_settings" VALUES (1, 'green', 'false;--
Put your signature here', 'false', 20, 'true;background.jpg', 'default', 'english', 'asc', 'US');
CREATE TABLE "user_outbox" (
"id_outbox" INTEGER PRIMARY KEY NOT NULL,
"id_user" INTEGER NOT NULL
);
CREATE TABLE "user_inbox" (
"id_inbox" INTEGER PRIMARY KEY NOT NULL,
"id_user" INTEGER NOT NULL,
"trash" BOOL NOT NULL DEFAULT 0
);
CREATE TABLE "user_sentitems" (
"id_sentitems" INTEGER PRIMARY KEY NOT NULL,
"id_user" INTEGER NOT NULL,
"trash" BOOL NOT NULL DEFAULT 0
);
CREATE TABLE "user_folders" (
"id_folder" INTEGER PRIMARY KEY AUTOINCREMENT,
"name" VARCHAR(50) NOT NULL,
"id_user" INTEGER NOT NULL
);
INSERT INTO "user_folders" VALUES(1, 'inbox', 0);
INSERT INTO "user_folders" VALUES(2, 'outbox', 0);
INSERT INTO "user_folders" VALUES(3, 'sent_items', 0);
INSERT INTO "user_folders" VALUES(4, 'draft', 0);
INSERT INTO "user_folders" VALUES(5, 'Trash', 0);
INSERT INTO "user_folders" VALUES(6, 'Spam', 0);
INSERT INTO "user_folders" VALUES(10, 'Reserved', 0);
CREATE TABLE "sms_used" (
"id_sms_used" INTEGER PRIMARY KEY AUTOINCREMENT,
"sms_date" DATE NOT NULL,
"id_user" INTEGER NOT NULL,
"out_sms_count" INTEGER NOT NULL DEFAULT 0,
"in_sms_count" INTEGER NOT NULL DEFAULT 0
);
ALTER TABLE "inbox" ADD COLUMN "id_folder" INTEGER NOT NULL DEFAULT 1;
ALTER TABLE "inbox" ADD COLUMN "readed" TEXT NOT NULL DEFAULT 'false';
ALTER TABLE "sentitems" ADD COLUMN "id_folder" INTEGER NOT NULL DEFAULT 3;
CREATE TABLE "user_group" (
"id_group" INTEGER PRIMARY KEY AUTOINCREMENT,
"id_pbk" INTEGER NOT NULL,
"id_pbk_groups" INTEGER NOT NULL,
"id_user" INTEGER NOT NULL
);
CREATE TABLE "kalkun" (
"version" TEXT NOT NULL
);
CREATE TABLE "user_templates" (
"id_template" INTEGER PRIMARY KEY AUTOINCREMENT,
"id_user" INTEGER NOT NULL,
"Name" VARCHAR(64) NOT NULL,
"Message" TEXT NOT NULL
);
-- --------------------------------------------------------
--
-- Table structure for table `b8_wordlist` v3 (for b8 >= v0.6)
--
create table "b8_wordlist" (
"token" varchar(255) PRIMARY KEY,
"count_ham" bigint default null,
"count_spam" bigint default null
);
insert into b8_wordlist (token, count_ham) values ('b8*dbversion', 3);
insert into b8_wordlist (token, count_ham, count_spam) values ('b8*texts', 0, 0);
CREATE TABLE "plugins" (
"plugin_id" INTEGER PRIMARY KEY AUTOINCREMENT,
"system_name" VARCHAR(255) NOT NULL UNIQUE,
"name" VARCHAR(255) NOT NULL,
"status" TINYINT(1) NOT NULL DEFAULT '1',
"uri" VARCHAR(120) DEFAULT NULL,
"version" VARCHAR(30) NOT NULL,
"description" TEXT,
"author" VARCHAR(120) DEFAULT NULL,
"author_uri" VARCHAR(120) DEFAULT NULL,
"data" TEXT
);
CREATE TABLE "user_forgot_password" (
"id_user" INTEGER PRIMARY KEY,
"token" VARCHAR(255) NOT NULL,
"valid_until" NUMERIC NOT NULL
);
CREATE TABLE "user_filters" (
"id_filter" INTEGER PRIMARY KEY AUTOINCREMENT,
"id_user" INTEGER NOT NULL,
"from" VARCHAR(15) NOT NULL,
"has_the_words" VARCHAR(50) NOT NULL,
"id_folder" INTEGER NOT NULL
);
|
