Camen Design

c share + remix sqlite 2.0 KB

Under the Hood #3:
Using a Quick & Easy SQLite Database

  1. The Database Class
  2. Instantiating
  3. Making Queries
    1. Standard Queries
    2. Executing Without Results
    3. Returning an Array
    4. Returning a Single Value
    5. Return a Flat Array of Single Values
    6. Compile a Query for Re-Use
  4. Limitations

When it came to writing this website, the thought of using a MySQL database made me laugh. There is nothing lightweight about MySQL. It is difficult to set up, difficult to maintain, difficult to fix, difficult-everything.

I set myself the goal of having no more than 10 fields in the database. There are 7.

It therefore made sense to use SQLite, a micro database system available to PHP that saves all its data into a file on disk, rather than through a client↔server architecture. It is quick, easy to use, and requires minimal fuss to operate.

To begin with I was using an excellent SQLite wrapper for PHP “SQLiteDB”. Whilst this is functionally very good, there were a number of issues I wanted to solve in my design that would mean modifying SQLiteDB heavily, and my website is supposed to be lightweight, using only the lines of code needed, and there was much I wasn’t using in SQLiteDB.

These considerations were:

  1. The database must be able to create itself, without having to connect and run an SQL check every page-load

  2. The database code must only connect to the database when necessary to reduce load.
    Connecting every page-load when the database isn’t used for that page is no good

  3. Very compact code

The Database Class

This is my SQLite database class that allows you to create and interact with SQLite databases with ease:
(I’ll break the code down and give examples of use afterwards)

This code requires PHP’s PDO to be installed and enabled in your environment, which is the default.

class database {
	//query types supported, see the `query` method for descriptions
	const query_standard     = 0;
	const query_array        = 1;
	const query_single       = 2;
	const query_single_array = 3;
	const query_prepare      = 4;
	
	private $filepath;
	private $handle;
	private $sql;
	
	function __construct ($filepath, $sql = '') {
		$this->filepath = $filepath;
		$this->sql      = $sql;
	}
	
	private function connect () {
		//does the database file exist on disk?
		$populate = file_exists ($this->filepath);
		//connect to the database (automatically creates the file on disk if it doesn’t exist)
		$this->handle = new PDO ('sqlite:'.$this->filepath);
		
		//if the database is new, build the tables from the sql originally passed to the class
		if (!$populate) $this->exec ($this->sql);
	}
	
	//execute sql statement(s) without returning a recordset. instead returns true/false for success
	public function exec ($sql) {
		//no connection is made to the database until a query is made
		if (!isset ($this->handle)) $this->connect ();
		return $this->handle->exec ($sql);
	}
	
	public function query ($sql, $mode = self::query_standard) {
		//no connection is made to the database until a query is made
		if (!isset ($this->handle)) $this->connect ();
		
		return 	//return the entire results as an array
			$mode == self::query_array  ? $this->handle->query ($sql)->fetchAll (PDO::FETCH_NUM) : (
			//return just the value of the very first column of the first row
			$mode == self::query_single ? $this->handle->query ($sql)->fetchColumn () : (
			//return a flat array of the first value of each row
			$mode == self::query_single_array
			? $this->handle->query ($sql)->fetchAll (PDO::FETCH_COLUMN) : (
			//compile an sql query for repeat execution
			$mode == self::query_prepare ? $this->handle->prepare ($sql)
			//else: return a standard result set
			: $this->handle->query ($sql, PDO::FETCH_NUM)
		)));
	}
	
	function __destruct () {
		$this->handle = null;
	}
}

Instantiating

To create a database / connect to an existing database and pre-populate it with some SQL if it doesn’t exist, instantiate a copy of the class and provide a filepath to a .sqlite file (in a writeable directory), and some SQL statements (separated by semi-colons).

Here’s how it’s done on this site:

$database = new database (_root.'/data/content.sqlite',
	'CREATE TABLE [content] ('.
		'[when]      INT PRIMARY KEY,'.	//INT instead of INTEGER, disables auto-numbering of primary key
		'[updated]   INTEGER,'.		//last edit timestamp (for the RSS) YYYYMMDDHHMM
		'[title]     TEXT,'.		//html
		'[content]   TEXT,'.		//html
		'[tags]      TEXT,'.		//"|tag|tag|tag|tag|"
		'[enclosure] TEXT'.		//"mime-type;filename;preview_filename"
	');'.
	'CREATE TABLE [tags] ('.
		'[tag] CHAR(20) PRIMARY KEY'.
	');'
);

The $database variable is set to an instance of the database class, and the SQL string containing two CREATE TABLE commands is provided, should the database not already exist on the disk.
See the SQLite website for help on the SQL syntax.

Inside the database class, the filepath and SQL statement are saved for later, no action is taken at all. We do not connect to the database yet, nor create it, until such a task is absolutely required.

