NobrIntegrált információs rendszerek br Adatbázisok szerver oldali programozása/nobr

Ez az oldal a korábbi SCH wiki-ről lett áthozva. Az eredeti változata itt érhető el.

Ha úgy érzed, hogy bármilyen formázási vagy tartalmi probléma van vele, akkor kérlek javíts rajta egy rövid szerkesztéssel.

Ha nem tudod, hogyan indulj el, olvasd el a migrálási útmutatót

2006.11.24.

A jegyzőkönyvet az embi@aut.bme.hu címre kell küldeni. Subject: IIR_NEPTUNKÓD

Kapcsolódó tárgyak

• Adatbázisok szerver oldali programozása

Adatbázis séma

							 +---------+
							 | terület |
							 +---------+
									|
									|*
+-----------+ *	0-1 +---------+ 0-1	1 +---------+
| mérföldkő |---------| főirány |---------| felelős |
+-----------+			+---------+			+---------+
	 |*		 \ *			  |			  1 /
	 |			\				|*			  /
+------+		 \  0-1 +---------+ 0-1  /
| ülés |		  +-----| projekt |-----+
+------+				  +---------+

• *terület*: id, név
• *főirány*: id, keret, felelős, létrehozás, tervezett vég, terület
• *projekt*: id, keret, felelős, létrehozás, tervezett vég, projekt
• *felelős*: id, név
• *mérföldkő*: id, létrehozás, tervezett vég, név, főirány vagy projekt, ülés
• *ülés*: id, mikor

Feladatok

Táblák létrehozása

DROP TABLE merfoldko;
DROP TABLE ules;
DROP TABLE projekt;
DROP TABLE foirany;
DROP TABLE felelos;
DROP TABLE terulet;

DROP SEQUENCE seq;

CREATE TABLE terulet (
	id int NOT NULL PRIMARY KEY,
	nev nvarchar2(50)
);

CREATE TABLE felelos (
	id int NOT NULL PRIMARY KEY,
	nev nvarchar2(50)
);

CREATE TABLE foirany (
	id int NOT NULL PRIMARY KEY,
	keret int,
	felelos_id int NOT NULL REFERENCING felelos(id) ON DELETE CASCADE,
	letrehozas date,
	tervezett_veg date,
	terulet_id int NOT NULL REFERENCING terulet(id)
);

CREATE TABLE projekt (
	id int NOT NULL PRIMARY KEY,
	keret int,
	felelos_id int NOT NULL REFERENCING felelos(id) ON DELETE CASCADE,
	letrehozas date,
	tervezett_veg date,
	foirany_id int NOT NULL REFERENCING foirany(id)
);

CREATE TABLE ules (
	id int NOT NULL PRIMARY KEY,
	mikor date
);

CREATE TABLE merfoldko (
	id int NOT NULL PRIMARY KEY,
	nev varchar2(100),
	letrehozas date,
	tervezett_veg date,
	foirany_id int REFERENCING foirany(id) ON DELETE CASCADE,
	projekt_id int REFERENCING projekt(id) ON DELETE CASCADE,
	ules_id int REFERENCING ules(id)
);

CREATE SEQUENCE seq;

Tárolt eljárások a beszúráshoz és törléshez

Mérföldkő beszúrása

CREATE OR REPLACE PROCEDURE insert_merfoldko(
	pfid int, nev varchar2, vege date)
IS
	c int;
	ules_id int;
BEGIN
	SELECT seq.nextval INTO ules_id FROM dual;
	INSERT INTO ules VALUES(ules_id, sysdate);
	SELECT count(*) INTO c FROM projekt WHERE id=pfid;
	IF c=1 THEN
		INSERT INTO merfoldko VALUES (seq.nextval, nev, sysdate, vege, NULL, pfid, ules_id);
	ELSE
		INSERT INTO merfoldko VALUES (seq.nextval, nev, sysdate, vege, pfid, NULL, ules_id);
	END IF;
END insert_merfoldko;

Projekt beszúrása

CREATE OR REPLACE PROCEDURE insert_projekt(
	keret int, felelos_nev varchar2, eleje date, vege date, foirany_id int)
IS
	felelos_id int;
BEGIN
	SELECT seq.nextval INTO felelos_id FROM dual;
	INSERT INTO felelos VALUES (felelos_id, felelos_nev);
	INSERT INTO projekt VALUES (seq.nextval, keret, felelos_id, eleje, vege, foirany_id);
END insert_projekt;

Projekt és a kapcsolódó mérföldkövek törlése

CREATE OR REPLACE PROCEDURE delete_projekt(projekt_id int) IS
BEGIN
	DELETE FROM projekt WHERE id=projekt_id;
END delete_projekt;

Felelős és a kapcsolódó projektek és mérföldkövek törlése

CREATE OR REPLACE PROCEDURE delete_felelos(felelos_id int) IS
BEGIN
	DELETE FROM felelos WHERE id=felelos_id;
END delete_felelos;

Select-ek írása

Projekt mérföldkőhöz tartozó adatok lekérdezése, határidő szerint rendezve

SELECT 
	merfoldko.nev AS merfoldkonev,
	pf.nev AS projektfelelosnev,
	ff.nev AS foiranyfelelosnev,
	terulet.nev AS terulet
FROM merfoldko 
	JOIN projekt ON projekt.id=merfoldko.projekt_id
	JOIN foirany ON foirany.id=projekt.foirany_id
	JOIN felelos pf ON pf.id=projekt.felelos_id
	JOIN felelos ff ON ff.id=foirany.felelos_id
	JOIN terulet ON terulet.id=foirany.terulet_id
ORDER BY merfoldko.tervezett_veg

Dátum alapján adott üléshez kapcsolódó felelősök megkeresése

SELECT
	felelos.nev AS nev
FROM ules
	JOIN merfoldko ON merfoldko.ules_id=ules.id
	LEFT JOIN foirany ON foirany.id=merfoldko.foirany_id
	JOIN felelos ON felelos.id=foirany.felelos_id
WHERE
	ules.mikor=:mikor
UNION
SELECT
	felelos.nev AS nev
FROM ules
	JOIN merfoldko ON merfoldko.ules_id=ules.id
	LEFT JOIN projekt ON projekt.id=merfoldko.projekt_id
	JOIN felelos ON felelos.id=projekt.felelos_id
WHERE
	ules.mikor=:mikor

Listázzuk ki a főirányokat, a területüket, és egy 0/1 értéket attól függően, hogy a főirány és az összes kapcsolódó projekt határideje lejárt-e

SELECT
  terulet.nev AS teruletnev,
  foirany.id AS foiranyid,
  1 AS lezart
FROM terulet
	JOIN foirany ON terulet_id=foirany.terulet_id
WHERE
	sysdate>=foirany.tervezett_veg AND
	sysdate>=ALL(SELECT tervezett_veg FROM projekt WHERE foirany.id=projekt.foirany_id)
UNION
SELECT
  terulet.nev AS teruletnev,
  foirany.id AS foiranyid,
  0 AS lezart
FROM terulet
	JOIN foirany ON terulet_id=foirany.terulet_id
WHERE
	sysdate<foirany.tervezett_veg OR
	sysdate<ANY(SELECT tervezett_veg FROM projekt WHERE foirany.id=projekt.foirany_id)

-- Peti - 2006.11.24.