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:

Klassidiagramm

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

Klassidiagramm

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). 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    |
+------------------+



Copyright © Konstantin Tretjakov, 25. mai 2004 a.