SQLite3 increment partial key sequence

Question

I'm trying to create an incrementing column in SQLite to keep information in the order it exists in an imported text log and grouped by item.

CREATE TABLE log (
    row INTEGER PRIMARY KEY AUTOINCREMENT,
    item TEXT,
    info TEXT
);

Using the following table, I'd like to automatically increment "seq" relative to "item".

CREATE TABLE test (
    item TEXT,
    seq  INTEGER,
    info TEXT,
   CONSTRAINT pk_test PRIMARY KEY (item, seq)
);

I've tried various INSERTs and continually get UNIQUE/CONSTRAINT violations:

INSERT INTO test (item, seq, info)
   SELECT item, (SELECT count(item) FROM test t WHERE l.item=t.item) + 1, info 
FROM log l;

INSERT INTO test (item, seq, info)
   SELECT item, (SELECT COALESCE(MAX(seq),0)+1 FROM test t WHERE l.item=t.item), info 
FROM log l;

When I remove the CONSTRAINT to see the results, "seq" always ends up 1.

Answer 1

The problem is that you're counting rows with the same item value in the test table, but that table is not yet filled.

Just count rows in the log table instead. Because you see all rows, but want only previous rows, you must add another filter (if you don't have something like a timestamp, use the rowid):

..., (SELECT COUNT(*)
      FROM log AS l2
      WHERE l2.item   = l.item
        AND l2.rowid <= l.rowid)), ...