Globals MUMPS: Extreme Database Programming. Part 3

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 storage, the primary index field is used as a key.

For example, consider the MUMPS function for adding a new line to the 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 

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

 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 for 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 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 keep a pair of keys in the global: customer number (custNo) and order number (orderNo).

To make the index (custNo, orderNo) expand the function setOrder 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 well-known thing of all referential actions in databases.
Reference actions include all actions that need to be performed on the same tables due to the fact that some other tables have changed.

The process of maintaining referential integrity is responsible for maintaining the semantic (in the original - semantic) integrity between related tables. In particular, this is related to maintaining relationships between tables based on NATURAL JOIN (different tables have columns that store values ​​of the same type, according to 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 the semantic integrity between the customer table and the order table, the corresponding change of the ORDER.custNo field should be made.

Similarly, when the order number ORDER.orderNo changes (or is deleted), in order to maintain the semantic integrity between the order table and the things table (of which the orders consist), the corresponding change must 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 update operation CUSTOMER will be:

SQL

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

As a result of the query, the corresponding entries in the ORDER table will be updated (by the link CUSTOMER.custNo = ORDER.custNo), if the corresponding foreign keys were correctly specified 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 mostly consists of the functions we have already created for servicing 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 $$ deleteCustomer () function. Similar considerations apply to deleting rows 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 query, 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 

The code above requires the $$ deleteOrder () function. Here she is:

 deleteOrder(custNo,orderNo) ; kill ^ITEM(orderNo) ;       kill ^ORDER(orderNo) ;   kill ^ORDERX1(custNo,orderNo) ;       ,     Quit 1 

Note that the records of 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 cascade connection (in SQL terms) between the tables with customers and their order numbers.

If, when you delete a customer, you need to save information about his orders, breaking the link between the customer and his orders, then the deleteCustomer function will look like this:

 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 the data stored in the ITEM table when the order number ORDER.orderNo is changed or deleted in the ORDER table.

Triggers

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

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

Take, for example, the number of orders of 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 statement must be included in the trigger code for the ORDER table in order to provide the correct value in CUSTOMER.totalOrders:

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

This query will be triggered by inserting and deleting rows in the ORDER table (according to the link CUSTOMER.custNo = ORDER.custNo)

MUMPS

We simply add the following (trigger) code to the 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 the ORDER table. Such a scheme can be used to automatically update the cost of the order ORDER.totalValue when adding things to the order.

SQL

The following SQL must be placed inside a 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 query will be triggered for all insert and delete operations on the ITEM table (according to the ORDER.orderNo = ITEM.orderNo connection).

MUMPS

Add the following (trigger) code to the insert function of rows 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 remove rows from the 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 saved in the ORDER.invoiceDate field. We can very simply add this functionality to our procedure for updating 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) Set ok=$$setCustomer(data("CustNo"),.custData) ; ;  -,     if Data("invoiceDate")`="" Set Result=$$invoiceOrder(orderNo) ; Quit 1 

Of course, the $$ invoiceOrder () function must be written to take 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 complete.

As a rule, in relational databases, transactions are enabled by default. 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 for the fact that transactions must be explicitly included. The program must execute the start command of the TSTART transaction, and not just confirm its completion with TCOMMIT.

SQL

We confirm the current transaction and (implicitly) start 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.

findings

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

• Code reuse and ease of maintenance. With careful programming, you can achieve an extreme high level of code reuse. The above examples show how you can encapsulate all database actions within the set, get, and delete functions for each table. Write these functions once, and then use them as you like.
• 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 to optimize the functions we used to work with the database. This is possible because the definition of data and the implementation of operations on it 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 extract 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 complete SQL environment on top of the MUMPS globals.