While working on my presentation about ORMs in PHP for the upcoming php|tek conference in May, I’m finding it slightly challenging to pick a good example of data represented in the application and in the database, so that I could insert various ORMs in between these two ends.

The example should be flexible enough to fit within different ORMs. Think of a scale from ActiveRecord to something really complicated like Java’s Hibernate – that’s the scale for the ORMs. It also needs to be really intuitive, so that the audience of the talk can understand it without much explanation – I want to focus on the ORMs themselves, not something else.

At this point, I’m leaning toward an example representing a user on a social network site and his friends. Something along these lines (don’t beat me up, it’s a very rough draft):

object model:

database model:

I think it’s a good idea to keep this example specific to functionality common in web applications (the alternative is kittens, really). On the other hand, I don’t want the example to be overly complex, so that the audience focuses more on the topic of the presentation rather than the details of the example.

When you come to presentations, what do you look for in examples? Are you looking for something you can basically copy and paste and use in your code? Or are you more interested in high-level theory where examples take a back seat and are there just for flavor but not really to explain?

People borrow my database books and a bunch of them are in circulation at any given time. Also, a lot of database resources are online only (e.g. for Oracle and MySQL). Right now, this is the state of my database bookshelf:

l2db

Not bad! You can probably get through a lot with just these three…

I think by now everyone’s familiar with the phrase:

“Premature optimization is the root of all evil.”

In my day-to-day job, I worry a lot about writing high-performance applications, especially in the way applications manage and retrieve data. There is a huge difference between designing your application to be optimized for a specific purpose and premature optimization. It’s one thing to worry whether echo or print is faster (who cares, really?) – but it’s another to design an application to tolerate some slowness in low-traffic parts but gaining ultra fast response time on the first access to the application. For example, viewing your photo album may take a while but seeing the homepage is pretty freakin’ fast!

In a recent post, Sebastian Bergmann points out that you should not micro-optimize (in reference to a PHP micro-optimization tricks post by Alex Netkachov). I agree wholeheartedly. It’s a nice list and definitely something to keep in mind, but it should be just a small component of your developer bag of tricks! Before you can optimize, you have to design well first. Proper design leads to potential for great optimization. And to design well, you have to really understand the problem you’re trying to solve. Sebastian links to slides by Ilia Alshanetky from PHP Quebec 2009 on Common Optimization Mistakes. Ilia also points out that it’s important to understand before you start fixing:

Solve the business case before optimizing the solution.

For PHP Advent, I wrote an article Optimize This!, which talks about how to optimize an existing application. The most important part to focus on is understand what the application is doing and then figuring out where you’ll have most impact. Sure, you can use echo over print (or print over echo, once again, who cares?) to get that 0.0001% performance boost, but if you’re doing 140 live SQL queries on your homepage, your application will still have 3 minute response time and with more users, the performance will continue to degrade until the application becomes unusable.

Modularizing code (aka “Object-Oriented Programming”) makes it easy to focus on problem areas in an existing application. Designing well doesn’t always mean designing for high performance. Instead, it means designing for flexibility and atomicity – you want to be able to easily change things without huge impact on the overall system. So before you go an optimize, take a step back and think about what your application is doing and what it’s supposed to do. Go write on the white board – diagrams prove very useful in figuring out flows and functionality! Go chat in #phpc channel on irc.freenode.net – the PHP community will help (with a little bit of playful making fun of, but we’ll help). Just don’t go changing all your methods to static methods unless you’re really sure it’s the right thing to do!

Being an Oracle fan, I often find myself on the defense trying to explain its awesomeness While it’s uber expensive, obviously it has desirable features that sometimes make it worth it. On the other hand, in my experience, Oracle takes a much stricter, “comp-sci” (quotes on purpose) approach while MySQL can be wonderful in forgiving developers and expecting that developers are more concerned with getting stuff done rather than being nitpicky (which is true most of the time, really).

Allow me to elaborate. There are a few features that are available to MySQL developers that are a bit harder to find if you’re using Oracle. Here are a couple:

