~vono22/narau/trunk

/*
 *  Copyright (C) 2012 Yvon TANGUY
 *
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package org.vono.narau.db;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;

import org.vono.narau.R;
import org.vono.narau.common.Common;
import org.vono.narau.models.dictionary.Entry;
import org.vono.narau.models.dictionary.KanjiElement;
import org.vono.narau.models.dictionary.ReadingElement;
import org.vono.narau.models.dictionary.Sense;
import org.vono.narau.preferences.Preferences;
import org.vono.narau.utils.Language;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.util.SparseArray;

/**
 * Function to manage the dictionaries.
 * 
 */
public class DictionaryDB extends CommonDB {
	private static final String TAG = DictionaryDB.class.getSimpleName();

	static final int VALUE_DB_VERSION = 2;

	private static final String TABLE_KANJI_ELEM = "k_ele";
	private static final String TABLE_READING_ELEM = "r_ele";
	private static final String TABLE_SENSE = "sense";
	private static final String TABLE_GLOSS = "gloss";

	private static final String COL_ID = "id";
	private static final String COL_KEB = "keb";
	private static final String COL_REB = "reb";
	private static final String COL_PRI = "pri";
	private static final String COL_INF = "inf";
	private static final String COL_NUM = "num";
	private static final String COL_STAGK = "stagk";
	private static final String COL_STAGR = "stagr";
	private static final String COL_POS = "pos";
	private static final String COL_XREF = "xref";
	private static final String COL_GLOSS_TEXT = "gloss";

	private static final String[] ARRAY_COL_ID = { COL_ID };

	/**
	 * COL_KEB, COL_PRI, COL_INF
	 */
	private static final String[] ARRAY_COLS_KELE = { COL_KEB, COL_PRI, COL_INF };

	/**
	 * COL_REB, COL_PRI, COL_INF
	 */
	private static final String[] ARRAY_COLS_RELE = { COL_REB, COL_PRI, COL_INF };

	/**
	 * COL_STAGK, COL_STAGR, COL_POS, COL_XREF
	 */
	private static final String[] ARRAY_COLS_SENSE = { COL_STAGK, COL_STAGR, COL_POS, COL_XREF, COL_NUM };

	/**
	 * COL_GLOSS_TEXT
	 */
	private static final String[] ARRAY_COLS_GLOSS = { COL_GLOSS_TEXT };

	/**
	 * A big where clause, to match only word (which can have a space or a
	 * slash, before and after.
	 */
	private static final String WHERE_LIKE_WORDMATCH_SELECTION = "%1$s LIKE ? " + "OR %1$s LIKE ? " + "OR %1$s LIKE ? "
			+ "OR %1$s LIKE ? " + "OR %1$s LIKE ? " + "OR %1$s LIKE ? " + "OR %1$s LIKE ? " + "OR %1$s LIKE ? "
			+ "OR %1$s LIKE ?";
	private static final String[] WHERE_LIKE_WORDMATCH_SELECTION_ARGS = { "%1$s", "%1$s %%", "%1$s/%%", "%% %1$s",
			"%%/%1$s", "%% %1$s %%", "%%/%1$s/%%", "%%/%1$s %%", "%% %1$s/%%" };
	private static final String WHERE_LIKE = "%1$s LIKE ?";
	private static final String WHERE_ID_EQUAL = COL_ID + " = ?";
	private static final String WHERE_ID_NUM_EQUAL = WHERE_ID_EQUAL + " AND " + COL_NUM + " = ?";

	// Database management:
	private static InfosDB mainDB = null;
	private static final ArrayList<InfosDB> langsDB = new ArrayList<InfosDB>();
	private static DatabaseState state;
	private static SparseArray<Entry> loadedEntries = null;

