## OpenCA::DBI
##
## Written by Michael Bell for the OpenCA project 2000
## Copyright (C) 2000-2004 The OpenCA Project
## GNU public license
##
## Code parts from OpenCA::DB are under the following license
## (copyright statement from OpenCA::DB v0.8.7a
##
## Copyright (C) 1998-1999 Massimiliano Pala (madwolf@openca.org)
## All rights reserved.
##
## This library is free for commercial and non-commercial use as long as
## the following conditions are aheared to.  The following conditions
## apply to all code found in this distribution, be it the RC4, RSA,
## lhash, DES, etc., code; not just the SSL code.  The documentation
## included with this distribution is covered by the same copyright terms
## 
## // Copyright remains Massimiliano Pala's, and as such any Copyright notices
## in the code are not to be removed.
## If this package is used in a product, Massimiliano Pala should be given
## attribution as the author of the parts of the library used.
## This can be in the form of a textual message at program startup or
## in documentation (online or textual) provided with the package.
## 
## Redistribution and use in source and binary forms, with or without
## modification, are permitted provided that the following conditions
## are met:
## 1. Redistributions of source code must retain the copyright
##    notice, this list of conditions and the following disclaimer.
## 2. Redistributions in binary form must reproduce the above copyright
##    notice, this list of conditions and the following disclaimer in the
##    documentation and/or other materials provided with the distribution.
## 3. All advertising materials mentioning features or use of this software
##    must display the following acknowledgement:
## //   "This product includes OpenCA software written by Massimiliano Pala
## //    (madwolf@openca.org) and the OpenCA Group (www.openca.org)"
## 4. If you include any Windows specific code (or a derivative thereof) from 
##    some directory (application code) you must include an acknowledgement:
##    "This product includes OpenCA software (www.openca.org)"
## 
## THIS SOFTWARE IS PROVIDED BY OPENCA DEVELOPERS ``AS IS'' AND
## ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
## IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
## ARE DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE
## FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
## DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
## OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
## HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
## LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
## OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
## SUCH DAMAGE.
## 
## The licence and distribution terms for any publically available version or
## derivative of this code cannot be changed.  i.e. this code cannot simply be
## copied and put under another distribution licence
## [including the GNU Public Licence.]
##
## end of license of OpenCA::DB v0.8.7a
##

## special thanks
##
## MySQL:	Julio Sanchez Fernandez <jsanchez@users.sf.net>
## Oracle:	balamood@vt.edu (if somebody knows the fullname I add it
##
## please write a note to us if one of the addresses is wrong

use strict;

package OpenCA::DBI;

our ($errno, $errval);

## We must store/retrieve CRLs,CERTs,REQs objects:
## proper instances of object management classes are
## needed.
## see http://www.informatik.hu-berlin.de/~mbell/OpenCA/OpenCA_DBI/OpenCA_DBI.html
## for more information about the datastructure

## The locale category LC_MESSAGES is not exported by the POSIX
## module on older Perl versions.
use Locale::Messages qw (LC_MESSAGES);

use POSIX ('setlocale');

## Set the locale according to our environment.
setlocale (LC_MESSAGES, '');

use OpenCA::REQ;
use OpenCA::X509;
use OpenCA::CRL;
use OpenCA::OpenSSL;
use OpenCA::Tools;
use DBI;

## the other use directions depends from the used databases

## $Revision: 1.11 $

# code contributed by Andreas Fitzner
($OpenCA::DBI::VERSION = '$Revision: 1.11 $' )=~ s/(?:^.*: (\d+))|(?:\s+\$$)/defined $1?"0\.9":""/eg; 

##################
## DB Org-stuff ##
##################

############################################################################
##  PLEASE read carefully the following conventions before you            ##
##  edit something of the database code                                   ##
############################################################################
##  1.  do not use plural at sometime because you can come into trouble   ##
##      with types                                                        ##
##  2.  please write all types and all names small - this is not good     ##
##      sql-style but sybase ase and ibm db2 for example has no problems  ##
##      with this (they made it big ;-) ). PostgreSQL has with the        ##
##      standard configuration (tested on debian 2.2 i386) really big     ##
##      trouble with names of tables and variables in big letters because ##
##      it tries to convert it to small letters but it does not work      ##
##      correctly (I think because it reports errors of mdopen that the   ##
##      lowercase forget the filename).                                   ##
############################################################################

$OpenCA::DBI::SQL = {
                     TABLE => { 
                               REQUEST        => "request",
                               CERTIFICATE    => "certificate",
                               CA_CERTIFICATE => "ca_certificate",
                               CRR            => "crr",
                               CRL            => "crl",
                              },
                     ## I use here several duplicate array
                     ## somewhere I have to stop the complexity ...
                     VARIABLE => {
                                  DATE                  => ["submit_date",          "TEXT"],
                                  SUBMIT_DATE           => ["submit_date",          "TEXT"],
                                  FORMAT                => ["format",        "TEXT"],
                                  DATA                  => ["data",          "LONGTEXT"],

                                  SERIAL                => ["serial",        "BIGINT"],
                                  KEY                   => ["mykey",         "TEXT_KEY"],

                                  CERTIFICATE_SERIAL    => ["cert_key",      "BIGINT"],
                                  # same like certificate_serial but for CRR
                                  REVOKE_CERTIFICATE_SERIAL    => ["cert_key",      "BIGINT"],
                                  CA_CERTIFICATE_SERIAL => ["ca_cert_key",   "TEXT_KEY"],
                                  REQUEST_SERIAL        => ["req_key",       "BIGINT"],
                                  CSR_SERIAL            => ["req_key",       "BIGINT"],
                                  CRR_SERIAL            => ["crr_key",       "BIGINT"],
#                                  CRL_SERIAL            => ["crl_key",       "TEXT_KEY"],
                                  CRL_SERIAL            => ["crl_key",       "BIGINT"],
                                  LOG_SERIAL            => ["action_number", "BIGINT"],
                                  SIGNATURE_SERIAL      => ["action_number", "BIGINT"],
                                  # end of redefined variables
                                  
				  # Order By
                                  CERTIFICATE_ORDERBY    => ["cert_key"],
                                  REVOKE_CERTIFICATE_ORDERBY  => ["cert_key"],
                                  CA_CERTIFICATE_ORDERBY => ["ca_cert_key"],
                                  REQUEST_ORDERBY        => ["req_key"],
                                  CSR_ORDERBY            => ["req_key"],
                                  CRR_ORDERBY            => ["crr_key"],
                                  CRL_ORDERBY            => ["last_update"],
                                  LOG_ORDERBY            => ["action_number"],
				  
                                  # for searching
                                  DN                    => ["dn",            "TEXT"],
                                  # same like dn but for CRRs
                                  REVOKE_CERTIFICATE_DN => ["dn",            "TEXT"],
                                  CN                    => ["cn",            "TEXT"],
                                  EMAIL                 => ["email",         "TEXT"],
                                  RA                    => ["ra",            "TEXT"],
                                  RAO                   => ["rao",           "TEXT"],
                                  Operator              => ["rao",           "TEXT"],
                                  OPERATOR              => ["rao",           "TEXT"],
                                  LAST_UPDATE           => ["last_update",   "BIGINT"],
                                  NEXT_UPDATE           => ["next_update",   "BIGINT"],
                                  DATATYPE              => ["datatype",      "TEXT"],
                                  ROLE                  => ["role",          "TEXT"],
                                  PUBKEY                => ["public_key",    "TEXT"],
                                  NOTAFTER              => ["notafter",      "BIGINT"],
                                  SCEP_TID              => ["scep_tid",      "TEXT"],
                                  LOA                   => ["loa",           "TEXT"],
                                  
                                  # logging and integrity support
                                  DATATYPE              => ["datatype",      "TEXT"],
                                  STATUS                => ["status",        "TEXT"],
                                  REASON                => ["reason",        "TEXT"],
                                  ACTION_NUMBER         => ["action_number", "BIGINT"],
                                  MODULETYPE            => ["moduletype",    "TEXT"],
                                  MODULE                => ["module",        "TEXT"],
                                  LOG_SUBMIT_DATE       => ["log_submit_date",   "TEXT"],
                                  LOG_DO_DATE           => ["log_do_date",   "TEXT"],
                                 }
                    };

## second call to $OpenCA::DBI::SQL because I use content of this variable
$OpenCA::DBI::SQL->{TABLE_STRUCTURE} = 
  {
   REQUEST => [
               "REQUEST_SERIAL",
               "FORMAT",
               "DATA",
               "DN",
               "CN",
               "EMAIL",
               "RA",
               "Operator",
               "STATUS",
               "ROLE",
               ## should be part of the header itself
               ## "HEADER_SIGNATURE",
               "PUBKEY",
               "SCEP_TID",
               "LOA"
              ],
   CERTIFICATE => [
                   "CERTIFICATE_SERIAL",
                   "FORMAT",
                   "DATA",
                   "DN",
                   "CN",
                   "EMAIL",
                   "STATUS",
                   "ROLE",
                   "PUBKEY",
                   "NOTAFTER",
                   "CSR_SERIAL",
                   "LOA"
                  ],
   CA_CERTIFICATE => [
                      ## real serial senseless because at every time zero
                      "CA_CERTIFICATE_SERIAL",
                      "FORMAT",
                      "DATA",
                      "DN",
                      "CN",
                      "EMAIL",
                      "STATUS",
                      "PUBKEY",
                      "NOTAFTER"
                     ],
   CRR => [
           "CRR_SERIAL",
           "REVOKE_CERTIFICATE_SERIAL",
           "SUBMIT_DATE",
           "FORMAT",
           "DATA",
           "REVOKE_CERTIFICATE_DN",
           "CN",
           "EMAIL",
           "RA",
           "Operator",
           "STATUS",
           "REASON",
           "LOA",
           ## should be part of the header itself
           ## "HEADER_SIGNATURE"
          ],
   CRL => [
           "CRL_SERIAL",
           "STATUS",
           "FORMAT",
           "DATA",
           "LAST_UPDATE",
           "NEXT_UPDATE",
          ],
  };

$OpenCA::DBI::STATUS = {
			EXIST       => 1,
			VALID       => 2,
			RENEW       => 3,
			UPDATED     => 4,
			PENDING     => 5,
			APPROVED    => 6,
			SUSPENDED   => 7,
			REVOKED     => 8,
			DELETED     => 9,
			ARCHIVED    => 10,
			EXPIRED     => 11,
                        NONEXISTENT => 12,
                        ANY         => 13,
                        NEW         => 14,
                        SIGNED      => 15
		       };

#########################
## end of DB Org-stuff ##
#########################

#################
## error-codes ##
#################

$OpenCA::DBI::ERROR = {
  
  SUCCESS            => 0,
  DO_NOT_COMMIT      => 11111, # protects the database from commiting if the modul crashs
  UNEXPECTED_ERROR   => 88888,
  ATTACK             => 16666,
                       
  # unspecific errors
  WRONG_DATATYPE               => 10001,
  NO_OBJECT                    => 10002,
  GETBASETYPE_FAILED           => 10003,
  UPDATE_WITHOUT_KEY           => 10004,
  UPDATE_WITHOUT_KEY           => 10005,
  ENTRY_EXIST                  => 10006,
  ENTRY_NOT_EXIST              => 10007,
  UNSUPPORTED_SEARCH_ATTRIBUTE => 10008,
  DB_TYPE_UNKNOWN              => 10009,
  SIGNING_LOG_FAILED           => 10010,
  ITEM_NOT_UNIQUE              => 10011,
  FALSE_MODE                   => 10012,
  FALSE_FAILSAFE               => 10013,
  FALSE_SECOND_CHANCE          => 10014,
  MISSING_PRIMARY_DATABASE     => 10015,
  MISSING_BACKUP_DATABASE      => 10016,
  MISSING_BACKEND              => 10017,
  MISSING_TOOLS                => 10018,
  AUTOCOMMIT                   => 10019,

  CERTIFICATE_TABLE_EXIST      => 10020,
  CA_CERTIFICATE_TABLE_EXIST   => 10021,
  CRR_TABLE_EXIST              => 10022,
  CRL_TABLE_EXIST              => 10023,
  LOG_TABLE_EXIST              => 10024,
  SIGNATURE_TABLE_EXIST        => 10025,
  SEQUENCE_TABLE_EXIST         => 10026,
  RBAC_TABLE_EXIST             => 10027,
  REQUEST_TABLE_EXIST          => 10028,
  
  CANNOT_REMOVE_CA_CERTIFICATE => 10030,
  CANNOT_REMOVE_CRR            => 10031,
  CANNOT_REMOVE_CRL            => 10032,
  CANNOT_REMOVE_LOG            => 10033,
  CANNOT_REMOVE_SIGNATURE      => 10034,
  CANNOT_REMOVE_SEQUENCE       => 10035,
  CANNOT_REMOVE_RBAC           => 10036,
  CANNOT_REMOVE_REQUEST        => 10037,
  CANNOT_REMOVE_CERTIFICATE    => 10038,
  
  CANNOT_CREATE_CA_CERTIFICATE => 10040,
  CANNOT_CREATE_CRR            => 10041,
  CANNOT_CREATE_CRL            => 10042,
  CANNOT_CREATE_LOG            => 10043,
  CANNOT_CREATE_SIGNATURE      => 10044,
  CANNOT_CREATE_SEQUENCE       => 10045,
  CANNOT_CREATE_RBAC           => 10046,
  CANNOT_CREATE_REQUEST        => 10047,
  CANNOT_CREATE_CERTIFICATE    => 10048,
 
  CANNOT_INIT_SEQUENCE         => 10050,

  CANNOT_CREATE_OBJECT         => 10060,
  MISSING_ARG_TABLE            => 10061,
  MISSING_ARG_SERIAL           => 10062,
  MISSING_ARG_DATATYPE         => 10063,
  UNSUPPORTED_OBJECT           => 10064,
  ILLEGAL_STATUS               => 10065,
  ILLEGAL_DATE                 => 10067,
  ILLEGAL_ARGUMENT             => 10068,
  MISSING_DATABASE_PARAMETERS  => 10070,
  MISSING_GETTEXT              => 10071,
  MISSING_DATABASE_TYPE        => 10072,
  MISSING_DATABASE_NAME        => 10073,
  MISSING_DATABASE_USER        => 10074,
  MISSING_DATABASE_PASSWD      => 10075,

  # DB-errors
  # using bitwise-or for DIAGNOSTICS so (ERROR+20000) 
  # & ERROR_MASK -> yes/no
  
  CONNECT_FAILED        => 20001,
  SECOND_CONNECT_FAILED => 20002,
  NO_BACKUP             => 20004,
 
  PREPARE_FAILED        => 20008,
  EXECUTE_FAILED        => 20016,
  
  SELECT_FAILED         => 20032,
  UPDATE_FAILED         => 20064,
  INSERT_FAILED         => 20128,
  DELETE_FAILED         => 20256,
  COMMIT_FAILED         => 20512,
  ROLLBACK_FAILED       => 21024,
  DISCONNECT_FAILED     => 22048,
  SEQUENCE_GENERATOR_FAILED => 24096,

                      };
  
