May 7, 2013 at 08:55

MUMPS Globals: Extreme Database Programming. Part 3

Original author: Rob Tweed
  • Transfer
  • Tutorial
Rob Tweed
Start see part 1 , part 2 .

Secondary indexes

In relational databases, secondary indexes are usually set when defining tables, or after using ALTER TABLE. If an index is defined, then it is automatically created, and then maintained and recalculated by the database when the data changes.

In MUMPS indexes are served explicitly by the programmer, for example, in the table update function.

Due to the hierarchical nature of the MUMPS store, the field with the primary index is used as a key.

For example, consider the MUMPS function to add a new line to ORDER:
setOrder(orderNo,data) ;
new rec,itemNo,ok
if orderNo="" Quit 0
; Вычислим общую стоимость заказа
set data("totalValue")=0
set itemNo=""
for set itemNo=$Order(^ITEM(orderNo,itemNo)) Quit:itemNo="" do
. set ok=$$getItem(orderNo,itemNo,.itemData)
. Set data("totalValue")=data("totalValue")+itemData("price")
set rec=data("custNo")_"~"_data("orderDate")_"~"_data("invoiceDate")
set rec=rec_"~"_data("totalValue")
set ^ORDER(orderNo)=rec
Quit 1


Pay attention to the code to calculate the total cost of the order. We go through all the entries from ITEM for a specific order number orderNo and use the $$ getItem () function to get the value of each item. The code for the $$ getItem () function is as follows:

getItem(orderNo,itemNo,itemData)
kill itemData
s itemData("price")=0
if orderNo="" Quit 0
if itemNo="" Quit 0
if ‘$data(^ITEM(orderNo,itemNo)) Quit 0
set itemData("price")=^ITEM(orderNo,itemNo)
Quit 1


Look at the deadline that checks the existence of an item in the global for a specific order number and item number. It uses the MUMPS function $ data and the negation operator the single quote `.

Let's add an index for quick access to each customer’s purchases.

To store the index, create a new global ^ ORDERX1. We will save a couple of keys in the global: customer number (custNo) and order number (orderNo).

To make the index (custNo, orderNo) work, we expand the setOrder function as follows:

setOrder(orderNo,data) ;
new rec,itemNo,ok
if orderNo="" Quit 0
; Посчитаем стоимость заказа
set data("totalValue")=0
set itemNo=""
for set itemNo=$Order(^ITEM(orderNo,itemNo)) Quit:itemNo="" do
. set ok=$$getItem(orderNo,itemNo,.itemData)
. Set data("totalValue")=data("totalValue")+itemData("price")
set rec=data("custNo")_"~"_data("orderDate")_"~"_data("invoiceDate")
set rec=rec_"~"_data("totalValue")
set ^ORDER(orderNo)=rec
if data("custNo")`="" set ^ORDERX1(data("custNo"),orderNo)=""
Quit 1


To create an order, we will use this function as follows:
set orderNo=21
kill data
set data("custNo")=101
set data("orderDate")="4/5/2003"
set data("invoiceDate")="4/7/2003"
set ok=$$setOrder(orderNo,.data)


Referential Integrity

Maintaining referential integrity seems to be the most famous thing of all referential actions in databases.
Reference actions include all the actions that must be done with one table due to the fact that any other tables have changed.

The process of maintaining referential integrity is responsible for maintaining semantic (in the original, semantic) integrity between related tables. In particular, this is due to the maintenance of relationships between tables based on NATURAL JOIN (different tables have columns that store values ​​of the same type by which the relationship between tables is built) or primary / foreign keys.

For example, when the customer number CUSTOMER.custNo is changed or deleted, in order to maintain semantic integrity between the customer table and the order table, an appropriate change of the ORDER.custNo field should be made.

Similarly, when the order number ORDER.orderNo is changed (or deleted), then in order to maintain semantic integrity between the table with orders and the table of things (of which orders are made), the corresponding change should be made with the ITEM.orderNo field.

In relational databases (RDBMS), integrity rules are specified when creating tables using primary and foreign keys. In MUMPS, these rules can be implemented right inside our functions.

Bearing in mind the relationship between customer tables and their orders, the CUSTOMER update operation will be like this:

SQL

UPDATE CUSTOMER A
SET custNo = :newCustNo
WHERE A.custNo = :oldCustNo

As a result of the request, the corresponding entries in the ORDER table will be updated (by CUSTOMER.custNo = ORDER.custNo link), if the corresponding foreign keys were correctly set when defining the database structure.

MUMPS

