Structuring an SQL program

Applications Programming
1

I have a number of queries which are to perform 50+ validations on a database. Some are simple and are one liners, for example whether a primary key is present on a table. Others are more complex and require procedural programming to accomplish, such as whether a column exists which has NULL-values only. All are implemented in pure SQL and have been tested on MySQL 5.5 only so far.

I was wondering if there is a best practice how to program this. I want a single command that performs all validations in sequence. Do I put all code in a single sql-file? Or is it better to have one main file which has the one liners in it and then all the procedural programs in a separate file? Or one main file and the procedural programs in their own files?

2

I suggest including samples of what you are talking about. It's not clear what you mean by procedural programming. Are you running a number of sql commands and driving them shell script? As far as best practice, I would be concerned about performance issues like total execution time, are you impacting other queries, etc. Post examples.

3

Stored procedures are also a possibility.

4

The code would look something as follows:

use information_schema;

-- Missing Primary Keys
SELECT t01.table_name 
FROM (SELECT table_name, COUNT(*) FROM columns WHERE table_schema = @myDB GROUP BY table_name, column_key) t01 
GROUP BY t01.table_name 
HAVING COUNT(*) = 1;

etc etc etc

At some point the program continues with (or could continue with) the procedural code:

DROP PROCEDURE IF EXISTS RuleDupeRows;

DELIMITER //
CREATE PROCEDURE RuleDupeRows (IN myDB VARCHAR(32))
COMMENT 'Duplicate Rows in a Table'
BEGIN
  DECLARE 
  [ --- some more code here --- ]
  END WHILE tableLoop;
  CLOSE curMyTable;
END;
//
DELIMITER ;

The procedural code could also be held in a separate file or even multiple separate files. What is the best practice in these circumstances?

It is invoked as follows:

mysql < dbchecks.sql databasename > dbchecks.txt