Zenn
Closed14

Mac Kindleからブクログに追加する調査

kawakenkawaken

Kindleのデータを眺める

sqlite3 コマンドで色々と試してみる。

kawakenkawaken
sqlite> .schema zbook
CREATE TABLE ZBOOK ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZISHIDDENBYUSER INTEGER, ZRAWAUTOSHELVE INTEGER, ZRAWBOOKSTATE INTEGER, ZRAWBOOKTYPE INTEGER, ZRAWBOOKUPGRADESNEEDED INTEGER, ZRAWCURRENTPOSITION INTEGER, ZRAWERL INTEGER, ZRAWFILESIZE INTEGER, ZRAWHASCOMPANION INTEGER, ZRAWHASFIXEDMOPHIGHLIGHTS INTEGER, ZRAWINBOOKCOVERCHECKED INTEGER, ZRAWISARCHIVABLE INTEGER, ZRAWISDICTIONARY INTEGER, ZRAWISENCRYPTED INTEGER, ZRAWISHIDDEN INTEGER, ZRAWISKEPT INTEGER, ZRAWISMULTIMEDIA INTEGER, ZRAWISPENDINGVERIFICATION INTEGER, ZRAWISSMDCREATEDDICTIONARY INTEGER, ZRAWISTRANSLATIONDICTIONARY INTEGER, ZRAWISUNREAD INTEGER, ZRAWLASTACCESSTIME INTEGER, ZRAWLASTOPENSUCCEEDED INTEGER, ZRAWLASTVIEW INTEGER, ZRAWMAXLOCATION INTEGER, ZRAWMAXPOSITION INTEGER, ZRAWNCXINFOSTORED INTEGER, ZRAWPROGRESSDOTCATEGORY INTEGER, ZRAWPUBLICATIONDATE INTEGER, ZRAWREADSTATE INTEGER, ZRAWREADSTATEORIGIN INTEGER, ZRAWREADINGMODE INTEGER, ZRAWUSERVISIBLELABELING INTEGER, ZCOMPANION INTEGER, ZALTERNATESORTTITLE VARCHAR, ZBOOKID VARCHAR, ZBUNDLEPATH VARCHAR, ZCONTENTTAGS VARCHAR, ZDICTIONARYLOCALEID VARCHAR, ZDICTIONARYSHORTTITLE VARCHAR, ZDICTIONARYSOURCELANG VARCHAR, ZDISPLAYTITLE VARCHAR, ZGROUPID VARCHAR, ZLANGUAGE VARCHAR, ZLONGCURRENTPOSITION VARCHAR, ZLONGMAXPOSITION VARCHAR, ZMIMETYPE VARCHAR, ZPARENTASIN VARCHAR, ZPATH VARCHAR, ZPERASINGUID VARCHAR, ZRAWPUBLISHER VARCHAR, ZSHELF VARCHAR, ZSORTTITLE VARCHAR, ZWATERMARK VARCHAR, ZALTERNATESORTAUTHOR BLOB, ZDISPLAYAUTHOR BLOB, ZEXTENDEDMETADATA BLOB, ZORIGINS BLOB, ZSORTAUTHOR BLOB, ZSYNCMETADATAATTRIBUTES BLOB, ZRAWTITLEDETAILSJSON BLOB );
CREATE INDEX ZBOOK_ZCOMPANION_INDEX ON ZBOOK (ZCOMPANION);
CREATE INDEX Z_Book_byBookIdIndex ON ZBOOK (ZBOOKID COLLATE BINARY ASC);
CREATE INDEX Z_Book_byPathIndex ON ZBOOK (ZPATH COLLATE BINARY ASC);
CREATE INDEX Z_Book_byRawBookStateIndex ON ZBOOK (ZRAWBOOKSTATE COLLATE BINARY ASC);
CREATE INDEX Z_Book_byRawBookTypeIndex ON ZBOOK (ZRAWBOOKTYPE COLLATE BINARY ASC);
CREATE INDEX Z_Book_byRawIsArchivableIndex ON ZBOOK (ZRAWISARCHIVABLE COLLATE BINARY ASC);
CREATE INDEX Z_Book_byRawIsEncryptedIndex ON ZBOOK (ZRAWISENCRYPTED COLLATE BINARY ASC);
CREATE INDEX Z_Book_byRawLastAccessTimeIndex ON ZBOOK (ZRAWLASTACCESSTIME COLLATE BINARY ASC);
kawakenkawaken
  • ZDISPLAYTITLE: 表示名っぽい
  • ZCONTENTTAGS: 漫画の時に ;MANGA って入るっぽい
  • ZRAWLASTACCESSTIME: 最後にアクセスした時刻？
