Revize 0e7c3096
Přidáno uživatelem David Friesecký před téměř 4 roky(ů)
| src/dao/certificate_repository.py | ||
|---|---|---|
| 42 | 42 |
|
| 43 | 43 |
try: |
| 44 | 44 |
sql = (f"INSERT INTO {TAB_CERTIFICATES} "
|
| 45 |
f"({COL_COMMON_NAME},"
|
|
| 46 |
f"{COL_VALID_FROM},"
|
|
| 45 |
f"({COL_VALID_FROM},"
|
|
| 47 | 46 |
f"{COL_VALID_TO},"
|
| 48 | 47 |
f"{COL_PEM_DATA},"
|
| 49 |
f"{COL_PRIVATE_KEY_ID},"
|
|
| 48 |
f"{COL_COMMON_NAME},"
|
|
| 49 |
f"{COL_COUNTRY_CODE},"
|
|
| 50 |
f"{COL_LOCALITY},"
|
|
| 51 |
f"{COL_PROVINCE},"
|
|
| 52 |
f"{COL_ORGANIZATION},"
|
|
| 53 |
f"{COL_ORGANIZATIONAL_UNIT},"
|
|
| 54 |
f"{COL_EMAIL_ADDRESS},"
|
|
| 50 | 55 |
f"{COL_TYPE_ID},"
|
| 51 |
f"{COL_PARENT_ID})"
|
|
| 52 |
f"VALUES(?,?,?,?,?,?,?)")
|
|
| 53 |
values = [certificate.common_name,
|
|
| 54 |
certificate.valid_from,
|
|
| 56 |
f"{COL_PARENT_ID},"
|
|
| 57 |
f"{COL_PRIVATE_KEY_ID}) "
|
|
| 58 |
f"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)")
|
|
| 59 |
values = [certificate.valid_from,
|
|
| 55 | 60 |
certificate.valid_to, |
| 56 | 61 |
certificate.pem_data, |
| 57 |
certificate.private_key_id, |
|
| 62 |
certificate.common_name, |
|
| 63 |
certificate.country_code, |
|
| 64 |
certificate.locality, |
|
| 65 |
certificate.province, |
|
| 66 |
certificate.organization, |
|
| 67 |
certificate.organizational_unit, |
|
| 68 |
certificate.email_address, |
|
| 58 | 69 |
certificate.type_id, |
| 59 |
certificate.parent_id] |
|
| 70 |
certificate.parent_id, |
|
| 71 |
certificate.private_key_id] |
|
| 60 | 72 |
self.cursor.execute(sql, values) |
| 61 | 73 |
self.connection.commit() |
| 62 | 74 |
|
| 63 | 75 |
last_id: int = self.cursor.lastrowid |
| 64 | 76 |
|
| 65 |
# TODO assure that this is correct |
|
| 66 | 77 |
if certificate.type_id == ROOT_CA_ID: |
| 67 | 78 |
certificate.parent_id = last_id |
| 68 | 79 |
self.update(last_id, certificate) |
| ... | ... | |
| 127 | 138 |
for usage_row in usage_rows: |
| 128 | 139 |
usage_dict[usage_row[2]] = True |
| 129 | 140 |
|
| 130 |
certificate: Certificate = Certificate(certificate_row[0], |
|
| 131 |
certificate_row[1], |
|
| 132 |
certificate_row[2], |
|
| 133 |
certificate_row[3], |
|
| 134 |
certificate_row[4], |
|
| 135 |
certificate_row[8], |
|
| 136 |
certificate_row[9], |
|
| 137 |
certificate_row[10], |
|
| 141 |
certificate: Certificate = Certificate(certificate_row[0], # ID |
|
| 142 |
certificate_row[1], # valid from |
|
| 143 |
certificate_row[2], # valid to |
|
| 144 |
certificate_row[3], # pem data |
|
| 145 |
certificate_row[14], # type ID |
|
| 146 |
certificate_row[15], # parent ID |
|
| 147 |
certificate_row[16], # private key ID |
|
| 138 | 148 |
usage_dict, |
| 139 |
certificate_row[5], |
|
| 140 |
certificate_row[6]) |
|
| 149 |
certificate_row[4], # common name |
|
| 150 |
certificate_row[5], # country code |
|
| 151 |
certificate_row[6], # locality |
|
| 152 |
certificate_row[7], # province |
|
| 153 |
certificate_row[8], # organization |
|
| 154 |
certificate_row[9], # organizational unit |
|
| 155 |
certificate_row[10], # email address |
|
| 156 |
certificate_row[11], # revocation date |
|
| 157 |
certificate_row[12]) # revocation reason |
|
| 158 |
|
|
| 141 | 159 |
except IntegrityError: |
| 142 | 160 |
Logger.error(INTEGRITY_ERROR_MSG) |
| 143 | 161 |
raise DatabaseException(INTEGRITY_ERROR_MSG) |
| ... | ... | |
| 195 | 213 |
for usage_row in usage_rows: |
| 196 | 214 |
usage_dict[usage_row[2]] = True |
| 197 | 215 |
|
| 198 |
certificates.append(Certificate(certificate_row[0], |
|
| 199 |
certificate_row[1], |
|
| 200 |
certificate_row[2], |
|
| 201 |
certificate_row[3], |
|
| 202 |
certificate_row[4], |
|
| 203 |
certificate_row[8], |
|
| 204 |
certificate_row[9], |
|
| 205 |
certificate_row[10], |
|
| 216 |
certificates.append(Certificate(certificate_row[0], # ID |
|
| 217 |
certificate_row[1], # valid from |
|
| 218 |
certificate_row[2], # valid to |
|
| 219 |
certificate_row[3], # pem data |
|
| 220 |
certificate_row[14], # type ID |
|
| 221 |
certificate_row[15], # parent ID |
|
| 222 |
certificate_row[16], # private key ID |
|
| 206 | 223 |
usage_dict, |
| 207 |
certificate_row[5], |
|
| 208 |
certificate_row[6])) |
|
| 224 |
certificate_row[4], # common name |
|
| 225 |
certificate_row[5], # country code |
|
| 226 |
certificate_row[6], # locality |
|
| 227 |
certificate_row[7], # province |
|
| 228 |
certificate_row[8], # organization |
|
| 229 |
certificate_row[9], # organizational unit |
|
| 230 |
certificate_row[10], # email address |
|
| 231 |
certificate_row[11], # revocation date |
|
| 232 |
certificate_row[12])) # revocation reason |
|
| 209 | 233 |
except IntegrityError: |
| 210 | 234 |
Logger.error(INTEGRITY_ERROR_MSG) |
| 211 | 235 |
raise DatabaseException(INTEGRITY_ERROR_MSG) |
| ... | ... | |
| 243 | 267 |
|
| 244 | 268 |
try: |
| 245 | 269 |
sql = (f"UPDATE {TAB_CERTIFICATES} "
|
| 246 |
f"SET {COL_COMMON_NAME} = ?, "
|
|
| 247 |
f"{COL_VALID_FROM} = ?, "
|
|
| 270 |
f"SET {COL_VALID_FROM} = ?, "
|
|
| 248 | 271 |
f"{COL_VALID_TO} = ?, "
|
| 249 | 272 |
f"{COL_PEM_DATA} = ?, "
|
| 250 |
f"{COL_PRIVATE_KEY_ID} = ?, "
|
|
| 273 |
f"{COL_COMMON_NAME} = ?, "
|
|
| 274 |
f"{COL_COUNTRY_CODE} = ?, "
|
|
| 275 |
f"{COL_LOCALITY} = ?, "
|
|
| 276 |
f"{COL_PROVINCE} = ?, "
|
|
| 277 |
f"{COL_ORGANIZATION} = ?, "
|
|
| 278 |
f"{COL_ORGANIZATIONAL_UNIT} = ?, "
|
|
| 279 |
f"{COL_EMAIL_ADDRESS} = ?, "
|
|
| 251 | 280 |
f"{COL_TYPE_ID} = ?, "
|
| 252 |
f"{COL_PARENT_ID} = ? "
|
|
| 281 |
f"{COL_PARENT_ID} = ?, "
|
|
| 282 |
f"{COL_PRIVATE_KEY_ID} = ? "
|
|
| 253 | 283 |
f"WHERE {COL_ID} = ?")
|
| 254 |
values = [certificate.common_name, |
|
| 255 |
certificate.valid_from, |
|
| 284 |
values = [certificate.valid_from, |
|
| 256 | 285 |
certificate.valid_to, |
| 257 | 286 |
certificate.pem_data, |
| 258 |
certificate.private_key_id, |
|
| 287 |
certificate.common_name, |
|
| 288 |
certificate.country_code, |
|
| 289 |
certificate.locality, |
|
| 290 |
certificate.province, |
|
| 291 |
certificate.organization, |
|
| 292 |
certificate.organizational_unit, |
|
| 293 |
certificate.email_address, |
|
| 259 | 294 |
certificate.type_id, |
| 260 | 295 |
certificate.parent_id, |
| 296 |
certificate.private_key_id, |
|
| 261 | 297 |
certificate_id] |
| 262 | 298 |
|
| 263 | 299 |
self.cursor.execute(sql, values) |
| ... | ... | |
| 409 | 445 |
|
| 410 | 446 |
try: |
| 411 | 447 |
sql = (f"UPDATE {TAB_CERTIFICATES} "
|
| 412 |
f"SET {COL_REVOCATION_DATE} = '', "
|
|
| 413 |
f"{COL_REVOCATION_REASON} = '' "
|
|
| 448 |
f"SET {COL_REVOCATION_DATE} = NULL, "
|
|
| 449 |
f"{COL_REVOCATION_REASON} = NULL "
|
|
| 414 | 450 |
f"WHERE {COL_ID} = ?")
|
| 415 | 451 |
values = [certificate_id] |
| 416 | 452 |
self.cursor.execute(sql, values) |
| ... | ... | |
| 469 | 505 |
for usage_row in usage_rows: |
| 470 | 506 |
usage_dict[usage_row[2]] = True |
| 471 | 507 |
|
| 472 |
certificates.append(Certificate(certificate_row[0], |
|
| 473 |
certificate_row[1], |
|
| 474 |
certificate_row[2], |
|
| 475 |
certificate_row[3], |
|
| 476 |
certificate_row[4], |
|
| 477 |
certificate_row[8], |
|
| 478 |
certificate_row[9], |
|
| 479 |
certificate_row[10], |
|
| 508 |
certificates.append(Certificate(certificate_row[0], # ID |
|
| 509 |
certificate_row[1], # valid from |
|
| 510 |
certificate_row[2], # valid to |
|
| 511 |
certificate_row[3], # pem data |
|
| 512 |
certificate_row[14], # type ID |
|
| 513 |
certificate_row[15], # parent ID |
|
| 514 |
certificate_row[16], # private key ID |
|
| 480 | 515 |
usage_dict, |
| 481 |
certificate_row[5], |
|
| 482 |
certificate_row[6])) |
|
| 516 |
certificate_row[4], # common name |
|
| 517 |
certificate_row[5], # country code |
|
| 518 |
certificate_row[6], # locality |
|
| 519 |
certificate_row[7], # province |
|
| 520 |
certificate_row[8], # organization |
|
| 521 |
certificate_row[9], # organizational unit |
|
| 522 |
certificate_row[10], # email address |
|
| 523 |
certificate_row[11], # revocation date |
|
| 524 |
certificate_row[12])) # revocation reason |
|
| 483 | 525 |
except IntegrityError: |
| 484 | 526 |
Logger.error(INTEGRITY_ERROR_MSG) |
| 485 | 527 |
raise DatabaseException(INTEGRITY_ERROR_MSG) |
| ... | ... | |
| 535 | 577 |
for usage_row in usage_rows: |
| 536 | 578 |
usage_dict[usage_row[2]] = True |
| 537 | 579 |
|
| 538 |
certificates.append(Certificate(certificate_row[0], |
|
| 539 |
certificate_row[1], |
|
| 540 |
certificate_row[2], |
|
| 541 |
certificate_row[3], |
|
| 542 |
certificate_row[4], |
|
| 543 |
certificate_row[8], |
|
| 544 |
certificate_row[9], |
|
| 545 |
certificate_row[10], |
|
| 580 |
certificates.append(Certificate(certificate_row[0], # ID |
|
| 581 |
certificate_row[1], # valid from |
|
| 582 |
certificate_row[2], # valid to |
|
| 583 |
certificate_row[3], # pem data |
|
| 584 |
certificate_row[14], # type ID |
|
| 585 |
certificate_row[15], # parent ID |
|
| 586 |
certificate_row[16], # private key ID |
|
| 546 | 587 |
usage_dict, |
| 547 |
certificate_row[5], |
|
| 548 |
certificate_row[6])) |
|
| 588 |
certificate_row[4], # common name |
|
| 589 |
certificate_row[5], # country code |
|
| 590 |
certificate_row[6], # locality |
|
| 591 |
certificate_row[7], # province |
|
| 592 |
certificate_row[8], # organization |
|
| 593 |
certificate_row[9], # organizational unit |
|
| 594 |
certificate_row[10], # email address |
|
| 595 |
certificate_row[11], # revocation date |
|
| 596 |
certificate_row[12])) # revocation reason |
|
| 549 | 597 |
except IntegrityError: |
| 550 | 598 |
Logger.error(INTEGRITY_ERROR_MSG) |
| 551 | 599 |
raise DatabaseException(INTEGRITY_ERROR_MSG) |
Také k dispozici: Unified diff
Re #8590 - Implemented all fields of subject into DB