SQL-based

Flickr mySQL DBA Blog

Quick note—Dathan Pattishall, one of Flickr’s db / software engineers has been dropping notes on his new blog mySQL DBA—some fascinating insight and technical tips.

Related to recent UTF-8 discussions

Will update the wiki in due course.

The Underground PHP and Oracle manual

Chris Jones just announced the publication of the PHP and Oracle Manual (PDF) and from a high speed eyeballing, it’s good—basically tells you everything you need to know to be able to do useful stuff with PHP + Oracle but with little assumed knowledge.

In fact it seems to be geared to the typical LAMP developer—for example there’s a section on “Installing Oracle XE on Debian, Ubuntu and Kubuntu” plus sections on “Limiting Rows and Creating Paged Datasets” and “Auto-Increment Columns” show an awareness that readers will probably have MySQL experience.

Perhaps one addendum (I didn’t find reference to it) would be pointing readers at SQL Developer, which is a fairly new, free offering from Oracle as a desktop based development tool—the Underground manual focuses on a web based interface to Oracle—something logically equivalent to phpMyAdmin and a good starting point I guess but if you have to do real work, SQL Developer is probably a better choice.

The Art of SQL

Graeme Williams writes "One difference between SQL and a conventional procedural programming language is that for SQL there's a bigger gap between what the code says and what the code does. The Art of SQL is the opposite of a cookbook – or rather it's about cooking rather than recipes. It's not a reference manual, although there's plenty to refer back to. It's an intermediate level book which assumes you know how to read and write SQL, and analyzes what SQL does and how it does it." Read on for Graeme's review.

Dinner Tagging

I returned from vacation this past weekend. It was great, very relaxing and overall a good time. Monday morning I faced the reality of the dayjob. Have you ever returned from a nice vacation just to find that certain areas in the office have quite changed for the worse? An increased political charge? Work you had counted on being finished was not touched? Maybe the timeline for your Big Project was silently cut in half?

MySQL 5.0 - A quick look

I've started seriously playing with MySQL 5.0 as an upgrade from the 4.0 that we've been using for a long time now at work. And I'm liking what I'm seeing.

Some people will claim that other databases have had features for years that are just now going into MySQL. I'll agree with that, though no other databases have had the same momentum and open source traction that MySQL has. The closest open source competitor, PostgreSQL, is more feature filled but still doesn't seem to have the following that MySQL has.

Let's look at a few of the new goodies.

Interview: Mike Ho of QCodo

This is the second in a series of interviews we're making available to the CodeSnipers community. We have been working to track down people who we thought had something valuable to say about the software development community, tools, practices, or direction. Some of the names you will recognize immediately, others you've probably never heard of, but all of them have made an impact in one way or another. Without further delay... we have Mike Ho the lead developer of Qcodo.

Qcodo had its debut at the Zend/PHP Conference in October and few in our community were there. Can you tell us about how Qcodo came about and what it does?

Well at the risk of sounding like “yet another PHP framework”, Qcodo is in fact a PHP development framework.

It is focused on allowing development teams create good, solid prototypes in a ridiculously short amount of time, and for giving developers a toolset to mature these prototypes into full-fledge enterprise-level applications.

At its core, Qcodo is broken down into two main parts: the Code Generator and Qforms. The Code Generator focuses on analyzing your database to create basic Create, Restore, Update and Delete (CRUD) functionality. Qforms is an object-oriented stateful, event-driven architecture to handle web page and HTML forms processing, similar to .NET or Java Struts. Both obviously work with each other seamlessly. But you could definitely choose to just use one or just the other.

The entire framework originally started out over 4 years ago as just a simple but robust Microsoft SQL Server and ASP code generator while I was working as an independent contractor. Since then, it has been rearchitected and greatly improved upon throughout the years, first being ported to ASP.NET. Over a year ago it was redesigned specifically for PHP 5 and has been made into a full-fledged development framework for use with the many projects I have been fortunate enough to work on. Throughout Qcodo’s life it has been used on a wide variety of projects on all these platforms, from small startups to Fortune 500 companies like Covad and Lockheed Martin and large government agencies like Chicago Public Schools and NASA.

Earlier this year, I was fortunate enough to be invited to speak at the MySQL User’s Conference, where I talked about the code generator, specifically, and how code generation techniques could be used to greatly accelerate enterprise application development. The feedback was so overwhelming, not only for the technique, but for the code generator itself, that I realized that the market has a huge need for not just the code generator, but an entire framework like Qcodo to be open sourced. So I spent the next couple of months cleaning up the code and ensuring that it was clear of any proprietary or IP constraints, and released it as an open source framework in time for the Zend/PHP Conference.

Business and Database Logic, Part 2

In a previous post on the subject, I touched on some thoughts I had based my application needs to keep data in a database over a period of time, without using updates and deletes.

After doing some pencil and paper design, and a lot of thought, I think I figured out the easiest way to implement this.

From a database standpoint, what we really have is a simple one-to-many relationship. For example:

Let’s say we define an employees table. I want to put as little information in here as possible, and yet I want the most amount of static information available in this table as well. For the purpose of this example, let’s just assume that the only thing static about an employee is their name.

My or Postgre ?

There's no doubt about it, MySQL and PostgreSQL are hot items in the database world right now. But which is better for what and who needs which feature from what and how (whew)?

Dynamically Generated SQL Stored Procedures

One of my favorite MSDN articles has been the key to saving countless tedious hours manually creating the select, insert, update, and delete stored procedures in SQL Server 2000 for web applications: Peter W. DeBetta and J. Byer Hill, MSDN April 2003, Automate the Generation of Stored Procedures for Your Database.

Some developers prefer not to use stored procedures for various reasons, but I agree with Douglas Reilly who essentially concludes that if you don't need to worry about switching from SQL Server to another RDBMS, and if some of your procs have complicated processing in them, then generally it is advantageous to use SQL stored procedures over ad hoc SQL.