900626 Asset Configuration and Tracking Consolidation : Design

This page last changed on Oct 10, 2007 by kgomes.

After the use cases were defined, several activity diagrams were created to help detail out the steps that would need to be taken to implement components to satisify the requirements of the system. Some of the pieces existed already between SSDS and BOG, but the new steps for the different use cases are as follows. Each of the DB changes listed below will cause a write to the database's history table, as indicated in the final scenario.

Current Design details

Create New Device Through Instru
1. Changes are verified (must ensure unique alternate key triple exists)
2. New entry is inserted into the Device table (set 'show in Instru view' flag to true)
Edit Device Through Instru
1. Changes are verified (must preserve uniqueness of alternate keys)
2. Changes are made to Device table
Create New Device Through SSDS
1. Need a new Create Device page in SSDS web application
2. Insert in device table
Edit Device Through SSDS
1. Need to create new Device Edit page (with JSF validation, DAO submission)
2. Update on Device table

So based on these steps the following components need to be built:

Augment Device table per the following:

Create column [MBARI_ID] [int] (but did not set NOT NULL or identity yet)
Create column [Serial] [nvarchar] (50)
Create column [Calibration_organization] [nvarchar] (50)
Create column [Features] [nvarchar] (50)
Create column [Pressure_sensor] [nvarchar] (50)
Create column [Depth_Rating] [int]
Create column [Firmware_EPROM] [nvarchar] (50)
Create column [Memory] [nvarchar] (50)
Create column [Receive_frequency] [nvarchar] (50)
Create column [Transmit_frequency] [nvarchar] (50)
Create column [Enable_code] [nvarchar] (50)
Create column [Release_code] [nvarchar] (50)
Create column [Tilt_option] [nvarchar] (50)
Create column [Purchased_for] [nvarchar] (50)
Create column [Owner] [nvarchar] (50)
Create column [Custodian] [nvarchar] (50)
Create column [Date_new] [datetime]
Create column [PO] [nvarchar] (50)
Create column [Transaction_col] [nvarchar] (10)
Create column [Permanent_comment] [nvarchar] (400)
Create column [document_dir] [nvarchar] (10)
Create column [osg_view] [bit]

Set default on osg_view to '0'

I changed the column type from ntext to nvarchar for 'Transaction_col', 'Permanent_comment', and 'document_dir' as ntext prevent triggers from being created. Also I shrunk them all down as I did not want to hit the 8060 size limit per row in SQL server. I made 'Transaction_col' and 'document_dir' really small (10) as they have only NULLs in the production database currently. I sent an email to Paul asking about those columns. He stated that the Transaction column was not really used and could be removed.

Create a view that matches the instru table structure using the following SQL

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[instru]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[instru]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.instru
AS
SELECT     ssdsdba.Device.MBARI_ID, ssdsdba.Device.id AS [SSDS ID], ssdsdba.Person.username AS Technician, ssdsdba.Device.mfgName AS Manufacturer, 
                      ssdsdba.DeviceType.name AS Type, ssdsdba.Device.mfgModel AS Model, ssdsdba.Device.Serial, ssdsdba.Device.mfgSerialNumber AS FullSerial, 
                      ssdsdba.Device.Calibration_organization AS [Calibration organization], ssdsdba.Device.Features, 
                      ssdsdba.Device.Pressure_sensor AS [Pressure sensor], ssdsdba.Device.Depth_Rating AS [Depth Rating], 
                      ssdsdba.Device.Firmware_EPROM AS [Firmware/EPROM], ssdsdba.Device.Memory, ssdsdba.Device.Receive_frequency AS [Receive frequency], 
                      ssdsdba.Device.Transmit_frequency AS [Transmit frequency], ssdsdba.Device.Enable_code AS [Enable code], 
                      ssdsdba.Device.Release_code AS [Release code], ssdsdba.Device.Tilt_option AS [Tilt option], ssdsdba.Device.Purchased_for AS [Purchased for], 
                      ssdsdba.Device.Owner, ssdsdba.Device.Custodian, ssdsdba.Device.Date_new AS [Date new], ssdsdba.Device.PO, 
                      ssdsdba.Device.Transaction_col AS [Transaction], ssdsdba.Device.Permanent_comment AS [Permanent comment], 
                      ssdsdba.Device.infoUrlList AS [manufacture web page], ssdsdba.Device.document_dir AS [document dir]
