Register Guidelines E-Books Search Today's Posts Mark Forums Read

Go Back   MobileRead Forums > E-Book Readers > Sony Reader

Notices

Reply
 
Thread Tools Search this Thread
Old 10-07-2013, 03:30 PM   #1
Gozer404
Member
Gozer404 began at the beginning.
 
Gozer404's Avatar
 
Posts: 12
Karma: 10
Join Date: Oct 2013
Location: France
Device: SONY PRS-T2
Lost 'date added' after a calibre transfer

With version 1.3 of calibre that I was using for managing my PRS-T2, during a transfer I got a problem with the USB link. After restarting the device, all dates inside the PRS-T2 were set to the current date (BTW I also lost the read/unread flag!)

Is is possible to update this date (date the epub was added - sorry I don't know the exact words in the Sony interface, because mine is in french) to be in sync with the Calibre library date ? A plugin somewhere ?

Gozer404 is offline   Reply With Quote
Old 10-23-2013, 10:06 AM   #2
Gozer404
Member
Gozer404 began at the beginning.
 
Gozer404's Avatar
 
Posts: 12
Karma: 10
Join Date: Oct 2013
Location: France
Device: SONY PRS-T2
No one on the forum ?
Gozer404 is offline   Reply With Quote
 
Enthusiast
Old 10-24-2013, 05:25 PM   #3
ebmr
Connoisseur
ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.
 
Posts: 98
Karma: 34554
Join Date: Aug 2012
Device: none
Quote:
Originally Posted by Gozer404 View Post
Is is possible to update this date (date the epub was added
Yes, by directly changing it in the books.db database using SQLite.

But what date do you want to set it to? Do you you remember all the dates of your books?
ebmr is offline   Reply With Quote
Old 11-01-2013, 12:16 PM   #4
Gozer404
Member
Gozer404 began at the beginning.
 
Gozer404's Avatar
 
Posts: 12
Karma: 10
Join Date: Oct 2013
Location: France
Device: SONY PRS-T2
Yes, the idea is to use the same date as in the calibre database. In general I enter some books in calibre, do some tweaking on the tags/covers and copy to the Sony the same day.

Now I imagine that I can use some SQL magic to update the sony database based on the calibre one, but I'm far from having the knowledge !

I had some tries with cygwin's sqlite3 and SQLite expert, but I really don't know how to do it.
Gozer404 is offline   Reply With Quote
Old 11-01-2013, 02:17 PM   #5
ebmr
Connoisseur
ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.ebmr is out to avenge the death of his or her father, Domingo Montoya.
 
Posts: 98
Karma: 34554
Join Date: Aug 2012
Device: none
Well, the single steps for a distinct book named book.epub are:
  • Get the date for book.epub, let's say 2010-05-01 14:10:00.
  • Convert the date into seconds since 1970-01-01 00:00:00 UTC by (e.g.)
    Code:
    date --date="2010-05-01 14:10:00" +"%s"
  • Add 000 to the result and get 1272715800000.
  • Perform SQL on books.db:
    Code:
    update books set added_date=1272715800000 where file_name=book.epub;
Try to make that a batch and backup your database before.
ebmr is offline   Reply With Quote
Old 11-11-2013, 01:01 PM   #6
Gozer404
Member
Gozer404 began at the beginning.
 
Gozer404's Avatar
 
Posts: 12
Karma: 10
Join Date: Oct 2013
Location: France
Device: SONY PRS-T2
Fine thanks. Based on this I created a bash script for cygwin:
I did'nt debug all error cases, in particular some grep results are not correctly filtered


Code:
#!/bin/bash

# Create book.txt from sony reader database: sqlite3.exe books.db 'select author,title,added_date from books' > book.txt
# Create reference calibre.txt from calibre database: sqlite3.exe  metadata.db 'select author_sort,title,timestamp from books' > calibre.txt
#
#

N=0
cat book.txt | while read LINE ; do
        N=$((N+1))
        echo "Line $N = $LINE"
        FOUND=""
        BTITLE=`echo "$LINE" | awk -F'|' '{print $2}'`
        BDAT=`echo "$LINE" | awk -F'|' '{print $3}'`
        # Search title in calibre file
        FOUND=`grep "$BTITLE" calibre.txt`
        if [ $? -eq 0 ]
        then
                # Create date based on calibre date
                CDAT1=`echo "$FOUND" | awk -F'|' '{print $3}'`
                CTITLE=`echo "$FOUND" | awk -F'|' '{print $2}'`
                CDAT2=`date --date="$CDAT1" +"%s"`
                CDAT="${CDAT2}000"
                if [ x"${BDAT}" != x"${CDAT}" ]
                then
                        echo "Line $N = Change \"$BTITLE\" status date to ${CDAT}"
                        # Update sony datebase with this date
                        sqlite3 books.db "update books set added_date=$CDAT where title=\"$CTITLE\";"
                fi
        fi
done
Gozer404 is offline   Reply With Quote
Reply

Tags
date added

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Not Sorting by Date Added? Enriana Calibre 9 01-19-2013 11:21 AM
Date Added column? Can I set date when book added to library? fitzhugh Calibre 4 06-03-2012 05:42 PM
Date Added changing lgladen Calibre 5 04-14-2011 09:25 AM
Date Added vs. Date Modified aglaia761 Calibre 5 11-28-2010 05:34 AM
Search on Added Date nynaevelan Calibre 6 11-10-2010 09:15 PM


All times are GMT -4. The time now is 08:39 AM.


MobileRead.com is a privately owned, operated and funded community.