1GAMMU-SMSD-DBI(7)                    Gammu                   GAMMU-SMSD-DBI(7)
2
3
4

NAME

6       gammu-smsd-dbi  -  gammu-smsd(1) backend using DBI abstraction layer to
7       use any supported database as a message storage
8

DESCRIPTION

10       DBI backend stores all data in any database supported by libdbi,  which
11       parameters  are defined by configuration (see gammu-smsdrc for descrip‐
12       tion of configuration options).
13
14       For tables description see gammu-smsd-tables.
15
16       This backend is based on gammu-smsd-sql.
17
18       NOTE:
19          The DBI driver is currently not supported on Windows because  libdbi
20          library does not support this platform.
21

CONFIGURATION

23       Before  running  gammu-smsd  you need to create necessary tables in the
24       database. You can use examples  given  in  database  specific  backends
25       parts of this manual to do that.
26
27       The configuration file then can look like:
28
29          [smsd]
30          service = sql
31          driver = DBI_DRIVER
32          host = localhost
33
34       SEE ALSO:
35          gammu-smsdrc
36

SUPPORTED DRIVERS

38       For complete list of drivers for libdbi see libdbi-drivers project. The
39       drivers for example include:
40
41       · sqlite3 - for SQLite 3
42
43       · mysql - for MySQL
44
45       · pgsql - for PostgeSQL
46
47       · freetds - for MS SQL Server or Sybase
48

CREATING TABLES FOR SQLITE

