JSon Schema for validation / proof of concept

DML.json

{
  "copyright": [
    "Copyright 2013 Red Hat, Inc. and/or its affiliates.",
    "This file is part of lightblue.",
    "This program is free software: you can redistribute it and/or modify",
    "it under the terms of the GNU General Public License as published by",
    "the Free Software Foundation, either version 3 of the License, or",
    "(at your option) any later version.",
    "This program is distributed in the hope that it will be useful,",
    "but WITHOUT ANY WARRANTY; without even the implied warranty of",
    "MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the",
    "GNU General Public License for more details.",
    "You should have received a copy of the GNU General Public License",
    "along with this program.  If not, see <http://www.gnu.org/licenses/>."
  ],
  "type": "object",
  "$schema": "http://json-schema.org/draft-03/schema",
  "id": "dml",
  "properties": {
    "delete": {
      "$ref": "#/properties/insert",
      "id": "delete"
    },
    "fetch": {
      "$ref": "#/properties/insert",
      "id": "fetch"
    },
    "insert": {
      "type": "object",
      "id": "insert",
      "properties": {
        "bindings": {
          "type": "object",
          "properties": {
            "in": {
              "type": "array",
              "items": {
                "type": "object",
                "properties": {
                  "column": {
                    "type": "string"
                  },
                  "path": {
                    "type": "string"
                  }
                }
              }
            },
            "out": {
              "$ref": "#/properties/insert/bindings/in"
            }
          }
        },
        "expressions": {
          "type": "array",
          "minItems": 1,
          "items": {
            "oneOf": [{
              "type": "object",
              "id": "statement",
              "properties": {
                "datasource": {
                  "type": "string",
                  "id": "datasource"
                },
                "sql": {
                  "type": "string",
                  "id": "sql"
                },
                "type": {
                  "id": "type",
                  "enum": ["select", "insert", "update", "delete", "call"]
                }
              }
            }, {
              "type": "object",
              "properties": {
                "if": {
                  "type": "object",
                  "properties": {
                    "oneOf": [
                      "$or": {
                        "description": "This piece of schema will be in conditional.json to avoid duplication",
                        "type": "array",
                        "minItems": 2,
                        "items": {
                          "anyOf": [{
                            "$ref": "conditional.json#/properties/oneOf/$or"
                          }, {
                            "$ref": "conditional.json#/properties/oneOf/$any"
                          }, {
                            "$ref": "conditional.json#/properties/oneOf/$and"
                          }, {
                            "$ref": "conditional.json#/properties/oneOf/$all"
                          }, {
                            "$ref": "conditional.json#/properties/oneOf/$not"
                          }, {
                            "description": "This schema will be in path-empty.json",
                            "type": "object",
                            "properties": {
                              "conditional": {
                                "enum": ["$isempty"]
                              },
                              "path1": {
                                "type": "string"
                              }
                            }
                          }, {
                            "description": "This schema will be in path-check-path.json",
                            "type": "object",
                            "properties": {
                              "conditional": {
                                "enum": ["$greaterThan", "$lessThan", "$equalTo", "$notEqualTo", "$greaterThanOrEqualTo", "$lessThanOrEqualTo", "$in", "$notIn", "$contains"]
                              },
                              "path1": {
                                "type": "string"
                              },
                              "path2": {
                                "type": "string"
                              }
                            }
                          }, {
                            "description": "This schema will be in path-check-value.json",
                            "type": "object",
                            "properties": {
                              "conditional": {
                                "enum": ["$greaterThan", "$lessThan", "$equalTo", "$notEqualTo", "$greaterThanOrEqualTo", "$lessThanOrEqualTo", "$in", "$notIn", "$contains"]
                              },
                              "path1": {
                                "type": "string"
                              },
                              "value2": {
                                "type": "string"
                              }
                            }
                          }, {
                            "description": "This schema will be in path-check-values.json",
                            "type": "object",
                            "properties": {
                              "conditional": {
                                "enum": ["$greaterThan", "$lessThan", "$equalTo", "$notEqualTo", "$greaterThanOrEqualTo", "$lessThanOrEqualTo", "$in", "$notIn", "$contains"]
                              },
                              "path1": {
                                "type": "string"
                              },
                              "values2": {
                                "type": "array",
                                "minItems": 1,
                                "items": {
                                  "type": "string"
                                },
                                "uniqueItems": true
                              }
                            }
                          }, {
                            "description": "This schema will be in path-regex.json",
                            "type": "object",
                            "properties": {
                              "path": {
                                "type": "string"
                              },
                              "regex": {
                                "type": "string",
                                "format": "regex"
                              },
                              "case_insensitive": {
                                "type": "boolean"
                              },
                              "multiline": {
                                "type": "boolean"
                              },
                              "extended": {
                                "type": "boolean"
                              },
                              "dotall": {
                                "type": "boolean"
                              }
                            }
                          }]
                        }
                      }, {
                        "$ref": "conditional.json#/properties/oneOf/$any"
                      }, {
                        "$ref": "conditional.json#/properties/oneOf/$and"
                      }, {
                        "$ref": "conditional.json#/properties/oneOf/$all"
                      }, {
                        "$ref": "conditional.json#/properties/oneOf/$not"
                      }, {
                        "$ref": "path-empty.json#/"
                      }, {
                        "$ref": "path-check-path.json#"
                      }, {
                        "$ref": "path-check-value.json#"
                      }, {
                        "$ref": "path-check-values.json#"
                      }, {
                        "$ref": "path-regex.json#"
                      }
                    ]
                  }
                },
                "then": {
                  "oneOf": [{
                    "$ref": "construct.json#expressions"
                  }, {
                    "enum": ["$fail", "$continue", "$break"]
                  }]
                },
                "elseIf": {
                  "type": "array",
                  "items": {
                    "allOf": [{
                      "$ref": "#properties/if"
                    }, {
                      "$ref": "#properties/then"
                    }]
                  }
                },
                "else": {
                  "oneOf": [{
                    "$ref": "construct.json#expressions"
                  }, {
                    "enum": ["$fail", "$continue", "$break"]
                  }]
                }
              }
            }, {
              "type": "object",
              "properties": {
                "$foreach": {
                  "properties": {
                    "iterateOverPath": {
                      "type": "string"
                    },
                    "expressions": {
                      "$ref": "construct.json#expressions"
                    }
                  }
                }
              }
            }, {
              "type": "object",
              "properties": {
                "$foreach": {
                  "properties": {
                    "loopTimes": {
                      "type": "number"
                    },
                    "loopVariable": {
                      "type": "string"
                    },
                    "expressions": {
                      "$ref": "construct.json#expressions"
                    }
                  }
                }
              }
            }]
          }
        }
      }
    },
    "save": {
      "$ref": "#/properties/insert"
    },
    "update": {
      "$ref": "#/properties/insert"
    }
  }
}

