Jump to content
The ORIGINAL FileMaker Community - Forum - Online Business Apps & Software Forum
AHunter3

SQL, from a FileMaker Developer's Perspective

Recommended Posts

AHunter3

Kind of like a Windows switcher's guide to the Mac or vice versa, I guess; the web — Amazon and otherwise — have plenty of guides to SQL, beginner's orientations, SQL for Dummies, etc etc, but some of it assumes you've never met a database before in your life, while some assumes a passing familiarity with relational db theory... I never did find any that were truly aimed at the proficient FileMaker developer.

 

Having muddled through it a bit this winter and spring, I thought I'd share my perceptions. Those of you who are FileMaker-centric as I am, and have yet to mess with SQL to any real extent, may benefit somewhat, and those of you who came IN here to learn FileMaker after already knowing SQL environments may find it amusing and informative to see what your old familiar world looks like from the alien perspective of a FileMaker-centric developer.

 

The specific environment I have installed and am cutting my SQL teeth on is MySQL. It was my intent, of course, to use it as a data source for FileMaker 9's (then 10's) ability to use ODBC data sources directly in the same fashion as native FmPro tables, but also to move beyond my elementary ability to muddle my way through a SELECT query and get a better sense of how SQL systems really 'think'.

 

Preconceptions: I wrote this before diving into MySQL. I can tell you that looking back at that I do not find much to disagree with, but I would stress slightly different things.

 

OK let's get started.

 

You are not here. You don't get to BE here. There is no YOU in this database. This, I find, is the biggest conceptual difference between FileMaker and SQL. In FileMaker, you yourself always have a context. You are IN a window, which is the active window. Inside that window, you are ON a layout. As a consequence of the native table of the table occurrence OF that layout, you are IN a table. Unless you are in Find Mode or Preview Mode, you are also, as a consquence of which record is the active record, ON a specific record. You may also be IN a found set that includes other records as well. And your cursor may be in a field in which case you are IN a field (and this may be true of Find Mode as well). Oh and yeah you are always IN a mode.

 

 

SQL is not like that. There is no focus. You do not at any time get to be IN the database doing stuff. You cannot be in Browse Mode and just viewing data and when you see a bit you'd like to change you click into the relevant field and type in new information. There is never at any time an answer to any form of the question "where am I".

 

Let me tell you what SQL is like instead. If FileMaker were by analogy the experience of actually GOING to the grocery store and being IN the grocery store and going up and down the aisles and seeing what is on the shelves and perhaps editing that by removing a loaf of bread here and a bottle of seltzer there, SQL is a very odd grocery store. You must instead hire someone. "You there! I want you go into that there grocery store and go to aisle 2 and see if they have English Muffins. Come back here and report to me all the brands of English Muffins, and the number in each pack, and whether they are whole wheat or regular wheat or whatever, and how many of each type of package, and the price of". The guy obligingly runs into the grocery store, where you may not go, and comes back and tells you what you asked for. But this guy makes no observations of his own and only tells you what you asked him to inform you of. Based on the information provided to you, you may then send the guy back in again to remove one pack of Thomas English Muffins, 4-pack, Sandwich Size, regular wheat, from the shelf. If it did not occur to you to ask the guy to look at the fresh date on the different packages, you do not get to observe that because you yourself do not get to go into the store. OK OK to be qutie honest you could have told the guy instead "Go into the store and return to me every piece of information that exists pertinent to items that are on aisle 2 that happen to be English Muffins"; there IS a wildcard symbol that means "every field that exists". And in truth I suppose that IS a bit like being in Browse Mode in FileMaker, especially if you were on an AllFields layout. But that brings me to the next item...

 

There is nothing quite like a layout. As many of you may already be aware, SQL itself is not the environment that its own users use to interact with SQL. THEY live in a screen-world set up for them in Brio, or Crystal Reports, or a PHP-tagged web page displayed in a web browser. I knew that much myself, which I think is why the "you can't BE here" aspect of SQL itself threw me; I guess I figured that was just an aspect of these various 'reporting' interfaces, and it's not, it's true of SQL itself. Anyway, if you specify (as per the grocery store example above) all the records where Item Name is "English Muffins" and Aisle is "2" and ask it to show all columns (fields) by using the wildcard symbol (*), you get a hideous morass of text on your command line screen. Raw SQL does not do "layouts". Everything comes back spreadsheet-style (Table-view style), each field as a column and each record a row, EXCEPT that stuffed into a command line terminal interface's output it wraps when it ceases to fit, much like a long calculation formula will wrap at the end of a formula-box even if you did not insert a hard return.

 

