gammu-smsd-sql (7)

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

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.

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

%P

PHONE ID (hostname)

%N

client name (eg. Gammu 1.12.3)

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.

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

Michal Čihař <[email protected]>

2009-2012, Michal Čihař <[email protected]>