gammu-smsd-mysql(7)

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

UPGRADING TABLES

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

AUTHOR

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

COPYRIGHT

495       2009-2015, Michal Čihař <michal@cihar.com>
496
497
498
499
5001.42.0                           Oct 03, 2020              GAMMU-SMSD-MYSQL(7)