From 88a8532e8cbe854fc3111c7954822abdffa310d1 Mon Sep 17 00:00:00 2001
From: Nicolas Boisselier <nicolas.boisselier@semantico.com>
Date: Tue, 2 Aug 2016 15:33:07 +0100
Subject: [PATCH] Fix db bug, add function.databases

---
 lib/php/db.php             | 22 ++++++++++++++++++++++
 lib/php/db/types/mysql.php |  1 +
 lib/php/db/types/pgsql.php | 21 ++++++++++++++++++++-
 3 files changed, 43 insertions(+), 1 deletion(-)

diff --git a/lib/php/db.php b/lib/php/db.php
index e65ec29f..003ce194 100644
--- a/lib/php/db.php
+++ b/lib/php/db.php
@@ -795,6 +795,28 @@ return;
 
     return $return;
   }
+
+  public function databases() {
+
+    if (!isset($this->databases)) {
+
+      $name = self::p('name','');
+      $sql = $this->type('databases');
+      if (!$sql) return [];
+
+      $st = $this->conn->prepare($sql);
+      $st->execute();
+      $this->databases = [];
+
+      while ($row = $st->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)) {
+        $this->databases[] = $row;
+      }
+
+    }
+
+    return $this->databases;
+  }
+
 } # < Class
 
 ?>
diff --git a/lib/php/db/types/mysql.php b/lib/php/db/types/mysql.php
index a1d46587..20594abf 100644
--- a/lib/php/db/types/mysql.php
+++ b/lib/php/db/types/mysql.php
@@ -22,6 +22,7 @@ $DB_TYPES['mysql'] = array (
 'extra_where' => 'having',
 
 'localFile' => array (getenv('HOME').'/.my.cnf','^(?:user(?:name)?=(?P<user>\\S+)|password=(?P<password>\\S+))'),
+'databases' => 'SELECT SCHEMA_NAME as `name`,DEFAULT_CHARACTER_SET_NAME as `encoding`,DEFAULT_COLLATION_NAME as `collate` FROM INFORMATION_SCHEMA.SCHEMATA',
 
 'table.sql' => 'SHOW CREATE TABLE `<NAME>`',
 '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 = '<DB>' AND s.TABLE_NAME='<NAME>' GROUP BY name ORDER BY SEQ_IN_INDEX",
diff --git a/lib/php/db/types/pgsql.php b/lib/php/db/types/pgsql.php
index 3bd3a3e7..44dbeaf6 100644
--- a/lib/php/db/types/pgsql.php
+++ b/lib/php/db/types/pgsql.php
@@ -4,6 +4,23 @@ $DB_TYPES['pgsql'] = array (
 
 'localFile' => array (getenv('HOME').'/.pgpass','^[^:]+:[^:]+:<NAME>:(?P<user>[^:]+):(?<password>[^:]+)'),
 
+/*
+'databases' => "SELECT d.datname as 'Name',
+       pg_catalog.pg_get_userbyid(d.datdba) as 'Owner',
+       pg_catalog.pg_encoding_to_char(d.encoding) as 'Encoding',
+       d.datcollate as 'Collate',
+       d.datctype as 'Ctype',
+       pg_catalog.array_to_string(d.datacl, E'\n') AS 'Access privileges',
+       CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
+            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
+            ELSE 'No Access'
+       END as 'Size',
+       t.spcname as 'Tablespace',
+       pg_catalog.shobj_description(d.oid, 'pg_database') as 'Description'
+FROM pg_catalog.pg_database d
+  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
+ORDER BY 1",
+*/
 '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')",
@@ -21,14 +38,16 @@ $DB_TYPES['pgsql'] = array (
   return $sql;
 },
 
+  #,(SELECT attname FROM pg_catalog.pg_attribute a WHERE a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)) as field
 '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
+  ,a.attname 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
+  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 = '<NAME>')
   AND pg_catalog.pg_table_is_visible(c.oid)
-- 
2.47.3