Uploaded image for project: 'RAML Module Builder'
  1. RAML Module Builder
  2. RMB-742

Create indexes on metadata.createdByUserId and metadata.updatedByUserId

    XMLWordPrintable

Details

    • Task
    • Status: Open (View Workflow)
    • TBD
    • Resolution: Unresolved
    • None
    • None
    • CP: Non-roadmap backlog
    • Core: Platform

    Description

      All foreign key columns should have indexes on them. This allows efficient JOINing of tables, or at least allows one to do look ups using the web APIs on those fields in an efficient manner. There appear to be many foreign key columns/properties that are not indexed. For example, I have some code that allows deleting a user. It's not something that one would normally do, but, can be useful if for example you create a test user and want to delete it afterwards. You don't want to have leave the user there forever. Since FOLIO doesn't have foreign key constraints, at least between modules, I went through the database and found all the relationships and added code to check each of the relationships. If the user is being referenced by another object, the user can't be deleted. I'm using the following code to do the checks (the below methods call the web APIs passing in CQL filters for the foreign key properties). The code took 5 minutes to complete due to it having to query large tables such as instances, holdings, items, etc.

      In general, you pretty much always want to have indexes on foreign key columns. It is bad practice not to have them.

      This example uses user, but, the same thing is true for all other objects. You need to have the ability to be able to check foreign key contraints in an efficient manner to allow deletes. You need to be able to check to make sure that a delete doesn't leave orphaned references.

              protected void User2FormView_ItemDeleting(object sender, FormViewDeleteEventArgs e)
              {
                  var id = (Guid?)User2FormView.DataKey.Value;
                  try
                  {
                      if (folioServiceContext.AnyAcquisitionsUnit2s($"metadata.createdByUserId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a acquisitions unit");
                      if (folioServiceContext.AnyAcquisitionsUnit2s($"metadata.updatedByUserId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a acquisitions unit");
                      if (folioServiceContext.AnyAddressType2s($"metadata.createdByUserId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a address type");
                      if (folioServiceContext.AnyAddressType2s($"metadata.updatedByUserId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a address type");
                      if (folioServiceContext.AnyAlternativeTitleType2s($"metadata.createdByUserId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a alternative title type");
                      if (folioServiceContext.AnyAlternativeTitleType2s($"metadata.updatedByUserId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a alternative title type");
                      if (folioServiceContext.AnyBatchGroup2s($"metadata.createdByUserId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a batch group");
                      if (folioServiceContext.AnyBatchGroup2s($"metadata.updatedByUserId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a batch group");
                      if (folioServiceContext.AnyBatchVoucherExport2s($"metadata.createdByUserId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a batch voucher export");
                      if (folioServiceContext.AnyBatchVoucherExport2s($"metadata.updatedByUserId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a batch voucher export");
                      if (folioServiceContext.AnyBatchVoucherExportConfig2s($"metadata.createdByUserId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a batch voucher export config");
                      if (folioServiceContext.AnyBatchVoucherExportConfig2s($"metadata.updatedByUserId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a batch voucher export config");
                      if (folioServiceContext.AnyBlock2s($"metadata.createdByUserId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a block");
                      if (folioServiceContext.AnyBlock2s($"metadata.updatedByUserId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a block");
                      if (folioServiceContext.AnyBlock2s($"userId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a block");
                      if (folioServiceContext.AnyBlockCondition2s($"metadata.createdByUserId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a block condition");
                      if (folioServiceContext.AnyBlockCondition2s($"metadata.updatedByUserId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a block condition");
                      if (folioServiceContext.AnyBlockLimit2s($"metadata.createdByUserId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a block limit");
                      if (folioServiceContext.AnyBlockLimit2s($"metadata.updatedByUserId == \"{id}\"")) throw new Exception("User cannot be deleted because it is being referenced by a block limit");
      ... (truncated for brevity)
                      folioServiceContext.DeleteUser2(id);
                      Response.Redirect("Default.aspx");
                  }
                  catch (Exception)
                  {
                      var cv = (CustomValidator)((FormView)sender).FindControl("DeleteCustomValidator");
                      cv.IsValid = false;
                  }
              }
      

      TestRail: Results

        Attachments

          Issue Links

            Activity

              People

                Unassigned Unassigned
                jemiller Jon Miller
                Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                  Created:
                  Updated:

                  TestRail: Runs

                    TestRail: Cases