How to find the Primary & Foreign Keys & How to access tables with String as Parameter

Hi

I started writing a response to your questions yesterday afternoon and I wrote a lengthy explanation using LabTrak as the basis for my response. I wrote about 400 lines of explanation and then my laptop rebooted to install some updates and I lost everything I wrote. So I am going to answer but I am going to abbreviate the explanation as I don't have enough time to rewrite the whole document I wrote yesterday.

1) Different database Storage Models

• DefaultStrorage
  • Is the Storage model used when you create a new Class/Table in Ensemble/IRIS.
  • Stores data in a Global whose name ends with a "D". It stores Indices for the Class/Table in a Global ending with an "I" and puts Stream data into a Global where the Name ends with an "S"
  • Data is stored in a $list structure. $list creates a couple of bytes at the beginning of each field that informs the Compiler how long the field is and the base datatype of the field: String, Number, Boolean and Bit.
  • By default, the RowId is a Sequential Contiguous Integer starting from 1. The RowId is referenced by the column named "ID"
  • The RowId can be overridden by creating an Index on a property of the class and assigning it the attributes [Unique, PrimaryKey, IDKey]. PrimaryKey says that the property value is the RowId for each row in the Table. IDKey says that the property Value is the Object.%Id() and is the Object Identifier. Unique is implicit in that you cannot have two different records with the same RowId value.
  • The Global reference for the Data Global looks like this:
  • ^GlobalD(RowId)=$lb(Property1,Property2,...,PropertyN)
  • An index on Property X will generate a Global Reference: ^GlobalI("{IndexName}",{Property_Value},{RowId_Value"})=""
  • The RowId, irrespective of whether it is system generated or based on the value of a specific property or properties is the "ID" in an SQL statement
• SQLStorage
  • This model was implemented to allow InterSystems customers who had developed applications that used direct Global Access to create a Mapping of the Global Structures in such a way that the data in the global can be exposed as Class Objects or Table Rows.
  • The RowId can be referenced as "ID" in an SQL Statement. The RowId has to be generated by the application even if it is a sequential Integer, though in many applications including LabTrak typically subscripted their Application Globals with a data value generated by the application. For example, the Episode Number in LabTrak is based on the 2 character Site Code followed by a string of Numerics. e.g. AA1345363

2) Links between Tables

• A Designative Reference is a term we use for a Property/Column whose value is the RowId of a row in another Class/Table. The most common examples are code tables. If you have a code table CT_Country where the Country_Code is the RowId of the table. A column on the table EP_VisitNumber (A Trak Episode)  called CountryDR is linked to a row in CT_Country. 
• The CT_Country Global will look like this: ^User.CTCountryD({Country_Code})="{Country_Name}\{Country_Currency}\{Country_Telephone_Code}" and an example would be: ^User.CTCountryD("ZA")="South Agrica\ZAR\+27"
• The Episode Record will look like this: ^TEPI({Episode_Number})="field1\Field2\...\ZA\...\FieldN".  The SQL query "Select Country_Code from SQLUser.EP_VisitNumber where EP_VisitNumber='AA12347690'" will display ZA from the Country_Code column. You can use a "->" (Points Into) construct supported in Ensemble/IRIS SQL to access the Country_Name without having to specify the CT_Country TableName in the FROM clause or the WHERE clause "WHERE EP_VisitNumber.Country_Code=CT_Country.ID"
• The Select statement looks like this "SELECT EP_VisitNumber,EP_VisitSurname,Country_Code->CountryName from SQLUser.EP_VisitNumber
• Designative References do not support Referential Integrity. That means that you could delete the "ZA" row in CT_Country even though there may be thousands of Episodes that point to that row. In order to enforce Referential Integrity, you declare a ForeignKey definition on CountryCode in the Class Definition. Ensemble will generate an Index of Country Codes and Episode Numbers and if there are any entries in this Index for the Country Code "ZA", i.e. Episodes linked to the Code "ZA" then an Error Status is generated that indicates that the ForeignKey Constraint prevents the delete of the the "ZA" row in CT_Country as there are rows in other Tables that reference that Row.
• LabTrak doesn't use ForeignKeys (generally speaking), the application does, however, prevent you from Deleting rows in Code Tables if those Codes are referenced by other LabTrak Tables.

3) Relationships

There are two types of relationships most commonly used in Ensemble/IRIS Classes/Tables: Parent-Child and One-Many. Many-Many relationships can be defined. Refer to the documentation on Relationships to see how Many-Many relationships are defined. I can't think of any examples of Many-Many relationships in LabTrak so instead, I will just focus on the two main relationship Types.

Parent-Child Relationships

