SQLite nebo MySQL?

V PHP 5 je zahrnuta nativní podpora pro SQLite. SQLite je velice zajímavá databáze. MySQL 4.1 má 25MB, zatímco SQLite má jen 200KB (ano, dvěstě kilobajtů). Navíc, nemusíte ji nijak instalovat, pokud máte PHP 5, máte i SQLite. Dlouho jsem tuto databázi ignoroval, nicméně, včera jsem si napsal databázový layer pro BLOG:CMS, podporující MySQL 4.0 (mysql) a MySQL 4.1 (mysqli), a začal jsem přidávat i SQLite.

Musím říci, že SQLite je fantastická databáze. Jaké jsou její hlavní výhody?

• SQLite se nemusí instalovat, je integrována přímo v jádře PHP 5,
• SQLite umí to samé, co MySQL, a navíc má podporu pro transakce (což MySQL pro MyISAM engine nikoliv), views a triggery (!),
• SQLite umí in-memory tabulky, což je fantastická věc pro dočasné tabulky a některé složitější operace,
• SQLite má k dispozici nástroje jako SQLite Control Center, či SQLite Web Admin.

Je zde ovšem i pár nevýhod, nelze například použít FULLTEXT klíč, je nutné používat SELECT LIKE ‘%slovo%’, a kromě verze 3.0 (kterou ale zase neumí PHP 5.0.2) zde není nativní podpora UTF-8 (což ostatně neumí ani MySQL 4.0, umí to až 4.1).

Každopádně, podpora pro triggery a views a velikost jen 200KB SQLite je dobrým důvodem, abych SQLite začal používat místo MySQL pro své projekty.

SQLite je prostě fantastická a efektivní “příruční databáze”, navíc pro SELECT příkazy má být rychlejší než MySQL.

Pokud to někomu pomůže, toto je DB-layer, který jsem si napsal pro BLOG:CMS (je navržený tak, abych toho na BLOG:CMS, který byl natvrdo psán pro MySQL 4.0, musel co nejméně přepisovat):

<?php
/**
  * BLOG:CMS: PHP/MySQL Personal Content Management System 
  * http://blogcms.com/
  * http://forum.blogcms.com/
  *
  * 2003-2004, (c) Radek HULAN 
  * http://hulan.cz/
  *
  * This program is free software; you can redistribute it and/or
  * modify it under the terms of the GNU General Public License
  * as published by the Free Software Foundation; either version 2
  * of the License, or (at your option) any later version.
**/


global $SQL_TYPE;
/**
 * $SQL_TYPE=0: MySQL 4.0 (mysql)
 * $SQL_TYPE=1: MySQL 4.1 (mysqli)
 * $SQL_TYPE=2: SQLite
 */ 

/**
 * Connects to mysql, mysqli, sqlite DB
 */
function sql_connect($select = true) {
	global $SQL_HOST, $SQL_USER, $SQL_PASSWORD, $SQL_DATABASE, $activedb, $SQL_TYPE;
	$activedb = false;
    switch ($SQL_TYPE) {
	  case 0: $activedb = @mysql_connect($SQL_HOST, $SQL_USER, $SQL_PASSWORD);
			  if (!$activedb) die('Could not connect do MySQL 4.0 database.');
			  break;
	  case 1: if (function_exists('mysqli_connect'))
		  		$activedb = @mysqli_connect($SQL_HOST, $SQL_USER, $SQL_PASSWORD);
			  // try old way if mysqli fails as well	
			  if (!$activedb) { 
				  $activedb = @mysql_connect($SQL_HOST, $SQL_USER, $SQL_PASSWORD);
				  if ($activedb) $SQL_TYPE = 0; 
	          }
			  if (!$activedb) die('Could not connect do MySQL 4.1 database.');
			  break;
	  case 2: if (!is_readable($SQL_DATABASE)) die('Unable to open database \''.$dbname.'\' for reading. Permission denied.');
			  if (!is_writable($SQL_DATABASE)) die('Unable to open database \''.$dbname.'\' for writing. Permission denied.');
			  $activedb = true;
	          break;
	  default: die('sql_connect');
	}
	// select database
	if ($select) sql_select_db($SQL_DATABASE);
	// disable DB if not connected
	if (!$activedb) $SQL_TYPE = -1;
}

/**
 * Creates a new database
 */
