I’m not one to use the phrase ‘full of win’ too often, but today’s xkcd certainly qualifies for anyone with a little SQL knowledge:

Via WWDN

I was very pleased when I heard about Oracle adding pivot functionality in select statements. Finally — we wouldn’t have to copy the data to a spreadsheet or code a ton of sum(case when col1 = 'X' then amount else 0 end) total_X for each column we would want to display.

I am basically looking for three things in a pivot-style query:

1. the ability to specify which column will be pivoted as one or more columns in the resulting query
2. row subtotals
3. column subtotals

The first item is the only one that really matters. I can work around the other two, so let’s get started.

Create a test table with a few rows:

create table bob_pivot_test( id number, cost number, tx_date date, product_type varchar2(50));
insert into bob_pivot_test values (1,10.99,sysdate-10,'Toy');
insert into bob_pivot_test values (1,10.99,sysdate-9,'Toy');
insert into bob_pivot_test values …

I am considering taking some time off work to concentrate deeply on MySQL Table Sync, which has been getting usage in very large companies whose names we all know. There are a lot of bugs and feature requests outstanding for it. It is overly complex, needs a lot of work, and I can't do it in one-hour or even three-hour chunks. I need to focus on it. I'm considering asking for a bounty of $2500 USD for this. Please let me know what you think of this; it seems to be a successful way to sponsor development on some other projects, like Vim.

For the amount of time I think this will take, $2500 is far below my per-hour consulting rate; I considered setting the bounty higher, but I think this will be a fair amount.

I would not begin this project before December at the earliest, so there's some time to raise funds and time for me to continue working on High Performance …

This release of MySQL Toolkit updates MySQL Parallel Dump. Together you and I found a few bugs in it (table locking, argument quoting, exit status code). The restore utility is in progress.

I haven't done a serious learning project for a while so this week I decided to play around with Windows Server 2008 and PowerShell. I'll post some thoughts about Server 2008 soon (at first glance it looks terrific once you get past the fact that IIS is now a "role" instead of something you install). But I wanted to post a PowerShell example first because I wanted to do something with it that I couldn't find information about anywhere -- specifically, perform a database query and dump the contents to the console. (Later we'll get fancy and dump it to HTML or send it somewhere via email, etc.)

PowerShell can invoke any .NET object, so I knew it was possible to do database stuff with a PowerShell script, but I couldn't find any examples anywhere, so here's what I cooked up via trial and error.

The first step is to create a database connection:

[system.reflection.assembly]::LoadWithPartialName("MySql.Data")
$cn …

How does INNODB in mysql 5.0 tablespace per file grow?

I can just add data and hope to catch when the table space grows, but I really want to understand the internals of INNODB and build an accurate modal on how big the table space will grow when X rows are added.

• Some things that need to be known.
  
• Data stored in INNODB is stored as pages with a default size of 16KB.
  
• INNODB adds a page for every secondary INDEX
  
• INNODB has some overhead of about 18 bytes (maybe more) per row
  

First let's set up a test environment

delimiter //

CREATE PROCEDURE resettest()

 begin  
        DROP TABLE IF EXISTS testvarchar;
        CREATE TABLE `testvarchar` (
                        `id` int(10) unsigned NOT NULL,
                        `data_key` varchar(255) default …

Today’s xkcd is just wonderful. I think we need giant ones of these on the wall of offices around the world.

A

The Open Source Developers' Conference 2007 Brisbane program schedule is now online!
Registrations are coming along nicely, but it's a bit too early to project possible total numbers.

The organisers have decided that if you register after October 31st, you don't get a conference T-shirt. Nanana.
But seriously.... Elspeth needs time to get them printed, and producing lots extra of various sizes gets silly.
So no worries, unless you slack and don't register soon... earlybird ($275) ends October 14th, after that it'll be $295 until October 31st, and from November 1st you lose your shirt and pay $325.
Full (non-student) registrations include the Wednesday conference dinner, until sold out.

I am considering taking some time off work to concentrate deeply on MySQL Table Sync, which has been getting usage in very large companies whose names we all know. There are a lot of bugs and feature requests outstanding for it. It is overly complex, needs a lot of work, and I can’t do it in one-hour or even three-hour chunks. I need to focus on it. I’m considering asking for a bounty of $2500 USD for this.