DBMAN 7 Database Access Layers The ORM Pattern

DBMAN 7 Database Access Layers The ORM Pattern Inheritance Modeling in Relational Databases V 1. 0 Szabo. Zs 1

DBMAN 7 Database Access Layers The ORM Pattern Inheritance Modeling in Relational Databases V 1. 0 Szabo. Zs 2

PHYSICAL layers Concurrency and recovery control exists here Query optimization and -execution Relational operators Db, Table, Permission Relational calculus [[LATER]] RAID Buffering Storage Management OS V 1. 0 Concurrency and recovery control DOES NOT EXIST FILE Szabo. Zs 3

LOGICAL layers / Logical model View 1 View 2 View 3 Conceptual model Implementation model Physical model V 1. 0 Szabo. Zs 4

LOGICAL layers • Physical model: DBMS implemented on the computer/executed by the OS (files, programs) • Implementation/representation model: a model understandable for the DBMS (types, constraints, fields: the actual DDL) • Conceptual model: The entities/tables we have in the DB • Views: the actual View/SELECT data that the user wants to see V 1. 0 Szabo. Zs 5

Example: university DB • Physical model: files containing unsorted data • Implementation model (= DDL) – Create table subject ( subid varchar(10) not null primary key, sname varchar (50) not null, credit int not null ) • Conceptual model (= tables) – Student (sid: string, name: string, age: integer, cumulative average: real) – Subject (subid: string, sname: string, credit: integer) – Registration (sid: string, subid: string, mark: integer) • View: Teachers can see info about their courses (= DQL ) V 1. 0 Szabo. Zs 6

EXECUTION model 1. 2. 3. 4. 5. 6. 7. 8. 9. V 1. 0 User ”asks” of the DBMS (SQL query) DBMS checks the permission in the schema DBMS checks the permission in the subschema DBMS asks the OS to execute the I/O operation OS looks for the asked record OS imports the record into the system buffer OS notifies the DBMS Record is taken the user workspace DBMS notifies the user about the recieved data Szabo. Zs 7

APPLICATIONlayers V 1. 0 Szabo. Zs 8

