Description of the Issue

Hi Wisser, hope you are doing well!

A couple of times I bumped into this unfortunate bug: When in the Relational Data Browser UI, click in Navigation on Open Table then again, click on Open Table.. afterwards in most of my trials the popover will be stuck until I close the app

in the background I can navigate to other tabs like Database and Desktop/SQL Console, but the table popover keeps being stuk

Steps to Reproduce the Issue

1. Start GUI
2. Ensure Data Browser selected, connect to a Data Model
3. Connect to a Database and wait for it to finish connecting
4. Then in Navigation tab, click Open Table
5. Again click on Open Table (or in my breaking trial now, sometimes had to click twice)
6. Then watch how the blinking cursor gets stuck, the Open Table button won't work anymore when you click on it and the popover with tables is stuck on screen, can't select a table, scroll the table list or close it.

Expected Behavior

I expect the Table popover list to open/close with pressing the Open Table button in Navigation.

Actual Behavior

The Table popover list got stuck and won't disappear until Jailer is closed.

Debug Information

• macOS 12.5 but have seen the issue in earlier versions of macOS as well
• Java 17.0.4, but have seen the issue in earlier 17.x versions as well
• Jailer 12.5, but had this issue before in 12.3 as well

Thank for you the great work :)

Is your feature request related to a problem? Please describe.

I am evaluating Jailer as a tool to extract part of a database for publishing it. In research, when we collect the data in the lab databases as a part of experimental and analysis routine, it would be helpful if I can extract part of a data together with the schema of the related tables (ideally views and functions as well). Jailer looks to be perfect fit. I can define which table data to extract, associated extraction model, and generate SQL statements. Unfortunately, that generated SQL does not include schema creation.

Describe the solution you'd like

When starting Export tool:

• include an option to generate schema creation statements
  • as suboptions, suggest to include related views, functions
• allow to save schema creation SQL statements as a separate file for flexibility

Describe alternatives you've considered

Alternative would be to generate schema dump by database utilities and manually remove all unrelated tables. Sounds like an error-rich solution, though.

Additional context

In principle, Jailer could be used as a part of the publishing model for scientific databases. The generated SQL statements could be published as they are or used to generate extracted database and publish that.

Description of the Issue

When using Jailer 12.2 Data Browser and setting up an Association with multiple Join conditions, in the Desktop tab the multiple conditions are ignored, while the SQL Console applies the multiple conditions.

Steps to Reproduce the Issue

Using Oracle: Setup table: MTL_SYSTEM_ITEMS_B Setup table: MTL_ITEM_CATEGORIES

Create Association: From A: MTL_SYSTEM_ITEMS_B Type: associates To B: MTL_ITEM_CATEGORIES Join condition: A.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID AND A.ORGANIZATION_ID = B.ORGANIZATION_ID Cardinality: 1:n Name: msi-mic

Expected Behavior

In the Desktop tab, I expected the same to happen as on the SQL Console: show only 13 records.

Actual Behavior

The Desktop tab shows 500+ records because the AND A.ORGANIZATION_ID = B.ORGANIZATION_ID is ignored it however does show the full condition when hovering over the 'on'

Debug Information

macOS: 12.2 Jailer 12.2

Thank you for creating and maintaining this wonderful tool!

Hello,

Thank you again for your wonderful work.

Description of the Issue

I have an issue using firebird databases.

I can create the data model and extraction model just fine. But when I try to export data i get the following error:

 ERROR  - No suitable Inline-View Style known for jdbc:firebirdsql://localhost:3050/mydb?encoding=NONE

Steps to Reproduce the Issue

I'm using a firebird database (https://hub.docker.com/r/jacobalberty/firebird/) and the example schema given in jailer (employee).

Debug Information

I'm using the latest release.

Full log below with my command line:

