How do I access more than 64 tables in one database?

Tohang · 2011-03-16 10:52:21

What is the maximum TSQLModel Record in formation, whether the 64 definitions of records?
if N>sizeof(SUPERVISOR_ACCESS_RIGHTS.Get)*8
What if more than 64 tables in the Database:
result: = TSQLModel.Create ([TSQLSampleRecord,...]);

I am interested to try, but I'm still confused user. Thank you for your guidance.

ab · 2011-03-16 11:23:08

It's not possible yet.

Because
1) TRecordReference use to map the table from its index in the TSQLModel, and the layout expect up to 64 tables;
2) set of records is mapped as Int64 some places in the source code, for instance access rights.

There is a similar limitation for the field numbers in a TSQLRecord: up to 64 fields can be defined.

edwinsn · 2011-03-16 13:57:17

Hi Arnaud,

This is a serious limit, SQLITE itself doesn't have such limits, right? Will remove this limit in the future?

And is there any other limits Synopse SQLITE introduced?

Thanks!

ab · 2011-03-16 17:50:36

There is no limit if you use SQLite directly.

This limit is just from the ORM side.

Tohang · 2011-03-17 02:06:43

If the field already set the maximum number of 64, whether the number of record definitions can not be dynamically allocated?
Because the method dynamically allocating AddTable done SetLength (fTables, i +1);

edwinsn · 2011-03-17 04:28:22

ab wrote:

There is no limit if you use SQLite directly.
This limit is just from the ORM side.

Thanks Arnaud, are you planning to remove this limit? Thanks!

ab · 2011-03-17 06:51:55

Tohang wrote:

If the field already set the maximum number of 64, whether the number of record definitions can not be dynamically allocated?

As I wrote above, and as is stated in TSQLModel description and the documentation, the table index is used is several places, especially TRecordReference:

  /// a Database Model (in a MVC-driven way), for storing some tables types
  // as TSQLRecord classes
  // - share this Model between TSQLRest Client and Server
  // - don't modify the order of Tables inside this Model, if you publish
  // some TRecordReference property in any of your tables
  TSQLModel = class(TObject)
  (...)

Access rights could be of bigger space.

We could not increase the table number without changing the type of TRecordReference into an Int64 for instance.

  /// a reference to another record in any table in the database Model
  // - stored as an 32 bits unsigned integer (i.e. a pointer=TObject)
  // - type cast any value of TRecordReference with the RecordRef object below
  // for easy access to its content
  // - use TSQLRest.Retrieve(Reference) to get a record value
  // - don't change associated TSQLModel tables order, since TRecordReference
  // depends on it to store the Table type in its highest bits
  TRecordReference = type PtrUInt;

This is possible, but do you really need more than 64 tables?

With an ORM, you have usually less tables than in a "regular" relational database, because you can use the high-level type of the TSQLRecord properties to handle some per-row data.
For instance, you won't need a table for every aspect of the configuration of your software (some architects design one configuration table per module or per data table). Just use an unique table (or even better separate the configuration from the data, for all not data-related configuration) in which you store all configuration with some JSON data, or some DFM-like data. See e.g. how the SQLite3Options unit works. With our framework, you can serialize directly any TSQLRecord or TPersistent instance into JSON, without the need of adding this TSQLRecord to the TSQLModel list.
Another example is that you should better not create Master/Detail tables, but just one "master" object with the details stored within, as JSON, records or dynamic arrays (to be included in rev. 1.13).

What I want to emphasize is that you should not think about the ORM like a mapping of an existing DB schema.
This is an usual mistake in ORM design.
But the DB is just one way of your objects persistence.

Don't think tables with simple types (text/number...), but objects with high level types.
Don't think Master/Detail, but logical units.
Don't think "SQL", think classes.

And don't forget the framework is made to have several level of objects.
You should have business-logic level objects at the Client side. Then both business-logic and DB objects at the Server side.
Business-logic objects can be of very high level, encapsulating some SQL views for reading, and accessed via some RESTful service commands for writing.
Another possibility whento access your high-level type, use either custom SQLite3 functions either stored procedures (see TOnSQLStoredProc), both coded in Delphi.

