Provided by: gammu-smsd_1.24.0-1ubuntu1_i386 bug

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

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