Provided by: gammu-smsd_1.39.0-1_amd64 bug

NAME

       gammu-smsd-sql  -  gammu-smsd(1)  backend using SQL abstraction layer to use any supported
       database as a message storage

DESCRIPTION

       SQL service stores all its data in  database.  It  can  use  one  of  these  SQL  backends
       (configuration option Driver in smsd section):

       • native_mysql for gammu-smsd-mysql

       • native_pgsql for gammu-smsd-pgsql

       • odbc for gammu-smsd-odbc

       •

         drivers supported by DBI for gammu-smsd-dbi, which include:sqlite3 - for SQLite 3

                • mysql - for MySQL

                • pgsql - for PostgeSQL

                • freetds - for MS SQL Server or Sybase

SQL CONNECTION PARAMETERS

       Common for all backends:

       • User - user connecting to database

       • Password - password for connecting to database

       • Host - database host or data source name

       • Database - database name

       • Driver - native_mysql, native_pgsql, odbc or DBI one

       • SQL - SQL dialect to use

       Specific for DBI:

       • DriversPath - path to DBI drivers

       • DBDir - sqlite/sqlite3 directory with database

       SEE ALSO:
          The variables are fully described in gammurc documentation.

TABLES

       New in version 1.37.1.

       You  can  customize name of all tables in the [tables]. The SQL queries will reflect this,
       so it’s enough to change table name in this section.

       gammu  Name of the gammu-table table.

       inbox  Name of the inbox table.

       sentitems
              Name of the sentitems table.

       outbox Name of the outbox table.

       outbox_multipart
              Name of the outbox_multipart table.

       phones Name of the phones table.

       You can change any table name using these:

          [tables]
          inbox = special_inbox

SQL QUERIES

       Almost all queries are configurable. You can edit them in [sql] section. There are several
       variables used in SQL queries. We can separate them into three groups:

       • phone specific, which can be used in every query, see Phone Specific Parameters

       • SMS  specific,  which  can  be  used  in  queries which works with SMS messages, see SMS
         Specific Parameters

       • query specific, which are numeric and are specific only  for  given  query  (or  set  of
         queries), see Configurable queries

   Phone Specific Parameters
       %I     IMEI of phone

       %S     SIM IMSI

       %P     PHONE ID (hostname)

       %N     client name (eg. Gammu 1.12.3)

       %O     network code

       %M     network name

   SMS Specific Parameters
       %R     remote number [1]

       %C     delivery datetime

       %e     delivery status on receiving or status error on sending

       %t     message reference

       %d     receiving datetime for received sms

       %E     encoded text of SMS

       %c     SMS coding (ie 8bit or UnicodeNoCompression)

       %F     sms centre number

       %u     UDH header

       %x     class

       %T     decoded SMS text

       %A     CreatorID of SMS (sending sms)

       %V     relative validity

       [1]  Sender  number  for  received  messages  (insert to inbox or delivery notifications),
            destination otherwise.

