Between types and tables generic mapping between relational databases and data structures in clean
Download
1 / 41

Between Types and Tables Generic Mapping Between Relational Databases and Data Structures in Clean - PowerPoint PPT Presentation


  • 106 Views
  • Uploaded on

Between Types and Tables Generic Mapping Between Relational Databases and Data Structures in Clean. Master’s Thesis Presentation Bas Lijnse. What I will talk about. The results of My Master’s thesis project

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Between Types and Tables Generic Mapping Between Relational Databases and Data Structures in Clean' - judd


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Between types and tables generic mapping between relational databases and data structures in clean

Between Types and TablesGeneric Mapping Between Relational Databases and Data Structures in Clean

Master’s Thesis Presentation

Bas Lijnse


What i will talk about
What I will talk about

  • The results of My Master’s thesis project

    • The use of generic programming for automated mapping between relational databases and data structures

  • Outline

    • Building information systems

    • Generic mapping

    • Demo: A project management system


Information systems
Information Systems

  • Everywhere in today’s businesses

    • E.g. Inventory, CRM, Project management

  • Are very similar at a high level

    • Data storage, entry and information extraction

  • Are very different due to differences in the application domains


Is development
IS Development

  • Is a lot of work!

  • But is a standardized process

  • Consists of two types of activities:

    • Specification and design

      • Requirements analysis, modeling, interface design etc…

    • Software construction

      • Database design, programming, testing etc…


Can we reduce effort
Can we reduce effort?

  • Abstract over repetitive patterns

    • Functions

    • Overloaded functions

    • Generic functions

  • Reuse specification effort

    • Derive of parts of the executable system

      • Database, scaffolding code, test generation


What takes time
What takes time?

  • Data entry

    • Adding new information

    • Keeping existing information up-to-date

  • Information extraction

    • Custom reporting

    • Specific views


Data entry
Data entry

  • Interaction with the database

    • Read information to display

    • Update changes in the database

  • Interaction with the user

    • Present information in views or forms

    • Handle user events


Example update an employee
Example: Update an employee

  • Read all data related to that employee from the database into a data structure

  • Present that information in a form

  • Map user events to that data structure

  • Propagate changes in the data structure to the database


Database interaction
Database interaction

  • Provide basic CRUD operations for all conceptual entities

    • Create, read, update and delete

  • Similar patterns for different entities

  • Boring and error prone


Database interaction1
Database interaction

  • For every entity we need write 4 functions

    • readEntity :: EntityID db -> (Entity, db)

    • createEntity :: Entity db -> (EntityID, db)

    • updateEntity :: Entity db -> db

    • deleteEntity :: EntityID db -> db


What we would like
What we would like

  • Write 4 functions for every entity

    • read :: id db -> entity db

    • create :: entity db -> id db

    • update :: entity db -> db

    • delete :: id -> db


Idea!

  • Can we implement these CRUD operations for all entities at once using generic programming?


Generic programming
Generic programming

  • Heavily overloaded term

    • Used for all kinds of programming techniques

  • Clean has data type generic programming

    • Specification of functions on a generic domain

    • This domain contains information about the types

    • Automatic conversion of any type to and from this generic domain

    • Hence, functions that work for any type

  • Useful for similar operations on different types


Idea!

  • Can we implement these CRUD operations for all entities at once using generic programming?

  • Ifthe relation between data in the database and the data types in Clean can be inferred from the types we can!


Explicit relations
Explicit relations

  • We need an explicit relation between entities in the database and entities as data structures

  • Therefore, we need a formal specification of entities

  • Object Role Modeling (ORM) provides this specification


Object role models orm
Object Role Models (ORM)

  • Conceptual Modeling Language

  • Expression of facts about objects

    • Objects play roles in facts

    • Objects can be values or entities

  • Models can be defined graphically

  • And have formal meaning

  • Can be used to automatically derive databases



Interesting aspects
Interesting aspects

  • It is simple!

  • It uses unary and binary facts

  • There are binary facts with one entity type

    • Parent-child relationship on projects

  • Various uniqueness constraints

    • One-to-many

    • Many-to-many


Recap

Generic programming

Abstract over types

Reduce repetitive work

May be used for the database operations

But needs an explicit relation

Object Role Models

Conceptual specification

Formal definition of entities

Automatic derivation of databases

Recap