FROM         ssdsdba.Device LEFT OUTER JOIN
                      ssdsdba.DeviceType ON ssdsdba.Device.DeviceTypeID_FK = ssdsdba.DeviceType.id LEFT OUTER JOIN
                      ssdsdba.Person ON ssdsdba.Device.PersonID_FK = ssdsdba.Person.id
WHERE     (ssdsdba.Device.osg_view = 1)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Use Enterprise Manager to run a DTS job to copy the trans table from Solstice->BOG to SSDS_Metadata (with data)
Perform a data normalization step. This will be a bit tricky.

Using SQL Query Analyzer, create a foreign key between trans and instru using:

ALTER TABLE [dbo].[trans] ADD 
	CONSTRAINT [FK_trans_instru] FOREIGN KEY 
	(
		[MBARI ID]
	) REFERENCES [ssdsdba].[Device] (
		[MBARI_ID]
	)
GO

NEED MBARI_IDs first

This is a bit nasty as we would like to create a foreign key to the view, but we cannot so we should link to MBARI_ID in Device table, but since not all instruments are ones that OSG cares about, we don't have MBARI_IDs for all of them. So in the data normalization step, we need to make sure all entries have MBARI_IDs and the ones from the current instru table match those in the trans table. If we get rid of MBARI_IDs and change them to SSDS_IDs, we will have a different issue.

Create a System DSN on the machine where instrumentsDE will run and point to the SSDS_Metadata database but call it "BOG". Use the ssdsdba login and password.
When you open up the instrumentsDE, you will need to create new linked tables for instru and trans that point to the new DSN and the view and table in SSDS_Metadata.
Make changes to the instrumentsDE application
Create a history table for the Device table using the stored procedure:
```
EXEC AdminGenerateHistoryTable 'Device'
go
```
Make sure not History_Device table exists

In order for the stored procedure to work correct drop any History_Device table that may exist
Create the triggers using the stored procedure:
```
EXEC AdminGenerateHistoryTriggers 'Device'
go
```
I found that I had to remove the old triggers before running this.
Drop both history tables for DeviceType and Person
Remove all triggers on DeviceType and Person tables

Generate History tables for DeviceType and Person

EXEC AdminGenerateHistoryTable 'DeviceType'
go

EXEC AdminGenerateHistoryTable 'Person'
go

Generate Triggers for DeviceType and Person

EXEC AdminGenerateHistoryTriggers 'DeviceType'
go

EXEC AdminGenerateHistoryTriggers 'Person'
go

OK, so now that all that is in place, we have a bit of a problem with updates of the columns 'Technician' and 'Type' in the access application. When 'Technician' is edited the Person.username will be changed. When the 'Type' is edited the DeviceType.name will be changed. This is where INSTEAD OF triggers should save our behind.

The history tables store the updated values for entries as the thought is that each entry would have to be inserted before any updates so you would always have the initial state of the row. In this case we are starting with existing data, so it is critical that we get a baseline of all the current rows. This can be done simply by running these in SQL Query Analyzer:
```
UPDATE ssdsdba.Device SET version = version
GO
```
```
UPDATE ssdsdba.Person SET version = version
GO
```
```
UPDATE ssdsdba.DeviceType SET version = version
GO
```
This will create entries for all the rows so that we will have the current data in the history table to compare future updates/deletions against.

Create an INSTEAD OF INSERT trigger on the view

