Merge lp:~tiagosh/history-service/fix-unreadCount-trigger into lp:history-service

[Tiago Salem Herrmann]

Fix unreadCount update.

Fix unreadCount update.

--Checklist--

Are there any related MPs required for this MP to build/function as expected? Please list.
No

Is your branch in sync with latest trunk (e.g. bzr pull lp:trunk -> no changes)
Yes

Did you perform an exploratory manual test run of your code change and any related functionality on device or emulator?
Yes

Did you successfully run all tests found in your component's Test Plan (https://wiki.ubuntu.com/Process/Merges/TestPlan/history-service) on device or emulator?
Yes

If you changed the UI, was the change specified/approved by design?
N/A

If you changed the packaging (debian), did you add a core-dev as a reviewer to this MP?
N/A

[PS Jenkins bot (ps-jenkins)]

PASSED: Continuous integration, rev:159
http://jenkins.qa.ubuntu.com/job/history-service-ci/130/
Executed test runs:
    SUCCESS: http://jenkins.qa.ubuntu.com/job/history-service-utopic-amd64-ci/25
    SUCCESS: http://jenkins.qa.ubuntu.com/job/history-service-utopic-armhf-ci/25
        deb: http://jenkins.qa.ubuntu.com/job/history-service-utopic-armhf-ci/25/artifact/work/output/*zip*/output.zip
    SUCCESS: http://jenkins.qa.ubuntu.com/job/history-service-utopic-i386-ci/25

Click here to trigger a rebuild:
http://s-jenkins.ubuntu-ci:8080/job/history-service-ci/130/rebuild

review: Approve (continuous-integration)

[Gustavo Pichorim Boiko (boiko)]

It would be nice to also add to v8.sql some UPDATE queries to update the existing data, otherwise it will only be updated when new events arrive.

review: Needs Fixing

[lp:~tiagosh/history-service/fix-unreadCount-trigger]

160. By Tiago Salem Herrmann on 2014-07-30

also update voice events

[Gustavo Pichorim Boiko (boiko)]

Did you perform an exploratory manual test run of the code change and any related functionality on device or emulator?
Yes

Did CI run pass? If not, please explain why.
Yes

Have you checked that submitter has accurately filled out the submitter checklist and has taken no shortcut?
Yes

After discussing more with Tiago, we came to the conclusion it is not worth writing queries to update the unread count on existing entries as they are not simple queries and we risk breaking existing installations, so the counters will only be updated after new events arrive on each thread.
Code looks good and works as expected!

review: Approve

[PS Jenkins bot (ps-jenkins)]

PASSED: Continuous integration, rev:160
http://jenkins.qa.ubuntu.com/job/history-service-ci/131/
Executed test runs:
    SUCCESS: http://jenkins.qa.ubuntu.com/job/history-service-utopic-amd64-ci/26
    SUCCESS: http://jenkins.qa.ubuntu.com/job/history-service-utopic-armhf-ci/26
        deb: http://jenkins.qa.ubuntu.com/job/history-service-utopic-armhf-ci/26/artifact/work/output/*zip*/output.zip
    SUCCESS: http://jenkins.qa.ubuntu.com/job/history-service-utopic-i386-ci/26

Click here to trigger a rebuild:
http://s-jenkins.ubuntu-ci:8080/job/history-service-ci/131/rebuild

review: Approve (continuous-integration)

=== added file 'plugins/sqlite/schema/v8.sql'
--- plugins/sqlite/schema/v8.sql	1970-01-01 00:00:00 +0000
+++ plugins/sqlite/schema/v8.sql	2014-07-30 16:39:22 +0000
@@ -0,0 +1,137 @@
+DROP TRIGGER text_events_insert_trigger;
+DROP TRIGGER text_events_update_trigger;
+DROP TRIGGER text_events_delete_trigger;
+DROP TRIGGER voice_events_insert_trigger;
+DROP TRIGGER voice_events_update_trigger;
+DROP TRIGGER voice_events_delete_trigger;
+
+CREATE TRIGGER text_events_insert_trigger  AFTER INSERT ON text_events
+FOR EACH ROW
+BEGIN
+    UPDATE threads SET count=(SELECT count(eventId) FROM text_events WHERE
+        accountId=new.accountId AND
+        threadId=new.threadId)
+        WHERE accountId=new.accountId AND threadId=new.threadId AND type=0;
+    UPDATE threads SET unreadCount=(SELECT count(eventId) FROM text_events WHERE
+        accountId=new.accountId AND threadId=new.threadId AND newEvent='1')
+        WHERE accountId=new.accountId AND threadId=new.threadId AND type=0;
+    UPDATE threads SET lastEventId=(SELECT eventId FROM text_events WHERE
+        accountId=new.accountId AND
+        threadId=new.threadId
+        ORDER BY timestamp DESC LIMIT 1)
+        WHERE accountId=new.accountId AND threadId=new.threadId AND type=0;
+    UPDATE threads SET lastEventTimestamp=(SELECT timestamp FROM text_events WHERE
+        accountId=new.accountId AND
+        threadId=new.threadId
+        ORDER BY timestamp DESC LIMIT 1)
+        WHERE accountId=new.accountId AND threadId=new.threadId AND type=0;
+END;
+CREATE TRIGGER text_events_update_trigger  AFTER UPDATE ON text_events
+FOR EACH ROW
+BEGIN
+    UPDATE threads SET count=(SELECT count(eventId) FROM text_events WHERE
+        accountId=new.accountId AND
+        threadId=new.threadId)
+        WHERE accountId=new.accountId AND threadId=new.threadId AND type=0;
+    UPDATE threads SET unreadCount=(SELECT count(eventId) FROM text_events WHERE
+        accountId=new.accountId AND threadId=new.threadId AND newEvent='1')
+        WHERE accountId=new.accountId AND threadId=new.threadId AND type=0;
+    UPDATE threads SET lastEventId=(SELECT eventId FROM text_events WHERE
+        accountId=new.accountId AND
+        threadId=new.threadId
+        ORDER BY timestamp DESC LIMIT 1)
+        WHERE accountId=new.accountId AND threadId=new.threadId AND type=0;
+    UPDATE threads SET lastEventTimestamp=(SELECT timestamp FROM text_events WHERE
+        accountId=new.accountId AND
+        threadId=new.threadId
+        ORDER BY timestamp DESC LIMIT 1)
+        WHERE accountId=new.accountId AND threadId=new.threadId AND type=0;
+END;
+CREATE TRIGGER text_events_delete_trigger  AFTER DELETE ON text_events
+FOR EACH ROW
+BEGIN
+    UPDATE threads SET count=(SELECT count(eventId) FROM text_events WHERE
+        accountId=old.accountId AND
+        threadId=old.threadId)
+        WHERE accountId=old.accountId AND threadId=old.threadId AND type=0;
+    UPDATE threads SET unreadCount=(SELECT count(eventId) FROM text_events WHERE
+        accountId=old.accountId AND threadId=old.threadId AND newEvent='1')
+        WHERE accountId=old.accountId AND threadId=old.threadId AND type=0;
+    UPDATE threads SET lastEventId=(SELECT eventId FROM text_events WHERE
+        accountId=old.accountId AND
+        threadId=old.threadId
+        ORDER BY timestamp DESC LIMIT 1)
+        WHERE accountId=old.accountId AND threadId=old.threadId AND type=0;
+    UPDATE threads SET lastEventTimestamp=(SELECT timestamp FROM text_events WHERE
+        accountId=old.accountId AND
+        threadId=old.threadId
+        ORDER BY timestamp DESC LIMIT 1)
+        WHERE accountId=old.accountId AND threadId=old.threadId AND type=0;
+    DELETE from text_event_attachments WHERE
+        accountId=old.accountId AND
+        threadId=old.threadId AND
+        eventId=old.eventId;
+END;
+CREATE TRIGGER voice_events_insert_trigger  AFTER INSERT ON voice_events
+FOR EACH ROW
+BEGIN
+    UPDATE threads SET count=(SELECT count(eventId) FROM voice_events WHERE
+        accountId=new.accountId AND
+        threadId=new.threadId)
+        WHERE accountId=new.accountId AND threadId=new.threadId AND type=1;
+    UPDATE threads SET unreadCount=(SELECT count(eventId) FROM voice_events WHERE
+        accountId=new.accountId AND threadId=new.threadId AND newEvent='1')
+        WHERE accountId=new.accountId AND threadId=new.threadId AND type=1;
+    UPDATE threads SET lastEventId=(SELECT eventId FROM voice_events WHERE
+        accountId=new.accountId AND
+        threadId=new.threadId
+        ORDER BY timestamp DESC LIMIT 1)
+        WHERE accountId=new.accountId AND threadId=new.threadId AND type=1;
+    UPDATE threads SET lastEventTimestamp=(SELECT timestamp FROM voice_events WHERE
+        accountId=new.accountId AND
+        threadId=new.threadId
+        ORDER BY timestamp DESC LIMIT 1)
+        WHERE accountId=new.accountId AND threadId=new.threadId AND type=1;
+END;
+CREATE TRIGGER voice_events_update_trigger  AFTER UPDATE ON voice_events
+FOR EACH ROW
+BEGIN
+    UPDATE threads SET count=(SELECT count(eventId) FROM voice_events WHERE
+        accountId=new.accountId AND
+        threadId=new.threadId)
+        WHERE accountId=new.accountId AND threadId=new.threadId AND type=1;
+    UPDATE threads SET unreadCount=(SELECT count(eventId) FROM voice_events WHERE
+        accountId=new.accountId AND threadId=new.threadId AND newEvent='1')
+        WHERE accountId=new.accountId AND threadId=new.threadId AND type=1;
+    UPDATE threads SET lastEventId=(SELECT eventId FROM voice_events WHERE
+        accountId=new.accountId AND
+        threadId=new.threadId
+        ORDER BY timestamp DESC LIMIT 1)
+        WHERE accountId=new.accountId AND threadId=new.threadId AND type=1;
+    UPDATE threads SET lastEventTimestamp=(SELECT timestamp FROM voice_events WHERE
+        accountId=new.accountId AND
+        threadId=new.threadId
+        ORDER BY timestamp DESC LIMIT 1)
+        WHERE accountId=new.accountId AND threadId=new.threadId AND type=1;
+END;
+CREATE TRIGGER voice_events_delete_trigger  AFTER DELETE ON voice_events
+FOR EACH ROW
+BEGIN
+    UPDATE threads SET count=(SELECT count(eventId) FROM voice_events WHERE
+        accountId=old.accountId AND
+        threadId=old.threadId)
+        WHERE accountId=old.accountId AND threadId=old.threadId AND type=1;
+    UPDATE threads SET unreadCount=(SELECT count(eventId) FROM voice_events WHERE
+        accountId=old.accountId AND threadId=old.threadId AND newEvent='1')
+        WHERE accountId=old.accountId AND threadId=old.threadId AND type=1;
+    UPDATE threads SET lastEventId=(SELECT eventId FROM voice_events WHERE
+        accountId=old.accountId AND
+        threadId=old.threadId
+        ORDER BY timestamp DESC LIMIT 1)
+        WHERE accountId=old.accountId AND threadId=old.threadId AND type=1;
+    UPDATE threads SET lastEventTimestamp=(SELECT timestamp FROM voice_events WHERE
+        accountId=old.accountId AND
+        threadId=old.threadId
+        ORDER BY timestamp DESC LIMIT 1)
+        WHERE accountId=old.accountId AND threadId=old.threadId AND type=1;
+END;