mORMot over OleDB

ab · 2011-06-10 08:49:13

Comments of the http://blog.synopse.info/post/2011/06/0 … gnosticism blog entry.

Our ORM RESTful Framework is about to access any available database engine.

It will probably change its name (since it won't use only SQlite3 as database), to become mORMot - could be an acronym for "Manage Object Relational Mapping Over Tables", or whatever you may think of...

We'll still rely on SQLite3 on the server, but a dedicated mechanism will allow to access via OleDB any remote database, and mix those table content with the native ORM tables of the framework. Virtual Tables and column mapping will allow any possible architecture: either a new project in pure ORM, either a project relying on an existing database with its own table layout.

TPrami · 2011-06-10 11:40:38

Hello,

Sounds good. But one feature, I think, is a MUST have.

Undo. Possibility make easy undo of operations done into the database. BEcause coding it at higher level is pain. and I think ORM/Persistence framework must have built in support or Undo.

just my $0.02

-Tee-

ab · 2011-06-10 14:13:57

What do you call ORM "Undo"?

Do not available Commit/RollBack feature suffice?

I'm just afraid that in a Stateless/RESTful world, what you call "Undo" would just be incompatible.

TPrami · 2011-06-13 05:00:31

OK,

Like all normal programs, Undo is very very important feature. And if there is some kind of support to save the changes at the serverside. Undo should be doable. (To work it would need some kind of table structure to save all the changes).

But multistage Undo would be a must feature in my point of view. Does not matter how it is done. But basically almost any kind of app would need an Undo, or at least would make it much better. I've been developing an application that did not have Undo, and it really made everything so bad (for User). Users had to really think very carefully what to do. And in many cases they did not have courage to do any changes even they needed to. Because there was no way back.

I think that any framework that sits between (G)UI and DB should have undo. Way or other...

-TP-

ab · 2011-06-13 05:51:00

As far as I can tell, your Undo feature is already available in the Commit/RollBack feature, already available in the framework.

An ORM-level could be imagined, storing the TSQLRecord content then handling a per-session Undo/Redo list, just like described in http://www.python.org/workshops/1997-10 … owski.html
Or even hack the ORM at low-level to implement a more efficient algorithm like the one described in http://drdobbs.com/184404861

The main issue is that the low-level SQL statements should have to be staid in synchonization with the high-level ORM features, like Undo.

But in all cases, I really don't see how the TransactionBegin/Commit/RollBack methods won't suffice.
You have plenty of other means of working on a data set with our ORM, like using an in-memory copy of the shared data, play with it, then commit the change when needed.
Another possibility is to have a dedicated table for storing the diverse state of a record. It could be easily done, in a quick and low resource way, by using a Synopse Big Table associated with a "normal" ORM table.

TPrami · 2011-06-14 05:06:52

Hello,

I am not sure that we are on the same page, but basically I mean that user must have to have possibility do multistage undo (and redo also, as you mention).

If you make 10 changes, you should be able to step back those 10 and then start again if you want to. and IMHO it is the job of the framework, not an "application" to make the support for undo/redo steps. If you can tweak it (etc) that is bonus. But just rollback multiple commits (one or multiple steps), would be cool....

-Tee-

ab · 2011-06-14 08:51:59

OK I think I get the idea.

Do you think that named transactions should make it?
See http://www.sqlite.org/lang_savepoint.html
With this feature, you can create nested "undo bookmarks", then go back to the corresponding data state.

We could add this feature at the ORM level, by adding some "save-point" dedicated methods, in addition to TransactionBegin / Commit / Release existing methods.
Virtual Tables already handle this, even if our in-memory engine doesn't implement it.
But for the mORMot framework, we could use this scheme, then translate it into corresponding SQL statements of the involved tables - for instance with SQL Server: http://msdn.microsoft.com/en-us/library/ms174377.aspx
But it could be a bit difficult to handle all cases, since there are several ways of accessing the data...

So a generic Undo/Redo mechanism, at the ORM level, could make sense.
It would have to be implemented on the Server side, of course, for both performance and data sharing reasons.
A potential draw-back is that we'll loose the possibility of having data consistency if both ORM and direct SQL access are performed on the same data...

TPrami · 2011-06-14 09:50:34

Yes,

I think something like that would cool.

Because I think framework is the best place for it. Even if it needs some (specific) table(s) (specific structure) to log the changes or what ever the mechanism would be.

And other point is the there is no need to anyone to solve this same problem if they need the Undo/Redo. And most likely there must be some kind of separation by user (or other entity). So that it is not just an undo for changes at general, but undo by some id, and maybe from middle the "change queue". If just possible.

Maybe user must write some handlers and/or event handlers. To help the Framework do it's job correctly regarding to the Undo/Redo feature. But still. Easier the feature is to implement and more flexible it is. I think that would be very strong selling point for the framework...

-TP-

ab · 2011-06-14 11:30:52

I've added Undo/Redo to my do-to list.
I'll first check for implementation design.

ab · 2011-06-14 11:33:28

TPrami wrote:

I think that would be very strong selling point for the framework...

Selling... for free...
But any feature that could make our ORM distinctive (like our Virtual Table use which allow to mix Delphi classes and SQL, which is a quite unique feature) sounds great to me.

Therefore, I've added Undo/Redo to my do-to list.
I'll first check for implementation design first, then schedule it on purpose.
But I will without doubt let mORMot still be able to handle this Undo / Redo mechanism. I don't want new features to break the ability to add such nice ideas in the future.

TPrami · 2011-06-16 05:28:19

ab wrote:

TPrami wrote:
I think that would be very strong selling point for the framework...
Selling... for free...

Price does not matter, you have to sell it even if it is free. No one will use it, if it is not good, or don't have good enough feature set...

-TP-

ab · 2011-06-16 05:48:40

TPrami wrote:

Price does not matter, you have to sell it even if it is free. No one will use it, if it is not good, or don't have good enough feature set...

You're right..

ab · 2011-06-27 19:35:25

That's it, the OleDB part seems working.

It's a very thin layer which will allow fast access to any OleDB database (including all ODBC drivers).

The SynOleDB unit has been designed for our mORMot ORM, but is stand-alone, and can be used as such.
Works from Delphi 6 up to XE, in all Delphi Edition, including Delphi 7 personal e.g.
See http://blog.synopse.info/post/2011/06/2 … -via-OleDB

It's definitively a good alternative to the ZDBC components as supplied with ZEOS.
By letting OleDB making most of the work, we found out a very elegant and efficient way of acessing any database from Delphi.

One giant step toward mORMot!

ab · 2011-07-25 12:10:13

A native layer to Oracle, via direct OCI connection, has been implemented.
see http://blog.synopse.info/post/2011/07/0 … cle-access

Together with a native SQLite3 connection - see http://blog.synopse.info/post/2011/07/2 … ect-access - our database-agnostic mORMot is closer to release.

ab · 2011-07-30 15:11:38

That's it: the newly introduced SQLite3DB unit implements Virtual tables for DB direct access classes for the mORMot framework.
See http://synopse.info/fossil/finfo?name=S … ite3DB.pas

In fact, the new TSQLRestServerStaticExternal, TSQLVirtualTableCursorExternal, TSQLVirtualTableExternal and TSQLRecordExternal classes will implement this feature.

SQLite3DB unit now completed and optimized - still need to write all appropriate regression tests, and do the benchmarking/testing over several external databases (at least with an external SLQLite3 db in common regression tests classes).

The following new global procedure:

procedure VirtualTableExternalRegister(aModel: TSQLModel; aClass: TSQLRecordExternalClass;
  aExternalDB: TSQLDBConnectionProperties; const aExternalTableName: RawUTF8);

can be called to register on the Server-side an external database for an ORM class
- will associate the supplied class with a TSQLVirtualTableExternal module
- the TSQLDBConnectionProperties instance should be shared by all classes, and released globaly when the ORM is no longer needed
- the full table name, as expected by the external database, should be provided here (SQLTableName will be used internaly as table name when called via the associated SQLite3 Virtual Table) - if no table name is specified (''), will use SQLTableName (e.g. 'Customer' for 'TSQLCustomer')

Typical usage is therefore for instance:

Props := TOleDBMSSQLConnectionProperties.Create('.\SQLEXPRESS','AdventureWorks2008R2','','');
Model := TSQLModel.Create([TSQLCustomer],'root');
VirtualTableExternalRegister(Model,TSQLCustomer,Props,'Sales.Customer');

All the rest of the code will use the "regular" ORM classes, methods and functions.
You don't have to know where and how the data persistence is stored.
The mORMot framework will do all the low-level DB work for you.

Stay tuned!

mORMot Open Source

#1 2011-06-10 08:49:13

mORMot over OleDB

#2 2011-06-10 11:40:38

Re: mORMot over OleDB

#3 2011-06-10 14:13:57

Re: mORMot over OleDB

#4 2011-06-13 05:00:31

Re: mORMot over OleDB

#5 2011-06-13 05:51:00

Re: mORMot over OleDB

#6 2011-06-14 05:06:52

Re: mORMot over OleDB

#7 2011-06-14 08:51:59

Re: mORMot over OleDB

#8 2011-06-14 09:50:34

Re: mORMot over OleDB

#9 2011-06-14 11:30:52

Re: mORMot over OleDB

#10 2011-06-14 11:33:28

Re: mORMot over OleDB

#11 2011-06-16 05:28:19

Re: mORMot over OleDB

#12 2011-06-16 05:48:40

Re: mORMot over OleDB

#13 2011-06-27 19:35:25

Re: mORMot over OleDB

#14 2011-07-25 12:10:13

Re: mORMot over OleDB

#15 2011-07-30 15:11:38

Re: mORMot over OleDB

Board footer