Skip to content
This repository was archived by the owner on Jun 8, 2026. It is now read-only.

feat: foreign key on delete cascade action testing and samples #910

Merged
asthamohta merged 14 commits into from
Jul 26, 2023
Merged

feat: foreign key on delete cascade action testing and samples #910

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
14 commits
Select commit Hold shift + click to select a range
7b7cca9
feat: fkdca
asthamohta Mar 13, 2023
069056a
lint
asthamohta Mar 13, 2023
bab2faa
lint
asthamohta Mar 13, 2023
1b4dd42
Merge branch 'main' into fkdc
asthamohta Jul 3, 2023
51e21e9
Update samples/samples/snippets.py
asthamohta Jul 13, 2023
b4b7e6e
Update samples/samples/snippets.py
asthamohta Jul 13, 2023
e33e7d9
Merge branch 'main' into fkdc
asthamohta Jul 13, 2023
c06d3b6
Merge branch 'main' into fkdc
asthamohta Jul 18, 2023
31d63c3
changed
asthamohta Jul 19, 2023
f1c2e3c
changes
asthamohta Jul 20, 2023
9487290
Merge branch 'main' into fkdc
asthamohta Jul 24, 2023
3b590e4
Merge branch 'main' into fkdc
asthamohta Jul 25, 2023
a7bbcc5
changes
asthamohta Jul 25, 2023
ce31137
Merge branch 'fkdc' of github.com:asthamohta/python-spanner into fkdc
asthamohta Jul 25, 2023
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
99 changes: 99 additions & 0 deletions samples/samples/snippets.py
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -2449,6 +2449,105 @@ def enable_fine_grained_access(
# [END spanner_enable_fine_grained_access]


# [START spanner_create_table_with_foreign_key_delete_cascade]
def create_table_with_foreign_key_delete_cascade(instance_id, database_id):
"""Creates a table with foreign key delete cascade action"""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

operation = database.update_ddl(
[
"""CREATE TABLE Customers (
CustomerId INT64 NOT NULL,
CustomerName STRING(62) NOT NULL,
) PRIMARY KEY (CustomerId)
""",
"""
CREATE TABLE ShoppingCarts (
CartId INT64 NOT NULL,
CustomerId INT64 NOT NULL,
CustomerName STRING(62) NOT NULL,
CONSTRAINT FKShoppingCartsCustomerId FOREIGN KEY (CustomerId)
REFERENCES Customers (CustomerId) ON DELETE CASCADE
) PRIMARY KEY (CartId)
"""
]
)

print("Waiting for operation to complete...")
operation.result(OPERATION_TIMEOUT_SECONDS)

print(
"""Created Customers and ShoppingCarts table with FKShoppingCartsCustomerId
foreign key constraint on database {} on instance {}""".format(
database_id, instance_id
)
)


# [END spanner_create_table_with_foreign_key_delete_cascade]


# [START spanner_alter_table_with_foreign_key_delete_cascade]
def alter_table_with_foreign_key_delete_cascade(instance_id, database_id):
"""Alters a table with foreign key delete cascade action"""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

operation = database.update_ddl(
[
"""ALTER TABLE ShoppingCarts
ADD CONSTRAINT FKShoppingCartsCustomerName
FOREIGN KEY (CustomerName)
REFERENCES Customers(CustomerName)
ON DELETE CASCADE"""
]
)

print("Waiting for operation to complete...")
operation.result(OPERATION_TIMEOUT_SECONDS)

print(
"""Altered ShoppingCarts table with FKShoppingCartsCustomerName
foreign key constraint on database {} on instance {}""".format(
database_id, instance_id
)
)


# [END spanner_alter_table_with_foreign_key_delete_cascade]


# [START spanner_drop_foreign_key_constraint_delete_cascade]
def drop_foreign_key_constraint_delete_cascade(instance_id, database_id):
"""Alter table to drop foreign key delete cascade action"""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

operation = database.update_ddl(
[
"""ALTER TABLE ShoppingCarts
DROP CONSTRAINT FKShoppingCartsCustomerName"""
]
)

print("Waiting for operation to complete...")
operation.result(OPERATION_TIMEOUT_SECONDS)

print(
"""Altered ShoppingCarts table to drop FKShoppingCartsCustomerName
foreign key constraint on database {} on instance {}""".format(
database_id, instance_id
)
)


# [END spanner_drop_foreign_key_constraint_delete_cascade]


if __name__ == "__main__": # noqa: C901
parser = argparse.ArgumentParser(
description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter
Expand Down
22 changes: 22 additions & 0 deletions samples/samples/snippets_test.py
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -794,3 +794,25 @@ def test_list_database_roles(capsys, instance_id, sample_database):
snippets.list_database_roles(instance_id, sample_database.database_id)
out, _ = capsys.readouterr()
assert "new_parent" in out


@pytest.mark.dependency(name="create_table_with_foreign_key_delete_cascade")
def test_create_table_with_foreign_key_delete_cascade(capsys, instance_id, sample_database):
snippets.create_table_with_foreign_key_delete_cascade(instance_id, sample_database.database_id)
out, _ = capsys.readouterr()
assert "Created Customers and ShoppingCarts table with FKShoppingCartsCustomerId" in out

@anuragsrivstv anuragsrivstv May 4, 2023

Copy link
Copy Markdown

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

May be instead of checking the output message we should verify that the table was actually created/ altered by the sample or not ?

@asthamohta asthamohta Jul 13, 2023

Copy link
Copy Markdown
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

For IT, yes but for samples we just need the to check if an error is not thrown



@pytest.mark.dependency(name="alter_table_with_foreign_key_delete_cascade",
depends=["create_table_with_foreign_key_delete_cascade"])

@anuragsrivstv anuragsrivstv Jun 2, 2023 •
edited
Loading

Copy link
Copy Markdown

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Hi, As we are adding Foreign Key Delete Cascade constraint , this should Not depend on sample which has already added the constraints while creating the tables, isn't it ?

@asthamohta asthamohta Jul 13, 2023

Copy link
Copy Markdown
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

It is common in samples to have this dependency to reduce the time to run these

def test_alter_table_with_foreign_key_delete_cascade(capsys, instance_id, sample_database):
snippets.alter_table_with_foreign_key_delete_cascade(instance_id, sample_database.database_id)
out, _ = capsys.readouterr()
assert "Altered ShoppingCarts table with FKShoppingCartsCustomerName" in out


@pytest.mark.dependency(depends=["alter_table_with_foreign_key_delete_cascade"])
def test_drop_foreign_key_contraint_delete_cascade(capsys, instance_id, sample_database):
snippets.drop_foreign_key_constraint_delete_cascade(instance_id, sample_database.database_id)
out, _ = capsys.readouterr()
assert "Altered ShoppingCarts table to drop FKShoppingCartsCustomerName" in out
26 changes: 26 additions & 0 deletions tests/_fixtures.py
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -68,6 +68,19 @@
email STRING(MAX),
deleted BOOL NOT NULL )
PRIMARY KEY(id, commit_ts DESC);

CREATE TABLE Customers (
Comment thread
surbhigarg92 marked this conversation as resolved.
CustomerId INT64 NOT NULL,
CustomerName STRING(62) NOT NULL,
) PRIMARY KEY (CustomerId);

CREATE TABLE ShoppingCarts (
CartId INT64 NOT NULL,
CustomerId INT64 NOT NULL,
CustomerName STRING(62) NOT NULL,
CONSTRAINT FKShoppingCartsCustomerId FOREIGN KEY (CustomerId)
REFERENCES Customers (CustomerId) ON DELETE CASCADE
) PRIMARY KEY (CartId);
"""

EMULATOR_DDL = """\
Expand Down Expand Up @@ -157,6 +170,19 @@
name VARCHAR(16),
PRIMARY KEY (id));
CREATE INDEX name ON contacts(first_name, last_name);
CREATE TABLE Customers (
CustomerId BIGINT,
CustomerName VARCHAR(62) NOT NULL,
PRIMARY KEY (CustomerId));