If you look at the TEPI global in LabTrak which is the global which stores Episodes. You will see something like this:

^TEPI({Episode_Number})="{Episode Fields}"

^TEPI({Episode_Number},1,{TestSetCode})="{Test_Set_Fields}"

^TEPI({Episode_Number},1,{Test_Set_Code},"DATA",{Test_Item_Code})="{Test_Item_Fields}"

for example:

^TEPI("AA00000401")=""SALM\NIGEL\M\44453\\\S2\61839\646\61838\480\61839\0DR\\R\\\1039444\\41C028W000\\\\\40\\\F\\fred.smith\\61839\646\N\\AA\,690425\\\\\\N\0\\\\N\H\38768\\\\41C028W000\\\\\\\\\\\\\\\Z03.9\52ZETH000003" - This is the Episode Record

^TEPI("AA00000401",1,"H065",1)="61842\594\jane.doe\61842\597\jane.doe\\N\\\\\\\\\\\\\61839\646\\\N\\\Y\\\A\~~~\ \N\\fred.smith\\\\\\N\\61838\28800\\\1\\\\\Y\\\\61839\38760\\\N\\\\\\\N\61843\8122\\\P\Z03.9\" - This is a Test Set Record within the Episode

^TEPI("AA00000401",1,"H065",1,"DATA","H0440")="4.14\\\\\AAH02\\\\\\\\\" - This is a Test Item Record within the Test Set.

As you can see this global is a Multidimensional Array. It has many subscript levels. The ability to create Arrays like this is one of the features that differentiates Cache/Ensemble/IRIS from any other Database Technology. It is one of the reasons why Ensemble/IRIS creates databases that are, on average, half the size of other database technologies on a Like for Like basis. Partly because the data records do not use fixed-length fields. By using Delimiters or $List we do not need to pad out field values with leading '0s or trailing spaces or NULL characters. Secondly, by storing the Test Sets within the Episode men's that when we access the Episod/IRIS loads the data page that contains that episode into memory it brings with it the Test Sets and Test Items. Where you walk through the Test Sets the Test Set Records are already in memory. We haven't had to go to another data structure in the Database to fetch the Test Set Records from a Test Set Table as you would need to do in other Relational Database Technologies.

If you delete an Episode the Test Sets and Test Items are deleted as well. You do not need to delete the Test Items first, then delete the Test Sets and then, finally, delete the Episode.

Nor do you need to maintain Indices to link Test Items to Test Sets or Test Sets to Episode.

In LabTrak the property in the Parent Table that references the Child rows in the Chile Table is prefixed with the word "Child" and the corresponding property in the Child Table that points to the row in the Parent Table is suffixed with the word "ParRef"

One-Many Relationships

In the One to Many relationship, there will be two tables, a Row in the "One" table is linked to Many rows in the "Many" table. From an Objects point of view, there is a Property in the "One" class that points to the related rows in the "Many" table. In LabTrak the property name is prefixed with the word "child". There is a corresponding property in the "Many" Table and that property name is suffixed with the word "ParRef".

Your Question

When I read your question and I looked at the Schema Diagram I was a bit confused as there is no SQLUser.CT_Loc table in LabTrak. There is however a table called SQLUser.CT_UserLocation.

If you use the Management Portal -> System Explorer -> SQL in the LabTrak namespace and you select the SQLUser schema in the Schema drop-down list on the left of the SQL form. You will be presented with a list of all Tables in the SQLUser schema. If you scroll down and find the table "CT_UserLocation" and you drag it into the "Execute SQL" text area on the form it will display the following SQL Statement:

SELECT 
CTUSL_RowId, CTUSL_Code, CTUSL_Desc, CTUSL_Destination_DR, CTUSL_UniqueSite, CTUSL_MoveDirectory, CTUSL_AccreditationNumber, CTUSL_WebReportFooter, CTUSL_DisplaySequence, CTUSL_RegionCode, CTUSL_Address1, CTUSL_Address2, CTUSL_Address3_Suburb, CTUSL_Address4_State_DR, CTUSL_Address5_PostCode, CTUSL_Phone, CTUSL_Fax, CTUSL_email, CTUSL_StructuredCode, CTUSL_StructuredCodeLength, CTUSL_StructuredCodeLevel, CTUSL_Address, CTUSL_DocCourierRun_DR, CTUSL_LocCourierRun_DR, CTUSL_ActiveFlag, CTUSL_DefaultNID, CTUSL_UnixMoveDirectory, CTUSL_PFforBatchEntry
FROM SQLUser.CT_UserLocation

Note the Columns  CTUSL_Address4_State_DR, CTUSL_DocCourierRun_DR, CTUSL_LocCourierRun_DR. These are all Designative References to rows in other Tables.

