SchemaBuilder Step by Step

[blaasvaer]

In an effort to try and understand SchemeBuilder, please explain the various steps to me while I try to make a simple query to a mysql database, and send the data to the view – here we go.

I have a simple table like this:

---------------------------------------------------
|  id  |      name     |  hourly_rate  |  active  |
---------------------------------------------------
|   1  |  service_one  |      123      |     1    |
---------------------------------------------------
|   2  |  service_two  |      234      |     1    |
---------------------------------------------------
|  10  |  service_ten  |      789      |     1    |
---------------------------------------------------

I have this in a controller (controllers/services.js):

exports.install = function() {
    F.route('/services', view_services, ['*Services']);
};

function view_services () {
    let self = this;
    self.$query( self, self.callback() );
}

What am I doing here? As far as I can understand from the docs is by piecing things together more or less randomly, that I execute 'some query' set in the (Model) Schema by doing a setQuery ...

So, ['*Services'] magically makes the model available to the controller. And in the process, .setQuery in the model magically becomes available in the controller as controller.$query() ... !? Yeah, this is PURE guessing ...

And this in a model (models/services.js) – copied and trimmed from somewhere in the docs I can no longer remember:

NEWSCHEMA('Services').make(function(schema) {
    schema.define('id', 'Number', true);
    schema.define('name', 'String(50)');
    schema.define('hourly_rate', 'Number');
    schema.define('active', 'Boolean');

    schema.setDefault(function(name) {
        switch (name) {
            case 'name':
                return 'name missing';
            case 'hourly_rate':
                return 850;
            case 'active':
                return 1;
        }
    });

    schema.setQuery(function(error, options, callback) {
        var sql = DB(error);
        sql.select('items', 'services').make(function(builder) {
            // builder.where('isremoved', false);
            // builder.skip(options.page * options.limit);
            // builder.take(options.limit);
        });
        sql.exec(callback, 'items');
    });
});

Now, WHAT am I supposed to do here!?

WHAT is 'items'? I can understand that 'services' is the table I'm querying.

HOW am I supposed to know/guess what .select is doing?

Where does the builder come from?

Should I be writing my own custom query!?

What all these builder properties are I don't know ... !?

Now, to keep it from getting to long:

1. WHAT do I do in the controller to get the data from the model and send it to the view for display?
2. WHAT do I do in the model to actually query the database?

[BlueMagnificent]

It seems you have a bit of things mixed up here. What you have in your schema.setQuery() method is actually a block of code making use of the SqlAgent library which is an ORM for node.js

Now, WHAT am I supposed to do here!? WHAT is 'items'? I can understand that 'services' is the table I'm querying.

I wouldn't really know where you got your code from, but from the description on the SqlAgent repository (and as I understand it) what your have as "items" is the name the query result will bear. So your execute can be like this

sql.exec(function(err, response) {
    console.log(response.items);
}

...and as you rightly said 'services' is the table you are querying

HOW am I supposed to know/guess what .select is doing? Where does the builder come from? Should I be writing my own custom query!? What all these builder properties are I don't know ... !?

All these are properly described in SqlAgent repo. SqlAgent has a select() method as well as save(), insert(), update(), delete(), and even query() if you want to pass custom queries. You'll also get to see SqlBuilder object which is the "builder" you are referring to

[petersirka]

I'll answer later, I'm on a business trip to thursday. @BlueMagnificent thank you!

[blaasvaer]

I spent days trying to get the SqlAgent to work ... without any luck. The only way I've managed to connect to mysql is with this:

exports.getProjects = function ( next ) {
    F.database(function( err, connection ) {
        if ( err != null ) {
            return;
        }

        let query = "SELECT projects.id AS project_id, projects.name AS project_name, clients.id AS client_id, clients.name AS client_name FROM projects, clients WHERE projects.client_id = clients.id";

        connection.query(query, function( err, rows ) {
            // Close connection
            connection.release();

            if (err != null) {
                return;
            }

            next( rows );
        });
    });
}

exports.getProject = function ( id, next ) {
    F.database(function ( err, connection ) {
        if ( err != null ) {
            return;
        }

        let query = "SELECT projects.id AS project_id, projects.name AS project_name, clients.id AS client_id, clients.name AS client_name, projects.description AS description FROM projects, clients WHERE projects.id = " + id + " AND projects.client_id = clients.id";

        connection.query( query, function ( err, rows ) {
            // Close connection
            connection.release;

            if ( err != null ) {
                return;
            }

            next( rows );
        });
    });
}

And that is, having a definition like this:

let mysql = require('mysql'),
    pool = mysql.createPool({
        host: 'localhost',
        user: 'root',
        password: 'root',
        database: 'timetracker',
        typeCast: function castField( field, useDefaultTypeCasting ) {
            if ( ( field.type === "BIT" ) && ( field.length === 1 ) ) {
            var bytes = field.buffer();
                return( bytes[ 0 ] === 1 );
            }
            return( useDefaultTypeCasting() );
        }
    });

F.database = function ( callback ) {
    return pool.getConnection( callback );
};

Here I'm manually writing all my queries ... And I haven't been able to 'convert' this into a Schema, as I simply do not know the inner workings of them. Not to mention that the DB stuff I haven't been able to convert to SqlAgent stuff.

Now, some well commented isolated examples would be nice. Oh, here I go again on the docs ... ; )

