| Q |
What is the "Analyse SQL Script" feature and when should I use it? |
| A |
Many legacy databases or databases managed by third-party
applications do not declare foreign key constraints at the
database level, even though the application clearly joins
certain tables together. Without foreign keys, Jailer's
Analyse Database
step cannot detect these relationships, and the data model
will be missing all such associations.
Analyse SQL Script
(found in the Data Model
menu of the Extraction Model Editor) solves this by analysing
an existing SQL file — such as application query logs,
ORM-generated SQL, migration scripts, or hand-written
reports — and proposing associations for every
column-equality condition it finds between two different
tables.
Use this feature whenever the data model is incomplete due to
absent foreign key definitions.
|
| Q |
What SQL scripts are suitable as input? |
| A |
The analyser looks for SELECT statements that join two or
more tables. Any condition of the form
TableA.col = TableB.col
— whether in an ON clause or a WHERE clause —
is a candidate for a new association. Transitive equalities
(A=B and B=C) are also resolved.
Good sources for input scripts:
- Slow-query or general query logs captured from the
database server
- SQL files generated by ORM frameworks (Hibernate,
MyBatis, etc.)
- Manually written report or migration SQL scripts
- Stored procedure bodies exported as plain SQL
DML and DDL statements (INSERT, UPDATE, CREATE TABLE, …)
are harmlessly ignored — only SELECT statements are
analysed. SQL comments starting with
-- are stripped
automatically.
Statements must be delimited either by empty lines
(the default) or by semicolons (;). A single file can
contain thousands of statements; the tool processes them all
in one pass.
|
| Q |
How do I open the tool? |
| A |
Open the Extraction Model Editor, then choose
Data Model → Analyse SQL Script
The Analyse SQL
dialog opens. It has three tabs:
Proposals,
Known, and
Problems.
|
| Q |
How do I load and analyse a SQL script? |
| A |
- Click "Load and
Analyze SQL Script" in the dialog.
- Select the .sql
file (or any plain-text file containing SQL) in the file
chooser.
- Choose the statement delimiter:
- ☑ Empty line separates statements
(default) — a blank line marks the boundary
between statements.
- Unchecked — each statement must end with a
semicolon (;).
- The engine reads the file statement by statement.
A progress bar shows the overall progress. Results appear
in the tabs as they are found — you do not have to
wait for the full file to be processed.
|
| Q |
How do I interpret the three result tabs? |
| A |
| Proposals |
New associations that the analyser found and that
are not yet part of the data model. Each row shows the
two table names and the auto-generated join condition
(e.g.
A.customer_id=B.id).
Use the checkbox in each row to decide which proposals
to accept. |
| Known |
Associations that were detected in the script but
already exist in the data model. This tab is
informational only and requires no action. |
| Problems |
SQL statements that the parser could not process,
listed with their line number and the error message.
Up to 1 000 errors are shown. Any proposals
found before an error are still valid — errors
only mean that those particular statements were
skipped. |
|
| Q |
Can I refine a proposed join condition before accepting it? |
| A |
Yes. Click any row in the Proposals
tab to select it. A Condition Editor
panel appears below the table showing the current join
condition as editable SQL.
Edit the condition as needed — for example to add a
type cast, narrow the join with an extra predicate, or correct
a false positive — then click
Apply to save the
change. The updated condition is shown immediately in the
table row.
|
| Q |
How do I accept proposals and add them to the data model? |
| A |
- Check the proposals you want to keep. Use
"Select all"
or "Deselect all"
for bulk selection.
- Click "Accept
selected proposals".
- A confirmation message reports how many associations
were added and how many were already known.
- The dialog closes and the Data Model Editor is
refreshed. The new associations are immediately available
and visible in the graphical view.
Each accepted association receives an auto-generated name of
the form P_TableA_TableB
(with a numeric suffix if necessary to avoid duplicates).
You can rename it later in the Data Model Editor.
|
| Q |
What should I do when there are many parsing errors? |
| A |
Parsing errors are normal with large or heterogeneous SQL
files. The most common causes are:
- Vendor-specific SQL extensions or hints (e.g. Oracle
/* + HINT */,
SQL Server
WITH (NOLOCK))
- Very complex or deeply nested subqueries that exceed
the 4-second parse timeout
- Missing semicolons when the semicolon-delimiter mode
is selected
- Non-SQL content mixed into the file
Strategies to reduce errors:
- Pre-process the file to extract only standard SELECT
statements and strip vendor-specific syntax.
- Switch to empty-line delimiter mode if statements lack
semicolons.
- Split a large file into smaller chunks and analyse
each chunk separately.
Even with many errors, proposals found in the
successfully-parsed statements are fully valid and can be
accepted normally.
|
