Subversion Repositories ALCASAR

Rev

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

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