CONFIGURABLE QUERIES

       All configurable queries can be set in [sql] section. Sequence  of  rows  in  selects  are
       mandatory.

       All  default  queries  noted  here  are noted for MySQL. Actual time and time addition are
       selected for default queries during initialization.

       delete_phone
              Deletes phone from database.

              Default value:

                 DELETE FROM phones WHERE IMEI = %I

       insert_phone
              Inserts phone to database.

              Default value:

                 INSERT INTO phones (IMEI, ID, Send, Receive, InsertIntoDB, TimeOut, Client, Battery, Signal)
                 VALUES (%I, %P, %1, %2, NOW(), (NOW() + INTERVAL 10 SECOND) + 0, %N, -1, -1)

              Query specific parameters:

              %1     enable send (yes or no) - configuration option Send

              %2     enable receive (yes or no)  - configuration option Receive

       save_inbox_sms_select
              Select message for update delivery status.

              Default value:

                 SELECT ID, Status, SendingDateTime, DeliveryDateTime, SMSCNumber FROM sentitems
                 WHERE DeliveryDateTime IS NULL AND SenderID = %P AND TPMR = %t AND DestinationNumber = %R

       save_inbox_sms_update_delivered
              Update message delivery status if message was delivered.

              Default value:

                 UPDATE sentitems SET DeliveryDateTime = %C, Status = %1, StatusError = %e WHERE ID = %2 AND TPMR = %t

              Query specific parameters:

              %1     delivery status returned by GSM network

              %2     ID of message

       save_inbox_sms_update
              Update message if there is an delivery error.

              Default value:

                 UPDATE sentitems SET Status = %1, StatusError = %e WHERE ID = %2 AND TPMR = %t

              Query specific parameters:

              %1     delivery status returned by GSM network

              %2     ID of message

       save_inbox_sms_insert
              Insert received message.

              Default value:

                 INSERT INTO inbox (ReceivingDateTime, Text, SenderNumber, Coding, SMSCNumber, UDH,
                 Class, TextDecoded, RecipientID) VALUES (%d, %E, %R, %c, %F, %u, %x, %T, %P)

       update_received
              Update statistics after receiving message.

              Default value:

                 UPDATE phones SET Received = Received + 1 WHERE IMEI = %I

       refresh_send_status
              Update messages in outbox.

              Default value:

                 UPDATE outbox SET SendingTimeOut = (NOW() + INTERVAL 60 SECOND) + 0
                 WHERE ID = %1 AND (SendingTimeOut < NOW() OR SendingTimeOut IS NULL)

              The default query calculates sending timeout based on LoopSleep value.

              Query specific parameters:

              %1     ID of message

       find_outbox_sms_id
              Find sms messages for sending.

              Default value:

                 SELECT ID, InsertIntoDB, SendingDateTime, SenderID FROM outbox
                 WHERE SendingDateTime < NOW() AND SendingTimeOut <  NOW() AND
                 SendBefore >= CURTIME() AND SendAfter <= CURTIME() AND
                 ( SenderID is NULL OR SenderID = '' OR SenderID = %P ) ORDER BY InsertIntoDB ASC LIMIT %1

              Query specific parameters:

              %1     limit of sms messages sended in one walk in loop

       find_outbox_body
              Select body of message.

              Default value:

                 SELECT Text, Coding, UDH, Class, TextDecoded, ID, DestinationNumber, MultiPart,
                 RelativeValidity, DeliveryReport, CreatorID FROM outbox WHERE ID=%1

              Query specific parameters:

              %1     ID of message

       find_outbox_multipart
              Select remaining parts of sms message.

              Default value:

                 SELECT Text, Coding, UDH, Class, TextDecoded, ID, SequencePosition
                 FROM outbox_multipart WHERE ID=%1 AND SequencePosition=%2

              Query specific parameters:

              %1     ID of message

              %2     Number of multipart message

       delete_outbox
              Remove messages from outbox after threir successful send.

              Default value:

                 DELETE FROM outbox WHERE ID=%1

              Query specific parameters:

              %1     ID of message

       delete_outbox_multipart
              Remove messages from outbox_multipart after threir successful send.

              Default value:

                 DELETE FROM outbox_multipart WHERE ID=%1

              Query specific parameters:

              %1     ID of message

       create_outbox
              Create message (insert to outbox).

              Default value:

                 INSERT INTO outbox (CreatorID, SenderID, DeliveryReport, MultiPart,
                 InsertIntoDB, Text, DestinationNumber, RelativeValidity, Coding, UDH, Class,
                 TextDecoded) VALUES (%1, %P, %2, %3, NOW(), %E, %R, %V, %c, %u, %x, %T)

              Query specific parameters:

              %1     creator of message

              %2     delivery status report - yes/default

              %3     multipart - FALSE/TRUE

              %4     Part (part number)

              %5     ID of message

       create_outbox_multipart
              Create message remaining parts.

              Default value:

                 INSERT INTO outbox_multipart (SequencePosition, Text, Coding, UDH, Class,
                 TextDecoded, ID) VALUES (%4, %E, %c, %u, %x, %T, %5)

              Query specific parameters:

              %1     creator of message

              %2     delivery status report - yes/default

              %3     multipart - FALSE/TRUE

              %4     Part (part number)

              %5     ID of message

       add_sent_info
              Insert to sentitems.

              Default value:

                 INSERT INTO sentitems (CreatorID,ID,SequencePosition,Status,SendingDateTime,
                 SMSCNumber, TPMR, SenderID,Text,DestinationNumber,Coding,UDH,Class,TextDecoded,
                 InsertIntoDB,RelativeValidity)
                 VALUES (%A, %1, %2, %3, NOW(), %F, %4, %P, %E, %R, %c, %u, %x, %T, %5, %V)

              Query specific parameters:

              %1     ID of sms message

              %2     part number (for multipart sms)

              %3     message state (SendingError, Error, SendingOK, SendingOKNoReport)

              %4     message reference (TPMR)

              %5     time when inserted in db

       update_sent
              Update sent statistics after sending message.

              Default value:

                 UPDATE phones SET Sent= Sent + 1 WHERE IMEI = %I

       refresh_phone_status
              Update phone status (battery, signal).

              Default value:

                 UPDATE phones SET TimeOut= (NOW() + INTERVAL 10 SECOND) + 0,
                 Battery = %1, Signal = %2 WHERE IMEI = %I

              Query specific parameters:

              %1     battery percent

              %2     signal percent

       update_retries
              Update number of retries for outbox message.

                 UPDATE outbox SET SendngTimeOut = (NOW() + INTERVAL 600 SECOND) + 0,
                 Retries = %2 WHERE ID = %1

              Query specific parameters:

              %1     message ID

              %2     number of retries

AUTHOR

       Michal Čihař <michal@cihar.com>

COPYRIGHT

       2009-2015, Michal Čihař <michal@cihar.com>