メモ: SONY Reader T2 のデータベース #

CREATE TABLE android_metadata (locale TEXT);

CREATE TABLE annotation (_id INTEGER PRIMARY KEY AUTOINCREMENT,content_id INTEGER,markup_type INTEGER DEFAULT 10,added_date INTEGER,modified_date INTEGER,name TEXT,marked_text TEXT,mark BLOB,mark_end BLOB,page DOUBLE,total_page INTEGER,mime_type TEXT,file_path TEXT);

CREATE TABLE bookmark (_id INTEGER PRIMARY KEY AUTOINCREMENT,font_size INTEGER,mark BLOB,mark_end BLOB,page DOUBLE,total_page INTEGER,page_style INTEGER,page_style_index INTEGER,crop_mode INTEGER,crop_left INTEGER,crop_top INTEGER,crop_right INTEGER,crop_bottom INTEGER,orientation INTEGER,content_id INTEGER,markup_type INTEGER DEFAULT 0,added_date INTEGER,modified_date INTEGER,name TEXT,marked_text TEXT,mime_type TEXT,file_path TEXT);

CREATE TABLE books (_id INTEGER PRIMARY KEY AUTOINCREMENT,title TEXT,author TEXT,kana_title TEXT,kana_author TEXT,title_key TEXT,author_key TEXT,source_id INTEGER,added_date INTEGER,modified_date INTEGER,reading_time INTEGER,purchased_date INTEGER,file_path TEXT,file_name TEXT,file_size INTEGER,thumbnail TEXT,mime_type TEXT,corrupted INTEGER,expiration_date INTEGER,prevent_delete INTEGER,sony_id TEXT,periodical_name TEXT,kana_periodical_name TEXT,periodical_name_key TEXT,publication_date INTEGER,conforms_to TEXT,description TEXT,logos TEXT);

CREATE TABLE collection (_id INTEGER PRIMARY KEY AUTOINCREMENT,title TEXT,kana_title TEXT,source_id INTEGER,uuid TEXT);

CREATE TABLE collections (_id INTEGER PRIMARY KEY AUTOINCREMENT,collection_id INTEGER,content_id INTEGER,added_order INTEGER);

CREATE TABLE current_position (_id INTEGER PRIMARY KEY AUTOINCREMENT,content_id INTEGER,dpi INTEGER,width INTEGER,height INTEGER,version INTEGER,mark BLOB,split_index INTEGER,page_style INTEGER,crop_mode INTEGER,crop_area_left INTEGER,crop_area_top INTEGER,crop_area_right INTEGER,crop_area_bottom INTEGER,font_size INTEGER,reflow INTEGER,font_style TEXT,orientation INTEGER,text_encoding TEXT);

CREATE TABLE deleted_markups (_id INTEGER PRIMARY KEY AUTOINCREMENT,content_id INTEGER,markup_type INTEGER,added_date INTEGER,name TEXT,mark BLOB,mark_end BLOB);

CREATE TABLE dic_histories (_id INTEGER PRIMARY KEY AUTOINCREMENT,content_id INTEGER,dic_content_id INTEGER,dic_content_name TEXT NOT NULL,dic_searchword TEXT NOT NULL,dic_search_no INTEGER,added_date TEXT NOT NULL);

CREATE TABLE freehand (_id INTEGER PRIMARY KEY AUTOINCREMENT,font_style TEXT,font_size INTEGER,mark BLOB,mark_end BLOB,page DOUBLE,total_page INTEGER,page_style INTEGER,page_style_index INTEGER,crop_mode INTEGER,crop_left INTEGER,crop_top INTEGER,crop_right INTEGER,crop_bottom INTEGER,orientation INTEGER,text_encoding TEXT,content_id INTEGER,markup_type INTEGER DEFAULT 20,added_date INTEGER,modified_date INTEGER,name TEXT,svg_file TEXT,thumbnail TEXT);

CREATE TABLE freehand_layout (_id INTEGER PRIMARY KEY AUTOINCREMENT,content_id INTEGER,markup_id INTEGER,version INTEGER,page_width INTEGER,page_height INTEGER);

