Database Manipulation

Setup

In order to manipulate a database which has been created through Easik, the following conditions must be met:

By a compatible database, we mean a database onto which our sketch maps. (Entities to tables, attributes to columns, edges to foreign keys, constraints to triggers and unique keys to unique indexes.) There is currently no utility to automatically detect compatibility between database and sketch. If an export was triggered from within Easik and no editing has been done to the sketch since, we trust that the user has not connected to the server by other means and altered the database. We therefore assume the database is compatible. If, however, our sketch has no knowledge of a database synchronization (e.g. it was created by an Easik-generated SQL text file) the user is prompted to verify that they are indeed connecting to an appropriate database.

Manipulation Actions

The section outlines the actions available by right clicking on an entity while the sketch is open in data manipulation mode. Note that as mentioned in constraints, insertion and deletion is restricted on some entities.
Sketch Manipulation Mode - Popup Menus

Shadow Edges

As noted on the constraint page, some constraints automatically generate rows in tables that are involved in the constraint. For example, insertion into a summand causes insertion into the sum entity (and possibly into intermediate tables between the sum and the summand, for summands with paths consisting of multiple edges). All attributes and partial edges for these new rows will be set to NULL (they can be updated manually after the insertion operations are complete), however regular and injective edges are foreign keys that are not nullable. To work around this, when Add Row to Table is selected, the user is given the option to select values for foreign keys from those tables in which new rows may be generated. This is implemented by foreign keys from the table at which the initial insert occurs to the target table for the actual foreign key, and handled in the triggers generated when exporting a sketch to SQL. These are called shadow edges. If conditions are right for a row to be generated, the specified values for shadow edges will be used for the generated rows.

It is important to note that the provided shadow edge values are not necessarily used, as some insertions do not necessarily create new rows in constraint tables. For instance, inserting into a table involved in an pullback that does not form a matching pair will not result in any new entry in the pullback table. Thus, shadow edge values are not always required, though insertion will fail if an edge is required but has not been specified.

There is no practical limit to the number of shadow edges Easik supports, though very complex cases--such as using complex path loops and overlapping paths for different constraints--may result in a situation where insertion fails or does not provide enough differentiation possibilities. For example, suppose entity n were involved in three different constraint paths that need to be created for an insertion: only a single n value can be specified, though it is quite possible that each path should refer to a different n value. The workaround for EASIK currently is that the n values of each row be updated after the necessary rows have been created.

Note that because of MySQL limitations, shadow edges in MySQL will retain shadow edge values. These should not interfere with any use of the tables as they set themselves to null if their target is deleted or updated, but note that these shadow edge foreign keys are not meant to be used after insertion. Under PostgreSQL, the shadow edges will always be set to null once constraint operations complete.