1CREATE TABLE gammu (
2  Version INTEGER NOT NULL DEFAULT '0' PRIMARY KEY
3);
4
5INSERT INTO gammu (Version) VALUES (17);
6
7CREATE TABLE inbox (
8  UpdatedInDB NUMERIC NOT NULL DEFAULT (datetime('now')),
9  ReceivingDateTime NUMERIC NOT NULL DEFAULT (datetime('now')),
10  Text TEXT NOT NULL,
11  SenderNumber TEXT NOT NULL DEFAULT '',
12  Coding TEXT NOT NULL DEFAULT 'Default_No_Compression',
13  UDH TEXT NOT NULL,
14  SMSCNumber TEXT NOT NULL DEFAULT '',
15  Class INTEGER NOT NULL DEFAULT '-1',
16  TextDecoded TEXT NOT NULL DEFAULT '',
17  ID INTEGER PRIMARY KEY AUTOINCREMENT,
18  RecipientID TEXT NOT NULL,
19  Processed TEXT NOT NULL DEFAULT 'false',
20  Status INTEGER NOT NULL DEFAULT '-1',
21  CHECK (Coding IN
22  ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression'))
23);
24
25CREATE TRIGGER update_inbox_time UPDATE ON inbox
26  BEGIN
27    UPDATE inbox SET UpdatedInDB = datetime('now') WHERE ID = old.ID;
28  END;
29
30CREATE TABLE outbox (
31  UpdatedInDB NUMERIC NOT NULL DEFAULT (datetime('now')),
32  InsertIntoDB NUMERIC NOT NULL DEFAULT (datetime('now')),
33  SendingDateTime NUMERIC NOT NULL DEFAULT (datetime('now')),
34  SendBefore time NOT NULL DEFAULT '23:59:59',
35  SendAfter time NOT NULL DEFAULT '00:00:00',
36  Text TEXT,
37  DestinationNumber TEXT NOT NULL DEFAULT '',
38  Coding TEXT NOT NULL DEFAULT 'Default_No_Compression',
39  UDH TEXT,
40  Class INTEGER DEFAULT '-1',
41  TextDecoded TEXT NOT NULL DEFAULT '',
42  ID INTEGER PRIMARY KEY AUTOINCREMENT,
43  MultiPart TEXT NOT NULL DEFAULT 'false',
44  RelativeValidity INTEGER DEFAULT '-1',
45  SenderID TEXT,
46  SendingTimeOut NUMERIC NOT NULL DEFAULT (datetime('now')),
47  DeliveryReport TEXT DEFAULT 'default',
48  CreatorID TEXT NOT NULL,
49  Retries INTEGER DEFAULT '0',
50  Priority INTEGER DEFAULT '0',
51  Status TEXT NOT NULL DEFAULT 'Reserved',
52  StatusCode INTEGER NOT NULL DEFAULT '-1',
53  CHECK (Coding IN
54  ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
55  CHECK (DeliveryReport IN ('default','yes','no')),
56  CHECK (Status IN
57  ('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
58  'DeliveryUnknown','Error','Reserved'))
59);
60
61CREATE INDEX outbox_date ON outbox(SendingDateTime, SendingTimeOut);
62CREATE INDEX outbox_sender ON outbox(SenderID);
63
64CREATE TRIGGER update_outbox_time UPDATE ON outbox
65  BEGIN
66    UPDATE outbox SET UpdatedInDB = datetime('now') WHERE ID = old.ID;
67  END;
68
69CREATE TABLE outbox_multipart (
70  Text TEXT,
71  Coding TEXT NOT NULL DEFAULT 'Default_No_Compression',
72  UDH TEXT,
73  Class INTEGER DEFAULT '-1',
74  TextDecoded TEXT DEFAULT NULL,
75  ID INTEGER,
76  SequencePosition INTEGER NOT NULL DEFAULT '1',
77  Status TEXT NOT NULL DEFAULT 'Reserved',
78  StatusCode INTEGER NOT NULL DEFAULT '-1',
79  CHECK (Coding IN
80  ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
81  CHECK (Status IN
82  ('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
83  'DeliveryUnknown','Error','Reserved')),
84 PRIMARY KEY (ID, SequencePosition)
85);
86
87CREATE TABLE phones (
88  ID TEXT NOT NULL,
89  UpdatedInDB NUMERIC NOT NULL DEFAULT (datetime('now')),
90  InsertIntoDB NUMERIC NOT NULL DEFAULT (datetime('now')),
91  TimeOut NUMERIC NOT NULL DEFAULT (datetime('now')),
92  Send TEXT NOT NULL DEFAULT 'no',
93  Receive TEXT NOT NULL DEFAULT 'no',
94  IMEI TEXT PRIMARY KEY NOT NULL,
95  IMSI TEXT NOT NULL,
96  NetCode TEXT DEFAULT 'ERROR',
97  NetName TEXT DEFAULT 'ERROR',
98  Client TEXT NOT NULL,
99  Battery INTEGER NOT NULL DEFAULT -1,
100  Signal INTEGER NOT NULL DEFAULT -1,
101  Sent INTEGER NOT NULL DEFAULT 0,
102  Received INTEGER NOT NULL DEFAULT 0
103);
104
105CREATE TRIGGER update_phones_time UPDATE ON phones
106  BEGIN
107    UPDATE phones SET UpdatedInDB = datetime('now') WHERE IMEI = old.IMEI;
108  END;
109
110CREATE TABLE sentitems (
111  UpdatedInDB NUMERIC NOT NULL DEFAULT (datetime('now')),
112  InsertIntoDB NUMERIC NOT NULL DEFAULT (datetime('now')),
113  SendingDateTime NUMERIC NOT NULL DEFAULT (datetime('now')),
114  DeliveryDateTime NUMERIC NULL,
115  Text TEXT NOT NULL,
116  DestinationNumber TEXT NOT NULL DEFAULT '',
117  Coding TEXT NOT NULL DEFAULT 'Default_No_Compression',
118  UDH TEXT NOT NULL,
119  SMSCNumber TEXT NOT NULL DEFAULT '',
120  Class INTEGER NOT NULL DEFAULT '-1',
121  TextDecoded TEXT NOT NULL DEFAULT '',
122  ID INTEGER,
123  SenderID TEXT NOT NULL,
124  SequencePosition INTEGER NOT NULL DEFAULT '1',
125  Status TEXT NOT NULL DEFAULT 'SendingOK',
126  StatusError INTEGER NOT NULL DEFAULT '-1',
127  TPMR INTEGER NOT NULL DEFAULT '-1',
128  RelativeValidity INTEGER NOT NULL DEFAULT '-1',
129  CreatorID TEXT NOT NULL,
130  StatusCode INTEGER NOT NULL DEFAULT '-1',
131  CHECK (Status IN
132  ('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
133  'DeliveryUnknown','Error')),
134  CHECK (Coding IN
135  ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')) ,
136  PRIMARY KEY (ID, SequencePosition)
137);
138
139CREATE INDEX sentitems_date ON sentitems(DeliveryDateTime);
140CREATE INDEX sentitems_tpmr ON sentitems(TPMR);
141CREATE INDEX sentitems_dest ON sentitems(DestinationNumber);
142CREATE INDEX sentitems_sender ON sentitems(SenderID);
143
144CREATE TRIGGER update_sentitems_time UPDATE ON sentitems
145  BEGIN
146    UPDATE sentitems SET UpdatedInDB = datetime('now') WHERE ID = old.ID;
147  END;
148