function sql_create_db($dbname){
  global $activedb, $SQL_TYPE;
  switch ($SQL_TYPE) {
	  case 0: sql_query("CREATE DATABASE $dbname");
	          sql_connect(false);
			  break;
	  case 1: sql_query("CREATE DATABASE $dbname");
	          sql_connect(false);
			  break;
	  case 2: if (!file_exists($dbname)) {
			    @touch($dbname);
				@chmod($dbname, 0666);
				if (!file_exists($dbname)) die('Unable to create new database \''.$dbname.'\'. Permission denied.');
			  }
			  if (!is_readable($dbname)) die('Unable to open database \''.$dbname.'\' for reading. Permission denied.');
			  if (!is_writable($dbname)) die('Unable to open database \''.$dbname.'\' for writing. Permission denied.');
			  sql_connect(false);
			  break;
	  default: die('sql_create_db');
  }
}

/**
 * Selects active DB
 */
function sql_select_db($dbname) {
  global $activedb, $SQL_TYPE;
  switch ($SQL_TYPE) {
	  case 0: @mysql_select_db($dbname,$activedb) 
	  			or 
				die('Could not select MySQL 4.0 database: '.mysql_error(&$activedb));
			  break;
	  case 1: @mysqli_select_db($activedb,$dbname) 
	  			or 
				die('Could not select MySQL 4.1 database: '. mysqli_error(&$activedb));
			  break;
	  case 2: $activedb = @sqlite_popen($dbname, 0666, $sqlite_error);
	          if (!$activedb) die('Could not open SQLite database.');
			  sqlite_busy_timeout(1000);
			  break;
	  default: die('sql_select_db');
  }
}

/**
 * Returns a prefixed table name
 */
function sql_table($name) {
	global $SQL_PREFIX;
	if ($SQL_PREFIX)
		return $SQL_PREFIX . 'nucleus_' . $name;
	else
		return 'nucleus_' . $name;
}

/**
 * Disconnects from SQL server
 */
function sql_disconnect() {
  global $activedb, $SQL_TYPE;
  switch ($SQL_TYPE) {
	  case 0: @mysql_close($activedb); 
	          break;
	  case 1: @mysqli_close($activedb);
	          break;
	  case 2: @sqlite_close($activedb);
	          break;
	  default: die('sql_disconnect');
  }
}

/**
  * executes an SQL query
  */
function sql_query($query, $option = MYSQLI_STORE_RESULT) {
  global $activedb, $SQL_TYPE;
  switch ($SQL_TYPE) {
	  case 0: $result = @mysql_query($query,$activedb) or die ("Invalid query: $query Error: ".sql_error());
	          break;
	  case 1: $result = @mysqli_query($activedb,$query,$option) or die ("Invalid query: $query Error: ".sql_error());
	          break;
	  case 2: $query = preg_replace('/`(\w+)`/','$1',$query);
              $result = @sqlite_query($activedb,$query) or die ("Invalid query: $query Error: ".sql_error());
	          break;
	  default: die('sql_query');
  }
  return $result;
}

/**
 * Shows SQL DB error message
 */
function sql_error() {
  global $activedb, $SQL_TYPE;
  switch ($SQL_TYPE) {
	  case 0: return @mysql_error($activedb); 
	          break;
	  case 1: return @mysqli_error($activedb);
	          break;
	  case 2: return @sqlite_error_string(@sqlite_last_error($activedb));
	          break;
	  default: die('sql_error');
  }
}

/**
 * Disconnects from SQL server
 */
function sql_close() {
  sql_disconnect();
}

/**
 * Fetch resultset as an object
 */
function sql_fetch_object(&$resource){
  global $activedb, $SQL_TYPE;
  switch ($SQL_TYPE) {
	  case 0: return @mysql_fetch_object($resource); 
	          break;
	  case 1: return @mysqli_fetch_object($resource);
	          break;
	  case 2: return @sqlite_fetch_object($resource);
	          break;
	  default: die('sql_fetch_object');
  }
}

/**
 * Fetch resultset as an array key
 */
function sql_fetch_array(&$resource){
  global $activedb, $SQL_TYPE;
  switch ($SQL_TYPE) {
	  case 0: return @mysql_fetch_array($resource); 
	          break;
	  case 1: return @mysqli_fetch_array($resource);
	          break;
	  case 2: return @sqlite_fetch_array($resource,SQLITE_ASSOC);
	          break;
	  default: die('sql_fetch_array');
  }
}

