View source

__NOTOC__''This page describes the design of the database table holding the connections.'' == Description == 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 == === 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: {| valign="top" border="1" cellpadding="3" cellspacing="0" |- style="color:white; background-color:#000000;" | | '''Name''' || '''Type''' || '''Description''' |- valign="top" | <tt>Id</tt> || integer primary key || Unique id within this table. |- valign="top" | <tt>TaksId</tt> || integer || Unique id of the corresponding task. |- valign="top" | <tt>DeliverableId</tt> || integer || Unique id of the corresponding deliverable (not the clone). |- valign="top" | <tt>IsDependency</tt> || integer || Whether or not this connection is a dependency (0=false, 1=true). |} === Constraints === The table has the following constraints: * When the task is deleted, the connection must be deleted as well. * When the deliverable is deleted, the connection must be deleted as well. === 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 == === Interface structure === <syntaxhighlight lang="c"> typedef struct { uint32 id, uint32 task_id, uint32 deliverable_id, bool is_dependency, } tdd_connection; </syntaxhighlight> === Create table === <syntaxhighlight lang="sql"> CREATE TABLE Connections ( Id INTEGER PRIMARY KEY, TaskId INTEGER REFERENCES Tasks(Id) ON DELETE CASCADE, DeliverableId INTEGER REFERENCES Deliverables(Id) ON DELETE CASCADE, IsDependency INTEGER ); ALTER TABLE Deliverables ADD ConnectionId INTEGER REFERENCES Connections(Id) ON DELETE CASCADE </syntaxhighlight> === Connect === <syntaxhighlight lang="sql"> INSERT INTO Connections SELECT t.Id TaskId, d.Id DeliverableId, %d IsDependency, 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> === Disconnect === <syntaxhighlight lang="sql"> 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> == References == <references/> ---- * [[Database]]

Return to Connections database table.