NAME
gammu-smsd-tables - description of tables for database backends of
gammu-smsd(1)
DESCRIPTION
gammu-smsd(1) supports several backends. Actual backend is chosen in a
config file gammu-smsdrc(5).
Separate backends are described in their appropriate man pages. This
manual page 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(1) does this), each SMS daemon will have separate
outbox queue.
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
daemons
Information about running daemons.
gammu Table holding single value - version of a database schema. See
HISTORY 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)
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
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
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)
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
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)
sender identification, it has to match PhoneID in SMSD
configuration to make SMSD process this message
outbox_multipart
Data for outgoing multipart messages.
Fields description:
ID (integer unsigned)
Text (text)
Coding (enum(’Default_No_Compression’, ’Unicode_No_Compression’,
’8bit’, ’Default_Compression’, ’Unicode_Compression’))
UDH (text)
Class (integer)
TextDecoded (varchar(160))
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).
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)
currently always true
Receive (boolean)
currently always true
IMEI (text)
IMEI of phone
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 mens 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)
TextDecoded (varchar(160))
SMS text in "human readable" form
ID (integer unsigned)
SMS ID
SenderID (text)
which SMSD instance sent this one sequence
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, matches PhoneID
pbk Not used by SMSD currently, included only for application
usage.
pbk_groups
Not used by SMSD currently, included only for application usage.
HISTORY
History of schema versions:
11 - all fields for storing message text are no longer limited to 160
chars, but are arbitrary length text fields (1.25.92)
10 - DeliveryDateTime is now NULL when message is not delivered, added
several indexes
9 - added sent/received counters to phones table
8 - introduced phones table
7 - added CreatorID to tables (it holds PhoneID if set)
EXAMPLE
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(1), 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’
);
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.
For long text message, the UDH starts with 050003 followed by byte as a
message reference (you can put anything there, but it should be
different for each message, D3 in following example), byte for number
of messages (02 in example) and byte for number of current message (01
for first message, 02 for second, etc.).
For example long text message of two parts could look like following:
INSERT INTO outbox (
CreatorID,
MultiPart,
DestinationNumber,
UDH,
TextDecoded,
Coding
) 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’
)
INSERT INTO outbox_multipart (
SequencePosition,
UDH,
Class,
TextDecoded,
ID,
Coding
) VALUES (
2,
’050003D30202’,
’u xewz qisubevumxyzk ufuylehyzc. Nse xobq dfolizygqysj t bvowsyhyhyemim ovutpapeaempye giuuwbib.’,
<ID_OF_INSERTED_RECORD_IN_OUBOX_TABLE>,
’Default_No_Compression’
)
SEE ALSO
Database backends: gammu-smsd-mysql(7), gammu-smsd-pgsql(7),
gammu-smsd-dbi(7)
gammu-smsd(1), gammu-smsdrc(5), gammu(1), gammurc(5)
AUTHOR
gammu-smsd and this manual page were written by Michal Cihar
<michal@cihar.com>.
COPYRIGHT
Copyright © 2009 Michal Cihar and other authors. License GPLv2: GNU
GPL version 2 <http://www.gnu.org/licenses/old-licenses/gpl-2.0.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
REPORTING BUGS
Please report bugs to <http://bugs.cihar.com>.