Guest User

Untitled

a guest
Mar 8th, 2018
95
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 22.78 KB | None | 0 0
  1. <?php
  2.  
  3. /**
  4. * MJ_E_CONN
  5. * MJ_E_CONN_NO_DSN
  6. * MJ_E_CONN_CONFIG
  7. * MJ_E_CONN_NO_GROUP
  8. * MJ_E_CONN_UNDEF_GROUP
  9. * MJ_E_CONN_UNSUPPORTED
  10. */
  11.  
  12. /**
  13. * An SQL builder class for PDO database connections.
  14. *
  15. * @package Majic
  16. * @author Michael Jackson
  17. * @version SVN: $id: $
  18. */
  19. abstract class majic_Connection extends PDO
  20. {
  21.  
  22. private static $instances = array();
  23. private $cache = array(
  24. 'column_names' => array()
  25. );
  26.  
  27. private $distinct = false;
  28. private $limit = null;
  29. private $offset = null;
  30. private $join = array();
  31. private $where = array();
  32. private $group_by = array();
  33. private $having = array();
  34. private $order_by = array();
  35.  
  36. /**
  37. * Gets a database connection object with the specified parameters.
  38. *
  39. * @param string $dsn The PDO data source name to use
  40. * @param string $user The user name to use
  41. * @param string $pass The password to use
  42. * @param array $options Some connection-specific options to use
  43. * @return majic_Connection The database connection object
  44. * @throws majic_Exception
  45. * @access public
  46. * @static
  47. */
  48. public static function factory($dsn, $user = null, $pass = null,
  49. $options = array())
  50. {
  51. $driver = "majic_connection_" . preg_replace('/:.*$/', '', $dsn);
  52. if (!class_exists($driver, true)) {
  53. throw new majic_Exception(MJ_E_CONN_UNSUPPORTED, "driver=$driver");
  54. }
  55.  
  56. try {
  57. $conn = new $driver($dsn, $user, $pass);
  58. $conn->setAttribute(PDO::ATTR_STATEMENT_CLASS, array(
  59. 'majic_Statement',
  60. array()
  61. ));
  62. $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  63. } catch (PDOException $e) {
  64. $msg = $e->getMessage();
  65. $trc = $e->getTraceAsString();
  66. throw new majic_Exception(MJ_E_CONN, "message=$msg, trace=$trc");
  67. }
  68.  
  69. self::$instances[$dsn] = array(
  70. 'conn' => $conn,
  71. 'user' => $user
  72. );
  73.  
  74. return $conn;
  75. }
  76.  
  77. /**
  78. * Gets a database connection object with the specified parameters. If an
  79. * object with the same connection parameters already exists, it will be
  80. * returned.
  81. *
  82. * @param string $group The database configuration options group
  83. * name to use
  84. * @return majic_Connection The database connection object
  85. * @access public
  86. * @static
  87. */
  88. public static function singleton($group = null)
  89. {
  90. extract(self::fetchOptions($group));
  91.  
  92. if (array_key_exists($dsn, self::$instances)) {
  93. $conn = self::$instances[$dsn];
  94. if ($user == $conn['user']) {
  95. return $conn['conn'];
  96. }
  97. }
  98.  
  99. $conn = self::factory($dsn, $user, $pass, $options);
  100. return $conn;
  101. }
  102.  
  103. /**
  104. * Fetches an array of database configuration options from the specified
  105. * group in the database configuration file.
  106. *
  107. * @param string $group The configuration group options to get
  108. * @return array An array of connection configuration options
  109. * @throws majic_Exception
  110. * @access private
  111. * @static
  112. */
  113. private static function fetchOptions($group = null)
  114. {
  115. require APP_DIR . DS . 'config' . DS . 'conn.php';
  116.  
  117. if (!isset($conn)) {
  118. throw new majic_Exception(MJ_E_CONN_CONFIG);
  119. }
  120. if (is_null($group)) {
  121. if (!isset($default_group)) {
  122. throw new majic_Exception(MJ_E_CONN_NO_GROUP);
  123. }
  124. $group = $default_group;
  125. unset($default_group);
  126. }
  127. if (!isset($conn[$group])) {
  128. throw new majic_Exception(MJ_E_CONN_UNDEF_GROUP);
  129. }
  130.  
  131. if (isset($conn[$group]['dsn'])) {
  132. $dsn = $conn[$group]['dsn'];
  133. } else {
  134. throw new majic_Exception(MJ_E_CONN_NO_DSN);
  135. }
  136. $user = @$conn[$group]['user'];
  137. $pass = @$conn[$group]['pass'];
  138. $options = isset($conn[$group]['options'])
  139. ? $conn[$group]['options']
  140. : array();
  141.  
  142. unset($conn);
  143. return array(
  144. 'dsn' => $dsn,
  145. 'user' => $user,
  146. 'pass' => $pass,
  147. 'options' => $options
  148. );
  149. }
  150.  
  151. /**
  152. * Resets all class variables to their initial state. Automatically called
  153. * after every select, insert, update, and/or delete.
  154. *
  155. * @return void
  156. * @access public
  157. */
  158. public function reset()
  159. {
  160. $this->distinct = false;
  161. $this->limit = null;
  162. $this->offset = null;
  163. $this->join = array();
  164. $this->where = array();
  165. $this->having = array();
  166. $this->group_by = array();
  167. $this->order_by = array();
  168. }
  169.  
  170. /**
  171. * Lists the columns in a database table.
  172. *
  173. * @param string $table The database table name
  174. * @return array The names of the table's columns
  175. * @access public
  176. */
  177. public function listColumns($table)
  178. {
  179. if (!isset($this->cache['column_names'][$table])) {
  180. if (!$this->moduleLoaded('manager')) {
  181. $this->loadModule('manager');
  182. }
  183. $this->cache['column_names'][$table] = $this->manager->listTableFields($table);
  184. }
  185.  
  186. return $this->cache['column_names'][$table];
  187. }
  188.  
  189. /**
  190. * Compiles a SELECT statement and queries the database.
  191. *
  192. * @param mixed $fields The fields to select
  193. * @param string $table The table to use
  194. * @param mixed $where The WHERE condition
  195. * @param string $limit The LIMIT to use
  196. * @param string $offset The OFFSET to use
  197. * @return MDB2_Result The result of the query
  198. * @throws majic_Exception, majic_PEARException
  199. * @access public
  200. */
  201. public function select($fields = null, $table = null, $where = null,
  202. $limit = null, $offset = null)
  203. {
  204. if (!is_null($where)) {
  205. $this->where($where);
  206. }
  207. if (!is_null($limit)) {
  208. $this->limit($limit, $offset);
  209. }
  210.  
  211. $sql = $this->distinct == true ? 'SELECT DISTINCT ' : 'SELECT ';
  212.  
  213. if (is_null($fields)) {
  214. $fields = array('*');
  215. }
  216. if (!is_array($fields)) {
  217. $fields = array($fields);
  218. }
  219.  
  220. $sql .= implode(', ', $fields);
  221.  
  222. if (is_null($table)) {
  223. throw new majic_Exception(MJ_E_SQL_NO_TABLE);
  224. } else {
  225. if (!is_array($table)) {
  226. $table = array($table);
  227. }
  228.  
  229. $sql .= "\nFROM " . implode(', ', $table);
  230. }
  231. if (count($this->join) > 0) {
  232. $sql .= "\n";
  233. $sql .= implode("\n", $this->join);
  234. }
  235. if (count($this->where) > 0) {
  236. $sql .= "\nWHERE ";
  237. $sql .= implode("\n", $this->where);
  238. }
  239. if (count($this->group_by) > 0) {
  240. $sql .= "\nGROUP BY " . implode(', ', $this->group_by);
  241. }
  242. if (count($this->having) > 0) {
  243. $sql .= "\nHAVING " . implode("\n", $this->having);
  244. }
  245. if (count($this->order_by) > 0) {
  246. $sql .= "\nORDER BY " . implode(', ', $this->order_by);
  247. }
  248. if (is_numeric($this->limit)) {
  249. $limit = $this->db->setLimit($this->limit, $this->offset);
  250. /*
  251. if (MDB2::isError($limit)) {
  252. throw new majic_PEARException($limit);
  253. }
  254. */
  255. }
  256.  
  257. $this->reset();
  258. $result = $this->query($sql);
  259. /*
  260. if (MDB2::isError($result)) {
  261. throw new majic_PEARException($result);
  262. }
  263. */
  264. return $result;
  265. }
  266.  
  267. /**
  268. * Compiles an INSERT statement and queries the database.
  269. *
  270. * @param string $table The table name
  271. * @param mixed $data An array (or object) of data to insert
  272. * @return bool True upon success
  273. * @throws majic_Exception, majic_PEARException
  274. * @access public
  275. */
  276. public function insert($table = null, $data = null)
  277. {
  278. if (is_null($table)) {
  279. throw new majic_Exception(MJ_E_SQL_NO_TABLE);
  280. }
  281. if (is_null($data)) {
  282. throw new majic_Exception(MJ_E_SQL_NO_DATA);
  283. }
  284. if (is_object($data)) {
  285. $data = $this->objectToArray($data);
  286. }
  287.  
  288. $values = array();
  289. foreach ($data as $datum) {
  290. $values[] = $this->db->quote($datum);
  291. }
  292.  
  293. $keys = implode(', ', array_keys($data));
  294. $values = implode(', ', $values);
  295. $sql = "INSERT INTO $table ($keys) VALUES ($values)";
  296.  
  297. $this->reset();
  298. $affected_rows = $this->db->exec($sql);
  299. if (MDB2::isError($affected_rows)) {
  300. throw new majic_PEARException($affected_rows);
  301. }
  302.  
  303. return $affected_rows;
  304. }
  305.  
  306. /**
  307. * Compiles an UPDATE statement and queries the database.
  308. *
  309. * @param mixed $table The table name
  310. * @param mixed $data An array (or object) of data to update
  311. * @param array $where The WHERE condition
  312. * @return int The number of affected rows
  313. * @throws majic_Exception, majic_PEARException
  314. * @access public
  315. */
  316. public function update($table = null, $data = null, $where = null)
  317. {
  318. if (is_null($table)) {
  319. throw new majic_Exception(MJ_E_SQL_NO_TABLE);
  320. }
  321. if (is_null($data)) {
  322. throw new majic_Exception(MJ_E_SQL_NO_DATA);
  323. }
  324. if (is_array($table)) {
  325. $table = implode(', ', $table);
  326. }
  327. if (is_object($data)) {
  328. $data = $this->objectToArray($data);
  329. }
  330. if (!is_null($where)) {
  331. $this->where($where);
  332. }
  333.  
  334. $values = array();
  335. foreach ($data as $column => $datum) {
  336. $values[] = $column . ' = ' . $this->db->quote($datum);
  337. }
  338.  
  339. $sql = "UPDATE $table SET " . implode(', ', $values);
  340. if (count($this->where) > 0) {
  341. $sql .= "\nWHERE " . implode("\n", $this->where);
  342. }
  343.  
  344. $this->reset();
  345. $affected_rows = $this->db->exec($sql);
  346. if (MDB2::isError($affected_rows)) {
  347. throw new majic_PEARException($affected_rows);
  348. }
  349.  
  350. return $affected_rows;
  351. }
  352.  
  353. /**
  354. * Compiles a DELETE statement and queries the database.
  355. *
  356. * @param string $table The table to use
  357. * @param mixed $where The WHERE condition
  358. * @return int The number of affected rows
  359. * @throws majic_Exception, majic_PEARException
  360. * @access public
  361. */
  362. public function delete($table = null, $where = null)
  363. {
  364. if (is_null($table)) {
  365. throw new majic_Exception(MJ_E_SQL_NO_TABLE);
  366. }
  367. if (!is_null($where)) {
  368. $this->where($where);
  369. }
  370.  
  371. $sql = "DELETE FROM $table";
  372. if (count($this->where) > 0) {
  373. $sql .= "\nWHERE " . implode("\n", $this->where);
  374. }
  375.  
  376. $this->reset();
  377. $affected_rows = $this->db->exec($sql);
  378. if (MDB2::isError($affected_rows)) {
  379. throw new majic_PEARException($affected_rows);
  380. }
  381.  
  382. return $affected_rows;
  383. }
  384.  
  385. /**
  386. * Sets a flag which tells the query string compiler to add DISTINCT to
  387. * the query.
  388. *
  389. * @param bool $distinct True to add DISTINCT, false otherwise
  390. * @return object
  391. * @access public
  392. */
  393. public function distinct($distinct = true)
  394. {
  395. $this->distinct = is_bool($distinct) ? $distinct : true;
  396. return $this;
  397. }
  398.  
  399. /**
  400. * Generates the JOIN portion of the query.
  401. *
  402. * @param string $table The table name
  403. * @param string $cond The join condition
  404. * @param string $type The type of join
  405. * @return object
  406. * @access public
  407. */
  408. public function join($table, $cond, $type = '')
  409. {
  410. // make sure the type of join is valid
  411. if ($type != '') {
  412. $join_types = array(
  413. 'LEFT',
  414. 'RIGHT',
  415. 'OUTER',
  416. 'INNER',
  417. 'LEFT OUTER',
  418. 'RIGHT OUTER'
  419. );
  420. $type = strtoupper(trim($type));
  421. if (!in_array($type, $join_types, true)) {
  422. $type = '';
  423. } else {
  424. $type .= ' ';
  425. }
  426. }
  427.  
  428. $this->join[] = "{$type}JOIN $table ON $cond";
  429.  
  430. return $this;
  431. }
  432.  
  433. /**
  434. * Prepares the WHERE condition of the query with escaped values. May
  435. * be called in several different ways:
  436. *
  437. * 1) where('id', 1)
  438. *
  439. * Appends 'WHERE id = 1' to the query. Note: if this is called after the
  440. * first WHERE condition has already been set, WHERE will not be used and
  441. * the condition will be appended using AND.
  442. *
  443. * 2) where('id', 1, 'or')
  444. *
  445. * Appends 'OR id = 1' to the query. Note: if this is called after the
  446. * first WHERE condition has already been set, WHERE will not be used and
  447. * the condition will be appended using OR.
  448. *
  449. * 3) where('AND balance >', 1)
  450. *
  451. * Appends 'AND balance > 1' to the query. The SQL conjunction and/or
  452. * operator may be appended to the first value.
  453. *
  454. * 4) where(array('id' => 1, 'balance >' => 200))
  455. *
  456. * Appends 'AND id = 1 AND balance > 200' to the query. An array may be
  457. * used as the first parameter to specify multiple conditions.
  458. *
  459. * 5) where(array('id' => 1, 'balance >' => 200), 'or')
  460. *
  461. * Appends 'OR id = 1 OR balance > 200' to the query. When an array is
  462. * passed as the first parameter, the second parameter specifies the default
  463. * conjunction to use.
  464. *
  465. * @param mixed $key The key to use or an array of key => values
  466. * @param mixed $value The value to match
  467. * @param string $conj May be either 'and' or 'or'
  468. * @return object
  469. * @access public
  470. */
  471. public function where($key, $value = null, $conj = null)
  472. {
  473. if (is_array($key)) {
  474. $conj = $value; // second param is conj
  475. } else {
  476. $key = array($key => $value);
  477. }
  478.  
  479. if (is_null($conj) || stripos($conj, 'and') !== false) {
  480. $conj = 'AND ';
  481. } else {
  482. $conj = 'OR ';
  483. }
  484.  
  485. foreach ($key as $k => $v) {
  486. if (count($this->where) === 0) {
  487. $k = $this->stripConjunction($k);
  488. $pre = '';
  489. } else {
  490. $pre = $conj;
  491. }
  492.  
  493. if (!empty($v)) {
  494. $v = $this->db->escape($v);
  495. if (!$op = $this->getOperator($k)) {
  496. $k .= ' =';
  497. } else if (strtoupper($op) == 'LIKE') {
  498. // does it have a wildcard already?
  499. if (!preg_match('/(^%|%$)/', $v)) {
  500. $v = "%$v%";
  501. }
  502. }
  503. $this->where[] = $pre . $k . ' ' . $this->db->quote($v);
  504. } else {
  505. $this->where[] = $pre . $k;
  506. }
  507.  
  508. }
  509.  
  510. return $this;
  511. }
  512.  
  513. /**
  514. * Prepares the HAVING condition of the query with escaped values. May be
  515. * called in several different ways:
  516. *
  517. * 1) having('count(*) = 10')
  518. *
  519. * Appends 'HAVING count(*) = 10' to the query.
  520. *
  521. * 2) having('count(*)', 10)
  522. *
  523. * Appends 'HAVING count(*) = 10' to the query. Note that passing in the
  524. * second value separately will automatically escape it.
  525. *
  526. * 3) having('count(*) >', 10)
  527. *
  528. * Appends 'HAVING count(*) > 10' to the query. Note that SQL operators
  529. * may be appended to the first parameter.
  530. *
  531. * @param mixed $key The key to use or an array of key => values
  532. * @param mixed $value The value to match
  533. * @param string $conj May be either 'and' or 'or'
  534. * @return object
  535. * @access public
  536. */
  537. public function having($key, $value = null, $conj = null)
  538. {
  539. if (is_array($key)) {
  540. $conj = $value; // second param is conj
  541. } else {
  542. $key = array($key => $value);
  543. }
  544.  
  545. if (is_null($conj) || stripos($conj, 'and') !== false) {
  546. $conj = 'AND ';
  547. } else {
  548. $conj = 'OR ';
  549. }
  550.  
  551. foreach ($key as $k => $v) {
  552. if (count($this->having) === 0) {
  553. $k = $this->stripConjunction($k);
  554. $pre = '';
  555. } else {
  556. $pre = $conj;
  557. }
  558.  
  559. if (!empty($v)) {
  560. if (!$op = $this->getOperator($k)) {
  561. $k .= ' =';
  562. }
  563. $v = ' ' . $this->db->escape($v);
  564. }
  565.  
  566. $this->having[] = $pre . $k . $v;
  567. }
  568.  
  569. return $this;
  570. }
  571.  
  572. /**
  573. * Sets the ORDER BY portion of the query.
  574. *
  575. * @param string $order_by The key to order by
  576. * @param string $order The order to use
  577. * @return object
  578. * @access public
  579. */
  580. public function orderBy($order_by, $order = '')
  581. {
  582. $order = strtoupper(trim($order));
  583. if ($order != '') {
  584. $order_types = array(
  585. 'ASC',
  586. 'DESC',
  587. 'RAND()'
  588. );
  589. $order = in_array($order, $order_types, true)
  590. ? " $order"
  591. : ' ASC';
  592. }
  593.  
  594. $this->order_by[] = $order_by . $order;
  595.  
  596. return $this;
  597. }
  598.  
  599. /**
  600. * Sets the GROUP BY portion of the query.
  601. *
  602. * @param string $by The value to group the results by
  603. * @return object
  604. * @access public
  605. */
  606. public function groupBy($by)
  607. {
  608. if (is_string($by)) {
  609. $by = explode(',', $by);
  610. }
  611.  
  612. foreach ($by as $val) {
  613. $val = trim($val);
  614. if ($val != '') {
  615. $this->group_by[] = $val;
  616. }
  617. }
  618.  
  619. return $this;
  620. }
  621.  
  622. /**
  623. * Sets the LIMIT of the query.
  624. *
  625. * @param int $value The limit value
  626. * @param int $offset The offset value
  627. * @return object
  628. * @access public
  629. */
  630. public function limit($value, $offset = null)
  631. {
  632. $this->limit = $value;
  633.  
  634. if (!is_null($offset)) {
  635. $this->offset = $offset;
  636. }
  637.  
  638. return $this;
  639. }
  640.  
  641. /**
  642. * Sets the OFFSET of the query.
  643. *
  644. * @param int $value The offset value
  645. * @return object
  646. * @access public
  647. */
  648. public function offset($value)
  649. {
  650. $this->offset = is_numeric($value) ? $value : 0;
  651. return $this;
  652. }
  653.  
  654. /**
  655. * Tests a string for SQL operators.
  656. *
  657. * @param string $value The string to test
  658. * @return string The SQL operator in the string
  659. * @access private
  660. */
  661. private function getOperator($value)
  662. {
  663. preg_match('/(<|>|!|=|like|is null|is not null)$/i', trim($value), $match);
  664. return isset($match[1]) ? $match[1] : null;
  665. }
  666.  
  667. /**
  668. * Strips the SQL conjunction from a string.
  669. *
  670. * @param string $value The string to strip
  671. * @return string The string with the SQL conjunction stripped
  672. * @access private
  673. */
  674. private function stripConjunction($value)
  675. {
  676. return preg_replace('/^\s*(and|or)\s*/i', '', $value);
  677. }
  678.  
  679. /**
  680. * Converts an object to an array, ignoring nested objects and arrays.
  681. *
  682. * @param object $obj The object to convert
  683. * @return array An associative array containing the object's variables
  684. * @access private
  685. */
  686. private function objectToArray($obj)
  687. {
  688. $arr = array();
  689. $obj_vars = get_object_vars($obj);
  690.  
  691. foreach ($obj_vars as $key => $value) {
  692. if (!is_object($value) && !is_array($value)) {
  693. $arr[$key] = $value;
  694. }
  695. }
  696. }
  697.  
  698. }
  699.  
  700. /**
  701. * A wrapper class for PDOStatement objects. Provides some convenient methods
  702. * for extracting data.
  703. *
  704. * @package Majic
  705. * @author Michael Jackson
  706. * @version SVN: $id: $
  707. */
  708. class majic_Statement extends PDOStatement
  709. {
  710.  
  711. /**
  712. * Constructor.
  713. *
  714. * @access protected
  715. */
  716. protected function __construct()
  717. {
  718.  
  719. }
  720.  
  721. /**
  722. * Fetches the result of the query as an array of objects.
  723. *
  724. * @return array An array of objects resulting from the query
  725. * @throws majic_PEARException
  726. * @access public
  727. */
  728. public function result()
  729. {
  730. return $this->fetchAll(PDO::FETCH_OBJ);
  731. }
  732.  
  733. /**
  734. * Fetches the result of the query as an array of arrays.
  735. *
  736. * @return array An array of arrays resulting from the query
  737. * @throws majic_PEARException
  738. * @access public
  739. */
  740. public function resultArray()
  741. {
  742. return $this->fetchAll(PDO::FETCH_ASSOC);
  743. return $this;
  744. }
  745.  
  746. /**
  747. * Fetches the next row of the result as an object.
  748. *
  749. * @return object An object representing the next row of the query result
  750. * @throws majic_PEARException
  751. * @access public
  752. */
  753. public function row($num = null)
  754. {
  755. $row = $this->result->fetchRow(MDB2_FETCHMODE_OBJECT, $num);
  756. if (MDB2::isError($row)) {
  757. throw new majic_PEARException($row);
  758. }
  759.  
  760. return $row;
  761. }
  762.  
  763. /**
  764. * Fetches the next row of the result as an array.
  765. *
  766. * @return array An array representing the next row of the query result
  767. * @throws majic_PEARException
  768. * @access public
  769. */
  770. public function rowArray($num = null)
  771. {
  772. $row = $this->result->fetchRow(MDB2_FETCHMODE_ASSOC, $num);
  773. if (MDB2::isError($row)) {
  774. throw new majic_PEARException($row);
  775. }
  776.  
  777. return $row;
  778. }
  779.  
  780. }
  781.  
  782. ?>
Add Comment
Please, Sign In to add comment