1 / 26

dcDB Stored Procedures: An Overview

DivConq Framework’s MUMPS API. dcDB Stored Procedures: An Overview. Connectivity. Linux, Windows or OS X Box. Linux Box. Java App w/ DivConq. MUMPS. SSH. Java code connects to the MUMPS database via an SSH connection. This keeps the data secure while in transit. .

arch
Download Presentation

dcDB Stored Procedures: An Overview

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DivConq Framework’s MUMPS API dcDB Stored Procedures:An Overview

  2. Connectivity Linux, Windows or OS X Box Linux Box Java App w/ DivConq MUMPS SSH Java code connects to the MUMPS database via an SSH connection. This keeps the data secure while in transit.

  3. Local Connectivity Linux Box Java App w/ DivConq MUMPS SSH Of course there is no reason why Java cannot run on the same box. SSH is still used, connecting over the loopback network interface.

  4. Channels Linux, Windows or OS X Box Linux Box Java App w/ DivConq MUMPS Session Channels One SSH session is used. Multiple SSH channels enable greater throughput. Typically DivConq uses three channels, which creates three MUMPS processes.

  5. Request-Response Linux, Windows or OS X Box Linux Box Java App w/ DivConq MUMPS Communication is Request-Response only, requests are originated in Java and responses are supplied by MUMPS code. By using three channels, up to three requests can be processed at once. A long running request does not hold up other requests.

  6. Request Structure • Structure • Name: [Stored Procedure Name] • Kind: “Update” or “Query” • Params: [any JSON-like data structure] • Example • Name: “dctListPeople” • Kind: “Query” • Params: { “MinAge”: 30, “MaxAge”: 48 } Your code builds a request and submits it to DivConq’s database interface. A request must have a name and a kind, parameters are optional. Name = name of the stored procedure. Kind will be Update only if it changes data within the database (Insert, Update, Delete), otherwise use Query.

  7. Request Flow, Step 1 Java App w/ DivConq MUMPS DivConq Database Interface MUMPS Process Request Queue Your Application Code Request Verifier MUMPS Process Database Workers #1 Response Verifier MUMPS Process DivConq Schema #1 - Your code builds a request and submits it. Request submission is asynchronous, so you also must provide a callback to handle the result.

  8. Request Flow, Step 2 Java App w/ DivConq MUMPS DivConq Database Interface MUMPS Process Request Queue Your Application Code Request Verifier MUMPS Process Database Workers #1 Response Verifier MUMPS Process #2 DivConq Schema #2 – Your request is verified, including validating the parameters with what is declared in the schema.

  9. Request Flow, Step 3 Java App w/ DivConq MUMPS DivConq Database Interface MUMPS Process Request Queue #3 Your Application Code Request Verifier MUMPS Process Database Workers #1 Response Verifier MUMPS Process #2 DivConq Schema #3 – If verification passes your request is put on to the request queue.

  10. Request Flow, Step 4 Java App w/ DivConq MUMPS DivConq Database Interface MUMPS Process Request Queue #4 #3 Your Application Code Request Verifier MUMPS Process Database Workers #1 Response Verifier #4 MUMPS Process #2 DivConq Schema #4 – When a database worker (channel) is available, it takes the request from the queue and sends it to MUMPS.

  11. Response Flow, Step 5 Java App w/ DivConq MUMPS DivConq Database Interface MUMPS Process Request Queue #4 #3 Your Application Code Request Verifier MUMPS Process Database Workers #1 Response Verifier #4 and #5 MUMPS Process #2 DivConq Schema #5 – That worker then reads (blocking) the result and any accompanying debug/error messages.

  12. Response Flow, Step 6 Java App w/ DivConq MUMPS DivConq Database Interface MUMPS Process Request Queue #4 #3 Your Application Code Request Verifier MUMPS Process Database Workers #1 Response Verifier #4 and #5 MUMPS Process #2 #6 DivConq Schema #6 – The response is verified by validating the (JSON-like) result with what is declared in the schema. If response does not validate, error messages are added to messages collected from MUMPS.

  13. Response Flow, Step 7 Java App w/ DivConq MUMPS DivConq Database Interface MUMPS Process Request Queue #4 #3 Your Application Code Request Verifier MUMPS Process Database Workers #1 Response Verifier #4 and #5 MUMPS Process #7 #2 #6 DivConq Schema #7 – The response and any debug/error messages are delivered to your code via a the callback you provided at submission.

  14. Request in Java, Step 1 Example RecordStruct ages = new RecordStruct(); ages.setField("MinAge", 3); ages.setField("MaxAge", 8); Parameters are composed of JSON-like structures. In DivConq use RecordStruct to compose records (aka Objects in JSON) and ListStruct to compose lists (aka Arrays in JSON). Records (aka Objects) have fields – just as in JSON – the field values may be lists, records or scalars. Above we are using numeric scalars.

  15. Request in Java, Step 2 Example RecordStruct ages = new RecordStruct(); ages.setField("MinAge", 3); ages.setField("MaxAge", 8); QueryRequestrequest = new QueryRequest("dctListPeople", ages); Create the request object. The Name and Kind are required. Kind can be derived from the class name (QueryRequestvsUpdateRequest). Name is the first parameter to the constructor. The parameters, optionally, follow the name in the call to the constructor.

  16. Request in Java, Step 3 ObjectCallbackcallback = new ObjectCallback() { @Override public void process(ObjectResultresult) { System.out.println("Messages:"); TestDb.printPretty(result.getMessages()); System.out.println(); System.out.println("Result:"); TestDb.printPretty(result.getResult()); } }; You also need to create a callback object. This example callback simply prints the messages and results to the console as JSON output. You may call “getResultAsRec” (RecordStruct) or “getResultAsList” (ListStruct) to process the result using DivConq’s JSON-like API.

  17. Request in Java, Step 4 Example RecordStruct ages = new RecordStruct(); ages.setField("MinAge", 3); ages.setField("MaxAge", 8); QueryRequestrequest = new QueryRequest("dctListPeople", ages); ObjectCallback callback = new ObjectCallback() ... Hub.instance.getDatabase().submit(request, callback); Finally, to get the request to the database call the “submit” method and pass in the request and callback. Your result, even if it is just an error message, will be presented in the callback’s “process” method.

  18. Request-Response in Schema <Procedure Name="dctListPeople" Execute="listPeople^dctToyTest"> <Description> Get a list of names of all people in test data. Optionally apply an age range filter </Description> <RecRequest> <Field Name="MinAge" Type="Integer" /> <Field Name="MaxAge" Type="Integer" /> </RecRequest> <ListResponse Type="String" /> </Procedure> A DivConq schema file holds custom data types, table declarations, stored procedure declarations and more. Above is just one snipped from the schema file showing the declaration for the stored procedure “dctListPeople”.

  19. Request-Response in Schema <Procedure Name="dctListPeople" Execute="listPeople^dctToyTest"> <Description> Get a list of names of all people in test data. Optionally apply an age range filter </Description> <RecRequest> <Field Name="MinAge" Type="Integer" /> <Field Name="MaxAge" Type="Integer" /> </RecRequest> <ListResponse Type="String" /> </Procedure> Key elements here are the Request and Response which provide the validation rules for this procedure. Expect either a RecRequest (RecordStruct) or a ListRequest (ListStruct) for request – those are our options for request parameters. Likewise, expect a RecResponse or a ListResponse for the response.

  20. Request-Response Example • Request (Params) Examples • a:{ “MinAge”: 30, “MaxAge”: 48 } • b: { “MaxAge”: 48 } • c:null • Response Examples • a: [ “Jim”, “Beth”, “Sandy” ] • b: [ ] Note that the params are not marked as required (in the schema), so any of the three examples for request are valid. The response is a list of strings. No minimum is given, so a list of zero is valid.

  21. Why the Focus on JSON Windows User w/ Web Browser Linux Box Java App w/ DivConq MUMPS MUMPS Process Your Application Code DivConq Database Interface MUMPS Process External App on Linux MUMPS Process DivConq Web Server (HTTP + Web Sockets) DivConq Schema There are many reasons why JSON – one of the best is interoperability with web apps and other external applications. Through HTTP or WebSocket calls JSON parameters can be sent and JSON results can be returned. To minimize interoperability hassles DivConq favors JSON-like structures throughout.

  22. Request in MUMPS • Java Request (Params) • { “MinAge”: 30, “MaxAge”: 48 } • MUMPS Params • Params(“MinAge”)=30 • Params(“MaxAge”)=48 JSON can be adopted to MUMPS structures without much effort, details in a future presentation. As far as this overview is concerned, the key point is that JSON-like structures get transformed into MUMPS structures before the stored procedure is called.

  23. MUMPS Procedure • Schema Declaration • <Procedure Name="dctListPeople" Execute="listPeople^dctToyTest"> • MUMPS Code • listPeople; • ; • w StartList ; start list of people • w ScalarStr_“Jim” ; write a scalar • w ScalarStr_“Beth” ; write another scalar • w EndList ; end list of people • ; • quit The schema tells us what MUMPS routine and function to call. Above is a hint at what the code has to do – return a list of strings. Note how the result from the procedure is sent back to Java by using the “write” command.

  24. MUMPS Procedure Logic listPeople n id,minage,maxage s minage=Params("MinAge"),maxage=Params("MaxAge") ; w StartList ; start list of people (name only) ; f s id=$o(^dctData("People",id)) q:id="" d . i(minage'="")&(^dctData("People",id,"Age")<minage) q . i(maxage'="")&(^dctData("People",id,"Age")>maxage) q . w ScalarStr_^dctData("People",id,"Name") ; w EndList; end list of people ; quit You’ll need to review the dctToyTest routine to get the details on this example, but what you can see here is that we loop through all the people an apply the (optional) filter. People who are not filtered are returned as scalars (just names) in the list.

  25. Result in Java ObjectCallbackcallback = new ObjectCallback() { @Override public void process(ObjectResultresult) { if (result.hasErrors()) { System.out.println("Error in List"); return; } ListStruct names = result.getResultAsList(); for (Struct item : names.getItems()) System.out.println("Name: " + item.toString()); } }; Back in Java you can process the result via ListStruct by looping all the items. We know, because of the schema validation and the call to “hasErrors” that our items are all just strings. There are a few ways to work with a string item, but certainly calling “toString” will work.

  26. Conclusion There is a lot more to learn about stored procedures, but now you have an overview of how it works as well as some of the design philosophy.

More Related