Passing `undefined` variable to a selector returns all records in the model

[misham]

Assuming there's a model Foo that looks like this:

var Schema = require('decaf-mysql').Schema;

Schema.add({
    name       : 'Foo',
    primaryKey : 'fooId',
    fields     : [
        { name : 'fooId', type : 'int', autoIncrement : true },
        { name : 'name', type : 'varchar', size : 255 },
        { name : 'categoryId', type : 'int', defaultValue : 0 }
    ]
});

Running the following selector returns all values:

var id = undefined;
var result = Schema.find('Foo', {fooId: id});

result contains all rows in Foo.

A better approach would be to throw an error that the selector is undefined or return empty perhaps?

[mschwartz]

The example looks empty to Schema.find().

It is proper behavior to return all records for an empty example.

On Tuesday, July 28, 2015, Misha Manulis notifications@github.com wrote:

Assuming there's a model Foo that looks like this:

var Schema = require('decaf-mysql').Schema;

Schema.add({
name : 'Foo',
primaryKey : 'fooId',
fields : [
{ name : 'fooId', type : 'int', autoIncrement : true },
{ name : 'name', type : 'varchar', size : 255 },
{ name : 'categoryId', type : 'int', defaultValue : 0 }
]
});

Running the following selector returns all values:

var id = undefined;
var result = Schema.find('Foo', {fooId: id});

result contains all rows in Foo.

A better approach would be to throw an error that the selector is
undefined or return empty perhaps?

—
Reply to this email directly or view it on GitHub
#3.

[misham]

It may be proper behavior for empty example, but it is unexpected behavior when the selector is undefined.

Similarly, when a row is NULL, Schema sets it's value in the returned object to 0. All of those are surprises.

Documenting such things would help clear up any confusion.

[mschwartz]

These are not surprises. Query by example is pretty consistent.

If any field in the example object you are querying for is "undefined" it
means "wildcard" or "match any value in the db row". This is why empty
object (all fields are undefined in the query) returns all rows (all fields
match every column in the db).

It seems you should validate your object before passing to Schema.putOne().

Schema finds and puts COMPLETE objects.

If you query for an object, you get every column in the table per row
returned. If you put an object, putOne() completes the object by adding
default values from your Schema.add() definition. You can provide
defaultValue: function() or value in the field array per field if you don't
like the default (0 for int, empty string for string)

If you want to read-modify-write to the database, then do so:

Schema.putOne('table', record.primaryKey ? decaf.extend(record,
Schema.findOne('table', record.primaryKey)) : record );

Note findOne() will return an empty record {} if not found so constructions
like the above will not throw an error. In fact, that assures the right
thing is actually done.

You are free to use SQL directly. I do it in the onRequest handler to
update lastAccess field because it's way faster to do that than
read-modify-write the whole user object. In fact, Schema.where() is
totally designed to assist you in generating raw queries.

SQL.getDataRows('SELECT * FROM Users,UserGroups WHERE
UserGroups.userGroupId=Users.userGroupId AND ' + Schema.where('Users',
example).concat(Schema.where('UserGroups', example)).join(' AND '));

The above returns an array of POJOs that are the joined result rows of that
query.

Consider that it isn't Schema's job to double check your intention. It may
well be someone else's work of art to use field: undefined with a purpose
other than what you "expect."

On Tue, Jul 28, 2015 at 12:48 PM, Misha Manulis notifications@github.com
wrote:

It may be proper behavior for empty example, but it is unexpected behavior
when the selector is undefined.

Similarly, when a row is NULL, Schema sets it's value in the returned
object to 0. All of those are surprises.

Documenting such things would help clear up any confusion.

—
Reply to this email directly or view it on GitHub
#3 (comment).