From 999fa80d0a135448f1a1c4d4ea04ff562f40ef40 Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Tue, 20 Sep 2016 00:05:13 +0100 Subject: [PATCH] Bed --- etc/dbs.php | 14 ++-- etc/dbs/puppetdb.php | 3 +- etc/dbs/ui.php | 30 +++---- lib/php/db.php | 151 +++++++++++++++++++----------------- lib/php/db/table.php | 36 +++++++-- lib/php/db/types/mysql.php | 26 ++----- lib/php/db/types/pgsql.php | 16 ++-- lib/php/db/types/sqlite.php | 34 ++------ lib/php/out.php | 4 +- 9 files changed, 147 insertions(+), 167 deletions(-) diff --git a/etc/dbs.php b/etc/dbs.php index d0c2b2c4..8af2fee8 100644 --- a/etc/dbs.php +++ b/etc/dbs.php @@ -45,7 +45,7 @@ if ($DIR_SQLITE) { foreach (nb::ls_dir($DIR_SQLITE,'\.db$') as $file) { if (isset($host_already_exists["$DIR_SQLITE/$file"])) { - $CONF[$host_already_exists["$DIR_SQLITE/$file"]]['exec'][] = 'PRAGMA journal_mode=OFF'; + $CONF[$host_already_exists["$DIR_SQLITE/$file"]]['sql_pre'][] = 'PRAGMA journal_mode=OFF'; continue; } @@ -75,12 +75,10 @@ $CONF['all'] = [ PDO::ATTR_PERSISTENT => true, ], 'tables' => [], - 'types' => [ - 'exec' => [ - 'PRAGMA busy_timeout = 1000', - #'PRAGMA synchronous=NORMAL', - 'PRAGMA journal_mode=MEMORY', - ], + 'sql_pre' => [ + 'PRAGMA busy_timeout = 1000', + #'PRAGMA synchronous=NORMAL', + 'PRAGMA journal_mode=MEMORY', ], ]; @@ -94,7 +92,7 @@ foreach ($CONF as $id => $db) { or !is_readable($db['host']) ) continue; $fname = basename($db['host'],'.db'); - $CONF['all']['types']['exec'][] = "ATTACH DATABASE '".$db['host']."' as ".$fname.""; + $CONF['all']['types']['sql_pre'][] = "ATTACH DATABASE '".$db['host']."' as ".$fname.""; conf_merge($CONF[$fname],$CONF['all']); } diff --git a/etc/dbs/puppetdb.php b/etc/dbs/puppetdb.php index bd743d0b..71949728 100644 --- a/etc/dbs/puppetdb.php +++ b/etc/dbs/puppetdb.php @@ -7,7 +7,8 @@ $CONF['puppetdb'] = array ( 'default_table' => 'view_hosts', 'row_parse' => function(&$r) { foreach ($r as $k=>$v) { - $r[$k] = preg_replace('/(:\d\d)\.\d+/','\1',$v); + # Clean date time + $r[$k] = preg_replace('/^(\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d)\.\d+$/','\1',$v); } }, # NB 11.04.16 'tables' => diff --git a/etc/dbs/ui.php b/etc/dbs/ui.php index 69bbef08..70de31d5 100644 --- a/etc/dbs/ui.php +++ b/etc/dbs/ui.php @@ -5,16 +5,6 @@ $CONF['_ui'] = array( 'host' => (file_exists('/opt/www/sem_ui/var/db/semantico.db') ? '/opt/www/sem_ui/var/db/semantico.db' : "$DIR_SQLITE/semantico.db"), 'default_table' => 'node', - 'sql_replace' => function($sql) { - if (!Db::p('db.type')) return $sql; - $r = []; - - $r["CAST(started as char(30)),duration||' seconds'"] = "from_unixtime(UNIX_TIMESTAMP(started)+duration)"; - - $sql = str_replace(array_keys($r),$r,$sql); - return $sql; - }, - 'tables' => array( 'aws_az' => array( @@ -163,28 +153,28 @@ $CONF['_ui'] = array( ), ); -$CONF['ui'] = array( - '_import' => array('_ui'), +$CONF['ui'] = [ + '_import' => ['_ui'], 'order' => (preg_match('/^(dev|isp|ist)/',php_uname('n')) ? 1 : null), #'order' => 1, -); -$CONF['ui-pgsql'] = array( +]; +$CONF['ui-pgsql'] = [ 'host' => 'ui.semantico.net', 'type' => 'pgsql', 'name' => 'ui', 'pdo' => '', - '_import' => array('_ui','_semadm'), -); -$CONF['ui-mysql'] = array( + '_import' => ['_ui','_semadm'], +]; +$CONF['ui-mysql'] = [ 'host' => 'ui.semantico.net', 'type' => 'mysql', 'name' => 'ui', 'pdo' => '', - 'sql_exec' => [ + 'sql_pre' => [ 'SET SESSION sql_mode=PIPES_AS_CONCAT', ], - '_import' => array('_ui','_semadm'), -); + '_import' => ['_ui','_semadm'], +]; #bye(realpath(dirname(__FILE__).'/../../lib/php/db.php')); #require_once(realpath(dirname(__FILE__).'/../../lib/php/db/types/sqlite.php')); #bye($DB_TYPES['sqlite']); diff --git a/lib/php/db.php b/lib/php/db.php index 6a842204..8758af67 100644 --- a/lib/php/db.php +++ b/lib/php/db.php @@ -25,6 +25,7 @@ class Db extends nb { public $port = null; public $user = null; public $password = null; + public $charset = 'utf8'; # Database infos public $id; @@ -35,7 +36,7 @@ class Db extends nb { public $tables = []; public $types = []; public $conf = []; - public $row_parse; # Function to call in rows() + public $row_parsee; # Function to call in table.rows() # Params protected static $paliases = [ @@ -86,6 +87,11 @@ class Db extends nb { unset($opt['options']); } + # Charset + if (!empty($opt['charset'])) $opt['charset'] + = preg_replace('/^ *utf-8 *$/','utf8',$opt['charset']) + ; + # Tables if (isset($opt['tables'])) { #debug($opt['tables']); @@ -104,7 +110,7 @@ class Db extends nb { foreach ($opt as $k=>$v) $this->$k = $v; #parent::__construct($opt); #if(!is_scalar($opt['type'])) debug($opt['type']); - if (empty($this->out)) $this->out = new Out(['charset'=>self::$encoding]); + if (empty($this->out)) $this->out = new Out(['charset'=>$this->charset]); # id if (!empty($this->conf)) { @@ -163,8 +169,10 @@ class Db extends nb { 'user' => 'user', 'password' => 'password', 'name' => 'dbname', + 'charset' => 'charset', ) as $k => $v) { #self::debug($k); + if ($v=='charset' and $this->type=='pgsql') continue; if (empty($this->$k)) continue; $this->pdo = preg_replace("/\b$v=[^;]*(;|$)/","",$this->pdo); $this->pdo .= (preg_match('/[:;]$/',$this->pdo) ? '' : ';') . "$v=".$this->$k; @@ -175,6 +183,10 @@ class Db extends nb { # Title if (empty($this->title)) $this->title = prettyText($this->name); + # Row parser + if (empty($this->row_parse) and $this->type('row_parse')) { + $this->row_parse = $this->type('row_parse'); + } } function disconnect() { @@ -210,12 +222,6 @@ class Db extends nb { if (empty($this->conn)) return false; - # Type queries - foreach ($this->sql_exec() as $s) { - $this->conn->exec($s); - } - #$this->conn->exec('BEGIN TRANSACTION'); - # Create functions if ($this->type('sqliteCreateFunction')) { foreach ($this->type('sqliteCreateFunction') as $name => $fct) { @@ -227,8 +233,10 @@ class Db extends nb { } } + foreach ($this->sql_pre() as $s) { $this->conn->exec($s); } + # Bye - return empty($this->conn) ? false : true; + return true; } function exec($sql) { @@ -345,25 +353,26 @@ class Db extends nb { return $return; } - private function unvar($value) { - return $value; - if (!is_scalar($value)) return $value; + public function unvar($value) { + #return $value; + if (empty($value)) return $value; + if (!is_scalar($value)) { + foreach ($value as $k=>$v) { + if (is_scalar($v)) $value[$k] = $this->unvar($v); + } + return $value; + } $replace = [ - '' => $this->db()->name, - '' => $this->name, - '' => $this->type, - '' => $this->name.'___TODEL___', - '' => $this->name, - '' => $this->type, - '' => self::$encoding, + '' => $this->name, + '' => $this->charset, ]; - debug( str_replace(array_keys($replace),array_values($replace),$value) ); + #debug( str_replace(array_keys($replace),array_values($replace),$value) ); return str_replace(array_keys($replace),array_values($replace),$value); } - public function method($key=null,$type=null) { - $method = $this->type($key,false,$type); - if (is_callable($method)) return $metod = $method($this); + public function method($key=null,$args=null) { + $method = $this->type($key); + if (is_callable($method)) return $this->unvar($method($this)); return $this->unvar($method); } @@ -396,6 +405,7 @@ class Db extends nb { return; } + return $this->unvar($db_type[$key]); $value = $db_type[$key]; /* */ @@ -484,7 +494,7 @@ class Db extends nb { } elseif ($action == 'db.tables' or $action == 'tables') { # Filters - $type = $this->p('type','table'); + $type = $this->p('type',''); $name = $this->p('name',''); #if ($count = $this->p('count','')) $this->pset('count',1); $engine = $this->p('engine',''); @@ -697,23 +707,30 @@ class Db extends nb { },$pdo); } - public function tables2csv() { + public function dump2csv() { + $this->pset('format','csv'); + # Filters $name = self::p('name',self::p('table','')); $sep = self::p('sep',"\t"); + foreach ($this->tables() as $t) { if ( - ($t->type != 'table') - or (!empty($name) and !$this->str_match($t->name,$name)) - ) continue; - #$this->pset('table.rows_preffix',$t->name); - $o = ['preffix'=>'::'.$t->name.'::'.$sep]; - $o = []; - $t->rows($o); + ($t->type == 'table') + and (empty($name) or $this->str_match($t->name,$name)) + ) { + $o = [ + 'preffix' => $t->name.$sep + ]; $t->rows($o); + } } + + } + + public function dump() { + return $this->sql(true); } - public function dump() { return $this->sql(true); } public function sql($insert=null) { if ($insert === null) $insert = self::p('insert'); @@ -764,52 +781,43 @@ class Db extends nb { echo "-- Type : ".self::p('db.type')."\n"; $type_from = $this->type; $type_to = self::p('db.type'); - #$this->type_out = self::p('db.type'); - $this->type = self::p('db.type'); - #$this->type_out = self::p('db.type'); + $this->type = $type_to; + } + + # SQL_PRE + $i = 0; foreach ($this->sql_pre() as $s) { + if (($i++) == 0) echo "\n-- SQL_PRE\n"; + echo rtrim($s,';').";\n"; } - # Contruct DROP / CREATE foreach ($tables as $t) { - $sql = rtrim($t->create(false),';'); - $sql = str_replace(';CREATE',";\nCREATE",$sql); + # DROP / CREATE + $create = rtrim($t->create(false),';'); + $create = str_replace(';CREATE',";\nCREATE",$create); + if (!($drop = $t->unvar($this->method('sql.drop')))) $drop = + 'DROP '.strtoupper($t->type).' IF EXISTS '.$t->sql_name() + ; + /* if ($drop = $this->type('sql.drop')) { $drop = str_replace('',$t->name,$drop); $drop = str_replace('',strtoupper($t->type),$drop); } else { $drop = 'DROP '.strtoupper($t->type).' IF EXISTS '.$t->sql_name(); } + */ + $t->sql_create = $drop.';'.NB_EOL - .$sql.';'.NB_EOL + .$create.';'.NB_EOL ; - if ($insert) { - $t->sql_create = "\n-- ".strtoupper($t->type).": ".$t->name."\n".$t->sql_create; - } -#echo $t->sql_create."\n"; -#echo($t->name.' '.$t->type."\n"); - } - -#return; - - # EXEC - $i = 0; foreach ($this->sql_exec() as $s) { - if (($i++) == 0) echo "\n-- EXEC\n"; - echo rtrim($s,';').";\n"; - } - - $i = 0; foreach ((array)$this->method('sql.pre') as $s) { - if (($i++) == 0) echo "\n-- SQL.PRE\n"; - echo rtrim($s,';').";\n"; - } - - # DUMP - foreach ($tables as $t) { - + if ($insert) $t->sql_create = + "\n-- ".strtoupper($t->type).": ".$t->name."\n".$t->sql_create + ; echo $t->sql_create; + # INSERT if ($insert and $t->type == 'table') { if ($type_from) $this->type = $type_from; $t->rows(); @@ -829,6 +837,7 @@ class Db extends nb { $status = $new = []; +#debug(array_keys($this->tables())); $new = [] #+( empty($this->id) ? [] : ['id' => $this->id]) +[ @@ -911,19 +920,15 @@ class Db extends nb { return $fields; } - public function sql_exec() { + public function sql_pre() { $return = []; - if ($sql = $this->type('exec')) { - if (is_scalar($sql)) $sql = array($sql); - foreach ($sql as $s) { - if (!empty($s)) $return[] = $s; - } + foreach ((array)$this->type('sql_pre') as $s) { + if (!empty($s)) $return[] = $s; } - if (!empty($this->sql_exec) and ($sql = $this->sql_exec)) { - if (is_scalar($sql)) $sql = array($sql); - foreach ($sql as $s) { + if (!empty($this->sql_pre)) { + foreach ((array)$this->sql_pre as $s) { if (!empty($s)) $return[] = $s; } } @@ -1007,7 +1012,7 @@ class Db extends nb { $db = []; foreach ($fields as $k) { - if (isset($d->$k)) $db[$k] = $d->$k; + $db[$k] = isset($d->$k) ? $d->$k : ''; } if (!empty($name) and !$this->str_match($db['name'],$name)) continue; diff --git a/lib/php/db/table.php b/lib/php/db/table.php index c4bd3df9..461759f6 100644 --- a/lib/php/db/table.php +++ b/lib/php/db/table.php @@ -137,7 +137,7 @@ Class Table extends nb { public function create($from_sql=true) { // String replace function - $sql_replace_fct = $this->db()->type('sql.replace',false); + $sql_replace_fct = $this->db()->type('table.sql.create',false); $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); @@ -199,8 +199,8 @@ Class Table extends nb { if (is_array($sql)) list($sql,$fct) = (count($sql)==1 ? [$sql[0],null] : $sql); if (!$sql) return []; - $sql = str_replace('',$this->name,$sql); - $sql = str_replace('',$this->db()->name,$sql); + $sql = str_replace('',$this->name,$sql); + $sql = str_replace('',$this->db()->name,$sql); $st = $this->db()->conn->prepare($sql); $st->execute(); $this->indexes = []; @@ -228,7 +228,7 @@ Class Table extends nb { return $this->sql; } - $sql = str_replace('',$this->name,$this->db()->type('table.sql',true)); + $sql = str_replace('',$this->name,$this->db()->type('table.sql',true)); # Noise before CREATE like MySql $this->sql = explode('\0',$this->db()->row($sql,'\0')); @@ -269,7 +269,7 @@ Class Table extends nb { if (!isset($this->fields)) { $this->fields = array(); - $conf = str_replace('',$this->name,$this->db()->type('table.fields',true)); + $conf = $this->unvar($this->db()->type('table.fields',true)); if (is_scalar($conf)) $conf = array('sql'=>$conf); $rows = $this->db()->conn->query($conf['sql']); $rows->setFetchMode(PDO::FETCH_ASSOC); @@ -785,8 +785,10 @@ Class Table extends nb { $count = 0; # Parser on/off (default: on) - $parse = self::p('table.parse') === '0' ? 0 : 1; + if (!isset($opt['parser'])) $opt['parser'] = true; + $parse = $opt['parser']; #bye($parse); + while ($row = $st->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)) { $count++; @@ -811,6 +813,11 @@ Class Table extends nb { $call = $this->row_parse_post; $call($row); } + # + # Close previous + # + if ($out_conf and $count !== 1) out::row_end($out_conf); + # # Preffix # @@ -844,8 +851,6 @@ Class Table extends nb { # # Row # - if ($out_conf and $count !== 1) out::concat($out_conf); - $count_fields = 0; foreach ($this->fields() as $f => $field) { @@ -1670,6 +1675,21 @@ Class Table extends nb { return $this->type = $this->status('type'); } + public function unvar($value) { + if (empty($value)) return $value; + if (!is_scalar($value)) { + foreach ($value as $k=>$v) { + if (is_scalar($v)) $value[$k] = $this->unvar($v); + } + return $value; + } + $replace = [ + '' => $this->name, + '' => $this->type, + ]; + return str_replace(array_keys($replace),array_values($replace),$value); + } + public function status($key=null) { #if ($this->type!='table' and $this->type=!'view') { } if (!isset($this->status)) { diff --git a/lib/php/db/types/mysql.php b/lib/php/db/types/mysql.php index 3e31be82..a3afd756 100644 --- a/lib/php/db/types/mysql.php +++ b/lib/php/db/types/mysql.php @@ -6,30 +6,18 @@ if (!class_exists('Db')) { $DB_TYPES['mysql'] = array ( 'quote_name' => '`', -#'select_count' => array('SQL_CALC_FOUND_ROWS','SELECT FOUND_ROWS()'), +#'select_count' => ['SQL_CALC_FOUND_ROWS','SELECT FOUND_ROWS()'], -'exec' => array_merge( - [ - 'SET NAMES '.str_replace('utf-8','utf8',strtolower(Db::$encoding)), - #'SET NAMES '.str_replace(['utf-8','utf8'],['utf8mb4','utf8mb4'],strtolower(Db::$encoding)), - ], - (Db::p('db.type') ? [ - "/*!40103 SET TIME_ZONE='+00:00' */", - '/*!40014 SET @@UNIQUE_CHECKS=0 */', - '/*!40014 SET @@FOREIGN_KEY_CHECKS=0 */', - #"/*!40101 SET @SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */", - ] : []) -), 'extra_where' => 'having', 'localFile' => array (getenv('HOME').'/.my.cnf','^(?:user(?:name)?=(?P\\S+)|password=(?P\\S+))'), 'databases' => "SELECT SCHEMA_NAME as `name`,DEFAULT_CHARACTER_SET_NAME as `encoding`,DEFAULT_COLLATION_NAME as `collate` FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('performance_schema','information_schema','mysql')", 'file2table' => "LOAD DATA INFILE ' REPLACE INTO TABLE '' TERMINATED BY '\\t' ENCLOSED BY '' LINES TERMINATED BY '\\r\\n' IGNORE 0 LINES", -'table.sql' => 'SHOW CREATE TABLE ``', -'table.sql.index' => "SELECT ".(Db::p('db.type') ? "CONCAT(s.TABLE_NAME,'_',s.INDEX_NAME,'_idx')" : 's.INDEX_NAME')." as name,(CASE NON_UNIQUE WHEN 1 THEN 0 ELSE 1 END) as uniqe,GROUP_CONCAT(COLUMN_NAME) as field FROM INFORMATION_SCHEMA.STATISTICS s LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t ON t.TABLE_SCHEMA=s.TABLE_SCHEMA AND t.TABLE_NAME=s.TABLE_NAME AND s.INDEX_NAME=t.CONSTRAINT_NAME WHERE 0=0 AND t.CONSTRAINT_NAME IS NULL AND s.TABLE_SCHEMA = DATABASE() AND s.TABLE_NAME='' GROUP BY name ORDER BY SEQ_IN_INDEX", +'table.sql' => 'SHOW CREATE TABLE ``', +'table.sql.index' => "SELECT ".(Db::p('db.type') ? "CONCAT(s.TABLE_NAME,'_',s.INDEX_NAME,'_idx')" : 's.INDEX_NAME')." as name,(CASE NON_UNIQUE WHEN 1 THEN 0 ELSE 1 END) as uniqe,GROUP_CONCAT(COLUMN_NAME) as field FROM INFORMATION_SCHEMA.STATISTICS s LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t ON t.TABLE_SCHEMA=s.TABLE_SCHEMA AND t.TABLE_NAME=s.TABLE_NAME AND s.INDEX_NAME=t.CONSTRAINT_NAME WHERE 0=0 AND t.CONSTRAINT_NAME IS NULL AND s.TABLE_SCHEMA = DATABASE() AND s.TABLE_NAME='' GROUP BY name ORDER BY SEQ_IN_INDEX", # NB 04.07.16 '_table.sql.index' => [ -# NB 04.07.16 'SHOW INDEX FROM ``', function(&$r) { +# NB 04.07.16 'SHOW INDEX FROM ``', function(&$r) { # NB 04.07.16 if ($r['Key_name'] != 'PRIMARY') return [ # NB 04.07.16 'name' => $r['Key_name'], # NB 04.07.16 'field' => $r['Column_name'], @@ -101,7 +89,7 @@ $DB_TYPES['mysql'] = array ( }, 'table.fields' => array ( - 'sql' => 'SHOW COLUMNS FROM ``', + 'sql' => 'SHOW COLUMNS FROM ``', 'fct' => create_function('&$r',join('',array( '$r["autoincrement"] = $r["extra"] == "auto_increment" ? 1 : 0;', '$r["name"] = $r["field"];', @@ -110,9 +98,9 @@ $DB_TYPES['mysql'] = array ( ))), ), -'sql.replace' => function($sql) { +'table.sql.create' => function($sql,$t) { $sql = preg_replace('/ESCAPE \'.*?\'/','',$sql); - $sql = preg_replace('/CHARSET=\S+\s*$/','CHARSET='.str_replace('-','',Db::$encoding),$sql); + $sql = preg_replace('/CHARSET=\S+\s*$/','CHARSET='.str_replace('-','',$t->db()->charset),$sql); return $sql; }, diff --git a/lib/php/db/types/pgsql.php b/lib/php/db/types/pgsql.php index 67d12d16..ce315d52 100644 --- a/lib/php/db/types/pgsql.php +++ b/lib/php/db/types/pgsql.php @@ -3,7 +3,7 @@ $DB_TYPES['pgsql'] = array ( 'extra_where' => 'denorm', 'regexp' => '~', -'localFile' => array (getenv('HOME').'/.pgpass','^[^:]+:[^:]+::(?P[^:]+):(?[^:]+)'), +'localFile' => array (getenv('HOME').'/.pgpass','^[^:]+:[^:]+::(?P[^:]+):(?[^:]+)'), /* 'databases' => "SELECT d.datname as 'Name', @@ -24,7 +24,7 @@ ORDER BY 1", */ 'databases' => 'SELECT datname as name,pg_catalog.pg_get_userbyid(datdba) as owner,pg_catalog.pg_encoding_encoding) as encoding, datcollate as "collate",datctype as "Ctype" FROM pg_catalog.pg_database', -'sql.drop' => 'DROP IF EXISTS "" CASCADE', +'sql.drop' => 'DROP IF EXISTS "" CASCADE', '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')", # NB 02.08.16 'create' => function(&$field) { @@ -36,7 +36,7 @@ ORDER BY 1", # NB 02.08.16 $field->type = preg_replace(array_keys($r),array_values($r),$field->type); # NB 02.08.16 }, -'sql.replace' => function($sql) { +'table.sql.create' => function($sql) { $sql = preg_replace('/ESCAPE \'.*?\'/','',$sql); $r = array( '/datetime/i' => 'timestamp', @@ -59,7 +59,7 @@ FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid JOIN pg_catalog.pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) WHERE NOT indisprimary AND c.relkind IN ('i','s','') AND n.nspname !~ '^pg_toast' - AND EXISTS (SELECT 1 FROM pg_catalog.pg_class c2 WHERE i.indrelid = c2.oid AND c2.relname = '') + AND EXISTS (SELECT 1 FROM pg_catalog.pg_class c2 WHERE i.indrelid = c2.oid AND c2.relname = '') AND pg_catalog.pg_table_is_visible(c.oid) GROUP BY i.indrelid,c.relname,indisunique ORDER BY i.indrelid @@ -78,10 +78,10 @@ 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 +(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) + 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", @@ -133,12 +133,12 @@ FROM ( ORDER BY c.oid,f.attrelid ) ORDER BY prio DESC) sql -WHERE sql.table=\'\' +WHERE sql.table=\'\' 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) +FROM pg_catalog.pg_class c WHERE c.relname = \'\' AND pg_catalog.pg_table_is_visible(c.oid) ', # < table.sql );?> diff --git a/lib/php/db/types/sqlite.php b/lib/php/db/types/sqlite.php index b67a350e..ef9f25bc 100644 --- a/lib/php/db/types/sqlite.php +++ b/lib/php/db/types/sqlite.php @@ -4,28 +4,6 @@ if (!class_exists('Db')) { exit; } $DB_TYPES['sqlite'] = array ( -'sql.pre' => [ - 'PRAGMA foreign_keys=OFF', - 'BEGIN TRANSACTION', -], -'sql.post' => [ - 'COMMIT', -], -'_disconnect' => function($db) { - #$table->db()->exec("COMMIT"); - $db->conn->commit(); -}, -'exec' => [ - #'PRAGMA journal_mode = MEMORY', - #'PRAGMA journal_mode = WAL', - #'PRAGMA journal_mode = OFF', - #'PRAGMA synchronous = OFF', - #'PRAGMA auto_vacuum=2', - 'PRAGMA encoding = "'.strtoupper(Db::$encoding).'"', - #'PRAGMA temp_store = 2', - #'PRAGMA read_uncommitted = false', - #'BEGIN TRANSACTION', -], 'use_path' => true, 'extra_where' => 'denorm', @@ -39,10 +17,10 @@ $DB_TYPES['sqlite'] = array ( }, ], -#'table.sql' => 'SELECT sql FROM sqlite_master WHERE name=\'\'', -'table.sql' => "SELECT GROUP_CONCAT(sql,';') FROM sqlite_master WHERE tbl_name=''", +#'table.sql' => 'SELECT sql FROM sqlite_master WHERE name=\'\'', +'table.sql' => "SELECT GROUP_CONCAT(sql,';') FROM sqlite_master WHERE tbl_name=''", 'table.sql.index' => [ - "SELECT sql FROM sqlite_master WHERE type='index' AND tbl_name=''", function(&$r) { + "SELECT sql FROM sqlite_master WHERE type='index' AND tbl_name=''", function(&$r) { if (!preg_match('/^\s*CREATE(\s+UNIQUE)?\s+INDEX\s+(\S+)\s+ON \S+ \(([^)]+)\)/',$r['sql'],$m)) return; return [ 'name' => $m[2], @@ -51,7 +29,7 @@ $DB_TYPES['sqlite'] = array ( ]; }, ], -'table.csv.load' => ['.mode csv','.sep \\t','.import '], +'table.csv.load' => ['.mode csv','.sep \\t','.import '], 'sqliteCreateFunction' => array ( 'ip2int' => function ($value) { return ip2long($value); }, @@ -107,7 +85,7 @@ $DB_TYPES['sqlite'] = array ( #'tables' => '.tables', 'table.fields' => array ( - 'sql' => 'PRAGMA table_info(\'\')', + 'sql' => 'PRAGMA table_info(\'\')', 'fct' => create_function('&$r',join('',array( #'debug("zaza");', '$r["key"] = $r["pk"] == "0" ? 0 : 1;', @@ -132,7 +110,7 @@ $DB_TYPES['sqlite'] = array ( $field->type = preg_replace(array_keys($r),array_values($r),$field->type); }, -'sql.replace' => function($sql,$t) { +'table.sql.create' => function($sql,$t) { #$sql .= $t->db()->row("SELECT sql FROM sqlite_master WHERE type='index' AND tbl_name=".$t->sql_name()); return $sql; }, diff --git a/lib/php/out.php b/lib/php/out.php index 04e0f832..0585e17c 100644 --- a/lib/php/out.php +++ b/lib/php/out.php @@ -271,7 +271,7 @@ Class Out extends Nb { return true; } - public static function concat($o) { + public static function row_end($o) { if (isset($o['rec'])) echo $o['rec']; if (isset($o['eol'])) echo $o['eol']; } @@ -329,7 +329,7 @@ Class Out extends Nb { } - if ($count>0) self::concat($conf); + if ($count>0) self::row_end($conf); $count++; self::row($conf,$row); -- 2.47.3