Column names should be quoted / escaped

[lroal]

orange Version

4.0.0

Database

None

Database Version

No response

Operating System

No response

Description

Because could collide with reserved words

Expected Behavior

No response

[Kyryus]

You talk about column name but are table name also not quoted/escaped ?

It looks like if my table is called "user", it is not working but if I rename it "users" it works.

[andreasbhansen]

It looks like if my table is called "user", it is not working but if I rename it "users" it works.

Are you using SQL Server, @Kyryus? This would work rather than renaming the table, just wrap the name in square brackets: x.table('[user]').map(({ column }) => ({})

[Kyryus]

It looks like if my table is called "user", it is not working but if I rename it "users" it works.

Are you using SQL Server, @Kyryus? This would work rather than renaming the table, just wrap the name in square brackets: x.table('[user]').map(({ column }) => ({})

No I am on PostgreSQL but I noticed that it is working if I avoid to use user as table name.

For example if I create a table like that :

create table "user" (
    id          serial PRIMARY KEY,
    email       text NOT NULL UNIQUE,
    "password"    text NOT NULL,
    "name"        text NOT NULL,
    firstname     text NOT NULL,
    country       text NOT NULL,
    activation    text,
    pwdResetCode  text,
    createdAt     timestamp DEFAULT CURRENT_TIMESTAMP
);

And I map like that :

user: x.table('"user"').map(({ column }) => ({
      id: column('id').numeric().primary().notNullExceptInsert(),
      email: column('email').string().notNull(),
      password: column('password').string().notNull(),
      name: column('name').string().notNull(),
      firstname: column('firstname').string().notNull(),
      country: column('country').string().notNull(),
      activation: column('activation').string(),
      pwdResetCode: column('pwdResetCode').string(),
      createdAt: column('createdAt').date(),
    })),

the insert code :

const toto = await db.user.insert({
    email: 'tto@toto.com',
    password: 'toto',
    name: 'TOTO',
    firstname: 'toto',
    country: 'ch',
  });

produce this error :

BEGIN
INSERT INTO "user" (email,password,name,firstname,country) VALUES ($1,$2,$3,$4,$5)RETURNING id,email,password,name,firstname,country,activation,pwdResetCode,createdAt
[ 'tto@toto.com', 'toto', 'TOTO', 'toto', 'ch' ]
select "user".id as s"user"0,"user".email as s"user"1,"user".password as s"user"2,"user".name as s"user"3,"user".firstname as s"user"4,"user".country as s"user"5,"user".activation as s"user"6,"user".pwdResetCode as s"user"7,"user".createdAt::text as s"user"8 from "user" "user" where "user".id=1 order by "user".id
ROLLBACK

[Nest] 13002  - 04/06/2024 10:46:13   ERROR [ExceptionsHandler] syntax error at or near ""user""
error: syntax error at or near ""user""

And if I use table name without quote (for example users instead of "user"), it is not producing any error.

I thought problem was related to that topic.

[Kyryus]

I did not see there was another topic for that : https://github.com/alfateam/orange-orm/issues/91

[lroal]

It looks like if my table is called "user", it is not working but if I rename it "users" it works.

Are you using SQL Server, @Kyryus? This would work rather than renaming the table, just wrap the name in square brackets: x.table('[user]').map(({ column }) => ({})

It won’t work for all cases - but I will try to fix it this week.