YaWK  24.1
Yet another WebKit
db.php
Go to the documentation of this file.
1 <?php
2 namespace YAWK
3 {
4  use Exception;
5  /**
6  * @details <b>Database class - connect to mysqli and return connection object</b>
7  * <p>This class establish the database connection if none already exists.
8  * It serves some handy methods as well, like quote data, get and delete tables,
9  * import .sql files, as well as typical select and query methods. </p>
10  *
11  * @author Daniel Retzl <[email protected]>
12  * @copyright 2012-2018 Daniel Retzl yawk.io
13  * @license https://opensource.org/licenses/MIT
14  * @version 1.0.0
15  * @brief Mysqli database class; returns db connection object
16  */
17  class db {
18 
19  /** @param array $config mysql configuration (host, username, database etc...) */
20  public $config;
21  /** @param object $connection holds the mysqli database connection */
22  public $connection;
23 
24  /**
25  * db constructor - Include the database config file
26  */
27  public function __construct()
28  { // include config array
29  $fullPathToConfigFile = __dir__.'/dbconfig.php';
30  if (!is_file($fullPathToConfigFile))
31  { // db config file not found.
32  die('The Database configuration file is missing. It has been created during the installation process, but it is not reachable.');
33  }
34  else
35  { // include config file
36  require_once ($fullPathToConfigFile);
37  }
38  }
39 
40  /**
41  * @brief Connect to the database
42  * @return object|bool false on failure / mysqli MySQLi object instance on success
43  * @throws Exception
44  */
45  public function connect(): object|bool
46  {
47  // if connection is not set
48  if (!isset($this->connection))
49  {
50  try {
51  // create new database object
52  $this->connection = new \mysqli(
53  $this->config['server'],
54  $this->config['username'],
55  $this->config['password'],
56  $this->config['dbname'],
57  $this->config['port']
58  );
59  // connection established successfully
60  return $this->connection;
61  } catch (\mysqli_sql_exception $e) {
62  // failed to connect to database (wrong credentials?)
63  throw new Exception('Failed to connect to database: ' . $e->getMessage());
64  } catch (Exception $e) {
65  throw new Exception('Database connection error: ' . $e->getMessage());
66  }
67  }
68  else
69  {
70  // connection already established...
71  return $this->connection;
72  }
73  }
74 
75  public function close(): void
76  {
77  $this->connection->close();
78  }
79 
80  /**
81  * @throws Exception
82  */
83  public function beginTransaction(): void
84  {
85  $this->connection->begin_transaction();
86  }
87 
88  public function multi_query($query)
89  { // query database
90  return $this->connection->multi_query($query);
91  }
92 
93  public function prepare($sql)
94  { // prepare statement
95  return $this->connection->prepare($sql);
96  }
97 
98  public function commit(): void
99  { // commit transaction
100  $this->connection->commit();
101  }
102 
103  public function real_escape_string($migrationSql){
104  return $this->connection->real_escape_string($migrationSql);
105  }
106 
107  /**
108  * Rollback the current transaction
109  *
110  * @return bool true if rollback succeeded, false otherwise
111  */
112  public function rollback()
113  {
114  try
115  {
116  while ($this->connection->more_results())
117  {
118  $this->connection->next_result();
119  }
120 
121  // Rollback the transaction
122  $result = $this->connection->rollback();
123 
124  if ($result)
125  { // Rollback succeeded
126  return true;
127  }
128 
129  }
130  catch (\Exception $e)
131  {
132  // An exception was thrown, so rollback failed
133  return false;
134  }
135  return false;
136  }
137 
138  /**
139  * @brief Move to next result set of a multi query
140  * @return bool
141  */
142  public function next_result()
143  {
144  if (method_exists($this->connection, 'next_result'))
145  {
146 // sys::setSyslog($this->connection, 53, 0, "next result called", 0, 0, 0, 0);
147  return $this->connection->next_result();
148  }
149  else
150  {
151  // Free any active result sets
152  while ($this->more_results() && $this->connection->next_result()) {
153 // sys::setSyslog($this->connection, 53, 0, "free any active result sets", 0, 0, 0, 0);
154  }
155  return true;
156  }
157  }
158 
159  /**
160  * @brief Checks if there are more query results from a multi query
161  * @return bool
162  */
163  public function more_results(): bool
164  {
165  if (method_exists($this->connection, 'more_results'))
166  {
167 // sys::setSyslog($this->connection, 53, 0, "more migration results available", 0, 0, 0, 0);
168  return $this->connection->more_results() && $this->connection->next_result() && $this->connection->store_result();
169  }
170  else
171  {
172  return false;
173  }
174  }
175 
176  /**
177  * Clear all pending result sets after a multi-query
178  * @return bool true if successful, false otherwise
179  */
180  public function clearResults()
181  {
182  try
183  {
184  // Check if there are more result sets available
185  while ($this->connection->more_results() && $this->connection->next_result())
186  {
187  // Store the current result set
188  $result = $this->connection->store_result();
189 
190  // Free the current result set
191  if ($result !== false)
192  {
193  $result->free();
194  }
195  }
196 
197  return true;
198  }
199  catch (\Exception $e)
200  {
201  error_log("Error clearing result sets: " . $e->getMessage());
202  return false;
203  }
204  }
205 
206 
207 
208 
209  /**
210  * @brief Execute any sql query
211  * @param string $query the sql query string
212  * @return mixed The mysqli result
213  */
214  public function query($query)
215  {
216  // connect to database
217  if ($this->connection = $this->connect())
218  {
219  // if connection is successful
220 
221  // replace {} in str to add table prefix
222  $query = str_replace("}", "", $query);
223  $query = str_replace("{", $this->config['prefix'], $query);
224 
225  // query database
226  return $this->connection->query($query);
227  }
228  else
229  { // could not connect to database, exit with error
230  die ('Database error: '.mysqli_connect_error().'('.mysqli_connect_errno().')');
231  }
232  }
233 
234  /**
235  * @brief Fetch rows from database (SELECT query)
236  * @param string $query the sql query string
237  * @return array|bool on failure
238  */
239  public function select($query)
240  {
241  // init result rows array
242  $rows = array();
243  // query database
244  $result = $this->query($query);
245  // check if result is false
246  if ($result === false)
247  {
248  // $error = $this->error();
249  //\YAWK\sys::setSyslog($db, 5, "$error", 0, 0, 0, 0);
250  return false;
251  }
252  else
253  { // fetch associative result
254  while ($row = $result->fetch_assoc())
255  { // fill array
256  $rows[] = $row;
257  }
258  }
259  // check if result rows array is set
260  if (isset($rows) && (!empty($rows) && (is_array($rows))))
261  { // ok, return result rows
262  return $rows;
263  }
264  else
265  { // no result set
266  return false;
267  }
268  }
269 
270  /**
271  * @brief Fetch last error from the database
272  * @return string database error
273  */
274  public function error()
275  {
276  // connect to database
277  $this->connection = $this->connect();
278  // return latest error
279  return $this->connection->error;
280  }
281 
282  /**
283  * @brief Quote and escape value for use in a database query *
284  * @param string $value the value to be quoted and escaped
285  * @return string the quoted and escaped string
286  */
287  public function quote($value)
288  {
289  // connect to database
290  $this->connection = $this->connect();
291  // escape and return string
292  return $this->connection->real_escape_string($value);
293  }
294 
295  /**
296  * @brief Import data from .sql file into database
297  * @param $sqlfile
298  * @param $lang
299  * @return bool
300  */
301  public function import($sqlfile, $lang)
302  {
303  // http://stackoverflow.com/questions/19751354/how-to-import-sql-file-in-mysql-database-using-php
304  if (!isset($sqlfile) || (empty($sqlfile)))
305  {
306  $filename = 'yawk_database.sql';
307  }
308  else
309  {
310  $filename = $sqlfile;
311  }
312  // filename
313  // $maxRuntime = 8; // less then your max script execution limit
314  // $maxRuntime = 8; // less then your max script execution limit
315 
316  // $deadline = time()+$maxRuntime;
317  $progressFilename = $filename.'_filepointer'; // tmp file for progress
318  $errorFilename = $filename.'_error'; // tmp file for erro
319 
320  ($fp = fopen($filename, 'r')) OR die('failed to open file:'.$filename);
321 
322  // check for previous error
323  if(file_exists($errorFilename) )
324  {
325  // die('<pre> previous error: '.file_get_contents($errorFilename));
326  }
327 
328  // go to previous file position
329  $filePosition = 0;
330  if(file_exists($progressFilename))
331  {
332  $filePosition = file_get_contents($progressFilename);
333  fseek($fp, $filePosition);
334  }
335 
336  $queryCount = 0;
337  $query = '';
338  while($line=fgets($fp, 1024000))
339  {
340  if(substr($line,0,2)=='--' OR trim($line)=='' )
341  {
342  continue;
343  }
344 
345  $query .= $line;
346  if( substr(trim($query),-1)==';' )
347  {
348  if(!$this->query($query))
349  { // error handling
350  $error = 'Error performing query \'<strong>' . $query . '\': ' . @mysqli_error($this);
351  @file_put_contents($errorFilename, $error."\n");
352  //exit;
353  }
354  $query = '';
355  @file_put_contents($progressFilename, ftell($fp)); // save the current file position for
356  $queryCount++;
357  }
358  }
359 
360  if(feof($fp))
361  {
362  return true;
363  }
364  else
365  {
366  // $status .= ftell($fp).'/'.filesize($filename).' '.(round(ftell($fp)/filesize($filename), 2)*100).'%'."\n";
367  return false;
368  }
369  } // ./ import
370 
371 
372  /**
373  * @brief Delete a whole database (including all tables)
374  * @param $database
375  */
376  public function deleteDatabase($database): bool
377  {
378  $deletedTables = 0;
379  $errors = 0;
380 
381  // get all tables as array
382  $result = $this->query("SHOW TABLES IN `$database`");
383  // check if result is set
384 
385  if (isset($result) && (!empty($result)))
386  {
387  // walk through result array
388  while ($table = mysqli_fetch_array($result))
389  { // store tablename in var
390  $tableName = $table[0];
391  // try to delete table
392  if ($this->query("DROP TABLE `$database`.`$tableName`"))
393  { // output(?)
394  //echo "$tableName was cleared <br>";
395  $deletedTables++;
396  }
397  else
398  { // output error
399  $errors++;
400  }
401  }
402  }
403  // something went wrong
404  if ($errors > 0)
405  { // output error
406  return false;
407  }
408  else {
409  return true;
410  }
411  }
412 
413  /**
414  * @brief Get all tables from a database and return as array
415  * @return array tableList
416  */
417  public function get_tables()
418  {
419  // set tableList array
420  $tableList = array();
421  // get tables from database
422  $res = $this->query("SHOW TABLES");
423  // walk through result
424  while($row = mysqli_fetch_array($res))
425  { // fill array
426  $tableList[] = $row[0];
427  }
428  return $tableList;
429  }
430 
431  /**
432  * @brief Truncate a table
433  * @param $table string the table to truncate
434  * @return bool
435  */
436  public function truncateTable(string $table): bool
437  {
438  if (!empty($table))
439  {
440  $table = '{'.$table.'}';
441  if ($this->query("TRUNCATE TABLE $table"))
442  { // success
443  return true;
444  }
445  else
446  { // error
447  return false;
448  }
449  }
450  return false;
451  }
452 
453  /**
454  * @brief Drop table from a database
455  * @param array $tables the tables to drop
456  * @return bool
457  */
458  public function dropTables($tables)
459  {
460  $processed = 0;
461  // check if table was set
462  if (!isset($tables) || (empty($tables)) || (!array($tables)))
463  {
464  return false;
465  }
466  foreach ($tables as $table)
467  {
468  if ($this->query("DROP TABLE `".$table."`") === true)
469  {
470  $processed++;
471  }
472  }
473  return true;
474  }
475  } // EOF ./dbclass
476 }// ./namespace
$filename
$filename
Definition: actions.php:10
$lang
print $lang['FILEMAN_UPLOAD']
Definition: filemanager.php:385
die
die
Definition: block-user.php:27
YAWK\db
Mysqli database class; returns db connection object.
Definition: db.php:16
YAWK\db\quote
quote($value)
Quote and escape value for use in a database query *.
Definition: db.php:286
YAWK\db\commit
commit()
Definition: db.php:97
YAWK\db\prepare
prepare($sql)
Definition: db.php:92
YAWK\db\rollback
rollback()
Definition: db.php:111
YAWK\db\__construct
__construct()
Definition: db.php:26
YAWK\db\beginTransaction
beginTransaction()
Definition: db.php:82
YAWK\db\next_result
next_result()
Move to next result set of a multi query.
Definition: db.php:141
YAWK\db\query
query($query)
Execute any sql query.
Definition: db.php:213
YAWK\db\multi_query
multi_query($query)
Definition: db.php:87
YAWK\db\real_escape_string
real_escape_string($migrationSql)
Definition: db.php:102
YAWK\db\$connection
$connection
Definition: db.php:21
YAWK\db\select
select($query)
Fetch rows from database (SELECT query)
Definition: db.php:238
YAWK\db\close
close()
Definition: db.php:74
YAWK\db\error
error()
Fetch last error from the database.
Definition: db.php:273
YAWK\db\connect
connect()
Connect to the database.
Definition: db.php:44
YAWK\db\more_results
more_results()
Checks if there are more query results from a multi query.
Definition: db.php:162
YAWK\db\clearResults
clearResults()
Definition: db.php:179
YAWK\db\$config
$config
Definition: db.php:19
$result
$result
Definition: email-send.php:137
$rows
$rows
Definition: menus.php:126
$sql
$sql
Definition: message-new.php:32
YAWK
This class serves methods to create backup from files.
Definition: AdminLTE.php:2
$value
$value
Definition: widget-edit.php:244