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

NAME

6       gammu-smsd-mysql - gammu-smsd(1) backend using MySQL database server as
7       a message storage
8

DESCRIPTION

10       MYSQL backend stores all data in a MySQL database server, which parame‐
11       ters  are defined by configuration (see gammu-smsdrc for description of
12       configuration options).
13
14       For tables description see gammu-smsd-tables.
15
16       This backend is based on gammu-smsd-sql.
17

CONFIGURATION

19       Before running gammu-smsd you need to create necessary  tables  in  the
20       database, which is described below.
21
22       The configuration file then can look like:
23
24          [smsd]
25          service = sql
26          driver = native_mysql
27          host = localhost
28
29       SEE ALSO:
30          gammu-smsdrc
31

PRIVILEGES

33       The user accessing the database does not need much privileges, the fol‐
34       lowing privileges should be enough:
35
36          GRANT USAGE ON *.* TO 'smsd'@'localhost' IDENTIFIED BY 'password';
37
38          GRANT SELECT, INSERT, UPDATE, DELETE ON `smsd`.* TO 'smsd'@'localhost';
39
40          CREATE DATABASE smsd;
41
42       NOTE:
43          For creating the SQL tables you need more privileges, especially for
44          creating triggers, which are used for some functionality.
45

CREATING TABLES FOR MYSQL

