“A DBMS as an application server? Is it Lotus Notes?”
CouchDB
CouchDB seemed odd to me in 2009 because it had … no SQL! This led me to play with it and then other NoSQL offerings. CouchDB features include:
- Each database is a collection of key-value entities
- Any database may be replicated to any other, at any time, or continuously
- Each value is a JSON document
- Sandboxed map/reduce, list, and show functions transform data
- CouchDB serves HTTP requests for an attachment as an HTTP response
Those last two bullets caught my geeky eye. CouchDB could host all three MVC layers?
It includes Futon, a web-based administration console written in the public API. There is separation of MVC concerns imposed by a framework of data map/reduce view queries and UI show and list functions. A developer codes them, but they are stateless, sandboxed JavaScript functions having no side effects. An MVC controller is any programmer-supplied script that reacts to events by calling list or show functions through a REST API. I conceived a query-only mobile web application – a Couch App – that scales by replication.
I used Futon to learn to create a database and add a design document for holding an index.html, map/reduce, list, and show functions. It was easy to insert a couple of test documents, some functions, and then run those functions. I found it off-putting to have to write a map function to create a data-view to carry out a query that I could do in seconds in Oracle, MySQL, or MongoDB. I got past my objection. A CouchDB view became like a SQL view to me.
Opto3 Database
I hoped to use the simplest tools, trying to not depend on much beside what CouchDB offers. That dog didn’t hunt.
I created and edited view, show, and list functions using an opaque database as my workspace and Futon as my editor. All JavaScript functions must fit into JSON records. Escaping quotes and managing JavaScript braces embedded in JSON is intolerable. I wanted a local workspace that I could push to CouchDB after each code change – something that I could distribute in GitHub. Moreover, I needed CommonJS modules so I could require packages such as handlebars. How to insert shallow directory trees into a design document?
I fell in love with Kanso. It describes dependencies in a kanso.json file. Java Maven users will see a resemblance to a POM. Want a dependency? Name it in kanso.json. Kanso properly pushed artifacts described by that file.
Demo data? I downloaded a Maxmind free demo CSV file of 200 optometrist locations. The app would be an optometrist look-up. Kanso commands enable you to decorate a CSV file with CouchDB unique ID columns and convert to JSON. I let CSV column names become keys for CSV column values. I created an opto3 database through Futon. In a command window, I issued a Kanso command to upload demo JSON into my opto3 database.
Immediately, the _all_docs REST query was able to list my entire database. See Figure 1. Every document has a CouchDB-generated identity, plus a revision ID prefixed by a sequential revision number. The key can be any JSON object, duplicates allowed. CouchDB stores the ID value into the key if you don’t supply an explicit key.
During coding, I repeatedly used Kanso to push updates to my design document. Artifacts landed at intended places. A local directory was source for pushes.
Figure 1
If I displayed the database in Futon, I could click a row to see the document data, as shown in Figure 2:
Figure 2: Document detail from clicking an ID
The REST path suffixed by an ID would return that ID’s document JSON in a browser or curl command line. The raw document for one optometrist is:
curl -X GET http://localhost:5984/opto3/3770717789a226c91f8ce4808e31cddd {“_id”:”3770717789a226c91f8ce4808e31cddd”,”_rev”:”1-c9818488639a0326aefc0624aba5bde2″,”npi”:”1003868795″,”full_name”:”David L Kjelland”,”first_name”:”David”,”middle_name”:”L”,”last_name”:”Kjelland”,”title”:”OD”,”mailing_address1″:”Po Box 211″,”mailing_city”:”Mineral Point”,”mailing_state”:”WI”,”mailing_zip”:”53565-0211″,”mailing_phone”:”608-987-3301″,”mailing_fax”:”608-987-3045″,”street_address1″:”318 High St”,”street_city”:”Mineral Point”,”street_state”:”WI”,”street_zip”:”53565-1219″,”street_county”:”Iowa”,”street_msa”:”0″,”street_phone”:”608-987-3301″,”street_fax”:”608-987-3045″,”gender”:”M”,”specialty_code”:”152W00000X”,”specialty”:”Optometrist”,”license_number”:”1588-035″,”license_state”:”WI”}
A Mobile Application
I created a single-page jQuery Mobile index.html consisting of four progressive drill-down logical pages:
- splash
- list of states showing counts of optometrists in each
- list of optometrist city addresses for a give state
- details of one optometrist
A database has one or more design documents denoted by a record ID of _design/something. I chose _design/opto. I inserted the map functions (model) needed to feed two list functions (view). In a Futon pull-down I could choose a view, see its code, and toggle any optional reduce function.
Figure 3 shows the view map code along with results for a count_by_state view. CouchDB calls the map function once per document (row). That function calls CouchDB’s emit function with a key and value, feeding into a new internal B-Tree collection. That expense occurs only when a view is created or updated. See “View Code” in the listing. Notice that CouchDB sorted documents by key. Of what use is a collection of “1s” keyed by sorted state abbreviations? Hang on.
Figure 3: Count-by-state map without reduce
In Figure 4, I have set the “Reduce” option. The display morphed to a sorted list of state keys, each having a value that is a count of optometrists in that state. The reduce function produced the sum of values for each state key (i.e. the sum of each 1 value per instance in each state). Cool! I needed that data for a top-level list page that would display clickable USA states, each showing an optometrist count. I would pass the view reference to a list function that would generate a line of HTML for each row of the reduced collection. For example, there are two optometrist records for Arkansas in the demo database. There are two “AR” records in Figure 3. After the reduce, Figure 4 shows an “AR’ having a count of two. Good.
Figure 4: Count by state map with reduce
The drill-down panel for a given USA state needed a data view consisting of only a map function. See Figure 5. That function emits a compound key of state-city to a value of the optometrist address. If we filter that view for just one state in a consuming list function, the cities collate in natural ascending sort order, each having an optometrist address.
That’s perfect for populating the cities list after clicking a state. CouchDB always sets a record ID in every document. The detail panel page change only needs access to the ID to retrieve and display the details of that optometrist record.
Figure 5: Sorted list of state, cities with address values
I RESTfully tested the view, show, and list functions, trying both curl and a browser, before tackling the UI.
CouchDB View Functions
Views are data, not rendered HTML. I exported map/reduce functions to a CommonJS module.
[__strong id=internal-source-marker_0.9778130722697824">// Used for panel two
exports.count_by_state = {
map: function(doc) { emit([doc.street_state], 1); },
reduce: function (key, values, rereduce) { return sum(values); }
};
// Used by panel three
exports.sorted_states_cities = {
map: function(doc) { emit([doc.street_state, doc.street_city], doc.street_address1); }
};
CouchDB List functions
Two list functions return collections of HTML line items via the CouchDB provides() function and getRow() iterator. Handlebars produces the dynamic HTML.
[__strong id=internal-source-marker_0.9778130722697824">// Populates list in panel two
exports.list_states = function(doc, req) {
provides('html', function() {
var Handlebars = require('handlebars');
var template = Handlebars.templates['stateName.html'];
var html = '';
while (row = getRow()) {
var context = { key: row.key, value: row.value};
html += template(context);
}
return html;
}
)
};
// Populates list in panel three
exports.list_cities = function(doc, req) {
provides('html', function() {
var Handlebars = require('handlebars');
var template = Handlebars.templates['cityName.html'];
var html = '';
while (row = getRow()) {
var context = { id: row.id, state: row.key[0], city: row.key[1], address: row.value};
html += template(context);
}
return html;
}
)
};
CouchDB Show function
A single show function uses a handlebars template to produce detail HTML from a document parameter and a document ID set by onclick.
"code-string" id="<span">internal-source-marker_0.9778130722697824">exports.detail = function(doc, req) {
[__strong id=internal-source-marker_0.9778130722697824"> var Handlebars = require('handlebars'); [___strong_>
var context = {
_id: doc._id,
full_name: doc.full_name,
title: doc.title,
street_address1: doc.street_address1,
street_address2: doc.street_address2,
street_city: doc.street_city,
street_state: doc.street_state,
street_zip: doc.street_zip,
street_county: doc.street_county,
street_phone: doc.street_phone,
gender: doc.gender,
specialty: doc.specialty
};
var template = Handlebars.templates['detail.html'];
var html = template(context);
return html;
};
Index.html
I used jQuery Mobile to create a four-page “single HTML page” mobile demo. You can replicate the application from http://mauget.cloudant.com/opto3 to your own CouchDB.
Controller
I tied views to list or show functions within scripts/controller.js through handlers fired by jquery-mobile-routerlite page change events. A jQuery-mobile button is a styled HTML hyperlink. Clicking a button causes a page-change event. The button on-click action stores an application-wide key used by the handler to find and render the result.
Handlebars Templates
Handlebars templates support the show function and two list functions. Recall that panel two and panel three onclick actions set a variable used by a template. See the italicization in the following two templates:
Panel two list item button template used by list_states
<li><a href="#three" onclick="APP.key='{{key}}';"> {{key}} <span> {{value}} </span></a></li>
Panel three list item button template used by list_cities
<li><a href="#four" onclick="APP.id='{{id}}';"> {{address}}, {{city}}, {{state}} </a></li>
Panel four show template used by “detail”
"code-string" id="<span">internal-source-marker_0.9778130722697824"><dl title="Detail">
<dt><strong>Name</strong></dt>
<dd>{{full_name}}, {{title}}</dd>
<dt><strong>Specialty</strong></dt>
<dd>{{specialty}}</dd>
<dt><strong>Gender</strong></dt>
<dd>{{gender}}</dd>
<dt><strong>Address</strong></dt>
<dd>{{street_address1}}</dd>
<dd>{{street_address2}}</dd>
<dt><strong>City, State</strong></dt>
<dd>{{street_city}}, {{street_state}}</dd>
<dt><strong>Zip</strong></dt>
<dd>{{street_zip}}</dd>
<dt><strong>County</strong></dt>
<dd>{{street_county}}</dd>
<dt><strong>Phone</strong></dt>
<dd>{{street_phone}}</dd>
</dl>[___strong_>
Screen Captures from iPhone
I pushed the workspace to GitHub.
The following screens are in drill-down order taken from an iPhone 5:
Figure 6: Animated GIF splash screen
-
Figure 7: List of US states with optometrist counts
-
Figure 8: Drill-down to Missouri optometrists
-
Figure 9: Details for one optometrist
The URL for a CouchApp is ugly. A production environment could redirect a “nice” URL to a Couch App that could reside in many CouchDB instances.
Conclusion
The geek in me says a mobile application hosted entirely in a DBMS that maintains good MVC separation is neat. New knowledge required, but not much coding involved. It could scale out by deploying Opto3 to many replicated instances. I have no idea how well it really scales as an application server. That and granular security are for future research.
CouchDB has structured update and delete functionality in addition to views. My app is a query-only app, but if the data changes, the app renders the change. I added a fake Alaska optometrist through a curl incantation. The app picked up the new state, city, and optometrist the next time I returned to the states page.
My paranoid side is wary. I had a learning curve, as would others. Would I suggest this all-in-one architecture to my client? Not now, for production. How about as an ad-tech proof-of-concept for their mobile applications? Certainly! I’d add CRUD and leverage role-based security also.
– Lou Mauget, asktheteam@keyholesoftware.com
References
- CouchDB, http://couchdb.apache.org/
- CouchDB Document API, http://wiki.apache.org/couchdb/HTTP_Document_API
- jquery-mobile-routerlite, https://github.com/1Marc/jquery-mobile-routerlite
- jQuery mobile , http://jquerymobile.com/
- handlerbars, http://handlebarsjs.com/
- CommonJS, http://www.commonjs.org/
- JSON, http://www.json.org/
- Kanso, http://kan.so/
- GeoLite Free Downloadable Databases, http://dev.maxmind.com/geoip/geolite
- CouchAPP, http://couchapp.org/page/index
- jquery Mobile + CouchDB, http://custardbelly.com/blog/2010/12/08/jquery-mobile-couchdb-part-1-getting-started/
- Is NoSQL The SQL Sequel? http://keyholesoftware.wordpress.com/2012/10/01/is-nosql-the-sql-sequel/
- NOSQL Databases for web CRUD (CouchDB) – Shows/Views, http://ilyasterin.com/blog/2010/02/nosql-databases-for-web-crud-couchdb-showsviews.html
- How feasible it is to really develop web applications just using CouchDB and client-side JavaScript, with no middle-tier?, http://www.quora.com/CouchDB/How-feasible-it-is-to-really-develop-web-applications-just-using-CouchDB-and-client-side-JavaScript-with-no-middle-tier
Tagged: CouchDB, jQuery Mobile, Kanso, Opto3 Database 
