| blob | 8acf2414a47fc3866727c8648357e4e4a2971ed1 |
1 .TH "GAMMU-SMSD-SQL" "7" "December 21, 2011" "1.31.0" "Gammu"
2 .SH NAME
3 gammu-smsd-sql \- gammu-smsd(1) backend using SQL abstraction layer to use any supported database as a message storage
4 .
5 .nr rst2man-indent-level 0
6 .
7 .de1 rstReportMargin
8 \\$1 \\n[an-margin]
9 level \\n[rst2man-indent-level]
10 level margin: \\n[rst2man-indent\\n[rst2man-indent-level]]
11 -
12 \\n[rst2man-indent0]
13 \\n[rst2man-indent1]
14 \\n[rst2man-indent2]
15 ..
16 .de1 INDENT
17 .\" .rstReportMargin pre:
18 . RS \\$1
19 . nr rst2man-indent\\n[rst2man-indent-level] \\n[an-margin]
20 . nr rst2man-indent-level +1
21 .\" .rstReportMargin post:
22 ..
23 .de UNINDENT
24 . RE
25 .\" indent \\n[an-margin]
26 .\" old: \\n[rst2man-indent\\n[rst2man-indent-level]]
27 .nr rst2man-indent-level -1
28 .\" new: \\n[rst2man-indent\\n[rst2man-indent-level]]
29 .in \\n[rst2man-indent\\n[rst2man-indent-level]]u
30 ..
31 .\" Man page generated from reStructeredText.
32 .
33 .SH DESCRIPTION
34 .sp
35 SQL service stores all its data in database. It can use one of these SQL backends
36 (configuration option \fBDriver\fP in smsd section):
37 .INDENT 0.0
38 .IP \(bu 2
39 \fBnative_mysql\fP for \fIgammu\-smsd\-mysql\fP
40 .IP \(bu 2
41 \fBnative_pgsql\fP for \fIgammu\-smsd\-pgsql\fP
42 .IP \(bu 2
43 \fBodbc\fP for \fIgammu\-smsd\-odbc\fP
44 .IP \(bu 2
45 .INDENT 2.0
46 .TP
47 .B drivers supported by DBI for \fIgammu\-smsd\-dbi\fP, which include:
48 .INDENT 7.0
49 .IP \(bu 2
50 \fBsqlite3\fP \- for SQLite 3
51 .IP \(bu 2
52 \fBmysql\fP \- for MySQL
53 .IP \(bu 2
54 \fBpgsql\fP \- for PostgeSQL
55 .IP \(bu 2
56 \fBfreetds\fP \- for MS SQL Server or Sybase
57 .UNINDENT
58 .UNINDENT
59 .UNINDENT
60 .SH SQL CONNECTION PARAMETERS
61 .sp
62 Common for all backends:
63 .INDENT 0.0
64 .IP \(bu 2
65 \fBUser\fP \- user connecting to database
66 .IP \(bu 2
67 \fBPassword\fP \- password for connecting to database
68 .IP \(bu 2
69 \fBHost\fP \- database host or data source name
70 .IP \(bu 2
71 \fBDatabase\fP \- database name
72 .IP \(bu 2
73 \fBDriver\fP \- \fBnative_mysql\fP, \fBnative_pgsql\fP, \fBodbc\fP or DBI one
74 .IP \(bu 2
75 \fBSQL\fP \- SQL dialect to use
76 .UNINDENT
77 .sp
78 Specific for DBI:
79 .INDENT 0.0
80 .IP \(bu 2
81 \fBDriversPath\fP \- path to DBI drivers
82 .IP \(bu 2
83 \fBDBDir\fP \- sqlite/sqlite3 directory with database
84 .UNINDENT
85 .IP "See also"
86 .sp
87 The variables are fully described in \fIgammurc\fP documentation.
88 .RE
89 .SH SQL QUERIES
90 .sp
91 Almost all queries are configurable. You can edit them in
92 \fB[sql]\fP section. There are several variables used in SQL
93 queries. We can separate them into three groups:
94 .INDENT 0.0
95 .IP \(bu 2
96 phone specific, which can be used in every query, see \fI\%Phone Specific Parameters\fP
97 .IP \(bu 2
98 SMS specific, which can be used in queries which works with SMS messages, see \fI\%SMS Specific Parameters\fP
99 .IP \(bu 2
100 query specific, which are numeric and are specific only for given query (or set of queries), see \fI\%Configurable queries\fP
101 .UNINDENT
102 .SS Phone Specific Parameters
103 .INDENT 0.0
104 .TP
105 .B \fB%I\fP
106 IMEI of phone
107 .TP
108 .B \fB%P\fP
109 PHONE ID (hostname)
110 .TP
111 .B \fB%N\fP
112 client name (eg. Gammu 1.12.3)
113 .UNINDENT
114 .SS SMS Specific Parameters
115 .INDENT 0.0
116 .TP
117 .B \fB%R\fP
118 remote number [1]
119 .TP
120 .B \fB%C\fP
121 delivery datetime
122 .TP
123 .B \fB%e\fP
124 delivery status on receiving or status error on sending
125 .TP
126 .B \fB%t\fP
127 message reference
128 .TP
129 .B \fB%d\fP
130 receiving datetime for received sms
131 .TP
132 .B \fB%E\fP
133 encoded text of SMS
134 .TP
135 .B \fB%c\fP
136 SMS coding (ie 8bit or UnicodeNoCompression)
137 .TP
138 .B \fB%F\fP
139 sms centre number
140 .TP
141 .B \fB%u\fP
142 UDH header
143 .TP
144 .B \fB%x\fP
145 class
146 .TP
147 .B \fB%T\fP
148 decoded SMS text
149 .TP
150 .B \fB%A\fP
151 CreatorID of SMS (sending sms)
152 .TP
153 .B \fB%V\fP
154 relative validity
155 .UNINDENT
156 .IP [1] 5
157 Sender number for received messages (insert to inbox or delivery notifications), destination otherwise.
158 .SH CONFIGURABLE QUERIES
159 .sp
160 All configurable queries can be set in \fB[sql]\fP section. Sequence of rows in selects are mandatory.
161 .sp
162 All default queries noted here are noted for MySQL. Actual time and time addition
163 are selected for default queries during initialization.
164 .INDENT 0.0
165 .TP
166 .B delete_phone
167 Deletes phone from database.
168 .sp
169 Default value:
170 .sp
171 .nf
172 .ft C
173 DELETE FROM phones WHERE IMEI = %I
174 .ft P
175 .fi
176 .UNINDENT
177 .INDENT 0.0
178 .TP
179 .B insert_phone
180 Inserts phone to database.
181 .sp
182 Default value:
183 .sp
184 .nf
185 .ft C
186 INSERT INTO phones (IMEI, ID, Send, Receive, InsertIntoDB, TimeOut, Client, Battery, SignalStrength)
187 VALUES (%I, %P, %1, %2, NOW(), (NOW() + INTERVAL 10 SECOND) + 0, %N, \-1, \-1)
188 .ft P
189 .fi
190 .sp
191 Query specific parameters:
192 .INDENT 7.0
193 .TP
194 .B \fB%1\fP
195 enable send (yes or no) \- configuration option Send
196 .TP
197 .B \fB%2\fP
198 enable receive (yes or no) \- configuration option Receive
199 .UNINDENT
200 .UNINDENT
201 .INDENT 0.0
202 .TP
203 .B save_inbox_sms_select
204 Select message for update delivery status.
205 .sp
206 Default value:
207 .sp
208 .nf
209 .ft C
210 SELECT ID, Status, SendingDateTime, DeliveryDateTime, SMSCNumber FROM sentitems
211 WHERE DeliveryDateTime IS NULL AND SenderID = %P AND TPMR = %t AND DestinationNumber = %R
212 .ft P
213 .fi
214 .UNINDENT
215 .INDENT 0.0
216 .TP
217 .B save_inbox_sms_update_delivered
218 Update message delivery status if message was delivered.
219 .sp
220 Default value:
221 .sp
222 .nf
223 .ft C
224 UPDATE sentitems SET DeliveryDateTime = %C, Status = %1, StatusError = %e WHERE ID = %2 AND TPMR = %t
225 .ft P
226 .fi
227 .sp
228 Query specific parameters:
229 .INDENT 7.0
230 .TP
231 .B \fB%1\fP
232 delivery status returned by GSM network
233 .TP
234 .B \fB%2\fP
235 ID of message
236 .UNINDENT
237 .UNINDENT
238 .INDENT 0.0
239 .TP
240 .B save_inbox_sms_update
241 Update message if there is an delivery error.
242 .sp
243 Default value:
244 .sp
245 .nf
246 .ft C
247 UPDATE sentitems SET Status = %1, StatusError = %e WHERE ID = %2 AND TPMR = %t
248 .ft P
249 .fi
250 .sp
251 Query specific parameters:
252 .INDENT 7.0
253 .TP
254 .B \fB%1\fP
255 delivery status returned by GSM network
256 .TP
257 .B \fB%2\fP
258 ID of message
259 .UNINDENT
260 .UNINDENT
261 .INDENT 0.0
262 .TP
263 .B save_inbox_sms_insert
264 Insert received message.
265 .sp
266 Default value:
267 .sp
268 .nf
269 .ft C
270 INSERT INTO inbox (ReceivingDateTime, Text, SenderNumber, Coding, SMSCNumber, UDH,
271 Class, TextDecoded, RecipientID) VALUES (%d, %E, %R, %c, %F, %u, %x, %T, %P)
272 .ft P
273 .fi
274 .UNINDENT
275 .INDENT 0.0
276 .TP
277 .B update_received
278 Update statistics after receiving message.
279 .sp
280 Default value:
281 .sp
282 .nf
283 .ft C
284 UPDATE phones SET Received = Received + 1 WHERE IMEI = %I
285 .ft P
286 .fi
287 .UNINDENT
288 .INDENT 0.0
289 .TP
290 .B refresh_send_status
291 Update messages in outbox.
292 .sp
293 Default value:
294 .sp
295 .nf
296 .ft C
297 UPDATE outbox SET SendingTimeOut = (NOW() + INTERVAL locktime SECOND) + 0
298 WHERE ID = %1 AND (SendingTimeOut < NOW() OR SendingTimeOut IS NULL)
299 .ft P
300 .fi
301 .sp
302 Query specific parameters:
303 .INDENT 7.0
304 .TP
305 .B \fB%1\fP
306 ID of message
307 .UNINDENT
308 .UNINDENT
309 .INDENT 0.0
310 .TP
311 .B find_outbox_sms_id
312 Find sms messages for sending.
313 .sp
314 Default value:
315 .sp
316 .nf
317 .ft C
318 SELECT ID, InsertIntoDB, SendingDateTime, SenderID FROM outbox
319 WHERE SendingDateTime < NOW() AND SendingTimeOut < NOW() AND
320 SendBefore >= CURTIME() AND SendAfter <= CURTIME() AND
321 ( SenderID is NULL OR SenderID = \(aq\(aq OR SenderID = %P ) ORDER BY InsertIntoDB ASC LIMIT %1
322 .ft P
323 .fi
324 .sp
325 Query specific parameters:
326 .INDENT 7.0
327 .TP
328 .B \fB%1\fP
329 limit of sms messages sended in one walk in loop
330 .UNINDENT
331 .UNINDENT
332 .INDENT 0.0
333 .TP
334 .B find_outbox_body
335 Select body of message.
336 .sp
337 Default value:
338 .sp
339 .nf
340 .ft C
341 SELECT Text, Coding, UDH, Class, TextDecoded, ID, DestinationNumber, MultiPart,
342 RelativeValidity, DeliveryReport, CreatorID FROM outbox WHERE ID=%1
343 .ft P
344 .fi
345 .sp
346 Query specific parameters:
347 .INDENT 7.0
348 .TP
349 .B \fB%1\fP
350 ID of message
351 .UNINDENT
352 .UNINDENT
353 .INDENT 0.0
354 .TP
355 .B find_outbox_multipart
356 Select remaining parts of sms message.
357 .sp
358 Default value:
359 .sp
360 .nf
361 .ft C
362 SELECT Text, Coding, UDH, Class, TextDecoded, ID, SequencePosition
363 FROM outbox_multipart WHERE ID=%1 AND SequencePosition=%2
364 .ft P
365 .fi
366 .sp
367 Query specific parameters:
368 .INDENT 7.0
369 .TP
370 .B \fB%1\fP
371 ID of message
372 .TP
373 .B \fB%2\fP
374 Number of multipart message
375 .UNINDENT
376 .UNINDENT
377 .INDENT 0.0
378 .TP
379 .B delete_outbox
380 Remove messages from outbox after threir successful send.
381 .sp
382 Default value:
383 .sp
384 .nf
385 .ft C
386 DELETE FROM outbox WHERE ID=%1
387 .ft P
388 .fi
389 .sp
390 Query specific parameters:
391 .INDENT 7.0
392 .TP
393 .B \fB%1\fP
394 ID of message
395 .UNINDENT
396 .UNINDENT
397 .INDENT 0.0
398 .TP
399 .B delete_outbox_multipart
400 Remove messages from outbox_multipart after threir successful send.
401 .sp
402 Default value:
403 .sp
404 .nf
405 .ft C
406 DELETE FROM outbox_multipart WHERE ID=%1
407 .ft P
408 .fi
409 .sp
410 Query specific parameters:
411 .INDENT 7.0
412 .TP
413 .B \fB%1\fP
414 ID of message
415 .UNINDENT
416 .UNINDENT
417 .INDENT 0.0
418 .TP
419 .B create_outbox
420 Create message (insert to outbox).
421 .sp
422 Default value:
423 .sp
424 .nf
425 .ft C
426 INSERT INTO outbox (CreatorID, SenderID, DeliveryReport, MultiPart,
427 InsertIntoDB, Text, DestinationNumber, RelativeValidity, Coding, UDH, Class,
428 TextDecoded) VALUES (%1, %P, %2, %3, NOW(), %E, %R, %V, %c, %u, %x, %T)
429 .ft P
430 .fi
431 .sp
432 Query specific parameters:
433 .INDENT 7.0
434 .TP
435 .B \fB%1\fP
436 creator of message
437 .TP
438 .B \fB%2\fP
439 delivery status report \- yes/default
440 .TP
441 .B \fB%3\fP
442 multipart \- FALSE/TRUE
443 .TP
444 .B \fB%4\fP
445 Part (part number)
446 .TP
447 .B \fB%5\fP
448 ID of message
449 .UNINDENT
450 .UNINDENT
451 .INDENT 0.0
452 .TP
453 .B create_outbox_multipart
454 Create message remaining parts.
455 .sp
456 Default value:
457 .sp
458 .nf
459 .ft C
460 INSERT INTO outbox_multipart (SequencePosition, Text, Coding, UDH, Class,
461 TextDecoded, ID) VALUES (%4, %E, %c, %u, %x, %T, %5)
462 .ft P
463 .fi
464 .sp
465 Query specific parameters:
466 .INDENT 7.0
467 .TP
468 .B \fB%1\fP
469 creator of message
470 .TP
471 .B \fB%2\fP
472 delivery status report \- yes/default
473 .TP
474 .B \fB%3\fP
475 multipart \- FALSE/TRUE
476 .TP
477 .B \fB%4\fP
478 Part (part number)
479 .TP
480 .B \fB%5\fP
481 ID of message
482 .UNINDENT
483 .UNINDENT
484 .INDENT 0.0
485 .TP
486 .B add_sent_info
487 Insert to sentitems.
488 .sp
489 Default value:
490 .sp
491 .nf
492 .ft C
493 INSERT INTO sentitems (CreatorID,ID,SequencePosition,Status,SendingDateTime,
494 SMSCNumber, TPMR, SenderID,Text,DestinationNumber,Coding,UDH,Class,TextDecoded,
495 InsertIntoDB,RelativeValidity)
496 VALUES (%A, %1, %2, %3, NOW(), %F, %4, %P, %E, %R, %c, %u, %x, %T, %5, %V)
497 .ft P
498 .fi
499 .sp
500 Query specific parameters:
501 .INDENT 7.0
502 .TP
503 .B \fB%1\fP
504 ID of sms message
505 .TP
506 .B \fB%2\fP
507 part number (for multipart sms)
508 .TP
509 .B \fB%3\fP
510 message state (SendingError, Error, SendingOK, SendingOKNoReport)
511 .TP
512 .B \fB%4\fP
513 message reference (TPMR)
514 .TP
515 .B \fB%5\fP
516 time when inserted in db
517 .UNINDENT
518 .UNINDENT
519 .INDENT 0.0
520 .TP
521 .B update_sent
522 Update sent statistics after sending message.
523 .sp
524 Default value:
525 .sp
526 .nf
527 .ft C
528 UPDATE phones SET Sent= Sent + 1 WHERE IMEI = %I
529 .ft P
530 .fi
531 .UNINDENT
532 .INDENT 0.0
533 .TP
534 .B refresh_phone_status
535 Update phone status (battery, signal).
536 .sp
537 Default value:
538 .sp
539 .nf
540 .ft C
541 UPDATE phones SET TimeOut= (NOW() + INTERVAL 10 SECOND) + 0,
542 Battery = %1, SignalStrength = %2 WHERE IMEI = %I
543 .ft P
544 .fi
545 .sp
546 Query specific parameters:
547 .INDENT 7.0
548 .TP
549 .B \fB%1\fP
550 battery percent
551 .TP
552 .B \fB%2\fP
553 signal percent
554 .UNINDENT
555 .UNINDENT
556 .SH AUTHOR
557 Michal Čihař <michal@cihar.com>
558 .SH COPYRIGHT
559 2009-2011, Michal Čihař <michal@cihar.com>
560 .\" Generated by docutils manpage writer.
561 .\"
562 .