group DAO;

ensureEntrySchemaInPlace() ::= <<

create table if not exists entry (entry_number serial primary key, sha256hex varchar, timestamp timestamp);

alter table entry alter column entry_number drop default;

create table if not exists total_entries (count integer);

--Insert query below initializes the total_entries table by 0 if it is not initialized yet
insert into total_entries (count) select 0 where not exists (select 1 from total_entries);

>>
group DAO;

ensureEntrySchemaInPlace() ::= <<

create table if not exists entry (entry_number serial primary key, sha256hex varchar, timestamp timestamp);

alter table entry alter column entry_number drop default;

create index if not exists entry_sha256hex_index on entry (sha256hex);

create table if not exists total_entries (count integer);

--Insert query below initializes the total_entries table by 0 if it is not initialized yet
insert into total_entries (count) select 0 where not exists (select 1 from total_entries);

>>

Allow efficient finding of entries by item hash
Allow efficient finding of entries by item hash

The record entries resource needs to find entries by item hash (based on
an item found by primary key on a gin index).  Without this index, this
defaults to a sequential scan:

    explain select entry_number, timestamp, sha256hex from entry
    where sha256hex in
      (select sha256hex from item
       where content @> '{"school":"402175"}');
                                QUERY PLAN
    ------------------------------------------------------------------
     Hash Semi Join  (cost=55.51..82.15 rows=1 width=77)
       Hash Cond: ((entry.sha256hex)::text = (item.sha256hex)::text)
       ->  Seq Scan on entry  (cost=0.00..24.00 rows=1000 width=77)
       ->  Hash  (cost=55.50..55.50 rows=1 width=65)
             ->  Seq Scan on item  (cost=0.00..55.50 rows=1 width=65)
                   Filter: (content @> '{"school": "402175"}'::jsonb)

(the `Seq Scan` bit is the nasty bit.)

With this index, we can show that the query planner will exploit it:

    explain select entry_number, timestamp, sha256hex from entry
    where sha256hex in
      (select sha256hex from item
       where content @> '{"school":"402175"}');
                                            QUERY PLAN
    ------------------------------------------------------------------------------------------
     Nested Loop  (cost=16.21..29.75 rows=565 width=44)
       ->  Bitmap Heap Scan on item  (cost=12.01..16.02 rows=1 width=32)
             Recheck Cond: (content @> '{"school": "402175"}'::jsonb)
             ->  Bitmap Index Scan on item_content_gin  (cost=0.00..12.01 rows=1 width=0)
                   Index Cond: (content @> '{"school": "402175"}'::jsonb)
       ->  Bitmap Heap Scan on entry  (cost=4.20..13.67 rows=6 width=44)
             Recheck Cond: ((sha256hex)::text = (item.sha256hex)::text)
             ->  Bitmap Index Scan on entry_sha256hex_index  (cost=0.00..4.20 rows=6 width=0)
                   Index Cond: ((sha256hex)::text = (item.sha256hex)::text)

(note in particular the `Bitmap Index Scan on entry_sha256hex_index`.)

NB: I haven't actually tested this end-to-end, merely demonstrated that
the indexer will create the index and that the query planner for
presentation's query will detect and use it.
