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 == | ||
| − | In | + | In TaskDepender™ 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 == | == Design == | ||
| − | The | + | 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]] | |
| − | This can be | + | 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= | + | 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> | ||
| + | |||
| + | ---- | ||
| + | |||
| + | * [[Database]] | ||
