Working with JsonSchema

[magick93]

Hello

I have a lot of jsonschema files that I'm looking to work with, as data.

I've managed to convert jsonschema to cue by doing:

1. Writing a script using https://github.com/APIDevTools/json-schema-ref-parser that will de-refefence any referenced schemas
2. Save the output of the above
3. Remove "$schema": "http://json-schema.org/draft-04/schema#", from the json schema
4. cue import de-referenced and edited schema

Example cue

version:     "4.3.0"
title:       "AmountType"
description: "An amount is a number of monetary units specified in a currency."
type:        "object"
properties: {
	value: {
		description: "The value of the amount."
		type:        "number"
	}
	currency: {
		description: "The currency of the amount."
		version:     "4.3.0"
		title:       "CurrencyCodeList"
		enum: 		["USD", "NZD"]
		enumNames: 	["US Dollar", "New Zealand Dollar"]
		links: [{
			href: "https://www.iso.org/iso-4217-currency-codes.html"
		}]
	}
}
required: [
	"value",
]

Templates

CREATE TABLE IF NOT EXISTS {{ snake .title }} (
    {{$first := true}}{{range $key, $value := .properties}}{{if $first}}{{$first = false}}{{else}},{{end}}
    {{ $key}} {{ template "sql_col.go" . }}
    {{end}}
)

COMMENT ON TABLE {{ snake .title }} $${{ .description }}$$

{{ range $key, $val :=  .properties -}}
COMMENT ON COLUMN {{ snake $key }} $${{.description}}$$
{{ end }}

Partial template for sql table columns

{{if .title}} int4 REFERENCES {{snake .title}}(id){{else}}{{.type}}{{end}}

Questions

The output from is:

CREATE TABLE IF NOT EXISTS amount_type (
    
    currency  int4 REFERENCES currency_code_list(id)
    ,
    value number
    
)
...

1. How can I improve the formatting of the output so that the , comma is at the end of the line and not on a line of its own.
2. How can I pass other data, or all data, to the partials? I'd like to have NOT NULL which comes from the required array in the cue file?
3. As I'm creating postgres ddl files, how can I have some kind of lookup, such that a jsonschema type can be replaced by a postgres type?

Thanks

[verdverm]

See the txtar file below for details, I changed a bit of your template and added an extra file to demonstrate.

1. I flip this around a bit. Instead of checking if we are at the first entry, check if we are at the last entry, and skip the comma there. I keep a counter for the map, for lists you can skip this, but they end up looking basically the same. The consistency is nice. Sometimes you have to play with the spacing removers (-) from text/template
2. For passing multiple inputs to a partial template, use the (dict "key" .value "ROOT" .) helper.
3. You can use a struct like a map to make a lookup table.

For both 2 & 3, I would enrich the input in CUE before sending to the template. Then just reference the values. So instead of needing to pass ROOT or required to the partial, write some CUE to enrich the in properties.

hof gen example.cue -T example.sql -P sql_col.sql -P extra.sql

-- example.cue --
version:     "4.3.0"
title:       "AmountType"
description: "An amount is a number of monetary units specified in a currency."
type:        "object"
properties: P={
        value: {
                description: "The value of the amount."
                type:        "number"
        }
        currency: {
                description: "The currency of the amount."
                version:     "4.3.0"
                title:       "CurrencyCodeList"
                enum:           ["USD", "NZD"]
                enumNames:      ["US Dollar", "New Zealand Dollar"]
                links: [{
                        href: "https://www.iso.org/iso-4217-currency-codes.html"
                }]
        }

        // required property enrichment
        for _, v in required {
                (v): required: true
        }

        // propertry type enrichment  (fixed, P was above with 'properties')
        [string]: P={
                if P.type != _|_ {
                        psqlType: json2psql[P.type]
                }
        }
}

required: [
        "value",
]

json2psql: {
  "number": "int"
  "boolean": "bool"
}

-- example.sql --
CREATE TABLE IF NOT EXISTS {{ snake .title }} (
        {{$len := len .properties }}
        {{$i := 0}}
        {{- range $key, $value := .properties }}{{ $i = add $i 1 -}}
        {{- $key}}{{ template "sql_col.sql" . -}}{{- if lt $i $len}},{{end }}
        {{end}}
)

COMMENT ON TABLE {{ snake .title }} $${{ .description }}$$

{{ range $key, $val :=  .properties -}}
COMMENT ON COLUMN {{ snake $key }} $${{.description}}$$
{{ end }}


