text:
package org.geometerplus.android.fbreader.libraryService;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.TreeSet;

import org.geometerplus.android.util.SQLiteUtil;
import org.geometerplus.fbreader.book.Author;
import org.geometerplus.fbreader.book.Book;
import org.geometerplus.fbreader.book.Bookmark;
import org.geometerplus.fbreader.book.BookmarkQuery;
import org.geometerplus.fbreader.book.BooksDatabase;
import org.geometerplus.fbreader.book.FileInfo;
import org.geometerplus.fbreader.book.FileInfoSet;
import org.geometerplus.fbreader.book.HighlightingStyle;
import org.geometerplus.fbreader.book.SeriesInfo;
import org.geometerplus.fbreader.book.Tag;
import org.geometerplus.fbreader.book.UID;
import org.geometerplus.zlibrary.core.config.ZLConfig;
import org.geometerplus.zlibrary.core.filesystem.ZLFile;
import org.geometerplus.zlibrary.core.options.ZLIntegerOption;
import org.geometerplus.zlibrary.core.options.ZLStringOption;
import org.geometerplus.zlibrary.core.util.ZLColor;
import org.geometerplus.zlibrary.text.view.ZLTextFixedPosition;
import org.geometerplus.zlibrary.text.view.ZLTextPosition;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

final class SQLiteBooksDatabase extends BooksDatabase {
	private static BooksDatabase ourInstance;

	static BooksDatabase Instance(Context context) {
		if (ourInstance == null) {
			ourInstance = new SQLiteBooksDatabase(context);
		}
		return ourInstance;
	}

	private final SQLiteDatabase myDatabase;

	private SQLiteBooksDatabase(Context context) {
		myDatabase = context.openOrCreateDatabase("books.db", Context.MODE_PRIVATE, null);
		migrate();
	}

	protected void executeAsTransaction(Runnable actions) {
		boolean transactionStarted = false;
		try {
			myDatabase.beginTransaction();
			transactionStarted = true;
		} catch (Throwable t) {
		}
		try {
			actions.run();
			if (transactionStarted) {
				myDatabase.setTransactionSuccessful();
			}
		} finally {
			if (transactionStarted) {
				myDatabase.endTransaction();
			}
		}
	}

	private void migrate() {
		final int version = myDatabase.getVersion();
		final int currentVersion = 25;
		if (version >= currentVersion) {
			return;
		}

		myDatabase.beginTransaction();

		switch (version) {
			case 0:
				createTables();
			case 1:
				updateTables1();
			case 2:
				updateTables2();
			case 3:
				updateTables3();
			case 4:
				updateTables4();
			case 5:
				updateTables5();
			case 6:
				updateTables6();
			case 7:
				updateTables7();
			case 8:
				updateTables8();
			case 9:
				updateTables9();
			case 10:
				updateTables10();
			case 11:
				updateTables11();
			case 12:
				updateTables12();
			case 13:
				updateTables13();
			case 14:
				updateTables14();
			case 15:
				updateTables15();
			case 16:
				updateTables16();
			case 17:
				updateTables17();
			case 18:
				updateTables18();
			case 19:
				updateTables19();
			case 20:
				updateTables20();
			case 21:
				updateTables21();
			case 22:
				updateTables22();
			case 23:
				updateTables23();
			case 24:
				updateTables24();
		}
		myDatabase.setTransactionSuccessful();
		myDatabase.setVersion(currentVersion);
		myDatabase.endTransaction();

		myDatabase.execSQL("VACUUM");
	}

	@Override
	protected Book loadBook(long bookId) {
		Book book = null;
		final Cursor cursor = myDatabase.rawQuery("SELECT file_id,title,encoding,language FROM Books WHERE book_id = " + bookId, null);
		if (cursor.moveToNext()) {
			book = createBook(
				bookId, cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getString(3)
			);
		}
		cursor.close();
		return book;
	}

	protected Book loadBookByFile(long fileId, ZLFile file) {
		if (fileId == -1) {
			return null;
		}
		Book book = null;
		final Cursor cursor = myDatabase.rawQuery("SELECT book_id,title,encoding,language FROM Books WHERE file_id = " + fileId, null);
		if (cursor.moveToNext()) {
			book = createBook(
				cursor.getLong(0), file, cursor.getString(1), cursor.getString(2), cursor.getString(3)
			);
		}
		cursor.close();
		return book;
	}

	private boolean myTagCacheIsInitialized;
	private final HashMap<Tag,Long> myIdByTag = new HashMap<Tag,Long>();
	private final HashMap<Long,Tag> myTagById = new HashMap<Long,Tag>();

	private void initTagCache() {
		if (myTagCacheIsInitialized) {
			return;
		}
		myTagCacheIsInitialized = true;

		Cursor cursor = myDatabase.rawQuery("SELECT tag_id,parent_id,name FROM Tags ORDER BY tag_id", null);
		while (cursor.moveToNext()) {
			long id = cursor.getLong(0);
			if (myTagById.get(id) == null) {
				final Tag tag = Tag.getTag(myTagById.get(cursor.getLong(1)), cursor.getString(2));
				myIdByTag.put(tag, id);
				myTagById.put(id, tag);
			}
		}
		cursor.close();
	}

	@Override
	protected Map<Long,Book> loadBooks(FileInfoSet infos, boolean existing) {
		Cursor cursor = myDatabase.rawQuery(
			"SELECT book_id,file_id,title,encoding,language FROM Books WHERE `exists` = " + (existing ? 1 : 0), null
		);
		final HashMap<Long,Book> booksById = new HashMap<Long,Book>();
		final HashMap<Long,Book> booksByFileId = new HashMap<Long,Book>();
		while (cursor.moveToNext()) {
			final long id = cursor.getLong(0);
			final long fileId = cursor.getLong(1);
			final Book book = createBook(
				id, infos.getFile(fileId), cursor.getString(2), cursor.getString(3), cursor.getString(4)
			);
			if (book != null) {
				booksById.put(id, book);
				booksByFileId.put(fileId, book);
			}
		}
		cursor.close();

		initTagCache();

		cursor = myDatabase.rawQuery(
			"SELECT author_id,name,sort_key FROM Authors", null
		);
		final HashMap<Long,Author> authorById = new HashMap<Long,Author>();
		while (cursor.moveToNext()) {
			authorById.put(cursor.getLong(0), new Author(cursor.getString(1), cursor.getString(2)));
		}
		cursor.close();

		cursor = myDatabase.rawQuery(
			"SELECT book_id,author_id FROM BookAuthor ORDER BY author_index", null
		);
		while (cursor.moveToNext()) {
			final Book book = booksById.get(cursor.getLong(0));
			if (book != null) {
				Author author = authorById.get(cursor.getLong(1));
				if (author != null) {
					addAuthor(book, author);
				}
			}
		}
		cursor.close();

		cursor = myDatabase.rawQuery("SELECT book_id,tag_id FROM BookTag", null);
		while (cursor.moveToNext()) {
			final Book book = booksById.get(cursor.getLong(0));
			if (book != null) {
				addTag(book, getTagById(cursor.getLong(1)));
			}
		}
		cursor.close();

		cursor = myDatabase.rawQuery(
			"SELECT series_id,name FROM Series", null
		);
		final HashMap<Long,String> seriesById = new HashMap<Long,String>();
		while (cursor.moveToNext()) {
			seriesById.put(cursor.getLong(0), cursor.getString(1));
		}
		cursor.close();

		cursor = myDatabase.rawQuery(
			"SELECT book_id,series_id,book_index FROM BookSeries", null
		);
		while (cursor.moveToNext()) {
			final Book book = booksById.get(cursor.getLong(0));
			if (book != null) {
				final String series = seriesById.get(cursor.getLong(1));
				if (series != null) {
					setSeriesInfo(book, series, cursor.getString(2));
				}
			}
		}
		cursor.close();

		cursor = myDatabase.rawQuery(
			"SELECT book_id,type,uid FROM BookUid", null
		);
		while (cursor.moveToNext()) {
			final Book book = booksById.get(cursor.getLong(0));
			if (book != null) {
				book.addUid(cursor.getString(1), cursor.getString(2));
			}
		}
		cursor.close();

		cursor = myDatabase.rawQuery(
			"SELECT BookLabel.book_id,Labels.name FROM Labels" +
			" INNER JOIN BookLabel ON BookLabel.label_id=Labels.label_id",
			null
		);
		while (cursor.moveToNext()) {
			final Book book = booksById.get(cursor.getLong(0));
			if (book != null) {
				book.addLabel(cursor.getString(1));
			}
		}
		cursor.close();

		cursor = myDatabase.rawQuery(
			"SELECT book_id FROM Bookmarks WHERE visible = 1 GROUP by book_id",
			null
		);
		while (cursor.moveToNext()) {
			final Book book = booksById.get(cursor.getLong(0));
			if (book != null) {
				book.HasBookmark = true;
			}
		}
		cursor.close();

		return booksByFileId;
	}

	@Override
	protected void setExistingFlag(Collection<Book> books, boolean flag) {
		if (books.isEmpty()) {
			return;
		}
		final StringBuilder bookSet = new StringBuilder("(");
		boolean first = true;
		for (Book b : books) {
			if (first) {
				first = false;
			} else {
				bookSet.append(",");
			}
			bookSet.append(b.getId());
		}
		bookSet.append(")");
		myDatabase.execSQL(
			"UPDATE Books SET `exists` = " + (flag ? 1 : 0) + " WHERE book_id IN " + bookSet
		);
	}

	private SQLiteStatement myUpdateBookInfoStatement;
	@Override
	protected void updateBookInfo(long bookId, long fileId, String encoding, String language, String title) {
		if (myUpdateBookInfoStatement == null) {
			myUpdateBookInfoStatement = myDatabase.compileStatement(
				"UPDATE OR IGNORE Books SET file_id = ?, encoding = ?, language = ?, title = ? WHERE book_id = ?"
			);
		}
		myUpdateBookInfoStatement.bindLong(1, fileId);
		SQLiteUtil.bindString(myUpdateBookInfoStatement, 2, encoding);
		SQLiteUtil.bindString(myUpdateBookInfoStatement, 3, language);
		myUpdateBookInfoStatement.bindString(4, title);
		myUpdateBookInfoStatement.bindLong(5, bookId);
		myUpdateBookInfoStatement.execute();
	}

	private SQLiteStatement myInsertBookInfoStatement;
	@Override
	protected long insertBookInfo(ZLFile file, String encoding, String language, String title) {
		if (myInsertBookInfoStatement == null) {
			myInsertBookInfoStatement = myDatabase.compileStatement(
				"INSERT OR IGNORE INTO Books (encoding,language,title,file_id) VALUES (?,?,?,?)"
			);
		}
		SQLiteUtil.bindString(myInsertBookInfoStatement, 1, encoding);
		SQLiteUtil.bindString(myInsertBookInfoStatement, 2, language);
		myInsertBookInfoStatement.bindString(3, title);
		final FileInfoSet infoSet = new FileInfoSet(this, file);
		myInsertBookInfoStatement.bindLong(4, infoSet.getId(file));
		return myInsertBookInfoStatement.executeInsert();
	}

	private SQLiteStatement myDeleteBookAuthorsStatement;
	protected void deleteAllBookAuthors(long bookId) {
		if (myDeleteBookAuthorsStatement == null) {
			myDeleteBookAuthorsStatement = myDatabase.compileStatement(
				"DELETE FROM BookAuthor WHERE book_id = ?"
			);
		}
		myDeleteBookAuthorsStatement.bindLong(1, bookId);
		myDeleteBookAuthorsStatement.execute();
	}

	private SQLiteStatement myGetAuthorIdStatement;
	private SQLiteStatement myInsertAuthorStatement;
	private SQLiteStatement myInsertBookAuthorStatement;
	protected void saveBookAuthorInfo(long bookId, long index, Author author) {
		if (myGetAuthorIdStatement == null) {
			myGetAuthorIdStatement = myDatabase.compileStatement(
				"SELECT author_id FROM Authors WHERE name = ? AND sort_key = ?"
			);
			myInsertAuthorStatement = myDatabase.compileStatement(
				"INSERT OR IGNORE INTO Authors (name,sort_key) VALUES (?,?)"
			);
			myInsertBookAuthorStatement = myDatabase.compileStatement(
				"INSERT OR REPLACE INTO BookAuthor (book_id,author_id,author_index) VALUES (?,?,?)"
			);
		}

		long authorId;
		try {
			myGetAuthorIdStatement.bindString(1, author.DisplayName);
			myGetAuthorIdStatement.bindString(2, author.SortKey);
			authorId = myGetAuthorIdStatement.simpleQueryForLong();
		} catch (SQLException e) {
			myInsertAuthorStatement.bindString(1, author.DisplayName);
			myInsertAuthorStatement.bindString(2, author.SortKey);
			authorId = myInsertAuthorStatement.executeInsert();
		}
		myInsertBookAuthorStatement.bindLong(1, bookId);
		myInsertBookAuthorStatement.bindLong(2, authorId);
		myInsertBookAuthorStatement.bindLong(3, index);
		myInsertBookAuthorStatement.execute();
	}

	protected List<Author> listAuthors(long bookId) {
		final Cursor cursor = myDatabase.rawQuery("SELECT Authors.name,Authors.sort_key FROM BookAuthor INNER JOIN Authors ON Authors.author_id = BookAuthor.author_id WHERE BookAuthor.book_id = ?", new String[] { String.valueOf(bookId) });
		if (!cursor.moveToNext()) {
			cursor.close();
			return null;
		}
		final ArrayList<Author> list = new ArrayList<Author>();
		do {
			list.add(new Author(cursor.getString(0), cursor.getString(1)));
		} while (cursor.moveToNext());
		cursor.close();
		return list;
	}

