Bookdata-tools/common-schema.sql

CREATE TABLE IF NOT EXISTS import_status (
    step VARCHAR PRIMARY KEY,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    finished_at TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS source_file (
  filename VARCHAR NOT NULL PRIMARY KEY,
  checksum VARCHAR NOT NULL
);

CREATE TABLE IF NOT EXISTS isbn_id (
  isbn_id SERIAL PRIMARY KEY,
  isbn VARCHAR NOT NULL UNIQUE
);

CREATE OR REPLACE FUNCTION bc_of_work(wk INTEGER) RETURNS INTEGER
AS $$ SELECT $1 + 100000000 $$
LANGUAGE SQL
IMMUTABLE STRICT PARALLEL SAFE;
CREATE OR REPLACE FUNCTION bc_of_edition(ed INTEGER) RETURNS INTEGER
AS $$ SELECT $1 + 200000000 $$
LANGUAGE SQL
IMMUTABLE STRICT PARALLEL SAFE;
CREATE OR REPLACE FUNCTION bc_of_loc_rec(rec INTEGER) RETURNS INTEGER
AS $$ SELECT $1 + 300000000 $$
LANGUAGE SQL
IMMUTABLE STRICT PARALLEL SAFE;
CREATE OR REPLACE FUNCTION bc_of_gr_work(rec INTEGER) RETURNS INTEGER
AS $$ SELECT $1 + 400000000 $$
LANGUAGE SQL
IMMUTABLE STRICT PARALLEL SAFE;
CREATE OR REPLACE FUNCTION bc_of_gr_book(rec INTEGER) RETURNS INTEGER
AS $$ SELECT $1 + 500000000 $$
LANGUAGE SQL
IMMUTABLE STRICT PARALLEL SAFE;
CREATE OR REPLACE FUNCTION bc_of_loc_work(rec INTEGER) RETURNS INTEGER
AS $$ SELECT $1 + 600000000 $$
LANGUAGE SQL
IMMUTABLE STRICT PARALLEL SAFE;
CREATE OR REPLACE FUNCTION bc_of_loc_instance(rec INTEGER) RETURNS INTEGER
AS $$ SELECT $1 + 700000000 $$
LANGUAGE SQL
IMMUTABLE STRICT PARALLEL SAFE;
CREATE OR REPLACE FUNCTION bc_of_isbn(id INTEGER) RETURNS INTEGER
AS $$ SELECT $1 + 900000000 $$
LANGUAGE SQL
IMMUTABLE STRICT PARALLEL SAFE;

-- ExtractISBN function
CREATE OR REPLACE FUNCTION extract_isbn(raw_isbn VARCHAR) RETURNS VARCHAR
LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE COST 5
AS $$
SELECT upper(regexp_replace(substring(regexp_replace(raw_isbn, '[^[:alnum:]_ -]', '') from
    '^\s*(?:(?:(?:ISBN)?[a-zA-Z]+?|\(\d+\))\s*)?([0-9 -]+[0-9Xx])'
), '[- ]', ''))
$$;


-- Node IRIs to UUIDs
CREATE OR REPLACE FUNCTION node_uuid(iri VARCHAR) RETURNS UUID
LANGUAGE SQL IMMUTABLE PARALLEL SAFE
AS $$
SELECT uuid_generate_v5(uuid_ns_url(), iri);
$$;