From the category archives:

Database technology

Alternatives to MySQL

by Pearlbear on March 14, 2011

For those of us that depend on MySQL everyday, the buyout of Sun (which had bought MySQL) by Oracle did not bode well. A decidedly biased survey by the folks behind PostgreSQL suggests that many people worry about the health of MySQL in Oracle’s hands. I’ve mentioned this before, and I do think the conventional wisdom is that open source software (which includes OpenOffice.org, MySQL and Java) will not flourish at Oracle. It makes sense – Oracle has never had a culture of fostering open source software, and it seems unlikely to obtain one.

So what does someone do who builds their houses right on top of the LAMP stack (M standing for MySQL)?

For most folks, especially if they build on shared hosting infrastructures, this just isn’t an issue. They depend upon their hosting providers, for whom it may or may not be an issue – but they won’t have to think about it. For those folks in a position to choose which database software to use, (for example, you use VPS systems like Amazon, Slicehost, Linode, etc.,) then I think there are two pretty good options:

  • Go with MariaDB, which is basically a drop-in replacement for MySQL (and conveniently starts with an “M”.)
  • Switch to PostgreSQL.

MariaDB is a branch of MySQL that came about because of the uncertainty relating to Oracle’s ownership of MySQL. From the website:

In most respects MariaDB will work exactly as MySQL: all commands, interfaces, libraries and APIs that exist in MySQL also exist in MariaDB. There is no need to convert databases to switch to MariaDB. MariaDB is a true drop in replacement of MySQL! Additionally, MariaDB has a lot of nice new featuresthat you can take advantage of.

The problem is that the major Linux distributions (Ubuntu, Debian, RedHat) don’t yet have MariaDB in their repositories, so it will be a while before MariaDB is an easy apt-get or yum away from installation (there are some independent repositories and builds.)

PostgreSQL is a different beast entirely. It’s been an also-ran in the open source database race, and I was, for many years, quite faithful to it. It’s a very solid database, and it was ACID compliant before MySQL was. It’s major weakness (and why the LAMP stack is called that and not the LAPP stack) was that it was a fair bit slower than MySQL. But  that weakness has long been taken care of, but the damage was already done.

Many open source web database systems can use PostgreSQL instead of MySQL at this point. But PostgreSQL doesn’t have the same large user base, and doesn’t have many of the same web-based and desktop tools that MySQL does. There are differences in the SQL commands and such, and the command-line interface looks different. There is also a big difference in how Auto-numbered fields get handled, but that’s not really an issue that folks who don’t dive into deep database and code need to deal with.

So which to go with? It probably makes sense to wait a bit, first for MariaDB to make it into mainstream repositories, etc., and also to see what the fate of MySQL is. And checking out PostgreSQL is always a good option, it’s a very good database system, and the likely flight from MySQL might do the project some good.

Tagged as: , , , ,

{ 3 comments }

The easier it looks, the more expensive it will be (or, how to avoid clusterfrack projects)

by Pearlbear on March 23, 2010

As most of you know, I’m a very long time veteran of web application building. I’ve been involved in web application development basically since they started – when a cgi-bin folder with some perl scripts to process simple forms was the norm. Until just a few years ago, there was very little sophistication about the user experience in web applications – what mattered most was functionality. and to make sure there weren’t too many errors when users did unexpected things.

I’ve considered myself pretty successful at both helping clients navigate the tough waters of web development projects, as well as accomplishing web projects for them. Recently, though, I had two projects that ended up, for wont of a better term, clusterfracks. And I’ve spent a lot of time lately trying to figure out what lessons I need to learn from those projects – what can I take away from them so I don’t make the same mistakes again. They were both custom web applications, both projects that I was a strategic, rather than engineering, partner on. Both projects were attempting to accomplish pretty sophisticated database functionality (such as case management). Functionality I knew how to get done, because I’d accomplished it before – so I had a very good feeling for what kind of code it would take to accomplish the task (and, ergo cost and time.) But what I hadn’t taken into consideration is how slick, AJAXy, easy to navigate, and easy to understand user interfaces people have gotten used to in the last few years. And, frankly, have come to expect. And how unwilling people are to sacrifice that for raw functionality.