CREATE TABLE ShoppingCarts (
CartId BIGINT,
CustomerId BIGINT NOT NULL,
CustomerName VARCHAR(62) NOT NULL,
CONSTRAINT "FKShoppingCartsCustomerId" FOREIGN KEY (CustomerId)
REFERENCES Customers (CustomerId) ON DELETE CASCADE,
PRIMARY KEY (CartId)
);
"""

DDL_STATEMENTS = [stmt.strip() for stmt in DDL.split(";") if stmt.strip()]
Expand Down
134 changes: 133 additions & 1 deletion tests/system/test_database_api.py
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -21,13 +21,16 @@
from google.iam.v1 import policy_pb2
from google.cloud import spanner_v1
from google.cloud.spanner_v1.pool import FixedSizePool, PingingPool
from google.cloud.spanner_admin_database_v1 import DatabaseDialect
from google.type import expr_pb2
from . import _helpers
from . import _sample_data
from google.cloud.spanner_admin_database_v1 import DatabaseDialect


DBAPI_OPERATION_TIMEOUT = 240 # seconds
FKADC_CUSTOMERS_COLUMNS = ("CustomerId", "CustomerName")
FKADC_SHOPPING_CARTS_COLUMNS = ("CartId", "CustomerId", "CustomerName")
ALL_KEYSET = spanner_v1.KeySet(all_=True)


@pytest.fixture(scope="module")
Expand Down Expand Up @@ -572,6 +575,135 @@ def _unit_of_work(transaction, name):
assert len(rows) == 2


def test_insertion_in_referencing_table_fkadc(not_emulator, shared_database):
with shared_database.batch() as batch:
batch.insert(
table="Customers",
columns=FKADC_CUSTOMERS_COLUMNS,
values=[
(1, "Marc"),
(2, "Catalina"),
],
)

with shared_database.batch() as batch:
batch.insert(
table="ShoppingCarts",
columns=FKADC_SHOPPING_CARTS_COLUMNS,
values=[
(1, 1, "Marc"),
],
)

with shared_database.snapshot() as snapshot:
rows = list(
snapshot.read(
"ShoppingCarts", ("CartId", "CustomerId", "CustomerName"), ALL_KEYSET
)
)

assert len(rows) == 1


def test_insertion_in_referencing_table_error_fkadc(not_emulator, shared_database):
with pytest.raises(exceptions.FailedPrecondition):
with shared_database.batch() as batch:
batch.insert(
table="ShoppingCarts",
columns=FKADC_SHOPPING_CARTS_COLUMNS,
values=[
(4, 4, "Naina"),
],
)


def test_insertion_then_deletion_in_referenced_table_fkadc(
not_emulator, shared_database
):
with shared_database.batch() as batch:
batch.insert(
table="Customers",
columns=FKADC_CUSTOMERS_COLUMNS,
values=[
(3, "Sara"),
],
)

with shared_database.batch() as batch:
batch.insert(
table="ShoppingCarts",
columns=FKADC_SHOPPING_CARTS_COLUMNS,
values=[
(3, 3, "Sara"),
],
)

with shared_database.snapshot() as snapshot:
rows = list(snapshot.read("ShoppingCarts", ["CartId"], ALL_KEYSET))

assert [3] in rows

with shared_database.batch() as batch:
batch.delete(table="Customers", keyset=spanner_v1.KeySet(keys=[[3]]))

with shared_database.snapshot() as snapshot:
rows = list(snapshot.read("ShoppingCarts", ["CartId"], ALL_KEYSET))

assert [3] not in rows


def test_insert_then_delete_referenced_key_error_fkadc(not_emulator, shared_database):
with pytest.raises(exceptions.FailedPrecondition):
with shared_database.batch() as batch:
batch.insert(
table="Customers",
columns=FKADC_CUSTOMERS_COLUMNS,
values=[
(3, "Sara"),
],
)
batch.delete(table="Customers", keyset=spanner_v1.KeySet(keys=[[3]]))


def test_insert_referencing_key_then_delete_referenced_key_error_fkadc(
not_emulator, shared_database
):
with shared_database.batch() as batch:
batch.insert(
table="Customers",
columns=FKADC_CUSTOMERS_COLUMNS,
values=[
(4, "Huda"),
],
)

with pytest.raises(exceptions.FailedPrecondition):
with shared_database.batch() as batch:
batch.insert(
table="ShoppingCarts",
columns=FKADC_SHOPPING_CARTS_COLUMNS,
values=[
(4, 4, "Huda"),
],
)
batch.delete(table="Customers", keyset=spanner_v1.KeySet(keys=[[4]]))


def test_information_schema_referential_constraints_fkadc(
not_emulator, shared_database
):
with shared_database.snapshot() as snapshot:
rows = list(
snapshot.execute_sql(
"SELECT DELETE_RULE "
"FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS "
"WHERE CONSTRAINT_NAME = 'FKShoppingCartsCustomerId'"
)
)

assert any("CASCADE" in stmt for stmt in rows)


def test_update_database_success(
not_emulator, shared_database, shared_instance, database_operation_timeout
):
Expand Down