Most of our module tables include a container column. This allows the data in the tables to be automatically cleaned up when the containing folder is deleted. So our usual strategy for testing is to create a test folder, create data in that folder during the test, then delete the folder at the end.

I'm back at trying to solve this problem. I'm attempting to go with the C. solution I talked about in my previous post.

Using the container column seems like a useful idea if I was writing this module from scratch or doing major maintenance on it. But I hoping to just wrap up this test and then hopefully forget about the module. I'm told that if I can't, then we'll just do without a working unit test.

This module, peptide, is written in such a way that it depends on the auto-generated table sequence id, peptide_id:
  
    CREATE TABLE peptide.peptides (
        peptide_id SERIAL NOT NULL,

to define the primary key that is used all over the app. The test I wrote, naturally, assumes the test data to have fixed peptide id values assigned that I can use to identify, manipulate, and verify the Peptide objects.

The problem with deleting the rows of data test via the LabKey API's is that the the table sequence id is not reset to its previous value. So rerunning the test fails because the next set of test peptides get assigned different primary keys from the ones the test was designed to work with.

What I really want to be able to do is run this Postgres command on my database schema:

   'TRUNCATE TABLE peptide.peptides RESTART IDENTITY CASCADE;'

I assume there is not a way to run that command from inside the test - as a test seems to only have access to the client API's for data manipulation. (Is that right?)

Is my only choice to add a hidden API method to my module that will actually run the above Postgres command and to call it appropriately from my test?

Thanks.

Scott

Hi Scott,

Using container is generally a good idea in modules, but in this case it sounds like it might not help anyway.

For your peptide id problem, could you just write the test so it doesn't assume we're starting at 1? It could use the API to query the max of this field and base it off that? That's not totally foolproof (you might have deletes), so you could also consider inserting 1 record and storing it's ID or piggybacking off the existing inserts.

In this case, I think that will work.

Thanks for the suggestion!

Scott

I think Ben's suggestion is a good way to go... we try to avoid writing tests that assume specific auto-increment values, for just this reason.

But to answer your previous question, yes, if you require a fixed starting value from a database-managed sequence then you would have to execute that PostgreSQL-specific "TRUNCATE..." SQL in a hidden API (e.g., via SqlExecutor.execute()). Another option (starting in 13.2) would be to use a DbSequence to populate the RowId. A DbSequence is a LabKey-managed virtual sequence that gives you more control over an auto-incrementing sequence. Probably not a good route in this case since you don't want to rework the code, but it might be useful in other scenarios.

Adam

hi adam - is there a column type (like 'container-specific autoincrement') that causes LK to do the work of populating the values for you, or would the developer need to manage that themselves?

At the moment, it needs to be wired up "manually" (via Java code). Something like:

   int nextId = DbSequenceManager.get(c, MY_SEQUENCE_NAME).next();
   bean.setRowId(nextId);

We do want to create a virtual type to make this automatic. The "type" will need to provide context... unique name, container column, extra key column... which makes it a bit unusual. Let me know if you're interested in using such a type; we could work through the details together.

Adam