SQL Queries

The "sql_queries" section of the deployment object is used for defining Insert/Update/Delete functionality.  The structure of a query can be visualized below:
{
    "name": "update_user",
    "type": 102,
    "table": "users",
    "parameters": [
        {
            "name": "name",
            "column": "name"
        },
        {
            "name": "age",
            "column": "age"
        }
    ],
    "where": [
        {
            "name": "address",
            "column": "address",
            "static": true,
            "value": "",
            "modifier": 101, // caller
            "operator": 101 // equal
        }
    ]
}
Name
The name is the unique identifier for the query.  The name must be unique for the database that the query lives within.
Type
The query type is an identifier that specifies the action that the query will perform.  See the query conventions section for a list of queries and their enumerators.  The above query is an "update" query type.
Insert
The "insert" query type is used for creating a new row.  Its enumerator form is "1".  An insert statement must have at least one parameter, and cannot have where clauses.  If a column has a "not null" parameter, then it must have a parameter in an insert statement.
Update
The "update" query type is used to update one or many rows.  Its enumerator form is "2".  Update queries must have at least one parameter and one where clause.
Delete
The "delete" query type is used to delete one or many rows.  Its enumerator form is "2".  Delete queries must have at least one where clause, and cannot have any parameters.
Table
The table field specifies the table that the query is performed on.  As of right now, it is not possible for a single query to alter two tables.
Parameters
Parameters specify data that is set in a column.  A parameter has two required fields, and three optional fields.  The structure of a parameter is shown below:
{
    "name": "param1", // required
    "column": "col1", // required
    "static": true, // optional
    "value": "AnNvbWVfdmFsdWU=", // encoded "some_value" sting - optional, required if static is true
    "modifier": 0, // optional.  0 also stands for no modifier
}
A parameter's name is used to uniquely identify the parameter.  The name must be unique across both the parameters and the where clauses  When the query is transpiled to an executable, the name will be used to identify your input.
A parameter's column is used to identify the column that the parameter is inserting / updating.  Each column can only be specified at most once, but are not required unless it has a "not null" attribute.
In general, it is best practice to name parameters after the column they act on.  An exception to this if you are updating a column that is also being used in a where clause.
The static field is used to specify whether the parameter should be fillable by the end user, or if it should be set to a predefined value.  This is different from a column "default" attribute, since this applies at the query level.  For example, an application could have two queries that are available to different sets of users that have different static values acting on the same column.
The value is only required if a query is static.  This specifies what value should be used as the pre-defined input.  The value must be the same data type as the column that it is acting on. In the JSON, the value is stored as a base64 encoded byte array, prepended with the enumerator of its corresponding data type.
Use the Kwil CLI Utility Commands to retrieve the encoded value for user inputs.
Modifiers are special functions that are applied to inputs at execution time.  For example, the "caller" modifier set the parameter value to the function caller's wallet address, similar to Solidity's "msg.caller".  An up to date list of modifiers and their restrictions can be found in the conventions page.
Where Clauses
Where clauses are used to specify on which rows a query should affect.  The structure of where clauses are very similar to "parameters", with a few notable exceptions.  A where clause is shown below:
{
    "name": "where1", // required
    "column": "col1", // required
    "operator": 101, // required.  101 stands for "equals"
    "static": true, // optional
    "value": "AnNvbWVfdmFsdWU=", // encoded "some_value" sting - optional, required if static is true
    "modifier": 0, // optional.  0 also stands for no modifier
}
The operator is a comparison operator, which is used to specify how columns should be compared when sorting.  A full list of supported comparison operators can be found here.
Unlike parameters, a where clauses column does not have to be unique.  While Kwil currently supports multiple where clauses, they all must be applicable; in SQL terms. it is currently only possible to perform an AND statement, and not yet possible to do an OR statement.  This feature will be added once our team has built proper compute pricing for OR statements.
Last modified 30d ago