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