Circ Snapshot for LDP
The first step in the process is to create the circsnapshot4.sql table in the LDP, which is where data updates will be appended and stored. Run the create_circsnapshot4.sql query to create the circsnapshot4 table in the local_core schema on the LDP. This query (run just once) finds all records in the loans_items table where the user_id is not null, and gets the demographics associated with that user via the user_users table and the users_departmnts_unpacked table. This makes the a starting table that is updated by the update_circsnapshot4 query daily automatically.
CREATE TABLE local_core.circ_snapshot4
AS
SELECT
now() AS extract_date,
li.loan_id,
li.item_id,
li.barcode,
li.loan_date,
li.loan_return_date,
li.patron_group_name,
uu.custom_fields__department,
uu.custom_fields__college,
udu.department_code
FROM folio_reporting.loans_items AS li
LEFT JOIN user_users AS uu
ON li.user_id = uu.id
LEFT JOIN folio_reporting.users_departments_unpacked AS udu
ON li.user_id = udu.user_id
WHERE li.user_id IS NOT NULL
;
The daily_update_circ_demographics task runs daily at 7am. This task runs a query called update_circsnapshot4.sql. The update_circ_snapshot4.sql query (in the LOCAL_AUTOMATED folder in DBeaver) runs the following code on the LDP, which uses the INSERT function to get the new checkouts and add them to the local_core.circsnapshot4 table daily automatically. The local_core.circsnapshot4 table is used in circulation queries to generate reports that need the PII data that is removed everyday from circulation data in the FOLIO system.
INSERT INTO local_core.circ_snapshot4
SELECT
now() AS extract_date,
li.loan_id,
li.item_id,
li.barcode,
li.loan_date,
li.loan_return_date,
li.patron_group_name,
uu.custom_fields__department,
uu.custom_fields__college,
udu.department_code
FROM folio_reporting.loans_items AS li
LEFT JOIN user_users AS uu
ON li.user_id = uu.id
LEFT JOIN folio_reporting.users_departments_unpacked AS udu
ON li.user_id = udu.user_id
WHERE
li.loan_id NOT IN
(SELECT cs4.loan_id
FROM local_core.circ_snapshot4 AS cs4
)
AND li.user_id IS NOT NULL
;
Here is a screenshot of the local_core.circsnapshot4 table in the LDP.
Circ Snapshot for Metadb
The circsnapshot for Metadb uses the table that was originally created in LDP to store additional updates to demographics information needed for certain circulation queries.
The daily_update_circ_demographics_metadb task for Metadb runs daily at 7am. This task runs a query called update_circsnapshot4_metadb.sql. The update_circ_snapshot4_metadb.sql query (in the LOCAL_AUTOMATED folder in DBeaver) runs the following code on Metadb, which uses the INSERT function to get the new checkouts and add them to the sm_local_core.circsnapshot4 table daily automatically. The sm_local_core.circsnapshot4 table is used in circulation queries to generate reports that need the PII data that is removed everyday from circulation data in the FOLIO system.
INSERT INTO local_core.sm_circ_snapshot4
SELECT
NOW() AS extract_date,
li.loan_id,
li.item_id,
li.barcode,
li.loan_date,
li.loan_return_date,
li.patron_group_name,
uu.custom_fields__department,
uu.custom_fields__college,
udu.department_code
FROM folio_derived.loans_items AS li
LEFT JOIN folio_users.users__t AS uu
ON li.user_id = uu.id
LEFT JOIN folio_derived.users_departments_unpacked AS udu
ON li.user_id = udu.user_id
WHERE
li.loan_id NOT IN
(SELECT cs4.loan_id
FROM local_core.sm_circ_snapshot4 AS cs4
)
AND li.user_id IS NOT NULL
;
Here is a screenshot of the local_core.circsnapshot4 table in the LDP.