2

The problem

I am working on an application with ~ 1000 tables in a sql server database. I am having a recurring issue with a sql script.

The script is used by the consulting team to "clean" some part of the database and sometimes it will fail because a new table with a foreign key constraint was added a few month ago and until this moment no one ran the script with any data in this new table. A foreign key violation is raised and everyone yells at the devs for their crappy tools.

There is a CI test on this script, but no ones updates the data provided in the test. So the test never fails because the new tables are left empty, but the script still fails in real environments.

The possible solution

I would like to make sure that every table in the database contains at least a few rows when the script CI test is run. I could try to maintain a manual script to fill up the data and add a test that checks that every table contains some rows but I fear that it will be a maintenance nightmare.

Another solution is to have another powershell/sql that script analyses the schema and creates rows according to the column types with constant/random values in all tables ordered using a topological ordering based on the foreign keys relation graph. The script would first fill table without relations, then tables with relations to the already filled tables, etc

I have two questions:

  • does this solution seems reasonable?
  • am I overcomplicating things? is there simpler way to make sure a sql script won't fail because of foreign key constraint violations?

I checked existing tools but the static analysis tools don't seem to have the required features and the data generation tools are used to specify "realistic" data which I don't care about.

| improve this question | |
3

You need to throw this back on the developers who are breaking the SQL. They are in the best position to update the SQL as they make changes which are fresh in their minds.

The simplest way to accomplish this is to use continuous integration.

Set up a test DB. Periodically, a CI build server will look at the source code repository for new check-ins. If it finds one, it runs the build.

Aside from compilation, the build should also deploy your application and test stuff like does it produce a valid executable file, do the SQL scripts run against a test DB, etc.

During the build, the DB should be rebuilt (backup, created from script, whatever is appropriate). Some process such as a stored procedure built for this purpose should ensure that data exists in all tables: that is, no table is empty.

Whenever a developer checks in a change that breaks your SQL script, it will break the build because a table is empty (no test data) or the clean procedure was not updated, or both. Eventually, developers will have to update the SQL when checking in their changes because without a functioning build, it cannot be deployed or tested and they are paid to produce a functional piece of software.

| improve this answer | |
  • We are already doing what you describe. The issue is that the test db does not contains enough data for the test to be relevant. I want to find a way to fill it up automatically. – Simon Bergot Sep 25 '15 at 13:39
  • 1
    @Simon I added some more notes on that. I think I was not clear enough. Although maybe the issue is that developers are not accountable: that is a people issue to take up with your manager (see the italics in the last paragraph) – user22815 Sep 25 '15 at 13:42
  • I agree with this solution but it means that someone will have to create a first version of this script that will have to fix all the issue that already exist today. – Simon Bergot Sep 25 '15 at 14:23
  • @Simon that is correct, the issues must be fixed sooner or later. I fail to see the downside of fixing these bugs. – user22815 Sep 25 '15 at 14:26
1

It sounds to me like you've got at least 3 environments; dev, ci, and production, and that the ci environment's database isn't being kept in a realistic condition.

Given that, I would say that the first option you offer, "I could try to maintain a manual script to fill up the data" is non-optimal - a CI server should be as fully-automated as possible, no "manual" scripts.

That said, we do use a process that creates data at the beginning of every CI run. We also use it in our dev and test environments and it's the responsibility of the development team (which in our case includes both "development" and "testing" tasks) to keep it up-to-date. As developers, we often (sometimes multiple times/day) wipe our local db and use this process to ensure we have a valid environment.

note: we consider that process to be just as important as test code, which is just as important as "real" code - so it's maintained and under source control.

| improve this answer | |
  • There is a big misunderstanding in this answer - the OP talks about manual maintenance of a test data script, that does not imply that script won't be executed automatically in the CI environment. – Doc Brown Sep 26 '15 at 7:09
  • my understanding of "manual script " is "a script that is run manually," it's possible that perhaps I am incorrect. – StevenV Sep 26 '15 at 20:47
1

I will propose an entirely different way of doing things:

You should have one "database-creation" script which creates the database from scratch, starting on a completely empty schema/catalog and creating all the tables, with all necessary relations, and all necessary immutable initial data. Immutable initial data are, for example, the days of the week (Sun,Mon,Tue, etc.) in the days_of_week table.

This script must always be committed and checked out together with the rest of the code, (assuming you are also using some other language besides SQL in the shop,) because the code relies on this script. This way, developers and testers can forget about keeping old databases around and amending them with changes. The database should be a throw-away entity, always created from scratch, and populated with data as needed. The state of your system is described entirely by what is committed in the version control system, and you avoid the constant headache of whether the version of the database is the same as the version of the database-creation script and the version of the code. Also, this way, each developer can have a local instance of the database for development and testing purposes, always being thrown away and recreated with every iteration of the checkout-develop-test-commit cycle.

So, my recommendation is to not have any script which creates test data. Each test should temporarily create the data that it needs in order to run, possibly in a transaction which gets rolled back instead of being committed, so as to leave the database empty, or each test should create a temporary schema/catalog, create the database in it, fill it with data, perform the test, and then drop the entire schema/catalog. If the test cannot create the data due to some constraint violation, then someone made a change to the database-creation script and committed it without first bothering to run the tests. It is their fault.

If you really insist on having a test-data-creation script, then it, too, should be committed and checked out together with the database-creation script and the rest of the code. That will also work.

Operations should maintain whatever older-version databases they need, and it is their problem to migrate the contents of old databases to new databases released by the developers.

| improve this answer | |
  • We started down that road as well, Mike; we ended up with a bit of a combination. We do have a create-empty procedure and a create-data procedure. Our create-data procedure sets up "well-known" common data - a basic set of users with known permissions, for example. We do also have tests that create/cleanup data specific to that one test, though we try to have few of these; we've found that tests like this, if they fail or fall over in some unexpected way, were difficult to have clean up behind themselves. Not impossible, but more work than we found with the "known-data" method. YMMV. – StevenV Sep 25 '15 at 15:21
  • my above comment applies to integration tests, btw - for unit tests any data required is created by the test, as our unit tests run with in-memory db where no contamination can occur; each test is self-contained. – StevenV Sep 25 '15 at 15:28

Your Answer

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

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