jeste k ciselnikum se since/until

Radek Kanovsky rk na dat.cz
Úterý Červenec 29 09:50:54 CEST 2003


On Mon, Jul 28, 2003 at 02:14:03PM +0200, Karel Zak wrote:

> > Jeste jsem premyslel nad zaangazovanim drive zminovaneho specialniho
> > UNIQUE indexu. Pokud bych si definoval novy datovy typ "interval" (jako
> 
>  Presne tak. Reseni pomoci toho UNIQUE by bylo naprosto idealni,
>  protoze o vse by se staral server sam. Jen doporucuji pouzit jiny 
>  nazev nez "interval", protoze to jiz existuje:-)
> 
> > dva casove udaje od-do) a na nem definoval operator "=" (pres OPERATOR
> > CLASS) tak, ze dva intervaly by si byly rovny, pokud by se prekryvaly
> > aspon v jeden den, mohl bych mozna pouzit UNIQUE index nad polozkami
> > (jedinecny_sloupec, interval). Nemam to ale uplne promyslene. V tom
> > dusnu to jde pomalu :-)
> 
>  Snad se zase neunahlim (urcite je to tim horkem:-), ale todle napsat 
>  napriklad v C by melo byt celkem jednoduche. IMHO je to dobry napad.

Uz to celkem funguje. Pouziti je jednoduche. Predpokladejme, ze mame
tabulku 'hist', kde atribut 'a' ma byt unikatni v kteremkoliv okamziku,
tj. nemohou byt dva zaznamy takove, ze by se jejich platnost (od-do)
prekryvala (byt jenom o jediny den) a mely stejnou hodnotu atributu 'a'.
Pokud se platnost dvou zaznamu neprekryva, mohou mit stejnou hodnotu
atributu 'a'.

Pouziju specialni datovy typ VALIDITY_DATE a specialni UNIQUE INDEX.
Cely figl je ve funkci validity_date_cmp. Pokud by nekdo potreboval
jemnejsi platnost nez dny, da se to celkem snadno upravit.



    CREATE TABLE hist (
        platnost       VALIDITY_DATE NOT NULL
                       DEFAULT 'from 1-1-1970 till 1-1-3000',
        a              INTEGER NOT NULL
    );
    CREATE UNIQUE INDEX hist_a_uniq ON hist (a, platnost);

    Pouziti:

    hist=# INSERT INTO hist VALUES ('from 01-01-2002 till 01-01-2003', 42);
    INSERT 205782 1
    hist=# INSERT INTO hist VALUES ('from 01-01-2002 till 01-01-2003', 42);
    ERROR:  Cannot insert a duplicate key into unique index hist_a_uniq
    hist=# INSERT INTO hist VALUES ('from 01-01-2002 till 01-01-2003', 41);
    INSERT 205786 1
    hist=# INSERT INTO hist VALUES ('from 01-01-2003 till 01-01-2004', 41);
    ERROR:  Cannot insert a duplicate key into unique index hist_a_uniq



Zatim to neni uplne dobre otestovano ani s timto nemam temer zadne
zkusenosti, takze zdrojaky je potreba brat s rezervou. Jako vzor
poslouzil contrib/isbn_issn.

Zdravi

Radek Kaňovský




----------------------------- validity.h -----------------------------------

#include <server/postgres.h>
#include <server/utils/date.h>



typedef struct ValidityDate
{
    DateADT     from;
    DateADT     till;
} ValidityDate;


#define DatumGetValidityDateP(X)    ((ValidityDate *) DatumGetPointer(X))
#define ValidityDatePGetDatum(X)    PointerGetDatum(X)
#define PG_GETARG_VALIDITYDATE_P(n) DatumGetValidityDateP(PG_GETARG_DATUM(n))
#define PG_RETURN_VALIDITYDATE_P(x) return ValidityDatePGetDatum(x)



ValidityDate * validity_date_in(char * str);
char * validity_date_out(ValidityDate * val);

int4 validity_date_cmp(ValidityDate * a1, ValidityDate * a2);
bool validity_date_lt(ValidityDate * a1, ValidityDate * a2);
bool validity_date_le(ValidityDate * a1, ValidityDate * a2);
bool validity_date_eq(ValidityDate * a1, ValidityDate * a2);
bool validity_date_ge(ValidityDate * a1, ValidityDate * a2);
bool validity_date_gt(ValidityDate * a1, ValidityDate * a2);
bool validity_date_ne(ValidityDate * a1, ValidityDate * a2);



----------------------------- validity.c -----------------------------------

#include <server/postgres.h>
#include <server/utils/timestamp.h>
#include <server/utils/datetime.h>
#include <server/fmgr.h>
#include <server/miscadmin.h>

#include "validity.h"


ValidityDate * validity_date_in(char * str)
{
    ValidityDate   *result;
    char           *from=NULL, *till=NULL, *q;

    if( strncasecmp(str, "from ", 5) != 0 ) {
        elog(ERROR, "missing \'from\' in validity representation \"%s\"", str);
        return NULL;
    }
    for( q = str + 5; *q; ++q ) {
        if(strncasecmp(q, " till ", 6) == 0) {
            from = strndup(str+5, q-str-5);
            till = strdup(q+6);
            break;
        }
    }
    if(!till) {
        elog(ERROR, "missing \'till\' in validity representation \"%s\"", str);
        return NULL;
    }
    result = (ValidityDate *) palloc(sizeof(ValidityDate));
    result->from = DatumGetDateADT(
                DirectFunctionCall1(date_in, CStringGetDatum(from)));
    result->till = DatumGetDateADT(
                DirectFunctionCall1(date_in, CStringGetDatum(till)));
    return result;
}

