CREATE TABLE DbVersion( version INTEGER NOT NULL, PRIMARY KEY(version));
CREATE TABLE content( 		ContentID TEXT NOT NULL, 		ContentType TEXT NOT NULL, 		MimeType TEXT NOT NULL,		BookID TEXT,		BookTitle TEXT,		ImageId TEXT,		Title TEXT COLLATE NOCASE,		Attribution TEXT COLLATE NOCASE,		Description TEXT,		DateCreated TEXT,		ShortCoverKey TEXT,		adobe_location TEXT,		Publisher TEXT,		IsEncrypted BOOL,		DateLastRead TEXT,		FirstTimeReading BOOL,		ChapterIDBookmarked TEXT,		ParagraphBookmarked INTEGER,		BookmarkWordOffset INTEGER,		NumShortcovers INTEGER,		VolumeIndex INTEGER,		___NumPages INTEGER,		ReadStatus INTEGER,		___SyncTime TEXT,		___UserID TEXT NOT NULL, PublicationId TEXT, ___FileOffset INTEGER, ___FileSize INTEGER, ___PercentRead INTEGER, ___ExpirationStatus INTEGER, FavouritesIndex NOT NULL DEFAULT -1, Accessibility INTEGER DEFAULT 1, ContentURL TEXT, Language TEXT, BookshelfTags TEXT, IsDownloaded BIT NOT NULL DEFAULT 1, FeedbackType INTEGER DEFAULT 0, AverageRating INTEGER DEFAULT 0, Depth INTEGER, PageProgressDirection TEXT, InWishlist BOOL NOT NULL DEFAULT FALSE, ISBN TEXT, WishlistedDate TEXT DEFAULT "0000-00-00T00:00:00.000", FeedbackTypeSynced INTEGER DEFAULT 0, IsSocialEnabled BOOL NOT NULL DEFAULT TRUE, EpubType INT NOT NULL DEFAULT -1, Monetization INTEGER DEFAULT 2, ExternalId TEXT, Series TEXT, SeriesNumber TEXT, Subtitle TEXT, WordCount INTEGER DEFAULT -1, Fallback TEXT, RestOfBookEstimate INTEGER, CurrentChapterEstimate INTEGER, CurrentChapterProgress FLOAT, PocketStatus INTEGER DEFAULT 0, UnsyncedPocketChanges TEXT, ImageUrl TEXT, DateAdded TEXT, WorkId TEXT, Properties TEXT, RenditionSpread TEXT, RatingCount INTEGER DEFAULT 0, ReviewsSyncDate TEXT, MediaOverlay TEXT, MediaOverlayType TEXT, RedirectPreviewUrl TEXT, PreviewFileSize INTEGER, EntitlementId TEXT, CrossRevisionId TEXT, DownloadUrl TEXT, ReadStateSynced BIT NOT NULL DEFAULT false, TimesStartedReading INTEGER, TimeSpentReading INTEGER, LastTimeStartedReading TEXT, LastTimeFinishedReading TEXT, ApplicableSubscriptions TEXT, ExternalIds TEXT, PurchaseRevisionId TEXT, SeriesID TEXT, SeriesNumberFloat REAL, AdobeLoanExpiration TEXT, HideFromHomePage bit, IsInternetArchive BOOL NOT NULL DEFAULT FALSE, titleKana TEXT, subtitleKana TEXT, seriesKana TEXT, attributionKana TEXT, publisherKana TEXT, IsPurchaseable BOOL DEFAULT TRUE, IsSupported BOOL DEFAULT TRUE,		PRIMARY KEY (ContentID)		);
CREATE TABLE shortcover_page ( 		shortcoverId TEXT not null, 		PageNumber INTEGER, 		FormattedPage TEXT, 		PRIMARY KEY (shortcoverId, PageNumber));
CREATE TABLE volume_shortcovers(		volumeId TEXT NOT NULL,		shortcoverId TEXT NOT NULL,		VolumeIndex INTEGER,		PRIMARY KEY (volumeId, shortcoverId));
CREATE TABLE content_keys ( volumeId TEXT NOT NULL, elementId TEXT NOT NULL, elementKey TEXT, PRIMARY KEY (volumeId, elementId));
CREATE TABLE Bookmark ( BookmarkID TEXT NOT NULL, VolumeID TEXT NOT NULL, ContentID TEXT NOT NULL, StartContainerPath TEXT NOT NULL, StartContainerChildIndex INTEGER NOT NULL, StartOffset INTEGER NOT NULL, EndContainerPath TEXT NOT NULL, EndContainerChildIndex INTEGER NOT NULL, EndOffset INTEGER NOT NULL, Text TEXT, Annotation TEXT, ExtraAnnotationData BLOB, DateCreated TEXT, ChapterProgress REAL NOT NULL DEFAULT 0, Hidden BOOL NOT NULL DEFAULT 0, Version TEXT, DateModified TEXT, Creator TEXT, UUID TEXT, UserID TEXT, SyncTime TEXT, Published BIT default false, PRIMARY KEY (BookmarkID) );
CREATE TABLE Event ( 
	EventType INTEGER NOT NULL,
	FirstOccurrence TEXT,
	LastOccurrence TEXT,
	EventCount INTEGER DEFAULT 0,
	ContentID TEXT,
	ExtraData BLOB, Checksum TEXT,
	PRIMARY KEY (EventType, ContentID) );
