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.
Transmitted messages are read from table outbox and possible subsequent parts of the same message from outbox_multipart.
Information about running daemons.
Table holding single field Version - version of a database schema. See History of database structure for details what has changed.
Table where received messages will be stored.
Fields description:
Messages enqueued for sending should be placed in this table. If message is multipart, subsequent parts are stored in table outbox_multipart.
Fields description:
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.
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.
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.
Data for outgoing multipart messages.
Fields description:
Information about connected phones. This table is periodically refreshed and you can get information such as battery or signal level from here.
Fields description:
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:
Status of message sending. SendingError mens that phone failed to send the message, Error indicates some other error while processing message.
Not used by SMSD currently, included only for application usage.
Not used by SMSD currently, included only for application usage.
Note
Testing versions (see Versioning) do not have to keep same table structure as final releases. Bellow mentioned versions are for informational purposes only, you should always use stable versions in production environment.
History of schema versions:
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.
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.
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.
DeliveryDateTime is now NULL when message is not delivered, added several indexes
Changed in version 1.22.95.
added sent/received counters to phones table
Changed in version 1.22.93.
Signal and battery state are now stored in database.
Changed in version 1.20.94.
Added CreatorID to several tables.
Changed in version 1.07.00.
Many fields in outbox can now be NULL.
Changed in version 1.06.00.
Introduced daemons table and various other changes.
Changed in version 1.03.00.
Introduced phones table and various other changes.
Changed in version 0.98.0.
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
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'
);
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.
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, 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.).
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'
)
Note
Adding UDH means that you have less space for text, in above example you can use only 153 characters in single message.