I did a lot of self-examination: where did I go wrong? What could I have done differently? Was it the client? The developers? Me? I realized a fairly simple truth. It was all three.  In reality, I should have looked at the budgets of those projects, and looked at the clients straight in the eye and said, “double, or triple the budget at least, or don’t do the project.” And walked away if they insisted. The vendors should have bid triple what they did, and had more user interface expertise on board. The clients should not have expected to get slick 2009 functionality for a mid 5-figure budget.

The easier a user interface is to use, the more money and time it took to create. It’s that simple. What most nonprofit decision makers don’t completely realize is that the interfaces they work in every day when they shop,  or tweet and facebook, or use other online tools, are the product of millions and millions of dollars of venture capital, or, in some cases, hundreds of thousands of person hours of work in open source projects (or some combination of both.) Ground-up custom applications, even when written in great frameworks like Ruby on Rails or CakePHP, which save all sorts of development time, just are not going to have the user experience people are getting more and more used to without very serious investment of time and expertise. In addition, most small development shops don’t have the user interface expertise on hand to accomplish that task, even with a hefty budget.

So the lessons:

1) If you are embarking on a custom development project (such as a case management, for example) exhaust every possible option of using and customizing/modifying existing tools (Salesforce, CiviCRM, SugarCRM, other open source tools) before you begin to consider custom development from scratch.

2) If you have a budget of less than $100,000, go back, and stay, at step 1. I know this is high, but I’m serious. Obviously, simpler projects won’t need a budget of this sort. But simpler projects generally don’t need custom databases.

3) If you’ve got the cash to spend, and have exhausted all other options, when choosing a vendor, make sure the vendor you choose has UE expertise on hand. Look at other custom database work they’ve done. Dig in. Make sure it has the ease of user experience that you are expecting.

4) Remember the mantra: the easier it is to use, the more expensive it is to build.

Tagged as: , , ,

{ 3 comments }

Exciting changes afoot…

by Pearlbear on April 1, 2009

I have some exciting news. For the last few months, I have been working on a new collaboration called OpenIssue, which is a growing, diverse, self-reflective and constantly-learning team. We are focused on delivering quality web technology solutions to nonprofit organizations and social enterprises.

As you know, I have built a long-time expertise in open source software and web applications, particularly Content Management Systems (CMS) and online database systems, including CRM. Thomas Groden, my new business partner, has expertise in Software-as-a-Service Constituent Relationship Management Systems (CRM), as well as much more broad expertise in technology infrastructure.

All technology implementors have to choose their tools (unless they run a very large shop) and we have decided to focus on implementation of both Salesforce.com and CiviCRM as CRMs, and Drupal as a CMS. We are keenly interested in building on our expertise to integrate these open platforms in really rich ways, to allow organizations to create great online applications.

I’m excited to be a part of a team – I’ve been a soloist for a while, and it’s nice to build collaborations, and work together with people with shared ideals on larger projects than I’d be able to take on alone. And I’m really excited by the set of technologies we’re working on, and the kinds of applications we’ll be building with these technologies.

And you can follow us on twitter.

Tagged as: , , , , , , ,

{ 1 comment }

Platforms break open, part II

by Pearlbear on October 18, 2007

The dust is settling. I looked over Allan Benamer’s post on the Convio and Kintera initiatives, I looked harder at the Convio Open and Kintera Connect docs, and I also had a chat with some Kintera folk. I have a few comments.

Allan is right – the Kintera API is more comprehensive, and provides for more flexibility than the Convio API. Of course, the API was only one part of Convio’s initiative, so I do still think they come out ahead, a bit. But it may well be that for more complex integrations, the Kintera API will provide more power.

