Task list database table
From TaskDepender
(→Design) |
(→Design) |
||
| 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 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. 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 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. 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 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>. | + | 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]]. |
| + | |||
| + | 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>. | ||
The following section give the implementation of the required SQL-statements. | The following section give the implementation of the required SQL-statements. | ||
