发布新帖

查找

问题
· 二月 2, 2023

sql网关连接sqlsever数据库

1.在SQL网关通过odbc连接第三方的数据库,第三方的odbc驱动网上只有命令下载的,没有直接的包,这种情况下怎么弄呢 ,jdbc连接的话,jar的类路径怎么填写

3 Comments
讨论 (3)1
登录或注册以继续
讨论 (10)6
登录或注册以继续
文章
· 二月 1, 2023 阅读大约需 17 分钟

OpenAPI Suite - Part 1

Hi Community,

I would like to present my last package OpenAPI-Suite, this is a set of tools to generate ObjectScript code from an OpenAPI specification version 3.0.  In short, these packages allow to: 

  • Generate server-side class.  It’s pretty similar to the generated code by ^%REST but the added value is the version 3.0 support.
  • Generate HTTP client classes.
  • Generate client production (business services, business operation, business process, Ens.Request, Ens.Response) classes.
  • A web interface to generate and download the code or generate and compile directly on the server.
  • Convert specification from version 1.x, 2.x to version 3.0.

Overview

OpenAPI Suite is split into many packages, and uses different developer community libraries and also public REST services.  You can see on the schema below, all packages have been developed, and libraries and web services used:

Note: In case of a problem using public REST services, it’s possible to start a docker instance of the converter and validator service.

What does each package do?

OpenAPI Suite has been designed in different packages to ease maintenance, improvement and future extension.  Each package has a role.  Let's have a look at it!

openapi-common-lib

This contains all the common code to the others packages.  For example, openapi-client-gen and openapi-server-gen accept the following input for an OpenAPI specification: 

  • URL
  • File path
  • %Stream.Object 
  • %DynamicObject
  • Format YAML
  • Format JSON
  • OpenAPI version 1.x, 2.x, 3.0.x.

However, only a specification 3.0.x in a %DynamicObject can be processed.  The code for the transformation is located in this package.  It also contains various utilities.  

swagger-converter-cli

It’s a dependency of openapi-common-lib.  This is an HTTP client using the public REST service converter.swagger.io in order to convert OpenAPI version 1.x or 2.x in version 3.0.

swagger-validator-cli

It’s also a dependency of openapi-common-lib, even if his name is “validator”, it’s not used to validate the specification.  converter.swagger.io provide the service “parse” allowing to simplify the structure of an OpenAPI specification.  For example: create a definition for a “nested object definition” and replace it with a “$ref”.  This reduces the number of cases to be dealt with in the code generation algorithm.

openapi-client-gen

This package is dedicated to client-side code generation to help developers to consume REST services.

It includes a simple HTTP client or a Production client (business services, process, operation, Production classes).  Originally created to support OpenAPI 2.x It was just completely refactored to support version 3.x.

openapi-server-gen

The opposite of openapi-client-gen, it is dedicated to server-side code generation.  There is no interest in specification version 2.0 because ^%REST exists but the target of this package the version 3.0 support.  

openapi-suite

It gathers all the above packages and provides a REST API to: 

  • Generate the code and compile code on the IRIS instance.
  • Generate code without compiling for download only.

A web interface is also provided to consume this REST API and thus exploit the functionalities of the OpenAPI Suite.

And the libraries?

Here are some of the existing libraries on the DC that have been useful in this development:

objectscript-openapi-definition

A useful library to generate model classes from an OpenAPI specification.  This is a very important piece of this project and I’m also a contributor.

ssl-client

Allows creating SSL Configuration.  Mainly used to create a configuration name “DefaultSSL” for HTTPS requests.  

yaml-utils

In the case of YAML format specification, this library is used to convert into JSON format.  A must-have in this project.  By the way, it was developed initially to test YAML specification with openapi-client-gen version 1.

io-redirect

This is one of my libraries, It allows redirecting “write” into a stream, file, global or string variable.  It’s used by the REST service to keep a trace of the logs.  It’s inspired of this community post.

Installation IPM

To install the suite, your best friend is IPM (zpm).  There are many packages and dependencies, and using IPM is definitely convenient.

zpm "install openapi-suite"
; optional
; zpm "install swagger-ui"

 

Installation Docker

There is nothing special, this project uses the intersystems-iris-dev-template.

git clone git@github.com:lscalese/openapi-suite.git
cd openapi-suite
docker-compose up -d

