COMP9315 22T1 – Assignment 1
Deadline
Pre-requisites:
Late Penalty:
Marks:
Submission:
COMP9315 22T1
Assignment 1
Adding a PersonName Data Type to
PostgreSQL
DBMS Implementation
Last updated: Tuesday 22nd February 2:16pm
Most recent changes are shown in red … older changes are shown in brown.
Aims
This assignment aims to give you
an understanding of how data is treated inside a DBMS
practice in adding a new base type to PostgreSQL
The goal is to implement a new data type for PostgreSQL, complete with input/output functions,
comparison operators, formatting functions, and the ability to build indexes on values of the type.
Summary
Friday 18 March, 9:00pm
before starting this assignment, it would be useful to complete Prac Work P04
0.04 marks off the ceiling mark for each hour late
This assignment contributes 15 marks toward your total mark for this course.
Webcms3 > Assignments > Ass1 Submission > Make Submission
or, on CSEmachines, give cs9315 ass1 pname.c pname.source
Make sure that you read this assignment specification carefully and completely before starting work
on the assignment.
Questions which indicate that you haven’t done this will simply get the response “Please read the
spec”.
We use the following names in the discussion below
PG_CODE … the directory where your PostgreSQL source code is located (typically
/localstorage/YOU/postgresql-14.1/)
PG_HOME … the directory where you have installed the PostgreSQL binaries (typically
/localstorage/YOU/pgsql/bin/)
PG_DATA … the directory where you have placed PostgreSQL’s data (typically
/localstorage/YOU/pgsql/data/)
PG_LOG … the file where you send PostgreSQL’s log output (typically
/localstorage/YOU/pgsql/data/log)
Introduction
PostgreSQL has an extensibility model which, among other things, provides a well-defined process
for adding new data types into a PostgreSQL server. This capability has led to the development by
3/15/22, 2:09 PM
COMP9315 22T1 – Assignment 1
https://cgi.cse.unsw.edu.au/~…
2/8
PostgreSQL users of a number of types (such as polygons) which have become part of the standard
distribution. It also means that PostgreSQL is the database of choice in research projects which aim
to push the boundaries of what kind of data a DBMS can manage.
In this assignment, we will be adding a new data type for dealing with people’s names. “Hmmm”, you
say, “but aren’t they just text strings, typically implemented as two attributes, one for family name
and one for given names?”. That may be true, but making names into a separate base data type
allows us to explore how we store and manipulate them.
One common way of writing names (e.g. used in UNSW student systems) is
Shepherd,John Andrew
Lin,Xuemin
Eilish,Billie
Martin, Eric Andre
Lakshminarasimhan,Venkateswaran Chandrasekara
Marshall-Martin, Sally Angela
Featherstone,Albert Basil Ernest George Harold Randolph William
i.e.
FamilyName,GivenNames
We give a more precise description of what text strings are valid PersonNames below.
Adding Data Types in PostgreSQL
The process for adding new base data types in PostgreSQL is described in the following sections of
the PostgreSQL documentation:
37.13 User-defined Types
37.10 C-Language Functions
37.14 User-defined Operators
SQL: CREATE TYPE
SQL: CREATE OPERATOR
SQL: CREATE OPERATOR CLASS
Section 37.13 uses an example of a complex number type, which you can use as a starting point for
defining your PersonName data type (see below). There are other examples of new data types under
the directories:
PG_CODE/contrib/chkpass/ … an auto-encrypted password datatype
PG_CODE/contrib/citext/ … a case-insensitive character string datatype
PG_CODE/contrib/seg/ … a confidence-interval datatype
These may or may not give you some useful ideas on how to implement the PersonName data type.
For example, many of these data types are fixed-size, while PersonNames are variable-sized. A
potentially useful example of implementing variable-sized types can be found in:
PG_CODE/src/tutorial/funcs.c … implementation of several data types
Setting Up
3/15/22, 2:09 PM
COMP9315 22T1 – Assignment 1
https://cgi.cse.unsw.edu.au/~…
3/8
You ought to start this assignment with a fresh copy of PostgreSQL, without any changes that you
might have made for the Prac exercises (unless these changes are trivial). Note that you only need
to configure, compile and install your PostgreSQL server once for this assignment. All subsequent
compilation takes place in the src/tutorial directory, and only requires modification of the files
there.
Once you have re-installed your PostgreSQL server, you should run the following commands:
$ cd PG_CODE/src/tutorial
$ cp complex.c pname.c
$ cp complex.source pname.source
Note the pname.* files will contain many references to complex; I do not want to see any remaining
occurences of the word complex in the files that you eventually submit. These files simply provide a
template in which you create your PersonName type.
Once you’ve made the pname.* files, you should also edit the Makefile in this directory and add the
green text to the following lines:
MODULES = complex funcs pname
DATA_built = advanced.sql basics.sql complex.sql funcs.sql syscat.sql pname.sql
The rest of the work for this assignment involves editing only the pname.c and pname.source files.
In order for the Makefile to work properly, you must use the identifier OBJWD in the pname.source
file to refer to the directory holding the compiled library. You should never modify directly the
pname.sql file produced by the Makefile. Place all of your C code in the pname.c file; do not
create any other *.c files.
Note that your submitted versions of pname.c and pname.source should not contain any references
to the complex type. Make sure that the documentation (comments in program) describes the code
that you wrote. Leaving the word complex anywhere in a pname.* file will cost 1 mark.
The Person Name Data Type
We wish to define a new base type PersonName to represent people’s names, in the format
FamilyName,GivenNames. We also aim to define a useful set of operations on values of type
PersonName and wish to be able to create indexes on PersonName attributes. How you represent
PersonName values internally, and how you implement the functions to manipulate them internally, is
up to you. However, they must satisfy the requirements below.
Once implemented correctly, you should be able to use your PostgreSQL server to build the
following kind of SQL applications:
create table Students (
zid integer primary key,
name PersonName not null,
degree text,
— etc. etc.
Submission
You need to submit two files: pname.c containing the C functions that implement the internals of the
PersonName data type, and pname.source containing the template SQL commands to install the
PersonName data type into a PostgreSQL server. Do not submit the pname.sql file, since it contains
absolute file names which are not helpful in our test environment.
Have fun, jas