47       Depending  on  MySQL  version  and  settings please choose best fitting
48       script to create tables:
49
50       · mysql-strict.sql can be used in strict mode, but requires MySQL 5.6.5
51         or newer
52
53       · mysql.sql  supports  all  MySQL  versions,  but  requires  neither of
54         NO_ZERO_DATE, ANSI or STRICT modes to be set in the server
55
56       SQL script mysql.sql for creating tables in MySQL database:
57
58          --
59          -- Database for Gammu SMSD
60          --
61          -- In case you get errors about not supported charset, please
62          -- replace utf8mb4 with utf8.
63
64          -- --------------------------------------------------------
65
66          --
67          -- Table structure for table `gammu`
68          --
69
70          CREATE TABLE `gammu` (
71            `Version` integer NOT NULL default '0' PRIMARY KEY
72          ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
73
74          --
75          -- Dumping data for table `gammu`
76          --
77
78          INSERT INTO `gammu` (`Version`) VALUES (17);
79
80          -- --------------------------------------------------------
81
82          --
83          -- Table structure for table `inbox`
84          --
85
86          CREATE TABLE `inbox` (
87            `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
88            `ReceivingDateTime` timestamp NOT NULL default '0000-00-00 00:00:00',
89            `Text` text NOT NULL,
90            `SenderNumber` varchar(20) NOT NULL default '',
91            `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
92            `UDH` text NOT NULL,
93            `SMSCNumber` varchar(20) NOT NULL default '',
94            `Class` integer NOT NULL default '-1',
95            `TextDecoded` text NOT NULL,
96            `ID` integer unsigned NOT NULL auto_increment,
97            `RecipientID` text NOT NULL,
98            `Processed` enum('false','true') NOT NULL default 'false',
99            `Status` integer NOT NULL default '-1',
100            PRIMARY KEY `ID` (`ID`)
101          ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;
102
103          --
104          -- Dumping data for table `inbox`
105          --
106
107
108          -- --------------------------------------------------------
109
110          --
111          -- Table structure for table `outbox`
112          --
113
114          CREATE TABLE `outbox` (
115            `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
116            `InsertIntoDB` timestamp NOT NULL default '0000-00-00 00:00:00',
117            `SendingDateTime` timestamp NOT NULL default '0000-00-00 00:00:00',
118            `SendBefore` time NOT NULL DEFAULT '23:59:59',
119            `SendAfter` time NOT NULL DEFAULT '00:00:00',
120            `Text` text,
121            `DestinationNumber` varchar(20) NOT NULL default '',
122            `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
123            `UDH` text,
124            `Class` integer default '-1',
125            `TextDecoded` text NOT NULL,
126            `ID` integer unsigned NOT NULL auto_increment,
127            `MultiPart` enum('false','true') default 'false',
128            `RelativeValidity` integer default '-1',
129            `SenderID` varchar(255),
130            `SendingTimeOut` timestamp NULL default '0000-00-00 00:00:00',
131            `DeliveryReport` enum('default','yes','no') default 'default',
132            `CreatorID` text NOT NULL,
133            `Retries` int(3) default 0,
134            `Priority` integer default 0,
135            `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error','Reserved') NOT NULL default 'Reserved',
136            `StatusCode` integer NOT NULL default '-1',
137            PRIMARY KEY `ID` (`ID`)
138          ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
139
140          CREATE INDEX outbox_date ON outbox(SendingDateTime, SendingTimeOut);
141          CREATE INDEX outbox_sender ON outbox(SenderID(250));
142
143          --
144          -- Dumping data for table `outbox`
145          --
146
147
148          -- --------------------------------------------------------
149
150          --
151          -- Table structure for table `outbox_multipart`
152          --
153
154          CREATE TABLE `outbox_multipart` (
155            `Text` text,
156            `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
157            `UDH` text,
158            `Class` integer default '-1',
159            `TextDecoded` text,
160            `ID` integer unsigned NOT NULL default '0',
161            `SequencePosition` integer NOT NULL default '1',
162            `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error','Reserved') NOT NULL default 'Reserved',
163            `StatusCode` integer NOT NULL default '-1',
164            PRIMARY KEY (`ID`, `SequencePosition`)
165          ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
166
167          --
168          -- Dumping data for table `outbox_multipart`
169          --
170
171          -- --------------------------------------------------------
172
173          --
174          -- Table structure for table `phones`
175          --
176
177          CREATE TABLE `phones` (
178            `ID` text NOT NULL,
179            `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
180            `InsertIntoDB` timestamp NOT NULL default '0000-00-00 00:00:00',
181            `TimeOut` timestamp NOT NULL default '0000-00-00 00:00:00',
182            `Send` enum('yes','no') NOT NULL default 'no',
183            `Receive` enum('yes','no') NOT NULL default 'no',
184            `IMEI` varchar(35) NOT NULL,
185            `IMSI` varchar(35) NOT NULL,
186            `NetCode` varchar(10) default 'ERROR',
187            `NetName` varchar(35) default 'ERROR',
188            `Client` text NOT NULL,
189            `Battery` integer NOT NULL DEFAULT -1,
190            `Signal` integer NOT NULL DEFAULT -1,
191            `Sent` int NOT NULL DEFAULT 0,
192            `Received` int NOT NULL DEFAULT 0,
193            PRIMARY KEY (`IMEI`)
194          ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
195
196          --
197          -- Dumping data for table `phones`
198          --
199
200          -- --------------------------------------------------------
201
202          --
203          -- Table structure for table `sentitems`
204          --
205
206          CREATE TABLE `sentitems` (
207            `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
208            `InsertIntoDB` timestamp NOT NULL default '0000-00-00 00:00:00',
209            `SendingDateTime` timestamp NOT NULL default '0000-00-00 00:00:00',
210            `DeliveryDateTime` timestamp NULL,
211            `Text` text NOT NULL,
212            `DestinationNumber` varchar(20) NOT NULL default '',
213            `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
214            `UDH` text NOT NULL,
215            `SMSCNumber` varchar(20) NOT NULL default '',
216            `Class` integer NOT NULL default '-1',
217            `TextDecoded` text NOT NULL,
218            `ID` integer unsigned NOT NULL default '0',
219            `SenderID` varchar(255) NOT NULL,
220            `SequencePosition` integer NOT NULL default '1',
221            `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error') NOT NULL default 'SendingOK',
222            `StatusError` integer NOT NULL default '-1',
223            `TPMR` integer NOT NULL default '-1',
224            `RelativeValidity` integer NOT NULL default '-1',
225            `CreatorID` text NOT NULL,
226            `StatusCode` integer NOT NULL default '-1',
227            PRIMARY KEY (`ID`, `SequencePosition`)
228          ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
229
230          CREATE INDEX sentitems_date ON sentitems(DeliveryDateTime);
231          CREATE INDEX sentitems_tpmr ON sentitems(TPMR);
232          CREATE INDEX sentitems_dest ON sentitems(DestinationNumber);
233          CREATE INDEX sentitems_sender ON sentitems(SenderID(250));
234
235          --
236          -- Dumping data for table `sentitems`
237          --
238
239
240          --
241          -- Triggers for setting default timestamps
242          --
243
244          DELIMITER //
245
246          CREATE TRIGGER inbox_timestamp BEFORE INSERT ON inbox
247          FOR EACH ROW
248          BEGIN
249              IF NEW.ReceivingDateTime = '0000-00-00 00:00:00' THEN
250                  SET NEW.ReceivingDateTime = CURRENT_TIMESTAMP();
251              END IF;
252          END;//
253
254          CREATE TRIGGER outbox_timestamp BEFORE INSERT ON outbox
255          FOR EACH ROW
256          BEGIN
257              IF NEW.InsertIntoDB = '0000-00-00 00:00:00' THEN
258                  SET NEW.InsertIntoDB = CURRENT_TIMESTAMP();
259              END IF;
260              IF NEW.SendingDateTime = '0000-00-00 00:00:00' THEN
261                  SET NEW.SendingDateTime = CURRENT_TIMESTAMP();
262              END IF;
263              IF NEW.SendingTimeOut = '0000-00-00 00:00:00' THEN
264                  SET NEW.SendingTimeOut = CURRENT_TIMESTAMP();
265              END IF;
266          END;//
267
268          CREATE TRIGGER phones_timestamp BEFORE INSERT ON phones
269          FOR EACH ROW
270          BEGIN
271              IF NEW.InsertIntoDB = '0000-00-00 00:00:00' THEN
272                  SET NEW.InsertIntoDB = CURRENT_TIMESTAMP();
273              END IF;
274              IF NEW.TimeOut = '0000-00-00 00:00:00' THEN
275                  SET NEW.TimeOut = CURRENT_TIMESTAMP();
276              END IF;
277          END;//
278
279          CREATE TRIGGER sentitems_timestamp BEFORE INSERT ON sentitems
280          FOR EACH ROW
281          BEGIN
282              IF NEW.InsertIntoDB = '0000-00-00 00:00:00' THEN
283                  SET NEW.InsertIntoDB = CURRENT_TIMESTAMP();
284              END IF;
285              IF NEW.SendingDateTime = '0000-00-00 00:00:00' THEN
286                  SET NEW.SendingDateTime = CURRENT_TIMESTAMP();
287              END IF;
288          END;//
289
290          DELIMITER ;
291
292
293
294       NOTE:
295          You can find the script in docs/sql/mysql.sql as well.
296
297       SQL script mysql-strict.sql for creating tables in MySQL database:
298
299          --
300          -- Database for Gammu SMSD
301          --
302          -- In case you get errors about not supported charset, please
303          -- replace utf8mb4 with utf8.
304
305          -- --------------------------------------------------------
306
307          --
308          -- Table structure for table `gammu`
309          --
310
311          CREATE TABLE `gammu` (
312            `Version` integer NOT NULL default '0' PRIMARY KEY
313          ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
314
315          --
316          -- Dumping data for table `gammu`
317          --
318
319          INSERT INTO `gammu` (`Version`) VALUES (17);
320
321          -- --------------------------------------------------------
322
323          --
324          -- Table structure for table `inbox`
325          --
326
327          CREATE TABLE `inbox` (
328            `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
329            `ReceivingDateTime` timestamp NOT NULL default CURRENT_TIMESTAMP,
330            `Text` text NOT NULL,
331            `SenderNumber` varchar(20) NOT NULL default '',
332            `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
333            `UDH` text NOT NULL,
334            `SMSCNumber` varchar(20) NOT NULL default '',
335            `Class` integer NOT NULL default '-1',
336            `TextDecoded` text NOT NULL,
337            `ID` integer unsigned NOT NULL auto_increment,
338            `RecipientID` text NOT NULL,
339            `Processed` enum('false','true') NOT NULL default 'false',
340            `Status` integer NOT NULL default '-1',
341            PRIMARY KEY `ID` (`ID`)
342          ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;
343
344          --
345          -- Dumping data for table `inbox`
346          --
347
348
349          -- --------------------------------------------------------
350
351          --
352          -- Table structure for table `outbox`
353          --
354
355          CREATE TABLE `outbox` (
356            `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
357            `InsertIntoDB` timestamp NOT NULL default CURRENT_TIMESTAMP,
358            `SendingDateTime` timestamp NOT NULL default CURRENT_TIMESTAMP,
359            `SendBefore` time NOT NULL DEFAULT '23:59:59',
360            `SendAfter` time NOT NULL DEFAULT '00:00:00',
361            `Text` text,
362            `DestinationNumber` varchar(20) NOT NULL default '',
363            `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
364            `UDH` text,
365            `Class` integer default '-1',
366            `TextDecoded` text NOT NULL,
367            `ID` integer unsigned NOT NULL auto_increment,
368            `MultiPart` enum('false','true') default 'false',
369            `RelativeValidity` integer default '-1',
370            `SenderID` varchar(255),
371            `SendingTimeOut` timestamp NULL default CURRENT_TIMESTAMP,
372            `DeliveryReport` enum('default','yes','no') default 'default',
373            `CreatorID` text NOT NULL,
374            `Retries` int(3) default 0,
375            `Priority` integer default 0,
376            `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error','Reserved') NOT NULL default 'Reserved',
377            `StatusCode` integer NOT NULL default '-1',
378            PRIMARY KEY `ID` (`ID`)
379          ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
380
381          CREATE INDEX outbox_date ON outbox(SendingDateTime, SendingTimeOut);
382          CREATE INDEX outbox_sender ON outbox(SenderID(250));
383
384          --
385          -- Dumping data for table `outbox`
386          --
387
388
389          -- --------------------------------------------------------
390
391          --
392          -- Table structure for table `outbox_multipart`
393          --
394
395          CREATE TABLE `outbox_multipart` (
396            `Text` text,
397            `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
398            `UDH` text,
399            `Class` integer default '-1',
400            `TextDecoded` text,
401            `ID` integer unsigned NOT NULL default '0',
402            `SequencePosition` integer NOT NULL default '1',
403            `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error','Reserved') NOT NULL default 'Reserved',
404            `StatusCode` integer NOT NULL default '-1',
405            PRIMARY KEY (`ID`, `SequencePosition`)
406          ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
407
408          --
409          -- Dumping data for table `outbox_multipart`
410          --
411
412          -- --------------------------------------------------------
413
414          --
415          -- Table structure for table `phones`
416          --
417
418          CREATE TABLE `phones` (
419            `ID` text NOT NULL,
420            `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
421            `InsertIntoDB` timestamp NOT NULL default CURRENT_TIMESTAMP,
422            `TimeOut` timestamp NOT NULL default CURRENT_TIMESTAMP,
423            `Send` enum('yes','no') NOT NULL default 'no',
424            `Receive` enum('yes','no') NOT NULL default 'no',
425            `IMEI` varchar(35) NOT NULL,
426            `IMSI` varchar(35) NOT NULL,
427            `NetCode` varchar(10) default 'ERROR',
428            `NetName` varchar(35) default 'ERROR',
429            `Client` text NOT NULL,
430            `Battery` integer NOT NULL DEFAULT -1,
431            `Signal` integer NOT NULL DEFAULT -1,
432            `Sent` int NOT NULL DEFAULT 0,
433            `Received` int NOT NULL DEFAULT 0,
434            PRIMARY KEY (`IMEI`)
435          ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
436
437          --
438          -- Dumping data for table `phones`
439          --
440
441          -- --------------------------------------------------------
442
443          --
444          -- Table structure for table `sentitems`
445          --
446
447          CREATE TABLE `sentitems` (
448            `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
449            `InsertIntoDB` timestamp NOT NULL default CURRENT_TIMESTAMP,
450            `SendingDateTime` timestamp NOT NULL default CURRENT_TIMESTAMP,
451            `DeliveryDateTime` timestamp NULL,
452            `Text` text NOT NULL,
453            `DestinationNumber` varchar(20) NOT NULL default '',
454            `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
455            `UDH` text NOT NULL,
456            `SMSCNumber` varchar(20) NOT NULL default '',
457            `Class` integer NOT NULL default '-1',
458            `TextDecoded` text NOT NULL,
459            `ID` integer unsigned NOT NULL default '0',
460            `SenderID` varchar(255) NOT NULL,
461            `SequencePosition` integer NOT NULL default '1',
462            `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error') NOT NULL default 'SendingOK',
463            `StatusError` integer NOT NULL default '-1',
464            `TPMR` integer NOT NULL default '-1',
465            `RelativeValidity` integer NOT NULL default '-1',
466            `CreatorID` text NOT NULL,
467            `StatusCode` integer NOT NULL default '-1',
468            PRIMARY KEY (`ID`, `SequencePosition`)
469          ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
470
471          CREATE INDEX sentitems_date ON sentitems(DeliveryDateTime);
472          CREATE INDEX sentitems_tpmr ON sentitems(TPMR);
473          CREATE INDEX sentitems_dest ON sentitems(DestinationNumber);
474          CREATE INDEX sentitems_sender ON sentitems(SenderID(250));
475
476          --
477          -- Dumping data for table `sentitems`
478          --
479
480
481       NOTE:
482          You can find the script in docs/sql/mysql-strict.sql as well.
483

UPGRADING TABLES

485       The easiest way to upgrade database structure is to backup old one  and
486       start with creating new one based on example above.
487
488       For  upgrading  existing  database,  you  can  use changes described in
489       smsd-tables-history and then manually update Version field in gammu ta‐
490       ble.
491

AUTHOR

493       Michal Čihař <michal@cihar.com>
494

496       2009-2015, Michal Čihař <michal@cihar.com>
497
498
499
500
5011.39.0                           Jan 05, 2018              GAMMU-SMSD-MYSQL(7)
Impressum