$OpenCA::DBI::MESSAGE = {
  
  0     => "Success",
  88888 => "Unexpected Error",
  16666 => "Possible Attack",
                       
  # unspecific errors
  10001 => "WRONG_DATATYPE",
  10002 => "NO_OBJECT",
  10003 => "GETBASETYPE_FAILED",
  10004 => "UPDATE_WITHOUT_KEY",
  10006 => "ENTRY_EXIST",
  10007 => "ENTRY_NOT_EXIST",
  10008 => "UNSUPPORTED_SEARCH_ATTRIBUTE",
  10009 => "DB_TYPE_UNKNOWN",
  10010 => "SIGNING_LOG_FAILED",
  10011 => "ITEM_NOT_UNIQUE",
  10012 => "FALSE_MODE - normally this means, that you are not using ".
           "secure or standard as the accessmode.",
  10013 => "FALSE_FAILSAFE - failsafe \"on|off\".",
  10014 => "FALSE_SECOND_CHANCE - second_chance \"yes|no\"",
  10015 => "MISSING_PRIMARY_DATABASE",
  10016 => "MISSING_BACKUP_DATABASE",
  10017 => "MISSING_BACKEND",
  10018 => "MISSING_TOOLS",
  10019 => "AUTOCOMMMIT is on.",
  
  10020 => "The table certificate already exists.",
  10021 => "The table ca_certificate already exists.",
  10022 => "The table crr already exists.",
  10023 => "The table crl already exists.",
  10024 => "The table log already exists.",
  10025 => "The table signature already exists.",
  10026 => "The sequence generator already exists.",
  10027 => "The table rbac already exists.",
  10028 => "The table request already exists.",
  
  10030 => "Cannot drop the table ca_certificate.",
  10031 => "Cannot drop the table crr.",
  10032 => "Cannot drop the table crl.",
  10033 => "Cannot drop the table log.",
  10034 => "Cannot drop the table signature.",
  10035 => "Cannot drop the sequence generator.",
  10036 => "Cannot drop the table rbac.",
  10037 => "Cannot drop the table request.",
  10038 => "Cannot drop the table certificate.",
  
  10040 => "Cannot create the table ca_certificate.",
  10041 => "Cannot create the table crr.",
  10042 => "Cannot create the table crl.",
  10043 => "Cannot create the table log.",
  10044 => "Cannot create the table signature.",
  10045 => "Cannot create the sequence generator.",
  10046 => "Cannot create the table rbac.",
  10047 => "Cannot create the table request.",
  10048 => "Cannot create the table certificate.",

  10050 => "Cannot init sequence generator.",  

  10060 => "CANNOT_CREATE_OBJECT",
  10061 => "MISSING_ARG_TABLE",
  10062 => "MISSING_ARG_SERIAL",
  10063 => "MISSING_ARG_DATATYPE",
  10064 => "UNSUPPORTED_OBJECT",
  10065 => "ILLEGAL_STATUS",
  10067 => "ILLEGAL_DATE",
  10068 => "There is an illegal or unsupported argument.",
  10070 => "Missing database parameters (type, name, user or passphrase). Does the passphrase be empty?",
  10071 => "The translation function is missing.",
  10072 => "The database type is missing.",
  10073 => "The database name is missing.",
  10074 => "The database user is missing.",
  10075 => "The database passphrase is missing. There must be a database passphrase.",

  11111 => "Do not commit if the database or the module itself fails.",
  # DB-errors
  # using bitwise-or for DIAGNOSTICS so (ERROR+20000) 
  # & ERROR_MASK -> yes/no
  
  20001 => "CONNECT_FAILED",
  20002 => "SECOND_CONNECT_FAILED",
  20004 => "NO_BACKUP",
 
  20008 => "PREPARE_FAILED",
  20016 => "EXECUTE_FAILED",
  
  20032 => "SELECT_FAILED",
  20064 => "UPDATE_FAILED",
  20128 => "INSERT_FAILED",
  20256 => "DELETE_FAILED",
  20512 => "COMMIT_FAILED",
  21024 => "ROLLBACK_FAILED",
  22048 => "DISCONNECT_FAILED",
  24096 => "SEQUENCE_GENERATOR_FAILED",

                      };
  
## these vars are used to handle crashes during new
$OpenCA::DBI::ERRNO  = $OpenCA::DBI::ERROR->{SUCCESS};

#######################
## end of errorcodes ##
#######################  

################
## modulecodes ##
################

$OpenCA::DBI::MODULETYPE = {
                           UNKNOWN    => 0,
                           CA         => 1,
                           PKIManager => 2,
                           RA         => 3,
                           WebGateway => 4,
                           RAServer   => 5,
                          };
	      
#######################
## end of modulecodes ##
#######################

############################################
## begin of vendordependent databasestuff ##
############################################

## how much spped costs this for 10 databases (compared with 2 databases)?
$OpenCA::DBI::DB = {
                    Pg => {
                           TYPE => {
                                    ## numeric available but not documented
                                    TEXT       => "text",
                                    LONGTEXT   => "text",
                                    TEXT_KEY   => "text",
                                    BIGINT     => "int8",
                                    PRIMARYKEY => "PRIMARY KEY NOT NULL",
                                   },
                           DBI_OPTION => {
                                          RaiseError => 0, 
                                          Taint => 0, 
                                          AutoCommit => 0},
                           LIMIT => "__QUERY__ LIMIT __MAXITEMS__"
                          },
                    mysql => {
                              TYPE => {
                                       ## numeric available but not documented
                                       TEXT       => "TEXT",
                                       LONGTEXT   => "TEXT",
                                       TEXT_KEY   => "VARCHAR (255)",
                                       BIGINT     => "BIGINT",
                                       PRIMARYKEY => "NOT NULL PRIMARY KEY",
                                      },
                              DBI_OPTION => {RaiseError => 0,
                                             AutoCommit => 0},
                              CREATE_TABLE_OPTION => "TYPE=BDB",
                              LIMIT => "__QUERY__ LIMIT __MAXITEMS__"
                             },
                    DB2 => {
                            TYPE => {
                                     TEXT       => "long varchar",
                                     LONGTEXT   => "long varchar",
                                     ## 255 is the limit for a index key in db2
                                     TEXT_KEY   => "varchar (255)",
                                     BIGINT     => "decimal (31, 0)",
                                     PRIMARYKEY => "PRIMARY KEY NOT NULL",
                                    },
                            DBI_OPTION => {
                                           RaiseError => 0, 
                                           Taint => 0, 
                                           AutoCommit => 0},
                            LIMIT => "__QUERY__ FETCH FIRST __MAXITEMS__ ROWS ONLY"
                           },
                    Oracle => {
                            TYPE => {
                                     TEXT       => "varchar2 (1999)",
                                     LONGTEXT   => "LONG",
                                     ## 2000 is the limit for varchar in Oracle7
                                     TEXT_KEY   => "varchar2 (1999)",
                                     BIGINT     => "number (38)",
                                     PRIMARYKEY => "PRIMARY KEY NOT NULL",
                                    },
                            DBI_OPTION => {
                                           RaiseError => 0, 
                                           Taint => 0, 
                                           AutoCommit => 0,
                                           LongReadLen => 32767},
                            LIMIT => "select * from ( __QUERY__ ) where rownum <= __MAXITEMS__"
                           },
                   };

##########################################
## end of vendordependent databasestuff ##
##########################################

$OpenCA::DBI::beginHeader     = "-----BEGIN HEADER-----";
$OpenCA::DBI::endHeader       = "-----END HEADER-----";
$OpenCA::DBI::beginAttribute  = "-----BEGIN ATTRIBUTE-----";
$OpenCA::DBI::endAttribute    = "-----END ATTRIBUTE-----";

## here a special remark
## OpenCA::DBI uses only PEM and such things like SPKAC ...
## binary data like DER is not storable in textfields

my $params = {
	      backend => undef,
	      ## debugging is off !!!
              DEBUG  => 0,
              ERRNO  => 0,
              ERRVAL => "",
	     };

sub new {
  
  # no idea what this should do
  
  my $that = shift;
  my $class = ref($that) || $that;
  
  ## my $self  = $params;
  my $self;
  my $help;
  foreach $help (keys %{$params}) {
    $self->{$help} = $params->{$help};
  }                                                                                          
 
  bless $self, $class;

  ## because db uses variablenames etc. I cannot define it in $params :-(

  # ok here I start ;-)

  my $keys = { @_ };

  $self->debug ("new: Starting to init a new OpenCA::DBI");

  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  # non-DB-specific

  $self->{DEBUG}          = 1 if ($keys->{DEBUG});
  $self->{gettext}        = $keys->{GETTEXT};
  $self->{backend}        = $keys->{SHELL};

  $self->debug ("new: checking for backend");

  if (not $self->{backend})
  {
    $self->set_error ( $OpenCA::DBI::ERROR->{MISSING_BACKEND} );
    return undef;
  }

  $self->debug ("new: defining the class parameters");

  # The minimum I need is remote: 
  # type, host, port, name, user, passwd

  $self->{DB_Type}   = $keys->{DB_Type};
  $self->{DB_Host}   = $keys->{DB_Host};
  $self->{DB_Port}   = $keys->{DB_Port};
  $self->{DB_Name}   = $keys->{DB_Name};
  $self->{DB_User}   = $keys->{DB_User};
  $self->{DB_Passwd} = $keys->{DB_Passwd};

  ## rewrite table names if there is a namespace

  $self->debug ("new: rewrite table spaces if necessary (namespace)");
  if ($keys->{DB_Namespace})
  {
      my $tableprefix = $keys->{DB_Namespace}.".";
      foreach my $table (keys %{$OpenCA::DBI::SQL->{TABLE}})
      {
          $OpenCA::DBI::SQL->{TABLE}->{$table} =
              $tableprefix.$OpenCA::DBI::SQL->{TABLE}->{$table};
      }
  }

  # Check for all neccessary variables to initialize OpenCA:DBI 

  $self->debug ("new: checking the configuration for enough data");

  if ( not $self->{gettext} ) {
    $self->set_error ( $OpenCA::DBI::ERROR->{MISSING_GETTEXT} );
    return undef;
  }

  return $self->set_error ($OpenCA::DBI::ERROR->{MISSING_DATABASE_TYPE})
      if (not $self->{DB_Type});
  return $self->set_error ($OpenCA::DBI::ERROR->{MISSING_DATABASE_USER})
      if (not $self->{DB_User});

  # 2004-10-27 Martin Bartosch <m.bartosch@cynops.de>
  # added special handling for Oracle external authentication
  if ($self->{DB_Type} ne 'Oracle') {
      return $self->set_error ($OpenCA::DBI::ERROR->{MISSING_DATABASE_NAME})
	  if (not $self->{DB_Name});
      return $self->set_error ($OpenCA::DBI::ERROR->{MISSING_DATABASE_PASSWD})
	  if (not $self->{DB_Passwd});
  } else {
      # Oracle does not require a database name if the ORACLE_SID is specified
      # in the environment. 
      # If external authentication is used with Oracle then DB_Name MUST 
      # be empty and ORACLE_SID MUST be set (see DBD::Oracle).
      return $self->set_error ($OpenCA::DBI::ERROR->{MISSING_DATABASE_NAME})
	  unless (($self->{DB_Name} ne '') or ($ENV{ORACLE_SID} ne ''));

      # Oracle allows 'external authentication'. In this case the username
      # is set to '/' and the password is empty.
      return $self->set_error ($OpenCA::DBI::ERROR->{MISSING_DATABASE_PASSWD})
	  unless (($self->{DB_Passwd} ne '') or ($self->{DB_User} eq '/'));
  }

  # The availability of the databases is checked during the operations
  # because I have different accessed databases and perhaps failsafe.
  # I could only stop here if there is no database online but to write
  # here a very big test only for this purpose makes no sense.

  ###########################
  ## vendor dependent part ##
  ###########################

  ## preparing now the database-strings
  ## this is very database dependent
  $self->debug ("new: preparing the database (vendor dependent)");

  ## WARNING I do not include any attributes into the DSN
  ## because I do not know how widely version 1.10 of DBI is used actually
  ## END of WARNING

  $self->{DSN} = "dbi:".$self->{DB_Type}.":";
  if ($self->{DB_Type} eq "Pg") {
    $self->debug ("new: Pg detected");
    $self->{DSN} .= "dbname=".$self->{DB_Name};
    $self->{DSN} .= ";"."host=".$self->{DB_Host} if ($self->{DB_Host});
    $self->{DSN} .= ";"."port=".$self->{DB_Port} if ($self->{DB_Port});
  } elsif ($self->{DB_Type} eq "mysql") {
    $self->debug ("new: mysql detected");
    $self->{DSN} .= "database=".$self->{DB_Name};
    $self->{DSN} .= ";"."host=".$self->{DB_Host} if ($self->{DB_Host});
    $self->{DSN} .= ";"."port=".$self->{DB_Port} if ($self->{DB_Port});
    ## not clean but safe
    $self->{DSN} .= ";mysql_ssl=0";
  } elsif ($self->{DB_Type} =~ /^DB2$/ ) {
    $self->debug ("new: DB2 detected");
    $self->{DSN} .= $self->{DB_Name};
  } elsif ($self->{DB_Type} =~ /^Oracle$/ ) {
    $self->debug ("new: Oracle detected");
    ## you can use tnsname or sidname
    $self->{DSN} .= $self->{DB_Name};
  } else {
   $self->set_error ( $OpenCA::DBI::ERROR->{DB_TYPE_UNKNOWN} );
   return undef;
  }
  $self->debug ("new: DB: ".$self->{DSN});

  ##################################
  ## end of vendor dependent part ##
  ##################################

  $self->debug ("new: OpenCA::DBI should now complete");

  $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} ); 
  return $self;
  
}

