iTranslated by AI

The content below is an AI-generated translation. This is an experimental feature, and may contain errors. View original article
🔖

Extracting Book Information from the New Kindle for Mac (Swift)

に公開
iOS
Swift
SwiftUI
Kindle
tech

Introduction

The previous Kindle app for Mac could retrieve book information from the XML file below, but in the new Kindle app, I couldn't find the XML file, so I looked for where the book information might be stored.

https://apps.apple.com/jp/app/kindle-classic/id405399194

/Library/Containers/com.amazon.Kindle/Data/Library/Application Support/Kindle/Cache/KindleSyncMetadataCache.xml

Upon searching, I found that book information could likely be retrieved from the following SQLite file.

https://apps.apple.com/jp/app/kindle/id302584613

/Library/Containers/com.amazon.Lassen/Data/Library/Protected/BookData.sqlite

I extracted only manga from here and tried displaying them in a list on an iOS app.

kindle

Environment

  • macOS 13.5.2
  • Kindle 6.85.2
  • Xcode 15
  • iOS 17

Book Information

There were 13 tables in BookData.sqlite as shown below.

tables

I wasn't sure exactly what each table contained, but it seemed like the ZBOOK table held the book information. The data in the ZBOOK table is as follows.

cid name type
0 Z_PK INTEGER
1 Z_ENT INTEGER
2 Z_OPT INTEGER
3 ZISHIDDENBYUSER INTEGER
4 ZRAWAUTOSHELVE INTEGER
5 ZRAWBOOKSTATE INTEGER
6 ZRAWBOOKTYPE INTEGER
7 ZRAWBOOKUPGRADESNEEDED INTEGER
8 ZRAWCURRENTPOSITION INTEGER
9 ZRAWERL INTEGER
10 ZRAWFILESIZE INTEGER
11 ZRAWHASCOMPANION INTEGER
12 ZRAWHASFIXEDMOPHIGHLIGHTS INTEGER
13 ZRAWINBOOKCOVERCHECKED INTEGER
14 ZRAWISARCHIVABLE INTEGER
15 ZRAWISDICTIONARY INTEGER
16 ZRAWISENCRYPTED INTEGER
17 ZRAWISHIDDEN INTEGER
18 ZRAWISKEPT INTEGER
19 ZRAWISMULTIMEDIA INTEGER
20 ZRAWISPENDINGVERIFICATION INTEGER
21 ZRAWISSMDCREATEDDICTIONARY INTEGER
22 ZRAWISTRANSLATIONDICTIONARY INTEGER
23 ZRAWISUNREAD INTEGER
24 ZRAWLASTACCESSTIME INTEGER
25 ZRAWLASTOPENSUCCEEDED INTEGER
26 ZRAWLASTVIEW INTEGER
27 ZRAWMAXLOCATION INTEGER
28 ZRAWMAXPOSITION INTEGER
29 ZRAWNCXINFOSTORED INTEGER
30 ZRAWPROGRESSDOTCATEGORY INTEGER
31 ZRAWPUBLICATIONDATE INTEGER
32 ZRAWREADSTATE INTEGER
33 ZRAWREADSTATEORIGIN INTEGER
34 ZRAWREADINGMODE INTEGER
35 ZRAWUSERVISIBLELABELING INTEGER
36 ZCOMPANION INTEGER
37 ZALTERNATESORTTITLE VARCHAR
38 ZBOOKID VARCHAR
39 ZBUNDLEPATH VARCHAR
40 ZCONTENTTAGS VARCHAR
41 ZDICTIONARYLOCALEID VARCHAR
42 ZDICTIONARYSHORTTITLE VARCHAR
43 ZDICTIONARYSOURCELANG VARCHAR
44 ZDISPLAYTITLE VARCHAR
45 ZGROUPID VARCHAR
46 ZLANGUAGE VARCHAR
47 ZLONGCURRENTPOSITION VARCHAR
48 ZLONGMAXPOSITION VARCHAR
49 ZMIMETYPE VARCHAR
50 ZPARENTASIN VARCHAR
51 ZPATH VARCHAR
52 ZPERASINGUID VARCHAR
53 ZRAWPUBLISHER VARCHAR
54 ZSHELF VARCHAR
55 ZSORTTITLE VARCHAR
56 ZWATERMARK VARCHAR
57 ZALTERNATESORTAUTHOR BLOB
58 ZDISPLAYAUTHOR BLOB
59 ZEXTENDEDMETADATA BLOB
60 ZORIGINS BLOB
61 ZSORTAUTHOR BLOB
62 ZSYNCMETADATAATTRIBUTES BLOB
63 ZRAWTITLEDETAILSJSON BLOB

