This was originally posted back in July.
So, I’m on vacation and finally have some time to work on my open source configuration manager project. I’m at the point where I have a bunch of backend modules finished and can start looking at how I’m going to store and work with the data.
My plans have changed quite a few times over the last couple years while I’ve been playing with the design. First it was going to be multi-database, then it was going to be just PostgreSQL. Then, when that looked a bit hard to me, I thought MS SQL Server express could be good. Now, I’m back to multi-database again.
MS SQL Server Express is free but it is severely limited in database size and power. This makes it a bad choice. Of course, you could buy a real copy of SQL Server, but that would defeat the point of providing a free software system. The fact that I’m writing this for Windows environments will probably already have some open source guys upset. But hey, I’ll worry about Linux when they get 50% market 🙂
So, first step was to install all the databases I would need. SQL Server, MySQL, and PostgreSQL. The most painful was SQL Server of course. First you install it, then patch it with a service pack, then apply hot fixes. Imagine telling a customer of your app to do all that.
Postgres and MySQL are quickly downloaded in their latest revisions and installed in under a minute each. Both run well under my Windows 2008 x64 environment.
Then, I started porting the previous SQL code I had written for SQL Server back to Postgres. That’s where the fun starts. Each database has its own unique SQL syntaxes and types. They all go after the ANSI 92 spec, but that only defines a limited set of keywords that must be understood.
Each database has its own set of functions, different definitions for data types, quirks on table types, etc.
For example, SQL Server allows varchar types to be anywhere from 1 to 8000 characters or “max”. MySQL allows 1 to 65535 bytes. Postgres allows for 1 to 1GB.
Designing an application to work in all environments will take some care to make sure the right types are used.
SQL Server makes it even harder. Whereas MySQL and Postgres allow me to store everything in UTF8 by default, Microsoft makes me use a special NVARCHAR and NTEXT data type if I want to store Unicode values. What a complete pain in the ass! And I have to be careful to prefix any strings with an “N” in front. Talk about stupid!
Procedural languages are where it gets real interesting. SQL Server and MySQL have procedures and functions. Postgres only has functions. But, it can return table results from functions so that is similar to SQL Server 2005. MySQL can’t do that though.
I’ve been writing code on SQL Server for 4 years now and I never knew how limited I really was in the TSQL language. After reviewing the structures of both MySQL and Postgres, I was amazed. Better looping, better conditional logic, many other capabilities. Did you know you can’t even do a simple IF THEN ELSEIF on SQL Server! Both open source databases allow that. MySQL even has a real CASE statement structure!
Anyway, I think I’m going to be learning a lot about these databases during this project.