wisser / jailer Goto Github PK

Hello,

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

The use case is the following; I have a database with a consequential amount of data (~12Go) that I wish to trim down to a few Mo. For this, I build a WHERE predicate that target all the data I don't want to keep.

I want to delete the data using jailer. But for this, the only solution is to generate the .sql file that I will then need to execute to actually delete anything.

The problem is that this 2 step process is slow and moreover, the .sql file generated is too big.

Describe the solution you'd like

What I wish I could do is use the delete command with the option INTRA_DATABASE. We can do this with the export command to directly copy data from a schema to another and generate a receipt file instead of the export .sql.

The command would look like:

jailer.bat delete database\extractionmodels\equipement.jm com.ibm.db2.jcc.DB2Driver jdbc:db2://localhost:50000/OMEGA db2admin adminDb2 -datamodel database\datamodel -d receipt.txt -threads 4 -entities 50 -format INTRA_DATABASE -source-schemamapping REFSCHEMA=REFSCHEMA -schemamapping REFSCHEMA=REFSCHEMA -scope LOCAL_DATABASE -check-primary-keys

Describe alternatives you've considered

Generate the delete .sql and then execute it.

Additional context

N/A

Best regards

Hi, let's assume I've created a data model at time t0. While working on data model, I also excluded some tables (with "delete" button) that are not important to export. (like log tables or some temporary tables left from other people's workings)

This database is continuously being updated by other teams. (schema changes like add table, alter column ...) So I have to check changes from time to time and update my data model continuous database subsetting.

I'm using "UI > model > analyse database" to get database updates at a later time t1.

Now, Jailer shows new tables and associations but I also see previously excluded tables as new tables.

When I hit "ok" button, those tables are added to data model. Or I have to remove them again from data model before "ok" button.

• Is there an easy way to define some tables as "excluded" or "ignored" from analyse database?
• Or is there another better workflow for this use case? (Maybe I'm in the wrong way.)

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

Description of the Issue

When opening the export window from within the Extraction model editor, if you had "Local database" set as your "Working table scope" in your last export, the dropdown menu for "Working table Schema" will be enabled again.
To disable it again, you have to cycle the "Working table scope" setting back to "local database".

Expected Behavior

That the dropdown menu for "Working table schema" stays disabled when the table scope is still set to "local database" after the last export.

Debug Information

Windows 10 1909
Jailer 10.3.5

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

For now, the details panel will update the value on hover. I want to have a options to toggle between link on hover and link on select (so I can inspect the value)

Describe the solution you'd like

Add a button at the top of detail pannel to toggle Link on Hover/Link on Select.

Describe alternatives you've considered

Double click on the row to show details popup which is not convenient.

Additional context

• Be able to select/copy cell value on the details panel also.
• Change double link behavior from Show Details to Select Cell Value
• Use Details Panel as a replacement for details popup

Thanks for the awesome tool Ralph. I had a query about How to use a different schema for the working tables. I see no such option on the UI. Can you please help

Description of the Issue

The execution environment was upgraded to JDK11. Jailer failed but no reason was stated in the logs or console output.
The reason was a NoClassDefFoundError caused by missing Jaxb.
This issue is not about the missing Jaxb, but for the missing logging of the Exception. It took some effort to get to the bottom of the problem.

Since it is an Error it slipped through the catch(Exception) in Jailer.jailerMain. The subsequent catch(Throwable) in Jailer.main does not log it, because it assumes exceptions were already logged.

The missing stacktrace:

java.lang.NoClassDefFoundError: javax/xml/bind/JAXBException
        at net.sf.jailer.datamodel.PrimaryKeyFactory.createPrimaryKey(PrimaryKeyFactory.java:85)
        at net.sf.jailer.datamodel.DataModel.<init>(DataModel.java:429)
        at net.sf.jailer.datamodel.DataModel.<init>(DataModel.java:324)
        at net.sf.jailer.extractionmodel.ExtractionModel.<init>(ExtractionModel.java:200)
        at net.sf.jailer.datamodel.PrimaryKeyFactory.createUPKScope(PrimaryKeyFactory.java:191)
        at net.sf.jailer.Jailer.jailerMain(Jailer.java:211)
        at net.sf.jailer.Jailer.main(Jailer.java:120)
Caused by: java.lang.ClassNotFoundException: javax.xml.bind.JAXBException
        at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:581)
        at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:178)
        at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:521)
        ... 7 more

