Database validation

Abstract

Being able to validation a database connection in a consistent, documented manner would be useful to aid developers in diagnosing connection issues. This enhancement proposal calls for adding an isValid() method on zero.data.Manager that can tell the callee if the database is ready and optionally verify that it is the correct vendor and version.

Rationale and motivation

Often is the case where a developer is stumped connecting to a database. Further, often a application module dependency is not configured with correct SQL statements to connect to particular vendors. Providing an API to allow application module developers and end-user developers alike to validate database configuration, vendor, and version would help.

Application module are dependent on database artifacts existing before the module can execute properly. Different modules will want to verify this in their own way. For instance, a module that stores comment information in the COMMENTS table may want to execute SELECT 1 FROM comments WHERE 1=0. Getting back empty results without an exception would validate that the database is running and has the COMMENTS table. Other modules may want their own behavior.

Proposal summary

isValid()

Add the isValid() method to zero.data.Manager that, by default, will obtain DatabaseMetaData from the database configured by the Manager. This simply verifies that a connection can be successfully made without error.

Validation factories

Second, provide a configurable means to validate specific artifacts in the database with some default implementations for common cases.

/config/db/reviewDB = {
    "dataSource" : "org.apache.derby.jdbc.EmbeddedDataSource",
    "properties" : {
        "serverName" : "localhost",
        "portNumber" : 1527
        "databaseName" : "./db/review",
        "createDatabase" : "create",
        "password" : "<xor>Lz4sLCgwLTs="
    },
    "validations" : {
        "table_check"
    }
}

/config/validations/db = {
    "table_check" : {
        "type" : "zero.data.validations.SimpleTableCheck",
        "statements" : {
            "SELECT 1 FROM comments WHERE 1=0"
        }
    }
}

Vendor validations

Thoughts in progress It would be useful to be able to specify in the database configuration the vendor and version to get timely feedback to module users that the database simply is not supported.

/config/requirements/db= {
    "vendor" : "oracle",
    "version" : "10"
}