Dabble DB Help

Written by Chad Fowler

Dabble lets you do a lot without ever having to write a line of code — from simple data modeling, to standard CRUD operations (creation, retrieval, update and deletion), importing and exporting data, searching, and back-linking, Dabble handles the common recurring data management tasks for you. Sometimes, though, you need your Dabble application to do something very specific to you or your users. You may have a custom algorithm you want to apply, or you might want to pass your data through a third-party web service. This is where the Dabble Plugin API comes in.

Dabble plugins allow Dabble applications to create new, derived fields by calling out to external HTTP-accessible applications. This solves the problem of safely enabling extension of a centrally-located hosted application, in that, while you’re writing code to extend and enhance the behavior of a Dabble application, your code never actually runs inside Dabble. You might even think of it as a plug out as opposed to a plug in.

So, with this talk of services over HTTP, you might be rightly concerned that the next step is to discuss Dabble’s XML web service protocol. You’ll be relieved to find out that Dabble plugins don’t speak XML. They speak CSV (Comma-Separated Values). Dabble passes UTF-8–encoded plain-text, comma-separated data in and expects UTF-8–encoded plain-text, comma-separated data out. Each row in your Dabble application generates a line in a CSV stream. To process a derived field for a Dabble category, you’ll simply need to process each incoming line of CSV and generate a new line of CSV as a response. If you’ve used a UNIX-based operating system, you’re probably familiar with the notion of pipes. The output of one program is piped into the input of another, creating a filter chain. This is conceptually the same as the way Dabble’s plugin IO works. Nice and simple.

I. A Simple Example

To best understand how all this works, let’s put together a simple example. Imagine you were an author and you used a Dabble application to track your own books as well as other books written in your field of work. Among other fields, you might track each book’s title, category, and ISBN number. A Book category with those three fields might look something like the following:

As a struggling author, you’re likely to want to track sales data for each of the books you’re interested in. Of course, you could always bookmark each book’s Amazon page and manually check its sales rank every day (Amazon sales rank might not give you specific information on how a book is selling industry-wide, but it’s a pretty good directional indicator). But that would take valuable time away from writing your next best-seller.

So let’s add a derived field to our Dabble application which will display each book’s Amazon sales rank.

The first step in creating a Dabble plugin is to create a configuration file for the plugin. This is how you tell Dabble what your plugin is capable of, and where on the network each remote service can be found. Here’s a sample configuration for our Amazon Sales Rank service:

[General]
Name = Amazon Sales Rank

[Sales Rank by ISBN]
URL = http://chadfowler.com/dabble/amazon_sales_rank.cgi
Input = Text
Output = Number

If you’ve ever had to configure a Windows workstation, this format should look instantly familiar to you. If not, it’s straight forward. The configuration file is broken into sections. Sections are delimited by bracketed headers [Like This] and white space. The header called [General] is special to Dabble and required for each plugin. This is the section that contains configuration for the entire plugin. In this case, we’re only naming the plugin. We’ll take a look at the additional options that can go here later.

Following the [General] section you can add sections for each service the plugin makes available. In our example, we only have one available service, which we’ve called [Sales Rank by ISBN]. According to the configuration here, the service is available at http://chadfowler.com/dabble/amazon_sales_rank.cgi, accepts text (the ISBN) as input and returns a number (the Sales Rank) as output.

Installing the Plugin

Now that we have the configuration file done, we’re ready to install the plugin into our Dabble application. To install the plugin, we’ll need to first upload it to an internet-accessible Web server. In this case, I’ve placed the file at http://chadfowler.com/dabble/amazon_sales_rank.txt.

After the plugin is uploaded, we’ll go into the Dabble’s Settings section and click the Plugins tab. There should be a section labeled Install New Plugin with a text box containing a partially filled URL. Type the URL for your plugin’s configuration file here, and click the Install Plugin button.

The page should refresh, and your plugin should appear in the list of installed plugins. If you make a change to the plugin’s configuration file, you can use the Reload feature on this page to cause Dabble to re-download and read the configuration of the plugin. Note that if you change the code associated with a plugin, it’s not necessary to reload the plugin, since the plugin itself is never loaded into Dabble’s memory.

Implementing the Plugin

Now that we’ve told Dabble about our plugin’s interface and where to find it, we’ll need to actually write some code.

Let’s take a look at the code which implements this service. We’ve chosen to write it as a simple Ruby CGI program, but any language and environment capable of making a Web application will work fine. Here’s the Ruby code for amazon_sales_rank.cgi:

#!/usr/local/bin/ruby
require 'cgi'
require 'csv'
require 'amazon/search'
cgi = CGI.new
print "Content-type: text/csv\n\n"
request = Amazon::Search::Request.new(amazon_dev_token = "YOUR_AMAZON_DEV_TOKEN")
CSV.parse(cgi.params['data'].first).each do |line|
    request.asin_search(line.first) do |product|
        puts product.sales_rank
    end
