sql

When I first saw the Google Chrome comic book, I really thought it was a great idea. Delivering tech content in a light-hearted manner has often been my style, and this just raised that to a higher level, with friednly comic style animations to walk you through it. It did make me wonder though, would a postgres book along similar lines be successful? I mean, we've been talking about how processed based architectures lead to more stability for years, but no one seemed to care about that untill Google talked about it.

But, it seems I'm already late to the game, based on this upcoming book (hat tip to Mark for the link):



I know, it sounds like a joke, but check out the description:

In The Manga Guide to Databases, Tico the fairy teaches the Princess how to simplify her data management. We follow along as they design a relational database, understand the entity-relationship model, perform basic database operations, and delve into more advanced topics. Once the Princess is familiar with transactions and basic SQL statements, she can keep her data timely and accurate for the entire kingdom. Finally, Tico explains ways to make the database more efficient and secure, and they discuss methods for concurrency and replication.

No Starch, if you need a tech/book review, mark me down, I can't wait to see this!

(And BTW, if any Japanese folks are familiar with Mana Takahashi's work, I'd love to hear your thoughts or get some links in the comments)

Lukas' recent post on [http://pooteeweet.org/blog/1201 solving the prepared statement problem] got wondering, if open source databases implement something, how likely is it to become a defacto standard? The best example of this is the LIMIT clause, which both [http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html MySQL] and [http://www.postgresql.org/docs/current/interactive/queries-limit.html PostgreSQL] support, and that anyone who has used either database will tell you is a far superior tool to what it is available in other systems (don't get me wrong, [http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html rownum()] has it's place, but for what LIMIT does it's not as good). And while LIMIT is not standard SQL, can you imagine if the SQL Standard Committee were to adopt a syntax that conflicted with the Postgres/MySQL implementations?



