Použití kompozitních typů v PL/pgSQL

Z PostgreSQL
Skočit na navigaci Skočit na vyhledávání

PostgreSQL umožňuje vytvářet a používat tzv kompozitní typy. Ty se do Postgresu dostaly ještě v době před SQL, kdy se hledal mezičlánek mezi relačními a OOP databázemi. Pomocí kompozitních typů lze jednodušší OOP systém vytvořit (bez podpory virtuálních metod a atypickým zápisem volání metod - metody jsou přetížené funkce, neexistují kontejnery). V praxi se ovšem kompozitní typy příliš neosvědčily - řada programátorů před nimi varuje, a to hlavně před jejich použitím v tabulkách. Přístup k položkám v kompozitních typech je o něco málo obtížnější a generické klientské aplikace mohou mít s těmito typy problémy. Což ale neznamená, že nemůžeme tyto typy úspěšně použít v PL/pgSQL.

Kompozitní typy v PostgreSQL neumožňují definovat omezení (CONSTRAINTS). Na druhou stranu umožňují definovat přetypování (CASTS). 

postgres=# CREATE TYPE address AS (street varchar(32), house_number integer);
CREATE TYPE

postgres=# CREATE TABLE users(name varchar, address address);
CREATE TABLE

postgres=# INSERT INTO users VALUES('Pavel', ROW('Skalice', 12));
INSERT 0 1

postgres=# SELECT * FROM users;
 name  │   address    
───────┼──────────────
 Pavel │ (Skalice,12)
(1 row)

postgres=# SELECT name, (address).* 
              FROM users 
             WHERE (address).street = 'Skalice';
 name  │ street  │ house_number 
───────┼─────────┼──────────────
 Pavel │ Skalice │           12
(1 row)

postgres=# CREATE INDEX ON users(((address).street));
CREATE INDEX

postgres=# EXPLAIN SELECT name, (address).*
                      FROM users
                     WHERE (address).street = 'Skalice';
                                  QUERY PLAN                                   
───────────────────────────────────────────────────────────────────────────────
 Index Scan using users_street_idx on users  (cost=0.12..8.14 rows=1 width=39)
   Index Cond: (((address).street)::text = 'Skalice'::text)
(2 rows)

Na první pohled je vše ook - nicméně generické aplikace občas mohou mít problémy s neznámými datovými typy (zde typ "address"). Na základě varování jsem si nikdy nezvykl používat kompozitní typy v tabulkách. Jelikož ale pracuji s uloženými procedurami a pracuji s entitami, kde se typicky opakují určité kombinace položek, kde by bylo použítí kompozitních typů poměrně přirozené a redukují opakující se kód v uložených procedurách, vytvořil jsem si jednoduchou metodu, jak jednoduše kompozitní typy v PL/pgSQL používat. 

CREATE TYPE public.identity_type AS
(
        cust_ref_num            public.custrefnum_type,
        owner_surname           public.ownersurname_type,
        owner_first_name        public.ownerfirstname_type,
        owner_company_name              public.ownercompname_type,
        ico_number              public.iconumber_type
);

Chybějící omezení jsem vyřešil používáním vlastních konstruktorů. V kódu funkce lze jednoduše implementovat i složitějsí kontroly, které by byly zápisem v CHECK hůře čitelné. 

/*
 * Funkcionalni konstruktor typu public.identity - vytvori a validuje hodnotu.
 *
 */
CREATE OR REPLACE FUNCTION public._identity(
                                        cust_ref_num public.custrefnum_type DEFAULT NULL,
                                        owner_surname public.ownersurname_type DEFAULT NULL,
                                        owner_first_name public.ownerfirstname_type DEFAULT NULL,
                                        owner_company_name public.ownercompname_type DEFAULT NULL,
                                        ico_number public.iconumber_type DEFAULT NULL)
RETURNS public.identity_type AS $$
DECLARE result public.identity_type;
BEGIN
  IF owner_first_name IS NOT NULL AND owner_surname IS NULL THEN
    RAISE EXCEPTION 'When FirstName is known, then SurName should be known too';
  END IF;
  IF owner_surname IS NULL AND owner_company_name IS NULL THEN
    RAISE EXCEPTION 'Owner is not known';
  END IF;
  IF owner_surname IS NOT NULL AND owner_company_name IS NOT NULL THEN
    RAISE EXCEPTION 'Owner should be person or company, not both';
  END IF;
  result := (cust_ref_num,
                        owner_surname,
                        owner_first_name,
                        owner_company_name,
                        ico_number)::public.identity_type;
  RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Konstruktorem mohu rovnou vytvářet i záznam v databázi (merge funkce jsou vysvětlené níže): 

