MySQL
Decided to move my project to MySQL because it seems to be more of a standard compared with Postgres. This is a pity because I like Postgres a lot, but you have to go with the flow and if I’m going to work on some non-Oracle skills I should at least go with the market leader.
So, got it installed on XP (which was a breeze). Read the manual through a couple of times (you can download a compressed HTM help file). Persuaded PHP to talk to it, which mainly consisted of placing the MySQL bin directory in the path and rebooting.
Worked out that I needed the INNODB table types so that foreign keys would work (you can default this using the excellent configuration utlility). Also discovered that (assume table jim exists for the sake of this discussion):
create table fred
( x varchar(2) references jim(x) ) ;
Does not work:- it accepts the syntax but does not create the foreign key. I had to hack my SQL generator to get rid of the Oracle shorthand and to not use domains (as I was using with Postgres) but translate them into raw types. Now it’s:
create table fred
( x varchar(2) ) ;
alter table fred add constraint fred_jim_fk (x) references jim(x) ;
Which should work in Oracle and Postgres anyway. I’ve decided to eschew using domains (although my software lets you define them) so that it can be portable around many SQL engines. Need to also drop the Oracle-ism varchar2, as it is now a synonym for varchar anyway. I believe that when Oracle first created the type it wasn’t done right so they had another go; hence the 2 on the end for compatibility reasons and then it stuck. I wonder how many petabytes of storage are taken up with all of those 2’s? Someone pointed out to me that a single-character field should be char(1), because you’re storing extra space for the length, which is pointless when you only have one character …
All in all fairly painless.
If you’re an Oracle supporter, read this:
I found it interesting that the Census Bureau had an Oracle site licence and chose not to use it. One of my buddies uses MySQL a lot and is looking at using the text extensions to replace Oracle’s context (or whatever they are calling it now) becuase Oracle want loads of cash and context is a pain in the proverbial. MySQL is also a bit quicker for complex queries and doesn’t have so many common(ish) words that are used as keywords that you have to pre-process into a form that isn’t interpreted as one, how lovely and user-friendly- those context guys really put some thought into it, didn’t they? Apparently every term added to an Oracle query increases the time taken for the query, where MySQL has what seems to be a fairly fixed cost. Don’t quote me on this I haven’t validated this for myself (but it doesn’t surprise me). Don’t say the words “memory leak” or “restart your database every morning” either. (I think this is 8i – maybe fixed?)
I’m quite curious about how their spatial database stacks up against Oracle’s but don’t have the time to look into it. The beauty of it is, of course, that you could add things in if you wanted to, with it being open source. Hmm … who the has the time, though?
Every database vendor other than Oracle allow you to define auto increment columns and then programatically get back the last autoincrement created in that session. Oracle sequence thing is a poor cludge. I suspect you could simulate the autoincrement with a packaged procedure (caching the last sequence number and messing with the insert statements or some horrible trigger), but why don’t Oracle do this?
MySQL doesn’t let you create views with subqueries in them. Not sure why, I’d have thought it fairly easy to implement. Views are just strings you run together with whatever you’re joining them to; or am I being naive?
MySQL 5 (which isn’t stable yet according to the website) has procedures and triggers. Postgres already has them. I just wish there was a standard for these languages so I don’t have to learn them all. I think MySQL’s look Postgresy but don’t want to do in-database stuff at the moment because it isn’t portable so don’t care about this. But I do like triggers for policing complex relationships and auditing.
Python
Sort of keeping a watching brief here. Was going to look at using Ruby as my scripting language of choice but it’s not mainstream enough to want on a CV. I like Python’s clean syntax. I like Ruby’s power and the syntax is OK. They have a lot in common as well (PHP has too). I suspect that when the Open Source guys see a good idea in another language they nick it, starting with variants of a lot of the good things in PERL. On first inspection I think Python’s object model is poor, but maybe what I’m seeing is flexibility. PHP 5 has a very strong one, as does Ruby (everything is an object in Ruby). It does have lambda functions (see http://diveintopython.org/power_of_introspection/lambda_functions.html). Lambda functions are very useful and give you a lot less clutter and overhead, if you use them carefully. Sharp tools, sharp tools. Love ‘em.
Professional Development
Have decided to spend 3 months on my PHP project and then go back to the Java Certification route. Java may be a boring thing to work with but at least it gives me options. Options are where it’s at. I think that Python or PHP or Ruby are probably a lot faster than Java in terms of bangs per developer buck and getting the job done with less fuss (I haven’t the space here to critique Java) but Java’s what the IT manager types want because it’s safe. Probably not as safe as .Net, but pretty safe.
Sheds and bookcases
Finally got my bike shed built in the back yard. Annoyingly it will only take 3 bikes and we have 4. Ho hum. I bought a new bookcase on Friday to replace one that fell to pieces one day. Guess what? I thought I’d got a wide but low one and in fact got a narrow low one that is about a quarter of the capacity I needed. Ah well back to IKEA and get another. Ho hum …
Onward … blessings all. I need some sleep.