CREATE TRIGGER trInstruInsert ON instru
INSTEAD OF INSERT
AS
BEGIN
  -- Check to see if there are updated rows
  IF EXISTS (Select * from Inserted)
  BEGIN
    -- Declare any needed variables
    DECLARE @mbari_id int,
            @ssds_id numeric(9),
            @username varchar(50),
            @mfgName varchar(255),
            @deviceTypeName varchar(255),
            @mfgModel varchar(255),
            @serial varchar(50),
            @mfgSerialNumber varchar(255),
            @calibration_organization nvarchar(50),
            @features nvarchar(50),
            @pressure_sensor nvarchar(50),
            @depth_rating int,
            @firmware_eprom nvarchar(50),
            @memory nvarchar(50),
            @receive_frequency nvarchar(50),
            @transmit_frequency nvarchar(50),
            @enable_code nvarchar(50),
            @release_code nvarchar(50),
            @tilt_option nvarchar(50),
            @purchased_for nvarchar(50),
            @owner nvarchar(50),
            @custodian nvarchar(50),
            @date_new datetime,
            @po nvarchar(50),
            @transaction_col nvarchar(50),
            @permanent_comment nvarchar(50),
            @infoUrlList varchar(2048),
            @document_dir nvarchar(50),
            @person_id numeric(9),
            @deviceType_id numeric(9)

    -- Now grab all the values from the Inserted table
    SELECT 
            @mbari_id = MBARI_ID,
            @ssds_id = [SSDS ID],
            @username = Technician,
            @mfgName = Manufacturer,
            @deviceTypeName = Type,
            @mfgModel = Model,
            @serial = Serial,
            @mfgSerialNumber = FullSerial,
            @calibration_organization = [Calibration organization],
            @features = Features,
            @pressure_sensor = [Pressure sensor],
            @depth_rating = [Depth Rating],
            @firmware_eprom = [Firmware/EPROM],
            @memory = Memory,
            @receive_frequency = [Receive Frequency],
            @transmit_frequency = [Transmit Frequency],
            @enable_code = [Enable code],
            @release_code = [Release code],
            @tilt_option = [Tilt option],
            @purchased_for = [Purchased for],
            @owner = Owner,
            @custodian = Custodian,
            @date_new = [Date new],
            @po = PO,
            @transaction_col = [Transaction],
            @permanent_comment = [Permanent comment],
            @infoUrlList = [manufacture web page],
            @document_dir = [document dir]
            FROM Inserted
    -- Now grab the technician name
    SELECT @username = Technician FROM Inserted
    -- Now grab the device type name
    SELECT @deviceTypeName = Type FROM Inserted

    -- Now let's make sure the MBARI_ID and SSDS_ID are null
    IF @mbari_id IS NOT NULL
      RAISERROR('The insert specified the MBARI_ID. This field is auto-generated, do not specify on insert',9,1)
    IF @ssds_id IS NOT NULL
      RAISERROR('The insert specified the SSDS_ID. This field is auto-generated, do not specify on insert',9,1)

    -- Next, let's check to see if the technician's name is not null
    IF @username IS NOT NULL
    BEGIN
      -- Now check to make sure it is just not empty
      IF @username != ''
      BEGIN
        -- Check to see if it exists
        IF NOT EXISTS (SELECT id FROM ssdsdba.Person WHERE username = @username)
          INSERT INTO ssdsdba.Person (version, username, email) VALUES (0, @username, @username)
        SELECT @person_id = id FROM ssdsdba.Person WHERE username = @username
      END
      ELSE
        SET @person_id = NULL
    END
    ELSE
      SET @person_id = NULL

    -- Let's now do the same thing for the device type
    IF @deviceTypeName IS NOT NULL
    BEGIN
      -- Now check to make sure it is just not empty
      IF @deviceTypeName != ''
      BEGIN
        -- Check to see if it exists
        IF NOT EXISTS (SELECT id FROM ssdsdba.DeviceType WHERE name = @deviceTypeName)
          INSERT INTO ssdsdba.DeviceType (version, name) VALUES (0, @deviceTypeName)
        SELECT @deviceType_id = id FROM ssdsdba.DeviceType WHERE name = @deviceTypeName
      END
      ELSE
        SET @deviceType_id = NULL
    END
    ELSE
      SET @deviceType_id = NULL

    -- First update the columns that map directly
    INSERT INTO ssdsdba.Device 
      (
       mfgName,
       mfgModel,
       Serial,
       mfgSerialNumber,
       Calibration_organization,
       Features,
       Pressure_Sensor,
       Depth_Rating,
       Firmware_EPROM,
       Memory,
       Receive_frequency,
       Transmit_frequency,
       Enable_code,
       Release_code,
       Tilt_option,
       Purchased_for,
       Owner,
       Custodian,
       Date_new,
       PO,
       Transaction_col,
       Permanent_comment,
       infoUrlList,
       document_dir,
       PersonID_FK,
       DeviceTypeID_FK
      )
    VALUES
      (
       @mfgName,
       @mfgModel,
       @serial,
       @mfgSerialNumber,
       @calibration_organization,
       @features,
       @pressure_sensor,
       @depth_rating,
       @firmware_eprom,
       @memory,
       @receive_frequency,
       @transmit_frequency,
       @enable_code,
       @release_code,
       @tilt_option,
       @purchased_for,
       @owner,
       @custodian,
       @date_new,
       @po,
       @transaction_col,
       @permanent_comment,
       @infoUrlList,
       @document_dir,
       @person_id,
       @deviceType_id
      )
  END
