mORMot Use Case Question

DavidRM · 2016-12-20 20:39:30

I'm very new to mORMot, so I'm still trying to wrap my head around some of it. And that includes trying to envision how I might build my next project using the framework.

What I envision is a server, with a central database of mounted databases. Though separate, each database uses the same model. That includes the same table names, schema, etc.

A client gets the list of databases from the server (which seems a simple enough model), picks one (or more), and links to it (them).

So there's a central model, which is the available databases (and whatever user/auth or additional information seems useful).

But there's also a model that encompasses what's in a mounted database. And possibly more than one of these active at a time. And, if possible, it might be fun to perform queries across all of them.

Is it possible to do that within a single server? And using the ORM features?

Forgive me if this is vague. I'm trying to keep from getting too bogged in the details.

Thanks!

-David

mpv · 2016-12-20 21:27:39

See Multiple databases in one Mormot server for similar discussion

Last edited by mpv (2016-12-20 21:28:45)

DavidRM · 2016-12-20 21:38:05

I will. Thanks!

-David

DavidRM · 2016-12-20 22:00:37

mpv wrote:

See Multiple databases in one Mormot server for similar discussion

That didn't answer my questions. Also, my case is nowhere near that extreme. At most a dozen external databases, and that's on the long side.

Of course, I'm not even sure if I'm asking the right question.

I'm looking at multiple collections of object models, each tied to a separate database on the server. That is, one set of models tied to the main server (the collection of databases), and a set per open database.

Is that doable in a single client connected to a single server? And can I still use the ORM features?

Thanks.

-David

erick · 2016-12-21 00:22:54

DavidRM wrote:

mpv wrote:
See Multiple databases in one Mormot server for similar discussion
>That didn't answer my questions. Also, my case is nowhere near that extreme. At most a dozen external databases, and that's on the long side.

You can host mysql, oracle, mssql on the same server or different servers, though usually you'd use only one brand at a time because of sanity!

You can access different tables in different databases from the same project. I tend to make one userid running on the mORMot server and grant it access to all required tables.

You can use SQL or ORM. I recommend ORM for new projects, or SQL if you are working with an existing database that doesn't easily support ID or similar as an index.

DavidRM wrote:

>I'm looking at multiple collections of object models, each tied to a separate database on the server. That is, one set of models tied to the main server (the collection of databases), and a set per open database.

Why not make a mega-application that does all these things in one? I mean, you might spend more time migrating things from one DB to another, why not make a program that can access subsets of any of it.

DavidRM wrote:

>Is that doable in a single client connected to a single server? And can I still use the ORM features?

mORMot is incredibly flexible, and that can bite you early in your mORMot career.

PLUG: My mormot book gives examples of both ORM and SQL and will get you started much faster than trying to figure out the online examples by yourself.

ab · 2016-12-21 08:07:15

For your use case, I would recommend:
- define a working server class, with all your process using a dedicated TSQLRestServerDB instance
- define a list of server class instance, with one "root" value for each (serv1, serv2, serv3... or some more explicit names)
- define one "balancing" server class, with the list of server instances, and a main REST interface based service to route to one instance
- expose the list of server instances, and the balancing server instance via a single TSQLHttpServer instance
- use SQlite3 for storage: you will have one file per database, very fast and simple to manage/backup - if you have some legacy databases, use dedicated SynDB SQL statements to retrieve information from them
- you may share some common tables (e.g. user rights) between instances using TSQLRestServer.RemoteDataCreate
- use REST interface based services with explicit methods corresponding to the use cases, defining DTO and running on server side all DB ORM calls, and do not expose the ORM via REST

You are not the first one I know willing to develop such a system.
A dedicated sample may help.

edwinsn · 2016-12-21 09:03:27

It's like a balancing server (the main server), and other real db servers (sub servers)?

I think the architecture would depend on how the clients would access the sub servers, for example, would these sub servers hosted on the same machine or on other physical macines?

DavidRM · 2016-12-21 13:25:17

Thank you for the replies! (And I have the book in my cart on Amazon.)

