Thanks! I think the reuse-table is a simpler concept for people who rotate on the project to deal with. We'll try that first.
On Tue, Nov 27, 2018 at 7:22 PM Jeremy Evans <[email protected]> wrote: > 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 a topic in the > Google Groups "sequel-talk" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sequel-talk/3GJ8_mOgJ9U/unsubscribe. > To unsubscribe from this group and all its topics, 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. > -- 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.
