Spanner recently added change streams (https://cloud.google.com/spanner/docs/change-streams/manage). Right now using the library on our schema fails because of unknown DDL (essentially because of CREATE CHANGE STREAM GlobalChangeStream FOR All;). Even being able to just ignore it would be great.

Thanks for an awesome library!

The following fails when running hammer diff:

CREATE TABLE SomeTable (
  Id String(40),
  Data JSON,
  HasSomeValue BOOL AS (IF(JSON_QUERY(Data, '$.value') IS NOT NULL), true, false)) STORED
) PRIMARY KEY (Id);

with the error:

failed to parse ddl: schema.ddl:<line_num>: got "(" while expecting ","

When I replace JSON_QUERY with JSON_VALUE, though not semantically correct, the tool is able to produce a correct diff.

It would be great if hammer app could include an additional command of something like hammer -v or hammer version or something on the similar lines, to be able identify which version the executable is on, if there already exists one such command or option, please feel free to close this ticket but do give reference for the same, since it gets tricky once the executable is moved out of it source directory.

When I applied diff on my dev db, the generated sql diff did not execute correctly to my prod db.
When I applied it I got Error: rpc error: code = NotFound desc = Table not found: Images.

Example failure:

CREATE TABLE Posts (
  PostId STRING(2048) NOT NULL,
  Text STRING(MAX),
  FOREIGN KEY (ImageId) REFERENCES Images (ImageId),
) PRIMARY KEY(PostId);

CREATE TABLE Images (
  ImageId STRING(2048) NOT NULL,
  URL STRING(2048),
) PRIMARY KEY(ImageId);

Order matters here due to foreign key so should be:

CREATE TABLE Images (
  ImageId STRING(2048) NOT NULL,
  URL STRING(16),
) PRIMARY KEY(ImageId);

CREATE TABLE Posts (
  PostId STRING(2048) NOT NULL,
  PairSymbol STRING(16),
  FOREIGN KEY (ImageId) REFERENCES Images (ImageId),
) PRIMARY KEY(PostId);

I verified by switching manually that this solved the problem. I think we just need some clever ordering in the library to handle this case.

schema.sql:1: unknown DDL statement

https://cloud.google.com/spanner/docs/reference/standard-sql/dml-syntax#insert-ignore

Currently hammer ignores foreign keys so that e.g the following detects no diff.

from:

CREATE TABLE T2 (
  ID INT64,
);

to:

CREATE TABLE T2 (
  ID INT64,
  CONSTRAINT FK_T2_ID FOREIGN KEY (ID) REFERENCES T1 (ID)
);

expected:

ALTER TABLE T2 ADD CONSTRAINT FK_T2_ID FOREIGN KEY (ID) REFERENCES T1 (ID)

When adding a spanner view like

CREATE VIEW SomeView
SQL SECURITY INVOKER
AS SELECT
  Table1.Id,
  Table2.SomeColumn
FROM Table1 LEFT JOIN Table2
ON Table1.Id = Table2.Table1Id;

hammer diff fails with the following error:

Error: unexpected ddl statement: &spansql.CreateView{Name:"SomeView", OrReplace:false, Query:spansql.Query{Select:spansql.Select{Distinct:false, List:[]spansql.Expr{spansql.PathExp{"Table1", "Id"}, spansql.PathExp{"Table2", "SomeColumn"}}, From:[]spansql.SelectFrom{spansql.SelectFromJoin{Type:3, LHS:spansql.SelectFromTable{Table:"Table1", Alias:"", Hints:map[string]string(nil)}, RHS:spansql.SelectFromTable{Table:"Table2", Alias:"", Hints:map[string]string(nil)}, On:spansql.ComparisonOp{Op:4, LHS:spansql.PathExp{"Table1", "Id"}, RHS:spansql.PathExp{"Table2", "Table1Id"}, RHS2:spansql.Expr(nil)}, Using:[]spansql.ID(nil), Hints:map[string]string(nil)}}, Where:spansql.BoolExpr(nil), GroupBy:[]spansql.Expr(nil), TableSamples:[]*spansql.TableSample(nil), ListAliases:[]spansql.ID(nil)}, Order:[]spansql.Order(nil), Limit:spansql.LiteralOrParam(nil), Offset:spansql.LiteralOrParam(nil)}, Position:spansql.Position{Line:153, Offset:7819}}

Is there/will there be support for diffing and applying views?

Hello,
On hammer apply or hammer diff, while creating a table with a generated column using a timestamp function, such as:

CREATE TABLE examples (
  inserted_at TIMESTAMP  NOT NULL,
  examples_id STRING(36) NOT NULL,
  examples_timestamp TIMESTAMP  AS(TIMESTAMP_ADD(inserted_at, INTERVAL 1 DAY)) STORED
) PRIMARY KEY(examples_id);

produces the following error:
Error: spanner://projects/test-project/instances/test-instance/databases/example_dev failed to parse ddl: spanner://projects/test-project/instances/test-instance/databases/example_dev:4: got "1", want ")" or ","

Adding a NOT NULL Numeric type to the schema and applying it with Hammer results in an error.

CREATE TABLE Test (
   ID STRING(MAX) NOT NULL,
+   A NUMERIC NOT NULL,
) PRIMARY KEY(ID);

$ hammer apply spanner://projects/xxx/instances/yyy/databases/zzz schema.sql
Error: rpc error: code = Unimplemented desc = Cannot add NOT NULL column Test.A to existing table Test.
exit status 1

$ hammer diff spanner://projects/xxx/instances/yyy/databases/zzz ./ddl/schema.sql
ALTER TABLE Test ADD COLUMN A NUMERIC NOT NULL;
ALTER TABLE Test ALTER COLUMN A DROP DEFAULT;

Hammer does not support default values for the Numeric type.

I am expecting the following results.

$ hammer diff spanner://projects/xxx/instances/yyy/databases/zzz ./ddl/schema.sql
ALTER TABLE Test ADD COLUMN A NUMERIC NOT NULL DEFAULT (0);
ALTER TABLE Test ALTER COLUMN A DROP DEFAULT;

Spanner does not distinguish between uppercase and lowercase in table and column names. However, in the case of hammer, it does, which leads to the generation of ALTER TABLE statements for renaming when differences are detected.
I would like to avoid outputting differences in this case.

When applying an ALTER TABLE statement that changes only the case of letters, the following error occurs.

Column names in table SampleTable differ only in case: SampleColumn, SAMPLECOLUMN.
Failed to execute migration, Column names in table SampleTable differ only in case: SampleColumn, SAMPLECOLUMN.

Hi!
Would it be feasible to include windows binaries in addition to the linux and osx binaries for new releases? Thanks!

Hi there,
We want to add a generated column to our schema, but hammer doesn't like the syntax. The DDL is like:
expiry TIMESTAMP AS (IF(status IS NULL, updated_at, '10000-01-01 00:00:00 UTC')) STORED

Thanks.

James

It is great for me if hammer has an option to ignore ALTER DATABASE statement while diff, apply, etc.

Backgrounds:

• The go spanner library (currently hammer is using it) has 2 critical issues involving the ALTER DATABASE statement. These issues prevent me to use hammer completely.
  • googleapis/google-cloud-go#5970
  • googleapis/google-cloud-go#5972
• ALTER DATABASE statement needs "database name" to run. it means an sql file that is synced to a database named "db1" cannot be directly used in a database named "db2", because the file has the DB name in SQL statement, like:

  ALTER DATABASE db1 SET OPTIONS (version_retention_period='7d');

  It will be great for me to ignore the "ALTER DATABASE" statement to share/apply the same sql file.

The DEFAULT keyword is supported now.
https://cloud.google.com/spanner/docs/release-notes?hl=ja#April_14_2022

And it's supported in cloud.google.com/go/spanner v1.32.0.
https://github.com/googleapis/google-cloud-go/releases/tag/spanner/v1.32.0

Solution

• update cloud.google.com/go/spanner to v1.32.0
• use a default value instead of an UPDATE statement when NOT NULL column is added

Hi, hammer newbie here. I was hoping to try it out against the Google Spanner emulator but I'm not sure how to do that. I tried various env vars and command line parameters but nothing seems to work?

Thanks!

I would like to use the output of hammer diff for my migration tool, but those queries are not terminated with a specific character (e.g. semicolon), so I can't separate multiple queries.
It would be great if each query could be terminated with a semicolon!

Thanks.

spanner v1.2.0 or later spansql supports column options, so I want to upgrade the version of spanner I'm using in hammer.

Hello!

This isn't quite a bug report or feature request, but could be either. I had the following table:

table Foos {
      BarID INT64 NOT NULL,

      CONSTRAINT FK_FoosBar FOREIGN KEY (BarID) REFERENCES Bars (ID),
}

I then wanted to remove the NOT NULL, i.e,

table Foo {
      BarID INT64,

      CONSTRAINT FK_FooBar FOREIGN KEY (BarID) REFERENCES Bars (ID),
}

And the diff said:

ALTER TABLE Foos ALTER COLUMN BarID INT64

However, this isn't a legal change! Since BarID is part of a foreign key it doesn't apply. Instead, the correct change is to drop the foreign key, change the table, and then readd the foreign key.

The question is: should hammer (a) refuse the diff, (b) warn about it, (c) generate a diff that does the correct drop/regen dance, or (d) ignore this and leave it up to the user to know?

Not saying either is wrong, or what I'd expect, but I think (d) is the least helpful (and works bad with "automation").