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

NAME

6       gammu-smsd-sql  -  gammu-smsd(1) backend using SQL abstraction layer to
7       use any supported database as a message storage
8

DESCRIPTION

10       SQL service stores all its data in database. It can use  one  of  these
11       SQL backends (configuration option Driver in smsd section):
12
13       · native_mysql for gammu-smsd-mysql
14
15       · native_pgsql for gammu-smsd-pgsql
16
17       · odbc for gammu-smsd-odbc
18
19       ·
20
21         drivers supported by DBI for gammu-smsd-dbi, which include:
22
23                · sqlite3 - for SQLite 3
24
25                · mysql - for MySQL
26
27                · pgsql - for PostgeSQL
28
29                · freetds - for MS SQL Server or Sybase
30

SQL CONNECTION PARAMETERS

32       Common for all backends:
33
34       · User - user connecting to database
35
36       · Password - password for connecting to database
37
38       · Host - database host or data source name
39
40       · Database - database name
41
42       · Driver - native_mysql, native_pgsql, odbc or DBI one
43
44       · SQL - SQL dialect to use
45
46       Specific for DBI:
47
48       · DriversPath - path to DBI drivers
49
50       · DBDir - sqlite/sqlite3 directory with database
51
52       SEE ALSO:
53          The variables are fully described in gammurc documentation.
54

TABLES

56       New in version 1.37.1.
57
58
59       You  can  customize name of all tables in the [tables]. The SQL queries
60       will reflect this, so it’s enough to change table name in this section.
61
62       gammu  Name of the gammu-table table.
63
64       inbox  Name of the inbox table.
65
66       sentitems
67              Name of the sentitems table.
68
69       outbox Name of the outbox table.
70
71       outbox_multipart
72              Name of the outbox_multipart table.
73
74       phones Name of the phones table.
75
76       You can change any table name using these:
77
78          [tables]
79          inbox = special_inbox
80

SQL QUERIES

82       Almost all queries are configurable. You can edit them  in  [sql]  sec‐
83       tion.  There are several variables used in SQL queries. We can separate
84       them into three groups:
85
86       · phone specific, which can be used in every query, see Phone  Specific
87         Parameters
88
89       · SMS  specific, which can be used in queries which works with SMS mes‐
90         sages, see SMS Specific Parameters
91
92       · query specific, which are numeric and are  specific  only  for  given
93         query (or set of queries), see Configurable queries
94
95   Phone Specific Parameters
96       %I     IMEI of phone
97
98       %S     SIM IMSI
99
100       %P     PHONE ID (hostname)
101
102       %N     client name (eg. Gammu 1.12.3)
103
104       %O     network code
105
106       %M     network name
107
108   SMS Specific Parameters
109       %R     remote number [1]
110
111       %C     delivery datetime
112
113       %e     delivery status on receiving or status error on sending
114
115       %t     message reference
116
117       %d     receiving datetime for received sms
118
119       %E     encoded text of SMS
120
121       %c     SMS coding (ie 8bit or UnicodeNoCompression)
122
123       %F     sms centre number
124
125       %u     UDH header
126
127       %x     class
128
129       %T     decoded SMS text
130
131       %A     CreatorID of SMS (sending sms)
132
133       %V     relative validity
134
135       [1]  Sender  number  for received messages (insert to inbox or delivery
136            notifications), destination otherwise.
137

CONFIGURABLE QUERIES

