org.opendatakit.services.database.utlities.ODKDatabaseImplUtils.java Source code

Java tutorial

Introduction

Here is the source code for org.opendatakit.services.database.utlities.ODKDatabaseImplUtils.java

Source

/*
 * Copyright (C) 2014 University of Washington
 *
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */
package org.opendatakit.services.database.utlities;

import android.content.ContentValues;
import android.database.Cursor;

import com.fasterxml.jackson.core.JsonParseException;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.JsonMappingException;

import org.apache.commons.io.FileUtils;
import org.apache.commons.io.filefilter.IOFileFilter;
import org.opendatakit.aggregate.odktables.rest.ConflictType;
import org.opendatakit.aggregate.odktables.rest.ElementDataType;
import org.opendatakit.aggregate.odktables.rest.ElementType;
import org.opendatakit.aggregate.odktables.rest.KeyValueStoreConstants;
import org.opendatakit.aggregate.odktables.rest.SavepointTypeManipulator;
import org.opendatakit.aggregate.odktables.rest.SyncState;
import org.opendatakit.aggregate.odktables.rest.TableConstants;
import org.opendatakit.aggregate.odktables.rest.entity.Column;
import org.opendatakit.aggregate.odktables.rest.entity.RowFilterScope;
import org.opendatakit.database.DatabaseConstants;
import org.opendatakit.database.LocalKeyValueStoreConstants;
import org.opendatakit.database.data.*;
import org.opendatakit.database.utilities.CursorUtils;
import org.opendatakit.database.utilities.KeyValueStoreUtils;
import org.opendatakit.database.utilities.QueryUtil;
import org.opendatakit.exception.ActionNotAuthorizedException;
import org.opendatakit.logging.WebLogger;
import org.opendatakit.services.database.AndroidConnectFactory;
import org.opendatakit.services.database.OdkConnectionInterface;
import org.opendatakit.utilities.DataHelper;
import org.opendatakit.utilities.LocalizationUtils;
import org.opendatakit.utilities.ODKFileUtils;
import org.opendatakit.utilities.StaticStateManipulator;
import org.opendatakit.utilities.StaticStateManipulator.IStaticFieldManipulator;
import org.opendatakit.database.queries.QueryBounds;
import org.opendatakit.database.RoleConsts;
import org.opendatakit.provider.*;
import org.sqlite.database.sqlite.SQLiteException;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.UUID;

public class ODKDatabaseImplUtils {

    private static final String t = "ODKDatabaseImplUtils";

    /**
     * Constants to minimize creation of String objects on the stack.
     *
     * Used with StringBuilder to reduce GC overhead
     */
    private static final String K_SELECT_FROM = "SELECT * FROM ";
    private static final String S_AND = " AND ";
    private static final String S_EQUALS_PARAM = " =?";
    private static final String S_IS_NULL = " IS NULL";
    private static final String S_IS_NOT_NULL = " IS NOT NULL";
    private static final String K_WHERE = " WHERE ";
    private static final String K_LIMIT = " LIMIT ";
    private static final String K_OFFSET = " OFFSET ";

    private static final String K_TABLE_DEFS_TABLE_ID_EQUALS_PARAM = TableDefinitionsColumns.TABLE_ID
            + S_EQUALS_PARAM;

    private static final String K_COLUMN_DEFS_TABLE_ID_EQUALS_PARAM = ColumnDefinitionsColumns.TABLE_ID
            + S_EQUALS_PARAM;

    private static final String K_KVS_TABLE_ID_EQUALS_PARAM = KeyValueStoreColumns.TABLE_ID + S_EQUALS_PARAM;
    private static final String K_KVS_PARTITION_EQUALS_PARAM = KeyValueStoreColumns.PARTITION + S_EQUALS_PARAM;
    private static final String K_KVS_ASPECT_EQUALS_PARAM = KeyValueStoreColumns.ASPECT + S_EQUALS_PARAM;
    private static final String K_KVS_KEY_EQUALS_PARAM = KeyValueStoreColumns.KEY + S_EQUALS_PARAM;

    private static final String K_DATATABLE_ID_EQUALS_PARAM = DataTableColumns.ID + S_EQUALS_PARAM;

    /**
     * The rolesList expansion is very time consuming.
     * Implement a simple 1-deep cache and a
     * special expansion of the privileged user roles list.
     */
    private static String cachedRolesList;
    private static List<String> cachedRolesArray;
    private static final List<String> cachedAdminRolesArray;
    private static final TypeReference<ArrayList<String>> arrayListTypeReference;

    static {
        arrayListTypeReference = new TypeReference<ArrayList<String>>() {
        };

        ArrayList<String> rolesArray = null;
        {
            try {
                rolesArray = ODKFileUtils.mapper.readValue(RoleConsts.ADMIN_ROLES_LIST, arrayListTypeReference);
            } catch (IOException e) {
                throw new IllegalStateException("this should never happen");
            }
        }
        cachedAdminRolesArray = Collections.unmodifiableList(rolesArray);
    }

    private static List<String> getRolesArray(String rolesList) {

        if (rolesList == null || rolesList.length() == 0) {
            return null;
        } else if (RoleConsts.ADMIN_ROLES_LIST.equals(rolesList)) {
            return cachedAdminRolesArray;
        } else if (cachedRolesList != null && cachedRolesList.equals(rolesList)) {
            return cachedRolesArray;
        }
        // figure out whether we have a privileged user or not
        ArrayList<String> rolesArray = null;
        {
            try {
                rolesArray = ODKFileUtils.mapper.readValue(rolesList, arrayListTypeReference);
            } catch (IOException e) {
                throw new IllegalStateException("this should never happen");
            }
        }
        cachedRolesArray = Collections.unmodifiableList(rolesArray);
        cachedRolesList = rolesList;
        return cachedRolesArray;
    }

    public enum AccessColumnType {
        NO_EFFECTIVE_ACCESS_COLUMN, LOCKED_EFFECTIVE_ACCESS_COLUMN, UNLOCKED_EFFECTIVE_ACCESS_COLUMN
    }

    public static class AccessContext {
        public final AccessColumnType accessColumnType;
        public final boolean canCreateRow;
        public final String activeUser;
        // true if user is a super-user or administrator
        public final boolean isPrivilegedUser;
        public final boolean isUnverifiedUser;
        private final List<String> rolesArray;

        AccessContext(AccessColumnType accessColumnType, boolean canCreateRow, String activeUser,
                List<String> rolesArray) {
            if (activeUser == null) {
                throw new IllegalStateException("activeUser cannot be null!");
            }
            this.accessColumnType = accessColumnType;
            this.canCreateRow = canCreateRow;
            this.activeUser = activeUser;
            this.rolesArray = rolesArray;
            if (rolesArray == null) {
                this.isPrivilegedUser = false;
                this.isUnverifiedUser = true;
            } else {
                this.isPrivilegedUser = rolesArray.contains(RoleConsts.ROLE_SUPER_USER)
                        || rolesArray.contains(RoleConsts.ROLE_ADMINISTRATOR);
                this.isUnverifiedUser = false;
            }
        }

        public boolean hasRole(String role) {
            return (rolesArray == null) ? false : rolesArray.contains(role);
        }

        public AccessContext cloneAsPrivilegedUser() {

            // figure out whether we have a privileged user or not
            List<String> rolesArray = getRolesArray(RoleConsts.ADMIN_ROLES_LIST);

            AccessContext that = new AccessContext(accessColumnType, true, activeUser, rolesArray);
            return that;
        }
    }

    /*
     * These are the columns that are present in any row in the database. Each row
     * should have these in addition to the user-defined columns. If you add a
     * column here you have to be sure to also add it in the create table
     * statement, which can't be programmatically created easily.
     */
    private static final List<String> ADMIN_COLUMNS;

    /**
     * These are the columns that should be exported
     */
    private static final List<String> EXPORT_COLUMNS;

    /**
     * When a KVS change is made, enforce in the database layer that the
     * value_type of some KVS entries is a specific type.  Log an error
     * if the user attempts to do something differently, but correct
     * the error. This is largely for migration / forward compatibility.
     */
    private static final ArrayList<Object[]> knownKVSValueTypeRestrictions = new ArrayList<Object[]>();

    /**
     * Same as above, but quick access via the key.
     * For now, we know that the keys are all unique.
     * Eventually this might need to be a MultiMap.
     */
    private static final TreeMap<String, ArrayList<Object[]>> keyToKnownKVSValueTypeRestrictions = new TreeMap<String, ArrayList<Object[]>>();

    private static void updateKeyToKnownKVSValueTypeRestrictions(Object[] field) {
        ArrayList<Object[]> fields = keyToKnownKVSValueTypeRestrictions.get(field[2]);
        if (fields == null) {
            fields = new ArrayList<Object[]>();
            keyToKnownKVSValueTypeRestrictions.put((String) field[2], fields);
        }
        fields.add(field);
    }

    static {
        ArrayList<String> adminColumns = new ArrayList<String>();
        adminColumns.add(DataTableColumns.ID);
        adminColumns.add(DataTableColumns.ROW_ETAG);
        adminColumns.add(DataTableColumns.SYNC_STATE); // not exportable
        adminColumns.add(DataTableColumns.CONFLICT_TYPE); // not exportable
        adminColumns.add(DataTableColumns.FILTER_TYPE);
        adminColumns.add(DataTableColumns.FILTER_VALUE);
        adminColumns.add(DataTableColumns.FORM_ID);
        adminColumns.add(DataTableColumns.LOCALE);
        adminColumns.add(DataTableColumns.SAVEPOINT_TYPE);
        adminColumns.add(DataTableColumns.SAVEPOINT_TIMESTAMP);
        adminColumns.add(DataTableColumns.SAVEPOINT_CREATOR);
        Collections.sort(adminColumns);
        ADMIN_COLUMNS = Collections.unmodifiableList(adminColumns);

        ArrayList<String> exportColumns = new ArrayList<String>();
        exportColumns.add(DataTableColumns.ID);
        exportColumns.add(DataTableColumns.ROW_ETAG);
        exportColumns.add(DataTableColumns.FILTER_TYPE);
        exportColumns.add(DataTableColumns.FILTER_VALUE);
        exportColumns.add(DataTableColumns.FORM_ID);
        exportColumns.add(DataTableColumns.LOCALE);
        exportColumns.add(DataTableColumns.SAVEPOINT_TYPE);
        exportColumns.add(DataTableColumns.SAVEPOINT_TIMESTAMP);
        exportColumns.add(DataTableColumns.SAVEPOINT_CREATOR);
        Collections.sort(exportColumns);
        EXPORT_COLUMNS = Collections.unmodifiableList(exportColumns);

        // declare the KVS value_type restrictions we know about...
        // This is a list of triples: ( required value type, partition_label, key_label )
        {
            Object[] fields;

            // for columns
            fields = new Object[3];
            fields[0] = ElementDataType.string.name();
            fields[1] = KeyValueStoreConstants.PARTITION_COLUMN;
            fields[2] = KeyValueStoreConstants.COLUMN_DISPLAY_CHOICES_LIST;
            knownKVSValueTypeRestrictions.add(fields);
            updateKeyToKnownKVSValueTypeRestrictions(fields);

            fields = new Object[3];
            fields[0] = ElementDataType.string.name();
            fields[1] = KeyValueStoreConstants.PARTITION_COLUMN;
            fields[2] = KeyValueStoreConstants.COLUMN_DISPLAY_FORMAT;
            knownKVSValueTypeRestrictions.add(fields);
            updateKeyToKnownKVSValueTypeRestrictions(fields);

            fields = new Object[3];
            fields[0] = ElementDataType.object.name();
            fields[1] = KeyValueStoreConstants.PARTITION_COLUMN;
            fields[2] = KeyValueStoreConstants.COLUMN_DISPLAY_NAME;
            knownKVSValueTypeRestrictions.add(fields);
            updateKeyToKnownKVSValueTypeRestrictions(fields);

            fields = new Object[3];
            fields[0] = ElementDataType.bool.name();
            fields[1] = KeyValueStoreConstants.PARTITION_COLUMN;
            fields[2] = KeyValueStoreConstants.COLUMN_DISPLAY_VISIBLE;
            knownKVSValueTypeRestrictions.add(fields);
            updateKeyToKnownKVSValueTypeRestrictions(fields);

            fields = new Object[3];
            fields[0] = ElementDataType.array.name();
            fields[1] = KeyValueStoreConstants.PARTITION_COLUMN;
            fields[2] = KeyValueStoreConstants.COLUMN_JOINS;
            knownKVSValueTypeRestrictions.add(fields);
            updateKeyToKnownKVSValueTypeRestrictions(fields);

            // and for the table...
            fields = new Object[3];
            fields[0] = ElementDataType.array.name();
            fields[1] = KeyValueStoreConstants.PARTITION_TABLE;
            fields[2] = KeyValueStoreConstants.TABLE_COL_ORDER;
            knownKVSValueTypeRestrictions.add(fields);
            updateKeyToKnownKVSValueTypeRestrictions(fields);

            fields = new Object[3];
            fields[0] = ElementDataType.object.name();
            fields[1] = KeyValueStoreConstants.PARTITION_TABLE;
            fields[2] = KeyValueStoreConstants.TABLE_DISPLAY_NAME;
            knownKVSValueTypeRestrictions.add(fields);
            updateKeyToKnownKVSValueTypeRestrictions(fields);

            fields = new Object[3];
            fields[0] = ElementDataType.array.name();
            fields[1] = KeyValueStoreConstants.PARTITION_TABLE;
            fields[2] = KeyValueStoreConstants.TABLE_GROUP_BY_COLS;
            knownKVSValueTypeRestrictions.add(fields);
            updateKeyToKnownKVSValueTypeRestrictions(fields);

            fields = new Object[3];
            fields[0] = ElementDataType.string.name();
            fields[1] = KeyValueStoreConstants.PARTITION_TABLE;
            fields[2] = KeyValueStoreConstants.TABLE_INDEX_COL;
            knownKVSValueTypeRestrictions.add(fields);
            updateKeyToKnownKVSValueTypeRestrictions(fields);

            fields = new Object[3];
            fields[0] = ElementDataType.object.name();
            fields[1] = KeyValueStoreConstants.PARTITION_TABLE;
            fields[2] = KeyValueStoreConstants.TABLE_SORT_COL;
            knownKVSValueTypeRestrictions.add(fields);
            updateKeyToKnownKVSValueTypeRestrictions(fields);

            fields = new Object[3];
            fields[0] = ElementDataType.object.name();
            fields[1] = KeyValueStoreConstants.PARTITION_TABLE;
            fields[2] = KeyValueStoreConstants.TABLE_SORT_ORDER;
            knownKVSValueTypeRestrictions.add(fields);
            updateKeyToKnownKVSValueTypeRestrictions(fields);

            // TODO: color rule groups
        }

        // Used to ensure that the singleton has been initialized properly
        AndroidConnectFactory.configure();
    }

    private static ODKDatabaseImplUtils databaseUtil = new ODKDatabaseImplUtils();

    static {
        // register a state-reset manipulator for 'databaseUtil' field.
        StaticStateManipulator.get().register(50, new IStaticFieldManipulator() {

            @Override
            public void reset() {
                databaseUtil = new ODKDatabaseImplUtils();
            }

        });
    }

    public static ODKDatabaseImplUtils get() {
        return databaseUtil;
    }

    /**
     * For mocking -- supply a mocked object.
     *
     * @param util
     */
    public static void set(ODKDatabaseImplUtils util) {
        databaseUtil = util;
    }

    private ODKDatabaseImplUtils() {
    }

    /**
     * Return an unmodifiable list of the admin columns that must be present in
     * every database table.
     *
     * @return
     */
    public List<String> getAdminColumns() {
        return ADMIN_COLUMNS;
    }

    /**
     * Return an unmodifiable list of the admin columns that should be exported to
     * a CSV file. This list excludes the SYNC_STATE and CONFLICT_TYPE columns.
     *
     * @return
     */
    public List<String> getExportColumns() {
        return EXPORT_COLUMNS;
    }

    private String applyQueryBounds(String sqlCommand, QueryBounds sqlQueryBounds) {
        if (sqlCommand == null || sqlQueryBounds == null) {
            return sqlCommand;
        }

        StringBuilder b = new StringBuilder();
        b.append(sqlCommand).append(K_LIMIT).append(sqlQueryBounds.mLimit).append(K_OFFSET)
                .append(sqlQueryBounds.mOffset);
        return b.toString();
    }

    public AccessContext getAccessContext(OdkConnectionInterface db, String tableId, String activeUser,
            String rolesList) {

        // figure out whether we have a privileged user or not
        List<String> rolesArray = getRolesArray(rolesList);

        if (tableId == null) {
            return new AccessContext(AccessColumnType.NO_EFFECTIVE_ACCESS_COLUMN, false, activeUser, rolesArray);
        }
        if (tableId.trim().length() == 0) {
            throw new IllegalArgumentException("tableId can be null but cannot be blank");
        }

        Boolean isLocked = false;
        {
            ArrayList<KeyValueStoreEntry> lockedList = this.getTableMetadata(db, tableId,
                    KeyValueStoreConstants.PARTITION_TABLE, LocalKeyValueStoreConstants.TableSecurity.ASPECT,
                    LocalKeyValueStoreConstants.TableSecurity.KEY_LOCKED).getEntries();

            if (lockedList.size() != 0) {
                if (lockedList.size() != 1) {
                    throw new IllegalStateException("should be impossible");
                }

                isLocked = KeyValueStoreUtils.getBoolean(db.getAppName(), lockedList.get(0));
            }
        }

        AccessColumnType accessColumnType = (isLocked ? AccessColumnType.LOCKED_EFFECTIVE_ACCESS_COLUMN
                : AccessColumnType.UNLOCKED_EFFECTIVE_ACCESS_COLUMN);
        boolean canCreateRow = false;
        if (isLocked) {
            // only super-user or tables administrator can create rows in locked tables.
            canCreateRow = rolesList.contains(RoleConsts.ROLE_SUPER_USER)
                    || rolesList.contains(RoleConsts.ROLE_ADMINISTRATOR);
        } else if (rolesList == null) {
            // this is the unverified user case. By default, they can create rows.
            // Administrator can use table properties to manage that capability.
            canCreateRow = true;
            ArrayList<KeyValueStoreEntry> canUnverifiedCreateList = this.getTableMetadata(db, tableId,
                    KeyValueStoreConstants.PARTITION_TABLE, LocalKeyValueStoreConstants.TableSecurity.ASPECT,
                    LocalKeyValueStoreConstants.TableSecurity.KEY_UNVERIFIED_USER_CAN_CREATE).getEntries();

            if (canUnverifiedCreateList.size() != 0) {
                if (canUnverifiedCreateList.size() != 1) {
                    throw new IllegalStateException("should be impossible");
                }

                canCreateRow = KeyValueStoreUtils.getBoolean(db.getAppName(), canUnverifiedCreateList.get(0));
            }
        } else {
            canCreateRow = true;
        }

        return new AccessContext(accessColumnType, canCreateRow, activeUser, rolesArray);
    }

    /**
     * Optionally add the _effective_access column to the SELECT statement.
     *
     * @param b
     * @param wrappedSqlArgs
     * @param accessContext
     */
    private void buildAccessRights(StringBuilder b, ArrayList<Object> wrappedSqlArgs, AccessContext accessContext) {

        if (accessContext.accessColumnType == AccessColumnType.NO_EFFECTIVE_ACCESS_COLUMN) {
            return;
        }

        b.append(", ");
        if (accessContext.isPrivilegedUser) {
            // privileged user
            b.append("\"rwd\" as ").append(DataTableColumns.EFFECTIVE_ACCESS);
        } else if (accessContext.isUnverifiedUser) {
            // un-verified user or anonymous user
            if (accessContext.accessColumnType == AccessColumnType.UNLOCKED_EFFECTIVE_ACCESS_COLUMN) {
                // unlocked tables have r, rw (modify) and rwd (default or new_row) options
                b.append("case when T.").append(DataTableColumns.SYNC_STATE).append("= \"")
                        .append(SyncState.new_row.name()).append("\" then \"rwd\" ").append(" when T.")
                        .append(DataTableColumns.FILTER_TYPE).append("= \"")
                        .append(RowFilterScope.Type.DEFAULT.name()).append("\" then \"rwd\" ").append(" when T.")
                        .append(DataTableColumns.FILTER_TYPE).append("= \"")
                        .append(RowFilterScope.Type.MODIFY.name()).append("\" then \"rw\" ")
                        .append(" else \"r\" end as ").append(DataTableColumns.EFFECTIVE_ACCESS);
            } else {
                // locked tables have just rwd (new_row) and r options
                b.append("case when T.").append(DataTableColumns.SYNC_STATE).append("= \"")
                        .append(SyncState.new_row.name()).append("\" then \"rwd\" ").append(" else \"r\" end as ")
                        .append(DataTableColumns.EFFECTIVE_ACCESS);
            }
        } else {
            // ordinary user
            if (accessContext.accessColumnType == AccessColumnType.UNLOCKED_EFFECTIVE_ACCESS_COLUMN) {
                // unlocked tables have r, rw (modify) and rwd (default or new_row) options
                b.append("case when T.").append(DataTableColumns.SYNC_STATE).append("= \"")
                        .append(SyncState.new_row.name()).append("\" then \"rwd\" ").append(" when T.")
                        .append(DataTableColumns.FILTER_VALUE).append("= ?").append(" then \"rwd\" ")
                        .append(" when T.").append(DataTableColumns.FILTER_TYPE).append("= \"")
                        .append(RowFilterScope.Type.DEFAULT.name()).append("\" then \"rwd\" ").append(" when T.")
                        .append(DataTableColumns.FILTER_TYPE).append("= \"")
                        .append(RowFilterScope.Type.MODIFY.name()).append("\" then \"rw\" ")
                        .append(" else \"r\" end as ").append(DataTableColumns.EFFECTIVE_ACCESS);
                wrappedSqlArgs.add(accessContext.activeUser);
            } else {
                // locked tables have just rwd (new_row) and r options
                b.append("case when T.").append(DataTableColumns.SYNC_STATE).append("= \"")
                        .append(SyncState.new_row.name()).append("\" then \"rwd\" ").append(" when T.")
                        .append(DataTableColumns.FILTER_VALUE).append("= ?").append(" then \"rw\" ")
                        .append(" else \"r\" end as ").append(DataTableColumns.EFFECTIVE_ACCESS);
                wrappedSqlArgs.add(accessContext.activeUser);
            }
        }
    }

    /**
     * Perform a raw query with bind parameters.
     *
     * @param db
     * @param sqlCommand
     * @param selectionArgs
     * @param sqlQueryBounds offset and max number of rows to return (zero is infinite)
     * @param accessContext  for managing what effective accesses to return
     * @return
     */
    public Cursor rawQuery(OdkConnectionInterface db, String sqlCommand, Object[] selectionArgs,
            QueryBounds sqlQueryBounds, AccessContext accessContext) {

        Cursor c = db.rawQuery(sqlCommand + " LIMIT 1", selectionArgs);
        if (c.moveToFirst()) {
            // see if we have the columns needed to apply row-level filtering
            boolean hasFilterType = c.getColumnIndex(DataTableColumns.FILTER_TYPE) != -1;
            boolean hasFilterValue = c.getColumnIndex(DataTableColumns.FILTER_VALUE) != -1;
            boolean hasSyncState = c.getColumnIndex(DataTableColumns.SYNC_STATE) != -1;
            c.close();

            if (!(hasFilterType && hasFilterValue && hasSyncState)) {
                // nope. we require all 3 to apply row-level filtering

                // no need to filter this resultset
                String sql = applyQueryBounds(sqlCommand, sqlQueryBounds);
                c = db.rawQuery(sql, selectionArgs);
                return c;
            }

            // augment query result list with the effective access controls for the row ("r", "rw", or "rwd")
            StringBuilder b = new StringBuilder();
            ArrayList<Object> wrappedSqlArgs = new ArrayList<Object>();

            b.append("SELECT *");
            buildAccessRights(b, wrappedSqlArgs, accessContext);
            b.append(" FROM (").append(sqlCommand).append(") AS T");
            if (selectionArgs != null) {
                Collections.addAll(wrappedSqlArgs, selectionArgs);
            }
            // apply row-level visibility filter only if we are not privileged
            // privileged users see everything.
            if (!accessContext.isPrivilegedUser) {
                b.append(" WHERE T.").append(DataTableColumns.FILTER_TYPE).append(" != \"")
                        .append(RowFilterScope.Type.HIDDEN.name()).append("\" OR T.")
                        .append(DataTableColumns.SYNC_STATE).append(" = \"").append(SyncState.new_row.name())
                        .append("\"");
                if (!accessContext.isUnverifiedUser && accessContext.activeUser != null
                        && accessContext.hasRole(RoleConsts.ROLE_USER)) {
                    // visible if activeUser matches the filter value
                    b.append(" OR T.").append(DataTableColumns.FILTER_VALUE).append(" = ?");
                    wrappedSqlArgs.add(accessContext.activeUser);
                }
            }
            String wrappedSql = b.toString();
            String limitAppliedSql = applyQueryBounds(wrappedSql, sqlQueryBounds);
            c = db.rawQuery(limitAppliedSql, wrappedSqlArgs.toArray());
            return c;
        } else {
            // cursor is empty!
            return c;
        }
    }

    /**
     * TESTING ONLY
     * <p/>
     * Perform a query with the given parameters.
     *
     * @param db
     * @param table
     * @param columns
     * @param selection
     * @param selectionArgs
     * @param groupBy
     * @param having
     * @param orderBy
     * @param limit
     * @return
     */
    public Cursor queryDistinctForTest(OdkConnectionInterface db, String table, String[] columns, String selection,
            Object[] selectionArgs, String groupBy, String having, String orderBy, String limit)
            throws SQLiteException {
        Cursor c = db.queryDistinct(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
        return c;
    }

    /**
     * TESTING ONLY
     *
     * @param db
     * @param table
     * @param columns
     * @param selection
     * @param selectionArgs
     * @param groupBy
     * @param having
     * @param orderBy
     * @param limit
     * @return
     * @throws SQLiteException
     */
    public Cursor queryForTest(OdkConnectionInterface db, String table, String[] columns, String selection,
            Object[] selectionArgs, String groupBy, String having, String orderBy, String limit)
            throws SQLiteException {
        Cursor c = db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
        return c;
    }

    /**
     * Get a {@link BaseTable} for this table based on the given sql query. All
     * columns from the table are returned.  Up to sqlLimit rows are returned
     * (zero is infinite).
     * <p/>
     * The result set is filtered according to the supplied rolesList if there
     * is a FILTER_TYPE column present in the result set.
     *
     * @param db
     * @param sqlCommand     the query to run
     * @param sqlBindArgs    the selection parameters
     * @param sqlCommand
     * @param sqlQueryBounds offset and max number of rows to return (zero is infinite)
     * @param accessContext  for managing what effective accesses to return
     * @return
     */
    public BaseTable query(OdkConnectionInterface db, String tableId, String sqlCommand, Object[] sqlBindArgs,
            QueryBounds sqlQueryBounds, AccessContext accessContext) {

        Cursor c = null;
        try {
            c = rawQuery(db, sqlCommand, sqlBindArgs, sqlQueryBounds, accessContext);
            BaseTable table = buildBaseTable(db, c, tableId, accessContext.canCreateRow);
            return table;
        } finally {
            if (c != null && !c.isClosed()) {
                c.close();
            }
        }
    }

    /**
     * Get a {@link BaseTable} for this table based on the given sql query. All
     * columns from the table are returned.
     * <p/>
     * The number of rows returned are limited to no greater than the sqlLimit (zero is infinite).
     *
     * @param db
     * @param sqlCommand     the query to run
     * @param sqlBindArgs    the selection parameters
     * @param sqlQueryBounds the number of rows to return (zero is infinite)
     * @param accessContext  for managing what effective accesses to return
     * @return
     */
    public BaseTable privilegedQuery(OdkConnectionInterface db, String tableId, String sqlCommand,
            Object[] sqlBindArgs, QueryBounds sqlQueryBounds, AccessContext accessContext) {

        if (!accessContext.isPrivilegedUser) {
            accessContext = accessContext.cloneAsPrivilegedUser();
        }
        return query(db, tableId, sqlCommand, sqlBindArgs, sqlQueryBounds, accessContext);
    }

    /**
     * Privileged execute of an arbitrary SQL command.
     * For obvious reasons, this is very dangerous!
     *
     * The sql command can be any valid SQL command that does not return a result set.
     * No data is returned (e.g., insert into table ... or similar).
     *
     * @param db
     * @param sqlCommand
     * @param sqlBindArgs
     */
    public void privilegedExecute(OdkConnectionInterface db, String sqlCommand, Object[] sqlBindArgs) {
        db.execSQL(sqlCommand, sqlBindArgs);
    }

    private BaseTable buildBaseTable(OdkConnectionInterface db, Cursor c, String tableId, boolean canCreateRow) {

        HashMap<String, Integer> mElementKeyToIndex = null;
        String[] mElementKeyForIndex = null;

        if (!c.moveToFirst()) {

            // Attempt to retrieve the columns from the cursor.
            // These may not be available if there were no rows returned.
            // It depends upon the cursor implementation.
            try {
                int columnCount = c.getColumnCount();
                mElementKeyForIndex = new String[columnCount];
                mElementKeyToIndex = new HashMap<>(columnCount);
                int i;

                for (i = 0; i < columnCount; ++i) {
                    String columnName = c.getColumnName(i);
                    mElementKeyForIndex[i] = columnName;
                    mElementKeyToIndex.put(columnName, i);
                }
            } catch (Exception e) {
                // ignore.
            }

            // if they were not available, declare an empty array.
            if (mElementKeyForIndex == null) {
                mElementKeyForIndex = new String[0];
            }
            c.close();

            // we have no idea what the table should contain because it has no rows...
            BaseTable table = new BaseTable(null, mElementKeyForIndex, mElementKeyToIndex, 0);
            table.setEffectiveAccessCreateRow(canCreateRow);
            return table;
        }

        int rowCount = c.getCount();
        int columnCount = c.getColumnCount();

        BaseTable table = null;

        // These maps will map the element key to the corresponding index in
        // either data or metadata. If the user has defined a column with the
        // element key _my_data, and this column is at index 5 in the data
        // array, dataKeyToIndex would then have a mapping of _my_data:5.
        // The sync_state column, if present at index 7, would have a mapping
        // in metadataKeyToIndex of sync_state:7.
        mElementKeyForIndex = new String[columnCount];
        mElementKeyToIndex = new HashMap<>(columnCount);

        int i;

        for (i = 0; i < columnCount; ++i) {
            String columnName = c.getColumnName(i);
            mElementKeyForIndex[i] = columnName;
            mElementKeyToIndex.put(columnName, i);
        }

        table = new BaseTable(null, mElementKeyForIndex, mElementKeyToIndex, rowCount);

        String[] rowData = new String[columnCount];
        do {
            // First get the user-defined data for this row.
            for (i = 0; i < columnCount; i++) {
                String value = CursorUtils.getIndexAsString(c, i);
                rowData[i] = value;
            }

            Row nextRow = new Row(rowData.clone(), table);
            table.addRow(nextRow);
        } while (c.moveToNext());
        c.close();

        table.setEffectiveAccessCreateRow(canCreateRow);

        if (tableId != null) {
            table.setMetaDataRev(getTableDefinitionRevId(db, tableId));
        }
        return table;
    }

    /************** LOCAL ONLY TABLE OPERATIONS ***************/

    /**
     * Create a local only table and prepend the given id with an "L_"
     *
     * @param db
     * @param tableId
     * @param columns
     * @return
     */
    public OrderedColumns createLocalOnlyTableWithColumns(OdkConnectionInterface db, String tableId,
            List<Column> columns) {

        boolean dbWithinTransaction = db.inTransaction();
        boolean success = false;

        OrderedColumns orderedDefs = new OrderedColumns(db.getAppName(), tableId, columns);
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            createTableWithColumns(db, tableId, orderedDefs, false);

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
            success = true;
            return orderedDefs;
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
            if (!success) {

                // Get the names of the columns
                StringBuilder colNames = new StringBuilder();
                if (columns != null) {
                    for (Column column : columns) {
                        colNames.append(" ").append(column.getElementKey()).append(",");
                    }
                    if (colNames != null && colNames.length() > 0) {
                        colNames.deleteCharAt(colNames.length() - 1);
                        WebLogger.getLogger(db.getAppName()).e(t,
                                "createLocalOnlyTableWithColumns: Error while adding table " + tableId
                                        + " with columns:" + colNames.toString());
                    }
                } else {
                    WebLogger.getLogger(db.getAppName()).e(t,
                            "createLocalOnlyTableWithColumns: Error while adding table " + tableId
                                    + " with columns: null");
                }
            }
        }
    }

    /**
     * Drop the given local only table
     *
     * @param db
     * @param tableId
     */
    public void deleteLocalOnlyTable(OdkConnectionInterface db, String tableId) {

        boolean dbWithinTransaction = db.inTransaction();

        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            // Drop the table used for the formId
            StringBuilder b = new StringBuilder();
            b.append("DROP TABLE IF EXISTS ").append(tableId).append(";");
            db.execSQL(b.toString(), null);

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }

        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * Insert a row into a local only table
     *
     * @param db
     * @param tableId
     * @param rowValues
     * @throws ActionNotAuthorizedException
     */
    public void insertLocalOnlyRow(OdkConnectionInterface db, String tableId, ContentValues rowValues)
            throws IllegalArgumentException {

        if (rowValues == null || rowValues.size() <= 0) {
            throw new IllegalArgumentException(t + ": No values to add into table " + tableId);
        }

        HashMap<String, Object> cvDataTableVal = new HashMap<String, Object>();
        for (String key : rowValues.keySet()) {
            cvDataTableVal.put(key, rowValues.get(key));
        }

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            db.insertOrThrow(tableId, null, cvDataTableVal);

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * Update a row in a local only table
     *
     * @param db
     * @param tableId
     * @param rowValues
     * @param whereClause
     * @param bindArgs
     * @throws ActionNotAuthorizedException
     */
    public void updateLocalOnlyRow(OdkConnectionInterface db, String tableId, ContentValues rowValues,
            String whereClause, Object[] bindArgs) throws IllegalArgumentException {

        if (rowValues == null || rowValues.size() <= 0) {
            throw new IllegalArgumentException(t + ": No values to add into table " + tableId);
        }

        HashMap<String, Object> cvDataTableVal = new HashMap<String, Object>();
        for (String key : rowValues.keySet()) {
            cvDataTableVal.put(key, rowValues.get(key));
        }

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            db.update(tableId, cvDataTableVal, whereClause, bindArgs);

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * Delete a row in a local only table
     *
     * @param db
     * @param tableId
     * @param whereClause
     * @param bindArgs
     * @throws ActionNotAuthorizedException
     */
    public void deleteLocalOnlyRow(OdkConnectionInterface db, String tableId, String whereClause,
            Object[] bindArgs) {

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            db.delete(tableId, whereClause, bindArgs);

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * Return the row(s) for the given tableId and rowId. If the row has
     * checkpoints or conflicts, the returned BaseTable will have more than one
     * Row returned. Otherwise, it will contain a single row.
     *
     * @param db
     * @param tableId
     * @param rowId
     * @param activeUser
     * @param rolesList
     * @return
     */
    public BaseTable getRowsWithId(OdkConnectionInterface db, String tableId, String rowId, String activeUser,
            String rolesList) {

        AccessContext accessContext = getAccessContext(db, tableId, activeUser, rolesList);

        BaseTable table = query(db, tableId,
                QueryUtil.buildSqlStatement(tableId, QueryUtil.GET_ROWS_WITH_ID_WHERE,
                        QueryUtil.GET_ROWS_WITH_ID_GROUP_BY, QueryUtil.GET_ROWS_WITH_ID_HAVING,
                        QueryUtil.GET_ROWS_WITH_ID_ORDER_BY_KEYS, QueryUtil.GET_ROWS_WITH_ID_ORDER_BY_DIR),
                new String[] { rowId }, null, accessContext);

        return table;
    }

    /**
     * Return the row(s) for the given tableId and rowId. If the row has
     * checkpoints or conflicts, the returned BaseTable will have more than one
     * Row returned. Otherwise, it will contain a single row.
     *
     * @param db
     * @param tableId
     * @param rowId
     * @param activeUser
     * @return
     */
    public BaseTable privilegedGetRowsWithId(OdkConnectionInterface db, String tableId, String rowId,
            String activeUser) {

        AccessContext accessContext = getAccessContext(db, tableId, activeUser, RoleConsts.ADMIN_ROLES_LIST);

        BaseTable table = privilegedQuery(db, tableId,
                QueryUtil.buildSqlStatement(tableId, QueryUtil.GET_ROWS_WITH_ID_WHERE,
                        QueryUtil.GET_ROWS_WITH_ID_GROUP_BY, QueryUtil.GET_ROWS_WITH_ID_HAVING,
                        QueryUtil.GET_ROWS_WITH_ID_ORDER_BY_KEYS, QueryUtil.GET_ROWS_WITH_ID_ORDER_BY_DIR),
                new String[] { rowId }, null, accessContext);

        return table;
    }

    /**
     * Return the row with the most recent changes for the given tableId and rowId.
     * If the rowId does not exist, it returns an empty BaseTable for this tableId.
     * If the row has conflicts, it throws an exception. Otherwise, it returns the
     * most recent checkpoint or non-checkpoint value; it will contain a single row.
     *
     * @param db
     * @param tableId
     * @param rowId
     * @return
     */
    public BaseTable getMostRecentRowWithId(OdkConnectionInterface db, String tableId, String rowId,
            String activeUser, String rolesList) {

        BaseTable table = getRowsWithId(db, tableId, rowId, activeUser, rolesList);

        if (table.getNumberOfRows() == 0) {
            return table;
        }

        // most recent savepoint timestamp...
        BaseTable t = new BaseTable(table, Collections.singletonList(0));

        if (hasConflictRows(t)) {
            throw new IllegalStateException("row is in conflict");
        }
        return t;
    }

    /**
     * Return the row with the most recent changes for the given tableId and rowId.
     * If the rowId does not exist, it returns an empty BaseTable for this tableId.
     * If the row has conflicts, it throws an exception. Otherwise, it returns the
     * most recent checkpoint or non-checkpoint value; it will contain a single row.
     *
     * @param db
     * @param tableId
     * @param rowId
     * @return
     */
    public BaseTable privilegedGetMostRecentRowWithId(OdkConnectionInterface db, String tableId, String rowId,
            String activeUser) {

        BaseTable table = privilegedGetRowsWithId(db, tableId, rowId, activeUser);

        if (table.getNumberOfRows() == 0) {
            return table;
        }

        // most recent savepoint timestamp...
        BaseTable t = new BaseTable(table, Collections.singletonList(0));

        if (hasConflictRows(t)) {
            throw new IllegalStateException("row is in conflict");
        }
        return t;
    }

    private boolean hasConflictRows(BaseTable table) {
        List<Row> rows = table.getRows();
        for (Row row : rows) {
            String conflictType = row.getDataByKey(DataTableColumns.CONFLICT_TYPE);
            if (conflictType != null && conflictType.length() != 0) {
                return true;
            }
        }
        return false;
    }

    /**
     * Return all the columns in the given table, including any metadata columns.
     * This does a direct query against the database and is suitable for accessing
     * non-managed tables. It does not access any metadata and therefore will not
     * report non-unit-of-retention (grouping) columns.
     *
     * @param db
     * @param tableId
     * @return
     */
    public String[] getAllColumnNames(OdkConnectionInterface db, String tableId) {
        Cursor cursor = null;
        try {
            StringBuilder b = new StringBuilder();
            b.append(K_SELECT_FROM).append(tableId).append(K_LIMIT).append("1");
            cursor = db.rawQuery(b.toString(), null);
            // If this query has been executed before, the cursor is created using the
            // previously-constructed PreparedStatement for the query. There is no actual
            // interaction with the database itself at the time the Cursor is constructed.
            // The first database interaction is when the content of the cursor is fetched.
            //
            // This can be triggered by a call to getCount().
            // At that time, if the table does not exist, it will throw an exception.
            cursor.moveToFirst();
            cursor.getCount();
            // Otherwise, when cached, getting the column names doesn't call into the database
            // and will not, itself, detect that the table has been dropped.
            String[] colNames = cursor.getColumnNames();
            return colNames;
        } finally {
            if (cursor != null && !cursor.isClosed()) {
                cursor.close();
            }
        }
    }

    /**
     * Retrieve the list of user-defined columns for a tableId using the metadata
     * for that table. Returns the unit-of-retention and non-unit-of-retention
     * (grouping) columns.
     *
     * @param db
     * @param tableId
     * @return
     */
    public OrderedColumns getUserDefinedColumns(OdkConnectionInterface db, String tableId) {
        ArrayList<Column> userDefinedColumns = new ArrayList<Column>();
        String selection = K_COLUMN_DEFS_TABLE_ID_EQUALS_PARAM;
        Object[] selectionArgs = { tableId };
        //@formatter:off
        String[] cols = { ColumnDefinitionsColumns.ELEMENT_KEY, ColumnDefinitionsColumns.ELEMENT_NAME,
                ColumnDefinitionsColumns.ELEMENT_TYPE, ColumnDefinitionsColumns.LIST_CHILD_ELEMENT_KEYS };
        //@formatter:on
        Cursor c = null;
        try {
            c = db.query(DatabaseConstants.COLUMN_DEFINITIONS_TABLE_NAME, cols, selection, selectionArgs, null,
                    null, ColumnDefinitionsColumns.ELEMENT_KEY + " ASC", null);

            int elemKeyIndex = c.getColumnIndexOrThrow(ColumnDefinitionsColumns.ELEMENT_KEY);
            int elemNameIndex = c.getColumnIndexOrThrow(ColumnDefinitionsColumns.ELEMENT_NAME);
            int elemTypeIndex = c.getColumnIndexOrThrow(ColumnDefinitionsColumns.ELEMENT_TYPE);
            int listChildrenIndex = c.getColumnIndexOrThrow(ColumnDefinitionsColumns.LIST_CHILD_ELEMENT_KEYS);
            c.moveToFirst();
            while (!c.isAfterLast()) {
                String elementKey = CursorUtils.getIndexAsString(c, elemKeyIndex);
                String elementName = CursorUtils.getIndexAsString(c, elemNameIndex);
                String elementType = CursorUtils.getIndexAsString(c, elemTypeIndex);
                String listOfChildren = CursorUtils.getIndexAsString(c, listChildrenIndex);
                userDefinedColumns.add(new Column(elementKey, elementName, elementType, listOfChildren));
                c.moveToNext();
            }
        } finally {
            if (c != null && !c.isClosed()) {
                c.close();
            }
        }
        return new OrderedColumns(db.getAppName(), tableId, userDefinedColumns);
    }

    /**
     * Verifies that the tableId exists in the database.
     *
     * @param db
     * @param tableId
     * @return true if table is listed in table definitions.
     */
    public boolean hasTableId(OdkConnectionInterface db, String tableId) {
        Cursor c = null;
        try {
            //@formatter:off
            c = db.query(DatabaseConstants.TABLE_DEFS_TABLE_NAME, null, K_TABLE_DEFS_TABLE_ID_EQUALS_PARAM,
                    new Object[] { tableId }, null, null, null, null);
            //@formatter:on
            // we know about the table...
            // tableId is the database table name...
            return (c != null) && c.moveToFirst() && (c.getCount() != 0);
        } finally {
            if (c != null && !c.isClosed()) {
                c.close();
            }
        }
    }

    /**
     * Return the health of a data table. The health can be one of
     * <ul>
     * <li>TABLE_HEALTH_IS_CLEAN = 0</li>
     * <li>TABLE_HEALTH_HAS_CONFLICTS = 1</li>
     * <li>TABLE_HEALTH_HAS_CHECKPOINTS = 2</li>
     * <li>TABLE_HEALTH_HAS_CHECKPOINTS_AND_CONFLICTS = 3</li>
     * <ul>
     *
     * @param db
     * @param tableId
     * @return
     */
    public int getTableHealth(OdkConnectionInterface db, String tableId) {
        StringBuilder b = new StringBuilder();
        b.append("SELECT SUM(case when _savepoint_type is null then 1 else 0 end) as checkpoints,")
                .append("SUM(case when _conflict_type is not null then 1 else 0 end) as conflicts from ")
                .append(tableId);

        Cursor c = null;
        try {
            c = db.rawQuery(b.toString(), null);
            Integer checkpoints = null;
            Integer conflicts = null;
            if (c != null) {
                if (c.moveToFirst()) {
                    int idxCheckpoints = c.getColumnIndex("checkpoints");
                    int idxConflicts = c.getColumnIndex("conflicts");
                    checkpoints = CursorUtils.getIndexAsType(c, Integer.class, idxCheckpoints);
                    conflicts = CursorUtils.getIndexAsType(c, Integer.class, idxConflicts);
                }
                c.close();
            }

            int outcome = CursorUtils.TABLE_HEALTH_IS_CLEAN;
            if (checkpoints != null && checkpoints != 0) {
                outcome += CursorUtils.TABLE_HEALTH_HAS_CHECKPOINTS;
            }
            if (conflicts != null && conflicts != 0) {
                outcome += CursorUtils.TABLE_HEALTH_HAS_CONFLICTS;
            }
            return outcome;
        } finally {
            if (c != null && !c.isClosed()) {
                c.close();
            }
        }
    }

    /**
     * Return all the tableIds in the database.
     *
     * @param db
     * @return an ArrayList<String> of tableIds
     */
    public ArrayList<String> getAllTableIds(OdkConnectionInterface db) {
        ArrayList<String> tableIds = new ArrayList<String>();
        Cursor c = null;
        try {
            c = db.query(DatabaseConstants.TABLE_DEFS_TABLE_NAME, new String[] { TableDefinitionsColumns.TABLE_ID },
                    null, null, null, null, TableDefinitionsColumns.TABLE_ID + " ASC", null);

            if (c.moveToFirst()) {
                int idxId = c.getColumnIndex(TableDefinitionsColumns.TABLE_ID);
                do {
                    String tableId = c.getString(idxId);
                    if (tableId == null || tableId.length() == 0) {
                        c.close();
                        throw new IllegalStateException("getAllTableIds: Unexpected tableId found!");
                    }
                    tableIds.add(tableId);
                } while (c.moveToNext());
            }
        } finally {
            if (c != null && !c.isClosed()) {
                c.close();
            }
        }
        return tableIds;
    }

    /**
     * Drop the given tableId and remove all the files (both configuration and
     * data attachments) associated with that table.
     *
     * @param db
     * @param tableId
     */
    public void deleteTableAndAllData(OdkConnectionInterface db, final String tableId) {

        SyncETagsUtils seu = new SyncETagsUtils();
        boolean dbWithinTransaction = db.inTransaction();

        Object[] whereArgs = { tableId };

        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            // Drop the table used for the formId
            StringBuilder b = new StringBuilder();
            b.append("DROP TABLE IF EXISTS ").append(tableId).append(";");
            db.execSQL(b.toString(), null);

            // Delete the server sync ETags associated with this table
            seu.deleteAllSyncETagsForTableId(db, tableId);

            // Delete the table definition for the tableId
            int count;
            {
                String whereClause = K_TABLE_DEFS_TABLE_ID_EQUALS_PARAM;

                count = db.delete(DatabaseConstants.TABLE_DEFS_TABLE_NAME, whereClause, whereArgs);
            }

            // Delete the column definitions for this tableId
            {
                String whereClause = K_COLUMN_DEFS_TABLE_ID_EQUALS_PARAM;

                db.delete(DatabaseConstants.COLUMN_DEFINITIONS_TABLE_NAME, whereClause, whereArgs);
            }

            // Delete the uploads for the tableId
            {
                String uploadWhereClause = InstanceColumns.DATA_TABLE_TABLE_ID + " = ?";
                db.delete(DatabaseConstants.UPLOADS_TABLE_NAME, uploadWhereClause, whereArgs);
            }

            // Delete the values from the 4 key value stores
            {
                String whereClause = K_KVS_TABLE_ID_EQUALS_PARAM;

                db.delete(DatabaseConstants.KEY_VALUE_STORE_ACTIVE_TABLE_NAME, whereClause, whereArgs);
            }

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }

        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }

        // And delete the files from the SDCard...
        String tableDir = ODKFileUtils.getTablesFolder(db.getAppName(), tableId);
        try {
            FileUtils.deleteDirectory(new File(tableDir));
        } catch (IOException e1) {
            e1.printStackTrace();
            throw new IllegalStateException("Unable to delete the " + tableDir + " directory", e1);
        }

        String assetsCsvDir = ODKFileUtils.getAssetsCsvFolder(db.getAppName());
        try {
            File file = new File(assetsCsvDir);
            if (file.exists()) {
                Collection<File> files = FileUtils.listFiles(file, new IOFileFilter() {

                    @Override
                    public boolean accept(File file) {
                        String[] parts = file.getName().split("\\.");
                        return (parts[0].equals(tableId) && parts[parts.length - 1].equals("csv")
                                && (parts.length == 2 || parts.length == 3
                                        || (parts.length == 4 && parts[parts.length - 2].equals("properties"))));
                    }

                    @Override
                    public boolean accept(File dir, String name) {
                        String[] parts = name.split("\\.");
                        return (parts[0].equals(tableId) && parts[parts.length - 1].equals("csv")
                                && (parts.length == 2 || parts.length == 3
                                        || (parts.length == 4 && parts[parts.length - 2].equals("properties"))));
                    }
                }, new IOFileFilter() {

                    // don't traverse into directories
                    @Override
                    public boolean accept(File arg0) {
                        return false;
                    }

                    // don't traverse into directories
                    @Override
                    public boolean accept(File arg0, String arg1) {
                        return false;
                    }
                });

                FileUtils.deleteDirectory(new File(tableDir));
                for (File f : files) {
                    FileUtils.deleteQuietly(f);
                }
            }
        } catch (IOException e1) {
            e1.printStackTrace();
            throw new IllegalStateException("Unable to delete the " + tableDir + " directory", e1);
        }
    }

    /**
     * Update the schema and data-modification ETags of a given tableId.
     *
     * @param db
     * @param tableId
     * @param schemaETag
     * @param lastDataETag
     */
    public void privilegedUpdateTableETags(OdkConnectionInterface db, String tableId, String schemaETag,
            String lastDataETag) {
        if (tableId == null || tableId.length() <= 0) {
            throw new IllegalArgumentException(t + ": application name and table name must be specified");
        }

        TreeMap<String, Object> cvTableDef = new TreeMap<String, Object>();
        cvTableDef.put(TableDefinitionsColumns.SCHEMA_ETAG, schemaETag);
        cvTableDef.put(TableDefinitionsColumns.LAST_DATA_ETAG, lastDataETag);

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }
            db.update(DatabaseConstants.TABLE_DEFS_TABLE_NAME, cvTableDef, K_TABLE_DEFS_TABLE_ID_EQUALS_PARAM,
                    new Object[] { tableId });

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }

        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * Update the timestamp of the last entirely-successful synchronization
     * attempt of this table.
     *
     * @param db
     * @param tableId
     */
    public void privilegedUpdateTableLastSyncTime(OdkConnectionInterface db, String tableId) {
        if (tableId == null || tableId.length() <= 0) {
            throw new IllegalArgumentException(t + ": application name and table name must be specified");
        }

        TreeMap<String, Object> cvTableDef = new TreeMap<String, Object>();
        cvTableDef.put(TableDefinitionsColumns.LAST_SYNC_TIME,
                TableConstants.nanoSecondsFromMillis(System.currentTimeMillis()));

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }
            db.update(DatabaseConstants.TABLE_DEFS_TABLE_NAME, cvTableDef, K_TABLE_DEFS_TABLE_ID_EQUALS_PARAM,
                    new Object[] { tableId });

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * Get the table definition entry for a tableId. This specifies the schema
     * ETag, the data-modification ETag, and the date-time of the last successful
     * sync of the table to the server.
     *
     * @param db
     * @param tableId
     * @return
     */
    public TableDefinitionEntry getTableDefinitionEntry(OdkConnectionInterface db, String tableId) {

        TableDefinitionEntry e = null;
        Cursor c = null;
        try {
            StringBuilder b = new StringBuilder();
            ArrayList<String> selArgs = new ArrayList<String>();
            b.append(K_KVS_TABLE_ID_EQUALS_PARAM);
            selArgs.add(tableId);

            c = db.query(DatabaseConstants.TABLE_DEFS_TABLE_NAME, null, b.toString(),
                    selArgs.toArray(new String[selArgs.size()]), null, null, null, null);
            if (c.moveToFirst()) {
                int idxSchemaETag = c.getColumnIndex(TableDefinitionsColumns.SCHEMA_ETAG);
                int idxLastDataETag = c.getColumnIndex(TableDefinitionsColumns.LAST_DATA_ETAG);
                int idxLastSyncTime = c.getColumnIndex(TableDefinitionsColumns.LAST_SYNC_TIME);
                int idxRevId = c.getColumnIndex(TableDefinitionsColumns.REV_ID);

                if (c.getCount() != 1) {
                    throw new IllegalStateException(
                            "Two or more TableDefinitionEntry records found for tableId " + tableId);
                }

                e = new TableDefinitionEntry(tableId);
                e.setSchemaETag(c.getString(idxSchemaETag));
                e.setLastDataETag(c.getString(idxLastDataETag));
                e.setLastSyncTime(c.getString(idxLastSyncTime));
                e.setRevId(c.getString(idxRevId));
            }
        } finally {
            if (c != null && !c.isClosed()) {
                c.close();
            }
        }
        return e;
    }

