Table of Contents

Datasource Limitations

Velocity strives to provide a consistent cross-platform database interface, but certain databases lack functionality available in others. Rather than restricting Velocity to only the lowest common denominator, we've documented each database's limitations.

This allows you to:

  • Use advanced features when targeting databases that support them
  • Avoid features incompatible with your target databases
  • Make informed decisions about which databases to support in your application

DB2

Data Types:

  • Timestamp: The Velocity Timestamp DataType (which stores timezone information alongside date/time values) is not supported. Using Timestamp will fall back to DateTime, and timezone information will not be accurately stored or retrieved.

Constraints:

  • Non-deterministic check constraints: Check constraints cannot contain non-deterministic functions like TODAY() or NOW().

Geospatial:

  • Self-intersecting polygons: Cannot be added to DB2, whereas other databases allow adding them and testing validity with ST_IsValid().
  • Interior ring ordering: Polygon interior rings are ordered by an undisclosed DB2 algorithm, not by the order specified in code. This makes ST_InteriorRingN() non-deterministic regarding ring order.

Schema Operations:

  • Drop schema: Cannot drop schemas that contain objects. Use EmptySchema first, then drop the schema.

MySQL

Data Types:

  • Timestamp: Limited support for the Velocity Timestamp DataType. Timestamps are converted to UTC and timezone information is discarded.

Constraints:

  • Non-deterministic check constraints: Check constraints cannot contain non-deterministic functions like TODAY() or NOW().

Queries:

  • Full outer joins: Not supported. Full outer joins can be simulated using UNION of left and right joins, but there is no direct syntax support.

Oracle

Data Types:

  • Integer types: Oracle uses the NUMBER DataType with scale and precision instead of traditional integer types. Velocity maps standard integer types to NUMBER configurations that can hold larger values than typical, ensuring values are stored correctly. However, this means returned values may exceed the original data type's range. Best practice: Use Long for all whole number types when Oracle is in your database mix.

Constraints:

  • Non-deterministic check constraints: Check constraints cannot contain non-deterministic functions like RANDOM().

Geospatial:

  • Spatial indexes (Oracle 19c): Cannot create spatial indexes on tables or columns using escaped quote-style identifiers. Workaround: Set the Velocity context setting EscapeIdentifiers to false.

    Warning: Use this setting with extreme caution. Changing it after schema creation will cause significant issues.

PostgreSQL

Data Types:

  • Timestamp: Not fully supported. Timestamps are converted to UTC with the timezone offset shown relative to the current client session. To maintain the actual timezone, use DateTime DataType and store the timezone in a separate column.

SQL Server

Schema Operations:

  • Drop schema: Cannot drop schemas that contain objects. Use EmptySchema first, then drop the schema.

SQLite

Schema Operations:

  • Multi-schema support: Velocity supports connecting to multiple schemas (databases in SQLite terminology) simultaneously using ATTACH DATABASE. However, SQLite does not support foreign keys across different schemas—any cross-schema foreign key constraints will fail.

Constraints:

  • Post-creation constraints: Cannot create or drop constraints after table creation. All constraints must be defined when creating the table.

Table Alterations:

  • ALTER TABLE limitations: SQLite has very limited ALTER TABLE support:
    • Cannot change column DataType directly—requires table recreation
    • Cannot add default constraints to existing columns
    • Cannot modify most column properties after creation
    • Workaround: Velocity can recreate tables when necessary, but this is more complex than standard ALTER operations.

Teradata

Full-Text Search:

  • Not supported. Teradata does not provide full-text indexing capabilities. All full-text search operations will fail.

Date/Time Functions:

  • UTC not supported for NOW()/TODAY(): The Velocity functions NOW() and TODAY() in DEFAULT constraints return the current date/time in the Teradata server's local timezone, not UTC. Workaround: Run the Teradata server in UTC timezone.

Schema Operations:

  • Drop schema: Cannot drop schemas that contain objects. Use EmptySchema first, then drop the schema.