[FX.php List] SQL strings transformed to FX.php functions

[DC]

Thanks for the insights guys.

I'm not suggesting that this transformation layer would be a perfect 
solution - dealing with joins and designing relationships and layouts 
would certainly be tricky. In fact, I'd really like to just switch on 
ODBC and talk to FMP that way.

But, it seems like a little SQL to FX transformer could be a useful and 
transparent addition to FX.

I think that a few things would be pretty easy to make - and those are 
the few things that would allow FMP to serve as the backend of some of 
these new MVC projects. One particular thing that these frameworks do is 
to help you automatically generate web browser interfaces for typical 
database tasks like Create, Retrieve, Update, and Delete (they call it 
CRUD).

I imagined porting those systems to work natively with FMP or FX and as 
I considered it, I realized that the best place for integration was 
going to be the SQL level itself. That's because most of them are 
abstracted and modularized at that level anyway and adding a new 
database is usually a matter of adding a class with the proper methods. 
In addition, allowing FX/FMP to talk SQL would open up a whole set of 
tools and libraries for web development. The trouble is, most of them 
are also designed with standard SQL in mind, not our clever little 
FMPXML mishmash.

As long as I can get FX to listen to a few rudimentary SQL calls that a 
simple CRUD framework requires, I can take advantage of the CRUD 
infrastructure. And that would mean a bunch of tasks offloaded to the 
web framework - web layouts for data entry and retrieval, URL generation 
and management, basic interface elements, etc... You know, the kind of 
things that IWP has right out of the box.

Joins are problematic but not too bad since in FMP you are able to 
pre-create joins as relationships. You even have to name them and it is 
possible to get a list of those names. So, FMP has even more expansion 
possibilities since you can just provide a list of relationship names to 
help transform any particular SQL join request. If there is no 
relationship that matches a requested join, then PHP could take over.

Since there is no comparable idea of the constraints of FMP layouts in 
SQL, it could be slow to just place all fields for a particular table 
and relationship set. With FMP layouts, each table would have a default 
layout and you would have to just throw a bunch of fields on that layout.

Further, joins are easily replicated in PHP using some array functions. 
Yes, with lots of fields on a layout and PHP doing joins it would be slow.

So, that all being said, I gather from the feedback that no one has 
tried this before...

I'd love to hear from any others who might have opinions and insights on 
this endeavor - are you lurking there somewhere? Are you thinking... 
this guy is crazy, or this guy is onto something? Would you like to help 
by suggesting the least painful way of parsing SQL into FX calls?

I'm thinking:
regexp or some kind of statemachine for parsing SQL
eval() or Function Handlers 
<http://us2.php.net/manual/en/ref.funchand.php> for calling the machine 
written FX code

Thanks,
dan

Steve Hannah had written:
> I share Greg's sentiments about practicality.  It seems that a  perfect 
> and complete solution is impossible but it still is possible  to 
> construct a partial solution that, coupled with some design  constraints 
> on the database, could be very effective.  A limited  solution would 
> work with greatest common divisors of the 2 types of  database.  The 
> problem with joins could be remedied in 2 ways:
> 1) Disallow joins
> 2) Predefine supported joins in the database.  Ie: Figure out which  
> join operations will be needed when the database is designed, and  
> create the appropriate relationships to support the join operations.   
> If an unsupported join is attempted, and exception could be thrown.
> 
> Steve
> On 8-Dec-05, at 10:23 AM, Greg Dykema wrote:
> 
>> On Dec 7, 2005, at 11:27 AM, DC wrote:
>>
>>> ...
>>> Build a transformation layer that would translate SQL statements  
>>> into a series of FX.php function calls and PHP array manipulations  
>>> to spit back the same format array that you would get if you were  
>>> talking directly to a SQL engine (probably modeled after MySQL).
>>> ...
>>> Does anyone have any thoughts on this? The ultimate goal is just  to 
>>> be able to use any PHP framework on top of FMP without building  new 
>>> databases in some SQL database. Basically, as a plugin to  FX.php it 
>>> would make FMP a viable backend for any number of PHP  projects.
>>>
>>
>> Most interesting SQL queries are going to involve joins. The only  way 
>> to efficiently implement a join in FileMaker is to use  relationships, 
>> and to support arbitrary queries you'd need a way to  define 
>> relationships on the fly, as well as build layouts using  related 
>> fields on the fly. Doesn't sound practical even if it were  possible.
>>
>>
>>
>> greg
>>
>> _______________________________________________
>> FX.php_List mailing list
>> FX.php_List at mail.iviking.org
>> http://www.iviking.org/mailman/listinfo/fx.php_list
>>
>>
> 
> _______________________________________________
> FX.php_List mailing list
> FX.php_List at mail.iviking.org
> http://www.iviking.org/mailman/listinfo/fx.php_list
>