Обсуждение: Rules to provide a virtual column

This might be a little off topic and it may show a degree of naivety on my
part but I have a small problem with coding a Ruby on Rails application and it
seems to me that the best answer may reside in the database itself.

The situation is this.  A dependent table relationship is episodic.  In other
words, a product might be available for a period of time, then not available,
then available again.  Or, a firm might be a client for a period, then not,
then again.  Or a person might be an employee, then not, then again. Further,
past intervals of activity must be preserved.

The way that we handle this is through two columns in the dependent table;
effective_from and superseded_after.  Thus an active row is retrieved via the
following code:

SELECT * FROM table WHERE ("table"."effective_from <= "current_date" AND
          ( "table"."superseded" IS NULL OR
            "table"."superseded" >= "current_date" ) )

The difficulty arises from the implementation of the Rails generated SQL
SELECTs which freezes any datetime employed therein to the instant that the
model is first evaluated. There is a way around this but it is fairly tedious
and has to be repeated in numerous places because of the evaluate once
difficulty referred to above.

It seems to me that there should be a fairly easy way to construct a function
on such a table to derive a BOOLEAN value for a virtual column named "active"
based upon the SELECT criteria given above. However, I am unsure if this is in
fact possible and, if so, how to do this.

Can someone show me how this could be accomplished? And, can someone correct
my use of current_date in the example given above if require?

Regards,

--
***          E-Mail is NOT a SECURE channel          ***
James B. Byrne                mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited          http://www.harte-lyne.ca
9 Brockley Drive              vox: +1 905 561 1241
Hamilton, Ontario             fax: +1 905 561 0757
Canada  L8E 3C3

James B. Byrne wrote:
> The situation is this.  A dependent table relationship is episodic.  In other
> words, a product might be available for a period of time, then not available,
> then available again.  Or, a firm might be a client for a period, then not,
> then again.  Or a person might be an employee, then not, then again. Further,
> past intervals of activity must be preserved.
>
> The way that we handle this is through two columns in the dependent table;
> effective_from and superseded_after.  Thus an active row is retrieved via the
> following code:
>
> SELECT * FROM table WHERE ("table"."effective_from <= "current_date" AND
>           ( "table"."superseded" IS NULL OR
>             "table"."superseded" >= "current_date" ) )
>
> The difficulty arises from the implementation of the Rails generated SQL
> SELECTs which freezes any datetime employed therein to the instant that the
> model is first evaluated. There is a way around this but it is fairly tedious
> and has to be repeated in numerous places because of the evaluate once
> difficulty referred to above.
>
> It seems to me that there should be a fairly easy way to construct a function
> on such a table to derive a BOOLEAN value for a virtual column named "active"
> based upon the SELECT criteria given above. However, I am unsure if this is in
> fact possible and, if so, how to do this.
>
> Can someone show me how this could be accomplished? And, can someone correct
> my use of current_date in the example given above if require?

I am not sure if that can solve your problem, but you could use views:

test=> CREATE TABLE test (id integer PRIMARY KEY,
test->                    val text,
test->                    effective_from date NOT NULL,
test->                    superseded date);

test=> CREATE VIEW test_view (id, val, active) AS
test->    SELECT id,
test->           val,
test->          (effective_from <= current_date AND (superseded IS NULL OR superseded >= current_date))
test->    FROM test;

test=> INSERT INTO test (id, val, effective_from, superseded)
test->        VALUES (1, 'one', '2007-01-01', NULL);
test=> INSERT INTO test (id, val, effective_from, superseded)
test->        VALUES (2, 'two', '2007-01-01', '2007-12-31');
test=> INSERT INTO test (id, val, effective_from, superseded)
test->        VALUES (3, 'three', '2009-01-01', NULL);

test=> SELECT * FROM test_view;
 id |  val  | active
----+-------+--------
  1 | one   | t
  2 | two   | f
  3 | three | f
(3 rows)

Yours,
Laurenz Albe