Semi-Persistent-Tables

Semi-Persistent Tables and Classes

This means a different approach to global storage.
While by default you have generated hard coded storage globals.
This example uses local variables with indirection to define
the globals where your data are homed.

Description

The base class (User.People) is kind of a common template that
you apply for USER, CLERKS, CUSTOMERS, …. that are strictly
separated from each other.
I admit that using inheritance may offer similar behaviour.
As the original description dates from 2020 it is all designed
for the traditional storage model.
Sharding, Columnar store, …. is not addressed by the example

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/Semi-Persistent-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 people where MyPopulate('^USER',5)>0
2.      select * from people where MyPopulate('^USER',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 ^USERD
^USERD=5
^USERD(1)=$lb("","Ahmed,Dmitry M.","782-47-5617",50613,"Bensonhurst")
^USERD(2)=$lb("","Vivaldi,Xavier X.","802-93-5957",31727,"Washington")
^USERD(3)=$lb("","Quine,Hannah N.","948-78-9049",56042,"Newton")
^USERD(4)=$lb("","LaRocca,Nellie L.","744-55-4737",56219,"Oak Creek")
^USERD(5)=$lb("","Gallant,Kim Z.","555-47-4869",49917,"Miami")
[SQL]USER>>cos ZW ^USERI
^USERI("NameDob"," AHMED,DMITRY M.",50613,1)=""
^USERI("NameDob"," GALLANT,KIM Z.",49917,5)=""
^USERI("NameDob"," LAROCCA,NELLIE L.",56219,4)=""
^USERI("NameDob"," QUINE,HANNAH N.",56042,3)=""
^USERI("NameDob"," VIVALDI,XAVIER X.",31727,2)=""
^USERI("NameIDX"," AHMED,DMITRY M.",1)=$lb("","782-47-5617")
^USERI("NameIDX"," GALLANT,KIM Z.",5)=$lb("","555-47-4869")
^USERI("NameIDX"," LAROCCA,NELLIE L.",4)=$lb("","744-55-4737")
^USERI("NameIDX"," QUINE,HANNAH N.",3)=$lb("","948-78-9049")
^USERI("NameIDX"," VIVALDI,XAVIER X.",2)=$lb("","802-93-5957")
^USERI("SSNKey"," 555-47-4869",5)=$lb("","Gallant,Kim Z.")
^USERI("SSNKey"," 744-55-4737",4)=$lb("","LaRocca,Nellie L.")
^USERI("SSNKey"," 782-47-5617",1)=$lb("","Ahmed,Dmitry M.")
^USERI("SSNKey"," 802-93-5957",2)=$lb("","Vivaldi,Xavier X.")
^USERI("SSNKey"," 948-78-9049",3)=$lb("","Quine,Hannah N.")

For temporary use on PPG this looks like this

[SQL]USER>>select * from people where MyPopulate('||PPG',3)>0
8.      select * from people where MyPopulate('||PPG',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.")

And any normal SELECT just requires the STATIC condition to identify the Globals

[SQL]USER>>SELECT name,city,id from People where SetStorage('||PPG')>0 order by city
9.      SELECT name,city,id from People where SetStorage('||PPG')>0 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

original Article