Upstream sync required: 4b846295 #6

New issue

Open

opened 2026-03-11 06:14:53 -03:00 by github-actions[bot] · 0 comments

github-actions[bot] commented 2026-03-11 06:14:53 -03:00 (Migrated from github.com)

Copy link

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.sql
• V131__Add_tts_position_cfi_to_user_book_file_progress.sql
• V132__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.java
• UserBookFileProgressEntity.java

🔍 Changed repositories (4)

Check for new nativeQuery = true annotations — these need JPQL rewrites in our overlay for SQLite/PostgreSQL compatibility.

• AuthorRepository.java
• BookRepository.java
• NotebookEntryRepository.java
• ReadingSessionRepository.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.gradle for 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;

## Upstream changes detected The upstream `develop` branch has moved ahead of our pinned submodule commit. | | Commit | |---|---| | **Submodule (current)** | [`40c98e84`](https://github.com/booklore-app/booklore/commit/40c98e84) | | **Upstream develop (latest)** | [`4b846295`](https://github.com/booklore-app/booklore/commit/4b846295) | | **Full diff** | [Compare on GitHub](https://github.com/booklore-app/booklore/compare/40c98e84...4b846295) | ### 🗄️ New / changed Flyway migrations (3) Port these to Liquibase in `src/main/resources/db/changelog/changelogs/`. - `V130__Add_book_per_file_organization_mode.sql` - `V131__Add_tts_position_cfi_to_user_book_file_progress.sql` - `V132__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.java` - `UserBookFileProgressEntity.java` ### 🔍 Changed repositories (4) Check for new `nativeQuery = true` annotations — these need JPQL rewrites in our overlay for SQLite/PostgreSQL compatibility. - `AuthorRepository.java` - `BookRepository.java` - `NotebookEntryRepository.java` - `ReadingSessionRepository.java` ### ✅ Checklist - [x] 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.gradle` for dependency changes - [ ] Build and run full test suite (`./gradlew test`) <details> <summary>Full diff (watched paths only)</summary> ```diff 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; ``` </details>

Sign in to join this conversation.

No Branch/Tag specified

Branches Tags

develop

task/6-upstream-sync-required-4b846295

fix/baseline-column-lengths

main

No results found.

Labels

Clear labels   

bug

Something isn't working

  

documentation

Improvements or additions to documentation

  

duplicate

This issue or pull request already exists

  

enhancement

New feature or request

  

good first issue

Good for newcomers

  

help wanted

Extra attention is needed

  

invalid

This doesn't seem right

  

question

Further information is requested

  

upstream-sync

Upstream Booklore changes that need to be ported

  

wontfix

This will not be worked on

No labels

bug

documentation

duplicate

enhancement

good first issue

help wanted

invalid

question

upstream-sync

wontfix

Milestone

Clear milestone

No items

No milestone

Projects

Clear projects

No items

No project

Assignees

Clear assignees

No assignees

1 participant

Notifications

Subscribe

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference

binarymelon/booklore-multidb#6

No description provided.