REST vs SOAP: Kintera seems to have chosen the “more power, harder to code” choice. I could argue it either way.

Methinks vendors in this space still just don’t grok, really, what “open” means. While I appreciate that one can, theoretically (I have yet to test it) easily become a “partner” with either company – but that doesn’t quite count as open. Allan hit the nail on the head when he said:

Again, this is a lesson in Web 2.0 transparency both for the sector and the vendors who serve it. Control? Let it go. I really mean that. From both a business point of view and from the point of view of how our sector should work to heighten transparency in society at large, there’s no reason to limit the ability of coders to learn about and discuss the API at hand. And the big guys have already done this work, check out the way Google and Amazon distribute their APIs. Those shine as industry-standard examples of how open APIs need to be distributed.

He’s right. Open it up, let anyone bang on test data to try things out, and you never know what might happen. The drive toward open everything is pretty inexorable, and the pressure is only going to get greater.

Kintera Connect

by Michelle Murrain on August 15, 2007

I listened in on the call with Kintera folks about their new platform, called Connect. I was mostly curious about how open this platform will be, and what the future holds for them. I have become fascinated by the ways the CRM/Fundraising space is changing so rapidly.

Basically, Kintera is taking directly from Salesforce‘s playbook. There are two initiatives that they have that I’ll talk most about, their “Connect” initiative, and their data warehousing initiative. These are, for pretty obvious reasons, the most interesting to me personally. They will also be doing some serious UI overhauls, and upgrading their CMS. They also are opening a new data center, as well as bringing Akamai technology into the mix.

The Connect platform is a set of APIs, starting with the contact and payment sets of entities, that will allow access (via SOAP 1.1) to the data in the Kintera platform. Basically, third parties will be able to build applications which will allow two-way communication into the platform. The APIs will be without cost.

The data warehouse initiative is to allow their customers access to large amounts of data for reporting and data mining. It seems like it will start out with a local query system, then will be opened up to allow third party development of data analysis tools. That part looks very interesting.

A couple of annoyances: the documentation for the APIs aren’t up yet, and the sample code they are going to publish is in C# and Java!  Now I’m sure that there are a lot of large Kintera customers that might be implementing other applications that will be written in C# and Java, but it seems to me that this is, in fact a pretty big red flag that they really don’t have a feeling for the technology that the sector is using. Code published in PHP and Python would probably get a lot more people up to speed and interested in building stuff that will integrate with the things that a lot of nonprofits really use. I mean really, how many nonprofits have stuff written in Java? Small minority, I’d bet. (I guess the C# would be useful for the Windows crowd.)

On the whole, though, I applaud them for seeing the light, and opening up their platform. It will be interesting to see where this leads them.

{ 1 comment }

Open Source Database solutions part I

by Pearlbear on January 1, 2007

I’m throwing up my hands. Y’all will just have to live with overlapping series. I have too many ideas be sequential. I promise (!) more on Open Standards and Benkler (actually, Benkler is up next – I’ve got two chapters to review).

I’ve been using databases since I was a grad student in the 80s, and I’ve been designing and developing database-driven applications for the web since 1995. I’ve been using varied Unix-based databases since then (as well as others including Access and Filemaker Pro), and most have been open source.

Although I’ve been using databases for a while, I’ve decided that I’m going to focus specifically on open source databases for the next while, and, in particular, the different kinds of open source solutions that are possible for desktop database systems, or systems that might be server-based, but need a desktop front end. I’m particularly interested in the open source technologies that are coming down the pike that might bump Access from it’s perch as general-purpose nonprofit desktop database king, and that can provide nonprofits with flexible, robust data management solutions.

So here is my current survey of the landscape. I’ll be working a lot with Open Office, and hope to design some screencasts using Open Office Base sometime in the next few months. I’m starting this series off with just a list of the server-based DBMS. I’ll be talking next about desktop DB options (which mostly use these as backends,) and then last about ways to put this all together in an all open-source landscape.