/*
 * Konstruktor pro zadani odchoziho cps_formu.
 *
 */
CREATE OR REPLACE FUNCTION outgoing.store_cps_form(
                                                        cps_operator public.operatorid_type,
                                                        cps_operator_appear public.operatorid_type,
                                                        csc_code public.csccode_type,
                                                        address public.address_type,
                                                        identity public.identity_type,
                                                        calling_line_id public.callinglineid_type,
                                                        cps_service_type public.cpsservicetype_type,
                                                        complex_order public.complexorder_type,
                                                        sending_date date DEFAULT 'infinity')
RETURNS int AS $$
DECLARE
  worker outgoing.cps_forms;
  seqvalue int;
BEGIN
  seqvalue := nextval('outgoing.cps_forms_id_seq'::regclass);
  worker.id := seqvalue;
  worker.inserted := LOCALTIMESTAMP;
  worker.cps_operator := store_cps_form.cps_operator;
  worker.cps_operator_appear := store_cps_form.cps_operator_appear;
  worker.csc_code := store_cps_form.csc_code;
  worker := outgoing.merge(worker, store_cps_form.address);
  worker := outgoing.merge(worker, store_cps_form.identity);
  worker.calling_line_id := store_cps_form.calling_line_id;
  worker.cps_service_type := store_cps_form.cps_service_type;
  worker.complex_order := store_cps_form.complex_order;
  worker.sending_date := store_cps_form.sending_date;
  INSERT INTO outgoing.cps_forms VALUES(worker.*);
  RETURN seqvalue;
END;
$$ LANGUAGE plpgsql;

/*
 Pouziti funkce:

  SELECT outgoing.store_cps_form(201, 202, '1234',
                             address := public._address(house_number_a := '10', city := 'Prague', street_name := 'Americka', district := 'Stredocesky kraj', post_code := '11150'),
                             identity := public._identity(owner_surname := 'Stehule', owner_first_name := 'Pavel', cust_ref_num := 'Ahoj'),
                             calling_line_id := 123456789,
                             cps_service_type := 'both',
                             complex_order := 'yes');
*/

Z výše uvedeného kódu je zřejmé, že se nespoléhám na pořadí parametrů, ale používám pojmenované parametry (kód je delší, ale je mnohem popisnější, a v případě úprav konstruktoru je mnohem menší šance na zavlečení chyb z důvodu nesprávného pořadí parametrů).

Použití konstruktorů je výhodnější přímé operace s tabulkami (v tomto případě INSERT).

  • Jsou k dispozici pojmenované parametry - syntaxe je o něco čitelnější a robustnější než u příkazu INSERT.
  • Je volán kód, který obaluje založení nového záznamu - logiku, která by musela být v triggerech, mohu přesunout do konstruktoru.
  • Mohu mít více konstruktorů a mohu volit mezi nimi podle potřeby (mohu mít jednodušší triggery, které nelze jednoduše parametrizovat - triggery primárně používám pouze pro kontroly a pro audit).

V PostgreSQL existuje ke každé tabulce automatický kompozitní typ. Pro všechny tabulky, resp. pro všechny kompozitní typy obsahující jiný kompopzitní typ mám připravenou funkci "merge", která zajistí zkopírování položek do cílového typu - fakticky zajistí rozbalení kompozitního typu. Vytvoření těchto funkcí je jednoduché - a kód je čitelný a přehledný. 

CREATE OR REPLACE FUNCTION outgoing.merge(t outgoing.np_forms, s public.identity_type)
RETURNS outgoing.np_forms AS $$
BEGIN
  t.cust_ref_num := s.cust_ref_num;
  t.owner_surname := s.owner_surname;
  t.owner_first_name := s.owner_first_name;
  t.owner_company_name := s.owner_company_name;
  t.ico_number := s.ico_number;
  RETURN t;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION outgoing.merge(t outgoing.np_forms, s public.np_id_info_type)
RETURNS outgoing.np_forms AS $$
BEGIN
  t.np_id := s.np_id;
  t.sp_id_out := s.sp_id_out;
  t.sp_id_in := s.sp_id_in;
  RETURN t;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION outgoing.merge(t outgoing.cps_forms, s public.address_type)
RETURNS outgoing.cps_forms AS $$
BEGIN
  t.house_number_a := s.house_number_a;
  t.house_number_b := s.house_number_b;
  t.street_name := s.street_name;
  t.city := s.city;
  t.city_section := s.city_section;
  t.district := s.district;
  t.post_code := s.post_code;
  RETURN t;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Intuitivně se navrhnou i inverzní funkce - v jednodušším případě je lze aktivovat přetypováním: 

/*
 * Cast funkce obsluhuji pretypovani z tab. typu obsahujicich polozky, ktere nalezneme
 * v identity_type a address_type na identity_type a address_type. Pouziti je nasnade,
 * Pro identity_type a address_type jsou k dispozici funkce to_xml, ktere serializuji
 * data v techto strukturach do xml.
 *
 * Pozn.: Na prvni pohled jsou tyto funkce identicke s funkcemi 'outgoing.merge'.
 * To je ovsem klamny dojem - lisi se v deklaracich promennych. Bohuzel anyelement
 * v PL/pgSQL nelze pouzit pro compozitni typy, record zase se nechova jako template.
 * Tudiz kod funkce je nutno duplikovat - vzhledem k jeho jednoduchosti a stabilite
 * to necitim jako problem - (Pavel).
 *
 */
CREATE OR REPLACE FUNCTION outgoing.cast_to_address(s outgoing.np_forms)
RETURNS public.address_type AS $$
DECLARE t public.address_type;
BEGIN
  t.house_number_a := s.house_number_a;
  t.house_number_b := s.house_number_b;
  t.street_name>:= s.street_name;
  t.city := s.city;
  t.city_section := s.city_section;
  t.district := s.district;
  t.post_code := s.post_code;
  RETURN t;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION outgoing.cast_to_identity(s outgoing.np_forms)
RETURNS public.identity_type AS $$
DECLARE t public.identity_type;
BEGIN
  t.cust_ref_num := s.cust_ref_num;
  t.owner_surname := s.owner_surname;
  t.owner_first_name := s.owner_first_name;
  t.owner_company_name := s.owner_company_name;
  t.ico_number := s.ico_number;
  RETURN t;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
   
CREATE OR REPLACE FUNCTION outgoing.cast_to_np_id_info(s outgoing.np_forms)
RETURNS public.np_id_info_type AS $$
DECLARE t public.np_id_info_type;
BEGIN
  t.np_id := s.np_id;
  t.sp_id_out := s.sp_id_out;
  t.sp_id_in := s.sp_id_in;
  RETURN t;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE CAST (outgoing.np_forms AS public.address_type)
   WITH FUNCTION outgoing.cast_to_address(outgoing.np_forms);

CREATE CAST (outgoing.np_forms AS public.identity_type)
   WITH FUNCTION outgoing.cast_to_identity(outgoing.np_forms);

CREATE CAST (outgoing.np_forms AS public.np_id_info_type)
   WITH FUNCTION outgoing.cast_to_np_id_info(outgoing.np_forms);

Pro kompozitní typy si lze napsat "metody" - zde např. serializaci do XML 

/*
 * Serializace typu 'identity_type' do xml.
 * 
 */
