GSP
Quick Navigator

Search Site

Unix VPS
A - Starter
B - Basic
C - Preferred
D - Commercial
MPS - Dedicated
Previous VPSs
* Sign Up! *

Support
Contact Us
Online Help
Handbooks
Domain Status
Man Pages

FAQ
Virtual Servers
Pricing
Billing
Technical

Network
Facilities
Connectivity
Topology Map

Miscellaneous
Server Agreement
Year 2038
Credits
 

USA Flag

 

 

Man Pages
GAMMU-SMSD-SQL(7) Gammu GAMMU-SMSD-SQL(7)

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ř <michal@cihar.com>

2009-2015, Michal Čihař <michal@cihar.com>
October 3, 2020 1.42.0

Search for    or go to Top of page |  Section 7 |  Main Index

Powered by GSP Visit the GSP FreeBSD Man Page Interface.
Output converted with ManDoc.