sub connect
{
  my $self = shift;

  $self->debug ("connect: connecting to database");

  $self->{STH} = undef;

  ## dsn etc. defined so lets try
  $self->debug ("connect: try to connect");
  $self->{DBH} = DBI->connect_cached ($self->{DSN},
                               $self->{DB_User},
                               $self->{DB_Passwd}, 
                               \%{$OpenCA::DBI::DB->{$self->{DB_Type}}->{DBI_OPTION}});
  if (not $self->{DBH}) {
    ## connect failed try again
    $self->debug ("connect: connect failed");
    $self->set_error ( $DBI::err, $DBI::errstr);
    $self->set_error ( $OpenCA::DBI::ERROR->{CONNECT_FAILED} );
    return undef;
  }

  $self->debug ("connect: Checking AutoCommit to be off ...");
  if ($self->{DBH}->{AutoCommit} == 1) {
    $self->debug ("connect: AutoCommit is on so I'm aborting ...");
    $self->set_error ( $OpenCA::DBI::ERROR->{AUTOCOMMIT} );
    return undef;
  }
  $self->debug ("connect: AutoCommit is off");

  my $charset = setlocale (LC_MESSAGES);
  if ($charset =~ /[^\.]+\.[^\.]+/)
  {
    ## encoding is set
    if( ($self->{DB_Type} =~ /mysql/i ) and ($charset =~ /utf-8/i ) ) {
	    $charset = 'utf8';
    }

    $self->debug ("connect: Setting characterset if the database support it ...");
    $charset =~ s/^[^\.]+\.//g;
    $self->doQuery (QUERY => "SET NAMES '$charset'");
    $self->debug ("connect: Characterset fixed if possible");
  }

  return 1;
}

#############################
## database initialization ##
#############################

sub initDB {
  ## Generate a new db and initialize it allowing the
  ## DB to keep track of the DB status
 
  my $self = shift;
  my $keys = { @_ };
  
  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  my $mode   = $keys->{MODE};

  $self->debug ("initDB: Entering sub initDB");
  $self->debug ("initDB: MODE: $mode");

  ## Accepted modes are
  ## NONE
  ## FORCE    to force table creation
  ## DRYRUN   to get SQL commands

  my ($db, $force, $table, $dsn); 
  # force ?
  $force = 0;
  if ( $mode =~ /^FORCE$/i ) {
      $force = 1;
  } elsif ( $mode eq "DRYRUN") {
      $self->{SQL_SCRIPT} = "";
  }
  $self->debug ("initDB: force: $force");
    
  foreach $table (keys %{$OpenCA::DBI::SQL->{TABLE}}) {
    $self->debug ("initDB: table: $table");
    # check for existence
    $self->debug ("initDB: dsn: ".$self->{DSN});
    $self->debug ("initDB: the folloing debugging-output is for DB2");
    $self->debug ("initDB: ld_library_path: ".$ENV{LD_LIBRARY_PATH});
    $self->debug ("initDB: path: ".           $ENV{PATH});
    $self->debug ("initDB: libpath".          $ENV{LIBPATH});
    $self->debug ("initDB: classpath".        $ENV{CLASSPATH});
    $self->debug ("initDB: oracle_home".      $ENV{ORACLE_HOME});
    if (defined $self->operateTable (DO=>"exist", TABLE => $table, MODE => $mode)) {
      if ($force or $mode eq "DRYRUN") {
        if (not defined $self->operateTable (DO=>"drop", TABLE => $table, MODE => $mode)) {
          $self->set_error ( $OpenCA::DBI::ERROR->{ "CANNOT_REMOVE_".$table } );
          $self->rollback ();
	  return undef;
	}
      } else {
        $self->set_error ( $OpenCA::DBI::ERROR->{ $table."_TABLE_EXIST" } );
        $self->rollback ();
        return undef;
      }
    }
    $self->debug ("initDB: try to create table");
    # create table
    if (not defined $self->operateTable (DO=>"create", TABLE => $table, MODE => $mode)) {
      $self->set_error ( $OpenCA::DBI::ERROR->{ "CANNOT_CREATE_".$table } );
      $self->rollback ();
      return undef;
    }
    $self->debug ("initDB: table created");
  }

  if (not defined $self->commit ()) {
    $self->rollback ();
    return undef;
  }

  $self->debug ("initDB: initDB successful completed");
  $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
  return $self->{SQL_SCRIPT} if ($mode eq "DRYRUN");
  return 1;
}

sub operateTable {
  my $self = shift;
  my $keys = { @_ };
  
  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  my $table     = $keys->{TABLE};
  my $operation = $keys->{DO};
 
  $self->debug ("operateTable: Entering sub operateTable");
 
  # the tables
  my (%tables, $dbh, $statement, $create);

  $self->debug ("operateTable: build the create statements");

  ############################
  ## initial tablestructure ##
  ## change carefully !!!   ##
  ############################

  $self->debug ("operateTable: table: ".$OpenCA::DBI::SQL->{TABLE}->{$table});
  $create = "create table ".$OpenCA::DBI::SQL->{TABLE}->{$table}." (";
  for (my $i=0; 
       $i < scalar (@{$OpenCA::DBI::SQL->{TABLE_STRUCTURE}->{$table}}); 
       $i++) {
    if ($i == 0) {
      $create .= $OpenCA::DBI::SQL->{VARIABLE}->{
                   $OpenCA::DBI::SQL->{TABLE_STRUCTURE}->{$table}[0]
                 }[0]." ".
                 $OpenCA::DBI::DB->{$self->{DB_Type}}->{TYPE}->{
                   $OpenCA::DBI::SQL->{VARIABLE}->{
                     $OpenCA::DBI::SQL->{TABLE_STRUCTURE}->{$table}[0]
                   }[1]
                 }." ".
                 $OpenCA::DBI::DB->{$self->{DB_Type}}->{TYPE}->{PRIMARYKEY};
    } else {
      $create .= ", ".
                 $OpenCA::DBI::SQL->{VARIABLE}->{
                   $OpenCA::DBI::SQL->{TABLE_STRUCTURE}->{$table}[$i]
                 }[0]." ".
                 $OpenCA::DBI::DB->{$self->{DB_Type}}->{TYPE}->{
                   $OpenCA::DBI::SQL->{VARIABLE}->{
                     $OpenCA::DBI::SQL->{TABLE_STRUCTURE}->{$table}[$i]
                   }[1]
                 };
    }
  }
  $create .= ")";
  $create .= " ".$OpenCA::DBI::DB->{$self->{DB_Type}}->{CREATE_TABLE_OPTION}
    if (exists $OpenCA::DBI::DB->{$self->{DB_Type}}->{CREATE_TABLE_OPTION});

  $self->debug ("operateTable: create: $create");

  ############################
  ##      end of            ##
  ## initial tablestructure ##
  ## change carefully !!!   ##
  ############################

  $self->debug ("operateTable: build the statement finally");

  # check table
  my $negator = 0;
  if ($operation eq "create") {
    $statement = $create;
  } elsif ($operation eq "drop") {
    $statement = "drop table ".$OpenCA::DBI::SQL->{TABLE}->{$table};
  } else {
    $statement = "select * from ".$OpenCA::DBI::SQL->{TABLE}->{$table};
  }

  $self->debug ("operateTable: statement: $statement");
  ## can happen if operation performs for sequence generator
  if ($statement eq "") {
    $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
    return 1;
  }
  $self->debug ("operateTable: run the statement");

  # attention not for final use because of the central $OpenCA::DBI::ERROR VARIABLE !!!

  # because of a postgres-bug we must commit all changes here
  if ($self->{DB_Type} =~ /Pg/i) {
      $self->commit();
  }
  if ($keys->{MODE} eq "DRYRUN")
  {
    $self->{SQL_SCRIPT} .= $statement.";";
  } else {
    if (not defined $self->doQuery ( QUERY => $statement )) {
      $self->debug ("operateTable: query failed return undef (EXCEPT OF NEGATOR)");
      # because of a postgres-bug we must rollback here to rescue the following operations
      if ($self->{DB_Type} =~ /Pg/i) {
          $self->rollback();
      }
      if ($negator) {
        $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
        return 1;
      }
      return undef;
    }
  }

  $self->debug ("operateTable: query succeeded return 1 (EXCEPT OF NEGATOR)");
  return undef if ($negator);
  $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
  return 1;
  
}

####################################
## end of database initialization ##
####################################

#################################
## storeItem related functions ##
#################################

## arguments miust be ransmitted via $arguments->{...}
sub storeItem {

  ## Store a provided Item (DATA) provided the exact
  ## DATATYPE. KEY (position in dB) data to match will
  ## be automatically chosen on a DATATYPE basis.
  
  ## The INFORM is used to get the data input format
  ## PEM|DER|NET|SPKAC
  
  my $self = shift;
  my $keys = { @_ };

  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  $self->debug ("storeItem: Entering function storeItem");
 
  my %arguments  = $self->storeItem_getArguments ( @_ );
  ## check for a correct run of storeItem_getArguments
  $self->debug ("storeItem: table: ".$arguments {TABLE});      

  ## errno set by function
  return undef if (not defined $self->storeItem_checkData ( \%arguments ) );

  ## why do we have a sub getTimeString ???
  $arguments {datetime} = getTimeString ();

  ##   declare variables
  my $rv;

  ## normal insertion of object
  if ($arguments {MODE} =~ /UPDATE/i) { 
    return undef if (not defined $self->storeItem_update ( \%arguments ));
  } else {
    return undef if (not defined $self->storeItem_insert ( \%arguments ));
  }

  ###########################################
  ## be warned: a serial can be a zero !!! ##
  ###########################################
  $self->debug ("storeItem: succeeded - KEY: ".$arguments {KEY});  
  $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
  return $arguments {KEY};
    
}

## parse the arguments
sub storeItem_getArguments {

  my $self = shift;
  my $keys = { @_ };

  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  my %result;

  $self->debug ("storeItem_getArguments: Entering storeItem_getArguments");

  $result{MODE}       = $keys->{MODE};
  $result{MODULETYPE} = $keys->{MODULETYPE};
  $result{MODULE}     = $keys->{MODULE};
  $result{object}     = $keys->{OBJECT};

  $result {TABLE} = $self->getTable ($keys->{DATATYPE});
  $self->debug ("storeItem_getArguments: table: ".$result {TABLE});

  ## get all searchable attributes
  @{$result {attributes}} = 
  	$self->getSearchAttributes( DATATYPE=>$result {TABLE} );
  
  my $attr;

  my $object = $keys->{OBJECT};

  ## storeItem

  for $attr ( @{$result {attributes}} ) {
 
    $self->debug ("storeItem_getArguments: attribute: $attr");      
    ##     Here we distinguish between parameteres in the header
    if( $attr =~ /^(LOA|RA|ROLE|CSR_SERIAL|OPERATOR|SCEP_TID)$/i ) {
      if ( defined $object->getParsed()->{HEADER}->{$attr} ) {
        $result {$attr} = $object->getParsed()->{HEADER}->{$attr};
        $self->debug ("storeItem_getArguments: value: ".$result {$attr});      
      }
    } elsif( $attr =~ /^(DN|PUBKEY|REVOKE_CERTIFICATE_DN|REVOKE_CERTIFICATE_SERIAL|REASON|SUBMIT_DATE)$/i ) {
      if ( defined $object->getParsed()->{$attr} ) {
        $result {$attr} = $object->getParsed()->{$attr};
        $self->debug ("storeItem_getArguments: value: ".$result {$attr});      
      }
    } elsif( $attr =~ /^(EMAIL)$/i ) {
      if ( defined $object->getParsed()->{EMAILADDRESSES} ) {
        $result {EMAILADDRESS} = "";
        $result {EMAIL} = "";
        foreach my $email (@{$object->getParsed()->{EMAILADDRESSES}})
        {
            $result {EMAILADDRESS} .= "," if ($result {EMAILADDRESS});
            $result {EMAILADDRESS} .= $email;
            $result {EMAIL} .= "," if ($result {EMAIL});
            $result {EMAIL} .= $email;
        }
        $self->debug ("storeItem_getArguments: value: ".$result {EMAILADDRESS});      
      }
    } else {
      if ( defined $object->getParsed()->{DN_HASH}->{$attr} ) {
        $result {$attr} = $object->getParsed()->{DN_HASH}->{$attr}[0];
        $self->debug ("storeItem_getArguments: value: ".$result {$attr});      
      }
    }
  }

  ## if it is a cert then we have to set notafter to handle expired certs
  if ($object->getParsed()->{NOTAFTER}) {
  	$result {NOTAFTER} = $self->{backend}->getNumericDate (
					$object->getParsed()->{NOTAFTER});
  }

  ## if it is a CRL then we have to set next_update to handle expired certs
  if ($object->getParsed()->{NEXT_UPDATE}) {
  	$result {NEXT_UPDATE} = $self->{backend}->getNumericDate (
  					$object->getParsed()->{NEXT_UPDATE});
  };

  if ($object->getParsed()->{NEXT_UPDATE}) {
  	$result {LAST_UPDATE} = $self->{backend}->getNumericDate (
  					$object->getParsed()->{LAST_UPDATE});
  };

  ## enforce status
  $result {STATUS} = $self->getStatus ( STATUS   => $result {STATUS},
                                        DATATYPE => $keys->{DATATYPE} );
  if ($result {STATUS} =~ /EXPIRED/i) {
    $result {STATUS} = "VALID";
  }
  if (not $result {STATUS}) {
    delete ($result {STATUS});
    $self->debug ("storeItem_getArguments: status: erased because empty");
  } else {
    $self->debug ("storeItem_getArguments: status: ".$result {STATUS});
  }

  ## storage formats
  ##   If the data is convertible, let us have only one internal
  ##   format to handle with
  $result {INFORM} = $keys->{INFORM};
  if ( not $result {INFORM} ) {
    $result {INFORM} = "PEM";
  }
  $self->debug ("  storeItem_getArguments: inform: ".$result {INFORM});

  $result {KEY} = $result {object}->getSerial ($result {TABLE});
  $result {CONVERTED} = $object->getItem ();
  if( $result {TABLE} =~ /(REQUEST|CRR)/i ) {
    $result {FORMAT} = $object->getParsed()->{TYPE};
  } else {
    $result {FORMAT} = "PEM";
  }
  $self->debug ("storeItem_getArguments: KEY:".$result{KEY});
  $self->debug ("storeItem_getArguments: format: ".$result {FORMAT});
  $self->debug ("  storeItem_getArguments: converted: ".$result {CONVERTED});  

  $self->debug ("storeItem_getArguments: object->getParsed hash:");
  for my $h (keys %{$object->getParsed()}) {
      $self->debug ("storeItem_getArguments: object-attribute: $h");
      $self->debug ("storeItem_getArguments: object-value: ".$object->getParsed ()->{$h});
  }

  $self->debug ("storeItem_getArguments: function succesfully finished");

  $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
  return %result;
}

