SQL Access to NoSQL Data: Implementing SQL Procedure in Caché with Dynamic Detection of Returned Metadata
As you know, Caché can be used as a relational DBMS, including through JDBC / ODBC drivers , with the ability to execute arbitrary SQL queries and call SQL procedures.
It is also known that all data in Caché is stored in multidimensional sparse arrays - globals . This allows, in case of insufficient performance of a single SQL procedure, not to use the standard CachéSQL engine, but to rewrite its execution code in the Caché ObjectScript (COS) server business logic language, in which you can implement an optimal algorithm for executing the SQL procedure, often using more optimal NoSQL data structures (globals).
However, there is one limitation in the standard Caché class library: for SQL procedures in which the selection is performed by self-written COS code, it is necessary to define a set of returned fields at the compilation stage — that is, There is no possibility to dynamically set metadata for a SQL procedure working with NoSQL structures.
How to remove this restriction, it is told under cat.
Requests via JDBC / ODBC to non-relational Caché structures are implemented using stored procedures as follows:
')
Such a stored procedure can return one or several record sets (ResultSets), or a scalar value.
As an example, let's call the stored procedure sample.SP_Sample_By_Name from the Samples area using one of the tools for working with ODBC:
By the signature of the SQL procedure, it is not known what it will return, it becomes known only during the execution of the procedure.
Caché allows you to make class methods stored SQL procedures both returning a value and returning a ResultSet. For example, this is how a stored procedure is declared that returns a ResultSet:
ClassMethod SomeSqlProc ( p1 As% Integer = 0 ) [ ReturnResultsets , SqlProc ]
With this construct, you can write Caché ObjectScript code that can be called via ODBC as a stored procedure, which will return a ResultSet (or several).
In Caché, there are two standard methods for generating NoSQL data, returned as a ResultSet:
Look here for details
This method allows you to write arbitrary code to form data in Caché ObjectScript, but the metadata of the returned ResultSet is generated by the compiler based on the% Query parameter. # ROWSPEC, i.e. at compile time.
Learn more about % SQL.CustomResultSet , an example implementation.
The method is similar to the previous one, but the metadata is generated based on the definition of the class-descendant% SQL.CustomResultSet - just as in the previous case, at compile time.
Note: Similarly, you can get and SQL-data:
In this case, metadata is generated at runtime, but data can only be obtained from SQL.
Thus, if we want to generate result metadata in runtime and use arbitrary Caché ObjectScript to generate data, then as can be seen - there are not enough funds in the Caché distribution.
There are 4 solutions to the problem:
I chose the last method - it seemed to me the most elegant (looking ahead, I could not manage without crutches).
To begin with, let's create a User.Query class and inherit it from% Query - so as not to overwrite the implementation of all% Query. When% Query is used by the consumer (% ResultSet), metadata is requested via two class methods: GetInfo and GetODBCInfo. In the heir class, you must write alternative implementations of these methods. Through several experiments (it's easier than to understand the generators), I found out about the parameters GetInfo (.colinfo, .parminfo, .idinfo, .qHandle, extoption, .extinfo):
With GetODBCInfo, everything is the same, there are slightly more fields, and the result should be added to one-level lists, but in general it’s still the same.
In order to return the correct metadata from GetInfo and GetODBCInfo, I need to find several not quite obvious techniques, which are mainly given below:
It is also known that all data in Caché is stored in multidimensional sparse arrays - globals . This allows, in case of insufficient performance of a single SQL procedure, not to use the standard CachéSQL engine, but to rewrite its execution code in the Caché ObjectScript (COS) server business logic language, in which you can implement an optimal algorithm for executing the SQL procedure, often using more optimal NoSQL data structures (globals).
However, there is one limitation in the standard Caché class library: for SQL procedures in which the selection is performed by self-written COS code, it is necessary to define a set of returned fields at the compilation stage — that is, There is no possibility to dynamically set metadata for a SQL procedure working with NoSQL structures.
How to remove this restriction, it is told under cat.
Work with SQL procedures in Caché
Requests via JDBC / ODBC to non-relational Caché structures are implemented using stored procedures as follows:
')
Such a stored procedure can return one or several record sets (ResultSets), or a scalar value.
As an example, let's call the stored procedure sample.SP_Sample_By_Name from the Samples area using one of the tools for working with ODBC:
By the signature of the SQL procedure, it is not known what it will return, it becomes known only during the execution of the procedure.
Caché allows you to make class methods stored SQL procedures both returning a value and returning a ResultSet. For example, this is how a stored procedure is declared that returns a ResultSet:
ClassMethod SomeSqlProc ( p1 As% Integer = 0 ) [ ReturnResultsets , SqlProc ]
With this construct, you can write Caché ObjectScript code that can be called via ODBC as a stored procedure, which will return a ResultSet (or several).
In Caché, there are two standard methods for generating NoSQL data, returned as a ResultSet:
The first way. Using class queries
Using class queries
ClassMethod SomeSqlProc ( p1 As% Integer = 0 ) [ ReturnResultsets , SqlProc ]
{
if ' $ isobject ( $ Get ( % sqlcontext )) { set % sqlcontext = ## class ( % ProcedureContext ). % New () }
Set query = ## class ( % ResultSet ). % New ( "User.SomeClass: Query" )
Do query . Execute ( p1 )
do % sqlcontext . AddResultSet ( query )
}
{
if ' $ isobject ( $ Get ( % sqlcontext )) { set % sqlcontext = ## class ( % ProcedureContext ). % New () }
Set query = ## class ( % ResultSet ). % New ( "User.SomeClass: Query" )
Do query . Execute ( p1 )
do % sqlcontext . AddResultSet ( query )
}
Look here for details
This method allows you to write arbitrary code to form data in Caché ObjectScript, but the metadata of the returned ResultSet is generated by the compiler based on the% Query parameter. # ROWSPEC, i.e. at compile time.
The second way. Using% SQL.CustomResultSet
Using% SQL.CustomResultSet
ClassMethod SomeSqlProc ( p1 As% Integer = 0 ) [ ReturnResultsets , SqlProc ]
{
if ' $ isobject ( $ Get ( % sqlcontext )) { set % sqlcontext = ## class ( % ProcedureContext ). % New () }
Set query = ## class ( User.MyResultSet ). % New (, p1 )
do % sqlcontext . AddResultSet ( query )
}
{
if ' $ isobject ( $ Get ( % sqlcontext )) { set % sqlcontext = ## class ( % ProcedureContext ). % New () }
Set query = ## class ( User.MyResultSet ). % New (, p1 )
do % sqlcontext . AddResultSet ( query )
}
Learn more about % SQL.CustomResultSet , an example implementation.
The method is similar to the previous one, but the metadata is generated based on the definition of the class-descendant% SQL.CustomResultSet - just as in the previous case, at compile time.
Note: Similarly, you can get and SQL-data:
SQL data retrieval
ClassMethod SomeSqlProc ( p1 As% Integer = 0 ) [ ReturnResultsets , SqlProc ]
{
s sqltext = "SELECT * FROM dbo.Classname" ##; Prepare request text
if ' $ isobject ( $ Get ( % sqlcontext )) { set % sqlcontext = ## class ( % ProcedureContext ). % New () }
Set query = ## class ( % ResultSet ). % New ( "% DynamicQuery: SQL" )
Do query . Prepare ( sqltext )
Do query . Execute ()
do % sqlcontext . AddResultSet ( query )
}
{
s sqltext = "SELECT * FROM dbo.Classname" ##; Prepare request text
if ' $ isobject ( $ Get ( % sqlcontext )) { set % sqlcontext = ## class ( % ProcedureContext ). % New () }
Set query = ## class ( % ResultSet ). % New ( "% DynamicQuery: SQL" )
Do query . Prepare ( sqltext )
Do query . Execute ()
do % sqlcontext . AddResultSet ( query )
}
In this case, metadata is generated at runtime, but data can only be obtained from SQL.
Thus, if we want to generate result metadata in runtime and use arbitrary Caché ObjectScript to generate data, then as can be seen - there are not enough funds in the Caché distribution.
The solution of the problem
There are 4 solutions to the problem:
- at runtime, create a class containing a class query with the generated on-the-fly ROWSPEC;
- at run time, create a class inherited from% SQL.CustomResultSet with the required set of fields;
- implement an alternative to% SQL.CustomResultSet, which will generate metadata at runtime based on the parameters of the call, not at compile time;
- implement an alternative to% Query, which will generate metadata at runtime.
I chose the last method - it seemed to me the most elegant (looking ahead, I could not manage without crutches).
To begin with, let's create a User.Query class and inherit it from% Query - so as not to overwrite the implementation of all% Query. When% Query is used by the consumer (% ResultSet), metadata is requested via two class methods: GetInfo and GetODBCInfo. In the heir class, you must write alternative implementations of these methods. Through several experiments (it's easier than to understand the generators), I found out about the parameters GetInfo (.colinfo, .parminfo, .idinfo, .qHandle, extoption, .extinfo):
- colinfo - you need to add $ lb ($ lb (name, typeid, caption), ...) into it, where name is the internal field name, typeid is the Caché type identifier, caption is the column header;
- parminfo - you need to add $ lb ($ lb (name, typeid), ...) into it - the same format as in the previous paragraph, but without a header;
- idinfo - you can add $ lb (0,0) into it (system information related to the index, we assume that it is not);
- qHandle is a multidimensional local array formed by a programmer;
- the rest can be left alone (it seems, for object references, in the absence of objects it is not necessary).
With GetODBCInfo, everything is the same, there are slightly more fields, and the result should be added to one-level lists, but in general it’s still the same.
In order to return the correct metadata from GetInfo and GetODBCInfo, I need to find several not quite obvious techniques, which are mainly given below:
- To get the type identifier Caché (typeid), you need to call $$ externaltype ^% apiOLE (ctype, .type, "0"), where ctype is the type name in Caché (for example,% String [link to class% string]). The function will put the id in type.
Before recognizing an identifier, the type (ctype) must be normalized (reduced to the Package.Class type), this can be done with the $$$ NormalizeClassname (ctype) macro
To get information for GetODBCInfo, you need to call
GetODBCColInfo ^% ourODBC ( ctype ,. ColParms ,. ColODBCTypeName ,. ColODBCType ,. MaxLen ,. Precision ,. Scale ) ,
where ctype is the type name in Caché, not necessarily normalized.
Since we want to generate metadata (field names and types) dynamically, our Query needs to pass information about them. The most obvious way to do this is with the qHandle parameter. Through it we will transmit information about the ResultSet. To do this, the programmer, in his implementation of the query execution (QueryExecute), should form a ROWSPEC string for the required fields and a string of formal query parameters (by analogy with ROWSPEC) and put them in qHandle (“rowspec”) and qHandle (“params”), respectively.
As a result, we get the following implementation of the User.Query class:
User.Query classClass User.Query Extends% Query
{
ClassMethod GetInfo ( ByRef colinfo As% List , ByRef parminfo As% List , ByRef idinfo As% List , ByRef qHandle As% Binary , extoption As% Integer = 0 , ByRef extinfo As% List ) As% Status
{
if $ get ( qHandle ( "colinfo" )) = ""
{
set RowSpec = qHandle ( "rowspec" )
set qHandle ( "colinfo" ) = ""
set sc = $$$ OK
for i = 1: 1: $ length ( RowSpec , "," )
{
set col = $ piece ( RowSpec , "," , i )
set name = "p" _ i
set ctype = $$$ NormalizeClassname ( $ select ( $ piece ( col , ":" , 2) '= "" : $ piece ( col , ":" , 2), 1: "% String" ))
set sc = $$ externaltype ^% apiOLE ( ctype ,. type , "0" )
quit : $$$ ISERR ( sc )
set caption = $ piece ( col , ":" , 1)
set qHandle ( "colinfo" ) = qHandle ( "colinfo" ) _ $ listbuild ( $ listbuild ( name , type , caption ))
}
quit : $$$ ISERR ( sc ) sc
}
if $ get ( qHandle ( "parminfo" )) = ""
{
set Params = qHandle ( "params" )
set qHandle ( "parminfo" ) = ""
set sc = $$$ OK
for i = 1: 1: $ length ( Params , "," )
{
set col = $ piece ( Params , "," , i )
set name = "p" _ i
set ctype = $$$ NormalizeClassname ( $ select ( $ piece ( col , ":" , 2) '= "" : $ piece ( col , ":" , 2), 1: "% String" ))
set sc = $$ externaltype ^% apiOLE ( ctype ,. type , "0" )
quit : $$$ ISERR ( sc )
set qHandle ( "parminfo" ) = qHandle ( "parminfo" ) _ $ listbuild ( $ listbuild ( name , type ))
}
quit : $$$ ISERR ( sc ) sc
}
set colinfo = qHandle ( "colinfo" )
set parminfo = qHandle ( "parminfo" )
set idinfo = $ listbuild (0,0)
quit $$$ OK
}
ClassMethod GetODBCInfo ( ByRef colinfo As% List , ByRef parminfo As% List , ByRef qHandle As% Binary )
{ if $ get ( qHandle ( "colinfoodbc" )) = ""
{
set RowSpec = qHandle ( "rowspec" )
set qHandle ( "colinfoodbc" ) = $ listbuild ( $ LENGTH ( RowSpec , "," ))
for i = 1: 1: $ length ( RowSpec , "," )
{
set col = $ piece ( RowSpec , "," , i )
set ctype = $ select ( $ piece ( col , ":" , 2) '= "" : $ piece ( col , ":" , 2), 1: "% String" )
Do GetODBCColInfo ^% ourODBC ( ctype ,. ColParms ,. ColODBCTypeName ,. ColODBCType ,. MaxLen ,. Precision ,. Scale )
set bstr = "$ Char (0,0,0,0,0,0,0,0,0,0,0,0,0)"
set name = $ piece ( col , ":" , 1)
set qHandle ( "colinfoodbc" ) = qHandle ( "colinfoodbc" ) _ $ listbuild ( name , colODBCType , precision , scale , 2, name , "Query" , "% Library" , "" , bstr )
}
}
if $ get ( qHandle ( "parminfoodbc" )) = ""
{
set Params = qHandle ( "params" )
set qHandle ( "parminfoodbc" ) = $ listbuild ( $ LENGTH ( Params , "," ))
for i = 1: 1: $ length ( RowSpec , "," )
{
set col = $ piece ( Params , "," , i )
set ctype = $ select ( $ piece ( col , ":" , 2) '= "" : $ piece ( col , ":" , 2), 1: "% String" )
Do GetODBCColInfo ^% ourODBC ( ctype ,. ColParms ,. ColODBCTypeName ,. ColODBCType ,. MaxLen ,. Precision ,. Scale )
set name = "p" _ i
set qHandle ( "parminfoodbc" ) = qHandle ( "parminfoodbc" ) _ $ listbuild ( colODBCType , precision , scale , 2, name , 1)
}
}
set colinfo = qHandle ( "colinfoodbc" )
set parminfo = qHandle ( "parminfoodbc" )
quit $$$ OK
}
}
How to apply the User.Query class
Using User.Query is similar to using % Query , but during initialization it is necessary to pass information to it to generate metadata.
The class using User.Query should look something like this:
User.DynamicQuery classClass User.DynamicQuery [ Abstract ]
{
Query Query ( p1 As% Integer ) As User.Query
{
}
ClassMethod QueryExecute ( ByRef qHandle As% Binary , p1 As% Integer ) As% Status
{
/// Making all the preparations
; ...
/// Forming ROWSPEC
s RowSpec = "ID:% Integer, date:% TimeStamp, Info:% String"
s qHandle ( "rowspec" ) = RowSpec
/// Build a string of formal parameters, constant
s qHandle ( "params" ) = "p1:% Integer"
q $$$ OK
}
ClassMethod QueryClose ( ByRef qHandle As% Binary ) As% Status [ PlaceAfter = QueryExecute]
{
Quit $$$ OK
}
ClassMethod QueryFetch ( ByRef qHandle As% Binary , ByRef Row As% List , ByRef AtEnd As% Integer = 0 ) As% Status [ PlaceAfter = QueryExecute]
{
/// We write the usual QueryFetch, as described in the documentation for class queries
}
}
/// Code of the stored procedure calling User.Query:
ClassMethod DynamicProc ( p1 As% Integer = 0 ) [ ReturnResultsets , SqlProc ]
{
if ' $ isobject ( $ Get ( % sqlcontext )) { set % sqlcontext = ## class ( % ProcedureContext ). % New () }
Set query = ## class ( % ResultSet ). % New ( "User.DynamicQuery: Query" )
Do query . Execute ( p1 )
do % sqlcontext . AddResultSet ( query )
}
Usage example
Create the Queries class in the Samples area. It will contain only one request, so it can be made abstract.
Class User.QueriesClass User.Queries [ Abstract ]
{
Query NoSQL ( ColCount As% Integer ) As User.Query
{
}
ClassMethod NoSQLExecute ( ByRef qHandle As% Binary , ColCount As% Integer ) As% Status
{
set RowSpec = "Id:% Integer"
for colNum = 1: 1: ColCount
{
set RowSpec = RowSpec _ ", p" _ colNum _ ":% Integer"
}
set qHandle ( "rowspec" ) = RowSpec
set qHandle ( "params" ) = "ColCount:% Integer"
kill ^ || MyData (+ ## this )
for rowNum = 1: 1: 100 {
for colNum = 1: 1: ColCount
{
set $ list (^ || MyData (+ ## this , rowNum ), colNum ) = $ R (1000)
}
}
set qHandle ( "colcount" ) = ColCount
set qHandle ( "cursor" ) = $ order (^ || MyData (+ ## this , "" ))
quit $$$ OK
}
ClassMethod NoSQLClose ( ByRef qHandle As% Binary ) As% Status [ PlaceAfter = NoSQLExecute]
{
kill ^ || MyData (+ ## this ), qHandle
Quit $$$ OK
}
ClassMethod NoSQLFetch ( ByRef qHandle As% Binary , ByRef Row As% List , ByRef AtEnd As% Integer = 0 ) As% Status [ PlaceAfter = NoSQLExecute]
{
if qHandle ( "cursor" ) = ""
{
set Row = "" , AtEnd = 1
quit $$$ OK
}
set rowNum = qHandle ( "cursor" )
set Row = $ listbuild ( rowNum ) _ ^ || MyData (+ ## this , rowNum )
set qHandle ( "cursor" ) = $ order (^ || MyData (+ ## this , rowNum ))
Quit $$$ OK
}
}
Our query takes a number of columns, and returns 100 entries filled with random numbers. Now we will write the Procedures class, which will contain the method of the class-stored procedure using our query.
User.Procedures classClass User.Procedures Extends% Persistent
{
ClassMethod ProcNoSQL ( p1 As% Integer ) [ ReturnResultsets , SqlName = proc_nosql , SqlProc ]
{
if ' $ isobject ( $ Get ( % sqlcontext )) { set % sqlcontext = ## class ( % ProcedureContext ). % New () }
Set query = ## class ( % ResultSet ). % New ( "User.Queries: NoSQL" )
Do query . Execute ( p1 )
do % sqlcontext . AddResultSet ( query )
}
ProcSQL ClassMethod ( p1 As% String = "" ) [ ReturnResultsets , SqlName = proc_sql , SqlProc ]
{
set sqltext = "SELECT ID, Name, DOB, SSN"
set sqltext = sqltext _ "FROM Sample.Person"
set sqltext = sqltext _ "WHERE (Name% STARTSWITH '" _ p1 _ "')"
set sqltext = sqltext _ "ORDER BY Name"
if ' $ isobject ( $ Get ( % sqlcontext )) { set % sqlcontext = ## class ( % ProcedureContext ). % New () }
Set query = ## class ( % ResultSet ). % New ( "% DynamicQuery: SQL" )
Do query . Prepare ( sqltext )
Do query . Execute ()
do % sqlcontext . AddResultSet ( query )
}
}
Now the generated SQL procedure that executes the NoSQL query can be called via xDBC:
Conclusion
I hope that the method I have proposed for creating NoSQL queries for SQL procedures with dynamic definition will be useful to someone, as it turned out to be useful to me, when implementing a specific practical task of improving the performance of SQL procedures, which I will probably discuss in the next article.
Source: https://habr.com/ru/post/160147/
All Articles