### Set up Open Library PostgreSQL Database

Source: https://context7.com/librarieshacked/openlibrary-search/llms.txt

The `openlibrary-db.sql` script orchestrates the PostgreSQL database setup. It creates the database, enables the `pg_trgm` extension, defines tables, loads CSV data, derives relationship tables from JSONB, builds indexes, and performs a final vacuum analyze. This process can take several hours.

```bash
# Run the full setup (replace 'username' with your PostgreSQL username)
psql --set=sslmode=require -f openlibrary-db.sql -h localhost -p 5432 -U username postgres

# The script executes these steps in order:
# 1. CREATE DATABASE openlibrary
# 2. \c openlibrary  (switch connection)
# 3. CREATE EXTENSION pg_trgm
# 4. CREATE TABLE authors, works, author_works, editions, edition_isbns, fileinfo
# 5. COPY data from CSV chunks (via openlibrary-data-loader.sql)
# 6. Derive author_works from works.data->'authors' JSONB
# 7. Derive edition_isbns from editions.data->'isbn_13'/'isbn_10'/'isbn' JSONB
# 8. CREATE indexes on all tables
# 9. DROP TABLE fileinfo
# 10. VACUUM ANALYZE
```

--------------------------------

### Import Data into PostgreSQL Database

Source: https://github.com/librarieshacked/openlibrary-search/blob/main/README.md

Use the psql command-line tool to run the database creation and data import scripts. Ensure the data files are in the 'data/processed' folder. Replace 'username' with your actual database username.

```console
psql --set=sslmode=require -f openlibrary-db.sql -h localhost -p 5432 -U username postgres
```

--------------------------------

### Download and Decompress Open Library Data Dumps

Source: https://context7.com/librarieshacked/openlibrary-search/llms.txt

Use wget to download the latest editions, works, and authors data dumps. Move them to the data/unprocessed directory and decompress using gzip. Ensure sufficient free disk space, as the editions dump can be very large.

```bash
# Download the latest data dumps
wget https://openlibrary.org/data/ol_dump_editions_latest.txt.gz -P ~/downloads
wget https://openlibrary.org/data/ol_dump_works_latest.txt.gz -P ~/downloads
wget https://openlibrary.org/data/ol_dump_authors_latest.txt.gz -P ~/downloads

# Move into the project's unprocessed data directory
mv ~/downloads/ol_dump_authors_*txt.gz  ./data/unprocessed/ol_dump_authors.txt.gz
mv ~/downloads/ol_dump_works_*txt.gz    ./data/unprocessed/ol_dump_works.txt.gz
mv ~/downloads/ol_dump_editions_*txt.gz ./data/unprocessed/ol_dump_editions.txt.gz

# Decompress (editions decompresses to ~45 GB; ensure 250 GB+ free space)
gzip -d -c data/unprocessed/ol_dump_editions.txt.gz > data/unprocessed/ol_dump_editions.txt
gzip -d -c data/unprocessed/ol_dump_works.txt.gz    > data/unprocessed/ol_dump_works.txt
gzip -d -c data/unprocessed/ol_dump_authors.txt.gz  > data/unprocessed/ol_dump_authors.txt
```

--------------------------------

### Uncompress Data Dumps

Source: https://github.com/librarieshacked/openlibrary-search/blob/main/README.md

Uncompress the downloaded .gz data files using gzip -d -c, redirecting the output to create .txt files in the ./data/unprocessed directory.

```bash
gzip -d -c data/unprocessed/ol_dump_editions.txt.gz > data/unprocessed/ol_dump_editions.txt
gzip -d -c data/unprocessed/ol_dump_works.txt.gz > data/unprocessed/ol_dump_works.txt
gzip -d -c data/unprocessed/ol_dump_authors.txt.gz > data/unprocessed/ol_dump_authors.txt
```

--------------------------------

### Download Open Library Data Dumps

Source: https://github.com/librarieshacked/openlibrary-search/blob/main/README.md

Use wget to download the latest compressed data dumps for editions, works, and authors from Open Library. Specify the download directory with -P.

```bash
wget https://openlibrary.org/data/ol_dump_editions_latest.txt.gz -P ~/downloads
wget https://openlibrary.org/data/ol_dump_works_latest.txt.gz -P ~/downloads
wget https://openlibrary.org/data/ol_dump_authors_latest.txt.gz -P ~/downloads
```

--------------------------------

### Process and Chunk Open Library Data with Python

Source: https://context7.com/librarieshacked/openlibrary-search/llms.txt