I will ponder and dig around in the docs and samples some more. Then probably come back with more questions.

-David

Update: I think I'm probably clinging too tightly to "how it was done before". A single database is probably a (much) cleaner design.

Last edited by DavidRM (2016-12-21 14:39:40)

ab · 2016-12-21 17:25:55

A single database may be tempting, but it is often done since setup a database is a heavy task for classical RDBMS (like Oracle or MSSQL).
But if you use mORMot and SQLite3 storage (or MongoDB), you can easily create separated databases, for each client.
It will come from how you define your classes.

Keep in mind that with mORMot, it is very easy to make a clear distinction between logical and physical views, and make proper OOP.

Having separated databases has several advantages:
- easier to backup or purge
- easier to change your hosting (you can change one client location from one server to another)
- more agile for scaling (cloud-like hosting without the cloud)
- you can replicate each database in several nodes (using mORMot replication features) to implement a real-time backup or offline work
- safer design (data of several clients would never mix)
- may be mandatory for regulatory purposes
- is a very good selling point: your client will have its own database!

edwinsn · 2016-12-22 08:03:20

mORMot is more than an ORM, on the db side, it's also a wrapper around SQLite to transform SQLite the single-user DB into a multi-user DB like other so-called enterprise DBs, while keeping the zero-configuration nature of the amazing SQLite

It's also a feature-rich web server.

Sometimes it take a little time to realize that for a newcomer

DavidRM · 2016-12-22 16:58:37

ab wrote:

A single database may be tempting, but it is often done since setup a database is a heavy task for classical RDBMS (like Oracle or MSSQL).
But if you use mORMot and SQLite3 storage (or MongoDB), you can easily create separated databases, for each client.
It will come from how you define your classes.
Keep in mind that with mORMot, it is very easy to make a clear distinction between logical and physical views, and make proper OOP.
Having separated databases has several advantages:
- easier to backup or purge
- easier to change your hosting (you can change one client location from one server to another)
- more agile for scaling (cloud-like hosting without the cloud)
- you can replicate each database in several nodes (using mORMot replication features) to implement a real-time backup or offline work
- safer design (data of several clients would never mix)
- may be mandatory for regulatory purposes
- is a very good selling point: your client will have its own database!

What you're describing is called a multitenant architecture. I'd love to see an example of that using mORMot, especially using Sqlite.

-David

ab · 2016-12-22 17:16:27

I implemented it, but was not aware of the terming.
https://en.wikipedia.org/wiki/Multitenancy

In practice, multitenant server often share a single database.
This is not what I proposed in fact: each client its own storage.
With mORMot and SQLite3, it becomes easy and efficient.

DavidRM · 2016-12-22 18:56:17

It's one of the variations on multitenancy. The separate-database-per-tenant is usually more challenging, and so not the typical approach, because commercial databases tend to take a lot of diskspace and/or other overhead. Sqlite helps to eliminate those issues, I would think.

I was researching this a few months ago. That's when I learned the term.

So you already have an example doing this?

-David

Last edited by DavidRM (2016-12-22 18:58:10)

edwinsn · 2016-12-23 08:34:58

Multitenancy is a new term to me too

erick · 2016-12-23 13:10:45

edwinsn wrote:

Multitenancy is a new term to me too

It's new to me too, but it makes sense instantly!

My suggestion of using multitenancy is because people like using on application where they can, rather than a bunch of smaller applications they might have to sign into.

In practice, I find I struggle between making small single-purposed apps that are well defined, and later addressing larger needs with a single-mega-app.

If you have single sign on (SSO), like CAS or OpenID, it makes less of an inconvenience for the user to use multiple apps as they don't have to sign in each time.

Erick

DavidRM · 2016-12-27 19:30:37

What I'm after isn't truly multitenancy. My apologies for the digression.

I will attempt to describe what I'm trying to do better, since even after reading (and rereading) the SAD, and looking at the various samples, I'm still not sure where to start or if what I want to do is even feasible.

The entry point is a central database that has some global settings but is mostly a list of databases.

