RE: Generic way of fetching a list of databases

[Fennell, Brian]

If you like you can review the "data_sources" subroutine defined here:

https://metacpan.org/pod/DBI

If you find the completely Generic way to list databases Lacking - here are few 
particular solutions:

In Particular for PostgreSQL:
This lists databases:
SELECT datname as database
FROM pg_database
WHERE datistemplate = false;

This lists tables in the current database
SELECT table_schema,table_name
FROM information_schema.tables
ORDER BY table_schema,table_name;


https://dba.stackexchange.com/questions/1285/how-do-i-list-all-databases-and-tables-using-psql

https://dba.stackexchange.com/a/1304


In Particular for Mysql:

show databases

OR

SELECT SCHEMA_NAME AS Database
FROM INFORMATION_SCHEMA.SCHEMATA

https://stackoverflow.com/questions/4366905/perl-dbi-dbdmysql-get-the-databases-name-from-mysql-server

In Particular for Oracle (what mysql and postgress call "Database" Oracle calls 
"Schema"):

https://stackoverflow.com/a/298771

SELECT USERNAME as SCHEMA FROM ALL_USERS ORDER BY USERNAME;

Or

SELECT USERNAME as SCHEMA FROM DBA_USERS ORDER BY USERNAME;

Or

SELECT DISTINCT OWNER as SCHEMA from ALL_OBJECTS order by OWNER;

Or

SELECT DISTINCT OWNER as SCHEMA from DBA_OBJECTS order by OWNER;

https://dba.stackexchange.com/questions/27725/how-to-see-list-of-databases-in-oracle

https://stackoverflow.com/questions/8739203/oracle-query-to-fetch-column-names

https://stackoverflow.com/questions/4833459/oracle-sql-query-for-listing-all-schemas-in-a-db

https://ss64.com/orad/ALL_OBJECTS.html

https://ss64.com/orad/DBA_OBJECTS.html


In particular for SQLServer (SQLServer has both databases and schemas - schemas 
are like Oracle schemas, databases are groups of schemas).

SELECT name FROM master.sys.databases

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

SELECT CATALOG_NAME AS DataBaseName  FROM INFORMATION_SCHEMA.SCHEMATA


https://stackoverflow.com/questions/147659/get-list-of-databases-from-sql-server

https://stackoverflow.com/questions/3719623/how-do-i-obtain-a-list-of-all-schemas-in-a-sql-server-database

https://stackoverflow.com/questions/873393/sql-server-query-to-find-all-current-database-names

https://stackoverflow.com/questions/5323740/difference-between-database-and-schema







The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

Re: Generic way of fetching a list of databases

[pali]

On Friday 16 November 2018 17:04:50 Marius Feraru wrote:
> On Fri, Nov 16, 2018 at 10:38 AM  wrote:
> > Hi! If you want to list all databases then use DBI's data_sources method:
> > https://metacpan.org/pod/DBI#data_sources
> 
> And DBD::mysql points to this method for a while:
> https://github.com/perl5-dbi/DBD-mysql/blame/9f96fab985327773397a60020f5dcb76dfd8c396/lib/DBD/mysql.pm#L1432
> 
> Yet, it never worked for me, while their "legacy" version still does:
> $ perl -MData::Dump=dd -MDBI -e 'for $impl (qw[mysql MariaDB]) { $plm
> = lc $impl; $dbh =
> DBI->connect("dbi:$impl:;${plm}_read_default_group=mysql"); dd {
> listdbs => [ eval {$dbh->func("_ListDBs")} ], data_sources => [
> $dbh->data_sources ], dbd => "DBD::$impl", dbi_version =>
> $DBI::VERSION, dbd_version => ${"DBD::$impl\::VERSION"}, map { $_ =>
> $dbh->{"${plm}_$_"} } qw(clientinfo clientversion serverversion) } }'
> {
>   clientinfo => "8.0.13",
>   clientversion => 80013,
>   data_sources => [],
>   dbd => "DBD::mysql",
>   dbd_version => 4.048,
>   dbi_version => 1.642,
>   listdbs => ["information_schema", "mysql", "performance_schema", "sys"],
>   serverversion => 80013,
> }
> {
>   clientinfo => "8.0.13",
>   clientversion => 80013,
>   data_sources => [
> "DBI:MariaDB:information_schema",
> "DBI:MariaDB:mysql",
> "DBI:MariaDB:performance_schema",
> "DBI:MariaDB:sys",
>   ],
>   dbd => "DBD::MariaDB",
>   dbd_version => "1.00",
>   dbi_version => 1.642,
>   listdbs => [],
>   serverversion => 80013,
> }
> 
> Pali, seeing that your flavor's data_sources does work, I reckon it
> might be yet another update to upstream. I suggest keeping ListDBs
> retirement separate though. ;-)

