Table and column names are being forced to lower case

[paddyinpdx]

The connector is forcing table and column names to lower case in lines 610-635 of /lib/postgresql.js. I don't think it should doing this. Suggest removing toLowerCase() calls therein.

Please refer to https://groups.google.com/forum/#!topic/loopbackjs/sHmDCTqgzI0.

[jmls]

I've just hit this. The bug is not the lowercase, but that the postgres connector should be mapping the propertyname to the column name and doesn't seem to be doing so.

for example

{
 "name": "Shippers",
 "options": {
  "idInjection": false,
  "postgresql": {
   "schema": "public",
   "table": "shippers"
  }
 },
 "properties": {
  "shipperid": {
   "type": "Number",
   "required": true,
   "length": null,
   "precision": 16,
   "scale": 0,
   "id": 1,
   "postgresql": {
    "columnName": "ShipperID",
    "dataType": "smallint",
    "dataLength": null,
    "dataPrecision": 16,
    "dataScale": 0,
    "nullable": "NO"
   } [snipped]

when I try and access the data, I get the error

{
  "error": {
    "name": "error",
    "status": 500,
    "message": "column \"shipperid\" does not exist",
    "length": 101,
    "severity": "ERROR",
    "code": "42703",
    "position": "8",
    "file": "parse_relation.c",
    "line": "2892",
    "routine": "errorMissingColumn",
    "stack": "error: column \"shipperid\" does not exist\n    at Connection.parseE (/usr/local/lib/node_modules/loopback-connector-postgresql/node_modules/pg/lib/connection.js:534:11)\n    at Connection.parseMessage (/usr/local/lib/node_modules/loopback-connector-postgresql/node_modules/pg/lib/connection.js:361:17)\n    at Socket.<anonymous> (/usr/local/lib/node_modules/loopback-connector-postgresql/node_modules/pg/lib/connection.js:105:22)\n    at Socket.emit (events.js:107:17)\n    at readableAddChunk (_stream_readable.js:163:16)\n    at Socket.Readable.push (_stream_readable.js:126:10)\n    at TCP.onread (net.js:529:20)"
  }
}

so what should be happening is that the connector should map shipperid=>ShipperID as defined in the columnName options of postgres

[jmls]

ok, found where the problem is happening

in
loopback-connector/lib/sql.js, lines 78 there is this code

SqlConnector.prototype.column = function (model, property) {
  var name = this.getDataSource(model).columnName(model, property);
  var dbName = this.dbName;
  if (typeof dbName === 'function') {
    name = dbName(name);
  }
  return name;
};

I don't know the purpose of dbName(name), but name is returned correctly (shipperid->ShipperId) and then because dbName is a function, it gets called, and name is reset to shipperid and returned

this also happens in the idColumn function

[jmls]

bump - anyone got any comments ? This issue makes my postgres database unusable with strongloop

[lambrojos]

Same problem here

[tuomastanner]

Hi, I created a fork that fixes the issue at: https://github.com/W3GroupFinland/loopback-connector-postgresql

If you want, you can include it in your package.json by replacing the connector line with:
"loopback-connector-postgresql": "git://github.com/W3GroupFinland/loopback-connector-postgresql.git#master",

As I understand it, loopback converts all db column names to lowercase when accessing. Since the names are in mixed case, the db column is not found. I simply removed the lowercase conversion.

I also set the default datatype for Strings to TEXT without any arbitrary limits. This is based on: http://stackoverflow.com/questions/4848964/postgresql-difference-between-text-and-varchar-character-varying

[sjschubert]

Nice work @paddyinpdx/@tuomastanner. Any chance of a PR so the loopback folks have it on the radar?

[sjschubert]

I did a bit of an archeological expedition and found that this issue has been partially fixed in commit
d08dad7#diff-ac54005f9bbf753aa45ceebc4a032628, the table name is still being lowercased, although that seems to work with the naming scheme now. Maybe this issue should be closed?

[mirow]

No, the issue is not fixed. The poorly named method PostgreSQL.prototype.dbName() is called not just for table names, but also for column names.
https://github.com/strongloop/loopback-connector/blob/master/lib/sql.js#L161-L164

[raymondfeng]

Please note if a DB column name is provided (such as {myProp: {postgresql: {column: 'MyCol'}}), the casing will be preserved (MyCol). See https://github.com/strongloop/loopback-connector/blob/master/lib/sql.js#L153-L158. If no customization is made, we continue to enforce default cases for the column name. I would consider the issue has been fixed.

[tuomastanner]

In that case the behavior between databases is inconsistent, since loopback-connector-mssql preserves the case for column names. I personally see no upside to lowercasing the column names. It only causes inconsistency and is a source for potential bugs.

BTW if the models id field has a different column name from the field name, upserts an updates brake, since existence query is done with field name instead of column value.

[Sogl]

+1
I found the same problem with mysql loopback connector.

[Sogl]

@raymondfeng Any chance to merge? So much time has passed and this bug still exists and issue open.

[emresebat]

Thanks for the information @tuomastanner, I've also created a fork that used lowercase+underscore naming and updated my package.json.

By the way for me the correct way to include your forked repo is

"loopback-connector-postgresql": "git+https://:x-oauth-basic@github.com/<USER_NAME>/<REPOSITORY_NAME>.git",