I just discovered, about 4 days ago, that you can format Visio database diagrams in the IDEF1X format. While IDEF1X has many shortcomings, I have to say I find it much easier to understand the relationships and constraints at a quick view in this format rather than the standard format.

Granted I’m still running the 2001 version of Visio for Enterprise Architects, so I’m sure that recent releases have more formats in which to view an object model. However, for the time being, I’m having fun with opening all my old schemas and changing their format.

To get your model in this format, you choose the following menu option:
Database > Options > Document…

Then in the General tab, select IDEF1X as the symbol set.

Ok, I just learned how to do levels and partitioning in an Oracle query today, and I have to say it’s pretty cool. Basically, I have a view that returns groups of data; i.e. multiple rows with matching “key” attributes but different non-prime attributes.

Simplistic example:

------------------------------------------------------
3786       01/26/2008      1        3      0578
------------------------------------------------------
3786       01/27/2008      1        3      0579
------------------------------------------------------
3786       01/28/2008      1        3      0581
------------------------------------------------------
3787       01/29/2008      1        3      0587
------------------------------------------------------
3787       01/30/2008      1        3      0591
------------------------------------------------------

I was wracking my brain trying to figure out how, with one query, I could properly aggregate this data into one resultset. Using OVER PARTITION BY, SYS_CONNECT_BY_PATH, and the LEVEL Pseudocolumn, I came up with a pretty slick resultset that groups certain columns from multiple rows into one parent row.

SELECT column1,
   ltrim(column2, '<br/>') column2,
   column3,
   column4,
   ltrim(column5, '<br/>') effective_date
FROM (SELECT row_number() over(PARTITION BY column1 ORDER BY column1, ord_level DESC) aggregateRow,
        column1,
        column2,
        column3,
        column4,
        column5
      FROM (SELECT column1,
                  column3,
                  column4,
                  LEVEL ord_level,
                  sys_connect_by_path(column2, '<br/>') column2,
                  sys_connect_by_path(column5, '<br/>') column5
           FROM (SELECT column1,
                       column2,
                       column3,
                       column4,
                       column5
                       row_number() over(PARTITION BY column1 ORDER BY column1, mapped_facility_no) dataSet
                   FROM   database_table
                   ORDER  BY column1, column3) x_alias
            CONNECT BY column1 = PRIOR column1
            AND dataSet - 1 = PRIOR dataSet
           )
    )
WHERE  aggregateRow = 1
ORDER  BY column1

Resultset:

------------------------------------------------------
          01/26/2008                     0578
3786      01/27/2008      1       3      0579
          01/28/2008                     0581
------------------------------------------------------
3787      01/29/2008      1       3      0587
          01/30/2008                     0591
------------------------------------------------------

In addition to the basic ComboBox functionality available in the Ext library, there is a poorly-documented extension called the TwinTriggerField. This is simply a standard ComboBox with two control icons on the right which you can customize.

This article is an extension of my Ext: Simple Autocomplete Example article, so please read that one as well to get all related code. This article will simply show the additional code needed to make a TwinTrigger work.

By default the ComboBox will have the down arrow which allows users to see the available elements, but you can add another icon to the right of that which, when clicked, can perform any function that you like.

The TwinTrigger Class

Start off by making a new Javascript file in your project named Ext.TwinTrigger.js and paste the following code into it. We’ll go through the code later to show what’s going on.

Ext.form.TwinTriggerField = function(config) {
    Ext.form.TwinTriggerField.superclass.constructor.apply(this, arguments);
};
Ext.extend(Ext.form.TwinTriggerField, Ext.form.ComboBox, {

    trigger1Class: 'x-form-search-trigger',
    trigger2Class: 'x-form-select-trigger',

    initComponent : function(){
        Ext.form.TwinTriggerField.superclass.initComponent.call(this);
        this.record = new Object();
        this.triggerConfig = {
            tag:'span', cls:'x-form-twin-triggers', cn:[
            {tag: "img", src: Ext.BLANK_IMAGE_URL, cls: "x-form-trigger " + this.trigger1Class},
            {tag: "img", src: Ext.BLANK_IMAGE_URL, cls: "x-form-trigger " + this.trigger2Class}
        ]};
    },
    getTrigger : function(index){
        return this.triggers[index];
    },
    initTrigger : function(){
        var ts = this.trigger.select('.x-form-trigger', true);
        var triggerField = this;
        ts.each(function(t, all, index){
            t.hide = function(){
                var w = triggerField.wrap.getWidth();
                this.dom.style.display = 'none';
                triggerField.el.setWidth(w-triggerField.trigger.getWidth());
            };
            t.show = function(){
                var w = triggerField.wrap.getWidth();
                this.dom.style.display = '';
                triggerField.el.setWidth(w-triggerField.trigger.getWidth());
            };
            var triggerIndex = 'Trigger'+(index+1);

            if(this['hide'+triggerIndex]){
                t.dom.style.display = 'none';
            }
            t.on("click", this['on'+triggerIndex+'Click'], this, {preventDefault:true});
            t.addClassOnOver('x-form-trigger-over');
            t.addClassOnClick('x-form-trigger-click');
        }, this);
        this.triggers = ts.elements;
    },
    onTrigger1Click : function() {
        this.onTriggerClick();
    },
    onTrigger2Click : function() {
        this.onTrigger2Click();
    }
});

