auto increment field that reset evry year

[Andrea Santini]

 need to create an auto increamet Field that reset to 1 every Year.

db.define_table('protocol',
Field('n_protocol'),
Field('year_protocol',type='datetime', writable = False, readable = False, default=request.now, requires=(IS_DATETIME(timezone=pytz.timezone("Europe/Gibraltar"),format=('%Y'))))

The field 'n_protocol' must start to 1 auto increment and return to 1 next year.

Why the date is correct when i insert data but is not correct when i see the data stored in db the date is wrong?

I also tried this:

ultimo_protocollo= db(db.protocollo.data_protocollo).select().last()
ieri = ultimo_protocollo.year

data = datetime.datetime

oggi = data.year

db.protocollo.n_protocollo = Field.Virtual('n_protocollo', lambda n_protocollo: (n_protocollo ++ 1) if (ieri == oggi)  else (db.protocollo.n_protocollo =="1"))

but i recive error.

Can you please help me?

Thank you guys

[villas]

Comments...
1. This kind of auto-incrementing is not bullet-proof in a high traffic database.  Probably better to rely on the DB to compute the field with a trigger or SP. 
2. n_protocol  should be an integer field?
3. Virtual fields are not saved to the database.  Maybe look at Compute instead.

All the best.

[Andrea Santini]

i was looking on the net and solved my problem using this code:

protocollo_ieri= db(db.protocollo.anno_registro).select().last()

ieri = protocollo_ieri.anno_registro.year

data = request.now

oggi = data.year

ultimo_protocollo = db(db.protocollo).select(db.protocollo.numero_protocollo,orderby=~db.protocollo.numero_protocollo,limitby=(0,1)).first()

ult_prot = ultimo_protocollo.numero_protocollo

if ieri==oggi:

    db.protocollo.numero_protocollo.default= (ult_prot +1)

else: 

    db.protocollo.numero_protocollo.default= 1

thank you anyway.

[Anthony]

On Tuesday, January 9, 2018 at 11:03:02 AM UTC-5, Andrea Santini wrote:

i was looking on the net and solved my problem using this code:

protocollo_ieri= db(db.protocollo.anno_registro).select().last()

ieri = protocollo_ieri.anno_registro.year

data = request.now

oggi = data.year

ultimo_protocollo = db(db.protocollo).select(db.protocollo.numero_protocollo,orderby=~db.protocollo.numero_protocollo,limitby=(0,1)).first()

ult_prot = ultimo_protocollo.numero_protocollo

if ieri==oggi:

    db.protocollo.numero_protocollo.default= (ult_prot +1)

else: 

    db.protocollo.numero_protocollo.default= 1

I think there are a couple problems with this. First, your value of ultimo_protocollo will end up being the maximum value across all years, but you only want the maximum value within the current year. Second, unless you lock the db.protocollo table on each of these requests, you could end up with a race condition where separate requests made nearly simultaneously both read the same current value for ieri and ultimo_protocollo and then end up creating separate records with identical values for numero_protocollo.

Anthony

[tim.n...@conted.ox.ac.uk]

I've done this with a table with a year as its id:

idb.define_table(
    'next_id',
    Field('year', 'id'),
    Field('next', 'integer'),
)


def get_next_id(year):
    record = idb.next_id(year)
    if not record:
        # Auto add new years
        idb.next_id.insert(year=year, next=1)
        record = idb.next_id(year)
   
    # Return the id while incrementing the seed for the next call
    id = record.next
    record.update_record(next=id + 1)
    return id

Probably not bullet-proof against collisions if traffic is extremely high, but it works for the 10000s of uses per year I use.