From 9e8ae97148f9949a720ee02b74220af25a48bdb6 Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Mon, 8 Jan 2018 00:25:46 +0000 Subject: [PATCH] lib/php/db/types/sqlite.php --- lib/php/db.php | 17 +++++++++++------ lib/php/db/field.php | 19 ++++++++++++------- lib/php/db/table.php | 14 +++++++++----- lib/php/db/types/sqlite.php | 13 +++++++++++++ www/dbq/dbq.php | 14 ++++++-------- www/dbq/html/default.js | 4 +++- 6 files changed, 54 insertions(+), 27 deletions(-) diff --git a/lib/php/db.php b/lib/php/db.php index 5d706db4..7d7003bb 100644 --- a/lib/php/db.php +++ b/lib/php/db.php @@ -957,12 +957,13 @@ class Db extends nb { } - public function dump() { + public function dump($db_type='') { return $this->sql(true); } - public function sql($insert=null) { + public function sql($insert=null,$db_type='') { if ($insert === null) $insert = self::p('insert'); + if (empty($db_type)) $db_type = self::p('db_type'); # Params $this->pset('orderby',null); @@ -1003,16 +1004,19 @@ class Db extends nb { echo '' #."-- Database : ".$this->name."\n" ."-- Date : ".strftime('%F %T')."\n" - ."-- Pdo : ".$this->pdo_info()."\n" +# NB 07.01.18 ."-- Pdo : ".$this->pdo_info()."\n" ; # Change db type if needed $type_from = $type_to = ''; - if (self::p('db_type')) { - echo "-- Type : ".self::p('db_type')."\n"; + if ($db_type) { + echo "-- Type orig: ".$this->type."\n"; + echo "-- Type : ".$db_type."\n"; $type_from = $this->type; - $type_to = self::p('db_type'); + $type_to = $db_type; $this->type = $type_to; + } else { + echo "-- Type : ".$this->type."\n"; } # Specific function @@ -1036,6 +1040,7 @@ class Db extends nb { #bye($this->db()->type); if ($type_from == 'mysql') $views = []; # Mysql store sql create view in mysql format wich only works with mysql + echo "\n-- SQL\n"; foreach (array_merge($tables,$views) as $t) { # DROP / CREATE diff --git a/lib/php/db/field.php b/lib/php/db/field.php index 0ea5d10c..4dfa67a9 100644 --- a/lib/php/db/field.php +++ b/lib/php/db/field.php @@ -38,14 +38,19 @@ class field extends nb { public function __construct($attr=[]) { if (is_scalar($attr)) $attr = ['name'=>$attr]; foreach ($attr as $k => $v) { $this->$k = $v; } + } + + public static function default2str($default) { if ($this->default == "''") $this->default = ''; -# NB 12.12.17 if (strtoupper($this->default) == "NULL") $this->default = null; - #if (strtoupper($this->default) == "NOW()") $this->default = null; - if (strtoupper($this->default) == "NOW()") $this->default = $this->date_microtime(); - if (strtoupper($this->default) == "CURRENT_TIMESTAMP)") $this->default = $this->date_microtime(); - if (strtoupper($this->default) == "NOW()") $this->default = date("Y-m-d H:i:s.u"); - if (strtoupper($this->default) == "CURRENT_TIMESTAMP)") $this->default = date("Y-m-d H:i:s.u"); - if (strtoupper($this->default) == "CURRENT_DATE)") $this->default = date("Y-m-d"); +# NB 12.12.17 if (strtoupper($default) == "NULL") $default = null; + #if (strtoupper($default) == "NOW()") $default = null; +# NB 08.01.18 if (strtoupper($default) == "NOW()") $default = $date_microtime(); +# NB 08.01.18 if (strtoupper($default) == "CURRENT_TIMESTAMP") $default = $date_microtime(); + if (strtoupper($default) == "NOW()") $default = date("Y-m-d H:i:s.u"); + if (strtoupper($default) == "CURRENT_TIMESTAMP") $default = date("Y-m-d H:i:s.u"); + if (strtoupper($default) == "CURRENT_DATE") $default = date("Y-m-d"); + if (preg_match('/^(null|.*\(.*)?$/',strtolower($default))) $default = ''; + return $default; } public function autoincrement() { diff --git a/lib/php/db/table.php b/lib/php/db/table.php index 467d45f9..e42903f1 100644 --- a/lib/php/db/table.php +++ b/lib/php/db/table.php @@ -217,17 +217,17 @@ Class Table extends nb { * return the sql to create the table build internaly * */ - public function create($from_sql=true) { + public function create($from_engine=true) { // String replace function - $sql_replace_fct = $this->db()->conf_type('table.sql.create',false); + $sql_replace_fct = $this->db()->conf_type('table.sql.create'); $db_sql_replace_fct = empty($this->db()->sql_replace) ? '' : $this->db()->sql_replace; $sql_replace = function($sql) use ($sql_replace_fct,$db_sql_replace_fct) { if ($db_sql_replace_fct) $sql = $db_sql_replace_fct($sql); return $sql_replace_fct ? $sql_replace_fct($sql,$this) : $sql; }; - if ($from_sql) return $sql_replace($this->sql()); + if ($from_engine) return $sql_replace($this->sql()); if ($this->type() == 'view') { return $sql_replace('CREATE VIEW '.$this->sql_name().' AS '.preg_replace('/^CREATE\s+.*?\s+AS\s+.*?SELECT/i','SELECT',$this->sql())); @@ -595,8 +595,10 @@ Class Table extends nb { foreach ($this->fields() as $name => $field) { - if ($add and !preg_match('/^(null)?$/',strtolower($field->default))) { - $row[$name] = $field->default; +# NB 08.01.18 if ($add and !preg_match('/^(null|.*\(.*)?$/',strtolower($field->default))) { +# NB 08.01.18 $row[$name] = $field->default; + if ($add) { + $row[$name] = $field->default2str($field->default); } elseif(!isset($row[$name])) { $row[$name] = ''; @@ -2056,6 +2058,8 @@ Class Table extends nb { if (!empty($opt['buttons'])) $buttons = $opt['buttons']; $r = '
'.NB_EOL; + #$r = ''.NB_EOL; + # See: http://html5doctor.com/html5-forms-input-types/ #$r .= ''; #$r .= ''; diff --git a/lib/php/db/types/sqlite.php b/lib/php/db/types/sqlite.php index a47c31d5..f98d3c74 100644 --- a/lib/php/db/types/sqlite.php +++ b/lib/php/db/types/sqlite.php @@ -128,6 +128,7 @@ $DB_TYPES['sqlite'] = array ( $sql = str_replace('\\n',"\n",$sql); return $sql; }, + 'connect' => function($Db) { if (!empty($Db->attach)) { @@ -146,9 +147,21 @@ $DB_TYPES['sqlite'] = array ( } }, + 'disconnect' => function($Db) { foreach ($Db->attach as $name => $file) { $Db->conn->exec("DETACH DATABASE $name"); } }, + +'table.sql.create' => function($sql) { + $r = [ + '/::\w+(\b|\s)/' => '$1', # pgsql cast; + '/"([\w_]+)"\(/' => '$1(', # pgsql function quoted; + "/'([\w_]+\(.*?\))'/" => '$1', # pgsql function quoted; + "/now\(\)/i" => 'CURRENT_TIMESTAMP', # pgsql function quoted; + ]; + $sql= preg_replace(array_keys($r),array_values($r),$sql); + return $sql; +}, );?> diff --git a/www/dbq/dbq.php b/www/dbq/dbq.php index 05f0210f..22ddf698 100644 --- a/www/dbq/dbq.php +++ b/www/dbq/dbq.php @@ -427,7 +427,7 @@ class DbQ extends nb { $this->table->html_edit($values, # NB 14.12.17 $this->table->base . '/' . ($add ? 'insert' : 'update' . '/' . urlencode($this->params['args'])) . '/' - $this->table->base . '/' . ($add ? 'insert' : 'update') . '/' + $this->table->base . '/' . ($add ? 'insert' : 'update') # NB 07.01.18 . '/' ,$add); } @@ -1166,21 +1166,19 @@ EOF; } elseif ($action == 'databases') { $this->page($this->db,'databases'); - } elseif ($action == 'dump') { + } elseif ($action == 'csv') { header('Content-type: text/plain'); - $this->db->dump(); + $this->db->dump2csv(); exit; - #echo serialize($this->db->tables()); - $this->page(array_values($this->db()->conf)); - } elseif ($action == 'csv') { + } elseif ($action == 'dump') { header('Content-type: text/plain'); - $this->db->dump2csv(); + $this->db->dump($this->params['action']); exit; } elseif ($action == 'schema') { header('Content-type: text/plain'); - $this->db->sql(); + $this->db->sql(null,$this->params['action']); exit; } diff --git a/www/dbq/html/default.js b/www/dbq/html/default.js index bf1761ba..65c357a7 100644 --- a/www/dbq/html/default.js +++ b/www/dbq/html/default.js @@ -74,7 +74,9 @@ document.addEventListener("DOMContentLoaded", function() { form.querySelectorAll("form.menu select.tables").forEach(function(e) { e.removeAttribute('name'); e.onchange = function() { - this.form.setAttribute('action',window._dbq['db.base']+'/'+this.value+'.html'); + this.form.setAttribute('action',window._dbq['db.base']+'/'+this.value + + ( window._dbq['param.format'] ? '.'+window._dbq['param.format'] : '') + ); return this.form.onsubmit(); } }); -- 2.47.3