/
Multitenant data preparation

Multitenant data preparation

Nov 29, 2024

PERF-769: Create document with SQL for data preparation for Mobius testingClosed

Workflow

File Name

First line name

SQL for file creation

File location

automatically added with script

Comment

Workflow

File Name

First line name

SQL for file creation

File location

automatically added with script

Comment

Check-In/Check-Out

CICO_available.csv

item_barcode_availiable

select jsonb->'barcode' from ${tenant}_mod_inventory_storage.item   WHERE    jsonb->'status'->>'name' = 'Available' and jsonb->'barcode'!='null'  order by id asc offset 300 limit 400000

DMS data

Yes

limit 400000 need to be changed

CICO_checked_out.csv

item_barcodes_checkedout

select jsonb->'barcode' from ${tenant}_mod_inventory_storage.item   WHERE    jsonb->'status'->>'name' = 'Checked out' and jsonb->'barcode'!='null'  order by id asc offset 0 limit 1500

DMS data

Yes

Could be created after database population with loans

CICO_service_point.csv

servicePoints_Id

select id from ${tenant}_mod_inventory_storage.service_point limit 1

DMS data

Yes

 

CICO_user_barcodes.csv

user_barcode

select jsonb->'barcode' from ${tenant}_mod_users.users where jsonb->'barcode'!='null' and jsonb->'active'='true' and jsonb->'expirationDate' is null order by id asc offset 10 limit 10

DMS data

Yes

'expirationDate' is null +???

Data Import

100_bib_Create.mrc

-

-

Jmeter artifact

No

50% tenants

${tenant}_100_bib_Update.mrc

-

-

Jmeter artifact

No

50% tenants

10k_bib_Create.mrc

-

-

Jmeter artifact

No

central tenant

[${central_tenant}]_10k_bib_Update.mrc

-

1) 

select completed_date-started_date as job_time,hrid,file_name,started_date,completed_date, status, job_profile_name FROM [tenant]_mod_source_record_manager.job_execution order by started_date desc



2)

select id from [tenant]_mod_inventory_storage.instance where creation_date >= '[started_date]' and creation_date<= '[completed_date]'

Jmeter artifact

No

Run Data Import Create with 10k records on the central tenant and perform queries to the database. Create .csv file with instance id and go to the Data EXPORT, choose created .csv file, choose profile "Export for Data Import updates" -> "instances" in drop-down -> "Run". Steps for testing process#MARCBIBUpdate:~:text=starts%20to%20upload.-,MARC%20BIB%20Update,-To%20run%20MARC

Authority??? 

-

-

Jmeter artifact

-

simplify

Data Export

 

${tenant}_800k_bib_Export.csv

-

select id from ${tenant}_mod_inventory_storage.instance where creation_date >= '[started_date]' and creation_date<= '[completed_date]'

Jmeter artifact

 

61 conc exports
Profile: Default 50%, custom 50%
 
     
Local instances (33%), shared instances (33%), shared instances with holdings (33%)
we have ins hold item

${tenant}_500_bib_Export.csv

-

select id from ${tenant}_mod_inventory_storage.instance limit 500

Jmeter artifact

 

${tenant}_2k_bib_Export.csv

-

select id from ${tenant}_mod_inventory_storage.instance limit 2000

Jmeter artifact

 

Manage invoices Approve/Invoices Operations

AIE_organization_id.csv

invoice_organization_id

select id from ${tenant}_mod_organizations_storage.organizations limit 2000

DMS data

Yes

 

 

AIE_funds.csv

fundId,fundCode

