1

I'm looking for performing a copy of a "dependency structure". What I mean is: there is a tree structure representing a product and its sub components. Each of this products may have one or more related tasks and these tasks may depend on each other. Here is a visual Graph representing the connections.

The user would then create a project, choose the products and the tasks would be available. When choosing a product, it creates a local copy associated to the project, because the user may want to change the product's name or description. The same will happen to the tasks.

The thing is that the relationship between the tasks would require multiple queries :

  • For a node from the product get the related tasks
  • If task has not a copy already created, create it
  • Check if the task has children
  • For each child task:
    • check if it's already create, if it's created just add the new relationship
    • otherwise create and add the new relationship

This is performed by Python and I would love to use SQL for security reasons. Does anyone have an idea on how to do it?

Edit : I'm currently using SQLite, but in the future I will transition to SQL Server.

Edit : The tables are already in place. For the Product table, I use Django-Mptt which works great in this case. For the Task table I'm using a many-to-many connection (no through table).

Edit : Creation statements:

CREATE TABLE
task (
"id"       INTEGER NOT NULL PRIMARY KEY autoincrement, 
"name"     varchar(50) NOT NULL
)

CREATE TABLE "relation_task" (
"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"from_activity_id" integer NOT NULL REFERENCES "task" ("id") DEFERRABLE INITIALLY DEFERRED,
"to_activity_id" integer NOT NULL REFERENCES "task" ("id") DEFERRABLE INITIALLY DEFERRED);

CREATE TABLE "product_template" 
             ( 
                          "id"       INTEGER NOT NULL PRIMARY KEY autoincrement, 
                          "name"     varchar(50) NOT NULL, 
                          "lft"       integer unsigned NOT NULL CHECK ("lft" >= 0), 
                          "rght"      integer unsigned NOT NULL CHECK ("rght" >= 0), 
                          "tree_id"   integer unsigned NOT NULL CHECK ("tree_id" >= 0), 
                          "level"     integer unsigned NOT NULL CHECK ("level" >= 0), 
                          "parent_id" integer NULL REFERENCES "product_template" ("id") deferrable initially deferred
             )

Edit : There are also tables created in the "Project" schema. These tables are the ones that contains the copy for each project, i.e:

User 1 creates a Project A. He selects a template from product_template table, for example product x. This will create a hard copy in the table in the Project schema table called project_products. This table is the same thing as product_template but with a key relating it to the project and also to original copied node. Because he selected product x, all tasks related to this templates are also copied to a table called project_tasks.

The objective is to copy the full tree of the product and all the related tasks into the project schema.

On the templates:

   Product X                  Task 1 --- Product X
    ├── Sub Component X.1     Task 2 (depend on T1) --- Sub Component X.2
    └── Sub Component X.2     Task 3 --- Sub Component X.1
                              Task 4 (depends on T1 T3)--- Sub Component X.1 

Why copy? Because this will allow users to add new items and modify added items without changing the validated templates.

CREATE TABLE
"project_task" (
"id"       INTEGER NOT NULL PRIMARY KEY autoincrement, 
"name"     varchar(50) NOT NULL,
"project" integer REFERENCES "project" ("id") deferrable initially deferred
)

CREATE TABLE "project_relation_task" (
"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"from_activity_id" integer NOT NULL REFERENCES "task" ("id") DEFERRABLE INITIALLY DEFERRED,
"to_activity_id" integer NOT NULL REFERENCES "task" ("id") DEFERRABLE INITIALLY DEFERRED);

CREATE TABLE "project_product" 
             ( 
                          "id"       INTEGER NOT NULL PRIMARY KEY autoincrement, 
                          "name"     varchar(50) NOT NULL, 
                          "lft"       integer unsigned NOT NULL CHECK ("lft" >= 0), 
                          "rght"      integer unsigned NOT NULL CHECK ("rght" >= 0), 
                          "tree_id"   integer unsigned NOT NULL CHECK ("tree_id" >= 0), 
                          "level"     integer unsigned NOT NULL CHECK ("level" >= 0), 
                          "parent_id" integer NULL REFERENCES "project_product" ("id") deferrable initially deferred,
, 
                          "original_node" integer NULL REFERENCES "project_product" ("id") deferrable initially deferred,
, 
                          "project" integer REFERENCES "project" ("id") deferrable initially deferred
             )

The current solution is three loops in Python : for each node, for each task connected to this node, for each child task.

Thanks in advance

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.