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             throw new AOOSException($this->core(), $this->tr("mysql_query_fail"), $this->tr("error").": ".mysql_error()."<br />Query: ".$query);
  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         if (!$this->_makeQuery($query)) {
 118             return false;
 119         }
 120         $a = array();
 121         while ($row = mysql_fetch_assoc($this->_query)) {
 122             $a[] = $row;
 123         }
 124         mysql_free_result($this->_query);
 125         return $a;
 126     }
 127
 128     /**
 129      * Update values
 130      * @param $values
 131      * @param $where
 132      * @param $limit
 133      * @return bool
 134      */
 135     public function update($values, $where, $limit = null)
 136     {
 137         $raw = "UPDATE %s SET %s WHERE %s";
 138         if (!($v = $this->_arrayToWhere($values))) {
 139             return false;
 140         }
 141         if (!($w = $this->_arrayToWhere($where))) {
 142             return false;
 143         }
 144         if ($l = $this->_arrayToLimit($limit)) {
 145             $raw .= $l;
 146         }
 147
 148         $query = sprintf($raw, $this->_table, $v, $w);
 149
 150         return $this->_makeQuery($query);
 151     }
 152
 153     /**
 154      * Deletes a row.
 155      * @param string $where
 156      * @return bool
 157      */
 158     public function remove($where, $limit = null)
 159     {
 160         $raw = "DELETE FROM %s WHERE %s";
 161         if (!($w = $this->_arrayToWhere($where))) {
 162             return false;
 163         }
 164         if ($l = $this->_arrayToLimit($limit)) {
 165             $raw .= " LIMIT ".$l;
 166         }
 167         $query = sprintf($raw, $this->_table, $w);
 168
 169         return $this->_makeQuery($query);
 170     }
 171
 172     /** XXX
 173      * Num rows
 174      * @return int
 175      */
 176     public function numRows($where = null)
 177     {
 178         $query = sprintf("SELECT COUNT(*) FROM %s ", $this->_table);
 179         if ($w = $this->_arrayToWhere($where)) {
 180             $query .= "WHERE ".$w;
 181         }
 182         $this->_makeQuery($query);
 183         $result = mysql_fetch_array($this->_query);
 184         return $result[0];
 185     }
 186
 187     /**
 188      * Creates a table in the database
 189      */
 190     public function create($fields, $props) {
 191         $f = array();
 192         $query = sprintf("CREATE TABLE IF NOT EXISTS %s (\n", $this->_table);
 193         foreach ($fields as $field => $type) {
 194             if ($props[$field] & AOOSMODEL_PROP_UNIQUE) {
 195                 $f[] = sprintf("PRIMARY KEY (%s)", $field);
 196             }
 197
 198             $t = $field;
 199             switch ($type) {
 200             case(AOOSMODEL_TYPE_STRING):
 201                 $t .= " TEXT";
 202                 break;
 203             case(AOOSMODEL_TYPE_INTEGER):
 204                 $t .= " INT(11)";
 205                 if ($props[$field] & AOOSMODEL_PROP_DATA_INCREASING) {
 206                     $t .= " AUTO_INCREMENT";
 207                 }
 208                 break;
 209             case(AOOSMODEL_TYPE_EMAIL):
 210                 $t .= " VARCHAR(255)";
 211                 break;
 212             }
 213
 214             if ($props[$field] & AOOSMODEL_PROP_DATA_HASH) {
 215                 $t = $field." VARCHAR(255)";
 216             }
 217
 218             $f[] = $t;
 219         }
 220         $query .= implode(",\n", $f);
 221         $query .= " );";
 222         print nl2br($query);
 223         return $this->_makeQuery($query);
 224     }
 225
 226     private function _arrayToWhere($array) {
 227         if (is_array($array)) {
 228             $a = array();
 229             foreach ($array as $key => $value) {
 230                 $a[] = $key."=".$value;
 231             }
 232             $str = count($a) > 1 ? implode(" AND ", $a) : $a[0];
 233             return $str;
 234         }
 235         return false;
 236     }
 237
 238     private function _arrayToLimit($a) {
 239         if (is_array($a)) {
 240             if (count($a) == 1) {
 241                 $query = sprintf("%s", $a[0]);
 242             }
 243             else {
 244                 $query = sprintf("%s", $a[0], $a[1]);
 245             }
 246             return $query;
 247         }
 248         return false;
 249     }
 250
 251     private function _arrayToOrder($a) {
 252         if (is_array($a)) {
 253             $o = sprintf("%s,%s", $a[0], $a[1]);
 254             return $o;
 255         }
 256         return false;
 257     }
 258
 259 }
 260 ?>