## checks all the available data
## warning: function must called with storeItem_checkData { \%arguments} !!!
sub storeItem_checkData {

  my $self = shift;
  my $arguments = $_[0];

  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  ## check data
  $self->debug ("storeItem_checkData: Entering storeItem_checkData");
  
  ##   determinate table
  if (not $arguments->{TABLE}) {
    # this is not allowed (for recovery too)
    $self->set_error ($OpenCA::DBI::ERROR->{WRONG_DATATYPE});
    return undef;
  }
  
  ## if VALID_* then take mode and in doubt use "UPDATE"
  ## else UPDATE
  ## special handling of CRLs
  ## all modes are now enforced !!!
  if ( not $arguments->{MODE} ) {
    if ( $arguments->{STATUS} =~ /^(VALID|NEW|RENEW)$/ ) {
      if ((uc $arguments->{MODE}) ne "UPDATE") {
        $arguments->{MODE} = "INSERT";
      } elsif ($arguments->{TABLE} eq "CRL") {
        ## blocks updating CRLs
        ## create a new one please
        $arguments->{MODE} = "INSERT";
      } else {
        $arguments->{MODE} = "UPDATE";
      }
    } elsif ( $arguments->{TABLE} eq "CRL" ) {
      ## blocks updating CRLs
      ## create a new one please
      $arguments->{MODE} = "INSERT";
    } else {
      $arguments->{MODE} = "UPDATE";
    }
  }

  ## if no moduletype then unknown
  if (not $arguments->{MODULETYPE}) {
    $arguments->{MODULETYPE} = "UNKNOWN";
    if ($arguments->{MODULE}) {
      $arguments->{MODULE} .= " - UNKNOWN MODULETYPE";
    } else {
      $arguments->{MODULE} = "UNKNOWN MODULETYPE AND UNKNOWN MODULE";
    }
  }
  ## if no module then "UNKNOWN MODULE"
  if (not $arguments->{MODULE}) {
    $arguments->{MODULE} = "UNKNOWN MODULE";
  }

  ##   if we have no object then return
  if (not $arguments->{object}) {
    $self->set_error ($OpenCA::DBI::ERROR->{NO_OBJECT});
    return undef;
  }

  my $query;
  my @bind_values;

  ## is item existent and unique ?
  $self->debug ("storeItem_checkData: check for existence of item");  
  $query = "select * from ".$OpenCA::DBI::SQL->{TABLE}->{$arguments->{TABLE}}." where ". 
           $OpenCA::DBI::SQL->{VARIABLE}->{$arguments->{TABLE}."_SERIAL"}[0]."=?";
  undef @bind_values;
  $bind_values[0] = $arguments->{KEY};

  $self->debug ("storeItem_checkData: doQuery: $query");  
  if ( not defined $self->doQuery ( QUERY => $query, BIND_VALUES => \@bind_values) ) {
    $self->debug ("storeItem_checkData: doQuery failure detected");  
    $self->set_error ( $OpenCA::DBI::ERROR->{SELECT_FAILED} );
    return undef;
  }
 
  my $rv = $self->{STH}->fetchrow_arrayref;

  ## normal insertion of object
  if (defined $rv and $rv) {
    if (defined $arguments->{MODE} and ($arguments->{MODE} =~ /INSERT/)) {
      $self->debug ("storeItem_checkData: illegal insert");  
      $self->set_error ( $OpenCA::DBI::ERROR->{ENTRY_EXIST} );
      return undef;
    } else {
      $arguments->{MODE} = "UPDATE";
    }
  } else {
    if (defined $arguments->{MODE} and ($arguments->{MODE} =~ /UPDATE/)) {
      $self->debug ("storeItem_checkData: illegal update");  
      $self->set_error ( $OpenCA::DBI::ERROR->{ENTRY_NOT_EXIST} );
      return undef;
    } else {
      $arguments->{MODE} = "INSERT";
    }
  }

  $self->debug ("storeItem_checkData: data is complete");  
  $self->debug ("storeItem_checkData: leaving function successfully");  

  $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
  return 1;
}

sub storeItem_update {

  my $self = shift;
  my $arguments = $_[0];

  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  my $query;
  my @bind_values;

  $self->debug ("storeItem_upate: update mode");  
  ## item existent
  ## ok this could be CRR, Request or Certificate
  ##   verify actual state (check signatures)
  ##     -- (I think that is not the job of the DBI-Module - so it is not implemented)
  ##   check all input data
  ##     -- this should be done earlier 
  ##     -- (attriubtes are checked directly before storing them)
  ##   is this statechange allowed (for example to prevent multiple DNs)
  ##     -- actually not implemented (do statechange only)
  ##   try statechange
  ##     -- prepare query
  $self->debug ("storeItem_update: prepare query");  
  $query = "update ".$OpenCA::DBI::SQL->{TABLE}->{$arguments->{TABLE}}." set ".
  ##     -- adding data, format, status
    $OpenCA::DBI::SQL->{VARIABLE}->{DATA}[0]."=?, ".
    $OpenCA::DBI::SQL->{VARIABLE}->{FORMAT}[0]."=? ";
  undef @bind_values;
  $bind_values [0] = $arguments->{CONVERTED};
  $bind_values [1] = $arguments->{FORMAT};
  ##     -- adding searchattributes - never update a date !!!
  ##     -- getSearchAttributes does not return date as attribute
  for my $attr ( @{$arguments->{attributes}} ) {
    # so transformation should be correct for SQL
    if ($attr !~ /^KEY$/ and $arguments->{$attr}) {
      $query .= ", ".$OpenCA::DBI::SQL->{VARIABLE}->{$attr}[0]."=?";
      $bind_values [scalar @bind_values] = $arguments->{$attr};
    }
  }
  if ($arguments->{TABLE} =~ /CERTIFICATE/i) {
    $query .= ", ".$OpenCA::DBI::SQL->{VARIABLE}->{NOTAFTER}[0]."=?";
    $bind_values [scalar @bind_values] = $arguments->{NOTAFTER};
  }

  ##     -- set serials
  $query .= " where ".
    $OpenCA::DBI::SQL->{VARIABLE}->{$arguments->{TABLE}."_SERIAL"}[0]."=?";
  $bind_values [scalar @bind_values] = 
    $arguments->{KEY};

  foreach my $help (@bind_values) {
    $self->debug ("storeItem_update: bind_values: $help");
  }
  $self->debug ("storeItem_update: query complete, call doQuery");  

  if ( not defined $self->doQuery ( QUERY => $query, BIND_VALUES => \@bind_values) ) {
    $self->set_error ( $OpenCA::DBI::ERROR->{UPDATE_FAILED} );
    return undef;
  }

  $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
  return 1;
}

sub storeItem_insert {

  my $self = shift;
  my $arguments = $_[0];

  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  my $query;
  my @bind_values;

  $self->debug ("storeItem_insert: Entering storeItem_insert");  
  ## INSERT
  ##   mode='update' is allowed in the future to support revoking non-existing request 
  ##   check all input data
  ##     -- this should be done earlier 
  ##     -- (attriubtes are checked directly before storing them)
  ##   is this statechange allowed (for example renewal nonexistent request)
  ##     -- actually not implemented (do statechange only)
  ##   create row with all additional attributes
  ##     -- prepare query
  $self->debug ("storeItem_insert: prepare query");  
  $query = "insert into ".
    $OpenCA::DBI::SQL->{TABLE}->{$arguments->{TABLE}}." ( ".
    $OpenCA::DBI::SQL->{VARIABLE}->{$arguments->{TABLE}."_SERIAL"}[0].", ".
    $OpenCA::DBI::SQL->{VARIABLE}->{DATA}[0].", ".
    $OpenCA::DBI::SQL->{VARIABLE}->{FORMAT}[0]." ";
  for my $attr ( @{$arguments->{attributes}} ) {
    $query .= ", ".$OpenCA::DBI::SQL->{VARIABLE}->{$attr}[0]
      if ($attr !~ /^KEY$/ and $arguments->{$attr});
  }
  if ($arguments->{TABLE} =~ /CERTIFICATE/i) {
    $query .= ", ".$OpenCA::DBI::SQL->{VARIABLE}->{NOTAFTER}[0];
  }
  $query .= ") VALUES (";
  ##     -- adding data, format, status
  $query .= " ?, ?, ?";
  undef @bind_values;
  $bind_values [0] = $arguments->{KEY};
  $bind_values [1] = $arguments->{CONVERTED};
  $bind_values [2] = $arguments->{FORMAT};
  $self->debug ("storeItem_insert: try to parse header");  
  ##     -- adding searchattributes - never update a date !!!
  ##     -- getSearchAttributes does not return date as attribute
  for my $attr ( @{$arguments->{attributes}} ) {
    if ($attr !~ /^KEY$/ and $arguments->{$attr}) {
      # so transformation should be correct for SQL
      $self->debug ("storeItem_insert: attr: $attr");
      $query .= ", ?";
      $bind_values [scalar @bind_values] = $arguments->{$attr};
    }
  }
  if ($arguments->{TABLE} =~ /CERTIFICATE/i) {
    $query .= ", ?";
    $bind_values [scalar @bind_values] = $arguments->{NOTAFTER};
  }
  $query .= ")";
      
  foreach my $help (@bind_values) {
    $self->debug ("storeItem_insert: bind_values: $help");
  }
  $self->debug ("storeItem_insert: query complete, call doQuery");  

  if ( not defined $self->doQuery (QUERY => $query, BIND_VALUES => \@bind_values) ) {
    $self->set_error ( $OpenCA::DBI::ERROR->{INSERT_FAILED} );
    return undef;
  }

  $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
  return 1;

}

####################################
## end of storeItem related stuff ##
####################################

sub updateStatus {
  
  my $self = shift;
  my $keys = { @_ };
 
  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  ## get the key
  my $key;
  if ($keys->{DATATYPE} =~ /CA_CERTIFICATE/i) {
    my $converted = $keys->{OBJECT}->getPEMHeader();
    $converted   .= $keys->{OBJECT}->getPEM();
    $converted   .= $keys->{OBJECT}->getParsed()->{KEY};
  }
  if ($keys->{OBJECT}) {
    $key = $keys->{OBJECT}->getSerial ($keys->{DATATYPE});
  } else {
    return undef;
  }

  ## verify status (or existance)
  if (not $self->getItem ( DATATYPE => $keys->{DATATYPE}, KEY => $key )) {
    $self->set_error ( $OpenCA::DBI::ERROR->{ENTRY_NOT_EXIST} );
    return undef;
  }

  ## change tatus
  return $self->storeItem ( MODE     => "UPDATE",
                            OBJECT   => $keys->{OBJECT},
                            DATATYPE => $keys->{NEWTYPE}
                          );
  
}

sub updateCRLStatus {

	my $self = shift;
	my $query = "";

	## Update the status of CRLs by updating the status
	## field fixing inconsistencies between the last_update
	## next_update and status columns;

	my $today = gmtime;
	my @bind_values;

	## Get the Numeric Version of the current GMT time
	my $numTodayValue = $self->{backend}->getNumericDate( $today );

	## First Step: check for CRLs that have the VALID
	## status but the last_update or the next_update are
	## non consistent with the current date
	
	@bind_values = ();
  	$query = "update ".
		$OpenCA::DBI::SQL->{TABLE}->{CRL} . 
			" set status = 'EXPIRED' where ( last_update > ? ) " .
				"or (next_update < ? )";

	push( @bind_values, $numTodayValue );
	push( @bind_values, $numTodayValue );
    	# $bind_values [scalar @bind_values] = $numTodayValue;
    	# $bind_values [scalar @bind_values] = $numTodayValue;

  	$self->debug ("updateCRLStatus: doQuery: $query");  
  	if ( not defined $self->doQuery ( QUERY => $query, 
					BIND_VALUES => \@bind_values) ) {
    		$self->debug ("updateCRLStatus: doQuery failure detected");  
    		$self->set_error ( $OpenCA::DBI::ERROR->{UPDATE_FAILED} );
		return undef;
	};

	## Second Step: check for CRLs that have the EXPIRED
	## status but the last_update and the next_update fields
	## would identify a valid CRL

	@bind_values = ();
  	$query = "update ".
		$OpenCA::DBI::SQL->{TABLE}->{CRL} . 
			" set status = 'VALID' where ( last_update < ? ) " .
				"and (next_update > ? )";

	push( @bind_values, $numTodayValue );
	push( @bind_values, $numTodayValue );
    	# $bind_values [scalar @bind_values] = $numTodayValue;
    	# $bind_values [scalar @bind_values] = $numTodayValue;

  	$self->debug ("updateCRLStatus: (2) doQuery: $query");  
  	if ( not defined $self->doQuery ( QUERY => $query, 
					BIND_VALUES => \@bind_values) ) {
    		$self->debug ("updateCRLStatus: doQuery failure detected 2");
    		$self->set_error ( $OpenCA::DBI::ERROR->{UPDATE_FAILED} );
		return undef;
	};

	return 1;
}

