#1 Re: mORMot 1 » LoadFromYaml undeclared » 2022-05-24 12:03:33

Would TOML be also considered in (near or far) future?

#2 Re: mORMot 1 » what happened to ab? » 2022-05-06 14:15:23

Yes.  He deserves a break but not seeing him around for a week is concerning, given all the madness going on.  Hope things are well with him.

Leslie7 wrote:

+1

He is very-very productive, but everyone needs time to regenerate off the regular paths. I am hoping that is all.

#3 mORMot 1 » what happened to ab? » 2022-05-06 13:18:22

wxinix
Replies: 7

He seems disappeared fro his daily routine this whole week.  Hope he is well.

#4 mORMot 1 » Delphi compilation error with latest mORMot (just checked out today) » 2022-03-07 20:33:11

wxinix
Replies: 2

Delphi 11, x64,

[dcc64 Error] mormot.db.raw.sqlite3.static.pas(1001): E2065 Unsatisfied forward or external declaration: 'sqlite3_error_offset'
[dcc64 Fatal Error] WxWorks.Core.Orm.pas(38): F2063 Could not compile used unit 'mormot.db.raw.sqlite3.static.pas'

#5 mORMot 1 » Delphi warning for mormot.net.sock » 2022-02-04 15:11:02

wxinix
Replies: 1

[dcc64 Warning] mormot.net.sock.pas(1433): W1073 Combining signed type and unsigned 64-bit type - treated as an unsigned type

#6 Re: mORMot 1 » mormot HttpGet, how to change connection timeout? » 2022-01-10 18:14:31

@tbo  hi Thomas, thank you again.

I ended up using THttpClientSocket class directly, instead of the function HttpGet.

THttpClientSocket has a constructor that allows specifying a non-default time out.

#7 Re: mORMot 1 » mormot HttpGet, how to change connection timeout? » 2022-01-09 23:55:29

@tbo  Thank you for the heads up.

I am looking at mormot.net.client.HttpGet function, which does not provide a parameter for setting connection time out. Internally, HttpGet ->OpenHttpGet->OpenHttp->TCrtSocket.Open->THttpClientSocket.Create(0)

I understand I can change HTTP_DEFAULT_RECEIVETIMEOUT to a different value, but I really don't want to change that default value.

Is it an intentional design that mormot.net.client.HttpGet does not allow setting a non-default timeout?  @ab  Do you think it make sense to add an extra timeout parameter, so we can use a non-default value?

#8 mORMot 1 » mormot HttpGet, how to change connection timeout? » 2022-01-09 17:42:51

wxinix
Replies: 4

The default connection timeout for HttpGet appears to be 30 seconds.

How can I change this number?  I'd like to make it smaller.

Thanks!

#10 Re: mORMot 1 » Program Hangs:TSqlDBConnectionProperties.ExecuteNoResult never returns » 2022-01-06 15:02:33

I think there is a bug with function GotoEndJsonItemString(P: PUtf8Char): PUtf8Char;

Line 3231, mormot.core.json:

     else if P^ = #0 then
        break
      else if P^ <= ' ' then
        continue;   // <== once the loop end up in this branch,  the loop will never be able to break, because P stops increment!!

#11 Re: mORMot 1 » Program Hangs:TSqlDBConnectionProperties.ExecuteNoResult never returns » 2022-01-06 13:15:03

Thank you.

I downloaded latest commit as of 8:11am, Jan 6, 2022 (Eastern Time).  This seems to fix the "hanging problem" of the unit test case (as reported above), and that test case was able to pass.

But then, another test case fails, at mormot.core.json, line 3196-3236, function GotoEndJsonItemString(P: PUtf8Char): PUtf8Char;

it hangs inside that function, and was never able to return.

The following is the call stack:

mormot.core.json.GotoEndJsonItemString
mormot.orm.core.TOrmTableJson.ParseAndConvert
mormot.orm.core.TOrmTableJson.CreateFromTables
mormot.orm.storage.TRestStorageInMemory.LoadFromJson
mormot.orm.storage.TRestStorageInMemory.ReloadFromFile
mormot.orm.storage.TRestStorageInMemory.Create
...

Any thoughts?

#12 mORMot 1 » Program Hangs:TSqlDBConnectionProperties.ExecuteNoResult never returns » 2022-01-06 03:11:01

