Mad Dog 21/21: The Codd Piece
February 13, 2012 Hesh Wiener
IBM used to boast that its mainframes and their DBMS packages manage most of the world’s business and government records. Maybe that’s still true. There are, however, other contenders for that distinction. One is Oracle. And then there’s the little DBMS that holds more data than DB2 and Oracle combined: SQLite. It will be installed in more than a half billion systems this year alone. Like DB2 and Oracle, SQLite is relational fruit of the tree planted by Ted Codd. It’s software as a component. It’s open source. It’s rock solid. And it’s turning computing on its head.
SQLite has become a very powerful force in application software because it is built into the world’s most widely used systems and applications. It is in Android and iOS and Blackberry software, so it’s in most smartphones. (It is not in Microsoft uses its alternative, SQL Server Compact.) If you use an Android, Blackberry, or iOS smartphone or tablet, you use SQLite every time you look up a phone number or email address in your contacts app (or when you receive a call or message and your phone or tablet tells you about the sender). The same DBMS manages your mobile device apps and just about every other feature that involves storing, retrieving, sorting, or searching lists of information records.
SQLite is built into the Mozilla Firefox web browser and Thunderbird email client, where it manages quite a few kinds of data–cookies, passwords, form data, browsing history, and more. The Mozilla programs provide transparency, and users can install SQLite Manager, an add-on that provides explicit access to the data stored by their browser or email client. (In addition to the add-on for Firefox, there’s one for Thunderbird, too.) The add-on also lets users build and use SQLite databases of any kind for any purpose. In fact, a Mozilla program with this add-on provides the best and perhaps the most widely used basic practical introduction to SQLite (and the SQL database language) available. Plus, it’s free! Google, which is big sponsor of Mozilla packages as well as the source of Android is one of the powerful computer industry organizations behind the rise of SQLite, although other companies more directly support SQLite code development. Ironically, while Google’s own browser, Chrome, uses SQLite, there isn’t yet an add-on that gives Chrome the transparency and functionality available in Firefox.
Apple‘s Safari browser uses SQLite, too, as do other Webkit-based browsers, and the DBMS shows up in Mac OS X. SQLite runs on other operating systems in the Unix constellation, including various Unixes and Linux. It is also available for Windows, Amiga, Symbian and, if you want a lightweight DBMS for your heavyweight mainframe the source code can probably be compiled inside z/OS using its Unix services features, although running it in zLinux is probably an easier way to teach that old mainframe dog some smartphone tricks. I don’t know of a version for IBM i but any Power Systems box support SQLite in a partition running AIX or Linux, and you could probably run it inside the PASE AIX runtime environment, too, that is part of IBM i and earlier OS/400 derivatives.
SQLite is a key component of VOIP powerhouse Skype’s client, and Microsoft seems to be happy leaving it in place. Adobe uses SQLite in a number of products.
SQLite is tiny. Even when implemented with all its features, it takes up only about a third of a megabyte in 32-bit environments and a little more space in 64-bit settings. It’s written in C and as it is open source you are welcome to look at its innards and, if you wish, try to make it better. It’s already pretty good. It’s not only ACID-compliant, it’s just plain hard to break. Smartphones with live applications using SQLite crap out millions of times every day (for a zillion reasons, but not because of SQLite) and you just don’t hear about data getting lost as the phone is cursed at and rebooted.
That’s not too shabby for a job that was initially done and is still guided by a fellow who reportedly says he would have been a college professor if he were better at what he studied. Dwayne Richard Hipp, who goes by Richard, began working on the software in 2000, while at General Dynamics, using gdbm (GNU Database Manager) as its foundation. SQLite matured and ultimately caught on in the open source crowd, and Hipp matured, too, forming a company that was ultimately renamed Hwaci as its ownership was turned over to the W after the H, Ginger Wyrick, Hipp’s muse and musical spouse.
If you think Hipp is unusual, well, perhaps he is, but maybe not much more so than some of the other seminal figures in the database culture. The RDBMS idea was conceived by Edgar F. (Ted) Codd. Codd, who worked for IBM beginning in the 1960s, was a British mathematician who thought about how to organize large data sets so they could be efficiently managed by machines. In 1970 he boiled his ideas down into a paper, called A Relational Model of Data for Large Shared Data Banks, published inside IBM and then in an academic journal.
At the time, IBM was making a ton of money with its flatfile IMS, a DBMS that remains in wide use. IBM initially decided to stick with its winner, but Codd kept yapping about what he believed was a better kind of DBMS, including to some enterprise customers. IBM’s initial solution was to keep Codd working as a researcher but get some of its other developers to put some of Codd’s ideas inside System R, a DBMS that was part of the ill-fated Future Systems project. (Some of the IBM’s FS work survived the demise of the project itself, emerging in the System/38.)
IBM’s botched up initial implementation of Codd’s concept was a not-really-relational language called Sequel. Still, Sequel and Codd’s published work and his talks managed to catch the attention of another character, Larry Ellison. Codd inspired Ellison (and colleagues) to form a company at first called Relational Software and to create a DBMS named Oracle for the US government. Ellison managed to make Oracle the most successful DBMS in the minicomputer world and, later, the whole world, renaming the software company Oracle along the way. That was before recent developments led to SQLite managing even more data, in aggregate, than Oracle will ever store. (SQLite garners much less revenue than Oracle. Oracle’s DBMS generates more money than even IBM’s flagship DB2.)
In any event, Codd, Hipp, and Ellison are, as the Unicode set says, special characters. By comparison, eccentric corporate database gurus are just run of the mill oddballs, people who, their programmers often allege, might have had one too many joins. The inspired efforts of the DBMS heroes have, somehow, changed the way developers and users of computers work . . . and the impact of their inventions is only starting to be understood.
Nowadays a growing number of IT folk have learned to appreciate SQLite and the way it has changes software technology. The availability of a small, sturdy, economical, efficient, and versatile RDBMS component that can run in memory or in conjunction with external storage is phenomenally disruptive.
The opportunity to shift extraordinary DBMS power to clients is a big part of what makes smartphones, smart TVs, and the Internet of things that themselves compute so delightful and impressive. When you tap on that Five Guys phone app and ask it to retrieve your favorite lunch selection, give you a choice of nearby burger outlets, and then place your order so it will be available just at the moment you show up, you can thank SQLite for making it possible for you to get lunch, that ketchup stain on your favorite shirt, and a swift modest hit on your charge card account.
No business that wants to grow is sitting still. Five Guys is suffering big time from competition by imitation. How many pizza delivery companies that want to make ordering from your new talking-and-listening (I have trouble bringing myself to say “smart”) TV set as much fun as scoffing down that pie? All of them.
There are limits, perhaps. It might be a while before Grainger gets its list of fanbelts for fanboys into your iPhone, but maybe not. Grainger’s computing crew might not be as traditional as its vast inventory of industrial items.
And then there’s the economic angle, which makes the client-side DBMS all the more compelling: One of these days the computer geeks at Fresh Direct, New York’s sensational Internet grocer, are going to realize that they can move some of their DBMS work to user’s PC browsers and mobile apps for a quarter of the cost of adding the server side MIPS it takes to do everything centrally. Even if they can only move half the computing for half their customers to the user end of the downlink, they’re going to come out ahead in a business that lives (or dies) on razor thin margins. The geeks who get this to work are gonna love their bonuses.
Maybe Salesforce.com and its ilk think the whole future is the cloud. For some services and some end users, it might be. But not if storage and processing in client devices is, for Salesforce as it is for everyone else, just about free, rapidly responsive, and easily tailored to the preferences of each user. If Salesforce doesn’t catch on, some rival that will surely eat its lunch (possibly at Five Guys).
Putting a database inside a PC’s browser or a mobile gadget is no longer a big deal. Even the smartphones that are at the bottom of the class pack gigabytes of storage space. The storage might have originally been put there for music or movies, but that same 16 GB can securely hold–and, more importantly, work with–large collections of data such as users’ shopping preferences, personal and business contact information, customer histories, clothing sizes, medical records including allergy and contraindication data, what-have-you.
Copies of that end user info can also be stored in the cloud, providing backup (and some insurance against the consequences or dropping a phone into a puddle), but doing processing in the cloud that can be done on the client while moving vastly more data from cloud to user and back, well, that might be a pretty wasteful choice. You say you want those X-rays of your broken leg stored where any doctor can be given access? Fine. But maybe you also want that same library (and similar data for the rest of your family) stashed in an encrypted folder on the smartphone you carry on your ski holiday.
If you think a teeny software component like SQLite is far too wimpy for this, check the facts and think again. SQLite can easily manage databases that are larger than any you are likely to need for personal use, sets of tables that might well be bigger than anything you can stash in your phone or tablet (or maybe even on your PC). The current release of SQLite can work with a database up to 128 TB in size. A row can have 32,768 columns, more or less the same as DB2. A blob of data can be up to 2GB. So while the lite part of SQLite might be light compared to, say, Oracle, DB2, or SQL Server, it’s heavy duty compared to simple PC packages like Microsoft Access. Moreover, on a mobile client, SQLite will be working entirely in DRAM and SSD, making your Android or iPad a better performer than a glasshouse, hard-drive based database.
I routinely look for info in my email client, which happens to use SQLite and which has in its inbox between 5,000 and 10,000 messages that all told take up about a half gigabyte of disk space. I can find a message from a particular client with specific subject or text body words far faster than I can get to cloudy records about that client’s payments history. Same goes for the emails I save that detail my travel plans, when compared to similar records kept by airlines or hotel chains. I haven’t put all my email archives in my smartphone yet, but that’s bound to happen one of these days, probably soon after I spring for the price of a newer phone and find a reliable, user friendly encryption app that I like.
Finally, there’s another valuable bonus provided by SQLite running locally: discretion. With a DBMS and several gigs of file space on my phone, I can peruse my personal data in private. I can use that information as I please without telling all to that evil yenta of a robot lurking in the cloud, Siri the seriously snoopy Sirian.