With the next 1.13 version of the ORM, you will have record and dynamic array handling added to the TSQLRecord field properties.
This will make even more simple the inclusion of the Master/Detail data at the record level.
I'll probably add some custom SQL functions in order to search easily the records or dynamic array content, without the need of writing stored procedures by hand.

We could increase the RecordReference type to an Int64, then manage up to 128 or 256 tables per TSQLModel.
But is it really worth it?

For our mORMot ORM, I think we'll handle at least 256 tables, because it will be made for mapping also some existing DB (e.g. Oracle or MySQL), with a classic relational layout, and possibility some huge number of tables.
But for our SQLite3 framework, I'm not sure it's worth it. I'm a bit concerned about backward compatibility here.

edwinsn · 2011-03-19 18:47:12

HI Arnaud,

Sorry for the late response. Yes, I agree that in most apps 64 tables would be enough, but in some cases, my case include, 64 tables is not enough. As you might remember in another post I'm planning to use Synopse sqlite to store various "documents", and the app will continue adding support for more type of documents, it will reach the 64-table limit at the end but 256 will be enough.

Or, if I don't use the permission-related features, can we remove this limit? Maybe via a conditional define?

Thanks!

edwinsn · 2011-03-20 16:11:23

Hi Arnaud,

While it'll be perfect if you can remove the limit (imagine large projects that will have over 64 types of objects that need to persist), I think I've come up with a workaround:

For saving:
1 - On the client side, encode the TSQLRecord-derived objects to JSON strings;
2 - Call the server side method to pass the JSON strings;
3 - On the server side, decode the JSON strings back to objects;
4 - Call the direct sqlite restful server to persist the object

And do the other way around when the clients need to retrieve objects from the server.

It's still quite neat IMHO, thanks to the excellent framework.

ab · 2011-03-21 07:35:03

But I suspect there is still a problem of design in your program. A more generic type of "document" would be better than multiple tables, IMHO.

About JSON serialization, you can do that of course. But not a perfect workaround, because it's more complicated than the plain RESTful architecture.

In all cases, I'll try to upgrade the number of tables to 256. Not a big problem.

ab · 2011-03-21 09:10:15

OK.

Now you can have any number of tables in a TSQLRecord.
By default, I've fixed its maximum to 256. But you can set any value, if it's really necessary (should be some design problem IMHO).

I've also modified the code so that it could be able to handle any number of fields in a table.
By default, maximum is still 64, but it can be set to any value (there are optimized versions of 64, 128 and 256 maximum field count).

See http://synopse.info/fossil/info/aa4b00652c

edwinsn · 2011-03-21 15:11:07

Well done, Arnaud, you are fast.

ab wrote:

But I suspect there is still a problem of design in your program. A more generic type of "document" would be better than multiple tables, IMHO.

Maybe the use of 'documents' misled you, actually, it'll be a program that need to store a wide variety of different types of objects.

ab · 2011-03-21 15:51:43

This does make sense to me.

Thanks for your feedback.

mORMot Open Source

#1 2011-03-16 10:52:21

How do I access more than 64 tables in one database?

#2 2011-03-16 11:23:08

Re: How do I access more than 64 tables in one database?

#3 2011-03-16 13:57:17

Re: How do I access more than 64 tables in one database?

#4 2011-03-16 17:50:36

Re: How do I access more than 64 tables in one database?

#5 2011-03-17 02:06:43

Re: How do I access more than 64 tables in one database?

#6 2011-03-17 04:28:22

Re: How do I access more than 64 tables in one database?

#7 2011-03-17 06:51:55

Re: How do I access more than 64 tables in one database?

#8 2011-03-19 18:47:12

Re: How do I access more than 64 tables in one database?

#9 2011-03-20 16:11:23

Re: How do I access more than 64 tables in one database?

#10 2011-03-21 07:35:03

Re: How do I access more than 64 tables in one database?

#11 2011-03-21 09:10:15

Re: How do I access more than 64 tables in one database?

#12 2011-03-21 15:11:07

Re: How do I access more than 64 tables in one database?

#13 2011-03-21 15:51:43

Re: How do I access more than 64 tables in one database?

Board footer