Subversion Repositories ALCASAR

Rev

Rev 2063 | Go to most recent revision | Details | Last modification | View Log

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