Database.php 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411
  1. <?php
  2. namespace Lychee\Modules;
  3. use Mysqli;
  4. final class Database {
  5. private $connection = null;
  6. private static $instance = null;
  7. private static $versions = array(
  8. '020700', // 2.7.0
  9. '030000', // 3.0.0
  10. '030001', // 3.0.1
  11. '030003', // 3.0.3
  12. '030100' // 3.1.0
  13. );
  14. /**
  15. * @return object Returns a new or cached connection.
  16. */
  17. public static function get() {
  18. if (!self::$instance) {
  19. $credentials = Config::get();
  20. self::$instance = new self(
  21. $credentials['host'],
  22. $credentials['user'],
  23. $credentials['password'],
  24. $credentials['name'],
  25. $credentials['prefix']
  26. );
  27. }
  28. return self::$instance->connection;
  29. }
  30. /**
  31. * Exits on error.
  32. * @return boolean Returns true when successful.
  33. */
  34. private function __construct($host, $user, $password, $name = 'lychee', $dbTablePrefix) {
  35. // Check dependencies
  36. Validator::required(isset($host, $user, $password, $name), __METHOD__);
  37. // Define the table prefix
  38. defineTablePrefix($dbTablePrefix);
  39. // Open a new connection to the MySQL server
  40. $connection = self::connect($host, $user, $password);
  41. // Check if the connection was successful
  42. if ($connection===false) Response::error('' . $connection->connect_error);
  43. if (self::setCharset($connection)===false) Response::error('Could not set database charset!');
  44. // Create database
  45. if (self::createDatabase($connection, $name)===false) Response::error('Could not create database!');
  46. // Create tables
  47. if (self::createTables($connection)===false) Response::error('Could not create tables!');
  48. // Update database
  49. if (self::update($connection, $name)===false) Response::error('Could not update database and tables!');
  50. $this->connection = $connection;
  51. return true;
  52. }
  53. /**
  54. * @return object|false Returns the connection when successful.
  55. */
  56. public static function connect($host = 'localhost', $user, $password) {
  57. // Open a new connection to the MySQL server
  58. $connection = new Mysqli($host, $user, $password);
  59. // Check if the connection was successful
  60. if ($connection->connect_errno) return false;
  61. return $connection;
  62. }
  63. /**
  64. * @return boolean Returns true when successful.
  65. */
  66. private static function setCharset($connection) {
  67. // Check dependencies
  68. Validator::required(isset($connection), __METHOD__);
  69. // Avoid sql injection on older MySQL versions by using GBK
  70. if ($connection->server_version<50500) @$connection->set_charset('GBK');
  71. else @$connection->set_charset('utf8');
  72. // Set unicode
  73. $query = 'SET NAMES utf8';
  74. $result = self::execute($connection, $query, null, null);
  75. if ($result===false) return false;
  76. return true;
  77. }
  78. /**
  79. * @return boolean Returns true when successful.
  80. */
  81. public static function createDatabase($connection, $name = 'lychee') {
  82. // Check dependencies
  83. Validator::required(isset($connection), __METHOD__);
  84. // Check if database exists
  85. if ($connection->select_db($name)===true) return true;
  86. // Create database
  87. $query = self::prepare($connection, 'CREATE DATABASE IF NOT EXISTS ?', array($name));
  88. $result = self::execute($connection, $query, null, null);
  89. if ($result===false) return false;
  90. if ($connection->select_db($name)===false) return false;
  91. return true;
  92. }
  93. /**
  94. * @return boolean Returns true when successful.
  95. */
  96. private static function createTables($connection) {
  97. // Check dependencies
  98. Validator::required(isset($connection), __METHOD__);
  99. // Check if tables exist
  100. $query = self::prepare($connection, 'SELECT * FROM ?, ?, ?, ? LIMIT 0', array(LYCHEE_TABLE_PHOTOS, LYCHEE_TABLE_ALBUMS, LYCHEE_TABLE_SETTINGS, LYCHEE_TABLE_LOG));
  101. $result = self::execute($connection, $query, null, null);
  102. if ($result!==false) return true;
  103. // Check if log table exists
  104. $exist = self::prepare($connection, 'SELECT * FROM ? LIMIT 0', array(LYCHEE_TABLE_LOG));
  105. $result = self::execute($connection, $exist, null, null);
  106. if ($result===false) {
  107. // Read file
  108. $file = __DIR__ . '/../database/log_table.sql';
  109. $query = @file_get_contents($file);
  110. if ($query===false) return false;
  111. // Create table
  112. $query = self::prepare($connection, $query, array(LYCHEE_TABLE_LOG));
  113. $result = self::execute($connection, $query, null, null);
  114. if ($result===false) return false;
  115. }
  116. // Check if settings table exists
  117. $exist = self::prepare($connection, 'SELECT * FROM ? LIMIT 0', array(LYCHEE_TABLE_SETTINGS));
  118. $result = self::execute($connection, $exist, __METHOD__, __LINE__);
  119. if ($result===false) {
  120. // Read file
  121. $file = __DIR__ . '/../database/settings_table.sql';
  122. $query = @file_get_contents($file);
  123. if ($query===false) {
  124. Log::error($connection, __METHOD__, __LINE__, 'Could not load query for lychee_settings');
  125. return false;
  126. }
  127. // Create table
  128. $query = self::prepare($connection, $query, array(LYCHEE_TABLE_SETTINGS));
  129. $result = self::execute($connection, $query, __METHOD__, __LINE__);
  130. if ($result===false) return false;
  131. // Read file
  132. $file = __DIR__ . '/../database/settings_content.sql';
  133. $query = @file_get_contents($file);
  134. if ($query===false) {
  135. Log::error($connection, __METHOD__, __LINE__, 'Could not load content-query for lychee_settings');
  136. return false;
  137. }
  138. // Add content
  139. $query = self::prepare($connection, $query, array(LYCHEE_TABLE_SETTINGS));
  140. $result = self::execute($connection, $query, __METHOD__, __LINE__);
  141. if ($result===false) return false;
  142. // Generate identifier
  143. $identifier = md5(microtime(true));
  144. $query = self::prepare($connection, "UPDATE `?` SET `value` = '?' WHERE `key` = 'identifier' LIMIT 1", array(LYCHEE_TABLE_SETTINGS, $identifier));
  145. $result = self::execute($connection, $query, __METHOD__, __LINE__);
  146. if ($result===false) return false;
  147. }
  148. // Check if albums table exists
  149. $exist = self::prepare($connection, 'SELECT * FROM ? LIMIT 0', array(LYCHEE_TABLE_ALBUMS));
  150. $result = self::execute($connection, $exist, __METHOD__, __LINE__);
  151. if ($result===false) {
  152. // Read file
  153. $file = __DIR__ . '/../database/albums_table.sql';
  154. $query = @file_get_contents($file);
  155. if ($query===false) {
  156. Log::error($connection, __METHOD__, __LINE__, 'Could not load query for lychee_albums');
  157. return false;
  158. }
  159. // Create table
  160. $query = self::prepare($connection, $query, array(LYCHEE_TABLE_ALBUMS));
  161. $result = self::execute($connection, $query, __METHOD__, __LINE__);
  162. if ($result===false) return false;
  163. }
  164. // Check if photos table exists
  165. $exist = self::prepare($connection, 'SELECT * FROM ? LIMIT 0', array(LYCHEE_TABLE_PHOTOS));
  166. $result = self::execute($connection, $exist, __METHOD__, __LINE__);
  167. if ($result===false) {
  168. // Read file
  169. $file = __DIR__ . '/../database/photos_table.sql';
  170. $query = @file_get_contents($file);
  171. if ($query===false) {
  172. Log::error($connection, __METHOD__, __LINE__, 'Could not load query for lychee_photos');
  173. return false;
  174. }
  175. // Create table
  176. $query = self::prepare($connection, $query, array(LYCHEE_TABLE_PHOTOS));
  177. $result = self::execute($connection, $query, __METHOD__, __LINE__);
  178. if ($result===false) return false;
  179. }
  180. return true;
  181. }
  182. /**
  183. * Exits when an update fails.
  184. * @return boolean Returns true when successful.
  185. */
  186. private static function update($connection, $dbName) {
  187. // Check dependencies
  188. Validator::required(isset($connection, $dbName), __METHOD__);
  189. // Get current version
  190. $query = self::prepare($connection, "SELECT * FROM ? WHERE `key` = 'version'", array(LYCHEE_TABLE_SETTINGS));
  191. $result = self::execute($connection, $query, __METHOD__, __LINE__);
  192. if ($result===false) return false;
  193. // Extract current version
  194. $current = $result->fetch_object()->value;
  195. // For each update
  196. foreach (self::$versions as $version) {
  197. // Only update when newer version available
  198. if ($version<=$current) continue;
  199. // Load update
  200. include(__DIR__ . '/../database/update_' . $version . '.php');
  201. }
  202. return true;
  203. }
  204. /**
  205. * @return boolean Returns true when successful.
  206. */
  207. public static function setVersion($connection, $version) {
  208. // Check dependencies
  209. Validator::required(isset($connection), __METHOD__);
  210. $query = self::prepare($connection, "UPDATE ? SET value = '?' WHERE `key` = 'version'", array(LYCHEE_TABLE_SETTINGS, $version));
  211. $result = self::execute($connection, $query, __METHOD__, __LINE__);
  212. if ($result===false) return false;
  213. return true;
  214. }
  215. /**
  216. * @return string Returns a escaped query.
  217. */
  218. public static function prepare($connection, $query, array $data) {
  219. // Check dependencies
  220. Validator::required(isset($connection, $query), __METHOD__);
  221. // Count the number of placeholders and compare it with the number of arguments
  222. // If it doesn't match, calculate the difference and skip this number of placeholders before starting the replacement
  223. // This avoids problems with placeholders in user-input
  224. // $skip = Number of placeholders which need to be skipped
  225. $skip = 0;
  226. $temp = '';
  227. $num = array(
  228. 'placeholder' => substr_count($query, '?'),
  229. 'data' => count($data)
  230. );
  231. if (($num['data']-$num['placeholder'])<0) Log::notice($connection, __METHOD__, __LINE__, 'Could not completely prepare query. Query has more placeholders than values.');
  232. foreach ($data as $value) {
  233. // Escape
  234. $value = mysqli_real_escape_string($connection, $value);
  235. // Recalculate number of placeholders
  236. $num['placeholder'] = substr_count($query, '?');
  237. // Calculate number of skips
  238. if ($num['placeholder']>$num['data']) $skip = $num['placeholder'] - $num['data'];
  239. if ($skip>0) {
  240. // Need to skip $skip placeholders, because the user input contained placeholders
  241. // Calculate a substring which does not contain the user placeholders
  242. // 1 or -1 is the length of the placeholder (placeholder = ?)
  243. $pos = -1;
  244. for ($i=$skip; $i>0; $i--) $pos = strpos($query, '?', $pos + 1);
  245. $pos++;
  246. $temp = substr($query, 0, $pos); // First part of $query
  247. $query = substr($query, $pos); // Last part of $query
  248. }
  249. // Put a backslash in front of every character that is part of the regular
  250. // expression syntax. Avoids a backreference when using preg_replace.
  251. $value = preg_quote($value);
  252. // Replace
  253. $query = preg_replace('/\?/', $value, $query, 1);
  254. if ($skip>0) {
  255. // Reassemble the parts of $query
  256. $query = $temp . $query;
  257. }
  258. // Reset skip
  259. $skip = 0;
  260. // Decrease number of data elements
  261. $num['data']--;
  262. }
  263. return $query;
  264. }
  265. /**
  266. * @return object|false Returns the results on success.
  267. */
  268. public static function execute($connection, $query, $function, $line) {
  269. // Check dependencies
  270. Validator::required(isset($connection, $query), __METHOD__);
  271. // Only activate logging when $function and $line is set
  272. $logging = ($function===null||$line===null ? false : true);
  273. // Execute query
  274. $result = $connection->query($query);
  275. // Check if execution failed
  276. if ($result===false) {
  277. if ($logging===true) Log::error($connection, $function, $line, $connection->error);
  278. return false;
  279. }
  280. return $result;
  281. }
  282. }
  283. ?>