Subversion Repositories ALCASAR

Rev

Rev 2063 | Details | Compare with Previous | Last modification | View Log

Rev Author Line No. Line
1800 richard 1
-- 
2540 rexy 2
-- Database for Gammu SMSD
1800 richard 3
-- 
2540 rexy 4
-- In case you get errors about not supported charset, please
5
-- replace utf8mb4 with utf8.
1800 richard 6
 
2540 rexy 7
-- --------------------------------------------------------
1800 richard 8
 
9
-- 
10
-- Table structure for table `gammu`
11
-- 
12
 
13
CREATE TABLE `gammu` (
2540 rexy 14
  `Version` INTEGER NOT NULL DEFAULT '0' PRIMARY KEY
15
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
1800 richard 16
 
17
-- 
18
-- Dumping data for table `gammu`
19
-- 
20
 
2540 rexy 21
INSERT INTO `gammu` (`Version`) VALUES (17);
1800 richard 22
 
2540 rexy 23
-- --------------------------------------------------------
24
 
1800 richard 25
-- 
26
-- Table structure for table `inbox`
27
-- 
28
 
29
CREATE TABLE `inbox` (
30
  `UpdatedInDB` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
31
  `ReceivingDateTime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
32
  `Text` text NOT NULL,
33
  `SenderNumber` VARCHAR(20) NOT NULL DEFAULT '',
34
  `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL DEFAULT 'Default_No_Compression',
35
  `UDH` text NOT NULL,
36
  `SMSCNumber` VARCHAR(20) NOT NULL DEFAULT '',
37
  `Class` INTEGER NOT NULL DEFAULT '-1',
2540 rexy 38
  `TextDecoded` text NOT NULL,
1800 richard 39
  `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
40
  `RecipientID` text NOT NULL,
41
  `Processed` enum('false','true') NOT NULL DEFAULT 'false',
2540 rexy 42
  `Status` INTEGER NOT NULL DEFAULT '-1',
1800 richard 43
  PRIMARY KEY `ID` (`ID`)
2540 rexy 44
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;
1800 richard 45
 
2540 rexy 46
-- --------------------------------------------------------
47
 
1800 richard 48
-- 
49
-- Table structure for table `outbox`
50
-- 
51
 
52
CREATE TABLE `outbox` (
53
  `UpdatedInDB` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
54
  `InsertIntoDB` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
55
  `SendingDateTime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
56
  `SendBefore` TIME NOT NULL DEFAULT '23:59:59',
57
  `SendAfter` TIME NOT NULL DEFAULT '00:00:00',
58
  `Text` text,
59
  `DestinationNumber` VARCHAR(20) NOT NULL DEFAULT '',
60
  `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL DEFAULT 'Default_No_Compression',
61
  `UDH` text,
62
  `Class` INTEGER DEFAULT '-1',
2540 rexy 63
  `TextDecoded` text NOT NULL,
1800 richard 64
  `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
65
  `MultiPart` enum('false','true') DEFAULT 'false',
66
  `RelativeValidity` INTEGER DEFAULT '-1',
67
  `SenderID` VARCHAR(255),
68
  `SendingTimeOut` TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00',
69
  `DeliveryReport` enum('default','yes','no') DEFAULT 'default',
70
  `CreatorID` text NOT NULL,
2540 rexy 71
  `Retries` INT(3) DEFAULT 0,
72
  `Priority` INTEGER DEFAULT 0,
73
  `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error','Reserved') NOT NULL DEFAULT 'Reserved',
74
  `StatusCode` INTEGER NOT NULL DEFAULT '-1',
1800 richard 75
  PRIMARY KEY `ID` (`ID`)
2540 rexy 76
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
1800 richard 77
 
78
CREATE INDEX outbox_date ON outbox(SendingDateTime, SendingTimeOut);
2540 rexy 79
CREATE INDEX outbox_sender ON outbox(SenderID(250));
1800 richard 80
 
2540 rexy 81
-- --------------------------------------------------------
82
 
1800 richard 83
-- 
84
-- Table structure for table `outbox_multipart`
85
-- 
86
 
87
CREATE TABLE `outbox_multipart` (
88
  `Text` text,
89
  `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL DEFAULT 'Default_No_Compression',
90
  `UDH` text,
91
  `Class` INTEGER DEFAULT '-1',
2540 rexy 92
  `TextDecoded` text,
1800 richard 93
  `ID` INTEGER UNSIGNED NOT NULL DEFAULT '0',
94
  `SequencePosition` INTEGER NOT NULL DEFAULT '1',
2540 rexy 95
  `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error','Reserved') NOT NULL DEFAULT 'Reserved',
96
  `StatusCode` INTEGER NOT NULL DEFAULT '-1',
1800 richard 97
  PRIMARY KEY (`ID`, `SequencePosition`)
2540 rexy 98
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
1800 richard 99
 
2540 rexy 100
-- --------------------------------------------------------
1800 richard 101
 
102
-- 
103
-- Table structure for table `phones`
104
-- 
105
 
106
CREATE TABLE `phones` (
107
  `ID` text NOT NULL,
108
  `UpdatedInDB` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
109
  `InsertIntoDB` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
110
  `TimeOut` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
111
  `Send` enum('yes','no') NOT NULL DEFAULT 'no',
112
  `Receive` enum('yes','no') NOT NULL DEFAULT 'no',
113
  `IMEI` VARCHAR(35) NOT NULL,
2540 rexy 114
  `IMSI` VARCHAR(35) NOT NULL,
1800 richard 115
  `NetCode` VARCHAR(10) DEFAULT 'ERROR',
116
  `NetName` VARCHAR(35) DEFAULT 'ERROR',
117
  `Client` text NOT NULL,
118
  `Battery` INTEGER NOT NULL DEFAULT -1,
119
  `Signal` INTEGER NOT NULL DEFAULT -1,
120
  `Sent` INT NOT NULL DEFAULT 0,
121
  `Received` INT NOT NULL DEFAULT 0,
122
  PRIMARY KEY (`IMEI`)
2540 rexy 123
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
1800 richard 124
 
2540 rexy 125
-- --------------------------------------------------------
126
 
1800 richard 127
-- 
128
-- Table structure for table `sentitems`
129
-- 
130
 
131
CREATE TABLE `sentitems` (
132
  `UpdatedInDB` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
133
  `InsertIntoDB` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
134
  `SendingDateTime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
135
  `DeliveryDateTime` TIMESTAMP NULL,
136
  `Text` text NOT NULL,
137
  `DestinationNumber` VARCHAR(20) NOT NULL DEFAULT '',
138
  `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL DEFAULT 'Default_No_Compression',
139
  `UDH` text NOT NULL,
140
  `SMSCNumber` VARCHAR(20) NOT NULL DEFAULT '',
141
  `Class` INTEGER NOT NULL DEFAULT '-1',
2540 rexy 142
  `TextDecoded` text NOT NULL,
1800 richard 143
  `ID` INTEGER UNSIGNED NOT NULL DEFAULT '0',
144
  `SenderID` VARCHAR(255) NOT NULL,
145
  `SequencePosition` INTEGER NOT NULL DEFAULT '1',
146
  `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error') NOT NULL DEFAULT 'SendingOK',
147
  `StatusError` INTEGER NOT NULL DEFAULT '-1',
148
  `TPMR` INTEGER NOT NULL DEFAULT '-1',
149
  `RelativeValidity` INTEGER NOT NULL DEFAULT '-1',
150
  `CreatorID` text NOT NULL,
2540 rexy 151
  `StatusCode` INTEGER NOT NULL DEFAULT '-1',
1800 richard 152
  PRIMARY KEY (`ID`, `SequencePosition`)
2540 rexy 153
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
1800 richard 154
 
155
CREATE INDEX sentitems_date ON sentitems(DeliveryDateTime);
156
CREATE INDEX sentitems_tpmr ON sentitems(TPMR);
157
CREATE INDEX sentitems_dest ON sentitems(DestinationNumber);
2540 rexy 158
CREATE INDEX sentitems_sender ON sentitems(SenderID(250));
1800 richard 159
 
160
-- 
161
-- Triggers for setting default timestamps
162
-- 
163
 
164
DELIMITER //
165
 
166
CREATE TRIGGER inbox_timestamp BEFORE INSERT ON inbox
167
FOR EACH ROW
168
BEGIN
169
    IF NEW.ReceivingDateTime = '0000-00-00 00:00:00' THEN
170
        SET NEW.ReceivingDateTime = CURRENT_TIMESTAMP();
171
    END IF;
172
END;//
173
 
174
CREATE TRIGGER outbox_timestamp BEFORE INSERT ON outbox
175
FOR EACH ROW
176
BEGIN
177
    IF NEW.InsertIntoDB = '0000-00-00 00:00:00' THEN
178
        SET NEW.InsertIntoDB = CURRENT_TIMESTAMP();
179
    END IF;
180
    IF NEW.SendingDateTime = '0000-00-00 00:00:00' THEN
181
        SET NEW.SendingDateTime = CURRENT_TIMESTAMP();
182
    END IF;
183
    IF NEW.SendingTimeOut = '0000-00-00 00:00:00' THEN
184
        SET NEW.SendingTimeOut = CURRENT_TIMESTAMP();
185
    END IF;
186
END;//
187
 
188
CREATE TRIGGER phones_timestamp BEFORE INSERT ON phones
189
FOR EACH ROW
190
BEGIN
191
    IF NEW.InsertIntoDB = '0000-00-00 00:00:00' THEN
192
        SET NEW.InsertIntoDB = CURRENT_TIMESTAMP();
193
    END IF;
194
    IF NEW.TimeOut = '0000-00-00 00:00:00' THEN
195
        SET NEW.TimeOut = CURRENT_TIMESTAMP();
196
    END IF;
197
END;//
198
 
199
CREATE TRIGGER sentitems_timestamp BEFORE INSERT ON sentitems
200
FOR EACH ROW
201
BEGIN
202
    IF NEW.InsertIntoDB = '0000-00-00 00:00:00' THEN
203
        SET NEW.InsertIntoDB = CURRENT_TIMESTAMP();
204
    END IF;
205
    IF NEW.SendingDateTime = '0000-00-00 00:00:00' THEN
206
        SET NEW.SendingDateTime = CURRENT_TIMESTAMP();
207
    END IF;
208
END;//
209
 
210
DELIMITER ;
211
 
212
 
213
----------------------------------------
214
-----   ALCASAR specific table  --------
215
----------------------------------------
216
--
217
-- Table structure for table `SMS_ban_perm`
218
--
219
 
220
CREATE TABLE IF NOT EXISTS `SMS_ban_perm` (
221
  `SenderNumber` VARCHAR(20) CHARACTER SET utf8 NOT NULL,
222
  `Expiration` VARCHAR(255) CHARACTER SET utf8 NOT NULL,
223
  `Perm` INT(11) NOT NULL,
224
  `date_add` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
225
  PRIMARY KEY (`SenderNumber`)
226
) ENGINE=MyISAM DEFAULT CHARSET=utf32;
227
 
228
--
229
-- Table structure for table `SMS_ban_temp`
230
--
231
 
232
CREATE TABLE IF NOT EXISTS `SMS_ban_temp` (
233
  `ID` INT(11) NOT NULL AUTO_INCREMENT,
234
  `SenderNumber` VARCHAR(20) NOT NULL,
235
  PRIMARY KEY (`ID`)
236
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
237
 
238
--
239
-- Table structure for table `SMS_country`
240
--
241
 
242
CREATE TABLE IF NOT EXISTS `SMS_country` (
2063 raphael.pi 243
  `name` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
244
  `id` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
1800 richard 245
  `status` INT(1) NOT NULL DEFAULT '0',
246
  PRIMARY KEY (`name`)
247
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
248
 
249
--
250
-- Dumping data for table `SMS_country`
251
--
252
 
253
INSERT INTO `SMS_country` (`name`, `id`, `status`) VALUES
254
('Afghanistan', '+93', 0),
255
('Afrique du Sud', '+27', 0),
256
('Albanie', '+355', 0),
257
('Algerie', '+213', 0),
258
('Allemagne', '+49', 0),
259
('Andorre', '+376', 0),
260
('Angleterre', '+44', 0),
261
('Angola', '+244', 0),
262
('Anguilla', '+1264', 0),
263
('Antigua et Barbuda', '+1268', 0),
264
('Arabie Saoudite', '+966', 0),
265
('Argentine', '+54', 0),
266
('Armenie', '+374', 0),
267
('Aruba', '+297', 0),
268
('Australie', '+61', 0),
269
('Autriche', '+43', 0),
270
('Azerbaidjan', '+994', 0),
271
('Bahamas', '+1242', 0),
272
('Bahrein', '+973', 0),
273
('Bangladesh', '+880', 0),
274
('Barbade', '+1246', 0),
275
('Belgique', '+32', 0),
276
('Benin', '+229', 0),
277
('Bermudes', '+1441', 0),
278
('Bhoutan', '+975', 0),
279
('Bielorussie', '+375', 0),
280
('Birmanie', '+95', 0),
281
('Bolivie', '+591', 0),
282
('Bonaire', '+599', 0),
283
('Bosnie', '+387', 0),
284
('Botswana', '+267', 0),
285
('Bresil', '+55', 0),
286
('Brunei', '+673', 0),
287
('Bulgarie', '+359', 0),
288
('Burkina Faso', '+226', 0),
289
('Burundi', '+257', 0),
290
('Cambodge', '+855', 0),
291
('Cameroun', '+237', 0),
292
('Canada', '+1', 0),
293
('Cap Vert', '+238', 0),
294
('Chili', '+56', 0),
295
('Chine', '+86', 0),
296
('Chypre', '+357', 0),
297
('Colombie', '+57', 0),
298
('Comores', '+269', 0),
299
('Congo RDC', '+243', 0),
300
('Congo RPC', '+242', 0),
301
('Coree du Nord', '+850', 0),
302
('Coree du Sud', '+82', 0),
303
('Costa Rica 1', '+501', 0),
304
('Costa Rica 2', '+506', 0),
305
('Cote d Ivoire', '+225', 0),
306
('Croatie', '+385', 0),
307
('Cuba', '+53', 0),
308
('Curacao', '+599', 0),
309
('Danemark', '+45', 0),
310
('Djibouti', '+253', 0),
311
('Dominique', '+1767', 0),
312
('Egypte', '+20', 0),
313
('El Salvador', '+503', 0),
314
('Emirats Arabes Unis', '+971', 0),
315
('Equateur', '+593', 0),
316
('Erythree', '+291', 0),
317
('Espagne', '+34', 0),
318
('Estonie', '+372', 0),
319
('Etats-Unis', '+1', 0),
320
('Ethiopie', '+251', 0),
321
('Falklands Malouines', '+500', 0),
322
('Finlande', '+358', 0),
323
('France', '+33', 1),
324
('Gabon', '+241', 0),
325
('Gambie', '+220', 0),
326
('Georgie', '+995', 0),
327
('Ghana', '+233', 0),
328
('Gibraltar', '+350', 0),
329
('Grece', '+30', 0),
330
('Grenade', '+1473', 0),
331
('Groenland', '+299', 0),
332
('Guadeloupe', '+590', 0),
333
('Guatemala', '+502', 0),
334
('Guernesey', '+441481', 0),
335
('Guinee', '+224', 0),
336
('Guinee Bissau', '+245', 0),
337
('Guinee Equatoriale', '+240', 0),
338
('Guyana', '+5921', 0),
339
('Guyane France', '+594', 0),
340
('Haiti', '+509', 0),
341
('Herm', '+441481', 0),
342
('Honduras', '+504', 0),
343
('Hong Kong', '+852', 0),
344
('Hongrie', '+36', 0),
345
('Ile de Ascension', '+247', 0),
346
('Ile Maurice', '+230', 0),
347
('Iles Cayman', '+1345', 0),
348
('Iles Christmas', '+61', 0),
349
('Iles Coco', '+61', 0),
350
('Iles Cook', '+682', 0),
351
('Iles Fidji', '+679', 0),
352
('Iles Mariannes', '+1670', 0),
353
('Iles Marshall', '+692', 0),
354
('Iles Salomon', '+677', 0),
355
('Iles Vierges GB', '+1284', 0),
356
('Iles Vierges USA', '+1340', 0),
357
('Inde', '+91', 0),
358
('Indonesie', '+62', 0),
359
('Irak', '+964', 0),
360
('Iran', '+98', 0),
361
('Irlande', '+353', 0),
362
('Islande', '+354', 0),
363
('Israel', '+972', 0),
364
('Italie', '+39', 0),
365
('Jamaique', '+1876', 0),
366
('Japon', '+81', 0),
367
('Jersey', '+441534', 0),
368
('Jordanie', '+962', 0),
369
('Kazakhstan', '+7', 0),
370
('Kenya', '+254', 0),
371
('Kirghizistan', '+996', 0),
372
('Kiribati', '+686', 0),
373
('Koweit', '+965', 0),
374
('Laos', '+856', 0),
375
('Lesotho', '+266', 0),
376
('Lettonie', '+371', 0),
377
('Liban', '+961', 0),
378
('Libye', '+218', 0),
379
('Liechtenstein', '+423', 0),
380
('Lituanie', '+370', 0),
381
('Luxembourg', '+352', 0),
382
('Macao', '+853', 0),
383
('Macedoine', '+389', 0),
384
('Madagascar', '+261', 0),
385
('Malaisie', '+60', 0),
386
('Malawi', '+265', 0),
387
('Maldives', '+960', 0),
388
('Mali', '+223', 0),
389
('Malte', '+356', 0),
390
('Maroc', '+212', 0),
391
('Martinique', '+596', 0),
392
('Mauritanie', '+222', 0),
393
('Mayotte', '+269', 0),
394
('Mexique', '+52', 0),
395
('Micronesie', '+691', 0),
396
('Moldavie', '+373', 0),
397
('Monaco', '+377', 0),
398
('Mongolie', '+9761', 0),
399
('Montserrat', '+1664', 0),
400
('Mozambique', '+258', 0),
401
('Namibie', '+264', 0),
402
('Nauru', '+674', 0),
403
('Nepal', '+977', 0),
404
('Nicaragua', '+505', 0),
405
('Niger', '+227', 0),
406
('Nigeria', '+2349', 0),
407
('Norvege', '+47', 0),
408
('Nouvelle Caledonie', '+687', 0),
409
('Nouvelle Zelande', '+64', 0),
410
('Oman', '+968', 0),
411
('Ouganda', '+2560', 0),
412
('Ouzbekistan', '+998', 0),
413
('Pakistan', '+92', 0),
414
('Palau', '+680', 0),
415
('Palestine', '+970', 0),
416
('Panama', '+507', 0),
417
('Papouasie Nouvelle Guinee', '+675', 0),
418
('Paraguay', '+595', 0),
419
('Pays-Bas', '+31', 0),
420
('Perou', '+51', 0),
421
('Philippines', '+63', 0),
422
('Pologne', '+48', 0),
423
('Polynesie France', '+689', 0),
424
('Porto Rico', '+1787', 0),
425
('Portugal', '+351', 0),
426
('Qatar', '+974', 0),
427
('Republique Centrafricaine', '+236', 0),
428
('Republique Dominicaine', '+1809', 0),
429
('Republique Tcheque', '+420', 0),
430
('Reunion', '+262', 0),
431
('Roumanie', '+40', 0),
432
('Royaume Uni', '+44', 0),
433
('Russie', '+7', 0),
434
('Rwanda', '+250', 0),
435
('Saint Marin (Rep.)', '+378', 0),
436
('Sainte Lucie', '+1758', 0),
437
('Samoa occidental', '+685', 0),
438
('Sao Tome et Principe', '+239', 0),
439
('Senegal', '+221', 0),
440
('Serbie', '+381', 0),
441
('Sercq', '+441481', 0),
442
('Seychelles', '+248', 0),
443
('Sierra Leone', '+232', 0),
444
('Singapour', '+65', 0),
445
('Slovaquie', '+421', 0),
446
('Slovenie', '+386', 0),
447
('Somalie', '+252', 0),
448
('Soudan', '+249', 0),
449
('Sri Lanka', '+94', 0),
450
('St Kitts', '+1869', 0),
451
('St Pierre et Miquelon', '+508', 0),
452
('St Vincent et les Grenadines', '+1784', 0),
453
('Suede', '+46', 0),
454
('Suisse', '+41', 0),
455
('Surinam', '+597', 0),
456
('Swaziland', '+268', 0),
457
('Syrie', '+963', 0),
458
('Tadjikistan', '+992', 0),
459
('Taiwan', '+886', 0),
460
('Tanzanie', '+255', 0),
461
('Tchad', '+235', 0),
462
('Thailande', '+661', 0),
463
('Togo', '+228', 0),
464
('Tonga', '+676', 0),
465
('Trinidad et Tobago', '+1868', 0),
466
('Tunisie', '+216', 0),
467
('Turkmenistan', '+993', 0),
468
('Turks et Caicos', '+1649', 0),
469
('Turquie', '+90', 0),
470
('Tuvalu', '+688', 0),
471
('Ukraine', '+380', 0),
472
('Uruguay', '+598', 0),
473
('Vanuatu', '+678', 0),
474
('Vatican', '+379', 0),
475
('Venezuela', '+58', 0),
476
('Vietnam', '+84', 0),
477
('Wallis et Futuna', '+681', 0),
478
('Yemen', '+967', 0),
479
('Zambie', '+260', 0),
480
('Zimbabwe', '+263', 0),
481
('FILTRAGE', 'fr', 0);
482