Introduction to Easik

Introduction to Easik

Easik is a Java based development kit for EA sketches. Easik allows graphical modelling of EA sketches and views. This information can be converted to SQL code and then saved as a text file, or exported directly to a database. Once exported, Easik supports interaction with the new database which allows the user to experiment with design decisions.

Any suggestions to make Easik a better product should be directed to rrosebrugh@mta.ca

Overview

Introduction

Easik Overview Window

The overview window opens when Easik is started and remains open throughout the program's entire execution. The overview window allows the user to have multiple sketches open for editing simultaneously and allows the definition views of sketches.

Editing Overview

The editing actions available when working with the overview:
Overview - Popup Menus

Opening a Sketch in Edit Mode

To open a sketch for editing, it must exist as a sketch node in the overview. Right clicking on the sketch node and selecting Edit sketch from the popup menu will open the sketch for editing. If there is currently no active connection to an SQL server, a double click will also enter edit-mode. If there is an active connection to a database, a double-click will open the sketch in data manipulation mode.

Note: A sketch cannot be edited if it has been exported to an SQL server. If the user wishes to make edits to the sketch, they must first verify that they realize that edits will break the sketch/database synchronization.

Opening a Sketch in Data Manipulation Mode

To open a sketch in data manipulation mode, right-click over the sketch's representation in the overview and select Manipulate database from the popup menu. If a valid SQL connection currently exists, Easik will immediately enter data manipulation mode on the selected sketch. If there is no such connection, the user is prompted for connection information, and a connection attempt is made. Should a connection establish successfully, data manipulation mode will be entered. See database manipulation.

Opening a View

From the overview, a view can be opened to edit by either right clicking over it and selecting Open view from the popup menu, or double clicking over it.

Editing a Sketch

Sketch Editing Actions

This section outlines the actions available when editing a sketch. (See opening a sketch to edit.) Note that any action specified to be in a popup menu can also be found in the Easik Edit menu. If the action requires information about position on the sketch (such as adding an entity), a random position is used.
Edit Sketch - Popup Menus

Add Entity

Right-click at the position on the canvas where the new entity should be placed. Select Add entity... from the popup menu.

Add Edge

Easik supports four types of edges: edge, injective edge, partial edge, and self-referencing edge. To add an edge to the sketch, highlight the desired entities and select Add <edge-type> from the popup menu. Note that self-referencing edges require only one node, while the others require two. A dialog appears prompting for the edge's name and cascade behaviour.

Add Constraint

Easik supports five types of constraints: commutative diagrams, sum constraints, product constraints, pullback constraints and equalizer constraints. Select Add <constraint type> from the Constraint menu of Easik to add the constraint to the sketch. See notes on each constraint type for details on adding them to a sketch.

Add Attribute

Right click on an entity and select Add Attribute from the popup menu. A dialog pops up prompting for the new attribute's name and type.

Edit Attribute

Right click on the attribute in the information tree located at the right of the sketch window and select Edit Attribute from the popup menu. The selected attribute can then be renamed, and its type redefined.

Delete Attribute

Right click on the attribute in the information tree located at the right of the sketch window and select Delete Attribute from the popup menu. The selected attribute will then be deleted from its entity.

Add Unique Key

Right click on an entity that has at least one attribute or at least one non-injective edge and select Add Unique Key from the popup menu. A dialog pops up prompting for the key's name, attributes and edges. Control-click to select multiple attributes/edges.

Edit Edge

Right click on an edge and select Edit edge from the popup menu. A dialog pops up allowing the edge to be renamed and its cascade behaviour specified.

Rename Entity

Right click on an entity and select Rename Entity from the popup menu. A dialog pop up prompting for the entity's new name.

Delete

Right click with any combination of entities and edges highlighted. Choosing Delete from the popup menu will remove the highlighted selection from the sketch.

Edge Types

This section describes the four types of edges available within Easik. See Add Edge for details on adding an edge.
Sketch edge types

Normal edges

The normal edge type in Easik is a non-nullable reference to a row of another entity. This represents a many-to-one relationship between entities.

Injective edges

Injective edges are non-nullable, unique references to a row of another entity. Because the reference must be unique, this represents a one-to-one relationship between entities, and is often used to "is-a" relationships between entities.

Partial edges

Partial edges are like normal edges, but may be null; that is, they may not reference a tuple of another entity at all.