App Creation

Since it's unclear what each data point represents, I'll extract only the necessary ones. I'll list and extract the following, which seem likely to be relevant:

  • Z_PK: ID
  • ZRAWPUBLICATIONDATE: Publication Date
  • ZBOOKID: ASIN
  • ZCONTENTTAGS: Category
  • ZDISPLAYTITLE: Title
  • ZRAWPUBLISHER: Publisher
  • ZSORTTITLE: Title Pronunciation (Kana)

DB Creation

While the current setup works, I'll use SwiftData to create a database for easier handling.

Convert the above data from BookData.sqlite into the following Book class.

import Foundation
import SwiftData

@Model
final class Book {

    @Attribute(.unique) var id: Int
    var asin: String
    var title: String
    var titlePronunciation: String
    var publicationDate: Date?
    var contentTags: String
    var publisher: String

    init(id: Int,
         asin: String,
         title: String,
         titlePronunciation: String,
         publicationDate: Date?,
         contentTags: String,
         publisher: String) {
        self.id = id
        self.asin = asin
        self.title = title
        self.titlePronunciation = titlePronunciation
        self.publicationDate = publicationDate
        self.contentTags = contentTags
        self.publisher = publisher
    }
}

Add BookData.sqlite to the project file and load the data using SQLite3 as follows.

import Foundation
import SQLite3

final class Database {

    struct MetaData {
        let id: Int
        let publicationDate: Date?
        let asin: String
        var contentTags: String
        let title: String
        var publisher: String
        let titlePronunciation: String

        init(row: [Any?]) {
            self.id = (row[0] as? Int) ?? 0
            let time = (row[1] as? Int) ?? 0
            self.publicationDate = time == 0 ? nil : Date(timeIntervalSince1970: TimeInterval(time))
            self.asin = (row[2] as? String) ?? ""
            self.contentTags = (row[3] as? String) ?? ""
            self.title = (row[4] as? String) ?? ""
            self.publisher = (row[5] as? String) ?? ""
            self.titlePronunciation = (row[6] as? String) ?? ""
        }

        func convertedBook() -> Book {
            let asinValue = asin.replacingOccurrences(of: "A:", with: "")
            return .init(
                id: id,
                asin: asinValue.components(separatedBy: "-").first!,
                title: title, titlePronunciation: titlePronunciation,
                publicationDate: publicationDate, contentTags: contentTags,
                publisher: publisher
            )
        }
    }