Take this with a grain of salt, but really there are only 5 databases that matter anymore, Oracle, DB2, MS SQL, MySQL, and Postgres. Sure, there are many other successful database systems, and also some niche databases that are big players in their segment, but those 5 are the ones that set the tone. Even with Oracle far out pacing MySQL on dollars, the number of people who have joined the tech industry within the last 5 years who have hands on experience with MySQL high enough that it is not uncommon for people to look for their syntax in other database products (for better or worse). And remember, Postgres is [http://freshmeat.net/stats/#popularity even more popular] than MySQL in some places; one should expect the impact of open source databases to rise in this area faster than just by watching market share.



So, what kind of problems could be solved with new syntax? Honestly I am not really sure. One piece of syntax I always though SQL got wrong was the UPDATE statement. I've always thought that the syntax should have been UPDATE table WHERE conditions SET column=data, if only because it would have saved us from countless unconstrained updates from junior DBA's. Other examples might be to take something like MySQL's [http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html ON DUPLICATE KEY] syntax, clear up any sanity issues with implementation, and then implement it in Postgres. (Yes, this examples are questionable, since there are SQL Standard ways of doing this stuff, but the syntax of this command sure seems more user friendly).



Of course, this whole idea overlooks the notion that the SQL Standard committee has often been used as a tool between database vendors to make life tougher for their competitors, so it might be unlikely that the committee would ever adopt the syntax from the [http://planetosdb.org/ FLOSS community] if it meant giving them some type of advantage, but this is also why FLOSS databases could give hope, since as more user oriented projects, if they worked together you should really see syntax that makes everyones lives easier. Imagine Postgres, MySQL, Firebird, SQLite, and Ingres all working together to create their own, user driven syntaxes.

A couple weeks ago I hacked up some sql for this and had planned to blog about it but never got around to it. Of course then I needed to use the technique again yesterday, so it's time to make a more permanent record for next time.

The problem we're solving is the need to do group by on time intervals of less than 1 hour. This isn't exactly hard, but it's a little tricky, since you can't easily pull out random fractions of an hour. The way I solved it was working my way through a series of sql statements.



This first sql statement has two parts; first I grab the current time and use the date_trunc function to cut it to the current minute. This isn't needed for the final query, it is more for reference while I worked things out. The second part of the query I used the extract function to get just the minute portion of the time, in this case 47 minutes.



Next, I divided the minutes by 15, to give me the quarter hour. Of course the decimal is a bit unwieldy, so...



I used the round function to get something a little nicer to work with. Now, this tells us our timestamp is in the third quarter hour, but if we're going to have something to group by on, we want something a little more real world.



So now we have our time, rounded to the quarter hour of 45 minutes, and the only thing left to do is turn it back into a normal timestamp.



Note here I used date_trunc to get my time to set to the base time of the hour, and then I add back in the '45 minutes' that are generated through our series of rounding and extracting. Once you have this, using it in an aggregate is only a short step away.



Note you can adjust the interval (eg. make it every 5 minutes) by changing the number you divde and multiply by, so this technique should work for any sub-hour intervals, and would not be hard to adapt to other non-standard amounts (like every 4 hours). Also remember this was something I whipped out rather quickly, I've done no analysis on how well it scales, if you've worked out a better/cleaner query by all means, please post to the comments. HTH.

They tell me that my hosting this edition of [http://www.pythian.com/blogs/about-log-buffer/ Log Buffer]; the weekly intersection of all things DBA and blog; has nothing to do with my birthday being on Sunday, but if it's all the same I'd like to think of it as a pre-emptive strike against my birthday wish list. While you ponder the possibilities of what gift you might want to get me, allow me to present a complimentary gift bag for your reading pleasure.






The right way to start this week off is by mentioning the gifts everyone else recieved this week, starting with Devrim's post in [http://people.planetpostgresql.org/devrim/ Devrim's PostgreSQL Diary] about the [http://people.planetpostgresql.org/devrim/index.php?/archives/76-New-PostgreSQL-releases-announced,-RPMs-are-built.html new series of releases of PostgreSQL], going from 8.2.1 all the way back to 7.3.16. PostgreSQL DBA's weren't the only ones with new toys, as Kaj from [http://www.planetmysql.org/kaj/ Kaj Arno's blog] announced the [http://www.planetmysql.org/kaj/?p=83 release of MySQL 5.0.33 Community Server] along with a lengthy explination on the MySQL release policy. If that doesn't sound exciting enough for you, Robin (of [http://www.planetmysql.org/robin/ Robin's blog] fame) notes that there are now [http://www.planetmysql.org/robin/?p=29 Falcon alpha binaries available] for download as well.



If your favorite database wasn't included in that list there's no need to worry, as Peter points out in [http://pkhosblog.blogspot.com/index.html Peter K's Blog] that all vendors will need to offer some type of patch soon for the upcoming [http://pkhosblog.blogspot.com/2007/01/daylight-savings-time-2007.html Daylight Savings Time rule changes] for 2007. And while you contemplate needing to do another update to your system, check out [http://blogs.ittoolbox.com/database/solutions An Expert's Guide to Database Solutions] where James F. Koopmanns posts on [http://blogs.ittoolbox.com/database/solutions/archives/Is-a-24X7-database-system-a-myth-13807 24x7 database systems].



So given the lack of downtime allowed and the frequency of updates required, you would think a DBA's job was tough enough right? If not then share the pain of Eddie Awad trying to straighten out the mess of [http://awads.net/wp/2007/01/10/what-does-reserved-y-really-mean/ Oracle's definition of "Reserved Words"] in [http://awads.net/wp/ Eddie Awad's blog], of Peter Zaitsev getting bitten by [http://www.mysqlperformanceblog.com/2007/01/09/mysql-automatic-data-truncation-can-backfire/ MySQL automatic data truncation] in the [http://www.mysqlperformanceblog.com/ MySQL Performance Blog], or watching Greg Mullane walk through [http://people.planetpostgresql.org/greg/index.php?/archives/88-Performing-a-reindex-of-the-system-tables.html Reindexing the PostgreSQL system catalogs] in his blog, Greg's Postgres stuff. Of course none of those problems seem as big as the problem talked about in the [http://dbasrus.blogspot.com/ DBAs-R-Us] blog, where Nuno Souto goes about [http://dbasrus.blogspot.com/2007/01/no-moore-part-1.html taking Moore's Law away from us].



OK, so it sounds like those folks may have had some rough times recently, but apparently Craig Mullins is having a blast listening to his new Video iPOD's. Perhaps he was listening to the recent podcast with one of the [http://www.orablogs.com/sergio/archives/001884.html Oracle Linux Kernel Team] employees that Sergio (of [http://www.orablogs.com/sergio/ Sergio's blog] fame) gave us a pointer to? Well whatever it was, Craig was so inspired he started a new series in his blog [http://www.dbazine.com/blogs/blog-cm/craigmullins/ Perspectives on Database Management] on managing metadata, with both [http://www.dbazine.com/blogs/blog-cm/craigmullins/blogentry.2007-01-08.7608570940 part 1] and [http://www.dbazine.com/blogs/blog-cm/craigmullins/blogentry.2007-01-09.1961841247 part 2] coming this week. Maybe that just goes to show you that sometimes you have to create your own happiness. Or if that fails, then try [http://blogs.ittoolbox.com/oracle/guide/archives/manually-creating-a-logical-change-record-lcr--13838 creating your own logical change records] using techniques from Lewis Cunningham's blog, [http://blogs.ittoolbox.com/oracle/guide An Expert's Guide to Oracle Technology].



Maybe that's not quite the same? Well, one thing that generally brings a smile to my face is reading up on interesting techniques for manipulating SQL, like the one laid out in the [http://www.xaprb.com/blog Xaprb] blog for [http://www.xaprb.com/blog/2007/01/11/how-to-implement-a-queue-in-sql/ implementing queue tables in SQL]. Of course what really made me chuckle was another item from [http://people.planetpostgresql.org/greg/ Greg's Postgres stuff] where Greg followed up with a [http://people.planetpostgresql.org/greg/index.php?/archives/89-Implementing-a-queue-in-SQL-Postgres-version.html one line queue system] that even works transparently for users, based on PostgreSQL's Rule system.



So with that fine readers, we shall conclude this weeks edition of Log Buffer. I hope it's been fun for you, I know I had a great time hosting it. I'd especially like to thank the cast and crew who have really made me feel at home... so long folks!

I originally had a need for a charindex like function in postgresql sometime last week, and after some discussion on irc PJMODOS was kind enough to give me this:



CREATE OR REPLACE FUNCTION CHARINDEX(text, text, integer) RETURNS integer AS

$$

SELECT CASE WHEN strpos(substr($2, $3+1), $1) = 0 THEN 0 ELSE strpos(substr($2, $3+1), $1) + $3 END;

$$

LANGUAGE SQL

IMMUTABLE

RETURNS NULL ON NULL INPUT;



Since I needed it again today and had to spend a bunch of time looking for it, I figured I ought to blog it so I could find it in the future,so here it is.



reference links for charindex:

[http://lejalgenes.com/techtips/tips/Microsoft_SQL_Server/Find_second_occurrence_on_string_in_MSSQL.php http://lejalgenes.com/techtips/tips/Microsoft_SQL_Server/Find_second_occurrence_on_string_in_MSSQL.php]

[http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks120.htm http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks120.htm]

I happened to be looking at the [http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm#sthref1748 ratio to report function] available in Oracle, and got a little intrigued by it. Mainly I was wondering how possible it would be to do something like this in PostgreSQL. The gist of it is fairly simple to reproduce in SQL; in this example we'll generate a breakdown of the sales processed by each employee:



pagila=# select staff_id, sum(amount), sum(amount)/(select sum(amount) from payment) from payment group by staff_id;

staff_id | sum | ?column?

----------+----------+------------------------

2 | 33927.04 | 0.50324527330174759862

1 | 33489.47 | 0.49675472669825240138

(2 rows)