exclude-tables

[adrianboangiu]

Hello,

I need to exclude some tables from the output. I tried exclude-tables (a little bit like exclude-columns). I got no error but my excluded tables were present into the output. Would it be possible to implement such a feature?

Thank you

[sualeh]

@adrianboangiu I do not understand exactly what you did. Please include your command-line as well as your SchemaCrawler config file for more clarity. It will be helpful to include the log as well. Take a look at Guidelines for Support for information on how to capture logs.

[adrianboangiu]

I was not sure that the exclude-tables option does not exist (I did not find it in the help but I sais to myself, who knows). I tried the following:

connect --server=oracle --host=... --port=1521 --database=... --schemas=... --user=system --password=...
limit --schemas=COSWIN --table-types=TABLE --exclude-tables=.*\.TEMP_.*
load --info-level=standard
execute --command=list

and in the output I had all the TEMP_... tables:

COSWIN.TEMP_ATTRIBUTE_VALUES                                     [table]
COSWIN.TEMP_AUTO_PLANNED_JOB                                     [table]
COSWIN.TEMP_BLOB                                                 [table]
...

Therefore, I am sure now that the option does not exist. So I wonder if an option exclude-tables can be implemented? And additionally I think that an error should be provided when wrong or unexisting options are used.

[adrianboangiu]

The definition of the excluded tables in the above post is .*\.TEMP_.*

[sualeh]

@adrianboangiu For advanced configuration, you will need to use a SchemaCrawler config file. You can set schemacrawler.table.pattern.include and schemacrawler.table.pattern.exclude, but these will get overridden from the command-line if you use --tables. Please check the help to see how to load the config file.

[sualeh]

I will look into reporting on unknown options that are provided to commands.

[adrianboangiu]

I have modified the file schemacrawler.config.properties to uncomment the following line:
schemacrawler.table.pattern.exclude=.*\.TEMP_.*

I have used the following limit and command:

schemacrawler> limit --schemas=COSWIN --table-types=TABLE
schemacrawler> load --info-level=standard
schemacrawler> execute --command=list

Unfortunatelly, the result still contains the "temporary" tables I want to be excuded:

COSWIN.TEMP_ATTRIBUTE_VALUES [table]
COSWIN.TEMP_AUTO_PLANNED_JOB [table]
COSWIN.TEMP_BLOB [table]
COSWIN.TEMP_BLOB_VERSION [table]
COSWIN.TEMP_CALIBRATION_EQUIPMENT [table]

Can you please tell me what am I doing wrong?
Thank you.

[sualeh]

Try using the config-file command to load the config file, instead of the limit command. Put your schema and tables limits in the config file.

[adrianboangiu]

Hello,

I have modified the config file to exlude some tables and get the tables from some schemas:

schemacrawler.schema.pattern.include=COSWIN.*
schemacrawler.table.pattern.exclude=.*\.TEMP_.*

I was obliged to use the following limit since I couldn't find a way to take only tables via the config file

limit --table-types=TABLE

and I use the config file in the following way:

execute --command=schema --config-file=/home/schcrwlr/schemacrawler.config.properties --output-file=./share/coswin.tables.html

But the temporary tables I want to exclude are still in the output file.

Please advise how to exclude some tables while doing a report with only tables.

Thank you

[sualeh]

@adrianboangiu You seem to be mixing up many commands here. Please could you try this:

config-file --config-file=/home/schcrwlr/schemacrawler.config.properties 
connect --server=oracle --host=... --port=1521 --database=... --user=system --password=...
limit --schemas=COSWIN --table-types=TABLE
load --info-level=standard
execute --command=list

[adrianboangiu]

The solution with the config file worked. Thank you.

However, I think it would be a good idea to have the option exclude-tables available in the shell (command line).
More general, I think all the options available in the config file should be available in the shell because the user needs may vary a lot.
I, for example, have a big database (around 950 tables, 90 temporary tables, +800 views). It is useless to have the entire conceptual model of the database in a single file. Therefore, I want to break the output into several files:

1. html with all non-temporary tables (with table and column comments/remarks)
2. html with views
3. several .png diagrams with subsets of non-temporary tables (without table and column remarks)

To realize the 1st point I need config file (for table exclusion) and limit for table type since it is not possible to limit to table type in config file and it is not possible to exclude some tables in command line option.
To realize the 2nd point I need to limit for views and to realize the 3rd point I need a different config file (because I cannot hide remarks in command line) and some grep to establish the sets of tables.

It would have been easier if the options from config were also available somehow in the shell.

What do you think?

[sualeh]

@adrianboangiu The SchemaCrawler command-line options cover the majority of the common use cases, and the config file is for detailed tweaking. Since the table types option is not in the configuration file, I can add it in, but you can as well use it with the limit command for now. For your use cases, you will probably need to create 3 config files.

The exclusion regular expression is just a convenience. You can do what you need to do with the include pattern. Regular expressions are very powerful and can "look-ahead" for "_TEMP" in your case. Please ask ChatGPT to help with a prompt like "How do I exclude strings that contain "_TEMP" in them using a regular expression?".

The "execute" command runs a SchemaCrawler command, and depending on the command, other options become available. Commands that show results support the --no-remarks option, which you can pass into the execute command-line.

In other words, you should be able to do everything you need to do without resorting to config files.