Few things:

• default datasource is the datasource given in datastore in metadata. So, we can remove the datasource element, but keep the optional datasource for each statement.
• SELECT statements would be structurally different from others. In particular: SELECT will have the predicates part that we will build based on the query, and that should contain the field->prediacateField mappings
• What are pre-chec post-check, etc. Also, what's the stuff about exceptions? Things fail if there are exceptions.

I didn't get any notification from your comment here.

I updated the schema, removing the default datasource as you mentioned.

About the Select and the temp variables, sorry, We mentioned in the meeting and I also thought about it but we I made the schema I forget. I was thinking (in the beginning) that binding would work for both temp variable or Document's fields (but it would have a different name in the schema to make sense). But after awhile I'm thinking all in one place would be harder to maintain (or maybe even to build). Well, for now I will add to the same place (biding), adding a new field which would be null or not (depending if it would map a path or a temp variable) (the temporary variable can be recursive as it may hold the output from many rows to iterate)(I also thought in a case where all these rows would be needed in the where predicate, but that case I think user should make a subselect).

Well, The pre-check and post-check are Strings that will be evaluated before calling the checked-next-statement (which would be another statement, using recursive) and after calling it. The other items in statements field would be free from conditional evaluated from the expression in the checked-next-statement field.

The exceptionInCheckFailured field would help there would be case that no returned result or other problem should be considered a problem or a not . We can remove if you dont see any scenario for that

Lets get rid of needsWhere and needsAnd. They are only required for select statements, and we can assume we won't need "where" and "and". So, write the select statement to end with "where" and "and"

Instead of pre-check and post-check, we can add conditional constructs as we need. For example:
[
{sql1},
{sql2},
{ $ifempty : {sql2} $then : $fail }
]

Something like that. Don't take this verbatim, we should add these kinds of features once we have use cases for them.

I'd have two schemas: one for insert/update/delete, and one for select.

OK, I will wipe needsWhere and needsAnd from this schema.

OK about the conditional constructs. I thought sth, I will fix it during the morning

Not sure about having more then one schema. I think I got you idea, but I'm afraid that select schema would be necessary for insert/update/delete.

Make the fixes and puts some other things I think is related and it would need. I also think that we would need an 'transformation' field to apply some transformation into a variable (or maybe the user would do that using 'select from dual' or binding another different variable just for that)

Note: we can take a look on the older revisions (like https://gist.github.com/luan-cestari/5a1d5a360b93cb4f38d2/8a0a25bd67d12c72058c9312a6f1ece85c7036ea ) to check some removed features (that may come back if we found an useful scenario)

The lastest version is in the following repository -> https://github.com/lightblue-platform/lightblue/tree/SQLFeatureBranch/lightblue-rdbms/metadata/src/main/resources/json-schema/metadata/rdbms/metadata