TABLE OF CONTENTS


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.


---START QUERY


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
;

---END QUERY

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.


Here is the update_circ_snapshot4.sql query for LDP:

---START QUERY

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
 ;

---END QUERY

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. This table was exported from the LDP and imported into the local_shared schema in Metadb. In the data import process, column data types were set to match the data types in the equivalent LDP table. 


The daily_update_circ_demographics_metadb task is set up to execute the update_circsnapshot4.sql query daily at 7am. 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_shared.circsnapshot4 table daily automatically. The sm_local_shared.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.

The metadb version of the query was revised to NOT use derived tables. This will ensure the retrieval of all changes to records when the update query appends new data to the local_shared sm_circ_snapshot4 table.


Here is the update_circ_snapshot4_metadb.sql query:


---START QUERY


-- MCR404
-- update_circsnapshot4_metadb.sql
-- Last updated: 7/1/24

-- 6-27-24: This query creates the "insert into" portion of the circ_snapshot4 query

INSERT INTO local_shared.sm_circ_snapshot4

-- 1. In order to get to the department code in the departments__t table, we need to extract the department ID from the jsonb array in the users__ table

WITH depts AS 
(SELECT
    users__.id AS user_id,
    departments.jsonb #>> '{}' AS department_id
    FROM 
        folio_users.users__
        CROSS JOIN jsonb_array_elements((users__.jsonb #> '{departments}')::jsonb) as departments
    WHERE users__.__current = 'true'        
)

-- 2. Use the results of the depts subquery above to link to the departments__t table and users__t table
    
SELECT
   NOW() AS extract_date,
   loan__t.id as loan_id,
   loan__t.item_id,
   item__t.barcode,
   loan__t.loan_date::timestamptz,
   loan__t.return_date::timestamptz as loan_return_date,
   groups__t.group AS patron_group_name,
   jsonb_extract_path_text (folio_users.users__.jsonb,'customFields','department') AS custom_fields__department,
   jsonb_extract_path_text (folio_users.users__.jsonb,'customFields','college') AS custom_fields__college,
   departments__t.code AS department_code

FROM 
    folio_users.users__
    LEFT JOIN folio_users.users__t 
       ON users__.id = users__t.id
       
       LEFT JOIN depts 
       ON users__.id = depts.user_id
       
       LEFT JOIN folio_users.departments__t 
    ON depts.department_id::UUID = departments__t.id

    LEFT JOIN folio_circulation.loan__t
    ON loan__t.user_id = users__t.id
    
    LEFT JOIN folio_users.groups__t 
    ON users__t.patron_group = groups__t.id

    LEFT JOIN folio_inventory.item__t 
    ON loan__t.item_id = item__t.id
   
WHERE
   loan__t.id NOT IN 
       (SELECT cs4.loan_id::UUID
       FROM local_shared.sm_circ_snapshot4 as cs4
       )
   AND loan__t.user_id is not null 
   AND users__.__current = true
   ;
   

---END QUERY


Here is a screenshot of the local_shared.sm_circs_snapshot4 table in Metadb. 


Permissions

The permissions for the local_shared.sm_circ_snapshot4 table are set to RW for z_slm5 and RO for all other users.

For instructions on setting permissions, see Setting Permissions for Schema Tables in Metadb. 

Automation

The update_circ_snapshot4_metadb.sql query is automated to run daily at the following times:

  • 7am
  • 11am
  • 2pm
  • 5pm
  • 9pm













  • No labels