A Caché of Tips: Arrays, $DATA, $GET, $ORDER and $MERGE

150 views

Skip to first unread message

Michael Cohen

unread,

Jan 7, 2013, 10:11:13 PM1/7/13

to InterSy...@googlegroups.com

Caché supports the scalar, dynamic array and dimensioned array variables familiar from other MultiValue platforms. So a Caché MVBasic program can use a traditional DIM statement to dimension an array of values. For example,

DIM A(10)

declares that your program can reference A with integer subscripts from 0 thru 10 (see Note *1). If the subscript you supply is a decimal number, it is truncated to an integer. If it is a string that does not represent a number, it is treated as zero. This program TSTA illustrates this.

DIM A(10)

A(1)="ONE"

CRT "A(1): ":A(1)

A(5)="FIVE"

CRT "A(5): ":A(5)

A(5.99)="DECIMAL" ;* 5.99 truncated to 5 and replaces existing value

CRT "A(5.99): ":A(5.99)

CRT "A(5): ":A(5)

A("CHAR1")=123 ;* treated as 0

A("CHAR2")=456 ;* replaces above value

CRT \A("CHAR1"): \:A("CHAR1")

CRT \A("CHAR2"): \:A("CHAR2")

CRT "A(0): ":A(0)

MV:TSTA

A(1): ONE

A(5): FIVE

A(5.99): DECIMAL

A(5): DECIMAL

A("CHAR1"): 456

A("CHAR2"): 456

A(0): 456

MV:

In addition, InterSystems has added un-dimensioned, multidimensional, sparse arrays to MVBasic (see Note *2). The compiler notes that you do not declare a dimension, and treats this as a COS array, and so you can use whatever subscripts you want, including decimal and character values. These sparse arrays are stored efficiently (without mark characters of dynamic arrays). This program TSTC illustrates these differences.

DIM C() ;* note empty subscript for COS array

C(1)="ONE"

CRT "C(1): ":C(1)

C(5)="FIVE"

CRT "C(5): ":C(5)

C(50000000)="FIVE MILLION" ;* no space wasted by undefined, intermediate values

CRT "C(50000000): ":C(50000000)

C(5.99)="DECIMAL" ;* decimal subscripts are preserved

CRT "C(5.99): ":C(5.99)

CRT "C(5): ":C(5)

C("CHAR1")=123 ;* character subscripts are preserved

C("CHAR2")=456

CRT \C("CHAR1"): \:C("CHAR1")

CRT \C("CHAR2"): \:C("CHAR2")

MV:TSTC

C(1): ONE

C(5): FIVE

C(50000000): FIVE MILLION

C(5.99): DECIMAL

C(5): FIVE

C("CHAR1"): 123

C("CHAR2"): 456

MV:

The ability to use large, decimal and character subscripts is often quite useful. This Tip discusses COS functions added to MVBasic to facilitate the use of these COS arrays.

Cache also uses multi-dimensional arrays for persistent database storage. These are referenced in MVBasic as variables beginning with a '^' character. The differences between the local DIM array without subscript illustrated above and a global array are:

1. no DIM is required/allowed, and

2. the global array data persists in the database after your program/session terminates and are visible to other authorized users.

The following program TSTG seems to behave like the one above.

^C(1)="ONE"

CRT "^C(1): ":^C(1)

^C(5)="FIVE"

CRT "^C(5): ":^C(5)

^C(50000000)="FIVE MILLION"

CRT "^C(50000000): ":^C(50000000)

^C(5.99)="DECIMAL"

CRT "^C(5.99): ":^C(5.99)

CRT "^C(5): ":^C(5)

^C("CHAR1")=123

^C("CHAR2")=456

CRT \^C("CHAR1"): \:^C("CHAR1")

CRT \^C("CHAR2"): \:^C("CHAR2")

MV:TSTG

^C(1): ONE

^C(5): FIVE

^C(50000000): FIVE MILLION

^C(5.99): DECIMAL

^C(5): FIVE

^C("CHAR1"): 123

^C("CHAR2"): 456

MV:

