1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
|
.TH "GAMMU-SMSD-DBI" "7" "February 25, 2011" "1.29.91" "Gammu"
.SH NAME
gammu-smsd-dbi \- gammu-smsd(1) backend using DBI abstraction layer to use any supported database as a message storage
.
.nr rst2man-indent-level 0
.
.de1 rstReportMargin
\\$1 \\n[an-margin]
level \\n[rst2man-indent-level]
level margin: \\n[rst2man-indent\\n[rst2man-indent-level]]
-
\\n[rst2man-indent0]
\\n[rst2man-indent1]
\\n[rst2man-indent2]
..
.de1 INDENT
.\" .rstReportMargin pre:
. RS \\$1
. nr rst2man-indent\\n[rst2man-indent-level] \\n[an-margin]
. nr rst2man-indent-level +1
.\" .rstReportMargin post:
..
.de UNINDENT
. RE
.\" indent \\n[an-margin]
.\" old: \\n[rst2man-indent\\n[rst2man-indent-level]]
.nr rst2man-indent-level -1
.\" new: \\n[rst2man-indent\\n[rst2man-indent-level]]
.in \\n[rst2man-indent\\n[rst2man-indent-level]]u
..
.\" Man page generated from reStructeredText.
.
.sp
DBI backend stores all data in any database supported by \fI\%libdbi\fP, which
parameters are defined by configuration (see \fIgammu\-smsdrc\fP for description of
configuration options).
.sp
For tables description see \fIgammu\-smsd\-tables\fP.
.sp
This backend is based on \fIgammu\-smsd\-sql\fP.
.IP Note
.
The DBI driver is currently not supported on Windows because libdbi
library does not support this platform.
.RE
.SH SUPPORTED DRIVERS
.sp
For complete list of drivers for \fI\%libdbi\fP see \fI\%libdbi-drivers\fP project. The
drivers for example include:
.INDENT 0.0
.IP \(bu 2
.
\fBsqlite3\fP \- for SQLite 3
.IP \(bu 2
.
\fBmysql\fP \- for MySQL
.IP \(bu 2
.
\fBpgsql\fP \- for PostgeSQL
.IP \(bu 2
.
\fBfreetds\fP \- for MS SQL Server or Sybase
.UNINDENT
.SH EXAMPLE
.sp
SQL script for creating tables in SQLite database:
.sp
.nf
.ft C
CREATE TABLE daemons (
Start TEXT NOT NULL,
Info TEXT NOT NULL
);
CREATE TABLE gammu (
Version INTEGER NOT NULL DEFAULT \(aq0\(aq
);
INSERT INTO gammu (Version) VALUES (13);
CREATE TABLE inbox (
UpdatedInDB NUMERIC NOT NULL DEFAULT (datetime(\(aqnow\(aq)),
ReceivingDateTime NUMERIC NOT NULL DEFAULT (datetime(\(aqnow\(aq)),
Text TEXT NOT NULL,
SenderNumber TEXT NOT NULL DEFAULT \(aq\(aq,
Coding TEXT NOT NULL DEFAULT \(aqDefault_No_Compression\(aq,
UDH TEXT NOT NULL,
SMSCNumber TEXT NOT NULL DEFAULT \(aq\(aq,
Class INTEGER NOT NULL DEFAULT \(aq\-1\(aq,
TextDecoded TEXT NOT NULL DEFAULT \(aq\(aq,
ID INTEGER PRIMARY KEY AUTOINCREMENT,
RecipientID TEXT NOT NULL,
Processed TEXT NOT NULL DEFAULT \(aqfalse\(aq,
CHECK (Coding IN
(\(aqDefault_No_Compression\(aq,\(aqUnicode_No_Compression\(aq,\(aq8bit\(aq,\(aqDefault_Compression\(aq,\(aqUnicode_Compression\(aq))
);
CREATE TRIGGER update_inbox_time UPDATE ON inbox
BEGIN
UPDATE inbox SET UpdatedInDB = datetime(\(aqnow\(aq) WHERE ID = old.ID;
END;
CREATE TABLE outbox (
UpdatedInDB NUMERIC NOT NULL DEFAULT (datetime(\(aqnow\(aq)),
InsertIntoDB NUMERIC NOT NULL DEFAULT (datetime(\(aqnow\(aq)),
SendingDateTime NUMERIC NOT NULL DEFAULT (datetime(\(aqnow\(aq)),
SendBefore time NOT NULL DEFAULT \(aq23:59:59\(aq,
SendAfter time NOT NULL DEFAULT \(aq00:00:00\(aq,
Text TEXT,
DestinationNumber TEXT NOT NULL DEFAULT \(aq\(aq,
Coding TEXT NOT NULL DEFAULT \(aqDefault_No_Compression\(aq,
UDH TEXT,
Class INTEGER DEFAULT \(aq\-1\(aq,
TextDecoded TEXT NOT NULL DEFAULT \(aq\(aq,
ID INTEGER PRIMARY KEY AUTOINCREMENT,
MultiPart TEXT NOT NULL DEFAULT \(aqfalse\(aq,
RelativeValidity INTEGER DEFAULT \(aq\-1\(aq,
SenderID TEXT,
SendingTimeOut NUMERIC NOT NULL DEFAULT (datetime(\(aqnow\(aq)),
DeliveryReport TEXT DEFAULT \(aqdefault\(aq,
CreatorID TEXT NOT NULL,
CHECK (Coding IN
(\(aqDefault_No_Compression\(aq,\(aqUnicode_No_Compression\(aq,\(aq8bit\(aq,\(aqDefault_Compression\(aq,\(aqUnicode_Compression\(aq)),
CHECK (DeliveryReport IN (\(aqdefault\(aq,\(aqyes\(aq,\(aqno\(aq))
);
CREATE INDEX outbox_date ON outbox(SendingDateTime, SendingTimeOut);
CREATE INDEX outbox_sender ON outbox(SenderID);
CREATE TRIGGER update_outbox_time UPDATE ON outbox
BEGIN
UPDATE outbox SET UpdatedInDB = datetime(\(aqnow\(aq) WHERE ID = old.ID;
END;
CREATE TABLE outbox_multipart (
Text TEXT,
Coding TEXT NOT NULL DEFAULT \(aqDefault_No_Compression\(aq,
UDH TEXT,
Class INTEGER DEFAULT \(aq\-1\(aq,
TextDecoded TEXT DEFAULT NULL,
ID INTEGER,
SequencePosition INTEGER NOT NULL DEFAULT \(aq1\(aq,
CHECK (Coding IN
(\(aqDefault_No_Compression\(aq,\(aqUnicode_No_Compression\(aq,\(aq8bit\(aq,\(aqDefault_Compression\(aq,\(aqUnicode_Compression\(aq)),
PRIMARY KEY (ID, SequencePosition)
);
CREATE TABLE pbk (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
GroupID INTEGER NOT NULL DEFAULT \(aq\-1\(aq,
Name TEXT NOT NULL,
Number TEXT NOT NULL
);
CREATE TABLE pbk_groups (
Name TEXT NOT NULL,
ID INTEGER PRIMARY KEY AUTOINCREMENT
);
CREATE TABLE phones (
ID TEXT NOT NULL,
UpdatedInDB NUMERIC NOT NULL DEFAULT (datetime(\(aqnow\(aq)),
InsertIntoDB NUMERIC NOT NULL DEFAULT (datetime(\(aqnow\(aq)),
TimeOut NUMERIC NOT NULL DEFAULT (datetime(\(aqnow\(aq)),
Send TEXT NOT NULL DEFAULT \(aqno\(aq,
Receive TEXT NOT NULL DEFAULT \(aqno\(aq,
IMEI TEXT PRIMARY KEY NOT NULL,
Client TEXT NOT NULL,
Battery INTEGER NOT NULL DEFAULT \-1,
SignalStrength INTEGER NOT NULL DEFAULT \-1,
Sent INTEGER NOT NULL DEFAULT 0,
Received INTEGER NOT NULL DEFAULT 0
);
CREATE TRIGGER update_phones_time UPDATE ON phones
BEGIN
UPDATE phones SET UpdatedInDB = datetime(\(aqnow\(aq) WHERE IMEI = old.IMEI;
END;
CREATE TABLE sentitems (
UpdatedInDB NUMERIC NOT NULL DEFAULT (datetime(\(aqnow\(aq)),
InsertIntoDB NUMERIC NOT NULL DEFAULT (datetime(\(aqnow\(aq)),
SendingDateTime NUMERIC NOT NULL DEFAULT (datetime(\(aqnow\(aq)),
DeliveryDateTime NUMERIC NULL,
Text TEXT NOT NULL,
DestinationNumber TEXT NOT NULL DEFAULT \(aq\(aq,
Coding TEXT NOT NULL DEFAULT \(aqDefault_No_Compression\(aq,
UDH TEXT NOT NULL,
SMSCNumber TEXT NOT NULL DEFAULT \(aq\(aq,
Class INTEGER NOT NULL DEFAULT \(aq\-1\(aq,
TextDecoded TEXT NOT NULL DEFAULT \(aq\(aq,
ID INTEGER,
SenderID TEXT NOT NULL,
SequencePosition INTEGER NOT NULL DEFAULT \(aq1\(aq,
Status TEXT NOT NULL DEFAULT \(aqSendingOK\(aq,
StatusError INTEGER NOT NULL DEFAULT \(aq\-1\(aq,
TPMR INTEGER NOT NULL DEFAULT \(aq\-1\(aq,
RelativeValidity INTEGER NOT NULL DEFAULT \(aq\-1\(aq,
CreatorID TEXT NOT NULL,
CHECK (Status IN
(\(aqSendingOK\(aq,\(aqSendingOKNoReport\(aq,\(aqSendingError\(aq,\(aqDeliveryOK\(aq,\(aqDeliveryFailed\(aq,\(aqDeliveryPending\(aq,
\(aqDeliveryUnknown\(aq,\(aqError\(aq)),
CHECK (Coding IN
(\(aqDefault_No_Compression\(aq,\(aqUnicode_No_Compression\(aq,\(aq8bit\(aq,\(aqDefault_Compression\(aq,\(aqUnicode_Compression\(aq)) ,
PRIMARY KEY (ID, SequencePosition)
);
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);
CREATE TRIGGER update_sentitems_time UPDATE ON sentitems
BEGIN
UPDATE sentitems SET UpdatedInDB = datetime(\(aqnow\(aq) WHERE ID = old.ID;
END;
.ft P
.fi
.IP Note
.
You can find the script in \fBdocs/sql/sqlite.sql\fP as well. There are
also scripts for other databases in same folder.
.RE
.SH AUTHOR
Michal Čihař <michal@cihar.com>
.SH COPYRIGHT
2009-2011, Michal Čihař <michal@cihar.com>
.\" Generated by docutils manpage writer.
.\"
.
|