The User’s View

Then include your file in an HTML page.

<html>
<head>
    <link href="css/ext-all.css" rel="stylesheet" type="text/css">

    <script type="text/javascript" src="js/ext-all.js"></script>
    <script type="text/javascript" src="js/Ext.TwinTrigger.js"></script>
    <script type="text/javascript" src="js/interaction.example.js"></script>
</head>

<body>
    <input type="text" size="20" id="facilitySearchField">
</body>
</html>

The Interaction Layer

And in your interaction layer, create an instance of your TwinTrigger field. In this example, you’ll see I’m using the facilityStore object that I set up in the previous article.

var search = new Ext.form.TwinTriggerField({
    applyTo:'divName',
    displayField:'name',
    store: facilityStore,
    minChars:4,
    forceSelection:true,
    width: 210,
    listWidth:350,
    onSelect: function(record){    },
    onTrigger2Click: function(){    }
});

Custom Style for Second Trigger

If you want to have a custom icon for the 2nd trigger, you’ll have to do two things.

First, define a custom CSS class and specify it in the Ext.TwinTrigger.js file. You can name this class anything you like, but try to keep it consistent with Ext’s naming conventions. You can see the one that I chose in my code above.

trigger2Class: 'x-form-select-trigger',

Second, modify the ext-all.css style sheet and specify the image that you’d like to use for your new class.

.x-form-field-wrap .x-form-select-trigger{background-image:url(../images/default/form/select-trigger.gif);cursor:pointer;}

The Guts

Now that I’ve laid all the code out, I show you the code to focus on. The important code in your TwinTrigger class is…

onTrigger2Click : function() {
    this.onTrigger2Click();
}

What this does is expose a new event that you can handle in your interaction layer.

onTrigger2Click: function(){
    // Do something wonderful when the user clicks the 2nd trigger icon
}

Those are the basics for having two trigger icons for a ComboBox. Like I said, refer to my previous article on how to get the basics of a autocomplete ComboBox working, and then implement this code if you need it.

Comments and questions, as always, are welcome.

It’s somewhat difficult to find examples of the autocomplete ComboBox that the Ext library provides, so I’ll add another one to the mix in the hopes that it makes it easier for future implementers to find.

First, let’s look at the code you need. The Ext stylesheet and the ext-all.js library. Then you’ll need your own, custom interaction code. My naming convention is to start with interaction and then the page to which the code applies.

<link href="css/ext-all.css" rel="stylesheet" type="text/css">

<script type="text/javascript" src="js/ext-all.js"></script>
<script type="text/javascript" src="js/interaction.example.js"></script>

This article is going to focus on the HTTPProxy code for the autocomplete feature. The one argument you need is URL, and it value will be the name of the file that is actually going to perform the query and return the results. This code is simply creating a connection to a page that will be used when the user types in a search string.

In the example I’m pulling from, I’m searching against a list of facilities for the company.

facilityProxy = new Ext.data.HttpProxy({url: 'liveQueries/facilities.cfm'});

When the user types in a search string, Ext will then use the HTTPProxy to call facilities.cfm with a URL variable named query that contains the search string. Therefore, if the user typed in ‘PHIL’, the proxy URL would be liveQueries/facilities.cfm?query=PHIL.

Now let’s look at the facilities.cfm code. First, we have to capture the query variable being passed to the page by Ext, which can be done simply with a <cfparam> tag. Then we execute our query. Once we have the resultset, we’ll need to serialize it. I like JSON serialization, so I used the CFJSON code from Thomas Messier, Jehiah Czebotar, and others.

<cfsetting enablecfoutputonly="true">

<cfparam name="query" default="">

<cfquery name="facilities" datasource="#datasource_name#">
select unique facility_no, facility_legal_name
from chg_facility
where (REGEXP_LIKE(facility_no,'#query#','i') OR REGEXP_LIKE(facility_legal_name,'#query#','i'))
order by facility_no asc
</cfquery>

<cfscript>
jsonBean = createobject("component","webapps.charm.model.ajax.JSON");
jsonEncodedCriteria = jsonBean.encode(data=facilities, queryFormat="array");
writeOutput(jsonEncodedCriteria);
</cfscript>

<cfsetting enablecfoutputonly="false">