If you have an error at Iris start, maybe this is a permission issue iris-main.log.

You can try:

touch iris-main.log && chmod 777 iris-main.log

Note: adding RW permission for the irisowner user should be enough.

How to use It

OpenAPI-Suite provides a web interface to “Generate and download” or “Generate and install” code.  

The interface is available at http://localhost:52796/openapisuite/ui/index.csp (*adapt with your port number if needed).  

It’s very simple, fill out the form:

  1. Application package name: this is the package used for the generated classes.  It must be a non-existing package name.
  2. Select what do you want to generate: HTTP Client, Client Production or REST server.
  3. Select the namespace where the code will be generated.  It makes sense only if you click “Install On Server” otherwise this field will be ignored.
  4. Web Application Name is optional and available only if you select “REST Server” to generate.  Leave empty if you don’t want to create a Web Application related to the REST dispatch class generated.
  5. The OpenAPI specification field could be an URL pointing to the specification or a copy\paste of the specification itself (in this case the specification must be in JSON format).

If you click the “Download Only” button the code will be generated and returned in an XML file, and then the classes will be deleted from the server.  The namespace used to store temporarily the generated classes is the namespace where OpenAPI-Suite is installed (By default IRISAPP if you use a docker installation).  

However, if you click the “Install On Server” button the code will be generated and compiled, and the server returns a JSON message with the status of the code generation \ compilation and also logs.

By default this feature is disabled, to enable just open an IRIS terminal and:

Set ^openapisuite.config("web","enable-install-onserver") = 1

Explore the OpenAPI-suite REST API

The CSP form uses REST services available at http://localhost:52796/openapisuite.

Open swagger-ui http://localhost:52796/swagger-ui/index.html  and explore http://localhost:52796/openapisuite/_spec 

This is the first step towards creating a more advanced front-end application with the Angular framework.

Generate code programmatically

Of course, it's not mandatory to use the UI, we see in this section how to generate the code programmatically and how to use the generated services.

All snippets are also available in the class dc.openapi.suite.samples.PetStore.

 

HTTP client

Set features("simpleHttpClientOnly") = 1
Set sc = ##class(dc.openapi.client.Spec).generateApp("petstoreclient", "https://petstore3.swagger.io/api/v3/openapi.json", .features)

 

The first argument is the package where the classes will be generated, so be sure to pass a valid package name.  The second argument could be an URL pointing to the specification, a filename, a stream, or a %DynamicObject.  “features” is an array, currently only subscripts are available : 

simpleHttpClientOnly: if 1 only a simple HTTP client will be generated otherwise, a production will be also generated (default behaviour).

compile: if 0 the generated code won’t be compiled.  It could be useful if you want to generate code for export purposes only.  By default, compile = 1

Below is an example of how to use the service “addPet” with the just-generated HTTP client:

Set messageRequest = ##class(petstoreclient.requests.addPet).%New()
Set messageRequest.%ContentType = "application/json"
Do messageRequest.PetNewObject().%JSONImport({"id":456,"name":"Mittens","photoUrls":["https://static.wikia.nocookie.net/disney/images/c/cb/Profile_-_Mittens.jpg/revision/latest?cb=20200709180903"],"status":"available"})
  
Set httpClient = ##class(petstoreclient.HttpClient).%New("https://petstore3.swagger.io/api/v3","DefaultSSL")
; MessageResponse will be an instance of petstoreclient.responses.addPet
Set sc = httpClient.addPet(messageRequest, .messageResponse)
If $$$ISERR(sc) Do $SYSTEM.Status.DisplayError(sc) Quit sc
  
Write !,"Http Status code : ", messageResponse.httpStatusCode,!
Do messageResponse.Pet.%JSONExport()

 

 
Click to show generated classes.

 

HTTP client Production

Set sc = ##class(dc.openapi.client.Spec).generateApp("petstoreproduction", "https://petstore3.swagger.io/api/v3/openapi.json")

The first argument is the package name if you test code generation of simple HTTP client and client production make sure to use a different package name.  The second and third follow the same rules as the HTTP client.

Before testing, please start the production via the management portal using this command

Do ##class(Ens.Director).StartProduction("petstoreproduction.Production")

Below is an example of how to use the service “addPet”, but this time with the generated production:

