Insert Non-Primary Key Incrementing variable into Sqlite DB

Question

I have a matrix table that links two objects together.

CREATE TABLE [TPS_TestPlanScenarioMatrix] (
  [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  [testPlanID] INTEGER REFERENCES [TPS_TestPlan]([testPlanID]),
  [scenarioID] INTEGER REFERENCES [TPS_Scenarios]([ID]));

Test plans contain a list of scenarios and may contain more than one copy of the same scenario. My program now needs to allow the user to determine the order of the scenarios belonging to the list in each test plan.

I am using Fluent-NHibernate to map my Sqlite DB Tables to the entities in my project. My current Test Plan mapping is as follows:

Table("TPS_TestPlan");
        Id(x => x.Id).Column("testPlanID");
        Map(x => x.Name).Column("testPlanName");
        Map(x => x.Description).Column("testPlanDescription");

        HasManyToMany(x => x.Scenarios)
            .Table("TPS_TestPlanScenarioMatrix")
            .ParentKeyColumn("testPlanID")
            .ChildKeyColumn("scenarioID")
            .Cascade.None();

Inorder to allow the user to specify the order of the scenarios I have added .AsList(index => index.Column("scenarioOrder")) to my Test Plan Mapping.

I then tried to alter my existing Sqlite Matrix Table and migrate in any previously existing test plan scenario connections using the following:

ALTER TABLE TPS_TestPlanScenarioMatrix RENAME TO tmp;

CREATE TABLE [TPS_TestPlanScenarioMatrix] (
   [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   [testPlanID] INTEGER REFERENCES [TPS_TestPlan]([testPlanID]),
   [scenarioID] INTEGER REFERENCES [TPS_Scenarios]([ID]),
   [scenarioOrder] INTEGER);

INSERT INTO TPS_TestPlanScenarioMatrix(testPlanID,scenarioID,scenarioOrder)
  SELECT testPlanID,scenarioID,
    CASE WHEN EXISTS(SELECT testPlanID FROM TPS_TestPlanScenarioMatrix)
      THEN (SELECT COUNT(testPlanID) FROM TPS_TestPlanScenarioMatrix)
      ELSE 1 
    END as scenarioOrder
  FROM tmp;

DROP TABLE tmp;

But the scenarioOrder is always being entered as 1 since the SELECT COUNT statement doesn't get re evaluated after each insert. I need for each scenario mapping to the same test plan to have an incrementing scenarioOrder from 1 - N. ScenarioOrder must always start at 1 for each new scenario list.

How can I get the SELECT COUNT to re-evaluate after each insert so that my table has the correct incrementing scenarioOrder at the end?

Answer 1

To get different counts from the subquery, you need to use a correlated subquery. In this case, the subquery counts how many rows there are before the current one for the current test plan, i.e., the ordering is by the old ID:

INSERT ...
SELECT ...,
       (SELECT COUNT(*)
        FROM tmp AS t2
        WHERE t2.testPlanID = tmp.testPlanID
          AND t2.ID        <= tmp.ID
       ) AS scenarioOrder
FROM tmp