139       All configurable queries can be set in [sql] section. Sequence of  rows
140       in selects are mandatory.
141
142       All  default  queries  noted  here are noted for MySQL. Actual time and
143       time addition are selected for default queries during initialization.
144
145       delete_phone
146              Deletes phone from database.
147
148              Default value:
149
150                 DELETE FROM phones WHERE IMEI = %I
151
152       insert_phone
153              Inserts phone to database.
154
155              Default value:
156
157                 INSERT INTO phones (IMEI, ID, Send, Receive, InsertIntoDB, TimeOut, Client, Battery, Signal)
158                 VALUES (%I, %P, %1, %2, NOW(), (NOW() + INTERVAL 10 SECOND) + 0, %N, -1, -1)
159
160              Query specific parameters:
161
162              %1     enable send (yes or no) - configuration option Send
163
164              %2     enable  receive  (yes  or  no)   -  configuration  option
165                     Receive
166
167       save_inbox_sms_select
168              Select message for update delivery status.
169
170              Default value:
171
172                 SELECT ID, Status, SendingDateTime, DeliveryDateTime, SMSCNumber FROM sentitems
173                 WHERE DeliveryDateTime IS NULL AND SenderID = %P AND TPMR = %t AND DestinationNumber = %R
174
175       save_inbox_sms_update_delivered
176              Update message delivery status if message was delivered.
177
178              Default value:
179
180                 UPDATE sentitems SET DeliveryDateTime = %C, Status = %1, StatusError = %e WHERE ID = %2 AND TPMR = %t
181
182              Query specific parameters:
183
184              %1     delivery status returned by GSM network
185
186              %2     ID of message
187
188       save_inbox_sms_update
189              Update message if there is an delivery error.
190
191              Default value:
192
193                 UPDATE sentitems SET Status = %1, StatusError = %e WHERE ID = %2 AND TPMR = %t
194
195              Query specific parameters:
196
197              %1     delivery status returned by GSM network
198
199              %2     ID of message
200
201       save_inbox_sms_insert
202              Insert received message.
203
204              Default value:
205
206                 INSERT INTO inbox (ReceivingDateTime, Text, SenderNumber, Coding, SMSCNumber, UDH,
207                 Class, TextDecoded, RecipientID) VALUES (%d, %E, %R, %c, %F, %u, %x, %T, %P)
208
209       update_received
210              Update statistics after receiving message.
211
212              Default value:
213
214                 UPDATE phones SET Received = Received + 1 WHERE IMEI = %I
215
216       refresh_send_status
217              Update messages in outbox.
218
219              Default value:
220
221                 UPDATE outbox SET SendingTimeOut = (NOW() + INTERVAL 60 SECOND) + 0
222                 WHERE ID = %1 AND (SendingTimeOut < NOW() OR SendingTimeOut IS NULL)
223
224              The  default query calculates sending timeout based on LoopSleep
225              value.
226
227              Query specific parameters:
228
229              %1     ID of message
230
231       find_outbox_sms_id
232              Find sms messages for sending.
233
234              Default value:
235
236                 SELECT ID, InsertIntoDB, SendingDateTime, SenderID FROM outbox
237                 WHERE SendingDateTime < NOW() AND SendingTimeOut <  NOW() AND
238                 SendBefore >= CURTIME() AND SendAfter <= CURTIME() AND
239                 ( SenderID is NULL OR SenderID = '' OR SenderID = %P ) ORDER BY InsertIntoDB ASC LIMIT %1
240
241              Query specific parameters:
242
243              %1     limit of sms messages sended in one walk in loop
244
245       find_outbox_body
246              Select body of message.
247
248              Default value:
249
250                 SELECT Text, Coding, UDH, Class, TextDecoded, ID, DestinationNumber, MultiPart,
251                 RelativeValidity, DeliveryReport, CreatorID FROM outbox WHERE ID=%1
252
253              Query specific parameters:
254
255              %1     ID of message
256
257       find_outbox_multipart
258              Select remaining parts of sms message.
259
260              Default value:
261
262                 SELECT Text, Coding, UDH, Class, TextDecoded, ID, SequencePosition
263                 FROM outbox_multipart WHERE ID=%1 AND SequencePosition=%2
264
265              Query specific parameters:
266
267              %1     ID of message
268
269              %2     Number of multipart message
270
271       delete_outbox
272              Remove messages from outbox after threir successful send.
273
274              Default value:
275
276                 DELETE FROM outbox WHERE ID=%1
277
278              Query specific parameters:
279
280              %1     ID of message
281
282       delete_outbox_multipart
283              Remove messages from outbox_multipart  after  threir  successful
284              send.
285
286              Default value:
287
288                 DELETE FROM outbox_multipart WHERE ID=%1
289
290              Query specific parameters:
291
292              %1     ID of message
293
294       create_outbox
295              Create message (insert to outbox).
296
297              Default value:
298
299                 INSERT INTO outbox (CreatorID, SenderID, DeliveryReport, MultiPart,
300                 InsertIntoDB, Text, DestinationNumber, RelativeValidity, Coding, UDH, Class,
301                 TextDecoded) VALUES (%1, %P, %2, %3, NOW(), %E, %R, %V, %c, %u, %x, %T)
302
303              Query specific parameters:
304
305              %1     creator of message
306
307              %2     delivery status report - yes/default
308
309              %3     multipart - FALSE/TRUE
310
311              %4     Part (part number)
312
313              %5     ID of message
314
315       create_outbox_multipart
316              Create message remaining parts.
317
318              Default value:
319
320                 INSERT INTO outbox_multipart (SequencePosition, Text, Coding, UDH, Class,
321                 TextDecoded, ID) VALUES (%4, %E, %c, %u, %x, %T, %5)
322
323              Query specific parameters:
324
325              %1     creator of message
326
327              %2     delivery status report - yes/default
328
329              %3     multipart - FALSE/TRUE
330
331              %4     Part (part number)
332
333              %5     ID of message
334
335       add_sent_info
336              Insert to sentitems.
337
338              Default value:
339
340                 INSERT INTO sentitems (CreatorID,ID,SequencePosition,Status,SendingDateTime,
341                 SMSCNumber, TPMR, SenderID,Text,DestinationNumber,Coding,UDH,Class,TextDecoded,
342                 InsertIntoDB,RelativeValidity)
343                 VALUES (%A, %1, %2, %3, NOW(), %F, %4, %P, %E, %R, %c, %u, %x, %T, %5, %V)
344
345              Query specific parameters:
346
347              %1     ID of sms message
348
349              %2     part number (for multipart sms)
350
351              %3     message  state (SendingError, Error, SendingOK, SendingO‐
352                     KNoReport)
353
354              %4     message reference (TPMR)
355
356              %5     time when inserted in db
357
358       update_sent
359              Update sent statistics after sending message.
360
361              Default value:
362
363                 UPDATE phones SET Sent= Sent + 1 WHERE IMEI = %I
364
365       refresh_phone_status
366              Update phone status (battery, signal).
367
368              Default value:
369
370                 UPDATE phones SET TimeOut= (NOW() + INTERVAL 10 SECOND) + 0,
371                 Battery = %1, Signal = %2 WHERE IMEI = %I
372
373              Query specific parameters:
374
375              %1     battery percent
376
377              %2     signal percent
378
379       update_retries
380              Update number of retries for outbox message.
381
382                 UPDATE outbox SET SendngTimeOut = (NOW() + INTERVAL 600 SECOND) + 0,
383                 Retries = %2 WHERE ID = %1
384
385              Query specific parameters:
386
387              %1     message ID
388
389              %2     number of retries
390

AUTHOR

392       Michal Čihař <michal@cihar.com>
393

395       2009-2015, Michal Čihař <michal@cihar.com>
396
397
398
399
4001.39.0                           Jan 05, 2018                GAMMU-SMSD-SQL(7)
Impressum