New Mate extensions for using AIR and SQLite: “SQLService + SQLServiceInvoker”

October 4th, 2008

UPDATE [10/26/08]: Source of the extensions and the example as well has been moved to the project called ‘mate-examples’ on Google Code

If you are using Mate for application development based on Adobe AIR you may need an extension for using SQLite, because its in Mate currently not built in. It seems that only one extension by Miran Loncaric available, which depends on Eric Feminellas SQLService. Unfortunately this Mate extension lacks for using result or fault handlers, using prepared SQLStatements, parameters etc.

Therefore I’ve started to develop new extensions called SQLService and SQLServiceInvoker with all the needed stuff such as result and fault handling within an EventMap, (re-)using prepared statements, “simple” SQL texts, named parameters etc.

BTW: Theo Hultberg has already opened a very cool project on Google Code for free examples based on Mate, so I hope that the new extensions will be added as well.

Screen shot of the AIR example

Code example for using the extensions

Here is the most important part – the MainEventMap which is an EventMap for handling all SQLite services.

MainEventMap.mxml ( Download code )

  1. <?xml version="1.0" encoding="utf-8"?>
  2. <!–
  3. *
  4. * Mate extensions called "SQLService" and "SQLServiceInvoker" for using AIR and SQLite
  5. *
  6. * @author   Jens Krause [ www.websector.de/blog ]
  7. *
  8. –>
  9. <EventMap
  10.     xmlns:mx="http://www.adobe.com/2006/mxml"
  11.     xmlns="http://mate.asfusion.com/"
  12.     xmlns:air="de.websector.mate.extensions.air.*"
  13.     xmlns:manager="example.models.manager.*"
  14.     xmlns:data="flash.data.*">
  15.     <mx:Script>
  16.         <![CDATA[
  17.             import example.models.presentation.MainViewModel;
  18.             import example.views.MainView;
  19.             import example.models.domain.MainModel;
  20.             import example.views.GenericFaultHandler;
  21.             import example.events.UserEvent;
  22.             import example.models.vo.UserVO;
  23.             import com.asfusion.mate.events.UnhandledFaultEvent;
  24.             import mx.events.FlexEvent;
  25.  
  26.  
  27.            
  28.         ]]>
  29.     </mx:Script>
  30.  
  31.     <Debugger
  32.         level="{ Debugger.ALL }"
  33.         />
  34.  
  35.     <air:SQLService id="sqlService"
  36.         databasePath="{ SQLManager.DB_PATH }"
  37.         />
  38.        
  39.     <manager:SQLManager id="sqlManager" /> 
  40.  
  41.     <!–
  42.         Flex Events
  43.     –>
  44.    
  45.  
  46.     <EventHandlers type="{FlexEvent.PREINITIALIZE}">
  47.         <ObjectBuilder generator="{ MainModel }" />  
  48.     </EventHandlers>
  49.  
  50.  
  51.    
  52.     <!–
  53.         Create a table using SQLServiceInvokers attribute called sql to set a SQL text
  54.     –>
  55.     <EventHandlers type="{FlexEvent.APPLICATION_COMPLETE}">
  56.             <air:SQLServiceInvoker
  57.                 instance="{ sqlService }"
  58.                 sql="CREATE TABLE IF NOT EXISTS users (userId INTEGER PRIMARY KEY AUTOINCREMENT, firstName TEXT, lastName TEXT)"
  59.                  >
  60.                  <air:resultHandlers>
  61.                     <EventAnnouncer
  62.                         generator="{ UserEvent}"
  63.                         type="{ UserEvent.GET_ALL }"
  64.                         />
  65.                  </air:resultHandlers>
  66.             </air:SQLServiceInvoker>
  67.     </EventHandlers>
  68.    
  69.     <!–
  70.         Get all stored user from database using a prepared SQLStatement, which is created by SQLManager.
  71.     –>
  72.     <EventHandlers type="{ UserEvent.GET_ALL }">
  73.             <air:SQLServiceInvoker
  74.                 instance="{ sqlService }"
  75.                 statement="{ sqlManager.getAllUsers }"
  76.                  >
  77.                 <air:resultHandlers>
  78.                     <MethodInvoker
  79.                         generator="{ MainModel }"
  80.                         method="setUserData"
  81.                         arguments="{ resultObject }"
  82.                         /> 
  83.                 </air:resultHandlers>                 
  84.             </air:SQLServiceInvoker>
  85.     </EventHandlers>
  86.  
  87.     <!–
  88.         Delete selected user from database using a prepared SQLStatement, which is created by SQLManager.
  89.     –>
  90.     <EventHandlers type="{ UserEvent.DELETE }">
  91.             <air:SQLServiceInvoker
  92.                 instance="{ sqlService }"
  93.                 statement="{ sqlManager.deleteUser }"
  94.                 parameters="{[ event.userId ]}"
  95.                  >
  96.                 <air:resultHandlers>
  97.                     <EventAnnouncer
  98.                         generator="{ UserEvent}"
  99.                         type="{ UserEvent.GET_ALL}"
  100.                         />
  101.                 </air:resultHandlers>         
  102.             </air:SQLServiceInvoker>
  103.     </EventHandlers>
  104.  
  105.     <!–
  106.         Add new user to database using a prepared SQLStatement, which is created by SQLManager.
  107.     –>
  108.     <EventHandlers type="{ UserEvent.ADD }">
  109.             <air:SQLServiceInvoker
  110.                 instance="{ sqlService }"
  111.                 statement="{ sqlManager.addUser }"
  112.                 parameters="{[ event.userVO.firstName, event.userVO.lastName ]}"
  113.                  >
  114.                 <air:resultHandlers>
  115.                     <EventAnnouncer
  116.                         generator="{ UserEvent}"
  117.                         type="{ UserEvent.GET_ALL }"
  118.                         />
  119.                 </air:resultHandlers>         
  120.             </air:SQLServiceInvoker>
  121.     </EventHandlers>
  122.  
  123.     <!–
  124.         Update selected user from database using a prepared SQLStatement, which is created by SQLManager.
  125.     –>
  126.     <EventHandlers type="{ UserEvent.UPDATE }">
  127.             <air:SQLServiceInvoker
  128.                 instance="{ sqlService }"
  129.                 statement="{ sqlManager.updateUser }"
  130.                 parameters="{[ event.userVO.firstName, event.userVO.lastName, event.userVO.userId ]}"
  131.                  >
  132.                 <air:resultHandlers>
  133.                     <EventAnnouncer
  134.                         generator="{ UserEvent}"
  135.                         type="{ UserEvent.GET_ALL }"
  136.                         />
  137.                 </air:resultHandlers>         
  138.             </air:SQLServiceInvoker>
  139.     </EventHandlers>
  140.        
  141.    
  142.     <!–
  143.         Handling for fault event may dispatched by SQLServiceInvoker or other services
  144.     –>
  145.     <EventHandlers type="{ UnhandledFaultEvent.FAULT }">
  146.         <MethodInvoker generator="{ GenericFaultHandler }"
  147.             method="handleFault"
  148.             arguments="{event.fault}" />
  149.     </EventHandlers>           
  150.  
  151.  
  152.  
  153. </EventMap>