    public String getTableDefinitionRevId(OdkConnectionInterface db, String tableId) {
        String revId = null;
        Cursor c = null;
        try {
            StringBuilder b = new StringBuilder();
            ArrayList<String> selArgs = new ArrayList<String>();
            b.append(K_KVS_TABLE_ID_EQUALS_PARAM);
            selArgs.add(tableId);

            c = db.query(DatabaseConstants.TABLE_DEFS_TABLE_NAME, null, b.toString(),
                    selArgs.toArray(new String[selArgs.size()]), null, null, null, null);
            if (c.moveToFirst()) {
                int idxRevId = c.getColumnIndex(TableDefinitionsColumns.REV_ID);

                if (c.getCount() != 1) {
                    throw new IllegalStateException(
                            "Two or more TableDefinitionEntry records found for tableId " + tableId);
                }

                revId = c.getString(idxRevId);
            }
        } finally {
            if (c != null && !c.isClosed()) {
                c.close();
            }
        }
        return revId;
    }

    /*
     * Build the start of a create table statement -- specifies all the metadata
     * columns. Caller must then add all the user-defined column definitions and
     * closing parentheses.
     */
    private void addMetadataFieldsToTableCreationStatement(StringBuilder b) {
        /*
         * Resulting string should be the following String createTableCmd =
         * "CREATE TABLE IF NOT EXISTS " + tableId + " (" + DataTableColumns.ID +
         * " TEXT NOT NULL, " + DataTableColumns.ROW_ETAG + " TEXT NULL, " +
         * DataTableColumns.SYNC_STATE + " TEXT NOT NULL, " +
         * DataTableColumns.CONFLICT_TYPE + " INTEGER NULL," +
         * DataTableColumns.FILTER_TYPE + " TEXT NULL," +
         * DataTableColumns.FILTER_VALUE + " TEXT NULL," + DataTableColumns.FORM_ID
         * + " TEXT NULL," + DataTableColumns.LOCALE + " TEXT NULL," +
         * DataTableColumns.SAVEPOINT_TYPE + " TEXT NULL," +
         * DataTableColumns.SAVEPOINT_TIMESTAMP + " TEXT NOT NULL," +
         * DataTableColumns.SAVEPOINT_CREATOR + " TEXT NULL";
         */

        List<String> cols = getAdminColumns();

        String endSeq = ", ";
        for (int i = 0; i < cols.size(); ++i) {
            if (i == cols.size() - 1) {
                endSeq = "";
            }
            String colName = cols.get(i);
            //@formatter:off
            if (colName.equals(DataTableColumns.ID) || colName.equals(DataTableColumns.SYNC_STATE)
                    || colName.equals(DataTableColumns.SAVEPOINT_TIMESTAMP)) {
                b.append(colName).append(" TEXT NOT NULL").append(endSeq);
            } else if (colName.equals(DataTableColumns.ROW_ETAG) || colName.equals(DataTableColumns.FILTER_TYPE)
                    || colName.equals(DataTableColumns.FILTER_VALUE) || colName.equals(DataTableColumns.FORM_ID)
                    || colName.equals(DataTableColumns.LOCALE) || colName.equals(DataTableColumns.SAVEPOINT_TYPE)
                    || colName.equals(DataTableColumns.SAVEPOINT_CREATOR)) {
                b.append(colName).append(" TEXT NULL").append(endSeq);
            } else if (colName.equals(DataTableColumns.CONFLICT_TYPE)) {
                b.append(colName).append(" INTEGER NULL").append(endSeq);
            }
            //@formatter:on
        }
    }

    /**
     * Ensure that the kvs entry is valid.
     *
     * @param appName
     * @param tableId
     * @param kvs
     * @throws IllegalArgumentException
     */
    private void validateKVSEntry(String appName, String tableId, KeyValueStoreEntry kvs)
            throws IllegalArgumentException {

        if (kvs.tableId == null || kvs.tableId.trim().length() == 0) {
            throw new IllegalArgumentException("KVS entry has a null or empty tableId");
        }

        if (!kvs.tableId.equals(tableId)) {
            throw new IllegalArgumentException("KVS entry has a mismatched tableId");
        }

        if (kvs.partition == null || kvs.partition.trim().length() == 0) {
            throw new IllegalArgumentException("KVS entry has a null or empty partition");
        }

        if (kvs.aspect == null || kvs.aspect.trim().length() == 0) {
            throw new IllegalArgumentException("KVS entry has a null or empty aspect");
        }

        if (kvs.key == null || kvs.key.trim().length() == 0) {
            throw new IllegalArgumentException("KVS entry has a null or empty key");
        }

        // a null value will remove the entry from the KVS
        if (kvs.value != null && kvs.value.trim().length() != 0) {
            // validate the type....
            if (kvs.type == null || kvs.type.trim().length() == 0) {
                throw new IllegalArgumentException("KVS entry has a null or empty type");
            }

            // find subset matching the key...
            ArrayList<Object[]> kvsValueTypeRestrictions = keyToKnownKVSValueTypeRestrictions.get(kvs.key);

            if (kvsValueTypeRestrictions != null) {
                for (Object[] restriction : kvsValueTypeRestrictions) {
                    if (kvs.partition.equals(restriction[1]) && kvs.key.equals(restriction[2])) {
                        // see if the client specified an incorrect type
                        if (!kvs.type.equals(restriction[0])) {
                            String type = kvs.type;
                            kvs.type = (String) restriction[0];

                            // TODO: detect whether the value conforms to the specified type.
                            enforceKVSValueType(kvs, ElementDataType.valueOf(kvs.type));

                            WebLogger.getLogger(appName).w("validateKVSEntry",
                                    "Client Error: KVS value type reset from " + type + " to " + restriction[0]
                                            + " table: " + kvs.tableId + " partition: " + restriction[1] + " key: "
                                            + restriction[2]);
                        }
                    }
                }
            }
        } else {
            // makes later tests easier...
            kvs.value = null;
        }
    }

    private void enforceKVSValueType(KeyValueStoreEntry e, ElementDataType type) {
        e.type = type.name();
        if (e.value != null) {
            if (type == ElementDataType.integer) {
                // TODO: can add matcher if we want to
            } else if (type == ElementDataType.number) {
                // TODO: can add matcher if we want to
            } else if (type == ElementDataType.bool) {
                // TODO: can add matcher if we want to
            } else if (type == ElementDataType.string || type == ElementDataType.rowpath
                    || type == ElementDataType.configpath) {
                // anything goes here...
            } else if (type == ElementDataType.array) {
                // minimal test for valid representation
                if (!e.value.startsWith("[") || !e.value.endsWith("]")) {
                    throw new IllegalArgumentException("array value type is not an array! " + "TableId: "
                            + e.tableId + " Partition: " + e.partition + " Aspect: " + e.aspect + " Key: " + e.key);
                }
            } else if (type == ElementDataType.object) {
                // this could be any value type
                // TODO: test for any of the above values...
                if (e.value.startsWith("\"") && !e.value.endsWith("\"")) {
                    throw new IllegalArgumentException("object value type is a malformed string! " + "TableId: "
                            + e.tableId + " Partition: " + e.partition + " Aspect: " + e.aspect + " Key: " + e.key);
                }
                if (e.value.startsWith("[") && !e.value.endsWith("]")) {
                    throw new IllegalArgumentException("object value type is a malformed array! " + "TableId: "
                            + e.tableId + " Partition: " + e.partition + " Aspect: " + e.aspect + " Key: " + e.key);
                }
                if (e.value.startsWith("{") && !e.value.endsWith("}")) {
                    throw new IllegalArgumentException("object value type is a malformed object! " + "TableId: "
                            + e.tableId + " Partition: " + e.partition + " Aspect: " + e.aspect + " Key: " + e.key);
                }
            } else {
                // and who knows what goes here...
            }
        }
    }

    /**
     * REVISIT THESE TO ENFORCE SAFE UPDATES OF KVS database
     * *********************************************************************************************
     * *********************************************************************************************
     * *********************************************************************************************
     * *********************************************************************************************
     */

    /**
     * Insert or update a single table-level metadata KVS entry.
     * The tableId, partition, aspect and key cannot be null or empty strings.
     * If e.value is null or an empty string, the entry is deleted.
     *
     * @param db
     * @param e  a KeyValueStoreEntry. If e.value is null or an empty string, the entry is deleted.
     */
    public void replaceTableMetadata(OdkConnectionInterface db, KeyValueStoreEntry e) {
        validateKVSEntry(db.getAppName(), e.tableId, e);

        TreeMap<String, Object> values = new TreeMap<String, Object>();
        values.put(KeyValueStoreColumns.TABLE_ID, e.tableId);
        values.put(KeyValueStoreColumns.PARTITION, e.partition);
        values.put(KeyValueStoreColumns.ASPECT, e.aspect);
        values.put(KeyValueStoreColumns.KEY, e.key);
        values.put(KeyValueStoreColumns.VALUE_TYPE, e.type);
        values.put(KeyValueStoreColumns.VALUE, e.value);

        TreeMap<String, Object> metadataRev = new TreeMap<String, Object>();
        metadataRev.put(TableDefinitionsColumns.REV_ID, UUID.randomUUID().toString());

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }
            if (e.value == null || e.value.trim().length() == 0) {
                deleteTableMetadata(db, e.tableId, e.partition, e.aspect, e.key);
            } else {
                db.replaceOrThrow(DatabaseConstants.KEY_VALUE_STORE_ACTIVE_TABLE_NAME, null, values);
            }

            // Update the table definition table with a new revision ID, essentially telling all caches
            // of this table's metadata that they are dirty.
            db.update(DatabaseConstants.TABLE_DEFS_TABLE_NAME, metadataRev, K_TABLE_DEFS_TABLE_ID_EQUALS_PARAM,
                    new Object[] { e.tableId });

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * Insert or update a list of table-level metadata KVS entries. If clear is
     * true, then delete the existing set of values for this tableId before
     * inserting the new values.
     *
     * @param db
     * @param tableId
     * @param metadata a List<KeyValueStoreEntry>
     * @param clear    if true then delete the existing set of values for this tableId
     *                 before inserting the new ones.
     */
    public void replaceTableMetadata(OdkConnectionInterface db, String tableId, List<KeyValueStoreEntry> metadata,
            boolean clear) {

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            if (clear) {
                db.delete(DatabaseConstants.KEY_VALUE_STORE_ACTIVE_TABLE_NAME, K_KVS_TABLE_ID_EQUALS_PARAM,
                        new Object[] { tableId });
            }

            for (KeyValueStoreEntry e : metadata) {
                replaceTableMetadata(db, e);
            }

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    public void replaceTableMetadataSubList(OdkConnectionInterface db, String tableId, String partition,
            String aspect, List<KeyValueStoreEntry> metadata) {

        StringBuilder b = new StringBuilder();
        ArrayList<Object> whereArgsList = new ArrayList<Object>();

        if (tableId == null || tableId.trim().length() == 0) {
            throw new IllegalArgumentException("tableId cannot be null or an empty string");
        }
        b.append(K_KVS_TABLE_ID_EQUALS_PARAM);
        whereArgsList.add(tableId);
        if (partition != null) {
            b.append(S_AND).append(K_KVS_PARTITION_EQUALS_PARAM);
            whereArgsList.add(partition);
        }
        if (aspect != null) {
            b.append(S_AND).append(K_KVS_ASPECT_EQUALS_PARAM);
            whereArgsList.add(aspect);
        }
        String whereClause = b.toString();
        Object[] whereArgs = whereArgsList.toArray(new Object[whereArgsList.size()]);

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            db.delete(DatabaseConstants.KEY_VALUE_STORE_ACTIVE_TABLE_NAME, whereClause, whereArgs);

            for (KeyValueStoreEntry e : metadata) {
                replaceTableMetadata(db, e);
            }

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * The deletion filter includes all non-null arguments. If all arguments
     * (except the db) are null, then all properties are removed.
     *
     * @param db
     * @param tableId
     * @param partition
     * @param aspect
     * @param key
     */
    public void deleteTableMetadata(OdkConnectionInterface db, String tableId, String partition, String aspect,
            String key) {

        StringBuilder b = new StringBuilder();
        ArrayList<String> selArgs = new ArrayList<String>();
        if (tableId != null) {
            b.append(K_KVS_TABLE_ID_EQUALS_PARAM);
            selArgs.add(tableId);
        }
        if (partition != null) {
            if (b.length() != 0) {
                b.append(S_AND);
            }
            b.append(K_KVS_PARTITION_EQUALS_PARAM);
            selArgs.add(partition);
        }
        if (aspect != null) {
            if (b.length() != 0) {
                b.append(S_AND);
            }
            b.append(K_KVS_ASPECT_EQUALS_PARAM);
            selArgs.add(aspect);
        }
        if (key != null) {
            if (b.length() != 0) {
                b.append(S_AND);
            }
            b.append(K_KVS_KEY_EQUALS_PARAM);
            selArgs.add(key);
        }

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            db.delete(DatabaseConstants.KEY_VALUE_STORE_ACTIVE_TABLE_NAME, b.toString(),
                    selArgs.toArray(new String[selArgs.size()]));

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * Filters results by all non-null field values.
     *
     * @param db
     * @param tableId
     * @param partition
     * @param aspect
     * @param key
     * @return
     */
    public TableMetaDataEntries getTableMetadata(OdkConnectionInterface db, String tableId, String partition,
            String aspect, String key) {

        TableMetaDataEntries metadata = new TableMetaDataEntries(tableId, getTableDefinitionRevId(db, tableId));

        Cursor c = null;
        try {
            StringBuilder b = new StringBuilder();
            ArrayList<String> selArgs = new ArrayList<String>();
            if (tableId != null) {
                b.append(K_KVS_TABLE_ID_EQUALS_PARAM);
                selArgs.add(tableId);
            }
            if (partition != null) {
                if (b.length() != 0) {
                    b.append(S_AND);
                }
                b.append(K_KVS_PARTITION_EQUALS_PARAM);
                selArgs.add(partition);
            }
            if (aspect != null) {
                if (b.length() != 0) {
                    b.append(S_AND);
                }
                b.append(K_KVS_ASPECT_EQUALS_PARAM);
                selArgs.add(aspect);
            }
            if (key != null) {
                if (b.length() != 0) {
                    b.append(S_AND);
                }
                b.append(K_KVS_KEY_EQUALS_PARAM);
                selArgs.add(key);
            }

            c = db.query(DatabaseConstants.KEY_VALUE_STORE_ACTIVE_TABLE_NAME, null, b.toString(),
                    selArgs.toArray(new String[selArgs.size()]), null, null, null, null);
            if (c.moveToFirst()) {
                int idxTableId = c.getColumnIndex(KeyValueStoreColumns.TABLE_ID);
                int idxPartition = c.getColumnIndex(KeyValueStoreColumns.PARTITION);
                int idxAspect = c.getColumnIndex(KeyValueStoreColumns.ASPECT);
                int idxKey = c.getColumnIndex(KeyValueStoreColumns.KEY);
                int idxType = c.getColumnIndex(KeyValueStoreColumns.VALUE_TYPE);
                int idxValue = c.getColumnIndex(KeyValueStoreColumns.VALUE);

                do {
                    KeyValueStoreEntry e = new KeyValueStoreEntry();
                    e.tableId = c.getString(idxTableId);
                    e.partition = c.getString(idxPartition);
                    e.aspect = c.getString(idxAspect);
                    e.key = c.getString(idxKey);
                    e.type = c.getString(idxType);
                    e.value = c.getString(idxValue);
                    metadata.addEntry(e);
                } while (c.moveToNext());
            }
        } finally {
            if (c != null && !c.isClosed()) {
                c.close();
            }
        }
        return metadata;
    }

    /**
     * Clean up the KVS row data types. This simplifies the migration process by
     * enforcing the proper data types regardless of what the values are in the
     * imported CSV files.
     *
     * @param db
     */
    private void enforceTypesTableMetadata(OdkConnectionInterface db) {

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            StringBuilder b = new StringBuilder();
            b.setLength(0);
            //@formatter:off
            b.append("UPDATE ").append(DatabaseConstants.KEY_VALUE_STORE_ACTIVE_TABLE_NAME).append(" SET ")
                    .append(KeyValueStoreColumns.VALUE_TYPE).append(S_EQUALS_PARAM).append(K_WHERE)
                    .append(K_KVS_PARTITION_EQUALS_PARAM).append(S_AND).append(K_KVS_KEY_EQUALS_PARAM);
            //@formatter:on

            String sql = b.toString();

            for (Object[] fields : knownKVSValueTypeRestrictions) {
                db.execSQL(sql, fields);
            }

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /*
     * Create a user defined database table metadata - table definiton and KVS
     * values
     */
    private void createTableMetadata(OdkConnectionInterface db, String tableId) {
        if (tableId == null || tableId.length() <= 0) {
            throw new IllegalArgumentException(t + ": application name and table name must be specified");
        }

        // Add the table id into table definitions
        TreeMap<String, Object> cvTableDef = new TreeMap<String, Object>();
        cvTableDef.put(TableDefinitionsColumns.TABLE_ID, tableId);
        cvTableDef.put(TableDefinitionsColumns.REV_ID, UUID.randomUUID().toString());
        cvTableDef.put(TableDefinitionsColumns.SCHEMA_ETAG, null);
        cvTableDef.put(TableDefinitionsColumns.LAST_DATA_ETAG, null);
        cvTableDef.put(TableDefinitionsColumns.LAST_SYNC_TIME, -1);

        db.replaceOrThrow(DatabaseConstants.TABLE_DEFS_TABLE_NAME, null, cvTableDef);
    }

    /*
     * Create a user defined database table metadata - table definiton and KVS
     * values
     *
     * @param db
     * @param tableId
     * @param orderedDefs
     */
    private void createTableWithColumns(OdkConnectionInterface db, String tableId, OrderedColumns orderedDefs,
            boolean isSynchronized) {

        if (tableId == null || tableId.length() <= 0) {
            throw new IllegalArgumentException(t + ": application name and table name must be specified");
        }

        StringBuilder createTableCmdWithCols = new StringBuilder();
        createTableCmdWithCols.append("CREATE TABLE IF NOT EXISTS ").append(tableId).append(" (");

        if (isSynchronized) {
            addMetadataFieldsToTableCreationStatement(createTableCmdWithCols);
        }

        boolean first = true;
        for (ColumnDefinition column : orderedDefs.getColumnDefinitions()) {
            if (!column.isUnitOfRetention()) {
                continue;
            }
            ElementType elementType = column.getType();

            ElementDataType dataType = elementType.getDataType();
            String dbType;
            if (dataType == ElementDataType.array) {
                dbType = "TEXT";
            } else if (dataType == ElementDataType.bool) {
                dbType = "INTEGER";
            } else if (dataType == ElementDataType.configpath) {
                dbType = "TEXT";
            } else if (dataType == ElementDataType.integer) {
                dbType = "INTEGER";
            } else if (dataType == ElementDataType.number) {
                dbType = "REAL";
            } else if (dataType == ElementDataType.object) {
                dbType = "TEXT";
            } else if (dataType == ElementDataType.rowpath) {
                dbType = "TEXT";
            } else if (dataType == ElementDataType.string) {
                dbType = "TEXT";
            } else {
                throw new IllegalStateException("unexpected ElementDataType: " + dataType.name());
            }

            if (!first || isSynchronized) {
                createTableCmdWithCols.append(", ");
            }

            createTableCmdWithCols.append(column.getElementKey()).append(" ").append(dbType).append(" NULL");
            first = false;
        }

        createTableCmdWithCols.append(");");

        db.execSQL(createTableCmdWithCols.toString(), null);

        if (isSynchronized) {
            // Create the metadata for the table - table def and KVS
            createTableMetadata(db, tableId);

            // Now need to call the function to write out all the column values
            for (ColumnDefinition column : orderedDefs.getColumnDefinitions()) {
                createNewColumnMetadata(db, tableId, column);
            }
        }
    }

    /*
     * Create a new column metadata in the database - add column values to KVS and
     * column definitions
     */
    private void createNewColumnMetadata(OdkConnectionInterface db, String tableId, ColumnDefinition column) {
        String colName = column.getElementKey();

        // Create column definition
        TreeMap<String, Object> cvColDefVal = new TreeMap<String, Object>();
        cvColDefVal.put(ColumnDefinitionsColumns.TABLE_ID, tableId);
        cvColDefVal.put(ColumnDefinitionsColumns.ELEMENT_KEY, colName);
        cvColDefVal.put(ColumnDefinitionsColumns.ELEMENT_NAME, column.getElementName());
        cvColDefVal.put(ColumnDefinitionsColumns.ELEMENT_TYPE, column.getElementType());
        cvColDefVal.put(ColumnDefinitionsColumns.LIST_CHILD_ELEMENT_KEYS, column.getListChildElementKeys());

        // Now add this data into the database
        db.replaceOrThrow(DatabaseConstants.COLUMN_DEFINITIONS_TABLE_NAME, null, cvColDefVal);
    }

    /**
     * Verifies that the schema the client has matches that of the given tableId.
     *
     * @param db
     * @param tableId
     * @param orderedDefs
     */
    private void verifyTableSchema(OdkConnectionInterface db, String tableId, OrderedColumns orderedDefs) {
        // confirm that the column definitions are unchanged...
        OrderedColumns existingDefns = getUserDefinedColumns(db, tableId);
        if (existingDefns.getColumnDefinitions().size() != orderedDefs.getColumnDefinitions().size()) {
            throw new IllegalStateException(
                    "Unexpectedly found tableId with different column definitions that already exists!");
        }
        for (ColumnDefinition ci : orderedDefs.getColumnDefinitions()) {
            ColumnDefinition existingDefn;
            try {
                existingDefn = existingDefns.find(ci.getElementKey());
            } catch (IllegalArgumentException e) {
                throw new IllegalStateException("Unexpectedly failed to match elementKey: " + ci.getElementKey());
            }
            if (!existingDefn.getElementName().equals(ci.getElementName())) {
                throw new IllegalStateException(
                        "Unexpected mis-match of elementName for elementKey: " + ci.getElementKey());
            }
            List<ColumnDefinition> refList = existingDefn.getChildren();
            List<ColumnDefinition> ciList = ci.getChildren();
            if (refList.size() != ciList.size()) {
                throw new IllegalStateException(
                        "Unexpected mis-match of listOfStringElementKeys for elementKey: " + ci.getElementKey());
            }
            for (int i = 0; i < ciList.size(); ++i) {
                if (!refList.contains(ciList.get(i))) {
                    throw new IllegalStateException("Unexpected mis-match of listOfStringElementKeys[" + i
                            + "] for elementKey: " + ci.getElementKey());
                }
            }
            ElementType type = ci.getType();
            ElementType existingType = existingDefn.getType();
            if (!existingType.equals(type)) {
                throw new IllegalStateException(
                        "Unexpected mis-match of elementType for elementKey: " + ci.getElementKey());
            }
        }
    }

    /**
     * Compute the app-global choiceListId for this choiceListJSON
     * and register the tuple of (choiceListId, choiceListJSON).
     * Return choiceListId.
     *
     * @param db
     * @param choiceListJSON -- the actual JSON choice list text.
     * @return choiceListId -- the unique code mapping to the choiceListJSON
     */
    public String setChoiceList(OdkConnectionInterface db, String choiceListJSON) {
        ChoiceListUtils utils = new ChoiceListUtils();
        boolean dbWithinTransaction = db.inTransaction();
        boolean success = false;
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }
            if (choiceListJSON == null || choiceListJSON.trim().length() == 0) {
                return null;
            }

            String choiceListId = ODKFileUtils.getNakedMd5Hash(db.getAppName(), choiceListJSON);

            utils.setChoiceList(db, choiceListId, choiceListJSON);

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
            success = true;
            return choiceListId;
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
            if (!success) {

                WebLogger.getLogger(db.getAppName()).e(t,
                        "setChoiceList: Error while updating choiceList entry " + choiceListJSON);
            }
        }
    }

    /**
     * Return the choice list JSON corresponding to the choiceListId
     *
     * @param db
     * @param choiceListId -- the md5 hash of the choiceListJSON
     * @return choiceListJSON -- the actual JSON choice list text.
     */
    public String getChoiceList(OdkConnectionInterface db, String choiceListId) {
        ChoiceListUtils utils = new ChoiceListUtils();

        if (choiceListId == null || choiceListId.trim().length() == 0) {
            return null;
        }
        return utils.getChoiceList(db, choiceListId);
    }

    /**
     * If the tableId is not recorded in the TableDefinition metadata table, then
     * create the tableId with the indicated columns. This will synthesize
     * reasonable metadata KVS entries for table.
     * <p/>
     * If the tableId is present, then this is a no-op.
     *
     * @param db
     * @param tableId
     * @param columns
     * @return the ArrayList<ColumnDefinition> of the user columns in the table.
     */
    public OrderedColumns createOrOpenTableWithColumns(OdkConnectionInterface db, String tableId,
            List<Column> columns) {
        boolean dbWithinTransaction = db.inTransaction();
        boolean success = false;

        OrderedColumns orderedDefs = new OrderedColumns(db.getAppName(), tableId, columns);
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }
            if (!hasTableId(db, tableId)) {
                createTableWithColumns(db, tableId, orderedDefs, true);
            } else {
                verifyTableSchema(db, tableId, orderedDefs);
            }

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
            success = true;
            return orderedDefs;
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
            if (!success) {

                // Get the names of the columns
                StringBuilder colNames = new StringBuilder();
                if (columns != null) {
                    for (Column column : columns) {
                        colNames.append(" ").append(column.getElementKey()).append(",");
                    }
                    if (colNames != null && colNames.length() > 0) {
                        colNames.deleteCharAt(colNames.length() - 1);
                        WebLogger.getLogger(db.getAppName()).e(t,
                                "createOrOpenTableWithColumns: Error while adding table " + tableId
                                        + " with columns:" + colNames.toString());
                    }
                } else {
                    WebLogger.getLogger(db.getAppName()).e(t,
                            "createOrOpenTableWithColumns: Error while adding table " + tableId
                                    + " with columns: null");
                }
            }
        }
    }

