We synchronize database objects between the database and the web service layers through reflection of those objects. For any object created through the Mathesar web interface, we do this by having the web service create a model instance in its service database corresponding to the created object. However, for objects created via other means, we reflect these in the Mathesar UI by capturing the state of relevant objects in the database, and propagating that state upwards to the web service’s models, and then eventually the UI. For more information about database reflection, see the SQLAlchemy docs.
The UI should reflect the current state of the DB, subject to performance constraints.
We include the
oid field for each database object model (currently table or schema) in order to identify it in the database.
Whenever we perform an action such as ‘list all tables in schema’ or ‘list all schemata in database’, we reflect all DB objects. However, we only do so at most every 5 minutes. This is accomplished by checking the Django cache for a particular key,
database_reflected_recently. We don’t store any actual data in the cache, but rather in the
Table models. The cache only serves to let us know when to re-reflect the underlying DB objects and make appropriate changes to the model tables.
In the current codebase, we trigger the function to check the cache and reflect DB objects when needed whenever we access the queryset for either the
mathesar/views/api.py). To do this, we use the special
get_queryset function. This gets picked up and automatically run when the ViewSet is called. Using this allows us to run arbitrary code before returning the queryset. In particular, we call the function
reflect_db_objects in the same module. This function finds all DB objects by their
oid, and notes any changes.
Since we use the
name property of DB objects more than anything else, we cache it using the Django cache framework.
We’ll “import” a previously existing DB simply by reflecting all schemata in the database, and then reflecting all tables in each schemata, adding the resulting objects to the
Table model tables as we go.
Records and Columns of tables are always read from the DB on the fly.