Existing project : can the server see external modifications

tech · 2014-09-11 09:48:59

Hi all,

I want to use mormot on an existing project which uses delphi and firebird engine. I want to use mormot for the new features as stated in § "1.4. Legacy code and existing projects" of documentation and move little by little.
The question is : can mormot server see modifications applied on the firebird database with the existing project ? I know that mormot by design can't do that but how can I get around ?

thnx,

ab · 2014-09-11 09:57:46

If you disable all cache for the external tables hosted on firebird, and also disable the internal sqlite cache, it will work.

Please read the "cache" documentation in the SAD 1.18 pdf.

tech · 2014-09-11 10:09:01

Thank you AB,

But this can affect the performance. I'll try to have an idea about response time.

ab · 2014-09-11 13:33:12

The SQlite3 level cache is used only if your external tables are accessed in virtual mode, which is only for some JOINed requests.
So disabling it won't change the level for most basic ORM operations.

I've updated the SAD 1.18 documentation to answer more precisely to your question:

Legacy code still modifying the database wrote:

It is pretty much possible that you would have to maintain and evolve a legacy project, based on an existing database, with a lot of already written SQL statements - see Legacy code and existing projects. For instance, you would like to use mORMot for new features, and/or add mobile or HTML clients - see Cross-Platform clients.
In this case, the ORM advanced features - like ORM Cache or BATCH process, see BATCH sequences for adding/updating/deleting records - may conflict with the legacy code, for the tables which may have to be shared. Here are some guidelines when working on such a project.
To be exhaustive about your question, we need to consider each ORM CRUD operation. We may have to divide them in three kinds: read queries, insertions, and modifications of existing data.
About ORM Retrieve() methods, the ORM cache can be tuned per table, and you will definitively lack of some cache, but remember :
- That you can set a "time out" period for this cache, so that you may still benefit of it in most cases;
- That you have a cache at server level and another at client level, so you can tune it to be less aggressive on the client, for instance;
- That you can tune the ORM cache per ID, so some items which are not likely to change can still be cached.
About ORM Add() or BatchAdd() methods, when using the external engine, if any external process is likely to INSERT new rows, ensure you set the TSQLRestStorageExternal EngineAddUseSelectMaxID property to TRUE, so that it will compute the next maximum ID by hand.
But it still may be an issue, since the external process may do an INSERT during the ORM insertion.
So the best is perhaps to NOT use the ORM Add() or BatchAdd() methods, but rely on dedicated INSERT SQL statement, e.g. hosted in an interface-based service on the server side.
About ORM Update() Delete() BatchUpdate() BatchDelete() methods, they sound safe to be used in conjunction with external process modifying the DB, as soon as you use transactions to let the modifications be atomic, and won't conflict any concurrent modifications in the legacy code.
Perhaps the safer pattern, when working with external tables which are to be modified in the background by some legacy code, may be to use server-side interface-based services - see Client-Server services via interfaces - for any process involving external tables which may be modified by another process, with manual SQL, instead of using the ORM "magic". But it will depend on your business logic, and you will fail to benefit from the ORM features of the framework.
Nevertheless, introducing Service-Oriented Architecture (SOA) into your application would be very beneficial: ORM is not mandatory, especially if you are "fluent" in SQL queries, know how to make them as standard as possible, and have a lot of legacy code, perhaps with already tuned SQL statements.
Introducing SOA is mandatory to introduce new kind of clients to your applications, like mobile apps or AJAX modern sites: you could not access directly the database any more, as you did with your legacy Delphi application, and RAD DB components.
All new features, involving new tables to store new data, would still benefit of the mORMot's ORM, and could still be hosted in the very same external database, shared by your existing code.
Then, you will be able to identify seams - see Legacy code and existing projects - in your legacy code, and move them to your new mORMot services, then let your application evolve into a newer SOA/MVC architecture, without breaking anything, nor starting from scratch.

I've create a blog article about this subject, this it may be helpful for others.
See http://blog.synopse.info/post/2014/09/1 … se-sharing

vsj · 2014-09-12 12:16:41

Hello AB,

How hard would be to create a view/table -> classes mapping tool for using mORMot as a REST API on our existing database?

Our main project works with a Firebird database modelled in the following way:

Almost everything is modelled to the third normal form and we use a lot of specializations. For a very simple example, an object that would represent a customer. In our database the data is splitted between at least two tables. PERSON and CUSTOMER.
To make our database application friendly, we create editable views to abstract from the application our database complexity. So, for our Delphi application there is only a V_CUSTOMER_ED which take care of saving and reading the data on the correct tables.
This allowed us to simplify our framework and CRUD operations. And we also have a lot of business rules on the database (99% I would say).

So, now I'm looking for a way to create a REST API for our database. What I think would be very easy because our views are just like objects.
I've tried a little with the TMS Aurelius trial, because they have a feature to create TMS Aurelius classes on Delphi but they only work with real tables, no views.

To be honest, we are even considering about abandoning the Pascal world and go for ServiceStacks, or some solution based on EntityFramework or even some PHP based solution. But personally I liked a lot your examples of simple server without the need of IIS/Apache etc.

I still didn't took the time to look for Embarcadero's REST solutions. And as far as I could read on mORMot documentation (I did this at least one year ago), you don't have such tool as table/view generating classes, only the other way (which I know it is obvius from an ORM point of view).

Any insights about the first question are very welcome.

Last edited by vsj (2014-09-12 18:23:12)

ab · 2014-09-12 12:54:49

Perhaps there will be some little changes to do to mORMot, but I think you could use it to publish your views to a REST API.
We may have to add some options to the external class model, so that no CREATE TABLE statement is generated in TSQLRestStorageExternal.Create.
And other

The only restrictions I think of is that the primary key should be an integer, and that you should use simple CRUD operations INSERT / UPDATE / DELETE / SELECT on the view directly (so I guess using triggers for insert/update/delete).
Then you can use the field mapping to map your view fields to the mORMot classes.

danielkuettner · 2014-09-12 13:32:21

I see no big problems related to mORMot. In contrary mORMot is perfect for your project.
I use mORMot also with external Firebird and views.
Views can be handled by mORMot without problems.
And inserts/updates of views are problematic ever, or not?

I use a view for drawing and an other TSQLRecord (wich is part of view) for CRUD.

And the ID of the table can also set explicitly. So it must be no integer.

Last edited by danielkuettner (2014-09-12 13:33:51)

vsj · 2014-09-12 18:43:44

Thank you AB and Daniel for your answers.
Ours editable views use triggers for (insert/update/delete) and every primary key is an integer. Every on the pattern V_CUSTOMER_ED.ID_CUSTOMER.

I'll do some tests soon. Then, when have something to report, I will add here.

Thanks!

mORMot Open Source

#1 2014-09-11 09:48:59

Existing project : can the server see external modifications

#2 2014-09-11 09:57:46

Re: Existing project : can the server see external modifications

#3 2014-09-11 10:09:01

Re: Existing project : can the server see external modifications

#4 2014-09-11 13:33:12

Re: Existing project : can the server see external modifications

#5 2014-09-12 12:16:41

Re: Existing project : can the server see external modifications

#6 2014-09-12 12:54:49

Re: Existing project : can the server see external modifications

#7 2014-09-12 13:32:21

Re: Existing project : can the server see external modifications

#8 2014-09-12 18:43:44

Re: Existing project : can the server see external modifications

Board footer