Transcript Lecture overheads

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