Dear all, I am testing Openca (pre 09x.RC) with DBI (mysql). As far as I understand, in the database the certs ar stored with status as VALID and REVOKED (see below). As far I am concerned, it's ok, if we consider the expired certificates as valid ones, but not longer in use. In listCerts and lists commands, there is another status involved (EXPIRED). For my surprise, I did not found any command that put a certificate in expired mode. So I presume that I can write two-lines command to make such command. But I get another surprise: the expired certs (in mysql database querry listed as valid) can not be get by using listItem from OPENCA DBI.pm I took a look in DBI code and I see that for list and update status functions it is assumed that expired=valid. But, as you can see in attachement, the query made are right till some point (NOTAFTER<) and then some are reversed (NOTAFTE>) and the result is a null set. I solved this problem *very ugly* by adding some functions (unshamessly copied from DBI.pm) changed a little bit so now I have an DBI.pm larger with 30%.
Michael, do you think there is an more elegant solution for this? Also, do you think that in DBI.pm is the right place to update the status to expired (as if we find a expired cert in a search and it has valid status to change it in expired)? Alex### new function call ###
Entering getArguments
OpenCA::DBI->getArguments: TABLE:CERTIFICATE
OpenCA::DBI->getArguments: MODE:
OpenCA::DBI->getArguments: attribute: KEY
OpenCA::DBI->getArguments: value:
OpenCA::DBI->getArguments: attribute: STATUS
OpenCA::DBI->getArguments: value:
OpenCA::DBI->getArguments: attribute: DN
OpenCA::DBI->getArguments: value:
OpenCA::DBI->getArguments: attribute: CN
OpenCA::DBI->getArguments: value:
OpenCA::DBI->getArguments: attribute: EMAIL
OpenCA::DBI->getArguments: value:
OpenCA::DBI->getArguments: attribute: ROLE
OpenCA::DBI->getArguments: value:
OpenCA::DBI->getArguments: attribute: PUBKEY
OpenCA::DBI->getArguments: value:
### new function call ###
Entering getStatus
no status given using datatype: EXPIRED_CERTIFICATE
given mode is now: "EXPIRED"
legal status
### leaving function successful ###
OpenCA::DBI->getArguments: status: VALID
completed successful
### leaving function getArguments ###
### new function call ###
Entering function elements
### new function call ###
Entering sub doQuery
query: select count(*) from certificate where status like ? and (notafter < 20020914123257)
bind_values: VALID
prepare statement
statement nr.: 1
execute statement
execute succeeded
### leaving function doQuery successfully ###
state: $rv=
elements comleted successful
### leaving function elements ###
### new function call ###
Entering function listItems
### new function call ###
Entering getArguments
OpenCA::DBI->getArguments: TABLE:CERTIFICATE
OpenCA::DBI->getArguments: MODE:
OpenCA::DBI->getArguments: attribute: KEY
OpenCA::DBI->getArguments: value: 0
OpenCA::DBI->getArguments: attribute: STATUS
OpenCA::DBI->getArguments: value:
OpenCA::DBI->getArguments: attribute: DN
OpenCA::DBI->getArguments: value:
OpenCA::DBI->getArguments: attribute: CN
OpenCA::DBI->getArguments: value:
OpenCA::DBI->getArguments: attribute: EMAIL
OpenCA::DBI->getArguments: value:
OpenCA::DBI->getArguments: attribute: ROLE
OpenCA::DBI->getArguments: value:
OpenCA::DBI->getArguments: attribute: PUBKEY
OpenCA::DBI->getArguments: value:
### new function call ###
Entering getStatus
no status given using datatype: EXPIRED_CERTIFICATE
given mode is now: "EXPIRED"
legal status
### leaving function successful ###
OpenCA::DBI->getArguments: status: VALID
completed successful
### leaving function getArguments ###
OpenCA::DBI->getNextItem: query: select MIN(cert_key) from certificate where (cert_key > ? ) and (status like ?) and (notafter < 20020914123257)
### new function call ###
Entering sub doQuery
query: select MIN(cert_key) from certificate where (cert_key > ? ) and (status like ?) and (notafter < 20020914123257)
bind_values: 0
bind_values: VALID
prepare statement
statement nr.: 2
execute statement
execute succeeded
### leaving function doQuery successfully ###
OpenCA::DBI->getItem: ### new function call ###
OpenCA::DBI->getItem: Entering sub getItem
### new function call ###
Entering getArguments
OpenCA::DBI->getArguments: TABLE:CERTIFICATE
OpenCA::DBI->getArguments: MODE:
OpenCA::DBI->getArguments: attribute: KEY
OpenCA::DBI->getArguments: value: 3
OpenCA::DBI->getArguments: attribute: STATUS
OpenCA::DBI->getArguments: value: VALID
OpenCA::DBI->getArguments: attribute: DN
OpenCA::DBI->getArguments: value:
OpenCA::DBI->getArguments: attribute: CN
OpenCA::DBI->getArguments: value:
OpenCA::DBI->getArguments: attribute: EMAIL
OpenCA::DBI->getArguments: value:
OpenCA::DBI->getArguments: attribute: ROLE
OpenCA::DBI->getArguments: value:
OpenCA::DBI->getArguments: attribute: PUBKEY
OpenCA::DBI->getArguments: value:
### new function call ###
Entering getStatus
status predefined: VALID
legal status
### leaving function successful ###
OpenCA::DBI->getArguments: status: VALID
completed successful
### leaving function getArguments ###
OpenCA::DBI->getItem: data complete OpenCA::DBI->getItem: query: select * from certificate where (cert_key=?) and (status like 'VALID') and (notafter > 20020914123257)
### new function call ###
Entering sub doQuery
query: select * from certificate where (cert_key=?) and (status like 'VALID') and (notafter > 20020914123257)
bind_values: 3
prepare statement
statement nr.: 3
execute statement
execute succeeded
### leaving function doQuery successfully ###
OpenCA::DBI->getItem: query completed
OpenCA::DBI->getItem: check that there is a non-empty result
listItem completed successful
### leaving function successfully ###
OpenCA::DBI automatic commit by destructor DESTROY
call finish on all statement handles to avoid warnings by DBI
