[FOLIO-1072] Add holdings and items to performance-testing system Created: 20/Feb/18  Updated: 12/Nov/18  Resolved: 22/Mar/18

Status: Closed
Project: FOLIO
Components: None
Affects versions: None
Fix versions: None

Type: Task Priority: P3
Reporter: Wayne Schneider Assignee: Wayne Schneider
Resolution: Done Votes: 0
Labels: ci, sprint32, sprint33, sprint34
Remaining Estimate: Not Specified
Time Spent: 1 day, 45 minutes
Original estimate: Not Specified

Issue links:
Blocks
blocks FOLIO-1066 Create AWS environment for performanc... Closed
Sprint:

 Comments   
Comment by shale99 [ 20/Feb/18 ]

hey Wayne Schneider
so the below works as follows , it isnt correct so it needs a small tweak.

the holdings_record has in instanceId which is generated as follows: generate_series(10000000, 13000001) || '-a3e4-465c-82f1-acade4e8e170'

the instance table's id is also generated in the same manner: generate_series(10000000, 13000001) || '-a3e4-465c-82f1-acade4e8e170'

this creates a single holdings record with a reference to the id of a single instance - 3M times

also , the items table generates a permanentLocationId using the same: generate_series(10000000, 13000001) || '-a3e4-465c-82f1-acade4e8e170'

which is of course incorrect, so that would need fixing, but that is the gist of the scripts below

  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;   

Comment by shale99 [ 21/Feb/18 ]

Wayne Schneider = sorry, had a brain lapse - the above wont help much as the instance ids already exist... will update

Comment by shale99 [ 21/Feb/18 ]

ok, this is should work pretty well - i was able to insert 1M in about 15 seconds

   INSERT INTO harvard5_mod_inventory_storage.holdings_record( _id, jsonb ) 
	SELECT gen_random_uuid() ,jsonb_build_object(
        'id',  gen_random_uuid(),
        'instanceId',  jsonb->>'id',
        'callNumber', jsonb->>'instanceTypeId') FROM harvard5_mod_inventory_storage.instance limit 1000000;

however, this will create a different uuid in the record then the one in the _id column - not sure it matters for the demo, but if it does, you can remove the _id from the insert statement and add the following trigger (there may be a simple way to do it without the trigger, didnt dig too deeply into that)

  CREATE OR REPLACE FUNCTION ${myuniversity}_${mymodule}.set_id_injson_${table.tableName}()
  RETURNS TRIGGER AS $$
  DECLARE
    injectedId text;
  BEGIN
    injectedId = '"'||NEW.${table.pkColumnName}||'"';
    NEW.jsonb = jsonb_set(NEW.jsonb, '{id}' ,  injectedId::jsonb , true);
      RETURN NEW;
  END;
  $$ language 'plpgsql';
  CREATE TRIGGER set_id_injson_${table.tableName} BEFORE INSERT OR UPDATE ON ${myuniversity}_${mymodule}.${table.tableName} FOR EACH ROW EXECUTE PROCEDURE ${myuniversity}_${mymodule}.set_id_injson_${table.tableName}();
Comment by Wayne Schneider [ 22/Mar/18 ]

Holdings and items are loaded into folio-perf.aws.indexdata.com

Generated at Thu Feb 08 23:10:37 UTC 2024 using Jira 1001.0.0-SNAPSHOT#100246-sha1:7a5c50119eb0633d306e14180817ddef5e80c75d.