SimpleWiki andmebaasi skeem

Kodune töö aines "Andmebaaside teooria"

Konstantin Tretjakov

25. mai 2004 a.

Probleemi kirjeldus

Ülesande sisuks on andmebaasi skeemi konstrueerimine pisikese hüpoteetilise Wiki (nimega SimpleWiki) jaoks. Wiki on praegu populaarne veebisaiti tüüp, mille korral igal külastajal on võimalus saiti lehti muuta. See annab veebisaidile teatud dünaamilisust ja sisuliselt arendatakse sait kollektiivselt paljude inimeste poolt. Selleks, et lehtede muutmise võimalust ei saaks kuritarvitada, salvestatakse Wikis iga lehe muutmise ajaloo. See lubab taastada lehe vanemaid versioone juhul kui viimased muutused osutuvad sobimatuks.

Klassikalises Wikis saavad kõik külastajad kõikide lehtede sisu vabalt muuta ja vaadaata, kuid mõnedes versioonides on võimalik seda vabadust piirata. Tüüpiliselt realiseeritakse see kasutajate süsteemi kaudu. Selleks et teatud lehti luua/vaadata, peab lehe külastaja ennast kasutajana autentima. Iga lehe juures on aga määratud see, mis kasutaja seda lehe lõi, millised kasutajad saavad lehe vaadata ja millised saavad seda muuta.

Veel üks kasuliks omadus, mis on olemas mõnedel Wikidel on lehtede kommenteerimise võimalus. See annab igale külastajale õigust lisada lehtedele oma kommentaare, isegi siis kui tal ei ole õigust vastava lehe sisu muuta.

Vastava töö ülesanneks on konstrueerida andmebaasiskeem sellise Wiki jaoks, mis omaks elementaarset kasutajate halduse süsteemi ja lubaks lehti kommenteerida. Ülesande valiku põhikriteeriumiks oli see, et lahendusena tuleks välja "paras suurusega" skeem, kus ei ole liiga palju olemeid, samas pole lahendus täielikult triviaalne.

Olemid

Andmebaasis piisab järgmistest olemitest:

Page — esitab ühe lehe, põhitunnusteks on lehe nimi ja loomise aeg. Lehega on seotud tema autor, õiguste nimekirjad, muudatuste ajaloo ja kommentaaride nimekiri.
Diff — on üks lehe muudatus, mille juures on muudatuse sisu (eeldatavasti mingis standartses DIFF formaadis) ja muudatuse aeg. Iga leht hoiab enda muudatuste täieliku ajaloo, kusjuures muudatused salvestatakse järjekorras "viimasest alates". S.t. viimases muudatuses on lehe teksti täielik versioon, eelviimases on näidatud ainult muutused viimase versiooni suhtes, jne. Muudatusega on seotud teda sooritatud kasutaja.
Comment — on lehe juurde kuuluv kommentaar. Kommentaari atrubuudid on tema sisu ning loomise aeg. Seose kaudu määratakse ka kasutaja kes kommenteeris.
User — on olem, mis esitab kasutajat. Selle juurde kuulub vähemalt kasutaja autentimisinfo (kasutajanimi/parool/avalik võti/jne).
ACL (Access Control List) — esitab lehe õiguste nimekirja. Sisuliselt on ACL lihtsalt kasutajate nimekiri. ACL-i juures on määratud ka mida see nimekiri tähendab. ALLOW-tüüpi ACL-id nimetavad kasutajaid, kellel on lehele ligipääs lubatud (ja muudel kasutajatel on ligipääs keelatud). DENY-tüüpi ACL-id nimetavad kasutajaid, kellel on ligipääs keelatud (ning teistel kasutajatel (välja arvatud anonüümne kasutaja) on ligipääs lubatud). Iga lehega on seotud kaks ACL-i: lugemise ja kirjutamise operatsioonide jaoks. Kui mõni nendest puudub, see tähendab et vastavaid ligipääsu piiranguid pole.

Klassidiagramm

Olemid ja nende omavahelised seosed on kajastatud järgmisel klassidiagrammil:

Skeemi viimine relatsioonilisele kujule

Antud skeemi teisendamisel relatsioonideks saame järgmised relatsioonid (nagu näha nii skeemis kui ka relatsioonides kasutame olemite identifitseerimiseks kunstlikke võtmeid. See aitab efektiivsusele ning vabastab mõnedest normaliseerimise probleemidest).

USER(USER_ID, USERNAME, PASSWORD_MD5) Funktsionaalsed sõltuvused:
- USER_ID -> USERNAME, PASSWORD_MD5
- USERNAME -> USER_ID, PASSWORD_MD5
Võtmed: USERNAME, USER_ID
ACL(ACL_ID, ACL_TYPE) Funktsionaalsed sõltuvused:
- ACL_ID -> ACL_TYPE
Võtmed: ACL_ID
ACL_USERS(ACL_ID, USER_ID) Funktsionaalsed sõltuvused: pole. Võtmed: (ACL_ID, USER_ID)
PAGE(PAGE_ID, NAME, DATE_CREATED, READ_ACL_ID, WRITE_ACL_ID, OWNER_ID) Funktsionaalsed sõltuvused:
- PAGE_ID -> kõik
- NAME -> kõik
Võtmed: PAGE_ID, NAME
DIFF(DIFF_ID, DIFF_TEXT, DATE_CREATED, PAGE_ID, USER_ID) Funktsionaalsed sõltuvused:
- DIFF_ID -> kõik
- PAGE_ID, DATE_CREATED -> kõik
Võtmed: DIFF_ID, (PAGE_ID, DATE_CREATED)
COMMENT(COMMENT_ID, TEXT, DATE_CREATED, PAGE_ID, USER_ID) Funktsionaalsed sõltuvused:
- COMMENT_ID -> kõik
Võtmed: COMMENT_ID

