Sql.php 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337
  1. <?php namespace Dever;
  2. class Sql
  3. {
  4. public static function desc($table)
  5. {
  6. return 'DESC ' . $table;
  7. }
  8. public static function truncate($table)
  9. {
  10. return 'TRUNCATE TABLE `' . $table . '`';
  11. }
  12. public static function opt($table)
  13. {
  14. return 'OPTIMIZE TABLE `' . $table . '`';
  15. }
  16. public static function explain($sql)
  17. {
  18. return 'EXPLAIN ' . $sql;
  19. }
  20. public static function showIndex($table)
  21. {
  22. return 'SHOW INDEX FROM ' . $table;
  23. }
  24. public static function create($config)
  25. {
  26. $sql = 'CREATE TABLE `' . $config['table'] . '`(';
  27. $struct = array('id' => array('name' => 'ID', 'type' => 'int(11)'),'cdate' => array('name' => 'cdate', 'type' => 'int(11)'));
  28. $struct = array_merge($struct, $config['struct']);
  29. foreach ($struct as $k => $v) {
  30. $sql .= self::createField($k, $v) . ',';
  31. }
  32. $sql = rtrim($sql, ',') . ')';
  33. if (isset($config['auto'])) {
  34. $sql .= ' AUTO_INCREMENT = ' . $config['auto'];
  35. }
  36. if (isset($config['type'])) {
  37. $sql .= ' ENGINE = ' . $config['type'] . ' ';
  38. }
  39. return $sql . ' COMMENT="'.$config['name'].'"';
  40. }
  41. public static function createField($name, $set)
  42. {
  43. $field = '`' . $name . '` ' . strtoupper($set['type']);
  44. if ($name == 'id') {
  45. $field .= ' UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL';
  46. } elseif (strpos($set['type'], 'text') !== false) {
  47. $field .= ' NULL';
  48. } elseif (isset($set['default'])) {
  49. $field .= ' NOT NULL DEFAULT "' . $set['default'] . '"';
  50. } elseif (strpos($set['type'], 'int') !== false || strpos($set['type'], 'float') !== false || strpos($set['type'], 'decimal') !== false || strpos($set['type'], 'double') !== false) {
  51. $field .= ' NOT NULL DEFAULT 0';
  52. } else {
  53. $field .= ' NOT NULL DEFAULT ""';
  54. }
  55. if (isset($set['name'])) {
  56. $field .= ' COMMENT \'' . $set['name'] . '\'';
  57. }
  58. return $field;
  59. }
  60. public static function alter($table, $struct, $data)
  61. {
  62. $sql = array();
  63. $alter = 'ALTER TABLE `' . $table . '` ';
  64. foreach ($data as $v) {
  65. $field = $v['Field'];
  66. if ($field != 'id' && $field != 'cdate') {
  67. $set = $struct[$field] ?? false;
  68. if ($set) {
  69. if ($set['type'] != $v['Type'] || (isset($set['default']) && $set['default'] != $v['Default'])) {
  70. $sql[] = $alter . ' CHANGE `' . $field . '` ' . self::createField($field, $set);
  71. } else {
  72. unset($struct[$field]);
  73. }
  74. } else {
  75. $sql[] = $alter . ' DROP `' . $field . '`';
  76. }
  77. }
  78. }
  79. if ($struct) {
  80. foreach ($struct as $k => $v) {
  81. $sql[] = $alter . ' ADD ' . self::createField($k, $v);
  82. }
  83. }
  84. return implode(';', $sql);
  85. }
  86. public static function index($table, $index, $del = array())
  87. {
  88. $sql = array();
  89. $alter = 'ALTER TABLE `' . $table . '` ';
  90. foreach ($del as $v) {
  91. if ($v['Key_name'] != 'PRIMARY') {
  92. $sql[] = $alter . ' DROP INDEX ' . $v['Key_name'];
  93. }
  94. }
  95. if ($index) {
  96. foreach ($index as $k => $v) {
  97. $t = 'INDEX';
  98. if (strpos($v, '.')) {
  99. list($v, $t) = explode('.', $v);
  100. }
  101. $sql[] = $alter . ' ADD ' . strtoupper($t) . ' ' . $k . ' (' . $v . ')';
  102. }
  103. }
  104. return implode(';', $sql);
  105. }
  106. public static function partition($table, $partition, $index)
  107. {
  108. $state = true;
  109. foreach ($index as $k => $v) {
  110. if ($v['Key_name'] == 'PRIMARY' && $v['Column_name'] == $partition['field']) {
  111. $state = false;
  112. }
  113. }
  114. $alter = '';
  115. if ($state) {
  116. $type = $partition['type'];
  117. if ($partition['type'] == 'time') {
  118. $type = 'range';
  119. }
  120. $alter = 'ALTER TABLE `' . $table . '` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, `'.$partition['field'].'`) USING BTREE;ALTER TABLE `' . $table . '` PARTITION BY '.strtoupper($type).' ('.$partition['field'].') ';
  121. } else {
  122. $alter = 'ALTER TABLE `' . $table . '` ADD PARTITION ';
  123. }
  124. if ($partition['type'] == 'range' || $partition['type'] == 'time') {
  125. $name = $partition['value'];
  126. if ($partition['type'] == 'time') {
  127. $name = date('Ymd', $name - 86400);
  128. }
  129. $sql = 'PARTITION p'.$name.' VALUES LESS THAN ('.$partition['value'].')';
  130. return $alter . '('.$sql.')';
  131. } elseif ($partition['type'] == 'list') {
  132. $sql = '';
  133. foreach ($partition['value'] as $k => $v) {
  134. $sql = 'PARTITION p'.$v['name'].' VALUES IN ('.$v['value'].')';
  135. }
  136. return $alter . '('.$sql.')';
  137. } elseif ($partition['type'] == 'hash' || $partition['type'] == 'key') {
  138. if ($state) {
  139. return $alter . 'PARTITIONS ' . $partition['value'];
  140. }
  141. return self::desc($table);
  142. }
  143. }
  144. public static function select($table, $param, &$bind, $set = array(), $field = array(), $lock = false, $type = '')
  145. {
  146. $col = '*';
  147. $rule = '';
  148. if (isset($set['col'])) {
  149. $col = $set['col'];
  150. }
  151. if (isset($set['join'])) {
  152. $temp = explode('_', $table);
  153. $prefix = $temp[0] . '_' . $temp[1] . '_';
  154. $table .= ' AS ' . $temp[2];
  155. foreach ($set['join'] as $k => $v) {
  156. $table .= ' ' . $v['type'] . ' ' . $prefix . $v['table'] . ' AS ' . $v['table'] . ' ON ' . $v['on'];
  157. }
  158. }
  159. if (isset($set['group'])) {
  160. $rule .= ' GROUP BY ' . $set['group'];
  161. }
  162. if (isset($set['order'])) {
  163. if ($type == 'Influxdb') {
  164. $set['order'] = ' time desc';
  165. }
  166. $rule .= ' ORDER BY ' . $set['order'];
  167. }
  168. if (isset($set['limit'])) {
  169. if (is_array($set['limit']) && !$type) {
  170. $table .= ' inner join (select id from ' . $table . self::where($param, $bind, $field) . $rule . ' limit ' . $set['limit'][0].','.$set['limit'][1].') as t on '.$table.'.id=t.id';
  171. $rule = '';
  172. $param = false;
  173. } else {
  174. if ($type == 'Influxdb' && strpos($set['limit'], ',')) {
  175. $temp = explode(',', $set['limit']);
  176. $rule .= ' LIMIT ' . $temp[1] . ' OFFSET ' . $temp[0];
  177. } else {
  178. $rule .= ' LIMIT ' . $set['limit'];
  179. }
  180. }
  181. }
  182. if ($lock) {
  183. $rule .= ' FOR UPDATE';
  184. }
  185. return 'SELECT ' . $col . ' FROM ' . $table . self::where($param, $bind, $field, $type) . $rule;
  186. }
  187. public static function where($param, &$bind, $field = array(), $type = '')
  188. {
  189. if ($param) {
  190. $first = $second = '';
  191. if (is_array($param)) {
  192. $i = 0;
  193. foreach ($param as $k => $v) {
  194. if (strpos($k, '#')) {
  195. $k = trim($k, '#');
  196. }
  197. if ($k == 'or' || $k == 'and') {
  198. $first_link = $second_link = '';
  199. foreach ($v as $k1 => $v1) {
  200. if (is_array($v1)) {
  201. self::field($second_link, $bind, $i, $k1, $v1[0], $v1[1], $field, $type);
  202. } else {
  203. self::field($first_link, $bind, $i, $k1, '=', $v1, $field, $type);
  204. }
  205. }
  206. $first_link = ltrim($first_link, ' and ');
  207. $second .= ' ' . $k . ' (' . $first_link . $second_link . ')';
  208. } else {
  209. if (is_array($v)) {
  210. self::field($second, $bind, $i, $k, $v[0], $v[1], $field, $type);
  211. } else {
  212. self::field($first, $bind, $i, $k, '=', $v, $field, $type);
  213. }
  214. }
  215. }
  216. return ' WHERE ' . ltrim($first . $second, ' and ');
  217. } elseif (is_numeric($param)) {
  218. if ($type == 'Influxdb') {
  219. return ' WHERE "id" = \'' . $param . '\'';
  220. } else {
  221. return ' WHERE id = ' . intval($param);
  222. }
  223. } else {
  224. return ' WHERE ' . $param;
  225. }
  226. } else {
  227. return '';
  228. }
  229. }
  230. private static function field(&$s, &$b, &$i, $k, $e, $v, $f, $t)
  231. {
  232. $type = '';
  233. if (is_string($b)) {
  234. $type = $b;
  235. $b = array();
  236. }
  237. if ($f && empty($f[$k]) && strpos('id,cdate', $k) === false) {
  238. return;
  239. }
  240. $s .= ' and ';
  241. $p = ':'.$k.$i;
  242. $x = '`';
  243. if ($t == 'Influxdb') {
  244. //$e = '=';
  245. if ($k == 'cdate') {
  246. $k = 'time';
  247. $v = date('Y-m-d H:i:s', $v-28800);
  248. }
  249. $x = '"';
  250. $p = "'".$v."'";
  251. if (strpos($e,'in') !== false) {
  252. $e = '=';
  253. }
  254. }
  255. $k = $x.$k.$x;
  256. if (strpos($e,'in') !== false) {
  257. if (!is_array($v)) {
  258. $v = explode(',', $v);
  259. }
  260. $t = '';
  261. foreach ($v as $ti => $tj) {
  262. if ($ti > 0) {
  263. $ti = $p.'_'.$ti;
  264. $t .= ','.$ti;
  265. $b[$ti] = $tj;
  266. } else {
  267. $v = $tj;
  268. $t .= $p;
  269. }
  270. }
  271. $s .= $k.' ' .$e.' ('.$t.')';
  272. } elseif ($e == 'like') {
  273. $s .= 'instr('.$k.','.$p.')'.' > 0';
  274. } elseif ($e == 'group') {
  275. $v = ','.$v.',';
  276. $s .= 'instr(concat(",",'.$k.',","),'.$p.')' . ' > 0';
  277. } elseif ($e == 'between') {
  278. $b[$p.'_e'] = $v[1];
  279. $v = $v[0];
  280. $s .= $k.' between '.$p.' and '.$p.'_e';
  281. } else {
  282. $s .= $k.$e.$p;
  283. }
  284. $b[$p] = $v;
  285. $i++;
  286. }
  287. public static function insert($table, $data, &$bind, $field)
  288. {
  289. $sql = 'INSERT INTO `' . $table . '` SET ';#IGNORE
  290. foreach ($data as $k => $v) {
  291. if ($field && empty($field[$k]) && strpos('id,cdate', $k) === false) {
  292. continue;
  293. }
  294. $sql .= '`' . $k . '`=:' . $k . ',';
  295. $bind[':'.$k] = $v;
  296. }
  297. return rtrim($sql, ',');
  298. }
  299. public static function update($table, $param, $data, &$bind, $field)
  300. {
  301. $i = 0;
  302. $sql = 'UPDATE `' . $table . '` SET ';
  303. foreach ($data as $k => $v) {
  304. if ($field && empty($field[$k]) && strpos('id,cdate', $k) === false) {
  305. continue;
  306. }
  307. $a = '';
  308. if (is_array($v)) {
  309. $a = '`' . $k . '`' . $v[0];
  310. $v = $v[1];
  311. }
  312. $sql .= '`' . $k . '`=' . $a . ':' . $k . ',';
  313. $bind[':'.$k] = $v;
  314. }
  315. return rtrim($sql, ',') . self::where($param, $bind, $field);
  316. }
  317. public static function delete($table, $param, &$bind, $field)
  318. {
  319. return 'DELETE FROM `' . $table . '`' . self::where($param, $bind, $field);
  320. }
  321. public static function inserts($table, $param)
  322. {
  323. $num = $param['num'] ?? 1;
  324. $sql = 'INSERT INTO `' . $table . '` (' . $param['field'] . ') VALUES ';
  325. foreach ($param['value'] as $k => $v) {
  326. for ($i = 1; $i <= $num; $i++) {
  327. $insert[] = '(' . $v . ')';
  328. }
  329. }
  330. $sql .= implode(',', $insert) . ',';
  331. return rtrim($sql, ',');
  332. }
  333. public static function distance($lng, $lat)
  334. {
  335. return 'round((st_distance(point(lng, lat), point('.$lng.', '.$lat.'))*111195)/1000, 2) as distance';
  336. }
  337. }