Onwards to those various reporting interfaces. Yes, you could set one up so as to have all the (relevant) fields on it, just as you would create a layout in FileMaker. And yes you could code it so that when the "submit" button is clicked it hands off to the actual SQL environment the SQL equivalent of a Find Request. But it requires a lot of code on both levels: setting up the array of blank input fields to specify the Find request visually & interactively, on the one hand, and turning that mass of submitted information into a SQL Query on the other. OH, and then a third level, returning back all of that information, duly formatted. And a Filemaker form layout, ideal for displaying one record to best advantage with a way to scroll through records viewing one at a time, appears at least at THIS stage of my SQL investigations, to be awkward and convoluted at best. More easily you could return that kind of display for the first of x records returned (by telling SQL to return one row only or by discarding the rest of what gets returned) OR you could display it in a list view (but now we are back to the problem of perhaps having more fields than can conveniently be displayed left to right... although at least we can use scroll bars and don't have to deal with the text wrap within a command line interface!

 

Big Shock: SQL is not a Relational Database Environment! Well of course it is. They have been telling us so, all those years when the SQL-centric folks dismissed FileMaker for NOT being a true relational database environment, remember that? Ok hold onto your proverbial hats. In FileMaker, you know how we set up relationships? Whether in old-world FileMaker like FileMaker 3/4/5/6 where its in a list view or modern FileMaker 7/8/9/10 where it is done and displayed from a graphical representation, the relationships are part of the structure, overtly and specifically defined? SQL does not have that. Really. Seriously.

 

At the most, what SQL has is this: a requirement in what we would consider child tables, that in order for a child table record to exist at all, there absolutely must be a corresponding parent record, set up in the definition of the foreign key field, within which you can describe the table and field that does constitute the parent table and primary key thereof.

 

More often, "relationships" are not even present in that sense; they have been described to me as "syntactical sugar" meaning "in our minds, as developers, they exist, but there's no actual structure there".

 

How relationships actually work in a SQL environment is that when you QUERY you might ask for {this field, that field, other field, yadda yadda} all records from Invoices and from InvoiceLineItems where the Client ID in Invoices is "12345" and the Invoice data in Invoices is "> 12/31/2007" and where InvoiceID in Invoices = ForeignKey in InvoiceLineItems.

 

To do that, you don't even need a relationship "defined" anywhere. You could just as easily (and with equal speed and effectiveness) "define" a new relationship on-the-fly within your query, one that had never existed before, by including in your query that CreatedBy in Invoices = SKU_ID in InvoiceLineItems. It may or may not be a useful relationship, one that may or may not have any actual matching records, doesn't matter, you can point to any field in any table and ask, as part of your QUERY, that it match up with the value of any other field in any other table and BANG! you've "created" a relationship. It neither persists nor fails to persist: there is no persistent structural definition of a relationship. They just ain't there.

 

As I said before, the limited exception is the restriction you can place in some field like ForeignKey in InvoiceLineItems to say "this value must correspond to at least one existent value in the column Invoice_ID in the table Invoices". continued -->

Share this post


Link to post
Share on other sites
AHunter3

Time for Some SQL Advantages. This all reads negative. Hey, it's foreign, new, weird, and different, and the things that strike us first and foremost are things that are different and dismayingly so. Can this FileMaker developer find anything nice to say about SQL? To start with, speed. More specifically, you just saw above how you can say "bring me all values from Invoices and also from InvoiceLineItems where the Client ID in Invoices is "12345" and the Invoice data in Invoices is "> 12/31/2007" and where InvoiceID in Invoices = ForeignKey in InvoiceLineItems? Add to that the request that the per-invoice Sum of ExtendedPrice where ExtendedPrice is in InvoiceLineItems is greater than $250. As a FileMaker developer you can see that if we did this in our own environment, this is a Find request that includes values in related tables. It's going to be slow because there is no way to index related field values, and, worse, the field being searched on is aggregating information from related table info (the SUM of the values in related table InvoiceLineItems, ugh!). SQL doesn't care. Zap, here's your answer. It can find based on queries that span related tables with no appreciable speed hits.

 

What, you want more? That's not impressive enough?

 

Umm, nothing compelling comes to mind. Yet. Stay tuned.

 

EDIT: Well, the ability to treat any two tables as related by Field X = Field Y (or for that matter Field X > Field Y and Field A

 

Except that that's misleading. In the SQL sense of word "relational", FileMaker Pro 2.1 was a relational database. You could have tables that had fields that you could consider to establish a relationship between the tables. SQL databases have no intrinsic structure that constitutes "a relationship' so why point at poor old FileMaker 2 and laugh at the fact that it, too, has no intrinsic structure that "is a relationship"? I can copy a field value in one table and call a remote script in another table (another file) and perform a find, then copy all those records and assemble on one "report screen" the values from the original file 1 record and the multiple related file 2 records; how is that more primitive, really, than writing a QUERY in Brio or PHP that SELECTS rows in roughly that same way and returns (writes back) the results to my screen? So the "convenience" consists of the fact that you can treat anything as a relationship because they don't exist in the structure, only in your imagination; and the advantage, once again, is back to speed. (Yes, you can do all of that in Filemaker 2. But not quickly.) The relationships that we, in FileMaker, really do have let us display data and navigate from table to table via relationships effortlessly and with some modicum of speed, and with ease to the developer. That SQL systems can go snag data and write it to screen quite a bit more quickly keeps us from noticing that if you ignore the speed and concentrate on what it is actually doing, it's doing things in a very 'manual' way, actually, with no true structure to facilitate that.

Share this post


Link to post
Share on other sites
dreygo

I always appreciate good musing and metaphors, but this one makes me wonder...

 

I think anyone who enjoys database development and exist primarily in the Filemaker world (i.e. ME) has at some point pondered the mysteries of foreign environments. Everyone is aware of SQL, but it seems to me like a life that evolved in a different solar system without any knowledge of FMP's existence. Being an in house developer may afford me the luxury of database snobbery, but how much can knowledge of SQL help me in ways that I have yet to realize? I am not one to waste energy unnecessarily, but am sure I would enjoy learning SQL if there were a significant return for the effort. When do you know you need it? I guess the context of my question is specific to function or performance...what can be done in SQL that really distinguishes it from FMP?

 

(speed is a great example)

Share this post


Link to post
Share on other sites
Prufrock

You fundamentally misunderstand what "relational" means. RDBMS has nothing to do with "relationships between tables". The term "relational" in RDBMS refers to the concept of Relations (R-Tables), not relationships.

 

The Relational Model of data was designed to abolish the need for navigational structures between different data elements. It is the absence of navigational links or pointers in data that is one of the key advantages of a relational system.

Share this post


Link to post
Share on other sites
AHunter3

What's an R-Table?

Share this post


Link to post
Share on other sites
dreygo
You fundamentally misunderstand what "relational" means. RDBMS has nothing to do with "relationships between tables". The term "relational" in RDBMS refers to the concept of Relations (R-Tables), not relationships.

 

The Relational Model of data was designed to abolish the need for navigational structures between different data elements. It is the absence of navigational links or pointers in data that is one of the key advantages of a relational system.

Am I reading this as more of a flat file structure?

Share this post


Link to post
Share on other sites
Maarten Witberg

here's an article, the first few pages are devoted to explaining the difference between navigational and relational databases.

 

http://www.cs.berkeley.edu/~brewer/cs262/SystemR.pdf

 

I hit on this thru wikipedia (googling for the term r-table, which term I also would like to know the meaning of).

 

flat file in filemaker speak means all data in a solution is stored in one table (I do not know if this is a common name outside fm world). A relational solution would typically have more than one table, doesn't matter on which platform it is created. IIUC "relationship" is the thing you define in a filemaker TO graph between two tables. "relational" means you have an algorithm to query datasets in separate tables. This is true of sql and filemaker and access and so on. Far as I know there are no development platforms based on navigational structure.

Share this post


Link to post
Share on other sites
Prufrock

R-Table is E.F.Codd's term for a table that contains a relation value. R-Tables are also known as relation variables, relvars or time-varying relations.

 

In the SQL context an R-Table is a table or view or table expression (query) that properly represents a relation and its constraints. In SQL, keys are optional or not supported at all in some places. So not all SQL tables are R-Tables and SQL is not truly relational. However, SQL does eliminate navigational structures in data and it does have a language that very roughly approximates some of the relational algebra and calculus. For those reasons SQL DBMSs are often called "relational", even though they have lots of non-relational aspects to them.

Share this post


Link to post
Share on other sites
AHunter3

I'm futzing around with PHP as well as MySQL. (I have the database set up with a FileMaker front, but since that's not how most of the world deals with their SQL data I figured I'd try to pick up some PHP).

 

At the beginning of the day, I had a little web form that would let me submit a case worker's name and it would query the database and return a list view of the active cases (status = "1 Active Case") assigned to that worker. Today's work moved that to having two fields, one for case worker name and one for status, and it does finds closer to the way FileMaker does now (i.e., you don't have to specify the exact full string in order to find by social worker or by status — "hunter" for caseworker and "active" will do the job now), and it has routines that handle if you leave one, the other, or both of the fields empty.

 

It's a pain to have to accomodate every possibility in three sets of code (what's on the submit form, what query to pass to MySQL, and how to arrange and display the results returned). It's fun to get to the point that it does what I want it to do, but I've got a long long way before I've replicated even the barest of bare bones of what I wrote without so much as cracking a manual in FileMaker 2.1 back 12-13 years ago.

 

As long as I'm picking up PHP I think I should also learn how to use it to query FileMaker data sources hosted on FmServer.

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use