1GAMMU-SMSD-PGSQL(7)                  Gammu                 GAMMU-SMSD-PGSQL(7)
2
3
4

NAME

6       gammu-smsd-pgsql  -  gammu-smsd(1)  backend  using  PostgreSQL database
7       server as a message storage
8

DESCRIPTION

10       PGSQL backend stores all data in a PostgreSQL  database  server,  which
11       parameters  are defined by configuration (see gammu-smsdrc for descrip‐
12       tion of configuration options).
13
14       For tables description see gammu-smsd-tables.
15
16       This backend is based on gammu-smsd-sql.
17

CONFIGURATION

19       Before running gammu-smsd you need to create necessary  tables  in  the
20       database, which is described below.
21
22       The configuration file then can look like:
23
24          [smsd]
25          service = sql
26          driver = native_pgsql
27          host = localhost
28
29       SEE ALSO:
30          gammu-smsdrc
31

CREATING TABLES FOR POSTGRESQL

33       SQL script for creating tables in PostgreSQL database:
34
35          --
36          -- Database: "smsd"
37          --
38          -- CREATE USER "smsd" WITH NOCREATEDB NOCREATEUSER;
39          -- CREATE DATABASE "smsd" WITH OWNER = "smsd" ENCODING = 'UTF8';
40          -- \connect "smsd" "smsd"
41          -- COMMENT ON DATABASE "smsd" IS 'Gammu SMSD Database';
42
43          -- --------------------------------------------------------
44
45          --
46          -- Function declaration for updating timestamps
47          --
48          CREATE LANGUAGE plpgsql;
49          CREATE OR REPLACE FUNCTION update_timestamp() RETURNS trigger AS $update_timestamp$
50            BEGIN
51              NEW."UpdatedInDB" := LOCALTIMESTAMP(0);
52              RETURN NEW;
53            END;
54          $update_timestamp$ LANGUAGE plpgsql;
55
56          -- --------------------------------------------------------
57
58          --
59          -- Sequence declarations for tables' primary keys
60          --
61
62          --CREATE SEQUENCE inbox_ID_seq;
63
64          --CREATE SEQUENCE outbox_ID_seq;
65
66          --CREATE SEQUENCE outbox_multipart_ID_seq;
67
68          --CREATE SEQUENCE sentitems_ID_seq;
69
70          -- --------------------------------------------------------
71
72          --
73          -- Index declarations for tables' primary keys
74          --
75
76          --CREATE UNIQUE INDEX inbox_pkey ON inbox USING btree ("ID");
77
78          --CREATE UNIQUE INDEX outbox_pkey ON outbox USING btree ("ID");
79
80          --CREATE UNIQUE INDEX outbox_multipart_pkey ON outbox_multipart USING btree ("ID");
81
82          --CREATE UNIQUE INDEX sentitems_pkey ON sentitems USING btree ("ID");
83
84          -- --------------------------------------------------------
85
86          --
87          -- Table structure for table "gammu"
88          --
89
90          CREATE TABLE gammu (
91            "Version" smallint NOT NULL DEFAULT '0' PRIMARY KEY
92          );
93
94          --
95          -- Dumping data for table "gammu"
96          --
97
98          INSERT INTO gammu ("Version") VALUES (17);
99
100          -- --------------------------------------------------------
101
102          --
103          -- Table structure for table "inbox"
104          --
105
106          CREATE TABLE inbox (
107            "UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
108            "ReceivingDateTime" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
109            "Text" text NOT NULL,
110            "SenderNumber" varchar(20) NOT NULL DEFAULT '',
111            "Coding" varchar(255) NOT NULL DEFAULT 'Default_No_Compression',
112            "UDH" text NOT NULL,
113            "SMSCNumber" varchar(20) NOT NULL DEFAULT '',
114            "Class" integer NOT NULL DEFAULT '-1',
115            "TextDecoded" text NOT NULL DEFAULT '',
116            "ID" serial PRIMARY KEY,
117            "RecipientID" text NOT NULL,
118            "Processed" boolean NOT NULL DEFAULT 'false',
119            "Status" integer NOT NULL DEFAULT '-1',
120            CHECK ("Coding" IN
121            ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression'))
122          );
123
124          --
125          -- Dumping data for table "inbox"
126          --
127
128          -- --------------------------------------------------------
129
130          --
131          -- Create trigger for table "inbox"
132          --
133
134          CREATE TRIGGER update_timestamp BEFORE UPDATE ON inbox FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
135
136          -- --------------------------------------------------------
137
138          --
139          -- Table structure for table "outbox"
140          --
141
142          CREATE TABLE outbox (
143            "UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
144            "InsertIntoDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
145            "SendingDateTime" timestamp NOT NULL DEFAULT LOCALTIMESTAMP(0),
146            "SendBefore" time NOT NULL DEFAULT '23:59:59',
147            "SendAfter" time NOT NULL DEFAULT '00:00:00',
148            "Text" text,
149            "DestinationNumber" varchar(20) NOT NULL DEFAULT '',
150            "Coding" varchar(255) NOT NULL DEFAULT 'Default_No_Compression',
151            "UDH" text,
152            "Class" integer DEFAULT '-1',
153            "TextDecoded" text NOT NULL DEFAULT '',
154            "ID" serial PRIMARY KEY,
155            "MultiPart" boolean NOT NULL DEFAULT 'false',
156            "RelativeValidity" integer DEFAULT '-1',
157            "SenderID" varchar(255),
158            "SendingTimeOut" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
159            "DeliveryReport" varchar(10) DEFAULT 'default',
160            "CreatorID" text NOT NULL,
161            "Retries" integer DEFAULT '0',
162            "Priority" integer DEFAULT '0',
163            "Status" varchar(255) NOT NULL DEFAULT 'Reserved',
164            "StatusCode" integer NOT NULL DEFAULT '-1',
165            CHECK ("Coding" IN
166            ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
167            CHECK ("DeliveryReport" IN ('default','yes','no')),
168            CHECK ("Status" IN
169            ('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
170            'DeliveryUnknown','Error','Reserved'))
171          );
172
173          CREATE INDEX outbox_date ON outbox("SendingDateTime", "SendingTimeOut");
174          CREATE INDEX outbox_sender ON outbox("SenderID");
175
176          --
177          -- Dumping data for table "outbox"
178          --
179
180          -- --------------------------------------------------------
181
182          --
183          -- Create trigger for table "outbox"
184          --
185
186          CREATE TRIGGER update_timestamp BEFORE UPDATE ON outbox FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
187
188          -- --------------------------------------------------------
189
190          --
191          -- Table structure for table "outbox_multipart"
192          --
193
194          CREATE TABLE outbox_multipart (
195            "Text" text,
196            "Coding" varchar(255) NOT NULL DEFAULT 'Default_No_Compression',
197            "UDH" text,
198            "Class" integer DEFAULT '-1',
199            "TextDecoded" text DEFAULT NULL,
200            "ID" serial,
201            "SequencePosition" integer NOT NULL DEFAULT '1',
202            "Status" varchar(255) NOT NULL DEFAULT 'Reserved',
203            "StatusCode" integer NOT NULL DEFAULT '-1',
204            PRIMARY KEY ("ID", "SequencePosition"),
205            CHECK ("Coding" IN
206            ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
207            CHECK ("Status" IN
208            ('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
209            'DeliveryUnknown','Error','Reserved'))
210          );
211
212          --
213          -- Dumping data for table "outbox_multipart"
214          --
215
216
217          -- --------------------------------------------------------
218
219          --
220          -- Table structure for table "phones"
221          --
222
223          CREATE TABLE phones (
224            "ID" text NOT NULL,
225            "UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
226            "InsertIntoDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
227            "TimeOut" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
228            "Send" boolean NOT NULL DEFAULT 'no',
229            "Receive" boolean NOT NULL DEFAULT 'no',
230            "IMEI" varchar(35) PRIMARY KEY NOT NULL,
231            "IMSI" varchar(35) NOT NULL,
232            "NetCode" varchar(10) DEFAULT 'ERROR',
233            "NetName" varchar(35) DEFAULT 'ERROR',
234            "Client" text NOT NULL,
235            "Battery" integer NOT NULL DEFAULT -1,
236            "Signal" integer NOT NULL DEFAULT -1,
237            "Sent" integer NOT NULL DEFAULT 0,
238            "Received" integer NOT NULL DEFAULT 0
239          );
240
241          --
242          -- Dumping data for table "phones"
243          --
244
245          -- --------------------------------------------------------
246
247          --
248          -- Create trigger for table "phones"
249          --
250
251          CREATE TRIGGER update_timestamp BEFORE UPDATE ON phones FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
252
253          -- --------------------------------------------------------
254
255          --
256          -- Table structure for table "sentitems"
257          --
258
259          CREATE TABLE sentitems (
260            "UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
261            "InsertIntoDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
262            "SendingDateTime" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
263            "DeliveryDateTime" timestamp(0) WITHOUT time zone NULL,
264            "Text" text NOT NULL,
265            "DestinationNumber" varchar(20) NOT NULL DEFAULT '',
266            "Coding" varchar(255) NOT NULL DEFAULT 'Default_No_Compression',
267            "UDH" text NOT NULL,
268            "SMSCNumber" varchar(20) NOT NULL DEFAULT '',
269            "Class" integer NOT NULL DEFAULT '-1',
270            "TextDecoded" text NOT NULL DEFAULT '',
271            "ID" serial,
272            "SenderID" varchar(255) NOT NULL,
273            "SequencePosition" integer NOT NULL DEFAULT '1',
274            "Status" varchar(255) NOT NULL DEFAULT 'SendingOK',
275            "StatusError" integer NOT NULL DEFAULT '-1',
276            "TPMR" integer NOT NULL DEFAULT '-1',
277            "RelativeValidity" integer NOT NULL DEFAULT '-1',
278            "CreatorID" text NOT NULL,
279            "StatusCode" integer NOT NULL DEFAULT '-1',
280            CHECK ("Status" IN
281            ('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
282            'DeliveryUnknown','Error')),
283            CHECK ("Coding" IN
284            ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
285            PRIMARY KEY ("ID", "SequencePosition")
286          );
287
288          CREATE INDEX sentitems_date ON sentitems("DeliveryDateTime");
289          CREATE INDEX sentitems_tpmr ON sentitems("TPMR");
290          CREATE INDEX sentitems_dest ON sentitems("DestinationNumber");
291          CREATE INDEX sentitems_sender ON sentitems("SenderID");
292
293          --
294          -- Dumping data for table "sentitems"
295          --
296
297          -- --------------------------------------------------------
298
299          --
300          -- Create trigger for table "sentitems"
301          --
302
303          CREATE TRIGGER update_timestamp BEFORE UPDATE ON sentitems FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
304
305
306
307       NOTE:
308          You can find the script in docs/sql/pgsql.sql as well.
309

UPGRADING TABLES

311       The  easiest way to upgrade database structure is to backup old one and
312       start with creating new one based on example above.
313
314       For upgrading existing database,  you  can  use  changes  described  in
315       smsd-tables-history and then manually update Version field in gammu ta‐
316       ble.
317

AUTHOR

319       Michal Čihař <michal@cihar.com>
320
322       2009-2015, Michal Čihař <michal@cihar.com>
323
324
325
326
3271.42.0                           Oct 03, 2020              GAMMU-SMSD-PGSQL(7)
Impressum