table of contents
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 storageDESCRIPTION¶
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] 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.
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
AUTHOR¶
Michal Čihař <michal@cihar.com>COPYRIGHT¶
2009-2012, Michal Čihař <michal@cihar.com>May 29, 2013 | 1.33.0 |