chore!: Change H2 Oracle longType and longAutoincType from NUMBER(19) to BIGINT and add CHECK constraint in Oracle and SQLite

[joc-a]

Description

Detailed description:

• What: The SQL type of H2 Oracle Long type were changed. A CHECK constraint was also added for SQLite and Oracle.
• Why:

1. H2 enforces the range on its own when BIGINT is used.
2. It is a preparation step for making detecting column type change for migration easier

• How: The CHECK constraint with a range doesn't reject out-of-range values in SQLite because SQLite can store values that exceed the 64-bit integer range as REAL (floating-point numbers), bypassing the constraint. Using the condition typeof(long_column) = 'integer' instead of the range ensures that only 64-bit integer values pass the check, preventing out-of-range values from being stored as REAL and slipping past the constraint. The reason all of this is done the createStatement() function and not in the column creation function as usual is because we need to invoke currentDialect to check the dialect and construct the CHECK constraint accordingly, and this cannot be invoke in the column creation function.

---

Type of Change

Please mark the relevant options with an "X":

• [ ] Bug fix
• [ ] New feature
• [ ] Documentation update
• [x] Improvement

Updates/remove existing public API methods:

• [ ] Is breaking change

Affected databases:

• [ ] MariaDB
• [ ] Mysql5
• [ ] Mysql8
• [x] Oracle
• [ ] Postgres
• [ ] SqlServer
• [x] H2
• [x] SQLite

Checklist

• [x] Unit tests are in place
• [x] The build is green (including the Detekt check)
• [x] All public methods affected by my PR has up to date API docs
• [x] Documentation for my change is up to date

---

Related Issues