Fix Null entry value on HTMLConstraintsPage.list

[zachdecook]

Got this error when running schemaspy over a mssql17 (really SQL Server 2022 16.00.*) database.

Caused by: java.lang.NullPointerException: Cannot invoke "String.trim()" because the return value of "java.util.Map$Entry.getValue()" is null
	at org.schemaspy.view.HtmlConstraintsPage.lambda$collectCheckConstraints$1(HtmlConstraintsPage.java:83)

Testing notes

• Be running openjdk 17 (for some reason some dependency complained about version 22)

  • e.g. with brew: brew link --overwrite openjdk@17

• ./mvnw -P release package

• use the file target/schemaspy-7.0.0-SNAPSHOT-app.jar as your .jar when calling java -jar

• be using a DB user that doesn't have VIEW DEFINITION permissions

Expect to see this now (and have no error)

WARN - Constraint text came back null for name_of_check_constraint using SQL 'select OBJECT_NAME(parent_object_id) as table_name, name AS constraint_name, definition as text from sys.check_constraints'

[npetzall]

Hi, the null pointer issues seem to be that the selectCheckConstraintsSql has an empty value in the definition column.

The query that is used is select OBJECT_NAME(parent_object_id) as table_name, name AS constraint_name, definition as text from sys.check_constraints

To investigate the issue select * could be used once the constraint has been identified. Since a check constraint should have a definition. It seems strange that it would be missing.

The code is a bit of a mess around the Table class and we should log something and set empty string. This could be done at https://github.com/schemaspy/schemaspy/blob/4b81d234220940ab3f98fb1a5977d433e6371a51/src/main/java/org/schemaspy/input/dbms/service/DatabaseService.java#L548

We should probably remove/deprecate https://github.com/schemaspy/schemaspy/blob/4b81d234220940ab3f98fb1a5977d433e6371a51/src/main/java/org/schemaspy/model/Table.java#L112 since it's only used in a test and it creates an alternate path to add CheckConstraints. But I'll admit the way it's done in DatabaseService is horrible.

This should be scoped to setting empty string and logging. Let me know if you're up for the task.

[zachdecook]

@npetzall Here's one of the rows from the table (some data changed, not that it would be sensitive). Sure enough, they all have definition as NULL

{
    "name": "name_of_check_constraint",
    "object_id": 123456789,
    "principal_id": null,
    "schema_id": 1,
    "parent_object_id": 111111111,
    "type": "C ",
    "type_desc": "CHECK_CONSTRAINT",
    "create_date": "2001-09-09 09:09:09",
    "modify_date": "2001-09-09 09:09:09",
    "is_ms_shipped": 0,
    "is_published": 0,
    "is_schema_published": 0,
    "is_disabled": 0,
    "is_not_for_replication": 0,
    "is_not_trusted": 0,
    "parent_column_id": 7,
    "definition": null,
    "uses_database_collation": 1,
    "is_system_named": 1,
}

So I think this just needs to be

setting empty string and logging

I'll see if I have time for it tomorrow.

[npetzall]

I've done a quick test with mssql 2022 and saw that I got the definition or at least not null. I was first thinking that we might need to update the query for 2022.

But now I wonder if it has anything to do with permissions. Since I've used the SA account in testing.

I'll look into that separately.

[zachdecook]

Yeah, likely permissions error, according to https://stackoverflow.com/a/77185959.

My DB user doesn't have the VIEW DEFINITION DATABASE permission.

[zachdecook]

This should be scoped to setting empty string and logging. Let me know if you're up for the task.

@npetzall

Made the change. It now spits out a warning like this whenever this problem would happen.

WARN - Constraint text came back null for name_of_check_constraint using SQL 'select OBJECT_NAME(parent_object_id) as table_name, name AS constraint_name, definition as text from sys.check_constraints'

[zachdecook]

@npetzall Can you review this? It fixes a crash for mssql and puts out a warning instead as you suggested.