The difference is that if you come back later or tomorrow, even if the database server was restarted, the values stored in ^C are still there. For example, you can see them from TCL:

MV:;CRT "^C(50000000): ":^C(50000000)

^C(50000000): FIVE MILLION

You can assign and read these globals in an MVBasic program just as you would for other variables. You can examine and edit them via the Management Portal. You can also display the entire global array via the COS ZWrite command:

MV:[ZW ^C

^C(1)="ONE"

^C(5)="FIVE"

^C(5.99)="DECIMAL"

^C(50000000)="FIVE MILLION"

^C("CHAR1")=123

^C("CHAR2")=456

MV:

^C is referred to as a global, and each set of subscript values, with the corresponding data value, is referred to as a global node. Global nodes are automatically stored in sorted order.

Instead of the hashed file storage that other MultiValue systems use, Caché stores file items as global nodes, where the subscript is the item ID. You still use utilities like CREATE-FILE and MVBasic READ and WRITE to access files, but you can also access the underlying global storage directly.

This tip discusses Caché Object Script functions that manipulate these sparse arrays. These functions have been implemented as part of our MVBasic language. This discussion will involve terminal and MVBasic access.

First I will create a data file, 2 DICTs and 2 data records (nothing new here).

MV:CREATE.FILE TIPS

[421] DICT for file 'TIPS' created. Type = INODE

[418] Default data section for file 'TIPS' created. Type = INODE

[437] Added default record '@ID' to 'DICT TIPS'.

[417] CreateFile Completed.

MV:ED DICT TIPS NAME NUM

NAME

New record.

----:I

0001= D

0002= 1

0003=

0004=

0005= 15L

0006= S

0007=

Bottom at line 6.

----:FI

"NAME" filed in file "DICT TIPS".

NUM

New record.

----:I

0001= D

0002= 2

0003=

0004=

0005= 4R

0006= S

0007=

Bottom at line 6.

----:FI

"NUM" filed in file "DICT TIPS".

MV:ED TIPS 1 3

New record.

----:I

0001= JOHN SMITH

0002= 123

0003=

Bottom at line 2.

----:FI

"1" filed in file "TIPS".

New record.

----:I

0001= JOHN PUBLIC

0002= 456

0003=

Bottom at line 2.

----:FI

"3" filed in file "TIPS".

MV:LIST TIPS NAME NUM

LIST TIPS NAME NUM

TIPS...... NAME........... NUM.

1 JOHN SMITH 123

3 JOHN PUBLIC 456

2 Items listed.

MV:

Our VOC/MD entry shows the globals where the data and DICT are stored:

MV:CT VOC TIPS

TIPS

0001 F

0002 ^TIPS

0003 ^DICT.TIPS

0004

0005

0006

0007

0008

0009

0010

MV:

We can examine these with the COS ZWrite command.

Here is the data:

MV:[ZW ^TIPS

^TIPS=$lb(0)

^TIPS(1)="JOHN SMITHþ123"

^TIPS(3)="JOHN PUBLICþ456"

MV:

Here are the DICTs:

MV:[ZW ^DICT.TIPS

^DICT.TIPS=$lb(0)

^DICT.TIPS("@ID")="Dþ0þþTIPSþ10LþS"

^DICT.TIPS("NAME")="Dþ1þþþ15LþS"

^DICT.TIPS("NUM")="Dþ2þþþ4RþS"

MV:

You can also use COS ZWrite and Write to access individual records:

MV:[ZW ^TIPS(1)

^TIPS(1)="JOHN SMITHþ123"

MV:[W ^TIPS(1)

JOHN SMITHþ123

MV:

These write to the terminal, but are not programmatic access. InterSystems has extended MVBasic to provide direct access to such globals as if they were program variables. I can CRT them, and apply MVBasic syntax and functions to them:

MV:;CRT ^TIPS(1)<1>

JOHN SMITH

MV:

MV:;CRT FIELD(^TIPS(1)<1>," ",2)

SMITH

MV:

Programs often need to know whether a record exists. In this example, there is no record with ID 2, so if I try to access it, I get an error:

MV:;CRT ^TIPS(2)

<UNDEFINED>+1^MVBASIC37804.mvi ^TIPS(2)

MV:

To avoid getting this error InterSystems has added the COS function $GET to MVBasic. With one argument, $GET returns the value of the argument if it exists, else "".

MV:;CRT $GET(^TIPS(1))

JOHN SMITHþ123

MV:MV:;CRT $GET(^TIPS(2))

MV:

Alternatively, you can supply a second argument that is returned if the first argument is not found.

MV:;CRT $GET(^TIPS(2),"<NOT FOUND>")

MV:

$GET only checks the exact combination of subscripts specified. Although MV file data is stored with only a single subscript, other globals, such as indices and ^%MV.SPOOL contain multiple levels. MVBasic also supports the COS $DATA function to allow you to determine if data exists at or below the level specified. For example, I can create some multi-node data:

MV:;^TEMP(1)="ONE"

MV:;^TEMP(2)="TWO"

MV:;^TEMP(2,2)="TWO TWO"

MV:

MV:[ZW ^TEMP

^TEMP(1)="ONE"

^TEMP(2)="TWO"

^TEMP(2,2)="TWO TWO"

MV:

The $DATA function returns one of the following results depending on its argument variable:

Result Meaning

0 The variable is undefined and has no descendents.

1 The variable contains data and has no descendants. Note that the null string ("") qualifies as data.

10 The variable is undefined, but has descendants that contain data. Status 10 identifies an array element that has descendants (contains a downward pointer to another array element) but does not itself contain data.

11 The variable contains data and has descendents. Status 11 identifies a defined array element that has descendants (contains a downward pointer to another array element that also contains data). Variables of this type can be referenced in expressions.

$DATA can be abbreviated $D. Here are some examples with the above data. There is no ^JUNK global in my database.

MV:;CRT $DATA(^JUNK)

MV:

MV:;CRT ^TEMP

<UNDEFINED>+1^MVBASIC37804.mvi ^TEMP

MV:

MV:;CRT $DATA(^TEMP)

MV:

MV:;CRT $DATA(^TEMP(1))

MV:

MV:;CRT $DATA(^TEMP(2))

MV:

Similar info is returned by the EXISTS function:

0: undefined and has no subnodes.

1: defined and has no subnodes.

2: undefined but has defined subnodes.

3: defined and has defined subnodes.

MV:[ZW ^TEMP

^TEMP(1)="ONE"

^TEMP(2)="TWO"

^TEMP(2,2)="TWO TWO"

MV:

MV:;CRT EXISTS(^TEMP)

MV:;CRT EXISTS(^TEMP(1))

MV:;CRT EXISTS(^TEMP(2))

MV:;CRT EXISTS(^TEMP(3))

MV:

You can also use the ASSIGNED and UNASSIGNED functions to determine whether a variable/node is assigned:

MV:;CRT ASSIGNED(^TEMP)

MV:;CRT ASSIGNED(^TEMP(1))

MV:;CRT ASSIGNED(^TEMP(2))

MV:;CRT ASSIGNED(^TEMP(3))

MV:

It is more common to want to process some or all data in a global, perhaps starting at a certain point. The $ORDER function helps do this. You must provide a starting point subscript. To start at the top, use "" as the subscript.

MV:;CRT $ORDER(^TEMP)

MVBasic Syntax Error: Subscripts are required

MV:

MV:;CRT $ORDER(^TEMP())

MVBasic Syntax Error: Subscripts are required

MV:;CRT $ORDER(^TEMP(""))

MV:

The last result indicates that the first subscript of the first node with data is 1.

To find the next first subscript with a value, supply the last result.

MV:;CRT $ORDER(^TEMP(1))

MV:;CRT $ORDER(^TEMP(2))

MV:

This indicates 2 is the next first subscript, and that there is no first subscript after 2.

To find subscripts at the second level, you must supply the first subscript plus a starting point for the second subscript, or "".

MV:;CRT $ORDER(^TEMP(1,""))

MV:

There is no node with a first subscript 1 and a second subscript.

MV:;CRT $ORDER(^TEMP(2,""))

MV:

This indicates that there is a node with a first subscript 2, and a second subscript that is also a 2; that is: (2,2).

Note that if you use multiple subscript levels, you are not required to provide a value for nodes at lower levels. For example, you can assign a value to ^TIPS(5,55) without assigning a value to ^TIPS(5). The following program displays the unique, first subscripts of all nodes.

^TIP(1)="ONE"

^TIP(3)="THREE"

^TIP(3,3)="THIRTY-THREE"

^TIP(5,55)="FIFTY-FIVE"

ID=""

COUNT1=0

LOOP

ID=$ORDER(^TIP(ID))

IF ID <> "" THEN

COUNT1=COUNT1+1

CRT "LEVEL 1 SUBSCRIPT ":COUNT1:": ":ID

END

UNTIL ID=""

REPEAT

Running it reports:

LEVEL 1 SUBSCRIPT 1: 1

LEVEL 1 SUBSCRIPT 2: 3

LEVEL 1 SUBSCRIPT 3: 5

But, since there is no data at ^TIP(5), it is not correct to reference each node to display its data. If I do so, like:

^TIP(1)="ONE"

^TIP(3)="THREE"

^TIP(3,3)="THIRTY-THREE"

^TIP(5,55)="FIFTY-FIVE"

ID=""

COUNT1=0

LOOP

ID=$ORDER(^TIP(ID))

IF ID <> "" THEN

COUNT1=COUNT1+1

CRT "LEVEL 1 SUBSCRIPT ":COUNT1:": ":ID

CRT "LEVEL 1 SUBSCRIPT ":COUNT1:" DATA: ":^TIP(ID)

END

UNTIL ID=""

REPEAT

and execute, I start ok but appropriately fail displaying ^TIP(5):

LEVEL 1 SUBSCRIPT 1: 1

LEVEL 1 SUBSCRIPT 1 DATA: ONE

LEVEL 1 SUBSCRIPT 2: 3

LEVEL 1 SUBSCRIPT 2 DATA: THREE

LEVEL 1 SUBSCRIPT 3: 5

<UNDEFINED> ^TIP(5)

File: BP Item: TSTORDER Line: 12

MVI: |"MV"|MVB.26 Line: 12

CRT "LEVEL 1 SUBSCRIPT ":COUNT1:" DATA: ":^

I need to use $DATA to determine if there is any data at that node:

^TIP(1)="ONE"

^TIP(3)="THREE"

^TIP(3,3)="THIRTY-THREE"

^TIP(5,55)="FIFTY-FIVE"

ID=""

COUNT1=0

LOOP

ID=$ORDER(^TIP(ID))

IF ID <> "" THEN

COUNT1=COUNT1+1

CRT "LEVEL 1 SUBSCRIPT ":COUNT1:": ":ID

CRT "LEVEL 1 SUBSCRIPT ":COUNT1:" $DATA: ":$DATA(^TIP(ID))

END

UNTIL ID=""

REPEAT

This reports:

LEVEL 1 SUBSCRIPT 1: 1

LEVEL 1 SUBSCRIPT 1 $DATA: 1

LEVEL 1 SUBSCRIPT 2: 3

LEVEL 1 SUBSCRIPT 2 $DATA: 11

LEVEL 1 SUBSCRIPT 3: 5

LEVEL 1 SUBSCRIPT 3 $DATA: 10

$DATA reports 1 to indicate that ^TIP(1) has data but no child nodes. It reports 11 to indicate that ^TIP(3) has data and child nodes. But it reports 10 to indicate that ^TIP(5) has no data, BUT there is data in at least one child node. So, we should only attempt to display data where $DATA returns a 1 or 11.

^TIP(1)="ONE"

^TIP(3)="THREE"

^TIP(3,3)="THIRTY-THREE"

^TIP(5,55)="FIFTY-FIVE"

ID=""

COUNT1=0

LOOP

ID=$ORDER(^TIP(ID))

IF ID <> "" THEN

COUNT1=COUNT1+1

CRT "LEVEL 1 SUBSCRIPT ":COUNT1:": ":ID

IF $DATA(^TIP(ID)) = 1 OR $DATA(^TIP(ID)) = 11 THEN ;* HAVE DATA HERE

CRT "LEVEL 1 SUBSCRIPT ":COUNT1:" DATA: ":^TIP(ID)

END

UNTIL ID=""

REPEAT

This displays all data with a single subscript and avoids the above error:

LEVEL 1 SUBSCRIPT 1: 1

LEVEL 1 SUBSCRIPT 1 DATA: ONE

LEVEL 1 SUBSCRIPT 2: 3

LEVEL 1 SUBSCRIPT 2 DATA: THREE

LEVEL 1 SUBSCRIPT 3: 5

To also display data for nodes with two subscripts, we need to $ORDER the second subscript when $DATA reports 10 or 11 for the first subscript value. This program uses $ORDER to display data for our both of our subscript levels.

^TIP(1)="ONE"

^TIP(3)="THREE"

^TIP(3,3)="THIRTY-THREE"

^TIP(5,55)="FIFTY-FIVE"

ID=""

LOOP

ID=$ORDER(^TIP(ID))

IF ID <> "" THEN ;* HAVE NEXT UNIQUE FIRST SUBSCRIPT

IF $DATA(^TIP(ID)) = 1 OR $DATA(^TIP(ID)) = 11 THEN ;* HAVE DATA HERE

CRT "^TIP(":ID:") = ":^TIP(ID)

END

IF $DATA(^TIP(ID)) = 10 OR $DATA(^TIP(ID)) = 11 THEN * HAVE CHILD WITH DATA

ID2=""

LOOP

ID2=$ORDER(^TIP(ID,ID2))

IF ID2<> "" THEN * HAVE NEXT SECOND SUBSCRIPT

IF $DATA(^TIP(ID,ID2)) = 1 OR $DATA(^TIP(ID,ID2)) = 11 THEN * HAVE DATA HERE

CRT "^TIP(":ID,",":ID2:") = ":^TIP(ID,ID2)

END

UNTIL ID2=""

REPEAT

END

UNTIL ID=""

REPEAT

It reports:

^TIP(1) = ONE

^TIP(3) = THREE

^TIP(3 ,3) = THIRTY-THREE

^TIP(5 ,55) = FIFTY-FIVE

This corresponds to the COS ZWrite result:

MV:[ZW ^TIP

^TIP(1)="ONE"

^TIP(3)="THREE"

^TIP(3,3)="THIRTY-THREE"

^TIP(5,55)="FIFTY-FIVE"

MV:

$ORDER accepts an optional second argument that specifies the subscript order in which to traverse the target array. It is an expression that resolves to either:

1 = ascending subscript order (the default) or

-1 = descending subscript order.

$ORDER can also accept a third argument that $ORDER will assign the data value of the next node it finds, if any.

MV:[ZW ^TEMP

^TEMP(1)="ONE"

^TEMP(2)="TWO"

^TEMP(2,2)="TWO TWO"

MV:

MV:;CRT $ORDER(^TEMP(""),,target)

MVBasic Syntax Error: Malformed expression

MV:;CRT $ORDER(^TEMP(""),1,target)

MV:;CRT target

ONE

MV:;CRT $ORDER(^TEMP(1),1,target)

MV:;CRT target

TWO

MV:;CRT $ORDER(^TEMP(2),1,target)

MV:;CRT $ORDER(^TEMP(2,""),1,target)

MV:;CRT target

TWO TWO

MV:

I can use the third $ORDER argument to change the above program to avoid referencing the global a second time to get its data. This program returns the same result as the one above.

^TIP(1)="ONE"

^TIP(3)="THREE"

^TIP(3,3)="THIRTY-THREE"

^TIP(5,55)="FIFTY-FIVE"

ID=""

LOOP

ID=$ORDER(^TIP(ID),1,DATA) ;* SAVE DATA, IF DEFINED

IF ID <> "" THEN * HAVE NEXT UNIQUE FIRST SUBSCRIPT

IF $DATA(^TIP(ID)) = 1 OR $DATA(^TIP(ID)) = 11 THEN * HAVE DATA HERE

CRT "^TIP(":ID:") = ":DATA ;* RETURNED BY $ORDER

END

IF $DATA(^TIP(ID)) = 10 OR $DATA(^TIP(ID)) = 11 THEN * HAVE CHILD WITH DATA

ID2=""

LOOP

ID2=$ORDER(^TIP(ID,ID2),1,DATA) ;* SAVE DATA, IF DEFINED

IF ID2<> "" THEN * HAVE NEXT SECOND SUBSCRIPT

IF $DATA(^TIP(ID,ID2)) = 1 OR $DATA(^TIP(ID,ID2)) = 11 THEN * HAVE DATA HERE

CRT "^TIP(":ID,",":ID2:") = ":DATA ;* RETURNED BY $ORDER

END

UNTIL ID2=""

REPEAT

END

UNTIL ID=""

REPEAT

Since simple MultiValue files have only a single subscript level, it is straightforward to use $ORDER as you might use SELECT and READNEXT to process all records in a file. Here is such a MVBasic program (READTIPS):

ID=""

LOOP

ID=$ORDER(^TIPS(ID),1,REC)

IF ID<>"" THEN CRT ID:": ":REC

UNTIL ID=""

REPEAT

And here is what it does:

MV:[ZW ^TIPS

^TIPS=$lb(0)

^TIPS(1)="JOHN SMITHþ123"

^TIPS(3)="JOHN PUBLICþ456"

MV:READTIPS

1: JOHN SMITHþ123

3: JOHN PUBLICþ456

MV:

Obviously, you can do what you want with the data, rather than just print it.

$ORDER can be abbreviated $O.

The last COS statement added to MVBasic that I will discuss this week is $MERGE. Unlike the $functions mentioned so far, this is a MVBasic command. It merges one array (source) into another (destination), replacing the values at matching nodes and creating new nodes and values. The syntax is:

$MERGE destination=source

Here is sample program TSTMERGE that illustrates this.

$KILL ^TEMP1,^TEMP2

^TEMP1(1)="ONE"

^TEMP1(2)="TWO"

^TEMP1(2,2)="TWO TWO"

^TEMP2(2)="NEW"

^TEMP2(5)="FIVE"

$MERGE ^TEMP1=^TEMP2

$XECUTE "ZW ^TEMP1"

Here is the result:

MV:TSTMERGE

^TEMP1(1)="ONE"

^TEMP1(2)="NEW"

^TEMP1(2,2)="TWO TWO"

^TEMP1(5)="FIVE"

MV:

Note that the value of node (2) is changed and node (5) is created.

In summary, these functions, combined with the enhanced ability of MVBasic to assign and reference Caché globals, make it convenient to add globals to your MVBasic programs.

Note *1:

On most legacy MV platforms (such as D3) there is no subscript 0. But on Caché the 0 subscript *is* there in all emulations, if you want to use it. The 0 subscript does not get assigned by a MATREAD.

Note *2:

Actually, every variable in Caché is an array with an unsubscripted root. This might be important if you use the same variable names with and without subscripts, and also use the $KILL command. For example, if you do the following, the $KILL kills both the root A and all the numbered nodes.

DIM A(5), AA(5)

A(5)=2

A=1 * un-subscripted assignment does not affect the subscripted values

AA(5)=5

S=2

CRT $DATA(S)

CRT $DATA(A)

CRT $DATA(AA)

CRT $ORDER(A(""))

$KILL A

$DATA(S) reports 1 (S contains data but no subscript nodes).

$DATA(A) reports 11 (A contains data and subscript nodes).

$DATA(AA) reports 10 (AA contains only subscript nodes).

$ORDER(A("")) reports 5 (the first subscript value of the first node).

$ORDER(AA("")) reports 5 (the first subscript value of the first node).

/Michael

Michael Cohen

unread,

Jan 7, 2013, 10:18:53 PM1/7/13

to InterSy...@googlegroups.com

Google seems to have truncated the bottom of my Post, so here it is...

Reply all

Reply to author

Forward

0 new messages