Uploaded image for project: 'EJBCA'
  1. EJBCA
  2. ECA-7539

Add subcommand to clientToolBox to interact with database over pure JDBC

    Details

    • Type: New Feature
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: EJBCA 7.0.0, EJBCA 6.15.2
    • Component/s: None
    • Labels:
    • Environment:
      CLI, Database agnostic
    • Issue discovered during:
      Integration
    • Sprint:
      EJBCA Team Bob - 2018 w45, EJBCA Team Bob - 2018 w48, EJBCA Team Bob - 2019 w2

      Description

      It is sometimes convenient to have the ability to interact directly with an SQL database using the CLI from the server where EJBCA runs without having a native database CLI installed.

      In this scenario there is always an JDBC driver JAR present on the system to be used by the application server's DataSource and we could leverage this driver for CLI interactions.

      Assume that the driver is compliant with JDBC 4.0+ so we can use java.sql.DriverManager.

      Example tool usage

      Build:

      $ ant clean clientToolBox
      $ ln -s ${APPSRV_HOME}/standalone/deployments/mariadb-java-client-2.2.3.jar dist/clientToolBox/ext/jdbc-driver.jar
      

      Health check:

      $ dist/clientToolBox/ejbcaClientToolBox.sh jdbc --url=jdbc:mariadb://mysql-instance:3306/ejbca --username=ejbca --password=ejbca --execute="select 1;"  ; echo $?
      

      Create database tables and apply indexes:

      $ dist/clientToolBox/ejbcaClientToolBox.sh jdbc --url=jdbc:mariadb://mysql-instance:3306/ejbca --username=ejbca --password-prompt --file=doc/sql-scripts/create-tables-ejbca-mysql.sql  ; echo $?
      $ dist/clientToolBox/ejbcaClientToolBox.sh jdbc --url=jdbc:mariadb://mysql-instance:3306/ejbca --username=ejbca --password-prompt --file=doc/sql-scripts/create-index-ejbca.sql  ; echo $?
      

      Run several statements without using a .sql file:

      $ dist/clientToolBox/ejbcaClientToolBox.sh jdbc --url=jdbc:mariadb://mysql-instance:3306/ejbca --username=ejbca --password-prompt --execute="SELECT * FROM CertificateData LIMIT 1;" --execute="SELECT * FROM AuditRecordData LIMIT 1;"   ; echo $?
      

        Attachments

          Activity

            People

            • Assignee:
              johan Johan Eklund
              Reporter:
              johan Johan Eklund
              Verified by:
              Bastian Fredriksson
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: