I generally consider myself a pretty competent programmer, but from
day to day it's not very exciting... Every once in a while, I do
something that just makes it difficult for me to sit in my chair
because I'm so jazzed about the end result... And it doesn't even have
to be anything really amazing. Sometimes it's just syntax.
So for a while now I've had a SQL-abstraction layer in my framework.
Probably most of you already know this. What you may not know is that
I've not been entirely happy with the syntax that's been required (in
the past to make it work)... When I started working on it I don't know
if CF6 was even an option (maybe it was, but I was still writing to
CF5 -- which I did for way too long and am now getting away from). So
I had tags:
<cfmodule template="#request.tapi.db.select()#" table="mytable"
return="qry">
<cfmodule template="#request.tapi.db.join()#" table="alt"
required="true" />
<cfmodule template="#request.tapi.db.filter()#" column="alt.x"
content="x" />
</cfmodule>
This would take the place of
<cfquery name="qry" datasource="#dsn.primary#"
username="#dsn.primary.username#"
password="#dsn.primary.password#">
select * from mytable
inner join alt on (alt.y = mytable.y)
where alt.x = <cfqueryparam value="x" cfsqltype="cf_sql_varchar">
</cfquery>
And while I preferred the syntax (tags give it a little more
structure), it wasn't any less code than writing the ad-hoc query, and
so I think a lot of people may not have understood what I was trying
to accomplish.
1) the abstraction layer means the app runs without modification on
any supported db platform, regardless of the morass of syntactical
variations between db providers
2) providing a default "datasource" and associating it by way of a
canonical dsn attribute in the parent tag means being able to easily
acomodate multiple datasources and knowing that the connection
credentials are going to be correct (i.e. you're not papering your
application with references to the datasource, username and password
combination).
So I think a lot of people were put off by the syntax because they
perceived it as being too verbose and/or over-complex... And after a
while, I implemented an alternative:
<cfset qry = request.tapi.sql.select("mytable","*","",
request.tapi.sql.join("alt",true,
request.tapi.sql.filter("alt.x","x")))>
Personally I thought this was better... certainly it was less verbose
(usually a plus), and particularly for simple queries it made a lot of
sense for making things easier, i.e. if I just wanted a single record
or everything out of the table, I could call something like
request.tapi.sql.select("mytable")
or
request.tapi.sql.getRecord("mytable")
and this would take the place of:
<cfquery name="qry" datasource="#dsn.primary#"
username="#dsn.primary.username#"
password="#dsn.primary.password#">
select * from mytable
where primary_key = <cfqueryparam value="#attributes.x#"
cfsqltype="cf_sql_varchar">
</cfquery>
So you can see how it would make life easier for some common tasks
which require very simple queries, but as you added join and filter
statements, the syntax became much more complex. The need to provide
arguments to functions in a specific order frequently resulted in
checking an argument's length and providing a default for blank
elements and then having 1-3 blank arguments in the middle of a
function call, and even I would find myself needing to look up in my
documentation to see whether I needed 2 or I needed 3 of those
arguments (not remembering the order of them). Plus, the location of
the dsn argument is totally arbitrary -- it's element 7 or so for a
select statement but only 4 for a delete, and sometimes there are
other arguments after it for features that weren't added until later.
And then there's the issue of nested filters, because this syntax,
while functional isn't easy to read:
request.tapi.sql.filter("col1",X,request.tapi.sql.filter("col2",Y,"","
="),"=")
This worked as an alternative
filters = request.tapi.sql.filter("col1",X,"","=")
filters = request.tapi.sql.filter("col2",Y,filters,"=")
Plus the functions gave me a number of new features like
sql.structToFilters(url) which was very handy (and responsible for the
bulk of the autmoation for my html table tools), but still wasn't as
elegant as I wanted... So when I started getting more serious about
CFCs (after figuring out how to resolve my problems with discovery --
which I still firmly believe is _BROKEN_ in every version of CF since
cfc's were introduced, with the possible exception of BlueDragon only
because of the cfmapping tag providing mappings tied to each
individual application), I created a datasource.cfc and in that I
bundled much of the functionality of the sql function library and
pre-loaded it with the dsn attribute, so no more padding the function
arguments to put in a dsn attribute (Woohoo!), so it was a little
better, although not much:
ds = request.tapi.getObject("datasource")
qry = ds.select("mytable","*","",ds.filter("col1",X,ds.filter("col2",Y
,"","="),"="))
Certainly closer to my goal of striking a balance between succinct
syntax and legibility.
The past month or so I've been working on a revision of my member
plugin, and then just last week I got this bug... I had to take
another stab at the interface for sql abstraction, and I must say, now
my head is full of ideas for improvements that are only possible when
the abstraction is created with objects. Plus I just love the
flexibility this new syntax gives me:
ds = request.tapi.getObject("datasource")
statement = ds.getStatement("select").init("mytable")
qry = statement.filter("X",x,"=").filter("Y",y,"=").execute()
Now that's beautiful to me... I didn't even realize just how beautiful
that was until today. And the dominant reason? I have a CFC I've been
working on at the office here at my day job, and I had a couple of
different methods for selecting data out of the same table... In other
places before, I've had wrapper methods which would accept several
predefined arguments and use those to create an array of filters, or
in some cases a function that returns an array of filters or that
accepts an array of filters as an argument, so that I could mix and
match the individual elements that go into a query, since they're so
frequently very similar... And they worked in spite of being somewhat
cryptic (which I was willing to live with) ... but now I don't have to
do that anymore -- now they can be very legible, almost if not
self-documenting and at the same time, more flexible than they've ever
been before. Case in point, here is the snippet from the CFC I've been
working on today (the entity names are not my idea -- legacy
database):
<cffunction name="getSectionStatement" access="private"
output="false">
<cfreturn ds.getStatement("select").init("pavilion_view","*","univid,
collid,pavid,pavsort,typesort,sort")>
</cffunction>
<cffunction name="getSections" access="public" output="false"
returntype="query">
<cfreturn getSectionStatement().execute()>
</cffunction>
<cffunction name="getSelectedSections" access="public" output="false"
returntype="query">
<cfargument name="univid" type="string" required="false" default="">
<cfargument name="collid" type="string" required="false" default="">
<cfargument name="pavid" type="string" required="false" default="">
<cfargument name="sectid" type="string" required="false" default="">
<cfset var statement = getSectionStatement()>
<cfif len(trim(univid & collid & pavid & sectid))>
<cfreturn statement.collectionFilter(arguments,"IN").execute()>
<cfelse>
<!--- return an empty query if nothing is selected --->
<cfreturn statement.filter("sectid","","IS NULL").execute()>
</cfif>
</cffunction>
There's even an alternative to execute() ... with the same statement
object you can statement.getSyntax() to return a string representation
of the same query that would be sent to your database using
statement.execute().
I'm not done by any stretch ... I tagged the framework at version 2.3
so I could rewrite the underlying engine so that the tags reference
the CFC's (instead of vice versa) to get even more features, like
statement.subquery(column,statement,comparison) or
statement.filterGroup().filter().filter() (grouping with the current
engine is handled but not very well)...
I just needed to share this while I was still euphoric about it. :)
s. isaac dealey 434.293.6201
new epoch : isn't it time for a change?
add features without fixtures with
the onTap open source framework
http://www.fusiontap.com
http://coldfusion.sys-con.com/author/4806Dealey.htm
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:5:194875
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/5
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:5
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.5
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
