PRS-T1 soften the 500 notes limit

[spitfire_ch]

Some people tend to markup like 50% of a book while studying it - I am one of them. Obviously, I wasn't exactly enthusiastic when I found out about the 500 notes limit (I reached it after 100 out of 600 pages).

To get around this ridiculous limit, I tried to write some triggers in SQLite. They allow you to set special bookmarks, containing the text on or off. If you set an 'on' bookmark, all notes out of range of the bookmark (-30 to +20 pages) will be "masked" (assigned to a non existing book). So you can easily add new notes. When you set an 'off' bookmark, all notes will be unmasked again.

It still isn't perfect, but does the job for me. At least I can continue my studies without having to by the physical book (in addition) and highlight all the passages again.

Here is the code, including some more detailed description and installation guide:

/* Trigger set to soften the 500 notes limit
 * =========================================
 * v 0.1.0, 2012-05-04
 * 
 * Installation: (no rooting required!)
 * —————————————
 * • Connect your reader and open the drive named 'READER'
 * • Create a backup copy of \Sony_Reader\database
 * • Get a SQLLite Manager. Example: SQLite Manager AddOn for Firefox:
 *   http://code.google.com/p/sqlite-manager/
 * • Start the manager and open \Sony_Reader\database\books.db (on the READER drive)
 * • Copy paste the script at hand into the 'Excecute SQL' tab and hit RUN SQL
 * --> if no error shows up in 'Last Error' you are done :)
 * • Close the database and deconnect your reader.
 * • If anything goes wrong, you can use your backup to undo the changes (or simply drop the triggers)
 *
 * Usage:
 * ——————
 * • Activate edit mode:
 *   Add or update bookmark (by keyboard) containing a memo 'on' (without '')
 *   --> This masks all notes more than 30 pages before the bookmark and 20 pages after the bookmark,
 *	     allowing you to add new notes
 *   Exit the book (home key) and reopen it
 *
 * • Activate reading mode:
 *   Add or update bookmark (by keyboard) containing a memo 'off' (without '')
 *   --> This restores all previously masked notes
 *   Exit the book (home key) and reopen it
 *
 * Restrictions/Bugs:
 * —————————————-----
 * • If you have already reached the limit, you cannot add a new bookmark. Change an existing bookmark to 'on', instead.
 *   If the error does not go away, simply click the home button and reopen the book.
 *   If you don't have an existing bookmark and have crossed the limit already, you will have to delete enough notes to get below the limit
 *   or use the SQL-Lite manager to manually mask the existing notes. Feel free to ask for help.
 * • To mask the notes, 100000 is added to the content_id (book number). If you have 100000 books installed, this won't work.
 * • The effect on synchronization using 'Reader for PC' has NOT been tested. It is possible, that it will remove the masked notes.
 * 	 --> Always unmask the notes before synchronization (using the off bookmark) !!!
 * • Old on/off bookmarks should be removed when adding a new one, but this does not always work --> manually clean them up
 */



-- === Delete the triggers, if they exist already === --
DROP TRIGGER IF EXISTS markup_limit_bypass_ON_I;
DROP TRIGGER IF EXISTS markup_limit_bypass_ON_U;
DROP TRIGGER IF EXISTS markup_limit_bypass_OFF_I;
DROP TRIGGER IF EXISTS markup_limit_bypass_OFF_U;


-- === Trigger fires, when new bookmark named 'on' is added --> "masks" all notes outside of the range of the bookmark === --
CREATE TRIGGER markup_limit_bypass_ON_I AFTER INSERT ON bookmark
FOR EACH ROW
WHEN new.name IN ('on', 'ON', 'On')
BEGIN
	-- first, restore all notes
	UPDATE annotation
	SET content_id = content_id-100000
	WHERE content_id = new.content_id+100000;
	
	-- delete previous on/off bookmarks (does not work, why???)
	DELETE
	FROM bookmark
	WHERE content_id = new.content_id
	AND name IN ('on', 'ON', 'On', 'off', 'OFF', 'Off')
	AND _id <> new._id;
	
	-- assign all out of range notes to new content_id (range: -30 to +20 of current page)
	UPDATE annotation
	SET content_id = content_id+100000
	WHERE content_id = new.content_id
	AND (page <= new.page-30 OR page >= new.page+20)
	AND markup_type = 10;
