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