CREATE OR REPLACE FUNCTION fbuilder.cast_to_xml(idt public.identity_type)
RETURNS xml AS $$
  SELECT xmlconcat(xmlelement(NAME "serviceContractOwner",
                                             xmlforest($1.owner_surname AS "ownerSurname",
                                                       $1.owner_first_name AS "ownerFirstName",
                                                       $1.owner_company_name AS "ownerCompanyName"))
$$ LANGUAGE sql;

Při serializaci obsahujícího objektu použiji přetypování a výše uvedenou metodu (resp. funkci použitou jako metodu). 

/*
 * Serializace outgoing.np_forms do xml
 *
 * Pozn.: Jelikoz tato funkce vyzaduje tri parametry, tak neni vytvoreno
 * pretypovani do xml
 */
CREATE OR REPLACE FUNCTION fbuilder.to_xml(f outgoing.np_forms, order_nr int, sequence_nr int)
RETURNS xml AS $$
BEGIN
  RETURN xmlelement(NAME "npProvide",
            xmlattributes(to_char(order_nr, 'FMO00000000') AS "orderNr",
                           sequence_nr AS "sequenceNr",
                           f.np_service_type AS "npServiceType",
                           f.complex_order AS "complexOrder"),
            xmlelement(NAME "recipientOperator", fbuilder.to_xml(f.recipient_op)),
            xmlelement(NAME "losingOperator", fbuilder.to_xml(f.losing_op)),
            f::identity_type::xml, ---<<<<<<<<<<<<<<
            f::address_type::xml,
            xmlforest(f.cust_ref_num AS "customerReferenceNumber",
                      f.ico_number AS "icoNumber"),
            f.directory_number::xml,
            f.ddi_range::xml,
            f::np_id_info_type::xml,
            xmlforest(fbuilder.attachment_to_xml(f.np_id_attachment) AS "npIDattachment"),
            xmlelement(NAME "portActivationDate", f.port_activation_date::xml),
            xmlelement(NAME "portActivationTime", f.port_activation_time::xml));
END
$$ LANGUAGE plpgsql STRICT;

Hierarchie kompozitních typů respektuje definici v DTD: 

<!ELEMENT installatioFirstName ( #PCDATA ) >
<!ELEMENT installatioSurname ( #PCDATA ) >
<!ELEMENT installationCompanyName ( #PCDATA ) >
<!ELEMENT installationdescription ( #PCDATA ) >
<!ELEMENT minspeed ( #PCDATA ) >
<!ELEMENT newActivationDate ( date ) >
<!ELEMENT newActivationTime ( time ) >
<!ELEMENT postCode ( #PCDATA ) >
<!ELEMENT serviceInstallationOwner ( (installatioSurname, installatioFirstName) | (installationCompanyName, icoNumber) ) >
<!ELEMENT speedAccept ( #PCDATA ) >
<!ELEMENT streetName ( #PCDATA ) >
<!ELEMENT time ( #PCDATA ) >
<!ELEMENT uircode ( #PCDATA ) >
<!ELEMENT billingaccount ( #PCDATA ) >
<!ELEMENT city ( #PCDATA ) >
<!ELEMENT citySection ( #PCDATA ) >
<!ELEMENT contactName ( #PCDATA ) >
<!ELEMENT contactPerson ( #PCDATA ) >
<!ELEMENT contactemail ( #PCDATA ) >
<!ELEMENT district ( #PCDATA ) >
<!ELEMENT speedRequired ( #PCDATA ) >
<!ELEMENT expectedActivationDate ( date ) >
<!ELEMENT expectedActivationTime ( time ) >
<!ELEMENT newPortTime (time)>
<!ELEMENT serviceContractOwner ((ownerSurname, ownerFirstName) | ownerCompanyName)>
<!ELEMENT serviceDisconnectionTime (time)>
<!ELEMENT installationAddress ( uircode?,(housenumberB | ( housenumberA , housenumberB?)), streetName, city, citySection?, district, postCode, contactName?, contactPerson?, contactemail?, installationdescription?, speedRequired? )>
  ...
<!ELEMENT npProvide (recipientOperator, losingOperator, serviceContractOwner, installationAddress, customerReferenceNumber, icoNumber?, (directoryNumber+ | ddiRange+), npIDinfo?, npIDattachment?, portActivationDate, portA
ctivationTime)>
<!ATTLIST npProvide
        orderNr CDATA #REQUIRED
        sequenceNr CDATA #REQUIRED
        npServiceType (geog | non_geog) #REQUIRED
        complexOrder (yes | no | Yes | No) #REQUIRED
        hasLLU (true | false) #IMPLIED
>

Pro tento typ dat se použití kompozitních typů osvědčilo. Jde to docela dobře dohromady - pomocí kompozitních typů se získáváme jednoduché statické OOP - to je ale adekvátní a vhodné vůči relační databázi, a v případě, že zadání vychází z DTD dokumentu, které vytváří statický model, tak si docela dobře vystačíme i s možnostmi, které (ohledně OOP) jsou v PL/pgSQL.

Citováno z „http://postgres.cz/index.php?title=Použití_kompozitních_typů_v_PL/pgSQL&oldid=539“