Tuesday 5 July 2011

Role-based Data Models

It does appear that Entity Frameworks loom ever larger in the data access world, whether they are used in a code-first or model-first way. But any database can support several data models.
It has been a common occurrence that Java programmers will use different data models (as defined by annotations in their program) to access the same database. This is because annotations can provide different entity and property names and map their relationships in different ways. Pyrrho (up to version 4.4) therefore regarded the data model in use as a thread-specific structure. This now seems quite the wrong thing to do.
Instead, Pyrrho now takes its cue from the business analytics and business modelling field, and considers that it will be best practice for each role to have a different view of the data in the database, and it is at such a conceptual model level that entity modelling should take place.
So, from version 4.5 of Pyrrho, a data model will be regarded as a property of a Role. The physical database defines a default data model, and an associated default Role. This default Role treats each base table with a primary key as an entity, and each column of such a table as a property, and allows navigation using foreign keys. Multi-column keys are handled as lists whose property name is the reserved word KEY.
[Updated 2 August: Users with admin access to a role can create new database objects for their role. Granting access to database objects for a Role copies the necessary entitiy and property definitions into the Role's data model. The data model that is applied is determined by the current Role: this is initially set in the connection but can be changed during the session to any other role that the user has been granted.]
The data model can be modified for a Role by hiding entity types or properties, or defining entity types and properties by renaming inherited entities and properties tables or views and their columns. Descriptive text can be associated with the entity and property definitions. An additional syntax for ALTER ROLE has been created for providing this facility.
This allows data models to have more user-friendly names and allows Roles to be localised for different languages. Pyrrho 4.5 enhances generated columns by introducing updatable generated columns in addition to updatable views so additional derived columns can be added to the database schema where required for making data models more natural. It is a feature of Pyrrho that such derived columns are only computed when required, so there is little overhead in using them.
[Added 2 August: The SQL contained within e.g. stored procedures executes under definer's rights. Although there is only one copy of the SQL in the database, a developer viewing the SQL will see a version adapted to their current data model. The SQL will execute with definer's rights, although the code will reflect the user's data model. The resulting dependencies between roles and their data models are tracked by Pyrrho, and the usual restrict/cascade protocols apply to changes to the data model schema.]
The data model can be retrieved from the database is the usual way in EDMX format, and will determine the URL mapping used in Pyrrho's new secure REST service. SQL will of course continue to be applied at the level of base tables. Pyrrho's implementation of LINQ and Java Persistence will also use the new role-based data models: for these APIs the change will only be noticed if a Role is specified in the connection string.
In Pyrrho, strong types, including types with embedded metadata, will continue to be supported in the physical database. The new derived column machinery can present the same physical data as different types as appropriate for role-based data models.
Over the summer I hope to publish more articles on the version 4.5 goodies, including some better support for role base security.

No comments:

Post a Comment