Task list database table - TaskDepender

Task list database table

From TaskDepender
Jump to: navigation, search
(→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 the [[GUI]] only actions can be performed on the tasks in the current diagram, i.e. that are inside the current container. Therefore, the [[Administration]] only has to supply these to the [[GUI]], by filling its [[Tasks administration class|tasks administration]] attribute. How this is done is described in the following sections.
+
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.
  
−
The following constraints apply:
+
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>.
−
* When the original task is deleted, the corresponding task in this table needs to be deleted as well.  
+
 
 +
The design and the implementation of this 'convenience' table is given in the following sections.
  
 
== Design ==
 
== Design ==
  
−
The [[Tasks administration class|tasks administration]] must be filled with all the [[Task class|tasks]] in the current diagram so that it can be used by the [[GUI]].
+
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:
−
 
+
−
This is done by creating a temporary table that holds all the tasks in the current container.
+
−
 
+
−
This can be using [http://www.sqlite.org/lang_createtable.html <tt>CREATE AS</tt>] functionality:
+
  
 
[[Image:Create_as.png]]
 
[[Image:Create_as.png]]
  
−
Additionally, this class also contains the <tt>IsContainer</tt> field that needs to be specified. 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 task that is being added to the table. If this is greater than 0 then the task is a container. The query statement must take [[Clone|clones]] into account; those are contained in a task but are not to be counted as being a contained element.
+
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]].
  
−
The resulting query is given in the following section.
+
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.
  
−
For performance reasons, all the finds that would be initiated by the [[GUI]] will best be performed on this temporary table.
+
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]).
  
−
The id of the current container can be retrieved by using the [[State variables database table]].
+
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.
  
−
When the table is created using the AS-SELECT statement, the constraint is not implemented yet. Therefore, after the table is created, the table must be [http://www.sqlite.org/lang_altertable.html altered] to create a reference to the original task in the [[Task database table|tasks table]]. The [www.sqlite.org SQLite] only supports a limited subset of [http://www.sqlite.org/lang_altertable.html ALTER TABLE] and it does not allow renaming or removing a column, or add or remove constraints from a table. Therefore, to implement the constraint, an additional column <tt>Parent</tt> is added that is referenced to the <tt>Id</tt> of the [[Tasks database table|tasks table]]. After creating this column, it must still be filled with the same value as the <tt>Id</tt>.
+
The following section gives the implementation of the required SQL statements.
  
 
== Implementation ==
 
== Implementation ==
  
−
=== Create the table ===
+
=== Create table ===
  
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
  
−
CREATE TEMPORARY TABLE TasksAdministration
+
CREATE TEMPORARY TABLE TaskList AS
−
   AS SELECT *,
+
   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>
+
−
 
+
−
=== Add reference to the original task ===
+
−
 
+
−
<syntaxhighlight lang="sql">
+
−
ALTER TABLE TaskList ADD
+
−
  Parent INTEGER REFERENCES Tasks(Id) ON DELETE CASCADE
+
−
</syntaxhighlight>
+
−
 
+
−
=== Fill reference field ===
+
−
 
+
−
<syntaxhighlight lang="sql">
+
−
UPDATE TasksList SET Parent=Id
+
 
</syntaxhighlight>
 
</syntaxhighlight>
  
−
=== Delete the table ===
+
=== Delete table ===
  
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
−
DROP TABLE TasksAdministration
+
DROP TABLE TaskList
 
</syntaxhighlight>
 
</syntaxhighlight>
  

Latest revision as of 11:16, 18 October 2011

Retrieved from "http://wiki.taskdepender.com/index.php/Task_list_database_table"