Instant porridge - do CRUD in Caché using jqGrid
It will be about creating a web application on Intersystems Caché using javascript plug-in to display tabular data - jqGrid . The plugin is often mentioned on Habré, so the focus will be on the features of its use by Caché
Advantages of using jqGrid :
The composition of the dish : the stored class with the data, the class page, the class data service. Necessary libraries and styles are connected from data delivery networks ( CDN ) and from developer sites, therefore, for the example to work in a local network, they will need to be downloaded from these sources.
Warning : the example is maximally simplified, only a small part of the plugin’s features is considered, but the code is still a lot, although it has been carefully documented.
The basis of this dish will be the class model.person .
Create a stored class in Caché Studio. File - Create - Caché Class
We will generate a small number of test instances - go to the “Output” window of the studio (Alt + 2) and execute:
Create a data display page - view.person class with the necessary styles and supporting libraries.
Before you go to the code of the data service class, you need to talk about the protocol and data exchange formats of the plug-in with the server. The plugin asynchronously loads portions of data from the server, sending requests to it of the following form:
Where: rows - the number of rows of data in the chunk, page - the sequence number of the chunk, _search - search mode (on or off), McCormick - search value in the name column of the table, sidx - sort field, sord - sort order
In response, jqGrid expects the following data (for example, the following json data format is selected)
Where, records is the total number of rows, total is the number of chunks of data on the server, page is the unloading chunk of data, rows is the array with data
Requests to change data from the plugin arriving at the address specified in the editurl parameter are as follows:
creation
change
removal
The server's response to these requests is processed in the afterSubmit function, the result of which should be an array of the form:
Where: result is the result of the query (true || false), error is an error message if result == false, id is the object identifier value (in the case of a create operation)
The implementation of this protocol will be performed by the data service class service.person , which will parse the incoming request parameters, form a dynamic query and output the data in a specific format.
To run the application, in Cache Studio, open the view.person class and press F5. The working example is deployed here.
The example can make a shocking impression because of the huge amount of code required for the implementation of a relatively simple task. At this point, you need to remember that Caché is an object-oriented DBMS and most of this code can be parameterized and rendered into parent classes, or implemented as an interactive dialog that creates a set of necessary classes. Then, to implement this example, it was necessary to specify only two parameters - the name of the stored class and the list of its properties.
UPD: It was on the day of publication of the article that jqueryui.com changed the design and structure, removing themeswitchertool.js from access. Now its simplified functional analogue is used, the source code can be collected here
Advantages of using jqGrid :
- server unloading from client logic
- possibility of using various data exchange formats (xml, json)
- various ways to display data (table, tree, nested tables)
- ready data modification functionality - editing in rows, editing in forms, checking data
- a large number of settings, options and events with documentation, examples and source code
- rich user functionality - sorting, grouping, filtering, searching, totals, customizing the display of columns (visibility, order, size), support for a variety of themes from jqueryui
The composition of the dish : the stored class with the data, the class page, the class data service. Necessary libraries and styles are connected from data delivery networks ( CDN ) and from developer sites, therefore, for the example to work in a local network, they will need to be downloaded from these sources.
Warning : the example is maximally simplified, only a small part of the plugin’s features is considered, but the code is still a lot, although it has been carefully documented.
Model
The basis of this dish will be the class model.person .
Create a stored class in Caché Studio. File - Create - Caché Class
The source code of the stored class model.person
/// Simple stored class
Class model.person Extends ( % Persistent , % Populate ) {
/// Full Name - we indicate which function to fill
Property name As % String ( POPSPEC = "Name ()" );
Index name On name;
')
/// Year of birth
Property year As % Integer ( MAXVAL = 2012 , MINVAL = 1910 );
/// Search by year will be faster
Index year On year [ Type = bitmap];
}
Class model.person Extends ( % Persistent , % Populate ) {
/// Full Name - we indicate which function to fill
Property name As % String ( POPSPEC = "Name ()" );
Index name On name;
')
/// Year of birth
Property year As % Integer ( MAXVAL = 2012 , MINVAL = 1910 );
/// Search by year will be faster
Index year On year [ Type = bitmap];
}
We will generate a small number of test instances - go to the “Output” window of the studio (Alt + 2) and execute:
write ##class(model.person).Populate(100000) View
Create a data display page - view.person class with the necessary styles and supporting libraries.
Class source code view.person
/// <class>model.person</class> /// <a href="http://www.trirand.com/blog/">jqgrid</a> Class view.person Extends %CSP.Page { /// node Parameter DOMAIN = "person"; /// ClassMethod OnPage() As %Status { &html<<!DOCTYPE html> <html lang="ru"><head> <meta charset="ru"/> <title>#($$$Text("Intersystems Caché + jqGrid"))#</title> <style> /* */ body {font-size: 11px; font-family: Georgia,Verdana,Arial,sans-serif; } </style> <!-- --> <link rel="stylesheet" type="text/css" href="http://code.jquery.com/ui/1.8.24/themes/base/jquery-ui.css" /> <!-- --> <link rel="stylesheet" type="text/css" href="http://www.trirand.net/themes/ui.jqgrid.css" /> <!-- , --> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script> <!-- (, , etc.) --> <script src="//ajax.googleapis.com/ajax/libs/jqueryui/1.8.23/jquery-ui.min.js"></script> <!-- jgGrid--> <script src="http://www.trirand.net/js/trirand/i18n/grid.locale-ru.js"></script> <!-- --> <script src="http://www.trirand.net/js/trirand/jquery.jqGrid.min.js"></script> <!-- --> <script src="themeswitcher.js"></script> </head><body> <!-- c --> <table id="grid"></table> <!-- --> <div id="bar"></div> <!-- --> <div id="theme"></div> <!-- --> <script type="text/javascript"> $( function(){ // document.ready var $grid=$( "#grid" ) // , bar="#bar" // , url='#(..Link("service.person.cls"))#' ; $grid.jqGrid({ // , caption: '#($$$Text("person"))#' , colModel: [ // { name: 'name', width: 250, editable: true } ,{ name: 'year', editable: true } ] , pager: bar // , url: url // , editurl: url // , datatype: "json" // , mtype: 'POST' , jsonReader: { // // // // repeatitems: false } , height: 350, width: 900 // , rownumbers: true // , rownumWidth: 45 // , viewrecords: true // , gridview: true // , , scroll: 1 // , hoverrows: true // , rowNum: 100 // , , sortable: true // , sortname: 'name' // }) .jqGrid('filterToolbar',{searchOnEnter:false}) // .jqGrid('gridResize', {}) // ; /// var serverHandler=function( resp ){ var array=[]; try { array=eval(resp.responseText); } catch(err){ return ["",e.description]; } return array; }; /// / , var opts={ afterSubmit: serverHandler // , closeAfterAdd: true // , clearAfterAdd: true // , closeAfterEdit: true // // . . , viewPagerButtons: false }; // $grid.jqGrid('navGrid',bar, { edit: true, edittext: '#($$$Text(""))#' , add: true, addtext: '#($$$Text(""))#' , del: true, deltext: '#($$$Text(""))#' , view: false, search: false } , opts // , opts // , opts // ); // // pager $(bar+"_center").remove(); // , UPD themeswitcher( $( "#theme" ) ); });</script></body></html>> Quit $$$OK } } Controller
Before you go to the code of the data service class, you need to talk about the protocol and data exchange formats of the plug-in with the server. The plugin asynchronously loads portions of data from the server, sending requests to it of the following form:
?rows=100&page=2&_search=true&name=McCormick&sidx=name&sord=descWhere: rows - the number of rows of data in the chunk, page - the sequence number of the chunk, _search - search mode (on or off), McCormick - search value in the name column of the table, sidx - sort field, sord - sort order
In response, jqGrid expects the following data (for example, the following json data format is selected)
{"records": 102, "total": 2, "page": 2, "rows": [ { "id": 1,"name": "McCormick,Diane Z.", "year": 1910 } ,{ "id": 2,"name": "McCormick,Christen G.", "year": 1911 } ]} Where, records is the total number of rows, total is the number of chunks of data on the server, page is the unloading chunk of data, rows is the array with data
Requests to change data from the plugin arriving at the address specified in the editurl parameter are as follows:
creation
?oper=add&id=_empty&name=habra&year=2005change
?oper=edit&id=1&name=habra&year=2006removal
?oper=del&id=1The server's response to these requests is processed in the afterSubmit function, the result of which should be an array of the form:
[ result, error, id ]Where: result is the result of the query (true || false), error is an error message if result == false, id is the object identifier value (in the case of a create operation)
The implementation of this protocol will be performed by the data service class service.person , which will parse the incoming request parameters, form a dynamic query and output the data in a specific format.
The source code for the service.person class
#; csp.inc #; service.person /// /// %request #define get(%name) $g(%request.Data(%name,1)) Include csp /// - Class service.person Extends %CSP.Page { /// node Parameter DOMAIN = "person"; /// Can only be referenced from another CSP page Parameter PRIVATE = 1; ClassMethod OnPage() As %Status { #; . csp.inc set oper=$$$get("oper") ; #; - if ( oper = "add" ) Quit ..Add() Q:oper="edit" ..Edit() Q:oper="del" ..Del() #; - #; set rows=$$$get("rows")\1 ; if ( rows < 1 ) { set rows = 100 ; } #; s page=$$$get("page")\1 s:page<1 page=1 #; ( - ) #; sql where s where="", params="", search=$CASE( $$$get("_search"), "true": 1, : 0 ) if ( search ) { s name=$$$get( "name" ) if ( name'="" ) { #; name , #; s where=where_$ListBuild( "name Like ?" ) s params( $increment(params) )="%"_name_"%" } s year=$$$get( "year" ) if ( year'="" ) { #; year - SQL - s where=where_$LB( """year"" = ?" ) ; - , params( $i( params ) )=year\1 ; } s where=$ListToString(where," AND ") } #; #; s countSQL=" SELECT Count(*) as records FROM model.person " if ( search ) s countSQL=countSQL_" WHERE "_where s records=0 #dim RS as %SQL.StatementResult s stmt=##class(%SQL.Statement).%New() s sc=stmt.%Prepare(countSQL) if 'sc d ..ShowError(sc) Q $$$OK s RS=stmt.%Execute(params...) if RS.%SQLCODE d ##class(%SYSTEM.SQL).SQLCODE(RS.%SQLCODE) Q $$$OK s:RS.%Next() records=RS.records kill RS #; s total = records\rows ; , part=records#rows ; s:part total=total+1 ; #; , s:page>total page=total ; #; s end=page*rows, start=end-rows #; #; sql order by #; jqgrid s order="",sidx=$$$get( "sidx" ), sord=$$$get( "sord" ) #; if $ListFind( $ListBuild("name","year"), sidx ) { s:sidx="year" sidx="""year""" ;year - sql s order=sidx _ " "_$CASE( sord, "desc": "desc", : "asc" ) } #; #; , ID s sql=" SELECT ID From model.person " s:search sql=sql_" WHERE "_where s:order'="" sql=sql_" ORDER BY "_order s sc=stmt.%Prepare(sql) if 'sc d ..ShowError(sc) Q $$$OK s RS=stmt.%Execute(params...) if RS.%SQLCODE d ##class(%SYSTEM.SQL).SQLCODE(RS.%SQLCODE) Q $$$OK write "{" ; , """records"": ", records ; , ", ""total"": ", total ; , ", ""page"": ", page ; , ", ""rows"": [" ; #; #; JSON - http://json.org #; ..QuoteJS() #;- #define json(%str) """"_$replace($zcvt(%str,"O","JS"),"\'","'")_"""" #; s sc="" for { s sc=RS.%Next() Quit:sc=0 s rnum=RS.%ROWCOUNT if (rnum < start) continue ; if (rnum > end) Quit ; #; , #;rnum>1 - , start=0 if ( rnum > start ) && ( rnum > 1 ) { w "," } s name=##class(model.person).nameGetStored(RS.ID) , year=##class(model.person).yearGetStored(RS.ID) w "{""id"":",RS.ID,",""name"":",$$$json(name),",""year"":",$$$json(year),"}" } w "]}" Q $$$OK } /// ClassMethod Add() As %Status { s obj=##class(model.person).%New() Q ..Set(.obj) } /// ClassMethod Edit() As %Status { s id=$$$get("id"), obj=##class(model.person).%OpenId(id,.sc) if $$$ISERR( sc ) { Q ..wResult( sc, id ) } Q ..Set(.obj) } /// , , ClassMethod Set(obj As model.person) As %Status { if ( $g(obj) = "" ) || ( '$IsObject(obj) ) { s sc=$$$ERROR( $$$GeneralError, $$$Text(" ") ) Q ..wResult( sc ) } s obj.name=$$$get("name") s obj.year=$$$get("year") s id="", sc=obj.%Save() s:$$$ISOK(sc) id=obj.%Id() Q ..wResult( sc, id ) Q $$$OK } /// ClassMethod Del() As %Status { s id=$$$get("id"), sc=##class(model.person).%DeleteId(id) Q ..wResult( sc, id ) Q $$$OK } /// ClassMethod wResult(sc As %String = "", id As %String) As %Status { s result="false", msg="", id=$g(id) if $$$ISOK( $g(sc) ) { s result="true" } else { s msg=##class(%SYSTEM.Status).GetOneErrorText(sc) } w "[",result,",",..QuoteJS(msg),",",..QuoteJS(id),"]" Q $$$OK } } To run the application, in Cache Studio, open the view.person class and press F5. The working example is deployed here.
Salt and sugar to taste
The example can make a shocking impression because of the huge amount of code required for the implementation of a relatively simple task. At this point, you need to remember that Caché is an object-oriented DBMS and most of this code can be parameterized and rendered into parent classes, or implemented as an interactive dialog that creates a set of necessary classes. Then, to implement this example, it was necessary to specify only two parameters - the name of the stored class and the list of its properties.
UPD: It was on the day of publication of the article that jqueryui.com changed the design and structure, removing themeswitchertool.js from access. Now its simplified functional analogue is used, the source code can be collected here
Source: https://habr.com/ru/post/144917/
All Articles