Programs with SQL

1. Programs with SQL Host language + Embedded SQL Preprocessor Preprocessor Host Language + function calls Host language compiler Host language compiler Host language program

2. The Impedance Mismatch Problem • The host language manipulates variables, values, pointers • SQL manipulates relations. • There is no construct in the host language for manipulating • relations. • Why not use only one language? • Forgetting SQL: definitely not a good idea! • SQL cannot do everything that the host language can do.

3. Using Shared Variables Void simpleInsert() { EXEC SQL BEGIN DECLARE SECTION; char productName[20], company[30]; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; /* get values for productName and company somehow */ EXEC SQL INSERT INTO Product(name, company) VALUES (:productName, :company); }

4. Single-Row Select Statements Void getPrice() { EXEC SQL BEGIN DECLARE SECTION; char productName[20], company[30]; integer price; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; /* read value of product name */ EXEC SQL SELECT price INTO :price FROM Product WHERE Product.name = :productName; /* print out value of price */ }

5. Cursors EXEC SQL DECLARE cursorName CURSOR FOR SELECT …. FROM …. WHERE …. ; EXEC SQL OPEN cursorName; while (true) { EXEC SQL FETCH FROM cursorName INTO :variables; if (NO_MORE_TUPLES) break; /* do something with values */ } EXEC SQL CLOSE cursorName;

6. Motivations for OODB’s(golden rules of OODB’s) • Complex objects (sets, lists, bags, arrays, tuples, constructors) (IM) • Object identity: • (peter, 40, {(john, 15, {})}) • (susan, 41, {(john, 15, {})}) • Same son? Maybe, maybe not. • Encapsulation: (modularity, protection, less distinction between • program and data). (IM) • Types and classes. • Hierarchies (of classes and types) • Overloading and late binding of method names. • Computational completeness. (IM) • Type extensibility. (IM)

7. Object Definition Language • Is part of ODMG, which also gives us OQL. • Resembles C++ (and Smalltalk). • Main concepts: • objects, literals • state of objects: values of its properties: • attributes, relationships, methods • behavior: defined by methods • types • classes, extents, keys.

8. ODL Class Declarations Interface <name> { attributes: <type> <name>; relationships <range type> <name>; methods } Method example: float gpa(in: Student) raises (noGrades) Arbitrary function can compute the value of gpa, based on a student object given as input.

9. Types in ODL Basic types: Atomic types (e.g., string, integer, …) Interface types (e.g., Person, Product, Company) Constructors: Set: (1, 5, 6) Bag: (1, 1, 5, 6, 6 ) List: (1, 5, 6, 1, 6 ) Array: Integer[17] Struct: {string street, string city, integer zipcode}

10. ODL Declarations Interface Product { attribute string name; attribute float price; attribute enum Categories {electronics, communications, sports …} category } Interface Company { attribute string name; attribute float stockprice; } Interface Person { attribute integer ssn; attribute string name; attribute Struct Address {string street, string city} address; }

11. ODL Declarations Interface Product { attribute string name; attribute float price; attribute enum Categories {electronics, communications, sports …} category; relationship <Company> madeBy; } Interface Person { attribute integer ssn; attribute string name; attribute Struct Address {string street, string city} address; relationshipset <Product> buys; relationshipset <Company> worksFor;}

12. ODL Declarations Interface Company { (key name); attribute string name; attribute float stockprice; relationship set <Product> makes inverse Product::madeBy; relationship set <Person> employs inverse Person::worksFor; }

13. Example Method Interface Movie { (extent Movies) attribute string title; relationship Set <Star> stars inverse Star::starredIn; float lengthInHours raises(noLengthFound); starNames (out Set <String>); otherMovies (in Star, out Set<Movie>) raises (noSuchStar); } Note: defining signature, not implementation. Overloading allowed.

14. OQL: Object Query Language SELECT can construct new objects, arbitrary structures FROM tuple variables can range over any collection; may have subqueries. WHERE pretty much the same as in SQL

15. Path Expressions Path expressions are needed in order to access components of objects. Attributes: a.p is the value of the attribute a of p. Relationships: a.p is the object or collection of objects related to a by p. Methods: a.p is the result of applying p to a (perhaps with a parameter). Also possible to have longer expressions: a.father.wife.child.father.wife….

16. Select-From-Where in OQL (simple) Example: SELECT s.name FROM Movies m, m.stars s WHERE m.title = “Sleepless in Seattle” Note: this looks a lot more procedural than SQL.

17. Complications in the FROM Clause SELECT a.phoneNumber FROM Movies m, (SELECT m.address FROM m.stars WHERE m.city=“Los Angeles”) AS a WHERE m.title = “Sleepless in Seattle” The FROM clause can contain arbitrary subqueries that return collections.

18. Complex Output Types The SELECT clause can create complex structures: SELECT Struct: (address: a, phoneNumber: a.phoneNumber) FROM Movies m, (SELECT m.address FROM m.stars WHERE m.city=“Los Angeles”) AS a WHERE m.title = “Sleepless in Seattle”

19. Other Features of OQL • Ordering of the results • ORDER BY m.title, m.year. • Subqueries wherever a collection is appropriate. • Quantifier expressions: • FOR ALL x IN S : C(x) • EXISTS x IN S:C(x) • Aggregation, grouping and HAVING clauses. • Set operators: UNION, INTERSECT, EXCEPT (different if • operating on bags or sets. • Remove duplicates: SELECT DISTINCT.

20. Interface with Host Language OQL is much more tightly integrated with the host language. OQL produces objects (of various types). One can simply assign the objects as values to variables with the appropriate types. No need for special interface. ELEMENT: turns a bag of one element into the single element: var1 = ELEMENT (SELECT m FROM Movies m WHERE title=“sleepless in Seattle”);

21. Handling Sets or Bags First: turn them into lists (using ORDER BY). Then: use host language operations to go through them. Example: movieList = select m FROM Movies m WHERE m.year > 1990 ORDER BY m.title, m.year; numberOfMovies = COUNT (movieList); for (I=0; I < numberOfMovies; I++) { movie = movieList[I]; do something with it. }