Junit SQL Storage with Microsoft SQL Server

Contributing
1

Out of curiosity, I looked at how difficult it would be to make Junit SQL Storage support Microsoft SQL Server. I’m not currently planning to work on this but here are my findings.

  • Junit SQL Storage connects to the global database configured in the Database plugin, which can already use the Microsoft SQL Server Database plugin.
  • Flyway claims to support SQL Server.
  • Would need a src/main/resources/db/migration/sqlserver directory alongside mysql and postgres.
  • I presume calls like StringUtils.truncate(job, MAX_JOB_LENGTH) count UTF-16 code units. SQL Server uses nvarchar for UTF-16 strings and varchar for 8-bit strings (which can be UTF-8 in newish versions), and has an 8000-byte limit unless MAX is used, so the column types should be nvarchar(255), nvarchar(500), nvarchar(1000), and nvarchar(MAX).
  • The SQL Server timestamp type is an alias for rowversion, not related to wall-clock time. The Java code doesn’t seem to read the caseResults.timestamp column. I guess it should be defined as timestamp datetime2 NOT NULL DEFAULT SYSUTCDATETIME() for SQL Server.
  • DatabaseTestResultStorage.getHistorySummary(int offset) uses ORDER BY build DESC LIMIT 25 OFFSET ? syntax that SQL Server does not support. This would have to be replaced with ORDER BY build DESC OFFSET ? ROWS FETCH FIRST 25 ROWS (see SELECT - ORDER BY Clause (Transact-SQL)), or with a subquery involving ROW_NUMBER.
  • DatabaseTestResultStorage.getCountOfBuildsWithTestResults() uses SELECT COUNT(DISTINCT build) as count, where the column name count conflicts with a reserved keyword and would have to be renamed or quoted.
  • DatabaseTestResultStorage.getFailedSinceRun(CaseResult caseResult) and DatabaseTestResultStorage.getPreviousResult() likewise use unsupported LIMIT syntax.
  • DatabaseSchemaLoader.migrateSchema() checks databaseDriverName.contains("mysql") in order to run the correct SQL scripts. IIUC, this databaseDriverName apparently is “org.jenkinsci.plugins.database.mysql.MySQLDatabase” rather than anything defined by the underlying JDBC driver. Still, this kind of string matching on the class name seems rather brittle, especially if DatabaseTestResultStorage will also have to do it. And it won’t work if the Jenkins administrator chooses GenericDatabase and inputs the JDBC URL manually.
2 Likes
2

I’m wonder if there is anything to add on Flyway API. I never used MSSQL so i’m not sure if there is any additional dependency to add (in addition to the driver)

I didn’t upgraded yet to Flyway 10 due to Java 17 contraints but is something on my todo list (Upgrade to Flyway 10 by jonesbusy · Pull Request #9 · jenkinsci/flyway-api-plugin · GitHub)