サンプル
     ZDISPLAYTITLE = 銃夢Ｌａｓｔ　Ｏｒｄｅｒ　ＮＥＷ　ＥＤＩＴＩＯＮ（１２） (イブニングコミックス)
      ZCONTENTTAGS = ;MANGA
ZRAWLASTACCESSTIME = 1570549279

     ZDISPLAYTITLE = イシューからはじめよ――知的生産の「シンプルな本質」
      ZCONTENTTAGS =
ZRAWLASTACCESSTIME = 1522869009
kawakenkawaken

SQLを組み立てる

出力する際やりたいこと

  1. 漫画は除去する
  2. 登録済みのものもあるので、タイトルを見て判断したい
  3. アクセス日時は読める形式にする
kawakenkawaken

時刻を数値ではなく日時で出力するには datetime を使うらしい。

sqlite> SELECT ZDISPLAYTITLE,datetime(ZRAWLASTACCESSTIME, 'unixepoch')  FROM zbook ORDER BY zbook.zrawlastaccesstime LIMIT 1;
                            ZDISPLAYTITLE = Daijisen Japanese Dictionary (大辞泉)
datetime(ZRAWLASTACCESSTIME, 'unixepoch') = 2013-10-04 03:28:44
kawakenkawaken
SELECT
  substring(zbook.zbookid, 3, 10) AS ASIN,
  ZDISPLAYTITLE,
  datetime(ZRAWLASTACCESSTIME, 'unixepoch') AS `日付`
FROM zbook
WHERE ZCONTENTTAGS != ";MANGA"
ORDER BY ZRAWLASTACCESSTIME DESC
LIMIT 100
;
kawakenkawaken

これでいけそう

sqlite3 ~/Library/Containers/com.amazon.Lassen/Data/Library/Protected/BookData.sqlite <<'SQL'
.mode csv
SELECT
  substring(zbook.zbookid, 3, 10) AS ASIN,
  ZDISPLAYTITLE,
  datetime(ZRAWLASTACCESSTIME, 'unixepoch') AS `日付`
FROM zbook
WHERE ZCONTENTTAGS != ";MANGA"
ORDER BY ZRAWLASTACCESSTIME DESC
LIMIT 100
;
SQL
kawakenkawaken

ASINがB始まりじゃないものはインポートしたPDFなど。ブクログには登録できないので、除外する。

SELECT
  substring(ZBOOKID, 3, 10) AS ASIN,
  ZDISPLAYTITLE AS `タイトル`,
  datetime(ZRAWLASTACCESSTIME, 'unixepoch') AS `日付`
FROM zbook
WHERE ZCONTENTTAGS != ";MANGA"
AND ZBOOKID LIKE 'A:B%'
ORDER BY ZRAWLASTACCESSTIME
kawakenkawaken

パイプラインの途中で手動で選択しつつ、次の処理に渡したい。
たぶん fzf で行けるはず。

kawakenkawaken
sqlite3 ~/Library/Containers/com.amazon.Lassen/Data/Library/Protected/BookData.sqlite <<'SQL' | fzf --multi
.mode csv
SELECT
  substring(zbook.zbookid, 3, 10) AS ASIN,
  ZDISPLAYTITLE,
  datetime(ZRAWLASTACCESSTIME, 'unixepoch') AS `日付`
FROM zbook
WHERE ZCONTENTTAGS != ";MANGA"
ORDER BY ZRAWLASTACCESSTIME DESC
LIMIT 100
;
SQL
kawakenkawaken
  • SQLを修正してKindleの書籍のみに絞り込み
  • fzfが出力順を反転してしまうので --tac オプションを追加
sqlite3 ~/Library/Containers/com.amazon.Lassen/Data/Library/Protected/BookData.sqlite <<'SQL' | fzf --multi --tac | cut -d, -f2
.mode csv
SELECT
  datetime(ZRAWLASTACCESSTIME, 'unixepoch') AS `日付`,
  substring(ZBOOKID, 3, 10) AS ASIN,
  ZDISPLAYTITLE AS `タイトル`
FROM zbook
WHERE ZCONTENTTAGS != ";MANGA"
AND ZBOOKID LIKE 'A:B%'
ORDER BY ZRAWLASTACCESSTIME
;
SQL
このスクラップは18日前にクローズされました