.\" Man page generated from reStructuredText
.\" by the Docutils 0.22.4 manpage writer.
.
.
.nr rst2man-indent-level 0
.
.de1 rstReportMargin
\\$1 \\n[an-margin]
level \\n[rst2man-indent-level]
level margin: \\n[rst2man-indent\\n[rst2man-indent-level]]
-
\\n[rst2man-indent0]
\\n[rst2man-indent1]
\\n[rst2man-indent2]
..
.de1 INDENT
.\" .rstReportMargin pre:
. RS \\$1
. nr rst2man-indent\\n[rst2man-indent-level] \\n[an-margin]
. nr rst2man-indent-level +1
.\" .rstReportMargin post:
..
.de UNINDENT
. RE
.\" indent \\n[an-margin]
.\" old: \\n[rst2man-indent\\n[rst2man-indent-level]]
.nr rst2man-indent-level -1
.\" new: \\n[rst2man-indent\\n[rst2man-indent-level]]
.in \\n[rst2man-indent\\n[rst2man-indent-level]]u
..
.TH "GAMMU-SMSD-SQL" "7" "Aug 19, 2026" "1.44.1" "Gammu"
.SH NAME
gammu-smsd-sql \- gammu-smsd(1) backend using SQL abstraction layer to use any supported database as a message storage
.SH DESCRIPTION
.sp
SQL service stores all its data in database. It can use one of these SQL backends
(configuration option \fBDriver\fP \%<#\:option-Driver> in smsd section):
.INDENT 0.0
.IP \(bu 2
\fBnative_mysql\fP for MySQL Backend \%<#\:gammu-smsd-mysql>
.IP \(bu 2
\fBnative_pgsql\fP for PostgreSQL Backend \%<#\:gammu-smsd-pgsql>
.IP \(bu 2
\fBodbc\fP for ODBC Backend \%<#\:gammu-smsd-odbc>
.IP \(bu 2
.INDENT 2.0
.TP
.B drivers supported by DBI for DBI Backend \%<#\:gammu-smsd-dbi>, which include:
.INDENT 7.0
.IP \(bu 2
\fBsqlite3\fP \- for SQLite 3
.IP \(bu 2
\fBmysql\fP \- for MySQL
.IP \(bu 2
\fBpgsql\fP \- for PostgeSQL
.IP \(bu 2
\fBfreetds\fP \- for MS SQL Server or Sybase
.UNINDENT
.UNINDENT
.UNINDENT
.SH SQL CONNECTION PARAMETERS
.sp
Common for all backends:
.INDENT 0.0
.IP \(bu 2
\fBUser\fP \%<#\:option-User> \- user connecting to database
.IP \(bu 2
\fBPassword\fP \%<#\:option-Password> \- password for connecting to database
.IP \(bu 2
\fBHost\fP \%<#\:option-Host> \- database host or data source name
.IP \(bu 2
\fBDatabase\fP \%<#\:option-Database> \- database name
.IP \(bu 2
\fBDriver\fP \%<#\:option-Driver> \- \fBnative_mysql\fP, \fBnative_pgsql\fP, \fBodbc\fP or DBI one
.IP \(bu 2
\fBSQL\fP \%<#\:option-SQL> \- SQL dialect to use
.UNINDENT
.sp
Specific for DBI:
.INDENT 0.0
.IP \(bu 2
\fBDriversPath\fP \%<#\:option-DriversPath> \- path to DBI drivers
.IP \(bu 2
\fBDBDir\fP \%<#\:option-DBDir> \- sqlite/sqlite3 directory with database
.UNINDENT
.sp
\fBSee also:\fP
.INDENT 0.0
.INDENT 3.5
The variables are fully described in Gammu Configuration File \%<#\:gammurc> documentation.
.UNINDENT
.UNINDENT
.SH TABLES
.sp
Added in version 1.37.1.

.sp
You can customize name of all tables in the \fB[tables]\fP \%<#\:section-[tables]>\&. The SQL
queries will reflect this, so it\(aqs enough to change table name in this section.
.INDENT 0.0
.TP
.B gammu
Name of the gammu \%<#\:gammu-table> table.
.UNINDENT
.INDENT 0.0
.TP
.B inbox
Name of the inbox \%<#\:inbox> table.
.UNINDENT
.INDENT 0.0
.TP
.B sentitems
Name of the sentitems \%<#\:sentitems> table.
.UNINDENT
.INDENT 0.0
.TP
.B outbox
Name of the outbox \%<#\:outbox> table.
.UNINDENT
.INDENT 0.0
.TP
.B outbox_multipart
Name of the outbox_multipart \%<#\:outbox-multipart> table.
.UNINDENT
.INDENT 0.0
.TP
.B phones
Name of the phones \%<#\:phones> table.
.UNINDENT
.sp
You can change any table name using these:
.INDENT 0.0
.INDENT 3.5
.sp
.EX
[tables]
inbox = special_inbox
.EE
.UNINDENT
.UNINDENT
.SH SQL QUERIES
.sp
Almost all queries are configurable. You can edit them in
\fB[sql]\fP \%<#\:section-[sql]> section. There are several variables used in SQL
queries. We can separate them into three groups:
.INDENT 0.0
.IP \(bu 2
phone specific, which can be used in every query, see Phone Specific Parameters
.IP \(bu 2
SMS specific, which can be used in queries which works with SMS messages, see SMS Specific Parameters
.IP \(bu 2
query specific, which are numeric and are specific only for given query (or set of queries), see Configurable queries
.UNINDENT
.SS Phone Specific Parameters
.INDENT 0.0
.TP
.B \fB%I\fP
IMEI of phone
.TP
.B \fB%S\fP
SIM IMSI
.TP
.B \fB%P\fP
PHONE ID (hostname)
.TP
.B \fB%N\fP
client name (eg. Gammu 1.12.3)
.TP
.B \fB%O\fP
network code
.TP
.B \fB%M\fP
network name
.UNINDENT
.SS SMS Specific Parameters
.INDENT 0.0
.TP
.B \fB%R\fP
remote number [1]
.TP
.B \fB%C\fP
delivery datetime
.TP
.B \fB%e\fP
delivery status on receiving or status error on sending
.TP
.B \fB%t\fP
message reference
.TP
.B \fB%d\fP
receiving datetime for received sms
.TP
.B \fB%E\fP
encoded text of SMS
.TP
.B \fB%c\fP
SMS coding (ie 8bit or UnicodeNoCompression)
.TP
.B \fB%F\fP
sms centre number
.TP
.B \fB%u\fP
UDH header
.TP
.B \fB%x\fP
class
.TP
.B \fB%T\fP
decoded SMS text
.TP
.B \fB%A\fP
CreatorID of SMS (sending sms)
.TP
.B \fB%V\fP
relative validity
.UNINDENT
.IP [1] 5
Sender number for received messages (insert to inbox or delivery notifications), destination otherwise.
.SH CONFIGURABLE QUERIES
.sp
All configurable queries can be set in \fB[sql]\fP \%<#\:section-[sql]> section. Sequence of rows in selects are mandatory.
.sp
All default queries noted here are noted for MySQL. Actual time and time addition
are selected for default queries during initialization.
.INDENT 0.0
.TP
.B delete_phone
Deletes phone from database.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
DELETE FROM phones WHERE IMEI = %I
.EE
.UNINDENT
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B insert_phone
Inserts phone to database.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
INSERT INTO phones (IMEI, IMSI, ID, NetCode, NetName, Send, Receive,
                    InsertIntoDB, TimeOut, Client, Battery, Signal)
VALUES (%I, %S, %P, %O, %M, %1, %2, NOW(),
        (NOW() + INTERVAL 70 SECOND) + 0, %N, \-1, \-1)
.EE
.UNINDENT
.UNINDENT
.sp
The shown 70\-second expiry uses the default 60\-second
\fBStatusFrequency\fP \%<#\:option-StatusFrequency> plus a ten\-second grace period. The
generated default query follows the configured frequency. Custom queries
should provide an equivalent expiry interval.
.sp
Query specific parameters:
.INDENT 7.0
.TP
.B \fB%1\fP
enable send (yes or no) \- configuration option Send
.TP
.B \fB%2\fP
enable receive (yes or no)  \- configuration option Receive
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B save_inbox_sms_select
Select message for update delivery status.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
SELECT ID, Status, SendingDateTime, DeliveryDateTime, SMSCNumber FROM sentitems
WHERE DeliveryDateTime IS NULL AND SenderID = %P AND TPMR = %t AND DestinationNumber = %R
.EE
.UNINDENT
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B save_inbox_sms_update_delivered
Update message delivery status if message was delivered.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
UPDATE sentitems SET DeliveryDateTime = %C, Status = %1, StatusError = %e WHERE ID = %2 AND TPMR = %t
.EE
.UNINDENT
.UNINDENT
.sp
Query specific parameters:
.INDENT 7.0
.TP
.B \fB%1\fP
delivery status returned by GSM network
.TP
.B \fB%2\fP
ID of message
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B save_inbox_sms_update
Update message if there is an delivery error.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
UPDATE sentitems SET Status = %1, StatusError = %e WHERE ID = %2 AND TPMR = %t
.EE
.UNINDENT
.UNINDENT
.sp
Query specific parameters:
.INDENT 7.0
.TP
.B \fB%1\fP
delivery status returned by GSM network
.TP
.B \fB%2\fP
ID of message
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B save_inbox_sms_insert
Insert received message.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
INSERT INTO inbox (ReceivingDateTime, InsertIntoDB, Text, SenderNumber, Coding,
SMSCNumber, UDH, Class, TextDecoded, RecipientID, Status, MessageID,
SequencePosition, PartCount, Processed)
VALUES (%d, NOW(), %E, %R, %c, %F, %u, %x, %T, %P, %e, %1, %2, %3, %4)
.EE
.UNINDENT
.UNINDENT
.sp
Query specific parameters:
.INDENT 7.0
.TP
.B \fB%1\fP
logical message identifier, or 0 until the first physical row ID is known
.TP
.B \fB%2\fP
sequence position of this physical part
.TP
.B \fB%3\fP
expected number of physical parts
.TP
.B \fB%4\fP
initial processed state; received rows are staged as processed until their
logical message identifier is finalized
.UNINDENT
.sp
Custom queries must store the database server\(aqs current timestamp in
\fBInsertIntoDB\fP so incomplete multipart groups can be restored without
extending their original timeout.
.UNINDENT
.INDENT 0.0
.TP
.B save_inbox_sms_update_metadata
Finalize the logical message identifier and multipart metadata, then publish
the received physical message to inbox consumers.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
UPDATE inbox SET MessageID = %1, SequencePosition = %2, PartCount = %3,
Processed = %5 WHERE ID = %4
.EE
.UNINDENT
.UNINDENT
.sp
Query specific parameters:
.INDENT 7.0
.TP
.B \fB%1\fP
ID of the first stored physical part of the logical message
.TP
.B \fB%2\fP
sequence position of this physical part
.TP
.B \fB%3\fP
expected number of physical parts
.TP
.B \fB%4\fP
ID of this physical part
.TP
.B \fB%5\fP
final processed state; false publishes the completed row
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B restore_inbox_groups
Restore incomplete multipart groups after SMSD restarts. The default query
selects recently inserted multipart rows for the current phone, including
earlier rows sharing their logical message identifier. SMSD discards
complete and expired groups after reading the result.
.sp
The selected columns must remain in the documented order. The final column
is the row age in seconds, calculated by the database so that restoration
does not depend on the SMSD host and database server sharing a timezone.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
SELECT MessageID, SenderNumber, SMSCNumber, UDH, SequencePosition,
PartCount, TIMESTAMPDIFF(SECOND, InsertIntoDB, NOW()) FROM inbox WHERE
PartCount > 1 AND RecipientID = %P AND MessageID IN (SELECT MessageID
FROM inbox WHERE PartCount > 1 AND RecipientID = %P AND
InsertIntoDB >= NOW() \- INTERVAL 600 SECOND)
ORDER BY InsertIntoDB ASC, ID ASC
.EE
.UNINDENT
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B update_received
Update statistics after receiving message.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
UPDATE phones SET Received = Received + 1 WHERE IMEI = %I
.EE
.UNINDENT
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B refresh_send_status
Update messages in outbox.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
UPDATE outbox SET SendingTimeOut = (NOW() + INTERVAL 60 SECOND) + 0
WHERE ID = %1 AND (SendingTimeOut < NOW() OR SendingTimeOut IS NULL)
.EE
.UNINDENT
.UNINDENT
.sp
The default query calculates sending timeout based on \fBLoopSleep\fP \%<#\:option-LoopSleep>
value.
.sp
Query specific parameters:
.INDENT 7.0
.TP
.B \fB%1\fP
ID of message
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B find_outbox_sms_id
Find sms messages for sending.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
SELECT ID, InsertIntoDB, SendingDateTime, SenderID FROM outbox
WHERE SendingDateTime < NOW() AND SendingTimeOut <  NOW() AND
SendBefore >= CURTIME() AND SendAfter <= CURTIME() AND
(SendDays & %2) <> 0 AND
( SenderID is NULL OR SenderID = \(aq\(aq OR SenderID = %P )
ORDER BY Priority DESC, InsertIntoDB ASC LIMIT %1
.EE
.UNINDENT
.UNINDENT
.sp
Query specific parameters:
.INDENT 7.0
.TP
.B \fB%1\fP
limit of sms messages sended in one walk in loop
.TP
.B \fB%2\fP
bit mask for the current weekday in the SMSD process local timezone,
using Monday as 1 through Sunday as 64
.UNINDENT
.sp
Custom queries need to use \fB%2\fP to enforce the outbox \%<#\:outbox>
\fBSendDays\fP restriction. The default bitwise expression is adapted to
the configured SQL dialect.
.UNINDENT
.INDENT 0.0
.TP
.B find_outbox_body
Select body of message.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
SELECT Text, Coding, UDH, Class, TextDecoded, ID, DestinationNumber, MultiPart,
RelativeValidity, DeliveryReport, CreatorID FROM outbox WHERE ID=%1
.EE
.UNINDENT
.UNINDENT
.sp
Query specific parameters:
.INDENT 7.0
.TP
.B \fB%1\fP
ID of message
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B find_outbox_multipart
Select remaining parts of sms message.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
SELECT Text, Coding, UDH, Class, TextDecoded, ID, SequencePosition
FROM outbox_multipart WHERE ID=%1 AND SequencePosition=%2
.EE
.UNINDENT
.UNINDENT
.sp
Query specific parameters:
.INDENT 7.0
.TP
.B \fB%1\fP
ID of message
.TP
.B \fB%2\fP
Number of multipart message
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B find_sent_item
Find an existing sent message before transmission. SMSD uses this to
reconcile messages which were written to \fBsentitems\fP but not removed
from \fBoutbox\fP, for example when the daemon stopped between these two
operations.
.sp
The selected columns and their order are mandatory. SMSD compares the
stored message identity and payload with the outbox message. A matching
successfully sent message is skipped; a different message using the same
ID is left in the outbox and reported as a conflict.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
SELECT Text, Coding, UDH, Class, TextDecoded, DestinationNumber,
InsertIntoDB, RelativeValidity, CreatorID, Status
FROM sentitems WHERE ID=%1 AND SequencePosition=%2
.EE
.UNINDENT
.UNINDENT
.sp
Query specific parameters:
.INDENT 7.0
.TP
.B \fB%1\fP
ID of message
.TP
.B \fB%2\fP
Number of multipart message
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B delete_outbox
Remove messages from outbox after threir successful send.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
DELETE FROM outbox WHERE ID=%1
.EE
.UNINDENT
.UNINDENT
.sp
Query specific parameters:
.INDENT 7.0
.TP
.B \fB%1\fP
ID of message
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B delete_outbox_multipart
Remove messages from outbox_multipart after threir successful send.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
DELETE FROM outbox_multipart WHERE ID=%1
.EE
.UNINDENT
.UNINDENT
.sp
Query specific parameters:
.INDENT 7.0
.TP
.B \fB%1\fP
ID of message
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B create_outbox
Create message (insert to outbox).
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
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)
.EE
.UNINDENT
.UNINDENT
.sp
Query specific parameters:
.INDENT 7.0
.TP
.B \fB%1\fP
creator of message
.TP
.B \fB%2\fP
delivery status report \- yes/default
.TP
.B \fB%3\fP
multipart \- FALSE/TRUE
.TP
.B \fB%4\fP
Part (part number)
.TP
.B \fB%5\fP
ID of message
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B create_outbox_multipart
Create message remaining parts.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
INSERT INTO outbox_multipart (SequencePosition, Text, Coding, UDH, Class,
TextDecoded, ID) VALUES (%4, %E, %c, %u, %x, %T, %5)
.EE
.UNINDENT
.UNINDENT
.sp
Query specific parameters:
.INDENT 7.0
.TP
.B \fB%1\fP
creator of message
.TP
.B \fB%2\fP
delivery status report \- yes/default
.TP
.B \fB%3\fP
multipart \- FALSE/TRUE
.TP
.B \fB%4\fP
Part (part number)
.TP
.B \fB%5\fP
ID of message
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B add_sent_info
Insert to sentitems.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
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)
.EE
.UNINDENT
.UNINDENT
.sp
Query specific parameters:
.INDENT 7.0
.TP
.B \fB%1\fP
ID of sms message
.TP
.B \fB%2\fP
part number (for multipart sms)
.TP
.B \fB%3\fP
message state (SendingError, Error, SendingOK, SendingOKNoReport)
.TP
.B \fB%4\fP
message reference (TPMR)
.TP
.B \fB%5\fP
time when inserted in db
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B update_sent
Update sent statistics after sending message.
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
UPDATE phones SET Sent= Sent + 1 WHERE IMEI = %I
.EE
.UNINDENT
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B refresh_phone_status
Update phone status (battery, signal, and network).
.sp
Default value:
.INDENT 7.0
.INDENT 3.5
.sp
.EX
UPDATE phones SET TimeOut = (NOW() + INTERVAL 70 SECOND) + 0,
Battery = %1, Signal = %2, NetCode = %O, NetName = %M
WHERE IMEI = %I
.EE
.UNINDENT
.UNINDENT
.sp
The expiry interval is generated from \fBStatusFrequency\fP \%<#\:option-StatusFrequency>
plus a ten\-second grace period, as described for \fBinsert_phone\fP\&.
Custom queries should keep \fBTimeOut\fP valid through the next refresh.
.sp
Query specific parameters:
.INDENT 7.0
.TP
.B \fB%1\fP
battery percent
.TP
.B \fB%2\fP
signal percent
.UNINDENT
.UNINDENT
.INDENT 0.0
.TP
.B update_retries
Update number of retries for outbox message. The interval can be configured
by \fBRetryTimeout\fP \%<#\:option-RetryTimeout>\&.
.INDENT 7.0
.INDENT 3.5
.sp
.EX
UPDATE outbox SET SendngTimeOut = (NOW() + INTERVAL 600 SECOND) + 0,
Retries = %2 WHERE ID = %1
.EE
.UNINDENT
.UNINDENT
.sp
Query specific parameters:
.INDENT 7.0
.TP
.B \fB%1\fP
message ID
.TP
.B \fB%2\fP
number of retries
.UNINDENT
.UNINDENT
.SH Author
Michal Čihař <michal@cihar.com>
.SH Copyright
2009-2015, Michal Čihař <michal@cihar.com>
.\" End of generated man page.