3

Is there a way to insert a row into SQL with an expiration (c.f. you can insert a new key that expires in a minute with Memcached)?

The context is that I want an integration test to insert rows into a database, but I'd prefer not deleting them myself, as it's shared by many. Those delete queries must be manual, or they may not be run, or they may have disastrous typos, etc. I'd prefer the system to do it for me if it can (i.e. automatically and efficiently and well-tested).

(I assume this is not part of the SQL standard and the answer is no.)

related: SQL entries that expire after 24 hours

related: What is the best way to delete old rows from MySQL on a rolling basis?

CONTEXT: I can't make any changes to the database schema, or any of the associated infrastructure.

Community
  • 1
  • 1
sam boosalis
  • 1,997
  • 4
  • 20
  • 32
  • 4
    Your assumption is probably correct; there's nothing in the SQL standard out of the box. – Robert Harvey Mar 24 '14 at 20:49
  • Just a thought: Add a trigger based on users who would be entering this data to store table (assuming they ONLY do testing) and row_ID or some unique identifier of the row. write a job to process entries into table, delete form source where record in store table, delete entry in store table if successful. Monitor to ensure records older than 24 hours are not in new table. – xQbert Mar 24 '14 at 20:59
  • 1
    I think what you are asking for is to implement a [Temporal Database](http://en.wikipedia.org/wiki/Temporal_database). You will need to use `Valid Time` to stop using the rows after expiration. – user2989408 Mar 24 '14 at 21:22
  • Which DBMS are you using? Postgres? Oracle? MySQL? –  Mar 24 '14 at 23:15
  • @a_horse_with_no_name MySQL. i wanted to know if this is a part of the SQL standard anywhere. – sam boosalis Mar 25 '14 at 03:04

2 Answers2

1

If you were doing unit testing, I would suggest wrapping each unit test in a BEGIN TRAN / ROLLBACK.

Since you are doing integrated testing, you probably need the data to live outside the scope of a single transaction. SQL Agent would work fine here, except that it would not distinguish between test data and real data. However, you could get around this by INSERTing some identifier to the specific records to be deleted upon expiration. That could be done in a single stored proc..

You might be able to accomplish this by using SQL Server Service Broker. I have not worked with the service broker, but maybe there is a way to delay message processing until a specific time has passed.

Paul Williams
  • 16,585
  • 5
  • 47
  • 82
0

add an expiration date column to your table(s). create a job that will delete data that is past expiration on some schedule (say nightly).

Z .
  • 12,657
  • 1
  • 31
  • 56
  • 1
    And then forget about this feature and suddenly have real data deleted after a couple of months. – ElmoVanKielmo Mar 24 '14 at 20:51
  • Or have a time to live field and use that in the job – Jesse Mar 24 '14 at 20:53
  • @elmo obviously you can prevent that issue in numerous ways... if you need to delete (test) data you are probably better off with a simple system like this... – Z . Mar 24 '14 at 20:54
  • @ZdravkoDanev I see your point, but you know how it is - your boss hires new developer. The meaning of data in this table makes a date field named `expiration_date` reasonable. So the new developer has to accomplish some task and makes use of this column for a different purpose. Unit tests are passed, everything works great until the Zero Day. You don't want to tell me that sharing knowledge between developers in a company is 100% efficient. – ElmoVanKielmo Mar 24 '14 at 20:59
  • I would go with a CreateDate or ExpDate column in my table any day instead of a TestData column... – Z . Mar 24 '14 at 21:08
  • right, that's what the related-question answers said, but i can't do this (see question edit for details). – sam boosalis Mar 25 '14 at 03:02