Putting mORMot on top of existing databases

bravecobra · 2013-05-15 11:11:50

Hi,

I've been reading the (excellent) documentation from front to back, but I must have missed the following use cases.

I've got an existing applications using database that have lots of stored procedures. The choice of using stored procedures in most cases is quite logical:
1. Performance of certain queries is just faster on the database directly that retrieving all the relevant data from the database and handling it in Delphi, like pivoting. In the documentation (5.4.2.1) sharding is mentioned as a solution for pivoting, but that won't/can't apply to (our) existing relational databases.
2. Sometimes I need to use optimizer hints (ORACLE) to make certain queries more performant. (http://docs.oracle.com/cd/B19306_01/ser … ntsref.htm)

The fact that all SQL is auto-generated could in certain cases make data retrieval from the database using mORMot inperformant.
Is there a way of either overwrtting the generated SQL or specifying the SQL to be used, just for that specific TSQLRecord. I just want to expose the result of the stored procedure in JSON to the client.

Which brings me seamlessly to the second use case. I noticed the mORMot "auto-maintains" the SQL schemes. Is there a way of disabling that? As I'm working with existing databases, that might cause a lot of problems on ie. dataware-house ETL's. I don't the SQL scheme to automa(t/g)ically change without me knowing, as the implications might be cause unforeseen problems. I btw noticed a SQLAddField method, but where are the SQLRemoveField and SQLRename methods? It seems the framework is just adding missing tables and fields, nothing more. I consider that a maintenance horror as you lose control over your database schemes. I'm probably to use the TSQLRecordMappedAuto(Forced)ID, but there are no examples to be found on how to use these classes (if they still exist, because I couldn't find them in the source code).

In the documentation (6.2.3) the distinction between DTO objects and Domain Values is mentioned. I totally agree with the concept here, but if based on the same TSQLRecord class, how do you make the distinction? Consider the case where the client retrieves a TSQLRecord object (as a DTO object) from the server, that is made up from different distinct Domain Values. The client then changes some properties on the DTO object and sends it back to the server. Is mORMot able to get back to those original Domain Values in order to persist those to the database. Furthermore, in the examples, I never found anything that demonstrates the writing of a (Delphi) business logic layer. It seems that the Domain Model and the ORM layer are tightly glued together. In such a way, that is/seems hard to write business logic (like complex transformations etc) in between. (btw RemObject's DataAbstract suffers the same problem, they even force you to write that in Javascript).

Is there an example of one HTTP server with exposing multiple TSQLRestServer classes and/or interface-services, each with a different model?

One last (less important) question/remark: I was looking for a fail-over solution (as DataSnap offers). If the main application server goes down, the backup application server takes over automatically (on a different port). We found this very useful when updating application servers as it brings your downtime to almost zero.

Btw, the mormot.pas file is getting rather big. I'd suggest splitting that into separate (logical) units. Just my 2 cents.

Also some of the units are not compiling on XE3 (latest "trunk"):
1. mORMotBigTable.pas :[dcc32 Error] mORMotBigTable.pas(85): E2037 Declaration of 'EngineList' differs from previous declaration
2. SynDBUniDAC [dcc32 Error] SynDBUniDAC.pas(283): E2034 Too many actual parameters (This would have been my gateway to MySQL )

I've been trying to place everything into a package(s), but I only succeeded doing that with a subset of the source files (luckily the main ones). The others gave compile errors, mostly the GUI stuff. Call me old fashioned, but I'd prefer packages over source code in my library path. I just don't want to re-compile everything over and over again. The complete source consist of several different logical parts, they should even be in separate packages. When the source code was placed into the library path, all the demo's did compile however, but, as I said, not the preferred way to do things.

In other words, I'm just having a hard time applying the mORMot architecture to existing databases. Putting mORMot on top of an existing database and implementing the schema of page 68 in the documentation in practice, might be a great example series. I think I'm just missing that big-picture example, exploiting the full potential of mORMot.

Sidenote: any plans on moving to Subversion or git, making it more "accessible" to the big masses?

Kinds regards

ab · 2013-05-15 11:53:54

About source code:
1. mORMotBigTable is not working at all by now, nor tested, nor integrated. It was just a first draft. Our SynBigTable unit has some issues, e.g. when handling a lot of records the internal lists tends to be slow.
2. SynDBUniDAC use a somewhat old version of the library with the following signature:
procedure TCustomDAConnection.GetTableNames(List: _TStrings; AllTables: boolean = False; OnlyTables: boolean = False);
perhaps your version did evolve.

For packages, ensure you did define USEPACKAGES conditional.
Your feedback is needed if it is not enough, since we do not use packages here, and compilation of our mORMot applications is pretty fast.

There is no sample of one HTTP server exposing multiple TSQLRestServer classes by now.
Just add the TSQLRestServer instances when initializing the HTTP server. Each one must have its own Root value in its model (to identify a genuine URI).

About the main point

Working with an existing and manually handled database is on the roadmap, via TSQLRecordMappedAuto(Forced)ID classes.
But it is still not implemented.
With existing database, you could just define interface-based services, executing directly manual SQl statements.
The ORM won't help much in its current state, you are perfectly right.

Perhaps I may begin to define the TSQLRecordMappedAuto(Forced)ID classes, auto-generated from SynDBExplorer, and you would provide your feedback to improve the design as soon as possible?

BTW, MySQL support is only theoretically by now.
I personally never used this DB in Delphi (only for LAMP apps). Therefore, for MySQL also, feedback is needed!

This is how Open Source works: contribution are always welcome!
Those contributions are worth the price: it will help the library fulfill your exact needs, and share your experiment with others.

SVN/Git was discussed several times.
I like Fossil very much, and find it very fast and easy to work with.
Its design is very clever, secure, scaling and modern.
SVN may be more "standard", in fact.
But would it add some visibility to the project?

bravecobra · 2013-05-15 12:37:51

Tnx for the quick answer.

I already found the USEPACKAGES compiler directive in the inc file. Note that the package should also only generate DCU's, anything in the direction of C++ just fails with internal errors. (That kept me busy for a while )
With that in mind, mORMotUIOptions.pas and mORMotUIQuery.pas certainly fail in a package (just retried that).

I'll see what I can do on UniDAC. I think I've got (almost) the latest version here, so that should work out. That would also make MySQL work, theoretically.

As for the main point: I don't mind using interfaced based. I guess I would have to do the JSON serializing "manually" as well then, right?
Would that be the right place to plugin "another ORM", until this has been resolved? You see how the separation of layers in separate units is useful here?

Extending (or building in support) for turning on/off the auto-SQL generation would solve the problem here I think. Instead of calling InternalListJSON, just call the (predefined) method being as coded/overridden by the programmer. However I do believe a Domain Value is to be ignorant of its persistence. It is to ask another class to have itself persisted, instead of retrieving itself based on values, thus making the overridden method not the correct solution.

I still fail to see where you make the distinction between DTO's and Domain Values, since the complete model is exposed. I don't think this is responsibility of the security layer, as I've read somewhere else in the forum.

I'd love to contribute, of course, and that's where git comes into play. At code.google.com or github, I could easily work on my own clone and make pull requests. I'd hate to post patches on a forum. How would you like to see this happen?

bravecobra · 2013-05-15 12:49:39

In SynDBUniDAC on line 283 should read : (MainConnection as TSQLDBUniDACConnection).fDatabase.GetTableNames(List,true);
Then it compiles perfectly. Just a mORMot issue. Whether I can now work with MySQL DB's, I still have to test out.

ab · 2013-05-15 13:19:35

bravecobra wrote:

In SynDBUniDAC on line 283 should read : (MainConnection as TSQLDBUniDACConnection).fDatabase.GetTableNames(List,true);
Then it compiles perfectly. Just a mORMot issue. Whether I can now work with MySQL DB's, I still have to test out.

Why is the method declared as

procedure TCustomDAConnection.GetTableNames(List: _TStrings; AllTables: boolean = False; OnlyTables: boolean = False);

in my UniDAC version?

Which revision of UniDAC are you using?

I've fixed the compilation problem.
See http://synopse.info/fossil/info/767e09a0f5

ab · 2013-05-15 13:26:04

bravecobra wrote:

As for the main point: I don't mind using interfaced based. I guess I would have to do the JSON serializing "manually" as well then, right?

If you use our SynDB classes, you have all JSON serialization already implemented, in a very optimized way.
For a sample, you can take a look at "16 - Execute SQL via services" in the "Samples" folder.

Then you can map the returned JSON list with any TObject with published properties, as soon as it maps the SQL column names (returned as JSON).
JSON2Object() can be used to retrieved the content.

Or you can just let mORMot serialize the other ORM objects directly.
Read the SAD about custom serialization.

bravecobra wrote:

I still fail to see where you make the distinction between DTO's and Domain Values, since the complete model is exposed. I don't think this is responsibility of the security layer, as I've read somewhere else in the forum.

You can just do not include the TSQLRecord on the client side model, then do not use the client-server ORM features, and rely only on interface based services.
If you want session control and security, minimal User and Group tables are necessary in the model, but can easily be implemented in-memory or in External DB.

bravecobra wrote:

I'd love to contribute, of course, and that's where git comes into play. At code.google.com or github, I could easily work on my own clone and make pull requests. I'd hate to post patches on a forum. How would you like to see this happen?

I can easily give your own fossil account, to do the same directly from our repository.
Fossil is a distributed Source Control Manager, just like git.

bravecobra · 2013-05-15 13:43:55

ab wrote:

If you use our SynDB classes, you have all JSON serialization already implemented, in a very optimized way.
For a sample, you can take a look at "16 - Execute SQL via services" in the "Samples" folder.
Then you can map the returned JSON list with any TObject with published properties, as soon as it maps the SQL column names (returned as JSON).
JSON2Object() can be used to retrieved the content.
Or you can just let mORMot serialize the other ORM objects directly.
Read the SAD about custom serialization.

Ok perfect, I'll look into that.

ab wrote:

You can just do not include the TSQLRecord on the client side model, then do not use the client-server ORM features, and rely only on interface based services.
If you want session control and security, minimal User and Group tables are necessary in the model, but can easily be implemented in-memory or in External DB.

That smells like a serious design fault. If I was to expose my REST server onto the internet,since I want to offer third party clients access to our database.In this case I would be exposing my complete model, right? Consider the fact that I might not be writing the client, but that third party (malicious) client might. You only want to expose what's needed through REST, nothing more. They should only be able to do whatever I offer in my REST API, nothing more!

ab wrote:

I can easily give your own fossil account, to do the same directly from our repository.
Fossil is a distributed Source Control Manager, just like git.

Great, I'll for one once I have something useful to share

Last edited by bravecobra (2013-05-15 13:46:19)

bravecobra · 2013-05-15 13:51:00

ab wrote:

Why is the method declared as
procedure TCustomDAConnection.GetTableNames(List: _TStrings; AllTables: boolean = False; OnlyTables: boolean = False);
in my UniDAC version?
Which revision of UniDAC are you using?
I've fixed the compilation problem.
See http://synopse.info/fossil/info/767e09a0f5

I'm using UniDAC 4.5.9, not the latest apparently, 5.0 just came out.

Last edited by bravecobra (2013-05-15 13:53:19)

ab · 2013-05-15 14:41:01

Mine was 4.6.11...

leus · 2013-05-27 16:36:59

On the topic of source control management, I've never heard of Fossil before reading about it here. I think this project could gain much more traction if you moved to a more popular source control program. I know you are used to Fossil and its conveniences, but nowadays Git, for example, can have just as much (if not more) functionality. Github may be a good place to host.

Personally, I don't want to install Yet Another Source Control thing on my PC for just this project (I'm using zip packages to download your code.)

I think your choice of source control system is actually harming the spread of this awesome project.

ab · 2013-05-27 16:41:42

What is the license terms about github?
Do we still fully own the content?
Is it compatible with our 3 licenses?

bravecobra · 2013-05-28 17:05:08

I don't think there would be any concerns regarding licenses. GitHub just places git repositories open for the public.
There is a "recent" article on github hosted projects and their licenses: http://www.theregister.co.uk/2013/04/18 … ing_study/

Note that git doesn't have tickets, wiki nor Blog/News. It's only a versioning repo. Github however provides the rest.
Fossil repo's can be perfectly converted into git : http://www.fossil-scm.org/xfer/doc/trunk/www/inout.wiki

Last edited by bravecobra (2013-05-28 17:13:04)

ab · 2013-05-29 08:56:00

I do not have ADSL Internet connection for some days (I'm on vacation, using a GSM connection).
It it not the best time for me to try to make the switch.

Exporting to git is OK.
But it will take time to switch the tickets, and blog/news.
I tried to make a lot of articles... some are extracted from the SAD source, but others are standard.
What about the wiki (static content)?
Is there any RESTful client to automate the import of existing content to tickets/blogs/wiki?

Honestly, I'm afraid it will be time consuming.
Any help?

bravecobra · 2013-05-29 10:31:07

The full API is available and documented here: http://developer.github.com/v3/

ab · 2013-05-29 13:02:45

This API sounds a bit confusing to me.

As far as I discovered, there is only access to the repositories (global account/repository/users/project list and commits of a given repository), not to the wiki/blog/tickets/news content of a given repository.

bravecobra · 2013-05-29 17:04:23

Check the menu on the right: here's the API for the issues/tickets: http://developer.github.com/v3/issues/
The wiki is updated through the git repo itself.

Last edited by bravecobra (2013-05-29 17:05:50)

ab · 2013-05-29 17:25:29

Yes, but nothing about blog/wiki articles?

eraldo · 2013-05-29 17:40:45

"git doesn't have tickets, wiki nor Blog/News"...

Sorry for those who think differently

I do not see many advantages to consider GitHub...

I believe there are more important tasks to be considered.

mingda · 2013-05-30 00:54:53

>>I believe there are more important tasks to be considered.

Yes, I also feeling this, these days I learn mORMot,
for I the lack a bridge between Old UI DB Bind Designer is a issue, and auto master-detail applyupdate,
I want the bidirectional edit of clientdataset, I think if this
feature implement, will able to attract a number of the original developers,
just my personal view, sorry for my poor english.

ab · 2013-05-30 10:04:58

Are you sure a TDataset kind of component is the best option?
Why not just use live bindings, available in newer versions?
It will work with all components, and for both VCL and FireMonkey.

On the road map there is an item about MVVM/MVC auto-synchronization between ORM and UI components.
What do you think?

mingda · 2013-05-30 15:33:41

For I not only pure new project, these are projects to maintain and evolution,
for thin Client Program customer like DB binding cxGrid, its function is rich, so i can't
just throw it away, i person like coding, but for UI part, I like Rad Designer.

About MVC, I like the idea, but i don't know how design the UI part.

leus · 2013-06-24 17:00:22

I agree there is always lot of work to be done in a project like this, but attracting developers is one of the most difficult yet important tasks for any open source project. So any steps in that direction should be a priority. I think this is one of the most important Delphi projects around, and it should have the attention it deserves. So, even if I agree that TDataSet support is important because it would make migrating existing projects easier (and I'm personally interested in it) I still feel that having the project in a more popular infrastructure would be of great benefit in the long run.

Anyways, keep up the good work!

ab · 2013-06-24 20:45:32

It does make perfectly sense!

What we need is a set of source code contribution.
Everybody's income is welcome!

leus · 2013-06-25 18:56:41

I'm willing to, but I haven't got around understanding Fossil...

Last edited by leus (2013-06-25 18:56:53)

mORMot Open Source

#1 2013-05-15 11:11:50

Putting mORMot on top of existing databases

#2 2013-05-15 11:53:54

Re: Putting mORMot on top of existing databases

#3 2013-05-15 12:37:51

Re: Putting mORMot on top of existing databases

#4 2013-05-15 12:49:39

Re: Putting mORMot on top of existing databases

#5 2013-05-15 13:19:35

Re: Putting mORMot on top of existing databases

#6 2013-05-15 13:26:04

Re: Putting mORMot on top of existing databases

#7 2013-05-15 13:43:55

Re: Putting mORMot on top of existing databases

#8 2013-05-15 13:51:00

Re: Putting mORMot on top of existing databases

#9 2013-05-15 14:41:01

Re: Putting mORMot on top of existing databases

#10 2013-05-27 16:36:59

Re: Putting mORMot on top of existing databases

#11 2013-05-27 16:41:42

Re: Putting mORMot on top of existing databases

#12 2013-05-28 17:05:08

Re: Putting mORMot on top of existing databases

#13 2013-05-29 08:56:00

Re: Putting mORMot on top of existing databases

#14 2013-05-29 10:31:07

Re: Putting mORMot on top of existing databases

#15 2013-05-29 13:02:45

Re: Putting mORMot on top of existing databases

#16 2013-05-29 17:04:23

Re: Putting mORMot on top of existing databases

#17 2013-05-29 17:25:29

Re: Putting mORMot on top of existing databases

#18 2013-05-29 17:40:45

Re: Putting mORMot on top of existing databases

#19 2013-05-30 00:54:53

Re: Putting mORMot on top of existing databases

#20 2013-05-30 10:04:58

Re: Putting mORMot on top of existing databases

#21 2013-05-30 15:33:41

Re: Putting mORMot on top of existing databases

#22 2013-06-24 17:00:22

Re: Putting mORMot on top of existing databases

#23 2013-06-24 20:45:32

Re: Putting mORMot on top of existing databases

#24 2013-06-25 18:56:41

Re: Putting mORMot on top of existing databases

Board footer