From 395372ecf064e91d8182b66bb0d3f81e8e957b46 Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Thu, 28 Jul 2016 17:29:18 +0100 Subject: [PATCH] db.dmp tables sort TODO --- lib/php/db.php | 129 +++++++++++++++++++++++++++---------------------- 1 file changed, 70 insertions(+), 59 deletions(-) diff --git a/lib/php/db.php b/lib/php/db.php index b968fe7d..df01896a 100644 --- a/lib/php/db.php +++ b/lib/php/db.php @@ -193,24 +193,6 @@ class Db extends nb { return empty($this->conn) ? false : true; } -/* - function database() { - $rows = $this->db->query('PRAGMA database_list'); - } - - function execParams($sql,$params) { - - try { - $r = $this->conn->execParams($sql, $params); - } catch (PDOException $e) { - err(join(': ',array($e->getMessage(),"Db.execParam()",$sql))); - $r = null; - } - - return $r; - } -*/ - function exec($sql) { try { @@ -338,11 +320,6 @@ class Db extends nb { return $DB_TYPES[$type][$key]; } -# NB 04.07.16 public function all_tables() { -# NB 04.07.16 $tables= $this->tables(); -# NB 04.07.16 return $tables; -# NB 04.07.16 } - public function tables() { if (!isset($this->tables)) { @@ -358,13 +335,6 @@ class Db extends nb { return $this->tables; } -# NB 28.03.16 public function rowCount($st) { -# NB 28.03.16 if (!$st) return null; -# NB 28.03.16 if ($this->type != 'sqlite') return $st->rowCount(); -# NB 28.03.16 $sql = $st->queryString; -# NB 28.03.16 return $this->conn->query("SELECT count(*) FROM ($sql) count",PDO::FETCH_COLUMN,0)->fetch(); -# NB 28.03.16 } - function print_header($format) { if (empty($_SERVER['DOCUMENT_ROOT'])) return null; @@ -598,35 +568,69 @@ class Db extends nb { },$this->pdo); } - public function dump() { - return $this->sql(true); - } + public function dump() { return $this->sql(true); } public function sql($insert=null) { if ($insert === null) $insert = self::p('insert'); + + # Params $this->pset('orderby',null); $this->pset('extras','0'); $this->pset('format','sql'); + # Filters + $type = self::p('type',''); + $name = self::p('name',self::p('table','')); + # Tables param filter $tables = $this->tables(); -# NB 21.06.16 if ($table=self::p('table')) { -# NB 21.06.16 $new = array(); -# NB 21.06.16 foreach (explode(',',$table) as $t) { -# NB 21.06.16 $new[$t] = $tables[$t]; -# NB 21.06.16 } -# NB 21.06.16 $tables = $new; -# NB 21.06.16 } - #if (NB_EOL == "\n") # Cache before changing db type + $views = []; foreach ($tables as $k=>$t) { + if ( + ($t->type != 'table' and $t->type != 'view') + or (!empty($name) and !$this->str_match($t->name,$name)) + or (!empty($type) and !$this->str_match($t->type,$type)) + ) { + unset($tables[$k]); + continue; + } + if ($t->type == 'view') $views[] = $t->name; unset($t->orderby); $t->fields(); $t->sql(); - if ($t->type == 'sql') unset($tables[$k]); } + # Table first + uasort($tables,function($a,$b) use($views){ + #if ($a->type == 'table' and $b->type == 'table') return strcmp($a->name,$b->name); + $embed = function($t) use($views) { + return preg_match('/ FROM .*\b'.join('|',$views).'\b/',$t->sql()) ? 1 : 0; + }; + + + if ($a->type != 'table') return ($embed($a) ? -1 : +1); + if ($b->type != 'table') return ($embed($b) ? -1 : +1); + #if ($a->type == 'table') return +1; + #if ($b->type == 'table') return -1; + + #if ($embed($a)) return +1; + #if ($embed($b)) return -1; + return (strlen($b->name) < strlen($a->name)) ? 1 : -1; + return 0; + return strcmp($a->name,$b->name); + $names = strcmp($a->name,$b->name); + $len = (strlen($b->name) < strlen($a->name)); + return ( + # Tables first + ( ($b->type == 'table' ? 0 : 1) < ($a->type == 'table' ? 0 : 1) ) + # Short name first for embed views + #+ (strlen($b->name) < strlen($a->name)) + ); + }); + + # HEADER echo '' #."-- Database : ".$this->name."\n" ."-- Date : ".strftime('%F %T')."\n" @@ -634,36 +638,43 @@ class Db extends nb { #."-- Host : ".$this->host."\n" ; + # Change db type if needed if (self::p('db.type')) { - $this->type = self::p('db.type'); echo "-- Type : ".self::p('db.type')."\n"; + $this->type = self::p('db.type'); } - $type = self::p('type',''); - $name = self::p('name',self::p('table','')); + # Contruct DROP / CREATE + foreach ($tables as $t) { + $sql = rtrim($t->create(),';'); + $sql = str_replace(';CREATE',";\nCREATE",$sql); + $t->sql_create = 'DROP '.strtoupper($t->type).' IF EXISTS '.$t->sql_name().';'.NB_EOL + .$sql.';'.NB_EOL + ; + + if ($insert) { + $t->sql_create = "\n-- ".strtoupper($t->type).": ".$t->name."\n".$t->sql_create; + } +#echo $t->sql_create."\n"; +#echo($t->name.' '.$t->type."\n"); + } + +#return; + + # EXEC + $i = 0; foreach ($this->sql_exec() as $s) { + if (($i++) == 0) echo "\n-- EXEC\n"; echo rtrim($s,';').";\n"; } + # DUMP foreach ($tables as $t) { - if (!empty($tables) and empty($tables[$t->name])) continue; - if (!empty($name) and !$this->str_match($t->name,$name)) continue; - if (!empty($type) and !$this->str_match($t->type,$type)) continue; - if ($insert) { - echo "\n-- ".strtoupper($t->type).": ".$t->name."\n"; - } - #echo 'DROP TABLE IF EXISTS '.$t->sql_name().';'.NB_EOL; - echo 'DROP '.strtoupper($t->type).' IF EXISTS '.$t->sql_name().';'.NB_EOL; - - $sql = rtrim($t->create(),';'); - $sql = str_replace(';CREATE',";\nCREATE",$sql); - #$sql = preg_replace('/;CREATE\s+(INDEX)\s+(\S+)(\s+ON\s+\S+[^;]+)/',";\nDROP $1 IF EXISTS $2;\nCREATE $1 $2",$sql); - echo $sql.';'.NB_EOL; + echo $t->sql_create; if ($insert and $t->type == 'table') $t->rows(); - #break; } return true; -- 2.47.3