Course outlines Database Application Development Database Programming Embedded

Course outlines Database Application Development Database Programming Embedded SQL Dynamic SQL Embedded SQL in Java Database APIs: Alternative to embedding Embedded SQL in Java using SQLJ - an example Database Stored Procedures SQL Persistent Stored Modules (SQL/PSM) Client-Server a Modern Database Architectures Client-Server Computing Two-Tier Architecture Multiple-Tier Architecture Active Database Concepts and Triggers – an introduction Generalized (ECA) Model for Active DB Database Triggers context Summary

Database Programming (1/2) } Objective: To access a database from an application program (as opposed to interactive interfaces) } Why? An interactive interface is convenient but not sufficient A majority of database operations are made thru application programs (increasingly thru web applications) } SQL commands can be called from within a host language (e. g. , C++ or Java) program. Ê SQL statements can refer to host variables (including special variables used to return status). Ê Must include a statement to connect to the right 2

Database Programming (2/2) } Different Approaches (mainly two) ÊEmbedded commands: Database commands are embedded in a general-purpose programming language ÊLibrary of database functions: Available to the host language for database calls; known as an API FA brand new, full-fledged language: Minimizes impedance mismatch } Impedance Mismatch Incompatibilities between a host programming language and the database model, e. g. , Êtype mismatch and incompatibilities; requires a new binding for each language Êset vs. record-at-a-time processing - need special iterators to loop over query results and manipulate individual values 3

Embedded SQL } Most SQL statements can be embedded in a generalpurpose host programming language such as COBOL, C, Java } An embedded SQL statement is distinguished from the host language statements by enclosing it between ÊEXEC SQL or EXEC SQL BEGIN Êand a matching END-EXEC or EXEC SQL END (or semicolon) Ê Syntax may vary with language Ê A preprocessor converts the SQL statements into special API calls. Ê Then a regular compiler is used to compile the code. } Language constructs: !Connecting to a database: EXEC SQL CONNECT !Declaring variables: EXEC SQL BEGIN (END)4

Embedded SQL in C - an example } Variables inside DECLARE are shared and can appear (while prefixed by a colon) in SQL statements } SQLCODE is used to communicate errors/exceptions between the database and the program int loop; EXEC SQL BEGIN DECLARE SECTION; varchar dname[16], fname[16], …; char ssn[10], bdate[11], …; int dno, dnumber, SQLCODE, …; EXEC SQL END DECLARE SECTION; loop = 1; while (loop) { prompt (“Enter SSN: “, ssn); EXEC SQL select FNAME, LNAME, ADDRESS, SALARY into : fname, : lname, : address, : salary from EMPLOYEE where SSN == : ssn; } if (SQLCODE == 0) printf(fname, …); else printf(“SSN does not exist: “, ssn); prompt(“More SSN? (1=yes, 0=no): “, loop); END-EXEC 5

Why is cursor needed? } When you write code for a transaction where the result set includes several rows of data, you must declare and use a cursor. Embedded SQL host program A program variable can hold one value at a time cursor DBMS result } Cursor bridges the gap between value-oriented host program and set-oriented DBMS. 6

A cursor is a mechanism you can use to fetch rows one at a time. Cursor; definition } An entity that maps over a result set and establishes a position on a single row within the result set. } After the cursor is positioned on a row, operations can be performed on that row, or on a block of rows starting at that position. } The most common operation is to fetch (retrieve) the current row or block of rows. DECLARE CURSOR - a syntax based on the SQL-92 standard DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ , . . . n ] ] } ]7

