table.php 17 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594
  1. <?php
  2. /**
  3. * 数据表的显示规则
  4. *
  5. * @author Jinhui Zhu<jinhui.zhu@live.cn> 2015-08-23 10:07
  6. */
  7. namespace model;
  8. use \Qii\Driver\DBModel;
  9. use \Qii\Driver\Response;
  10. class table extends DBModel
  11. {
  12. public $tablesError;
  13. public function __construct()
  14. {
  15. parent::__construct();
  16. $this->checkRuleTable();
  17. }
  18. public function getRuleTableInfo()
  19. {
  20. $table = _include('../private/configure/table.rules.config.php');
  21. $table['database'] = $this->db->currentDB;
  22. return $table;
  23. }
  24. public function checkRuleTable()
  25. {
  26. $config = $this->getRuleTableInfo();
  27. if($config['sql'])
  28. {
  29. $this->db->exec($config['sql']);
  30. }
  31. }
  32. /**
  33. * 获取数据库列表
  34. * @return array
  35. */
  36. public function getDatabases()
  37. {
  38. $databases = array();
  39. $sql = "SHOW DATABASES";
  40. $rs = $this->db->setQuery($sql);
  41. while ($row = $rs->fetch()) {
  42. if (!in_array($row['Database'], array('information_schema', 'mysql', 'performance_schema', 'test'))) $databases[] = $row['Database'];
  43. }
  44. return $databases;
  45. }
  46. public function getTableSQL($db, $table)
  47. {
  48. if (!$db || !$table) {
  49. $this->tablesError = '参数不正确';
  50. return array('code' => 1, 'msg' => '参数不正确');
  51. }
  52. $data = array();
  53. $data['database'] = $db;
  54. $data['table'] = $table;
  55. try {
  56. $data['code'] = 0;
  57. $sql = "SHOW CREATE TABLE {$db}.{$table}";
  58. $rs = $this->db->setQuery($sql);
  59. $row = $rs->fetch();
  60. $createTableSQL = preg_replace('/AUTO_INCREMENT\=[\d]/', 'AUTO_INCREMENT=1', $row['Create Table']);
  61. if ($row) $data['sql'] = str_replace("CREATE TABLE ", "CREATE TABLE IF NOT EXISTS ", $createTableSQL) . ';';
  62. } catch (Exception $e) {
  63. $data['code'] = 1;
  64. $data['msg'] = $e->getMessage();
  65. $data['sql'] = '';
  66. }
  67. return $data;
  68. }
  69. /**
  70. * 获取指定数据库中的表名
  71. * @param string $db
  72. * @return array
  73. */
  74. public function getTableLists($db = 'istudy', $table = '')
  75. {
  76. if (!$db) {
  77. $this->tablesError = '参数不正确';
  78. return array();
  79. }
  80. $tables = array();
  81. $sql = "SHOW TABLES IN " . $db;
  82. $rs = $this->db->setQuery($sql);
  83. while ($row = $rs->fetch()) {
  84. $tableName = $row['Tables_in_' . $db];
  85. $insert = true;
  86. if (!empty($table)) $insert = stristr($tableName, $table) ? true : false;
  87. if ($insert) $tables[] = $tableName;
  88. }
  89. return $tables;
  90. }
  91. /**
  92. * 匹配类型并返回类型字段长度
  93. * @param string $type
  94. * @return Array
  95. */
  96. public function parseType($type)
  97. {
  98. preg_match("/(.*)\(.*?\)/", $type, $matches);
  99. $data = array();
  100. $data['source'] = $type;
  101. $data['type'] = isset($matches[1]) ? $matches[1] : $type;
  102. $ext = isset($matches[1]) ? str_replace(array($data['type'] . '(', ')'), '', $type) : 0;
  103. if ($data['type'] == 'enum') {
  104. $data['length'] = 0;
  105. $data['sets'] = $ext;
  106. $data['setsArray'] = preg_replace("/\"|\'/", "", explode(",", $ext));
  107. } else {
  108. $data['length'] = (int)$ext;
  109. }
  110. return $data;
  111. }
  112. /**
  113. * 获取数据库中某个表的字段
  114. * @param string $db
  115. * @param string $table
  116. * @return array 表中的字段列表
  117. */
  118. public function getFieldsLists($db = 'istudy', $table)
  119. {
  120. $fields = array();
  121. $sql = 'DESC ' . $db . '.' . $table;
  122. $rs = $this->db->setQuery($sql);
  123. while ($row = $rs->fetch()) {
  124. $val = array();
  125. $val['field'] = $row['Field'];
  126. $val['null'] = strtolower($row['Null']);
  127. $matches = $this->parseType($row['Type']);
  128. $val['length'] = 0;
  129. $val['type'] = $row['Type'];
  130. if (isset($matches['length'])) {
  131. $val['length'] = intval($matches['length']);
  132. $val['type'] = $matches['type'];
  133. }
  134. if ($matches['type'] == 'enum') {
  135. $val['sets'] = $matches['sets'];
  136. $val['setsArray'] = $matches['setsArray'];
  137. }
  138. $val['length'] = intval($matches['length']);
  139. if ($row['Null'] == 'NO') $val['require'] = true;
  140. if ($row['Key'] != '') {
  141. if ($row['Key'] == 'PRI') $val['pri'] = true;//主键,用于查询用
  142. if ($row['Key'] == 'UNI') $val['uni'] = true;
  143. }
  144. if ($row['Default']) $val['default'] = $row['Default'];
  145. if ($row['Extra']) $val['extra'] = $row['Extra'];
  146. $fields[$row['Field']] = $val;
  147. }
  148. return $fields;
  149. }
  150. /**
  151. * 获取指定数据库、数据表的规则
  152. * @param string $database 数据库
  153. * @param string $table 数据表
  154. * @return array 返回表的显示规则
  155. */
  156. public function getRules($database, $table)
  157. {
  158. if (!$database || !$table) {
  159. $this->tablesError = '参数不正确';
  160. return array();
  161. }
  162. $rules = (new \Qii\Driver\Easy())->_initialize();
  163. $rules->setPrivateKey(array('database', 'table'));
  164. $rules->setRules(new \Qii\Driver\Rules($this->getRuleTableInfo()));
  165. $rules->database = $database;
  166. $rules->table = $table;
  167. $tableRules = $rules->_exist();
  168. $tableRule = array();
  169. $tableRule['rules'] = array();
  170. if (!$tableRules->isError()) {
  171. $tableRule = $tableRules->getResult();
  172. $rule = isset($tableRule['rules']) ? $tableRule['rules'] : '';
  173. if ($rule != '') {
  174. $tableRule['rules'] = json_decode($rule, true);
  175. } else {
  176. $tableRule['rules'] = array();
  177. }
  178. }
  179. $defaultRules = $this->tableRules($database, $table);
  180. if ($defaultRules['rules']) $tableRule['rules'] = array_merge($defaultRules['rules'], $tableRule['rules']);
  181. return $tableRule;
  182. }
  183. /**
  184. * 保存数据库表的显示规则
  185. * @param string $database 数据库
  186. * @param string $table 数据表
  187. * @param string $rule 显示规则
  188. * @return bool 保存成功或失败
  189. */
  190. public function saveRules($database, $table, array $rule)
  191. {
  192. $rules = (new \Qii\Driver\Easy())->_initialize();
  193. $rules->setPrivateKey(array('database', 'table'));
  194. $rules->setRules(new \Qii\Driver\Rules($this->getRuleTableInfo()));
  195. $rules->database = $database;
  196. $rules->table = $table;
  197. $isExists = $rules->_exist();
  198. if ($isExists->isError()) {
  199. $this->tablesError = $isExists->getErrors();
  200. return false;
  201. }
  202. if (isset($isExists->getResult()['database'])) {
  203. //保存额外的规则,确保更新不影响
  204. $databaseRules = $this->getRules($database, $table);
  205. if (isset($databaseRules['rules']['invalidMessage']) && is_array($databaseRules['rules']['invalidMessage'])) {
  206. $rule['invalidMessage'] = $databaseRules['rules']['invalidMessage'];
  207. }
  208. if (isset($databaseRules['rules']['extRules']) && is_array($databaseRules['rules']['extRules'])) {
  209. $rule['extRules'] = $databaseRules['rules']['extRules'];
  210. }
  211. $rules->rules = json_encode($rule);
  212. $rules->update_time = time();
  213. //更新
  214. $result = $rules->_update();
  215. } else {
  216. $rules->rules = json_encode($rule);
  217. $rules->add_time = time();
  218. $result = $rules->_save();
  219. }
  220. $result = true;
  221. if ($rules->getErrors()) {
  222. $result = false;
  223. $this->tablesError = $rules->getErrors();
  224. }
  225. return $result;
  226. }
  227. /**
  228. * 更新数据表规则
  229. *
  230. * @param string $database 数据库名称
  231. * @param string $table 数据表名称
  232. * @param array $rule 规则
  233. * @return bool
  234. */
  235. public function updateRules($database, $table, array $rule)
  236. {
  237. if (!$database || !$table) {
  238. $this->tablesError = '参数不正确';
  239. return false;
  240. }
  241. $databaseRules = $this->getRules($database, $table);
  242. if (!isset($databaseRules['rules']) || !is_array($databaseRules['rules'])) {
  243. $this->tablesError = '规则不存在';
  244. return false;
  245. }
  246. $rule = array_merge($databaseRules['rules'], $rule);
  247. $rules = (new \Qii\Driver\Easy())->_initialize();
  248. $rules->setPrivateKey(array('database', 'table'));
  249. $rules->setRules(new \Qii\Driver\Rules($this->getRuleTableInfo()));
  250. $rules->database = $database;
  251. $rules->table = $table;
  252. $rules->rules = json_encode($rule);
  253. $isExists = $rules->_exist();
  254. if ($isExists->isError()) {
  255. $this->tablesError = $isExists->getError();
  256. return false;
  257. }
  258. if ($isExists->getResult()['database']) {
  259. $rules->update_time = time();
  260. //更新
  261. $result = $rules->_update();
  262. } else {
  263. $rules->add_time = time();
  264. $result = $rules->_save();
  265. }
  266. $result = true;
  267. if ($rules->isError()) {
  268. $result = false;
  269. $this->tablesError = $rules->getErrors();
  270. }
  271. return $result;
  272. }
  273. /**
  274. * 自动表的规则
  275. *
  276. * @param $database
  277. * @param $tableName
  278. * @return array
  279. */
  280. public function tableRules($database, $tableName)
  281. {
  282. if (!$database || !$tableName) {
  283. $this->tablesError = '参数不正确';
  284. return array();
  285. }
  286. $data = array();
  287. $rules = $this->getFieldsLists($database, $tableName);
  288. $data['database'] = $database;
  289. $data['tableName'] = $tableName;
  290. $data['rules'] = array();
  291. $data['rules']['end'] = array();
  292. $data['rules']['front'] = array();
  293. $data['rules']['alias'] = array();
  294. $data['rules']['fields'] = array();
  295. foreach ($rules AS $key => $val) {
  296. $data['rules']['fields'][] = $key;
  297. $data['rules']['end'][$key] = $data['rules']['front'][$key] = 1;
  298. $data['rules']['alias'][$key] = $key;
  299. $data['rules']['length'][$key] = $val['length'];
  300. $data['rules']['type'][$key] = $val['type'];
  301. $data['rules']['null'][$key] = $val['null'];
  302. if (isset($val['sets'])) $data['rules']['sets'][$key] = $val['sets'];
  303. if (isset($val['setsArray'])) $data['rules']['setsArray'][$key] = $val['setsArray'];
  304. if (isset($val['pri'])) $data['rules']['pri'][$key] = 1;
  305. if (isset($val['uni'])) $data['rules']['uni'][$key] = 1;
  306. }
  307. if (!isset($data['rules']['validate'])) $data['rules']['validate'] = array();
  308. return $data;
  309. }
  310. /**
  311. * 合并表的规则,当没有存储相关规则的时候就用系统默认规则
  312. *
  313. * @param $database
  314. * @param $tableName
  315. * @param $rules
  316. */
  317. public function mergeRules($database, $tableName, &$rules)
  318. {
  319. $tableRules = $this->tableRules($database, $tableName);
  320. $rules = array_merge($tableRules, $rules);
  321. foreach ($tableRules['rules'] AS $key => $val) {
  322. if (!isset($rules['rules'][$key]) || count($rules['rules'][$key]) == 0) $rules['rules'][$key] = $val;
  323. }
  324. return $rules;
  325. }
  326. /**
  327. * 自动合并数据表规则
  328. *
  329. * @param $database
  330. * @param $tableName
  331. * @return array
  332. */
  333. public function autoRules($database, $tableName)
  334. {
  335. if (!$database || !$tableName) {
  336. $this->tablesError = '参数不正确';
  337. return array();
  338. }
  339. $rules = $this->getRules($database, $tableName);
  340. $this->mergeRules($database, $tableName, $rules);
  341. return $rules;
  342. }
  343. /**
  344. * 获取数据表的数据
  345. *
  346. * @param $database 当前数据表
  347. * @param $tableName 当前表名称
  348. * @param int $currentPage 当前页码
  349. * @param int $pageSize 每一页数据条数
  350. * @return mixed
  351. */
  352. public function loadTableData($database, $tableName, $currentPage = 1, $pageSize = 12)
  353. {
  354. if (!$database || !$tableName) {
  355. $this->tablesError = '参数不正确';
  356. return array();
  357. }
  358. $currentPage = max(1, $currentPage);
  359. $start = ($currentPage - 1) * $pageSize;
  360. /*
  361. $rules = $this->getRules($database, $tableName);
  362. //当rules为空的时候,通过数据表结构自动生成规则
  363. if(empty($rules['rules']))
  364. {
  365. $rules = $this->tableRules($database, $tableName);
  366. }
  367. else
  368. {
  369. $this->mergeRules($database, $tableName, $rules);
  370. }*/
  371. $rules = $this->autoRules($database, $tableName);
  372. $data = array();
  373. $data['rows'] = array();
  374. $data['page'] = array('total' => 0, 'currentPage' => 0, 'totalPage' => 0);
  375. $data['page']['total'] = $this->db->getOne("SELECT COUNT(*) FROM {$database}.{$tableName}");
  376. $data['page']['currentPage'] = $currentPage;
  377. $data['page']['totalPage'] = ceil($data['page']['total'] / $pageSize);
  378. $sql = "SELECT * FROM {$database}.{$tableName} LIMIT " . $start . ',' . $pageSize;
  379. $rs = $this->db->setQuery($sql);
  380. $rulesCount = isset($rules['rules']['end']) && count($rules['rules']['end']) > 0 ? $rules['rules']['end'] : 0;
  381. while ($row = $rs->fetch()) {
  382. $val = array();
  383. if ($rulesCount > 0) {
  384. foreach ($rules['rules']['end'] AS $key => $field) {
  385. if ($field == 1) $val[$key] = $row[$key];
  386. }
  387. }
  388. $updateFields = array();
  389. $priKeys = array();
  390. foreach ($rules['rules']['pri'] AS $key => $pri) {
  391. $priKeys[] = $key;
  392. $updateFields[$key] = $row[$key];
  393. }
  394. //为了避免表中字段跟主要参数冲突,主要参数前边添加两个下划线
  395. $updateFields['__pri'] = join(',', $priKeys);
  396. $updateFields['__database'] = $database;
  397. $updateFields['__tableName'] = $tableName;
  398. $val['__updateFields'] = http_build_query($updateFields);
  399. $data['rows'][] = $val;
  400. unset($val);
  401. }
  402. $data['rules'] = $rules['rules'];
  403. return $data;
  404. }
  405. /**
  406. * 获取表中的数据
  407. * @author Jinhui Zhu 2015-08-26
  408. * @param string $database
  409. * @param string $tableName
  410. * @param string $pri
  411. * @param Array $val
  412. * @return mixed
  413. */
  414. public function loadDataFromTable($database, $tableName, $pri, $val)
  415. {
  416. if (!$database || !$tableName || (!$val && count($val) == 0)) {
  417. $this->tablesError = '参数不正确';
  418. return array();
  419. }
  420. return $this->db->whereArray($val)->selectRow($database . '.' . $tableName);
  421. }
  422. /**
  423. * 更新数据表数据
  424. * @param string $database
  425. * @param string $tableName
  426. * @param array $priVal
  427. * @param array $fields
  428. * @return bool
  429. */
  430. public function updateTableData($database, $tableName, $priVal, $fields)
  431. {
  432. if (!$database || !$tableName || !$fields) {
  433. $this->tablesError = '参数不正确';
  434. return array();
  435. }
  436. $rules = $this->autoRules($database, $tableName);
  437. $privateKey = array();
  438. if (empty($priVal) && isset($rules['rules']['uni'])) $privateKey = array_keys($rules['rules']['uni']);
  439. $table = (new \Qii\Driver\Easy())->_initialize();
  440. $table->setPrivateKey($priVal);
  441. $table->setRules(new \Qii\Driver\Rules($rules));
  442. foreach ($fields AS $key => $val) {
  443. $table->{$key} = $val;
  444. }
  445. return $result = $table->_update();
  446. }
  447. /**
  448. * 删除指定数据表中数据
  449. * 避免删除整张表的数据,验证val,如果为空就不删除
  450. * @param string $database
  451. * @param string $tableName
  452. * @param array $val {key : val}
  453. * @return bool
  454. */
  455. public function removeTableData($database, $tableName, $val)
  456. {
  457. if (empty($val)) {
  458. $this->tablesError = '参数不正确';
  459. return false;
  460. }
  461. return $this->db->deleteObject($database . '.' . $tableName, $val);
  462. }
  463. /**
  464. * 向指定数据库中插入数据
  465. *
  466. * @param $database
  467. * @param $tableName
  468. * @param $value
  469. */
  470. public function addTableData($database, $tableName, $value)
  471. {
  472. if (!$database || !$tableName || !$value) {
  473. $this->tablesError = '参数不正确';
  474. return array();
  475. }
  476. $rules = $this->autoRules($database, $tableName);
  477. //去掉自动privateKey,通过配置文件来做
  478. //$privateKey = array();
  479. //if(isset($rules['rules']['uni'])) $privateKey = array_keys($rules['rules']['uni']);
  480. $table = (new \Qii\Driver\Easy())->_initialize();
  481. //$table->setPrivateKey($privateKey);
  482. $table->setRules(new \Qii\Driver\Rules($rules));
  483. foreach ($value AS $key => $val) {
  484. $table->{$key} = $val;
  485. }
  486. try {
  487. $result = $table->_save();
  488. if ($result->isError()) {
  489. return $result;
  490. }
  491. return Response::Success('addTableData', '操作成功');
  492. } catch (\Exception $e) {
  493. $msg = strip_tags($e->getMessage());
  494. return Response::Instance(10010, 'addTableData', array('message' => $msg));
  495. }
  496. }
  497. /**
  498. * 备份指定数据表
  499. *
  500. * @param $database 数据库名称
  501. * @param $tableName 数据表名称
  502. * @return string
  503. */
  504. public function backupTable($database, $tableName)
  505. {
  506. $sql = "SELECT * FROM {$database}.{$tableName}";
  507. $rs = $this->db->setQuery($sql);
  508. $data = array();
  509. $backupSQL = "USE {$database};\n";
  510. $tableSQL = $this->getTableSQL($database, $tableName);
  511. if ($tableSQL['sql'] == '') _e('获取数据表错误', __LINE__);
  512. $backupSQL .= $tableSQL['sql'];
  513. $i = 0;
  514. $fields = array();
  515. while ($row = $this->db->fetch($rs)) {
  516. $fields = array_keys($row);
  517. $row = array_map('addslashes', $row);
  518. $data[] = '(\'' . join("','", $row) . '\')';
  519. if ($i == 500) {
  520. //执行一次合并操作
  521. $backupSQL .= "\nINSERT INTO {$database}.{$tableName}(`" . join('`, `', $fields) . "`) VALUES " . join(', ', $data) . ";\n";
  522. $data = array();
  523. $i = 0;
  524. } else {
  525. $i++;
  526. }
  527. }
  528. if (count($data) > 0) {
  529. $backupSQL .= "\nINSERT INTO {$database}.{$tableName}(`" . join('`, `', $fields) . "`) VALUES " . join(', ', $data) . ";\n";
  530. }
  531. return $backupSQL;
  532. }
  533. /**
  534. * 还原数据
  535. *
  536. * @param $database 数据库名
  537. * @param $tableName 数据表名
  538. * @param $fileName 文件名
  539. * @return array|void
  540. */
  541. public function restore($database, $tableName, $fileName)
  542. {
  543. $data = array();
  544. $data['code'] = 1;
  545. if (!$data || !$tableName || !$fileName) {
  546. $data['msg'] = '参数错误或文件错误';
  547. return $data;
  548. }
  549. $contents = file_get_contents($fileName);
  550. $tableSQL = explode(';', $contents);
  551. if (count($tableSQL) == 0) {
  552. $data['msg'] = '上传的文件无相关据';
  553. return $data;
  554. }
  555. try {
  556. foreach ($tableSQL AS $sql) {
  557. $sql = trim($sql);
  558. if ($sql == '') continue;
  559. $this->db->setQuery($sql);
  560. }
  561. $data['code'] = 0;
  562. $data['msg'] = '成功';
  563. } catch (\Exception $e) {
  564. $data['code'] = $e->getCode();
  565. $data['msg'] = strip_tags($e->getMessage());
  566. }
  567. return $data;
  568. }
  569. }