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