Self-referencing partial edges

Self-referencing edges allow tuples of an entity to refer to other tuples of the same entity. These edges must be partial: it would not be possible to insert the first tuple of a table if a null value for the reference value was not permitted. Self-referencing edges can be useful for representing tree or graph structures within a database.

Constraints

There are several EA constraints which can be implemented and represented graphically using Easik. These constraints include commutative diagrams, sum constraints, product constraints, pullback constraints and equalizer constraints. Defining constraints requires selecting paths in the sketch. This is done by successive ctrl-clicks on composable edges and then clicking Next or Finish as described below. Note that by definition, some entities involved in constraints have rows that are automatically generated. Easik restricts insertion and deletion on such entities.

Warning!

Invalid Interacting Constraints

We cannot guarantee the correctness of interacting constraints. Our implementations make some combinations invalid. Such an example is when a pullback is a sum. The pullback constraint will try to insert into a table that has, due do a sum constraint, had insertions restricted.

Commutative Diagram

A Commutative Diagram

To add a commutative diagram constraint, select Add a Commutative Diagram from the Constraints menu.

Select the first path involved in the commutative diagram. This path should begin with the domain of the commutative diagram and should terminate at the co-domain. Once the path is selected, click Next.

After selecting the first path, the user will then be prompted to select the second path. Once the second path is selected, the user may then choose Next or Finish depending on whether more paths are involved in the commutative diagram constraint, or whether all paths are accounted for. The user will continue to be prompted to add more paths to the commutative diagram until the Finish or Cancel button is pressed. There is no upper bound to how many paths can be involved in a commutative diagram.

Should the commutative diagram requirements be violated, an error will be produced and no path will be built.

After it is created, new paths can be added to the constraint by right clicking over it in the sketch and selecting Add path(s) to constraint. Path selection works as described above.

Sum Constraint

A Sum Constraint

To add a sum constraint to the sketch, select Add Sum Constraint from the Constraints menu. Select the first path involved in the constraint. This path should begin at a summand and end at the sum. Confirm this path by pressing the Next button at the bottom of the window.

After selecting the first path, the user will then be prompted to select the second path. Once the second path is selected, the user may then choose Next or Finish depending on whether more paths are involved in the sum constraint, or whether all paths are accounted for. The user will continue to be prompted to add more paths to the sum constraint until the Finish or Cancel button is pressed. There is no upper bound to how many paths can be involved in a sum constraint.

There are several conditions which must be observed for the successful creation of a sum constraint:

Should these conditions not be met, an error message will appear, and the constraint will not be created.

After it is created, new paths can be added to the constraint by right clicking over it in the sketch and selecting Add path(s) to constraint. Path selection works as described above.

Product Constraint

A Product Constraint

To add a product constraint to the sketch, select Add Product Constraint from the Constraints menu. The user will then be prompted to select the first path involved in the constraint, and confirm this path by pressing the Next button at the bottom of the sketch pane.

After selecting the first path, the user will then be prompted to select the second path. Once the second path is selected, the user may then choose Next or Finish depending on whether more paths are involved in the product constraint, or whether all paths are accounted for. The user will continue to be prompted to add more paths to the product constraint until the Finish or Cancel button is pressed. There is no upper bound to how many paths can be involved in a product constraint.

There are several conditions which must be observed for the successful addition of a product constraint:

Should these conditions not be met, an error message will appear.

After it is created, new paths can be added to the constraint by right clicking over it in the sketch and selecting Add path(s) to constraint. Path selection works as described above.

Pullback Constraint

A Pullback Constraint

To add a pullback constraint to the sketch, select Add a Pullback Constraintfrom the Constraints menu. The user then defines the paths involved in the pullback constraint. The paths must be selected in the correct order.

The first and second paths must have a common codomain, the codomain of the pullback. The third path must have the pullback entity as its domain and its codomain must be the domain of the first selected path. The fourth path has the pullback entity as its domain and its codomain must be the domain of the second selected path. After the fourth path is selected, click Finish.

There are several conditions which must be observed for the successful addition of a pullback constraint:

Should these conditions not be met, an error message will appear, and the constraint will not be created.

Equalizer Constraint

An Equalizer Constraint

To add an equalizer constraint to the sketch, select Add Equalizer Constraint from the Constraints menu. The user will then be prompted to select the first path involved in the constraint. This path must be a single injective edge that has the equalizer entity as its domain. Confirm this selection by pressing the Next button at the bottom of the window.

