SIMPLE SOLUTIONS

GAMMU-SMSD-SQL(7) - Linux man page online | Overview, conventions, and miscellany

Gammu-smsd(1) backend using SQL abstraction layer to use any supported database as a message.

Chapter
Jan 05, 2018
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 (config‐ uration 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 Spe‐ cific 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), des‐ tination 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 DestinationNumbe ↲ r = %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 LI ↲ MIT %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. 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ř <@cihar.com>
2009-2015, Michal Čihař <@cihar.com>
1.39.0 Jan 05, 2018 GAMMU-SMSD-SQL(7)
This manual Reference Other manuals
gammu-smsd-sql(7) referred by
refer to gammu-smsd(1)
Download raw manual
Index Gammu (+20) 1.39.0 (+20) № 7 (+1560)
Go top