''' 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 [[br]]
    Ln671 - Object found. relevant columns: o.grColor, o.riColor, o.izColor, o.zyColor	 [[br]]
    Lines updated: 667, 676, 677.  [[br]]
    <---------------updated----------
    
    - C:\chenx\psps_schema-20120731\PanSTARRS.Tables.Common.sql [[br]]
    All relevant 56 columns in Object table are updated.  [[br]]
    <---------------updated----------
    
    - C:\chenx\psps_schema-20120731\20Q\The20Queries_4Pan-rev-2009-7-31-Zones.sql [[br]]
    Ln454 - Object found. relevant columns: o.grColor, o.riColor, o.izColor, o.zyColor [[br]]	
    Lines updated: 451, 460, 461. [[br]]
    Make this C:\chenx\psps_schema-20120731\20Q\The20Queries_4Pan-rev-2012-7-31-Zones.sql [[br]]
    and the previous file unchanged. May need to check and update the new version  [[br]]
    <---------------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 [[br]]
    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 [[br]]
    C:\chenx\ODM-20120731\Workflows\Scripts\Admin\WMD.Reference.Scripts.ST.sql [[br]]
    C:\chenx\ODM-20120731\Workflows\Scripts\Admin\WMD.Reference.Scripts.P2.sql [[br]]
    These 3 files contain Object table creation script. Update this. [[br]]
    <---------------updated----------
    
  - C:\chenx\ODM-20120731\Workflows\Scripts\Admin\WMD.Reference.sql [[br]]
    Table Reference.ColumnValidation has values for Object table validation.  [[br]]
    Update this, but leave values out since we don't know yet. [[br]]
    <---------------updated----------
    
  - C:\chenx\ODM-20120731\Workflows\Scripts\LoadMerge\ColdSlice.PanSTARRS.Workflow.Common.State.sql [[br]]
    Contains s.p. PanSTARRS_Workflow_Common_State.MergeObject_insertSwitchTable_Objects [[br]]
    This was used to created Object table before. But now we load OB from ipp, so this is no longer used. [[br]]
    So do NOT update this!!! <---------------Not updated !!!----------
  
  - C:\chenx\ODM-20120731\Workflows\Scripts\LoadMerge\Load.PanSTARRS.Workflow.Common.State.sql [[br]]
    Contains s.p. PanSTARRS_Workflow_Common_State.LoadObject_LoadDerivedTables_OB.  [[br]]
    <---------------updated----------
    
  - ODM/Services - No Object table used.

  - ODM/Initialize  [[br]]
    - 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 [[br]]
      contains Object table. [[br]]
      --> These 2 files contain scripts to create Object table, but they are not used now, so don't change yet. [[br]]
          If need to change, only the two scripts to create Object table need to be updated. This is trivial. [[br]]

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

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

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

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

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

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