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
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
|
<!DOCTYPE Book PUBLIC "-//OASIS//DTD DocBook V4.1//EN"[
<!ENTITY freedoc-license SYSTEM "../../doc/include/copying-fdl.sgml">
]>
<!-- $Id: dbd_sqlite3.sgml,v 1.9 2008/07/28 21:07:49 mhoenicka Exp $ -->
<book>
<bookinfo>
<title>sqlite3 driver manual</title>
<subtitle>A libdbi driver using the SQLite3 embedded database engine</subtitle>
<author>
<firstname>Markus</firstname>
<surname>Hoenicka</surname>
<affiliation>
<address><email>mhoenicka@users.sourceforge.net</email></address>
</affiliation>
</author>
<copyright>
<year>2005-2008</year>
<holder>Markus Hoenicka</holder>
</copyright>
<legalnotice>
<para>
Permission is granted to copy, distribute and/or modify this
document under the terms of the <link linkend="copying-FDL">
<citetitle>GNU Free Documentation License</citetitle></link>,
Version 1.1 or any later version published by the Free Software
Foundation; with no Invariant Sections, with no Front-Cover Texts,
and with no Back-Cover Texts. A copy of the license is included in
<xref linkend="copying-FDL">.
</para>
</legalnotice>
<revhistory>
<revision>
<revnumber>$Revision: 1.9 $</revnumber>
<date>$Date: 2008/07/28 21:07:49 $</date>
</revision>
</revhistory>
</bookinfo>
<preface>
<title>Preface</title>
<para><ulink url="http://libdbi.sourceforge.net">libdbi</ulink> is a database abstraction layer written in C. It implements a framework that can utilize separate driver libraries for specific database servers. The <ulink url="http://libdbi-drivers.sourceforge.net">libdbi-drivers</ulink> project provides the drivers necessary to talk to the supported database servers.</para>
<para>This manual provides information about the sqlite3 driver. The manual is intended for programmers who write applications linked against libdbi and who want their applications to work with the sqlite3 driver.</para>
<para>Questions and comments about the sqlite3 driver should be sent to the <ulink url="mailto:libdbi-drivers-devel@lists.sourceforge.net">libdbi-drivers-devel</ulink> mailing list. Visit the <ulink url="http://lists.sourceforge.net/lists/listinfo/libdbi-drivers-devel">libdbi-drivers-devel list page</ulink> to subscribe and for further information. Questions and comments about the libdbi library should be sent to the appropriate libdbi mailing list.</para>
<para>The sqlite3 driver is maintained by <ulink url="mailto:mhoenicka@users.sourceforge.net">Markus Hoenicka</ulink>.</para>
</preface>
<chapter>
<title>Introduction</title>
<para><ulink url="http://www.sqlite.org">SQLite</ulink> is a smart library that implements an embeddable SQL database engine. No need for an external database server - an application linked against libsqlite can do it all by itself. Of course there are a few limitations of this approach compared to "real" SQL database servers, mostly for massively parallel high-throughput database applications, but on the other hand, installation and administration are a breeze.</para>
<para>SQLite3 is a redesign of SQLite which is incompatible with the older 2.x versions. As the API functions and the library itself were renamed, SQLite3 also requires a slightly modified libdbi driver. You can have both the sqlite and the sqlite3 driver installed on your system, but you have to make sure that you access your databases with the driver that matches the database version. The easiest way to accomplish this is to use different database directories for each version.</para>
<para>Your application should support the sqlite3 driver if one of the following applies:</para>
<itemizedlist>
<listitem>
<para>You want to support potential users of your application who don't have the skills to administer a database server.</para>
</listitem>
<listitem>
<para>You want to offer the simplest possible installation of your application.</para>
</listitem>
<listitem>
<para>You want to let users test-drive your application without the need to fiddle with their production database servers.</para>
</listitem>
</itemizedlist>
</chapter>
<chapter>
<title>Installation</title>
<para>This chapter describes the prerequisites and the procedures to build and install the sqlite3 driver from the sources.</para>
<sect1>
<title>Prerequisites</title>
<para>The following packages have to be installed on your system:</para>
<variablelist>
<varlistentry>
<term>libdbi</term>
<listitem>
<para>This library provides the framework of the database abstraction layer which can utilize the sqlite3 driver to perform database operations. The download page as well as the mailing lists with bug reports and patches are accessible at <ulink url="http://sourceforge.net/projects/libdbi">sourceforge.net/projects/libdbi</ulink>.</para>
</listitem>
</varlistentry>
<varlistentry>
<term>sqlite3</term>
<listitem>
<para>This library implements the embedded database engine. Find the most recent release at <ulink url="http://www.sqlite.org">www.sqlite.org</ulink>. The current version of the sqlite driver was tested with SQLite3 version 3.0.8 and should work ok with later releases.</para>
</listitem>
</varlistentry>
</variablelist>
</sect1>
<sect1>
<title>Build and install the sqlite3 driver</title>
<para>First you have to unpack the libdbi-drivers archive in a suitable directory. Unpacking will create a new subdirectory <filename class="directory">libdbi-drivers-X.Y</filename> where "X.Y" denotes the version:</para>
<screen><prompt>$ </prompt><userinput>tar -xzf libdbi-drivers-0.7.2.tar.gz</userinput></screen>
<para>The libdbi-drivers project consists of several drivers that use a common build system. Therefore you <emphasis>must</emphasis> tell configure explicitly that you want to build the sqlite3 driver (you can list as many drivers as you want to build):</para>
<screen><prompt>$ </prompt><userinput>cd libdbi-drivers</userinput></screen>
<screen><prompt>$ </prompt><userinput>./configure --with-sqlite3</userinput></screen>
<para>Run <command>./configure --help</command> to find out about additional options.</para>
<para>Then build the driver with the command:</para>
<screen><prompt>$ </prompt><userinput>make</userinput></screen>
<note>
<para>Please note that you may have to invoke <command>gmake</command>, the GNU version of make, on some systems.</para>
</note>
<para>Then install the driver with the command (you'll need root permissions to do this):</para>
<screen><prompt>$ </prompt><userinput>make install</userinput></screen>
<para>To test the operation of the newly installed driver, use the command:</para>
<screen><prompt>$ </prompt><userinput>make check</userinput></screen>
<para>This command creates and runs a test program that performs a few basic input and output tests. The program will ask for a database name. This can be any name that is a valid filename on your system. It will also ask for a data directory. This is the directory that is used to create the test database. Needless to say that you need write access to that directory. If you accept the default ".", the database will be created in the <filename class="directory">tests</filename> subdirectory.</para>
</sect1>
</chapter>
<chapter>
<title>Driver options</title>
<para>Your application has to initialize libdbi drivers by setting some driver options with the <function>dbi_conn_set_option()</function> and the <function>dbi_conn_set_option_numeric()</function> library functions. The sqlite driver supports the following options:</para>
<variablelist>
<varlistentry>
<term>dbname</term>
<listitem>
<para>The name of the database you want to work with. As a SQLite database corresponds to one file in your filesystem, <varname>dbname</varname> can be any legal filename. If the database/file doesn't exist when you first try to access it, SQLite will create it on the fly.</para>
<para>It is important to understand that the full path of the database is composed of <varname>sqlite3_dbdir</varname> and <varname>dbname</varname>. Therefore <varname>dbname</varname> should not contain the full path of a file, but just the name.</para>
</listitem>
</varlistentry>
<varlistentry>
<term>sqlite3_dbdir</term>
<listitem>
<para>This is the directory that contains all sqlite databases. Use the full path please. If this option is not specified, a compile-time default directory will be used. By default, this directory (usually <filename class="directory" moreinfo="none">/usr/local/var/lib/libdbi/sqlite3</filename>) has read and write permissions for everyone with the sticky bit set. If this is not suitable for your application, use a custom directory with your preferred permissions instead.</para>
<note>
<para>It is necessary to keep all sqlite databases in one directory to make it possible to list all existing databases through the libdbi API. However, you are free to open more than one connection simultaneously, each one using a different setting of <parameter moreinfo="none">sqlite3_dbdir</parameter>.</para>
</note>
</listitem>
</varlistentry>
<varlistentry>
<term>timeout (numeric)</term>
<listitem>
<para>The connection timeout in seconds.</para>
</listitem>
</varlistentry>
<varlistentry>
<term>sqlite3_timeout (numeric)</term>
<listitem>
<para>The design of SQLite3 does not allow fully concurrent access by two clients. However, if the timeout is larger than zero, the second client will wait for the given amount of time for the first client to release its lock, if necessary. If the timeout is set to zero, the second client will return immediately, indicating a busy status. The numerical value of this option specifies the timeout in milliseconds.</para>
<note>
<para>This option is deprecated. Use the generic option <option>timeout</option> instead. In the current implementation, <option>sqlite3_timeout</option> overrides <option>timeout</option> if both are set. Please be aware that these options use different time scales.</para>
</note>
</listitem>
</varlistentry>
</variablelist>
</chapter>
<chapter>
<title>Peculiarities you should know about</title>
<para>This chapter lists known peculiarities of the sqlite3 driver. This includes SQLite3 features that differ from what you know from the other database servers supported by libdbi, and it includes features and misfeatures introduced by the sqlite3 driver. It is the intention of the driver author to reduce the number of misfeatures in future releases if possible.</para>
<sect1>
<title>SQLite3 (mis)features</title>
<para>As the SQLite3 package is constantly being improved, you should refer to the original documentation about the SQL features it <ulink url="http://www.sqlite.org/lang.html">supports</ulink> and about the SQL features it <ulink url="http://www.sqlite.org/omitted.html">doesn't support</ulink>.</para>
<para>One noticeable difference between SQLite3 and other SQL database engines is that the former uses something called "manifest typing". The practical difference to the typeless nature of SQLite 2.x is negligible as it still does not support the rich typing used by most other SQL database engines. In order to satisfy the needs of the strongly typed retrieval functions of libdbi, the only way out is to declare the column types in a <command>CREATE TABLE</command> statement just as you would with any other SQL database engine. As an example, the following statement is perfectly fine with SQLite3, but not with the sqlite3 driver:</para>
<screen><userinput>CREATE TABLE foo (a,b,c)</userinput></screen>
<para>However, the following statement is fine with SQLite3, the sqlite3 driver, and just about any other SQL database engine out there:</para>
<screen><userinput>CREATE TABLE foo (a INTEGER,b TEXT,c VARCHAR(64))</userinput></screen>
<para>The following table lists the column types which are positively recognized by the sqlite driver. Essentially all column types supported by MySQL and PostgreSQL are supported by this driver as well, making it reasonably easy to write portable SQL code. All other column types are treated as strings.</para>
<table>
<title>SQL column types supported by the sqlite driver</title>
<tgroup cols="2">
<thead>
<row>
<entry>type</entry>
<entry>description</entry>
</row>
</thead>
<tbody>
<row>
<entry>TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, BYTEA</entry>
<entry>String types of unlimited length. Binary data must be safely encoded, see text.</entry>
</row>
<row>
<entry>CHAR(), VARCHAR(), TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT</entry>
<entry>String types of unlimited length. There is no chopping or padding performed by the database engine.</entry>
</row>
<row>
<entry>ENUM</entry>
<entry>String type of unlimited length. In contrast to MySQL, choosing ENUM over VARCHAR does not save any storage space.</entry>
</row>
<row>
<entry>SET</entry>
<entry>String type of unlimited length. In contrast to MySQL, the input is not checked against the list of allowed values.</entry>
</row>
<row>
<entry>YEAR</entry>
<entry>String type of unlimited length. MySQL stores 2 or 4 digit years as a 1 byte value, whereas the SQLite drivers stores the string as provided.</entry>
</row>
<row>
<entry>TINYINT, INT1, CHAR</entry>
<entry>A 1 byte type used to store one character, a signed integer between -128 and 127, or an unsigned integer between 0 and 255.</entry>
</row>
<row>
<entry>SMALLINT, INT2</entry>
<entry>2 byte (short) integer type used to store a signed integer between -32768 and 32767 or an unsigned integer between 0 and 65535.</entry>
</row>
<row>
<entry>MEDIUMINT</entry>
<entry>3 byte integer type used to store a signed integer between -8388608 and 8388607 or an unsigned integer between 0 and 16777215.</entry>
</row>
<row>
<entry>INT, INTEGER, INT4</entry>
<entry>4 byte (long) integer type used to store a signed integer between -2147483648 and 2147483647 or an unsigned integer between 0 and 4294967295.</entry>
</row>
<row>
<entry>BIGINT, INT8, INTEGER PRIMARY KEY</entry>
<entry>8 byte (long long) integer type used to store a signed integer between -9223372036854775808 and 9223372036854775807 or an unsigned integer between 0 and 18446744073709551615. See below for a discussion of INTEGER PRIMARY KEY.</entry>
</row>
<row>
<entry>DECIMAL, NUMERIC</entry>
<entry>A string type of unlimited length used to store floating-point numbers of arbitrary precision.</entry>
</row>
<row>
<entry>TIMESTAMP, DATETIME</entry>
<entry>A string type of unlimited length used to store date/time combinations. The required format is 'YYYY-MM-DD HH:MM:SS', anything following this pattern is ignored.</entry>
</row>
<row>
<entry>DATE</entry>
<entry>A string type of unlimited length used to store a date. The required format is 'YYYY-MM-DD', anything following this pattern is ignored.</entry>
</row>
<row>
<entry>TIME</entry>
<entry>A string type of unlimited length used to store a time. The required format is 'HH:MM:SS', anything following this pattern is ignored.</entry>
</row>
<row>
<entry>FLOAT, FLOAT4, REAL</entry>
<entry>A 4 byte floating-point number. The range is -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. Please note that MySQL treats REAL as an 8 byte instead of a 4 byte float like PostgreSQL.</entry>
</row>
<row>
<entry>DOUBLE, DOUBLE PRECISION, FLOAT8</entry>
<entry>An 8 byte floating-point number. The range is -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308.</entry>
</row>
</tbody>
</tgroup>
</table>
<para>Another difference is the lack of access control on the database engine level. Most SQL database servers implement some mechanisms to restrict who is allowed to fiddle with the databases and who is not. As SQLite3 uses regular files to store its databases, all available access control is on the filesystem level. There is no SQL interface to this kind of access control, but <command>chmod</command> and <command>chown</command> are your friends.</para>
</sect1>
<sect1>
<title>sqlite driver misfeatures</title>
<para>And now we have to discuss how successful the sqlite driver is in squeezing the SQLite idea of a database engine into the libdbi framework which was shaped after MySQL and PostgreSQL. Keep in mind that the limitations mentioned here are not intrinsic (except maybe the first one which is beyond our control), that is a sufficient amount of coding might fix these problems eventually.</para>
<itemizedlist>
<listitem>
<para>SQLite3 handles auto-increment columns in a fairly non-intuitive way. Only the type INTEGER PRIMARY KEY auto-increments. As a user of other database engine you might expect the row IDs to be 4-byte integers (they were in 2.x), but nope: they are in fact 8-byte integers, and therefore equivalent to INT8 or BIGINT of other engines. This leaves us with the odd "feature" of the sqlite3 driver that INTEGER is a 4-byte integer, whereas INTEGER PRIMARY KEY is a 8-bit integer type. If this were not the case, auto-incrementing columns would be artificially limited to the range of 4-byte integers. On the other hand this means that you cannot declare a real 4-byte integer auto-incrementing column.</para>
<warning>
<para>Do not forget to use <function moreinfo="none">dbi_result_get_longlong()</function> or <function moreinfo="none">dbi_result_get_ulonglong</function> to retrieve values from columns declared as INTEGER PRIMARY KEY.</para>
</warning>
</listitem>
<listitem>
<para>The (essentially) typeless nature of SQLite has some nasty consequences. The sqlite driver takes great care to reconstruct the type of a field that you request in a query, but this isn't always successful. To help the driver get things right, please stick to the following rules:</para>
<itemizedlist>
<listitem>
<para>When using a function as a result column [e.g. count(*)], the opening bracket <emphasis>must</emphasis> hug the function name as shown. The function call <emphasis>must</emphasis> also be aliased.</para>
</listitem>
<listitem>
<para>When using an expression as a result column [e.g. (count(*) + 6)], the entire expression <emphasis>must</emphasis> be enclosed in brackets as shown and <emphasis>must</emphasis> also be aliased.</para>
</listitem>
</itemizedlist>
</listitem>
<listitem>
<para>The sqlite driver currently assumes that the directory separator of your filesystem is a slash (/). This may be wrong on your particular system. It is not a problem for Windows systems as long as the sqlite driver is built with the Cygwin tools (see <filename>README.win32</filename>).</para>
</listitem>
<listitem>
<para>Listing tables with the <function>dbi_conn_get_table_list()</function> libdbi function currently returns only permanent tables. Temporary tables are ignored.</para>
</listitem>
<listitem>
<para>The sqlite driver assumes that table and field names do not exceed 128 characters in length, including the trailing \0. I don't know whether SQLite internally has such a limit or not (both MySQL and PostgreSQL have a lower limit). The limit can be increased by changing a single #define in the <filename moreinfo="none">dbd_sqlite.h</filename> header file.</para>
</listitem>
<listitem>
<para>In a few cases, the sqlite driver expects you to type SQL keywords in all lowercase or all uppercase, but not mixed. This holds true for the 'from' in a SELECT statement. Type it either as 'from' or as 'FROM', but refrain from using 'fRoM' or other funny mixtures of uppercase and lowercase. Most other database engines treat the keywords as case-insensitive and would accept all variants.</para>
</listitem>
</itemizedlist>
</sect1>
</chapter>
&freedoc-license;
</book>
|
