Add getTableNames() in the DBAL of Propel

[c33s]

the propelbundle uses the SHOW TABLES to get all table names, which is mysql (only). it won't work in postgresql and in sqlite.

propelorm/PropelBundle#164

sometimes you get in the situation, that you need to get the table names in your application, this should be handled by the DBAL = Propel

would be good to have something like

$connection->getTableNames();

i think it's not that difficult to research it for all databases. where should the $xxx->getTableNames() be added? to the connection? $con->getTableNames()?

Postgres

postgres equivalent for SHOW TABLES is

SELECT table_name FROM information_schema.tables

SELECT tablename FROM pg_catalog.pg_tables

the problem with both commands are, that they are also listing internal table schemas( pg_catalog and infromation_schema).

so you can hardcode a little bit like this:

SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname='public'

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'

but then you force the user to use the public schema. how is the support of postgres schemas in propel? if there is any schema support we maybe can use it for this.

infos partly from http://stackoverflow.com/questions/769683/show-tables-in-postgresql

what also would be possible is to make something like this:

SELECT tablename FROM pg_catalog.pg_tables WHERE tableowner='my_symyony_db_user'

all the system tables are owned by the postgres user, so sleecting by my_symyony_db_user should work. also by selecting by "not" works:

SELECT tablename FROM pg_catalog.pg_tables WHERE tableowner != 'postgres'

Sqlite

for sqlite the command is (tested with sqlitemanager in firefox on windows)
SELECT name FROM sqlite_master WHERE type='table'

general command:
SELECT * FROM dbname.sqlite_master WHERE type='table';

http://stackoverflow.com/questions/82875/how-do-i-list-the-tables-in-a-sqlite-database-file

[danwit]

+1 for this

[willdurand]

👍 on this, but this is not a priority yet.

[hypervalley]

Incredible this is not a priority! I'm almost changing into Doctrine for this question.

[jaugustin]

@valle6 You can provide a pull request for that if you really need it.

[marcj]

Mh, I don't see the benefit with that method here. We've already a powerful schema parser for mssql, mysql, oracle, pgsql and sqlite and should thus not write stuff twice.

I think, improving the interface of SchemaParserInterface would be better. E.g. Table[] getTables(), Columns[] getColumns(Table $table) etc. That wouldn't be much work and you can then reflect the whole database schema at PHP's side.

[marcj]

So, getTableNames would actually the same as:

$database = new Database();
$parser = new MysqlSchemaParser();
$parser->setConnection($conn);
$parse->parse($database);

$tables = $database->getTables();

So, this is already possible with all databases and even more powerful as you have all information of the whole database, with its tables, columns, foreignKeys, indices, etc.

Indeed it's a bit more to write, but a shortcut at $conn would be fine to me.
($con->readDatabase():Database)

Feel free to send a PR. :-)

[marcj]

Ok, it's a easy-pick now, since its really only a method that fires those ~5 lines of code above to have a easier api.

[sitilge]

@marcj A late call here. I still don't get it why the following is not working

$map = new DatabaseMap('default');
$tables = $map->getTables(); //yields an empty array

[c33s]

as i read this issue again i notice:

...
$parser = new MysqlSchemaParser();

from my point of view this is a no go, if i want to get the table names i don't want to hardcode which database i am using also from the DX it should be as easy as '$connection->getTableNames();'

[marcj]

Actually needless to say, but for you: Of course it's not hardcoded, but connection dependent.