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 …

In the last several months at Grazr, we've been wrestling with a large database (running on MySQL) of feeds and feed items. The schema is essentially a feeds table with child tables items, items_text (text), and enclosures. We have this database to provide the means for users to be able to merge (a Stream) feeds so that you have an aggregate feed with items for whatever feeds you want in the list of feeds for your merge. It works great, the only problem being the volume of data, which more data means the query to produce that merge becomes slower. We want this merge to be able to be run on the fly, and if it's too slow, the user experience is unacceptable.

So, now I'm in the process of implementing a "Hot Cache" of feeds with an LRU (Least Recently Used) policy. The idea being, that this cache provides a smaller data set for performing the merge query against. We need to be able to handle storing much more data than we currently do …

How To Set Up WebDAV With MySQL Authentication On Apache2 (Debian Etch)

This guide explains how to set up WebDAV with MySQL authentication (using mod_auth_mysql) on Apache2 on a Debian Etch server. WebDAV stands for Web-based Distributed Authoring and Versioning and is a set of extensions to the HTTP protocol that allow users to directly edit files on the Apache server so that they do not need to be downloaded/uploaded via FTP. Of course, WebDAV can also be used to upload and download files.

To coincide with EURO 2008, I’m embarking on a virtual European tour, taking a quick look at open source policies and deployment projects in the 16 nations that are competing in the tournament.

The Czech Republic team was just three minutes away from qualifying for the knockout stages of EURO 2008 on Sunday before Turkey managed to turn a 2-1 deficit into a 3-2 win. Publicly available information on Czech open source deployment projects suggests that the country has had rather more luck when it comes to open source.

Key projects:
There was early success in 2001 when …

We are very pleased to announce the release of MONyog 2.5 Beta 1. This is a major upgrade from the previous releases.

MySQL currently lacks advanced query profiling tools like SQL Server’s Query Profiler. While the current breed of MySQL monitoring tools provide monitoring and advisory information on various system metrics, they don’t help in pinpointing the problematic queries. No amount of hardware upgrades or tuning of mysql.cnf / mysql.ini parameters can match the performance gains that can be achieved when problematic queries and identified, rewritten and/or appropriate indexes are created.

MONyog 2.5 helps in identifying problem SQL by

1) Analyzing the general query log
2) Analyzing the slow query log
3) If the above mentioned logs are unavailable then MONyog query sniffer can capture the results of SHOW PROCESSLIST at user-defined …

When I first saw ZDNet reporting that IBM may open source its DB2 database, I kept waiting for the other shoe to drop. The comment that led to the report? "We have a light version of the product offered for free, which is a step towards exposing our core (DB2) ...

ZDNet and its sister sites ran an interesting story yesterday indicating that IBM might be preparing to release its DB2 database under an open source license. If true, it would be a fascinating turn of events that would have a significant impact on the database industry. Unfortunately, it’s not.

I was immediately suspicious when reading the initial story. For a start it quotes a UK IBM executive: IBM’s UK director of information management software, Chris Livesey. With all due respect to him, if IBM was even hinting at open sourcing DB2, it would surely be rolling out the big guns.

Additionally, I’ve had briefings in the last couple of weeks with both IBM’s data management and open source executives, neither of whom thought to mention open sourcing DB2. That didn’t rule it out entirely of course.

Then there was what …

I’m at the Sun Tech Days in beautiful Philippines, and all I can say is the energy is tremendous. I’m hearing there are about 1,400 attendees, and this number might grow tomorrow.

Armed with a video camera, I decided to take a few video snapshots. My first victimguest on my yet to be named videocast is Wen Huang, Product Manager for NetBeans, at Sun Microsystems.

Wen Huang has been a MySQL user since 1999, and had a past life as a web developer in various web shops, some large, some small. One commonality he had at all his jobs though is that they always use MySQL.

He’s an action junkie, preferring to have the latest version of the MySQL database all the time, and can’t wait for MySQL 5.1 when …

This week the first plugin OS_INFO was successfully compiled and build. Soon it will be available on Launchpad and everyone will be able to download the source code using Bazaar VCS client.
For the upcoming week I'm going to implement the next five plugins according to my tables design http://uosis.mif.vu.lt/~much1973/tables_revised_210508.pdf.
And here goes a report on what was done last week.

Report [2008-06-09 - 2008-06-16):

KEY ACCOMPLISHMENTS LAST WEEK
-- Because of my hardware issues had to move to a new one. Reinstalled OS (Windows and Ubuntu dualboot), SAndbox, MySQL servers. So that now everything is building and running cleanly. The main development is going on Ubuntu and I VirtualBox on Windows will be used to test plugin on other OS.
-- Investigated loader plugin crashes and learnt how to use gdb. I was posting to internals about the crashes and from the feedback I …

Now the fun begins compiling all the data and pretty charts made. We've had 432 responses!

I want to thank the community for taking the survey. If you'd like to comment on the survey, please do! You can post your comment on my blog, or email me directly: mark.schoonover@gmail.com

I've learned a few things about creating surveys and I plan on writing up my thoughts once all the tabulation is done....MySQL DBA & Programming Blog by Mark Schoonover