Mapped-Tables

Mapped Tables and Classes

The previous example Semi-Persistent-Tables was oriented to the
traditional storage model and used dynamic change of globals.
This has limits and risks that are avoided here.

Description

The base class (User.Common) is a template that you apply for
USER, CLERKS, CUSTOMERS, …. that are separated from each other
as before.
The difference: Class User.Common uses parameter [NoExtent]
so it doesn’t include a storage definition.
The storage to use is then assigned in the individual classes
and it is now a static value that defines the Globals to use.
And is a really simple definition. e.g.:

Class User.USR Extends User.Common [ Not NoExtent ]
{
Parameter DEFAULTGLOBAL As STRING = "^USR";
}

All the rest is done by the compiler.
Side effect: POPULATE is specific to every Class/Table

Prerequisites

Make sure you have git and Docker desktop installed.

Installation

Clone/git pull the repo into any local directory

$ git clone https://github.com/r-cemper/Mapped-Tables.git

To build and start the container run:

$ docker compose up -d && docker compose logs -f

To open IRIS console Terminal do:

$ docker-compose exec iris iris session iris
USER>

or using iterm

http://localhost:52773/iterm/

To access IRIS System Management Portal

http://localhost:52773/csp/sys/UtilHome.csp

Testing

Enter iris console or iterm {_SYSTEM/SYS}
or work from SMP > Explorer > SQL

USER>:sql
SQL Command Line Shell
[SQL]USER>>select * from USR where USR_POP(5)>0
2.      select * from USR where USR_POP(5)>0



ID
City
DOB
Name
SSN




1
Bensonhurst
50613
Ahmed,Dmitry M.
782-47-5617


2
Washington
31727
Vivaldi,Xavier X.
802-93-5957


3
Newton
56042
Quine,Hannah N.
948-78-9049


4
Oak Creek
56219
LaRocca,Nellie L.
744-55-4737


5
Miami
49917
Gallant,Kim Z.
555-47-4869



5 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0528s/39,623/207,252/0ms
execute time(s)/globals/cmds/disk: 0.0044s/116/3,631/0ms
query class: %sqlcq.USER.cls3
[SQL]USER>>cos ZW ^USRD
^USRD=5
^USRD(1)=$lb("","Ahmed,Dmitry M.","782-47-5617",50613,"Bensonhurst")
^USRD(2)=$lb("","Vivaldi,Xavier X.","802-93-5957",31727,"Washington")
^USRD(3)=$lb("","Quine,Hannah N.","948-78-9049",56042,"Newton")
^USRD(4)=$lb("","LaRocca,Nellie L.","744-55-4737",56219,"Oak Creek")
^USRD(5)=$lb("","Gallant,Kim Z.","555-47-4869",49917,"Miami")
[SQL]USER>>cos ZW ^USERI
^USRI("NameDob"," AHMED,DMITRY M.",50613,1)=""
^USRI("NameDob"," GALLANT,KIM Z.",49917,5)=""
^USRI("NameDob"," LAROCCA,NELLIE L.",56219,4)=""
^USRI("NameDob"," QUINE,HANNAH N.",56042,3)=""
^USRI("NameDob"," VIVALDI,XAVIER X.",31727,2)=""
^USRI("NameIDX"," AHMED,DMITRY M.",1)=$lb("","782-47-5617")
^USRI("NameIDX"," GALLANT,KIM Z.",5)=$lb("","555-47-4869")
^USRI("NameIDX"," LAROCCA,NELLIE L.",4)=$lb("","744-55-4737")
^USRI("NameIDX"," QUINE,HANNAH N.",3)=$lb("","948-78-9049")
^USRI("NameIDX"," VIVALDI,XAVIER X.",2)=$lb("","802-93-5957")
^USRI("SSNKey"," 555-47-4869",5)=$lb("","Gallant,Kim Z.")
^USRI("SSNKey"," 744-55-4737",4)=$lb("","LaRocca,Nellie L.")
^USRI("SSNKey"," 782-47-5617",1)=$lb("","Ahmed,Dmitry M.")
^USRI("SSNKey"," 802-93-5957",2)=$lb("","Vivaldi,Xavier X.")
^USRI("SSNKey"," 948-78-9049",3)=$lb("","Quine,Hannah N.")

For temporary use on PPG this looks like this

[SQL]USER>>select * from PPG where PPG_POP(3)>0
8.      select * from PPG where PPG_POP(3)>0



ID
City
DOB
Name
SSN




1
Queensbury
42086
Nichols,Phyllis F.
677-79-9564


2
Elmhurst
44148
Vanzetti,Milhouse C.
178-30-1660


3
Larchmont
55590
Jenkins,Joshua V.
586-48-2608



3 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0028s/37/4,700/0ms
execute time(s)/globals/cmds/disk: 0.0044s/104/3,054/0ms
query class: %sqlcq.USER.cls3
[SQL]USER>>cos zw ^||PPGD
^||PPGD=3
^||PPGD(1)=$lb("","Nichols,Phyllis F.","677-79-9564",42086,"Queensbury")
^||PPGD(2)=$lb("","Vanzetti,Milhouse C.","178-30-1660",44148,"Elmhurst")
^||PPGD(3)=$lb("","Jenkins,Joshua V.","586-48-2608",55590,"Larchmont")
[SQL]USER>>cos zw ^||PPGI
^||PPGI("NameDob"," JENKINS,JOSHUA V.",55590,3)=""
^||PPGI("NameDob"," NICHOLS,PHYLLIS F.",42086,1)=""
^||PPGI("NameDob"," VANZETTI,MILHOUSE C.",44148,2)=""
^||PPGI("NameIDX"," JENKINS,JOSHUA V.",3)=$lb("","586-48-2608")
^||PPGI("NameIDX"," NICHOLS,PHYLLIS F.",1)=$lb("","677-79-9564")
^||PPGI("NameIDX"," VANZETTI,MILHOUSE C.",2)=$lb("","178-30-1660")
^||PPGI("SSNKey"," 178-30-1660",2)=$lb("","Vanzetti,Milhouse C.")
^||PPGI("SSNKey"," 586-48-2608",3)=$lb("","Jenkins,Joshua V.")
^||PPGI("SSNKey"," 677-79-9564",1)=$lb("","Nichols,Phyllis F.")

Any normal SELECT just requires the Table name. Nothing else. No tricks

[SQL]USER>>SELECT name,city,id from PPG order by city
9.      SELECT name,city,id from PPG order by city



Name
City
ID




Vanzetti,Milhouse C.
Elmhurst
2


Jenkins,Joshua V.
Larchmont
3


Nichols,Phyllis F.
Queensbury
1



3 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0591s/39,452/216,468/0ms
execute time(s)/globals/cmds/disk: 0.0003s/4/936/0ms
query class: %sqlcq.USER.cls6

actual Article . .
original Article