1GAMMU-SMSD-TABLES(7) Gammu Documentation GAMMU-SMSD-TABLES(7)
2
6 gammu-smsd-tables - description of tables for database backends of
7 gammu-smsd(1)
8
11 gammu-smsd(1) supports several backends. Actual backend is chosen in a
12 config file gammu-smsdrc(5).
13 Separate backends are described in their appropriate man pages. This
15 manual page describes general database structure and required tables.
16 More SMS daemons can share single database. If you do not specify
18 PhoneID in their configuration, all are treated equally and you have no
19 guarantee which one sends outgoing message. If you configure PhoneID
20 and use it when inserting message to the outbox table
21 (gammu-smsd-inject(1) does this), each SMS daemon will have separate
22 outbox queue.
23 Receiving of messages
26 Received messages are stored in inbox table.
27 Transmitting of messages
30 Transmitted messages are read from table outbox and possible subsequent
31 parts of the same message from outbox_multipart.
32 Description of tables
35 daemons
36 Information about running daemons.
38 gammu
41 Table holding single value - version of a database schema. See
43 HISTORY for details what has changed.
44 inbox
47 Table where received messages will be stored.
49 Fields description:
51 UpdatedInDB (timestamp)
53 when somebody (daemon, user, etc.) updated it
54 ReceivingDateTime (timestamp)
56 when SMS was received
57 Text (text)
59 encoded SMS text (for all SMS)
60 SenderNumber (varchar(20))
62 decoded SMS sender number
63 Coding (enum('Default_No_Compression', 'Unicode_No_Compression',
65 '8bit', 'Default_Compression', 'Unicode_Compression'))
66 SMS text coding
67 UDH (text)
69 encoded User Data Header text
70 SMSCNumber (varchar(20))
72 decoded SMSC number
73 Class (integer)
75 SMS class or -1 (0 is flash SMS, 1 is normal one)
76 TextDecoded (varchar(160))
78 decoded SMS text (for Default Alphabet/Unicode SMS)
79 ID (integer unsigned)
81 SMS identificator (for using with external applications)
82 RecipientID (text)
84 which Gammu daemon has added it
85 Processed (enum('false', 'true'))
87 you can use for marking, whether SMS was processed or not
88 outbox
92 Messages enqueued for sending should be placed in this table. If
94 message is multipart, subsequent parts are stored in table out‐
95 box_multipart.
96 Fields description:
98 UpdatedInDB (timestamp)
100 when somebody (daemon, user, etc.) updated it
101 InsertIntoDB (timestamp)
103 when message was inserted into database
104 SendingDateTime (timestamp)
106 set it to some value, when want to force sending after some
107 planned time
108 Text (text)
110 SMS text encoded using hex values in proper coding. If you want
111 to use TextDecoded field, keep this NULL (or empty).
112 DestinationNumber (varchar(20))
114 recipient number
115 Coding (enum('Default_No_Compression', 'Unicode_No_Compression',
117 '8bit', 'Default_Compression', 'Unicode_Compression'))
118 SMS text coding
119 UDH (text)
121 User Data Header encoded using hex values which will be used for
122 constructing the message. Without this, message will be sent as
123 plain text.
124 Class (integer)
126 SMS class or -1 (0 is normal SMS, 1 is flash one)
127 TextDecoded (varchar(160))
129 SMS text in "human readable" form
130 ID (integer unsigned)
132 SMS/SMS sequence ID
133 Please note that this number has to be unique also for sentitems
135 table, so reusing message IDs might not be a good idea.
136 MultiPart (enum('false','true'))
138 info, whether there are more SMS from this sequence in out‐
139 box_multipart
140 RelativeValidity (integer)
142 SMS relative validity like encoded using GSM specs
143 SenderID (text)
145 which SMSD instance should send this one sequence
146 SendingTimeOut (timestamp)
148 used by SMSD instance for own targets
149 DeliveryReport (enum('default','yes','no'))
151 when default is used, Delivery Report is used or not according
152 to SMSD instance settings; yes forces Delivery Report.
153 CreatorID (text)
155 sender identification, it has to match PhoneID in SMSD configu‐
156 ration to make SMSD process this message
157 outbox_multipart
163 Data for outgoing multipart messages.
165 Fields description:
167 ID (integer unsigned)
169 Text (text)
170 Coding (enum('Default_No_Compression', 'Unicode_No_Compression',
171 '8bit', 'Default_Compression', 'Unicode_Compression'))
172 UDH (text)
173 Class (integer)
174 TextDecoded (varchar(160))
175 ID (integer unsigned)
176 the same meaning as values in outbox table
177 SequencePosition (integer)
179 info, what is SMS number in SMS sequence (start at 2, first part
180 is in outbox table).
181 phones
185 Information about connected phones. This table is periodically
187 refreshed and you can get information such as battery or signal
188 level from here.
189 Fields description:
191 ID (text)
193 PhoneID value
194 UpdatedInDB (timestamp)
196 when this record has been updated
197 InsertIntoDB (timestamp)
199 when this record has been created (when phone has been con‐
200 nected)
201 TimeOut (timestamp)
203 when this record expires
204 Send (boolean)
206 currently always true
207 Receive (boolean)
209 currently always true
210 IMEI (text)
212 IMEI of phone
213 Client (text)
215 client name, usually string Gammu with version
216 Battery (integer)
218 battery level in percent (or -1 if unknown)
219 Signal (integer)
221 signal level in percent (or -1 if unknown)
222 Sent (integer)
224 Number of sent SMS messages (SMSD does not reset this counter,
225 so it might overflow).
226 Received (integer)
228 Number of received SMS messages (SMSD does not reset this
229 counter, so it might overflow).
230 sentitems
233 Log of sent messages (and unsent ones with error code). Also if
235 delivery reports are enabled, message state is updated after
236 receiving delivery report.
237 Fields description:
239 UpdatedInDB (timestamp)
241 when somebody (daemon, user, etc.) updated it
242 InsertIntoDB (timestamp)
244 when message was inserted into database
245 SendingDateTime (timestamp)
247 when message has been sent
248 DeliveryDateTime (timestamp)
250 Time of receiving delivery report (if it has been enabled).
251 Status (enum('SendingOK', 'SendingOKNoReport', 'SendingError',
253 'DeliveryOK', 'DeliveryFailed', 'DeliveryPending', 'DeliveryUn‐
254 known', 'Error'))
255 Status of message sending. SendingError mens that phone failed
256 to send the message, Error indicates some other error while pro‐
257 cessing message.
258 - SendingOK - Message has been sent, waiting for delivery
260 report.
261 - SendingOKNoReport - Message has been sent without asking for
262 delivery report.
263 - SendingError - Sending has failed.
264 - DeliveryOK - Delivery report arrived and reported success.
265 - DeliveryFailed - Delivery report arrived and reports failure.
266 - DeliveryPending - Delivery report announced pending deliver.
267 - DeliveryUnknown - Delivery report reported unknown status.
268 - Error - Some other error happened during sending (usually bug
269 in SMSD).
270 StatusError (integer)
272 Status of delivery from delivery report message, codes are
273 defined in GSM specification.
274 Text (text)
276 SMS text encoded using hex values
277 DestinationNumber (varchar(20))
279 decoded destination number for SMS
280 Coding (enum('Default_No_Compression', 'Unicode_No_Compression',
282 '8bit', 'Default_Compression', 'Unicode_Compression'))
283 SMS text coding
284 UDH (text)
286 User Data Header encoded using hex values
287 SMSCNumber (varchar(20))
289 decoded number of SMSC, which sent SMS
290 Class (integer)
292 SMS class or -1 (0 is normal SMS, 1 is flash one)
293 TextDecoded (varchar(160))
295 SMS text in "human readable" form
296 ID (integer unsigned)
298 SMS ID
299 SenderID (text)
301 Injecting a message using SQL
303 which SMSD instance sent this one sequence
304 SequencePosition (integer)
306 SMS number in SMS sequence
307 TPMR (integer)
309 Message Reference like in GSM specs
310 RelativeValidity (integer)
312 SMS relative validity like encoded using GSM specs
313 CreatorID (text)
315 copied from CreatorID from outbox table, matches PhoneID
316 pbk
320 Not used by SMSD currently, included only for application usage.
322 pbk_groups
325 Not used by SMSD currently, included only for application usage.
327
330 History of schema versions:
331 11 - all fields for storing message text are no longer limited to 160
333 chars, but are arbitrary length text fields (1.25.92)
334 10 - DeliveryDateTime is now NULL when message is not delivered, added
336 several indexes
337 9 - added sent/received counters to phones table
339 8 - introduced phones table
341 7 - added CreatorID to tables (it holds PhoneID if set)
343
346 Creating tables
347 SQL scripts to create all needed tables for most databases are included
348 in Gammu documentation (docs/sql). As well as some PHP scripts inter‐
349 acting with the database.
350 For example to create SQLite tables, issue following command:
352 sqlite3 smsd.db < docs/sql/sqlite.sql
354 Injecting a message using SQL
357 To send a message, you can either use gammu-smsd-inject(1), which does
358 all the magic for you, or you can insert the message manually. The sim‐
359 plest example is short text message:
360 INSERT INTO outbox (
362 DestinationNumber,
363 TextDecoded,
364 CreatorID,
365 Coding
366 ) VALUES (
367 '800123465',
368 'This is a SQL test message',
369 'Program',
370 'Default_No_Compression'
371 );
372 Injecting long message using SQL
375 Inserting multipart messages is a bit more tricky, you need to con‐
376 struct also UDH header. For example long text message would look like
377 (please note that each UDH needs to contain number of parts and
378 sequence position as last two bytes, the D3 value in example is message
379 reference number, it can be anything):
380 INSERT INTO outbox (
382 CreatorID,
383 MultiPart,
384 DestinationNumber,
385 UDH,
386 TextDecoded,
387 Coding
388 ) VALUES (
389 'Gammu 1.23.91',
390 'true',
391 '123465',
392 '050003D30201',
393 'Mqukqirip ya konej eqniu rejropocejor hugiygydewl tfej nrupxu‐
394 job xuemymiyliralj. Te tvyjuh qaxumur ibewfoiws zuucoz tdygu gelum L
395 ejqigqesykl kya jdytbez',
396 'Default_No_Compression'
397 )
398 INSERT INTO outbox_multipart (
400 SequencePosition,
401 UDH,
402 Class,
403 TextDecoded,
404 ID,
405 Coding
406 ) VALUES (
407 2,
408 '050003D30202',
409 'u xewz qisubevumxyzk ufuylehyzc. Nse xobq dfolizygqysj t
410 bvowsyhyhyemim ovutpapeaempye giuuwbib.',
411 <ID_OF_INSERTED_RECORD_IN_OUBOX_TABLE>,
412 'Default_No_Compression'
413 )
414
417 Database backends: gammu-smsd-mysql(7), gammu-smsd-pgsql(7),
418 gammu-smsd-dbi(7)
419 gammu-smsd(1), gammu-smsdrc(5), gammu(1), gammurc(5)
421
423 gammu-smsd and this manual page were written by Michal Cihar
424 <michal@cihar.com>.
425
427 Copyright © 2009 Michal Cihar and other authors. License GPLv2: GNU
428 GPL version 2 <http://www.gnu.org/licenses/old-licenses/gpl-2.0.html>
429 This is free software: you are free to change and redistribute it.
430 There is NO WARRANTY, to the extent permitted by law.
431
433 Please report bugs to <http://bugs.cihar.com>.
434Gammu 1.26.0 August 17, 2009 GAMMU-SMSD-TABLES(7)