Nagu on näha, kõik saadud relatsioonid on Boyce-Codd normaalkujul, kuna kõikide funktsionaalsete sõltuvuste determinandid sisalduvad võtme. Seega on kõik relatsioonid ka 3-dal normaal kujul.

Skeemi realisatsioon MySQL vahenditega

Järgmine on MySQL DDL skript, mis realiseerib ülaltoodud skeemi. Tabelite deklaratsioonidele järgnevad näite andmed ning päringute näited.

Tabelid



create table user (
	user_id int auto_increment primary key,
	username varchar(64) not null unique,
	password_md5 char(32)
	);

create table acl (
	acl_id int auto_increment primary key,
	acl_type enum('ALLOW', 'DENY') not null default 'DENY'
	);

create table acl_users (
	acl_id int references acl (acl_id) on delete cascade,
	user_id int references user (user_id) on delete cascade
	);
	
create table page (
	page_id int auto_increment primary key,
	name varchar(255) not null unique,
	date_created datetime not null,
	read_acl_id int references acl (acl_id),
	write_acl_id int references acl (acl_id),
	owner_id int references user (user_id)
	);

create table diff (
	diff_id int auto_increment primary key,
	diff_text mediumtext not null,
	date_created datetime not null,
	page_id int references page (page_id) on delete cascade,
	user_id int references user (user_id)
	);

create table comment (
	comment_id int auto_increment primary key,
	text text not null,
	date_created datetime not null,
	page_id int references page (page_id) on delete cascade,
	user_id int references user (user_id)
	);

Näiteandmed



insert into user (user_id, username, password_md5) values
	(1, 'admin', ''),
	(2, 'kt', '');

insert into acl (acl_id, acl_type) values
	(1, 'DENY'),
	(2, 'DENY'),
	(3, 'ALLOW'),
	(4, 'ALLOW');

insert into acl_users (acl_id, user_id) values
	(2, 1),
	(3, 2);

insert into page (page_id, name, date_created, read_acl_id, 
                                write_acl_id, owner_id) values
	(1, 'AdminsPublicPage', '2004-05-01 00:00:00', 1, 4, 1),
	(2, 'KTsPrivatePage', '2004-05-01 00:00:01', 3, 3, 2),
	(3, 'AntiAdminPage', '2004-05-01 00:00:02', 2, 2, 2);

insert into diff (diff_id, diff_text, date_created, page_id,
                                 user_id) values
	(1, 'The public page of admin it is', '2004-05-01 00:00:10', 1, 1),
	(2, 'Older diff of the public page of admin',
                                 '2004-05-01 00:00:05', 1, 1),
	(3, 'KTsPrivatePage text goes here', '2004-05-01 00:00:11', 2, 2);

insert into comment (comment_id, text, date_created, 
                               page_id, user_id) values
	(1, 'This is a comment for AdminsPublicPage', 
                  '2004-05-01 00:00:01', 1, 2),
	(2, 'This is another comment for AdminsPublicPage',
                  '2004-05-01 00:00:02', 1, 2);

Päringute näited




-- Tagastab kõikide lehtede nimed
select name from page;

-- Väljund:
+------------------+
| name             |
+------------------+
| AdminsPublicPage |
| AntiAdminPage    |
| KTsPrivatePage   |
+------------------+


-- Tagastab kõik lehe 'AdminsPublicPage' muudatused alates 2004-05-01 00:00:08
select diff_text from diff 
	inner join page using (page_id)
where	page.name = 'AdminsPublicPage' and 
	diff.date_created < now() and
	diff.date_created >= '2004-05-01 00:00:08';

-- Väljund:
+--------------------------------+
| diff_text                      |
+--------------------------------+
| The public page of admin it is |
+--------------------------------+


-- Tagastab lehe 'AdminsPublicPage' kõik kommentaarid (viimased esimesena)
select text, comment.date_created from comment
	inner join page using (page_id)
where	page.name = 'AdminsPublicPage'
order by comment.date_created desc;

-- Väljund:
+----------------------------------------------+---------------------+
| text                                         | date_created        |
+----------------------------------------------+---------------------+
| This is another comment for AdminsPublicPage | 2004-05-01 00:00:02 |
| This is a comment for AdminsPublicPage       | 2004-05-01 00:00:01 |
+----------------------------------------------+---------------------+


-- Arvutab kui palju kommentaare kirjutas kasutaja kt 
select count(*) from comment 
	left outer join user using (user_id)
where username = 'kt';

-- Väljund:
+----------+
| count(*) |
+----------+
|        2 |
+----------+


-- Abi tabel vajalik järgmises päringus.
-- Seda oleks mõtekam teha VIEW-na, aga MySQL versioon < 5 ei toeta neid.)
-- Tulemuseks on tabel (page_id, acl_type, user_id)
create table page_read_acl as
	select page_id, acl_type, user_id from page 
		left outer join acl on (acl.acl_id = page.read_acl_id) 
		left outer join acl_users using (acl_id);


-- Nüüd päring ise: leia lehti, mida kasutaja kt võib lugeda
select p.name from page p
	left outer join page_read_acl p_acl using (page_id)
	left outer join user using (user_id)
where	(acl_type = 'ALLOW' and username = 'kt') or
	(acl_type = 'DENY' and not exists (
      		select * from page_read_acl p_acl2
			left outer join user using (user_id)
		where p_acl2.page_id = p_acl.page_id and username = 'kt'
	));

-- Väljund:
+------------------+
| name             |
+------------------+
| AdminsPublicPage |
| KTsPrivatePage   |
| AntiAdminPage    |
+------------------+