wxinix
Replies: 6

This used to work, but it fails my unit testing today - so I guess this is some newly introduced bug.

When calling ExecuteNoResult,  it never returns.

Tracing the code,  it seems the program hangs at mormot.db.sql,  line 3492, TSqlDBConnectionProperties.NewThreadSafeStatementPrepared

function TSqlDBConnectionProperties.NewThreadSafeStatementPrepared(const aSql:
  RawUtf8; ExpectResults, RaiseExceptionOnError: boolean): ISqlDBStatement;
begin
  result := ThreadSafeConnection.NewStatementPrepared(aSql, ExpectResults,
    RaiseExceptionOnError);
end;

Any advice? Seems like a new bug, or something that breaking changes for the existing code?

#13 mORMot 1 » mormot.crypt.ecc.pas Delphi compiler error » 2022-01-05 02:01:00

wxinix
Replies: 1

[dcc64 Error] mormot.crypt.ecc.pas(5147): E2089 Invalid typecast
[dcc64 Warning] mormot.crypt.ecc.pas(5147): W1021 Comparison always evaluates to False
[dcc64 Fatal Error] mormot.crypt.jwt.pas(760): F2063 Could not compile used unit 'mormot.crypt.ecc.pas'

#14 mORMot 1 » ASN.1 "compiler" » 2022-01-05 01:27:27

wxinix
Replies: 1

I saw that there have been quite heavy development activities about mORMot crypto functionalities.

And I notice some limited ASN.1 capability with mORMot.

Is there any possibility to expand that to an ASN.1 "compiler", i.e., generate Pascal types and utility methods for encoding, decoding ASN.1 (such as SNMP Mibs)?

#15 mORMot 1 » CreateAndFillPrepare throws out of memory exception for large table » 2021-12-29 01:55:10

wxinix
Replies: 1

mormot.db.raw.sqlite3, line 6810, had the max memory hard coded as 4096 - is that intentional? 

"TSqlRequest.Execute: output overflow after 512 MB for []'.

function DirectExplainQueryPlan(DB: TSqlite3DB; const aSql: RawUtf8): RawUtf8;
var
  R: TSqlRequest;
  cnt: integer;
begin
  result := R.ExecuteJson(DB, 'explain query plan ' + aSql, true, @cnt, 4096,
    [twoForceJsonExtended, twoIgnoreDefaultInRecord]);
  if cnt = 0 then
    result := ''; // no query plan
end;

#16 mORMot 1 » Some warning message - Delphi 11, mormot.crypt.secure.pas » 2021-12-17 23:28:10

wxinix
Replies: 1

[dcc64 Warning] mormot.crypt.secure.pas(1289): W1010 Method 'Equals' hides virtual method of base type 'TObject'


Not serious but a warning hanging there.

#17 Re: mORMot 1 » TRawUTF8List not saved to DB? » 2021-12-07 14:09:22

@ab - you are right!  After making the constructor override, everything works!

#18 mORMot 1 » TRawUTF8List not saved to DB? » 2021-12-07 05:47:14

wxinix
Replies: 2

I have a TSQLRecord derived class, which has a TRawUTF8List field.

  TConfig = class(TSQLRecord)
  private
    FExcludedMACList: TRawUTF8List;
  public
    constructor Create;
    destructor Destroy; override;
  published
    property ExcludedMACList: TRawUTF8List read FExcludedMACList write FExcludedMACList;
  end;


I tried to save it to a static object list db, but the TRawUTF8List field always saves as "null".  Is there something wrong?

#19 mORMot 1 » Does mORMOt have a better way for checking if str can be converted num » 2021-11-17 16:08:19

wxinix
Replies: 1

I know I can use TryStrToFloat, but is there any other (better, and faster) way provided by mORMot,  to check if a string can be converted to number?

#20 Re: mORMot 1 » Ekon 25 Slides » 2021-11-16 12:13:01

After the official seal of mORMot2, I guess the next big thing on your to-do list is updating its SAD?

Or, is SAD 2.0 available already?

#21 Re: mORMot 1 » Will mORMOT/Delphi work together on Linux? » 2021-11-14 20:39:50

Thank you @ab again for the advice.  I installed Lazarus on Windows and on Kali Linux,  with the docked layout enabled.   It looks quite similar to Delphi, and very neat.

