Connections database table
From TaskDepender
(→Connect) |
(→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 43: | Line 43: | ||
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. | 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]] | [[Image:Single_source_syntax_diagram.gif]] | ||
| − | Next, | + | 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>''' | '''<TODO: check whether existing connections are deleted or not --> no they're indicated as to be changed first -- error: one for each existing connection>''' | ||
| Line 60: | 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 | + | 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 96: | 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 | INSERT INTO Deliverables | ||
| − | SELECT d.Name, d.Description, d.Resource, d.X, d.Y, d.Width, d.Height, d.Link, d.Available, | + | 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; | |
| − | + | ||
| − | + | ||
| − | + | ||
| − | NewConnections.TaskId ContainerId, NewConnections.Id ConnectionId | + | |
| − | + | ||
| − | + | ||
| − | + | ||
| − | SELECT Id, TaskId, DeliverableId FROM Connections | + | |
| − | WHERE Id NOT IN (SELECT ConnectionId FROM Deliverables) AS NewConnections | + | |
| − | + | ||
| − | + | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 124: | 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> | ||