    func fetchBooks() -> [Book] {
        var db: OpaquePointer?
        defer {
            sqlite3_close(db)
        }

        let filePath = Bundle.main.path(forResource: "BookData", ofType: "sqlite")
        guard sqlite3_open(filePath, &db) == SQLITE_OK else {
            return []
        }

        var stmt: OpaquePointer?
        defer {
            sqlite3_finalize(stmt)
        }

        let sql = "SELECT Z_PK, ZRAWPUBLICATIONDATE, ZBOOKID, ZCONTENTTAGS, ZDISPLAYTITLE, ZRAWPUBLISHER, ZSORTTITLE FROM ZBOOK;"
        guard sqlite3_prepare_v2(db, (sql as NSString).utf8String, -1, &stmt, nil) == SQLITE_OK else {
            return []
        }

        var metaDataList = [MetaData]()
        while SQLITE_ROW == sqlite3_step(stmt) {
            metaDataList.append(.init(row: [
                _getIntValue(0), _getIntValue(1),
                _getStringValue(2), _getStringValue(3),
                _getStringValue(4), _getStringValue(5),
                _getStringValue(6)
            ]))
        }

        func _getIntValue(_ column: Int32) -> Int? {
            if sqlite3_column_type(stmt, column) == SQLITE_NULL {
                return nil
            } else {
                return Int(sqlite3_column_int(stmt, column))
            }
        }

        func _getStringValue(_ column: Int32) -> String? {
            if sqlite3_column_type(stmt, column) == SQLITE_NULL {
                return nil
            } else {
                return String(cString: sqlite3_column_text(stmt, column))
            }
        }

        return metaDataList.map { $0.convertedBook() }
    }
}

The key point is the date conversion process. Since the dates in the database are in Unix time, they are converted using Date(timeIntervalSince1970:) as follows:

let time = (row[1] as? Int) ?? 0
self.publicationDate = time == 0 ? nil : Date(timeIntervalSince1970: TimeInterval(time))

Display

All that's left is to extract the manga and display them in a list. Manga are identified by ;MANGA in the ZCONTENTTAGS field, so we will extract only that data.

import SwiftUI
import SwiftData

@main
struct KindleBooksApp: App {
    var body: some Scene {
        WindowGroup {
            ContentView()
                .modelContainer(for: Book.self)
        }
    }
}

struct ContentView: View {

    @Environment(\.modelContext) private var context
    @Query(filter: #Predicate<Book>  { $0.contentTags == ";MANGA" }, sort: \Book.titlePronunciation)
    private var books: [Book]

    var body: some View {
        NavigationStack {
            VStack {
                if books.isEmpty {
                    addButton
                } else {
                    List {
                        ForEach(books, id: \.id) { book in
                            NavigationLink {
                                DetailView(book: book)
                            } label: {
                                VStack {
                                    Text(book.title)
                                }
                            }
                        }
                    }
                }
            }.navigationTitle("List")
        }
    }

    private var addButton: some View {
        return  Button {
            let books = Database().fetchBooks()
            books.forEach { context.insert($0) }
            do {
                try context.save()
            } catch let error {
                print(error)
            }
        } label: {
            Text("Get Data")
        }
    }
}

struct DetailView: View {

    let book: Book

    var dateFormat: DateFormatter = {
        let format = DateFormatter()
        format.dateFormat = "yyyy/MM/dd"
        return format
    }()

    var body: some View {
        VStack(spacing: 8) {
            Text("ID: \(book.id)")
            Text("ASIN: \(book.asin)")
            Text("Title: \(book.title)")
            Text("Title Kana: \(book.titlePronunciation)")
            if let date = book.publicationDate {
                Text("Release Date: \(dateFormat.string(from: date))")
            }
            Text("Tags: \(book.contentTags)")
            Text("Publisher: \(book.publisher)")
        }
        .padding(16)
        .navigationTitle("Details")
    }
}

When the "Get Data" button is pressed, the book information is created, and the manga are extracted and sorted in syllabic (Gojuon) order as shown below.

@Query(filter: #Predicate<Book>  { $0.contentTags == ";MANGA" }, sort: \Book.titlePronunciation)

With this, it's complete! 🎉

Conclusion

By switching from XML to SQLite, I can now retrieve various information!

However, the data displayed in the Kindle app seems slightly different from what's in the ZBOOK table, so it might be stored elsewhere (it seems the latest books aren't in the ZBOOK table...).

For now, I'm satisfied since I was able to retrieve data that looks plausible.

Reference Sites

Discussion