[CFCDev] ANNOUNCEMENT: CFSQLTool Version 1.6p1 Release -- Long posting

Joseph Flanigan Tue, 28 Sep 2004 04:09:04 -0700

ANNOUNCEMENT: CFSQLTool Version 1.6p1 Release

This release of Switch_box CF SQLTool, V1.6p1, has many fixes and enhancements. The main feature of
this release is the integration of table wrapper functions and CFC instance variables. The reason for
the long post is to demonstrate the technical solution of the wrapper approach. A wrapper CFC is
a set of CRUD functions that deliver and receive data between the application and the database.
Typically, a wrapper is for one table or view. In the application, the wrapper becomes an object
that encapsulates the wrapper functions and data structures. The attached code demonstrates the
wrapper CFC and an application using the wrapper object.

The demonstration code uses a Contacts table with 4 columns, ContactID, Name, Address and DateCreate.
There are demostration 2 files. Contacts.cfm is an application that uses the Contacts wrapper to provide
the user with forms for listing, selecting, updating, and adding Contacts information. The Contacts_wrap.cfc has
all the CRUD functions plus the basic data forms that Contacts.cfm uses for data collection and display.

All the demonstration code was dynamically generated with the Switch_box CF SQLTool by using
SQL Server meta-data schemas as the model for object names, variable names and SQL function building.
By using the data model to drive the application data layers, the data binding between the
two services is simplified allowing precise data transactions.

As you review the code you will notice variable names beginning with a $, dollar sign.
Think of the $ as a data type much like using str in front of strName. In this case,
the data type is of type CFC instance variable. The significance of using $, is because
the CFC makes no distinction between different kinds of variables with any declarative
built-in functions. As it turns out, the $ becomes very handy for program code generation
and hand coding as well because it is an easy marker to follow through the logic. For example,
if we create an object, objContact, that invokes the Contact_wrap.cfc, references to
objContact.$Address means the instance variable named $Address in object contact.

The integration of wrappers and instance variables with application logic and database services is best
labeled as single point variable store using pass-by-reference access for all functions.
This means that a variable value has to be stored only once and all accesses to the variable
are by pointing to the value, not by passing the value. The approach CF SQLTool uses to
implement this technique is that the namespace data storage location for a data model variable is defined
when the wrapper for the data model is opened as an object. In ColdFusion jargon,
each table has a wrapper CFC reflecting the table's data model where table column names become
CFC instance variable names of the "this" scope. Because CF can pass data structures to cffunction
as pass-by-reference, variable data can be stored when the value is available and simply calling
the function without parameters is all that is necessary. This technique is demonstrated in the code.
Look at the beginning of the CFC to see the instance variables name spaces set. In each CRUD function is
an attribute named theParam with it's default value referencing the this scope.
Further, any queryparam that requires data only has to reference a value by stating the variable
name for the this scope name. Rarely does data have to be passed into the CRUD function because the
query data is already stored in the CFC's scope.

More about this release can be found at: http://www.switch-box.org/CFSQLTool/doc_1_6_ReleaseNotes.html

CF SQLTool is Open Source, GPL, no charge. The download is about 218K, so there is plenty of code. In the release notes
are some comments about a new wizard that I am considering for the tool -- object services.

Joseph

============== Comtacts.cfm ==================================
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>



<head>
<title>objContacts</title>
</head>
<body>
<cfparam name="Request.IN.DSN" default="SwitchBoxSQLTool">
<cfparam name="Request.IN.SMV" default="New">


<cfinvoke component="Contacts_wrap" method="init" returnvariable="objContacts">
<cfset objContacts.SetInit(Request.IN.DSN) >



<cfoutput>

#objContacts.ActorIn()#


<cfset keylist = StructKeyList(objContacts)>
      <cfloop index="key" list="#keylist#">
           <cfif Mid(key, 1, 1) EQ "$" >
                <cfset RequestKey = RemoveChars(key, 1, 1) >
                <cfset objContacts[key] = Request.IN[RequestKey] >
                <cfset Request.OUT[RequestKey] = Request.IN[RequestKey] >
           </cfif >
</cfloop>

      <h2><font color="Blue">objContacts</font> Wrapper Application</h2>
