Schema.newRecord() returns first existing record. Not a new 'empty' one.

[Eric Dykstra]

Hey everyone,

I've got this schema definition:

Schema.define({

name:       'Addresses',

fields:      [

{ name: 'address_id',                    type: 'int',     autoIncrement: true, defaultValue: 1 },

{ name: 'address_street',                type: 'varchar', size: 100                            },

{ name: 'address_unit',                  type: 'varchar', size: 100                            },

{ name: 'address_city',                  type: 'varchar', size: 100                            },

{ name: 'address_code',                  type: 'varchar', size: 20                             },

{ name: 'address_country_id',            type: 'int'                                           },

{ name: 'address_region_id',             type: 'int'                                           }

],

primaryKey: 'address_id'

});

Then, in a *.sjs file, I am executing the following:

var address = {};

address = Schema.newRecord('Addresses');

console.dir(address);

Which creates this output:

(object) :

 [address_id] : (number) 1

 [address_street] : (string) (empty)

 [address_unit] : (string) (empty)

 [address_city] : (string) (empty)

 [address_code] : (string) (empty)

 [address_country_id] : (number) 0

 [address_region_id] : (number) 0

Problem is, that there are 2 rows of data in the table already, so I thought the newRecord method should return an object with address_id == 3.

Obviously, continuing with an existing address_id is kinda messing things up.

What am I missing?

Thanks,

Eric

[Michael Schwartz]

You have defaultValue: 1in your Schema.define() call.

Schema makes heavy use of MySQL "REPLACE INTO" for its work.  So you want address_id to be 0 for new records and for it to be the actual id of a record if you want to overwrite an existing one.

In the case of newRecord(), it's simply returning an object with all the fields and their defaultValue from your Schema.define().

If you call Schema.putOne('Addresses', Schema.newRecord('Addresses')) in a loop, it will add that (loop count) many records to the database.  As long as you have address_id's default value set to 0!!!

After you call Schema.putOne() with primaryKey set to 0, the returned object will have the value set to what's in the database for the record.  For example (assume empty table):

var o = Schema.putOne('Addresses', Schema.newRecord('Addresses'));

o will be 

(object) :

 [address_id] : (number) 1

 [address_street] : (string) (empty)

 [address_unit] : (string) (empty)

 [address_city] : (string) (empty)

 [address_code] : (string) (empty)

 [address_country_id] : (number) 0

 [address_region_id] : (number) 0

If you then:

o = Schema.putOne('Addresses', Schema.newRecord('Addresses'));

o will be 

(object) :

 [address_id] : (number) 2 <<<<<<< NOTE!!!

 [address_street] : (string) (empty)

 [address_unit] : (string) (empty)

 [address_city] : (string) (empty)

 [address_code] : (string) (empty)

 [address_country_id] : (number) 0

 [address_region_id] : (number) 0

Note: if no defaultValue specified in Schema definition, then 0 is used for int fields, and empty string is used for varchar, etc.

--
You received this message because you are subscribed to the Google Groups "SilkJS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to silkjs+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Eric Dykstra]

Mike,

Thanks. That worked perfect.

For others that find their way here, below is an example code segment. It is not the most elegant way to achieve the goal - but it is clear to understand.

Eric

        ........

        var newAddress = false;

        if((address_id == null) || (address_id == 0)) newAddress = true;

        var MySQL = require('MySQL').MySQL;

        var SQL   = new MySQL();

        

        SQL.connect();

        SQL.startTransaction();

    

        try {

            var address = {};

            if(!newAddress) {

                address = Schema.findOne('Addresses', {address_id: address_id}); 

            };

            address.address_street     = address_street; 

            address.address_unit       = address_unit; 

            address.address_city       = address_city; 

            address.address_region_id  = address_region_id; 

            address.address_country_id = address_country_id; 

            address.address_code       = address_code; 

            if(newAddress) {

                address = Schema.putOne('Addresses', Schema.newRecord('Addresses', address)); 

            } else {

                address = Schema.putOne('Addresses', address); 

            };

            SQL.commit();  

        } catch (e) {

        ........

 

[Michael Schwartz]

If you have an object called "example" that has some of the Address members filled in…

example = Schema.newRecord('Addresses', example);

This fills in the new record, then sets the fields in the returned record to the values in example.

If you are just setting members of the example object - meaning you did not first findOne() to load it in…

example = example.address_id ? Schema.findOne('Addresses', { address_id: example.addressId }).extend(example) : Schema.newRecord('Addresses', example);

Look at that one close :)

(if the example is in the database, load it in and set the fields of the loaded record to the ones set in example, otherwise create a new record and set the fields to the ones set in example)

Also, to make things way easier, I do things like make an Addresses class in JavaScript and require() it so it's always there.

// file lib/Addresses.js

exports = {

    findOne: function(example) { return Schema.findOne('Addresses', example); },

    find: function(example) { return Schema.find('Addresses', example); },

    // etc.

   …

    customAdd: function(address_id, toAdd)  { SQL.update('UPDATE Addresses SET custom=custom+' + toAdd + ' WHERE address_id='  + SQL.quote(address_id)); }

};

In your server.js, bootstrap.js, whatever you call your main code (or top of shebang script):

// near the top:

require.paths.push('lib');

// not as near the top:

global.Addresses = require('Addresses');

etc.

Now you just do Addresses.findOne(example) in your code instead of Schema.findOne('Addresses', example).  Easier to type ? :)

Your code where you set all the members of address object could be a little cleaner:

address.extend({

  address_street: address_street,

  address_unit: address_unit,

… 

});

SilkJS adds the extend() method to Object prototype.

[Michael Schwartz]

A couple other things…

You don't need the transaction logic unless you are doing putOne() to two related tables where a failure of one query would hose your DB.

You don't need to require() MySQL or instantiate an SQL object if your code is running in the http server.  Each child will create a global SQL object when it first is forked.

On May 13, 2013, at 12:44 PM, Eric Dykstra <virtu...@gmail.com> wrote:

[Eric Dykstra]

Mike,

Sweet. Makes sense. I still need it in 1 spot, where 3 related tables are being updated. Works fine. Going over your earlier post now.

Thanks.

Eric

[Eric Dykstra]

Mike,

I must admit, the post with all the suggestions looked interesting, but initially, I didn't think it was going to 'buy' me much.

For example, re: "Now you just do Addresses.findOne(example) in your code instead of Schema.findOne('Addresses', example).  Easier to type ? :)"

Sure - it is 10 less characters, but worth it? 

Well, I went ahead and followed your lead. What ended up happening, was that one thing lead to another, and I saw opportunities to leverage those changes. I was able to remove a lot of supporting code/logic not shown in the code segment provided, and overall, the re-factoring (easy really) made things much more concise, and even more 'readable'.

I've now implemented all the suggested changes. 

Thanks again.

Eric

[Michael Schwartz]

Yep.

For a Users class and Schema, you might have the basic findOne(), et al.  Then you mixin or augment your Users singleton with methods like authenticate(), addUser(), and so on.

More food for thought...