GAMMU-SMSD-SQL(7)

🎀 GAMMU-SMSD-SQL 💅

okay bestie, welcome to gammu-smsd-sql(7) — this is the page in the gammu manual that explains the gammu-smsd(1) backend which uses the SQL abstraction layer so you can use literally any supported database as your message storage. no cap, it's a whole vibe.

---

✨ DESCRIPTION ✨

so the SQL service stores, like, all of its data in a database — that's the whole thing. it can use one of these SQL backends (that's the configuration option Driver in the smsd section, iykyk):

• native_mysql for the MySQL Backend
• native_pgsql for the PostgreSQL Backend
• odbc for the ODBC Backend
• drivers supported by DBI for the DBI Backend, which include:
  • sqlite3 — for SQLite 3
  • mysql — for MySQL
  • pgsql — for PostgeSQL
  • freetds — for MS SQL Server or Sybase

---

🔌 SQL CONNECTION PARAMETERS 🌈

these ones are common for all backends:

• User — the user connecting to the database
• Password — the password for connecting to the database
• Host — the database host or data source name
• Database — the database name
• Driver — native_mysql, native_pgsql, odbc or a DBI one
• SQL — the SQL dialect to use

these ones are specific to DBI:

• DriversPath — path to the DBI drivers
• DBDir — the sqlite/sqlite3 directory with the database

See also: the variables are fully described in the Gammu Configuration File documentation. 🫶

---

🗂️ TABLES 🎀

added in version 1.37.1. periodt.

you can customize the name of, like, every table in the [tables] section. the SQL queries will reflect that change, so honestly it's enough to just change the table name in this section. slay.

• gammu — name of the gammu 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 the [sql] section. there are several variables used in SQL queries and we can separate them into three groups:

• phone specific ones, which can be used in every query — see Phone Specific Parameters
• SMS specific ones, which can be used in queries that work with SMS messages — see SMS Specific Parameters
• query specific ones, which are numeric and are specific only for a 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), destination otherwise. 💅

---

👑 CONFIGURABLE QUERIES 🔥

all configurable queries can be set in the [sql] section. the sequence of rows in selects is mandatory — do not play with that, fr fr.

all default queries noted here are noted for MySQL. actual time and time addition are selected for default queries during initialization. noted.

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, 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)

the shown 70-second expiry uses the default 60-second StatusFrequency plus a ten-second grace period. the generated default query follows the configured frequency. custom queries should provide an equivalent expiry interval.

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, 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)

query specific parameters:

• %1 — logical message identifier, or 0 until the first physical row ID is known
• %2 — sequence position of this physical part
• %3 — expected number of physical parts
• %4 — initial processed state; received rows are staged as processed until their logical message identifier is finalized

⚠️ custom queries must store the database server's current timestamp in InsertIntoDB so incomplete multipart groups can be restored without extending their original timeout. don't skip this, bestie 😭

save_inbox_sms_update_metadata

finalize the logical message identifier and multipart metadata, then publish the received physical message to inbox consumers.

default value:

UPDATE inbox SET MessageID = %1, SequencePosition = %2, PartCount = %3,
Processed = %5 WHERE ID = %4

query specific parameters:

• %1 — ID of the first stored physical part of the logical message
• %2 — sequence position of this physical part
• %3 — expected number of physical parts
• %4 — ID of this physical part
• %5 — final processed state; false publishes the completed row

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.

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. iconic, honestly. 🦋

default value:

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

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 the 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
(SendDays & %2) <> 0 AND
( SenderID is NULL OR SenderID = '' OR SenderID = %P )
ORDER BY Priority DESC, InsertIntoDB ASC LIMIT %1

query specific parameters:

• %1 — limit of sms messages sended in one walk in loop
• %2 — bit mask for the current weekday in the SMSD process local timezone, using Monday as 1 through Sunday as 64

⚠️ custom queries need to use %2 to enforce the outbox SendDays restriction. the default bitwise expression is adapted to the configured SQL dialect. yep, keep it.

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

find_sent_item

find an existing sent message before transmission. SMSD uses this to reconcile messages which were written to sentitems but not removed from outbox, for example when the daemon stopped between these two operations. 💀

The selected columns and their order are mandatory — like, don't shuffle them, don't skip one, nope 🙅. SMSD compares the stored message identity and payload against the outbox message. If it's a matching message that already sent successfully, it gets skipped (slay, we're done here). But if it's a different message chilling under the same ID, it stays in the outbox and gets reported as a conflict 😭.

Default value:

SELECT Text, Coding, UDH, Class, TextDecoded, DestinationNumber,
InsertIntoDB, RelativeValidity, CreatorID, Status
FROM sentitems WHERE ID=%1 AND SequencePosition=%2

Query specific parameters:

• %1 — ID of message
• %2 — Number of multipart message

---

delete_outbox

Removes messages from outbox once they've sent successfully. Bye bestie 💅

Default value:

DELETE FROM outbox WHERE ID=%1

Query specific parameters:

• %1 — ID of message

delete_outbox_multipart

Same energy, but it clears messages out of outbox_multipart after their successful send 🧹✨

Default value:

DELETE FROM outbox_multipart WHERE ID=%1

Query specific parameters:

• %1 — ID of message

create_outbox

Creates a message — aka inserts it into outbox. Main character entry moment 👑

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

Creates the remaining parts of a message — the rest of the multipart squad showing up 🫶

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

Inserts into sentitems — the receipt of record, no cap 📝

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

Updates the sent statistics after sending a message. Gotta keep the receipts up to date, periodt 📊

Default value:

UPDATE phones SET Sent= Sent + 1 WHERE IMEI = %I

refresh_phone_status

Updates phone status (battery, signal, and network). Little wellness check for your modem 🌸🔋

Default value:

UPDATE phones SET TimeOut = (NOW() + INTERVAL 70 SECOND) + 0,
Battery = %1, Signal = %2, NetCode = %O, NetName = %M
WHERE IMEI = %I

The expiry interval is generated from the StatusFrequency option plus a ten-second grace period, as described for insert_phone. Custom queries should keep TimeOut valid through the next refresh — like, don't break the timing bestie 😭.

Query specific parameters:

• %1 — battery percent
• %2 — signal percent

update_retries

Updates the number of retries for an outbox message. The interval can be configured by the RetryTimeout option 🔁

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

---

🦋 COPYRIGHT

2009-2015, Michal Čihař <michal@cihar.com>

---

And that's the whole page, bestie, you ate it up 🎀✨ This edition is 1.45.0, dated Sep 16, 2026, and it lives at GAMMU-SMSD-SQL(7) — go forth and query responsibly 💖