SQLite | Part 3

SQLite | Part 3

SQLite – PRIMARY KEY

The primary key constraint will create a UNIQUE constraint on the column or columns in question, however, and contrary to the standard, the PRIMARY KEY SQLite allows entries with NULL. It is a breach of the standard, and this gap could be addressed in future versions. It is therefore advisable to add the NOT NULL constraint to the declaration of a primary key.

ROWID and AUTOINCREMENT

Each row of a table is identified by a 64-bit signed integer called ROWID. When a table is declared with a single column and a INTEGER PRIMARY KEY, this column is an alias for the ROWID. The use of an alias identifier ROWID can increase the speed of research, they can be up to two times faster than a normal primary key associated with its index of uniqueness.

When the table is empty, the algorithm assigns a value to the identifier that increments for each new record, reaching the limit of a 64-bit signed integer (263-1 = 9223372036854775807). Once this limit is reached, it will reuse the space freed by deleted records. The assignment of identifiers is no longer incremental but uncertain.

It is possible to use the keyword AUTOINCREMENT. This slightly changes the algorithm: once the integer limit is reached, it will not be possible to insert a new record. This ensures that the same identifier will never be carried by two separate records, even if they do not coexist at the same time.

FOREIGN KEY

Since version 3.6.19, SQLite is able to manage the foreign key constraints.

For backward compatibility, support for foreign keys is not enabled by default. The activation is done by the pragma foreign_keys.

Any column referenced by a foreign key must be declared as UNIQUE (PRIMARY KEY creates a unique key). SQLite does not yet take into account the MATCH clause in the definition of foreign keys.

Triggers

SQLite manages a fairly complete triggers (triggers in English). Triggers BEFORE, AFTER or INSTEAD OF can be declared. SQLite supports the optional FOR EACH ROW (default setting) but not FOR EACH STATEMENT.

Views

SQLite allows the creation of views to reduce the length of queries.

The views are read-only, but it is possible to use Triggers with INSTEAD OF property to simulate the possibility of modifying them.

Transactions

All SQL commands to change the state of the database (almost any other order as SELECT) involving the creation of a transaction that is dedicated to them, provided that a transaction that includes the command is already created. This means that all commands are atomic. If running the command does not cause an error, the change is automatically committed (autocommit), but if this is not the case then the entirety of changes made by the command is canceled.

All changes to the database are serialized: a single change is made to both the base and is locked in reading when a change.

SQLite allows the creation of transactions as well as the creation of turning points (SAVEPOINT) but does not handle different levels of insulation. In a transaction, the first call to a read command, a shared lock is activated which allows read access but prohibits any modification of data by another transaction during the first appeal in writing, all the base is locked in reading and writing for other transactions.

ACID

Although at first sight SQLite meets all the ACID properties, which determine the reliability of a transactional system, it is possible to place the database in an inconsistent state because the types are not forced: it is possible For example, to insert a string in a column whose type affinity is defined as an integer. In their strict interpretations, SQLite does not meet all the ACID properties.

Temporary Tables

SQLite allows the creation of temporary tables which are defined. Data and indexes are not saved in the database and are therefore lost during the closure of it.

Virtual tables

It is possible to create, directly from the library, its own storage engine to simulate a table in the database. Creating a virtual table is done by implementing a set of functions. Access to the table is done completely transparent, except the absence of some features (not create triggers or index or to modify the table structure).

This mechanism allows access using the SQL language to any kind of data source like CSV or XML.

Functions

SQLite provides a programming interface, through its library for creating user-defined functions. The set of functions defined by the library can be overridden, to redefine their implementation. Some operators such as LIKE using sub-layer functions eponymous which may then be replaced by user-defined functions.

SQLite does not support the creation of procedures, but their need is less because of the embedded architecture.

Index

SQLite allows the creation of indexes on one or more columns. Indexes can be amounts (ASC) or descending (DESC) and single DateTime (this is akin to creating a unique constraint). SQLite uses its indexes to tree B.

SQLite brought on keyword EXPLAIN for describing the steps necessary to execute a command and the index used.

Pragmas

Pragmas are pairs of keys / values SQLite configuration. They are internal to a database and can describe how SQLite interpreting certain operations. They can also enable or disable certain features, especially for backward compatibility.

Adoption

In addition to its formal establishment in C, bindings for other languages exist (C + +, Perl, Ruby, TCL, using languages .NET framework via an ADO.NET driver).

Some programming languages include SQLite in their standard library, is the case, among others, Python (since version 2.5) and PHP (since version 5).

SQLite is used in many open source software like Mozilla Firefox, in many GNU / Linux in server operating systems like Solaris and desktop or mobile as Android or Symbian, in some software from Apple, Google, Adobe and McAfee as well as some devices from Philips.

The draft covering the storage of a SQL database on the side of the web browser published by the W3C also states that locating the software functionality must be able to correctly interpret the dialect in SQLite version 3.6.19. Although SQLite is not imposed by the W3C, Google Chrome, Apple Safari and Opera Browser use for this purpose.

Different versions

SQLite exists in two main versions: 2.x and 3.x Versions 2 and 3 of SQLite are distinguished by several developments:

  • The database files are not always mutually compatible. This means that basic format SQLite 2 can not be read certainly by SQLite 3 and vice versa;
  • SQL syntax highlighting does not exist in SQLite 2: IF NOT EXISTS for queries, and CREATE TABLE, ADD COLUMN and RENAME COLUMN to queries ALTER TABLE;
  • Only the latest versions of SQLite support some more advanced features, such as management of foreign keys or CHECK constraints;
  • SQLite 3 supports standard UTF-8 and UTF-16;
  • SQLite 3 encodes identifiers lines of 64 bits instead of 32 bits, allowing a virtually unlimited number of lines;
  • PHP uses a class or PDO with PHP 5.3, function sqlite3_ * () to manage SQLite 3, so it uses functions sqlite_ * () for SQLite 2.

Study: From Wikipedia, the free encyclopedia. The text is available under the Creative Commons.

Sharing