a2ndrade · August 5, 2016 15:44 · Aug 5, 2016 · Jul 27, 2016 · Jul 26, 2016 · Jul 25, 2016
diff --git a/db.q b/db.q
@@ -20,7 +20,7 @@ attrsSystem:{[attrEntityId]
   :flip`entityId`entityDt`entityName`attrType`attrIndex`attrFullText`entityDoc!flip
     ((0N;attrEntityId;`entityDt;"r";1b;0b;"This entity's datatype. Every object in the system gets its id from this table. Every object has a type");
     (0N;attrEntityId;`entityName;"s";1b;0b;"Unique identifier among all entities in the system. Used as a symbolic entity Id");
-    (0N;attrEntityId;`attrType;"h";1b;0b;"kdb+ storage type char; see http://code.kx.com/wiki/Reference/Datatypes");
+    (0N;attrEntityId;`attrType;"c";1b;0b;"kdb+ storage type char; see http://code.kx.com/wiki/Reference/Datatypes");
     (0N;attrEntityId;`attrIndex;"b";1b;0b;"1b if designer expects to filter or sort on this attribute; 0b if not. Defaults to 1b");
     (0N;attrEntityId;`attrFullText;"b";1b;0b;"1b if attribute value should be indexed for full-text search; 0b if not. Defaults to 1b");
     (0N;attrEntityId;`entityDoc;"C";1b;0b;"Documentation string");
