Case-confusion in JDBC insert

[mfprimo]

LDAP is case-insensitive in attribute names, while some implementations/configurations of SQL db are case sensitive in field names. The current JDBC insert is a little bit messy about this topic. Specifically:

• field and table names are converted to lowercase, which can produce errors in database access
• attribute names are managed as-is, which produce errors i mapping from/to field names

As example, if you modify the default DB sample to use:

server.DBEmployees.DB.config.rdn=employeeNumber server.DBEmployees.DB.config.mapping=employeeNumber=ID,givenName=firstname,sn=lastname,mail=email,l=location

it whille produces the query:

SELECT * FROM (SELECT ID,firstname,lastname,email,location FROM employees) X WHERE null IN (SELECT id FROM employees) ORDER BY null

where the the "ID" field becomes "id" in the second sub-select and "employeeNumber" is missing in the mapping table and it is converted to the string "null"... obviously no entry is returned.

I can try to fix this issue, but I need an hint on the strategy that you prefers: 1) fix every code-point to handle db field names verbatim as in configuration and LDAP attribute names as case-insensitive or 2) switch to a case-insensitive map instead of HashMap and leave the code as much as possible untouched An half-way strategy could be possible.

[mlbiam]

1. You are correct it should be consistent
2. I generally use lowercase for everything when deploying the db insert (this is a lazy fix, but has worked for me)
3. have you seen a use case where the current implementation doesn't work? I've used it with SQL Server and MariaDB. Especially with the ability to quote the field names I haven't run into any problems so far

Ideally, the way case sensitivity should be handled is however its configured in the properties is how the SQL should be generated. So if its employeeNumber in the properties it should be employeeNumber in the SQL. I'm trying to remember why i originally forced the attribute names to lower case, and now I can't remember. I think its because a db was always returning lowercase names (hsqldb?) but i honestly don't remember.

[mfprimo]

By default, PostgreSQL forces the names to lowercase, while HSQLDB converts them to uppercase. But if you try in HSQLDB:

CREATE TABLE employees (id int NOT NULL,
  "firstname" varchar(50) default NULL,
  "FirstName" varchar(50) default NULL,
  lastname varchar(50) default NULL,
  email varchar(50) default NULL,
  location varchar(50) default NULL,
  PRIMARY KEY  (id)
) 

the resulting table will have two different columns "firstname" and "FirstName". Morover, the basic query

SELECT FIRSTNAME
FROM PUBLIC.EMPLOYEES;

won't work, because it requires the correct case field name surrounded by "". Thus even the double quote must be preserved in the SQL string.

The query:

SELECT "firstname", "FirstName"
FROM PUBLIC.EMPLOYEES;

will return two column firstname and FirstName. I think the correct solution is handle column names verbatim and attribute mapping case-insensitive on the LDAP-side.

[mlbiam]

Sorry for the delayed response. Thats probably where I forced everything since I did most of the development against HSQLDB and Derby back in the day. You're right, it should just be whatever is put in the config. If you're interested in helping out here I'd welcome a pull request.