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
|
--
-- Database: "smsd"
--
-- CREATE USER "smsd" WITH NOCREATEDB NOCREATEUSER;
-- CREATE DATABASE "smsd" WITH OWNER = "smsd" ENCODING = 'UTF8';
-- \connect "smsd" "smsd"
-- COMMENT ON DATABASE "smsd" IS 'Gammu SMSD Database';
-- --------------------------------------------------------
--
-- Function declaration for updating timestamps
--
CREATE LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION update_timestamp() RETURNS trigger AS $update_timestamp$
BEGIN
NEW."UpdatedInDB" := LOCALTIMESTAMP(0);
RETURN NEW;
END;
$update_timestamp$ LANGUAGE plpgsql;
-- --------------------------------------------------------
--
-- Sequence declarations for tables' primary keys
--
--CREATE SEQUENCE inbox_ID_seq;
--CREATE SEQUENCE outbox_ID_seq;
--CREATE SEQUENCE outbox_multipart_ID_seq;
--CREATE SEQUENCE sentitems_ID_seq;
-- --------------------------------------------------------
--
-- Index declarations for tables' primary keys
--
--CREATE UNIQUE INDEX inbox_pkey ON inbox USING btree ("ID");
--CREATE UNIQUE INDEX outbox_pkey ON outbox USING btree ("ID");
--CREATE UNIQUE INDEX outbox_multipart_pkey ON outbox_multipart USING btree ("ID");
--CREATE UNIQUE INDEX sentitems_pkey ON sentitems USING btree ("ID");
-- --------------------------------------------------------
--
-- Table structure for table "gammu"
--
CREATE TABLE gammu (
"Version" smallint NOT NULL DEFAULT '0' PRIMARY KEY
);
--
-- Dumping data for table "gammu"
--
INSERT INTO gammu ("Version") VALUES (16);
-- --------------------------------------------------------
--
-- Table structure for table "inbox"
--
CREATE TABLE inbox (
"UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"ReceivingDateTime" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"Text" text NOT NULL,
"SenderNumber" varchar(20) NOT NULL DEFAULT '',
"Coding" varchar(255) NOT NULL DEFAULT 'Default_No_Compression',
"UDH" text NOT NULL,
"SMSCNumber" varchar(20) NOT NULL DEFAULT '',
"Class" integer NOT NULL DEFAULT '-1',
"TextDecoded" text NOT NULL DEFAULT '',
"ID" serial PRIMARY KEY,
"RecipientID" text NOT NULL,
"Processed" boolean NOT NULL DEFAULT 'false',
CHECK ("Coding" IN
('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression'))
);
--
-- Dumping data for table "inbox"
--
-- --------------------------------------------------------
--
-- Create trigger for table "inbox"
--
CREATE TRIGGER update_timestamp BEFORE UPDATE ON inbox FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
-- --------------------------------------------------------
--
-- Table structure for table "outbox"
--
CREATE TABLE outbox (
"UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"InsertIntoDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"SendingDateTime" timestamp NOT NULL DEFAULT LOCALTIMESTAMP(0),
"SendBefore" time NOT NULL DEFAULT '23:59:59',
"SendAfter" time NOT NULL DEFAULT '00:00:00',
"Text" text,
"DestinationNumber" varchar(20) NOT NULL DEFAULT '',
"Coding" varchar(255) NOT NULL DEFAULT 'Default_No_Compression',
"UDH" text,
"Class" integer DEFAULT '-1',
"TextDecoded" text NOT NULL DEFAULT '',
"ID" serial PRIMARY KEY,
"MultiPart" boolean NOT NULL DEFAULT 'false',
"RelativeValidity" integer DEFAULT '-1',
"SenderID" varchar(255),
"SendingTimeOut" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"DeliveryReport" varchar(10) DEFAULT 'default',
"CreatorID" text NOT NULL,
"Retries" integer DEFAULT '0',
"Priority" integer DEFAULT '0',
CHECK ("Coding" IN
('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
CHECK ("DeliveryReport" IN ('default','yes','no'))
);
CREATE INDEX outbox_date ON outbox("SendingDateTime", "SendingTimeOut");
CREATE INDEX outbox_sender ON outbox("SenderID");
--
-- Dumping data for table "outbox"
--
-- --------------------------------------------------------
--
-- Create trigger for table "outbox"
--
CREATE TRIGGER update_timestamp BEFORE UPDATE ON outbox FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
-- --------------------------------------------------------
--
-- Table structure for table "outbox_multipart"
--
CREATE TABLE outbox_multipart (
"Text" text,
"Coding" varchar(255) NOT NULL DEFAULT 'Default_No_Compression',
"UDH" text,
"Class" integer DEFAULT '-1',
"TextDecoded" text DEFAULT NULL,
"ID" serial,
"SequencePosition" integer NOT NULL DEFAULT '1',
PRIMARY KEY ("ID", "SequencePosition"),
CHECK ("Coding" IN
('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression'))
);
--
-- Dumping data for table "outbox_multipart"
--
-- --------------------------------------------------------
--
-- Table structure for table "phones"
--
CREATE TABLE phones (
"ID" text NOT NULL,
"UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"InsertIntoDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"TimeOut" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"Send" boolean NOT NULL DEFAULT 'no',
"Receive" boolean NOT NULL DEFAULT 'no',
"IMEI" varchar(35) PRIMARY KEY NOT NULL,
"IMSI" varchar(35) NOT NULL,
"NetCode" varchar(10) DEFAULT 'ERROR',
"NetName" varchar(35) DEFAULT 'ERROR',
"Client" text NOT NULL,
"Battery" integer NOT NULL DEFAULT -1,
"Signal" integer NOT NULL DEFAULT -1,
"Sent" integer NOT NULL DEFAULT 0,
"Received" integer NOT NULL DEFAULT 0
);
--
-- Dumping data for table "phones"
--
-- --------------------------------------------------------
--
-- Create trigger for table "phones"
--
CREATE TRIGGER update_timestamp BEFORE UPDATE ON phones FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
-- --------------------------------------------------------
--
-- Table structure for table "sentitems"
--
CREATE TABLE sentitems (
"UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"InsertIntoDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"SendingDateTime" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"DeliveryDateTime" timestamp(0) WITHOUT time zone NULL,
"Text" text NOT NULL,
"DestinationNumber" varchar(20) NOT NULL DEFAULT '',
"Coding" varchar(255) NOT NULL DEFAULT 'Default_No_Compression',
"UDH" text NOT NULL,
"SMSCNumber" varchar(20) NOT NULL DEFAULT '',
"Class" integer NOT NULL DEFAULT '-1',
"TextDecoded" text NOT NULL DEFAULT '',
"ID" serial,
"SenderID" varchar(255) NOT NULL,
"SequencePosition" integer NOT NULL DEFAULT '1',
"Status" varchar(255) NOT NULL DEFAULT 'SendingOK',
"StatusError" integer NOT NULL DEFAULT '-1',
"TPMR" integer NOT NULL DEFAULT '-1',
"RelativeValidity" integer NOT NULL DEFAULT '-1',
"CreatorID" text NOT NULL,
CHECK ("Status" IN
('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
'DeliveryUnknown','Error')),
CHECK ("Coding" IN
('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
PRIMARY KEY ("ID", "SequencePosition")
);
CREATE INDEX sentitems_date ON sentitems("DeliveryDateTime");
CREATE INDEX sentitems_tpmr ON sentitems("TPMR");
CREATE INDEX sentitems_dest ON sentitems("DestinationNumber");
CREATE INDEX sentitems_sender ON sentitems("SenderID");
--
-- Dumping data for table "sentitems"
--
-- --------------------------------------------------------
--
-- Create trigger for table "sentitems"
--
CREATE TRIGGER update_timestamp BEFORE UPDATE ON sentitems FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
|
