postgresql table for storing automation test results

Question

I am building an automation test suite which is running on multiple machines, all reporting their status to a postgresql database. We will run a number of automated tests for which we will store the following information:

• test ID (a GUID)
• test name
• test description
• status (running, done, waiting to be run)
• progress (%)
• start time of test
• end time of test
• test result
• latest screenshot of the running test (updated every 30 seconds)

The number of tests isn't huge (say a few thousands) and each machine (say, 50 of them) have a service which checks the database and figures out if it's time to start a new automated test on that machine.

How should I organize my SQL table to store all the information? Is a single table with a column per attribute the way to go?

If in the future I need to add attributes but want to keep compatibility with old database format (ie I may not want to delete and create a new table with more columns), how should I proceed? Should the new attributes just be in a different table?

I'm also thinking of replicating the database. In case of failure, I don't mind if the latest screenshots aren't backed up on the slave database. Should I just store the screenshots in its own table to simplify the replication?

Thanks!

Answer 1

If you specify the columns in the insert list like

INSERT INTO TABLE (Col1, Col2,..) VALUES (1, 2, ...)

it will be fine if you add columns later. The only problem might be if you start removing columns.

UPDATES should be fine, as you already specify the columns.

Ultimately you could start with an attributes table, but then you loose the explicit column types, and selecting from the table will be a maintinance and performance hassle.

So create the table as you have it, and then when inserting, make sure you specify the column names, then when you do change the table structure (adding new columns) this should not be an issue, unless they are set to be not null, in which case you might have to specify a default value.

Answer 2

A single table will be fine.

If you have a set of redundant data (like, say, test status, etc.) you might want to break those out in to little tables of their own (so you store 1 or 2 instead of "Passed" "Failed", and can join to a status table).

If you add columns, no big deal. To ensure backward compatibility, make sure you always qualify with column names:

select cola, colb, colc from table...

insert into table(cola, colb, colc) values ('x', 'y', 'z')

Make sure you allow nulls, of course, in the new columns.

Few thousand rows is nothing to postgres.