embedded database logic · user-defined types a user-defined type is a special data type that is...
TRANSCRIPT
![Page 1: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/1.jpg)
Database Systems
15-445/15-645
Fall 2018
Andy PavloComputer Science Carnegie Mellon Univ.AP
Lecture #15
Embedded Database Logic
![Page 2: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/2.jpg)
CMU 15-445/645 (Fall 2018)
ADMINISTRIVIA
Project #3 is due Monday October 19th
Project #4 is due Monday December 10th
Homework #4 is due Monday November 12th
2
![Page 3: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/3.jpg)
CMU 15-445/645 (Fall 2018)
UPCOMING DATABASE EVENTS
BlazingDB Tech Talk→ Thursday October 25th @ 12pm→ CIC - 4th floor (ISTC Panther Hollow Room)
Brytlyt Tech Talk→ Thursday November 1st @ 12pm→ CIC - 4th floor (ISTC Panther Hollow Room)
3
![Page 4: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/4.jpg)
CMU 15-445/645 (Fall 2018)
OBSERVATION
Until now, we have assumed that all of the logic for an application is located in the application itself.
The application has a "conversation" with the DBMS to store/retrieve data.→ Protocols: JDBC, ODBC
4
![Page 5: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/5.jpg)
CMU 15-445/645 (Fall 2018)
CONVERSATIONAL DATABASE API
5
Application
BEGIN
SQL
Program Logic
SQL
Program Logic
⋮
COMMIT
ParserPlannerOptimizerQuery Execution
![Page 6: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/6.jpg)
CMU 15-445/645 (Fall 2018)
CONVERSATIONAL DATABASE API
5
Application
BEGIN
SQL
Program Logic
SQL
Program Logic
⋮
COMMIT
ParserPlannerOptimizerQuery Execution
![Page 7: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/7.jpg)
CMU 15-445/645 (Fall 2018)
CONVERSATIONAL DATABASE API
5
Application
BEGIN
SQL
Program Logic
SQL
Program Logic
⋮
COMMIT
ParserPlannerOptimizerQuery Execution
![Page 8: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/8.jpg)
CMU 15-445/645 (Fall 2018)
CONVERSATIONAL DATABASE API
5
Application
BEGIN
SQL
Program Logic
SQL
Program Logic
⋮
COMMIT
ParserPlannerOptimizerQuery Execution
![Page 9: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/9.jpg)
CMU 15-445/645 (Fall 2018)
CONVERSATIONAL DATABASE API
5
Application
BEGIN
SQL
Program Logic
SQL
Program Logic
⋮
COMMIT
ParserPlannerOptimizerQuery Execution
![Page 10: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/10.jpg)
CMU 15-445/645 (Fall 2018)
EMBEDDED DATABASE LOGIC
Move application logic into the DBMS to avoid multiple network round-trips.
Potential Benefits→ Efficiency→ Reuse
6
![Page 11: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/11.jpg)
CMU 15-445/645 (Fall 2018)
TODAY'S AGENDA
User-defined Functions
Stored Procedures
Triggers
Change Notifications
User-defined Types
Views
7
![Page 12: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/12.jpg)
CMU 15-445/645 (Fall 2018)
USER-DEFINED FUNCTIONS
A user-defined function (UDF) is a function written by the application developer that extends the system's functionality beyond its built-in operations.→ It takes in input arguments (scalars)→ Perform some computation→ Return a result (scalars, tables)
8
![Page 13: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/13.jpg)
CMU 15-445/645 (Fall 2018)
UDF DEFINITION
Return Types:→ Scalar Functions: Return a single data value→ Table Functions: Return a single result table.
Computation Definition:→ SQL Functions→ External Programming Language
9
![Page 14: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/14.jpg)
CMU 15-445/645 (Fall 2018)
UDF SQL FUNCTIONS
A SQL-based UDF contains a list of SQL statements that the DBMS executes in order when the UDF is invoked.→ The function returns whatever the result is of
the last query executed;
10
CREATE TABLE foo (id INT PRIMARY KEY,val VARCHAR(16)
);
CREATE FUNCTION get_foo(int)RETURNS foo AS $$
SELECT * FROM foo WHERE foo.id = $1;$$ LANGUAGE SQL;
![Page 15: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/15.jpg)
CMU 15-445/645 (Fall 2018)
UDF SQL FUNCTIONS
A SQL-based UDF contains a list of SQL statements that the DBMS executes in order when the UDF is invoked.→ The function returns whatever the result is of
the last query executed;
10
CREATE TABLE foo (id INT PRIMARY KEY,val VARCHAR(16)
);
CREATE FUNCTION get_foo(int)RETURNS foo AS $$
SELECT * FROM foo WHERE foo.id = $1;$$ LANGUAGE SQL;
Input Args
![Page 16: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/16.jpg)
CMU 15-445/645 (Fall 2018)
UDF SQL FUNCTIONS
A SQL-based UDF contains a list of SQL statements that the DBMS executes in order when the UDF is invoked.→ The function returns whatever the result is of
the last query executed;
10
CREATE TABLE foo (id INT PRIMARY KEY,val VARCHAR(16)
);
CREATE FUNCTION get_foo(int)RETURNS foo AS $$
SELECT * FROM foo WHERE foo.id = $1;$$ LANGUAGE SQL;
Return Args
![Page 17: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/17.jpg)
CMU 15-445/645 (Fall 2018)
UDF SQL FUNCTIONS
A SQL-based UDF contains a list of SQL statements that the DBMS executes in order when the UDF is invoked.→ The function returns whatever the result is of
the last query executed;
10
CREATE TABLE foo (id INT PRIMARY KEY,val VARCHAR(16)
);
CREATE FUNCTION get_foo(int)RETURNS foo AS $$
SELECT * FROM foo WHERE foo.id = $1;$$ LANGUAGE SQL;
Function Body
![Page 18: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/18.jpg)
CMU 15-445/645 (Fall 2018)
UDF EXTERNAL PROGRAMMING L ANGUAGE
Some DBMSs support writing UDFs in languages other than SQL.→ SQL Standard: SQL/PSM→ Oracle/DB2: PL/SQL→ Postgres: PL/pgSQL→ MSSQL/Sybase: Transact-SQL
Other systems support more common programming languages:→ Sandbox vs. non-Sandbox
11
![Page 19: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/19.jpg)
CMU 15-445/645 (Fall 2018)
PL/PGSQL EXAMPLE
12
CREATE OR REPLACE FUNCTION get_foo(int)RETURNS SETOF foo AS $$
BEGINRETURN QUERY SELECT * FROM foo
WHERE foo.id = $1;END;
$$ LANGUAGE plpgsql;
![Page 20: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/20.jpg)
CMU 15-445/645 (Fall 2018)
PL/PGSQL EXAMPLE (2)
13
CREATE OR REPLACE FUNCTION sum_foo(i int) RETURNS int AS $$
DECLARE foo_rec RECORD;DECLARE out INT;BEGIN
out := 0;FOR foo_rec IN SELECT id FROM foo
WHERE id > i LOOPout := out + foo_rec.id;
END LOOP;RETURN out;
END;$$ LANGUAGE plpgsql;
![Page 21: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/21.jpg)
CMU 15-445/645 (Fall 2018)
STORED PROCEDURES
A stored procedure is a self-contained function that performs more complex logic inside of the DBMS.→ Can have many input/output parameters.→ Can modify the database table/structures.→ Not normally used within a SQL query.
Some DBMSs distinguish UDFs vs. stored procedures, but not all.
14
![Page 22: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/22.jpg)
CMU 15-445/645 (Fall 2018)
STORED PROCEDURES
15
Application
BEGIN
SQL
Program Logic
SQL
Program Logic
⋮
COMMIT
![Page 23: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/23.jpg)
CMU 15-445/645 (Fall 2018)
STORED PROCEDURES
15
Application
CALL PROC(x=99)
PROC(x)
![Page 24: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/24.jpg)
CMU 15-445/645 (Fall 2018)
STORED PROCEDURES
15
Application
CALL PROC(x=99)
PROC(x)
![Page 25: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/25.jpg)
CMU 15-445/645 (Fall 2018)
STORED PROCEDURE VS. UDF
A UDF is meant to perform a subset of a read-only computation within a query.
A stored procedure is meant to perform a complete computation that is independent of a query.
16
![Page 26: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/26.jpg)
CMU 15-445/645 (Fall 2018)
DATABASE TRIGGERS
A trigger instructs the DBMS to invoke a UDF when some event occurs in the database.
The developer has to define:→ What type of event will cause it to fire.→ The scope of the event.→ When it fires relative to that event.
17
![Page 27: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/27.jpg)
CMU 15-445/645 (Fall 2018)
CREATE TABLE foo (id INT PRIMARY KEY,val VARCHAR(16)
);
TRIGGER EXAMPLE
18
CREATE TABLE foo_audit (id SERIAL PRIMARY KEY,foo_id INT REFERENCES foo (id),orig_val VARCHAR,cdate TIMESTAMP
);
![Page 28: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/28.jpg)
CMU 15-445/645 (Fall 2018)
CREATE TABLE foo (id INT PRIMARY KEY,val VARCHAR(16)
);
TRIGGER EXAMPLE
18
CREATE TABLE foo_audit (id SERIAL PRIMARY KEY,foo_id INT REFERENCES foo (id),orig_val VARCHAR,cdate TIMESTAMP
);
CREATE OR REPLACE FUNCTION log_foo_updates() RETURNS trigger AS $$
BEGINIF NEW.val <> OLD.val THENINSERT INTO foo_audit
(foo_id, orig_val, cdate)VALUES (OLD.id, OLD.val, NOW());
END IF;RETURN NEW;
END;$$ LANGUAGE plpgsql;
Tuple Versions
![Page 29: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/29.jpg)
CMU 15-445/645 (Fall 2018)
CREATE TABLE foo (id INT PRIMARY KEY,val VARCHAR(16)
);
TRIGGER EXAMPLE
18
CREATE TABLE foo_audit (id SERIAL PRIMARY KEY,foo_id INT REFERENCES foo (id),orig_val VARCHAR,cdate TIMESTAMP
);
CREATE OR REPLACE FUNCTION log_foo_updates() RETURNS trigger AS $$
BEGINIF NEW.val <> OLD.val THENINSERT INTO foo_audit
(foo_id, orig_val, cdate)VALUES (OLD.id, OLD.val, NOW());
END IF;RETURN NEW;
END;$$ LANGUAGE plpgsql;
CREATE TRIGGER foo_updatesBEFORE UPDATE ON foo FOR EACH ROWEXECUTE PROCEDURE log_foo_updates();
![Page 30: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/30.jpg)
CMU 15-445/645 (Fall 2018)
TRIGGER DEFINITION
Event Type:→ INSERT→ UPDATE→ DELETE→ TRUNCATE→ CREATE→ ALTER→ DROP
19
Trigger Timing:→ Before the statement executes.→ After the statement executes→ Before each row that the statement
affects.→ After each row that the statement
affects.→ Instead of the statement.
Event Scope:→ TABLE→ DATABASE→ VIEW→ SYSTEM
![Page 31: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/31.jpg)
CMU 15-445/645 (Fall 2018)
CHANGE NOTIFICATIONS
A change notification is like a trigger except that the DBMS sends a message to an external entity that something notable has happened in the database.→ Think a "pub/sub" system.→ Can be chained with a trigger to pass along whenever a
change occurs.
SQL standard: LISTEN + NOTIFY
20
![Page 32: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/32.jpg)
CMU 15-445/645 (Fall 2018)
NOTIFICATION EXAMPLE
21
CREATE OR REPLACE FUNCTION notify_foo_updates() RETURNS trigger AS $$
DECLARE notification JSON;BEGIN
notification = row_to_json(NEW);PERFORM pg_notify('foo_update',
notification::text);RETURN NEW;
END;$$ LANGUAGE plpgsql;
NotificationPayload
![Page 33: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/33.jpg)
CMU 15-445/645 (Fall 2018)
NOTIFICATION EXAMPLE
21
CREATE OR REPLACE FUNCTION notify_foo_updates() RETURNS trigger AS $$
DECLARE notification JSON;BEGIN
notification = row_to_json(NEW);PERFORM pg_notify('foo_update',
notification::text);RETURN NEW;
END;$$ LANGUAGE plpgsql;
NotificationPayload
CREATE TRIGGER foo_notifyAFTER INSERT ON foo_audit FOR EACH ROWEXECUTE PROCEDURE notify_foo_updates();
![Page 34: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/34.jpg)
CMU 15-445/645 (Fall 2018)
OBSERVATION
All DBMSs support the basic primitive types in the SQL standard. They also support basic arithmetic and string manipulation on them.
But what if we want to store data that doesn't match any of the built-in types?
22
coordinate (x, y, label)
![Page 35: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/35.jpg)
CMU 15-445/645 (Fall 2018)
COMPLEX T YPES
Approach #1: Attribute Splitting→ Store each primitive element in the complex
type as its own attribute in the table.
Approach #2: Application Serialization→ Java serialize, Python pickle→ Google Protobuf, Facebook Thrift→ JSON / XML
23
INSERT INTO location (coord)VALUES ('{x:10, y:20, label:"OTB"}');
INSERT INTO locations(x, y, label)
VALUES(10, 20, "OTB");
CREATE TABLE locations (coord JSONB NOT NULL
);
![Page 36: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/36.jpg)
CMU 15-445/645 (Fall 2018)
USER-DEFINED T YPES
A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively.→ First introduced by Postgres in the 1980s.→ Added to the SQL:1999 standard as part of the "object-
relational database" extensions.
Sometimes called structured user-defined typesor structured types.
24
![Page 37: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/37.jpg)
CMU 15-445/645 (Fall 2018)
USER-DEFINED T YPES
Each DBMS exposes a different API that allows you to create a UDT.→ Oracle supports PL/SQL.→ DB2 supports creating types based on built-in types.→ MSSQL/Postgres only support type definition using
external languages (.NET, C)
25
CREATE TYPE coordinates AS OBJECT (x INT NOT NULL,y INT NOT NULL,label VARCHAR(32) NOT NULL
);
![Page 38: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/38.jpg)
CMU 15-445/645 (Fall 2018)
VIEWS
Creates a "virtual" table containing the output from a SELECT query. The view can then be accessed as if it was a real table.
This allows programmers to simplify a complex query that is executed often.→ Won’t make it faster though.
Often used as a mechanism for hiding a subset of a table's attributes from certain users.
26
![Page 39: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/39.jpg)
CMU 15-445/645 (Fall 2018)
VIEW EXAMPLE (1 )
Create a view of the CS student records with just their id, name, and login.
27
Original Table
CREATE VIEW cs_students ASSELECT sid, name, login
FROM studentWHERE login LIKE '%@cs';
sid name login age gpa
53666 Kanye West kw@cs 40 3.5
53677 Justin Bieber jb@ece 23 2.25
53688 Tone Loc tloc@isr 51 3.8
53699 Andy Pavlo pavlo@cs 36 3.0
![Page 40: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/40.jpg)
CMU 15-445/645 (Fall 2018)
VIEW EXAMPLE (1 )
Create a view of the CS student records with just their id, name, and login.
27
Original Table
CREATE VIEW cs_students ASSELECT sid, name, login
FROM studentWHERE login LIKE '%@cs';
sid name login age gpa
53666 Kanye West kw@cs 40 3.5
53677 Justin Bieber jb@ece 23 2.25
53688 Tone Loc tloc@isr 51 3.8
53699 Andy Pavlo pavlo@cs 36 3.0
sid name login
53666 Kanye West kw@cs
53699 Andy Pavlo pavlo@cs
SELECT * FROM cs_students;
![Page 41: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/41.jpg)
CMU 15-445/645 (Fall 2018)
VIEW EXAMPLE (1 )
Create a view of the CS student records with just their id, name, and login.
27
Original Table
CREATE VIEW cs_students ASSELECT sid, name, login
FROM studentWHERE login LIKE '%@cs';
sid name login age gpa
53666 Kanye West kw@cs 40 3.5
53677 Justin Bieber jb@ece 23 2.25
53688 Tone Loc tloc@isr 51 3.8
53699 Andy Pavlo pavlo@cs 36 3.0
sid name login
53666 Kanye West kw@cs
53699 Andy Pavlo pavlo@cs
SELECT * FROM cs_students;
![Page 42: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/42.jpg)
CMU 15-445/645 (Fall 2018)
VIEW EXAMPLE (2)
Create a view with the average age of all of the students.
28
CREATE VIEW cs_gpa ASSELECT AVG(gpa) AS avg_gpaFROM studentWHERE login LIKE '%@cs';
![Page 43: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/43.jpg)
CMU 15-445/645 (Fall 2018)
VIEWS VS. SELECT INTO
VIEW→ Dynamic results are only materialized
when needed.
SELECT…INTO→ Creates static table that does not get
updated when student gets updated.
29
CREATE VIEW cs_gpa ASSELECT AVG(gpa) AS avg_gpa
FROM studentWHERE login LIKE '%@cs';
SELECT AVG(gpa) AS avg_gpaINTO cs_gpaFROM studentWHERE login LIKE '%@cs';
![Page 44: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/44.jpg)
CMU 15-445/645 (Fall 2018)
VIEWS VS. SELECT INTO
VIEW→ Dynamic results are only materialized
when needed.
SELECT…INTO→ Creates static table that does not get
updated when student gets updated.
29
CREATE VIEW cs_gpa ASSELECT AVG(gpa) AS avg_gpa
FROM studentWHERE login LIKE '%@cs';
SELECT AVG(gpa) AS avg_gpaINTO cs_gpaFROM studentWHERE login LIKE '%@cs';
![Page 45: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/45.jpg)
CMU 15-445/645 (Fall 2018)
UPDATING VIEWS
The SQL-92 standard specifies that an application is allowed to modify a VIEW if it has the following properties:→ It only contains one base table.→ It does not contain grouping, distinction, union, or
aggregation.
30
![Page 46: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/46.jpg)
CMU 15-445/645 (Fall 2018)
MATERIALIZED VIEWS
Creates a view containing the output from a SELECT query that is automatically updated when the underlying tables change.
31
CREATE MATERIALIZED VIEW cs_gpa ASSELECT AVG(gpa) AS avg_gpaFROM studentWHERE login LIKE '%@cs';
![Page 47: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/47.jpg)
CMU 15-445/645 (Fall 2018)
CONCLUSION
Moving application logic into the DBMS has lots of benefits.→ Better Efficiency→ Reusable across applications
But it has problems:→ Not portable→ DBAs don't like constant change.→ Potentially need to maintain different versions.
32
![Page 48: Embedded Database Logic · USER-DEFINED TYPES A user-defined type is a special data type that is defined by the application developer that the DBMS can stored natively. →First introduced](https://reader030.vdocuments.us/reader030/viewer/2022040519/5e780dc3b25e7654c62e694c/html5/thumbnails/48.jpg)
CMU 15-445/645 (Fall 2018)
NEXT CL ASS
TRANSACTIONS!!!
33