Add a check to find object names that do not follow naming convention

[mfvanek]

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_).

See https://www.postgresql.org/docs/17/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

But using liquibase XML syntax we can easily get wrong names

  <changeSet author="author" id="add-index-on-task-expiration-date-status-v2">
    <createIndex tableName="my_task" indexName="idx-my-task-expired-at-status">
      <column name="task_expired_at"/>
      <column name="task_status"/>
    </createIndex>
  </changeSet>

IndexWithNulls.of("my_task", "\"idx-my-task-expired-at-status\"", 0L, "task_expired_at")

[gmottajr]

Hi @mfvanek,
Can I collaborate through this one?

Required Clarifications

Before starting to work on the issue, I have a few questions to ensure alignment:

1. Scope of Validation:

   • Should this check be limited to Liquibase XML configurations?
   • Or should it be a general utility for validating any SQL object names?

2. Expected Workflow:

   • Should this validation run as part of build-time checks (e.g., Maven/Gradle plugin)?
   • Should it be integrated into Liquibase migrations to validate during execution?

3. Handling Violations:

   • How should violations be handled?
     • Errors that fail the build/execution?
     • Warnings that flag potential issues?
     • Automatic fixes (e.g., sanitizing invalid names)?

4. Integration Point:

   • Should this validation be integrated into an existing framework or tool in your codebase?
   • Or should it be a standalone utility/module?

5. Test Cases:

   • Can you provide examples of edge cases or problematic names you've encountered? This would help ensure comprehensive testing.

6. Acceptance Criteria:

   • Can you list some specific acceptance criteria for this feature?

Let me know if there’s anything else you'd like me to consider or if you have preferences for implementation details. Looking forward to your feedback!

[mfvanek]

Hi @gmottajr
Take a look at series of articles

• https://habr.com/ru/articles/800121/
• https://habr.com/ru/articles/803841/
• https://habr.com/ru/articles/821993/

Also please read

• https://habr.com/ru/companies/tensor/articles/488104/

and

• https://habr.com/ru/articles/490824/

Sorry, currently only in russian. Please use machine translation.

We use the system catalog (pg_catalog) https://www.postgresql.org/docs/current/catalogs.html and work with finite database state (after all migrations are applied).

---

Should this check be limited to Liquibase XML configurations?

No, it shouldn't
It should starts as an sql query to find all misspelled names in system catalog.
See https://github.com/mfvanek/pg-index-health-sql/blob/master/sql/possible_object_name_overflow.sql as basic example

[mfvanek]

See also

Linter: schemacrawler.tools.linter.LinterTableWithQuotedNames
Checks for tables that have spaces in table or column names, or names that are reserved words in the ANSI SQL standard.

https://www.schemacrawler.com/lint.html