A beginner's guide to creating SQL tables and seeing them as classes

The August Article Bounty on the Global Masters article caught my attention, and one of the proposed topics sounded quite interesting in regard to its future use in my teaching. So, here's what I'd like to tell my students about tables in IRIS and how they correlate with the object model. 

First of all, InterSystems IRIS boasts a unified data model. This means that when you work with data, you are not locked into a single paradigm. The same data can be accessed and manipulated as a traditional SQL table, as a native object, or even as a multidimensional array (a global). It means that when you create an SQL table, IRIS automatically creates a corresponding object class. When you define an object class, IRIS automatically makes it available as an SQL table. The data itself is stored only once in IRIS's efficient multidimensional storage engine. The SQL engine and the object engine are simply different "lenses" to view and work with the same data.

First, let's look at the correlation between the relational model and the object model:

It's not always a 1:1 correlation, as you may have several tables represent one class, for example. But it's a general rule of thumb. 

In this article, I will discuss creating a table by listing its columns. 

The most basic approach:

CREATE TABLE [IF NOT EXISTS] table (
   column1 type1 [NOT NULL], 
   column2 type2 [UNIQUE], 
   column3 type3 [PRIMARY KEY]
   ...
   [CONSTRAINT fKeyName FOREIGN KEY (column) REFERENCES refTable (refColumn)]
)

[ ] designate the optional parts.

Let's create a table DC.PostType, which consists of three columns: TypeID(primary key), Name, and Description:

CREATE TABLE DC.PostType (
  TypeID        INT NOT NULL,
  Name          VARCHAR(20), 
  Description   VARCHAR(500),
  CONSTRAINT Type_PK PRIMARY KEY (TypeID)
)

As a result, we will get the following class after executing the SQL statement above:

/// 
Class DC.PostType Extends %Persistent [ ClassType = persistent, DdlAllowed, Final, Owner = {UnknownUser}, ProcedureBlock, SqlRowIdPrivate, SqlTableName = PostType ]
{

Property TypeID As %Library.Integer(MAXVAL = 2147483647, MINVAL = -2147483648) [ Required, SqlColumnNumber = 2 ];
Property Name As %Library.String(MAXLEN = 20) [ SqlColumnNumber = 3 ];
Property Description As %Library.String(MAXLEN = 500) [ SqlColumnNumber = 4 ];
Parameter USEEXTENTSET = 1;
/// Bitmap Extent Index auto-generated by DDL CREATE TABLE statement.  Do not edit the SqlName of this index.
Index DDLBEIndex [ Extent, SqlName = "%%DDLBEIndex", Type = bitmap ];
/// DDL Primary Key Specification
Index TypePK On TypeID [ PrimaryKey, SqlName = Type_PK, Type = index, Unique ];
Storage Default
{
<Data name="PostTypeDefaultData">
<Value name="1">
<Value>TypeID</Value>
</Value>
<Value name="2">
<Value>Name</Value>
</Value>
<Value name="3">
<Value>Description</Value>
</Value>
</Data>
<DataLocation>^B3xx.DXwO.1</DataLocation>
<DefaultData>PostTypeDefaultData</DefaultData>
<ExtentLocation>^B3xx.DXwO</ExtentLocation>
<IdFunction>sequence</IdFunction>
<IdLocation>^B3xx.DXwO.1</IdLocation>
<Index name="DDLBEIndex">
<Location>^B3xx.DXwO.2</Location>
</Index>
<Index name="IDKEY">
<Location>^B3xx.DXwO.1</Location>
</Index>
<Index name="TypePK">
<Location>^B3xx.DXwO.3</Location>
</Index>
<IndexLocation>^B3xx.DXwO.I</IndexLocation>
<StreamLocation>^B3xx.DXwO.S</StreamLocation>
<Type>%Storage.Persistent</Type>
}

}

Key Observations:

• TABLE DC.PostType became Class DC.PostType.
• The class Extends %Persistent, which is what tells IRIS to store its data in the database.
• VARCHAR became %String.
• INT became %Integer.
• The PRIMARY KEY constraint created an Index with the PrimaryKey keyword.

You can now use this table/class from either side, for example, using SQL:

INSERT INTO DC.PostType (TypeID, Name, Description) VALUES (1, 'Question', 'Ask a question from the Community')

There's a lot more to creating tables using SQL, please read the documentation provided below.