Comments (28)
@raymondfeng Any chance to merge? So much time has passed and this bug still exists and issue open.
from loopback-connector-postgresql.
I ran into this problem when trying to use Model.find({include: 'some-relation'})
when Model
's 'some-relation' is hasMany to the other model. @tuomastanner's fork didn't solve the problem (but it broke include for built-in models).
I found that the issue was only present when the relation is defined on both sides. Model1 hasMany to Model2 and Model2 belongsTo Model1. When I removed the relation from Model2, Model1.find
with the include filter worked as expected.
from loopback-connector-postgresql.
Sorry for the noise, seems if name for each of the database, schema, table are quotes i.e.
create [database|schema|table] "CaseSensitiveName"
Postgres will honor the casing as expected, but one really has to wonder why this isn't the default behavior if in fact Postgres by default is case sensitive... Really feels broken in this regard...
from loopback-connector-postgresql.
I've found a simple solution to keeping mixed case that doesn't require modifying the connector source. I am running version 3.1.0 of loopback-connector-postgresql
Example: In a boot script (server/boot/z_my_script.js):
// First get my DataStore that runs on postgres:
const server = require('../server');
const ds = server.dataSources.myDataSource;
// override PostgreSQL.prototype.dbName in postgresql.js:
ds.connector.dbName = name => name // just use the name as-is!
I also have a call to DataSource.discoverAndBuildModels which I used to generate a model for a database view. That, too, has issues with case that can be solved by explicitly passing nameMapper:null as below:
ds.discoverAndBuildModels(v.name, {visited: {}, associations: true, nameMapper: null})
(I also posted this to issue 116)
from loopback-connector-postgresql.
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
from loopback-connector-postgresql.
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
from loopback-connector-postgresql.
bump - anyone got any comments ? This issue makes my postgres database unusable with strongloop
from loopback-connector-postgresql.
Same problem here
from loopback-connector-postgresql.
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
from loopback-connector-postgresql.
Nice work @paddyinpdx/@tuomastanner. Any chance of a PR so the loopback folks have it on the radar?
from loopback-connector-postgresql.
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?
from loopback-connector-postgresql.
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
from loopback-connector-postgresql.
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.
from loopback-connector-postgresql.
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.
from loopback-connector-postgresql.
+1
I found the same problem with mysql loopback connector.
from loopback-connector-postgresql.
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://:[email protected]/<USER_NAME>/<REPOSITORY_NAME>.git",
from loopback-connector-postgresql.
same problem
from loopback-connector-postgresql.
With respect to what @raymondfeng said, this is our default behaviour:
Please note if a DB column name is provided (such as
{myProp: {postgresql: {column: 'MyCol'}}
), the casing will be preserved (MyCol
). If no customization is made, we continue to enforce default cases for the column name.
Closing this issue as resolved. Feel free to reopen if needed.
from loopback-connector-postgresql.
Sorry @ssh24 and @raymondfeng, but it's not true: with the sql server connector the case of the column name is never preserved.
Even if the correct name is indicated in the customization.
from loopback-connector-postgresql.
@gmarab Sorry there was a typo. It should be columnName
not column
.
"info": {
"type": "object",
"postgresql": {
"columnName": "Info",
"dataType": "json"
}
}
from loopback-connector-postgresql.
The name of the column is correct:
But the result is always wrong:
from loopback-connector-postgresql.
What version of the connector are you using?
from loopback-connector-postgresql.
The last: 3.0.0
from loopback-connector-postgresql.
Sorry, just realized you are using mssql
. This is the postgresql module. This might be an issue with mssql, do you mind creating an issue there and ping me?
from loopback-connector-postgresql.
Sorry, but in the previous posts I saw that the connector for Mysql and MsSql was also mentioned. I thought the problem could be general.
from loopback-connector-postgresql.
Hi @ssh24,
I really think the problem is general:
In loopback-conector \ sql.js, at line 1402 is made this assignment:
data [p] = columnValue;
when I think the correct assignment is:
data [columnName] = columnValue;
Or at least that works;)
from loopback-connector-postgresql.
@gmarab It works for postgresql with the latest version of this connector module.
from loopback-connector-postgresql.
I'm running with Postgres 9.6.5
Just curious what is the correct behavior for this? I'm kind of a newbie to Postgres in general, I get that it is case sensitive, many years doing c/c++ easy to live with if that is the way things are. The behavior I'm seeing seems broken. If one creates a table "MyTable" with column names "ColumnOne" why would Postgres if it actually is case sensitive ever alter the names? I can't think of any other tool or data format where case matters that anything just blindly lower cases stuff. How could one handle the case where you need to serialize in an Xml document where element become tables and attribute columns say for example?
<MyDatabase> <elem1 a="1"/> <Elem1 A="1"/> </MyDatabase>
How can one get this into a Postgres database where the db name is MyDatabase with two tables "elem1" and "Elem1"?
Create Database MyDatabase...
Create Table elem1( a int);
Create Table Elem1( A int);
Given my code is at the mercy of the xml author I have no control over the authored xml. Nothing else I've seen which is case sensitive plays with the names to cause bugs in user code…
How does one correctly handle serializing the sample xml above into a case sensitive database?
from loopback-connector-postgresql.
Related Issues (20)
- Postgresql column as text[] or character varying() is returning data as string HOT 8
- Query with AND & OR combination is not working HOT 2
- Error when using transaction timeout HOT 6
- Postgres support > 9.x HOT 4
- LB4 with [email protected] causing SSL Error HOT 4
- eq object (JSON columns) doesn't work HOT 6
- Error: syntax error at or near ")"
- Support for Full Text Search
- Option to use joins instead of multiple calls for relations. HOT 1
- Release called on client which has already been released to the pool HOT 2
- Doesn't scramble the where correctly
- If url is empty string in config server crashes HOT 2
- Dependency Dashboard
- Add back Node.js v12 tests HOT 4
- Support bulk insert in one query using newly added createAll() in connector
- Error: Postgresql column as jsonb returning data as string using createAll() method HOT 2
- MongoDB extended operator rules erroneously applied to SQL databases and json bodies
- Default values in Models properties totally being ignored when using migrateschema. HOT 1
- Support for overlaps json operator
- Getting 5000ms Timeout error while connecting Postgres - I'm using node version 16.0.0, still running into the same problem. And I'm using the latest Postgres connector: `^7.0.1`. HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from loopback-connector-postgresql.