From 2f9751a5aa39c35015674dd79cc47c7a2c52c647 Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Sat, 3 Sep 2016 23:58:52 +0100 Subject: [PATCH] test --- etc/dbs.php | 67 +++++++++++++++++++++++++++++-------- etc/dbs.yaml | 10 ------ etc/dbs/all.php | 57 +++++++++++++++++++++++++++++++ etc/dbs/nb.php | 10 +++--- lib/php/db.php | 40 ++++++++++++---------- lib/php/db/index.php | 2 +- lib/php/db/types/mysql.php | 3 +- lib/php/db/types/sqlite.php | 25 +++++++++++--- lib/php/nb.php | 2 +- 9 files changed, 161 insertions(+), 55 deletions(-) create mode 100644 etc/dbs/all.php diff --git a/etc/dbs.php b/etc/dbs.php index 5fa65b0c..7e60ac3c 100644 --- a/etc/dbs.php +++ b/etc/dbs.php @@ -1,6 +1,6 @@ ':memory:', + 'type' => 'sqlite', + 'pdo' => '', + #'order' => '0', + 'options' => [ + PDO::ATTR_PERSISTENT => true, + ], +]; +$CONF['all']['tables'] = []; +$CONF['all']['types']['exec'] = []; +foreach ($CONF as $id => $db) { + if (0 + or empty($db['type'] ) + or $db['type']!='sqlite' + or empty($db['host'] ) + or !is_readable($db['host']) + ) continue; + #debug(basename($db['host'],'.db')); + $CONF['all']['types']['exec'][] = "ATTACH DATABASE '".$db['host']."' as ".basename($db['host'],'.db').""; + conf_merge($CONF[$id],$CONF['all']); + +} + +conf_merge($CONF['_nb'],$CONF['all']); +conf_merge($CONF['nb'],$CONF['all']); +function conf_merge(&$c1,&$c2) { + + if (!empty($c1['tables'])) + #debug('zaza'); + #$c2['tables'] = array_merge($c2['tables'],$c1['tables']); + #$c2['tables'] + #$c1['tables'] + foreach ($c1['tables'] as $k=>$v) { + $c2['tables'][$k] = $v; + } + ; + + foreach ([ + 'default_table', + 'title', + #'_import', + ] as $k) if (!empty($c1[$k])) + $c2[$k] = $c1[$k]; + ; + + return [$c1,$c2]; } ?> diff --git a/etc/dbs.yaml b/etc/dbs.yaml index 6ed8599a..c0e1c98b 100644 --- a/etc/dbs.yaml +++ b/etc/dbs.yaml @@ -41,13 +41,3 @@ izi-dev: #user: nico _import: - izi - -mem: - name: mem - host: ":memory:" - type: sqlite - types: - exec: - - "ATTACH DATABASE '/opt/www/sem_ui/var/db/semantico.db' as ui" - - "ATTACH DATABASE '/home.local/nicoadm/ownCloud/var/lib/sqlite/wp.db' as wp" - - "ATTACH DATABASE '/home.local/nicoadm/ownCloud/var/lib/sqlite/nb.db' as nb" diff --git a/etc/dbs/all.php b/etc/dbs/all.php new file mode 100644 index 00000000..23c0a099 --- /dev/null +++ b/etc/dbs/all.php @@ -0,0 +1,57 @@ + ':memory:', + 'type' => 'sqlite', + #'order' => '0', + 'options' => [ + PDO::ATTR_PERSISTENT => true, + ], +); +$attach = []; +foreach ([ + '/opt/www/sem_ui/var/db/semantico.db', + '/dev/shm/crypt.db', +] as $file) { + if (file_exists($file)) $attach[] = $file; +} + +foreach (nb::ls_dir($DIR_SQLITE,'\.db$') as $file) { + $attach[] = "$DIR_SQLITE/$file"; +} + +# nb last to win the merge +usort($attach,function($a,$b){ + if ( strpos($a,'/nb.db') !== false ) return 1; + if ( strpos($b,'/nb.db') !== false ) return -1; + return strcmp($a,$b); + return 0; +}); +if (!$attach) return; + +$CONF['all']['types']['exec'] = []; +$CONF['all']['_import'] = []; +if (nb::p('debug')=='all') debug($attach); + +foreach ($attach as $file) { + if (!is_readable($file)) continue; + + $name = basename($file,'.db'); + $CONF['all']['types']['exec'][] = "ATTACH DATABASE '$file' as $name"; + #$CONF['all']['_import'][] = $name; +} +$CONF['all']['_import'][] = 'rent'; +#bye($CONF['all']['default_table']); + +#$CONF['all']['default_table'] = 'rent'; +#debug($CONF['all']); +?> diff --git a/etc/dbs/nb.php b/etc/dbs/nb.php index 50bdbad5..76b144a5 100644 --- a/etc/dbs/nb.php +++ b/etc/dbs/nb.php @@ -5,8 +5,8 @@ $CONF['nb'] = array( 'type' => 'mysql', 'name' => 'nb', #'order' => ($LOCAL_DB ? null : 1), - 'order' => 1, - '_import' => array('_nb','_nico'), + #'order' => 1, + '_import' => ['_nb','_nico'], ); $CONF['nb-sqlite'] = array ( @@ -15,11 +15,11 @@ $CONF['nb-sqlite'] = array ( '_import' => '_nb', ); -if ($LOCAL_DB and !empty($CONF['nb-sqlite'])) { +/***************************************************************************** +if ($LOCAL_DB and !empty($CONF['nb-sqlite']) and isset($CONF['nb']['order'])) { $CONF['nb-sqlite']['order'] = isset($CONF['nb']['order']) ? $CONF['nb']['order'] : 1; unset($CONF['nb']['order']); } -/***************************************************************************** *****************************************************************************/ #die(print_r(posix_getpwnam('nico'),true)); #die(print_r(posix_getpwuid(posix_getuid()),true)); @@ -121,7 +121,7 @@ function rent_doc($table,&$r) { ) { - foreach (ls_dir($dir,true) as $p) { + foreach (nb::ls_dir($dir,'',true) as $p) { $r['doc'] .= sprintf('%s ',"$url/$p", nb::prettyText(preg_replace('@^.*?([^/\.]+).*?$@','\1',$p)) ); diff --git a/lib/php/db.php b/lib/php/db.php index e1b9a5e6..1e4953a3 100644 --- a/lib/php/db.php +++ b/lib/php/db.php @@ -20,9 +20,7 @@ class Db extends nb { public static $encoding = 'utf-8'; public $conn; public $pdo; -# NB 07.04.16 public $pdo_error = array( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); # See: http://php.net/manual/en/pdo.error-handling.php - public $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION); # See: http://php.net/manual/en/pdo.error-handling.php - #public $options; + public $options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]; # See: http://php.net/manual/en/pdo.error-handling.php public $host = null; public $port = null; public $user = null; @@ -39,8 +37,8 @@ class Db extends nb { public $default_table; public $sort; public $extras; - public $formats = array( 'table','div','csv','xml','json','yaml' ); - public $limits = array('10','20','50','100','500','1000'); + public $formats = [ 'table','div','csv','xml','json','yaml','sh' ]; + public $limits = ['10','20','50','100','500','1000']; function __construct($opt = '') { @@ -48,6 +46,12 @@ class Db extends nb { if ($opt==='') $opt = []; $opt = is_scalar($opt) ? ['pdo' => $opt] : $opt; + # Options + if (isset($opt['options'])) { + foreach ($opt['options'] as $k=>$v) $this->options[$k] = $v; + unset($opt['options']); + } + # Tables if (isset($opt['tables'])) { foreach ($opt['tables'] as $name=>$param) $this->table($name,$param); @@ -147,7 +151,6 @@ class Db extends nb { # Connect try { $this->conn = new PDO($this->pdo,$this->user,$this->password,$this->options); - #$this->conn = new PDO($this->pdo,$this->user,$this->password,$this->pdo_error); #if (isset($this->pdo_error)) $this->conn->setAttribute($this->pdo_error[0], $this->pdo_error[1]); } catch (PDOException $e) { @@ -300,8 +303,11 @@ class Db extends nb { return $value; if (!is_scalar($value)) return $value; $replace = [ + '' => $this->db()->name, + '' => $this->name, + '' => $this->type, + '' => $this->name.'___TODEL___', '' => $this->name, - '' => $this->name, '' => $this->type, '' => self::$encoding, ]; @@ -309,10 +315,10 @@ class Db extends nb { return str_replace(array_keys($replace),array_values($replace),$value); } - public function info($key=null,$die=false,$type=null) { - $info = $this->type($key,$die,$type); - if (is_callable($info)) return $info($this); - return $this->unvar($info); + public function method($key=null,$type=null) { + $method = $this->type($key,false,$type); + if (is_callable($method)) return $metod = $method($this); + return $this->unvar($method); } public function type($key=null,$die=false,$type=null) { @@ -323,7 +329,7 @@ class Db extends nb { # Load php file static $require = []; if (empty($require[$type])) { - if (empty($type)) self::bye('Db::$type is required'); + if (empty($type)) self::bye('Db->type is required'); require_once(dirname(__FILE__).'/db/types/'.$type.'.php'); $require[$type] = 1; } @@ -366,6 +372,8 @@ class Db extends nb { $sql = $this->type('tables',true,$this->type); if (is_callable($sql)) $sql = $sql($this); + $sql = $this->unvar($sql); + $sql = $this->method('tables'); foreach ($this->conn->query($sql,PDO::FETCH_ASSOC) as $row) { #debug($row); @@ -531,9 +539,9 @@ class Db extends nb { # Sort by `order`, min first uasort($h,function($a,$b){ - if (empty($a["order"]) and empty($b["order"])) return strcmp($a['id'],$b['id']); - $a_ = !empty($a["order"]) ? $a["order"] : 9999999; - $b_ = !empty($b["order"]) ? $b["order"] : 9999999; + if (empty($a['order']) and empty($b['order'])) return strcmp($a['id'],$b['id']); + $a_ = !empty($a['order']) ? $a['order'] : 9999999; + $b_ = !empty($b['order']) ? $b['order'] : 9999999; return($a_-$b_); }); @@ -951,8 +959,6 @@ class Db extends nb { $db = []; foreach ($fields as $k) { - #if (isset($d::$k)) $db[$k] = $d::$k; - #if ($k=='type' and isset($d::$type)) $db[$k] = $d::$type; if (isset($d->$k)) $db[$k] = $d->$k; } diff --git a/lib/php/db/index.php b/lib/php/db/index.php index 30e832ed..02656a59 100755 --- a/lib/php/db/index.php +++ b/lib/php/db/index.php @@ -17,7 +17,7 @@ Db::pdef('format',out::client_type()); if(defined('DB_NO_INIT') and DB_NO_INIT) return true; #bye(Db::conf_dbs(array( Db::ROOT_DIR.'/etc/dbs.yaml','/etc/dbs.yaml' ))); #$conf = Db::conf_dbs(array( Db::ROOT_DIR.'/etc/dbs.yaml','/etc/dbs.yaml' )); Db::init($conf); -Db::init(array( Db::ROOT_DIR.'/etc/dbs.yaml',Db::ROOT_DIR.'/etc/dbs.php','/etc/dbs.yaml' )); +Db::init([Db::ROOT_DIR.'/etc/dbs.yaml',Db::ROOT_DIR.'/etc/dbs.php','/etc/dbs.yaml']); if(defined('DB_NO_ACTION') and DB_NO_ACTION) return true; return $Db->action($Db->p('action'),$Table); diff --git a/lib/php/db/types/mysql.php b/lib/php/db/types/mysql.php index 60f64996..881a739e 100644 --- a/lib/php/db/types/mysql.php +++ b/lib/php/db/types/mysql.php @@ -25,7 +25,7 @@ $DB_TYPES['mysql'] = array ( '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')", '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 = '' AND s.TABLE_NAME='' GROUP BY name ORDER BY SEQ_IN_INDEX", +'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 if ($r['Key_name'] != 'PRIMARY') return [ @@ -103,7 +103,6 @@ $DB_TYPES['mysql'] = array ( 'fct' => create_function('&$r',join('',array( '$r["autoincrement"] = $r["extra"] == "auto_increment" ? 1 : 0;', '$r["name"] = $r["field"];', - #'debug($r["key"]);', '$r["uniq"] = $r["key"] == "UNI" ? 1 : 0;', '$r["key"] = $r["key"] == "PRI" ? 1 : 0;', ))), diff --git a/lib/php/db/types/sqlite.php b/lib/php/db/types/sqlite.php index cef379a8..10b0ab8d 100644 --- a/lib/php/db/types/sqlite.php +++ b/lib/php/db/types/sqlite.php @@ -65,12 +65,27 @@ $DB_TYPES['sqlite'] = array ( ,2), ), -'tables' => "SELECT name,type FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type IN('table','view') AND name NOT LIKE 'sqlite_%'", -'_tables' => function($Db) { - #debug($Db->databases()); - $sql = "SELECT name,type FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type IN('table','view') AND name NOT LIKE 'sqlite_%'"; +'_tables' => "SELECT name,type FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type IN('table','view') AND name NOT LIKE 'sqlite_%'", +'tables' => function($Db) { + # NB 03.09.16 + # Handle attach mechnisum $dbs = $Db->databases(); - if (count($dbs)<2) return $sql; + $debug = 0;#$Db->p('db')=='all'; + + if (count($dbs)<2) { + return "SELECT name,type FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type IN('table','view') AND name NOT LIKE 'sqlite_%'"; + } + + if ($debug) debug($dbs); + $sql = ''; + foreach ($dbs as $db) { + $sql .= ($sql ? ' UNION ' : '').str_replace('sqlite_',$db['name'].'.sqlite_', + "SELECT '".$db['name'].".'||name as name,type FROM sqlite_master WHERE type IN('table','view') AND name NOT LIKE 'sqlite_%'" + ); + } + if ($debug) debug($sql); + + return $sql; }, #'tables' => '.tables', diff --git a/lib/php/nb.php b/lib/php/nb.php index bd1fe8cc..b55c244d 100644 --- a/lib/php/nb.php +++ b/lib/php/nb.php @@ -695,7 +695,7 @@ class NB { if ($recurse and is_dir("$path/$file")) { #print ">>>$path/$file\n"; - foreach (ls_dir("$path/$file",$recurse,$exp) as $l) { + foreach (self::ls_dir("$path/$file",$recurse,$exp) as $l) { #print ">>>$path/$file << $l\n"; $ls[] = "$file/$l"; #$ls[] = $l; -- 2.47.3