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
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.
New Overview (File | New Overview): Creates a fresh canvas containing no sketches or views. Any unsaved work will be lost.
Save Overview (File | Save Overview): Saves the current overview to an XML file. Note: overview files are saved with a .easik extension.
Open Overview (File | Open Overview): Opens an overview from a .easik file. Any unsaved work will be lost.
Edit sketch (Right click on desired sketch → Edit sketch): See opening a sketch to edit.
Manipulate database (Right click on desired sketch → Manipulate database): See opening a sketch to manipulate a database.
Export Sketch to XML (Right click on sketch → Export sketch to XML): See exporting a sketch to XML.
Export Sketch to SQL Server: See exporting a sketch to SQL server.
Add Sketch (Right click → Add sketch OR Edit | Add sketch): Adds a new empty sketch to the overview canvas.
Import Sketch (Right click → Import sketch OR Edit | Import sketch): To import a sketch (and its views) from an XML file, right click on the overview and select Import sketch from the popup menu. (The action is also available through the Edit menu.) Select the XML file and click OK to add it to the overview.
Add View (Right click on sketch → Add view): Creates a new empty view of the selected sketch.
Rename (Right click on sketch or view → Rename): Prompts the user for the new name of the selection.
Delete (Right click with sketches and/or views selected → Delete): Deletes the current selection.
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.
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.
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.
Right-click at the position on the canvas where the new entity should be placed. Select Add entity... from the popup menu.
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.
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.
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.
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.
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.
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.
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.
Restrict: Causes attempted deletions of referenced rows to fail.
Cascade: Cause deletions in this table to trigger deletions of any rows in other tables that point to the row(s) being deleted.
Set null: Causes references to be set to NULL when the targeted row is deleted (only available on partial 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 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 are like normal edges, but may be null; that is, they may not reference a tuple of another entity at all.
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.
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.
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.
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:
All paths involved must target the same entity
All paths involved must have the first edge in the path be an injective edge
At least two paths must be selected
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.
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:
All paths involved must have the same source entity
At least two paths must be selected
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.
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:
The first and second paths must have a common codomain
The third and fourth paths must have a common domain
The codomain of the third path must be the domain of the first; the codomain of the fourth path must be the domain of the second
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:
The first path must be an injective edge
All paths (excluding the first) have as their source the target entity of the first edge
All paths (excluding the first) must have the same entity as their target
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.
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.
Create database: Set database definition to create a new database.
Drop database before creating: Available if Create database enabled. Sets database definition to drop an existing database in the event of a naming conflict.
Drop and recreate schema (PostgreSQL only): Sets database definition to drop and recreate a schema in the event of a naming conflict.
Use BIGINTs instead of INTs for keys: Enabling this option changes the type of foreign key columns and the primary key columns to which they point from the default of int(11) to bigint(20).
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.
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.
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.
Select SQL dialect (MySQL or PostgreSQL).
Select database parameters.
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.
Select where the SQL text file is to be saved and click OK. See shadow edges for details on auto-generated SQL code.
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.
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.
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.
Adding a Query Node (Right click → Add query node): The user is prompted for the query node name. This will become its SQL view name when exported to SQL. The query entered is an SQL SELECT statement that the view implements.
Editing a Query Node (Right click → Define query node OR double-click): The user is prompted for a new name and new SELECT statement for the selected node.
The following numeric types are available:
INTEGER: An integer value field (usually a 32-bit int) that stores integer values from -2147483648 to 2147483647"
SMALLINT: An integer value field (usually a 16-bit int) that stores integer values from (at least) -32768 to 32767
BIGINT: An integer value field (usually a 64-bit int) that stores integer values from -9223372036854775808 to 9223372036854775807
DOUBLE PRECISION: A floating point value with at least 15 digits of precision (typically a standard 64-bit floating-point value with 53 bits of precision)
FLOAT: A floating point value with at least 6 digits of precision (typically a standard 32-bit floating-point value with 24 bits of precision). This is sometimes known as a REAL, but a REAL is also sometimes an alias for a DOUBLE PRECISION
NUMERIC: A fixed-point numeric type. Also known as DECIMAL. This type is substantially slower than integer and floating-point types, but guarantees precision for the range of values it supports. The precision value is the total number of digits storable, and the scale is the number of digits stored after the decimal point. "12345.67" has precision 7 and scale 2.
The following character and data types are available:
VARCHAR: Stores a string of characters of up to size characters. Unlike a CHAR, a VARCHAR column is typically stored using the minimum storage space required, while a CHAR field pads shorter strings to always store values of size length.
CHAR: Stores a string of characters of up to size characters. Unlike a VARCHAR, a CHAR column is typically padded up to the specified size to make it a fixed-width column (the padding is removed on retrieval). Note that some databases implicitly convert CHAR columns to VARCHAR if other variable-size columns exist in the table.
TEXT: Stores large amounts of text data. Also sometimes known as a CLOB.
BLOB: Stores large amounts of binary data (bytes). Will result in a BYTEA under PostgreSQL.
The following date/time types are available:
DATE: A date field that does not include a time, such as "2008/07/14".
TIME: A field that stores just a time (e.g. "12:13:14").
TIMESTAMP: A field that stores a date and time (e.g. "2008/07/14 12:13:14"). Note that this is converted to a DATETIME when using MySQL.
The following other types are supported by Easik:
BOOLEAN: A column that stores true/false values. Note that this type may be converted to a small integer type by databases (such as MySQL) that do not fully support BOOLEAN data types.
Custom Data Type: This allows you to specify any data type supported by your database. Note that the type you enter here will not be checked for correctness: ensure that the provided type is valid for the SQL type(s) you intend to use.
Select an SQL dialect (MySQL or PostgreSQL).
Select database parameters.
If exporting, the user is prompted for database parameters.
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.
General preferences control the main Easik settings.
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.
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.
Colour settings control the look and feel of Easik sketches, views, and the main overview window.
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.
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.
This setting controls the default SQL driver used when connecting and exporting. Currently, MySQL and PostgreSQL database connections are supported.
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.
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.
This sets the default enabling of identifier quoting.
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.
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.