Take the 2-minute tour ×
Software Quality Assurance & Testing Stack Exchange is a question and answer site for software quality control experts, automation engineers, and software testers. It's 100% free, no registration required.

Our DBA is very eager to help us with monitoring DB status. We had recently some problems with buggy deployments scripts (both manual and automated) that set up DB incorrectly or incompletely. So his idea is to have SELECT queries that will check the status of DB to provide quick feedback, e.g.

SELECT count(*) > 0 FROM user_table;

Obviously, many DB problems will be discovered by end-to-end tests, but having a number of tests cases checking directly DB will help us with troubleshooting and shorten the feedback loop.

I would like to add such DB checks as Jenkins jobs and provide to DBA some way (natural for him) to express such test cases. I found two approaches to do that:

  1. Translate SQL queries to JUnit tests, e.g. using DBAssert library:

    //Set condition to search record by. 
    dbAssert.table("authors").where("id", 123).and("last_name", "Coupland") ; 
    
    // assert customers _name_ column equals Douglas value in database.
    dbAssert.assert_column("name", "Douglas");
    
  2. Embed SQL queries into bash scripts, as proposed here:

    #!/bin/bash -l
    CHECK=`mysql -u jenkins -ppassword -h 192.168.56.101 -BNe 'SELECT COUNT(*) FROM foo_db.jobs WHERE depth >= 100'`
    exit ${CHECK}
    

The choice will depend on our DBA, obviously. Though, I believe, the second approach is easier as it requires no Java skills.

Any other ideas?

share|improve this question

1 Answer 1

If it were me, I would use a bash or python script that reads from a properties file and reads/executes each query from the file and prints the query string and its return value (your link says Jenkins has email notifications, which is good for this)

Then show the DBA how to edit the properties file to add/edit/remove queries.

I would think it would be convenient for the DBA because they only have to add sql statements and read the email notification.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.