gt: 				DomainDefinition d = mDomains.get(i);
context: /*
   * @copyright 2012 Philip Warner
   * @license GNU General Public License
   * 
   * This file is part of Book Catalogue.
   *
   * Book Catalogue 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.
   *
   * Book Catalogue 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 Book Catalogue.  If not, see <http://www.gnu.org/licenses/>.
   */
  package com.eleybourn.bookcatalogue.booklist;
  
  import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.EMPTY_STRING_ARRAY;
  import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_DATE_PUBLISHED;
  import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_LOANED_TO;
  import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.encodeString;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_AUTHOR;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_BOOK;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_DAY_ADDED;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_DAY_READ;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_FORMAT;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_GENRE;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_LANGUAGE;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_LOANED;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_LOCATION;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_MONTH_ADDED;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_MONTH_PUBLISHED;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_MONTH_READ;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_PUBLISHER;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_READ_AND_UNREAD;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_SERIES;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_TITLE_LETTER;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_YEAR_ADDED;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_YEAR_PUBLISHED;
  import static com.eleybourn.bookcatalogue.booklist.BooklistGroup.RowKinds.ROW_KIND_YEAR_READ;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_ABSOLUTE_POSITION;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_ADDED_DATE;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_ADDED_DAY;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_ADDED_MONTH;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_ADDED_YEAR;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_AUTHOR_FORMATTED;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_AUTHOR_ID;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_AUTHOR_POSITION;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_AUTHOR_SORT;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_BOOK;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_BOOKSHELF_NAME;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_BOOK_COUNT;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_BOOK_UUID;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_EXPANDED;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_FAMILY_NAME;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_FORMAT;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_GENRE;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_GIVEN_NAMES;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_ID;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_KIND;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_LANGUAGE;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_LEVEL;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_LOANED_TO;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_LOANED_TO_SORT;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_LOCATION;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_MARK;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_PRIMARY_SERIES_COUNT;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_PUBLICATION_MONTH;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_PUBLICATION_YEAR;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_PUBLISHER;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_READ;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_READ_DAY;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_READ_END;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_READ_MONTH;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_READ_STATUS;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_READ_YEAR;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_REAL_ROW_ID;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_ROOT_KEY;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_SERIES_ID;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_SERIES_NAME;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_SERIES_NUM;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_SERIES_NUM_FLOAT;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_SERIES_POSITION;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_TITLE;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_TITLE_LETTER;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.DOM_VISIBLE;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.TBL_AUTHORS;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.TBL_BOOKS;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.TBL_BOOKSHELF;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.TBL_BOOKS_FTS;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.TBL_BOOK_AUTHOR;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.TBL_BOOK_BOOKSHELF;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.TBL_BOOK_LIST_DEFN;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.TBL_BOOK_LIST_NODE_SETTINGS;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.TBL_BOOK_SERIES;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.TBL_LOAN;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.TBL_ROW_NAVIGATOR_DEFN;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.TBL_ROW_NAVIGATOR_FLATTENED_DEFN;
  import static com.eleybourn.bookcatalogue.booklist.DatabaseDefinitions.TBL_SERIES;
  
  import java.util.ArrayList;
  import java.util.HashSet;
  import java.util.Hashtable;
  import java.util.Locale;
  import java.util.Map.Entry;
  
  import android.database.Cursor;
  import android.database.sqlite.SQLiteCursorDriver;
  import android.database.sqlite.SQLiteDatabase;
  import android.database.sqlite.SQLiteDatabase.CursorFactory;
  import android.database.sqlite.SQLiteDoneException;
  import android.database.sqlite.SQLiteQuery;
  import android.os.Build;
  
  import com.eleybourn.bookcatalogue.BookCatalogueApp;
  import com.eleybourn.bookcatalogue.CatalogueDBAdapter;
  import com.eleybourn.bookcatalogue.OtherPreferences;
  import com.eleybourn.bookcatalogue.R;
  import com.eleybourn.bookcatalogue.booklist.BooklistGroup.BooklistAuthorGroup;
  import com.eleybourn.bookcatalogue.booklist.BooklistGroup.BooklistSeriesGroup;
  import com.eleybourn.bookcatalogue.booklist.BooklistStyle.CompoundKey;
  import com.eleybourn.bookcatalogue.database.DbSync.SynchronizedDb;
  import com.eleybourn.bookcatalogue.database.DbSync.SynchronizedStatement;
  import com.eleybourn.bookcatalogue.database.DbSync.Synchronizer.SyncLock;
  import com.eleybourn.bookcatalogue.database.DbUtils.DomainDefinition;
  import com.eleybourn.bookcatalogue.database.DbUtils.JoinContext;
  import com.eleybourn.bookcatalogue.database.DbUtils.TableDefinition;
  import com.eleybourn.bookcatalogue.database.DbUtils.TableDefinition.TableTypes;
  import com.eleybourn.bookcatalogue.database.SqlStatementManager;
  import com.eleybourn.bookcatalogue.debug.Tracker;
  import com.eleybourn.bookcatalogue.utils.Logger;
  
  
  /**
   * Class used to build and populate temporary tables with details of a flattened book list used to
   * display books in a ListView control and perform operation like 'expand/collapse' on pseudo nodes
   * in the list.
   * 
   * @author Philip Warner
   */
  public class BooklistBuilder {
  	/** Counter for BooklistBuilder IDs */
  	private static Integer mBooklistBuilderIdCounter = 0;
  
  	/**
  	 * Details of extra domain requested by caller before the build() method is called.
  	 * 
  	 * @author Philip Warner
  	 */
  	private class ExtraDomainDetails {
  		/** Domain definition of domain to add */
  		DomainDefinition	domain;
  		/** Expression to use in deriving domain value */
  		String sourceExpression;
  		/** Indicates if domain is to be part of the list sort key */
  		boolean isSorted;	
  	};
  
  	/** Collection of statements created by this Builder */
  	private final SqlStatementManager mStatements;
  	/** Database to use */
  	private final SynchronizedDb mDb;
  	/** Internal ID */
  	private final int mBooklistBuilderId;
  
  	/** List of columns for the group-by clause, including COLLATE clauses. Set by build() method. */
  	//private String mGroupColumnList;
  	/** Collection of 'extra' domains requested by caller */
  	private Hashtable<String, ExtraDomainDetails> mExtraDomains = new Hashtable<String, ExtraDomainDetails>();
  	/** Style to use in building the list */
  	private final BooklistStyle mStyle;
  	/** Local copy of the BOOK_LIST table definition, renamed to match this instance */
  	private TableDefinition mListTable;
  	/** Local copy of the navigation table definition, renamed to match this instance */
  	private TableDefinition mNavTable;
  
  	/** Object used in constructing the output table */
  	private SummaryBuilder mSummary = null;
  
  	/** Statement used to perform initial insert */
  	private SynchronizedStatement mBaseBuildStmt = null;
  	/** Collection of statements used to build remaining data */
  	private ArrayList<SynchronizedStatement> mLevelBuildStmts = null;
  
  	/** Debug counter */
  	private static Integer mInstanceCount = 0;
  
  	/**
  	 * Constructor
  	 * 
  	 * @param adapter	Database Adapter to use
  	 * @param style		Book list style to use
  	 */
  	public BooklistBuilder(CatalogueDBAdapter adapter, BooklistStyle style) {
  		synchronized(mInstanceCount) {
  			mInstanceCount++;
  			System.out.println("Builder instances: " + mInstanceCount);
  		}
  
  		// Allocate ID
  		synchronized(mBooklistBuilderIdCounter) {
  			mBooklistBuilderId = ++mBooklistBuilderIdCounter;
  		}
  		// Get the database and create a statements collection
  		mDb = adapter.getDb();
  		mStatements = new SqlStatementManager(mDb);
  		// Save the requested style
  		mStyle = style;
  
  		// Clone the temp. table definitions and append the ID to make new names in case
  		// more than one view is open.
  		mListTable = TBL_BOOK_LIST_DEFN.clone();
  		mListTable.setName(mListTable.getName() + "_" + getId());
  		mListTable.setType(TableTypes.Temporary); //RELEASE Make sure is TEMPORARY
  
  		mNavTable = TBL_ROW_NAVIGATOR_DEFN.clone()
  				.addReference(mListTable, DOM_REAL_ROW_ID)
  				;
  		mNavTable.setName(mNavTable.getName() + "_" + getId());
  		mNavTable.setType(TableTypes.Temporary); //RELEASE Make sure is TEMPORARY
  	}
  
  	/** Counter for 'flattened' book temp tables */
  	private static Integer mFlatNavCounter = 0;
  	/**
  	 * Construct a flattened table of ordered book IDs based on the underlying list
  	 */
  	public FlattenedBooklist createFlattenedBooklist() {
  		int flatId;
  		synchronized(mFlatNavCounter) {
  			flatId = mFlatNavCounter++;
  		}
  		TableDefinition flat = TBL_ROW_NAVIGATOR_FLATTENED_DEFN.clone();
  		flat.setName(flat.getName() + "_" + flatId);
  		flat.setType(TableTypes.Temporary); //RELEASE Make sure is TEMPORARY
  		flat.create(mDb, true);
  		String sql = flat.getInsert(DOM_ID, DOM_BOOK)
  					+ " select " + mNavTable.dot(DOM_ID) + ", " + mListTable.dot(DOM_BOOK) 
  					+ " From " + mListTable.ref() 
  					+ mListTable.join(mNavTable) 
  					+ " Where " + mListTable.dot(DOM_BOOK) + " Not Null " 
  					+ " Order by " + mNavTable.dot(DOM_ID);
  		mDb.execSQL(sql);
  		return new FlattenedBooklist(mDb, flat);
  	}
  
  	/**
  	 * Accessor.
  	 * 
  	 * @return
  	 */
  	public int getId() {
  		return mBooklistBuilderId;
  	}
  
  	/**
  	 * Add a domain to the resulting flattened list based on the details provided.
  	 * 
  	 * @param domain			Domain to add (used for name)
  	 * @param sourceExpression	Expression to generate date for this column
  	 * @param isSorted			Indicates if it should be added to the sort key
  	 * 
  	 * @return		The builder (to allow chaining)
  	 */
  	public BooklistBuilder requireDomain(DomainDefinition domain, String sourceExpression, boolean isSorted) {
  		// Save the details
  		ExtraDomainDetails info = new ExtraDomainDetails();
  		info.domain = domain;
  		info.sourceExpression = sourceExpression;
  		info.isSorted = isSorted;
  
  		// Check if it already exists
  		if (mExtraDomains.containsKey(domain.name)) {
  			// Make sure it has the same definition.
  			boolean ok = false;
  			ExtraDomainDetails oldInfo = mExtraDomains.get(domain.name);
  			if (oldInfo.sourceExpression == null) {
  				if (info.sourceExpression == null ) {
  					ok = true;
  				} else {
  					ok = info.sourceExpression.equals("");
  				}
  			} else {
  				if (info.sourceExpression == null ) {
  					ok = oldInfo.sourceExpression.equals("");
  				} else {
  					ok = oldInfo.sourceExpression.equalsIgnoreCase(info.sourceExpression);
  				}					
  			}
  			if (!ok)
  				throw new RuntimeException("Required domain '" + domain.name + "' added with differing source expression");
  		} else
  			// Add it.
  			mExtraDomains.put(domain.name, info);
  
  		return this;
  	}
  
  	public static class SortedDomainInfo {
  		DomainDefinition domain;
  		boolean isDescending;
  		SortedDomainInfo(DomainDefinition domain, boolean isDescending) {
  			this.domain = domain;
  			this.isDescending = isDescending;
  		}
  	}
  
  	/**
  	 * Structure used to store components of the SQL required to build the list.
  	 * We use this for experimenting with alternate means of construction.
  	 */
  	private static class SqlComponents {
  		public String destinationColumns;
  		public String select;
  		public String insert;
  		public String insertSelect;
  		public String insertValues;
  		public String rootkeyExpression;
  		public String join;
  		public String where;
  	}
  
  	/**
  	 * Utility class to accumulate date for the build() method.
  	 * 
  	 * @author Philip Warner
  	 */
  	private class SummaryBuilder {
  
  		/** Flag indicating added domain has no special properties */
  		public static final int FLAG_NONE = 0;
  		/** Flag indicating added domain is SORTED */
  		public static final int FLAG_SORTED = 1;
  		/** Flag indicating added domain is GROUPED */
  		public static final int FLAG_GROUPED = 2;
  		// Not currently used.
  		///** Flag indicating added domain is part of the unique key */
  		//public static final int FLAG_KEY = 4;
  		/** Flag indicating added domain should be SORTED in descending order. DO NOT USE FOR GROUPED DATA. See notes below. */
  		public static final int FLAG_SORT_DESCENDING = 8;
  
  		/** Domains required in output table */
  		private ArrayList<DomainDefinition> mDomains = new ArrayList<DomainDefinition>();
  		/** Source expressions for output domains */
  		private ArrayList<String> mExpressions = new ArrayList<String>();
  		/** Mapping from Domain to source Expression */
  		private Hashtable<DomainDefinition, String> mExpressionMap = new Hashtable<DomainDefinition, String>();
  
  		/** Domains that are GROUPED */
  		private ArrayList<DomainDefinition> mGroups = new ArrayList<DomainDefinition>();
  		// Not currently used.
  		///** Domains that form part of accumulated unique key */
  		//private ArrayList<DomainDefinition> mKeys = new ArrayList<DomainDefinition>();
  		/**
  		 * Domains that form part of the sort key. These are typically a reduced set of the GROUP domains since 
  		 * the group domains may contain more than just the key
  		 */
  		private ArrayList<SortedDomainInfo> mSortedColumns = new ArrayList<SortedDomainInfo>();
  		
  		/**
  		 * Add a domain and source expression to the summary.
  		 * 
  		 * @param domain		Domain to add
  		 * @param expression	Source Expression
  		 * @param flags			Flags indicating attributes of new domain
  		 */
  		public void addDomain(DomainDefinition domain, String expression, int flags) {
  			// Add to various collections. We use a map to improve lookups and ArrayLists
  			// so we can preserve order. Order preservation makes reading the SQL easier
  			// but is unimportant for code correctness.
  
  			// Add to table
  			mListTable.addDomain(domain);
  
  			// Domains and Expressions must be synchronized; we should probably use a map.
  			// For now, just check if mExpression is null. If it IS null, it means that
  			// the domain is just for the lowest level of the hierarchy.
  			if (expression != null) {
  				mDomains.add(domain);
  				mExpressions.add(expression);
  				mExpressionMap.put(domain, expression);				
  			}
  
  			// Based on the flags, add the domain to other lists.
  			if ((flags & FLAG_GROUPED) != 0)
  				mGroups.add(domain);
  
  			if ((flags & FLAG_SORTED) != 0) {				
  				mSortedColumns.add(new SortedDomainInfo(domain, (flags & FLAG_SORT_DESCENDING) != 0) );	
  			}
  
  			// Not currently used
  			//if ((flags & FLAG_KEY) != 0)
  			//	mKeys.add(domain);
  		}
  
  		/**
  		 * Return a clone of the CURRENT groups. Since BooklistGroup objects are processed in order, this
  		 * allows us to get the GROUP-BY fields applicable to the currently processed group, including all
  		 * outer groups. Hence why it is cloned -- subsequent domains will modify this collection.
  		 * 
  		 * @return
  		 */
  		@SuppressWarnings("unchecked")
  		public ArrayList<DomainDefinition> cloneGroups() {
  			return (ArrayList<DomainDefinition>)mGroups.clone();				
  		}
  
  		/**
  		 * Return the collection of columns used to sort the output.
  		 * 
  		 * @return
  		 */
  		public ArrayList<SortedDomainInfo> getSortedColumns() {
  			return mSortedColumns;				
  		}
  
  		/**
  		 * Drop and recreate the underlying temp table
  		 */
  		public void recreateTable() {
  			//mListTable.setIsTemporary(true);
  			long t0 = System.currentTimeMillis();
  			mListTable.drop(mDb);
  			long t1 = System.currentTimeMillis();
  			mListTable.create(mDb, false);
  			long t2 = System.currentTimeMillis();
  			System.out.println("Drop = " + (t1-t0));
  			System.out.println("Create = " + (t2-t1));			
  		}
  
  		/**
  		 * Using the collected domain info, create the various SQL phrases used to build the resulting
  		 * flat list table and build the 'INSERT...SELECT...From'  portion of the SQL that does the 
  		 * initial table load.
  		 * 
  		 * @param rootKey	The key for the root level group. Stored in each row and used to determine the
  		 * expand/collapse results.
  		 * 
  		 * @return SqlComponents structure
  		 * 
  		 */
  		public SqlComponents buildSqlComponents(CompoundKey rootKey) {
  			SqlComponents cmp = new SqlComponents();
  
  			// Rebuild the data table
  			recreateTable();
  			// List of column names for the INSERT... part
  			StringBuilder columns = new StringBuilder();
  			// List of expressions for the SELECT... part.
  			StringBuilder expressions = new StringBuilder();
  			// List of ?'s for the VALUES... part.
  			StringBuilder values = new StringBuilder();
  
  			// Build the lists. mDomains and mExpressions were built in synch with each other.
  			for(int i = 0 ; i < mDomains.size(); i++) {
  