char * validity_date_out(ValidityDate *val)
{
    char	*s, buf[MAXDATELEN*2+12];

    if (val == NULL) return NULL;

    strcpy(buf, "from ");
    s =  DatumGetCString(DirectFunctionCall1(date_out, val->from));
    strcat(buf, s);
    pfree(s);
    strcat(buf, " till ");
    s =  DatumGetCString(DirectFunctionCall1(date_out, val->till));
    strcat(buf, s);
    pfree(s);
    return pstrdup(buf);
}

int4 validity_date_cmp(ValidityDate * a1, ValidityDate * a2)
{
    if(a1->till < a2->from) return -1;
    if(a1->from > a2->till) return 1;
    return 0;
}

bool validity_date_lt(ValidityDate * a1, ValidityDate * a2)
{
    return (validity_date_cmp(a1, a2) < 0);
}

bool validity_date_le(ValidityDate * a1, ValidityDate * a2)
{
    return (validity_date_cmp(a1, a2) <= 0);
}

bool validity_date_eq(ValidityDate * a1, ValidityDate * a2)
{
    return (validity_date_cmp(a1, a2) == 0);
}

bool validity_date_ge(ValidityDate * a1, ValidityDate * a2)
{
    return (validity_date_cmp(a1, a2) >= 0);
}

bool validity_date_gt(ValidityDate * a1, ValidityDate * a2)
{
    return (validity_date_cmp(a1, a2) > 0);
}

bool validity_date_ne(ValidityDate * a1, ValidityDate * a2)
{
    return (validity_date_cmp(a1, a2) != 0);
}







----------------------------- validity.sql ---------------------------------

CREATE FUNCTION validity_date_in(cstring)
RETURNS validity_date
AS '/home/radekk/experiment/sql/history/validity.so'
LANGUAGE 'C';

CREATE FUNCTION validity_date_out(validity_date)
RETURNS cstring
AS '/home/radekk/experiment/sql/history/validity.so'
LANGUAGE 'C';

CREATE TYPE validity_date (
	INTERNALLENGTH = 8,
	INPUT = validity_date_in,
	OUTPUT = validity_date_out
);

CREATE FUNCTION validity_date_lt(validity_date, validity_date)
RETURNS bool
AS '/home/radekk/experiment/sql/history/validity.so'
LANGUAGE 'C';

CREATE FUNCTION validity_date_le(validity_date, validity_date)
RETURNS bool
AS '/home/radekk/experiment/sql/history/validity.so'
LANGUAGE 'C';

CREATE FUNCTION validity_date_eq(validity_date, validity_date)
RETURNS bool
AS '/home/radekk/experiment/sql/history/validity.so'
LANGUAGE 'C';

CREATE FUNCTION validity_date_ge(validity_date, validity_date)
RETURNS bool
AS '/home/radekk/experiment/sql/history/validity.so'
LANGUAGE 'C';

CREATE FUNCTION validity_date_gt(validity_date, validity_date)
RETURNS bool
AS '/home/radekk/experiment/sql/history/validity.so'
LANGUAGE 'C';

CREATE FUNCTION validity_date_ne(validity_date, validity_date)
RETURNS bool
AS '/home/radekk/experiment/sql/history/validity.so'
LANGUAGE 'C';

CREATE OPERATOR < (
	LEFTARG = validity_date,
	RIGHTARG = validity_date,
	NEGATOR = >=,
	PROCEDURE = validity_date_lt
);

CREATE OPERATOR <= (
	LEFTARG = validity_date,
	RIGHTARG = validity_date,
	NEGATOR = >,
	PROCEDURE = validity_date_le
);

CREATE OPERATOR = (
	LEFTARG = validity_date,
	RIGHTARG = validity_date,
	COMMUTATOR = =,
	NEGATOR = <>,
	PROCEDURE = validity_date_eq
);

CREATE OPERATOR >= (
	LEFTARG = validity_date,
	RIGHTARG = validity_date,
	NEGATOR = <,
	PROCEDURE = validity_date_ge
);

CREATE OPERATOR > (
	LEFTARG = validity_date,
	RIGHTARG = validity_date,
	NEGATOR = <=,
	PROCEDURE = validity_date_gt
);

CREATE OPERATOR <> (
	LEFTARG = validity_date,
	RIGHTARG = validity_date,
	NEGATOR = =,
	PROCEDURE = validity_date_ne
);

CREATE FUNCTION validity_date_cmp(validity_date, validity_date)
RETURNS integer
AS '/home/radekk/experiment/sql/history/validity.so'
LANGUAGE c;

CREATE OPERATOR CLASS validity_date_ops
DEFAULT FOR TYPE validity_date USING btree 
AS
        OPERATOR        1       < ,
        OPERATOR        2       <= ,
        OPERATOR        3       = ,
        OPERATOR        4       >= ,
        OPERATOR        5       > ,
        FUNCTION        1       validity_date_cmp(validity_date, validity_date);



Další informace o konferenci Test