Make simpletest_get_like_tables() work in MySQL and PostgreSQL

Committed.

Thanks.

This breaks PostgreSQL support, as SHOW is something completely else there.

Edit: Or, I guess, it's not something completely different, but it doesn't support SHOW TABLES any way.

How's this?

Isn't this just the way it was before the patch from #1? Ie., it won't work on MySQL < 5? I'm only using MySQL 5 and PostgreSQL (8.3), so it wouldn't bother me a whole lot; but if the module aims to support the same software setups as Drupal core...

Very similar to previous way, exactly the same SQL query.

I'm not sure what else to do unless someone has some SQL magic...

This module already requires PHP 5, which is above Drupal 6 requirement...I think it is fair to require MySQL 5 as it is for testing and should be on a developers machine anyway.

Then again Drupal 6 doesn't tend to support Postgres that well....so which do we choose?

Well. SimpleTest 6.x-2.x is aiming to be compatible with the test module in Drupal 7, which has various requirements like PHP >= 5.2 && ( MySQL >= 5 || PostgreSQL >= 8.3 ). So... it might be best/easiest to simply set the same requirements for SimpleTest 6.x-2.x?

I very much agree...just making sure everyone was fine with that. So commit patch?

Fine with me, but not with carlos8f. Perhaps you could take a quick census in #drupal or something?

Committed revert and added node in README.txt.

Reopening.

You've still got no Postgres compatibility, so cleaning the environment doesn't work for Postgres users.

Attached a patch to provide equivalent functionality on postgres (switching on $db_type). Other databases may need to be considered also though.

It's a pity the database abstraction layer can't provide a list of tables!

I thought that information_schema.tables was cross-compatible?? At least that is what I was told.

http://www.postgresql.org/docs/7.4/static/infoschema-tables.html

On second look, yes it should be, but it's different.

Here's a row that should be returned:

-[ RECORD 3 ]----------------+----------------------------------------
table_catalog                | drupal-signon
table_schema                 | public
table_name                   | simpletest317254variable
table_type                   | BASE TABLE
self_referencing_column_name |
reference_generation         |
user_defined_type_catalog    |
user_defined_type_schema     |
user_defined_type_name       |
is_insertable_into           | YES
is_typed                     | NO
commit_action                |

The module is checking if table_schema = $database, but as you can see, in Postgres table_schema actually points to the schema, not the database. The table_catalog points at the database name. This is PostgreSQL 8.3.

Have a look at how db_find_tables() does this in Drupal 7 (and especially at buildTableNameCondition() in includes/database/schema.inc for how it puts together this query. It seems to match table_catalog to the database name.

Also, another bug in this code is that simpletest_get_like_tables() prefixes the $base_table in its search with $db_prefix, but if the site is actually using db prefixing, this will fail because drupal_web_test_case.php overrides $db_prefix to simpletest$uniqid (without preserving any configured prefix), so the simpletest temporary tables are actually not pre-prefixed at all, so applying the site's $db_prefix (if not '') in simpletest_get_like_tables() will cause it not to match anything.

Attaching a patch against simpletest.test to self-test the database functionality. This patch should work in either D7 using db_find_tables() or in D6 using simpletest_get_like_tables(), whichever is available.

I was trying to call simpletest_clean_environment(), but of course calling that from within a unit test causes the temporary environment that the test is executing under to be destroyed (chicken/egg problem).

Hopefully this will allow you to check this works on all databases.

I confirm that SHOW TABLES is not ANSI SQL, and fails in PostgreSQL.

I added (manually, because patch failed on v 1.1.2.11 2009/04/23 05:39:51)
simpletest.test.checkdbcleanup.patch test and launched it again in may pgsql localhost installation.

Unfortunately it fails, it several points for me:

* Exception: pg_query() [function.pg-query]: Query failed: ERROR: relation "simpletest705219cache" does not exist
Warning database.pgsql.inc 151 _db_query()

* Exception: query: UPDATE simpletest705219cache SET data = '.....', created = 1243442532, expire = 0, headers = '', serialized = 1 WHERE cid = 'schema'
User warning database.pgsql.inc 172 _db_query()
(consequence of previous !)

* Assertion failure: "Checking for any missed tables that should have been cleaned up"
Other simpletest.test 318 SimpleTestFunctionalTest->testEnvironmentCleanup()

Disabling testEnvironmentCleanup() makes the whole block.test succeed again.

RockyRoad, are you on D6 or D7?

For my test case to succeed on D6, you either have to apply my (slightly bad) patch from above for simpletest_get_like_tables(), or fix it in the way I described later, by changing "table_schema" to "table_catalog" in that function.

Would anyone be interested in me rolling a new patch for the Postgres fix + the test case in the way I described?

hi neilnz,

I'm running D6.

I must confess I didn't really study your code yet.
I came to that bug ... to be sure SHOW TABLES wouldn't come up again (I found it, or similar, in several other modules).

But if I have time (and knowledge, I'm quite new to drupal, though having programming experience) I'd be glad to help you polishing your patch.

Adding this to our code sprint for fixing

Rolled a patch to change the use of information_schema.tables to be cross database

Committed.