· 8 years ago · May 29, 2018, 08:46 PM
1<?php
2/*
3 Copyright (C) 2007 - 2009 Nicaw
4
5 This program is free software; you can redistribute it and/or modify
6 it under the terms of the GNU General Public License as published by
7 the Free Software Foundation; either version 2 of the License, or
8 (at your option) any later version.
9
10 This program 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 General Public License for more details.
14
15 You should have received a copy of the GNU General Public License along
16 with this program; if not, write to the Free Software Foundation, Inc.,
17 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
18*/
19class SQL {
20 private
21 $sql_connection,
22 $schema_version,
23 $sql_tables,
24 $last_query,
25 $last_insert_id;
26
27 //creates new connection
28 public function __construct($server, $user, $password, $database) {
29
30 //warn if MySQL extension is not installed
31 if(!extension_loaded('mysql'))
32 throw new LibraryMissingException('MySQL library is not installed. Database access is impossible.', 0);
33
34 //establish a link to MySQL
35 $con = @mysql_connect($server,$user,$password);
36 if ($con === false)
37 throw new DatabaseConnectException('Unable to connect to mysql server. Please make sure it is up and running and you have correct user/password in config.inc.php.', 1);
38
39 //select otserv database
40 if (!mysql_select_db($database))
41 throw new DatabaseSelectException('Unable to select database: '.$database.'. Make sure it exists.', 2);
42
43 //retrieve table list
44 $result = mysql_query('SHOW TABLES');
45 if ($result === false)
46 DatabaseQueryException('Failed to retrieve a table list.');
47
48 while ($a = mysql_fetch_array($result))
49 $this->sql_tables[] = $a[0];
50
51 //retrieve schema version
52 $result = mysql_query('SELECT value FROM schema_info WHERE name = \'version\'');
53 if ($result === false) {
54 $this->schema_version = false;
55 } else {
56 $a = mysql_fetch_array($result);
57 $this->schema_version = $a['value'];
58 }
59
60 //assign the connection
61 $this->sql_connection = $con;
62
63 return true;
64 }
65
66 public function getSchemaVersion() {
67 return $this->schema_version;
68 }
69
70 public function isTable($mixed) {
71 return in_array($mixed, $this->sql_tables);
72 }
73
74 public function __destruct() {
75 if(is_resource($this->last_query))
76 mysql_free_result($this->last_query);
77 mysql_close($this->sql_connection);
78 }
79
80 //Creates tables
81 public function setup() {
82 $tables = explode(';', file_get_contents('documents/shema.mysql'));
83 foreach ($tables as $table) mysql_query($table);
84 }
85
86 //Perform simple SQL query
87 public function myQuery($q) {
88 if(is_resource($this->last_query))
89 mysql_free_result($this->last_query);
90 $this->last_query = mysql_query($q, $this->sql_connection);
91 $this->last_insert_id = mysql_insert_id();
92 if ($this->last_query === false) {
93 $this->analyze();
94 throw new DatabaseQueryException('Error #'.mysql_errno().':'.mysql_error(), $q);
95 }
96 return $this->last_query;
97 }
98
99 //True is last query failed
100 public function failed() {
101 if ($this->last_query === false) return true;
102 return false;
103 }
104
105 //Returns current array with data values
106 public function fetch_array() {
107 if (!$this->failed())
108 if (isset($this->last_query))
109 return mysql_fetch_array($this->last_query);
110 else
111 throw new ClassException('Attempt to fetch a null query.');
112 else
113 throw new ClassException('Attempt to fetch failed query.');
114 }
115
116 //Returns the last insert id
117 public function insert_id() {
118 return $this->last_insert_id;
119 }
120
121 //Returns the number of rows affected
122 public function num_rows() {
123 if (!$this->failed())
124 return mysql_num_rows($this->last_query);
125 else
126 throw new ClassException('Attempt to count failed query.');
127 }
128
129 //Quotes a string
130 public function escape_string($string) {
131 return mysql_real_escape_string($string);
132 }
133
134 //Quotes a value so it's safe to use in SQL statement
135 public function quote($value) {
136 if(is_numeric($value) && $value[0] != '0')
137 return (int) $value;
138 else
139 return '\''.$this->escape_string($value).'\'';
140 }
141
142 public function analyze() {
143 //determine database type, try to perform autosetup
144 $is_aac_db = in_array('nicaw_accounts',$this->sql_tables);
145 $is_server_db = in_array('accounts',$this->sql_tables) && in_array('players',$this->sql_tables);
146 $is_svn = in_array('player_depotitems',$this->sql_tables) && in_array('groups',$this->sql_tables);
147 $is_cvs = in_array('playerstorage',$this->sql_tables) && in_array('skills',$this->sql_tables);
148 if (!$is_aac_db) {
149 $this->setup();
150 throw new DatabaseException('Notice: AutoSetup has attempted to create missing tables for you. Please create MySQL tables manually from "database.sql" if you are still getting this message.', 3);
151 }elseif (!$is_server_db) {
152 throw new DatabaseException('It appears you don\'t have SQL sample imported for OT server or it is not supported.', 4);
153 }elseif ($is_cvs && !$is_svn) {
154 throw new DatabaseException('This AAC version does not support your server. Consider using SQL v1.5.', 5);
155 }
156 return true;
157 }
158
159 public function repairTables() {
160 if (isset($this->sql_tables))
161 foreach($this->sql_tables as $table)
162 mysql_query('REPAIR TABLE '.$table);
163 return true;
164 }
165
166 ######################################
167 # Methods for simple data access #
168 ######################################
169
170 //Insert data
171 public function myInsert($table,$data) {global $cfg;
172 $fields = array_keys($data);
173 $values = array_values($data);
174 $query = 'INSERT INTO `'.mysql_escape_string($table).'` (';
175 foreach ($fields as $field)
176 $query.= '`'.mysql_escape_string($field).'`,';
177 $query = substr($query, 0, strlen($query)-1);
178 $query.= ') VALUES (';
179 foreach ($values as $value)
180 if ($value === null)
181 $query.= 'NULL,';
182 else
183 $query.= $this->quote($value).',';
184 $query = substr($query, 0, strlen($query)-1);
185 $query.= ');';
186 $this->myQuery($query);
187 return true;
188 }
189
190 //Replace data
191 public function myReplace($table,$data) {global $cfg;
192 $fields = array_keys($data);
193 $values = array_values($data);
194 $query = 'REPLACE INTO `'.mysql_escape_string($table).'` (';
195 foreach ($fields as $field)
196 $query.= '`'.mysql_escape_string($field).'`,';
197 $query = substr($query, 0, strlen($query)-1);
198 $query.= ') VALUES (';
199 foreach ($values as $value)
200 if ($value === null)
201 $query.= 'NULL,';
202 else
203 $query.= $this->quote($value).',';
204 $query = substr($query, 0, strlen($query)-1);
205 $query.= ');';
206 $this->myQuery($query);
207 return true;
208 }
209
210 //Retrieve single row
211 public function myRetrieve($table,$data) {
212 $fields = array_keys($data);
213 $values = array_values($data);
214 $query = 'SELECT * FROM `'.mysql_escape_string($table).'` WHERE (';
215 for ($i = 0; $i < count($fields); $i++)
216 $query.= '`'.mysql_escape_string($fields[$i]).'` = '.$this->quote($values[$i]).' AND ';
217 $query = substr($query, 0, strlen($query)-4);
218 $query.=');';
219 $this->myQuery($query);
220 if ($this->num_rows() != 1) return false;
221 return $this->fetch_array();
222 }
223
224 //Update data
225 public function myUpdate($table,$data,$where,$limit=1) {
226 $fields = array_keys($data);
227 $values = array_values($data);
228 $query = 'UPDATE `'.mysql_escape_string($table).'` SET ';
229 for ($i = 0; $i < count($fields); $i++)
230 $query.= '`'.mysql_escape_string($fields[$i]).'` = '.$this->quote($values[$i]).', ';
231 $query = substr($query, 0, strlen($query)-2);
232 $query.=' WHERE (';
233 $fields = array_keys($where);
234 $values = array_values($where);
235 for ($i = 0; $i < count($fields); $i++)
236 $query.= '`'.mysql_escape_string($fields[$i]).'` = '.$this->quote($values[$i]).' AND ';
237 $query = substr($query, 0, strlen($query)-4);
238 if (isset($limit))
239 $query.=') LIMIT '.$limit.';';
240 else
241 $query.=');';
242 $this->myQuery($query);
243 return true;
244 }
245
246 //Delete data
247 public function myDelete($table,$data,$limit = 1) {
248 $fields = array_keys($data);
249 $values = array_values($data);
250 $query = 'DELETE FROM `'.mysql_escape_string($table).'` WHERE (';
251 for ($i = 0; $i < count($fields); $i++)
252 $query.= '`'.mysql_escape_string($fields[$i]).'` = '.$this->quote($values[$i]).' AND ';
253 $query = substr($query, 0, strlen($query)-4);
254 if ($limit > 0)
255 $query.=') LIMIT '.$limit.';';
256 else
257 $query.=');';
258 $this->myQuery($query);
259 return true;
260 }
261}
262?>