search

loopbackio / loopback-connector-sqlite3

SQLite3 Connector for LoopBack
Other
13 stars 22 forks source link

Select statement does not return correct value for columns whose name contains uppercase characters #32

Closed johnsoftek closed 7 years ago

johnsoftek commented 7 years ago

Bug or feature request

Description of feature (or steps to reproduce if bug)

  1. Define a table with a column containing an uppercase character.
  2. Select the column from the table
  3. Returned value incorrect: undefined, false etc.

The User table is a good example: emailVerified is always false; verificationToken is always undefined.

To be fair, I don't know whether this issue is a bug with this connector or with loopback-connector.

In loopback-connector/lib/sql.js, when retrieving column properties from query row data in fromRow function

    var columnName = this.column(model, p);
    // Load properties from the row
    var columnValue = this.fromColumnValue(props[p], rowData[columnName]);

this.column calls dbName function in loopback-connector-sqlite3/lib/sqlite3, which converts property name to lower case e.g. emailVerified to emailverified. But the rowData returned by Sqlite3 has correct case, so rowData[columnName] returns undefined.

I have found several workarounds. The simplest is to remove the toLowerCase in this connector's dbName function. Changing the fromRow function also works:

    var columnValue = this.fromColumnValue(props[p], rowData[p]);

Link to sample repo to reproduce issue (if bug)

Expected result

Selected data returned correctly

Actual result (if bug)

Selected data incorrect for columns with any uppercase letter(s)

Additional information (Node.js version, LoopBack version, etc)

loopback@3.1.0 loopback-datasource-juggler @3.0.0

johnsoftek commented 7 years ago

The automigrate method of this connector creates tables with lowercase column names. So, provided a table is created by this connector, this issue does not arise.

Maybe it's worth a note in the docs for anyone transitioning to this connector.

Hintalo commented 6 years ago

I have the same problem. This line ("return name.toLowerCase()" in sqlite3.js) is causing a lot of problems in our project, as we tried to migrate Loopback from version 2 to version 3. Previously, we used Loopback 2.38.3 with "loopback-connector-sqlite", now we wanted to use the latest Loopback (3.18.0) with "loopback-connector-sqlite3": "1.2.0". The problem is that the physical DB tables and table columns are created in a case-sensitive manner, and "loopback-connector" cannot find the columns, because "loopback-connector-sqlite3" converts their names to lowercase, but the rowData contains the original, case-sensitive column names, as mentioned above in the bug description. Re-creating (and migrating) all the existing tables with only lowercase column names is difficult, due to the fact that parts of our software is already released at our customers. Further, renaming column names in existing sqlite tables itself is a tricky task. Why is the dbName conversion to lower-case actually needed? What is the benefit? Can I somehow disable this behaviour and how can I proceed so that the dbName and the rowData values match?