Expected Behavior

The stacktrace of the error should be logged.

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!

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 :)

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.

I guess, We are hinting towards the lack of documentation in wiki.

Hi,
I have an error when exporting data to SQLite.

Discovered behavior:
When I export from PostgreSQL -> SQLite, I get 'true' and 'false' as values for Boolean values.

Expected behavior:
SQLite doesn't support boolean values. According to https://stackoverflow.com/questions/843780/store-boolean-value-in-sqlite the values should be stored as 0 and 1

Environment:
SourceDB: PostgreSQL
TargetDB: SQLite
Jailer version: 8.2.1

Some Java distributions (e.g. Corretto) no longer provide a JRE as it is now preferred to use jlink to provide a software package that does not require a JRE to run.

I saw the patch notes on the Jailer site stating that since the 4th of February one can submit a database containing circular references at it will break them. I was very curious as to how this would work, however upon a closer inspection of the code, I could only find the removal of reflexive cycles. Am I missing something?

P.S: Sorry for posting this in an issue tab, but I could not find a better way to contact the devs.

Please Publish Jailer to the AUR repositories or provide instuctions on how to install on arch linux

Thank you very much.

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

Describe the solution you'd like

Describe alternatives you've considered

Additional context

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

Working in the Data Browser, on the Desktop tab on a macOS laptop and a Magic Mouse 2/Trackpad I often find
the tables/table windows & relationships zooming in/out while what I wanted was to navigate/scroll across the table data or the entire display instead of the zoom level. I find myself constantly readjusting the zoom level.

Data Browser -> Window menu:

Default behaviour on a Mac is when you use the scroll-wheel, you move the page/document/window further along instead of adjusting the zoom level.

Describe the solution you'd like

Create a way to distinguish between Navigating the Desktop tab and Zooming the Desktop tab, besides the little box on the left side of the window below Navigation.

Some of the ideas I've come up with:

• Add an option: Scrolling will zoom (default) Yes/No
• Add a keyboard shortcut + scrolling command that changes the behaviour (example on Mac: Command + Scroll = Zoom, while normal Scroll = Navigate the display)
• Since I rarely zoom in/out, and use the little window on the left to navigate more often: disable the scroll wheel for zooming, leave the keyboard shortcuts in case you want to

Describe alternatives you've considered

For the past week+ I've been trying to adjust to the scroll = zoom behaviour when not scrolling within a table, but often OS habits take over and I find the entire zoom level adjusted (also Magic Mouse 2 is pretty sensitive to scrolling).

Additional context

Sorry if this has already been discussed / decided upon and again, thanks on the amazing work on this tool!

When I open Jailer or Jailer Data Browser, can't add new db connection because there is no button for it. For now I must edit the existing one

Description of the Issue

Expected Behavior

There is a button to create new db connection

Actual Behavior

There is no button to create new db connection

Here is a fuller description of what I have in mind:
https://softwarerecs.stackexchange.com/questions/82300/i-need-to-navigate-db-state-using-jpa-definitions-for-viewing-data

Questions

1. Is this already part of your product? (If so, I could not find it in the documentation.)
2. If not -- is it on your roadmap?
3. If not -- can you suggest and point me to a plugin approach if I wanted to extend your SW by the above capabilities?

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

