1 | <?php |
---|
2 | |
---|
3 | /* ----------------------------------------------------------------------------- |
---|
4 | class name: GPCTables |
---|
5 | class version: 1.5 |
---|
6 | date: 2010-03-28 |
---|
7 | ------------------------------------------------------------------------------ |
---|
8 | author: grum at piwigo.org |
---|
9 | << May the Little SpaceFrog be with you >> |
---|
10 | ------------------------------------------------------------------------------ |
---|
11 | |
---|
12 | this class provides base functions to manage tables while plugin installation |
---|
13 | - constructor GPCTables($tables) |
---|
14 | - (public) function create($tables_def) |
---|
15 | - (public) function updateTablesFields($tables_alteration) |
---|
16 | - (public) function drop() |
---|
17 | - (public) function rename($list) -v1.1 |
---|
18 | - (public) function exists() -v1.1 |
---|
19 | - (public) function export($filename, $options, $tables, $infos) -v1.3 |
---|
20 | - (public) function multi_queries($queries) -v1.3 |
---|
21 | - (public) function import($filename) -v1.3 |
---|
22 | |
---|
23 | ------------------------------------------------------------------------------ |
---|
24 | v1.1 + add rename($list) function |
---|
25 | + add exists() function |
---|
26 | v1.2 + add export($filename, $options, $tables) function |
---|
27 | v1.3 + modify export($filename, $options, $tables, $infos, $resultboolean) function |
---|
28 | + new parameters '$infos' allows to add some information on the |
---|
29 | exported file |
---|
30 | + add 'delete' and 'colnames' options |
---|
31 | + $resultbooelan option for return |
---|
32 | + add multi_queries($queries) function |
---|
33 | + add import($filename) function |
---|
34 | v1.4 - 2009-03-01 + PHP5 migration (use of public/protected/private) |
---|
35 | + export => DROP TABLE IF EXISTS |
---|
36 | + import => if DROP TABLE without IF EXISTS, add IF EXISTS |
---|
37 | v1.5 - 2010-03-28 + Uses piwigo pwg_db_* functions instead of mysql_* functions |
---|
38 | |
---|
39 | -------------------------------------------------------------------------- */ |
---|
40 | class GPCTables |
---|
41 | { |
---|
42 | public $tables; //array of tables names |
---|
43 | public $version = "1.5"; |
---|
44 | |
---|
45 | public function __construct($tables) |
---|
46 | { |
---|
47 | $this->tables = $tables; |
---|
48 | } |
---|
49 | |
---|
50 | public function __destruct() |
---|
51 | { |
---|
52 | unset($this->tables); |
---|
53 | unset($this->version); |
---|
54 | } |
---|
55 | |
---|
56 | |
---|
57 | /* |
---|
58 | create tables |
---|
59 | $tables_def is an array of SQL CREATE queries |
---|
60 | |
---|
61 | return true if everything is ok, otherwise tablename |
---|
62 | */ |
---|
63 | public function create($tables_def) |
---|
64 | { |
---|
65 | //deleting tables if exists |
---|
66 | $this->drop(); |
---|
67 | |
---|
68 | for($i=0;$i<count($tables_def);$i++) |
---|
69 | { |
---|
70 | $result=pwg_query($tables_def[$i]); |
---|
71 | if(!$result) |
---|
72 | { |
---|
73 | //if an error occurs, deleting created tables |
---|
74 | $this->drop(); |
---|
75 | return(false); |
---|
76 | } |
---|
77 | } |
---|
78 | return(true); |
---|
79 | } |
---|
80 | |
---|
81 | /* update tables definition |
---|
82 | $tables_alteration : array of arrays |
---|
83 | example : |
---|
84 | $tables_alteration['table1']=array( |
---|
85 | "attribute1" => " ADD COLUMN `attribute1` text null default ''", |
---|
86 | "attribute2" => " ADD COLUMN `attribute2` text null default ''")); |
---|
87 | $tables_alteration['table2']=array( |
---|
88 | "attribute1" => " ADD COLUMN `attribute1` text null default ''", |
---|
89 | "attribute2" => " ADD COLUMN `attribute2` text null default ''")); |
---|
90 | |
---|
91 | return true if no error, otherwise return table.fields of error |
---|
92 | */ |
---|
93 | public function updateTablesFields($tables_alteration) |
---|
94 | { |
---|
95 | if(!is_array($tables_alteration)) |
---|
96 | { |
---|
97 | return(''); |
---|
98 | } |
---|
99 | |
---|
100 | reset($tables_alteration); |
---|
101 | while (list($key, $val) = each($tables_alteration)) |
---|
102 | { |
---|
103 | $sql="SHOW COLUMNS FROM $key"; |
---|
104 | $result=pwg_query($sql); |
---|
105 | if($result) |
---|
106 | { |
---|
107 | $columns=array(); |
---|
108 | while($row=pwg_db_fetch_assoc($result)) |
---|
109 | { |
---|
110 | array_push($columns, $row['Field']); |
---|
111 | } |
---|
112 | |
---|
113 | reset($val); |
---|
114 | while (list($attname, $sql) = each($val)) |
---|
115 | { |
---|
116 | if(!in_array($attname, $columns)) |
---|
117 | { |
---|
118 | $result=pwg_query("ALTER TABLE `$key` ".$sql); |
---|
119 | if(!$result) |
---|
120 | { |
---|
121 | return($key.".".$attname); |
---|
122 | } |
---|
123 | } |
---|
124 | } |
---|
125 | } |
---|
126 | } |
---|
127 | return(true); |
---|
128 | } |
---|
129 | |
---|
130 | |
---|
131 | /* |
---|
132 | delete tables listed in $this->tables_list |
---|
133 | */ |
---|
134 | public function drop() |
---|
135 | { |
---|
136 | foreach($this->tables as $key => $table_name) |
---|
137 | { |
---|
138 | $sql="DROP TABLE IF EXISTS ".$table_name; |
---|
139 | $result=pwg_query($sql); |
---|
140 | } |
---|
141 | } |
---|
142 | |
---|
143 | /* |
---|
144 | rename tables name of list |
---|
145 | $list is an array('old_name' => 'new_name') |
---|
146 | return true if ok, else old table name |
---|
147 | */ |
---|
148 | public function rename($list) |
---|
149 | { |
---|
150 | $tmplist=array_flip($this->tables); |
---|
151 | foreach($list as $key => $val) |
---|
152 | { |
---|
153 | if(isset($tmplist[$key])) |
---|
154 | { |
---|
155 | $this->tables[$tmplist[$key]] = $val; |
---|
156 | $sql="ALTER TABLE `$key` RENAME TO `$val`"; |
---|
157 | if(!pwg_query($sql)) |
---|
158 | { |
---|
159 | return($key); |
---|
160 | } |
---|
161 | } |
---|
162 | else |
---|
163 | { |
---|
164 | return($key); |
---|
165 | } |
---|
166 | } |
---|
167 | return(true); |
---|
168 | } |
---|
169 | |
---|
170 | /* |
---|
171 | return true if all listed tables exists |
---|
172 | */ |
---|
173 | public function exists() |
---|
174 | { |
---|
175 | $list=array_flip($this->tables); |
---|
176 | $sql="SHOW TABLES"; |
---|
177 | $result=pwg_query($sql); |
---|
178 | if($result) |
---|
179 | { |
---|
180 | while($row=pwg_db_fetch_row($result)) |
---|
181 | { |
---|
182 | if(isset($list[$row[0]])) |
---|
183 | { |
---|
184 | array_splice($list, $row[0],1); |
---|
185 | } |
---|
186 | } |
---|
187 | } |
---|
188 | if(count($list)>0) |
---|
189 | { |
---|
190 | return(false); |
---|
191 | } |
---|
192 | else |
---|
193 | { |
---|
194 | return(true); |
---|
195 | } |
---|
196 | } |
---|
197 | |
---|
198 | /* |
---|
199 | export all tables as SQL in a text file |
---|
200 | |
---|
201 | each query end with a " -- EOQ" ; it's just a method to increase parsing for |
---|
202 | import function |
---|
203 | |
---|
204 | $filename : name of the file |
---|
205 | $options : array of options like |
---|
206 | array( |
---|
207 | 'drop' => true/false, //add DROP TABLE statements |
---|
208 | 'create' => true/false, //add CREATE TABLE statements |
---|
209 | 'insert' => true/false, //add INSERT statements |
---|
210 | 'delete' => true/false, //add delete statements |
---|
211 | 'colnames' => true/false, //add columns names for inserts statements |
---|
212 | ) |
---|
213 | $tables : array of tables names to export |
---|
214 | array('tablename1', 'tablenamen', 'tablename3', ...) |
---|
215 | if empty, assume that all tables have to be exported |
---|
216 | $infos : additional info written in exported file (as comment) |
---|
217 | $resultboolean : if true, result is true/false ; |
---|
218 | if false, if result, return a string with nfo about export |
---|
219 | */ |
---|
220 | public function export($filename, $options=array(), $tables=array(), $infos="", $resultboolean=true) |
---|
221 | { |
---|
222 | $defaultopt=array( |
---|
223 | 'drop' => true, |
---|
224 | 'create' => true, |
---|
225 | 'insert' => true, |
---|
226 | 'delete' => false, |
---|
227 | 'colnames' => false |
---|
228 | ); |
---|
229 | |
---|
230 | if(!isset($options['drop'])) |
---|
231 | { |
---|
232 | $options['drop']=$defaultopt['drop']; |
---|
233 | } |
---|
234 | if(!isset($options['create'])) |
---|
235 | { |
---|
236 | $options['create']=$defaultopt['create']; |
---|
237 | } |
---|
238 | if(!isset($options['insert'])) |
---|
239 | { |
---|
240 | $options['insert']=$defaultopt['insert']; |
---|
241 | } |
---|
242 | if(!isset($options['delete'])) |
---|
243 | { |
---|
244 | $options['delete']=$defaultopt['delete']; |
---|
245 | } |
---|
246 | if(!isset($options['colnames'])) |
---|
247 | { |
---|
248 | $options['colnames']=$defaultopt['colnames']; |
---|
249 | } |
---|
250 | if(count($tables)==0) |
---|
251 | { |
---|
252 | $tables=$this->tables; |
---|
253 | } |
---|
254 | |
---|
255 | $resultnfo=''; |
---|
256 | |
---|
257 | $returned=true; |
---|
258 | $text=' |
---|
259 | -- *************************************************************** -- EOQ |
---|
260 | -- * SQL export made with Grum Plugins Classes (Export tool r'.$this->version.') -- EOQ |
---|
261 | -- * Export date :'.date('Y-m-d H:i:s').' -- EOQ |
---|
262 | -- * Export options :'; |
---|
263 | if($options['drop']){$text.=' [drop]';} |
---|
264 | if($options['delete']){$text.=' [delete]';} |
---|
265 | if($options['create']){$text.=' [create]';} |
---|
266 | if($options['insert']){$text.=' [insert]';} |
---|
267 | if($options['colnames']){$text.=' [colnames]';} |
---|
268 | $text.=" -- EOQ"; |
---|
269 | if($infos!="") |
---|
270 | { |
---|
271 | $text.=' |
---|
272 | -- * '.$infos." -- EOQ"; |
---|
273 | } |
---|
274 | $text.=' |
---|
275 | -- *************************************************************** -- EOQ |
---|
276 | |
---|
277 | '; |
---|
278 | foreach($tables as $key => $val) |
---|
279 | { |
---|
280 | $countelems=0; |
---|
281 | |
---|
282 | $text.=" |
---|
283 | |
---|
284 | -- *************************************************************** -- EOQ |
---|
285 | -- * Statements for ".$this->tables[$key]." table -- EOQ |
---|
286 | -- *************************************************************** -- EOQ |
---|
287 | "; |
---|
288 | |
---|
289 | if($options['drop']) |
---|
290 | { |
---|
291 | $text.=sprintf("DROP TABLE IF EXISTS `%s`; -- EOQ\n", $this->tables[$key]); |
---|
292 | } |
---|
293 | |
---|
294 | if($options['delete']) |
---|
295 | { |
---|
296 | $text.=sprintf("DELETE FROM `%s`; -- EOQ\n", $this->tables[$key]); |
---|
297 | } |
---|
298 | |
---|
299 | if($options['create']) |
---|
300 | { |
---|
301 | $sql='SHOW CREATE TABLE '.$this->tables[$key]; |
---|
302 | $result=pwg_query($sql); |
---|
303 | if($result) |
---|
304 | { |
---|
305 | while($row=pwg_db_fetch_row($result)) |
---|
306 | { |
---|
307 | $text.=sprintf("%s; -- EOQ\n", $row[1]); |
---|
308 | } |
---|
309 | } |
---|
310 | else |
---|
311 | { |
---|
312 | $returned=false; |
---|
313 | } |
---|
314 | } |
---|
315 | |
---|
316 | if($options['insert']) |
---|
317 | { |
---|
318 | $colnames=""; |
---|
319 | if($options['colnames']) |
---|
320 | { |
---|
321 | $sql='SHOW COLUMNS FROM `'.$this->tables[$key].'`'; |
---|
322 | $result=pwg_query($sql); |
---|
323 | if($result) |
---|
324 | { |
---|
325 | $tmp=array(); |
---|
326 | while($row=pwg_db_fetch_row($result)) |
---|
327 | { |
---|
328 | $tmp[]=$row[0]; |
---|
329 | } |
---|
330 | } |
---|
331 | $colnames='('.implode(',', $tmp).')'; |
---|
332 | } |
---|
333 | |
---|
334 | $sql='SELECT * FROM '.$this->tables[$key]; |
---|
335 | $result=pwg_query($sql); |
---|
336 | if($result) |
---|
337 | { |
---|
338 | while($row=pwg_db_fetch_row($result)) |
---|
339 | { |
---|
340 | foreach($row as $key2 => $val2) |
---|
341 | { |
---|
342 | $row[$key2]="'".addslashes($val2)."'"; |
---|
343 | } |
---|
344 | $text.=sprintf("INSERT INTO `%s` %s VALUES(%s); -- EOQ\n", $this->tables[$key], $colnames, implode(', ', $row)); |
---|
345 | $countelems++; |
---|
346 | } |
---|
347 | } |
---|
348 | else |
---|
349 | { |
---|
350 | $returned=false; |
---|
351 | } |
---|
352 | $resultnfo.=$key.':'.$countelems.'@'; |
---|
353 | } |
---|
354 | } |
---|
355 | $fhandle=fopen($filename, 'wb'); |
---|
356 | if($fhandle) |
---|
357 | { |
---|
358 | fwrite($fhandle, $text); |
---|
359 | fclose($fhandle); |
---|
360 | } |
---|
361 | else |
---|
362 | { |
---|
363 | $returned=false; |
---|
364 | } |
---|
365 | if(($resultboolean==false)&&($returned)) |
---|
366 | { |
---|
367 | $returned=$resultnfo; |
---|
368 | } |
---|
369 | return($returned); |
---|
370 | } |
---|
371 | |
---|
372 | /* |
---|
373 | import an .sql file |
---|
374 | $filename : name of the file |
---|
375 | 'errors' : -1 file don't exists |
---|
376 | -2 can't open file |
---|
377 | */ |
---|
378 | public function import($filename) |
---|
379 | { |
---|
380 | $return = array( |
---|
381 | 'numinsert'=>0, |
---|
382 | 'numdelete'=>0, |
---|
383 | 'numdrop'=>0, |
---|
384 | 'numcreate'=>0, |
---|
385 | 'errors'=>array(), |
---|
386 | 'total_ok'=>0 |
---|
387 | ); |
---|
388 | |
---|
389 | if(file_exists($filename)) |
---|
390 | { |
---|
391 | $fhandle=fopen($filename, 'r'); |
---|
392 | if($fhandle) |
---|
393 | { |
---|
394 | $queries=fread($fhandle, filesize($filename)); |
---|
395 | fclose($fhandle); |
---|
396 | $return=$this->multi_queries($queries); |
---|
397 | } |
---|
398 | else |
---|
399 | { |
---|
400 | $return['errors']=-2; |
---|
401 | } |
---|
402 | } |
---|
403 | else |
---|
404 | { |
---|
405 | $return['errors']=-1; |
---|
406 | } |
---|
407 | return($return); |
---|
408 | } |
---|
409 | |
---|
410 | /* |
---|
411 | execute multiple query |
---|
412 | each query have to be separated by a "-- EOQ\n" |
---|
413 | |
---|
414 | $queries : sql queries |
---|
415 | */ |
---|
416 | public function multi_queries($queries) |
---|
417 | { |
---|
418 | $queries_list=preg_split( |
---|
419 | '/\s*;?\s*--\s+EOQ[\r\n]{1}/i', $queries, -1, PREG_SPLIT_NO_EMPTY); |
---|
420 | |
---|
421 | $return = array( |
---|
422 | 'numinsert'=>0, |
---|
423 | 'numdelete'=>0, |
---|
424 | 'numdrop'=>0, |
---|
425 | 'numcreate'=>0, |
---|
426 | 'errors'=>array(), |
---|
427 | 'total_ok'=>0 |
---|
428 | ); |
---|
429 | |
---|
430 | $i=0; |
---|
431 | foreach($queries_list as $key => $sql) |
---|
432 | { |
---|
433 | $i++; |
---|
434 | |
---|
435 | // replace " DROP TABLE `xxxx`; " by " DROP TABLE IF EXISTS `xxxx`; " |
---|
436 | $sql=preg_replace("/(.*DROP TABLE)\s*(`.*)/i", "$1 IF EXISTS $2", $sql); |
---|
437 | |
---|
438 | @$result=pwg_query($sql); |
---|
439 | if($result) |
---|
440 | { |
---|
441 | $return['total_ok']++; |
---|
442 | if(preg_match('/\b[\s]*insert[\s]+/i', $sql)>0) |
---|
443 | {$return['numinsert']++;} |
---|
444 | elseif(preg_match('/\b[\s]*drop[\s]+/i', $sql)>0) |
---|
445 | {$return['numdrop']++;} |
---|
446 | elseif(preg_match('/\b[\s]*delete[\s]+/i', $sql)>0) |
---|
447 | {$return['numdelete']++;} |
---|
448 | elseif(preg_match('/\b[\s]*create[\s]+/i',$sql)>0) |
---|
449 | {$return['numcreate']++;} |
---|
450 | } |
---|
451 | else |
---|
452 | { |
---|
453 | array_push($return['errors'], '['.$i.'] '.$sql); |
---|
454 | } |
---|
455 | } |
---|
456 | return($return); |
---|
457 | } |
---|
458 | |
---|
459 | } //class |
---|
460 | |
---|
461 | |
---|
462 | ?> |
---|