	/**
	 * Init the DictionaryDB.state value
	 */
	static void initDB() {
		DictionaryDB.state = DatabaseState.bad;

		boolean haveMain = false;
		boolean haveLang = false;
		final ArrayList<InfosDB> listDBs = CommonDB.listDBInfos;
		final int size = listDBs.size();
		for (int i = 0; i < size; i++) {
			final InfosDB db = listDBs.get(i);
			if (db.type == DatabaseType.dictonary) {
				if (Language.JAPANESE.equals(db.language)) {
					haveMain = true;
					DictionaryDB.mainDB = db;
				} else {
					haveLang = true;
					DictionaryDB.langsDB.add(db);
				}
			}
		}
		if (haveMain && haveLang) {
			DictionaryDB.state = DatabaseState.good;
		}

		DictionaryDB.loadedEntries = new SparseArray<Entry>();
	}

	protected static void cleanDB() {
		DictionaryDB.state = DatabaseState.bad;
		DictionaryDB.langsDB.clear();
		DictionaryDB.mainDB = null;
		if (null != loadedEntries) {
			DictionaryDB.loadedEntries.clear();
			DictionaryDB.loadedEntries = null;
		}
	}

	static boolean isReady() {
		return DictionaryDB.state == DatabaseState.good;
	}

	/**
	 * Can be called only by Preferences.getLangsPriorities()<br/>
	 * Use Preferences.getLangsPriorities() instead
	 * 
	 * @return All available translation languages in the dictionary database.
	 */
	public static ArrayList<Language> getLangs() {

		ArrayList<Language> languages = null;
		if (DatabaseState.good == DictionaryDB.state) {
			languages = new ArrayList<Language>(langsDB.size());
			for (InfosDB infosDB : langsDB) {
				languages.add(infosDB.language );
			}
		}
		Collections.sort(languages);

		return languages;
	}

	private static ArrayList<InfosDB> getSortedListLangDB() {
		final ArrayList<InfosDB> listLangDB = new ArrayList<InfosDB>();
		final ArrayList<Language> languages = Preferences.getLangsPriorities(DatabaseType.dictonary);

		// Quick & Dirty
		for (Language language : languages) {
			for (InfosDB dbInfos : DictionaryDB.langsDB) {
				if (language.equals(dbInfos.language)) {
					listLangDB.add(dbInfos);
				}
			}
		}

		return listLangDB;
	}

	public static Entry loadEntry(int id) {
		Entry entry = loadedEntries.get(id);
		if (null == entry) {
			final String[] selectionArgs = new String[1];
			selectionArgs[0] = Integer.toString(id);

			// get kanji element(s)
			ArrayList<KanjiElement> k_ele = DictionaryDB.loadKanjiElements(selectionArgs);

			// get reading element(s)
			ArrayList<ReadingElement> r_ele = DictionaryDB.loadReadingElements(selectionArgs);

			// get sense element(s)
			ArrayList<Sense> senses = DictionaryDB.loadSenseElements(selectionArgs);

			// Build entry:
			entry = new Entry(id);
			entry.k_ele = k_ele;
			entry.r_ele = r_ele;
			entry.senses = senses;

			loadedEntries.put(id, entry);
		}

		return entry;
	}

	private static ArrayList<KanjiElement> loadKanjiElements(String[] selectionArgs) {
		ArrayList<KanjiElement> k_ele = null;
		Cursor cursor = mainDB.db.query(TABLE_KANJI_ELEM, ARRAY_COLS_KELE, WHERE_ID_EQUAL, selectionArgs, null, null,
				COL_NUM);
		if (cursor.moveToFirst()) {
			k_ele = new ArrayList<KanjiElement>(cursor.getCount());
			do {
				KanjiElement ke = new KanjiElement();
				ke.keb = cursor.getString(0);

				String tmp = cursor.getString(1);
				if (null != tmp) {
					ke.ke_pri = Common.splitString(tmp, Common.SLASH);
				}

				tmp = cursor.getString(2);
				if (null != tmp) {
					ke.ke_inf = Common.splitString(tmp, Common.SLASH);
				}

				k_ele.add(ke);

			} while (cursor.moveToNext());
		}
		cursor.close();

		return k_ele;
	}

