Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Interleaved tables are generated as normal Foreign Keys in snapshot #78

Closed
marcindyjas opened this issue Apr 2, 2021 · 3 comments
Closed

Interleaved tables are generated as normal Foreign Keys in snapshot #78

marcindyjas opened this issue Apr 2, 2021 · 3 comments
Labels
bug Something isn't working

Comments

@marcindyjas
Copy link

Descripton:

constraintName is not generated in changelog for addForeignKeyConstraint operation.

Steps to reproduce:

Use for example DDL as below to create new table on Spanner:

CREATE TABLE TEST (
TEST_LOC STRING(8) NOT NULL,
) PRIMARY KEY(TEST_LOC);

CREATE TABLE TEST_CONTNT (
TEST_LOC STRING(8) NOT NULL,
) PRIMARY KEY(TEST_LOC),
INTERLEAVE IN PARENT TEST ON DELETE CASCADE;

Then run:
liquibase --url="jdbc:cloudspanner:/projects/<project>/instances/<instance>/databases/<database>" --changeLogFile=changeLog.yml generateChangeLog

ChangeLog contains addForeignKeyConstraint without "constraintName" - as below:

- addForeignKeyConstraint:
    baseColumnNames: TEST_LOC
    baseTableName: TEST_CONTNT
    deferrable: false
    initiallyDeferred: false
    onDelete: CASCADE
    onUpdate: RESTRICT
    referencedColumnNames: TEST_LOC
    referencedTableName: TEST
@olavloite olavloite added the bug Something isn't working label Apr 2, 2021
@olavloite
Copy link
Collaborator

The INTERLEAVE IN PARENT construct is not a normal FOREIGN KEY constraint. Cloud Spanner supports two types of relationships between tables:

  1. Interleaved tables: The data of the child table is stored physically together with the parent table. This relationship can only be defined when the child table is created, it cannot be added or removed at a later moment.
  2. Foreign keys: This is the normal foreign key relationship that most relational databases support.

The example in this issue uses INTERLEAVED IN PARENT which is not really supported by Liquibase. There's a code example in the repository on how to create such tables using Liquibase. I'll have a look and see if it would be possible to get Liquibase to generate that when generating a change log, but I have my doubts it will be possible.

@olavloite olavloite mentioned this issue Apr 2, 2021
Merged
@olavloite
Copy link
Collaborator

I've been looking into whether it would be feasible to generate the INTERLEAVE IN PARENT in the snapshots, but that is not possible, because:

  1. There is no suitable element that can be included in the createTable change type for the child table. The parent/child relationship must be specified when the child table is created, so it cannot be a separate change type. The trick with modifySql that we use when a change type is created manually, is not applicable to snapshots, as these cannot contain anything like modifySql.
  2. In order to support parent/child relationships in snapshots, we would also need to ensure that parent tables are listed in the snapshot before the child table. Liquibase currently lists table in a snapshot in alphabetical order, and then adds foreign key constraints as separate change sets after the tables. Changing the ordering of tables in a snapshot in Liquibase is not possible in an extension, except by replacing the entire snapshot feature of Liquibase by a custom implementation.

@olavloite olavloite changed the title constraintName is required for addForeignKeyConstraint on cloudspanner Interleaved tables are generated as normal Foreign Keys in snapshot Apr 7, 2021
@olavloite
Copy link
Collaborator

@marcindyjas I'll close this issue and open a new one for the remaining issue: Interleaved table relationships are not included in a generated snapshot.

@olavloite olavloite mentioned this issue Apr 7, 2021
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@olavloite @marcindyjas