7.5. The Code

The code for this application is broken out into multiple source files, similarly to how the previous few applications have been in that each file, generally, relates to a particular UI element.

Naturally, though, the first one we're looking at doesn't!

7.5.1. SQLWorkbench.js

Now we start with the true code for this application, beginning with the contents of SQLWorkbench.js. You can see the UML diagram for this class in Figure 7-4. Its members include 8 fields and 17 methods. Many of these are not defined in SQLWorkbench.js, so my intention is to introduce each as they are encountered in whatever source file we happen to be examining at the time.

As I mentioned earlier, this "class" is actually a namespace, which you can see created with the first executable statement in this file:

Ext.namespace("SQLWorkbench", "SQLWorkbench.UIObjects",
  "SQLWorkbench.UIEventHandlers", "SQLWorkbench.Data");

Not only is the SQLWorkbench namespace being created, but so too are some sub-namespaces nested underneath SQLWorkbench. These mimic what was seen in the previous applications. To reiterate, the SQLWorkbench.UIObjects namespace contains the config objects for the various UI components used in the application, SQLWorkbench.UIEventHandlers contains the JavaScript functions called in response to various UI events, and SQLWorkbench.Data is where we find things like Record and Store definitions.

In this application we list available databases that the user can play with, but within the context of this application it is unfortunately a fact that Gears does not allow you to get such a list automatically. Therefore, users have to tell us what database(s) they want to work with, and we probably should store that information somewhere. It felt a little odd to have to maintain a database to store a list of databases, though! So, I opted to go with cookies, and so we need an Ext.state.CookieProvider in this application:

SQLWorkbench.cookieProvider = new Ext.state.CookieProvider({
  expires : new Date(new Date().getTime() + (1000 * 60 * 60 * 24 * 365))
});

As you saw earlier, this defines a CookieProvider so that the cookies persist for one year. I suppose it's not too nice for users to have to reenter the names of all their databases every year, but it seems like a reasonable period of time (better than having to reenter them once a week for example, or even worse, every time the application is used).

Next up we have the init() method that we know is called via Ext.onReady() from index.htm. Its job is to "prime the pump," so to speak, to get the application up and running and ready for user interaction:

SQLWorkbench.init = function() {

  new Ext.Toolbar({
    id : "toolbar", renderTo : "divToolbar", items : [
      {
        text : "List Databases",
        icon : "img/ListDatabases.gif", cls : "x-btn-text-icon",
        handler : function() {
          SQLWorkbench.listDatabases();
        }
      },
      "-",
      {
        text : "Query Tool",
        icon : "img/QueryTool.gif", cls : "x-btn-text-icon",
        handler : function() {
          SQLWorkbench.showQueryTool();
        }
      },
      "-",
      {
        text : "Help", icon : "img/Help.gif", cls : "x-btn-text-icon",
        handler : function() {
          SQLWorkbench.showHelp(0);
        }
      },
   "-",

{
        text : "About", icon : "img/About.gif", cls : "x-btn-text-icon",
        handler : function() {
          SQLWorkbench.showAbout();
    }
   }
  ]
 });

 SQLWorkbench.listDatabases();

}

First, the Toolbar is built. The renderTo attribute in the config object tells Ext JS to put this Toolbar into the divToolbar <div> we saw in index.htm. Again, while most of the applications in this book take a very Ext JS-specific approach (meaning the UI is built as an explicit hierarchy of components in JavaScript code), you can in fact build an entire UI this way instead. If you, for example, want to have a giant table structure on your page, or a CSS-based layout, and then simply create Ext JS components and insert them onto the page where you want, you absolutely can. You will, however, give up some of the power of Ext JS, things like users being able to dynamically resize page elements and things of that nature. But sometimes you don't need any of that, so the choice is often completely up to you.

CONTROVERSY/PHILOSOPHY: CODE-BASED LAYOUTS VS. MARKUP+CSS Many people find the idea of designing the UI of an application in code to be controversial. I must admit, I do to some extent. The argument against it most often heard centers around the idea of page "designers" versus page "developers." The thought is that you have graphic artists doing the design, and often that means they are writing the simple markup and CSS that forms the structure of a page. Then, the developer comes along and adds the functionality to the layout. This separation of concerns allows each group to focus on their core competencies. It's a compelling argument. However, the reality is that not nearly as many development environments work that way as we might like. More often than not, it is the developer doing it all. There may still be guidance from a graphic artist, but it is still left to the developer to implement both the functionality and the layout. The argument frequently heard is that creating a UI in code is intermingling things that shouldn't be intermingled. After all, markup defines the structure of how some data is presented, and CSS defines what it all looks like. If you have a database, and you want to display its data, you mark up the data using HTML's table features, and then style the table using CSS. The code's job is to get the data and present it to the HTML and CSS for display. This too is a pretty compelling argument. However, using code to do layout allows for a level of dynamic ability that is hard to achieve otherwise. Markup and CSS are, for the most part, pretty static. JavaScript obviously is not. Therefore, a layout using JavaScript can allow for things that are difficult or impossible to achieve with just HTML and CSS—things like reflowing of content on resize events, or manipulation of layout, or dynamic creation of content. In the end, I won't try to give you a right or wrong answer here mainly because I don't believe there is one. Obviously, writing a book on Ext JS implies I have an affinity for JavaScript-based layouts, and I suppose that's a fair supposition, but I've also publicly argued against them in the past. I could give the typical cop-out answer and simply say use the right tool for the job, and in fact, I think I'll do exactly that! I will say this, however: doing RIA development with the intent of mimicking desktop app–like functionality pretty well requires code-based UI layouts, so take that for what you will!

The About Window shown in Figure 7-5 is a result of executing the showAbout() method, which contains this code:

SQLWorkbench.showAbout = function() {

  var aboutWindow = Ext.getCmp("dialogAbout");
  if (aboutWindow) {
    aboutWindow.show(Ext.getDom("divSource"));
  } else {
    new Ext.Window({
      applyTo : "dialogAbout", id : "dialogAbout", closable : true,
      width : 400, height : 320, minimizable : false,
      resizable : false, draggable : false, closeAction : "hide",
      buttons : [{
        text : "Ok",
        handler : function() {
          Ext.getCmp("dialogAbout").hide();
        }
      }]
    }).show(Ext.getDom("divSource"));
  }

}

Recall in index.htm that this Window was described via markup. Of course, that in and of itself does nothing for us until we create an actual Window from it!

The first step is to see if the Window has already been created, and if it has, we simply call show() on it, passing it a reference to divSource. If it doesn't already exist, though, it is created here.

Linking the newly created Window to the markup in index.htm is a simple matter of pointing the applyTo config attribute of the Ext.Window constructor to the DOM node where the Window definition is. The Window is given an id of dialogAbout so that we can reference it later in the Ok Button's handler function to hide the Window by calling its hide() method. The Window is closable (closable:true) and cannot be minimized, resized, or dragged (minimizable:false, resizable:false and draggable:false correspondingly). Finally, it is given a size of 400 by 320 pixels and is shown immediately upon creation by calling show() and passing it a reference to the divSource <div> so we get our nice animation effect.

7.5.2. StoresAndRecords.js

The StoresAndRecords.js file contains a couple of Record and Store definitions, although not too many in this particular application. In Figure 7-6 you can see the two Record types defined: DatabaseRecord and TableRecord.

The DatabaseRecord describes an SQLite database. All we need is the name of the database and the tables it contains. The tables field is something new. Previously we've seen Record fields as simple types like strings and numbers, but here it's an Ext.data.Store, based on the diagram at least. There is no limitation on what types a field can be in a Record. A Store doesn't especially care, although it may not know what to do with some types, so you may have to write your own Store that does. In this case, however, these Records are only used with the UI; they aren't read from or written to a persistent data store of any kind, so there's none of that concern.

The code that creates this Record is as follows:

SQLWorkbench.Data.DatabaseRecord = Ext.data.Record.create([
  { name : "name", mapping : "name" },
  { name : "tables", mapping : "tables" }
]);

Notice that there is no type attached to either field, again supporting the notion that you can use any type you wish, and in true JavaScript form you could even dynamically change the "type" of a field (although that's typically not a smart thing to do, as much with Records as in JavaScript in general).

The TableRecord is similarly defined for describing a table within a database:

SQLWorkbench.Data.TableRecord = Ext.data.Record.create([
  { name : "databaseName", mapping : "databaseName" },
  { name : "name", mapping : "name" },
  { name : "sql", mapping : "sql" },
  { name : "tableDetails" }
]);

For a table we need to know what database it belongs to, hence the databaseName field, which maps to a given DatabaseRecord's name field. The name field itself is the name of the table, and the sql field is the SQL statement that was used to create the table. As we'll see later, this is a bit of information that we can coax out of SQLite and it is the key to making this whole application worth anything! Finally, the tableDetails field, similar to the tables field in the DatabaseRecord, isn't a simple type. It will have a value that is a JavaScript object that will contain information describing the table in detail (information that will be directly derived from the value of the sql field). There will be more to say on this last field later, but for now that gives you a good idea of what it and the others are for.

As you probably would have guessed, we need to have a Store for our DatabaseRecords to which we can bind the Grid used to display them in the Databases Window:

SQLWorkbench.Data.databasesStore = new Ext.data.Store({});

Again, since there's no requirement to persist these Records, there are no event handlers that need to be attached, so that single line of code is all we need.

The final bit of code looks like this:

SQLWorkbench.Data.columnTypeStore = new Ext.data.Store({});
var columnTypeVals = [ "INTEGER", "DOUBLE", "FLOAT", "REAL", "CHAR", "TEXT",
  "VARCHAR", "BLOB", "NUMERIC", "DATETIME" ];
  for (var i = 0; i < columnTypeVals.length; i++) {
  SQLWorkbench.Data.columnTypeStore.add
    (new Ext.data.Record( { optVal : columnTypeVals[i] } )
  );
}

This is a Store with some hardcoded values that will be bound to the ComboBoxes used to select the type of a field when creating a table. The strings listed in the columnTypeValues array are the valid SQLite data types. That array is iterated over and add() is called on the columnTypeStore for each string. This all executes immediately when this file is loaded, as part of loading index.htm. Therefore, this Store is ready to go when the UI is built (which won't happen until the user decides to build a new table), so the ComboBoxes immediately have the values in them upon creation.

7.5.3. DatabasesWindow.js

Now that we have some of the groundwork laid, let's look at our first big piece of functionality: the Databases Window, the code for which is found in the aptly named DatabasesWindow.js file. This Window is what you see in Figure 7-7. Its job is to list all the databases that SQLWorkbench knows about that the user can work with and give that user the ability to choose one to work on, as well as add new ones and remove existing ones.

The first bit of code in this file is simply a field definition:

SQLWorkbench.lastSelectedDatabase = null;

Generally, once users select a database, this Window doesn't really care which one they selected. The code will open the Tables Window for the selected database and that's about the extent that the code in this file cares about database selection. One important exception is when the user wants to remove a database. In that case, obviously, the code needs to know which database was selected, and that's precisely what this field does: it stores the name of the last selected database.

Following that field definition is the listDatabases() method, which is called to display this Window and, you know, list databases!

SQLWorkbench.listDatabases = function() {

  var databasesWindow = Ext.getCmp("databasesWindow");
  if (databasesWindow) {
    databasesWindow.close();
  }

First, we check to see if the Window is already open, and if so, it is closed. In this way we have essentially a poor man's refresh function, and it means that whatever is displayed in this Window is current—at least since the last time this method was called.

The method continues thusly:^[]

^[] Thusly? Who talks like that? It's like the expression "rue the day." Who talks like that? And yes, that's a Real Genius reference for those "in the know" For those "outside the know" (now there's an expression to use more often!), see http://www.imdb.com/title/tt0089886/.

SQLWorkbench.Data.databasesStore.removeAll();
var i = 0;
var nextCookie =
  SQLWorkbench.cookieProvider.get("SQLWorkbench_db_" + i);
while (!Ext.isEmpty(nextCookie)) {
  SQLWorkbench.Data.databasesStore.add(
    new SQLWorkbench.Data.DatabaseRecord(
      { name : nextCookie, tables : null }, nextCookie
    )
  );
  i = i + 1;
  nextCookie =
    SQLWorkbench.cookieProvider.get("SQLWorkbench_db_" + i);
}

This Window has a Grid within it, and that's where the databases are listed. So, we need a Store to contain DatabaseRecords that we can bind to that Grid. To do that, we begin by calling removeAll() on the databasesStore so we're starting with a clean slate. Next, we try retrieving a cookie with the name SQLWorkbench_db_0. This name is formed by appending an index value (starting with 0) to the static string SQLWorkbench_db. We use the Ext.isEmpty() function to see if the cookie was retrieved. If it was, then a new DatabaseRecord is created and the name field is set to the value of the retrieved cookie, which is stored in the variable nextCookie. Also, the id of the record is set to the database name. Then, we bump up the index counter and try to retrieve the next cookie. The loop continues until a cookie isn't found, which means we've read in all the previously saved databases from cookies.

Once that's done, we can go ahead and construct the Window:

new Ext.Window({
  title : "Databases", width : 300, height : 200, constrain : true,
  animateTarget : "divSource", id : "databasesWindow", maximizable : false,
  layout : "fit", x : 5, y : 40,
  bbar : [
    { text : "Add Database",
      icon : "img/AddDatabase.gif", cls : "x-btn-text-icon",
      handler : function() {
        SQLWorkbench.addDatabase();
      }
    },
    "-",
    { text : "Remove Database",
      icon : "img/RemoveDatabase.gif", cls : "x-btn-text-icon",
      handler : function() {
        SQLWorkbench.removeDatabase();
      }
    }
  ],

This is a straightforward Window definition. Note the use of the constrain config attribute, which ensures that the Window cannot be dragged off the edges of the page. Also note how the x and y attributes are used to position the Window in the upper-left corner of the page just below the Toolbar (which is why, if you recall from earlier, I explicitly set the height of the Toolbar even though I said it wasn't absolutely necessary... doing that allows me to know how far down the page I need to position this Window so as to not overlap the Toolbar). We use the bbar attribute to add some buttons to the bottom Toolbar. The two buttons added are for adding and removing a database from the list, which also do double duty by allowing the user to create or delete databases physically from SQLite. Clicking the Add Database button will add the database to the list if the database exists, but it will create the database in SQLite automatically if it doesn't exist. Likewise, clicking Remove Database not only removes the database from the list but actually removes it from the SQLite database. (There's no way to simply remove the database from the list, a point my diligent technical reviewer correctly made, so maybe that's your first enhancement challenge to tackle!)

The next part of the Window definition is the Grid:

items : [
  { xtype : "grid", border : false, stripeRows : true,
    store : SQLWorkbench.Data.databasesStore, hideHeaders : true,
    autoExpandColumn : "colName",
    sm : new Ext.grid.RowSelectionModel( { singleSelect : true } ),
    columns : [
      { header : "Name", sortable : true, dataIndex : "name",
        id : "colName" }
    ],
    listeners: {
      rowclick : {
        fn : function(inGrid, inRowIndex, inEventObject) {
          var databaseName =
            inGrid.getSelectionModel().getSelected().get("name");
          SQLWorkbench.lastSelectedDatabase = databaseName;
          SQLWorkbench.listTables(databaseName);
          inGrid.getSelectionModel().clearSelections();
        }
       }
      }
     }
    ]
  }).show();

};

We've seen a bunch of Grids before, so there aren't any surprises in the basic structure here. The rowclick event handler is different. Here we begin by getting the name of the selected database by first getting the selection model of the Grid, then calling its getSelected() method to get the DatabaseRecord for the row that was clicked, and then getting the name field of that record. Once we have that, we set the lastSelectedDatabase field to the database name so that we know which to remove if the user clicks that button. Then, the listTables() method is called, which will open a Window listing the tables in the selected database. Finally, the code clears the selection in this Grid. The purpose of this Window and Grid is for the user to select a database. However, since more than one database at a time can be open, leaving the last selected item in the Grid didn't quite feel right to me, and that's the reason this clearing of the selection is done.

When the user clicks the Add Database button, the addDatabase() method is called (bet you didn't see that coming!). Here comes that method now:

SQLWorkbench.addDatabase = function() {

  Ext.MessageBox.prompt("Add Database",
    "Please enter the name of the database you wish to add.<br><br>Note that " +
    "if the database does not currently exist it will be created.<br>",
   function(inBtnID, inVal) {
     if (inBtnID == "cancel") { return; }
     if (inVal != null) { inVal = inVal.trim(); }
     if (!Ext.isEmpty(inVal)) {
       var db = google.gears.factory.create("beta.database");
       db.open(inVal);
       db.close();
       SQLWorkbench.Data.databasesStore.add(
         new SQLWorkbench.Data.DatabaseRecord({
           name : inVal
         }, inVal)
       );
       SQLWorkbench.updateDatabaseCookies();
       SQLWorkbench.listDatabases();
       Ext.MessageBox.hide();
     }
    }
  );

};

In Figure 7-8 you can see the result of this code, which starts by asking the user to supply the name of the database to add. This is done by using the Ext.MessageBox.prompt() method, similar to JavaScript's built-in prompt() method. If the user clicks Ok (clicking Cancel or manually closing the MessageBox aborts), then the name entered is trimmed and we simply open the database. Gears and SQLite are kind enough to create the database if it doesn't already exist, and if it does, no harm is done and we simply close the database right away. Once that's done, the only things left to do are to add a DatabaseRecord to the databasesStore for the new database, write out our cookies by calling the updateDatabaseCookies() method (which we'll look at shortly), and finally, call listDatabases() to re-create the Databases Window, but now with the new database listed in it.

The removeDatabase() method is next. It looks a bit complex at first but it isn't too bad:

SQLWorkbench.removeDatabase = function() {

  if (Ext.isEmpty(SQLWorkbench.lastSelectedDatabase)) { return; }
  Ext.MessageBox.confirm("Confirm Database Removal",
    "This will remove the " + SQLWorkbench.lastSelectedDatabase + " database " +
    "from the list of available database AND WILL ALSO physically remove the " +

"database from the SQLite directory structure. ALL DATA IN THE DATABASE " +
    "WILL BE LOST! Are you absolutely sure about this?",
    function(inButtonClicked) {
      if (inButtonClicked == "yes") {
        var db = google.gears.factory.create("beta.database");
        db.open(SQLWorkbench.lastSelectedDatabase);
        db.remove();
        var tablesWindow =
          Ext.getCmp("tablesWindow~" + SQLWorkbench.lastSelectedDatabase);
        if (tablesWindow) { tablesWindow.close(); }
        var databaseRecord = SQLWorkbench.Data.databasesStore.getById(
          SQLWorkbench.lastSelectedDatabase);
        var tables = databaseRecord.get("tables");
        tables.each(function(nextRecord) {
          var tableDetailsWindow = Ext.getCmp("tableWindow_" +
            nextRecord.get("name")).close();
          if (tableDetailsWindow) { tablesDetailWindow.close(); }
        });
        SQLWorkbench.Data.databasesStore.remove(
          SQLWorkbench.Data.databasesStore.getById(
            SQLWorkbench.lastSelectedDatabase));
        SQLWorkbench.updateDatabaseCookies();
        SQLWorkbench.lastSelectedDatabase = null;
        SQLWorkbench.listDatabases();
        Ext.MessageBox.hide();
      }
    }
  );

}

First, we ensure that a database has been selected and abort if not. Next, we pop a confirmation using the Ext.MessageBox.confirm() method, which you can see in Figure 7-9. The text of the message asks users if they are sure they want to delete the database. If they click Yes, then we're off to the races. First, the database is opened and then the remove() method on it is called. This is a Gears-supplied method that takes care of destroying the currently opened database for us. So, that part is quite easy!

Next, we need to close the Tables Window for the database, if one was open. Ext.getCmp() is used and is passed the constructed ID of the Window. Assuming it is opened, the close() method is called on it and it's history.

Then we have to do the same thing for the Table Detail Windows that might be opened for the tables in this database, so we retrieve the DatabaseRecord from the databasesStore for the database being removed. This is done because we need to get at the tables field, which lists the tables in this database, if any. Once we have a hold of that store, we use its each() method to iterate over the TableRecords in it (remember that's the field that is itself a DataStore). For each we try to get a reference to the Table Details Window for that table, and if found we close it.

Once that's done we can go ahead and remove the DatabaseRecord from the databases Store. Then there's just some housekeeping to do: a call to updateDatabaseCookies() will cause the cookie for the database to be deleted; lastSelectedDatabase is set to null since the last selected database was just removed and therefore is no longer a valid value; and finally, listDatabases() is called to effectively refresh the Databases Window on the screen.

The last method in this source file is the one you've seen a couple of times now: updateDatabaseCookies(). As you can see, there's not much to it:

SQLWorkbench.updateDatabaseCookies = function() {

  var i = 0;
  var nextCookie =
    SQLWorkbench.cookieProvider.get("SQLWorkbench_db_" + i);
  while (!Ext.isEmpty(nextCookie)) {
    SQLWorkbench.cookieProvider.set("SQLWorkbench_db_" + i, null);
    i = i + 1;
    nextCookie =
      SQLWorkbench.cookieProvider.get("SQLWorkbench_db_" + i);
  }

i = 0;
  SQLWorkbench.Data.databasesStore.each(function(nextRecord) {
    SQLWorkbench.cookieProvider.set("SQLWorkbench_db_" + i,
      nextRecord.get("name"));
    i = i + 1;
  });

}

The values corresponding to databases are stored in cookies named SQLWorkbench_db_x, where x is a number starting at 0. "Updating" the cookies, whether as a result of adding a new database or removing one, is a two-step process. First, a loop is entered into that keeps trying to get the next cookie in sequence. For each one found it is set to a value of null, which has the effect of removing it from the browser. Then, the databasesStore is iterated over using its each() method and for each DatabaseRecord a cookie is written out. In other words, the "update" is a cheat: it's really just re-creating all the cookies each time! I'm not sure there are too many alternate ways to pull this off if you're using cookies... you could just write out a single cookie with something like a comma-separated list of database names, but this way was more fun!

7.5.4. TablesWindow.js

The TablesWindow.js file contains all the code for the Tables Window (bet you didn't see that coming!). This Window, shown in Figure 7-10, is where the tables found in a selected database are listed.

Any table listed can be clicked to open a Table Details Window for it, and a new table can be created by clicking the Create New button on the bottom Toolbar. Let's take a look at the listTables() method, which is called when a database is selected from the Databases Window:

SQLWorkbench.listTables = function(inDatabaseName) {

  var tablesWindow = Ext.getCmp("tablesWindow~" + inDatabaseName);
  if (tablesWindow) {
    tablesWindow.close();
  }

First we check to see if a Tables Window is already opened for the selected database. The ID of any Tables Window is in the form tablesWindow~<databaseName>, where <databaseName> is replaced with the name of the selected database. If such a Window is already opened, we go ahead and close it. This makes clicking a database double as a refresh function.

The next step is to get the list of tables in the selected database. To do that we'll have to play with Gears and SQLite a bit:

var db = google.gears.factory.create("beta.database");
db.open(inDatabaseName);

var rs = db.execute (
  "SELECT name, sql FROM sqlite_master where type="table";"
);

Every SQLite database has a special read-only table named sqlite_master in it. The data in this table describes the database schema. The structure of this table is always what is shown in Table 7-1.

Table 7.1. The Structure of the sqlite_master Table

Field	Description
type	Tells the type of entity the record describes. For our purposes, the only value we care about is table.
name	Gives you the name of the entity the record describes. For our purposes, that means the name of the table.
tbl_name	When the value of type is index, the record is describing a table index. In that case, tbl_name gives the name of the table.
rootpage	I couldn't find definitive documentation of what this is anywhere, but I'm putting my money on it being the code page of the described table.
sql	This is the CREATE TABLE or CREATE INDEX query that created the described table or table index.

Since sqlite_master is just a table in the database, we can query it for information, and that's exactly what the snippet of code does: it queries for all records from sqlite_master where the type is table and it returns the name and sql fields, since that's all we care about here.

The next step is to get the DatabaseRecord associated with the database we just queried for tables:

var databaseRecord = SQLWorkbench.Data.databasesStore.getById(inDatabaseName);
var foundTables = databaseRecord.get("tables");
if (!foundTables) {
  foundTables = new Ext.data.Store({});
}
foundTables.removeAll();

The purpose behind this is that we're going to populate the tables field of the Record, which you'll recall from earlier is an Ext JS DataStore, with TableRecord objects. So, we request the DatabaseRecord from the SQLWorkbench.Data.databaseStores by using its getById() method, passing in the name of the database we want. Once we have that, we get its tables field. If the returned value is null, which happens the first time the database is selected from the Databases Window, we create a new Ext.data.Store(). If on the other hand we already have a Store, then we call removeAll() on it since we'll be repopulating it next, as you can see here:

while (rs.isValidRow()) {
  foundTables.add(new SQLWorkbench.Data.TableRecord({
    databaseName : inDatabaseName, name : rs.field(0), sql : rs.field(1)
  }, rs.field(0)));
  rs.next();
}
rs.close();
db.close();

We iterate over the ResultSet returned by the query of sqlite_master and for each row we create a new SQLWorkbench.Data.TableRecord. The databaseName field is set, as is the name and sql fields with the values returned by the query. The TableRecord is keyed by the table name as well so that we can retrieve it by name easily later. This TableRecord is added to the foundTables DataStore:

databaseRecord.set("tables", foundTables);

Finally, foundTables is added as the value of the tables field in the DatabaseRecord and we're good to go.

At this point we have all the information we need ready to go, so now it's just a matter of creating the Window:

new Ext.Window({
  title : inDatabaseName + " : Table List", width : 300, height : 200,
  constrain : true, animateTarget : "divSource", maximizable : false,
  layout : "fit", id : "tablesWindow~" + inDatabaseName,
  bbar : [

{ text : "Create New",
  icon : "img/CreateNew.gif", cls : "x-btn-text-icon",
  handler : function() {
    SQLWorkbench.createTable(inDatabaseName);
  }
 }
],
items : [
  { xtype : "grid", border : false, stripeRows : true,
    store : foundTables, hideHeaders : true,
    autoExpandColumn : "colName",
    sm : new Ext.grid.RowSelectionModel( { singleSelect : true } ),
    columns : [
      { header : "Name", sortable : true, dataIndex : "name",
        id : "colName" }
    ],
    listeners: {
      rowclick : {
        fn : function(inGrid, inRowIndex, inEventObject) {
          SQLWorkbench.listTableDetails(inDatabaseName,
            inGrid.getSelectionModel().getSelected().get("name"));
            inGrid.getSelectionModel().clearSelections();
        }
       }
      }
     }
    ]
  }).show();
};

The Window has a bottom Toolbar, courtesy of the bbar attribute, that has a single Create New button that calls the SQLWorkbench.createTable() method. The name of the database that was passed in to the listTables() method is now passed in to the createTable() method, which means we have a closure here, as discussed in Chapter 3.

The object in the items array, which winds up being the main content of the Window, is a GridPanel that is bound to the foundTables Store created previously. That's how we get the list of tables to show up. Clicking a row in the Grid results in the rowclick event firing and the handler defined here executing. This handler calls the SQLWorkbench.listTableDetails() method, passing in the name of the table to display details for, which is derived from the row that was clicked. We take the inGrid argument passed to the callback and call its getSelectionModel() method to get a hold of its SelectionModel. From there we can get the selected TableRecord by calling the getSelected() method, and then we just get the value of the name field to pass along to the listTableDetails() method.

7.5.5. CreateTableWindow.js

When users click the Create New button in the Tables Window, the Create Table Window appears, where they can enter the details for the new table they want to create. This Window is shown in Figure 7-11.

As you can see, users enter the name of the table at the top, and then enter details for up to 20 columns in the table. For each, they enter a name, the data type of the column, whether the column is the primary key field of the table, whether or not nulls are allowed in the column, and what the default value should be for that column.

When the user clicks the Create New button in the Tables Window, the createTable() method is called. I'll chunk-ify this method a bit so it's easier to digest:

SQLWorkbench.createTable = function(inDatabaseName) {

  var createTableWindow = new Ext.Window({
    animateTarget : "divSource", id : "createTableWindow", autoScroll : true,
    draggable : true, resizable : true, shadowOffset : 8, width : 700,
    height : 500, layout : "fit", constrain : true, title : "Create Table",
    maximizable : true,
    items : [
      { xtype : "form", labelWidth : 100, id : "createTableForm",
        bodyStyle : "padding:4px;overflow:scroll;",
        items : [
          { xtype : "textfield", width : 250, fieldLabel : "New table name",
            name : "createTableName" },

The basic Window is constructed first. This one, like all the others, is constrained to the boundaries of the browser's content area, but it can be dragged and resized however users like. It can even be maximized if they wish. Within this Window we create a FormPanel, and the first field we add is a simple TextField for entering the name.

Note, however, that you do not see the 20 rows corresponding to the column details we know the user can enter. If you look at the screenshot again, you'll see that this is in some sort of tabular layout, complete with column headers. We haven't seen a form that looks like that before, so how is that pulled off? The answer begins with this next chunk of code:

{ xtype : "panel", layout : "table", layoutConfig : { columns : 5 },
  id : "createTablePanel", border : false,
  items : [
    { html :
      "<div style="background-color:#dfe8f6;">Name</div>",
      cellCls : "cssTableHeader" },
    { html :
      "<div style="background-color:#dfe8f6;">Type</div>",
      cellCls : "cssTableHeader" },
    { html :
      "<div style="background-color:#dfe8f6;">P. Key?</dv>",
      cellCls : "cssTableHeader cssTableCentered" },
    { html :
      "<div style="background-color:#dfe8f6;">Not Null?</div>",
      cellCls : "cssTableHeader cssTableCentered" },
    { html :
      "<div style="background-color:#dfe8f6;">Default</div>",
      cellCls : "cssTableHeader" }
   ]
 }
],

So, we're using the TableLayout to create a table, which makes sense given what you see in the screenshot. The interesting thing to note is that this Panel using the TableLayout is an element in the items collection of the FormPanel, so any form-type fields we add here are still part of the form, even though they are not directly nested under the FormPanel itself (i.e., not elements in the items array directly, as the TextField for entering the table's name is).

But even still, all we see here are some header definitions! Now, the header definitions are interesting because they are just simple elements with some HTML defined. Each is a <div> with a background-color set and a cssTableHeader CSS class applied to it. Some of them actually have two classes applied, cssTableHeader and cssTableCentered. If you look back at the CSS definition you'll see that cssTableCentered is the one with text-align set to center. So, the data in these columns will be centered, and since they're CheckBoxes it makes sense because they look better as centered than using the default left alignment.

So, while that's interesting, it still doesn't explain how those 20 rows of data entry fields get on the screen! I'll pull one of my "we'll get to that shortly" tricks... and in this case I do mean shortly, but first we have one more chunk to see:

bbar : [
     { text : "Ok", formBind : true,
       icon : "img/Ok.gif", cls : "x-btn-text-icon",
       handler : function() {
         SQLWorkbench.createTableExecute(inDatabaseName);
       }
     },
     "-",
     { text : "Cancel", icon : "img/Cancel.gif", cls : "x-btn-text-icon",
       handler : function() { Ext.getCmp("createTableWindow").close(); }
     }
    ]
   }
  ]
});

This defines the bottom Toolbar on the Window where our Ok and Cancel buttons appear. Clicking Ok called the createTableExecute() method, passing it the name of the database this Window was opened for. We'll look at that method very soon as well. The Cancel button simply gets a reference to the Window and closes it.

So now we come to the mythical, magical beast that is responsible for those 20 rows we just know had to be here somewhere!

var createTablePanel = Ext.getCmp("createTablePanel");
for (var i = 0; i < 20; i++) {
  createTablePanel.add({ xtype : "textfield", hideLabel : true,
    width : 150, ctCls:"cssTableCell",
    name : "createTable_columnName_" + i });
  createTablePanel.add({ xtype : "combo", width : 100, editable : false,
    triggerAction : "all", mode : "local", valueField : "optVal",
    displayField : "optVal", store : SQLWorkbench.Data.columnTypeStore,
    ctCls : "cssTableCell", name : "createTable_columnType_" + i });

createTablePanel.add({ xtype : "checkbox", hideLabel : true,
    ctCls : "cssTableCell cssTableCentered",
    name : "createTable_primaryKey_" + i });
  createTablePanel.add({ xtype : "checkbox", hideLabel : true,
    ctCls : "cssTableCell cssTableCentered",
    name : "createTable_notNull_" + i });
  createTablePanel.add({ xtype : "textfield", hideLabel : true,
    width : 150, ctCls : "cssTableCell",
    name : "createTable_defaultValue_" + i });
}

The ability to dynamically add elements to an existing FormPanel is something we haven't seen before, but that's precisely what we have here. We have a loop with 20 iterations (ah-ha!) and within each we're adding five elements (hey, that's how many columns there are in the table!) to the createTablePanel Panel (the one using the TableLayout). The add() method allows us to add new Ext JS Components to another Component. You simply pass in the config object for the Component you want to create and it gets added.

The Components added here aren't too special; they're just basic form elements that we're already pretty familiar with. A couple of TextFields, a ComboBox, and some CheckBoxes are what we need. The ComboBox gets bound to the SQLWorkbench.Data.columnTypeStore that contains the list of valid SQLite data types.

Note that on all of these fields the hideLabel attribute is set to true since the headers of the table are effectively the field labels. We've manually done what Ext JS normally does for us automatically (the price to pay for a different sort of UI presentation). Also note that each gets a name value that has the index of the array appended to it. That allows us to easily retrieve them all later.

Now, these form elements aren't being added directly to the FormPanel; they're being added to the Panel with the TableLayout, which you'll recall was the Component directly under the FormPanel in its items array. The nice thing here is that these dynamically added Components still become part of the FormPanel, part of the underlying form, just as if they had been defined explicitly in the config object of the Window. Doing it this way saves you from having to have a ton more config information here: imagine 20 groups of five Component definitions statically within the Window's config object. Not pretty at all!

Once all the Components have been added, we have only to show the Window:

createTableWindow.show();

At this point users see the Window as they should, all created and ready for their use.

When users click the Ok button, it's time to create the table:

SQLWorkbench.createTableExecute = function(inDatabaseName) {

  var formVals =
    Ext.getCmp("createTableForm").getForm().getValues();

    if (Ext.isEmpty(formVals.createTableName)) { return; }

  var sql = "CREATE TABLE IF NOT EXISTS " +
    formVals.createTableName + " (";

First, the form is retrieved and, once we ensure users entered a table name, we begin to construct the SQL statement to execute, including the name of the table entered.

Next we can begin to deal with the columns of the table:

var columnCount = 0;
var primaryKeyCount = 0;
var primaryKeyNotNullFound = false;
for (var i = 0; i < 20; i++) {
  var columnName = formVals["createTable_columnName_" + i];
  var columnType = formVals["createTable_columnType_" + i];
  var primaryKey = formVals["createTable_primaryKey_" + i];
  var notNull = formVals["createTable_notNull_" + i];
  var defaultValue = formVals["createTable_defaultValue_" + i];

Since we know the names of the fields in the form used the index numbers, we can easily construct those names again to pull the appropriate fields out of the formVals object. So, we grab the values of each of the five fields for the row we're currently examining. Once we have them, we can do some checks:

if (!Ext.isEmpty(columnName) && !Ext.isEmpty(columnType)) {
  if (!Ext.isEmpty(primaryKey) && !Ext.isEmpty(notNull)) {
    primaryKeyNotNullFound = true;
    break;
  }
  if (columnCount > 0) { sql += ", "; }
  columnCount++;
  sql += """ + columnName + "" " + columnType;

if (!Ext.isEmpty(primaryKey)) {
    primaryKeyCount++;
    sql += " PRIMARY KEY";
  }
  if (!Ext.isEmpty(notNull)) {
    sql += " NOT NULL";
  }
  if (!Ext.isEmpty(defaultValue)) {
    sql += " DEFAULT "" + defaultValue + """;
  }
 }
}
sql += ");";

First we ensure that a name has been entered for the column and that a type has been selected. Those are the two required elements. Next, we check to see if the Primary Key and Not Null CheckBoxes were both checked, and if so, we set primaryKeyNotNullFound to true and break out of the loop. This is a simple error check that has to be done.

Next, we bump up the columnCount variable so we know we have enough information for this column to actually create it. Next, we construct more of the SQL statement. Remember, the SQL statement will be in the form:

CREATE TABLE IF NOT EXISTS <tableName> ("<fieldName>" <fieldType>);

So, first we see if a column has already been defined, and if so, we append a comma to the sql value. Next, we construct the "<fieldName>" <fieldType> portion of the string. Next, we see if the Primary Key CheckBox was checked. If so, the string PRIMARY KEY is appended. Note too that the primaryKeyCount field is incremented in this case. The same thing is done for Not Null. Finally, if a default value was supplied, we append it as well with the DEFAULT clause. All of that completes the definition of this table column.

Next we have some error checking to do:

if (primaryKeyNotNullFound) {
  Ext.MessageBox.alert("Error",
    "Primary fields cannot be null.");
  return;
}
if (columnCount == 0) {
  Ext.MessageBox.alert("Error",
    "There were no columns to create. Note that Column Name " +
    "and Column Type are both required for every column.");
  return;
}
if (primaryKeyCount > 1) {
  Ext.MessageBox.alert("Error",
    "Only a single column can be designated as Primary Key.");
  return;
}

First, if that primaryKeyNotNullFound flag is set, then we have to tell the user that a primary key field cannot allow null values and abort creation of the table. Next we ensure that at least one valid column definition was found, meaning at least one column had a name and type selected. Finally, we ensure that only a single column is the primary key, which appears to be a limitation in SQLite.

Once all the validations have passed, we can get to the business of actually creating the table:

Ext.MessageBox.confirm("Confirm SQL Execution",
  "Are you sure you want to execute the following " +
  "SQL statement?<br><br>" + sql,
  function(inButtonClicked) {
    if (inButtonClicked == "yes") {
      var db = google.gears.factory.create("beta.database");
      db.open(inDatabaseName);
      try {
        db.execute(sql);
        db.close();
        SQLWorkbench.listTables(inDatabaseName);
        Ext.getCmp("createTableWindow").close();
        Ext.MessageBox.hide();
      } catch (e) {
        db.close();
        Ext.MessageBox.alert("SQL Execution Error", e);
      }
     }
    }
  );

};

After we confirm that users want to execute the SQL we've constructed (and we very nicely show it to them!), then creating the table is a simple matter of opening the appropriate database, the name of which was passed in as inDatabaseName, and executing the SQL query. We wrap that all up in try...catch in case anything goes wrong, and if it does, we display the exception that was thrown. If no exception is thrown, we list the tables in this database again, which to the user appears as a refresh of the Tables Window, and then we close the Create Table Window and we're all done.

7.5.6. TableDetailsWindow.js

At this point we've looked at maybe half the code in this application, but where we find ourselves now is without question the biggest chunk to look at: the TableDetailsWindow.js file. This is all the code related to the Table Details Window, seen in Figure 7-12. As it turns out, the biggest chunk of functionality available in this application is found right here in this file.

Before we get to anything difficult, though, we have a bit of code that's very simple:

SQLWorkbench.listTableDetails = function(inDatabaseName, inTableName) {

  var tableWindow = Ext.getCmp("tableWindow_" + inTableName);
  if (tableWindow) {
    tableWindow.close();
 }

As you can see, the name of both the database and the table to display details for are passed in to this method as a result of the user clicking a table from the Tables Window. Using the table name specifically, we try to get a reference to a Window associated with this table, since one may already be opened. If one is found, we call its close() method, which results in it being destroyed. The next step is to create the Window, so by closing it first and then re-creating it we're effectively implementing a cheap refresh capability.

7.5.6.1. Defining the Window

Speaking of re-creating the Window:

tableWindow = new Ext.Window({
  title : inDatabaseName + " : " + inTableName, width : 700,
  height : 500, constrain : true, maximizable : true,
  animateTarget : "divSource", layout : "fit",
  id : "tableWindow_" + inTableName,
  items : [
    { xtype : "tabpanel", activeTab : 0, layoutOnTabChange : true,
      items : [

The title of the Window shows both the database and table names, which is pretty logical. The id value is created based on the table name, which is how we're able to get a reference to it prior to this. The items array begins with a Component of xtype tabpanel. If you look at Figure 7-12 again, you'll see there are two tabs, Structure and Browse. This is where the definition of those tabs begins. Note that I set the layoutOnTabChange config option to true. This causes the tabs to be laid out when switched to. This is often necessary to get Components that are children of a tab to be drawn properly.

7.5.6.2. Defining the Structure Tab

With the TabPanel started, we can begin to insert some tabs via its items array. The first is the Structure tab:

{ title : "Structure", layout : "table",
  id : "structureTablePanel_" + inTableName,
  layoutConfig : { columns : 5 }, autoScroll : true,
  items : [
    { html :
      "<div style="background-color:#dfe8f6;">Name</div>",
      cellCls : "cssTableHeader" },
    { html :
      "<div style="background-color:#dfe8f6;">Type</div>",
      cellCls : "cssTableHeader" },
    { html :
      "<div style="background-color:#dfe8f6;">P. Key?</dv>",
      cellCls : "cssTableHeader cssTableCentered" },
    { html :
      "<div style="background-color:#dfe8f6;">Not Null?</div>",
      cellCls : "cssTableHeader cssTableCentered" },
    { html :
      "<div style="background-color:#dfe8f6;">Default</div>",
      cellCls : "cssTableHeader" }
    ],
    bbar : [

{ text : "Drop", icon : "img/Drop.gif", cls : "x-btn-text-icon",
     handler : function() {
       SQLWorkbench.doTableOp(inDatabaseName, inTableName, "drop");
     }
    },
    "-",
    { text : "Empty", icon : "img/Empty.gif", cls : "x-btn-text-icon",
      handler : function() {
        SQLWorkbench.doTableOp(inDatabaseName, inTableName, "empty");
     }
    },
    "-",
    { text : "Rename",
      icon : "img/Rename.gif", cls : "x-btn-text-icon",
      handler : function() {
        SQLWorkbench.renameCopyTable(
          inDatabaseName, inTableName, "rename");
      }
    },
    "-",
    { text : "Copy", icon : "img/Copy.gif", cls : "x-btn-text-icon",
      handler : function() {
        SQLWorkbench.renameCopyTable(
          inDatabaseName, inTableName, "copy");
      }
    }
  ]
},

That was a fairly lengthy chunk of code, but it's not too complex and doesn't really contain anything new. A TableLayout is used within the tab to create a table where the details of the tables' structure will be displayed. The table is defined as having five columns (layoutConfig:{columns:5}), and autoScroll is set to true to ensure the user can scroll the content and not miss anything. After that, five elements with some simple HTML are added. These are the headers of the table. They are simply <div>s with a background color chosen to blend in with the rest of the Window, and with the cssTableHeader style class applied via the cellCls attribute. Two of the headers, P. Key and Not Null, also have the additional cssTableCentered class applied so that the text in the header is centered, which just plain looks better when the fields themselves are drawn below the headers because they contain CheckBoxes.

Next, the bbar attribute is used to define a Toolbar at the bottom of the tab. This includes a number of Buttons: Drop, for dropping the table from the database; Empty, for emptying the table but leaving its structure intact; Rename, for literally renaming the table; and Copy, for creating a copy of the table, including its data. The first two call on the doTableOp() method, and the last two call a third method named renameCopyTable. We'll be looking at those later, so don't worry about them just yet.

7.5.6.3. Defining the Browse Tab

The definition of the Browse tab, which you can see in Figure 7-13, is next, and it is surprisingly sparse.

{ title : "Browse", layout : "fit", id : "browseTab_" + inTableName,
       items : [ { id : "browseTablePanel_" + inTableName, html : "" } ]
     }
    ]
   }
  ]
});

Yep, that's it! The reason you don't see a bunch more here is that the actual content that will be shown is going to have to be built dynamically, and that's something we'll be looking at shortly.

7.5.6.4. Populating the Structure Tab

For now, though, the Window definition is complete and it's time to look at some executable code:

var databaseRecord = SQLWorkbench.Data.databasesStore.getById(inDatabaseName);

var tables = databaseRecord.get("tables");
var tableRecord = tables.getById(inTableName);
var sql = tableRecord.get("sql");

First, we retrieve the DatabaseRecord for the database name passed in as inDatabaseName from the databasesStore by using its getById() method. Once we have that, we grab the tables field from the DatabaseRecord, and then we grab the TableRecord for the specified table (inTableName). Finally, we get the SQL that was used to create the table by getting the value of the sql field on the TableRecord.

The next step is to do something with that SQL:

var tableDetails = SQLWorkbench.parseCreateSQL(sql);
tableRecord.set("tableDetails", tableDetails);

The parseCreateSQL() method is a fairly complex and long piece of code that we'll look at later. For the moment let me just explain what it does: it parses the SQL statement passed in and generates a simple JavaScript object from it that I call a table details record. This contains information about the structure of the table, including the fields it contains and the information about each, such as the type, whether each is a primary key, and whether any default values may be defined. Once we have the table details object, it is added to the TableRecord in its tableDetails field for later use.

With the details about the structure of the table in hand, we can move on to the business of populating the Structure tab:

var structureTablePanel = Ext.getCmp("structureTablePanel_" + inTableName);

for (var i = 0; i < tableDetails.fields.length; i++) {
  structureTablePanel.add({ html : tableDetails.fields[i].name,
    border : false, cellCls : "cssTableCell" });
  structureTablePanel.add({ html :
    Ext.util.Format.uppercase(tableDetails.fields[i].type),
    border : false, cellCls : "cssTableCell" });
  if (tableDetails.fields[i].primaryKey) {
    structureTablePanel.add(
      { xtype : "checkbox", checked : true, readOnly : true,
        ctCls : "cssTableCentered" }
    );
  } else {
    structureTablePanel.add({ html : "&nbsp;", border : false });
  }

First we get a hold of the Panel using the TableLayout that was defined in the items array for the Window. This is the table we'll be building. Next, we iterate over the fields in the table details object. For each we're going to add a cell to the table, five in total, which completes a row in the table. The first one is the name of the field. All we need to do is use the add() method of the Panel and include the value to be displayed as the value of the html attribute. The cssTableCell class is assigned to the cell using the cellCls attribute, and the border is turned off, which makes it look better. For the field name and data type, that's all it takes.

For the columns that tell whether the database field is a primary key field or can be null, there is just a little more work to do. We begin by looking to see if the field is a primary key by checking the primaryKey field of the appropriate element in the fields array that is part of the tableDetails object. If the value returned is true, then a cell is added that contains a CheckBox that is checked. If the field is not a primary key field, then an empty cell is inserted (the &nbsp; HTML entity is used to avoid the cell collapsing, as happens in some browsers).

The same basic sequence of events is performed for the not null column as well:

if (tableDetails.fields[i].notNull) {
   structureTablePanel.add(
     { xtype : "checkbox", checked : true, readOnly : true,
       ctCls : "cssTableCentered" }
   );
 } else {
   structureTablePanel.add({ html : " ", border : false });
 }
 structureTablePanel.add({ html : tableDetails.fields[i].defaultValue,
   border : false, cellCls : "cssTableCell" });
}

The final column added is the default value. This could be empty, which is fine.

7.5.6.5. Populating the Browse Tab

The next step is to populate the Browse tab and show the Window we just built:

SQLWorkbench.showAllRecords(inDatabaseName, inTableName);
  tableWindow.show();
};

Don't worry, the showAllRecords() method is our next stop, and it's responsible for populating the Browse tab:

SQLWorkbench.showAllRecords = function(inDatabaseName, inTableName) {

  var databaseRecord = SQLWorkbench.Data.databasesStore.getById(inDatabaseName);
  var tables = databaseRecord.get("tables");
  var tableRecord = tables.getById(inTableName);
  var tableDetails = tableRecord.get("tableDetails");

The same bit of code that we saw just a short while ago is again executed to get the DatabaseRecord, and the DataStore containing all the TableRecords associated with the database, and then the TableRecord for this specific table. In this case, however, we don't need to parse the creation SQL because we already have the table details object sitting on the TableRecord; all we need to do is retrieve it from the tableDetails field.

Next we need to get a reference to the Browse tab itself:

var browseTab = Ext.getCmp("browseTab_" + inTableName);
browseTab.remove("browseTablePanel_" + inTableName);

We also need to remove the Panel using the TableLayout that may exist, which fortunately is easy: just call the remove() method of the Panel, passing it the ID of the Component to remove. Since we'll be re-creating that now, no one will miss it!

Once that's done we can begin:

var browseTablePanel = new Ext.Panel({
  id : "browseTablePanel_" + inTableName, layout : "table", autoScroll : true,
  layoutConfig : { columns : tableDetails.fields.length }
});
for (var i = 0; i < tableDetails.fields.length; i++) {
  browseTablePanel.add({
    html : "<div style="background-color:#dfe8f6;">" +
    tableDetails.fields[i].name + "</div>", cellCls : "cssTableHeader"
  });
}

First, a new Ext.Panel is created, which uses a TableLayout. The rest of this is very much along the lines of the Structure tab and how its contents were created. However, with that tab we know the columns in the table; they are static. Here however, the columns we have in the table depend on the fields in the table itself. So, we begin by setting the columns attribute of the layoutConfig object to the length of the fields field in the tableDetails object. That way, the table will have a column for each field in the table.

Next we start to iterate over the fields in the table. For each we add a cell to the table, just like we saw in the Browse tab code. Once the loop is complete, we have column headers in the table.

The next step is to populate the actual data, so as I'm sure you can guess we need to go get it first:

var db = google.gears.factory.create("beta.database");
db.open(inDatabaseName);
var rs = db.execute("SELECT * FROM " + inTableName);

A simple query is performed to get all the data in the table (and if you think this sounds like it could be a problem if the table is too long, you're right... see the suggested exercises at the end of the chapter).

Once we have all the data it's time to generate some stuff on the screen:

while (rs.isValidRow()) {
  for (var i = 0; i < tableDetails.fields.length; i++) {
    browseTablePanel.add({
      html : rs.fieldByName(tableDetails.fields[i].name),
      border : false, cellCls : "cssTableCell"
    });
  }
  rs.next();
}
db.close();

For each row returned we need to output the value for each field that we know is in the table, and it has to be done in the proper order so the data matches up with the column headers. So, we again loop through the elements in the tableDetails.fields array and for each add a cell to the table, taking the value of the html attribute from the current row of the ResultSet. Once that's done we have only to close the ResultSet and the Database and then:

browseTab.add(browseTablePanel);

Calling add() on the browseTab reference inserts the Panel we just built into the DOM (well, indirectly at least... it's handed to Ext JS's management, which then shoves it into the DOM) and voilà, we have a read-only browse view of the data in the table on the screen ready for the user to peruse!

7.5.6.6. Executing Various "Simple" Operations

As mentioned earlier, two of the Buttons on the Structure tab call the doTableOp() method, and as we'll find out, actually handle the other two Buttons as well! Here's the beginning of that method:

SQLWorkbench.doTableOp =
 function(inDatabaseName, inTableName, inOperation, inValue) {

As you can see, the method accepts the name of the database and table to operate on, which I think is pretty obvious, but it also accepts inOperation, which is literally the name of the operation to perform. Four values are understood: drop, empty, rename, and copy. The inValue argument is a value that will be used by the operation being performed. Two of the four operations, rename and copy, require this argument, the others do not.

The next thing this method does is some setup work:

var sql1 = null;
var sql2 = null;
switch (inOperation) {
  case "drop":
    sql1 = "DROP TABLE " + inTableName;
  break;
  case "empty":
    sql1 = "DELETE FROM " + inTableName;
  break;
  case "rename":
    sql1 = "ALTER TABLE " + inTableName + " RENAME TO " + inValue;
  break;
  case "copy":
    var databaseRecord =
      SQLWorkbench.Data.databasesStore.getById(inDatabaseName);
    var tables = databaseRecord.get("tables");
    var tableRecord = tables.getById(inTableName);
    var creationSQL = tableRecord.get("sql");
    sql1 = creationSQL.replace(inTableName, inValue);
    sql2 = "INSERT INTO " + inValue + " SELECT * FROM " + inTableName;
  break;
}

Based on the value of inOperation, the appropriate SQL query is constructed. All of them require the inTableName value, and that's all that the drop and empty operations require (inDatabaseName is used later). For rename, inValue is used because that's the new name of the table, so you can see it being appended as part of the SQL statement.

The copy operation is different and more complex than the rest. First, we get the Database Record corresponding to inDatabaseName in the same fashion as we've seen before. Then, we get the tables in the database, then the TableRecord for the specified table, and then the creation SQL statement. Next, we use the replace() method, an intrinsic method available on all JavaScript strings, to replace the name of the table with the value of inValue, which is the name of the copy of the table. Finally, we create a second SQL statement and assign it to the variable sql2 (all the others were assigned to sql1 notice, and sql2 started with a value of null).

All of that string manipulation gymnastics is required because there's no query, or function provided by Gears, to simply say "copy this table." To "copy" a table really means creating the new table and then copying the data from the old one. That's why we need the creation SQL for the source table and that's why two queries are involved in this operation.

Once that's done, we can go ahead and execute the query (or queries), once the user confirms it:

Ext.MessageBox.confirm("Confirm SQL Execution",
  "Are you sure you want to execute the following " +
  "SQL statement(s)?<br><br><br>" + sql1 + (sql2?"<br>"+sql2:""),

function(inButtonClicked) {
  if (inButtonClicked == "yes") {
    var db = google.gears.factory.create("beta.database");
    db.open(inDatabaseName);
    try {
      db.execute(sql1);
      if (sql2) {
        db.execute(sql2);
      }
      db.close();

Ext.MessageBox.confirm() is used to show users the SQL statement we're about to execute and requires them to confirm or deny the operation, as seen in Figure 7-14. If inButtonClicked, the argument passed to the callback function, is the value yes, then the operation can proceed. So, we open the database specified, and try to execute sql1. Then, if sql2 isn't null we execute that as well.

Once the queries have executed and the database has been closed, we have some UI work left to do:

SQLWorkbench.listTables(inDatabaseName);
Ext.MessageBox.hide();
if (inOperation == "rename") {
  Ext.getCmp("tableWindow_" + inTableName).setTitle(
    inDatabaseName + " : " + inValue);
} else if (inOperation = "drop") {
  Ext.getCmp("tableWindow_" + inTableName).close();
}
return true;

Calling the listTables() method causes the Tables Window for this database to be redrawn, which accounts for rename, drop, and copy operations to be reflected in the list since those result in new tables to show, or one old one no longer being shown. Then, the MessageBox is hidden.

After that we check to see if the operation that was just performed was a rename. If it was, then we need to change the title of the Table Details Window for that table. Ext.getCmp() allows us to get a reference to it, once we construct the appropriate ID value, and then the setTitle() method is called, passing in the name title that reflects the new name. Similarly, if the operation was a drop operation, then we need to close the Table Details Window, which is a simple matter of getting a handle to it and calling close() on it. At this point, we return true so the caller knows the operation was successful and we're done.

However, since the execution of the SQL queries were wrapped in try...catch, we know there's one last piece to this puzzle:

} catch (e) {
       db.close();
       Ext.MessageBox.alert("SQL Execution Error", e);
       return false;
     }
    } else {
      return false;
    }
   }
  );
};

Any exceptions thrown simply result in an Ext.MessageBox.alert() Window being shown that contains the exception message. We also return false in this case so the caller knows the operation failed. Note that false is also returned if the user didn't click the Yes button in the confirmation dialog (the final else branch here accounts for that).

7.5.6.7. Renaming and Copying a Table

Although we just saw that renaming a table and copying a table are functions the doTableOp() method handles, that isn't the method that the Buttons on the Toolbar call directly. As it turns out, there's a little bit of preparation to do before those functions can be executed by doTableOp(), and that work is handled by the renameCopyTable() method:

SQLWorkbench.renameCopyTable =
  function(inDatabaseName, inTableName, inOperation) {

Similar to how doTableOp() accepts an inOperation argument, so too does this method. Here, the supported values are rename and copy:

var windowTitle = "Rename Table";
var promptText = "Please enter the new name for the table";
if (inOperation == "copy") {
  windowTitle = "Copy Table";
  promptText = "Please enter the name for the new copy";
}

We start off assuming the operation will be rename, so the windowTitle variable reflects that, as does the promptText variable. If inOperation is copy, though, we override the values for windowTitle and promptText so they apply to a copy operation.

Then, an Ext.MessageBox.prompt() dialog is opened:

Ext.MessageBox.prompt(windowTitle, promptText,
   function(inBtnID, inVal) {
     if (inBtnID == "cancel") { return; }
     if (inVal != null) { inVal = inVal.trim(); }
     if (!Ext.isEmpty(inVal)) {
       SQLWorkbench.doTableOp(inDatabaseName, inTableName, inOperation, inVal);
     }
   }, null, false, inTableName
  );

};

For both of these operations, the user is required to enter a value: the name to rename the table to or the name of the copy of the table. We use the windowTitle and promptText variables to set the title of the MessageBox and the text of the prompt message to the user accordingly.

The callback function passed to Ext.MessageBox.prompt() first checks to see if the Cancel button was clicked, and if so, it just returns, no harm no foul. If that doesn't happen, we check to make sure inVal, what the user entered, isn't null, and if not we trim() it. Then, if inVal has a value, we call SQLWorkbench.doTableOp, passing in all the pertinent information.

Note too that the name of the table being renamed or copied is the default value in the MessageBox, which is what that bit at the end with }, null, false, inTableName is all about.

7.5.6.8. Parsing the Creation SQL Statement

Warning! The following is not especially pretty code! I have no doubt that readers will write telling me a million different ways to do this part better, and that's cool, I look forward to hearing from you! In the meantime, while this code isn't going to win any awards for...well, anything probably, it does have the virtue of doing exactly what we need it to do: parse an SQL creation statement and get the details about the table from it. Unfortunately, there's no clean way to obtain this information from SQLite through Gears, so this general approach, whether the technique is great or not, is about the only path available to us.

SQLWorkbench.parseCreateSQL = function(inSQL) {

  var trimQuotes = function(inString) {
    return inString.replace(/^['"`]+|['"`]+$/g, ");
  };
  var replaceString = function(inSource, inReplace, inReplaceWith) {
    var start = inSource.toLowerCase().indexOf(inReplace.toLowerCase());
    while (start != −1) {
      inSource = inSource.substring(0, start - 1) +
        inReplaceWith + inSource.substring(start + inReplace.length);
      start = inSource.toLowerCase().indexOf(inReplace.toLowerCase());
    }
    return inSource;
  };

First, two utility functions are created (remember you can nest functions in JavaScript all you like). The first simply trims quotation marks from the ends of a string, which we'll need to clean up the value as we parse the incoming SQL string. The second replaces all occurrences of a given string within another.

var tableDetails = {
  sql : inSQL,
  error : null,
  temporary : false,
  ifNotExists : false,
  tableName : null,
  databaseName : null,
  fields : [ ]
};

The tableDetails structure is what we're ultimately trying to create here. The fields in this object are summarized in Table 7-2.

Table 7.2. The Fields of the tableDetails Object

Field	Description
sql	The table creation SQL statement passed into the method
error	If certain errors occur during parsing, this field will contain the failure reason
temporary	Tells us whether the table is a temporary table
ifNotExists	Tells us whether the if not exists clause was present in the SQL statement
tableName	The name of the table
databaseName	The name of the database the table belongs to
fields	An array of field descriptor objects, which is another plain JavaScript object with the following fields: name, the name of the field; type, the data type of the field; primaryKey, true if the field is a primary key; notNull, true if null is not allowed in the field; and defaultValue, the default value of the field

Next, the incoming SQL statement is trimmed:

inSQL = inSQL.trim();

Ensuring that there is no whitespace on either ends of the string makes parsing it easier. In fact, anything we can do to "normalize" things as we go makes the job easier, and you'll see a lot of that now.

The next step is one such normalization step:

var stringBefore = inSQL;
var stringAfter = inSQL.replace(/ss/g, ' '),
while (stringBefore != stringAfter) {
  stringBefore = stringAfter;
  stringAfter = stringAfter.replace(/ss/g, ' '),
}
inSQL = stringAfter;

What we're doing here is ensuring that throughout the entire string there are only single spaces. Since whitespace will be ignored when the SQL is executed, it's not an issue from a "does the SQL statement work" perspective, but it does make parsing things harder because we can't treat a space as a token delimiter. Ensuring there are only single spaces, however, allows us to do just that. So, a little regex magic later and the value of inSQL is guaranteed to only have single spaces.

The next step is to get the "prefix" portion of the SQL—that is, in an SQL statement like

CREATE TABLE "test" ("aaa" INTEGER PRIMARY KEY NOT NULL , "bbb" TEXT)

the prefix is the portion before the field list, meaning the portion before the opening parenthesis. We can get this prefix pretty easily:

var prefix = (inSQL.substring(0, inSQL.indexOf("("))).trim();

Now that we have the prefix, we can do a quick validation:

if (prefix.toLowerCase().indexOf("create") != 0) {
  tableDetails.error = "create keyword not found";
  return tableDetails;
}
prefix = prefix.substring(7);

If we don't find the create keyword, then it's an error; we can't parse the incoming string, and the method aborts.

If this validation passes, then we know the first part of inSQL is create. So, by using prefix.substring(7), we get everything following the create keyword. With that, we check for a few other keywords:

if (prefix.toLowerCase().indexOf("temp ") == 0) {
  tableDetails.temporary = true;
  prefix = prefix.substring(5);
} else if (prefix.toLowerCase().indexOf("temporary") == 0) {
  tableDetails.temporary = true;
  prefix = prefix.substring(10);
}

If the keyword temp is present, then the table is a temporary table. It's also possible to have the keyword temporary in there, so we have to check for that as well. Fortunately, temp or temporary can only follow the create keyword, so the code doesn't have to be too clever (nor do I, thankfully!). Another substring() call gets us past whichever keyword was there, if any.

The next possible keyword is table, so we check for it next:

if (prefix.toLowerCase().indexOf("table") != 0) {
  tableDetails.error = "table keyword not found";
  return tableDetails;
}
prefix = prefix.substring(6);

In fact, it's not just possible; it has to appear next. Once you work past create and temp/temporary, table is the only valid keyword that can follow. So, if it's not found, it's another validation error that aborts the method. If it is found, then we can continue to parse the prefix.

if (prefix.toLowerCase().indexOf("if not exists") == 0) {
  tableDetails.ifNotExists = true;
  prefix = prefix.substring(14);
}

The if not exists clause is the next portion that can be present, so we check for that and set the flag in tableDetails if found.

The next step is to get the name of the table:

if (prefix.indexOf(".") == −1) {
  tableDetails.databaseName = null;
  tableDetails.tableName = trimQuotes(prefix);
} else {
  tableDetails.databaseName = trimQuotes(prefix.split(".")[0]);
  tableDetails.tableName = trimQuotes(prefix.split(".")[1]);
}

The complication here is that the SQL can have just the table name or the table name plus the database name in the form <databaseName>.<tableName>. So, we account for that by checking for a period. If not found, then all we have is a table name. However, it could have quotes around it, so now that trimQuotes() method we created earlier comes into play. If a period was found, then we split the prefix string on the period and trim both resulting array elements: the first is the database name, and the second is the table name.

The next step is to retrieve the list of field specifications:

var fields = inSQL.substring(inSQL.indexOf("(") + 1, inSQL.length - 1);

This is a simple matter of taking everything from the first character after the opening parenthesis to the end of inSQL, minus the last character.

However, we have a problem: the fields string could contain a primary key clause at the end. So, we check for that:

var primaryKeyListLocation = fields.toLowerCase().indexOf("primary key(");
var primaryKeyList = null;
if (primaryKeyListLocation != −1) {
  primaryKeyList = fields.substring(primaryKeyListLocation).trim();
  fields = fields.substring(0, primaryKeyListLocation).trim();
  fields = fields.substring(0, fields.length - 1);
}

If the test string primary key( is found, then we get everything from the point it's found, taking care to trim it. Then, it is removed from the fields string.

The next step is to scan through the list of field specifications and learn from each the information we need. To do so, we tokenize the string with the JavaScript string's split() method, using commas as the delimiter:

var fieldTokens = fields.split(",");
for (var i = 0; i < fieldTokens.length; i++) {

Then we iterate over the resulting array. For each element, which is a field in the table, we create a fieldDescriptor object:

var fieldDescriptor = {
  name : null, type : null, primaryKey : false, notNull : false,
  defaultValue : null
};

The next token, meaning the next field specification, is taken, trimmed, and converted to lowercase:

var field = fieldTokens[i].trim();
var testField = field.toLowerCase();

Then, we search for each of the valid SQLite data types within that string:

if (testField.indexOf("integer") != −1) {
  fieldDescriptor.type = "integer";
}
if (testField.indexOf("double") != −1) {
  fieldDescriptor.type = "double";
}
if (testField.indexOf("float") != −1) {
  fieldDescriptor.type = "float";
}
if (testField.indexOf("real") != −1) {
  fieldDescriptor.type = "real";
}
if (testField.indexOf("char") != −1) {
  fieldDescriptor.type = "char";
}
if (testField.indexOf("varchar") != −1) {
  fieldDescriptor.type = "varchar";
}
if (testField.indexOf("text") != −1) {
  fieldDescriptor.type = "text";
}
if (testField.indexOf("blob") != −1) {
  fieldDescriptor.type = "blob";
}
if (testField.indexOf("numeric") != −1) {
  fieldDescriptor.type = "numeric";
}
if (testField.indexOf("datetime") != −1) {
  fieldDescriptor.type = "datetime";
}
field = replaceString(field, fieldDescriptor.type, "").trim();

Whichever one is found, we set the appropriate value in the fieldDescriptor. Finally, we replace the data type in the token with a blank string, effectively removing it. This is another one of those "normalization" tricks to make the next part easier:

if (field.toLowerCase().indexOf("not null") != −1) {
  fieldDescriptor.notNull = true;
  field = replaceString(field, "not null", "").trim();
}

We check to see if the string not null appears in the token. If so, then we set the notNull field of the fieldDescriptor to true and delete the string from the token. Then we move on to a similar check for the primary key string that might be present:

if (field.toLowerCase().indexOf("primary key") != −1) {
  fieldDescriptor.primaryKey = true;
  field = replaceString(field, "primary key", "").trim();
}

After that, we have to see if there is a default value specified for the field:

var defaultValueKeywordStart = field.toLowerCase().indexOf("default");
if (defaultValueKeywordStart != −1) {
  var defaultValueStart = field.indexOf("'", defaultValueKeywordStart);
  var defaultValueEnd = field.indexOf("'", defaultValueStart + 1);
  fieldDescriptor.defaultValue =
    field.substring(defaultValueStart + 1, defaultValueEnd);
  field = field.substring(0, defaultValueKeywordStart).trim();
}

If the string default is found, then we look for the opening and closing single-quote characters that must be surrounding the default value. Once found, we use the substring() method to get the portion of the string in between them and record that as the defaultValue in the fieldDescriptor.

All that remains for dealing with the field is to get rid of any quotes around the name of the field, if they are present, and then push the fieldDescriptor onto the tableDetails.fields array:

fieldDescriptor.name = trimQuotes(field);

 tableDetails.fields.push(fieldDescriptor);

}

The last task is to deal with that primaryKeyList that we pulled out earlier, if there is one:

if (primaryKeyList) {
  primaryKeyList = primaryKeyList.trim();
  primaryKeyList = primaryKeyList.substring(primaryKeyList.indexOf("(") + 1,
    primaryKeyList.indexOf(")"));
  var pkFields = primaryKeyList.split(",");
  for (var i = 0; i < pkFields.length; i++) {
    var pkFieldName = trimQuotes(pkFields[i]).trim();
    for (var j = 0; j < tableDetails.fields.length; j++) {
      if (pkFieldName.toLowerCase() ==
        tableDetails.fields[j].name.toLowerCase()) {
        tableDetails.fields[j].primaryKey = true;
        break;
      }
     }
    }
   }

We start by trimming primaryKeyList, and then find the content between the opening and closing parentheses. With that portion of the string now in primaryKeyList, we split on comma again and iterate over the array. For each element, we get the value, trim quotes from the ends, and trim() it, just to be safe. At this point we have the name of the primary key field. So, we now iterate over the tableDetails.fields array and look for that field. Once found, we set primaryKey on that field descriptor to true and we're good to go.

return tableDetails;

};

The method returns the tableDetails object and the caller has just what they want: an object containing details about the table, derived from the SQL that created it! Again, I apologize if you find this bit of code to be rather brute-force. The apology only goes so far, however, because, well, the code works! I have no doubt there's some magic regex, or fancy recursive algorithm that could do it cleaner, but hey, if the code works, it meets the first qualification for being decent, right?

7.5.7. QueryToolWindow.js

To paraphrase Star Wars: "Don't be too proud of this technological marvel you've created... the power to browse a database table is insignificant next to the power of naked SQL."^[]

^[] You certainly can't be considered a true geek if you need to read this, but the quote I'm paraphrasing is from Star Wars (and I mean the real Star Wars, not Star Wars: A New Hope, not the prequel trilogy, but the real one!). Spoken by Darth Vader, the original quote was, "Don't be too proud of this technological terror you've constructed. The ability to destroy a planet is insignificant next to the power of the Force."

Er, yeah, I realize that's a stretch. Ricky Henderson^[] stretching a single up the middle into a triple stretch.

^[] Ricky Henderson is a Hall of Fame baseball player who is considered by most to be one of, if not the, best leadoff hitter in the history of the game. He is the all-time stolen base leader among other accomplishments and was known to frequently get more bases out of hits than other players could.

But, stretch jokes aside, there's truth in the paraphrasing: this application lets us browse database tables, drop and empty tables, some other basic things, but if you have the ability to enter arbitrary, or naked, SQL statements and execute them, a ton more power becomes available to you. That's exactly what the Query tool, shown in Figure 7-15, is all about.

Here users can enter any SQL statement they want and it will be executed. If it's a query-type statement, then the results of the query will be shown at the bottom in tabular form. If it is instead an update-type query, then the number of rows affected will be shown instead. If any exceptions occur, that information will be displayed as well.

The code begins with the showQueryTool() Window, which does exactly what you think it does!

SQLWorkbench.showQueryTool = function() {

  if (Ext.getCmp("queryToolWindow")) {
    queryToolWindow.show();
    return;
}

A quick check is first performed to see if the Query tool Window is already opened, and if so, it is shown, in front of all other open Windows. In that situation, this method is done, but if not, this code executes:

new Ext.Window({
  title : "Query Tool", width : 700, height : 600, constrain : true,
  animateTarget : "divSource", id : "queryToolWindow", maximizable : true,
  layout : "anchor", bodyStyle:"padding:8px;background-color:#ffffff",
  minWidth : 400, minHeight : 500,

The Window is defined with most of the usual config attributes, but the bodyStyle value is a little different. We've seen padding used before to ensure that the content of the Window doesn't bump right up against the borders, but what gives with setting the background-color? As it turns out, with the layout attribute set to anchor as we have here, the space around the content in the Window wasn't white—it was blue, which makes it not look very good, and not that much different either. So, by forcing the background color here to white, you can achieve the padding space you're looking for.

Next is the definition of two Toolbars, one on the top and one on the bottom of the Window:

tbar : [
  { xtype : "panel", baseCls: "x-window-header",
    html : "Select database for query: " },
  { xtype : "combo", width : 100, editable : false, id : "qtDatabase",
    triggerAction : "all", mode : "local", valueField : "name",
    displayField : "name", store : SQLWorkbench.Data.databasesStore }
 ],
 bbar : [
  { text : "Execute SQL",
    icon : "img/ExecuteSQL.gif", cls : "x-btn-text-icon",
    handler : function() {
      SQLWorkbench.executeArbitrarySQL();
    }
  }
],

The top Toolbar contains two elements. The first is a plain-old Panel with some text in it via its html property. Elements on a Toolbar don't necessarily have to be buttons; they can be virtually any Component, of which a Panel is one. This allows for simple text labels and such, as is done here. Setting the baseCls attribute to x-window-header ensures that the Panel has the same color styling as the Toolbar; otherwise its background would be white and it would stick out like a sore thumb. Following that is a ComboBox bound to the SQLWorkbench.Data.databasesStore. Since the user has to tell the Query tool what database to run the query in, this ComboBox is necessary.

The bottom toolbar simply has the single Execute SQL button that calls the executeArbitrarySQL() method. After that is the items array:

items : [
     { html : "<b>Enter SQL to execute:</b>", anchor : "100% 4%",
       border : false },
     { xtype : "textarea", anchor : "100% 20%", id : "qtSQL" },
     { anchor : "100% 2%", border : false },
     { html : "<b>Last error:</b>", anchor : "100% 4%", border : false },
     { anchor : "100% 10%", id : "qtError" },
     { anchor : "100% 2%", border : false },
     { anchor : "100% 58%", border : false, id : "qtResult" }
    ]
  }).show();

};

Recall that on the Window config object was the layout:anchor setting. This is the first use of AnchorLayout in any project. Simply stated, an AnchorLayout allows you to define regions in a container in such a way that they will dynamically resize along with the container. Imagine, for example, a TextArea in a Window and when you resize the Window the TextArea expands and contracts along with the Window.

Usually you specify percentage values within an AnchorLayout, but that's not always the case. In this example, though, it very much is. Each of the elements in the items array has an anchor attribute. This is in the form X Y, where X specifies the width of the element and Y the height (this isn't the only form actually, but for our purposes here it is, so if and when you decided to use an AnchorLayout on your own I'd hop on over to the Ext JS documentation, which lays this all out pretty well). Here, each of the elements should always stretch across the entire container so the X value is 100% for all of them. The Y value is some percentage such that the total height of all elements adds up to 100%.

So, what happens when the Window is resized? Well, the first element, which is just a simple text label, will be resized so that it takes up 4 percent of the total height of the Window. The TextArea where users enter their query will resize to take up 20 percent of the total height of the Window, and so on. This does leave open the possibility that the Window is resized too small and certain elements don't have enough space for their content. For example, making the Window too small will mean that 4 percent of its height is too small a space for the text to fit into and it will be cut off. That's exactly why the minWidth and minHeight attributes were specified on the Window config: the values (400 and 500 respectively) are about the minimum before Bad Things™ begin to happen to some portions of the UI.

This fluidity of layout is one of the major benefits of the code-centric approach to UI building that Ext JS provides.

Note that the borders are turned off on all the elements except the query entry box and the box where errors are reported (qtError). This is purely a "it looks better this way" thing.

So, when the user finally clicks the Execute SQL Button, the executeArbitrarySQL() method is called:

SQLWorkbench.executeArbitrarySQL = function() {

  var databaseName = Ext.getCmp("qtDatabase").getValue().trim();
  var sql = Ext.getCmp("qtSQL").getValue().trim();
  if (Ext.isEmpty(databaseName)) {
    Ext.MessageBox.alert("Error", "Please select a database");
    return;
  }
  if (Ext.isEmpty(sql)) {
    Ext.MessageBox.alert("Error", "Please enter a SQL query");
    return;
  }

First, we get the value from the qtDatabase ComboBox and trim it using the trim() method that Ext JS adds to the String class. Next, using Ext.isEmpty(), we make sure something was actually entered, and if not we display an alert using Ext.MessageBox.alert() and abort the method.

Next, a check is done to ensure users entered a query; they are notified and the method aborted if not.

With those validations out of the way, it's time to execute some SQL:

Ext.MessageBox.confirm("Confirm SQL Execution",
  "Are you sure you want to execute the following " +
  "SQL statement?<br><br><br>" + sql + "<br><br><br>...in database : " +
  databaseName,
  function(inButtonClicked) {
    if (inButtonClicked == "yes") {
      var queryToolWindow = Ext.getCmp("queryToolWindow");
      queryToolWindow.remove("qtError");
      queryToolWindow.remove("qtResult");
      var errMsg = "";
      var db = google.gears.factory.create("beta.database");
      db.open(databaseName);
      var resultTablePanel = null;

Well, almost time to execute some SQL! First we confirm that users want to do this by calling Ext.MessageBox.confirm() and showing the SQL to be executed and in what database it will be executed against. If they click the Yes button, which is determined by checking the value of the inButtonClicked argument to the callback (and remember the value is always lowercase regardless of the label on the button), then we can do our work.

That works begins by getting a reference to the Query tool Window. Then, using the remove() method available on it, we get rid of the elements seen in the items array with the IDs qtError and qtResults. These are the areas where errors are reported and where query results are shown. As you'll see, they are re-created during this process. Next, the errMsg variable is initialized to an empty string and then the database is opened. Note that this code is not wrapped in try...catch because if the database happens to not exist (which means it must have been deleted since the cookies were last read in), then it will simply be created.

Then, we try to execute the query:

try {
  var rs = db.execute(sql);
  var headersDone = false;
  while (rs.isValidRow()) {
    if (!headersDone) {
      resultTablePanel = new Ext.Panel({
        layout : "table", autoScroll : true, anchor : "100% 58%",
        layoutConfig : { columns : rs.fieldCount() }, border : false,
        id : "qtResult"
      });

This is wrapped in try...catch, however, because exceptions can most definitely be thrown at this point and we want to be able to display them to the user.

The next task is to begin constructing an HTML table structure for displaying the query results (we don't know if there are any yet, but I'm getting ahead of things!). This will look very similar to the Browse tab of the Table Details Window that we saw earlier. So first we check the ResultSet to see if we have a valid row. The only time the call to rs.isValidRow() will return false is if the query returned no results, which will come into play shortly. But for now let's assume it returns true.

In that case we're basically going to be iterating over the ResultSet. The first element encountered is special because, as we're building an HTML table here, we need some column headers. So, the headersDone variable is checked, and it starts off as false, so we hit the if branch of the check.

In that branch we build a new Panel that uses a TableLayout. Since we have to define the number of columns, we need to ask the ResultSet how many columns there are. Fortunately, the rs.fieldCount() method gives us exactly that information so our TableLayout can be built properly. Since this Panel will eventually be added to the items array of the Window, and since we already established that an AnchorLayout is used there, we need to include the anchor attribute on this new Panel so that it works properly when the Window is resized.

Next, we need to add a column to the TableLayout for each field in the ResultSet. The rs.fieldCount() is again used to perform a loop:

for (var i = 0; i < rs.fieldCount(); i++) {
  resultTablePanel.add({
    html : "<div style="background-color:#dfe8f6;">" +
    rs.fieldName(i) + "</div>", cellCls : "cssTableHeader"
  });
  headersDone = true;
 }
}

For each, we create a <div> with a background color and those <div>s have the cssTableHeader style class applied to give them padding. The value inserted into the body of the <div> is the name of the field, obtained by calling rs.fieldName() and passing it the index of the field. At the end we set headersDone to true so that for the next iteration of the loop (meaning the next row of data, if there is any), we'll skip a generation of the headers.

So, that takes care of the headers. Remember at this point that we are already iterating over the records returned by the query, which means that we're currently processing the first real data that needs to be displayed. So, we need to generate table cells for the data in addition to the headers this time around:

for (var i = 0; i < rs.fieldCount(); i++) {
   resultTablePanel.add({
     html : rs.field(i), border : false, cellCls : "cssTableCell"
   });
  }
  rs.next();
}

It's the same sort of loop we used to generate the headers, but now we're just going after the field values in the normal rs.field(i) way that we've become used to. The cellCls attribute's value is now cssTableCell rather than cssTableHeader, but otherwise it's not much different. Figure 7-16 shows the result of all this hard work.

That concludes the iteration over the ResultSet. The next task is to account for queries that didn't return results. To do this, we simplify things a little and simply check to see if headersDone is false. If no results were returned, then headersDone would never have been set to true, and we're going to take that to mean that the query wasn't of a type that returned results (like a delete or update). This is a broken assumption, however, because a select query could return no results, in which case we'd be showing the "Rows affected" message in a case where it doesn't apply. Consider this an opportunity for you to enhance the application! But, it's probably good enough working this way anyway.

So, here's what happens when headersDone is false:

if (!headersDone) {
  resultTablePanel = new Ext.Panel({
    layout : "table", anchor : "100% 58%", border : false,
    layoutConfig : { columns : 1 }, id : "qtResult"
  });
  resultTablePanel.add({
    html : "Rows affected: " + db.rowsAffected,
    border : false, cellCls : "cssTableCell"
 });
}

We still need to create a table, but this time there's only a single column and it contains just a simple message telling how many rows were affected. The db.rowsAffected() method gives us that bit of metadata (notice that method is on the Database object and not the ResultSet object, as most of the others have been).

Once that's done, we have some minor cleanup to do:

rs.close();
  db.close();
  Ext.MessageBox.hide();
} catch (e) {
  db.close();
  errMsg = e;
}

Close the ResultSet and Database and then hide the MessageBox that was prompting for confirmation, and we're done—well, unless an exception occurred. In that case, we set errMsg to the exception value.

The final task is to ensure all this stuff gets put on the screen! Here's that code:

queryToolWindow.insert(4,
       { html : errMsg, anchor : "100% 10%", id : "qtError" }
     );
     queryToolWindow.add(resultTablePanel);
     queryToolWindow.doLayout();
    }
   }
  );

};

First, we call the insert() method on the Window to insert a new Panel whose contents is errMsg (which may be a blank string of course, but that's perfectly fine). The insert() method accepts as its first argument an index value that indicates where in the items array this element should be inserted. If you look back at the Window definition, the qtError element was the fifth element in the items array, so that's an index value of 4 in a zero-based array. Now, contrast this to the addition of the results table. For that we're using the add() method, which simply appends the element to the end of the items array, which is always where the results should go. Finally, a call to doLayout() causes the Window to redraw itself, resulting in all these UI changes being shown as expected.

7.5.8. Help.js

The final file to look at is Help.js, which is where the online help resides. To begin with, we have a field:

SQLWorkbench.currentHelpPage = −1;

This records what page of help is currently being looked at. There are three pages that can be displayed and the user can cycle through them, as you can see in Figure 7-17. Each page is displayed in a Window and the Windows are created dynamically. Creating them, though, requires that we have some content to display, and that content is contained in the following array:

SQLWorkbench.helpPagesContent = [
  "<h1><i><u>Creating/modifying databases</u></i></h1>" +
  "<br>" +
  "The Databases window shows the list of databases you can work with. You " +
  "can add a new database by clicking the Add Database button. This will " +
  "prompt for the name of the database. If the name you enter is a database " +
  "that already exists it will simply be added to the list. If the database " +
  "does not exist then it will be created and added to the list.<br><br>" +
  "To remove a database, click it and then click the <b>Remove Database</b> " +
  "toolbar button. The last database selected (since the last one to be " +
  "removed, if any) is the one that will be removed." +
  "<br><br>" +
  "Clicking a database opens the Tables window for that database, from which " +
  "you can work with tables.",

That's the first element of the array, and the value of the element is simply a giant string of HTML. This will become the content of the Window. There are similarly two more elements in the array, one for each page of help that there is, but there's probably no real benefit to displaying all the text here. Suffice it to say that they are defined just like the first element is: they too are just giant string concatenations where each formed string is the next element in the array.

The code that is called in response to the user clicking the Help Toolbar button is the showHelp() method:

SQLWorkbench.showHelp = function(inPageToDisplay) {

  new Ext.Window({
    id : "dialogHelpPage" + inPageToDisplay, closable : false,
    width : 400, height : 320, minimizable : false, resizable : false,
    draggable : true, shadow : false, maximizable : false, layout : "fit",
    autoScroll : true,
    title : "Help page " + (inPageToDisplay + 1) + " of " +
      SQLWorkbench.helpPagesContent.length,
    items : [
      { html : SQLWorkbench.helpPagesContent[inPageToDisplay],
        bodyStyle : "padding:8px" }
    ],

This first chunk of code builds a Window with an id that has the page of help tied to it (so dialogHelpPage0 or dialogHelpPage1, for example). The Window is not closable, maximizable or minimizable, or resizable, but it is draggable. The layout type is fit so that the content we insert fills the entire Window. The autoScroll attribute is set ot true to allow for scrolling of long help pages (there aren't any, but this way you could expand the text without worrying about it not being visible). The title attribute is a string that tells users what page they are looking at. Finally, the items array has a single element within it: a Panel that has the contents of the helpPagesContent array element corresponding to the help page currently being viewed.

Going back to the id value for a moment, the number appended as part of it is taken from the inPageToDisplay argument to the method. So, when the Toolbar button is clicked, the value passed in would be 0. However, when the user clicks the Previous Page or Next Page button, the following code is executed:

bbar : [
  { xtype : "button", text : "Previous Page",
    handler : function() {
      if (SQLWorkbench.currentHelpPage > 0) {
        SQLWorkbench.showHelp(SQLWorkbench.currentHelpPage - 1);
      }
    }
  },
  "-",
  { xtype : "button", text : "Next Page",
    handler : function() {
      if (SQLWorkbench.currentHelpPage <
        (SQLWorkbench.helpPagesContent.length - 1)) {
        SQLWorkbench.showHelp(SQLWorkbench.currentHelpPage + 1);
      }
    }
  },

Here we have a bottom Toolbar defined via the bbar attribute. Each button does some bounds checking to ensure there is a next or previous page to display, then calls showHelp() again, passing in the new value for currentHelpPage. The Window for the next page to display is constructed, if necessary, but isn't yet shown... we'll get to that right after this break:

"-",
  { xtype : "button", text : "Close Help",
    handler : function() {
      Ext.getCmp("dialogHelpPage" + SQLWorkbench.currentHelpPage).close();
      SQLWorkbench.currentHelpPage = −1;
    }
   }
  ]
}).show();

The Previous Page and Next Page buttons aren't the only ones on the bottom Toolbar. After a divider is inserted (just like the one I neglected to mention is between the Previous Page and Next Page buttons as well!), we have the Close Help button. This simply gets a reference to the currently opened Window, using the id value, and calls close() on it. Since only a single Window can be open at a time, that's all we need. In addition, the currentHelpPage field is set back to its starting value of −1, which strictly speaking isn't necessary, but I like having variables in known states whenever possible, so doing this satisfies that bit of obsessive-compulsive disorder on my part!

The next bit of code follows the Window construction and is responsible for showing the appropriate Window:

if (SQLWorkbench.currentHelpPage != −1) {
  Ext.get("dialogHelpPage" + SQLWorkbench.currentHelpPage).puff({
    duration : 1, remove : true
  });
}

Assuming we didn't just execute the code behind the Close Help button, then we get a reference to the Window currently being shown and use the puff effect on it to close it. The puff effect basically expands the Window and fades it out at the same time, like a puff of smoke does. Note that the call to the puff() method isn't blocking, so we can issue another effect at the same time, which comes into play next. By the way, the arguments to the puff() method specify that we want the effect to take one second to complete and that the element being puffed should be destroyed and removed from the DOM when the effect is completed.

Now, at the same time that the previous Window is puffed out of existence, the next Window to be displayed is faded in:

Ext.get("dialogHelpPage" + inPageToDisplay).fadeIn({
   duration : 1
  });
  SQLWorkbench.currentHelpPage = inPageToDisplay;

};

The fadeIn() method provides that effect, and again we do it over the course of one second. So, by the time the previous Window is gone, the new one is completely displayed, giving us a nice, smooth transition. Finally, the currentHelpPage is set to the new value provided by the inPageToDisplay argument, and the method is done.