This one’s kind of a cliche and if I had $1 for every time I heard it, I’d have maybe $20 by now!

“Oracle doesn’t have SHOW CREATE TABLE!”

So let’s level the playing field a little bit. First off, MySQL doesn’t offer “show create table” either. When you see:

mysql>

you’re actually running a MySQL client that provides this functionality. With Oracle, the somewhat default client is sqlplus – it offers access, but it’s not very fancy. You can use gqlplus (built by the open source community!) for things like command line history (aka “arrow up to previous query”), or go full GUI experience with SQL Developer (which I personally recommend, it’s pretty sweet). The latter offers functionality that will do the same thing you expect “show create table” to do when using MySQL client.

More importantly, Oracle offers an entire set of built-in tables that provide data about your database schema. Next time you have access to Oracle, try this:

DESCRIBE user_tables;
DESCRIBE user_tab_columns;
DESCRIBE user_constraints;
DESCRIBE all_source;

Oracle offers you hundreds of ways to find out information about your schema. My hunch is that the reason “show create table” does not come in a default client is because of the amount of possibilities this means. Compare the CREATE TABLE statement available in Oracle and in MySQL. There is simply so much that “show create table” could show you in Oracle, making choices what to omit and deciding what is default perhaps is better left to developers than to a shell script. (It is also my belief that your DDL – how your tables are created – should be stored as code under version control, so there’s that.)

Another “Zomg, MySQL is so much better than Oracle!” claim I witnessed recently is:

“Oracle doesn’t have INSERT IGNORE!”

The response is, yes, of course it does, it’s just not called “insert ignore”. “Insert ignore” is MySQL’s feature that veers away from standard SQL as much as anything else.

So what’s the deal here? I find that the best way to find equivalent features between MySQL and Oracle is to take a step back and think in more general, basic language concepts. What “insert ignore” really does is:

1. attempt to insert
2. if failed due to a constraint violation, continue

This looks to me to be simple exception handling! Oracle comes with PL/SQL, an entire language of its own. Surely it has a way to handle exceptions! Here are a couple of ways to do “insert ignore” in oracle:

begin
    insert into some_table(field) values ('zomg');
exception
    when DUP_VAL_ON_INDEX then
        return;
end;
/

Not only does Oracle allow you to do “insert ignore”, it provides you with fine grained control about exceptions – in this case, I am catching the case when a unique key (such as the primary key) is violated. In his blog post, Hampus recommends a similar approach and also points out that you can use Oracle’s MERGE statement (which, incidentally, is on of my favorite statements!) for this purpose.

I guess the purpose of this post is to once again point out the inconsistency in how different databases implement standard SQL (I talked about this a little bit in a post about The Meaning of Keys). When you see something that Oracle has and MySQL and vice versa is sometimes tied to this different approach. I’m willing to concede that MySQL has some awesomeness, such as the kick-ass GROUP_CONCAT function. But if you still feel like QQ’ing at Oracle (e.g. in comments on this blog post), fair warning, I will continue to defend it – I only ask that you QQ constructively!

A coworker, Craig Campbell just launched a new blog recently. One of the really neat things about his blog is how he handles syntax highlighting for code samples – check out examples in his interesting post about Cool Object Building with PHP. In fact, he got so many good comments about this approach that he even wrote a post explaining exactly how he does it: Syntax Highlighting for Your Blog Using TextMate. (It even works in Internet Exploder!)

I’ve been looking for a good solution for syntax highlighting for code samples I post on this blog since my move to WordPress, so his solution certainly looks appealing. I use vim as my main editor and I’ve been staying away from TextMate for quite some time. However, there seems to be a lot of interest in creating vi plugins for TextMate that would emulate vi behavior, such as TextMate vi Plugin. If you’ve ever used and loved vi/vim, you know that it’s hard to move to an editor that requires the use of a mouse for text editing. I finally caved in 2 years ago and moved from Linux to Mac as my primary development environment, so perhaps checking out TextMate is a good idea.

I’m still keeping pine/alpine for reading e-mail though!