PageRenderTime 47ms CodeModel.GetById 20ms RepoModel.GetById 0ms app.codeStats 0ms

/core/inc/class/class.sqlite.php

https://bitbucket.org/greenbox/microframework
PHP | 253 lines | 201 code | 6 blank | 46 comment | 7 complexity | 2f955851c894c6f5cc21fb35283b2732 MD5 | raw file
Possible License(s): LGPL-2.1, GPL-3.0
  1. <?php
  2. /**
  3. * @author Jonathan Gotti <jgotti at jgotti dot org>
  4. * @copyleft (l) 2003-2008 Jonathan Gotti
  5. * @package DB
  6. * @license http://opensource.org/licenses/gpl-license.php GNU Public License
  7. * @subpackage SQLITE
  8. * @changelog - 2008-04-06 - no more $mode parameter to construct the database (not managed by the extension at all so drop it)
  9. * - drop php4 support, and buffered query are no longer supported (was useless as db has it's own buffer)
  10. * - autoconnect is now a static property
  11. * - add check_conn method
  12. * - 2006-05-12 - clean the escape_string() method
  13. * - 2006-04-17 - rewrite the class to use abstarction class db
  14. * - Conditions params support on methods select_*, update, delete totally rewrite to handle smat question mark
  15. * @see db::process_conds()
  16. * - get_field and list_fields are now deprecated but still supported (listfield will be indexed by name whatever is $indexed_by_name)
  17. * - 2005-02-25 - now the associative_array_from_q2a_res method won't automaticly ksort the results anymore
  18. * - re-enable the possibility to choose between SQLITE_ASSOC or SQLITE_NUM
  19. * - 2005-02-28 - new method optimize and vacuum
  20. * - 2005-04-05 - get_fields will now try to get fields from sqlite_master if no data found in the table
  21. * @todo add transactions support (you can use it on your own with query())
  22. */
  23. class sqlitedb extends db{
  24. public $autocreate= TRUE;
  25. public $db_file = '';
  26. public $_protect_fldname = "'";
  27. /**
  28. * create a sqlitedb object for managing locale data
  29. * if DATA_PATH is define will force access in this directory
  30. * @param string $Db_file
  31. * @return sqlitedb object
  32. */
  33. function __construct($db_file){
  34. $this->host = 'localhost';
  35. $this->db_file = $db_file;
  36. $this->conn = &$this->db; # only for better compatibility with other db implementation
  37. if(db::$autoconnect)
  38. $this->open();
  39. }
  40. ###*** REQUIRED METHODS FOR EXTENDED CLASS ***###
  41. /** open connection to database */
  42. function open(){
  43. //prevent multiple db open
  44. if($this->db)
  45. return $this->db;
  46. if(! $this->db_file )
  47. return FALSE;
  48. if(! (is_file($this->db_file) || $this->autocreate) )
  49. return FALSE;
  50. if( $this->db = sqlite_open($this->db_file, 0666, $error)){
  51. return $this->db;
  52. }else{
  53. $this->verbose($error,__FUNCTION__,1);
  54. return FALSE;
  55. }
  56. }
  57. /** close connection to previously opened database */
  58. function close(){
  59. if( !is_null($this->db) )
  60. sqlite_close($this->db);
  61. $this->db = null;
  62. }
  63. /**
  64. * check and activate db connection
  65. * @param string $action (active, kill, check) active by default
  66. * @return bool
  67. */
  68. function check_conn($action = ''){
  69. if(! $this->db){
  70. if($action !== 'active')
  71. return $action==='kill'?true:false;
  72. return $this->open()===false?false:true;
  73. }else{
  74. if($action==='kill'){
  75. $this->close();
  76. $this->db = null;
  77. }
  78. return true;
  79. }
  80. }
  81. /**
  82. * take a resource result set and return an array of type 'ASSOC','NUM','BOTH'
  83. * @param resource $result_set
  84. * @param string $result_type in 'ASSOC','NUM','BOTH'
  85. */
  86. function fetch_res($result_set,$result_type='ASSOC'){
  87. $result_type = strtoupper($result_type);
  88. if(! in_array($result_type,array('NUM','ASSOC','BOTH')) )
  89. $result_type = 'ASSOC';
  90. eval('$result_type = SQLITE_'.$result_type.';');
  91. while($res[]=sqlite_fetch_array($result_set,$result_type));
  92. unset($res[count($res)-1]);//unset last empty row
  93. #- ~ $this->num_rows = sqlite_num_rows($this->last_qres);
  94. $this->num_rows = count($res);
  95. return $this->last_q2a_res = count($res)?$res:FALSE;
  96. }
  97. function last_insert_id(){
  98. return $this->db?sqlite_last_insert_rowid($this->db):FALSE;
  99. }
  100. /**
  101. * perform a query on the database
  102. * @param string $Q_str
  103. * @return result id | FALSE
  104. */
  105. function query($Q_str){
  106. if(! $this->db ){
  107. if(! (db::$autoconnect && $this->open()) )
  108. return FALSE;
  109. }
  110. $this->verbose($Q_str,__FUNCTION__,2);
  111. $this->last_qres = sqlite_unbuffered_query($this->db,$Q_str);
  112. if(! $this->last_qres)
  113. $this->set_error(__FUNCTION__);
  114. return $this->last_qres;
  115. }
  116. /**
  117. * perform a query on the database like query but return the affected_rows instead of result
  118. * give a most suitable answer on query such as INSERT OR DELETE
  119. * Be aware that delete without where clause can return 0 even if several rows were deleted that's a sqlite bug!
  120. * i will add a workaround when i'll get some time! (use get_count before and after such query)
  121. * @param string $Q_str
  122. * @return int affected_rows
  123. */
  124. function query_affected_rows($Q_str){
  125. if(! $this->query($Q_str) )
  126. return FALSE;
  127. return sqlite_changes($this->db);
  128. }
  129. /**
  130. * return the list of field in $table
  131. * @param string $table name of the sql table to work on
  132. * @param bool $extended_info if true will return the result of a show field query in a query_to_array fashion
  133. * (indexed by fieldname instead of int if false)
  134. * @return array
  135. */
  136. function list_table_fields($table,$extended_info=FALSE){
  137. # Try the simple method
  138. if( (! $extended_info) && $res = $this->query_to_array("SELECT * FROM $table LIMIT 0,1")){
  139. return array_keys($res[0]);
  140. }else{ # There 's no row in this table so we try an alternate method or we want extended infos
  141. if(! $fields = $this->query_to_array("SELECT sql FROM sqlite_master WHERE type='table' AND name ='$table'") )
  142. return FALSE;
  143. # get fields from the create query
  144. $flds_str = $fields[0]['sql'];
  145. $flds_str = substr($flds_str,strpos($flds_str,'('));
  146. $type = "((?:[a-z]+)\s*(?:\(\s*\d+\s*(?:,\s*\d+\s*)?\))?)?\s*";
  147. $default = '(?:DEFAULT\s+((["\']).*?(?<!\\\\)\\4|[^\s,]+))?\s*';
  148. if( preg_match_all('/(\w+)\s+'.$type.$default.'[^,]*(,|\))/i',$flds_str,$m,PREG_SET_ORDER) ){
  149. $key = "PRIMARY|UNIQUE|CHECK";
  150. $Extra = 'AUTOINCREMENT';
  151. $default = 'DEFAULT\s+((["\'])(.*?)(?<!\\\\)\\2|\S+)';
  152. foreach($m as $v){
  153. list($field,$name,$type,$default) = $v;
  154. # print_r($field);
  155. if(!$extended_info){
  156. $res[] = $name;
  157. continue;
  158. }
  159. $res[$name] = array('Field'=>$name,'Type'=>$type,'Null'=>'YES','Key'=>'','Default'=>$default,'Extra'=>'');
  160. if( preg_match("!($key)!i",$field,$n))
  161. $res[$name]['Key'] = $n[1];
  162. if( preg_match("!($Extra)!i",$field,$n))
  163. $res[$name]['Extra'] = $n[1];
  164. if( preg_match('!(NO)T\s+NULL!i',$field,$n))
  165. $res[$name]['Null'] = $n[1];
  166. }
  167. return $res;
  168. }
  169. return FALSE;
  170. }
  171. }
  172. /**
  173. * get the table list
  174. * @return array
  175. */
  176. function list_tables(){
  177. if(! $tables = $this->query_to_array('SELECT name FROM sqlite_master WHERE type=\'table\'') )
  178. return FALSE;
  179. foreach($tables as $v){
  180. $ret[] = $v['name'];
  181. }
  182. return $ret;
  183. }
  184. /** Verifier si cette methode peut s'appliquer a SQLite * /
  185. function show_table_keys($table){}
  186. /**
  187. * optimize table statement query
  188. * @param string $table name of the table to optimize
  189. * @return bool
  190. */
  191. function optimize($table){
  192. return $this->vacuum($table);
  193. }
  194. /**
  195. * sqlitedb specific method to use the vacuum statement (used as replacement for mysql optimize statements)
  196. * you should use db::optimize() method instead for better portability
  197. * @param string $table_or_index name of table or index to vacuum
  198. * @return bool
  199. */
  200. function vacuum($table_or_index){
  201. return $this->query("VACUUM $table_or_index;");
  202. }
  203. function error_no(){
  204. return $this->db?sqlite_last_error($this->db):FALSE;
  205. }
  206. function error_str($errno=null){
  207. return sqlite_error_string($errno);
  208. }
  209. /**
  210. * base method you should replace this one in the extended class, to use the appropriate escape func regarding the database implementation
  211. * @param string $quotestyle (both/single/double) which type of quote to escape
  212. * @return str
  213. */
  214. function escape_string($string,$quotestyle='both'){
  215. $string = sqlite_escape_string($string);
  216. switch(strtolower($quotestyle)){
  217. case 'double':
  218. case 'd':
  219. case '"':
  220. $string = str_replace("''","'",$string);
  221. $string = str_replace('"','\"',$string);
  222. break;
  223. case 'single':
  224. case 's':
  225. case "'":
  226. break;
  227. case 'both':
  228. case 'b':
  229. case '"\'':
  230. case '\'"':
  231. $string = str_replace('"','\"',$string);
  232. break;
  233. }
  234. return $string;
  235. }
  236. }