From 9e650c8f9f8bc2ff1dd05258149218cb5b31e172 Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Thu, 9 Jul 2015 01:26:34 +0100 Subject: [PATCH] db --- lib/php/cache.php | 2 +- lib/php/db.php | 720 +----------------------------------------- lib/php/db/field.php | 91 ++++++ lib/php/db/table.php | 727 +++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 823 insertions(+), 717 deletions(-) create mode 100644 lib/php/db/field.php create mode 100644 lib/php/db/table.php diff --git a/lib/php/cache.php b/lib/php/cache.php index 90f1bbaa..e304bf6a 100644 --- a/lib/php/cache.php +++ b/lib/php/cache.php @@ -1,6 +1,6 @@ conn = new PDO($db['conn']); + if (DB_ERR_PRINT) $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); #if (empty($this->name)) $this->name = preg_replace('/^(?:(?:sqlite:.*(.*?)(\.\w+)?)|(?:.*?dbname=([^;]+).*?))$/','\1',$db['conn']); if (empty($this->name) and preg_match('/(?:sqlite:|dbname=)([^;\.]+)/',$db['conn'],$m)) { $this->name = $m[1]; @@ -242,719 +245,4 @@ EOF; } -class table { - - public $name; - public $db; - public $sql; - public $fields = array(); - public $fields_keys = array(); - public $extras = array(); - public $params = array( - 'table', - 'limit', - 'debug', - ); - - function __construct($name,$opt=array()) { - - // Connection - if (@$opt['db']) { - $this->db = @$opt['db']; - } else { - $this->db = new db(); - } - - // Table could be a select - if (stripos($name,'SELECT ')===0) { - $this->db->conn->query("CREATE TEMPORARY TABLE _query_ AS $name"); - $name = '_query_'; - } elseif (preg_match('/\b(\.import|LOAD DATA|COPY|INSERT|REPLACE|DELETE|TRUNCATE|CREATE|DROP|ALERT)\b/',$name)) { - bye("Query not Allowed !"); - } - - $this->name = $name; - - if (@$opt['extras']) $this->add_extras($opt['extras']); - - return $this->fields(); - } - - /* - * Function db.fields - * - * return all or one fields from a table - * - * @name (string) name of the field to return. Default: null - * @return (array) return null where name does not exsts - */ - function fields($name=null) { - - if (!$this->fields) { - - if ($this->db->type == 'sqlite') { - $sql = "PRAGMA table_info('$this->name')"; - - } elseif ($this->db->type == 'pgsql') { -$sql = "SELECT -a.attname AS name, -pg_catalog.format_type(a.atttypid, a.atttypmod) AS type, -CASE a.attnotnull WHEN 't' then 1 ELSE 0 END AS notnull, -(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 default, -(SELECT 1 FROM pg_index i WHERE a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) AND i.indrelid = '$this->name'::regclass AND i.indisprimary) as pk -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='$this->name' AND pg_catalog.pg_table_is_visible(c.oid) ) AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum"; - - } elseif ($this->db->type == 'mysql') { - $sql = "SHOW COLUMNS FROM `$this->name`"; - - } else { - err('table.fields(): Unknow db type: '.$this->db->type); - return array(); - - } - - $rows = $this->db->conn->query($sql); - - $rows->setFetchMode(PDO::FETCH_ASSOC); - - foreach ($rows as $row) { - - $this->fields[$row['name']] = array( - 'extra' => null, - 'type' => null, - 'default' => null, - 'key' => null, - ); - - $this->fields[$row['name']]['type'] = $row['type']; - - if (isset($row['notnull'])) { - $this->fields[$row['name']]['null'] = $row['notnull'] == '0' ? 1 : 0; - - } else { - $this->fields[$row['name']]['null'] = preg_match('/^1|yes|t/i',$row['null']) ? 1 : 0; - - } - - foreach (array('dflt_value') as $f) { - if (!isset($row[$f])) continue; - $this->fields[$row['name']]['default'] = $row[$f]; - } - - foreach (array('pk','Key') as $f) { - if (!isset($row[$f])) continue; - $this->fields[$row['name']]['key'] = preg_match('/^1|yes|t/i',$row[$f]) ? 1 : 0; - } - - } - - } - - if ($name !== null ) { - if (!isset($this->fields[$name])) return null; - return $this->fields[$name]; - } - - return $this->fields; - } - - function url_edit($values=null,$sep='&') { - if ($values === null) $values = $this->db->p(); - $url_edit = array(); - - foreach ($this->fields_keys() as $name => $spec) { - $url_edit[] = $name . '=' .urlencode($values[$name]); - } - - return $url_edit ? 'edit/?table='.$this->db->p('table').$sep.join($sep,$url_edit) : ''; - - } - - function fields_keys() { - - if (!$this->fields_keys) { - $this->fields_keys = array(); - - foreach ($this->fields() as $name => $f) { - #debug($f); - if (@$f['key'] == 1) $this->fields_keys[$name] = $f; - } - - } - - return $this->fields_keys; - - } - - function html_edit($values = null) { - if ($values === null) $values = $this->db->p(); - if (!is_array($values)) $values = array($values); - - $sql = "SELECT *" . $this->select_extras(); - $sql .= " FROM $this->name".str_replace(' LIKE ','=',$this->where_criterias($values)); - $sql .= " LIMIT 1"; - $this->sql = $sql; - - $this->debug($sql,1); - $st = $this->db->conn->prepare($sql); - $st->execute(); - - echo '
'.PHP_EOL; - $count = 0; - if ($row = $st->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)) { - $count ++; - - foreach ($this->fields() as $name => $attr) { - $field = new field($name,$attr); - $field->html_edit(array_key_exists($name,$row) ? $row[$name] : $attr['default']); -// NB 03.07.15 continue; -// NB 03.07.15 -// NB 03.07.15 $value = array_key_exists($name,$row) ? $row[$name] : $attr['default']; -// NB 03.07.15 -// NB 03.07.15 echo '' -// NB 03.07.15 .'' -// NB 03.07.15 .PHP_EOL; - } - } - - echo '' - .'
' - .'' - .'' - .'' - .( empty($_SERVER['HTTP_REFERER']) ? '' : '') - .'
'.PHP_EOL - .'
'.PHP_EOL; - - $this->row = $row; - return $this; - } - - function debug($msg,$level=0) { return debug($msg,$level); } - - function url_params($k='',$v='') { - - $params = array(); - - foreach (array_merge( $this->params, array_keys($this->fields()) ) as $f) { - - if (@strcmp($this->db->p($f),'')==0) continue; - $params[$f] = $this->db->p($f); - - } - - if ($k) { - - if (strcmp($v,'')==0) { - unset($params[$k]); - } else { - $params[$k] = $v; - } - - } - - $flat = array(); - foreach ($params as $k=>$v) { $flat[] = $k.'='.urlencode($v); } - return $flat ? '?'. join('&',$flat) : ''; - - } - - function url_sort($name) { - - $html = ''; - - # Asc - $sel = ( $this->db->p('sort')=="$name asc") ? " sel" : ""; - $html .= '' - .'↓' - .''; - $html .= ' '; - - $html .= ucfirst($name); - - $html .= ' '; - - # Desc - $sel = ( $this->db->p('sort')=="$name desc") ? " sel" : ""; - $html .= '' - .'↑' - .''; - - return $html; - - } - - function nav($count,$tot,$limit) { - - if ($count<$tot) { - list($x,$y) = strpos($limit,',')!==false - ? preg_split('/\s*,\s*/',$limit) - : array(0,$limit) - ; - - $prev = $x - $y; - $next = $x + $y; - - $this->debug("x=$x limit=$y prev=$prev next=$next tot=$tot",1); - } else { - $x = 0; - $y = $tot; - $prev = -1; - $next = 999999; - } - - echo ''.PHP_EOL; - static $js = null; - if ($js === null) { - echo ''.PHP_EOL - ; - $js = '1'; - } - - } - - function form_criterias($opt=array()) { - - echo '
'.PHP_EOL; - echo '
Use: '.join(' | ',$this->db->help_criterias).'
'.PHP_EOL; - - foreach ($this->params as $k) { - $v = @$_REQUEST[$k]; - if ($k == 'limit') $v = ''; - echo ''.PHP_EOL; - } - - $criteria = array(); - foreach ( array_keys($this->fields()) as $k ) { - - $v = @$_REQUEST[$k]; - - $criteria[] = '' - . '' - . '' - ; - - } - - $criteria[] = html_select_array(array( - 'AND', - 'OR', - ),array( - 'html' => 'name="op"', - 'selected' => $this->db->p('op'), - )); - - $criteria[] = html_select_array(array( - array('','HTML'), - array('csv','CSV'), - array('xml','XML'), - array('yaml','YAML'), - array('json','JSON'), - ),array( - 'html' => 'name="format"', - 'selected' => $this->db->p('format'), - )); - $criteria[] = ''; - - echo join(''.PHP_EOL,$criteria); - - echo '
'.PHP_EOL; - - } - - function where_criterias($values,$logic='AND') { - $having = $where = array(); - - foreach ($this->fields() as $k => $spec) { - - $field = new field($k,$spec); - - // No empty values - $v = @$values[$k]; - if (strcmp($v,'')==0 or $v=='!' or $v=='~') continue; -// NB 03.07.15 $number = preg_match('/int|float|number|currency/',$spec['type']) ? 1 : 0; - $number = $field->is_num(); - - // Equal / Not Equal - $equal = '='; - $not = strpos($v,'!')===0 ? 1 : 0; - if ($not) $v = substr($v,1); - - // Regex - if (strpos($v,'~')===0) { - $v = substr($v,1); - $v = $this->db->conn->quote($v); - $equal = ' '.($not ? 'NOT ' : '').'REGEXP '; - - // Text - } elseif (preg_match('/text|char|blob/',$spec['type']) - or !preg_match('/^\d+(\.\d*)?$/',$v) # text criteria value - - ) { - - if (strtolower($v)=='null') $v = ''; - #$k = "COLAESCE($k,'')"; - - // * -> % - $v = str_replace('*','%',$v); - - $v = $this->db->conn->quote($v); - $equal = ' '.($not ? 'NOT ' : '').'LIKE '; - - // Others - } else { - - // Integer - if ($number) { - if (strtolower($v)=='null') $v = '0'; - - // Date, Time - } else { - $v = $this->db->conn->quote($v); - - } - $equal = $not ? '<>' : '='; - - } - - if (preg_match('/(LIKE|REGEXP) ..$/',"$equal$v")) { - $k = "COALESCE($k,".$this->db->conn->quote('').")"; - } - - if ($this->db->type == 'mysql' and $spec['extra']) { - $having[] = "$k$equal$v"; - - } elseif ($this->db->type == 'pgsql' and $spec['extra']) { - $where[] = $this->extras[$k]."$equal$v"; - - } else { - $where[] = "$k$equal$v"; - } - - } - - $sql = ''; - if ($where) $sql .= ' WHERE '.join(' '. $logic.' ',$where); - if ($having) $sql .= ' HAVING '.join(' '. $logic.' ',$having); - return $sql; - - } - - function add_extras($extras) { - $this->fields(); - - foreach ($extras as $k => $v) { - - $this->fields[$k] = array( - 'type' => 'text', - 'null' => 0, - 'extra' => $v, - ); - - $this->extras[$k] = $v; - - } - - } - - function select_extras() { - - if (!$this->extras) return ''; - - $select = array(); - - foreach ($this->extras as $k => $v) { - - $select[] = "$v AS ".($this->db->type == 'pgsql' - ? '"'.str_replace('"','\"',$k).'"' - : $this->db->conn->quote($k) - ); - /* - $select[] = "$v AS ".$this->db->conn->quote($k); - */ - - /* - $k = $this->db->conn->quote($k); - if ($this->db->type == 'pgsqpl') $k = str_replace( - $select[] = "$v AS $k" - */ - - } - - return ','.join(',',$select); - } - - /****************************************************************** - Html Output - ******************************************************************/ - function rows($opt=array()) { - - // - // Select - // - $sql = "SELECT *" . $this->select_extras(); - $sql .= " FROM $this->name".$this->where_criterias($this->db->p(),$this->db->p('op')); - $this->sql = $sql; - #$this->debug($sql); - $this->debug($sql,1); - - // - // Tot - // - $query = $this->db->conn->query("SELECT count(*) FROM ($sql) count",PDO::FETCH_COLUMN,0); - if (!$query) { - $err = $this->db->conn->errorInfo(); - $err[] = $sql; - err(join(' | ',$err)); - return $err[0]; - } - $tot = $query->fetch(); - #if (!$tot) return; - - if ($this->db->p('sort')) $sql .= ' ORDER BY '.$this->db->p('sort'); - - if ($this->db->p('limit')) { - $limit = $this->db->p('limit'); - $sql .= ' LIMIT '.$limit; - } else { - $limit = ''; - } - - // - // Get results - // - $st = $this->db->conn->prepare($sql); - $st->execute(); - - $format = (!empty($opt['format']) ? $opt['format'] : 'table'); - - if (!@$opt['is_html']) $opt['is_html'] = preg_match('/^(table)$/',$format) ? true : false; - if ($opt['is_html']) echo ''.PHP_EOL; - - $escape = preg_match('/^(table|row|xml)$/',$format) ? true : false; - if (preg_match('/^(1)?$/',$this->db->p('header'))) echo $this->{"rows_begin_$format"}($opt); - - $count = 0; - while ($row = $st->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)) { - $count++; - $count_fields = 0; - - foreach ($this->fields() as $f => $spec) { - - if (!$spec['extra']) { - if ($escape) $row[$f] = htmlspecialchars($row[$f]); - } - - if ($this->db->type == 'sqlite' and preg_match('/^float\((?:\d+,)?(\d+)\)/',$spec['type'],$m)) { - $row[$f] = sprintf('%.'.$m[1].'f',$row[$f]); - } - - if ($count_fields === 0) { - #$row[$f] = '' . $row[$f] . ''; - } - - $count_fields++; - /* only if in latin1 - if ($this->db->p('format') == 'csv') { - $row[$f] = utf8_encode($row[$f]); - } elseif ($spec['extra']) { - $row[$f] = htmlentities($row[$f]); - - } - */ - - } - - #debug($this->url_edit($row)); - if ($format == 'table') array_unshift($row,''.DB_HTML_EDIT.''); - echo $this->{"rows_rec_$format"}($row); - - } - - echo $this->{"rows_end_$format"}($opt); - if ($opt['is_html']) echo $this->nav($count,$tot,$limit); - - $st->closeCursor(); - - return $count; - } - - /*----------------------------------------------------------------- - Json - -----------------------------------------------------------------*/ - function rows_begin_json() { - $this->_row_json = null; - echo '['.PHP_EOL; - } - - function rows_rec_json($row) { - if ($this->_row_json === null) { - $this->_row_json = true; - } else { - echo ','; - } - echo json_encode($row).PHP_EOL; - } - - function rows_end_json() { - unset($this->_row_json); - echo ']'.PHP_EOL; - } - - /*----------------------------------------------------------------- - Yaml - -----------------------------------------------------------------*/ - function rows_begin_yaml() { - echo "---\n"; - } - - function rows_rec_yaml($row) { - $yaml = yaml_emit($row); - $yaml = preg_replace('/^---\n/','',$yaml); - $yaml = preg_replace('/\n\.\.\.$/','',$yaml); - $yaml = preg_replace('/^/m',' ',$yaml); - echo '- '.trim($yaml)."\n"; - } - - function rows_end_yaml() { - echo ''; - } - - /*----------------------------------------------------------------- - Xml - -----------------------------------------------------------------*/ - function rows_begin_xml() { - echo '' - .''.PHP_EOL - .''.PHP_EOL - ; - } - - function rows_rec_xml($row) { - echo "\t<".$this->name.">".PHP_EOL; - foreach (array_keys($this->fields()) as $f) { - if ($row[$f] !== '') echo '' - . "\t\t<".$f.'>' - .'' - . '' - .PHP_EOL; - } - echo "\tname.">".PHP_EOL; - } - - function rows_end_xml() { - #echo 'name.'>'.PHP_EOL; - echo ''.PHP_EOL; - } - - /*----------------------------------------------------------------- - Csv - -----------------------------------------------------------------*/ - function rows_begin_csv() { - echo join("\t",array_keys($this->fields()))."\n"; - } - - function rows_rec_csv($row) { - echo join("\t",array_values($row))."\n"; - } - - function rows_end_csv() { - } - - /*----------------------------------------------------------------- - Html Table - -----------------------------------------------------------------*/ -// NB 14.04.14 function rows_begin_table($opt=array()) { - function rows_begin_table() { - - echo ''.PHP_EOL; - - echo ''; - echo ''; - foreach (array_keys($this->fields()) as $f) { - echo ''; - } - - echo ''.PHP_EOL; - } - - function rows_rec_table($row) { - echo ''; - - foreach ($row as $k => $v) { - echo ''; - } - - echo ''.PHP_EOL; - } - - function rows_end_table() { - echo '
'.$this->url_sort($f).'
'.$v.'
'.PHP_EOL; - } - - /*----------------------------------------------------------------- - Html Div - -----------------------------------------------------------------*/ - function rows_begin_div() { - echo '
'.PHP_EOL; - } - - function rows_rec_div($row) { - - echo ''.PHP_EOL; - } - - function rows_end_div() { - return "
".PHP_EOL; - } - -} - -class field { - - public $name; - public $extras = array(); - public $type = 'text'; - public $default = null; - public $key = false; - - function __construct($name,$attr=array()) { - foreach ($attr as $k => $v) { $this->$k = $v; } - } - - function is_num() { - return preg_match('/int|float|number|currency/',$this->type) ? 1 : 0; - } - - function html_edit($value) { - - echo '' - .'' - .PHP_EOL; - - } - -} - -return; ?> diff --git a/lib/php/db/field.php b/lib/php/db/field.php new file mode 100644 index 00000000..297c10df --- /dev/null +++ b/lib/php/db/field.php @@ -0,0 +1,91 @@ +conn : null; +class field { + + public $name; + public $type = 'text'; + public $default = null; + public $key = false; + + function __construct($name,$attr=array()) { + foreach ($attr as $k => $v) { $this->$k = $v; } + } + + function is_num() { + return preg_match('/int|float|number|currency/',$this->type) ? 1 : 0; + } + + function html_edit($value) { + + echo '' + .'' + .PHP_EOL; + + } + + function quote($value,$force_quote=false) { + + if ($DB_FIELD_CONN === null) return "'".str_replace("'","\\'",$value)."'"; + return $DB_FIELD_CONN->quote($value); + +// TODEL - NB 08.07.15 + if ($force_quote or !$field->is_num()) { + + if ($DB_FIELD_CONN === null) return "'".preg_replace("/'/","\\'",$value)."'"; + return $DB_FIELD_CONN->quote($value); + } + + return $value; + } + + function where($value) { + + // No empty value + $v = isset($value) ? $value : null; + if (strcmp($v,'')==0 + or $v=='!' + or $v=='!~' + or $v=='~' + ) return null; + +// NB 03.07.15 $number = preg_match('/int|float|number|currency/',$this->type) ? 1 : 0; + $number = $this->is_num(); + + // Equal / Not Equal + $equal = '='; + + $not = strpos($v,'!')===0 ? 1 : 0; + if ($not) $v = substr($v,1); + + // Regex + if (strpos($v,'~')===0) { + return $this->name . ($not ? 'NOT ' : '').'REGEXP ' . $DB_FIELD_CONN->quote( substr($v,1) ); + } + + // Text + if (!$this->num() or !preg_match('/^\d+(\.\d*)?$/',$v)) { # text criteria value + + if (strtolower($v)=='null') $v = ''; + return $this->name.' '.($not ? 'NOT ' : '').'LIKE '.$DB_FIELD_CONN->quote(str_replace('*','%',$v)); + + // Others + } else { + + // Integer + if ($number) { + if (strtolower($v)=='null') $v = '0'; + + // Date, Time + } else { + $v = $DB_FIELD_CONN->quote($v); + + } + $equal = $not ? '<>' : '='; + + } + + return "$k$equal$v"; + } + +} +?> diff --git a/lib/php/db/table.php b/lib/php/db/table.php new file mode 100644 index 00000000..a7527fa8 --- /dev/null +++ b/lib/php/db/table.php @@ -0,0 +1,727 @@ +db = $opt['db']; + } else { + $this->db = new db(); + } + + // Table could be a select + if (stripos($name,'SELECT ')===0) { + $this->db->conn->query("CREATE TEMPORARY TABLE _query_ AS $name"); + $name = '_query_'; + } elseif (preg_match('/\b(\.import|LOAD DATA|COPY|INSERT|REPLACE|DELETE|TRUNCATE|CREATE|DROP|ALERT)\b/',$name)) { + bye("Query not Allowed !"); + } + + $this->name = $name; + + if (@$opt['extras']) $this->add_extras($opt['extras']); + + return $this->fields(); + } + + /* + * Function db.fields + * + * return all or one fields from a table + * + * @name (string) name of the field to return. Default: null + * @return (array) return null where name does not exsts + */ + function fields($name=null) { + + if (!$this->fields) { + + if ($this->db->type == 'sqlite') { + $sql = "PRAGMA table_info('$this->name')"; + + } elseif ($this->db->type == 'pgsql') { +$sql = "SELECT +a.attname AS name, +pg_catalog.format_type(a.atttypid, a.atttypmod) AS type, +CASE a.attnotnull WHEN 't' then 1 ELSE 0 END AS notnull, +(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 default, +(SELECT 1 FROM pg_index i WHERE a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) AND i.indrelid = '$this->name'::regclass AND i.indisprimary) as pk +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='$this->name' AND pg_catalog.pg_table_is_visible(c.oid) ) AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum"; + + } elseif ($this->db->type == 'mysql') { + $sql = "SHOW COLUMNS FROM `$this->name`"; + + } else { + err('table.fields(): Unknow db type: '.$this->db->type); + return array(); + + } + + $rows = $this->db->conn->query($sql); + + $rows->setFetchMode(PDO::FETCH_ASSOC); + + foreach ($rows as $row) { + + $this->fields[$row['name']] = array( + 'extra' => null, + 'type' => null, + 'default' => null, + 'key' => null, + ); + + $this->fields[$row['name']]['type'] = $row['type']; + + if (isset($row['notnull'])) { + $this->fields[$row['name']]['null'] = $row['notnull'] == '0' ? 1 : 0; + + } else { + $this->fields[$row['name']]['null'] = preg_match('/^1|yes|t/i',$row['null']) ? 1 : 0; + + } + + foreach (array('dflt_value') as $f) { + if (!isset($row[$f])) continue; + $this->fields[$row['name']]['default'] = $row[$f]; + } + + foreach (array('pk','Key') as $f) { + if (!isset($row[$f])) continue; + $this->fields[$row['name']]['key'] = preg_match('/^1|yes|t/i',$row[$f]) ? 1 : 0; + } + + } + + } + + if ($name !== null ) { + if (!isset($this->fields[$name])) return null; + return $this->fields[$name]; + } + + return $this->fields; + } + + function url_edit($values=null,$sep='&') { + if ($values === null) $values = $this->db->p(); + $url_edit = array(); + + foreach ($this->fields_keys() as $name => $spec) { + $url_edit[] = $name . '=' .urlencode($values[$name]); + } + + return $url_edit ? 'edit/?table='.$this->db->p('table').$sep.join($sep,$url_edit) : ''; + + } + + function fields_keys(&$others=array()) { + + #if (!$this->fields_keys) { + $this->fields_keys = array(); + + foreach ($this->fields() as $name => $f) { + #debug($f); + if ((int)$f['key'] == 1) { + $this->fields_keys[$name] = $f; + } else { + $others[$name] = $f; + } + } + + #} + + return $this->fields_keys; + + } + + function html_edit($values = null) { + if ($values === null) $values = $this->db->p(); + if (!is_array($values)) $values = array($values); + + $sql = "SELECT *" . $this->select_extras(); + $sql .= " FROM $this->name".str_replace(' LIKE ','=',$this->where_criterias($values)); + $sql .= " LIMIT 1"; + $this->sql = $sql; + + $this->debug($sql,1); + $st = $this->db->conn->prepare($sql); + $st->execute(); + + echo '
'.PHP_EOL; + $count = 0; + if ($row = $st->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)) { + $count ++; + + foreach ($this->fields() as $name => $attr) { + $field = new field($name,$attr); + $field->html_edit(array_key_exists($name,$row) ? $row[$name] : $attr['default']); +// NB 03.07.15 continue; +// NB 03.07.15 +// NB 03.07.15 $value = array_key_exists($name,$row) ? $row[$name] : $attr['default']; +// NB 03.07.15 +// NB 03.07.15 echo '' +// NB 03.07.15 .'' +// NB 03.07.15 .PHP_EOL; + } + } + + echo '' + .'
' + .'' + .'' + .'' + .( empty($_SERVER['HTTP_REFERER']) ? '' : '') + .'
'.PHP_EOL + .'
'.PHP_EOL; + + $this->row = $row; + return $this; + } + + function debug($msg,$level=0) { return debug($msg,$level); } + + function url_params($k='',$v='') { + + $params = array(); + + foreach (array_merge( $this->params, array_keys($this->fields()) ) as $f) { + + if (@strcmp($this->db->p($f),'')==0) continue; + $params[$f] = $this->db->p($f); + + } + + if ($k) { + + if (strcmp($v,'')==0) { + unset($params[$k]); + } else { + $params[$k] = $v; + } + + } + + $flat = array(); + foreach ($params as $k=>$v) { $flat[] = $k.'='.urlencode($v); } + return $flat ? '?'. join('&',$flat) : ''; + + } + + function url_sort($name) { + + $html = ''; + + # Asc + $sel = ( $this->db->p('sort')=="$name asc") ? " sel" : ""; + $html .= '' + .'↓' + .''; + $html .= ' '; + + $html .= ucfirst($name); + + $html .= ' '; + + # Desc + $sel = ( $this->db->p('sort')=="$name desc") ? " sel" : ""; + $html .= '' + .'↑' + .''; + + return $html; + + } + + function nav($count,$tot,$limit) { + + if ($count<$tot) { + list($x,$y) = strpos($limit,',')!==false + ? preg_split('/\s*,\s*/',$limit) + : array(0,$limit) + ; + + $prev = $x - $y; + $next = $x + $y; + + $this->debug("x=$x limit=$y prev=$prev next=$next tot=$tot",1); + } else { + $x = 0; + $y = $tot; + $prev = -1; + $next = 999999; + } + + echo ''.PHP_EOL; + static $js = null; + if ($js === null) { + echo ''.PHP_EOL + ; + $js = '1'; + } + + } + + function form_criterias($opt=array()) { + + echo '
'.PHP_EOL; + echo '
Use: '.join(' | ',$this->db->help_criterias).'
'.PHP_EOL; + + foreach ($this->params as $k) { + $v = @$_REQUEST[$k]; + if ($k == 'limit') $v = ''; + echo ''.PHP_EOL; + } + + $criteria = array(); + foreach ( array_keys($this->fields()) as $k ) { + + $v = @$_REQUEST[$k]; + + $criteria[] = '' + . '' + . '' + ; + + } + + $criteria[] = html_select_array(array( + 'AND', + 'OR', + ),array( + 'html' => 'name="op"', + 'selected' => $this->db->p('op'), + )); + + $criteria[] = html_select_array(array( + array('','HTML'), + array('csv','CSV'), + array('xml','XML'), + array('yaml','YAML'), + array('json','JSON'), + ),array( + 'html' => 'name="format"', + 'selected' => $this->db->p('format'), + )); + $criteria[] = ''; + + echo join(''.PHP_EOL,$criteria); + + echo '
'.PHP_EOL; + + } + + function where_criterias($values,$logic='AND') { + $having = $where = array(); + + foreach ($this->fields() as $k => $spec) { + + $field = new field($k,$spec); + + // No empty values + $v = isset($values[$k]) ? $values[$k] : null; + if (strcmp($v,'')==0 or $v=='!' or $v=='~') continue; +// NB 03.07.15 $number = preg_match('/int|float|number|currency/',$spec['type']) ? 1 : 0; + $number = $field->is_num(); + + // Equal / Not Equal + $equal = '='; + $not = strpos($v,'!')===0 ? 1 : 0; + if ($not) $v = substr($v,1); + + // Regex + if (strpos($v,'~')===0) { + $v = substr($v,1); + $v = $this->db->conn->quote($v); + $equal = ' '.($not ? 'NOT ' : '').'REGEXP '; + + // Text + } elseif (preg_match('/text|char|blob/',$spec['type']) + or !preg_match('/^\d+(\.\d*)?$/',$v) # text criteria value + ) { + + if (strtolower($v)=='null') $v = ''; + #$k = "COLAESCE($k,'')"; + + // * -> % + $v = str_replace('*','%',$v); + + $v = $this->db->conn->quote($v); + $equal = ' '.($not ? 'NOT ' : '').'LIKE '; + + // Others + } else { + + // Integer + if ($number) { + if (strtolower($v)=='null') $v = '0'; + + // Date, Time + } else { + $v = $this->db->conn->quote($v); + + } + $equal = $not ? '<>' : '='; + + } + + if (preg_match('/(LIKE|REGEXP) ..$/',"$equal$v")) { + $k = "COALESCE($k,".$this->db->conn->quote('').")"; + } + + if ($this->db->type == 'mysql' and $spec['extra']) { + $having[] = "$k$equal$v"; + + } elseif ($this->db->type == 'pgsql' and $spec['extra']) { + $where[] = $this->extras[$k]."$equal$v"; + + } else { + $where[] = "$k$equal$v"; + } + + } + + $sql = ''; + if ($where) $sql .= ' WHERE '.join(' '. $logic.' ',$where); + if ($having) $sql .= ' HAVING '.join(' '. $logic.' ',$having); + return $sql; + + } + + function add_extras($extras) { + $this->fields(); + + foreach ($extras as $k => $v) { + + $this->fields[$k] = array( + 'type' => 'text', + 'null' => 0, + 'extra' => $v, + ); + + $this->extras[$k] = $v; + + } + + } + + function select_extras() { + + if (!$this->extras) return ''; + + $select = array(); + + foreach ($this->extras as $k => $v) { + + $select[] = "$v AS ".($this->db->type == 'pgsql' + ? '"'.str_replace('"','\"',$k).'"' + : $this->db->conn->quote($k) + ); + /* + $select[] = "$v AS ".$this->db->conn->quote($k); + */ + + /* + $k = $this->db->conn->quote($k); + if ($this->db->type == 'pgsqpl') $k = str_replace( + $select[] = "$v AS $k" + */ + + } + + return ','.join(',',$select); + } + + /****************************************************************** + Html Output + ******************************************************************/ + function rows($opt=array()) { + + // + // Select + // + $sql = "SELECT *" . $this->select_extras(); + $sql .= " FROM $this->name".$this->where_criterias($this->db->p(),$this->db->p('op')); + $this->sql = $sql; + #$this->debug($sql); + $this->debug($sql,1); + + // + // Tot + // + $query = $this->db->conn->query("SELECT count(*) FROM ($sql) count",PDO::FETCH_COLUMN,0); + if (!$query) { + $err = $this->db->conn->errorInfo(); + $err[] = $sql; + err(join(' | ',$err)); + return $err[0]; + } + $tot = $query->fetch(); + #if (!$tot) return; + + if ($this->db->p('sort')) $sql .= ' ORDER BY '.$this->db->p('sort'); + + if ($this->db->p('limit')) { + $limit = $this->db->p('limit'); + $sql .= ' LIMIT '.$limit; + } else { + $limit = ''; + } + + // + // Get results + // + $st = $this->db->conn->prepare($sql); + $st->execute(); + + $format = (!empty($opt['format']) ? $opt['format'] : 'table'); + + if (!@$opt['is_html']) $opt['is_html'] = preg_match('/^(table)$/',$format) ? true : false; + if ($opt['is_html']) echo ''.PHP_EOL; + + $escape = preg_match('/^(table|row|xml)$/',$format) ? true : false; + if (preg_match('/^(1)?$/',$this->db->p('header'))) echo $this->{"rows_begin_$format"}($opt); + + $count = 0; + while ($row = $st->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)) { + $count++; + $count_fields = 0; + + foreach ($this->fields() as $f => $spec) { + + if (!$spec['extra']) { + if ($escape) $row[$f] = htmlspecialchars($row[$f]); + } + + if ($this->db->type == 'sqlite' and preg_match('/^float\((?:\d+,)?(\d+)\)/',$spec['type'],$m)) { + $row[$f] = sprintf('%.'.$m[1].'f',$row[$f]); + } + + if ($count_fields === 0) { + #$row[$f] = '' . $row[$f] . ''; + } + + $count_fields++; + /* only if in latin1 + if ($this->db->p('format') == 'csv') { + $row[$f] = utf8_encode($row[$f]); + } elseif ($spec['extra']) { + $row[$f] = htmlentities($row[$f]); + + } + */ + + } + + #debug($this->url_edit($row)); + if ($format == 'table') array_unshift($row,''.DB_HTML_EDIT.''); + echo $this->{"rows_rec_$format"}($row); + + } + + echo $this->{"rows_end_$format"}($opt); + if ($opt['is_html']) echo $this->nav($count,$tot,$limit); + + $st->closeCursor(); + + return $count; + } + + /*----------------------------------------------------------------- + Json + -----------------------------------------------------------------*/ + function rows_begin_json() { + $this->_row_json = null; + echo '['.PHP_EOL; + } + + function rows_rec_json($row) { + if ($this->_row_json === null) { + $this->_row_json = true; + } else { + echo ','; + } + echo json_encode($row).PHP_EOL; + } + + function rows_end_json() { + unset($this->_row_json); + echo ']'.PHP_EOL; + } + + /*----------------------------------------------------------------- + Yaml + -----------------------------------------------------------------*/ + function rows_begin_yaml() { + echo "---\n"; + } + + function rows_rec_yaml($row) { + $yaml = yaml_emit($row); + $yaml = preg_replace('/^---\n/','',$yaml); + $yaml = preg_replace('/\n\.\.\.$/','',$yaml); + $yaml = preg_replace('/^/m',' ',$yaml); + echo '- '.trim($yaml)."\n"; + } + + function rows_end_yaml() { + echo ''; + } + + /*----------------------------------------------------------------- + Xml + -----------------------------------------------------------------*/ + function rows_begin_xml() { + echo '' + .''.PHP_EOL + .''.PHP_EOL + ; + } + + function rows_rec_xml($row) { + echo "\t<".$this->name.">".PHP_EOL; + foreach (array_keys($this->fields()) as $f) { + if ($row[$f] !== '') echo '' + . "\t\t<".$f.'>' + .'' + . '' + .PHP_EOL; + } + echo "\tname.">".PHP_EOL; + } + + function rows_end_xml() { + #echo 'name.'>'.PHP_EOL; + echo ''.PHP_EOL; + } + + /*----------------------------------------------------------------- + Csv + -----------------------------------------------------------------*/ + function rows_begin_csv() { + echo join("\t",array_keys($this->fields()))."\n"; + } + + function rows_rec_csv($row) { + echo join("\t",array_values($row))."\n"; + } + + function rows_end_csv() { + } + + /*----------------------------------------------------------------- + Html Table + -----------------------------------------------------------------*/ +// NB 14.04.14 function rows_begin_table($opt=array()) { + function rows_begin_table() { + + echo ''.PHP_EOL; + + echo ''; + echo ''; + foreach (array_keys($this->fields()) as $f) { + echo ''; + } + + echo ''.PHP_EOL; + } + + function rows_rec_table($row) { + echo ''; + + foreach ($row as $k => $v) { + echo ''; + } + + echo ''.PHP_EOL; + } + + function rows_end_table() { + echo '
'.$this->url_sort($f).'
'.$v.'
'.PHP_EOL; + } + + /*----------------------------------------------------------------- + Html Div + -----------------------------------------------------------------*/ + function rows_begin_div() { + echo '
'.PHP_EOL; + } + + function rows_rec_div($row) { + + echo ''.PHP_EOL; + } + + function rows_end_div() { + return "
".PHP_EOL; + } + + function sql_name() { + if ($this->type == 'mysql') return '`'.$this->name.'`'; + return $this->name; + } + + function update($hvalues) { + + $keys = $this->fields_keys($values); + $values = array_keys($values); + $keys = $keys ? array_keys($keys) : $values; + #bye($keys); + + $sql = + ($this->type == 'mysql' ? 'REPLACE' : 'REPLACE') + .' INTO ' . $this->sql_name() . ' (' . join(',',$values).')' + .' VALUES (' . join(',',ar_map('":$a"',$values)).')' + #.' WHERE ' . join(' AND ',ar_map('"$a=:$a"',$keys)) + ; + debug($sql); + $query = $this->db->conn->prepare($sql); + #echo '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'; + + #bye($hvalues); + foreach (array_keys($this->fields()) as $k) { + echo $hvalues[$k].PHP_EOL; + $query->bindParam(":$k", $hvalues[$k]); + } + + #return $sql; + return $query->execute(); + + } + +} +?> -- 2.47.3