{{ template "extra.sql" (dict "ROOT" . "property" .properties.value)}}
-- extra.sql --
/*
title: {{ .ROOT.title }}

property:
{{ yaml .property }}

*/

-- sql_col.sql --
{{- if .title}} int4 REFERENCES {{snake .title}}(id){{else}}{{.type}}{{end -}}

---

edit:

There is another version of the example.cue below featuring a nice pattern for applying schemas and transformations to the properties

[verdverm]

Yes, you should be able to put them in a separate file and add that file to the CUE entrypoints. As long as the fields and values appear in the same place after merging, then they will work just the same as if they were in the file. You can even put a bunch of enrichers in a file, setting it up so only those needed are applied. Data dependent, but doable

[magick93]

cue export full-AmountType.cue enrich.cue - wow! Cue is cute!

[magick93]

hof gen full-AmountType.cue enrich.cue -P sql_col.go -T sql.go- @verdverm - this is a very powerful tool you've created!

[magick93]

Its not quite working as expected.

• the json2psql lookup / replace isnt being applied.

version:     "4.3.0"
title:       string
description: "An amount is a number of monetary units specified in a currency."
type:        "object"
properties: P={
        //this isnt being applied
        [string]: {
                if P.type != _|_ {
                        psqlType: json2psql[P.type]
                }                
        }

        // required property enrichment
        for _, v in required {
                (v): required: true
        }

        [string]: { table: title   }

        
        
}

required: [
        "value",
]

json2psql: {
  "number": "int"
  "boolean": "bool"
}

[verdverm]

My bad, should have paid closer attention. The fix is to move the P= like so

properties: {
        // move the P= to the pattern constraint.
        [string]: P={

We want it on the value from the pattern constraint we apply to every property (regular fields in properties).

https://cuetorials.com/overview/types-and-values/#pattern-matching-constraints

Often, a schema is also applied, there is a nice way to write these on separate lines

version:     "4.3.0"
title:       "AmountType"
description: "An amount is a number of monetary units specified in a currency."
type:        "object"
properties: {
	value: {
		description: "The value of the amount."
		type:        "number"
	}
	currency: {
		description: "The currency of the amount."
		version:     "4.3.0"
		title:       "CurrencyCodeList"
		enum: ["USD", "NZD"]
		enumNames: ["US Dollar", "New Zealand Dollar"]
		links: [{
			href: "https://www.iso.org/iso-4217-currency-codes.html"
		}]
	}

	// required property enrichment
	for _, v in required {
		(v): required: true
	}
}

properties: [pName=string]: #Property & { name: pName }
properties: [string]: #TypeEnricher

#Property: {
	name:        string
	description: string
	type:        string // restrict with disjunction if desired

	... // open to allow any additonal fields
}

#TypeEnricher: T={
	#Property// embed proprty def
	if T.type != _|_ {
		psqlType: json2psql[T.type]
	}
}

required: [
	"value",
]

json2psql: {
	"number":  "int"
	"boolean": "bool"
}

It can be very helpful to use cue eval and cue export on the cue file to work with the code until you get the data you want for templating. You have the full power of CUE here, even more in the upcoming release, so you can do some pretty cool things. You can now use @tag()s to inject simple values

[verdverm]

With regards to hof and refs, there is support for the #/... type references. I can put together an example of those refs would be usable in your application.

I'm generally leaning on CUE for the foundational openapi / jsonschema support

---

edit, a short example how you can extract the reference in a template. I have an openapi hof mod that is in early prototype stages, but I still want to think through it before sharing, it's a bit hacky, probably will be until CUE improves in these areas.

hof gen data.cue -T template.txt

-- data.cue --
foo: bar: "baz"
cow: $ref: "#/foo.bar"

-- template.txt --
cow's ref is {{ dref ( index .cow "$ref" ) . }}

dref <path> <data> (or lookup, synonyms) is what accepts a string representation of a path (#/... or path.to.vals) (the dotpath notation is a little more powerful, the hash paths are actually converted to them internally)

We need the ( index .cow "$ref" ) to access the $ref field because go will not parse special chars, same is needed for hyphenated fields.

[verdverm]

@magick93 I added a small example here until I can write up a longer one based on openapi / jsonschema

There is a more general problem with making / dref'n paths, because you cannot have CUE values that refer to each other. This becomes a problem when you want to express database relationships like HasOne, OwnedBy from both directions. One of the sides needs to use a $ref like path to make it past the evaluator.