From d59aac3e5955b1c12b495964e153784bd7b13da9 Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Sun, 28 Feb 2016 05:51:38 +0000 Subject: [PATCH] out(), clean html, ... --- lib/php/db.php | 49 +++++----- lib/php/db/table.php | 210 +++++++++++++++++++++++++++++++------------ lib/php/nb.php | 28 ++++++ 3 files changed, 207 insertions(+), 80 deletions(-) diff --git a/lib/php/db.php b/lib/php/db.php index 9539aa7a..52e6467e 100644 --- a/lib/php/db.php +++ b/lib/php/db.php @@ -8,7 +8,6 @@ *****************************************************************************/ require_once(dirname(__FILE__).'/nb.php'); require_once(dirname(__FILE__).'/db/table.php'); -# NB 29.12.15 define('SQL_NAME_FORCE_QUOTE_EXP','^(range)$'); if (!defined('DB_HTML_FORM_BUTTONS')) define('DB_HTML_FORM_BUTTONS','' #.'' #.'' @@ -263,9 +262,9 @@ EOF; function print_header($format) { if (empty($_SERVER['DOCUMENT_ROOT'])) return null; - if (!$c=$this->ext2mime($format)) return false; - header('Content-type: '.$c); - return true; + if (!$mime=$this->ext2mime($format)) return false; + header('Content-type: '.$mime); + return $mime; } function html_menu($table=null) { @@ -362,41 +361,43 @@ EOF; } function sql_name($value) { -# NB 29.12.15 if (preg_match('/^\w+$/',$value) and -# NB 29.12.15 ( -# NB 29.12.15 !SQL_NAME_FORCE_QUOTE_EXP or -# NB 29.12.15 !preg_match('/'.SQL_NAME_FORCE_QUOTE_EXP.'/',$value) -# NB 29.12.15 ) -# NB 29.12.15 ) return $value; if ($this->type == 'mysql') return '`'.$value.'`'; return '"'.$value.'"'; } + function out($v) { + $f = $this->p('format','json'); + #if (preg_match('/yaml|yml/',$f)) bye($this->yaml_encode($this->object2array($this->fields()))); + $mime = $this->print_header($f); + + #echo $v.PHP_EOL; + #bye($f); return true; + if ($f == 'json') { echo json_encode($v).PHP_EOL; } + elseif (preg_match('/yaml|yml/',$f)) { echo $this->yaml_encode($v); } + elseif (preg_match('/php|var_export/',$f)) { echo var_export($v,true); } + elseif (preg_match('/var_dump/',$f)) { echo var_dump($v,true); } + elseif (is_scalar($v)) { echo($v.PHP_EOL); } + //else { echo($v.PHP_EOL); } + else{ echo var_export($v,true); } + + return true; + + } + function action($table) { if ($this->p('format') == 'table') $this->pset('format',''); $action = $this->p('action'); - $formated = $this->print_header($this->p('format')); - $data = null; - if ($action == 'tables') { - $this->print_header('json'); - $data = $this->tables(); - echo json_encode($data); - return true; - - } elseif ($action == 'fields') { - $this->print_header('json'); - $data = $table->fields(); - echo json_encode($data); - return true; + return $this->out($this->tables()); } elseif ($action == 'html_menu') { + $this->print_header($this->p('format','html')); echo $this->html_menu(); return true; } else { - if ($table->action()) return true; + if ($r=$table->action()) return $r; } diff --git a/lib/php/db/table.php b/lib/php/db/table.php index 08f6e8a6..beb7f9ae 100644 --- a/lib/php/db/table.php +++ b/lib/php/db/table.php @@ -16,7 +16,6 @@ if (false and empty($_SERVER['DOCUMENT_ROOT'])) { if (!defined('DB_HTML_EDIT')) define('DB_HTML_EDIT','Edit'); if (!defined('DB_HTML_DELETE')) define('DB_HTML_DELETE','Delete'); -if (!defined('DB_HTML_NAV_TOP')) define('DB_HTML_NAV_TOP',''.PHP_EOL); if (!defined('DB_TABLE_QUERY_NAME')) define('DB_TABLE_QUERY_NAME','_query_'); class table extends nb { @@ -28,10 +27,11 @@ class table extends nb { public $fields_keys = null; public $replace = array(); # replace by javascript public $extras = array(); - public $params = array( + public $params = array( # hidden, sort, ... fields 'table', 'limit', 'debug', + 'action', ); public $order_by = null; @@ -60,7 +60,10 @@ class table extends nb { $this->name = $name; // Extras - if (isset($opt['extras'])) { + if ($this->p('extras')==='0') { + $this->extras = array(); + + } else if (isset($opt['extras'])) { $this->add_extras($opt['extras']); } elseif ( @@ -89,12 +92,64 @@ class table extends nb { */ function sql() { if (isset($this->sql)) return $this->sql; + + if (!preg_match('/^[\w_-]+$/',$this->name) ) { + $this->sql = $this->name; + return $this->sql; + } + if ($this->db->type == 'sqlite') { $sql = "SELECT sql FROM sqlite_master WHERE name='$this->name'"; } elseif ($this->db->type == 'mysql') { $sql = "SHOW CREATE TABLE `$this->name`"; } elseif ($this->db->type == 'pgsql') { $sql = "SELECT show_create_table('$this->name')"; + $sql = " +SELECT + 'CREATE TABLE '||sql.table||'(' + ||array_to_string(array_agg(sql),', ') + ||')' as sql +FROM ( + ( + SELECT -- FIELDS + c.oid AS id + ,c.relname as table + ,9 as prio + ,'' + || f.attname + || ' ' || pg_catalog.format_type(f.atttypid,f.atttypmod) + ||CASE WHEN f.attnotnull THEN ' NOT NULL' ELSE '' END + ||CASE WHEN f.atthasdef = 't' AND d.adsrc !=''THEN ' DEFAULT '||d.adsrc ELSE '' END + AS sql + FROM pg_attribute f + JOIN pg_class c ON c.oid = f.attrelid + LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum + WHERE c.relkind = 'r'::char + AND f.attnum > 0 + ORDER BY f.attnum + ) UNION ( + SELECT -- CONSTRAINTS + c.oid as id + ,c.relname as table + ,0 as prio + ,CASE + WHEN p.contype = 'p' THEN 'PRIMARY KEY' + WHEN p.contype = 'u' THEN 'UNIQ' + ELSE '' END + ||'('||array_to_string(array_agg(f.attname),', ')||')' AS sql + FROM pg_attribute f + JOIN pg_class c ON c.oid = f.attrelid + LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey) + WHERE c.relkind = 'r'::char + AND f.attnum > 0 + AND p.contype IN ('u','p') + GROUP BY c.oid,p.contype,f.attrelid,c.relname + ORDER BY c.oid,f.attrelid + ) +ORDER BY prio DESC) sql +WHERE sql.table='$this->name' +GROUP BY sql.id,sql.table +"; } else { err('table.sql(): Unknow db type: '.$this->db->type); } @@ -153,7 +208,7 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. #bye($row); $field = array( 'name' => $row['name'], - 'extra' => null, + 'extra' => null, # !!! nothing todo with class variable $extras 'type' => strtolower($row['type']), 'default' => null, 'key' => null, @@ -389,24 +444,12 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. $next = 999999; } - #$html .= ''.PHP_EOL; - static $js = null; - if ($js === null) { - $html .= ''.PHP_EOL - ; - $js = '1'; - } - return $html; } @@ -578,7 +621,8 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. if (!$this->extras) return ''; $select = array(); foreach ($this->extras as $k => $v) { - $select[] = $v->extra." AS ".$v->quote($k,true); +# NB 27.02.16 $select[] = $v->extra." AS ".$v->quote($k,true); + $select[] = $v->extra." AS ".$this->sql_name($k); } return ','.join(',',$select); @@ -597,8 +641,7 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. $sql = "SELECT$select_count *" . $this->select_extras(); $sql .= " FROM ".$this->sql_name(); - - $this->sql = $sql = $sql . $where; + $sql .= $where; if ($this->p('sort')) $sql .= ' ORDER BY '.$this->p('sort'); @@ -612,15 +655,20 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. // // Get results // + $this->sql = $sql; $this->debug($sql,1); $st = $this->db->conn->prepare($sql); $st->execute(); $format = ( $this->p('format') ? $this->p('format') : 'table' ); - if (!isset($opt['is_html'])) $opt['is_html'] = preg_match('/^(table|div)$/',$format) ? true : false; + if (!isset($opt['is_html'])) $opt['is_html'] = preg_match('/^(table|div)$/',$format) + ? ( $this->p('header')!=="0" and $this->p('inc')!=="1") + : false + ; - if (preg_match('/^(1)?$/',$this->p('header'))) echo $this->{"rows_begin_$format"}($this->fields()); + if ($opt['is_html']) echo $this->html_nav_top(); + echo $this->{"rows_begin_$format"}($this->fields()); $count = 0; while ($row = $st->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)) { @@ -668,7 +716,8 @@ FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid FROM pg_catalog. $opt['count'] = $count; $opt['limit'] = $limit; echo $this->{"rows_end_$format"}($opt); - if ($opt['is_html']) echo '