unrecognized configuration parameter "tables"

[c33s]

php app/console propel:table:drop --force

leads to

    [Propel] Exception caught
    SQLSTATE[42704]: Undefined object: 7 ERROR:  unrecognized configuration parameter "tables"

only found http://stackoverflow.com/questions/7367077/symfony2-how-to-make-a-pgsql-database-reverse-enginering-process-with-propelbun

config error or propel bug?

db tested: postgres, sqlite

[willdurand]

The issue here is that we run SHOW TABLES to determine tables but it doesn't work with PostgreSQL.

[c33s]

hmm i see,

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

Conclusio

so if we want to fix this we have to refactor the following section https://github.com/propelorm/PropelBundle/blob/1.1/Command/TableDropCommand.php#L85

for me this is not a PropelBundle bug, but a Propel bug, because Propel, as good DBAL, should handle this.

if i use propel i just want to call xxx->getTableNames() and don't want to care how it works internally or be confronted to handle it with hacks by myself on every location i want to use show tables.

[willdurand]

Well. Does a DBAL need database introspection at runtime? I'm not sure. It's better if you focus on performance ;)

[c33s]

Does a DBAL need database introspection at runtime?

my answer would be yes. if i need performance i still can call plain sql. but if i simply want it to work, i would need it

[willdurand]

This feature was more an "extra" command than a really required feature. I don't plan to work on that at the moment, but we really need to find a better solution.

[c33s]

what do you think of having such a feature?

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

if you tell me where to add, i can have a look at it and try forging a PR

[willdurand]

Let me think about that :) Oh, and please open an issue in the Propel2 tracker as a Feature Request.

[stof]

for reference, here is the query used by Doctrine DBAL for Postgres: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/PostgreSqlPlatform.php#L193
and here is the Sqlite one: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/SqlitePlatform.php#L345

[marcj]

We have already a parser since years that support this, although especially Postgres and SQLite is only with v2 really stable. I've no idea why MySQL is still in v2 https://github.com/propelorm/PropelBundle/blob/2.0/Command/TableDropCommand.php hardcoded down to v1. We have for all supported vendors a reverser schema parser:

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

$tables = $database->getTables();