Connections database table
From TaskDepender
(→Disconnect) |
(→Description) |
||
| Line 3: | Line 3: | ||
== Description == | == Description == | ||
| − | As specified by the [[Task management method|"Task management method"]], | + | 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 == | ||
| Line 34: | Line 34: | ||
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"]]. | 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"]]. | ||
| − | 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 join between the [[Selected tasks database table|selected tasks]] and the [[Selected deliverables database table|selected deliverables]]. | + | 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 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: | 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: | ||
| Line 61: | Line 61: | ||
--> <todo, move this to connection list> The [[Connection list database table|connection list]], holding all the connections in the current diagram must be recreated. | --> <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]]. | + | 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. | 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. | ||
| Line 113: | Line 119: | ||
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
| − | DELETE FROM Connections WHERE TaskId | + | 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> | ||
