Monthly Archives: June 2018

Introducing the C.R.A.P. Framework for ColdFusion

The CRUD Access Protocol framework enables you to quickly and easily make your ColdFusion data available to web services securely.

It’s a framework for people who hate frameworks.

Let’s say, for instance, that you have 20 data tables in ORACLE that you want to be able to create new records, edit existing records, logically delete records, and read records (with pagination) through a javascript-based GUI and common interface. It also enables you to alias database column names in the event that an angry DBA decides to start renaming columns on you!

My framework can make this back-end process simple!

You simply need to generate a CFC that extends an AbstractGateway class and has the following properties:

  • tableName – the name of the table in the database
  • pkField – describes the primary key field, and whether it’s an autonumber/identity/sequence
  • relationships – (optional), describes the join conditions (supports inner and outer)
  • permissions – (optional), describes which user roles will be granted access to the create/read/update/delete methods.
  • columnmappings – describes each column, it’s data type, whether it’s writeable, and whether it should be encrypted in the database

A typical piece of CRAP would resemble the following:

<cfcomponent  output="false" 
              extends="components.components.gateway.AbstractGateway">

    <cfset this.tableName = "USA_CLAS">
    
    <cfset this.pkField = {
	 "columnName" = "#this.tableName#.CLASS_CODE",
	 "fieldName" = "code",
	 "type" = "VARCHAR"
    }>

    <cfset this.relationships = [
      {
	field1 = "#this.tableName#.CLASS_GROUP",
	field2 = "USA_CLAS_GROUP.GROUP_ID"
      }
     ]>

     <cfset this.permissions = {
  	"create" = "admin",
  	"read"   = "authenticated",
  	"update" = "admin",
  	"delete" = "admin"
     }>

    <cfsavecontent variable="this.columnMappings">
	 <cfoutput>
	 {
	   "code": {
		  "columnName": "#this.tableName#.CLASS_CODE",
		  "type": "VARCHAR",
		  "persist": true
	   },
	   "type": {
		  "columnName": "#this.tableName#.CLASS_TYPE",
		  "type": "VARCHAR",
		  "persist": true
	   },
	   "description": {
		  "columnName": "#this.tableName#.CLASS_DESC",
		  "type": "VARCHAR",
		  "persist": true
	   }
	   "group": {
		  "columnName": "#this.tableName#.CLASS_GROUP",
		  "type": "NUMERIC",
		  "persist": true
	   },
	   "createdBy": {
		  "columnName": "#this.tableName#.CREATED_BY",
		  "type": "VARCHAR",
		  "persist": true
	   },
	   "createdDate": {
		  "columnName": "#this.tableName#.CREATED_DATE",
		  "type": "TIMESTAMP",
		  "persist": true
	   },
	   "lastModifiedDate": {
		  "columnName": "#this.tableName#.LAST_MODIFIED_DATE",
		  "type": "TIMESTAMP",
		  "persist": true
	   },
	   "lastModifiedBy": {
		  "columnName": "#this.tableName#.LAST_MODIFIED_BY",
		  "type": "VARCHAR",
		  "persist": true
	   },
	   "active": {
		  "columnName": "#this.tableName#.ACTIVE_FLAG",
		  "type": "VARCHAR",
		  "persist": true
	   },
	   "groupName" : {
	      "columnName": "USA_CLAS_GROUP.GROUP_DESC",
	      "type": "VARCHAR",
	      "persist": false
	   }
    }
	</cfoutput>
    </cfsavecontent>

    <cfset this.columnMappings = deserializeJson(this.columnMappings)>

</cfcomponent>

After dropping your CRAP onto the server, you can make advanced data requests like the following:

 <cfset local.results = mycrap.read(
    start = 0,
    limit = 5,
    filters = [
      { property = "active", value="Y" },
      { property = "description", value="test", operator="like"}
    ],
    sorters = [
      { property = "code", direction="ASC" }
    ]
)>

Of course, you can also easily perform inserts, updates, and deletes…

  <cfset local.rec = mycrap.create("code" = "hey", type="example")>

Note that you only have to pass data to the create/update functions that has changed.

So keep an eye out for my C.R.A.P. — I’ll be posting it to Github within the next week after I, uh… clean it up just a little bit and enhance it with field-level and row-level security!