Database.php 5.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228
  1. <?php
  2. namespace App;
  3. use Exception;
  4. use mysqli;
  5. use mysqli_result;
  6. use RuntimeException;
  7. use App\Constraint\Constraint;
  8. class Database {
  9. /** @var string */
  10. public static $lastQuery;
  11. /** @var string[] */
  12. public static $queries = [];
  13. /** @var int */
  14. public static $queryCount = 0;
  15. /** @var mysqli */
  16. public static $connection;
  17. public static function affectedRows(): int {
  18. return mysqli_affected_rows(self::$connection);
  19. }
  20. /**
  21. * @param string $host
  22. * @param string $username
  23. * @param string $password
  24. * @param string $database
  25. * @param int $port
  26. * @return mysqli
  27. * @throws Exception
  28. */
  29. public static function connect(string $host, string $username, string $password, string $database, int $port = 3306): mysqli {
  30. $connection = @new mysqli($host, $username, $password, $database, $port);
  31. if($connection->connect_errno !== 0) {
  32. throw new RuntimeException("Connect Error: " . $connection->connect_error);
  33. }
  34. self::$connection = $connection;
  35. self::query("SET NAMES utf8mb4");
  36. self::query("SET SESSION sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'");
  37. return self::$connection;
  38. }
  39. public static function dataSeek(mysqli_result $result, int $row_number): bool {
  40. return mysqli_data_seek($result, $row_number);
  41. }
  42. public static function escape(?string $string): string {
  43. return mysqli_real_escape_string(self::$connection, $string);
  44. }
  45. public static function error(): string {
  46. return mysqli_error(self::$connection);
  47. }
  48. public static function fetchArray(mysqli_result $result): ?array {
  49. return mysqli_fetch_array($result);
  50. }
  51. public static function fetchAssoc(mysqli_result $result): ?array {
  52. return mysqli_fetch_assoc($result);
  53. }
  54. public static function fetchObject(mysqli_result $result): ?object {
  55. return mysqli_fetch_object($result);
  56. }
  57. public static function fetchRow(mysqli_result $result): ?array {
  58. return mysqli_fetch_row($result);
  59. }
  60. public static function freeResult($result): void {
  61. mysqli_free_result($result);
  62. }
  63. public static function getValueString(array $pairs, bool $escape = false): string {
  64. if($escape) {
  65. $pairs = array_map("DB::escape", $pairs);
  66. }
  67. $string_array = [];
  68. foreach($pairs as $field => $value) {
  69. if($value === null) {
  70. $string_array[] = "`$field`=NULL";
  71. }
  72. else {
  73. $string_array[] = "`$field`='$value'";
  74. }
  75. }
  76. return implode(", ", $string_array);
  77. }
  78. /**
  79. * @param array $pairs
  80. * @param array|bool $options
  81. * @return string
  82. */
  83. public static function getWhereString(array $pairs, $options = []): string {
  84. $escape = false;
  85. $fuzzy = false;
  86. if(is_bool($options)) { // Legacy escape
  87. $escape = $options;
  88. }
  89. if(is_array($options)) {
  90. $escape = $options["escape"] ?? false;
  91. $fuzzy = $options["fuzzy"] ?? false;
  92. }
  93. if($escape) {
  94. foreach($pairs as &$value) {
  95. $value = self::escape($value);
  96. }
  97. unset($value);
  98. }
  99. $parts = [];
  100. foreach($pairs as $field => $value) {
  101. if($value === null) {
  102. $parts[] = "`$field` IS NULL";
  103. }
  104. else if(is_array($value)) {
  105. $values = array_map(static function(string $string) {
  106. return "'$string'";
  107. }, $value);
  108. if(empty($value)) {
  109. $parts[] = "0";
  110. } // Empty array
  111. else {
  112. $parts[] = "`$field` IN (" . implode(", ", $values) . ")";
  113. }
  114. }
  115. else if(is_a($value, Constraint::class)) {
  116. /** @var Constraint $value */
  117. $parts[] = "`$field` " . $value->getString();
  118. }
  119. else {
  120. $parts[] = $fuzzy ? "`$field` LIKE '%$value%'" : "`$field`='$value'";
  121. }
  122. }
  123. return implode(" AND ", $parts);
  124. }
  125. /**
  126. * @param string $table
  127. * @param string $name
  128. * @return bool
  129. * @throws Exception
  130. */
  131. public static function hasIndex(string $table, string $name): bool {
  132. $sql = "SHOW INDEX FROM `$table` WHERE `Key_name`='$name'";
  133. $result = self::query($sql);
  134. return self::numRows($result) > 0;
  135. }
  136. public static function insertId() {
  137. return mysqli_insert_id(self::$connection);
  138. }
  139. /**
  140. * @param mysqli_result|bool $result
  141. * @return int
  142. */
  143. public static function numRows($result): int {
  144. if(is_bool($result)) {
  145. return 0;
  146. }
  147. return mysqli_num_rows($result);
  148. }
  149. public static function objectOrNull(mysqli_result $result): ?object {
  150. return mysqli_num_rows($result)>0 ? self::fetchObject($result) : null;
  151. }
  152. public static function ping(): bool {
  153. return mysqli_ping(self::$connection);
  154. }
  155. /**
  156. * @param string $sql
  157. * @return bool|mysqli_result
  158. * @throws Exception
  159. */
  160. public static function query(string $sql) {
  161. if(self::$connection === null) {
  162. return false;
  163. }
  164. $is_development = ($_ENV["ENV"] === "development");
  165. self::$queries[] = $sql;
  166. self::$lastQuery = $sql;
  167. self::$queryCount++;
  168. $result = mysqli_query(self::$connection, $sql);
  169. if($result === false) {
  170. if($is_development) {
  171. $error = self::error();
  172. throw new RuntimeException("Query failed: $error $sql");
  173. }
  174. throw new RuntimeException("A database error has occurred. Please try again later.");
  175. }
  176. return $result;
  177. }
  178. public static function selectDatabase(string $name): void {
  179. mysqli_select_db(self::$connection, $name);
  180. }
  181. public static function dts(): string {
  182. return gmdate("Y-m-d H:i:s");
  183. }
  184. }