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: