Between Types and Tables Generic Mapping Between Relational Databases and Data Structures in Clean

1. Between Types and TablesGeneric Mapping Between Relational Databases and Data Structures in Clean Master’s Thesis Presentation Bas Lijnse

2. 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

3. 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

4. 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…

5. 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

6. What takes time? • Data entry • Adding new information • Keeping existing information up-to-date • Information extraction • Custom reporting • Specific views

7. 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

8. 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

9. Database interaction • Provide basic CRUD operations for all conceptual entities • Create, read, update and delete • Similar patterns for different entities • Boring and error prone

10. 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

11. 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

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

13. 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

14. 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!

15. 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

16. 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

17. Example ORM

18. 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

19. 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

20. 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?

21. Rephrased visually… Conceptual model Specification Database Clean program Relational model CM types Types CM values DB Values

22. The generic mapping

23. 1 4 2 3 Project overview Conceptual model Goals Relational model CM types Types CM values DB Values

24. 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

25. 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

26. 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 }

27. 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

28. Example tables project employee 

29. 3. Generic mapping • Provides the four CRUD operations • Based on a parser/printer analogy • Implemented in a prototype library

30. 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

31. 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

32. 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

33. 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

34. Demo

35. Demo implementation • CGI Web application • MySQL database • “Plain” Clean GUI • Generic database mapping

36. 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)

37. 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

38. 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

39. 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

40. Conclusions • Thank you for listening • Download my thesis at: • http://www.baslijnse.nl/projects/between-types-and-tables/