/**
 * Fetch resultset as an array key/num
 */
function sql_fetch_assoc(&$resource){
  global $activedb, $SQL_TYPE;
  switch ($SQL_TYPE) {
	  case 0: return @mysql_fetch_assoc($resource); 
	          break;
	  case 1: return @mysqli_fetch_assoc($resource);
	          break;
	  case 2: return @sqlite_fetch_array($resource,SQLITE_BOTH);
	          break;
	  default: die('sql_fetch_assoc');
  }
}

/**
 * Fetch resultset as an array where keys are numeric values
 */
function sql_fetch_row(&$resource){
  global $activedb, $SQL_TYPE;
  switch ($SQL_TYPE) {
	  case 0: return @mysql_fetch_row($resource); 
	          break;
	  case 1: return @mysqli_fetch_row($resource);
	          break;
	  case 2: return @sqlite_fetch_array($resource,SQLITE_NUM);
	          break;
	  default: die('sql_fetch_row');
  }
}

/**
 * Returns number of rows for resultset
 */
function sql_num_rows(&$resource){
  global $activedb, $SQL_TYPE;
  switch ($SQL_TYPE) {
	  case 0: return @mysql_num_rows($resource); 
	          break;
	  case 1: return @mysqli_num_rows($resource);
	          break;
	  case 2: return @sqlite_num_rows($resource);
	          break;
	  default: die('sql_num_rows');
  }
}

/**
 * Frees from memory resultset
 */
function sql_free_result(&$resource){
  global $activedb, $SQL_TYPE;
  switch ($SQL_TYPE) {
	  case 0: @mysql_free_result($resource); 
	          break;
	  case 1: @mysqli_free_result($resource);
	          break;
	  case 2: return true;
	          break;
	  default: die('sql_free_result');
  }
}

/**
 * Returns autoincrement id of last INSERT INTO statement
 */
function sql_insert_id(){
  global $activedb, $SQL_TYPE;
  switch ($SQL_TYPE) {
	  case 0: return @mysql_insert_id(); 
	          break;
	  case 1: return @mysqli_insert_id($activedb);
	          break;
	  case 2: return @sqlite_last_insert_rowid($activedb);
	          break;
	  default: die('sql_insert_id');
  }
}

/**
 * Returns number of fields in a resultset
 */
function sql_num_fields(&$result){
  global $activedb, $SQL_TYPE;
  switch ($SQL_TYPE) {
	  case 0: return @mysql_num_fields($result); 
	          break;
	  case 1: return @mysqli_num_fields($result);
	          break;
	  case 2: return @sqlite_num_fields($result);
	          break;
	  default: die('sql_num_fields');
  }
}

/**
 * Returns number of rows affected by query
 */
function sql_affected_rows(){
  global $activedb, $SQL_TYPE;
  switch ($SQL_TYPE) {
	  case 0: return @mysql_affected_rows($activedb); 
	          break;
	  case 1: return @mysqli_affected_rows($activedb);
	          break;
	  case 2: return @sqlite_changes($activedb);
	          break;
	  default: die('sql_affected_rows');
  }
}

/**
 * Returns field attributes
 */
function sql_fetch_field(&$resource){
  global $activedb, $SQL_TYPE;
  switch ($SQL_TYPE) {
	  case 0: return @mysql_fetch_field($resource); 
	          break;
	  case 1: return @mysqli_fetch_field($resource);
	          break;
	  case 2: return array();
	          break;
	  default: die('sql_fetch_field');
  }
}

?>

PS: neobjektové je to zcela záměrně. Pokud mám 2MB zdrojového PHP kódu, který natvrdo používá (z historických důvodů) například mysql_fetch_object, je nejlepší a nejrychlejší cesta pro DB layer udělat si funkci sql_fetch_object, která bude transparentně fungovat pro MySQL 4.0, MySQL 4.1 a SQLite, a pomocí globálního search-and-replace nad těmi 2MB kódu to během jedné minuty zprovoznit :-)

Dělat to objektově, aby to “vypadalo lépe”, je hloupost a testování a ruční přepisování na dva dny, pochopitelně se stejným výsledkem jako bez objektů…

Nicméně, pokud nutně chcete objekt, doplňte k tomu kódu následující:

class DBlayer {
  // ...
  // původní kód, kde zaměníte $activedb za $this->activedb
  // ...
}