Globals MUMPS: Extreme Database Programming. Part 2

Start see part 1 .

Chapter 2. SQL / relational databases against MUMPS

This chapter will outline the main differences between ordinary SQL relational databases and a MUMPS database.
')
Read Chapter 1 if you need to better understand what globals are and how they are manipulated.

Define data structures

Let's start with the basics - we define the data. For example, we will use a simple database consisting of 3 tables:

1. Customer table (CUSTOMER)
2. Order table (ORDER)
3. A table with a list of items constituting an individual order (ITEM)



Table names are in bold, primary keys are underlined.

Custom
custNo Unique customer number
name Client name
address Customer Address
totalOrders Total number of sales orders

ORDER
orderNo Order Number
custNo Client number (foreign key from the CUSTOMER table)
orderDate Order Date
invoiceDate Invoice Date
totalValue Order Cost

ITEM
orderNo Order number (corresponding to the key from the ORDER table).
itemNo Item Article
price The price of the item for the client (including all discounts).

The one-to-many relationship is shown in the diagram. Each customer can have many orders and each order can consist of many things.

The number of orders of a specific customer (CUSTOMER.totalOrders) is the total number of orders placed by the customer in the ORDERS table, which are identified by its number.

The order price (ORDER.totalValue) is the sum of the cost of all things in the order, each specific cost is determined by the field ITEM.price.

The fields CUSTOMER.totalOrders and ORDER.totalValue are not entered directly by the user - these are calculated fields.

For SQL / relational databases, these table definitions must be loaded into the database (using CREATE TABLE) before you can add, modify, and retrieve records using SQL.

MUMPS does not impose the use of table definitions before using them, and therefore rows can be written directly to the repository without being formally defined.

However, it is important to note that the relational schema can be transparently overlaid on MUMPS storage for accessing data through SQL tools. A relational schema can be added to an existing MUMPS repository when it becomes necessary, providing access to the records in a normalized form (if the structures are normalizable).

These three tables will be presented in MUMPS using the following globals:

CUSTOMER table

^ CUSTOMER (custNo) = name | address | totalOrders

ORDER table

^ ORDER (orderNo) = custNo | orderDate | invoiceDate | totalValue

ITEM table

^ ITEM (orderNo, itemNo) = price

The link between CUSTOMER and ORDER will be represented using the global:

^ ORDERX1 (custNo, orderNo) = ””

He will provide order numbers by customer number.

In MUMPS, you can use any globals name. You also have the choice of using one global for each table (as we did) or the same global for several or all tables.

For example, we could use one global for our entire structure:

^OrderDatabase(“customer”,custNo)= name_”~”_address_”~”_totalOrders ^OrderDatabase(“order”,orderNo)= custNo_”~”_orderDate_”~”_invoiceDate_”~”_totalValue ^OrderDatabase(“item”,orderNo,itemNo)=price ^OrderDatabase(“index1”,custNo,orderNo)=”” 

For the training purposes of this article, we chose to use the global per table.

We also chose to use the tilde character (~) as a field separator in globals (you can select any other character).

Add a record to the database

Let's start with a very simple example. Add a new client to the CUSTOMER table.

SQL

 INSERT INTO CUSTOMER (CustNo, Name, Address) VALUES (100, 'Chris Munt', 'Oxford') 

MUMPS

 Set ^CUSTOMER(100)= “Chris Munt”_"~"_"Oxford" 

"_" Is the symbol for sticking (concatenating) strings.

On the right side, we entered 2 fields separated by a tilde symbol. By the way, you can use any symbol as a separator, including non-printable symbols.

We could write:

 Set ^CUSTOMER(100)= “Chris Munt”_$c(1)_"Oxford" 

The function $ c (1) means "ASCII character whose value is 1".
$ c is the abbreviated name of the $ char function.

And in this case, the ASCII 1 character would be used to separate the field.

Of course, in a real situation, the data that is inserted into the INSERT request (or into the MUMPS command) is stored in variables.

SQL

 INSERT INTO CUSTOMER (custNo, name, address) VALUES (:custNo, :name, :address) 

Note translator: in ANSI SQL, the preceding colon is used to specify variables.

MUMPS

 Set ^CUSTOMER(custNo)=name_"~"_address 

Selection of records from the database

SQL

 SELECT A.name, A.address FROM CUSTOMER A WHERE A.custNo = :custNo 

MUMPS

 Set record=$get(^CUSTOMER(custNo)) Set name=$piece(record,"~",1) Set address=$piece(record,"~",2) 

A note about using the $ get () function. This is a convenient way to extract values ​​from globals. If the requested item does not exist, $ get () will return null ("").

If we did not use $ get (), then we would have to do this:

 Set record=^CUSTOMER(custNo) 

