PostgreSQL dungeon with table inheritance and constraints

1. PostgreSQLdungeon with table inheritance and constraints Edel Sherratt

2. Relations • location{ name, description, is_lit } • exit{ name, description, is_open, exits_from, leads_to} • exit_pair{name_1, exits_from_1, name_2, exits_from_2} • item{ name, description, kind, is_lit, is_at, held_by} • character{ name, description, kind, location} • Nowhere location: (nowhere, ' ', false) • Self character: (me, myself, person, my location); • Nobody character: (nobody, ' ', nonentity, nowhere)

3. Location • create table location (name varchar(20) primary key, description text,is_litboolean ); • insert into location (name, description, is_lit) values ('nowhere', ' ', false);

4. Exit • create table exit ( name varchar(20), description text,is_openboolean,exits_fromvarchar(20) references location(name),leads_tovarchar(20) references location(name), primary key(exits_from, name) );

5. Character with Table Inheritance • create table character ( name varchar(20) primary key, description text, location varchar(20) references location(name)); • create table monster () inherits (character); • create table player () inherits (character);

6. Problems • Primary and foreign key constraints are not inherited (hopefully will be in future PostgreSQL releases) • Work round this using functions and triggers

7. Primary key: Character and descendants • /* character.name is a primary key; pkey_character_name checks the inheritance hierarcy from character to ensure that name is unique and not null */ • create function pkey_character_name() returns trigger as $pkey_character_name$BEGIN if (exists (select * from character where character.name = NEW.name)) then raise exception ‘cannot have more than one character named %.', NEW.name; end if; return NEW;END$pkey_character_name$ language plpgsql;

8. Triggering the not null and unique checks on monster.name • create table monster () inherits (character); • create trigger pkey_character_namebefore insert on monster for each row execute procedure pkey_character_name(); • The same is needed for other descendants of character (such as player)

9. Foreign key reference to location: character and descendants • create function valid_location() returns trigger as $valid_location$BEGIN if not exists (select name from location where location.name = NEW.location) then raise exception 'There is no location called %', NEW.location; end if; return NEW;END $valid_location$ language plpgsql;

10. Triggering the referential integrity constraint on character.location • create trigger valid_location before insert on monster for each row execute procedure valid_location(); • The same is done for player • And the same for item, which also refers to location.name • And for the descendants of item

11. Item with table inheritance • create table item ( name varchar (20) not null, description text, location varchar (20) references location(name)); • create table portable_item (held_byvarchar (20)) inherits (item);

12. More descendants of item • create table light_source (is_litboolean) inherits (item); • create table portable_light_source () inherits (portable_item, light_source); • And each of these has triggers to enforce entity and referential integrity constraints.

13. A domain-specific constraint • /* The location of a portable item is the same as the location of its holder. When a new portable item is added to the database, its location is set to the location of its holder. */ • create function no_bilocation () returns trigger as $no_bilocation$BEGIN if (NEW.held_by != 'nobody‘ thenNEW.location := (select location from character where character.name = NEW.held_by); end if; return NEW;END $no_bilocation$ language plpgsql;

14. Triggering ‘no_bilocation’ • create trigger no_bilocationbefore insert on portable_item for each row execute procedure no_bilocation(); • create trigger no_bilocationbefore insert on portable_light_source for each row execute procedure no_bilocation();

15. Another domain-specific constraint • /* when a character changes location, all the portable items held by that character should move as well. */ • create function move_portable_items () returns trigger as $move_portable_items$BEGIN update portable_item set location = NEW.location where portable_item.held_by = NEW.name; return NEW;END$move_portable_items$ language plpgsql;

16. Triggering ‘move_portable_items’ • create trigger move_portable_itemsafter update on character for each rowexecute procedure move_portable_items();

17. Yet another domain-specific constraint • /* no_remote_pickup ensures that the held_by attribute of a portable item can only be updated to the name of a holder whose location is the same as that of the item; in other words, a character must move to the place where an item is before picking up the item. */ • create function no_remote_pickup() returns trigger as $no_remote_pickup$BEGIN if NEW.location != (select location from character where character.name = NEW.held_by) then raise exception '% must move to % in order to pick up %',NEW.held_by, NEW.location, NEW.name; end if; return NEW;END $no_remote_pickup$ language plpgsql;

18. Table Inheritance • Convenient, but with some problems • Check constraints and not null constraints are inherited, but other kinds of constraints are not • Unique, Primary key and foreign key constraints are not inherited • Some SQL commands default to accessing descendants; others do not • Commands that default to accessing descendants use ONLY to avoid doing so

19. User defined composite types • PostgreSQL also enables user defined composite types • Composite types allow table elements to contain structured data • Composite types are a kind of user defined type like those discussed in connection with object-relational database management systems.

20. Functions and Triggers • Primary use: to implement domain-specific constraints at the database level • Also used to work round lack of constraint inheritance in this example • Typically: • Define a function that returns a named trigger • Then add that trigger to one or more tables

21. Conclusion • Modern relational database management systems provide various extras • But it is important to weigh up the benefits of these against their costs