Process

1. Receive new container weights from TAS.

Typically as TXT, CSV, or Excel file(s).

It is preferrable the communication goes wide--for example to the programmers@iodp.tamu.edu distribution list--rather than buried in a individual queue.

2. Connect to the production TRANSFER schema.

SQL Developer is a sufficient tool to conduct this script.

The scripting may be conducted as your development DBA user. Be sure to be explicit that the tables being modified are in the TRANSFER schema.

3. Review TRANSFER.BEAKER_RAW.

Make sure its empty.

We do not want to append new data to old, nor recreate container records that have previously been made.

4. Prepare the input file(s).e

Typically several text files are received with the columns shown below.

NAME,MASS,MEASURED_BY,MEASURED_ON
18285,20.4705,tg,7/25/2014 11:56:09 AM
18286,20.4557,tg,7/25/2014 12:37:56 PM
STND,20.001,,7/25/2014 12:41:13 PM
...

When multiple files are provided, combine them into a single file. Apply these changes

• If the files received do not have the above format, revise to match the pattern.
• The file is expected to be ASCII, not Unicode.
• Remove any records where NAME='STND'. We do not insert containers based on check standards.
• Remove any empty records resulting from empty lines.

• Remove any (resent | prior duplicate) records. Shouldn't be any. But do check.

  Verify that the lowest container number (in the NAME column) is greater in the CSV file than for any container record already in the shipboard LIMS (LIMSJR). Run the LORE > Moisture and Density > MAD Containers report for a open-ended time range to see the last batch of containers.

• Date formatting was once controlled to "yyyy-mm-dd hh24:mi:ss"; but these days is more likely to be "mm/dd/yyyy hh:mi:ss am".
• If initials for measured_by were omitted, supply some based on the email traffic or other records in the file.

Export the cleaned list of containers to container.csv. This file should be checked into Subversion for future reference
https://build.ship.iodp.tamu.edu/svn/wapps/Database/DbScript/containers

Please indicate beaker range in the Subversion the log message.

5. "Import Data..." using SQLDeveloper

Right-click on TRANSFER.BEAKER_RAW > Import Data...
Follow the prompts to complete ingestion of containers.csv.

• Typically the files have no header--be sure the SQLDeveloper import option "Header" is unchecked.
• The format of the MEASURED_ON date must be specified for SQLDeveloper to import successfully. At present this format must be used: "mm/dd/yyyy hh:mi:ss am". Historically the shore balance program controlled for date formats like: "yyyy/mm/dd hh24:mi:ss".

When the import is complete, the TRANSFER.BEAKER_RAW table should have the same number of rows as lines in the containers.csv file created.

Do some double-checking to ensure whether or not new measurements were made under old numbers. Has not happened yet, but could: sufficient book-keeping and communication has (so far) prevented this scenario.

6. Review the container_load.sql script from Subversion.

Run it. Or cut'n'paste the salient procedures and SQL from it as documented and in the order provided. If you choose the cut'n'paste method--be sure to supply meaningful input parameters--exampler ampersanded inputs like whodunit, etc.

The script was designed around SQLPLUS command-line. The SQL Worksheet environment of SQL Developer is fully compatible with all the legacy SQLPLUS syntax.

7. Review the content of the TRANSFER.BEAKER_* tables

There should be one record per container in BEAKER_SAMPLE, BEAKER_TEST.

BEAKER_RESULT should have 5 records for each container.

sample, test, result.

8. Copy the new sample, test, and result records into production LIMS.

insert into lims.sample select * from transfer.beaker_sample;
insert into lims.test select * from transfer.beaker_test;
insert into lims.result select * from transfer.beaker_result;
insert into lims.x_sample_depth (sample_number, scale_id, top, bot) select sample_number, 11311, -99, -99 from transfer.beaker_sample;
commit;

9. Notify

Inform technical staff, ALOs, developers that its done.

Beware

• If the container load process is run in any test environment, DO NOT COPY THOSE RECORDS to a production environment. The primary key sequences in test environments are not controlled for reliable inter-instance transport with ANY other instance. There is a significant probability of introducing "primary key collision time-bombs" into the production environment this way.
• Please be conservative with respect to re-running this script in the production TRANSFER schemass. It spins through the production primary keys for sample, test, result in sizeable blocks (e.g. during 398P 425 new samples, 425 new tests, 2125 results).

Change

20230330 df The vendor of the Wheaton800 containers no longer provides that product. Therefore container metadata and density have changed with the purchase of borosilicate containers from Kimble. In Subversion copies of the script have been created that reflect the vendor glass to which they apply. CL.sql is now for Kimble supply.

Revised code in TRANSFER.CL implements

• vendor Kimble;
• a comment indicating that containers are not measured with lids or labels affixed--that is, the simple volumed computation is valid and accepted for current container weighing processes; and
• the borosilicate glass density of 2.29 g/cm^3 is applied within the code.

Repaired container load in SHIPTEST. At some point the records in the TRANSFER.BEAKER_CONST_* tables had been duplicated, resulting in duplicate key failures when operating CL.sql.

MAD Container report updated in LIMSJR and SHIPTEST to shorten the report name and supply the right column to actually display Test Comments.

Reference

See also Shore MAD Balance, Making MAD vial sequenced labels using Zebra Designer.