sub updateObjStatus {

	my $self = shift;
	my $query = "";

	my $keys  = { @_ };

	## Update the status of Objects by updating the status
	## field fixing inconsistencies between their validity
	## status columns;

	my $today = gmtime;
	my @bind_values;

	if( $keys->{TABLE} eq "" ) {
		## ERROR: we need a table
		return undef;
	};

	## Get the Numeric Version of the current GMT time
	my $numTodayValue = $self->{backend}->getNumericDate( $today );

	## First Step: check for Objs that have the VALID
	## status but the last_update, next_update, notafter are
	## non consistent with the current date
	
	@bind_values = ();
  	$query = "update ".
		$OpenCA::DBI::SQL->{TABLE}->{$keys->{TABLE}} . 
			" set status = 'EXPIRED' where ";

	if ( $keys->{TABLE} =~ /CERTIFICATE/i ) {

		$query .= " ( " .
			$OpenCA::DBI::SQL->{VARIABLE}->{NOTAFTER}[0] .
			" < ? ) and ( " . 
			$OpenCA::DBI::SQL->{VARIABLE}->{STATUS}[0] .
			" like ? ) ";

		push( @bind_values, $numTodayValue );
		push( @bind_values, 'VALID' );
	} elsif ( $keys->{TABLE} =~ /CRL/i ) {

		$query .= " (( " .
			$OpenCA::DBI::SQL->{VARIABLE}->{LAST_UPDATE}[0] . 
			"  > ? ) or ( " .
			$OpenCA::DBI::SQL->{VARIABLE}->{NEXT_UPDATE}[0] . 
			" < ? )) and ( " . 
			$OpenCA::DBI::SQL->{VARIABLE}->{STATUS}[0] . 
			" like ? ) ";

		push( @bind_values, $numTodayValue );
		push( @bind_values, $numTodayValue );
		push( @bind_values, 'EXPIRED' );
	} else {
		## Unknown TABLE!
		return undef;
	}

  	$self->debug ("updateObjStatus: doQuery: $query");  
  	if ( not defined $self->doQuery ( QUERY => $query, 
					BIND_VALUES => \@bind_values) ) {
    		$self->debug ("updateObjStatus: doQuery failure detected");  
    		$self->set_error ( $OpenCA::DBI::ERROR->{UPDATE_FAILED} );
		return undef;
	};

	## Second Step: check for OBJs that have the EXPIRED
	## status but the last_update, next_update fields or notafter
	## would identify a valid CRL

	@bind_values = ();

  	$query = "update ".
		$OpenCA::DBI::SQL->{TABLE}->{$keys->{TABLE}} . 
			" set status = 'VALID' where ";

	if ( $keys->{TABLE} =~ /CERTIFICATE/i ) {

		$query .= " ( " .
			$OpenCA::DBI::SQL->{VARIABLE}->{NOTAFTER}[0] .
			" > ? ) and ( " .
			$OpenCA::DBI::SQL->{VARIABLE}->{STATUS}[0] .
			" like ? ) ";

		push( @bind_values, $numTodayValue );
		push( @bind_values, 'EXPIRED' );

	} elsif( $keys->{TABLE} =~ /CRL/ ) {

		$query .= " (( " .
			$OpenCA::DBI::SQL->{VARIABLE}->{LAST_UPDATE}[0] . 
				" < ? ) and ( " .
			$OpenCA::DBI::SQL->{VARIABLE}->{NEXT_UPDATE}[0] . 
				" < ? )) and ( " . 
			$OpenCA::DBI::SQL->{VARIABLE}->{STATUS}[0] . 
			" like ? ) ";

		push( @bind_values, $numTodayValue );
		push( @bind_values, $numTodayValue );
		push( @bind_values, 'EXPIRED' );
	}	

  	$self->debug ("updateObjStatus: (2) doQuery: $query");  
  	if ( not defined $self->doQuery ( QUERY => $query, 
					BIND_VALUES => \@bind_values) ) {
    		$self->debug ("updateObjStatus: doQuery failure detected 2");
    		$self->set_error ( $OpenCA::DBI::ERROR->{UPDATE_FAILED} );
		return undef;
	};

	return 1;
}

sub getItem {

  ## Get an Item provided the exact data to match:
  ## DATATYPE, KEY. Will return, if exists, the data
  ## on the corresponding dB file.
  
  ## Actually, as the search function, the returned
  ## value will be a referenced object (REQ, X509,
  ## CRL, etc... ).
  
  my $self = shift;
  
  $self->debug ("getItem: Entering sub getItem");

  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  my ( $fileName, $item, $body, $header, $hash, $tmpBody );
  
  my %arguments = $self->getArguments ( @_ );
  return undef if (not %arguments);

  my $query;
  my @bind_values;

  ## support for direct access to latest CRL
  if ((not defined $arguments{KEY}) && ($arguments{TABLE} ne "CRL")) {
    $self->set_error ( $OpenCA::DBI::ERROR->{ MISSING_ARG_SERIAL } );
    return undef;
  }

  $self->debug ("getItem: data complete");  

  ## I hope the people only search for Certs, Requests and CRRs
  ## mmh this is impossible
  $query = "select * from ".$OpenCA::DBI::SQL->{TABLE}->{$arguments{TABLE}}." where ";
  if (defined $arguments{KEY}) {
    if ($arguments{TABLE} =~ /CA_CERTIFICATE/i)
    {
        $query .= "(".$OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[0]." like ?)";
    } else {
        $query .= "(".$OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[0]."=?)";
    }
    if ($arguments{STATUS}) {
      $query .= " and (".$OpenCA::DBI::SQL->{VARIABLE}->{STATUS}[0]." like '".$arguments{STATUS}."')";
      $query .= $self->handleExpiredCert ( \%arguments ) if ($arguments{NOTAFTER});
    }
    $bind_values [0] = $arguments{KEY};
  } else {
    ## to support most actual CRL (you can use it find the last cert etc. too)
    $query .= $OpenCA::DBI::SQL->{VARIABLE}->{DATE}[0]." like (select MAX(".
      $OpenCA::DBI::SQL->{VARIABLE}->{LAST_UPDATE}[0].") from ".$OpenCA::DBI::SQL->{TABLE}->{$arguments{TABLE}}.")";
    undef @bind_values;
  }
  $self->debug ("getItem: query: $query");

  my ($rv);

  ## do_query
  if ( not defined $self->doQuery (QUERY => $query, BIND_VALUES => \@bind_values) ) {
    $self->set_error ( $OpenCA::DBI::ERROR->{SELECT_FAILED} );
    return undef;
  }

  $self->debug ("getItem: check that there is a non-empty result");

  ## STH->rows does not work with Oracle (DB2 does not like rows sometimes too)
  my $arrayref = $self->{STH}->fetchrow_arrayref;
  if (not defined $arrayref or not $arrayref) {
    return undef;
  }

  return $self->getResultItem (ARGUMENTS => \%arguments, ARRAYREF => $arrayref);

}

sub getNextItem {

  ## Get an Item provided the exact data to match:
  ## DATATYPE, KEY. Will return, if exists, the data
  ## on the corresponding dB file.
  
  ## Actually, as the search function, the returned
  ## value will be a referenced object (REQ, X509,
  ## CRL, etc... ).
  
  my $self = shift;

  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  my %arguments = $self->getArguments ( @_ );
  return undef if (not %arguments);

  my $query;
  my @bind_values; 

  ## Let us make some needed check
  if (not $arguments{TABLE}) {
    $self->set_error ( $OpenCA::DBI::ERROR->{ MISSING_ARG_TABLE } );
    return undef;
  }
  if ( not exists $arguments{KEY} ) {
    if ($arguments{TABLE} eq "CA_CERTIFICATE" or
        $arguments{TABLE} eq "CA")
    {
        $arguments{KEY} = "";
    } else {
        $arguments{KEY} = -1;
    }
    return undef;
  }

  ## I hope the people only search for Certs, Requests and CRRs
  ## mmh this is impossible
  my $and = 0;
  undef @bind_values;
  $query = "select MIN(".$OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[0].")".
    " from ".$OpenCA::DBI::SQL->{TABLE}->{$arguments{TABLE}};
  if (defined $arguments{KEY}) {
    $query .= " where (".
              $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[0]." > ? ".
              ")";
    $bind_values [0] = $arguments{KEY};
    $and = 1;
  }
  if ($arguments{STATUS}) {
    if ($and) {
      $query .= " and ";
    } else {
      $query .= " where ";
    }
    ## the like is necessary for IBM DB2
    $query .= " (".$OpenCA::DBI::SQL->{VARIABLE}->{STATUS}[0]." like ?)";
    $bind_values [scalar @bind_values] = $arguments{STATUS};
    $query .= $self->handleExpiredCert ( \%arguments ) if ($arguments{NOTAFTER}); 
  }
  $self->debug ("getNextItem: query: $query");

  ## do_query
  if ( not defined $self->doQuery (QUERY => $query, BIND_VALUES => \@bind_values) ) {
    $self->set_error ( $OpenCA::DBI::ERROR->{SELECT_FAILED} );
    return undef;
  }
  my $ref = $self->{STH}->fetchrow_arrayref;

  $self->debug ("getNextItem: arg: ".$arguments{KEY});
  $self->debug ("getNextItem: result: ".$ref);
  $self->debug ("getNextItem: result: ".$ref->[0]);

  if (defined $ref and exists $ref->[0] and $ref->[0] ne "") {
    return $self->getItem (DATATYPE => $arguments{TABLE},
                           KEY      => $ref->[0],
                           MODE     => $arguments{MODE}
                          );
  } else {
    if (defined $ref or
        not defined $self->{STH}->err) {
      $self->debug ("getNextItem: there is no next item");
      $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
      return undef; ## no errors no results
    } else { # mmh this should never happen here
      $self->set_error ( $OpenCA::DBI::ERROR->{SELECT_FAILED} );
      return undef;
    }
  }
  
  ## never reached
  $self->set_error ( $OpenCA::DBI::ERROR->{UNEXPECTED_ERROR} );
  return undef;

}

sub getPrevItem {

  ## Get an Item provided the exact data to match:
  ## DATATYPE, KEY. Will return, if exists, the data
  ## on the corresponding dB file.
  
  ## Actually, as the search function, the returned
  ## value will be a referenced object (REQ, X509,
  ## CRL, etc... ).
  
  my $self = shift;
  
  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  my %arguments = $self->getArguments ( @_ );
  return undef if (not %arguments);

  my $query;
  my @bind_values;

  ## Let us make some needed check
  if (not $arguments{TABLE}) {
    $self->set_error ( $OpenCA::DBI::ERROR->{MISSING_ARG_TABLE} );
    return undef;
  }
  if (not $arguments{KEY} and ($arguments{KEY} != 0)) {
    $self->set_error ( $OpenCA::DBI::ERROR->{MISSING_ARG_SERIAL} );
    return undef;
  }

  ## I hope the people only search for Certs, Requests and CRRs
  ## mmh this is impossible
  ## Attention date is not numeric !!!
  $query = "select MAX(".$OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[0].")".
    " from ".$OpenCA::DBI::SQL->{TABLE}->{$arguments{TABLE}};
  undef @bind_values;

  my $where = 0;
  if (defined $arguments{KEY})
  {
      $where = 1;
      $query .= " where (".$OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[0]."< ? ".")";
      $bind_values [0] = $arguments{KEY}; # date is not numeric
  }
  if ($arguments{STATUS}) {
    ## the like is necessary for IBM DB2
    if ($where)
    {
        $query .= " and ";
    } else {
        $query .= " where ";
    }
    $query .= " (".$OpenCA::DBI::SQL->{VARIABLE}->{STATUS}[0]." like ?)";
    $bind_values [scalar @bind_values] = $arguments{STATUS};
    $query .= $self->handleExpiredCert ( \%arguments ) if ($arguments{NOTAFTER});
  }

  my $rv;

  ## do_query
  if ( not defined $self->doQuery (QUERY => $query, BIND_VALUES => \@bind_values) ) {
    $self->set_error ( $OpenCA::DBI::ERROR->{SELECT_FAILED} );
    return undef;
  }

  my $ref = $self->{STH}->fetchrow_arrayref;

  if (defined $ref) {
    # copy by value !!!
    $arguments{KEY} = $ref->[0];
  }

  if (defined $arguments{KEY}) {
    return $self->getItem (DATATYPE => $arguments{TABLE},
                           KEY      => $arguments{KEY},
                           MODE     => $arguments{MODE}
                          );
  } else {
    if (defined $ref or
        not defined $self->{STH}->err) {
      $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
      return 0; ## no errors no results
    } else { # mmh this should never happen here
      $self->set_error ( $OpenCA::DBI::ERROR->{SELECT_FAILED} );
      return undef;
    }
  }
  
  ## never reached
  $self->set_error ( $OpenCA::DBI::ERROR->{UNEXPECTED_ERROR} );
  return undef;

}

sub deleteItem {
  ## it is not neccessary to delete an object if it is revoked/marked as deleted
  return 1;
}

sub destroyItem {
  ## attention this code is not for normal use only for recovery reasons !
  ## if you want to say a request is deleted than storeItem with
  ## STATUS = $OpenCA::DBI::status->{DELETED}

  ## Get an Item provided the exact data to match:
  ## DATATYPE, KEY. Will return, if exists, the data
  ## on the corresponding dB file.
  
  ## Actually, as the search function, the returned
  ## value will be a referenced object (REQ, X509,
  ## CRL, etc... ).
  
  my $self = shift;
  my $keys = { @_ };
  
  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  my $type  = $keys->{DATATYPE};
  my $table = $self->getTable ($type);
  
  my $serial = $keys->{KEY};  ## Key passed when stored item

  my $query;
  my @bind_values;

  ## Let us make some needed check
  if (not $table) {
    $self->set_error ( $OpenCA::DBI::ERROR->{MISSING_ARG_TABLE} );
    return undef;
  }
  if (not $serial and ($serial != 0)) {
    $self->set_error ( $OpenCA::DBI::ERROR->{MISSING_ARG_SERIAL} );
    return undef;
  }

  ## I hope the people only search for Certs, Requests and CRRs
  ## mmh this is impossible
  ## Attention date is not numeric !!!
  $query = "delete from ".$OpenCA::DBI::SQL->{TABLE}->{$table}." where ".
    $OpenCA::DBI::SQL->{VARIABLE}->{$table."_SERIAL"}[0]."= ? ".
  undef @bind_values;
  $bind_values [0] = $serial; # date is not numeric

  ## do_query
  if ( not defined $self->doQuery (QUERY => $query, BIND_VALUES => \@bind_values) ) {
    $self->set_error ( $OpenCA::DBI::ERROR->{DELETE_FAILED} );
    return undef;
  }

  ## successful
  $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
  return 1;

}

sub keyList {

  ## Get the list of Serials for a specific type of data
  
  my $self = shift;
  
  # $self->{DEBUG} = 1;

  $self->debug ("keysList: Entering function keysList");

  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  my %arguments = $self->getArguments ( @_ );
  return undef if (not %arguments);
  
  my $query;
  my @bind_values;    
  my @retList = ();
  
  ## Let us make some needed check
  if (not $arguments{TABLE}) {
    $self->set_error ( $OpenCA::DBI::ERROR->{MISSING_ARG_TABLE} );
    return undef;
  }
  my $count;

  ## I hope the people only search for Certs, Requests and CRRs
  ## mmh this is impossible
  ## Attention date is not numeric !!!
  $query = "select " .
  	$OpenCA::DBI::SQL->{VARIABLE}->{$arguments {TABLE}."_SERIAL"}[0] .
		" from ".$OpenCA::DBI::SQL->{TABLE}->{$arguments{TABLE}};
  undef @bind_values;
  if ($arguments{STATUS}) {
    ## the like is necessary for IBM DB2
    $query .= " where ".$OpenCA::DBI::SQL->{VARIABLE}->{STATUS}[0]." like ?";
    $bind_values [0] = $arguments{STATUS};
    $query .= $self->handleExpiredCert ( \%arguments ) if ($arguments{NOTAFTER});
  }
  
  $self->debug ("keysList Query::$query\n");

  ## do_query
  if (not defined $self->doQuery (QUERY => $query, 
	  				BIND_VALUES => \@bind_values) ) {
    $self->set_error ( $OpenCA::DBI::ERROR->{SELECT_FAILED} );
    return undef;
  }

  while ( (my $h =  $self->{STH}->fetchrow_arrayref) ) {
      $self->debug ("keysList: item: ".$h->[0]);
      push ( @retList, $h->[0] );
  }

  $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );

  $self->set_error (32322323, "keysList Query::$query\n");

  $self->{DEBUG} = 0;

  return @retList;
}

