evoluteur / evolutility-server-node

Model-driven REST APIs for CRUD and more, written in Javascript, using Node.js, Express, and PostgreSQL.
GNU Affero General Public License v3.0
112 stars 33 forks source link

PostgreSQL database quoted field names #3

Closed Libertium closed 8 years ago

Libertium commented 8 years ago

Hi, this project is amazing !! Working on Linux, when update some item in comics, i get this error: ... --- UPDATE ONE --- params = { "objectId": "comics", "id": "1" } body = { "id": 1, "title": "Do Androids Dream Of Electric Sheep?", "genre": "sf", "authors": "Philip K Dick, Tony Parker", "language": "EN", "serieNb": 6, "haveNb": 6, "have": "1-6", "complete": true, "finished": true, "notes": "", "pix": "comics/androitsheep1.jpeg", "amazon": "", "bdfugue": "" } sql = UPDATE evol_demo.comics SET title=($1),genre=($2),authors=($3),language=($4),serieNb=($5),haveNb=($6),have=($7),complete=($8),finished=($9),notes=($10),pix=($11) WHERE id=($12)

sql = SELECT * FROM evol_demo.comics WHERE id=($1)

events.js:141 throw er; // Unhandled 'error' event ^

error: column "serienb" of relation "comics" does not exist ...

Seems that Postgre needs quoted fields when their names have upper and lower cases like the field serieNb.

So I do this changes in the file index.js

// ######### UPDATE ONE ###### // ... _.forEach(fields, function(f){ if(f.attribute!='id' && f.type!='formula'){ var fv=req.body[f.attribute]; switch(f.type){ case 'formula': break; case 'boolean': idx++; ns.push('"'+f.attribute+'"'+'=($'+idx+')'); // Changed vs.push(fv?'TRUE':'FALSE'); break; case 'date': case 'time': case 'datetime': if(fv===''){ fv=null; } //no break; default: idx++; ns.push('"'+f.attribute+'"'+'=($'+idx+')'); // Changed vs.push(fv); break; } }

The same change must be done in the Insert: // ######### INSERT ONE ###### router.post('/api/v1/evolutility/:objectId', function(req, res) { ... ns.push('"'+f.attribute+'"'); // Changed

evoluteur commented 8 years ago

I think it now works. Using all lowercase column name seem to solve the problem.

Libertium commented 8 years ago

Sure, using lowercase column names works, but It would be best to use quotes in the columns names ? For example, in the in the Insert ONE use this: ns.push('"'+f.attribute+'"');

evoluteur commented 8 years ago

You are right. I will add it. Thanks.

evoluteur commented 8 years ago

Done.