Data Access Views

Before diving into extensive development, our first step is to construct Views. This involves leveraging the Definition domain, Reference attribute, and the DataPointType table.

Views serve a dual purpose as access control and system-specific names. Our objective is to create views seamlessly, even from a complex SELECT * from table1, table2, … setup, without encountering ambiguous column errors. To achieve this, we adopt a systematic approach to naming our datasets and columns. By combining the first four characters of a table name with the first three characters of a column name, we create a unique and coherent dataset name.

Control over SELECT, UPDATE, DELETE, and INSERT operations is crucial. This article outlines how we plan to generate over 1500 views, each with its own defined purpose, through a procedure and a set of records.

vtbl_name_select:

A Select view comes equipped with triggers preventing update, insert, and delete actions. It encompasses all DataPoints (columns) in the DataSet.

vtbl_name_insert:

An Insert view is selective, containing only specific columns. This necessitates proper definition of datapoint families, with access restricted to families such as KEY, TENANT, CLASSIFIER, DESCRIPTOR, SORT, VALUE, and STATE. Only primary and foreign keys, the tenant, any RCFT id, names and descriptions, orders, active/trusted flags, and the value columns for our ATTRIBUTEs are accessible. All other columns default to their values. No updates or deletes are allowed from this view.

vtbl_name_update:

An Update view includes all columns except for the ROWGUID. The data-driven nature of this approach allows for flexibility, and any changes in strategy can be accommodated. However, inserts or deletes are not permitted.

vtbl_name_delete:

A delete view is minimalistic, containing only the datapointtype of PRIMARYKEY and TENANT - two columns. This stringent control ensures precision in deletions, requiring specificity in identifying what and who is being deleted.

Apart from these, there are additional views yet to be explored in detail.

Creating Views Automatically

For the creation processes, understanding the system concepts is pivotal. Creating views becomes straightforward once the underlying concepts are clear. I am contemplating using 'SYSTEM', 'OBJECT CREATE,' 'VIEW,' and 'COMMON' for the Definition RCFT. The Definition base record will include SELECT, UPDATE, INSERT, DELETE. I will introduce a Reference RCFT of 'DEFINITION', 'OBJECT CREATE', 'VIEW', 'DATAPOINT TYPE'. The ReferenceRealm of DEFINITION is used to describe who owns the ReferenceType, while the DefinitionRealm of SYSTEM tells us all subsequent classifications pertain to the SYSTEM. The DefinitionReference table will link the Definition (SELECT, UPDATE, INSERT, DELETE) to their corresponding DataPointTypes.

The code execution involves obtaining definitions for views, querying datapoints for the intersection of datapointtypes and definitionreference datapoint types, and executing dynamic SQL. While the process is almost complete, our experience suggests creating three procedures or functions for efficiency: one for 'building all views for all tables', one for 'building views for this table', and one for 'building this view for this table'. This structured approach aligns with the RCFT paradigm, seamlessly integrating into our development methodology.

Code

With the functions we have created, we need two new RCFTs, definition and reference subjects.

I created a datapoint name transforming function. Helps with isolating code as much as possible. We may never need to use this function anywhere else.

After filling the definition_reference table with our relationships, we essentially just build 3 functions. Function 1: cursor with a for each on the dataset subject. Function 2: For each on the definition table where the type equals our new DEFINITION OBJECT_CREATE VIEW COMMON. Function 3: A simple aggregation and execute.

UNFORTUNATELY... We will need to rewrite all of this code to use the views. We should never query tables directly.