Hence the question
Hence the question…

  • How can we derive a database and a set of representation/view types from an ORM model such that generic programming can be used to automatically map between them?


Rephrased visually
Rephrased visually…

Conceptual model

Specification

Database

Clean program

Relational model

CM types

Types

CM values

DB

Values



Project overview

1

4

2

3

Project overview

Conceptual model

Goals

Relational model

CM types

Types

CM values

DB

Values


Project overview1
Project overview

  • Map types in the ORM model to types in Clean

  • Map these types to a relational model

  • Map instances of the types to instances of the model

  • Map the relational model types to types in Clean


1 from orm to types
1. From ORM to types

  • Similar to deriving databases from ORM

  • Structured manual process

  • Basic steps:

    • For each ORM entity type an entity record and an identification record

    • Collect all relations of an entity in the entity records

    • Choose to nest structures, or reference

    • Structured field names provide mapping information


Example types
Example types

  { project_projectNr :: Int

, project_description :: String

, project_parent :: Maybe ProjectID

, task_ofwhich_project :: [Task]

, project_ofwhich_parent :: [ProjectID]

, projectworkers_employee_ofwhich_project :: [EmployeeID]

}

:: ProjectID = { project_projectNr :: Int

}


2 from types to tables
2. From types to tables

  • Systematically collect all relation information from:

    • Field names

    • Field types

    • Record names (ID suffix)

  • Construct a relational model

    • Tables (relations)

    • Integrity constraints

  • Can be fully automated


Example tables
Example tables

project

employee




3 generic mapping
3. Generic mapping

  • Provides the four CRUD operations

  • Based on a parser/printer analogy

  • Implemented in a prototype library


The library offers
The library offers:

  • Wrapper functions for the CRUD operations

  • A way to automatically derive the gSQL{|*|} function

_  a *cur -> (Maybe b, *cur) | gSQL{|*|} a & gSQL{|*|} b & SQLCursor cur

gsql_create :: b *cur -> (Maybe a, *cur) | gSQL{|*|} a & gSQL{|*|} b & SQLCursor cur

gsql_update :: b *cur -> (Maybe a, *cur) | gSQL{|*|} a & gSQL{|*|} b & SQLCursor cur

gsql_delete :: a *cur -> (Maybe b, *cur) | gSQL{|*|} a & gSQL{|*|} b & SQLCursor cur


Parser printer analogy
Parser/printer analogy

  • Concatenated database records can be viewed as a token stream

  • Reading data structures is parsing

    • From flat to nested structure

  • Creating or updating is printing

    • From nested to flat structure

  • Just-in-time reading/writing


Challenges
Challenges

  • Order of creates and deletes

    • because of integrity constraints

  • Updates of lists

    • Items can also be added or removed

  • Updating and removing relations

  • Implementation in Clean

    • All-in-one function, level of abstraction


4 from tables to types
4. From tables to types

  • Extra step

  • Types are views on existing databases

  • Only when the database could have been derived from a set of types

  • Relation between entities must be known

    • From foreign keys or background knowledge

  • Follows a similar systematic approach as deriving types from an ORM model



Demo implementation
Demo implementation

  • CGI Web application

  • MySQL database

  • “Plain” Clean GUI

  • Generic database mapping


The mapping at work
The mapping at work

editProjectPage :: !Int !HTTPRequest !*cur

-> (Maybe (String,String), !String, [HtmlTag], !*cur)

| SQLCursor cur

editProjectPage pid req cursor

| req.req_method == "POST"

# project = editProjectUpd req.arg_post

# (mbErr,mbId, cursor) = gsql_update project cursor

= (Just ("/projects/" +++ toString (0 + (fromJust mbId)),

"Successfully updated project " +++ toString pid),"",[],cursor)

| otherwise

# (mbErr, mbProject, cursor) = gsql_read pid cursor

# project = fromJust mbProject

# (projects, cursor) = getProjectOptions cursor

# (employees,cursor) = getEmployeeOptions cursor

= (Nothing, project.project_description,

[editProjectForm False project projects employees],cursor)


What we would have needed
What we would have needed

  • 4 functions for each entity we want to manipulate

    • readEntity, createEntity, updateEntity, deleteEntity

  • We have saved: (number of entities) x 4 = 8 functions


Functions like this one
Functions like this one…

updateProject :: Project !*cur -> (Maybe SQLError, *cur) | SQLCursor cur

