Convert common DDL types and syntax to Spanner equivalents for better compatibility

[O1O1O1O]

I tried to use the driver with Scala Play Framework but to support the "evolutions" (aka "migrations") feature Play creates tables with DDL using types incompatible with Spanner's eg.

      create table ${schema}play_evolutions (
          id int not null primary key,
          hash varchar(255) not null,
          applied_at timestamp not null,
          apply_script text,
          revert_script text,
          state varchar(255),
          last_problem text
      )

from the source in Github

Although Play specifically could be updated to support Spanner DDL directly (as for Oracle, SQL Server etc.) it occurs to me that if there was a way to map types like int to int64, varchar(x) to string(x), text to string, and collate primary key attributes into a primary key (...) declaration then we'd get wider compatibility beyond Play.

This may not be in the spirit of what JDBC drivers are supposed to do but if possible perhaps it could be turned on only via xxxSQLCompatability=true driver flag?

Alternatively I can create a PR for Play Framework to add Spanner support but given you have the only DDL supporting JDBC driver so far they may not want to do that being such a niche case.

[O1O1O1O]

I've since found that Play has a way to customize how evolutions work. This issue comment gives the basic idea of how a customized implementation of the EvolutionsApi interface could be used:

https://github.com/playframework/playframework/issues/6919#issuecomment-276018274

So doing that could be a workaround for any Scala Play-ers who find their way here. Combined with your Spanner emulator that could give a pretty complete support by Play - note Play also uses a lock table to guard against concurrent evolutions and of course that also has problematic DDL syntax. You can disable by using:

play.evolutions.db.<spanner_profile_name>.useLocks = false

in your application.conf file.

If I get a custom EvolutionsApi supporting Spanner via this driver working I'll report back here with a link. But I still think a compatibility mode as originally suggested could be very useful to framework users and porters of legacy code.

PS. Thanks for this great driver - working great so far!

[olavloite]

@O1O1O1O Interesting idea. I will certainly have a look whether it would be feasible to add such a feature. Although it is, as you say, normally not the responsibility of a JDBC driver to do such conversion, one of the reasons that I created the driver was to enable existing frameworks and applications to be integrated with Spanner, and this feature would fit within that goal.