| 1 | ## OpenCA::DBI | ||
| 2 | ## | ||
| 3 | ## Written by Michael Bell for the OpenCA project 2000 | ||
| 4 | ## Copyright (C) 2000-2004 The OpenCA Project | ||
| 5 | ## GNU public license | ||
| 6 | ## | ||
| 7 | ## Code parts from OpenCA::DB are under the following license | ||
| 8 | ## (copyright statement from OpenCA::DB v0.8.7a | ||
| 9 | ## | ||
| 10 | ## Copyright (C) 1998-1999 Massimiliano Pala (madwolf@openca.org) | ||
| 11 | ## All rights reserved. | ||
| 12 | ## | ||
| 13 | ## This library is free for commercial and non-commercial use as long as | ||
| 14 | ## the following conditions are aheared to. The following conditions | ||
| 15 | ## apply to all code found in this distribution, be it the RC4, RSA, | ||
| 16 | ## lhash, DES, etc., code; not just the SSL code. The documentation | ||
| 17 | ## included with this distribution is covered by the same copyright terms | ||
| 18 | ## | ||
| 19 | ## // Copyright remains Massimiliano Pala's, and as such any Copyright notices | ||
| 20 | ## in the code are not to be removed. | ||
| 21 | ## If this package is used in a product, Massimiliano Pala should be given | ||
| 22 | ## attribution as the author of the parts of the library used. | ||
| 23 | ## This can be in the form of a textual message at program startup or | ||
| 24 | ## in documentation (online or textual) provided with the package. | ||
| 25 | ## | ||
| 26 | ## Redistribution and use in source and binary forms, with or without | ||
| 27 | ## modification, are permitted provided that the following conditions | ||
| 28 | ## are met: | ||
| 29 | ## 1. Redistributions of source code must retain the copyright | ||
| 30 | ## notice, this list of conditions and the following disclaimer. | ||
| 31 | ## 2. Redistributions in binary form must reproduce the above copyright | ||
| 32 | ## notice, this list of conditions and the following disclaimer in the | ||
| 33 | ## documentation and/or other materials provided with the distribution. | ||
| 34 | ## 3. All advertising materials mentioning features or use of this software | ||
| 35 | ## must display the following acknowledgement: | ||
| 36 | ## // "This product includes OpenCA software written by Massimiliano Pala | ||
| 37 | ## // (madwolf@openca.org) and the OpenCA Group (www.openca.org)" | ||
| 38 | ## 4. If you include any Windows specific code (or a derivative thereof) from | ||
| 39 | ## some directory (application code) you must include an acknowledgement: | ||
| 40 | ## "This product includes OpenCA software (www.openca.org)" | ||
| 41 | ## | ||
| 42 | ## THIS SOFTWARE IS PROVIDED BY OPENCA DEVELOPERS ``AS IS'' AND | ||
| 43 | ## ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE | ||
| 44 | ## IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE | ||
| 45 | ## ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE | ||
| 46 | ## FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL | ||
| 47 | ## DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS | ||
| 48 | ## OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) | ||
| 49 | ## HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT | ||
| 50 | ## LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY | ||
| 51 | ## OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF | ||
| 52 | ## SUCH DAMAGE. | ||
| 53 | ## | ||
| 54 | ## The licence and distribution terms for any publically available version or | ||
| 55 | ## derivative of this code cannot be changed. i.e. this code cannot simply be | ||
| 56 | ## copied and put under another distribution licence | ||
| 57 | ## [including the GNU Public Licence.] | ||
| 58 | ## | ||
| 59 | ## end of license of OpenCA::DB v0.8.7a | ||
| 60 | ## | ||
| 61 | |||
| 62 | ## special thanks | ||
| 63 | ## | ||
| 64 | ## MySQL: Julio Sanchez Fernandez <jsanchez@users.sf.net> | ||
| 65 | ## Oracle: balamood@vt.edu (if somebody knows the fullname I add it | ||
| 66 | ## | ||
| 67 | ## please write a note to us if one of the addresses is wrong | ||
| 68 | |||
| 69 | use strict; | ||
| 70 | use utf8; | ||
| 71 | |||
| 72 | package OpenCA::DBI; | ||
| 73 | |||
| 74 | our ($errno, $errval); | ||
| 75 | our ($lastUpdate); | ||
| 76 | |||
| 77 | ## We must store/retrieve CRLs,CERTs,REQs objects: | ||
| 78 | ## proper instances of object management classes are | ||
| 79 | ## needed. | ||
| 80 | ## see http://www.informatik.hu-berlin.de/~mbell/OpenCA/OpenCA_DBI/OpenCA_DBI.html | ||
| 81 | ## for more information about the datastructure | ||
| 82 | |||
| 83 | ## The locale category LC_MESSAGES is not exported by the POSIX | ||
| 84 | ## module on older Perl versions. | ||
| 85 | use Locale::Messages qw (LC_MESSAGES); | ||
| 86 | |||
| 87 | use POSIX ('setlocale'); | ||
| 88 | |||
| 89 | ## Set the locale according to our environment. | ||
| 90 | setlocale (LC_MESSAGES, ''); | ||
| 91 | |||
| 92 | use OpenCA::REQ; | ||
| 93 | use OpenCA::X509; | ||
| 94 | use OpenCA::CRL; | ||
| 95 | use OpenCA::OpenSSL; | ||
| 96 | use OpenCA::Tools; | ||
| 97 | use DBI; | ||
| 98 | |||
| 99 | ## define the SQL_* values. | ||
| 100 | use DBI qw(:sql_types); | ||
| 101 | |||
| 102 | ## the other use directions depends from the used databases | ||
| 103 | |||
| 104 | ## $Revision: 1.46 $ | ||
| 105 | |||
| 106 | # code contributed by Andreas Fitzner | ||
| 107 | ($OpenCA::DBI::VERSION = '$Revision: 1.46 $' )=~ s/(?:^.*: (\d+))|(?:\s+\$$)/defined $1?"2\.0":""/eg; | ||
| 108 | |||
| 109 | ################## | ||
| 110 | ## DB Org-stuff ## | ||
| 111 | ################## | ||
| 112 | |||
| 113 | ############################################################################ | ||
| 114 | ## PLEASE read carefully the following conventions before you ## | ||
| 115 | ## edit something of the database code ## | ||
| 116 | ############################################################################ | ||
| 117 | ## 1. do not use plural at sometime because you can come into trouble ## | ||
| 118 | ## with types ## | ||
| 119 | ## 2. please write all types and all names small - this is not good ## | ||
| 120 | ## sql-style but sybase ase and ibm db2 for example has no problems ## | ||
| 121 | ## with this (they made it big ;-) ). PostgreSQL has with the ## | ||
| 122 | ## standard configuration (tested on debian 2.2 i386) really big ## | ||
| 123 | ## trouble with names of tables and variables in big letters because ## | ||
| 124 | ## it tries to convert it to small letters but it does not work ## | ||
| 125 | ## correctly (I think because it reports errors of mdopen that the ## | ||
| 126 | ## lowercase forget the filename). ## | ||
| 127 | ############################################################################ | ||
| 128 | |||
| 129 | $OpenCA::DBI::SQL = { | ||
| 130 | TABLE => { | ||
| 131 | REQUEST => "request", | ||
| 132 | CERTIFICATE => "certificate", | ||
| 133 | CA_CERTIFICATE => "ca_certificate", | ||
| 134 | CRR => "crr", | ||
| 135 | CRL => "crl", | ||
| 136 | USER => "user", | ||
| 137 | USER_DATA => "user_data", | ||
| 138 | MESSAGES => "messages", | ||
| 139 | }, | ||
| 140 | ## I use here several duplicate array | ||
| 141 | ## somewhere I have to stop the complexity ... | ||
| 142 | VARIABLE => { | ||
| 143 | DATE => ["submit_date", "TEXT"], | ||
| 144 | SUBMIT_DATE => ["submit_date", "TEXT"], | ||
| 145 | FORMAT => ["format", "TEXT"], | ||
| 146 | DATA => ["data", "LONGTEXT"], | ||
| 147 | SERIAL => ["serial", "BIGINT"], | ||
| 148 | ROWID => ["rowid", "AUTO_ID"], | ||
| 149 | KEY => ["mykey", "TEXT_KEY"], | ||
| 150 | CERTIFICATE_SERIAL => ["cert_key", "DECIMAL"], | ||
| 151 | |||
| 152 | # same like certificate_serial but for CRR | ||
| 153 | REVOKE_CERTIFICATE_SERIAL => ["cert_key", "DECIMAL"], | ||
| 154 | CA_CERTIFICATE_SERIAL => ["ca_cert_key", "TEXT_KEY"], | ||
| 155 | REQUEST_SERIAL => ["req_key", "BIGINT"], | ||
| 156 | CSR_SERIAL => ["req_key", "BIGINT"], | ||
| 157 | CRR_SERIAL => ["crr_key", "BIGINT"], | ||
| 158 | APPROVED_AFTER => ["approved_after", "BIGINT"], | ||
| 159 | DELETED_AFTER => ["deleted_after", "BIGINT"], | ||
| 160 | ARCHIVED_AFTER => ["archivied_after", "BIGINT"], | ||
| 161 | #CRL_SERIAL => ["crl_key", "TEXT_KEY"], | ||
| 162 | CRL_SERIAL => ["crl_key", "BIGINT"], | ||
| 163 | LOG_SERIAL => ["action_number", "BIGINT"], | ||
| 164 | SIGNATURE_SERIAL => ["action_number", "BIGINT"], | ||
| 165 | # end of redefined variables | ||
| 166 | |||
| 167 | # Order By | ||
| 168 | CERTIFICATE_ORDERBY => ["rowid"], | ||
| 169 | REVOKE_CERTIFICATE_ORDERBY => ["rowid"], | ||
| 170 | CA_CERTIFICATE_ORDERBY => ["rowid"], | ||
| 171 | REQUEST_ORDERBY => ["rowid"], | ||
| 172 | CSR_ORDERBY => ["rowid"], | ||
| 173 | CRR_ORDERBY => ["rowid"], | ||
| 174 | CRL_ORDERBY => ["rowid"], | ||
| 175 | USER_ORDERBY => ["rowid"], | ||
| 176 | LOG_ORDERBY => ["action_number"], | ||
| 177 | |||
| 178 | # for searching | ||
| 179 | DN => ["dn", "TEXT"], | ||
| 180 | # same like dn but for CRRs | ||
| 181 | REVOKE_CERTIFICATE_DN => ["dn", "TEXT"], | ||
| 182 | CN => ["cn", "TEXT"], | ||
| 183 | EMAIL => ["email", "TEXT"], | ||
| 184 | RA => ["ra", "TEXT"], | ||
| 185 | RAO => ["rao", "TEXT"], | ||
| 186 | OPERATOR => ["rao", "TEXT"], | ||
| 187 | LAST_UPDATE => ["last_update", "BIGINT"], | ||
| 188 | NEXT_UPDATE => ["next_update", "BIGINT"], | ||
| 189 | DATATYPE => ["datatype", "TEXT"], | ||
| 190 | ROLE => ["role", "TEXT"], | ||
| 191 | PUBKEY => ["public_key", "TEXT"], | ||
| 192 | NOTAFTER => ["notafter", "BIGINT"], | ||
| 193 | NOTBEFORE => ["notbefore", "BIGINT"], | ||
| 194 | SUSPENDED_AFTER => ["suspended_after", "BIGINT"], | ||
| 195 | REVOKED_AFTER => ["revoked_after", "BIGINT"], | ||
| 196 | INVALIDITY_REASON => ["invalidity_reason", "TEXT"], | ||
| 197 | OWNER => ["owner", "OWNER"], | ||
| 198 | LAST_ACTIVITY => ["last_activity", "BIGINT"], | ||
| 199 | SCEP_TID => ["scep_tid", "TEXT"], | ||
| 200 | LOA => ["loa", "TEXT"], | ||
| 201 | |||
| 202 | # logging and integrity support | ||
| 203 | DATATYPE => ["datatype", "TEXT"], | ||
| 204 | STATUS => ["status", "TEXT"], | ||
| 205 | REASON => ["reason", "TEXT"], | ||
| 206 | ACTION_NUMBER => ["action_number", "BIGINT"], | ||
| 207 | MODULETYPE => ["moduletype", "TEXT"], | ||
| 208 | MODULE => ["module", "TEXT"], | ||
| 209 | LOG_SUBMIT_DATE => ["log_submit_date", "TEXT"], | ||
| 210 | LOG_DO_DATE => ["log_do_date", "TEXT"], | ||
| 211 | |||
| 212 | # user managing support | ||
| 213 | USER_ID => ["user_id", "USER_ID"], | ||
| 214 | EXT_USER_ID => ["user_id", "EXT_USER_ID"], | ||
| 215 | SECRET => ["secret", "TEXT"], | ||
| 216 | DATA_SOURCE => ["data_source", "TEXT_KEY"], | ||
| 217 | EXTERN_ID => ["extern_id", "TEXT_KEY"], | ||
| 218 | DATA_NAME => ["data_name", "TEXT_KEY"], | ||
| 219 | DATA_VALUE => ["data_value", "TEXT"], | ||
| 220 | |||
| 221 | # messages | ||
| 222 | SUBJECT => ["subject", "TEXT_KEY" ], | ||
| 223 | FROM => ["sender", "EXT_USER_ID" ], | ||
| 224 | TO => [ "receiver", "EXT_USER_ID" ], | ||
| 225 | HEADER => [ "header", "TEXT" ], | ||
| 226 | } | ||
| 227 | }; | ||
| 228 | |||
| 229 | ## second call to $OpenCA::DBI::SQL because I use content of this variable | ||
| 230 | $OpenCA::DBI::SQL->{TABLE_STRUCTURE} = | ||
| 231 | { | ||
| 232 | REQUEST => [ | ||
| 233 | "REQUEST_SERIAL", | ||
| 234 | "FORMAT", | ||
| 235 | "DATA", | ||
| 236 | "DN", | ||
| 237 | "CN", | ||
| 238 | "EMAIL", | ||
| 239 | "RA", | ||
| 240 | "OPERATOR", | ||
| 241 | "STATUS", | ||
| 242 | "ROLE", | ||
| 243 | ## should be part of the header itself | ||
| 244 | ## "HEADER_SIGNATURE", | ||
| 245 | "PUBKEY", | ||
| 246 | "SCEP_TID", | ||
| 247 | "LOA", | ||
| 248 | "NOTBEFORE", | ||
| 249 | "NOTAFTER", | ||
| 250 | "APPROVED_AFTER", | ||
| 251 | "DELETED_AFTER", | ||
| 252 | "ARCHIVED_AFTER", | ||
| 253 | "CA_CERTIFICATE_SERIAL", | ||
| 254 | "OWNER", | ||
| 255 | "ROWID", | ||
| 256 | ], | ||
| 257 | CERTIFICATE => [ | ||
| 258 | "CERTIFICATE_SERIAL", | ||
| 259 | "FORMAT", | ||
| 260 | "DATA", | ||
| 261 | "DN", | ||
| 262 | "CN", | ||
| 263 | "EMAIL", | ||
| 264 | "STATUS", | ||
| 265 | "ROLE", | ||
| 266 | "PUBKEY", | ||
| 267 | "NOTAFTER", | ||
| 268 | "NOTBEFORE", | ||
| 269 | "CSR_SERIAL", | ||
| 270 | "LOA", | ||
| 271 | "SUSPENDED_AFTER", | ||
| 272 | "REVOKED_AFTER", | ||
| 273 | "INVALIDITY_REASON", | ||
| 274 | "OWNER", | ||
| 275 | "ROWID", | ||
| 276 | ], | ||
| 277 | CA_CERTIFICATE => [ | ||
| 278 | ## real serial senseless because at every time zero | ||
| 279 | "CA_CERTIFICATE_SERIAL", | ||
| 280 | "FORMAT", | ||
| 281 | "DATA", | ||
| 282 | "DN", | ||
| 283 | "CN", | ||
| 284 | "EMAIL", | ||
| 285 | "STATUS", | ||
| 286 | "PUBKEY", | ||
| 287 | "NOTAFTER", | ||
| 288 | "NOTBEFORE", | ||
| 289 | "SUSPENDED_AFTER", | ||
| 290 | "REVOKED_AFTER", | ||
| 291 | "INVALIDITY_REASON", | ||
| 292 | "ROWID", | ||
| 293 | ], | ||
| 294 | CRR => [ | ||
| 295 | "CRR_SERIAL", | ||
| 296 | "REVOKE_CERTIFICATE_SERIAL", | ||
| 297 | "SUBMIT_DATE", | ||
| 298 | "FORMAT", | ||
| 299 | "DATA", | ||
| 300 | "REVOKE_CERTIFICATE_DN", | ||
| 301 | "CN", | ||
| 302 | "EMAIL", | ||
| 303 | "RA", | ||
| 304 | "OPERATOR", | ||
| 305 | "STATUS", | ||
| 306 | "REASON", | ||
| 307 | "NOTBEFORE", | ||
| 308 | "NOTAFTER", | ||
| 309 | "APPROVED_AFTER", | ||
| 310 | "DELETED_AFTER", | ||
| 311 | "ARCHIVED_AFTER", | ||
| 312 | "LOA", | ||
| 313 | "OWNER", | ||
| 314 | "ROWID", | ||
| 315 | ## should be part of the header itself | ||
| 316 | ## "HEADER_SIGNATURE" | ||
| 317 | ], | ||
| 318 | CRL => [ | ||
| 319 | "CRL_SERIAL", | ||
| 320 | "STATUS", | ||
| 321 | "FORMAT", | ||
| 322 | "DATA", | ||
| 323 | "LAST_UPDATE", | ||
| 324 | "NEXT_UPDATE", | ||
| 325 | "ROWID", | ||
| 326 | ], | ||
| 327 | USER => [ | ||
| 328 | "ROWID", | ||
| 329 | "USER_ID", | ||
| 330 | "DATA_SOURCE", | ||
| 331 | "SECRET", | ||
| 332 | "NOTAFTER", | ||
| 333 | "NOTBEFORE", | ||
| 334 | "STATUS", | ||
| 335 | "EXTERN_ID", | ||
| 336 | "SUSPENDED_AFTER", | ||
| 337 | "REVOKED_AFTER", | ||
| 338 | "LAST_ACTIVITY", | ||
| 339 | "INVALIDITY_REASON", | ||
| 340 | ], | ||
| 341 | USER_DATA => [ | ||
| 342 | "ROWID", | ||
| 343 | "USER_ID", | ||
| 344 | "DATA_NAME", | ||
| 345 | "DATA_VALUE", | ||
| 346 | "DATA_SOURCE", | ||
| 347 | ], | ||
| 348 | MESSAGES => [ | ||
| 349 | "ROWID", | ||
| 350 | "FROM", | ||
| 351 | "TO", | ||
| 352 | "SUBJECT", | ||
| 353 | "NOTBEFORE", | ||
| 354 | "HEADER", | ||
| 355 | "DATA", | ||
| 356 | "STATUS", | ||
| 357 | ], | ||
| 358 | }; | ||
| 359 | |||
| 360 | $OpenCA::DBI::SQL->{FOREIGN_KEYS} = { | ||
| 361 | USER_DATA => { | ||
| 362 | USER_ID => ["USER", "USER_ID"] | ||
| 363 | }, | ||
| 364 | MESSAGES => { | ||
| 365 | FROM => [ "USER", "USER_ID" ], | ||
| 366 | TO => [ "USER", "USER_ID" ], | ||
| 367 | }, | ||
| 368 | }; | ||
| 369 | |||
| 370 | $OpenCA::DBI::STATUS = { | ||
| 371 | EXIST => 1, | ||
| 372 | VALID => 2, | ||
| 373 | RENEW => 3, | ||
| 374 | UPDATED => 4, | ||
| 375 | PENDING => 5, | ||
| 376 | APPROVED => 6, | ||
| 377 | SUSPENDED => 7, | ||
| 378 | REVOKED => 8, | ||
| 379 | DELETED => 9, | ||
| 380 | ARCHIVED => 10, | ||
| 381 | EXPIRED => 11, | ||
| 382 | NONEXISTENT => 12, | ||
| 383 | ANY => 13, | ||
| 384 | NEW => 14, | ||
| 385 | SIGNED => 15 | ||
| 386 | }; | ||
| 387 | |||
| 388 | ######################### | ||
| 389 | ## end of DB Org-stuff ## | ||
| 390 | ######################### | ||
| 391 | |||
| 392 | ################# | ||
| 393 | ## error-codes ## | ||
| 394 | ################# | ||
| 395 | |||
| 396 | $OpenCA::DBI::ERROR = { | ||
| 397 | |||
| 398 | SUCCESS => 0, | ||
| 399 | DO_NOT_COMMIT => 11111, # protects the database from commiting if the modul crashs | ||
| 400 | UNEXPECTED_ERROR => 88888, | ||
| 401 | ATTACK => 16666, | ||
| 402 | |||
| 403 | # unspecific errors | ||
| 404 | WRONG_DATATYPE => 10001, | ||
| 405 | NO_OBJECT => 10002, | ||
| 406 | GETBASETYPE_FAILED => 10003, | ||
| 407 | UPDATE_WITHOUT_KEY => 10004, | ||
| 408 | UPDATE_WITHOUT_KEY => 10005, | ||
| 409 | ENTRY_EXIST => 10006, | ||
| 410 | ENTRY_NOT_EXIST => 10007, | ||
| 411 | UNSUPPORTED_SEARCH_ATTRIBUTE => 10008, | ||
| 412 | DB_TYPE_UNKNOWN => 10009, | ||
| 413 | SIGNING_LOG_FAILED => 10010, | ||
| 414 | ITEM_NOT_UNIQUE => 10011, | ||
| 415 | FALSE_MODE => 10012, | ||
| 416 | FALSE_FAILSAFE => 10013, | ||
| 417 | FALSE_SECOND_CHANCE => 10014, | ||
| 418 | MISSING_PRIMARY_DATABASE => 10015, | ||
| 419 | MISSING_BACKUP_DATABASE => 10016, | ||
| 420 | MISSING_BACKEND => 10017, | ||
| 421 | MISSING_TOOLS => 10018, | ||
| 422 | AUTOCOMMIT => 10019, | ||
| 423 | |||
| 424 | CERTIFICATE_TABLE_EXIST => 10020, | ||
| 425 | CA_CERTIFICATE_TABLE_EXIST => 10021, | ||
| 426 | CRR_TABLE_EXIST => 10022, | ||
| 427 | CRL_TABLE_EXIST => 10023, | ||
| 428 | LOG_TABLE_EXIST => 10024, | ||
| 429 | SIGNATURE_TABLE_EXIST => 10025, | ||
| 430 | SEQUENCE_TABLE_EXIST => 10026, | ||
| 431 | RBAC_TABLE_EXIST => 10027, | ||
| 432 | REQUEST_TABLE_EXIST => 10028, | ||
| 433 | |||
| 434 | CANNOT_REMOVE_CA_CERTIFICATE => 10030, | ||
| 435 | CANNOT_REMOVE_CRR => 10031, | ||
| 436 | CANNOT_REMOVE_CRL => 10032, | ||
| 437 | CANNOT_REMOVE_LOG => 10033, | ||
| 438 | CANNOT_REMOVE_SIGNATURE => 10034, | ||
| 439 | CANNOT_REMOVE_SEQUENCE => 10035, | ||
| 440 | CANNOT_REMOVE_RBAC => 10036, | ||
| 441 | CANNOT_REMOVE_REQUEST => 10037, | ||
| 442 | CANNOT_REMOVE_CERTIFICATE => 10038, | ||
| 443 | |||
| 444 | CANNOT_CREATE_CA_CERTIFICATE => 10040, | ||
| 445 | CANNOT_CREATE_CRR => 10041, | ||
| 446 | CANNOT_CREATE_CRL => 10042, | ||
| 447 | CANNOT_CREATE_LOG => 10043, | ||
| 448 | CANNOT_CREATE_SIGNATURE => 10044, | ||
| 449 | CANNOT_CREATE_SEQUENCE => 10045, | ||
| 450 | CANNOT_CREATE_RBAC => 10046, | ||
| 451 | CANNOT_CREATE_REQUEST => 10047, | ||
| 452 | CANNOT_CREATE_CERTIFICATE => 10048, | ||
| 453 | |||
| 454 | CANNOT_INIT_SEQUENCE => 10050, | ||
| 455 | |||
| 456 | CANNOT_CREATE_OBJECT => 10060, | ||
| 457 | MISSING_ARG_TABLE => 10061, | ||
| 458 | MISSING_ARG_SERIAL => 10062, | ||
| 459 | MISSING_ARG_DATATYPE => 10063, | ||
| 460 | UNSUPPORTED_OBJECT => 10064, | ||
| 461 | ILLEGAL_STATUS => 10065, | ||
| 462 | ILLEGAL_DATE => 10067, | ||
| 463 | ILLEGAL_ARGUMENT => 10068, | ||
| 464 | MISSING_DATABASE_PARAMETERS => 10070, | ||
| 465 | MISSING_GETTEXT => 10071, | ||
| 466 | MISSING_DATABASE_TYPE => 10072, | ||
| 467 | MISSING_DATABASE_NAME => 10073, | ||
| 468 | MISSING_DATABASE_USER => 10074, | ||
| 469 | MISSING_DATABASE_PASSWD => 10075, | ||
| 470 | |||
| 471 | # DB-errors | ||
| 472 | # using bitwise-or for DIAGNOSTICS so (ERROR+20000) | ||
| 473 | # & ERROR_MASK -> yes/no | ||
| 474 | |||
| 475 | CONNECT_FAILED => 20001, | ||
| 476 | SECOND_CONNECT_FAILED => 20002, | ||
| 477 | NO_BACKUP => 20004, | ||
| 478 | |||
| 479 | PREPARE_FAILED => 20008, | ||
| 480 | EXECUTE_FAILED => 20016, | ||
| 481 | |||
| 482 | SELECT_FAILED => 20032, | ||
| 483 | UPDATE_FAILED => 20064, | ||
| 484 | INSERT_FAILED => 20128, | ||
| 485 | DELETE_FAILED => 20256, | ||
| 486 | COMMIT_FAILED => 20512, | ||
| 487 | ROLLBACK_FAILED => 21024, | ||
| 488 | DISCONNECT_FAILED => 22048, | ||
| 489 | SEQUENCE_GENERATOR_FAILED => 24096, | ||
| 490 | |||
| 491 | }; | ||
| 492 | |||
| 493 | $OpenCA::DBI::MESSAGE = { | ||
| 494 | |||
| 495 | 0 => "Success", | ||
| 496 | 88888 => "Unexpected Error", | ||
| 497 | 16666 => "Possible Attack", | ||
| 498 | |||
| 499 | # unspecific errors | ||
| 500 | 10001 => "WRONG_DATATYPE", | ||
| 501 | 10002 => "NO_OBJECT", | ||
| 502 | 10003 => "GETBASETYPE_FAILED", | ||
| 503 | 10004 => "UPDATE_WITHOUT_KEY", | ||
| 504 | 10006 => "ENTRY_EXIST", | ||
| 505 | 10007 => "ENTRY_NOT_EXIST", | ||
| 506 | 10008 => "UNSUPPORTED_SEARCH_ATTRIBUTE", | ||
| 507 | 10009 => "DB_TYPE_UNKNOWN", | ||
| 508 | 10010 => "SIGNING_LOG_FAILED", | ||
| 509 | 10011 => "ITEM_NOT_UNIQUE", | ||
| 510 | 10012 => "FALSE_MODE - normally this means, that you are not using ". | ||
| 511 | "secure or standard as the accessmode.", | ||
| 512 | 10013 => "FALSE_FAILSAFE - failsafe \"on|off\".", | ||
| 513 | 10014 => "FALSE_SECOND_CHANCE - second_chance \"yes|no\"", | ||
| 514 | 10015 => "MISSING_PRIMARY_DATABASE", | ||
| 515 | 10016 => "MISSING_BACKUP_DATABASE", | ||
| 516 | 10017 => "MISSING_BACKEND", | ||
| 517 | 10018 => "MISSING_TOOLS", | ||
| 518 | 10019 => "AUTOCOMMMIT is on.", | ||
| 519 | |||
| 520 | 10020 => "The table certificate already exists.", | ||
| 521 | 10021 => "The table ca_certificate already exists.", | ||
| 522 | 10022 => "The table crr already exists.", | ||
| 523 | 10023 => "The table crl already exists.", | ||
| 524 | 10024 => "The table log already exists.", | ||
| 525 | 10025 => "The table signature already exists.", | ||
| 526 | 10026 => "The sequence generator already exists.", | ||
| 527 | 10027 => "The table rbac already exists.", | ||
| 528 | 10028 => "The table request already exists.", | ||
| 529 | |||
| 530 | 10030 => "Cannot drop the table ca_certificate.", | ||
| 531 | 10031 => "Cannot drop the table crr.", | ||
| 532 | 10032 => "Cannot drop the table crl.", | ||
| 533 | 10033 => "Cannot drop the table log.", | ||
| 534 | 10034 => "Cannot drop the table signature.", | ||
| 535 | 10035 => "Cannot drop the sequence generator.", | ||
| 536 | 10036 => "Cannot drop the table rbac.", | ||
| 537 | 10037 => "Cannot drop the table request.", | ||
| 538 | 10038 => "Cannot drop the table certificate.", | ||
| 539 | |||
| 540 | 10040 => "Cannot create the table ca_certificate.", | ||
| 541 | 10041 => "Cannot create the table crr.", | ||
| 542 | 10042 => "Cannot create the table crl.", | ||
| 543 | 10043 => "Cannot create the table log.", | ||
| 544 | 10044 => "Cannot create the table signature.", | ||
| 545 | 10045 => "Cannot create the sequence generator.", | ||
| 546 | 10046 => "Cannot create the table rbac.", | ||
| 547 | 10047 => "Cannot create the table request.", | ||
| 548 | 10048 => "Cannot create the table certificate.", | ||
| 549 | |||
| 550 | 10050 => "Cannot init sequence generator.", | ||
| 551 | |||
| 552 | 10060 => "CANNOT_CREATE_OBJECT", | ||
| 553 | 10061 => "MISSING_ARG_TABLE", | ||
| 554 | 10062 => "MISSING_ARG_SERIAL", | ||
| 555 | 10063 => "MISSING_ARG_DATATYPE", | ||
| 556 | 10064 => "UNSUPPORTED_OBJECT", | ||
| 557 | 10065 => "ILLEGAL_STATUS", | ||
| 558 | 10067 => "ILLEGAL_DATE", | ||
| 559 | 10068 => "There is an illegal or unsupported argument.", | ||
| 560 | 10070 => "Missing database parameters (type, name, user or passphrase). Does the passphrase be empty?", | ||
| 561 | 10071 => "The translation function is missing.", | ||
| 562 | 10072 => "The database type is missing.", | ||
| 563 | 10073 => "The database name is missing.", | ||
| 564 | 10074 => "The database user is missing.", | ||
| 565 | 10075 => "The database passphrase is missing. There must be a database passphrase.", | ||
| 566 | |||
| 567 | 11111 => "Do not commit if the database or the module itself fails.", | ||
| 568 | # DB-errors | ||
| 569 | # using bitwise-or for DIAGNOSTICS so (ERROR+20000) | ||
| 570 | # & ERROR_MASK -> yes/no | ||
| 571 | |||
| 572 | 20001 => "CONNECT_FAILED", | ||
| 573 | 20002 => "SECOND_CONNECT_FAILED", | ||
| 574 | 20004 => "NO_BACKUP", | ||
| 575 | |||
| 576 | 20008 => "PREPARE_FAILED", | ||
| 577 | 20016 => "EXECUTE_FAILED", | ||
| 578 | |||
| 579 | 20032 => "SELECT_FAILED", | ||
| 580 | 20064 => "UPDATE_FAILED", | ||
| 581 | 20128 => "INSERT_FAILED", | ||
| 582 | 20256 => "DELETE_FAILED", | ||
| 583 | 20512 => "COMMIT_FAILED", | ||
| 584 | 21024 => "ROLLBACK_FAILED", | ||
| 585 | 22048 => "DISCONNECT_FAILED", | ||
| 586 | 24096 => "SEQUENCE_GENERATOR_FAILED", | ||
| 587 | |||
| 588 | }; | ||
| 589 | |||
| 590 | ## these vars are used to handle crashes during new | ||
| 591 | $OpenCA::DBI::ERRNO = $OpenCA::DBI::ERROR->{SUCCESS}; | ||
| 592 | |||
| 593 | ####################### | ||
| 594 | ## end of errorcodes ## | ||
| 595 | ####################### | ||
| 596 | |||
| 597 | ################ | ||
| 598 | ## modulecodes ## | ||
| 599 | ################ | ||
| 600 | |||
| 601 | $OpenCA::DBI::MODULETYPE = { | ||
| 602 | UNKNOWN => 0, | ||
| 603 | CA => 1, | ||
| 604 | PKIManager => 2, | ||
| 605 | RA => 3, | ||
| 606 | WebGateway => 4, | ||
| 607 | RAServer => 5, | ||
| 608 | }; | ||
| 609 | |||
| 610 | ####################### | ||
| 611 | ## end of modulecodes ## | ||
| 612 | ####################### | ||
| 613 | |||
| 614 | ############################################ | ||
| 615 | ## begin of vendordependent databasestuff ## | ||
| 616 | ############################################ | ||
| 617 | |||
| 618 | ## how much spped costs this for 10 databases (compared with 2 databases)? | ||
| 619 | $OpenCA::DBI::DB = { | ||
| 620 | Pg => { | ||
| 621 | TYPE => { | ||
| 622 | ## numeric available but not documented | ||
| 623 | TEXT => "text", | ||
| 624 | LONGTEXT => "text", | ||
| 625 | TEXT_KEY => "text", | ||
| 626 | BIGINT => "int8", | ||
| 627 | AUTO_ID => "BIGSERIAL", | ||
| 628 | DECIMAL => "DECIMAL (60, 0)", | ||
| 629 | USER_ID => "VARCHAR(255)", | ||
| 630 | EXT_USER_ID => "VARCHAR(255) NOT NULL", | ||
| 631 | OWNER => "VARCHAR(255)", | ||
| 632 | PRIMARYKEY => "PRIMARY KEY NOT NULL", | ||
| 633 | }, | ||
| 634 | DBI_OPTION => { | ||
| 635 | RaiseError => 0, | ||
| 636 | Taint => 0, | ||
| 637 | AutoCommit => 0}, | ||
| 638 | LIMIT => "__QUERY__ LIMIT __MAXITEMS__", | ||
| 639 | FOREIGN_KEY => "ALTER TABLE __TABLE__ ADD CONSTRAINT __COL__REF__TARGET_COL__ FOREIGN KEY (__COL__) REFERENCES __TARGET_TABLE__(__TARGET_COL__)", | ||
| 640 | }, | ||
| 641 | mysql => { | ||
| 642 | TYPE => { | ||
| 643 | ## numeric available but not documented | ||
| 644 | TEXT => "TEXT", | ||
| 645 | LONGTEXT => "TEXT", | ||
| 646 | TEXT_KEY => "VARCHAR (255)", | ||
| 647 | BIGINT => "BIGINT", | ||
| 648 | DECIMAL => "DECIMAL (60, 0)", | ||
| 649 | USER_ID => "VARCHAR(255)", | ||
| 650 | AUTO_ID => "SERIAL", | ||
| 651 | EXT_USER_ID => "VARCHAR(255) NOT NULL", | ||
| 652 | OWNER => "VARCHAR(255)", | ||
| 653 | PRIMARYKEY => "NOT NULL PRIMARY KEY", | ||
| 654 | |||
| 655 | }, | ||
| 656 | DBI_OPTION => {RaiseError => 0, | ||
| 657 | AutoCommit => 0}, | ||
| 658 | # CREATE_TABLE_OPTION => "TYPE=BDB ENGINE=INNODB CHARSET=utf8", | ||
| 659 | CREATE_TABLE_OPTION => "ENGINE=INNODB CHARSET=utf8", | ||
| 660 | LIMIT => "__QUERY__ LIMIT __MAXITEMS__", | ||
| 661 | FOREIGN_KEY => "ALTER TABLE __TABLE__ ADD CONSTRAINT __COL___REF___TARGET_COL__ FOREIGN KEY (__COL__) REFERENCES __TARGET_TABLE__ (__TARGET_COL__)", | ||
| 662 | }, | ||
| 663 | DB2 => { | ||
| 664 | TYPE => { | ||
| 665 | TEXT => "long varchar", | ||
| 666 | LONGTEXT => "long varchar", | ||
| 667 | ## 255 is the limit for a index key in db2 | ||
| 668 | TEXT_KEY => "varchar (255)", | ||
| 669 | BIGINT => "decimal (31, 0)", | ||
| 670 | DECIMAL => "DECIMAL (31, 0)", | ||
| 671 | USER_ID => "VARCHAR(255)", | ||
| 672 | EXT_USER_ID=> "VARCHAR(255) NOT NULL", | ||
| 673 | OWNER => "VARCHAR(255)", | ||
| 674 | AUTO_ID => "SERIAL", | ||
| 675 | PRIMARYKEY => "PRIMARY KEY NOT NULL", | ||
| 676 | }, | ||
| 677 | DBI_OPTION => { | ||
| 678 | RaiseError => 0, | ||
| 679 | Taint => 0, | ||
| 680 | AutoCommit => 0}, | ||
| 681 | LIMIT => "__QUERY__ FETCH FIRST __MAXITEMS__ ROWS ONLY", | ||
| 682 | FOREIGN_KEY => "ALTER TABLE __TABLE__ ADD CONSTRAINT __COL__REF__TARGET_COL__ FOREIGN KEY (__COL__) REFERENCES __TARGET_TABLE__(__TARGET_COL__)", | ||
| 683 | }, | ||
| 684 | Oracle => { | ||
| 685 | TYPE => { | ||
| 686 | TEXT => "varchar2 (1999)", | ||
| 687 | LONGTEXT => "LONG", | ||
| 688 | ## 2000 is the limit for varchar in Oracle7 | ||
| 689 | TEXT_KEY => "varchar2 (1999)", | ||
| 690 | BIGINT => "number (38)", | ||
| 691 | DECIMAL => "DECIMAL (38, 0)", | ||
| 692 | USER_ID => "VARCHAR2(255)", | ||
| 693 | EXT_USER_ID => "VARCHAR2(255) NOT NULL", | ||
| 694 | OWNER => "VARCHAR(255)", | ||
| 695 | AUTO_ID => "DECIMAL(38,0) NOT NULL AUTO INCREMENT", | ||
| 696 | PRIMARYKEY => "PRIMARY KEY NOT NULL", | ||
| 697 | }, | ||
| 698 | DBI_OPTION => { | ||
| 699 | RaiseError => 0, | ||
| 700 | Taint => 0, | ||
| 701 | AutoCommit => 0, | ||
| 702 | LongReadLen => 32767}, | ||
| 703 | LIMIT => "select * from ( __QUERY__ ) where rownum <= __MAXITEMS__", | ||
| 704 | FOREIGN_KEY => "ALTER TABLE __TABLE__ ADD CONSTRAINT __COL__REF__TARGET_COL__ FOREIGN KEY (__COL__) REFERENCES __TARGET_TABLE__(__TARGET_COL__) INITIALLY DEFERRED DEFERRABLE" | ||
| 705 | }, | ||
| 706 | }; | ||
| 707 | |||
| 708 | ########################################## | ||
| 709 | ## end of vendordependent databasestuff ## | ||
| 710 | ########################################## | ||
| 711 | |||
| 712 | $OpenCA::DBI::beginHeader = "-----BEGIN HEADER-----"; | ||
| 713 | $OpenCA::DBI::endHeader = "-----END HEADER-----"; | ||
| 714 | $OpenCA::DBI::beginAttribute = "-----BEGIN ATTRIBUTE-----"; | ||
| 715 | $OpenCA::DBI::endAttribute = "-----END ATTRIBUTE-----"; | ||
| 716 | |||
| 717 | ## here a special remark | ||
| 718 | ## OpenCA::DBI uses only PEM and such things like SPKAC ... | ||
| 719 | ## binary data like DER is not storable in textfields | ||
| 720 | |||
| 721 | my $params = { | ||
| 722 | backend => undef, | ||
| 723 | ## debugging is off !!! | ||
| 724 | DEBUG => 0, | ||
| 725 | DEBUG_STDERR => 0, | ||
| 726 | ERRNO => 0, | ||
| 727 | ERRVAL => "", | ||
| 728 | }; | ||
| 729 | |||
| 730 | sub new { | ||
| 731 | |||
| 732 | # no idea what this should do | ||
| 733 | |||
| 734 | my $that = shift; | ||
| 735 | my $class = ref($that) || $that; | ||
| 736 | |||
| 737 | ## my $self = $params; | ||
| 738 | my $self; | ||
| 739 | my $help; | ||
| 740 | foreach $help (keys %{$params}) { | ||
| 741 | $self->{$help} = $params->{$help}; | ||
| 742 | } | ||
| 743 | |||
| 744 | bless $self, $class; | ||
| 745 | |||
| 746 | ## because db uses variablenames etc. I cannot define it in $params :-( | ||
| 747 | |||
| 748 | # ok here I start ;-) | ||
| 749 | |||
| 750 | my $keys = { @_ }; | ||
| 751 | |||
| 752 | $self->debug ("new: Starting to init a new OpenCA::DBI"); | ||
| 753 | |||
| 754 | $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} ); | ||
| 755 | |||
| 756 | # non-DB-specific | ||
| 757 | |||
| 758 | $self->{DEBUG} = 1 if ($keys->{DEBUG}); | ||
| 759 | $self->{DEBUG_STDERR} = 1 if ($keys->{DEBUG_STDERR}); | ||
| 760 | $self->{gettext} = $keys->{GETTEXT}; | ||
| 761 | $self->{backend} = $keys->{SHELL}; | ||
| 762 | |||
| 763 | |||
| 764 | $self->debug ("new: checking for backend"); | ||
| 765 | |||
| 766 | if (not $self->{backend}) | ||
| 767 | { | ||
| 768 | $self->set_error ( $OpenCA::DBI::ERROR->{MISSING_BACKEND} ); | ||
| 769 | return undef; | ||
| 770 | } | ||
| 771 | |||
| 772 | $self->debug ("new: defining the class parameters"); | ||
| 773 | |||
| 774 | # The minimum I need is remote: | ||
| 775 | # type, host, port, name, user, passwd | ||
| 776 | |||
| 777 | $self->{DB_Type} = $keys->{DB_Type}; | ||
| 778 | $self->{DB_Host} = $keys->{DB_Host}; | ||
| 779 | $self->{DB_Port} = $keys->{DB_Port}; | ||
| 780 | $self->{DB_Name} = $keys->{DB_Name}; | ||
| 781 | $self->{DB_User} = $keys->{DB_User}; | ||
| 782 | $self->{DB_Passwd} = $keys->{DB_Passwd}; | ||
| 783 | $self->{DB_Namespace} = $keys->{DB_Namespace}; | ||
| 784 | |||
| 785 | ## rewrite table names if there is a namespace | ||
| 786 | |||
| 787 | $self->debug ("new: rewrite table spaces if necessary (namespace)"); | ||
| 788 | if ($keys->{DB_Namespace}) | ||
| 789 | { | ||
| 790 | my $tableprefix = $keys->{DB_Namespace}."."; | ||
| 791 | foreach my $table (keys %{$OpenCA::DBI::SQL->{TABLE}}) | ||
| 792 | { | ||
| 793 | $OpenCA::DBI::SQL->{TABLE}->{$table} = | ||
| 794 | $tableprefix.$OpenCA::DBI::SQL->{TABLE}->{$table}; | ||
| 795 | } | ||
| 796 | } | ||
| 797 | |||
| 798 | # Check for all neccessary variables to initialize OpenCA:DBI | ||
| 799 | |||
| 800 | $self->debug ("new: checking the configuration for enough data"); | ||
| 801 | |||
| 802 | if ( not $self->{gettext} ) { | ||
| 803 | $self->set_error ( $OpenCA::DBI::ERROR->{MISSING_GETTEXT} ); | ||
| 804 | return undef; | ||
| 805 | } | ||
| 806 | |||
| 807 | return $self->set_error ($OpenCA::DBI::ERROR->{MISSING_DATABASE_TYPE}) | ||
| 808 | if (not $self->{DB_Type}); | ||
| 809 | return $self->set_error ($OpenCA::DBI::ERROR->{MISSING_DATABASE_USER}) | ||
| 810 | if (not $self->{DB_User}); | ||
| 811 | |||
| 812 | # 2004-10-27 Martin Bartosch <m.bartosch@cynops.de> | ||
| 813 | # added special handling for Oracle external authentication | ||
| 814 | if ($self->{DB_Type} ne 'Oracle') { | ||
| 815 | return $self->set_error ($OpenCA::DBI::ERROR->{MISSING_DATABASE_NAME}) | ||
| 816 | if (not $self->{DB_Name}); | ||
| 817 | return $self->set_error ($OpenCA::DBI::ERROR->{MISSING_DATABASE_PASSWD}) | ||
| 818 | if (not $self->{DB_Passwd}); | ||
| 819 | } else { | ||
| 820 | # Oracle does not require a database name if the ORACLE_SID is specified | ||
| 821 | # in the environment. | ||
| 822 | # If external authentication is used with Oracle then DB_Name MUST | ||
| 823 | # be empty and ORACLE_SID MUST be set (see DBD::Oracle). | ||
| 824 | return $self->set_error ($OpenCA::DBI::ERROR->{MISSING_DATABASE_NAME}) | ||
| 825 | unless (($self->{DB_Name} ne '') or ($ENV{ORACLE_SID} ne '')); | ||
| 826 | |||
| 827 | # Oracle allows 'external authentication'. In this case the username | ||
| 828 | # is set to '/' and the password is empty. | ||
| 829 | return $self->set_error ($OpenCA::DBI::ERROR->{MISSING_DATABASE_PASSWD}) | ||
| 830 | unless (($self->{DB_Passwd} ne '') or ($self->{DB_User} eq '/')); | ||
| 831 | } | ||
| 832 | |||
| 833 | # The availability of the databases is checked during the operations | ||
| 834 | # because I have different accessed databases and perhaps failsafe. | ||
| 835 | # I could only stop here if there is no database online but to write | ||
| 836 | # here a very big test only for this purpose makes no sense. | ||
| 837 | |||
| 838 | ########################### | ||
| 839 | ## vendor dependent part ## | ||
| 840 | ########################### | ||
| 841 | |||
| 842 | ## preparing now the database-strings | ||
| 843 | ## this is very database dependent | ||
| 844 | $self->debug ("new: preparing the database (vendor dependent)"); | ||
| 845 | |||
| 846 | ## WARNING I do not include any attributes into the DSN | ||
| 847 | ## because I do not know how widely version 1.10 of DBI is used actually | ||
| 848 | ## END of WARNING | ||
| 849 | |||
| 850 | $self->{DSN} = "dbi:".$self->{DB_Type}.":"; | ||
| 851 | if ($self->{DB_Type} eq "Pg") { | ||
| 852 | $self->debug ("new: Pg detected"); | ||
| 853 | $self->{DSN} .= "dbname=".$self->{DB_Name}; | ||
| 854 | $self->{DSN} .= ";"."host=".$self->{DB_Host} if ($self->{DB_Host}); | ||
| 855 | $self->{DSN} .= ";"."port=".$self->{DB_Port} if ($self->{DB_Port}); | ||
| 856 | } elsif ($self->{DB_Type} eq "mysql") { | ||
| 857 | $self->debug ("new: mysql detected"); | ||
| 858 | $self->{DSN} .= "database=".$self->{DB_Name}; | ||
| 859 | $self->{DSN} .= ";"."host=".$self->{DB_Host} if ($self->{DB_Host}); | ||
| 860 | $self->{DSN} .= ";"."port=".$self->{DB_Port} if ($self->{DB_Port}); | ||
| 861 | ## not clean but safe | ||
| 862 | $self->{DSN} .= ";mysql_ssl=0"; | ||
| 863 | } elsif ($self->{DB_Type} =~ /^DB2$/ ) { | ||
| 864 | $self->debug ("new: DB2 detected"); | ||
| 865 | $self->{DSN} .= $self->{DB_Name}; | ||
| 866 | } elsif ($self->{DB_Type} =~ /^Oracle$/ ) { | ||
| 867 | $self->debug ("new: Oracle detected"); | ||
| 868 | ## you can use tnsname or sidname | ||
| 869 | $self->{DSN} .= $self->{DB_Name}; | ||
| 870 | } else { | ||
| 871 | $self->set_error ( $OpenCA::DBI::ERROR->{DB_TYPE_UNKNOWN} ); | ||
| 872 | return undef; | ||
| 873 | } | ||
| 874 | $self->debug ("new: DB: ".$self->{DSN}); | ||
| 875 | |||
| 876 | ################################## | ||
| 877 | ## end of vendor dependent part ## | ||
| 878 | ################################## | ||
| 879 | |||
| 880 | $self->debug ("new: OpenCA::DBI should now complete"); | ||
| 881 | |||
| 882 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 883 | return $self; | ||
| 884 | |||
| 885 | } | ||
| 886 | |||
| 887 | sub connect { | ||
| 888 | my $self = shift; | ||
| 889 | |||
| 890 | $self->debug ("connect: connecting to database"); | ||
| 891 | |||
| 892 | $self->{STH} = undef; | ||
| 893 | |||
| 894 | ## dsn etc. defined so lets try | ||
| 895 | $self->debug ("connect: try to connect"); | ||
| 896 | $self->{DBH} = DBI->connect_cached ($self->{DSN}, | ||
| 897 | $self->{DB_User}, | ||
| 898 | $self->{DB_Passwd}, | ||
| 899 | \%{$OpenCA::DBI::DB->{$self->{DB_Type}}->{DBI_OPTION}}); | ||
| 900 | if (not $self->{DBH}) { | ||
| 901 | ## connect failed try again | ||
| 902 | $self->debug ("connect: connect failed"); | ||
| 903 | $self->set_error ( $DBI::err, $DBI::errstr); | ||
| 904 | $self->set_error ( $OpenCA::DBI::ERROR->{CONNECT_FAILED} ); | ||
| 905 | return undef; | ||
| 906 | } | ||
| 907 | |||
| 908 | $self->debug ("connect: Checking AutoCommit to be off ..."); | ||
| 909 | if ($self->{DBH}->{AutoCommit} == 1) { | ||
| 910 | $self->debug ("connect: AutoCommit is on so I'm aborting ..."); | ||
| 911 | $self->set_error ( $OpenCA::DBI::ERROR->{AUTOCOMMIT} ); | ||
| 912 | return undef; | ||
| 913 | } | ||
| 914 | $self->debug ("connect: AutoCommit is off"); | ||
| 915 | |||
| 916 | my $charset = setlocale (LC_MESSAGES); | ||
| 917 | if ($charset =~ /[^\.]+\.[^\.]+/) | ||
| 918 | { | ||
| 919 | ## encoding is set | ||
| 920 | if( ($self->{DB_Type} =~ /mysql/i ) and ($charset =~ /utf-8/i ) ) { | ||
| 921 | $charset = 'utf8'; | ||
| 922 | } | ||
| 923 | |||
| 924 | $self->debug ("connect: Setting characterset if the database support it ..."); | ||
| 925 | $charset =~ s/^[^\.]+\.//g; | ||
| 926 | $self->doQuery (QUERY => "SET NAMES '$charset'"); | ||
| 927 | $self->debug ("connect: Characterset fixed if possible"); | ||
| 928 | } | ||
| 929 | |||
| 930 | if ( $self->{DB_Type} =~ /Pg/i ) { | ||
| 931 | ## We Set the option for Pg Database | ||
| 932 | $self->{DBH}->{pg_enable_utf8} = 'true'; | ||
| 933 | } | ||
| 934 | |||
| 935 | return 1; | ||
| 936 | } | ||
| 937 | |||
| 938 | ############################# | ||
| 939 | ## database initialization ## | ||
| 940 | ############################# | ||
| 941 | |||
| 942 | sub initDB { | ||
| 943 | ## Generate a new db and initialize it allowing the | ||
| 944 | ## DB to keep track of the DB status | ||
| 945 | |||
| 946 | my $self = shift; | ||
| 947 | my $keys = { @_ }; | ||
| 948 | |||
| 949 | $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} ); | ||
| 950 | |||
| 951 | my $mode = $keys->{MODE}; | ||
| 952 | |||
| 953 | $self->debug ("initDB: Entering sub initDB"); | ||
| 954 | $self->debug ("initDB: MODE: $mode"); | ||
| 955 | |||
| 956 | ## Accepted modes are | ||
| 957 | ## NONE | ||
| 958 | ## FORCE to force table creation | ||
| 959 | ## DRYRUN to get SQL commands | ||
| 960 | |||
| 961 | my ($db, $force, $table, $dsn); | ||
| 962 | # force ? | ||
| 963 | $force = 0; | ||
| 964 | if ( $mode =~ /^FORCE$/i ) { | ||
| 965 | $force = 1; | ||
| 966 | } elsif ( $mode eq "DRYRUN") { | ||
| 967 | $self->{SQL_SCRIPT} = ""; | ||
| 968 | } | ||
| 969 | $self->debug ("initDB: force: $force"); | ||
| 970 | |||
| 971 | ## For Postgres, we generate a new SCHEMA, to be used with namespace | ||
| 972 | ## option (to distinguish between openca's user and pg's user tables) | ||
| 973 | if ( $self->{DB_Type} =~ /Pg/i ) { | ||
| 974 | my $query = undef; | ||
| 975 | |||
| 976 | if ( $self->{DB_Namespace} eq "" ) { | ||
| 977 | generalError( "Please use option <b>namespace</b> in ". | ||
| 978 | "PREFIX/etc/openca/config.xml (required for " . | ||
| 979 | "DB Type (" . $self->{DB_Type} . ")" ); | ||
| 980 | } | ||
| 981 | |||
| 982 | $query = qq{CREATE SCHEMA } . $self->{DB_Namespace}; | ||
| 983 | if ( not defined $self->doQuery ( QUERY => $query, | ||
| 984 | BIND_VALUES => undef ) ) { | ||
| 985 | $self->debug_err("upgradeDB: doQuery fail ($query)"); | ||
| 986 | }; | ||
| 987 | } | ||
| 988 | |||
| 989 | foreach $table (keys %{$OpenCA::DBI::SQL->{TABLE}}) { | ||
| 990 | $self->debug ("initDB: table: $table"); | ||
| 991 | # check for existence | ||
| 992 | $self->debug ("initDB: dsn: ".$self->{DSN}); | ||
| 993 | $self->debug ("initDB: the folloing debugging-output is for DB2"); | ||
| 994 | $self->debug ("initDB: ld_library_path: ".$ENV{LD_LIBRARY_PATH}); | ||
| 995 | $self->debug ("initDB: path: ". $ENV{PATH}); | ||
| 996 | $self->debug ("initDB: libpath". $ENV{LIBPATH}); | ||
| 997 | $self->debug ("initDB: classpath". $ENV{CLASSPATH}); | ||
| 998 | $self->debug ("initDB: oracle_home". $ENV{ORACLE_HOME}); | ||
| 999 | |||
| 1000 | if (defined $self->operateTable (DO=>"exist", TABLE => $table, | ||
| 1001 | MODE => $mode )) { | ||
| 1002 | if ($force or $mode eq "DRYRUN") { | ||
| 1003 | if (not defined $self->operateTable (DO=>"drop", TABLE => $table, | ||
| 1004 | MODE => $mode)) { | ||
| 1005 | $self->set_error ( $OpenCA::DBI::ERROR->{ "CANNOT_REMOVE_".$table } ); | ||
| 1006 | $self->rollback (); | ||
| 1007 | return undef; | ||
| 1008 | } | ||
| 1009 | |||
| 1010 | } else { | ||
| 1011 | $self->set_error ( $OpenCA::DBI::ERROR->{ $table."_TABLE_EXIST" } ); | ||
| 1012 | $self->rollback (); | ||
| 1013 | return undef; | ||
| 1014 | } | ||
| 1015 | } | ||
| 1016 | $self->debug ("initDB: try to create table"); | ||
| 1017 | # create table | ||
| 1018 | if (not defined $self->operateTable (DO=>"create", TABLE => $table, MODE => $mode)) { | ||
| 1019 | $self->set_error ( $OpenCA::DBI::ERROR->{ "CANNOT_CREATE_".$table } ); | ||
| 1020 | $self->rollback (); | ||
| 1021 | return undef; | ||
| 1022 | } | ||
| 1023 | $self->debug ("initDB: table created"); | ||
| 1024 | } | ||
| 1025 | |||
| 1026 | if (not defined $self->commit ()) { | ||
| 1027 | $self->rollback (); | ||
| 1028 | return undef; | ||
| 1029 | } | ||
| 1030 | |||
| 1031 | $self->debug ("initDB: initDB successful completed"); | ||
| 1032 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 1033 | return $self->{SQL_SCRIPT} if ($mode eq "DRYRUN"); | ||
| 1034 | return 1; | ||
| 1035 | } | ||
| 1036 | |||
| 1037 | sub operateTable { | ||
| 1038 | my $self = shift; | ||
| 1039 | my $keys = { @_ }; | ||
| 1040 | |||
| 1041 | $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} ); | ||
| 1042 | |||
| 1043 | my $table = $keys->{TABLE}; | ||
| 1044 | my $operation = $keys->{DO}; | ||
| 1045 | |||
| 1046 | $self->debug ("operateTable: Entering sub operateTable"); | ||
| 1047 | |||
| 1048 | # the tables | ||
| 1049 | my (%tables, $dbh, $statement, $create); | ||
| 1050 | |||
| 1051 | $self->debug ("operateTable: build the create statements"); | ||
| 1052 | |||
| 1053 | ############################ | ||
| 1054 | ## initial tablestructure ## | ||
| 1055 | ## change carefully !!! ## | ||
| 1056 | ############################ | ||
| 1057 | |||
| 1058 | $self->debug_err ("operateTable: table: ($table)". | ||
| 1059 | $OpenCA::DBI::SQL->{TABLE}->{$table}); | ||
| 1060 | $create = "create table ".$OpenCA::DBI::SQL->{TABLE}->{$table}." ("; | ||
| 1061 | for (my $i=0; | ||
| 1062 | $i < scalar (@{$OpenCA::DBI::SQL->{TABLE_STRUCTURE}->{$table}}); | ||
| 1063 | $i++) { | ||
| 1064 | if ($i == 0) { | ||
| 1065 | $create .= $OpenCA::DBI::SQL->{VARIABLE}->{ | ||
| 1066 | $OpenCA::DBI::SQL->{TABLE_STRUCTURE}->{$table}[0] | ||
| 1067 | }[0]." ". | ||
| 1068 | $OpenCA::DBI::DB->{$self->{DB_Type}}->{TYPE}->{ | ||
| 1069 | $OpenCA::DBI::SQL->{VARIABLE}->{ | ||
| 1070 | $OpenCA::DBI::SQL->{TABLE_STRUCTURE}->{$table}[0] | ||
| 1071 | }[1] | ||
| 1072 | }; | ||
| 1073 | if ( $table =~ /CA_CERTIFICATE|CERTIFICATE|CRL|CRR|REQUEST|USER/ ) { | ||
| 1074 | $create .= " " . | ||
| 1075 | $OpenCA::DBI::DB->{$self->{DB_Type}}->{TYPE}->{PRIMARYKEY}; | ||
| 1076 | }; | ||
| 1077 | } else { | ||
| 1078 | $create .= ", ". | ||
| 1079 | $OpenCA::DBI::SQL->{VARIABLE}->{ | ||
| 1080 | $OpenCA::DBI::SQL->{TABLE_STRUCTURE}->{$table}[$i] | ||
| 1081 | }[0]." ". | ||
| 1082 | $OpenCA::DBI::DB->{$self->{DB_Type}}->{TYPE}->{ | ||
| 1083 | $OpenCA::DBI::SQL->{VARIABLE}->{ | ||
| 1084 | $OpenCA::DBI::SQL->{TABLE_STRUCTURE}->{$table}[$i] | ||
| 1085 | }[1] | ||
| 1086 | }; | ||
| 1087 | } | ||
| 1088 | } | ||
| 1089 | $create .= ")"; | ||
| 1090 | $create .= " ".$OpenCA::DBI::DB->{$self->{DB_Type}}->{CREATE_TABLE_OPTION} | ||
| 1091 | if (exists $OpenCA::DBI::DB->{$self->{DB_Type}}->{CREATE_TABLE_OPTION}); | ||
| 1092 | |||
| 1093 | $self->debug ("operateTable: create: $create"); | ||
| 1094 | |||
| 1095 | ############################ | ||
| 1096 | ## end of ## | ||
| 1097 | ## initial tablestructure ## | ||
| 1098 | ## change carefully !!! ## | ||
| 1099 | ############################ | ||
| 1100 | |||
| 1101 | $self->debug ("operateTable: build the statement finally"); | ||
| 1102 | |||
| 1103 | # check table | ||
| 1104 | my $negator = 0; | ||
| 1105 | if ($operation eq "create") { | ||
| 1106 | $statement = $create; | ||
| 1107 | } elsif ($operation eq "drop") { | ||
| 1108 | $statement = "drop table ".$OpenCA::DBI::SQL->{TABLE}->{$table}; | ||
| 1109 | } else { | ||
| 1110 | $statement = "select * from ".$OpenCA::DBI::SQL->{TABLE}->{$table}; | ||
| 1111 | } | ||
| 1112 | |||
| 1113 | $self->debug_err ("operateTable: statement: $statement"); | ||
| 1114 | ## can happen if operation performs for sequence generator | ||
| 1115 | if ($statement eq "") { | ||
| 1116 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 1117 | return 1; | ||
| 1118 | } | ||
| 1119 | $self->debug ("operateTable: run the statement"); | ||
| 1120 | |||
| 1121 | # attention not for final use because of the central | ||
| 1122 | # $OpenCA::DBI::ERROR VARIABLE !!! | ||
| 1123 | |||
| 1124 | # because of a postgres-bug we must commit all changes here | ||
| 1125 | if ($self->{DB_Type} =~ /Pg/i) { | ||
| 1126 | $self->commit(); | ||
| 1127 | } | ||
| 1128 | if ($keys->{MODE} eq "DRYRUN") { | ||
| 1129 | $self->{SQL_SCRIPT} .= $statement.";"; | ||
| 1130 | } else { | ||
| 1131 | my $ret = $self->doQuery ( QUERY => $statement ); | ||
| 1132 | if ( not defined $ret ) { | ||
| 1133 | $self->debug ("operateTable: query failed return " . | ||
| 1134 | "undef (EXCEPT OF NEGATOR)"); | ||
| 1135 | |||
| 1136 | # because of a postgres-bug we must rollback here to | ||
| 1137 | # rescue the following operations | ||
| 1138 | if ($self->{DB_Type} =~ /Pg/i) { | ||
| 1139 | $self->rollback(); | ||
| 1140 | } | ||
| 1141 | |||
| 1142 | if ($negator) { | ||
| 1143 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 1144 | return 1; | ||
| 1145 | } | ||
| 1146 | return undef; | ||
| 1147 | } | ||
| 1148 | } | ||
| 1149 | |||
| 1150 | $self->debug ("operateTable: query succeeded return 1 (EXCEPT OF NEGATOR)"); | ||
| 1151 | return undef if ($negator); | ||
| 1152 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 1153 | return 1; | ||
| 1154 | |||
| 1155 | } | ||
| 1156 | |||
| 1157 | ## ################################################################## | ||
| 1158 | ## Function Name: upgradeDB | ||
| 1159 | ## ################################################################## | ||
| 1160 | |||
| 1161 | sub upgradeDB { | ||
| 1162 | |||
| 1163 | my $self = shift; | ||
| 1164 | my $query = ""; | ||
| 1165 | my $keys = { @_ }; | ||
| 1166 | |||
| 1167 | my @bind_values = (); | ||
| 1168 | my @my_tables = (); | ||
| 1169 | |||
| 1170 | my $mode = "FORCE"; | ||
| 1171 | |||
| 1172 | if ( $self->{DB_Type} =~ /Pg/i ) { | ||
| 1173 | my $query = undef; | ||
| 1174 | |||
| 1175 | if ( $self->{DB_Namespace} eq "" ) { | ||
| 1176 | generalError( "Please use option <b>namespace</b> in ". | ||
| 1177 | "PREFIX/etc/openca/config.xml (required for " . | ||
| 1178 | "DB Type (" . $self->{DB_Type} . ")" ); | ||
| 1179 | } | ||
| 1180 | |||
| 1181 | $query = qq{CREATE SCHEMA } . $self->{DB_Namespace}; | ||
| 1182 | if ( not defined $self->doQuery ( QUERY => $query, | ||
| 1183 | BIND_VALUES => undef ) ) { | ||
| 1184 | $self->debug_err("upgradeDB: doQuery fail ($query)"); | ||
| 1185 | $self->rollback(); | ||
| 1186 | } else { | ||
| 1187 | $self->commit(); | ||
| 1188 | } | ||
| 1189 | } | ||
| 1190 | |||
| 1191 | $query = "alter table certificate alter column " . | ||
| 1192 | $OpenCA::DBI::SQL->{VARIABLE}->{ | ||
| 1193 | $OpenCA::DBI::SQL->{TABLE_STRUCTURE}->{CERTIFICATE}[0] | ||
| 1194 | }[0]." ". | ||
| 1195 | $OpenCA::DBI::DB->{$self->{DB_Type}}->{TYPE}->{ | ||
| 1196 | $OpenCA::DBI::SQL->{VARIABLE}->{ | ||
| 1197 | $OpenCA::DBI::SQL->{TABLE_STRUCTURE}->{CERTIFICATE}[0]} | ||
| 1198 | [1]}; | ||
| 1199 | |||
| 1200 | |||
| 1201 | if ( not defined $self->doQuery ( QUERY => $query, | ||
| 1202 | BIND_VALUES => \@bind_values) ) { | ||
| 1203 | $self->debug_err("upgradeDB: doQuery failure detected 2"); | ||
| 1204 | $self->rollback(); | ||
| 1205 | } else { | ||
| 1206 | $self->commit(); | ||
| 1207 | } | ||
| 1208 | |||
| 1209 | ## Create the new tables | ||
| 1210 | @my_tables = ( "USER", "USER_DATA", "MESSAGES" ); | ||
| 1211 | foreach my $table ( @my_tables ) { | ||
| 1212 | if (defined $self->operateTable (DO=>"exist", TABLE => $table, | ||
| 1213 | MODE => $mode)) { | ||
| 1214 | $self->debug_err ("upgradeDB::Table $table exists, skipping."); | ||
| 1215 | next; | ||
| 1216 | }; | ||
| 1217 | |||
| 1218 | if (not defined $self->operateTable (DO=>"create", TABLE => $table )) { | ||
| 1219 | $self->debug_err ("upgradeDB::ERROR creating TABLE $table."); | ||
| 1220 | $self->rollback(); | ||
| 1221 | } | ||
| 1222 | |||
| 1223 | if (not defined $self->commit ()) { | ||
| 1224 | $self->debug_err ("upgradeDB::Commit failed for $table"); | ||
| 1225 | } | ||
| 1226 | } | ||
| 1227 | |||
| 1228 | foreach my $table ( keys %{$OpenCA::DBI::SQL->{FOREIGN_KEYS}} ) { | ||
| 1229 | |||
| 1230 | ## Now add the Foreign Keys | ||
| 1231 | if ( defined $OpenCA::DBI::SQL->{FOREIGN_KEYS}->{$table} ) { | ||
| 1232 | my ( $col, $target_col, $target_table ); | ||
| 1233 | my ( %refs ); | ||
| 1234 | my ( $query, $myTable ); | ||
| 1235 | |||
| 1236 | $self->debug_err ( "foreignKey:: Keys to be added for $table"); | ||
| 1237 | |||
| 1238 | %refs = %{$OpenCA::DBI::SQL->{FOREIGN_KEYS}->{$table}}; | ||
| 1239 | |||
| 1240 | foreach my $k ( keys %refs ) { | ||
| 1241 | |||
| 1242 | $self->debug_err ( "foreignKey:: K2=> $k [" . | ||
| 1243 | $refs{$k}->[0] . " - " . $refs{$k}->[1]); | ||
| 1244 | |||
| 1245 | $query = $OpenCA::DBI::DB->{ | ||
| 1246 | $self->{DB_Type}}->{FOREIGN_KEY}; | ||
| 1247 | |||
| 1248 | $col = $OpenCA::DBI::SQL->{VARIABLE}->{$k}[0]; | ||
| 1249 | |||
| 1250 | $myTable = | ||
| 1251 | $OpenCA::DBI::SQL->{TABLE}->{$table}; | ||
| 1252 | |||
| 1253 | $target_table = | ||
| 1254 | $OpenCA::DBI::SQL->{TABLE}->{$refs{$k}->[0]}; | ||
| 1255 | |||
| 1256 | $target_col = | ||
| 1257 | $OpenCA::DBI::SQL->{VARIABLE}->{$refs{$k}->[1]}[0]; | ||
| 1258 | |||
| 1259 | $query =~ s/__COL__/$col/g; | ||
| 1260 | $query =~ s/__TABLE__/$myTable/g; | ||
| 1261 | $query =~ s/__TARGET_COL__/$target_col/g; | ||
| 1262 | $query =~ s/__TARGET_TABLE__/$target_table/g; | ||
| 1263 | |||
| 1264 | $self->debug_err( "foreignKey::query => $query" ); | ||
| 1265 | $self->debug_err( "foreignKey::col => $col" ); | ||
| 1266 | $self->debug_err( "foreignKey::target_table => $target_table" ); | ||
| 1267 | $self->debug_err( "foreignKey::target_col => $target_col" ); | ||
| 1268 | if ( not defined $self->doQuery ( QUERY => $query, | ||
| 1269 | BIND_VALUES => \@bind_values) ) { | ||
| 1270 | $self->debug_err("upgradeDB: doQuery fail ($query)"); | ||
| 1271 | $self->rollback(); | ||
| 1272 | } else { | ||
| 1273 | $self->commit(); | ||
| 1274 | } | ||
| 1275 | } | ||
| 1276 | } else { | ||
| 1277 | $self->debug_err ( "foreignKey:: NO FKeys for $table"); | ||
| 1278 | } | ||
| 1279 | } | ||
| 1280 | |||
| 1281 | my $update_cols = { | ||
| 1282 | CERTIFICATE => [ "ROWID", "NOTBEFORE", "SUSPENDED_AFTER","REVOKED_AFTER", | ||
| 1283 | "INVALIDITY_REASON", "OWNER" ], | ||
| 1284 | CA_CERTIFICATE => [ "ROWID", "NOTBEFORE", "SUSPENDED_AFTER", | ||
| 1285 | "REVOKED_AFTER", "INVALIDITY_DATE" ], | ||
| 1286 | REQUEST => [ "ROWID", "NOTBEFORE", "NOTAFTER", "APPROVED_AFTER", | ||
| 1287 | "DELETED_AFTER", "ARCHIVED_AFTER", "OWNER", | ||
| 1288 | "CA_CERTIFICATE_SERIAL" ], | ||
| 1289 | CRR => [ "ROWID", "NOTBEFORE", "NOTAFTER", "APPROVED_AFTER", | ||
| 1290 | "DELETED_AFTER", "ARCHIVED_AFTER", "OWNER" ], | ||
| 1291 | CRL => [ "ROWID" ], | ||
| 1292 | USER => [ "ROWID" ], | ||
| 1293 | USER_DATA => [ "ROWID" ], | ||
| 1294 | MESSAGES => ["ROWID"], | ||
| 1295 | }; | ||
| 1296 | |||
| 1297 | foreach my $table ( keys %$update_cols ) { | ||
| 1298 | my ( $myTable, $myCol, $def, $query ); | ||
| 1299 | |||
| 1300 | $myTable = $OpenCA::DBI::SQL->{TABLE}->{$table}; | ||
| 1301 | foreach my $col ( @{$update_cols->{$table}} ) { | ||
| 1302 | $self->debug_err ( "updateDB: col update $table($col)"); | ||
| 1303 | |||
| 1304 | $myCol = $OpenCA::DBI::SQL->{VARIABLE}->{$col}[0]; | ||
| 1305 | |||
| 1306 | $def = $OpenCA::DBI::DB->{$self->{DB_Type}}->{TYPE}->{ | ||
| 1307 | $OpenCA::DBI::SQL->{VARIABLE}->{$col}[1]}; | ||
| 1308 | |||
| 1309 | $query = "alter table $myTable add column $myCol $def"; | ||
| 1310 | $self->debug_err ( "updateDB: query $query"); | ||
| 1311 | |||
| 1312 | if ( not defined $self->doQuery ( QUERY => $query, | ||
| 1313 | BIND_VALUES => undef ) ) { | ||
| 1314 | $self->debug_err("upgradeDB: doQuery fail ($query)"); | ||
| 1315 | $self->rollback(); | ||
| 1316 | } else { | ||
| 1317 | $self->commit(); | ||
| 1318 | } | ||
| 1319 | } | ||
| 1320 | |||
| 1321 | if ( $self->{DB_Type} =~ /MySQL/i ) { | ||
| 1322 | $query = "alter table $myTable ENGINE=InnoDB"; | ||
| 1323 | |||
| 1324 | if ( not defined $self->doQuery ( QUERY => $query, | ||
| 1325 | BIND_VALUES => undef ) ) { | ||
| 1326 | $self->debug_err("upgradeDB: doQuery fail ($query)"); | ||
| 1327 | }; | ||
| 1328 | } | ||
| 1329 | } | ||
| 1330 | |||
| 1331 | ## Fix for the Postgres_DB --- the DB is correct, but since we might | ||
| 1332 | ## have empty owners, we need to DROP the NOT NULL constraint | ||
| 1333 | if ( $self->{DB_Type} =~ /Pg/i ) { | ||
| 1334 | my $query = undef; | ||
| 1335 | |||
| 1336 | my @my_tables = ( "CERTIFICATE", "REQUEST", "CRR" ); | ||
| 1337 | foreach my $table ( @my_tables ) { | ||
| 1338 | |||
| 1339 | $query = qq{ALTER TABLE } . | ||
| 1340 | $OpenCA::DBI::SQL->{TABLE}->{$table} . | ||
| 1341 | qq{ ALTER COLUMN } . | ||
| 1342 | $OpenCA::DBI::SQL->{VARIABLE}->{OWNER}[0] . | ||
| 1343 | qq{ DROP NOT NULL}; | ||
| 1344 | |||
| 1345 | if ( not defined $self->doQuery ( QUERY => $query, | ||
| 1346 | BIND_VALUES => undef ) ) { | ||
| 1347 | $self->debug_err("upgradeDB: doQuery fail ($query)"); | ||
| 1348 | $self->rollback(); | ||
| 1349 | } else { | ||
| 1350 | $self->commit(); | ||
| 1351 | } | ||
| 1352 | } | ||
| 1353 | } | ||
| 1354 | |||
| 1355 | ## Now we need to update the (CA_)CERTIFICATE TABLES | ||
| 1356 | my @kList = $self->searchItems( DATATYPE=>"CA_CERTIFICATE", | ||
| 1357 | MODE=>"KEYLIST" ); | ||
| 1358 | foreach my $k ( @kList ) { | ||
| 1359 | my $cert = undef; | ||
| 1360 | |||
| 1361 | $cert = $self->getItem ( DATATYPE=>"CA_CERTIFICATE", KEY=>"$k"); | ||
| 1362 | next if ( not $cert ); | ||
| 1363 | |||
| 1364 | $self->debug_err ( "updating Certs: $k [" . | ||
| 1365 | $cert->getParsed()->{CN} . "]" ); | ||
| 1366 | |||
| 1367 | $self->updateKey ( DATATYPE => "CA_CERTIFICATE", | ||
| 1368 | KEY=>"$k", | ||
| 1369 | NEWKEY => $cert->getSerial("CA_CERTIFICATE") ); | ||
| 1370 | } | ||
| 1371 | |||
| 1372 | @kList = $self->searchItems( DATATYPE=>"CERTIFICATE", | ||
| 1373 | MODE=>"KEYLIST" ); | ||
| 1374 | foreach my $k ( @kList ) { | ||
| 1375 | my $cert = undef; | ||
| 1376 | |||
| 1377 | $cert = $self->getItem ( DATATYPE=>"CERTIFICATE", KEY=>"$k"); | ||
| 1378 | next if ( not $cert ); | ||
| 1379 | |||
| 1380 | $self->debug_err ( "updating Certs: $k [" . | ||
| 1381 | $cert->getParsed()->{CN} . "]" ); | ||
| 1382 | |||
| 1383 | $self->updateItem ( DATATYPE=>"CERTIFICATE", KEY=>"$k", | ||
| 1384 | OBJECT => $cert ); | ||
| 1385 | } | ||
| 1386 | |||
| 1387 | @kList = $self->searchItems( DATATYPE=>"CRL", MODE=>"KEYLIST" ); | ||
| 1388 | foreach my $k ( @kList ) { | ||
| 1389 | my $crl = undef; | ||
| 1390 | |||
| 1391 | $crl = $self->getItem ( DATATYPE=>"CRL", KEY=>"$k"); | ||
| 1392 | next if ( not $crl ); | ||
| 1393 | |||
| 1394 | $self->debug_err ( "updating CRL: $k [" . | ||
| 1395 | $crl->getParsed()->{ISSUER} . "]" ); | ||
| 1396 | |||
| 1397 | $self->updateItem ( DATATYPE=>"CRL", KEY=>"$k", | ||
| 1398 | OBJECT => $crl ); | ||
| 1399 | } | ||
| 1400 | |||
| 1401 | return 1; | ||
| 1402 | } | ||
| 1403 | |||
| 1404 | ##################################################################### | ||
| 1405 | #################### END of database initialization ################# | ||
| 1406 | ##################################################################### | ||
| 1407 | |||
| 1408 | ##################################################################### | ||
| 1409 | ## ----------------- storeItem related functions ----------------- ## | ||
| 1410 | ##################################################################### | ||
| 1411 | |||
| 1412 | ## ################################################################## | ||
| 1413 | ## Function Name: updateItem | ||
| 1414 | ## ################################################################## | ||
| 1415 | |||
| 1416 | sub updateItem { | ||
| 1417 | |||
| 1418 | my $self = shift; | ||
| 1419 | my $keys = { @_ }; | ||
| 1420 | my $status = undef; | ||
| 1421 | |||
| 1422 | if ( not defined $keys->{OBJECT} ) { | ||
| 1423 | $errval = $self->{gettext} ("Required Parameter OBJECT is missing."); | ||
| 1424 | return undef; | ||
| 1425 | } | ||
| 1426 | |||
| 1427 | return $self->storeItem ( MODE=>"UPDATE", @_ ); | ||
| 1428 | } | ||
| 1429 | |||
| 1430 | sub updateKey { | ||
| 1431 | |||
| 1432 | my $self = shift; | ||
| 1433 | my $keys = { @_ }; | ||
| 1434 | my $status = undef; | ||
| 1435 | my @bind_values = (); | ||
| 1436 | |||
| 1437 | if ( not defined $keys->{KEY} or not defined $keys->{NEWKEY} ) { | ||
| 1438 | $errval = $self->{gettext} ("Required Parameter is missing."); | ||
| 1439 | return undef; | ||
| 1440 | } | ||
| 1441 | |||
| 1442 | my $table = $self->getTable ($keys->{DATATYPE}); | ||
| 1443 | |||
| 1444 | my $query = "update ". $OpenCA::DBI::SQL->{TABLE}->{$table} . " set " . | ||
| 1445 | $OpenCA::DBI::SQL->{VARIABLE}->{$table."_SERIAL"}[0]."=? ". | ||
| 1446 | " where ". $OpenCA::DBI::SQL->{VARIABLE}->{$table."_SERIAL"}[0]."=?"; | ||
| 1447 | |||
| 1448 | push( @bind_values, $keys->{NEWKEY}); | ||
| 1449 | push( @bind_values, $keys->{KEY}); | ||
| 1450 | |||
| 1451 | if ( not defined $self->doQuery ( QUERY => $query, | ||
| 1452 | BIND_VALUES => \@bind_values) ) { | ||
| 1453 | $self->set_error ( $OpenCA::DBI::ERROR->{UPDATE_FAILED} ); | ||
| 1454 | return undef; | ||
| 1455 | } | ||
| 1456 | |||
| 1457 | return $keys->{NEWKEY}; | ||
| 1458 | |||
| 1459 | } | ||
| 1460 | ## ################################################################## | ||
| 1461 | ## Function Name: storeItem | ||
| 1462 | ## ################################################################## | ||
| 1463 | |||
| 1464 | sub storeItem { | ||
| 1465 | |||
| 1466 | ## arguments miust be ransmitted via $arguments->{...} | ||
| 1467 | |||
| 1468 | ## Store a provided Item (DATA) provided the exact | ||
| 1469 | ## DATATYPE. KEY (position in dB) data to match will | ||
| 1470 | ## be automatically chosen on a DATATYPE basis. | ||
| 1471 | |||
| 1472 | ## The INFORM is used to get the data input format | ||
| 1473 | ## PEM|DER|NET|SPKAC | ||
| 1474 | |||
| 1475 | my $self = shift; | ||
| 1476 | my $keys = { @_ }; | ||
| 1477 | |||
| 1478 | $self->debug_err (">>>>>>>>>> updateItem: datatype => " . | ||
| 1479 | $keys->{DATATYPE} . " / " . | ||
| 1480 | $keys->{OBJECT}->{DATATYPE} ); | ||
| 1481 | |||
| 1482 | if ($keys->{DATATYPE} =~ /CERTIFICATE|REQUEST|CRR/ ) { | ||
| 1483 | my $status = undef; | ||
| 1484 | |||
| 1485 | $status = $self->getStatus ( DATATYPE => $keys->{DATATYPE} ); | ||
| 1486 | $keys->{OBJECT}->setStatus( $status ) | ||
| 1487 | } | ||
| 1488 | |||
| 1489 | $self->debug_err (">>>>>>>>>> updateItem: datatype => " . | ||
| 1490 | $keys->{DATATYPE} . " / " . | ||
| 1491 | $keys->{OBJECT}->{DATATYPE} ); | ||
| 1492 | |||
| 1493 | $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} ); | ||
| 1494 | |||
| 1495 | $self->debug ("storeItem: Entering function storeItem"); | ||
| 1496 | |||
| 1497 | my %arguments = $self->storeItem_getArguments ( @_ ); | ||
| 1498 | ## check for a correct run of storeItem_getArguments | ||
| 1499 | $self->debug ("storeItem: table: ".$arguments {TABLE}); | ||
| 1500 | |||
| 1501 | ## errno set by function | ||
| 1502 | return undef if (not defined $self->storeItem_checkData ( \%arguments ) ); | ||
| 1503 | |||
| 1504 | ## why do we have a sub getTimeString ??? | ||
| 1505 | $arguments {datetime} = getTimeString (); | ||
| 1506 | |||
| 1507 | ## declare variables | ||
| 1508 | my $rv; | ||
| 1509 | |||
| 1510 | ## normal insertion of object | ||
| 1511 | if ($arguments {MODE} =~ /UPDATE/i) { | ||
| 1512 | return undef if (not defined $self->storeItem_update ( \%arguments )); | ||
| 1513 | } else { | ||
| 1514 | return undef if (not defined $self->storeItem_insert ( \%arguments )); | ||
| 1515 | } | ||
| 1516 | |||
| 1517 | ########################################### | ||
| 1518 | ## be warned: a serial can be a zero !!! ## | ||
| 1519 | ########################################### | ||
| 1520 | $self->debug ("storeItem: succeeded - KEY: ".$arguments {KEY}); | ||
| 1521 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 1522 | return $arguments {KEY}; | ||
| 1523 | |||
| 1524 | } | ||
| 1525 | |||
| 1526 | ## ################################################################## | ||
| 1527 | ## Function Name: storeItem_getArguments | ||
| 1528 | ## ################################################################## | ||
| 1529 | |||
| 1530 | sub storeItem_getArguments { | ||
| 1531 | |||
| 1532 | ## parse the arguments | ||
| 1533 | |||
| 1534 | my $self = shift; | ||
| 1535 | my $keys = { @_ }; | ||
| 1536 | |||
| 1537 | $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} ); | ||
| 1538 | |||
| 1539 | my %result; | ||
| 1540 | |||
| 1541 | $self->debug ("storeItem_getArguments: Entering storeItem_getArguments"); | ||
| 1542 | |||
| 1543 | $result{MODE} = $keys->{MODE}; | ||
| 1544 | $result{MODULETYPE} = $keys->{MODULETYPE}; | ||
| 1545 | $result{MODULE} = $keys->{MODULE}; | ||
| 1546 | $result{object} = $keys->{OBJECT}; | ||
| 1547 | |||
| 1548 | return undef if ( not defined $keys->{OBJECT} ); | ||
| 1549 | |||
| 1550 | if ( not defined $keys->{DATATYPE} ) { | ||
| 1551 | $keys->{DATATYPE} = $keys->{OBJECT}->{DATATYPE}; | ||
| 1552 | } | ||
| 1553 | |||
| 1554 | $result {TABLE} = $self->getTable ($keys->{DATATYPE}); | ||
| 1555 | $self->debug ("storeItem_getArguments: table: ".$result {TABLE}); | ||
| 1556 | |||
| 1557 | ## get all searchable attributes | ||
| 1558 | @{$result {attributes}} = | ||
| 1559 | $self->getSearchAttributes( DATATYPE=>$result {TABLE} ); | ||
| 1560 | |||
| 1561 | my $attr; | ||
| 1562 | |||
| 1563 | my $object = $keys->{OBJECT}; | ||
| 1564 | |||
| 1565 | ## storeItem | ||
| 1566 | |||
| 1567 | for $attr ( @{$result {attributes}} ) { | ||
| 1568 | my $value = undef; | ||
| 1569 | |||
| 1570 | $self->debug_err ( "storeItem_getArguments: $attr" ); | ||
| 1571 | |||
| 1572 | # We deal with email separately | ||
| 1573 | next if ( $attr =~ /^EMAIL$/ ); | ||
| 1574 | |||
| 1575 | my %k = %{ $keys }; | ||
| 1576 | |||
| 1577 | $value = undef; | ||
| 1578 | if ( exists $k{$attr} ) { | ||
| 1579 | $value = $k{$attr}; | ||
| 1580 | } else { | ||
| 1581 | if ( defined $object->getParsed()->{HEADER}->{$attr} ) { | ||
| 1582 | $self->debug_err ("storeItem_getArguments: $attr in " . | ||
| 1583 | "HEADER [".$object->getParsed()->{HEADER}->{$attr}."]"); | ||
| 1584 | $value = $object->getParsed()->{HEADER}->{$attr}; | ||
| 1585 | } elsif ( defined $object->getParsed()->{$attr} ) { | ||
| 1586 | $self->debug_err("storeItem_getArguments: $attr in BODY [". | ||
| 1587 | $object->getParsed()->{$attr} . "]" ); | ||
| 1588 | $value = $object->getParsed()->{$attr}; | ||
| 1589 | } elsif ( defined $object->getParsed()->{DN_HASH}->{$attr} ) { | ||
| 1590 | $self->debug_err ("storeItem_getArguments: $attr in DN_HASH [". | ||
| 1591 | $object->getParsed()->{DN_HASH}->{$attr}[0] . "]" ); | ||
| 1592 | $value = $object->getParsed()->{DN_HASH}->{$attr}[0]; | ||
| 1593 | } elsif ( defined $object->{$attr} ) { | ||
| 1594 | $self->debug_err ("storeItem_getArguments: $attr in OBJECT [". | ||
| 1595 | $object->{$attr} . "]" ); | ||
| 1596 | $value = $object->{$attr}; | ||
| 1597 | } else { | ||
| 1598 | $self->debug_err ("storeItem_getArguments: $attr NOT found!"); | ||
| 1599 | $value = undef; | ||
| 1600 | } | ||
| 1601 | }; | ||
| 1602 | |||
| 1603 | $self->debug_err ( "RESULT { $attr } = $value"); | ||
| 1604 | |||
| 1605 | # if ( utf8::is_utf8($value) ) { | ||
| 1606 | # utf8::upgrade($value); | ||
| 1607 | # }; | ||
| 1608 | |||
| 1609 | $result{$attr} = $value; | ||
| 1610 | } | ||
| 1611 | |||
| 1612 | # Now fix the EMAIL attribute | ||
| 1613 | if ( defined $object->getParsed()->{EMAILADDRESSES} ) { | ||
| 1614 | $result {EMAILADDRESS} = ""; | ||
| 1615 | $result {EMAIL} = ""; | ||
| 1616 | foreach my $email (@{$object->getParsed()->{EMAILADDRESSES}}) { | ||
| 1617 | $result {EMAILADDRESS} .= "," if ($result {EMAILADDRESS}); | ||
| 1618 | $result {EMAILADDRESS} .= $email; | ||
| 1619 | $result {EMAIL} .= "," if ($result {EMAIL}); | ||
| 1620 | $result {EMAIL} .= $email; | ||
| 1621 | } | ||
| 1622 | |||
| 1623 | # if ( utf8::is_utf8($result{EMAILADDRESS}) ) { | ||
| 1624 | # print STDERR "VALUE is EMAILADDRESS UTF8 => " . | ||
| 1625 | # "$result{EMAILADDRESS}\n"; | ||
| 1626 | # # utf8::decode($result{EMAILADDRESS}); | ||
| 1627 | # }; | ||
| 1628 | |||
| 1629 | # if ( utf8::is_utf8($result{EMAIL}) ) { | ||
| 1630 | # print STDERR "VALUE is EMAIL UTF8 => " . $result{EMAIL} . "\n"; | ||
| 1631 | # # utf8::decode($result{EMAIL}); | ||
| 1632 | # }; | ||
| 1633 | } | ||
| 1634 | |||
| 1635 | ## enforce status | ||
| 1636 | $result {STATUS} = $self->getStatus ( STATUS => $result {STATUS}, | ||
| 1637 | DATATYPE => $keys->{DATATYPE} ); | ||
| 1638 | |||
| 1639 | ## Get the Numeric Version of the current GMT time | ||
| 1640 | my $today = gmtime; | ||
| 1641 | my $numTodayValue = $self->{backend}->getNumericDate( "$today" ); | ||
| 1642 | my $tempDate = undef; | ||
| 1643 | |||
| 1644 | # print STDERR "storeItem_getArgs: RESULT{STATUS} = " . $result{STATUS} ."\n"; | ||
| 1645 | # print STDERR "storeItem_getArgs: numTodayValue = $numTodayValue\n"; | ||
| 1646 | |||
| 1647 | # print STDERR "storeItem_getArgs: RESULT{NOTAFTER} = " . | ||
| 1648 | $result{NOTAFTER} . "\n"; | ||
| 1649 | # print STDERR "storeItem_getArgs: RESULT{NOTBEFORE} = " . | ||
| 1650 | $result{NOTAFTER} . "\n"; | ||
| 1651 | |||
| 1652 | if ($result {NOTAFTER} ) { | ||
| 1653 | $tempDate = $self->{backend}->getNumericDate ( $result{NOTAFTER} ); | ||
| 1654 | $result {STATUS} = "EXPIRED" if ( $tempDate < $numTodayValue); | ||
| 1655 | } | ||
| 1656 | |||
| 1657 | if ($result {NOTBEFORE} ) { | ||
| 1658 | $tempDate = $self->{backend}->getNumericDate ( $result{NOTBEFORE} ); | ||
| 1659 | $result {STATUS} = "EXPIRED" if ( $tempDate > $numTodayValue); | ||
| 1660 | } | ||
| 1661 | |||
| 1662 | if ($result{NEXT_UPDATE}) { | ||
| 1663 | $tempDate = $self->{backend}->getNumericDate ( $result{NEXT_UPDATE} ); | ||
| 1664 | $result {STATUS} = "EXPIRED" if ( $tempDate < $numTodayValue); | ||
| 1665 | } | ||
| 1666 | |||
| 1667 | if ($result{LAST_UPDATE}) { | ||
| 1668 | $tempDate = $self->{backend}->getNumericDate ( $result{LAST_UPDATE} ); | ||
| 1669 | $result {STATUS} = "EXPIRED" if ($tempDate > $numTodayValue); | ||
| 1670 | } | ||
| 1671 | |||
| 1672 | # print STDERR "storeItem_getArgs: NEW STATUS = " . $result{STATUS} ."\n"; | ||
| 1673 | |||
| 1674 | if (not $result {STATUS}) { | ||
| 1675 | delete ($result {STATUS}); | ||
| 1676 | $self->debug ("storeItem_getArguments: status: erased because empty"); | ||
| 1677 | } else { | ||
| 1678 | $self->debug ("storeItem_getArguments: status: ".$result {STATUS}); | ||
| 1679 | } | ||
| 1680 | |||
| 1681 | ## storage formats | ||
| 1682 | ## If the data is convertible, let us have only one internal | ||
| 1683 | ## format to handle with | ||
| 1684 | $result {INFORM} = $keys->{INFORM}; | ||
| 1685 | if ( not $result {INFORM} ) { | ||
| 1686 | $result {INFORM} = "PEM"; | ||
| 1687 | } | ||
| 1688 | $self->debug (" storeItem_getArguments: inform: ".$result {INFORM}); | ||
| 1689 | |||
| 1690 | $result {KEY} = $object->getSerial ( $keys->{DATATYPE} ); | ||
| 1691 | $self->debug_err (">>> storeItem::KEY => " . $result{KEY} ); | ||
| 1692 | |||
| 1693 | # $result {KEY} = $object->getParsed()->{HEADER}->{SERIAL}; | ||
| 1694 | # $self->debug_err (">>> storeItem::KEY [2] => " . $result{KEY} ); | ||
| 1695 | |||
| 1696 | $result {CONVERTED} = $object->getItem (); | ||
| 1697 | if( $result {TABLE} =~ /(REQUEST|CRR)/i ) { | ||
| 1698 | $result {FORMAT} = $object->getParsed()->{TYPE}; | ||
| 1699 | } else { | ||
| 1700 | $result {FORMAT} = "PEM"; | ||
| 1701 | } | ||
| 1702 | $self->debug ("storeItem_getArguments: KEY:".$result{KEY}); | ||
| 1703 | $self->debug ("storeItem_getArguments: format: ".$result {FORMAT}); | ||
| 1704 | $self->debug ("storeItem_getArguments: converted: ".$result {CONVERTED}); | ||
| 1705 | |||
| 1706 | $self->debug ("storeItem_getArguments: object->getParsed hash:"); | ||
| 1707 | for my $h (keys %{$object->getParsed()}) { | ||
| 1708 | $self->debug ("storeItem_getArguments: object-attribute: $h"); | ||
| 1709 | $self->debug ("storeItem_getArguments: object-value: ".$object->getParsed ()->{$h}); | ||
| 1710 | } | ||
| 1711 | |||
| 1712 | $self->debug ("storeItem_getArguments: function succesfully finished"); | ||
| 1713 | |||
| 1714 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 1715 | return %result; | ||
| 1716 | } | ||
| 1717 | |||
| 1718 | ## ################################################################## | ||
| 1719 | ## Function Name: storeItem_checkData | ||
| 1720 | ## ################################################################## | ||
| 1721 | |||
| 1722 | sub storeItem_checkData { | ||
| 1723 | |||
| 1724 | ## checks all the available data | ||
| 1725 | ## warning: function must called with | ||
| 1726 | # storeItem_checkData { \%arguments} !!! | ||
| 1727 | |||
| 1728 | my $self = shift; | ||
| 1729 | my $arguments = $_[0]; | ||
| 1730 | |||
| 1731 | $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} ); | ||
| 1732 | |||
| 1733 | ## check data | ||
| 1734 | $self->debug ("storeItem_checkData: Entering storeItem_checkData"); | ||
| 1735 | |||
| 1736 | ## determinate table | ||
| 1737 | if (not $arguments->{TABLE}) { | ||
| 1738 | # this is not allowed (for recovery too) | ||
| 1739 | $self->set_error ($OpenCA::DBI::ERROR->{WRONG_DATATYPE}); | ||
| 1740 | return undef; | ||
| 1741 | } | ||
| 1742 | |||
| 1743 | ## if VALID_* then take mode and in doubt use "UPDATE" | ||
| 1744 | ## else UPDATE | ||
| 1745 | ## special handling of CRLs | ||
| 1746 | ## all modes are now enforced !!! | ||
| 1747 | if ( not $arguments->{MODE} ) { | ||
| 1748 | if ( $arguments->{STATUS} =~ /^(VALID|NEW|RENEW)$/ ) { | ||
| 1749 | if ((uc $arguments->{MODE}) ne "UPDATE") { | ||
| 1750 | $arguments->{MODE} = "INSERT"; | ||
| 1751 | } elsif ($arguments->{TABLE} eq "CRL") { | ||
| 1752 | ## blocks updating CRLs | ||
| 1753 | ## create a new one please | ||
| 1754 | $arguments->{MODE} = "INSERT"; | ||
| 1755 | } else { | ||
| 1756 | $arguments->{MODE} = "UPDATE"; | ||
| 1757 | } | ||
| 1758 | } elsif ( $arguments->{TABLE} eq "CRL" ) { | ||
| 1759 | ## blocks updating CRLs | ||
| 1760 | ## create a new one please | ||
| 1761 | $arguments->{MODE} = "INSERT"; | ||
| 1762 | } else { | ||
| 1763 | $arguments->{MODE} = "UPDATE"; | ||
| 1764 | } | ||
| 1765 | } | ||
| 1766 | |||
| 1767 | ## if no moduletype then unknown | ||
| 1768 | if (not $arguments->{MODULETYPE}) { | ||
| 1769 | $arguments->{MODULETYPE} = "UNKNOWN"; | ||
| 1770 | if ($arguments->{MODULE}) { | ||
| 1771 | $arguments->{MODULE} .= " - UNKNOWN MODULETYPE"; | ||
| 1772 | } else { | ||
| 1773 | $arguments->{MODULE} = "UNKNOWN MODULETYPE AND UNKNOWN MODULE"; | ||
| 1774 | } | ||
| 1775 | } | ||
| 1776 | ## if no module then "UNKNOWN MODULE" | ||
| 1777 | if (not $arguments->{MODULE}) { | ||
| 1778 | $arguments->{MODULE} = "UNKNOWN MODULE"; | ||
| 1779 | } | ||
| 1780 | |||
| 1781 | ## if we have no object then return | ||
| 1782 | if (not $arguments->{object}) { | ||
| 1783 | $self->set_error ($OpenCA::DBI::ERROR->{NO_OBJECT}); | ||
| 1784 | return undef; | ||
| 1785 | } | ||
| 1786 | |||
| 1787 | my $query = undef; | ||
| 1788 | my @bind_values = (); | ||
| 1789 | my @bind_types = (); | ||
| 1790 | |||
| 1791 | ## is item existent and unique ? | ||
| 1792 | $self->debug ("storeItem_checkData: check for existence of item"); | ||
| 1793 | $query = "select * from ".$OpenCA::DBI::SQL->{TABLE}->{$arguments->{TABLE}}." where ". | ||
| 1794 | $OpenCA::DBI::SQL->{VARIABLE}->{$arguments->{TABLE}."_SERIAL"}[0]."=?"; | ||
| 1795 | undef @bind_values; | ||
| 1796 | $bind_values[0] = $arguments->{KEY}; | ||
| 1797 | $bind_types [0] = $OpenCA::DBI::SQL->{VARIABLE}->{$arguments->{TABLE}."_SERIAL"}[1]; | ||
| 1798 | |||
| 1799 | $self->debug_err ("storeItem_checkData: doQuery: $query"); | ||
| 1800 | $self->debug_err ("storeItem_checkData: bind_values: @bind_values\n"); | ||
| 1801 | $self->debug_err ("storeItem_checkData: bind_types: @bind_types\n"); | ||
| 1802 | |||
| 1803 | if ( not defined $self->doQuery ( QUERY => $query, | ||
| 1804 | BIND_VALUES => \@bind_values, BIND_TYPES => \@bind_types) ) { | ||
| 1805 | $self->debug_err ("storeItem_checkData: doQuery failure detected"); | ||
| 1806 | $self->set_error ( $OpenCA::DBI::ERROR->{SELECT_FAILED} ); | ||
| 1807 | return undef; | ||
| 1808 | } | ||
| 1809 | |||
| 1810 | my $rv = $self->{STH}->fetchrow_arrayref; | ||
| 1811 | |||
| 1812 | ## normal insertion of object | ||
| 1813 | if (defined $rv and $rv) { | ||
| 1814 | if (defined $arguments->{MODE} and ($arguments->{MODE} =~ /INSERT/)) { | ||
| 1815 | $self->debug_err ("storeItem_checkData: illegal insert"); | ||
| 1816 | $self->set_error ( $OpenCA::DBI::ERROR->{ENTRY_EXIST} ); | ||
| 1817 | return undef; | ||
| 1818 | } else { | ||
| 1819 | $arguments->{MODE} = "UPDATE"; | ||
| 1820 | } | ||
| 1821 | } else { | ||
| 1822 | my @call = caller; | ||
| 1823 | if (defined $arguments->{MODE} and ($arguments->{MODE} =~ /UPDATE/)) { | ||
| 1824 | $self->debug ("storeItem_checkData: illegal update"); | ||
| 1825 | $self->set_error ( $OpenCA::DBI::ERROR->{ENTRY_NOT_EXIST} ); | ||
| 1826 | return undef; | ||
| 1827 | } else { | ||
| 1828 | $arguments->{MODE} = "INSERT"; | ||
| 1829 | } | ||
| 1830 | } | ||
| 1831 | |||
| 1832 | $self->debug ("storeItem_checkData: data is complete"); | ||
| 1833 | $self->debug ("storeItem_checkData: leaving function successfully"); | ||
| 1834 | |||
| 1835 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 1836 | return 1; | ||
| 1837 | } | ||
| 1838 | |||
| 1839 | ## ################################################################## | ||
| 1840 | ## Function Name: storeItem_update | ||
| 1841 | ## ################################################################## | ||
| 1842 | |||
| 1843 | sub storeItem_update { | ||
| 1844 | |||
| 1845 | my $self = shift; | ||
| 1846 | my $arguments = $_[0]; | ||
| 1847 | |||
| 1848 | $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} ); | ||
| 1849 | |||
| 1850 | my $query = undef; | ||
| 1851 | my @bind_values = (); | ||
| 1852 | my @bind_types = (); | ||
| 1853 | |||
| 1854 | $self->debug ("storeItem_upate: update mode"); | ||
| 1855 | ## item existent | ||
| 1856 | ## ok this could be CRR, Request or Certificate | ||
| 1857 | ## verify actual state (check signatures) | ||
| 1858 | ## -- (I think that is not the job of the DBI-Module - so it is not implemented) | ||
| 1859 | ## check all input data | ||
| 1860 | ## -- this should be done earlier | ||
| 1861 | ## -- (attriubtes are checked directly before storing them) | ||
| 1862 | ## is this statechange allowed (for example to prevent multiple DNs) | ||
| 1863 | ## -- actually not implemented (do statechange only) | ||
| 1864 | ## try statechange | ||
| 1865 | ## -- prepare query | ||
| 1866 | $self->debug ("storeItem_update: prepare query"); | ||
| 1867 | $query = "update ".$OpenCA::DBI::SQL->{TABLE}->{$arguments->{TABLE}}." set ". | ||
| 1868 | $OpenCA::DBI::SQL->{VARIABLE}->{DATA}[0]."=?, ". | ||
| 1869 | $OpenCA::DBI::SQL->{VARIABLE}->{FORMAT}[0]."=? "; | ||
| 1870 | |||
| 1871 | push( @bind_values, $arguments->{CONVERTED}); | ||
| 1872 | push( @bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{DATA}[1]); | ||
| 1873 | |||
| 1874 | push( @bind_values, $arguments->{FORMAT}); | ||
| 1875 | push( @bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{FORMAT}[1]); | ||
| 1876 | |||
| 1877 | ## -- adding searchattributes - never update a date !!! | ||
| 1878 | ## -- getSearchAttributes does not return date as attribute | ||
| 1879 | for my $attr ( @{$arguments->{attributes}} ) { | ||
| 1880 | if ( ($attr) and ( $attr !~ /^KEY|ROWID$/ ) ) { | ||
| 1881 | |||
| 1882 | if ( $OpenCA::DBI::SQL->{VARIABLE}->{$attr}[0] eq "" ) { | ||
| 1883 | #print STDERR "ERR::PARAM MISMATCH::$attr\n"; | ||
| 1884 | next; | ||
| 1885 | } | ||
| 1886 | |||
| 1887 | $query .= ", ".$OpenCA::DBI::SQL->{VARIABLE}->{$attr}[0]."=?"; | ||
| 1888 | push ( @bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{$attr}[1] ); | ||
| 1889 | |||
| 1890 | if ( $attr =~ /NOTBEFORE|NOTAFTER|LAST_UPDATE|NEXT_UPDATE|EXPIRES_AFTER|SUBMIT_DATE|LAST_ACTIVITY|SUSPENDED_AFTER|REVOKED_AFTER|APPROVED_AFTER|ARCHIVED_AFTER|DELETED_AFTER/ ) { | ||
| 1891 | if ( $arguments->{$attr} ne "" ) { | ||
| 1892 | $arguments->{$attr} = $self->{backend}->getNumericDate ( | ||
| 1893 | $arguments->{$attr} ); | ||
| 1894 | } | ||
| 1895 | } | ||
| 1896 | |||
| 1897 | $self->debug_err ( "storeItem_update: $attr converted to: " . | ||
| 1898 | $arguments->{$attr} ); | ||
| 1899 | |||
| 1900 | push( @bind_values, $arguments->{$attr} ); | ||
| 1901 | } | ||
| 1902 | } | ||
| 1903 | |||
| 1904 | ## -- set serials | ||
| 1905 | $query .= " where ". | ||
| 1906 | $OpenCA::DBI::SQL->{VARIABLE}->{$arguments->{TABLE}."_SERIAL"}[0]."=?"; | ||
| 1907 | |||
| 1908 | push( @bind_values, $arguments->{KEY}); | ||
| 1909 | push( @bind_types, | ||
| 1910 | $OpenCA::DBI::SQL->{VARIABLE}->{$arguments->{TABLE}."_SERIAL"}[1]); | ||
| 1911 | |||
| 1912 | foreach my $help (@bind_values) { | ||
| 1913 | $self->debug ("storeItem_update: bind_values: $help"); | ||
| 1914 | } | ||
| 1915 | $self->debug ("storeItem_update: query complete, call doQuery"); | ||
| 1916 | $self->debug_err ("storeItem_update: query: $query"); | ||
| 1917 | $self->debug_err ("storeItem_update: bind_values: @bind_values"); | ||
| 1918 | $self->debug_err ("storeItem_update: bind_types: @bind_types"); | ||
| 1919 | |||
| 1920 | if ( not defined $self->doQuery ( QUERY => $query, | ||
| 1921 | BIND_VALUES => \@bind_values, | ||
| 1922 | BIND_TYPES => \@bind_types) ) { | ||
| 1923 | |||
| 1924 | $self->set_error ( $OpenCA::DBI::ERROR->{UPDATE_FAILED} ); | ||
| 1925 | return undef; | ||
| 1926 | } | ||
| 1927 | |||
| 1928 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 1929 | return 1; | ||
| 1930 | } | ||
| 1931 | |||
| 1932 | ## ################################################################## | ||
| 1933 | ## Function Name: storeItem_insert | ||
| 1934 | ## ################################################################## | ||
| 1935 | |||
| 1936 | sub storeItem_insert { | ||
| 1937 | |||
| 1938 | my $self = shift; | ||
| 1939 | my $arguments = $_[0]; | ||
| 1940 | |||
| 1941 | $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} ); | ||
| 1942 | |||
| 1943 | my $query = undef; | ||
| 1944 | my @bind_values = (); | ||
| 1945 | my @bind_types = (); | ||
| 1946 | |||
| 1947 | $self->debug ("storeItem_insert: Entering storeItem_insert"); | ||
| 1948 | ## INSERT | ||
| 1949 | ## mode='update' is allowed in the future to support revoking non-existing request | ||
| 1950 | ## check all input data | ||
| 1951 | ## -- this should be done earlier | ||
| 1952 | ## -- (attriubtes are checked directly before storing them) | ||
| 1953 | ## is this statechange allowed (for example renewal nonexistent request) | ||
| 1954 | ## -- actually not implemented (do statechange only) | ||
| 1955 | ## create row with all additional attributes | ||
| 1956 | ## -- prepare query | ||
| 1957 | $self->debug ("storeItem_insert: prepare query"); | ||
| 1958 | $query = "insert into ". | ||
| 1959 | $OpenCA::DBI::SQL->{TABLE}->{$arguments->{TABLE}}." ( ". | ||
| 1960 | $OpenCA::DBI::SQL->{VARIABLE}->{$arguments->{TABLE}."_SERIAL"}[0].", ". | ||
| 1961 | $OpenCA::DBI::SQL->{VARIABLE}->{DATA}[0].", ". | ||
| 1962 | $OpenCA::DBI::SQL->{VARIABLE}->{FORMAT}[0]." "; | ||
| 1963 | for my $attr ( @{$arguments->{attributes}} ) { | ||
| 1964 | $query .= ", ".$OpenCA::DBI::SQL->{VARIABLE}->{$attr}[0] | ||
| 1965 | if ($attr !~ /^KEY$/ and $arguments->{$attr}); | ||
| 1966 | } | ||
| 1967 | |||
| 1968 | # if ($arguments->{TABLE} =~ /CERTIFICATE/i) { | ||
| 1969 | # $query .= ", ".$OpenCA::DBI::SQL->{VARIABLE}->{NOTAFTER}[0]; | ||
| 1970 | # } | ||
| 1971 | |||
| 1972 | $query .= ") VALUES ("; | ||
| 1973 | |||
| 1974 | ## -- adding data, format, status | ||
| 1975 | $query .= " ?, ?, ?"; | ||
| 1976 | |||
| 1977 | push(@bind_values, $arguments->{KEY}); | ||
| 1978 | push(@bind_types, | ||
| 1979 | $OpenCA::DBI::SQL->{VARIABLE}->{$arguments->{TABLE}."_SERIAL"}[1]); | ||
| 1980 | |||
| 1981 | push(@bind_values, $arguments->{CONVERTED}); | ||
| 1982 | push(@bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{DATA}[1]); | ||
| 1983 | |||
| 1984 | push(@bind_values, $arguments->{FORMAT}); | ||
| 1985 | push(@bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{FORMAT}[1]); | ||
| 1986 | |||
| 1987 | $self->debug ("storeItem_insert: try to parse header"); | ||
| 1988 | ## -- adding searchattributes - never update a date !!! | ||
| 1989 | ## -- getSearchAttributes does not return date as attribute | ||
| 1990 | for my $attr ( @{$arguments->{attributes}} ) { | ||
| 1991 | if ($attr !~ /^KEY$/ and $arguments->{$attr}) { | ||
| 1992 | # so transformation should be correct for SQL | ||
| 1993 | $self->debug ("storeItem_insert: attr: $attr"); | ||
| 1994 | $query .= ", ?"; | ||
| 1995 | if ( $attr =~ /NOTBEFORE|NOTAFTER|LAST_UPDATE|NEXT_UPDATE|EXPIRES_AFTER|SUBMIT_DATE|LAST_ACTIVITY|SUSPENDED_AFTER|REVOKED_AFTER|APPROVED_AFTER|ARCHIVED_AFTER|DELETED_AFTER/ ) { | ||
| 1996 | $arguments->{$attr} = $self->{backend}->getNumericDate ( | ||
| 1997 | $arguments->{$attr} ); | ||
| 1998 | $self->debug_err ( "storeItem_insert: $attr converted to: " . | ||
| 1999 | $arguments->{$attr} ); | ||
| 2000 | }; | ||
| 2001 | |||
| 2002 | push ( @bind_values, $arguments->{$attr} ); | ||
| 2003 | push ( @bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{$attr}[1]); | ||
| 2004 | } | ||
| 2005 | } | ||
| 2006 | |||
| 2007 | $query .= ")"; | ||
| 2008 | |||
| 2009 | foreach my $help (@bind_values) { | ||
| 2010 | $self->debug ("storeItem_insert: bind_values: $help"); | ||
| 2011 | } | ||
| 2012 | $self->debug ("storeItem_insert: query complete, call doQuery"); | ||
| 2013 | |||
| 2014 | $self->debug_err ( "storeItem_insert: $query"); | ||
| 2015 | # for ( my $i = 0 ; $i <$#bind_values; $i++ ) { | ||
| 2016 | # $self->debug_err ( "storeItem_insert: [$i]" . | ||
| 2017 | # substr($bind_values[$i], 0, 60)); | ||
| 2018 | # } | ||
| 2019 | |||
| 2020 | # print STDERR "insert: $query\n"; | ||
| 2021 | $self->debug_err("storeItem_insert: bind_values: @bind_values"); | ||
| 2022 | $self->debug_err("storeItem_insert: bind_types: @bind_types"); | ||
| 2023 | |||
| 2024 | if ( not defined $self->doQuery (QUERY => $query, | ||
| 2025 | BIND_VALUES => \@bind_values, | ||
| 2026 | BIND_TYPES => \@bind_types) ) { | ||
| 2027 | $self->set_error ( $OpenCA::DBI::ERROR->{INSERT_FAILED} ); | ||
| 2028 | return undef; | ||
| 2029 | } | ||
| 2030 | |||
| 2031 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 2032 | return 1; | ||
| 2033 | |||
| 2034 | } | ||
| 2035 | |||
| 2036 | #################################### | ||
| 2037 | ## end of storeItem related stuff ## | ||
| 2038 | #################################### | ||
| 2039 | |||
| 2040 | ## ################################################################## | ||
| 2041 | ## Function Name: updateStatus | ||
| 2042 | ## ################################################################## | ||
| 2043 | |||
| 2044 | sub updateStatus { | ||
| 2045 | |||
| 2046 | my $self = shift; | ||
| 2047 | my $keys = { @_ }; | ||
| 2048 | |||
| 2049 | my $new_status = $keys->{STATUS}; | ||
| 2050 | my $new_datatype = $keys->{NEWTYPE}; | ||
| 2051 | my $item = $keys->{OBJECT}; | ||
| 2052 | |||
| 2053 | return undef if ( not $item ); | ||
| 2054 | |||
| 2055 | if ( not $new_status ) { | ||
| 2056 | if ( not $new_datatype ) { | ||
| 2057 | return undef; | ||
| 2058 | } | ||
| 2059 | if( $new_datatype =~ /^([^\_]+)\_/ ) { | ||
| 2060 | $new_status = $1; | ||
| 2061 | } else { | ||
| 2062 | return undef; | ||
| 2063 | } | ||
| 2064 | } | ||
| 2065 | |||
| 2066 | $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} ); | ||
| 2067 | $item->setStatus ( "$new_status"); | ||
| 2068 | |||
| 2069 | return $self->updateItem ( OBJECT => $item ); | ||
| 2070 | |||
| 2071 | } | ||
| 2072 | |||
| 2073 | ## ################################################################## | ||
| 2074 | ## Function Name: getItem | ||
| 2075 | ## ################################################################## | ||
| 2076 | |||
| 2077 | sub getItem { | ||
| 2078 | |||
| 2079 | ## Get an Item provided the exact data to match: | ||
| 2080 | ## DATATYPE, KEY. Will return, if exists, the data | ||
| 2081 | ## on the corresponding dB file. | ||
| 2082 | |||
| 2083 | ## Actually, as the search function, the returned | ||
| 2084 | ## value will be a referenced object (REQ, X509, | ||
| 2085 | ## CRL, etc... ). | ||
| 2086 | |||
| 2087 | my $self = shift; | ||
| 2088 | |||
| 2089 | $self->debug ("getItem: Entering sub getItem"); | ||
| 2090 | |||
| 2091 | $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} ); | ||
| 2092 | |||
| 2093 | my ( $fileName, $item, $body, $header, $hash, $tmpBody ); | ||
| 2094 | |||
| 2095 | my %arguments = $self->getArguments ( @_ ); | ||
| 2096 | return undef if (not %arguments); | ||
| 2097 | |||
| 2098 | my $query = undef; | ||
| 2099 | my @bind_values = (); | ||
| 2100 | my @bind_types = (); | ||
| 2101 | |||
| 2102 | ## support for direct access to latest CRL | ||
| 2103 | if ((not defined $arguments{KEY}) && ($arguments{TABLE} ne "CRL")) { | ||
| 2104 | $self->set_error ( $OpenCA::DBI::ERROR->{ MISSING_ARG_SERIAL } ); | ||
| 2105 | return undef; | ||
| 2106 | } | ||
| 2107 | |||
| 2108 | $self->debug ("getItem: data complete"); | ||
| 2109 | |||
| 2110 | ## I hope the people only search for Certs, Requests and CRRs | ||
| 2111 | ## mmh this is impossible | ||
| 2112 | $query = "select "; | ||
| 2113 | |||
| 2114 | my @cols = @{$OpenCA::DBI::SQL->{TABLE_STRUCTURE}->{$arguments{TABLE}}}; | ||
| 2115 | for (my $i = 0; $i <= $#cols; $i++) { | ||
| 2116 | $query .= $OpenCA::DBI::SQL->{VARIABLE}->{$cols[$i]}[0] . " "; | ||
| 2117 | $query .= ", " if ( $i < $#cols ); | ||
| 2118 | }; | ||
| 2119 | |||
| 2120 | $query .= " from ".$OpenCA::DBI::SQL->{TABLE}->{$arguments{TABLE}} . | ||
| 2121 | " where "; | ||
| 2122 | |||
| 2123 | if (defined $arguments{KEY}) { | ||
| 2124 | if ($arguments{TABLE} =~ /^CA_CERTIFICATE/i) { | ||
| 2125 | $query .= "(" . $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE} . | ||
| 2126 | "_SERIAL"}[0]." like ?)"; | ||
| 2127 | } else { | ||
| 2128 | $query .= "(" . $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE} . | ||
| 2129 | "_SERIAL"}[0]."=?)"; | ||
| 2130 | } | ||
| 2131 | push ( @bind_values, $arguments{KEY} ); | ||
| 2132 | push ( @bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[1]); | ||
| 2133 | |||
| 2134 | if (($arguments{STATUS} =~ /REVOKED|SUSPENDED/ ) and | ||
| 2135 | ($arguments{TABLE} =~ /^CERTIFICATE/i)) { | ||
| 2136 | $query .= " and (" . $OpenCA::DBI::SQL->{VARIABLE}->{STATUS}[0] . | ||
| 2137 | " like ?)"; | ||
| 2138 | push ( @bind_values, $arguments{STATUS} ); | ||
| 2139 | push ( @bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{STATUS}[1]); | ||
| 2140 | } | ||
| 2141 | } else { | ||
| 2142 | ## to support most actual CRL (you can use it find the last cert etc. too) | ||
| 2143 | $query .= $OpenCA::DBI::SQL->{VARIABLE}->{DATE}[0]." like (select MAX(" . | ||
| 2144 | $OpenCA::DBI::SQL->{VARIABLE}->{LAST_UPDATE}[0] . | ||
| 2145 | ") from " . $OpenCA::DBI::SQL->{TABLE}->{$arguments{TABLE}}.")"; | ||
| 2146 | undef @bind_values; | ||
| 2147 | } | ||
| 2148 | |||
| 2149 | # print STDERR "QUERY => $query\n"; | ||
| 2150 | # print STDERR "VALUES => @bind_values\n"; | ||
| 2151 | # print STDERR "VALUES => @bind_types\n"; | ||
| 2152 | |||
| 2153 | my ($rv); | ||
| 2154 | |||
| 2155 | ## do_query | ||
| 2156 | if ( not defined $self->doQuery (QUERY => $query, | ||
| 2157 | BIND_VALUES => \@bind_values, BIND_TYPES => \@bind_types) ) { | ||
| 2158 | $self->set_error ( $OpenCA::DBI::ERROR->{SELECT_FAILED} ); | ||
| 2159 | $self->debug_err ( "getItem: failedQuery: query: $query"); | ||
| 2160 | $self->debug_err ( "getItem: failedQuery: bind_values: @bind_values"); | ||
| 2161 | $self->debug_err ( "getItem: failedQuery: bind_types: @bind_types"); | ||
| 2162 | return undef; | ||
| 2163 | } | ||
| 2164 | |||
| 2165 | ## STH->rows does not work with Oracle (DB2 does not like rows sometimes too) | ||
| 2166 | my $arrayref = $self->{STH}->fetchrow_arrayref; | ||
| 2167 | if (not defined $arrayref or not $arrayref) { | ||
| 2168 | return undef; | ||
| 2169 | } | ||
| 2170 | |||
| 2171 | return $self->getResultItem (ARGUMENTS => \%arguments, | ||
| 2172 | ARRAYREF => $arrayref); | ||
| 2173 | |||
| 2174 | } | ||
| 2175 | |||
| 2176 | ## ################################################################## | ||
| 2177 | ## Function Name: getPrevItem | ||
| 2178 | ## ################################################################## | ||
| 2179 | |||
| 2180 | sub getPrevItem { | ||
| 2181 | |||
| 2182 | ## Get an Item provided the exact data to match: | ||
| 2183 | ## DATATYPE, KEY. Will return, if exists, the data | ||
| 2184 | ## on the corresponding dB file. | ||
| 2185 | |||
| 2186 | ## Actually, as the search function, the returned | ||
| 2187 | ## value will be a referenced object (REQ, X509, | ||
| 2188 | ## CRL, etc... ). | ||
| 2189 | |||
| 2190 | my $self = shift; | ||
| 2191 | |||
| 2192 | $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} ); | ||
| 2193 | |||
| 2194 | my %arguments = $self->getArguments ( @_ ); | ||
| 2195 | return undef if (not %arguments); | ||
| 2196 | |||
| 2197 | my $query = undef; | ||
| 2198 | my @bind_values = (); | ||
| 2199 | my @bind_types = (); | ||
| 2200 | |||
| 2201 | ## Let us make some needed check | ||
| 2202 | if (not $arguments{TABLE}) { | ||
| 2203 | $self->set_error ( $OpenCA::DBI::ERROR->{ MISSING_ARG_TABLE } ); | ||
| 2204 | return undef; | ||
| 2205 | } | ||
| 2206 | |||
| 2207 | # if ( not exists $arguments{KEY} ) { | ||
| 2208 | # if ($arguments{TABLE} eq "CA_CERTIFICATE" or | ||
| 2209 | # $arguments{TABLE} eq "CA") { | ||
| 2210 | # $arguments{KEY} = ""; | ||
| 2211 | # } else { | ||
| 2212 | # $arguments{KEY} = -1; | ||
| 2213 | # } | ||
| 2214 | # return undef; | ||
| 2215 | # } | ||
| 2216 | |||
| 2217 | my $and = 0; | ||
| 2218 | $query = "SELECT " . $OpenCA::DBI::SQL->{VARIABLE}->{ROWID}[0] . ", " . | ||
| 2219 | $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[0] . | ||
| 2220 | " from " . $OpenCA::DBI::SQL->{TABLE}->{$arguments{TABLE}}; | ||
| 2221 | |||
| 2222 | if (defined $arguments{KEY} and $arguments{KEY} ne "-1" ) { | ||
| 2223 | $query .= " where ". | ||
| 2224 | $OpenCA::DBI::SQL->{VARIABLE}->{ROWID}[0] . " < ( " . | ||
| 2225 | " select " . $OpenCA::DBI::SQL->{VARIABLE}->{ROWID}[0] . " from " . | ||
| 2226 | $OpenCA::DBI::SQL->{TABLE}->{$arguments{TABLE}} . " where " . | ||
| 2227 | $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[0] . | ||
| 2228 | " = ? ) "; | ||
| 2229 | push (@bind_values, $arguments{KEY}); | ||
| 2230 | push (@bind_types, | ||
| 2231 | $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[1]); | ||
| 2232 | $and = 1; | ||
| 2233 | } | ||
| 2234 | |||
| 2235 | $query .= " ORDER BY " . | ||
| 2236 | $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_ORDERBY"}[0] . | ||
| 2237 | " DESC"; | ||
| 2238 | |||
| 2239 | print STDERR "getPrevItem: query: $query\n"; | ||
| 2240 | print STDERR "getPrevItem: bind_values: @bind_values\n"; | ||
| 2241 | print STDERR "getPrevItem: bind_types: @bind_types\n"; | ||
| 2242 | |||
| 2243 | ## do_query | ||
| 2244 | if ( not defined $self->doQuery (QUERY => $query, | ||
| 2245 | BIND_VALUES => \@bind_values, BIND_TYPES => \@bind_types) ) { | ||
| 2246 | $self->set_error ( $OpenCA::DBI::ERROR->{SELECT_FAILED} ); | ||
| 2247 | return undef; | ||
| 2248 | } | ||
| 2249 | |||
| 2250 | my $ref = $self->{STH}->fetchrow_arrayref; | ||
| 2251 | |||
| 2252 | $self->debug_err ("getPrevItem: arg: " . $arguments{KEY}); | ||
| 2253 | $self->debug_err ("getPrevItem: result: " . $ref->[0]); | ||
| 2254 | $self->debug_err ("getPrevItem: result: " . $ref->[1]); | ||
| 2255 | |||
| 2256 | print STDERR "getPrevItem: arg: " . $arguments{KEY} . "\n"; | ||
| 2257 | print STDERR "getPrevItem: rawid: " . $ref->[0] . "\n"; | ||
| 2258 | print STDERR "getPrevItem: serial: " . $ref->[1] . "\n"; | ||
| 2259 | |||
| 2260 | if (defined $ref and exists $ref->[0] and $ref->[0] ne "") { | ||
| 2261 | return $self->getItem (DATATYPE => $arguments{TABLE}, | ||
| 2262 | KEY => $ref->[1], | ||
| 2263 | MODE => $arguments{MODE} | ||
| 2264 | ); | ||
| 2265 | } else { | ||
| 2266 | if ((defined $ref) or (not defined $self->{STH}->err)) { | ||
| 2267 | $self->debug ("getNextItem: there is no next item"); | ||
| 2268 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 2269 | return undef; ## no errors no results | ||
| 2270 | } else { # mmh this should never happen here | ||
| 2271 | $self->set_error ( $OpenCA::DBI::ERROR->{SELECT_FAILED} ); | ||
| 2272 | return undef; | ||
| 2273 | } | ||
| 2274 | } | ||
| 2275 | |||
| 2276 | ## never reached | ||
| 2277 | $self->set_error ( $OpenCA::DBI::ERROR->{UNEXPECTED_ERROR} ); | ||
| 2278 | return undef; | ||
| 2279 | } | ||
| 2280 | |||
| 2281 | ## ################################################################## | ||
| 2282 | ## Function Name: getNextItem | ||
| 2283 | ## ################################################################## | ||
| 2284 | |||
| 2285 | sub getNextItem { | ||
| 2286 | |||
| 2287 | ## Get an Item provided the exact data to match: | ||
| 2288 | ## DATATYPE, KEY. Will return, if exists, the data | ||
| 2289 | ## on the corresponding dB file. | ||
| 2290 | |||
| 2291 | ## Actually, as the search function, the returned | ||
| 2292 | ## value will be a referenced object (REQ, X509, | ||
| 2293 | ## CRL, etc... ). | ||
| 2294 | |||
| 2295 | my $self = shift; | ||
| 2296 | |||
| 2297 | $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} ); | ||
| 2298 | |||
| 2299 | my %arguments = $self->getArguments ( @_ ); | ||
| 2300 | return undef if (not %arguments); | ||
| 2301 | |||
| 2302 | my $query = undef; | ||
| 2303 | my $rowid = -1; | ||
| 2304 | my @bind_values = (); | ||
| 2305 | my @bind_types = (); | ||
| 2306 | |||
| 2307 | ## Let us make some needed check | ||
| 2308 | if (not $arguments{TABLE}) { | ||
| 2309 | $self->set_error ( $OpenCA::DBI::ERROR->{ MISSING_ARG_TABLE } ); | ||
| 2310 | return undef; | ||
| 2311 | } | ||
| 2312 | |||
| 2313 | # if ( $arguments{KEY} ) { | ||
| 2314 | # $query = "select ". $OpenCA::DBI::SQL->{VARIABLE}->{ROWID}[0] . ", " . | ||
| 2315 | # $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[0] . | ||
| 2316 | # " from " . $OpenCA::DBI::SQL->{TABLE}->{$arguments{TABLE}} . | ||
| 2317 | # " where " . | ||
| 2318 | # $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[0] . | ||
| 2319 | # " = ?"; | ||
| 2320 | # push(@bind_values, $arguments{KEY}); | ||
| 2321 | # push(@bind_types, | ||
| 2322 | ## $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[1]); | ||
| 2323 | # | ||
| 2324 | # ## do_query | ||
| 2325 | # if ( not defined $self->doQuery (QUERY => $query, | ||
| 2326 | # BIND_VALUES => \@bind_values, BIND_TYPES => \@bind_types) ) { | ||
| 2327 | ## $self->set_error ( $OpenCA::DBI::ERROR->{SELECT_FAILED} ); | ||
| 2328 | # return undef; | ||
| 2329 | # } | ||
| 2330 | # my $ref = $self->{STH}->fetchrow_arrayref; | ||
| 2331 | |||
| 2332 | $self->debug_err ("getNextItem: arg: " . $arguments{KEY}); | ||
| 2333 | |||
| 2334 | # } | ||
| 2335 | |||
| 2336 | ## I hope the people only search for Certs, Requests and CRRs | ||
| 2337 | ## mmh this is impossible | ||
| 2338 | my $and = 0; | ||
| 2339 | $query = "select ". $OpenCA::DBI::SQL->{VARIABLE}->{ROWID}[0] . ", " . | ||
| 2340 | $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[0] . | ||
| 2341 | " from " . $OpenCA::DBI::SQL->{TABLE}->{$arguments{TABLE}}; | ||
| 2342 | |||
| 2343 | if (defined $arguments{KEY} and $arguments{KEY} ne "-1" ) { | ||
| 2344 | $query .= " where ". | ||
| 2345 | $OpenCA::DBI::SQL->{VARIABLE}->{ROWID}[0] . " > ( " . | ||
| 2346 | " select " . $OpenCA::DBI::SQL->{VARIABLE}->{ROWID}[0] . " from " . | ||
| 2347 | $OpenCA::DBI::SQL->{TABLE}->{$arguments{TABLE}} . " where " . | ||
| 2348 | $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[0] . | ||
| 2349 | " = ? )"; | ||
| 2350 | push (@bind_values, $arguments{KEY}); | ||
| 2351 | push (@bind_types, | ||
| 2352 | $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[1]); | ||
| 2353 | $and = 1; | ||
| 2354 | } | ||
| 2355 | |||
| 2356 | $query .= " ORDER BY " . | ||
| 2357 | $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_ORDERBY"}[0]; | ||
| 2358 | |||
| 2359 | ## do_query | ||
| 2360 | if ( not defined $self->doQuery (QUERY => $query, | ||
| 2361 | BIND_VALUES => \@bind_values, BIND_TYPES => \@bind_types) ) { | ||
| 2362 | $self->set_error ( $OpenCA::DBI::ERROR->{SELECT_FAILED} ); | ||
| 2363 | return undef; | ||
| 2364 | } | ||
| 2365 | my $ref = $self->{STH}->fetchrow_arrayref; | ||
| 2366 | |||
| 2367 | $self->debug_err ("getNextItem: arg: " . $arguments{KEY}); | ||
| 2368 | $self->debug_err ("getNextItem: result: " . $ref->[0]); | ||
| 2369 | $self->debug_err ("getNextItem: result: " . $ref->[1]); | ||
| 2370 | |||
| 2371 | if (defined $ref and exists $ref->[0] and $ref->[0] ne "") { | ||
| 2372 | return $self->getItem (DATATYPE => $arguments{TABLE}, | ||
| 2373 | KEY => $ref->[1], | ||
| 2374 | MODE => $arguments{MODE} | ||
| 2375 | ); | ||
| 2376 | } else { | ||
| 2377 | if (defined $ref or | ||
| 2378 | not defined $self->{STH}->err) { | ||
| 2379 | $self->debug ("getNextItem: there is no next item"); | ||
| 2380 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 2381 | return undef; ## no errors no results | ||
| 2382 | } else { # mmh this should never happen here | ||
| 2383 | $self->set_error ( $OpenCA::DBI::ERROR->{SELECT_FAILED} ); | ||
| 2384 | return undef; | ||
| 2385 | } | ||
| 2386 | } | ||
| 2387 | |||
| 2388 | ## never reached | ||
| 2389 | $self->set_error ( $OpenCA::DBI::ERROR->{UNEXPECTED_ERROR} ); | ||
| 2390 | return undef; | ||
| 2391 | |||
| 2392 | } | ||
| 2393 | |||
| 2394 | ## ################################################################## | ||
| 2395 | ## Function Name: deleteItem | ||
| 2396 | ## ################################################################## | ||
| 2397 | |||
| 2398 | sub deleteItem { | ||
| 2399 | ## it is not neccessary to delete an object if it is revoked/marked | ||
| 2400 | ## as deleted | ||
| 2401 | return 1; | ||
| 2402 | } | ||
| 2403 | |||
| 2404 | ## ################################################################## | ||
| 2405 | ## Function Name: destroyItem | ||
| 2406 | ## ################################################################## | ||
| 2407 | |||
| 2408 | sub destroyItem { | ||
| 2409 | ## attention this code is not for normal use only for recovery reasons ! | ||
| 2410 | ## if you want to say a request is deleted than storeItem with | ||
| 2411 | ## STATUS = $OpenCA::DBI::status->{DELETED} | ||
| 2412 | |||
| 2413 | ## Get an Item provided the exact data to match: | ||
| 2414 | ## DATATYPE, KEY. Will return, if exists, the data | ||
| 2415 | ## on the corresponding dB file. | ||
| 2416 | |||
| 2417 | ## Actually, as the search function, the returned | ||
| 2418 | ## value will be a referenced object (REQ, X509, | ||
| 2419 | ## CRL, etc... ). | ||
| 2420 | |||
| 2421 | my $self = shift; | ||
| 2422 | my $keys = { @_ }; | ||
| 2423 | |||
| 2424 | $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} ); | ||
| 2425 | |||
| 2426 | my $type = $keys->{DATATYPE}; | ||
| 2427 | my $table = $self->getTable ($type); | ||
| 2428 | |||
| 2429 | my $serial = $keys->{KEY}; ## Key passed when stored item | ||
| 2430 | |||
| 2431 | my $query = undef; | ||
| 2432 | my @bind_values = (); | ||
| 2433 | my @bind_types = (); | ||
| 2434 | |||
| 2435 | ## Let us make some needed check | ||
| 2436 | if (not $table) { | ||
| 2437 | $self->set_error ( $OpenCA::DBI::ERROR->{MISSING_ARG_TABLE} ); | ||
| 2438 | return undef; | ||
| 2439 | } | ||
| 2440 | if (not $serial and ($serial != 0)) { | ||
| 2441 | $self->set_error ( $OpenCA::DBI::ERROR->{MISSING_ARG_SERIAL} ); | ||
| 2442 | return undef; | ||
| 2443 | } | ||
| 2444 | |||
| 2445 | ## I hope the people only search for Certs, Requests and CRRs | ||
| 2446 | ## mmh this is impossible | ||
| 2447 | ## Attention date is not numeric !!! | ||
| 2448 | $query = "delete from ".$OpenCA::DBI::SQL->{TABLE}->{$table}." where ". | ||
| 2449 | $OpenCA::DBI::SQL->{VARIABLE}->{$table."_SERIAL"}[0]."= ? ". | ||
| 2450 | push ( @bind_values, $serial); | ||
| 2451 | push ( @bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{$table."_SERIAL"}[1]); | ||
| 2452 | |||
| 2453 | ## do_query | ||
| 2454 | if ( not defined $self->doQuery (QUERY => $query, | ||
| 2455 | BIND_VALUES => \@bind_values, BIND_TYPES => \@bind_types) ) { | ||
| 2456 | $self->set_error ( $OpenCA::DBI::ERROR->{DELETE_FAILED} ); | ||
| 2457 | return undef; | ||
| 2458 | } | ||
| 2459 | |||
| 2460 | ## successful | ||
| 2461 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 2462 | return 1; | ||
| 2463 | |||
| 2464 | } | ||
| 2465 | |||
| 2466 | |||
| 2467 | ## ################################################################## | ||
| 2468 | ## Function Name: Search Items | ||
| 2469 | ## ################################################################## | ||
| 2470 | |||
| 2471 | sub elements { | ||
| 2472 | |||
| 2473 | ## Get an Item provided the exact data to match: | ||
| 2474 | ## DATATYPE, KEY. Will return, if exists, the data | ||
| 2475 | ## on the corresponding dB file. | ||
| 2476 | |||
| 2477 | ## Actually, as the search function, the returned | ||
| 2478 | ## value will be a referenced object (REQ, X509, | ||
| 2479 | ## CRL, etc... ). | ||
| 2480 | |||
| 2481 | my $self = shift; | ||
| 2482 | return $self->searchItems( MODE=>"COUNT", @_ ); | ||
| 2483 | } | ||
| 2484 | |||
| 2485 | ## ################################################################## | ||
| 2486 | ## Function Name: Search Items | ||
| 2487 | ## ################################################################## | ||
| 2488 | |||
| 2489 | sub searchItems { # new one !!! | ||
| 2490 | |||
| 2491 | ## Get an Item provided the exact data to match: | ||
| 2492 | ## DATATYPE, KEY. Will return, if exists, the data | ||
| 2493 | ## on the corresponding dB file. | ||
| 2494 | |||
| 2495 | ## Actually, as the search function, the returned | ||
| 2496 | ## value will be a referenced object (REQ, X509, | ||
| 2497 | ## CRL, etc... ). | ||
| 2498 | |||
| 2499 | my $self = shift; | ||
| 2500 | |||
| 2501 | my $keys = { @_ }; | ||
| 2502 | |||
| 2503 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 2504 | |||
| 2505 | my (@retList, @objRetList); | ||
| 2506 | my ( $items ); | ||
| 2507 | |||
| 2508 | $self->debug ("searchItems: Entering function searchItems"); | ||
| 2509 | $self->debug ("searchItems: OpenCA::DBI::errno: $errno"); | ||
| 2510 | |||
| 2511 | my %arguments = $self->getArguments ( @_ ); | ||
| 2512 | |||
| 2513 | $self->debug ("searchItems: OpenCA::DBI::errno: $errno"); | ||
| 2514 | |||
| 2515 | return undef if ($errno); | ||
| 2516 | |||
| 2517 | $self->debug ("searchItems: dbi-status:".$arguments {STATUS}); | ||
| 2518 | |||
| 2519 | my $query = undef; | ||
| 2520 | my @bind_values = (); | ||
| 2521 | my @bind_types = (); | ||
| 2522 | |||
| 2523 | ## Let us make some needed check | ||
| 2524 | if (not $arguments {TABLE}) { | ||
| 2525 | $self->set_error ( $OpenCA::DBI::ERROR->{MISSING_ARG_TABLE} ); | ||
| 2526 | return undef; | ||
| 2527 | } | ||
| 2528 | |||
| 2529 | ## let us prepare the question | ||
| 2530 | my $and = 0; | ||
| 2531 | my $mode = "*"; | ||
| 2532 | my $today = gmtime; | ||
| 2533 | my $now = $self->{backend}->getNumericDate( "$today" ); | ||
| 2534 | |||
| 2535 | if ( $arguments { MODE } =~ /ROWS|COUNT/ ) { | ||
| 2536 | $mode = "count(*)"; | ||
| 2537 | } elsif ( $arguments { MODE } =~ /KEYLIST/ ) { | ||
| 2538 | $mode = $OpenCA::DBI::SQL->{VARIABLE}->{$arguments {TABLE}. | ||
| 2539 | "_SERIAL"}[0]; | ||
| 2540 | } else { | ||
| 2541 | $mode = '*'; | ||
| 2542 | } | ||
| 2543 | |||
| 2544 | $query = "select $mode from " . | ||
| 2545 | $OpenCA::DBI::SQL->{TABLE}->{$arguments {TABLE}}; | ||
| 2546 | |||
| 2547 | ## check for unique identifier scan | ||
| 2548 | if ( $arguments{KEY} and ($arguments {TABLE} =~ /CERTIFICATE/ ) ) { | ||
| 2549 | if ($and) { | ||
| 2550 | $query .= " and "; | ||
| 2551 | } else { | ||
| 2552 | $query .= " where "; | ||
| 2553 | $and = 1; | ||
| 2554 | } | ||
| 2555 | if ( $arguments {TABLE} =~ /CA_CERTIFICATE/ ) { | ||
| 2556 | $query .= "(".$OpenCA::DBI::SQL->{VARIABLE}->{CA_CERTIFICATE_SERIAL}[0]."=?)"; | ||
| 2557 | } else { | ||
| 2558 | $query .= "(".$OpenCA::DBI::SQL->{VARIABLE}->{CERTIFICATE_SERIAL}[0]."=?)"; | ||
| 2559 | } | ||
| 2560 | |||
| 2561 | ## prepare bind_values | ||
| 2562 | push ( @bind_values, $arguments{KEY}); | ||
| 2563 | push ( @bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{CERTIFICATE_SERIAL}[1]); | ||
| 2564 | ## delete from keys | ||
| 2565 | delete $arguments{KEY}; | ||
| 2566 | } | ||
| 2567 | |||
| 2568 | if ( $arguments {EXPIRES_BEFORE} or $arguments {EXPIRES_AFTER} ) { | ||
| 2569 | delete $arguments { STATUS }; | ||
| 2570 | } else { | ||
| 2571 | if ( $arguments { STATUS } =~ /EXPIRED/i ) { | ||
| 2572 | $arguments { EXPIRES_BEFORE } = $now; | ||
| 2573 | delete $arguments { STATUS }; | ||
| 2574 | } elsif ( $arguments { STATUS } =~ /VALID/ ) { | ||
| 2575 | $arguments { EXPIRES_AFTER } = $now; | ||
| 2576 | delete $arguments { STATUS }; | ||
| 2577 | } | ||
| 2578 | } | ||
| 2579 | |||
| 2580 | $self->debug(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<\n"); | ||
| 2581 | $self->debug("EXPIRES_BEFORE => " . $arguments {EXPIRES_BEFORE} . "\n"); | ||
| 2582 | $self->debug("EXPIRES_AFTER => " . $arguments {EXPIRES_AFTER} . "\n"); | ||
| 2583 | $self->debug("TABLE => " . $arguments {TABLE} . "\n"); | ||
| 2584 | $self->debug(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<"); | ||
| 2585 | |||
| 2586 | if ( $arguments {EXPIRES_BEFORE} and | ||
| 2587 | ($arguments {TABLE} =~ /CERTIFICATE/ ) ) { | ||
| 2588 | if ($and) { | ||
| 2589 | $query .= " and "; | ||
| 2590 | } else { | ||
| 2591 | $query .= " where "; | ||
| 2592 | $and = 1; | ||
| 2593 | } | ||
| 2594 | $query .= "(".$OpenCA::DBI::SQL->{VARIABLE}->{NOTAFTER}[0]." < ? )"; | ||
| 2595 | ## prepare bind_values | ||
| 2596 | push( @bind_values, $arguments{EXPIRES_BEFORE} ); | ||
| 2597 | push( @bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{NOTAFTER}[1]); | ||
| 2598 | ## delete from keys | ||
| 2599 | delete $arguments{EXPIRES_BEFORE}; | ||
| 2600 | } | ||
| 2601 | |||
| 2602 | if ( $arguments {EXPIRES_AFTER} and | ||
| 2603 | ($arguments {TABLE} =~ /CERTIFICATE/ ) ) { | ||
| 2604 | if ($and) { | ||
| 2605 | $query .= " and "; | ||
| 2606 | } else { | ||
| 2607 | $query .= " where "; | ||
| 2608 | $and = 1; | ||
| 2609 | } | ||
| 2610 | $query .= "(".$OpenCA::DBI::SQL->{VARIABLE}->{NOTAFTER}[0]." > ? )"; | ||
| 2611 | ## prepare bind_values | ||
| 2612 | push ( @bind_values, $arguments{EXPIRES_AFTER} ); | ||
| 2613 | push ( @bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{NOTAFTER}[1]); | ||
| 2614 | ## delete from keys | ||
| 2615 | delete $arguments{EXPIRES_AFTER}; | ||
| 2616 | } | ||
| 2617 | |||
| 2618 | if ( $arguments {EXPIRES_BEFORE} and | ||
| 2619 | ($arguments {TABLE} =~ /^CRL/ ) ) { | ||
| 2620 | if ($and) { | ||
| 2621 | $query .= " and "; | ||
| 2622 | } else { | ||
| 2623 | $query .= " where "; | ||
| 2624 | $and = 1; | ||
| 2625 | } | ||
| 2626 | $query .= "(".$OpenCA::DBI::SQL->{VARIABLE}->{NEXT_UPDATE}[0]." < ? )"; | ||
| 2627 | ## prepare bind_values | ||
| 2628 | push ( @bind_values, $arguments{EXPIRES_BEFORE} ); | ||
| 2629 | push ( @bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{NEXT_UPDATE}[1]); | ||
| 2630 | ## delete from keys | ||
| 2631 | delete $arguments{EXPIRES_BEFORE}; | ||
| 2632 | } | ||
| 2633 | |||
| 2634 | if ( $arguments {EXPIRES_AFTER} and | ||
| 2635 | ($arguments {TABLE} =~ /^CRL/ ) ) { | ||
| 2636 | if ($and) { | ||
| 2637 | $query .= " and "; | ||
| 2638 | } else { | ||
| 2639 | $query .= " where "; | ||
| 2640 | $and = 1; | ||
| 2641 | } | ||
| 2642 | $query .= "(".$OpenCA::DBI::SQL->{VARIABLE}->{NEXT_UPDATE}[0]." > ? )"; | ||
| 2643 | ## prepare bind_values | ||
| 2644 | push ( @bind_values, $arguments{EXPIRES_AFTER} ); | ||
| 2645 | push ( @bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{NEXT_UPDATE}[1]); | ||
| 2646 | ## delete from keys | ||
| 2647 | delete $arguments{EXPIRES_AFTER}; | ||
| 2648 | } | ||
| 2649 | |||
| 2650 | if ( $arguments{FROM} ) { | ||
| 2651 | |||
| 2652 | if ($and) { | ||
| 2653 | $query .= " and "; | ||
| 2654 | } else { | ||
| 2655 | $query .= " where "; | ||
| 2656 | $and = 1; | ||
| 2657 | } | ||
| 2658 | $query .= "(".$OpenCA::DBI::SQL->{VARIABLE}->{ROWID}[0]." >= ? ) "; | ||
| 2659 | push ( @bind_values, $arguments{FROM} ); | ||
| 2660 | push ( @bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{ROWID}[1]); | ||
| 2661 | delete $arguments{FROM}; | ||
| 2662 | } | ||
| 2663 | |||
| 2664 | if ( $arguments { ITEMS } ) { | ||
| 2665 | $items = $arguments{ITEMS}; | ||
| 2666 | delete $arguments{ITEMS}; | ||
| 2667 | } | ||
| 2668 | |||
| 2669 | $self->debug ("searchItems: query now: $query"); | ||
| 2670 | |||
| 2671 | ## For every keyword let us get the list of values | ||
| 2672 | my @attributes = $self->getSearchAttributes (DATATYPE=>$arguments {TABLE}); | ||
| 2673 | |||
| 2674 | my $attr; | ||
| 2675 | my $array = 0; | ||
| 2676 | my @arrayVal = (); | ||
| 2677 | |||
| 2678 | for $attr ( @attributes ) { | ||
| 2679 | $self->debug ("searchItems: scan attribute: $attr"); | ||
| 2680 | |||
| 2681 | if ($arguments {$attr}) { | ||
| 2682 | $self->debug ("searchItems: attribute's content: ".$arguments {$attr}); | ||
| 2683 | |||
| 2684 | ## get from keys | ||
| 2685 | if ($and) { | ||
| 2686 | $query .= " and "; | ||
| 2687 | } else { | ||
| 2688 | $query .= " where "; | ||
| 2689 | $and = 1; | ||
| 2690 | } | ||
| 2691 | if ( ref($arguments {$attr}) =~ /ARRAY/ ) { | ||
| 2692 | $self->debug ( "ARGUMENT IS AN ARRAY!!!\n"); | ||
| 2693 | $array = 1; | ||
| 2694 | @arrayVal = @{ $arguments{$attr}}; | ||
| 2695 | } else { | ||
| 2696 | $array = 0; | ||
| 2697 | @arrayVal = ( $arguments{$attr} ); | ||
| 2698 | } | ||
| 2699 | |||
| 2700 | if ($attr =~ /^KEY$/) { | ||
| 2701 | $query .= "(". | ||
| 2702 | $OpenCA::DBI::SQL->{VARIABLE}->{$arguments {TABLE}."_SERIAL"}[0]; | ||
| 2703 | |||
| 2704 | if( $array eq "1" ) { | ||
| 2705 | $query .= " in ( "; | ||
| 2706 | foreach my $tmpVal ( @arrayVal ) { | ||
| 2707 | $query .= " ? ,"; | ||
| 2708 | push (@bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[1]); | ||
| 2709 | } | ||
| 2710 | $query =~ s/,$//; | ||
| 2711 | $query .= " ) "; | ||
| 2712 | } else { | ||
| 2713 | $query .= "= ? "; | ||
| 2714 | push (@bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[1]); | ||
| 2715 | } | ||
| 2716 | $query .= ") "; | ||
| 2717 | |||
| 2718 | } elsif ($OpenCA::DBI::SQL->{VARIABLE}->{$attr}[1] =~ /BIGINT/i) { | ||
| 2719 | $self->debug ("searchItems: BIGINT: ". | ||
| 2720 | $attr." --> ". | ||
| 2721 | $OpenCA::DBI::SQL->{VARIABLE}->{$attr}[1]); | ||
| 2722 | $query .= "( ".$OpenCA::DBI::SQL->{VARIABLE}->{$attr}[0]; | ||
| 2723 | |||
| 2724 | if( $array eq "1" ) { | ||
| 2725 | $query .= " in ( "; | ||
| 2726 | foreach my $tmpVal ( @arrayVal ) { | ||
| 2727 | $query .= " ? ,"; | ||
| 2728 | push (@bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{$attr}[1]); | ||
| 2729 | } | ||
| 2730 | $query =~ s/,$//; | ||
| 2731 | $query .= " ) "; | ||
| 2732 | } else { | ||
| 2733 | $query .= " = ? "; | ||
| 2734 | push (@bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{$attr}[1]); | ||
| 2735 | } | ||
| 2736 | |||
| 2737 | $query .= ") "; | ||
| 2738 | |||
| 2739 | } else { | ||
| 2740 | $self->debug ("searchItems: TEXT: ". | ||
| 2741 | $attr." --> ". | ||
| 2742 | $OpenCA::DBI::SQL->{VARIABLE}->{$attr}[1]); | ||
| 2743 | |||
| 2744 | $query .= "(".$OpenCA::DBI::SQL->{VARIABLE}->{$attr}[0]; | ||
| 2745 | |||
| 2746 | if( $array eq "1" ) { | ||
| 2747 | $query .= " in ( "; | ||
| 2748 | foreach my $tmpVal ( @arrayVal ) { | ||
| 2749 | $query .= " ? ,"; | ||
| 2750 | push (@bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{$attr}[1]); | ||
| 2751 | } | ||
| 2752 | $query =~ s/,$//; | ||
| 2753 | $query .= " ) "; | ||
| 2754 | } else { | ||
| 2755 | $query .= " like ? "; | ||
| 2756 | push (@bind_types, $OpenCA::DBI::SQL->{VARIABLE}->{$attr}[1]); | ||
| 2757 | } | ||
| 2758 | |||
| 2759 | $query .= ") "; | ||
| 2760 | |||
| 2761 | } | ||
| 2762 | |||
| 2763 | if ( $array eq "1" ) { | ||
| 2764 | push ( @bind_values, @arrayVal ); | ||
| 2765 | } else { | ||
| 2766 | ## prepare bind_values | ||
| 2767 | $bind_values [scalar (@bind_values)] = $arguments {$attr}; | ||
| 2768 | }; | ||
| 2769 | |||
| 2770 | ## delete from keys | ||
| 2771 | delete $arguments{$attr}; | ||
| 2772 | } | ||
| 2773 | } | ||
| 2774 | |||
| 2775 | if ( $mode ne "count(*)" ) { | ||
| 2776 | ## order by key to support correct listings | ||
| 2777 | if ( $arguments{ORDERBY} ) { | ||
| 2778 | $query .= " ORDER BY " . | ||
| 2779 | $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{ORDERBY}}[0]; | ||
| 2780 | } else { | ||
| 2781 | $query.= " order by ". | ||
| 2782 | $OpenCA::DBI::SQL->{VARIABLE}->{$arguments {TABLE}."_ORDERBY"}[0]; | ||
| 2783 | } | ||
| 2784 | } | ||
| 2785 | delete $arguments { ORDERBY }; | ||
| 2786 | |||
| 2787 | # Limit the results!!!! | ||
| 2788 | if ( $items ) { | ||
| 2789 | my $hquery = $OpenCA::DBI::DB->{$self->{DB_Type}}->{LIMIT}; | ||
| 2790 | |||
| 2791 | $items = -1 if ( not $items ); | ||
| 2792 | |||
| 2793 | $hquery =~ s/__QUERY__/$query/; | ||
| 2794 | $hquery =~ s/__MAXITEMS__/$items/; | ||
| 2795 | $query = $hquery; | ||
| 2796 | } | ||
| 2797 | |||
| 2798 | my $rv = 0; | ||
| 2799 | |||
| 2800 | $self->debug_err ( "searchItems: query now: $query", | ||
| 2801 | "searchItems: arguments: @bind_values" ); | ||
| 2802 | |||
| 2803 | #print STDERR "QUERY => $query\n"; | ||
| 2804 | #print STDERR "VALUE => @bind_values\n"; | ||
| 2805 | |||
| 2806 | ## do_query | ||
| 2807 | $rv = $self->doQuery (QUERY => $query, BIND_VALUES => \@bind_values, BIND_TYPES => \@bind_types); | ||
| 2808 | if (not defined $rv ) { | ||
| 2809 | $self->set_error ( $OpenCA::DBI::ERROR->{SELECT_FAILED} ); | ||
| 2810 | return undef; | ||
| 2811 | } else { | ||
| 2812 | |||
| 2813 | $self->debug ("searchItems: errstr(undef is OK): ". | ||
| 2814 | $self->{STH}->errstr()); | ||
| 2815 | $self->debug ("searchItems: rows (this is buggy in DBD::DB2 and " . | ||
| 2816 | "DBD::Oracle)): ". $self->{STH}->rows); | ||
| 2817 | |||
| 2818 | ## Results | ||
| 2819 | ## be warned fetchrow_hashref does not work with DB2 | ||
| 2820 | @retList = (); | ||
| 2821 | while ( (my $h = $self->{STH}->fetchrow_arrayref) ) { | ||
| 2822 | # $counter++; | ||
| 2823 | |||
| 2824 | $self->debug ("searchItems: item: ".$h->[0]); | ||
| 2825 | push ( @retList, $h->[0] ); | ||
| 2826 | } | ||
| 2827 | |||
| 2828 | ## because of searchItemDB + searchItem | ||
| 2829 | ## but what it is doing ? | ||
| 2830 | if( $arguments {MODE} =~ /ROWS|COUNT/i ) { | ||
| 2831 | $self->debug ("searchItems: leaving function successfully (mode $mode)"); | ||
| 2832 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 2833 | return ($retList[0]); | ||
| 2834 | } elsif ( $arguments{MODE} =~ /KEYLIST/i ) { | ||
| 2835 | $self->debug ("searchItems: leaving function successfully"); | ||
| 2836 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 2837 | return ( @retList ); | ||
| 2838 | } | ||
| 2839 | |||
| 2840 | for my $i (@retList) { | ||
| 2841 | my $obj; | ||
| 2842 | |||
| 2843 | if ( not $obj = $self->getItem( DATATYPE => $arguments{TABLE}, | ||
| 2844 | STATUS => $arguments{STATUS}, | ||
| 2845 | KEY => $i )) { | ||
| 2846 | $self->debug_err ( "searchItems: error creating object " . | ||
| 2847 | $arguments{TABLE} . "::" . $arguments{STATUS} . | ||
| 2848 | "::" . $i); | ||
| 2849 | } | ||
| 2850 | |||
| 2851 | $self->debug ("searchItems: add an object to the returnlist"); | ||
| 2852 | push( @objRetList, $obj ); | ||
| 2853 | } | ||
| 2854 | |||
| 2855 | $self->debug ("searchItems: leaving function successfully"); | ||
| 2856 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 2857 | return @objRetList; | ||
| 2858 | |||
| 2859 | } | ||
| 2860 | |||
| 2861 | ## never reached | ||
| 2862 | $self->set_error ( $OpenCA::DBI::ERROR->{UNEXPECTED_ERROR} ); | ||
| 2863 | return undef; | ||
| 2864 | |||
| 2865 | } | ||
| 2866 | |||
| 2867 | ## ################################################################## | ||
| 2868 | ## Function Name: list Items | ||
| 2869 | ## ################################################################## | ||
| 2870 | |||
| 2871 | sub listItems { | ||
| 2872 | my $self = shift; | ||
| 2873 | return $self->searchItems ( @_ ); | ||
| 2874 | } | ||
| 2875 | |||
| 2876 | ################################################## | ||
| 2877 | ## original unchanged functions from OpenCA::DB ## | ||
| 2878 | ################################################## | ||
| 2879 | |||
| 2880 | sub rows { | ||
| 2881 | |||
| 2882 | ## Returns the number of item matching the request. You can search | ||
| 2883 | ## for generic DATATYPE such as CERTIFICATE|REQUEST|CRL | ||
| 2884 | ## or restricted type (EXPIRED_CERTIFICATE|REVOKED_CERTIFICATE| | ||
| 2885 | ## VALID_CERTIFICATE... | ||
| 2886 | ## | ||
| 2887 | ## This function should be used in conjunction with searching function | ||
| 2888 | ## use the elements sub instead if you wish to know how many specific | ||
| 2889 | ## dB elements are there (such as VALID_CERTIFICATES, etc ... ) | ||
| 2890 | |||
| 2891 | my $self = shift; | ||
| 2892 | my $keys = { @_ }; | ||
| 2893 | |||
| 2894 | return $self->searchItems( MODE=>"ROWS", @_ ); | ||
| 2895 | } | ||
| 2896 | |||
| 2897 | ## ################################################################## | ||
| 2898 | ## Function Name: getSearchAttributes | ||
| 2899 | ## ################################################################## | ||
| 2900 | |||
| 2901 | sub getSearchAttributes { | ||
| 2902 | |||
| 2903 | ## new extended function for getSearchAttributes which does not | ||
| 2904 | ## return the index (SERIAL, KEY or DATE) | ||
| 2905 | |||
| 2906 | my $self = shift; | ||
| 2907 | my $keys = { @_ }; | ||
| 2908 | |||
| 2909 | $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} ); | ||
| 2910 | |||
| 2911 | my $type = $keys->{DATATYPE}; | ||
| 2912 | my @ret = (); | ||
| 2913 | |||
| 2914 | if ( not $type ) { | ||
| 2915 | $self->set_error ( $OpenCA::DBI::ERROR->{MISSING_ARG_DATATYPE} ); | ||
| 2916 | return undef; | ||
| 2917 | } | ||
| 2918 | |||
| 2919 | if ( $type =~ /REQUEST/ ) { | ||
| 2920 | @ret = ( "KEY", | ||
| 2921 | "STATUS", | ||
| 2922 | "DN", | ||
| 2923 | "CN", | ||
| 2924 | "EMAIL", | ||
| 2925 | "RA", | ||
| 2926 | "OPERATOR", | ||
| 2927 | "ROLE", | ||
| 2928 | "PUBKEY", | ||
| 2929 | "SCEP_TID", | ||
| 2930 | "NOTBEFORE", | ||
| 2931 | "NOTAFTER", | ||
| 2932 | "APPROVED_AFTER", | ||
| 2933 | "ARCHIVED_AFTER", | ||
| 2934 | "DELETED_AFTER", | ||
| 2935 | "ROWID", | ||
| 2936 | "LOA" ); | ||
| 2937 | } elsif ( $type =~ /CA_CERTIFICATE/ ) { | ||
| 2938 | @ret = ( "KEY", | ||
| 2939 | "STATUS", | ||
| 2940 | "EXPIRES_BEFORE", | ||
| 2941 | "EXPIRES_AFTER", | ||
| 2942 | "NOTBEFORE", | ||
| 2943 | "NOTAFTER", | ||
| 2944 | "SUSPENDED_AFTER", | ||
| 2945 | "REVOKED_AFTER", | ||
| 2946 | "INVALIDITY_REASON", | ||
| 2947 | "DN", | ||
| 2948 | "CN", | ||
| 2949 | "EMAIL", | ||
| 2950 | "ROWID", | ||
| 2951 | "PUBKEY" ); | ||
| 2952 | } elsif ( $type =~ /CERTIFICATE/ ) { | ||
| 2953 | @ret = ( "KEY", | ||
| 2954 | "STATUS", | ||
| 2955 | "DN", | ||
| 2956 | "CN", | ||
| 2957 | "EMAIL", | ||
| 2958 | "ROLE", | ||
| 2959 | "PUBKEY", | ||
| 2960 | "CSR_SERIAL", | ||
| 2961 | "EXPIRES_BEFORE", | ||
| 2962 | "EXPIRES_AFTER", | ||
| 2963 | "NOTBEFORE", | ||
| 2964 | "NOTAFTER", | ||
| 2965 | "SUSPENDED_AFTER", | ||
| 2966 | "REVOKED_AFTER", | ||
| 2967 | "INVALIDITY_REASON", | ||
| 2968 | "ROWID", | ||
| 2969 | "LOA" ); | ||
| 2970 | } elsif ( $type =~ /CRR/ ) { | ||
| 2971 | @ret = ( "KEY", | ||
| 2972 | "STATUS", | ||
| 2973 | "REVOKE_CERTIFICATE_SERIAL", | ||
| 2974 | "REVOKE_CERTIFICATE_DN", | ||
| 2975 | "CN", | ||
| 2976 | "EMAIL", | ||
| 2977 | "RA", | ||
| 2978 | "OPERATOR", | ||
| 2979 | "SUBMIT_DATE", | ||
| 2980 | "REASON", | ||
| 2981 | "NOTBEFORE", | ||
| 2982 | "NOTAFTER", | ||
| 2983 | "APPROVED_AFTER", | ||
| 2984 | "ARCHIVED_AFTER", | ||
| 2985 | "DELETED_AFTER", | ||
| 2986 | "LOA" ); | ||
| 2987 | } elsif ( $type =~ /CRL/ ) { | ||
| 2988 | @ret = ( "KEY", | ||
| 2989 | "STATUS", | ||
| 2990 | "EXPIRES_BEFORE", | ||
| 2991 | "EXPIRES_AFTER", | ||
| 2992 | "LAST_UPDATE", | ||
| 2993 | "NEXT_UPDATE" ); | ||
| 2994 | "ROWID", | ||
| 2995 | } elsif ( $type =~ /^USER$/ ) { | ||
| 2996 | @ret = ( "USER_ID", | ||
| 2997 | "DATA_SOURCE", | ||
| 2998 | "SECRET", | ||
| 2999 | "NOTBEFORE", | ||
| 3000 | "NOTAFTER", | ||
| 3001 | "STATUS", | ||
| 3002 | "EXTERN_ID", | ||
| 3003 | "SUSPENDED_AFTER", | ||
| 3004 | "REVOKED_AFTER", | ||
| 3005 | "LAST_ACTIVITY", | ||
| 3006 | "ROWID", | ||
| 3007 | "INVALIDITY_REASON" ); | ||
| 3008 | } elsif ( $type =~ /USER_DATA/ ) { | ||
| 3009 | @ret = ( "USER_ID", | ||
| 3010 | "NAME", | ||
| 3011 | "ROWID", | ||
| 3012 | "DATA_SOURCE" ); | ||
| 3013 | } elsif ( $type =~ /MESSAGES/ ) { | ||
| 3014 | @ret = ( "USER_ID", | ||
| 3015 | "ROWID", | ||
| 3016 | "FROM", | ||
| 3017 | "TO", | ||
| 3018 | "SUBJECT", | ||
| 3019 | "NOTBEFORE", | ||
| 3020 | "HEADER", | ||
| 3021 | "DATA", | ||
| 3022 | "STATUS" ); | ||
| 3023 | }; | ||
| 3024 | |||
| 3025 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 3026 | return @ret; | ||
| 3027 | } | ||
| 3028 | |||
| 3029 | ## ################################################################## | ||
| 3030 | ## Function Name: getTimeString | ||
| 3031 | ## ################################################################## | ||
| 3032 | |||
| 3033 | sub getTimeString { | ||
| 3034 | |||
| 3035 | ## returns now iso-time | ||
| 3036 | |||
| 3037 | my $self = shift; | ||
| 3038 | my ( $ret, @T ); | ||
| 3039 | |||
| 3040 | @T = gmtime( time() ); | ||
| 3041 | |||
| 3042 | ## iso is yyyy-mm-dd hh:mm:ss | ||
| 3043 | $ret = sprintf( "%4.4d-%2.2d-%2.2d %2.2d:%2.2d:%2.2d", | ||
| 3044 | $T[5]+1900, $T[4], $T[3], $T[2], $T[1], $T[0] ); | ||
| 3045 | |||
| 3046 | return $ret; | ||
| 3047 | |||
| 3048 | } | ||
| 3049 | |||
| 3050 | ## ################################################################## | ||
| 3051 | ## Function Name: getArguments | ||
| 3052 | ## ################################################################## | ||
| 3053 | |||
| 3054 | sub getArguments { | ||
| 3055 | |||
| 3056 | ## parse the arguments for all functions | ||
| 3057 | |||
| 3058 | my $self = shift; | ||
| 3059 | my $keys = { @_ }; | ||
| 3060 | my $check; | ||
| 3061 | |||
| 3062 | my %result; | ||
| 3063 | |||
| 3064 | if (exists $keys->{DEBUG}) { | ||
| 3065 | $self->{DEBUG} = $keys->{DEBUG}; | ||
| 3066 | delete $keys->{DEBUG}; | ||
| 3067 | } | ||
| 3068 | |||
| 3069 | $self->debug ("getArguments: entering function"); | ||
| 3070 | |||
| 3071 | foreach my $key (keys %$keys) { | ||
| 3072 | if ( $key ne "" ) { | ||
| 3073 | $check->{$key} = $keys->{$key}; | ||
| 3074 | $self->debug ("getArguments: check: $key=".$check->{$key}); | ||
| 3075 | }; | ||
| 3076 | } | ||
| 3077 | |||
| 3078 | $result {FROM} = $check->{FROM}; | ||
| 3079 | delete $check->{FROM}; | ||
| 3080 | |||
| 3081 | $result {ITEMS} = $check->{ITEMS}; | ||
| 3082 | delete $check->{ITEMS}; | ||
| 3083 | |||
| 3084 | $result {TABLE} = $self->getTable ($keys->{DATATYPE}); | ||
| 3085 | $result {MODE} = $keys->{MODE}; | ||
| 3086 | delete $check->{MODE}; | ||
| 3087 | $self->debug ("getArguments: TABLE:".$result {TABLE}); | ||
| 3088 | $self->debug ("getArguments: MODE:".$result {MODE}); | ||
| 3089 | |||
| 3090 | ## get all searchable attributes | ||
| 3091 | my @attributes = $self->getSearchAttributes( DATATYPE => $result {TABLE} ); | ||
| 3092 | my $attr; | ||
| 3093 | |||
| 3094 | for $attr ( @attributes ) { | ||
| 3095 | |||
| 3096 | $self->debug ("getArguments: attribute: $attr"); | ||
| 3097 | |||
| 3098 | if ($attr =~ /^EMAIL$/ and not $keys->{$attr}) { | ||
| 3099 | $result {EMAIL} = $keys->{EMAILADDRESS}; | ||
| 3100 | delete $check->{EMAILADDRESS}; | ||
| 3101 | } else { | ||
| 3102 | $result {$attr} = $keys->{$attr}; | ||
| 3103 | delete $check->{$attr}; | ||
| 3104 | } | ||
| 3105 | |||
| 3106 | $self->debug ("getArguments: value: ".$result {$attr}); | ||
| 3107 | } | ||
| 3108 | |||
| 3109 | ## enforce status | ||
| 3110 | $result {STATUS} = $self->getStatus ( STATUS => $result {STATUS}, | ||
| 3111 | DATATYPE => $keys->{DATATYPE} ); | ||
| 3112 | |||
| 3113 | if (not $result {STATUS}) { | ||
| 3114 | $self->debug ("getArguments: no STATUS present"); | ||
| 3115 | delete ($result {STATUS}); | ||
| 3116 | } else { | ||
| 3117 | $self->debug ("getArguments: status: ".$result {STATUS}); | ||
| 3118 | } | ||
| 3119 | delete $check->{STATUS}; | ||
| 3120 | delete $check->{DATATYPE}; | ||
| 3121 | |||
| 3122 | ## New Parameters - used only for CRL and CERTS for now | ||
| 3123 | delete $check->{EXPIRES_BEFORE}; | ||
| 3124 | delete $check->{EXPIRES_AFTER}; | ||
| 3125 | delete $check->{ROWID}; | ||
| 3126 | |||
| 3127 | ## madwolf -- DEBUG | ||
| 3128 | if (scalar (keys %$check)) { | ||
| 3129 | print STDERR "getArguments: ILLEGAL ARGUMENT\n"; | ||
| 3130 | foreach my $key (keys %$check) { | ||
| 3131 | print STDERR "getArguments: [$key] = [".$check->{$key} . "]\n"; | ||
| 3132 | } | ||
| 3133 | $self->set_error ( $OpenCA::DBI::ERROR->{ILLEGAL_ARGUMENT} ); | ||
| 3134 | return undef; | ||
| 3135 | } | ||
| 3136 | |||
| 3137 | $self->debug ("getArguments: completed successful"); | ||
| 3138 | return %result; | ||
| 3139 | |||
| 3140 | } | ||
| 3141 | |||
| 3142 | ## ################################################################## | ||
| 3143 | ## Function Name: getTable | ||
| 3144 | ## ################################################################## | ||
| 3145 | |||
| 3146 | sub getTable { | ||
| 3147 | |||
| 3148 | ## this is a standardinterface to get the table from the original | ||
| 3149 | ## datatype. so we can use the normal interface of OpenCA::DB | ||
| 3150 | |||
| 3151 | my $self = shift; | ||
| 3152 | my $datatype = $_[0]; | ||
| 3153 | |||
| 3154 | my $ret; | ||
| 3155 | |||
| 3156 | if ( $datatype =~ /CA_CERTIFICATE/ ) { | ||
| 3157 | $ret = "CA_CERTIFICATE"; | ||
| 3158 | } elsif ( $datatype =~ /CERTIFICATE/ ) { | ||
| 3159 | $ret = "CERTIFICATE"; | ||
| 3160 | } elsif ( $datatype =~ /CRL/ ) { | ||
| 3161 | $ret = "CRL"; | ||
| 3162 | } elsif ( $datatype =~ /REQUEST/ ) { | ||
| 3163 | $ret = "REQUEST"; | ||
| 3164 | } elsif ( $datatype =~ /CRR/ ) { | ||
| 3165 | $ret = "CRR"; | ||
| 3166 | } else { | ||
| 3167 | ## Unsupported DATATYPE | ||
| 3168 | $ret = ""; | ||
| 3169 | } | ||
| 3170 | |||
| 3171 | return $ret; | ||
| 3172 | } | ||
| 3173 | |||
| 3174 | ## ################################################################## | ||
| 3175 | ## Function Name: getStatus | ||
| 3176 | ## ################################################################## | ||
| 3177 | |||
| 3178 | sub getStatus { | ||
| 3179 | |||
| 3180 | ## this function support to work with old and new code | ||
| 3181 | ## this means that I check for STATUS and if it is not existent | ||
| 3182 | ## I try to extract it from the datatype | ||
| 3183 | |||
| 3184 | my $self = shift; | ||
| 3185 | my $keys = { @_ }; | ||
| 3186 | |||
| 3187 | $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} ); | ||
| 3188 | |||
| 3189 | my $status = $keys->{STATUS}; | ||
| 3190 | my $datatype = $keys->{DATATYPE}; | ||
| 3191 | |||
| 3192 | $status =~ s/ARCHIVIED/ARCHIVED/; | ||
| 3193 | $datatype =~ s/ARCHIVIED/ARCHIVED/; | ||
| 3194 | |||
| 3195 | $self->debug ("getStatus: Entering function"); | ||
| 3196 | |||
| 3197 | if ($status) { | ||
| 3198 | $self->debug ("getStatus: status predefined: $status"); | ||
| 3199 | ## check for legal status | ||
| 3200 | if ( $OpenCA::DBI::STATUS->{$status} ) { | ||
| 3201 | $self->debug ("getStatus: legal status (leaving function)"); | ||
| 3202 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 3203 | return $status; | ||
| 3204 | } else { | ||
| 3205 | $self->debug ("getStatus: illegal status (leaving function)"); | ||
| 3206 | $self->set_error ( $OpenCA::DBI::ERROR->{ILLEGAL_STATUS} ); | ||
| 3207 | return undef; | ||
| 3208 | } | ||
| 3209 | } else { | ||
| 3210 | $self->debug ("getStatus: no status given using datatype: $datatype"); | ||
| 3211 | ## try to extract status from datatype | ||
| 3212 | ## erase all behind the first "_" incl. this "_" itself | ||
| 3213 | my $old = $datatype; | ||
| 3214 | $datatype =~ s/_.*//g; | ||
| 3215 | $datatype = $old if ($datatype =~ /^CA$/i); | ||
| 3216 | $datatype = "" if ($old eq $datatype); | ||
| 3217 | $datatype = uc $datatype; | ||
| 3218 | $self->debug ("getStatus: given mode is now: $datatype"); | ||
| 3219 | ## check for legal status | ||
| 3220 | if ( $datatype =~ /^$/ ) | ||
| 3221 | { | ||
| 3222 | $self->debug ("getStatus: no status (leaving function)"); | ||
| 3223 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 3224 | return $datatype; | ||
| 3225 | } elsif ( $OpenCA::DBI::STATUS->{$datatype} ) { | ||
| 3226 | $self->debug ("getStatus: legal status (leaving function)"); | ||
| 3227 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 3228 | return $datatype; | ||
| 3229 | } else { | ||
| 3230 | $self->debug ("getStatus: illegal status (leaving function)"); | ||
| 3231 | $self->set_error ( $OpenCA::DBI::ERROR->{ILLEGAL_STATUS} ); | ||
| 3232 | return undef; | ||
| 3233 | } | ||
| 3234 | } | ||
| 3235 | } | ||
| 3236 | |||
| 3237 | ## ################################################################## | ||
| 3238 | ## Function Name: doQuery | ||
| 3239 | ## ################################################################## | ||
| 3240 | |||
| 3241 | sub doQuery { | ||
| 3242 | my $self = shift; | ||
| 3243 | my $keys = { @_ }; | ||
| 3244 | |||
| 3245 | $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} ); | ||
| 3246 | |||
| 3247 | $self->debug ("doQuery: entering function"); | ||
| 3248 | |||
| 3249 | # these variables are in-vars | ||
| 3250 | my $query = $keys->{QUERY}; | ||
| 3251 | # my @bind_values = @{$keys->{BIND_VALUES}} if ($keys->{BIND_VALUES}); | ||
| 3252 | my @bind_values = (); | ||
| 3253 | my @bind_types = @{$keys->{BIND_TYPES}} if ($keys->{BIND_TYPES}); | ||
| 3254 | |||
| 3255 | foreach my $help ( @{$keys->{BIND_VALUES}} ) { | ||
| 3256 | # if(utf8::is_utf8($help)) { | ||
| 3257 | # print STDERR "HELP::VALUE is UTF8 => $help\n"; | ||
| 3258 | # utf8::decode($help); | ||
| 3259 | # } | ||
| 3260 | push( @bind_values, $help ); | ||
| 3261 | } | ||
| 3262 | |||
| 3263 | $self->debug ("doQuery: query: $query"); | ||
| 3264 | |||
| 3265 | foreach my $help (@bind_values) { | ||
| 3266 | ## madwolf -- DEBUG | ||
| 3267 | $self->debug ("doQuery: bind_values: $help"); | ||
| 3268 | } | ||
| 3269 | |||
| 3270 | ## query empty so not a DB-failure | ||
| 3271 | return undef if ($query eq ""); | ||
| 3272 | |||
| 3273 | ## prepare | ||
| 3274 | $self->debug ("doQuery: prepare statement"); | ||
| 3275 | $self->{STH} = $self->{DBH}->prepare ($query); | ||
| 3276 | if (not exists $self->{STH} or not defined $self->{STH} or | ||
| 3277 | not ref $self->{STH}) { | ||
| 3278 | ## necessary for Oracle | ||
| 3279 | $self->debug ("doQuery: prepare failed"); | ||
| 3280 | $self->debug ("doQuery: query: $query"); | ||
| 3281 | $self->debug ("doQuery: prepare returned undef"); | ||
| 3282 | $self->set_error ( $OpenCA::DBI::ERROR->{PREPARE_FAILED} ); | ||
| 3283 | return undef; | ||
| 3284 | } | ||
| 3285 | |||
| 3286 | ## numer of elements in @bind_values and @bind_types have to be equal | ||
| 3287 | if ($#bind_values != $#bind_types) { | ||
| 3288 | $self->set_error ($OpenCA::DBI::ERROR->{PREPARE_FAILED}); | ||
| 3289 | return undef; | ||
| 3290 | } | ||
| 3291 | |||
| 3292 | ## binding values to placeholders | ||
| 3293 | my $q_count=0; | ||
| 3294 | foreach my $q_value (@bind_values) { | ||
| 3295 | $q_count ++; | ||
| 3296 | my $q_type = shift(@bind_types); | ||
| 3297 | if ( $q_type =~ /DECIMAL/ ) { | ||
| 3298 | $self->{STH}->bind_param( $q_count, $q_value, SQL_DECIMAL ); | ||
| 3299 | } elsif ( $q_type =~ /BIGINT/ ) { | ||
| 3300 | $self->{STH}->bind_param( $q_count, $q_value, SQL_BIGINT ); | ||
| 3301 | } else { | ||
| 3302 | $self->{STH}->bind_param( $q_count, $q_value, SQL_UNKNOWN_TYPE ); | ||
| 3303 | } | ||
| 3304 | } | ||
| 3305 | |||
| 3306 | ## execute | ||
| 3307 | $self->debug ("doQuery: execute statement"); | ||
| 3308 | |||
| 3309 | my $result = $self->{STH}->execute (); | ||
| 3310 | if (defined $result) { | ||
| 3311 | $self->debug ("doQuery: execute succeeded (leaving function - $result)"); | ||
| 3312 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 3313 | return $result; | ||
| 3314 | } else { | ||
| 3315 | # print STDERR "doQuery: query: $query\n"; | ||
| 3316 | |||
| 3317 | $self->debug ("doQuery: execute failed (leaving function)"); | ||
| 3318 | $self->set_error ( $OpenCA::DBI::ERROR->{EXECUTE_FAILED} ); | ||
| 3319 | return undef; | ||
| 3320 | } | ||
| 3321 | } | ||
| 3322 | |||
| 3323 | ## ################################################################## | ||
| 3324 | ## Function Name: getResultItem | ||
| 3325 | ## ################################################################## | ||
| 3326 | |||
| 3327 | sub getResultItem { | ||
| 3328 | |||
| 3329 | ## this function is a ready to build an answer from the arguments and | ||
| 3330 | ## from the resulting array. Parameters are: | ||
| 3331 | ## ARGUMENTS | ||
| 3332 | ## ARRAYREF | ||
| 3333 | |||
| 3334 | my $self = shift; | ||
| 3335 | my $keys = { @_ }; | ||
| 3336 | my %hash = undef; | ||
| 3337 | |||
| 3338 | my $item; | ||
| 3339 | |||
| 3340 | $self->debug ("getResultItem: entering function"); | ||
| 3341 | |||
| 3342 | my %arguments = %{$keys->{ARGUMENTS}}; | ||
| 3343 | return undef if (not %arguments); | ||
| 3344 | |||
| 3345 | my $arrayref = $keys->{ARRAYREF};; | ||
| 3346 | return undef if (not defined $arrayref); | ||
| 3347 | |||
| 3348 | $self->debug ("getResultItem: all params present"); | ||
| 3349 | |||
| 3350 | %hash = $self->getResultHash (TABLE => $arguments{TABLE}, | ||
| 3351 | ARRAY => $arrayref); | ||
| 3352 | |||
| 3353 | # foreach my $i ( keys %hash ) { | ||
| 3354 | # print STDERR "getResultItem: HASH: $i => " . $hash{$i} . "\n"; | ||
| 3355 | # } | ||
| 3356 | |||
| 3357 | my $data = $hash{DATA}; | ||
| 3358 | my $priv_format = $hash{FORMAT}; | ||
| 3359 | my $today = gmtime; | ||
| 3360 | my $now = $self->{backend}->getNumericDate( "$today" ); | ||
| 3361 | my $tempBefore = undef; | ||
| 3362 | my $tempAfter = undef; | ||
| 3363 | |||
| 3364 | $self->debug ("getResultItem: data: $data"); | ||
| 3365 | $self->debug ("getResultItem: format: $priv_format"); | ||
| 3366 | $self->debug ("getResultItem: have all data"); | ||
| 3367 | |||
| 3368 | ## If it was asked only the text version, we send out only that | ||
| 3369 | ## without generating an OBJECT from it | ||
| 3370 | if( $arguments{MODE} eq "RAW" ) { | ||
| 3371 | $self->debug ("getResultItem: return data RAW"); | ||
| 3372 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 3373 | return $data; | ||
| 3374 | } | ||
| 3375 | |||
| 3376 | ## Build an Object from retrieved DATA | ||
| 3377 | if( $arguments{TABLE} =~ /CERTIFICATE/ ) { | ||
| 3378 | |||
| 3379 | $item = new OpenCA::X509( SHELL => $self->{backend}, | ||
| 3380 | GETTEXT => $self->{gettext}, | ||
| 3381 | INFORM => "PEM", | ||
| 3382 | DATA => $data); | ||
| 3383 | |||
| 3384 | if ( not $item ) { | ||
| 3385 | #print STDERR "ERROR::" . $arguments{TABLE} . "::$data\n"; | ||
| 3386 | return undef; | ||
| 3387 | } | ||
| 3388 | |||
| 3389 | $item->setStatus( $hash{STATUS} ); | ||
| 3390 | |||
| 3391 | if ( $item->getStatus() =~ /VALID|EXPIRED/ ) { | ||
| 3392 | $tempBefore = $self->{backend}->getNumericDate( | ||
| 3393 | $item->getParsed()->{NOTBEFORE} ); | ||
| 3394 | $tempAfter = $self->{backend}->getNumericDate( | ||
| 3395 | $item->getParsed()->{NOTAFTER} ); | ||
| 3396 | |||
| 3397 | if (( $tempBefore <= $now ) and ( $tempAfter >= $now )) { | ||
| 3398 | $item->setStatus( "VALID" ); | ||
| 3399 | } else { | ||
| 3400 | # print STDERR "STATUS CHANGE:: BY CHECK => NOW is $now\n"; | ||
| 3401 | # print STDERR "STATUS CHANGE:: NOTBEFORE => " . | ||
| 3402 | # $item->getParsed()->{NOTBEFORE} . "\n"; | ||
| 3403 | # print STDERR "STATUS CHANGE:: NOTAFTER => " . | ||
| 3404 | # $item->getParsed()->{NOTAFTER} . "\n"; | ||
| 3405 | |||
| 3406 | $item->setStatus( "EXPIRED" ); | ||
| 3407 | } | ||
| 3408 | } | ||
| 3409 | |||
| 3410 | } elsif ( $arguments{TABLE} eq "CRL" ) { | ||
| 3411 | $self->debug ("getItem: try to create crl"); | ||
| 3412 | $item = new OpenCA::CRL( SHELL => $self->{backend}, | ||
| 3413 | INFORM => "PEM", | ||
| 3414 | GETTEXT => $self->{gettext}, | ||
| 3415 | DATA => $data); | ||
| 3416 | |||
| 3417 | $self->debug ("getResultItem: crl there") if ($item); | ||
| 3418 | $self->debug ("ResultItem: crl failed") if (not $item); | ||
| 3419 | |||
| 3420 | $item->setStatus( $hash{STATUS} ); | ||
| 3421 | |||
| 3422 | if ( $item->getStatus() =~ /VALID|EXPIRED/ ) { | ||
| 3423 | $tempBefore = $self->{backend}->getNumericDate( | ||
| 3424 | $item->getParsed()->{NOTBEFORE} ); | ||
| 3425 | $tempAfter = $self->{backend}->getNumericDate( | ||
| 3426 | $item->getParsed()->{NOTAFTER} ); | ||
| 3427 | if (( $tempBefore <= $now ) and ( $tempAfter >= $now )) { | ||
| 3428 | $item->setStatus( "VALID" ); | ||
| 3429 | } else { | ||
| 3430 | $item->setStatus( "EXPIRED" ); | ||
| 3431 | } | ||
| 3432 | } | ||
| 3433 | |||
| 3434 | } elsif ( $arguments{TABLE} =~ /(REQUEST|CRR)/i ) { | ||
| 3435 | $item = new OpenCA::REQ( SHELL => $self->{backend}, | ||
| 3436 | GETTEXT => $self->{gettext}, | ||
| 3437 | DATA => $data); | ||
| 3438 | |||
| 3439 | $item->setStatus( $hash{STATUS} ); | ||
| 3440 | |||
| 3441 | } else { | ||
| 3442 | ## if we cannot build the object there is probably | ||
| 3443 | ## an error, retrun a void ... | ||
| 3444 | $self->debug ("getResultItem: cannot determine table"); | ||
| 3445 | $self->set_error ( $OpenCA::DBI::ERROR->{ WRONG_DATATYPE } ); | ||
| 3446 | return undef; | ||
| 3447 | } | ||
| 3448 | if (not $item) | ||
| 3449 | { | ||
| 3450 | $self->debug ("getResultItem: cannot build object return void"); | ||
| 3451 | $self->set_error ( $OpenCA::DBI::ERROR->{ CANNOT_CREATE_OBJECT } ); | ||
| 3452 | return undef; | ||
| 3453 | } | ||
| 3454 | |||
| 3455 | # $item->{STATUS} = $hash{STATUS}; | ||
| 3456 | |||
| 3457 | ## who uses DBKEY ?! | ||
| 3458 | $item->{parsedItem}->{DBKEY} = $arguments{KEY}; | ||
| 3459 | $item->{parsedItem}->{KEY} = $arguments{KEY}; | ||
| 3460 | $item->{KEY} = $arguments{KEY}; | ||
| 3461 | |||
| 3462 | $item->{parsedItem}->{ROWID} = $hash { ROWID }; | ||
| 3463 | $item->{ROWID} = $hash { ROWID }; | ||
| 3464 | |||
| 3465 | $item->{DATATYPE} = $hash{STATUS} . "_" . $arguments{TABLE}; | ||
| 3466 | |||
| 3467 | ## We return the object | ||
| 3468 | $self->debug ("getResultItem: return item"); | ||
| 3469 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 3470 | |||
| 3471 | return $item; | ||
| 3472 | |||
| 3473 | } ## end of getResultItem | ||
| 3474 | |||
| 3475 | ## ################################################################## | ||
| 3476 | ## Function Name: getResultHash | ||
| 3477 | ## ################################################################## | ||
| 3478 | |||
| 3479 | sub getResultHash { | ||
| 3480 | |||
| 3481 | ## this function is neccessary because DB2 does not support | ||
| 3482 | ## the function fetchrow_hashref | ||
| 3483 | |||
| 3484 | my $self = shift; | ||
| 3485 | my $keys = { @_ }; | ||
| 3486 | my @cols = (); | ||
| 3487 | my %result; | ||
| 3488 | |||
| 3489 | $self->debug ("getResultHash: entring function"); | ||
| 3490 | @cols = @{$OpenCA::DBI::SQL->{TABLE_STRUCTURE}->{$keys->{TABLE}}}; | ||
| 3491 | |||
| 3492 | for (my $i = 0; $i <= $#cols; $i++) { | ||
| 3493 | my $val = undef; | ||
| 3494 | |||
| 3495 | $val = $keys->{ARRAY}->[$i]; | ||
| 3496 | if ( ($val ne "" ) and (! utf8::is_utf8( $val )) ) { | ||
| 3497 | utf8::decode($val); | ||
| 3498 | } | ||
| 3499 | |||
| 3500 | $result { $cols[$i]} = $val; | ||
| 3501 | |||
| 3502 | # print STDERR "getResultHash: " . $keys->{TABLE} . ": " . | ||
| 3503 | # "HASH{" . $cols[$i] . "} = ".$keys->{ARRAY}->[$i]."\n"; | ||
| 3504 | |||
| 3505 | $self->debug ("getResultHash: column: ". $cols[$i]); | ||
| 3506 | $self->debug ("getResultHash: value: ".$keys->{ARRAY}->[$i]); | ||
| 3507 | } | ||
| 3508 | |||
| 3509 | $self->debug ("getResultHash: leaving function"); | ||
| 3510 | |||
| 3511 | return %result; | ||
| 3512 | |||
| 3513 | } | ||
| 3514 | |||
| 3515 | ## ################################################################## | ||
| 3516 | ## BEGIN BLOCK: DB Management: rollback, commit, disconnect, DESTROY | ||
| 3517 | ## ################################################################## | ||
| 3518 | |||
| 3519 | sub rollback { | ||
| 3520 | |||
| 3521 | ## rollback never touch the status because | ||
| 3522 | ## rollback is normally the action if a | ||
| 3523 | ## statement fails | ||
| 3524 | |||
| 3525 | my $self = shift; | ||
| 3526 | |||
| 3527 | $self->debug ("rollback: entering function"); | ||
| 3528 | |||
| 3529 | ## if there is no databasehandle then we have not to and cannot roll back | ||
| 3530 | if (not $self->{DBH} or $self->{DBH}->rollback()) { | ||
| 3531 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ) | ||
| 3532 | if (not $self->errno()); | ||
| 3533 | return 1; | ||
| 3534 | } else { | ||
| 3535 | $self->set_error ( $OpenCA::DBI::ERROR->{ROLLBACK_FAILED} ); | ||
| 3536 | return undef; | ||
| 3537 | } | ||
| 3538 | } | ||
| 3539 | |||
| 3540 | ## Function: commit | ||
| 3541 | ## ================ | ||
| 3542 | |||
| 3543 | sub commit { | ||
| 3544 | ## commit sets the status-variable | ||
| 3545 | my $self = shift; | ||
| 3546 | |||
| 3547 | $self->debug ("commit: entering function"); | ||
| 3548 | |||
| 3549 | if (not defined $self->{DBH} or $self->{DBH}->commit() ) { | ||
| 3550 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ) | ||
| 3551 | if (not $self->errno()); | ||
| 3552 | return 1; | ||
| 3553 | } else { | ||
| 3554 | $self->set_error ( $OpenCA::DBI::ERROR->{COMMIT_FAILED} ); | ||
| 3555 | return undef; | ||
| 3556 | } | ||
| 3557 | } | ||
| 3558 | |||
| 3559 | ## Function: disconnect | ||
| 3560 | ## ==================== | ||
| 3561 | ## disconnect does not set the status-variable because commit | ||
| 3562 | ## and rollback called before disconnect so success is not important | ||
| 3563 | |||
| 3564 | sub disconnect { | ||
| 3565 | |||
| 3566 | my $self = shift; | ||
| 3567 | |||
| 3568 | ## Added to correctly handle the disconnection | ||
| 3569 | if( exists $self->{STH} ) { | ||
| 3570 | $self->{STH}->finish(); | ||
| 3571 | }; | ||
| 3572 | |||
| 3573 | if ( (exists $self->{DBH}) and ($self->{DBH}->disconnect()) ) { | ||
| 3574 | $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); | ||
| 3575 | return 1; | ||
| 3576 | } else { | ||
| 3577 | $self->set_error ( $OpenCA::DBI::ERROR->{DISCONNECT_FAILED} ); | ||
| 3578 | return undef; | ||
| 3579 | } | ||
| 3580 | } | ||
| 3581 | |||
| 3582 | ## Function: DESTROY | ||
| 3583 | ## ================= | ||
| 3584 | |||
| 3585 | sub DESTROY { | ||
| 3586 | my $self = shift; | ||
| 3587 | |||
| 3588 | if ($self->{ERRNO} != $OpenCA::DBI::ERROR->{SUCCESS}) { | ||
| 3589 | $self->debug ("DESTROY: automatic rollback by destructor DESTROY"); | ||
| 3590 | $self->rollback (); | ||
| 3591 | } else { | ||
| 3592 | $self->debug ("DESTROY: automatic commit by destructor DESTROY"); | ||
| 3593 | if (not defined $self->commit ()) { | ||
| 3594 | $self->debug_err ( "WARNING: commit failed when destroying db handler", | ||
| 3595 | "WARNING: rollback() called."); | ||
| 3596 | $self->rollback (); | ||
| 3597 | } | ||
| 3598 | } | ||
| 3599 | ## finish the statement handles to reduce warnings by DBI | ||
| 3600 | $self->debug ("DESTROY: call finish on all statement handles to avoid" . | ||
| 3601 | " warnings by DBI"); | ||
| 3602 | $self->{STH}->finish(); | ||
| 3603 | |||
| 3604 | $self->{DBH}->disconnect () if (exists $self->{DBH}); | ||
| 3605 | } | ||
| 3606 | |||
| 3607 | ## ################################################################## | ||
| 3608 | ## BEGIN BLOCK: Error Handling | ||
| 3609 | ## ################################################################## | ||
| 3610 | |||
| 3611 | sub errno | ||
| 3612 | { | ||
| 3613 | my $self = shift; | ||
| 3614 | |||
| 3615 | if ( exists $self->{errno} ) { | ||
| 3616 | $self->debug ("errno: returning local errorcode ".$self->{errno}); | ||
| 3617 | return $self->{errno}; | ||
| 3618 | } else { | ||
| 3619 | $self->debug ("errno: returning global errorcode $OpenCA::DBI::errno"); | ||
| 3620 | return $OpenCA::DBI::errno; | ||
| 3621 | } | ||
| 3622 | } | ||
| 3623 | |||
| 3624 | sub set_error { | ||
| 3625 | my $self = shift; | ||
| 3626 | |||
| 3627 | $self->debug ("Entering set_error ..."); | ||
| 3628 | |||
| 3629 | ## checking for an explicit error message (from DBI) | ||
| 3630 | |||
| 3631 | my $message = $OpenCA::DBI::MESSAGE->{$_[0]}; | ||
| 3632 | $message = $_[1] if ($_[1]); | ||
| 3633 | |||
| 3634 | ## if gettext was not defined | ||
| 3635 | ## then fall back to conventional errormessages | ||
| 3636 | |||
| 3637 | if (not $self->{gettext}) | ||
| 3638 | { | ||
| 3639 | $self->debug ("set_error: gettext is not defined"); | ||
| 3640 | |||
| 3641 | if (defined $_[0]) | ||
| 3642 | { | ||
| 3643 | $self->{errno} = $_[0]; | ||
| 3644 | $self->{errval} = $message; | ||
| 3645 | } | ||
| 3646 | $self->debug ("set_error: errno and errval set"); | ||
| 3647 | } else | ||
| 3648 | { | ||
| 3649 | ## fully i18n error handling | ||
| 3650 | |||
| 3651 | $self->debug ("errno: gettext is defined"); | ||
| 3652 | $message = $self->{gettext} ($message); | ||
| 3653 | |||
| 3654 | ## set errorcode | ||
| 3655 | my $old = $self->{errno}; | ||
| 3656 | $self->{errno} = $_[0]; | ||
| 3657 | |||
| 3658 | ## this is the new OpenCA-standard | ||
| 3659 | if ($old) | ||
| 3660 | { | ||
| 3661 | $self->debug ("errno: old errno $old is present"); | ||
| 3662 | |||
| 3663 | ## save the last error too | ||
| 3664 | $self->{errval} = $self->{gettext} ("__MESSAGE__ (error __OLD_ERRNO__: __OLD_ERRVAL__)", | ||
| 3665 | "__MESSAGE__", $message, | ||
| 3666 | "__OLD_ERRNO__", $old, | ||
| 3667 | "__OLD_ERRVAL__", $self->{errval}); | ||
| 3668 | } else { | ||
| 3669 | $self->{errval} = $self->{gettext} ($message); | ||
| 3670 | } | ||
| 3671 | |||
| 3672 | $self->debug ("errno: new errorcode is $errno"); | ||
| 3673 | } | ||
| 3674 | $errno = $self->{errno}; | ||
| 3675 | $errval = $self->{errval}; | ||
| 3676 | |||
| 3677 | return undef; | ||
| 3678 | } | ||
| 3679 | |||
| 3680 | sub errval { | ||
| 3681 | my $self = shift; | ||
| 3682 | my $text = ""; | ||
| 3683 | my $code; | ||
| 3684 | |||
| 3685 | return $self->{errval} if ($self->{errval}); | ||
| 3686 | return $errval; | ||
| 3687 | } | ||
| 3688 | |||
| 3689 | sub debug { | ||
| 3690 | my $self = shift; | ||
| 3691 | my @call = caller ( 1 ); | ||
| 3692 | |||
| 3693 | if ($_[0]) { | ||
| 3694 | $self->{debug_msg}[scalar @{$self->{debug_msg}}] = $_[0]; | ||
| 3695 | $self->debug () if ($self->{DEBUG}); | ||
| 3696 | } else { | ||
| 3697 | my $msg; | ||
| 3698 | if ( $self->{DEBUG_STDERR} ) { | ||
| 3699 | foreach $msg (@{$self->{debug_msg}}) { | ||
| 3700 | print STDERR "OpenCA::DBI->$msg\n"; | ||
| 3701 | } | ||
| 3702 | } | ||
| 3703 | $self->{debug_msg} = (); | ||
| 3704 | } | ||
| 3705 | } | ||
| 3706 | |||
| 3707 | sub debug_err { | ||
| 3708 | my $self = shift; | ||
| 3709 | my @call = caller ( 1 ); | ||
| 3710 | |||
| 3711 | if ( $self->{DEBUG_STDERR} ) { | ||
| 3712 | foreach my $msg ( @_ ) { | ||
| 3713 | print STDERR "OpenCA::DBI->$msg\n"; | ||
| 3714 | } | ||
| 3715 | } | ||
| 3716 | } | ||
| 3717 | |||
| 3718 | ########################## | ||
| 3719 | ## end of new functions ## | ||
| 3720 | ########################## | ||
| 3721 | |||
| 3722 | # Autoload methods go after =cut, and are processed by the autosplit program. | ||
| 3723 | |||
| 3724 | 1; | ||
| 3725 | __END__ |