Implements a higher-level method in SquidDatabase for "upsert", aka update or insert. Upsertable models define a "logical key" -- a tuple of non-null columns (maybe the same as the primary key) that uniquely identify a row in the database. When persisting a model using upsert, the method will first check to see if there is an existing model in the database matching the logical key values. If it exists, an update with the changed values will be performed; otherwise, the model will be inserted.
Our implementation is supported by a new 'Upsertable' interface and and @UpsertKey annotation for code generation to take care of building the logical key for a model into a criterion. A few pieces of behavior are configurable using the @UpsertConfig annotation -- whether or not to ignore the model rowid when upserting if one is present, and whether or not to throw a runtime exception or return false if the model being upserted doesn't have values for any of the logical key columns.
Currently the code generator generates a unique Index that the user is required to create in the database to enforce the uniqueness constraint. In a future enhancement I'd prefer to get rid of this index and simply do some regex matching on the table constraint to make sure that the user has declared either a UNIQUE or a PRIMARY KEY constraint for the columns -- IMO it's better to log an error during compilation than risking letting the user forget a required uniqueness constraint and potentially end up with bad data in the db. I'll make this enhancement before we put out a 4.0 beta, but let's get this basic foundation in first.
Implements a higher-level method in SquidDatabase for "upsert", aka update or insert. Upsertable models define a "logical key" -- a tuple of non-null columns (maybe the same as the primary key) that uniquely identify a row in the database. When persisting a model using upsert, the method will first check to see if there is an existing model in the database matching the logical key values. If it exists, an update with the changed values will be performed; otherwise, the model will be inserted.
Our implementation is supported by a new 'Upsertable' interface and and
@UpsertKeyannotation for code generation to take care of building the logical key for a model into a criterion. A few pieces of behavior are configurable using the@UpsertConfigannotation -- whether or not to ignore the model rowid when upserting if one is present, and whether or not to throw a runtime exception or return false if the model being upserted doesn't have values for any of the logical key columns.Currently the code generator generates a unique Index that the user is required to create in the database to enforce the uniqueness constraint. In a future enhancement I'd prefer to get rid of this index and simply do some regex matching on the table constraint to make sure that the user has declared either a
UNIQUEor aPRIMARY KEYconstraint for the columns -- IMO it's better to log an error during compilation than risking letting the user forget a required uniqueness constraint and potentially end up with bad data in the db. I'll make this enhancement before we put out a 4.0 beta, but let's get this basic foundation in first.