Task list database table
From TaskDepender
(→Design) |
(→Implementation) |
||
Line 1: | Line 1: | ||
− | ''This page describes the temporary table that is created to hold all the tasks of the current diagram.'' | + | __NOTOC__''This page describes the temporary table that is created to hold all the tasks of the current diagram.'' |
== Description == | == Description == | ||
Line 11: | Line 11: | ||
== Design == | == 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: | + | 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]] | [[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 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 | + | 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 == | == Implementation == | ||
− | === Create | + | === Create table === |
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
− | CREATE TEMPORARY TABLE | + | CREATE TEMPORARY TABLE TaskList AS |
− | + | SELECT *, | |
(SELECT COUNT(ContainerId) from Tasks where Tasks.ContainerId=t.Id)>0 | (SELECT COUNT(ContainerId) from Tasks where Tasks.ContainerId=t.Id)>0 | ||
OR | OR | ||
(SELECT COUNT(ContainerId) from Deliverables where Deliverables.ContainerId=t.Id AND Deliverable.CloneId=0)>0 IsContainer | (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') | + | FROM Tasks t WHERE ContainerId=(SELECT Value FROM StateVariables WHERE Key='CurrentContainerId') |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
</syntaxhighlight> | </syntaxhighlight> | ||
− | === Delete | + | === Delete table === |
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
− | DROP TABLE | + | DROP TABLE TaskList |
</syntaxhighlight> | </syntaxhighlight> | ||