gammu-smsd-tables - Man Page

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:

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.

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.

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.

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).

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.

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, 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.