Source of classes/csql.php:
  1. <?
  2. // vim: ts=4:sw=4:fdc=4:nu:nospell
  3. /**
  4. * Lightweight SQL class suitable for public use and sqlite access
  5. *
  6. * @author Ing. Jozef Sakáloš
  7. * @copyright (c) 2008, by Ing. Jozef Sakáloš
  8. * @date 31. March 2008
  9. * @version $Id: csql.php 826 2010-02-23 20:37:16Z jozo $
  10. */
  11.  
  12. // {{{
  13. /**
  14. * regexp: callback for sqlite REGEXP operator
  15. *
  16. * @author Ing. Jozef Sakáloš <jsakalos@aariadne.com>
  17. * @date 01. April 2008
  18. * @return boolean true if value matches regular expression
  19. * @param string $search string to find
  20. * @param string $value string to search in
  21. */
  22. function regexp($search, $value) {
  23. // case insensitive hard coded
  24. return @preg_match("/$search/i", $value);
  25. }
  26. // }}}
  27.  
  28. /**
  29. * concat_ws: sqlite custom function
  30. *
  31. * @author Ing. Jozef Sakáloš <jsakalos@aariadne.com>
  32. * @date 11. May 2008
  33. * @return string
  34. * @param mixed
  35. */
  36. function concat_ws() {
  37. $args = func_get_args();
  38. $sep = array_shift($args);
  39. return implode($sep, $args);
  40. } // eo function concat_ws
  41. // {{{
  42. /**
  43. * Quote array callback function
  44. *
  45. * This is walk array callback function that
  46. * surrounds array element with quotes.
  47. *
  48. * @date 08. September 2003
  49. * @access public
  50. * @return void
  51. * @param string &$val Array element to be quouted
  52. * @param mixed $key Dummy. Not used in the function.
  53. * @param string $quot Quoute to use. Double quote by default
  54. */
  55. function quote_array(&$val, $key, $quot = '"') {
  56. $quot_right = array_key_exists(1, (array) $quot) ? $quot[1] : $quot[0];
  57. $val = is_null($val) ? "null" : $quot[0] . preg_replace("/'/", "''", $val) . $quot_right;
  58. }
  59. // }}}
  60.  
  61. /**
  62. * csql class
  63. *
  64. * @author Ing. Jozef Sakáloš <jsakalos@aariadne.com>
  65. * @copyright (c) 2008 by Ing. Jozef Sakáloš
  66. * @date 31. March 2008
  67. */
  68. class csql {
  69.  
  70. // protected functions
  71. // {{{
  72. /**
  73. * getOdb: Creates PDO object
  74. *
  75. * @author Ing. Jozef Sakáloš <jsakalos@aariadne.com>
  76. * @date 31. March 2008
  77. * @access protected
  78. * @return PDO
  79. * @param string $engine
  80. * @param string $file
  81. */
  82. protected function getOdb($engine, $file) {
  83. switch($engine) {
  84. case "sqlite":
  85. if("/" !== $file[0]) {
  86. $file = realpath(".") . "/$file";
  87. }
  88. $odb = new PDO("sqlite:$file");
  89. $odb->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  90. $odb->sqliteCreateFunction("regexp", "regexp", 2);
  91. $odb->sqliteCreateFunction("concat_ws", "concat_ws");
  92. break;
  93. }
  94.  
  95. return $odb;
  96. } // eo function getOdb
  97. // }}}
  98. // {{{
  99. /**
  100. * getWhere: return where clause
  101. *
  102. * @author Ing. Jozef Sakáloš <jsakalos@aariadne.com>
  103. * @date 01. April 2008
  104. * @access protected
  105. * @return string where clause including where keyword
  106. * @param array $params
  107. */
  108. protected function getWhere($params, $ignoreFilter = "") {
  109. extract($params);
  110.  
  111. $where = isset($where) ? "where $where" : "";
  112.  
  113. if($filters) {
  114. $a = array();
  115. foreach($filters as $f=>$value) {
  116. if($f === $ignoreFilter) {
  117. continue;
  118. }
  119. if(is_array($value)) {
  120. if(sizeof($value)) {
  121. array_walk($value, "quote_array", "'");
  122. $a[] = "$f in (" . implode(",", $value) . ")";
  123. }
  124. }
  125. else {
  126. $value = trim($value);
  127. $ab = preg_split("/(>)|(<)|(=)|(!)/", $value, -1, PREG_SPLIT_DELIM_CAPTURE | PREG_SPLIT_NO_EMPTY);
  128. if(preg_match("/\|/", $value)) {
  129. $value = preg_replace("/\s*\|\s*/", "|", $value);
  130. $aa = explode("|", $value);
  131. for($i = 0; $i < sizeof($aa); $i++) {
  132. $aa[$i] = "'" . $aa[$i] . "'";
  133. }
  134. $a[] = "$f in (" . implode(",", $aa) . ")";
  135. }
  136. else if(1 < sizeof($ab)) {
  137. $value = array_pop($ab);
  138. $operator = "";
  139. for($i = 0; $i < sizeof($ab); $i++) {
  140. // $operator .= ($ab[$i] === "!" ? "not " : $ab[$i]);
  141. $operator .= $ab[$i];
  142. }
  143. $a[] = "$f $operator '$value'";
  144. }
  145. else {
  146. $a[] = "$f regexp '$value'";
  147. }
  148. }
  149. }
  150. if(sizeof($a)) {
  151. $where .= $where ? " and(" : "where (";
  152. $where .= implode(" and ", $a) . ")";
  153. }
  154. }
  155.  
  156. if($query && is_array($search) && sizeof($search)) {
  157. $a = array();
  158. foreach($search as $f) {
  159. $a[] = "$f regexp '$query'";
  160. }
  161. $where .= $where ? " and(" : "where (";
  162. $where .= implode(" or ", $a) . ")";
  163. }
  164.  
  165. error_log("Where = " . $where);
  166. return $where;
  167.  
  168. } // eo function getWhere
  169. // }}}
  170.  
  171. // public functions
  172. // {{{
  173. /**
  174. * __construct: Constructs the csql instance
  175. *
  176. * @author Ing. Jozef Sakáloš <jsakalos@aariadne.com>
  177. * @date 31. March 2008
  178. * @access public
  179. * @return void
  180. * @param string $engine Engine to use
  181. */
  182. public function __construct($engine = "sqlite", $file = "db.sqlite") {
  183. $this->odb = $this->getOdb($engine, $file);
  184. } // eo constructor
  185. // }}}
  186. // {{{
  187. /**
  188. * getCount: Returns count of records in a table
  189. *
  190. * @author Ing. Jozef Sakáloš <jsakalos@aariadne.com>
  191. * @date 31. March 2008
  192. * @access public
  193. * @return integer number of records
  194. * @param array $params
  195. */
  196. public function getCount($params) {
  197. $count = null;
  198. $countArg = $params["distinct"] ? "distinct " . $params["distinct"] : "*";
  199. $ostmt = $this->odb->prepare("select count($countArg) from {$params['table']} " . $this->getWhere($params));
  200. $ostmt->bindColumn(1, $count);
  201. $ostmt->execute();
  202. $ostmt->fetch();
  203. return (int) $count;
  204. } // eo function getCount
  205. // }}}
  206. // {{{
  207. /**
  208. * getData: Retrieves data and returns array of objects
  209. *
  210. * @author Ing. Jozef Sakáloš <jsakalos@aariadne.com>
  211. * @date 31. March 2008
  212. * @access public
  213. * @return array
  214. * @param array $params Associative array with:
  215. * - string table (Mandatory)
  216. * - array field (Mandatory)
  217. * - integer start (Optional)
  218. * - integer limit (Optional)
  219. * - string sort (Optional)
  220. * - array search (Optional) fields to search in
  221. * - string where (Optional)
  222. */
  223. public function getData($params) {
  224. // params to variables
  225. extract($params);
  226.  
  227. $sql = "select " . ($distinct ? "distinct " : "");
  228. $sql .= implode(",", $fields);
  229. $sql .= " from $table " . $this->getWhere($params);
  230. $sql .= isset($groupBy) && $groupBy ? " group by $groupBy" : "";
  231.  
  232. if(!is_null($sort)) {
  233. $sql .= " order by $sort";
  234. $sql .= is_null($dir) ? "" : " $dir";
  235. }
  236. if(!is_null($start) && !is_null($limit)) {
  237. $sql .= " limit $start,$limit";
  238. }
  239.  
  240. $ostmt = $this->odb->query($sql);
  241. return $ostmt->fetchAll(PDO::FETCH_OBJ);
  242.  
  243. } // eo function getData
  244. // }}}
  245.  
  246. /**
  247. * getFilterRows: short description of
  248. *
  249. * @author Ing. Jozef Sakáloš <jsakalos@aariadne.com>
  250. * @date 05. February 2010
  251. * @access public
  252. * @return mixed Description
  253. * @param
  254. */
  255. public function getFilterRows($name, $params, $ignoreMyself = false) {
  256. extract($params);
  257. $ignoreFilter = $ignoreMyself ? $name : "";
  258. $sql = "select distinct $name from $table " . $this->getWhere($params, $ignoreFilter) . " order by $name";
  259. error_log($sql);
  260. $ostmt = $this->odb->query($sql);
  261. $rows = $ostmt->fetchAll(PDO::FETCH_NUM);
  262. $a = array();
  263. foreach($rows as $row) {
  264. $a[] = $row[0];
  265. }
  266. return $a;
  267. // return $rows;
  268. } // eo function getFilterRows
  269.  
  270. // {{{
  271. /**
  272. * insertRecord: inserts record to table
  273. *
  274. * @author Ing. Jozef Sakáloš <jsakalos@aariadne.com>
  275. * @date 01. April 2008
  276. * @access public
  277. * @return integer id of the inserted record
  278. * @param array $params
  279. */
  280. public function insertRecord($params) {
  281. // params to vars
  282. extract($params);
  283.  
  284. $o = new stdClass();
  285. $o->success = false;
  286.  
  287. $a = "object" === gettype($data) ? get_object_vars($data) : $data;
  288. unset($a["newRecord"]);
  289. if($idName) {
  290. unset($a[$idName]);
  291. }
  292. $fields = array_keys($a);
  293. $values = array_values($a);
  294. array_walk($values, "quote_array", "'");
  295.  
  296. $sql = "insert into $table (" . implode(",", $fields) . ") values (" . implode(",", $values) . ")";
  297.  
  298. try {
  299. $this->odb->exec($sql);
  300. $o->success = true;
  301. $o->insertId = $this->odb->lastInsertId();
  302. }
  303. catch(PDOException $e) {
  304. $o->error = "$e";
  305. }
  306.  
  307. return $o;
  308.  
  309. } // eo function insertRecord
  310. // }}}
  311. // {{{
  312. /**
  313. * saveData: saves data (updates or inserts)
  314. *
  315. * @author Ing. Jozef Sakáloš <jsakalos@aariadne.com>
  316. * @date 01. April 2008
  317. * @access public
  318. * @return object either {success:true} or {success:false,error:message}
  319. * @param array $params
  320. */
  321. public function saveData($params) {
  322. // params to vars
  323. extract($params);
  324.  
  325. // return object
  326. $o = new stdClass;
  327. $o->success = false;
  328.  
  329. if(!$table || !is_array($data) || !$idName) {
  330. $o->error = "Table, data or idName is missing";
  331. return $o;
  332. }
  333.  
  334. // record loop
  335. $p = array(
  336. "table"=>$table
  337. ,"idName"=>$idName
  338. );
  339. $this->odb->exec("begin transaction");
  340.  
  341. foreach($data as $orec) {
  342. $p["data"] = $orec;
  343.  
  344. // insert/update switch
  345. if(isset($orec->newRecord) && $orec->newRecord) {
  346. $result = $this->insertRecord($p);
  347. }
  348. else {
  349. $result = $this->updateRecord($p);
  350. }
  351.  
  352. // handle error
  353. if(true !== $result->success) {
  354. $o->success = false;
  355. $o->error = $result->error;
  356. $this->odb->exec("rollback");
  357. return $o;
  358. }
  359. else {
  360. $o->success = true;
  361. }
  362.  
  363. // handle insertId if any
  364. if(isset($result->insertId)) {
  365. $o->insertIds[] = $result->insertId;
  366. }
  367. } // eo record loop
  368. $this->odb->exec("commit");
  369. return $o;
  370. } // eo function saveData
  371. // }}}
  372. // {{{
  373. /**
  374. * updateRecord: updtates one record in table
  375. *
  376. * @author Ing. Jozef Sakáloš <jsakalos@aariadne.com>
  377. * @date 31. March 2008
  378. * @access public
  379. * @return object either {success:true} or {success:false,error:message}
  380. * @param array $params with:
  381. * - string table (Mandatory)
  382. * - string idName (Mandatory) name of id field
  383. * - object data (Mandatory) Name/value pairs object or associative array
  384. */
  385. public function updateRecord($params) {
  386. // array to vars
  387. extract($params);
  388.  
  389. $o = new stdClass();
  390. $o->success = false;
  391.  
  392. if(!isset($table) || !isset($idName) || !isset($data)) {
  393. $o->error = "Table, idName or data not set.";
  394. return $o;
  395. }
  396. $asets = array();
  397. $where = "";
  398.  
  399. foreach($data as $field => $value) {
  400. if($idName === $field) {
  401. $where = " where $field='$value'";
  402. continue;
  403. }
  404. array_push($asets, "$field=" . (is_null($value) ? "null" : "'$value'"));
  405. }
  406. if(!$where) {
  407. $o->error = "idName not found in data";
  408. return $o;
  409. }
  410.  
  411. $sql = "update $table set " . implode(",", $asets) . $where;
  412.  
  413. try {
  414. $this->odb->exec($sql);
  415. $o->success = true;
  416. }
  417. catch(PDOException $e) {
  418. $o->error = "$e";
  419. }
  420.  
  421. return $o;
  422.  
  423. } // eo function updateRecord
  424. // }}}
  425. // {{{
  426. /**
  427. * output: Encodes json object and sends it to client
  428. *
  429. * @author Ing. Jozef Sakáloš <jsakalos@aariadne.com>
  430. * @date 31. March 2008
  431. * @access protected
  432. * @return void
  433. * @param object/array $o
  434. * @param string $contentType
  435. */
  436. public function output($o = null, $contentType = "application/json; charset=utf-8") {
  437. $o = $o ? $o : $this->o;
  438. $buff = json_encode($o);
  439. header("Content-Type: {$contentType}");
  440. header("Content-Size: " . strlen($buff));
  441. echo $buff;
  442. } // eo function output
  443. // }}}
  444.  
  445. } // eo class csql
  446.  
  447. // eof