メモ: SONY Reader T2 のデータベース
メモ: SONY Reader T2 のデータベース # あとで書くかもしれない とりあえず、さわりがいがありそうな books.db だけ、schema 貼っとく books.db # 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;