diff --git a/booklore-api/src/main/java/org/booklore/model/entity/BookEntity.java b/booklore-api/src/main/java/org/booklore/model/entity/BookEntity.java
index 2ae538c5..758bce9c 100644
--- a/booklore-api/src/main/java/org/booklore/model/entity/BookEntity.java
+++ b/booklore-api/src/main/java/org/booklore/model/entity/BookEntity.java
@@ -53,6 +53,9 @@ public class BookEntity {
@Column(name = "added_on")
private Instant addedOn;
+ @Column(name = "scanned_on")
+ private Instant scannedOn;
+
@Column(name = "book_cover_hash", length = 20)
private String bookCoverHash;
diff --git a/booklore-api/src/main/java/org/booklore/model/entity/BookFileEntity.java b/booklore-api/src/main/java/org/booklore/model/entity/BookFileEntity.java
index 07a55a99..1d1bfcbc 100644
--- a/booklore-api/src/main/java/org/booklore/model/entity/BookFileEntity.java
+++ b/booklore-api/src/main/java/org/booklore/model/entity/BookFileEntity.java
@@ -128,7 +128,7 @@ public class BookFileEntity {
.filter(java.nio.file.Files::isRegularFile)
.filter(p -> {
String name = p.getFileName().toString().toLowerCase();
- return name.endsWith(".mp3") || name.endsWith(".m4a") || name.endsWith(".m4b");
+ return name.endsWith(".mp3") || name.endsWith(".m4a") || name.endsWith(".m4b") || name.endsWith(".opus");
})
.sorted()
.findFirst()
diff --git a/booklore-api/src/main/java/org/booklore/model/entity/UserBookFileProgressEntity.java b/booklore-api/src/main/java/org/booklore/model/entity/UserBookFileProgressEntity.java
index 0e2d376c..c07561b4 100644
--- a/booklore-api/src/main/java/org/booklore/model/entity/UserBookFileProgressEntity.java
+++ b/booklore-api/src/main/java/org/booklore/model/entity/UserBookFileProgressEntity.java
@@ -36,6 +36,9 @@ public class UserBookFileProgressEntity {
@Column(name = "progress_percent")
private Float progressPercent;
+ @Column(name = "tts_position_cfi", length = 1000)
+ private String ttsPositionCfi;
+
@Column(name = "last_read_time")
private Instant lastReadTime;
}
diff --git a/booklore-api/src/main/java/org/booklore/repository/AuthorRepository.java b/booklore-api/src/main/java/org/booklore/repository/AuthorRepository.java
index 6ff2e106..476c5a39 100644
--- a/booklore-api/src/main/java/org/booklore/repository/AuthorRepository.java
+++ b/booklore-api/src/main/java/org/booklore/repository/AuthorRepository.java
@@ -30,4 +30,16 @@ public interface AuthorRepository extends JpaRepository<AuthorEntity, Long> {
@Query("SELECT COUNT(b) > 0 FROM AuthorEntity a JOIN a.bookMetadataEntityList bm JOIN bm.book b WHERE a.id = :authorId AND b.library.id IN :libraryIds")
boolean existsByIdAndLibraryIds(@Param("authorId") Long authorId, @Param("libraryIds") Set<Long> libraryIds);
+
+ @Query(value = "SELECT m.book_id AS bookId, a.name AS authorName " +
+ "FROM book_metadata_author_mapping m " +
+ "JOIN author a ON a.id = m.author_id " +
+ "WHERE m.book_id IN :bookIds ORDER BY a.name",
+ nativeQuery = true)
+ List<AuthorBookProjection> findAuthorNamesByBookIds(@Param("bookIds") Set<Long> bookIds);
+
+ interface AuthorBookProjection {
+ Long getBookId();
+ String getAuthorName();
+ }
}
diff --git a/booklore-api/src/main/java/org/booklore/repository/NotebookEntryRepository.java b/booklore-api/src/main/java/org/booklore/repository/NotebookEntryRepository.java
index 0f6323ab..017c2244 100644
--- a/booklore-api/src/main/java/org/booklore/repository/NotebookEntryRepository.java
+++ b/booklore-api/src/main/java/org/booklore/repository/NotebookEntryRepository.java
@@ -7,6 +7,7 @@ import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;
+import java.time.Instant;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Set;
@@ -55,6 +56,13 @@ public interface NotebookEntryRepository extends Repository<AnnotationEntity, Lo
String getBookTitle();
}
+ interface BookWithCountProjection {
+ Long getBookId();
+ String getBookTitle();
+ int getNoteCount();
+ Instant getCoverUpdatedOn();
+ }
+
@Query(value = "SELECT t.id, t.type, t.book_id AS bookId, t.book_title AS bookTitle, " +
"t.text, t.note, t.color, t.style, t.chapter_title AS chapterTitle, " +
"(SELECT bf.book_type FROM book_file bf WHERE bf.book_id = t.book_id ORDER BY bf.id LIMIT 1) AS primaryBookType, " +
@@ -83,4 +91,37 @@ public interface NotebookEntryRepository extends Repository<AnnotationEntity, Lo
List<BookProjection> findBooksWithAnnotations(@Param("userId") Long userId,
@Param("search") String search,
Pageable pageable);
+
+ @Query(value = "SELECT t.book_id AS bookId, t.book_title AS bookTitle, " +
+ "COUNT(*) AS noteCount, t.cover_updated_on AS coverUpdatedOn " +
+ "FROM (" +
+ "SELECT a.book_id, a.user_id, bm.title AS book_title, bm.cover_updated_on " +
+ "FROM annotations a JOIN book_metadata bm ON bm.book_id = a.book_id " +
+ "UNION ALL " +
+ "SELECT n.book_id, n.user_id, bm.title AS book_title, bm.cover_updated_on " +
+ "FROM book_notes_v2 n JOIN book_metadata bm ON bm.book_id = n.book_id " +
+ "UNION ALL " +
+ "SELECT b.book_id, b.user_id, bm.title AS book_title, bm.cover_updated_on " +
+ "FROM book_marks b JOIN book_metadata bm ON bm.book_id = b.book_id" +
+ ") t WHERE t.user_id = :userId" +
+ " AND (:search IS NULL OR t.book_title LIKE :search ESCAPE '\\\\')" +
+ " GROUP BY t.book_id, t.book_title, t.cover_updated_on" +
+ " ORDER BY t.book_title",
+ countQuery = "SELECT COUNT(*) FROM (" +
+ "SELECT DISTINCT t.book_id FROM (" +
+ "SELECT a.book_id, a.user_id, bm.title AS book_title " +
+ "FROM annotations a JOIN book_metadata bm ON bm.book_id = a.book_id " +
+ "UNION ALL " +
+ "SELECT n.book_id, n.user_id, bm.title AS book_title " +
+ "FROM book_notes_v2 n JOIN book_metadata bm ON bm.book_id = n.book_id " +
+ "UNION ALL " +
+ "SELECT b.book_id, b.user_id, bm.title AS book_title " +
+ "FROM book_marks b JOIN book_metadata bm ON bm.book_id = b.book_id" +
+ ") t WHERE t.user_id = :userId" +
+ " AND (:search IS NULL OR t.book_title LIKE :search ESCAPE '\\\\')" +
+ ") cnt",
+ nativeQuery = true)
+ Page<BookWithCountProjection> findBooksWithAnnotationsPaginated(@Param("userId") Long userId,
+ @Param("search") String search,
+ Pageable pageable);
}
diff --git a/booklore-api/src/main/java/org/booklore/repository/ReadingSessionRepository.java b/booklore-api/src/main/java/org/booklore/repository/ReadingSessionRepository.java
index c3b2e46d..e5007f07 100644
--- a/booklore-api/src/main/java/org/booklore/repository/ReadingSessionRepository.java
+++ b/booklore-api/src/main/java/org/booklore/repository/ReadingSessionRepository.java
@@ -213,4 +213,182 @@ public interface ReadingSessionRepository extends JpaRepository<ReadingSessionEn
@Param("userId") Long userId,
@Param("year") int year,
@Param("tzOffset") String tzOffset);
+
+ // ========================================================================
+ // Listening (audiobook) stats
+ // ========================================================================
+
+ @Query(value = """
+ SELECT DATE(CONVERT_TZ(start_time, '+00:00', :tzOffset)) as date,
+ COUNT(*) as sessions,
+ COALESCE(ROUND(SUM(duration_seconds) / 60.0), 0) as durationMinutes
+ FROM reading_sessions
+ WHERE user_id = :userId
+ AND book_type = 'AUDIOBOOK'
+ AND YEAR(CONVERT_TZ(start_time, '+00:00', :tzOffset)) = :year
+ AND MONTH(CONVERT_TZ(start_time, '+00:00', :tzOffset)) = :month
+ GROUP BY DATE(CONVERT_TZ(start_time, '+00:00', :tzOffset))
+ ORDER BY date
+ """, nativeQuery = true)
+ List<ListeningHeatmapDto> findListeningSessionsByUserAndMonth(
+ @Param("userId") Long userId,
+ @Param("year") int year,
+ @Param("month") int month,
+ @Param("tzOffset") String tzOffset);
+
+ @Query(value = """
+ SELECT YEAR(CONVERT_TZ(start_time, '+00:00', :tzOffset)) as year,
+ WEEK(CONVERT_TZ(start_time, '+00:00', :tzOffset), 3) as week,
+ COALESCE(SUM(duration_seconds), 0) as totalDurationSeconds,
+ COUNT(*) as sessions
+ FROM reading_sessions
+ WHERE user_id = :userId
+ AND book_type = 'AUDIOBOOK'
+ AND CONVERT_TZ(start_time, '+00:00', :tzOffset) >= DATE_SUB(NOW(), INTERVAL :weeks WEEK)
+ GROUP BY year, week
+ ORDER BY year, week
+ """, nativeQuery = true)
+ List<WeeklyListeningTrendDto> findWeeklyListeningTrend(
+ @Param("userId") Long userId,
+ @Param("weeks") int weeks,
+ @Param("tzOffset") String tzOffset);
+
+ @Query(value = """
+ SELECT rs.book_id as bookId,
+ COALESCE(bm.title, 'Unknown') as title,
+ COALESCE(MAX(rs.end_progress), 0) as maxProgress,
+ COALESCE(MAX(bf.duration_seconds), 0) as totalDurationSeconds,
+ SUM(rs.duration_seconds) as listenedDurationSeconds
+ FROM reading_sessions rs
+ JOIN book b ON rs.book_id = b.id
+ LEFT JOIN book_metadata bm ON bm.book_id = b.id
+ LEFT JOIN book_file bf ON bf.book_id = b.id AND bf.book_type = 'AUDIOBOOK'
+ WHERE rs.user_id = :userId
+ AND rs.book_type = 'AUDIOBOOK'
+ GROUP BY rs.book_id, bm.title
+ """, nativeQuery = true)
+ List<AudiobookProgressDto> findAudiobookProgressByUser(@Param("userId") Long userId);
+
+ @Query(value = """
+ SELECT YEAR(COALESCE(ubp.date_finished, ubp.read_status_modified_time)) as year,
+ MONTH(COALESCE(ubp.date_finished, ubp.read_status_modified_time)) as month,
+ COUNT(*) as booksCompleted
+ FROM user_book_progress ubp
+ WHERE ubp.user_id = :userId
+ AND ubp.read_status = 'READ'
+ AND COALESCE(ubp.date_finished, ubp.read_status_modified_time) IS NOT NULL
+ AND EXISTS (
+ SELECT 1 FROM reading_sessions rs
+ WHERE rs.book_id = ubp.book_id
+ AND rs.user_id = ubp.user_id
+ AND rs.book_type = 'AUDIOBOOK'
+ )
+ GROUP BY year, month
+ ORDER BY year DESC, month DESC
+ """, nativeQuery = true)
+ List<MonthlyCompletedAudiobookDto> findMonthlyCompletedAudiobooks(@Param("userId") Long userId);
+
+ @Query(value = """
+ SELECT YEAR(CONVERT_TZ(start_time, '+00:00', :tzOffset)) as year,
+ MONTH(CONVERT_TZ(start_time, '+00:00', :tzOffset)) as month,
+ COALESCE(SUM(duration_seconds), 0) as totalDurationSeconds
+ FROM reading_sessions
+ WHERE user_id = :userId
+ AND book_type = 'AUDIOBOOK'
+ GROUP BY year, month
+ ORDER BY year DESC, month DESC
+ """, nativeQuery = true)
+ List<MonthlyListeningDurationDto> findMonthlyListeningDurations(
+ @Param("userId") Long userId,
+ @Param("tzOffset") String tzOffset);
+
+ @Query(value = """
+ SELECT
+ HOUR(CONVERT_TZ(start_time, '+00:00', :tzOffset)) as hourOfDay,
+ COUNT(*) as sessionCount,
+ SUM(duration_seconds) as totalDurationSeconds
+ FROM reading_sessions
+ WHERE user_id = :userId
+ AND book_type = 'AUDIOBOOK'
+ AND (:year IS NULL OR YEAR(CONVERT_TZ(start_time, '+00:00', :tzOffset)) = :year)
+ AND (:month IS NULL OR MONTH(CONVERT_TZ(start_time, '+00:00', :tzOffset)) = :month)
+ GROUP BY HOUR(CONVERT_TZ(start_time, '+00:00', :tzOffset))
+ ORDER BY hourOfDay
+ """, nativeQuery = true)
+ List<PeakReadingHourDto> findListeningPeakHoursByUser(
+ @Param("userId") Long userId,
+ @Param("year") Integer year,
+ @Param("month") Integer month,
+ @Param("tzOffset") String tzOffset);
+
+ @Query("""
+ SELECT
+ c.name as genre,
+ COUNT(DISTINCT b.id) as bookCount,
+ COUNT(rs) as totalSessions,
+ SUM(rs.durationSeconds) as totalDurationSeconds
+ FROM ReadingSessionEntity rs
+ JOIN rs.book b
+ JOIN b.metadata.categories c
+ WHERE rs.user.id = :userId
+ AND rs.bookType = org.booklore.model.enums.BookFileType.AUDIOBOOK
+ GROUP BY c.name
+ ORDER BY totalDurationSeconds DESC
+ """)
+ List<GenreStatisticsDto> findListeningGenreStatisticsByUser(@Param("userId") Long userId);
+
+ @Query(value = """
+ SELECT a.name as authorName,
+ COUNT(DISTINCT rs.book_id) as bookCount,
+ COUNT(*) as totalSessions,
+ COALESCE(SUM(rs.duration_seconds), 0) as totalDurationSeconds
+ FROM reading_sessions rs
+ JOIN book_metadata_author_mapping bam ON bam.book_id = rs.book_id
+ JOIN author a ON a.id = bam.author_id
+ WHERE rs.user_id = :userId
+ AND rs.book_type = 'AUDIOBOOK'
+ GROUP BY a.name
+ ORDER BY totalDurationSeconds DESC
+ """, nativeQuery = true)
+ List<ListeningAuthorDto> findListeningAuthorStatsByUser(@Param("userId") Long userId);
+
+ @Query(value = """
+ SELECT
+ HOUR(CONVERT_TZ(rs.start_time, '+00:00', :tzOffset))
+ + MINUTE(CONVERT_TZ(rs.start_time, '+00:00', :tzOffset)) / 60.0 as hourOfDay,
+ rs.duration_seconds / 60.0 as durationMinutes,
+ DAYOFWEEK(CONVERT_TZ(rs.start_time, '+00:00', :tzOffset)) as dayOfWeek
+ FROM reading_sessions rs
+ WHERE rs.user_id = :userId
+ AND rs.book_type = 'AUDIOBOOK'
+ ORDER BY rs.start_time DESC
+ LIMIT 500
+ """, nativeQuery = true)
+ List<SessionScatterDto> findListeningSessionScatterByUser(
+ @Param("userId") Long userId,
+ @Param("tzOffset") String tzOffset);
+
+ @Query(value = """
+ SELECT rs.book_id as bookId,
+ COALESCE(bm.title, 'Unknown') as title,
+ bm.page_count as pageCount,
+ MIN(CONVERT_TZ(rs.start_time, '+00:00', :tzOffset)) as firstSessionDate,
+ MAX(CONVERT_TZ(rs.end_time, '+00:00', :tzOffset)) as lastSessionDate,
+ COUNT(*) as totalSessions,
+ COALESCE(SUM(rs.duration_seconds), 0) as totalDurationSeconds,
+ COALESCE(MAX(rs.end_progress), 0) / 100.0 as maxProgress,
+ ubp.read_status as readStatus
+ FROM reading_sessions rs
+ JOIN book b ON rs.book_id = b.id
+ LEFT JOIN book_metadata bm ON bm.book_id = b.id
+ LEFT JOIN user_book_progress ubp ON ubp.book_id = rs.book_id AND ubp.user_id = rs.user_id
+ WHERE rs.user_id = :userId
+ AND YEAR(CONVERT_TZ(rs.start_time, '+00:00', :tzOffset)) = :year
+ GROUP BY rs.book_id, bm.title, bm.page_count, ubp.read_status
+ ORDER BY firstSessionDate
+ """, nativeQuery = true)
+ List<BookTimelineDto> findBookTimelineByUserAndYear(
+ @Param("userId") Long userId,
+ @Param("year") int year,
+ @Param("tzOffset") String tzOffset);
}
diff --git a/booklore-api/src/main/resources/db/migration/V131__Add_tts_position_cfi_to_user_book_file_progress.sql b/booklore-api/src/main/resources/db/migration/V131__Add_tts_position_cfi_to_user_book_file_progress.sql
new file mode 100644
index 00000000..e77fc1c0
--- /dev/null
+++ b/booklore-api/src/main/resources/db/migration/V131__Add_tts_position_cfi_to_user_book_file_progress.sql
@@ -0,0 +1 @@
+ALTER TABLE user_book_file_progress ADD COLUMN IF NOT EXISTS tts_position_cfi VARCHAR(1000) NULL;
diff --git a/booklore-api/src/main/resources/db/migration/V132__Add_scanned_on_to_books.sql b/booklore-api/src/main/resources/db/migration/V132__Add_scanned_on_to_books.sql
new file mode 100644
index 00000000..9819c28c
--- /dev/null
+++ b/booklore-api/src/main/resources/db/migration/V132__Add_scanned_on_to_books.sql
@@ -0,0 +1 @@
+ALTER TABLE book ADD COLUMN IF NOT EXISTS scanned_on TIMESTAMP NULL;
Upstream changes detected
The upstream
developbranch has moved ahead of our pinned submodule commit.bf195784cac557b7🗄️ New / changed Flyway migrations (2)
Port these to Liquibase in
src/main/resources/db/changelog/changelogs/.V131__Add_tts_position_cfi_to_user_book_file_progress.sqlV132__Add_scanned_on_to_books.sql🏗️ Changed entities (3)
Review for new fields that may require Liquibase columns or
${datetime.type}/${id.type}substitutions.BookEntity.javaBookFileEntity.javaUserBookFileProgressEntity.java🔍 Changed repositories (3)
Check for new
nativeQuery = trueannotations — these need JPQL rewrites in our overlay for SQLite/PostgreSQL compatibility.AuthorRepository.javaNotebookEntryRepository.javaReadingSessionRepository.java✅ Checklist
cac557b7(or the latest stable tag)build.gradlefor dependency changes./gradlew test)Full diff (watched paths only)