end

Before you can type this code in and use it, you’ll need to get an Amazon developer token from the Amazon Web Services site. It’s free, quick, and painless, so don’t let the token deter you from following along at home.

Let’s walk through the code:

The first few lines load Ruby libraries we’ll be using in the program. The CSV and CGI libraries ship with the standard Ruby install. We also require Ian Macdonald’s excellent Ruby/Amazon library, which makes accessing Amazon’s Web Services really easy. After requiring the libraries we’ll need, we create a CGI object which we’ll use for parsing incoming parameters on the HTTP request. Since we’re going to be responding with CSV data, we set the Content-type of the response to the appropriate MIME type.

With all of the setup out of the way, the fun part starts. We first create an Amazon search request object, passing in our developer token. Then we parse the incoming data as a CSV string. Dabble sends data to plugins as a string in the parameter data. Since Ruby’s CGI library supports multiple incoming parameters with the same name, we have to take the first (and only) occurrence of the data parameter. Each line of input represents one row, or entry, in our Dabble category. The CSV parser splits each line, yielding a parsed array called line containing that line’s elements. Since we’re going to have Dabble send us the ISBN number of each book, each line will contain only one element. We pass that element to the Amazon ASIN search service. For books, Amazon’s ASIN (Amazon Standard Identification Number) is the same as the ISBN. Each search should return one product. From each product, we print the sales rank, giving us a single-element row of CSV.

The important pattern to notice here is that, for each incoming row of CSV, we respond with one row of CSV in the same order. The position of each row is how Dabble matches our output with the rows in our Dabble category. Again: one row in; one row out.

Displaying the Derived Field

Now that we have the code ready to run, we simply need to add the derived field to a view in our Dabble application. Here’s how:

Navigate to your application’s Book category. You can optionally choose a custom view to add the derived field to, but the default view works just as well. Add the ISBN field to the view if it’s not already present. After you’ve added the field, click on its column header and choose Add Derived Field. You should then see a list of any derived fields you’ve configured via the plugins installed in your application. The derived field name in this case should be Sales Rank by ISBN, as we specified in our plugins configuration file.

The derived field should now appear in the category view. If all went right, each row will now display the book’s Amazon sales rank as in the picture below.

II. Adding Visual Elements

Text is great, but with as much data as the average person processes in a typical day, sometimes a visual representation of the data can make a big difference in a data-centric application. In our previous example, having the actual sales rank on-hand is useful, but most of the time what you really want to know is if a book is selling well. You don’t care if its rank is 3000 versus 3400. You just want a directional indicator.

In this case, a simple image can help significantly. Given a set of thresholds, we could represent the sales ranks as green, yellow, or red as shown in the image below:

Creating this plugin is surprisingly simple. Dabble plugins support a data type called Image. When a plugin is configured to respond with an Image, Dabble will interpret the response as a URL to an image to be embedded as an <image> tag. Here’s the configuration:

[Rankometer]
URL = http://chadfowler.com/dabble/rankometer.cgi
Input = Number
Output = Image

This service accepts a Number as input and responds with the URL of an image. Here’s the code:

require 'cgi'
require 'csv'
GREEN_THRESHOLD = 4000
YELLOW_THRESHOLD = 50000

cgi = CGI.new
print "Content-type: text/csv\n\n"
CSV.parse(cgi.params['data'].first).each do |line|
    rank = Integer(line.first) rescue 0
    color = case rank
                        when 0
                            "grey"
                        when 1..GREEN_THRESHOLD
                            "green"
                        when GREEN_THRESHOLD..YELLOW_THRESHOLD
                            "yellow"
                        else
                            "red"
                        end
    puts "http://chadfowler.com/dabble/images/#{color}_light.gif"
end

The basic idea is that we have four image files representing good, so-so, and bad sales ranks. For each incoming row of CSV data, we determine which threshold the provided sales rank fits into, and return the URL of the appropriate image for that threshold.

Deriving from a derived field

Adding the derived field for these images is the same as in the previous example. Go to the view we created earlier which includes each book’s sales rank. Click the column heading for the sales rank field and choose Add derived field. You should see Rankometer in the list of possible derived fields. Clicking Rankometer should add the image to your view.

This brings up an interesting point. Once you’ve created a derived field, it is a Dabble field. Other than the fact that its actual values are retrieved via a service, it behaves exactly like any other Dabble field. This means you can chain derived fields. The values returned by one service can be fed to yet another service. For example, the values from a concurrency conversion service could then be piped to a sales tax service, which could potentially be hosted on an altogether different system by a third party.

