Subversion Repositories ALCASAR

Rev

Rev 2540 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log

Rev 2540 Rev 3327
Line 1... Line 1...
1 
--
1 
--
2 
-- Database for Gammu SMSD
 2 
-- Database for Gammu SMSD
3 
--
3 
--
4 
-- In case you get errors about not supported charset, please
 4 
-- In case you get errors about not supported charset, please
5 
-- replace utf8mb4 with utf8.
 5 
-- replace utf8mb4 with utf8.
6 
 
 6 
 
7 
-- --------------------------------------------------------
 7 
-- --------------------------------------------------------
8 
 
 8 
 
9 
--
9 
--
10 
-- Table structure for table `gammu`
 10 
-- Table structure for table `gammu`
11 
--
11 
--
12 
 
 12 
 
13 
CREATE TABLE `gammu` (
 13 
CREATE TABLE `gammu` (
14 
  `Version` integer NOT NULL default '0' PRIMARY KEY
 14 
  `Version` integer NOT NULL default 0 PRIMARY KEY
15 
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
 15 
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
16 
 
 16 
 
17 
--
17 
--
18 
-- Dumping data for table `gammu`
 18 
-- Dumping data for table `gammu`
19 
--
19 
--
20 
 
 20 
 
21 
INSERT INTO `gammu` (`Version`) VALUES (17);
 21 
INSERT INTO `gammu` (`Version`) VALUES (17);
22 
 
 22 
 
23 
-- --------------------------------------------------------
 23 
-- --------------------------------------------------------
24 
 
 24 
 
25 
--
25 
--
26 
-- Table structure for table `inbox`
 26 
-- Table structure for table `inbox`
27 
--
27 
--
28 
 
 28 
 
29 
CREATE TABLE `inbox` (
 29 
CREATE TABLE `inbox` (
30 
  `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 30 
  `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
31 
  `ReceivingDateTime` timestamp NOT NULL default '0000-00-00 00:00:00',
 31 
  `ReceivingDateTime` timestamp NOT NULL default CURRENT_TIMESTAMP,
32 
  `Text` text NOT NULL,
 32 
  `Text` text NOT NULL,
33 
  `SenderNumber` varchar(20) NOT NULL default '',
 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',
 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,
 35 
  `UDH` text NOT NULL,
36 
  `SMSCNumber` varchar(20) NOT NULL default '',
 36 
  `SMSCNumber` varchar(20) NOT NULL default '',
37 
  `Class` integer NOT NULL default '-1',
 37 
  `Class` integer NOT NULL default -1,
38 
  `TextDecoded` text NOT NULL,
 38 
  `TextDecoded` text NOT NULL,
39 
  `ID` integer unsigned NOT NULL auto_increment,
 39 
  `ID` integer unsigned NOT NULL auto_increment,
40 
  `RecipientID` text NOT NULL,
 40 
  `RecipientID` text NOT NULL,
41 
  `Processed` enum('false','true') NOT NULL default 'false',
 41 
  `Processed` enum('false','true') NOT NULL default 'false',
42 
  `Status` integer NOT NULL default '-1',
 42 
  `Status` integer NOT NULL default -1,
43 
  PRIMARY KEY `ID` (`ID`)
 43 
  PRIMARY KEY `ID` (`ID`)
44 
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;
 44 
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;
45 
 
 45 
 
- 
 
 46 
--
- 
 
 47 
-- Dumping data for table `inbox`
- 
 
 48 
--
- 
 
 49 
 
- 
 
 50 
 
46 
-- --------------------------------------------------------
 51 
-- --------------------------------------------------------
47 
 
 52 
 
48 
--
53 
--
49 
-- Table structure for table `outbox`
 54 
-- Table structure for table `outbox`
50 
--
55 
--
51 
 
 56 
 
52 
CREATE TABLE `outbox` (
 57 
CREATE TABLE `outbox` (
53 
  `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 58 
  `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
54 
  `InsertIntoDB` timestamp NOT NULL default '0000-00-00 00:00:00',
 59 
  `InsertIntoDB` timestamp NOT NULL default CURRENT_TIMESTAMP,
55 
  `SendingDateTime` timestamp NOT NULL default '0000-00-00 00:00:00',
 60 
  `SendingDateTime` timestamp NOT NULL default CURRENT_TIMESTAMP,
56 
  `SendBefore` time NOT NULL DEFAULT '23:59:59',
 61 
  `SendBefore` time NOT NULL DEFAULT '23:59:59',
57 
  `SendAfter` time NOT NULL DEFAULT '00:00:00',
 62 
  `SendAfter` time NOT NULL DEFAULT '00:00:00',
58 
  `Text` text,
 63 
  `Text` text,
59 
  `DestinationNumber` varchar(20) NOT NULL default '',
 64 
  `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',
 65 
  `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
61 
  `UDH` text,
 66 
  `UDH` text,
62 
  `Class` integer default '-1',
 67 
  `Class` integer default -1,
63 
  `TextDecoded` text NOT NULL,
 68 
  `TextDecoded` text NOT NULL,
64 
  `ID` integer unsigned NOT NULL auto_increment,
 69 
  `ID` integer unsigned NOT NULL auto_increment,
65 
  `MultiPart` enum('false','true') default 'false',
 70 
  `MultiPart` enum('false','true') default 'false',
66 
  `RelativeValidity` integer default '-1',
 71 
  `RelativeValidity` integer default -1,
67 
  `SenderID` varchar(255),
 72 
  `SenderID` varchar(255),
68 
  `SendingTimeOut` timestamp NULL default '0000-00-00 00:00:00',
 73 
  `SendingTimeOut` timestamp NULL default CURRENT_TIMESTAMP,
69 
  `DeliveryReport` enum('default','yes','no') default 'default',
 74 
  `DeliveryReport` enum('default','yes','no') default 'default',
70 
  `CreatorID` text NOT NULL,
 75 
  `CreatorID` text NOT NULL,
71 
  `Retries` int(3) default 0,
 76 
  `Retries` int(3) default 0,
72 
  `Priority` integer default 0,
 77 
  `Priority` integer default 0,
73 
  `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error','Reserved') NOT NULL default 'Reserved',
 78 
  `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error','Reserved') NOT NULL default 'Reserved',
74 
  `StatusCode` integer NOT NULL default '-1',
 79 
  `StatusCode` integer NOT NULL default -1,
75 
  PRIMARY KEY `ID` (`ID`)
 80 
  PRIMARY KEY `ID` (`ID`)
76 
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
 81 
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
77 
 
 82 
 
78 
CREATE INDEX outbox_date ON outbox(SendingDateTime, SendingTimeOut);
 83 
CREATE INDEX outbox_date ON outbox(SendingDateTime, SendingTimeOut);
79 
CREATE INDEX outbox_sender ON outbox(SenderID(250));
 84 
CREATE INDEX outbox_sender ON outbox(SenderID(250));
80 
 
 85 
 
- 
 
 86 
--
- 
 
 87 
-- Dumping data for table `outbox`
- 
 
 88 
--
- 
 
 89 
 
- 
 
 90 
 
81 
-- --------------------------------------------------------
 91 
-- --------------------------------------------------------
82 
 
 92 
 
83 
--
93 
--
84 
-- Table structure for table `outbox_multipart`
 94 
-- Table structure for table `outbox_multipart`
85 
--
95 
--
86 
 
 96 
 
87 
CREATE TABLE `outbox_multipart` (
 97 
CREATE TABLE `outbox_multipart` (
88 
  `Text` text,
 98 
  `Text` text,
89 
  `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
 99 
  `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
90 
  `UDH` text,
 100 
  `UDH` text,
91 
  `Class` integer default '-1',
 101 
  `Class` integer default -1,
92 
  `TextDecoded` text,
 102 
  `TextDecoded` text,
93 
  `ID` integer unsigned NOT NULL default '0',
 103 
  `ID` integer unsigned NOT NULL default 0,
94 
  `SequencePosition` integer NOT NULL default '1',
 104 
  `SequencePosition` integer NOT NULL default 1,
95 
  `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error','Reserved') NOT NULL default 'Reserved',
 105 
  `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error','Reserved') NOT NULL default 'Reserved',
96 
  `StatusCode` integer NOT NULL default '-1',
 106 
  `StatusCode` integer NOT NULL default -1,
97 
  PRIMARY KEY (`ID`, `SequencePosition`)
 107 
  PRIMARY KEY (`ID`, `SequencePosition`)
98 
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
 108 
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
99 
 
 109 
 
- 
 
 110 
--
- 
 
 111 
-- Dumping data for table `outbox_multipart`
- 
 
 112 
--
- 
 
 113 
 
100 
-- --------------------------------------------------------
 114 
-- --------------------------------------------------------
101 
 
 115 
 
102 
--
116 
--
103 
-- Table structure for table `phones`
 117 
-- Table structure for table `phones`
104 
--
118 
--
105 
 
 119 
 
106 
CREATE TABLE `phones` (
 120 
CREATE TABLE `phones` (
107 
  `ID` text NOT NULL,
 121 
  `ID` text NOT NULL,
108 
  `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 122 
  `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
109 
  `InsertIntoDB` timestamp NOT NULL default '0000-00-00 00:00:00',
 123 
  `InsertIntoDB` timestamp NOT NULL default CURRENT_TIMESTAMP,
110 
  `TimeOut` timestamp NOT NULL default '0000-00-00 00:00:00',
 124 
  `TimeOut` timestamp NOT NULL default CURRENT_TIMESTAMP,
111 
  `Send` enum('yes','no') NOT NULL default 'no',
 125 
  `Send` enum('yes','no') NOT NULL default 'no',
112 
  `Receive` enum('yes','no') NOT NULL default 'no',
 126 
  `Receive` enum('yes','no') NOT NULL default 'no',
113 
  `IMEI` varchar(35) NOT NULL,
 127 
  `IMEI` varchar(35) NOT NULL,
114 
  `IMSI` varchar(35) NOT NULL,
 128 
  `IMSI` varchar(35) NOT NULL,
115 
  `NetCode` varchar(10) default 'ERROR',
 129 
  `NetCode` varchar(10) default 'ERROR',
Line 120... Line 134...
120 
  `Sent` int NOT NULL DEFAULT 0,
 134 
  `Sent` int NOT NULL DEFAULT 0,
121 
  `Received` int NOT NULL DEFAULT 0,
 135 
  `Received` int NOT NULL DEFAULT 0,
122 
  PRIMARY KEY (`IMEI`)
 136 
  PRIMARY KEY (`IMEI`)
123 
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
 137 
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
124 
 
 138 
 
- 
 
 139 
--
- 
 
 140 
-- Dumping data for table `phones`
- 
 
 141 
--
- 
 
 142 
 
125 
-- --------------------------------------------------------
 143 
-- --------------------------------------------------------
126 
 
 144 
 
127 
--
145 
--
128 
-- Table structure for table `sentitems`
 146 
-- Table structure for table `sentitems`
129 
--
147 
--
130 
 
 148 
 
131 
CREATE TABLE `sentitems` (
 149 
CREATE TABLE `sentitems` (
132 
  `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 150 
  `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
133 
  `InsertIntoDB` timestamp NOT NULL default '0000-00-00 00:00:00',
 151 
  `InsertIntoDB` timestamp NOT NULL default CURRENT_TIMESTAMP,
134 
  `SendingDateTime` timestamp NOT NULL default '0000-00-00 00:00:00',
 152 
  `SendingDateTime` timestamp NOT NULL default CURRENT_TIMESTAMP,
135 
  `DeliveryDateTime` timestamp NULL,
 153 
  `DeliveryDateTime` timestamp NULL,
136 
  `Text` text NOT NULL,
 154 
  `Text` text NOT NULL,
137 
  `DestinationNumber` varchar(20) NOT NULL default '',
 155 
  `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',
 156 
  `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
139 
  `UDH` text NOT NULL,
 157 
  `UDH` text NOT NULL,
140 
  `SMSCNumber` varchar(20) NOT NULL default '',
 158 
  `SMSCNumber` varchar(20) NOT NULL default '',
141 
  `Class` integer NOT NULL default '-1',
 159 
  `Class` integer NOT NULL default -1,
142 
  `TextDecoded` text NOT NULL,
 160 
  `TextDecoded` text NOT NULL,
143 
  `ID` integer unsigned NOT NULL default '0',
 161 
  `ID` integer unsigned NOT NULL default 0,
144 
  `SenderID` varchar(255) NOT NULL,
 162 
  `SenderID` varchar(255) NOT NULL,
145 
  `SequencePosition` integer NOT NULL default '1',
 163 
  `SequencePosition` integer NOT NULL default 1,
146 
  `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error') NOT NULL default 'SendingOK',
 164 
  `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error') NOT NULL default 'SendingOK',
147 
  `StatusError` integer NOT NULL default '-1',
 165 
  `StatusError` integer NOT NULL default -1,
148 
  `TPMR` integer NOT NULL default '-1',
 166 
  `TPMR` integer NOT NULL default -1,
149 
  `RelativeValidity` integer NOT NULL default '-1',
 167 
  `RelativeValidity` integer NOT NULL default -1,
150 
  `CreatorID` text NOT NULL,
 168 
  `CreatorID` text NOT NULL,
151 
  `StatusCode` integer NOT NULL default '-1',
 169 
  `StatusCode` integer NOT NULL default -1,
152 
  PRIMARY KEY (`ID`, `SequencePosition`)
 170 
  PRIMARY KEY (`ID`, `SequencePosition`)
153 
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
 171 
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
154 
 
 172 
 
155 
CREATE INDEX sentitems_date ON sentitems(DeliveryDateTime);
 173 
CREATE INDEX sentitems_date ON sentitems(DeliveryDateTime);
156 
CREATE INDEX sentitems_tpmr ON sentitems(TPMR);
 174 
CREATE INDEX sentitems_tpmr ON sentitems(TPMR);
157 
CREATE INDEX sentitems_dest ON sentitems(DestinationNumber);
 175 
CREATE INDEX sentitems_dest ON sentitems(DestinationNumber);
158 
CREATE INDEX sentitems_sender ON sentitems(SenderID(250));
 176 
CREATE INDEX sentitems_sender ON sentitems(SenderID(250));
159 
 
 177 
 
160 
--
178 
--
161 
-- Triggers for setting default timestamps
 179 
-- Dumping data for table `sentitems`
162 
--
180 
--
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 
 
 181 
 
212 
 
 182 
 
213 
----------------------------------------
 183 
----------------------------------------
214 
-----   ALCASAR specific table  --------
 184 
-----   ALCASAR specific table  --------
215 
----------------------------------------
 185 
----------------------------------------