Working on my new twitter app, I heavily use SQLite-based templates (kudos to Laurent Jouanneau for that) and implemented search in twits in a so simple way it's fast and beautiful. I would like to share that with you so if you're building an app or extension using SQLite-based templates, you could use it too:

  • let's suppose you have a table of todo list items todolist in your sqlite database desktop.sqlite
  • your table todolist has three columns: text, assignee and importance, all three being varchar
  • you want to be able to search in that beast in all columns

First, you can build a tree representing your table like this:

  <tree seltype="multiple"
        id="myTodoListTree"
        enableColumnDrag="true"
        draggable="true"
        datasources="profile:desktop.sqlite"
        ref="?"
        querytype="storage">
      <treecols>
        <treecol id="cText" label="Text" flex="1" persist="width ordinal"/>
        <splitter class="tree-splitter" persist="ordinal"/>
        <treecol id="cAssignee" label="Assignee" flex="1" persist="width ordinal"/>
        <splitter class="tree-splitter" persist="ordinal"/>
        <treecol id="cImportance" label="Importance" flex="1" persist="width ordinal"/>
      </treecols>
      <template>
        <query>
              SELECT *
              FROM todolist
              WHERE (text LIKE :like
                     OR assignee LIKE :like
                     OR importance LIKE :like)
          <param name="like" id="like" type="string">%</param>
        </query>
        <action>
          <treechildren id="myTodoListTreeChildren">
            <treeitem uri="?">
              <treerow>
                <treecell label="?text"/>
                <treecell label="?assignee"/>
                <treecell label="?importance"/>
              </treerow>
            </treeitem>
          </treechildren>
        </action>
      </template>
  </tree>

Then adding a search field to your UI is trivial, thanks to the new cool features of Firefox 3.5:

<textbox type="search" oncommand="SearchKeyword(this)"/>

where your SearchKeyword function is:

function SearchKeyword(aElt)
{
  var like = document.getElementById("like");
  var value = aElt.value;
  like.textContent = "%" + (value ? value + "%" : "");

  document.getElementById("myTodoListTree").builder.rebuild();
}

That's all. Of course, you could also add a listener on the tree builder to preserve the selection and other cool things but all the magic is above. And it's sooooo simpler than a custom tree view...

/* enjoy */