[MODINVSTOR-27] Join requirements for items-holdings-instances Created: 14/Nov/17 Updated: 15/Jan/19 |
|
| Status: | Open |
| Project: | mod-inventory-storage |
| Components: | None |
| Affects versions: | None |
| Fix versions: | None |
| Type: | New Feature | Priority: | P3 |
| Reporter: | Niels Erik Nielsen | Assignee: | Unassigned |
| Resolution: | Unresolved | Votes: | 0 |
| Labels: | core | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | 4 hours, 15 minutes | ||
| Original estimate: | Not Specified | ||
| Issue links: |
|
||||||||||||||||||||||||||||||||
| Sprint: | |||||||||||||||||||||||||||||||||
| Development Team: | Core: Platform | ||||||||||||||||||||||||||||||||
| Description |
|
Specify the business logic requirements for searching, sorting (and details display) and thus for joining items, holdings and instances and possibly the set of reference tables. There are two main join scenarios:
Small scale joins – ie pulling in reference table translations – could conceivably be done in business logic module. Large scale joins more likely on the database level. |
| Comments |
| Comment by Niels Erik Nielsen [ 15/Nov/17 ] |
|
I'm not entirely clear on whether we're developing the Codex search, or a native Inventory search, or both at this point. The Codex search would not require any joins (assuming that the Codex API is basically our instance record minus a few fields) The Inventory UX prototype so far has one suggestion (which is just a suggestion at this point) for filtering, that would require a potentially somewhat large scale join between 'instance' and 'holdings-record' (filtering by location). This would be beyond the Codex requirements afaict. But I can spec that out in pseudo code or something. There is nothing in the UX yet that seems to require a three-way join between 'instance', 'holdings-record' and 'item'. I don't know if there will be. We have a 'format' on 'item' for example. Searching by format could result in a very large join, but would we need to search on that, since we also have instance-format and instance-type on 'instance'? I could spec it out regardless, though. I suppose we could eventually make use of a three-way join for the very distinct search of items by barcode. I can make an example of that too. |
| Comment by Niels Erik Nielsen [ 15/Nov/17 ] |
|
Searching for instances by location (from 'holdings-record' table that does not yet exist):
find instance(attributes)
from instance and holdings-record, joined on instance.uuid
where holdings-record(location) = 'some location'
location could be a UUID, so potentially a join with a 'location' table as well. Finding an instance (one that would be) by item barcode:
find instance(attributes) and holdings(location, callnumber, holdingsstatements) and item (format, status, temp location, barcode)
from instance and holdings-record and item, joined on instance UUID and holdings UUID
where item.barcode = 'some barcode'
Searching for instances by item format (??):
find instance(attributes)
from instance and holdings-record and item, joined on instance UUID and holdings UUID
where item.formatID = 'some UUID'
As for sorting, we're so far displaying title, creators(repeated), identifiers(repeated), publishers(repeated), publication dates(repeated). Guess these would be the initial sorting candidates for instances results lists. |
| Comment by shale99 [ 03/Dec/17 ] |
|
cases 2 and 3 seem to be very similar (bar-code matching / format matching) data population: (existing indexes in mod-inv-storage not shown here, fields that need to be searchable / sortable need indexes as well) INSERT INTO cql7_mod_inventory_storage.holdings_record
SELECT id,
jsonb_build_object(
'id', id,
'instanceId', instance_id ,
'permanentLocationId' , permanentLocationId)
FROM (select gen_random_uuid() AS id , generate_series(10000000, 13000001) || '-a3e4-465c-82f1-acade4e8e170' as instance_id ,
'd9cd0bed-1b49-4b5e-a7bd-064b8d17723' || trunc(random() * 9) as permanentLocationId) as alias;
INSERT INTO cql7_mod_inventory_storage.instance
SELECT id,
jsonb_build_object(
'id', id,
'title', title,
'source' , source,
'edition' , edition,
'instanceTypeId' , '2b94c631-fca9-a892-c730-03ee529ffe2c',
'creators', json_build_array(jsonb_build_object('name', 'ari sigal' , 'creatorTypeId' , '2b94c631-fca9-a892-c730-03ee529ffe2a')))
FROM (select (generate_series(10000000, 13000001) || '-a3e4-465c-82f1-acade4e8e170')::uuid AS id ,
(md5(random()::text)) || ' lörd the root ' || (md5(random()::text)) AS title,
(md5(random()::text)) as source, trunc(random() * 20) || ' ed.' as edition) as alias;
INSERT INTO cql7_mod_inventory_storage.item
SELECT id,
jsonb_build_object(
'id', id,
'barcode' , barcode ,
'status' , jsonb_build_object('name', barcode || '21ag'),
'materialTypeId' , '1c302429-5d78-4f8b-a367-3437bf1ab56e',
'permanentLoanTypeId', '2c302429-5d78-4f8b-a367-3437bf1ab56e',
'temporaryLoanTypeId', '2c302429-5d78-4f8b-a367-3437bf1ab56e',
'title', title,
'permanentLocationId' , permanentLocationId)
FROM (select gen_random_uuid() AS id ,
trunc(random() * 200000) || 'g' AS barcode,
((md5(random()::text)) || ' zeev shalev ' || (md5(random()::text))) AS title,
(generate_series(10000000, 13000001) || '-a3e4-465c-82f1-acade4e8e170')::uuid AS permanentLocationId) as alias;
//Searching for instances by location
CREATE OR REPLACE VIEW cql7_mod_inventory_storage.instance_holding_view AS
select
u._id,u.jsonb as jsonb, g.jsonb as h_jsonb from cql7_mod_inventory_storage.instance u
join
cql7_mod_inventory_storage.holdings_record g on lower(f_unaccent(u.jsonb->>'id')) = lower(f_unaccent(g.jsonb->>'instanceId'))
//Finding an instance (one that would be) by item barcode:
CREATE OR REPLACE VIEW cql7_mod_inventory_storage.item_instance_holding_view AS
select
it._id,it.jsonb as jsonb, ho.jsonb as h_jsonb, ins.jsonb as ins_jsonb from cql7_mod_inventory_storage.instance ins
join cql7_mod_inventory_storage.holdings_record ho on lower(f_unaccent(ins.jsonb->>'id')) = lower(f_unaccent(ho.jsonb->>'instanceId'))
join cql7_mod_inventory_storage.item it on lower(f_unaccent(it.jsonb->>'permanentLocationId')) = lower(f_unaccent(ho.jsonb->>'instanceId'))
INDEXES
instance (lower(f_unaccent(jsonb->>'id')));
item (lower(f_unaccent(jsonb->>'permanentLocationId')));
holdings_record (lower(f_unaccent(jsonb->>'instanceId')));
holdings_record using gin (lower ( f_unaccent(jsonb->>'permanentLocationId')) gin_trgm_ops);
QUERIES //find instances by location //Finding an instance (one that would be) by item barcode: (SORT BY FIELDS IN INSTANCE TABLE ONLY!) select ins_jsonb from cql7_mod_inventory_storage.item_instance_holding_view where lower(f_unaccent(jsonb->>'barcode')) ~ '5f0bc5a84750f6be3358f03330f02' order by lower(f_unaccent(ins_jsonb->>'title')) limit 10; |
| Comment by shale99 [ 03/Dec/17 ] |
|
this view can be added manually (writing the actual sql) in the schema.json. |