AIR - Sqlite Proxy object

[Jiri]

Hello list,

first-off I am a bit of a newbie to using pureMVC, but so far it feels very nice to work with. I want to make a small test AIR application to get familiar with the frame work. The app will be using the Sqlite database so I will be creating a SqliteDatabaseProxy.I am just having some design questions.

I am creating a SqliteDatabaseProxy which extends Proxy. This class has a method  initialize(databaseName:String , location:File) which checks if the db file excists if not creates is and opens a Async connection using dbaseConnection.openAsync( dbFile ), where dbaseConnection is an instance of SQLConnection. After all this, a sendNotification is called to notify observers.

Considering the fact that this SqliteDatabaseProxy is only there to establish, verify and serve a database connection, I might need another Proxy object, let call it QueryProxy, that deals with the querys like CREATE,INSERT and UPDATE.
Do I then have to give QueryProxy and instance of SqliteDatabaseProxy and then implement an EventDispatcher in the SqliteDatabaseProxy so that the QueryProxy can addListeners to the SqliteDatabaseProxy and based on that send notifications to the rest of the system.
Or do I have then SqliteDatabaseProxy and the QueryProxy communicate which each other through notifications only.
Or do I just forget about two Proxy objects and just use one that deals with everything, where in this case I give up my goal to create a re-useable SQlite class.

I hope my questions are clear and that somebody can advise me.

Jir

[puremvc]

You might take a look at the way thr AIR XML Database utility works. Of course it will differ quite a bit, but it might be a useful exercise.

-=Cliff>

[Jiri]

Thank you for the quick reply. I came across it and am using it as a base, I just would like to figure out how to make the SQliteProxy into a reusable object, so up to a certain point I can follow the same logic as in the SerializationProxy. I am just wondering where to put the query logic and if I need to seperate that into a seperate Object.

J

[puremvc]

Well, the direction was heading with the XML Database as a suggestion was really not so much its implementation as how it achieves reusability by subclassing.

You might extend your SQLLiteProxy it and override the query methods in your concrete subclasses, rather than putting that logic in a separate class and being concerned about how to collaborate with the database class.

You might even create an ISQLProxy interface which this SQLLiteProxy implements. That would leave room for writing other ISQL Proxy classes (when it becomes possible to talk to something other than SQLLite).

Then, an app using the SQLLiteProxy would really cast and manipulate an ISQLProxy and never an SQLLiteProxy. Later when, say MySQL becomes available to AIR via an Adobe supported driver, a new MySQLProxy is written for it that implements this interface, and bada-bing, you can treat the two interchangably and swap them out without having to refactor the rest of the app.

-=Cliff>

[Jiri]

Ok, let me see if I get this correct. Instead of programming to the concrete class SQLLiteProxy, I should program to an interface, namely the ISQLProxy.
This ISQLProxy could define methods like getConnection() , initialize() , doQuery(SQLstatement) , etc.
If i understand this correctly that means that all the database handling is done inside a concreteclass that implements the ISQLProxy. How would I then deal with the possible async nature of the queries. Do I make for every ERROR, UPDATE  ,CREATE etc a command object and register those commands

If I look at the XML example, the Async methods signature defines a responder, this implies that composition is used. So I should probably have a Proxy that holds an instance of ISQLProxy (_dbase:ISQLProxy) and program to that interface. This way _dbase can be changable.
So basically I am then following the logic AIR XML Database utility, because I also intend to use responders and eventListeners.

I need to study that utility again, being more of a flash designer it is a hard and slow process entering the arena of OOP and the whole lot

Jiri

[Jiri]

To get things clear for myself and to practice, i did a scheme. I was wondering if someone has time to reflect on it and possible give some feedback. It is very crud, but should display my intentions.

Thank you,

Jiri

[Rhysyngsun]

Jiri, you'll also have to take into consideration that if you're using an asynchronous connection, methods such as getContacts won't be able to return a value immediately as your proxy will have to listen for a result event (and also error events) on the SQLConnection and then send the value off via a notification. Of course if your query results are expected to be small, you may find you are able to get away with a synchronous connection.

[Jiri]

Rhysyngsun, thnx for having a look at it and the comment. I realize the async nature and I am working on how to deal with that. As I see it now the notifications should indeed be called by the SQLProxy . Because the SQLProxy holds a reference to the concrete Class SQLiteDatabase I probably need to addListeners to the SQLiteDatabase i.e. SQLProxy.databaseRef.addEventListener(.onResult / onError) or register a responder like it is done in the Serialization Proxy example. Somehow I cant decide which one is the best one. I feel like adding dispacthers to the SQLiteDatabase that the SQLProxy can listen to is a messy solution and that defining a responder and have it implement an interface is a much cleaner solution.
I am right on this?

Jiri

[Rhysyngsun]

I think eitehr way will be fine, the only difference is that with Notifications you would be able let other parts of you application respond to the events in a PureMVC way (i.e., on an error, update the UI to show an error message). The biggest difference is that the proxies are decoupled from each other and you don't have to worry about adding/removing listeners with notifications. Either way, though you're going to need something to discern what kind of result you're getting. I personally might even go so far as to write a custom Event subclass for this, this way I could customize the interaction between the proxies as much as possible.

[puremvc]

I might need another Proxy object, let call it QueryProxy, that deals with the querys like CREATE,INSERT and UPDATE.

From the original conversation, I wasn't expecting the ISQLProxy to have methods like AddSmsToArchive. More like add, change, delete. Clearly an ISQlProxy/SQLProxy implemented like this within your app would achieve the benefit of allowing easy migration to another database later. However it wouldn't make ISQLProxy/SQLProxy reusable outside your application domain.