select ${tenant}_mod_finance_storage.fund.id as fundId, ${tenant}_mod_finance_storage.fund.jsonb->>'code'as fundCode from ${tenant}_mod_finance_storage.fund join ${tenant}_mod_finance_storage.budget   on ${tenant}_mod_finance_storage.fund.id=(${tenant}_mod_finance_storage.budget.jsonb->>'fundId')::uuid join ${tenant}_mod_finance_storage.fiscal_year on ${tenant}_mod_finance_storage.fiscal_year.id=(${tenant}_mod_finance_storage.budget.jsonb->>'fiscalYearId')::uuid where ${tenant}_mod_finance_storage.fund.jsonb->>'fundStatus' = 'Active'  and ${tenant}_mod_finance_storage.budget.jsonb->>'budgetStatus' = 'Active'  and ${tenant}_mod_finance_storage.budget.jsonb->>'allowableEncumbrance' != '0.0'  and ${tenant}_mod_finance_storage.budget.jsonb->>'allowableExpenditure' != '0.0'  and (${tenant}_mod_finance_storage.fiscal_year.jsonb->>'period end)::date > CURRENT_TIMESTAMP  limit 1

DMS data

Yes

(manual for now)

Rollover Ledgers GROUP

FYR_ledger_ids.csv

ledger_id

select ${tenant}_mod_finance_storage.ledger.id from ${tenant}_mod_finance_storage.ledger  join ${tenant}_mod_finance_storage.fiscal_year on ${tenant}_mod_finance_storage.fiscal_year.id=(${tenant}_mod_finance_storage.ledger.jsonb->>'fiscalYearOneId')::uuid  where (${tenant}_mod_finance_storage.fiscal_year.jsonb->>'periodEnd')::date > CURRENT_TIMESTAMP

DMS data

Yes

requires data preparation ??? It picks the ledger with Fiscal year period ends later then today

Item-level Requests Creation

ILR_itembarcodes.csv

itembarcode

select jsonb->'barcode' from ${tenant}_mod_inventory_storage.item   WHERE    jsonb->'status'->>'name' = 'Available' and jsonb->'barcode'!='null'  order by id asc offset 0 limit 300

DMS data

Yes

 

ILR_userBarcodes.csv

userbarcode

select ${tenant}_mod_users.users.jsonb->>'barcode' from ${tenant}_mod_circulation_storage.loan join ${tenant}_mod_users.users on ${tenant}_mod_users.users.id = (${tenant}_mod_circulation_storage.loan.jsonb->>'userId')::uuid join ${tenant}_mod_inventory_storage.item on ${tenant}_mod_inventory_storage.item.id = (${tenant}_mod_circulation_storage.loan.jsonb->>'itemId')::uuid where ${tenant}_mod_inventory_storage.item.jsonb->'status'->>'name' = 'Checked out' and ${tenant}_mod_inventory_storage.item.jsonb->'barcode'!='null' order by (${tenant}_mod_circulation_storage.loan.jsonb->>'itemId')::uuid asc offset 1500 limit 1000

DMS data

Yes

 

Invoices Operations (Create Invoices)

IO_invoice_codes.csv

invoice_name

select ${tenant}_mod_invoice_storage.invoices.jsonb->'vendorInvoiceNo'     from ${tenant}_mod_invoice_storage.invoices limit 2000

DMS data

Yes

 

IO_organization_names.csv

invoice_organization_name

select DISTINCT(REPLACE(text(jsonb->'name'),' ','+'))     FROM ${tenant}_mod_organizations_storage.organizations where jsonb->>'name' not like '%,%' limit 2000

DMS data

Yes

replace spaces with + symbol in organization names. Remove names with ,

OAI-PMH

OAI_PMH_API_KEY.csv

API_KEY

GENERATE APIKEYS    {"s":"nZ56F3LeAa","t":"cs00000int","u":"ecs_admin"}
s  - whatever - should be the same for all tenants
t  - tenant
u  - username

tenant_credentials.csv

No

https://www.base64encode.org/

can be added to tenants credentials

Single Record Import (Create)

SDIC_selectedJobProfileId.csv

selectedJobProfileId

select id from ${tenant}_mod_di_converter_storage.job_profiles where jsonb->>'name' like 'Inventory Single Record - Default Create Instance'

DMS data

Yes

selectedJobProfileId - Data Import Create profile (any profile can be used)

oclc_numbers - same file as for Single Record Import (Update) SDIU_oclc_numbers.csv

profileId - same file as for Single Record Import (Update) SDIU_profile_id.csv

Single Record Import (Update)

SDIU_oclc_numbers.csv

oclc_numbers

any numbers/ the same file can be used for all tenants

Jmeter artifact

 

SDIU_selectedJobProfileId.csv

selectedJobProfileId

select id from ${tenant}_mod_di_converter_storage.job_profiles where jsonb->>'name' like 'Inventory Single Record - Default Update Instance'

DMS data

Yes

Update profile (any profile can be used)

SDIU_instance_Ids.csv

instance_Ids

select id from ${tenant}_mod_inventory_storage.instance limit 3000

DMS data

Yes

 

SDIU_profile_id.csv

profileId

select id from ${tenant}_mod_copycat.profile where jsonb->>'name' like 'OCLC WorldCat'

DMS data

Yes

Z39.50 target profiles

Users Loan Renewal 

ULR_users.csv

users

select id from ${tenant}_mod_users.users where jsonb->'barcode'!='null' and jsonb->'active'='true' and jsonb->'expirationDate' is null order by id asc offset 0 limit 10

DMS data

Yes

'expirationDate' is null +??? data preparation- loans for users.

pickupServicePointId.csv

pickupServicePointId

select id from ${tenant}_mod_inventory_storage.service_point WHERE jsonb->>'pickupLocation' = 'true'  limit 1

DMS data

Yes

 

POO

-

-

-

-

-

No files needed

RTAC

RTAC_instanceIds.csv

id_sets

SELECT id FROM ( SELECT instance.id AS id, COUNT(DISTINCT item.jsonb->'status'->>'name') AS distinct_status_count FROM ${tenant}_mod_inventory_storage.instance AS instance JOIN ${tenant}_mod_inventory_storage.holdings_record AS holding ON instance.id = (holding.jsonb->>'instanceId')::uuid JOIN ${tenant}_mod_inventory_storage.item AS item ON holding.id = (item.jsonb->>'holdingsRecordId')::uuid GROUP BY instance.id ) AS item_instance WHERE item_instance.distinct_status_count > 1 limit 1000;

DMS data

 

File with id separated with comma (Example: 00021f5a-d588-48b5-abb7-db4ff60881a2,00021ff6-dff4-43c0-9e30-6abd972f4b2e,00022064-0802-49ee-8f60-166a496e2337,0002219c-1485-40cb-bef5-03df44592580,000223d7-89c5-465a-960c-1b7a3258e515,00022514-b4a6-4819-b954-5b62aaa63adc,00022640-c226-4036-be0a-c66b9682724f)

Edifact Order Export

???

 

 

Jmeter artifact

No

 

RW Receiving Worfklows

RW_orderId

order_ID

select id from ${tenant}_mod_orders_storage.purchase_order limit 1000

DMS data

Yes

 

EVA_edge-View Account

externalSystemId.csv

externalSystemId

Select jsonb->>'externalSystemId' from ${tenant}_mod_users.users where jsonb->>'externalSystemId' is not null limit 1000