search

codeforkjeff / dbt-sqlite

A SQLite adapter plugin for dbt (data build tool)
Apache License 2.0
78 stars 13 forks source link

Implement DROP CASCADE behavior #1

Closed codeforkjeff closed 3 years ago

codeforkjeff commented 4 years ago

This PR tries to implement DROP CASCADE behavior to avoid using the legacy_alter_table hack/workaround.

Start with 3 models whose materializations are table -> view -> table.

The code for cascading drop in this branch needs a ton of work but is functional, if your SQL is super simple. But dbt doesn't seem aware of the dropped view that occurs when the 1st table gets dropped, so it tries to rename it to a backup, and fails. Oddly, this failure happens every other invocation of "dbt run".

To add to the complication, I'm not sure I need the call to get_live_relation_type in rename_relation, but if I take that out and use from_relation.type instead, I get the same exact result as above.

My guess is that something is broken in this adapter, whereby dbt's DAG isn't being updated after drop_relation? But I'm not sure where to look.

codeforkjeff commented 4 years ago

This has been superceded by PR #2 which simplifies materializations instead. There are too many limitations in sqlite to make it work without a ton of effort, though it's possible. Leaving this PR open for now for notes towards that goal.

codeforkjeff commented 3 years ago

Closing this out, I don't think this is worth it.