<cfswitch _expression_="#Request.IN.SMV#">
<cfcase value="New">
      <h3>New Form       </h3>
      #objContacts.ActorOutIn()#
      #objContacts.Form()#
      #objContacts.ActorOut()#
      <input type="submit" name="smv" id="smv" value="Insert">
      <input type="submit" name="smv" id="smv" value="List">
      #objContacts.EndForm()#
</cfcase>

<cfcase value="Insert">
      <h3>Insert</h3>
<cfset objContacts.Create() >
      #objContacts.Form()#
      #objContacts.ActorOut()#
      <input type="submit" name="smv" id="smv" value="Update">
      <input type="submit" name="smv" id="smv" value="List">
      #objContacts.EndForm()#
</cfcase>

<cfcase value="Select">
      <h3>Select</h3>
      <cfset objContacts.Read() >
      #objContacts.Form()#
      #objContacts.ActorOut()#
      <input type="submit" name="smv" id="smv" value="Update">
      <input type="submit" name="smv" id="smv" value="Delete">
      <input type="submit" name="smv" id="smv" value="List">
      <input type="submit" name="smv" id="smv" value="New">
      #objContacts.EndForm()#
</cfcase>

<cfcase value="List">
      <h3>List</h3>
      #objContacts.Form()#
      <cfset #objContacts.List()# >
      <input type="submit" name="smv" id="smv" value="Select">
      <input type="submit" name="smv" id="smv" value="Delete">
      <input type="submit" name="smv" id="smv" value="New">
      #objContacts.EndForm()#
</cfcase>

<cfcase value="Update">
      <h3>Update</h3>
      <cfset objContacts.Update() >
      #objContacts.Form()#
      #objContacts.ActorOut()#
      <input type="submit" name="smv" id="smv" value="Update">
      <input type="submit" name="smv" id="smv" value="List">
      #objContacts.EndForm()#
</cfcase>

<cfcase value="Delete">
      <h3>Deleted</h3>
      <cfset objContacts.Delete() >
      <cfset thisFile = GetFileFromPath(GetCurrentTemplatePath()) >
      <cfset Request.IN.SMV = "List" >
      <cfset getpagecontext().include(thisFile) >
</cfcase>
</cfswitch>
</cfoutput>

</body>
</html>
============== Contacts.cfm ==================================

============== Contacts_wrap.cfc ==============================

<cfcomponent
     displayname="Contacts_wrap"
     extends="com.switch_box.framework.udf"
     hint="Comments: Demo contacts CFC; Title: CFC CRUD Wrapper; DSN: JosephTest; Table: dbo.Contacts; Version: {ts '2004-09-28 08:44:54'}; Iteration: 2">



<cfscript>
     this.$ContactsID = "";      //Null: No , Type: int, Char Len:, Num Pre: 10, Radix: 10, Scale: 0, Time Prec:
     this.$Name = "";      //Null: No , Type: varchar, Char Len:50, Num Pre: , Radix: , Scale: , Time Prec:
     this.$Address = "";      //Null: YES, Type: varchar, Char Len:50, Num Pre: , Radix: , Scale: , Time Prec:
     this.$DateCreate = "";      //Null: No , Type: datetime, Char Len:, Num Pre: 23, Radix: , Scale: 3, Time Prec: 3
</cfscript>


<cffunction name="init" returntype="struct" hint="Init for the CFC" output="no">
     <cfreturn this>
</cffunction>


<cffunction name="SetInit" returntype="boolean" hint="SetInit for the CFC" output="no">
     <cfargument name="DSN" type="string" default="">
     <cfset this.DSN = Arguments.DSN >
     <cfreturn TRUE>
</cffunction>


<cffunction name="InstanceActorIn" returntype="void" output="no">
     <cfscript>
          this.$ContactsID = Request.IN.ContactsID;
          this.$Name = Request.IN.Name;
          this.$Address = Request.IN.Address;
          this.$DateCreate = Request.IN.DateCreate;
     </cfscript>
<cfreturn>
</cffunction>



<cffunction name="ActorIn" returntype="void" output="yes">
     <cfparam name="Request.IN.ContactsID" default="">
     <cfparam name="Request.IN.Name" default="">
     <cfparam name="Request.IN.Address" default="">
     <cfparam name="Request.IN.DateCreate" default="">
