lib/AOOSMysqlInterface.php

   1 <?php
   2 require_once("AOOSException.php");
   3 require_once("AOOSModule.php");
   4 require_once("AOOSInterfaces.php");
   5
   6 /**
   7  * The mysql-part of AOOSStorageDevice
   8  * @author Sebastian Skejø
   9  */
  10 class AOOSMysqlInterface extends AOOSModule implements AOOSStorageInterface
  11 {
  12     private $_connection    = null;
  13     private $_username      = null;
  14     private $_password      = null;
  15     private $_database      = null;
  16     private $_host          = null;
  17     private $_table         = null;
  18     private $_query         = null;
  19     private $_queryText     = null;
  20
  21     public function __construct($core)
  22     {
  23         parent::__construct($core);
  24
  25         try {
  26             $this->_username    = $core->getSetting("DBUsername");
  27             $this->_password    = $core->getSetting("DBPassword");
  28             $this->_database    = $core->getSetting("DBDatabase");
  29             $this->_host        = $core->getSetting("DBHost");
  30         }
  31         catch (AOOSException $e)
  32         {
  33         }
  34
  35         $this->_connect();
  36     }
  37
  38     public function dataModelDefinition() {
  39         return 0;
  40     }
  41
  42     private function _connect() {
  43         if (!$this->_connection = mysql_connect($this->_host, $this->_username, $this->_password))
  44             throw new AOOSException($this->core(), $this->tr("mysql_connect_fail"), $this->tr("error").": ".mysql_error());
  45         if (!mysql_select_db($this->_database))
  46             throw new AOOSException($this->core(), $this->tr("mysql_select_db_fail"), $this->tr("error").": ".mysql_error());
  47     }
  48
  49     public function __wakeup() {
  50         $this->_connect();
  51         $this->_queryText = null;
  52     }
  53
  54     private function _makeQuery($query)
  55     {
  56         $this->_queryText = $query;
  57         if (!($this->_query = mysql_query($this->_queryText))) {
  58             $e = new AOOSException($this->core(), $this->tr("mysql_query_fail"), $this->tr("error").": ".mysql_error()."<br />Query: ".$query, true, 0);
  59             return false;
  60         }
  61
  62         return true;
  63     }
  64
  65     public function setTable($table)
  66     {
  67         $this->_table = $table;
  68     }
  69
  70     public function setQuery($query)
  71     {
  72         return $this->_makeQuery($query);
  73     }
  74
  75     /** Inserts an array
  76      * @param string $field Field name
  77      * @param string $value Value
  78      * @return bool
  79      */
  80     public function insert($values)
  81     {
  82         $rawQuery   = "INSERT INTO %s (%s) VALUES (%s)";
  83         $fields = array();
  84         $vals = array();
  85         foreach ($values as $key => $val) {
  86             if ($val && $val !== "''") {
  87                 $fields[] = $key;
  88                 $vals[] = $val;
  89             }
  90         }
  91         $fields = implode(",", $fields);
  92         $vals = implode(",", $vals);
  93         $query = sprintf($rawQuery, $this->_table, $fields, $vals);
  94
  95         if (!$this->_makeQuery($query)) {
  96             return false;
  97         }
  98         return true;
  99     }
 100
 101     /**
 102      * Selects a single row
 103      */
 104     public function select($fields, $where, $order, $limit)
 105     {
 106         $fields = implode(",", $fields);
 107         $raw = "SELECT %s FROM %s";
 108         $query = sprintf($raw, $fields, $this->_table);
 109
 110         if ($w = $this->_arrayToWhere($where)) {
 111             $query .= " WHERE ".$w;
 112         }
 113
 114         if ($l = $this->_arrayToLimit($limit)) {
 115             $query .= " LIMIT ".$l;
 116         }
 117
 118         if ($o = $this->_arrayToOrder($order)) {
 119             $query .= " ORDER BY ".$o;
 120         }
 121
 122         try {
 123             $this->_makeQuery($query);
 124         } catch (AOOSException $e) {
 125             throw $e;
 126         }
 127         $a = array();
 128         while ($row = mysql_fetch_assoc($this->_query)) {
 129             $a[] = $row;
 130         }
 131         mysql_free_result($this->_query);
 132         return $a;
 133     }
 134
 135     /**
 136      * Update values
 137      * @param $values
 138      * @param $where
 139      * @param $limit
 140      * @return bool
 141      */
 142     public function update($values, $where, $limit = null)
 143     {
 144         $raw = "UPDATE %s SET %s WHERE %s";
 145         if (!($v = $this->_arrayToWhere($values))) {
 146             return false;
 147         }
 148         $v = str_replace("AND", ",", $v);
 149         if (!($w = $this->_arrayToWhere($where))) {
 150             return false;
 151         }
 152         if ($l = $this->_arrayToLimit($limit)) {
 153             $raw .= $l;
 154         }
 155
 156         $query = sprintf($raw, $this->_table, $v, $w);
 157         return $this->_makeQuery($query);
 158     }
 159
 160     /**
 161      * Deletes a row.
 162      * @param string $where
 163      * @return bool
 164      */
 165     public function remove($where, $limit = null)
 166     {
 167         $raw = "DELETE FROM %s WHERE %s";
 168         if (!($w = $this->_arrayToWhere($where))) {
 169             return false;
 170         }
 171         if ($l = $this->_arrayToLimit($limit)) {
 172             $raw .= " LIMIT ".$l;
 173         }
 174         $query = sprintf($raw, $this->_table, $w);
 175
 176         return $this->_makeQuery($query);
 177     }
 178
 179     /** XXX
 180      * Num rows
 181      * @return int
 182      */
 183     public function numRows($where = null)
 184     {
 185         $query = sprintf("SELECT COUNT(*) FROM %s ", $this->_table);
 186         if ($w = $this->_arrayToWhere($where)) {
 187             $query .= "WHERE ".$w;
 188         }
 189         $this->_makeQuery($query);
 190         $result = mysql_fetch_array($this->_query);
 191         return $result[0];
 192     }
 193
 194     /**
 195      * Creates a table in the database
 196      */
 197     public function create($fields, $flags) {
 198         $f = array();
 199         $nr = 0;
 200         $query = sprintf("CREATE TABLE IF NOT EXISTS %s (\n", $this->_table);
 201         foreach ($fields as $field => $type) {
 202             $nr++;
 203             if ($flags[$field] & AOOSMODEL_FLAG_PRIMARY_KEY) {
 204                 $f[] = sprintf("PRIMARY KEY (%s)", $field);
 205             }
 206
 207             $t = $field;
 208             switch ($type) {
 209             case(AOOSMODEL_TYPE_STRING):
 210                 $t .= " VARCHAR(255)";
 211                 break;
 212             case(AOOSMODEL_TYPE_INTEGER):
 213                 $t .= " INT(11)";
 214                 break;
 215             case(AOOSMODEL_TYPE_TEXT):
 216                 $t .= " TEXT";
 217                 break;
 218             case(AOOSMODEL_TYPE_BOOLEAN):
 219                 $t .= " BOOL";
 220                 break;
 221             }
 222
 223             $f[] = $t;
 224         }
 225         $query .= implode(",\n", $f);
 226         $query .= " );";
 227         if ($nr == 0) {
 228             return true;
 229         }
 230         return $this->_makeQuery($query);
 231     }
 232
 233     /**
 234      * Removes a table
 235      */
 236     public function drop() {
 237         $query = sprintf("DROP TABLE IF EXISTS %s", $this->_table);
 238         return $this->_makeQuery($query);
 239     }
 240
 241     private function _arrayToWhere($array) {
 242         if (is_array($array)) {
 243             $a = array();
 244             foreach ($array as $key => $value) {
 245                 if ($value && $value != "''") {
 246                     $a[] = $key."=".$value;
 247                 }
 248             }
 249             $str = count($a) > 1 ? implode(" AND ", $a) : $a[0];
 250             return $str;
 251         }
 252         return false;
 253     }
 254
 255     private function _arrayToLimit($a) {
 256         if (is_array($a)) {
 257             if (count($a) == 1) {
 258                 $query = sprintf("%s", $a[0]);
 259             }
 260             else {
 261                 $query = sprintf("%s", $a[0], $a[1]);
 262             }
 263             return $query;
 264         }
 265         return false;
 266     }
 267
 268     private function _arrayToOrder($a) {
 269         if (is_array($a)) {
 270             $o = sprintf("%s %s", $a[0], $a[1]);
 271             return $o;
 272         }
 273         return false;
 274     }
 275
 276 }
 277 ?>