Plan:

• Go to Ruhlman!
• anonymous hw8 submission. Only 13?
• Talk a little about documentation
• Recap of security stuff
• Compare/Contrast PHP versus Perl versus Java
• Object databases

Compare/Contrast Middleware Languages

There are many comparison sites. Google on "php versus perl"

• PHP Vs. Perl
• Why Netconcepts prefers PHP
• PHP versus other languages
• PHP versus other languages chart

PHP

• A pre-packaged solution with most desirable features built-in
• Easy switching from HTML to scripting and back; others require "print" statements
• Possibly more efficient than Perl (although mod_perl eliminates that)
• lacks the modularity and abstractions that Perl and Java have
• Error messages to browser can leak information.
• Nice online documentation
• Having the number of results is convenient (albeit a memory hog)
• Syntax Example:

      result = mysql_query("select * from products where productDesc='$prod '");
      $myrow = mysql_fetch_array($result);
      printf (" ID number: %s, Highest bid: %s, Seller: %s",
           $myrow["idnum"], $myrow["currentHighBid"], $myrow["sellerId"]);
  

• Usually have to develop and test on the web: who's watching?
• Automatic quoting is helpful
• Almost all web-hosting sites provide this.

Perl

• More of a general purpose language
• superior string crunching power
• convenient functions for writing HTML
• many more modules available (CPAN)
• With CGI::Carp qw(fatalsToBrowser), debugging is less painful, and easy to turn off
• Syntax example:

       my $sth1= $dbh->prepare("select * from products where productDesc='$prod'");
       $sth1->execute;
       (my $idnum, my $sellerId, my $currentHighBid)=  $sth1->fetchrow_array();
       print p(" ID number: $idnum, Highest bid: $currentHighBid, Seller: $sellerId");
  

• documentation is harder to find.
• Taint mode is helpful
• suexec mode is very good when there are multiple users

Java Servlets

• Industrial strength language
• Best performance
• Lots of additional modules, though a slower development cycle
• No support for writing HTML
• File upload isn't standardized yet
• Deploying is bit more of a pain, no "quick and dirty sites"
• Few web-hosting sites provide this.
• Run-time errors pretty much always go to logs (good and bad)
• URL mapping is pretty cool.

Language "wars" are ultimately futile. People choose languages for a variety of reasons:

Is is true that Worse is Better

Object Databases (16.1-3)

Troubles with Relations

Relational databases are not perfectly general. Some shortcomings:

Set-Valued Attributes

Our prior example was hobbies, and we solved this redundancy by decomposing the one table into two, where the second had a tuple for each hobby and the key of the first table.

Another example is a table like this:

SSN	Name	Phone	Child

This has lots of redundancy and functional dependency.

• What is the key?
• What is the functional dependency?

Can solve by decomposition into:

SSN Name

SSN Phone

SSN Child

Awkward Queries

Consider a query like "List phone numbers for Joe's grandchildren":

Original table:

SELECT G.Phone
FROM   Person P, Person C, Person G
WHERE  P.Name = 'Joe' AND
       P.Child = C.SSN AND
       C.Child = G.SSN

Decomposed tables:

SELECT N.Phone
FROM   Children C, Children G, Person P, Phone N
WHERE  P.Name = 'Joe' AND
       P.SSN = C.SSN AND
       C.Child = G.SSN AND
       G.SSN = N.SSN

Supposedly these are really cumbersome for such a simple request. But compared to those horrible "list professors who taught all classes in all departments"; it's a piece of cake. Still, it could be better.

Solution:

• Allow set-valued attributes (after all, it's just a variable-length record)
• results in a more natural key

Even allow the attributes to be real tuples, so that the queries can be:

SELECT P.Child.Child.Phone
FROM   Person P
WHERE  P.Name= 'Joe'

ISA Hierarchies

We've seen ISA hierarchies before. To implement them, we might have separate tables, and a query might be:

SELECT P.Name
FROM   Person P, Student S
WHERE  P.SSN = S.SSN AND S.Major = 'CS'

If the database supported ISA hierarchies, this would be much more intuitive:

SELECT S.Name
FROM   Student S
WHERE  S.Major = 'CS'

Blobs

Large binary objects. Can have idiosyncratic operators, such as height and width (for JPEGS) or playing time (for MP3s). Now this is suited for OOP.

Objects

Define methods for objects like blobs.

Impedance Mismatch

A declarative language (SQL) embedded in a procedural language (C) is a bit of a mismatch.

• The problem is that a query returns a set or sequence,
• the procedural languages typically deal only with elements.
• Hence the "cursor" approach
• However, there are lots of languages that support lists well: Lisp, Scheme, OCaml, etc.

Instead, put an OOP database language in an OOP programming language (Smalltalk, C++ or Java).

Troubles:

• all those programming languages are still procedural.
• they have different object models

Summary

• class = table, object = tuple
• in RDMS, attributes are simple scalar primitives, in OO DB, attributes are objects:
  • sets
  • tuples
  • objects
• objects can have inheritance hierarchy
• objects can have methods, implemented in host language

History

• Early 80s: nested relations: tuples can contain relations. Complex and insufficiently powerful
• Mid 80s: persistent objects: a program can have objects that last between invocations (saved on disk). However, no high-level query language.
• Early 90s: Object-relational databases. Tuples can contain objects, not just attributes. However, no top-level objects.
• Mid 90s: proliferation of object DBMSes
• Mid to late 90s: need for standards. At least three:
  1. ODMG: Object Definition Language (ODL), Object Query Language (OQL) and host language bindings
  2. SQL:1999 Similar concepts, different syntax.
  3. CORBA: Client/server architecture for object databases. Very employable.

Conceptual Object Data Model (16.2)

Objects and Values

Every object has an OID which is unique and immutable. (Rather like a memory address or pointer.)

An object is defined as a pair of (OID, value), where value is:

• primitive type
• reference value -- an oid
• tuple value -- like a hash: a collection of attributes and values
• set value

Example

(#32, [ SSN: 111-22-3333,
        Name:  Joe Public,
	PhoneN:  {"516-123-4567", "516-345-7890"},
	Child:  {#445, #73} ] )

• primitive values
• reference values
• tuple values
• set values

Classes

A class is a collection of objects, analogous to a table.

Q: is that different from OOP?

It has a type, which indicates the structure of each object (primitive types, reference values, and so forth).

Can also have method signatures.

Classes have relationships to other classes. The extent of a class is its set of objects.

Types

The type of an object is the structure of the types of its components, which may be aggregate (like tuple types and set types).

Determining if an object is of a type gets tricky.

• subtype and supertype: one type has additional components than another. Recursive definition, of course.
• domain of a type:
  • primitive type
  • reference type is the extent of the class
  • tuple type is recursively defined, sets of domains
  • set type, the set of sets of a domain

Rule: The domain of a subtype is a subset of the domain of a supertype.

Domain(Student) ⊂ Domain(Person)

Brain Teaser: What is the domain of the tuple type [], which has no attributes?

Database Schema and Instance

The schema contains the specification for each class of objects. For each class, it has

• The type (a structure)
• method signatures
• the subclass-of relation; they have multiple inheritance!
• integrity constraints

Object-Relational Databases

Intended as gentle transition. Such a database has:

• set of relations (classes)
• each relation is a set of tuples (objects)
• each tuple is a pair of (oid, value).

Objects in SQL:1999 and SQL:2003 (16.3)

Row Types:

CREATE TABLE Person(
   Name CHAR(20),
   Address ROW(Number INTEGER, Street CHAR(20), Zip CHAR(5)))

Then, just use path expressions

SELECT P.Name
FROM Person P
WHERE P.Address.Zip = '11794'

INSERT INTO Person(Name, Address)
VALUES ('John Doe', ROW(666, 'Hollow Rd.', '12345'))

UPDATE Person
SET Address.Zip = '12346'
WHERE Address.Zip = '12345'

UPDATE Person
SET Address = ROW(21,'Main St.', '54321')
WHERE Address = ROW(666, 'Hollow Rd.', '12345') AND Name = 'John Doe'

User-Defined Types

CREATE TYPE PersonType AS (
   Name CHAR(20),
   Address ROW(Number INTEGER, Street CHAR(20), Zip CHAR(5)));

CREATE TYPE StudentType UNDER PersonType AS (
   Id INTEGER,
   Status CHAR(2) )

METHOD award_degree() RETURNS BOOLEAN;

CREATE METHOD award_degree() FOR StudentType
LANGUAGE C
EXTERNAL NAME 'file:/home/admin/award_degree';

Notice the inheritance and the method declaration and definition.

We can create tables using these types:

CREATE TABLE Transcript (
   Student  StudentType,
   CrsCode  CHAR(6),
   Semester CHAR(6),
   Grade    CHAR(1) )

and

CREATE TABLE Student OF StudentType;

CREATE TABLE Student2 OF StudentType
REF IS stud_oid;

Objects

Queries:

SELECT T.Student.Name, T.Grade
FROM Transcript T
WHERE T.Student.Address.Street = 'Hollow Rd.';

Updates:

INSERT INTO Transcript( Student, Course, Semester, Grade)
VALUES (NEW StudentType()
          .Id(666)
	  .Status('G5')
	  .Name('Vlad the Impaler')
	  .Address(ROW(666,'Transylvania Ave.','12345')),
	'HIS100',
	'F1462',
	'D')

There are accessor and mutator methods automatically defined for each attribute. The mutators return the whole object, not the new value. Why?

We could define new methods to "flatten" the Address information.

Reference Types

CREATE TABLE Transcript2 (
   Student REF(StudentType) SCOPE Student2,
   CrsCode CHAR(6),
   Semester CHAR(6),
   Grade    CHAR(1) )

The SCOPE provides some referential integrity. Only students in Student2 are allowed in here.

As in C/C++, the syntax changes:

SELECT T.Student->Name, T.Grade
FROM Transcript2 T
WHERE T.Student->Address.Street = 'Hollow Rd.'

To insert, you need to be able to get an OID:

INSERT INTO Transcript2 (Student, Course, Semester, Grade)
SELECT S.stud_oid, 'HIS100', 'F1462', 'D'
FROM Student2 S
WHERE S.Id = '666'

Inheritance

Tuples inserted into subclasses are automatically inserted into all listed superclasses:

CREATE TABLE Student OF StudentType UNDER Person

INSERT INTO Student(Name,Address,Id,Status)
VALUES ('John Jones', ROW(123, 'Main St.',11733), 111223333, 'G2')

Collection Types

SQL:2003 allows MULTISET as an attribute type. Treated as a 1-column table

CREATE TYPE StudentType UNDER Person AS (
   Id INTEGER,
   Status CHAR(2),
   Enrolled REF(CourseType) MULTISET
)

SELECT S.Id, C.Name
FROM Student S, Course C
WHERE C.CrsCode IN
   ( SELECT E->TmpCrsCode
     FROM UNNEST(S.Enrolled) AS TmpCourse(TmpCrsCode) E )

Ghastly syntax!

And in the other direction:

CREATE TABLE TranscriptI (
   Student  REF(StudentType),
   Course   REF(CourseType),
   Semester CHAR(6),
   Grade    CHAR(1))

INSERT INTO STUDENT(Id,Status,Enrolled)
VALUES (12345678, 'G2', MULTISET(SELECT T.Course
                                 FROM TranscriptI T
				 WHERE TranscriptI->Id=12345678)