| blob | 126083476372fd3f98ee018ae1cb870368134c1f |
1 .TH "GAMMU-SMSD-TABLES" "7" "January 19, 2011" "1.29.0" "Gammu"
2 .SH NAME
3 gammu-smsd-tables \- description of tables for database backends of gammu-smsd(1)
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 The backends themselves are described in their sections, this document
35 describes general database structure and required tables.
36 .sp
37 More SMS daemons can share single database. If you do not specify PhoneID in
38 their configuration, all are treated equally and you have no guarantee which
39 one sends outgoing message. If you configure PhoneID and use it when inserting
40 message to the \fBoutbox\fP table (\fIgammu\-smsd\-inject\fP does this), each SMS
41 daemon will have separate outbox queue.
42 .SH RECEIVING OF MESSAGES
43 .sp
44 Received messages are stored in \fBinbox\fP table.
45 .SH TRANSMITTING OF MESSAGES
46 .sp
47 Transmitted messages are read from table \fBoutbox\fP and possible subsequent parts
48 of the same message from \fBoutbox_multipart\fP.
49 .SH DESCRIPTION OF TABLES
50 .SS daemons
51 .sp
52 Information about running daemons.
53 .SS gammu
54 .sp
55 Table holding single value \- version of a database schema. See HISTORY for
56 details what has changed.
57 .SS inbox
58 .sp
59 Table where received messages will be stored.
60 .sp
61 Fields description:
62 .INDENT 0.0
63 .TP
64 .B \fBUpdatedInDB\fP (timestamp)
65 .sp
66 when somebody (daemon, user, etc.) updated it
67 .TP
68 .B \fBReceivingDateTime\fP (timestamp)
69 .sp
70 when SMS was received
71 .TP
72 .B \fBText\fP (text)
73 .sp
74 encoded SMS text (for all SMS)
75 .TP
76 .B \fBSenderNumber\fP (varchar(20))
77 .sp
78 decoded SMS sender number
79 .TP
80 .B \fBCoding\fP (enum(\(aqDefault_No_Compression\(aq, \(aqUnicode_No_Compression\(aq, \(aq8bit\(aq, \(aqDefault_Compression\(aq, \(aqUnicode_Compression\(aq))
81 .sp
82 SMS text coding
83 .TP
84 .B \fBUDH\fP (text)
85 .sp
86 encoded User Data Header text
87 .TP
88 .B \fBSMSCNumber\fP (varchar(20))
89 .sp
90 decoded SMSC number
91 .TP
92 .B \fBClass\fP (integer)
93 .sp
94 SMS class or \-1 (0 is flash SMS, 1 is normal one)
95 .TP
96 .B \fBTextDecoded\fP (varchar(160))
97 .sp
98 decoded SMS text (for Default Alphabet/Unicode SMS)
99 .TP
100 .B \fBID\fP (integer unsigned)
101 .sp
102 SMS identificator (for using with external applications)
103 .TP
104 .B \fBRecipientID\fP (text)
105 .sp
106 which Gammu daemon has added it
107 .TP
108 .B \fBProcessed\fP (enum(\(aqfalse\(aq, \(aqtrue\(aq))
109 .sp
110 you can use for marking, whether SMS was processed or not
111 .UNINDENT
112 .SS outbox
113 .sp
114 Messages enqueued for sending should be placed in this table. If message
115 is multipart, subsequent parts are stored in table outbox_multipart.
116 .sp
117 Fields description:
118 .INDENT 0.0
119 .TP
120 .B \fBUpdatedInDB\fP (timestamp)
121 .sp
122 when somebody (daemon, user, etc.) updated it
123 .TP
124 .B \fBInsertIntoDB\fP (timestamp)
125 .sp
126 when message was inserted into database
127 .TP
128 .B \fBSendingDateTime\fP (timestamp)
129 .sp
130 set it to some value, when want to force sending after some planned time
131 .TP
132 .B \fBText\fP (text)
133 .sp
134 SMS text encoded using hex values in proper coding. If you want to use
135 TextDecoded field, keep this NULL (or empty).
136 .TP
137 .B \fBDestinationNumber\fP (varchar(20))
138 .sp
139 recipient number
140 .TP
141 .B \fBCoding\fP (enum(\(aqDefault_No_Compression\(aq, \(aqUnicode_No_Compression\(aq, \(aq8bit\(aq, \(aqDefault_Compression\(aq, \(aqUnicode_Compression\(aq))
142 .sp
143 SMS text coding
144 .TP
145 .B \fBUDH\fP (text)
146 .sp
147 User Data Header encoded using hex values which will be used for constructing
148 the message. Without this, message will be sent as plain text.
149 .TP
150 .B \fBClass\fP (integer)
151 .sp
152 SMS class or \-1 (0 is normal SMS, 1 is flash one)
153 .TP
154 .B \fBTextDecoded\fP (varchar(160))
155 .sp
156 SMS text in "human readable" form
157 .TP
158 .B \fBID\fP (integer unsigned)
159 .sp
160 SMS/SMS sequence ID
161 .sp
162 Please note that this number has to be unique also for sentitems table, so
163 reusing message IDs might not be a good idea.
164 .TP
165 .B \fBMultiPart\fP (enum(\(aqfalse\(aq,\(aqtrue\(aq))
166 .sp
167 info, whether there are more SMS from this sequence in outbox_multipart
168 .TP
169 .B \fBRelativeValidity\fP (integer)
170 .sp
171 SMS relative validity like encoded using GSM specs
172 .TP
173 .B \fBSenderID\fP (text)
174 .sp
175 which SMSD instance should send this one sequence
176 .TP
177 .B \fBSendingTimeOut\fP (timestamp)
178 .sp
179 used by SMSD instance for own targets
180 .TP
181 .B \fBDeliveryReport\fP (enum(\(aqdefault\(aq,\(aqyes\(aq,\(aqno\(aq))
182 .sp
183 when default is used, Delivery Report is used or not according to SMSD instance settings; yes forces Delivery Report.
184 .TP
185 .B \fBCreatorID\fP (text)
186 .sp
187 sender identification, it has to match PhoneID in SMSD configuration to make
188 SMSD process this message
189 .UNINDENT
190 .SS outbox_multipart
191 .sp
192 Data for outgoing multipart messages.
193 .sp
194 Fields description:
195 .INDENT 0.0
196 .TP
197 .B \fBID\fP (integer unsigned)
198 .sp
199 the same meaning as values in outbox table
200 .TP
201 .B \fBText\fP (text)
202 .sp
203 the same meaning as values in outbox table
204 .TP
205 .B \fBCoding\fP (enum(\(aqDefault_No_Compression\(aq, \(aqUnicode_No_Compression\(aq, \(aq8bit\(aq, \(aqDefault_Compression\(aq, \(aqUnicode_Compression\(aq))
206 .sp
207 the same meaning as values in outbox table
208 .TP
209 .B \fBUDH\fP (text)
210 .sp
211 the same meaning as values in outbox table
212 .TP
213 .B \fBClass\fP (integer)
214 .sp
215 the same meaning as values in outbox table
216 .TP
217 .B \fBTextDecoded\fP (varchar(160))
218 .sp
219 the same meaning as values in outbox table
220 .TP
221 .B \fBID\fP (integer unsigned)
222 .sp
223 the same meaning as values in outbox table
224 .TP
225 .B \fBSequencePosition\fP (integer)
226 .sp
227 info, what is SMS number in SMS sequence (start at 2, first part is in outbox
228 table).
229 .UNINDENT
230 .SS phones
231 .sp
232 Information about connected phones. This table is periodically refreshed and
233 you can get information such as battery or signal level from here.
234 .sp
235 Fields description:
236 .INDENT 0.0
237 .TP
238 .B \fBID\fP (text)
239 .sp
240 PhoneID value
241 .TP
242 .B \fBUpdatedInDB\fP (timestamp)
243 .sp
244 when this record has been updated
245 .TP
246 .B \fBInsertIntoDB\fP (timestamp)
247 .sp
248 when this record has been created (when phone has been connected)
249 .TP
250 .B \fBTimeOut\fP (timestamp)
251 .sp
252 when this record expires
253 .TP
254 .B \fBSend\fP (boolean)
255 .sp
256 indicates whether SMSD is sending messages, depends on configuration directive \fBSend\fP
257 .TP
258 .B \fBReceive\fP (boolean)
259 .sp
260 indicates whether SMSD is receiving messages, depends on configuration directive \fBReceive\fP
261 .TP
262 .B \fBIMEI\fP (text)
263 .sp
264 IMEI of phone
265 .TP
266 .B \fBClient\fP (text)
267 .sp
268 client name, usually string Gammu with version
269 .TP
270 .B \fBBattery\fP (integer)
271 .sp
272 battery level in percent (or \-1 if unknown)
273 .TP
274 .B \fBSignal\fP (integer)
275 .sp
276 signal level in percent (or \-1 if unknown)
277 .TP
278 .B \fBSent\fP (integer)
279 .sp
280 Number of sent SMS messages (SMSD does not reset this counter, so it might
281 overflow).
282 .TP
283 .B \fBReceived\fP (integer)
284 .sp
285 Number of received SMS messages (SMSD does not reset this counter, so it might
286 overflow).
287 .UNINDENT
288 .SS sentitems
289 .sp
290 Log of sent messages (and unsent ones with error code). Also if delivery
291 reports are enabled, message state is updated after receiving delivery report.
292 .sp
293 Fields description:
294 .INDENT 0.0
295 .TP
296 .B \fBUpdatedInDB\fP (timestamp)
297 .sp
298 when somebody (daemon, user, etc.) updated it
299 .TP
300 .B \fBInsertIntoDB\fP (timestamp)
301 .sp
302 when message was inserted into database
303 .TP
304 .B \fBSendingDateTime\fP (timestamp)
305 .sp
306 when message has been sent
307 .TP
308 .B \fBDeliveryDateTime\fP (timestamp)
309 .sp
310 Time of receiving delivery report (if it has been enabled).
311 .TP
312 .B \fBStatus\fP (enum(\(aqSendingOK\(aq, \(aqSendingOKNoReport\(aq, \(aqSendingError\(aq, \(aqDeliveryOK\(aq, \(aqDeliveryFailed\(aq, \(aqDeliveryPending\(aq, \(aqDeliveryUnknown\(aq, \(aqError\(aq))
313 .sp
314 Status of message sending. SendingError mens that phone failed to send the
315 message, Error indicates some other error while processing message.
316 .INDENT 7.0
317 .TP
318 .B \fBSendingOK\fP
319 .sp
320 Message has been sent, waiting for delivery report.
321 .TP
322 .B \fBSendingOKNoReport\fP
323 .sp
324 Message has been sent without asking for delivery report.
325 .TP
326 .B \fBSendingError\fP
327 .sp
328 Sending has failed.
329 .TP
330 .B \fBDeliveryOK\fP
331 .sp
332 Delivery report arrived and reported success.
333 .TP
334 .B \fBDeliveryFailed\fP
335 .sp
336 Delivery report arrived and reports failure.
337 .TP
338 .B \fBDeliveryPending\fP
339 .sp
340 Delivery report announced pending deliver.
341 .TP
342 .B \fBDeliveryUnknown\fP
343 .sp
344 Delivery report reported unknown status.
345 .TP
346 .B \fBError\fP
347 .sp
348 Some other error happened during sending (usually bug in SMSD).
349 .UNINDENT
350 .TP
351 .B \fBStatusError\fP (integer)
352 .sp
353 Status of delivery from delivery report message, codes are defined in GSM
354 specification 03.40 section 9.2.3.15 (TP\-Status).
355 .TP
356 .B \fBText\fP (text)
357 .sp
358 SMS text encoded using hex values
359 .TP
360 .B \fBDestinationNumber\fP (varchar(20))
361 .sp
362 decoded destination number for SMS
363 .TP
364 .B \fBCoding\fP (enum(\(aqDefault_No_Compression\(aq, \(aqUnicode_No_Compression\(aq, \(aq8bit\(aq, \(aqDefault_Compression\(aq, \(aqUnicode_Compression\(aq))
365 .sp
366 SMS text coding
367 .TP
368 .B \fBUDH\fP (text)
369 .sp
370 User Data Header encoded using hex values
371 .TP
372 .B \fBSMSCNumber\fP (varchar(20))
373 .sp
374 decoded number of SMSC, which sent SMS
375 .TP
376 .B \fBClass\fP (integer)
377 .sp
378 SMS class or \-1 (0 is normal SMS, 1 is flash one)
379 .TP
380 .B \fBTextDecoded\fP (varchar(160))
381 .sp
382 SMS text in "human readable" form
383 .TP
384 .B \fBID\fP (integer unsigned)
385 .sp
386 SMS ID
387 .TP
388 .B \fBSenderID\fP (text)
389 .sp
390 which SMSD instance sent this one sequence
391 .TP
392 .B \fBSequencePosition\fP (integer)
393 .sp
394 SMS number in SMS sequence
395 .TP
396 .B \fBTPMR\fP (integer)
397 .sp
398 Message Reference like in GSM specs
399 .TP
400 .B \fBRelativeValidity\fP (integer)
401 .sp
402 SMS relative validity like encoded using GSM specs
403 .TP
404 .B \fBCreatorID\fP (text)
405 .sp
406 copied from CreatorID from outbox table, matches PhoneID
407 .UNINDENT
408 .SS pbk
409 .sp
410 Not used by SMSD currently, included only for application usage.
411 .SS pbk_groups
412 .sp
413 Not used by SMSD currently, included only for application usage.
414 .SH HISTORY
415 .sp
416 History of schema versions:
417 .INDENT 0.0
418 .TP
419 .B 12
420 .
421 the changes only affect MySQL structure changing default values for
422 timestamps from \fB0000\-00\-00 00:00:00\fP to \fBCURRENT_TIMESTAMP()\fP by
423 using triggers, to update to this version, just execute triggers
424 definition at the end of SQL file.
425 .TP
426 .B 11
427 .
428 all fields for storing message text are no longer limited to 160 chars,
429 but are arbitrary length text fields (1.25.92)
430 .TP
431 .B 10
432 .
433 \fBDeliveryDateTime\fP is now NULL when message is not delivered, added several
434 indexes
435 .TP
436 .B 9
437 .
438 added sent/received counters to phones table
439 .TP
440 .B 8
441 .
442 introduced phones table
443 .TP
444 .B 7
445 .
446 added CreatorID to tables (it holds PhoneID if set)
447 .UNINDENT
448 .SH EXAMPLES
449 .SS Creating tables
450 .sp
451 SQL scripts to create all needed tables for most databases are included in
452 Gammu documentation (docs/sql). As well as some PHP scripts interacting with
453 the database.
454 .sp
455 For example to create SQLite tables, issue following command:
456 .sp
457 .nf
458 .ft C
459 sqlite3 smsd.db < docs/sql/sqlite.sql
460 .ft P
461 .fi
462 .SS Injecting a message using SQL
463 .sp
464 To send a message, you can either use \fIgammu\-smsd\-inject\fP, which does all the
465 magic for you, or you can insert the message manually. The simplest example is
466 short text message:
467 .sp
468 .nf
469 .ft C
470 INSERT INTO outbox (
471 DestinationNumber,
472 TextDecoded,
473 CreatorID,
474 Coding
475 ) VALUES (
476 \(aq800123465\(aq,
477 \(aqThis is a SQL test message\(aq,
478 \(aqProgram\(aq,
479 \(aqDefault_No_Compression\(aq
480 );
481 .ft P
482 .fi
483 .SS Injecting long message using SQL
484 .sp
485 Inserting multipart messages is a bit more tricky, you need to construct also
486 UDH header and store it hexadecimally written into UDH field. Unless you have
487 a good reason to do this manually, use \fIgammu\-smsd\-inject\fP.
488 .sp
489 For long text message, the UDH starts with \fB050003\fP followed by byte as a
490 message reference (you can put anything there, but it should be different for
491 each message, \fBD3\fP in following example), byte for number of messages (\fB02\fP
492 in example, it should be unique for each message you send to same phone number)
493 and byte for number of current message (\fB01\fP for first message, \fB02\fP for
494 second, etc.).
495 .sp
496 For example long text message of two parts could look like following:
497 .sp
498 .nf
499 .ft C
500 INSERT INTO outbox (
501 CreatorID,
502 MultiPart,
503 DestinationNumber,
504 UDH,
505 TextDecoded,
506 Coding
507 ) VALUES (
508 \(aqGammu 1.23.91\(aq,
509 \(aqtrue\(aq,
510 \(aq123465\(aq,
511 \(aq050003D30201\(aq,
512 \(aqMqukqirip ya konej eqniu rejropocejor hugiygydewl tfej nrupxujob xuemymiyliralj. Te tvyjuh qaxumur ibewfoiws zuucoz tdygu gelum L ejqigqesykl kya jdytbez\(aq,
513 \(aqDefault_No_Compression\(aq
514 )
516 INSERT INTO outbox_multipart (
517 SequencePosition,
518 UDH,
519 Class,
520 TextDecoded,
521 ID,
522 Coding
523 ) VALUES (
524 2,
525 \(aq050003D30202\(aq,
526 \(aqu xewz qisubevumxyzk ufuylehyzc. Nse xobq dfolizygqysj t bvowsyhyhyemim ovutpapeaempye giuuwbib.\(aq,
527 <ID_OF_INSERTED_RECORD_IN_OUBOX_TABLE>,
528 \(aqDefault_No_Compression\(aq
529 )
530 .ft P
531 .fi
532 .IP Note
533 .
534 Adding UDH means that you have less space for text, in above example you
535 can use only 153 characters in single message.
536 .RE
537 .SH AUTHOR
538 Michal Čihař <michal@cihar.com>
539 .SH COPYRIGHT
540 2009-2011, Michal Čihař <michal@cihar.com>
541 .\" Generated by docutils manpage writer.
542 .\"
543 .