Considering the fact that this SqliteDatabaseProxy is only there to establish, verify and serve a database connection, I might need another Proxy object, let call it QueryProxy, that deals with the querys like CREATE,INSERT and UPDATE.
Do I then have to give QueryProxy and instance of SqliteDatabaseProxy and then implement an EventDispatcher in the SqliteDatabaseProxy so that the QueryProxy can addListeners to the SqliteDatabaseProxy and based on that send notifications to the rest of the system.

Lets go back to the idea of an SQLLiteDatabaseProxy, and make that implement ISQLDatabaseProxy. Its responsibility is as you describe, to establish, verify and serve a database connection. It might even subclass SQLDatabaseProxy which would define the most reusable properties and methods of an ISQLDatabaseProxy implementer. This lets us vary the database implementation at this level.  In your app you would subclass SQLLiteDatabaseProxy as something like SMSAppDatabaseProxy, and initialize it with what is necessary to create the concrete DB from scratch or to access it if it exists. Later SMSAppDatabaseProxy could be changed to extend a MySQLAppDatabaseProxy if such a database migration takes place.

Then you have ISQLQueryProxy, which would manage the preparation and execution of a query via its relationship with an ISQLDatabaseProxy implementer. An abstract SQLQueryProxy class would define things like a protected query property. The data property would be the result set. It would need to have result and fault methods to act as a responder, etc. SQLLiteQueryProxy would subclass SQLQueryProxy to override or add SQLLite specific functionality for the required interface methods. These need to be general methods that are focused on the management of a query or its result set.

Then classes like SMSArchiveProxy or ContactProxy would subclass SQLLiteQueryProxy, which could use their inherited ability to prepare and execute SQLQueries to encapsulate what is needed to manage these entities. These proxies can also add methods like addSmsToArchive. These are manipulated by the Commands, Mediators or other Proxies of the PureMVC app. It isn't necessary for these other actors to make ISQLQueryProxy interface calls. addSmsToArchive might be called by a Command, which internally triggers preparation and execution of the query via ISQLQueryProxy interface methods.

When you do your model preparation, you'd instantiate and register a MySQLAppDatabaseProxy, and likely have it create or connect at to the db at onRegister time. Then you'd create and register SMSArchiveProxy and ContactProxy and any others. They would retrieve the MySQLAppDatabaseProxy by name and use an interface method to execute their queries. The MySQLAppDatabaseProxy would set the ISQLQueryProxy implementers as responders for their calls. Mind your AsyncTokens.

-=Cliff>

[Jiri]

Cliff, thank your for the ellaborate explaniation. It is helping me a lot, allthough I am having a hard time to get my head around it! Can you please tell me a bit more about what you mean with this

An abstract SQLQueryProxy class would define things like a protected query property.

Do you mean by that a static var INSERT = 'INSERT INTO ....', or could you give an example?

Then I also dont really understand this in the last sentence.

The MySQLAppDatabaseProxy would set the ISQLQueryProxy implementers as responders for their calls. Mind your AsyncTokens

Here is also a new scheme, am I on the right track?
Jiri

[puremvc]

Jiri,

You are on top of it. This diagram is nearly there. The only thing missing (I think), is the thing you call out to for clarification; the abstract SQLQueryProxy class.

Your diagram has ContactProxy implementing ISQLQueryProxy. And it would, so the diagram is not invalid.

I'm suggesting that there would be an SQLQueryProxy utility class that implements this interface. ContactProxy would implement ISQLQueryProxy by extending SQLQueryProxy and overriding the methods it needs.

I think that the SQLQueryProxy will want to have a protected property for its query. So that you can set a query on the Proxy but invoke it at a later time. Or reinvoke the same query more than once.

Also, the any ISQLQueryProxy may want to take an action when its query property is set, perhaps validating it to make sure it the user hasn't attempted an SQL injection in data input, or updating an associated Proxy. Therefore, creating a protected getter and setter for this property on SQLQueryProxy that calls a boolean validate method and only sets the _query property if validate returns true. The SQLQueryProxy's validate method could simply return true. ContactProxy and any other SQLQueryProxy could override this method if it wishes to perform special validation.

If execute is called with no argument, then you validate and execute the query that's in the query property.

The execute method could still take an *optional* string. If present it would set the query property equal to this, validate it, perhaps and then attempt to execute it.

Also, it would be good to provide for parameter replacement in queries with arguments. I don't see if SQLLite can do this or not, but the SQLQueryProxy could do it as an in-Proxy preparation of the query. You'd have a template property and an arguments property. In the same way that the setting of the query property triggers validation, setting the arguments property would do parameter replacement on the template from the arguments, and then set the query property with the replaced parameters, ready to execute.

-=Cliff>

[Jiri]

Cliff,

Good to hear that I am on the right track. Just one more question could you please explain or provide an example about this part:

Also, it would be good to provide for parameter replacement in queries with arguments. I don't see if SQLLite can do this or not, but the SQLQueryProxy could do it as an in-Proxy preparation of the query. You'd have a template property and an arguments property. In the same way that the setting of the query property triggers validation, setting the arguments property would do parameter replacement on the template from the arguments, and then set the query property with the replaced parameters, ready to execute.

[puremvc]

Parameterized queries could be left off for now if SQLLite doesn't support it.

Such would be a query template like 'SELECT * from %1 WHERE %2 LIKE %3' and you'd pass 3 arguments, which would in order be used to replace the place holders in the template.

-=Cliff>

[Jiri]

Ok, thats what you meant and yes SQlite does support it!!