1GAMMU-SMSD-TABLES(7) Gammu GAMMU-SMSD-TABLES(7)
2
6 gammu-smsd-tables - description of tables for database backends of
7 gammu-smsd(1)
8 The backends themselves are described in their sections, this document
10 describes general database structure and required tables.
11 More SMS daemons can share single database. If you do not specify
13 PhoneID in their configuration, all are treated equally and you have no
14 guarantee which one sends outgoing message. If you configure PhoneID
15 and use it when inserting message to the outbox table
16 (gammu-smsd-inject does this), each SMS daemon will have separate out‐
17 box queue. See also smsd-multi.
18
20 Received messages are stored in inbox table.
21
23 Transmitted messages are read from table outbox and possible subsequent
24 parts of the same message from outbox_multipart.
25
27 gammu
28 Table holding single field Version - version of a database schema. See
29 History of database structure for details what has changed.
30 inbox
32 Table where received messages will be stored.
33 Fields description:
35 UpdatedInDB (timestamp)
37 when somebody (daemon, user, etc.) updated it
38 ReceivingDateTime (timestamp)
40 when SMS was received
41 Text (text)
43 encoded SMS text (for all SMS)
44 SenderNumber (varchar(20))
46 decoded SMS sender number
47 Coding (enum(‘Default_No_Compression’, ‘Unicode_No_Compression’,
49 ‘8bit’, ‘Default_Compression’, ‘Unicode_Compression’))
50 SMS text coding
51 UDH (text)
53 encoded User Data Header text
54 SMSCNumber (varchar(20))
56 decoded SMSC number
57 Class (integer)
59 SMS class or -1 (0 is flash SMS, 1 is normal one, 127 is USSD)
60 TextDecoded (varchar(160))
62 decoded SMS text (for Default Alphabet/Unicode SMS)
63 ID (integer unsigned)
65 SMS identificator (for using with external applications)
66 RecipientID (text)
68 which Gammu daemon has added it
69 Processed (enum(‘false’, ‘true’))
71 you can use for marking, whether SMS was processed or not
72 Status (integer)
74 Status of incoming message. Currently only used for Class 127
75 (USSD) messages with following meaning:
76 1 Unknown status.
78 2 No action is needed, maybe network initiated USSD.
80 3 Reply is expected.
82 4 USSD dialog terminated.
84 5 Another client replied.
86 6 Operation not supported.
88 7 Network timeout.
90 New in version 1.38.5.
92 outbox
95 Messages enqueued for sending should be placed in this table. If mes‐
96 sage is multipart, subsequent parts are stored in table
97 outbox_multipart.
98 Fields description:
100 UpdatedInDB (timestamp)
102 when somebody (daemon, user, etc.) updated it
103 InsertIntoDB (timestamp)
105 when message was inserted into database
106 SendingDateTime (timestamp)
108 set it to some value, when want to force sending after some
109 planned time
110 SendBefore (time)
112 Send message before specified time, can be used to limit mes‐
113 sages from being sent in night. Default value is 23:59:59
114 New in version 1.29.90.
116 SendAfter (time)
119 Send message after specified time, can be used to limit messages
120 from being sent in night. Default value is 00:00:00
121 New in version 1.29.90.
123 Text (text)
126 SMS text encoded using hex values in proper coding. If you want
127 to use TextDecoded field, keep this NULL (or empty).
128 DestinationNumber (varchar(20))
130 recipient number
131 Coding (enum(‘Default_No_Compression’, ‘Unicode_No_Compression’,
133 ‘8bit’, ‘Default_Compression’, ‘Unicode_Compression’))
134 SMS text coding
135 UDH (text)
137 User Data Header encoded using hex values which will be used for
138 constructing the message. Without this, message will be sent as
139 plain text.
140 Class (integer)
142 SMS class or -1 (0 is normal SMS, 1 is flash one, 127 is USSD)
143 TextDecoded (varchar(160))
145 SMS text in “human readable” form
146 ID (integer unsigned)
148 SMS/SMS sequence ID
149 Please note that this number has to be unique also for sentitems
151 table, so reusing message IDs might not be a good idea.
152 MultiPart (enum(‘false’,’true’))
154 info, whether there are more SMS from this sequence in out‐
155 box_multipart
156 RelativeValidity (integer)
158 SMS relative validity like encoded using GSM specs
159 SenderID (text)
161 which SMSD instance should send this one sequence, see PhoneID
162 and smsd-multi. If blank, first SMSD who sees this message first
163 will process it.
164 SendingTimeOut (timestamp)
166 used by SMSD instance for own targets
167 DeliveryReport (enum(‘default’,’yes’,’no’))
169 when default is used, Delivery Report is used or not according
170 to SMSD instance settings; yes forces Delivery Report.
171 CreatorID (text)
173 identification of program created the message
174 Retries (integer)
176 number of attempted retries when sending this message
177 Priority (integer)
179 priority of message, messages with higher priority are processed
180 first
181 Status (enum(‘SendingOK’, ‘SendingOKNoReport’, ‘SendingError’, ‘Deliv‐
183 eryOK’, ‘DeliveryFailed’, ‘DeliveryPending’, ‘DeliveryUnknown’,
184 ‘Error’, ‘Reserved’))
185 Status of message sending. SendingError means that phone failed
186 to send the message, Error indicates some other error while pro‐
187 cessing message.
188 SendingOK
190 Message has been sent, waiting for delivery report.
191 SendingOKNoReport
193 Message has been sent without asking for delivery report.
194 SendingError
196 Sending has failed.
197 DeliveryOK
199 Delivery report arrived and reported success.
200 DeliveryFailed
202 Delivery report arrived and reports failure.
203 DeliveryPending
205 Delivery report announced pending deliver.
206 DeliveryUnknown
208 Delivery report reported unknown status.
209 Error Some other error happened during sending (usually bug in
211 SMSD).
212 Reserved
214 Initial value, meaning the status has not been set.
215 New in version 1.38.5.
217 StatusCode (integer)
220 GSM status code
221 New in version 1.38.5.
223 outbox_multipart
226 Data for outgoing multipart messages.
227 Fields description:
229 ID (integer unsigned)
231 the same meaning as values in outbox table
232 Text (text)
234 the same meaning as values in outbox table
235 Coding (enum(‘Default_No_Compression’, ‘Unicode_No_Compression’,
237 ‘8bit’, ‘Default_Compression’, ‘Unicode_Compression’))
238 the same meaning as values in outbox table
239 UDH (text)
241 the same meaning as values in outbox table
242 Class (integer)
244 the same meaning as values in outbox table
245 TextDecoded (varchar(160))
247 the same meaning as values in outbox table
248 ID (integer unsigned)
250 the same meaning as values in outbox table
251 SequencePosition (integer)
253 info, what is SMS number in SMS sequence (start at 2, first part
254 is in outbox table).
255 Status (enum(‘SendingOK’, ‘SendingOKNoReport’, ‘SendingError’, ‘Deliv‐
257 eryOK’, ‘DeliveryFailed’, ‘DeliveryPending’, ‘DeliveryUnknown’,
258 ‘Error’, ‘Reserved’))
259 Status of message sending. SendingError means that phone failed
260 to send the message, Error indicates some other error while pro‐
261 cessing message.
262 SendingOK
264 Message has been sent, waiting for delivery report.
265 SendingOKNoReport
267 Message has been sent without asking for delivery report.
268 SendingError
270 Sending has failed.
271 DeliveryOK
273 Delivery report arrived and reported success.
274 DeliveryFailed
276 Delivery report arrived and reports failure.
277 DeliveryPending
279 Delivery report announced pending deliver.
280 DeliveryUnknown
282 Delivery report reported unknown status.
283 Error Some other error happened during sending (usually bug in
285 SMSD).
286 Reserved
288 Initial value, meaning the status has not been set.
289 New in version 1.38.5.
291 StatusCode (integer)
294 GSM status code
295 New in version 1.38.5.
297 phones
300 Information about connected phones. This table is periodically
301 refreshed and you can get information such as battery or signal level
302 from here.
303 Fields description:
305 ID (text)
307 PhoneID value
308 UpdatedInDB (timestamp)
310 when this record has been updated
311 InsertIntoDB (timestamp)
313 when this record has been created (when phone has been con‐
314 nected)
315 TimeOut (timestamp)
317 when this record expires
318 Send (boolean)
320 indicates whether SMSD is sending messages, depends on configu‐
321 ration directive Send
322 Receive (boolean)
324 indicates whether SMSD is receiving messages, depends on config‐
325 uration directive Receive
326 IMEI (text)
328 IMEI of phone
329 IMSI (text)
331 SIM IMSI
332 Client (text)
334 client name, usually string Gammu with version
335 Battery (integer)
337 battery level in percent (or -1 if unknown)
338 Signal (integer)
340 signal level in percent (or -1 if unknown)
341 Sent (integer)
343 Number of sent SMS messages (SMSD does not reset this counter,
344 so it might overflow).
345 Received (integer)
347 Number of received SMS messages (SMSD does not reset this
348 counter, so it might overflow).
349 sentitems
351 Log of sent messages (and unsent ones with error code). Also if deliv‐
352 ery reports are enabled, message state is updated after receiving
353 delivery report.
354 Fields description:
356 UpdatedInDB (timestamp)
358 when somebody (daemon, user, etc.) updated it
359 InsertIntoDB (timestamp)
361 when message was inserted into database
362 SendingDateTime (timestamp)
364 when message has been sent
365 DeliveryDateTime (timestamp)
367 Time of receiving delivery report (if it has been enabled).
368 Status (enum(‘SendingOK’, ‘SendingOKNoReport’, ‘SendingError’, ‘Deliv‐
370 eryOK’, ‘DeliveryFailed’, ‘DeliveryPending’, ‘DeliveryUnknown’,
371 ‘Error’))
372 Status of message sending. SendingError means that phone failed
373 to send the message, Error indicates some other error while pro‐
374 cessing message.
375 SendingOK
377 Message has been sent, waiting for delivery report.
378 SendingOKNoReport
380 Message has been sent without asking for delivery report.
381 SendingError
383 Sending has failed.
384 DeliveryOK
386 Delivery report arrived and reported success.
387 DeliveryFailed
389 Delivery report arrived and reports failure.
390 DeliveryPending
392 Delivery report announced pending deliver.
393 DeliveryUnknown
395 Delivery report reported unknown status.
396 Error Some other error happened during sending (usually bug in
398 SMSD).
399 StatusError (integer)
401 Status of delivery from delivery report message, codes are
402 defined in GSM specification 03.40 section 9.2.3.15 (TP-Status).
403 Text (text)
405 SMS text encoded using hex values
406 DestinationNumber (varchar(20))
408 decoded destination number for SMS
409 Coding (enum(‘Default_No_Compression’, ‘Unicode_No_Compression’,
411 ‘8bit’, ‘Default_Compression’, ‘Unicode_Compression’))
412 SMS text coding
413 UDH (text)
415 User Data Header encoded using hex values
416 SMSCNumber (varchar(20))
418 decoded number of SMSC, which sent SMS
419 Class (integer)
421 SMS class or -1 (0 is normal SMS, 1 is flash one, 127 is USSD)
422 TextDecoded (varchar(160))
424 SMS text in “human readable” form
425 ID (integer unsigned)
427 SMS ID
428 SenderID (text)
430 which SMSD instance sent this one sequence, see PhoneID
431 SequencePosition (integer)
433 SMS number in SMS sequence
434 TPMR (integer)
436 Message Reference like in GSM specs
437 RelativeValidity (integer)
439 SMS relative validity like encoded using GSM specs
440 CreatorID (text)
442 copied from CreatorID from outbox table
443 StatusCode (integer)
445 GSM status code
446 New in version 1.38.5.
448
451 NOTE:
452 Testing versions (see versioning) do not have to keep same table
453 structure as final releases. Below mentioned versions are for infor‐
454 mational purposes only, you should always use stable versions in
455 production environment.
456 History of schema versions:
458 17
460 · Added Status field to outbox and outbox_multipart.
462 · Added StatusCode field to sentitems, outbox and
464 outbox_multipart.
465 · Added Status field to inbox.
467 Changed in version 1.38.5.
469 16
472 · Removed unused daemons, pbk and pbk_groups tables.
474 · Added primary key to the gammu table.
476 · Added Priority field to the outbox.
478 · Added IMSI field to the phones.
480 Changed in version 1.37.90.
482 15
485 Added Retries field to the outbox.
486 Changed in version 1.36.7.
488 14
491 Added NetCode and NetName fields.
492 Changed in version 1.34.0.
494 13 Added SendBefore and SendAfter fields.
497 Changed in version 1.29.90.
499 Also PostgreSQL fields are now case sensitive (same as other
502 backends).
503 Changed in version 1.29.93.
505 12 the changes only affect MySQL structure changing default values
508 for timestamps from 0000-00-00 00:00:00 to CURRENT_TIMESTAMP()
509 by using triggers, to update to this version, just execute trig‐
510 gers definition at the end of SQL file.
511 Changed in version 1.28.94.
513 11 all fields for storing message text are no longer limited to 160
516 chars, but are arbitrary length text fields.
517 Changed in version 1.25.92.
519 10 DeliveryDateTime is now NULL when message is not delivered,
522 added several indexes
523 Changed in version 1.22.95.
525 9 added sent/received counters to phones table
528 Changed in version 1.22.93.
530 8 Signal and battery state are now stored in database.
533 Changed in version 1.20.94.
535 7 Added CreatorID to several tables.
538 Changed in version 1.07.00.
540 6 Many fields in outbox can now be NULL.
543 Changed in version 1.06.00.
545 5 Introduced daemons table and various other changes.
548 Changed in version 1.03.00.
550 3 Introduced phones table and various other changes.
553 Changed in version 0.98.0.
555
558 Creating tables
559 SQL scripts to create all needed tables for most databases are included
560 in Gammu documentation (docs/sql). As well as some PHP scripts inter‐
561 acting with the database.
562 For example to create SQLite tables, issue following command:
564 sqlite3 smsd.db < docs/sql/sqlite.sql
566 Injecting a message using SQL
568 To send a message, you can either use gammu-smsd-inject, which does all
569 the magic for you, or you can insert the message manually. The simplest
570 example is short text message:
571 INSERT INTO outbox (
573 DestinationNumber,
574 TextDecoded,
575 CreatorID,
576 Coding
577 ) VALUES (
578 '800123465',
579 'This is a SQL test message',
580 'Program',
581 'Default_No_Compression'
582 );
583 Please note usage of TextDecoded field, for Text field, you would have
585 to hex encode the unicode text:
586 INSERT INTO outbox (
588 DestinationNumber,
589 Text,
590 CreatorID,
591 Coding
592 ) VALUES (
593 '800123465',
594 '005400680069007300200069007300200061002000530051004c002000740065007300740020006d006500730073006100670065',
595 'Program',
596 'Default_No_Compression'
597 );
598 Injecting long message using SQL
600 Inserting multipart messages is a bit more tricky, you need to con‐
601 struct also UDH header and store it hexadecimally written into UDH
602 field. Unless you have a good reason to do this manually, use
603 gammu-smsd-inject, C library (SMSD_InjectSMS()) or Python library
604 (gammu.smsd.SMSD.InjectSMS()).
605 For long text message, the UDH starts with 050003 followed by byte as a
607 message reference (you can put any hex value there, but it should be
608 different for each message, D3 in following example), byte for number
609 of messages (02 in example, it should be unique for each message you
610 send to same phone number) and byte for number of current message (01
611 for first message, 02 for second, etc.).
612 I most cases, the mutlipart message has to be class 1.
614 For example long text message of two parts could look like following:
616 INSERT INTO outbox (
618 CreatorID,
619 MultiPart,
620 DestinationNumber,
621 UDH,
622 TextDecoded,
623 Coding,
624 Class
625 ) VALUES (
626 'Gammu 1.23.91',
627 'true',
628 '123465',
629 '050003D30201',
630 'Mqukqirip ya konej eqniu rejropocejor hugiygydewl tfej nrupxujob xuemymiyliralj. Te tvyjuh qaxumur ibewfoiws zuucoz tdygu gelum L ejqigqesykl kya jdytbez',
631 'Default_No_Compression',
632 1
633 )
634 INSERT INTO outbox_multipart (
636 SequencePosition,
637 UDH,
638 Class,
639 TextDecoded,
640 ID,
641 Coding,
642 Class
643 ) VALUES (
644 2,
645 '050003D30202',
646 'u xewz qisubevumxyzk ufuylehyzc. Nse xobq dfolizygqysj t bvowsyhyhyemim ovutpapeaempye giuuwbib.',
647 <ID_OF_INSERTED_RECORD_IN_OUBOX_TABLE>,
648 'Default_No_Compression',
649 1
650 )
651 NOTE:
653 Adding UDH means that you have less space for text, in above example
654 you can use only 153 characters in single message.
655
657 Michal Čihař <michal@cihar.com>
658
660 2009-2015, Michal Čihař <michal@cihar.com>
6611.39.0 Jan 05, 2018 GAMMU-SMSD-TABLES(7)