The FAQ (http://jailer.sourceforge.net/faq.html) has this question and answer:

Q: I'm getting the message "Table has no primary key". What's wrong?
A: Jailer cannot handle tables without a primary key. Exporting data is not possible if the subject table is associated with a
 table without a primary key. In this case, you should manually define a primary key in the data model of the tool (not in the 
database!) using the Data Model Editor. Note that a key must be unique.

(On Oracle, however, rowid-pseudo columns can be used instead of primary keys)

I suggest adding that similarly to Oracle's rowid-pseudo column the ctid system column in PostgreSQL can also be used as a virtual primary key of type tid.

https://www.postgresql.org/docs/current/datatype-oid.html

A final identifier type used by the system is tid, or tuple identifier (row identifier). This is the data type of the system column ctid. A tuple ID is a pair (block number, tuple index within block) that identifies the physical location of the row within its table.

I have many-to-many join tables generated by Hibernate, which don't have primary keys generated but by using ctid I could make them work with jailer like this:

Maybe ctid could be also offered as an option similar to "Identity column (MS SQL)".

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!

Hi, first of all thanks to team for this great product. 🙏

I'm planning to keep .jailer in VCS (example, git) for tracking changes.

At first look, extraction model and data model seems to be important and valuable for us.

• Which files or folders should be included in VCS from .jailer and shareable between database developers?
• Which files or folders should be defined in .gitignore? (mostly reproducible, locally generated and non-shareable ones)

I've read documentation and searched in forum/github issues but couldn't find a clear answer yet. 🤔

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,

Lack of navigation arrows. They have been seen last time in version 10.6 - they are very useful.

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

We are using the jailer-engine embedded in our tool chain. To integrate new Jailer versions we have to manually upload the jailer-engine jar to our local repository. This manual process is somewhat cumbersome and error prone.

Describe the solution you'd like

It would be nice if jailer-engine-jar was published to the maven central repository(https://maven.apache.org/repository/).
Then our build process would just fetch the versions from there.
The dependencies (args4j, com.h2database:h2, log4j) should be stated in the pom.xml so that dependencies can be fetched transitive.

Describe alternatives you've considered

Manually uploading the jars to our local repository.

Hi Ralf,

using version 9.3 of Jailer creating of a table results in a syntax error.

net.sf.jailer.database.SqlException: "ERROR: syntax error at or near "CREATE"
Position: 103" in statement "CREATE TABLE jailer.JAILER_IFM ( OLD_VALUE1 int8, NEW_VALUE1 int8, OLD_VALUE2 int8, NEW_VALUE2 int8 ) CREATE UNLOGGED TABLE"
at net.sf.jailer.database.Session.execute(Session.java:967)
at net.sf.jailer.util.SqlScriptExecutor$3.run(SqlScriptExecutor.java:371)
at net.sf.jailer.util.SqlScriptExecutor.execute(SqlScriptExecutor.java:453)
at net.sf.jailer.util.SqlScriptExecutor.executeScript(SqlScriptExecutor.java:346)
at net.sf.jailer.util.SqlScriptExecutor.executeScript(SqlScriptExecutor.java:129)
at net.sf.jailer.api.Importer.execute(Importer.java:57)
[...]

According to PostgreSQL documentation the UNLOGGED parameter should be placed in prefix.

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

The FAQ (http://jailer.sourceforge.net/faq.html) suggests using SQL functions and test tables to anonymise data; in addition to this, it would be great to be able to call certain key words to generate fake data. This would avoid having database tables who are not business related.

Describe the solution you'd like

"Java Faker" (https://github.com/DiUS/java-faker) offers plenty of "fake data" options that would be useful for data anonymisation.

As "${old-value}" is available in the filter editor, I had in mind the ability to call on "${faker.name().fullName()}" or "${faker.address().streetAddress()} in order to keep it as generic as possible.

If you approve this issue, I can look into implementing it.

MacBook-Pro-8:Jailer sean$ ./JailerDataBrowser.sh
:/Users/sean/Library/Preferences/DataGrip2018.2/jdbc-drivers/PostgreSQL/42.2.5:config:lib/junit.jar:lib/commons-logging.jar:lib/log4j.jar:lib/args4j.jar:lib/spring.jar:lib/prefuse.jar:lib/sdoc-0.5.0-beta.jar:lib/activation-1.0.2.jar:lib/jaxb-core-2.3.0-b170127.1453.jar:lib/jaxb-impl-2.3.0-b170127.1453.jar:lib/jaxb-api-2.3.0-b170201.1204.jar:lib/jsqlparser-1.3.jar:lib/tablefilter-swing-5.3.1.jar:jailer.jar
Error: Could not find or load main class net.sf.jailer.ui.databrowser.DataBrowser
Caused by: java.lang.ClassNotFoundException: net.sf.jailer.ui.databrowser.DataBrowser

The docs mention jailer.jar which is missing from repo. However, the sourceforge download contains the jar.

Description of the Issue

I opened a model file and saved it, then closed Jailer. I noticed an error in the file name so I renamed the file on the file system. I started Jailer again, it tried to load the recent file list and could not find the file. An error message with stack trace appeared

Steps to Reproduce the Issue

1. open a jm-file
2. save it
3. close Jailer
4. rename jm-file
5. start Jailer again

Expected Behavior

remove missing files from the recent file list and continue to start Jailer.

Actual Behavior

NPE:
Help Desk: https://sourceforge.net/p/jailer/discussion/
Mail: [email protected]

Jailer 8.8.8.2

java.lang.NullPointerException
at net.sf.jailer.ui.DataModelManagerDialog.initJMTable(DataModelManagerDialog.java:682)
at net.sf.jailer.ui.DataModelManagerDialog.(DataModelManagerDialog.java:269)
at net.sf.jailer.ui.ExtractionModelFrame$70.(ExtractionModelFrame.java:2152)
at net.sf.jailer.ui.ExtractionModelFrame.createFrame(ExtractionModelFrame.java:2152)
at net.sf.jailer.ui.ExtractionModelFrame$69.run(ExtractionModelFrame.java:2074)
at java.awt.event.InvocationEvent.dispatch$$$capture(InvocationEvent.java:311)
at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:756)
at java.awt.EventQueue.access$500(EventQueue.java:97)
at java.awt.EventQueue$3.run(EventQueue.java:709)
at java.awt.EventQueue$3.run(EventQueue.java:703)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:726)
at net.sf.jailer.ui.Environment$JEventQueue.dispatchEvent(Environment.java:116)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)

Debug Information

Jailer 8.8.8.2, current source from github, commit c04a7ad

Jailer 9.1.4.3

I have a large sql file, about 450MB, trying to use the "Import Sql Data" tool.
After about 7 minutes into the load I get a bunch of "WARN - cancellation request received" and
then I get "ERROR - "Read timed out" in statement".
later in the log I get this.
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Read timed out

This error happens on a remote SQL Server instance.
I ran the same process locally and it works fine, no error.

I assume it's just taking too long and there is some time out setting, but I couldn't find anything about it in the documentation.

Thanks.

I'm having trouble connecting to a database for certain passwords when running Jailer from the command-line:
Error: org.postgresql.util.PSQLException: FATAL: password authentication failed for user...

I believe it is because of special characters in the password because a password like "pancakes" will work but a password like "qx#PA7xXu$Oo" will fail. However, when I connect with the GUI, it works for every password. I tried percent-encoding, so the second password would be passed like "qx%23PA7xXu%24Oo".

The command I am running is:

sh jailer.sh build-model org.postgresql.Driver jdbc:postgresql://<host>:<port>/<database>?ssl <user> "<password>" -datamodel datamodel
I have tried without ssl as well and same outcome.

I'm using the postgresql-42.2.0.jre7.jar driver.

This is untenable when using a password manager with 32 character passwords!

Description of the Issue

I am able to connect to this same database with both TablePlus and DBeaver, but not Jailer for some reason. I've triple verified the correct JDBC url format, host, username, and password.

This probably isn't specific to AWS; I'm guessing I'd have the same issue with any cloud provider. It's worth noting that I am on a company VPN in order to access the database, but again TablePlus and DBeaver handle that just fine (and I assume it's not an issue with Jailer and VPNs specifically).

Steps to Reproduce the Issue

Add a remote database connection pointing to an AWS-hosted Postgres instance and try to test the connection.

Expected Behavior

I'd expect Jailer to be able to connect to databases like TablePlus and DBeaver.

Actual Behavior

It doesn't seem to work no matter what I try; I always get the same socket timeout exception.

Debug Information

Jailer version: 12.3
Driver: lib/postgresql-42.2.16.jar

Hello,

When creating Subsetter like that:

`@Resource(name = "jdbc/datasrouce")
DataSource dataSource;

  @PostConstruct
public void init() throws IOException {
    subsetter = new Subsetter(dataSource,
        DBMS.ORACLE,
        RestControler.class.getResource("model"),
        RestControler.class.getResource("extraction-model.csv"),
        ScriptFormat.SQL);`

and executing using execute method, I'm getting NPE here:
java.lang.NullPointerException at net.sf.jailer.util.Quoting.<init>(Quoting.java:90) at net.sf.jailer.entitygraph.remote.RemoteEntityGraph.<init>(RemoteEntityGraph.java:83) at net.sf.jailer.entitygraph.remote.RemoteEntityGraph.create(RemoteEntityGraph.java:120) at net.sf.jailer.subsetting.SubsettingEngine.export(SubsettingEngine.java:1303) at net.sf.jailer.api.Subsetter.execute(Subsetter.java:150) at net.sf.jailer.api.Subsetter.execute(Subsetter.java:118)

if(session.dbUrl.toLowerCase().startsWith("jdbc:jtds:"))

In my case session.dbUrl is null.

I think this is comming from Session class where
this.dbUrl = dataSource instanceof BasicDataSource?((BasicDataSource)dataSource).dbUrl:null;

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

For now, Jailer only allow me to restrict the dataset on the main table, if I set condition on the related table, it only hide other records on this particular table and other sub tables (perform the condition on the ON clause), what I want is to hide other records from the main table and other tables also (perform the condition on the WHERE clause)
Assume I have 2 tables: EMPLOYEE (main table) and EMPLOYEE_COUNTRY (related table)
Now if I want to find all the employee with a particular name and country, I can't do this.
The correct query I want is:

SELECT * FROM EMPLOYEE 
LEFT JOIN EMPLOYEE_COUNTRY 
ON EMPLOYEE.ID = EMPLOYEE_COUNTRY.ID 
WHERE EMPLOYEE.NAME = ? AND EMPLOYEE_COUNTRY.NAME = ?

But what Jailer is doing:

SELECT * FROM EMPLOYEE 
LEFT JOIN EMPLOYEE_COUNTRY 
ON (EMPLOYEE.ID = EMPLOYEE_COUNTRY.ID AND AND EMPLOYEE_COUNTRY.NAME = ?)
WHERE EMPLOYEE.NAME = ? 

Describe the solution you'd like

-To have another panel for global WHERE, not just condition for joining.

• Or to have another textbox on tables for WHERE condition

Describe alternatives you've considered

I can use 'Start Navigation Here' to turn sub table into main table but it only accept conditions on main table only, what I want is conditions on multiple tables.

Additional context

Hi,

Having an issue exporting data when table uses a composite primary key of varchar(11) and datetime2. The datetime2 appears to be truncated to datetime, and subsequent queries don't match the datetime2, resulting in 0 rows exported.

When exporting I get a popup stating

Warning: The number of rows collected (8) differs from that of the exported ones (0).
This may have been caused by an invalid primary key definition.
Please note that each primary key must be unique and never null.
It is recommended to check the integrity of the primary keys.

The sql.log shows the PK datetime2 is converted to datetime, which is truncating the value.
this is the PK sql.
Select '49452127001' PK0, convert(datetime, '2019-03-01T04:08:38.153', 126) PK1
I did some experimenting and found that this sql works by using datetime2 and including all the decimal places.
(Select '49452127001' PK0, convert(datetime2, '2019-03-01T04:08:38.1533333', 126)

Jailer 9.1.4
Microsoft SQL Server (MS SQL Server) 13.00.4522
Thanks is advance... let me know if you need more info.

For the record, I'm not a fan of datetime in primary keys, but changing it now is not an option.

Hi,

I am using jailer_8.5.5

$ java -version
java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)

macOS Mojave
Version: 10.14.3

Im running with ./jailerGUI.sh

Copy paste does not work. I've tried with Cmd+V and Right-Clicking then pasting, both does not work. Also, the right-click context menu does not show up as well.

Description of the Issue

Jailer doesn't handle SQLite fields/columns with space in field name correctly

Steps to Reproduce the Issue

Try loading any table with a field name containing a space e.g. "recording location"

Expected Behavior

Fields containing on or more spaces in field name should be handled like any other.

Actual Behavior

Throws an error

Debug Information

Arch Linux, Jailer 9.1.7.2

Add a limit parameter as a safeguard for exports.
If the number of records exceeds the limit, Jailer should stop and throw an error.

see https://sourceforge.net/p/jailer/discussion/700499/thread/953062b743

Description of the Issue

When making an deletion procedure from Model Extraction Screen similar to what's detailed in here; there seem to be systematically more rows inserted (in the insert script) than deleted (in the delete script).

Steps to Reproduce the Issue

1. Get a database similar to http://jailer.sourceforge.net/deleting-data.htm
2. In the Extraction Model Editor Select a parent entity and its children (disable / restrict the parent associations)
3. Be sure that the checkbox "exclude from deletion" is on "No" or "No by default" for all the table in question.
4. Click on export into let's say insert.sql and delete.sql scripts

Assert that Insert.sql has more inserts than in the delete script; ie

Expected Behavior

If 8 rows are exported in the insert.sql, then jailer should be able to generate a delete.sql with the same number of delete, so that we can always remove what we exported. If we come back to the example web page; on my machine I miss a few "Delete from PROJECT_PARTICIPATION" inside the delete.sql script.

Debug Information

Working on Ubuntu 18.04 with latest jailer 10.2.4 (zip version). NB: the problem was already present in the 9.5.2 version

I'm currently using the API to generate my extraction file programmatically rather than with the GUI.
When doing so, I came across an issue adding a restriction using getRestrictionModel().addRestriction() where removePreviousRestriction = false.
This properly wraps the previous restriction in () and appends, but when written out to the file, the first '(' and last ')' are removed. The damage seems to occur on calls to association.getRestrictionCondition(), the condition in RestrictionModel.restriction map is correct.

Example:

getRestrictionModel().addRestriction(... "cond1", false)
getRestrictionModel().addRestriction(... "cond2", false)

Results in:
RestrictionModel.restriction have '(cond1) and (cond2)'

In the file and calls to getRestrictionCondition() result in:
'cond1) and (cond2'

Perhaps this is no longer necessary or you could change the code so RestrictionModel.restriction maintains a List<> of conditions per association. Then join the list with and's on the getRestrictionCondition call. One benefit to the latter is multiple calls will not result nested parans.

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

I want to copy value on selecting cell to clipboard

Describe the solution you'd like

Highlight selecting cell, be able to copy the value of selecting cell to clipboard (right click -> Copy Cell Value, Ctrl+C).

Describe alternatives you've considered

• Need to double click to open details panel then copy which is tiresome if you need to do it a lot.
• Use Edit mode which is unsafe (modify the cell value accidentally).

Additional

Even better if I can copy Row Value from Context Menu also (Copy Row Value -> AS CSV, AS SQL INSERT, AS JSON...)

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

When using PostgreSQL, we have database with the several related schemas, in context of PostgreSQL. In this respect, table name is test.table with test denoting schema.

Describe the solution you'd like

• If I export the data to another PostgreSQL database, I would like to have an option for preserving the same partitioning of the tables into schemas.
• For databases without such schema support, would be great to have an option for formatting the end table name. For example, test.table -> test_table.

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.

Would love to see an option that would create MSSQL scripts that qualify column names with square brackets instead of double quotes, as show in the examples below.

Insert into Daybreak.UserAgreementHistories([Timestamp]) values

instead of

Insert into Daybreak.UserAgreementHistories("Timestamp") values

Description of the Issue

Log4j 1.x has reached End of Life in 2015 and is no longer supported. Vulnerabilities reported after August 2015 against Log4j 1.x were not checked and will not be fixed. Users should upgrade to Log4j 2 to obtain security fixes.
Our security team declared this file as potentialy vulnerable: jailer/lib/log4j.jar

Could you upgrade this library?

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.

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)