Flyway DB Migrations

[Josh Mahonin <jm...@interset.com>]

Hi all,

As an FYI, I've got a pull request into Flyway (http://flywaydb.org/) for
Phoenix support:
https://github.com/flyway/flyway/pull/930

I don't know what everyone else is using for schema management, if anything
at all, but the preliminary support works well enough for Flyway's various
commands (migrate, clean, baseline, repair, etc.).

A lot of the functionality is using direct queries against SYSTEM.CATALOG,
so it's entirely possible I'm doing something incorrectly, but it passes a
fairly extensive suite of tests. If anyone else wants to take a quick look
at the code to double check everything looks in order, that would be great.

I've only tested against Phoenix 4.2.2, though it should be
straight-forward enough to support other versions as well.

Josh

Re: Flyway DB Migrations

[Josh Mahonin <jm...@interset.com>]

Hi James,

I had actually hoped to use the DatabaseMetaData originally, but I was
getting some interesting behaviour when using the 'getTables()' query when
'schemaPattern' was null. I'm not at my dev. machine to check for sure, but
I think I was getting back a list of all tables, rather than just those for
which 'TABLE_SCHEM' was null.

I wasn't quite sure if that was expected behaviour or not, and I was
knee-deep in an unknown codebase with Flyway, so I just forged ahead and
used SYSTEM.CATALOG directly. Taking a second look at it, I think it would
have worked if I had set schemaPattern to "" instead of passing in null,
although I'm not sure that's necessarily correct either. There's a bit of
an impedance mismatch between the notions of schemas in Flyway and Phoenix,
so I tried to find a reasonable balance with only a rote understanding of
each. A few more eyes on the problem would be very helpful!

I agree that long term using the DatabaseMetaData is the right way to go.
I'll continue to work on it when I can, but I'm hoping there might be some
interest on the mailing list to continue development on / fix any glaring
mistakes in there as well.

I'll take a look at that zookeeper exception, I'm certainly doing the most
naive thing possibly to get the in-memory cluster running so I quite likely
missed some important settings.

Thanks,

Josh

On Wed, Jan 14, 2015 at 8:35 PM, James Taylor <ja...@apache.org>
wrote:

> Wow, that's really awesome, Josh. Nice work. Can you let us know
> if/when it makes it in?
>
> One modification you may want to consider in a future revision to
> protect yourself in case the SYSTEM.CATALOG schema changes down the
> road: Use the DatabaseMetaData APIs[1] instead of querying the
> SYSTEM.CATALOG table directly. You can access this through the
> Connection (connection.getMetaData()).
>
> Just a wild guess, but for the zookeeper exception you mentioned that
> can be ignored, is it maybe related to not setting the
> hbase.master.info.port to -1?
>
> Thanks,
> James
>
> [1]
> http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html
>
> On Wed, Jan 14, 2015 at 1:41 PM, Josh Mahonin <jm...@interset.com>
> wrote:
> > Hi all,
> >
> > As an FYI, I've got a pull request into Flyway (http://flywaydb.org/)
> for
> > Phoenix support:
> > https://github.com/flyway/flyway/pull/930
> >
> > I don't know what everyone else is using for schema management, if
> anything
> > at all, but the preliminary support works well enough for Flyway's
> various
> > commands (migrate, clean, baseline, repair, etc.).
> >
> > A lot of the functionality is using direct queries against
> SYSTEM.CATALOG,
> > so it's entirely possible I'm doing something incorrectly, but it passes
> a
> > fairly extensive suite of tests. If anyone else wants to take a quick
> look
> > at the code to double check everything looks in order, that would be
> great.
> >
> > I've only tested against Phoenix 4.2.2, though it should be
> straight-forward
> > enough to support other versions as well.
> >
> > Josh
>

Re: Flyway DB Migrations

[James Taylor <ja...@apache.org>]

Wow, that's really awesome, Josh. Nice work. Can you let us know
if/when it makes it in?

One modification you may want to consider in a future revision to
protect yourself in case the SYSTEM.CATALOG schema changes down the
road: Use the DatabaseMetaData APIs[1] instead of querying the
SYSTEM.CATALOG table directly. You can access this through the
Connection (connection.getMetaData()).

Just a wild guess, but for the zookeeper exception you mentioned that
can be ignored, is it maybe related to not setting the
hbase.master.info.port to -1?

Thanks,
James

[1] http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html

On Wed, Jan 14, 2015 at 1:41 PM, Josh Mahonin <jm...@interset.com> wrote:
> Hi all,
>
> As an FYI, I've got a pull request into Flyway (http://flywaydb.org/) for
> Phoenix support:
> https://github.com/flyway/flyway/pull/930
>
> I don't know what everyone else is using for schema management, if anything
> at all, but the preliminary support works well enough for Flyway's various
> commands (migrate, clean, baseline, repair, etc.).
>
> A lot of the functionality is using direct queries against SYSTEM.CATALOG,
> so it's entirely possible I'm doing something incorrectly, but it passes a
> fairly extensive suite of tests. If anyone else wants to take a quick look
> at the code to double check everything looks in order, that would be great.
>
> I've only tested against Phoenix 4.2.2, though it should be straight-forward
> enough to support other versions as well.
>
> Josh