PageRenderTime 46ms CodeModel.GetById 18ms RepoModel.GetById 0ms app.codeStats 1ms

/include/dblayer/mysql.php

https://github.com/Dratone/EveBB
PHP | 443 lines | 303 code | 120 blank | 20 comment | 50 complexity | 4be43377fb7bd0e0750e8a645fa527f9 MD5 | raw file
Possible License(s): GPL-2.0
  1. <?php
  2. /**
  3. * Copyright (C) 2008-2010 FluxBB
  4. * based on code by Rickard Andersson copyright (C) 2002-2008 PunBB
  5. * License: http://www.gnu.org/licenses/gpl.html GPL version 2 or higher
  6. */
  7. // Make sure we have built in support for MySQL
  8. if (!function_exists('mysql_connect'))
  9. exit('This PHP environment doesn\'t have MySQL support built in. MySQL support is required if you want to use a MySQL database to run this forum. Consult the PHP documentation for further assistance.');
  10. class DBLayer
  11. {
  12. var $prefix;
  13. var $link_id;
  14. var $query_result;
  15. var $saved_queries = array();
  16. var $num_queries = 0;
  17. var $error_no = false;
  18. var $error_msg = 'Unknown';
  19. var $datatype_transformations = array(
  20. '/^SERIAL$/' => 'INT(10) UNSIGNED AUTO_INCREMENT'
  21. );
  22. function DBLayer($db_host, $db_username, $db_password, $db_name, $db_prefix, $p_connect)
  23. {
  24. $this->prefix = $db_prefix;
  25. if ($p_connect)
  26. $this->link_id = @mysql_pconnect($db_host, $db_username, $db_password);
  27. else
  28. $this->link_id = @mysql_connect($db_host, $db_username, $db_password);
  29. if ($this->link_id)
  30. {
  31. if (!@mysql_select_db($db_name, $this->link_id))
  32. error('Unable to select database. MySQL reported: '.mysql_error(), __FILE__, __LINE__);
  33. }
  34. else
  35. error('Unable to connect to MySQL server. MySQL reported: '.mysql_error(), __FILE__, __LINE__);
  36. // Setup the client-server character set (UTF-8)
  37. if (!defined('FORUM_NO_SET_NAMES'))
  38. $this->set_names('utf8');
  39. return $this->link_id;
  40. }
  41. function start_transaction()
  42. {
  43. return;
  44. }
  45. function end_transaction()
  46. {
  47. return;
  48. }
  49. function query($sql, $unbuffered = false)
  50. {
  51. if (defined('PUN_SHOW_QUERIES'))
  52. $q_start = get_microtime();
  53. if ($unbuffered)
  54. $this->query_result = @mysql_unbuffered_query($sql, $this->link_id);
  55. else
  56. $this->query_result = @mysql_query($sql, $this->link_id);
  57. if ($this->query_result)
  58. {
  59. if (defined('PUN_SHOW_QUERIES'))
  60. $this->saved_queries[] = array($sql, sprintf('%.5f', get_microtime() - $q_start));
  61. ++$this->num_queries;
  62. return $this->query_result;
  63. }
  64. else
  65. {
  66. if (defined('PUN_SHOW_QUERIES'))
  67. $this->saved_queries[] = array($sql, 0);
  68. $this->error_no = @mysql_errno($this->link_id);
  69. $this->error_msg = @mysql_error($this->link_id);
  70. return false;
  71. }
  72. }
  73. /**
  74. * This function mimics the functionality for the 'ON DUPLICATE KEY UPDATE' command in MySQL5.
  75. * Since this is mysqli, we will just reconstruct the normal query.
  76. * You are still expected to pass the correct $primaryKey to reference however.
  77. */
  78. function insert_or_update($fields, $primaryKey, $table, $fields_to_update = array()) {
  79. if (is_array($primaryKey)) {
  80. if (count($primaryKey) == 0) {
  81. $this->error_msg = 'No Primary keys passed.';
  82. return false;
  83. } //End if.
  84. foreach ($primaryKey as $key) {
  85. if (!isset($fields[$key])) {
  86. $this->error_msg = '[0] Primary key value missing.';
  87. return false;
  88. } //End if.
  89. } //End foreach().
  90. } else {
  91. if (!isset($fields[$primaryKey])) {
  92. $this->error_msg = '[1] Primary key value missing.';
  93. return false;
  94. } //End if.
  95. } //End if - else.
  96. $table_fields = $table."(";
  97. $insert_fields = "VALUES(";
  98. $update_fields = "";
  99. //Do a full update?
  100. if (empty($fields_to_update)) {
  101. foreach($fields as $key => $value) {
  102. $update_fields .= "`".$key."`=".(is_string($value) ? "'".$value."'": $value).",";
  103. $insert_fields .= (is_string($value) ? "'".$value."'": $value).",";
  104. $table_fields .= '`'.$key.'`,';
  105. } //End foreach().
  106. } else {
  107. foreach($fields_to_update as $key => $value) {
  108. $update_fields .= "`".$key."`=".(is_string($value) ? "'".$value."'": $value).",";
  109. } //End foreach().
  110. foreach($fields as $key => $value) {
  111. $update_fields .= "`".$key."`=".(is_string($value) ? "'".$value."'": $value).",";
  112. $insert_fields .= (is_string($value) ? "'".$value."'": $value).",";
  113. $table_fields .= '`'.$key.'`,';
  114. } //End foreach().
  115. } //End if - else.
  116. //Lob off the last character, which should be the comma.
  117. $update_fields = substr($update_fields, 0, -1);
  118. $insert_fields = substr($insert_fields, 0, -1);
  119. $table_fields = substr($table_fields, 0, -1);
  120. $table_fields .= ")";
  121. $insert_fields .= ")";
  122. $sql = "INSERT INTO ".$table_fields." ".$insert_fields." ON DUPLICATE KEY UPDATE ".$update_fields.";";
  123. return $this->query($sql);
  124. } //End insert_or_update().
  125. function result($query_id = 0, $row = 0, $col = 0)
  126. {
  127. return ($query_id) ? @mysql_result($query_id, $row, $col) : false;
  128. }
  129. function fetch_assoc($query_id = 0)
  130. {
  131. $result = ($query_id) ? @mysql_fetch_assoc($query_id) : false;
  132. if (is_array($result)) {
  133. return array_change_key_case($result, CASE_LOWER);
  134. } //End if.
  135. return $result;
  136. }
  137. function fetch_row($query_id = 0)
  138. {
  139. return ($query_id) ? @mysql_fetch_row($query_id) : false;
  140. }
  141. function num_rows($query_id = 0)
  142. {
  143. return ($query_id) ? @mysql_num_rows($query_id) : false;
  144. }
  145. function affected_rows()
  146. {
  147. return ($this->link_id) ? @mysql_affected_rows($this->link_id) : false;
  148. }
  149. function insert_id()
  150. {
  151. return ($this->link_id) ? @mysql_insert_id($this->link_id) : false;
  152. }
  153. function get_num_queries()
  154. {
  155. return $this->num_queries;
  156. }
  157. function get_saved_queries()
  158. {
  159. return $this->saved_queries;
  160. }
  161. function free_result($query_id = false)
  162. {
  163. return ($query_id) ? @mysql_free_result($query_id) : false;
  164. }
  165. function escape($str)
  166. {
  167. if (is_array($str))
  168. return '';
  169. else if (function_exists('mysql_real_escape_string'))
  170. return mysql_real_escape_string($str, $this->link_id);
  171. else
  172. return mysql_escape_string($str);
  173. }
  174. function error()
  175. {
  176. $result['error_sql'] = @current(@end($this->saved_queries));
  177. $result['error_no'] = $this->error_no;
  178. $result['error_msg'] = $this->error_msg;
  179. return $result;
  180. }
  181. function close()
  182. {
  183. if ($this->link_id)
  184. {
  185. if ($this->query_result)
  186. @mysql_free_result($this->query_result);
  187. return @mysql_close($this->link_id);
  188. }
  189. else
  190. return false;
  191. }
  192. function get_names()
  193. {
  194. $result = $this->query('SHOW VARIABLES LIKE \'character_set_connection\'');
  195. return $this->result($result, 0, 1);
  196. }
  197. function set_names($names)
  198. {
  199. return $this->query('SET NAMES \''.$this->escape($names).'\'');
  200. }
  201. function get_version()
  202. {
  203. $result = $this->query('SELECT VERSION()');
  204. return array(
  205. 'name' => 'MySQL Standard',
  206. 'version' => preg_replace('/^([^-]+).*$/', '\\1', $this->result($result))
  207. );
  208. }
  209. function table_exists($table_name, $no_prefix = false)
  210. {
  211. $result = $this->query('SHOW TABLES LIKE \''.($no_prefix ? '' : $this->prefix).$this->escape($table_name).'\'');
  212. return $this->num_rows($result) > 0;
  213. }
  214. function field_exists($table_name, $field_name, $no_prefix = false)
  215. {
  216. $result = $this->query('SHOW COLUMNS FROM '.($no_prefix ? '' : $this->prefix).$table_name.' LIKE \''.$this->escape($field_name).'\'');
  217. return $this->num_rows($result) > 0;
  218. }
  219. function index_exists($table_name, $index_name, $no_prefix = false)
  220. {
  221. $exists = false;
  222. $result = $this->query('SHOW INDEX FROM '.($no_prefix ? '' : $this->prefix).$table_name);
  223. while ($cur_index = $this->fetch_assoc($result))
  224. {
  225. if (strtolower($cur_index['key_name']) == strtolower(($no_prefix ? '' : $this->prefix).$table_name.'_'.$index_name))
  226. {
  227. $exists = true;
  228. break;
  229. }
  230. }
  231. return $exists;
  232. }
  233. function rename_table($old_table, $new_table, $no_prefix = false)
  234. {
  235. // If there new table exists and the old one doesn't, then we're happy
  236. if ($this->table_exists($new_table, $no_prefix) && !$this->table_exists($old_table, $no_prefix))
  237. return true;
  238. return $this->query('ALTER TABLE '.($no_prefix ? '' : $this->prefix).$old_table.' RENAME TO '.($no_prefix ? '' : $this->prefix).$new_table) ? true : false;
  239. }
  240. function create_table($table_name, $schema, $no_prefix = false)
  241. {
  242. if ($this->table_exists($table_name, $no_prefix))
  243. return true;
  244. $query = 'CREATE TABLE '.($no_prefix ? '' : $this->prefix).$table_name." (\n";
  245. // Go through every schema element and add it to the query
  246. foreach ($schema['FIELDS'] as $field_name => $field_data)
  247. {
  248. $field_data['datatype'] = preg_replace(array_keys($this->datatype_transformations), array_values($this->datatype_transformations), $field_data['datatype']);
  249. $query .= $field_name.' '.$field_data['datatype'];
  250. if (isset($field_data['collation']))
  251. $query .= 'CHARACTER SET utf8 COLLATE utf8_'.$field_data['collation'];
  252. if (!$field_data['allow_null'])
  253. $query .= ' NOT NULL';
  254. if (isset($field_data['default']))
  255. $query .= ' DEFAULT '.$field_data['default'];
  256. $query .= ",\n";
  257. }
  258. // If we have a primary key, add it
  259. if (isset($schema['PRIMARY KEY']))
  260. $query .= 'PRIMARY KEY ('.implode(',', $schema['PRIMARY KEY']).'),'."\n";
  261. // Add unique keys
  262. if (isset($schema['UNIQUE KEYS']))
  263. {
  264. foreach ($schema['UNIQUE KEYS'] as $key_name => $key_fields)
  265. $query .= 'UNIQUE KEY '.($no_prefix ? '' : $this->prefix).$table_name.'_'.$key_name.'('.implode(',', $key_fields).'),'."\n";
  266. }
  267. // Add indexes
  268. if (isset($schema['INDEXES']))
  269. {
  270. foreach ($schema['INDEXES'] as $index_name => $index_fields)
  271. $query .= 'KEY '.($no_prefix ? '' : $this->prefix).$table_name.'_'.$index_name.'('.implode(',', $index_fields).'),'."\n";
  272. }
  273. // We remove the last two characters (a newline and a comma) and add on the ending
  274. $query = substr($query, 0, strlen($query) - 2)."\n".') ENGINE = '.(isset($schema['ENGINE']) ? $schema['ENGINE'] : 'MyISAM').' CHARACTER SET utf8';
  275. return $this->query($query) ? true : false;
  276. }
  277. function drop_table($table_name, $no_prefix = false)
  278. {
  279. if (!$this->table_exists($table_name, $no_prefix))
  280. return true;
  281. return $this->query('DROP TABLE '.($no_prefix ? '' : $this->prefix).$table_name) ? true : false;
  282. }
  283. function add_field($table_name, $field_name, $field_type, $allow_null, $default_value = null, $after_field = null, $no_prefix = false)
  284. {
  285. if ($this->field_exists($table_name, $field_name, $no_prefix))
  286. return true;
  287. $field_type = preg_replace(array_keys($this->datatype_transformations), array_values($this->datatype_transformations), $field_type);
  288. if ($default_value !== null && !is_int($default_value) && !is_float($default_value))
  289. $default_value = '\''.$this->escape($default_value).'\'';
  290. return $this->query('ALTER TABLE '.($no_prefix ? '' : $this->prefix).$table_name.' ADD '.$field_name.' '.$field_type.($allow_null ? ' ' : ' NOT NULL').($default_value !== null ? ' DEFAULT '.$default_value : ' ').($after_field != null ? ' AFTER '.$after_field : '')) ? true : false;
  291. }
  292. function alter_field($table_name, $field_name, $field_type, $allow_null, $default_value = null, $after_field = null, $no_prefix = false)
  293. {
  294. if (!$this->field_exists($table_name, $field_name, $no_prefix))
  295. return true;
  296. $field_type = preg_replace(array_keys($this->datatype_transformations), array_values($this->datatype_transformations), $field_type);
  297. if ($default_value !== null && !is_int($default_value) && !is_float($default_value))
  298. $default_value = '\''.$this->escape($default_value).'\'';
  299. return $this->query('ALTER TABLE '.($no_prefix ? '' : $this->prefix).$table_name.' MODIFY '.$field_name.' '.$field_type.($allow_null ? ' ' : ' NOT NULL').($default_value !== null ? ' DEFAULT '.$default_value : ' ').($after_field != null ? ' AFTER '.$after_field : '')) ? true : false;
  300. }
  301. function drop_field($table_name, $field_name, $no_prefix = false)
  302. {
  303. if (!$this->field_exists($table_name, $field_name, $no_prefix))
  304. return true;
  305. return $this->query('ALTER TABLE '.($no_prefix ? '' : $this->prefix).$table_name.' DROP '.$field_name) ? true : false;
  306. }
  307. function add_index($table_name, $index_name, $index_fields, $unique = false, $no_prefix = false)
  308. {
  309. if ($this->index_exists($table_name, $index_name, $no_prefix))
  310. return true;
  311. return $this->query('ALTER TABLE '.($no_prefix ? '' : $this->prefix).$table_name.' ADD '.($unique ? 'UNIQUE ' : '').'INDEX '.($no_prefix ? '' : $this->prefix).$table_name.'_'.$index_name.' ('.implode(',', $index_fields).')') ? true : false;
  312. }
  313. function drop_index($table_name, $index_name, $no_prefix = false)
  314. {
  315. if (!$this->index_exists($table_name, $index_name, $no_prefix))
  316. return true;
  317. return $this->query('ALTER TABLE '.($no_prefix ? '' : $this->prefix).$table_name.' DROP INDEX '.($no_prefix ? '' : $this->prefix).$table_name.'_'.$index_name) ? true : false;
  318. }
  319. function truncate_table($table_name, $no_prefix = false)
  320. {
  321. return $this->query('TRUNCATE TABLE '.($no_prefix ? '' : $this->prefix).$table_name) ? true : false;
  322. }
  323. }