Overview
The SQLite engine works over a local file from the file
system. The entire database is kept in a single file and the
engine is responsible to maintain its internal structure and
consistency. The file is automatically opened for reading and/or
writing - if both are possible the both modes are enabled if the
file is on a read-only media the engine will report errors
whenever an attempt is made to change the database. The database
engine is contained in the COM DLL and requires no
configuration - supplying the database file name to the Open
method is everything it needs to know!
The textual data is kept internally UTF8 encoded and thus
there is no need of UNICODE to ANSI and reverse conversions -
all the texts are fully preserved and processed correctly. The
other parts of the application may require some character code
page tuning, but not the database interface - all the UNICODE
characters passed to and from the engine are fully preserved. This
means also that the data in the database file is binary the same
on all the platforms. For instance you can copy a database
created on a PC to a Pocket PC device and work with it without
need of any conversion.
A specific feature of SQLite is that it is type-less. This
can be perceived as a plus or as a minus depending on the
programming habits you have and the applications you are going
to build. However, most of the modern programming environments
can even benefit from this feature. In most cases the
applications need to take care for themselves to validate the
data entered into the database and even if the database engine
supports wise validation methods the interface requirements
often make them inconvenient. On the other hand applications
that may benefit of relaxed data typing cannot do that
effectively if the
database requires each column to be of the specified type.
SQLite is designed to deal smartly when the fields participate
in expressions and will always return appropriate results by
doing whatever conversions are required.
Data types and auto typing mode
SQLite COM object allows manual and Auto
Typing mode. By default it is manual but many developers
will want to use auto typing as it results in automatic
conversions of the numeric types to numeric variant types when
results are returned from a query. SQLite COM types
for more information. In autotyping mode you can configure the
type names that refer to numeric values as you prefer.
When writing data to the db (INSERT and UPDATE queries for
example) you can use the Sprintf
or SAPrintf
funtions from StringUtilities
component to construct queries with minimal efforts.
SQLite engine supports transactions (not nested)
without limit to the number of statements in them. This allows
you to perform safe database updates and commit the changes only
if everything is ok.
SQLite supports triggers on tables and views (with
some limitations - see Unsupported
SQL features). This allows you build databases that care for
themselves internally or/and create views that act as pseudo
tables and simplify certain frequent operations.
The extended specific SQL commands (Pragma-s)
allow you to control the engine behavior at run-time and also
allow you fetch detailed information about the database objects
and use it to construct utility code.
If used rationally SQLite engine is faster or equal to the
most of the other small-scale and embedded databases (such as MS
Jet, MySQL and so on).
More information about the engine itself you can find on the
official SQLite site.
Where are the recordsets? Do you really need them?
The fetched data is returned by SQLite COm as collection of rows
which are collection of named values (the field values) in turn
(see Execute). This is even
easier to work with as it allows direct indexing of each value
in each row by index or name. Thus it combines features similar
to what both GetRows and ADODB.Recordset will give you in ADO.
When you need to page the output (in an ASP page for example)
you can use the optional parameters of the Execute method to
restrict the result to the part you really need. When writing
data the recordsets are most often limited to one table only
which is just some more source code - to write down all these
rst("X1").Value = something etc. Many developers
prefer executing insert/update SQL statements directly as they
happen to require less code. What really recordset gives you is
the type conversions, but with the Sprintf
or SAPrintf
methods you can do this in even less code. Thus, in
conclusion, SQLite COM makes the work with the database closer
to the SQL language in contrast to ADO centered programming
which tries to hide at least part of the SQL used for the actual
operations performed over the DB. If this is good or bad depends
on the point of view, but in most cases SQLite COM will require
less code than equivalent ADO based solution. Added the fact
that it is embedded, zero-configuration and able to work from
scratch (without even COm registration) the weight of its good
sides becomes higher. |
