mORMot Open Source

wxinix

Member

Registered: 2020-09-07

Posts: 121

How to get a string copy of CreateSQLMultiIndex

When calling  TRestServerDB.CreateSQLMultiIndex and TRestServerDB.CreateMissingTables,  is it possible to obtain a string copy of the generated SQL script?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: How to get a string copy of CreateSQLMultiIndex

No, this is a stand-alone method.
On some platforms (e.g. TRestStorageMongoDB) it works without any SQL because the database is No-SQL.

But you can get the SQL from TSqlDBConnectionProperties.SqlAddIndex if you need to.

Why do you need this SQL?

wxinix

Member

Registered: 2020-09-07

Posts: 121

Re: How to get a string copy of CreateSQLMultiIndex

No, this is a stand-alone method.
On some platforms (e.g. TRestStorageMongoDB) it works without any SQL because the database is No-SQL.

But you can get the SQL from TSqlDBConnectionProperties.SqlAddIndex if you need to.

Why do you need this SQL?

Thank you very much @ab.  This is cool.

I need this SQL (SqlAddIndex, and SqlCreate - so I can append additional lines for creating custom partitions (of an PostgreSQL db), and adding index for the partitions.

Edit: according to Postgresql document https://www.postgresql.org/docs/10/ddl- … oning.html

For Postgresql,  to create a partitioned parent table,  we would need the DDL like below:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

Then,  create children partitions with extra DDL:

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

...
CREATE TABLE measurement_y2007m11 PARTITION OF measurement
    FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');

Note - child partitions needs indexes to be specified on them, seperately. Indexes are not inherited from parent table (columns are, though).

Given the mORMot framework (TSqlDBConnectionProperties.SqlAddIndex, SqlCreate) - what would be the BEST location to "INSERT" (or callback?)  those extra DDL scripts? Any advice or insights are appreciated. 

Thank you again.

Last edited by wxinix (2021-10-03 23:14:57)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: How to get a string copy of CreateSQLMultiIndex

Usually, what I did is to create the table manually before calling CreateMissingTable.

But a callback could be handy.

For you partition need, check our "sharded" databases. This allows to split written data into several SQLite3 instances. This is not as powerful as PostgreSQL partitions, but it is simple and efficient for read-only partitions of archived/backup/reference data.

Edit: I have added a new event handler to customize TRestStorageExternal.CreateSqlMultiIndex.

wxinix

Member

Registered: 2020-09-07

Posts: 121

Re: How to get a string copy of CreateSQLMultiIndex

Usually, what I did is to create the table manually before calling CreateMissingTable.
But a callback could be handy.
Edit: I have added a new event handler to customize TRestStorageExternal.CreateSqlMultiIndex.

Many thanks @ab!

Not sure if it makes sense — Would it be possible to add another callback for TResrServerDB.CreateMissingTables?  The callback is expected to include the table class, and generated SQL passed by reference -- this way, "PARTITION BY RANGE" can be appended,  and extra SQL code can be added for creating child partitions.  The table class is needed - thus by the table class type, the callback can put different/customized partition logic according to the table class.

This seems to be better and organized than directly creating tables before calling CreateMissingTables.

Thank you again.  If you don't think it makes senses, that is also fine. Just let us know your thoughts.

Last edited by wxinix (2021-10-04 14:22:09)