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 {
56 if (!HTML_FlexyFramework::get()->cli) {
57 die("Access only permitted from cli");
62 var $deps = array(); // list of dependants
63 var $out = array(); // list of created sql/shell scripts.
67 $argv = $_SERVER['argv'];
71 $opts = explode(',', 'table==,where==,dump-dir==');
72 require_once 'Console/Getopt.php';
73 $go = Console_Getopt::getopt2($argv, '', $opts );
78 foreach($go[0] as $ar) {
79 $args[substr($ar[0],2)] = $ar[1];
82 foreach($opts as $req) {
83 if (empty($args[substr($req,0, -2)])) {
84 $errs[] = "--".substr($req,0, -2) . ' is required';
88 die(print_R($errs,true));
93 $this->discoverChildren($this->args['table'], $this->args['where'], true);
94 //print_R($this->deletes);
95 //print_r($this->dumps);
98 $this->discover($this->args['table'], $this->args['where'], true);
101 if (!file_exists($args['dump-dir'])) {
102 mkdir($args['dump-dir'], 0777, true);
105 $this->generateInsert();
106 $this->generateDelete();
107 $this->generateShell();
109 echo "GENERATED FILES:";
117 var $deletes = array(); // TABLE => [key] => TRUE|FALSE
118 var $dumps = array(); // TABLE => [key] => TRUE|FALSE - if it's been scanned..
119 var $dscan = array(); // TABLE:COL => [value => TRUE|FALSE] - if its been scanned..
122 * a) what depends on it (eg. child elements) - which will be deleted.
123 * b) what it depends on it (eg. parent elements) - which will be dumped..
126 function discover($table, $where, $is_delete = false )
129 if (!isset($this->dumps[$table])) {
130 $this->dumps[$table] = array();
132 if ($is_delete && !isset($this->deletes[$table])) {
133 $this->deletes[$table] = array();
135 //DB_DataObject::debugLevel(1);
136 $x = DB_DataObject::factory($table);
137 if (PEAR::isError($x)) {
138 if (isset($this->dumps[$table])) {
139 unset($this->dumps[$table]); // links to non-existant tables..
146 if (is_array( $where)) {
147 $x->whereAddIn($keys[0] , $where, 'int');
150 $x->whereAdd($where);
153 // a) id's of elements in this table
154 // b) columns which point to other tables..
155 $links = $x->links();
156 $cols = array_keys($links);
158 array_push($cols, $keys[0]);
162 $x->selectAdd('`'. implode('`,`', $cols) . '`');
164 //DB_DataObject::debugLevel(0);
165 while ($x->fetch()) {
166 foreach($cols as $k) {
167 if (empty($x->$k)) { // skip blanks.
170 if (isset($links[$k])) {
172 $kv = explode(':', $links[$k]);
173 if (!isset($this->dumps[$kv[0]])) {
174 $this->dumps[$kv[0]] = array();
176 if (!isset($this->dumps[$kv[0]][$x->$k])) {
177 $this->dumps[$kv[0]][$x->$k] = 0; // not checked yet..
181 // assume it's the key..
182 if (empty($this->dumps[$table][$x->$k])) {
183 $this->dumps[$table][$x->$k] = 1; // we have checked this one...
185 //if ($is_delete && !isset($this->deletes[$table][$x->$k])) {
187 // $this->deletes[$table][$x->$k] = 0 ; // not checked yet..
194 // flag as checked if we where given an array.. - as some links might have been broken.
195 if (is_array($where)) {
196 foreach($where as $k) {
197 $this->dumps[$table][$k] = 1;
202 // itterate through dumps to find what needs discovering
203 foreach($this->dumps as $k=>$v) {
205 foreach($v as $id => $fetched) {
211 $this->discover($k, $ar,false);
223 function discoverChildren($table, $where, $col=false )
225 global $_DB_DATAOBJECT;
226 $do = DB_DataObject::factory($table);
227 if (PEAR::isError($do)) {
228 if (isset($this->dumps[$table])) {
229 unset($this->dumps[$table]); // links to non-existant tables..
233 if (!isset($this->dumps[$table])) {
234 $this->dumps[$table] = array();
236 if (!isset($this->deletes[$table])) {
237 $this->deletes[$table] = array();
243 if (is_array( $where)) {
244 $do->whereAddIn($col ? $col : $keys[0] , $where, 'int');
247 $do->whereAdd($where);
250 static $children = array();
252 if (!isset($children[$table])) {
254 // force load of linsk
256 foreach($_DB_DATAOBJECT['LINKS'][$do->database()] as $tbl => $links) {
257 // hack.. - we should get rid of this hack..
258 if ($tbl == 'database__render') {
261 //if ($tbl == $tn) { // skip same table
264 foreach ($links as $tk => $kv) {
267 list($k,$v) = explode(':', $kv);
271 $add = implode(':', array($tbl, $tk));
272 //echo "ADD $tbl $tk=>$kv : $add\n";
273 $children[$table][$add] = true;
279 if (empty($children[$table])) {
283 // DB_DataObject::debugLevel(1);
286 $do->selectAdd($key);
288 while ($do->fetch()) {
289 $this->dumps[$table][$do->$key] = 0;
290 if (!isset($this->deletes[$table][$do->$key])) {
291 $this->deletes[$table][$do->$key] = 0;
294 foreach($children[$table] as $kv=>$t) {
295 if (!isset($this->dscan[$kv])) {
296 $this->dscan[$kv] = array();
298 if (!isset($this->dscan[$kv][$do->$key])) {
299 $this->dscan[$kv][$do->$key]= 0; // unscanned.
305 // now iterate throught dependants. and scan them.
308 foreach($this->dscan as $kv => $ids) {
310 foreach($ids as $id => $checked) {
312 $this->dscan[$kv][$id] = 1; // flag it as checked.
321 list($k, $v) = explode(':', $kv);
322 $this->discoverChildren($k, $ar, $v);
331 function generateDelete() {
332 $target = $this->args['dump-dir'] .'/'. date('Y-m-d').'.delete.sql';
333 $this->out[] = $target;
334 $fh = fopen($target, 'w');
335 foreach($this->deletes as $tbl=>$ar) {
336 $do = DB_DataObject::factory($tbl);
339 foreach($ar as $id => $deleted) {}
340 fwrite($fh, "DELETE FROM `$tbl` WHERE `$key` = $id;\n"); // we assume id's and nice column names...
345 function generateShell() {
348 $target = $this->args['dump-dir'] .'/'. date('Y-m-d').'.copy.sh';
349 $this->out[] = $target;
350 $fh = fopen($target, 'w');
352 $target = $this->args['dump-dir'] .'/'. date('Y-m-d').'.delete.sh';
353 $this->out[] = $target;
354 $fh2 = fopen($target, 'w');
356 $target = $this->args['dump-dir'] .'/'. date('Y-m-d').'.restore.sh';
357 $this->out[] = $target;
358 $fh3 = fopen($target, 'w');
361 foreach($this->childfiles as $s=>$v) {
362 fwrite($fh,"mkdir -p " . escapeshellarg(dirname($args['dump-dir'] .'/'.$v[1])) ."\n" );
363 fwrite($fh,"cp " . escapeshellarg($v[0].'/'.$v[1]) . ' ' . escapeshellarg($args['dump-dir'] .'/'.$v[1]) ."\n" );
365 fwrite($fh3,"mkdir -p " . escapeshellarg(dirname($v[0].'/'.$v[1])) ."\n" );
366 fwrite($fh3,"cp " . escapeshellarg($args['dump-dir'] .'/'.$v[1]) . ' ' . escapeshellarg($v[0].'/'.$v[1]) . "\n" );
368 fwrite($fh2,"rm " . escapeshellarg($v[0].'/'.$v[1]) ."\n" );
371 fclose($fh3); // restore does not need to bother with thumbnails.
375 foreach($this->childthumbs as $s=>$v) {
377 fwrite($fh2,"rm " . escapeshellarg($vv). "\n");
383 function generateInsert()
385 $target = $this->args['dump-dir'] .'/'. date('Y-m-d').'.sql';
386 $this->out[] = $target;
387 $fh = fopen($target,'w');
392 foreach($this->dumps as $tbl => $ar) {
396 $do = DB_DataObject::factory($table);
400 $do->whereAddIn($keys[0] , $ar, 'int');
402 while ($do->fetch()) {
403 fwrite($fh,$this->toInsert($do));
416 * toInsert - does not handle NULLS...
418 function toInsert($do, $ar)
420 $kcol = array_shift($do->keys());
422 // for auto_inc column we need to use a 'set argument'...
423 $items = $do->table();
425 $quoteIdentifiers = true; //!empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
432 foreach( $items as $k=>$v)
439 $leftq .= ($quoteIdentifiers ? ($DB->quoteIdentifier($k) . ' ') : "$k ");
444 if ($v & DB_DATAOBJECT_STR) {
445 $rightq .= $do->_quote((string) (
446 ($v & DB_DATAOBJECT_BOOL) ?
447 // this is thanks to the braindead idea of postgres to
448 // use t/f for boolean.
449 (($do->$k === 'f') ? 0 : (int)(bool) $do->$k) :
454 if (is_numeric($do->$k)) {
455 $rightq .=" {$do->$k} ";
458 $rightq .= ' ' . intval($do->$k) . ' ';
460 $table = ($quoteIdentifiers ? $DB->quoteIdentifier($do->__table) : $do->__table);
461 return "INSERT INTO {$table} ($leftq) VALUES ($rightq);\n";