XUL templates with SQLite background

I really like development on Mozilla platform – here is one of the reasons why: I’m played with Boogie (I wrote about it in this post) and I want to upgrade Boogie’s old text database to the SQLite database and I’ve noticed that new Gecko versions enables building XUL templates also with SQLite datasources and not only with with RDF datasources as before so I’ve decided to try this new technology and I really like that I’ve did so.
Here are few notes about how easy I built Boogie using this:

firstly there is no JavaScript to initialize Boogie’s UI – this task is fully served by XUL templates as you can see on image below:

here is the excerpt from db-service.js – this part is initializing database service:



function BoogieDatabaseServicePrototype() {}


BoogieDatabaseServicePrototype.prototype =


{





  // Getter for Mozilla storage service


  _db_srv : null,


  get mDbSrv() {


    if(!this._db_srv)


      this._db_srv = Components.classes["@mozilla.org/storage/service;1"].


          getService(Components.interfaces.mozIStorageService);





    return this._db_srv;


  },





  mDbConnect : null,








  /**


   * Initialize Boogie database


   * 


   * @returns {boolean}


   */


  initialize : function()


  {


    var file = Components.classes["@mozilla.org/file/directory_service;1"].


        getService(Components.interfaces.nsIProperties).


        get("ProfD", Components.interfaces.nsIFile);


    file.append("boogiedb.sqlite");





    this.mDbConnect = this.mDbSrv.openDatabase(file);





    var counter = 0;


    while(!this.mDbConnect.connectionReady)


      if(counter++ == 1000000) return false;





    var create_tables = false;


    try {


      var stmt = this.mDbConnect.createStatement(SET_DB_ENCODING);


      stmt.execute();





      if(!this.createTables())


        return false;


    } catch(e) {}





    return true;


  }, // end initialize()





  // ...





}; // End of BoogieDatabaseServicePrototype

an another excerpt from db-service.js which serves adding/updating/deleting of single items for the single Boogie’s data table):



  // ...





  /**


   * Add note to the database


   * 


   * @param aNote {BoogieNotePrototype}


   * @returns {integer}


   */


  addNote : function(aNote)


  {


    var stmt = this.mDbConnect.createStatement(INSERT_NOTE);


    stmt.bindInt32Parameter(0, aNote.mDeveloperId);


    stmt.bindInt32Parameter(1, aNote.mProjectId);


    stmt.bindUTF8StringParameter(2, aNote.mSummary);


    stmt.bindUTF8StringParameter(3, aNote.mText);


    stmt.bindStringParameter(4, aNote.mSince);


    stmt.execute();





    return this.mDbConnect.lastInsertRowID;


  }, // end addNote(aNote)





  /**


   * Gets single note object with given id from the database


   * 


   * @param aId {integer}


   * @returns {BoogieNotePrototype} Or FALSE


   */


  getNote : function(aId)


  {


    var stmt = this.mDbConnect.createStatement(SELECT_NOTE);


    stmt.bindInt32Parameter(0, aId);





    while(stmt.executeStep()) {


      return new BoogieNotePrototype(stmt.getInt32(0), // note id


                                     stmt.getInt32(1), // developer id


                                     stmt.getInt32(2), // project id


                                     stmt.getUTF8String(3), // summmary


                                     stmt.getUTF8String(4), // text


                                     stmt.getString(5)); // since


    }





    return null;


  }, // end getNote(aId)





  /**


   * Returns total count of notes


   *


   * @returns {integer}


   */


  getNotesCount : function()


  {


    var stmt = this.mDbConnect.createStatement(NOTES_COUNT);





    while(stmt.executeStep())


      return stmt.getInt32(0);





    return null;


  }, // end getDevelopersCount()





  /**


   * Returns array with all notes that are stored in the database.


   * 


   * @return {array} Returns instances of BoogieNotePrototype.


   */


  getNotes : function()


  {


    var notes = new Array();


    var stmt = this.mDbConnect.createStatement(SELECT_NOTES);





    while(stmt.executeStep()) {


      var note = new BoogieNotePrototype(stmt.getInt32(0), // note id


                                         stmt.getInt32(1), // developer id


                                         stmt.getInt32(2), // project id


                                         stmt.getUTF8String(3), // summmary


                                         stmt.getUTF8String(4), // text


                                         stmt.getString(5)); // since


      notes.push(note);


    }





    return notes;


  }, // end getNotes()





  /**


   * Update note with the given new one


   * 


   * @param aOldNote {BoogieNotePrototype}


   * @param aNewNote {BoogieNotePrototype}


   * @returns {boolean}


   */


  updateNote : function(aOldNote, aNewNote)


  {


    var stmt = this.mDbConnect.createStatement(UPDATE_NOTE);


    stmt.bindInt32Parameter(0, aNewNote.mDeveloperId);


    stmt.bindInt32Parameter(1, aNewNote.mProjectId);


    stmt.bindUTF8StringParameter(2, aNewNote.mSummary);


    stmt.bindUTF8StringParameter(3, aNewNote.mText);


    stmt.bindStringParameter(4, aNewNote.mSince);


    stmt.bindInt32Parameter(5, aOldNote.mId);


    stmt.execute();





    return true;


  }, // end updateNote(aOldNote, aNewNote)





  /**


   * Remove note with the given identifier from the database


   * 


   * @param aId {integer}


   * @returns {BoogieNotePrototype} Or FALSE.


   */


  removeNote : function(aId)


  {


    var stmt = this.mDbConnect.createStatement(REMOVE_NOTE);


    stmt.bindInt32Parameter(0, aId);


    stmt.execute();





    return note;


  }, // end removeNote()





  /**


   * Removes all note


   *


   * @returns {boolean}


   */


  removeAllNotes : function()


  {


    try {


      this.removeAll("notes");


    } catch(e) {


      Components.utils.reportError(e);


      return false;


    }





    return true;


  }, // end removeAllNotes()





  // ...

