Implement EET DML oracle (initially for DELETE statements only) - #1356
Conversation
…ntation), including generic DML infrastructure for future extensions to other DML statements
| * @return the SQL statement | ||
| */ | ||
| default String addRowIdColumnStatement(T table) { | ||
| return "ALTER TABLE " + table.getName() + " ADD COLUMN " + ROW_ID_COLUMN + " VARCHAR(36)"; |
There was a problem hiding this comment.
Hmm, that part seems MySQL-specific. Can we make this code more general?
There was a problem hiding this comment.
I have now addressed this in the latest commit pushed, let me know if it matches what you had in mind
| * | ||
| * @return the SQL statement | ||
| */ | ||
| default String beginTransactionStatement() { |
There was a problem hiding this comment.
Just to check, are there any database systems that SQLancer supports that support transactions, but not BEGIN and ROLLBACK? If not, we can keep it simple and omit these two methods.
There was a problem hiding this comment.
Most do support those statements, so in most cases they will just use the default method, but there are one or two exceptions. E.g. HSQLDB and H2 use START TRANSACTION instead of BEGIN.
| @Override | ||
| public void check() throws SQLException { | ||
| List<T> tables = state.getSchema().getDatabaseTables(); | ||
| if (tables.isEmpty()) { |
There was a problem hiding this comment.
Are there possible situations where tables is empty?
There was a problem hiding this comment.
Whether tables is empty depends on DBMS-specific code:
- The generateDatabase method is DBMS-specific, and from what I've seen, the DBMSs do ensure at least one table is created.
- If QPG is enabled, its mutateTables method may cause an attempted drop of the last remaining table in the schema. The drop method itself is DBMS-specific, and from what I've seen, the DBMS which support QPG do ensure they do not drop the last remaining table.
So, it seems like tables will never be empty at the moment, but that is only a consequence of DBMS-specific code. The non-emptiness of tables is not guaranteed by any shared/generic code, so if somebody adds extra DBMS support, they would risk breaking that guarantee unless they are careful. So, this emptiness check is guarding against that eventuality.
|
By the way, as a minor suggestion, it would be fine to only summarize the high-level changes and rationale within a short paragraph or so. If the change summary is too detailed, it's easier to just read the code changes. |
Summary
The EET oracle previously only transformed
SELECTqueries and compared the two result sets. This PR adds a DML variant,EETDMLOracle, that transforms a DML statement's expressions and compares the two database states the original and transformed statements leave behind.To observe the database state, a similar approach to the DQE oracle is used: add an auxiliary column that uniquely identifies each row, and run each statement inside a transaction that is rolled back. This way, the original and transformed statements can each be measured against the same starting state without permanently mutating the database.
Only
DELETEis implemented in this PR, as it is the simplest (its only transformable site is theWHEREpredicate, and row identity alone is a sufficient comparison surface). The generic infrastructure is designed soUPDATEandINSERTcan be added later without rework. MySQL is included as a specific DBMS implementation.The expression transformer (
EETTransformer/MySQLEETTransformer) is statement-agnostic and remains unchanged.Changes
Dedicated new oracle (
common/oracle/EETDMLOracle.java)check():WHEREpredicatep; transform it in a boolean context (transformer.transform(p, true)).DELETEstrings via the generator.ADD COLUMN rowid; then, inside atrywhosefinallydrops the column, stamp every row once with a unique id.DELETE(original, then transformed):BEGIN; run it; snapshot the survivingrowids;ROLLBACK(rollback in afinally).rowidsets; on mismatch throw anAssertionErrorwhose message embeds both statement strings.Error handling mirrors the SELECT oracle: an expected DBMS error (on the
ExpectedErrorsallow-list) aborts the iteration viaIgnoreMeException, while an unexpected error surfaces as a reported bug.Test case reduction will be implemented at a later stage.
New generator interface (
common/gen/EETDMLGenerator.java)A sibling of
EETGenerator, supplying the DBMS-specific pieces the DML oracle needs.generateBooleanExpression()(the predicate),createTransformer(),setTablesAndColumns(...),asString(E)(render an expression), andstampRowIdsStatement(T)(assign every existing row a unique id).addRowIdColumnStatement/dropRowIdColumnStatement/selectRowIdsStatement/deleteStatement/beginTransactionStatement/rollbackTransactionStatement.MySQL generator (
mysql/gen/MySQLExpressionGenerator.java)Now also implements
EETDMLGenerator<MySQLExpression, MySQLTable, MySQLColumn>. It reuses the existinggenerateBooleanExpression/createTransformer/setTablesAndColumnsand adds onlyasStringandstampRowIdsStatement.Oracle registration (
mysql/MySQLOracleFactory.java)New
EET_DMLenum entry that constructs anEETDMLOraclewith existing expected errors plus a couple of extra ones encountered.Table generation (
mysql/gen/MySQLTableGenerator.java)The
ENGINEtable option is forced toInnoDBif EET DML is being used (checked withusesEETDML()method). This is because it is the only transactional engine, and the test procedure relies on transactions.Follow-ups (to be integrated into same oracle)
UPDATEsupportINSERTsupportEETReproducerused for the SELECT oracle.