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 Backend
native_pgsql for PostgreSQL Backend
odbc for ODBC Backend
Common for all backends:
Specific for DBI:
See also
The variables are fully described in Gammu Configuration File documentation.
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.
Name of the outbox_multipart table.
You can change any table name using these:
[tables]
inbox = special_inbox
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:
[1] | Sender number for received messages (insert to inbox or delivery notifications), destination otherwise. |
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.
Deletes phone from database.
Default value:
DELETE FROM phones WHERE IMEI = %I
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:
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
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:
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:
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 statistics after receiving message.
Default value:
UPDATE phones SET Received = Received + 1 WHERE IMEI = %I
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:
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:
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:
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:
Remove messages from outbox after threir successful send.
Default value:
DELETE FROM outbox WHERE ID=%1
Query specific parameters:
Remove messages from outbox_multipart after threir successful send.
Default value:
DELETE FROM outbox_multipart WHERE ID=%1
Query specific parameters:
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:
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:
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:
Update sent statistics after sending message.
Default value:
UPDATE phones SET Sent= Sent + 1 WHERE IMEI = %I
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:
Update number of retries for outbox message.
UPDATE outbox SET SendngTimeOut = (NOW() + INTERVAL 600 SECOND) + 0,
Retries = %2 WHERE ID = %1
Query specific parameters: