1: <?php
  2: /**
  3:  * WsDatabase
  4:  * Is base class for working with database objects in Webiness
  5:  * framework. It handles connection to database server and exeecution of
  6:  * SQL commands.
  7:  *
  8:  * Example usage:
  9:  *
 10:  * <code>
 11:  * $db = new WsDatabase()
 12:  *
 13:  * // query database
 14:  * $res = $db->query('SELECT name FROM mytable WHERE id<=:id', array(
 15:  *      ':id' => 3
 16:  * ));
 17:  * // number of results
 18:  * $number_of_results = $db->nRows;
 19:  *
 20:  * // update record in database
 21:  * $db->execute('UPDATE mytable SET name=:name WHERE id=:id',
 22:  *     array(':name' => 'new', ':id' => 3));
 23:  * </code>
 24:  *
 25:  */
 26: class WsDatabase
 27: {
 28:     /**
 29:      * @var PDO $_dbh PDO database handler
 30:      *
 31:      */
 32:     private $_dbh;
 33:     /**
 34:      * @var integer $nRows Number of affected or returned rows
 35:      * @see query()
 36:      * @see execute()
 37:      *
 38:      */
 39:     public $nRows;
 40:     /**
 41:      * @var boolean $isConnected Is database connection live
 42:      *
 43:      */
 44:     public $isConnected = false;
 45: 
 46: 
 47:     public function __construct()
 48:     {
 49:         // PDO connection string
 50:         $cs = WsConfig::get('db_driver');
 51:         if (WsConfig::get('db_driver') != 'sqlite') {
 52:             $cs .= ':host='.WsConfig::get('db_host');
 53:             if (WsConfig::get('db_port')) {
 54:                 $cs .= ';port='.WsConfig::get('db_port');
 55:             }
 56:             $cs .= ';dbname='.WsConfig::get('db_name');
 57:         } else {
 58:             $cs .= ':'.dirname(dirname(dirname(__FILE__))).DIRECTORY_SEPARATOR;
 59:             $cs .= 'runtime'.DIRECTORY_SEPARATOR.'webiness.db';
 60:         }
 61: 
 62:         // connect to database
 63:         try {
 64:             $this->_dbh = new PDO($cs,
 65:                 WsConfig::get('db_user'), WsConfig::get('db_password'));
 66:             $this->_dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
 67:             $this->isConnected = true;
 68:             // enable foreign keys in sqlite
 69:             if (WsConfig::get('db_driver') === 'sqlite') {
 70:                 $this->execute('PRAGMA foreign_keys = ON');
 71:             }
 72:         } catch(PDOException $ex) {
 73:             $this->isConnected = false;
 74:             header('HTTP/1.1 500 Internal Server Error');
 75:             trigger_error($ex->getMessage(), E_USER_ERROR);
 76:         }
 77: 
 78:         unset($cs);
 79:     }
 80: 
 81: 
 82:     public function __destruct()
 83:     {
 84:         $this->_dbh = null;
 85:     }
 86: 
 87: 
 88:     /**
 89:      * Prepare and execute custom SQL query that return results (SELECT).
 90:      *
 91:      * @param string $sql Custom SQL query
 92:      * @param array $parameters List of parameters forwarded to $query
 93:      * @return array $results Results of SQL query
 94:      *
 95:      */
 96:     public function query($sql, $parameters = array())
 97:     {
 98:         if (!$this->isConnected) {
 99:             return false;
100:         }
101: 
102:         // prepare SQL statment
103:         $sth = $this->_dbh->prepare($sql,
104:             array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
105: 
106:         // bind values
107:         foreach ($parameters as $key => &$value) {
108:             if (is_int($value)) {
109:                 $param = PDO::PARAM_INT;
110:             } else if (is_bool($value)) {
111:                 $param = PDO::PARAM_BOOL;
112:             } else if (is_null($value)) {
113:                 $param = PDO::PARAM_NULL;
114:             } else if (is_string($value)) {
115:                 $param = PDO::PARAM_STR;
116:             } else {
117:                 $param = false;
118:             }
119: 
120:             if ($param) {
121:                 $sth->bindValue(":$key", $value, $param);
122:             }
123:         }
124: 
125:         $this->nRows = 0;
126: 
127:         try {
128:             $sth->execute();
129:         } catch(PDOException $ex) {
130:             header('HTTP/1.1 500 Internal Server Error');
131:             trigger_error('WsDatabase: <code>'.$ex->getMessage().'</code>',
132:                 E_USER_ERROR);
133:             return false;
134:         }
135: 
136:         $values = array();
137:         while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
138:             $values[] = $row;
139:             $this->nRows++;
140:         }
141:         $sth->closeCursor();
142: 
143:         unset($row, $sth);
144: 
145:         return $values;
146:     }
147: 
148: 
149:     /**
150:      * Prepare and execute custom SQL query that don't return results
151:      * (INSERT, UPDATE, DELETE).
152:      *
153:      * @param string $sql Custom SQL query
154:      * @param array $parameters List of parameters forwarded to $query
155:      *
156:      */
157:     public function execute($sql, $parameters = array())
158:     {
159:         if (!$this->isConnected) {
160:             return false;
161:         }
162: 
163:         // prepare SQL statement
164:         $sth = $this->_dbh->prepare($sql);
165: 
166:         // bind values
167:         foreach ($parameters as $key => $value) {
168:             if (is_numeric($value)) {
169:                 $sth->bindValue(":$key", $value, PDO::PARAM_INT);
170:             } else if (is_bool($value)) {
171:                 if (WsConfig::get('db_driver') === 'pgsql') {
172:                     $v = $value ? 't' : 'f';
173:                     $sth->bindValue(":$key", $v, PDO::PARAM_STR);
174:                 } else {
175:                     $v = $value ? 1 : 0;
176:                     $sth->bindValue(":$key", $v, PDO::PARAM_INT);
177:                 }
178:             } else if (is_null($value)) {
179:                 $sth->bindValue(":$key", $value, PDO::PARAM_NULL);
180:             } else {
181:                 $sth->bindValue(":$key", $value, PDO::PARAM_STR);
182:             }
183:         }
184: 
185:         // execute query
186:         $this->_dbh->beginTransaction();
187:         if (!$sth->execute()) {
188:             $this->_dbh->rollBack();
189:             $this->nRows = 0;
190:             header('HTTP/1.1 500 Internal Server Error');
191:             trigger_error('WsDatabase: <code>'.$sql.'</code>',
192:                 E_USER_ERROR);
193:             return false;
194:         } else {
195:             $this->_dbh->commit();
196:             $this->nRows = $sth->rowCount();
197:         }
198:         $sth->closeCursor();
199: 
200:         unset($sth);
201:         return true;
202:     }
203: 
204: 
205: /**
206:  * Execute multiple custom SQL commands without parameters
207:  *
208:  * @param string $sql Custom SQL query
209:  *
210:  */
211: public function execute_batch($sql)
212: {
213:     if (!$this->isConnected) {
214:         return false;
215:     }
216: 
217:     // execute query
218:     $this->_dbh->beginTransaction();
219:     $count = $this->_dbh->exec($sql);
220:     if ($count === false ) {
221:         $this->_dbh->rollBack();
222:         $this->nRows = 0;
223:         return false;
224:     } else {
225:         $this->_dbh->commit();
226:         $this->nRows = $count;
227:     }
228: 
229:     return true;
230: }
231: 
232: 
233:     /**
234:      * Closes connection to database server.
235:      *
236:      */
237:     public function close()
238:     {
239:         $this->_dbh = null;
240:     }
241: }
242: