User:Hitspacebar

A very brief fact list about me

• From Germany.
• Avid reader of speculative fiction since childhood, mostly Science Fiction, sometimes Fantasy, Magic Realism etc. and very rarely Horror.
• Sometimes reads English books in English, somtimes reads the German translation - depending an author, current mood and availability.
• Started editing here because I saw an error in a publication, created an account to correct that and then couldn't stop myself adding and editing. :)

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

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. One should create one book record in the app, export that to a CSV file and compare it to the described CSV format.

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

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 ''
    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 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 series_details,
  pubs.pub_pages pages,
  null as 'notes',
  null as 'list_price',
  case 
    when pubs.pub_ctype = 'ANTHOLOGY' then '1'
    else '0'
  end 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 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 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.

• 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.