C20 0046 database management systems lecture 21
Download
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 l.jpg

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 l.jpg
Agenda

  • Stored procedures?

  • Triggers

  • Transactions

  • RAID?

  • Implementation?

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


Integration with sql l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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