50       SQL script for creating tables in SQLite database:
51
52          CREATE TABLE gammu (
53            Version INTEGER NOT NULL DEFAULT '0' PRIMARY KEY
54          );
55
56          INSERT INTO gammu (Version) VALUES (17);
57
58          CREATE TABLE inbox (
59            UpdatedInDB NUMERIC NOT NULL DEFAULT (datetime('now')),
60            ReceivingDateTime NUMERIC NOT NULL DEFAULT (datetime('now')),
61            Text TEXT NOT NULL,
62            SenderNumber TEXT NOT NULL DEFAULT '',
63            Coding TEXT NOT NULL DEFAULT 'Default_No_Compression',
64            UDH TEXT NOT NULL,
65            SMSCNumber TEXT NOT NULL DEFAULT '',
66            Class INTEGER NOT NULL DEFAULT '-1',
67            TextDecoded TEXT NOT NULL DEFAULT '',
68            ID INTEGER PRIMARY KEY AUTOINCREMENT,
69            RecipientID TEXT NOT NULL,
70            Processed TEXT NOT NULL DEFAULT 'false',
71            Status INTEGER NOT NULL DEFAULT '-1',
72            CHECK (Coding IN
73            ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression'))
74          );
75
76          CREATE TRIGGER update_inbox_time UPDATE ON inbox
77            BEGIN
78              UPDATE inbox SET UpdatedInDB = datetime('now') WHERE ID = old.ID;
79            END;
80
81          CREATE TABLE outbox (
82            UpdatedInDB NUMERIC NOT NULL DEFAULT (datetime('now')),
83            InsertIntoDB NUMERIC NOT NULL DEFAULT (datetime('now')),
84            SendingDateTime NUMERIC NOT NULL DEFAULT (datetime('now')),
85            SendBefore time NOT NULL DEFAULT '23:59:59',
86            SendAfter time NOT NULL DEFAULT '00:00:00',
87            Text TEXT,
88            DestinationNumber TEXT NOT NULL DEFAULT '',
89            Coding TEXT NOT NULL DEFAULT 'Default_No_Compression',
90            UDH TEXT,
91            Class INTEGER DEFAULT '-1',
92            TextDecoded TEXT NOT NULL DEFAULT '',
93            ID INTEGER PRIMARY KEY AUTOINCREMENT,
94            MultiPart TEXT NOT NULL DEFAULT 'false',
95            RelativeValidity INTEGER DEFAULT '-1',
96            SenderID TEXT,
97            SendingTimeOut NUMERIC NOT NULL DEFAULT (datetime('now')),
98            DeliveryReport TEXT DEFAULT 'default',
99            CreatorID TEXT NOT NULL,
100            Retries INTEGER DEFAULT '0',
101            Priority INTEGER DEFAULT '0',
102            Status TEXT NOT NULL DEFAULT 'Reserved',
103            StatusCode INTEGER NOT NULL DEFAULT '-1',
104            CHECK (Coding IN
105            ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
106            CHECK (DeliveryReport IN ('default','yes','no')),
107            CHECK (Status IN
108            ('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
109            'DeliveryUnknown','Error','Reserved'))
110          );
111
112          CREATE INDEX outbox_date ON outbox(SendingDateTime, SendingTimeOut);
113          CREATE INDEX outbox_sender ON outbox(SenderID);
114
115          CREATE TRIGGER update_outbox_time UPDATE ON outbox
116            BEGIN
117              UPDATE outbox SET UpdatedInDB = datetime('now') WHERE ID = old.ID;
118            END;
119
120          CREATE TABLE outbox_multipart (
121            Text TEXT,
122            Coding TEXT NOT NULL DEFAULT 'Default_No_Compression',
123            UDH TEXT,
124            Class INTEGER DEFAULT '-1',
125            TextDecoded TEXT DEFAULT NULL,
126            ID INTEGER,
127            SequencePosition INTEGER NOT NULL DEFAULT '1',
128            Status TEXT NOT NULL DEFAULT 'Reserved',
129            StatusCode INTEGER NOT NULL DEFAULT '-1',
130            CHECK (Coding IN
131            ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
132            CHECK (Status IN
133            ('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
134            'DeliveryUnknown','Error','Reserved')),
135           PRIMARY KEY (ID, SequencePosition)
136          );
137
138          CREATE TABLE phones (
139            ID TEXT NOT NULL,
140            UpdatedInDB NUMERIC NOT NULL DEFAULT (datetime('now')),
141            InsertIntoDB NUMERIC NOT NULL DEFAULT (datetime('now')),
142            TimeOut NUMERIC NOT NULL DEFAULT (datetime('now')),
143            Send TEXT NOT NULL DEFAULT 'no',
144            Receive TEXT NOT NULL DEFAULT 'no',
145            IMEI TEXT PRIMARY KEY NOT NULL,
146            IMSI TEXT NOT NULL,
147            NetCode TEXT DEFAULT 'ERROR',
148            NetName TEXT DEFAULT 'ERROR',
149            Client TEXT NOT NULL,
150            Battery INTEGER NOT NULL DEFAULT -1,
151            Signal INTEGER NOT NULL DEFAULT -1,
152            Sent INTEGER NOT NULL DEFAULT 0,
153            Received INTEGER NOT NULL DEFAULT 0
154          );
155
156          CREATE TRIGGER update_phones_time UPDATE ON phones
157            BEGIN
158              UPDATE phones SET UpdatedInDB = datetime('now') WHERE IMEI = old.IMEI;
159            END;
160
161          CREATE TABLE sentitems (
162            UpdatedInDB NUMERIC NOT NULL DEFAULT (datetime('now')),
163            InsertIntoDB NUMERIC NOT NULL DEFAULT (datetime('now')),
164            SendingDateTime NUMERIC NOT NULL DEFAULT (datetime('now')),
165            DeliveryDateTime NUMERIC NULL,
166            Text TEXT NOT NULL,
167            DestinationNumber TEXT NOT NULL DEFAULT '',
168            Coding TEXT NOT NULL DEFAULT 'Default_No_Compression',
169            UDH TEXT NOT NULL,
170            SMSCNumber TEXT NOT NULL DEFAULT '',
171            Class INTEGER NOT NULL DEFAULT '-1',
172            TextDecoded TEXT NOT NULL DEFAULT '',
173            ID INTEGER,
174            SenderID TEXT NOT NULL,
175            SequencePosition INTEGER NOT NULL DEFAULT '1',
176            Status TEXT NOT NULL DEFAULT 'SendingOK',
177            StatusError INTEGER NOT NULL DEFAULT '-1',
178            TPMR INTEGER NOT NULL DEFAULT '-1',
179            RelativeValidity INTEGER NOT NULL DEFAULT '-1',
180            CreatorID TEXT NOT NULL,
181            StatusCode INTEGER NOT NULL DEFAULT '-1',
182            CHECK (Status IN
183            ('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
184            'DeliveryUnknown','Error')),
185            CHECK (Coding IN
186            ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')) ,
187            PRIMARY KEY (ID, SequencePosition)
188          );
189
190          CREATE INDEX sentitems_date ON sentitems(DeliveryDateTime);
191          CREATE INDEX sentitems_tpmr ON sentitems(TPMR);
192          CREATE INDEX sentitems_dest ON sentitems(DestinationNumber);
193          CREATE INDEX sentitems_sender ON sentitems(SenderID);
194
195          CREATE TRIGGER update_sentitems_time UPDATE ON sentitems
196            BEGIN
197              UPDATE sentitems SET UpdatedInDB = datetime('now') WHERE ID = old.ID;
198            END;
199
200
201       NOTE:
202          You can find the script in docs/sql/sqlite.sql as  well.  There  are
203          also scripts for other databases in same folder.
204

UPGRADING TABLES

206       The  easiest way to upgrade database structure is to backup old one and
207       start with creating new one based on example above.
208
209       For upgrading existing database,  you  can  use  changes  described  in
210       smsd-tables-history and then manually update Version field in gammu ta‐
211       ble.
212

AUTHOR

214       Michal Čihař <michal@cihar.com>
215

217       2009-2015, Michal Čihař <michal@cihar.com>
218
219
220
221
2221.41.0                           Sep 27, 2019                GAMMU-SMSD-DBI(7)
Impressum