SQL Service¶
Description¶
SQL service stores all its data in database. It can use one of these SQL backends
(configuration option Driver in smsd section):
native_mysqlfor MySQL Backendnative_pgsqlfor PostgreSQL Backendodbcfor ODBC Backend- drivers supported by DBI for DBI Backend, which include:
sqlite3- for SQLite 3mysql- for MySQLpgsql- for PostgeSQLfreetds- for MS SQL Server or Sybase
SQL connection parameters¶
Common for all backends:
User- user connecting to databasePassword- password for connecting to databaseHost- database host or data source nameDatabase- database nameDriver-native_mysql,native_pgsql,odbcor DBI oneSQL- SQL dialect to use
Specific for DBI:
DriversPath- path to DBI driversDBDir- sqlite/sqlite3 directory with database
See also
The variables are fully described in Gammu Configuration File 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.
-
outbox_multipart¶ Name of the outbox_multipart 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
LoopSleepvalue.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