Embedding SQL using cursors DECLARE @sid varchar(5), @sfname varchar(20), @slname varchar(40), … DECLARE st_cursor CURSOR FOR SELECT sid, sfname, slname FROM student WHERE … Embedding SQL in C - an Example char SQLSTATE[6]; EXEC SQL BEGIN DECLARE SECTION char c_sname[20]; short c_minrating; OPEN st_cursor float c_age; FETCH NEXT FROM st_cursor INTO EXEC SQL END DECLARE SECTION @sid, @sfname, @slname c_minrating = random(); WHILE @@FETCH_STATUS = 0 BEGIN … END SELECT … --Get the next student. FETCH NEXT FROM st_cursor INTO @sid, @sfname, @slname CLOSE st_cursor DEALLOCATE st_cursor GO EXEC SQL DECLARE sinfo CURSOR FOR SELECT S. sname, S. age FROM Sailors S WHERE S. rating > : c_minrating ORDER BY S. sname; do { EXEC SQL FETCH sinfo INTO : c_sname, : c_age; 8

Dynamic SQL Objective: Allows programs to construct and submit/executing SQL statements/queries at run time (on-the-fly). Ê a program accepts SQL statements from the keyboard at run-time Ê a point-and-click operation translates to certain SQL query Dynamic SQL: An Example EXEC SQL BEGIN DECLARE SECTION; varchar sqlupdatestring[256]; EXEC SQL END DECLARE SECTION; … prompt (“Enter update command: “, sqlupdatestring); EXEC SQL PREPARE sqlcommand FROM : sqlupdatestring; EXEC SQL EXECUTE sqlcommand; Example of dynamic SQL within a C program. char* sqlcmd = “update account set balance = balance * 1. 05 where accountnumber= ? ” EXEC SQL prepare myprog from : sqlcmd; char account[10] = “A 101”; EXEC SQL execute myprog using : account; The dynamic SQL program contains a ? , which is a place holder for a value that is provided when the SQL program is executed. 9

Embedded SQL in Java SQLJ: a standard (part of the SQL) for embedding SQL in Java ÊAn SQLJ translator converts SQL statements into Java ÊThese are executed thru the JDBC interface ÊCertain classes have to be imported - E. g. , java. sql JDBC: Java Database Connectivity (Sun’s JDBC: Java API) SQL connection function calls for Java programming ÊA Java program with JDBC functions can access any relational DBMS that has a JDBC driver ÊJDBC allows a program to connect to several 10

Database APIs: Alternative to embedding } Embedded SQL provides static database programming } API: Dynamic database programming with a library of functions ÊAdvantage: No preprocessor needed (thus more flexible) ÊDisadvantage: SQL syntax checks to be done at run-time Rather than modify compiler, add library with database calls (API) (Database Programming with Functional Calls) Ê Special standardized interface: procedures/objects Ê Pass SQL strings from language, presents result sets in a language-friendly way Ê Sun’s JDBC: Java API SQL Call Level Interface - A part of the SQL standard ÊProvides easy access to several databases within the same program ÊCertain libraries (e. g. , sqlcli. h for C) have to be installed 11

Steps in JDBC Database Access 1. Import JDBC library (java. sql. *) 2. Load JDBC driver: Class. forname(“oracle. jdbc. driver. Oracle. Driver ”) 3. Define appropriate variables 4. Create a connect object (via get. Connection) 5. Create a statement object from the Statement class: 6. Identify statement parameters (designated by question marks) 7. Bound parameters to program variables 8. Execute SQL statement (referenced by an object) via JDBC’s execute. Query 12

Embedded SQL in Java using SQLJ - an example Compiler can perform syntax checks, strong type checks, consistency of the query with the schema All arguments always bound to the same variable: Int sid; String name; Int rating; // named iterator #sql iterator Sailors(Int sid, String name, Int rating); Sailors sailors; // assume that the application sets rating #sailors = { SELECT sid, sname INTO : sid, : name FROM Sailors WHERE rating = : rating }; // retrieve results while (sailors. next()) { System. out. println(sailors. sid + “ “ + sailors. sname)); 13

SQLJ Iterators SQLJ supports two types of iterators (“cursors”): Named iterator - associated with a query result Ê Need both variable type and name, and then allows retrieval of columns by name. Ê Refer to previous slide. Positional iterator - lists only attribute types in a query result Need only variable type, and then uses FETCH. . INTO construct: #sql iterator Sailors(Int, String, Int); Sailors sailors; #sailors = … while (true) { #sql {FETCH : sailors INTO : sid, : name} ; if (sailors. end. Fetch()) { break; } 14

Database Stored Procedures } What is a stored procedure: Ê Program executed through a single SQL statement Ê Executed in the process space of the server } Persistent procedures/functions (modules) are stored locally and executed by the database server } Advantages: Ê Can encapsulate application logic while staying “close” to the data Ê A procedure can be invoked by any applications (thus reduce duplications -reuse of application logic by different users) Ê Execution by the server reduces communication (traffic) costs Ê Enhance the modeling power of views } Disadvantages: Ê Every DBMS has its own syntax and this can make the system 15

Stored Procedure Constructs } A stored procedure CREATE PROCEDURE procedure-name (params) local-declarations procedure-body; } A stored function CREATE FUNCTION fun-name (params) RETRUNS return-type local-declarations function-body; } Calling a procedure or function 16

Stored Procedures: Examples CREATE PROCEDURE Show. Num. Reservations SELECT S. sid, S. sname, COUNT(*) FROM Sailors S, Reserves R WHERE S. sid = R. sid GROUP BY S. sid, S. sname Stored procedures can have parameters - three different modes: IN, OUT, INOUT CREATE PROCEDURE Increase. Rating( IN sailor_sid INTEGER, IN increase INTEGER) UPDATE Sailors SET rating = rating + increase WHERE sid = sailor_sid Stored procedure do not have to be written in SQL: CREATE PROCEDURE Top. Sailors( IN num INTEGER) LANGUAGE JAVA EXTERNAL NAME “file: ///c: /stored. Procs/rank. jar” 17

Calling Stored Procedures EXEC SQL BEGIN DECLARE SECTION Int sid; Int rating; EXEC SQL END DECLARE SECTION // now increase the rating of this sailor EXEC CALL Increase. Rating(: sid, : rating); JDBC: Callable. Statement cstmt= con. prepare. Call(“{call Show. Sailors}); Result. Set rs = cstmt. execute. Query(); while (rs. next()) { … } SQLJ: #sql iterator Show. Sailors(…); Show. Sailors showsailors; #sql showsailors={CALL Show. Sailors}; 18

SQL Persistent Stored Modules (SQL/PSM) Most DBMSs allow users to write stored procedures in a simple, general-purpose language (close to SQL) à SQL/PSM standard is a representative } SQL/PSM: Part of the SQL standard for writing persistent stored modules } SQL + stored procedures/functions + additional programming constructs Ê E. g. , branching and looping statements Ê Enhance the power of SQL An Example Declare a stored procedure: CREATE FUNCTION DEPT_SIZE (IN CREATE PROCEDURE name(p 1, p 2, …, pn) deptno INTEGER) local variable declarations RETURNS VARCHAR[7] procedure code; DECLARE TOT_EMPS INTEGER; SELECT COUNT (*) INTO TOT_EMPS Declare a function: FROM SELECT EMPLOYEE CREATE FUNCTION name (p 1, WHERE …, pn)DNO = deptno; RETURNS sql. Data. Type IF TOT_EMPS > 100 THEN RETURN local variable declarations “HUGE” function code; ELSEIF TOT_EMPS > 50 THEN 19

Client-Server a Modern Database Architectures } Client-Server Computing Components of Data-Intensive Systems } Client-Server Computing with middleware } Two-Tier Architecture } Multiple-Tier Architecture with Web Server Case study – an example Advantages – an overview

Client-server computing (1990 s) Components of Data-Intensive Systems Client-Server Database Architectures (1/2) } Three separate types of functionality: ÊPresentation Logic ÊApplication or business logic ÊData management The system architecture determines whether these three components reside on a single system (“tier) or are distributed across several tiers } Tasks to Distribute Ê Presentation: code to maintain the graphical user interface Ê Validation: code to ensure the consistency of the database and user inputs Ê Business logic: code to perform business functions Ê Workflow: code to ensure completion of business processes Ê Data access: code to extract data to answer queries and modify a database 21

Client-server computing (1990 s) Components of Data-Intensive Systems Client-Server Database Architectures (2/2) } Client-Server Architecture Êis an arrangement of components (clients and servers) among computers connected by a network. Êsupports efficient processing of messages (requests for service) between clients and servers. } Design Issues ÊDivision of processing: the allocation of tasks to clients and servers. ÊProcess management: interoperability among clients and servers and efficiently processing messages between clients and servers. ÊMiddleware: software for process management 22

Client-server computing Client-Server Computing with Middleware } } A software component that performs process management. Allow clients and servers to exist on different platforms. Allows servers to efficiently process messages from a large number of clients. Often located on a dedicated computer. 23

Client-server computing Two-Tier Architecture (1/2) SQL statements Database ser Database Query results Two-Tier Architecture } A PC client and a database server interact directly to request and transfer data. } The PC client contains the user interface code. } The server contains the data access logic. } The PC client and the server share the validation and business 24 logic.

Client-Server Architectures – Work division Two-Tier Architecture (2/2) ra t Ex Thin client Ê Client implements only the graphical user interface Ê Server implements business logic and data management Thick client Ê Client implements both the graphical user interface and the business logic Ê Server implements data management Disadvantages Ê No central place to update the business logic Ê Security issues: Server needs to trust clients ÊAccess control and authentication needs to be managed at the server ÊClients need to leave server database in consistent state ÊOne possibility: Encapsulate all database access into stored procedures Ê Does not scale to more than several 100 s of clients 25

Client-server computing Three-Tier Architecture (Application Server) Application server Database SQL statements Database server Application server Query results } To improve performance, the three-tier architecture adds another server layer either by a middleware server or an application server. } The additional server software can reside on a separate computer. } Alternatively, the additional server software can be distributed between the database server and PC clients. 26