Given that derived fields act essentially like normal fields, you also have access to the usual Dabble field operations, such as filtering, subtotaling and grouping. For example, since the images in our Rankometer field are essentially a categorization, we could group by the Rankometer field to see sublists of books by how well their sales are generally doing.

To try it out, click the column header for the Rankometer field and select By Exact Value from the Group menu. Note that the grouping we’re doing here is by the image’s URL. Dabble doesn’t know anything about the contents of the image. It just knows (and renders) the URL returned by the plugin. And, given that the URL is a string, we can use it as a value by which to group. Now that we’ve built these groups, we could use them as subsets with which to filter and subtotal.

III. Caching

By now, you might rightly be concerned that Dabble plugins are going to result in many calls from Dabble to your services. If every Dabble page view results in one call for every derived field, performance would surely suffer.

To avoid this problem, Dabble caches the values retrieved from plugins. You configure caching in the each derived field’s section of your plugin’s configuration file. Here’s an example configuration:

[Sales Rank by ISBN]
URL = http://chadfowler.com/dabble/amazon_sales_rank.cgi
Input = Text
Output = Number
TTL = 1 hour

The TTL setting tells Dabble that values retrieved for this field should be refreshed every hour or whenever the source data changes (see the section below for more information about the possible values of TTL). If you leave this setting out, Dabble will cache the field’s values until the source data changes. In some cases, you really need data to be timely, and caching won’t work. This is especially true as you develop a plugin, since your source data will likely remain static as you work, while the plugin’s output will be continuously refined. To turn off caching for a field, set the field’s TTL to 0.

Note that, though we’ve set a time value in the plugin’s TTL configuration, Dabble won’t necessarily call your plugin and refresh the data right at the time specified in the TTL. The TTL setting specifies a maximum Time-to-Live for the data, but the data will not be refreshed unless a view which accesses that data is accessed. This means that if you set a TTL of 3 minutes and only hit the Dabble application once per hour, Dabble would only request new data once per hour.

IV. The Plugin Configuration File Format

As we’ve seen, a plugin configuration file is a simple text file with a format resembling that of a Windows .ini file. The configuration file consists of one or more sections. Sections are blank-line delimited and start with a section heading in square brackets. Inside a section are new-line separated name/value pairs of the format: name = value.

Every plugin must have a General section. The General must include a key called name, which will be used as the display name for the plugin. The following is the General section from our sample plugin:

[General]
Name = Amazon Sales Rank

After the General section comes zero or more sections detailing specific services the plugin provides. Each service’s section must contain at least three fields: a URL at which the service is accessible, the type of value passed from Dabble as Input to the plugin, and the type of the plugin’s Output. As detailed above, each service can also specify a TTL, which tells Dabble the frequency at which it should refresh its cached copy of the plugin’s output.

Dabble accepts the following types for input and output:

Type	Description
Text	Raw text
Address	A string representation of a street address, like: 123 Main St., Denver, CO 10333
Image	The URL of an image, such as: http://dabbledb.com/images/logo2.gif
Number	A number. Can be a floating point, but never includes any extra formatting (such as commas between thousands)
Date/Time	A date expressed in seconds since the epoch (January 1, 1970)
Money	A number followed by an optional currency code
Duration	A length of time expressed in number of seconds

When Dabble sends values to your plugin, the values will be sent as “raw data”, not necessarily the same way they are displayed in your view. So, for example, if you have a duration which displays as “2 days” in your application, Dabble will send “172800” to your plugin (the number of seconds in 2 days).

V. Debugging

What we’ve described so far is what happens when everything’s working right. Sometimes, of course, you’ll run into problems. You may write a new plugin and, for some reason, when you try to use the plugin, the derived fields are blank or don’t contain the values you expect. For situations such as these, Dabble provides a simple plugin debug log.

To access the log, first use one of your derived fields in a view. After you’ve executed the view (and, therefore, your plugin), go to the Plugins tab in your application’s Settings section. For each plugin, the available derived fields are listed. Click on the name of the derived field you just used, and a new window should pop open.

The new window contains two sections. The first is a dump of the raw CSV data passed into your plugin. The second is the raw response data.

It’s a simple mechanism, but if you run into a problem that isn’t obvious, it can save a lot of time.

Parting Thoughts

Dabble plugins are a simple but powerful way to extend a Dabble application. While a vanilla Dabble application can do an awful lot for you, plugins give you the extra ingredients you need to integrate virtually any type of data or processing logic imaginable. And they give you this power via the familiar metaphor of operating system pipes: data from one process is piped into another, transformed, and streamed out the other end.

This simple system greatly expands the possible uses of Dabble, and as you start to build your own plugins you’ll think of more and more ways to use Dabble to manage your data.