updateCustomer(oldCustNo,newCustNo,newData) ;
new result,orderData,orderNo
if (oldCustNo="")!(newCustNo="") Quit 0
set orderNo=""
for set orderNo=$order(^ORDERX1(oldCustNo,orderNo)) Quit:orderNo="" do
. set result=$$getOrder(orderNo,.orderData)
. set orderData("custNo")=newCustNo
. set ok=$$setOrder(orderNo,.orderData)
set ok=$$setCustomer(newCustNo,.newData)
if newCustNo`=oldCustNo set ok=$$deleteCustomer(oldCustNo)
Quit 1


Note that this code for the most part consists of the functions we have already created for serving the CUSTOMER and ORDER tables. In MUMPS, just reuse the code.

Now create the $$ getOrder function:

getOrder(orderNo,orderData) ;
new record
if (orderNo="") Quit 0
set record=$g(^ORDER(orderNo))
set orderData("custNo")=$piece(record,"~",1)
set orderData("orderDate")=$piece(record,"~",2)
set orderData("invoiceDate")=$piece(record,"~",3)
set orderData("totalValue")=$piece(record,"~",4)
Quit 1

We also need to extend our original simple function $$ deleteCustomer (). Similar considerations apply to row deletion from a client table.

The SQL query and its equivalent in M ​​are shown below:

SQL

DELETE FROM CUSTOMER A
WHERE A.custNo = :custNo

As a result of this request, the corresponding order records will be deleted from the ORDER table (according to the relationship CUSTOMER.custNo = ORDER.custNo and the integrity rules defined when creating the database schema). Integrity rules can be set using foreign keys, for example.

MUMPS

deleteCustomer(custNo) ;
new orderNo
if custNo="" Quit 0
set orderNo=""
for set orderNo=$order(^ORDERX1(custNo,orderNo)) Quit:orderNo="" do
. set result=$$deleteOrder(custNo,orderNo)
kill ^CUSTOMER(custNo)
Quit 1


For the code above, you need the $$ deleteOrder () function. Here she is:

deleteOrder(custNo,orderNo) ;
kill ^ITEM(orderNo) ; Удалим все вещи входящие в заказ
kill ^ORDER(orderNo) ; Удалим заказ
kill ^ORDERX1(custNo,orderNo) ; Удалим связь между клиентом и номером заказа, используемую как вторичный индекс
Quit 1

Note that records about all things included in a particular order are deleted with just one KILL command on the index with the order number. This is because there is a cascading relationship between tables with customers and their order numbers (in SQL terms).

If when deleting a client it is necessary to save information about his orders, breaking the connection between the client and his orders, then the deleteCustomer function will take the following form:

deleteCustomer(custNo) ;
new orderNo,result,orderData
if custNo="" Quit 0
set orderNo=""
for set orderNo=$order(^ORDERX1(custNo,orderNo)) Quit:orderNo="" do
. set result=$$getOrder(orderNo,.orderData)
. set orderData("custNo")=""
. set result=$$setOrder(orderNo,.orderData)
kill ^CUSTOMER(custNo)
Quit 1

Similar logic should be applied to data stored in the ITEM table when the order number ORDER.orderNo in the ORDER table is changed or deleted.

Triggers

Triggers are an easy way to call some predefined code when certain conditions are met inside the database. Usually triggers are triggered when some information in the database changes.

In relational databases, triggers are defined at the database schema level. In MUMPS, we can place any trigger code in functions that serve the database.

Take, for example, the number of orders for a particular customer, CUSTOMER.totalOrders. We must define a trigger to automatically update this field when adding or removing an order from the ORDER table.

SQL

This SQL expression must be included in the trigger code for the ORDER table in order to ensure the correct value in CUSTOMER.totalOrders:

SELECT COUNT(A.orderNo)
FROM A.ORDER
WHERE A.custNo = :custNo


This request will be triggered on the insert and delete operations of rows in the ORDER table (according to the relationship CUSTOMER.custNo = ORDER.custNo)

MUMPS

We simply add the following (trigger) code to the row insert function for the ORDER table:

setOrder(orderNo,data) ;
new rec,itemNo,ok
if orderNo="" Quit 0
; Подсчитаем общую стоимость вещей в заказе
set data("totalValue")=0
set itemNo=""
for set itemNo=$Order(^ITEM(orderNo,itemNo)) Quit:itemNo="" do
. set ok=$$getItem(orderNo,itemNo,.itemData)
. Set data("totalValue")=data("totalValue")+itemData("price")
set rec=data("custNo")_"~"_data("orderDate")_"~"_data("invoiceDate")
set rec=rec_"~"_data("totalValue")
set ^ORDER(orderNo)=rec
; Сохраним информацию для нашего вторичного индекса
if data("custNo")`="" set ^ORDERX1(data("custNo"),orderNo)=""
;
; Обновим значения поля CUSTOMER.totalOrders
new custData
Set ok=$$getCustomer(data("custNo"),.custData)
; При сохранении клиента будет автоматически пересчитано CUSTOMER.totalOrders. См. определение функции setCustomer в предыдущей части статьи
Set ok=$$setCustomer(data("CustNo"),.custData)
;
Quit 1

