Persistence MSO 08 09 Chapter 11 WP Persistence

Persistence MSO 08/09, Chapter 11, WP

Persistence to persist = to last , to endure In a business application you often need your data to last even if you sometimes have to shut down the application. Simple: Save the data. When the application is up again, it re-load the data. 2 But this is both critical and challenging!

Challenges Reliability my data should really be saved Data integrity (latter) Ability to rollback Performance I want to persist terra bytes of data Fast query and update Concurrency I have concurrent clients accessing the data 3

Objectives of chapter 11, learning: several object-persistence formats about mapping objects to your persistence formats optimizing relational data bases Design 4 Implementation

How to 'persist' my data ? Save them in files. Simple But a business app often requires more: Querying specific parts of data Fast query DBMS Backup and roll back Access control Save them in a database. 5

Typical Architecture (of business app) Presentation / User Interface Application Logic / PD Layer In our setup this layer is in OO. Persistence/DAM Layer database 6 Design issue: which persistence approach should we use?

Recommended architecture in Dennis et al DAM takes care interaction with DBMS (e. g. to save and load). Problem Domain (PD) Layer Data Access Management (DAM) Layer DBMS 7 • Relieve PD classes from having to implement save and load themselves • Makes PD independent of the underlying DBMS. Person ------Name Age Person. DAM ----------------save delete load

Database. . . traditionally is relational 8 Means that you store data in relation forms, aka tables.

Basic elements of a table primary key foreign key attribute row column 9

Referential Integrity 10 Foreign and primary keys should be consistent.

RDBMS Relational Data Base Management System Refers to an implementation of a relational database concept. They come with strong features: 11 Powerful query language (SQL) Access control Referential integrity High performance ACID (Atomicity, Consistency, Isolation, Durability) transactions Proven technology. . .

So, how to persist objects in an RDB ? RDB has no notion of inheritance. Still, we can map a class diagram to an ER diagram (next slide) This will tell how to map our class structures to tables. But. . . when we save an object: save(patient) 12 this is not the same as inserting a row. We may potentially have to save the object substructures its induced relations. Same goes with load and delete.

Mapping ER diagram Person -------------------Name : String Age : int 1 Sub. Object. Of 0. . 1 Patient ---------ID : String Insurance : String Patient ----------ID Insurance 0. . * Suffers 0. . * 13 Symptom ----------Code Name suffers 0. . * Symptom --------Code : String Name : String So, how do you implement : save (Patient p) Patient load()

Various DB persistence alternatives Persisting in RDBMS you have to build the DAM yourself Persisting in ORDBMS support for inheritance thinner DAM. Persisting in OODBMS in principle no DAM needed. Persisting in RDMBS + ORM (Object-Relation mapping) gives a virtual OODBMS 14

ORDB(MS) It's a classical RDB extended with some OO concepts User Defined Type class REF allow object navigation, e. g. x. attr 1. attr SET (of REFs) allows a more direct representation of one-to-many relation. Inheritance SQL 1999 comes with all these extensions. Many vendors of traditional RDBMS already support SQL 99 (DB 2, Oracle, Postgre. SQL, Microsoft) 15

ORDB, example with SQL 99 CREATE TYPE Person AS OBJECT ( Name CHAR(20) Age INT ) CREATE TYPE Symptom AS OBJECT ( Code CHAR(10) Name CHAR(20) ) CREATE TYPE Patient UNDER Person ( ID CHAR(12) Insurance. . . Suffers SET (REF Symptom) ) 16

ORDB, example with SQL 99 CREATE TABLE Persons OF TYPE Person CREATE TABLE Symptoms OF TYPE Symptom CREATE TABLE Patients OF TYPE Patient INSERT INTO Persons VALUES (Person("Octo", 50)). . . INSERT INTO Patients VALUES (Patient("Spons Bob", 3, SET(1465))) SELECT p. Name FROM Persons p 17

So, mapping OO-PD to ORDBMS. . . In Dennis et al: Rule 1. . 9 b p 340 Assuming ORDBMS that does not support inheritance Read it yourself I'll give you a simplified set of 'rules' We'll assume an ORDBMS that supports (single) inheritance After all, SQL 99 already includes single inheritance 18

Simplified mapping rules Map your class diagram to ER diagram (as before). But no need to factor-out inheritance. This is already supported in our ORDB. Map an entity to a table over a User Defined Type (UDT) UDT can express inheritance. You have more options when implementing a relation via REF / OID via SET of REFs 19

Converting to ER diagram Person -------------------Name : String Age : int Patient ----------ID Insurance Patient ---------ID : String Insurance : String 0. . * Suffers 0. . * 20 Symptom ----------Code Name suffers 0. . * Symptom --------Code Name

From ER diagram to ORDB tables Person ----------Name : String Age : int CREATE TYPE Person AS OBJECT ( Name CHAR(20) Age INT ) CREATE TYPE Symptom AS OBJECT ( Code CHAR(10) Name CHAR(20) ) Patient ----------ID Insurance CREATE TYPE Patient UNDER Person ( ID CHAR(12) Insurance. . . Suffers SET (REF Symptom) ) 0. . * Suffers 0. . * 21 Symptom ----------Code Name But keep in mind you still have to build your DAM !!

OODB Db 4 o for Java and. Net Available in GPL and as commercial Stable, and is just. 5 MB ! Object. Container db = Db 4 o. open. File(Util. 15 DB 4 OFILENAME) try {. . . // do something with db 4 o } finally { db. close(); } From db 4 o " Formula One Tutorial" 22

Examples of using db 4 o Person p = new Person("Octo", 50) Person q = new Patient("Spons Bob", 3) Person r = new Patient("Patrick", 5) r. suffers. add(Panic. Disorder) db. set(p) ; db. set(q) ; db. set(r) Object. Set result 1 =db. get(Patient. class) Object. Set result 2 =db. get(new Person(null, 5)) List result 3 = db. query( new Predicate() { public boolean match(Person p) { return p. age >= 5 ; } } 23 )

So. . . Notice that when using an OODB we don't need a DAM; we just call: db. set(object 1) object 2 = db. get(prototype) ; 24

Hibernate Is an ORM (Object-Relation mapping) solution : Allow you to use ordinary RDB to persist Automate the back and forth transformation (from objects to tables) No DAM is needed either give you the illusion of using an OODB. But you have to tell Hibernate how your classes are mapped to different tables. Very popular choice at the moment. 25

Describe the mapping to tables . . . . . . < key column="patient. Id"> 26

Then you can directly save and load Session session = Hibernate. Util. get. Session. Factory(). get. Current. Session() session. begin. Transaction() Patient p =. . . session. save(p) Patient q = (Patient) session. load(Patient. class, 101) List result = session. create. Query("from Patient as r where r. insurance != null"). list() session. get. Transaction(). commit() 27

If MI / SI mismatch is an issue. . . Problem: my application uses a MI OO language, buy my persistence technology only supports SI. Map MI to SI : By representing inheritance as association. Or by flattening Your DAM will have to implement the back and forth mapping between MI PD and SI OODB. But perhaps we should avoid this kind of mismatch, unless we can find a tool that can do the mapping automatically. 28

Example of approach 1 Chat --------Nickname Person ------Name Age Chat. ODB --------Nickname 1. . 1 Person. ODB --------Name Age sub. Obj. Of XOR Chat. Bot Patient -------ID Insurance Automated. Prg 1. . 1 Chat. Bot. ODB Patient. ODB -------ID Insurance Automated. Prg. ODB PD classes 29 sub. Obj. Of 1. . 1 OODB classes

Example of approach 2 Chat --------Nickname Chat. Bot Patient -------ID Insurance Automated. Prg Chat. Bot. ODB ----------Nickname Patient. ODB -------ID Insurance Nickname Automated. Prg. ODB PD classes 30 Person. ODB --------Name Age Person ------Name Age OODB classes

Optimizing your RDB duplicated information 31 wasted space

Normalization Decomposing your initial DB schemes into a new set of schemes: optimal e. g. no unnecessary duplication and no wasted space we can still reconstruct the original schemes You may want to recall the DB course. Here we'll discuss 1 st Normal Form (1 NF) 2 NF 3 NF 32

1 NF A table T is in 1 NF if : No attribute a 2 in T that actually is a duplication of attribute a 1. T has no empty cell. Table Order. Nr Cust. ID Name State Tax Prd. Nr 1 Desc 1 239 1035 Black MD 0. 05 555 Tray 241 1123 William CA 0. 08 444 Wine 290 1123 William CA 0. 08 555 Tray 237 2242 Berry DC 0. 06 111 Guide 234 2242 Berry DC 0. 06 555 Tray This table is not 1 NF 33 Prod. Nr 2 Desc 2 444 Wine

Org. Table Order. Nr Cust. ID Name State Tax Prd. Nr 1 Desc 1 239 1035 Black MD 0. 05 555 Tray 241 1123 William CA 0. 08 444 Wine 290 1123 William CA 0. 08 555 Tray 237 2242 Berry DC 0. 06 111 Guide 234 2242 Berry DC 0. 06 555 Tray New Table Order Prod. Nr 2 Desc 2 444 Wine Table Product. Order. Nr Cust. ID Name State Tax Order. Nr Prd. Nr Desc 239 1035 Black MD 0. 05 239 555 Tray 241 1123 William CA 0. 08 241 444 Wine 290 1123 William CA 0. 08 290 555 Tray 237 2242 Berry DC 0. 06 237 111 Guide 234 2242 Berry DC 0. 06 237 444 Wine 234 555 Tray 34

2 NF A table T is in 2 NF if it is in 1 NF no nonkey attribute in T depends on only a part of T's primary key. Old Table Product. Order New Product. Order Table Product Order. Nr Desc Order. Nr Prd. Nr Desc 239 555 Tray 241 444 Wine 290 555 Tray 290 555 111 Guide 237 111 237 444 Wine 237 444 234 35 Prd. Nr 555 Tray 234 555 Prd. Nr Desc

3 NF transformation Old Table Order A table T is in 3 NF if Order. Nr Cust. ID Name State Tax 239 1035 Black MD 0. 05 241 1123 William CA 0. 08 290 1123 William CA 0. 08 237 2242 Berry DC 0. 06 234 2242 Berry DC 0. 06 it is in 2 NF every attribute in T directly depends on T's primary key. Table Order. Nr 239 1035 241 1123 290 1123 237 36 Cust. ID 2242 234 2242 Table Customer Cust. ID Name State Tax 1035 Black MD 0. 05 1123 William CA 0. 08 2242 Berry 0. 06 DC

Transformation Note that we do not ideally normalize/transform tables. We ideally apply normalization at the design phase on our DB schemes, which form the structural design of an RDB. Product. Order : (Ord. Nr, Prd. Nr, Desc) (Ord. Nr, Prd. Nr) is primary key Prd. Nr Desc Product. Order : (Ord. Nr, Prd. Nr) 37 Product : (Prd. Nr, Desc)

3 NF Table Customer Cust. ID State 1035 Old table Customer Name Black MD Cust. ID Name State Tax 1123 William CA 1035 Black MD 0. 05 2242 Berry DC 1123 William CA 0. 08 5555 Spons DC 2242 Berry DC 0. 06 5555 Spons DC 0. 06 table State 38 Tax MD 0. 05 CA State Tax State 0. 08 DC 0. 06

So. . . After 3 NF we optimize space usage : No wasted empty cells No unnecessarily duplicated information But now information is scattered over multiple tables We rely on joins E. g. to know which customers order wine we have to query on: Customer join Order join Product will cost time! 39

Optimizing data access speed Clustering see book Indexing below Denormalization Customer-State Index State Pointer Table Customer Name State MD 1035 Black MD MD 1123 William CA CA 2242 Berry DC DC 5555 Spons DC DC 40 Cust. ID 6666 Octo MD

Denormalization putting back some redundancy to make certain things faster. Remember we did this 2 NF normalization: Old Table Product. Order New Product. Order Table Product Order. Nr Prd. Nr Desc 239 555 Tray 241 444 Wine 290 555 Tray 290 555 111 Guide 237 111 237 444 Wine 237 444 234 555 Tray 234 555 denormalize 41

Estimating storage size Scheme of Product. Order Ord. Nr : INT(10) Prd. Nr : INT(10) Desc : VARCHAR(160) Average row size 100 bytes #row now 106 growth: 100 new orders per day This is a crude estimation. 42 Size now 100 MB After 3 years 110 MB