[NFR] Escaping system to generated SQL according to database system

[bungcip]

phalcon cannot save model when using table named "user" in postgresql.

$user = new User();
$user->username = "foo";
$user->pasword = "bar";
$user->save(); /// error...

causing error:
PhalconException: ERROR: column "username" does not exist LINE 1: SELECT COUNT() AS rowcount FROM user WHERE username = 'foo' ^ when executing SELECT COUNT() AS rowcount FROM user WHERE username = 'foo'

This is because phalcon generated sql not using quoted version. The right query is

SELECT COUNT(*) AS rowcount FROM "user" WHERE username = 'foo'

sql script:

CREATE TABLE "user"
(
  username character varying(32) NOT NULL,
  password character(40) NOT NULL,
  CONSTRAINT users_pkey PRIMARY KEY (username )
)

User.php

<?php

use Phalcon\Mvc\Model;

class User extends Model {
}

[phalcon]

Maybe this could work:

class User extends Model {

   public function getSource(){
            return '"user"';
   }

}

[daviddoran]

Looks like your source name is double-quoted.

[bungcip]

@phalcon
nope. still wrong.

PhalconException: Table ""user"" doesn't exist on database when dumping meta-data for User

[nesbert]

Try backticks... since "user" is reserved word in MySQL I believe.

HTH

class User extends Model {

   public function getSource() {
            return '`user`';
   }

}

[bungcip]

@nesbert:
postgresql use double quote (") and "user" is not reversed word in MySQL. you can check in this url

http://www.petefreitag.com/tools/sql_reserved_words_checker/?word=user

[nesbert]

@bungcip "postgresql" my bad missed that in the title... thanks for the link btw :)

[phalcon]

we need to implement a full scaping system for columns and tables in Phalcon to fix this, that will take a couple of weeks.

[gyde]

Yes, we have the exact same problem with all the keywords when using Pdo\Mysql
For now we just use a namespace_keyword convention when needed, and try to avoid keywords in general, but a full scaping system will be appriciated.

[divgit]

I have the same error.
PhalconException: Table "users" doesn't exist on database when dumping meta-data for Users

When i create object use model and try to select data use static methods than i catch an error.
Error in Postgres.

Example:
$usersTable = new Users();
Users::find(); // return error
$usersTable::find(); // also error

But if i dont create Users instance all work good.

My decision for Postgres:
Create 2 models on the same table.
First use for saving (updating), second for selecting.

Very nasty bug

[phalcon]

From 0.7.0 a full escaping system for columns/tables/schemas is implemented in the ORM:

For example, the following PHQL statement:

SELECT name, type FROM Robots ORDER BY name

is transformed to:

SELECT `robots`.`name` AS `name`, `robots`.`type` AS `type` 
FROM `phalcon_test`.`robots` ORDER BY `robots`.`name`