removeDeletedShadowRows method

@override String removeDeletedShadowRows(QualifiedTablename oplog, QualifiedTablename shadow)

Deletes any shadow rows where the last oplog operation was a DELETE

Implementation

@override
String removeDeletedShadowRows(
  QualifiedTablename oplog,
  QualifiedTablename shadow,
) {
  // We do an inner join in a CTE instead of a `WHERE EXISTS (...)`
  // since this is not reliant on re-executing a query
  // for every row in the shadow table, but uses a PK join instead.
  return '''
    WITH _to_be_deleted (rowid) AS (
      SELECT shadow.rowid
        FROM $oplog AS op
        INNER JOIN $shadow AS shadow
          ON shadow.namespace = op.namespace AND shadow.tablename = op.tablename AND shadow.primaryKey = op.primaryKey
        WHERE op.timestamp = ?
        GROUP BY op.namespace, op.tablename, op.primaryKey
        HAVING op.rowid = max(op.rowid) AND op.optype = 'DELETE'
    )

    DELETE FROM $shadow
    WHERE rowid IN _to_be_deleted
  ''';
}