a72bf83d032391cfef62afe0881540d36e7e8902.svn-base 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598
  1. <?php namespace Maze\Data;
  2. class Sql
  3. {
  4. /**
  5. * instance
  6. *
  7. * @var string
  8. */
  9. static protected $instance;
  10. /**
  11. * getInstance
  12. *
  13. * @return Maze\Data\Sql;
  14. */
  15. static public function getInstance()
  16. {
  17. if(empty(self::$instance))
  18. {
  19. self::$instance = new self();
  20. }
  21. return self::$instance->init();
  22. }
  23. /**
  24. * create
  25. *
  26. * @return string
  27. */
  28. public function create($table, $struct)
  29. {
  30. $create = $primary = array();
  31. foreach($struct as $k => $v)
  32. {
  33. if(isset($v['table']) && $v['table'] != $table)
  34. {
  35. continue;
  36. }
  37. $primary[$k] = '';
  38. if(is_array($v))
  39. {
  40. if(!isset($v['type']))
  41. {
  42. continue;
  43. }
  44. $com = $v;
  45. $v = $com['type'];
  46. if(strpos($com['type'], 'text') !== false)
  47. {
  48. $primary[$k] .= '';
  49. }
  50. elseif(strpos($com['type'], 'int') === false)
  51. {
  52. $primary[$k] .= 'not null default \'\'';
  53. }
  54. elseif(!empty($com['default']))
  55. {
  56. $primary[$k] .= 'not null default \'' . $com['default'] . '\'';
  57. }
  58. elseif($k != 'id' && strpos($com['type'], 'int') !== false)
  59. {
  60. $primary[$k] .= 'not null default 0';
  61. }
  62. else
  63. {
  64. $primary[$k] .= 'not null';
  65. }
  66. if(!empty($com['name']))
  67. {
  68. $primary[$k] .= ' comment \'' . $com['name'] . '\'';
  69. }
  70. }
  71. elseif(is_string($v) && strpos($v,' ') !== false)
  72. {
  73. $com = explode(' ',$v);
  74. $v = $com[0];
  75. if(!empty($com[1]))
  76. {
  77. $primary[$k] .= 'not null default \'' . $com[1] . '\'';
  78. }
  79. else
  80. {
  81. $primary[$k] .= 'not null';
  82. }
  83. if(!empty($com[2]))
  84. {
  85. $primary[$k] .= ' comment \'' . $com[2] . '\'';
  86. }
  87. }
  88. if($k == 'id')
  89. {
  90. $primary[$k] = 'unsigned auto_increment primary key ' . $primary[$k];
  91. }
  92. $create[] = '`' . $k . '` ' . strtoupper(str_replace('-','(',$v) . ') ' . $primary[$k] . '');// not null
  93. }
  94. $sql = 'DROP TABLE IF EXISTS `' . $table . '`;CREATE TABLE `' . $table . '`(' . implode(',', $create) . ')';
  95. //echo $sql;die;
  96. return $sql;
  97. }
  98. /**
  99. * alter
  100. *
  101. * @return string
  102. */
  103. public function alter($table, $config)
  104. {
  105. $create = $primary = array();
  106. $alter = 'ALTER TABLE `' . $table;
  107. $sql = array();
  108. foreach($config as $k => $v)
  109. {
  110. if(isset($v['type']))
  111. {
  112. $v = array
  113. (
  114. 'add', $k, $k, $v['type'] . ' ' . $v['default'] . ' ' . $v['name'],
  115. );
  116. }
  117. $sql[$k] = '';
  118. if(isset($v[3]) && strpos($v[3],' ') !== false)
  119. {
  120. $com = explode(' ',$v[3]);
  121. $sql[$k] = str_replace('-','(',$com[0]) . ') ';
  122. if(isset($com[1]) && $com[1] != '')
  123. {
  124. $sql[$k] .= 'not null default \'' . $com[1] . '\'';
  125. }
  126. else
  127. {
  128. $sql[$k] .= 'not null';
  129. }
  130. if(!empty($com[2]))
  131. {
  132. $sql[$k] .= ' comment \'' . $com[2] . '\'';
  133. }
  134. $sql[$k] = strtoupper($sql[$k]);
  135. }
  136. if($v[0] == 'add')
  137. {
  138. # 新增字段
  139. $sql[$k] = $alter . '` ADD `' . $v[1] . '` ' . $sql[$k];
  140. }
  141. elseif($v[0] == 'delete')
  142. {
  143. # 删除字段
  144. $sql[$k] = $alter . '` DROP `' . $v[1] . '`';
  145. }
  146. else
  147. {
  148. # 修改字段
  149. $sql[$k] = $alter . '` CHANGE `' . $v[1] . '` `' . $v[2] . '` ' . $sql[$k];
  150. }
  151. }
  152. $sql = implode(';', $sql);
  153. return $sql;
  154. }
  155. /**
  156. * col
  157. *
  158. * @return string
  159. */
  160. private function col($col)
  161. {
  162. $result = '';
  163. if(is_array($col))
  164. {
  165. $array = array();
  166. foreach($col as $k => $v)
  167. {
  168. if(!is_numeric($k))
  169. {
  170. $array[] = $k . ' AS ' . $v;
  171. }
  172. else
  173. {
  174. $array[] = $v;
  175. }
  176. }
  177. $result = implode(' ', $array);
  178. }
  179. else
  180. {
  181. $result = $col ? $col : '*';
  182. }
  183. if(isset($this->col) && $this->col)
  184. {
  185. $result .= ',' . $this->col;
  186. }
  187. return $result;
  188. }
  189. /**
  190. * select
  191. *
  192. * @return string
  193. */
  194. public function select($table, $col = '')
  195. {
  196. $where = '';
  197. if($this->where)
  198. {
  199. if(isset($this->between))
  200. {
  201. $where = 'WHERE ' . $this->between . ' AND ' . implode(' ', $this->where);
  202. $this->limit = '';
  203. }
  204. else
  205. {
  206. $where = 'WHERE ' . implode(' ', $this->where);
  207. }
  208. }
  209. $sql = 'SELECT ' . $this->col($col) . ' FROM `' . $table . '` ' . $where . ' ' . $this->group . ' ' . $this->order . ' ' . $this->limit;
  210. $this->init();
  211. return $sql;
  212. }
  213. /**
  214. * count
  215. *
  216. * @return string
  217. */
  218. public function count($table, $col = '')
  219. {
  220. $where = '';
  221. if($this->where)
  222. {
  223. $where = 'WHERE ' . implode(' ', $this->where);
  224. }
  225. $state = 1;
  226. if($col == 'clear')
  227. {
  228. $col = '';
  229. $state = 2;
  230. }
  231. if(!$col)
  232. {
  233. $col = 'count(*) as total';
  234. }
  235. $sql = 'SELECT ' . $col . ' FROM `' . $table . '` ' . $where . ' ' . $this->group . ' ';
  236. if($state == 1)
  237. {
  238. $this->init();
  239. }
  240. return $sql;
  241. }
  242. /**
  243. * showIndex
  244. *
  245. * @return string
  246. */
  247. public function showIndex($table)
  248. {
  249. $sql = 'SHOW INDEX FROM `' . $table . '` ';
  250. return $sql;
  251. }
  252. /**
  253. * dropIndex
  254. *
  255. * @return string
  256. */
  257. public function dropIndex($table, $name)
  258. {
  259. $sql = 'ALTER TABLE `' . $table . '` DROP INDEX ' . $name;
  260. return $sql;
  261. }
  262. /**
  263. * index
  264. *
  265. * @return string
  266. */
  267. public function index($table, $value)
  268. {
  269. $sql = 'ALTER TABLE `' . $table . '` ADD INDEX ';
  270. $max = count($value)-1;
  271. $i = 0;
  272. foreach($value as $k => $v)
  273. {
  274. $sql .= ' ' . $k . ' (' . $v . ')';
  275. if($i >= $max)
  276. {
  277. $sql .= '';
  278. }
  279. else
  280. {
  281. $sql .= ',';
  282. }
  283. $i++;
  284. }
  285. return $sql;
  286. }
  287. /**
  288. * insert
  289. *
  290. * @return string
  291. */
  292. public function insert($table)
  293. {
  294. $sql = 'INSERT INTO `' . $table . '` (' . implode(',', $this->col) . ') VALUES (' . implode(',', $this->value) . ')';
  295. $this->init();
  296. return $sql;
  297. }
  298. /**
  299. * inserts
  300. *
  301. * @return string
  302. */
  303. public function inserts($table, $col, $value)
  304. {
  305. $sql = 'INSERT INTO `' . $table . '` (' . $col . ') VALUES ';
  306. $max = count($value)-1;
  307. foreach($value as $k => $v)
  308. {
  309. $sql .= '(' . $v . ')';
  310. if($k >= $max)
  311. {
  312. $sql .= '';
  313. }
  314. else
  315. {
  316. $sql .= ',';
  317. }
  318. }
  319. return $sql;
  320. }
  321. /**
  322. * update
  323. *
  324. * @return string
  325. */
  326. public function update($table)
  327. {
  328. $where = '';
  329. if(!$this->where)
  330. {
  331. return false;
  332. }
  333. else
  334. {
  335. $where = 'WHERE ' . implode(' ', $this->where);
  336. }
  337. $sql = 'UPDATE `' . $table . '` SET ' . implode(',', $this->value) . ' ' . $where;
  338. $this->init();
  339. return $sql;
  340. }
  341. /**
  342. * delete
  343. *
  344. * @return string
  345. */
  346. public function delete($table)
  347. {
  348. $where = '';
  349. if(!$this->where)
  350. {
  351. return false;
  352. }
  353. else
  354. {
  355. $where = 'WHERE ' . implode(' ', $this->where);
  356. }
  357. $sql = 'DELETE FROM `' . $table . '` ' . $where;
  358. $this->init();
  359. return $sql;
  360. }
  361. /**
  362. * truncate
  363. *
  364. * @return string
  365. */
  366. public function truncate($table)
  367. {
  368. $sql = 'TRUNCATE TABLE `' . $table . '`';
  369. return $sql;
  370. }
  371. /**
  372. * sql
  373. *
  374. * @return string
  375. */
  376. public function sql($sql)
  377. {
  378. return $sql;
  379. }
  380. /**
  381. * init
  382. *
  383. * @return object
  384. */
  385. public function init()
  386. {
  387. $this->where = $this->value = $this->col = array();
  388. $this->order = '';
  389. $this->group = '';
  390. $this->limit = '';
  391. return $this;
  392. }
  393. /**
  394. * where
  395. *
  396. * @return string
  397. */
  398. public function where($param)
  399. {
  400. if(empty($param[2])) $param[2] = '=';
  401. if(empty($param[3])) $param[3] = 'and';
  402. $where = '`' . $param[0] . '` ' . $param[2] . ' ' . $param[1];
  403. if(!$this->where)
  404. {
  405. $this->where[] = $where;
  406. }
  407. else
  408. {
  409. $this->where[] = $param[3] . ' ' . $where;
  410. }
  411. }
  412. /**
  413. * order
  414. *
  415. * @return string
  416. */
  417. public function order($param)
  418. {
  419. if(is_array($param[0]))
  420. {
  421. $this->order = 'order by ';
  422. foreach($param[0] as $k => $v)
  423. {
  424. $order[] = '`' . $k . '` ' . $v;
  425. }
  426. $this->order .= implode(',', $order);
  427. //echo $this->order;
  428. }
  429. else
  430. {
  431. if(empty($param[1])) $param[1] = 'desc';
  432. $this->order = 'order by `' . $param[0] . '` ' . $param[1];
  433. }
  434. }
  435. /**
  436. * group
  437. *
  438. * @return string
  439. */
  440. public function group($param)
  441. {
  442. if(is_array($param))
  443. {
  444. $param = $param[0];
  445. }
  446. # 去掉id的分组,没用
  447. if(isset($param) && $param != 'id')
  448. {
  449. if($param == 'day')
  450. {
  451. $this->col = 'FROM_UNIXTIME(cdate, "%Y-%m-%d") as day';
  452. }
  453. elseif($param == 'month')
  454. {
  455. $this->col = 'FROM_UNIXTIME(cdate, "%Y-%m") as month';
  456. }
  457. elseif($param == 'year')
  458. {
  459. $this->col = 'FROM_UNIXTIME(cdate, "%Y") as year';
  460. }
  461. else
  462. {
  463. $this->col = $param;
  464. }
  465. $this->group = 'group by `' . $param . '`';
  466. }
  467. }
  468. /**
  469. * limit
  470. *
  471. * @return string
  472. */
  473. public function limit($param)
  474. {
  475. if(empty($param[1])) $param[1] = 0;
  476. $this->limit = 'limit ' . $param[1] . ',' . $param[0];
  477. //$this->between = ' `id` BETWEEN ' . $param[1] . ' AND ' . ($param[1] + $param[0]);
  478. }
  479. /**
  480. * reset limit
  481. *
  482. * @return string
  483. */
  484. public function reset($param)
  485. {
  486. $this->{$param[0]} = '';
  487. }
  488. /**
  489. * add
  490. *
  491. * @return string
  492. */
  493. public function add($param)
  494. {
  495. $this->col[] = '`' . $param[0] . '`';
  496. $this->value[] = $param[1];
  497. }
  498. /**
  499. * set
  500. *
  501. * @return string
  502. */
  503. public function set($param)
  504. {
  505. if(empty($param[2])) $param[2] = '=';
  506. $param[0] = '`' . $param[0] . '`';
  507. if(strpos($param[2], '+=') !== false)
  508. {
  509. $param[2] = '=' . $param[0] . '+';
  510. }
  511. if(strpos($param[2], '-=') !== false)
  512. {
  513. $param[2] = '=' . $param[0] . '-';
  514. }
  515. $this->value[] = $param[0] . $param[2] . $param[1];
  516. }
  517. }