Perl Cd Bookshelf [Electronic resources] نسخه متنی

14.18.1. Problem

An SQLite database lives in a single file, specified with the
dbname parameter in the DBI constructor. Unlike
most relational databases, there's no database server
here—DBD::SQLite interacts directly with the file. Multiple
processes can read from the same database file at the same time (with
SELECTs), but only one process can make changes (and other processes
are prevented from reading while those changes are being made).

SQLite supports transactions. That is, you can make a number of
changes to different tables, but the updates won't be written to the
file until you commit them:

use DBI;
$dbh = DBI->connect("dbi:SQLite:dbname=/Users/gnat/salaries.sqlt", ", ",
{ RaiseError => 1, AutoCommit => 0 });
eval {
$dbh->do("INSERT INTO people VALUES (29, 'Nat', 1973)");
$dbh->do("INSERT INTO people VALUES (30, 'William', 1999)");
$dbh->do("INSERT INTO father_of VALUES (29, 30)");
$dbh->commit( );
};
if ($@) {
eval { $dbh->rollback( ) };
die "Couldn't roll back transaction" if $@;
}

SQLite is a typeless database system. Regardless of the types
specified when you created a table, you can put any type (strings,
numbers, dates, blobs) into any field. Indeed, you can even create a
table without specifying any types:

CREATE TABLE people (id, name, birth_year);

The only time that data typing comes into play is when comparisons
occur, either through WHERE clauses or when the database has to sort
values. The database ignores the type of the column and looks only at
the type of the specific value being compared. Like Perl, SQLite
recognizes only strings and numbers. Two numbers are compared as
floating-point values, two strings are compared as strings, and a
number is always less than a string when values of two different
types are compared.

There is only one case when SQLite looks at the type you declare for
a column. To get an automatically incrementing column, such as unique
identifiers, specify a field of type "INTEGER PRIMARY KEY":

CREATE TABLE people (id INTEGER PRIMARY KEY, name, birth_year);

SQLite can hold 8-bit text data, but can't hold an ASCII NUL
character (\0). The only workaround is to do your
own encoding (for example, URL encoding or Base64) before you store
and after you retrieve the data. This is true even of columns
declared as BLOBs.