Subversion Repositories ALCASAR

-- 

-- Database for Gammu SMSD

-- 

-- In case you get errors about not supported charset, please

-- replace utf8mb4 with utf8.

-- --------------------------------------------------------

-- 

-- Table structure for table `gammu`

-- 

CREATE TABLE `gammu` (

  `Version` integer NOT NULL default '0' PRIMARY KEY

) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- 

-- Dumping data for table `gammu`

-- 

INSERT INTO `gammu` (`Version`) VALUES (17);

-- --------------------------------------------------------

-- 

-- Table structure for table `inbox`

-- 

CREATE TABLE `inbox` (

  `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

  `ReceivingDateTime` timestamp NOT NULL default '0000-00-00 00:00:00',

  `Text` text NOT NULL,

  `SenderNumber` varchar(20) NOT NULL default '',

  `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',

  `UDH` text NOT NULL,

  `SMSCNumber` varchar(20) NOT NULL default '',

  `Class` integer NOT NULL default '-1',

  `TextDecoded` text NOT NULL,

  `ID` integer unsigned NOT NULL auto_increment,

  `RecipientID` text NOT NULL,

  `Processed` enum('false','true') NOT NULL default 'false',

  `Status` integer NOT NULL default '-1',

  PRIMARY KEY `ID` (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

-- 

-- Table structure for table `outbox`

-- 

CREATE TABLE `outbox` (

  `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

  `InsertIntoDB` timestamp NOT NULL default '0000-00-00 00:00:00',

  `SendingDateTime` timestamp NOT NULL default '0000-00-00 00:00:00',

  `SendBefore` time NOT NULL DEFAULT '23:59:59',

  `SendAfter` time NOT NULL DEFAULT '00:00:00',

  `Text` text,

  `DestinationNumber` varchar(20) NOT NULL default '',

  `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',

  `UDH` text,

  `Class` integer default '-1',

  `TextDecoded` text NOT NULL,

  `ID` integer unsigned NOT NULL auto_increment,

  `MultiPart` enum('false','true') default 'false',

  `RelativeValidity` integer default '-1',

  `SenderID` varchar(255),

  `SendingTimeOut` timestamp NULL default '0000-00-00 00:00:00',

  `DeliveryReport` enum('default','yes','no') default 'default',

  `CreatorID` text NOT NULL,

  `Retries` int(3) default 0,

  `Priority` integer default 0,

  `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error','Reserved') NOT NULL default 'Reserved',

  `StatusCode` integer NOT NULL default '-1',

  PRIMARY KEY `ID` (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE INDEX outbox_date ON outbox(SendingDateTime, SendingTimeOut);

CREATE INDEX outbox_sender ON outbox(SenderID(250));

-- --------------------------------------------------------

-- 

-- Table structure for table `outbox_multipart`

-- 

CREATE TABLE `outbox_multipart` (

  `Text` text,

  `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',

  `UDH` text,

  `Class` integer default '-1',

  `TextDecoded` text,

  `ID` integer unsigned NOT NULL default '0',

  `SequencePosition` integer NOT NULL default '1',

  `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error','Reserved') NOT NULL default 'Reserved',

  `StatusCode` integer NOT NULL default '-1',

  PRIMARY KEY (`ID`, `SequencePosition`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

-- 

-- Table structure for table `phones`

-- 

CREATE TABLE `phones` (

  `ID` text NOT NULL,

  `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

  `InsertIntoDB` timestamp NOT NULL default '0000-00-00 00:00:00',

  `TimeOut` timestamp NOT NULL default '0000-00-00 00:00:00',

  `Send` enum('yes','no') NOT NULL default 'no',

  `Receive` enum('yes','no') NOT NULL default 'no',

  `IMEI` varchar(35) NOT NULL,

  `IMSI` varchar(35) NOT NULL,

  `NetCode` varchar(10) default 'ERROR',

  `NetName` varchar(35) default 'ERROR',

  `Client` text NOT NULL,

  `Battery` integer NOT NULL DEFAULT -1,

  `Signal` integer NOT NULL DEFAULT -1,

  `Sent` int NOT NULL DEFAULT 0,

  `Received` int NOT NULL DEFAULT 0,

  PRIMARY KEY (`IMEI`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

-- 

-- Table structure for table `sentitems`

-- 

CREATE TABLE `sentitems` (

  `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

  `InsertIntoDB` timestamp NOT NULL default '0000-00-00 00:00:00',

  `SendingDateTime` timestamp NOT NULL default '0000-00-00 00:00:00',

  `DeliveryDateTime` timestamp NULL,

  `Text` text NOT NULL,

  `DestinationNumber` varchar(20) NOT NULL default '',

  `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',

  `UDH` text NOT NULL,

  `SMSCNumber` varchar(20) NOT NULL default '',

  `Class` integer NOT NULL default '-1',

  `TextDecoded` text NOT NULL,

  `ID` integer unsigned NOT NULL default '0',

  `SenderID` varchar(255) NOT NULL,

  `SequencePosition` integer NOT NULL default '1',

  `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error') NOT NULL default 'SendingOK',

  `StatusError` integer NOT NULL default '-1',

  `TPMR` integer NOT NULL default '-1',

  `RelativeValidity` integer NOT NULL default '-1',

  `CreatorID` text NOT NULL,

  `StatusCode` integer NOT NULL default '-1',

  PRIMARY KEY (`ID`, `SequencePosition`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE INDEX sentitems_date ON sentitems(DeliveryDateTime);

CREATE INDEX sentitems_tpmr ON sentitems(TPMR);

CREATE INDEX sentitems_dest ON sentitems(DestinationNumber);

CREATE INDEX sentitems_sender ON sentitems(SenderID(250));

-- 

-- Triggers for setting default timestamps

-- 

DELIMITER //

CREATE TRIGGER inbox_timestamp BEFORE INSERT ON inbox

FOR EACH ROW

BEGIN

    IF NEW.ReceivingDateTime = '0000-00-00 00:00:00' THEN

        SET NEW.ReceivingDateTime = CURRENT_TIMESTAMP();

    END IF;

END;//

CREATE TRIGGER outbox_timestamp BEFORE INSERT ON outbox

FOR EACH ROW

BEGIN

    IF NEW.InsertIntoDB = '0000-00-00 00:00:00' THEN

        SET NEW.InsertIntoDB = CURRENT_TIMESTAMP();

    END IF;

    IF NEW.SendingDateTime = '0000-00-00 00:00:00' THEN

        SET NEW.SendingDateTime = CURRENT_TIMESTAMP();

    END IF;

    IF NEW.SendingTimeOut = '0000-00-00 00:00:00' THEN

        SET NEW.SendingTimeOut = CURRENT_TIMESTAMP();

    END IF;

END;//

CREATE TRIGGER phones_timestamp BEFORE INSERT ON phones

FOR EACH ROW

BEGIN

    IF NEW.InsertIntoDB = '0000-00-00 00:00:00' THEN

        SET NEW.InsertIntoDB = CURRENT_TIMESTAMP();

    END IF;

    IF NEW.TimeOut = '0000-00-00 00:00:00' THEN

        SET NEW.TimeOut = CURRENT_TIMESTAMP();

    END IF;

END;//

CREATE TRIGGER sentitems_timestamp BEFORE INSERT ON sentitems

FOR EACH ROW

BEGIN

    IF NEW.InsertIntoDB = '0000-00-00 00:00:00' THEN

        SET NEW.InsertIntoDB = CURRENT_TIMESTAMP();

    END IF;

    IF NEW.SendingDateTime = '0000-00-00 00:00:00' THEN

        SET NEW.SendingDateTime = CURRENT_TIMESTAMP();

    END IF;

END;//

DELIMITER ;

----------------------------------------

-----   ALCASAR specific table  --------

----------------------------------------

--

-- Table structure for table `SMS_ban_perm`

--

CREATE TABLE IF NOT EXISTS `SMS_ban_perm` (

  `SenderNumber` varchar(20) CHARACTER SET utf8 NOT NULL,

  `Expiration` varchar(255) CHARACTER SET utf8 NOT NULL,

  `Perm` int(11) NOT NULL,

  `date_add` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`SenderNumber`)

) ENGINE=MyISAM DEFAULT CHARSET=utf32;

--

-- Table structure for table `SMS_ban_temp`

--

CREATE TABLE IF NOT EXISTS `SMS_ban_temp` (

  `ID` int(11) NOT NULL AUTO_INCREMENT,

  `SenderNumber` varchar(20) NOT NULL,

  PRIMARY KEY (`ID`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--

-- Table structure for table `SMS_country`

--

CREATE TABLE IF NOT EXISTS `SMS_country` (

  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  `id` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  `status` int(1) NOT NULL DEFAULT '0',

  PRIMARY KEY (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--

-- Dumping data for table `SMS_country`

--

INSERT INTO `SMS_country` (`name`, `id`, `status`) VALUES

('Afghanistan', '+93', 0),

('Afrique du Sud', '+27', 0),

('Albanie', '+355', 0),

('Algerie', '+213', 0),

('Allemagne', '+49', 0),

('Andorre', '+376', 0),

('Angleterre', '+44', 0),

('Angola', '+244', 0),

('Anguilla', '+1264', 0),

('Antigua et Barbuda', '+1268', 0),

('Arabie Saoudite', '+966', 0),

('Argentine', '+54', 0),

('Armenie', '+374', 0),

('Aruba', '+297', 0),

('Australie', '+61', 0),

('Autriche', '+43', 0),

('Azerbaidjan', '+994', 0),

('Bahamas', '+1242', 0),

('Bahrein', '+973', 0),

('Bangladesh', '+880', 0),

('Barbade', '+1246', 0),

('Belgique', '+32', 0),

('Benin', '+229', 0),

('Bermudes', '+1441', 0),

('Bhoutan', '+975', 0),

('Bielorussie', '+375', 0),

('Birmanie', '+95', 0),

('Bolivie', '+591', 0),

('Bonaire', '+599', 0),

('Bosnie', '+387', 0),

('Botswana', '+267', 0),

('Bresil', '+55', 0),

('Brunei', '+673', 0),

('Bulgarie', '+359', 0),

('Burkina Faso', '+226', 0),

('Burundi', '+257', 0),

('Cambodge', '+855', 0),

('Cameroun', '+237', 0),

('Canada', '+1', 0),

('Cap Vert', '+238', 0),

('Chili', '+56', 0),

('Chine', '+86', 0),

('Chypre', '+357', 0),

('Colombie', '+57', 0),

('Comores', '+269', 0),

('Congo RDC', '+243', 0),

('Congo RPC', '+242', 0),

('Coree du Nord', '+850', 0),

('Coree du Sud', '+82', 0),

('Costa Rica 1', '+501', 0),

('Costa Rica 2', '+506', 0),

('Cote d Ivoire', '+225', 0),

('Croatie', '+385', 0),

('Cuba', '+53', 0),

('Curacao', '+599', 0),

('Danemark', '+45', 0),

('Djibouti', '+253', 0),

('Dominique', '+1767', 0),

('Egypte', '+20', 0),

('El Salvador', '+503', 0),

('Emirats Arabes Unis', '+971', 0),

('Equateur', '+593', 0),

('Erythree', '+291', 0),

('Espagne', '+34', 0),

('Estonie', '+372', 0),

('Etats-Unis', '+1', 0),

('Ethiopie', '+251', 0),

('Falklands Malouines', '+500', 0),

('Finlande', '+358', 0),

('France', '+33', 1),

('Gabon', '+241', 0),

('Gambie', '+220', 0),

('Georgie', '+995', 0),

('Ghana', '+233', 0),

('Gibraltar', '+350', 0),

('Grece', '+30', 0),

('Grenade', '+1473', 0),

('Groenland', '+299', 0),

('Guadeloupe', '+590', 0),

('Guatemala', '+502', 0),

('Guernesey', '+441481', 0),

('Guinee', '+224', 0),

('Guinee Bissau', '+245', 0),

('Guinee Equatoriale', '+240', 0),

('Guyana', '+5921', 0),

('Guyane France', '+594', 0),

('Haiti', '+509', 0),

('Herm', '+441481', 0),

('Honduras', '+504', 0),

('Hong Kong', '+852', 0),

('Hongrie', '+36', 0),

('Ile de Ascension', '+247', 0),

('Ile Maurice', '+230', 0),

('Iles Cayman', '+1345', 0),

('Iles Christmas', '+61', 0),

('Iles Coco', '+61', 0),

('Iles Cook', '+682', 0),

('Iles Fidji', '+679', 0),

('Iles Mariannes', '+1670', 0),

('Iles Marshall', '+692', 0),

('Iles Salomon', '+677', 0),

('Iles Vierges GB', '+1284', 0),

('Iles Vierges USA', '+1340', 0),

('Inde', '+91', 0),

('Indonesie', '+62', 0),

('Irak', '+964', 0),

('Iran', '+98', 0),

('Irlande', '+353', 0),

('Islande', '+354', 0),

('Israel', '+972', 0),

('Italie', '+39', 0),

('Jamaique', '+1876', 0),

('Japon', '+81', 0),

('Jersey', '+441534', 0),

('Jordanie', '+962', 0),

('Kazakhstan', '+7', 0),

('Kenya', '+254', 0),

('Kirghizistan', '+996', 0),

('Kiribati', '+686', 0),

('Koweit', '+965', 0),

('Laos', '+856', 0),

('Lesotho', '+266', 0),

('Lettonie', '+371', 0),

('Liban', '+961', 0),

('Libye', '+218', 0),

('Liechtenstein', '+423', 0),

('Lituanie', '+370', 0),

('Luxembourg', '+352', 0),

('Macao', '+853', 0),

('Macedoine', '+389', 0),

('Madagascar', '+261', 0),

('Malaisie', '+60', 0),

('Malawi', '+265', 0),

('Maldives', '+960', 0),

('Mali', '+223', 0),

('Malte', '+356', 0),

('Maroc', '+212', 0),

('Martinique', '+596', 0),

('Mauritanie', '+222', 0),

('Mayotte', '+269', 0),

('Mexique', '+52', 0),

('Micronesie', '+691', 0),

('Moldavie', '+373', 0),

('Monaco', '+377', 0),

('Mongolie', '+9761', 0),

('Montserrat', '+1664', 0),

('Mozambique', '+258', 0),

('Namibie', '+264', 0),

('Nauru', '+674', 0),

('Nepal', '+977', 0),

('Nicaragua', '+505', 0),

('Niger', '+227', 0),

('Nigeria', '+2349', 0),

('Norvege', '+47', 0),

('Nouvelle Caledonie', '+687', 0),

('Nouvelle Zelande', '+64', 0),

('Oman', '+968', 0),

('Ouganda', '+2560', 0),

('Ouzbekistan', '+998', 0),

('Pakistan', '+92', 0),

('Palau', '+680', 0),

('Palestine', '+970', 0),

('Panama', '+507', 0),

('Papouasie Nouvelle Guinee', '+675', 0),

('Paraguay', '+595', 0),

('Pays-Bas', '+31', 0),

('Perou', '+51', 0),

('Philippines', '+63', 0),

('Pologne', '+48', 0),

('Polynesie France', '+689', 0),

('Porto Rico', '+1787', 0),

('Portugal', '+351', 0),

('Qatar', '+974', 0),

('Republique Centrafricaine', '+236', 0),

('Republique Dominicaine', '+1809', 0),

('Republique Tcheque', '+420', 0),

('Reunion', '+262', 0),

('Roumanie', '+40', 0),

('Royaume Uni', '+44', 0),

('Russie', '+7', 0),

('Rwanda', '+250', 0),

('Saint Marin (Rep.)', '+378', 0),

('Sainte Lucie', '+1758', 0),

('Samoa occidental', '+685', 0),

('Sao Tome et Principe', '+239', 0),

('Senegal', '+221', 0),

('Serbie', '+381', 0),

('Sercq', '+441481', 0),

('Seychelles', '+248', 0),

('Sierra Leone', '+232', 0),

('Singapour', '+65', 0),

('Slovaquie', '+421', 0),

('Slovenie', '+386', 0),

('Somalie', '+252', 0),

('Soudan', '+249', 0),

('Sri Lanka', '+94', 0),

('St Kitts', '+1869', 0),

('St Pierre et Miquelon', '+508', 0),

('St Vincent et les Grenadines', '+1784', 0),

('Suede', '+46', 0),

('Suisse', '+41', 0),

('Surinam', '+597', 0),

('Swaziland', '+268', 0),

('Syrie', '+963', 0),

('Tadjikistan', '+992', 0),

('Taiwan', '+886', 0),

('Tanzanie', '+255', 0),

('Tchad', '+235', 0),

('Thailande', '+661', 0),

('Togo', '+228', 0),

('Tonga', '+676', 0),

('Trinidad et Tobago', '+1868', 0),

('Tunisie', '+216', 0),

('Turkmenistan', '+993', 0),

('Turks et Caicos', '+1649', 0),

('Turquie', '+90', 0),

('Tuvalu', '+688', 0),

('Ukraine', '+380', 0),

('Uruguay', '+598', 0),

('Vanuatu', '+678', 0),

('Vatican', '+379', 0),

('Venezuela', '+58', 0),

('Vietnam', '+84', 0),

('Wallis et Futuna', '+681', 0),

('Yemen', '+967', 0),

('Zambie', '+260', 0),

('Zimbabwe', '+263', 0),

('FILTRAGE', 'fr', 0);