updateProject project =: {Project | project_projectNr = pid} cursor

//Update the project record

# (mbErr,cursor) = sql_execute "UPDATE project SET description = ?, parent = ? WHERE projectNr = ?" pvalues cursor

| isJust mbErr = (mbErr, cursor)

//Update/create the linked employees

# (mbErr, ids, cursor) = linkEmployees project.projectworkers_employee_ofwhich_project cursor

| isJust mbErr = (mbErr, cursor)

//Garbage collect linked employees

# (mbErr,cursor) = sql_execute ("DELETE FROM projectworkers WHERE project = ?" +++ ematch ids) (evalues ids) cursor

| isJust mbErr = (mbErr, cursor)

//Update/add the tasks

# (mbErr,ids,cursor) = updateTasks project.task_ofwhich_project cursor | isJust mbErr = (mbErr, cursor)

//Garbage collect tasks

# (mbErr,cursor) = sql_execute ("DELETE FROM task WHERE project = ?" +++ tmatch ids) (tvalues ids) cursor

| isJust mbErr = (mbErr, cursor)

= (Nothing, cursor)

where

pvalues = [SQLVVarchar project.project_description, pparent project.project_parent, SQLVInteger project.Project.project_projectNr]

pparent Nothing = SQLVNull

pparent (Just {ProjectID| project_projectNr = x}) = SQLVInteger x

linkEmployees [] cursor = (Nothing, [], cursor)

linkEmployees [{EmployeeID | employee_name = e}:es] cursor

# (mbErr, cursor) = sql_execute "SELECT * FROM projectworkers WHERE project = ? AND employee = ?" [SQLVInteger pid, SQLVVarchar e] cursor

| isJust mbErr = (mbErr,[],cursor)

# (mbErr, num, cursor) = sql_numRows cursor

| num == 0

# (mbErr, cursor) = sql_execute "INSERT INTO projectworkers (project,employee) VALUES (?,?)” [SQLVInteger pid, SQLVVarchar e] cursor

| isJust mbErr = (mbErr,[],cursor)

# (mbErr,ids,cursor) = linkEmployees es cursor

= (mbErr,[e:ids],cursor)

| otherwise

# (mbErr,ids,cursor) = linkEmployees es cursor

= (mbErr,[e:ids],cursor)

ematch [] = ""

ematch ids = " AND NOT (employee IN (" +++ (text_join "," ["?" \\ x <- ids]) +++ "))"

evalues ids = [SQLVInteger pid: map SQLVVarchar ids]

updateTasks [] cursor = (Nothing, [], cursor)

updateTasks [{Task | task_taskNr = taskNr, task_description = description, task_done = done}:ts] cursor

| taskNr == 0

# vals = [SQLVVarchar description, SQLVInteger (if done 1 0), SQLVInteger pid]

# (mbErr, cursor) = sql_execute "INSERT INTO task (description,done,project) VALUES (?,?,?)" vals cursor

| isJust mbErr = (mbErr, [], cursor)

# (mbErr, i, cursor) = sql_insertId cursor

| isJust mbErr = (mbErr, [], cursor)

# (mbErr, ids, cursor) = updateTasks ts cursor

= (mbErr, [i:ids], cursor)

| otherwise

# vals = [SQLVVarchar description,SQLVInteger (if done 1 0),SQLVInteger pid,SQLVInteger taskNr]

# (mbErr, cursor) = sql_execute "UPDATE task SET description = ?, done = ?, project = ? WHERE taskNr = ? " vals cursor

| isJust mbErr = (mbErr, [], cursor)

# (mbErr, ids, cursor) = updateTasks ts cursor

= (mbErr, [taskNr:ids], cursor)

tmatch [] = ""

tmatch ids = " AND NOT (taskNr IN (" +++ (text_join "," ["?" \\ x <- ids]) +++ "))"

tvalues ids = map SQLVInteger [pid:ids]

  • 56 lines

  • 7 handwritten SQL statements

  • Only useful for the Project type


Conclusions
Conclusions

  • Generics can be successfully applied!

    • Saves work

    • Reduces errors

  • Two interesting areas

    • Development of new information systems

    • As views on existing databases

  • Additionally provides a way to realize sharing in a functional language


Conclusions1
Conclusions

  • Thank you for listening

  • Download my thesis at:

    • http://www.baslijnse.nl/projects/between-types-and-tables/


ad