	private static ArrayList<ReadingElement> loadReadingElements(String[] selectionArgs) {
		ArrayList<ReadingElement> r_ele = null;
		Cursor cursor = mainDB.db.query(TABLE_READING_ELEM, ARRAY_COLS_RELE, WHERE_ID_EQUAL, selectionArgs, null, null,
				COL_NUM);
		if (cursor.moveToFirst()) {
			r_ele = new ArrayList<ReadingElement>(cursor.getCount());

			do {
				ReadingElement re = new ReadingElement();
				re.reb = cursor.getString(0);

				String tmp = cursor.getString(1);
				if (null != tmp) {
					re.re_pri = Common.splitString(tmp, Common.SLASH);
				}

				tmp = cursor.getString(2);
				if (null != tmp) {
					re.re_inf = Common.splitString(tmp, Common.SLASH);
				}
				r_ele.add(re);

			} while (cursor.moveToNext());

		} else {
			Log.e(TAG, "no reading element for: " + selectionArgs[0]);
		}
		cursor.close();

		return r_ele;
	}

	private static ArrayList<Sense> loadSenseElements(String[] selectionArgs) {
		ArrayList<Sense> senses = null;
		Cursor cursor = mainDB.db.query(TABLE_SENSE, ARRAY_COLS_SENSE, WHERE_ID_EQUAL, selectionArgs, null, null,
				COL_NUM);
		if (cursor.moveToFirst()) {
			senses = new ArrayList<Sense>(cursor.getCount());

			final ArrayList<InfosDB> lLangsDB = DictionaryDB.langsDB;
			final int numLangs = lLangsDB.size();

			final String[] glossSelectionArgs = new String[2];
			glossSelectionArgs[0] = selectionArgs[0];

			do {
				Sense sense = new Sense();
				sense.stagk = cursor.getString(0);
				sense.stagr = cursor.getString(1);
				sense.pos = cursor.getString(2);
				sense.xref = cursor.getString(3);
				int num = cursor.getInt(4);

				sense.glosses = new HashMap<Language, String>(numLangs);
				glossSelectionArgs[1] = Integer.toString(num);
				for (int i = 0; i < numLangs; i++) {
					final InfosDB infosDB = lLangsDB.get(i);
					String gloss = loadGloss(infosDB, glossSelectionArgs);
					if (null != gloss) {
						sense.glosses.put(infosDB.language, gloss);
					}
				}

				senses.add(sense);
			} while (cursor.moveToNext());
		}
		cursor.close();

		return senses;
	}

	private static String loadGloss(InfosDB infosDB, String[] selectionArgs) {
		String gloss = null;

		Cursor cursor = infosDB.db.query(TABLE_GLOSS, ARRAY_COLS_GLOSS, WHERE_ID_NUM_EQUAL, selectionArgs, null, null,
				null);
		if (cursor.moveToFirst()) {
			gloss = cursor.getString(0);
		}
		cursor.close();

		return gloss;
	}

	/**
	 * Common class for Dictionary iterators.
	 * 
	 */
	private static abstract class InternalIterator implements Iterator<Entry> {
		protected Cursor cursor;
		protected ArrayList<InfosDB> dbLangs;
		protected String limit;

		public InternalIterator() {
			this.cursor = null;
			this.dbLangs = DictionaryDB.getSortedListLangDB();
			this.limit = null;
		}

		@Override
		protected void finalize() throws Throwable {
			this.close();
			super.finalize();
		}

		/**
		 * Close the SQLite connection.
		 */
		public void close() {
			if (null != this.cursor) {
				this.cursor.close();
				this.cursor = null;
			}
		}

		public boolean hasNext() {
			boolean ret = false;
			if (null != this.cursor && !this.cursor.isLast()) {
				ret = true;
			}
			return ret;
		}