	private SQLiteStatement myGetTagIdStatement;
	private SQLiteStatement myCreateTagIdStatement;
	private long getTagId(Tag tag) {
		if (myGetTagIdStatement == null) {
			myGetTagIdStatement = myDatabase.compileStatement(
				"SELECT tag_id FROM Tags WHERE parent_id = ? AND name = ?"
			);
			myCreateTagIdStatement = myDatabase.compileStatement(
				"INSERT OR IGNORE INTO Tags (parent_id,name) VALUES (?,?)"
			);
		}
		{
			final Long id = myIdByTag.get(tag);
			if (id != null) {
				return id;
			}
		}
		if (tag.Parent != null) {
			myGetTagIdStatement.bindLong(1, getTagId(tag.Parent));
		} else {
			myGetTagIdStatement.bindNull(1);
		}
		myGetTagIdStatement.bindString(2, tag.Name);
		long id;
		try {
			id = myGetTagIdStatement.simpleQueryForLong();
		} catch (SQLException e) {
			if (tag.Parent != null) {
				myCreateTagIdStatement.bindLong(1, getTagId(tag.Parent));
			} else {
				myCreateTagIdStatement.bindNull(1);
			}
			myCreateTagIdStatement.bindString(2, tag.Name);
			id = myCreateTagIdStatement.executeInsert();
		}
		myIdByTag.put(tag, id);
		myTagById.put(id, tag);
		return id;
	}

	private SQLiteStatement myDeleteBookTagsStatement;
	protected void deleteAllBookTags(long bookId) {
		if (myDeleteBookTagsStatement == null) {
			myDeleteBookTagsStatement = myDatabase.compileStatement(
				"DELETE FROM BookTag WHERE book_id = ?"
			);
		}
		myDeleteBookTagsStatement.bindLong(1, bookId);
		myDeleteBookTagsStatement.execute();
	}

	private SQLiteStatement myInsertBookTagStatement;
	protected void saveBookTagInfo(long bookId, Tag tag) {
		if (myInsertBookTagStatement == null) {
			myInsertBookTagStatement = myDatabase.compileStatement(
				"INSERT OR IGNORE INTO BookTag (book_id,tag_id) VALUES (?,?)"
			);
		}
		myInsertBookTagStatement.bindLong(1, bookId);
		myInsertBookTagStatement.bindLong(2, getTagId(tag));
		myInsertBookTagStatement.execute();
	}

	private Tag getTagById(long id) {
		Tag tag = myTagById.get(id);
		if (tag == null) {
			final Cursor cursor = myDatabase.rawQuery("SELECT parent_id,name FROM Tags WHERE tag_id = ?", new String[] { String.valueOf(id) });
			if (cursor.moveToNext()) {
				final Tag parent = cursor.isNull(0) ? null : getTagById(cursor.getLong(0));
				tag = Tag.getTag(parent, cursor.getString(1));
				myIdByTag.put(tag, id);
				myTagById.put(id, tag);
			}
			cursor.close();
		}
		return tag;
	}

	protected List<Tag> listTags(long bookId) {
		final Cursor cursor = myDatabase.rawQuery("SELECT Tags.tag_id FROM BookTag INNER JOIN Tags ON Tags.tag_id = BookTag.tag_id WHERE BookTag.book_id = ?", new String[] { String.valueOf(bookId) });
		if (!cursor.moveToNext()) {
			cursor.close();
			return null;
		}
		final ArrayList<Tag> list = new ArrayList<Tag>();
		do {
			list.add(getTagById(cursor.getLong(0)));
		} while (cursor.moveToNext());
		cursor.close();
		return list;
	}

	@Override
	protected List<String> listLabels(long bookId) {
		final Cursor cursor = myDatabase.rawQuery(
			"SELECT Labels.name FROM Labels" +
			" INNER JOIN BookLabel ON BookLabel.label_id=Labels.label_id" +
			" WHERE BookLabel.book_id=?",
			new String[] { String.valueOf(bookId) }
		);
		final LinkedList<String> names = new LinkedList<String>();
		while (cursor.moveToNext()) {
			names.add(cursor.getString(0));
		}
		cursor.close();
		return names;
	}

	private SQLiteStatement myDeleteBookUidsStatement;
	protected void deleteAllBookUids(long bookId) {
		if (myDeleteBookUidsStatement == null) {
			myDeleteBookUidsStatement = myDatabase.compileStatement(
				"DELETE FROM BookUid WHERE book_id = ?"
			);
		}
		myDeleteBookUidsStatement.bindLong(1, bookId);
		myDeleteBookUidsStatement.execute();
	}

	private SQLiteStatement myInsertBookUidStatement;
	@Override
	protected void saveBookUid(long bookId, UID uid) {
		if (myInsertBookUidStatement == null) {
			myInsertBookUidStatement = myDatabase.compileStatement(
				"INSERT OR IGNORE INTO BookUid (book_id,type,uid) VALUES (?,?,?)"
			);
		}

		synchronized (myInsertBookUidStatement) {
			myInsertBookUidStatement.bindLong(1, bookId);
			myInsertBookUidStatement.bindString(2, uid.Type);
			myInsertBookUidStatement.bindString(3, uid.Id);
			myInsertBookUidStatement.execute();
		}
	}

	@Override
	protected List<UID> listUids(long bookId) {
		final ArrayList<UID> list = new ArrayList<UID>();
		final Cursor cursor = myDatabase.rawQuery("SELECT type,uid FROM BookUid WHERE book_id = ?", new String[] { String.valueOf(bookId) });
		while (cursor.moveToNext()) {
			list.add(new UID(cursor.getString(0), cursor.getString(1)));
		}
		cursor.close();
		return list;
	}

	@Override
	protected Long bookIdByUid(UID uid) {
		Long bookId = null;
		final Cursor cursor = myDatabase.rawQuery("SELECT book_id FROM BookUid WHERE type = ? AND uid = ?", new String[] { uid.Type, uid.Id });
		if (cursor.moveToNext()) {
			bookId = cursor.getLong(0);
		}
		cursor.close();
		return bookId;
	}

	private SQLiteStatement myGetSeriesIdStatement;
	private SQLiteStatement myInsertSeriesStatement;
	private SQLiteStatement myInsertBookSeriesStatement;
	private SQLiteStatement myDeleteBookSeriesStatement;
	protected void saveBookSeriesInfo(long bookId, SeriesInfo seriesInfo) {
		if (myGetSeriesIdStatement == null) {
			myGetSeriesIdStatement = myDatabase.compileStatement(
				"SELECT series_id FROM Series WHERE name = ?"
			);
			myInsertSeriesStatement = myDatabase.compileStatement(
				"INSERT OR IGNORE INTO Series (name) VALUES (?)"
			);
			myInsertBookSeriesStatement = myDatabase.compileStatement(
				"INSERT OR REPLACE INTO BookSeries (book_id,series_id,book_index) VALUES (?,?,?)"
			);
			myDeleteBookSeriesStatement = myDatabase.compileStatement(
				"DELETE FROM BookSeries WHERE book_id = ?"
			);
		}

		if (seriesInfo == null) {
			myDeleteBookSeriesStatement.bindLong(1, bookId);
			myDeleteBookSeriesStatement.execute();
		} else {
			long seriesId;
			try {
				myGetSeriesIdStatement.bindString(1, seriesInfo.Series.getTitle());
				seriesId = myGetSeriesIdStatement.simpleQueryForLong();
			} catch (SQLException e) {
				myInsertSeriesStatement.bindString(1, seriesInfo.Series.getTitle());
				seriesId = myInsertSeriesStatement.executeInsert();
			}
			myInsertBookSeriesStatement.bindLong(1, bookId);
			myInsertBookSeriesStatement.bindLong(2, seriesId);
			SQLiteUtil.bindString(
				myInsertBookSeriesStatement, 3,
				seriesInfo.Index != null ? seriesInfo.Index.toPlainString() : null
			);
			myInsertBookSeriesStatement.execute();
		}
	}

	protected SeriesInfo getSeriesInfo(long bookId) {
		final Cursor cursor = myDatabase.rawQuery("SELECT Series.name,BookSeries.book_index FROM BookSeries INNER JOIN Series ON Series.series_id = BookSeries.series_id WHERE BookSeries.book_id = ?", new String[] { String.valueOf(bookId) });
		SeriesInfo info = null;
		if (cursor.moveToNext()) {
			info = SeriesInfo.createSeriesInfo(cursor.getString(0), cursor.getString(1));
		}
		cursor.close();
		return info;
	}

	private SQLiteStatement myRemoveFileInfoStatement;
	protected void removeFileInfo(long fileId) {
		if (fileId == -1) {
			return;
		}
		if (myRemoveFileInfoStatement == null) {
			myRemoveFileInfoStatement = myDatabase.compileStatement(
				"DELETE FROM Files WHERE file_id = ?"
			);
		}
		myRemoveFileInfoStatement.bindLong(1, fileId);
		myRemoveFileInfoStatement.execute();
	}

	private SQLiteStatement myInsertFileInfoStatement;
	private SQLiteStatement myUpdateFileInfoStatement;
	protected void saveFileInfo(FileInfo fileInfo) {
		final long id = fileInfo.Id;
		SQLiteStatement statement;
		if (id == -1) {
			if (myInsertFileInfoStatement == null) {
				myInsertFileInfoStatement = myDatabase.compileStatement(
					"INSERT OR IGNORE INTO Files (name,parent_id,size) VALUES (?,?,?)"
				);
			}
			statement = myInsertFileInfoStatement;
		} else {
			if (myUpdateFileInfoStatement == null) {
				myUpdateFileInfoStatement = myDatabase.compileStatement(
					"UPDATE Files SET name = ?, parent_id = ?, size = ? WHERE file_id = ?"
				);
			}
			statement = myUpdateFileInfoStatement;
		}
		statement.bindString(1, fileInfo.Name);
		final FileInfo parent = fileInfo.Parent;
		if (parent != null) {
			statement.bindLong(2, parent.Id);
		} else {
			statement.bindNull(2);
		}
		final long size = fileInfo.FileSize;
		if (size != -1) {
			statement.bindLong(3, size);
		} else {
			statement.bindNull(3);
		}
		if (id == -1) {
			fileInfo.Id = statement.executeInsert();
		} else {
			statement.bindLong(4, id);
			statement.execute();
		}
	}

	protected Collection<FileInfo> loadFileInfos() {
		Cursor cursor = myDatabase.rawQuery(
			"SELECT file_id,name,parent_id,size FROM Files", null
		);
		HashMap<Long,FileInfo> infosById = new HashMap<Long,FileInfo>();
		while (cursor.moveToNext()) {
			final long id = cursor.getLong(0);
			final FileInfo info = createFileInfo(id,
				cursor.getString(1),
				cursor.isNull(2) ? null : infosById.get(cursor.getLong(2))
			);
			if (!cursor.isNull(3)) {
				info.FileSize = cursor.getLong(3);
			}
			infosById.put(id, info);
		}
		cursor.close();
		return infosById.values();
	}

	protected Collection<FileInfo> loadFileInfos(ZLFile file) {
		final LinkedList<ZLFile> fileStack = new LinkedList<ZLFile>();
		for (; file != null; file = file.getParent()) {
			fileStack.addFirst(file);
		}

		final ArrayList<FileInfo> infos = new ArrayList<FileInfo>(fileStack.size());
		final String[] parameters = { null };
		FileInfo current = null;
		for (ZLFile f : fileStack) {
			parameters[0] = f.getLongName();
			final Cursor cursor = myDatabase.rawQuery(
				(current == null) ?
					"SELECT file_id,size FROM Files WHERE name = ?" :
					"SELECT file_id,size FROM Files WHERE parent_id = " + current.Id + " AND name = ?",
				parameters
			);
			if (cursor.moveToNext()) {
				current = createFileInfo(cursor.getLong(0), parameters[0], current);
				if (!cursor.isNull(1)) {
					current.FileSize = cursor.getLong(1);
				}
				infos.add(current);
				cursor.close();
			} else {
				cursor.close();
				break;
			}
		}

		return infos;
	}

	protected Collection<FileInfo> loadFileInfos(long fileId) {
		final ArrayList<FileInfo> infos = new ArrayList<FileInfo>();
		while (fileId != -1) {
			final Cursor cursor = myDatabase.rawQuery(
				"SELECT name,size,parent_id FROM Files WHERE file_id = " + fileId, null
			);
			if (cursor.moveToNext()) {
				FileInfo info = createFileInfo(fileId, cursor.getString(0), null);
				if (!cursor.isNull(1)) {
					info.FileSize = cursor.getLong(1);
				}
				infos.add(0, info);
				fileId = cursor.isNull(2) ? -1 : cursor.getLong(2);
			} else {
				fileId = -1;
			}
			cursor.close();
		}
		for (int i = 1; i < infos.size(); ++i) {
			final FileInfo oldInfo = infos.get(i);
			final FileInfo newInfo = createFileInfo(oldInfo.Id, oldInfo.Name, infos.get(i - 1));
			newInfo.FileSize = oldInfo.FileSize;
			infos.set(i, newInfo);
		}
		return infos;
	}

	private SQLiteStatement mySaveRecentBookStatement;
	protected void saveRecentBookIds(final List<Long> ids) {
		if (mySaveRecentBookStatement == null) {
			mySaveRecentBookStatement = myDatabase.compileStatement(
				"INSERT OR IGNORE INTO RecentBooks (book_id) VALUES (?)"
			);
		}
		executeAsTransaction(new Runnable() {
			public void run() {
				myDatabase.delete("RecentBooks", null, null);
				for (long id : ids) {
					mySaveRecentBookStatement.bindLong(1, id);
					mySaveRecentBookStatement.execute();
				}
			}
		});
	}

	@Override
	protected List<Long> loadRecentBookIds() {
		final Cursor cursor = myDatabase.rawQuery(
			"SELECT book_id FROM RecentBooks ORDER BY book_index", null
		);
		final LinkedList<Long> ids = new LinkedList<Long>();
		while (cursor.moveToNext()) {
			ids.add(cursor.getLong(0));
		}
		cursor.close();
		return ids;
	}

