From 0c1fc7effd1503574af54a39340c50449f167d40 Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Mon, 4 Apr 2016 01:55:17 +0100 Subject: [PATCH] Bed --- etc/dbs.yaml | 15 +++++- lib/php/cache.php | 2 +- lib/php/config.php | 7 +-- lib/php/db.php | 18 ++++--- lib/php/db/table.php | 98 +++++++++++++++++++--------------- lib/php/db/types/mysql.php | 2 + lib/php/db/types/pgsql.php | 2 + lib/php/db/types/sqlite.php | 10 ++-- lib/php/nb.php | 5 +- lib/php/out.php | 2 +- lib/php/page.php | 102 ++++++++++++++++++++---------------- 11 files changed, 157 insertions(+), 106 deletions(-) diff --git a/etc/dbs.yaml b/etc/dbs.yaml index d3429978..ffbe6204 100644 --- a/etc/dbs.yaml +++ b/etc/dbs.yaml @@ -16,6 +16,7 @@ nb: _import: - _nico - _mysql + #- _rent puppetdb: #pdo: 'pgsql:host=big;dbname=puppetdb;user=puppetdb;password=' @@ -47,13 +48,20 @@ izi: title: Mysql Izi on Admin default_table: process host: admin.izideal.vpn - user: izideal + #user: izideal + name: izi _import: - _nico - _mysql + tables: + site: + replace: + idlang: lang.id izidev: + host: big.cascais.loc title: Mysql Izi on Big + #user: nico _import: - izi @@ -81,6 +89,10 @@ rt: rent: order: 1 pdo: 'sqlite:/opt/rent/rent.db' + _import: + - _rent + +_rent: title: 'Rent' notice: 'Micro foncier 4BE' default_table: 'rent' @@ -110,6 +122,7 @@ rent: rent_year: type: "float(8,2)" extra: "rent * (1 + ( strftime('%m',end)+12*strftime('%Y',end) ) - ( strftime('%m',start)+12*strftime('%Y',start) ))" + #extra: "rent * (1 + ( date_format(end,'%m')+12*date_format(end,'%Y') ) - ( date_format(start,'%m')+12*date_format(start,'%Y') ))" #revision: (SELECT GROUP_CONCAT(''||substr(id,0,instr(id,'.'))||'',' ') FROM template) # TODEL - NB 10.01.16 diff --git a/lib/php/cache.php b/lib/php/cache.php index e304bf6a..0b29e34c 100644 --- a/lib/php/cache.php +++ b/lib/php/cache.php @@ -1,5 +1,5 @@ diff --git a/lib/php/db.php b/lib/php/db.php index a8841a40..2b1c58e4 100644 --- a/lib/php/db.php +++ b/lib/php/db.php @@ -135,8 +135,6 @@ class Db extends nb { if (!$this->type(null)) $this->bye("unknow type = `".$this->type."`"); # Connect -# NB 28.03.16 if (preg_match('/^sqlite:(.*)$/',$this->pdo,$m) and !is_readable($m[1])) { -# NB 28.03.16 $this->bye("Can't connect to database `".$m[1]."` type=$this->type",false); if ($this->type('use_path') and !is_readable($this->name)) { $this->bye("Can't read database file `".$this->name."` type=`$this->type`",false); @@ -337,7 +335,7 @@ class Db extends nb { # NB 28.03.16 } function help($tables=null) { - if (!empty($_SERVER['DOCUMENT_ROOT'])) header('Content-type: text/plain'); + if (!self::php_cli()) header('Content-type: text/plain'); if ($tables === null) $tables = array_keys($this->tables()); $tables = join('',ar_map('" ".$a."\n"',$tables)); @@ -368,9 +366,7 @@ EOF; } function sql_name($value) { - if ($this->type == 'mysql' - or $this->type == 'sqlite' - ) return '`'.$value.'`'; + if ($q=$this->type('quote_name')) return $q.$value.$q; return '"'.$value.'"'; } @@ -658,6 +654,16 @@ EOF; ."-- Pdo : ".$this->pdo_info()."\n" #."-- Host : ".$this->host."\n" ; + +# NB 03.04.16 if ($insert) { +# NB 03.04.16 if ($sql = $this->type('post_connect')) { +# NB 03.04.16 if (is_scalar($sql)) $sql = array($sql); +# NB 03.04.16 foreach ($sql as $s) { +# NB 03.04.16 echo "$sql;\n"; +# NB 03.04.16 } +# NB 03.04.16 } +# NB 03.04.16 } + foreach ($this->tables() as $t) { if ($insert) { echo "\n-- Table: ".$t->name."\n"; diff --git a/lib/php/db/table.php b/lib/php/db/table.php index a954cae5..1fc26d67 100644 --- a/lib/php/db/table.php +++ b/lib/php/db/table.php @@ -2,6 +2,7 @@ require_once(dirname(__FILE__).'/../db.php'); require_once(dirname(__FILE__).'/../db/field.php'); require_once(dirname(__FILE__).'/../out.php'); +#$a = array('a','b','c'); $b = array('c','a'); debug(array_diff($a,$b)); define('TABLE_INDENT',NB_EOL ? "\t" : ""); define('TABLE_CSV_SEP',nb::p('sep') ? nb::p('sep') : "\t"); @@ -26,7 +27,7 @@ Class Table extends nb { public $created; # hidden, sort, ... fields - public static $params = array( 'table', 'limit', 'debug', 'action'); + public static $params = array( 'db', 'table', 'limit', 'debug', 'action'); public $orderby = null; public $fields; @@ -84,6 +85,7 @@ Class Table extends nb { if (stripos($this->name,'SELECT ')===0) { #$temp = '_'.substr(md5($this->name),0,6); $temp = DB_TABLE_QUERY_NAME; + #bye("CREATE TEMPORARY VIEW $temp AS $this->name"); $this->db()->conn->query("CREATE TEMPORARY TABLE $temp AS $this->name"); $this->name = $temp; @@ -128,7 +130,7 @@ Class Table extends nb { $sql = str_replace('',$this->name,$this->db()->type('table.sql',true)); $this->sql = $this->db()->row($sql); - # Noise before CREATE from mysql + # Noise before CREATE (ex: from mysql) $this->sql = preg_replace("/^\w+\s+(CREATE)/i",'$1',$this->sql); # Remove comments @@ -219,8 +221,8 @@ Class Table extends nb { ; } - if (($v = $this->p('db'))) $url[] = 'db='.$this->p('db'); - return $url ? '?table='.$this->p('table').$sep.join($sep,$url) : ''; + foreach (self::$params as $p) if ($v=self::p($p)) $url[] = $p.'='.$v; + return $url ? '?'.$sep.join($sep,$url) : ''; } public function fields_keys(&$others=array()) { @@ -239,6 +241,17 @@ Class Table extends nb { } + public static function form_hidden($ignore=array()) { + $h = ''; + foreach (array_diff(self::$params,$ignore) as $p) { + if ($v=self::p($p)) { + if (self::p('debug')) $h .= ""; + $h .= ''; + } + } + return $h; + } + public function html_edit($values = null) { if ($values === null) $values = $this->p(); if (!is_array($values)) $values = array($values); @@ -257,7 +270,7 @@ Class Table extends nb { $sql .= " FROM ".$this->sql_name().$where; # NB 28.03.16 $this->sql = $sql; - $this->debug($sql,1); + $this->debug(preg_replace('/(,|FROM|WHERE|HAVING|GROUP|ORDER)/i',"\n\\1",$sql),1); $st = $this->db()->conn->prepare($sql); $st->execute(); @@ -276,7 +289,7 @@ Class Table extends nb { } } - echo ''.NB_EOL; + echo ''.NB_EOL; # < fields echo '' .'
' @@ -284,11 +297,12 @@ Class Table extends nb { .'' .'' .'
'.NB_EOL - .'' - .'' - .'' - .'' - .'' + ; + + echo '' + .'' + .self::form_hidden(array('action','HTTP_REFERER')) + .(!empty($_SERVER['HTTP_REFERER']) ? '' : '') .''.NB_EOL; } @@ -298,7 +312,7 @@ Class Table extends nb { $params = array(); $fields = ($this->p('action') == 'delete') ? array() : $this->fields(); - foreach (array_merge( self::$params, array_keys($fields) ) as $f) { + foreach ( array_diff( array_merge(self::$params,array_keys($fields)), array('action') ) as $f) { if (strcmp($this->p($f,''),'')==0) continue; $params[$f] = $this->p($f); @@ -315,7 +329,7 @@ Class Table extends nb { } - if ($this->p('db')) $params['db'] = $this->p('db'); +# NB 03.04.16 if ($this->p('db')) $params['db'] = $this->p('db'); $flat = array(); foreach ($params as $k=>$v) { $flat[] = $k.'='.urlencode($v); } return $flat ? '?'. join('&',$flat) : ''; @@ -469,10 +483,12 @@ Class Table extends nb { $k = "COALESCE($k,".$this->db()->quote('').")"; } - if ($this->db()->type == 'mysql' and $field->extra) { + # having, denorm, EMPTY + $extra_where = (string)$this->db()->type('extra_where'); + if ($extra_where == 'having' and $field->extra) { $having[] = "$k$equal$v"; - } elseif ($this->db()->type == 'pgsql' and $field->extra) { + } elseif ($extra_where == 'denorm' and $field->extra) { $where[] = $this->extras[$k]->sql_name()."$equal$v"; } else { @@ -532,9 +548,10 @@ Class Table extends nb { // Select // $where = $this->where_criterias($this->p(),$this->p('op')); - $select_count = ( (false and $where and $this->db()->type =='mysql') ? " SQL_CALC_FOUND_ROWS" : ""); + $select_count = ( $where ? $this->db()->type('select_count') : null ); + if (empty($select_count)) $select_count = array('',''); - $sql = "SELECT$select_count *" . $this->select_extras(); + $sql = "SELECT ".trim("$select_count[0] *"). $this->select_extras(); $sql .= " FROM ".$this->sql_name(); $sql .= $where; @@ -551,7 +568,7 @@ Class Table extends nb { // Get results // # NB 28.03.16 $this->sql = $sql; - $this->debug($sql,1); + $this->debug(preg_replace('/(,|FROM|WHERE|HAVING|GROUP|ORDER)/i',"\n\\1",$sql),1); $st = $this->db()->conn->prepare($sql); $st->execute(); @@ -628,16 +645,18 @@ Class Table extends nb { // Tot // if (!$where and !$limit) { - debug("Table.rows(): Not using count(*)",1); + debug("Table.rows(): Not using count(*), use $count",1); $query = $this->db()->conn->query("SELECT $count"); - } elseif ($select_count) { - $query = $this->db()->conn->query('SELECT FOUND_ROWS()'); + } 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); } else { @@ -913,32 +932,33 @@ Class Table extends nb { } public function insert($hvalues) { - $fields = $values = array(); + $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; - $fields[$field->sql_name()] = $field; + #$fields[$field->sql_name()] = $field; + $fields[$name] = $field; + $sql_names[$name] = $field->sql_name(); $values[] = $hvalues[$name]; } #bye($values); $sql = - 'INSERT INTO '. $this->sql_name() . ' (' . join(',',array_keys($fields)).')' + 'INSERT INTO '. $this->sql_name() . ' (' . join(',',array_values($sql_names)).')' #.' VALUES (' . join(',',$values).')' .' VALUES (' . join(',',ar_map('":$a"',array_keys($fields))) . ')' ; if (!($query = $this->db()->conn->prepare($sql))) { - err('PDO::errorInfo(): ' .join(' ', $this->db()->conn->errorInfo()) .NB_EOL); + $this->err_sql($sql); return false; } foreach ($fields as $name => $field) $field->bindParam($query,$hvalues[$name],":$name"); -#debug(array($sql,$values)); if (!($execute = $query->execute())) { - err('PDO::errorInfo(): ' .join(' ', $this->db()->conn->errorInfo()) .NB_EOL); + $this->err_sql($sql); return false; } @@ -1033,7 +1053,7 @@ Class Table extends nb { } $sql = 'DELETE FROM ' . $this->sql_name() . $where; - bye($sql); + #bye($sql); return $this->db()->exec($sql); } @@ -1124,6 +1144,7 @@ Class Table extends nb { } public function html_rows_top() { + if (self::p('replace') === '0') return ''; $html = ''; if (!empty($this->replace)) { @@ -1157,7 +1178,7 @@ Class Table extends nb { } - public static function err_sql($sql) { + public function err_sql($sql) { $err = $this->db()->conn->errorInfo(); $err[] = $sql; self::bye(join(' | ',$err)); @@ -1167,22 +1188,15 @@ Class Table extends nb { $r = '
'.NB_EOL; - // - // Hiddens - // - foreach (array( - 'db', - 'debug', - ) as $v) { - $r .= ''; - } - $r .= ''; // Embed for no html format - // // Options // $r .= '
'; + // Hiddens + $r .= self::form_hidden(array('db','table','format','limit','download')); + $r .= ''; // Embed for no html format + // Format $r .= ''; @@ -1200,8 +1214,8 @@ Class Table extends nb { if (!empty($this->db()->dbs) and count($this->db()->dbs)>1) { $r .= ''; $r .= ''.html_select_array($this->db()->dbs,array( - 'html' => 'class="dbs" onchange="document.location=\''.preg_replace('/\?.*$/','',$_SERVER['REQUEST_URI']).'?db=\'+this.value"', - 'selected' => $this->name, + 'html' => 'class="dbs" name="db" onchange="document.location=\''.preg_replace('/\?.*$/','',$_SERVER['REQUEST_URI']).'?db=\'+this.value"', + 'selected' => self::p('db'), 'prettyText' => true, )); $r .= ''; diff --git a/lib/php/db/types/mysql.php b/lib/php/db/types/mysql.php index d15809f7..e5a5e71b 100644 --- a/lib/php/db/types/mysql.php +++ b/lib/php/db/types/mysql.php @@ -2,7 +2,9 @@ $DB_TYPES['mysql'] = array ( 'quote_name' => '`', +#'select_count' => array('SQL_CALC_FOUND_ROWS','SELECT FOUND_ROWS()'), 'post_connect' => 'SET NAMES utf8', +'extra_where' => 'having', 'localFile' => array (getenv('HOME').'/.my.cnf','^(?:user(?:name)?=(?P\\S+)|password=(?P\\S+))'), diff --git a/lib/php/db/types/pgsql.php b/lib/php/db/types/pgsql.php index ad72ea0b..4f8d703c 100644 --- a/lib/php/db/types/pgsql.php +++ b/lib/php/db/types/pgsql.php @@ -1,5 +1,7 @@ 'denorm', + 'localFile' => array (getenv('HOME').'/.pgpass','^[^:]+:[^:]+::(?P[^:]+):(?[^:]+)'), '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\')', diff --git a/lib/php/db/types/sqlite.php b/lib/php/db/types/sqlite.php index 11b08dd3..bab403e5 100644 --- a/lib/php/db/types/sqlite.php +++ b/lib/php/db/types/sqlite.php @@ -9,13 +9,13 @@ $DB_TYPES['sqlite'] = array ( 'sqliteCreateFunction' => array ( 'ip2int' => function ($value) { return ip2long($value); }, 'concat' => function ($v1,$v2) { return $v1.$v2; }, + 'date_format' => function ($date,$format) { return strftime($format,$date); }, 'regexp' => function ($pattern, $data, $delimiter = '~', $modifiers = 'isuS') { - if (isset($pattern, $data) === true) { - return (preg_match(sprintf('%1$s%2$s%1$s%3$s', $delimiter, $pattern, $modifiers), $data) > 0); - } - return null; + if (isset($pattern, $data) === true) { + return (preg_match(sprintf('%1$s%2$s%1$s%3$s', $delimiter, $pattern, $modifiers), $data) > 0); } - , + return null; + }, 'to_char' => array(function ($value,$format) { $replace = array( 'YYYY' => '%Y', diff --git a/lib/php/nb.php b/lib/php/nb.php index 0a4ff75f..d039f61e 100644 --- a/lib/php/nb.php +++ b/lib/php/nb.php @@ -1,12 +1,12 @@ test()); +class NB { const ROOT_DIR = NB_ROOT; @@ -27,6 +27,7 @@ class nb { ); #public static function zaza() { return (is_object($this) ? 'TRUE' : 'FALSE'); } + public static function test() { return 'TEST'; } public function __sleep() { return array_keys((array)$this); } #public function __wakeup() { } diff --git a/lib/php/out.php b/lib/php/out.php index 9215aa9f..bf6d0b47 100644 --- a/lib/php/out.php +++ b/lib/php/out.php @@ -1,5 +1,5 @@ charset); + $charset = strtoupper(self::$charset); #$replace_flags = ENT_COMPAT | ENT_DISALLOWED; $replace_flags = ENT_COMPAT; - if (preg_match('/html/',$this->content_type)) { + if (preg_match('/html/',self::$content_type)) { $replace_flags = $replace_flags | ENT_XHTML; - } elseif (preg_match('/xml/',$this->content_type)) { + } elseif (preg_match('/xml/',self::$content_type)) { $replace_flags = $replace_flags | ENT_XML1; } else { #$replace_flags = $replace_flags | ENT_HTML401; @@ -157,7 +157,7 @@ class Page extends nb { return htmlspecialchars($v,$replace_flags,$charset); } - function tag($tag,$content='',$attrs='') { + public static function tag($tag,$content='',$attrs='') { # Extract attrs from tag if (preg_match('/^([\w-]+)\s+(.*?)$/',$tag,$m)) { @@ -166,7 +166,7 @@ class Page extends nb { } if ($content===null) { - $this->tag_end($tag); + self::tag_end($tag); return '<' . $tag . ($attrs ? " $attrs" : "") . '>'; } @@ -200,7 +200,7 @@ class Page extends nb { static function debug($debug,$level=0) { if (is_array($debug) or is_object($debug)) $debug = print_r($debug,true); - if (preg_match('/ml$/',$this->content_type)) { + if (preg_match('/ml$/',self::$content_type)) { #echo ""; echo "
".htmlentities($debug)."
".NB_EOL; @@ -218,22 +218,22 @@ class Page extends nb { $this->headers(); - if (preg_match('/html$/',$this->content_type)) { + if (preg_match('/html$/',self::$content_type)) { echo $this->doctype(); - if (!$this->lang) { + if (!self::$lang) { echo ' '.NB_EOL; - } elseif ($this->html5) { - echo ' '.NB_EOL; + } elseif (self::$html5) { + echo ' '.NB_EOL; } else { - echo ''.NB_EOL; + echo ''.NB_EOL; } } - if (preg_match('/ml$/',$this->content_type)) { + if (preg_match('/ml$/',self::$content_type)) { echo $this->head(); @@ -250,7 +250,7 @@ class Page extends nb { } - function tag_end($tag=null) { + public static function tag_end($tag=null) { static $tags = array(); if ($tag !== null) return array_unshift($tags,$tag); foreach ($tags as $t) echo "".NB_EOL; @@ -261,11 +261,10 @@ class Page extends nb { array_unshift($tags,$tag); } - function end() { + public static function end() { - if (preg_match('/ml$/',$this->content_type)) { - #foreach ($this->tag_end() as $t) echo "".NB_EOL; - $this->tag_end(); + if (preg_match('/ml$/',self::$content_type)) { + self::tag_end(); echo '' . NB_EOL; echo '' . NB_EOL; } @@ -276,7 +275,7 @@ class Page extends nb { * Function client_content_type * Return the head Accept from the client */ - function client_content_type() { + public static function client_content_type() { $headers = getallheaders(); if (0 or !isset($headers['Accept']) @@ -286,29 +285,37 @@ class Page extends nb { return $v ? $v[0] : ''; } -# NB 19.08.15 function header($name,$value=null) { -# NB 19.08.15 -# NB 19.08.15 if (empty($_SERVER['DOCUMENT_ROOT'])) return false; -# NB 19.08.15 if ($value === null) return $this->get_header($name); -# NB 19.08.15 header("$name: $value"); -# NB 19.08.15 -# NB 19.08.15 } + public static function headers_no_cache() { + $ts = gmdate("D, d M Y H:i:s") . " GMT"; + header("Expires: $ts"); + header("Last-Modified: $ts"); + header("Pragma: no-cache"); + header("Cache-Control: no-cache, must-revalidate"); + } - function headers() { + public static function headers_cache($seconds_to_cache=3600) { + $ts = gmdate("D, d M Y H:i:s", time() + $seconds_to_cache) . " GMT"; + header("Expires: $ts"); + header("Pragma: cache"); + header("Cache-Control: max-age=$seconds_to_cache"); + } - #header('Content-type: ' . $this->content_type); - $c = strtoupper ( $this->charset ); -# NB 19.08.15 $this->header('Content-type: ',$this->content_type . ($c ? "; charset=$c" : "")); - header('Content-type: '.$this->content_type . ($c ? "; charset=$c" : "")); + public function headers() { + + #header('Content-type: ' . self::$content_type); + $c = strtoupper ( self::$charset ); +# NB 19.08.15 $this->header('Content-type: ',self::$content_type . ($c ? "; charset=$c" : "")); + header('Content-type: '.self::$content_type . ($c ? "; charset=$c" : "")); if (self::p('download-attachment')) header('Content-Disposition: attachment; filename="' - . $this->title2filename($this->title,$this->content_type) + . $this->title2filename($this->title,self::$content_type) ); + #self::headers_cache(); return true; } - function doctype($content_type = null) { - if (!$content_type) $content_type = $this->content_type; + public function doctype($content_type = null) { + if (!$content_type) $content_type = self::$content_type; $doctype = ''; if (preg_match('/frame/',$content_type)) { @@ -316,8 +323,8 @@ class Page extends nb { } elseif (preg_match('/xhtml/',$content_type)) { $doctype = ''.NB_EOL; } elseif (preg_match('/xml/',$content_type)) { - $doctype = 'charset).'"?>'.NB_EOL; //html5) { + $doctype = ''.NB_EOL; //'; } else { @@ -334,9 +341,9 @@ class Page extends nb { if ($this->title) $head .= $this->tag('title',$this->title) . NB_EOL; - if (preg_match('/tml$/',$this->content_type)) { + if (preg_match('/tml$/',self::$content_type)) { - if ($this->charset) $head .= ''.NB_EOL; + if (self::$charset) $head .= ''.NB_EOL; $head .= ''.NB_EOL; @@ -372,9 +379,9 @@ class Page extends nb { } public static function is($is) { - if ($is == 'xhtml') return preg_match('/xhtml$/',$this->content_type); - if ($is == 'html') return preg_match('/html$/',$this->content_type); - if ($is == 'xml') return preg_match('/xml$/',$this->content_type); + if ($is == 'xhtml') return preg_match('/xhtml$/',self::$content_type); + if ($is == 'html') return preg_match('/html$/',self::$content_type); + if ($is == 'xml') return preg_match('/xml$/',self::$content_type); die("Db->is(): unknow argument '$is'. Accepted values are xhtml, html, xml"); } @@ -477,5 +484,10 @@ class Page extends nb { #echo ''.NB_EOL; } + public static function content_type($set=null){ + if ($set) return (self::$content_type = $set); + return self::$content_type; + } + } return; # < Class ?> -- 2.47.3