blib/lib/OpenCA/DBI.pm untested

filter: Statementstmt 0.0% 0 / 1449 Branchbran 0.0% 0 / 498 Conditioncond 0.0% 0 / 117 MC/DCmcdc 0.0% 0 / 75 Subroutinesub 0.0% 0 / 50 Podpod 0.0% 0 / 39 totaltotal 0.0% 0 / 2228 CC 250 SCAR 58.8
CC
250
Cov
0%
searchItems
1560.0
storeItem_getArguments
552.0
new
462.0
CRAP 356.8
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." --&gt; ".
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." --&gt; ".
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__