In DBD::MariaDB was that legacy _ListDBs() method removed. Just because
DBI provides generic method data_sources().

Documentation for DBD::MariaDB also describes how to call data_sources()
method also without need to create $dbh connection. You need to specify
host, port, user, password:
https://metacpan.org/pod/DBD::MariaDB#data_sources

Btw, same additional arguments (host, port, user, password) work also
with DBD::mysql, just it is not properly documented.

IIRC, data_sources() method work also with PostgreSQL...
https://metacpan.org/pod/DBD::Pg#data_sources

> For the sake of discussion –and to disappoint Daniel, sorry– given
> this lack of coherence (NOT only at DBD level, e.g. privileges might
> be involved), I have not been able to rely on DBI::data_sources,
> having to employ DBMS specific queries instead.
> e.g. @mysql: while "show databases" usually still works, an
> "information_schema" based alternative/fallback could be useful.
> 
> Cheers!

Re: Generic way of fetching a list of databases

[Marius Feraru]

On Fri, Nov 16, 2018 at 10:38 AM  wrote:
> Hi! If you want to list all databases then use DBI's data_sources method:
> https://metacpan.org/pod/DBI#data_sources

And DBD::mysql points to this method for a while:
https://github.com/perl5-dbi/DBD-mysql/blame/9f96fab985327773397a60020f5dcb76dfd8c396/lib/DBD/mysql.pm#L1432

Yet, it never worked for me, while their "legacy" version still does:
$ perl -MData::Dump=dd -MDBI -e 'for $impl (qw[mysql MariaDB]) { $plm
= lc $impl; $dbh =
DBI->connect("dbi:$impl:;${plm}_read_default_group=mysql"); dd {
listdbs => [ eval {$dbh->func("_ListDBs")} ], data_sources => [
$dbh->data_sources ], dbd => "DBD::$impl", dbi_version =>
$DBI::VERSION, dbd_version => ${"DBD::$impl\::VERSION"}, map { $_ =>
$dbh->{"${plm}_$_"} } qw(clientinfo clientversion serverversion) } }'
{
  clientinfo => "8.0.13",
  clientversion => 80013,
  data_sources => [],
  dbd => "DBD::mysql",
  dbd_version => 4.048,
  dbi_version => 1.642,
  listdbs => ["information_schema", "mysql", "performance_schema", "sys"],
  serverversion => 80013,
}
{
  clientinfo => "8.0.13",
  clientversion => 80013,
  data_sources => [
"DBI:MariaDB:information_schema",
"DBI:MariaDB:mysql",
"DBI:MariaDB:performance_schema",
"DBI:MariaDB:sys",
  ],
  dbd => "DBD::MariaDB",
  dbd_version => "1.00",
  dbi_version => 1.642,
  listdbs => [],
  serverversion => 80013,
}

Pali, seeing that your flavor's data_sources does work, I reckon it
might be yet another update to upstream. I suggest keeping ListDBs
retirement separate though. ;-)

For the sake of discussion –and to disappoint Daniel, sorry– given
this lack of coherence (NOT only at DBD level, e.g. privileges might
be involved), I have not been able to rely on DBI::data_sources,
having to employ DBMS specific queries instead.
e.g. @mysql: while "show databases" usually still works, an
"information_schema" based alternative/fallback could be useful.

Cheers!

Re: Generic way of fetching a list of databases

[Peter Vanroose]

IMHO, listing databases is not well-defined in a cross-RDBMS context, since the 
definition of database largely differs between RDBMS products. As an example, 
"database" even has different definitions between Db2 for Linux/Unix/Windows on 
the one hand, and Db2 for z/OS on the other hand!

What should be well-defined, and maybe that's what you want, is listing schema 
names. For some RDBMS (e.g. MySQL) schemas coincode with databases. On most 
platforms they coincode with users.
But the definition of a schema is clear: it's the (explicit) table prefix 
(dot-separated) you need to specify when referring tables in an other than your 
default schema.
As some answers were suggesting, just look at the list of tables 
(information_schema.tables or the like) and return the distinct values from the 
column called schema (or user or creator or the like).

Your example with PostgreSQL seems to suggest that your definition of database 
is the "connection entity"; but listing all "connection entities" is 
intrinsically impossible since you first need to connect (to a database) before 
you can start asking it for the objects it knows, and by definition it only 
knows about itself ...