I am surprised and impressed that Lazarus/FPC has progressed this good.  It is great to be able to directly build applications inside Linux.

Thanks again!  Go mORMot~

#22 Re: mORMot 1 » Will mORMOT/Delphi work together on Linux? » 2021-11-12 15:15:34

It seems FPC when using LLVM, would give faster code in some cases. Would you recommend (or against?) using FPC/LLVM for mORMot on Linux x86-64?

#23 Re: mORMot 1 » Will mORMOT/Delphi work together on Linux? » 2021-11-11 21:03:44

Thank you @ab

Forgive my ignorance. Is FPC based on LLVM (or gcc), or it is by itself?  Since it is your favorite, I guess performance wise, it would generate better optimized binary code than Delphi?

#24 Re: mORMot 1 » Will mORMOT/Delphi work together on Linux? » 2021-11-11 17:23:46

Thank you. Will take you advice

So there will be no problem to run mORMot on Kali linux ( since it is a variant of Debian)?

#25 mORMot 1 » Will mORMOT/Delphi work together on Linux? » 2021-11-11 16:57:29

wxinix
Replies: 13

To use mORMot on Linux, do I have to use FPC?

What is the best option on Linux, Delphi, or FPC?

#26 mORMot 1 » FastMove that claims to be 50% faster than mORMot » 2021-11-01 12:34:15

wxinix
Replies: 1

https://github.com/dbyoung720/FastMove

@ab  comments?  How possible it can be 50% faster than mORMot?

#27 Re: mORMot 1 » mormot.core.log - EventArchiveSynLZ bug? » 2021-10-28 20:58:21

ab wrote:

Nice finding.

Please check my latest commit.
Now such global variables are released at program ending, in mormot.core.base finalization, so nested dependencies should work as expected.

Yes it is fixed.

How did you make it happen that mormot.core.base.finalization is guaranteed to be called last?

#28 Re: mORMot 1 » mormot.core.log - EventArchiveSynLZ bug? » 2021-10-28 17:26:48

ab wrote:

TAlgoSynLZ.AlgoCompressDestLen exists.

OK.  This mysterious problem that has been bothering me now comes to light.

FastMM5 is INNOCENT.  It is mORMot's fault.

mormot.core.buffers, line 5329, inside TAlgoCompress.FileCompress:

 SetString(dst, nil, AlgoCompressDestLen(Head.UnCompressedSize)); // Calling on the global variable AlgoSynLZ.AlgoCompressDestLen method

This invocation can happen during system FINALIZATION stage. That that is the problem, because the global variable declared at line 473 of mormot.core.buffers

  AlgoSynLZ: TAlgoCompress; 

AlgoSynLZ is instantiated during mormot.core.buffers UNIT INITIALIZATION, and destroyed during mormot.core.buffers UNIT FINALIZATION.

This means,  mormot.core.buffers can be un-loaded, but AlgoSynLZ is still referenced, and called from some other unit's finalization.

This is exactly the reason for the mysterious access violation, because AlgoSynLZ is referenced by TSynLogFamily.Destroy, which is called as part of the calling stack of  mormot.core.rtti.Finalization, which is AFTER mormot.core.buffer.Finalization.  At this point, AlgoSynLZ has already been destroyed.


@ab  advice?  This seems a design issue on mORMot side, it should not be dependent on a specific order of unit finalization.

#29 Re: mORMot 1 » mORMot2 SQLite Engine, MREW lock question » 2021-10-17 23:37:13

It is a time-series in-memory (mORMot SQLite direct in-mem) DB - the data are from roadway network sensors that report data every 1 minutes - so the data are WRITTEN to the DB every minute

There are 6000 sensors.

Every 1 minute, I need to calculate a statistical measure using the latest 20 minutes data — this query is for for the purpose of retrieving (READ) the lastest 20 minutes data. It is like a 20-minutes rolling window moving forward at 1 minute step in real time.

The database is not a read only DB - it keeps receiving new data every 1 minutes from those 6000 sensors. So I guess “replication” is really not meaningful here?

#30 Re: mORMot 1 » mORMot2 SQLite Engine, MREW lock question » 2021-10-17 20:29:40

@ab thank you very much for the advice.

From the query planning, I saw the index was hit. There is another multi-index (ZID, UnixMinutes, DataType), but the query planner indicates that a simpler index (ZID, UnixMinutes) was selected.

