wonkablog

There are some things in life that, once you get used to them … there’s just no going back. Things like underwear, Malt-O-Meal, Neverwinter Nights, and views.

At work we use databases much, much more than I ever have anywhere else. Because of that, I’ve been forced to stretch and learn more ways to do things efficiently because if things are going slow, we are losing $$$. All that’s nice and good, but the part I’m enjoying as a developer is the convenience of that views, stored procedures and user-defined functions give me.

For instance, I’ll use a recent example. We have three tables that we pull data from a lot, and I’m constantly doing INNER JOIN foo ON bar = poop across a lot of pages. Well, a view lets me take the most critical data of those three tables and lump them all into one subset. So now, I can just do ‘SELECT * FROM view-la-la WHERE foo-poo-pah’ and be done with it.

I really have no idea how I lasted so long without these great features. It’s probably because I was stuck using that other open-source database that has said for years that those features weren’t important. Well, that and because sometimes I won’t try something new if I’m familiar with what I have. Until they stop selling my favorite flavor of Cream of Wheat at the store, and then I *have* to try something different. Mmm… Malt-O-Mealie.

I’ve spent almost all day trying to get these free versions of large databases working. Well, that, and watching a good amount of Justice League. So far, I’ve yet to feel like I’ve made any good progress. Although that one battle with Lord Hades was pretty darn cool.

These new cartoons are a lot grittier than even Batman: The Animated Series was. The animation isn’t as sharp, though, but it is detailed in other places. It’s a trade-off, I guess.

Anyway, here’s my impressions so far:

Oracle XE

• Easy to install
• A pain in the arse to connect, what the crap is TNS and SID variables I need to set? I’m really lost.
• The web interface is really slick.
• The instant client installed without any issues on Gentoo (on both 32 and 64 bit installs, I dual boot).

SQL Server 2005 Express Edition

• Just as easy to install
• No cool frontends, no nothing. It does come with a separate SQL script thing you can run in DOS (that’s odd).
• I can’t figure out for the life of me how to connect with PHP, but I don’t think it’s going to be easy or possible, considering its so new. ODBC *might* work.
• I’m not even sure if its accepting remote connections yet. I can’t even get an ODBC connection created.
• I would be using the 2000 version, but I didn’t find it til much later, and I’ve already got everything installed.
• I would have found it much earlier if Microsoft didn’t make it so freaking absolutely gouge-my-eyes-out incredibly hard to find. Naming the databases 2000 and 2005 doesn’t help my search queries too much. Not that sql or server are really original either. Come up with some original names, guys, like … “Relational Asinity” or something.

Firebird

• I can’t even get the thing started or figure out how to create a database.
• I tried the web admin (ibwebadmin), and while it looks like a really cool app, see above.
• Couldn’t find any cool foss guis, so I gave up after searching for just a little bit to try the other ones.

IBM DB2

• Just barely finished installing it, I’m feeling optimistic about this one.
• Not too excited about installing RPMs on my Gentoo box just to get the PHP client working. Actually, I wouldn’t mind if there was an ebuild.

Granted, I’m reading very little documentation while I do this, other than the PHP website, and going as far as this will take me intuitively. I’ve spent most of my time on Oracle, and their website is really nice, and has a section just for PHP. I still can’t get any connections working though.

One thing’s for sure, it makes me love PostgreSQL even more than ever. And I used to think it was a pain to setup. Holy crap, its a cakewalk compared to these guys.

I think it’s time for some more cartoons. Green Lantern is definately the man. I think he’s tougher than Superman.

I’m sure I’m going to get into trouble by announcing something I *plan* on releasing, because then I might never get around to it … but oh well, I felt like mentioning it.

I started working tonight on a SQL Server release of the lds-scriptures package. This one is actually going to have foreign keys, and maybe some views — some of the big things that will be in the next point release for the other databases too.

Someday I’ll have to get an eval version of Oracle and DB2 and maybe port it to those just for fun.

This one isn’t a rant, just another head-scratching-that’s-an-odd-thing-to-do kind of post.

One of the main scripts was running slow tonight that gets hit on a regular basis, and we couldn’t figure out why.

So, we do what we do when we aren’t sure what to do — we reindex the table. That’s the first general fix we like to do. Turns out, that fixed it in this case, as well.

What had changed recently you might ask (and well so)? We added a column to one of the database tables.

I guess that was too much of a strain for SQL Server.

Just for a background .. anyone who has talked to me for more than 30 seconds about databases knows that I hate Microsoft SQL Server with a passion. The real bonus is that it’s not just an unfounded passion! I have proof of its inadequacy, which you’ll see in this special 5,789-part story.

In today’s adventure our hero ran into a small problem — he wanted to select year-month-day from the database in the format of (2006-01-17). Easy, you would think, until you’ve actually worked with the database first hand.

First, I tried selecting them all individuall and concatenating them together .. which worked, except that datepart() won’t pad the zeroes (it would return 2006-1-17).

Next, I decided to write my own UDF (User Defined Function for the unwashed masses), which would set the variables, but I ran into another problem — you can’t run getdate() within a UDF. That’s weird (translation: That’s about the dumbest thing I’ve seen this databse do yet).

Actually, the last problem turned out for a small bonus, because with my new UDF I could just pass it in as a variable anyway. Sure it complicates the function a little bit, but it will work out in the end.

Speaking of which, here is the final SQL for the function:

CREATE FUNCTION dateYMD(@getdate DATETIME) RETURNS VARCHAR(255) AS
BEGIN
DECLARE @year CHAR(4);
DECLARE @month VARCHAR(2);
DECLARE @day VARCHAR(2);
DECLARE @date VARCHAR(255);

SET @year = YEAR(@getdate);
SET @month = MONTH(@getdate);
SET @day = DAY(@getdate);

IF LEN(@day) = 1 BEGIN SET @day = ‘0′ + @day END
IF LEN(@month) = 1 BEGIN SET @month = ‘0′ + @month END

SET @date = @year + ‘-’ + @month + ‘-’ + @day;

RETURN @date;

END

Then, the final blow. After about getting halfway through writing this one, I find that there’s an undcoumented feature (I guess those Microsoft developers enjoy Easter Eggs just as much as I do) to do the same thing I was doing:

SELECT CONVERT(VARCHAR, GETDATE(), 23);

SQL Server: 1, Developer: 0