delete_cascade Relationship functions

[ssweber]

When you get back and resolve the current pulls, I have the delete_cascade stuff working for Relationships and the SQLite sqldriver.

However, I’d like to know what to call the current cascade functions/variables to clarify things. I’m proposing the idea of a “select_cascade”

What is a select_cascade?

A select_cascade is what you originally called requery_table, then update_cascade. Right now ON UPDATE CASCADE is used as convenient meta data for pysimplesql, when a different parent is selected, it then requeries, using the new parent pk as a filter in the where clause. But what if a user has or wants to use ON UPDATE CASCADE in the true database sense, updating child records to match the parent? They currently wouldn’t be able to use auto_map_relationships. Or if they did, they'd need to go back and set some update_cascade's to False

So my current plan is to:

1. Grab ON DELETE CASCADE from database, store in Relationships as delete_cascade
2. Keep update_cascade as is, as a variable in Relationships
3. Create two new Form init variables:
   a) update_cascade_as_select = True by default
   b) select_cascade_as_delete = ? Probably False, if you want 3.0 users to add delete cascade to their databases. But True would allow v2 compatibility.
4. Create two new @Property functions in Relationships, and move all logic over to them:

    @property
    def _select_cascade(self):
        if self.select_cascade:
            return True
        elif self.frm.update_cascade_as_select and self.update_cascade:
            return True
        else: return False

    @property
    def _delete_cascade(self):
        if self.delete_cascade:
            return True
        elif self.frm.select_cascade_as_delete and self._select_cascade:
            return True
        else: return False

5. [OPTIONAL] Finally: Allow users to do the following as an alternative to using ON UPDATE CASCADE in their database: selector('parent', sg.Table, select_cascade=['child1','child2']). This would then be matched to the foreign key, in a separate auto function:

def auto_map_selector_relationships:
for sel in self.selectors:
    If len[sel.select_cascade]:
        for child in sel.select_cascade:
            for rel in relationships:
                 if rel.parent == sel.table and rel.child == child:
                     rel.select_cascade = True

Does that sound like a good plan? This allows v2 compatibility (with the _delete_cascade and select_cascade_as_delete), and gives users more flexibility about using ON UPDATE CASCADE for database reasons, or if they arent at liberty to modify an existing database.

Or let me know which parts sound good, which parts to keep out!

[ssweber]

Other potential names for select_cascade: auto_requery, orm_cascade, etc.

[ssweber]

I think I have the delete cascade grabbing figured out for mysql/postgres (already had sqlite figured out)

[PySimpleSQL]

Sorry for the hiatus

Here are my thoughts:
1- looks good
2- looks good
3 - I find the names confusing. I think I would personally just rather call things update_cascade and delete_cascade. The documentation can clarify what they do. Perhaps these should be set to None and act as overrides to that is defined in the database schema - this is to your point that one may not have privileges to change this at the database level. This kind of gives the best of both worlds
4 - Absolutely
5 - #3 above might take care of this on it's own

I think overall it's a great idea and would add a lot of flexibility!

[ssweber]

Cool. I’ll work on this next

[ssweber]

Would it be simpler, instead of the property functions, if in the Form init, we instead had db_cascades = True?

Then, if False, we wouldn’t get grab that info to begin with?

[ssweber]

Nvm, I’m overthinking this. I’ll have something for you to review by tonight or tomorrow afternoon

[ssweber]

#180