---

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.

Status

RFC Template Version	(1.1)
Submission Date	September 11, 2013
Last Modified	Jackie Ng: 11 Feb 2018
Author	Bruno Scott
RFC Status	adopted
Implementation Status	completed
Proposed Milestone	3.9.0.0
Assigned PSC guide(s)
Voting History
+1
+0
-0
-1

Overview

This RFC is for enhancing the PostgreSQL provider to support case insensitive PostGreSQL database.

Motivation

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.

Proposed Solution

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.

Implications

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

Test Plan

Actual unit test should be run as is without the CaseSensitive parameter It could be useful to re run the test with the CaseSensitive parameter set to FALSE

Funding/Resources

Geomap Infrastruction Solutions to provide resources/funding.