Autoincrement which resets each month (not primary id)

[Paul Ellis]

I want to have a numbering system which is 2 digit year, 2 digit month and then an autoincrement number which resets each month. 1703#####

Currently using SQLite with a view to move to MYSQL in future. The MYSQL examples I have found suggest using a composite primary key, which doesn't seem to fit too well with web2py.

The reason I am trying for autoincrement is so the database ensures the numbers are unique. I am willing to look at another way if I am sure I won't end up with 2 identical numbers.

At the moment I have it working with datetime.date.today().strftime('%y%m') + id. So I am half way there, but can't see how to reset each month. I guess I can check for the highest number in the database programmatically, but I am worried about duplicate numbers with a high number of users. This also seems like if the document with the highest number was deleted (but possibly already printed), then the number will be reused.

The program uses the actual primary key for all backend work. This is just an identifier for humans, but the documents produced by the program can't afford to have the same number as another document.

Can someone give me a nudge in the right direction, I am a bit stuck?

[黄祥]

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

[Brian M]

First of all, at the risk of asking a silly question - is there actually a reason to store this secondary ID in the database rather than just have it calculated on the fly as-needed using a virtual field? Assuming that you've got a created_date field already in the table that'll give you the month portion and then there's definitely an id field to give you that so just let web2py figure it out for you on-the-fly

Field.Virtual('human_id', lambda row: int(row.your_table.created_date.strftime('%y%m00000')) + row.your_table.id)

The above would cause a record created today (2017-03-05) that had id = 123 to return a human_id of 170300123 which is I think what you want. Note the extra zeros tacked on to the end of the strftime they are important so that when you add the ID you don't accidentally increment your month number - be sure to include enough to ensure that you can cover the highest realistic record ID (and then add an extra zero :D). Alternatively, you may wish to consider changing your human ID format to something like yymm-###### so that you don't have to worry about inadvertently messing up your date related portion and it is perhaps slightly easier for humans to understand which is presumably important because I don't get why you'd want to include the year and month if it isn't supposed to mean anything to the user.

db.your_table.human_id = Field.Virtual('human_id', lambda row: '{0}-{1}'.format(row.your_table.created_date.strftime('%y%m') +'-' + row.your_table.id), table_name = 'your_table')

If you need to actually store it in the database then you'll have to work more.  Sadly, web2py's computed fields won't work because they don't know the ID before the insert. That leaves you with doing it DB side. MySQL appears to only lets you have one auto-increment per table so unfortunately you can't have both your actual primary key ID and a second human readable monthly ID (that would intentionally be reset to something like 1703000000 this month and 1704000000 next).  What I'd consider is creating a DB trigger that would take care of automatically populating your secondary ID for you all within the database so that there's nothing for you to manage. So on insert the database could automatically look at the date (or use an existing created_date field) and the current auto-increment number and combine as needed and store it for you.

~Brian

[Paul Ellis]

Hey Brian,

Doing it on the fly won't work because I want the number to be set at record creation and be a part of the dataset. Also, this is what I am already doing.

Using a database trigger is something I don't know anything about. So thank you for the nudge, I will research this option.

Paul

--
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 a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/hH_O_mUV4rw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Kiran Subbaraman]

For the trigger ... you could also consider using the web2py support for on insert / on update callbacks: http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#callbacks-on-record-insert-delete-and-update

________________________________________
Kiran Subbaraman
http://subbaraman.wordpress.com/about/

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 web2py+un...@googlegroups.com.

[Anthony]

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

[Richard Vézina]

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

--

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 web2py+unsubscribe@googlegroups.com.