Being in process of building my idioms “collection” for sqlite/iphone, wanted to share/log few lines showing “upsert” and coalesce usage.
create table usage (pid string, channel integer, count integer, PRIMARY KEY (pid, channel));
INSERT OR REPLACE INTO usage (pid, channel, count)
values (11, 1, coalesce((select count+1 from usage where pid = 11 and channel = 1),1));
The above sample counts number of usages for the selected pid and channel. Provider id and order submission channel in my case.