<cfreturn>
</cffunction>



<cffunction name="ActorOutIn" returntype="void" output="yes">
     <cfparam name="Request.OUT.ContactsID" default="#Request.IN.ContactsID#">
     <cfparam name="Request.OUT.Name" default="#Request.IN.Name#">
     <cfparam name="Request.OUT.Address" default="#Request.IN.Address#">
     <cfparam name="Request.OUT.DateCreate" default="#Request.IN.DateCreate#">
<cfreturn>
</cffunction>



<cffunction name="Create" returntype="boolean">
     <cfargument name="theParams" type="struct" default="#this#" required="yes" >
     <cfargument name="DSN" type="string" default="#this.DSN#">
      <cfset var qContacts_INS = "" >

     <cfquery name="qContacts_INS" datasource="#Arguments.DSN#" maxrows=-1 >
          BEGIN TRANSACTION
          INSERT INTO dbo.Contacts
               (
               [Name],
               [DateCreate]
               )
               VALUES
               (
               <cfqueryparam value="#theParams.$Name#" cfsqltype="CF_SQL_varchar" >,
               <cfqueryparam value="#theParams.$DateCreate#" cfsqltype="CF_SQL_DATE" >
               )

          COMMIT TRAN
     </cfquery>
<cfreturn TRUE>
</cffunction>




<cffunction name="Update" returntype="boolean">
     <cfargument name="theParams" type="struct" default="#this#" required="yes" >
     <cfargument name="DSN" type="string" default="#this.DSN#">
      <cfset var qGet_Contacts_UPD = "" >

     <cfquery name="qGet_Contacts_UPD" datasource="#Arguments.DSN#" maxrows=-1 >
          BEGIN TRANSACTION
          UPDATE dbo.Contacts
          SET
               [Name] = <cfqueryparam value="#theParams.$Name#" cfsqltype="CF_SQL_varchar" >,
               [Address] = <cfqueryparam value="#theParams.$Address#" cfsqltype="CF_SQL_varchar" NULL="#isNull(theParams.$Address)#" >,
               [DateCreate] = <cfqueryparam value="#theParams.$DateCreate#" cfsqltype="CF_SQL_DATE" >

          WHERE
               (
               ContactsID = <cfqueryparam value="#theParams.$ContactsID#" cfsqltype="CF_SQL_INTEGER" >
               )
          COMMIT TRAN
     </cfquery>
<cfreturn TRUE >
</cffunction>




<cffunction name="Delete" returntype="boolean" >
     <cfargument name="theParams" type="struct" default="#this#" required="yes" >
     <cfargument name="DSN" type="string" default="#this.DSN#">
     <cfset var qGet_Contacts_DEL = "" >

     <cfquery name="qGet_Contacts_DEL" datasource="#Arguments.DSN#" >
          BEGIN TRANSACTION
          DELETE
          FROM dbo.Contacts
          WHERE ContactsID = <cfqueryparam value="#theParams.$ContactsID#" cfsqltype="CF_SQL_INTEGER" >
          COMMIT TRAN
     </cfquery>
<cfreturn TRUE>
</cffunction>




<cffunction name="List" returntype="void" output="yes" >
      <cfargument name="MaxRow" type="string" default="-1" >
      <cfargument name="DSN" type="string" default="#this.DSN#">
      <cfset var qGet_Contacts_LST = "" >
      <cfquery name="qGet_Contacts_LST" datasource="#Arguments.DSN#" maxrows="#Arguments.MaxRow#" >
           SELECT [Name],[Address],[DateCreate]
           FROM dbo.Contacts
      </cfquery>

<cftable query="qGet_Contacts_LST" maxrows="#Arguments.MaxRow#" colheaders htmltable>
           <cfcol    header="Name"   text="<input type='radio' name='ContactsID' value='#qGet_Contacts_LST.ContactsID#'> #Name#" align="LEFT">
           <cfcol    header="Address"   text="#Address#" align="LEFT">
           <cfcol    header="DateCreate"   text="#DateCreate#" align="LEFT">
      </cftable>
<cfreturn>
</cffunction>