CREATE TABLE Achievement( 
	Acknowledged BOOL,
	CompleteDescription TEXT,
	DateCreated TEXT,
	Difficulty INTEGER,
	EventLogDescription TEXT,
	Hidden BOOL,
	Id TEXT NOT NULL,
	ImageId TEXT NOT NULL,
	IncompleteDescription TEXT, 
	Name TEXT NOT NULL,
	Ordinal INTEGER,
	PercentComplete INTEGER,
	Presented BOOL,
	Synchronized BOOL,
	UserId TEXT, Checksum TEXT, FacebookImageId TEXT,
	PRIMARY KEY (Id) );
CREATE TABLE Rules( 
	AchievementId	 TEXT, 
	EventProperty	 TEXT, 
	EventType	 TEXT, 
	GoalValue	 TEXT NOT NULL, 
	Id		 TEXT NOT NULL, 
	Operation	 INT  NOT NULL, 
	ParentRuleId	 TEXT, 
	ConjunctionType	INT, 
	IsConjunction	 BOOL, Checksum TEXT, 
	PRIMARY KEY(Id));
CREATE TABLE volume_tabs (volumeId TEXT NOT NULL,tabId TEXT NOT NULL DEFAULT 'abcdefff-ffff-ffff-ffff-fffffffffffd',PRIMARY KEY (volumeId, tabId));
CREATE TABLE ratings (ContentID TEXT NOT NULL, Rating INTEGER, Review TEXT, DateModified TEXT NOT NULL, PRIMARY KEY(ContentID));
CREATE TABLE ShelfContent (
	ShelfName	TEXT,
	ContentId	TEXT,
	DateModified	TEXT,
	_IsDeleted	BOOL,
	_IsSynced	BOOL,
	PRIMARY KEY(ShelfName, ContentId)
);
CREATE TABLE Dictionary (
	Suffix		TEXT,
	Name		TEXT,
	Installed	BOOL,
	Size		TEXT,
	LastModified	TEXT,
	IsSynced	BOOL,
	PRIMARY KEY(Suffix)
);
CREATE TABLE content_settings (ContentID TEXT NOT NULL, ContentType INTEGER NOT NULL, DateModified TEXT NOT NULL, ReadingFontFamily TEXT, ReadingFontSize INTEGER, ReadingAlignment TEXT, ReadingLineHeight REAL, ReadingLeftMargin INTEGER, ReadingRightMargin INTEGER, ReadingPublisherMode INTEGER, ActivityFacebookShare BIT default TRUE, RecentBookSearches TEXT, AuthorNotesShown BIT default false, LastAuthorNotesSyncTime TEXT, ZoomFactor INTEGER default 1, BTBFooterSection TEXT, SelectedDictionary TEXT, PRIMARY KEY (ContentID, ContentType));
CREATE TABLE Shelf (
	CreationDate	TEXT,
	Id		TEXT,
	InternalName	TEXT,
	LastModified	TEXT,
	Name		TEXT,
	Type		TEXT,
	_IsDeleted	BOOL,
	_IsVisible	BOOL,
	_IsSynced	BOOL, _SyncTime TEXT, LastAccessed TEXT,
	PRIMARY KEY(Id)
);
CREATE TABLE user(UserID TEXT NOT NULL,UserKey TEXT NOT NULL,UserDisplayName TEXT,UserEmail TEXT,___DeviceID TEXT,FacebookAuthToken TEXT,HasMadePurchase BIT DEFAULT FALSE, IsOneStoreAccount BIT DEFAULT FALSE, IsChildAccount BIT DEFAULT FALSE, RefreshToken TEXT, AuthToken TEXT, AuthType TEXT, Loyalty BLOB, IsLibraryMigrated BIT NOT NULL DEFAULT true, SyncContinuationToken TEXT, Subscription INT NOT NULL DEFAULT 0, LibrarySyncType TEXT, LibrarySyncTime TEXT, SyncTokenAppVersion TEXT,PRIMARY KEY (UserID));
CREATE TABLE SyncQueue (
	Date		TEXT,
	VolumeId	TEXT,
	State		INT,
	PRIMARY KEY(VolumeId)
);
CREATE TABLE AbTest( 
	Id TEXT, 
	Expiration TEXT, 
	Name TEXT, 
	GroupId INT, 
	Variables TEXT, 
	Status INT, 
	Description TEXT, 
	Checksum TEXT, TestKey TEXT, 
	PRIMARY KEY(Id));
