View source

__NOTOC__''This page describes the temporary table that is created to hold all the tasks of the current diagram.''

== Description ==

In TaskDepender&trade; only actions can be performed on the elements visible in the current diagram. Therefore it would be convenient to be able to create a table containing these elements. This table can then be used to perform all the searches so the complete [[Tasks database table|tasks table]] does not have to be queried every time. This is important from a performance aspect as well.

As was described in the [[Task management method|"Task management method"]], tasks that contain elements (the [[Task_management_method#Container tasks|containers]]) are to be displayed differently from the other tasks in the diagram. Therefore, the table could also indicate whether a certain task is a container without the database having to be queried every time for elements that have the id of the task as the <tt>ContainerId</tt>.

The design and the implementation of this 'convenience' table is given in the following sections.

== Design ==

The table must be filled with all the tasks in the current container. This can be done using [http://www.sqlite.org/lang_createtable.html <tt>CREATE AS</tt>] functionality of which the path to be followed is given in the following syntax diagram:

[[Image:Create_as.png]]

Additionally, also a <tt>IsContainer</tt> column needs to be specified which indicates whether the task is container. This can be done by adding it as a named expression to the select part of the syntax diagram and filling it by going through the [[Tasks database table|tasks table]] and the [[Deliverables database table|deliverables table]] and counting the elements that have the <tt>ContainerId</tt> equal to the id of the task that is being added to the table. If this is greater than 0 then the task is a container. The expression must take [[Clone|clones]] into account; those are contained in a task but are not to be counted as being a contained element. The id of the current container can be retrieved by using the [[State variables database table]].

Note that the alternative method using and [http://www.sqlite.org/lang_insert.html <tt>INSERT</tt>] statement would means that the columns must be explicitly defined whereby the design of the columns of the [[Tasks database table|tasks table]] need to be maintained at two locations, which is not preferable and unnecessary if the method above is used.

To maintain consistency, every time an action (update, insert or delete) is performed on the [[Tasks database table|tasks]] or when the current container changes, the task-list table must be deleted (i.e. [http://www.sqlite.org/lang_droptable.html dropped]).

It would be possible to create a reference link to the original table. This is not done to keep the implementation generic because the reference would only be of use when deleting elements from the [[Tasks database table|tasks table]] but not when inserting new elements.

The following section gives the implementation of the required SQL statements.

== Implementation ==

=== Create table ===

<syntaxhighlight lang="sql">

CREATE TEMPORARY TABLE TaskList AS
  SELECT *,
  (SELECT COUNT(ContainerId) from Tasks where Tasks.ContainerId=t.Id)>0
  OR 
  (SELECT COUNT(ContainerId) from Deliverables where Deliverables.ContainerId=t.Id AND Deliverable.CloneId=0)>0 IsContainer 
  FROM Tasks t WHERE ContainerId=(SELECT Value FROM StateVariables WHERE Key='CurrentContainerId') 
</syntaxhighlight>

=== Delete table ===

<syntaxhighlight lang="sql">
DROP TABLE TaskList
</syntaxhighlight>

----

* [[Database]]