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
|
CREATE SEQUENCE "user_id_user_seq" START 2;
CREATE TABLE "user" (
"id_user" integer PRIMARY KEY DEFAULT nextval('user_id_user_seq'),
"username" varchar(12) NOT NULL,
"realname" varchar(100) NOT NULL,
"password" varchar(255) NOT NULL,
"phone_number" varchar(15) NOT NULL,
"level" text NOT NULL DEFAULT 'user',
UNIQUE("phone_number", "username"),
CHECK ("level" IN ('admin','user'))
);
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" VALUES(1, 'kalkun', 'Kalkun SMS', '$2y$10$sIXe0JiaTIOsC7OOnox5t.deuJwZoawd5QKpQlSNfywziTDHpmmyy', '+123456', 'admin');
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,
"id_user" integer NOT NULL
);
CREATE TABLE "user_inbox" (
"id_inbox" integer PRIMARY KEY,
"id_user" integer NOT NULL,
"trash" smallint NOT NULL DEFAULT 0
);
CREATE TABLE "user_sentitems" (
"id_sentitems" integer PRIMARY KEY,
"id_user" integer NOT NULL,
"trash" smallint NOT NULL DEFAULT 0
);
CREATE SEQUENCE "user_folders_id_folder_seq" START 11;
CREATE TABLE "user_folders" (
"id_folder" integer PRIMARY KEY DEFAULT nextval('user_folders_id_folder_seq'),
"name" varchar(50) NOT NULL,
"id_user" integer NOT NULL
);
INSERT INTO "user_folders" VALUES(1, 'inbox', 0), (2, 'outbox', 0), (3, 'sent_items', 0), (4, 'draft', 0), (5, 'Trash', 0), (6, 'Spam', 0);
CREATE TABLE "sms_used" (
"id_sms_used" serial PRIMARY KEY,
"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" serial PRIMARY KEY,
"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" serial PRIMARY KEY,
"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" serial PRIMARY KEY,
"system_name" varchar(255) NOT NULL,
"name" varchar(255) NOT NULL,
"status" smallint 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,
UNIQUE("system_name")
);
CREATE TABLE "user_forgot_password" (
"id_user" integer PRIMARY KEY,
"token" varchar(255) NOT NULL,
"valid_until" timestamp(0) WITHOUT time zone NOT NULL
);
CREATE TABLE "user_filters" (
"id_filter" serial PRIMARY KEY,
"id_user" integer NOT NULL,
"from" varchar(15) NOT NULL,
"has_the_words" varchar(50) NOT NULL,
"id_folder" integer NOT NULL
);
-- --------------------------------------------------------
--
-- Table structure for table `ci_sessions`
-- see: https://codeigniter.com/userguide3/libraries/sessions.html#database-driver
--
CREATE TABLE "ci_sessions" (
"id" varchar(128) NOT NULL,
"ip_address" varchar(45) NOT NULL,
"timestamp" bigint DEFAULT 0 NOT NULL,
"data" text DEFAULT '' NOT NULL
);
CREATE INDEX "ci_sessions_timestamp" ON "ci_sessions" ("timestamp");
-- When sess_match_ip = FALSE
ALTER TABLE ci_sessions ADD PRIMARY KEY (id);
|
