1

I'm looking for a data integrity checking tool that lets me specify rules with which it scans a database for logical inconsistencies.

Background:

We have a large application where the data integrity is maintained in the code: there are e.g. no triggers in the database for cascading deletes.
Because of things like program aborts, failing updates, errors in code, etc. data can become corrupt.

I would like to have a separate tool to check for logical errors/inconsistencies in the data, specifying rules like:

  • Values in field must be filled in

  • Field values are required

  • Field values must be in constant range [X..Y]

  • Field values must match a regexp

  • All values for Field X must be unique

Or 'across table' relations:

  • Field values must be in range specified by field X from table T to field Y from table T

  • Values in field X of table T should come from field Y of table S

  • Field X in table T must be larger than field Y in table T

I'm not talking about the programs that most DBMS already have and that check internal file structures, index corruption etc (e.g. the Check Database Integrity Task that you can use in a maintenance plan for SQL Server), I want to check for logical errors.

Requirements:

  • Multiple DBMSs, I would just have to specify a database type/location/login. Firebird, MSSQL, Oracle are a must.

  • Running under Windows

  • Free would be nice

  • You are looking for an extract-transform-load tool. I might suggest Talend (talend.com/resource/free-etl.html) as I've heard good things about it but have no experience with it. Microsoft has SSIS; definitely not free but very powerful. It supports many database types. There are a lot of others. Every tool has the ability to specify constraints on the data as those are essential for properly extracting and transforming data. – Bob Dalgleish Aug 17 '14 at 1:51
  • What about using constraints in the DBs to prevent the errors from even getting into the database in the first place? Most of your list can be prevented at the source. Using transactions (which again, are widely supported across DBs) can greatly help agaist program aborts, for example. My prime suggestion would be to try to fix the bugs and improve error handling in the app, if at all possible – Alejandro Aug 17 '14 at 4:35
1

I would add constraints to your dba as said above. Of course, if your data doesn't conform to the constraint, then you'll have to with update the records to get the data in the correct form. Identifying the data that is inconsistent with business requirements is not enough. You have to fix the data dictionary with constraints and then add triggers. Build the complicated business requirements into the code.

  • 1
    Theoretically you're right, but the issue here is those could still be insufficient. This is long-living software where the choice was made to safeguard integrity in the code. I'm mainly talking about the development databases where code breaks all the time or the developer aborts tests. We seldom have clients with corrupt data, it's just that we sometimes bump into invalid test data during development (with an ever changing database). – user416 Oct 30 '14 at 14:10
  • 1
    And to add: careful with triggers; used incorrectly they might back-fire (I had a customer with that exact case, almost dead-locking himself with a trigger mechanism finally "closing the circle" coming back to itself). – Izzy Oct 30 '14 at 14:44
0

I would suggest using Python and a simple script using the appropriate database interface:

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" # You will need your real DB details here
                    )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to get records from the database.
sql = "SELECT * FROM EMPLOYEE"  # for example
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch a row in a list.
   for row in cursor.fetchone():
      # Put your checks here with Failure set to the results!
      if Failure:
          # Now print fetched result
          print "some details that identify the record that failed e.g.: %s, %s" % \
             (row[0], row[1])
except:
   print "Error: unable to fetch data"

# disconnect from server
db.close()
  • Free
  • Can interface to a number of database backends
  • Cross Platform
  • Your checks can be as simple or as complicated as you need.
  • Lots of web help, books & examples
  • Once you are happy it is working from your local machine it could be simply ported to the server and run nightly from a chron job.
  • 1
    Thanks, but "been there, done that". I'm a programmer by profession, and had already built a rudimentary app a few years back - it's just too much work that way ;-) – user416 Aug 16 '14 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy