web/public_php/api/common/db_lib.php

   1 <?php
   2 /* Copyright (C) 2009 Winch Gate Property Limited
   3  *
   4  * This file is part of ryzom_api.
   5  * ryzom_api is free software: you can redistribute it and/or modify
   6  * it under the terms of the GNU Lesser General Public License as published by
   7  * the Free Software Foundation, either version 3 of the License, or
   8  * (at your option) any later version.
   9  *
  10  * ryzom_api is distributed in the hope that it will be useful,
  11  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13  * GNU Lesser General Public License for more details.
  14  *
  15  * You should have received a copy of the GNU Lesser General Public License
  16  * along with ryzom_api.  If not, see <http://www.gnu.org/licenses/>.
  17  */
  18
  19 define('SQL_DEF_TEXT', 0);
  20 define('SQL_DEF_BOOLEAN', 1);
  21 define('SQL_DEF_INT', 2);
  22 define('SQL_DEF_DATE', 3);
  23
  24
  25 // Wrapper for SQL database interactions
  26 class ServerDatabase
  27 {
  28     var $hostname = '';
  29     var $username = '';
  30     var $password = '';
  31     var $database = '';
  32
  33     var $_connection = Null;
  34
  35     function ServerDatabase($host='', $user='', $passwd='', $dbname='')
  36     {
  37         if (($host != '') && ($user != '') && ($dbname != ''))
  38         {
  39                 $this->hostname = $host;
  40                 $this->username = $user;
  41                 $this->password = $passwd;
  42                 $this->database = $dbname;
  43         }
  44
  45                 if (($this->hostname != '') && ($this->username != '') && ($this->database != ''))
  46                         $this->_connection = new mysqli($this->hostname, $this->username, $this->password, $this->database);
  47     }
  48
  49     function close()
  50     {
  51                 $this->_connection->close();
  52     }
  53
  54     function query($sql_statement)
  55     {
  56                 $result = $this->_connection->query($sql_statement);
  57                 if (!$result)
  58                         alert('MYSQL', $this->get_error(), 2);
  59         return $result;
  60     }
  61
  62         function select_db($dbname) {
  63                 $this->database = $dbname;
  64                 $this->_connection->select_db($dbname);
  65         }
  66
  67         function num_rows($result)
  68         {
  69                 return $result->num_rows;
  70         }
  71
  72     function fetch_row($result, $result_type=MYSQLI_BOTH)
  73     {
  74                 if (gettype($result) == "object")
  75                         return $result->fetch_array($result_type);
  76                  return NULL;
  77     }
  78
  79         function fetch_assoc($result)
  80     {
  81                 if (gettype($result) == "object")
  82                         return $result->fetch_assoc();
  83                 return NULL;
  84     }
  85
  86     function query_single_row($sql_statement)
  87     {
  88         $result = $this->query($sql_statement);
  89                 if (gettype($result) == "object")
  90                         return $result->fetch_array();
  91
  92                 return NULL;
  93     }
  94
  95         function free_result($result)
  96         {
  97                 $result->free();
  98         }
  99
 100     function get_error()
 101     {
 102                 return $this->_connection->errno.': '.$this->_connection->error;
 103     }
 104
 105     function last_insert_id()
 106     {
 107                 return $this->_connection->insert_id;
 108     }
 109
 110     function escape_string($escapestr) {
 111                 return $this->_connection->real_escape_string($escapestr);
 112     }
 113
 114     function change_to($host,$user,$pass,$dbname)
 115     {
 116         /*$this->close();
 117         $this->hostname = $host;
 118         $this->username = $user;
 119         $this->password = $pass;
 120         $this->database = $dbname;
 121         $this->ServerDatabase();*/
 122     }
 123 }
 124
 125 class ryDB {
 126
 127         private static $_instances = array();
 128         private $db;
 129         private $defs = array();
 130         private $errors = '';
 131
 132
 133         private function __construct($db_name) {
 134                 global $_RYZOM_API_CONFIG;
 135                 $this->db_name = $db_name;
 136                 $this->db = new ServerDatabase(RYAPI_WEBDB_HOST, RYAPI_WEBDB_LOGIN, RYAPI_WEBDB_PASS, $db_name);
 137                 $this->db->query("SET NAMES utf8mb4");
 138         }
 139
 140         public static function getInstance($db_name) {
 141                 if (!array_key_exists($db_name, self::$_instances))
 142                         self::$_instances[$db_name] = new ryDB($db_name);
 143
 144                 self::$_instances[$db_name]->db->select_db($db_name);
 145                 return self::$_instances[$db_name];
 146         }
 147
 148         function setDbDefs($table, $defs, $check=true) {
 149                 if ($check)
 150                 {
 151                         $result = $this->db->query('SHOW FIELDS FROM '.$table);
 152                         if (!$result) {
 153                                 die("Table [$table] not found in database [$this->db_name]");
 154
 155                         }
 156
 157                         $fields = array_keys($defs);
 158                         while ($row = $this->db->fetch_row($result)) {
 159                                 if (in_array($row['Field'], $fields))
 160                                         unset($fields[array_search($row['Field'], $fields)]);
 161                                 else
 162                                         alert('DbLib', 'Missing field '.$row['Field']." on DbDef of table [$table] of database [$this->db_name] !", 2);
 163                         }
 164                         if ($fields)
 165                                 die('Missing fields ['.implode('] [', $fields)."] in table [$table] of database [$this->db_name] !");
 166                 }
 167                 $this->defs[$table] = $defs;
 168         }
 169
 170         function getDefs($table) {
 171                 if ($this->hasDbDefs($table))
 172                         return $this->defs[$table];
 173
 174                 alert('DBLIB', "Please add tables to '$this->db_name' defs using setDbDefs('$table', \$defs)", 2);
 175         }
 176
 177         function hasDbDefs($table) {
 178                 return array_key_exists($table, $this->defs);
 179         }
 180
 181         function getErrors() {
 182                 return $this->db->get_error();
 183         }
 184
 185         function now() {
 186                 return date('Y-m-d H:i:s', time());
 187         }
 188
 189         function toDate($timestamp) {
 190                 return date('Y-m-d H:i:s', $timestamp);
 191         }
 192
 193         function fromDate($string_date) {
 194                 return strtotime($string_date);
 195         }
 196
 197         function addDbTableProp($table, $props) {
 198                 $this->props[$table] = $props;
 199         }
 200
 201         function sqlEscape($escapestr) {
 202                 return $this->db->escape_string($escapestr);
 203         }
 204
 205         function insertID() {
 206                 return $this->db->last_insert_id();
 207         }
 208
 209
 210         /// DIRECT QUERY
 211         function sqlQuery($sql, $index = false, $result_type = MYSQLI_BOTH) {
 212                 $result = $this->db->query($sql);
 213                 if (!$result)
 214                         return NULL;
 215                 if($index !== false && !is_array($index)){
 216                         $index = array($index);
 217                 }
 218                 $ret = array();
 219                 while ($row = $this->db->fetch_row($result, $result_type)) {
 220                         if($index !== false) {
 221                                 // if $index is ['id1', 'id2'], then this code executes as
 222                                 // $ret[$row['id1']][$row['id2']] = $row
 223                                 $current = &$ret;
 224                                 foreach($index as $key){
 225                                         if(!isset($row[$key]))
 226                                                 alert('DBLIB', "Requested index field ($key) was not selected from db");
 227                                         $current = &$current[$row[$key]];
 228                                 }
 229                                 $current = $row;
 230                         } else
 231                                 $ret[] = $row;
 232                 }
 233                 return $ret;
 234         }
 235
 236
 237         /// QUERY ///
 238         function sqlSelect($table, $props, $values=array(), $extra='') {
 239                 if ($table) {
 240                         $sql = "SELECT\n\t";
 241                         $params = array();
 242                         $test = array();
 243                         if (!$props)
 244                                 alert('DBLIB', "Bad Select on [$table] : missing props");
 245
 246                         foreach($props as $name => $type)
 247                                 $params[] = '`'.$this->sqlEscape($name).'`';
 248
 249                         foreach($values as $name => $value) {
 250                                 if ($name[0] == '=')
 251                                         $test[] = '('.$this->sqlEscape(substr($name, 1)).' LIKE '.var_export($value, true).')';
 252                                 else
 253                                         $test[] = '('.$this->sqlEscape($name).' = '.var_export($value, true).')';
 254                         }
 255                         $sql .= implode(",\n\t", $params)."\nFROM\n\t".$table."\n";
 256                         if ($test)
 257                                 $sql .= "WHERE\n\t".implode("\nAND\n\t", $test);
 258                 }
 259
 260                 if ($extra)
 261                         $sql .= "\n".$extra;
 262                 return $sql.';';
 263
 264         }
 265
 266         function querySingle($table, $values=array(), $extra='') {
 267                 $sql = $this->sqlSelect($table, $this->getDefs($table), $values, $extra);
 268                 $result = $this->sqlQuery($sql, false, MYSQLI_BOTH);
 269                 if(empty($result))
 270                         return NULL;
 271                 return $result[0];
 272         }
 273
 274         function querySingleAssoc($table, $values=array(), $extra='') {
 275                 $sql = $this->sqlSelect($table, $this->getDefs($table), $values, $extra);
 276                 $result = $this->sqlQuery($sql, false, MYSQLI_ASSOC);
 277                 if(empty($result))
 278                         return NULL;
 279                 return $result[0];
 280         }
 281
 282         function query($table, $values=array(), $extra='', $index = false, $result_type = MYSQLI_BOTH) {
 283                 $sql = $this->sqlSelect($table, $this->getDefs($table), $values, $extra);
 284                 return $this->sqlQuery($sql, $index, $result_type);
 285         }
 286
 287         function queryAssoc($table, $values=array(), $extra='', $index = false) {
 288                 return $this->query($table, $values, $extra, $index, MYSQLI_ASSOC);
 289         }
 290
 291         function queryIndex($table, $index, $values=array(), $extra='') {
 292                 return $this->query($table, $values, $extra, $index, MYSQLI_ASSOC);
 293         }
 294
 295
 296         /// INSERT ///
 297         function sqlInsert($table, $props, $vals) {
 298                 $sql = 'INSERT INTO '.$table.' ';
 299                 $params = array();
 300                 $values = array();
 301                 foreach($props as $name => $type) {
 302                         if (!isset($vals[$name]))
 303                                 continue;
 304                         $params[] = '`'.$name.'`';
 305                         switch ($type) {
 306                                 case SQL_DEF_BOOLEAN:
 307                                         $values[] = $vals[$name]?1:0;
 308                                         break;
 309                                 case SQL_DEF_INT:
 310                                         $values[] = $vals[$name];
 311                                         break;
 312                                 case SQL_DEF_DATE: // date
 313                                         if (is_string($vals[$name]))
 314                                                 $values[] = "'".$this->sqlEscape($vals[$name])."'";
 315                                         else
 316                                                 $values[] = "'".$this->toDate($vals[$name])."'";
 317                                         break;
 318                                 default:
 319                                         $values[] = "'".$this->sqlEscape($vals[$name])."'";
 320                                         break;
 321                         }
 322                 }
 323                 $sql .= "(\n\t".implode(",\n\t", $params)."\n) VALUES (\n\t".implode(",\n\t", $values)."\n);";
 324                 return $sql;
 325         }
 326
 327         function insert($table, $values) {
 328                 $sql = $this->sqlInsert($table, $this->getDefs($table), $values);
 329                 $this->db->query($sql);
 330                 return $this->db->last_insert_id();
 331         }
 332
 333         /// DELETE ///
 334         function sqlDelete($table, $values=array(), $where='') {
 335                 $sql = "DELETE FROM\n\t".$table."\n";
 336                 $test = array();
 337                 foreach($values as $name => $value)
 338                         $test[] = '('.$this->sqlEscape($name).' = '.var_export($value, true).')';
 339
 340                 if ($test or $where)
 341                         $sql .= "WHERE\n\t";
 342                 if ($test)
 343                         $sql .= implode("\nAND\n\t", $test);
 344                 if ($where)
 345                         $sql .= "\n".$where;
 346                 return $sql.';';
 347         }
 348
 349         function delete($table, $values=array(), $where='') {
 350                 $sql = $this->sqlDelete($table, $values, $where);
 351                 $result = $this->db->query($sql);
 352                 return $result;
 353         }
 354
 355         /// UPDATE ///
 356         function sqlUpdate($table, $props, $vals, $tests, $extra) {
 357                 $sql = 'UPDATE '.$table.' SET ';
 358                 $params = array();
 359                 $test = array();
 360                 $values = array();
 361                 foreach($props as $name => $type) {
 362                         if (!array_key_exists($name, $vals))
 363                                 continue;
 364                         switch ($type) {
 365                                 case SQL_DEF_BOOLEAN:
 366                                         $values[] = '`'.$name.'` = '.($vals[$name]?'1':'0');
 367                                         break;
 368                                 case SQL_DEF_DATE:
 369                                         if (is_string($vals[$name]))
 370                                                 $values[] = '`'.$name.'` = \''.$this->sqlEscape($vals[$name]).'\'';
 371                                         else
 372                                                 $values[] = '`'.$name.'` = \''.$this->toDate($vals[$name]).'\'';
 373                                         break;
 374                                 default:
 375                                         $values[] = '`'.$name.'` = \''.$this->sqlEscape($vals[$name]).'\'';
 376                                         break;
 377                         }
 378                 }
 379                 $sql .= "\n\t".implode(",\n\t", $values)."\n";
 380
 381                 foreach($tests as $name => $value) {
 382                         $test[] = '('.$this->sqlEscape($name).' = '.var_export($value, true).')';
 383                 }
 384                 if ($test)
 385                         $sql .= "WHERE\n\t".implode("\nAND\n\t", $test);
 386
 387                 $sql .= "\n".$extra;
 388
 389                 return $sql;
 390         }
 391
 392
 393         function update($table, $values=array(), $test=array(), $extra='') {
 394                 $sql = $this->sqlUpdate($table, $this->getDefs($table), $values, $test, $extra);
 395                 $result = $this->db->query($sql);
 396                 return $result;
 397         }
 398
 399         function sqlInsertOrUpdate($table, $props, $vals) {
 400                 $sql = $this->sqlInsert($table, $props, $vals);
 401                 $sql = substr($sql, 0, strlen($sql)-1);
 402                 $params = array();
 403                 $test = array();
 404                 $values = array();
 405                 foreach($props as $prop) {
 406                         if (!array_key_exists($prop[2], $vals))
 407                         continue;
 408                         $type = $prop[0];
 409                         $check = $prop[1];
 410                         $name = $prop[2];
 411                         if ($type{0} == '#')
 412                         $type = substr($type, 1);
 413                         if (($type{0} == '>') or ($type == 'id'))
 414                         continue;
 415                         switch ($type) {
 416                         case 'trad':
 417                                 $values[] = '`'.$name."` = '".$this->sqlEscape($vals[$name])."'";
 418                                 break;
 419                         case 'textarea':
 420                         case 'string':
 421                         case 'option':
 422                                 $values[] =  '`'.$name."` = '".$this->sqlEscape($vals[$name])."'";
 423                                 break;
 424                         case 'id':
 425                         case 'int':
 426                         case 'float':
 427                                 $values[] =  '`'.$name.'` = '.$this->sqlEscape($vals[$name]);
 428                                 break;
 429                         case 'bool':
 430                                 $values[] = '`'.$name.'` = '.($vals[$name]?'1':'0');
 431                                 break;
 432                         }
 433                 }
 434                 $sql .= "\nON DUPLICATE KEY UPDATE\n\t".implode(",\n\t", $values)."\n";
 435                 return $sql;
 436         }
 437
 438         function insertOrUpdate($table, $values) {
 439                 $sql = $this->sqlInsertOrUpdate($table, $this->getDefs($table), $values);
 440                 return $result;
 441         }
 442
 443
 444         /// Display
 445         function getTableHtml($name, $params, $values, $order_by='')
 446         {
 447                 $ret = '<table cellpadding="0" cellspacing="0" width="100%">';
 448                 $tr_header = '<td align="left" height="32px">&nbsp;';
 449                 $tr_header .= implode('</td><td align="left">&nbsp;', array_keys($params)).'</td>';
 450                 $ret .= _s('t header', $tr_header);
 451                 $i = 0;
 452                 if (!$values)
 453                         return '';
 454                 $current_section = '';
 455                 foreach ($values as $rows) {
 456                         if ($order_by && $rows[$order_by] != $current_section) {
 457                                 $current_section = $rows[$order_by];
 458                                 if ($current_section != '0')
 459                                         $ret .= _s('t row ', '<td>'._s('section', $current_section).'</td>'.str_repeat('<td>'._s('section', '&nbsp;').'</td>', count($params)-1));
 460                         }
 461                         $td = '';
 462                         foreach ($params as $test => $param)
 463                                 $td .= '<td align="left" height="22px">&nbsp;'.$rows[$param].'</td>';
 464                         $ret .= _s('t row '.strval($i % 2), $td);
 465                         $i++;
 466                 }
 467                 $ret .= '</table>';
 468                 return $ret;
 469         }
 470
 471         /// Update Database Structure
 472
 473         static function updateDatabaseStruct($defs)
 474         {
 475                 if (file_exists(RYAPP_PATH.'database.versions'))
 476                         $versions = unserialize(file_get_contents(RYAPP_PATH.'database.versions'));
 477                 else
 478                         $versions = array();
 479
 480                 $c = "Updating DB Structure...\n";
 481                 foreach ($defs as $dbname => $tables) {
 482                         $db = new ServerDatabase(RYAPI_WEBDB_HOST, RYAPI_WEBDB_LOGIN, RYAPI_WEBDB_PASS, $dbname);
 483                         $db->query("SET NAMES utf8mb4");
 484                         $c .= "\n       Selected DB '$dbname'\n";
 485                         foreach ($tables as $table => $sql)
 486                         {
 487                                 $version = count($sql);
 488                                 if (array_key_exists($table, $versions))
 489                                         $diff = $version - $versions[$table];
 490                                 else {
 491                                         $versions[$table] = 0;
 492                                         $diff = $version;
 493                                 }
 494
 495                                 $c .= "         Table '$table' need v$version (current v".strval($versions[$table].') => ');
 496
 497                                 if ($diff > 0) {
 498                                         $sql_to_run = array_slice($sql, $versions[$table], $diff);
 499                                         foreach($sql_to_run as $sql_run) {
 500                                                 if ($sql_run) {
 501                                                         $c .= "Run sql... ";
 502                                                         $result = $db->query($sql_run);
 503                                                 } else
 504                                                         $c .= "KO!!!";
 505                                         }
 506                                         if ($result) {
 507                                                 $c .= "OK";
 508                                                 $versions[$table] = $version;
 509                                         }
 510                                 } else
 511                                         $c .= "OK";
 512                                 $c .= "\n";
 513                         }
 514                         $c .= "\n";
 515                         $db->close();
 516                 }
 517                 file_put_contents(RYAPP_PATH.'database.versions', serialize($versions));
 518                 return '<pre>'.$c.'<pre>';
 519         }
 520 }
 521
 522 ?>