Provided by: gammu-smsd_1.37.0-1build1_amd64 bug

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

   daemons
       Information about running daemons.

   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)

       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

       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)

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

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

   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

       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)

       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

   pbk
       Not used by SMSD currently, included only for application usage.

   pbk_groups
       Not used by SMSD currently, included only for application usage.

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:

       15
          Added Retries field.

          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>