Difference between revisions of "User:Hitspacebar"

From ISFDB
Jump to navigation Jump to search
 
(4 intermediate revisions by the same user not shown)
Line 6: Line 6:
 
* 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. :)
 
* 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 =
+
= Subpages =
  
−
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 [https://github.com/eleybourn/Book-Catalogue 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.
+
{{Special:Prefixindex/User:Hitspacebar}}
−
 
 
−
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 [https://github.com/eleybourn/Book-Catalogue/wiki/Export-Import-Format 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:
 
−
 
 
−
<source lang="sql">
 
−
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;
 
−
</source>
 
−
 
 
−
=== 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.
 
−
 
 
−
Replace the "-1" for the user_id below (above the ORDER BY) with your user id.
 
−
 
 
−
<source lang="sql">
 
−
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';
 
−
</source>
 
−
 
 
−
== 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.
 

Latest revision as of 09:58, 26 November 2016

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. :)

Subpages

Retrieved from "https://isfdb.org/wiki/index.php?title=User:Hitspacebar&oldid=452265"