mutation.opt

# mutation.opt contains Optgen language definitions for the mutation statement
# operator (Insert, Upsert, Update, Delete).

# Insert evaluates a relational input expression, and inserts values from it
# into a target table. The input may be an arbitrarily complex expression:
#
#   INSERT INTO ab SELECT x, y+1 FROM xy ORDER BY y
#
# It can also be a simple VALUES clause:
#
#   INSERT INTO ab VALUES (1, 2)
#
# It may also return rows, which can be further composed:
#
#   SELECT a + b FROM [INSERT INTO ab VALUES (1, 2) RETURNING a, b]
#
# The Insert operator is capable of inserting values into computed columns and
# mutation columns, which are not writable (or even visible in the case of
# mutation columns) by SQL users.
[Relational, Mutation]
define Insert {
    Input RelExpr
    Checks FKChecksExpr

    _ MutationPrivate
}

[Private]
define MutationPrivate {
    # Table identifies the table which is being mutated. It is an id that can be
    # passed to the Metadata.Table method in order to fetch cat.Table metadata.
    Table TableID

    # InsertCols are columns from the Input expression that will be inserted into
    # the target table. They must be a subset of the Input expression's output
    # columns. The count and order of columns corresponds to the count and order
    # of the target table's columns, including in-progress schema mutation
    # columns. If any column ID is zero, then that column will not be part of
    # the insert operation (e.g. delete-only mutation column). Column values are
    # read from the input columns and are then inserted into the corresponding
    # table columns. For example:
    #
    #   INSERT INTO ab VALUES (1, 2)
    #
    # If there is a delete-only mutation column "c", then InsertCols would contain
    # [a_colid, b_colid, 0].
    InsertCols ColList

    # FetchCols are columns from the Input expression that will be fetched from
    # the target table. They must be a subset of the Input expression's output
    # columns. The count and order of columns corresponds to the count and order
    # of the target table's columns, including in-progress schema mutation
    # columns. If any column ID is zero, then that column will not take part in
    # the update operation (e.g. columns in unreferenced column family).
    #
    # Fetch columns are referenced by update, computed, and constraint
    # expressions. They're also needed to formulate the final key/value pairs;
    # updating even one column in a family requires the entire value to be
    # reformulated. For example:
    #
    #   CREATE TABLE abcd (
    #     a INT PRIMARY KEY, b INT, c INT, d INT, e INT,
    #     FAMILY (a, b), FAMILY (c, d), FAMILY (e))
    #   UPDATE ab SET c=c+1
    #
    # The (a, c, d) columns need to be fetched from the store in order to satisfy
    # the UPDATE query. The "a" column is needed because it's in the primary key.
    # The "c" column is needed because its value is used as part of computing an
    # updated value, and the "d" column is needed because it's in the same family
    # as "c". Taking all this into account, FetchCols would contain this list:
    # [a_colid, 0, c_colid, d_colid, 0].
    FetchCols ColList

    # UpdateCols are columns from the Input expression that contain updated values
    # for columns of the target table. They must be a subset of the Input
    # expression's output columns. The count and order of columns corresponds to
    # the count and order of the target table's columns, including in-progress
    # schema mutation columns. If any column ID is zero, then that column will not
    # take part in the update operation (e.g. columns that are not updated).
    # Updated column values are read from the input columns and are then inserted
    # into the corresponding table columns. For example:
    #
    #   CREATE TABLE abc (a INT PRIMARY KEY, b INT, c INT AS (b+1) AS STORED)
    #   UPDATE abc SET b=1
    #
    # Since column "b" is updated, and "c" is a computed column dependent on "b",
    # then UpdateCols would contain [0, b_colid, c_colid].
    UpdateCols ColList

    # CheckCols are columns from the Input expression containing the results of
    # evaluating the check constraints from the target table. Evaluating a check
    # check constraint expression produces a boolean value which is projected as
    # a column and then checked by the mutation operator. Check columns must be
    # a subset of the Input expression's output columns. The count and order of
    # columns corresponds to the count and order of the target table's Check
    # collection (see the opt.Table.CheckCount and opt.Table.Check methods). If
    # any column ID is zero, then that check will not be performed (i.e. because
    # it's been statically proved to be true). For example:
    #
    #   CREATE TABLE abc (a INT CHECK (a > 0), b INT, c INT CHECK (c <> 0))
    #   UPDATE abc SET a=1, b=b+1
    #
    # Since the check constraint for column "a" can be statically proven to be
    # true, CheckCols would contain [0, b_colid].
    CheckCols ColList

    # CanaryCol is used only with the Upsert operator. It identifies the column
    # that the execution engine uses to decide whether to insert or to update.
    # If the canary column value is null for a particular input row, then a new
    # row is inserted into the table. Otherwise, the existing row is updated.
    # While CanaryCol is 0 for all non-Upsert operators, it is also 0 for the
    # "blind" Upsert case in which a "Put" KV operator inserts a new row or
    # overwrites an existing row.
    CanaryCol ColumnID

    # ReturnCols are the set of columns returned by the mutation operator when
    # the RETURNING clause has been specified. By default, the return columns
    # include all columns in the table, including hidden columns, but not
    # including any columns that are undergoing mutation (being added or dropped
    # as part of online schema change). If no RETURNING clause was specified,
    # then ReturnCols is nil.
    ReturnCols ColList

    # PassthroughCols are columns that the mutation needs to passthrough from
    # its input. It's similar to the passthrough columns in projections. This
    # is useful for `UPDATE .. FROM` mutations where the `RETURNING` clause
    # references columns from tables in the `FROM` clause. When this happens
    # the update will need to pass through those refenced columns from its input.
    PassthroughCols ColList

    # Mutation operators can act similarly to a With operator: they buffer their
    # input, making it accessible to FK queries. If this is not required, WithID
    # is zero.
    WithID WithID
}

