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/
On Mon, 06-03-2017 5:39 PM, Paul Ellis wrote:
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
On Mon, Mar 6, 2017 at 1:03 AM, Brian M <bmere...@gmail.com
<mailto:bmere...@gmail.com>> wrote:
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',lambdarow: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
On Sunday, March 5, 2017 at 11:26:51 AM UTC-6, Paul Ellis wrote:
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?
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py
<http://github.com/web2py/web2py> (Source code)
- https://code.google.com/p/web2py/issues/list
<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
<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+unsubscr...@googlegroups.com
<mailto:web2py+unsubscr...@googlegroups.com>.
For more options, visit https://groups.google.com/d/optout
<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 web2py+unsubscr...@googlegroups.com
<mailto:web2py+unsubscr...@googlegroups.com>.
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 web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
