From 229bffef8bcb44a6130fe0fce1945d4f7327495b Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Tue, 22 Mar 2016 00:59:27 +0000 Subject: [PATCH] dump, bed --- etc/dbs.yaml | 10 ++ lib/php/benchmark.php | 5 +- lib/php/db.php | 261 ++++++++++++++++++++++++++++-------------- lib/php/db/table.php | 6 +- lib/php/out.php | 30 ++++- 5 files changed, 219 insertions(+), 93 deletions(-) diff --git a/etc/dbs.yaml b/etc/dbs.yaml index 19023ffd..98241820 100644 --- a/etc/dbs.yaml +++ b/etc/dbs.yaml @@ -1,3 +1,8 @@ +_mysql: + type: mysql + options: + - "PDO::MYSQL_ATTR_INIT_COMMAND: SET NAMES utf8'" + crypt: pdo: 'sqlite:/dev/shm/crypt.db' @@ -120,6 +125,11 @@ rent: # NB 10.01.16 #revision: (SELECT GROUP_CONCAT(''||substr(id,0,instr(id,'.'))||'',' ') FROM template) # NB 10.01.16 template: # NB 10.01.16 View: "''||id||''" +nb: + host: admin.izideal.vpn + _import: + - _nico + - _mysql postfix: #pdo: 'mysql:host=media.izideal.vpn;port=3306;dbname=postfix' user: postfix diff --git a/lib/php/benchmark.php b/lib/php/benchmark.php index e0ef96ac..5dec29a2 100755 --- a/lib/php/benchmark.php +++ b/lib/php/benchmark.php @@ -24,8 +24,9 @@ function __f2($ar) { return; foreach ($ar as $v) { 1; } } $array = array_fill(0,100000,"a"); #if (function_exists('__f1')) { function _f1() { __f1($array); } nb::benchmark('_f1'); } #if (function_exists('__f2')) { function _f2() { __f2($array); } nb::benchmark('_f2'); } -function _f1() { ini_set('display_errors', 'On'); } nb::benchmark('_f1'); -function _f2() { ; } nb::benchmark('_f2'); +$fct = 'is_scalar'; +function _f1() { is_scalar('zaza'); } nb::benchmark('_f1'); +function _f2() { global $fct; $fct('zaza'); } nb::benchmark('_f2'); nb::benchmark(); exit; diff --git a/lib/php/db.php b/lib/php/db.php index 3fea979c..d87aa82e 100644 --- a/lib/php/db.php +++ b/lib/php/db.php @@ -9,9 +9,16 @@ require_once(dirname(__FILE__).'/nb.php'); require_once(dirname(__FILE__).'/out.php'); require_once(dirname(__FILE__).'/db/table.php'); -require_once('out.php'); $DB_TYPES = array( + 'config' => array( + # hostname:port:database:username:password + 'pgsql' => array($_SERVER['HOME'].'/'.'.pgpass', '^[^:]+:[^:]+::(?P[^:]+):(?[^:]+)'), + 'mysql' => array($_SERVER['HOME'].'/'.'.my.cnf', '^(?:user(?:name)?=(?P\S+)|password=(?P\S+))'), + 'sqlite' => array($_SERVER['HOME'].'/'.'.db.sqlite', '^(?:user(?:name)?=(?P\S+)|password=(?P\S+))'), + #'sqlite' => array($_SERVER['HOME'].'/'.'.db.pass', '^:(?P[^:]*):(?[^:]*)'), + #'sqlite' => array(), + ), 'tables' => array( 'pgsql' => "SELECT table_name as name" .",LOWER(CASE table_type WHEN 'BASE TABLE' THEN 'TABLE' ELSE table_type END) as type" @@ -27,6 +34,7 @@ $DB_TYPES = array( 'sqlite' => "SELECT name,type FROM sqlite_master WHERE type IN('table','view') AND name NOT LIKE 'sqlite_%' ORDER BY name", ), ); +#die($DB_TYPES['tables']['mysql']); class Db extends nb { @@ -271,19 +279,58 @@ class Db extends nb { if (isset($this->tables) and array_key_exists($name,$this->tables)) { foreach ($this->tables[$name] as $k=>$v) { $opt[$k] = $v; } } - return new Table($name,array_merge($opt)); + return new Table($name,$opt); + #return new Table($name,array_merge($opt)); + } + + public function config() { + #bye($this->types('config')); + if (!($config = $this->types('config'))) + self::bye("db.config(): Unknow db type: ".$this->type." from ".print_r($GLOBALS['DB_TYPES'],1)) + ; + $config[1] = str_replace('',$this->name,$config[1]); + + if (!file_exists($config[0])) return array(); + $conf = file_get_contents($config[0]); + + $return = array(); + foreach (explode("\n",$conf) as $line) { +#bye($line); + if (!preg_match('/'.($config[1]).'/',$line,$m)) continue; + foreach ($m as $k=>$v) if (preg_match('/^\d+$/',$k)) unset($m[$k]); + $return = array_replace_recursive($return,$m); + } + +#debug($return); + return $return; } - public function types($key) { + public function types($key=false,$die=false) { #if (!isset($DB_TYPES[$key][$this->type])) return; global $DB_TYPES; + if ($key === false) { + $types = array(); + + foreach ($DB_TYPES as $k=>$v) { + if (!isset($v[$this->type])) continue; + $types[$k] = $v[$this->type]; + #$types[] = array( $k => $v[$this->type] ); + } +#bye($types); + return $types; + + } + if ( !isset($DB_TYPES[$key]) or !isset($DB_TYPES[$key][$this->type]) + or empty($DB_TYPES[$key][$this->type]) ) - #$this->bye("db.types(`$key`): Unknow db type: ".$this->type." from ".print_r($DB_TYPES,1)) - return + # ??? not working ??? + #self::bye("db.types(): Unknow key `$key` for type `".$this->type."`".print_r($DB_TYPES,1)); + #if ($die) self::bye("db.types(): Unknow key `$key` for type `".$this->type."`"); + return; ; return $DB_TYPES[$key][$this->type]; @@ -291,9 +338,10 @@ class Db extends nb { public function tables($return_hash=false) { if (isset($this->_tables) and $this->_tables) return ($return_hash ? $this->tables : $this->_tables); + if (!isset($this->_tables)) $this->_tables = array(); if (!($sql = $this->types('tables'))) - $this->bye("db.types(`$key`): Unknow db type: ".$this->type." from ".print_r($DB_TYPES,1)) + self::bye("db.types(): Unknow db type: ".$this->type." from ".print_r($GLOBALS['DB_TYPES'],1)) ; $rows = $this->conn->query($sql,PDO::FETCH_ASSOC); @@ -306,6 +354,9 @@ class Db extends nb { $this->tables[$name][$k] = $v; } } + #bye($this->query("SELECT DATABASE()")); + #bye($this->_tables); + #bye($rows); if ($return_hash) return $this->tables; return $this->_tables; @@ -351,7 +402,9 @@ EOF; } function sql_name($value) { - if ($this->type == 'mysql') return '`'.$value.'`'; + if ($this->type == 'mysql' + or $this->type == 'sqlite' + ) return '`'.$value.'`'; return '"'.$value.'"'; } @@ -431,7 +484,7 @@ EOF; } - function out2($rows,$head=array()) { + public function out2($rows,$head=array()) { #if (is_scalar($head)) bye(var_dump($head,true)); if ($this->p('header') === '0' ) $head = false; @@ -446,65 +499,78 @@ EOF; return true; } - function action($table) { + public function action($table) { #if ($this->p('format') == 'table') $this->pset('format',''); $this->pdef('format',($this->php_cli() ? 'csv' : '')); - $action = $this->p('action'); + $actions = explode(',',$this->p('action')); $rows = array(); + $return = false; + + foreach($actions as $action) { + + if ($action == 'db.help') { + $this->out2(array( + array('db.help','This help'), + array('[db.]dbs','List databases'), + array('[db.]tables','List tables'), + array('[db.]html_menu','Html menu for tables with links'), + + array('[table.]rows','Dump one table, use format='), + array('[table.]fields','List fields'), + array('table.inf','Table infos (count(*). Type, ...)'), + array('table.sql','Get the sql source'), + + array('[table.]insert','Insert a record'), + array('[table.]update','Update a record'), + array('[table.]delete','Delete a record'), + + ),array('command','description')); + $return = true; + + } elseif ($action == 'db.tables' or $action == 'tables') { + + foreach ($this->tables(true) as $name => $t) { $row=array('name'=>$t['name']); + foreach (array( + 'type', + 'count', + 'engine', + 'created', + ) as $f) { + if (isset($t[$f])) $row[$f] = $t[$f]; + if ($f=='count' and $this->p('count')==='1') $row['count'] = $this->table($name)->count(); + } + $rows[] = $row; + } - if ($action == 'db.help') { - return $this->out2(array( - array('db.help','This help'), - array('[db.]dbs','List databases'), - array('[db.]tables','List tables'), - array('[db.]html_menu','Html menu for tables with links'), - - array('[table.]rows','Dump one table, use format='), - array('[table.]fields','List fields'), - array('table.inf','Table infos (count(*). Type, ...)'), - array('table.sql','Get the sql source'), - - array('[table.]insert','Insert a record'), - array('[table.]update','Update a record'), - array('[table.]delete','Delete a record'), - - ),array('command','description')); - return true; - - } elseif ($action == 'db.tables' or $action == 'tables') { - - foreach ($this->tables(true) as $name => $t) { $row=array('name'=>$t['name']); - foreach (array( - 'type', - 'count', - 'engine', - 'created', - ) as $f) { - if (isset($t[$f])) $row[$f] = $t[$f]; - if ($f=='count' and $this->p('count')==='1') $row['count'] = $this->table($name)->count(); - } - $rows[] = $row; - } + $return = $this->out2($rows); + # NB 21.03.16 $return = $this->out2($rows,array_keys($row)); + # NB 21.03.16 $return = $this->out2(array_values($this->tables(true))); + # NB 21.03.16 $return = $this->out2($this->tables(),array('name')); - return $this->out2($rows); - return $this->out2($rows,array_keys($row)); - return $this->out2(array_values($this->tables(true))); - return $this->out2($this->tables(),array('name')); + } elseif ($action == 'db.dbs' or $action == 'dbs') { + $return = $this->out2($this->dbs,"name"); - } elseif ($action == 'db.dbs' or $action == 'dbs') { - return $this->out2($this->dbs,"name"); + } elseif ($action == 'db.html_menu' or $action == 'html_menu') { + $this->print_header($this->p('format','html')); + echo $this->html_menu(); + $return = true; - } elseif ($action == 'db.html_menu' or $action == 'html_menu') { - $this->print_header($this->p('format','html')); - echo $this->html_menu(); - return true; + } elseif (preg_match('/db\.(\w+)/',$action,$m)) { + $meth = $m[1]; + $rows = $this->$meth(); + #$return = $this->out2($rows); + if ($rows) $return = + out::rows($this->p('format',out::php_cli() ? 'csv' : 'csv'),$rows) + ; + $return = true; - } else { - if ($r=$table->action()) return $r; + } else { + if ($r=$table->action($action)) $return = $r; + } } - return false; + return $return; } /** @@ -512,42 +578,53 @@ EOF; * @param [FILES] $files Files to load * @return ARRAY the new/existing value of $this->db */ - static function conf2h ($files=array(),&$first=false) { + static function conf_dbs($files=array(),&$first=false) { if (empty($files)) return array(); - - if (!empty($files)) { - - $h = array(); - - foreach ($files as $file) { - if (is_readable($file) and ($yaml = yaml_parse_file($file))) { - #foreach ($yaml as $k=>$v) { $yaml[$k]['_conf2h'] = $file; } - #debug($file); - $h = array_replace_recursive($h,$yaml); - } - #debug($file); - #if ( ($yaml = @yaml_parse_file($file)) ) $h = array_replace_recursive($h,$yaml) - ; - #} + if (is_scalar($files)) $files = array($files); + + # Load all files into a hash + $h = array(); + foreach ($files as $file) { + if (is_readable($file) and ($yaml = yaml_parse_file($file))) { + #foreach ($yaml as $k=>$v) { $yaml[$k]['_conf_dbs'] = $file; } + #debug($file); + $h = array_replace_recursive($h,$yaml); } - - unset($yaml); - - # Emulate hash pointer - foreach ($h as $db=>$o) { - foreach ($o as $k => $v) { - if ($k != '_conf2h_copy') continue; - + #debug($file); + #if ( ($yaml = @yaml_parse_file($file)) ) $h = array_replace_recursive($h,$yaml) + ; + #} + } + unset($yaml); + + # Emulate hash pointer _import + foreach ($h as $db=>$o) { + foreach ($o as $k => $v) { + if ($k != '_conf_dbs_copy' + and $k != '_import' + ) continue; + + ##die 'zaza'; + $import = is_array($v) ? $v : explode(',',$v); + #debug($import); + foreach ($import as $v) { foreach ($h[$v] as $kk => $vv) { if (!isset($o[$kk])) $h[$db][$kk] = $vv; } - unset($h[$db][$k]); - #bye($h[$db]); - } - } + unset($h[$db][$k]); + #bye($h[$db]); + + } } + + # Remove db starting with _ + foreach ($h as $db=>$o) { if (preg_match('/^_/',$db)) unset($h[$db]); } + + # Add missing name + foreach ($h as $db=>$o) { if (empty($o['name'])) $h[$db]['name'] = $db; } + if (!$h) return false; #self::bye($h['puppetdb']); #if ($first !== false) debug ( self::ar_first($h) ); @@ -574,12 +651,13 @@ EOF; # NB 19.03.16 if (!in_array('/etc/dbs.yaml',$conf)) $conf[] = '/etc/dbs.yaml'; # Load databases - if (! ($dbs = $Db->conf2h($conf)) ) return false; + if (! ($dbs = $Db->conf_dbs($conf)) ) return false; + $Db->hksort($dbs,'order'); + #bye($dbs['nb']); #die (nb::p('db')); # Param - Default base on order hight num value if (!$Db->p('db')) { - $Db->hksort($dbs,'order'); $Db->pset('db',$Db->ar_first($dbs,true)); } @@ -607,6 +685,17 @@ EOF; } + public function dump() { + $this->pset('orderby',null); + foreach ($this->tables() as $t) { + $t = $this->table($t); + #$this->pset('format','sql'); + echo preg_replace('/\s+/',' ',$t->sql()).";\n"; + $t->rows(); + } + return array(); + } + } # < Class ?> diff --git a/lib/php/db/table.php b/lib/php/db/table.php index bd275104..16c57f78 100644 --- a/lib/php/db/table.php +++ b/lib/php/db/table.php @@ -1102,13 +1102,13 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. public function out($v,$head=false) { return $this->db->out($v,$head); } public function out2($v,$head=array()) { return $this->db->out2($v,$head); } - public function action() { - $action = $this->p('action'); + public function action($action=null) { + if ($action === null) $action = $this->p('action'); if ($action == 'table.fields' or $action == 'fields') { return $this->out2(array_values($this->object2array($this->fields()))); - } elseif ($action == 'table.sql') { return $this->out2($this->sql(),'sql'); + } elseif ($action == 'table.sql' or $action == 'sql') { return $this->out2($this->sql(),'sql'); } elseif ($action == 'table.count') { return $this->out2($this->count(),'count'); } elseif ($action == 'table.rows' or $action == 'rows') { diff --git a/lib/php/out.php b/lib/php/out.php index c9e17591..743ce46f 100644 --- a/lib/php/out.php +++ b/lib/php/out.php @@ -10,6 +10,8 @@ class Out extends Nb { public static $types = null; public static function types() { self::$types = array( + 'sql' => array( + ), 'divs' => array( 'is_html' => true, 'enclose' => array("
".NB_EOL,"
".NB_EOL), @@ -66,6 +68,7 @@ class Out extends Nb { } public static function begin($o,$head,$data=array()) { + #if (self::p('header')==="0") return; if (!empty($o['enclose'])) echo $o['enclose'][0]; if (!isset($o['head'])) return; @@ -86,7 +89,11 @@ class Out extends Nb { public static function row($o,&$row) { if (isset($o['function'])) { - echo $o['function']($row,$o); + if (preg_match('/^out_/',$o['function'])) { + echo $o['function']($row,$o); + } else { + echo $o['function']($row); + } return true; } out_tag($row,$o); @@ -103,6 +110,10 @@ class Out extends Nb { } public static function rows($type,&$data,$head=array()) { + + # Ex: for action=tables when header=0 + if (is_scalar($data)) $data = array(array($data)); + #bye($data); #if (empty($head)) $head = array_fill(0,count($data[0]),'?'); if (!isset(self::$types[$type])) self::bye("Unknow type: `$type`"); $conf = self::$types[$type]; @@ -158,17 +169,32 @@ class Out extends Nb { /****************************************************************************/ function out_yaml(&$row,$o) { + #echo out::yaml_encode($row); return; + #$yaml = ' '.out::yaml_encode($row); + #$yaml = out::yaml_encode($row); + #echo yaml_emit($row); return; #echo out::yaml_encode($row); return; $yaml = '- '.out::yaml_encode($row); + #$yaml = (out::is_hash($row) ? ' ' : '- ') . out::yaml_encode($row); echo preg_replace("/^(?!-)/m"," ",$yaml); } function out_csv(&$row,$o) { - echo join($o['sep'],array_values($row)); + $values = array(); + + foreach (array_values($row) as $k=>$v) { + #if (!is_scalar($v)) $v = join('',$v); + if (!is_scalar($v)) $v = json_encode($v); + $values[] = $v; + } + + #echo join($o['sep'],array_values($row)); + echo join($o['sep'],$values); } function out_csv_head(&$row,$o) { if (!is_array($row)) echo 0; + echo "\r"; if (out::is_hash($row)) { $ar = array_keys($row); out_csv($ar,$o); -- 2.47.3