Transcript Generic Database Mapping in Clean

Between Types and Tables
Generic Mapping Between Relational
Databases and Data Structures in Clean
Master’s Thesis Presentation
Bas Lijnse
July 11, 2008
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
July 11, 2008
2
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
July 11, 2008
3
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…
July 11, 2008
4
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
July 11, 2008
5
What takes time?
• Data entry
– Adding new information
– Keeping existing information up-to-date
• Information extraction
– Custom reporting
– Specific views
July 11, 2008
6
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
July 11, 2008
7
Example: Update an employee
1. Read all data related to that employee
from the database into a data structure
2. Present that information in a form
3. Map user events to that data structure
4. Propagate changes in the data
structure to the database
July 11, 2008
8
Database interaction
• Provide basic CRUD operations for all
conceptual entities
– Create, read, update and delete
• Similar patterns for different entities
• Boring and error prone
July 11, 2008
9
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
July 11, 2008
10
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
July 11, 2008
11
Idea!
• Can we implement these CRUD
operations for all entities at once using
generic programming?
July 11, 2008
12
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
July 11, 2008
13
Idea!
• Can we implement these CRUD
operations for all entities at once using
generic programming?
• If the relation between data in the
database and the data types in Clean
can be inferred from the types we can!
July 11, 2008
14
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
July 11, 2008
15
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
July 11, 2008
16
Example ORM
July 11, 2008
17
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
July 11, 2008
18
Recap
• Generic programming
– Abstract over types
– Reduce repetitive work
– May be used for the
database operations
– But needs an explicit
relation
July 11, 2008
• Object Role Models
– Conceptual
specification
– Formal definition of
entities
– Automatic derivation of
databases
19
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?
July 11, 2008
20
Rephrased visually…
Specification
Conceptual model
Database
Relational model
DB
July 11, 2008
Clean program
CM types
Types
CM values
Values
21
The generic mapping
July 11, 2008
22
Project overview
Conceptual model
Goals
1
4
Relational model
DB
July 11, 2008
2
3
CM types
Types
CM values
Values
23
Project overview
1. Map types in the ORM model to types
in Clean
2. Map these types to a relational model
3. Map instances of the types to
instances of the model
4. Map the relational model types to
types in Clean
July 11, 2008
24
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
July 11, 2008
25
Example types
  { project_projectNr
, project_description
:: Int
:: String
, project_parent
:: Maybe ProjectID
, task_ofwhich_project
:: [Task]
, project_ofwhich_parent
:: [ProjectID]
, projectworkers_employee_ofwhich_project
:: [EmployeeID]
}
:: ProjectID = { project_projectNr
:: Int
}
July 11, 2008
26
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
July 11, 2008
27
Example tables
project
projectNr
INT
NOT NULL, PRI
description
VARCHAR
NOT NULL
parent
INT
NULL
name
VARCHAR
NOT NULL, PRI
description
VARCHAR
NOT NULL
project
INT
NOT NULL, PRI
employee
VARCHAR
NOT NULL, PRI
employee

July 11, 2008
28
3. Generic mapping
• Provides the four CRUD operations
• Based on a parser/printer analogy
• Implemented in a prototype library
July 11, 2008
29
The library offers:
• Wrapper functions for the CRUD
operations
_  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
• A way to automatically derive the
gSQL{|*|} function
July 11, 2008
30
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
July 11, 2008
31
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
July 11, 2008
32
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
July 11, 2008
as deriving types from an ORM model
33
Demo
July 11, 2008
34
Demo implementation
•
•
•
•
CGI Web application
MySQL database
“Plain” Clean GUI
Generic database
mapping
July 11, 2008
35
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)
July 11, 2008
36
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
July 11, 2008
37
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
• 56 lines
• 7 handwritten SQL statements
• Only useful for the Project type
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]
July 11, 2008
38
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
July 11, 2008
39
Conclusions
• Thank you for listening
• Download my thesis at:
–
http://www.baslijnse.nl/projects/between-types-and-tables/
July 11, 2008
40
July 11, 2008
41