Task list database table - TaskDepender

Task list database table

From TaskDepender
Jump to: navigation, search
(Undo revision 637 by Fred (talk))
(→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 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]].
  
−
Furthermore, to automatically maintain consistency, a constraint must be added to the table as well so that when the original task is deleted, the corresponding task in this table is deleted as well. However, 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 [[Tasks 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>.
+
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.
  
−
The id of the current container can be retrieved by using the [[State variables database 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]).
 +
 
 +
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 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')  
−
  FOREIGN KEY(Id) REFERENCES Tasks(Id) ON DELETE CASCADE
+
 
</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"