1. <?php
2. /**
3. * 数据库控类库
4. * @author [我就叫小柯] <[972581428@qq.com]>
5. * @copyright Copyright (c) 2020 [环企优站科技] (https://www.h7uz.com)
6. * @version v1.0
7. */
8. namespace databases;
9. use think\Db;
10. class Databases{
11. private $db = '';
12. private $datadir = '';//数据库备份地址
13. private $startrow = 0;
14. private $startfrom = 0;
15. private $complete = true;
16.
17. /**
18. * 构造函数
19. * @param string 公钥文件（验签和加密时传入）
20. * @param string 私钥文件（签名和解密时传入）
21. */
22. public function __construct($dir){
23. $this->db = Db::getConnection();
24. $this->check_dir($dir);
25. }
26.
27.
28. protected function check_dir($datadir){
29. if(!is_dir($datadir)){
30. mkdir($datadir,0755,true);
31. }
32. $this->datadir = $datadir;
33. return $this;
34. }
35.
36. /**
37. * 当前数据库表
38. * @param $db_prefix 表前缀
39. * @return array
40. */
41. public function db_list($db_prefix){
42. return $this->db->query("SHOW TABLE STATUS LIKE '".$db_prefix."%'");
43. }
44.
45. //导入数据库
46. public function backup($data,$tabelsarr,$file){
47. $tableid = $data['tableid'];
48. $this->startfrom = $data['startfrom'];
49. $sizelimit = $data['sizelimit']; //分卷大小
50. $volume = $data['volume'];
51. //查询
52. $dataList = $this->db_list($data['db_prefix']);
53. foreach ($dataList as $row){
54. $table_info[$row['Name']] = $row;
55. }
56. $tables = cache('backuptables');
57. if(empty($tabelsarr) && empty($tables)) {
58. foreach ($dataList as $row){
59. $tables[]= $row['Name'];
60. }
61. }else{
62. $tables = [];
63. if(!$tableid) {
64. $tables = $tabelsarr;
65. cache('backuptables',$tables);
66. } else {
67. $tables = cache('backuptables');
68. }
69. if( !is_array($tables) || empty($tables)) {
70. return ['status'=>0,'msg'=>lang('do_empty')];
71. }
72. }
73. unset($dataList);
74. $sql = '';
75. if(!$tableid) {
76. $sql .= "-- Usezanphp SQL Backup\n-- Time:".toDate(time())."\n-- https://www.h7uz.com \n\n";
77. foreach($tables as $key=>$table) {
78. $sql .= "--\n-- Usezanphp Table `$table`\n-- \n";
79. $sql .= "DROP TABLE IF EXISTS `$table`;\n";
80. $info = $this->db->query("SHOW CREATE TABLE $table");
81. $sql .= str_replace(array('USING BTREE','ROW_FORMAT=DYNAMIC'),'',$info[0]['Create Table']).";\n";
82. }
83. }
84. for(; $this->complete && $tableid < count($tables) && strlen($sql) + 500 < $sizelimit * 1000; $tableid++) {
85. if($table_info[$tables[$tableid]]['Rows'] > 0){
86. $sql .= $this->dumptablesql($tables[$tableid], $this->startfrom, strlen($sql),$table_info[$tables[$tableid]]['Auto_increment']);
87. if($this->complete) {
88. $this->startfrom = 0;
89. }
90. }
91. }
92. !$this->complete && $tableid--;
93. $filename = htmlspecialchars(strip_tags($file));
94. $filename = !$filename ? 'Usezan_'.rand_string(5).'_'.date('YmdH') : $filename;
95. $filename_valume = sprintf($filename."-%s".'.sql', $volume);
96. if(trim($sql)){
97. $putfile = $this->datadir . $filename_valume;
98. $r = file_put_contents($putfile , trim($sql));
99. }
100. if($tableid < count($tables) || $r){
101. return ['status'=>200,'msg'=>"备份数据库".$filename_valume."成功"];
102. }else{
103. cache('backuptables',null);
104. return ['status'=>200,'msg'=>lang('do_ok')];
105. }
106. }
107.
108. /***
109. * 恢复-删除
110. * @param $do
111. * @param $files
112. */
113. public function db_recover($do,$files,$filename,$db_prefix=null){
114. switch ($do) {
115. case 'del':
116. if (!empty($files) && is_array($files)) {
117. foreach ($files as $r){
118. @unlink($r);
119. }
120. return ['status'=>200,'msg'=>'删除数据库成功(ˇˍˇ)'];
121. } else {
122. return ['status'=>200,'msg'=>'请选择要删除的数据库'];
123. }
124. break;
125. case 'import':
126. header('Content-Type:text/html;charset=UTF-8');
127. $filelist = $this->dir_list($this->datadir);
128. foreach ((array)$filelist as $r){
129. $file = explode('-',basename($r));
130. if($file[0] == $filename){
131. $files[] = $r;
132. }
133. }
134. $db_prefix = $db_prefix ? $db_prefix : config('database.prefix');
135. foreach((array)$files as $file){
136. //读取数据文件
137. $sqldata = file_get_contents($file);
138. $sqlFormat = $this->sql_split($sqldata, $db_prefix);
139. foreach ((array)$sqlFormat as $sql){
140. $sql = trim($sql);
141. if (strstr($sql, 'CREATE TABLE')){
142. preg_match('/CREATE TABLE `([^ ]*)`/', $sql, $matches);
143. $ret =$this->excuteQuery($sql);
144. }else{
145. $ret = $this->excuteQuery($sql);
146. }
147. }
148. return ['status'=>200,'msg'=>'恢复数据库成功'];
149. }
150. break;
151. }
152. }
153.
154. /**
155. * 数据库修复、分析
156. * @param $tables
157. * @param $do
158. * @return array
159. */
160. public function docommand($tables,$do){
161. if(empty($tables)) return ['status'=>0,'msg'=>'请勾选数据表!'];
162. $tables = implode('`,`',$tables);
163. $r = $this->db->query("{$do} TABLE `{$tables}`");
164. if(false != $r){
165. return ['status'=>200,'msg'=>$do.'数据表成功!'];
166. }else{
167. return ['status'=>0,'msg'=>$r['dberror']];
168. }
169. }
170.
171. /**
172. * 下载
173. * @param $filename
174. */
175. public function db_download($filename) {
176. if (strstr($filename,'sql')) {
177. $path = $this->datadir.$filename;
178. } else {
179. $path = $this->datadir.$filename.'.sql';
180. }
181. @header("Content-disposition:attachment;filename=".$filename);
182. @header("Content-type:application/octet-stream");
183. @header("Accept-Ranges: bytes");
184. @header("Content-Length:".filesize($path));
185. @header("Pragma:no-cache");
186. @header("Expires:0");
187. readfile($path);
188. }
189.
190. /***
191. * 恢复操作
192. * @param string $sql
193. * @return mixed
194. */
195. protected function excuteQuery($sql='') {
196. if(empty($sql)) return false;
197. $queryType = 'INSERT|UPDATE|DELETE|REPLACE|CREATE|DROP|LOAD DATA|SELECT .* INTO|COPY|ALTER|GRANT|TRUNCATE|REVOKE|LOCK|UNLOCK';
198. if (preg_match('/^\s*"?(' . $queryType . ')\s+/i', $sql)) {
199. $data['result'] = $this->db->execute($sql);
200. $data['type'] = 'execute';
201. }else {
202. $data['result'] = $this->db->query($sql);
203. $data['type'] = 'query';
204. }
205. return $data;
206. }
207.
208. //组合、检查Sql 语句
209. protected function dumptablesql($table, $startfrom = 0, $currsize = 0,$auto_increment=0) {
210. $offset = 300;
211. $insertsql = '';
212. $sizelimit = intval(input('param.sizelimit'));
213. $modelname = str_replace(config('database.prefix'),'',$table);
214. $model = Db::name($modelname);
215. $keyfield = $model->getPk();
216. $rows = $offset;
217. while($currsize + strlen($insertsql) + 500 < $sizelimit * 1000 && $rows == $offset) {
218. if($auto_increment) {
219. $selectsql = "SELECT * FROM $table WHERE $keyfield > $startfrom ORDER BY $keyfield LIMIT $offset";
220. } else {
221. $selectsql = "SELECT * FROM $table LIMIT $startfrom, $offset";
222. }
223. $tabledumped = 1;
224. $row = $this->db->query($selectsql);
225. $rows = count($row);
226. foreach($row as $key=>$val) {
227. foreach ($val as $k=>$field){
228. if(is_string($field)) {
229. $val[$k] = '\''. str_ireplace("'", "''", $field).'\''; //SQL指令安全过滤
230. } elseif($field === 0) {
231. $val[$k] = 0;
232. } elseif ($field == null){
233. $val[$k] = 'NULL';
234. }
235. }
236. if($currsize + strlen($insertsql) + 500 < $sizelimit * 1000) {
237. if($auto_increment) {
238. $startfrom = $row[$key][$keyfield];
239. } else {
240. $startfrom++;
241. }
242. $insertsql .= "INSERT INTO `$table` VALUES (".implode(',', $val).");\n";
243. } else {
244. $this->complete = false;
245. break 2;
246. }
247. }
248. }
249. $this->startfrom = $startfrom;
250. return $insertsql;
251. }
252.
253. /**
254. * [导入数据库]
255. * @param [type] $sql [数据库]
256. * @param [type] $tablepre [数据表]
257. * @return [type]
258. */
259. protected function sql_split($sql,$tablepre) {
260. //检查表前缀
261. if($tablepre != "usezan_") $sql = str_replace("usezan_", $tablepre, $sql);
262.
263. $sql = str_replace("\r", "\n", $sql);
264. $ret = [];
265. $num = 0;
266. $queriesarray = explode(";\n", trim($sql));
267. unset($sql);
268. foreach($queriesarray as $query){
269. $ret[$num] = '';
270. $queries = explode("\n", trim($query));
271. $queries = array_filter($queries);
272. foreach($queries as $queryv){
273. $str1 = substr($queryv, 0, 1);
274. if($str1 != '#' && $str1 != '-') $ret[$num] .= $queryv;
275. }
276. $num++;
277. }
278. return $ret;
279. }
280.
281. /**
282. * [检查文件或者目录]
283. * @param [type] $path [文件路径]
284. * @param string $exts [类型]
285. * @param array $list [返回数组]
286. * @return [type]
287. */
288. public function dir_list($path, $exts = '', $d = '', $list= array()) {
289. $path = $this->dir_path($path);
290. $files = glob($path.'*');
291. foreach($files as $v) {
292. $fileext = $this->fileext($v);
293. if (!$exts || preg_match("/\.($exts)/i", $v)) {
294. $list[] = $v;
295. if (is_dir($v)) {
296. $list = $this->dir_list($v, $exts, $list);
297. if($d && count(scandir($v)) == 2){
298. @rmdir($v);
299. }
300. }
301. }
302. }
303. return $list;
304. }
305.
306. /**
307. * [检查目录是否合法]
308. * @param [type] $path [目录路径]
309. * @return [type]
310. */
311. protected function dir_path($path) {
312. $path = str_replace('\\', '/', $path);
313. if(substr($path, -1) != '/') $path = $path.'/';
314. return $path;
315. }
316.
317. /**
318. * [检查文件名称]
319. * @param [type] $filename [文件名称]
320. * @return [type]
321. */
322. protected function fileext($filename) {
323. return strtolower(trim(substr(strrchr($filename, '.'), 1, 10)));
324. }
325.
326.
327. }
