Force escape column names in Postgresql

Juninho De Luca

Hi guys (Allan!),
I'm moving my system to Heroku, which uses mainly Postgres (other option is ClearDB/Mysql 5.5 based, very old). So... I've created the migration tables with Laravel and seeded it. Voilà! DB is populated. But, lasts 2 days I have working fixing a lot of important errors. As I was using MySQL, some of my tables have columns names as start, end, group. That are reserved words in Postgres, but not in MySQL. In the PG query, I just can use something like INSERT INTO (id, "group", "end" ...) to escape the column name to make it work. But Editor looks don't do that. And I have some dozens of tables in this situation to change and test everything (and change controllers, javascript, etc). Theres some place or setting where I can add something like '"' . $field . '"' for escape all columns?
Sorry my bad english.

allan

Just to confirm - this is with PHP? You should be able to replace this line with:

protected $_identifier_limiter = array( '"', '"' );

And I believe that should do it.

Allan

Juninho De Luca

I tryed, but seems don't work. I suspect was because I ever use compound field names like Field::inst( 'projects.description' ) instead of Field::inst( 'description' ). But not sure. Anyway, I just found a "forced" solution, but not sure if will pass in future tests hehe

In Database\Query.php, I changed the return of _build_field() method like below

//return ' ' . implode( ', ', $a ) . ' '; <<<< ORIGINAL!!!
return ' "' . implode( '", "', $a ) . '" ';

This is an exemple of the output string

INSERT INTO  projects  ( "status", "closed", "label_id", "name", "start", "end" ) VALUES (  :status,  :closed,  :label_id,  :name,  :start,  :end )

Now it works fine.

allan

\Interesting - thanks for that. This is something I need to look into and I've made a note to that effect (DD-1097 in our internal bug tracker).

Allan

Juninho De Luca

In fact, I rolled back. That works fine to Editor in POST requests, but not for GET (some issue with count). I wasn't able to finish the migration due a lot of problems with postgres (not datatables issues). So... I changed my add-on to Jaws-DB (mariaDB) and now it's working again.