-- Peter Vanroose,
ABIS Training & Consulting,
Leuven, Belgium.


On 16 november 2018 03:09 Daniel Kasak <> wrote:

> [... ]
> I've done quite a bit of search, but can't find any docs that mention 
> fetching *databases* - either in ODBC docs or in Perl/DBI docs. The closest 
> I've found that *might* have worked was DBI's tables() method:
> 
> 
> ... but:
> - this doesn't work in cases where there is a separation between hierarchies 
> at the database level ( eg postgres only lists schemas and tables in the 
> current database )
>

Re: Generic way of fetching a list of databases

[Darren Duncan]

What you seek is not possible, in any general sense.

The concept of "what is a database" varies too greatly between different DBMS 
products, even if you restrict yourself to SQL DBMSs.  Loosely the question is 
like asking for a "list of websites".


If what you want is to have a product or service that is DBMS-agnostic, you need 
to more precisely define the criteria for what you want a list of.


Here are some more specific definitions of a database:

- A DBMS process service endpoint or embedded library that you can connect to as 
a client.


- A file or file collection on disk that is a logical database.

- A collection of database table variables that can be addressed within a common 
database query.


- A collection of database table variables that can be collectively subject to a 
common ACID transaction, changes to the collection succeed or no-op as a whole.


- A logical namespace for database table variables.

For some DBMS engines several of the above things are one and the same, while 
with others they are not.


-- Darren Duncan

On 2018-11-15 6:09 PM, Daniel Kasak wrote:

Hi all.

I have a project that has to support pretty much every major database around, 
plus a number of more esoteric "big data" platforms as well. Until now, I've had 
a bunch of methods, implementing fetching databases, schemas, tables, etc, per 
database, with database-specific queries ( eg against information_schema ).


Some of the newer databases I'm trying to support have very little 
documentation, and in some cases no apparent way of fetching in the schema via 
SQL. I've had a conversation with one of the tech support people for a DB 
product who said that there were generic ODBC functions we can call for this 
kind of thing. Is this the case?


I've done quite a bit of search, but can't find any docs that mention fetching 
*databases* - either in ODBC docs or in Perl/DBI docs. The closest I've found 
that *might* have worked was DBI's tables() method:

https://metacpan.org/pod/DBI#tables
   ... but:

- this doesn't work in cases where there is a separation between hierarchies at 
the database level ( eg postgres only lists schemas and tables in the current 
database )

- this isn't returning *anything* for me with any of the ODBC drivers I've tried

So is it possible to retrieve a list of databases in a generic way? Failing 
that, assuming that there *is* some ODBC call ( as suggested by one DB vendor ) 
that I can use, is there some way of calling it from Perl with DBD::ODBC?


Thanks :)

Dan

RE: Generic way of fetching a list of databases

[Fennell, Brian]

Closest thing that I know of is INFORMATION_SCHEMA and the 
INFORMATION_SCHEMA.TABLES pseudo table.
INFORMATION_SCHEMA is a standard across many databases.  One Database that 
doesn’t comply is Oracle (but it can be installed by the DBA as an Add-on) for 
Oracle select from ALL_TABLES (or DBA_TABLES if you have administrative 
privileges or “read any data dictionary” privileges).
Every database that I have ever worked with has some way to list the tables as 
if there were a “table of tables” somewhere.
If you are working with a database where cannot select from 
INFORMATION_SCHEMA.TABLES than just search google/bing for the name of that 
database and “INFORMATION_SCHEMA.TABLES” to find the equivalent for your 
odd-ball database.
Put the whole thing in a subroutine that returns the same kind of data 
structure as a SELECT, adding perhaps an if/the/else structure to handle the 
different kinds and you should at least be able to keep all the messiness in 
one subroutine.


From: Scott Webster Wood via dbi-users 
Sent: Friday, November 16, 2018 10:26 AM
To: dbi-users@perl.org; Daniel Kasak 
Subject: Re: Generic way of fetching a list of databases

convert to 'rest'ful JSON?

 Barack-O-phobia: The fear of politicians who think (more) government is 
the solution to every problem.


On Thursday, November 15, 2018, 9:09:44 PM EST, Daniel Kasak 
mailto:d.j.kasak...@gmail.com>> wrote:


Hi all.

I have a project that has to support pretty much every major database around, 
plus a number of more esoteric "big data" platforms as well. Until now, I've 
had a bunch of methods, implementing fetching databases, schemas, tables, etc, 
per database, with database-specific queries ( eg against information_schema ).

