Document 2.5 Database Changes

[dspinosa]

Coordinate with CTUIR on 2.5 database changes

[sekoukeita]

We have worked on comparing schemas of CDMS database vs NPT database (provided by Clark) to make our development environment work.

Database comparison

Overall differences

• tables, views and users have been added to the NPT database
• tables, views and users have been deleted from the NPT database
• changes in tables and view (add, deletion of columns)

Changes to core tables and views:

Users:

• missing Cell and Email columns
• added WorkPhone and PhoneExtension columns

Datasets:

• added ArchiveDatasetId

Locations:

• missing Quadrangel and RelatedHabProject columns
• added LocationTypeId

LocationDatasets:

• this table was missing in NPT database

LocationProjects:

• this table was added to the NPT database

Datastores:

• added LocationTypeId

DatasetFields:

• missing PossibleValues and Disabled columns

ProjectObjectives:

• this table was missing in the NPT database

Projects_VW (view):

• missing LocationId, SdeObjectId, SdeFeatureClassId columns
• missing Project-LocationProject-Location join

Update the database to make the dev environment work

1. Added Cell and Email columns to Users table to make the Login work
2. Deleted LocationTypeId from DataStores and LocationsTable
3. Created LocationDatasets junction table and populate it
4. Created ProjectObjectives table
5. Added PossibleValues and Disabled columns to DatasetFields tabled
6. Added Quadrangle and RelateHabProject columns to Locations table
7. Added the dataset with id = 1262 to the Datasets table. Note: this dataset is always called in Location tab.
8. Wrote a SQL script to automate the above 7 steps.

[sekoukeita]

We also received a sample database of CRITFC, from Lisa, that we compared with the NPT one.

• Most of the changes we discovered were indexes that have been removed from CRITFC database.
• Besides that, the main difference was that CRITFC database had already the junction table we needed to create for NPT. However, it was named "DatasetLocations" rather than 'LocationDatasets".
• We update the SQL script to deal with the update of both databases.

[MatteoBerg]

• ActivityQAs table - QAComments becomes Comments (This has been added to an updated version of the script Sekou provided)

• ActivityQAs_VW - change QAComments to Comments (right click view -> design)

• ActivityQAs_VW2 - change QAComments to Comments (right click view -> design)