sub elements {

  ## Get an Item provided the exact data to match:
  ## DATATYPE, KEY. Will return, if exists, the data
  ## on the corresponding dB file.
  
  ## Actually, as the search function, the returned
  ## value will be a referenced object (REQ, X509,
  ## CRL, etc... ).
  
  my $self = shift;
  
  $self->debug ("elements: Entering function elements");

  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  my %arguments = $self->getArguments ( @_ );
  return undef if (not %arguments);
  
  my $query;
  my @bind_values;    
  
  ## Let us make some needed check
  if (not $arguments{TABLE}) {
    $self->set_error ( $OpenCA::DBI::ERROR->{MISSING_ARG_TABLE} );
    return undef;
  }
  my $count;

  ## I hope the people only search for Certs, Requests and CRRs
  ## mmh this is impossible
  ## Attention date is not numeric !!!
  $query = "select count(*)  from ".$OpenCA::DBI::SQL->{TABLE}->{$arguments{TABLE}};
  undef @bind_values;
  if ($arguments{STATUS}) {
    ## the like is necessary for IBM DB2
    $query .= " where ".$OpenCA::DBI::SQL->{VARIABLE}->{STATUS}[0]." like ?";
    $bind_values [0] = $arguments{STATUS};
    $query .= $self->handleExpiredCert ( \%arguments ) if ($arguments{NOTAFTER});
  }
  
  ## do_query
  if (not defined $self->doQuery (QUERY => $query, BIND_VALUES => \@bind_values) ) {
    $self->set_error ( $OpenCA::DBI::ERROR->{SELECT_FAILED} );
    return undef;
  }

  $count = $self->{STH}->fetchrow_arrayref->[0];

  $self->debug ("elements: leaving function successfully");
  $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
  return $count;
}

sub searchItems { # new one !!!

  ## Get an Item provided the exact data to match:
  ## DATATYPE, KEY. Will return, if exists, the data
  ## on the corresponding dB file.
  
  ## Actually, as the search function, the returned
  ## value will be a referenced object (REQ, X509,
  ## CRL, etc... ).
  
  my $self = shift;
  
  my $keys = { @_ };

  $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );

  my (@retList, @objRetList);
  my ( $items );

  $self->debug ("searchItems: Entering function searchItems");
 
  $self->debug ("searchItems: OpenCA::DBI::errno: $errno");
  my %arguments = $self->getArguments ( @_ );
  $self->debug ("searchItems: OpenCA::DBI::errno: $errno");
  return undef if ($errno);
  $self->debug ("searchItems: dbi-status:".$arguments {STATUS});

  my $query;
  my @bind_values;
  undef @bind_values;

  ## Let us make some needed check
  if (not $arguments {TABLE}) {
    $self->set_error ( $OpenCA::DBI::ERROR->{MISSING_ARG_TABLE} );
    return undef;
  }
  
  ## let us prepare the question
  my $and = 0;
  my $mode = "*";

  if ( $arguments { MODE } eq "COUNT" ) {
	$mode = "count(*)";
  } elsif ( $arguments { MODE } eq "KEYLIST" ) {
	$mode = $OpenCA::DBI::SQL->{VARIABLE}->{$arguments {TABLE}.
							"_SERIAL"}[0];
  } else {
	$mode = '*';
  }

  $query = "select $mode from " . 
		$OpenCA::DBI::SQL->{TABLE}->{$arguments {TABLE}};

  ## check for unique identifier scan
  if ( $arguments{KEY} and ($arguments {TABLE} =~ /^CERTIFICATE/ ) ) {
    if ($and) {
      $query .= " and ";
    } else {
      $query .= " where ";
      $and = 1;
    }
    $query .= "(".$OpenCA::DBI::SQL->{VARIABLE}->{CERTIFICATE_SERIAL}[0]."=?)";
    ## prepare bind_values
    $bind_values [scalar (@bind_values)] = $arguments{KEY};
    ## delete from keys
    delete $arguments{KEY};
  }

  $self->debug(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<\n");
  $self->debug("EXPIRES_BEFORE => " . $arguments {EXPIRES_BEFORE} . "\n");
  $self->debug("EXPIRES_AFTER => " . $arguments {EXPIRES_AFTER} . "\n");
  $self->debug("TABLE => " . $arguments {TABLE} . "\n");
  $self->debug(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<");

  if ( $arguments {EXPIRES_BEFORE} and 
	  			($arguments {TABLE} =~ /^CERTIFICATE/ ) ) {
    if ($and) {
      $query .= " and ";
    } else {
      $query .= " where ";
      $and = 1;
    }
    $query .= "(".$OpenCA::DBI::SQL->{VARIABLE}->{NOTAFTER}[0]." < ? )";
    ## prepare bind_values
    $bind_values [scalar (@bind_values)] = $arguments{EXPIRES_BEFORE};
    ## delete from keys
    delete $arguments{EXPIRES_BEFORE};
  }

  if ( $arguments {EXPIRES_AFTER} and 
	  			($arguments {TABLE} =~ /^CERTIFICATE/ ) ) {
    if ($and) {
      $query .= " and ";
    } else {
      $query .= " where ";
      $and = 1;
    }
    $query .= "(".$OpenCA::DBI::SQL->{VARIABLE}->{NOTAFTER}[0]." > ? )";
    ## prepare bind_values
    $bind_values [scalar (@bind_values)] = $arguments{EXPIRES_AFTER};
    ## delete from keys
    delete $arguments{EXPIRES_AFTER};
  }

  if ( $arguments {EXPIRES_BEFORE} and 
	  			($arguments {TABLE} =~ /^CRL/ ) ) {
    if ($and) {
      $query .= " and ";
    } else {
      $query .= " where ";
      $and = 1;
    }
    $query .= "(".$OpenCA::DBI::SQL->{VARIABLE}->{NEXT_UPDATE}[0]." < ? )";
    ## prepare bind_values
    $bind_values [scalar (@bind_values)] = $arguments{EXPIRES_BEFORE};
    ## delete from keys
    delete $arguments{EXPIRES_BEFORE};
  }

  if ( $arguments {EXPIRES_AFTER} and 
	  			($arguments {TABLE} =~ /^CRL/ ) ) {
    if ($and) {
      $query .= " and ";
    } else {
      $query .= " where ";
      $and = 1;
    }
    $query .= "(".$OpenCA::DBI::SQL->{VARIABLE}->{NEXT_UPDATE}[0]." > ? )";
    ## prepare bind_values
    $bind_values [scalar (@bind_values)] = $arguments{EXPIRES_AFTER};
    ## delete from keys
    delete $arguments{EXPIRES_AFTER};
  }

  if ( $arguments{FROM} ) {
 
    if ($and) {
      $query .= " and ";
    } else {
      $query .= " where ";
      $and = 1;
    }
    $query .= "(".$OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE} .
			"_SERIAL"}[0]." > ? ) ";
    $bind_values [scalar (@bind_values)] = $arguments{FROM};
    delete $arguments{FROM};
  }

  if (( $arguments {TABLE} =~ /^CRL$/i ) and 
	  			( $arguments {STATUS} =~ /^EXPIRED$/i )) {
	my $today = gmtime;

	if ($and) {
		$query .= " and ";
	} else {
		$query .= " where ";
		$and = 1;
	}
	$query .= "(".$OpenCA::DBI::SQL->{VARIABLE}->{NEXT_UPDATE}[0] .
							" < ? ) ";
	$bind_values [scalar (@bind_values)] = 
			$self->{backend}->getNumericDate($today);
				# 100000000;

	# The status is handled automatically with the next_update field
	# madwolf change
	delete $arguments { STATUS };
  }
  delete $arguments{NEXT_UPDATE};

  if (( $arguments {TABLE} =~ /^CRL$/i ) and 
	  			( $arguments {STATUS} =~ /^VALID$/i )) {

	my $today = gmtime;
	my $numericDate = $self->{backend}->getNumericDate( $today );
			  # 100000000;

	if ($and) {
      		$query .= " and ";
    	} else {
      		$query .= " where ";
      		$and = 1;
    	}
    	$query .= "(" . $OpenCA::DBI::SQL->{VARIABLE}->{LAST_UPDATE}[0] 
								." < ? ) ";
    	$bind_values [scalar (@bind_values)] = $numericDate;

    	$query .= " and (" . $OpenCA::DBI::SQL->{VARIABLE}->{NEXT_UPDATE}[0] 
								." > ? ) ";
    	$bind_values [scalar (@bind_values)] = $numericDate;

	# The status is handled automatically with the next_update field
	# madwolf change
	delete $arguments { STATUS };
  }
  delete $arguments{LAST_UPDATE};

  if ( $arguments { ITEMS } ) { 
	$items = $arguments{ITEMS};
	delete $arguments{ITEMS};
  }

  $self->debug ("searchItems: query now: $query");

  ## For every keyword let us get the list of values
  my @attributes = $self->getSearchAttributes (DATATYPE=>$arguments {TABLE});

  my $attr;
  my $array = 0;
  my @arrayVal = ();

  for $attr ( @attributes ) {
    $self->debug ("searchItems: scan attribute: $attr");

    if ($arguments {$attr}) {
      $self->debug ("searchItems: attribute's content: ".$arguments {$attr});

      ## get from keys
      if ($and) {
	$query .= " and ";
      } else {
	$query .= " where ";
	$and = 1;
      }
      if ( ref($arguments {$attr}) =~ /ARRAY/ ) {
	      $self->debug ( "ARGUMENT IS AN ARRAY!!!\n");
	      $array = 1;
	      @arrayVal = @{ $arguments{$attr}};
      } else {
	      $array = 0;
	      @arrayVal = ( $arguments{$attr} );
      }

      if ($attr =~ /^KEY$/) {
        $query .= "(".
          $OpenCA::DBI::SQL->{VARIABLE}->{$arguments {TABLE}."_SERIAL"}[0];
	  
	if( $array eq "1" ) {
		$query .= " in ( ";
		foreach my $tmpVal ( @arrayVal ) {
			$query .= " ? ,";
		}
		$query =~ s/,$//;
		$query .= " ) ";
	} else {
	  $query .= "= ? ";
	}
	$query .= ") ";

      } elsif ($OpenCA::DBI::SQL->{VARIABLE}->{$attr}[1] =~ /BIGINT/i) {
        $self->debug ("searchItems: BIGINT: ".
                      $attr." --&gt; ".
                      $OpenCA::DBI::SQL->{VARIABLE}->{$attr}[1]);
        $query .= "( ".$OpenCA::DBI::SQL->{VARIABLE}->{$attr}[0];

	if( $array eq "1" ) {
		$query .= " in ( ";
		foreach my $tmpVal ( @arrayVal ) {
			$query .= " ? ,";
		}
		$query =~ s/,$//;
		$query .= " ) ";
	} else {
	  $query .= " = ? ";
	}

	$query .= ") ";

      } else {
        $self->debug ("searchItems: TEXT: ".
                      $attr." --&gt; ".
                      $OpenCA::DBI::SQL->{VARIABLE}->{$attr}[1]);

        $query .= "(".$OpenCA::DBI::SQL->{VARIABLE}->{$attr}[0];

	if( $array eq "1" ) {
		$query .= " in ( ";
		foreach my $tmpVal ( @arrayVal ) {
			$query .= " ? ,";
		}
		$query =~ s/,$//;
		$query .= " ) ";
	} else {
	  $query .= " like ? ";
	}

	$query .= ") ";

      }

      if ($attr =~ /STATUS/i) {
        $query .= $self->handleExpiredCert ( \%arguments ) 
					if ($arguments{NOTAFTER});
      }

      if ( $array eq "1" ) {
	      push ( @bind_values, @arrayVal );
      } else {
	      ## prepare bind_values
      	      $bind_values [scalar (@bind_values)] = $arguments {$attr};
      };

      ## delete from keys
      delete $arguments{$attr};
    }
  }

  ## order by key to support correct listings
  $query.= " order by ".
           $OpenCA::DBI::SQL->{VARIABLE}->{$arguments {TABLE}."_ORDERBY"}[0];

  $self->debug ("searchItems: query: $query");

  # Limit the results!!!!
  if ( $items ) {
  	my $hquery = $OpenCA::DBI::DB->{$self->{DB_Type}}->{LIMIT};

	$items = -1 if ( not $items );

  	$hquery =~ s/__QUERY__/$query/;
  	$hquery =~ s/__MAXITEMS__/$items/;
  	$query  = $hquery; 
  }

  ## madwolf DEBUG
  # print STDERR "searchItems: query => $query\n";
  # foreach my $help (@bind_values) {
  #   print STDERR "searchItems: bind_values: $help\n";
  # };

  ## Let's update the status of CRLs before executing the Search!
  if( $arguments { TABLE } =~ /(CRL|CERTIFICATE)/i ) {

	## madwolf DEBUG
	# print STDERR "searchItems: updating CRL table!\n";
	$self->updateObjStatus( TABLE => $arguments { TABLE } );
  };

  my $rv = 0;

  ## do_query
  $rv = $self->doQuery (QUERY => $query, BIND_VALUES => \@bind_values);
  if (not defined $rv ) {
    $self->set_error ( $OpenCA::DBI::ERROR->{SELECT_FAILED} );
    return undef;
  } else {

    ## original "stolen" from OpenCA::DB sub searchItemDB

    $self->debug ("searchItems: errstr(undef is OK): ".
    						$self->{STH}->errstr());
    $self->debug ("searchItems: rows (this is buggy in DBD::DB2 and " . 
    					"DBD::Oracle)): ".  $self->{STH}->rows);

    ## $self->{STH}->rows."<br>\n" if ($self->{DEBUG});
    ## Results
    ## be warned fetchrow_hashref does not work with DB2
    my $counter = 0;
    while ( (my $h =  $self->{STH}->fetchrow_arrayref) ) {
	# $counter++;

	$self->debug ("searchItems: item: ".$h->[0]);
	push ( @retList, $h->[0] );
    }

    ## because of searchItemDB + searchItem 
    ## but what it is doing ?
    if( $arguments {MODE} eq "ROWS" ) {
      $self->debug ("searchItems: leaving function successfully");
      $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
      return ($#retList+1);
    } elsif ( $arguments { MODE } eq "COUNT" ) {
      $self->debug ("searchItems: leaving function successfully");
      $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
      return ( $retList[0] );
    } elsif ( $arguments { MODE } eq "KEYLIST" ) {
      $self->debug ("searchItems: leaving function successfully");
      $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
      return ( @retList );
    }

    for my $i (@retList) {
      my $obj;
	
      next if ( not $obj = $self->getItem( DATATYPE => $arguments{TABLE},
                                           STATUS   => $arguments{STATUS}, 
                                           KEY      => $i ));

      $self->debug ("searchItems: add an object to the returnlist");

      push( @objRetList, $obj );
    }

    $self->debug ("searchItems: leaving function successfully");
    $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
    return @objRetList;

  }

  ## never reached
  $self->set_error ( $OpenCA::DBI::ERROR->{UNEXPECTED_ERROR} );
  return undef;

}

