didit-tech / fastlegs Goto Github PK

Take a look at #29

Hi guys, I am still looking forward to the README/Examples. Are you still doing this?

How do you implement full text search with Postgres using FastLegs?

All it does is put quotes around things. It doesn't escape inner quotes. If you pass it a string like "'lol'" it will cause an error.

var quote = exports.quote = function(value) { ...
    return "'" + value + "'";
... };

This might be more appropriate:

var quote = exports.quote = function(value) { ...
    return "'" + value.replace(/'/g,"\\'") + "'";
... };

Example:
{ 'field1.equals' : val, 'field2.in: [val2, val3] } will produce 'SELECT * from table where field1 = $1 AND field2 IN ($1, $2, $3)

Currently tried to use the limit/offset feature. Fastlegs only return 1 record although I specify a limit and offset

For a test I have 9 records that match the sql (json object).
limit: 2; offset: 0, 2, 4, 6 for each page number.

Fastlegs returns only one record after the first load i.e. limit:2; offset:0 return the first 2 records; limit: 2; offset: 2, 4 ,6 return 1. It should return 2 records each time.

var page = req.query.page || 1
var limit = 2;
var offset = (page - 1) * limit;

is my pagination calculation

NOTES: I am using PostGreSQL

Hi!

What is the interface to create tables? Schema should be derived from fastlegs entities definitions.

TIA,
--Vladimir

I ran npm install FastLegS. Now I have a few directories in /home/brian with no files.

npm WARN publish-everything [email protected] Adding entire directory to tarball. Please add a
npm WARN publish-everything [email protected] .npmignore or specify a 'files' array in the package.json
...
make[2]: Leaving directory `/usr/local/lib/nodejs/.npm/expresso/0.8.1/package/deps/jscoverage'
make[1]: Leaving directory `/usr/local/lib/nodejs/.npm/expresso/0.8.1/package/deps/jscoverage'
Checking for program g++ or c++          : /usr/bin/g++
Checking for program cpp                 : /usr/bin/cpp
Checking for program ar                  : /usr/bin/ar
Checking for program ranlib              : /usr/bin/ranlib
Checking for g++                         : ok 
Checking for node path                   : not found
Checking for node prefix                 : ok /usr
Checking for program pg_config           : /usr/bin/pg_config
'configure' finished successfully (0.146s)
Waf: Entering directory `/home/brian/dev/node-postgres/build'
Waf: Leaving directory `/home/brian/dev/node-postgres/build'
'build' finished successfully (0.021s)
npm ok

In https://github.com/didit-tech/FastLegS/blob/master/lib/fast_legs/statements.js#L21 file
In line ' return stmt + join + where + limit + offset + order + ';';' limit and offset should be at the end on the sql statement.
Now it is impossible to use order statement with limit.

Thanks.

Hi!

Wonder if you could consider using mondodb style for search conditions/directives? That way people could keep their business tier intact while switching sql/nosql backend.

In turn, in underscore-data we have tools to parse querystrings down to mongo conditions. Gluing these two solutions, one could describe/get his queries in textual form, say: GET /Foo?name=a&email=re:foo,dob<1990&limit(10)&sort(-name) ...

Feasible to have?

TIA,
--Vladimir

When updating a field to a null i.e. clearing its contents. I always get an error.
I have tried null, '', undefined, 'null', 'undefined'. The below error was from a timestamp field i wanted to clear. I get this with and type of field I want to clear using the "empty" values above.

How do you clear a field usign fastlegs?

{ [error: invalid input syntax for type timestamp with time zone: ""]
length: 141,
name: 'error',
severity: 'ERROR',
code: '22007',
detail: undefined,
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
file: '.\src\backend\utils\adt\datetime.c',
line: '3560',
routine: 'DateTimeParseError' }

I'm trying to use FastLegS in a production environment, but even in Staging (i.e. few users) I'm getting a ton of "Unexpected EOF on client connection" errors in my PostGres logs, as well as occasional "FATAL: sorry, too many clients already"; both of which indicate that the connection isn't being closed properly after being used.

I also noticed (could be related, might not be) that FastLegS uses the 'constructor' version of the node-postgres client (ie.. new pg.Client(...)), which does not participate in connection pooling (according to the node-postgres wiki).

I'm wondering what's the right way to be using FL in production, to avoid these errors? I imagine it'll affect app performance pretty significantly if I start letting users hit it, if it's noticeable to just me and a handful of testers.

I'm trying to do a many to many relation between the tables account and user, via account_user. My tables are:

Account

• account_id
• account_name

User

• user_id
• user_name

AccountUser

• account_id
• user_id

What I did:

var User = FastLegS.Base.extend({
  tableName: 'user',
  primaryKey: 'user_id',
  _fields: [
    {column_name: 'user_id'},
    {column_name: 'user_name'},
  ],
  many: [
    {'accounts': Account, joinOn: 'account_user.user_id'}
  ],
});

var Account = FastLegS.Base.extend({
  tableName: 'account',
  primaryKey: 'account_id',
  _fields: [
    {column_name: 'account_id'},
    {column_name: 'account_name'},
  ],
  many: [
    {'users': User, joinOn: 'account_user.account_id'}
  ],
});

With the above tables I'm running this code:

Account.findOne(75, {include: {'users': {}}}, function(err, result) {
    // Do something...
});

I've printed all the queries to see what was happening and I found that the Where clause is selecting the the id in the same table, like this:

SELECT "user".*
FROM "user"
INNER JOIN "account_user" ON "user".user_id="account_user".user_id
WHERE "user".user_id = 75;

The correct query should be:

SELECT "user".*
FROM "user"
INNER JOIN "account_user" ON "user".user_id="account_user".user_id
WHERE "account_user".account_id = 75;

How do I correct this behavior?

PS: In the meantime I'm trying to hack the source trying to figure out how to do this.

'$or': {
'id': 56,
'name': 'harry'
}

'$or': {
'id.equals': 56,
'name.equals': 'harry'
}

tried both of the above methods for an OR select statement, didn't return anything. All AND statements work fine.

{
'id': 56
}

'$or': {
'id': 56,
'name': 'harry'
}

'$or': {
'id.equal.equals': 56,
'name.equals': 'harry'
}

tried both of these methods for an OR select statement, didn't return anything all AND statements work fine.

{
'id': 56
}

(Example in coffeescript)

Stream = FastLegS.Base.extend
  tableName  : "streams"
  primaryKey : "id"

Radio = FastLegS.Base.extend
  tableName  :"radios"
  primaryKey : "id"
  many       : [
    streams : Stream
    joinOn  : "radio_id"
  ]

Radio.find param, {
  order   : [ "name" ]
  only    : [ "id", "name", "title", "genre", "description"]
  include : { 
    streams : {
      only : [ "format", "url", "msg" ]
    }
  }
}, (err, radio) -> (...)

If I remove "id" from only then streams is empty..

would like the ability to have basic validations. should probably look into node validation libraries for a hook.

$ make test

FastLegS: Please enter your Postgres credentials and a database for us to create.

pg username: foo
pg password: 
pg database: (fastlegs_test) 
pg host: (localhost) 
pg port: (5432) 

   100% 36 tests


   integration_test.js integrates: Error: EBADF, Bad file descriptor '/usr/local/lib/node_modules/FastLegS/test/integration/../../.fastlegs'
    at Object.openSync (fs.js:221:18)
    at Object.readFileSync (fs.js:112:15)
    at /usr/local/lib/node_modules/FastLegS/test/integration/integration_test.js:23:21
    at next (/usr/local/lib/node_modules/expresso/bin/expresso:844:25)
    at runSuite (/usr/local/lib/node_modules/expresso/bin/expresso:862:6)
    at check (/usr/local/lib/node_modules/expresso/bin/expresso:766:16)
    at runFile (/usr/local/lib/node_modules/expresso/bin/expresso:770:10)
    at Array.forEach (native)
    at runFiles (/usr/local/lib/node_modules/expresso/bin/expresso:747:13)
    at run (/usr/local/lib/node_modules/expresso/bin/expresso:715:5)


   Failures: 1


make: *** [test-integration] Error 123

Should be pretty straightforward to fix, but it's not immediately clear to me what should be in the file.

Is the FastLegS module still being supported ?

...Just seen that SWIG is no longer supported

I've taken a look and as the binary buffer in newer versions of node have changed the postgres module needs updating to > 0.6.5.

I can provide a patch, but not right now (have run some queries against FastLegS running with 0.6.6 and seems ok, but need to test more)

Are nested queries supposed to work? For example, let's say I have a .find query, and in its results callback I issue further query; I consistently get errors of the type:

    throw new Error('Socket is not writable');
          ^
Error: Socket is not writable
    at Socket._writeOut (net.js:391:11)
    at Socket.write (net.js:377:17)
    at [object Object].query (/usr/local/lib/node_modules/FastLegS/node_modules/pg/lib/connection.js:96:15)
    at [object Object].submit (/usr/local/lib/node_modules/FastLegS/node_modules/pg/lib/query.js:96:16)
    at [object Object]._pulseQueryQueue (/usr/local/lib/node_modules/FastLegS/node_modules/pg/lib/client.js:129:24)
    at [object Object].query (/usr/local/lib/node_modules/FastLegS/node_modules/pg/lib/client.js:153:8)
    at Client.<anonymous> (/usr/local/lib/node_modules/FastLegS/lib/fast_legs/client.js:25:17)
    at Client.emit (events.js:67:17)
    at /usr/local/lib/node_modules/FastLegS/lib/fast_legs/base.js:225:18
    at Object.create (/usr/local/lib/node_modules/FastLegS/lib/fast_legs/base.js:45:3)```

Is there Many To Many support right now?

I tried defining a set of models in a way that I thought would work, but when I try and call a 'find' I get told that my 'id' is ambiguous. I don't have the freedom to change my db schema to make every table use a different column name for Id, and the query that's passed looks right except for the fact that id isn't scoped.

Are there docs somewhere that can tell me how to do m2m?

Hi!

I see no support for OR-ing conditions. How'd you suggest to work it around?

TIA,
--Vladimir

Dates are being returned as timestamp with timezone E.G My database has 1985-09-13 (type: Date) but when queried it returns Fri Sep 13 1985 00:00:00 GMT+0000 (UTC).

This occurred after my last FastLegS update. Before, it simply returned the 1985-09-13.

In the database it is a Date ONLY field but FastLegS returns date, time and timezone

before/after callbacks to hook in to certain actions.

How do I query through a join table with this?

var where = {
'$or': {
'account_id.equals': 1,
'recipient_id.equals': 1
},
'approved_on.ne': null
};

approved on is a timestamp field.

Can null not be checked against a timestamp field? THe query doesn't return any rows with that line

I currently have run into two (2) roadblocks with FastLegs

1. Joining by columns other than the primary key. How can i join two tables using other columns than the primary key? ... I may want to join a table by the author_id (not a pk) (product reviews model) and id (pk) (users model)
2. Accessing defined models anywhere in the file, I may have to create a join for a model that comes after the model i am defining. i.e. circular dependancy ?

e.g.

USERS have many REVIEWS have many USERS (authors)

Lets say I'm doing a many through, and I want the results from the join table as well. It could be a many on the first relationship and a one on the second, and result in something like this:

User = {name:'Fred', 'friendships': [{'user':{name:'Bob'}, confirmed:True}, {'user':{name:'George'}, confirmed:False}]}

Given tables like:

create table user (int id primary key, text name)
create table friendship (int sender_id references user, int receiver_id references user, bool confirmed) 

Currently looking at your test

it('complex or', function() {
expect(StatementsPg.select(model, {
'name.equals': 'John',
'$or': { 'field.equals': 'hi', 'age.equals': 18, 'index.gt': 42 },
'email.ne': '[email protected]'
}, {}, []))
.to.be("SELECT * FROM "model_name" WHERE name = $1 AND (field = $2 OR age = $3 OR index > $4) AND email <> $5;");
})

Is it possible to have ($1 AND $2) OR ($3 AND $4) for SELECT ?

(removed)

I still don't understand how to get a many-to-many query working, and I'm sure there are queries I'd like to do that this system doesn't support. Is it possible to run arbitrary queries using the same database connection? Could the API expose that functionality?

I noticed it just attaches a pg client at FastLegS.client.client, but it doesn't seem to actually execute queries that I run on it.

should have the ability to use belongs_to/one since we have many and many through working.

TypeError: Object 1 has no method 'forEach'
at buildOperator (/home/dc/webapps/app/node_modules/FastLegS/lib/adapters/pg/statements.js:186:11)
at /home/a9designs/webapps/gahoola_desktop/node_modules/FastLegS/lib/adapters/pg/statements.js:271:28
at /home/dc/webapps/app/node_modules/FastLegS/node_modules/underscore/underscore.js:99:42
at .each..forEach (/home/dc/webapps/app/node_modules/FastLegS/node_modules/underscore/underscore.js:86:24)
at Function..map..collect (/home/dc/webapps/app/node_modules/FastLegS/node_modules/underscore/underscore.js:98:5)
at _.(anonymous function) as map
at buildWhereClause (/home/dc/webapps/app/node_modules/FastLegS/lib/adapters/pg/statements.js:267:18)
at Object.exports.select (/home/dc/webapps/app/node_modules/FastLegS/lib/adapters/pg/statements.js:26:16)
at findQuery (/home/dc/webapps/app/node_modules/FastLegS/lib/adapters/pg/base.js:61:32)
at loadSchema (/home/dc/webapps/app/node_modules/FastLegS/lib/adapters/pg/base.js:248:5)

I've noticed for certain classes of error (e.g. db connection string not provided; db connection failed), the module surfaces uncaughtExceptions which can only be handled by the process.on('uncaughtException') stuff from Node. Seems logical that these things could be handled and passed up as a regular error in the callback, rather than crashing the whole application.

{
only: ['id', 'title', 'update_time'],
order: ['-id']
}
I found the order no working! I am using is mysql！

Take a look at #29

Is there any reason not to abstract the Client to be swapped with other DB modules, like node-mysql? I know that mysql and postgres aren't 100% compatible, so maybe it's worth the effort.

I really like FastLegS but my requirements have switched to MySQL. If anyone thinks it's a good idea to abstract the Client to be either MySQL or Postgres, I'll happily do that work. Otherwise I will fork FastLegS and create a MySQL version.

Thoughts?

/test/integration/../../.fastlegs should be
test/integration/../bootstrap/.fastlegs

better documentation, with more examples...