Clean-up of unused database objects

[IagoSRL]

Overview

To simplify all work, we should remove unused objects, like some (old) store procedures and experimental tables, since it adds noise, warnings and even extra work that is not worth.

Tasks

• [ ] Make a list of unused objects
  • [ ] We have copies of store procedures and functions at a /database folder at the main repo (loconomics/loconomics), were we tried to keep track of changes; changes here should be reflected there
  • [ ] Or if we have an organized per object schema in place at this repo already, just remove the whole /database folder there and keep tracks here.
• [ ] Update this repo with the changes
• [ ] Save removal SQL script and place a copy at the loconomics/loconomics repo as for usual db upgrades (keeping that system until we replace it).
• [ ] Apply removal and communicate so others can replicate that change using the scripts (in case don't want to replicate the database from scratch).

Related issues at loconomics/loconomics

Some issues exists already requesting clean-up, with some list of objects already, review them and transfer here:

• https://github.com/loconomics/loconomics/issues/76
• https://github.com/loconomics/loconomics/issues/224

[IagoSRL]

Even if is not specifically 'unused objects' but is part of a kind of clean-up of database, I put here the SQL code that changed as of #3 regarding adding an explicit constraint name to anonymous FKs, that would help prevent errors on upgrade scripts.

ALTER TABLE [dbo].[CCCUsers] ADD
    CONSTRAINT [FK__CCCUsers__FieldOfStudyID] FOREIGN KEY ([FieldOfStudyID]) REFERENCES [dbo].[FieldOfStudy] ([FieldOfStudyID]),
    CONSTRAINT [FK__CCCUsers__InstitutionID] FOREIGN KEY ([InstitutionID]) REFERENCES [dbo].[institution] ([InstitutionID]),
    CONSTRAINT [FK__CCCUsers__UserID] FOREIGN KEY ([UserID]) REFERENCES [dbo].[users] ([UserID]),
    CONSTRAINT [FK__CCCUsers__UserID__6EA14102] FOREIGN KEY ([UserID]) REFERENCES [dbo].[users] ([UserID])

ALTER TABLE [dbo].[COUNTY] ADD
CONSTRAINT [FK__county__StateProvinceID] FOREIGN KEY ([StateProvinceID]) REFERENCES [dbo].[stateprovince] ([StateProvinceID])

ALTER TABLE [dbo].[FieldOfStudy] ADD
    CONSTRAINT [FK__FieldOfStudy__LanguageID__CountryID] FOREIGN KEY ([LanguageID], [CountryID]) REFERENCES [dbo].[language] ([LanguageID], [CountryID])

ALTER TABLE [dbo].[municipality] ADD
    FOREIGN KEY ([CountyID]) REFERENCES [dbo].[county] ([CountyID])

ALTER TABLE [dbo].[postalcode] ADD
    CONSTRAINT [FK__postalcode__CountyID] FOREIGN KEY ([CountyID]) REFERENCES [dbo].[county] ([CountyID]),
    CONSTRAINT [FK__postalcode__MunicipalityID] FOREIGN KEY ([MunicipalityID]) REFERENCES [dbo].[municipality] ([MunicipalityID]),
    CONSTRAINT [FK__postalcode__StateProvinceID] FOREIGN KEY ([StateProvinceID]) REFERENCES [dbo].[stateprovince] ([StateProvinceID])

ALTER TABLE [dbo].[UserLicenseCertifications] ADD
    CONSTRAINT [FK_userlicen__ProviderUserID] FOREIGN KEY ([ProviderUserID]) REFERENCES [dbo].[users] ([UserID])

The schema at this repo has this fixed already, but they need to be applied to dev and live to keep all in sync.

[IagoSRL]

Progress on this:

• As part of #2, some clean-up already done
• The upgrade script has fixed named PKs and FKs should be fine now too, so my previous comment here should be outdated now.