Provided by: gammu-smsd_1.33.0-3_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-mysqlnative_pgsql for gammu-smsd-pgsqlodbc for gammu-smsd-odbcdrivers 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.

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

       %P     PHONE ID (hostname)

       %N     client name (eg. Gammu 1.12.3)

   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

AUTHOR

       Michal Čihař <michal@cihar.com>

COPYRIGHT

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