Repository: HTTP-APIs/hydrus (python)

Issue:
Foreign key relationship between Collection and managed Class
### I'm submitting a 
- [ ] bug report.
- [x] feature request.

### Current Behaviour:
<!-- Describe about the bug -->
Presently, if an object is deleted from a Class it would still be there in the collection table in database( if it was previously inserted in a Collection that directly manages that Class). In such case, a GET response from Collection endpoint will show the member available in the database but will throw an InstanceNotFound error when a GET request will be made to view details of the object because it has already been deleted from the Class.

### Expected Behaviour:
<!-- Describe what will happen if bug is removed -->
hydrus should check if an object is also present in the database table of a Collection that directly manages the class and then delete the object from both concurrently. There should be foreign-key relationship between a Class and a Collection that manages that Class. We can use `ondelete=CASCASE` and `onupdate` while creating a foreign key column. Using this, a member could be deleted automatically if it’s available in it’s Class table.

### Steps to reproduce:
<!-- If you can then please provide the steps to reproduce the bug -->

### Snapshot:
<!-- If you can then please provide the screenshot of the issue you are facing -->

### Environment:
<!-- Please provide the following environment details --> 
* python version
* pip version
* OS details

### Do you want to work on this issue?
<!-- yes/no -->


Pull request description:
Added foreign key relationship between a Collection and managed Class
<!-- Please create (if there is not one yet) a issue before sending a PR -->
<!-- Add issue number (Eg: fixes #123) -->
<!-- Always provide changes in existing tests or new tests -->

Fixes #592

### Checklist
- [x] My branch is up-to-date with upstream/develop branch.
- [x] Everything works and tested for Python 3.6.0 and above.

### Current behaviour
<!-- Describe the code you are going to change and its behaviour -->
Presently, if an object is deleted from a Class it would still be there in the collection table in database( if it was previously inserted in a Collection that directly manages that Class). This was because there was no foreign key relationship between a Class and a Collection. 


### New expected behaviour
<!-- Describe the new code and its expected behaviour -->
![image](https://i.ibb.co/Ssx6zzh/Screenshot-from-2021-06-23-16-46-15.png)
_img : schema -> ID of a class object will be a foreign key to `member` in a Collection_ 
### Change logs
-  Change `member` in Collection table to a foreign key column
-  Added test to check members from a Collection are deleted after the class objects are deleted from the database.
<!-- #### Added -->
<!-- Edit these points below to describe the new features added with this PR -->
<!-- - Feature 1 -->
<!-- - Feature 2 -->


<!-- #### Changed -->
<!-- Edit these points below to describe the changes made in existing functionality with this PR -->
<!-- - Change 1 -->
<!-- - Change 1 -->


<!-- #### Fixed -->
<!-- Edit these points below to describe the bug fixes made with this PR -->
<!-- - Bug 1 -->


<!-- #### Removed -->
<!-- Edit these points below to describe the removed features with this PR -->
<!-- - Deprecated feature 1 -->


Patch:
diff --git a/hydrus/data/db_models.py b/hydrus/data/db_models.py
index 9d08b0a..cd8e6cb 100644
--- a/hydrus/data/db_models.py
+++ b/hydrus/data/db_models.py
@@ -111,6 +111,9 @@ class Resource:
             )
             # if the class is a collection, add an extra column for
             # member @type
+            manages = self.resource["manages"]
+            managed_class = manages["object"].split(expanded_base_url)[1]
+            attr_dict[title] = Resource.foreign_key_column(managed_class, title)
             attr_dict["member_type"] = Column(
                 String,
             )


Test patch:
diff --git a/tests/functional/test_app.py b/tests/functional/test_app.py
index 652fd4e..07c9f59 100644
--- a/tests/functional/test_app.py
+++ b/tests/functional/test_app.py
@@ -279,6 +279,35 @@ class TestApp():
                 delete_response = test_app_client.delete(
                     f'{endpoint["@id"]}/{id_}')
                 assert delete_response.status_code == 200
+    
+    def test_Collection_Class_foreign_key(self, test_app_client, constants, doc):
+        """Test insert data to the collection."""
+        API_NAME = constants['API_NAME']
+        index = test_app_client.get(f'/{API_NAME}')
+        assert index.status_code == 200
+        endpoints = json.loads(index.data.decode('utf-8'))
+        a = []
+        for endpoint in endpoints['collections']:
+            collection_name = '/'.join(endpoint['@id'].split(f'/{API_NAME}/')[1:])
+            collection = doc.collections[collection_name]['collection']
+            collection_methods = [x.method for x in collection.supportedOperation]
+            if 'PUT' in collection_methods:
+                dummy_object = gen_dummy_object(collection.name, doc)
+                collection_response_put = test_app_client.put(endpoint['@id'],
+                                                        data=json.dumps(dummy_object))
+                collection_iri = collection_response_put.location
+                assert collection_response_put.status_code == 201
+                class_name = collection.manages["object"].split(DocUrl.doc_url)[1]
+                class_ = doc.parsed_classes[class_name]['class']
+                class_methods = [x.method for x in class_.supportedOperation]
+                member_ids = get_ids_list(dummy_object)
+                class_endpoint = f"{endpoint['@id'].rsplit('/', 1)[0]}/{class_name}" 
+                if 'DELETE' in class_methods:
+                    delete_endpoint = f"{class_endpoint}?instances={member_ids}"
+                    delete_response = test_app_client.delete(delete_endpoint)
+                    assert delete_response.status_code == 200
+                    collection_final_res = test_app_client.get(collection_iri)
+                    assert collection_final_res.status_code == 404
 
     def test_object_PUT_at_id(self, test_app_client, constants, doc):
         """Create object in collection using PUT at specific ID."""