The physical location of these databases may vary (on the hard drive, or on the LAN, or whatever), but each of them has the same schema. In other words, they would have the same ORM model definitions and relationships (so the same tables with the same names).

The client software connects to the central database and retrieves the list of available database.

The client may open one or more of these databases simultaneously.

Objects retrieved from a database need to know which database they came from (for updating purposes).

Now, from what I've read, a particular TSqlRecord class is assigned to a single Model, which is assigned to a single Database/Server. That appears to me to be a limiting factor. Is that correct? Or can a TSqlRecord be associated with multiple models? Or can a single model be associated with multiple databases?

In the SAD there is an example (in 7.3.6) given of creating specific types of TSqlRecord for a specific database, to differentiate them between multiple databases. As I understand it, this doesn't work for me.

Do I need separate models and associated servers? That is, one model+server for the central database, and another model that can be associated with the other databases? If so, do those have to be separate exe's? Or can I just create servers as necessary?

Thank you for your help.

-David

EMartin · 2016-12-27 19:59:14

Try use "root" for each databases:

1) create main server and database
2) create TSQLModel with "root"
3) create TSQLRestServer with created TSQLModel
4) create TSQLHttpServer with created TSQLRestServer
5) repeat steps 2-4 for each database changing the "root"

All HTTP servers can share the same HTTP configuration (port, etc.), the REST server is different because the "root" change.

I think this can work.

Best regards.

Esteban

ab · 2016-12-27 20:09:48

@DavidRM
No, you can re-use the very same TSQLRecord in several TSQLModel.
Only each TSQLRest instance should have its own TSQLModel instance.
But several TSQLRest instances can have TSQLModel with the same content, i.e. the same TSQLRecord.
Only requirement is that each TSQLModel should have its own "root" name, as EMartin explained.

DavidRM · 2016-12-27 20:49:48

OK. I think I understand. I'll experiment and see how it goes.

Thank you, Esteban and Arnaud.

-David

edwinsn · 2016-12-28 05:31:50

I think @EMartin's idea is very simple and workable! The "root" as mentioned by EMartin is actually the URI for the data model, and each model/rest-server/http-server combination can have it's own URI, for example, "db1", "db2", "db3"....

It's not not clear to me that, how to create a TSQLHttpClient connection to the specified "db1" or "db2"?...

Last edited by edwinsn (2016-12-28 05:39:34)

ab · 2016-12-28 13:44:00

Each client should have its TSQLModel.Root set to the specific "db?" value.
Then the right URI will be computed to access the expected server.

DavidRM · 2016-12-28 16:09:05

EMartin wrote:

Try use "root" for each databases:
1) create main server and database
2) create TSQLModel with "root"
3) create TSQLRestServer with created TSQLModel
4) create TSQLHttpServer with created TSQLRestServer
5) repeat steps 2-4 for each database changing the "root"
All HTTP servers can share the same HTTP configuration (port, etc.), the REST server is different because the "root" change.
I think this can work.
Best regards.
Esteban

Since each non-root database will have its own URI, can they have their own authentication, as well? Since each sub-database will have its own users/passwords.

I'm guessing this would involve a separate set of authentication tokens passed back and forth?

Part of the complication is caused by legacy. As many complications are...

Thanks.

-David

ab · 2016-12-28 17:31:10

About authentication process itself over the wire, each client and each server will have its own challenge and token.

But as we wrote, you may either have each server have its own credentials (i.e. TSQLAuthUser and TSQLAuthGroup tables), or share those tables across the servers, using redirection for them.

DavidRM · 2016-12-28 18:07:14

Ah, OK. Thanks.

DavidRM · 2017-01-18 22:22:47

So I've implemented something along these lines. There is a single HTTP server that manages a collection of models, each with its own root.

My question now is: How do I connect from the client side?

My first stab at it uses a TSQLHttpClient per database. One for the primary database on the server, and one for each of the sub-databases. That's not working for the sub-databases. I can create the TSQLHttpClient object, but when I try ServerTimeStampSynchronize, it returns False. Also, attempts to Retrieve from the TSQLHttpClient object don't generate an error, but they also don't work.

