Too many tables TSQLModel error

Kobe · 2014-12-18 11:59:24

Hello,

I got the error: EModelException: 'TSQLModel has too many Tables: 257>256'.

How can I solve it? Because I need all those tables in the same SQLite3 database and TSQLRestServerDB only accepts one model.

It's a ERP and all this tables cannot be modified.

Last edited by Kobe (2014-12-18 12:01:07)

ab · 2014-12-18 12:34:02

256 tables?
I suppose you are not using data sharding.
Some data refactoring may be a good idea.
The ORM would not be so beneficial (or would it even work, with e.g. the restriction of integer primary ID?).
What you should do is perhaps to use raw SQL, and define your work as SOA services, and not as RESTful direct ORM access.

Sadly, we rely on Delphi sets to store some information about tables (and fields).
And Delphi sets are restricted to up to 256 items only.
So we would need a lot of refactoring to support more than 256 bits/tables.

Kobe · 2014-12-18 13:02:35

Is there a way to permit more than 1 model in the TSQLRestServerDB?

ab · 2014-12-18 13:46:27

No, it is not possible nor needed.

Kobe · 2014-12-19 15:42:23

How can I use the ORM the select multiple sqlite databases (each database would have its own TSQLRestServerDB class) ?

ab · 2014-12-19 21:01:59

You just create several models.

Kobe · 2014-12-20 01:04:09

OK, how can I do join selects if the tables are in differents databases (TSQLRestServerDB) ?

ab · 2014-12-20 09:06:28

This is not possible due to how virtual tables are implemented.

What you should do is perhaps to use raw SQL, and define your work as SOA services, and not as RESTful direct ORM access.
Ensure you did read and understand http://synopse.info/files/html/Synopse% … l#TITL_106

triguinhu · 2018-03-09 14:18:46

To use ORM we can not break the limit of 256 tables? An ERP application easily passes 256 tables .. When using SQL manual via SOA-based services, how do I keep the possibility of exchanging databases with a line of code? It's possible ? How to handle manual SQL via SOA without losing the possibility of the application running in multiple databases?

ab · 2018-03-09 18:43:39

The limit of 256 tables is due to the limit size of a pascal set, which are used by the ORM.
So there is no plan to make a bigger limit.

In practice, we often create several databases, sometimes just with one table. Or even one table split into several database files, one per client. With SQLite3, it is very easy to do, and also safer to maintain, archive and backup.
Data is to be stored as aggregate, so you need no join, and use a document-based approach with rich fields stored as JSON or binary, containing objects, array of objects, dynamic array of records, or any unstructure TDocVariant content.

This is how data sharding, and proper DDD/Microservices architecture works.

triguinhu · 2018-03-12 18:32:19

Ab,

I think this time I understood the concept, but ..
How would you implement that for an ERP with Mormot, using ORM? multiple models, each with its TRestServerDB, and in HttpServer, multiple instances of TRestServerDB? In that case would there be no limitation of 256 instances of TRestServerDB too? I'm confused ..

Last edited by triguinhu (2018-03-12 18:34:04)

mpv · 2018-03-12 20:13:41

See for example how SAP organize their ERP http://www.tcodesearch.com/sap-tables
Each module can be a different model. No-one excited 256 tables.

ab · 2018-03-12 20:24:50

Take a look at how MicroServices work, for instance.
Each one has its own database.

So this is a design / architecture principle.
If you have a single big monolithic database, you are facing a big issue: it is against all common today's best practices.

Take a look at how classes should work, if you follow the SOLID principle.
Not one big class implementing everything, but several smaller classes, each one doing its own process.

triguinhu · 2018-03-13 12:01:30

Guys, I got the concept! Perfect, sorry for ignorance, but it's all new to me! I took a look at the SOLID and DDD principle, the MVC concept really makes sense to me now! I have one doubt, I realized that I will have several models and several REST servers. The relationship between the HTTP Server and each REST Server is one to one? In that case would I have multiple HTTP servers responding on multiple TCP ports through my application? or would it be an HTTP Server only?

ab · 2018-03-13 19:01:17

You can host several REST servers in a single HTTP server, sharing the same port, but each with its own root name - see TSQLModel.Root.

macfly · 2018-03-13 21:53:09

About sharding....

How queries are made in a slqlite db?

I suppose they are emulated via "LIKE"

Example: To find name=John in a field stored as doc, the query sent will be LIKE %\ "name \": \ "Jhon \"%

If this is the case, there is no loss of performance compared with a pivot table?

ab · 2018-03-13 22:17:05

The most efficient is just to use IDs (i.e. Int64 for the ORM) with proper indexes.

macfly · 2018-03-14 16:53:44

If I am using a pivot table, a query to find name = Jhon (in the pivot table) would be:

Select ...
FROM my_main_table
INNER JOIN my_pivot_table (...)
WHERE my_pivot_table.name = ""John""

Using sharding where the docvariant data are stored in a field named data the query would be this below?

Select ...
FROM my_main_table
WHERE my_main_table.data LIKE "%\""name\"": \ ""Jhon\""%"

If I understood the documentation correctly, to make queries using methods like JsonGet / JsonHas ORM need to parse all rows?

ab · 2018-03-14 18:59:32

No, the idea is to store the same data several times, in each aggregate.

mORMot Open Source

#1 2014-12-18 11:59:24

Too many tables TSQLModel error

#2 2014-12-18 12:34:02

Re: Too many tables TSQLModel error

#3 2014-12-18 13:02:35

Re: Too many tables TSQLModel error

#4 2014-12-18 13:46:27

Re: Too many tables TSQLModel error

#5 2014-12-19 15:42:23

Re: Too many tables TSQLModel error

#6 2014-12-19 21:01:59

Re: Too many tables TSQLModel error

#7 2014-12-20 01:04:09

Re: Too many tables TSQLModel error

#8 2014-12-20 09:06:28

Re: Too many tables TSQLModel error

#9 2018-03-09 14:18:46

Re: Too many tables TSQLModel error

#10 2018-03-09 18:43:39

Re: Too many tables TSQLModel error

#11 2018-03-12 18:32:19

Re: Too many tables TSQLModel error

#12 2018-03-12 20:13:41

Re: Too many tables TSQLModel error

#13 2018-03-12 20:24:50

Re: Too many tables TSQLModel error

#14 2018-03-13 12:01:30

Re: Too many tables TSQLModel error

#15 2018-03-13 19:01:17

Re: Too many tables TSQLModel error

#16 2018-03-13 21:53:09

Re: Too many tables TSQLModel error

#17 2018-03-13 22:17:05

Re: Too many tables TSQLModel error

#18 2018-03-14 16:53:44

Re: Too many tables TSQLModel error

#19 2018-03-14 18:59:32

Re: Too many tables TSQLModel error

Board footer