END
GO

Create an INSTEAD OF UPDATE trigger on the view

CREATE TRIGGER trInstruUpdate ON instru
INSTEAD OF UPDATE
AS
BEGIN
  -- Check to see if there are updated rows
  IF EXISTS (Select * from Inserted)
  BEGIN
    -- Declare any needed variables
    DECLARE @mbari_id int,
            @ssds_id numeric(9),
            @username varchar(50),
            @mfgName varchar(255),
            @deviceTypeName varchar(255),
            @mfgModel varchar(255),
            @serial varchar(50),
            @mfgSerialNumber varchar(255),
            @calibration_organization nvarchar(50),
            @features nvarchar(50),
            @pressure_sensor nvarchar(50),
            @depth_rating int,
            @firmware_eprom nvarchar(50),
            @memory nvarchar(50),
            @receive_frequency nvarchar(50),
            @transmit_frequency nvarchar(50),
            @enable_code nvarchar(50),
            @release_code nvarchar(50),
            @tilt_option nvarchar(50),
            @purchased_for nvarchar(50),
            @owner nvarchar(50),
            @custodian nvarchar(50),
            @date_new datetime,
            @po nvarchar(50),
            @transaction_col nvarchar(50),
            @permanent_comment nvarchar(50),
            @infoUrlList varchar(2048),
            @document_dir nvarchar(50),
            @person_id numeric(9),
            @deviceType_id numeric(9)

    -- Now grab all the values from the Inserted table
    SELECT 
            @mbari_id = MBARI_ID,
            @ssds_id = [SSDS ID],
            @username = Technician,
            @mfgName = Manufacturer,
            @deviceTypeName = Type,
            @mfgModel = Model,
            @serial = Serial,
            @mfgSerialNumber = FullSerial,
            @calibration_organization = [Calibration organization],
            @features = Features,
            @pressure_sensor = [Pressure sensor],
            @depth_rating = [Depth Rating],
            @firmware_eprom = [Firmware/EPROM],
            @memory = Memory,
            @receive_frequency = [Receive Frequency],
            @transmit_frequency = [Transmit Frequency],
            @enable_code = [Enable code],
            @release_code = [Release code],
            @tilt_option = [Tilt option],
            @purchased_for = [Purchased for],
            @owner = Owner,
            @custodian = Custodian,
            @date_new = [Date new],
            @po = PO,
            @transaction_col = [Transaction],
            @permanent_comment = [Permanent comment],
            @infoUrlList = [manufacture web page],
            @document_dir = [document dir]
            FROM Inserted
    -- Now grab the technician name
    SELECT @username = Technician FROM Inserted
    -- Now grab the device type name
    SELECT @deviceTypeName = Type FROM Inserted

    -- Next, let's check to see if the technician's name is not null
    IF @username IS NOT NULL
    BEGIN
      -- Now check to make sure it is just not empty
      IF @username != ''
      BEGIN
        -- Check to see if it exists
        IF NOT EXISTS (SELECT id FROM ssdsdba.Person WHERE username = @username)
          INSERT INTO ssdsdba.Person (version, username, email) VALUES (0, @username, @username)
        SELECT @person_id = id FROM ssdsdba.Person WHERE username = @username
      END
      ELSE
        SET @person_id = NULL
    END
    ELSE
      SET @person_id = NULL

    -- Let's now do the same thing for the device type
    IF @deviceTypeName IS NOT NULL
    BEGIN
      -- Now check to make sure it is just not empty
      IF @deviceTypeName != ''
      BEGIN
        -- Check to see if it exists
        IF NOT EXISTS (SELECT id FROM ssdsdba.DeviceType WHERE name = @deviceTypeName)
          INSERT INTO ssdsdba.DeviceType (version, name) VALUES (0, @deviceTypeName)
        SELECT @deviceType_id = id FROM ssdsdba.DeviceType WHERE name = @deviceTypeName
      END
      ELSE
        SET @deviceType_id = NULL
    END
    ELSE
      SET @deviceType_id = NULL

    -- First update the columns that map directly
    UPDATE ssdsdba.Device 
      SET ssdsdba.Device.mfgName = @mfgName,
          ssdsdba.Device.mfgModel = @mfgModel,
          ssdsdba.Device.Serial = @serial,
          ssdsdba.Device.mfgSerialNumber = @mfgSerialNumber,
          ssdsdba.Device.Calibration_organization = @calibration_organization,
          ssdsdba.Device.Features = @features,
          ssdsdba.Device.Pressure_Sensor = @pressure_sensor,
          ssdsdba.Device.Depth_Rating = @depth_rating,
          ssdsdba.Device.Firmware_EPROM = @firmware_eprom,
          ssdsdba.Device.Memory = @memory,
          ssdsdba.Device.Receive_frequency = @receive_frequency,
          ssdsdba.Device.Transmit_frequency = @transmit_frequency,
          ssdsdba.Device.Enable_code = @enable_code,
          ssdsdba.Device.Release_code = @release_code,
          ssdsdba.Device.Tilt_option = @tilt_option,
          ssdsdba.Device.Purchased_for = @purchased_for,
          ssdsdba.Device.Owner = @owner,
          ssdsdba.Device.Custodian = @custodian,
          ssdsdba.Device.Date_new = @date_new,
          ssdsdba.Device.PO = @po,
          ssdsdba.Device.Transaction_col = @transaction_col,
          ssdsdba.Device.Permanent_comment = @permanent_comment,
          ssdsdba.Device.infoUrlList = @infoUrlList,
          ssdsdba.Device.document_dir = @document_dir,
          ssdsdba.Device.PersonID_FK = @person_id,
          ssdsdba.Device.DeviceTypeID_FK = @deviceType_id
      WHERE ssdsdba.Device.id = (Select [SSDS ID] from Inserted)
  END