After selecting the first path, the user will then be prompted to select the second path. Its domain must be the codomain of the first path. A third path with the same domain and codomain as the second path must be selected, and the user may then choose Next or Finish depending on whether more paths are involved in the equalizer constraint, or whether all paths are accounted for. The user will continue to be prompted to add more paths to the equalizer constraint until the Finish or Cancel button is pressed. There is no upper bound to how many paths can be involved in a equalizer constraint.

There are several conditions which must be observed for the successful addition of an equalizer constraint:

Should these conditions not be met, an error message will appear, and the constraint will not be created.

Adding Paths

Some constraints support path addition after their creation. These include commutative diagrams, product constraints, and sum constraints. To add one or more paths to a constraint, right click on its node and select Add path(s) to constraint from the popup menu. Paths can then be selected by successive ctrl-clicks on composable edges and clicking either next to select another path, or finish to add the selected paths to the constraint.

Sketch Exporting

This page describes the various ways of exporting an Easik sketch.

Exporting a Sketch to an SQL Server

This action is started by right clicking on the desired sketch from the overview and selecting Export to SQL server from the popup menu. If there is currently no active connection to a database, the user is prompted for connection information and database parameters. The following is a list of the database parameters.

Database Parameters

Once a connection is established, the sketch (and its views) are converted to SQL code (see notes on shadow edges) and sent to the server. The database is created and the sketch is opened in data manipulation mode. The user can now interact with the new database. Note that this export action can also be found in the file menu of the sketch window - File | Export to | SQL server.

Exporting a Sketch to XML

To export a sketch to an XML file, right click on its representation in the overview and select Export sketch to XML from the popup menu. The action can also be fired through the menu bar in the sketch window - File | Export to | XML. Select where the XML is to be saved and click OK. Note that all views of this sketch are automatically exported. Note also that these XML files are given a .sketch extension. See import sketch for details on importing.

Exporting a sketch to an SQL Text File

This action can be fired from the file menu of the sketch window - File | Export to | SQL text file. The following steps are then taken to export the sketch to an SQL text file.

  1. Select SQL dialect (MySQL or PostgreSQL).

  2. Select database parameters.

  3. Enter database connection options

    • Database name: The name used for the new database.

    • Schema name (PostgreSQL only): The name used for the new schema.

    • Identifier quoting: See identifier quoting for details.

  4. Select where the SQL text file is to be saved and click OK. See shadow edges for details on auto-generated SQL code.

Export a Sketch to Image File

This action can be fired from the File menu in the sketch window - File | Export to | Image. Select where to save the image file and click OK. The default image type is png. The image type is specified by including its extension in the filename. Supported image types are png, jpeg, gif, and bmp.

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.

View

Easik currently supports read-only views. See adding and opening views. Views of a sketch are automatically included when the sketch is exported to XML or SQL.

Editing a View

View - Editing Popup Menus

A view in Easik is simply a collection of query nodes. A query node is a named node on the view canvas that represents an SQL SELECT query.

Data Types

Easik supports many SQL data types for attributes, and allows you to specify any additional data type supported by your database. Note that the main data types listed here may be slightly different, depending on the SQL dialect in use (for example, the TEXT type becomes a LONGTEXT when exporting to MySQL).

Numeric Types

The following numeric types are available:

Numeric Types

Character/Data Types

The following character and data types are available:

Character/Data Types

Date/Time Types

The following date/time types are available:

Date/Time Types

Other

The following other types are supported by Easik:

Other Types

Database Connection

Easik requires an active connection to an SQL server for some of its functionality, such as exporting a sketch to database and manipulating a database which is defined by an Easik sketch. When Easik determines that a connection is needed, if a one has not previously been established, the user has the option to make a connection.

Establishing a connection