sub listItems {
  my $self = shift;
  my $keys = { @_ };

  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  my $dataType	= $keys->{DATATYPE};
  my $items 	= $keys->{ITEMS};
  my $from 	= $keys->{FROM};
  ## my $to 		= $keys->{TO};
  my $mode	= $keys->{MODE};

  my ( @ret, $retItems, $i, $tmpObj );

  $self->debug ("listItems: Entering function listItems");

  if( not $dataType ) {
    $self->set_error ( $OpenCA::DBI::ERROR->{MISSING_ARG_DATATYPE} );
    return undef;
  }

  ## check sql-type of key of table
  my $table = $self->getTable ($dataType);
  if (not $table) {
    $self->set_error ( $OpenCA::DBI::ERROR->{MISSING_ARG_TABLE} );
    return undef;
  }
  my $pseudo_sql_type = 
    $OpenCA::DBI::SQL->{VARIABLE}->{
      $OpenCA::DBI::SQL->{TABLE_STRUCTURE}->{$table}[0]
    }[1];
  my $is_int = 0;
  $is_int = 1 if ( $pseudo_sql_type =~ /INT/i );

  ## if all items are requested
  $items = -1 if (not $items);

  my %arguments = $self->getArguments ( @_ );
  return undef if (not %arguments);

  my $query;
  my @bind_values; 

  ## Let us make some needed check
  if (not $arguments{TABLE}) {
    $self->set_error ( $OpenCA::DBI::ERROR->{ MISSING_ARG_TABLE } );
    return undef;
  }

  ## I hope the people only search for Certs, Requests and CRRs
  ## mmh this is impossible
  my $and = 0;
  undef @bind_values;
  $query = "select * from ".$OpenCA::DBI::SQL->{TABLE}->{$arguments{TABLE}};
  if (defined $from) {
    $query .= " where (".
              $OpenCA::DBI::SQL->{VARIABLE}->{$arguments{TABLE}."_SERIAL"}[0]." >= ? ".
              ")";
    $bind_values [0] = $from;
    $and = 1;
  }
  if ($arguments{STATUS}) {
    if ($and) {
      $query .= " and ";
    } else {
      $query .= " where ";
    }
    ## the like is necessary for IBM DB2
    $query .= " (".$OpenCA::DBI::SQL->{VARIABLE}->{STATUS}[0]." like ?)";
    $bind_values [scalar @bind_values] = $arguments{STATUS};
    $query .= $self->handleExpiredCert ( \%arguments ) if ($arguments{NOTAFTER}); 
  }
  $query .= " order by ".
            $OpenCA::DBI::SQL->{VARIABLE}->{$arguments {TABLE}."_ORDERBY"}[0];

  $self->debug ("listItems: query: $query");

  ## build query limit
  my $hquery = $OpenCA::DBI::DB->{$self->{DB_Type}}->{LIMIT};
  $hquery =~ s/__QUERY__/$query/;
  $hquery =~ s/__MAXITEMS__/$items/;
  $query  = $hquery;
  $self->debug ("listItems: limited query: $query");

  if( $arguments { TABLE } =~ /(CRL|CERTIFICATE)/i ) {
	  $self->updateObjStatus( TABLE => $arguments { TABLE } );
  };

  ## do_query
  if ( not defined $self->doQuery (QUERY => $query, 
	  				BIND_VALUES => \@bind_values) ) {
    $self->set_error ( $OpenCA::DBI::ERROR->{SELECT_FAILED} );
    return undef;
  }

  my $arrayref;
  while ($arrayref = $self->{STH}->fetchrow_arrayref) {

    $tmpObj = $self->getResultItem (ARGUMENTS => \%arguments, ARRAYREF => $arrayref);

    if ( $mode ne "RAW" ) {
      push (@ret, $tmpObj);
    } else {
      push (@ret, $tmpObj->getSerial($dataType));
    }

    $self->debug ("listItems: added item ".$tmpObj->getSerial($dataType)." to result");
  }

  $self->debug ("listItems: completed successful");
  $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
  return @ret;
}

##################################################
## original unchanged functions from OpenCA::DB ##
##################################################

# For logical reasons I do not touch this code
# This is original from OpenCA::DB so please
# ask Massimiliano Pala if anything is unclear
# madwolf@openca.org
#

sub rows {

	## Returns the number of item matching the request. You can search
	## for generic DATATYPE such as CERTIFICATE|REQUEST|CRL
	## or restricted type (EXPIRED_CERTIFICATE|REVOKED_CERTIFICATE|
	## VALID_CERTIFICATE...
	##
	## This function should be used in conjunction with searching function
	## use the elements sub instead if you wish to know how many specific
	## dB elements are there (such as VALID_CERTIFICATES, etc ... )

	my $self = shift;
	my $keys = { @_ };

	return $self->searchItems( MODE=>"ROWS", @_ );
}

sub byKey { $a->{KEY} <=> $b->{KEY} };

sub hash2txt {
  my $self  = shift;

  my $keys = $_[0];
 
  my $record = "";
  my ( $i, $key, $val );
 
  for $key (keys %{$keys}) {
    $self->debug ("hash2txt: key=$key val=".$keys->{$key});
    if ($keys->{$key} =~ /\n/) {
      ## multicolumn
      $record .= "$key=\n".$OpenCA::DBI::beginAttribute.
                 "\n".$keys->{$key}."\n".$OpenCA::DBI::endAttribute."\n";
    } else {
      ## single column
      $record .= "$key=".$keys->{$key}."\n";
    }
  }
  $record =~ s/(\n)$//;

  $self->debug ("hash2txt: record=$record"); 
  return $record;
}

## actually completely unused
## but present for compatibility reasons
sub txt2hash {
        my $self = shift;
        my $keys = { @_ };
 
        my $txt      = $keys->{TXT};
 
        my ( $ret, $key, $val, $line );

        ## begin of copy from X509.pm

        my $active_multicolumn = 0;
        foreach my $i ( split ( /\n/, $txt ) ) {
                if ($active_multicolumn) {
                  ## multicolumn
                  if ($i =~ /^$OpenCA::DBI::endAttribute$/) {
                    ## end of multicolumn
                    $active_multicolumn = 0;
                  } else {
                    ## additional data
                    $ret->{key} .= "\n".$i;
                  }
                } elsif ($i =~ /^$OpenCA::DBI::beginAttribute$/) {
                  ## begin of multicolumn
                  $active_multicolumn = 1;
                } else {
                  ## no multicolumn
                  ## if multicolumn then $ret->{key} is initially empty)                          $i =~ s/\s*=\s*/=/;
                  ( $key, $val ) = ( $i =~ /(.*)\s*=\s*(.*)\s*/ );
                  $ret->{$key} = $val;
                }
        } 

        ## end of copy from X509.pm

        return $ret;
} 

#########################################################
## end of original unchanged functions from OpenCA::DB ##
#########################################################

###################
## new functions ##
###################

## new extended function for getSearchAttributes which does not
## return the index (SERIAL, KEY or DATE)
sub getSearchAttributes {
        my $self = shift;
        my $keys = { @_ };

       $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

        my $type = $keys->{DATATYPE};
        my @ret = ();

        if ( not $type ) {
          $self->set_error ( $OpenCA::DBI::ERROR->{MISSING_ARG_DATATYPE} );
          return undef;
        }

        if ( $type =~ /REQUEST/ ) {
                @ret = ( "KEY", 
                         "STATUS",
                         "DN",
                         "CN",
                         "EMAIL",
                         "RA",
                         "OPERATOR",
                         "ROLE",
                         "PUBKEY",
                         "SCEP_TID",
                         "LOA" );
        } elsif ( $type =~ /CA_CERTIFICATE/ ) {
                @ret = ( "KEY",
                         "STATUS",
                         "DN",
                         "CN",
                         "EMAIL",
                         "PUBKEY" );
        } elsif ( $type =~ /CERTIFICATE/ ) {
                @ret = ( "KEY",
                         "STATUS",
                         "DN",
                         "CN",
                         "EMAIL",
                         "ROLE",
                         "PUBKEY",
                         "CSR_SERIAL",
			 "EXPIRES_BEFORE",
			 "EXPIRES_AFTER",
                         "LOA" );
        } elsif ( $type =~ /CRR/ ) {
                @ret = ( "KEY",
                         "STATUS",
                         "REVOKE_CERTIFICATE_SERIAL",
                         "REVOKE_CERTIFICATE_DN",
                         "CN",
                         "EMAIL",
                         "RA",
                         "OPERATOR",
                         "SUBMIT_DATE",
                         "REASON",
                         "LOA" );
        } elsif ( $type =~ /CRL/ ) {
                @ret = ( "KEY",
                         "STATUS",
                         "LAST_UPDATE",
                         "NEXT_UPDATE" );
        };

        $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
        return @ret;
}

## returns now iso-time
sub getTimeString {

	my $self = shift;
	my  ( $ret, @T );

	@T = gmtime( time() );
        ## iso is yyyy-mm-dd hh:mm:ss
	$ret = sprintf( "%4.4d-%2.2d-%2.2d %2.2d:%2.2d:%2.2d",
			 $T[5]+1900, $T[4], $T[3], $T[2], $T[1], $T[0] );
	# $ret = sprintf( "%4.4d-%2.2d-%2.2d %2.2d:%2.2d:%2.2d,%6.6d",
	#		 $T[5]+1900, $T[4], $T[3], $T[2], $T[1], $T[0], ${$} );

	return $ret;

}

## parse the arguments for all functions
sub getArguments {

  my $self = shift;
  my $keys = { @_ };
  my $check;

  my %result;

  if (exists $keys->{DEBUG}) {
      $self->{DEBUG} = $keys->{DEBUG};
      delete $keys->{DEBUG};
  }

  $self->debug ("getArguments: entering function");

  foreach my $key (keys %$keys) {
      $check->{$key} = $keys->{$key};
      $self->debug ("getArguments: check: $key=".$check->{$key});
  }

  $result {FROM} = $check->{FROM};
  delete $check->{FROM};
 
  $result {ITEMS} = $check->{ITEMS};
  delete $check->{ITEMS};
 
  $result {TABLE} = $self->getTable ($keys->{DATATYPE});
  $result {MODE}  = $keys->{MODE};
  delete $check->{MODE};
  $self->debug ("getArguments: TABLE:".$result {TABLE});
  $self->debug ("getArguments: MODE:".$result {MODE});

  ## get all searchable attributes
  my @attributes = $self->getSearchAttributes( DATATYPE => $result {TABLE} );
  my $attr;

  for $attr ( @attributes ) {
    
    	$self->debug ("getArguments: attribute: $attr");

    	if ($attr =~ /^EMAIL$/ and not $keys->{$attr}) {
        	$result {EMAIL} = $keys->{EMAILADDRESS};
        	delete $check->{EMAILADDRESS};
    	} else {
        	$result {$attr} = $keys->{$attr};
        	delete $check->{$attr};
    	}

    	$self->debug ("getArguments: value: ".$result {$attr});      
  }

  ## enforce status
  $result {STATUS} = $self->getStatus ( STATUS   => $result {STATUS},
                                          DATATYPE => $keys->{DATATYPE} );

  ## madwolf -- DEBUG
  # print STDERR "getArguments::status is " . $result{STATUS} . "\n";
  # print STDERR "getArguments::table is " . $result{TABLE} . "\n";

  if ( ($result {STATUS} =~ /EXPIRED/i) and
                                   ( $result {TABLE} =~ /CRL/) ) {

     my $today = gmtime;

     $self->debug ("getArguments: handling EXPIRED_CRL");      
     # my @T = gmtime( time() );
     # my $t_time = sprintf( "%4.4d%2.2d%2.2d%2.2d%2.2d%2.2d",
     #          $T[5]+1900, $T[4]+1, $T[3], $T[2], $T[1], $T[0] );
     $result {NEXT_UPDATE} = 
		$self->{backend}->getNumericDate ($today);
  } 

  if (not $result {STATUS}) {
    $self->debug ("getArguments: no STATUS present");
    delete ($result {STATUS});
  } else {
    $self->debug ("getArguments: status: ".$result {STATUS});
  }
  delete $check->{STATUS};
  delete $check->{DATATYPE};

  ## New Parameters - used only for CRL and CERTS for now
  delete $check->{EXPIRES_BEFORE};
  delete $check->{EXPIRES_AFTER};

  ## madwolf -- DEBUG
  # print STDERR "getArguments::NEW status is " . $result{STATUS} . "\n\n";

  if (scalar (keys %$check)) {
      $self->debug ("getArguments: ILLEGAL ARGUMENT");
      foreach my $key (keys %$check) {
          $self->debug ("getArguments: $key: ".$check->{$key});
      }
      $self->set_error ( $OpenCA::DBI::ERROR->{ILLEGAL_ARGUMENT} );
      return undef;
  }

  $self->debug ("getArguments: completed successful");
  return %result;

}


## this is a standardinterface to get the table from the original
## datatype. so we can use the normal interface of OpenCA::DB
sub getTable {
  my $self = shift;
  my $datatype = $_[0];
  
  my $ret;
  
  if ( $datatype =~ /CA_CERTIFICATE/ ) {
    $ret = "CA_CERTIFICATE";
  } elsif ( $datatype =~ /CERTIFICATE/ ) {
    $ret = "CERTIFICATE";
  } elsif ( $datatype =~ /CRL/ ) {
    $ret = "CRL";
  } elsif ( $datatype =~ /REQUEST/ ) {
    $ret = "REQUEST";
  } elsif ( $datatype =~ /CRR/ ) {
    $ret = "CRR";
  } else {
    ## Unsupported DATATYPE
    $ret = "";
  }
  
  return $ret;  
}