END
GO

Create an INSTEAD OF DELETE trigger on the view

CREATE TRIGGER trInstruDelete ON instru
INSTEAD OF DELETE
AS
BEGIN
  -- Check to see if there are updated rows
  IF EXISTS (Select * from Deleted)
  BEGIN
    -- Declare any needed variables
    DECLARE @mbari_id int,
            @ssds_id numeric(9),
            @username varchar(50),
            @mfgName varchar(255),
            @deviceTypeName varchar(255),
            @mfgModel varchar(255),
            @serial varchar(50),
            @mfgSerialNumber varchar(255),
            @calibration_organization nvarchar(50),
            @features nvarchar(50),
            @pressure_sensor nvarchar(50),
            @depth_rating int,
            @firmware_eprom nvarchar(50),
            @memory nvarchar(50),
            @receive_frequency nvarchar(50),
            @transmit_frequency nvarchar(50),
            @enable_code nvarchar(50),
            @release_code nvarchar(50),
            @tilt_option nvarchar(50),
            @purchased_for nvarchar(50),
            @owner nvarchar(50),
            @custodian nvarchar(50),
            @date_new datetime,
            @po nvarchar(50),
            @transaction_col nvarchar(50),
            @permanent_comment nvarchar(50),
            @infoUrlList varchar(2048),
            @document_dir nvarchar(50),
            @person_id numeric(9),
            @deviceType_id numeric(9)

    -- Now grab all the values from the Deleted table
    SELECT 
            @mbari_id = MBARI_ID,
            @ssds_id = [SSDS ID],
            @username = Technician,
            @mfgName = Manufacturer,
            @deviceTypeName = Type,
            @mfgModel = Model,
            @serial = Serial,
            @mfgSerialNumber = FullSerial,
            @calibration_organization = [Calibration organization],
            @features = Features,
            @pressure_sensor = [Pressure sensor],
            @depth_rating = [Depth Rating],
            @firmware_eprom = [Firmware/EPROM],
            @memory = Memory,
            @receive_frequency = [Receive Frequency],
            @transmit_frequency = [Transmit Frequency],
            @enable_code = [Enable code],
            @release_code = [Release code],
            @tilt_option = [Tilt option],
            @purchased_for = [Purchased for],
            @owner = Owner,
            @custodian = Custodian,
            @date_new = [Date new],
            @po = PO,
            @transaction_col = [Transaction],
            @permanent_comment = [Permanent comment],
            @infoUrlList = [manufacture web page],
            @document_dir = [document dir]
            FROM Deleted

    -- Now let's make sure the SSDS_ID is not null
    IF @ssds_id IS NULL
      RAISERROR('The delete did not specify the SSDS_ID. No delete performed',9,1)

    -- Now delete the row specified
    DELETE FROM ssdsdba.Device 
      WHERE id = @ssds_id
  END
