Task list database table
From TaskDepender
Line 17: | Line 17: | ||
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]]. | 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. | The following section gives the implementation of the required SQL statements. | ||
Line 39: | Line 37: | ||
(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> | ||