I am very thrilled to hear that SUN is evaluating joining the SQL standard process. I feel that Peter would be the perfect candidate for this job too, given that he knows the standard inside out. My hope would be that this way the OSDB consortium (yes, I still believe) will get a liason to the SQL standards body. Also this way the much more down to earth real world approach of OSS databases to finding new syntax to add to SQL now has a chance to find its way into newer versions of the standard. It seems things are too late to ensure that SQL2008 will follow the popular LIMIT syntax, but in a way its already a huge leap forward that SQL2008 defines a way …

So I took my concerns over prepared statements to the #postgresql IRC channel on freenode. I pointed out that I think there should be a way to get server side handling of placeholders in statements but without the additional overhead of a second round trip or the drawbacks of overly generic query plans due to not being able to use the parameters in the planning stage. Some people have noted that this feature is available in MSSQL. It also seems to be available in PostgreSQL in the form of PQexecParams and its even exposed in ext/pgsql, though it's only used for sequence reading in PDO_PGSQL. It would be really cool if it could be used when emulating prepared statements (probably with a PostgreSQL PDO …

This is not related directly to MySQL, but alas I must rant. In this day and age I’m not sure why an application would require IE7 and ActiveX controls to run a ticketing system. If we’re in the technical world, as sysadmins or DBAs, which run Linux/Solaris/Unix on any good server in order to get work done, it only makes sense to use a unix based OS (osx,linux,solaris) as a workstation.

It’s easier to interface with servers, there are better terminal options (in which we live our daily lives), free options to just about everything that exists in Windows, and YET there are ticketing systems (RNT) that require IE7 and ActiveX controls - which require you to run WindowsXP. It doesn’t work on windows server 2003, it doesn’t work on Windows 2000 Pro.

So what are we left with? An impossible situation that requires an employee to run two OSes in order to get work done. Ridiculous! It’s a waste of time and resources. Not to …

So I took Roland's comment and tried to get it integrated into my code. For the tab management it worked well, but for portlet management it was a lot harder. Actually I only have a partial implementation finished. What's missing is the necessary logic to be able to move a portlet in the same tab from one column to another (there are 3 columns a portlet can be in for each tab). The thing that was most important to me was cleaning up the pruning operation. This took some trickery (aka hackery). I think its a better implementation but it does make me a bit nervous. Of course its all still very MySQL only.

Anyways so here goes the final query for moving a tab:

UPDATE user_tabs ut
    JOIN user_tabs ut2
        ON (ut.user_id = ut2.user_id
            AND ut2.id = :id
            AND ut2.pos != :pos
            AND ut.pos >= LEAST(ut2.pos, :pos)
            AND ut.pos …

So everybody and their dog hopefully knows about SQL injection attacks these days. Most people should have also heard someone telling them that using prepared statements is the magic super fix to all of these issues. People slightly more in the know will have read that prepared statements lead to all sorts of issues. Some of which can be fixed with hacks (or eventually at the source). Some of which can only be solved of the source also exist of course. Some others can only be fixed with certain assumptions (like using the first set of data for generating the …

On my current project my team had to develop a portlet interface. Users can load portlets and organize them in multiple tabs with 3 columns per tab. They can reorganize the order of their tabs and move portlets within a tab an also move them to new tabs. Portlets are always placed at the top left when they get added or moved to a tab. Furthermore portlets and tabs can be removed, though the last delete operation can always be undone. All of this essentially required me to devise a plan for how to manage ordered lists inside an RDBMS.

Note that while this was written for MySQL (it makes heavy use of MySQL session user variables), I am using the Oracle style named placeholder support that PDO emulates for MySQL. So do not get confused by ":foo" in the SQL statements. This is just like the "?" you should know if you ever used prepared statements with MySQL. Furthermore I am using pseudo code control logic around the SQL. I think it should be …

Do they exist? Do they make you money (or in the case of Firebird meaningfully extend your community)? Would you be unhappy if support would be dropped in PHP 5.3, in PHP 6.0? Do you have ressources to prevent this from happening, by taking ownership of the code in question? Are you interested in ensuring the availability of solid support in PDO? While I do not think support will be dropped in PHP 5.3 (well for FrontBaseSQL I have a hard time standing up in defense like I did for the other 3), there is a good chance this will happen in PHP 6.0. Without PDO support your users will be left more or less in the dust. So if you work for one of these vendors, please talk to who ever who can prevent this. If you know …

There are only two kinds of storage devices - those that have failed, and those that are about to fail. That's the view most datacenters have about the traditionally mechanical devices pejoratively referred to as "spinning rust." All disk drives fail, cheap drives fail faster.

If the average time to fail is five years, you and your laptop can make do with the occasional backup. But when an average enterprise has 100, or 1,000, or increasingly 10,000 or 100,000 individual disk drives, failure is a daily, if not hourly occurrence. Mechanical devices fail.

And with failure comes the potential for losing data - using commodity disks to save your boss $500,000 does her no good if she's fined $50,000,000 for violating data retention regulations. Stock transactions, medical images or feature length movies - take your pick, some data has to be perfect. Not a decimal point or pixel out of place.

That's exactly why, years …

This is going to be an unusual blog post, because I will continuously update it with features that MySQL still has on top of PostgreSQL, which is generally considered to be more feature rich. Some of these missing features can however hurt a lot. I am including MySQL 5.1 in here, since eventhough its not yet released as GA, more and more people have started to use it in production. At the same time I am also including 8.4. So in a way I am talking about what MySQL has on top of PostgreSQL by the end of the year. I am not going to include stuff like auto increment if there is something that is more or less equivalent with SERIAL. I am also not including features I consider unwise (like REPLACE). Finally I am skipping XML support, because I know both are working on improving the support, but I have not yet looked at it so closely. So here it goes:

• Multiple charsets/collations in the same DB (all the way down to the column level)
…

De facto standards are the only ones that matter.

That's a bit of a truism in the technology world - well intentioned standards bodies and departments of justice can do their best, but at the end of the day, volume deployment is the only setter of standards. Ubiquity trumps policy, just about every time.

To that point, I was on a panel recently, discussing the impact of technology on the world's more rapidly developing economies (what's often referred to as "BRICA," or Brazil, Russia, India, China and Africa).

One of the speakers referenced an interesting shift in the traditional media industry: western companies were turning their attention toward the developing world. GDP growth wasn't drawing their attention - as much as demographics. Teenagers and those in their early twenties represent the biggest media buyers in …