Global MUMPS: Extreme Database Programming. Part 2

Start see part 1 .

Chapter 2. SQL / relational databases versus MUMPS

This chapter will outline the main differences between regular SQL relational databases and MUMPS-based databases.

Read chapter 1 if you need to better understand what globals are and how to manipulate them.

Define data structures

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

1. Customer table (CUSTOMER)
2. Order table (ORDER)
3. Table with a list of things that make up an individual order (ITEM)



The table names are shown in bold, primary keys are underlined.

CUSTOMER
CustNo unique customer number
name customer name
address customer address
totalOrders total number of customer orders

the ORDER
OrderNo Order No.
CustNo Customer number (foreign key of the CUSTOMER table)
orderDate Order Date
invoiceDate date of the invoice
totalValue Order value

ITEM
orderNo Order number (corresponding to the key from the ORDER table).
itemNo Item number
price 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 for a specific client (CUSTOMER.totalOrders) is the total number of orders placed by the client 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 ITEM.price field.

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 using SQL, you can add, modify, and retrieve records.

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

Nevertheless, it is important to note that the relational schema can be transparently superimposed on the MUMPS storage in order to access data through SQL tools. A relational schema can be added to an existing MUMPS repository when it becomes necessary, providing access to records in a normalized form (if the structures lend themselves to normalization).

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

Table CUSTOMER

^ CUSTOMER (custNo) = name | address | totalOrders

Table ORDER

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

Table ITEM

^ ITEM (orderNo, itemNo) = price

The relationship 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 global names. You also have the choice: use 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 teaching 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 the globals (you can select any other character).

Adding 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 a character for gluing (concatenating) strings.

On the right side we entered 2 fields separated by a tilde symbol. By the way, any character can be used as a separator, including non-printable characters.

We could write:

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

Function $ c (1) means "ASCII character whose value is 1".
$ c is the short name for 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 substituted 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

Fetching records from an SQL database

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 on using the $ get () function. This is a convenient way to extract values ​​from globals. If the requested item does not exist, then $ get () will return null ("").

If we had not used $ 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 runtime error (i.e. data is not defined).

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

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

Removing a record from an SQL database

DELETE FROM CUSTOMER A WHERE A.custNo = :custNo

MUMPS

kill ^CUSTOMER(custNo)

Note that this simple example does not yet contain checks that allow you to maintain the logical integrity of the database. Next we will show how to do it.

Fetching multiple SQL records

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

MUMPS

s custNo=”” f s 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)
. ; добавьте свой код для обработки текущей строки

Note that we use dot syntax (dot-syntax). Lines starting with dots represent a subprogram called by the do command (see the end of the first line).

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

The $ order function in MUMPS is one of the pillars of the power and flexibility of globals. The essence of its work is usually generally incomprehensible to those who are only familiar 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 whose keys 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 that 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, to reuse and eliminate code redundancy, the MUMPS commands shown above should 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 parameter of the data function. This is a link call. 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, 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 OOP terms, this corresponds to public. We can refer to it, even if it is contained in another program. External functions are a kind of data access method.

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 (i.e. true). You can check the ok variable to check whether the save is complete or not.

Since we have a calculated field totalOrders in the CUSTOMER table, we will make its 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 to discuss computed 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 string of the specified client:

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

Delete a record from the database

The following external function will delete 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.