CREATE TABLE AnalyticsEvents( 
	Id TEXT, 
	Type TEXT, 
	Timestamp TEXT, 
	Attributes TEXT, 
	Metrics TEXT, 
	TestGroups TEXT, ClientApplicationVersion TEXT, 
	PRIMARY KEY(Id));
CREATE TABLE Activity (
	Id		TEXT,
	Enabled	BIT default TRUE,
	Type	TEXT,
	Action	INTEGER,
	Date	TEXT,
	Data	BLOB,
	PRIMARY KEY(Id, Type)
);
CREATE TABLE Authors( 
	UserId  TEXT, 
	Avatar TEXT, 
	Name TEXT, 
	FacebookId TEXT, 
	PRIMARY KEY(UserID));
CREATE TABLE BookAuthors( 
	AuthorId  TEXT, 
	BookId TEXT, 
	PRIMARY KEY(AuthorId, BookId));
CREATE TABLE Reviews (
Id TEXT NOT NULL,
Header TEXT,
Content TEXT,
CreationDate TEXT,
VolumeId TEXT NOT NULL,
AuthorDisplayName TEXT,
Sentiment TEXT,
UserId TEXT,
Likes INTEGER,
Dislikes INTEGER,
Rating INTEGER,
SyncDate TEXT,
Status TEXT,
PRIMARY KEY(Id));
CREATE TABLE Tab (
tabId TEXT NOT NULL,
tabType TEXT,
browseTabType TEXT,
displayTitle TEXT,
parentTabId TEXT,
isDefault BOOL,
maxSize INTEGER,
totalResults INTEGER,
updateFrequencyMin INTEGER,
imageID TEXT,
isLeaf BOOL,
hasFeaturedLists BOOL, etag TEXT,
PRIMARY KEY(tabID));
CREATE TABLE OverDriveCards (CardId INTEGER NOT NULL, LibraryKey TEXT NOT NULL, BestLibraryKey TEXT NOT NULL, WebsiteId INTEGER NOT NULL, Name TEXT, LastModified TEXT, PRIMARY KEY(CardId));
CREATE TABLE OverDriveLibrary (
Selected BIT NOT NULL DEFAULT false,
WebsiteId INTEGER NOT NULL,
LibraryKey TEXT,
Name TEXT,
PRIMARY KEY(WebsiteId));
CREATE TABLE OverDriveCheckoutBook (
id TEXT,
title TEXT,
libraryKey TEXT,
cardId TEXT,
PRIMARY KEY(id));
CREATE TABLE WordList( 
	Text  TEXT, 
	VolumeId TEXT, 
	DictSuffix TEXT, DateCreated TEXT, 
	PRIMARY KEY(Text));
CREATE TABLE Wishlist( 
	CrossRevisionId TEXT, 
	DateModified TEXT NOT NULL, 
	IsAdded BOOL, 
	IsSynced BOOL, 
	PRIMARY KEY(CrossRevisionId));
CREATE INDEX content_bookid_index ON content (BookID);
CREATE INDEX volume_shortcovers_shortcoverId ON volume_shortcovers (shortcoverId);
CREATE INDEX volume_tabs_volumeId ON volume_tabs (volumeId);
CREATE INDEX shelfcontent_datemodified_index ON ShelfContent (DateModified);
CREATE INDEX content_settings_index ON content_settings (ContentID, ContentType);
CREATE INDEX shelf_id_index ON shelf (Id);
CREATE INDEX shelf_name_index ON shelf (Name);
CREATE INDEX shelf_creationdate_index ON shelf (CreationDate);
CREATE INDEX analytics_events_timestamp ON AnalyticsEvents (Timestamp);
CREATE INDEX activity_id_index ON Activity (Id);
CREATE INDEX OverDriveCards_LibraryKey_index ON OverDriveCards (LibraryKey);
CREATE INDEX bookmark_content ON bookmark (ContentID);
CREATE INDEX content_keys_volume ON content_keys (volumeid);
CREATE INDEX volume_tabs_tabId ON volume_tabs (tabId);