Server-based DBMS (DataBase Management Systems)

  • MySQL – MySQL is, I think, the most popular, and best known open source DBMS. It is cross-platform. It is the most popular because historically, it has been the fastest of the open source DBMS, but it has always lagged behind in terms of ACID compliance and other features. You can access a MySQL database via many many different drivers that people have written for just about any programming language. It is also possible to access MySQL databases via ODBC (Open DataBase Connectivity) or JDBC (Java DataBase Connectivity)
  • PostgreSQL – PostgreSQL has always been my favorite. I’ve been using it since it was called Postgres95 – before version 6. (Wikipedia has a great entry on PostgreSQL, including some history). PostgreSQL has always been ahead of MySQL in terms of ACID compliance and robustness, and still is. It lagged behind MySQL for years because of speed issues (it was much slower,) but that has changed with the newest versions, such that in fact PostgreSQL is faster and more scalable than MySQL. PostgreSQL is also cross-platform, with binaries available for Linux and Win32 from Postgresql.org, and Mac OS via Darwin Ports. A PostgreSQL database can, like MySQL, be accessed via APIs written for just about all programming languages, JDBC, and ODBC (which I have quite a bit of experience with.)
  • Firebird – this is a newer kid on the block, sort of. It has a very long history, though, since it is based on Borland’s InterBase codebase. It’s doesn’t have nearly the user base, or the amount of available tools as the others, but InterBase is a pretty interesting product, with some good features (like a small footprint, server performance tuning, and a great rollback and recovery system.) It is also cross platform.
  • Apache Derby – a DBMS written entirely in Java. This project has a small footprint, and is designed to be easily embedded in other Java projects. It comes with a scripting language and interpreter, called ‘ij’ which is how you can interact with Derby on the command line. Also, of course, you can use JDBC is a way to access Derby. I’ll be doing a fair bit of experimentation with Derby (’cause I’m curious.)
  • SQLite – a small footprint C library that implements an ACID compliant DB engine. It has a command-line tool, and it is possible to use C/C++ and Tcl for database access. Unlike the others, that are released under varied open source licenses, the code for SQLite is public domain.
  • There are a few others (see list here,) but they are either research-focused (like Ingres,) developed very little, or have small user bases, and seem not relevant to nonprofit technology.

Nonprofit technology take home lesson: MySQL is certainly the leader – it’s most commonly thought of as the “M” in LAMP (Linux, Apache, MySQL, PHP/Perl/Python), which is a nptech web mainstay. I’d argue that PostgreSQL is a better choice, but for most nptech applications, it doesn’t matter – what matters is what your tech/consultant knows, and that’s much more likely to be MySQL. The others are most likely of interest to pretty small niche groups, for specific kinds of projects.

Technorati Tags: ,

{ 3 comments }

Web 2.0 and database technology

by Pearlbear on September 24, 2006

I’ve been beginning to think a lot about databases, and where they are going. I’ve been using databases now since grad school, and relational databases for the past 10 years or so. There have been two specific advances in Web 2.0 that might, in the end, change how we think about databases.

This is described well in a post on O’Reilly Radar, which describes what Google did when it was creating a new bug tracking system. They, of course, have the worlds most kick-ass full-text searching system (I’m not sure whether that’s Web 1.5 or 2.0.) So they combined that system, with specific kinds of tagging and metadata, to decrease the structure of the database of the bug tracking system – they were encouraging people to just put in lots of text in a free-form field.

It made me think – how many kinds of databases that we create and use could be simplified by adding tagging, and really good full-text searching? I already can imagine something like an event management system, or some kinds of content-rich applications that depended upon highly structured relational schema, that could use this kind of new idea. Come up with one good full-text and metadata search functionality (or use someone else’s) and trim down the time and energy both creating the schema, and entering in the data, at the same time as you enrich the content.

I kinda like it.