END
GO

Person Creation/Edit/Delete page (KG)
DeviceType Creation/Edit page (KG)
Device Creation Page and Device Edit Page (KG)
1. We will need a unique device edit page that can edit non-SSDS fields (like OSG view flag).
2. Authenticate against LDAP group
3. JSF Validation (unique alternate keys; what else?)
4. Calls DAO to create new device/submit changes to existing device
External application (JG/AM)
1. Runs on interval (5 minutes) against history table
2. Checks for new devices
  1. mail to SSDS admin with key and alternate key fields (all fields?), and whether device is exposed through Instru view
  2. send mail to OSG admin (with link to kick of the servlet to make it visible)...
    1. only if not visible in Instru? (Does OSG want notification on all additions?)
    2. only if not OSG-created? (If OSG creates thru SSDS, send mail? set Instru-visible flag?)
  3. If OSG operator clicks link, servlet is called that sets Instru view flag on that device
3. Checks for edited devices
  1. mail to SSDS admin with changed fields (old and new), who and when changed, whether device is exposed through Instru view
  2. if exposed through Instru view, send same mail to OSG admin with link to edit
    1. just point back to edit the device for now; later can make this a link to revert the change
    2. does OSG admin want to be notified of changes by OSG admin personnel?
4. Marks each checked item as done
1 Servlet (KG)
1. Change Instru view flag
  1. Pass in SSDS Device ID
  2. Set the flag on that device to allow it to show through the Instru view
  3. Send email to SSDS Admin confirming change
2 History Triggers on Device Table (KG)
1. Every time a device is added, a trigger writes to a history table
  - Information includes key, the alternate key fields, who added the device, and when
2. Every time a device is edited, a trigger writes to a history table
  - Information includes key, old record, new record (or old field, new field if atomic), who, when
SQL to perform initial database normalization between BOG and SSDS (JG/AM)
1. All XML in puckxml will have to have matching changes made
Custom field comparison utilities
1. Serial number
  1. Ignore non-alphanumeric text (remove white space, punctuation)
  2. If existing serial number matches the end of the new serial number, consider it a (likely?) match
2. Model number
  1. Ignore non-alphanumeric text (remove white space, punctuation)
Put links to CVS web xml in Device listing web page (KG)

Notes:

A proposal for an integrated set of database fields: DatabaseFields

AccessAppChanges

During our design, questions were raised:

Does the Microsoft Access application have any validation (check for duplicate serial numbers, etc.)?

Generally speaking, no. Some fields (Mfg, Model, Device, ...)are regularized by using drop-downs on the new/edit dialog boxes.