From ecbb69d787042da522a7456c09dada97675140b4 Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Wed, 6 Apr 2016 01:34:16 +0100 Subject: [PATCH] Bed --- lib/js/nb.js | 43 ++++++++ lib/php/db/table.php | 218 +++++++++++++++++++++---------------- lib/php/db/types/pgsql.php | 45 ++++---- lib/php/functions.php | 3 +- lib/php/out.php | 3 +- lib/php/page.php | 2 +- 6 files changed, 196 insertions(+), 118 deletions(-) diff --git a/lib/js/nb.js b/lib/js/nb.js index b3e1431c..96e94d28 100644 --- a/lib/js/nb.js +++ b/lib/js/nb.js @@ -522,3 +522,46 @@ NB.urlParam = NB.prototype.urlParam //NB.prototype.test = function(v) { console.log(v)} //NB.test = function() { console.log(that.dump('zaza'))} //NB.prototype.your_method = function() { } +jQuery.fn.numAdd = function(num,re) { + if (typeof num == 'undefined') num = 1; num = parseInt(num); + if (typeof re == 'undefined') re = new RegExp('([\+\-]?\\d+)'); + if (typeof re == 'string') re = new RegExp(re); + //var re = new RegExp(exp); + + return this.each(function() { + var html = $(this).html(); + var m = html.match(re); + if (!m || m.length<2) return; + + for (var i=1;i',$this->name,$this->db()->type('table.sql',true)); $this->sql = $this->db()->row($sql); - # Noise before CREATE (ex: from mysql) - $this->sql = preg_replace("/^\w+\s+(CREATE)/i",'$1',$this->sql); + # Noise before CREATE like MySql + $this->sql = preg_replace("/^\w+\s+(CREATE)/i",'$1',$this->sql,1); # Remove comments $this->sql = join(' ',preg_grep('/^ *--/',explode("\n",$this->sql),true)); @@ -221,7 +221,7 @@ Class Table extends nb { ; } - foreach (self::$params as $p) if ($v=self::p($p)) $url[] = $p.'='.$v; + foreach (self::$params as $p) if ($v=self::p($p)) $url[] = $p.'='.urlencode($v); return $url ? '?'.$sep.join($sep,$url) : ''; } @@ -536,7 +536,8 @@ Class Table extends nb { /****************************************************************** Html Output ******************************************************************/ - public function rows($opt=array()) { + public function rows(&$opt=array(),$opt_by_val=null) { + if ($opt_by_val !== null) $opt = $opt_by_val; # NB 17.03.16 $format = ( $this->p('format') ? $this->p('format') : 'table' ); # NB 19.03.16 $format = $this->pdef('format','table'); @@ -587,16 +588,14 @@ Class Table extends nb { if (!$this->p('action')) echo $this->html_menu(); if (!isset($opt['is_html'])) $opt['is_html'] = preg_match('/^(table|div)$/',$format) - ? ( - $this->p('header')!=="0" - ) + ? ( $this->p('header')!=="0") : false ; # Use the module out when format unknow $out_conf = null; if ($this->p('out') or !preg_match('/^('.join('|', - array( 'table','sql','div','_csv','_xml','_json','_yaml' ) + array( 'table','sql','div','_csv','_xml','_json','_yaml' ) # local ).')$/',$format)) { @@ -620,10 +619,10 @@ Class Table extends nb { if ($opt['is_html']) echo $this->html_rows_top(); if ($out_conf) { - out::head($out_conf,$fields,array($row)); + out::head($out_conf,$this->fields(),array($row)); } else { - echo $this->{"rows_begin_$format"}($fields); + echo $this->{"rows_begin_$format"}($this->fields()); } } @@ -635,7 +634,7 @@ Class Table extends nb { $count_fields = 0; - foreach ($fields as $f => $field) { + foreach ($this->fields() as $f => $field) { $row[$f] = $field->out($row[$f]); $count_fields++; } @@ -643,73 +642,70 @@ Class Table extends nb { if ($out_conf) { out::row($out_conf,$row); } else { - echo $this->{"rows_rec_$format"}($row,$fields); + echo $this->{"rows_rec_$format"}($row); } + } # < rows + + if (!$count) { + $st->closeCursor(); + return; } $opt['tot'] = $count; $opt['count'] = $count; $opt['limit'] = $limit; - if ($count) { - - if ($opt['is_html']) { - // - // Tot - // - if (!$where and !$limit) { - debug("Table.rows(): Not using count(*), use $count",1); - $query = $this->db()->conn->query("SELECT $count"); - - } elseif ($select_count[1]) { - debug("Table.rows(): Using $select_count[1]",1); - $query = $this->db()->conn->query($select_count[1]); - - } elseif ($where) { - $sql_count = $sql; - $sql_count = preg_replace('/ (ORDER|LIMIT) .*?$/s','',$sql_count); - $sql_count = preg_replace('/^SELECT .*FROM/s','SELECT count(*) FROM ',$sql_count); - debug("Table.rows(): Using $sql_count",1); - $query = $this->db()->conn->query($sql_count); + if ($opt['is_html']) { - } else { - $query = $this->db()->conn->query('SELECT count(*) FROM '.$this->sql_name()); + // + // Tot + // + if (!$where and !$limit) { + $debug = "Table.rows(): Not using count(*), use $count"; + $query = $this->db()->conn->query("SELECT $count"); - } + } elseif ($select_count[1]) { + $debug = "Table.rows(): Using $select_count[1]"; + $query = $this->db()->conn->query($select_count[1]); - if (!$query) $this->err_sql($sql); + } elseif ($where) { + $debug = "Table.rows(): Using $sql_count"; + $sql_count = $sql; + $sql_count = preg_replace('/ (ORDER|LIMIT) .*?$/s','',$sql_count); + $sql_count = preg_replace('/^SELECT .*FROM/s','SELECT count(*) FROM ',$sql_count); + $query = $this->db()->conn->query($sql_count); - $tot = $query->fetch(PDO::FETCH_COLUMN); - $opt['tot'] = $tot; - #if (!$tot) return; + } else { + $query = $this->db()->conn->query('SELECT count(*) FROM '.$this->sql_name()); - $opt['count'] = $count; - $opt['limit'] = $limit; } - if ($count === 0 and $this->p('header') === 'force') { - echo $this->{"rows_begin_$format"}($fields); - } + if (!$query) $this->err_sql($sql); - if ($count) { - if ($out_conf) { - out::end($out_conf); - } else { - echo $this->{"rows_end_$format"}(); + $tot = $query->fetch(PDO::FETCH_COLUMN); + $opt['tot'] = $tot; + #if (!$tot) return; - if ($opt['is_html']) { + } # < is_html - echo '
' - .$this->nav($opt['count'],$opt['tot'],$opt['limit']) - .'
'.NB_EOL - ; - } - } + if ($count === 0 and $this->p('header') === 'force') { + echo $this->{"rows_begin_$format"}($this->fields()); + } - } # < is_html + if ($out_conf) { + out::end($out_conf); + } else { + echo $this->{"rows_end_$format"}(); + + if ($opt['is_html']) { + echo '
' + .$this->nav($opt['count'],$opt['tot'],$opt['limit']) + .'
'.NB_EOL + ; + } - } # < count + } # < out $st->closeCursor(); @@ -724,12 +720,12 @@ Class Table extends nb { return "\n-- ".$this->name."\n"; } - public function rows_rec_sql(&$row,$fields) { + public function rows_rec_sql(&$row) { $keys = $values = array(); foreach ($row as $k=>$v) { if (isset($this->extras[$k])) continue; - $f = $fields[$k]; + $f = $this->fields($k); $values[] = $f->quote($v); $keys[] = $f->sql_name(); @@ -840,7 +836,7 @@ Class Table extends nb { } public function rows_rec_csv(&$row) { - return join(TABLE_CSV_SEP,array_values($row))."\n"; + return str_replace("\n",'|',join(TABLE_CSV_SEP,array_values($row)))."\n"; } public function rows_end_csv() { @@ -855,7 +851,7 @@ Class Table extends nb { $html = ''; #$html .= ''.NB_EOL; - $html .= '
'.NB_EOL; + $html .= '
'.NB_EOL; #if (!empty($opt)) $html .= '' .NB_EOL; if ($this->p('header')!=="0") { @@ -910,7 +906,7 @@ Class Table extends nb { Html Div -----------------------------------------------------------------*/ public function rows_begin_div() { - return '
'.NB_EOL; + return '
'.NB_EOL; } public function rows_rec_div(&$row) { @@ -945,9 +941,12 @@ Class Table extends nb { return $this->db()->sql_name($value === null ? $this->name : $value); } - public function insert($hvalues) { + public function insert($hvalues,&$info=array()) { + if (empty($info['values'])) $info['values'] = array(); + $info['values'] = $hvalues; + $sql_names = $fields = $values = array(); -#var_dump($hvalues); + foreach ($this->fields() as $name => $field) { if (!isset($hvalues[$name])) continue; if ($field->key and $field->autoincrement()) continue; @@ -956,13 +955,13 @@ Class Table extends nb { $sql_names[$name] = $field->sql_name(); $values[] = $hvalues[$name]; } - #bye($values); $sql = 'INSERT INTO '. $this->sql_name() . ' (' . join(',',array_values($sql_names)).')' #.' VALUES (' . join(',',$values).')' .' VALUES (' . join(',',ar_map('":$a"',array_keys($fields))) . ')' ; + $info['sql'] = $sql; if (!($query = $this->db()->conn->prepare($sql))) { $this->err_sql($sql); @@ -971,20 +970,30 @@ Class Table extends nb { foreach ($fields as $name => $field) $field->bindParam($query,$hvalues[$name],":$name"); + if (self::p('debug')) { + $this->debug($info,1); + return false; + } + if (!($execute = $query->execute())) { $this->err_sql($sql); return false; } + $info['rowCount'] = $query->rowCount(); + return $query->rowCount(); + return $execute; return $this->db()->exec($sql,$values); return $this->db()->exec($sql); } - public function update($hvalues,&$info=null) { # dbq t=nb.mime_type a=table.update type=application/rss+xml name='RSS - Really Simple Syndication' ext=rss debug=2 - + public function update($hvalues,&$info=array()) { + if (empty($info['values'])) $info['values'] = array(); + $info['values'] = $hvalues; + $keys = array(); $keys_values = array(); $fields = array(); @@ -1026,6 +1035,7 @@ Class Table extends nb { .' SET ' . join(',',$this->ar_map('"$a=:$a"',$fields)) .' WHERE ' . join(' AND ',$this->ar_map('"$a=:key_$a"',$keys)) ; + $info['sql'] = $sql; if (!($query = $this->db()->conn->prepare($sql))) { err('PDO::errorInfo(): ' .join(' ', $this->db()->conn->errorInfo()) .NB_EOL); @@ -1044,19 +1054,26 @@ Class Table extends nb { #return $sql; #bye($sql); + if (self::p('debug')) { + $this->debug($info,1); + return false; + } + if (!($ex = $query->execute())) { err('PDO::errorInfo(): ' .join(' ', $this->db()->conn->errorInfo()) .NB_EOL); return false; } #debug($execute); - $info = array('sql'=>$sql,'values'=>$hvalues,'rowCount'=>$query->rowCount()); - $this->debug($info,1); + $info['rowCount'] = $query->rowCount(); return $query->rowCount(); } - public function delete($hvalues,&$e=null) { + public function delete($hvalues,&$info=array()) { + if (empty($info['values'])) $info['values'] = array(); + $info['values'] = $hvalues; + $keys = $this->fields_keys(); // If no primary keys, we use all field if (empty($keys)) $keys = $this->fields(); @@ -1067,8 +1084,20 @@ Class Table extends nb { } $sql = 'DELETE FROM ' . $this->sql_name() . $where; - #bye($sql); - return $this->db()->exec($sql)->fetchColumn(); + $info['sql'] = $sql; + + if (self::p('debug')) { + $this->debug($info,1); + return false; + } + + $query = $this->db()->exec($sql); + $info['rowCount'] = $query; + return $info['rowCount']; + $info['rowCount'] = $query->rowCount(); + return $info['rowCount']; + # Like insert- NB 05.04.16 +# NB 05.04.16 return $this->db()->exec($sql)->fetchColumn(); } # NB 01.04.16 public function out($v,$head=false) { return $this->db()->out($v,$head); } @@ -1091,6 +1120,14 @@ Class Table extends nb { ; } + private function url_referer() { + if (self::p('referer')) { + return urldecode($this->p('referer')); + } else { + return '?table=' . urlencode($this->name) . (self::p('db') ? '&db='.self::p('db') : ''); + } + } + public function action($action=null) { if ($action === null) $action = $this->p('action'); @@ -1112,34 +1149,28 @@ Class Table extends nb { return true; } elseif ($action == 'table.insert' or $action == 'insert') { - $this->insert($this->p()); - if ($this->p('referer')) { - header('Location: '.urldecode($this->p('referer'))); - } else { - header('Location: ?table=' . $this->name . ($this->p('db') ? '&db='.$this->p('db') : '')); - } - #header('Location: '.str_replace('&','&',$this->url_list())); + if (!$this->insert($this->p(),$e)) bye($e); + header('Location: '.$this->url_referer()); + $this->out2($e); return true; } elseif ($action == 'table.update' or $action == 'update') { #$this->bye($this->p()); - if ($r=$this->update($this->p(),$out)) { - if ($this->p('referer')) { - header('Location: '.urldecode($this->p('referer'))); - } else { - header('Location: ?table=' . $this->name . ($this->p('db') ? '&db='.$this->p('db') : '')); - } - } - #header('Location: '.str_replace('&','&',$this->url_list())); - $this->out2($out); - return $r; + if (!$this->update($this->p(),$e)) bye($e); + header('Location: '.$this->url_referer()); + $this->out2($e); + return true; } elseif (preg_match('/^table\.(\w+)/',$action,$m)) { - if ($rows = $this->$m[1]()) $return = $this->out2($rows,(is_scalar($rows) ? $m[1] : array())); + if ($rows = $this->$m[1]() and !empty($rows)) { + $return = $this->out2($rows,(is_scalar($rows) ? $m[1] : array())); + } else { + $return = true; + } } elseif ($this->p('format') and !preg_match('/^(table|div)$/',$this->p('format'))) { - $this->rows(array( 'format' => $this->p('format'))); + $this->rows($dummy,array('format' => $this->p('format'))); return true; } elseif ($action == 'edit') { @@ -1201,6 +1232,9 @@ Class Table extends nb { public function html_menu() { $r = '
'.NB_EOL; + # See: http://html5doctor.com/html5-forms-input-types/ + #$r .= ''; + #$r .= ''; // // Options diff --git a/lib/php/db/types/pgsql.php b/lib/php/db/types/pgsql.php index 4f8d703c..37b12b30 100644 --- a/lib/php/db/types/pgsql.php +++ b/lib/php/db/types/pgsql.php @@ -6,28 +6,7 @@ $DB_TYPES['pgsql'] = array ( 'tables' => '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\')', -'table.fields' => array ( - 'fct' => create_function('&$r',join('',array( - 'if (!isset($r["pg_default"])) return;', - 'if (preg_match("/^nextval\(/",$r["pg_default"])) { $r["autoincrement"] = 1; }', - 'elseif (preg_match("/^\'\'/",$r["pg_default"])) { $r["default"] = ""; }', - 'else { $r["default"] = $r["pg_default"]; }', - ))), - 'sql' => 'SELECT -a.attname AS name, -pg_catalog.format_type(a.atttypid, a.atttypmod) AS type, -CASE a.attnotnull WHEN \'f\' then 1 ELSE 0 END AS null, -(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) AS pg_default, -(SELECT 1 FROM pg_index i WHERE a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) AND i.indrelid = \'\'::regclass AND i.indisprimary) as key -FROM pg_catalog.pg_attribute a -WHERE a.attrelid = ( - SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname=\'\' AND pg_catalog.pg_table_is_visible(c.oid) - ) - AND a.attnum > 0 AND NOT a.attisdropped -ORDER BY a.attnum', - -'table.sql' => ' -SELECT CASE c.relkind::char WHEN \'r\' THEN ( +'table.sql' => 'SELECT CASE c.relkind::char WHEN \'r\' THEN ( SELECT \'CREATE TABLE \'||sql.table||\'( \' @@ -79,7 +58,27 @@ GROUP BY sql.id,sql.table WHEN \'v\' THEN trim(regexp_replace(pg_catalog.pg_get_viewdef(c.oid::pg_catalog.oid, true),\'\\s\\s+\',\' \',\'g\')) ELSE \'NOT\' END as sql FROM pg_catalog.pg_class c WHERE c.relname = \'\' AND pg_catalog.pg_table_is_visible(c.oid) - ', +', # < table.sql + +'table.fields' => array ( + 'fct' => create_function('&$r',join('',array( + 'if (!isset($r["pg_default"])) return;', + 'if (preg_match("/^nextval\(/",$r["pg_default"])) { $r["autoincrement"] = 1; }', + 'elseif (preg_match("/^\'\'/",$r["pg_default"])) { $r["default"] = ""; }', + 'else { $r["default"] = $r["pg_default"]; }', + ))), + 'sql' => 'SELECT +a.attname AS name, +pg_catalog.format_type(a.atttypid, a.atttypmod) AS type, +CASE a.attnotnull WHEN \'f\' then 1 ELSE 0 END AS null, +(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) AS pg_default, +(SELECT 1 FROM pg_index i WHERE a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) AND i.indrelid = \'\'::regclass AND i.indisprimary) as key +FROM pg_catalog.pg_attribute a +WHERE a.attrelid = ( + SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname=\'\' AND pg_catalog.pg_table_is_visible(c.oid) + ) + AND a.attnum > 0 AND NOT a.attisdropped +ORDER BY a.attnum', ), );?> diff --git a/lib/php/functions.php b/lib/php/functions.php index 227e98f6..411bbb4d 100644 --- a/lib/php/functions.php +++ b/lib/php/functions.php @@ -107,7 +107,8 @@ function warn ($msg) { } function err($msg='__err__',$preff='err',$backtrace_deep=0) { - $msg = is_scalar($msg) ? $msg : print_r($msg,true); +# NB 05.04.16 $msg = is_scalar($msg) ? $msg : print_r($msg,true); + $msg = is_scalar($msg) ? $msg : var_export($msg,true); $preff_msg = $preff ? strtoupper($preff).': ' : ''; if ($msg !== '__err__' and $backtrace_deep !== false) { $msg = trim($preff_msg.$msg).' ' diff --git a/lib/php/out.php b/lib/php/out.php index bf6d0b47..3ad25d54 100644 --- a/lib/php/out.php +++ b/lib/php/out.php @@ -289,6 +289,7 @@ Class Out extends Nb { $replace_flags = $replace_flags | ENT_HTML5; } else { # text + return str_replace("\n",'|',$v); return $v; return preg_replace('/\n/','',$v); @@ -326,7 +327,7 @@ function out_csv(&$row,$o) { } #echo join($o['sep'],array_values($row)); - echo join($o['sep'],$values); + echo str_replace("\n",'|',join($o['sep'],$values)); } function out_csv_head(&$row,$o) { diff --git a/lib/php/page.php b/lib/php/page.php index 726692ba..9e8d630e 100644 --- a/lib/php/page.php +++ b/lib/php/page.php @@ -24,7 +24,7 @@ class Page extends nb { protected static $content_type = 'text/html'; public static $charset = 'utf-8'; public static $lang = 'en'; - public static $html5 = false; + public static $html5 = true; public $title = ''; public $css = array(); -- 2.47.3
' . $this->nav($opt['count'],$opt['tot'],$opt['limit']) . '