[00:00.033] Executing program:
"C:\Program Files\Java\jdk-11.0.6\bin\java.exe" net.sf.jailer.Jailer export C:\data\repo\_innersource\cnaf\yadaman\src\bin\example\db\extractionmodels\employe.jm org.firebirdsql.jdbc.FBDriver jdbc:firebirdsql://localhost:3050/mydb?encoding=NONE myuser myuser -datamodel C:\data\repo\_innersource\cnaf\yadaman\src\bin\example\db\datamodel -e C:\Users\jcaillon\Desktop\2try.sql -threads 1 -entities 50 -format SQL -scope LOCAL_DATABASE -working-table-schema try -upsert-only -check-primary-keys -target-dbms FIREBIRD
[00:02.026] 2020-08-05 17:23:56,455 [main] ERROR  - No suitable Inline-View Style known for jdbc:firebirdsql://localhost:3050/mydb?encoding=NONE
[00:02.028] Error: java.lang.RuntimeException: No suitable Inline-View Style known for jdbc:firebirdsql://localhost:3050/mydb?encoding=NONE
[00:02.030]    MySQL: ""Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 45; Union [SQLState:42000, ISC error code:335544634]" in statement "Select A, B, C from (Select 1 A, '2' B, 3 C Union all Select 4, '5', 6) Entity""
[00:02.031]    MySQL: ""Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 45; Union [SQLState:42000, ISC error code:335544634]" in statement "Select A, B, C from (Select 1 A, '2' B, 3 C Union all Select 4, '5', 6) Entity""
[00:02.033]    Oracle: ""Dynamic SQL Error; SQL error code = -204; Table unknown; DUAL; At line 1, column 50 [SQLState:42S02, ISC error code:335544580]" in statement "Select A, B, C from (Select 1 A, '2' B, 3 C from dual Union all Select 4, '5', 6 from dual) Entity""
[00:02.035]    Oracle: ""Dynamic SQL Error; SQL error code = -204; Table unknown; DUAL; At line 1, column 50 [SQLState:42S02, ISC error code:335544580]" in statement "Select A, B, C from (Select 1 A, '2' B, 3 C from dual Union all Select 4, '5', 6 from dual) Entity""
[00:02.036]    DB2: ""Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 22; values [SQLState:42000, ISC error code:335544634]" in statement "Select A, B, C from (values (1, '2', 3), (4, '5', 6)) Entity(A, B, C)""
[00:02.046]    DB2: ""Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 22; values [SQLState:42000, ISC error code:335544634]" in statement "Select A, B, C from (values (1, '2', 3), (4, '5', 6)) Entity(A, B, C)""
[00:02.051]    INFORMIX1: ""Dynamic SQL Error; SQL error code = -204; Procedure unknown; SYSMASTER.SYSDUAL; At line 1, column 50 [SQLState:42000, ISC error code:335544581]" in statement "Select A, B, C from (Select 1 A, '2' B, 3 C from sysmaster.sysdual Union all Select 4, '5', 6 from sysmaster.sysdual) Entity""
[00:02.057]    INFORMIX1: ""Dynamic SQL Error; SQL error code = -204; Procedure unknown; SYSMASTER.SYSDUAL; At line 1, column 50 [SQLState:42000, ISC error code:335544581]" in statement "Select A, B, C from (Select 1 A, '2' B, 3 C from sysmaster.sysdual Union all Select 4, '5', 6 from sysmaster.sysdual) Entity""
[00:02.066]    INFORMIX2: ""Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 50; table [SQLState:42000, ISC error code:335544634]" in statement "Select A, B, C from (Select 1 A, '2' B, 3 C from table(set{1}) Union all Select 4, '5', 6 from table(set{1})) Entity""
[00:02.073]    INFORMIX2: ""Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 50; table [SQLState:42000, ISC error code:335544634]" in statement "Select A, B, C from (Select 1 A, '2' B, 3 C from table(set{1}) Union all Select 4, '5', 6 from table(set{1})) Entity""
[00:02.079]    DB2_ZOS: ""Dynamic SQL Error; SQL error code = -204; Procedure unknown; SYSIBM.SYSDUMMY1; At line 1, column 44 [SQLState:42000, ISC error code:335544581]" in statement "Select A, B, C from (Select 1, '2', 3 from sysibm.sysdummy1 Union all Select 4, '5', 6 from sysibm.sysdummy1) Entity(A, B, C)""
[00:02.084]    DB2_ZOS: ""Dynamic SQL Error; SQL error code = -204; Procedure unknown; SYSIBM.SYSDUMMY1; At line 1, column 44 [SQLState:42000, ISC error code:335544581]" in statement "Select A, B, C from (Select 1, '2', 3 from sysibm.sysdummy1 Union all Select 4, '5', 6 from sysibm.sysdummy1) Entity(A, B, C)""
[00:02.090]    INFORMIX3: ""Dynamic SQL Error; SQL error code = -104; Unexpected end of command - line 1, column 128 [SQLState:42000, ISC error code:335544851]" in statement "Select A, B, C from (Select 1 A, '2' B, 3 C from systables WHERE tabid=1 Union all Select 4, '5', 6 from systables WHERE tabid=1""
[00:02.095]    INFORMIX3: ""Dynamic SQL Error; SQL error code = -104; Unexpected end of command - line 1, column 128 [SQLState:42000, ISC error code:335544851]" in statement "Select A, B, C from (Select 1 A, '2' B, 3 C from systables WHERE tabid=1 Union all Select 4, '5', 6 from systables WHERE tabid=1""
[00:02.099]    INFORMIX4: ""Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 59; : [SQLState:42000, ISC error code:335544634]" in statement "Select A, B, C from (Select 1 A, '2' B, 3 C from sysmaster:"informix".sysdual Union all Select 4, '5', 6 from sysmaster:"informix".sysdual) Entity""
[00:02.104]    INFORMIX4: ""Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 59; : [SQLState:42000, ISC error code:335544634]" in statement "Select A, B, C from (Select 1 A, '2' B, 3 C from sysmaster:"informix".sysdual Union all Select 4, '5', 6 from sysmaster:"informix".sysdual) Entity""
[00:02.108] java.lang.RuntimeException: No suitable Inline-View Style known for jdbc:firebirdsql://localhost:3050/mydb?encoding=NONE
[00:02.112] Arguments:  0: {export},  1: {C:\data\repo\_innersource\cnaf\yadaman\src\bin\example\db\extractionmodels\employe.jm},  2: {org.firebirdsql.jdbc.FBDriver},  3: {jdbc:firebirdsql://localhost:3050/mydb?encoding=NONE},  4: {?},  5: {?},  6: {-datamodel},  7: {C:\data\repo\_innersource\cnaf\yadaman\src\bin\example\db\datamodel},  8: {-e},  9: {C:\Users\jcaillon\Desktop\2try.sql},  10: {-threads},  11: {1},  12: {-entities},  13: {50},  14: {-format},  15: {SQL},  16: {-scope},  17: {LOCAL_DATABASE},  18: {-working-table-schema},  19: {try},  20: {-upsert-only},  21: {-check-primary-keys},  22: {-target-dbms},  23: {FIREBIRD}
[00:02.116]    MySQL: ""Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 45; Union [SQLState:42000, ISC error code:335544634]" in statement "Select A, B, C from (Select 1 A, '2' B, 3 C Union all Select 4, '5', 6) Entity""
[00:02.122]    Oracle: ""Dynamic SQL Error; SQL error code = -204; Table unknown; DUAL; At line 1, column 50 [SQLState:42S02, ISC error code:335544580]" in statement "Select A, B, C from (Select 1 A, '2' B, 3 C from dual Union all Select 4, '5', 6 from dual) Entity""
[00:02.126]    DB2: ""Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 22; values [SQLState:42000, ISC error code:335544634]" in statement "Select A, B, C from (values (1, '2', 3), (4, '5', 6)) Entity(A, B, C)""
[00:02.130]    INFORMIX1: ""Dynamic SQL Error; SQL error code = -204; Procedure unknown; SYSMASTER.SYSDUAL; At line 1, column 50 [SQLState:42000, ISC error code:335544581]" in statement "Select A, B, C from (Select 1 A, '2' B, 3 C from sysmaster.sysdual Union all Select 4, '5', 6 from sysmaster.sysdual) Entity""
[00:02.137]    INFORMIX2: ""Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 50; table [SQLState:42000, ISC error code:335544634]" in statement "Select A, B, C from (Select 1 A, '2' B, 3 C from table(set{1}) Union all Select 4, '5', 6 from table(set{1})) Entity""
[00:02.141]    DB2_ZOS: ""Dynamic SQL Error; SQL error code = -204; Procedure unknown; SYSIBM.SYSDUMMY1; At line 1, column 44 [SQLState:42000, ISC error code:335544581]" in statement "Select A, B, C from (Select 1, '2', 3 from sysibm.sysdummy1 Union all Select 4, '5', 6 from sysibm.sysdummy1) Entity(A, B, C)""
[00:02.145]    INFORMIX3: ""Dynamic SQL Error; SQL error code = -104; Unexpected end of command - line 1, column 128 [SQLState:42000, ISC error code:335544851]" in statement "Select A, B, C from (Select 1 A, '2' B, 3 C from systables WHERE tabid=1 Union all Select 4, '5', 6 from systables WHERE tabid=1""
[00:02.150]    INFORMIX4: ""Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 59; : [SQLState:42000, ISC error code:335544634]" in statement "Select A, B, C from (Select 1 A, '2' B, 3 C from sysmaster:"informix".sysdual Union all Select 4, '5', 6 from sysmaster:"informix".sysdual) Entity""
[00:02.155]     at net.sf.jailer.database.InlineViewStyle.forSession(InlineViewStyle.java:331)
[00:02.159]     at net.sf.jailer.entitygraph.local.LocalEntityGraph.<init>(LocalEntityGraph.java:258)
[00:02.163]     at net.sf.jailer.entitygraph.local.LocalEntityGraph.create(LocalEntityGraph.java:326)
[00:02.167]     at net.sf.jailer.subsetting.SubsettingEngine.export(SubsettingEngine.java:1403)
[00:02.172]     at net.sf.jailer.Jailer.jailerMain(Jailer.java:217)
[00:02.176]     at net.sf.jailer.Jailer.main(Jailer.java:120)

Is your feature request related to a problem? Please describe.

When I open the Relational Data Browser using a PostgreSQL database selected, for which I've updated the Schema Mapping to not use public but another one called data.

Now when I switch to the Database tab, the public schema is fully expanded to the table list.

Describe the solution you'd like

It would be nice if either:

• The Schema Mapping schema is expanded by default (data in my case)
• The expanded schema's include both public and the Schema Mapping schema

Describe alternatives you've considered

It's easy to just open up the other schema, so no big deal.

Additional context

Interestingly: the "Open Table" button on the Database tab, does show the public and Schema Mapping schema selected.

Again, thank you for your continuous development and support!

Hello @Wisser ,

First of all, let me thank you and congratulate you for this amazing tool that is jailer. Really awesome work.

I would like to suggest a new feature:

When you open Jailer or JailerDataBrowser, you get an initial dialog window that let you choose the data model/connection[/export model]. Would it be possible to bypass this dialog if the needed information are passed directly to the program through process arguments?

This would use roughly the same arguments as jailer.bat/.sh, something along those lines:

jailerDataBrowser.bat -jdbcjar db2jcc4.jar -driver=class.Driver -url="jdbc:xxx://localhost:xxx/database" -username=user -password=*** -datamodel "datamodel\xxx" -schemamapping SCHEMA_IN_DATAMODEL=SCHEMA_IN_DATABASE [-extractionmodel "extractionmodel\xxx.jm"]

jailerGUI.bat -jdbcjar db2jcc4.jar -driver=class.Driver -url="jdbc:xxx://localhost:xxx/database" -username=user -password=*** -datamodel "datamodel\xxx" -schemamapping SCHEMA_IN_DATAMODEL=SCHEMA_IN_DATABASE [-extractionmodel "extractionmodel\xxx.jm"]

What do you think about it?

Best regards

Hi,

since Jailer 7.10 i have currently a problem when i want connect to remote databases. Jailer say that he can not connect to the DB, before 7.10 everything works fine. How can i solve this problem?

P.S. Thanks for the great product.

Description of the Issue

The SQL Console lags on keyboard input while it does IntelliSense guessing. If I type "abacus" it takes roughly 5 seconds for the text to appear on the screen whereas I can type it in less than one. During this time it's slowly adding letters, the pulldown menu is appearing and the cursor appears to show it's loading.

Steps to Reproduce the Issue

Type at a sensible speed in the field list area of an SQL statement that looks something like:

with j (req, calc_sheet_id, detail_sheet_id, location_id) as (
    values
        ('Client/Place',1406077,1159656,145726),('Client/Place',1607434,1599283,149018),('Client/Place',1607965,1607766,149330),('Client/Place',1611497,1595313,148588),('Client/Place',1612222,1611544,149334),('Client/Place',1610602,1609570,149032),('Client/Place',1633128,1591554,147940),('Client/Place',1591422,1591554,147934),
        ('Country/Place',1200708,1173687,146122),('Country/Place',1566181,1566140,147596),('Country/Place',1196628,1201329,145772),('Country/Place',1181210,1402791,147615),('Country/Place',1395290,1395539,146181),('Country/Place',1181434,1168903,146051),('Country/Place',1638604,1600134,149094),('Country/Place',1100250,1210432,146004),('Country/Place',1637369,1637345,150217)
),
file_details as (
    select
        j.*,
        calc_sheet.file_id as calc_file_id,
        (select original_filename from source_file where file_id = calc_sheet.file_id order by source_file_id desc limit 1) as calc_filename,
        calc_sheet.sheet as calc_sheet_name,
        detail_sheet.file_id as detail_file_id,
        (select original_filename from source_file where file_id = detail_sheet.file_id order by source_file_id desc limit 1) as detail_filename,
        detail_sheet.sheet as detail_sheet_name
    from j
    inner join sheets calc_sheet on calc_sheet.sheet_id = calc_sheet_id
    inner join sheets detail_sheet on detail_sheet.sheet_id = detail_sheet_id
    inner join calc_metadata on calc_metadata.file_id = calc_sheet.file_id
)
select * from file_details;

Expected Behavior

It should be easy to type

Actual Behavior

I have to stop for roughly 5 seconds after I input any field or table name for the SQL console to catch up.

Debug Information

Ubuntu 22.04, PostgreSQL database.

Description of the Issue

When adding parameters to certain filter expressions (Whether that's through templates or direct ones), the parameter gets blank space characters added to it, rendering it useless as jailer won't recognize it as a parameter anymore , with a failed export as the result.

I noticed that this bug is only visible when opening the expression editor (Apart from the error log output), viewing it "inline" or directly displays the parameter correctly:

I attempted many different combinations, (My real filter expressions has lots of concatenation operators), but even simple ones as the example above fails. The only expressions I could get consistent working results with are ones where the full expression is solely the parameter name itself.

Other things that did not help:

• Adding the parameter from the sidebar instead of typing it out.
• Only using the parameter once instead of twice as in my full expression.
• Different naming standards did not resolve the issue. In fact, it happens for the default "old-value" parameter as well.

Expected Behavior

At save, runtime & edit, one expects the parameter to stay the way it was written out when closing the editor.

Actual Behavior

Parameters are changed into: $ { to_plc_id } from: ${to_plc_id}.

Debug Information

Windows 10 1909 Jailer 10.3.5

Description of the Issue

The option -schemamapping is not used for all the statements generated during the generation of a delete script.

Steps to Reproduce the Issue

I execute this command on my database:

"C:\Program Files\Java\jre1.8.0_221\bin\java.exe" net.sf.jailer.Jailer delete C:\data\omega\repo\Omega\omega\database\extractionmodels\equipement.jm com.ibm.db2.jcc.DB2Driver jdbc:db2://localhost:50000/OMEGA *** *** -datamodel C:\data\omega\repo\Omega\omega\database\datamodel -d C:\Users\jcaillon\Desktop\test_delete.sql -threads 4 -entities 50 -format SQL -source-schemamapping REFSCHEMA=BUG44762 -schemamapping REFSCHEMA=REFSCHEMA -scope LOCAL_DATABASE -where "T.IDEQUIPEMENT IN (44762) " -check-primary-keys

Expected Behavior

This should generate a .sql script which deletes rows that are not used aside from the exported rows. The .sql script should target the REFSCHEMA (which is the set to be the target schema).

Actual Behavior

In the generated scripts, I have some statements targeting REFSCHEMA and other statements targeting BUG44762 (which is my source schema).

SQL sample:

Delete from BUG44762.ADRESSE Where IDADRESSE in (112324);
Delete from BUG44762.EXERCICECONTRAT Where IDEXERCICECONTRAT in (443);
Delete from BUG44762.FONCTIONTIERS Where IDFONCTIONTIERS in (13938);
Delete from BUG44762.INTERFACECOMPTABLE Where IDINTERFACECOMPTABLE in (36);
-- sync
Delete from BUG44762.LOTINTERFACE Where IDLOTINTERFACE in (49);
Delete from BUG44762.ADRESSE Where IDADRESSE in (16834);
-- sync
-- sync
-- explicit due to circular dependency
Update REFSCHEMA.UTILISATEUR set IDMAJUTI=null, IDORGANISME=null, IDPARTENAIRE=null Where IDUTILISATEUR=48192;
-- explicit due to circular dependency
Update REFSCHEMA.CONTRAT set IDMAJUTI=null, IDPERIODEGESTION=null, IDDOSSIERAFC=null, IDUTILISATEUR=null, IDCONTRATCONVENTIONPARENT=null Where IDCONTRAT=73043;
-- explicit due to circular dependency

The script will not work and it will modify my source schema instead of my target schema.

Debug Information

Using jailer 8.8.6.

Best regards,

It would be nice if i could select multiple rows (STRG + click) in the data browser and then click "select row(s)" to select only this rows in the table.