Tip: ExtJS, Adobe AIR and SQLite

[BenJaminPrater]

I had a hard finding a simple code sample demonstrating ExtJS binding to an AIR SQLite database. I'm posting here for anyone looking for a few lines of code to get them started.

With some help from the sample app, here's the shortest code snippet I could cook up:

myConn = Ext.sql.Connection.getInstance();
myConn.open('clippy2.db.sqlite');
        
ClipStore = Ext.extend(Ext.data.Store, {
    clipsRecord: Ext.data.Record.create([
        {name: 'id',    type:'string'},  // this is your db schema
        {name: 'title', type:'string'},
        {name: 'clip',  type:'string'}
    ]),
    constructor: function(){
        ClipStore.superclass.constructor.call(this, {
            sortInfo:{field: 'title', direction: "ASC"},
            reader: new Ext.data.JsonReader({
                id: 'id',
                fields: this.clipsRecord
            })
        });
        this.conn = myConn;
        this.proxy = new Ext.sql.Proxy(myConn, 'clips', 'id', this);
    }
});

mx = new ClipStore;
mx.load();
air.Introspector.Console.log(ClipStore); // evidence the store has your data!

[aconran]

Take a look at the latest release of Ext.air.

This will be included in the Ext 2.2.1 and Ext 3.x versions but can be downloaded now via the blog.

One new convenience class that was added was the Ext.sql.SQLiteStore which assists in setting up a store quickly and easily.

Example:

var mx = new Ext.sql.SQLiteStore({
   dbFile: 'clippy2.db.sqlite',
   key: 'id',
   fields: [
        {name: 'id',    type:'string'},  // this is your db schema
        {name: 'title', type:'string'},
        {name: 'clip',  type:'string'}
    ]
});

This will also handle creating the table if it does not already exist.

[BenJaminPrater]

Aaron -- well that's just damn lovely! Thanks for the tip. I'll migrate my code over to using that.

Another quick tip for everyone binding SQLite to ExtJS: once you have the database bound to the store, everything else works like it normally would. You can bind it to a grid, add/edit/destroy records, etc. It is automatically talking to the database behind the scenes. You can go into a SQLite editor (like Firefox's SQLite manager) and see the changes. It's really slick not to have to deal with any SQL for very simple operations.

Even though there is a bit of a curve involved in learning ExtJS (which I'm still doing), I've been very, very impressed by the framework. Well thought out, kudos!

[asfez]

var mx = new Ext.sql.SQLiteStore({
   dbFile: 'clippy2.db.sqlite',
   key: 'id',
   tableName : 'toto',
fields: [
        {name: 'id',    type:'string'},  // this is your db schema
        {name: 'title', type:'string'},
        {name: 'clip',  type:'string'}
    ]
});

Don't forget the config option "tableName" else it will not work.

[Ballsacian1]

var mx = new Ext.sql.SQLiteStore({
   dbFile: 'clippy2.db.sqlite',
   key: 'id',
   tableName : 'toto',
fields: [
        {name: 'id',    type:'string'},  // this is your db schema
        {name: 'title', type:'string'},
        {name: 'clip',  type:'string'}
    ]
});

Don't forget the config option "tableName" else it will not work.

I can't seem to find this Class anywhere. Could someone point me towards what I need to call / include in order for Ext.sql.SQLiteStore to work.

[Ballsacian1]

Nevermind my Ext version was lying to me.

[carbotex]

Aaron,

How do I go about joining two or more tables together or any custom query using Ext.sql.SQLiteStore?

[PranKe01]

Hi,

is it possible to use an absolute path for the dbfile? So instead of

dbFile: 'clippy2.db.sqlite',

something like this

dbFile: 'c:/clippy2.db.sqlite',

or

dbFile: 'file:///c:/clippy2.db.sqlite',

???

I am using Adobe Air and my database is not saved in the prog-path but in the AppData folder of windows.

Thanks!

[PranKe01]

Works now... My solution for Adobe Air:

dbFile: air.File.applicationStorageDirectory.resolvePath("clippy2.db").url.replace('\\','\/'),

[Sasha172]

One question... will this work for Sencha Touch too ??