DBMS Caché. Woodworking - SQL access to multidimensional data structures

“Caché gives programmers the freedom to choose how to store and access data through SQL objects, or through direct access to multidimensional data structures. Regardless of the access method, all data in Caché databases is stored in multidimensional arrays. "

Caché Technology Handbook



“Globals (global stored variables) are an abstraction of B-tree structures used in MUMPS for storing large amounts of data.”

A Universal NoSQL Engine, Using a Tried and Tested Technology



The article presents a couple of examples of creating SQL projections on various global structures.



For new stored classes, Caché automatically uses the standard storage method, CacheStorage . When might you need to create your own storage methods (CacheSQLStorage or CustomStorage)?

')

• When upgrading MUMPS (or switching from GlobalsDB ) applications built on globals. The global is often used as a non-formalized (dynamic) structure, the meaning of the nodes of which is determined by the agreement of the developers. Why not formalize these conventions (create a class description), connect a version control system for them and use all the advantages of OOP development?
• For a number of special tasks: classes with a large number of properties, their own projection classes on system globals.

Some theory

• CacheStorage - used by Caché when compiling new stored classes. Methods for working through SQL and objects are generated by the system.
• CacheSQLStorage - used to work with existing globals via SQL. Created by a programmer. In the object data access methods (% SaveData,% LoadData,% DeleteData), SQL is used, respectively, triggers are triggered. It is necessary to customize the SQL projection.
• CustomStorage - used to implement its own complex object access logic. Created by a programmer. You must independently implement the% SaveData,% LoadData,% DeleteData methods. You can customize the SQL projection.

For the CacheSQLStorage (or CustomStorage) storage scheme, a SQL projection (SQL storage map) is configured. The projection contains the necessary information for the compiler about the globals used, their keys (subscripts), the rules for passing through the keys and the location of the data. Based on this information, the compiler generates the necessary data access methods.

More practice

Let us take a look at a simple example from the article A Universal NoSQL Engine . The telephone directory uses two global ^ telephone - to store data and ^ nameIndex - to store the index by phone number owner.



An important point in creating an SQL projection is to establish the correspondence between the keys of the data global and the object identifier. In our case, this is the phone number - the first key ^ telephone .

• Create a new stored class data.phones (phone book), add the properties name , number , address and index by the property number with type IdKey .
• Add to the class the method of storing CacheSQLStorage with the name sql. Create a projection map to the data in the global telephone with the name data. We will begin to fill the map with the definition of global keys. We add the first key, we specify that the number property is stored in this key
• We proceed to the description of the location of property values. Add a projection to the address property. We indicate that the values ​​are stored in the node with the additional (second) key "address" . By default, the system assumes the storage of properties in a delimited string. In our case, this is not the case. Therefore, we remove the separator and the position number. We act in a similar way for the name property - the value is stored in the additional key “name” . Remove the separator and position number. Compile the class. Check the work of the projection - execute the Select query.
• Add a projection on the index global. Create an index by the name property. Add a map (with type index) on ^ nameIndex . Add a description of the keys - the first key is the name property, the second key is the property number . Compile the class. We look at the plan of the request for sampling from the telephone directory with filtering by the name field.

After setting up the storage, execute insert, update, delete requests to the data.phones class. Try using object access to class instances (% New,% Save,% DeleteId). Pay attention to the contents of the global after operations.



Consider a more complex example typical of legacy applications. At the entrance of the global view:

^ dish (3) = "CASH"

^ dish (3.315) = “Viscous rice porridge * 200 * 178.2 * 3.1 * 4.4 * 33.7 ** gr * 780”

^ dish (3.315.2) = "Water * 161 * 161"

^ dish (3,315,36) = "Rice groats * 43.6 * 43.6"

^ dish (3,315,50) = "Butter * 5 * 5"

^ dish (3.316) = “Viscous pearl barley porridge * 200 * 178.7 * 4.1 * 4.6 * 32.1 ** then * 4744”

^ dish (3.316.2) = "Water * 161 * 161"

^ dish (3,316,34) = "Pearl barley * 43.6 * 43.6"

^ dish (3,316,50) = “Butter * 5 * 5”

^ dish (3.317) = “Viscous oatmeal porridge * 200 * 206.5 * 5.8 * 6.9 * 32 ** then * 6282”

^ dish (3,317,2) = "Water * 157 * 157"

^ dish (3,317,33) = "Oatmeal * 49.0 * 49.0"

^ dish (3,317,50) = “Butter * 5 * 5”

^ dish (3,318) = "Oatmeal viscous porridge * 200 * 192.2 * 5.7 * 6.8 * 28.6 ** then * 2436"

^ dish (3.318.2) = "Water * 161 * 160"

^ dish (3,318,3) = "Hercules * 43.6 * 43.6"

^ dish (3,318,50) = “Butter * 5 * 5”

Objects of several classes are stored in one global: dish.kind - dish type, dish.dish - dish, dish.product - dish product. Moreover, the global structure determines the type of relationship between these classes - parent-child .





A feature of the projection setting for the dish.dish and dish.product classes is the syntax for describing global keys



Instead of the name of the property, the name of the parent of the class is indicated, and after the dot, the name of its property — the identifier.

Accelerated projection creation process:

Tips and secrets.

The configured projection is stored by Cache in instances of classes:

• % Dictionary.StorageDefinition - storage definition
• % Dictionary.StorageSQLMapDefinition - definition of data cards and indexes that make up the SQL projection
• % Dictionary.StorageSQLMapSubDefinition - global keys, order and rules for passing through them
• % Dictionary.StorageSQLMapDataDefinition - Property Location
• % Dictionary.StorageSQLMapRowIdSpecDefinition - to indicate the correspondence between an object identifier and a row identifier. Specify to the compiler which keys are included in the string identifier.

Accordingly, storage can be created programmatically. For example, make your own storage creation wizard . In Studio, the configured storage method is displayed as xml (in Cache 2013.1 automatically, and in earlier versions through the menu: View - Show Storage ).



For each stored class, Caché creates an Extent request and generates programs for its execution. The generated class code can help in setting up or debugging the projection (see labels zExtentExecute, zExtentFetch).



Interesting examples of projections can be found in the Caché system classes. For example, the above storage scheme classes are a projection on the global.



For more information on setting up projections, please read the lecture by Vadim Fedorov from the Intersystems School of Innovation.