{ Monthly Archives }

April 2008

Simple ActionScript ORM example

The code base is getting a lot closer to being ready for an alpha release, but here is a quick example of how to use the orm to get an array of all users with a first name containing the string 'matt'.

PLAIN TEXT
Actionscript:
  1. import com.angrysprite.data.Database;
  2. import com.angrysprite.data.models.DataModelFactory;
  3. import com.angrysprite.data.models.DataModelStatement;
  4. import com angrysprite.data.QuerySet;
  5. import com.angrysprite.events.DatabaseEvent;
  6. import com.angrysprte.events.DataModelStatementEvent;
  7. import com.angrysprite.models.User;
  8.  
  9. private var database:Database;
  10. private var dataModelFactory:DataModelFactory;
  11. private var users:Array = [];
  12.  
  13. private function init():void {
  14.     database = new Database(new File(File.applicationStorageDirectory.nativePath + File.seperator + "test.db"));
  15.     dataModelFactory = new DataModelFactory(database);
  16.     database.addEventListener(DataBaseEvent.DATABASE_READY, onDatabaseReady);
  17.     database.open();
  18. }
  19.  
  20. private function onDatabaseReady(event:DatabaseEvent):void {
  21.     var qs:QuerySet = new QuerySet().filter([{firstname__contains: 'matt'}]);
  22.     var dmStatement:DataModelStatement = dataModelFactory.getModel(User, qs);
  23.     dmStatement.addEventListener(DataModelStatementEvent.RESULT, userModelsReady);
  24.     dmStatement.execute();
  25. }
  26.  
  27. private function userModelsReady(event:DataModelStatementEvent):void {
  28.     users = users.concat(event.target.result);
  29. }

I think that is a lot easier and cleaner then using the straight air SQLite API.

Please take a look, and let me know if there are any questions or comments.

As soon as I get things a little more together, I will be creating a new google code project for this, and linking to it from here.

2008 04 10

Uncategorized

Comments (3)

Permalink

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. **/

2008 04 04

Actionscript
Air

Comments (2)

Permalink

ActionScript SQLite ORM

I have been playing with Air for a while now, and while I love that SQLite was included, I hate embedding SQL in my AS code. It's 2008, we should not be working this way. For anyone who does work in other languages the lack of an ORM (Object Relational Mapper) is a big one. I have been unable to find anyone who has already built one but have been unable to find one so I am going to have to through my hat into the ring and build it myself.

When not working in ActionScript I am playing with django (an awesome web framework), and I really like the database API they have built. It is clean and simple. I have started trying to build something similar in AS, but AS is a very different language than python, and it is still a work in progress. I will start posting code examples in the next few days as I get further.

Please let me know if there is any interest in this kind of project.

Thanks.

2008 04 03

Actionscript

Comments (1)

Permalink