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 ==
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]].
  
−
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. Otherwise, upon deleting a task from the current diagram, an explicit statement must be executed to delete the task from this table as well in addition to deleting it from the [[Tasks database table|tasks 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.
  
−
However, when the table is created using the AS-SELECT statement, the constraint cannot implemented yet as it cannot be part of the statement as can be seen from the syntax diagram above. 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]]. However, the [www.sqlite.org SQLite] only supports a limited subset of [http://www.sqlite.org/lang_altertable.html ALTER TABLE] and (amongst other) does not allow adding constraints to 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>.
+
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]).
  
−
Since the table is created and filled at the same time, the table must be deleted (i.e. [http://www.sqlite.org/lang_droptable.html dropped]) when the current container changes.
+
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.
−
 
+
−
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 explicitely 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 following section gives the implementation of the required SQL statements.
 
The following section gives the implementation of the required SQL statements.
Line 29: Line 27:
 
== Implementation ==
 
== Implementation ==
  
−
=== Create the table ===
+
=== Create table ===
  
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
Line 41: Line 39:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
−
=== Add reference to the original task ===
+
=== Delete table ===
−
 
+
−
<syntaxhighlight lang="sql">
+
−
ALTER TABLE TaskList ADD
+
−
  Parent INTEGER REFERENCES Tasks(Id) ON DELETE CASCADE
+
−
</syntaxhighlight>
+
−
 
+
−
=== Fill reference field ===
+
−
 
+
−
<syntaxhighlight lang="sql">
+
−
UPDATE TaskList SET Parent=Id
+
−
</syntaxhighlight>
+
−
 
+
−
=== Delete the table ===
+
  
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">

Latest revision as of 11:16, 18 October 2011

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