Connections database table
From TaskDepender
(→Design) |
(→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 == | ||
+ | |||
+ | === 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 13: | Line 15: | ||
| '''Name''' || '''Type''' || '''Description''' | | '''Name''' || '''Type''' || '''Description''' | ||
|- valign="top" | |- valign="top" | ||
− | | <tt>Id</tt> || integer primary key || Unique id within this | + | | <tt>Id</tt> || integer primary key || Unique id within this table. |
|- valign="top" | |- valign="top" | ||
| <tt>TaksId</tt> || integer || Unique id of the corresponding task. | | <tt>TaksId</tt> || integer || Unique id of the corresponding task. | ||
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 | + | === Connect === |
+ | |||
+ | 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 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: | ||
+ | * 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. | ||
+ | |||
+ | 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 45: | Line 82: | ||
uint32 deliverable_id, | uint32 deliverable_id, | ||
bool is_dependency, | bool is_dependency, | ||
− | |||
− | |||
} tdd_connection; | } tdd_connection; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
Line 68: | Line 103: | ||
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
INSERT INTO Connections | INSERT INTO Connections | ||
− | SELECT t. | + | 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 | + | 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]] |