function __construct ($filepath, $sql = '') {
	$this->filepath = $filepath;
	$this->sql      = $sql;
}

Making Queries

Standard Queries

This code returns the date, tags and title of 3 blog entries from the website. An object-orientated PDOStatement is returned that you can manipulate, or loop over using a regular foreach.

$rows = $database->query (
	'SELECT [when], [tags], [title] FROM [content] ORDER BY 1 DESC LIMIT 3;',
	database::query_standard
);

print_r ($rows);

foreach ($rows as $row) {
	print_r ($row);
}

Which outputs:

PDOStatement Object
(
    [queryString] => SELECT [when], [tags], [title] FROM [content] ORDER BY 1 DESC LIMIT 3;
)
Array
(
    [0] => 200807081323
    [1] => |code|cc-by|code-is-art|web-dev|
    [2] => Under The Hood #2: <br />Internal / External Links, The CSS3 Way
)
Array
(
    [0] => 200807051806
    [1] => |code|cc-by|code-is-art|web-dev|
    [2] => Under The Hood #1: <br />Is A PNG 32-Bit? In One Line
)
Array
(
    [0] => 200807050142
    [1] => |blog|cc-by|web-dev|
    [2] => Real-World Test Successful
)

Executing Without Results

When you’re doing INSERT, UPDATE or DELETE queries there’s no recordset returned. The exec method allows you to execute an SQL statement, and only return true or false as to whether it succeeded or not. For example, this just empties the content table.

$database->exec ('DELETE FROM [content];');

Returning an Array

By using database::query_array, a normal PHP array will be returned of all the columns and rows. It’s not recommended to do this on large data-sets as it would fill up PHP’s memory and impact performance.

print_r ($database->query (
	'SELECT [when], [tags], [title] FROM [content] ORDER BY 1 DESC LIMIT 3;',
	database::query_array
));

This outputs:

Array
(
    [0] => Array
        (
            [0] => 200807081323
            [1] => |code|cc-by|code-is-art|web-dev|
            [2] => Under The Hood #2: <br />Internal / External Links, The CSS3 Way
        )

    [1] => Array
        (
            [0] => 200807051806
            [1] => |code|cc-by|code-is-art|web-dev|
            [2] => Under The Hood #1: <br />Is A PNG 32-Bit? In One Line
        )

    [2] => Array
        (
            [0] => 200807050142
            [1] => |blog|cc-by|web-dev|
            [2] => Real-World Test Successful
        )

)

Returning a Single Value

If you’re only interested in the very first value in the first row, you can return just that value without it being wrapped in an array or object. This example echoes the title of a particular blog entry.

echo ($database->query ('SELECT [title] FROM [content] WHERE [when]=200806181021;', database::query_single));

And outputs:

Hello.

Return a Flat Array of Single Values

There are instances where you wish to get the values from a single column from a number of rows. For example, I wish to retrieve just a list of the titles from a few blog entries; if I used database::query_array, like this:

print_r ($database->query (
	'SELECT [title] FROM [content] LIMIT 3;', database::query_array
));

It would give me a difficult to use array:

Array
(
    [0] => Array
        (
            [0] => The Real Reason Microsoft About-Faced on IE8 Standards Opt-In
        )

    [1] => Array
        (
            [0] => Will Microsoft please stop pulling the damn strings of A List Apart please. …
        )

    [2] => Array
        (
            [0] => Why don’t cans of paint have the colour in hexadecimal printed on them?
        )

)

What I really need is to flatten this array. The database class has a database::query_single_array type to do this for you.

print_r ($database->query (
	'SELECT [title] FROM [content] LIMIT 3;', database::query_single_array
));

Which now gives:

Array
(
    [0] => The Real Reason Microsoft About-Faced on IE8 Standards Opt-In
    [1] => Will Microsoft please stop pulling the damn strings of A List Apart please. …
    [2] => Why don’t cans of paint have the colour in hexadecimal printed on them?
)

Compile a Query for Re-Use

If you have an SQL statement that you have to execute over and over, but with different values, you can prepare an SQL statement with blanks that you can fill in afterwards each time you execute it.
Refer to the PHP Manual for instructions on how to prepare SQL statements.

Using my class, just call the query method with the statement to prepare, using database::query_prepare as the query type to get a compiled PDOStatement object back.

Limitations

SQLite only supports a subset of what MySQL does, and so it’s best for where you want to keep things simple. If you have serious data to crunch then consider MySQL, but if you just want to store some HTML and get it back, SQLite offers a light and simple way to do it.

On some hosted web-servers, PDO may not be installed or enabled. If not, you can use the built-in procedural SQLite 2 commands in PHP. Here’s my older version of the database class ;) sqlite2.php