Set messageRequest = ##class(petstoreproduction.requests.addPet).%New()
Set messageRequest.%ContentType = "application/json"
Do messageRequest.PetNewObject().%JSONImport({"id":123,"name":"Kitty Galore","photoUrls":["https://www.tippett.com/wp-content/uploads/2017/01/ca2DC049.130.1264.jpg"],"status":"pending"})
; MessageResponse will be an instance of petstoreclient.responses.addPet
Set sc = ##class(petstoreproduction.Utils).invokeHostSync("petstoreproduction.bp.SyncProcess", messageRequest, "petstoreproduction.bs.ProxyService", , .messageResponse)
Write !, "Take a look in visual trace (management portal)"
If $$$ISERR(sc) Do $SYSTEM.Status.DisplayError(sc)
Write !,"Http Status code : ", messageResponse.httpStatusCode,!
Do messageResponse.Pet.%JSONExport()

Now, you can open the visual trace to see the details: 

The generated classes in the packages model, requests and responses are pretty similar to the generated code for a simple HTTP client.  Classes from package requests inherit from Ens.Request and classes in package responses inherit Ens.Response.  The default implementation of the business operation is very simple, see this snippet: 

Class petstoreproduction.bo.Operation Extends Ens.BusinessOperation [ ProcedureBlock ]
{

Parameter ADAPTER = "EnsLib.HTTP.OutboundAdapter";
Property Adapter As EnsLib.HTTP.OutboundAdapter;
/// Implement operationId : addPet
/// post /pet
Method addPet(requestMessage As petstoreproduction.requests.addPet, Output responseMessage As petstoreproduction.responses.addPet) As %Status
{
	Set sc = $$$OK, pHttpRequestIn = ##class(%Net.HttpRequest).%New(), responseMessage = ##class(petstoreproduction.responses.addPet).%New()
	$$$QuitOnError(requestMessage.LoadHttpRequestObject(pHttpRequestIn))
	$$$QuitOnError(..Adapter.SendFormDataArray(.pHttpResponse, "post", pHttpRequestIn, , , ..Adapter.URL_requestMessage.%URL))
	$$$QuitOnError(responseMessage.LoadFromResponse(pHttpResponse, "addPet"))
	Quit sc
}
...
}
}

 

HTTP server-side REST application

Set sc = ##class(dc.openapi.server.ServerAppGenerator).Generate("petstoreserver", "https://petstore3.swagger.io/api/v3/openapi.json", "/petstore/api")

The first argument is the package name to generate classes.  The second follows the same rules as the HTTP client.  The third argument is not mandatory, but if present a web application will be created with the given name (be careful to give a valid web application name).

The class petstoreserver.disp (dispatch %CSP.REST class) looks like a code generated by ^%REST, performs many checks to accept or reject the request and calls the related service ClassMethod implementation in petstoreserver.impl.  The main difference is the argument passed to the implementation method, this is petstoreserver.requests object. Example : 

Class petstoreserver.disp Extends %CSP.REST [ ProcedureBlock ]
{

Parameter CHARSET = "utf-8";
Parameter CONVERTINPUTSTREAM = 1;
Parameter IgnoreWrites = 1;
Parameter SpecificationClass = "petstoreserver.Spec";
/// Process request post /pet
ClassMethod addPet() As %Status
{
	Set sc = $$$OK
	Try{
		Set acceptedMedia = $ListFromString("application/json,application/xml,application/x-www-form-urlencoded")
		If '$ListFind(acceptedMedia,$$$LOWER(%request.ContentType)) {
			 Do ##class(%REST.Impl).%ReportRESTError(..#HTTP415UNSUPPORTEDMEDIATYPE,$$$ERROR($$$RESTContentType,%request.ContentType)) Quit
		}
		Do ##class(%REST.Impl).%SetContentType($Get(%request.CgiEnvs("HTTP_ACCEPT")))
		If '##class(%REST.Impl).%CheckAccepts("application/xml,application/json") Do ##class(%REST.Impl).%ReportRESTError(..#HTTP406NOTACCEPTABLE,$$$ERROR($$$RESTBadAccepts)) Quit
		If '$isobject(%request.Content) Do ##class(%REST.Impl).%ReportRESTError(..#HTTP400BADREQUEST,$$$ERROR($$$RESTRequired,"body")) Quit
		Set requestMessage = ##class(petstoreserver.requests.addPet).%New()
		Do requestMessage.LoadFromRequest(%request)
		Set scValidateRequest = requestMessage.RequestValidate()
		If $$$ISERR(scValidateRequest) Do ##class(%REST.Impl).%ReportRESTError(..#HTTP400BADREQUEST,$$$ERROR(5001,"Invalid requestMessage object.")) Quit
		Set response = ##class(petstoreserver.impl).addPet(requestMessage)
		Do ##class(petstoreserver.impl).%WriteResponse(response)
	} Catch(ex) {
		Do ##class(%REST.Impl).%ReportRESTError(..#HTTP500INTERNALSERVERERROR,ex.AsStatus(),$parameter("petstoreserver.impl","ExposeServerExceptions"))
	}
	Quit sc
}
...
}

