FDO RFC 68 - Allowing PostgreSQL provider to support case insensitive database
This page contains a request for comments document (RFC) for the FDO Open Source project. More FDO RFCs can be found on the RFCs page.
|RFC Template Version||(1.1)|
|Submission Date||September 11, 2013|
|Last Modified||Jackie Ng: 11 Feb 2018|
|Assigned PSC guide(s)|
This RFC is for enhancing the PostgreSQL provider to support case insensitive PostGreSQL database.
Actually all internal SQL statement made by the postgreSQL provider are double quoted, this means that it only support case sensitive database. When you have an existing application that use FDO as a generic database engine , you are forced to change your application to surround all your table name and column name with double quotes ( on native SQL staments ) and to use the same Tble/Column Name case as defined in the database. You have to do this only when dealing with a PostGreSQL database. FDO is a great tool because you can use the same application to support many databases(Oracle, Sqlite, Sqlserver,PostgreSQL...) only by changing the connection string. The purpose of this RFC is to make the provider supporting case insensitive postgreSQL database along with the actual case sensitive behavior.
We propose to add a new optional connection parameter : CaseSensitive
example : dataStore=myDatabase;Service=localhost;Password=mypwd;Username=postgres;CaseSensitive=FALSE
If not set:
the default behavior is CaseSensitive=TRUE, so there will be no impact on existing application.
When case sensitive is set to TRUE:
No double quotes would be added to internal sql satement.
Table creation will be made with lowercase table name and lowercase column name, thus creating a case insensitive table.
Find all the places where double quotes are added and modify the code to test the CaseSensitive parameter.
Find all the places where schema element (table and column name) are looked up using case sensitive function like wcscmp and modify the code to test the CaseSensitive parameter
Geomap Infrastruction Solutions to provide resources/funding.