Table field naming convention issues

[hantsy]

• Helidon Version:2.0.m1
• Helidon SE
• JDK version: AdaptOpenJDK 11
• OS: Windows 10
• Docker version (if applicable):

I used PostgreSQL in Docker for tests.

When I defined the createdAt as TIMESTAMP like this.

createdAt TIMESTAMP NOT NULL DEFAULT LOCALTIMESTAMP ,

Then I tried to fetch this field via DbRow, I found the field name is createdat, not createdAt. I am not sure this is the design purpose or an issue.

(I have changed my codes to use created_at as the field name finally to avoid this issue)

[Tomas-Kraus]

This can be affected by too many things, not just Helidon. I don't have access to your DB so please verify few things first.

• check whether database columns are really case-sensitive. See https://blog.xojo.com/2016/09/28/about-postgresql-case-sensitivity/
  What does describe on the table show?
• Helidon DB client shall work exactly the same way as your JDBC driver. Please try some minimal test with JDBC prepare statement and check ResultSet:

ResultSet rs = statement.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
String name = metaData.getColumnLabel(i); // where i is index of your column

If metaData.getColumnLabel(i) returns case-sensitive names and Helidon does not, it's bug in Helidon.

[hantsy]

This problem should be caused by PostgreSQL itself. I know camel-case naming maybe not a good option for RDBMS.

[m0mus]

@hantsy It's worth investigating it. @Tomas-Kraus can you simulate it based on the sample code here https://blog.xojo.com/2016/09/28/about-postgresql-case-sensitivity/ and make sure that DB Client doesn't have problems with case sensitivity.

[hantsy]

I confirmed this is a PostgreSQL issue(maybe convention in PostgreSQL).

I defined a table like this.

CREATE TABLE IF NOT EXISTS Person(
ID UUID DEFAULT uuid_generate_v4() ,
FullName VARCHAR(255) NOT NULL,
Email VARCHAR(255) NOT NULL,
createdAt TIMESTAMP NOT NULL DEFAULT LOCALTIMESTAMP ,
PRIMARY KEY (ID)
);

And create a jdbc sample to connect the database and println the metadata of table Persone.

public class JdbcMetadataSample {
    private final String url = "jdbc:postgresql://localhost/test";
    private final String user = "user";
    private final String password = "password";

    public void printPersonTableMetadata() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("Connected to the PostgreSQL server successfully.");

            var statement = conn.prepareStatement("select FullName, EMAIL, createdAt from PERSON");
            ResultSet rs = statement.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();

            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                String label = metaData.getColumnLabel(i);
                String name = metaData.getColumnName(i);
                System.out.println("label:" + label);
                System.out.println("name:" + name);
            }

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public static void main(String[] args) {
        JdbcMetadataSample app = new JdbcMetadataSample();
        app.printPersonTableMetadata();
    }
}

It prints the following results.

Connected to the PostgreSQL server successfully.
label:fullname
name:fullname
label:email
name:email
label:createdat
name:createdat

The column name and table name in the SQL are case-insensitive, and in the JDBC metadata, all become lowercase.

[ljnelson]

That's right; DatabaseMetaData can help you here.

[Tomas-Kraus]

@hantsy It's worth investigating it. @Tomas-Kraus can you simulate it based on the sample code here https://blog.xojo.com/2016/09/28/about-postgresql-case-sensitivity/ and make sure that DB Client doesn't have problems with case sensitivity.

Integration tests are running with MySQL. But maybe it's time to add few more databases.

[Tomas-Kraus]

Closing this. Created #1518 to setup PgSQL integration tests.