	private SQLiteStatement mySetLabelStatement;
	@Override
	protected void setLabel(long bookId, String label) {
		myDatabase.execSQL("INSERT OR IGNORE INTO Labels (name) VALUES (?)", new Object[] { label });
		if (mySetLabelStatement == null) {
			mySetLabelStatement = myDatabase.compileStatement(
				"INSERT OR IGNORE INTO BookLabel(label_id,book_id)" +
				" SELECT label_id,? FROM Labels WHERE name=?"
			);
		}
		mySetLabelStatement.bindLong(1, bookId);
		mySetLabelStatement.bindString(2, label);
		mySetLabelStatement.execute();
	}

	private SQLiteStatement myRemoveLabelStatement;
	@Override
	protected void removeLabel(long bookId, String label) {
		if (myRemoveLabelStatement == null) {
			myRemoveLabelStatement = myDatabase.compileStatement(
				"DELETE FROM BookLabel WHERE book_id=? AND label_id IN" +
				" (SELECT label_id FROM Labels WHERE name=?)"
			);
		}
		myRemoveLabelStatement.bindLong(1, bookId);
		myRemoveLabelStatement.bindString(2, label);
		myRemoveLabelStatement.execute();
	}

	@Override
	protected boolean hasVisibleBookmark(long bookId) {
		final Cursor cursor = myDatabase.rawQuery(
			"SELECT bookmark_id FROM Bookmarks WHERE book_id = " + bookId +
			" AND visible = 1 LIMIT 1", null
		);
		final boolean result = cursor.moveToNext();
		cursor.close();
		return result;
	}

	@Override
	protected List<Bookmark> loadBookmarks(BookmarkQuery query) {
		final LinkedList<Bookmark> list = new LinkedList<Bookmark>();
		final StringBuilder sql = new StringBuilder("SELECT")
			.append(" bm.bookmark_id,bm.book_id,b.title,bm.bookmark_text,")
			.append("bm.creation_time,bm.modification_time,bm.access_time,bm.access_counter,")
			.append("bm.model_id,bm.paragraph,bm.word,bm.char,")
			.append("bm.end_paragraph,bm.end_word,bm.end_character,")
			.append("bm.style_id")
			.append(" FROM Bookmarks AS bm")
			.append(" INNER JOIN Books AS b ON b.book_id = bm.book_id")
			.append(" WHERE");
		if (query.Book != null) {
			sql.append(" b.book_id = " + query.Book.getId() +" AND");
		}
		sql
			.append(" bm.visible = " + (query.Visible ? 1 : 0))
			.append(" ORDER BY bm.bookmark_id")
			.append(" LIMIT " + query.Limit * query.Page + "," + query.Limit);
		Cursor cursor = myDatabase.rawQuery(sql.toString(), null);
		while (cursor.moveToNext()) {
			list.add(createBookmark(
				cursor.getLong(0),
				cursor.getLong(1),
				cursor.getString(2),
				cursor.getString(3),
				SQLiteUtil.getDate(cursor, 4),
				SQLiteUtil.getDate(cursor, 5),
				SQLiteUtil.getDate(cursor, 6),
				(int)cursor.getLong(7),
				cursor.getString(8),
				(int)cursor.getLong(9),
				(int)cursor.getLong(10),
				(int)cursor.getLong(11),
				(int)cursor.getLong(12),
				cursor.isNull(13) ? -1 : (int)cursor.getLong(13),
				cursor.isNull(14) ? -1 : (int)cursor.getLong(14),
				query.Visible,
				(int)cursor.getLong(15)
			));
		}
		cursor.close();
		return list;
	}