The same considerations apply when deleting rows from an ORDER table. A similar scheme can be used to automatically update the order value ORDER.totalValue when adding things to the order.

SQL

The following SQL code must be placed inside the trigger for the ITEM table to calculate the new order value ORDER.Value:

SELECT SUM(A.price)
FROM A.ITEM
WHERE A.orderNo = :orderNo

The request will be triggered for all insert and delete operations on the ITEM table (according to the relationship ORDER.orderNo = ITEM.orderNo).

MUMPS

Add the following (trigger) code to the row insert function with the ITEM table:

setItem(orderNo,itemNo,data) ;
new ok
if (orderNo="")!(itemNo="") Quit 0
set ^ITEM(orderNo,itemNo)=data("price")
set^ORDERX1(custNo,orderNo)=""
; Обновим значение поля ORDER.totalValue
set ok=$$getOrder(orderNo,.orderData)
; При сохранении заказа поле ORDER.totalValue будет пересчитано
set ok=$$setOrder(orderNo,.orderData)
Quit 1

The same considerations apply to operations to delete rows from an ITEM table.
The next example of using triggers in our database will be the automatic generation of an invoice for a client as soon as its creation date is stored in the ORDER.invoiceDate field. We can very simply add this functionality to our ORDER table update procedure:

setOrder(orderNo,data) ;
new rec,itemNo,ok
if orderNo="" Quit 0
; Подсчитаем стоимость заказа
set data("totalValue")=0
set itemNo=""
for set itemNo=$Order(^ITEM(orderNo,itemNo)) Quit:itemNo="" do
. set ok=$$getItem(orderNo,itemNo,.itemData)
. Set data("totalValue")=data("totalValue")+itemData("price")
set rec=data("custNo")_"~"_data("orderDate")_"~"_data("invoiceDate")
set rec=rec_"~"_data("totalValue")
set ^ORDER(orderNo)=rec
if data("custNo")`="" set ^ORDERX1(data("custNo"),orderNo)=""
;
; Обновим поле CUSTOMER.totalOrders
new custData
Set ok=$$getCustomer(data("custNo"),.custData)
Set ok=$$setCustomer(data("CustNo"),.custData)
;
; Генерируем счёт-фактуру, если присутствует его дата
if Data("invoiceDate")`="" Set Result=$$invoiceOrder(orderNo)
;
Quit 1

Of course, the $$ invoiceOrder () function must be written in order to carry out all the necessary steps to generate an invoice.

Transactions A

complete database update often consists of many updates to a number of tables. All these related updates must be guaranteed to be completed before the main update (or transaction) can be considered completed.

As a rule, transactions are enabled by default in relational databases. In other words, changes to the database are not recorded until the modifying process issues a COMMIT command, after which all changes are confirmed and a new transaction begins.

MUMPS is very similar in this respect, except that transactions must be explicitly included. The program must necessarily execute the command to start the transaction TSTART, and not just confirm its completion by TCOMMIT.

SQL

Confirm the current transaction and (implicitly) run a new one:

COMMIT

MUMPS

Start a new transaction:

TSTART

Confirm Transaction:

TCOMMIT

If transaction management is not explicitly enabled in the MUMPS system, then all updates will be immediately applied to the current repository. In other words, any SET or KILL command on the global or its element can be considered as a completed transaction.

Conclusions

There are many key advantages to using MUMPS over traditional relational databases.

  • Code reuse and ease of maintenance. With careful programming, an extremely high level of code reuse can be achieved. The above examples show how you can encapsulate all actions with the database within the set, get and delete functions for each table. Write these functions once, and then use them as you want.
  • Portability. Data structure definitions are contained within functions that work with them. There is no difference between the definition of data and its implementation.
  • Flexibility. The code that updates the data can be changed to trigger on any action or event within the system.
  • Performance and optimization. Experienced MUMPS analysts will see opportunities for optimizing the functions we use to work with the database. This is possible because the definition of data and the implementation of operations on them are contained together within specific functions. An analyst using SQL does not have precise control over how triggers, referential integrity actions, and transactions are handled.
  • The advantages of using SQL to work or retrieve data (you can use special utilities for this) are not lost, because the data definition layer can be transparently superimposed on existing MUMPS tables (globals). This can be done later. For example, Caché SQL and the third-party utility KB_SQL implement a full-fledged SQL environment on top of MUMPS globals.
Tags:

Also popular now: