gammu-smsd-sql − gammu-smsd(1) backend using SQL abstraction layer to use any supported database as a message storage
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.
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
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 |
|
%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 |
|
%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 |
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 |
Michal ÄihaÅ <[email protected]>
2009-2015, Michal ÄihaÅ <[email protected]>