Any hints?

Thanks!

-David

ab · 2017-01-19 09:34:45

Each client will have its own TSQLModel, with its own root.

You have something wrong with your client side models...

DavidRM · 2017-01-19 19:17:03

ab wrote:

Each client will have its own TSQLModel, with its own root.
You have something wrong with your client side models...

Unique roots turned out not to be the issue. Instead, I had roots with spaces in them. I took the spaces out and everything worked as expected.

Is that specified somewhere? That roots don't like spaces? Or should I have known that based on how roots are used as URIs? Or both?

-David

ab · 2017-01-19 20:07:18

Not, it was not specified as such directly, but it is implied by how URI are encoded.
URI could not contain spaces - spaces are replaced by the + character.
So in the TSQLModel.Root value, there should not be any URI not friendly characters, like spaces or accents.

I've made the documentation clear about that, and just added an explicit check, and raise an EModelException for this.
See https://synopse.info/fossil/info/e30de8cb21

emaxx · 2017-01-20 09:23:40

@ab, there is a BREAKING CHANGE introduced with this last commit https://synopse.info/fossil/info/e30de8cb21

TSQLModel.Root=""AAV/V1"" contains URI unfriendly chars".

A versioning of our services with an URI-Fragment is not possible any more.

ab · 2017-01-20 15:02:05

This is indeed a regression, due to a paranoid (but safe) check.
Sorry for the mistake.

It does make sense that "/" character should be allowed.
I've added it back.
See https://synopse.info/fossil/info/acf518135f

emaxx · 2017-01-20 15:44:14

@ab, thank you for the quick fix und thank you for this excellent piece of software

mORMot Open Source

#1 2016-12-20 20:39:30

mORMot Use Case Question

#2 2016-12-20 21:27:39

Re: mORMot Use Case Question

#3 2016-12-20 21:38:05

Re: mORMot Use Case Question

#4 2016-12-20 22:00:37

Re: mORMot Use Case Question

#5 2016-12-21 00:22:54

Re: mORMot Use Case Question

#6 2016-12-21 08:07:15

Re: mORMot Use Case Question

#7 2016-12-21 09:03:27

Re: mORMot Use Case Question

#8 2016-12-21 13:25:17

Re: mORMot Use Case Question

#9 2016-12-21 17:25:55

Re: mORMot Use Case Question

#10 2016-12-22 08:03:20

Re: mORMot Use Case Question

#11 2016-12-22 16:58:37

Re: mORMot Use Case Question

#12 2016-12-22 17:16:27

Re: mORMot Use Case Question

#13 2016-12-22 18:56:17

Re: mORMot Use Case Question

#14 2016-12-23 08:34:58

Re: mORMot Use Case Question

#15 2016-12-23 13:10:45

Re: mORMot Use Case Question

#16 2016-12-27 19:30:37

Re: mORMot Use Case Question

#17 2016-12-27 19:59:14

Re: mORMot Use Case Question

#18 2016-12-27 20:09:48

Re: mORMot Use Case Question

#19 2016-12-27 20:49:48

Re: mORMot Use Case Question

#20 2016-12-28 05:31:50

Re: mORMot Use Case Question

#21 2016-12-28 13:44:00

Re: mORMot Use Case Question

#22 2016-12-28 16:09:05

Re: mORMot Use Case Question

#23 2016-12-28 17:31:10

Re: mORMot Use Case Question

#24 2016-12-28 18:07:14

Re: mORMot Use Case Question

#25 2017-01-18 22:22:47

Re: mORMot Use Case Question

#26 2017-01-19 09:34:45

Re: mORMot Use Case Question

#27 2017-01-19 19:17:03

Re: mORMot Use Case Question

#28 2017-01-19 20:07:18

Re: mORMot Use Case Question

#29 2017-01-20 09:23:40

Re: mORMot Use Case Question

#30 2017-01-20 15:02:05

Re: mORMot Use Case Question

#31 2017-01-20 15:44:14

Re: mORMot Use Case Question

Board footer