FileMaker 12 SQL: Crafting Structural Beauty

It’s hard to believe it’s been only a few months since SQL was introduced as part of FileMaker 12.

In the short time since, developers have quickly put this new capability to use with style and passion, publishing FileMaker SQL tutorials, demos, and development tools almost too many to number. Some early highlights include:

• Greg Lane created an excellent webcast and helpful twitter posts explaining the basics and the finer points of SQL use in FileMaker 12.
• Kevin Frank launched an insightful series of articles exploring the nuances and deeper capabilities of the ExecuteSQL function. And Kevin’s ExecuteSQL unconference session at FileMaker DevCon was heavily attended, giving evidence of growing community interest.
• SeedCode released SQL Explorer, a powerful and free tool for creating well-structured SQL queries directly within FileMaker.
• Andrew Duncan created a set of SQL-based custom functions and explained how SQL can retrieve metadata, enabling us to query our tables and fields directly within SQL queries.
• Beezwax  released InspectorPro 4, rewritten in FileMaker 12 and taking advantage of ExecuteSQL to go from 636 to 135 Table Occurrences.

With all this activity, it’s easy to forget that our use of SQL in FileMaker solutions is still in its early stages. We’re just getting our toes wet in the vast ocean of SQL. This is a very good thing: The best is still to come.

Getting Our Toes Wet

Looking at our early use of FileMaker SQL, many examples in the wild use an ad hoc, left-to-right structure. Often, these queries are written as linear recipes: Start with fields to be retrieved, place them in a SELECT clause, set the matching table in the FROM clause, then tune the WHERE, and ORDER clauses until the query returns desired results. For example:

Select Title, Duration From Movie Where Duration < 90 Order By Title

This is a fine query: It’s simple, direct, and fast. In this kind of basic SQL, a left-to-right, ad hoc approach is natural, and likely the way most of us get started. We can accomplish a great deal with this form of query. Many of us have eliminated scores of TO’s from our FileMaker 12 solutions using basic queries just like this.

But this approach doesn’t scale well as queries become more complex. For example, let’s extend this query to ask which theaters are playing movies between 1 and 2 hours long:

Select Movie::Title, Theater::Theater_Name, Coalesce (Showing::Showtime, '[Call for
Details]') from Movie Join Showing on Showing::Id_Movie = Movie::Id Join Theater on
Theater::Id = Showing::Id_Theater Where Lower (Theater::Region) in ('Santa Barbara',
'San Francisco') And Movie::Duration > 60 and Movie::Duration < 1200 Order by Movie::Title

This is still a simple query, but it’s suddenly become very difficult to read. Are the inner joins valid? Do the columns in the select clause agree with the tables and columns in our other clauses? We’d need to pull this query apart to answer these basic questions. It’s easy to foresee that as we added more clauses, tables, fields, and conditions, a tipping point would soon be reached where this query would become incomprehensible.

The Beginning of Craft

Let’s step back a bit and reflect on our shared experience as FileMaker developers. For many of us, our initial discovery of FileMaker was exhilarating. We found that we could do amazing things with this malleable and powerful application, and we were overnight heroes! In time we took on increasingly complex projects, running into walls here and there, and parts of our solutions became unwieldy. Sooner, or perhaps (ahem) later, we learned the importance of structure for managing complexity. We adopted naming conventions, learned the importance of structuring code, separated long ad hoc scripts into small, reusable parts, and adopted other best practices. We — and our solutions — are now the better for it.

As we learned to gracefully handle complexity, we became craftspeople.

I’m privileged to know many FileMaker developers for whom crafting elegant scripts, custom functions, data structures and (of course) user interfaces is a pursuit of functional excellence and aesthetic beauty.

I’m confident the same soon will be said about how we approach FileMaker SQL. As our experience with SQL deepens in the coming months and years, we will have more opportunity to apply many of the best practices we use in FileMaker scripts and data structures to craft SQL structures of greater beauty, clarity, and resilience.

Creating Structural Beauty

With this aspiration in mind, let’s take another look at this same query, but this time set in a different structure:

