gammu-smsd-tables (7) - Linux Manuals

gammu-smsd-tables: description of tables for database backends of gammu-smsd(1)

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 normal SMS, 1 is flash 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.

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)

Injecting a message using SQL

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:

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. For example long text message would look like (please note that each UDH needs to contain number of parts and sequence position as last two bytes, the D3 value in example is message reference number, it can be anything):

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

AUTHOR

gammu-smsd and this manual page were written by Michal Cihar <michal [at] 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>.