Look this above! This will help you. I did't maked all test cases!
/***************************************************************************/
CREATE TABLE account (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
initial_balance REAL
);
CREATE TABLE movement (
id INTEGER PRIMARY KEY AUTOINCREMENT,
account_id INTEGER REFERENCES account(id),
payment UNIXEPOCH,
amount REAL,
balance REAL
);
CREATE INDEX movent_idx01 ON movement (account_id, payment, id);
DROP TRIGGER IF EXISTS movement_trg01;
CREATE TRIGGER movement_trg01 AFTER DELETE ON movement FOR EACH ROW
BEGIN
UPDATE movement
SET balance = balance - old.amount
WHERE account_id = old.account_id
AND (payment > old.payment
OR (payment = old.payment
AND id > old.id));
END;
DROP TRIGGER IF EXISTS movement_trg02;
CREATE TRIGGER movement_trg02 AFTER INSERT ON movement FOR EACH ROW
BEGIN
REPLACE INTO movement
SELECT t1.id
, t1.account_id
, t1.payment
, t1.amount
, (COALESCE((SELECT balance
FROM movement s1
WHERE s1.account_id = t1.account_id
AND (s1.payment < t1.payment
OR (s1.payment = t1.payment
AND s1.id < t1.id))
GROUP BY s1.account_id
HAVING s1.payment = MAX(s1.payment)
AND s1.id = MAX(s1.id)), 0)
+ t1.amount) AS balance
FROM movement t1
WHERE id = new.id;
UPDATE movement
SET balance = balance + new.amount
WHERE account_id = new.account_id
AND payment >= new.payment
AND id > new.id;
END;
-- account and payment not changed
DROP TRIGGER IF EXISTS movement_trg03;
CREATE TRIGGER movement_trg03 AFTER UPDATE ON movement FOR EACH ROW WHEN
((old.account_id == new.account_id) AND (old.payment == new.payment) AND
(new.amount != old.amount))
BEGIN
UPDATE movement
SET balance = balance + (new.amount - old.amount)
WHERE account_id = old.account_id
AND (payment > old.payment
OR (payment = old.payment
AND id >= old.id));
END;
-- account_id or payment changed
DROP TRIGGER IF EXISTS movement_trg03;
CREATE TRIGGER movement_trg03 AFTER UPDATE ON movement FOR EACH ROW WHEN
(((old.account_id != new.account_id) OR (old.payment != new.payment)) AND
(new.amount != old.amount))
BEGIN
-- same code as delete trigger
UPDATE movement
SET balance = balance - old.amount
WHERE account_id = old.account_id
AND (payment > old.payment
OR (payment = old.payment
AND id > old.id));
-- same code as insert
REPLACE INTO movement
SELECT t1.id
, t1.account_id
, t1.payment
, t1.amount
, (COALESCE((SELECT balance
FROM movement s1
WHERE s1.account_id = t1.account_id
AND (s1.payment < t1.payment
OR (s1.payment = t1.payment
AND s1.id < t1.id))
GROUP BY s1.account_id
HAVING s1.payment = MAX(s1.payment)
AND s1.id = MAX(s1.id)), 0)
+ t1.amount) AS balance
FROM movement t1
WHERE id = new.id;
UPDATE movement
SET balance = balance + new.amount
WHERE account_id = new.account_id
AND payment >= new.payment
AND id > new.id;
END;
INSERT INTO account (name, initial_balance) VALUES ('account 1', 0);
INSERT INTO account (name, initial_balance) VALUES ('account 2', 100.0);
INSERT INTO account (name, initial_balance) VALUES ('account 3', -100.0);
INSERT INTO movement (account_id, payment, amount) VALUES (1, 1275503470,
123.45);
INSERT INTO movement (account_id, payment, amount) VALUES (1, 1275503475,
-24.10);
INSERT INTO movement (account_id, payment, amount) VALUES (1, 1275503475,
50.00);
UPDATE movement
SET amount = 150.45
WHERE payment = 1275503470;
-- to know what are the current balance of a account
SELECT a.initial_balance + m.balance
FROM account a
JOIN movement m ON (a.id = m.account_id)
GROUP BY m.account_id
HAVING payment = MAX(payment);
/***************************************************************************/
----- Mensagem original -----
De: "Israel Lins Albuquerque" <israel...@polibrasnet.com.br>
Para: steph...@mankowski.fr, "General Discussion of SQLite Database"
<sqlite-users@sqlite.org>
Enviadas: Terça-feira, 1 de Junho de 2010 17:12:02
Assunto: Re: [sqlite] Performance issue on view
Create a new table to do this and add a trigger on op to make the sum.
----- Mensagem original -----
De: "Stéphane MANKOWSKI" <steph...@mankowski.fr>
Para: sqlite-users@sqlite.org
Enviadas: Terça-feira, 1 de Junho de 2010 16:57:16
Assunto: [sqlite] Performance issue on view
Hi,
In the this database file (http://skrooge.org/files/test.wrk), I created a
table
named "op" containing banking
transactions.
A transaction has:
An unique id
An account
A date
An amount
I created a view named "v_op" with one more computed attribute named
"balance".
This attribute is the sum of all previous transactions (including current one)
for the same account.
My problem is that v_op is very slow. This is not usable.
What can I do to improve performances ?
PS: I don't want to compute "balance" attribute by code and save it in op
balance due to the fact that I am using an undo/redo mechanism.
Regards,
Stephane
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Atenciosamente,
Israel Lins Albuquerque
Desenvolvimento
Polibrás Brasil Software Ltda.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Atenciosamente,
Israel Lins Albuquerque
Desenvolvimento
Polibrás Brasil Software Ltda.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