CREATE TABLE history (_id INTEGER PRIMARY KEY AUTOINCREMENT,content_id INTEGER,dpi INTEGER,width INTEGER,height INTEGER,version INTEGER,added_counter INTEGER,reading_time INTEGER,mark BLOB,split_index INTEGER,page_style INTEGER,crop_mode INTEGER,crop_area_left INTEGER,crop_area_top INTEGER,crop_area_right INTEGER,crop_area_bottom INTEGER,font_size INTEGER,reflow INTEGER,font_style TEXT,orientation INTEGER,text_encoding TEXT);

CREATE TABLE layout_cache (_id INTEGER PRIMARY KEY AUTOINCREMENT,content_id INTEGER,dpi INTEGER,width INTEGER,height INTEGER,font_size INTEGER,reflow INTEGER,font_style TEXT,encoding TEXT,state INTEGER,file_path TEXT,layout_version INTERGER);

CREATE TABLE network_markups (_id INTEGER PRIMARY KEY AUTOINCREMENT,content_id INTEGER,markup_id INTEGER,network_id TEXT,markup_type INTEGER,svc_create_date INTEGER,svc_modify_date INTEGER,title TEXT,position_owner TEXT,position_start TEXT,position_end TEXT,client_update_date INTEGER,client_delete_date INTEGER);

CREATE TABLE network_position (_id INTEGER PRIMARY KEY AUTOINCREMENT,content_id INTEGER,network_id TEXT,type TEXT,client_create_date INTEGER,svc_create_date INTEGER,position TEXT,percent DOUBLE);

CREATE TABLE preference (_id INTEGER PRIMARY KEY AUTOINCREMENT,content_id INTEGER NOT NULL,tone_curve_type TEXT,contrast INTEGER,brightness INTEGER,show_notes INTEGER,binding_direction TEXT);

CREATE VIEW collections_view AS SELECT content_id as _id,content_id,collection_id,added_order,title,author,kana_title,kana_author,source_id,added_date,modified_date,reading_time,purchased_date,file_path,file_name,file_size,thumbnail,mime_type,corrupted,expiration_date,prevent_delete,sony_id,periodical_name,conforms_to FROM collections LEFT OUTER JOIN books ON (collections.content_id = books._id);

CREATE VIEW freehand_view AS SELECT * FROM freehand LEFT OUTER JOIN (SELECT markup_id AS _id,content_id AS content_id,version,page_width,page_height FROM freehand_layout) USING (_id,content_id);

CREATE VIEW markups AS SELECT a._id _id,a.content_id content_id,a.markup_type markup_type,a.added_date added_date,a.modified_date modified_date,trim(a.name) name,a.page page,a.total_page total_page,a.mark mark,a.file_path file1,null file2,b.file_name file_name,b.title title,b.author author,b.kana_title kana_title,b.kana_author kana_author,b.periodical_name periodical_name,b.publication_date publication_date,b.mime_type mime_type,b.source_id source_id FROM bookmark a JOIN books b ON b._id=a.content_id UNION ALL SELECT a._id _id,a.content_id content_id,a.markup_type markup_type,a.added_date added_date,a.modified_date modified_date,trim(a.name) name,a.page page,a.total_page total_page,a.mark mark,a.file_path file1,null file2,b.file_name file_name,b.title title,b.author author,b.kana_title kana_title,b.kana_author kana_author,b.periodical_name periodical_name,b.publication_date publication_date,b.mime_type mime_type,b.source_id source_id FROM annotation a JOIN books b ON b._id=a.content_id UNION ALL SELECT a._id _id,a.content_id content_id,a.markup_type markup_type,a.added_date added_date,a.modified_date modified_date,trim(a.name) name,a.page page,a.total_page total_page,a.mark mark,a.svg_file file1,a.thumbnail file2,b.file_name file_name,b.title title,b.author author,b.kana_title kana_title,b.kana_author kana_author,b.periodical_name periodical_name,b.publication_date publication_date,b.mime_type mime_type,b.source_id source_id FROM freehand a JOIN books b ON b._id=a.content_id;

