5 * This should allow you to dump a series of data, so it could be restored later...
6 * The format will be an SQL file...
9 * php index.php Admin/Dump --table=Project --where="id=123"
10 * --dump-dir=/directory_to_put_sql+shell files
12 * outputs list of generated files.
15 * {DATE}.sql - the recreate sql including all dependancies, run with mysql DB -f < ....
16 * {DATE}.restore.sh - shell script to recreate files that where removed (excluding thumgs)
19 * {DATE}.copy.sh - shell script that backs up all the related files.
22 * {DATE}.delete.sql - the delete data sql.
23 * {DATE}.delete.sh - shell script to delete the files related to these records
26 * Basically it has to output all the records and their dependants. (parent and children)
28 * Then when deleting it deletes record + children..
31 * Each ouput is simple insert statement..
34 * TODO - handle Images table (or similar) where we use tablename=XXXX, tid=.... etc..
39 * DataObjects->archivePaths() - returns array ( sourcedirectory, remainder of path to dependant file )
40 * DataObjects->listThumbs() - returns array ( list of full path to thumbnail urls. )
49 require_once 'Pman.php';
51 class Pman_Admin_Dump extends Pman {
53 static $cli_desc = "Dump database ?/ needs more info...";
59 if (!HTML_FlexyFramework::get()->cli) {
60 die("Access only permitted from cli");
65 var $deps = array(); // list of dependants
66 var $out = array(); // list of created sql/shell scripts.
70 ini_set('memory_limit', '256M'); // we need alot of memory
73 $argv = $_SERVER['argv'];
77 $opts = explode(',', 'table==,where==,dump-dir==,debug=');
78 require_once 'Console/Getopt.php';
79 $go = Console_Getopt::getopt2($argv, '', $opts );
84 foreach($go[0] as $ar) {
85 $args[substr($ar[0],2)] = $ar[1];
88 foreach($opts as $req) {
89 if (substr($req,-2, 2) != '==') { // skip optional arguments
92 if (empty($args[substr($req,0, -2)])) {
93 $errs[] = "--".substr($req,0, -2) . ' is required';
97 die(print_R($errs,true));
99 if (!empty($args['debug'])) {
100 DB_DataObject::debugLevel($args['debug']);
103 $this->out = array();
104 $this->discoverChildren($this->args['table'], $this->args['where'], true);
105 //print_R($this->deletes);
106 //print_r($this->dumps);
109 $this->discover($this->args['table'], $this->args['where'], true);
112 if (!file_exists($args['dump-dir'])) {
113 mkdir($args['dump-dir'], 0777, true);
116 $this->generateInsert();
117 $this->generateDelete();
118 $this->generateShell();
121 foreach($this->deletes as $tbl => $ar) {
122 if (empty($ar)) { continue; }
123 echo " " .$tbl . ' -> ' . count(array_keys($ar)) . " Records\n";
126 foreach($this->dumps as $tbl => $ar) {
127 if (empty($ar)) { continue; }
128 echo " " .$tbl . ' -> ' . count(array_keys($ar)) . " Records\n";
131 echo " Total : " . $this->filetotal . " files using " . floor($this->filesize/1000000) . "Mb\n";
133 echo "GENERATED FILES:\n";
135 echo " ". implode("\n ", $this->out). "\n";
144 var $deletes = array(); // TABLE => [key] => TRUE|FALSE
145 var $dumps = array(); // TABLE => [key] => TRUE|FALSE - if it's been scanned..
146 var $dscan = array(); // TABLE:COL => [value => TRUE|FALSE] - if its been scanned..
147 var $childfiles = array(); // array of [ 'sourcedirectory' , 'subdirectory(s) and filename' ]
148 var $childthumbs = array(); // array of [ 'filename', 'filename' ,......]
149 var $filesize = 0; // size of files to be saved. (not total deletd..)
150 var $filetotal = 0; // number of distinct files to be saved (not total deleted)
153 * a) what depends on it (eg. child elements) - which will be deleted.
154 * b) what it depends on it (eg. parent elements) - which will be dumped..
157 function discover($table, $where, $is_delete = false )
160 if (!isset($this->dumps[$table])) {
161 $this->dumps[$table] = array();
163 if ($is_delete && !isset($this->deletes[$table])) {
164 $this->deletes[$table] = array();
166 //DB_DataObject::debugLevel(1);
167 $x = DB_DataObject::factory($table);
168 if (PEAR::isError($x)) {
169 if (isset($this->dumps[$table])) {
170 unset($this->dumps[$table]); // links to non-existant tables..
177 if (is_array( $where)) {
178 $x->whereAddIn($keys[0] , $where, 'int');
181 $x->whereAdd($where);
184 // a) id's of elements in this table
185 // b) columns which point to other tables..
186 $links = $x->links();
187 $cols = array_keys($links);
189 array_push($cols, $keys[0]);
193 $x->selectAdd('`'. implode('`,`', $cols) . '`');
195 //DB_DataObject::debugLevel(0);
196 while ($x->fetch()) {
197 foreach($cols as $k) {
198 if (empty($x->$k)) { // skip blanks.
201 if (isset($links[$k])) {
203 $kv = explode(':', $links[$k]);
204 if (!isset($this->dumps[$kv[0]])) {
205 $this->dumps[$kv[0]] = array();
207 if (!isset($this->dumps[$kv[0]][$x->$k])) {
208 $this->dumps[$kv[0]][$x->$k] = 0; // not checked yet..
212 // assume it's the key..
213 if (empty($this->dumps[$table][$x->$k])) {
214 $this->dumps[$table][$x->$k] = 1; // we have checked this one...
216 //if ($is_delete && !isset($this->deletes[$table][$x->$k])) {
218 // $this->deletes[$table][$x->$k] = 0 ; // not checked yet..
226 // flag as checked if we where given an array.. - as some links might have been broken.
227 if (is_array($where)) {
228 foreach($where as $k) {
229 $this->dumps[$table][$k] = 1;
234 // itterate through dumps to find what needs discovering
235 foreach($this->dumps as $k=>$v) {
237 foreach($v as $id => $fetched) {
243 $this->discover($k, $ar,false);
255 function discoverChildren($table, $where, $col=false )
257 echo "discoverChildren:$table:$col:". (is_array($where) ? (count($where) . " children" ): $where ). "\n";
258 global $_DB_DATAOBJECT;
259 $do = DB_DataObject::factory($table);
260 if (PEAR::isError($do)) {
261 if (isset($this->dumps[$table])) {
262 unset($this->dumps[$table]); // links to non-existant tables..
264 echo "SKIPPING invalid table $table\n";
267 if (!isset($this->dumps[$table])) {
268 $this->dumps[$table] = array();
270 if (!isset($this->deletes[$table])) {
271 $this->deletes[$table] = array();
277 if (is_array( $where)) {
278 $do->whereAddIn($col ? $col : $keys[0] , $where, 'int');
281 $do->whereAdd($where);
284 static $children = array();
286 if (!isset($children[$table])) {
287 $children[$table] = array();
288 // force load of linsk
290 foreach($_DB_DATAOBJECT['LINKS'][$do->database()] as $tbl => $links) {
291 // hack.. - we should get rid of this hack..
292 if ($tbl == 'database__render') {
295 //if ($tbl == $tn) { // skip same table
298 foreach ($links as $tk => $kv) {
301 list($k,$v) = explode(':', $kv);
305 $add = implode(':', array($tbl, $tk));
306 //echo "ADD $tbl $tk=>$kv : $add\n";
307 $children[$table][$add] = true;
317 $do->selectAdd($key);
318 echo "GOT ". $do->find() ." results\n";
319 //DB_DataObject::debugLevel(0);
320 while ($do->fetch()) {
321 $this->dumps[$table][$do->$key] = 0;
322 if (!isset($this->deletes[$table][$do->$key])) {
323 $this->deletes[$table][$do->$key] = 0;
326 foreach($children[$table] as $kv=>$t) {
327 if (!isset($this->dscan[$kv])) {
328 $this->dscan[$kv] = array();
330 if (!isset($this->dscan[$kv][$do->$key])) {
331 $this->dscan[$kv][$do->$key]= 0; // unscanned.
337 // now iterate throught dependants. and scan them.
340 foreach($this->dscan as $kv => $ids) {
342 foreach($ids as $id => $checked) {
344 $this->dscan[$kv][$id] = 1; // flag it as checked.
353 list($k, $v) = explode(':', $kv);
354 $this->discoverChildren($k, $ar, $v);
363 function generateDelete() {
364 $target = $this->args['dump-dir'] .'/'. date('Y-m-d').'.delete.sql';
365 $this->out[] = $target;
366 $fh = fopen($target, 'w');
370 foreach($this->deletes as $tbl=>$ar) {
372 $do = DB_DataObject::factory($tbl);
373 $tbl = $do->tableName();
376 $do->whereAddIn($keys[0] , array_keys($ar), 'int');
378 $archivePaths = method_exists($do,'archivePaths');
379 $listThumbs = method_exists($do,'listThumbs');
380 while ($do->fetch()) {
383 $ct = $do->archivePaths();
385 $this->childfiles[] = $ct;
389 $ct = $do->listThumbs();
391 $this->childthumbs[] = $ct;
396 fwrite($fh, "DELETE FROM `$tbl` WHERE `$key` = $id;\n"); // we assume id's and nice column names...
402 function generateShell() {
404 if (empty($this->childfiles) && empty($this->childthumbs)) {
407 $target = $this->args['dump-dir'] .'/'. date('Y-m-d').'.copy.sh';
408 $this->out[] = $target;
409 $fh = fopen($target, 'w');
411 $target = $this->args['dump-dir'] .'/'. date('Y-m-d').'.delete.sh';
412 $this->out[] = $target;
413 $fh2 = fopen($target, 'w');
415 $target = $this->args['dump-dir'] .'/'. date('Y-m-d').'.restore.sh';
416 $this->out[] = $target;
417 $fh3 = fopen($target, 'w');
422 foreach($this->childfiles as $v) {
424 if (isset($done[$v[1]])) {
430 $this->filesize += filesize($v[0].'/'.$v[1]);
432 $fdir = dirname($this->args['dump-dir'] .'/'.$v[1]);
433 if (!isset($donedir[$fdir])) {
434 fwrite($fh,"mkdir -p " . escapeshellarg(dirname($this->args['dump-dir'] .'/'.$v[1])) ."\n" );
436 fwrite($fh,"cp " . escapeshellarg($v[0].'/'.$v[1]) . ' ' . escapeshellarg($this->args['dump-dir'] .'/'.$v[1]) ."\n" );
437 if (!isset($donedir[$fdir])) {
438 fwrite($fh3,"mkdir -p " . escapeshellarg(dirname($v[0].'/'.$v[1])) ."\n" );
440 $donedir[$fdir] = true;
442 fwrite($fh3,"cp " . escapeshellarg($this->args['dump-dir'] .'/'.$v[1]) . ' ' . escapeshellarg($v[0].'/'.$v[1]) . "\n" );
443 fwrite($fh2,"rm " . escapeshellarg($v[0].'/'.$v[1]) ."\n" );
446 fclose($fh3); // restore does not need to bother with thumbnails.
450 foreach($this->childthumbs as $v) {
452 fwrite($fh2,"rm " . escapeshellarg($vv). "\n");
458 function generateInsert()
460 $target = $this->args['dump-dir'] .'/'. date('Y-m-d').'.sql';
461 $this->out[] = $target;
462 $fh = fopen($target,'w');
466 foreach($this->dumps as $tbl => $ar) {
470 $do = DB_DataObject::factory($tbl);
474 $do->whereAddIn($keys[0] , array_keys($ar), 'int');
476 while ($do->fetch()) {
477 fwrite($fh,$this->toInsert($do));
490 * toInsert - does not handle NULLS...
492 function toInsert($do)
494 $kcol = array_shift($do->keys());
496 // for auto_inc column we need to use a 'set argument'...
497 $items = $do->table();
504 $table = $do->tableName();
507 foreach( $items as $k=>$v)
514 $leftq .= '`' . $k . '`';
519 if ($v & DB_DATAOBJECT_STR) {
520 $rightq .= $do->_quote((string) (
521 ($v & DB_DATAOBJECT_BOOL) ?
522 // this is thanks to the braindead idea of postgres to
523 // use t/f for boolean.
524 (($do->$k === 'f') ? 0 : (int)(bool) $do->$k) :
529 if (is_numeric($do->$k)) {
530 $rightq .=" {$do->$k} ";
533 $rightq .= ' ' . intval($do->$k) . ' ';
536 return "INSERT INTO `{$table}` ($leftq) VALUES ($rightq);\n";