From 2aaf15ca013c447901f3edd114cfcf3285d5f25a Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Tue, 8 Mar 2016 02:04:59 +0000 Subject: [PATCH] Bed ! --- bin/dbq | 2 +- etc/dbs.yaml | 4 +- lib/php/db.php | 96 +++++++++++++++++++++++---------------- lib/php/db/table.php | 105 ++++++++++++++++++++++--------------------- lib/php/out.php | 26 +++-------- 5 files changed, 122 insertions(+), 111 deletions(-) diff --git a/bin/dbq b/bin/dbq index ecf23f0b..f5ec8360 100755 --- a/bin/dbq +++ b/bin/dbq @@ -160,7 +160,7 @@ if ($keys{format} eq 'nc') { $keys{format} = 'json'; open(STDOUT,"|$0 --parse_debug|jq .") -} elsif ( $keys{debug} or $Opt{debug}) { +} elsif ( $keys{debug} or $Opt{debug} ) { open(STDOUT,"|$0 --parse_debug") } diff --git a/etc/dbs.yaml b/etc/dbs.yaml index f28d23bf..27700cd5 100644 --- a/etc/dbs.yaml +++ b/etc/dbs.yaml @@ -73,8 +73,8 @@ rent: # NEW- NB 10.01.16 tables: - template: - View: "''||id||''" +# NB 08.03.16 template: +# NB 08.03.16 View: "''||id||''" place: replace: diff --git a/lib/php/db.php b/lib/php/db.php index d8efff59..17fd8d90 100644 --- a/lib/php/db.php +++ b/lib/php/db.php @@ -23,6 +23,22 @@ if (!defined('DB_HTML_FORM_BUTTONS')) define('DB_HTML_FORM_BUTTONS','' #if (!defined('DB_HTML_BUTTON_ADD')) define('DB_HTML_BUTTON_ADD','Add'); #if (!defined('DB_HTML_BUTTON_ADD')) define('DB_HTML_BUTTON_ADD',''); +$DB_TYPES = array( + 'tables' => array( + 'pgsql' => "SELECT table_name as name" + .",LOWER(CASE table_type WHEN 'BASE TABLE' THEN 'TABLE' ELSE table_type END) as type" + .",table_type" + ." FROM information_schema.tables WHERE table_type in('BASE TABLE','VIEW') AND table_schema NOT IN ('pg_catalog', 'information_schema')" + , + 'mysql' => "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()" + , + 'sqlite' => "SELECT name,type FROM sqlite_master WHERE type IN('table','view') AND name NOT LIKE 'sqlite_%' ORDER BY name", + ), +); class Db extends nb { # PDO Connection @@ -227,7 +243,6 @@ class Db extends nb { } function row($sql,$sep=' ') { - #$sql = "SELECT * FROM addr LIMIT 1"; $query = $this->query($sql); $result = $query->fetch(PDO::FETCH_NUM); if (!is_array($result)) return $result; @@ -267,29 +282,40 @@ class Db extends nb { return new Table($name,array_merge($opt)); } - function tables() { - if (isset($this->_tables) and $this->_tables) return $this->_tables; + public function types($key) { + #if (!isset($DB_TYPES[$key][$this->type])) return; + global $DB_TYPES; - if ($this->type == 'sqlite') { - $sql = "SELECT name FROM sqlite_master WHERE type IN('table','view') AND name NOT LIKE 'sqlite_%' ORDER BY name"; + if ( + !isset($DB_TYPES[$key]) + or !isset($DB_TYPES[$key][$this->type]) + ) + #$this->bye("db.types(`$key`): Unknow db type: ".$this->type." from ".print_r($DB_TYPES,1)) + return + ; - } elseif ($this->type == 'pgsql') { - $sql = "SELECT table_name FROM information_schema.tables WHERE table_type in('BASE TABLE','VIEW') AND table_schema NOT IN ('pg_catalog', 'information_schema')"; + return $DB_TYPES[$key][$this->type]; + } - } elseif ($this->type == 'mysql') { - $sql = "SHOW TABLES"; + public function tables($return_hash=false) { + if (isset($this->_tables) and $this->_tables) return ($return_hash ? $this->tables : $this->_tables); - } else { - if ($this->pdo) $this->bye('db.tables(): Unknow db type: '.$this->type); - $this->_tables = array(); - return array(); - } + if (!($sql = $this->types('tables'))) + $this->bye("db.types(`$key`): Unknow db type: ".$this->type." from ".print_r($DB_TYPES,1)) + ; - $rows = $this->conn->query($sql); + $rows = $this->conn->query($sql,PDO::FETCH_ASSOC); foreach ($rows as $row) { - $this->_tables[] = current($row); + $name = current($row); + $this->_tables[] = $name; + + if (!isset($this->tables[$name])) $this->tables[$name] = array(); + foreach ($row as $k => $v) { + $this->tables[$name][$k] = $v; + } } + if ($return_hash) return $this->tables; return $this->_tables; } @@ -525,7 +551,6 @@ EOF; #if ($this->p('format') == 'table') $this->pset('format',''); $this->pdef('format',($this->php_cli() ? 'csv' : '')); $action = $this->p('action'); - $head = ($this->p('header',1) and $this->p('format','csv') == 'csv' ) ? true : false; $rows = array(); if ($action == 'db.help') { @@ -533,12 +558,11 @@ EOF; array('db.help','This help'), array('[db.]dbs','List databases'), array('[db.]tables','List tables'), - array('db.count','Tables count(*)'), 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.count','Table count(*)'), + array('table.inf','Table infos (count(*). Type, ...)'), array('table.sql','Get the sql source'), array('[table.]insert','Insert a record'), @@ -549,15 +573,18 @@ EOF; return true; } elseif ($action == 'db.tables' or $action == 'tables') { - return $this->out2($this->tables(),array('name')); - - } elseif ($action == 'db.count') { - foreach ($this->tables() as $t) { $rows[] = array( - 'name' => $t, - 'count' => $this->table($t)->count(), - ); } + foreach ($this->tables(true) as $name => $t) { $row=array('name'=>$t['name']); + if ($this->p('count')==='1') $row['count'] = $this->table($name)->count(); + foreach (array( + 'type', + 'engine', + 'created', + ) as $f) { if (isset($t[$f])) $row[$f] = $t[$f]; } + $rows[] = $row; + } return $this->out2($rows); - return $this->out2(self::ar_map('array("table"=>$a,"count"=>$GLOBALS["Db"]->table($a)->count())',$this->tables())); + 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"); @@ -575,12 +602,6 @@ EOF; return false; } - /** - * @copyright NB 05.03.16 - */ - static function conf ($default,$files=array()) { - } - /** * @copyright NB 05.03.16 * @param [FILES] $files Files to load @@ -619,8 +640,12 @@ EOF; echo "__destruct\n"; } + // + // Create globals from $conf=array(yaml_fileds) + // public static function init($conf) { global $Db, $Table; + if (isset($Table)) self::bye("Table.init(): GLOBALS['Table'] already exists !"); if (isset($Db)) self::bye("Db.init(): GLOBALS['Db'] already exists !"); @@ -631,11 +656,6 @@ EOF; # Load databases if (! ($dbs = $Db->config2h($conf)) ) return false; -# NB 05.03.16 $ar = array('a'); -# NB 05.03.16 #$ar[] = 'b'; -# NB 05.03.16 array_unshift($ar,'b'); -# NB 05.03.16 bye($ar); - #bye($dbs['izi']); # Param - Extract dbname from table if (preg_match('/^(\w+)\.(.*?)$/',$Db->p('table'),$m)) { diff --git a/lib/php/db/table.php b/lib/php/db/table.php index 0e0fd1b6..6d24da70 100644 --- a/lib/php/db/table.php +++ b/lib/php/db/table.php @@ -14,11 +14,14 @@ if (false and empty($_SERVER['DOCUMENT_ROOT'])) { #print_r($Table->sql()); } +define('TABLE_EOL',defined(NB_EOL) ? NB_EOL : PHP_EOL); +define('TABLE_INDENT',TABLE_EOL ? "\t" : ""); +define('TABLE_CSV_SEP',nb::p('sep') ? nb::p('sep') : "\t"); if (!defined('DB_HTML_EDIT')) define('DB_HTML_EDIT','Edit'); if (!defined('DB_HTML_DELETE')) define('DB_HTML_DELETE','Delete'); if (!defined('DB_TABLE_QUERY_NAME')) define('DB_TABLE_QUERY_NAME','_query_'); -class table extends nb { +class Table extends nb { public $name; public $db; @@ -334,8 +337,8 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. $st = $this->db->conn->prepare($sql); $st->execute(); - echo '
'.PHP_EOL; - echo '
'.PHP_EOL; + echo ''.TABLE_EOL; + echo '
'.TABLE_EOL; $count = 0; if ( $add or ($row = $st->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT))) { $count ++; @@ -349,20 +352,20 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. } } - echo '
'.PHP_EOL; + echo '
'.TABLE_EOL; echo '' .'
' .( empty($_SERVER['HTTP_REFERER']) ? '' : '') .'' .'' - .'
'.PHP_EOL + .''.TABLE_EOL .'' .'' .'' .'' .'' - .'
'.PHP_EOL; + .''.TABLE_EOL; } @@ -685,7 +688,7 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. echo '
' .$this->nav($opt['count'],$opt['tot'],$opt['limit']) - .'
'.PHP_EOL + .''.TABLE_EOL ; } } # < count @@ -723,11 +726,11 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. -----------------------------------------------------------------*/ function rows_begin_json() { $this->_row_json = null; - return '['.PHP_EOL; + return '['.TABLE_EOL; return '' #."// database: ".$this->db->name."\n" #."// table: $this->name\n" - .'['.PHP_EOL; + .'['.TABLE_EOL; } function rows_rec_json(&$row) { @@ -735,14 +738,14 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. $json = ''; $this->_row_json = true; } else { - $json = ','.PHP_EOL; + $json = ','.TABLE_EOL; } return $json . json_encode($row); } function rows_end_json() { unset($this->_row_json); - return PHP_EOL.']'.PHP_EOL; + return TABLE_EOL.']'.TABLE_EOL; } /*----------------------------------------------------------------- @@ -757,28 +760,28 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. -----------------------------------------------------------------*/ function rows_begin_xml() { return '' - .''.PHP_EOL #db->name.'" table="'.$this->name.'" type="'.$this->db->type.'">'.PHP_EOL - .''.PHP_EOL + .''.TABLE_EOL #db->name.'" table="'.$this->name.'" type="'.$this->db->type.'">'.TABLE_EOL + .''.TABLE_EOL ; } function rows_rec_xml(&$row) { $xml = ''; - $xml .= "\t".PHP_EOL; + $xml .= TABLE_INDENT."".TABLE_EOL; foreach ($row as $k=>$v) { if ($v !== '') $xml .= '' - . "\t\t<".$k.">" + . TABLE_INDENT.TABLE_INDENT."<".$k.">" .'' . '' - .PHP_EOL; + . TABLE_EOL; } - $xml .= "\t".PHP_EOL; + $xml .= TABLE_INDENT."".TABLE_EOL; return $xml; } function rows_end_xml() { - return ''.PHP_EOL; + return ''.TABLE_EOL; } /*----------------------------------------------------------------- @@ -787,11 +790,11 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. function rows_begin_csv($fields) { if ($this->p('header')==="0") return ''; - return join("\t",array_keys($fields))."\n"; + return join(TABLE_CSV_SEP,array_keys($fields))."\n"; } function rows_rec_csv(&$row) { - return join("\t",array_values($row))."\n"; + return join(TABLE_CSV_SEP,array_values($row))."\n"; } function rows_end_csv() { @@ -805,55 +808,55 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. $html = ''; - #$html .= ''.PHP_EOL; - $html .= '
'.PHP_EOL; - #if (!empty($opt)) $html .= '' .PHP_EOL; + #$html .= '
' . $this->nav($opt['count'],$opt['tot'],$opt['limit']) . '
'.TABLE_EOL; + $html .= '
'.TABLE_EOL; + #if (!empty($opt)) $html .= '' .TABLE_EOL; if ($this->p('header')!=="0") { - $html .= ''.PHP_EOL.''.PHP_EOL; + $html .= ''.TABLE_EOL.''.TABLE_EOL; - if ($this->p('buttons')!=='0' and DB_HTML_EDIT) $html .= ''.PHP_EOL; + if ($this->p('buttons')!=='0' and DB_HTML_EDIT) $html .= ''.TABLE_EOL; foreach (array_keys($fields) as $f) { - #$html .= ''.PHP_EOL; - $html .= ''.PHP_EOL; + #$html .= ''.TABLE_EOL; + $html .= ''.TABLE_EOL; } - if ($this->p('buttons')!=='0' and DB_HTML_DELETE) $html .= ''.PHP_EOL; - $html .= ''.PHP_EOL.''.PHP_EOL; + if ($this->p('buttons')!=='0' and DB_HTML_DELETE) $html .= ''.TABLE_EOL; + $html .= ''.TABLE_EOL.''.TABLE_EOL; } - $html .= ''.PHP_EOL; + $html .= ''.TABLE_EOL; return $html; } function rows_rec_table(&$row) { - $html = ''.PHP_EOL; + $html = ''.TABLE_EOL; if ($this->p('buttons')!=='0') $html .= ''.PHP_EOL; + .'">'.DB_HTML_EDIT.''.TABLE_EOL; foreach ($row as $k => $v) { if (isset($this->extras[$k])) $k .= " extra"; - $html .= ''.PHP_EOL; + $html .= ''.TABLE_EOL; } if ($this->p('buttons')!=='0') $html .= ''.PHP_EOL; + .'">'.DB_HTML_DELETE.''.TABLE_EOL; - $html .= ''.PHP_EOL; + $html .= ''.TABLE_EOL; return $html; } function rows_end_table($opt=array()) { $html = ''; - $html .= ''.PHP_EOL; - $html .= '
' . $this->nav($opt['count'],$opt['tot'],$opt['limit']) . '
'.($this->p('buttons')==='0' ? $f : $this->url_sort($f)).''.($this->p('buttons')==='0' ? $this->prettyText($f) : $this->url_sort($f)).''.($this->p('buttons')==='0' ? $f : $this->url_sort($f)).''.($this->p('buttons')==='0' ? $this->prettyText($f) : $this->url_sort($f)).'
'.DB_HTML_EDIT.''.$v.''.$v.''.DB_HTML_DELETE.'
'.PHP_EOL; + $html .= ''.TABLE_EOL; + $html .= ''.TABLE_EOL; return $html; } @@ -861,30 +864,30 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. Html Div -----------------------------------------------------------------*/ function rows_begin_div() { - return '
'.PHP_EOL; + return '
'.TABLE_EOL; } function rows_rec_div(&$row) { $html = ''; - $html .= '
    '.PHP_EOL; + $html .= '
      '.TABLE_EOL; if ($this->p('buttons')!=='0') { - $html .= '
    • '.PHP_EOL; - $html .= ''.DB_HTML_EDIT.''.PHP_EOL; - $html .= ''.DB_HTML_DELETE.''.PHP_EOL; - $html .= '
      • '.PHP_EOL; + $html .= '
    • '.TABLE_EOL; + $html .= ''.DB_HTML_EDIT.''.TABLE_EOL; + $html .= ''.DB_HTML_DELETE.''.TABLE_EOL; + $html .= '
      • '.TABLE_EOL; } foreach ($row as $k => $v) { $html .= '
    • ' .( $k == '0' ? '' : '') .''.$v.'' - .'
      • '.PHP_EOL; + .''.TABLE_EOL; } - $html .= '
    '.PHP_EOL; + $html .= '
'.TABLE_EOL; return $html; } @@ -914,7 +917,7 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. ; if (!($query = $this->db->conn->prepare($sql))) { - err('PDO::errorInfo(): ' .join(' ', $this->db->conn->errorInfo()) .PHP_EOL); + err('PDO::errorInfo(): ' .join(' ', $this->db->conn->errorInfo()) .TABLE_EOL); return false; } @@ -922,7 +925,7 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. #debug(array($sql,$values)); if (!($execute = $query->execute())) { - err('PDO::errorInfo(): ' .join(' ', $this->db->conn->errorInfo()) .PHP_EOL); + err('PDO::errorInfo(): ' .join(' ', $this->db->conn->errorInfo()) .TABLE_EOL); return false; } @@ -972,7 +975,7 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. $this->debug($sql,1); if (!($query = $this->db->conn->prepare($sql))) { - err('PDO::errorInfo(): ' .join(' ', $this->db->conn->errorInfo()) .PHP_EOL); + err('PDO::errorInfo(): ' .join(' ', $this->db->conn->errorInfo()) .TABLE_EOL); return false; } @@ -987,7 +990,7 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. #return $sql; #bye($sql); if (!($execute = $query->execute())) { - err('PDO::errorInfo(): ' .join(' ', $this->db->conn->errorInfo()) .PHP_EOL); + err('PDO::errorInfo(): ' .join(' ', $this->db->conn->errorInfo()) .TABLE_EOL); return false; } @@ -1080,7 +1083,7 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. $replace = array(); foreach ($this->replace as $k=>$v) { $replace[] = "$k.$v"; } - $html .= ''.PHP_EOL; + $html .= ''.TABLE_EOL; } diff --git a/lib/php/out.php b/lib/php/out.php index 83989195..40b4c43c 100644 --- a/lib/php/out.php +++ b/lib/php/out.php @@ -15,6 +15,7 @@ class Out extends Nb { 'csv' => array( 'enclose' => array("","\n"), 'eol' => "\n", + 'sep' => (self::p('sep') ? self::p('sep') : "\t"), 'head' => 'out_csv_head', 'function' => 'out_csv', ), @@ -120,7 +121,7 @@ class Out extends Nb { print $conf['enclose'][1]; } -} # < Class +} Out::types() ; # < Class /****************************************************************************/ function out_yaml(&$row,$o) { @@ -130,23 +131,10 @@ function out_yaml(&$row,$o) { } function out_csv(&$row,$o) { - if(is_scalar($row)) echo $row; -# NB 06.03.16 elseif(out::is_hash($row)) { -# NB 06.03.16 return join("\t",array_values($row)); -# NB 06.03.16 } - else { - #bye($row); - #debug(array_values($row)); - #$row = array_values($row); - #$row = array(print_r($row,true)); - echo join("\t",array_values($row)); - } - -} Out::types() ; + echo join($o['sep'],array_values($row)); +} function out_csv_head(&$row,$o) { -# NB 07.03.16 function out_csv_head(&$data,$o) { -# NB 07.03.16 $row = out::ar_first($data); if (!is_array($row)) echo 0; if (out::is_hash($row)) echo out_csv(array_keys($row),$o); echo out_csv($row,$o); @@ -154,7 +142,7 @@ function out_csv_head(&$row,$o) { #function out_tag_head(&$row,$o) { return ''; } function out_tag_head(&$data,$o) { - #return var_dump($row,true); return ''; +#return var_dump($row,true); return ''; $o['tag'] = isset($o['tag_head']) ? $o['tag_head'] : $o['tag']; #echo out_tag($row,$o); return true; $row = out::ar_first($data); @@ -167,10 +155,10 @@ function out_tag_head(&$data,$o) { function out_tag(&$row,$o) { #var_dump($row); return ''; - if (!is_array($row)) return var_export($row,true); +# NB 07.03.16 if (!is_array($row)) return var_export($row,true); foreach ($row as $k => $v) { #if (!is_scalar($v)) continue; - if (!is_scalar($v)) return var_export($row,true); +# NB 07.03.16 if (!is_scalar($v)) return var_export($row,true); $k = empty($o['tag']) ? $k : $o['tag']; return '' .(OUT_EOL ? ' ' : '') -- 2.47.3