Skip to content

Commit cd09032

Browse files
disqdisq
authored
feat: Add JSON schema to mssql destination plugin (#16438)
Closes #16434
1 parent 9eaedb7 commit cd09032

10 files changed

Lines changed: 410 additions & 77 deletions

File tree

‎.github/workflows/dest_mssql.yml‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -70,6 +70,12 @@ jobs:
7070
args: "--config ../../.golangci.yml"
7171
skip-pkg-cache: true
7272
skip-build-cache: true
73+
- name: gen
74+
if: github.event_name == 'pull_request'
75+
run: make gen
76+
- name: Fail if generation updated files
77+
if: github.event_name == 'pull_request'
78+
run: test "$(git status -s | wc -l)" -eq 0 || (git status -s; exit 1)
7379
- run: go mod tidy
7480
- name: Build
7581
run: go build .

‎plugins/destination/mssql/Makefile‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7,3 +7,11 @@ test:
77
.PHONY: lint
88
lint:
99
golangci-lint run --config ../../.golangci.yml
10+
11+
.PHONY: gen-spec-schema
12+
gen-spec-schema:
13+
go run client/spec/gen/main.go
14+
15+
# All gen targets
16+
.PHONY: gen
17+
gen: gen-spec-schema

‎plugins/destination/mssql/client/schema.json‎

Lines changed: 64 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎plugins/destination/mssql/client/spec.go‎

Lines changed: 40 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,32 +1,53 @@
11
package client
22

33
import (
4+
_ "embed"
45
"errors"
56
"strings"
67
"time"
78

89
"github.com/cloudquery/plugin-sdk/v4/configtype"
10+
"github.com/invopop/jsonschema"
911
mssql "github.com/microsoft/go-mssqldb"
1012
"github.com/microsoft/go-mssqldb/azuread"
1113
)
1214

1315
type AuthMode string
1416

1517
const (
16-
AuthModeAzure = "azure"
17-
AuthModeMS = "ms"
18+
AuthModeAzure = AuthMode("azure")
19+
AuthModeMS = AuthMode("ms")
1820
)
1921

2022
type Spec struct {
21-
ConnectionString string `json:"connection_string,omitempty"`
22-
AuthMode AuthMode `json:"auth_mode,omitempty"`
23-
Schema string `json:"schema,omitempty"`
23+
// Connection string to connect to the database.
24+
// See [SDK documentation](https://github.com/microsoft/go-mssqldb#connection-parameters-and-dsn) for details.
25+
ConnectionString string `json:"connection_string" jsonschema:"required,minLength=1"`
2426

25-
BatchSize int `json:"batch_size,omitempty"`
26-
BatchSizeBytes int `json:"batch_size_bytes,omitempty"`
27-
BatchTimeout *configtype.Duration `json:"batch_timeout,omitempty"`
27+
// If you need to authenticate via Azure Active Directory ensure you specify `azure` value.
28+
// See [SDK documentation](https://github.com/microsoft/go-mssqldb#azure-active-directory-authentication) for more information.
29+
// Supported values:
30+
//
31+
// - `ms` _connect to Microsoft SQL Server instance_
32+
// - `azure` _connect to Azure SQL Server instance_
33+
AuthMode AuthMode `json:"auth_mode,omitempty" jsonschema:"default=ms"`
34+
35+
// By default, Microsoft SQL Server destination plugin will use the [default](https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/ownership-and-user-schema-separation?view=sql-server-ver16#the-dbo-schema) schema named `dbo`.
36+
Schema string `json:"schema,omitempty" jsonschema:"default=dbo"`
37+
38+
// Maximum amount of items that may be grouped together to be written in a single write.
39+
BatchSize int `json:"batch_size,omitempty" jsonschema:"minimum=1,default=1000"`
40+
41+
// Maximum size of items that may be grouped together to be written in a single write.
42+
BatchSizeBytes int `json:"batch_size_bytes,omitempty" jsonschema:"minimum=1,default=5242880"`
43+
44+
// Timeout for writing a single batch.
45+
BatchTimeout *configtype.Duration `json:"batch_timeout,omitempty"`
2846
}
2947

48+
//go:embed schema.json
49+
var JSONSchema string
50+
3051
func (s *Spec) Validate() error {
3152
if len(s.ConnectionString) == 0 {
3253
return errors.New("missing required \"connection_string\" option")
@@ -59,8 +80,18 @@ func (s *Spec) SetDefaults() {
5980
}
6081

6182
func (s *Spec) Connector() (*mssql.Connector, error) {
62-
if strings.EqualFold(string(s.AuthMode), AuthModeAzure) {
83+
if strings.EqualFold(string(s.AuthMode), string(AuthModeAzure)) {
6384
return azuread.NewConnector(s.ConnectionString)
6485
}
6586
return mssql.NewConnector(s.ConnectionString)
6687
}
88+
89+
func (AuthMode) JSONSchemaExtend(sc *jsonschema.Schema) {
90+
sc.Type = "string"
91+
sc.Enum = []any{AuthModeAzure, AuthModeMS}
92+
}
93+
94+
func (Spec) JSONSchemaExtend(sc *jsonschema.Schema) {
95+
batchTimeout := sc.Properties.Value("batch_timeout").OneOf[0] // 0 - val, 1 - null
96+
batchTimeout.Default = "20s"
97+
}

‎plugins/destination/mssql/client/spec/gen/main.go‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,26 @@
1+
package main
2+
3+
import (
4+
"fmt"
5+
"log"
6+
"path"
7+
"runtime"
8+
9+
"github.com/cloudquery/cloudquery/plugins/destination/mssql/client"
10+
cqjsonschema "github.com/cloudquery/codegen/jsonschema"
11+
)
12+
13+
func main() {
14+
fmt.Println("Generating JSON schema for plugin spec")
15+
cqjsonschema.GenerateIntoFile(new(client.Spec), path.Join(currDir(), "../..", "schema.json"),
16+
cqjsonschema.WithAddGoComments("github.com/cloudquery/cloudquery/plugins/destination/mssql/client", path.Join(currDir(), "../..")),
17+
)
18+
}
19+
20+
func currDir() string {
21+
_, filename, _, ok := runtime.Caller(0)
22+
if !ok {
23+
log.Fatal("Failed to get caller information")
24+
}
25+
return path.Dir(filename)
26+
}

‎plugins/destination/mssql/client/spec_test.go‎

Lines changed: 93 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,93 @@
1+
package client_test
2+
3+
import (
4+
"testing"
5+
6+
"github.com/cloudquery/cloudquery/plugins/destination/mssql/client"
7+
"github.com/cloudquery/codegen/jsonschema"
8+
)
9+
10+
func TestJSONSchema(t *testing.T) {
11+
jsonschema.TestJSONSchema(t, client.JSONSchema, []jsonschema.TestCase{
12+
{
13+
Name: "empty spec",
14+
Spec: `{}`,
15+
Err: true,
16+
},
17+
{
18+
Name: "spec with connection_string",
19+
Spec: `{"connection_string": "conn"}`,
20+
},
21+
{
22+
Name: "spec with connection_string and schema",
23+
Spec: `{"connection_string": "conn", "schema":"foo"}`,
24+
},
25+
{
26+
Name: "spec with connection_string and auth_mode azure",
27+
Spec: `{"connection_string": "conn", "auth_mode":"azure"}`,
28+
},
29+
{
30+
Name: "spec with connection_string and auth_mode ms",
31+
Spec: `{"connection_string": "conn", "auth_mode":"ms"}`,
32+
},
33+
{
34+
Name: "spec with connection_string and empty auth_mode",
35+
Spec: `{"connection_string": "conn", "auth_mode":""}`,
36+
Err: true,
37+
},
38+
{
39+
Name: "spec with connection_string and invalid auth_mode",
40+
Spec: `{"connection_string": "conn", "auth_mode":"invalid"}`,
41+
Err: true,
42+
},
43+
{
44+
Name: "spec with bool connection_string",
45+
Spec: `{"connection_string": true}`,
46+
Err: true,
47+
},
48+
{
49+
Name: "spec with null connection_string",
50+
Spec: `{"connection_string": null}`,
51+
Err: true,
52+
},
53+
{
54+
Name: "spec with int connection_string",
55+
Spec: `{"connection_string": 123}`,
56+
Err: true,
57+
},
58+
{
59+
Name: "spec with bool batch_size",
60+
Spec: `{"connection_string": "abc", "batch_size":false}`,
61+
Err: true,
62+
},
63+
{
64+
Name: "spec with null batch_size",
65+
Spec: `{"connection_string": "abc", "batch_size":null}`,
66+
Err: true,
67+
},
68+
{
69+
Name: "spec with string batch_size",
70+
Spec: `{"connection_string": "abc", "batch_size":"str"}`,
71+
Err: true,
72+
},
73+
{
74+
Name: "spec with array batch_size",
75+
Spec: `{"connection_string": "abc", "batch_size":["abc"]}`,
76+
Err: true,
77+
},
78+
{
79+
Name: "spec with proper batch_size",
80+
Spec: `{"connection_string": "abc", "batch_size":7}`,
81+
},
82+
{
83+
Name: "spec with bool batch_timeout",
84+
Spec: `{"connection_string": "abc", "batch_timeout":true}`,
85+
Err: true,
86+
},
87+
{
88+
Name: "spec with unknown field",
89+
Spec: `{"connection_string": "abc", "unknown": "test"}`,
90+
Err: true,
91+
},
92+
})
93+
}

‎plugins/destination/mssql/docs/configuration.md‎

Lines changed: 6 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -19,13 +19,12 @@ This is the (nested) spec used by the Microsoft SQL Server destination plugin.
1919
- `connection_string` (`string`) (required)
2020

2121
Connection string to connect to the database.
22-
See [SDK documentation](https://github.com/microsoft/go-mssqldb#connection-parameters-and-dsn) for more details.
22+
See [SDK documentation](https://github.com/microsoft/go-mssqldb#connection-parameters-and-dsn) for details.
2323

2424
- `auth_mode` (`string`) (optional) (default: `ms`)
2525

2626
If you need to authenticate via Azure Active Directory ensure you specify `azure` value.
27-
See [SDK documentation](https://github.com/microsoft/go-mssqldb#azure-active-directory-authentication) for more
28-
information.
27+
See [SDK documentation](https://github.com/microsoft/go-mssqldb#azure-active-directory-authentication) for more information.
2928
Supported values:
3029

3130
- `ms` _connect to Microsoft SQL Server instance_
@@ -34,21 +33,19 @@ This is the (nested) spec used by the Microsoft SQL Server destination plugin.
3433
- `schema` (`string`) (optional) (default: `dbo`)
3534

3635
Schema name to be used.
37-
By default, Microsoft SQL Server destination plugin will use the
38-
[default](https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/ownership-and-user-schema-separation?view=sql-server-ver16#the-dbo-schema)
39-
schema named `dbo`.
36+
By default, Microsoft SQL Server destination plugin will use the [default](https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/ownership-and-user-schema-separation?view=sql-server-ver16#the-dbo-schema) schema named `dbo`.
4037

4138
- `batch_size` (`integer`) (optional) (default: `1000`)
4239

43-
This parameter controls the maximum amount of items may be grouped together to be written as a single write.
40+
Maximum amount of items that may be grouped together to be written in a single write.
4441

4542
- `batch_size_bytes` (`integer`) (optional) (default: `5242880` (= 5 MiB))
4643

47-
This parameter controls the maximum size of items that may be grouped together to be written as a single write.
44+
Maximum size of items that may be grouped together to be written in a single write.
4845

4946
- `batch_timeout` (`duration`) (optional) (default: `20s` (= 20 seconds))
5047

51-
This parameter controls the timeout for writing a single batch.
48+
Timeout for writing a single batch.
5249

5350
### Verbose logging for debug
5451

0 commit comments

Comments
 (0)