Front-end has no direct access to sp7-exclusive tables

[grantfitzsimmons]

[grantfitzsimmons]

https://sp7demofish.specifycloud.org/specify/query/new/spdataset/

[maxpatiiuk]

I don't know why, but Ben made some tables not be available through the main specify API (tables for notifications, workbench, permissions and all other specify7-exclusive tables).

Thus, all of those tables are not available to the front-end, except though dedicated APIs. We can look into changing that in the future.

[maxpatiiuk]

Though, the query builder should not show a blank page - it should show a 404 page.
You could open a separate bug for that

[grantfitzsimmons]

It does show a 404 page, so that's not a problem 😄

[maxpatiiuk]

oh I see. it's not visible on the screenshot.
Yeah, then it's just a back-end bug - something that we can consider changing

[melton-jason]

Add a block for

• spdataset

For more context, this is regarding adding a deletion blocker to SpecifyUser. This operation is not currently supported with how I extended deletion blockers, but I will add a way to support adding reverse relationships so this can be added.

Spatasets will more complicated than any other table(s) to work with on the backend. Many of Specify's internal helper functions rely on the datamodel being loaded when Specify starts up (this happens in load_datamodel.py). The issue is that to get the list of tables and fields in the database, Specify 7 reads the specify_datamodel.xml in the Specify 6 config directory (as seen below), and as far as I know the spdataset table is not included within that file.

specify7/specifyweb/specify/load_datamodel.py

Lines 222 to 231 in 4877f75

	def load_datamodel() -> Datamodel:
	datamodeldef = ElementTree.parse(os.path.join(settings.SPECIFY_CONFIG_DIR, 'specify_datamodel.xml'))
	datamodel = Datamodel()
	datamodel.tables = [make_table(tabledef) for tabledef in datamodeldef.findall('table')]
	add_collectingevents_to_locality(datamodel)
	flag_dependent_fields(datamodel)
	flag_system_tables(datamodel)
	return datamodel

We could manually use SQL to get the correct information, but that will only be a short-term solution (which may be fine for now). Preferably, we would want to engineer a way to load/register these Specify 7 specific tables in the internal specify datamodel. (If such a way already exists and I am overlooking it, then feel free to point it out. I have checked all attributes on specify.models and spdataset does not exist)

From #2806 (comment)

It would be nice to have a proper solution to adding these tables into the Specify datamodel, this would be beneficial both for backend use (as described above) and for frontend use. Currently in Specify 7, users are not allowed to query on these tables that are not loaded into the datamodel, which may become an issue if tables are added solely in Specify 7.

From #2835

[maxpatiiuk]

Implementing this would also allow getting rid of some apis (i.e, the notifications API. Some Workbench APIs too potentially, but those would be more difficult as they would also require migrating permissions)

[maxpatiiuk]

@grantfitzsimmons reminded me that it's not just about having access to those tables on the front-end - we would also need to add them to the schema config

minor thing, but that would allow to better resolve #4472 (review)

[acwhite211]

Here a list of the Specify 7 exclusive tables that are currently not in the specify_datamodel.xml file provided by Specify 6:

accounts:

• Spuserexternalid

attachment_gw:

• Spattachmentdataset

business_rules:

• UniquenessRule
• UniquenessRule_Field

notifications:

• Message
• Spmerging

permissions:

• UserPolicy
• Role
• LibraryRole
• UserRole
• RolePolicy
• LibraryRolePolicy

workbench:

• Spdataset

stored_queries:

• All the sqlalchemy models in stored_queries app (not needed in api)

[acwhite211]

My plan to implement this, after the datamodel, django models, and sqlalchemy models are statically defined, is to add these missing tables to the datamodel.py file. In order to prevent making duplicate models across django apps, I think I might add a django app name as a property of each table in the datamodel.

[melton-jason]

PsuedoManyToManyManager

This is a Model Manager, not a Model instance.
It does not represent a table in the database and like the SQLAlchemy models, the frontend shouldn't know it exists.

add these missing tables to the datamodel.py file

Why do the Specify7 exclusive Models need to be added to the datamodel.py file?
Is the motivation to have a representation of all models in one place?
Is the plan to completely migrate the Models to the file, or have an additional representation of the Models alongside the current definitions in their respective Django Apps?

Duplicating the models may run into some problems in the future.
Notably: how will discrepancies be resolved between the model defined in the Django App and the model in the datamodel.py? How will new models be added to the datamodel.py?
I assume there would be some functionality to auto-update the associated model representations in the datamodel.py whenever Django is being initialized.

Alternatively, instead of duplicating the model representations, I would think an adapter can be made to serialize already-existing models for the /context/datamodel.json endpoint.

Regardless of the implementation, I think all models can be loaded from Django by using the following:

(Fetching all models programmatically means that Specify7 models can be created/updated without worrying about or managing some other code specifically for the datamodel code)

from django.apps import apps

all_models = apps.get_models()

[emenslin]

Fixed