PostgreSQL dungeon with table inheritance and constraints

Edel Sherratt

• • • • • • • •

From Before

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); description and kind should either be defaults or perhaps chosen by the player from a menu Nobody character: (nobody, ”, nonentity, nowhere)

Location

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

Exit

• create table exit ( name varchar(20), description text, is_open boolean, exits_from varchar(20) references location(name), leads_to varchar(20) references location(name), primary key(exits_from, name) );

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);

Problems

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

• • 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;

Triggering the not null and unique checks on monster.name

• • • create table monster () inherits (character); create trigger pkey_character_name before insert on monster for each row execute procedure pkey_character_name(); The same is needed for other descendants of character (such as player)

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

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

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_by varchar (20) ) inherits (item);

More descendants of item

• • • create table light_source (is_lit boolean) 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.

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‘ then NEW.location := (select location from character where character.name = NEW.held_by); end if; return NEW; END $no_bilocation$ language plpgsql;

Triggering ‘no_bilocation’

• • create trigger no_bilocation before insert on portable_item for each row execute procedure no_bilocation(); create trigger no_bilocation before insert on portable_light_source for each row execute procedure no_bilocation();

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;

Triggering ‘move_portable_items’

• create trigger move_portable_items after update on character for each row execute procedure move_portable_items();

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;

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

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.

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

Conclusion

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