LiveCode Forums.

My First post. Apologies if this post is a bit of a ramble.

I have an application that provides access to a db and other crm style functionality via a hosted Web2.0 interface. Truth is whilst this is nice administratively, most customers would like to be able to operate in both an online and offline world. So we then enter the world of Rich Internet Applications. Adobe Air, Zzee etc. These would allow us to transition to become a web aware desktop client, however those technologies have their issues. For instance what about native look and feel. I much prefer the performance of a desktop app over a web based app anyway. For productivity apps there is no question a zippy feel is needed.

It then becomes logical for me to reassess Runtime Revolution as a dev platform.

The Question Then?

I was thinking we could maintain our web based data store, and use SQLite as the local data store on the client. A typical user might have 10-50K records out of say 10 million from the db. I assume that Run-Rev can access web services etc like Amazon's SimpleDB or remote mysql services (I am sure it can).

What is the best approach to keeping the data sync'd between the on and off line worlds?

Perhaps in a run-rev context this is more trival than it is in web 2.0 setting where using databound grids etc (even with paging, makes using large data sets painful).

Perhaps one approach on instalation is to grab the initial record set for the client and to look for the web service on each program launch. Another to force the user to hit a process updates button, another more sophisticated to update both local (fast) and use this dataset for the program and update remote data live when connected as a back ground process.

Not sure of the speed, overhead or potential issues from a Revolution perspective.

Any comments would be appreciated.

BTW, perfect timing with the new data grid, in the context of what we may try to achieve with revolution.

Kind regards, Andrew

I'm not much of a database guy, however i have networking experience with runrev.

People have told me that using mysql directly via remote connection can be highly unreliable, mostly due to unexpected timeouts. setting the "timeoutinterval" can help, but only enlarges the time period, and does not remove the underlying problem. Especially with queries that take long time to execute server side, this can soon become unmanageable.

So if you have code on the server, I'd suggest making an interface that your rev app (or other technology standalone) connects to. simple http request and posts can give you great control and feedback possibilities (load-bars etc.). Also you can send xml or just plain text or even binary stuff fore and back, whatever is the best data format for you.

As for where the main data is stored, and when to send what where: This can only be answered by yourself. Ask these questions in regards to your user case and data volumes:

• Where is the biggest time bottleneck? on the inserts? the queries? the user entering data? showing the data to the user?
• Where does the data reside more often, locally, or on the server?
• Is there a big size different between updating the client from the server or the other way around?
• What would your users preffer, local storage, or network storage? also think about one user using the data of another user, maybe even on the same machine.

I guess you already are appending an entry time & date to everything, this is important as sooner or later there would be asynchronous update problems, if data is kept locally, and only synced sporadically.

So basically... it all depends on what you want to do [/list]

Thank you for the reply BvG,

You are dead right about the MySQL connection persistence issue, as evidenced by my testing last night.

I am leaning towards a local SQLite implementation on the client, and an update remote svr when connection available model for syncronising data between clients and the server. A replication model akin for example to Lotus Notes. The server being the most authorative version of the data.

The bottlenecks in our web 2.0 application are really in navigating, and slicing large datasets on the web.

I am testing the new data grid control with large datasets, and so far, so good.

Thanks again for the feedback.

- Andrew

Hi Dickey,

I have some degree of expertise in Lotus Notes, having administered and developed on that platform for over a decade. Unfortunately, I'd say it's very difficult to try to reproduce the functionality of Notes' replication and distributed datastores.

With Notes there's far more to the issues of replication and distributed data then the server being merely the definitive version. Notes has a huge variety of ways to configure and control replication, and in the default implementation all servers and clients are masters. The reason that Notes can have such flexibility is that replication was built-in to the architecture from the beginning, along side fine-grained security and identity management (servers and users have hierarchical identities, and both servers and users can be assigned to multiple groups and assigned to multiple roles, and security and replication privileges can in turn be assigned to groups and roles). In fact, you can even configure Notes replication to be only one way, and can choose whether or not data structures and code changes are replicated as well as data changes (there are more issues to distributed applications than just replicating data).

Obviously if you are going to implement a local datastore for your application, you need some way to check the versions. Server-side your app and database will need to have some version-logging code, and if you are going to allow the local data to be updated when there is no server-side connection, then you are going to need some way of reconciling distributed changes. You don't want User A to send his changes to the server from his local datastore, only to have them over-written by User B when she connects to the server. The way Notes does this is a) by allowing any data item in a Note to overwrite another provided that the over-writing item has a later modification timestamp (thus making the over-write when there is no contention at this atomic level automatic), b) when a) cannot be performed by creating a duplicate copy of the Note and signalling this conflict by marking one document to be a "replication/save conflict" of the other. Manually managing these replication/save conflicts is another matter.

A further problem to consider is that of the vulnerability of your local data. With Notes every database can be encrypted with the id of the user, making it useless to anyone who is not that user. So you will need to consider the vulnerability of both your local data and the schema of your database.

Another security implication here, is that there may be data that is read-only for some users, yet having a local datastore may allow them to over-ride your controls. Thus, if that data is stored in a local database, there may need to be obstacles that prevent the user installing a sqlite tool, searching in the database for certain strings, changing them, then replicating the changes they should not be permitted to make back to the server. Even if they are not allowed to install software, what is to stop them from taking the database home, making the changes at home, then bringing it back and copying it over the existing database? None of this is possible with Notes - even in a local datastore, if the user has no access to a field then the user has no access to it. (If the database is not encrypted I guess they might be able to use a hex editor to make changes, but since Notes stores a lot of additional data with the field, they're just as likely to make the database unusable by doing that).

A further corollary is how do you manage changes to your database schema? If you change your data structures on the server, how do you send those changes to the client, and how do you migrate the data in the local datastore to the now changed schema (assuming that the schema is not just going to introduce new columns/tables).

I'm saying all this because in the 25 years or so that Notes has been around, no other development environment/database has managed to successfully reproduce what Notes can do. And this is again because these things were all considerations when the architecture of Notes was designed.

There are many things I dislike about IBM, and about the way they marketed Notes in the last 10 years. Notes is under-appreciated technically by many people (including many at IBM who have marketed it primarily as an e-mail platform for far too long). There are also problems with the Notes client's complexity that would prevent me from ever recommending it into an workforce where the staff would not get ample tuition and support in exploiting its features. I used to do admin/development in such a company, and they eventually ripped out Notes as an application development platform and as an email client. Instead they've massively invested in web applications.

I expect you've already given thought to much of what I've said, so I'll shut up!