Jailer - FAQ

Frequently Asked Questions

Why am I getting so much data back?

Each association will be traversed in both directions, unless there is a restriction defined. For example, if the subject table is employee, and the association from department table to employee table is enabled, you will not only get all departments associated with any subject employee, but also all employees associated with any of that departments.

If you get rows which you don't intend to get, then a restriction is missing. You can use the explain feature in such a case. The explain log shows the chain of associations for each exported row back to a subject row, where the exportation starts. I recommend to read the tutorial, which explains the matters.

Instead of adding all restrictions you need, starting from the unrestricted model, you can also go the opposit way: at first disable all associations (except dependencies) using "Edit->Disable all associations" menu item. Then remove restrictions until you get all the rows you are looking for.

It's easier that way to get a working extraction model, but it's harder to ensure that the model isn't too restrictive, i.e. that you will get all rows you intend to get.

From my experience the best way to define an extraction model is to use the "Closure Browser" (use "Closure..." button).

The closure browser lists all tables which are directly or indirectly associated with the subject table (where extraction starts) according to the restrictions, ordered by distance.

Check each table of this list from top to bottom. If you don't want to have rows from the table to be exported, select the table and disable the association which gets automatically selected then (table's context menu is also available here). The table disapears from the list as soon as it is no longer associated with subject.

This way you easily get an extraction model which extracts rows only from tables you want to extract data from.

Finally check all associations between the remaining tables.

How can I disable triggers and constraints during import?

In order to disable triggers (or constraints) belonging to a table T during the execution of generated SQL scripts, create a file named 'prolog/INSERT/T.sql' and write the SQL statement to disable the trigger into it (T is the fully qualified name of the table). The content of that file will be written into the script, if at least 1 row of the table has been exported.

Put the statement to re-enable the trigger into the file 'epilog/INSERT/T.sql'.

For more information, please read 'prolog/INSERT/Readme.txt'.

What does $DISTANCE and $IS_SUBJECT mean?

$DISTANCE and $IS_SUBJECT are special expressions used in restriction conditions in order to define restriction models based on topological properties of rows.

Expression	Type
$DISTANCE	INTEGER	Minimum length of all paths from the row back to any subject row. The distance of subject rows is 0.
$BIRTHDAY	INTEGER	Synonym for $DISTANCE.
$IS_SUBJECT	BOOLEAN	Synonym for $DISTANCE=0.

How do I anonymize the extracted data?

It's possible to anonymize the extracted data by defining filters (via "Edit" menu "Filter editor...", or table's context menu)

A filter is an assignment of a SQL expression to a table column, the column values will be replaced by the result of the expression when the export file is written.

For example:

In order to substitute the values of the column PERSON.SSN with the constant value '123', define the filter:
PERSON.SSN := '123'

The filter
PERSON.SSN := substr(SSN, 1, length(SSN) - 3) || '***'
replaces the last 3 characters of the SSN with '***'

The filter:
PERSON.SSN := (SELECT SSN FROM TEST_SSN ORDER BY RAND()
FETCH FIRST 1 ROWS ONLY)
would replace the SSN with a randomly chosen SSN from a table holding test SSNs (in IBM's DB2)

Why doesn't Jailer detect associations in MySQL databases?

If you use MyISAM database engine, no association will be detected. MyISAM doesn't know the concept of foreign key relationships, even if they're defined in the DDL.

The typical alternative engine that supports foreign key relationships is InnoDB.

Is it possible to avoid the creation of JAILER_* tables in the database?

It's not possible to avoid the creation of that tables, but it's possible to have them in an own schema.

The JAILER_* tables will always be created within the database user's default schema. If you don't want to have them in the schema of user A, then create a new database user B, connect as B and enter A's schema as the schema to read rows from in the data export dialog. The meta tables will be created in B's schema and the data will be exported from A's schema.

For DB2, Oracle, PostgreSQL, Sybase and MS SQL Server, it is also possible to create the tables as temporary tables.

I'm getting the message "Table has no primary key". What's wrong?

Jailer cannot handle tables without primary keys. Exporting data is not possible, if a table without a primary key is associated with the subject table. In this case, you should define a key of the table in the tool's data model (not in the database!) manually, using the data model editor. Note that a key must be unique and known (i.e. not NULL).

The GUI is slow, what can I do?

Due to the dynamic nature of the layouting algorithm, the table-association graph is permanently redrawn. It seems that this consumes too much cpu time on some systems. However, there is a parameter called "animation step time" which determines the minimum time (in ms) for one animation step. Increasing that parameter (View->Animation step time) will reduce the cpu utilization.