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

Kobe
Member
Registered: 2013-04-08
Posts: 38

Too many tables TSQLModel error

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)

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: Too many tables TSQLModel error

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.

Offline

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

Kobe
Member
Registered: 2013-04-08
Posts: 38

Re: Too many tables TSQLModel error

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: Too many tables TSQLModel error

No, it is not possible nor needed.

Offline

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

Kobe
Member
Registered: 2013-04-08
Posts: 38

Re: Too many tables TSQLModel error

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: Too many tables TSQLModel error

You just create several models.

Offline

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

Kobe
Member
Registered: 2013-04-08
Posts: 38

Re: Too many tables TSQLModel error

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: Too many tables TSQLModel error

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

Offline

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

triguinhu
Member
From: Brazil
Registered: 2016-07-28
Posts: 27

Re: Too many tables TSQLModel error

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?

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: Too many tables TSQLModel error

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.

Offline

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

triguinhu
Member
From: Brazil
Registered: 2016-07-28
Posts: 27

Re: Too many tables TSQLModel error

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 .. roll

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

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: Too many tables TSQLModel error

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: Too many tables TSQLModel error

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.

Offline

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

triguinhu
Member
From: Brazil
Registered: 2016-07-28
Posts: 27

Re: Too many tables TSQLModel error

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?

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: Too many tables TSQLModel error

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.

Offline

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

macfly
Member
From: Brasil
Registered: 2016-08-20
Posts: 374

Re: Too many tables TSQLModel error

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?

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: Too many tables TSQLModel error

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

Offline

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

macfly
Member
From: Brasil
Registered: 2016-08-20
Posts: 374

Re: Too many tables TSQLModel error

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?

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: Too many tables TSQLModel error

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

Offline

Board footer

Powered by FluxBB