home | help 
GAMMU-SMSD-SQL(7)		      Gammu		       GAMMU-SMSD-SQL(7)

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

     * 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 noti-
	  fications), 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, SendingOKNoRe-
		   port)

	    %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. The interval can be
	    configured by RetryTimeout.

	       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 AihaA <michal@cihar.com>

COPYRIGHT
     2009-2015, Michal AihaA <michal@cihar.com>

1.42.0				  Oct 03, 2020		       GAMMU-SMSD-SQL(7)
home | help