CREATE TRIGGER gc_update_ev_add1 BEFORE INSERT ON Events FOR EACH ROW WHEN NEW.group_uid IS NOT NULL BEGIN INSERT OR IGNORE INTO GroupCache (event_id, service_id, group_uid, total_events, read_events, flags) VALUES (0, NEW.service_id, NEW.group_uid, 0, 0, 0); END; CREATE TRIGGER gc_update_ev_add4 AFTER INSERT ON Events FOR EACH ROW WHEN NEW.group_uid IS NOT NULL BEGIN UPDATE GroupCache SET event_id = NEW.id, total_events = total_events + 1, read_events = read_events + NEW.is_read, flags = flags | NEW.flags WHERE group_uid = NEW.group_uid; END; CREATE TRIGGER gc_update_ev_update AFTER UPDATE ON Events FOR EACH ROW WHEN NEW.group_uid IS NOT NULL BEGIN UPDATE GroupCache SET read_events = read_events - OLD.is_read + NEW.is_read, flags = (flags & (~OLD.flags)) | NEW.flags WHERE group_uid = NEW.group_uid; END;
const char * sqlUpdateGroupCache( "INSERT OR REPLACE INTO groupcache " "SELECT id, service_id, group_uid, total, readcount, mergedflags " "FROM events e, " "(SELECT max(start_time) as maxdate, group_uid as maxgid, count(group_uid) as total, total(is_read) as readcount, _OR(flags) as mergedflags " "FROM events " "GROUP BY maxgid) maxresults " "WHERE group_uid = maxgid AND start_time=maxresults.maxdate " "GROUP BY group_uid");