This is the most simplest example of using XUL templates – here we need to render menulist with developers:



<menulist id="developer-menulist"


          oncommand="developerMenulistCommand();"


          datasources="profile:boogiedb.sqlite"


          querytype="storage" ref="*"


          persist="value">


  <template>


    <query>


      select idd, surname, firstname from developers


    </query>


    <action>


      <menupopup>


        <menuitem uri="?" label="?surname ?firstname" value="?idd"/>


      </menupopup>


    </action>


  </template>


</menulist>

And here is a little more complicated example – this XUL code renders tree with notes listed – you can notice that SQL query uses more tables to collect the requested result:



<tree id="boogieNotesTree" flex="1"


      hidecolumnpicker="true" selType="single"


      datasources="profile:boogiedb.sqlite" ref="*"


      querytype="storage" flags="dont-build-content">





  <treecols>


    <!-- Here are some treecols -->


  </treecols>


  <template>


    <query>


      SELECT


        a.idn,


        a.summary,


        c.surname AS developer_surname,


        c.firstname AS developer_firstname,


        b.name AS project_name,


        a.since


      FROM notes AS a


      LEFT JOIN projects AS b ON b.idp = a.idp


      LEFT JOIN developers AS c ON c.idd = a.idd


      WHERE 1


    </query>


    <action>


      <treechildren>


        <treeitem uri="?">


          <treerow value="?idn">


            <treecell label="?summary"/>


            <treecell label="?developer_surname ?developer_firstname"/>


            <treecell label="?project_name"/>


            <treecell label="?since"/>


          </treerow>


        </treeitem>


      </treechildren>


    </action>


  </template>


</tree>

the latest example is the most complex – it uses complicated SQL query with parameters:



<tree anonid="bugview-tree" flex="1" enableColumnDrag="true" 


      seltype="single" datasources="profile:boogiedb.sqlite" ref="*"


      querytype="storage" flags="dont-build-content">


  <!-- *** treecols *** -->


  <template>


    <query>


      SELECT


        a.idb,


        b.name AS type,


        c.name AS project,


        d.name AS component,


        e.surname AS developer_surname,


        e.firstname AS developer_firstname,


        f.name AS status,


        g.name AS resolution,


        h.name AS severity,


        i.name AS priority,


        j.name AS version_from,


        k.name AS version_to


      FROM bugs AS a


      LEFT JOIN types AS b ON b.idt = a.idt


      LEFT JOIN projects AS c ON c.idp = a.idp


      LEFT JOIN components AS d ON d.idc = a.idc


      LEFT JOIN developers AS e ON e.idd = a.idd


      LEFT JOIN statuses AS f ON f.ids = a.ids


      LEFT JOIN resolutions AS g ON g.idr = a.idr


      LEFT JOIN severities AS h ON h.idsv = a.idsv


      LEFT JOIN priorities AS i ON i.idpr = a.idpr


      LEFT JOIN versions AS j ON j.idv = a.idv1


      LEFT JOIN versions AS k ON j.idv = a.idv2


      WHERE


        a.idp = :boogieProjectID


      <param id="bugsViewProjectID" name="boogieProjectID">1</param>


    </query>


    <action>


      <!-- *** treechildren *** -->


    </action>


  </template>


</tree>

Source files mentioned above:

Usefull links:

Published 16.3.2008

Filed in Blog and tagged Development, XUL

No replies

Write comment - Trackback

ondrejd's blog