From 5ab6737b15b471896d2dc0e3d1f3a9f1947f7cdb Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Fri, 1 Apr 2016 01:46:46 +0100 Subject: [PATCH] Bed --- etc/dbs.yaml | 2 +- lib/php/db.php | 68 ++++++++++++++++++++++++++++--------- lib/php/db/table.php | 16 ++++----- lib/php/db/types/mysql.php | 18 ++++++++-- lib/php/db/types/pgsql.php | 3 +- lib/php/db/types/sqlite.php | 8 ++--- lib/php/out.php | 39 +++++++++++++++------ 7 files changed, 109 insertions(+), 45 deletions(-) diff --git a/etc/dbs.yaml b/etc/dbs.yaml index 9178b618..d3429978 100644 --- a/etc/dbs.yaml +++ b/etc/dbs.yaml @@ -1,7 +1,7 @@ _mysql: type: mysql options: - - "PDO::MYSQL_ATTR_INIT_COMMAND: SET NAMES utf8'" + - "PDO::MYSQL_ATTR_INIT_COMMAND: SET NAMES utf8" crypt: pdo: 'sqlite:/dev/shm/crypt.db' diff --git a/lib/php/db.php b/lib/php/db.php index cdfc571f..bcdde716 100644 --- a/lib/php/db.php +++ b/lib/php/db.php @@ -147,6 +147,14 @@ class Db extends nb { if (empty($this->conn)) return false; + # Type queries + if ($sql = $this->type('post_connect')) { + if (is_scalar($sql)) $sql = array($sql); + foreach ($sql as $s) { + $this->conn->exec($s); + } + } + # Create functions if ($this->type('sqliteCreateFunction')) { foreach ($this->type('sqliteCreateFunction') as $name => $fct) { @@ -222,6 +230,13 @@ class Db extends nb { return join($sep,$query->fetch(PDO::FETCH_NUM)); } + private function rows($sql,$style=PDO::FETCH_BOTH) { + return $this->query($sql)->fetchAll($style); + $sth = $this->conn->prepare($sql); + $sth->execute(); + return $sth->fetchAll($style); + } + /* */ function query2a($sql) { @@ -359,6 +374,8 @@ EOF; return '"'.$value.'"'; } +# TODEL - NB 01.04.16 +/* function out($row,$head=false) { if (!$this->p('header',1) and $head) $head = false; $f = $this->p('format','json'); @@ -434,6 +451,7 @@ EOF; return true; } +*/ public function out2($rows,$head=array()) { #if (is_scalar($head)) bye(var_dump($head,true)); @@ -491,10 +509,7 @@ EOF; $return = true; } elseif (preg_match('/^db\.(\w+)/',$action,$m)) { - $meth = $m[1]; - $rows = $this->$meth(); - if ($rows) $return = $this->out2($rows,self::is_hash($rows) ? array_keys($rows) : array($meth)); - $return = true; + if ($rows = $this->$m[1]()) $return = $this->out2($rows,(is_scalar($rows) ? $m[1] : array())); } elseif($table) { if ($r=$table->action($action)) $return = $r; @@ -636,6 +651,7 @@ EOF; $this->pset('format','sql'); $r = array(); #if (NB_EOL == "\n") + echo '' #."-- Database : ".$this->name."\n" ."-- Date : ".strftime('%F %T')."\n" @@ -643,25 +659,45 @@ EOF; #."-- Host : ".$this->host."\n" ; foreach ($this->tables() as $t) { - if ($insert) echo "\n-- Table: ".$t->name."\n"; + if ($insert) { + echo "\n-- Table: ".$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; + } echo rtrim($t->sql(),';').';'.NB_EOL; - if ($insert) $t->rows(); + if ($insert and $t->type == 'table') $t->rows(); #break; } return $r; } - public function infos() { + public function status() { + if ($status=$this->type('status')) { + $new = array(); + foreach ($status as $sql => $fct) { + foreach ($rows = $this->rows($sql) as $i=>$r) { + #bye($rows); + #$fct($rows[$i]); + #if (!$rows[$i] = $fct($r)) unset($rows[$i]); + if ($v = $fct($r)) $new += $v; + } + $status = $new; + #debug($rows); + } + } else { + $status = array(); + } return array( - array('key'=>'host','val'=>$this->host), - array('key'=>'port','val'=>$this->port), - array('key'=>'name','val'=>$this->name), - array('key'=>'user','val'=>$this->user), - #array('key'=>'tables','val'=>count($this->tables())), - #array('key'=>'dbs','val'=>count($this->dbs)), - array('key'=>'tables','val'=>join(' ',array_keys($this->tables()))), - array('key'=>'dbs','val'=>join(' ',array_values($this->dbs))), - ); + 'host' => $this->host, + 'port' => $this->port, + 'name' => $this->name, + 'user' => $this->user, + #'tables' => count($this->tables()), + #'dbs' => count($this->dbs), + 'tables' => count($this->tables()), + #'tables' => join(' ',array_keys($this->tables())), + #'dbs' => join(' ',array_values($this->dbs)), + )+$status; } } # < Class diff --git a/lib/php/db/table.php b/lib/php/db/table.php index 99c06fbc..9dae919c 100644 --- a/lib/php/db/table.php +++ b/lib/php/db/table.php @@ -692,13 +692,17 @@ Class Table extends nb { } public function rows_rec_sql(&$row) { - $values = array(); + $keys = $values = array(); foreach ($row as $k=>$v) { + if (isset($this->extras[$k])) continue; $f = $this->fields($k); + $values[] = $f->quote($v); + $keys[] = $f->sql_name(); } + return "INSERT INTO ".$this->sql_name()." (".join(',',$keys).") VALUES (".join(',',array_values($values)).");".NB_EOL; return "INSERT INTO ".$this->sql_name()." (".join(',',array_keys($row)).") VALUES (".join(',',array_values($values)).");".NB_EOL; } @@ -1033,7 +1037,7 @@ Class Table extends nb { return $this->db()->exec($sql); } - public function out($v,$head=false) { return $this->db()->out($v,$head); } +# NB 01.04.16 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 infos() { @@ -1061,9 +1065,6 @@ Class Table extends nb { #return $this->out2(serialize(($this->fields()))); return $this->out2(array_values($this->object2array($this->fields()))); - } 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') { $this->db()->print_header($this->p('format')); $this->rows(); return true; @@ -1100,10 +1101,7 @@ Class Table extends nb { return $r; } elseif (preg_match('/^table\.(\w+)/',$action,$m)) { - $meth = $m[1]; - $rows = $this->$meth(); - if ($rows) $return = $this->out2($rows,self::is_hash($rows) ? true : array($meth)); - return true; + if ($rows = $this->$m[1]()) $return = $this->out2($rows,(is_scalar($rows) ? $m[1] : array())); } elseif ($this->p('format') and !preg_match('/^(table|div)$/',$this->p('format'))) { diff --git a/lib/php/db/types/mysql.php b/lib/php/db/types/mysql.php index e202f671..d15809f7 100644 --- a/lib/php/db/types/mysql.php +++ b/lib/php/db/types/mysql.php @@ -1,12 +1,27 @@ '`', +'post_connect' => 'SET NAMES utf8', + 'localFile' => array (getenv('HOME').'/.my.cnf','^(?:user(?:name)?=(?P\\S+)|password=(?P\\S+))'), 'table.sql' => 'SHOW CREATE TABLE ``', 'tables' => 'SELECT TABLE_NAME as name,LOWER(IF(TABLE_TYPE=\'BASE TABLE\',\'TABLE\',TABLE_TYPE)) as type,ENGINE as engine,CREATE_TIME as created FROM information_schema.tables WHERE TABLE_SCHEMA=DATABASE()', +'status' => array ( + 'SHOW GLOBAL STATUS' => create_function('&$r',join('',array( + 'if (preg_match("/(Threads.*|Conn.*|Max_used_connections|Table_.*|Open.*_tables)/",$r["Variable_name"]))' + .'return array("GLB.".$r[0]=>$r[1]);' + #'if (preg_match("/(Threads_connected|Max_used_connections|Table_locks_immediate|Table_locks_waited|Open.*_tables)/",$r[0])) return $r;', + ))), + 'SHOW VARIABLES' => create_function('&$r',join('',array( + 'if (preg_match("/^(Table|.*[Cc]onn|Open|Com).*/",$r["Variable_name"]))' + .'return array("VAR.".$r[0]=>$r[1]);' + ))), +), + 'table.fields' => array ( 'sql' => 'SHOW COLUMNS FROM ``', 'fct' => create_function('&$r',join('',array( @@ -15,5 +30,4 @@ $DB_TYPES['mysql'] = array ( ))), ), -); -?> +);?> diff --git a/lib/php/db/types/pgsql.php b/lib/php/db/types/pgsql.php index 2d17155e..ad72ea0b 100644 --- a/lib/php/db/types/pgsql.php +++ b/lib/php/db/types/pgsql.php @@ -80,5 +80,4 @@ FROM pg_catalog.pg_class c WHERE c.relname = \'\' AND pg_catalog.pg_table_ ', ), -); -?> +);?> diff --git a/lib/php/db/types/sqlite.php b/lib/php/db/types/sqlite.php index bf71f92c..11b08dd3 100644 --- a/lib/php/db/types/sqlite.php +++ b/lib/php/db/types/sqlite.php @@ -1,6 +1,9 @@ true, + +'quote_name' => '`', + 'table.sql' => 'SELECT sql FROM sqlite_master WHERE name=\'\'', 'sqliteCreateFunction' => array ( @@ -26,8 +29,6 @@ $DB_TYPES['sqlite'] = array ( ,2), ), -'localFile' => array ('/Users/nico/.sqlite','^(?:user(?:name)?=(?P\\S+)|password=(?P\\S+))'), - 'tables' => 'SELECT name,type FROM sqlite_master WHERE type IN(\'table\',\'view\') AND name NOT LIKE \'sqlite_%\' ORDER BY name', 'table.fields' => array ( @@ -41,5 +42,4 @@ $DB_TYPES['sqlite'] = array ( ))), ), -); -?> +);?> diff --git a/lib/php/out.php b/lib/php/out.php index 91d7d427..9215aa9f 100644 --- a/lib/php/out.php +++ b/lib/php/out.php @@ -106,10 +106,25 @@ Class Out extends Nb { } public static function head(&$o,$head,$data=array()) { - if (is_scalar($head) and !is_bool($head)) $head = array($head); + + if ($head !== false) { + + if (is_scalar($head) and !is_bool($head)) { + $head = array($head); + + } elseif (empty($head)) { + if (self::is_hash($data)) $head = array_keys($data); + elseif (is_array($data) and self::is_hash($data[0])) $head = array_keys($data[0]); + } + + } # For sprintf +#bye($head); unset($o['head_max_len']); +# NB 01.04.16 if (!empty($head)) { +# NB 01.04.16 $o['head_max_len'] = max(self::ar_map('strlen($a)',$head)); +# NB 01.04.16 } if (!empty($data)) { if (self::is_hash($data[0])) { $o['head_max_len'] = max(self::ar_map('strlen($a)',array_keys($data[0]))); @@ -124,17 +139,18 @@ Class Out extends Nb { if (!isset($o['head'])) return; #var_dump($head); return; - if (empty($head) and $head!== false) { - if (self::is_hash($data[0])) { - $head = array_keys($data[0]); - } else { - return; - } - } +# NB 01.04.16 if (empty($head) and $head!== false) { +# NB 01.04.16 if (self::is_hash($data[0])) { +# NB 01.04.16 $head = array_keys($data[0]); +# NB 01.04.16 } else { +# NB 01.04.16 return; +# NB 01.04.16 } +# NB 01.04.16 } if (self::p('header') === '0' ) return; if ($head === false) return; echo $o['head']($head,$o) . empty($o['eol']) ? '' : $o['eol']; + return $head; } @@ -202,7 +218,7 @@ Class Out extends Nb { $count = 0; # Function head - self::head($conf,$head,$data); + $head = self::head($conf,$head,$data); foreach ($data as $row) { if ($count>0) out::concat($conf); @@ -211,14 +227,15 @@ Class Out extends Nb { # Transform simple array into hash if (isset($row[0]) and $head) { #bye($conf); - $i = 0; - $new = array(); if (is_scalar($row)) $row = array($row); + + $new = array(); $i = 0; foreach ($head as $k => $h) { $new[$h] = $row[$i]; $i++; } $row = $new; + } self::row($conf,$row); -- 2.47.3