# Update evaluates a relational input expression that fetches existing rows from
# a target table and computes new values for one or more columns. Arbitrary
# subsets of rows can be selected from the target table and processed in order,
# as with this example:
#
#   UPDATE abc SET b=10 WHERE a>0 ORDER BY b+c LIMIT 10
#
# The Update operator will also update any computed columns, including mutation
# columns that are computed.
[Relational, Mutation]
define Update {
    Input RelExpr
    Checks FKChecksExpr

    _ MutationPrivate
}

# Upsert evaluates a relational input expression that tries to insert a new row
# into a target table. If a conflicting row already exists, then Upsert will
# instead update the existing row. The Upsert operator is used for all of these
# syntactic variants:
#
#   INSERT..ON CONFLICT DO UPDATE
#     INSERT INTO abc VALUES (1, 2, 3) ON CONFLICT (a) DO UPDATE SET b=10
#
#   INSERT..ON CONFLICT DO NOTHING
#     INSERT INTO abc VALUES (1, 2, 3) ON CONFLICT DO NOTHING
#
#   UPSERT
#     UPSERT INTO abc VALUES (1, 2, 3)
#
# The Update operator will also insert/update any computed columns, including
# mutation columns that are computed.
[Relational, Mutation]
define Upsert {
    Input RelExpr
    Checks FKChecksExpr

    _ MutationPrivate
}

# Delete is an operator used to delete all rows that are selected by a
# relational input expression:
#
#   DELETE FROM abc WHERE a>0 ORDER BY b LIMIT 10
#
[Relational, Mutation]
define Delete {
    Input RelExpr
    Checks FKChecksExpr

    _ MutationPrivate
}

# FKChecks is a list of foreign key check queries, to be run after the main
# query.
[Scalar, List]
define FKChecks {
}

# FKChecksItem is a foreign key check query, to be run after the main query.
# An execution error will be generated if the query returns any results.
[Scalar, ListItem]
define FKChecksItem {
    Check RelExpr

    _ FKChecksItemPrivate
}

[Private]
define FKChecksItemPrivate {
    OriginTable     TableID
    ReferencedTable TableID

    # If FKOutbound is true: this item checks that a new value in the origin
    # table has a valid reference. The FK constraint is
    # OutboundForeignKey(FKOrdinal) on the origin table.
    #
    # If FKOutbound is false: this item checks that a removed value from the
    # referenced table doesn't orphan references to it from the origin table.
    # The FK constraint is InboundForeignKey(FKOrdinal) on the referenced table.
    FKOutbound bool
    FKOrdinal  int

    # KeyCols are the columns in the Check query that form the value tuple shown
    # in the error message.
    KeyCols ColList
}