[erlang-questions] Fwd: mnesia, log tables

[Christian S]

Seems like I accidently missed to reply to the list.

---------- Forwarded message ----------
From: Christian S <chsu79@REDACTED>
Date: Sep 30, 2006 12:55 PM
Subject: Re: [erlang-questions] mnesia, log tables
To: Anton Fedorov <datacompboy@REDACTED>


I have a similar database structure. It is a log for product orders.

I keep one table with orders: {OrderId, Status, LastSerial} and
another table with log events on that order: {{OrderId, Serial},
LogData}

To summarize all data logged about an order I query the OrderId to
find out the LastSerial and then read each logged item {OrderId, 0} to
{OrderId, LastSerial}.

And yes, this is code that performs 1 + LastSerial mnesia:reads, no
hidden query in the mnesia module that reads a range.

You ask for how to find the logged items that happened in a given time
period. This is simply a table that you have to update manually each
time you add a new log item. Compound key as you guessed. The table
would look something like {{OrderId, Period}, BeginSerial, EndSerial}.
This way you can prune old processed periods to keep this "index"
table small. This is something you couldnt do if you instead added an
index on an additional compound column of {OrderId, Period} to each
logg item.

I have often wished for the ability to hook som kind of 'tail
transaction handlers' that can inspect changes to tables and update
these kind of compound indexes behind the curtains. Those are probably
easy to get wrong though. I need to inspect 'rdbms' that Ulf Wiger
keeps teasing about to see what it can offer.



On 9/30/06, Anton Fedorov <datacompboy@REDACTED> wrote:
> Hello, erlang-questions.
>
> How to write effectively log of operations, so later I will able to
> effectively select part for some time and build some statistics?
>
> Mnesia doesn't have, as I see, compound indexes and read of range...
>
> May be just do compound fields? since in general I need in montly
> blocks, add to record key yearmonth (f.e. 200609) and add index for
> it?
>