Database Connection
  1. Select an SQL dialect (MySQL or PostgreSQL).

  2. Select database parameters.

    If exporting, the user is prompted for database parameters.

  3. Enter database connection options:

    • Username: The username on the database server.

    • Password: <Username>'s password on the database server.

    • Database hostname: Location of the database server.

    • Database port: The port on which to attempt the connection. Defaults are used when this field is left blank.

    • Database name: The name of the database that is defined by the current sketch. If exporting the sketch, this is the name that will be given to the new database. If connecting to a database, this must be the name with which the sketch had been exported.

    • Schema name (PostgreSQL only): The name of the schema in which this sketch exists in the database. If exporting the sketch, this is the new schema name. If connecting to a database, this must be the name of the schema in which this sketch exists.

    • Identifier quoting: If enabled, this setting will make EASIK use SQL identifier quoting when interacting with the database. When enabled, this allows you to use non-alphanumeric values in entity, edge, and attribute names, which will be preserved in the generated SQL tables. Note, however, that if quoting is used, it must continually be used, including other tools accessing the database (for MySQL, this means using `identifier`, and for PostgreSQL, "identifier"). Also note that for PostgreSQL, enabling identifier quoting will make table and column names case-sensitive.

      When disabled, non-alphanumeric characters will be converted to underscores.

      This setting defaults to off, and should not be enabled unless the above is desired.

      Also note that when an entity or attribute conflicts with a built-in SQL keyword for the driver being used, quoting of the identifier will be forced, even if this setting is disabled.

Preferences

Preferences are accessed through the Edit → Preferences menu of an overview or a sketch that is open for editing. (Note: on Mac OS X, the Preferences option is in the standard location in the main application menu, rather than the Edit menu).

General

General preferences control the main Easik settings.

General preferences tab.

Path

The path setting defines the default system folder Easik uses when opening or saving files. You can set this to be the last folder a file was opened or saved from ("Last used folder"), the folder Easik was started from ("Running directory"), or a specific folder.

The default setting is the last used folder.

Show attributes & unique keys

This option controls whether or not attributes and unique keys are displayed in new sketches. The default option is enabled. This can be controlled for a sketch which is open for editing from the sketch window's Edit menu.

The default setting is the last used folder.

Thumbnail scale

This option controls the size of sketch and view thumbnails displayed in the overview. A setting of 0.25 means that the thumbnail of sketches and views will be of their actual size; 0.5 would indicate of the actual size.

The default setting is 0.25 ( size).

Colours

Colour settings control the look and feel of Easik sketches, views, and the main overview window.

Colour preferences tab.

The colours tab allows you to adjust the colours and, in some cases, line widths, used within Easik for displaying sketches, overviews, and views. To change a colour, click the Edit button beside the colour you wish to change. To change a line or border width, drag the slider beside the colour to select the desired width.

SQL Defaults

The SQL Defaults settings allow you to control the default settings to be used when connecting to a database, to manipulate an existing set of tables, or export a sketch to a database.

SQL Defaults preferences tab.

Default SQL driver

This setting controls the default SQL driver used when connecting and exporting. Currently, MySQL and PostgreSQL database connections are supported.

Primary key column names

This setting controls how the primary key column will be named when exporting a sketch to an SQL server or an SQL file.

The default, "id", uses the name "id" for the primary key of all tables. Alternatively, "<tablename>_id" can be used to base the primary key column on the table name: for example, the primary key of the entity "Car" would be "Car_id". You can also use a custom name; <table> in this custom field will be replaced with the table name.

Foreign key column names

This setting controls how foreign keys will be named when exporting a sketch to an SQL server or SQL file.

The default, "Use edge labels", will use the label specified for an edge name in a sketch as the foreign key column. Alternatively, you may use the target table name followed by the edge label, target table name alone, and target table name followed by _id name "id" as the foreign key columns. Note that using a foreign key that does not contain the edge label will not work if there are parallel edges between entities. You may also specify a custom naming scheme: <source> will be replaced with the source entity name; <target> with the target entity name; and <edge> with the edge label.

Identifier quoting

This sets the default enabling of identifier quoting.

Edge cascading

This setting allows you to set the default cascading mode for foreign keys (edges). Note that you can also specify this for each edge within a sketch: this setting controls only the default cascading mode.

The two options here are cascading, and restricted. Cascading deletions means that when deleting a row, any referenced rows will also be deleted (subject to any foreign keys of the referencing rows). Restricted disallows any deletion while a row is still referenced.

The default Easik setting is cascading deletions.

Partial edge cascading

This setting allows you to set the default cascading mode for partial edges (in SQL, these are nullable foreign keys). In addition to the two options available for edge cascading, you may also specify "Set null": this option sets any referring foreign key values to NULL when deleting a row.

The "Set null" option is the default for partial edges.