Make delete recursive

[ssweber]

An issue for tables with grandchildren. Currently can be orphaned if parent is deleted. They will be in database, but hidden.

[PySimpleSQL]

Yeah, that's on my planned list, and one of the reasons I'm working on changing requery parameters to update_cascade, so we can start capturing ON DELETE CASCADE too

…

On Thu, Feb 23, 2023, 1:59 PM ssweber ***@***.***> wrote: An issue for tables with grandchildren. Currently can be orphaned if parent is deleted. They will be in database, but hidden. — Reply to this email directly, view it on GitHub <#89>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQX2REXHLRJBIKVWOJULVRLWY6XRFANCNFSM6AAAAAAVGAHJIM> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[ssweber]

Ah, yes that would be better! So we wouldn’t do delete just on CACADE, only for ON DELETE

[ssweber]

Was looking into this again… read https://stackoverflow.com/questions/29420910/using-foreign-keys-in-sqlite3-for-python

Do MySQL, Postgres work similarly?

connection = sqlite3.connect(DB_FILE)
connection.execute('PRAGMA foreign_keys = ON')
cursor = connection.cursor()

where we could just delete the parent and let the database handle the cascade if user adds “ON DELETE CASCADE” to their setup?

[ssweber]

Or did you want it to work even if they define the update_cascade, delete_cascade relationship outside of the database

[ssweber]

Btw, I tested SQLite enforcing foreign keys ( #134 ) and it worked well.

[ssweber]

You can enable this in user-code by calling:

driver = ss.Sqlite(":memory:", sql_commands=sql)  # Create a new database connection
frm = ss.Form(driver, bind_window=window)  # <=== Here is the magic!
driver.con.execute('PRAGMA foreign_keys = ON') ## Enable foreign-key constraints in Sqlite.

see my example

[ssweber]

Do you mind if I work on this? I have an idea I want to share with you when I get it a little firmed up. #137

[PySimpleSQL]

I don't mind, but keep in mind that the best path forward is getting the ON DELETE CASCADE from the database and letting the schema tell how it should be done. This gives the database maintainer full control over how data is deleted

…

On Sun, Mar 12, 2023, 10:47 AM ssweber ***@***.***> wrote: Do you mind if I work on this? I have an idea I want to share with you when I get it a little firmed up. #137 <#137> — Reply to this email directly, view it on GitHub <#89 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQX2RETYI45B3YHIDY3E45LW3XVZ7ANCNFSM6AAAAAAVGAHJIM> . You are receiving this because you commented.Message ID: ***@***.***>

[ssweber]

Definitely. Determining ON DELETE CACADE from SQLite was easy enough, will need some help for MySQL/Postgres. Hope to have something for you to look at by Monday or Tuesday.

What are your thoughts on recursion depth? I’m sorta wondering if we should only support 2-step deletes (self/child/grandchildren) and then if someone is deleting more that have more steps in the cascade, raise some kind of warning if they don’t have foreign key constraints turned On.

[ssweber]

The accepted answer to this Stack exchange was how I was planning on doing the grandchild records, and so I guess I could do another inner join to do great grandchildren, but I don’t know how or if we want to keep scaling that up. At some point you’d probably want to set a recursion limit 😅

[PySimpleSQL]

Sorry, I'm out of town for a couple of days. I will touch base ASAP

…

On Sun, Mar 12, 2023, 7:21 PM ssweber ***@***.***> wrote: The accepted answer to this Stack exchange <https://stackoverflow.com/questions/34864738/how-can-i-delete-a-record-child-records-and-grandchildren-records> was how I was planning on doing the grandchild records, and so I guess I could do another inner join to do great grandchildren, but I don’t know how or if we want to keep scaling that up. At some point you’d probably want to set a recursion limit 😅 — Reply to this email directly, view it on GitHub <#89 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQX2RERMB3EIGI7OSO6JHULW3ZR7LANCNFSM6AAAAAAVGAHJIM> . You are receiving this because you commented.Message ID: ***@***.***>

[ssweber]

No rush! I’m going to use a DELETE_RECURSION_LIMIT: int = and try to setup logic as a loop.

Then we can setup a sensible limit and let users change it if need be

[ssweber]

#163