c20 0046 database management systems lecture 21
Download
Skip this Video
Download Presentation
C20.0046: Database Management Systems Lecture #21

Loading in 2 Seconds...

play fullscreen
1 / 35

C20.0046: Database Management Systems Lecture #21 - PowerPoint PPT Presentation


  • 229 Views
  • Uploaded on

C20.0046: Database Management Systems Lecture #21. M.P. Johnson Stern School of Business, NYU Spring, 2008. Agenda. Stored procedures? Triggers Transactions RAID? Implementation?. Integration with SQL. DECLARE l\_book\_count INTEGER; BEGIN SELECT COUNT(*) INTO l\_book\_count

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'C20.0046: Database Management Systems Lecture #21' - locke


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
c20 0046 database management systems lecture 21

C20.0046: Database Management SystemsLecture #21

M.P. Johnson

Stern School of Business, NYU

Spring, 2008

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

agenda
Agenda
  • Stored procedures?
  • Triggers
  • Transactions
  • RAID?
  • Implementation?

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

integration with sql
Integration with SQL

DECLARE

l_book_count INTEGER;

BEGIN

SELECT COUNT(*)

INTO l_book_count

FROM books

WHERE author LIKE '%FEUERSTEIN, STEVEN%';

DBMS_OUTPUT.PUT_LINE (

'Steven has written (or co-written) ' || l_book_count ||

' books.');

END;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

dynamic pl sql
Dynamic PL/SQL
  • E.g.: write function to return number rows in an arbitrary table

CREATE OR REPLACE FUNCTION rowCount (

tabname IN VARCHAR2) return integer as

retval integer;

begin

execute immediate 'select count(*) from ' || tabname into retval;

return retval;

end;

/

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

dynamic pl sql for ddl
Dynamic PL/SQL for DDL
  • Ordinarily can’t do DDL in PL/SQL
  • But you can in dynamic PL/SQL
  • Here’s an e.g.:

CREATE OR REPLACE procedure dropproc(procname in varchar2) as

begin

execute immediate 'drop procedure ' || procname;

end;

/

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

live examples
Live examples
  • Factorial function:
    • http://pages.stern.nyu.edu/~mjohnson/dbms/plsql/fact.sql
  • Converting between bases:
    • http://pages.stern.nyu.edu/~mjohnson/dbms/plsql/numsys.sql
  • Directory of examples:
    • http://pages.stern.nyu.edu/~mjohnson/dbms/plsql/

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

sps in mysql 5 0
SPs in MySQL (5.0)
  • Generally similar, though technical diffs
    • Need to temporarily redefine ; delimiter

mysql> drop procedure if exists hello;

myslq> delimiter /

mysql> create procedure hello()

-> begin

-> select 'hi';

-> end;

-> /

mysql> delimiter ;

mysql> call hello();

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

new topic triggers
New topic: Triggers
  • PL/SQL programs that run automatically (are “triggered”) when a certain event occurs
  • E.g.: on insert to some table
  • On system start-up
  • On delete from table
  • Big benefit: need not be called explicitly
  • However row in table x is deleted, the trigger gets called

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

trigger events
Trigger events
  • Trigger code may be “triggered” by many kinds of events:
  • Oracle start-up/shut-down
    • Triggers may replace initialization scripts
  • Data updates:
    • Delete: maybe delete related rows
    • Inserts
    • Updates: maybe make other rows consistent
    • Delete: maybe prevent
  • DDL statements
    • Log creation of all objects, e.g.

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

triggers
Triggers
  • Constraints state what must remain true
    • DBMS decides when to check
  • Triggers are instructions to perform at explicitly specified times
  • Three aspects:
    • An event (e.g., update to an attribute)
    • A condition (e.g., a test of that update value)
    • An action (the trigger’s effect) (deletion, update, insertion)
  • When the event occurs, DBMS checks the constraint, and if it is satisfied, performs the action

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

dml trigger options
DML trigger options
  • The trigger may be:
    • Statement-level (e.g., a DELETE WHERE statement) or
    • Row-level (e.g., for each row deleted)
  • The trigger may run
    • BEFORE
    • AFTER or
    • INSTEAD OF the statement (in Oracle, not in others)
  • It may be triggered by
    • INSERTs
    • UPDATEs
    • DELETEs

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

trigger form
Trigger form

CREATE [OR REPLACE] TRIGGER trigger name

