and probably my last one ..

I mentioned a couple of weeks ago that I was looking into a way of converting my static page with published papers, articles and presentations in a more dynamic page where I could create RSS feeds from the updates and feed them into another site for everybody to use.

Some people suggested that I'd have a look at CCK & Views for this. While up till now when creating an app I had usually written my own Drupal module with my own database schema and manually written SQL Queries. This indeed looked like the perfect opportunity to dig into the CCK and Views thingie.

Now I must admit that I`m not really fond of "generated queries"

I've had nightmares before when having …

As I putter around the MySQL INFORMATION_SCHEMA, I am finding lots of undocumented behavior for fields that should be straightforward. For example, the VIEWS table holds information about views, and the VIEW_DEFINITION field contains the view definition, right?

Well, when I was looking at the VIEW_DEFINITION today, I noticed an odd thing. Even though I had permissions to see the view definition (as proven by the SHOW CREATE VIEW command), the INFORMATION_SCHEMA.VIEWS table sometimes came up blank for the VIEW_DEFINITION. I had to figure out why, and now that I know, I’m not sure if it’s a bug or a feature…..can you figure it out?

mysql> USE INFORMATION_SCHEMA;
Database changed
mysql> SELECT TABLE_NAME,VIEW_DEFINITION FROM VIEWS WHERE TABLE_SCHEMA='sakila';

+----------------------------+-----------------+
| TABLE_NAME …

I just read about OLAP4ALL's Materialized Views for MySQL software that "offers the functionality of Materialized Views in MySQL that are not natively supported in the MySQL database.".

What nags me about this is the fact that it is "implemented as a separate Java program running on the server where MySQL is installed".

The server-side of this should be doable using Stored Procedures and/or Triggers. For the client-side a bit of convenience functionality for query rewriting would be needed.

Sounds like an interesting project that I would like to pursue when I find the time.

…

In one of my latest postings I mentioned a way to create semi materialized views in MySQL. The problem was that the solution required a stored procedure for every materialized view. That's very annoying. So I looked for a more general and dynamic approach. The result is another stored procedure with 3 parameters:

• the name for the source table / view
• the primary key columns of the source table / view
• the desired name for the materialized view

Basically this procedure does the following:

• It drops the materialized view if it already exists
• It creates the materialized view with the structure and data of the source table
• It adds a primary key to the newly created materialized view

However the procedure does not create triggers on the table. This could be an improvement for a …