[LRUG] Scoping sequences to a parent

[Craig R Webster]

Hello,

How do you feel about database triggers?

Something like this should do what you're after, I think:

```
CREATE TRIGGER ensure_children_are_indexed_scoped_to_parent
  AFTER INSERT ON child_table
  UPDATE child_table SET index = MAX(index) + 1 WHERE id = LAST_INSERT_ID();
```

No idea if it still works, but you could manage them using something like `hair_trigger`:

  https://github.com/jenseng/hair_trigger <https://github.com/jenseng/hair_trigger>

Yours,
Craig | http://barkingiguana.com/
--
Barking Iguana Ltd. is a company registered in England and Wales.
Registered number: 08915147. Registered address: Jubilee House, East Beach, Lytham, St. Annes, Lancashire, England, FY8 5FT.

> On 2 Sep 2016, at 10:11, Andrew Stewart <boss at airbladesoftware.com> wrote:
> 
> Hello LRUG!
> 
> I have a Rails app using MySQL.  In the app I have a parent model with children and I would like to number the children scoped to the parent – much as GitHub issues are numbered by repo.
> 
> MySQL autoincrement sequences would be ideal if they could be scoped by parent but I don't think that's possible.
> 
> For the past 8 years each parent model has held a counter.  In an after_create callback in the child model, it calls a method on the parent which increments the counter and returns it; the child updates itself to store that value.
> 
> This generally works but I've noticed I get the odd deadlock and uniqueness violation (there's a uniqueness constraint on the child for [parent_id, number]).  These errors crop up when people are importing large numbers of children into the parent's collection.
> 
> An alternative might be to change the after_create callback to query the table for the maximum number among the parent's children, add one, then update itself with that value.  Then the parent table wouldn't be involved.  But it still feels a bit hacky.
> 
> This strikes me as the sort of thing for which there must be a standard solution.  Any hints would be appreciated!
> 
> Many thanks in advance,
> 
> Andy Stewart
> _______________________________________________
> Chat mailing list
> Chat at lists.lrug.org
> Archives: http://lists.lrug.org/pipermail/chat-lrug.org
> Manage your subscription: http://lists.lrug.org/options.cgi/chat-lrug.org
> List info: http://lists.lrug.org/listinfo.cgi/chat-lrug.org

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lrug.org/pipermail/chat-lrug.org/attachments/20160902/50e59036/attachment-0002.html>