{BEFORE | AFTER | INSTEAD OF}

{INSERT | DELETE | UPDATE | UPDATE OF column list} ON table name

[FOR EACH ROW]

[WHEN (...)]

[DECLARE ... ]

BEGIN

... executable statements ...

[EXCEPTION ... ]

END [trigger name];

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

trigger type examples
Trigger type examples
  • First run copy_tables.sql
  • statement_vs_row.sql
    • INSERT INTO to_table SELECT * FROM from_table;
  • before_vs_after.sql
    • INSERT INTO to_table SELECT * FROM from_table;
  • one_trigger_per_type.sql
    • INSERT INTO to_table VALUES (1);

UPDATE to_table SET col1 = 10;

DELETE to_table;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

dml trigger e g
DML Trigger e.g.
  • Q: Why is this (maybe) better than client-side validation?

CREATE OR REPLACE TRIGGER validate_employee_changes

BEFORE INSERT OR UPDATE

ON employee

FOR EACH ROW

BEGIN

check_age (:NEW.date_of_birth);

check_resume (:NEW.resume);

END;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

triggers with when
Triggers with WHEN

CREATE OR REPLACE TRIGGER check_raise

AFTER UPDATE OF salary, commission

ON employee

FOR EACH ROW

WHEN ((OLD.salary != NEW.salary OR

(OLD.salary IS NULL AND NEW.salary IS NULL))

OR (OLD.commission != NEW.commission OR

(OLD.commission IS NULL AND NEW.commission IS NULL)))

BEGIN

...

END;

  • NB: WHEN applies only to row-level triggers

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

triggers with when16
Triggers with WHEN
  • Parentheses are required
  • Can only call built-in functions in when
    • Packages like DBMS_OUTPUT are not allowed

CREATE OR REPLACE TRIGGER valid_when_clause

BEFORE INSERT ON frame

FOR EACH ROW

WHEN ( TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17 )

...

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

simple trigger example
Simple trigger example
  • R(id, data, last-modified)
    • data is a large string
    • Last-modified is a newly added date field
  • Goal: whenever data is modified, update last-modified date
  • Could modify all scripts/programs that touch this table
    • Bad idea
  • Better: user a trigger

CREATE TRIGGER UpdateDateTrigger

AFTER UPDATE OF data ON R

  • REFERENCING
  • NEW ROW AS NewTuple

FOR EACH ROW

BEGIN

NewTuple.last-modified = sysdate;

END;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

multiple dml actions
Multiple DML actions
  • DML actions may be ORed together

CREATE OR REPLACE TRIGGER three_for_the_price_of_one

BEFORE DELETE OR INSERT OR UPDATE ON account_transaction

FOR EACH ROW

BEGIN

IF INSERTING

THEN

:NEW.created_by := USER;

:NEW.created_date := SYSDATE;

ELSIF DELETING

THEN

audit_deletion(USER,SYSDATE);

END;

  • To find actual action, check:
    • INSERTING
    • DELETING
    • UPDATING

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

more on updating
More on UPDATING
  • UPDATING may be called for partic. columns

CREATE OR REPLACE TRIGGER validate_update

BEFORE UPDATE ON account_transaction

FOR EACH ROW

BEGIN

IF UPDATING ('ACCOUNT_NO')

THEN

errpkg.raise('Account number cannot be updated');

END IF;

END;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

extended auditing example
Extended auditing example
  • Tables: grades, grades_audit
  • Run: grades_tables.sql, grades_audit.sql
  • Cases: hacker changes grades, deletes others

UPDATE grades

SET grade = 'A+'

WHERE student_id = 1

AND class_id = 101;

DELETE grades

WHERE student_id = 2

AND class_id = 101;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

extended auditing example21
Extended auditing example
  • Run: grades_tables.sql, grades_audit2.sql
  • Cases: hacker changes student or class ids

UPDATE grades SET student_id = 3

WHERE student_id = 1 AND class_id = 101;

UPDATE grades SET student_id = 1

WHERE student_id = 2 AND class_id = 101;

UPDATE grades SET student_id = 2

WHERE student_id = 3 AND class_id = 101;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

ddl triggers
DDL Triggers
  • Respond to DDL events
    • Creating/dropping tables, indices, etc.
    • ALTER TABLE etc.
  • General form:

CREATE [OR REPLACE] TRIGGER trigger name

