Upstream sync required: 4b846295 #6
Description
Upstream changes detected
The upstream develop branch has moved ahead of our pinned submodule commit.
| Commit | |
|---|---|
| Submodule (current) | 40c98e84 |
| Upstream develop (latest) | 4b846295 |
| Full diff | Compare on GitHub |
🗄️ New / changed Flyway migrations (3)
Port these to Liquibase in src/main/resources/db/changelog/changelogs/.
V130__Add_book_per_file_organization_mode.sqlV131__Add_tts_position_cfi_to_user_book_file_progress.sqlV132__Add_scanned_on_to_books.sql
🏗️ Changed entities (2)
Review for new fields that may require Liquibase columns or ${datetime.type} / ${id.type} substitutions.
BookEntity.javaUserBookFileProgressEntity.java
🔍 Changed repositories (4)
Check for new nativeQuery = true annotations — these need JPQL rewrites in our overlay for SQLite/PostgreSQL compatibility.
AuthorRepository.javaBookRepository.javaNotebookEntryRepository.javaReadingSessionRepository.java
✅ Checklist
- Update submodule to
4b846295(or the latest stable tag) - Port 3 new Flyway migration(s) to Liquibase changelogs
- Review 2 changed entity file(s) for schema impact
- Audit 4 changed repository file(s) for new native queries
- Diff upstream
build.gradlefor dependency changes - Build and run full test suite (
./gradlew test)
Full diff (watched paths only)
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/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/BookRepository.java b/booklore-api/src/main/java/org/booklore/repository/BookRepository.java
index 08ffb61b..7ba0c1ea 100644
--- a/booklore-api/src/main/java/org/booklore/repository/BookRepository.java
+++ b/booklore-api/src/main/java/org/booklore/repository/BookRepository.java
@@ -27,12 +27,15 @@ public interface BookRepository extends JpaRepository<BookEntity, Long>, JpaSpec
@Query("SELECT b FROM BookEntity b JOIN b.bookFiles bf WHERE bf.currentHash = :currentHash AND bf.isBookFormat = true AND (b.deleted IS NULL OR b.deleted = false)")
Optional<BookEntity> findByCurrentHash(@Param("currentHash") String currentHash);
+ @Query("SELECT b FROM BookEntity b JOIN FETCH b.bookFiles bf WHERE bf.currentHash = :currentHash AND bf.isBookFormat = true AND (b.deleted IS NULL OR b.deleted = false OR b.deletedAt > :cutoff)")
+ Optional<BookEntity> findByCurrentHashIncludingRecentlyDeleted(@Param("currentHash") String currentHash, @Param("cutoff") Instant cutoff);
+
Optional<BookEntity> findByBookCoverHash(String bookCoverHash);
@Query("SELECT b.id FROM BookEntity b WHERE b.library.id = :libraryId AND (b.deleted IS NULL OR b.deleted = false)")
Set<Long> findBookIdsByLibraryId(@Param("libraryId") long libraryId);
- @Query("SELECT DISTINCT b FROM BookEntity b JOIN b.bookFiles bf WHERE b.libraryPath.id = :libraryPathId AND bf.fileSubPath LIKE CONCAT(:fileSubPathPrefix, '%') AND bf.isBookFormat = true AND (b.deleted IS NULL OR b.deleted = false)")
+ @Query("SELECT DISTINCT b FROM BookEntity b JOIN b.bookFiles bf WHERE b.libraryPath.id = :libraryPathId AND (bf.fileSubPath = :fileSubPathPrefix OR bf.fileSubPath LIKE CONCAT(:fileSubPathPrefix, '/%')) AND bf.isBookFormat = true AND (b.deleted IS NULL OR b.deleted = false)")
List<BookEntity> findAllByLibraryPathIdAndFileSubPathStartingWith(@Param("libraryPathId") Long libraryPathId, @Param("fileSubPathPrefix") String fileSubPathPrefix);
@Query("SELECT DISTINCT b FROM BookEntity b JOIN b.bookFiles bf WHERE b.libraryPath.id = :libraryPathId AND bf.fileSubPath = :fileSubPath AND bf.isBookFormat = true AND (b.deleted IS NULL OR b.deleted = false)")
@@ -129,6 +132,19 @@ public interface BookRepository extends JpaRepository<BookEntity, Long>, JpaSpec
@Query("SELECT COUNT(b) FROM BookEntity b WHERE b.deleted = TRUE")
long countAllSoftDeleted();
+ @Query("""
+ SELECT DISTINCT b FROM BookEntity b
+ JOIN FETCH b.bookFiles bf
+ WHERE b.libraryPath.id = :libraryPathId
+ AND (bf.fileSubPath = :folderPath
+ OR bf.fileSubPath LIKE CONCAT(:folderPath, '/%')
+ OR (bf.folderBased = true AND CONCAT(bf.fileSubPath, '/', bf.fileName) = :folderPath))
+ AND bf.isBookFormat = true
+ AND (b.deleted IS NULL OR b.deleted = false)
+ """)
+ List<BookEntity> findBooksWithFilesUnderPath(@Param("libraryPathId") Long libraryPathId,
+ @Param("folderPath") String folderPath);
+
@Query(value = """
SELECT b.*
FROM book b
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..e6bded0e 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) 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/V130__Add_book_per_file_organization_mode.sql b/booklore-api/src/main/resources/db/migration/V130__Add_book_per_file_organization_mode.sql
new file mode 100644
index 00000000..034c35bb
--- /dev/null
+++ b/booklore-api/src/main/resources/db/migration/V130__Add_book_per_file_organization_mode.sql
@@ -0,0 +1 @@
+ALTER TABLE library MODIFY COLUMN organization_mode VARCHAR(50) DEFAULT 'BOOK_PER_FILE';
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;