SELECT
      movie.title
   ,  theater.theater_name
   ,  COALESCE ( showing.showtime, '[call for details]' )

FROM
      movie
      JOIN showing
         ON showing.id_movie = movie.id
      JOIN theater
         ON theater.id = showing.id_theater

WHERE
      LOWER ( theater.region ) IN ( 'santa barbara' , 'san francisco' )
      AND movie.duration > 60 
      AND movie.duration < 120

ORDER BY
      movie.title ASC

This example uses these simple techniques to create structural clarity:

• Primary SQL Keywords are outdented and capitalized to emphasize essential clause structure.
• Secondary SQL keywords are capitalized inline.
• Other elements, including data table and column names, are rendered in lowercase, again focusing attention on structural keywords.
• Native SQL dot nomenclature is used, rather than FileMaker “::” syntax.
• Individual clause elements are each placed in parallel on separate lines; for example, each field in the SELECT clause appears on a separate line.
• Required punctuation is placed prior to elements; for example, commas in the SELECT clause precede column names, making it much easier to locate missing or extra delimiters, and facilitating copying/pasting lines.

Imparting Semantic Meaning

Structuring a query like this may at first feel foreign and perhaps even unnecessary, particularly if we’ve experienced some initial success with ad hoc queries. But there’s a very big payoff to a structured approach: Once you’ve trained your eye to understand SQL structures, well-structured queries become easy to quickly scan and understand. They expose the key parts of a query directly to us.

By contrast, the earlier ad hoc version of this same query was nearly impossible to grasp as presented. Linear form blocked meaning. The difference between these approaches becomes increasingly pronounced as queries grow in complexity.

Well-structured queries are semantically meaningful, and help us craft SQL that is lighter, more flexible, and more powerful. Said differently, the pursuit of well-crafted SQL isn’t an affectation … but a core part of the craft of creating SQL. As Steve Jobs has it, “Design is not just what it looks like and feels like. Design is how it works.”

One more lesson from our collective FileMaker experience also applies. As a community, we agree on the importance of crafting well-structured, consistent scripts. But we don’t believe there’s only one way to do this, and a number of structural and naming conventions are used in our FileMaker community (there is a similar range of practice in the SQL world as well).

I suggest approaching writing structured SQL in this same spirit. Choose a set of conventions that will help you create clear, consistent, self-documenting SQL code, and make it your own.

Diving into the Deep End

One especially thoughtful aspect of FileMaker 12’s new ExecuteSQL function is the way it masks so much of SQL’s native complexity. This dramatically lowers the bar for FileMaker developers getting started with SQL, letting us ignore native SQL issues like data typing, potential injection attacks, character escaping issues, and more. At the beginning — and later when we want to quickly execute a simple query — this is great convenience.

But when we’re ready to take our SQL skills to a more advanced level, this masking of native SQL can become a hindrance. At this point, we may need to stop reyling on FileMaker to mask SQL’s quirks and instead take a bold plunge into the deep end of the pool. For me, this means explicitly casting FileMaker values into correct SQL types, learning and using standard SQL functions instead of FileMaker variants, and following accepted SQL conventions. My goal is for the SQL I create to be fully readable to a SQL developer who knows nothing of FileMaker.

As before, this may raise some eyebrows. But once again, this approach offers significant payback for the effort invested.

First, SQL written like this is immediately clear to SQL professionals who won’t recognize Filemaker-specific syntax, making it possible to share questions, problems, and inspirations with native SQL developers. Second, working in native SQL makes it much more natural to step directly into deeper features of the SQL language.

As a long-time FileMaker developer, I can report that — when well structured — native SQL queries are much less scary than I’d imagined. As a poor swimmer who can barely float, I can also say that the water in the SQL pool is really quite nice. Jump in!

Recommended Reading

Simply SQL by Rudy Limeback has become my trusted companion as I explore SQL. I’ve found this short book to be a friendly introduction to the syntactical details and native mindset of SQL. Coming from a FileMaker-centric background, it’s helped me understand both simple and more advanced SQL concepts, nicely balancing clarity, depth, and detailed examples.

— Brian

Follow Brian on Twitter