The `openlibrary_data_process.py` script processes raw dump files, filters malformed rows, and splits data into 2-million-line CSV chunks. It uses Python's multiprocessing for parallel processing and outputs chunk files to `data/processed/` along with a `filenames.txt` manifest. Adjust `LINES_PER_FILE` to control chunk size.

```python
# Key configuration at the top of openlibrary_data_process.py
LINES_PER_FILE = 2000000          # Lines per output chunk; ~3.24 GB per chunk for editions
INPUT_PATH     = "./data/unprocessed/"
OUTPUT_PATH    = "./data/processed/"
FILE_IDENTIFIERS = ["authors", "works", "editions"]

# Run the script (processes all three files in parallel; ~15 min on Apple M3)
# python openlibrary_data_process.py

# Expected output in data/processed/filenames.txt:
# authors    0    False    {authors_2000000.csv,authors_4000000.csv}
# works      1    False    {works_2000000.csv,works_4000000.csv,works_6000000.csv}
# editions   2    False    {editions_2000000.csv,editions_4000000.csv,...}

# To create a small test subset before running:
# sed -i '' '100000,$ d' ./data/unprocessed/ol_dump_editions.txt
```

--------------------------------

### Process Open Library Data with Python Script

Source: https://github.com/librarieshacked/openlibrary-search/blob/main/README.md

Execute the openlibrary_data_process.py script to clean and split the large text data files into smaller, importable chunks. This script handles files with varying column counts and can be configured to control the size of the output chunks.

```bash
python openlibrary_data_process.py
```

--------------------------------

### Move Downloaded Data Dumps

Source: https://github.com/librarieshacked/openlibrary-search/blob/main/README.md

Move the downloaded compressed data files from the downloads directory to the ./data/unprocessed directory, renaming them for consistency.

```bash
mv ~/downloads/ol_dump_authors_*txt.gz ./data/unprocessed/ol_dump_authors.txt.gz
mv ~/downloads/ol_dump_works_*txt.gz ./data/unprocessed/ol_dump_works.txt.gz
mv ~/downloads/ol_dump_editions_*txt.gz ./data/unprocessed/ol_dump_editions.txt.gz
```

--------------------------------

### Create Edition ISBNs Join Table Schema

Source: https://context7.com/librarieshacked/openlibrary-search/llms.txt

Defines the 'edition_isbns' join table for storing flattened ISBNs from edition records.

```sql
CREATE TABLE edition_isbns (
  edition_key text NOT NULL,
  isbn        text NOT NULL,
  CONSTRAINT pk_editionisbns_editionkey_isbn PRIMARY KEY (edition_key, isbn)
);
```

--------------------------------

### Database Indexing for OpenLibrary Tables

Source: https://context7.com/librarieshacked/openlibrary-search/llms.txt

Defines unique indexes, clustered indexes, GIN indexes with pg_trgm for text search, and jsonb_path_ops for array containment queries on various OpenLibrary tables like authors, works, editions, and join tables. These indexes are crucial for efficient data retrieval and search operations.

```sql
-- Authors
CREATE UNIQUE INDEX cuix_authors_key ON authors (key);
ALTER TABLE authors CLUSTER ON cuix_authors_authors_key;
CREATE INDEX ix_authors_name ON authors USING GIN ((data->>'name') gin_trgm_ops);

-- Works
CREATE UNIQUE INDEX cuix_works_key ON works (key);
ALTER TABLE works CLUSTER ON cuix_works_key;
CREATE INDEX ix_works_data_authors ON works USING GIN ((data->'authors') jsonb_path_ops);
CREATE INDEX ix_works_title       ON works USING GIN ((data->>'title')       gin_trgm_ops);
CREATE INDEX ix_works_subtitle    ON works USING GIN ((data->>'subtitle')    gin_trgm_ops);
CREATE INDEX ix_works_description ON works USING GIN ((data->>'description') gin_trgm_ops);

-- Editions
CREATE UNIQUE INDEX cuix_editions_key ON editions (key);
ALTER TABLE editions CLUSTER ON cuix_editions_key;
CREATE INDEX ix_editions_workkey  ON editions (work_key);
CREATE INDEX ix_editions_isbn     ON editions USING GIN ((data->'isbn')    jsonb_path_ops);
CREATE INDEX ix_editions_isbn13   ON editions USING GIN ((data->'isbn_13') jsonb_path_ops);
CREATE INDEX ix_editions_isbn10   ON editions USING GIN ((data->'isbn_10') jsonb_path_ops);
CREATE INDEX ix_editions_title    ON editions USING GIN ((data->>'title')    gin_trgm_ops);
CREATE INDEX ix_editions_subtitle ON editions USING GIN ((data->>'subtitle') gin_trgm_ops);

-- Join tables
CREATE UNIQUE INDEX cuix_authorworks_authorkey_workkey ON author_works (author_key, work_key);
CREATE INDEX ix_authorworks_workkey   ON author_works (work_key);
CREATE INDEX ix_authorworks_authorkey ON author_works (author_key);

CREATE UNIQUE INDEX cuix_editionisbns_editionkey_isbn ON edition_isbns (edition_key, isbn);
CREATE INDEX ix_editionisbns_isbn       ON edition_isbns (isbn);
CREATE INDEX ix_editionisbns_editionkey ON edition_isbns (edition_key);
```

