sierra sql Tips and Tricks - plch/sierra-sql GitHub Wiki

PLCH_Logo_Horizontal_Red.png

General Tips for the sierra-db

To get record numbers

(for use in the Sierra Desktop Application)

import record numbers feature for example, we can join the table sierra_view.record_metadata, appending the columns (and extra checkdigit character, ‘a’) sierra_view.record_type_code || sierra_view.record_number || ‘a’

Example:

SELECT
r.record_type_code || r.record_num || 'a' as record_number

FROM
sierra_view.record_metadata as r

WHERE
r.record_type_code || r.campus_code = 'p'
and r.deletion_date_gmt is null

limit 5

... would produce the following output for example:

p1234567a
p1234568a
p1234569a
p1234570a
p1234571a

Generate check digit

If the 'a' character isn't doing it for you, you can use the following code to generate the check digit as outlined in the Sierra documentation: https://techdocs.iii.com/sierrahelp/Default.htm#sril/sril_records_numbers.html?Highlight=check%20digit

CREATE OR REPLACE FUNCTION pg_temp.rec2check(rec_num INTEGER) RETURNS CHAR as $$
DECLARE
	a TEXT[];
	counter INTEGER;
	agg_sum INTEGER;
BEGIN
	SELECT regexp_split_to_array($1::VARCHAR, '') INTO a;
	-- counter is what we're multiplying by
	counter := 2;
	agg_sum := 0;
	FOR i IN REVERSE array_length(a, 1)..1 LOOP
		agg_sum := agg_sum + (a[i]::INTEGER * counter);
		counter := counter + 1;
	END LOOP;

	IF (agg_sum % 11) = 10 THEN
		RETURN 'x';
	ELSE
		RETURN agg_sum % 11;
	END IF;

END;
$$ LANGUAGE plpgsql;


-- CHECK EXAMPLE #1
-- https://techdocs.iii.com/sierrahelp/Default.htm#sril/sril_records_numbers.html?Highlight=check%20digit
-- the check digit from the example record number of "1024364" is "1"
-- SELECT 
-- pg_temp.rec2check(1024364)
-- ;


-- CHECK EXAMPLE #2
SELECT
r.record_type_code || r.record_num || pg_temp.rec2check(r.record_num) as record_num
FROM
sierra_view.record_metadata as r
WHERE
r.record_type_code || r.campus_code = 'b'
LIMIT 100;

Picking the correct primary / foreign keys for joins can be tricky.

* Make sure you carefully examine the sierra DNA documentation 
* ```id``` generally is the primary key, as it is in ```sierra_view.record_metadata```. This means that it'll appear in other tables. For example, it might appear as

bib_record_id in table sierra_view.bib_record_item_record_link

or

record_id in table sierra_view.bib_record

  • Joining varfields in results can be tricky, since these can be repeatable fields.
    • Typically you want to avoid multiple rows of results being produced because of a unexpected (or expected) repeating varfields.
    • An example of this could be notes fields, added authors, etc, which could appear multiple times. The following query should return only one row for the one patron, but instead we get two, because the patron has two barcodes assigned to them.
SELECT
p.record_id,
v.*

FROM
sierra_view.patron_record as p

-- get the barcode
JOIN
sierra_view.varfield as v
ON
  v.record_id = p.record_id
  AND v.varfield_type_code = 'b'

WHERE
p.record_id = 481037410624

To avoid this, we could either select only one value for the barcode, or select all matching values and aggregate them into one value (so that we only get one row of results back as expected) with the string_agg() function that postgresql offers as a subquery in the select statement:

SELECT
p.record_id,
(
	SELECT
	string_agg(v.field_content, ',' order by v.occ_num)

	FROM
	sierra_view.varfield as v

	WHERE
	v.record_id = p.record_id
	AND v.varfield_type_code = 'b'
)

FROM
sierra_view.patron_record as p

WHERE
p.record_id = 481037410624

This might produce data similar to this 53933149,49548969 / PREV ID

  • Some of the views combine multiple tables, and can be convenient, but can be much slower than other methods of retrieving data. sierra_view.*_view Example: sierra_view.bib_view combines data from multiple tables, but query speeds can be slower than accessing other tables more directly.
SELECT
*
FROM
sierra_view.bib_view

LIMIT 1000

(1.6 seconds)

VS

SELECT
*
FROM
sierra_view.bib_record as b

JOIN
sierra_view.bib_record_property as p
ON
  p.bib_record_id = b.record_id

LIMIT 1000

(543 milliseconds)

⚠️ **GitHub.com Fallback** ⚠️