Task list database table

Revision as of 17:30, 16 October 2011 (view source)

Fred (Talk | contribs)

← Older edit

Latest revision as of 11:16, 18 October 2011 (view source)

Fred (Talk | contribs)

(→Implementation)

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 ==

Line 11:

== 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]]

−

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~~ [http://www.sqlite.org/~~foreignkeys~~.html ~~foreign-key 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~~.

+

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 ==

−

=== Create ~~the~~ table ===

+

=== Create table ===

−

CREATE TEMPORARY TABLE ~~TasksAdministration~~

+

CREATE TEMPORARY TABLE TaskList AS

−

AS SELECT *,

+

SELECT *,

(SELECT COUNT(ContainerId) from Tasks where Tasks.ContainerId=t.Id)>0

OR

(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>

−

=== Delete ~~the~~ table ===

+

=== Delete table ===

−

DROP TABLE ~~TasksAdministration~~

+

DROP TABLE TaskList

</syntaxhighlight>

Task list database table

Latest revision as of 11:16, 18 October 2011

Views

Personal tools

Navigation

Search

Toolbox

@@ 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 ==
@@ Line 11: / Line 11: @@
 == 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]]
-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 [http://www.sqlite.org/foreignkeys.html foreign-key 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.
+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 ==
-=== Create the table ===
+=== Create table ===
 <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
    OR
    (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>
-=== Delete the table ===
+=== Delete table ===
 <syntaxhighlight lang="sql">
-DROP TABLE TasksAdministration
+DROP TABLE TaskList
 </syntaxhighlight>