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

       o native_mysql for gammu-smsd-mysql

       o native_pgsql for gammu-smsd-pgsql

       o odbc for gammu-smsd-odbc

       o

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

		o sqlite3 - for SQLite 3

		o mysql - for MySQL

		o pgsql - for PostgeSQL

		o freetds - for MS SQL Server or Sybase

SQL CONNECTION PARAMETERS

       Common for all backends:

       o User - user connecting to database

       o Password - password for connecting to database

       o Host - database host or data source name

       o Database - database name

       o Driver - native_mysql, native_pgsql, odbc or DBI one

       o SQL - SQL dialect to use

       Specific for DBI:

       o DriversPath - path to DBI drivers

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

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

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

       o 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, SignalStrength)
	      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 locktime SECOND) + 0
	      WHERE ID = %1 AND (SendingTimeOut < NOW() OR SendingTimeOut IS NULL)

	      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, SignalStrength = %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>

1.31.90 							 February 24, 2012						 GAMMU-SMSD-SQL(7)