{BEFORE | AFTER| {DDL event} ON

{DATABASE | SCHEMA}

DECLARE

Variable declarations

BEGIN

... some code...

END;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

ddl trigger e g
DDL trigger e.g.
  • Town crier examples triggered by creates:
  • uninformed_town_crier.sql
  • informed_town_crier.sql

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

available ddl events
Available DDL events
  • CREATE, ALTER, DROP, GRANT, RENAME, REVOKE, TRUNCATE
  • DDL: any DDL event no_create.sql
  • Q: Does this work??

CREATE OR REPLACE TRIGGER no_create

AFTER CREATE ON SCHEMA

BEGIN

RAISE_APPLICATION_ERROR (-20000,

'ERROR : Objects cannot be created in the production database.');

END;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

db event triggers
DB Event triggers
  • Form similar to DDL triggers:
  • Triggering events: STARTUP, SHUTDOWN, SERVERERROR, LOGON, LOGOFF

CREATE [OR REPLACE] TRIGGER trigger name

{BEFORE | AFTER} {database event} ON {DATABASE | SCHEMA}

DECLARE

Variable declarations

BEGIN

... some code...

END;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

db event restrictions
DB event restrictions
  • Have BEFORE and AFTER as above, but they don’t always apply:
    • No BEFORESTARTUP/LOGON/SERVERERROR
    • No AFTERSHUTDOWN/LOGOFF

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

db trigger e g
DB Trigger e.g.
  • Gather stats before shutdown:
  • Log error messages

CREATE OR REPLACE TRIGGER on_shutdown

BEFORE SHUTDOWN ON DATABASE

BEGIN

gather_system_stats;

END;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

new old topic transactions
New-old topic: Transactions
  • So far, have simply issued commands
    • Ignored xacts
  • Recall, though: an xact is an operation/set of ops executed atomically
    • In one instant
  • ACID test:
    • Xacts are atomic
    • Each xact (not each statement) must leave the DB consistent

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

default xact behavior in oracle
Default xact behavior (in Oracle)
  • An xact begins upon login
  • By default, xact lasts until logoff
    • Except for DDL statements
    • They automatically commit
  • Examples with two views of tbl…
    • But with TYPE=innodb !
    • mysql> set autocommit = 0

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

direct xact instructions
Direct xact instructions
  • At any point, may explicitly COMMIT:
    • SQL> COMMIT;
    • Saves all statements entered up to now
    • Begins new xact
  • Conversely, can ROLLBACK
    • SQL> ROLLBACK;
    • Cancels all statements entered since start of xact
  • Example: delete from emp; or delete junk;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

direct xact instructions31
Direct xact instructions
  • Remember, DDL statements are auto-committed
  •  They cannot be rollbacked
  • Examples:
  • Q: Why doesn’t rollback “work”?

drop table junk;

rollback;

truncate table junk;

rollback;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

savepoints
Savepoints
  • Xacts are atomic
  • Can rollback to beginning of current xact
  • But might want to rollback only part way
  • Make 10 changes, make one bad change
  • Want to: roll back to before last change
  • Don’t have Word-like multiple undo
    • But do have savepoints

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

savepoints33
Savepoints
  • Create a savepoint:
  • emp example:

SAVEPOINT savept_name;

--changes

SAVEPOINT sp1;

--changes

SAVEPOINT sp2;

--changes

SAVEPOINT sp3

--changes

ROLLBACK TO SAVEPOINT sp2;

ROLLBACK TO SAVEPOINT sp1;

  • Can skip savepoints
  • But can ROLLBACK only backwards
  • Can ROLLBACK only to last COMMIT

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

autocommit
AUTOCOMMIT
  • Finally, can turn AUTOCOMMIT on:
    • Oralce: SQL> SET AUTOCOMMIT ON;
    • Mysql: mysql> SET AUTOCOMMIT=1;
    • Can put this in your config file
    • Can specify through JDBC, etc.
  • Then each statement is auto-committed as its own xact
    • Not just DDL statements

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

raid levels
RAID levels
  • RAID level 1: each disk gets a mirror
  • RAID level 4: one disk is xor of all others
    • Each bit is sum mod 2 of corresponding bits
  • E.g.:
    • Disk 1: 10110011
    • Disk 2: 10101010
    • Disk 3: 00111000
    • Disk 4:
  • How to recover?
  • What’s the disadvantage of R4?
    • Various other RAID levels in text…

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

ad