--------------------------------

### Create Editions Table Schema

Source: https://context7.com/librarieshacked/openlibrary-search/llms.txt

Defines the 'editions' table structure for storing Open Library edition records, including JSONB fields and a derived 'work_key'.

```sql
CREATE TABLE editions (
  type          text,
  key           text NOT NULL,   -- e.g. '/books/OL7353617M'
  revision      integer,
  last_modified date,
  data          jsonb,           -- {"title": ..., "isbn_13": [...], "isbn_10": [...], ...}
  work_key      text,            -- derived: data->'works'->0->>'key'
  CONSTRAINT pk_editions_key PRIMARY KEY (key)
);
```

--------------------------------

### Derive Edition ISBNs

Source: https://context7.com/librarieshacked/openlibrary-search/llms.txt

Inserts data into the 'edition_isbns' table by extracting ISBNs from the 'isbn_13', 'isbn_10', and 'isbn' JSONB arrays in the 'editions' table.

```sql
INSERT INTO edition_isbns (edition_key, isbn) 
SELECT DISTINCT edition_key, isbn FROM (
  SELECT key AS edition_key, jsonb_array_elements_text(data->'isbn_13') AS isbn
  FROM editions WHERE jsonb_array_length(data->'isbn_13') > 0 AND key IS NOT NULL
  UNION ALL
  SELECT key, jsonb_array_elements_text(data->'isbn_10')
  FROM editions WHERE jsonb_array_length(data->'isbn_10') > 0 AND key IS NOT NULL
  UNION ALL
  SELECT key, jsonb_array_elements_text(data->'isbn')
  FROM editions WHERE jsonb_array_length(data->'isbn') > 0 AND key IS NOT NULL
) isbns;
```

--------------------------------

### SQL Query to Find Editions for a Work

Source: https://context7.com/librarieshacked/openlibrary-search/llms.txt

Retrieves all editions associated with a specific work key, including their titles and ISBNs. This query is useful for listing different editions of the same book.

```sql
-- Find all editions for a known work key
SELECT e.key, e.data->>'title' AS edition_title, ei.isbn
FROM editions e
JOIN edition_isbns ei ON ei.edition_key = e.key
WHERE e.work_key = '/works/OL82563W'
ORDER BY e.last_modified DESC;
```

--------------------------------

### SQL Query to Find Works by Author

Source: https://context7.com/librarieshacked/openlibrary-search/llms.txt

Lists all works associated with a specific author key, including their titles. This query is useful for finding all books written by a particular author.

```sql
-- Find all works by a known author key
SELECT w.key, w.data->>'title' AS title
FROM works w
JOIN author_works aw ON aw.work_key = w.key
WHERE aw.author_key = '/authors/OL23919A';
```

--------------------------------

### Query Edition Details by ISBN13

Source: https://github.com/librarieshacked/openlibrary-search/blob/main/README.md

Retrieve detailed information about a specific edition, its work, and author using its ISBN13. This SQL query joins multiple tables to gather comprehensive data.

```sql
select
    e.data->>'title' "EditionTitle",
    w.data->>'title' "WorkTitle",
	a.data->>'name' "Name",
    e.data->>'subtitle' "EditionSubtitle",
    w.data->>'subtitle' "WorkSubtitle",
    e.data->>'subjects' "Subjects",
    e.data->'description'->>'value' "EditionDescription",
    w.data->'description'->>'value' "WorkDescription",
    e.data->'notes'->>'value' "EditionNotes",
    w.data->'notes'->>'value' "WorkNotes"
from editions e
join edition_isbns ei
    on ei.edition_key = e.key
join works w
    on w.key = e.work_key
join author_works a_w
	on a_w.work_key = w.key
join authors a
	on a_w.author_key = a.key
where ei.isbn = '9781551922461'
```

--------------------------------