		/**
		 * 
		 * @return the number of elements fetch, or -1 if close.
		 */
		public int getCount() {
			if (null != this.cursor && !this.cursor.isClosed()) {
				return this.cursor.getCount();
			}
			return -1;
		}

		/**
		 * throw UnsupportedOperationException()
		 */
		public void remove() {
			throw new UnsupportedOperationException("Can't delete data from the dictionary database");
		}

	}

	/**
	 * The dictionary iterator used by the dictionary activity.
	 */
	public static class DictionaryIterator extends InternalIterator {
		private boolean wordMatch;

		public DictionaryIterator() {
			super();
			super.limit = Integer.toString(Preferences.getInt(R.string.prefNameDictionaryMaxResult));
			this.wordMatch = Preferences.getBoolean(R.string.prefNameDictionaryWordMatch);
		}

		public boolean searchFromJpnKana(String text, boolean withLimit) {
			super.close();

			String selection = String.format(WHERE_LIKE, COL_REB);
			String[] selectionArgs = new String[1];
			if (this.wordMatch) {
				selectionArgs[0] = text;
			} else {
				selectionArgs[0] = "%" + text + "%";
			}

			String limitClause = withLimit ? super.limit : null;

			super.cursor = DictionaryDB.mainDB.db.query(TABLE_READING_ELEM, ARRAY_COL_ID, selection, selectionArgs,
					null, null, null, limitClause);

			return super.cursor.getCount() > 0;
		}

		public boolean searchFromJpnKanji(String text, boolean withLimit) {
			super.close();

			String selection = String.format(WHERE_LIKE, COL_KEB);
			String[] selectionArgs = new String[1];
			if (this.wordMatch) {
				selectionArgs[0] = text;
			} else {
				selectionArgs[0] = "%" + text + "%";
			}

			String limitClause = withLimit ? super.limit : null;

			super.cursor = DictionaryDB.mainDB.db.query(TABLE_KANJI_ELEM, ARRAY_COL_ID, selection, selectionArgs, null,
					null, null, limitClause);

			return super.cursor.getCount() > 0;
		}

		public boolean searchToJpn(String text, Language language, boolean withLimit) {
			super.close();

			SQLiteDatabase db = null;
			for (InfosDB infosDB : DictionaryDB.langsDB) {
				if (infosDB.language.equals(language)) {
					db = infosDB.db;
					break;
				}
			}

			if (null == db) {
				return false;
			}

			final String selection;
			final String[] selectionArgs;
			if (this.wordMatch) {
				selection = String.format(WHERE_LIKE_WORDMATCH_SELECTION, COL_GLOSS_TEXT);
				final int lenWhere = WHERE_LIKE_WORDMATCH_SELECTION_ARGS.length;
				selectionArgs = new String[lenWhere];
				for (int i = 0; i < lenWhere; i++) {
					selectionArgs[i] = String.format(WHERE_LIKE_WORDMATCH_SELECTION_ARGS[i], text);
				}
			} else {
				selection = String.format(WHERE_LIKE, COL_GLOSS_TEXT);
				selectionArgs = new String[1];
				selectionArgs[0] = "%" + text + "%";
			}

			String limitClause = withLimit ? super.limit : null;

			super.cursor = db.query(TABLE_GLOSS, ARRAY_COL_ID, selection, selectionArgs, null, null, null, limitClause);

			return super.cursor.getCount() > 0;
		}

		public Entry next() {
			Entry entry = null;

			if (null != super.cursor && !super.cursor.isClosed() && cursor.moveToNext()) {
				int id = cursor.getInt(0);
				entry = DictionaryDB.loadEntry(id);
			}
			return entry;
		}

	}

	/**
	 * An iterator for the KanjiToWords activity.
	 * 
	 */
	public static class KanjiToWordsIterator extends InternalIterator {