Client-server computing Multiple-Tier Architecture Application server Middleware server Database server Databa se Application server } A client-server architecture with more than three layers: a PC client, a backend database server, an intervening middleware server, and application servers. } Provides more flexibility on division of processing } The application servers perform business logic and manage specialized kinds of data such as images. 27

Client-server computing Multiple-Tier Architecture with Web Server Page request Database request HTML SQL Middleware Server with listener Web server HTML SQL statements and formatting requirements Database Server Results Database 28

Multiple-Tier Architecture / Web Application Case study – Course Enrollment Build a system using which students can enroll in courses } Database System Student info, course info, instructor info, course availability, pre-requisites, etc. } Application Server Logic to add a course, drop a course, create a new course, etc. } Client Program Log in different users (students, staff, faculty), display forms Client Program HTML, and human-readable output (Web Browser) Javascript Technologi es XSLT Application Server Ph. P, CGI Database System XML (MS SQLServer) Stored Procedures (Apache) 29

Multiple-Tier Architecture / Web Application Advantages of the Three-Tier Architecture Extra } Heterogeneous systems Tiers can be independently maintained, modified, and replaced } Thin clients Only presentation layer at clients (web browsers) } Integrated data access Ê Several database systems can be handled transparently at the middle tier Ê Central management of connections } Scalability Replication at middle tier permits scalability of business logic } Software development Ê Code for business logic is centralized Ê Interaction between tiers through well-defined APIs: Can reuse standard components at each tier 30

Active Database Concepts and Triggers – an introduction } Generalized (ECA) Model for Active DB } Creating Triggers } Database Triggers context

Generalized Model for Active DB Event-Condition-Action (ECA model) rules } Allow for decisions to be made in the database instead of a separate application Ê Event : trigger the rule, Usually database update Example: – Inserting new employee tuples – Changing the salary of existing employees – Deleting employee tuples` Ê Condition : determines whether the rule action should be executed Ê No condition – executed once the event occurs Ê With condition (WHEN clause) Example DNO for new tuple is not null Ê Action : usually a sequence of SQL statements Example - } Automatically update TOTAL_SAL (1, 2, & 4) Implemented as triggers 32

Database Trigger: Example Application SQL> INSERT INTO EMP 2. . . ; EMP table EMPNO 7838 7698 7369 7788 ENAME KING BLAKE SMITH SCOTT CHECK_SAL trigger JOB PRESIDENT MANAGER CLERK ANALYST SAL 5000 2850 800 3000 33

Creating Triggers } Trigger timing - When should the trigger fire? Ê BEFORE: The code in the trigger body will execute before the triggering DML event. Ê AFTER: The code in the trigger body will execute after the triggering DML event. } Triggering event - What DML operation will cause the trigger to execute? Ê INSERT, UPDATE , DELETE Ê Any combination of the above } Table name: On table } Trigger Type - How many times should the trigger body execute when the triggering event takes place? Ê Statement: The trigger body executes once for the triggering event. This is the default. Ê Row: The trigger body executes once for each row affected by the triggering event. } When clause: Restricting condition 34

SQL Triggers: An Example A trigger to compare an employee’s salary to his/her supervisor during insert or update operations: Create trigger inform_supervisor before INSERT OR UPDATE OF salary, supervisor_snn ON employee for each row when (NEW. SALARY> (SELECT FROM EMPLOYEE WHERE SSN=NEW. SUPERVISOR_SSN) ) inform_supervisor (NEW. SUPERVISOR_SSN, NEW. SSN); SALARY 35

Database Triggers context } Implementation of Triggers Enforcing ÊSecurity, ÊAuditing ÊData integrity ÊReferential integrity Within the Server ÊTable replication, ÊDerived data ÊEvent logging } Benefits of Database Triggers ÊImproved data security ÊProvide value-based security checks ÊProvide value-based auditing ÊImproved data integrity ÊEnforce dynamic data integrity constraints ÊEnforce complex referential integrity constraints ÊEnsure related operations are performed together implicitly 36

Summary } A database may be accessed in an interactive mode } Most often data in a database is manipulate via application programs } Several methods of database programming: } } } } Ê Embedded SQL Ê Dynamic SQL Ê Stored procedure and function Embedded SQL allows execution of parametrized static queries within a host language Dynamic SQL allows execution of completely ad-hoc queries within a host language Cursor mechanism allows retrieval of one record at a time and bridges impedance mismatch between host language and SQL APIs such as JDBC introduce a layer of abstraction between application and DBMS SQLJ: Static model, queries checked a compile-time. Stored procedures execute application logic directly at the server SQL/PSM standard for writing stored procedures Client-Server computing architecture is largely adopted by database development community 37