On Tuesday, November 27, 2018 at 5:14:51 PM UTC-8, Eric Promislow wrote:
>
> I've done enough digging to know this involves self-joins, but I'm not
> sure how to combine everything.
>
> I've got a table `mangos` with a column `pseudo_id` which I'd like to be
> as small a positive integer as possible.
> When a `mango` is deleted we want to reuse its `pseudo_id` for a new
> `mango` as opposed to setting it
> to something like `self.class.max(:pseudo_id). We spun a few wheels
> trying to figure out how to do this
> in Sequel; any tips?
>
This is more of an SQL question than a Sequel question. One possible way
to solve it:
def DB.next_pseudo_id
n = DB[:mangos].exclude(:pseudo_id=>1).exclude{pseudo_id - 1 =~
DB[:mangos].select(:pseudo_id)}.order(:pseudo_id).get{pseudo_id-1} ||
(return DB[:mangos].max(:pseudo_id)+1)
DB[:mangos].where{pseudo_id < n}.reverse(:pseudo_id).get{pseudo_id+1}
|| 1
end
That's not going to be safe with concurrent use unless you lock the table,
though.
An alternative approach would be to add a separate table with pseudo_ids
available for reuse, with a delete trigger on mangos that inserts values
into that table, and an insert trigger on mangos that deletes the lowest
value from that table and uses it as the value of the pseudo_id column (or
uses max(pseudo_id)+1 if the table is empty).
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