<cffunction name="ReadAll" returntype="query">
     <cfargument name="where" type="string" default="WHERE 0=0">
     <cfargument name="DSN" type="string" default="#this.DSN#">
      <cfset var qGet_Contacts_SELECT = "" >

     <cfquery name="qGet_Contacts_SELECT" datasource="#Arguments.DSN#" maxrows=-1>
          SELECT TOP 100 PERCENT ContactsID,Name,Address,DateCreate
          FROM dbo.Contacts
           #PreserveSingleQuotes(Arguments.Where)#
     </cfquery>
     <cfif qGet_Contacts_SELECT.recordcount eq 1 >
          <cfset Request.OUT.ContactsID = qGet_Contacts_SELECT.ContactsID >
          <cfset Request.OUT.Name = qGet_Contacts_SELECT.Name >
          <cfset Request.OUT.Address = qGet_Contacts_SELECT.Address >
          <cfset Request.OUT.DateCreate = qGet_Contacts_SELECT.DateCreate >
     </cfif>
<cfreturn qGet_Contacts_SELECT >
</cffunction>



<cffunction name="ReadByContactID" returntype="query">
     <cfargument name="theParams" type="struct" default="#this#" required="yes" >
     <cfargument name="DSN" type="string" default="#this.DSN#">
      <cfset var qGet_Contacts_SEL = "" >

     <cfquery name="qGet_Contacts_SEL" datasource="#Arguments.DSN#" maxrows="-1">
          SELECT TOP 100 PERCENT [ContactsID],[Name],[Address],[DateCreate]
          FROM dbo.Contacts
          WHERE ( [ContactsID] = <cfqueryparam value="#theParams.$ContactsID#" cfsqltype="CF_SQL_INTEGER" >)
     </cfquery>
     <cfif qGet_Contacts_SEL.recordcount eq 1 >
          <cfset Request.OUT.ContactsID = qGet_Contacts_SEL.ContactsID >
          <cfset Request.OUT.Name = qGet_Contacts_SEL.Name >
          <cfset Request.OUT.Address = qGet_Contacts_SEL.Address >
          <cfset Request.OUT.DateCreate = qGet_Contacts_SEL.DateCreate >
     </cfif>
<cfreturn qGet_Contacts_SEL >
</cffunction>



<cffunction name="ActorOut" returntype="void" output="yes">
     <input type="hidden" name="bot_table" value="Contacts">
<cfoutput>
     <input type="hidden" name="ContactsID" value="#Request.OUT.ContactsID#">
     <input type="hidden" name="bot_formfield" value="ContactsID">
     <input type="hidden" name="bot_formfield" value="ContactsID,Name,Address,DateCreate">

<table>
     <tr>
          <td align="right">
               ContactsID
          </td>
          <td align="left">
               <input type="text" name="ContactsID" value="#Request.OUT.ContactsID#">
          </td>
     </tr>     <tr>
          <td align="right">
               Name
          </td>
          <td align="left">
               <input type="text" name="Name" value="#Request.OUT.Name#">
          </td>
     </tr>     <tr>
          <td align="right">
               Address
          </td>
          <td align="left">
               <input type="text" name="Address" value="#Request.OUT.Address#">
          </td>
     </tr>     <tr>
          <td align="right">
               DateCreate
          </td>
          <td align="left">
               <input type="text" name="DateCreate" value="#Request.OUT.DateCreate#">
          </td>
     </tr>
</table>
</cfoutput>
<cfreturn>
</cffunction>



<cffunction name="form" returntype="string" output="yes">
     <cfargument name="action" type="string" default="#cgi.script_name#">
     <cfargument name="method" type="string" default="post">
     <cfargument name="target" type="string" default="_self">
     <cfset var strReturn = "<form action='' method='#Arguments.Method#' target='#Arguments.target#' >" >
     <cfreturn strReturn >
</cffunction>



<cffunction name="endform" returntype="string" output="yes">
     <cfreturn "</form>" >
</cffunction>


</cfcomponent>

============== Contacts_wrap.cfc ==============================

[CFCDev] ANNOUNCEMENT: CFSQLTool Version 1.6p1 Release -- Long posting

Reply via email to