BiblioteQ

Extending BiblioteQ: Custom Reports and Integration Tips

Overview

• BiblioteQ stores content in PostgreSQL or SQLite and exposes flexible querying and export features suitable for custom reports and integrations.

Custom Reports — approach

1. Decide data source
   • Use PostgreSQL for multi-user production; SQLite for single-user/portable setups.
2. Design report SQL
   • Create SELECT queries joining tables (items, members, loans, reservations, images).
   • Avoid QByteArray/blob columns in reports (large binary fields).
3. Use BiblioteQ’s Custom Query
   • Paste SQL into Tools → Custom Query; results map to the main table for export or printing.
4. Export and format
   • Export query results to CSV from the main view; open in Excel/LibreOffice for formatting or use scripting to convert to PDF.
5. Automated reports
   • For scheduled reports, query the underlying DB directly (psql/sqlite3) and generate CSV/PDF with scripts (Python + pandas/reportlab or Node.js + csv-writer + PDF library).

Integration Tips

• Use the database as the integration interface
  • Read/write directly to PostgreSQL (recommended) using proper database accounts and transactions. Run biblioteq-provided schema scripts when creating/updating DBs.
• Respect schema and constraints
  • Modify data via BiblioteQ or carefully with transactions to avoid breaking referential integrity (use provided SQL DDL/upgrade scripts for schema changes).
• APIs and external tools
  • BiblioteQ has no public HTTP API; build a small middleware service that queries the DB and exposes needed endpoints (use parameterized queries, connection pooling).
• Import/Export formats
  • Use CSV import for bulk item creation; BiblioteQ supports MARC21/UNIMARC for MARC workflows and Open Library/Amazon for cover lookups.
• Search protocols
  • Leverage SRU and Z39.50 support for catalog enrichment; configure sites in biblioteq.conf.
• Attachments and covers
  • Store/manage binary files via the DB; when integrating, extract blobs to filesystem or serve via middleware to avoid heavy DB traffic.

Security & Operations

• Accounts & privileges
  • Create limited DB users for integrations with only required privileges; use PostgreSQL roles for row-level security if needed.
• Backups & migrations
  • Regularly back up DB (pg_dump for PostgreSQL; file copies for SQLite). Run BiblioteQ’s upgrade SQL scripts when updating versions.
• Performance
  • Add indexes on frequently queried columns; use PostgreSQL for larger catalogs and concurrent users.
• Logging & error handling
  • Enable DB and application logs; middleware should retry transient errors and validate inputs.

Example: Automated overdue report (concept)

• SQL: select member_id, name, item_id, title, due_date from loans join items on … where due_date < current_date and returned = false;
• Run nightly via cron, output CSV, convert to PDF, email to librarians.

Resources

• BiblioteQ source & docs: GitHub repository (textbrowser/biblioteq) — consult README, SQL/Tools folders, and postgresql_create_schema.sql for schema details.