Hi sqlite-users :)
I have found sqlite as the best solution to all my data storage problems - a
great piece of software!
Im currently working on a specific algorithm to fill in a table of numbers.
Within the range of an index (idx), I have to count the numbers of rows where
(in1==in2) and place this number in the result column for each subsequent
row, if (in1 != in2) I have to fill in a '0' and restart counting, e.g.
idx | in1 | in2 | result
1 | 1 | 1 | 2
2 | 1 | 1 | 2
3 | 1 | 2 | 0
4 | 1 | 2 | 0
5 | 2 | 2 | 4
6 | 2 | 2 | 4
7 | 2 | 2 | 4
8 | 2 | 2 | 4
The algorithm works as follows:
CREATE TABLE work (idx integer, in1 integer, in2 integer,
result integer);
// insert data, order by idx
CREATE TRIGGER update_work UPDATE OF result ON work
WHEN new.result == 0
BEGIN
UPDATE work
SET result = (SELECT count(*) FROM work WHERE result==-1)
WHERE result == -1;
END;
UPDATE work
SET result = CASE
WHEN in1 == 0 THEN 0
WHEN in2 == 0 THEN 0
WHEN in1 != in2 THEN 0
ELSE -1
END;
There is one difficulty: I have to rely on the order of updates, say idx==1
has to be updated first, then idx==2 and so on. In principle the algorithm
works - I wouldn't write this mail if there wasn't a BUT:
In a dataset of max(idx) == 220 I found by inserting a line with the current
idx into a triggerlog-table, that updating started at the 125th row, went to
last last (220), got on with the first (1) und stopped at the 124th row -
ough! [The actual work table had some more columns, the index column is 3
columns wide and and there are 4 but 1 result columns - but the principle is
the same ;)].
My question(s): is there a way to urge sqlite to start at idx==1 and increment
as expected? If not is there a better way to achieve the goal of my task?
As an afterthought:
Testing the code above on a newly created table (still 220 rows but less
columns) gives the expected results - maybe an issue with sqlite's memory
management?!
Thanks a lot for any help!
With kind regards
Daniel
--
Dipl.-Math. (FH) Daniel Franke
Institut fuer Medizinische Biometrie und Statistik
Medizinische Universität zu Luebeck
Ratzeburger Allee 160, Haus 4
23538 Luebeck
Telefon: 0451-500-2786
Telefax: 0451-500-2999
[EMAIL PROTECTED]