### SQL Query for Full Bibliographic Details by ISBN-13

Source: https://context7.com/librarieshacked/openlibrary-search/llms.txt

Retrieves comprehensive book information by joining multiple tables using an ISBN-13. This query is useful for looking up detailed bibliographic data for a specific edition of a book.

```sql
-- Full bibliographic lookup by ISBN-13: 9781551922461 (Harry Potter and the Prisoner of Azkaban)
SELECT
    e.data->>'title'                   AS "EditionTitle",
    w.data->>'title'                   AS "WorkTitle",
    a.data->>'name'                    AS "AuthorName",
    e.data->>'subtitle'                AS "EditionSubtitle",
    w.data->>'subtitle'                AS "WorkSubtitle",
    e.data->>'subjects'                AS "Subjects",
    e.data->'description'->>'value'    AS "EditionDescription",
    w.data->'description'->>'value'    AS "WorkDescription",
    e.data->'notes'->>'value'          AS "EditionNotes",
    w.data->'notes'->>'value'          AS "WorkNotes"
FROM editions e
JOIN edition_isbns ei
    ON ei.edition_key = e.key
JOIN works w
    ON w.key = e.work_key
JOIN author_works a_w
    ON a_w.work_key = w.key
JOIN authors a
    ON a_w.author_key = a.key
WHERE ei.isbn = '9781551922461';
```

--------------------------------

### Create Authors Table Schema

Source: https://context7.com/librarieshacked/openlibrary-search/llms.txt

Defines the 'authors' table structure for storing Open Library author records, including a JSONB field for the full record.

```sql
CREATE TABLE authors (
  type          text,
  key           text NOT NULL,   -- e.g. '/authors/OL23919A'
  revision      integer,
  last_modified date,
  data          jsonb,           -- full author record, e.g. {"name": "J.K. Rowling", ...}
  CONSTRAINT pk_author_key PRIMARY KEY (key)
);
```

--------------------------------

### Set Table to UNLOGGED for Bulk Insert

Source: https://context7.com/librarieshacked/openlibrary-search/llms.txt

Temporarily sets a table to UNLOGGED to speed up bulk insert operations. Remember to set it back to LOGGED afterward.

```sql
ALTER TABLE authors SET UNLOGGED;    -- speeds up bulk insert
```

--------------------------------

### Create Works Table Schema

Source: https://context7.com/librarieshacked/openlibrary-search/llms.txt

Defines the 'works' table structure for storing Open Library work records, including a JSONB field for the full record.

```sql
CREATE TABLE works (
  type          text,
  key           text NOT NULL,   -- e.g. '/works/OL82563W'
  revision      integer,
  last_modified date,
  data          jsonb            -- {"title": "Harry Potter...", "authors": [...], ...}
  CONSTRAINT pk_works_key PRIMARY KEY (key)
);
```

--------------------------------

### Set Table to LOGGED After Bulk Insert

Source: https://context7.com/librarieshacked/openlibrary-search/llms.txt

Restores a table to its LOGGED state after bulk loading operations are complete.

```sql
ALTER TABLE authors SET LOGGED;
```

--------------------------------

### SQL Query for Fuzzy Title Search

Source: https://context7.com/librarieshacked/openlibrary-search/llms.txt

Performs a case-insensitive fuzzy search for book titles using the ILIKE operator and trigram similarity. This query is useful for finding books when the exact title is unknown.

```sql
-- Trigram fuzzy title search across works
SELECT key, data->>'title' AS title
FROM works
WHERE data->>'title' ILIKE '%prisoner of azkaban%'
LIMIT 10;
```

--------------------------------

### Derive Author-Work Relationships

Source: https://context7.com/librarieshacked/openlibrary-search/llms.txt

Inserts data into the 'author_works' table by extracting author and work keys from the 'data' JSONB field in the 'works' table.

```sql
INSERT INTO author_works (author_key, work_key)
SELECT DISTINCT
  jsonb_array_elements(data->'authors')->'author'->>'key' AS author_key,
  key AS work_key
FROM works
WHERE key IS NOT NULL
  AND data->'authors'->0->'author' IS NOT NULL;
```

--------------------------------

### Create Author-Works Join Table Schema

Source: https://context7.com/librarieshacked/openlibrary-search/llms.txt

Defines the 'author_works' join table for managing many-to-many relationships between authors and works.

```sql
CREATE TABLE author_works (
  author_key text NOT NULL,
  work_key   text NOT NULL,
  CONSTRAINT pk_authorworks_authorkey_workkey PRIMARY KEY (author_key, work_key)
);
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.