Postgres database schema generation not maintaining case sensitivity of names and not including the joins based on the referential integrity

[dgkm]

Describe the bug
When generating the schema in the Playground or CLI selecting multiple table names on Postgres is not generating the schema file having the joins automatically based on the referential integrity/foreign key columns.

In addition, schema is not adding quotes to maintain case sensitivity in both the Table name and column names.

To Reproduce
Create tables and columns having case sensitive names with double quotes.

CREATE TABLE "public"."Account"
(
    "id" serial primary key,
    "departmentId" integer REFERENCES "public"."Department"("id") ON DELETE CASCADE,
    "firstName" text,
    "lastName" text,
    "dateOfBirth" date,
    "email" text,
    "status" text,
    "createdAt" timestamptz not null default now(),
    "updatedAt" timestamptz not null default now(),
    "archiveAt" timestamptz not null default now()
)

CREATE TABLE "public"."Department"
(
    "id" serial primary key,
    "name" text,
    "description" text,
    "createdAt" timestamptz not null default now(),
    "updatedAt" timestamptz not null default now(),
    "archiveAt" timestamptz not null default now()
)

Steps to reproduce the behavior

1. Connect cube.js to 'Postgres' database
2. Go to 'Playground'
3. Click on 'Database or Tables'
4. Select all the 'Tables' then click on generate schema
5. Click on 'Schema' and open the Schema file

there you see no entries for joins property.

Expected behavior

1. to maintain the case sensitivity of the table and column names by adding single quotes to the name property values in the schema.

E.g., "Account" for table name and "accountId", "firstName", "lastName", "dataOfBirth" for column names.

2. To populate the joins automatically primary, referential integrity columns in a mixed case sensitivity setup.

E.g., having table name as "Account" and reference column as "accountId" rather than "AccountId".

Version:
Latest

[paveltiunov]

@dgkmurthy Hey Keshava! Thanks for posting this! Yep. Here we should have lowercase comparison:
https://github.com/statsbotco/cube.js/blob/a53f0136e3efd2c4cf0ad52bec033319e6966438/packages/cubejs-schema-compiler/scaffolding/ScaffoldingSchema.js#L155

[dgkm]

Fixed in #122

[craigharman]

This still seems to be an issue in v0.18.3 with time dimensions and Postgres.
Column name "startTime" gives Error: column tableName.starttime does not exist
Changing the column name to all lowercase fixes the issue.

[paveltiunov]

@craigharman Hey Craig! Could you please share generated schema?

[craigharman]

Please see below. Note changing startTime to start_time or starttime and updating database column name accordingly fixes the error.

cube(DeviceConsumption, { sql: SELECT * FROM public."DeviceConsumption"`,

joins: {
Devices: {
sql: ${CUBE}.device_id = ${Devices}.id,
relationship: belongsTo
},

PricingPeriods: {
  sql: `${CUBE}.pricing_period_id = ${PricingPeriods}.id`,
  relationship: `belongsTo`
}

},

measures: {
count: {
type: count,
drillMembers: [id, startTime, endTime]
}
},

dimensions: {
id: {
sql: id,
type: number,
primaryKey: true
},

startTime: {
  sql: `start_time`,
  type: `time`
},

endTime: {
  sql: `end_time`,
  type: `time`
}

}
});`