Archive Tables

BIZ TMS Aurelius
1

What is the recommended method for handling archive tables with Aurelius?

I have an app that stores analytical results data for client environmental samples. Each result is stored in one record, with the current record total in the high tens of millions. This data needs to be stored for up to 25 years as it may be recalled for court cases, but usually we are only working with the last years worth of data. I'd like to move all of the older data to an archive table so that it's still instantly available, but keeping the active table leaner and faster.

For this to work well, I'd need to be able to map my TResult class to one of two tables - the active or archive - and be able to map the entity at run time to either of the tables. Is this possible at all? Maybe with some mapping explorer magic?

  [Entity]
  [Table('RESULTS')]
  [Sequence('RESULTS_GEN')]
  [Id('FRESULTS_ID', TIdGenerator.IdentityOrSequence)]
  TResult = class
  ...........
  end;

  or

  [Entity]
  [Table('RESULTS_ARCHIVE')]
  [Sequence('RESULTS_GEN')]
  [Id('FRESULTS_ID', TIdGenerator.IdentityOrSequence)]
  TResult = class
  ...........
  end;

2

It depends on how you want to deal with such data in your code. There are some alternatives. Things you can do, for example:


1. Have two completely different classes

2. Have two explorers, both with the same class, but change the table name where such class is mapped to. You use either one or another explorer

3. You could have a base TResultBase class and inherit two different classes from it (TResult and TResultArchive). You put all common code in the base class (for processing purposed) and in the descendant classes you just do the attribute mapping. 

As I said you should tell what is best for you depending on how you are going to use those classes.
3

I'd like to keep the same class as this would simplify changes my existing code (close to 1 mil loc), so option 1. is out.

I had considered 2 and 3 in theory, but assumed that 3 wouldn't be workable because with the common fields being in the base class (with the associated mappings), I would be tied to a single mapping. I may be wrong on that.

Option 2. sounds like the most suitable for my needs, but I can't see how to do that. I would know in advance of the call whether my results were in the active or archive tables. Currently, I have functions such as:

function LoadResults(objectManager: TObjectManager; sampleID: integer): TList<TResult>;
begin
  Result := objectManager.Find<TResult>
                                         .Add(TExpression.Eq('sampleID', sampleID))
                                         .List;
end;

If I could pass in another object manager which had the mappings set to the archive tables, that would be ideal.  How would I change the table mapping to handle this case?

Thanks.

4

About option 3, it's the contrary: you can't have same persistence config, you must redeclare the properties in derived class and add persistence attributes there. So you have what you want in theory: the same base class to have your code worked on, but different persistent options. 


About option 2, simply create a separated mapping explorer instance and change the table name there:

ArchiveExplorer := TMappingExplorer.Create('');
ArchiveExplorer.GetTable(TResult).Name := 'RESULTS_ARCHIVE';
ArchiveManager := TObjectManager.Create(Connection, ArchiveExplorer);

it's worth mentioning that you can keep a global ArchiveExplorer instance for the whole application, no problem with that.
5

That's excellent - option 2 will work for what I need.

Thanks!