How to create a "SqlStore" that is integrated with SequelSphereDB?

[j.fowler]

All,

Over the next several weeks, I'm creating a "Connector" to help integrate SequelSphere (an HTML5 / JavaScript Relational Database Engine) with ExtJS. Populating grids and charts is relatively easy (see here for my first attempt), but I'm looking to provide more fundamental integration with ExtJS at the DataStore level. For example, creating a store like this might be pretty cool (powerful):

var emplQuery = "" +
    "SELECT e.empl_id, e.name, e.age, d.name as dept_name " +
    "  FROM empl e " +
    "  JOIN dept d " +
    "    ON e.dept_id = d.dept_id " +
    "";

var store = new Ext.data.SqlStore({
    storeId: 'emplInfo',
    sql: emplQuery
});

Now, I've been spelunking through the ExtJS source code concerning DataStores, Readers, Models, and such, and I'm hoping some ExtJS gurus can help me with some basic questions concerning creating a custom "SqlStore":

Which of these approaches is correct, or is it some combination, or something else entirely?

1. Enherit from JsonStore, and provide the results of the query in a form it understands in the constructor of SqlStore.
2. Enherit from Store, and provide a custom "Reader" to query data from the SequelSphere database.
3. Enherit from DirectStore, and provide a custom "Client Proxy" that retrieves data from the SequelSphere database.

It looks to me like the Ext.data.* classes are very flexible and written with excellent OO principles in mind. That means there are probably multiple ways to make it work, and picking the preferred way a bit subjective. Nevertheless, please provide me feedback into what way would work the best.

One more simple question: Above, I called the SqlStore "Ext.data.SqlStore". Is that good form? Or should it be referenced by a namespace outside of Ext, such as: "SequelSphere.data.SqlStore"?

Many thanks!

john...

[j.fowler]

I don't know if this is the best way, but my Option #1 was easy to implement:

Ext.define('Ext.data.SqlStore', {
    extend: 'Ext.data.Store',
    constructor: function(config) {
        if (!config.sql)
            Ext.Error.raise("'sql' is a required parameter of SqlStore.");
        this.queryResults = db.queryObjects(config.sql);
        var model = Ext.define(null, {
            extend: 'Ext.data.Model',
            fields: this.queryResults.columnNames
        });
        config = Ext.apply({
            autoLoad: true,
            model   : model,
            data    : this.queryResults.data,
            proxy   : { type: 'memory', reader: 'json' }
        }, config);
        this.callParent([config]);
    }
});

Using the above "SqlStore", I am able to execute SQL and create a store as follows:

var query = Ext.getCmp("sqlArea").getValue();
var store = Ext.create('Ext.data.SqlStore', {
    sql: query //  The sql is executed, creating 'queryResults'
});

But my question still stands: Is this the best way? What are the drawbacks? Should I be looking into something else?

Your help is appreciated,

john...

PS. For an example page, go here: SqlStore Example

[James Goddard]

Ignore the store. The store is just the endpoint where the data is stored. In fact if you look at xml store or json store you will see they just extend store and set the proxy/reader/writer type to xml or json.

The proxy, reader and writer are what you need to implement.

The proxy will be used to store and retrieve data from your database in whatever format your database uses.

The reader/writer will be used to convert your data to/from model data.

[j.fowler]

Thank you James!

But a question: What is the benefit of having a custom Proxy & Reader as opposed to using the built in ones? That is, when I try to extend Ext.data.Store, I find it is actually very easy to implement:

Ext.define('Ext.data.SqlStore', {
    extend: 'Ext.data.Store',
    constructor: function(config) {
        if (!config.sql)
            Ext.Error.raise("'sql' is a required parameter of SqlStore.");
        this.queryResults = db.queryObjects(config.sql);
        var model = Ext.define(null, {
            extend: 'Ext.data.Model',
            fields: this.queryResults.columnNames
        });
        config = Ext.apply({
            autoLoad: true,
            model   : model,
            data    : this.queryResults.data,
            proxy   : { type: 'memory', reader: 'json' }
        }, config);
        this.callParent([config]);
    }
});

Using the above "SqlStore", I am able to execute SQL and create a store as follows:

var query = Ext.getCmp("sqlArea").getValue();
var store = Ext.create('Ext.data.SqlStore', {
    sql: query //  The sql is executed, creating 'queryResults'
});

But it's clear you believe a custom Proxy/Reader/Writer is the right way to go. I'll start trying to create a custom Proxy/Reader/Writer, and see what it gives me.

BTW: I also posted an example of using the above here: SqlStore Example.


john...

[James Goddard]

If the format of your data is already compatible with the json reader, which yours does appear to be else your example would not work then there is no reason at all to implement a custom reader.

What you have implemented is essentially something that reads the data into memory and then passes that data to a store. This method may be sufficient for your current needs but note that it will not support:

Paging,
Remote Filtering,
Remote Sorting,
Load on Demand
Adding/Removing records
Updating records
Refreshing
etc.

If you expect you might need some of this functionality you should create a proxy. Most likely, since this server is already a JSON/Ajax server you would extend either the Ajax or Rest proxies and just override the buildReuest function, to add where's, limits, order by's etc.

I've been looking for something to kill some time. I might just do a ux for this.

[j.fowler]

James,

First: Thanks. I appreciate the knowledgeable help.

Second: Supporting paging, refreshing, and a number of your other points is very important. I'll definitely look into creating a proxy.

Third: SequelSphereDB is a _client_ side relational database. If I'm reading the source code right, the proxy would need to be a descendant from Ext.data.proxy.Client. The Ajax or Rest proxies would not work here. I'm guessing the best proxy to inherit from would be Ext.data.proxy.Memory. I'm still looking for some tips or a guide to creating my own proxy. Got any of that?

Thanks again,

john...