PSPS Schema Change

Outline

• Confirm changed IPP to PSPS mappings with Ken
• Make and test alter script

• Change psps_schema (consider a branch). confirm with main, views, etc. Also change csv files.
• Change ODM (i.e stored procedures)
• Check PSPSSchemaParser.

• Extend changes to tables and stored procedures in existing databases (3PI, MDF).
  • try new schema with new SAS
  • test load/merge
  • other verifications

Work summary

1. Change of source file (ODM, psps_schema etc.):

• psps_schema:

• C:\chenx\psps_schema-20120731\PanSTARRS.Testing.Common.sql
  Ln671 - Object found. relevant columns: o.grColor, o.riColor, o.izColor, o.zyColor
  Lines updated: 667, 676, 677.
  <---------------updated----------

• C:\chenx\psps_schema-20120731\PanSTARRS.Tables.Common.sql
  All relevant 56 columns in Object table are updated.
  <---------------updated----------

• C:\chenx\psps_schema-20120731\20Q\The20Queries_4Pan-rev-2009-7-31-Zones.sql
  Ln454 - Object found. relevant columns: o.grColor, o.riColor, o.izColor, o.zyColor
  Lines updated: 451, 460, 461.
  Make this C:\chenx\psps_schema-20120731\20Q\The20Queries_4Pan-rev-2012-7-31-Zones.sql
  and the previous file unchanged. May need to check and update the new version
  <---------------updated----------

• ODM

• All C# code don't involve Object table. i.e., no hard-coded stuff in C# code.

• C:\chenx\ODM-20120731\Workflows\SQL\cleanup-Jim.sql
  Contains Object table. -> but this does NOT need to be updated -- this is a draft file.

• C:\chenx\ODM-20120731\Workflows\Scripts\Admin\WMD.Reference.Scripts.OB.sql
  C:\chenx\ODM-20120731\Workflows\Scripts\Admin\WMD.Reference.Scripts.ST.sql
  C:\chenx\ODM-20120731\Workflows\Scripts\Admin\WMD.Reference.Scripts.P2.sql
  These 3 files contain Object table creation script. Update this.
  <---------------updated----------

• C:\chenx\ODM-20120731\Workflows\Scripts\Admin\WMD.Reference.sql
  Table Reference.ColumnValidation has values for Object table validation.
  Update this, but leave values out since we don't know yet.
  <---------------updated----------

• C:\chenx\ODM-20120731\Workflows\Scripts\LoadMerge\ColdSlice.PanSTARRS.Workflow.Common.State.sql
  Contains s.p. PanSTARRS_Workflow_Common_State.MergeObject_insertSwitchTable_Objects
  This was used to created Object table before. But now we load OB from ipp, so this is no longer used.
  So do NOT update this!!! <---------------Not updated !!!----------

• C:\chenx\ODM-20120731\Workflows\Scripts\LoadMerge\Load.PanSTARRS.Workflow.Common.State.sql
  Contains s.p. PanSTARRS_Workflow_Common_State.LoadObject_LoadDerivedTables_OB.
  <---------------updated----------

• ODM/Services - No Object table used.

• ODM/Initialize
  
  • C:\chenx\ODM-20120731\Initialize\PanSTARRS.Initialize.Jim\DevSql\04-Pan-rev-08-08-29.sql
  • C:\chenx\ODM-20120731\Initialize\PanSTARRS.Initialize.Jim\JimSql\04-Pan-rev-08-08-29.sql
    contains Object table.
    --> These 2 files contain scripts to create Object table, but they are not used now, so don't change yet.
    

If need to change, only the two scripts to create Object table need to be updated. This is trivial.

• Look at: C:\chenx\ODM-20120731\Workflows\Scripts\Spherical-T20070613-SQL
  no Object found.

• Look at: C:\chenx\ODM-20120731\Workflows\Scripts\ReferenceData
  no Object found.

• Conclusion: The places to change are in ODM\Workflow and psps_schema:
  psps_schema\PanSTARRS.Testing.Common.sql - change testing script
  psps_schema\20Q\The20Queries_4Pan-rev-2012-7-31-Zones.sql - change script to create table Object
  

ODM\Workflows\Scripts\Admin\WMD.Reference.Scripts.OB.sql - change script to create table Object
ODM\Workflows\Scripts\Admin\WMD.Reference.Scripts.ST.sql - change script to create table Object
ODM\Workflows\Scripts\Admin\WMD.Reference.Scripts.P2.sql - change script to create table Object

ODM\Workflows\Scripts\Admin\WMD.Reference.sql - change script to make table Reference.ColumnValidation

ODM\Workflows\Scripts\LoadMerge\Load.PanSTARRS.Workflow.Common.State.sql - change script to make stored procedure PanSTARRS_Workflow_Common_State.LoadObject_LoadDerivedTables_OB.

2. Change of database

Conclusion: These database objects should be changed:

• Stored procedure PanSTARRS_Workflow_Common_State.LoadObject_LoadDerivedTables_OB
  
  • Note: this s.p. exists in the load database.
    For new survey such as SAS, this will be created refresh.
    For existing survey such as 3PI, this needs to be updated before load again.
    
• Admin machine WMD table Reference.ColumnValidation
  
  • Note: this table is in the Admin machine WMD. This needs to be updated for new load.
• Admin machine WMD table Reference.Scripts, from:
  C:\chenx\ODM-20120731\Workflows\Scripts\Admin\WMD.Reference.Scripts.OB.sql
  C:\chenx\ODM-20120731\Workflows\Scripts\Admin\WMD.Reference.Scripts.ST.sql
  C:\chenx\ODM-20120731\Workflows\Scripts\Admin\WMD.Reference.Scripts.P2.sql
  
  • Note: these are in the Admin machine WMD. They need to be updated for new load.
    Here the order of fields should match the order of fields in the incoming batch.