The EP_VisitTestSet table contains the following Columns:

SELECT 
VISTS_ParRef, VISTS_RowId, VISTS_TestSetCounter, VISTS_TestSet_DR, VISTS_DateOfEntry, VISTS_TimeOfEntry, VISTS_UserEntered_DR, VISTS_DateOfAuthorisation, VISTS_TimeOfAuthorisation, VISTS_UserAuthorised_DR, VISTS_PathologistID_DR, VISTS_ExcludeFromCCR, VISTS_Rule3Exempt_Sequence, VISTS_Priority_DR, VISTS_Rule3Exempt_Max, VISTS_TherapeutDosage, VISTS_TimeOfDosage, VISTS_24HUVolume, VISTS_24HUTimePeriod, VISTS_BB_TransfEvents_DR, VISTS_Rule3Exempt_Date, VISTS_DateOfPathologistAtt, VISTS_TimeOfPathologistAtt, VISTS_StaffNotes, VISTS_DateOfCreation, VISTS_TimeOfCreation, VISTS_StandardLettersChecked, VISTS_BB_DateRequired, VISTS_ExcludeFromPatientMean, VISTS_UserSite_DR, VISTS_Machine, VISTS_Printed, VISTS_SuperSet_DR, VISTS_StatusResult, VISTS_DFT_TimeOfFirstCollection, VISTS_HISTO_Extra, VISTS_HISTO_BillingItem, VISTS_SupressBilling, VISTS_HospitalRefNumber, VISTS_UserCreated_DR, VISTS_MoveToReferralLab_DR, VISTS_MoveToUserSite_DR, VISTS_DFT_DR, VISTS_DFT_Position, VISTS_DFT_DateOfFirstCollection, VISTS_StatusEntry, VISTS_Confidential, VISTS_SpecimenType_DR, VISTS_SpecimenNo, VISTS_DateOfCollection, VISTS_TimeOfCollection, VISTS_CollectedBy_DR, VISTS_DisplaySequence, VISTS_SupressReason, VISTS_UserSupress_DR, VISTS_DateOfSupressBilling, VISTS_Rule3Exempt_Comments, VISTS_AnatomicalSite_DR, VISTS_Reason_DR, VISTS_DateOfReason, VISTS_TimeOfReason, VISTS_UserReason_DR, VISTS_DateOfReceive, VISTS_TimeOfReceive, VISTS_PaymentCode_DR, VISTS_SpecimenGroup_DR, VISTS_Document_DR, VISTS_BB_Neonatal, VISTS_RR_Date, VISTS_RR_Time, VISTS_RR_User_DR, VISTS_RV_Date, VISTS_RV_Time, VISTS_RV_User_DR, VISTS_BB_DoNotFile, VISTS_DateOfLastChange, VISTS_TimeOfLastChange, VISTS_UserOfLastChange_DR, VISTS_DateOfSentSTM, VISTS_TimeOfSentSTM, VISTS_DateOfReceivedSTM, VISTS_TimeOfReceivedSTM, VISTS_MovementStatus, VISTS_AddedByAction, VISTS_PricingStatus, VISTS_ICD10List, VISTS_LongTerm, VISTS_RequestBy_DR, VISTS_LongTermReason, VISTS_PairedSeraQueue, VISTS_DoctorDR, VISTS_BB_TimeRequired, VISTS_BB_Tags, VISTS_DTOfResultChange, VISTS_DateOfFirstAuthorisation, VISTS_TimeOfFirstAuthorisation, VISTS_AnatomicalSiteFT, VISTS_ReasonReportedTo, VISTS_ReasonTelephone, VISTS_ReasonObservations, VISTS_ManualPCEntry, VISTS_ReasonClearResults, VISTS_ReasonClearSN, VISTS_ReasonResultsNR
FROM SQLUser.EP_VisitTestSet

If I add a WHERE Clause: WHERE VISTS_ParRef %STARTSWITH 'AA' it will return records from the site that "AA" 

If you run this query you will see rows of data that look like this

The ParRef is the Episode Number. The RowId for the Test Set is AA00000005||H065||1, {Episode}||{TestSetCode}||{TestSetCounter}.

I created an ODBC connection to a LabTrak Namespace on our UAT server. I unspecified the Server IP address, the Super Server Port Number, The Namespace and my Credentials. I tested the connection which was successful. Te ODBC connection can be used by Excel and other reporting tools to create Reports and Charts.

I then use an application called DBBeaver (available on the Open Exchange site) to create ERD diagrams of the LabTrak classes. DBeaver uses the InterSystems JDBC driver and you set up the connection in DBeaver itself.

I