END;


-- === Trigger fires, when existing bookmark name is changed to 'on' --> "masks" all notes outside of the range of the bookmark === --
CREATE TRIGGER markup_limit_bypass_ON_U AFTER UPDATE ON bookmark
FOR EACH ROW
WHEN new.name IN ('on', 'ON', 'On')
BEGIN
	-- first, restore all notes
	UPDATE annotation
	SET content_id = content_id-100000
	WHERE content_id = new.content_id+100000;

	-- assign all out of range notes to new content_id (range: -30 to +20 of current page)
	UPDATE annotation
	SET content_id = content_id+100000
	WHERE content_id = new.content_id
	AND (page <= new.page-30 OR page >= new.page+20)
	AND markup_type = 10;
END;


-- === Trigger fires, when new bookmark named 'off' is added --> unmasks all notes === --
CREATE TRIGGER markup_limit_bypass_OFF_I AFTER INSERT ON bookmark
FOR EACH ROW
WHEN new.name IN ('off', 'OFF', 'Off')
BEGIN
	-- delete previous on/off bookmarks (does not work, why???)
	DELETE
	FROM bookmark
	WHERE content_id = new.content_id
	AND name IN ('on', 'ON', 'On', 'off', 'OFF', 'Off')
	AND _id <> new._id;

	-- restore all notes
	UPDATE annotation
	SET content_id = content_id-100000
	WHERE content_id = new.content_id+100000;
END;


-- === Trigger fires, when existing bookmark name is changed to 'off'--> unmasks all notes === --
CREATE TRIGGER markup_limit_bypass_OFF_U AFTER UPDATE ON bookmark
FOR EACH ROW
WHEN new.name IN ('off', 'OFF', 'Off')
BEGIN
	-- restore all notes
	UPDATE annotation
	SET content_id = content_id-100000
	WHERE content_id = new.content_id+100000;
END;

Have fun studying
- spitfire_ch

P.S. Help is always welcome:

Problem 1:
If anybody knows how to fix the following passage, I'd be really glad for your help!

	-- delete previous on/off bookmarks (does not work, why???)
	DELETE
	FROM bookmark
	WHERE content_id = new.content_id
	AND name IN ('on', 'ON', 'On', 'off', 'OFF', 'Off')
	AND _id <> new._id;

Problem 2
Originally, I planned to create some kind of sliding window. Meaning, all the notes out of region of the current page would be masked. As you turn pages, the window would move along. Not setting of bookmarks would be required. This solution would be way more elegant.

I tried to use the table current_position. Unfortunately, it does not contain a column indicating the current page. Instead, the position is stored in a blob column named mark.

I do not know how to extract data from a blob in SQLite. Even when I export the blob, I can't figure out how to extract the current page number from it. If anybody has an idea, please share your thoughts!

Thank you very much!

[FreeSReader]

Hi spitfire_ch,
I've the same problem, and I spent the last two days to find another solution: to modify the limit in the EbookReader source code. Here is a version where the limit is 8192 annotations. Enjoy

[spitfire_ch]

Originally Posted by FreeSReader

Hi spitfire_ch,
I've the same problem, and I spent the last two days to find another solution: to modify the limit in the EbookReader source code. Here is a version where the limit is 8192 annotations. Enjoy

Wow, that's amazing! Thank you very much for sharing!

Where did you get the source code from? That opens totally different possibilities than just tweaking the database.

Is there any way to install an android package without rooting the reader? Mine is still "unrooted" so far, but your modified app might be a reason to change that ...

Best regards and thanks!!
- spit

[medard]

That would be amazing for the T2, too.. I didn't even know that there is a 500 notes limit, we have the collected works of Nietzsche with 7'375 pages now for example, and 500 notes wouldn't be enough..