Question
· Aug 4, 2021

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

Trackcare 2021.2

Please may I know  how to access SQL parameter with string as datatype with NO reference to SQL Table and SQL Column.

Hi Experts,

 

I have these two clarifications to make:

  1. I am struggling to understand the process and method of accessing the SQL table with no reference to SQL parameter or Table but string

This is what I tried: Tired to create ERD diagrams and try to extract Primary and Foreign keys to associate, but I cannot in this case

I would like to do something like this

Select * FROM

SQLUser.SS_User SSU

LEFT JOIN SQLUser.CT_Loc CTL ON CTL.CTLOC_Desc = ?? < Cannot find Info here>

WHERE SSUSR_Initials = 'xxxx"

 

Example:

 

  1. Similarly CT_Loc table has
CTLOC_Code

CT_LOC_Desc,

CT_LOC_Hospital_DR->Hosp_Desc 

 etc – This is a working code

 

Same as

SELECT * from SQLUser.CT_Hospital CTH

LEFT JOIN SQLUser.PAC_FacilityType PFT ON CTH.HOSP_Desc = PFT.FACIL_Desc

 

But please may I know how am I supposed to know when table’s primary key / foreign key do not match and doesn’t show any relationship in ERD as below.

 

But when I extracted the ERD diagram of CT_Loc and CT_ Hospital they do not have any relationship and when I used the rowID, I got wrong answers.

Any detailed explanation will help, with reference to documentation will be appreciated.

Discussion (6)2
Log in or sign up to continue

We may need more clarity to answer the specific question you have but here are some answers to things I could pull from your post:

- The reason relationships are not showing in ERD is because adding an object reference from one class to another doesn't generate a ForeignKey (only relationships do this automatically) thus no relationship is defined in the generated ERD. If a FK is required then it should be defined manually.

- The primary keys for the tables in the ERD diagram are listed in your posted image. The fields listed there are the ones that correspond to the primary key of each of the tables displayed. If you're looking for a programmatically way to see the primary (assuming you don't have access to the code) then you could check information_schema.key_column_usage for constraint_type = "PRIMARY KEY". You may need to look deeper depending on whether table correspond to a global mapped (legacy) table or a "pure" SQL table. 

- As for the different results, it may be you're passing the wrong value to the query (eg. ID for one table being used to query another) or indices are out of sync or corrupted (rebuilding indices should fix this).

Hi @Warlin Garcia 

Firstly Thank you for the response.
Please may I know what more details you need for both 1 and 2 questions above.

1. For question 1 is most common scenario in Trackcare, example "Other logon locations" highlighted has no SQL table or SQL table, it is a String datatype (Link). Such instances are common and if you can help me how to approach towards solution to this specific issue and any reference examples, that would be so useful. Also let me know what specific information you need to get help. 

2. The solution is known "CT_Hospital"  is linked to "PAC_Hospital" and I am keen to know how as user, should know if I am working on different table, ERD did show the relationship between two tables automatically but not for other tables, I know are related. 

3. How do we know how System Parameters are related and how to know from the tables Like "ParamRef" 

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

AA00000005 AA00000005||H065||1 1 H065 04/19/2010 1209

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  

@Nigel.Salm5021 
Thank you very much. It is unfortunate that I lost the precious detailed explanation and I am greatly appreciative and thankful for these details. This information should be really useful

So as I apply your knowledge to 

Question 1: From SS_User Table, As I need to fetch the details "OtherUserLoc" which has no SQL table or SQL column defined as extracted from the Trackcare - > Component - >Items  

1. There is no indication prefix, postfix about the storage type

2. As "OtherUserLoc" is not a SQL table/column, I am not able to establish any relationship. So please can you help me, how can I know if "OtherUserLoc" data is in ParamRef? 

For example; In the CT_Loc table, there is a no reference to ParamRef but when writing query,  Join is made between CT_loc & ParamRef on CT_loc.code = ParamRef.code (No Idea, how we can figure it out, that will help me to extract information about other tables).

2. Yes, I a using Dbeaver, as per the screenshot above, Dbeaver is showing a relationship between CT_Loc and CT_LocPType but not PAC_FacilityType and other tables? Can you help me?

Hi

I have had a quick look at the SS_User Table in L2016. The problem with the SS_Useer table is that it tends to be customised to the needs of the customer and so in my version I have no Column "OtherUserLoc". I checked the class to see if it exists in the class definition and is not projected to SQL but it is not in the class either.

The SS_User table is not a child table. It does have some child Relationships. The Episode and Test Set Tables will reference the SS_User table for the user who registers the Episode and the Laboratory Technician who performs the Tests. They will point to the SS_User Table like this:

Property EPVISUserIDDR As SSUser [ SqlColumnNumber = 31, SqlFieldName = EPVIS_UserID_DR ];

Let me investigate and I will come back to you. As a matter of Interest what exactly do you mean by TrakCare - Components - Items?

Do you mean Episode -> TestSet -> TestItem?

Nigel

@Nigel.Salm5021 
Thank you very much and much appreciated. I will wait and if not specific to SS_User, is there a standard check list approach to take when referring to tables with data type String. a link in the code table, where it gets bold, enabled only when table data is filled in. 

Note: From existing code, CT_Loc joined with "websys.StandardTypeItem on( ParRef = Locationtype)

So I checked the ParRef, they are many and there are many types of websys tables some tables some not tables. but unsure, when to refer to what ? if that is the linkage to be done. 

If I know the standard approach, It would be easy and if that is documented with different types of these 

User epr web websys lab Custom Report. Is there a document which defines, what these are and how to use them to link what code table, that would I think help.

Now how I get these relationships from the Tackcare application , I do not have access to actual code, tables etc. I only have backend access and I have to pull reports, table relationships etc. How I am doing now is is via the "Components" (That should also answer your question). 
attached picture would give complete details.