2021/3/13 COMP9315 21T1 – Assignment 1
COMP9315 21T1
Aignmen 1
Adding a Set Data Tpe to PostgreSQL
La daed: Teda 9h March 11:05pm M ecen change ae hn in ed; lde change ae hn in bn.
idci e ie ( ale eai bmii
DBMS Imlemenain
This is a orking draft.
Small updates and clarifications ill be added as needed. No major changes are anticipated.
jm …
Sa
Deadline Marks Lae Penal
Sbmission
Pre-reqisies
eeeig
) chagelg
mma
Fida 19 Mach, 9:00m
Thi aigme cibe 15 marks ad al mak f hi ce.
0.089 mark ff he ceilig mak f each h lae.
Afe 7 da he aigme i h 0 mark.
Fm WebCMS: COMP9315 ebie, -> Aignmen, -> A1 Specificaion, -> Make Sbmiion, -> lad inse.c, inse.sorce, [
Fm CSE: give cs9315 ass1 intset.c intset.source [< .c > Ma]
Befe aig hi aigme, i ld be efl cmlee Pac Eecie P04.
Thi aigme aim gie :
A deadig f h daa i eaed iide a DBMS Pacice i addig a e bae e PgeSQL
The gal i imleme a e daa e f PgeSQL, cmlee ih i/ fci ad a age f eai.
Make e ha ead hi aigme ecificai carefll ad compleel befe aig k he aigme.
Qei hich idicae ha hae’ de hi ill iml ge he ee “Pleae ead he ec”.
We ill be ig he fllig ame i he dici bel:
The diec hee PgeSQL ce cde i lcaed (icall /srr/$ER/posgresql-12.5)
The diec hee hae ialled he PgeSQL biaie (icall /srr/$ER/pgsql)
The diec hee hae laced PgeSQL’ daa (icall /srr/$ER/pgsql/daa)
he file hee ed PgeSQL’ lg (icall /srr/$ER/pgsql/log)
Idci
PgeSQL ha a eeibili mdel hich, amg he hig, ide a ell-defied ce f addig e daa e i a PgeSQL ee.
Thi caabili ha led he deelme b PgeSQL e f a mbe f e (ch a lg) hich hae becme a f he adad diibi.
I al mea ha PgeSQL i he daabae f chice i eeach jec hich aim h he bdaie f ha kid f daa a DBMS ca maage.
I hi aigme, e ill be addig a e daa e f dealig ih ses of inegers.
Y ma imleme he fci f he daa e i a a like, proided ha he aif he emaic gie bel.
(i he iSe eci)
Ne ha aa i PgeSQL hae me eie ad eai ha make hem lk a lile bi like e. Hee, he ae no e ad hae ie diffee emaic he daa e ha e ae akig imleme.
PG_CODE
PG_HOME
PG_DATA
PG_LOG
https://cgi.cse.uns.edu.au/cs9315/21T1/assignments/ass1/inde.php
1/6
2021/3/13 COMP9315 21T1 – Assignment 1
The ce f addig e bae daa e i PgeSQL i decibed i he fllig eci f he PgeSQL dcmeai:
37.10 C-Lagage Fci 37.13 Ue-defied Te 37.14 Ue-defied Oea SQL: CREATE TYPE
SQL: CREATE OPERATOR
SQL: CREATE OPERATOR CLASS
Seci 37.13 e a eamle f a cmle mbe e, hich ca e a a aig i f defiig inSe daa e (ee bel). Ne ha he cmle e i a aig i onl, gie a idea h e daa e ae added. D’ be fled i hikig ha hi aigme j eie chage he ame comple inSe; he inSe e i me cmle ( ieded) ha he cmle mbe e.
Thee ae he eamle f e daa e de he orial ad conrib diecie. Thee ma ma gie me efl idea h imleme he inSe daa e:
A a-eced ad daae
PGCODE/conrib/chkpass/
A cae-ieiie chaace ig daae
PGCODE/conrib/cie/
A cfidece-ieal daae
PGCODE/conrib/seg/
Seig U
Y gh a hi aigme ih a feh c f PgeSQL, ih a chage ha migh hae made f he Pac eecie (le hee chage ae iial).
Ne ha l eed cfige, cmile, ad iall PgeSQL ee ce f hi aigme.
All bee cmilai ake lace i he src/orial diec, ad l eie mdificai f he file hee.
Oce hae e-ialled PgeSQL ee, hld he fllig cmmad:
Oce ’e made he inse file, hld al edi he Makefile i hi diec, ad add he gee e he fllig lie:
MODULES = comple fncs inse
DATA_bil = adanced.sql basics.sql comple.sql fncs.sql ssca.sql inse.sql
The e f he k f hi aigme ile ediig he inse.c ad inse.sorce file.
I de f he Makefile k el, m e he ideifie _OBJWD_ i he inse.sorce file efe he diec hldig he cmiled liba.
Y hld ee diecl mdif he inse.sql file dced b he Makefile.
If a e he *.c file alg ih inse.c, he ca d , b ill eed make fhe chage he Makefile ee ha he ae cmiled ad liked cecl i he liba.
Ne ha bmied ei f inse.c ad inse.sorce hld cai a efeece he comple e (becae ha’ ha ’e imlemeig).
Make e ha he cmme i he gam decibe he cde ha o e.
Al, do no eig eie i inse.sorce; all i hld d i ceae he e daa e. P a eig a d i a eaae *.sql, hich d’ eed bmi. Ad do no d he inSe e a he ed f inse.sorce. If d, daa e ill aih befe e hae a chace e i.
The iSe Daa Te
We aim defie a e bae e inSe, e he i f e f iege ale. We al aim defie a efl clleci f eai he inSe e.
$ cd PG_CODE/src/tutorial
$ cp complex.c intset.c
$ cp complex.source intset.source
https://cgi.cse.uns.edu.au/cs9315/21T1/assignments/ass1/inde.php
2/6
2021/3/13 COMP9315 21T1 – Assignment 1
H eee inSe ale, ad imleme fci mailae hem, i . Hee, he m aif he eieme bel
Oce imlemeed cecl, hld be able e PgeSQL ee bild he fllig kid f SQL alicai:
creae able Feares (
id ineger primar ke,
name e
);
creae able DBSsems (
name e primar ke,
feares inSe
);
inser ino Feares (id, name) ales
(1, ‘ell designed’),
(2, ‘efficien’),
(3, ‘fleible’),
(4, ‘robs’);
inser ino DBSsems (name, feares) ales
(‘MSQL’, ‘’),
(‘MongoDB’, ‘’),
(‘Oracle’, ‘2,4’),
(‘PosgreSQL’, ‘1,2,3,4’);
intSet alues
I mahemaic, e eee a e a a cl-backeed, cmma-eaaed clleci f ale: Sch a e cai l diic ale, ad aicla deig ca be imed.
O inSe ale ca be eeeed imilal.
We ca hae a cmma-eaaed li f -egaie iege, ded b a e f cl bace, hich i eeed ad b PgeSQL a a ig.
F eamle:
‘ 1, 2, 3, 4, 5 ’.
Whieace hld mae, ‘1,2,3’ ad ‘ 1, 2, 3 ’ ae eiale. Similal, a e cai diic ale, ‘1,1,1,1,1’ i eiale ‘1’. Ad deig i ielea, ‘1,2,3’ i eiale ‘3,2,1’.
The iege ale i he e ae amed ci f a eece f digi. Thee ae + – ig.
Thee ca be leadig ee, b he hld effeciel be iged, e.g. 0001 hld be eaed he ame a 1.
Y ma no assme a fied limi he ie f he e.
I ma cai e me eleme, bded b he daabae’ caaci e ale.
Y ma assme ha each iege ale ill be le ha INT_MAX. ie. each eleme i he e ill be le ha .
Valid intSets
‘ ’
‘2,3,1’
‘6,6,6,6,6,6’
‘10, 9, 8, 7, 6,5,4,3,2,1’
‘1, 999, 13, 666, 5’
‘ 1 , 3 , 5 , 7,9 ’
‘1, 01, 001, 0001’ (same as ‘1’) ‘1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20’ ‘1,2,3,4,5,6,7,8,9,10,(and hen all nmbers o),9999,10000’ ‘ 1,5,7,9’
‘2,4,6, 8 ‘
‘0’
Inalid intSets
‘a,,’
‘ a, , ’
‘1, 2.0, 3’
‘1, 2,3, 4’
‘1, 2, 3, 4, ’
‘ 1 2 3 4 ’
‘1 2 3 4’
‘1 2 3’
‘ -1 ’
‘1,2,3’
‘1,2,3,4,5’
‘1,2,3,5’
https://cgi.cse.uns.edu.au/cs9315/21T1/assignments/ass1/inde.php
3/6
2021/3/13 COMP9315 21T1 – Assignment 1
Operations on intSets
Y m imleme all f he fllig eai f he inSe e.
(amig
, , ad ae inSe, ad i a iege):
i ? S #S
A>@B A@B
A&&B
AB A!!B
A-B
iSe cai he iege ;
Tha i, .
Gie he cardinali, mbe f diic eleme i, iSe ; Tha i, .
De iSe cai all he ale i iSe ie, f ee eleme f , i i a eleme f Tha i, he ime ee ( ) De iSe cai l ale i iSe ie, f ee eleme f , i i a eleme f Tha i, he ime be ( ) iSe ad ae eal;
? ?
? ?
Tha i, iSe cai all he ale f iSe ad iSe iSe ;
Tha i, ee eleme i ca be fd i , ad ice ea. iSe ad ae eal;
cai all he ale f
Tha i, iSe
ale f iSe
Tha i, me eleme i ca be fd i , ice ea.
Take he e inerecion, ad dce a iSe caiig he eleme cmm
ad ;
Tha i, .
Take he e nion, ad dce a iSe caiig all he eleme f ad ;
Tha i, .
Take he e dijncion, ad dce a iSe caiig eleme ha ae i ad i ,
ha ae i ad i .
Take he e difference, ad dce a iSe caiig eleme ha ae i ad i
.
Ne ha hi i no he ame a A !! B.
de’ cai all he ale f iSe iSe de’ cai all he ;
Bel ae eamle f h migh e inSe, illae he emaic.
Y ca e hee a a iiial e e; e ill l a me cmeheie e ie lae.
‘7,17,,27,37’
‘1,2,3,5,8,’
db=# create table mySets (id integer primary key, iset intSet);
CREATE TABLE
db=# insert into mySets values (1, ‘1,2,3’);
INSERT 0 1
db=# insert into mySets values (2, ‘1,3,1,3,1’);
INSERT 0 1
db=# insert into mySets values (3, ‘3,4,5’);
INSERT 0 1
db=# insert into mySets values (4, ‘4,5’);
INSERT 0 1
db=# select * from mySets order by id;
id ise
—-+———
1 1,2,3
2 1,3
3 3,4,5
4 4,5
(4 ros)
— get all pairs of tuples where the second iset is a subset of first iset
db=# select a.*, b.* from mySets a, mySets b
db-# where (b.iset @< a.iset) and a.id != b.id;
id ise id ise
----+---------+----+-------
11,2,3 21,3
33,4,5 44,5 (2 ros)
https://cgi.cse.uns.edu.au/cs9315/21T1/assignments/ass1/inde.php
4/6
2021/3/13 COMP9315 21T1 - Assignment 1
Y hld hik f me me e f .
I aicla, make e ha check ha cde k ih lage inSe (e.g. cadiali 1000).
If cme ih a e ha hik ae aiclal clee, feel fee hem i he cmme eci bel.
Representing intSets
The fi hig eed d i decide a ieal eeeai f inSe daa e. Y hld d hi afer hae ded he decii f he ea abe.
Sice ha he eie ma affec h decide he eeeai f inSe ale.
Ne ha becae f he eieme ha a inSe ca be abiail lage (ee abe), canno hae a eeeai ha e a fied-ie bjec hld ale f e inSe.
Whe ead ig eeeig inSe ale, he ae ceed i ieal fm,
ed i he daabae i hi fm, ad eai inSe ale ae caied ig hi daa ce. Whe dila inSe ale, hld h hem i a caical fm, egadle f h he ee eeed h he ae ed.
The caical fm f (a lea) hld iclde ace, ad hld hae eleme i acedig de.
The fi fci eed ie ae e ead ad dila ale f e inSe.
Y hld ie aalge f he fci comple_in() ad comple_o() ha ae defied i he file
-- insert extra values into the iset in tuple #4 via union
db=# update mySets set iset = iset '5,6,7,8' where id = 4;
UPDATE 1
db=# select * from mySets where id=4;
id ise
----+-------------
4 4,5,6,7,8
(1 ro)
-- tuple #4 is no longer a subset of tuple #3
db=# select a.*, b.* from mySets a, mySets b
db-# where (b.iset @< a.iset) and a.id != b.id;
id ise id ise
----+---------+----+-------
11,2,3 21,3 (1 ro)
-- get the cardinality (size) of each intSet
db=# select id, iset, (#iset) as card from mySets order by id;
id ise card
----+-------------+------
1 1,2,3
2 1,3
3 3,4,5
4 4,5,6,7,8 5
(4 ros)
-- form the intersection of each pair of sets
db=# select a.iset, b.iset, a.iset && b.iset
db-# from mySets a, mySets b where a.id < b.id;
ise ise ?colmn?
---------+-------------+----------
1,2,3 1,3 1,3
1,2,3 3,4,5 3
1,2,3 4,5,6,7,8
1,3 3,4,5 3
1,3 4,5,6,7,8
3,4,5 4,5,6,7,8 4,5
(6 ros)
db=# delete from mySets where iset @< '1,2,3,4,5,6';
DELETE 3
db=# select * from mySets;
id ise
----+-------------
4 4,5,6,7,8
(1 ro)
-- etc. etc. etc.
3 2 3
https://cgi.cse.uns.edu.au/cs9315/21T1/assignments/ass1/inde.php
5/6
2021/3/13 COMP9315 21T1 - Assignment 1
comple.c.
Siable ame f hee fci ld be e.g.
inse_in() ad inse_o().
Make e ha e he V1 le fci ieface (a i de i comple.c).
Ne ha he i/ fci hld be cmlemea, meaig ha a ig dilaed b he fci m be able be ead ig he i fci.
Thee i eieme f eai he ecie ig ha a ed f i
(e.g. cld e he inSe ale ieall i caical fm).
Ne ha ae no eied defie bia i/ fci called receie_fncion ad send_fncion i he PgeSQL dcmeai, ad called comple_send() ad comple_rec() i he comple.c file.
Hin: e a ma f C fci a ca oide PgeSQL (e.g., ie a imle e die)
Befe iall hem i PgeSQL.
Thi ill make debggig mch eaie.
Y hld ee ha defiii capre he fll emanic of he operaor (e.g. ecif cmmaii if he ea i cmmaie).
ChageLg
1.0 (2021-02-26 15:00:00+10:00) eleaed Aigme 1
1.1 (2021-02-27 11:00:00+10:00)
Mdif he "Oeai iSe" eci
Chage he mbl f "cai" fm '<@' '?' Chage he mbl f "cadiali" fm '@' '#' Chage he mbl f "be" fm '@>‘ ‘@<' Add he "ee" eai, ig he '>@’ mbl Add he “ieali” eai, ig he ‘<>‘ mbl
1.2 (2021-02-26 16:00:00+10:00)
Ceced i eamle f Valid ISe
Add addidial eamle f (I)Valid ISe
Add a e bd f he ie f each eleme i a ISe
1.3 (2021-02-26 18:00:00+10:00)
Cec ee ad be mble ed i “Oeai iSe”
1.4 (2021-03-01 18:00:00+10:00)
Med deadlie fad 9m be cie ih CSE lic
Sbii
Y eed bmi file:
inse.c – caiig he C fci ha imleme he ieal f he inSe daa e. inse.sorce – caiig he emlae SQL cmmad iall he inSe daa e i a PgeSQL ee.
D no bmi he inse.sql file, ice i cai able file ame hich ae efl i e eime.
If em eie he *.c file, hld bmi hem, alg ih he mdified Makefile fm he src/orial diec.
Do no iclde:
creae able …
inser ino …
selec …
drop pe …
O a he aeme diecl eeded f ceaig he inSe daa e i inse.sorce. Hae f, ja ad dlan.
https://cgi.cse.uns.edu.au/cs9315/21T1/assignments/ass1/inde.php
6/6