Some of the newer databases I'm trying to support have very little 
documentation, and in some cases no apparent way of fetching in the schema via 
SQL. I've had a conversation with one of the tech support people for a DB 
product who said that there were generic ODBC functions we can call for this 
kind of thing. Is this the case?

I've done quite a bit of search, but can't find any docs that mention fetching 
*databases* - either in ODBC docs or in Perl/DBI docs. The closest I've found 
that *might* have worked was DBI's tables() method:
 
https://metacpan.org/pod/DBI#tables
  ... but:

- this doesn't work in cases where there is a separation between hierarchies at 
the database level ( eg postgres only lists schemas and tables in the current 
database )
- this isn't returning *anything* for me with any of the ODBC drivers I've tried

So is it possible to retrieve a list of databases in a generic way? Failing 
that, assuming that there *is* some ODBC call ( as suggested by one DB vendor ) 
that I can use, is there some way of calling it from Perl with DBD::ODBC?

Thanks :)

Dan



The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

Re: Generic way of fetching a list of databases

[Scott Webster Wood via dbi-users]

convert to 'rest'ful JSON?
 Barack-O-phobia: The fear of politicians who think (more) government is 
the solution to every problem. 

On Thursday, November 15, 2018, 9:09:44 PM EST, Daniel Kasak 
 wrote:  
 
 Hi all.
I have a project that has to support pretty much every major database around, 
plus a number of more esoteric "big data" platforms as well. Until now, I've 
had a bunch of methods, implementing fetching databases, schemas, tables, etc, 
per database, with database-specific queries ( eg against information_schema ).
Some of the newer databases I'm trying to support have very little 
documentation, and in some cases no apparent way of fetching in the schema via 
SQL. I've had a conversation with one of the tech support people for a DB 
product who said that there were generic ODBC functions we can call for this 
kind of thing. Is this the case?
I've done quite a bit of search, but can't find any docs that mention fetching 
*databases* - either in ODBC docs or in Perl/DBI docs. The closest I've found 
that *might* have worked was DBI's tables() method: 
https://metacpan.org/pod/DBI#tables
  ... but:
- this doesn't work in cases where there is a separation between hierarchies at 
the database level ( eg postgres only lists schemas and tables in the current 
database )- this isn't returning *anything* for me with any of the ODBC drivers 
I've tried
So is it possible to retrieve a list of databases in a generic way? Failing 
that, assuming that there *is* some ODBC call ( as suggested by one DB vendor ) 
that I can use, is there some way of calling it from Perl with DBD::ODBC?
Thanks :)
Dan  

Re: Generic way of fetching a list of databases

[pali]

On Friday 16 November 2018 13:09:21 Daniel Kasak wrote:
> Hi all.
> 
> I have a project that has to support pretty much every major database
> around, plus a number of more esoteric "big data" platforms as well. Until
> now, I've had a bunch of methods, implementing fetching databases, schemas,
> tables, etc, per database, with database-specific queries ( eg against
> information_schema ).
> 
> Some of the newer databases I'm trying to support have very little
> documentation, and in some cases no apparent way of fetching in the schema
> via SQL. I've had a conversation with one of the tech support people for a
> DB product who said that there were generic ODBC functions we can call for
> this kind of thing. Is this the case?
> 
> I've done quite a bit of search, but can't find any docs that mention
> fetching *databases* - either in ODBC docs or in Perl/DBI docs. The closest
> I've found that *might* have worked was DBI's tables() method:
>  https://metacpan.org/pod/DBI#tables
>   ... but:
> 
> - this doesn't work in cases where there is a separation between
> hierarchies at the database level ( eg postgres only lists schemas and
> tables in the current database )
> - this isn't returning *anything* for me with any of the ODBC drivers I've
> tried
> 
> So is it possible to retrieve a list of databases in a generic way? Failing
> that, assuming that there *is* some ODBC call ( as suggested by one DB
> vendor ) that I can use, is there some way of calling it from Perl with
> DBD::ODBC?
> 
> Thanks :)
> 
> Dan

Hi! If you want to list all databases then use DBI's data_sources method:
https://metacpan.org/pod/DBI#data_sources

That method is there exactly for this purpose to list all databases in
DBI's DSN format. If you are not interested in full DSN, just extract
database... See example:

$ perl -MDBI -E 'say $_ foreach map { $_ =~ /^DBI:[^:]*:(.*)/i } 
DBI->data_sources("mysql")'
information_schema
mysql
performance_schema
test
test2
test3
test4