SQLite nebo MySQL?

MyEgo.cz

home foto blogy mywindows.cz kontakt

SQLite nebo MySQL?

PHP 22.10.04
SQLite

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
  // ...
}

Komentáře

  1. 1 Vlado Raab 22.10.04, 02:10:06
    FB

    To je opravdu velmi pěkné a kdy se dočkáme PgSql verze pro Blog:CMS ?

    Je mi známo, že nativní podpora od Nuclesu 1.0 je pro MySql, ale pokud už jde SQLite, tak proč ne PgSql taky obsahuje Trigery a jsou na tom ještě lépe než SQLite, sice ta velikost je o něčm jiném, to je pravda...

    Neutrpí tím rychlost zpracování dotazu, když je to přes Gzip tak je to ostatně jedno, stejně jde všechno z komprimovaného archivu nebo se snad někde pletu...?
    Pokud ano tak mne opravte...

    Empyrea|Vlado Raab

  2. 2 Radek Hulán 22.10.04, 02:10:09
    FB

    [1] Doplnit PSQL je otázka hodiny práce. Nicméně, pokud má BLOG:CMS podporovat více databází, musí to VŽDY být na úrovni nejmenšího společného jmenovatele, tedy MySQL, DB layer pro MySQL prostě nemůže řešit triggery pro PSQL a SQlite.. Tudíž triggery v PSQL či SQLite nejdou použít. To je věc, který by programátorovi měla být jasná ;-)

    Kromě toho.. díky mému NP_Cache pluginu je naprosto jedno, kolik SQL dotazů BLOG:CMS udělá... ;-) Chce to domýšlet věci..

  3. 3 Michal 22.10.04, 07:10:29
    FB

    Musím jen konstatovat: Proč mě to taky ještě nenapadlo? Na některé mé projekty by taky SQLite stačil - díky za tip!

  4. 4 Vašek 22.10.04, 10:10:00
    FB

    ....

    Jinak sqlite je podle mě hodně dobře použitelná (škoda té nepodpory utf-8 ve verzi pro php)
    Hlavně je sexy, že se nemusí instalovat žádná databáze, ale běží to na filesystému, takže čupr na hosting.

  5. 5 cyberluk 22.10.04, 02:10:42
    FB

    SQ'lite' je hodne 'lite' - ma urcite vyhody, ale neni lepsi nez MySQL protoze: MySQL(bavime se o pouzitelne verzi 4.1) umi replikaci,clustery(master/master a master/slave) apod...nebudu tady vypisovat cely mysql manual...staci se podivat na mysql manual a manual pro sqlite a hned zjistite v cem je rozdil ;)

    SQLite se tvari spise jako 'lepsi' DBM, kdezto MySQL je relacni databaze.

    Jo taky jak tady mas tu svoji 'abstraktni vrstvu' nebo rekneme spise 'sada funkci' ,tak nevim jestli to nekdo muze pouzit, kdyz na spouste radcich nejde videt konec (chybi slova,neukoncene zavorky)....nekomu tady schazi trocha teorie ohledne programovani ;)

    Jo a nedavno jsem se docetl ze PHP,Java se narozdil od C,C++ radi mezi vyssi jazyky - myslel jsem si ze C++ patri taky mezi ty vyssi...ale ne - pry mimojine z duvodu ze si program automaticky nespravuje vyuzitou pamet.

  6. 6 Radek Hulán 22.10.04, 02:10:58
    FB

    [5] nesmíš používat MSIE, má chybu v pre tagu.. Firefox a Opera nikoliv.

  7. 7 Tomáš Kubát 22.10.04, 03:10:11
    FB

    [4] Může mi nikde vysvětlit, co v praxi znamená pojem "nativní podpora UTF-8" v souvislosti s relační databází? Jaké jsou výhody v praxi oproti DB, která podporu UTF-8 nemá.

    Omllouvám se za příspěvek, který je okrajově "stupid" :)

  8. 8 Radek Hulán 22.10.04, 03:10:30
    FB

    [7] DB, která nemá podporu pro UTF-8 (a tvůj web v UTF-8 jede) dělá chybu třeba v SELECT .. ORDER BY NAME, či třeba i v SUBSTRING(...), takže se v ní některé věci prostě nedají použít. Třeba jména začínající na Č budou řazeny až za Z a SUBSTRING(...) nebude fungovat vůbec..

  9. 9 msw 22.10.04, 06:10:32
    FB

    [2] pgSQL je precejen odlisnejsi. Nejake ty pocitadla na pg_fetch_*ok, ale sql_insert_id() (bez nazvu sloupce) si rozume predstavit neumim.
    Pokud ma pgSQL vytlacit na webu tu pomalou MySQL (ten clanek uz nevim) mely by ji podobne systemy podporovat, a to ikdyz nepouzije vsechny moznosti, ne? ;o)

    [6]Taky pouzivam nebezpečný browser (Maxthon). Zrovna vcera jsem mel opet snahu zacit pouzivat vic FF. Tak jsem ho zacal krmit ruznymi doplnky, asi jsem to prehnal pac zacal zlobit :o\\. Tak jsem musel nektere ubrat. Ale neco co by umelo mouse gestures jako Maxthon jsem stejne nenasel. Takze jsem zas msie a doufam casem maxthon vylepsi podporu Gecka.

  10. 10 Radek Hulán 22.10.04, 06:10:53
    FB

    [9] sql_insert_id bere jako parametr otevřený link na databázi, nikoliv resultset, stačí se podívat do dokumentace..

    Jinak cyberlook nepoužívá MSIE a jeho klony (=smrduté krávy), není masochista...

    http://myego.cz/item/dokonala-konfigurace-firefoxu...

  11. 11 msw 22.10.04, 09:10:33
    FB

    [10] jj, $activedb to jsem si vsiml. Ja jen psal, ze nevim jak bych ziskal hodnotu posledni vlozenou hodnotu do nektereho serial sloupce v otevrene databazi. Zjisteni OID pres pg_last_oid() mi neprijde odpovidajici fce k mysql_insert_id().
    Jedine co me napadlo je normalni dotaz, ale to bych musel prohledavat dotazy a zjistovat do jakeho sloupce se naposled vkladalo.

    Aha, tak jsem to spatne pochopil, cyberlookovi se omlovam. Nj, asi holt jsem masochista. Opravdu kvalitni vyber, skoda ze jsem instaloval myie2 a maxthona uz bych mohl byt spokojeny uzivatel FF :). AllInOne Gestures je docela dobry, ale smrduta krava to zvlada podstatne lip ;)).

  12. 12 qeedo 23.10.04, 10:10:03
    FB

    K tej rýchlosti príkazu SELECT podľa porovnania rýchlosti SQLite, MYSQL a PostreSQL možno povedať, že rýchlosť je porovnateľná. Nejaký výraznejší rozdiel je pri príkaze UPDATE (v uvedenom teste).

  13. 13 Jiri Zahradil 24.10.04, 12:10:08
    FB

    U SQLite je třeba dávat pozor na zamykání a práci s databází. SQLite je navržena spíše pro "sigle" přístup a tedy pokud do ní přistupuje více klientů najednou a chtějí zápis - dochází k výrazným prodlevám. Z toho důvodu je lepší používat "dávky" SQL příkazů (několik INSERT atd najednou) jsou výrazně rychlejší než jednotlivé příkazy - SQLite musí vždy zajistit zámek nad celou souborovou databází a po dokončení transakce dělá sync. MySQL myslím zamyká jen tabulky ne celou db ale zase napsat několik SQL příkazů do jednoho myqsl_query nejde vůbec.

    Stávalo se mi u stránek, které hned na začátku skriptu chtěli něco číst+psát do tabulky session_data a na konci skriptu opět aktualizovat tuto tabulku. Server mohl zpracovávat z důvodu zamčení opravdu jen jeden požadavek a pokud generování stránek trvalo déle, propustnost šla oproti MySQL rapidně dolů. Asi na to zkusím napsat nějaký porovnávací skript ...

  14. 14 Jakub 26.11.04, 05:11:06
    FB

    [13] MySQL tabulky nezamyká, pokud o to explicitně nepožádáte. Do tabulky lze střídavě zapisovat, číst a dělat cokoliv dalšího z různých konexí.

  15. 15 Jakub 26.11.04, 05:11:11
    FB

    "SQLite umí in-memory tabulky"

    MySQL přece taky umí in-memory tabulky, stačí použít typ tabulky HEAP. Dokonce jsem žil v domnění, že je defaultní pro TEMPORARY tabulky, ale v dokumentaci to teď nemohu najít.