User:Hitspacebar/BookCatalogueAppExport

From ISFDB

Jump to: navigation, search

Contents

One-time export of primary verified records to Book Catalogue app for Android

This describes a way to do a one-time export of your primary verified records to a CSV file which can be imported by the Book Catalogue Android app (as of November 2015 and Book Catalogue 5.1.2). This might not work for later versions of ISFDB or Book Catalogue.

It's a one-time export, nothing you can do repeatedly to update your app data with later primary verifications.

Data export

Note that the import/export format of Book Catalogue described on its web site might not be up-to-date.

Get your user id

First get your user id: search one of the publications you primary verified as "Primary" (not "Primary2" etc.) and replace the "-1" in the SQL below with the id of that publication: 

SELECT v.user_id FROM pubs p, verification v
WHERE v.reference_id = 1
AND v.pub_id = p.pub_id
AND p.pub_id = -1;

Create the CSV file

Note that this SQL has been written with my primary verified records in mind and does not necessarily export all of your primary verified records (e.g. SERIALs are missing here) and is localized to GERMAN in some parts. Your votes are not exported because the backup dumps don't contain them.

Replace the "-1" for the user_id below (above the ORDER BY) with your user id. 

SELECT   
  NULL AS '_id',
  (
    SELECT 
     GROUP_CONCAT(
      CONCAT(
        a.author_lastname, ', ', TRIM(SUBSTRING_INDEX(a.author_canonical, a.author_lastname, 1))
      ) 
      SEPARATOR '|'
    )
    FROM authors a, pub_authors pa 
    WHERE pubs.pub_id = pa.pub_id
    AND a.author_id = pa.author_id
  ) author_details,
  pubs.pub_title title, 
  pubs.pub_isbn isbn, 
  pb.publisher_name publisher, 
  case
    when pubs.pub_year = '0000-00-00' then NULL
    when extract(month FROM pubs.pub_year) = 0 then SUBSTRING(pubs.pub_year, 1, 4)
    when extract(day FROM pubs.pub_year) = 0 then SUBSTRING(pubs.pub_year, 1, 7)
    else pubs.pub_year 
  end AS 'date_published',
  '0' AS 'rating',
  '1,' AS 'bookshelf_id',
  'Default,' AS 'bookshelf',
  '0' AS 'read',
  case 
    when ps.pub_series_name IS NOT NULL AND pubs.pub_series_num IS NOT NULL then CONCAT(ps.pub_series_name, ' (',  pubs.pub_series_num, ')')
    else ps.pub_series_name
  end AS 'series_details',
  pubs.pub_pages pages,
  NULL AS 'notes',
  NULL AS 'list_price',
  case 
    when pubs.pub_ctype = 'ANTHOLOGY' then '1'
    else '0'
  end AS 'anthology',
  NULL AS 'location',
  NULL AS 'read_start',
  NULL AS 'read_end',
  case 
    when pubs.pub_ptype = 'tp' then 'Paperback'
    when pubs.pub_ptype = 'pb' then 'Taschenbuch'
    when pubs.pub_ptype = 'hc' then 'Hardcover'
    when pubs.pub_ptype = 'digest' then 'Heft'
    else pubs.pub_ptype
  end AS 'format',
  '0' AS 'signed',
  NULL AS 'loaned_to',
  NULL AS 'anthology_files',
  NULL AS 'description',
  NULL AS 'genre',
  case 
    when l.lang_name = 'German' then 'Deutsch'
    when l.lang_name = 'English' then 'Englisch'
    else l.lang_name
  end AS 'language', 
  convert(v.ver_time, DATE) AS 'date_added',
  NULL AS 'goodreads_book_id',
  NULL AS 'last_goodreads_sync_date',
  NULL AS 'last_update_date',
  NULL AS 'book_uuid'
FROM verification v
INNER JOIN pubs ON pubs.pub_id = v.pub_id
INNER JOIN pub_content pc ON pubs.pub_id = pc.pub_id
INNER JOIN titles t ON t.title_id = pc.title_id
LEFT OUTER JOIN languages l ON t.title_language = l.lang_id
LEFT OUTER JOIN publishers pb ON pb.publisher_id = pubs.publisher_id
LEFT OUTER JOIN pub_series ps ON ps.pub_series_id = pubs.pub_series_id
WHERE pubs.pub_ctype IN ('COLLECTION', 'ANTHOLOGY', 'MAGAZINE', 'NOVEL', 'CHAPBOOK', 'OMNIBUS', 'NONFICTION')
AND ((t.title_ttype = pubs.pub_ctype) OR (t.title_ttype = 'EDITOR' AND pubs.pub_ctype = 'MAGAZINE'))
AND v.reference_id IN (1, 15, 16, 17, 18)
AND v.ver_status = 1
AND v.user_id = -1
ORDER BY pubs.pub_title
INTO OUTFILE '/tmp/isfdb_export.csv'
CHARACTER SET 'utf8'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Data post-processing

Some data in the CSV file needed to be adjusted in order to be understood correctly by Book Catalogue.

  • Add one book manually to Book Catalogue and do an export there. Extract the header (first line) from that CSV file created by Book Catalogue and add it as the first line to the ISFDB export file. I think the header is needed and it's also a good way to see that data and column name match.
  • Replace all \N values (that's not a newline character but meant verbatim) with "" (two double quotes).
  • Add a comma at the end of each row except for the last row.
Retrieved from "http://www.isfdb.org/wiki/index.php/User:Hitspacebar/BookCatalogueAppExport"