	@Override
	protected List<HighlightingStyle> loadStyles() {
		final LinkedList<HighlightingStyle> list = new LinkedList<HighlightingStyle>();
		final String sql = "SELECT style_id,name,bg_color FROM HighlightingStyle";
		final Cursor cursor = myDatabase.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			list.add(createStyle(
				(int)cursor.getLong(0),
				cursor.getString(1),
				(int)cursor.getLong(2)
			));
		}
		cursor.close();
		return list;
	}

	private SQLiteStatement myInsertStyleStatement;
	protected void saveStyle(HighlightingStyle style) {
		if (myInsertStyleStatement == null) {
			myInsertStyleStatement = myDatabase.compileStatement(
				"INSERT OR REPLACE INTO HighlightingStyle (style_id,name,bg_color) VALUES (?,?,?)"
			);
		}
		myInsertStyleStatement.bindLong(1, style.Id);
		final String name = style.getName();
		myInsertStyleStatement.bindString(2, name != null ? name : "");
		final ZLColor bgColor = style.getBackgroundColor();
		myInsertStyleStatement.bindLong(3, bgColor != null ? bgColor.intValue() : -1);
		myInsertStyleStatement.executeInsert();
	}

	private SQLiteStatement myInsertBookmarkStatement;
	private SQLiteStatement myUpdateBookmarkStatement;
	@Override
	protected long saveBookmark(Bookmark bookmark) {
		SQLiteStatement statement;
		if (bookmark.getId() == -1) {
			if (myInsertBookmarkStatement == null) {
				myInsertBookmarkStatement = myDatabase.compileStatement(
					"INSERT OR IGNORE INTO Bookmarks (book_id,bookmark_text,creation_time,modification_time,access_time,access_counter,model_id,paragraph,word,char,end_paragraph,end_word,end_character,visible,style_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
				);
			}
			statement = myInsertBookmarkStatement;
		} else {
			if (myUpdateBookmarkStatement == null) {
				myUpdateBookmarkStatement = myDatabase.compileStatement(
					"UPDATE Bookmarks SET book_id = ?, bookmark_text = ?, creation_time =?, modification_time = ?,access_time = ?, access_counter = ?, model_id = ?, paragraph = ?, word = ?, char = ?, end_paragraph = ?, end_word = ?, end_character = ?, visible = ?, style_id = ? WHERE bookmark_id = ?"
				);
			}
			statement = myUpdateBookmarkStatement;
		}

		statement.bindLong(1, bookmark.getBookId());
		statement.bindString(2, bookmark.getText());
		SQLiteUtil.bindDate(statement, 3, bookmark.getDate(Bookmark.DateType.Creation));
		SQLiteUtil.bindDate(statement, 4, bookmark.getDate(Bookmark.DateType.Modification));
		SQLiteUtil.bindDate(statement, 5, bookmark.getDate(Bookmark.DateType.Access));
		statement.bindLong(6, bookmark.getAccessCount());
		SQLiteUtil.bindString(statement, 7, bookmark.ModelId);
		statement.bindLong(8, bookmark.ParagraphIndex);
		statement.bindLong(9, bookmark.ElementIndex);
		statement.bindLong(10, bookmark.CharIndex);
		final ZLTextPosition end = bookmark.getEnd();
		if (end != null) {
			statement.bindLong(11, end.getParagraphIndex());
			statement.bindLong(12, end.getElementIndex());
			statement.bindLong(13, end.getCharIndex());
		} else {
			statement.bindLong(11, bookmark.getLength());
			statement.bindNull(12);
			statement.bindNull(13);
		}
		statement.bindLong(14, bookmark.IsVisible ? 1 : 0);
		statement.bindLong(15, bookmark.getStyleId());

		if (statement == myInsertBookmarkStatement) {
			return statement.executeInsert();
		} else {
			final long id = bookmark.getId();
			statement.bindLong(16, id);
			statement.execute();
			return id;
		}
	}

	private SQLiteStatement myDeleteBookmarkStatement;
	@Override
	protected void deleteBookmark(Bookmark bookmark) {
		if (myDeleteBookmarkStatement == null) {
			myDeleteBookmarkStatement = myDatabase.compileStatement(
				"DELETE FROM Bookmarks WHERE bookmark_id = ?"
			);
		}
		myDeleteBookmarkStatement.bindLong(1, bookmark.getId());
		myDeleteBookmarkStatement.execute();
	}

	protected ZLTextPosition getStoredPosition(long bookId) {
		ZLTextPosition position = null;
		Cursor cursor = myDatabase.rawQuery(
			"SELECT paragraph,word,char FROM BookState WHERE book_id = " + bookId, null
		);
		if (cursor.moveToNext()) {
			position = new ZLTextFixedPosition(
				(int)cursor.getLong(0),
				(int)cursor.getLong(1),
				(int)cursor.getLong(2)
			);
		}
		cursor.close();
		return position;
	}

	private SQLiteStatement myStorePositionStatement;
	protected void storePosition(long bookId, ZLTextPosition position) {
		if (myStorePositionStatement == null) {
			myStorePositionStatement = myDatabase.compileStatement(
				"INSERT OR REPLACE INTO BookState (book_id,paragraph,word,char) VALUES (?,?,?,?)"
			);
		}
		myStorePositionStatement.bindLong(1, bookId);
		myStorePositionStatement.bindLong(2, position.getParagraphIndex());
		myStorePositionStatement.bindLong(3, position.getElementIndex());
		myStorePositionStatement.bindLong(4, position.getCharIndex());
		myStorePositionStatement.execute();
	}

	private SQLiteStatement myDeleteVisitedHyperlinksStatement;
	private void deleteVisitedHyperlinks(long bookId) {
		if (myDeleteVisitedHyperlinksStatement == null) {
			myDeleteVisitedHyperlinksStatement = myDatabase.compileStatement(
				"DELETE FROM VisitedHyperlinks WHERE book_id = ?"
			);
		}

		myDeleteVisitedHyperlinksStatement.bindLong(1, bookId);
		myDeleteVisitedHyperlinksStatement.execute();
	}

	private SQLiteStatement myStoreVisitedHyperlinksStatement;
	protected void addVisitedHyperlink(long bookId, String hyperlinkId) {
		if (myStoreVisitedHyperlinksStatement == null) {
			myStoreVisitedHyperlinksStatement = myDatabase.compileStatement(
				"INSERT OR IGNORE INTO VisitedHyperlinks(book_id,hyperlink_id) VALUES (?,?)"
			);
		}

		myStoreVisitedHyperlinksStatement.bindLong(1, bookId);
		myStoreVisitedHyperlinksStatement.bindString(2, hyperlinkId);
		myStoreVisitedHyperlinksStatement.execute();
	}

	protected Collection<String> loadVisitedHyperlinks(long bookId) {
		final TreeSet<String> links = new TreeSet<String>();
		final Cursor cursor = myDatabase.rawQuery("SELECT hyperlink_id FROM VisitedHyperlinks WHERE book_id = ?", new String[] { String.valueOf(bookId) });
		while (cursor.moveToNext()) {
			links.add(cursor.getString(0));
		}
		cursor.close();
		return links;
	}

	private void createTables() {
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS Books(" +
				"book_id INTEGER PRIMARY KEY," +
				"encoding TEXT," +
				"language TEXT," +
				"title TEXT NOT NULL," +
				"file_name TEXT UNIQUE NOT NULL)");
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS Authors(" +
				"author_id INTEGER PRIMARY KEY," +
				"name TEXT NOT NULL," +
				"sort_key TEXT NOT NULL," +
				"CONSTRAINT Authors_Unique UNIQUE (name, sort_key))");
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS BookAuthor(" +
				"author_id INTEGER NOT NULL REFERENCES Authors(author_id)," +
				"book_id INTEGER NOT NULL REFERENCES Books(book_id)," +
				"author_index INTEGER NOT NULL," +
				"CONSTRAINT BookAuthor_Unique0 UNIQUE (author_id, book_id)," +
				"CONSTRAINT BookAuthor_Unique1 UNIQUE (book_id, author_index))");
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS Series(" +
				"series_id INTEGER PRIMARY KEY," +
				"name TEXT UNIQUE NOT NULL)");
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS BookSeries(" +
				"series_id INTEGER NOT NULL REFERENCES Series(series_id)," +
				"book_id INTEGER NOT NULL UNIQUE REFERENCES Books(book_id)," +
				"book_index INTEGER)");
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS Tags(" +
				"tag_id INTEGER PRIMARY KEY," +
				"name TEXT NOT NULL," +
				"parent INTEGER REFERENCES Tags(tag_id)," +
				"CONSTRAINT Tags_Unique UNIQUE (name, parent))");
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS BookTag(" +
				"tag_id INTEGER REFERENCES Tags(tag_id)," +
				"book_id INTEGER REFERENCES Books(book_id)," +
				"CONSTRAINT BookTag_Unique UNIQUE (tag_id, book_id))");
	}

	private void updateTables1() {
		myDatabase.execSQL("ALTER TABLE Tags RENAME TO Tags_Obsolete");
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS Tags(" +
				"tag_id INTEGER PRIMARY KEY," +
				"name TEXT NOT NULL," +
				"parent_id INTEGER REFERENCES Tags(tag_id)," +
				"CONSTRAINT Tags_Unique UNIQUE (name, parent_id))");
		myDatabase.execSQL("INSERT INTO Tags (tag_id,name,parent_id) SELECT tag_id,name,parent FROM Tags_Obsolete");
		myDatabase.execSQL("DROP TABLE IF EXISTS Tags_Obsolete");

		myDatabase.execSQL("ALTER TABLE BookTag RENAME TO BookTag_Obsolete");
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS BookTag(" +
				"tag_id INTEGER NOT NULL REFERENCES Tags(tag_id)," +
				"book_id INTEGER NOT NULL REFERENCES Books(book_id)," +
				"CONSTRAINT BookTag_Unique UNIQUE (tag_id, book_id))");
		myDatabase.execSQL("INSERT INTO BookTag (tag_id,book_id) SELECT tag_id,book_id FROM BookTag_Obsolete");
		myDatabase.execSQL("DROP TABLE IF EXISTS BookTag_Obsolete");
	}

	private void updateTables2() {
		myDatabase.execSQL("CREATE INDEX BookAuthor_BookIndex ON BookAuthor (book_id)");
		myDatabase.execSQL("CREATE INDEX BookTag_BookIndex ON BookTag (book_id)");
		myDatabase.execSQL("CREATE INDEX BookSeries_BookIndex ON BookSeries (book_id)");
	}

	private void updateTables3() {
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS Files(" +
				"file_id INTEGER PRIMARY KEY," +
				"name TEXT NOT NULL," +
				"parent_id INTEGER REFERENCES Files(file_id)," +
				"size INTEGER," +
				"CONSTRAINT Files_Unique UNIQUE (name, parent_id))");
	}

	private void updateTables4() {
		final FileInfoSet fileInfos = new FileInfoSet(this);
		final Cursor cursor = myDatabase.rawQuery(
			"SELECT file_name FROM Books", null
		);
		while (cursor.moveToNext()) {
			fileInfos.check(ZLFile.createFileByPath(cursor.getString(0)).getPhysicalFile(), false);
		}
		cursor.close();
		fileInfos.save();

		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS RecentBooks(" +
				"book_index INTEGER PRIMARY KEY," +
				"book_id INTEGER REFERENCES Books(book_id))");
		final ArrayList<Long> ids = new ArrayList<Long>();

		final SQLiteStatement statement = myDatabase.compileStatement(
			"SELECT book_id FROM Books WHERE file_name = ?"
		);

		for (int i = 0; i < 20; ++i) {
			final ZLStringOption option = new ZLStringOption("LastOpenedBooks", "Book" + i, "");
			final String fileName = option.getValue();
			option.setValue("");
			try {
				statement.bindString(1, fileName);
				final long bookId = statement.simpleQueryForLong();
				if (bookId != -1) {
					ids.add(bookId);
				}
			} catch (SQLException e) {
			}
		}
		saveRecentBookIds(ids);
	}

	private void updateTables5() {
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS Bookmarks(" +
				"bookmark_id INTEGER PRIMARY KEY," +
				"book_id INTEGER NOT NULL REFERENCES Books(book_id)," +
				"bookmark_text TEXT NOT NULL," +
				"creation_time INTEGER NOT NULL," +
				"modification_time INTEGER," +
				"access_time INTEGER," +
				"access_counter INTEGER NOT NULL," +
				"paragraph INTEGER NOT NULL," +
				"word INTEGER NOT NULL," +
				"char INTEGER NOT NULL)");

		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS BookState(" +
				"book_id INTEGER UNIQUE NOT NULL REFERENCES Books(book_id)," +
				"paragraph INTEGER NOT NULL," +
				"word INTEGER NOT NULL," +
				"char INTEGER NOT NULL)");
		Cursor cursor = myDatabase.rawQuery(
			"SELECT book_id,file_name FROM Books", null
		);
		final SQLiteStatement statement = myDatabase.compileStatement("INSERT INTO BookState (book_id,paragraph,word,char) VALUES (?,?,?,?)");
		while (cursor.moveToNext()) {
			final long bookId = cursor.getLong(0);
			final String fileName = cursor.getString(1);
			final int position = new ZLIntegerOption(fileName, "PositionInBuffer", 0).getValue();
			final int paragraph = new ZLIntegerOption(fileName, "Paragraph_" + position, 0).getValue();
			final int word = new ZLIntegerOption(fileName, "Word_" + position, 0).getValue();
			final int chr = new ZLIntegerOption(fileName, "Char_" + position, 0).getValue();
			if ((paragraph != 0) || (word != 0) || (chr != 0)) {
				statement.bindLong(1, bookId);
				statement.bindLong(2, paragraph);
				statement.bindLong(3, word);
				statement.bindLong(4, chr);
				statement.execute();
			}
			ZLConfig.Instance().removeGroup(fileName);
		}
		cursor.close();
	}

	private void updateTables6() {
		myDatabase.execSQL(
			"ALTER TABLE Bookmarks ADD COLUMN model_id TEXT"
		);

		myDatabase.execSQL(
			"ALTER TABLE Books ADD COLUMN file_id INTEGER"
		);

		myDatabase.execSQL("DELETE FROM Files");
		final FileInfoSet infoSet = new FileInfoSet(this);
		Cursor cursor = myDatabase.rawQuery(
			"SELECT file_name FROM Books", null
		);
		while (cursor.moveToNext()) {
			infoSet.check(ZLFile.createFileByPath(cursor.getString(0)).getPhysicalFile(), false);
		}
		cursor.close();
		infoSet.save();

		cursor = myDatabase.rawQuery(
			"SELECT book_id,file_name FROM Books", null
		);
		final SQLiteStatement deleteStatement = myDatabase.compileStatement("DELETE FROM Books WHERE book_id = ?");
		final SQLiteStatement updateStatement = myDatabase.compileStatement("UPDATE OR IGNORE Books SET file_id = ? WHERE book_id = ?");
		while (cursor.moveToNext()) {
			final long bookId = cursor.getLong(0);
			final long fileId = infoSet.getId(ZLFile.createFileByPath(cursor.getString(1)));

			if (fileId == -1) {
				deleteStatement.bindLong(1, bookId);
				deleteStatement.execute();
			} else {
				updateStatement.bindLong(1, fileId);
				updateStatement.bindLong(2, bookId);
				updateStatement.execute();
			}
		}
		cursor.close();

		myDatabase.execSQL("ALTER TABLE Books RENAME TO Books_Obsolete");
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS Books(" +
				"book_id INTEGER PRIMARY KEY," +
				"encoding TEXT," +
				"language TEXT," +
				"title TEXT NOT NULL," +
				"file_id INTEGER UNIQUE NOT NULL REFERENCES Files(file_id))");
		myDatabase.execSQL("INSERT INTO Books (book_id,encoding,language,title,file_id) SELECT book_id,encoding,language,title,file_id FROM Books_Obsolete");
		myDatabase.execSQL("DROP TABLE IF EXISTS Books_Obsolete");
	}

	private void updateTables7() {
		final ArrayList<Long> seriesIDs = new ArrayList<Long>();
		Cursor cursor = myDatabase.rawQuery(
			"SELECT series_id,name FROM Series", null
		);
		while (cursor.moveToNext()) {
			if (cursor.getString(1).length() > 200) {
				seriesIDs.add(cursor.getLong(0));
			}
		}
		cursor.close();
		if (seriesIDs.isEmpty()) {
			return;
		}

		final ArrayList<Long> bookIDs = new ArrayList<Long>();
		for (Long id : seriesIDs) {
			cursor = myDatabase.rawQuery(
				"SELECT book_id FROM BookSeries WHERE series_id=" + id, null
			);
			while (cursor.moveToNext()) {
				bookIDs.add(cursor.getLong(0));
			}
			cursor.close();
			myDatabase.execSQL("DELETE FROM BookSeries WHERE series_id=" + id);
			myDatabase.execSQL("DELETE FROM Series WHERE series_id=" + id);
		}

		for (Long id : bookIDs) {
			myDatabase.execSQL("DELETE FROM Books WHERE book_id=" + id);
			myDatabase.execSQL("DELETE FROM BookAuthor WHERE book_id=" + id);
			myDatabase.execSQL("DELETE FROM BookTag WHERE book_id=" + id);
		}
	}

	private void updateTables8() {
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS BookList ( " +
				"book_id INTEGER UNIQUE NOT NULL REFERENCES Books (book_id))");
	}

	private void updateTables9() {
		myDatabase.execSQL("CREATE INDEX BookList_BookIndex ON BookList (book_id)");
	}

	private void updateTables10() {
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS Favorites(" +
				"book_id INTEGER UNIQUE NOT NULL REFERENCES Books(book_id))");
	}

	private void updateTables11() {
		myDatabase.execSQL("UPDATE Files SET size = size + 1");
	}

	private void updateTables12() {
		myDatabase.execSQL("DELETE FROM Files WHERE parent_id IN (SELECT file_id FROM Files WHERE name LIKE '%.epub')");
	}

	private void updateTables13() {
		myDatabase.execSQL(
			"ALTER TABLE Bookmarks ADD COLUMN visible INTEGER DEFAULT 1"
		);
	}

	private void updateTables14() {
		myDatabase.execSQL("ALTER TABLE BookSeries RENAME TO BookSeries_Obsolete");
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS BookSeries(" +
				"series_id INTEGER NOT NULL REFERENCES Series(series_id)," +
				"book_id INTEGER NOT NULL UNIQUE REFERENCES Books(book_id)," +
				"book_index REAL)");
		myDatabase.execSQL("INSERT INTO BookSeries (series_id,book_id,book_index) SELECT series_id,book_id,book_index FROM BookSeries_Obsolete");
		myDatabase.execSQL("DROP TABLE IF EXISTS BookSeries_Obsolete");
	}

	private void updateTables15() {
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS VisitedHyperlinks(" +
				"book_id INTEGER NOT NULL REFERENCES Books(book_id)," +
				"hyperlink_id TEXT NOT NULL," +
				"CONSTRAINT VisitedHyperlinks_Unique UNIQUE (book_id, hyperlink_id))");
	}

	private void updateTables16() {
		myDatabase.execSQL(
			"ALTER TABLE Books ADD COLUMN `exists` INTEGER DEFAULT 1"
		);
	}

	private void updateTables17() {
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS BookStatus(" +
				"book_id INTEGER NOT NULL REFERENCES Books(book_id) PRIMARY KEY," +
				"access_time INTEGER NOT NULL," +
				"pages_full INTEGER NOT NULL," +
				"page_current INTEGER NOT NULL)");
	}

	private void updateTables18() {
		myDatabase.execSQL("ALTER TABLE BookSeries RENAME TO BookSeries_Obsolete");
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS BookSeries(" +
				"series_id INTEGER NOT NULL REFERENCES Series(series_id)," +
				"book_id INTEGER NOT NULL UNIQUE REFERENCES Books(book_id)," +
				"book_index TEXT)");
		final SQLiteStatement insert = myDatabase.compileStatement(
			"INSERT INTO BookSeries (series_id,book_id,book_index) VALUES (?,?,?)"
		);
		final Cursor cursor = myDatabase.rawQuery("SELECT series_id,book_id,book_index FROM BookSeries_Obsolete", null);
		while (cursor.moveToNext()) {
			insert.bindLong(1, cursor.getLong(0));
			insert.bindLong(2, cursor.getLong(1));
			final float index = cursor.getFloat(2);
			final String stringIndex;
			if (index == 0.0f) {
				stringIndex = null;
			} else {
				if (Math.abs(index - Math.round(index)) < 0.01) {
					stringIndex = String.valueOf(Math.round(index));
				} else {
					stringIndex = String.format("%.1f", index);
				}
			}
			final BigDecimal bdIndex = SeriesInfo.createIndex(stringIndex);
			SQLiteUtil.bindString(insert, 3, bdIndex != null ? bdIndex.toString() : null);
			insert.executeInsert();
		}
		cursor.close();
		myDatabase.execSQL("DROP TABLE IF EXISTS BookSeries_Obsolete");
	}

	private void updateTables19() {
		myDatabase.execSQL("DROP TABLE IF EXISTS BookList");
	}

	private void updateTables20() {
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS Labels(" +
				"label_id INTEGER PRIMARY KEY," +
				"name TEXT NOT NULL UNIQUE)");
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS BookLabel(" +
				"label_id INTEGER NOT NULL REFERENCES Labels(label_id)," +
				"book_id INTEGER NOT NULL REFERENCES Books(book_id)," +
				"CONSTRAINT BookLabel_Unique UNIQUE (label_id,book_id))");
		final SQLiteStatement insert = myDatabase.compileStatement(
			"INSERT INTO Labels (name) VALUES ('favorite')"
		);
		final long id = insert.executeInsert();
		myDatabase.execSQL("INSERT INTO BookLabel (label_id,book_id) SELECT " + id + ",book_id FROM Favorites");
		myDatabase.execSQL("DROP TABLE IF EXISTS Favorites");
	}

	private void updateTables21() {
		myDatabase.execSQL("DROP TABLE IF EXISTS BookUid");
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS BookUid(" +
				"book_id INTEGER NOT NULL UNIQUE REFERENCES Books(book_id)," +
				"type TEXT NOT NULL," +
				"uid TEXT NOT NULL," +
				"CONSTRAINT BookUid_Unique UNIQUE (book_id,type,uid))");
	}

	private void updateTables22() {
		myDatabase.execSQL("ALTER TABLE Bookmarks ADD COLUMN end_paragraph INTEGER");
		myDatabase.execSQL("ALTER TABLE Bookmarks ADD COLUMN end_word INTEGER");
		myDatabase.execSQL("ALTER TABLE Bookmarks ADD COLUMN end_character INTEGER");
	}

	private void updateTables23() {
		myDatabase.execSQL(
			"CREATE TABLE IF NOT EXISTS HighlightingStyle(" +
				"style_id INTEGER PRIMARY KEY," +
				"name TEXT NOT NULL," +
				"bg_color INTEGER NOT NULL)");
		myDatabase.execSQL("ALTER TABLE Bookmarks ADD COLUMN style_id INTEGER NOT NULL REFERENCES HighlightingStyle(style_id) DEFAULT 1");
		myDatabase.execSQL("UPDATE Bookmarks SET end_paragraph = LENGTH(bookmark_text)");
	}

	private void updateTables24() {
		myDatabase.execSQL("INSERT OR REPLACE INTO HighlightingStyle (style_id, name, bg_color) VALUES (1, '', 136*256*256 + 138*256 + 133)"); // #888a85
		myDatabase.execSQL("INSERT OR REPLACE INTO HighlightingStyle (style_id, name, bg_color) VALUES (2, '', 245*256*256 + 121*256 + 0)"); // #f57900
		myDatabase.execSQL("INSERT OR REPLACE INTO HighlightingStyle (style_id, name, bg_color) VALUES (3, '', 114*256*256 + 159*256 + 207)"); // #729fcf
	}
}


text:
@import UIKit;

@interface DRPLoadingSpinnerTimingFunction : NSObject

+ (CAMediaTimingFunction *)linear;
+ (CAMediaTimingFunction *)easeIn;
+ (CAMediaTimingFunction *)easeOut;
+ (CAMediaTimingFunction *)easeInOut;
+ (CAMediaTimingFunction *)sharpEaseInOut;

