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
- relates to
-
RMB-666 Add indexes for metadata.updatedDate property on all tables to allow for efficient sorting, remove redundant sort field, use propert data type
-
- Open
-
-
-
- Closed
-
-
-
- Open
-
-
RMB-765 Index, search and sort createdDate and updatedDate as TIMESTAMP (schema.json)
-
- Open
-