    /**
     * If the tableId is not recorded in the TableDefinition metadata table, then
     * create the tableId with the indicated columns. This will synthesize
     * reasonable metadata KVS entries for table.
     * <p/>
     * If the tableId is present, then this is a no-op.
     *
     * @param db
     * @param tableId
     * @param columns
     * @return the ArrayList<ColumnDefinition> of the user columns in the table.
     */
    public OrderedColumns createOrOpenTableWithColumnsAndProperties(OdkConnectionInterface db, String tableId,
            List<Column> columns, List<KeyValueStoreEntry> metaData, boolean clear) throws JsonProcessingException {
        boolean dbWithinTransaction = db.inTransaction();
        boolean success = false;

        OrderedColumns orderedDefs = new OrderedColumns(db.getAppName(), tableId, columns);

        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }
            boolean created = false;
            if (!hasTableId(db, tableId)) {
                createTableWithColumns(db, tableId, orderedDefs, true);
                created = true;
            } else {
                // confirm that the column definitions are unchanged...
                verifyTableSchema(db, tableId, orderedDefs);
            }

            replaceTableMetadata(db, tableId, metaData, (clear || created));
            enforceTypesTableMetadata(db);

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
            success = true;
            return orderedDefs;
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
            if (!success) {

                // Get the names of the columns
                StringBuilder colNames = new StringBuilder();
                if (columns != null) {
                    for (Column column : columns) {
                        colNames.append(" ").append(column.getElementKey()).append(",");
                    }
                    if (colNames != null && colNames.length() > 0) {
                        colNames.deleteCharAt(colNames.length() - 1);
                        WebLogger.getLogger(db.getAppName()).e(t,
                                "createOrOpenTableWithColumnsAndProperties: Error while adding table " + tableId
                                        + " with columns:" + colNames.toString());
                    }
                } else {
                    WebLogger.getLogger(db.getAppName()).e(t,
                            "createOrOpenTableWithColumnsAndProperties: Error while adding table " + tableId
                                    + " with columns: null");
                }
            }
        }
    }

    /***********************************************************************************************
     * REVISIT THESE TO ENFORCE SAFE UPDATES OF KVS database
     * *********************************************************************************************
     * *********************************************************************************************
     * *********************************************************************************************
     * *********************************************************************************************
     */

    /**
     * SYNC only
     * <p/>
     * Call this when the schema on the server has changed w.r.t. the schema on
     * the device. In this case, we do not know whether the rows on the device
     * match those on the server.
     * <p/>
     * <ul>
     * <li>Reset all 'in_conflict' rows to their original local state (changed or
     * deleted).</li>
     * <li>Leave all 'deleted' rows in 'deleted' state.</li>
     * <li>Leave all 'changed' rows in 'changed' state.</li>
     * <li>Reset all 'synced' rows to 'new_row' to ensure they are sync'd to the
     * server.</li>
     * <li>Reset all 'synced_pending_files' rows to 'new_row' to ensure they are
     * sync'd to the server.</li>
     * </ul>
     *
     * @param db
     * @param tableId
     */
    private void changeDataRowsToNewRowState(OdkConnectionInterface db, String tableId) {

        StringBuilder b = new StringBuilder();

        // remove server conflicting rows
        b.setLength(0);
        b.append("DELETE FROM ").append(tableId).append(K_WHERE).append(DataTableColumns.SYNC_STATE)
                .append(S_EQUALS_PARAM).append(S_AND).append(DataTableColumns.CONFLICT_TYPE).append(" IN (?, ?)");

        String sqlConflictingServer = b.toString();
        //@formatter:off
        String argsConflictingServer[] = { SyncState.in_conflict.name(),
                Integer.toString(ConflictType.SERVER_DELETED_OLD_VALUES),
                Integer.toString(ConflictType.SERVER_UPDATED_UPDATED_VALUES) };
        //@formatter:on

        // update local delete conflicts to deletes
        b.setLength(0);
        //@formatter:off
        b.append("UPDATE ").append(tableId).append(" SET ").append(DataTableColumns.SYNC_STATE)
                .append(S_EQUALS_PARAM).append(", ").append(DataTableColumns.CONFLICT_TYPE).append(" = null")
                .append(K_WHERE).append(DataTableColumns.CONFLICT_TYPE).append(S_EQUALS_PARAM);
        //@formatter:on

        String sqlConflictingLocalDeleting = b.toString();
        //@formatter:off
        String argsConflictingLocalDeleting[] = { SyncState.deleted.name(),
                Integer.toString(ConflictType.LOCAL_DELETED_OLD_VALUES) };
        //@formatter:on

        // update local update conflicts to updates
        String sqlConflictingLocalUpdating = sqlConflictingLocalDeleting;
        //@formatter:off
        String argsConflictingLocalUpdating[] = { SyncState.changed.name(),
                Integer.toString(ConflictType.LOCAL_UPDATED_UPDATED_VALUES) };
        //@formatter:on

        // reset all 'rest' rows to 'insert'
        b.setLength(0);
        //@formatter:off
        b.append("UPDATE ").append(tableId).append(" SET ").append(DataTableColumns.SYNC_STATE)
                .append(S_EQUALS_PARAM).append(K_WHERE).append(DataTableColumns.SYNC_STATE).append(S_EQUALS_PARAM);
        //@formatter:on

        String sqlRest = b.toString();
        //@formatter:off
        String argsRest[] = { SyncState.new_row.name(), SyncState.synced.name() };
        //@formatter:on

        String sqlRestPendingFiles = sqlRest;
        //@formatter:off
        String argsRestPendingFiles[] = { SyncState.new_row.name(), SyncState.synced_pending_files.name() };
        //@formatter:on

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            db.execSQL(sqlConflictingServer, argsConflictingServer);
            db.execSQL(sqlConflictingLocalDeleting, argsConflictingLocalDeleting);
            db.execSQL(sqlConflictingLocalUpdating, argsConflictingLocalUpdating);
            db.execSQL(sqlRest, argsRest);
            db.execSQL(sqlRestPendingFiles, argsRestPendingFiles);

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * SYNC
     * <p/>
     * Clean up this table and set the dataETag to null.
     * <p/>
     * changeDataRowsToNewRowState(sc.getAppName(), db, tableId);
     * <p/>
     * we need to clear out the dataETag so
     * that we will pull all server changes and sync our properties.
     * <p/>
     * updateTableETags(sc.getAppName(), db, tableId, null, null);
     * <p/>
     * Although the server does not recognize this tableId, we can
     * keep our record of the ETags for the table-level files and
     * manifest. These may enable us to short-circuit the restoration
     * of the table-level files should another client be simultaneously
     * trying to restore those files to the server.
     * <p/>
     * However, we do need to delete all the instance-level files,
     * as these are tied to the schemaETag we hold, and that is now
     * invalid.
     * <p/>
     * if the local table ever had any server sync information for this
     * host then clear it. If the user changed the server URL, we have
     * already cleared this information.
     * <p/>
     * Clearing it here handles the case where an admin deleted the
     * table on the server and we are now re-pushing that table to
     * the server.
     * <p/>
     * We do not know whether the rows on the device match those on the server.
     * We will find out later, in the course of the sync.
     * <p/>
     * if (tableInstanceFilesUri != null) {
     * deleteAllSyncETagsUnderServer(sc.getAppName(), db, tableInstanceFilesUri);
     * }
     *
     * @param db
     * @param tableId
     * @param tableInstanceFilesUri
     * @parma schemaETag
     */
    public void serverTableSchemaETagChanged(OdkConnectionInterface db, String tableId, String schemaETag,
            String tableInstanceFilesUri) {

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            changeDataRowsToNewRowState(db, tableId);

            privilegedUpdateTableETags(db, tableId, schemaETag, null);

            if (tableInstanceFilesUri != null) {
                SyncETagsUtils seu = new SyncETagsUtils();
                seu.deleteAllSyncETagsUnderServer(db, tableInstanceFilesUri);
            }

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * Deletes the server conflict row (if any) for this rowId in this tableId.
     *
     * @param db
     * @param tableId
     * @param rowId
     */
    private void deleteServerConflictRowWithId(OdkConnectionInterface db, String tableId, String rowId) {
        // delete the old server-values in_conflict row if it exists
        StringBuilder b = new StringBuilder();
        b.append(K_DATATABLE_ID_EQUALS_PARAM).append(S_AND).append(DataTableColumns.SYNC_STATE)
                .append(S_EQUALS_PARAM).append(S_AND).append(DataTableColumns.CONFLICT_TYPE).append(" IN ( ?, ? )");
        Object[] whereArgs = { rowId, SyncState.in_conflict.name(),
                String.valueOf(ConflictType.SERVER_DELETED_OLD_VALUES),
                String.valueOf(ConflictType.SERVER_UPDATED_UPDATED_VALUES) };

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            db.delete(tableId, b.toString(), whereArgs);

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * Delete any prior server conflict row.
     * Move the local row into the indicated local conflict state.
     * Insert a server row with the values specified in the cvValues array.
     *
     * @param db
     * @param tableId
     * @param orderedColumns
     * @param serverValues
     * @param rowId
     * @param localRowConflictType
     * @param activeUser
     * @param locale
     */
    public void privilegedPlaceRowIntoConflictWithId(OdkConnectionInterface db, String tableId,
            OrderedColumns orderedColumns, ContentValues serverValues, String rowId, int localRowConflictType,
            String activeUser, String locale) {

        // The rolesList of the activeUser does not impact the execution of this action.
        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            this.deleteServerConflictRowWithId(db, tableId, rowId);
            this.placeRowIntoConflict(db, tableId, rowId, localRowConflictType);
            this.privilegedInsertRowWithId(db, tableId, orderedColumns, serverValues, rowId, activeUser, locale,
                    false);

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    public boolean identicalValue(String localValue, String serverValue, ElementDataType dt) {

        if (localValue == null && serverValue == null) {
            return true;
        } else if (localValue == null || serverValue == null) {
            return false;
        } else if (localValue.equals(serverValue)) {
            return true;
        }

        // NOT textually identical.
        //
        // Everything must be textually identical except possibly number fields
        // which may have rounding due to different database implementations,
        // data representations, and marshaling libraries.
        //
        if (dt == ElementDataType.number) {
            // !!Important!! Double.valueOf(str) handles NaN and +/-Infinity
            Double localNumber = Double.valueOf(localValue);
            Double serverNumber = Double.valueOf(serverValue);

            if (localNumber.equals(serverNumber)) {
                // simple case -- trailing zeros or string representation mix-up
                //
                return true;
            } else if (localNumber.isInfinite() && serverNumber.isInfinite()) {
                // if they are both plus or both minus infinity, we have a match
                if (Math.signum(localNumber) == Math.signum(serverNumber)) {
                    return true;
                } else {
                    return false;
                }
            } else if (localNumber.isNaN() || localNumber.isInfinite() || serverNumber.isNaN()
                    || serverNumber.isInfinite()) {
                // one or the other is special1
                return false;
            } else {
                double localDbl = localNumber;
                double serverDbl = serverNumber;
                if (localDbl == serverDbl) {
                    return true;
                }
                // OK. We have two values like 9.80 and 9.8
                // consider them equal if they are adjacent to each other.
                double localNear = localDbl;
                int idist = 0;
                int idistMax = 128;
                for (idist = 0; idist < idistMax; ++idist) {
                    localNear = Math.nextAfter(localNear, serverDbl);
                    if (localNear == serverDbl) {
                        break;
                    }
                }
                if (idist < idistMax) {
                    return true;
                }
                return false;
            }
        } else {
            // textual identity is required!
            return false;
        }
    }

    /**
     * Delete any prior server conflict row.
     * Examine database and incoming server values to determine how to apply the
     * server values to the database. This might delete the existing row, update
     * it, or create a conflict row.
     *
     * @param db
     * @param tableId
     * @param orderedColumns
     * @param serverValues  field values for this row coming from server.
     *                      All fields must have values. The SyncState field
     *                      (a local field that does not come from the server)
     *                      should be "changed" or "deleted"
     * @param rowId
     * @param activeUser
     * @param rolesList  passed in to determine if the current user is a privileged user
     * @param locale
     */
    public void privilegedPerhapsPlaceRowIntoConflictWithId(OdkConnectionInterface db, String tableId,
            OrderedColumns orderedColumns, ContentValues serverValues, String rowId, String activeUser,
            String rolesList, String locale) {

        AccessContext accessContext = getAccessContext(db, tableId, activeUser, rolesList);

        // The rolesList of the activeUser does not impact the execution of this action.
        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            // delete any existing server conflict row
            this.deleteServerConflictRowWithId(db, tableId, rowId);
            // fetch the current local (possibly-in-conflict) row
            BaseTable baseTable = this.privilegedGetRowsWithId(db, tableId, rowId, activeUser);
            if (baseTable.getNumberOfRows() == 0) {
                throw new IllegalArgumentException("no matching row found for server conflict");
            } else if (baseTable.getNumberOfRows() != 1) {
                throw new IllegalArgumentException("row has checkpoints or database is corrupt");
            }

            Row localRow = baseTable.getRowAtIndex(0);

            if (localRow.getDataByKey(DataTableColumns.SAVEPOINT_TYPE) == null) {
                throw new IllegalArgumentException("row has checkpoints");
            }

            String strSyncState = localRow.getDataByKey(DataTableColumns.SYNC_STATE);
            SyncState state = SyncState.valueOf(strSyncState);

            int localRowConflictTypeBeforeSync = -1;
            if (state == SyncState.in_conflict) {
                // we need to remove the in_conflict records that refer to the
                // prior state of the server
                String localRowConflictTypeBeforeSyncStr = localRow.getDataByKey(DataTableColumns.CONFLICT_TYPE);
                if (localRowConflictTypeBeforeSyncStr == null) {
                    // this row is in conflict. It MUST have a non-null conflict type.
                    throw new IllegalStateException("conflict type is null on an in-conflict row");
                }

                localRowConflictTypeBeforeSync = Integer.parseInt(localRowConflictTypeBeforeSyncStr);
                if (localRowConflictTypeBeforeSync == ConflictType.SERVER_DELETED_OLD_VALUES
                        || localRowConflictTypeBeforeSync == ConflictType.SERVER_UPDATED_UPDATED_VALUES) {
                    // should be impossible
                    throw new IllegalStateException("only the local conflict record should remain");
                }
            }

            boolean isServerRowDeleted = serverValues.getAsString(DataTableColumns.SYNC_STATE)
                    .equals(SyncState.deleted.name());

            boolean executeDropThrough = false;

            if (isServerRowDeleted) {
                if (state == SyncState.synced) {
                    // the server's change should be applied locally.
                    this.privilegedDeleteRowWithId(db, tableId, rowId, activeUser);
                } else if (state == SyncState.synced_pending_files) {
                    // sync logic may want to UPLOAD local files up to server before calling this routine.
                    // this would prevent loss of attachments that have not yet been pushed to server.

                    // the server's change should be applied locally.
                    this.privilegedDeleteRowWithId(db, tableId, rowId, activeUser);
                } else if ((state == SyncState.deleted) || ((state == SyncState.in_conflict)
                        && (localRowConflictTypeBeforeSync == ConflictType.LOCAL_DELETED_OLD_VALUES))) {
                    // this occurs if
                    // (1) a delete request was never ACKed but it was performed
                    // on the server.
                    // (2) if there is an unresolved conflict held locally with the
                    // local action being to delete the record, and the prior server
                    // state being a value change, but the newly sync'd state now
                    // reflects a deletion by another party.
                    //
                    // the server's change should be applied locally.
                    this.privilegedDeleteRowWithId(db, tableId, rowId, activeUser);
                } else {
                    // need to resolve a conflict situation...
                    executeDropThrough = true;
                }
            } else if (state == SyncState.synced || state == SyncState.synced_pending_files) {
                // When a prior sync ends with conflicts, we will not update the table's "lastDataETag"
                // and when we next sync, we will pull the same server row updates as when the
                // conflicts were raised (elsewhere in the table).
                //
                // Therefore, we can expect many server row updates to have already been locally
                // applied (for the rows were not in conflict). Detect and ignore these already-
                // processed changes by testing for the server and device having identical field values.
                //

                boolean isDifferent = false;
                for (int i = 0; i < baseTable.getWidth(); ++i) {
                    String colName = baseTable.getElementKey(i);
                    if (DataTableColumns.ID.equals(colName) || DataTableColumns.CONFLICT_TYPE.equals(colName)
                            || DataTableColumns.EFFECTIVE_ACCESS.equals(colName)
                            || DataTableColumns.SYNC_STATE.equals(colName)) {
                        // these values are ignored during comparisons
                        continue;
                    }
                    String localValue = localRow.getDataByKey(colName);
                    String serverValue = serverValues.containsKey(colName) ? serverValues.getAsString(colName)
                            : null;

                    ElementDataType dt = ElementDataType.string;
                    try {
                        ColumnDefinition cd = orderedColumns.find(colName);
                        dt = cd.getType().getDataType();
                    } catch (IllegalArgumentException e) {
                        // ignore
                    }
                    if (!identicalValue(localValue, serverValue, dt)) {
                        isDifferent = true;
                        break;
                    }
                }

                if (isDifferent) {
                    // Local row needs to be updated with server values.
                    //
                    // detect and handle file attachment column changes and sync state
                    // (need to change serverRow value of this)
                    boolean hasNonNullAttachments = false;

                    for (ColumnDefinition cd : orderedColumns.getColumnDefinitions()) {
                        // todo: does not handle array containing (types containing) rowpath elements
                        if (cd.isUnitOfRetention() && cd.getType().getDataType().equals(ElementDataType.rowpath)) {
                            String uriFragment = serverValues.getAsString(cd.getElementKey());
                            String localUriFragment = localRow.getDataByKey(cd.getElementKey());
                            if (uriFragment != null) {
                                if (localUriFragment == null || !localUriFragment.equals(uriFragment)) {
                                    hasNonNullAttachments = true;
                                    break;
                                }
                            }
                        }
                    }

                    // update the row from the changes on the server
                    ContentValues values = new ContentValues(serverValues);

                    values.put(DataTableColumns.SYNC_STATE,
                            (hasNonNullAttachments || (state == SyncState.synced_pending_files))
                                    ? SyncState.synced_pending_files.name()
                                    : SyncState.synced.name());
                    values.putNull(DataTableColumns.CONFLICT_TYPE);

                    this.privilegedUpdateRowWithId(db, tableId, orderedColumns, values, rowId, activeUser, locale,
                            false);
                }

                // and don't execute the drop-through in this case.
            } else {
                executeDropThrough = true;
            }

            if (executeDropThrough) {
                // SyncState.deleted and server is not deleting
                // SyncState.new_row and record exists on server
                // SyncState.changed and new change (or delete) on server
                // SyncState.in_conflict and new change (or delete) on server

                // ALSO: this case can occur when our prior sync attempt pulled down changes that
                // placed local row(s) in conflict -- which we have since resolved -- and we are now
                // issuing a sync to push those changes up to the server.
                //
                // This is because we do not update our local table's "lastDataETag" until we have
                // sync'd and applied all changes from the server and have no local conflicts or
                // checkpoints on the table. Because of this, when we issue a sync after resolving
                // a conflict, we will get the set of server row changes that include the row(s)
                // that were previously in conflict. This will appear as one of:
                //    changed | changed
                //    changed | deleted
                //    deleted | changed
                //    deleted | deleted
                //
                // BUT, this time, however, the local records will have the same rowETag as the
                // server record, indicating that they are valid changes (or deletions) on top of
                // the server's current version of this same row.
                //
                // If this is the case (the rowETags match), then we should not place the row into
                // conflict, but should instead ignore the reported content from the server and push
                // the local row's change or delete up to the server in the next section.
                //
                // If not, when we reach this point in the code, the rowETag of the server row
                // should **not match** our local row -- indicating that the server has a change from
                // another source, and that we should place the row into conflict.
                //
                String localRowETag = localRow.getDataByKey(DataTableColumns.ROW_ETAG);
                String serverRowETag = serverValues.getAsString(DataTableColumns.ROW_ETAG);
                boolean isDifferentRowETag = (localRowETag == null) || !localRowETag.equals(serverRowETag);
                if (!isDifferentRowETag) {
                    // ignore the server record.
                    // This is an update we will push to the server.
                    // todo: make sure local row is not in_conflict at this point.
                    if (state == SyncState.in_conflict) {
                        // don't think this is logically possible at this point, but we should
                        // transition record to changed or deleted state.
                        ContentValues values = new ContentValues();
                        for (int i = 0; i < baseTable.getWidth(); ++i) {
                            String colName = baseTable.getElementKey(i);
                            if (DataTableColumns.EFFECTIVE_ACCESS.equals(colName)) {
                                continue;
                            }
                            if (localRow.getDataByIndex(i) == null) {
                                values.putNull(colName);
                            } else {
                                values.put(colName, localRow.getDataByIndex(i));
                            }
                        }
                        values.put(DataTableColumns.ID, rowId);
                        values.putNull(DataTableColumns.CONFLICT_TYPE);
                        values.put(DataTableColumns.SYNC_STATE,
                                (localRowConflictTypeBeforeSync == ConflictType.LOCAL_DELETED_OLD_VALUES)
                                        ? SyncState.deleted.name()
                                        : SyncState.changed.name());

                        this.privilegedUpdateRowWithId(db, tableId, orderedColumns, values, rowId, activeUser,
                                locale, false);
                    }
                } else {
                    // figure out what the localRow conflict type should be...
                    int localRowConflictType;
                    if (state == SyncState.changed) {
                        // SyncState.changed and new change on server
                        localRowConflictType = ConflictType.LOCAL_UPDATED_UPDATED_VALUES;
                    } else if (state == SyncState.new_row) {
                        // SyncState.new_row and record exists on server
                        // The 'new_row' case occurs if an insert is never ACKed but
                        // completes successfully on the server.
                        localRowConflictType = ConflictType.LOCAL_UPDATED_UPDATED_VALUES;
                    } else if (state == SyncState.deleted) {
                        // SyncState.deleted and server is not deleting
                        localRowConflictType = ConflictType.LOCAL_DELETED_OLD_VALUES;
                    } else if (state == SyncState.in_conflict) {
                        // SyncState.in_conflict and new change on server
                        // leave the local conflict type unchanged (retrieve it and
                        // use it).
                        localRowConflictType = localRowConflictTypeBeforeSync;
                    } else {
                        throw new IllegalStateException("Unexpected state encountered");
                    }

                    boolean isDifferentPrivilegedFields = false;
                    {
                        String[] privilegedColumns = new String[] { DataTableColumns.FILTER_TYPE,
                                DataTableColumns.FILTER_VALUE };
                        for (int i = 0; i < privilegedColumns.length; ++i) {
                            String colName = privilegedColumns[i];
                            String localValue = localRow.getDataByKey(colName);
                            String serverValue = serverValues.containsKey(colName)
                                    ? serverValues.getAsString(colName)
                                    : null;

                            ElementDataType dt = ElementDataType.string;
                            try {
                                ColumnDefinition cd = orderedColumns.find(colName);
                                dt = cd.getType().getDataType();
                            } catch (IllegalArgumentException e) {
                                // ignore
                            }

                            boolean sameValue = identicalValue(localValue, serverValue, dt);

                            if (!sameValue) {
                                isDifferentPrivilegedFields = true;
                                break;
                            }
                        }
                    }
                    boolean isDifferentExcludingPrivilegedFields = false;
                    for (int i = 0; i < baseTable.getWidth(); ++i) {
                        String colName = baseTable.getElementKey(i);
                        if (DataTableColumns.ID.equals(colName) || DataTableColumns.CONFLICT_TYPE.equals(colName)
                                || DataTableColumns.EFFECTIVE_ACCESS.equals(colName)
                                || DataTableColumns.SYNC_STATE.equals(colName)
                                || DataTableColumns.ROW_ETAG.equals(colName)
                                || DataTableColumns.FILTER_TYPE.equals(colName)
                                || DataTableColumns.FILTER_VALUE.equals(colName)) {
                            // these values are ignored during this comparison
                            continue;
                        }
                        String localValue = localRow.getDataByKey(colName);
                        String serverValue = serverValues.containsKey(colName) ? serverValues.getAsString(colName)
                                : null;

                        ElementDataType dt = ElementDataType.string;
                        try {
                            ColumnDefinition cd = orderedColumns.find(colName);
                            dt = cd.getType().getDataType();
                        } catch (IllegalArgumentException e) {
                            // ignore
                        }

                        if (serverValue != null && dt == ElementDataType.bool) {
                            serverValue = Integer
                                    .toString(DataHelper.boolToInt(serverValues.getAsBoolean(colName)));
                        }
                        boolean sameValue = identicalValue(localValue, serverValue, dt);

                        if (!sameValue) {
                            isDifferentExcludingPrivilegedFields = true;
                            break;
                        }
                    }

                    boolean hasNonNullDifferingServerAttachments = false;

                    if (isDifferentExcludingPrivilegedFields || (state != SyncState.synced)) {
                        for (ColumnDefinition cd : orderedColumns.getColumnDefinitions()) {
                            // todo: does not handle array containing (types containing) rowpath elements
                            if (cd.isUnitOfRetention()
                                    && cd.getType().getDataType().equals(ElementDataType.rowpath)) {
                                String uriFragment = serverValues.getAsString(cd.getElementKey());
                                String localUriFragment = localRow.getDataByKey(cd.getElementKey());
                                if (uriFragment != null) {
                                    if (localUriFragment == null || !localUriFragment.equals(uriFragment)) {
                                        hasNonNullDifferingServerAttachments = true;
                                    }
                                }
                            }
                        }
                    }

                    ContentValues values = new ContentValues(serverValues);

                    if (isDifferentExcludingPrivilegedFields || isDifferentPrivilegedFields || isDifferentRowETag
                            || isServerRowDeleted) {

                        if (isDifferentExcludingPrivilegedFields || isServerRowDeleted
                                || (localRowConflictType == ConflictType.LOCAL_DELETED_OLD_VALUES)
                                || (accessContext.isPrivilegedUser && isDifferentPrivilegedFields)) {

                            this.placeRowIntoConflict(db, tableId, rowId, localRowConflictType);
                            serverValues.put(DataTableColumns.SYNC_STATE, SyncState.in_conflict.name());
                            serverValues.put(DataTableColumns.CONFLICT_TYPE,
                                    (isServerRowDeleted ? ConflictType.SERVER_DELETED_OLD_VALUES
                                            : ConflictType.SERVER_UPDATED_UPDATED_VALUES));
                            this.privilegedInsertRowWithId(db, tableId, orderedColumns, serverValues, rowId,
                                    activeUser, locale, false);
                        } else {
                            // just apply the server RowETag and filterScope to the local row
                            values.put(DataTableColumns.SYNC_STATE,
                                    hasNonNullDifferingServerAttachments ? SyncState.synced_pending_files.name()
                                            : SyncState.synced.name());
                            values.putNull(DataTableColumns.CONFLICT_TYPE);

                            // move the local conflict back into the normal non-conflict (null) state
                            // set the sync state to "changed" temporarily (otherwise we can't update)

                            this.restoreRowFromConflict(db, tableId, rowId, SyncState.changed,
                                    localRowConflictTypeBeforeSync);

                            this.privilegedUpdateRowWithId(db, tableId, orderedColumns, values, rowId, activeUser,
                                    locale, false);
                        }
                    } else {
                        // data matches -- update row to adjust sync state and conflict type
                        // if needed.
                        SyncState destState = (hasNonNullDifferingServerAttachments
                                || (state == SyncState.synced_pending_files)) ? SyncState.synced_pending_files
                                        : SyncState.synced;

                        if ((state != destState) || isDifferentRowETag
                                || (localRow.getDataByKey(DataTableColumns.CONFLICT_TYPE) != null)) {
                            // todo: handle case where local row was in conflict
                            // server has now matched the local row's state. i.e.,
                            // update rowEtag, clear conflictType and adjust syncState on row.
                            values.put(DataTableColumns.SYNC_STATE, destState.name());
                            values.putNull(DataTableColumns.CONFLICT_TYPE);

                            // move the local conflict back into the normal non-conflict (null) state
                            // set the sync state to "changed" temporarily (otherwise we can't update)

                            this.restoreRowFromConflict(db, tableId, rowId, SyncState.changed,
                                    localRowConflictTypeBeforeSync);

                            this.privilegedUpdateRowWithId(db, tableId, orderedColumns, values, rowId, activeUser,
                                    locale, false);

                            WebLogger.getLogger(db.getAppName()).w(t,
                                    "identical rows returned from server -- " + "SHOULDN'T THESE NOT HAPPEN?");
                        }
                    }
                }
            }

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * Change the conflictType for the given row from null (not in conflict) to
     * the specified one.
     *
     * @param db
     * @param tableId
     * @param rowId
     * @param conflictType expected to be one of ConflictType.LOCAL_DELETED_OLD_VALUES (0) or
     *                     ConflictType.LOCAL_UPDATED_UPDATED_VALUES (1)
     */
    private void placeRowIntoConflict(OdkConnectionInterface db, String tableId, String rowId, int conflictType) {

        StringBuilder b = new StringBuilder();
        b.append(K_DATATABLE_ID_EQUALS_PARAM).append(S_AND).append(DataTableColumns.CONFLICT_TYPE)
                .append(S_IS_NULL);
        Object[] whereArgs = { rowId };

        TreeMap<String, Object> cv = new TreeMap<String, Object>();
        cv.put(DataTableColumns.SYNC_STATE, SyncState.in_conflict.name());
        cv.put(DataTableColumns.CONFLICT_TYPE, conflictType);

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            db.update(tableId, cv, b.toString(), whereArgs);

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * Changes the conflictType for the given row from the specified one to null
     * and set the sync state of this row to the indicated value. In general, you
     * should first update the local conflict record with its new values, then
     * call deleteServerConflictRowWithId(...) and then call this method.
     *
     * @param db
     * @param tableId
     * @param rowId
     * @param syncState
     * @param conflictType
     */
    private void restoreRowFromConflict(OdkConnectionInterface db, String tableId, String rowId,
            SyncState syncState, Integer conflictType) {

        // TODO: is roleList applicable here?

        StringBuilder b = new StringBuilder();
        Object[] whereArgs;

        if (conflictType == null) {
            b.append(K_DATATABLE_ID_EQUALS_PARAM).append(S_AND).append(DataTableColumns.CONFLICT_TYPE)
                    .append(S_IS_NULL);
            whereArgs = new Object[] { rowId };
        } else {
            b.append(K_DATATABLE_ID_EQUALS_PARAM).append(S_AND).append(DataTableColumns.CONFLICT_TYPE)
                    .append(S_EQUALS_PARAM);
            whereArgs = new Object[] { rowId, conflictType };
        }

        TreeMap<String, Object> cv = new TreeMap<String, Object>();
        cv.put(DataTableColumns.CONFLICT_TYPE, null);
        cv.put(DataTableColumns.SYNC_STATE, syncState.name());
        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            db.update(tableId, cv, b.toString(), whereArgs);

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * @param db
     * @param tableId
     * @param rowId
     * @return the sync state of the row (see {@link SyncState}), or null if the
     * row does not exist.  Rows are required to have non-null sync states.
     * @throws IllegalStateException if the row has a null sync state or has
     *                               2+ conflicts or checkpoints and
     *                               those do not have matching sync states!
     */
    public SyncState getSyncState(OdkConnectionInterface db, String tableId, String rowId)
            throws IllegalStateException {
        Cursor c = null;
        try {
            c = db.query(tableId, new String[] { DataTableColumns.SYNC_STATE }, K_DATATABLE_ID_EQUALS_PARAM,
                    new Object[] { rowId }, null, null, null, null);

            if (c.moveToFirst()) {
                int syncStateIndex = c.getColumnIndex(DataTableColumns.SYNC_STATE);
                if (c.isNull(syncStateIndex)) {
                    throw new IllegalStateException(t + ": row had a null sync state!");
                }
                String val = CursorUtils.getIndexAsString(c, syncStateIndex);
                while (c.moveToNext()) {
                    if (c.isNull(syncStateIndex)) {
                        throw new IllegalStateException(t + ": row had a null sync state!");
                    }
                    String otherVal = CursorUtils.getIndexAsString(c, syncStateIndex);
                    if (!val.equals(otherVal)) {
                        throw new IllegalStateException(t + ": row with 2+ conflicts or checkpoints does "
                                + "not have matching sync states!");
                    }
                }
                return SyncState.valueOf(val);
            }
            return null;
        } finally {
            if (c != null && !c.isClosed()) {
                c.close();
            }
        }
    }

    /**
     * Delete the specified rowId in this tableId. Deletion respects sync
     * semantics. If the row is in the SyncState.new_row state, then the row and
     * its associated file attachments are immediately deleted. Otherwise, the row
     * is placed into the SyncState.deleted state and will be retained until the
     * device can delete the record on the server.
     * <p/>
     * If you need to immediately delete a record that would otherwise sync to the
     * server, call updateRowETagAndSyncState(...) to set the row to
     * SyncState.new_row, and then call this method and it will be immediately
     * deleted (in this case, unless the record on the server was already deleted,
     * it will remain and not be deleted during any subsequent synchronizations).
     *
     * @param db
     * @param tableId
     * @param rowId
     * @param activeUser
     * @param rolesList
     * @throws ActionNotAuthorizedException
     */
    public void deleteRowWithId(OdkConnectionInterface db, String tableId, String rowId, String activeUser,
            String rolesList) throws ActionNotAuthorizedException {

        // TODO: rolesList of user may impact whether we can delete the record.
        // Particularly with sync'd records, is there anything special to do here?
        // consider sync path vs. tools path.
        // I.e., if the user is super-user or higher, we should take local FilterScope.
        // otherwise, we should take server FilterScope. Or should we allow user to select
        // which to take?

        boolean shouldPhysicallyDelete = false;

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            Object[] whereArgs = new Object[] { rowId };
            String whereClause = K_DATATABLE_ID_EQUALS_PARAM;

            // first need to test whether we can delete all the rows under this rowId.
            // If we can't, then throw an access violation
            Cursor c = null;
            try {
                c = db.query(tableId,
                        new String[] { DataTableColumns.SYNC_STATE, DataTableColumns.FILTER_TYPE,
                                DataTableColumns.FILTER_VALUE },
                        whereClause, whereArgs, null, null, DataTableColumns.SAVEPOINT_TIMESTAMP + " ASC", null);
                boolean hasFirst = c.moveToFirst();

                int idxSyncState = c.getColumnIndex(DataTableColumns.SYNC_STATE);
                int idxFilterType = c.getColumnIndex(DataTableColumns.FILTER_TYPE);
                int idxFilterValue = c.getColumnIndex(DataTableColumns.FILTER_VALUE);

                List<String> rolesArray = getRolesArray(rolesList);

                TableSecuritySettings tss = getTableSecuritySettings(db, tableId);

                if (hasFirst) {
                    do {
                        // verify each row
                        String priorSyncState = c.getString(idxSyncState);
                        String priorFilterType = c.isNull(idxFilterType) ? null : c.getString(idxFilterType);
                        String priorFilterValue = c.isNull(idxFilterValue) ? null : c.getString(idxFilterValue);

                        tss.allowRowChange(activeUser, rolesArray, priorSyncState, priorFilterType,
                                priorFilterValue, RowChange.DELETE_ROW);

                    } while (c.moveToNext());
                }

            } finally {
                if (c != null && !c.isClosed()) {
                    c.close();
                }
                c = null;
            }

            // delete any checkpoints
            whereClause = K_DATATABLE_ID_EQUALS_PARAM + S_AND + DataTableColumns.SAVEPOINT_TYPE + S_IS_NULL;
            db.delete(tableId, whereClause, whereArgs);

            // this will return null if there are no rows.
            SyncState syncState = getSyncState(db, tableId, rowId);

            if (syncState == null) {
                // the rowId no longer exists (we deleted all checkpoints)
                shouldPhysicallyDelete = true;

            } else if (syncState == SyncState.new_row) {
                // we can safely remove this record from the database
                whereClause = K_DATATABLE_ID_EQUALS_PARAM;

                db.delete(tableId, whereClause, whereArgs);
                shouldPhysicallyDelete = true;

            } else if (syncState != SyncState.in_conflict) {

                TreeMap<String, Object> values = new TreeMap<String, Object>();
                values.put(DataTableColumns.SYNC_STATE, SyncState.deleted.name());
                values.put(DataTableColumns.SAVEPOINT_TIMESTAMP,
                        TableConstants.nanoSecondsFromMillis(System.currentTimeMillis()));

                db.update(tableId, values, K_DATATABLE_ID_EQUALS_PARAM, whereArgs);
            }
            // TODO: throw exception if in the SyncState.in_conflict state?

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }

        if (shouldPhysicallyDelete) {
            File instanceFolder = new File(ODKFileUtils.getInstanceFolder(db.getAppName(), tableId, rowId));
            try {
                FileUtils.deleteDirectory(instanceFolder);
            } catch (IOException e) {
                // TODO Auto-generated catch block
                WebLogger.getLogger(db.getAppName()).e(t,
                        "Unable to delete this directory: " + instanceFolder.getAbsolutePath());
                WebLogger.getLogger(db.getAppName()).printStackTrace(e);
            }
        }
    }

    /*
      * Internal method to execute a delete checkpoint statement with the given where clause
      *
      * @param db
      * @param tableId
      * @param rowId
      * @param whereClause
      * @param whereArgs
      * @param activeUser
      * @param rolesList
      * @throws ActionNotAuthorizedException
     */
    private void rawCheckpointDeleteDataInTable(OdkConnectionInterface db, String tableId, String rowId,
            String whereClause, Object[] whereArgs, String activeUser, String rolesList)
            throws ActionNotAuthorizedException {

        boolean shouldPhysicallyDelete = false;

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            // first need to test whether we can delete all the rows that are selected
            // by the where clause. If we can't, then throw an access violation
            Cursor c = null;
            try {
                c = db.query(tableId, new String[] { DataTableColumns.SYNC_STATE, DataTableColumns.FILTER_TYPE,
                        DataTableColumns.FILTER_VALUE }, whereClause, whereArgs, null, null, null, null);
                boolean hasRow = c.moveToFirst();

                int idxSyncState = c.getColumnIndex(DataTableColumns.SYNC_STATE);
                int idxFilterType = c.getColumnIndex(DataTableColumns.FILTER_TYPE);
                int idxFilterValue = c.getColumnIndex(DataTableColumns.FILTER_VALUE);

                List<String> rolesArray = getRolesArray(rolesList);

                TableSecuritySettings tss = getTableSecuritySettings(db, tableId);

                if (hasRow) {
                    do {
                        // the row is entirely removed -- delete the attachments
                        String priorSyncState = c.getString(idxSyncState);
                        String priorFilterType = c.isNull(idxFilterType) ? null : c.getString(idxFilterType);
                        String priorFilterValue = c.isNull(idxFilterValue) ? null : c.getString(idxFilterValue);

                        tss.allowRowChange(activeUser, rolesArray, priorSyncState, priorFilterType,
                                priorFilterValue, RowChange.DELETE_ROW);
                    } while (c.moveToNext());
                }

            } finally {
                if (c != null && !c.isClosed()) {
                    c.close();
                }
                c = null;
            }

            db.delete(tableId, whereClause, whereArgs);

            // see how many rows remain.
            // If there are none, then we should delete all the attachments for this row.
            c = null;
            try {
                c = db.query(tableId, new String[] { DataTableColumns.SYNC_STATE }, K_DATATABLE_ID_EQUALS_PARAM,
                        new Object[] { rowId }, null, null, null, null);
                c.moveToFirst();
                // the row is entirely removed -- delete the attachments
                shouldPhysicallyDelete = (c.getCount() == 0);
            } finally {
                if (c != null && !c.isClosed()) {
                    c.close();
                }
            }

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }

        if (shouldPhysicallyDelete) {
            File instanceFolder = new File(ODKFileUtils.getInstanceFolder(db.getAppName(), tableId, rowId));
            try {
                FileUtils.deleteDirectory(instanceFolder);
            } catch (IOException e) {
                // TODO Auto-generated catch block
                WebLogger.getLogger(db.getAppName()).e(t,
                        "Unable to delete this directory: " + instanceFolder.getAbsolutePath());
                WebLogger.getLogger(db.getAppName()).printStackTrace(e);
            }
        }
    }

    /**
     * Delete any checkpoint rows for the given rowId in the tableId. Checkpoint
     * rows are created by ODK Survey to hold intermediate values during the
     * filling-in of the form. They act as restore points in the Survey, should
     * the application die.
     *
     * @param db
     * @param tableId
     * @param rowId
     * @param activeUser
     * @param rolesList
     * @throws ActionNotAuthorizedException
     */
    public void deleteAllCheckpointRowsWithId(OdkConnectionInterface db, String tableId, String rowId,
            String activeUser, String rolesList) throws ActionNotAuthorizedException {
        StringBuilder b = new StringBuilder();
        b.append(K_DATATABLE_ID_EQUALS_PARAM).append(S_AND).append(DataTableColumns.SAVEPOINT_TYPE)
                .append(S_IS_NULL);

        rawCheckpointDeleteDataInTable(db, tableId, rowId, b.toString(), new Object[] { rowId }, activeUser,
                rolesList);
    }

    /**
     * Delete any checkpoint rows for the given rowId in the tableId. Checkpoint
     * rows are created by ODK Survey to hold intermediate values during the
     * filling-in of the form. They act as restore points in the Survey, should
     * the application die.
     *
     * @param db
     * @param tableId
     * @param rowId
     * @param activeUser
     * @param rolesList
     * @throws ActionNotAuthorizedException
     */
    public void deleteLastCheckpointRowWithId(OdkConnectionInterface db, String tableId, String rowId,
            String activeUser, String rolesList) throws ActionNotAuthorizedException {
        StringBuilder b = new StringBuilder();
        b.append(K_DATATABLE_ID_EQUALS_PARAM).append(S_AND).append(DataTableColumns.SAVEPOINT_TYPE)
                .append(S_IS_NULL).append(S_AND).append(DataTableColumns.SAVEPOINT_TIMESTAMP)
                .append(" IN (SELECT MAX(").append(DataTableColumns.SAVEPOINT_TIMESTAMP).append(") FROM ")
                .append(tableId).append(K_WHERE).append(K_DATATABLE_ID_EQUALS_PARAM).append(")");

        rawCheckpointDeleteDataInTable(db, tableId, rowId, b.toString(), new Object[] { rowId, rowId }, activeUser,
                rolesList);
    }

    /**
     * Update all rows for the given rowId to SavepointType 'INCOMPLETE' and
     * remove all but the most recent row. When used with a rowId that has
     * checkpoints, this updates to the most recent checkpoint and removes any
     * earlier checkpoints, incomplete or complete savepoints. Otherwise, it has
     * the general effect of resetting the rowId to an INCOMPLETE state.
     *
     * @param db
     * @param tableId
     * @param rowId
     */
    public void saveAsIncompleteMostRecentCheckpointRowWithId(OdkConnectionInterface db, String tableId,
            String rowId) {

        // TODO: if user becomes unverified, we still allow them to save-as-incomplete ths record.
        // Is this the behavior we want?  I think it would be difficult to explain otherwise.

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            StringBuilder b = new StringBuilder();
            b.append("UPDATE ").append(tableId).append(" SET ").append(DataTableColumns.SAVEPOINT_TYPE)
                    .append(S_EQUALS_PARAM).append(K_WHERE).append(K_DATATABLE_ID_EQUALS_PARAM);
            db.execSQL(b.toString(), new Object[] { SavepointTypeManipulator.incomplete(), rowId });
            b.setLength(0);
            b.append(K_DATATABLE_ID_EQUALS_PARAM).append(S_AND).append(DataTableColumns.SAVEPOINT_TIMESTAMP)
                    .append(" NOT IN (SELECT MAX(").append(DataTableColumns.SAVEPOINT_TIMESTAMP).append(") FROM ")
                    .append(tableId).append(K_WHERE).append(K_DATATABLE_ID_EQUALS_PARAM).append(")");
            db.delete(tableId, b.toString(), new Object[] { rowId, rowId });

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * Update all rows for the given rowId to SavepointType 'COMPLETE' and
     * remove all but the most recent row. When used with a rowId that has
     * checkpoints, this updates to the most recent checkpoint and removes any
     * earlier checkpoints, incomplete or complete savepoints. Otherwise, it has
     * the general effect of resetting the rowId to an COMPLETE state.
     *
     * @param db
     * @param tableId
     * @param rowId
     */
    public void saveAsCompleteMostRecentCheckpointRowWithId(OdkConnectionInterface db, String tableId,
            String rowId) {

        // TODO: if user becomes unverified, we still allow them to save-as-complete ths record.
        // Is this the behavior we want?  I think it would be difficult to explain otherwise.

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            StringBuilder b = new StringBuilder();
            b.append("UPDATE ").append(tableId).append(" SET ").append(DataTableColumns.SAVEPOINT_TYPE)
                    .append(S_EQUALS_PARAM).append(K_WHERE).append(K_DATATABLE_ID_EQUALS_PARAM);
            db.execSQL(b.toString(), new Object[] { SavepointTypeManipulator.complete(), rowId });

            b.setLength(0);
            b.append(K_DATATABLE_ID_EQUALS_PARAM).append(S_AND).append(DataTableColumns.SAVEPOINT_TIMESTAMP)
                    .append(" NOT IN (SELECT MAX(").append(DataTableColumns.SAVEPOINT_TIMESTAMP).append(") FROM ")
                    .append(tableId).append(K_WHERE).append(K_DATATABLE_ID_EQUALS_PARAM).append(")");
            db.delete(tableId, b.toString(), new Object[] { rowId, rowId });

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * Update the given rowId with the values in the cvValues. If certain metadata
     * values are not specified in the cvValues, then suitable default values may
     * be supplied for them. Furthermore, if the cvValues do not specify certain
     * metadata fields, then an exception may be thrown if there are more than one
     * row matching this rowId.
     *
     * @param db
     * @param tableId
     * @param orderedColumns
     * @param cvValues
     * @param rowId
     * @param activeUser
     * @param rolesList
     * @param locale
     */
    public void updateRowWithId(OdkConnectionInterface db, String tableId, OrderedColumns orderedColumns,
            ContentValues cvValues, String rowId, String activeUser, String rolesList, String locale)
            throws ActionNotAuthorizedException {

        // TODO: make sure caller passes in the correct roleList for the use case.
        // TODO: for multi-step sync actions, we probably need an internal variant of this.

        if (cvValues.size() <= 0) {
            throw new IllegalArgumentException(t + ": No values to add into table " + tableId);
        }

        HashMap<String, Object> cvDataTableVal = new HashMap<String, Object>();
        cvDataTableVal.put(DataTableColumns.ID, rowId);
        for (String key : cvValues.keySet()) {
            cvDataTableVal.put(key, cvValues.get(key));
        }

        upsertDataIntoExistingTable(db, tableId, orderedColumns, cvDataTableVal, true, false, activeUser, rolesList,
                locale, false);
    }

    private void updateRowWithId(OdkConnectionInterface db, String tableId, OrderedColumns orderedColumns,
            Map<String, Object> cvValues, String activeUser, String rolesList, String locale)
            throws ActionNotAuthorizedException {

        // TODO: make sure caller passes in the correct roleList for the use case.
        // TODO: for multi-step sync actions, we probably need an internal variant of this.

        if (cvValues.size() <= 0) {
            throw new IllegalArgumentException(t + ": No values to add into table " + tableId);
        }

        if (!cvValues.containsKey(DataTableColumns.ID)) {
            throw new IllegalArgumentException(t + ": No rowId in cvValues map " + tableId);
        }

        upsertDataIntoExistingTable(db, tableId, orderedColumns, cvValues, true, false, activeUser, rolesList,
                locale, false);
    }

    /**
     * Update the given rowId with the values in the cvValues. All field
     * values are specified in the cvValues. This is a server-induced update
     * of the row to match all fields from the server. An error is thrown if
     * there isn't a row matching this rowId or if there are checkpoint or
     * conflict entries for this rowId.
     *
     * @param db
     * @param tableId
     * @param orderedColumns
     * @param cvValues
     * @param rowId
     * @param activeUser
     * @param locale
     * @param asCsvRequestedChange
     */
    private void privilegedUpdateRowWithId(OdkConnectionInterface db, String tableId, OrderedColumns orderedColumns,
            ContentValues cvValues, String rowId, String activeUser, String locale, boolean asCsvRequestedChange) {

        // TODO: make sure caller passes in the correct roleList for the use case.
        // TODO: for multi-step sync actions, we probably need an internal variant of this.

        String rolesList = RoleConsts.ADMIN_ROLES_LIST;

        if (cvValues.size() <= 0) {
            throw new IllegalArgumentException(t + ": No values to add into table " + tableId);
        }

        HashMap<String, Object> cvDataTableVal = new HashMap<String, Object>();
        cvDataTableVal.put(DataTableColumns.ID, rowId);
        for (String key : cvValues.keySet()) {
            cvDataTableVal.put(key, cvValues.get(key));
        }

        try {
            upsertDataIntoExistingTable(db, tableId, orderedColumns, cvDataTableVal, true, true, activeUser,
                    rolesList, locale, asCsvRequestedChange);
        } catch (ActionNotAuthorizedException e) {
            WebLogger.getLogger(db.getAppName()).printStackTrace(e);
            throw new IllegalStateException(e);
        }
    }

    /**
     * SYNC Only. ADMIN Privileges!
     * <p/>
     * Delete the local row.
     *
     * @param db
     * @param tableId
     * @param rowId
     * @param activeUser
     */
    public void privilegedDeleteRowWithId(OdkConnectionInterface db, String tableId, String rowId,
            String activeUser) {

        // TODO: make sure caller passes in the correct roleList for the use case.
        String rolesList = RoleConsts.ADMIN_ROLES_LIST;

        boolean inTransaction = false;
        try {

            inTransaction = db.inTransaction();
            if (!inTransaction) {
                db.beginTransactionNonExclusive();
            }

            // delete the record of the server row
            deleteServerConflictRowWithId(db, tableId, rowId);

            // move the local record into the 'new_row' sync state
            // so it can be physically deleted.

            if (privilegedUpdateRowETagAndSyncState(db, tableId, rowId, null, SyncState.new_row, activeUser)) {

                // delete what was the local conflict record
                deleteRowWithId(db, tableId, rowId, activeUser, rolesList);
            }

            if (!inTransaction) {
                db.setTransactionSuccessful();
            }
        } catch (ActionNotAuthorizedException e) {
            WebLogger.getLogger(db.getAppName()).printStackTrace(e);
            throw new IllegalStateException(e);
        } finally {
            if (db != null) {
                if (!inTransaction) {
                    db.endTransaction();
                }
            }
        }
    }

    /**
     * Delete the local and server conflict records to resolve a server conflict
     * <p/>
     * A combination of primitive actions, all performed in one transaction:
     * <p/>
     * // delete the record of the server row
     * deleteServerConflictRowWithId(appName, dbHandleName, tableId, rowId);
     * <p/>
     * // move the local record into the 'new_row' sync state
     * // so it can be physically deleted.
     * updateRowETagAndSyncState(appName, dbHandleName, tableId, rowId, null,
     * SyncState.new_row.name());
     * // move the local conflict back into the normal (null) state
     * deleteRowWithId(appName, dbHandleName, tableId, rowId);
     *
     * @param db
     * @param tableId
     * @param rowId
     * @param activeUser
     * @param rolesList
     * @throws ActionNotAuthorizedException
     */
    public void resolveServerConflictWithDeleteRowWithId(OdkConnectionInterface db, String tableId, String rowId,
            String activeUser, String rolesList) throws ActionNotAuthorizedException {

        // TODO: make sure caller passes in the correct roleList for the use case.

        boolean inTransaction = false;
        try {

            inTransaction = db.inTransaction();
            if (!inTransaction) {
                db.beginTransactionNonExclusive();
            }

            // delete the record of the server row
            deleteServerConflictRowWithId(db, tableId, rowId);

            // move the local record into the 'new_row' sync state
            // so it can be physically deleted.

            if (!privilegedUpdateRowETagAndSyncState(db, tableId, rowId, null, SyncState.new_row, activeUser)) {
                throw new IllegalArgumentException(
                        "row id " + rowId + " does not have exactly 1 row in table " + tableId);
            }

            // move the local conflict back into the normal (null) state
            deleteRowWithId(db, tableId, rowId, activeUser, rolesList);

            if (!inTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (db != null) {
                if (!inTransaction) {
                    db.endTransaction();
                }
            }
        }
    }

    /**
     * Resolve the server conflict by taking the local changes.
     * If the local changes are to delete this record, the record will be deleted
     * upon the next successful sync.
     *
     * @param db
     * @param tableId
     * @param rowId
     * @param activeUser
     * @param rolesList
     * @param locale
     */
    public void resolveServerConflictTakeLocalRowWithId(OdkConnectionInterface db, String tableId, String rowId,
            String activeUser, String rolesList, String locale) throws ActionNotAuthorizedException {

        // TODO: if rolesList contains RoleConsts.ROLE_ADMINISTRATOR or  RoleConsts.ROLE_SUPER_USER
        // TODO: then we should take the local rowFilterScope values. Otherwise use server values.

        // I.e., if the user is super-user or higher, we should take local FilterScope.
        // otherwise, we should take server FilterScope. Or should we allow user to select
        // which to take?

        boolean inTransaction = false;
        try {

            inTransaction = db.inTransaction();
            if (!inTransaction) {
                db.beginTransactionNonExclusive();
            }

            OrderedColumns orderedColumns = getUserDefinedColumns(db, tableId);

            AccessContext accessContext = getAccessContext(db, tableId, activeUser, RoleConsts.ADMIN_ROLES_LIST);

            // get both conflict records for this row.
            // the local record is always before the server record (due to conflict_type values)
            StringBuilder b = new StringBuilder();
            b.append(K_DATATABLE_ID_EQUALS_PARAM).append(S_AND).append(DataTableColumns.CONFLICT_TYPE)
                    .append(S_IS_NOT_NULL);
            BaseTable table = privilegedQuery(db, tableId,
                    QueryUtil.buildSqlStatement(tableId, b.toString(), null, null,
                            new String[] { DataTableColumns.CONFLICT_TYPE }, new String[] { "ASC" }),
                    new Object[] { rowId }, null, accessContext);

            if (table.getNumberOfRows() != 2) {
                throw new IllegalStateException(
                        "Did not find a server and local row when resolving conflicts for rowId: " + rowId);
            }
            Row localRow = table.getRowAtIndex(0);
            Row serverRow = table.getRowAtIndex(1);

            int localConflictType = Integer.parseInt(localRow.getDataByKey(DataTableColumns.CONFLICT_TYPE));

            int serverConflictType = Integer.parseInt(serverRow.getDataByKey(DataTableColumns.CONFLICT_TYPE));

            if (localConflictType != ConflictType.LOCAL_UPDATED_UPDATED_VALUES
                    && localConflictType != ConflictType.LOCAL_DELETED_OLD_VALUES) {
                throw new IllegalStateException(
                        "Did not find local conflict row when resolving conflicts for rowId: " + rowId);
            }

            if (serverConflictType != ConflictType.SERVER_UPDATED_UPDATED_VALUES
                    && serverConflictType != ConflictType.SERVER_DELETED_OLD_VALUES) {
                throw new IllegalStateException(
                        "Did not find server conflict row when resolving conflicts for rowId: " + rowId);
            }

            // update what was the local conflict record with the local's changes
            // by the time we apply the update, the local conflict record will be
            // restored to the proper (conflict_type, sync_state) values.
            //
            // No need to specify them here.
            TreeMap<String, Object> updateValues = new TreeMap<String, Object>();
            updateValues.put(DataTableColumns.ID, rowId);
            updateValues.put(DataTableColumns.ROW_ETAG, serverRow.getDataByKey(DataTableColumns.ROW_ETAG));

            // take the server's filter metadata values ...
            TreeMap<String, Object> privilegedUpdateValues = new TreeMap<String, Object>();
            privilegedUpdateValues.put(DataTableColumns.ID, rowId);
            privilegedUpdateValues.put(DataTableColumns.FILTER_TYPE,
                    serverRow.getDataByKey(DataTableColumns.FILTER_TYPE));
            privilegedUpdateValues.put(DataTableColumns.FILTER_VALUE,
                    serverRow.getDataByKey(DataTableColumns.FILTER_VALUE));
            privilegedUpdateValues.put(DataTableColumns.SAVEPOINT_TIMESTAMP,
                    serverRow.getDataByKey(DataTableColumns.SAVEPOINT_TIMESTAMP));
            privilegedUpdateValues.put(DataTableColumns.SAVEPOINT_CREATOR,
                    serverRow.getDataByKey(DataTableColumns.SAVEPOINT_CREATOR));

            // Figure out whether to take the server or local metadata fields.
            // and whether to take the server or local data fields.

            SyncState finalSyncState = SyncState.changed;

            if (localConflictType == ConflictType.LOCAL_UPDATED_UPDATED_VALUES) {
                // We are updating -- preserve the local metadata and column values
                // this is a no-op, as we are updating the local record, so we don't
                // need to do anything special.
            } else {
                finalSyncState = SyncState.deleted;

                // Deletion is really a "TakeServerChanges" action, but ending with 'deleted' as
                // the final sync state.

                // copy everything over from the server row
                updateValues.put(DataTableColumns.FORM_ID, serverRow.getDataByKey(DataTableColumns.FORM_ID));
                updateValues.put(DataTableColumns.LOCALE, serverRow.getDataByKey(DataTableColumns.LOCALE));
                updateValues.put(DataTableColumns.SAVEPOINT_TYPE,
                        serverRow.getDataByKey(DataTableColumns.SAVEPOINT_TYPE));
                updateValues.put(DataTableColumns.SAVEPOINT_TIMESTAMP,
                        serverRow.getDataByKey(DataTableColumns.SAVEPOINT_TIMESTAMP));
                updateValues.put(DataTableColumns.SAVEPOINT_CREATOR,
                        serverRow.getDataByKey(DataTableColumns.SAVEPOINT_CREATOR));

                // including the values of the user fields on the server
                for (String elementKey : orderedColumns.getRetentionColumnNames()) {
                    updateValues.put(elementKey, serverRow.getDataByKey(elementKey));
                }
            }

            // delete the record of the server row
            deleteServerConflictRowWithId(db, tableId, rowId);

            // move the local conflict back into the normal non-conflict (null) state
            // set the sync state to "changed" temporarily (otherwise we can't update)

            restoreRowFromConflict(db, tableId, rowId, SyncState.changed, localConflictType);

            // update local with the changes
            updateRowWithId(db, tableId, orderedColumns, updateValues, activeUser, rolesList, locale);

            // update as if user has admin privileges.
            // do this so we can update the filter type and filter value
            updateRowWithId(db, tableId, orderedColumns, privilegedUpdateValues, activeUser,
                    RoleConsts.ADMIN_ROLES_LIST, locale);

            // and if we are deleting, try to delete it.
            // this may throw an ActionNotAuthorizedException
            if (finalSyncState == SyncState.deleted) {
                deleteRowWithId(db, tableId, rowId, activeUser, rolesList);
            }

            if (!inTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (db != null) {
                if (!inTransaction) {
                    db.endTransaction();
                }
            }
        }
    }

    /**
     * Resolve the server conflict by taking the local changes plus a value map
     * of select server field values.  This map should not update any metadata
     * fields -- it should just contain user data fields.
     * <p/>
     * It is an error to call this if the local change is to delete the row.
     *
     * @param db
     * @param tableId
     * @param cvValues   key-value pairs from the server record that we should incorporate.
     * @param rowId
     * @param activeUser
     * @param rolesList
     * @param locale
     */
    public void resolveServerConflictTakeLocalRowPlusServerDeltasWithId(OdkConnectionInterface db, String tableId,
            ContentValues cvValues, String rowId, String activeUser, String rolesList, String locale)
            throws ActionNotAuthorizedException {

        // TODO: if rolesList does not contain RoleConsts.ROLE_SUPER_USER or RoleConsts.ROLE_ADMINISTRATOR
        // TODO: then take the server's rowFilterScope rather than the user's values of those.
        // TODO: and apply the update only if the user roles support that update.

        // I.e., if the user is super-user or higher, we should take local FilterScope.
        // otherwise, we should take server FilterScope. Or should we allow user to select
        // which to take?

        boolean inTransaction = false;
        try {

            inTransaction = db.inTransaction();
            if (!inTransaction) {
                db.beginTransactionNonExclusive();
            }

            OrderedColumns orderedColumns = getUserDefinedColumns(db, tableId);

            AccessContext accessContext = getAccessContext(db, tableId, activeUser, RoleConsts.ADMIN_ROLES_LIST);

            // get both conflict records for this row.
            // the local record is always before the server record (due to conflict_type values)
            BaseTable table = privilegedQuery(db, tableId,
                    QueryUtil.buildSqlStatement(tableId,
                            K_DATATABLE_ID_EQUALS_PARAM + S_AND + DataTableColumns.CONFLICT_TYPE + S_IS_NOT_NULL,
                            null, null, new String[] { DataTableColumns.CONFLICT_TYPE }, new String[] { "ASC" }),
                    new Object[] { rowId }, null, accessContext);

            if (table.getNumberOfRows() != 2) {
                throw new IllegalStateException(
                        "Did not find a server and local row when resolving conflicts for rowId: " + rowId);
            }
            Row localRow = table.getRowAtIndex(0);
            Row serverRow = table.getRowAtIndex(1);

            int localConflictType = Integer.parseInt(localRow.getDataByKey(DataTableColumns.CONFLICT_TYPE));

            int serverConflictType = Integer.parseInt(serverRow.getDataByKey(DataTableColumns.CONFLICT_TYPE));

            if (localConflictType != ConflictType.LOCAL_UPDATED_UPDATED_VALUES
                    && localConflictType != ConflictType.LOCAL_DELETED_OLD_VALUES) {
                throw new IllegalStateException(
                        "Did not find local conflict row when resolving conflicts for rowId: " + rowId);
            }

            if (serverConflictType != ConflictType.SERVER_UPDATED_UPDATED_VALUES
                    && serverConflictType != ConflictType.SERVER_DELETED_OLD_VALUES) {
                throw new IllegalStateException(
                        "Did not find server conflict row when resolving conflicts for rowId: " + rowId);
            }

            if (localConflictType == ConflictType.LOCAL_DELETED_OLD_VALUES) {
                throw new IllegalStateException(
                        "Local row is marked for deletion -- blending does not make sense for rowId: " + rowId);
            }

            HashMap<String, Object> updateValues = new HashMap<String, Object>();
            for (String key : cvValues.keySet()) {
                updateValues.put(key, cvValues.get(key));
            }

            // clean up the incoming map of server values to retain
            cleanUpValuesMap(orderedColumns, updateValues);
            updateValues.put(DataTableColumns.ID, rowId);
            updateValues.put(DataTableColumns.ROW_ETAG, serverRow.getDataByKey(DataTableColumns.ROW_ETAG));

            // update what was the local conflict record with the local's changes
            // by the time we apply the update, the local conflict record will be
            // restored to the proper (conflict_type, sync_state) values.
            //
            // No need to specify them here.

            // but take the local's metadata values (i.e., do not change these
            // during the update) ...
            updateValues.put(DataTableColumns.FORM_ID, localRow.getDataByKey(DataTableColumns.FORM_ID));
            updateValues.put(DataTableColumns.LOCALE, localRow.getDataByKey(DataTableColumns.LOCALE));
            updateValues.put(DataTableColumns.SAVEPOINT_TYPE,
                    localRow.getDataByKey(DataTableColumns.SAVEPOINT_TYPE));
            updateValues.put(DataTableColumns.SAVEPOINT_TIMESTAMP,
                    localRow.getDataByKey(DataTableColumns.SAVEPOINT_TIMESTAMP));
            updateValues.put(DataTableColumns.SAVEPOINT_CREATOR,
                    localRow.getDataByKey(DataTableColumns.SAVEPOINT_CREATOR));

            // take the server's filter metadata values ...
            TreeMap<String, Object> privilegedUpdateValues = new TreeMap<String, Object>();
            privilegedUpdateValues.put(DataTableColumns.ID, rowId);
            privilegedUpdateValues.put(DataTableColumns.FILTER_TYPE,
                    serverRow.getDataByKey(DataTableColumns.FILTER_TYPE));
            privilegedUpdateValues.put(DataTableColumns.FILTER_VALUE,
                    serverRow.getDataByKey(DataTableColumns.FILTER_VALUE));
            privilegedUpdateValues.put(DataTableColumns.SAVEPOINT_TIMESTAMP,
                    serverRow.getDataByKey(DataTableColumns.SAVEPOINT_TIMESTAMP));
            privilegedUpdateValues.put(DataTableColumns.SAVEPOINT_CREATOR,
                    serverRow.getDataByKey(DataTableColumns.SAVEPOINT_CREATOR));

            // delete the record of the server row
            deleteServerConflictRowWithId(db, tableId, rowId);

            // move the local conflict back into the normal (null) state

            restoreRowFromConflict(db, tableId, rowId, SyncState.changed, localConflictType);

            // update local with server's changes
            updateRowWithId(db, tableId, orderedColumns, updateValues, activeUser, rolesList, locale);

            // update as if user has admin privileges.
            // do this so we can update the filter type and filter value
            updateRowWithId(db, tableId, orderedColumns, privilegedUpdateValues, activeUser,
                    RoleConsts.ADMIN_ROLES_LIST, locale);

            if (!inTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (db != null) {
                if (!inTransaction) {
                    db.endTransaction();
                }
            }
        }
    }

    /**
     * Resolve the server conflict by taking the server changes.  This may delete the local row.
     *
     * @param db
     * @param tableId
     * @param rowId
     * @param activeUser
     * @param locale
     */
    public void resolveServerConflictTakeServerRowWithId(OdkConnectionInterface db, String tableId, String rowId,
            String activeUser, String locale) {

        String rolesList = RoleConsts.ADMIN_ROLES_LIST;
        // TODO: incoming rolesList should be the privileged user roles because we are
        // TODO: overwriting our local row with everything from the server.

        // we have no way in the resolve conflicts screen to choose which filter scope
        // to take. Need to allow super-user and above to choose the local filter scope
        // vs just taking what the server has.

        boolean inTransaction = false;
        try {

            inTransaction = db.inTransaction();
            if (!inTransaction) {
                db.beginTransactionNonExclusive();
            }

            OrderedColumns orderedColumns = getUserDefinedColumns(db, tableId);

            AccessContext accessContext = getAccessContext(db, tableId, activeUser, RoleConsts.ADMIN_ROLES_LIST);

            // get both conflict records for this row.
            // the local record is always before the server record (due to conflict_type values)
            StringBuilder b = new StringBuilder();
            b.append(K_DATATABLE_ID_EQUALS_PARAM).append(S_AND).append(DataTableColumns.CONFLICT_TYPE)
                    .append(S_IS_NOT_NULL);
            BaseTable table = privilegedQuery(db, tableId,
                    QueryUtil.buildSqlStatement(tableId, b.toString(), null, null,
                            new String[] { DataTableColumns.CONFLICT_TYPE }, new String[] { "ASC" }),
                    new Object[] { rowId }, null, accessContext);

            if (table.getNumberOfRows() != 2) {
                throw new IllegalStateException(
                        "Did not find a server and local row when resolving conflicts for rowId: " + rowId);
            }
            Row localRow = table.getRowAtIndex(0);
            Row serverRow = table.getRowAtIndex(1);

            int localConflictType = Integer.parseInt(localRow.getDataByKey(DataTableColumns.CONFLICT_TYPE));

            int serverConflictType = Integer.parseInt(serverRow.getDataByKey(DataTableColumns.CONFLICT_TYPE));

            if (localConflictType != ConflictType.LOCAL_UPDATED_UPDATED_VALUES
                    && localConflictType != ConflictType.LOCAL_DELETED_OLD_VALUES) {
                throw new IllegalStateException(
                        "Did not find local conflict row when resolving conflicts for rowId: " + rowId);
            }

            if (serverConflictType != ConflictType.SERVER_UPDATED_UPDATED_VALUES
                    && serverConflictType != ConflictType.SERVER_DELETED_OLD_VALUES) {
                throw new IllegalStateException(
                        "Did not find server conflict row when resolving conflicts for rowId: " + rowId);
            }

            if (serverConflictType == ConflictType.SERVER_DELETED_OLD_VALUES) {

                // delete the record of the server row
                deleteServerConflictRowWithId(db, tableId, rowId);

                // move the local record into the 'new_row' sync state
                // so it can be physically deleted.

                if (!privilegedUpdateRowETagAndSyncState(db, tableId, rowId, null, SyncState.new_row, activeUser)) {
                    throw new IllegalArgumentException(
                            "row id " + rowId + " does not have exactly 1 row in table " + tableId);
                }

                // and delete the local conflict and all of its associated attachments
                deleteRowWithId(db, tableId, rowId, activeUser, rolesList);

            } else {
                // update the local conflict record with the server's changes
                HashMap<String, Object> updateValues = new HashMap<String, Object>();
                updateValues.put(DataTableColumns.ID, rowId);
                updateValues.put(DataTableColumns.ROW_ETAG, serverRow.getDataByKey(DataTableColumns.ROW_ETAG));

                // update what was the local conflict record with the server's changes
                // by the time we apply the update, the local conflict record will be
                // restored to the proper (conflict_type, sync_state) values.
                //
                // No need to specify them here.

                // take the server's metadata values too...
                updateValues.put(DataTableColumns.FILTER_TYPE,
                        serverRow.getDataByKey(DataTableColumns.FILTER_TYPE));
                updateValues.put(DataTableColumns.FILTER_VALUE,
                        serverRow.getDataByKey(DataTableColumns.FILTER_VALUE));
                updateValues.put(DataTableColumns.FORM_ID, serverRow.getDataByKey(DataTableColumns.FORM_ID));
                updateValues.put(DataTableColumns.LOCALE, serverRow.getDataByKey(DataTableColumns.LOCALE));
                updateValues.put(DataTableColumns.SAVEPOINT_TYPE,
                        serverRow.getDataByKey(DataTableColumns.SAVEPOINT_TYPE));
                updateValues.put(DataTableColumns.SAVEPOINT_TIMESTAMP,
                        serverRow.getDataByKey(DataTableColumns.SAVEPOINT_TIMESTAMP));
                updateValues.put(DataTableColumns.SAVEPOINT_CREATOR,
                        serverRow.getDataByKey(DataTableColumns.SAVEPOINT_CREATOR));

                // take all the data values from the server...
                for (String elementKey : orderedColumns.getRetentionColumnNames()) {
                    updateValues.put(elementKey, serverRow.getDataByKey(elementKey));
                }

                // determine whether we should flag this as pending files
                // or whether it can transition directly to sync'd.
                SyncState newState;
                {
                    boolean hasUriFragments = false;
                    // we are collapsing to the server state. Examine the
                    // server row. Look at all the columns that may contain file
                    // attachments. If they do (non-null, non-empty), then
                    // set the hasUriFragments flag to true and break out of the loop.
                    //
                    // Set the resolved row to synced_pending_files if there are
                    // non-null, non-empty file attachments in the row. This
                    // ensures that we will pull down those attachments at the next
                    // sync.
                    for (ColumnDefinition cd : orderedColumns.getColumnDefinitions()) {
                        if (cd.getType().getDataType() != ElementDataType.rowpath) {
                            // not a file attachment
                            continue;
                        }
                        String v = serverRow.getDataByKey(cd.getElementKey());
                        if (v != null && v.length() != 0) {
                            // non-null file attachment specified on server row
                            hasUriFragments = true;
                            break;
                        }
                    }
                    newState = hasUriFragments ? SyncState.synced_pending_files : SyncState.synced;
                }

                // delete the record of the server row
                deleteServerConflictRowWithId(db, tableId, rowId);

                // move the local conflict back into either the synced or synced_pending_files
                // state

                restoreRowFromConflict(db, tableId, rowId, newState, localConflictType);

                // update local with server's changes

                updateRowWithId(db, tableId, orderedColumns, updateValues, activeUser, rolesList, locale);

                // and reset the sync state to whatever it should be (update will make it changed)
                restoreRowFromConflict(db, tableId, rowId, newState, null);
            }

            if (!inTransaction) {
                db.setTransactionSuccessful();
            }
        } catch (ActionNotAuthorizedException e) {
            WebLogger.getLogger(db.getAppName()).printStackTrace(e);
            throw new IllegalStateException(e);
        } finally {
            if (db != null) {
                if (!inTransaction) {
                    db.endTransaction();
                }
            }
        }
    }

    /**
     * Inserts a checkpoint row for the given rowId in the tableId. Checkpoint
     * rows are created by ODK Survey to hold intermediate values during the
     * filling-in of the form. They act as restore points in the Survey, should
     * the application die.
     *
     * @param db
     * @param tableId
     * @param orderedColumns
     * @param cvValues
     * @param rowId
     * @param activeUser
     * @param rolesList
     * @param locale
     */
    public void insertCheckpointRowWithId(OdkConnectionInterface db, String tableId, OrderedColumns orderedColumns,
            ContentValues cvValues, String rowId, String activeUser, String rolesList, String locale)
            throws ActionNotAuthorizedException {

        if (cvValues.size() <= 0) {
            throw new IllegalArgumentException(t + ": No values to add into table for checkpoint" + tableId);
        }

        // these are all managed in the database layer...
        // the user should NOT set them...

        if (cvValues.containsKey(DataTableColumns.SAVEPOINT_TIMESTAMP)) {
            throw new IllegalArgumentException(
                    t + ": No user supplied savepoint timestamp can be included for a checkpoint");
        }

        if (cvValues.containsKey(DataTableColumns.SAVEPOINT_TYPE)) {
            throw new IllegalArgumentException(
                    t + ": No user supplied savepoint type can be included for a checkpoint");
        }

        if (cvValues.containsKey(DataTableColumns.ROW_ETAG)) {
            throw new IllegalArgumentException(t + ": No user supplied row ETag can be included for a checkpoint");
        }

        if (cvValues.containsKey(DataTableColumns.SYNC_STATE)) {
            throw new IllegalArgumentException(
                    t + ": No user supplied sync state can be included for a checkpoint");
        }

        if (cvValues.containsKey(DataTableColumns.CONFLICT_TYPE)) {
            throw new IllegalArgumentException(
                    t + ": No user supplied conflict type can be included for a checkpoint");
        }

        if (cvValues.containsKey(DataTableColumns.FILTER_VALUE)) {
            throw new IllegalArgumentException(
                    t + ": No user supplied filter value can be included for a checkpoint");
        }

        if (cvValues.containsKey(DataTableColumns.FILTER_TYPE)) {
            throw new IllegalArgumentException(
                    t + ": No user supplied filter type can be included for a checkpoint");
        }

        // If a rowId is specified, a cursor will be needed to
        // get the current row to create a checkpoint with the relevant data
        Cursor c = null;
        try {
            // Allow the user to pass in no rowId if this is the first
            // checkpoint row that the user is adding
            if (rowId == null) {

                // TODO: is this even valid any more? I think we disallow this in the AIDL flow.

                String rowIdToUse = LocalizationUtils.genUUID();
                HashMap<String, Object> currValues = new HashMap<String, Object>();
                for (String key : cvValues.keySet()) {
                    currValues.put(key, cvValues.get(key));
                }
                currValues.put(DataTableColumns._ID, rowIdToUse);
                currValues.put(DataTableColumns.SYNC_STATE, SyncState.new_row.name());
                insertCheckpointIntoExistingTable(db, tableId, orderedColumns, currValues, activeUser, rolesList,
                        locale, true, null, null);
                return;
            }

            StringBuilder b = new StringBuilder();
            b.append(K_DATATABLE_ID_EQUALS_PARAM).append(S_AND).append(DataTableColumns.SAVEPOINT_TIMESTAMP)
                    .append(" IN (SELECT MAX(").append(DataTableColumns.SAVEPOINT_TIMESTAMP).append(") FROM ")
                    .append(tableId).append(K_WHERE).append(K_DATATABLE_ID_EQUALS_PARAM).append(")");
            c = db.query(tableId, null, b.toString(), new Object[] { rowId, rowId }, null, null, null, null);
            c.moveToFirst();

            if (c.getCount() > 1) {
                throw new IllegalStateException(t + ": More than one checkpoint at a timestamp");
            }

            // Inserting a checkpoint for the first time
            if (c.getCount() <= 0) {
                HashMap<String, Object> currValues = new HashMap<String, Object>();
                for (String key : cvValues.keySet()) {
                    currValues.put(key, cvValues.get(key));
                }
                currValues.put(DataTableColumns._ID, rowId);
                currValues.put(DataTableColumns.SYNC_STATE, SyncState.new_row.name());
                insertCheckpointIntoExistingTable(db, tableId, orderedColumns, currValues, activeUser, rolesList,
                        locale, true, null, null);
                return;
            } else {
                // Make sure that the conflict_type of any existing row
                // is null, otherwise throw an exception
                int conflictIndex = c.getColumnIndex(DataTableColumns.CONFLICT_TYPE);
                if (!c.isNull(conflictIndex)) {
                    throw new IllegalStateException(
                            t + ":  A checkpoint cannot be added for a row that is in conflict");
                }

                HashMap<String, Object> currValues = new HashMap<String, Object>();
                for (String key : cvValues.keySet()) {
                    currValues.put(key, cvValues.get(key));
                }

                // This is unnecessary
                // We should only have one row at this point
                //c.moveToFirst();

                String priorFilterType = null;
                String priorFilterValue = null;

                // Get the number of columns to iterate over and add
                // those values to the content values
                for (int i = 0; i < c.getColumnCount(); i++) {
                    String name = c.getColumnName(i);

                    if (currValues.containsKey(name)) {
                        continue;
                    }

                    // omitting savepoint timestamp will generate a new timestamp.
                    if (name.equals(DataTableColumns.SAVEPOINT_TIMESTAMP)) {
                        continue;
                    }

                    // set savepoint type to null to mark this as a checkpoint
                    if (name.equals(DataTableColumns.SAVEPOINT_TYPE)) {
                        currValues.put(name, null);
                        continue;
                    }

                    // sync state (a non-null field) should either remain 'new_row'
                    // or be set to 'changed' for all other existing values.
                    if (name.equals(DataTableColumns.SYNC_STATE)) {
                        String priorState = c.getString(i);
                        if (priorState.equals(SyncState.new_row.name())) {
                            currValues.put(name, SyncState.new_row.name());
                        } else {
                            currValues.put(name, SyncState.changed.name());
                        }
                        continue;
                    }

                    if (c.isNull(i)) {
                        currValues.put(name, null);
                        continue;
                    }

                    // otherwise, just copy the values over...
                    Class<?> theClass = CursorUtils.getIndexDataType(c, i);
                    Object object = CursorUtils.getIndexAsType(c, theClass, i);
                    insertValueIntoContentValues(currValues, theClass, name, object);

                    if (name.equals(DataTableColumns.FILTER_TYPE)) {
                        priorFilterType = c.getString(i);
                    }

                    if (name.equals(DataTableColumns.FILTER_VALUE)) {
                        priorFilterValue = c.getString(i);
                    }
                }

                insertCheckpointIntoExistingTable(db, tableId, orderedColumns, currValues, activeUser, rolesList,
                        locale, false, priorFilterType, priorFilterValue);
            }
        } finally {
            if (c != null && !c.isClosed()) {
                c.close();
            }
        }
    }

    private void insertValueIntoContentValues(Map<String, Object> cv, Class<?> theClass, String name, Object obj) {

        if (obj == null) {
            cv.put(name, null);
            return;
        }

        // Couldn't use the CursorUtils.getIndexAsType
        // because assigning the result to Object v
        // would not work for the currValues.put function
        if (theClass == Long.class) {
            cv.put(name, (Long) obj);
        } else if (theClass == Integer.class) {
            cv.put(name, (Integer) obj);
        } else if (theClass == Double.class) {
            cv.put(name, (Double) obj);
        } else if (theClass == String.class) {
            cv.put(name, (String) obj);
        } else if (theClass == Boolean.class) {
            // stored as integers
            Integer v = (Integer) obj;
            cv.put(name, Boolean.valueOf(v != 0));
        } else if (theClass == ArrayList.class) {
            cv.put(name, (String) obj);
        } else if (theClass == HashMap.class) {
            // json deserialization of an object
            cv.put(name, (String) obj);
        } else {
            throw new IllegalStateException("Unexpected data type in SQLite table " + theClass.toString());
        }
    }

    /**
     * Insert the given rowId with the values in the cvValues. All metadata field
     * values must be specified in the cvValues. This is called from Sync for inserting
     * a row verbatim from the server.
     * <p/>
     * If a row with this rowId is present, then an exception is thrown.
     *
     * @param db
     * @param tableId
     * @param orderedColumns
     * @param cvValues
     * @param rowId
     * @param activeUser
     * @param locale
     * @param asCsvRequestedChange
     */
    public void privilegedInsertRowWithId(OdkConnectionInterface db, String tableId, OrderedColumns orderedColumns,
            ContentValues cvValues, String rowId, String activeUser, String locale, boolean asCsvRequestedChange) {

        String rolesList = RoleConsts.ADMIN_ROLES_LIST;

        if (cvValues == null || cvValues.size() <= 0) {
            throw new IllegalArgumentException(t + ": No values to add into table " + tableId);
        }

        HashMap<String, Object> cvDataTableVal = new HashMap<String, Object>();
        cvDataTableVal.put(DataTableColumns.ID, rowId);
        for (String key : cvValues.keySet()) {
            cvDataTableVal.put(key, cvValues.get(key));
        }

        // TODO: verify that all fields are specified
        try {
            upsertDataIntoExistingTable(db, tableId, orderedColumns, cvDataTableVal, false, true, activeUser,
                    rolesList, locale, asCsvRequestedChange);
        } catch (ActionNotAuthorizedException e) {
            WebLogger.getLogger(db.getAppName()).printStackTrace(e);
            throw new IllegalStateException(e);
        }
    }

    /**
     * Insert the given rowId with the values in the cvValues. If certain metadata
     * values are not specified in the cvValues, then suitable default values may
     * be supplied for them.
     * <p/>
     * If a row with this rowId and certain matching metadata fields is present,
     * then an exception is thrown.
     *
     * @param db
     * @param tableId
     * @param orderedColumns
     * @param cvValues
     * @param rowId
     * @param activeUser
     * @param rolesList
     * @param locale
     */
    public void insertRowWithId(OdkConnectionInterface db, String tableId, OrderedColumns orderedColumns,
            ContentValues cvValues, String rowId, String activeUser, String rolesList, String locale)
            throws ActionNotAuthorizedException {

        if (cvValues == null || cvValues.size() <= 0) {
            throw new IllegalArgumentException(t + ": No values to add into table " + tableId);
        }

        HashMap<String, Object> cvDataTableVal = new HashMap<String, Object>();
        cvDataTableVal.put(DataTableColumns.ID, rowId);
        for (String key : cvValues.keySet()) {
            cvDataTableVal.put(key, cvValues.get(key));
        }

        upsertDataIntoExistingTable(db, tableId, orderedColumns, cvDataTableVal, false, false, activeUser,
                rolesList, locale, false);
    }

    /**
     * Write checkpoint into the database
     *
     * @param db
     * @param tableId
     * @param orderedColumns
     * @param cvValues
     * @param activeUser
     * @param rolesList
     * @param locale
     * @param isNewRow
     */
    private void insertCheckpointIntoExistingTable(OdkConnectionInterface db, String tableId,
            OrderedColumns orderedColumns, HashMap<String, Object> cvValues, String activeUser, String rolesList,
            String locale, boolean isNewRow, String priorFilterType, String priorFilterValue)
            throws ActionNotAuthorizedException {
        String rowId = null;

        if (cvValues.size() <= 0) {
            throw new IllegalArgumentException(t + ": No values to add into table " + tableId);
        }

        HashMap<String, Object> cvDataTableVal = new HashMap<String, Object>();
        cvDataTableVal.putAll(cvValues);

        if (cvDataTableVal.containsKey(DataTableColumns.ID)) {

            rowId = (String) cvDataTableVal.get(DataTableColumns.ID);
            if (rowId == null) {
                throw new IllegalArgumentException(DataTableColumns.ID + ", if specified, cannot be null");
            }
        } else {
            throw new IllegalArgumentException(
                    t + ": rowId should not be null in insertCheckpointIntoExistingTable in the ContentValues");
        }

        if (!cvDataTableVal.containsKey(DataTableColumns.ROW_ETAG)
                || cvDataTableVal.get(DataTableColumns.ROW_ETAG) == null) {
            cvDataTableVal.put(DataTableColumns.ROW_ETAG, DataTableColumns.DEFAULT_ROW_ETAG);
        }

        if (!cvDataTableVal.containsKey(DataTableColumns.CONFLICT_TYPE)) {
            cvDataTableVal.put(DataTableColumns.CONFLICT_TYPE, null);
        }

        if (!cvDataTableVal.containsKey(DataTableColumns.FORM_ID)) {
            cvDataTableVal.put(DataTableColumns.FORM_ID, null);
        }

        if (!cvDataTableVal.containsKey(DataTableColumns.LOCALE)
                || (cvDataTableVal.get(DataTableColumns.LOCALE) == null)) {
            cvDataTableVal.put(DataTableColumns.LOCALE, locale);
        }

        if (!cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_TYPE)
                || (cvDataTableVal.get(DataTableColumns.SAVEPOINT_TYPE) == null)) {
            cvDataTableVal.put(DataTableColumns.SAVEPOINT_TYPE, null);
        }

        if (!cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_TIMESTAMP)
                || cvDataTableVal.get(DataTableColumns.SAVEPOINT_TIMESTAMP) == null) {
            String timeStamp = TableConstants.nanoSecondsFromMillis(System.currentTimeMillis());
            cvDataTableVal.put(DataTableColumns.SAVEPOINT_TIMESTAMP, timeStamp);
        }

        if (!cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_CREATOR)
                || (cvDataTableVal.get(DataTableColumns.SAVEPOINT_CREATOR) == null)) {
            cvDataTableVal.put(DataTableColumns.SAVEPOINT_CREATOR, activeUser);
        }

        cleanUpValuesMap(orderedColumns, cvDataTableVal);

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            List<String> rolesArray = getRolesArray(rolesList);

            // get the security settings
            TableSecuritySettings tss = getTableSecuritySettings(db, tableId);

            if (isNewRow) {

                // ensure that filter type and value are defined. Use defaults if not.

                if (!cvDataTableVal.containsKey(DataTableColumns.FILTER_TYPE)
                        || (cvDataTableVal.get(DataTableColumns.FILTER_TYPE) == null)) {
                    cvDataTableVal.put(DataTableColumns.FILTER_TYPE, tss.filterTypeOnCreation);
                }

                if (!cvDataTableVal.containsKey(DataTableColumns.FILTER_VALUE)) {
                    cvDataTableVal.put(DataTableColumns.FILTER_VALUE, activeUser);
                }

                cvDataTableVal.put(DataTableColumns.SYNC_STATE, SyncState.new_row.name());

                tss.allowRowChange(activeUser, rolesArray, SyncState.new_row.name(), priorFilterType,
                        priorFilterValue, RowChange.NEW_ROW);

            } else {

                // don't allow changes to filter type or value or syncState when inserting checkpoints
                cvDataTableVal.put(DataTableColumns.FILTER_TYPE, priorFilterType);

                cvDataTableVal.put(DataTableColumns.FILTER_VALUE, priorFilterValue);

                // for this call path, syncState is already updated by caller

                tss.allowRowChange(activeUser, rolesArray, (String) cvDataTableVal.get(DataTableColumns.SYNC_STATE),
                        priorFilterType, priorFilterValue, RowChange.CHANGE_ROW);
            }

            db.insertOrThrow(tableId, null, cvDataTableVal);

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }

    }

    private enum RowChange {
        NEW_ROW, CHANGE_ROW, DELETE_ROW
    }

    private class TableSecuritySettings {
        final String tableId;
        final boolean isLocked;
        final boolean canUnverifiedUserCreateRow;
        final String filterTypeOnCreation;

        public TableSecuritySettings(final String tableId, final boolean isLocked,
                final boolean canUnverifiedUserCreateRow, final String filterTypeOnCreation) {
            this.tableId = tableId;
            this.isLocked = isLocked;
            this.canUnverifiedUserCreateRow = canUnverifiedUserCreateRow;
            this.filterTypeOnCreation = filterTypeOnCreation;
        }

        public void canModifyFilterTypeAndValue(List<String> rolesArray) throws ActionNotAuthorizedException {

            if (rolesArray == null) {
                // unverified user

                // throw an exception
                throw new ActionNotAuthorizedException(
                        t + ": unverified users cannot modify filterType or filterValue fields in (any) table "
                                + tableId);

            } else if (!(rolesArray.contains(RoleConsts.ROLE_SUPER_USER)
                    || rolesArray.contains(RoleConsts.ROLE_ADMINISTRATOR))) {
                // not (super-user or administrator)

                // throw an exception
                throw new ActionNotAuthorizedException(t
                        + ": user does not have the privileges (super-user or administrator) to modify filterType or filterValue fields in table "
                        + tableId);
            }
        }

        public void allowRowChange(String activeUser, List<String> rolesArray, String updatedSyncState,
                String priorFilterType, String priorFilterValue, RowChange rowChange)
                throws ActionNotAuthorizedException {

            switch (rowChange) {
            case NEW_ROW:

                // enforce restrictions:
                // 1. if locked, only super-user and administrator can create rows.
                // 2. otherwise, if unverified user, allow creation based upon unverifedUserCanCreate flag
                if (isLocked) {
                    // inserting into a LOCKED table

                    if (rolesArray == null) {
                        // unverified user

                        // throw an exception
                        throw new ActionNotAuthorizedException(
                                t + ": unverified users cannot create a rows in a locked table " + tableId);
                    }

                    if (!(rolesArray.contains(RoleConsts.ROLE_SUPER_USER)
                            || rolesArray.contains(RoleConsts.ROLE_ADMINISTRATOR))) {
                        // bad JSON
                        // not a super-user and not an administrator

                        // throw an exception
                        throw new ActionNotAuthorizedException(t
                                + ": user does not have the privileges (super-user or administrator) to create a row in a locked table "
                                + tableId);
                    }

                } else if (rolesArray == null) {
                    // inserting into an UNLOCKED table

                    // unverified user
                    if (!canUnverifiedUserCreateRow) {

                        // throw an exception
                        throw new ActionNotAuthorizedException(t
                                + ": unverified users do not have the privileges to create a row in this unlocked table "
                                + tableId);
                    }
                }
                break;
            case CHANGE_ROW:

                // if SyncState is new_row then allow edits in both locked and unlocked tables
                if (!updatedSyncState.equals(SyncState.new_row.name())) {

                    if (isLocked) {
                        // modifying a LOCKED table

                        // disallow edits if:
                        // 1. user is unverified
                        // 2. existing filterValue is null or does not match the activeUser AND
                        //    the activeUser is neither a super-user nor an administrator.

                        if (rolesArray == null || rolesArray.isEmpty()) {
                            // unverified user

                            // throw an exception
                            throw new ActionNotAuthorizedException(
                                    t + ": unverified users cannot modify rows in a locked table " + tableId);
                        }

                        // allow if prior filterValue matches activeUser
                        if (!(priorFilterValue != null && activeUser.equals(priorFilterValue))) {
                            // otherwise...
                            // reject if the activeUser is not a super-user or administrator

                            if (rolesArray == null || !(rolesArray.contains(RoleConsts.ROLE_SUPER_USER)
                                    || rolesArray.contains(RoleConsts.ROLE_ADMINISTRATOR))) {
                                // bad JSON or
                                // not a super-user and not an administrator

                                // throw an exception
                                throw new ActionNotAuthorizedException(t
                                        + ": user does not have the privileges (super-user or administrator) to modify rows in a locked table "
                                        + tableId);
                            }
                        }
                    } else {
                        // modifying an UNLOCKED table

                        // allow if filterType is MODIFY or DEFAULT
                        if (priorFilterType == null || !(priorFilterType.equals(RowFilterScope.Type.MODIFY.name())
                                || priorFilterType.equals(RowFilterScope.Type.DEFAULT.name()))) {
                            // otherwise...

                            // allow if prior filterValue matches activeUser
                            if (priorFilterValue == null || !activeUser.equals(priorFilterValue)) {
                                // otherwise...
                                // reject if the activeUser is not a super-user or administrator

                                if (rolesArray == null || !(rolesArray.contains(RoleConsts.ROLE_SUPER_USER)
                                        || rolesArray.contains(RoleConsts.ROLE_ADMINISTRATOR))) {
                                    // bad JSON or
                                    // not a super-user and not an administrator

                                    // throw an exception
                                    throw new ActionNotAuthorizedException(t
                                            + ": user does not have the privileges (super-user or administrator) to modify hidden or read-only rows in an unlocked table "
                                            + tableId);
                                }
                            }
                        }
                    }
                }
                break;
            case DELETE_ROW:

                // if SyncState is new_row then allow deletes in both locked and unlocked tables
                if (!updatedSyncState.equals(SyncState.new_row.name())) {

                    if (isLocked) {
                        // deleting a LOCKED table

                        // disallow deletes if:
                        // 1. user is unverified
                        // 2. user is not a super-user or an administrator

                        if (rolesArray == null) {
                            // unverified user

                            // throw an exception
                            throw new ActionNotAuthorizedException(
                                    t + ": unverified users cannot delete rows in a locked table " + tableId);
                        }

                        // reject if the activeUser is not a super-user or administrator

                        if (rolesArray == null || !(rolesArray.contains(RoleConsts.ROLE_SUPER_USER)
                                || rolesArray.contains(RoleConsts.ROLE_ADMINISTRATOR))) {
                            // bad JSON or
                            // not a super-user and not an administrator

                            // throw an exception
                            throw new ActionNotAuthorizedException(t
                                    + ": user does not have the privileges (super-user or administrator) to delete rows in a locked table "
                                    + tableId);
                        }
                    } else {
                        // delete in an UNLOCKED table

                        // allow if filterType is DEFAULT
                        if (priorFilterType == null
                                || !(priorFilterType.equals(RowFilterScope.Type.DEFAULT.name()))) {
                            // otherwise...

                            // allow if prior filterValue matches activeUser
                            if (priorFilterValue == null || !activeUser.equals(priorFilterValue)) {
                                // otherwise...
                                // reject if the activeUser is not a super-user or administrator

                                if (rolesArray == null || !(rolesArray.contains(RoleConsts.ROLE_SUPER_USER)
                                        || rolesArray.contains(RoleConsts.ROLE_ADMINISTRATOR))) {
                                    // bad JSON or
                                    // not a super-user and not an administrator

                                    // throw an exception
                                    throw new ActionNotAuthorizedException(t
                                            + ": user does not have the privileges (super-user or administrator) to delete hidden or read-only rows in an unlocked table "
                                            + tableId);
                                }
                            }
                        }
                    }
                }
                break;
            }
        }
    }

    /**
     * Get the table's security settings.
     *
     * @param db
     * @param tableId
     * @return
     */
    private TableSecuritySettings getTableSecuritySettings(OdkConnectionInterface db, String tableId) {

        // get the security settings
        List<KeyValueStoreEntry> entries = getTableMetadata(db, tableId, KeyValueStoreConstants.PARTITION_TABLE,
                LocalKeyValueStoreConstants.TableSecurity.ASPECT, null).getEntries();

        KeyValueStoreEntry locked = null;
        KeyValueStoreEntry filterTypeOnCreation = null;
        KeyValueStoreEntry unverifiedUserCanCreate = null;
        for (KeyValueStoreEntry entry : entries) {
            if (entry.key.equals(LocalKeyValueStoreConstants.TableSecurity.KEY_FILTER_TYPE_ON_CREATION)) {
                filterTypeOnCreation = entry;
            } else if (entry.key.equals(LocalKeyValueStoreConstants.TableSecurity.KEY_UNVERIFIED_USER_CAN_CREATE)) {
                unverifiedUserCanCreate = entry;
            } else if (entry.key.equals(LocalKeyValueStoreConstants.TableSecurity.KEY_LOCKED)) {
                locked = entry;
            }
        }

        Boolean isLocked = (locked != null) ? KeyValueStoreUtils.getBoolean(db.getAppName(), locked) : null;
        if (isLocked == null) {
            isLocked = false;
        }

        Boolean canUnverifiedUserCreateRow = (unverifiedUserCanCreate != null)
                ? KeyValueStoreUtils.getBoolean(db.getAppName(), unverifiedUserCanCreate)
                : null;
        if (canUnverifiedUserCreateRow == null) {
            canUnverifiedUserCreateRow = true;
        }

        String filterType = (filterTypeOnCreation != null) ? filterTypeOnCreation.value : null;
        if (filterType == null) {
            filterType = DataTableColumns.DEFAULT_FILTER_TYPE;
        }

        return new TableSecuritySettings(tableId, isLocked, canUnverifiedUserCreateRow, filterType);
    }

    /*
     * Write data into a user defined database table
     *
     * TODO: This is broken w.r.t. updates of partial fields
     */
    private void upsertDataIntoExistingTable(OdkConnectionInterface db, String tableId,
            OrderedColumns orderedColumns, Map<String, Object> cvValues, boolean shouldUpdate,
            boolean asServerRequestedChange, String activeUser, String rolesList, String locale,
            boolean asCsvRequestedChange) throws ActionNotAuthorizedException {

        String rowId = null;
        String whereClause = null;
        boolean specifiesConflictType = cvValues.containsKey(DataTableColumns.CONFLICT_TYPE);
        boolean nullConflictType = specifiesConflictType && (cvValues.get(DataTableColumns.CONFLICT_TYPE) == null);
        Object[] whereArgs = new Object[specifiesConflictType ? (1 + (nullConflictType ? 0 : 1)) : 1];
        boolean update = false;
        String updatedSyncState = SyncState.new_row.name();
        String priorFilterType = DataTableColumns.DEFAULT_FILTER_TYPE;
        String priorFilterValue = null;

        if (cvValues.size() <= 0) {
            throw new IllegalArgumentException(t + ": No values to add into table " + tableId);
        }

        HashMap<String, Object> cvDataTableVal = new HashMap<String, Object>();
        cvDataTableVal.putAll(cvValues);

        // if this is a server-requested change, all the user fields and admin columns should be specified.
        if (asServerRequestedChange && !asCsvRequestedChange) {
            for (String columnName : orderedColumns.getRetentionColumnNames()) {
                if (!cvDataTableVal.containsKey(columnName)) {
                    throw new IllegalArgumentException(t + ": Not all user field values are set during server "
                            + (shouldUpdate ? "update" : "insert") + " in table " + tableId + " missing: "
                            + columnName);
                }
            }
            for (String columnName : ADMIN_COLUMNS) {
                if (!cvDataTableVal.containsKey(columnName)) {
                    throw new IllegalArgumentException(t + ": Not all metadata field values are set during server "
                            + (shouldUpdate ? "update" : "insert") + " in table " + tableId + " missing: "
                            + columnName);
                }
            }
        }

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            if (cvDataTableVal.containsKey(DataTableColumns.ID)) {
                // The user specified a row id; we need to determine whether to
                // insert or update the record, or to reject the action because
                // there are either checkpoint records for this row id, or, if
                // a server conflict is associated with this row, that the
                // _conflict_type to update was not specified.
                //
                // i.e., the tuple (_id, _conflict_type) should be unique. If
                // we find that there are more than 0 or 1 records matching this
                // tuple, then we should reject the update request.
                //
                // TODO: perhaps we want to allow updates to the local conflict
                // row if there are no checkpoints on it? I.e., change the
                // tri-state conflict type to a pair of states (local / remote).
                // and all local changes are flagged local. Remote only exists
                // if the server is in conflict.

                rowId = (String) cvDataTableVal.get(DataTableColumns.ID);
                if (rowId == null) {
                    throw new IllegalArgumentException(DataTableColumns.ID + ", if specified, cannot be null");
                }

                if (specifiesConflictType) {
                    if (nullConflictType) {
                        whereClause = K_DATATABLE_ID_EQUALS_PARAM + S_AND + DataTableColumns.CONFLICT_TYPE
                                + S_IS_NULL;
                        whereArgs[0] = rowId;
                    } else {
                        whereClause = K_DATATABLE_ID_EQUALS_PARAM + S_AND + DataTableColumns.CONFLICT_TYPE
                                + S_EQUALS_PARAM;
                        whereArgs[0] = rowId;
                        whereArgs[1] = cvValues.get(DataTableColumns.CONFLICT_TYPE);
                    }
                } else {
                    whereClause = K_DATATABLE_ID_EQUALS_PARAM;
                    whereArgs[0] = rowId;
                }

                AccessContext accessContext = getAccessContext(db, tableId, activeUser,
                        RoleConsts.ADMIN_ROLES_LIST);

                StringBuilder b = new StringBuilder();
                b.append(K_SELECT_FROM).append(tableId).append(K_WHERE).append(whereClause);
                BaseTable data = privilegedQuery(db, tableId, b.toString(), whereArgs, null, accessContext);

                // There must be only one row in the db for the update to work
                if (shouldUpdate) {
                    if (data.getNumberOfRows() == 1) {
                        int filterTypeCursorIndex = data.getColumnIndexOfElementKey(DataTableColumns.FILTER_TYPE);
                        priorFilterType = data.getRowAtIndex(0).getDataByIndex(filterTypeCursorIndex);
                        if (priorFilterType == null) {
                            priorFilterType = DataTableColumns.DEFAULT_FILTER_TYPE;
                        }
                        int filterValueCursorIndex = data.getColumnIndexOfElementKey(DataTableColumns.FILTER_VALUE);
                        priorFilterValue = data.getRowAtIndex(0).getDataByIndex(filterValueCursorIndex);
                        int syncStateCursorIndex = data.getColumnIndexOfElementKey(DataTableColumns.SYNC_STATE);
                        updatedSyncState = data.getRowAtIndex(0).getDataByIndex(syncStateCursorIndex);

                        if (updatedSyncState.equals(SyncState.deleted.name())
                                || updatedSyncState.equals(SyncState.in_conflict.name())) {
                            throw new IllegalStateException(t + ": Cannot update a deleted or in-conflict row");
                        } else if (updatedSyncState.equals(SyncState.synced.name())
                                || updatedSyncState.equals(SyncState.synced_pending_files.name())) {
                            updatedSyncState = SyncState.changed.name();
                        }
                        update = true;
                    } else if (data.getNumberOfRows() > 1) {
                        throw new IllegalArgumentException(
                                t + ": row id " + rowId + " has more than 1 row in table " + tableId);
                    }
                } else {
                    if (data.getNumberOfRows() > 0) {
                        throw new IllegalArgumentException(
                                t + ": row id " + rowId + " is already present in table " + tableId);
                    }
                }

            } else {
                rowId = "uuid:" + UUID.randomUUID().toString();
            }

            // TODO: This is broken w.r.t. updates of partial fields
            // TODO: This is broken w.r.t. updates of partial fields
            // TODO: This is broken w.r.t. updates of partial fields
            // TODO: This is broken w.r.t. updates of partial fields

            if (!cvDataTableVal.containsKey(DataTableColumns.ID)) {
                cvDataTableVal.put(DataTableColumns.ID, rowId);
            }

            List<String> rolesArray = getRolesArray(rolesList);

            // get the security settings
            TableSecuritySettings tss = getTableSecuritySettings(db, tableId);

            if (!asServerRequestedChange) {
                // do not allow filterType or filterValue to be modified in normal workflow
                if (cvDataTableVal.containsKey(DataTableColumns.FILTER_TYPE)
                        || cvDataTableVal.containsKey(DataTableColumns.FILTER_VALUE)) {

                    tss.canModifyFilterTypeAndValue(rolesArray);
                }
            }

            if (update) {

                // MODIFYING

                if (!cvDataTableVal.containsKey(DataTableColumns.SYNC_STATE)
                        || (cvDataTableVal.get(DataTableColumns.SYNC_STATE) == null)) {
                    cvDataTableVal.put(DataTableColumns.SYNC_STATE, updatedSyncState);
                }

                if (!asServerRequestedChange) {

                    // apply row access restrictions
                    // this will throw an IllegalArgumentException
                    tss.allowRowChange(activeUser, rolesArray, updatedSyncState, priorFilterType, priorFilterValue,
                            RowChange.CHANGE_ROW);

                }

                if (cvDataTableVal.containsKey(DataTableColumns.LOCALE)
                        && (cvDataTableVal.get(DataTableColumns.LOCALE) == null)) {
                    cvDataTableVal.put(DataTableColumns.LOCALE, locale);
                }

                if (cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_TYPE)
                        && (cvDataTableVal.get(DataTableColumns.SAVEPOINT_TYPE) == null)) {
                    cvDataTableVal.put(DataTableColumns.SAVEPOINT_TYPE, SavepointTypeManipulator.complete());
                }

                if (!cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_TIMESTAMP)
                        || cvDataTableVal.get(DataTableColumns.SAVEPOINT_TIMESTAMP) == null) {
                    String timeStamp = TableConstants.nanoSecondsFromMillis(System.currentTimeMillis());
                    cvDataTableVal.put(DataTableColumns.SAVEPOINT_TIMESTAMP, timeStamp);
                }

                if (!cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_CREATOR)
                        || (cvDataTableVal.get(DataTableColumns.SAVEPOINT_CREATOR) == null)) {
                    cvDataTableVal.put(DataTableColumns.SAVEPOINT_CREATOR, activeUser);
                }
            } else {

                // INSERTING

                if (!cvDataTableVal.containsKey(DataTableColumns.ROW_ETAG)
                        || cvDataTableVal.get(DataTableColumns.ROW_ETAG) == null) {
                    cvDataTableVal.put(DataTableColumns.ROW_ETAG, DataTableColumns.DEFAULT_ROW_ETAG);
                }

                if (!cvDataTableVal.containsKey(DataTableColumns.SYNC_STATE)
                        || (cvDataTableVal.get(DataTableColumns.SYNC_STATE) == null)) {
                    cvDataTableVal.put(DataTableColumns.SYNC_STATE, SyncState.new_row.name());
                }

                if (!cvDataTableVal.containsKey(DataTableColumns.CONFLICT_TYPE)) {
                    cvDataTableVal.put(DataTableColumns.CONFLICT_TYPE, null);
                }

                if (!asServerRequestedChange) {

                    cvDataTableVal.put(DataTableColumns.FILTER_TYPE, tss.filterTypeOnCreation);

                    // activeUser
                    cvDataTableVal.put(DataTableColumns.FILTER_VALUE, activeUser);

                    tss.allowRowChange(activeUser, rolesArray, updatedSyncState, priorFilterType, priorFilterValue,
                            RowChange.NEW_ROW);
                }

                if (!cvDataTableVal.containsKey(DataTableColumns.FORM_ID)) {
                    cvDataTableVal.put(DataTableColumns.FORM_ID, null);
                }

                if (!cvDataTableVal.containsKey(DataTableColumns.LOCALE)
                        || (cvDataTableVal.get(DataTableColumns.LOCALE) == null)) {
                    cvDataTableVal.put(DataTableColumns.LOCALE, locale);
                }

                if (!cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_TYPE)
                        || (cvDataTableVal.get(DataTableColumns.SAVEPOINT_TYPE) == null)) {
                    cvDataTableVal.put(DataTableColumns.SAVEPOINT_TYPE, SavepointTypeManipulator.complete());
                }

                if (!cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_TIMESTAMP)
                        || cvDataTableVal.get(DataTableColumns.SAVEPOINT_TIMESTAMP) == null) {
                    String timeStamp = TableConstants.nanoSecondsFromMillis(System.currentTimeMillis());
                    cvDataTableVal.put(DataTableColumns.SAVEPOINT_TIMESTAMP, timeStamp);
                }

                if (!cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_CREATOR)
                        || (cvDataTableVal.get(DataTableColumns.SAVEPOINT_CREATOR) == null)) {
                    cvDataTableVal.put(DataTableColumns.SAVEPOINT_CREATOR, activeUser);
                }
            }

            cleanUpValuesMap(orderedColumns, cvDataTableVal);

            if (update) {
                db.update(tableId, cvDataTableVal, whereClause, whereArgs);
            } else {
                db.insertOrThrow(tableId, null, cvDataTableVal);
            }

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * Update the ETag and SyncState of a given rowId. There should be exactly one
     * record for this rowId in the database (i.e., no conflicts or checkpoints).
     *
     * @param db
     * @param tableId
     * @param rowId
     * @param rowETag
     * @param state
     * @return true if rowId exists. False otherwise.
     */
    public boolean privilegedUpdateRowETagAndSyncState(OdkConnectionInterface db, String tableId, String rowId,
            String rowETag, SyncState state, String activeUser) {

        String whereClause = K_DATATABLE_ID_EQUALS_PARAM;
        Object[] whereArgs = { rowId };

        TreeMap<String, Object> cvDataTableVal = new TreeMap<String, Object>();

        cvDataTableVal.put(DataTableColumns.ROW_ETAG, rowETag);
        cvDataTableVal.put(DataTableColumns.SYNC_STATE, state.name());

        boolean dbWithinTransaction = db.inTransaction();
        try {
            if (!dbWithinTransaction) {
                db.beginTransactionNonExclusive();
            }

            AccessContext accessContext = getAccessContext(db, tableId, activeUser, RoleConsts.ADMIN_ROLES_LIST);

            StringBuilder b = new StringBuilder();
            b.append(K_SELECT_FROM).append(tableId).append(K_WHERE).append(whereClause);
            BaseTable data = privilegedQuery(db, tableId, b.toString(), whereArgs, null, accessContext);

            // There must be only one row in the db
            if (data.getNumberOfRows() != 1) {
                return false;
            }

            db.update(tableId, cvDataTableVal, whereClause, whereArgs);

            if (!dbWithinTransaction) {
                db.setTransactionSuccessful();
            }
            return true;
        } finally {
            if (!dbWithinTransaction) {
                db.endTransaction();
            }
        }
    }

    /**
     * If the caller specified a complex json value for a structured type, flush
     * the value through to the individual columns.
     *
     * @param orderedColumns
     * @param values
     */
    private void cleanUpValuesMap(OrderedColumns orderedColumns, Map<String, Object> values) {

        TreeMap<String, String> toBeResolved = new TreeMap<String, String>();

        for (String key : values.keySet()) {
            if (DataTableColumns.CONFLICT_TYPE.equals(key)) {
                continue;
            } else if (DataTableColumns.FILTER_TYPE.equals(key)) {
                continue;
            } else if (DataTableColumns.FILTER_VALUE.equals(key)) {
                continue;
            } else if (DataTableColumns.FORM_ID.equals(key)) {
                continue;
            } else if (DataTableColumns.ID.equals(key)) {
                continue;
            } else if (DataTableColumns.LOCALE.equals(key)) {
                continue;
            } else if (DataTableColumns.ROW_ETAG.equals(key)) {
                continue;
            } else if (DataTableColumns.SAVEPOINT_CREATOR.equals(key)) {
                continue;
            } else if (DataTableColumns.SAVEPOINT_TIMESTAMP.equals(key)) {
                continue;
            } else if (DataTableColumns.SAVEPOINT_TYPE.equals(key)) {
                continue;
            } else if (DataTableColumns.SYNC_STATE.equals(key)) {
                continue;
            } else if (DataTableColumns._ID.equals(key)) {
                continue;
            }
            // OK it is one of the data columns
            ColumnDefinition cp = orderedColumns.find(key);
            if (!cp.isUnitOfRetention()) {
                toBeResolved.put(key, (String) values.get(key));
            }
        }

        // remove these non-retained values from the values set...
        for (String key : toBeResolved.keySet()) {
            values.remove(key);
        }

        while (!toBeResolved.isEmpty()) {

            TreeMap<String, String> moreToResolve = new TreeMap<String, String>();

            for (TreeMap.Entry<String, String> entry : toBeResolved.entrySet()) {
                String key = entry.getKey();
                String json = entry.getValue();
                if (json == null) {
                    // don't need to do anything
                    // since the value is null
                    continue;
                }
                ColumnDefinition cp = orderedColumns.find(key);
                try {
                    TypeReference<Map<String, Object>> reference = new TypeReference<Map<String, Object>>() {
                    };
                    Map<String, Object> struct = ODKFileUtils.mapper.readValue(json, reference);
                    for (ColumnDefinition child : cp.getChildren()) {
                        String subkey = child.getElementKey();
                        ColumnDefinition subcp = orderedColumns.find(subkey);
                        if (subcp.isUnitOfRetention()) {
                            ElementType subtype = subcp.getType();
                            ElementDataType type = subtype.getDataType();
                            if (type == ElementDataType.integer) {
                                values.put(subkey, (Integer) struct.get(subcp.getElementName()));
                            } else if (type == ElementDataType.number) {
                                values.put(subkey, (Double) struct.get(subcp.getElementName()));
                            } else if (type == ElementDataType.bool) {
                                values.put(subkey, ((Boolean) struct.get(subcp.getElementName())) ? 1 : 0);
                            } else {
                                values.put(subkey, (String) struct.get(subcp.getElementName()));
                            }
                        } else {
                            // this must be a javascript structure... re-JSON it and save (for
                            // next round).
                            moreToResolve.put(subkey,
                                    ODKFileUtils.mapper.writeValueAsString(struct.get(subcp.getElementName())));
                        }
                    }
                } catch (JsonParseException e) {
                    e.printStackTrace();
                    throw new IllegalStateException("should not be happening");
                } catch (JsonMappingException e) {
                    e.printStackTrace();
                    throw new IllegalStateException("should not be happening");
                } catch (IOException e) {
                    e.printStackTrace();
                    throw new IllegalStateException("should not be happening");
                }
            }

            toBeResolved = moreToResolve;
        }
    }

    public static void initializeDatabase(OdkConnectionInterface db) {
        commonTableDefn(db);
    }

    private static void commonTableDefn(OdkConnectionInterface db) {
        WebLogger.getLogger(db.getAppName()).i("commonTableDefn", "starting");
        WebLogger.getLogger(db.getAppName()).i("commonTableDefn", DatabaseConstants.UPLOADS_TABLE_NAME);
        db.execSQL(InstanceColumns.getTableCreateSql(DatabaseConstants.UPLOADS_TABLE_NAME), null);
        WebLogger.getLogger(db.getAppName()).i("commonTableDefn", DatabaseConstants.FORMS_TABLE_NAME);
        db.execSQL(FormsColumns.getTableCreateSql(DatabaseConstants.FORMS_TABLE_NAME), null);
        WebLogger.getLogger(db.getAppName()).i("commonTableDefn", DatabaseConstants.COLUMN_DEFINITIONS_TABLE_NAME);
        db.execSQL(ColumnDefinitionsColumns.getTableCreateSql(DatabaseConstants.COLUMN_DEFINITIONS_TABLE_NAME),
                null);
        WebLogger.getLogger(db.getAppName()).i("commonTableDefn",
                DatabaseConstants.KEY_VALUE_STORE_ACTIVE_TABLE_NAME);
        db.execSQL(KeyValueStoreColumns.getTableCreateSql(DatabaseConstants.KEY_VALUE_STORE_ACTIVE_TABLE_NAME),
                null);
        WebLogger.getLogger(db.getAppName()).i("commonTableDefn", DatabaseConstants.TABLE_DEFS_TABLE_NAME);
        db.execSQL(TableDefinitionsColumns.getTableCreateSql(DatabaseConstants.TABLE_DEFS_TABLE_NAME), null);
        WebLogger.getLogger(db.getAppName()).i("commonTableDefn", DatabaseConstants.SYNC_ETAGS_TABLE_NAME);
        db.execSQL(SyncETagColumns.getTableCreateSql(DatabaseConstants.SYNC_ETAGS_TABLE_NAME), null);
        WebLogger.getLogger(db.getAppName()).i("commonTableDefn", DatabaseConstants.CHOICE_LIST_TABLE_NAME);
        db.execSQL(ChoiceListColumns.getTableCreateSql(DatabaseConstants.CHOICE_LIST_TABLE_NAME), null);
        WebLogger.getLogger(db.getAppName()).i("commonTableDefn", "done");
    }
}