Objects & Databases Trends over the last 25 years by Dolan

Objects & Databases Trends over the last 25 years by Dolan Antenucci and Poorva Potdar

Overview 1.Objects and Databases in 1986 2.Trends with Objects and Databases 3.POSTGRES Data/Query Model & Fast Path 4.POSTGRES Rules and Storage 5.POSTGRES v2.1 Implementation 6.Future of Objects and Databases

Back in 1986 . Connection between objects and databases was new and getting explored. Persistent Programming Languages Extended Relational Database Systems New Era of Objects Database system toolkits. Object Oriented Database Systems.

Why Extended Relational Databases? Motivation: Storage and Querying of complex Data-types Example: Probabilistic Databases Location of ACL Conference in 2012 is either Europe or USA, each with probability 0.5. Representation? {USA/0.5,Europe/0.5} ({Paris,Vienna}/0.5 , {Michigan, California}/0.5) Solution: Abstract Data-types.

What are ADT's? User defined Abstract Data-types, o Register with Database - System aware of its size and functions. o Benefits - Encapsulation of data and methods of an object Re usability Flexibility.

Impedance Mismatch still persists. Arises at the boundary when Programming Language meets the Relational Database. Eg: Data Model for Departmental statistics.

Persistent Programming Languages Motivation: Reduce the impedance mismatch How? - Allow objects to be created and stored in a database, and used directly from a programming language o o o No need of SQL to query data. No Need of explicit format type changes. Allow objects to be manipulated in-memory. Drawbacks?o o Easy to make programming errors Complexity of languages make Optimization difficult.

Object Oriented Database Systems Motivation: Reduce impedance mismatch, support for querying and indexing and addressing version management. .

Object Oriented Database Systems Drawbackso No uniform agreement on the any OODB paradigm. o Differences in several OODB products as no standard. [Only O2 supports all standards of OQL] o Behind with respect to Relational DB - View facility not provided, Schema Evolution is a pain. o Robustness, scalability and Fault-tolerance not as good as Relational DB.

Database System Toolkits/Components Motivation: To build a Domain-Specialized Database system. Difference in Query Languages, access methods, storage organizations and transaction mechanisms. Eg: Geographic Information Systems manages the Geographic Data.

Overview 1.Objects and Databases in 1986 2.Trends with Objects and Databases 3.POSTGRES Data/Query Model & Fast Path 4.POSTGRES Rules and Storage 5.POSTGRES v2.1 Implementation 6.Future of Objects and Databases

What was the conclusion? Four Database Systems since 1986. Outcome? o Losers Database System Tool-kits Persistent Programming languages o Survivors Object Oriented Databases. Extended Relations Databases

Casualty 1 - Database System Tool-kits. o Too much Expertise required o Inflexible and incomplete in terms of database design. o Query Optimizer was general but inefficient to use, left details of Logical Query rewrites and predicates to the implementer. o Very less control over buffering, concurrency and recovery.

Casualty 2 - Persistent Programming Languages No commercial implementation of a pure persistent programming language. Why not a complete disaster? o Impact on the research of many of OODB's products. o Persistence Models, Pointer Swizzling Mechanisms[?] and garbage collection schemes relate to OODB concepts.

Extended Relational Databases. In parallel with OODB, extended relational DB also matured. [CAIngres, IBM, Illustra]

Object Relational Databases. ORDB have relational model and a Query language built from there. Support ADT's and Row types. Set Types Shortcomings- No agreement on ORDB paradigms.

Overview 1.Objects and Databases in 1986 2.Trends with Objects and Databases 3.POSTGRES Data/Query Model & Fast Path 4.POSTGRES Rules and Storage 5.POSTGRES v2.1 Implementation 6.Future of Objects and Databases

Postgres Motivation Factors Motivating towards Postgres ADT’s to support Bitmaps, Videos, text etc Support for Data, object and Knowledge Management Object and Rule management Supports Nooverwrite Storage manager and Time Travel

Postgres Data Model & Query Language Design Criteria Postgres Data Model Postgres Functions Postgres Query Language Fast Path

Design Criteria Three Design Criteria Orientation towards Database access from Query Language. Orientation towards Multilingual access [ Neutral and can tightly couple with any Language] Smaller Number of Concepts [Constructs like classes, Inheritance, types and functions.]

Postgres Data Model Classes - Collection of instances of objects. Eg: Create EMP (name C12, salary float, age int) EMP Name C12 Salary Float Age int Salesman Quota float Inheritance Eg:Create salesman (quota float ) inherits EMP. Types of Classes- Real Classes, Derived Classes, Versioned classes

Postgres Data-Model Postgres Data-types Base Types – ADT Eg: Create DEPT (dname c10, manager c12, floorspace polygon, mailstop point) Replace DEPT(mailstop "(10,10)", where DEPT.name "shoe") Arrays of Base Types Create EMP(name c12, salary float[12], age int) Composite Datatypes. Two Types: Nested Definition, Set Definition create EMP( name c12, salary float, age int, manager EMP, corworkers EMP) add to EMP (hobbies set)

Postgres Functions C – Functions Eg: retrieve (EMP.name) where overpaid(EMP) o o Overpaid returns a boolean. Flexibility of invoking like an attribute. Eg: retrieve (EMP.name) where EMP.overpaid o Drawbacks-Optimization is left to the User. Operators Operators are functions with one or more operands Eg: retrieve(DEPT.dname) where DEPT.floorspace AGT "(0,0),(1,1),(2,2)“ o o Flexibility to write new operator- Creator can define how B -tree can be created. o Postgres requires the user to write 13 C functions which perform the record level operations. Liberty of optimization by writing multidimensional access methods. o PostQuel Functions Set of commands in a Postgres query can be packaged together to define a Postquel function. Eg: define function high-pay returns EMP as retrieve (EMP.all) where EMP.sal 50000 o Postquel functions can have parameters accessed by the sign. Eg: define function high-pay(C12) returns EMP as retrieve (EMP.name) where EMP.sal 50000 and EMP.name 1 o Postgres Functions

Postgres Query Language Nested Queries To find dept that occupies the entire floor. Eg: retrieve (DEPT.dname ) where DEPT.floor not in {D.floor from D in DEPT where D.dname! Transitive Closure Eg: To find all ancestors of Joe parent (older,younger) retrieve * into ans (parent.older) from a into ans where parent.younger 'Joe' or parent.younger a.older. DEPT.dname} Inheritance retrieve (E.name )from E in EMP* where E.age 40. The * indicates that query should be run over all derived classes of EMP. Postgres Query Language Time Travel Stores archives and historical data. Eg: retrieve (*) from EMP(T)

Fast Path Motivation : To provide direct access to low level functions without checking for validation. o Construction of a parse tree for a Specialized Query. Require User to access any Postgres function and directly call the parser, optimizer, executor or any access methods. Eg: Sensor Database

Fast Path Temp. Sensor Database MI Region Ann Arbor Block1 T1 Block2 T2 Detroit Block1 T3 Block2 T4 Block1 T5 Ohio Ada Query to retrieve the average temperature of all cities in a particular state. User can access the Query optimizer to add the function asAvg (T1,T2,T3, ) (T1 T2 T3 )/ N Now the Query to retrieve avg temp is - Retrieve Temp into T from TS where Temp Avg(TS1,TS2,TS3, .)

Overview 1.Objects and Databases in 1986 2.Trends with Objects and Databases 3.POSTGRES Data/Query Model & Fast Path 4.POSTGRES Rules and Storage 5.POSTGRES v2.1 Implementation 6.Future of Objects and Databases

POSTGRES Rules System Motivation: One System to RULE them all!

POSTGRES Rules System Implementation of rules

POSTGRES Rules System Inner-workings: Rules defined in POSTQUEL Rule Chaining o Since rules can trigger other rules, or can involve derived forms, chaining is required. Semantics of rules o o Immediate vs. deferral Same vs. separate transaction

POSTGRES Rules System Example of use: Triggers Enforcing employees have same salary

POSTGRES Rules System Application example: Views User-level syntax is compiled into one or more rules POSTGRES takes more general approach to updates than traditional RDBMS's

POSTGRES Rules System Application example: Versions Similar to branching in Source Control

POSTGRES Storage System Motivation: Be different!

POSTGRES Storage System The old storage manager: "write-ahead logging" Used to ensure atomicity and durability Before changes are applied, they are written to a log

POSTGRES Storage System The new storage manager: "no-overwrite" No transaction log used, so only one write to disk Old record remains in database

POSTGRES Storage System Time Travel (a.k.a. Versioning)

Overview 1.Objects and Databases in 1986 2.Trends with Objects and Databases 3.POSTGRES Data/Query Model & Fast Path 4.POSTGRES Rules and Storage 5.POSTGRES v2.1 Implementation 6.Future of Objects and Databases

POSTGRES Implementation (v2.1) Four areas different from RDBMS: 1. Process structure 2. Extendability 3. Dynamic loading 4. Rule wake-up

POSTGRES Performance (v2.1) Summary of Tests At time of paper (June 1991), POSTGRES v2.1 was running on 125 sites Use the Wisconsin benchmark and an engineering benchmark Systems compared with: o UC Berkeley version of INGRES o Commercial version of INGRES from ASK o Cattell's in-house system o Commercial OODB o Commercial RDBMS

POSTGRES Performance (v2.1) Summary of Results

POSTGRES Performance (v2.1)

Overview 1.Objects and Databases in 1986 2.Trends with Objects and Databases 3.POSTGRES Data/Query Model & Fast Path 4.POSTGRES Rules and Storage 5.POSTGRES v2.1 Implementation 6.Future of Objects and Databases

POSTGRES Future (1996 to present) Postgres95 -- replaced POSTQUEL with SQL Spun off into Open Source project, PostgreSQL as v6.0 Implemented many standard DBMS features Up to v9.1 with (K-nearest-neighbor indexing, etc.)

Future of Objects and Databases "Predictions for 2006" Fully integrated solution Server functionality & performance Client integration Legacy data sources Standardization