bionic (7) gammu-smsd-tables.7.gz
NAME
gammu-smsd-tables - description of tables for database backends of gammu-smsd(1) The backends themselves are described in their sections, this document describes general database structure and required tables. More SMS daemons can share single database. If you do not specify PhoneID in their configuration, all are treated equally and you have no guarantee which one sends outgoing message. If you configure PhoneID and use it when inserting message to the outbox table (gammu-smsd-inject does this), each SMS daemon will have separate outbox queue. See also smsd-multi.
RECEIVING OF MESSAGES
Received messages are stored in inbox table.
TRANSMITTING OF MESSAGES
Transmitted messages are read from table outbox and possible subsequent parts of the same message from
outbox_multipart.
DESCRIPTION OF TABLES
gammu
Table holding single field Version - version of a database schema. See History of database structure for
details what has changed.
inbox
Table where received messages will be stored.
Fields description:
UpdatedInDB (timestamp)
when somebody (daemon, user, etc.) updated it
ReceivingDateTime (timestamp)
when SMS was received
Text (text)
encoded SMS text (for all SMS)
SenderNumber (varchar(20))
decoded SMS sender number
Coding (enum(‘Default_No_Compression’, ‘Unicode_No_Compression’, ‘8bit’, ‘Default_Compression’,
‘Unicode_Compression’))
SMS text coding
UDH (text)
encoded User Data Header text
SMSCNumber (varchar(20))
decoded SMSC number
Class (integer)
SMS class or -1 (0 is flash SMS, 1 is normal one, 127 is USSD)
TextDecoded (varchar(160))
decoded SMS text (for Default Alphabet/Unicode SMS)
ID (integer unsigned)
SMS identificator (for using with external applications)
RecipientID (text)
which Gammu daemon has added it
Processed (enum(‘false’, ‘true’))
you can use for marking, whether SMS was processed or not
Status (integer)
Status of incoming message. Currently only used for Class 127 (USSD) messages with following
meaning:
1 Unknown status.
2 No action is needed, maybe network initiated USSD.
3 Reply is expected.
4 USSD dialog terminated.
5 Another client replied.
6 Operation not supported.
7 Network timeout.
New in version 1.38.5.
outbox
Messages enqueued for sending should be placed in this table. If message is multipart, subsequent parts
are stored in table outbox_multipart.
Fields description:
UpdatedInDB (timestamp)
when somebody (daemon, user, etc.) updated it
InsertIntoDB (timestamp)
when message was inserted into database
SendingDateTime (timestamp)
set it to some value, when want to force sending after some planned time
SendBefore (time)
Send message before specified time, can be used to limit messages from being sent in night.
Default value is 23:59:59
New in version 1.29.90.
SendAfter (time)
Send message after specified time, can be used to limit messages from being sent in night. Default
value is 00:00:00
New in version 1.29.90.
Text (text)
SMS text encoded using hex values in proper coding. If you want to use TextDecoded field, keep
this NULL (or empty).
DestinationNumber (varchar(20))
recipient number
Coding (enum(‘Default_No_Compression’, ‘Unicode_No_Compression’, ‘8bit’, ‘Default_Compression’,
‘Unicode_Compression’))
SMS text coding
UDH (text)
User Data Header encoded using hex values which will be used for constructing the message. Without
this, message will be sent as plain text.
Class (integer)
SMS class or -1 (0 is normal SMS, 1 is flash one, 127 is USSD)
TextDecoded (varchar(160))
SMS text in “human readable” form
ID (integer unsigned)
SMS/SMS sequence ID
Please note that this number has to be unique also for sentitems table, so reusing message IDs
might not be a good idea.
MultiPart (enum(‘false’,’true’))
info, whether there are more SMS from this sequence in outbox_multipart
RelativeValidity (integer)
SMS relative validity like encoded using GSM specs
SenderID (text)
which SMSD instance should send this one sequence, see PhoneID and smsd-multi. If blank, first
SMSD who sees this message first will process it.
SendingTimeOut (timestamp)
used by SMSD instance for own targets
DeliveryReport (enum(‘default’,’yes’,’no’))
when default is used, Delivery Report is used or not according to SMSD instance settings; yes
forces Delivery Report.
CreatorID (text)
identification of program created the message
Retries (integer)
number of attempted retries when sending this message
Priority (integer)
priority of message, messages with higher priority are processed first
Status (enum(‘SendingOK’, ‘SendingOKNoReport’, ‘SendingError’, ‘DeliveryOK’, ‘DeliveryFailed’,
‘DeliveryPending’, ‘DeliveryUnknown’, ‘Error’, ‘Reserved’))
Status of message sending. SendingError means that phone failed to send the message, Error
indicates some other error while processing message.
SendingOK
Message has been sent, waiting for delivery report.
SendingOKNoReport
Message has been sent without asking for delivery report.
SendingError
Sending has failed.
DeliveryOK
Delivery report arrived and reported success.
DeliveryFailed
Delivery report arrived and reports failure.
DeliveryPending
Delivery report announced pending deliver.
DeliveryUnknown
Delivery report reported unknown status.
Error Some other error happened during sending (usually bug in SMSD).
Reserved
Initial value, meaning the status has not been set.
New in version 1.38.5.
StatusCode (integer)
GSM status code
New in version 1.38.5.
outbox_multipart
Data for outgoing multipart messages.
Fields description:
ID (integer unsigned)
the same meaning as values in outbox table
Text (text)
the same meaning as values in outbox table
Coding (enum(‘Default_No_Compression’, ‘Unicode_No_Compression’, ‘8bit’, ‘Default_Compression’,
‘Unicode_Compression’))
the same meaning as values in outbox table
UDH (text)
the same meaning as values in outbox table
Class (integer)
the same meaning as values in outbox table
TextDecoded (varchar(160))
the same meaning as values in outbox table
ID (integer unsigned)
the same meaning as values in outbox table
SequencePosition (integer)
info, what is SMS number in SMS sequence (start at 2, first part is in outbox table).
Status (enum(‘SendingOK’, ‘SendingOKNoReport’, ‘SendingError’, ‘DeliveryOK’, ‘DeliveryFailed’,
‘DeliveryPending’, ‘DeliveryUnknown’, ‘Error’, ‘Reserved’))
Status of message sending. SendingError means that phone failed to send the message, Error
indicates some other error while processing message.
SendingOK
Message has been sent, waiting for delivery report.
SendingOKNoReport
Message has been sent without asking for delivery report.
SendingError
Sending has failed.
DeliveryOK
Delivery report arrived and reported success.
DeliveryFailed
Delivery report arrived and reports failure.
DeliveryPending
Delivery report announced pending deliver.
DeliveryUnknown
Delivery report reported unknown status.
Error Some other error happened during sending (usually bug in SMSD).
Reserved
Initial value, meaning the status has not been set.
New in version 1.38.5.
StatusCode (integer)
GSM status code
New in version 1.38.5.
phones
Information about connected phones. This table is periodically refreshed and you can get information such
as battery or signal level from here.
Fields description:
ID (text)
PhoneID value
UpdatedInDB (timestamp)
when this record has been updated
InsertIntoDB (timestamp)
when this record has been created (when phone has been connected)
TimeOut (timestamp)
when this record expires
Send (boolean)
indicates whether SMSD is sending messages, depends on configuration directive Send
Receive (boolean)
indicates whether SMSD is receiving messages, depends on configuration directive Receive
IMEI (text)
IMEI of phone
IMSI (text)
SIM IMSI
Client (text)
client name, usually string Gammu with version
Battery (integer)
battery level in percent (or -1 if unknown)
Signal (integer)
signal level in percent (or -1 if unknown)
Sent (integer)
Number of sent SMS messages (SMSD does not reset this counter, so it might overflow).
Received (integer)
Number of received SMS messages (SMSD does not reset this counter, so it might overflow).
sentitems
Log of sent messages (and unsent ones with error code). Also if delivery reports are enabled, message
state is updated after receiving delivery report.
Fields description:
UpdatedInDB (timestamp)
when somebody (daemon, user, etc.) updated it
InsertIntoDB (timestamp)
when message was inserted into database
SendingDateTime (timestamp)
when message has been sent
DeliveryDateTime (timestamp)
Time of receiving delivery report (if it has been enabled).
Status (enum(‘SendingOK’, ‘SendingOKNoReport’, ‘SendingError’, ‘DeliveryOK’, ‘DeliveryFailed’,
‘DeliveryPending’, ‘DeliveryUnknown’, ‘Error’))
Status of message sending. SendingError means that phone failed to send the message, Error
indicates some other error while processing message.
SendingOK
Message has been sent, waiting for delivery report.
SendingOKNoReport
Message has been sent without asking for delivery report.
SendingError
Sending has failed.
DeliveryOK
Delivery report arrived and reported success.
DeliveryFailed
Delivery report arrived and reports failure.
DeliveryPending
Delivery report announced pending deliver.
DeliveryUnknown
Delivery report reported unknown status.
Error Some other error happened during sending (usually bug in SMSD).
StatusError (integer)
Status of delivery from delivery report message, codes are defined in GSM specification 03.40
section 9.2.3.15 (TP-Status).
Text (text)
SMS text encoded using hex values
DestinationNumber (varchar(20))
decoded destination number for SMS
Coding (enum(‘Default_No_Compression’, ‘Unicode_No_Compression’, ‘8bit’, ‘Default_Compression’,
‘Unicode_Compression’))
SMS text coding
UDH (text)
User Data Header encoded using hex values
SMSCNumber (varchar(20))
decoded number of SMSC, which sent SMS
Class (integer)
SMS class or -1 (0 is normal SMS, 1 is flash one, 127 is USSD)
TextDecoded (varchar(160))
SMS text in “human readable” form
ID (integer unsigned)
SMS ID
SenderID (text)
which SMSD instance sent this one sequence, see PhoneID
SequencePosition (integer)
SMS number in SMS sequence
TPMR (integer)
Message Reference like in GSM specs
RelativeValidity (integer)
SMS relative validity like encoded using GSM specs
CreatorID (text)
copied from CreatorID from outbox table
StatusCode (integer)
GSM status code
New in version 1.38.5.
HISTORY OF DATABASE STRUCTURE
NOTE:
Testing versions (see versioning) do not have to keep same table structure as final releases. Below
mentioned versions are for informational purposes only, you should always use stable versions in
production environment.
History of schema versions:
17
• Added Status field to outbox and outbox_multipart.
• Added StatusCode field to sentitems, outbox and outbox_multipart.
• Added Status field to inbox.
Changed in version 1.38.5.
16
• Removed unused daemons, pbk and pbk_groups tables.
• Added primary key to the gammu table.
• Added Priority field to the outbox.
• Added IMSI field to the phones.
Changed in version 1.37.90.
15
Added Retries field to the outbox.
Changed in version 1.36.7.
14
Added NetCode and NetName fields.
Changed in version 1.34.0.
13 Added SendBefore and SendAfter fields.
Changed in version 1.29.90.
Also PostgreSQL fields are now case sensitive (same as other backends).
Changed in version 1.29.93.
12 the changes only affect MySQL structure changing default values for timestamps from 0000-00-00
00:00:00 to CURRENT_TIMESTAMP() by using triggers, to update to this version, just execute
triggers definition at the end of SQL file.
Changed in version 1.28.94.
11 all fields for storing message text are no longer limited to 160 chars, but are arbitrary length
text fields.
Changed in version 1.25.92.
10 DeliveryDateTime is now NULL when message is not delivered, added several indexes
Changed in version 1.22.95.
9 added sent/received counters to phones table
Changed in version 1.22.93.
8 Signal and battery state are now stored in database.
Changed in version 1.20.94.
7 Added CreatorID to several tables.
Changed in version 1.07.00.
6 Many fields in outbox can now be NULL.
Changed in version 1.06.00.
5 Introduced daemons table and various other changes.
Changed in version 1.03.00.
3 Introduced phones table and various other changes.
Changed in version 0.98.0.
EXAMPLES
Creating tables
SQL scripts to create all needed tables for most databases are included in Gammu documentation
(docs/sql). As well as some PHP scripts interacting with the database.
For example to create SQLite tables, issue following command:
sqlite3 smsd.db < docs/sql/sqlite.sql
Injecting a message using SQL
To send a message, you can either use gammu-smsd-inject, which does all the magic for you, or you can
insert the message manually. The simplest example is short text message:
INSERT INTO outbox (
DestinationNumber,
TextDecoded,
CreatorID,
Coding
) VALUES (
'800123465',
'This is a SQL test message',
'Program',
'Default_No_Compression'
);
Please note usage of TextDecoded field, for Text field, you would have to hex encode the unicode text:
INSERT INTO outbox (
DestinationNumber,
Text,
CreatorID,
Coding
) VALUES (
'800123465',
'005400680069007300200069007300200061002000530051004c002000740065007300740020006d006500730073006100670065',
'Program',
'Default_No_Compression'
);
Injecting long message using SQL
Inserting multipart messages is a bit more tricky, you need to construct also UDH header and store it
hexadecimally written into UDH field. Unless you have a good reason to do this manually, use
gammu-smsd-inject, C library (SMSD_InjectSMS()) or Python library (gammu.smsd.SMSD.InjectSMS()).
For long text message, the UDH starts with 050003 followed by byte as a message reference (you can put
any hex value there, but it should be different for each message, D3 in following example), byte for
number of messages (02 in example, it should be unique for each message you send to same phone number)
and byte for number of current message (01 for first message, 02 for second, etc.).
I most cases, the mutlipart message has to be class 1.
For example long text message of two parts could look like following:
INSERT INTO outbox (
CreatorID,
MultiPart,
DestinationNumber,
UDH,
TextDecoded,
Coding,
Class
) VALUES (
'Gammu 1.23.91',
'true',
'123465',
'050003D30201',
'Mqukqirip ya konej eqniu rejropocejor hugiygydewl tfej nrupxujob xuemymiyliralj. Te tvyjuh qaxumur ibewfoiws zuucoz tdygu gelum L ejqigqesykl kya jdytbez',
'Default_No_Compression',
1
)
INSERT INTO outbox_multipart (
SequencePosition,
UDH,
Class,
TextDecoded,
ID,
Coding,
Class
) VALUES (
2,
'050003D30202',
'u xewz qisubevumxyzk ufuylehyzc. Nse xobq dfolizygqysj t bvowsyhyhyemim ovutpapeaempye giuuwbib.',
<ID_OF_INSERTED_RECORD_IN_OUBOX_TABLE>,
'Default_No_Compression',
1
)
NOTE:
Adding UDH means that you have less space for text, in above example you can use only 153 characters
in single message.
AUTHOR
Michal Čihař <michal@cihar.com>
COPYRIGHT
2009-2015, Michal Čihař <michal@cihar.com>