View source

__NOTOC__''This page describes the temporary database table that holds all the connections in the current diagram.''

== Description ==

When drawing the connections in the current diagram in TaskDepender&trade;, basically for each connection only the type of the connection and the coordinates of the corresponding task and deliverable are required. Therefore a 'convenience' table is created that holds this information of the current container.

== Design ==

A temporary table <tt>ConnectionList</tt> is created that has the following columns:

{| valign="top" border="1" cellpadding="3" cellspacing="0"
|- style="color:white; background-color:#000000;" |
| '''Name''' || '''Type''' || '''Description'''
|- valign="top"
| <tt>Id</tt> || integer || The reference to the id in the <tt>Connections</tt> table.
|- valign="top"
| <tt>Task_x</tt> || integer || The x-coordinate of the task.
|- valign="top"
| <tt>Task_y</tt> || integer || The y-coordinate of the task.
|- valign="top"
| <tt>Deliverable_x</tt> || integer || The x-coordinate of the deliverable.
|- valign="top"
| <tt>Deliverable_y</tt> || integer || The y-coordinate of the deliverable.
|- valign="top"
| <tt>IsDependency</tt> || integer || Whether or not the connection is a dependency.
|}


The list can be filled with all those connections for which the task is located in the current container. This can be done by performing a join between the [[Tasks database table|<tt>Tasks</tt>]] table, the [[Deliverables database table|<tt>Deliverables</tt>]] table and the [[Connections database table|<tt>Connections</tt>]] table. The <tt>Id</tt> of the current container can be retrieved by using the [[State variables database table|<tt>StateVariables</tt>]] table. When the current container changes, the table must be emptied and refilled again.

To maintain consistency, every time a connection is delete or added (inserted) the temporary table must be changed as well. To handle deletion of a connection from the current diagram, a reference is to be made to the original entry in the [[Connections database table|<tt>Connections</tt>]] table. This can be done by using the <tt>Id</tt> column of the [[Connections database table|<tt>Connections</tt>]] table. When a connection is added in the [[Connections database table|<tt>Connections</tt>]] table, the <tt>Id</tt> of the last inserted element is available via the [http://www.sqlite.org/lang_corefunc.html#last_insert_rowid <tt>last_insert_rowid()</tt>] SQL-function. This can be used in the select statement of the insert.

The following section gives the implementation of the required SQL statements.

== Implementation ==

=== Create table ===

<syntaxhighlight lang="sql">
CREATE TEMPORARY TABLE ConnectionList
(
  Id INTEGER REFERENCES Connections(Id) ON DELETE CASCADE,
  Task_x INTEGER,
  Task_y INTEGER,
  Deliverable_x INTEGER,
  Deliverable_y INTEGER,
  IsDependency Integer
)
</syntaxhighlight>

=== Fill the table ===

<syntaxhighlight lang="sql">
INSERT INTO ConnectionList
  SELECT c.Id, t.X, t.Y, d.X, d.Y, c.IsDependency
  FROM Connections c, Tasks t, Deliverables d
  WHERE
    c.TaskId      = t.Id AND 
    d.Id          = c.DeliverableId AND
    t.ContainerId = (SELECT VALUE FROM StateVariables WHERE KEY='CurrentContainerId')
</syntaxhighlight>

=== Insert new connection ===

<syntaxhighlight lang="sql">
INSERT INTO ConnectionList
  FROM Connections c, Tasks t, Deliverables d
  WHERE
    c.Id            = last_insert_rowid() AND
    c.TaskId        = t.Id AND 
    c.DeliverableId = d.Id
</syntaxhighlight>

=== Empty table ===

<syntaxhighlight lang="sql">
DELETE FROM ConnectionList
</syntaxhighlight>


----
* [[Database]]