Scroll to navigation

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


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.

gammu
Name of the gammu-table table.

inbox
Name of the inbox table.

sentitems
Name of the sentitems table.

outbox
Name of the outbox table.

outbox_multipart
Name of the outbox_multipart table.

phones
Name of the phones 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


AUTHOR

Michal Čihař <michal@cihar.com>

COPYRIGHT

2009-2015, Michal Čihař <michal@cihar.com>
September 27, 2019 1.41.0