20211017 20242137 DB    mormot.rest.sqlite3.TRestServerDB(047f5ec0) prepared 54us  
SELECT GID,LengthFt,SpeedMph,NumLanes,TraTimeSec
FROM TranscomLinkStatus 
WHERE ZID=? and UnixMinutes>=? and DataType=?  
[{id:3,detail:"SEARCH TranscomLinkStatus USING INDEX IndexTranscomLinkStatusZIDUnixMinutes (ZID=? AND UnixMinutes>?)"}] 

And it takes 116.76ms to fetch 104800 rows - is this speed normal?

20211017 20242144 SQL   mormot.rest.sqlite3.TRestServerDB(047f5ec0) 116.76ms returned 104800 rows as 8.7 MB 
SELECT GID,LengthFt,SpeedMph,NumLanes,TraTimeSec
FROM TranscomLinkStatus 
WHERE ZID=:(1): and UnixMinutes>=:(27241444): and DataType=:('S'):

#31 mORMot 1 » mORMot2 SQLite Engine, MREW lock question » 2021-10-17 14:44:33

wxinix
Replies: 5

I understand that the Server Engine is thread-safe,  with an internal lock when executing SQL.

This lock seems to be a READ-WRITE lock --- it locks up the DB for both READ, and WRITE.  If there are a lot of concurrent READ query coming in from different threads, the performance drops down quickly because of the blocking.  I am looking at mormot.orm.sqlite3, line 1778

   result := DB.LockJson(SQL, ReturnedRowCount); // lock and try from cache

My problem is:   if I have two threads, each running a separate query to fetch some data from different tables:
-  Thead 1:  the query execution would take 200 milliseconds
-  Thead 2:  would have to wait Thread 1 unlock the DB, before running its own query. This means Thread 2 must wait at least 200 milliseconds, which is a waste.

Is it possible to make it a Multi-Read Exclusive Write Lock?  This would improve multi-thread READ access to the DB.

Or mORMot already has some existing solution to resolve this concurrent READ access from different threads? Advice appreciated.

#32 mORMot 1 » What is the best(fastest) way of loading a JSON file into TDocVariant? » 2021-10-14 13:19:36

wxinix
Replies: 1

Using this sample data https://gist.github.com/wxinix/2162c9c1 … b157f1ce6c

The following code takes about 6 ms.  Is that the normal speed?   Any other approaches that might be faster?

  FData: Variant := _JsonFast(AData);
  FDataLC: PDocVariantData := DocVariantData(FData.LC);

#33 Re: mORMot 1 » mORMot2 SynDataSet ? » 2021-10-13 21:48:52

ab wrote:

It was defined to avoid a Data.FMTBcd reference in mormot.ui.rad uses clause.

I have disabled it so I hope it would compile on Dlephi 11.

Yes it works now

#34 Re: mORMot 1 » mORMot2 SynDataSet ? » 2021-10-13 12:32:05

wxinix wrote:

hi @ab

mormot.ab.rad.pas,  line84

type
  TBcd = FMTBcd.TBCD;

gives the following compiling error for Delphi 11 (at least):
[dcc64 Error] mormot.db.rad.pas(84): E2003 Undeclared identifier: 'FMTBcd'
[dcc64 Error] mormot.db.rad.pas(84): E2029 '=' expected but ';' found

Is TBcd = FMTBcd.TBCD even necessary?  FMBCD unit has been referenced in the USES clause!

#35 Re: mORMot 1 » mORMot2 SynDataSet ? » 2021-10-13 12:31:05

hi @ab

mormot.ab.rad.pas,  line84

type
  TBcd = FMTBcd.TBCD;

gives the following compiling error for Delphi 11 (at least):
[dcc64 Error] mormot.db.rad.pas(84): E2003 Undeclared identifier: 'FMTBcd'
[dcc64 Error] mormot.db.rad.pas(84): E2029 '=' expected but ';' found

#36 Re: mORMot 1 » zCloud, OSS web framework that supports auto-scaling & auto-deployment » 2021-10-11 16:10:09

The description in Chinese is quite hard to understand.   But it seems to me that it is a micro-service architecture, with a back-end "cloud"-based server farms?  "Auto-scaling" - refers to that the server farms can transparently grow to handle increasing demands, something like that?

