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_mysql
for MySQL Backendnative_pgsql
for PostgreSQL Backendodbc
for 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
,odbc
or 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
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. 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