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         {
  59             $e = new AOOSException($this->core(), $this->tr("mysql_query_fail"), $this->tr("error").": ".mysql_error()."<br />Query: ".$query, true, 0);
  60             return false;
  61         }
  62
  63         return true;
  64     }
  65
  66     public function setTable($table)
  67     {
  68         $this->_table = $table;
  69     }
  70
  71     public function setQuery($query)
  72     {
  73         return $this->_makeQuery($query);
  74     }
  75
  76     /** Inserts an array
  77      * @param string $field Field name
  78      * @param string $value Value
  79      * @return bool
  80      */
  81     public function insert($values)
  82     {
  83         $rawQuery   = "INSERT INTO %s (%s) VALUES (%s)";
  84         foreach ($values as $row) {
  85             $fields = implode(",", array_keys($row));
  86             $vals = implode(",", array_values($row));
  87             $query = sprintf($rawQuery, $this->_table, $fields, $vals);
  88
  89             if (!$this->_makeQuery($query)) {
  90                 return false;
  91             }
  92         }
  93         return true;
  94     }
  95
  96     /**
  97      * Selects a single row
  98      */
  99     public function select($fields, $where, $order, $limit)
 100     {
 101         $fields = implode(",", $fields);
 102         $raw = "SELECT %s FROM %s";
 103         $query = sprintf($raw, $fields, $this->_table);
 104
 105         if ($w = $this->_arrayToWhere($where)) {
 106             $query .= " WHERE ".$w;
 107         }
 108
 109         if ($l = $this->_arrayToLimit($limit)) {
 110             $query .= " LIMIT ".$l;
 111         }
 112
 113         if ($o = $this->_arrayToOrder($order)) {
 114             $query .= " ORDER BY ".$o;
 115         }
 116
 117         try {
 118             $this->_makeQuery($query);
 119         } catch (AOOSException $e) {
 120             throw $e;
 121         }
 122         $a = array();
 123         while ($row = mysql_fetch_assoc($this->_query)) {
 124             $a[] = $row;
 125         }
 126         mysql_free_result($this->_query);
 127         return $a;
 128     }
 129
 130     /**
 131      * Update values
 132      * @param $values
 133      * @param $where
 134      * @param $limit
 135      * @return bool
 136      */
 137     public function update($values, $where, $limit = null)
 138     {
 139         $raw = "UPDATE %s SET %s WHERE %s";
 140         if (!($v = $this->_arrayToWhere($values))) {
 141             return false;
 142         }
 143         if (!($w = $this->_arrayToWhere($where))) {
 144             return false;
 145         }
 146         if ($l = $this->_arrayToLimit($limit)) {
 147             $raw .= $l;
 148         }
 149
 150         $query = sprintf($raw, $this->_table, $v, $w);
 151
 152         return $this->_makeQuery($query);
 153     }
 154
 155     /**
 156      * Deletes a row.
 157      * @param string $where
 158      * @return bool
 159      */
 160     public function remove($where, $limit = null)
 161     {
 162         $raw = "DELETE FROM %s WHERE %s";
 163         if (!($w = $this->_arrayToWhere($where))) {
 164             return false;
 165         }
 166         if ($l = $this->_arrayToLimit($limit)) {
 167             $raw .= " LIMIT ".$l;
 168         }
 169         $query = sprintf($raw, $this->_table, $w);
 170
 171         return $this->_makeQuery($query);
 172     }
 173
 174     /** XXX
 175      * Num rows
 176      * @return int
 177      */
 178     public function numRows($where = null)
 179     {
 180         $query = sprintf("SELECT COUNT(*) FROM %s ", $this->_table);
 181         if ($w = $this->_arrayToWhere($where)) {
 182             $query .= "WHERE ".$w;
 183         }
 184         $this->_makeQuery($query);
 185         $result = mysql_fetch_array($this->_query);
 186         return $result[0];
 187     }
 188
 189     /**
 190      * Creates a table in the database
 191      */
 192     public function create($fields, $props) {
 193         $f = array();
 194         $nr = 0;
 195         $query = sprintf("CREATE TABLE IF NOT EXISTS %s (\n", $this->_table);
 196         foreach ($fields as $field => $type) {
 197             if (!($props[$field] & AOOSMODEL_PROP_FROM_DATABASE)) {
 198                 continue;
 199             }
 200             $nr++;
 201             if ($props[$field] & AOOSMODEL_PROP_UNIQUE) {
 202                 $f[] = sprintf("PRIMARY KEY (%s)", $field);
 203             }
 204
 205             $t = $field;
 206             switch ($type) {
 207             case(AOOSMODEL_TYPE_STRING):
 208                 $t .= " TEXT";
 209                 break;
 210             case(AOOSMODEL_TYPE_DATETIME):
 211             case(AOOSMODEL_TYPE_INTEGER):
 212                 $t .= " INT(11)";
 213                 if ($props[$field] & AOOSMODEL_PROP_DATA_INCREASING) {
 214                     $t .= " AUTO_INCREMENT";
 215                 }
 216                 break;
 217             case(AOOSMODEL_TYPE_EMAIL):
 218                 $t .= " VARCHAR(255)";
 219                 break;
 220             }
 221
 222             if ($props[$field] & AOOSMODEL_PROP_DATA_HASH) {
 223                 $t = $field." VARCHAR(255)";
 224             }
 225
 226             $f[] = $t;
 227         }
 228         $query .= implode(",\n", $f);
 229         $query .= " );";
 230         if ($nr == 0) {
 231             return true;
 232         }
 233         return $this->_makeQuery($query);
 234     }
 235
 236     /**
 237      * Removes a table
 238      */
 239     public function drop() {
 240         $query = sprintf("DROP TABLE IF EXISTS %s", $this->_table);
 241         return $this->_makeQuery($query);
 242     }
 243
 244     private function _arrayToWhere($array) {
 245         if (is_array($array)) {
 246             $a = array();
 247             foreach ($array as $key => $value) {
 248                 $a[] = $key."=".$value;
 249             }
 250             $str = count($a) > 1 ? implode(" AND ", $a) : $a[0];
 251             return $str;
 252         }
 253         return false;
 254     }
 255
 256     private function _arrayToLimit($a) {
 257         if (is_array($a)) {
 258             if (count($a) == 1) {
 259                 $query = sprintf("%s", $a[0]);
 260             }
 261             else {
 262                 $query = sprintf("%s", $a[0], $a[1]);
 263             }
 264             return $query;
 265         }
 266         return false;
 267     }
 268
 269     private function _arrayToOrder($a) {
 270         if (is_array($a)) {
 271             $o = sprintf("%s,%s", $a[0], $a[1]);
 272             return $o;
 273         }
 274         return false;
 275     }
 276
 277 }
 278 ?>