Ok, so now we’ve got a JSON-serialized query. What do we do with it? Well, Ext just happens to have a built-in JSON reader. Just create a new JsonReader object, tell it what node contains the data (in our case, the node name is data) and optionally provide a totalProperty argument that contains the total number of records returned.

You then provide a defintion of what a single record of data consist. You can define a seperate object called a Record….

facilityRecord = Ext.data.Record.create([
    {name: 'facility_no',         	type: 'string'},
    {name: 'facility_legal_name',	type: 'string'}
]); 

facilityReader = new Ext.data.JsonReader({
    root: 			"data",
    totalProperty:	"recordcount"
}, facilityRecord);

Or just do it inline if the record is simple enough.

facilityReader = new Ext.data.JsonReader({
    root: 			"data",
    totalProperty:	"recordcount"
}, [
	{name: 'facility_no',         	type: 'string'},
	{name: 'facility_legal_name',	type: 'string'}
]);

Alright, so we’ve got a proxy object to facilities.cfm that will perform the query on the user’s search string and return JSON-serialized data. We’ve defined the structure of each record, and use a built-in JSON reader to parse the results.

Lastly, we need to populate a data Store with the deserialized data set that we’ve retrieved. We simply provide it with the name of the proxy we’ll be using and which reader it should use to deserialize the data.

facilityStore = new Ext.data.Store({
    proxy: facilityProxy,
    reader: facilityReader
});

You can also define each element inline instead of creating a separate variables for each object. Here’s an example:

new Ext.data.Store({
    proxy: new Ext.data.HttpProxy({url: 'liveQueries/facilities.cfm'}),
    reader: new Ext.data.JsonReader({
        root: 			"data",
        totalProperty:	"recordcount"
    }, [
    	{name: 'facility_no',         	type: 'string'},
    	{name: 'facility_legal_name',	type: 'string'}
	])
})

Now that’s we’ve got some interaction code running, let’s start creating the actual ComboBox. Create a simple HTML file and place an input element on the page with a unique name.

<input type="text" size="20" id="facilitySearchField">

Then, back in your interaction code, let’s create a ComboBox instance and tell it to use the data store that we’ve already defined.

var search = new Ext.form.ComboBox({
    store: facilityStore,
    minChars:2,
    itemSelector: 'div.search-item',
    tpl: new Ext.XTemplate(
        '<tpl for="."><div class="search-item">',
            '{facility_no} - {facility_legal_name}',
        '</div></tpl>'
    ),
    onSelect: function(record){
        // What you want to happen when the enter selects a record (or hit the ENTER key)
    	// Example (redirect to another page):
        //    document.location.href = 'showFacilityDetails.cfm?facilitySelected&fid=' + record.data.facility_no;
    }
});

// Apply the comboBox to the &lt;input&gt; element in our HTML page.
search.applyTo('facilitySearchField');

Summary

HTML Code (example.htm)

<html>
<head>
    <link href="css/ext-all.css" rel="stylesheet" type="text/css">

    <script type="text/javascript" src="js/ext-all.js"></script>
    <script type="text/javascript" src="js/interaction.example.js"></script>
</head>

<body>
    <input type="text" size="20" id="facilitySearchField">
</body>
</html>

Javascript Code (interaction.example.js)

facilityProxy = new Ext.data.HttpProxy({url: 'liveQueries/facilities.cfm'});

facilityRecord = Ext.data.Record.create([
    {name: 'facility_no',         	type: 'string'},
    {name: 'facility_legal_name',	type: 'string'}
]); 

facilityReader = new Ext.data.JsonReader({
    root: 			"data",
    totalProperty:	"recordcount"
}, facilityRecord);

facilityStore = new Ext.data.Store({
    proxy: facilityProxy,
    reader: facilityReader
});

var search = new Ext.form.ComboBox({
    store: facilityStore,
    minChars:2,
    itemSelector: 'div.search-item',
    tpl: new Ext.XTemplate(
        '<tpl for="."><div class="search-item">',
            '{facility_no} - {facility_legal_name}',
        '</div></tpl>'
    ),
    onSelect: function(record){   }
});
search.applyTo('facilitySearchField');

ColdFusion Code (facilities.cfm)

<cfsetting enablecfoutputonly="true">

<cfparam name="query" default="">

<cfquery name="facilities" datasource="#datasource_name#">
    // Perform search based on user's search string (query parameter)
</cfquery>

<cfscript>
jsonBean = createobject("component","webapps.model.ajax.JSON");
jsonEncodedCriteria = jsonBean.encode(data=facilities, queryFormat="array");
writeOutput(jsonEncodedCriteria);
</cfscript>

<cfsetting enablecfoutputonly="false">

Just some pics of my girl dancing around to “I’m Too Sexy”. She then, of course, spins in circles until she gets too dizzy and then walks around running into walls or just falling on her butt. I usually let Michelle handle posting pics like this, but they’re so darn cute, I couldn’t resist.

As I always tell her, she’s a silly monkey.