複数のところで買ってたらわからなくなったよ
という事で、一覧を作成することにしました。
kobo honto dmmのPCアプリは、SQLiteで書籍を管理しているので
その中から、必要な情報を引っ張り出してくることにする。
手順
- 各アプリを起動して、書籍情報を最新の状態にする。
- 使用している、SQLiteのDBを調べる。
kobo C:\Users\xxxxxx\AppData\Local\Kobo\Kobo Desktop Edition\Kobo.sqlite
honto C:\Users\xxxxx\AppData\Local\DNP\honto\BookShelf.db
dmm C:\Users\xxxxx\AppData\Roaming\DMM\DMMbookviewer2\dmmbookshelf.sqlite3 - SQLiteを使えるようにして、対応するSQLを実行する。
お好みの言語等(python)で実行してください。
A5:SQL Mk-2などで実行して、そのままCSV等に出力できます。
SQL文は、自分用にNotionでインポート用に作ってあります。
レイアウトギャラリー表示で使えるようにしてます。
お好みでSQL文を変更してください。不要な項目の削除、ソート順など。
項目によって、カンマが含まれるものがある為、注意が必要。
出力内容 書名,仮名,表紙,著者,ReadLink,eShop,販売ページ,カテゴリ,出版社,説明,ID,ISBN,購入日
※出力されない項目もあります。
kobo用
kobo.sql
SELECT
quote(Title) Title
, "" kana
, "" Cover
, quote(Attribution) author
, "" ReadLink
, "kobo" eShop
, "https://books.rakuten.co.jp/rk/" || REPLACE(CrossRevisionId,'-','') || "/" ShopURL
, "" category_name
, quote(Publisher) Publisher
, quote(Description) Description
, REPLACE(CrossRevisionId,'-','') product_Id
, ISBN
, "" purchaseDate
FROM
"content"
WHERE
ContentType = 6 and
Accessibility = 1 and
___UserID <> "removed"
ORDER BY
-- ContentID", TitleKana
TitleKana, ContentID
-- ___SyncTime desc, TitleKana, ContentID
honto用
honto.sql
SELECT
BM.bookTitle Title
,BM.bookTitleKana kana
,BC.thumbnailUrl Cover
,BM.displayAuthors2 author
,"" ReadLink
,"honto" eShop
,ifnull(BM.purchaseUrl, 'https://honto.jp/ebook/pd_' || replace(substr(BC.thumbnailUrl,35,9),'/','') || '.html') ShopURL
,BM.ph2_category category_name
,BM.publisher Publisher
,"" Description
,BC.bookId product_Id -- bookId
,BM.isbn ISBN
,BC.purchaseDate
FROM
BOOK_CONTENT_TBL AS BC,
BOOK_COMMON_TBL AS BM
WHERE
BC.bookId = BM.bookId
and BC.purchaseFlag =1
and BC.downloadLimitDate=""
and BC.userid <>""
ORDER BY
bookTitlekana asc, purchaseDate desc, userId asc
-- purchaseDate desc, bookTitlekana asc, userId asc
dmm用
※category_idによって、ShopURLがへ変わる可能性があります
dmm.sql
SELECT
title 'Title'
, title_ruby kana
, image_src Cover
, main_author_name author
, "" ReadLink
,"dmm" eShop
, case
when category_id = '1' then "https://book.dmm.co.jp/detail/" || product_Id || "/"
when category_id = '2' then
case when series_id = '-1' then "https://book.dmm.com/detail/" || product_Id || "/"
else "https://book.dmm.com/product/" || series_id || "/" || product_Id || "/"
end
when category_id = '7' then "https://www.dmm.co.jp/dc/doujin/-/detail/=/cid=" || product_Id || "/"
end as ShopURL
, category_name category_name
, "" "Publisher"
,"" Description
, product_Id
, "" ISBN
, purchase_date 'purchaseDate' -- purchase_date
FROM
my_library
WHERE
category_id IS NOT -1
ORDER BY
-- purchase_date desc , product_Id , sp_number
--- purchase_date, title_ruby , product_Id , sp_number
title_ruby,purchase_date, product_Id , sp_number
おまけ
powershell用 タブ区切りで出力
※powershellでSQLiteが使えるようにしておく。
booklist.ps1
#PowerShell -ExecutionPolicy RemoteSigned ./booklist.ps1
using namespace System.Windows.Forms
Import-Module "./System.Data.SQLite.dll"
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
[Application]::EnableVisualStyles()
$hontoDB = "C:\Users\xxxx\AppData\Local\DNP\honto\BookShelf.db"
$koboDB = "C:\Users\xxxx\AppData\Local\Kobo\Kobo Desktop Edition\Kobo.sqlite"
$dmmDB = "C:\Users\xxxx\AppData\Roaming\DMM\DMMbookviewer2\dmmbookshelf.sqlite3"
#--honto--------------------------------------------------
#書名,仮名,表紙,著者,(ReadLink),honto,販売ページ,カテゴリ,出版社,(説明),ID,ISBN,購入日L
$hontoSQL = "SELECT BM.bookTitle Title, BM.bookTitleKana kana, BC.thumbnailUrl Cover, BM.displayAuthors2 author
,'' AS ReadLink,'honto' eShop,ifnull(BM.purchaseUrl, 'https://honto.jp/ebook/pd_' || replace(substr(BC.thumbnailUrl,35,9),'/','') || '.html') ShopURL
,BM.ph2_category category_name, BM.publisher Publisher, '' Description, BC.bookId product_Id, BM.isbn ISBN, BC.purchaseDate
FROM BOOK_CONTENT_TBL AS BC, BOOK_COMMON_TBL AS BM
WHERE BC.bookId = BM.bookId and BC.purchaseFlag =1 and BC.downloadLimitDate='' and BC.userid <>''
ORDER BY bookTitlekana asc, purchaseDate desc, userId asc
-- purchaseDate desc, bookTitlekana asc, userId asc"
#--kobo--------------------------------------------------
#書名,(仮名),(表紙),著者,(ReadLink),kobo,販売ページ,(カテゴリ),出版社,説明,ID,ISBN,(購入日) --kobo
$koboSQL = "SELECT
quote(Title) Title -- Title
, '' kana
, '' Cover
, quote(Attribution) author
, '' ReadLink
, 'kobo' eShop
, 'https://books.rakuten.co.jp/rk/' || REPLACE(CrossRevisionId,'-','') || '/' ShopURL
, '' category_name
, quote(Publisher) Publisher -- Publisher
, quote(Description) Description
, REPLACE(CrossRevisionId,'-','') product_Id
, ISBN -- ISBN
, DateLastRead -- 購入日がないので最後に読んだ日
FROM
'content'
WHERE
ContentType = 6 and
Accessibility = 1 and
___UserID <> 'removed'
ORDER BY
-- ContentID, TitleKana
TitleKana, ContentID
-- ___SyncTime desc, TitleKana, ContentID "
#--dmm--------------------------------------------------
#書名,仮名,表紙,著者,(ReadLink),dmm,販売ページ,カテゴリ,(出版社),(説明),ID,(ISBN),購入日 --dmm
$dmmSQL = "SELECT
title 'Title'
, title_ruby kana
, image_src Cover
, main_author_name author
, "" ReadLink
,"dmm" eShop
, case
when category_id = '1' then "https://book.dmm.co.jp/detail/" || product_Id || "/"
when category_id = '2' then
case when series_id = '-1' then "https://book.dmm.com/detail/" || product_Id || "/"
else "https://book.dmm.com/product/" || series_id || "/" || product_Id || "/"
end
when category_id = '7' then "https://www.dmm.co.jp/dc/doujin/-/detail/=/cid=" || product_Id || "/"
end as ShopURL
, category_name category_name
, "" "Publisher"
,"" Description
, product_Id
, "" ISBN
, purchase_date 'purchaseDate' -- purchase_date
FROM
my_library
WHERE
category_id IS NOT -1
ORDER BY
-- purchase_date desc , product_Id , sp_number
--- purchase_date, title_ruby , product_Id , sp_number
title_ruby,purchase_date, product_Id , sp_number
##-------------------------------------------------------------------------
# フォーム
$frame = New-Object Form -Property @{
Text = '購入一覧作成'
Size = New-Object Drawing.Size(300, 250)
MaximizeBox = $false
FormBorderStyle = 'FixedDialog'
Font = New-Object Drawing.Font('Meiryo UI', 8.5)
StartPosition = 'CenterScreen'
}
$Button1 = New-Object Button -Property @{
Text = '実行'
Location = New-Object Drawing.Point(40, 150)
Size = New-Object Drawing.Size(200, 30)
DialogResult = "OK"
}
# ラジオボタン定義
$RadioButton1 = New-Object RadioButton -Property @{
Size = New-Object Drawing.Size(60, 17)
Text = 'honto'
Location = New-Object Drawing.Point(20, 25)
Checked = $True
}
$RadioButton2 = New-Object RadioButton -Property @{
Size = New-Object Drawing.Size(60, 17)
Text = 'kobo'
Location = New-Object Drawing.Point(20, 55)
}
$RadioButton3 = New-Object RadioButton -Property @{
Size = New-Object Drawing.Size(60, 17)
Text = 'dmmm'
Location = New-Object Drawing.Point(20, 85)
}
$GroupBox1 = New-Object GroupBox -Property @{
Text = '電子書店'
Location = New-Object Drawing.Point(20, 10)
Size = New-Object Drawing.Size(120, 120)
}
$GroupBox1.Controls.AddRange(@($RadioButton1, $RadioButton2, $RadioButton3))
$frame.Controls.Add($GroupBox1)
$frame.Controls.Add($Button1)
$result = $frame.ShowDialog()
if ($result -ne "OK") { exit }
if ($RadioButton1.Checked){
$dbFile=$hontoDB
$textSQL=$hontoSQL
$out_file="honto.txt"
}
if ($RadioButton2.Checked){
$dbFile=$koboDB
$textSQL=$koboSQL
$out_file="kobo.txt"
}
if ($RadioButton3.Checked){
$dbFile=$dmmDB
$textSQL=$dmmSQL
$out_file="dmm.txt"
}
Out-File $out_file
#--------------------------------------------------------------------------
$connectionString = "Data Source=$dbFile"
$connection = New-Object System.Data.SQLite.SQLiteConnection($connectionString)
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText=$textSQL
$reader = $command.ExecuteReader()
while ($reader.Read())
{
$FC = $reader.FieldCount -1
$csv=$reader[0..$FC] -join "`t"
Write-Output $csv
Write-Output $csv | Out-File -Append $out_file
}
$connection.Close()