If the requested global element does not exist, then MUMPS will return a run-time error (that is, no data is defined).

Like most commands and functions in MUMPS, instead of $ get (), you can use the abbreviation $ g ():

 Set record=$g(^CUSTOMER(custNo)) 

Deleting a record from the database

SQL

 DELETE FROM CUSTOMER A WHERE A.custNo = :custNo 

MUMPS

 kill ^CUSTOMER(custNo) 

Note that this simple example does not yet contain checks that preserve the logical integrity of the database. Further we will show how this is done.

Selection of several records

SQL

 SELECT A.custNo, A.name, A.address FROM CUSTOMER A 

MUMPS

 s custNo=”” fs custNo=$order(^CUSTOMER(custNo)) Quit:custNo= “” do . Set record=$get(^CUSTOMER(custNo)) . Set name=$piece(record,"~",1) . Set address=$piece(record,"~",2) . Set totalOrders=$piece(record,"~",3) . ;        

Notice that we use dotted syntax. Lines starting with dots are a subroutine called by the do command (see the end of the first line)

You can do everything you need with each line inside the subroutine, as the commentary shows (the last line starting with ";")

The $ order function in MUMPS is one of the pillars of global power and flexibility. The essence of its work is usually not clear for those who are familiar only with SQL and relational databases, so read more about it in Chapter 1 .

With the $ order function and globals, we can bypass any rows in the table, the keys to which begin and end with any values. It is important to understand that globals are a hierarchical repository. We emulated the key in the table through the index in the global, so we do not have access to the rows in the sequence in which they were created: the $ order function can be applied to each index (key) of the global independently.

Using MUMPS functions for high-level data access

In practice, for reuse and elimination of redundancy code, the MUMPS commands shown above must be converted to functions. Examples of such functions are shown below.

Adding a new record to the database

 setCustomer(custNo,data) ;    If custNo="" Quit 0 Set ^CUSTOMER(custNo)=data("name")_"~"_data("address") Quit 1 

This function can be called in the following way:

 kill data ;    data (    RAM) set data("name")="Rob Tweed" set data("address")="London" set custNo=101 set ok=$$setCustomer(custNo,.data) ; $$ ,    

Pay attention to the point before the data parameter of the function. This is a call by reference. data is a local array, not a simple variable, so we pass it to the function by reference.

The setCustomer function may be contained in another program (for example, in myFunctions). And since the programs in MUMPS are contained in globals, to call a function from the global, you need to write $$ setCustomer ^ myFunctions (custNo, .data)

Example:

 kill data ; clear down data local array set data("name")="Rob Tweed" set data("address")="London" set custNo=101 set ok=$$setCustomer^myFunctions(custNo,.data) 

The $$ setCustomer () function can be called extrinsic. In terms of OOP, this corresponds to public. We can refer to it, even if it is contained in another program. External functions are some kind of data access methods.

The $$ setCustomer () function returns zero (i.e., false) if we pass null as the client number. In other cases, the record will be saved and $$ setCustomer () will return 1 (that is, true). You can check the variable ok to check whether it is saved or not.

Since we have a totalOrders computed field in the CUSTOMER table, let's make it support in the code:

 setCustomer(custNo,data) ; if custNo="" Quit 0 ;    Set data(“totalOrders”)=0 Set orderNo=”” for set orderNo=$order(^ORDERX1(custNo,orderNo)) Quit:orderNo=”” do . set data(“totalOrders”)=data(“totalOrders”)+1 set ^CUSTOMER(custNo)=data("name")_"~"_data("address")_”~”_data(“totalOrders”) Quit 1 

We will continue the discussion of calculated fields later in the Triggers section.

Getting a record from the database

The following external function will return a row from the CUSTOMER table.

 getCustomer(custNo,data) ; new record kill data ; clear down data array if custNo="" Quit 0 set record=$get(^CUSTOMER(custNo)) set data("name")=$piece(record,"~",1) set data("address")=$piece(record,"~",2) set data("totalOrders")=$piece(record,"~",3) Quit 1 

This function can be used as follows:

 S custNo=101 set ok=$$getCustomer(custNo,.data) 

It will return a local array (or rather, change it, because it is passed by reference), containing 3 fields from the specified client string:

data (“name)
data (“address”)
data (“totalOrders”)

Deleting a record from the database

The following external function will remove a row from the CUSTOMER table:

 deleteCustomer(custNo) ; if custNo="" Quit 0 kill ^CUSTOMER(custNo) Quit 1 

This function can be used as follows:

 S custNo=101 S ok=$$deleteCustomer(custNo) 

Note Translator: In 3 parts we will talk about secondary indexes, triggers and transactions.

3rd part, ending.