michaelsilver.us Software, programming and whatever.

February 21, 2007

No Easy Database Choices

Filed under: Database,Programming,Ruby,Ruby on Rails,Web Development — Michael Silver @ 12:49 am

As I try to get NutritionFacts up and running on the web with Ruby on Rails I need to select a database to use. Obviously, in the open source realm, MySQL is the forerunner or at least the first one to look at since it’s use is so widespread.

I have a few criteria that any database must meet:

  1. Transactions
  2. Full-Text Search
  3. Cross-platform (my server runs FreeBSD, I run WindowsXP)
  4. Have bindings for Rails
  5. Open source
  6. Server Process

In taking a closer look, MySQL is not perfect, in fact it’s missing some critical features. If you use the MyISAM storage (MySQL offers numerous methods of storing data, each offering different features), you get full text searching, but no transactions. I have the need for transactions since the data users may enter span numerous tables. If you use the InnoDB storage engine you get transactions (and row locking instead of table locking), but no full text search.

Well perhaps another database would have both transactions and full text searching. I moved my search to SQLite which does have both, but it doesn’t have a server process to manage access, which could mean problems on a busy site. Not good.

Next in line is PostgreSQL. Very impressive feature set but full test search is through an addon called TSearch2.

The standard instructions for TSearch2 are rather complex, but thanks to the Google and the Internets (aka the series of tubes), there are some good guides for setting it up. It still seems like a solution prone to break though, since it requires a trigger and an additional field in the tables to be indexed. I also don’t know about the quality of the tools to access and manage PostgreSQL, although phppgadmin looks decent enough.

Just to rule out an other potential canidates I also looked at Firebird which has transactions
and is cross-platform and has rails bindings. Unfortunately, no full text search.

Next in line: Ingres. I took a quick look and frankly I don’t have the energy to explore it. It doesn’t seem to have as much support in the open source community and I couldn’t find any documentation on full text searches, so I presume it doesn’t offer it.

I am fresh out of databases to explore. Until I can choose a database (and I am leaning towards PostgreSQL) I am going to useFerret and the acts_as_ferret plugin for Rails to preform the full text searches. (Tutorial) While Ferret is not a bad solution, it does appear to be slightly slower than the MySQL full text searching and the potential exists for the index to become out of sync with the database since it is not stored (or even related) to the database. Thankfully, with Rails I can use this solution for now and when I find a better one, switching will be trivial.


Database Full Text Search Transactions Server Process
MySQL (MyISAM) Yes No Yes
MySQL (InnoDB) No Yes Yes
SQLite Yes Yes No
PostgreSQL Plugin Yes Yes
Firebird No Yes Yes
Ingres No Yes Yes

February 17, 2007

Web Development is Fun?!?

Filed under: Programming,Ruby,Ruby on Rails,Web Development — Michael Silver @ 2:25 am

While most of my expertise has been in developing desktop applications, I was recently moved to our web team. I have written a web application before, an ISAPI dll at that, and as expected, web development has only improved marginally since my last forray.

At work we use C# and VB.NET with ASP.NET. While I love the C# syntax and Visual Studio is impressive, web development is still tedious. Even simple sites can takes days or weeks to implement. While some of this is due to ignorance, some is from lack of innovation in the web development realm.

I had heard of and tinkered with PHP, Perl, Python and the essentially defunct ASP and none impressed me for web development. They are behind ASP.NET technologically and usually combine business logic right into the web page, since language limitations made it the easiest route. Yuk!

Another option, Java Server Pages, are far too complex.

A few friends at work keep me up to date on various programming languages and language features they encounter. Such friends are always great to hang around since they have a passion for innovation, whether the innovation is from them or someone else. Sure enough one introduced me to Ruby on Rails, a framework for developing web sites based on the Ruby programming language.

I had heard of Ruby previously, but I didn’t really have much desire to check out another scripting language, that is until Rails came along.

I don’t know all the details, but Ruby supposedly offers features that make the creation of Rails possible. While similar frameworks exist for PHP, Python, Java and C#, they are all hamstrung, although some only to a small degree, by language limitations.

With all the press Rails is getting, I had no choice but to take a closer look. Next, I needed a project. Viola Nutrition Facts.

Nutrition Facts is an application I wrote for windows that allows quick searching of a food database to determine carb or fat counts, etc. This would be a perfect project to move to the web.

So I am off and running developing my first web app with Rails. While I am still in the early stages, I have noticed something. Rails makes programming fun. It eases the tedious programming tasks associated with the web. WOW! Web development can be fun!

As I progress through my project, I will create new articles to detail the problems and successes I encounter.

Happy Web Programming

Powered by WordPress