CREATE VIEW periodicals AS SELECT DISTINCT * FROM (SELECT DISTINCT periodical_name,MAX(publication_date) AS publication_date,kana_periodical_name,CASE WHEN COUNT(reading_time)=COUNT(*) THEN MAX(reading_time) ELSE NULL END AS reading_time,MAX(reading_time) AS reading_time,COUNT(periodical_name) AS _count FROM books GROUP BY periodical_name, conforms_to HAVING (periodical_name NOT NULL AND conforms_to LIKE 'http://xmlns.sony.net/e-book/prs/periodicals/%')) AS g LEFT JOIN (SELECT periodical_name,publication_date,kana_periodical_name,_id,source_id,thumbnail,periodical_name_key,logos FROM books GROUP BY periodical_name, conforms_to, publication_date HAVING (periodical_name NOT NULL AND conforms_to LIKE 'http://xmlns.sony.net/e-book/prs/periodicals/%')) AS b ON((g.periodical_name = b.periodical_name)  AND ((g.publication_date = b.publication_date)  OR (g.publication_date IS NULL AND b.publication_date IS NULL) ));

CREATE VIEW uncollections AS SELECT _id,title,author,kana_title,kana_author,source_id,added_date,modified_date,reading_time,purchased_date,file_path,file_name,file_size,thumbnail,mime_type,corrupted,expiration_date,prevent_delete,sony_id,periodical_name,conforms_to FROM books WHERE  NOT EXISTS(SELECT 1 FROM collections  WHERE content_id=books._id );

CREATE INDEX collections_index ON collections(content_id);

CREATE TRIGGER annotation_cleanup DELETE ON annotation BEGIN INSERT INTO deleted_markups (content_id,markup_type,added_date,name,mark,mark_end) VALUES (old.content_id,old.markup_type,strftime('%s', 'now')*1000,old.name,old.mark,old.mark_end);UPDATE network_markups SET client_delete_date=strftime('%s', 'now')*1000 WHERE (content_id= old.content_id AND markup_id= old._id AND (markup_type)/10= (old.markup_type)/10);END;

CREATE TRIGGER bookmark_cleanup DELETE ON bookmark BEGIN INSERT INTO deleted_markups (content_id,markup_type,added_date,name,mark) VALUES (old.content_id,old.markup_type,strftime('%s', 'now')*1000,old.name,old.mark);UPDATE network_markups SET client_delete_date=strftime('%s', 'now')*1000 WHERE (content_id= old.content_id AND markup_id= old._id AND (markup_type)/10= (old.markup_type)/10);END;

CREATE TRIGGER books_cleanup DELETE ON books BEGIN DELETE FROM preference WHERE content_id = old._id;DELETE FROM current_position WHERE content_id = old._id;DELETE FROM layout_cache WHERE content_id = old._id;DELETE FROM history WHERE content_id = old._id;DELETE FROM bookmark WHERE content_id = old._id;DELETE FROM annotation WHERE content_id = old._id;DELETE FROM freehand WHERE content_id = old._id;DELETE FROM collections WHERE content_id = old._id;DELETE FROM deleted_markups WHERE content_id = old._id;DELETE FROM network_position WHERE content_id = old._id;DELETE FROM network_markups WHERE content_id = old._id;END;

CREATE TRIGGER collections_cleanup DELETE ON collection BEGIN DELETE FROM collections WHERE collection_id = old._id;END;

CREATE TRIGGER freehand_cleanup DELETE ON freehand BEGIN INSERT INTO deleted_markups (content_id,markup_type,added_date,name,mark) VALUES (old.content_id,old.markup_type,strftime('%s', 'now')*1000,old.name,old.mark);UPDATE network_markups SET client_delete_date=strftime('%s', 'now')*1000 WHERE (content_id= old.content_id AND markup_id= old._id AND (markup_type)/10= (old.markup_type)/10);END;

CREATE TRIGGER freehand_layout_cleanup DELETE ON freehand BEGIN DELETE FROM freehand_layout WHERE markup_id = old._id;END;