@@ -49,11 +49,11 @@ dtsSystem:{[attrEntityId;dtEntityId]
   :flip`entityId`entityDt`entityName`dtNamespace`dtName`dtParent`dtFields`entityDoc!flip
     ((dtEntityId;dtEntityId;`dtDt;namespace;"Datatype";0N;`entityDt`dtNamespace`dtName`dtParent`dtList`dtComponent`dtFields;"The datatype for datatypes");
      (attrEntityId;dtEntityId;`attrDt;namespace;"Attribute";0N;`entityDt`entityName`attrType`attrIndex`attrFullText;"The attribute datatype");
-     (0N;dtEntityId;namespace;`userDt;"User";0N;enlist`userName;"The user datatype");
-     (0N;dtEntityId;namespace;`groupDt;"Group";0N;enlist`groupId;"The group datatype");
-     (0N;dtEntityId;namespace;`roleDt;"Role";0N;enlist`roleName;"The role datatype");
-     (0N;dtEntityId;namespace;`roleMapEntryDt;"RoleMapEntry";0N;`roleMapEntryRole`roleMapEntryUsers`roleMapEntryGroups;"The rolemap entry datatype");
-     (0N;dtEntityId;namespace;`roleMapDt;"RoleMap";0N;enlist`roleMapEntries;"The rolemap datatype"))}
+     (0N;dtEntityId;`userDt;namespace;"User";0N;enlist`userName;"The user datatype");
+     (0N;dtEntityId;`groupDt;namespace;"Group";0N;enlist`groupId;"The group datatype");
+     (0N;dtEntityId;`roleDt;namespace;"Role";0N;enlist`roleName;"The role datatype");
+     (0N;dtEntityId;`roleMapEntryDt;namespace;"RoleMapEntry";0N;`roleMapEntryRole`roleMapEntryUsers`roleMapEntryGroups;"The rolemap entry datatype");
+     (0N;dtEntityId;`roleMapDt;namespace;"RoleMap";0N;enlist`roleMapEntries;"The rolemap datatype"))}
 
 initHdb:{[]
   / start at entity & transaction 1
@@ -68,21 +68,22 @@ initHdb:{[]
   / allocate & assign real entity ids to data
   dataIds:(A`entityId),D`entityId;
   allocation:allocateNewEntityIds[nextEntityId;dataIds];
-  eIds:value eIdsMapping:allocation`eIdsMapping;
-  A:update entityId:(count A)#eIds,entityDt:eIdsMapping[entityDt] from A;
-  D:update entityId:(count A)_eIds,entityDt:eIdsMapping[entityDt] from D;
+  eIds:value idMappings:allocation`idMappings;
+  A:update entityId:(count A)#eIds,entityDt:idMappings[entityDt] from A;
+  D:update entityId:(count A)_eIds,entityDt:idMappings[entityDt] from D;
   / overrides symbolic ids with entity ids
   D[`dtFields]:A[`entityId]@(A`entityName)?D`dtFields;
 
   / create EVT tables
   data:joinTables[A;D];
   evtTables:toEvtTables[data;nextTxId];
 
-  / only include explicit mappings
-  tmpIds:0N_ eIdsMapping;
   nextId:allocation`nextId;
+  / keyed access by entityName + O(1) lookup by entityId
+  A:update `u#entityId from `entityName xkey A;
+  D:update `u#entityId from `entityName xkey D;
 
-  :`ids`newIds`tmpIds`nextId`nextTxId`attributes`datatypes`tables!(eIds;eIds;()!();nextId;nextTxId+1;A;D;evtTables)}
+  :`idMappings`nextId`nextTxId`newAttributes`newDatatypes`tables!(idMappings;nextId;nextTxId+1;A;D;evtTables)}
 
 joinTables:{ / needed when x & y are tables with different schemas; result is a list of dictionaries
   countX:count x;
@@ -92,19 +93,22 @@ joinTables:{ / needed when x & y are tables with different schemas; result is a
   joined[countX+til countY]:y;
   :joined}
 
-toEvtTables:{[normalizedData;nextTxId]
+toEvtTables:{[data;nextTxId]
   / figure out ALL distinct attribute names being written to
-  k:(distinct raze key each normalizedData) except `entityId;
+  k:targetAttrs[data];
   / mask of where each attribute appears
-  w:where each (count each normalizedData)>/:(key each normalizedData)?\:/:k;
-  eIds:normalizedData[;`entityId];
+  w:wTargetAttrs[data; k];
+  eIds:data[;`entityId];
   / get ids by attribute
   e:eIds@w;
   / get values by attribute
-  v:(flip normalizedData[;k])@'w;
+  v:(flip data[;k])@'w;
   / create in-memory EV tables keyed by attribute name
   :k!{[e;v;t;w]flip`e`v`t!(e[w];v[w];t)}[e;v;nextTxId] each til count k}
 
+targetAttrs:{[data]:(distinct raze key each data) except `entityId}
+wTargetAttrs:{[data;targetAttrs]:where each (count each data)>/:(key each data)?\:/:targetAttrs}
+
 allocateNewEntityIds:{[nextEntityId;dataIds]
   / verify that ids being referenced are valid
   eIds:(count dataIds)#0N; / initialize ids
@@ -126,7 +130,7 @@ allocateNewEntityIds:{[nextEntityId;dataIds]
   eIds[wTmp]:nextEntityId+til count wTmp;
   nextEntityId+:count wTmp;
   eIds[wTmp]:tmpIdsMapping@dataIds[wTmp];
-  :`nextId`eIdsMapping!(nextEntityId;dataIds!eIds)}
+  :`nextId`idMappings!(nextEntityId;dataIds!eIds)}
 
 loadTpcchSchema:{[nextEntityId;nextTxId]
 
@@ -135,18 +139,28 @@ loadTpcchSchema:{[nextEntityId;nextTxId]
   / allocate & assign real entity ids to data
   dataIds:data[;`entityId];
   allocation:allocateNewEntityIds[nextEntityId;dataIds];
-  eIds:value eIdsMapping:allocation`eIdsMapping;
+  eIds:value idMappings:allocation`idMappings;
   data[;`entityId]:eIds;
 
+  k:targetAttrs[data];  w:wTargetAttrs[data; k];
+  t:k!w; / entityName to indices where those appear in data
+  / attrType is used to cast/validate data being received
+  attrTypes:DB[`newAttributes;([] entityName:k);`attrType];
+  0N!STORAGE_NAMES[STORAGE_CHARS?attrTypes]; / this is just for info purposes
+  / TODO1: cast all fields of type r and R to symbols, then look their ids against the DB[`tables;`entityName] table
+  / TODO2: cast all numeric fields to exact numberic type
+  break;
+
   evtTables:toEvtTables[data;nextTxId];
   A:data@wA:where{`attrType in key x} each data; / attributes
   D:data@wD:where{`dtName in key x} each data; / datatypes
 
-  / only include explicit mappings
-  tmpIds:0N_ eIdsMapping;
   nextId:allocation`nextId;
+  / keyed access by entityName + O(1) lookup by entityId
+  A:update `u#entityId from `entityName xkey A;
+  D:update `u#entityId from `entityName xkey D;
 
-  :`eIds`newIds`tmpIds`nextId`nextTxId`attributes`datatypes`tables!(eIds;eIds;tmpIds;nextId;nextTxId+1;A;D;evtTables)}
+  :`idMappings`nextId`nextTxId`newAttributes`newDatatypes`tables!(idMappings;nextId;nextTxId+1;A;D;evtTables)}
 
 DB:0N!initHdb[];
 TPC:0N!loadTpcchSchema[DB`nextId;DB`nextTxId];

diff --git a/db.q b/db.q
@@ -16,98 +16,153 @@ STORAGE_NAMES,:{`$,[;"Vector"] each string x} STORAGE_NAMES
 STORAGE_TYPES:type each STORAGE_VALUES
 
 attrsSystem:{[attrEntityId]
-  / Attribute schema
-  ((0N;attrEntityId;`entityDt;"r";1b;0b;"This entity's datatype. Every object in the system gets its id from this table. Every object has a type");
-  (0N;attrEntityId;`attrName;"s";1b;0b;"Unique identifier among all attributes. This also controls the table name on disk");
-  (0N;attrEntityId;`attrType;"h";1b;0b;"kdb+ storage type char; see http://code.kx.com/wiki/Reference/Datatypes");
-  (0N;attrEntityId;`attrIndex;"b";1b;0b;"1b if designer expects to filter or sort on this attribute; 0b if not. Defaults to 1b");
-  (0N;attrEntityId;`attrFullText;"b";1b;0b;"1b if attribute value should be indexed for full-text search; 0b if not. Defaults to 1b");
-  (0N;attrEntityId;`entityDoc;"C";1b;0b;"Documentation string");
-   / First-class transactions
-  (0N;attrEntityId;`txTimestamp;"p";1b;0b;"The timestamp associated with this transaction.");
-   / Datatype meta-model
-  (0N;attrEntityId;`dtNamespace;"s";1b;0b;"Datatype's namespace");
-  (0N;attrEntityId;`dtName;"s";1b;0b;"Datatype's local name. Names within a given namespace are unique");
-  (0N;attrEntityId;`dtParent;"r";1b;0b;"Datatype's parent datatype. Only populated in datatype hierarchies");
-  (0N;attrEntityId;`dtList;"r";1b;0b;"Datatype's list datatype");
-  (0N;attrEntityId;`dtComponent;"r";1b;0b;"Datatype's component datatype");
-  (0N;attrEntityId;`dtFields;"R";1b;0b;"Datatype's fields");
-   / Security model
-  (0N;attrEntityId;`userName;"s";1b;0b;"User");
-  (0N;attrEntityId;`groupId;"r";1b;0b;"Group");
-  (0N;attrEntityId;`roleName;"s";1b;0b;"Role name. Unique across the system");
-  (0N;attrEntityId;`roleMapEntryRole;"r";1b;0b;"Reference to a RoleMap entity");
-  (0N;attrEntityId;`roleMapEntryUsers;"R";1b;0b;"References to User entities");
-  (0N;attrEntityId;`roleMapEntryGroups;"R";1b;0b;"References to Group entities");
-  (0N;attrEntityId;`roleMapEntries;"R";1b;0b;"References to RoleMapEntry entities"))}
-
-attrsTpch:{[attrEntityId]
-  ((0N;attrEntityId;`s_name;"C";1b;0b;"Supplier Name");
-  (0N;attrEntityId;`s_nationkey;"r";1b;0b;"Supplier Nation Key");
-  (0N;attrEntityId;`n_name;"C";1b;0b;"Nation Name");
-  (0N;attrEntityId;`n_regionkey;"r";1b;0b;"Nation Region Key");
-  (0N;attrEntityId;`r_name;"C";1b;0b;"Region Name"))}
+  / in-memory table that contains the definition of the core built-in attributes in the database
+  :flip`entityId`entityDt`entityName`attrType`attrIndex`attrFullText`entityDoc!flip
+    ((0N;attrEntityId;`entityDt;"r";1b;0b;"This entity's datatype. Every object in the system gets its id from this table. Every object has a type");
+    (0N;attrEntityId;`entityName;"s";1b;0b;"Unique identifier among all entities in the system. Used as a symbolic entity Id");
+    (0N;attrEntityId;`attrType;"h";1b;0b;"kdb+ storage type char; see http://code.kx.com/wiki/Reference/Datatypes");
+    (0N;attrEntityId;`attrIndex;"b";1b;0b;"1b if designer expects to filter or sort on this attribute; 0b if not. Defaults to 1b");
+    (0N;attrEntityId;`attrFullText;"b";1b;0b;"1b if attribute value should be indexed for full-text search; 0b if not. Defaults to 1b");
+    (0N;attrEntityId;`entityDoc;"C";1b;0b;"Documentation string");
+     / First-class transactions
+    (0N;attrEntityId;`txTimestamp;"p";1b;0b;"The timestamp associated with this transaction.");
+     / Datatype meta-model
+    (0N;attrEntityId;`dtNamespace;"s";1b;0b;"Datatype's namespace");
+    (0N;attrEntityId;`dtName;"C";1b;0b;"Datatype's local name. Names within a given namespace are unique");
+    (0N;attrEntityId;`dtParent;"r";1b;0b;"Datatype's parent datatype. Only populated in datatype hierarchies");
+    (0N;attrEntityId;`dtList;"r";1b;0b;"Datatype's list datatype");
+    (0N;attrEntityId;`dtComponent;"r";1b;0b;"Datatype's component datatype");
+    (0N;attrEntityId;`dtFields;"R";1b;0b;"Datatype's fields");
+     / Security model
+    (0N;attrEntityId;`userName;"s";1b;0b;"User");
+    (0N;attrEntityId;`groupId;"r";1b;0b;"Group");
+    (0N;attrEntityId;`roleName;"s";1b;0b;"Role name. Unique across the system");
+    (0N;attrEntityId;`roleMapEntryRole;"r";1b;0b;"Reference to a RoleMap entity");
+    (0N;attrEntityId;`roleMapEntryUsers;"R";1b;0b;"References to User entities");
+    (0N;attrEntityId;`roleMapEntryGroups;"R";1b;0b;"References to Group entities");
+    (0N;attrEntityId;`roleMapEntries;"R";1b;0b;"References to RoleMapEntry entities"))}
 
+/ Core Datatypes: Datatype + Attribute + User + Group + Role + RoleMapEntry + RoleMap
 dtsSystem:{[attrEntityId;dtEntityId]
-  / Core Datatypes: Datatype + Attribute + User + Group + Role + RoleMapEntry + RoleMap
+  / in-memory table that provides fast access to all datatype definitions in the database
   namespace:`$"http://www.appian.com/ae/types/2009";
-  ((dtEntityId;dtEntityId;namespace;`Datatype;0N;`entityDt`dtNamespace`dtName`dtParent`dtList`dtComponent`dtFields;"The datatype for datatypes");
-   (attrEntityId;dtEntityId;namespace;`Attribute;0N;`entityDt`attrName`attrType`attrIndex`attrFullText;"The attribute datatype");
-   (0N;dtEntityId;namespace;`User;0N;enlist`userName;"The user datatype");
-   (0N;dtEntityId;namespace;`Group;0N;enlist`groupId;"The group datatype");
-   (0N;dtEntityId;namespace;`Role;0N;enlist`roleName;"The role datatype");
-   (0N;dtEntityId;namespace;`RoleMapEntry;0N;`roleMapEntryRole`roleMapEntryUsers`roleMapEntryGroups;"The rolemap entry datatype");
-   (0N;dtEntityId;namespace;`RoleMap;0N;enlist`roleMapEntries;"The rolemap datatype"))}
-
-dtsTpch:{[dtEntityId]
-  namespace:`tpch;
-  ((0N;dtEntityId;namespace;`SUPPLIER;0N;`s_name`s_nationkey;"The Supplier datatype");
-  (0N;dtEntityId;namespace;`NATION;0N;`n_name`n_regionkey;"The Nation datatype");
-  (0N;dtEntityId;namespace;`REGION;0N;enlist `r_name;"The Region datatype"))}
-
-initHdb:{[nextEntityId;nextTxId]
-  / `A` is an in-memory table that contains the definition of the core built-in attributes in the database
-  attrEntityId:-1; / negative numbers are temp ids used to specify relationships between entities before those entities are persisted
-  A:flip`entityId`entityDt`attrName`attrType`attrIndex`attrFullText`entityDoc!flip attrsSystem[attrEntityId],attrsTpch[attrEntityId];
-
-  / `D` is an in-memory table that provides fast access to all datatype definitions in the database
-  dtEntityId:-2;
-  D:flip`entityId`entityDt`dtNamespace`dtName`dtParent`dtFields`entityDoc!flip dtsSystem[attrEntityId;dtEntityId],dtsTpch[dtEntityId];
-
-  / Initialize in-memory tables
-  allTmpIds:(A`entityId),D`entityId;
-  eIds:value tmpIdsMapping::allocateNewEntityIds[nextEntityId;allTmpIds]; / temporary ids TO persisted ids table
-  / 1) assign first X ids from eIds + 2) override temp datatype entity ids with new allocated ids
-  A:update entityId:(count A)#eIds,entityDt:tmpIdsMapping[entityDt] from A;
-  D:update entityId:(count A)_eIds,entityDt:tmpIdsMapping[entityDt] from D;
-  / overrides friendly attribute names with their primary key in the `A` table
-  D[`dtFields]:A[`entityId]@(A`attrName)?D`dtFields; / if `A` is an unkeyed table
-  /D[`dtFields]:{exec entityId from A where attrName in x} each D`dtFields / if `A` is a keyed table
-
-  / join table A and D into a single one (data)
-  data:(count allTmpIds)#();
-  data[til count A]:A;
-  data[(count A) + til count D]:D;
-  /data[;`entityId]:eIds;
+  :flip`entityId`entityDt`entityName`dtNamespace`dtName`dtParent`dtFields`entityDoc!flip
+    ((dtEntityId;dtEntityId;`dtDt;namespace;"Datatype";0N;`entityDt`dtNamespace`dtName`dtParent`dtList`dtComponent`dtFields;"The datatype for datatypes");
+     (attrEntityId;dtEntityId;`attrDt;namespace;"Attribute";0N;`entityDt`entityName`attrType`attrIndex`attrFullText;"The attribute datatype");
+     (0N;dtEntityId;namespace;`userDt;"User";0N;enlist`userName;"The user datatype");
+     (0N;dtEntityId;namespace;`groupDt;"Group";0N;enlist`groupId;"The group datatype");
+     (0N;dtEntityId;namespace;`roleDt;"Role";0N;enlist`roleName;"The role datatype");
+     (0N;dtEntityId;namespace;`roleMapEntryDt;"RoleMapEntry";0N;`roleMapEntryRole`roleMapEntryUsers`roleMapEntryGroups;"The rolemap entry datatype");
+     (0N;dtEntityId;namespace;`roleMapDt;"RoleMap";0N;enlist`roleMapEntries;"The rolemap datatype"))}
+
+initHdb:{[]
+  / start at entity & transaction 1
+  nextEntityId:1; nextTxId:1;
+  / negative numbers are temp ids used to specify relationships between entities before those entities are persisted
+  attrEntityId:-1; dtEntityId:-2;
+
+  / build core attributes + datatypes
+  A:attrsSystem[attrEntityId];
+  D:dtsSystem[attrEntityId;dtEntityId];
+
+  / allocate & assign real entity ids to data
+  dataIds:(A`entityId),D`entityId;
+  allocation:allocateNewEntityIds[nextEntityId;dataIds];
+  eIds:value eIdsMapping:allocation`eIdsMapping;
+  A:update entityId:(count A)#eIds,entityDt:eIdsMapping[entityDt] from A;
+  D:update entityId:(count A)_eIds,entityDt:eIdsMapping[entityDt] from D;
+  / overrides symbolic ids with entity ids
+  D[`dtFields]:A[`entityId]@(A`entityName)?D`dtFields;
+
+  / create EVT tables
+  data:joinTables[A;D];
+  evtTables:toEvtTables[data;nextTxId];
+
+  / only include explicit mappings
+  tmpIds:0N_ eIdsMapping;
+  nextId:allocation`nextId;
+
+  :`ids`newIds`tmpIds`nextId`nextTxId`attributes`datatypes`tables!(eIds;eIds;()!();nextId;nextTxId+1;A;D;evtTables)}
+
+joinTables:{ / needed when x & y are tables with different schemas; result is a list of dictionaries
+  countX:count x;
+  countY:count y;
+  joined:(countX+countY)#();
+  joined[til countX]:x;
+  joined[countX+til countY]:y;
+  :joined}
+
+toEvtTables:{[normalizedData;nextTxId]
   / figure out ALL distinct attribute names being written to
-  k:(distinct raze key each data) except `entityId;
+  k:(distinct raze key each normalizedData) except `entityId;
   / mask of where each attribute appears
-  w:where each (count each data)>/:(key each data)?\:/:k;
+  w:where each (count each normalizedData)>/:(key each normalizedData)?\:/:k;
+  eIds:normalizedData[;`entityId];
   / get ids by attribute
   e:eIds@w;
   / get values by attribute
-  v:(flip data[;k])@'w;
+  v:(flip normalizedData[;k])@'w;
   / create in-memory EV tables keyed by attribute name
-  T:k!{[e;v;t;w]flip`e`v`t!(e[w];v[w];t)}[e;v;nextTxId] each til count k;
+  :k!{[e;v;t;w]flip`e`v`t!(e[w];v[w];t)}[e;v;nextTxId] each til count k}
+
+allocateNewEntityIds:{[nextEntityId;dataIds]
+  / verify that ids being referenced are valid
+  eIds:(count dataIds)#0N; / initialize ids
+  mNull:null dataIds; / find nulls i.e. new entities to be created
+  wNull:where mNull;
+  mNotNull:not mNull; / note: m -> mask
+  wNotNull:where mNotNull; / explicitly specified ids, can be negative (for tmp ids) or positive (for existing ids)
+  listToString:{:trim raze" ",/:string x};
+  if[any not nextEntityId>dataIds@wNotNull; '"Invalid id in (",listToString[dataIds@wNotNull],"). Cannot be greater than nextEntityId (",string[nextEntityId],")"];
+
+  / allocate new ids
+  eIds[wNull]:nextEntityId+til count wNull;
+  nextEntityId+:count wNull;
+
+  / allocate temporary ids
+  wTmp:where mNotNull&dataIds<0;
+  tmpIds:distinct dataIds@wTmp;
+  tmpIdsMapping:tmpIds!nextEntityId+til count tmpIds;
+  eIds[wTmp]:nextEntityId+til count wTmp;
+  nextEntityId+:count wTmp;
+  eIds[wTmp]:tmpIdsMapping@dataIds[wTmp];
+  :`nextId`eIdsMapping!(nextEntityId;dataIds!eIds)}
+
+loadTpcchSchema:{[nextEntityId;nextTxId]
+
+  data:.j.k each read0 `:tpcch.json;
+
+  / allocate & assign real entity ids to data
+  dataIds:data[;`entityId];
+  allocation:allocateNewEntityIds[nextEntityId;dataIds];
+  eIds:value eIdsMapping:allocation`eIdsMapping;
+  data[;`entityId]:eIds;
+
+  evtTables:toEvtTables[data;nextTxId];
+  A:data@wA:where{`attrType in key x} each data; / attributes
+  D:data@wD:where{`dtName in key x} each data; / datatypes
+
   / only include explicit mappings
-  tmpIds:0N_ tmpIdsMapping;
-  :`tmpIds`attributes`datatypes`tables!(tmpIds;A;D;T)}
+  tmpIds:0N_ eIdsMapping;
+  nextId:allocation`nextId;
+
+  :`eIds`newIds`tmpIds`nextId`nextTxId`attributes`datatypes`tables!(eIds;eIds;tmpIds;nextId;nextTxId+1;A;D;evtTables)}
 
-allocateNewEntityIds:{[nextEntityId;tmpEntityIds]
-  eIds:(count tmpEntityIds)#0N;
-  eIds[where mNull]:nextEntityId+til countNulls:0 +/ mNull:0N=tmpEntityIds;
-  tmpIdsMapping:tmpIds!nextEntityId+countNulls+til count tmpIds:distinct tmpEntityIds@wNotNull:where not mNull;
-  eIds[wNotNull]:tmpIdsMapping@tmpEntityIds[wNotNull];
-  :tmpEntityIds!eIds}
+DB:0N!initHdb[];
+TPC:0N!loadTpcchSchema[DB`nextId;DB`nextTxId];
 
-DB:0N!initHdb[1;1]
+\
+attrsTpch:{[]
+  :flip`entityId`entityDt`entityName`attrType`attrIndex`attrFullText`entityDoc!flip
+    ((0N;`attrDt;`s_name;"C";1b;0b;"Supplier Name");
+    (0N;`attrDt;`s_nationkey;"r";1b;0b;"Supplier Nation Key");
+    (0N;`attrDt;`n_name;"C";1b;0b;"Nation Name");
+    (0N;`attrDt;`n_regionkey;"r";1b;0b;"Nation Region Key");
+    (0N;`attrDt;`r_name;"C";1b;0b;"Region Name"))}
+
+dtsTpch:{[]
+  namespace:`tpch;
+  :flip`entityId`entityDt`entityName`dtNamespace`dtName`dtParent`dtFields`entityDoc!flip
+    ((0N;`dtDt;`SUPPLIER;namespace;"Supplier";0N;`s_name`s_nationkey;"The Supplier datatype");
+    (0N;`dtDt;`NATION;namespace;"Nation";0N;`n_name`n_regionkey;"The Nation datatype");
+    (0N;`dtDt;`REGION;namespace;"Region";0N;enlist `r_name;"The Region datatype"))}
diff --git a/db.q b/db.q
@@ -15,7 +15,8 @@ STORAGE_NAMES:`boolean`guid`byte`short`int`long`real`float`char`symbol`timestamp
 STORAGE_NAMES,:{`$,[;"Vector"] each string x} STORAGE_NAMES
 STORAGE_TYPES:type each STORAGE_VALUES
 
-systemDts:{[attrEntityId]
+attrsSystem:{[attrEntityId]
+  / Attribute schema
   ((0N;attrEntityId;`entityDt;"r";1b;0b;"This entity's datatype. Every object in the system gets its id from this table. Every object has a type");
   (0N;attrEntityId;`attrName;"s";1b;0b;"Unique identifier among all attributes. This also controls the table name on disk");
   (0N;attrEntityId;`attrType;"h";1b;0b;"kdb+ storage type char; see http://code.kx.com/wiki/Reference/Datatypes");
@@ -40,36 +41,38 @@ systemDts:{[attrEntityId]
   (0N;attrEntityId;`roleMapEntryGroups;"R";1b;0b;"References to Group entities");
   (0N;attrEntityId;`roleMapEntries;"R";1b;0b;"References to RoleMapEntry entities"))}
 
-tpch:{[attrEntityId]
+attrsTpch:{[attrEntityId]
   ((0N;attrEntityId;`s_name;"C";1b;0b;"Supplier Name");
   (0N;attrEntityId;`s_nationkey;"r";1b;0b;"Supplier Nation Key");
   (0N;attrEntityId;`n_name;"C";1b;0b;"Nation Name");
   (0N;attrEntityId;`n_regionkey;"r";1b;0b;"Nation Region Key");
   (0N;attrEntityId;`r_name;"C";1b;0b;"Region Name"))}
 
+dtsSystem:{[attrEntityId;dtEntityId]
+  / Core Datatypes: Datatype + Attribute + User + Group + Role + RoleMapEntry + RoleMap
+  namespace:`$"http://www.appian.com/ae/types/2009";
+  ((dtEntityId;dtEntityId;namespace;`Datatype;0N;`entityDt`dtNamespace`dtName`dtParent`dtList`dtComponent`dtFields;"The datatype for datatypes");
+   (attrEntityId;dtEntityId;namespace;`Attribute;0N;`entityDt`attrName`attrType`attrIndex`attrFullText;"The attribute datatype");
+   (0N;dtEntityId;namespace;`User;0N;enlist`userName;"The user datatype");
+   (0N;dtEntityId;namespace;`Group;0N;enlist`groupId;"The group datatype");
+   (0N;dtEntityId;namespace;`Role;0N;enlist`roleName;"The role datatype");
+   (0N;dtEntityId;namespace;`RoleMapEntry;0N;`roleMapEntryRole`roleMapEntryUsers`roleMapEntryGroups;"The rolemap entry datatype");
+   (0N;dtEntityId;namespace;`RoleMap;0N;enlist`roleMapEntries;"The rolemap datatype"))}
+
+dtsTpch:{[dtEntityId]
+  namespace:`tpch;
+  ((0N;dtEntityId;namespace;`SUPPLIER;0N;`s_name`s_nationkey;"The Supplier datatype");
+  (0N;dtEntityId;namespace;`NATION;0N;`n_name`n_regionkey;"The Nation datatype");
+  (0N;dtEntityId;namespace;`REGION;0N;enlist `r_name;"The Region datatype"))}
+
 initHdb:{[nextEntityId;nextTxId]
   / `A` is an in-memory table that contains the definition of the core built-in attributes in the database
   attrEntityId:-1; / negative numbers are temp ids used to specify relationships between entities before those entities are persisted
-  / Attribute definition. Note entityId is a virtual attributes i.e. have no explicit storage representation
-  A:flip`entityId`entityDt`attrName`attrType`attrIndex`attrFullText`entityDoc!
-    flip systemDts[attrEntityId],tpch[attrEntityId];
-  /A[`attrType]:STORAGE_TYPES@STORAGE_CHARS?A[`attrType]; / overrides type chars with type names
+  A:flip`entityId`entityDt`attrName`attrType`attrIndex`attrFullText`entityDoc!flip attrsSystem[attrEntityId],attrsTpch[attrEntityId];
 
-  / Core Datatypes: Datatype + Attribute + User + Group + Role + RoleMapEntry + RoleMap
-  / This is an in-memory table that provides fast access to all datatype definitions in the database
-  namespace:`$"http://www.appian.com/ae/types/2009";
+  / `D` is an in-memory table that provides fast access to all datatype definitions in the database
   dtEntityId:-2;
-  D:flip`entityId`entityDt`dtNamespace`dtName`dtParent`dtFields`entityDoc!
-    flip((dtEntityId;dtEntityId;namespace;`Datatype;0N;`entityDt`dtNamespace`dtName`dtParent`dtList`dtComponent`dtFields;"The datatype for datatypes");
-         (attrEntityId;dtEntityId;namespace;`Attribute;0N;`entityDt`attrName`attrType`attrIndex`attrFullText;"The attribute datatype");
-         (0N;dtEntityId;namespace;`User;0N;enlist`userName;"The user datatype");
-         (0N;dtEntityId;namespace;`Group;0N;enlist`groupId;"The group datatype");
-         (0N;dtEntityId;namespace;`Role;0N;enlist`roleName;"The role datatype");
-         (0N;dtEntityId;namespace;`RoleMapEntry;0N;`roleMapEntryRole`roleMapEntryUsers`roleMapEntryGroups;"The rolemap entry datatype");
-         (0N;dtEntityId;namespace;`RoleMap;0N;enlist`roleMapEntries;"The rolemap datatype");
-         (0N;dtEntityId;namespace;`SUPPLIER;0N;`s_name`s_nationkey;"The Supplier datatype");
-         (0N;dtEntityId;namespace;`NATION;0N;`n_name`n_regionkey;"The Nation datatype");
-         (0N;dtEntityId;namespace;`REGION;0N;enlist `r_name;"The Region datatype"));
+  D:flip`entityId`entityDt`dtNamespace`dtName`dtParent`dtFields`entityDoc!flip dtsSystem[attrEntityId;dtEntityId],dtsTpch[dtEntityId];
 
   / Initialize in-memory tables
   allTmpIds:(A`entityId),D`entityId;

diff --git a/db.q b/db.q
@@ -15,34 +15,44 @@ STORAGE_NAMES:`boolean`guid`byte`short`int`long`real`float`char`symbol`timestamp
 STORAGE_NAMES,:{`$,[;"Vector"] each string x} STORAGE_NAMES
 STORAGE_TYPES:type each STORAGE_VALUES
 
+systemDts:{[attrEntityId]
+  ((0N;attrEntityId;`entityDt;"r";1b;0b;"This entity's datatype. Every object in the system gets its id from this table. Every object has a type");
+  (0N;attrEntityId;`attrName;"s";1b;0b;"Unique identifier among all attributes. This also controls the table name on disk");
+  (0N;attrEntityId;`attrType;"h";1b;0b;"kdb+ storage type char; see http://code.kx.com/wiki/Reference/Datatypes");
+  (0N;attrEntityId;`attrIndex;"b";1b;0b;"1b if designer expects to filter or sort on this attribute; 0b if not. Defaults to 1b");
+  (0N;attrEntityId;`attrFullText;"b";1b;0b;"1b if attribute value should be indexed for full-text search; 0b if not. Defaults to 1b");
+  (0N;attrEntityId;`entityDoc;"C";1b;0b;"Documentation string");
+   / First-class transactions
+  (0N;attrEntityId;`txTimestamp;"p";1b;0b;"The timestamp associated with this transaction.");
+   / Datatype meta-model
+  (0N;attrEntityId;`dtNamespace;"s";1b;0b;"Datatype's namespace");
+  (0N;attrEntityId;`dtName;"s";1b;0b;"Datatype's local name. Names within a given namespace are unique");
+  (0N;attrEntityId;`dtParent;"r";1b;0b;"Datatype's parent datatype. Only populated in datatype hierarchies");
+  (0N;attrEntityId;`dtList;"r";1b;0b;"Datatype's list datatype");
+  (0N;attrEntityId;`dtComponent;"r";1b;0b;"Datatype's component datatype");
+  (0N;attrEntityId;`dtFields;"R";1b;0b;"Datatype's fields");
+   / Security model
+  (0N;attrEntityId;`userName;"s";1b;0b;"User");
+  (0N;attrEntityId;`groupId;"r";1b;0b;"Group");
+  (0N;attrEntityId;`roleName;"s";1b;0b;"Role name. Unique across the system");
+  (0N;attrEntityId;`roleMapEntryRole;"r";1b;0b;"Reference to a RoleMap entity");
+  (0N;attrEntityId;`roleMapEntryUsers;"R";1b;0b;"References to User entities");
+  (0N;attrEntityId;`roleMapEntryGroups;"R";1b;0b;"References to Group entities");
+  (0N;attrEntityId;`roleMapEntries;"R";1b;0b;"References to RoleMapEntry entities"))}
+
+tpch:{[attrEntityId]
+  ((0N;attrEntityId;`s_name;"C";1b;0b;"Supplier Name");
+  (0N;attrEntityId;`s_nationkey;"r";1b;0b;"Supplier Nation Key");
+  (0N;attrEntityId;`n_name;"C";1b;0b;"Nation Name");
+  (0N;attrEntityId;`n_regionkey;"r";1b;0b;"Nation Region Key");
+  (0N;attrEntityId;`r_name;"C";1b;0b;"Region Name"))}
+
 initHdb:{[nextEntityId;nextTxId]
   / `A` is an in-memory table that contains the definition of the core built-in attributes in the database
   attrEntityId:-1; / negative numbers are temp ids used to specify relationships between entities before those entities are persisted
+  / Attribute definition. Note entityId is a virtual attributes i.e. have no explicit storage representation
   A:flip`entityId`entityDt`attrName`attrType`attrIndex`attrFullText`entityDoc!
-    flip( / Attribute definition. Note entityId is a virtual attributes i.e. have no explicit storage representation
-         (0N;attrEntityId;`entityDt;"r";1b;0b;"This entity's datatype. Every object in the system gets its id from this table. Every object has a type");
-         (0N;attrEntityId;`attrName;"s";1b;0b;"Unique identifier among all attributes. This also controls the table name on disk");
-         (0N;attrEntityId;`attrType;"h";1b;0b;"kdb+ storage type char; see http://code.kx.com/wiki/Reference/Datatypes");
-         (0N;attrEntityId;`attrIndex;"b";1b;0b;"1b if designer expects to filter or sort on this attribute; 0b if not. Defaults to 1b");
-         (0N;attrEntityId;`attrFullText;"b";1b;0b;"1b if attribute value should be indexed for full-text search; 0b if not. Defaults to 1b");
-         (0N;attrEntityId;`entityDoc;"C";1b;0b;"Documentation string");
-          / First-class transactions
-         (0N;attrEntityId;`txTimestamp;"p";1b;0b;"The timestamp associated with this transaction.");
-          / Datatype meta-model
-         (0N;attrEntityId;`dtNamespace;"s";1b;0b;"Datatype's namespace");
-         (0N;attrEntityId;`dtName;"s";1b;0b;"Datatype's local name. Names within a given namespace are unique");
-         (0N;attrEntityId;`dtParent;"r";1b;0b;"Datatype's parent datatype. Only populated in datatype hierarchies");
-         (0N;attrEntityId;`dtList;"r";1b;0b;"Datatype's list datatype");
-         (0N;attrEntityId;`dtComponent;"r";1b;0b;"Datatype's component datatype");
-         (0N;attrEntityId;`dtFields;"R";1b;0b;"Datatype's fields");
-          / Security model
-         (0N;attrEntityId;`userName;"s";1b;0b;"User");
-         (0N;attrEntityId;`groupId;"r";1b;0b;"Group");
-         (0N;attrEntityId;`roleName;"s";1b;0b;"Role name. Unique across the system");
-         (0N;attrEntityId;`roleMapEntryRole;"r";1b;0b;"Reference to a RoleMap entity");
-         (0N;attrEntityId;`roleMapEntryUsers;"R";1b;0b;"References to User entities");
-         (0N;attrEntityId;`roleMapEntryGroups;"R";1b;0b;"References to Group entities");
-         (0N;attrEntityId;`roleMapEntries;"R";1b;0b;"References to RoleMapEntry entities"));
+    flip systemDts[attrEntityId],tpch[attrEntityId];
   /A[`attrType]:STORAGE_TYPES@STORAGE_CHARS?A[`attrType]; / overrides type chars with type names
 
   / Core Datatypes: Datatype + Attribute + User + Group + Role + RoleMapEntry + RoleMap
@@ -56,7 +66,10 @@ initHdb:{[nextEntityId;nextTxId]
          (0N;dtEntityId;namespace;`Group;0N;enlist`groupId;"The group datatype");
          (0N;dtEntityId;namespace;`Role;0N;enlist`roleName;"The role datatype");
          (0N;dtEntityId;namespace;`RoleMapEntry;0N;`roleMapEntryRole`roleMapEntryUsers`roleMapEntryGroups;"The rolemap entry datatype");
-         (0N;dtEntityId;namespace;`RoleMap;0N;enlist`roleMapEntries;"The rolemap datatype"));
+         (0N;dtEntityId;namespace;`RoleMap;0N;enlist`roleMapEntries;"The rolemap datatype");
+         (0N;dtEntityId;namespace;`SUPPLIER;0N;`s_name`s_nationkey;"The Supplier datatype");
+         (0N;dtEntityId;namespace;`NATION;0N;`n_name`n_regionkey;"The Nation datatype");
+         (0N;dtEntityId;namespace;`REGION;0N;enlist `r_name;"The Region datatype"));
 
   / Initialize in-memory tables
   allTmpIds:(A`entityId),D`entityId;

diff --git a/db.q b/db.q
@@ -83,14 +83,10 @@ initHdb:{[nextEntityId;nextTxId]
   v:(flip data[;k])@'w;
   / create in-memory EV tables keyed by attribute name
   T:k!{[e;v;t;w]flip`e`v`t!(e[w];v[w];t)}[e;v;nextTxId] each til count k;
-  tmpIds:getExplicitTmpIdsMapping[tmpIdsMapping];
+  / only include explicit mappings
+  tmpIds:0N_ tmpIdsMapping;
   :`tmpIds`attributes`datatypes`tables!(tmpIds;A;D;T)}
 
-getExplicitTmpIdsMapping:{[tmpIdsMapping]
- tmpIds:key tmpIdsMapping;
- explicitTmpIds:tmpIds@where not 0N=tmpIds;
- :explicitTmpIds!(tmpIdsMapping@explicitTmpIds)}
-
 allocateNewEntityIds:{[nextEntityId;tmpEntityIds]
   eIds:(count tmpEntityIds)#0N;
   eIds[where mNull]:nextEntityId+til countNulls:0 +/ mNull:0N=tmpEntityIds;

diff --git a/db.q b/db.q
@@ -59,17 +59,17 @@ initHdb:{[nextEntityId;nextTxId]
          (0N;dtEntityId;namespace;`RoleMap;0N;enlist`roleMapEntries;"The rolemap datatype"));
 
   / Initialize in-memory tables
-  tmpEIds:(A`entityId),D`entityId;
-  eIds:value eIdsMapping::allocateNewEntityIds[nextEntityId;tmpEIds]; / temporary ids TO persisted ids table
+  allTmpIds:(A`entityId),D`entityId;
+  eIds:value tmpIdsMapping::allocateNewEntityIds[nextEntityId;allTmpIds]; / temporary ids TO persisted ids table
   / 1) assign first X ids from eIds + 2) override temp datatype entity ids with new allocated ids
-  A:update entityId:(count A)#eIds,entityDt:eIdsMapping[entityDt] from A;
-  D:update entityId:(count A)_eIds,entityDt:eIdsMapping[entityDt] from D;
+  A:update entityId:(count A)#eIds,entityDt:tmpIdsMapping[entityDt] from A;
+  D:update entityId:(count A)_eIds,entityDt:tmpIdsMapping[entityDt] from D;
   / overrides friendly attribute names with their primary key in the `A` table
   D[`dtFields]:A[`entityId]@(A`attrName)?D`dtFields; / if `A` is an unkeyed table
   /D[`dtFields]:{exec entityId from A where attrName in x} each D`dtFields / if `A` is a keyed table
 
   / join table A and D into a single one (data)
-  data:(count tmpEIds)#();
+  data:(count allTmpIds)#();
   data[til count A]:A;
   data[(count A) + til count D]:D;
   /data[;`entityId]:eIds;
@@ -83,7 +83,13 @@ initHdb:{[nextEntityId;nextTxId]
   v:(flip data[;k])@'w;
   / create in-memory EV tables keyed by attribute name
   T:k!{[e;v;t;w]flip`e`v`t!(e[w];v[w];t)}[e;v;nextTxId] each til count k;
-  :`eIds`attributes`datatypes`tables!(eIdsMapping;A;D;T)}
+  tmpIds:getExplicitTmpIdsMapping[tmpIdsMapping];
+  :`tmpIds`attributes`datatypes`tables!(tmpIds;A;D;T)}
+
+getExplicitTmpIdsMapping:{[tmpIdsMapping]
+ tmpIds:key tmpIdsMapping;
+ explicitTmpIds:tmpIds@where not 0N=tmpIds;
+ :explicitTmpIds!(tmpIdsMapping@explicitTmpIds)}
 
 allocateNewEntityIds:{[nextEntityId;tmpEntityIds]
   eIds:(count tmpEntityIds)#0N;

diff --git a/db.q b/db.q
@@ -15,17 +15,6 @@ STORAGE_NAMES:`boolean`guid`byte`short`int`long`real`float`char`symbol`timestamp
 STORAGE_NAMES,:{`$,[;"Vector"] each string x} STORAGE_NAMES
 STORAGE_TYPES:type each STORAGE_VALUES
 
-/ Historical DB utility functions
-getHdbTableHandle:{[a]`$,[;"/"] ":",string 0x0 sv md5 string a}
-isPersistedHdbTable:{[th]11h~type key th}
-isPersistedAttribute:{[a]isPersistedHdbTable getHdbTableHandle a}
-createHdbTable:{[a]$[isPersistedHdbTable th:getHdbTableHandle a;th;.[th;();:;([]e:();v:())]]}
-
-appendToHdbSystemTable:{[systemAttributes;a;attrValues]
-  th:getHdbTableHandle[a];
-  rows:([]e:til count systemAttributes; v:attrValues);
-  upsert[th;rows]}
-
 initHdb:{[nextEntityId;nextTxId]
   / `A` is an in-memory table that contains the definition of the core built-in attributes in the database
   attrEntityId:-1; / negative numbers are temp ids used to specify relationships between entities before those entities are persisted

diff --git a/db.k b/db.k
@@ -1,7 +0,0 @@
-/ c: count | ht: historical table | st: snapshot table | rt: realtime table | tx: transaction
-createRandomHdbFrom:{[c;u]flip`e`v`t!(c?c*4;u@c?count u;1+til c)}
-createRandomHdb:createRandomHdbFrom[;"abcdefghijklmnopqrstuvwxyz"]
-appendToHdbFrom:{[ht;c;u]ht upsert flip(c?100000*count u;c?u;(1+$[0~count lastTx:select [-1] t from ht;0;first lastTx@`t]))}
-appendToHdb:appendToHdbFrom[;;"abcdefghijklmnopqrstuvwxyz"]
-
-createSnapshotTable:{[ht;tx]tx:$[-1~tx;0Wj;tx];s:`v xasc select e,v from ht where t <= tx;w:last each value group s@`e;`e xkey s@w}

diff --git a/db.q b/db.q
@@ -0,0 +1,106 @@
+\c 30 300
+
+/ KDB built-in types
+
+/ default values
+STORAGE_VALUES:(0b;0Ng;0x00;0h;0i;0;0e;0.0;" ";`;0Np;2000.01m;2000.01.01;2000.01.01T00:00:00.000;0Nn;00:00;00:00:00;00:00:00.000;0)
+STORAGE_VALUES,:enlist each STORAGE_VALUES
+
+/ char aliases
+STORAGE_CHARS:"bgxhijefcspmdznuvtr" / scalar char aliases
+STORAGE_CHARS,:upper STORAGE_CHARS / list char aliases
+
+/ friendly names
+STORAGE_NAMES:`boolean`guid`byte`short`int`long`real`float`char`symbol`timestamp`month`date`datetime`timespan`minute`second`time`ref
+STORAGE_NAMES,:{`$,[;"Vector"] each string x} STORAGE_NAMES
+STORAGE_TYPES:type each STORAGE_VALUES
+
+/ Historical DB utility functions
+getHdbTableHandle:{[a]`$,[;"/"] ":",string 0x0 sv md5 string a}
+isPersistedHdbTable:{[th]11h~type key th}
+isPersistedAttribute:{[a]isPersistedHdbTable getHdbTableHandle a}
+createHdbTable:{[a]$[isPersistedHdbTable th:getHdbTableHandle a;th;.[th;();:;([]e:();v:())]]}
+
+appendToHdbSystemTable:{[systemAttributes;a;attrValues]
+  th:getHdbTableHandle[a];
+  rows:([]e:til count systemAttributes; v:attrValues);
+  upsert[th;rows]}
+
+initHdb:{[nextEntityId;nextTxId]
+  / `A` is an in-memory table that contains the definition of the core built-in attributes in the database
+  attrEntityId:-1; / negative numbers are temp ids used to specify relationships between entities before those entities are persisted
+  A:flip`entityId`entityDt`attrName`attrType`attrIndex`attrFullText`entityDoc!
+    flip( / Attribute definition. Note entityId is a virtual attributes i.e. have no explicit storage representation
+         (0N;attrEntityId;`entityDt;"r";1b;0b;"This entity's datatype. Every object in the system gets its id from this table. Every object has a type");
+         (0N;attrEntityId;`attrName;"s";1b;0b;"Unique identifier among all attributes. This also controls the table name on disk");
+         (0N;attrEntityId;`attrType;"h";1b;0b;"kdb+ storage type char; see http://code.kx.com/wiki/Reference/Datatypes");
+         (0N;attrEntityId;`attrIndex;"b";1b;0b;"1b if designer expects to filter or sort on this attribute; 0b if not. Defaults to 1b");
+         (0N;attrEntityId;`attrFullText;"b";1b;0b;"1b if attribute value should be indexed for full-text search; 0b if not. Defaults to 1b");
+         (0N;attrEntityId;`entityDoc;"C";1b;0b;"Documentation string");
+          / First-class transactions
+         (0N;attrEntityId;`txTimestamp;"p";1b;0b;"The timestamp associated with this transaction.");
+          / Datatype meta-model
+         (0N;attrEntityId;`dtNamespace;"s";1b;0b;"Datatype's namespace");
+         (0N;attrEntityId;`dtName;"s";1b;0b;"Datatype's local name. Names within a given namespace are unique");
+         (0N;attrEntityId;`dtParent;"r";1b;0b;"Datatype's parent datatype. Only populated in datatype hierarchies");
+         (0N;attrEntityId;`dtList;"r";1b;0b;"Datatype's list datatype");
+         (0N;attrEntityId;`dtComponent;"r";1b;0b;"Datatype's component datatype");
+         (0N;attrEntityId;`dtFields;"R";1b;0b;"Datatype's fields");
+          / Security model
+         (0N;attrEntityId;`userName;"s";1b;0b;"User");
+         (0N;attrEntityId;`groupId;"r";1b;0b;"Group");
+         (0N;attrEntityId;`roleName;"s";1b;0b;"Role name. Unique across the system");
+         (0N;attrEntityId;`roleMapEntryRole;"r";1b;0b;"Reference to a RoleMap entity");
+         (0N;attrEntityId;`roleMapEntryUsers;"R";1b;0b;"References to User entities");
+         (0N;attrEntityId;`roleMapEntryGroups;"R";1b;0b;"References to Group entities");
+         (0N;attrEntityId;`roleMapEntries;"R";1b;0b;"References to RoleMapEntry entities"));
+  /A[`attrType]:STORAGE_TYPES@STORAGE_CHARS?A[`attrType]; / overrides type chars with type names
+
+  / Core Datatypes: Datatype + Attribute + User + Group + Role + RoleMapEntry + RoleMap
+  / This is an in-memory table that provides fast access to all datatype definitions in the database
+  namespace:`$"http://www.appian.com/ae/types/2009";
+  dtEntityId:-2;
+  D:flip`entityId`entityDt`dtNamespace`dtName`dtParent`dtFields`entityDoc!
+    flip((dtEntityId;dtEntityId;namespace;`Datatype;0N;`entityDt`dtNamespace`dtName`dtParent`dtList`dtComponent`dtFields;"The datatype for datatypes");
+         (attrEntityId;dtEntityId;namespace;`Attribute;0N;`entityDt`attrName`attrType`attrIndex`attrFullText;"The attribute datatype");
+         (0N;dtEntityId;namespace;`User;0N;enlist`userName;"The user datatype");
+         (0N;dtEntityId;namespace;`Group;0N;enlist`groupId;"The group datatype");
+         (0N;dtEntityId;namespace;`Role;0N;enlist`roleName;"The role datatype");
+         (0N;dtEntityId;namespace;`RoleMapEntry;0N;`roleMapEntryRole`roleMapEntryUsers`roleMapEntryGroups;"The rolemap entry datatype");
+         (0N;dtEntityId;namespace;`RoleMap;0N;enlist`roleMapEntries;"The rolemap datatype"));
+
+  / Initialize in-memory tables
+  tmpEIds:(A`entityId),D`entityId;
+  eIds:value eIdsMapping::allocateNewEntityIds[nextEntityId;tmpEIds]; / temporary ids TO persisted ids table
+  / 1) assign first X ids from eIds + 2) override temp datatype entity ids with new allocated ids
+  A:update entityId:(count A)#eIds,entityDt:eIdsMapping[entityDt] from A;
+  D:update entityId:(count A)_eIds,entityDt:eIdsMapping[entityDt] from D;
+  / overrides friendly attribute names with their primary key in the `A` table
+  D[`dtFields]:A[`entityId]@(A`attrName)?D`dtFields; / if `A` is an unkeyed table
+  /D[`dtFields]:{exec entityId from A where attrName in x} each D`dtFields / if `A` is a keyed table
+
+  / join table A and D into a single one (data)
+  data:(count tmpEIds)#();
+  data[til count A]:A;
+  data[(count A) + til count D]:D;
+  /data[;`entityId]:eIds;
+  / figure out ALL distinct attribute names being written to
+  k:(distinct raze key each data) except `entityId;
+  / mask of where each attribute appears
+  w:where each (count each data)>/:(key each data)?\:/:k;
+  / get ids by attribute
+  e:eIds@w;
+  / get values by attribute
+  v:(flip data[;k])@'w;
+  / create in-memory EV tables keyed by attribute name
+  T:k!{[e;v;t;w]flip`e`v`t!(e[w];v[w];t)}[e;v;nextTxId] each til count k;
+  :`eIds`attributes`datatypes`tables!(eIdsMapping;A;D;T)}
+
+allocateNewEntityIds:{[nextEntityId;tmpEntityIds]
+  eIds:(count tmpEntityIds)#0N;
+  eIds[where mNull]:nextEntityId+til countNulls:0 +/ mNull:0N=tmpEntityIds;
+  tmpIdsMapping:tmpIds!nextEntityId+countNulls+til count tmpIds:distinct tmpEntityIds@wNotNull:where not mNull;
+  eIds[wNotNull]:tmpIdsMapping@tmpEntityIds[wNotNull];
+  :tmpEntityIds!eIds}
+
+DB:0N!initHdb[1;1]
diff --git a/atomic.k → db.k b/atomic.k → db.k
diff --git a/atomic.k b/atomic.k
@@ -1,5 +1,7 @@
-/ ht: historical table | st: snapshot table | rt: realtime table | tx: transaction
-createRandomHdb:{u:"abcdefghijklmnopqrstuvwxyz";flip`e`v`t!(x?x*4;u@x?count u;1+til x)}
-createSnapshotTable:{[ht;tx]s:`v xasc select e,v from ht where t <= tx;w:last each value group s@`e;:`e xkey s@w}
-
+/ c: count | ht: historical table | st: snapshot table | rt: realtime table | tx: transaction
+createRandomHdbFrom:{[c;u]flip`e`v`t!(c?c*4;u@c?count u;1+til c)}
+createRandomHdb:createRandomHdbFrom[;"abcdefghijklmnopqrstuvwxyz"]
+appendToHdbFrom:{[ht;c;u]ht upsert flip(c?100000*count u;c?u;(1+$[0~count lastTx:select [-1] t from ht;0;first lastTx@`t]))}
+appendToHdb:appendToHdbFrom[;;"abcdefghijklmnopqrstuvwxyz"]
 
+createSnapshotTable:{[ht;tx]tx:$[-1~tx;0Wj;tx];s:`v xasc select e,v from ht where t <= tx;w:last each value group s@`e;`e xkey s@w}
diff --git a/atomic.k b/atomic.k
@@ -1,19 +1,5 @@
-/ data sample
-V:"abcdefghijklmnopqrstuvwxyz" / universe of values
-e:1 1 2 3 4 4 4 4 4 4 4 5 6 6 6 6 6 6 8 8 / entity column
-v:V@(#e)?#V / value column
-t:1+!#v / transaction column
+/ ht: historical table | st: snapshot table | rt: realtime table | tx: transaction
+createRandomHdb:{u:"abcdefghijklmnopqrstuvwxyz";flip`e`v`t!(x?x*4;u@x?count u;1+til x)}
+createSnapshotTable:{[ht;tx]s:`v xasc select e,v from ht where t <= tx;w:last each value group s@`e;:`e xkey s@w}
 
-historical:+`e`v`t!(e;v;t) / historical table
 
-/ snapshot table as-of transaction *15*
-whereAsOfTx:&~t>15
-asOfE:e[whereAsOfTx]
-whereLatestE:-1+*:'1_=asOfE,#asOfE
-uniqueE:e[whereLatestE]
-latestV:v[whereLatestE]
-whereSortedV:<latestV
-eSortedByV:uniqueE[whereSortedV]
-sortedV:latestV[whereSortedV]
-
-snapshot:(+(,`e)!,eSortedByV)!+(,`v)!,sortedV / snapshot table
diff --git a/atomic.k b/atomic.k
@@ -1,19 +1,19 @@
 / data sample
 V:"abcdefghijklmnopqrstuvwxyz" / universe of values
 e:1 1 2 3 4 4 4 4 4 4 4 5 6 6 6 6 6 6 8 8 / entity column
-v:V@(#e)_draw#V / value column
+v:V@(#e)?#V / value column
 t:1+!#v / transaction column
 
-.+(`e`v`t;(e;v;t)) / historical table
+historical:+`e`v`t!(e;v;t) / historical table
 
-/ sorting table as-of transaction *15*
+/ snapshot table as-of transaction *15*
 whereAsOfTx:&~t>15
 asOfE:e[whereAsOfTx]
 whereLatestE:-1+*:'1_=asOfE,#asOfE
 uniqueE:e[whereLatestE]
 latestV:v[whereLatestE]
 whereSortedV:<latestV
-eSortedByV:uniqueE[whereSortedLatestV]
+eSortedByV:uniqueE[whereSortedV]
 sortedV:latestV[whereSortedV]
 
-.+(`e`v;(eSortedByV;sortedV)) / snapshot table
+snapshot:(+(,`e)!,eSortedByV)!+(,`v)!,sortedV / snapshot table
diff --git a/atomic.k b/atomic.k
@@ -4,10 +4,16 @@ e:1 1 2 3 4 4 4 4 4 4 4 5 6 6 6 6 6 6 8 8 / entity column
 v:V@(#e)_draw#V / value column
 t:1+!#v / transaction column
 
+.+(`e`v`t;(e;v;t)) / historical table
+
 / sorting table as-of transaction *15*
 whereAsOfTx:&~t>15
 asOfE:e[whereAsOfTx]
 whereLatestE:-1+*:'1_=asOfE,#asOfE
 uniqueE:e[whereLatestE]
 latestV:v[whereLatestE]
-sortedTable:.+(`e`v;(uniqueE;latestV))
+whereSortedV:<latestV
+eSortedByV:uniqueE[whereSortedLatestV]
+sortedV:latestV[whereSortedV]
+
+.+(`e`v;(eSortedByV;sortedV)) / snapshot table
diff --git a/atomic.k b/atomic.k
@@ -2,9 +2,12 @@
 V:"abcdefghijklmnopqrstuvwxyz" / universe of values
 e:1 1 2 3 4 4 4 4 4 4 4 5 6 6 6 6 6 6 8 8 / entity column
 v:V@(#e)_draw#V / value column
+t:1+!#v / transaction column
 
-/ sorting table 
-whereLatestE:-1+*:'1_=e,#e
+/ sorting table as-of transaction *15*
+whereAsOfTx:&~t>15
+asOfE:e[whereAsOfTx]
+whereLatestE:-1+*:'1_=asOfE,#asOfE
 uniqueE:e[whereLatestE]
 latestV:v[whereLatestE]
 sortedTable:.+(`e`v;(uniqueE;latestV))
diff --git a/atomic.q → atomic.k b/atomic.q → atomic.k
diff --git a/atomic.q b/atomic.q
@@ -1,8 +1,9 @@
-/ sorting table
-V:"abcdefghijklmnopqrstuvwxyz"
-e:1 1 2 3 4 4 4 4 4 4 4 5 6 6 6 6 6 6 8 8
-v:V@(#e)_draw#V
+/ data sample
+V:"abcdefghijklmnopqrstuvwxyz" / universe of values
+e:1 1 2 3 4 4 4 4 4 4 4 5 6 6 6 6 6 6 8 8 / entity column
+v:V@(#e)_draw#V / value column
 
+/ sorting table 
 whereLatestE:-1+*:'1_=e,#e
 uniqueE:e[whereLatestE]
 latestV:v[whereLatestE]

diff --git a/atomic.q b/atomic.q
@@ -0,0 +1,9 @@
+/ sorting table
+V:"abcdefghijklmnopqrstuvwxyz"
+e:1 1 2 3 4 4 4 4 4 4 4 5 6 6 6 6 6 6 8 8
+v:V@(#e)_draw#V
+
+whereLatestE:-1+*:'1_=e,#e
+uniqueE:e[whereLatestE]
+latestV:v[whereLatestE]
+sortedTable:.+(`e`v;(uniqueE;latestV))