library/database.php

   1 <?php
   2         // @title       DataBase abstract class
   3         // @author      Matt Todd <matt@matttoddphoto.com>
   4         // @created     2005-09-26
   5         // @desc        An abstract class to standardize the database interaction methods
   6         //              (to be implemented/inherited from a specific class, such as 'MySQL',
   7         //              'MSSQL', et al).
   8         // @requires stdexception.php (StdException class)
   9
  10         include_once 'stdexception.php';
  11
  12         // classes
  13         abstract class database {
  14                 // protected variables
  15                 public $config, $handle, $result, $is_connected; // state and configuration
  16
  17                 // adapter type
  18                 public static $adapter_class = ''; // set automatically in the constructor
  19                 public static $static_handle = null; // static handle to the DB
  20
  21                 /*
  22                         @ Adapters need to define these constants for the Query Builder
  23
  24                         // query templates
  25                         const       select = 'SELECT :columns FROM :table :join :where :group_by :having :order_by :limit';
  26                         const       delete = 'DELETE FROM :table :where :order_by :limit;';
  27                         const       insert = 'INSERT INTO :table SET :values;';
  28                         const       update = 'UPDATE :table SET :values :where :order_by :limit;';
  29                         // query templates' parts
  30                         const         join = 'LEFT JOIN :table ON :on :join';
  31                         const        where = 'WHERE :where';
  32                         const     group_by = 'GROUP BY :group_by';
  33                         const       having = 'HAVING :having';
  34                         const     order_by = 'ORDER BY :order_by';
  35                         const        limit = 'LIMIT :limit :offset';
  36                         const       offset = 'OFFSET :offset';
  37                         // meta templates
  38                         const named_entity = ':name AS :alias';
  39                         const     equality = '":1" = ":2"';
  40                 */
  41
  42                 // constructor
  43                 public function __construct($params) {
  44                         // foreach($params as $key=>$value) $this->config[$key] = $value; // '$this->config = $params;' is much shorter and far more optimized
  45                         $this->config = $params;
  46
  47                         // defaults
  48                         $this->handle = null;
  49                         self::$static_handle = null;
  50                         $this->is_connected = false;
  51                 }
  52
  53                 // abstract functions
  54                 abstract public function connect();
  55                 abstract public function select_db($database = null);
  56                 abstract public function disconnect();
  57                 abstract protected function query($params);
  58                 abstract public function iterate($result);
  59
  60                 // interface functions
  61                 public function find($params) {
  62                         // if $params is null, that means we should select everything!
  63                         // or, if it's a string, select whichever is specified: first, last, all, et al
  64
  65                         // select all columns by default
  66                         if(empty($params['columns'])) $params['columns'] = '*';
  67
  68                         if(is_array($params)) {
  69                                 // minimal preparations needed to be done to execute select query
  70                         } elseif(is_string($params)) {
  71                                 // depending on string, set certain parameters
  72                                 switch(strtolower($params)) {
  73                                         case 'all':   break;
  74                                         case 'first': $params['limit'] = '1';
  75                                                       break;
  76                                         case 'last':  $params['order_by'] = 'id DESC';
  77                                                       $params['limit'] = '1';
  78                                                       break;
  79                                 }
  80                                 $params = self::prepare_params($params);
  81                         } else {
  82                                 $params = self::prepare_params($params);
  83                         }
  84
  85                         $result = $this->select($params);
  86
  87                         // throw an exception if there's a problem
  88                         // why throw an exception? // if($result == false) throw new CouldNotFind('Could not find anything');
  89
  90                         while($row = $this->iterate($result)) {
  91                                 $rows[$row['id']] = new row($row);
  92                         }
  93
  94                         return $rows;
  95                 }
  96                 public function save($params) {
  97                         $row = $params['values'];
  98                         $params['values'] = self::build_values($params['values']->as_array());
  99
 100                         // if the ID is set, update the row; otherwise, insert a new one
 101                         if($row->id)
 102                                 $result = $this->update($params);
 103                         else
 104                                 $result = $this->insert($params);
 105
 106                         // throw an exception if there's a problem
 107                         if($result == false) throw new CouldNotSave('Could not save the data');
 108
 109                         // get the ID set properly if it isn't set (for new entries)
 110                         if(!$row->id) $row->id = $this->id($result);
 111
 112                         // return the row saved
 113                         return $row;
 114                 }
 115
 116                 // action functions
 117                 protected function select($params) {
 118                         $params[0] = self::get_clause('select');
 119
 120                         // build special clauses
 121                         $params['columns'] = self::build_columns($params);
 122                         $params = self::build_join($params);
 123                         if(empty($params['where'][0]) && !empty($params['where'])) $params['where'][0] = self::build_where_query($params['where']);
 124
 125                         // build other clauses
 126                         $clauses = array('cache', 'where', 'order_by', 'group_by', 'limit', 'offset', 'having');
 127                         foreach($clauses as $clause) {
 128                                 if(!empty($params[$clause])) $params[$clause] = self::build_clause($clause, $params[$clause]);
 129                         }
 130
 131                         return $this->query(self::build_query(self::prepare_params($params)));
 132                 }
 133                 protected function insert($params) {
 134                         $params[0] = self::get_clause('insert');
 135
 136                         return $this->query(self::build_query(self::prepare_params($params)));
 137                 }
 138                 protected function update($params) {
 139                         $params[0] = self::get_clause('update');
 140
 141                         // build other clauses
 142                         $clauses = array('where', 'order_by', 'limit');
 143                         foreach($clauses as $clause) {
 144                                 if(!empty($params[$clause])) $params[$clause] = self::build_clause($clause, $params[$clause]);
 145                         }
 146
 147                         return $this->query(self::build_query(self::prepare_params($params)));
 148                 }
 149                 public function delete($params) {
 150                         $params[0] = self::get_clause('delete');
 151
 152                         // build where clause
 153                         if(empty($params['where'][0]) && !empty($params['where'])) $params['where'][0] = self::build_where_query($params['where']);
 154
 155                         // build other clauses
 156                         $clauses = array('where', 'order_by', 'group_by', 'limit', 'offset', 'having');
 157                         foreach($clauses as $clause) {
 158                                 if(!empty($params[$clause])) $params[$clause] = self::build_clause($clause, $params[$clause]);
 159                         }
 160
 161                         $result = $this->query(self::build_query(self::prepare_params($params)));
 162
 163                         // throw an exception if there's a problem
 164                         if($result == false) throw new CouldNotDelete('Could not delete the row');
 165
 166                         return $result;
 167                 }
 168
 169                 // transactional functionality
 170                 protected function begin() {
 171                         $params[0] = self::get_clause('begin');
 172                         return $this->query(self::build_query(self::prepare_params($params)));
 173                 }
 174                 protected function commit() {
 175                         $params[0] = self::get_clause('commit');
 176                         return $this->query(self::build_query(self::prepare_params($params)));
 177                 }
 178                 protected function end() {
 179                         // alias of commit()
 180                         return $this->commit();
 181                 }
 182                 protected function rollback() {
 183                         $params[0] = self::get_clause('rollback');
 184                         return $this->query(self::build_query(self::prepare_params($params)));
 185                 }
 186
 187                 // internal functions (builders, dependent builders, special builders, et al)
 188     protected static function build_query($params) {
 189                         $query = $params[0];
 190                         unset($params[0]);
 191
 192                         $query = self::build_sub_query($query, $params);
 193
 194                         // handle root references
 195                         foreach($params as $key=>$value) {
 196                                 $query = str_replace(":@{$key}", $value, $query);
 197                         }
 198
 199                         return $query;
 200                 }
 201                 protected static function build_sub_query($query, $params) {
 202                         foreach($params as $key=>$value) {
 203                                 // if it's an array, it needs to be built itself and then the contents can be directly subbed in
 204                                 if(is_array($value))
 205                                         $value = self::build_sub_query($query, $value);
 206
 207                                 if(empty($value))
 208                                         $query = str_replace(" :{$key}", '', $query);
 209                                 else {
 210                                         $query = str_replace(":{$key}", $value, $query);
 211                                 }
 212                         }
 213
 214                         // handle backreferences and parent references
 215                         $query = self::build_query_backreferences($query, $params);
 216
 217                         return $query;
 218                 }
 219                 protected static function build_query_backreferences($query, $params) {
 220                         foreach($params as $key=>$value) {
 221                                 $query = str_replace(":{$key}", $value, $query);
 222                         }
 223                         return preg_replace('/:#(#*)([\w\d_]+)/', ':$1$2', $query);
 224                 }
 225                 protected static function prepare_params($params) {
 226                         // the clauses to check against
 227                         $clauses = self::get_clauses_from($params[0]);
 228
 229                         // make sure that all values are set and the proper clauses are set
 230                         foreach($clauses as $clause) {
 231                                 if(empty($params[$clause])) { $params[$clause] = ''; continue; } // if it's not been set, give it an empty value
 232                                 // if(is_array($params[$clause]) && empty($params[0])) $params[$clause][0] = self::get_clause($clause); // otherwise, get its clause
 233                         }
 234
 235                         return $params;
 236                 }
 237
 238                 // special dependent builder functions
 239                 protected static function build_values($params) {
 240                         foreach($params as $column=>$value) {
 241                                 if($column == 'id') continue;
 242                                 $value = self::sanitize($value);
 243                                 $values .= (((!empty($values)) ? ', ' : '') . str_replace('{:1}', $column, str_replace('{:2}', $value, self::get_clause('value'))));
 244                         }
 245                         return $values;
 246                 }
 247                 protected static function build_where_query($params) {
 248                         foreach($params as $column=>$value) {
 249                                 $query .= (((!empty($query)) ? ' and ' : '') . str_replace('{:1}', $column, str_replace('{:2}', ":{$column}", self::get_clause('value'))));
 250                         }
 251                         return $query;
 252                 }
 253                 protected static function build_clause($name, $clause_data) {
 254                         $clause_template = self::get_clause($name);
 255                         if(empty($clause_template)) $clause_template = ":{$name}";
 256                         if(is_array($clause_data)) {
 257                                 $clause_form = $clause_data[0];
 258                                 unset($clause_data[0]);
 259                                 foreach($clause_data as $key=>$value) {
 260                                         $clause_form = str_replace(":{$key}", $value, $clause_form);
 261                                 }
 262                                 $clause_data = $clause_form;
 263                         }
 264                         return str_replace(":{$name}", $clause_data, $clause_template);;
 265                 }
 266                 protected static function get_clause($name) {
 267                         $class = new ReflectionClass(self::$adapter_class);
 268                         $constants = $class->getConstants();
 269                         return $constants[$name];
 270                 }
 271                 protected static function get_clauses_from($query) {
 272                         $query = explode(' ', $query);
 273
 274                         foreach($query as $clause) {
 275                                 if($clause[0] != ':') continue;
 276                                 $clauses[] = substr($clause, 1);
 277                         }
 278
 279                         return $clauses;
 280                 }
 281
 282                 // special, overridable builders
 283                 protected static function build_columns($params) {
 284                         if(!is_array($params['columns'])) return $params['columns'];
 285
 286                         $columns = $params['columns'];
 287                         foreach($columns as $column) {
 288                                 $columns_as_string = ((!empty($columns_as_string)) ? ', ' : '') . "{$params['table']}.*";
 289                         }
 290
 291                         return $columns;
 292                 }
 293                 protected static function build_join($params) {
 294                         // if(empty($params['join'][0])) $params['join'][0] = self::get_clause('join');
 295                         // if(!empty($params['join']['join'])) $params['join']['join'] = self::build_join($params['join']);
 296
 297                         if(empty($params['join'])) return $params;
 298
 299                         // handle columns
 300                         if(empty($params['columns']) || $params['columns'] == '*') $params['columns'] = array("{$params['table']}.*");
 301                         foreach(self::get_join_columns($params['join']) as $column) {
 302                                 $params['columns'][] = $column;
 303                         }
 304                         $params['columns'] = implode(', ', $params['columns']);
 305
 306                         // assemble join clauses
 307                         $params['join'] = self::add_join_clause($params['join']);
 308
 309                         return $params;
 310                 }
 311                 protected static function add_join_clause($join) {
 312                         $query = $join[0];
 313                         unset($join[0]);
 314
 315                         // get sub-join query clause if there is one available
 316                         if(empty($query)) $query = self::get_clause('join');
 317
 318                         // get the table name as singular form
 319                         $join['singular_table'] = Inflector::singularize($join['table']);
 320
 321                         if(!empty($join['join']))
 322                                 $join['join'] = self::add_join_clause($join['join']);
 323                         else
 324                                 $query = str_replace(' :join', '', str_replace(' :#join', '', $query));
 325
 326                         $query = self::build_sub_query($query, $join);
 327
 328                         return $query;
 329                 }
 330                 protected static function get_join_columns($join) {
 331                         // get nested joins' columns
 332                         if(!empty($join['join']))
 333                                 $columns = self::get_join_columns($join['join']);
 334
 335                         if(empty($join['columns'])) $join['columns'] = self::get_columns($join['table']);
 336
 337                         // get columns
 338                         if(!is_array($join['columns'])) $join['columns'] = explode(',', str_replace(' ', '', $join['columns']));
 339                         foreach($join['columns'] as $column) {
 340                                 // skip over 'id' columns (because 'id' is gotten for the parent table)
 341                                 if($column == 'id') continue;
 342                                 // associate table and columns
 343                                 $columns[] = "{$join['table']}.{$column}" . ($column == '*' ? '' : " AS {$column}");
 344                         }
 345
 346                         return $columns;
 347                 }
 348
 349                 // support functions
 350                 protected static function get_columns($table) {
 351                         return call_user_func(array(self::$adapter_class, 'columns'), $table);
 352                 }
 353
 354                 protected static function sanitize($value) {
 355                         return call_user_func(array(self::$adapter_class, 'sanitize'), $value);
 356                 }
 357
 358                 // descructor
 359                 public function __destruct() {
 360                         // possibly put some serialization code in here?
 361                         // end;
 362                 }
 363         }
 364
 365         // as soon as Row is not used, get rid of it
 366         class row {
 367                 // variables
 368                 private $row;
 369
 370                 // constructor
 371                 public function __construct($row = array()) {
 372                         $this->row = $row;
 373                 }
 374
 375                 // magic function override
 376                 public function __set($name, $value) {
 377                         $this->row[$name] = $value;
 378                 }
 379                 public function __get($name) {
 380                         return $this->row[$name];
 381                 }
 382
 383                 // functions
 384                 public function to_array() {
 385                         return $this->row;
 386                 }
 387                 // alias
 388                 public function as_array() {
 389                         return $this->to_array();
 390                 }
 391
 392                 // remove non columns (to help with associations and joins)
 393                 public function remove_non_columns($columns) {
 394                         foreach($this->row as $column=>$value) {
 395                                 if(array_search($column, $columns) === false) unset($this->row[$column]);
 396                         }
 397                 }
 398         }
 399
 400         class DBException extends StdException {}
 401         class DBQueryException extends DBException {}
 402         class DBExecutionException extends DBException {}
 403         class CouldNotConnect extends DBException {}
 404         class CouldNotSelectDB extends DBException {}
 405         class CouldNotIterate extends DBException {}
 406         class CouldNotFind extends DBException {}
 407         class CouldNotSave extends DBException {}
 408         class CouldNotDelete extends DBException {}
 409 ?>