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 (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.

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

    %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 [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

AUTHOR
Michal Čihař <michal@cihar.com>

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