As you can see, the dispatch class call “LoadFromRequest” and “RequestValidate” before calling the implementation method.  These methods have a default implementation, but the code generator cannot cover all cases.  Currently, the most common cases are automatically handled as parameters in "query", "headers", "path" and body with the content type “application/json”, “application/octet-stream” or “multipart/form-data”.  The developer has to check the implementation to check\complete if needed (by default the code generator set $$$ThrowStatus($$$ERROR($$$NotImplemented)) for unsupported case).

 

 
Example of request class :

 

As with the ^%REST usage, the "petstoreserver.impl" class contains all the methods related to the services that the developer has to implement. 

Class petstoreserver.impl Extends %REST.Impl [ ProcedureBlock ]
{

Parameter ExposeServerExceptions = 1;
/// Service implemntation for post /pet
ClassMethod addPet(messageRequest As petstoreserver.requests.addPet) As %DynamicObject
{
	; Implement your service here.
	; Return {}
	$$$ThrowStatus($$$ERROR($$$NotImplemented))
}

...
}

 

Short description of the generated packages

Package name \ Class Name

Type

Description

petstoreclient.model


petstoreproduction.model

Client-side and server-side

It contains all models.  These classes extend %JSON.Adaptor to ease loading objects from JSON.  

If a production is generated these classes also extend %Persistent.

petstoreclient.requests

 


petstoreproduction.requests

Client-side and server-side

Object used for easily initialize %Net.HttpRequest.  There is a class per operation defined in the specification.
If production is generated these classes extend Ens.Request.

Note: The implementation of this class is different if it’s generated for server-side or client-side purposes.  In the case of the client-side, all class contains a method “LoadHttpRequestObject” allowing to load a “%Net.HttpRequest” from this class properties.

If the classes are generated for server-side purposes, each class contains a method “LoadFromRequest” in order to load the instance from “%request” object.

petstoreclient.responses


petstoreproduction.responses

Client-side and server-side

It’s the opposite of petstoreclient. requests.  It allows handling the response of a %Net.HttpRequest.
If production is generated these classes extend Ens.Response

petstoreclient.HttpClient

Client-side

Contains all methods to execute HTTP requests, there is one method by operation defined in the OpenAPI specification.

petstoreproduction. bo.Operation

Client-side

Operation class has a method per operation defined in the OpenAPI specification.

petstoreproduction.bp

Client-side

Two default business processes are defined: sync and async.

petstoreproduction.bs

Client-side

Contains all empty businesses services to implement.

petstoreproduction.Production

Client-side

Production configuration setting.

petstoreserver.disp

Server-side

Class dispatch %CSP.REST.

petstoreserver.Spec

Server-side

This class contains the OpenAPI specification in an XData block.

petstoreserver.impl

Server-side

It contains all empty methods related to operations defined in the OpenAPI specification.  This is the class (extend %REST.Impl) where developers have to implement services.

Development status

OpenAPI-Suite is still a very young product and needs to be more tested and then improved.  The support of OpenAPI 3 is partial, more possibilities could be supported. 

The tests were done with the public specification https://petstore3.swagger.io/api/v3/openapi.json and two other relatively simple ones.  Of course, this is not enough to cover all cases.  If you have any specifications to share, I would be happy to use them for my tests.

I think the foundation of the project is good and it can easily evolve, for example, be extended to support AsyncAPI.

Do not hesitate to leave feedback.

I hope you will enjoy this application and deserves your support for the developer tools contest.

Thank you for reading.

23 Comments
讨论 (23)7
登录或注册以继续
问题
· 二月 1, 2023

