Full SQL Statements

Goal

There may arise cases in your database structure that require certain complete SQL statements to run before and after the anonymizations are carried out.

• sql-before allows you to write complete SQL statements that are run before tasks.
• sql-after allows you to write complete SQL statements that are run after tasks.

These complete SQL statements may be run before and after

1. The task root (e.g., before all Task Groups)
2. A specific Task Group (e.g., a specific task <task-name> {...})
3. A specific Work Task (e.g., a specific update <table>)

Custom SQL notes

• There will be no check of SQL syntax
• Any occurrences of double quotes or backslash inside a SQL statement must be escaped with a backslash: \" and \.
• A single statement cannot be divided on several lines. Multiple SQL statements may be written divided by newline

Use cases

The sql-before and sql-after may be useful to

• Temporarily remove table constraints
• Temporarily allow inserts into primary-key columns
• Delete rows according to conditions, i.e., delete rows with null values
• Do other CRUD operations you require, but that are not handled by ano-logic

Ano Structure and Example

Placements in .ano

// Root SQL statements
sql-before "SELECT 1;"            // before all tasks
sql-after "SELECT 1;"             // after all tasks

task <task2>
// Task Group SQL statements
sql-before "SELECT 2.1;"            // before <task1>
sql-after "SELECT 2.1;"             // after <task1>
{
    update <table1>
        // Work Task SQL statements
        sql-before "SELECT 3.1;"  // before update <table1>
        sql-after "SELECT 3.1;"   // after update <table1>
        <table1-anonymizations>

    update <table2>
        sql-before "SELECT 3.2;"  // before update <table2>
        sql-after "SELECT 3.2;"   // before update <table2>
        <table2-anonymizations>
}


task <task2>
sql-before "SELECT 2.2;"            // before <task2>
sql-after "SELECT 2.2;"             // after <task2>
{
  ...
}

Work Task Example

table Customer
    column text lastname size 50
    ...

task <task-name> {
    update Customer
        sql-before "delete from Customer where lastname is null"
        mask lastname
            format %s
            file src/main/resources/lastname.txt random-order
}

Work Task Example

• We do not want table Customer rows where lastname = null to be part of the database, so we delete these before performing the anonymization

How to trigger execution of the SQL statements

Running Tasks from Root

Running Tasks from Root using

run

will run the root SQL statements, and will run all the Task Groups and Work Tasks and their SQL statements.

Running Task Groups

When runing a particular Task Group, using

run <task-group>

you will not run the root SQL statements. And you will only run the target Task Group SQL Statements, and the once contained within.

Running Work Tasks

When running a particular Work Task, using

run <work-task>

You will neither run the Root or the Task Group SQL Statements. Only the SQL Statements of the particular Work Task will be run.