SQLite

SQLite

Developer(s)	D. Richard Hipp
Initial release	August 2000 (2000-08)
Repository	{{#property:P1324}}
Written in	C
Operating system	Crossplatform
Size	699 KiB
Type	RDBMS (embedded)
License	Public domain
Website	www.sqlite.org

SQLite — embeddable cross platform database, that support near full set of SQL commands and available open source (written on C).

General

SQLite is embeddable library with implementation of SQL 92. Its popularity caused by architecture and ability to store all data in single file. From the point of view of functionality SQLite is between MySQL and PostgreSQL. However, in practice, SQLite often appears 2-3 times faster. It is possible by virtue of highly ordered internal architecture and removing connections like "client-server" and vice versa.

All of this collected in single library is just a little bigger than client application of MySQL appears to be an impressive achievement for full value database. Using highly efficient infrastructure. SQLite is able to work with a small amount of allocated memory. This makes SQLite a very handy instrument for solving any issues assigned to database.

Pros

1. Very popular
2. Very reliable
3. Shell for managing database
4. Open source

Cons

1. Absence of stored procedures
2. Absence of inbox unicode
3. Does not fit for application that intensively appeals to database

SQLite support dynamic typing

Possible types

1. INTEGER
2. REAL
3. TEXT
4. BLOB

Architecture

Database's engine is a library, that assembles with main application. All data stored in single file on machine, that executes application.

Several processes or threads are able to read from the base simultaneously. Writing to the base can be completed just in case if there are no any another requests, treated in the moment. Otherwise, writing is rejected and sender will receive error message.

Usage

SQLite interface is similar to MySQL. Mainly transition to the SQLite concludes to changing mysql/pq/etc... prefix to sqlite.

<?php 
// create db
$db = sqlite_open("db.sqlite"); 

// create table
sqlite_query($db, "CREATE TABLE foo (id INTEGER PRIMARY KEY, name CHAR(255))"); 

// add something 
sqlite_query($db, "INSERT INTO foo (name) VALUES ('Ilia')"); 
sqlite_query($db, "INSERT INTO foo (name) VALUES ('Ilia2')"); 
sqlite_query($db, "INSERT INTO foo (name) VALUES ('Ilia3')"); 

// execute request
$result = sqlite_query($db, "SELECT * FROM foo"); 
// iterating
while ($row = sqlite_fetch_array($result)) { 
    print_r($row); 
     /* каждый результат будет выглядеть примерно так 
    Array 
    ( 
        [0] => 1 
        [id] => 1 
        [1] => Ilia 
        [name] => Ilia 
    ) 
*/ 
} 

// Close connection
sqlite_close($db); 

?>

Links

• Official page
• Introduction into SQLite
• SQLite — nice DB