New Mate extensions for using AIR and SQLite: "SQLService + SQLServiceInvoker"
Posted on October 4, 2008
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.
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 </mx:Script>
28
29 <Debugger
30 level="{ Debugger.ALL }"
31 />
32
33 <air:SQLService id="sqlService"
34 databasePath="{ SQLManager.DB_PATH }"
35 />
36
37 <manager:SQLManager id="sqlManager" />
38
39 <!--
40 Flex Events
41 -->
42
43 <EventHandlers type="{FlexEvent.PREINITIALIZE}">
44 <ObjectBuilder generator="{ MainModel }" />
45 </EventHandlers>
46
47
48
49 <!--
50 Create a table using SQLServiceInvokers attribute called sql to set a SQL text
51 -->
52 <EventHandlers type="{FlexEvent.APPLICATION_COMPLETE}">
53 <air:SQLServiceInvoker
54 instance="{ sqlService }"
55 sql="CREATE TABLE IF NOT EXISTS users (userId INTEGER PRIMARY KEY AUTOINCREMENT, firstName TEXT, lastName TEXT)"
56 >
57 <air:resultHandlers>
58 <EventAnnouncer
59 generator="{ UserEvent}"
60 type="{ UserEvent.GET_ALL }"
61 />
62 </air:resultHandlers>
63 </air:SQLServiceInvoker>
64 </EventHandlers>
65
66 <!--
67 Get all stored user from database using a prepared SQLStatement, which is created by SQLManager.
68 -->
69 <EventHandlers type="{ UserEvent.GET_ALL }">
70 <air:SQLServiceInvoker
71 instance="{ sqlService }"
72 statement="{ sqlManager.getAllUsers }"
73 >
74 <air:resultHandlers>
75 <MethodInvoker
76 generator="{ MainModel }"
77 method="setUserData"
78 arguments="{ resultObject }"
79 />
80 </air:resultHandlers>
81 </air:SQLServiceInvoker>
82 </EventHandlers>
83
84 <!--
85 Delete selected user from database using a prepared SQLStatement, which is created by SQLManager.
86 -->
87 <EventHandlers type="{ UserEvent.DELETE }">
88 <air:SQLServiceInvoker
89 instance="{ sqlService }"
90 statement="{ sqlManager.deleteUser }"
91 parameters="{[ event.userId ]}"
92 >
93 <air:resultHandlers>
94 <EventAnnouncer
95 generator="{ UserEvent}"
96 type="{ UserEvent.GET_ALL}"
97 />
98 </air:resultHandlers>
99 </air:SQLServiceInvoker>
100 </EventHandlers>
101
102 <!--
103 Add new user to database using a prepared SQLStatement, which is created by SQLManager.
104 -->
105 <EventHandlers type="{ UserEvent.ADD }">
106 <air:SQLServiceInvoker
107 instance="{ sqlService }"
108 statement="{ sqlManager.addUser }"
109 parameters="{[ event.userVO.firstName, event.userVO.lastName ]}"
110 >
111 <air:resultHandlers>
112 <EventAnnouncer
113 generator="{ UserEvent}"
114 type="{ UserEvent.GET_ALL }"
115 />
116 </air:resultHandlers>
117 </air:SQLServiceInvoker>
118 </EventHandlers>
119
120 <!--
121 Update selected user from database using a prepared SQLStatement, which is created by SQLManager.
122 -->
123 <EventHandlers type="{ UserEvent.UPDATE }">
124 <air:SQLServiceInvoker
125 instance="{ sqlService }"
126 statement="{ sqlManager.updateUser }"
127 parameters="{[ event.userVO.firstName, event.userVO.lastName, event.userVO.userId ]}"
128 >
129 <air:resultHandlers>
130 <EventAnnouncer
131 generator="{ UserEvent}"
132 type="{ UserEvent.GET_ALL }"
133 />
134 </air:resultHandlers>
135 </air:SQLServiceInvoker>
136 </EventHandlers>
137
138
139 <!--
140 Handling for fault event may dispatched by SQLServiceInvoker or other services
141 -->
142 <EventHandlers type="{ UnhandledFaultEvent.FAULT }">
143 <MethodInvoker generator="{ GenericFaultHandler }"
144 method="handleFault"
145 arguments="{event.fault}" />
146 </EventHandlers>
147 </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
- Using transactions for batch INSERT/UPDATE/DELETE operations based on the great tips using AIR and SQLite by Paul Robertson!
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
- Eric Feminella: AIR SQL Framework
- Peter Elst: SQLite wrapper classes
- AIR doku on Adobe: "Working with local SQL databases"
- Paul Robertson: "360|Flex slides for 'AIR SQLite: An optimization conversation"
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.