In order to manipulate a database which has been created through Easik, the following conditions must be met:
The sketch which defines the database must exist as a sketch node in the overview. (See importing/exporting a sketch)
An active connection to a compatible database on an SQL server must exist. (See connect to an SQL database)
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.
Add Row to table: Adds a row to the highlighted table. The user specifies values for each column through a popup dialog. In some cases, extra columns may be specified (see shadow edges).
Insert row(s) via query: Pops up a query dialog from which the user is free to execute any SQL INSERT query.
Select row and update: The user is prompted to select a row from the highlighted table. A dialog is then displayed that allows the user to update any of the row's column values.
Delete row(s) from table: The user is prompted to select one or more rows from the highlighted table. The selected rows are then deleted from the table. Note that cascade behaviour for affected edges is invoked.
Delete row(s) via query: Pops up a query dialog from which the user is free to execute any SQL DELETE query. cascade
View table contents: Displays the contents of the highlighted table on the screen.
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.