I’m a big fan of using presentation models, so you will have a further example for using it as well ;-) . For using presentation models within Mate check out the great example called ‘document based’ by Theo Hultberg and his very detailed post on Google Code.

To-dos

Download full source

Full source of the AIR example above including the new extensions called SQLService and SQLServiceInvoker MateAIRSQLiteExample.zip

[UPDATE] Full source of the extensions and the example as well has been moved to the project called ‘mate-examples’ on Google Code[/UPDATE]

Acknowledge

Posted by sectore Filed in AIR, Open Source
23 Comments »

23 Responses to “New Mate extensions for using AIR and SQLite: “SQLService + SQLServiceInvoker””

  1. mloncaric Says:
    October 5th, 2008 at 12:18 pm

    Wow, this is what I call real extension, mine was just one useless experiment and was never ment to be used in real applications.

    I’ll try it as soon as possible and if I find something I’ll let you know at http://mate.asfusion.com/forums/topic.php?id=169

    ..and BTW this is mine website: http://miran.nonlogic.org/
    and not http://www.freewebtown.com/mloncaric/ which shouldn’t be online at all :)

  2. Extension SQLite pour Mate | For Evolution On AIR - Toutes les applications et l'actualité d'Adobe AIR en un seul blog Says:
    October 5th, 2008 at 7:50 pm

    [...] Voici l’adresse : WS-Blog [...]

  3. Laura Says:
    October 13th, 2008 at 12:30 am

    Hi Jens,
    It would be great if you can get in touch with Theo to get access to the new extensions repository and put the code there. (if you want, of course).
    :)

  4. sectore Says:
    October 13th, 2008 at 8:24 pm

    Of course, Laura ;) Theo is already contacted and within the next few days I will move all files to the mate-examples project on Google Code.

    That’s why I love open source :-)

    -Jens

  5. Andrew Muller Says:
    October 21st, 2008 at 8:34 am

    This is really nice work Jens, I’m using this extension in an AIR project ATM – suggestion for you, with INSERT statements it could be useful to be able to utilise the lastInsertRowID from the SqlConnection in the SQLService class, since it’s there to save on making an extra trip to get that value – what do you think?

  6. sectore Says:
    October 21st, 2008 at 9:29 am

    Andrew, thats a good point! I will check it and update as soon as possible.

    BTW: As mentioned before I’m going to move the extension to the Google Code project called “mate-examples”. So anybody who is interested can make an improvement directly. That’s called real open source ;)

    -Jens

  7. Thomas Says:
    October 23rd, 2008 at 6:53 am

    Gute Arbeit Jens, great work! Looking forward to future releases. Are you planning to create a SWC as well?
    Thanks! Thomas

  8. sectore Says:
    October 23rd, 2008 at 8:16 am

    Thomas, the extension will be available as SWC at mate-examples too ;) .

    -Jens

  9. sitron Says:
    November 20th, 2008 at 5:02 pm

    Hello!
    thanks for this extension.
    is there a way to share a sqlService between multiple EventMaps?

  10. WS-Blog » Swiz example based on Adobe AIR and SQLite Says:
    January 7th, 2009 at 2:39 pm

    [...] …to dive into Swiz. So I ported my latest published example using Mate into Swiz. [...]

  11. Jason Rice Says:
    January 26th, 2009 at 9:31 pm

    The google code page does *not* have the source code available anymore. Can this be remedied?

  12. Jason Rice Says:
    January 26th, 2009 at 9:33 pm

    Actually it only seemed to work by taking the link to it from the project description page. Going to the source tab by itself never worked

  13. Jason Rice Says:
    January 26th, 2009 at 10:33 pm

    Real question: Do you plan on adding support for Async connections?

  14. sectore Says:
    January 27th, 2009 at 3:04 pm

    @Jon: At the moment I don’t plan to add an asynchronous support.

    Have you tried to checkout the source using SVN? It should be available using the following command: svn checkout http://mate-examples.googlecode.com/svn/trunk/ mate-examples-read-only.

    Or you can check the code using your Browser (without Google’s interface): http://mate-examples.googlecode.com/svn/trunk/examples/airsqlite/

    -Jens

  15. Andrew Muller Says:
    January 29th, 2009 at 11:35 pm

    Hi Jens

    Have you thought to update the extension to include an argument for the encryptionKey parameter for the connections open() method for encrypted databases for AIR 1.5?

    I’m hacking the original source for now for an application I’m using your extension on.

    Thanks, Andrew

  16. sectore Says:
    January 30th, 2009 at 11:26 am

    @Andrew: At the moment I don’t plan any improvements on this extensions.

    Feel free to add your “hack” to the original extension on Google Code. It’s open source ;) Thanks!

    -Jens

  17. Tom Says:
    February 2nd, 2009 at 2:45 pm

    When I try to start the application it quits without even showing the main screen… I’m using AIR 1.5. Any idea?

  18. jurgen Says:
    February 4th, 2009 at 3:27 pm

    Thank you for this great example..

    Regarding the SQLManager .. how would I define the sql if I wanted to use WHERE .. LIKE instead of = ?
    Tried adding % to the string but I always get errors.

    Thanks in advance!

  19. sectore Says:
    February 5th, 2009 at 9:51 am

    @jurgen: Have you tried something like this?
    sql = "SELECT anyvalue FROM anytable WHERE anyvalue LIKE 'a%'"
    Note the ‘ ‘ for the wildcard definition within the SQL statement.

    -Jens

  20. Ben Says:
    February 17th, 2009 at 4:09 am

    Interesting stuff! Just got into Mate today after using UM Cairngorm for the past 6 months. I too plan to have a go at hacking your code to put in the transaction management, which is really easy. I don’t think Mate’ll be appropriate for the sorts of things I’m doing with SQLite, but an interesting challenge nonetheless.

    I know it’s possible, but I’m yet to figger out why; It doesn’t seem like leaving the connection open from multiple operations is actually any faster than closing it every time. I was testing it on a coupla hundred inserts/deletes rather than selects (before I had transactions working), so maybe there’s benefit purely on selects…?

  21. Andrew Muller Says:
    May 3rd, 2009 at 5:19 am

    @jurgen The problem is using the ‘?’ index parameter next to the ‘%’ character – that’s why the error keeps getting thrown.

    The solution is to concatenate the parameter with the character like this, where ‘||’ is the concatenation operator in SQLite:

    sql = “SELECT anyvalue FROM anytable WHERE anyvalueLIKE ? || ‘%’”

    Andrew

  22. [AIR] Mate ???extensions for SQLite « Lab of Chowky Says:
    August 8th, 2009 at 10:02 am

    [...] http://www.websector.de/blog/2008/10/04/new-mate-extensions-for-using-air-and-sqlite-sqlservice-sqls... Source: [...]

  23. to domain name Says:
    May 3rd, 2010 at 4:47 pm

    hi guys…

    hi guysI would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well and i have start my own blog now, , thanks for your effort…

Leave a Reply

Follow sectore on Twitter