{ 2008 04 04 }

Dynamic SQL querys in ActionScript

I put together the start of a a dynamic query set builder today. It is still in the beginning stages, but it already has some real power.

PLAIN TEXT
Actionscript:
  1. var qs:QS = new QS().filter([{name__iexact: 'adsf', insider_id__notnull: '', age__gt: 18},{name: 'bob'}]).exclude([{age: 21}])
  2. trace(qs.output());

Outputs:

PLAIN TEXT
SQL:
  1. ...((age> :age1 AND insider_id IS NOT NULL AND LOWER(name) = LOWER(:name1)) OR (name = :name2)) AND NOT ((age = :age2))

With the bind parameters all taken care of for you in a nice object:

PLAIN TEXT
Actionscript:
  1. qs.parameters = {
  2. ':name2': 'bob',
  3. ':age1': 18,
  4. ':insider_id1': '',
  5. ':age2': 21,
  6. ':name1': 'adsf',
  7. };

Nice, eh?

The QS class file follows. I will be working on wiring everything together over the weekend.
Have fun.
If anyone has already gone down this road, I would love to hear from them.
`

PLAIN TEXT
Actionscript:
  1. package com.angrysprite.query {
  2.    
  3.     import com.angrysprite.errors.QuerySetError;
  4.    
  5.     public class QS {
  6.        
  7.         protected var filterGroups:Object;
  8.         protected var filters:Array;
  9.         protected var excludes:Array;
  10.         protected var bindParameters:Object;
  11.         protected var parameterCount:Object;
  12.         protected var queryTerms:Array = [
  13.             'exact',       // exact match "="
  14.             'iexact',      // case insensitive match
  15.             'contains',    // like match: col LIKE %parameter%
  16.             'icontains',   // case insensitive like match: lower(col) LIKE %parameter% (SQLite LIKE is case insensitive)
  17.             'gt',          // greater than match: col> parameter
  18.             'gte',         // greater than or equal match: col>= parameter
  19.             'lt',          // less than match: col <parameter
  20.             'lte',         // less than or equal match: col <= parameter
  21.             'in',          // in match: col IN (parameter1, parameter2, parameter3)
  22.             'startswith',  // starts with match: col LIKE parameter%
  23.             'istartswith', // case insensitive starts with match: col LIKE parameter% (SQLite LIKE is case insensitive)
  24.             'endswith',    // ends with match: col LIKE %parameter
  25.             'iendswith',   // case insensitive ends with match: col LIKE %parameter (SQLite LIKE is case insensitive)
  26.             'isnull',      // null match: col IS NULL
  27.             'notnull',   // is not null match: col IS NOT NULL
  28.             //'range',       //
  29.             //'year',        //
  30.             //'month','day', //
  31.             //'search',      //
  32.             //'regex',       //
  33.             //'iregex',      //
  34.         ];
  35.         public function QS() {
  36.             filterGroups = {filters: [], excludes: []};
  37.             bindParameters = new Object();
  38.             parameterCount = new Object();
  39.         }
  40.        
  41.         public function filter(filter:Array):QS {
  42.             for (var i:int = 0; i <filter.length; i++) {
  43.                 filterGroups.filters.push(parseFilters(filter[i]));
  44.             }
  45.             return this;
  46.         }
  47.        
  48.         public function exclude(filter:Array):QS {
  49.             for (var i:int = 0; i <filter.length; i++) {
  50.                 filterGroups.excludes.push(parseFilters(filter[i]));
  51.             }
  52.             return this;
  53.         }
  54.        
  55.         public function get parameters():Object {
  56.             return bindParameters;
  57.         }
  58.        
  59.         public function output():String {
  60.             var sql:String = "";
  61.             var filters:Array = new Array();
  62.             var excludes:Array = new Array();
  63.             for (var filterIndex:int = 0; filterIndex <filterGroups.filters.length; filterIndex++) {
  64.                 filters.push("(" + filterGroups.filters[filterIndex].join(" AND ") + ")");
  65.             }
  66.             for (var excludeIndex:int = 0; excludeIndex <filterGroups.excludes.length; excludeIndex++) {
  67.                 excludes.push("(" + filterGroups.excludes[excludeIndex].join(" AND ") + ")");
  68.             }
  69.             if (filters.length> 0) {
  70.                 sql += "(" + filters.join(" OR ") + ")";
  71.                 if (excludes.length> 0) {
  72.                     sql += " AND NOT (" + excludes.join(" OR ") + ")";
  73.                 }
  74.             } else if (excludes.length> 0) {
  75.                 sql += "NOT (" + excludes.join(" AND ") + ")";
  76.             }
  77.             return sql;
  78.         }
  79.        
  80.         protected function parseFilters(filter:Object):Array {
  81.             var sqlArray:Array = new Array();
  82.             for (var term:String in filter) {
  83.                 var sql:String = "";
  84.                 if (term.indexOf("__") == -1) {
  85.                     var bindParameter:String = getBindParameterKey(term);
  86.                     setBindParameter(bindParameter, filter[term]);
  87.                     sql += term + " = " + bindParameter;
  88.                 } else {
  89.                     sql += parseTerm(term, filter[term]);
  90.                 }
  91.                 sqlArray.push(sql);
  92.             }
  93.             return sqlArray;
  94.         }
  95.        
  96.         protected function parseTerm(term:String, value:Object):String {
  97.             var tokens:Array = term.split("__");
  98.             if (tokens.length != 2) {
  99.                 throw new QuerySetError("Invalid number of query terms");
  100.             }
  101.             var col:String = tokens[0];
  102.             var term:String = tokens[1];
  103.             var sql:String = '';
  104.             var bindParameter:String = getBindParameterKey(col);
  105.             setBindParameter(bindParameter, value);
  106.             switch (term) {
  107.                 case 'exact':
  108.                     sql = col + " = " + bindParameter;
  109.                     break;
  110.                 case 'iexact':
  111.                     sql = "LOWER(" + col + ") = LOWER(" + bindParameter + ")";
  112.                     break;
  113.                 case 'contains':
  114.                     sql = col + " LIKE " + bindParameter;
  115.                     break;
  116.                 case 'icontains':
  117.                     sql = col + " LIKE " + bindParameter;
  118.                     break;
  119.                 case 'gt':
  120.                     sql = col + "> " + bindParameter;
  121.                     break;
  122.                 case 'gte':
  123.                     sql = col + ">= " + bindParameter;
  124.                     break;
  125.                 case 'lt':
  126.                     sql = col + " <" + bindParameter;
  127.                     break;
  128.                 case 'lte':
  129.                     sql = col + ">= " + bindParameter;
  130.                     break;
  131.                 case 'in':
  132.                     sql = col + " IN (" + bindParameter + ")";
  133.                     break;
  134.                 case 'startswith':
  135.                     sql = col + " LIKE " + bindParameter + "%";
  136.                     break;
  137.                 case 'istartswith':
  138.                     sql = col + " LIKE " + bindParameter + "%";
  139.                     break;
  140.                 case 'endswith':
  141.                     sql = col + " LIKE %" + bindParameter;
  142.                     break;
  143.                 case 'iendswith':
  144.                     sql = col + " LIKE %" + bindParameter;
  145.                     break;
  146.                 case 'isnull':
  147.                     sql = col + " IS NULL";
  148.                     break;
  149.                 case 'notnull':
  150.                     sql = col + " IS NOT NULL";
  151.                     break;
  152.                 default:
  153.                     throw new QuerySetError('Invalid query term: ' + term);
  154.                     break;
  155.             }
  156.             return sql;
  157.         }
  158.        
  159.         protected function getBindParameterKey(bindParameter:String):String {
  160.             if (parameterCount[bindParameter]) {
  161.                 parameterCount[bindParameter]++;
  162.             } else {
  163.                 parameterCount[bindParameter] = 1;
  164.             }
  165.             return ":" + bindParameter + parameterCount[bindParameter].toString();
  166.         }
  167.        
  168.         protected function setBindParameter(key:String, value:Object):void {
  169.             if (typeof(value) == 'Array') {
  170.                 var a:Array = value as Array;
  171.                 bindParameters[key] = a.join(', ');
  172.             }
  173.             bindParameters[key] = value;
  174.         }
  175.     }
  176. }
  177.  
  178. /**
  179. *
  180. * var qs = new QS().filter({name__iexact: 'adsf', notnull__insider_id: '', gt__age: 18}).exclude({age: 21});
  181. *
  182. * ...WHERE (LOWER(name) = LOWER(:name1) AND insider_id IS NOT NULL AND age> :age1) AND NOT (age = :age2);
  183. * bindParameters = {
  184. *  ':name1': 'asdf',
  185. *  ':age1': 18,
  186. *  ':age2': 21
  187. * }
  188. * 
  189. **/

Follow any responses to this entry with the RSS 2.0 feed.

You can post a comment, or trackback from your site.