You are not logged in.
- Topics: Active | Unanswered
Pages: 1
#1 2019-06-01 15:15:16
- xiwuping
- Member
- Registered: 2018-02-11
- Posts: 32
How to do with "rotated" tables using mORMot?
Need advice from mORMot experts.
I have a database server that archives real time simulation data in multiple tables. Each table has incoming data of 10 million records per day. Because the data are ever-increasing with time, I have to rotate the table names by year-month. That means, every month, a new table will be created, with the name as original_table_name_yyyymm. For example, original_table_name_201901 representing the data archived for January, 2019.
So I have a situation that the table names are changing with time.
How can I cope with this situation with mORMot framework? Can it automatically send/get data from tables that have changing names?
Thank you.
Offline
#3 2019-06-01 16:42:06
- xiwuping
- Member
- Registered: 2018-02-11
- Posts: 32
Re: How to do with "rotated" tables using mORMot?
See e.g. how TSQLRestStorageShard (and TSQLRestStorageShardDB) works.
Thank you for the heads up. I am looking at the documentation. For helping dummy users like me to jump start - would it be possible to have a demo project to illustrate the usage?
Offline
#4 2019-06-01 17:08:29
- xiwuping
- Member
- Registered: 2018-02-11
- Posts: 32
Re: How to do with "rotated" tables using mORMot?
TSQLRecord.SQLTableName
Can I override this class function, so as to access the correct rotated table names in an existing database server? Inside the overridden SQLTablename function, I can apply the same table name rotation rules as the database server (which I don't have control), so the ORM can connect to the correct table with names changing over time.
If I do so, any forseeable side-effects?
Last edited by xiwuping (2019-06-01 17:10:29)
Offline
#5 2019-06-02 08:17:06
- edwinsn
- Member
- Registered: 2010-07-02
- Posts: 1,218
Re: How to do with "rotated" tables using mORMot?
Really interesting question.
What do you mean by not having control of the server? Is the server also based on mORMot?
A thought:
- Pause the client, then perform the table rotating (renaming) on the db level (not the ORMot leve), empty the cache (both the client and server side), and resuming using the clients.
Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.
Offline
#6 2019-06-02 11:04:09
- xiwuping
- Member
- Registered: 2018-02-11
- Posts: 32
Re: How to do with "rotated" tables using mORMot?
Really interesting question.
What do you mean by not having control of the server? Is the server also based on mORMot?
A thought:
- Pause the client, then perform the table rotating (renaming) on the db level (not the ORMot leve), empty the cache (both the client and server side), and resuming using the clients.
The database server is just a standard MySQL database server. The tricky part is that - there is a separate, independent service not under my control which will constantly check the database server, and if the relevant tables have not been rotated (for example, at the beginning midnight of a new rotation time such as start of a new month), that service will CREATE new rotated table automatically. This process is not controlled or managed within mORMot framework.
So, from mORMot side, if the target table has already been rotated, it will do nothing and just connect to that table. If the table SHOULD have been rotated, but has NOT been, mORMot will create a new rotated table. Is this doable? Any advice? Or there is some fundamental problem with the overall scheme?
Last edited by xiwuping (2019-06-02 11:04:35)
Offline
#7 2019-06-03 07:46:08
Re: How to do with "rotated" tables using mORMot?
Use several TSQLRestServerDB instances, once per database. Then do the rotation as you expect, by creating/freeing the instances.
This is what does TSQLRestStorageShard (and TSQLRestStorageShardDB), from an unique TSQLRest-like entry point.
Offline
Pages: 1