Db. Connection vs Data. Set vs Entity Framework • Db. Connection – SQL-based access, SQL statements in strings – Results as object arrays / type conversions with methods – Fast, but VERY uncomfortable to use – This lesson (not necessarily C#-centered) • Data. Set – Usage of a strongly-typed OOP layer on top of the SQL layer – GUI-centered, unique approach reinvent the wheel, make it square, and make it work only in MS products… • Entity Framework – Use the ORM (Object Relational Mapping) principle – Generic approach with well-built design patterns – Tables are treated as in-memory collection of instances – Programming 3 Szabo. Zs V 1. 0 9

ADO. NET: Db. Connection/Db. Reader • Old-fashioned data access (connected mode) • Advantage: fast, simple SQL statements • Cons: hard to modify and to change technology/storage mode, must handle if the connection is lost, hard to manage the types • Different implementations for different database servers • Common base classes for the common operations – Connection : Db. Connection – SQL/RPC execution: Db. Command – Read SQL results : Db. Data. Reader • Descendant classes for the different database servers: – Sql. Connection (MSSQL System. Data. Sql. Client), My. Sql. Connection (My. SQL My. Sql. Data. My. Sql. Client), Npgsql. Connection (Postgre. SQL - Npgsql), Oracle. Connection (Oracle System. Data. Oracle. Client / ODAC & ODP) V 1. 0 Szabo. Zs 10

1. Initialization string conn. Str = @"Data Source=. SQLEXPRESS; Initial Catalog=nikdb; User ID=nik; Password=kin" ; Sql. Connection conn; private void button 15_Click( object sender, Event. Args e) { conn = new Sql. Connection (conn. Str); conn. Open(); Message. Box. Show( "CONNECTED" ); } V 1. 0 Szabo. Zs 11

1. Initialization(My. SQL, Postgre) case server_type. mysql: FConn. Str = "server = " + FServer + "; n" + "database = " + FDb + "; n" + "user id = " + FUser + "; n" + "password = " + FPass + "; n" ; FDatabase_Connection = new My. Sql. Connection (FConn. Str); break ; case server_type. postgre: FConn. Str = "server = " + FServer + "; n" + "database = " + FDb + "; n" + "user id = " + FUser + "; n" + "password = " + FPass + "; n" ; FDatabase_Connection = new Npgsql. Connection (FConn. Str); break ; V 1. 0 Szabo. Zs 12

1. Initialization(Oracle) case server_type. oracle: FConn. Str = "data source = " + FServer + "; n" + "user id = " + FUser + "; n" + "password = " + FPass + "; n" ; FDatabase_Connection = new Oracle. Connection (FConn. Str); break ; V 1. 0 Szabo. Zs 13

2. INSERT private void button 18_Click( object sender, Event. Args e) { Sql. Command cmd = new Sql. Command ("insert into EMP (ENAME, MGR, DEPTNO, EMPNO) values (' BILL', NULL, 20, 1000)" , conn); int affected=cmd. Execute. Non. Query(); Console. Write. Line(affected. To. String()); } V 1. 0 Szabo. Zs 14

3. UPDATE private void button 18_Click( object sender, Event. Args e) { Sql. Command cmd = new Sql. Command ("update EMP set ENAME='J OE' where EMPNO=1000" , conn); int affected=cmd. Execute. Non. Query(); Console. Write. Line(affected. To. String()); } V 1. 0 Szabo. Zs 15

4. DELETE private void button 18_Click( object sender, Event. Args e) { Sql. Command cmd = new Sql. Command ("delete from EMP where empno=1000" , conn); int affected=cmd. Execute. Non. Query(); Console. Write. Line(affected. To. String()); } V 1. 0 Szabo. Zs 16

5. SELECT private void Select() { Sql. Command cmd = new Sql. Command ("select * from EMP where SAL>=3000 order by ENAME" , conn); Sql. Data. Reader reader = cmd. Execute. Reader(); while (reader. Read()) { Console. Write. Line(reader[ "ENAME" ]. To. String()); } reader. Close(); } V 1. 0 Szabo. Zs 17

5. SELECT for (int i = 0; i < reader. Field. Count; i++) { string coltext = reader. Get. Name(i). To. Lower(); Console. Write. Line(coltext); } for (int i = 0; i < reader. Field. Count; i++) { Console. Write. Line(reader[i]. To. String()); Console. Write. Line(reader. Get. Value(i)); Console. Write. Line(reader. Get. Decimal(i )); } V 1. 0 Szabo. Zs 18

Problems • SQL Injection: We must be extra cautious that the user input MUST NEVER be interpreted as an SQL command string u. Name = "root", u. Pass = "adminpass "; string sql = $"SELECT * FROM users WHERE username ='{u. Name} ' AND userpass=sha 2(' {u. Pass }'); • u. Pass = "x') OR 1=1 OR 1<>sha 2('x" ; • The business logic will contain the SQL code, that has to be changed when changing the server / dialect / data structure • The business logic should NEVER depend on the data storage • Solution: – Prepared statements (SQL command parameters, today) – Use a strongly typed, strongly OOP-based layer with same (or similar) functionality on top of the physical SQL layer (in Programming 3) Szabo. Zs V 1. 0 19

Prepared statements V 1. 0 Szabo. Zs 20

Prepared statements V 1. 0 Szabo. Zs 21

DBMAN 7 Database Access Layers The ORM Pattern (detailed and used in: prog 3) Inheritance Modeling in Relational Databases V 1. 0 Szabo. Zs 22

ORM • • V 1. 0 C#: Entity Framework, Java: Hibernate/JPA Python: Django ORM, SQLAlchemy Ruby on Rails PHP: Eloquent, Propel, Doctrine Szabo. Zs 23

Example for the SQL-free approach V 1. 0 Szabo. Zs 24

Example for the SQL-free approach V 1. 0 Szabo. Zs 25

Java 8 + Hibernate + Stream API V 1. 0 Szabo. Zs 26

ORM layers • Raw data access layer to execute true SQL commands (commands, parameters, results Db. Command, …) – Dialect-independent conversion: execute operations regardless of the actual SQL dialect underneath • Object persistence: query results translated into objects and collections, can be persisted across operations – Table Class mapping – SQL-free application: write data operations in a nonsql way, either in lambda expressions (c#/java) or using a new query language (doctrine) • The actual ORM: – The impression of working with an in-memory data V 1. 0 Szabo. Zs 27

Active Record / Data Mapper V 1. 0 Szabo. Zs 28

Active Record / Data Mapper • Active Record: – – Simple, easy to learn Better suited for CRUD, as CRUD is built into the data objects Coupled DB Performance bottlenecks • Data Mapper – Harder to configure/learn – CRUD is executed via a repository/context class – Flexibility (class / table isn’t necessarily 1: 1 possibility to use more inheritance models!) – Better suited for DDD / SOLID – Can be faster in SOME cases (good configuration!) – A lot better to use with unit tests V 1. 0 Szabo. Zs 29

DBMAN 7 Database Access Layers The ORM Pattern Inheritance Modeling in Relational Databases V 1. 0 Szabo. Zs 30

OOP • OOP = Classes Subclasses Objects/Instances • There can be various relations between classes/objects • Association/Dependency: “accidental” connection (e. g. used as a parameter) • Aggregation: “strong association”, separate lifetimes • Composition: “strong aggregation”, connected lifetimes • Inheritance: specialization • Instance: create an instance from a class • Question: how to map these OOP relations in tables? What are the different ways the Data Mapper (Table Class mapping) can work? V 1. 0 Szabo. Zs 31

RDBMS common relations • No need to know the latin names: in an ER, we usually see the English version of the relationship names • Holonymy (HAS-A) and Meronymy (PART-OF) tree <HAS-A/CONTAINS> branch finger <PART-OF> hand • Hyponymy/Hypernymy (IS-A/TYPE-OF): Hyponym <IS-A> hypernym Subtype, subclass <IS-A> Supertype, superclass Car. Class <IS-A> Vehicle. Class • INSTANCE-OF: BMW 116 i <IS-AN-INSTANCE-OF> Car. Class V 1. 0 Szabo. Zs 32

HAS-A / PART-OF • In the OOP world: a simple data field/parameter with the type of “Something” or “List<Something>” • Same implementation can be used for all the different forms of Dependency/Association/Aggregation/ Composition, different meanings (Detailed in: prog 4) • In an RDBMS, These relationships are described using simple FK connections • In an ER diagram – Alternative names: x CONTAINS y / x BELONGS TO y – 1: N or M: N depends on the actual tables and the relationship type V 1. 0 Szabo. Zs 33

INSTANCE-OF / IS-A • INSTANCE-OF: instantiation OOP: Car bmw 116 i = new Car(xxxxx); ER: specific. Car <INSTANCE-OF> car – Usually we have a type-table, an instance-table, and then 1: N is good for most of the languages – N: M if multiple base types or all the implemented interfaces must be stored as well • IS-A: inheritance OOP: class car : vehicle { } class vehicle : game. Object { } ER: car <IS-A> vehicle <IS-A> game. Object – In DB: not so trivial… – Same data fields for base and descendant classes, but the tables cannot be inherited… V 1. 0 Szabo. Zs 34

OOP mapping • to map (v): The act of determining how objects and their relationships are persisted in permanent data storage, in this case relational databases. • mapping (n): The definition of how an object’s property or a relationship is persisted in permanent storage. • http: //www. agiledata. org/essays/mapping. Objects. html • http: //www. ibm. com/developerworks/library/wsmapping-to-rdb/ • https: //martinfowler. com /eaa. Catalog/ • https: //docs. microsoft. com/enus/ef/core/modeling/inheritance V 1. 0 Szabo. Zs 35

1. Single Table Inheritance / Table per Hierarchy (TPH) V 1. 0 Szabo. Zs 36

1. Single Table Inheritance - Advantages • Simple approach. • Easy to add new classes, you just need to add new columns for the additional data. • Supports polymorphism by simply changing the type of the row. • Data access is fast because the data is in one table. • Ad-hoc reporting is very easy because all of the data is found in one table. V 1. 0 Szabo. Zs 37

1. Single Table Inheritance - Disadvantages • Coupling within the class hierarchy is increased because all classes are mapped to the same table • A change in one class will affect the table which can then affect the other classes in the hierarchy. • Space potentially wasted • Indicating the type becomes complex when significant overlap between types exists. • Table can grow quickly for large hierarchies. ideal for simple and/or shallow class hierarchies where there is little or no overlap between the types within the hierarchy. V 1. 0 Szabo. Zs 38

2. Concrete Table Inheritance / Table per Concrete Class (TPC) V 1. 0 Szabo. Zs 39

2. Concrete Table Inheritance - Advantages • Easy to do ad-hoc reporting as all the data you need about a single class is stored in only one table. • Good performance to access a single object’s data. V 1. 0 Szabo. Zs 40

2. Concrete Table Inheritance - Disadvantages • When you modify a class you need to modify its table and the table of any of its subclasses. • Whenever an object changes its role, you need to copy the data into the appropriate table and assign it a new ID value (or perhaps you could reuse the existing ID value – GUID? ? ? ). • It is difficult to support multiple roles and still maintain data integrity. For example, where would you store the name of someone who plays two sports? • When changing types and/or overlap between types is rare. V 1. 0 Szabo. Zs 41

3. Class Table Inheritance / Table per Type (TPT) V 1. 0 Szabo. Zs 42

3. Class Table Inheritance– Advantages • Easy to understand because of the one-to-one mapping. • Supports polymorphism very well as you have records in the appropriate tables for each type. • Very easy to modify superclasses and add new subclasses as you merely need to modify/add one table. • Data size grows in direct proportion to growth in the number of objects. V 1. 0 Szabo. Zs 43

3. Class Table Inheritance– Disadvantages • There are many tables in the database, one for every class (plus tables to maintain relationships). • Potentially takes longer to read and write data using this technique because you need to access multiple tables. (can be improved using striped RAID!) • Ad-hoc reporting on your database is difficult, unless you add views to simulate the desired concrete tables. • When there is significant overlap between types or when changing types is common. V 1. 0 Szabo. Zs 44

Comparison Factors to Consider Single Table (TPH) Concrete Table (TPC) Class Table (TPT) Ad hoc reporting Simple Medium / Difficult Ease of implementation Simple Medium Difficult Ease of data access Simple Medium / Simple Very high High Low Speed of data access Fast Medium / Fast Support for polymorphism V 1. 0 Medium Low High Coupling Szabo. Zs 45

4. Generic Table Structure V 1. 0 Szabo. Zs 46

4. Generic Table Structure – Advantages • Only structure to support multiple inheritance (e. g. C++) • Works very well when database access is encapsulated by a robust persistence framework. • It can be extended to provide metadata to support a wide range of other mapping strategies , including relationship and instance mappings • It is incredibly flexible, enabling you to quickly change the structure of the objects V 1. 0 Szabo. Zs 47

4. Generic Table Structure – Disadvantages • Very advanced technique that can be difficult to implement at first. • It only works for small amounts of data because you need to access many database rows to build a single object. • You will likely want to build a small administration application to maintain the meta data. • Reporting against this data can be very difficult due to the need to access several rows to obtain the data for a single object. • For complex applications that work with small amounts of data, or where you data access isn’t very common or you can pre-load data into caches. V 1. 0 Szabo. Zs 48

V 1. 0 Szabo. Zs 49

Szabo. Zs 50