GAMMU-SMSD-SQL(7) Gammu GAMMU-SMSD-SQL(7)NAMEgammu-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.
SQL QUERIES
Almost all queries are configurable. You can edit them in [sql] sec‐
tion. 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 mes‐
sages, 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)
%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, SendingO‐
KNoReport)
%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
AUTHOR
Michal Čihař <michal@cihar.com>
COPYRIGHT
2009-2015, Michal Čihař <michal@cihar.com>
1.36.8 December 08, 2015 GAMMU-SMSD-SQL(7)