## this function support to work with old and new code
## this means that I check for STATUS and if it is not existent
## I try to extract it from the datatype
sub getStatus {
  my $self = shift;
  my $keys = { @_ };

  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  my $status   = $keys->{STATUS};
  my $datatype = $keys->{DATATYPE};

  $self->debug ("getStatus: Entering function");

  if ($status) {
    $self->debug ("getStatus: status predefined: $status");
    ## check for legal status
    if ( $OpenCA::DBI::STATUS->{$status} ) {
      $self->debug ("getStatus: legal status (leaving function)");
      $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
      return $status;
    } else {
      $self->debug ("getStatus: illegal status (leaving function)");
      $self->set_error ( $OpenCA::DBI::ERROR->{ILLEGAL_STATUS} );
      return undef;
    }
  } else {
    $self->debug ("getStatus: no status given using datatype: $datatype");
    ## try to extract status from datatype
    ## erase all behind the first "_" incl. this "_" itself
    my $old = $datatype;
    $datatype =~ s/_.*//g;
    $datatype = $old if ($datatype =~ /^CA$/i);
    $datatype = "" if ($old eq $datatype); 
    $datatype = uc $datatype;
    $self->debug ("getStatus: given mode is now: $datatype");
    ## check for legal status
    if ( $datatype =~ /^$/ )
    {
      $self->debug ("getStatus: no status (leaving function)");
      $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
      return $datatype;
    } elsif ( $OpenCA::DBI::STATUS->{$datatype} ) {
      $self->debug ("getStatus: legal status (leaving function)");
      $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
      return $datatype;
    } else {
      $self->debug ("getStatus: illegal status (leaving function)");
      $self->set_error ( $OpenCA::DBI::ERROR->{ILLEGAL_STATUS} );
      return undef;
    }
  }
}

sub build_date {
  my $self = shift;
  my $date = $_[0];
  my %help;
  my $new_date;

  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  ##  Mar 10 19:36:45 2001 GMT

  ## Month
  if ( $date =~ /^ *JAN/i ) {
    ##  january
    $help {MONTH} = "01";
  } elsif ( $date =~ /^ *FEB/i ) {
    ## february
    $help {MONTH} = "02";
  } elsif ( $date =~ /^ *MAR/i ) {
    ## march
    $help {MONTH} = "03";
  } elsif ( $date =~ /^ *APR/i ) {
    ## april
    $help {MONTH} = "04";
  } elsif ( $date =~ /^ *MAY/i ) {
    ## may
    $help {MONTH} = "05";
  } elsif ( $date =~ /^ *JUN/i ) {
    ## june
    $help {MONTH} = "06";
  } elsif ( $date =~ /^ *JUL/i ) {
    ## july
    $help {MONTH} = "07";
  } elsif ( $date =~ /^ *AUG/i ) {
    ## august
    $help {MONTH} = "08";
  } elsif ( $date =~ /^ *SEP/i ) {
    ## september
    $help {MONTH} = "09";
  } elsif ( $date =~ /^ *OCT/i ) {
    ## october
    $help {MONTH} = "10";
  } elsif ( $date =~ /^ *NOV/i ) {
    ## november
    $help {MONTH} = "11";
  } elsif ( $date =~ /^ *DEC/i ) {
    ## december
    $help {MONTH} = "12";
  } else {
    ## return illegal
    $self->set_error ( $OpenCA::DBI::ERROR->{ILLEGAL_DATE} );
    return undef;
  }

  ## day
  $date =~ s/^ *//;
  $date = substr ($date, 4, length ($date)-4);
  $help {DAY} = substr ($date, 0, 2);

  ## hour
  $help {HOUR} = substr ($date, 3, 2);

  ## minute
  $help {MINUTE} = substr ($date, 6, 2);

  ## second
  $help {SECOND} = substr ($date, 9, 2);

  ## year
  $help {YEAR} = substr ($date, 12, 4);

  ## build date
  $new_date = $help {YEAR}.
              $help {MONTH}.
              $help {DAY}.
              $help {HOUR}.
              $help {MINUTE}.
              $help {SECOND};

  $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
  return $new_date; 

}

sub doQuery {
  my $self = shift;
  my $keys = { @_ };

  $self->set_error ( $OpenCA::DBI::ERROR->{DO_NOT_COMMIT} );

  $self->debug ("doQuery: entering function");

  # these variables are in-vars
  my $query     = $keys->{QUERY};
  my @bind_values = @{$keys->{BIND_VALUES}} if ($keys->{BIND_VALUES});

  $self->debug ("doQuery: query: $query");

  foreach my $help (@bind_values) {
    $self->debug ("doQuery: bind_values: $help");
    ## madwolf -- DEBUG
    $self->debug ("doQuery: bind_values: $help");
  }

  ## query empty so not a DB-failure
  return undef if ($query eq "");

  ## prepare
  $self->debug ("doQuery: prepare statement");
  $self->{STH} = $self->{DBH}->prepare ($query);
  if (not exists $self->{STH} or not defined $self->{STH} or
      						not ref $self->{STH}) {
    	## necessary for Oracle
    	$self->debug ("doQuery: prepare failed");
    	$self->debug ("doQuery: query: $query");
    	$self->debug ("doQuery: prepare returned undef");
    	$self->set_error ( $OpenCA::DBI::ERROR->{PREPARE_FAILED} );
    	return undef;
  }

  ## execute
  $self->debug ("doQuery: execute statement");

  my $result = $self->{STH}->execute (@bind_values);
  if (defined $result) {
    $self->debug ("doQuery: execute succeeded (leaving function - $result)");
    $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
    return $result;
  } else {
    $self->debug ("doQuery: execute failed (leaving function)");
    $self->set_error ( $OpenCA::DBI::ERROR->{EXECUTE_FAILED} );
    return undef;
  }
}

## this function is neccessary because DB2 does not support
## the function fetchrow_hashref
sub getResultHash {

  my $self = shift;
  my $keys = { @_ };
  my %result;

  $self->debug ("getResultHash: entring function");

  for (my $i = 0;
       $i < scalar (@{$OpenCA::DBI::SQL->{TABLE_STRUCTURE}->{$keys->{TABLE}}});
       $i++) {
    $result {$OpenCA::DBI::SQL->{TABLE_STRUCTURE}->{$keys->{TABLE}}[$i]} =
      $keys->{ARRAY}->[$i];
    $self->debug ("getResultHash: column: ".
                  $OpenCA::DBI::SQL->{TABLE_STRUCTURE}->{$keys->{TABLE}}[$i]);
    $self->debug ("getResultHash: value: ".$keys->{ARRAY}->[$i]);
  }

  $self->debug ("getResultHash: leaving function");

  return %result;
  
}

## this function is a ready to build an answer from the arguments and
## from the resulting array
sub getResultItem {

  ## parameters are
  ##   ARGUMENTS
  ##   ARRAYREF
  
  my $self = shift;
  my $keys = { @_ };

  my $item;

  $self->debug ("getResultItem: entering function");

  my %arguments = %{$keys->{ARGUMENTS}};
  return undef if (not %arguments);

  my $arrayref = $keys->{ARRAYREF};;
  return undef if (not defined $arrayref);

  $self->debug ("getResultItem: all params present"); 
  my %hash = $self->getResultHash (TABLE => $arguments{TABLE},
                                   ARRAY => $arrayref);

  my $data        = $hash{DATA};
  my $priv_format = $hash{FORMAT};
  $self->debug ("getResultItem: data: $data");
  $self->debug ("getResultItem: format: $priv_format");
  $self->debug ("getResultItem: have all data");

  ## If it was asked only the text version, we send out only that
  ## without generating an OBJECT from it
  if( $arguments{MODE} eq "RAW" ) {
    $self->debug ("getResultItem: return data RAW");
    $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
    return $data;
  }
    
  ## Build an Object from retrieved DATA
  if( $arguments{TABLE} =~ /CERTIFICATE/ ) {
    $item = new OpenCA::X509( SHELL      => $self->{backend},
                              GETTEXT    => $self->{gettext},
                              INFORM     => "PEM",
                              DATA       => $data);
  } elsif ( $arguments{TABLE} eq "CRL" ) {
    $self->debug ("getItem: try to create crl");	
    $item = new OpenCA::CRL( SHELL      => $self->{backend},
                             INFORM     => "PEM",
                             GETTEXT    => $self->{gettext},
                             DATA       => $data);
    $self->debug ("getResultItem: crl there") if ($item);	
    $self->debug ("ResultItem: crl failed") if (not $item);	
  } elsif ( $arguments{TABLE} =~ /(REQUEST|CRR)/i ) {
    $item = new OpenCA::REQ( SHELL      => $self->{backend},
                             GETTEXT    => $self->{gettext},
                             DATA       => $data);
  } else {
    ## if we cannot build the object there is probably
    ## an error, retrun a void ...
    $self->debug ("getResultItem: cannot determine table");
    $self->set_error ( $OpenCA::DBI::ERROR->{ WRONG_DATATYPE } );
    return undef;
  }
  if (not $item)
  {
    $self->debug ("getResultItem: cannot build object return void");
    $self->set_error ( $OpenCA::DBI::ERROR->{ CANNOT_CREATE_OBJECT } );
    return undef;
  }
  $item->setStatus ($hash{STATUS});

  ## who uses DBKEY ?!
  $item->{parsedItem}->{DBKEY} = $arguments{KEY};

  ## We return the object
  $self->debug ("getResultItem: return item");
  $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );

  return $item;
      
} ## end of getResultItem

## rollback never touch the status because 
## rollback is normally the action if a
## statement fails
sub rollback {
  my $self = shift;

  $self->debug ("rollback: entering function");

  ## if there is no databasehandle then we have not to and cannot roll back
  if (not $self->{DBH} or $self->{DBH}->rollback()) {
    $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} )
        if (not $self->errno());
    return 1;
  } else {
    $self->set_error ( $OpenCA::DBI::ERROR->{ROLLBACK_FAILED} );
    return undef;
  }
}

## commit sets the status-variable
sub commit {
  my $self = shift;

  $self->debug ("commit: entering function");

  if (not defined $self->{DBH} or $self->{DBH}->commit() ) {
    $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} )
        if (not $self->errno());
    return 1;
  } else {
    $self->set_error ( $OpenCA::DBI::ERROR->{COMMIT_FAILED} );
    return undef;
  }
}

## disconnect does not set the status-variable because commit
## and rollback called before disconnect so success is not important
sub disconnect {
  
  my $self = shift;

  ## Added to correctly handle the disconnection
  if( exists $self->{STH} ) {
  	$self->{STH}->finish();
  };

  if ( (exists $self->{DBH}) and ($self->{DBH}->disconnect()) ) {
    $self->set_error ( $OpenCA::DBI::ERROR->{SUCCESS} );
    return 1;
  } else {
    $self->set_error ( $OpenCA::DBI::ERROR->{DISCONNECT_FAILED} );
    return undef;
  }
}

sub DESTROY {
  my $self = shift;

  if ($self->{ERRNO} != $OpenCA::DBI::ERROR->{SUCCESS}) {
    $self->debug ("DESTROY: automatic rollback by destructor DESTROY");
    $self->rollback ();
  } else {
    $self->debug ("DESTROY: automatic commit by destructor DESTROY");
    if (not defined $self->commit ()) {
      print STDERR "WARNING: commit failed when destroying db handler\n";
      print STDERR "         rollback() called.";
      $self->rollback ();
    }
  }
  ## finish the statement handles to reduce warnings by DBI
  $self->debug ("DESTROY: call finish on all statement handles to avoid warnings by DBI");
  $self->{STH}->finish();

  $self->{DBH}->disconnect () if (exists $self->{DBH});
}

############################
## begin of errorhandling ##
############################

sub errno
{
  my $self = shift;
  
  if ( exists $self->{errno} ) {
    $self->debug ("errno: returning local errorcode ".$self->{errno});
    return $self->{errno};
  } else {
    $self->debug ("errno: returning global errorcode $OpenCA::DBI::errno");
    return $OpenCA::DBI::errno;
  }
}

sub set_error {
  my $self = shift;

  $self->debug ("Entering set_error ...");

  ## checking for an explicit error message (from DBI)

  my $message = $OpenCA::DBI::MESSAGE->{$_[0]};
  $message = $_[1] if ($_[1]);

  ## if gettext was not defined
  ## then fall back to conventional errormessages

  if (not $self->{gettext})
  {
    $self->debug ("set_error: gettext is not defined");

    if (defined $_[0])
    {
      $self->{errno}  = $_[0];
      $self->{errval} = $message;
    }
    $self->debug ("set_error: errno and errval set");
  } else
  {
    ## fully i18n error handling

    $self->debug ("errno: gettext is defined");
    $message = $self->{gettext} ($message);

    ## set errorcode
    my $old = $self->{errno};
    $self->{errno} = $_[0];

    ## this is the new OpenCA-standard
    if ($old)
    {
      $self->debug ("errno: old errno $old is present");

      ## save the last error too
      $self->{errval} = $self->{gettext} ("__MESSAGE__ (error __OLD_ERRNO__: __OLD_ERRVAL__)",
                                  "__MESSAGE__", $message,
                                  "__OLD_ERRNO__", $old,
                                  "__OLD_ERRVAL__", $self->{errval});
    } else {
      $self->{errval} = $self->{gettext} ($message);
    }

    $self->debug ("errno: new errorcode is $errno");
  }
  $errno  = $self->{errno};
  $errval = $self->{errval};

  return undef;
}

sub errval {
  my $self = shift;
  my $text = "";
  my $code;

  return $self->{errval} if ($self->{errval});
  return $errval;
}

sub debug
{
    my $self = shift;

    if ($self->{DEBUG})
    {
        foreach my $msg (@_)
        {
            print STDERR "DEBUG: OpenCA::DBI->".$msg."\n";
        }
    }
}

##########################
## end of errorhandling ##
##########################

sub handleExpiredCert {
  my $self = shift;
  my $arguments = $_[0];
  my $query ="";

  if ($arguments->{NOTAFTER}) {
    $query .= " and (".$OpenCA::DBI::SQL->{VARIABLE}->{NOTAFTER}[0];
    if ($arguments->{STATUS_OLD} =~ /EXPIRED/i) {
      $query .= " < ";
    } else {
      $query .= " > ";
    }
    $query .= "\'" . $arguments->{NOTAFTER}."\' )";
  }
}

##########################
## end of new functions ##
##########################

# Autoload methods go after =cut, and are processed by the autosplit program.

1;
__END__
