Elfde-Liniestraat 24 3500 Hasselt
Schooljaar 2009-2010
TINFO POKER GAME
Oracle Scripts
Studenten:
Peter Asnong Rik Broens Tom De Keyser Daan Gielen Kris Gregoire Koen Olaerts Toon Wouters
Inhoudsopgave 1.
Gegevensmodel ........................................................................................................................... 3
2.
Installatiehandleiding .................................................................................................................. 4 Alle scripts en hun functie: .............................................................................................................. 5
3.
Oracle scripts ............................................................................................................................... 6 3.1
Hoofdscript .......................................................................................................................... 6
3.2
Gebruikers ........................................................................................................................... 7
3.3
Rollen ................................................................................................................................... 8
3.4
Tabellen ............................................................................................................................... 9
3.5
Synonyms .......................................................................................................................... 12
3.6
Triggers .............................................................................................................................. 12
3.7
Procedures......................................................................................................................... 14
3.8
Views ................................................................................................................................. 15
3.9
Sequences.......................................................................................................................... 16
Groep 10
~2~
TINFO Poker Game
1. Gegevensmodel
Groep 10
~3~
TINFO Poker Game
2. Installatiehandleiding Om de scripts te installeren zorg je ervoor dat je de scripts beschikbaar hebt op een makkelijke plaats zoals bijvoorbeeld: C:\Scripts\ Hierin zouden alle scripts moeten gekopieerd worden: - GebruikersAanmaken.sql - GebruikersVerwijderen.sql - Hoofdscript.sql - ProceduresAanmaken.sql - RollenAanmaken.sql - RollenToekennen.sql - RollenVerwijderen.sql - TabellenAanmaken.sql - TabellenOpvullen.sql - TabellenVerwijderen.sql - SynonymsAanmaken.sql - SynonymsVerwijderen.sql - TriggersAanmaken.sql - ViewsAanmaken.sql Nadien kan je met de Oracle client connecteren op de server van de school om het hoofdscript uit te voeren: SQL> connect PROJ10/
[email protected]/PHL SQL> start c:\Scripts\hoofdscript.sql Om deze scripts te installeren moet je enkel Hoofdscript.sql uitvoeren. Normaal gezien worden al de rechten van hieruit toegekend en worden de deelscripts uitgevoerd. Om alle rollen en gebruikers aan te maken heb je ook de overeenkomstige rechten nodig. Dus voor sommige scripts te kunnen uitvoeren moet men ingelogd zijn als sysdba. Dit hadden we opgelost door eerst de rollen en gebruikers aan te maken en vervolgens wordt er automatisch ingelogd ingelogd als onze ‘beheerder’-gebruiker. Deze gebruiker is eigenlijk onze persoonlijke administrator met alle rechten voor het aanmaken en het onderhoud van de database. We hadden het inloggen als sysdba ook in het hoofdscript opgenomen. Dus je kon de scripts als een gewone gebruiker uitvoeren zonder foutmeldingen te krijgen. Voor het testen op de server van de school hebben we de scripts GebruikersVerwijderen.sql, GebruikersAanmaken.sql, RollenVerwijderen.sql, RollenAanmaken.sql en RollenToekennen.sql in commentaar gezet in het hoofdscript. Als we de scripts lokaal testen kunnen we inloggen als sysdba en mogen deze scripts uitgevoerd worden, maar op de server van de school zijn we geen sysdba en kunnen we als gevolg geen gebruikers en rollen toevoegen en verwijderen.
Groep 10
~4~
TINFO Poker Game
Alle scripts en hun functie: Hoofdscript.sql
Van hieruit worden alle deelscripts opgeroepen.
GebruikersVerwijderen.sql
De vorige gebruikers worden verwijderd.
GebruikersAanmaken.sql
De gebruikers worden aangemaakt.
RollenVerwijderen.sql
De vorige rollen worden verwijderd.
RollenAanmaken.sql
De rollen worden aangemaakt.
RollenToekennen.sql
De reeds aangemaakte rollen worden in dit script toegekend aan de reeds aangemaakte gebruikers.
TabellenVerwijderen.sql
De vorige tabellen worden verwijderd.
TabellenAanmaken.sql
De tabellen worden aangemaakt.
TabellenOpvullen.sql
De tabellen worden gevuld met gegevens.
TriggersAanmaken.sql
De triggers worden aangemaakt.
SynonymsVerwijderen.sql
Het verwijderen van de vorige synonyms.
SynonymsAanmaken.sql
Aanmaken van synonyms zodat alle gebruikers de aangemaakte tabellen kunnen raadplegen.
ProceduresAanmaken.sql
De procedures worden aangemaakt.
ViewsAanmaken.sql
De views worden aangemaakt.
Het hoofdscript is voorzien om snel en efficiënt de database aan te maken en te vullen en om meerdere keren achter elkaar te kunnen worden uitgevoerd zonder foutmeldingen. Daarom hebben we in de "deelscripts" waarbij de vorige gegevens verwijderd worden gebruik gemaakt van de functies: "set term on" en "set term off". Dit commando heeft als doel om geen foutmeldingen te tonen voor als er bijvoorbeeld tabellen gedropt worden die nog niet bestonden als het script voor de eerste keer wordt uitgevoerd.
Groep 10
~5~
TINFO Poker Game
3. Oracle scripts 3.1 Hoofdscript Hoofdscript.sql REM Hoofdscript REM ----------REM connect PROJ10/PROJ10; REM Gebruikers en rollen REM -------------------REM REM REM REM REM REM
start c:\scripts\GebruikersVerwijderen.sql start c:\scripts\RollenVerwijderen.sql start c:\scripts\RollenAanmaken.sql start c:\scripts\GebruikersAanmaken.sql start c:\scripts\RollenToekennen.sql connect beheerder/beheerder;
REM Tabellen REM -------start c:\scripts\TabellenVerwijderen.sql start c:\scripts\TabellenAanmaken.sql REM Synonyms REM -------start c:\scripts\SynonymsAanmaken.sql start c:\scripts\SynonymsVerwijderen.sql
REM Triggers en procedures REM ---------------------start c:\scripts\TriggersAanmaken.sql start c:\scripts\ProceduresAanmaken.sql REM Views REM ----start c:\scripts\ViewsAanmaken.sql REM Sequences REM --------start c:\scripts\SequencesVerwijderen.sql start c:\scripts\SequencesAanmaken.sql REM Tabellen opvullen REM ----------------start c:\scripts\TabellenOpvullen.sql commit;s
Groep 10
~6~
TINFO Poker Game
3.2 Gebruikers GebruikersVerwijderen.sql REM Verwijderen van gebruikers REM -------------------------set term off drop drop drop drop drop drop drop drop drop drop drop drop
user user user user user user user user user user user user
VeroniqueHuybrechts; KrisGregoire; ToonWouters; KoenOlaerts; DaanGielen; PeterAsnong; TomDekeyser; RikBroens; applicatie; beheerder cascade; gamebeheerder; userbeheerder;
set term on
GebruikersAanmaken.sql REM Aanmaken van gebruikers REM ----------------------create create create create create create create create create create create create
user user user user user user user user user user user user
VeroniqueHuybrechts identified by veronique; KrisGregoire identified by kris; ToonWouters identified by toon; KoenOlaerts identified by koen; DaanGielen identified by daan; PeterAsnong identified by peter; TomDekeyser identified by tom; RikBroens identified by rik; applicatie identified by applicatie; beheerder identified by beheerder; gamebeheerder identified by gamebeheerder; userbeheerder identified by userbeheerder;
REM Zorgen dat de gebruikers zich ook kunnen inloggen REM ------------------------------------------------grant grant grant grant grant grant grant grant grant grant grant grant
create create create create create create create create create create create create
session session session session session session session session session session session session
to to to to to to to to to to to to
VeroniqueHuybrechts; KrisGregoire; ToonWouters; KoenOlaerts; DaanGielen; PeterAsnong; TomDekeyser; RikBroens; applicatie; gamebeheerder; userbeheerder; beheerder;
grant unlimited tablespace to beheerder; grant unlimited tablespace to VeroniqueHuybrechts;
Groep 10
~7~
TINFO Poker Game
3.3 Rollen RollenVerwijderen.sql REM Rollen verwijderen REM -----------------set term off drop drop drop drop drop drop drop drop
role role role role role role role role
r_viewsBekijken; r_usersOnderhouden; r_gamesOnderhouden; r_admin; r_gamebeheer; r_userbeheer; r_gebruiker; r_applicatie;
set term on
RollenAanmaken.sql REM Aanmaken rollen en eigenschappen aan rol toekennen REM -------------------------------------------------REM r_viewsBekijken REM --------------CREATE ROLE r_viewsBekijken; grant select on vchip to r_viewsBekijken; grant select on vlose to r_viewsBekijken; grant select on vwin to r_viewsBekijken;
REM r_usersOnderhouden REM -----------------CREATE ROLE r_usersOnderhouden; grant select, update, delete on users to r_usersOnderhouden; grant r_viewsBekijken to r_usersOnderhouden; REM r_gamesOnderhouden REM -----------------CREATE ROLE r_gamesOnderhouden; grant select, insert, update, delete on games to r_gamesOnderhouden; grant select, insert, update, delete on table_models to r_gamesOnderhouden; grant r_viewsBekijken to r_gamesOnderhouden; REM r_gebruiker REM ----------CREATE ROLE r_gebruiker; grant r_usersOnderhouden to r_gebruiker; grant select on games to r_gebruiker; REM r_gamebeheer REM ------------
Groep 10
~8~
TINFO Poker Game
CREATE ROLE r_gamebeheer; grant r_gamesOnderhouden to r_gamebeheer; REM r_userbeheer REM -----------CREATE ROLE r_userbeheer; grant r_usersOnderhouden to r_userbeheer; grant insert on users to r_userbeheer; grant execute any procedure to r_userbeheer;
REM r_applicatie REM -----------CREATE ROLE r_applicatie; grant r_usersOnderhouden to r_applicatie; grant r_gamesOnderhouden to r_applicatie; grant select, insert, update on sessions to r_applicatie; grant execute any procedure to r_applicatie; REM r_admin REM ------CREATE ROLE r_admin; grant r_viewsBekijken to r_admin; grant all on users to r_admin; grant all on games to r_admin; grant all on table_models to r_admin; grant all on sessions to r_admin; grant create table to r_admin; grant create trigger to r_admin; grant create procedure to r_admin; grant create view to r_admin; grant create sequence to r_admin; grant create public synonym to r_admin; grant drop public synonym to r_admin; grant execute any procedure to r_admin;
RollenToekennen.sql REM Rollen toekennen REM ---------------grant grant grant grant grant grant grant grant grant grant grant
r_admin to VeroniqueHuybrechts; r_admin to beheerder; r_gamebeheer to gamebeheerder; r_gebruiker to KrisGregoire; r_gebruiker to ToonWouters; r_gebruiker to KoenOlaerts; r_gebruiker to DaanGielen; r_gebruiker to PeterAsnong; r_gebruiker to TomDekeyser; r_gebruiker to RikBroens; r_applicatie to applicatie;
3.4 Tabellen TabellenVerwijderen.sql REM verwijderen van tabellen
Groep 10
~9~
TINFO Poker Game
REM -----------------------set term off drop drop drop drop
table table table table
Users cascade constraints; Sessions cascade constraints; Table_models cascade constraints; Games cascade constraints;
set term on
TabellenAanmaken.sql REM Aanmaken van tabellen REM --------------------CREATE TABLE Users ( user_id NUMBER(4) , nickname VARCHAR2(150) , , , ,
voornaam achternaam geboortedatum emailadres
, , , , ,
chipcount winstreak losestreak wachtwoordhash admin
constraint U_UID_PK primary key constraint U_NICKNAME_UN unique constraint U_NICKNAME_NN not null VARCHAR2(150) constraint U_VOORNAAM_NN not null VARCHAR2(150) constraint U_ACHTERNAAM_NN not null DATE constraint U_GB_NN not null VARCHAR2(150) constraint U_EMAIL_UN unique constraint U_EMAIL_NN not null NUMBER(10) constraint U_CHIPCOUNT_NN not null NUMBER(3) constraint U_WINSTREAK_NN not null NUMBER(3) constraint U_LOSESTREAK_NN not null VARCHAR2(200) constraint U_WACHTWHASH_NN not null CHAR(1) default 'N' constraint U_ADMIN_NN not null constraint U_ADMIN_YN check (admin in ('Y','N')))
/ CREATE TABLE Table_models ( table_model_id NUMBER(4) , small_blind NUMBER(5) , big_blind NUMBER(5) , seats NUMBER(2) , min_in NUMBER(5) , max_in NUMBER(5) / CREATE TABLE Games ( game_id , game_tablemodelid
constraint constraint constraint constraint constraint constraint
TM_ID_PK TM_SB_NN TM_BB_NN TM_SEATS_NN TM_MIN_NN TM_MAX_NN
primary key not null not null not null not null not null)
NUMBER(4) NUMBER(4)
constraint G_ID_PK primary key constraint G_UN unique constraint G_NN not null constraint G_FK references Table_models)
/
Groep 10
~ 10 ~
TINFO Poker Game
CREATE TABLE Sessions ( session_id NUMBER(4) , session_userid NUMBER(4)
constraint S_ID_PK primary key constraint S_UID_UN unique constraint S_UID_NN not null constraint S_UID_FK references Users , session_playergameid NUMBER(4) constraint S_PGID_UN unique constraint S_PGID_NN not null constraint S_PGID_FK references Games , start_time VARCHAR2(150 constraint S_STIME_NN not null , end_time VARCHAR2(150)) /
TabellenOpvullen.sql REM Tabellen opvullen REM -----------------------INSERT INTO Users (user_id, nickname, voornaam, achternaam, geboortedatum, emailadres, chipcount, winstreak, losestreak, wachtwoordhash, admin) VALUES (sequence_userid.nextval, 'koen', 'Koen', 'Olaerts', to_date('198504-07','YYYY/MM/DD'), '
[email protected]', 50, 0, 0, 'ff032e56f9de78ed9a583aeb00eb0b72', 'Y'); INSERT INTO Users (user_id, nickname, voornaam, achternaam, geboortedatum, emailadres, chipcount, winstreak, losestreak, wachtwoordhash, admin) VALUES (sequence_userid.nextval, 'toon', 'Toon', 'Wouters', to_date('198407-05','YYYY/MM/DD'), '
[email protected]', 50, 0, 0, '1f2004d2d300ac1da929d30860236fc6', 'Y'); INSERT INTO Users (user_id, nickname, voornaam, achternaam, geboortedatum, emailadres, chipcount, winstreak, losestreak, wachtwoordhash, admin) VALUES (sequence_userid.nextval, 'kris', 'Kris', 'Gregoire', to_date('198402-10','YYYY/MM/DD'), '
[email protected]', 50, 0, 0, '03e13700e25563c0c0a8ffdb48dbbc19', 'Y'); INSERT INTO Users (user_id, nickname, voornaam, achternaam, geboortedatum, emailadres, chipcount, winstreak, losestreak, wachtwoordhash, admin) VALUES (sequence_userid.nextval, 'daan', 'Daan', 'Gielen', to_date('1988-0723','YYYY/MM/DD'), '
[email protected]', 50, 0, 0, 'c0ad03a5bce9d551d8810aef1f872d2d', 'Y'); INSERT INTO Users (user_id, nickname, voornaam, achternaam, geboortedatum, emailadres, chipcount, winstreak, losestreak, wachtwoordhash, admin) VALUES (sequence_userid.nextval, 'tom', 'Tom', 'Dekeyser', to_date('1989-0731','YYYY/MM/DD'), '
[email protected]', 50, 0, 0, '34b7da764b21d298ef307d04d8152dc5', 'Y'); INSERT INTO Users (user_id, nickname, voornaam, achternaam, geboortedatum, emailadres, chipcount, winstreak, losestreak, wachtwoordhash, admin) VALUES (sequence_userid.nextval, 'peter', 'Peter', 'Asnong', to_date('198703-31','YYYY/MM/DD'), '
[email protected]', 50, 0, 0, '51dc30ddc473d43a6011e9ebba6ca770', 'Y'); INSERT INTO Users (user_id, nickname, voornaam, achternaam, geboortedatum, emailadres, chipcount, winstreak, losestreak, wachtwoordhash, admin) VALUES
Groep 10
~ 11 ~
TINFO Poker Game
(sequence_userid.nextval, 'rik', 'Rik', 'Broens', to_date('1988-1019','YYYY/MM/DD'), '
[email protected]', 50, 0, 0, '496b7e6d1d1eb11c52e5e01947b22b96', 'Y'); INSERT INTO Users (user_id, nickname, voornaam, achternaam, geboortedatum, emailadres, chipcount, winstreak, losestreak, wachtwoordhash, admin) VALUES (sequence_userid.nextval, 'admin', 'admin', 'admin', to_date('199001-01','YYYY/MM/DD'), '
[email protected]', 50, 0, 0, '21232f297a57a5a743894a0e4a801fc3', 'Y'); INSERT INTO Users (user_id, nickname, voornaam, achternaam, geboortedatum, emailadres, chipcount, winstreak, losestreak, wachtwoordhash, admin) VALUES (sequence_userid.nextval, 'guest', 'guest', 'guest', to_date('199001-01','YYYY/MM/DD'), '
[email protected]', 50, 0, 0, '084e0343a0486ff05530df6c705c8bb4', 'N'); INSERT INTO Users (user_id, nickname, voornaam, achternaam, geboortedatum, emailadres, chipcount, winstreak, losestreak, wachtwoordhash, admin) VALUES (sequence_userid.nextval, 'vero', 'Veronique', 'Huybrechts', to_date('1965-01-01','YYYY/MM/DD'), '
[email protected]', 50, 0, 0, 'cc491de401e5dbcde41ef91090975f42', 'Y');
3.5 Synonyms SynonymsVerwijderen.sql REM Verwijderen van synonyms REM -----------------------set term off drop drop drop drop
public public public public
synonym synonym synonym synonym
users; games; table_models; sessions;
set term on
SynonymsAanmaken.sql REM Aanmaken van synonyms REM --------------------create create create create
public public public public
synonym synonym synonym synonym
users for beheerder.users; games for beheerder.games; table_models for beheerder.table_models; sessions for beheerder.sessions;
3.6 Triggers TriggersAanmaken.sql REM Triggers REM -------REM Leeftijd controleren, gebruiker moet minstens 16 jaar zijn REM ---------------------------------------------------------CREATE OR REPLACE TRIGGER t_controleer_leeftijd
Groep 10
~ 12 ~
TINFO Poker Game
before update or insert on users for each row DECLARE v_leeftijd NUMBER(3); BEGIN v_leeftijd := FLOOR(months_between(sysdate,:new.geboortedatum)/12); if v_leeftijd < 16 then raise_application_error( -20000, 'Minimum leeftijd is 16 jaar.'); end if; END controleer_leeftijd; / REM Bij het aanmaken van een gebruiker heeft de gebruiker nog geen spellen gewonnen of verloren, daarom moeten deze bij het inserten 0 zijn. De gebruiker krijgt bij het maken van zijn account standaard 50 chips (dus chipcount = 50). REM ----------------------------------------------------------------CREATE OR REPLACE TRIGGER t_user before insert on users for each row BEGIN if (:new.chipcount != 50) then raise_application_error( -20000, 'Een nieuwe user moet 50 chips hebben'); end if; if (:new.winstreak != 0) then raise_application_error( -20000, 'Een nieuwe user moet een winstreak van 0 hebben'); end if; if (:new.losestreak != 0) then raise_application_error( -20000, 'Een nieuwe user moet een losestreak van 0 hebben'); end if; END t_user; / REM Bij het aanmaken van een tablemodel mag de small_blind niet groter zijn dan de big_blind REM ----------------------------------------------------------------CREATE OR REPLACE TRIGGER t_blind before insert on table_models for each row BEGIN if (:new.small_blind > :new.big_blind ) then raise_application_error( -20000, 'De big blind moet groter dan of gelijk zijn aan de small blind.'); end if; END t_blind; / REM In een sessie moet de end_time altijd later zijn dan de start_time
Groep 10
~ 13 ~
TINFO Poker Game
REM ----------------------------------------------------------------CREATE OR REPLACE TRIGGER t_session before insert or update on sessions for each row BEGIN if (inserting and :new.end_time is not null) then raise_application_error( -20000, 'De end_time moet leeg zijn.'); elsif (updating('end_time') and :new.end_time <= :old.start_time) then raise_application_error( -20000, 'De end_time moet later zijn dan de start_time.'); end if; END t_session; / REM Iedere keer als een gebruiker zich aanmeldt wordt het datumformaat juist ingesteld REM ----------------------------------------------------------------CREATE OR REPLACE TRIGGER t_logon_trigger after logon on database BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET nls_date_format = ''DD/MM/YYYY'' '; END logon_trigger; / REM Alle aangemaakte triggers enablen REM --------------------------------ALTER ALTER ALTER ALTER ALTER
TRIGGER TRIGGER TRIGGER TRIGGER TRIGGER
t_controleer_leeftijd ENABLE; t_user ENABLE; t_blind ENABLE; t_session ENABLE; t_logon_trigger ENABLE;
3.7 Procedures ProceduresAanmaken.sql REM Stored procedure om een gebruiker te verwijderen. Bij het verwijderen van een gebruiker moet er gecontroleerd worden of de user nog in een sessie zit. REM ----------------------------------------------------------------CREATE OR REPLACE PROCEDURE DeleteUser(p_nick in users.nickname%type) IS v_session sessions.session_id%type; v_userId users.user_id%type; v_commando varchar2(200); BEGIN v_commando := 'delete from Users where nickname = ' || p_nick; select user_id into v_userId from Users where nickname = p_nick; select session_id into v_session -- DEZE KAN GEEN DATA OPLEVEREN, DAN NAAR DE
Groep 10
~ 14 ~
TINFO Poker Game
EXCPETION from Sessions where session_userid = v_userId; update Sessions set end_time = sysdate where session_userid = v_userId and end_time is null; execute immediate v_commando; EXCEPTION when no_data_found then -- ALS ER GEEN DATA GEVONDEN IS IN DE SELECT delete from Users where nickname = p_nick; END; / REM Stored procedure om de velden van een gebruiker leeg te maken REM ----------------------------------------------------------------CREATE OR REPLACE PROCEDURE ProfielOpkuisen(p_nick in users.nickname%type) IS BEGIN update users set voornaam = 'N/A' , achternaam = 'N/A' , chipcount = 50 , losestreak = 0 , winstreak = 0 , admin = 'N'; END; /
3.8 Views ViewsAanmaken.sql REM Aanmaken van Views REM Dit zijn: highscores op chipcount, op winstreak en op losestreak REM ---------------------------------------------------------------CREATE OR REPLACE VIEW vchip as select nickname, chipcount, winstreak, losestreak from (select nickname, chipcount, winstreak, losestreak from users order by chipcount DESC) WHERE ROWNUM <= 50; CREATE OR REPLACE VIEW vlose as select nickname, chipcount, winstreak, losestreak from (select nickname, chipcount, winstreak, losestreak from users order by losestreak DESC) WHERE ROWNUM <= 50; CREATE OR REPLACE VIEW vwin as select nickname, chipcount, winstreak, losestreak from (select nickname, chipcount, winstreak, losestreak from users order by winstreak DESC) WHERE ROWNUM <= 50;
Groep 10
~ 15 ~
TINFO Poker Game
3.9 Sequences Op alle primary keys willen we autonummering toepassen, hiervoor kan je dan “Sequences” gebruiken. SequencesVerwijderen.sql REM Verwijderen sequences REM --------------------set term off DROP DROP DROP DROP
SEQUENCE SEQUENCE SEQUENCE SEQUENCE
sequence_userid; sequence_sessionid; sequence_tablemodelid; sequence_gamerid;
set term on
SequencesAanmaken.sql REM Aanmaken sequences REM -----------------CREATE CREATE CREATE CREATE
Groep 10
SEQUENCE SEQUENCE SEQUENCE SEQUENCE
sequence_userid START WITH 1 INCREMENT BY 1; sequence_sessionid START WITH 1 INCREMENT BY 1; tablemodel_sessionid START WITH 1 INCREMENT BY 1; sequence_gameid START WITH 1 INCREMENT BY 1;
~ 16 ~
TINFO Poker Game
