Connections database table - TaskDepender

Connections database table

From TaskDepender
Jump to: navigation, search
(→Interface structure)
(→Description)
 
Line 3: Line 3:
 
== Description ==
 
== Description ==
  
−
As specified by the [[Task management method|"Task management method"]], a deliverable can be connected and specified as being a dependency or a deliverable of the task. Although a task will contain a clone for each connection, this does not specify the type of the connection. Therefore, a <tt>Connections</tt> table is created to hold the connections between tasks and deliverables.
+
As specified by the [[Task management method|"Task management method"]], deliverables can be connected to tasks and specified as being a dependency or a deliverable. Although a task will contain a clone for each connection, this does not specify the type of the connection. Therefore, a <tt>Connections</tt> table is created to hold the connections between tasks and deliverables.
  
 
== Design ==
 
== Design ==
 +
 +
=== Definition ===
  
 
The <b><tt>Connections</tt></b> table holds all the connections between deliverables and tasks. There are two connection types: a ''deliverable'' and a ''dependency''. The table has the following columns:
 
The <b><tt>Connections</tt></b> table holds all the connections between deliverables and tasks. There are two connection types: a ''deliverable'' and a ''dependency''. The table has the following columns:
Line 21: Line 23:
 
| <tt>IsDependency</tt> || integer || Whether or not this connection is a dependency (0=false, 1=true).
 
| <tt>IsDependency</tt> || integer || Whether or not this connection is a dependency (0=false, 1=true).
 
|}
 
|}
 +
 +
=== Constraints ===
  
 
The table has the following constraints:
 
The table has the following constraints:
Line 26: Line 30:
 
* When the deliverable is deleted, the connection must be deleted as well.
 
* When the deliverable is deleted, the connection must be deleted as well.
  
−
Connections can only be made between the selected tasks and deliverables. This means that new connections must be [http://www.sqlite.org/lang_insert.html inserted] via a select-statement that results in a join between the [[Selected tasks database table|selected tasks]] and the [[Selected deliverables database table|selected deliverables]].
+
=== Connect ===
  
−
Furthermore, as described in [[Creating a cross reference|"Creating a cross-reference"]], the cross-referencing field of the first table; <tt>ConnectionId</tt> of the [[Deliverables database table|<tt>Deliverables</tt>]] table in this case, must be created when the other table is created. The cross-reference takes care of deleting the clones when the corresponding connection is deleted.
+
Connections can only be made between the selected tasks and deliverables. This is associated with the use cases [[Define dependencies of tasks|"Define dependencies of tasks"]] and [[Define deliverables of tasks|"Define deliverables of tasks"]].
  
−
After this is done, the [[Connection list database table|connection list]], holding all the connections in the current diagram must be recreated.
+
New connections must be inserted<ref name="SQL_insert">[http://www.sqlite.org/lang_insert.html Insert command - SQLite reference]</ref> via a SQL-statement that results in a cross join <ref name="cross_join">[http://en.wikipedia.org/wiki/Join_%28SQL%29#Cross_join Cross join - Wikipedia]</ref> between the [[Selected tasks database table|selected tasks]] and the [[Selected deliverables database table|selected deliverables]]. In the resulting table the <tt>Id</tt> from the selected task must be stored in the <tt>TaskId</tt> column and the <tt>Id</tt> of the selected deliverable must be stored in the <tt>DeliverableId</tt> column. The created connections can only be of one type; either a dependency or not.
  
−
Since the connections are made between the selected task(s) and the selected deliverable(s), multiple connections can be made. For each new connection a clone must be created ''inside'' the corresponding task.
+
Since the connections are made between the selected task(s) and the selected deliverable(s), multiple connections can be made. For each new connection a clone of the deliverable must be created ''inside'' the corresponding task. Using an SQL-statement to do this after the connections have been inserted, is not trivial. There are the following considerations:
 +
* A certain deliverable could be connected to a number of tasks. This means that for a single ''selected'' deliverable, several clones could be required to be created.
 +
* A selected deliverable could already have other connections (e.g. it can be a deliverable of not selected task).
 +
* Only one clone relates to a certain connection.
  
−
To delete the connection, the id of the task and the deliverable are supplied. When the connection is deleted, the clone that references to the connection is deleted also.
+
Taking these considerations into account means that the easiest way to add the clones will probably be to check the adjusted <tt>Connections</tt> table and to see for which connections no clones are present and then create a clone for each one of these. This is done as described in the following.
 +
 
 +
The first thing the SQL statement would require is a list of all the connections for which the <tt>Id</tt> is not in<ref name="not_in">[http://www.sqlite.org/lang_expr.html#in_op The IN and NOT IN operators - SQLite reference]</ref> the selection of the of the <tt>ConnectionId</tt> from the [[Deliverables database table|deliverables table]]. The resulting table can be given an alias and from the SQLite specification<ref name="single_source">[http://www.sqlite.org/syntaxdiagrams.html#single-source Single-source syntax diagram - SQLite syntax specification]</ref> it can be seen that it can be used in the remainder of the SQL query. The syntax diagram is given in the figure below where the "select-stmt" would represent this table.
 +
 
 +
[[Image:Single_source_syntax_diagram.gif]]
 +
 
 +
Next, an inner join<ref name="inner_join">[http://en.wikipedia.org/wiki/Join_%28SQL%29#Inner_join Inner join - Wikipedia]</ref> must be made between this table and the [[Deliverables database table|deliverables table]] in which the join predicate is given by the <tt>DeliverableId</tt> of the connection corresponding to the <tt>Id</tt> of the deliverable. The select statement must retrieve the column values of the deliverable with the acception of the <tt>ConnetionId</tt> and <tt>ContainerId</tt>. These are set to the <tt>Id</tt> of the deliverable and <tt>TaskId</tt> of the connection respectively.
 +
 
 +
The result is a table that can be inserted into the [[Deliverables database table|deliverables table]] as can be seen from the syntax diagram:
 +
 
 +
[[Image:Insert_into_syntax_diagram.gif]]
 +
 
 +
'''<TODO: check whether existing connections are deleted or not --> no they're indicated as to be changed first -- error: one for each existing connection>'''
 +
 
 +
=== Create ===
 +
As described in [[Creating a cross reference|"Creating a cross-reference"]], the cross-referencing field of the first table; <tt>ConnectionId</tt> of the [[Deliverables database table|<tt>Deliverables</tt>]] table in this case, must be created when the other table is created. The cross-reference takes care of deleting the clones when the corresponding connection is deleted.
 +
 
 +
=== Disconnect ===
 +
--> <todo, move this to connection list> The [[Connection list database table|connection list]], holding all the connections in the current diagram must be recreated.
 +
 
 +
To delete the connections between the [[Selected tasks database table|selected tasks]] and [[Selected deliverables database table|selected deliverables]] the is done in a compound SQL statement.
 +
 
 +
This is done by creating a inner join of the [[Selected tasks database table|selected tasks]] and [[Selected deliverables database table|selected deliverables]].
 +
 
 +
Next, the resulting table can be joined with the connections table to create a table of <tt>Id</tt>s of connections for which the <tt>TaskId</tt>s and the <tt>DeliverableId</tt>'s correspond.
 +
 
 +
Finally, the delete statement can simply remove all the connections with the <tt>Id</tt> in this resulting table.
 +
 
 +
Note that due to the added constraint of the [[Deliverables database table|deliverables table]]. When the connection is deleted, the clone that references to the connection is deleted also.
  
 
== Implementation ==
 
== Implementation ==
Line 70: Line 105:
 
   SELECT t.Id TaskId, d.Id DeliverableId, %d IsDependency,
 
   SELECT t.Id TaskId, d.Id DeliverableId, %d IsDependency,
 
   FROM SelectedTasks t, SelectedDeliverables d;
 
   FROM SelectedTasks t, SelectedDeliverables d;
 +
 +
INSERT INTO Deliverables
 +
  SELECT
 +
    d.Name, d.Description, d.Resource, d.X, d.Y, d.Width, d.Height, d.Link, d.Available,
 +
    NewConnections.TaskId ContainerId, NewConnections.Id ConnectionId
 +
  FROM
 +
    Deliverables d,
 +
    (SELECT Id, TaskId, DeliverableId FROM Connections WHERE Id NOT IN (SELECT ConnectionId FROM Deliverables)) AS NewConnections
 +
  WHERE NewConnections.DeliverableId = d.Id;
 
</syntaxhighlight>
 
</syntaxhighlight>
  
Line 75: Line 119:
  
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
−
DELETE FROM Connections WHERE TaskId=%d AND DeliverableId=%d
+
DELETE FROM Connections  
 +
WHERE Id IN
 +
(
 +
  SELECT Id
 +
  FROM
 +
    Connections
 +
  INNER JOIN
 +
    (SELECT t.Id TaskId, d.Id DeliverableId FROM SelectedTasks t, SelectedDeliverables d) AS SelectedConnections
 +
  ON
 +
    Connections.TaskId = SelectedConnections.TaskId
 +
    AND
 +
    Connections.DeliverableId = SelectedConnections.DeliverableId
 +
)
 
</syntaxhighlight>
 
</syntaxhighlight>
  
 +
== References ==
 +
 +
<references/>
  
 
----
 
----
  
 
* [[Database]]
 
* [[Database]]

Latest revision as of 18:17, 29 November 2011

Retrieved from "http://wiki.taskdepender.com/index.php/Connections_database_table"