Support For Azure SQL In Apicurio

[Surbhi27946]

We are deploying Apicurio on our internal platforms which supports only Azure SQL.
I found this documentation to switch to either Myswl or PostGres : https://apicurio-studio.readme.io/docs/switching-to-mysql-or-postgresql

Is there any documentation available to use Azure SQL as well with Apicurio Studio?
With the above documentation, I am getting errors with Azure SQL :

21:55:53,991 ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread) WFLYCTL0013: Operation ("add") failed - address: ([
    ("subsystem" => "datasources"),
    ("data-source" => "ApicurioDS")
]) - failure description: {
    "WFLYCTL0412: Required services that are not installed:" => ["jboss.jdbc-driver.com_microsoft_sqlserver_jdbc_SQLServerDriver"],
    "WFLYCTL0180: Services with missing/unavailable dependencies" => [
        "jboss.driver-demander.java:jboss/datasources/ApicurioDS is missing [jboss.jdbc-driver.com_microsoft_sqlserver_jdbc_SQLServerDriver]",
        "org.wildfly.data-source.ApicurioDS is missing [jboss.jdbc-driver.com_microsoft_sqlserver_jdbc_SQLServerDriver]"
    ]
}
21:55:53,992 ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread) WFLYCTL0013: Operation ("add") failed - address: ([
    ("subsystem" => "datasources"),
    ("data-source" => "ApicurioDS")
]) - failure description: {
    "WFLYCTL0412: Required services that are not installed:" => [
        "jboss.jdbc-driver.com_microsoft_sqlserver_jdbc_SQLServerDriver",
        "jboss.jdbc-driver.com_microsoft_sqlserver_jdbc_SQLServerDriver"
    ],
    "WFLYCTL0180: Services with missing/unavailable dependencies" => [
        "jboss.driver-demander.java:jboss/datasources/ApicurioDS is missing [jboss.jdbc-driver.com_microsoft_sqlserver_jdbc_SQLServerDriver]",
        "org.wildfly.data-source.ApicurioDS is missing [jboss.jdbc-driver.com_microsoft_sqlserver_jdbc_SQLServerDriver]",
        "org.wildfly.data-source.ApicurioDS is missing [jboss.jdbc-driver.com_microsoft_sqlserver_jdbc_SQLServerDriver]"
    ]

[EricWittmann]

We do not have support for Azure SQL, which as I understand it is a cloud (managed) version of SQL Server. We have not implemented SQL Server support. However, the DB schema and SQL statements used by Studio are not very complex, nor are most of them DB-specific. You can find the SQL statements common to all 3 DBs we support here:

https://github.com/Apicurio/apicurio-studio/blob/master/back-end/hub-core/src/main/java/io/apicurio/hub/core/storage/jdbc/CommonSqlStatements.java

And also (for example) SQL statements specific to MySQL:

https://github.com/Apicurio/apicurio-studio/blob/master/back-end/hub-core/src/main/java/io/apicurio/hub/core/storage/jdbc/MySQL5SqlStatements.java

As you can see there aren't very many DB-specific queries.

So if you were interested in contributing support for SQL Server, I would be happy to help guide you!

[Surbhi27946]

@EricWittmann
Surely I would love to do that. I have the SQL statements ready for the Azure SQL infact. Just need you guidance in going further.
If you want I can raise the PR for the current changes that I have made, that might help us in moving further in this.

[EricWittmann]

Sounds good - why don't we collaborate on this using the newly created feat/sql-server branch? You can find it here:

https://github.com/apicurio/apicurio-studio/tree/feat/sql-server

Please submit PRs against that and and we can work through it together. Please note that I don't have ready access to a SQL Server (or Azure SQL) instance to test the work. So that's on you. :)

[Surbhi27946]

Hi @EricWittmann
I have raised the PR for Azure sql support :
#1501

I would need your guidance. On starting the server for the first time, the DDL is working fine and tables are getting created fine.
But the second time when I am starting the server, the DDL is failing saying tables already exists, which is expected. Is there anyway or any flag with which we can control the DDL does not re-run?

This is the documentation change PR :
Apicurio/apicurio.github.io#15

[EricWittmann]

The call to isDatabaseInitialized() is supposed to check for the existence of the tables so that the DDL is not re-run. I see you have this:

    public String isDatabaseInitialized() {
        return "SELECT count(*) AS count FROM information_schema.tables WHERE table_name = 'api_designs' AND table_schema = 'guest'";
    }

Does that not work? This will need to be a SQLServer-specific query that essentially returns true (or 1) if the DDL has already been run.