#37 mORMot 1 » BatchAdd bug? (Direct SQL Memory DB Inserts) » 2021-10-10 02:52:53

wxinix
Replies: 1

@ab

This is related to this discussion https://synopse.info/forum/viewtopic.php?id=5998

With the latest performance optimization,  it seems the BatchAdd for Direct SQLite in Memory DB has some problem

The following entity has 10 fields (if counted RowID, then all together 11 fields). However, when BatchAdd to DirectSQL Memory DB,  I got the error : "ESqlite3Exception with message 'Error SQLITE_ERROR (1)"[insert into MyRecord (RowID,CreateAt,WeekTime,GID,ZID,WKT,Attrs,Len,Num,Spd,TTS) values (?,?,?,?,?,?,?,?,?)....
sing 3.36.0 - 9 values for 11 columns, extended_errcode=1'

If I call BatchAdd for an external DB (e.g., postgresql) - for the same ORM entity, everything is working, and the insert was successful.

Any advice?

TMyRecord = class(TOrm)
...
published
  property CreateAt: TDateTimeMS;
  property WeekTime: Integer;
  property GID: Int64;
  property ZID: Integer;
  property WKT: RawUtf8;
  property Attrs: Variant;
  property Len: Double;
  property Num: Double;
  property Spd: Double;
  property TTS: Double;
end;

#38 Re: mORMot 1 » Can TSqlRecord primary key be changed? » 2021-10-09 13:04:26

ab wrote:

For 7K values per minute, PostgreSQL could be enough.

Even SQlite3, perhaps with our sharding abilities, or gathering streets in several DBs (one SQLite3 DB file per 1K street), or one DB file per year.

Thank you - I am also thinking of exploring mORMot sharding abilities - but putting 1 year's data into a single SQLiteDB file - the file size would be huge I guess?  No problem with that?

#39 Re: mORMot 1 » Can TSqlRecord primary key be changed? » 2021-10-09 12:46:53

Thank you very much for the advice.  I'll look into it.

The time series data is generated from sensors deployed at city streets (6~7K streets), at an interval of every 1 minute - so the ID burst is really happening every 1 minute, thus we need 6K new IDs to be generated every 1 minute.  Ah - I mis-stated - the daily data increment is not 100 million but 10 million records/day (3650 million/yearly).

#40 Re: mORMot 1 » Can TSqlRecord primary key be changed? » 2021-10-09 12:22:56

@ ab

I understand mORMot needs to have the 64bit integer ID as the primary key

Does it have to be continuously incremented?

Is there a way to encode the month number (1-12) in it?

#41 Re: mORMot 1 » Can TSqlRecord primary key be changed? » 2021-10-09 12:03:43

mpv wrote:

Postgres (and most or RDBMS) not allows several primary keys on table. The only way I see is to have primary key what includes partition column, and another non primary unique key for ID column as required by ORM.

@wxinix - what is your goal of using partitions? From my experience it needed very rarely,  mostly for historical data (partition by years/month) in case table contains > 100 millions of rows. For data isolation Postgre RLS can be used, and it transparent for ORM

I tried and it doesn’t work with postgresql

The partition key must be part of the primary key and any unique constraint.

I have an ever-increasing time series database - at a rate of 100 million records per day - so I need to partition it for query performance. I need to keep at least 1 year data (365000 million records)

#42 mORMot 1 » Can TSqlRecord primary key be changed? » 2021-10-09 05:07:28

wxinix
Replies: 12

The default primary key is "ID",  can we include more fields as the primary key?

I am asking this because, in order to partition postgresql db, 

https://www.postgresql.org/docs/13/ddl- … oning.html , Section 5.11.2.3, says:

- Unique constraints (and hence primary keys) on partitioned tables must include all the partition key columns. This limitation exists because the individual indexes making up the constraint can only directly enforce uniqueness within their own partitions; therefore, the partition structure itself must guarantee that there are not duplicates in different partitions.

In other words,  in order to partition,  the partition key must be part of the primary key.

I guess the answer is NO.  Then this leads to the conclusion that we cannot partition postgresql db with the mORMot generated tables, with this primary key requirement.

#43 Re: mORMot 1 » Using mORMot2 framework for PostgreSQL partitioned tables » 2021-10-08 12:41:52

ab wrote:

The easiest is to define your callback in the context of the TRestServer instance, so that it can access the TModel and therefore find the TOrmClass from the table name.

That is what I thought, but I just was just asking in case there might be more elegant way from mORMot2 framework side.

#44 Re: mORMot 1 » Using mORMot2 framework for PostgreSQL partitioned tables » 2021-10-08 08:24:21

@ab Is there a way otherwise elegant to obtain the TOrmClass info from inside the callbacks?

#45 Re: mORMot 1 » Using mORMot2 framework for PostgreSQL partitioned tables » 2021-10-06 20:38:50

In the OnTableCreate handler,  what is the best way to determine the Table Creation result (True / False)?

TRestStorageExternal has a method ExecuteDirect which would return a boolean, but it is not accessible from TSqlDBConnectionProperties.

function TMormotOrmModel.OnTableCreate(ASender: TSqlDBConnectionProperties; const ATableName: RawUtf8; const
  AColumns: TSqlDBColumnCreateDynArray; const ASQL: RawUtf8): Boolean;
begin
   ASender.ExecuteNoResult(ASQL, []); // <<--- how to determine if this table-creation Query is successful or not?   
end;

Edit:

Is it possible to changed the callback parameter const ATableName: RawUtf8 to const ATable: TOrmClass?

#46 Re: mORMot 1 » Using mORMot2 framework for PostgreSQL partitioned tables » 2021-10-06 16:41:37

ab wrote:

I have moved external table/field/index creation customization callbacks to TSqlDBConnectionProperties.

Now you should be able to customize the process as you expect.

Thank you ab!

With these callbacks affiliated with TSqlDBConnectionProperties,  it is more logical (than with TRestStorageExternal).  The entire workflow is much better organized.   

Everything works like a CHARM, clean and beautiful.

Thank you again for this wonderful work.

#47 Re: mORMot 1 » Multi-threaded BatchAdd performance » 2021-10-05 21:27:38

ab wrote:

The performance numbers of mORMot 1 - which were already first in their class - are clearly outperformed with mORMot 2.
We handle more then 1 millions of records per second, for both reading and insertion.
big_smile

Congratulations!  @ab

Really, really neat, amazing, and fantastic work!  It rocks!!!!

Wow wow wow! 
Yes Yes Yes! - this time, it is indeed that the Direct SQLite File/InMem is much faster than External SQLite File/InMem!!!!!!
Super, super, super fasttttttttttttttttttttttttt!

Thank you!!!!!!!!!!!!!!!!!!!!!!!!

#48 mORMot 1 » Using mORMot2 framework for PostgreSQL partitioned tables » 2021-10-04 20:03:10

wxinix
Replies: 8

Following this query "How to get a string copy of CreateSQLMultiIndex",  @ab implemented two extra really cool callbacks to facilitate PostgreSQL partitioned tables in mormot.orm.sql.pas :

- TRestStorageExternal.OnEngineCreate, for customize-creating the parent partitioned table
- TRestStorageExternal.OnEngineCreateMultiIndex for customize-creating index for the child partitions

However, some problems on using them - in the following code,  (FServer.OrmInstance as TRestOrmServer).StaticDataServer[LTable] as TRestStorageExternal would return nil (the nil is set at line 594, mormot.orm.server, where fStaticData = nil, causing the result set to be nil).

Any advice or insights?

  VirtualTableExternalRegister(FSQLModel, ATables, FSQLDBConnectionProps);
  FServer := TRestServerDB.Create(FSQLModel, SQLITE_MEMORY_DATABASE_NAME, False, '');

  if FSQLDBConnectionProps.Dbms = dPostgreSQL then
  begin
    for var LTable in ATables do
    begin
      var LStorExt := (FServer.OrmInstance as TRestOrmServer).StaticDataServer[LTable] as TRestStorageExternal; // <=<= This returns LStorExt = nil
      LStorExt.OnEngineCreate := AOnEngineCreate;
      LStorExt.OnEngineCreateMultiIndex := AOnEngineCreateMultiIndex;
    end;
  end;

  FServer.CreateMissingTables;

#49 Re: mORMot 1 » How to get a string copy of CreateSQLMultiIndex » 2021-10-04 09:31:08

ab wrote:

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.

#50 Re: mORMot 1 » How to get a string copy of CreateSQLMultiIndex » 2021-10-03 22:44:34

ab wrote:

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

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.

Board footer

Powered by FluxBB