Add limited support for MySQL and Oracle

Description

Summary

Your administrator will have to switch away from GenericDDLDialect to either MySQLDDLDialect or OracleDDLDialect.

Context

Our customers keep using MySQL and Oracle with Play SQL, knowing it has always been officially unsupported, because the product is free and awesome.

In Play SQL 2.8, we noticed those customers would we totally blocked if we didn't introduce a specific dialect for their databases. So we're introducing the dialects, but they remain unsupported. Here's the current list of dialects:

  • PostgreSQL (Read-write, full features, recommended)

  • Hsql (Read-write, for evaluation purposes only)

  • MySQL (Unsupported)

  • Oracle (Unsupported)

  • Generic (Unsupported)

Why is it "unsupported"?

There are differences between databases:

  • The double-quote " is used in Postgres to escape case-sensitive column names, whereas Oracle uses the back-tick `. MySQL is adverse to double-quotes since it considers them as a literal string value.

  • The keyword AS is mandatory in PostgreSQL, optional in HSQL, forbidden in Oracle and MySQL.

  • The keywords LIMIT and OFFSET are native to HSQL, PostgreSQL and MySQL. It is forbidden in Oracle, which relies on a WHERE clause with a ROWNUM condition; Given it's a different query structure, we can't generate queries automatically for Oracle.

  • Access to the metadata of columns/tables is very different across the 4 providers.

Each time we add a supported platform, the testing and support workload double: We support 4 major versions of Confluence, read and write modes and 3 browsers. Therefore we've chosen to pin one variable: Albeit controversial*, we've chosen the DBMS with the most complete feature set, with a enterprise-awarded reliability and performance, and open source, and this is PostgreSQL.

  • It's controversial in the industry to require one DBMS.

What kind of support can customers expect?

Play SQL executes queries for Oracle and MySQL in "best effort" mode. It's read-only. There's no auto-complete or list of tables/columns. Sorting isn't native in MySQL (but you can write an ORDER BY statement which will do the right thing).

Negative reviews on the Marketplace from users of Oracle and MySQL will be automatically flagged as inappropriate.

What is not guaranteed

  • We don't guarantee we'll keep support for those DBMS in future releases.

  • We don't guarantee that features will work in MySQL or Oracle.

What about changing the dialects yourself?

We don't allow it because it requires uncompiling the playsql-base-plugin, finding the files dialect-generic.xml, dialect-oracle.xml and dialect-mysql.xml and understanding how they work. In many cases it would also be necessary to modify files such as com.playsql.jdbc.dialect.MySQLDDLDialect.

We hope, of course, to move those files in a separate plugin which would be easier to modify, but it's not a promise.

Environment

None

Observations

None

Assignee

Adrien Ragot (Play SQL / Requirement Yogi)

Reporter

Adrien Ragot (Old account)

Labels

None

Participants

None

Components

Fix versions

Priority

Configure