[webdesus]

I wanna disagree. SqlBuilder very simple and have full documentation. It's just replacer wich using pattern builder. Another question whether you need it or not. Cuz if you love sql then write to sql.

I recommend that I get acquainted this exist projects. For example

[blaasvaer]

So, here we go again – another shot at making the SqlAgent work, and hopefully in the process get just a tiny bit of understanding of what is going on:

I have this in (definitions/mysql.js):

let mysql_connection_string = "Server=localhost;Port=80;Database=timetracker;Uid=root;Pwd=root";
require('sqlagent/mysql').init( mysql_connection_string, true ); // debug is by default: false

And this in (controllers/projects.js):

exports.install = function () {
    F.route('/projects', view_projects, ['*Projects']);
};

function view_projects () {
    let self = this;

    self.$get(self, self.callback());
}

And yes, I know I don't return any response ... but one step at a time.

What I don't get is, what is $get actually calling on the model? As I read the docs, it's executing the get delegate(!?) on the model, which – to my best understanding of it – is set using schema.setGet .... But why I'm passing in the controller I don't know, and what self.callback() is I don't know ... and on and on it goes.

And this in (models/projects.js):

NEWSCHEMA('Projects').make(function ( schema ) {
    schema.define('id', 'Number');
    schema.define('name', 'String(50)');
    schema.define('client_id', 'Number');
    schema.define('description', 'String');
    schema.define('active', 'Boolean');

    schema.setDefault(function(name) {
        switch (name) {
            case 'active':
                return "b'1'";
        }
    });

    schema.setGet(function (controller, callback) {
        const sql = DATABASE( new ErrorBuilder() );

        sql.select('projects', 'projects');

        sql.exec(function ( err, response ) {
console.log(response.projects);
        });
    });
});

I'm simply trying to get SOMETHING back from the DB for a start.

But what I get so far is:

500: Internal Server ErrorTypeError: Cannot read property 'split' of null

Yeah, start debugging that ... ; )

I suspect it's because I haven't gotten any where and the like attached to the sql. But I would expect the above to return EVERYTHING from the projects table, as in 'SELECT * FROM projects'.

@webdesus, I've been through a lot of the finished apps. They're uncommented, and NOT the way to learn a whole framework.

[molda]

@blaasvaer i have already commneted here https://github.com/totaljs/node-sqlagent/issues/28#issuecomment-377182334

Your mysql_connection_string is in unsupported format!!!

[blaasvaer]

Oh, great to know. Sorry, totally missed that response.

[blaasvaer]

And now I suddenly get:

500: Internal Server ErrorError: Schema "Projects" does not exist.

How on earth did that suddenly remove my Schema!?!?

[blaasvaer]

Well, had to scrap the tmp folder to get rid of that ...

[blaasvaer]

So, a bit of a break through ... with the correct connection string – which @molda gave me the answer to six days ago in another thread – I can pull data from the DB. Hooray!

Now, how do I wrap that in the controller to send it to the view ... the self.callback() is confusing me.

wait for this self.$get(self, self.callback()); ... then send to view.

[molda]

The simplist would be to check how you can use self.callback() by reading documentation https://docs.totaljs.com/latest/en.html#api~FrameworkController~controller.callback

You would find out that it takes an optional argument, a view name. So the bellow code would do exactly what you want.

self.$get(self, self.callback('your-view-name'));

and the above is shorter version of

self.$get(self, function(err,response){
    self.view('your-view-name', response);
});

you also don't need to pass controller (self) to the $get function since you can access it in the schema using $.controller although only when you use the dollar sign. It's just to make it simpler although it may make it a bit confusing but it's all in the docs https://docs.totaljs.com/latest/en.html#api~SchemaBuilder~(-v2-5-0-supports-improved-declarations-of-schema-operations)

schema.setGet(function ($) {
    $.controller;
});