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