Anthony is right... In my case I had to manage such kind of custom primary key generation... I use onvalidation() make a callback and find the sequence field and increment it... I have some function to generate the item id or custom primary key that contains the logic to increment properly the sequence field as the sequence may depend of different other field input. As sequence can be reused if it the last one with no consequence a simple "last sequence value + 1" is sufficient. It proves resilient to as if someone for some reason delete previous sequence the system doesn't care as it just take the greatest last sequence record to iterate over it to find the next sequence. No one never report any issue since it in place back to 2010, so...
Having another table to manage the increment is a good idea and may prevent to have to do a callback to find the last highest sequence value... But you can't use a surrogate id field as for instance in postgres id are burn for ever once used in case insert failed of if you delete a record. It depends if you can reuse a sequence if the last input get delete or not. So the rules that you need to follow to generate you custom primary key has to be clear and procedured, or if not procedured you need to make it clears with your client with a URS (user riqurements). Richard On Mon, Mar 6, 2017 at 11:01 AM, Anthony <[email protected]> wrote: > The problem with any solution that inspects the current records in the > database table in order to figure out the ID for a new record is that you > will not be able to account for deleted records (i.e., you might end up > re-using IDs that were previously used by records that were later deleted). > This approach also requires an extra query for every insert and has a > potential race condition (unless you select for update). > > Instead, you would probably need to store the starting record ID of the > first record each month externally (e.g., in a separate db table). To avoid > having to query that extra table before each insert, you could use the > web2py cache to cache the current month's value and update it with a query > only once a month. > > Anthony > > > On Sunday, March 5, 2017 at 6:23:27 PM UTC-5, 黄祥 wrote: >> >> perhaps you can use count >> *e.g.* >> count_invoice = db.invoice.id.count() >> this_year = request.now.year >> this_month = request.now.month >> >> query_invoice = ((db.invoice.invoice_date.year() == this_year) & >> (db.invoice.invoice_date.month() == this_month) ) >> query_count_invoice = db(query_invoice).select(count >> _invoice).first()[count_invoice] if db(query_invoice).select() else 0 >> >> invoice_no_count = int(query_count_invoice) + 1 if query_count_invoice >> else 1 >> invoice_no_format_count = format(invoice_no_count, '05') >> >> invoice_no = request.now.strftime('%y%m')+invoice_no_format_count >> >> best regards, >> stifan >> > -- > Resources: > - http://web2py.com > - http://web2py.com/book (Documentation) > - http://github.com/web2py/web2py (Source code) > - https://code.google.com/p/web2py/issues/list (Report Issues) > --- > You received this message because you are subscribed to the Google Groups > "web2py-users" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
