From b0fb30b491bba47331965bd1b0614343309c867b Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Mon, 4 Jul 2016 15:12:26 +0100 Subject: [PATCH] indexes, --- etc/dbs.yaml | 5 +++- lib/php/db.php | 6 ++-- lib/php/db/table.php | 25 +++++++++++++++- lib/php/db/types/mysql.php | 15 ++++++++++ lib/php/db/types/pgsql.php | 60 +++++++++++++++++++++++-------------- lib/php/db/types/sqlite.php | 11 ++++++- lib/php/nb.php | 12 ++++---- lib/php/out.php | 21 ++++--------- 8 files changed, 105 insertions(+), 50 deletions(-) diff --git a/etc/dbs.yaml b/etc/dbs.yaml index 3d125086..7153d2db 100644 --- a/etc/dbs.yaml +++ b/etc/dbs.yaml @@ -9,7 +9,10 @@ crypt: host: /dev/shm/crypt.db rt: - pdo: 'pgsql:host=db.rt.semantico.net;port=5432;dbname=rtdb;user=rtuser;password=' + type: pgsql + host: db.rt.semantico.net + name: rtdb + user: rtuser title: 'RT' izi: diff --git a/lib/php/db.php b/lib/php/db.php index 1d26caa9..e9685303 100644 --- a/lib/php/db.php +++ b/lib/php/db.php @@ -394,13 +394,13 @@ class Db extends nb { # Filters $name = self::p('name',''); $type = self::p('type',''); - $count = self::p('count',''); + #$count = self::p('count',''); $engine = self::p('engine',''); foreach ($this->tables() as $t) { if (!empty($name) and !$this->str_match($t->name,$name)) continue; if (!empty($type) and !$this->str_match($t->type,$type)) continue; - if (!empty($count) and !$this->str_match($t->count,$count)) continue; + #if (!empty($count) and !$this->str_match($t->count,$count)) continue; if (!empty($engine) and !$this->str_match($t->engine,$engine)) continue; $rows[] = $t->infos(); } @@ -438,7 +438,7 @@ class Db extends nb { } else $return = $this->out($this->dbs,"id"); - } elseif ($r=self::class_action_out($this,$action)) { + } elseif ($r=self::class_action_out($this,$action) !== null) { return $r; } elseif($table) { diff --git a/lib/php/db/table.php b/lib/php/db/table.php index 3b7a09f9..3ce8e962 100644 --- a/lib/php/db/table.php +++ b/lib/php/db/table.php @@ -166,6 +166,29 @@ Class Table extends nb { return $sql_replace($sql.')'.($sql_index ? ';'.$this->db()->row($sql_index) : '')); } + /* + * Function indexes + * + * return indexes + * + */ + public function indexes() { + $sql = str_replace('',$this->name,$this->db()->type('table.sql.index')); + if (is_array($sql)) list($sql,$fct) = count($sql)==1 ? [$sql[0],null] : $sql; + if (!$sql) return []; + + $st = $this->db()->conn->prepare($sql); + $st->execute(); + $return = []; + while ($row = $st->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)) { + + if (!$fct) $return[] = $row; + elseif ($r = $fct($row)) $return[] = $r; + + } + return $return; + } + /* * Function sql * @@ -1335,7 +1358,7 @@ Class Table extends nb { $this->out($e); return true; - } elseif (self::class_action_out($this,$action)) { + } elseif (self::class_action_out($this,$action) !== null) { return true; } elseif ($this->p('format') and !preg_match('/^(table|div)$/',$this->p('format'))) { diff --git a/lib/php/db/types/mysql.php b/lib/php/db/types/mysql.php index 27de8951..fcbe8b2a 100644 --- a/lib/php/db/types/mysql.php +++ b/lib/php/db/types/mysql.php @@ -1,4 +1,8 @@ '`', @@ -13,6 +17,16 @@ $DB_TYPES['mysql'] = array ( 'localFile' => array (getenv('HOME').'/.my.cnf','^(?:user(?:name)?=(?P\\S+)|password=(?P\\S+))'), 'table.sql' => 'SHOW CREATE TABLE ``', +'table.sql.index' => [ + 'SHOW INDEX FROM ``', function(&$r) { + if ($r['Key_name'] != 'PRIMARY') return [ + 'id' => $r['Key_name'], + 'field' => $r['Column_name'], + 'uniq' => ($r['Non_unique'] ? 0 : 1), + 'type' => $r['Index_type'] + ]; + }, +], 'tables' => 'SELECT TABLE_NAME as name,LOWER(IF(TABLE_TYPE=\'BASE TABLE\',\'TABLE\',TABLE_TYPE)) as type,ENGINE as engine,CREATE_TIME as created FROM information_schema.tables WHERE TABLE_SCHEMA=DATABASE()', @@ -77,6 +91,7 @@ $DB_TYPES['mysql'] = array ( 'sql.replace' => function($sql) { $sql = preg_replace('/ESCAPE \'.*?\'/','',$sql); + $sql = preg_replace('/CHARSET=latin1\s*$/','CHARSET='.str_replace('-','',Db::$encoding),$sql); return $sql; }, diff --git a/lib/php/db/types/pgsql.php b/lib/php/db/types/pgsql.php index 7be54138..3bd3a3e7 100644 --- a/lib/php/db/types/pgsql.php +++ b/lib/php/db/types/pgsql.php @@ -4,7 +4,9 @@ $DB_TYPES['pgsql'] = array ( '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\')', +'databases' => 'SELECT datname as name,pg_catalog.pg_get_userbyid(datdba) as owner,pg_catalog.pg_encoding_to_char(encoding) as encoding, datcollate as "collate",datctype as "Ctype" FROM pg_catalog.pg_database', + +'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')", 'create' => function(&$field) { $r = array( @@ -19,6 +21,41 @@ $DB_TYPES['pgsql'] = array ( return $sql; }, +'table.sql.index' => [ + "SELECT + c.relname as name + ,(SELECT attname FROM pg_catalog.pg_attribute a WHERE a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)) as field + ,(CASE indisunique WHEN 't' THEN 1 ELSE 0 END) as unique +FROM pg_catalog.pg_class c + LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace + LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid +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 pg_catalog.pg_table_is_visible(c.oid) +ORDER BY i.indrelid +" +], +'table.fields' => array ( + 'fct' => create_function('&$r',join('',array( + 'if (!isset($r["pg_default"])) return;', + 'if (preg_match("/^nextval\(/",$r["pg_default"])) { $r["autoincrement"] = 1; }', + 'elseif (preg_match("/^\'\'/",$r["pg_default"])) { $r["default"] = ""; }', + 'else { $r["default"] = $r["pg_default"]; }', + ))), + 'sql' => "SELECT +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 +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) + ) + AND a.attnum > 0 AND NOT a.attisdropped +ORDER BY a.attnum", +), + 'table.sql' => 'SELECT CASE c.relkind::char WHEN \'r\' THEN ( SELECT \'CREATE TABLE \'||sql.table||\'( @@ -73,25 +110,4 @@ ELSE \'NOT\' END as sql FROM pg_catalog.pg_class c WHERE c.relname = \'\' AND pg_catalog.pg_table_is_visible(c.oid) ', # < table.sql -'table.fields' => array ( - 'fct' => create_function('&$r',join('',array( - 'if (!isset($r["pg_default"])) return;', - 'if (preg_match("/^nextval\(/",$r["pg_default"])) { $r["autoincrement"] = 1; }', - 'elseif (preg_match("/^\'\'/",$r["pg_default"])) { $r["default"] = ""; }', - 'else { $r["default"] = $r["pg_default"]; }', - ))), - 'sql' => 'SELECT -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 -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) - ) - AND a.attnum > 0 AND NOT a.attisdropped -ORDER BY a.attnum', -), - );?> diff --git a/lib/php/db/types/sqlite.php b/lib/php/db/types/sqlite.php index 39db9881..44a81ebd 100644 --- a/lib/php/db/types/sqlite.php +++ b/lib/php/db/types/sqlite.php @@ -13,7 +13,16 @@ $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.index' => "SELECT sql FROM sqlite_master WHERE type='index' AND tbl_name=''", +'table.sql.index' => [ + "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], + 'field' => $m[3], + 'unique' => (empty($m[1]) ? 0 : 1), + ]; + }, +], 'table.csv.load' => ['.mode csv','.sep \\t','.import '], 'sqliteCreateFunction' => array ( diff --git a/lib/php/nb.php b/lib/php/nb.php index 7d350248..fef5f436 100644 --- a/lib/php/nb.php +++ b/lib/php/nb.php @@ -478,10 +478,13 @@ class NB { )); } + /** + * If action on class, gener output + */ public static function class_action_out($class,$action) { $class_name = strtolower(get_class($class)); - if (!preg_match('/^'.$class_name.'\.(\w+)/',$action,$m)) return false; + if (!preg_match('/^'.$class_name.'\.(\w+)/',$action,$m)) return null; $name = $m[1]; $rows = array(); @@ -489,6 +492,7 @@ class NB { elseif (in_array($name,get_class_vars($class_name))) $rows = $class->$name; else self::bye("Unknow action: `$action`"); #else $rows = $class->$name; + if (is_array($rows) and !$rows) return $rows; if (!$rows) return false; if ($rows === true) return true; @@ -499,12 +503,8 @@ class NB { #return true; } #debug($action.' '.$class_name.' '.$name); - out::rows(self::p('format',self::php_cli() ? 'csv' : 'table'),$rows,(is_scalar($rows) ? $name : array())); - return true; - $class->out2(self::object2array($rows),(is_scalar($rows) ? $name : array())); + out::rows(self::p('format',self::php_cli() ? 'csv' : 'table'),$rows,(is_scalar($rows) ? $name : [])); return true; - return self::object2array($rows); - return $class->out2(self::object2array($rows),(is_scalar($rows) ? $name : array())); } public static function cidr_range( $cidr, $chkip=null ) { diff --git a/lib/php/out.php b/lib/php/out.php index 8c71e8a3..da8f6e4e 100644 --- a/lib/php/out.php +++ b/lib/php/out.php @@ -353,13 +353,7 @@ Class Out extends Nb { /****************************************************************************/ function out_yaml(&$row,$o) { - #echo out::yaml_encode($row); return; - #$yaml = ' '.out::yaml_encode($row); - #$yaml = out::yaml_encode($row); - #echo yaml_emit($row); return; - #echo out::yaml_encode($row); return; $yaml = '- '.out::yaml_encode($row); - #$yaml = (out::is_hash($row) ? ' ' : '- ') . out::yaml_encode($row); $yaml = preg_replace("/^(?!-)/m"," ",$yaml); echo $yaml; } @@ -369,12 +363,9 @@ function out_csv(&$row,$o) { $values = array(); foreach (array_values($row) as $k=>$v) { -# NB 06.04.16 $values[] = preg_replace('/zAZA/','',out::scalar($v)); $values[] = out::scalar($v); - #$values[] = preg_replace('/\r?\n/','',out::scalar($v)); } - #echo join($o['sep'],array_values($row)); echo str_replace("\n",'\\n',join($o['sep'],$values)); } @@ -389,9 +380,7 @@ function out_csv_head(&$row,$o) { } } -#function out_tag_head(&$row,$o) { return ''; } function out_tag_head(&$data,$o) { -#return var_dump($row,true); return ''; #if (isset($o['tag_key'])) return ''; # No header, field will be in rows - NB 24.03.16 if (isset($o['tag_head'])) $o['tag'] = $o['tag_head']; $row = out::ar_first($data); @@ -403,12 +392,10 @@ function out_tag_head(&$data,$o) { # Avoid: name if (!out::is_hash($row)) $row = array_combine(array_values($row),array_values($row)); - #bye($row); out_tag($row,$o); } function out_tag(&$row,&$o) { -#var_dump($row); return ''; if (isset($o['tag_enclose'])) echo (NB_EOL ? ' ' : '').'<'.$o['tag_enclose'].'>'.NB_EOL; foreach ($row as $k => $v) { @@ -452,9 +439,10 @@ function out_xml(&$row,$o) { #die("A NB_EOL B \n"); if (empty($argv) or count($argv) < 2 or $argv[1] != 'test') return true; $data = array( - 'a' => 1, - 'b' => 2, + 'fatc_name_idx' => 'name', + 'fatc_value_idx' => 'value', ); +/* $data = array( 'a', 'b', 'c' ); $data = array( array('a1','b1','c1'), @@ -466,7 +454,8 @@ $data = array( array('a' => 'a1', 'b' => 'b1'), array('a' => 'a2', 'b' => 'b2'), ); +*/ #$data = array( array('a'=>'A'), array('b'=>'B'), array('c'=>'C') ); $field = array(); -$o = out::rows($argv[2] ? $argv[2] : 'yaml',$data,$field); +$o = out::rows(!empty($argv[2]) ? $argv[2] : 'yaml',$data,$field); ?> -- 2.47.3