@end

redpajama_set_name: RedPajamaGithub


text:
    <meta http-equiv="content-type" content="text/html; charset=UTF-8"><title>OptionGadget</title>

OptionGadget()

语法

Result = OptionGadget(#Gadget, x, y, Width, Height, Text$)

概要

Creates an OptionGadget in the current GadgetList.

参数

#Gadget

A number to identify the new gadget. #PB_Any 可以用来自动生成这个编码.

x, y, Width, Height

The position and dimensions of the new gadget.

Text$

The text to display.

返回值

Returns nonzero on success and zero on failure. If #PB_Any was used as the #Gadget parameter
then the return-value is the auto-generated gadget number on success.

备注

The first time this function is called,
a group is created and all following calls of OptionGadget() will add a gadget to
this group. To finish the group, just create a gadget of another type. These kind of gadgets
are very useful as only one gadget from the group can be selected at any time.

A 'mini help' can be added to this gadget using GadgetToolTip().

示例

  If OpenWindow(0, 0, 0, 140, 110, "OptionGadget", #PB_Window_SystemMenu | #PB_Window_ScreenCentered)
    OptionGadget(0, 30, 20, 60, 20, "Option 1")
    OptionGadget(1, 30, 45, 60, 20, "Option 2")
    OptionGadget(2, 30, 70, 60, 20, "Option 3")
    SetGadgetState(1, 1)   ; set second option as active one
    Repeat : Until WaitWindowEvent() = #PB_Event_CloseWindow
  EndIf

参阅

GetGadgetText(), SetGadgetText(), GetGadgetState(), SetGadgetState(), CheckBoxGadget()

已支持操作系统
所有

<- OpenGadgetList() - Gadget Index - PanelGadget() ->


text:
package jlibs.nio.util;

import jlibs.core.util.AbstractIterator;

import java.io.IOException;
import java.net.Inet4Address;
import java.net.InetAddress;
import java.net.NetworkInterface;
import java.net.SocketException;
import java.util.*;
import java.util.stream.Stream;
import java.util.stream.StreamSupport;

/**
 * Helper class to iterate through local ip addresses
 * with streaming support
 *
 * @author Santhosh Kumar Tekuri
 */
public class IPAddresses extends AbstractIterator<InetAddress>{
    private List<Enumeration<NetworkInterface>> interfaces = new ArrayList<>();
    private Enumeration<InetAddress> addresses = Collections.emptyEnumeration();

    public IPAddresses() throws SocketException{
        interfaces.add(NetworkInterface.getNetworkInterfaces());
    }

    public IPAddresses(NetworkInterface ni){
        interfaces.add(Collections.enumeration(Collections.singleton(ni)));
    }

    @Override
    protected Object computeNext(){
        while(true){
            if(addresses.hasMoreElements())
                return addresses.nextElement();
            if(interfaces.isEmpty())
                return NO_MORE_ELEMENTS;
            while(!interfaces.isEmpty()){
                Enumeration<NetworkInterface> enumer = interfaces.get(interfaces.size()-1);
                if(enumer.hasMoreElements()){
                    NetworkInterface ni = enumer.nextElement();
                    interfaces.add(ni.getSubInterfaces());
                    addresses = ni.getInetAddresses();
                    break;
                }else
                    interfaces.remove(interfaces.size()-1);
            }
        }
    }

    public Stream<InetAddress> stream() throws IOException{
        int characteristics = Spliterator.NONNULL;
        Spliterator<InetAddress> spliterator = Spliterators.spliteratorUnknownSize(this, characteristics);
        return StreamSupport.stream(spliterator, false);
    }
}


text:
void renderScene();
void renderCell(int x, int y);
void renderText(int x, int y, const char* text);

// Event handling
void onReshape(int w, int h);
void onMouse(int x, int y);
void onMouseClick(int, int, int x, int y);
void onKeyboard(unsigned char key, int x, int y);

// Misc utility
typedef struct point
{
    float x;
    float y;
} point_t;

point_t screenToGrid(float x, float y);
point_t gridToScreen(float x, float y);

// }}}


text:
using namespace System;

namespace Microsoft
{
	namespace TeamFoundation
	{
		namespace Migration
		{
			namespace SubversionAdapter
			{
				namespace Interop
				{
					namespace Subversion
					{
						public ref class SvnError
						{
						private:
							/// <summary>
							/// The constructor is private to ensure that the class cannot be instantiiated
							/// </summary>
							SvnError() { }

							/// <summary>
							/// This method is used to log all svn related errors
							/// </summary>
							static void LogSvnError(svn_error_t* svnError);

						public:
							/// <summary>
							/// Analyzes the svnError object
							/// </summary>
							static void Err(svn_error_t* svnError);
						};
					}
				}
			}
		}
	}
}


text:
<!DOCTYPE html>

    <title></title>
    <link href="../style/docs.min.css" rel="stylesheet" />

AtomListBox

        AtomListBox is derived from AtomItemsControl, it acts as ListBox and DataGrid. Providing simple itemTemplate makes it work like ListBox and providing Table as template makes it look like DataGrid. Other classes like AtomToggleButtonBar and AtomLinkBar are derived from AtomListBox and their styles and templates are different.

AutoSelectOnClick

By default this is true, it means, when any part of item is clicked, item will be selected. However if you need only one button or checkbox to work for selection, then it can be customized in following ways.

Note: To set any atom property as false, it must always be set in curley braces so that we identify it as javascript expression, otherwise in javascript string with "false" is considered as true.

Using Button as Selector

Using Checkbox as Selector for Multiple Selection

Display as DataGrid

DataGrid with Sorting

In this example, sorting is performed at client side, however if you want sorting to be performed at server side, all you have to do is bind $scope.sortPath in AtomPromise.json method's query parameter.


text:
<?php

namespace PragmaRX\Sdk\Core\Events;

abstract class UserEvent {

	public $user;

	function __construct($user)
	{
		$this->user = $user;
	}

}


text:
title:  Ježiš – Prostredník lepšej zmluvy
date:  06/01/2022
---

>

> 8Keď ich karhá, hovorí: Hľa, prichádzajú dni, hovorí Pán, a uzavriem s domom Izraela a s domom Júdu novú zmluvu. 9Nie takú zmluvu, ako som uzavrel s ich otcami v ten deň, keď som ich vzal za ruku, aby som ich vyviedol z egyptskej krajiny. Oni totiž nezotrvali pri mojej zmluve a ja som ich opustil, hovorí Pán. 10Lebo toto je zmluva, ktorú uzavriem s domom Izraela po tých dňoch, hovorí Pán. Svoje zákony vložím do ich mysle a napíšem ich na ich srdcia. Budem ich Bohom a oni budú mojím ľudom. 11A nikto už nebude poúčať svojho blížneho ani brat svojho brata slovami: "Poznaj Pána!", lebo ma budú poznať všetci, od najmenšieho po najväčšieho, 12pretože sa zľutujem nad ich neprávosťami a na ich hriechy si viac nespomeniem.

**Osobné štúdium**

List Hebrejom sa v kapitolách 8 až 10 zameriava na dielo Ježiša ako Sprostredkovateľa novej zmluvy. Problémom starej zmluvy bolo vlastne iba to, že bola predzvesťou všetkého dobrého, čo malo prísť. Jej ustanovenia mali predznamenávať, zobrazovať a opisovať dielo, ktoré Ježiš vykoná v budúcnosti. Kňazi poukazovali na Ježiša, ale oni sami boli smrteľní a hriešni. Nemohli prinášať dokonalosť, ktorú priniesol Ježiš. Okrem toho slúžili vo svätyni, ktorá bola len "obrazom a tieňom" (Heb 8,5) nebeskej svätyne.

Ježiš koná svoju službu v ozajstnej svätyni a umožňuje nám prístup k Bohu. Obete zvierat predstavovali Ježišovu smrť ako obeť za nás. Ich krv však nemohla očistiť svedomie človeka. Ježišova krv očisťuje aj naše svedomie. Vďaka Ježišovi, vierou v neho a prijatím jeho sprostredkovateľskej služby za nás, môžeme k Bohu pristupovať so smelou dôverou (Heb 10,19–22).

`Uvažuj o úvodnom texte (Heb 8,8–12). Čo nám Boh zasľúbil v novej zmluve? Aký to má praktický vplyv na tvoj život?`

Tým, že Otec ustanovil Ježiša za nášho Veľkňaza, uviedol do života novú zmluvu, ktorá uskutoční to, čo stará zmluva mohla iba naznačovať. Nová zmluva prináša to, čo môže uskutočniť len dokonalý, večný a božsko-ľudský kňaz. Tento Veľkňaz nielenže vysvetľuje a učí Boží zákon, ale ho aj vkladá do našich sŕdc. Tento Veľkňaz ponúka obeť, ktorá prináša odpustenie. Tento Veľkňaz nás očisťuje a premieňa. Z našich kamenných sŕdc robí srdcia "z mäsa" (Ez 36,26). Robí z nás nové stvorenie (2Kor 5,17). Tento kňaz nás požehnáva tým najobdivuhodnejším spôsobom – privádza nás do prítomnosti samotného Otca.

Boh ustanovil starú zmluvu, aby poukazovala na budúce dielo Ježiša Krista. Vo svojom zámere a cieli bola nádherná. Mnohí však prišli o požehnania, ktoré im Ježiš ponúkol svojou službou, pretože neboli ochotní opustiť symboly a tiene starej zmluvy a prijať pravdu, na ktorú tieto symboly ukazovali.

"Kristus bol základom i životom chrámu. Chrámové služby boli predobrazom obete Božieho Syna. Kňazská služba mala predstavovať prostrednícku povahu Kristovho diela. Celý systém obetnej bohoslužby bol symbolom Spasiteľovej smrti na vykúpenie sveta. Všetky obete stratia svoj zmysel pri onej veľkej udalosti, ktorú tieto obete po celé veky názorne sprítomňovali." (DA 165; TV105)

**Aplikácia**

`V čom je Ježiš lepší Veľkňaz, ako boli veľkňazi starej zmluvy?`

---

#### Dodatečné otázky k diskuzi

`Co je tou "starou smlouvou"? Kdo, kdy a s kým ji uzavřel?`

`Proč chce Ježíš s křesťany uzavřít novou smlouvu?`

`Proč je třeba, aby byly jeho zákony vepsány do naší mysli i srdce?`

`Jak to dopadá, když má někdo zákon zapsaný jen ve své mysli?`

`Jak to dopadá, když má někdo zákon zapsaný jen ve svém srdci?`

`Jak se mění tvůj život v závislosti na tom, jak roste tvé poznání Boha?`

`Proč potřebujeme Ježíše k uzavření této lepší smlouvy?`

`Cílem naší proměny je být  dokonalým "obrazem" Boha? Věříš, že to může být i tvůj případ? Proč?`

`Co je pro takovou proměnu potřeba? V čem spočívá?`


text:
@interface UIViewController (BeautifyPrivate)

-(void)createRenderer;

@end


text:
package org.ow2.contrail.federation.federationapi.resources;

import org.ow2.contrail.federation.federationapi.interfaces.BaseSingle;

import javax.ws.rs.*;
import javax.ws.rs.core.Response;

/**
 * @author ales
 */
public interface IApplicationResource extends BaseSingle {

    @GET
    @Produces("application/json")
    @Path("/ovfs")
    public Response getOvfs() throws Exception;

    /**
     * Gets the OVF.
     *
     * @return
     */
    @GET
    @Produces("application/json")
    @Path("/ovfs/{ovfId}")
    public Response getOvf(@PathParam("ovfId") int ovfId) throws Exception;

    /**
     * Gets the OVF.
     *
     * @return
     */
    @DELETE
    @Produces("application/json")
    @Path("/ovfs/{ovfId}")
    public Response deleteOvf(@PathParam("ovfId") int ovfId) throws Exception;

}


text:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Store extends Model
{
  public function product() {
    return $this->belongsToMany('App\Product');
  }
}


text:
@extends('layouts.app')

@section('content')

            ServicioCrm

        @include('adminlte-templates::common.errors')

                    {!! Form::open(['route' => 'admin.servicioCrms.store']) !!}

                        @include('admin.servicioCrms.fields')

                    {!! Form::close() !!}

@endsection


text:
<?php namespace MaddHatter\LaravelFullcalendar;

use Illuminate\Support\ServiceProvider as BaseServiceProvider;

class ServiceProvider extends BaseServiceProvider
{

    /**
     * Register the service provider.
     *
     * @return void
     */
    public function register()
    {
        $this->app->bind('laravel-fullcalendar', function ($app) {
            return $app->make('MaddHatter\LaravelFullcalendar\Calendar');
        });
    }

    public function boot()
    {
        $this->loadViewsFrom(__DIR__ . '/../../views/', 'fullcalendar');
    }

    /**
     * Get the services provided by the provider.
     *
     * @return array
     */
    public function provides()
    {
        return ['laravel-fullcalendar'];
    }

}


text:
# Database Modelling
## Creating E/R Diagrams with SQL Developer

Databses with Java

Telerik School Academy

    http://academy.telerik.com

# Table of Contents
- Data Modelling – Principles
-	Data Types in Oracle
-	Creating Databases in Oracle
-	Creating Tables
-	Defining a Primary Key and Identity Columns
-	Creating Relationships between the Tables
	-	One-to-many, Many-to-many, One-to-one
-	Naming Conventions

# Relational Data Modelling
## Fundamental Concepts

# Steps in Database Design
*	Steps in the database design process:
	1.	Identification of the entities
	1.	Identification of the columns in the tables
	1.	Defining a primary key for each entity table
	1.	Identification and modelling of relationships
		-	Multiplicity of relationships
	1.	Defining other constraints
	1.	Filling test data in the tables

# Identification of Entities
-	Entity tables represent objects from the real world
-	Most often they are nouns in the specification
-	For example:

```xml
We need to develop a system that stores information about
students, which are trained in various courses. The courses
are held in different towns. When registering a new student
the following information is entered: name, faculty number,
photo and date.
```
*	Entities: Student, Course, Town

# Identification of Columns
-	Columns in the tables are characteristics of the entities
	-	They have name and type
-	For example students have:
	-	Name (text)
	-	Faculty number (number)
	-	Photo (binary block)
	-	Date of enlistment (date)

# Identification of the Columns
-	Columns are clarifications for the entities in the text of the specification, for example:

```xml
We need to develop a system that stores information about
students, which are trained in various courses. The courses
are held in different towns. When registering a new student
the following information is entered: name, faculty number,
photo and date.
```

-	Students have the following characteristics:
	-	Name, faculty number, photo, date of enlistment and a list of courses they visit

# How to Choose a Primary Key?
-	Always define an additional column for the primary key
	-	Don't use an existing column (for example SSN)
	-	Must be an integer number
	-	Must be declared as a primary key
	-	Use `identity` to implement auto-increment
	-	Put the primary key as a first column
-	Exceptions
	-	Entities that have well known ID, e.g. countries (BG, DE, US) and currencies (USD, EUR, BGN)

# Identification of Relationships
-	Relationships are dependencies between the entities:

```xml
We need to develop a system that stores information about
students, which are trained in various courses. The courses
are held in different towns. When registering a new student
the following information is entered: name, faculty number,
photo and date.
```

-	"`Students are trained in courses`" – many-to-many relationship
-	"`Courses are held in towns`" – many-to-one (or many-to-many) relationship

# Data Types in Oracle

-	Character Datatypes
	-	`CHAR(size)` – fixed size string
	-	`VARCHAR2(size)` and `VARCHAR(size)` – variable size string
	-	`NVARCHAR2(size)` and `NCHAR(size)` – Unicode variable size string
	-	`LONG` - variable-length character data (up to 2 gigabytes)
	-	`CLOB` and `NCLOB` – text data block (8 terabytes of character data)

-	`NUMBER`
	-	Positive numbers from 1 x 10-130 to 9.99...9 x 10125
	-	Negative numbers from -1 x 10-130 to 9.99...99 x 10125
	 -	Up to **38 significant digits**
	-	Syntax: `column_name NUMBER (precision, scale)`
	-	[How Scale Factors Affect Numeric Data Storage](https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#g23242)
-	`Floating-Point Numbers`
	 -	`BINARY_FLOAT`
	   - 32-bit single-precision floating-point number
	 -	`BINARY_DOUBLE`
  	 -	64-bit double-precision floating-point number

-	`DATE`
	-	stores point-in-time values (**dates** and **times**)
	-	stores the **year** (including the century), the **month**, the **day**, the **hours**, the **minutes**, and the **seconds** (after midnight)
-	`LOB` Datatypes
  -	`BLOB`, `CLOB`, `NCLOB`, and `BFILE`
	-	The maximum size of a `LOB` is **128 terabytes**

-	Other types
	-	`TIMESTAMP` – automatically generated number whenever a change is made to the data row
	-	`XML` – data in XML format
	-	`URI`

-	`NULL` and `NOT NULL` types
	-	All columns are `Nullable` if `NOT NULL` or `PRIMARY KEY`
-	`PRIMARY KEY` columns
	-	Uniquely defines a record
	-	Cannot be Null
-	`IDENTITY` columns **available on Oracle 12c**
	-	Automatically increased values when a new row is inserted (auto-increment values)
	-	Used in combination with `PRIMARY KEY`

# Database Modeling with SQL Developer
## Creating Database

# Connecting to the Database
- Create Database using `cmd` > `dbca`
  - Run as administrator
-	Create new Connection in SQL Developer

## [Demo]()

# Database Modelling with SQL Developer
## Creating E/R Diagrams

# Creating an E/R diagram
-	In the "Database Diagrams" menu choose the "New Database Diagram"

-	We can choose from the existing tables, which we want to add to the diagram

# Database Modeling with SQL Developer
## Creating Tables

# Creating Tables
-	If the database doesn't show immediately in Object Explorer perform "Refresh" [F5]
-	Creating new table:

# Creating Tables
-	Enter table name  and define the table columns (name and type):

# Creating Tables
*	Defining a primary key

# Creating Tables
-	It is a good practice to set the name of the table at the time it is created

# Database Modeling with SQL Developer
## Creating Relationships between Tables

# Creating Relationships
-	To create one-to-many relationship drag the foreign key column onto the other table
	-	Drag from the child table to the parent table

# Database Modeling with SQL Developer
## Naming Conventions

# Naming Conventions
-	Tables
	-	Each word is capitalized (Pascal Case)
	-	In English, plural
	-	Examples: `Users`, `PhotoAlbums`, `Countries`
-	Columns
	-	In English, singular
	-	Each word is capitalized (Pascal Case)
	-	Avoid reserved words (e.g. `key`, `int`, `date`)
	-	Examples: `FirstName`, `OrderDate`, `Price`

# Naming Conventions
-	Primary key
	-	Use "`Id`" or `name_of_the_table` + "Id"
	-	Example: in the `Users` table the PK column should be be called `Id` or `UserId`
-	Foreign key
	-	Use the name of the referenced table + "`Id`"
	-	Example: in the `Users` table the foreign key column that references the `Groups` table should be named `GroupId`

# Naming Conventions
-	Relationship names (constraints)
	-	In English, Pascal Case
	-	"`FK_`" + `table1` + "`_`" + `table2`
	-	For example: `FK_Users_Groups`
-	Index names
	-	"`IX_`" + `table` + `column`
	-	For example: `IX_Users_UserName`

# Naming Conventions
-	Unique key constraints names
	-	"`UK_`" + `table` + `column`
	-	For instance: `UK_Users_UserName`
-	Views names
	-	`V_` + `name`
	-	Example: `V_BGCompanies`
-	Stored procedures names
	-	`usp_` + `name`
	-	Example: `usp_InsertCustomer(@name)`

# Database Modelling with SQL Developer
## [Demo]()

# Questions
## Database Modeling


text:
<?php

namespace Pimcore;

class ExtensionManager {

    /**
     * @var \Zend_Config
     */
    private static $config;

    /**
     * @static
     * @return \Zend_Config
     */
    public static function getConfig () {
        if(!self::$config) {
            try {
                self::$config = new \Zend_Config_Xml(PIMCORE_CONFIGURATION_DIRECTORY . "/extensions.xml", null, array("allowModifications" => true));
            }
            catch (\Exception $e) {
                self::$config = new \Zend_Config(array(), true);
            }
        }
        return self::$config;
    }

    /**
     * @static
     * @param \Zend_Config $config
     * @return void
     */
    public static function setConfig (\Zend_Config $config) {

        self::$config = $config;

        $writer = new \Zend_Config_Writer_Xml(array(
            "config" => $config,
            "filename" => PIMCORE_CONFIGURATION_DIRECTORY . "/extensions.xml"
        ));
        $writer->write();
    }

    /**
     * @static
     * @param  $type
     * @param  $id
     * @return bool
     */
    public static function isEnabled ($type, $id) {
        $config = self::getConfig();
        if($config->$type) {
            return (bool) $config->$type->$id;
        }
        return false;
    }

    /**
     * @static
     * @param  $type
     * @param  $id
     * @return void
     */
    public static function enable ($type, $id) {
        $config = self::getConfig();
        if(!isset($config->$type)) {
            $config->$type = new \Zend_Config(array(), true);
        }
        $config->$type->$id = true;
        self::setConfig($config);

        // call enable.php inside the extension
        $extensionDir = self::getPathForExtension($id, $type);
        $enableScript = $extensionDir . "/enable.php";
        if(is_file($enableScript)) {
            include($enableScript);
        }
    }

    /**
     * @static
     * @param  $type
     * @param  $id
     * @return void
     */
    public static function disable ($type, $id) {
        $config = self::getConfig();
        if(!isset($config->$type)) {
            $config->$type = new \Zend_Config(array(), true);
        }
        $config->$type->$id = false;
        self::setConfig($config);

        // call disable.php inside the extension
        $extensionDir = self::getPathForExtension($id, $type);
        $disableScript = $extensionDir . "/disable.php";
        if(is_file($disableScript)) {
            include($disableScript);
        }
    }

    /**
     * @return Array $pluginConfigs
     */
    public static function getPluginConfigs() {

        $pluginConfigs = array();

        if (is_dir(PIMCORE_PLUGINS_PATH) && is_readable(PIMCORE_PLUGINS_PATH)) {
            $pluginDirs = scandir(PIMCORE_PLUGINS_PATH);
            if (is_array($pluginDirs)) {
                foreach ($pluginDirs as $d) {
                    if ($d != "." and $d != ".." and is_dir(PIMCORE_PLUGINS_PATH . "//" . $d)) {
                        if (file_exists(PIMCORE_PLUGINS_PATH . "/" . $d . "/plugin.xml")) {
                            try {
                                $pluginConf = new \Zend_Config_Xml(PIMCORE_PLUGINS_PATH . "/" . $d . "/plugin.xml");
                                if ($pluginConf != null) {
                                    $pluginConfigs[] = $pluginConf->toArray();
                                }
                            } catch (\Exception $e) {
                                \Logger::error("Unable to initialize plugin with ID: " . $d);
                                \Logger::error($e);
                            }
                        }
                    }
                }
            }
        }
        return $pluginConfigs;
    }

    /**
     * @param $id
     * @throws \Exception
     */
    public static function getPluginConfig ($id) {

        $pluginConfigs = self::getPluginConfigs();

        foreach ($pluginConfigs as $config) {
            if($config["plugin"]["pluginName"] == $id) {
                return $config;
            }
        }

        throw new \Exception("Plugin with id: " . $id . " does not exists");
    }

    /**
     * @param null $customPath
     * @return array|mixed
     */
    public static function getBrickDirectories ($customPath = null) {

        $cacheKey = "brick_directories";
        if($customPath) {
            $cacheKey .= "_" . crc32($customPath);
        }

        $areas = array();
        try
        {
            $areas = \Zend_Registry::get($cacheKey);
        }
        catch  (\Exception $e) {
            if($customPath) {
                $areaRepositories = array($customPath);
            } else {
                $areaRepositories = array(
                    PIMCORE_WEBSITE_PATH . "/views/areas",
                    PIMCORE_WEBSITE_VAR . "/areas"
                );
            }

            // include area repositories from active plugins
            $configs = ExtensionManager::getPluginConfigs();
            foreach ($configs as $config) {
                $className = $config["plugin"]["pluginClassName"];

                if (!empty($className)) {
                    $isEnabled = ExtensionManager::isEnabled("plugin", $config["plugin"]["pluginName"]);
                    $areaDir = PIMCORE_PLUGINS_PATH . "/" . $config["plugin"]["pluginName"] . "/views/areas";

                    if ($isEnabled && file_exists($areaDir)) {
                        $areaRepositories[] = $areaDir;
                    }
                }
            }

            // get directories
            foreach ($areaRepositories as $respository) {

                if(is_dir($respository) && is_readable($respository)) {
                    $blockDirs = scandir($respository);

                    foreach ($blockDirs as $blockDir) {
                        if(is_dir($respository . "/" . $blockDir)) {
                            if(is_file($respository . "/" . $blockDir . "/area.xml")) {
                                $areas[$blockDir] = $respository . "/" . $blockDir;
                            }
                        }
                    }
                }
            }
            \Zend_Registry::set($cacheKey, $areas);
        }

        return $areas;
    }

    /**
     * @param null $customPath
     * @return array|mixed
     */
    public static function getBrickConfigs($customPath = null) {

        $cacheKey = "brick_configs";
        if($customPath) {
            $cacheKey .= "_" . crc32($customPath);
        }

        try {
            $configs = \Zend_Registry::get($cacheKey);
        } catch (\Exception $e) {
            $configs = array();

            foreach (self::getBrickDirectories($customPath) as $areaName => $path) {
                try {
                    $configs[$areaName] = new \Zend_Config_Xml($path . "/area.xml");
                } catch (\Exception $e) {
                    \Logger::error("Unable to initalize brick with id: " . $areaName);
                    \Logger::error($e);
                }
            }

            \Zend_Registry::set($cacheKey, $configs);
        }

        return $configs;
    }

    /**
     * @param $id
     * @throws \Exception
     */
    public static function getBrickConfig ($id) {

        $brickConfigs = self::getBrickConfigs();

        foreach ($brickConfigs as $brickId => $config) {
            if($brickId == $id) {
                return $config;
            }
        }

        throw new \Exception("Areabrick with id: " . $id . " does not exists");
    }

    /**
     * @param $id
     * @param $type
     */
    public static function delete ($id, $type) {
        if($type == "plugin") {
            $pluginDir = PIMCORE_PLUGINS_PATH . "/" . $id;
            if(is_writeable($pluginDir)) {
                recursiveDelete($pluginDir,true);
            }
        } else if ($type == "brick") {
            $brickDirs = self::getBrickDirectories();
            $brickDir = $brickDirs[$id];

            if(is_writeable($brickDir)) {
                recursiveDelete($brickDir,true);
            }
        }
    }

    /**
     * @param $id
     * @param $type
     * @return string
     */
    public static function getPathForExtension($id, $type) {

        $extensionDir = "";

        if($type == "plugin") {
            $extensionDir = PIMCORE_PLUGINS_PATH . "/" . $id;
        } else if ($type == "brick") {
            $brickDirs = self::getBrickDirectories();
            $extensionDir = $brickDirs[$id];
        }

        return $extensionDir;
    }
}


text:
package org.scaladebugger.api.profiles.java.info

import com.sun.jdi._
import org.scaladebugger.api.profiles.traits.info.ThreadStatusInfo

/**
 * Represents a java implementation of a thread status profile that adds no
 * custom logic on top of the standard JDI.
 *
 * @param _threadReference The reference to the underlying JDI thread
 */
class JavaThreadStatusInfo(
  private val _threadReference: ThreadReference
) extends ThreadStatusInfo {
  /**
   * Represents the status code for the thread.
   *
   * @return The status code as a number
   */
  override def statusCode: Int = _threadReference.status()

  /**
   * Indicates whether or not the status of this thread is known.
   *
   * @return False if the status is known, otherwise true
   */
  override def isUnknown: Boolean =
    statusCode == ThreadReference.THREAD_STATUS_UNKNOWN

  /**
   * Indicates whether or not this thread is a zombie.
   *
   * @return True if a zombie, otherwise false
   */
  override def isZombie: Boolean =
    statusCode == ThreadReference.THREAD_STATUS_ZOMBIE

  /**
   * Indicates whether or not this thread is running.
   *
   * @return True if running, otherwise false
   */
  override def isRunning: Boolean =
    statusCode == ThreadReference.THREAD_STATUS_RUNNING

  /**
   * Indicates whether or not this thread is sleeping.
   *
   * @return True if sleeping, otherwise false
   */
  override def isSleeping: Boolean =
    statusCode == ThreadReference.THREAD_STATUS_SLEEPING

  /**
   * Indicates whether or not this thread is monitoring.
   *
   * @return True if monitoring, otherwise false
   */
  override def isMonitor: Boolean =
    statusCode == ThreadReference.THREAD_STATUS_MONITOR

  /**
   * Indicates whether or not this thread is waiting.
   *
   * @return True if waiting, otherwise false
   */
  override def isWait: Boolean =
    statusCode == ThreadReference.THREAD_STATUS_WAIT

  /**
   * Indicates whether or not this thread has started.
   *
   * @return True if has started, otherwise false
   */
  override def isNotStarted: Boolean =
    statusCode == ThreadReference.THREAD_STATUS_NOT_STARTED

  /**
   * Indicates whether or not this thread is suspended at a breakpoint.
   *
   * @return True if suspended at a breakpoint, otherwise false
   */
  override def isAtBreakpoint: Boolean = _threadReference.isAtBreakpoint

  /**
   * Indicates the total number of times this thread has been suspended.
   *
   * @return The total number of pending suspensions
   */
  override def suspendCount: Int = _threadReference.suspendCount()

  /**
   * Indicates whether or not this thread is suspended.
   *
   * @return True if suspended, otherwise false
   */
  override def isSuspended: Boolean = _threadReference.isSuspended
}


text:
<?php
// Get the PHP helper library from https://twilio.com/docs/libraries/php
require_once '/path/to/vendor/autoload.php'; // Loads the library

use Twilio\Rest\Client;

// Your Account Sid and Auth Token from twilio.com/user/account
// To set up environmental variables, see http://twil.io/secure
$accountSid = getenv('TWILIO_ACCOUNT_SID');
$authToken = getenv('TWILIO_AUTH_TOKEN');
$workspaceSid = "WSXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
$workflowSid = "WWXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";

$client = new Client($accountSid, $authToken);

$workflow = $client->taskrouter
    ->workspaces($workspaceSid)
    ->workflows($workflowSid)
    ->fetch();

echo $workflow->friendlyName;


text:
<resources xmlns:android="http://schemas.android.com/apk/res/android">

    <!--
        Base application theme, dependent on API level. This theme is replaced
        by AppBaseTheme from res/values-vXX/styles.xml on newer devices.
    -->
    <style name="AppBaseTheme" parent="android:Theme.Holo.Light">
        <!--
            Theme customizations available in newer API levels can go in
            res/values-vXX/styles.xml, while customizations related to
            backward-compatibility can go here.
        -->
    </style>

    <!-- Application theme. -->
    <style name="AppTheme" parent="AppBaseTheme">
        <!-- All customizations that are NOT specific to a particular API-level can go here. -->
    </style>

    <style name="elementText" parent="@android:style/TextAppearance.DeviceDefault.Medium">
        <item name="android:paddingLeft">0dp</item>
        <item name="android:textColor">#006778</item>
        <item name="android:textStyle">bold</item>
        <item name="android:textSize">30dp</item>
    </style>

</resources>


text:
module Faker
  module Company
    extend ModuleUtils
    extend self

    def name
      case rand(3)
      when 0 then "#{Name.last_name} #{suffix}"
      when 1 then "#{Name.last_name}-#{Name.last_name}"
      when 2 then "%s, %s and %s" % [ Name.last_name, Name.last_name, Name.last_name ]
      end
    end

    def suffix
      SUFFIXES.rand
    end

    # Generate a buzzword-laden catch phrase.
    # Wordlist from http://www.1728.com/buzzword.htm
    def catch_phrase
      "#{CATCH_PRE.rand} #{CATCH_MID.rand} #{CATCH_POS.rand}"
    end

    # When a straight answer won't do, BS to the rescue!
    # Wordlist from http://dack.com/web/bullshit.html
    def bs
      "#{BS_PRE.rand} #{BS_MID.rand} #{BS_POS.rand}"
    end

    def position
      case rand(3)
      when 0 then [POSITION_PREFIXES.rand, POSITIONS.rand]
      when 1 then [POSITION_AREAS.rand, POSITIONS.rand]
      when 2 then [POSITION_PREFIXES.rand, POSITION_AREAS.rand, POSITIONS.rand]
      end.join(' ')
    end

    SUFFIXES = k %w(Inc and\ Sons LLC Group)

    POSITION_PREFIXES = k %w[Executive Assistant General Associate]
    POSITIONS = k %w[President Manager Director Secretary Consultant]
  end
end


text:
<template name="ReleaseDashboardProjectCard">

      {{title}}

      {{#if cardTemplate}}
        {{> Template.dynamic template=cardTemplate }}
      {{else}}

Next Release

        {{#with nextRelease}}
          {{> ReleaseLink releaseId=_id showDate=true }}
        {{else}}

TBD

        {{/with}}

Last Release

        {{#with latestRelease}}
          {{> ReleaseLink releaseId=_id showDate=true }}
        {{else}}
        {{/with}}

Upcoming Releases

        {{#if futureReleases.length}}
          {{#each futureReleases}}
            {{> ReleaseLink releaseId=_id showDate=true }}
          {{/each}}
        {{else}}

TBD

        {{/if}}
      {{/if}}

</template>


text:
package com.restfb.util;

import static org.assertj.core.api.Assertions.assertThat;
import static org.assertj.core.api.Fail.failBecauseExceptionWasNotThrown;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.junit.jupiter.api.Test;

class StringUtilsTest {

  @Test
  void integerParseTest_null() {
    assertThat(StringUtils.toInteger(null)).isNull();
  }

  @Test
  void integerParseTest_number() {
    assertThat(StringUtils.toInteger("23")).isEqualTo(23);
  }

  @Test
  void integerParseTest_String() {
    assertThat(StringUtils.toInteger("bla")).isNull();
  }

  @Test
  void joinArrayTest() {
    assertThat(String.join(",", new String[] { "foo", "bar" })).isEqualTo("foo,bar");
  }

  @Test
  void joinListTest_List() {
    List<String> myStrings = new ArrayList<>();
    myStrings.add("foo");
    myStrings.add("bar");
    assertThat(String.join(",", myStrings)).isEqualTo("foo,bar");
  }

  @Test
  void toString_null() {
    try {
      StringUtils.toString(null);
      failBecauseExceptionWasNotThrown(NullPointerException.class);
    } catch (NullPointerException npe) {
      assertThat(npe).hasMessage("Parameter 'data' cannot be null.");
    }
  }

  @Test
  void toString_array() {
    assertThat(StringUtils.toString("abc".getBytes())).contains("abc");
  }

  @Test
  void toBytes_null() {
    try {
      StringUtils.toBytes(null);
      failBecauseExceptionWasNotThrown(NullPointerException.class);
    } catch (NullPointerException npe) {
      assertThat(npe).hasMessage("Parameter 'string' cannot be null.");
    }
  }

  @Test
  void toBytes_String() {
    assertThat(StringUtils.toBytes("abc")).contains('a', 'b', 'c');
  }

  @Test
  void fromInputStream_null() throws IOException {
    assertThat(StringUtils.fromInputStream(null)).isNull();
  }
}


text:
#include "twoDModelScene.h"
#include <QtWidgets/QGraphicsSceneMouseEvent>
#include <QtGui/QKeyEvent>
#include <QtGui/QPainter>

#include <qrkernel/settingsManager.h>
#include <qrutils/graphicsUtils/gridDrawer.h>
#include <qrutils/deleteLaterHelper.h>

#include "robotItem.h"

#include "twoDModel/engine/model/model.h"
#include "src/engine/items/wallItem.h"
#include "src/engine/items/curveItem.h"
#include "src/engine/items/stylusItem.h"
#include "src/engine/items/rectangleItem.h"
#include "src/engine/items/ellipseItem.h"
#include "src/engine/items/regions/regionItem.h"
#include "src/engine/items/startPosition.h"

using namespace twoDModel;
using namespace view;
using namespace qReal;
using namespace graphicsUtils;

TwoDModelScene::TwoDModelScene(model::Model &model
		, AbstractView *view
		, QObject *parent)
	: AbstractScene(view, parent)
	, mModel(model)
	, mDrawingAction(none)
{
	mFirstPenWidth = 6;
	mSizeEmptyRectX = 1000;
	mSizeEmptyRectY = 1000;
	setEmptyRect(-500, -500, mSizeEmptyRectX, mSizeEmptyRectY);
	setItemIndexMethod(NoIndex);
	setEmptyPenBrushItems();

	connect(&mModel.worldModel(), &model::WorldModel::wallAdded, this, &TwoDModelScene::onWallAdded);
	connect(&mModel.worldModel(), &model::WorldModel::colorItemAdded, this, &TwoDModelScene::onColorItemAdded);
	connect(&mModel.worldModel(), &model::WorldModel::regionItemAdded, [=](items::RegionItem *item) { addItem(item); });
	connect(&mModel.worldModel(), &model::WorldModel::traceItemAdded, [=](QGraphicsLineItem *item) { addItem(item); });
	connect(&mModel.worldModel(), &model::WorldModel::itemRemoved, this, &TwoDModelScene::onItemRemoved);

	connect(&mModel, &model::Model::robotAdded, this, &TwoDModelScene::onRobotAdd);
	connect(&mModel, &model::Model::robotRemoved, this, &TwoDModelScene::onRobotRemove);
}

TwoDModelScene::~TwoDModelScene()
{
}

bool TwoDModelScene::oneRobot() const
{
	return mRobots.size() == 1;
}

void TwoDModelScene::setInteractivityFlags(kitBase::ReadOnlyFlags flags)
{
	mWorldReadOnly = (flags & kitBase::ReadOnly::World) != 0;
	mRobotReadOnly = (flags & kitBase::ReadOnly::RobotPosition) != 0;
	mSensorsReadOnly = (flags & kitBase::ReadOnly::Sensors) != 0;

	for (const auto item : items()) {
		const auto robotItem = dynamic_cast<RobotItem *>(item);
		const auto sensorItem = dynamic_cast<SensorItem *>(item);
		const auto worldItem = dynamic_cast<items::ColorFieldItem *>(item);
		const auto startPosition = dynamic_cast<items::StartPosition *>(item);
		if (worldItem) {
			worldItem->setEditable(!mWorldReadOnly);
		} else if (robotItem) {
			robotItem->setEditable(!mRobotReadOnly);
		} else if (sensorItem) {
			sensorItem->setEditable(!mSensorsReadOnly);
		} else if (startPosition) {
			startPosition->setEditable(!mRobotReadOnly);
		}
	}
}

void TwoDModelScene::handleNewRobotPosition(RobotItem *robotItem)
{
	for (const items::WallItem *wall : mModel.worldModel().walls()) {
		if (wall->realShape().intersects(robotItem->realBoundingRect())) {
			robotItem->recoverDragStartPosition();
			return;
		}
	}
}

void TwoDModelScene::onRobotAdd(model::RobotModel *robotModel)
{
	RobotItem * const robotItem = new RobotItem(robotModel->info().robotImage(), *robotModel);

	connect(robotItem, &RobotItem::changedPosition, this, &TwoDModelScene::handleNewRobotPosition);
	connect(robotItem, &RobotItem::mousePressed, this, &TwoDModelScene::robotPressed);
	connect(robotItem, &RobotItem::drawTrace, &mModel.worldModel(), &model::WorldModel::appendRobotTrace);

	robotItem->setEditable(!mRobotReadOnly);

	addItem(robotItem);
	addItem(robotItem->robotModel().startPositionMarker());

	mRobots.insert(robotModel, robotItem);

	emit robotListChanged(robotItem);
}

void TwoDModelScene::onRobotRemove(model::RobotModel *robotModel)
{
	RobotItem *robotItem = mRobots[robotModel];

	removeItem(robotItem);
	mRobots.remove(robotModel);

	delete robotItem;

	emit robotListChanged(nullptr);
}

void TwoDModelScene::onWallAdded(items::WallItem *wall)
{
	addItem(wall);
	connect(wall, &items::WallItem::wallDragged, this, &TwoDModelScene::worldWallDragged);
	connect(wall, &items::WallItem::deletedWithContextMenu, this, &TwoDModelScene::deleteSelectedItems);
	wall->setEditable(!mWorldReadOnly);
}

void TwoDModelScene::onColorItemAdded(graphicsUtils::AbstractItem *item)
{
	addItem(item);
	connect(item, &graphicsUtils::AbstractItem::deletedWithContextMenu, this, &TwoDModelScene::deleteSelectedItems);
	item->setEditable(!mWorldReadOnly);
}

void TwoDModelScene::onItemRemoved(QGraphicsItem *item)
{
	mGraphicsItem = nullptr;
	removeItem(item);
	// We delete the item not immediately cause in other handlers of WorldModel`s itemRemoved() signal
	// it may still be used.
	utils::DeleteLaterHelper<QGraphicsItem>::deleteLater(item);
}

void TwoDModelScene::mousePressEvent(QGraphicsSceneMouseEvent *mouseEvent)
{
	if (mouseEvent->button() != Qt::LeftButton) {
		AbstractScene::mousePressEvent(mouseEvent);
		return;
	}

	const QPointF position = mouseEvent->scenePos();

	emit mousePressed();

	auto initItem = [this, mouseEvent](QGraphicsItem *item) {
		removeMoveFlag(mouseEvent, item);
		// This will deselect all items
		setSelectionArea(QPainterPath());
	};

	auto initColorField = [this, mouseEvent, &initItem](items::ColorFieldItem *item) {
		initItem(item);
		item->setPenBrush(penStyleItems(), penWidthItems(), penColorItems()
				, brushStyleItems(), brushColorItems());
		mModel.worldModel().addColorField(item);
	};

	for (RobotItem * const robotItem : mRobots.values()) {
		if (!robotItem->realBoundingRect().contains(position)) {
			switch (mDrawingAction) {
			case wall:
				mCurrentWall = new items::WallItem(position, position);
				initItem(mCurrentWall);
				mModel.worldModel().addWall(mCurrentWall);
				break;
			case line:
				mCurrentLine = new items::LineItem(position, position);
				initColorField(mCurrentLine);
				break;
			case bezier:
				mCurrentCurve = new items::CurveItem(position, position);
				initColorField(mCurrentCurve);
				break;
			case stylus:
				mCurrentStylus = new items::StylusItem(position.x(), position.y());
				initColorField(mCurrentStylus);
				break;
			case rectangle:
				mCurrentRectangle = new items::RectangleItem(position, position);
				initColorField(mCurrentRectangle);
				break;
			case ellipse:
				mCurrentEllipse = new items::EllipseItem(position, position);
				initColorField(mCurrentEllipse);
				break;
			default:
				break;
			}
		}
	}

	if (mDrawingAction == none) {
		forPressResize(mouseEvent);
	}

	AbstractScene::mousePressEvent(mouseEvent);
}

void TwoDModelScene::mouseMoveEvent(QGraphicsSceneMouseEvent *mouseEvent)
{
	bool needUpdate = true;
	switch (mDrawingAction) {
	case wall:
		reshapeWall(mouseEvent);
		break;
	case line:
		reshapeLine(mouseEvent);
		break;
	case bezier:
		reshapeCurve(mouseEvent);
		break;
	case stylus:
		reshapeStylus(mouseEvent);
		break;
	case rectangle:
		reshapeRectangle(mouseEvent);
		break;
	case ellipse:
		reshapeEllipse(mouseEvent);
		break;
	default:
		needUpdate = false;
		if (mouseEvent->buttons() & Qt::LeftButton) {
			forMoveResize(mouseEvent);
		}

		AbstractScene::mouseMoveEvent(mouseEvent);
		break;
	}

	if (needUpdate) {
		update();
	}
}

void TwoDModelScene::mouseReleaseEvent(QGraphicsSceneMouseEvent *mouseEvent)
{
	if (mouseEvent->button() != Qt::LeftButton) {
		AbstractScene::mouseReleaseEvent(mouseEvent);
		return;
	}

	emit mouseReleased();

	// After dragging item may be null. We mustn`t select it in that case.
	QGraphicsItem *itemToSelect = nullptr;
	switch (mDrawingAction){
	case wall: {
		reshapeWall(mouseEvent);
		itemToSelect = mCurrentWall;
		mCurrentWall = nullptr;
		break;
	}
	case line: {
		reshapeLine(mouseEvent);
		itemToSelect = mCurrentLine;
		mCurrentLine = nullptr;
		break;
	}
	case bezier: {
		reshapeCurve(mouseEvent);
		itemToSelect = mCurrentCurve;
		mCurrentCurve = nullptr;
		break;
	}
	case stylus: {
		reshapeStylus(mouseEvent);
		itemToSelect = mCurrentStylus;
		mCurrentStylus = nullptr;
		break;
	}
	case rectangle: {
		reshapeRectangle(mouseEvent);
		itemToSelect = mCurrentRectangle;
		mCurrentRectangle = nullptr;
		break;
	}
	case ellipse: {
		reshapeEllipse(mouseEvent);
		itemToSelect = mCurrentEllipse;
		mCurrentEllipse = nullptr;
		break;
	}
	default:
		if (itemToSelect) {
			forReleaseResize(mouseEvent);
		}
		break;
	}

	if (itemToSelect) {
		itemToSelect->setSelected(true);
	}

	setMoveFlag(mouseEvent);

	for (RobotItem * const robotItem : mRobots) {
		setSceneRect(sceneRect().united(robotItem->sceneBoundingRect()));
	}

	update();
	AbstractScene::mouseReleaseEvent(mouseEvent);
}

void TwoDModelScene::deleteItem(QGraphicsItem *item)
{
	if (!items().contains(item)) {
		return;
	}

	if (SensorItem * const sensor = dynamic_cast<SensorItem *>(item)) {
		for (RobotItem * const robotItem : mRobots.values()) {
			const kitBase::robotModel::PortInfo port = robotItem->sensors().key(sensor);
			if (port.isValid()) {
				deviceConfigurationChanged(robotItem->robotModel().info().robotId()
						, port, kitBase::robotModel::DeviceInfo(), Reason::userAction);
			}
		}
	} else if (items::WallItem * const wall = dynamic_cast<items::WallItem *>(item)) {
		mModel.worldModel().removeWall(wall);
		mCurrentWall = nullptr;
	} else if (items::ColorFieldItem *colorField = dynamic_cast<items::ColorFieldItem *>(item)) {
		mModel.worldModel().removeColorField(colorField);
		mCurrentLine = nullptr;
		mCurrentStylus = nullptr;
		mCurrentEllipse = nullptr;
	}
}

void TwoDModelScene::deleteSelectedItems()
{
	for (QGraphicsItem * const item : selectedItems()) {
		deleteItem(item);
	}
}

void TwoDModelScene::reshapeItem(QGraphicsSceneMouseEvent *event)
{
	setX2andY2(event);
	if (mGraphicsItem && mGraphicsItem->editable()) {
		const QPointF oldBegin(mGraphicsItem->x1(), mGraphicsItem->y1());
		const QPointF oldEnd(mGraphicsItem->x2(), mGraphicsItem->y2());
		if (mGraphicsItem->dragState() != graphicsUtils::AbstractItem::None) {
			mView->setDragMode(QGraphicsView::NoDrag);
		}

		mGraphicsItem->resizeItem(event);

		QPainterPath shape;

		for (RobotItem * const robotItem : mRobots.values()) {
			shape.addRect(robotItem->realBoundingRect());
		}

		if (dynamic_cast<items::WallItem *>(mGraphicsItem) && mGraphicsItem->realShape().intersects(shape)) {
			mGraphicsItem->reverseOldResizingItem(oldBegin, oldEnd);
		}
	}
}

void TwoDModelScene::keyPressEvent(QKeyEvent *event)
{
	if (event->key() == Qt::Key_Delete && (selectedItems().size() > 0)) {
		for (QGraphicsItem * const item : selectedItems()) {
			const bool isWorldItem = dynamic_cast<items::ColorFieldItem *>(item)
					|| dynamic_cast<items::WallItem *>(item);
			const bool isRobotItem = dynamic_cast<RobotItem *>(item) != nullptr;
			const bool isSensorItem = dynamic_cast<SensorItem *>(item) != nullptr;
			if (isWorldItem && mWorldReadOnly) {
				return;
			} else if (isRobotItem && mRobotReadOnly) {
				return;
			} else if (isSensorItem && mSensorsReadOnly) {
				return;
			}

			deleteItem(item);
		}
	} else {
		QGraphicsScene::keyPressEvent(event);
	}
}

void TwoDModelScene::drawBackground(QPainter *painter, const QRectF &rect)
{
	if (SettingsManager::value("2dShowGrid").toBool()) {
		mWidthOfGrid = SettingsManager::value("GridWidth").toReal() / 100;
		painter->setPen(QPen(Qt::black, mWidthOfGrid));
		QGraphicsScene::drawBackground(painter, rect);
		const int cellSize = SettingsManager::value("2dGridCellSize").toInt();
		mGridDrawer.drawGrid(painter, rect, cellSize);
	}
}

void TwoDModelScene::addWall()
{
	mDrawingAction = wall;
}

void TwoDModelScene::addLine()
{
	mDrawingAction = line;
}

void TwoDModelScene::addBezier()
{
	mDrawingAction = bezier;
}

void TwoDModelScene::addStylus()
{
	mDrawingAction = stylus;
}

void TwoDModelScene::addRectangle()
{
	mDrawingAction = rectangle;
}

void TwoDModelScene::addEllipse()
{
	mDrawingAction = ellipse;
}

void TwoDModelScene::setNoneStatus()
{
	mDrawingAction = none;
}

void TwoDModelScene::clearScene(bool removeRobot, Reason reason)
{
	mModel.worldModel().clear();

	for (model::RobotModel *robotModel : mRobots.keys()) {
		robotModel->clear();
		if (removeRobot) {
			for (const kitBase::robotModel::PortInfo &port : robot(*robotModel)->sensors().keys()) {
				deviceConfigurationChanged(robotModel->info().robotId()
						, port, kitBase::robotModel::DeviceInfo(), reason);
			}
		}
	}
}

void TwoDModelScene::reshapeWall(QGraphicsSceneMouseEvent *event)
{
	const QPointF pos = event->scenePos();
	if (mCurrentWall) {
		const QPointF oldPos = mCurrentWall->end();
		mCurrentWall->setX2(pos.x());
		mCurrentWall->setY2(pos.y());
		if (SettingsManager::value("2dShowGrid").toBool()) {
			mCurrentWall->reshapeBeginWithGrid(SettingsManager::value("2dGridCellSize").toInt());
			mCurrentWall->reshapeEndWithGrid(SettingsManager::value("2dGridCellSize").toInt());
		} else {
			const QPainterPath shape = mCurrentWall->realShape();

			for (RobotItem * const robotItem : mRobots.values()) {
				if (shape.intersects(robotItem->realBoundingRect())) {
					mCurrentWall->setX2(oldPos.x());
					mCurrentWall->setY2(oldPos.y());
					break;
				}
			}

			if (event->modifiers() & Qt::ShiftModifier) {
				mCurrentWall->reshapeRectWithShift();
			}
		}
	}
}

void TwoDModelScene::reshapeLine(QGraphicsSceneMouseEvent *event)
{
	const QPointF pos = event->scenePos();
	if (mCurrentLine) {
		mCurrentLine->setX2(pos.x());
		mCurrentLine->setY2(pos.y());
		if (event->modifiers() & Qt::ShiftModifier) {
			mCurrentLine->reshapeRectWithShift();
		}
	}
}

void TwoDModelScene::reshapeCurve(QGraphicsSceneMouseEvent *event)
{
	const QPointF pos = event->scenePos();
	if (mCurrentCurve) {
		mCurrentCurve->setX2(pos.x());
		mCurrentCurve->setY2(pos.y());
		if (event->modifiers() & Qt::ShiftModifier) {
			mCurrentCurve->reshapeRectWithShift();
		}
	}
}

void TwoDModelScene::reshapeStylus(QGraphicsSceneMouseEvent *event)
{
	const QPointF pos = event->scenePos();
	if (mCurrentStylus) {
		mCurrentStylus->addLine(pos.x(), pos.y());
	}
}

void TwoDModelScene::reshapeRectangle(QGraphicsSceneMouseEvent *event)
{
	const QPointF pos = event->scenePos();
	if (mCurrentRectangle) {
		mCurrentRectangle->setX2(pos.x());
		mCurrentRectangle->setY2(pos.y());
		if (event->modifiers() & Qt::ShiftModifier) {
			mCurrentRectangle->reshapeRectWithShift();
		}
	}
}

void TwoDModelScene::reshapeEllipse(QGraphicsSceneMouseEvent *event)
{
	const QPointF pos = event->scenePos();
	if (mCurrentEllipse) {
		mCurrentEllipse->setX2(pos.x());
		mCurrentEllipse->setY2(pos.y());
		if (event->modifiers() & Qt::ShiftModifier) {
			mCurrentEllipse->reshapeRectWithShift();
		}
	}
}

void TwoDModelScene::worldWallDragged(items::WallItem *wall, const QPainterPath &shape, const QRectF &oldPos)
{
	bool isNeedStop = false;

	for (RobotItem * const robotItem : mRobots.values()) {
		if (shape.intersects(robotItem->realBoundingRect())) {
			isNeedStop = true;
			break;
		}
	}

	wall->onOverlappedWithRobot(isNeedStop);
	if (wall->isDragged() && ((mDrawingAction == none) ||
			(mDrawingAction == TwoDModelScene::wall && mCurrentWall == wall)))
	{
		wall->setFlag(QGraphicsItem::ItemIsMovable, !isNeedStop);
		if (isNeedStop) {
			wall->setCoordinates(oldPos);
		}
	}
}

void TwoDModelScene::alignWalls()
{
	if (SettingsManager::value("2dShowGrid").toBool()) {
		for (items::WallItem * const wall : mModel.worldModel().walls()) {
			if (items().contains(wall)) {
				wall->alignTheWall(SettingsManager::value("2dGridCellSize").toInt());
			}
		}
	}
}

RobotItem *TwoDModelScene::robot(model::RobotModel &robotModel)
{
	return mRobots.value(&robotModel);
}

void TwoDModelScene::centerOnRobot(RobotItem *selectedItem)
{
	RobotItem *robotItem = mRobots.values().first();

	if (selectedItem) {
		robotItem = selectedItem;
	}

	for (QGraphicsView * const view : views()) {
		const QRectF viewPortRect = view->mapToScene(view->viewport()->rect()).boundingRect();
		if (!viewPortRect.contains(robotItem->sceneBoundingRect().toRect())) {
			const QRectF requiredViewPort = viewPortRect.translated(robotItem->scenePos() - viewPortRect.center());
			setSceneRect(itemsBoundingRect().united(requiredViewPort));
			view->centerOn(robotItem);
		}
	}
}