The RCFT Functions
The DataSetNumber is a unique number that identifies each dataset, and it appears on every row within that dataset. In simpler terms, let's say tbl_person has a DataSetNumber of 343 for every record, and no other dataset recognized by the system shares the same 343.
Postgres has this cool feature called ON CONFLICT that we'll use for our website. It lets us insert or update records in one go. However, Microsoft SQL doesn't have a similar feature.
While we used UNASSIGNED multiple times for dataset classification, we're doing something different for DataPoint classification. Instead of repeating that task, we want to classify DataPoints as they should be. This will help us build data access control views.
Here's the tricky part: we have table structures to automatically set up defaults and constraints, but we need data in the system to leverage them. However, this cyclic dependency allows us to enhance existing code. So, any code we create can be abstracted and refined gradually, which is crucial in an agile environment. We can make working proof of concepts and convert them into their final form piece by piece.
To bring data into the system, I'll create 9 functions for every subject, totaling 342 functions.
Get Classifier Id Functions (1-4):
Simple functions where we provide a TenantId and a KeyName to get a Realm, Class, Family, or Type Id back. A byproduct of using KeyNames as inputs is human readable code. For example:
1
2
3typeId := fnc_Name_Get_TypeId(
4 _TenantId
5 , 'PERSON'
6 , 'LEGAL'
7 , 'PREFERRED'
8 , 'FIRST'
9);
10
11Insert-If-Not-Exists Functions (5-8):
These functions handle insert-if-not-exists processes. Instead of procedures, we're using functions (Postgres) with straightforward code like:
1
2
3INSERT INTO tbl_table (values)
4VALUES (parameters)
5ON CONFLICT DO NOTHING;
6
7Combination Function (9):
The last function brings all the above functions together. In our system, we get an Id even if we have to create one. This function takes in parameters like Tenant, RealmKeyName, ClassKeyName, FamilyKeyName, TypeKeyName. It attempts to retrieve a TypeId, and if it can't, it tries to retrieve each classifier id (Realm, Class, etc.) and creates each level until it can finally create the TypeId.
1
2
3typeid=: fnc_name_get_typeid(_tenant,_realm,_class,_family,_type);
4if typeid is null then
5
6 realm_id:= fnc_name_get_realmid(_tenant,_realm);
7 if realm_id is null then
8 perform fnc_name_realm_insert(_tenant,10,_realm,null,_realm,true,true);
9 realm_id:= fnc_name_get_realmid(_tenant,_realm);
10 end if;
11 class_id:= fnc_name_get_classid(_tenant,_realm,_class);
12 if class_id is null then
13 perform fnc_name_class_insert(_tenant,realm_id,_class,null,_class,true,true);
14 class_id:= fnc_name_get_classid(_tenant,_realm,_class);
15 end if;
16 family_id:= fnc_name_get_familyid(_tenant,_realm,_class,_family);
17 if family_id is null then
18 perform fnc_name_family_insert(_tenant,class_id,_family,null,_family,true,true);
19 family_id:= fnc_name_get_familyid(_tenant,_realm,_class,_family);
20 end if;
21 type_id:= fnc_name_get_typeid(_tenant,_realm,_class,_family,_type);
22 if type_id is null then
23 perform fnc_name_type_insert(_tenant,family_id,_type,null,_type,true,true);
24 type_id:= fnc_name_get_typeid(_tenant,_realm,_class,_family,_type);
25 end if;
26end if;
27
28There are two approaches to handle a null id issue. While it might not be the preferred design for any system, the idea is to retrieve an Id when the entire chain is provided. This enables me to focus on creating code without the concern of duplicating this code across various procedures and functions.