NEED NUMBER ROWS IN SQL SELECT

Hi guys,

I'm trying find a way to create a row with the number of each row.

For example:

In the SQL above, i want a way to create a collum with the number of corresponding row, in this case:

1

2

3

In SQl SERVER i can use FOR, RANK or ROM_NUMBER to do this, but, i can't find a way to do the same in cache SQL.

 

Can someone help me?

Thank you.

4 Comments
讨论 (4)2
登录或注册以继续
InterSystems 官方
· 一月 31, 2023

InterSystems announces General Availability of InterSystems IRIS, IRIS for Health, HealthShare Health Connect, & InterSystems IRIS Studio 2022.3

InterSystems is pleased to announce the 2022.3 release of InterSystems IRIS Data Platform, InterSystems IRIS for Health, HealthShare Health Connect, and InterSystems IRIS Studio are now Generally Available (GA).

2022.3 is a Continuous Delivery (CD) release. Many updates and enhancements have been added in 2022.3, in SQL management, cloud integration, Kafka and JMS adapters, the SQL Loader, and other areas. The new FHIR SQL Builder and enhancements to Columnar Storage are included, but both are still experimental features (not for production, with an active Early Access Program in place).  

 

Release Highlights

Platform Updates

InterSystems IRIS Data Platform 2022.3 expands support to include the following new & updated operating systems for production workloads:

  • Oracle Linux 9
  • SUSE 15 SP4.

SQL Enhancements

InterSystems IRIS SQL now offers a machine-readable format for query plans. Using this new option, the $SYSTEM.SQL.Explain() method will now produce a JSON-based rendering of the query plan with significantly more detail on the plan itself as well as the tables and indices it accesses. Where the previous XML-based format used simple English phrases to describe different steps, the new format is easier to consumer for tools that wish to perform more thorough analysis or graphical rendering of query plans. 

This release introduces an opt-in capability to sample runtime parameters for SQL statements. The Statement Index already records a rich set of metadata such as detailed runtime statistics and query plans for each statement, which typically has any literals substituted for placeholders that parameterize the cached query code. Now, the statement index can be extended with a sampling of the actual runtime values for those parameters. These can then be combined with the normalized statement text to, for example, build a representative workload that can be run against another deployment, for example to benchmark a new hardware environment or experiment with a different set of indices for the schema. 

InterSystems IRIS SQL now supports CREATE SCHEMA and DROP SCHEMA commands to include in scripts for setting up and tearing down your application environment.  

Speed, Scale, & Security

This release offers full elasticity for InterSystems IRIS Sharded clusters. DBAs can now call an API method to mark a shard for removal. Once this method is issued, data immediately gets offloaded from the designated shard to other data nodes in the cluster and automatically disconnect the node when all data buckets have been successfully moved to other shards. This process leverages the same mechanism as Online Rebalancing, meaning users can continue to query sharded tables and ingest data into them while the data is being moved.  

Analytics & AI

  • Updated version of InterSystems Reports (Logi Report 19.2):  Key improvements are:
    • Bookmark feature - save parameters and filters on a web report
    • Report Studio available on the Report Server to provide additional report editing directly from Server
  • Updated version of Adaptive Analytics (AtScale 2022.3) Key improvements are:
    • Support for the timeline feature in Microsoft Excel
    • Data Catalog API to expose the AtSCale Semantic Layer to data catalog vendors

More details on all these features are available through these links below:

 

How to get the software

As usual, CD releases come with classic installation packages for all supported platforms, as well as container images in Docker container format.  For a complete list, refer to the Supported Platforms document.

Installation packages and preview keys are available from the WRC's Continuous Delivery Releases site or through the evaluation services website.

InterSystems IRIS Studio is available from the Components distribution page.

Container images for both Enterprise and Community Editions of InterSystems IRIS and IRIS for Health and all corresponding components are available from the new InterSystems Container Registry web interface.

yesFor additional information about docker commands, please see this post:: Announcing the InterSystems Container Registry web user interface. The build number for this developer preview release is 2022.3.0.606.0


FHIR SQL Builder

As we described before, FHIR SQL Builder is an experimental feature and is part of an active EAP (Early Access Program).To get access to this feature, please, email Patrick Jamieson, Product Manager for IRIS for Health at patrick.jamieson@intersystems.com.

3 Comments
讨论 (3)2
登录或注册以继续