Description: ------------ Two errors in Reverse/sqlite.php: - index/constraint name can have upper case letters - $result->free() is bogus: $result does not exist here and there's nothing to free Patch: www.markwiesemann.de/php/MDB2/MDB2_Reverse_sqlite.patch

I removed the bogus $result->free(). For the other fix: So SQLite matches case sensitive? Anyways .. the lower should only be done given the relevant portability mode. I will need to expand your patch.

> So SQLite matches case sensitive? Yes. I have an index named 'test2_ID_idx' which is not matched by the old query ('test2_id_idx' is returned by listTableConstraints).

Lukas, see how I solved similar problem in createDatabase I believe ... it needed some fubar fix because of case issues + the mode part not being what needed and yata yata.

Without being an expert about the portability feature, here is a new patch suggestion: www.markwiesemann.de/php/MDB2/MDB2_Reverse_sqlite2.patch

Hmm I am still not sure how to deal with this situation. One problem is that you are dealing with a legacy database but with settings that do not go well with those. Which means that you are using mixed cases even though you have set the default fixed case portability option. If you would have disabled it, then you would not have any issues. However it may still be worthwhile to fix the case of identifiers in all manager and reverse methods just like we do in ibase. In that case it would probably be sufficient to just force either both the identifier parameter and field name to either upper or lower. Anyways I am "promoting" this to be an MDB2 level bug. Lorenzo what is your take on this? Maybe we should only force a specific identifier case if people enable the fix case option and leave things untouched otherwise?

Well, I'm not sure about what to do. Your last suggestion is probably the best thing we can do. About ibase, that's a bit different. Interbase/Firebird by default uses uppercase identifiers, and is case-insensitive, unless you quote them, in which case they become case-sensitive. That's why I ended up uppercasing them by default and ignoring the quotes. Things would get really, really, really hairy otherwise.

I just compared DB's and MDB2's defaults. DB uses as default DB_PORTABILITY_NONE, while MDB2 uses MDB2_PORTABILITY_ALL as default value. I'm unsure - maybe I could just turn set it to MDB2_PORTABILITY_NONE in DB_Table (and having the same values with DB and MDB2 then) and restore it after getting the index/constraint information?

OK, I think in order to be consistent fix case needs to also fix case when calling the manager and reverse modules. However it should be sufficient to simply just lower case both the identifier parameter and identifier field value. As for ibase: I presume you only need to fix the case when you read the information schema and it normal queries ibase internally uppercases things? In that case I think our current implementation is correct.

I strongly encourage MDB2 to not automatically delimit identifiers or change the case of identifiers when creating or requesting information from identifiers. The case changing should only happen on the way to the PHP script, after the data comes out of the database.

This is entirely optional .. both identifier quoting and case "fixing" is controlled by options. the idea is to assist in making things portable, but not to force this onto the user.

Ok, some more investigation into the problem. The SQL standard defines databases to be case insensitive for identifiers unless they are quoted. PGSQL and Oracle seem to follow this. Interbase/Firebird also follow this with the exception of the information schema where you need to uppercase everything. Now we have issues with the following: MySQL only if you set the lower_case_table_names system variable. We could work around this using the information schema, because contrary to the SHOW statements there you can use standard SQL functions like LOWER() or UPPER(). But thats mysql5 only and too much work at this point. We also have the noted issues with SQLite. Which I will fix in a similar manner to how you suggested.