		private static final String[] ARRAY_COLS_KANJI = { "id", "keb", "pri" };
		private static final String[] ARRAY_COLS_READING = { "reb" };
		private static final String SELECTION_KANJI = "keb like ? and num=?";
		private static final String SELECTION_READING = "id=?";
		private static final String SELECTION_GLOSS = "id=? and num=?";
		private static final String ORDER_READING = "num asc";
		private static final String ORDER_GLOSS = "num asc";

		public KanjiToWordsIterator() {
			super();
			super.limit = Integer.toString(Preferences.getInt(R.string.prefNameKanjiToWordsNWTL));
		}

		/**
		 * 
		 * @param codepoint
		 *            Set the current kanji to look words
		 * @param noLimit
		 *            If true, remove the limit of results in the query
		 * @return true if there is some words founds
		 */
		public boolean setCodepoint(int codepoint, boolean noLimit) {

			if (this.cursor != null) {
				this.cursor.close();
			}
			final String[] selectionArgs = new String[2];
			selectionArgs[0] = "%" + new String(Character.toChars(codepoint)) + "%";
			selectionArgs[1] = "0";
			String limitClause = this.limit;
			if (noLimit) {
				limitClause = null;
			}
			this.cursor = DictionaryDB.mainDB.db.query(DictionaryDB.TABLE_KANJI_ELEM,
					KanjiToWordsIterator.ARRAY_COLS_KANJI, KanjiToWordsIterator.SELECTION_KANJI, selectionArgs, null,
					null, null, limitClause);
			return this.cursor.getCount() != 0;
		}

		/**
		 * @return A minimal Dictionary Entry (id/keb/pri, common reading, one
		 *         sense).
		 */
		public Entry next() {
			if (null != this.cursor && this.cursor.moveToNext()) {

				final int id = cursor.getInt(0);
				final String keb = cursor.getString(1);
				final String pri = cursor.getString(2);

				// The kanji element
				Entry entry = new Entry(id);
				entry.partial = true;
				KanjiElement ke = new KanjiElement();
				ke.keb = keb;

				ke.ke_pri = Common.splitString(pri, Common.SLASH);
				entry.k_ele = new ArrayList<KanjiElement>(1);
				entry.k_ele.add(ke);

				// The reading element
				String[] selectionArgs = new String[1];
				selectionArgs[0] = Integer.toString(id); // id=

				Cursor lcursor = DictionaryDB.mainDB.db.query(TABLE_READING_ELEM,
						KanjiToWordsIterator.ARRAY_COLS_READING, KanjiToWordsIterator.SELECTION_READING, selectionArgs,
						null, null, KanjiToWordsIterator.ORDER_READING);
				if (lcursor.moveToFirst()) {
					entry.r_ele = new ArrayList<ReadingElement>(lcursor.getCount());
					do {
						ReadingElement re = new ReadingElement();
						re.reb = lcursor.getString(0);
						entry.r_ele.add(re);
					} while (lcursor.moveToNext());
				}
				lcursor.close();
				// Sense
				selectionArgs = new String[2];
				selectionArgs[0] = Integer.toString(id); // id=
				selectionArgs[1] = Integer.toString(0); // num=

				final ArrayList<InfosDB> lDBLangs = this.dbLangs;
				final int langCount = lDBLangs.size();
				entry.senses = new ArrayList<Sense>();
				for (int i = 0; i < langCount; i++) {
					final InfosDB infosDB = lDBLangs.get(i);
					lcursor = infosDB.db.query(DictionaryDB.TABLE_GLOSS, DictionaryDB.ARRAY_COLS_GLOSS,
							KanjiToWordsIterator.SELECTION_GLOSS, selectionArgs, null, null,
							KanjiToWordsIterator.ORDER_GLOSS);
					if (lcursor.moveToFirst()) {
						Sense sense = new Sense();
						sense.glosses = new HashMap<Language, String>(1);
						sense.glosses.put(infosDB.language, lcursor.getString(0));
						entry.senses.add(sense);
						i = langCount;
					}
					lcursor.close();
				}
				return entry;
			}
			this.cursor.close();
			this.cursor = null;
			return null;
		}

	}

}