Book Database

Book Database is for managing a library of books, documenting book reviews, and tracking review analytics.

I have a book review plugin already, but Book Database is a fun recoding project for me. It's basically, "what would my other plugin look like if I could start over with zero backwards compatibility?" -- Basically a rewrite considering all the things I now hate about my other plugin and what I wish I could have done differently.

Things I'm focusing on:

Fewer third party libraries. These started to really piss me off in my other plugin.
Better job scaling, when people want to add thousands of books.
Better UI and UX.
And finally, I'm focusing more on what I want from this plugin, rather than what other people say they want.

Features:

Add books to the database with book information (title, author, cover, synopsis, etc.).
Create reviews and associate those reviews with books.
Display book information in blog posts via a shortcode.
Maintain a front-end archive of all your book reviews.
See analytics about the books you've reviewed during a specific date range.

Requirements:

PHP 7.0+
MySQL 5.6.5+
64-bit

Fun Queries:

Most read books:

SELECT
  book_id,
  book.title,
  COUNT(*) AS times_read
FROM `wp_bdb_reading_log` log INNER JOIN `wp_bdb_books` book ON book.id = log.book_id
GROUP BY book_id
HAVING times_read > 1
ORDER BY times_read DESC
LIMIT 25

Books you own two or more copies of:

SELECT
  book_id,
  book.title,
  COUNT(*) AS number_copies
FROM `wp_bdb_owned_editions` e INNER JOIN `wp_bdb_books` book ON book.id = e.book_id
GROUP BY book_id
HAVING number_copies > 1
ORDER BY number_copies DESC

Get all the books and their ratings from a specific term name ("Fantasy") that were read in 2017.

SELECT
  book.title,
  log.rating,
  log.date_finished
FROM `wp_bdb_books` book RIGHT JOIN `wp_bdb_reading_log` log ON log.book_id = book.id
  INNER JOIN `wp_bdb_book_term_relationships` r ON r.book_id = book.id
  INNER JOIN `wp_bdb_book_terms` term ON (term.id = r.term_id AND term.name = 'Fantasy')
WHERE 2017 = YEAR (date_finished)
ORDER BY log.date_finished ASC

Same as above, but checks reviews only, where the review was written in a specific year (2017).

SELECT
  book.title,
  log.rating
FROM `wp_bdb_reviews` review RIGHT JOIN `wp_bdb_reading_log` log ON log.review_id = review.id
  INNER JOIN `wp_bdb_books` book ON book.id = review.book_id
  INNER JOIN `wp_bdb_book_term_relationships` r ON r.book_id = review.book_id
  INNER JOIN `wp_bdb_book_terms` term ON (term.id = r.term_id AND term.name = 'Fantasy')
WHERE 2017 = YEAR (date_written)
ORDER BY book.title ASC

Get books with 4 stars or higher in the genres "Contemporary" and "Romance":

SELECT
  book.title,
  author.name,
  log.rating
FROM wp_bdb_books AS book
  INNER JOIN wp_bdb_book_author_relationships AS r ON book.id = r.book_id
  INNER JOIN wp_bdb_authors AS author ON r.author_id = author.id
  INNER JOIN wp_bdb_reading_log AS log ON book.id = log.book_id
WHERE log.rating > 4
      AND book.id IN (
  SELECT book_id
  FROM wp_bdb_book_term_relationships AS r
    INNER JOIN wp_bdb_book_terms AS t ON r.term_id = t.id
  WHERE t.name = 'Contemporary'
        AND book_id IN (
    SELECT book_id
    FROM wp_bdb_book_term_relationships AS r2
      INNER JOIN wp_bdb_book_terms AS t2 ON r2.term_id = t2.id
    WHERE t2.name = 'Romance'
  )
)
GROUP BY book.id
ORDER BY log.rating DESC

Get a count of how many books were read in each format in a given year (2017).

SELECT
  format,
  COUNT(*) AS number_books_read
FROM `wp_bdb_owned_editions` AS b
  INNER JOIN wp_bdb_reading_log AS l ON l.book_id = b.book_id
WHERE 2017 = YEAR (date_finished)
GROUP BY format
ORDER BY format ASC;

Get a count of how many books read in 2017 were published in each year.

SELECT
  YEAR(pub_date) AS pub_year,
  COUNT(*) AS number_books_published
FROM wp_bdb_books AS b
  INNER JOIN wp_bdb_reading_log AS l ON l.book_id = b.id
WHERE 2017 = YEAR (date_finished)
GROUP BY YEAR(pub_date)
ORDER BY pub_year DESC;

Get your top 5 most read genres (order by ASC to get least read).

SELECT COUNT(log.id) AS count,t.name
FROM wp_bdb_reading_log AS log
INNER JOIN wp_bdb_book_term_relationships AS tr ON( log.book_id = tr.book_id )
INNER JOIN wp_bdb_book_terms AS t ON( tr.term_id = t.id )
WHERE t.taxonomy = 'genre'
GROUP BY t.name
ORDER BY count DESC
LIMIT 5;

Average number of days it takes you to finish a book. To exclude DNF books, add another condition for AND percentage_complete >= 1.

SELECT ROUND( AVG( DATEDIFF( date_finished, date_started ) * percentage_complete ) ) + 1 AS number_days
FROM wp_bdb_reading_log
WHERE date_started IS NOT NULL
AND date_finished IS NOT NULL

Average number of days between the date you acquire a book and the date you first start reading it.

SELECT ROUND( AVG( DATEDIFF( date_started, date_acquired ) ) ) + 1 AS number_days_to_start
FROM wp_bdb_owned_editions AS edition
INNER JOIN wp_bdb_reading_log AS log ON log.id = (
	SELECT id
	FROM wp_bdb_reading_log AS log2
	WHERE edition_id = edition.id
	AND date_started IS NOT NULL
	ORDER BY date_started
	LIMIT 1
)
WHERE date_acquired IS NOT NULL

ashleyfae/book-database

Book Database

Features:

Requirements:

Fun Queries: