Table.java

/*
 * Copyright (c) 2004-2026 Fred Hutchinson Cancer Research Center
 *
 * 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.labkey.api.data;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.Strings;
import org.apache.logging.log4j.Level;
import org.apache.logging.log4j.Logger;
import org.jetbrains.annotations.NotNull;
import org.jetbrains.annotations.Nullable;
import org.junit.Assert;
import org.junit.Test;
import org.labkey.api.collections.BoundMap;
import org.labkey.api.collections.CaseInsensitiveHashMap;
import org.labkey.api.collections.CaseInsensitiveHashSet;
import org.labkey.api.data.dialect.SqlDialect;
import org.labkey.api.data.validator.ColumnValidator;
import org.labkey.api.data.validator.ColumnValidators;
import org.labkey.api.dataiterator.AbstractDataIterator;
import org.labkey.api.dataiterator.DataIteratorBuilder;
import org.labkey.api.dataiterator.DataIteratorContext;
import org.labkey.api.dataiterator.Pump;
import org.labkey.api.dataiterator.SimpleTranslator;
import org.labkey.api.dataiterator.SimpleTranslator.SpecialColumn;
import org.labkey.api.dataiterator.TableInsertDataIteratorBuilder;
import org.labkey.api.di.DataIntegrationService;
import org.labkey.api.exceptions.OptimisticConflictException;
import org.labkey.api.exp.api.ExperimentService;
import org.labkey.api.exp.property.Domain;
import org.labkey.api.exp.property.DomainKind;
import org.labkey.api.miniprofiler.MiniProfiler;
import org.labkey.api.query.AliasedColumn;
import org.labkey.api.query.FieldKey;
import org.labkey.api.query.QueryService;
import org.labkey.api.query.RuntimeValidationException;
import org.labkey.api.security.User;
import org.labkey.api.util.BaseScanner;
import org.labkey.api.util.ExceptionUtil;
import org.labkey.api.util.GUID;
import org.labkey.api.util.JunitUtil;
import org.labkey.api.util.MemTracker;
import org.labkey.api.util.TestContext;
import org.labkey.api.util.logging.LogHelper;

import java.io.EOFException;
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;

/**
 * Table manipulation methods
 * <p/>
 * select/insert/update/delete
 */

public class Table
{
    public static final String SQLSTATE_TRANSACTION_STATE = "25000";
    public static final int ERROR_ROWVERSION = 10001;
    public static final int ERROR_DELETED = 10002;
    public static final int ERROR_TABLEDELETED = 10003;

    private static final Logger _log = LogHelper.getLogger(Table.class, "SQL generation and execution, some TableInfo-scoped DB operations");

    // Don't limit the rows returned to a specific count (filters and offsets can still eliminate rows)
    public static final int ALL_ROWS = -1;
    // Return no rows -- useful for query validation or when you need just metadata
    public static final int NO_ROWS = 0;

    // Makes long parameter lists easier to read
    public static final int NO_OFFSET = 0;

    private static final String ENTITY_ID_COLUMN_NAME = SpecialColumn.EntityId.name();
    private static final String OWNER_COLUMN_NAME = "Owner";
    private static final String CREATED_BY_COLUMN_NAME = SpecialColumn.CreatedBy.name();
    private static final String CREATED_COLUMN_NAME = SpecialColumn.Created.name();
    private static final String MODIFIED_BY_COLUMN_NAME = SpecialColumn.ModifiedBy.name();
    private static final String MODIFIED_COLUMN_NAME = SpecialColumn.Modified.name();

    /** Columns that are magically populated as part of an insert or update operation */
    public static final Set<String> AUTOPOPULATED_COLUMN_NAMES = CaseInsensitiveHashSet.of(
        ENTITY_ID_COLUMN_NAME,
        OWNER_COLUMN_NAME,
        CREATED_BY_COLUMN_NAME,
        CREATED_COLUMN_NAME,
        MODIFIED_BY_COLUMN_NAME,
        MODIFIED_COLUMN_NAME
    );

    private Table()
    {
    }

    public static boolean validMaxRows(int maxRows)
    {
        return NO_ROWS == maxRows | ALL_ROWS == maxRows | maxRows > 0;
    }

    // ================== These methods have not been converted to Selector/Executor ==================

    // Careful: caller must track and clean up parameters (e.g., close InputStreams) after execution is complete
    public static PreparedStatement prepareStatement(Connection conn, String sql, Collection<?> parameters, List<Parameter> jdbcParameters) throws SQLException
    {
        PreparedStatement stmt = conn.prepareStatement(sql);
        setParameters(stmt, parameters, jdbcParameters);
        MemTracker.getInstance().put(stmt);
        return stmt;
    }

    public static void setParameters(PreparedStatement stmt, Collection<?> parameters, List<Parameter> jdbcParameters)
    {
        if (null == parameters)
            return;

        int i = 1;

        for (Object value : parameters)
        {
            //Parameter validation
            //Bug 1996 - rossb:  Generally, we let JDBC validate the
            //parameters and throw exceptions, however, it doesn't recognize NaN
            //properly which can lead to database corruption.
            // [This is now handled by making a special value (in ResultSetUtil) to hand to the DB]

            Parameter p = new Parameter(stmt, i);
            p.setValue(value);
            jdbcParameters.add(p);
            i++;
        }
    }

    /**
     *  @return if this is a SELECT statement that contains FROM, possibly proceeded by one or more CTEs (WITH clause).
     *  Comments and quoted strings are ignored.
     */
    public static boolean isSelect(String sql)
    {
        // Remove comments and quoted strings, then trim
        String noComments = new SqlScanner(sql).stripComments().toString();
        String strippedSql = new SqlScanner(noComments).stripQuotedStrings().toString().trim();

        // If present, remove WITH statement, which should leave behind a SELECT
        if (strippedSql.matches("^(?ims)WITH\\s.*$")) // flags: case-insensitive, multiline, dot-all
        {
            int fromIndex = "WITH ".length();

            // WITH clause contains one or more CTEs; skip them all
            do
            {
                SqlScanner scanner = new SqlScanner(strippedSql);
                SkipCteHandler handler = new SkipCteHandler();
                scanner.scan(fromIndex, handler);
                String error = handler.getError();

                if (null != error)
                {
                    _log.warn("{}!\n{}", error, strippedSql);
                    return false;
                }

                strippedSql = strippedSql.substring(handler.getSelectIdx()).trim();
                fromIndex = 1;
            }
            while (strippedSql.startsWith(","));
        }

        // We must see a FROM clause, so we don't flag stored procedure invocations, #22648
        return strippedSql.matches("^(?ims)SELECT\\s.*\\sFROM\\s.*$"); // flags: case-insensitive, multiline, dot-all
    }

    private static class SkipCteHandler implements BaseScanner.Handler
    {
        private boolean _parenSeen = false;
        private int _parens = -1;
        private int _selectIdx = -1;

        @Override
        public boolean character(char c, int index)
        {
            if (!_parenSeen)
            {
                if (c == '(')
                {
                    _parenSeen = true;
                    _parens = 1;
                }
            }
            else
            {
                switch (c)
                {
                    case '(' -> _parens++;
                    case ')' -> _parens--;
                }

                if (_parens == 0)
                {
                    _selectIdx = index + 1;
                    return false;
                }
            }

            return true;
        }

        private @Nullable String getError()
        {
            if (!_parenSeen)
                return "Parentheses expected after WITH statement";
            else if (_parens != 0)
                return "Mismatched parentheses";
            else
                return null;
        }

        private int getSelectIdx()
        {
            return _selectIdx;
        }
    }

    public static class IsSelectTestCase extends Assert
    {
        @Test
        public void test()
        {
            assertTrue(isSelect("SELECT * FROM foo.bar"));
            assertTrue(isSelect("WITH Blue AS (SELECT * FROM foo.bar) SELECT * FROM Blue"));
            assertTrue(isSelect("WITH cte AS (SELECT CAST('1' AS INTEGER) AS i) SELECT * FROM cte"));
            assertTrue(isSelect("with lowercase as (select cast('1' as integer) as i) select * from lowercase"));
            assertTrue(isSelect("WITH\nMultiLine AS (SELECT * FROM foo.bar)\nSELECT *\nFROM Blue"));
            assertTrue(isSelect("""
                WITH RECURSIVE
                /*CTE*/
                    org_lk_exp_CHILDREN_INNER AS (SELECT
                      0 AS depth)
                ,/*CTE*/
                    org_lk_exp_CHILDREN AS (SELECT
                        I.depth,
                        I.self),
                /*CTE*/
                    org_lk_exp_CHILDREN_OUTER AS (SELECT 2 AS depth)
                SELECT *
                FROM (
                SELECT
                SourceSamples.SampleState AS SampleState
                FROM org_lk_exp_CHILDREN
                WHERE depth != 0
                AND objectid <> self
                AND depth <= 10
                GROUP BY self, objectid) AS X))
                ORDER BY rowid ASC
                LIMIT 21"""));

            assertFalse(isSelect("WITH Nothing SELECT * FROM foo.bar"));
            assertFalse(isSelect("WITH Mismatched AS (SELECT * FROM (SELECT * FROM foo.bar) SELECT * FROM Mismatched"));
            assertFalse(isSelect("WITH Incomplete AS (SELECT * FROM foo.bar)"));
            assertFalse(isSelect("WITH NonSelect AS (SELECT * FROM foo.bar) UPDATE foo.bar SET bam = 1"));
            assertFalse(isSelect("WITH NonSelect AS (SELECT * FROM foo.bar) DELETE FROM foo.bar WHERE 1=1"));
            assertFalse(isSelect("WITHOUT NonCTE AS (SELECT * FROM foo.bar) SELECT NonCTE WHERE 1=1"));
            assertFalse(isSelect("SELECT \" FROM \" /* FROM */ ' FROM ' foo.bar WHERE 1=1"));
        }
    }

    // Careful: Caller must track and clean up parameters (e.g., close InputStreams) after execution is complete
    public static void batchExecute(DbSchema schema, String sql, Iterable<? extends Collection<?>> paramList) throws SQLException
    {
        Connection conn = schema.getScope().getConnection();
        PreparedStatement stmt = null;

        try (Parameter.ParameterList jdbcParameters = new Parameter.ParameterList())
        {
            stmt = conn.prepareStatement(sql);
            int paramCounter = 0;
            for (Collection<?> params : paramList)
            {
                setParameters(stmt, params, jdbcParameters);
                stmt.addBatch();

                paramCounter += params.size();
                if (paramCounter > 1000)
                {
                    paramCounter = 0;
                    stmt.executeBatch();
                    jdbcParameters.close();
                }
            }
            stmt.executeBatch();
        }
        catch (SQLException|RuntimeSQLException e)
        {
            SQLException sqlx = e instanceof RuntimeSQLException ? ((RuntimeSQLException)e).getSQLException() : (SQLException)e;
            if (sqlx instanceof BatchUpdateException)
            {
                if (null != sqlx.getNextException())
                    sqlx = sqlx.getNextException();
            }
            logException(new SQLFragment(sql), conn, sqlx, Level.WARN);
            throw sqlx;
        }
        finally
        {
            doClose(null, stmt, conn, schema.getScope());
        }
    }

    static void batchExecute1String(DbSchema schema, String sql, Iterable<String> paramList) throws SQLException
    {
        Connection conn = schema.getScope().getConnection();
        PreparedStatement stmt = null;

        try
        {
            stmt = conn.prepareStatement(sql);
            int paramCounter = 0;
            for (String s : paramList)
            {
                stmt.setString(1, s);
                stmt.addBatch();
                paramCounter++;
                if (paramCounter > 2000)
                {
                    paramCounter = 0;
                    stmt.executeBatch();
                }
            }
            stmt.executeBatch();
        }
        catch (SQLException e)
        {
            if (e instanceof BatchUpdateException)
            {
                if (null != e.getNextException())
                    e = e.getNextException();
            }

            logException(new SQLFragment(sql), conn, e, Level.WARN);
            throw(e);
        }
        finally
        {
            doClose(null, stmt, conn, schema.getScope());
        }
    }

    public static void batchExecute1Integer(DbSchema schema, @NotNull String sql1, @Nullable String sql100, List<Integer> paramList) throws SQLException
    {
        _batchExecute1Integer(Integer.class, schema, sql1, sql100, paramList);
    }

    public static void batchExecute1Long(DbSchema schema, @NotNull String sql1, @Nullable String sql100, List<Long> paramList) throws SQLException
    {
        _batchExecute1Integer(Long.class, schema, sql1, sql100, paramList);
    }

    public static <T> void _batchExecute1Integer(Class<T> clazz, DbSchema schema, @NotNull String sql1, @Nullable String sql100, List<T> paramList) throws SQLException
    {
        boolean useInteger = clazz == Integer.class;

        try (Connection conn = schema.getScope().getConnection())
        {
            try
            {
                int paramCounter = 0;

                // insert by 100's
                int outerIndex = 0;
                if (null != sql100 && paramList.size() >= 100)
                {
                    try (PreparedStatement stmt100 = conn.prepareStatement(sql100))
                    {
                        for (; outerIndex < paramList.size() - 100; outerIndex += 100)
                        {
                            for (int index = outerIndex; index < outerIndex + 100; index++)
                            {
                                Number I = (Number)paramList.get(index);
                                if (null == I)
                                    stmt100.setNull(index % 100 + 1, Types.INTEGER);
                                else if (useInteger)
                                    stmt100.setInt(index % 100 + 1, I.intValue());
                                else
                                    stmt100.setLong(index % 100 + 1, I.longValue());
                                paramCounter++;
                            }
                            stmt100.addBatch();
                            if (paramCounter > 10000)
                            {
                                stmt100.executeBatch();
                                paramCounter = 0;
                            }
                        }
                        if (paramCounter > 0)
                        {
                            stmt100.executeBatch();
                            paramCounter = 0;
                        }
                    }
                }

                // insert by 1's
                try (PreparedStatement stmt1 = conn.prepareStatement(sql1))
                {
                    for (int index = outerIndex; index < paramList.size(); index++)
                    {
                        Number I = (Number)paramList.get(index);
                        if (null == I)
                            stmt1.setNull(1, Types.INTEGER);
                        else if (useInteger)
                            stmt1.setInt(1, I.intValue());
                        else
                            stmt1.setLong(1, I.longValue());

                        stmt1.addBatch();
                        paramCounter++;
                        if (paramCounter > 1000)
                        {
                            stmt1.executeBatch();
                            paramCounter = 0;
                        }
                    }
                    if (paramCounter > 0)
                        stmt1.executeBatch();
                }
            }
            catch (SQLException e)
            {
                if (e instanceof BatchUpdateException)
                {
                    if (null != e.getNextException())
                        e = e.getNextException();
                }

                logException(new SQLFragment(sql1), conn, e, Level.WARN);
                throw (e);
            }
        }
    }


    private static final Map<Class<?>, Getter> _getterMap = new HashMap<>(20);

    public enum Getter
    {
        STRING(String.class) {
            @Override
            String getObject(ResultSet rs, int i) throws SQLException { return rs.getString(i); }
            @Override
            String getObject(ResultSet rs, String columnLabel) throws SQLException { return rs.getString(columnLabel); }
        },
        INTEGER(Integer.class) {
            @Override
            Integer getObject(ResultSet rs, int i) throws SQLException { int n = rs.getInt(i); return rs.wasNull() ? null : n ; }
            @Override
            Integer getObject(ResultSet rs, String columnLabel) throws SQLException { int n = rs.getInt(columnLabel); return rs.wasNull() ? null : n ; }
        },
        DOUBLE(Double.class) {
            @Override
            Double getObject(ResultSet rs, int i) throws SQLException { double d = rs.getDouble(i); return rs.wasNull() ? null : d ; }
            @Override
            Double getObject(ResultSet rs, String columnLabel) throws SQLException { double d = rs.getDouble(columnLabel); return rs.wasNull() ? null : d ; }
        },
        BOOLEAN(Boolean.class) {
            @Override
            Boolean getObject(ResultSet rs, int i) throws SQLException { boolean f = rs.getBoolean(i); return rs.wasNull() ? null : f ; }
            @Override
            Boolean getObject(ResultSet rs, String columnLabel) throws SQLException { boolean f = rs.getBoolean(columnLabel); return rs.wasNull() ? null : f ; }
        },
        LONG(Long.class) {
            @Override
            Long getObject(ResultSet rs, int i) throws SQLException { long l = rs.getLong(i); return rs.wasNull() ? null : l; }
            @Override
            Long getObject(ResultSet rs, String columnLabel) throws SQLException { long l = rs.getLong(columnLabel); return rs.wasNull() ? null : l; }
        },
        UTIL_DATE(Date.class) {
            @Override
            Date getObject(ResultSet rs, int i) throws SQLException { return rs.getTimestamp(i); }
            @Override
            Date getObject(ResultSet rs, String columnLabel) throws SQLException { return rs.getTimestamp(columnLabel); }
        },
        BYTES(byte[].class) {
            @Override
            Object getObject(ResultSet rs, int i) throws SQLException { return rs.getBytes(i); }
            @Override
            Object getObject(ResultSet rs, String columnLabel) throws SQLException { return rs.getBytes(columnLabel); }
        },
        TIMESTAMP(Timestamp.class) {
            @Override
            Object getObject(ResultSet rs, int i) throws SQLException { return rs.getTimestamp(i); }
            @Override
            Object getObject(ResultSet rs, String columnLabel) throws SQLException { return rs.getTimestamp(columnLabel); }
        },
        LOCAL_DATE(LocalDate.class) {
            @Override
            Object getObject(ResultSet rs, int i) throws SQLException { return convert(rs.getDate(i)); }
            @Override
            Object getObject(ResultSet rs, String columnLabel) throws SQLException { return convert(rs.getDate(columnLabel)); }
            private LocalDate convert(@Nullable java.sql.Date sqlDate)
            {
                return null == sqlDate ? null : sqlDate.toLocalDate();
            }
        },
        LOCAL_DATE_TIME(LocalDateTime.class) {
            @Override
            Object getObject(ResultSet rs, int i) throws SQLException { return convert(rs.getTimestamp(i)); }
            @Override
            Object getObject(ResultSet rs, String columnLabel) throws SQLException { return convert(rs.getTimestamp(columnLabel)); }
            private LocalDateTime convert(@Nullable Timestamp timestamp)
            {
                return null == timestamp ? null : timestamp.toLocalDateTime();
            }
        },
        OBJECT(Object.class) {
            @Override
            Object getObject(ResultSet rs, int i) throws SQLException { return rs.getObject(i); }
            @Override
            Object getObject(ResultSet rs, String columnLabel) throws SQLException { return rs.getObject(columnLabel); }
        };

        abstract Object getObject(ResultSet rs, int i) throws SQLException;
        abstract Object getObject(ResultSet rs, String columnName) throws SQLException;

        Object getObject(ResultSet rs) throws SQLException
        {
            return getObject(rs, 1);
        }

        Getter(Class<?> c)
        {
            _getterMap.put(c, this);
        }

        public static <K> @Nullable Getter forClass(Class<K> c)
        {
            return _getterMap.get(c);
        }
    }


    // Standard SQLException catch block: log exception, query SQL, and params
    static void logException(@Nullable SQLFragment sql, @Nullable Connection conn, SQLException e, Level logLevel)
    {
        Level level = getExceptionLogLevel(sql, e, logLevel);
        if (null == level)
            return;

        if (SqlDialect.isConnectionLostException(e))
            _log.error("Database connection lost during query, SQLState: {}", e.getSQLState(), e);
        else if (sql == null)
            _log.error("SQL Exception, no SQL query text available", e);
        else
            _log.log(level, "SQL Exception{}", e.getSQLState() == null ? "" : (" with SQLState: " + e.getSQLState()), e);

        _logQuery(level, sql, conn);
    }

    /** Level at which logException() logs e and its SQL, or null if it shouldn't be logged */
    static @Nullable Level getExceptionLogLevel(@Nullable SQLFragment sql, SQLException e, Level logLevel)
    {
        if (SqlDialect.isCancelException(e))
            return null;

        // A killed backend surfaces as an I/O error wrapping EOFException, which isIgnorable() mistakes for a client abort
        boolean connectionLost = SqlDialect.isConnectionLostException(e);
        if (!connectionLost && ExceptionUtil.isIgnorable(e))
            return null;

        // Treat a ConstraintException during INSERT/UPDATE as a WARNING. Treat all other SQLExceptions as an ERROR.
        Level level = Level.ERROR;
        if (!connectionLost && sql != null && RuntimeSQLException.isConstraintException(e))
        {
            String trim = sql.getSQL().trim();
            if (Strings.CI.startsWith(trim, "INSERT") || Strings.CI.startsWith(trim, "UPDATE"))
                level = Level.WARN;
        }

        return level.isMoreSpecificThan(logLevel) ? level : null;
    }

    static void debugException(SQLFragment sql, @Nullable Connection conn, SQLException e)
    {
        _log.debug(e);
        _logQuery(Level.DEBUG, sql, conn);
    }


    /** Typical finally block cleanup. Tolerant of null or already closed JDBC resources */
    static void doClose(@Nullable ResultSet rs, @Nullable Statement stmt, @Nullable Connection conn, @NotNull DbScope scope)
    {
        try
        {
            if (stmt == null && rs != null && !rs.isClosed())
                stmt = rs.getStatement();
        }
        catch (SQLException x)
        {
            _log.error("doFinally", x);
        }

        try
        {
            if (null != rs) rs.close();
        }
        catch (SQLException x)
        {
            _log.error("doFinally", x);
        }
        try
        {
            if (null != stmt) stmt.close();
        }
        catch (SQLException x)
        {
            _log.error("doFinally", x);
        }

        if (null != conn) scope.releaseConnection(conn);
    }

    /**
     * return a 'clean' list of fields to update
     */
    protected static <K> Map<String, Object> _getTableData(TableInfo table, K from, boolean insert)
    {
        Map<String, Object> fields;
        //noinspection unchecked
        ObjectFactory<K> f = ObjectFactory.Registry.getFactory((Class<K>)from.getClass());
        if (null == f)
            throw new IllegalArgumentException("Could not find a matching object factory.");
        fields = f.toMap(from, null);
        return _getTableData(table, fields, insert);
    }

    protected static Map<String, Object> _getTableData(TableInfo table, Map<String, Object> fields, boolean insert)
    {
        if (!(fields instanceof CaseInsensitiveHashMap))
            fields = new CaseInsensitiveHashMap<>(fields);

        // special rename case
        if (fields.containsKey("containerId"))
            fields.put("container", fields.get("containerId"));

        List<ColumnInfo> columns = table.getColumns();
        Map<String, Object> m = new CaseInsensitiveHashMap<>(columns.size() * 2);

        for (ColumnInfo column : columns)
        {
            String key = column.getName();

            if (!insert && column.isReadOnly() || column.isAutoIncrement() || column.isVersionColumn())
                continue;

            if (!fields.containsKey(key))
            {
                if (Character.isUpperCase(key.charAt(0)))
                {
                    key = Character.toLowerCase(key.charAt(0)) + key.substring(1);
                    if (!fields.containsKey(key))
                        continue;
                }
                else
                    continue;
            }

            Object v = fields.get(key);
            if (v instanceof String s)
                v = _trimRight(s);
            m.put(column.getName(), v);
        }
        return m;
    }

    static String _trimRight(String s)
    {
        if (null == s) return "";
        return StringUtils.stripEnd(s, "\t\r\n ");
    }

    protected static void _insertSpecialFields(User user, TableInfo table, Map<String, Object> fields, NowTimestamp now)
    {
        ColumnInfo col = table.getColumn(OWNER_COLUMN_NAME);
        if (null != col && null != user)
            fields.put(OWNER_COLUMN_NAME, user.getUserId());
        col = table.getColumn(CREATED_BY_COLUMN_NAME);
        if (null != col && null != user)
            fields.put(CREATED_BY_COLUMN_NAME, user.getUserId());
        col = table.getColumn(CREATED_COLUMN_NAME);
        if (null != col)
        {
            Date dateCreated = (Date)fields.get(CREATED_COLUMN_NAME);
            if (null == dateCreated || 0 == dateCreated.getTime())
                fields.put(CREATED_COLUMN_NAME, now);
        }
        col = table.getColumn(ENTITY_ID_COLUMN_NAME);
        if (col != null && fields.get(ENTITY_ID_COLUMN_NAME) == null)
            fields.put(ENTITY_ID_COLUMN_NAME, GUID.makeGUID());
    }

    protected static void _copyInsertSpecialFields(Object returnObject, Map<String, Object> fields)
    {
        if (returnObject == fields)
            return;

        // make sure that any GUID generated in this routine is stored in the returned object
        if (fields.containsKey(ENTITY_ID_COLUMN_NAME))
            _setProperty(returnObject, ENTITY_ID_COLUMN_NAME, fields.get(ENTITY_ID_COLUMN_NAME));
        if (fields.containsKey(OWNER_COLUMN_NAME))
            _setProperty(returnObject, OWNER_COLUMN_NAME, fields.get(OWNER_COLUMN_NAME));
        if (fields.containsKey(CREATED_COLUMN_NAME))
            _setProperty(returnObject, CREATED_COLUMN_NAME, fields.get(CREATED_COLUMN_NAME));
        if (fields.containsKey(CREATED_BY_COLUMN_NAME))
            _setProperty(returnObject, CREATED_BY_COLUMN_NAME, fields.get(CREATED_BY_COLUMN_NAME));
    }

    protected static void _updateSpecialFields(@Nullable User user, TableInfo table, Map<String, Object> fields, NowTimestamp now)
    {
        ColumnInfo colModifiedBy = table.getColumn(MODIFIED_BY_COLUMN_NAME);
        if (null != colModifiedBy && null != user)
            fields.put(colModifiedBy.getName(), user.getUserId());

        ColumnInfo colModified = table.getColumn(MODIFIED_COLUMN_NAME);
        if (null != colModified)
            fields.put(colModified.getName(), now);

        ColumnInfo colVersion = table.getVersionColumn();
        if (null != colVersion && colVersion != colModified && colVersion.getJdbcType() == JdbcType.TIMESTAMP)
            fields.put(colVersion.getName(), now);
    }

    protected static void _copyUpdateSpecialFields(TableInfo table, Object returnObject, Map<String, Object> fields)
    {
        if (returnObject == fields)
            return;

        if (fields.containsKey(MODIFIED_BY_COLUMN_NAME))
            _setProperty(returnObject, MODIFIED_BY_COLUMN_NAME, fields.get(MODIFIED_BY_COLUMN_NAME));

        if (fields.containsKey(MODIFIED_COLUMN_NAME))
            _setProperty(returnObject, MODIFIED_COLUMN_NAME, fields.get(MODIFIED_COLUMN_NAME));

        ColumnInfo colModified = table.getColumn(MODIFIED_COLUMN_NAME);
        ColumnInfo colVersion = table.getVersionColumn();
        if (null != colVersion && colVersion != colModified && colVersion.getJdbcType() == JdbcType.TIMESTAMP)
            _setProperty(returnObject, colVersion.getName(), fields.get(colVersion.getName()));
    }

    static private void _setProperty(Object fields, String propName, Object value)
    {
        // Replace marker NowTimestamp instances with Timestamp for default serialization
        if (value instanceof NowTimestamp now)
            value = new java.sql.Timestamp(now.getTime());

        if (fields instanceof Map)
        {
            ((Map<String, Object>) fields).put(propName, value);
        }
        else
        {
            if (Character.isUpperCase(propName.charAt(0)))
                propName = Character.toLowerCase(propName.charAt(0)) + propName.substring(1);
            try
            {
                if (PropertyUtils.isWriteable(fields, propName))
                {
                    // use BeanUtils instead of PropertyUtils because BeanUtils will use a registered Converter
                    BeanUtils.copyProperty(fields, propName, value);
                }
                // UNDONE: horrible postgres hack..., not general fix
                else if (propName.endsWith("id"))
                {
                    propName = propName.substring(0,propName.length()-2) + "Id";
                    if (PropertyUtils.isWriteable(fields, propName))
                        BeanUtils.copyProperty(fields, propName, value);
                }
            }
            catch (Exception x)
            {
                throw new RuntimeException(x);
            }
        }
    }


    /**
     * @return a new Map&lt;String, Object&gt; if fieldsIn is a Map, otherwise returns modified version of fieldsIn.
     * The row is not re-queried after the insert, so the returned value carries only the fields passed in plus the
     * generated autoincrement key and special fields (e.g. created/createdBy) -- not any other database-computed
     * values such as defaults, computed columns, or trigger-modified data. A record can be provided to insert, but
     * it's returned unchanged, since it's immutable and can't receive the generated key or special fields.
     * @throws RuntimeValidationException if there is a problem with the data that's detected before we try to actually do the insert
     * @throws RuntimeSQLException if there is a problem communicating with the database or there is a constraint violation or similar error
     */
    public static <K> K insert(@Nullable User user, TableInfo table, K fieldsIn)
    {
        assert assertInDb(table);

        SQLFragment insertSQL = new SQLFragment();
        SQLFragment columnSQL = new SQLFragment();
        SQLFragment valueSQL = new SQLFragment();
        ColumnInfo autoIncColumn = null;
        String comma = "";

        //noinspection unchecked
        Map<String, Object> fields = fieldsIn instanceof Map ?
                _getTableData(table, (Map<String, Object>)fieldsIn, true) :
                _getTableData(table, fieldsIn, true);
        NowTimestamp now = new NowTimestamp();
        _insertSpecialFields(user, table, fields, now);
        _updateSpecialFields(user, table, fields, now);

        List<ColumnInfo> columns = table.getColumns();

        for (ColumnInfo column : columns)
        {
            // note  if(version) is not an no-op, it protects the isautoinc test from version columns implemented using sequences
            if (column.isVersionColumn())
            {
            }
            else if (column.isAutoIncrement())
                autoIncColumn = column;

            if (!fields.containsKey(column.getName()))
                continue;

            Object value = fields.get(column.getName());

            if (!column.isAutoIncrement() &&
                    column.isRequired() &&
                    (null == value || value instanceof String s && s.isEmpty()) &&
                    !Table.AUTOPOPULATED_COLUMN_NAMES.contains(column.getName()) &&
                    column.getJdbcDefaultValue() == null)
            {
                throw new RuntimeValidationException("A value is required for field '" + column.getName() + "'", column.getName());
            }

            columnSQL.append(comma);
            columnSQL.appendIdentifier(column.getSelectIdentifier());
            valueSQL.append(comma);
            if (null == value || value instanceof String s && s.isEmpty())
                valueSQL.unsafeAppend("NULL");
            else if (value instanceof NowTimestamp ts)
                valueSQL.appendValue(ts);
            else
            {
                // Validate the value
                List<ColumnValidator> validators = ColumnValidators.create(column, null);
                for (ColumnValidator v : validators)
                {
                    String msg = v.validate(1, value);
                    if (msg != null)
                        throw new RuntimeValidationException(msg, column.getName()); // CONSIDER: would prefer throwing ValidationException instead, but it's not a RuntimeException
                }

                valueSQL.append("?");
                if (value instanceof Parameter.JdbcParameterValue)
                    valueSQL.add(value);
                else
                    valueSQL.add(new Parameter.TypedValue(value, column.getJdbcType()));
            }
            comma = ", ";
        }

        if (comma.isEmpty())
        {
            // NO COLUMNS TO INSERT
            throw new IllegalArgumentException("Table.insert called with no column data. table=" + table + " object=" + fieldsIn);
        }

        insertSQL.unsafeAppend("INSERT INTO ");
        insertSQL.append(table.getSelectName());
        insertSQL.unsafeAppend("\n\t(");
        insertSQL.append(columnSQL);
        insertSQL.unsafeAppend(")\n\t");
        insertSQL.unsafeAppend("VALUES (");
        insertSQL.append(valueSQL);
        insertSQL.append(")");

        // CONSIDER reselect version column
        if (null != autoIncColumn)
            table.getSqlDialect().addReselect(insertSQL, autoIncColumn, null);

        // If Map was handed in, then we hand back a Map
        // UNDONE: use Table.select() to reselect and return new Object

        //noinspection unchecked
        K returnObject = (K) (fieldsIn instanceof Map && !(fieldsIn instanceof BoundMap) ? fields : fieldsIn);

        DbSchema schema = table.getSchema();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try (Parameter.ParameterList jdbcParameters = new Parameter.ParameterList())
        {
            conn = schema.getScope().getConnection();
            stmt = prepareStatement(conn, insertSQL.getSQL(), insertSQL.getParams(), jdbcParameters);

            if (null == autoIncColumn)
            {
                stmt.execute();
            }
            else
            {
                rs = table.getSqlDialect().executeWithResults(stmt);
                rs.next();
                _setProperty(returnObject, autoIncColumn.getName(), rs.getLong(1));
            }

            _copyInsertSpecialFields(returnObject, fields);
            _copyUpdateSpecialFields(table, returnObject, fields);
        }
        catch (SQLException e)
        {
            logException(insertSQL, conn, e, Level.WARN);
            throw new RuntimeSQLException(e);
        }
        finally
        {
            doClose(rs, stmt, conn, schema.getScope());
        }

        return returnObject;
    }

    public static <K> K update(@Nullable User user, TableInfo table, K fieldsIn, Object pkVals)
    {
        return update(user, table, fieldsIn, pkVals, null, Level.WARN);
    }

    public static <K> K update(@Nullable User user, TableInfo table, K fieldsIn, Object pkVals, @Nullable Filter filter, Level level)
    {
        return update(user, table, fieldsIn, null, pkVals, filter, level);
    }

    /**
     * NOTE: this does not enforce that keyColumn is an appropriately unique column!
     * @return fieldsIn itself (mutated in place) if it's a bean, or a new Map&lt;String, Object&gt; if fieldsIn is a Map.
     * The row is not re-queried after the update, so the returned value reflects only the fields as passed in --
     * not database-computed values such as version-column update expressions, defaults, or trigger-modified data.
     */
    public static <K> K update(@Nullable User user, TableInfo table, K fieldsIn, @Nullable ColumnInfo keyColumn, Object pkVals, @Nullable Filter filter, Level level)
    {
        assert assertInDb(table);
        assert null != pkVals;

        SQLFragment setSQL = new SQLFragment();
        SQLFragment whereSQL = new SQLFragment();
        String comma = "";

        // UNDONE -- rowVersion
        List<ColumnInfo> columnPK;
        if (null != keyColumn)
        {
            columnPK = Collections.singletonList(keyColumn);
        }
        else
        {
            columnPK = table.getPkColumns();
            if (columnPK.isEmpty())
                columnPK = table.getAlternateKeyColumns();
        }

        // Name-value pairs for the PK columns for this row
        Map<String, Object> keys = new CaseInsensitiveHashMap<>();

        if (columnPK.size() == 1 && !pkVals.getClass().isArray())
            keys.put(columnPK.getFirst().getName(), pkVals);
        else if (pkVals instanceof Map)
            keys.putAll((Map<? extends String, ?>)pkVals);
        else
        {
            Object[] pkValueArray = (Object[]) pkVals;
            if (pkValueArray.length != columnPK.size())
            {
                throw new IllegalArgumentException("Expected to get " + columnPK.size() + " key values, but got " + pkValueArray.length);
            }
            // Assume that the key values are in the same order as the key columns returned from getPkColumns()
            for (int i = 0; i < columnPK.size(); i++)
            {
                keys.put(columnPK.get(i).getName(), pkValueArray[i]);
            }
        }

        String whereAND = "WHERE ";

        if (null != filter)
        {
            SQLFragment fragment = filter.getSQLFragment(table.getSqlDialect(), null, createMetaDataNameMap(table));
            whereSQL.append(fragment);
            whereAND = " AND ";
        }

        for (ColumnInfo col : columnPK)
        {
            whereSQL.append(whereAND);
            whereSQL.appendIdentifier(col.getSelectIdentifier());
            whereSQL.unsafeAppend("=?");
            whereSQL.add(keys.get(col.getName()), col.getJdbcType());
            whereAND = " AND ";
        }

        //noinspection unchecked
        Map<String, Object> fields = fieldsIn instanceof Map ?
            _getTableData(table, (Map<String,Object>)fieldsIn, true) :
            _getTableData(table, fieldsIn, true);
        _updateSpecialFields(user, table, fields, new NowTimestamp());

        List<ColumnInfo> columns = table.getColumns();
        ColumnInfo colModified = table.getColumn(MODIFIED_COLUMN_NAME);

        // Issue 22873: user could attempt to updateRow with no columns in the table; avoid SQL error and log warning
        boolean hasFieldsToSet = false;
        for (ColumnInfo column : columns)
        {
            if (column.isVersionColumn() && column != colModified)
            {
                SQLFragment expr = column.getVersionUpdateExpression();
                if (null != expr)
                {
                    setSQL.append(comma);
                    setSQL.appendIdentifier(column.getSelectIdentifier());
                    setSQL.unsafeAppend("=");
                    setSQL.append(expr);
                    comma = ", ";
                    hasFieldsToSet = true;
                    continue;
                }
            }

            if (!fields.containsKey(column.getName()))
            {
                // CONSIDER column.isImportHash()
                if (DataIntegrationService.Columns.TransformImportHash.getColumnName().equals(column.getName()))
                {
                    setSQL.append(comma);
                    setSQL.appendIdentifier(column.getSelectIdentifier());
                    setSQL.unsafeAppend("=NULL");
                    comma = ", ";
                }
                continue;
            }

            Object value = fields.get(column.getName());
            setSQL.append(comma);
            setSQL.appendIdentifier(column.getSelectIdentifier());
            setSQL.unsafeAppend("=");

            if (null == value || value instanceof String s && s.isEmpty())
                setSQL.unsafeAppend("NULL");
            else if (value instanceof NowTimestamp ts)
                setSQL.appendValue(ts);
            else
            {
                // Validate the value
                List<ColumnValidator> validators = ColumnValidators.create(column, null);
                for (ColumnValidator v : validators)
                {
                    String msg = v.validate(1, value);
                    if (msg != null)
                        throw new RuntimeValidationException(msg, column.getName()); // CONSIDER: would prefer throwing ValidationException instead, but it's not a RuntimeException
                }

                setSQL.unsafeAppend("?");
                if (value instanceof Parameter.JdbcParameterValue)
                    setSQL.add(value);
                else
                    setSQL.add(new Parameter.TypedValue(value, column.getJdbcType()));
            }

            comma = ", ";
            hasFieldsToSet = true;
        }

        if (hasFieldsToSet)
        {
            // UNDONE: reselect
            SQLFragment updateSQL = new SQLFragment()
                    .unsafeAppend("UPDATE ").append(table.getSQLName()).unsafeAppend("\n\t")
                    .unsafeAppend("SET ").append(setSQL).unsafeAppend("\n\t")
                    .append(whereSQL);

            try
            {
                int count = new SqlExecutor(table.getSchema()).execute(updateSQL);

                // check for concurrency problem
                if (count == 0)
                {
                    throw OptimisticConflictException.create(ERROR_DELETED);
                }

                _copyUpdateSpecialFields(table, fieldsIn, fields);
            }
            catch (OptimisticConflictException e)
            {
                logException(updateSQL, null, e.getSQLException(), level);
                throw (e);
            }
        }
        else
            _log.warn("Attempt to update table '{}' with no valid fields.", table.getName());

        return (fieldsIn instanceof Map && !(fieldsIn instanceof BoundMap)) ? (K)fields : fieldsIn;
    }

    /**
     * Updates the container of specified rows in the provided database table. Optionally, the modification timestamp
     * and the user who made the modification can also be updated if specified.
     *
     * @param table           The table where the container update should be applied.
     * @param idField         The name of the identifier field used to locate the rows to update.
     * @param ids             A collection of identifier values specifying the rows to be updated.
     * @param targetContainer The target container to set for the specified rows.
     * @param user            The user performing the update. If null, modified/modifiedBy details are not updated.
     * @param withModified    If true, updates the modified timestamp and the user who made the modification.
     * @return The number of rows updated in the table.
     */
    public static int updateContainer(TableInfo table, String idField, Collection<?> ids, Container targetContainer, @Nullable User user, boolean withModified)
    {
        assert assertInDb(table);
        ColumnInfo idColumn = table.getColumn(idField);
        if (idColumn == null)
            throw new IllegalArgumentException("Table " + fullTableName(table) + " has no column named '" + idField + "'.");

        if (ids == null || ids.isEmpty())
            return 0;

        SimpleFilter filter = new SimpleFilter();
        filter.addInClause(idColumn.getFieldKey(), ids);

        return updateContainer(table, targetContainer, filter, user, withModified);
    }

    public static int updateContainer(TableInfo table, Container targetContainer, @NotNull SimpleFilter filter, @Nullable User user, boolean withModified)
    {
        assert assertInDb(table);
        ColumnInfo containerColumn = table.getColumn(SpecialColumn.Container.name());
        if (containerColumn == null)
            throw new IllegalArgumentException("Table " + fullTableName(table) + " has no column named '" + SpecialColumn.Container.name() + "'.");

        SQLFragment dataUpdate = new SQLFragment("UPDATE ").append(table)
                .unsafeAppend(" SET  ").appendIdentifier(containerColumn.getSelectIdentifier())
                .unsafeAppend(" = ")
                .appendValue(targetContainer);

        if (withModified)
        {
            assert user != null : "User must be specified when updating modified/modifiedBy details.";
            ColumnInfo colModified = table.getColumn(MODIFIED_COLUMN_NAME);
            if (null != colModified)
            {
                dataUpdate.unsafeAppend(", ").appendIdentifier(colModified.getSelectIdentifier())
                        .unsafeAppend(" = ")
                        .appendNowTimestamp();
            }

            ColumnInfo colModifiedBy = table.getColumn(MODIFIED_BY_COLUMN_NAME);
            if (null != colModifiedBy)
            {
                dataUpdate.unsafeAppend(", ").appendIdentifier(colModifiedBy.getSelectIdentifier())
                        .unsafeAppend(" = ")
                        .appendValue(user.getUserId());
            }
        }

        SQLFragment whereClause = filter.getSQLFragment(table.getSqlDialect(), null, createMetaDataNameMap(table));
        dataUpdate.unsafeAppend("\n").append(whereClause);

        return new SqlExecutor(table.getSchema()).execute(dataUpdate);
    }

    public static void delete(TableInfo table, Object rowId)
    {
        List<ColumnInfo> columnPK = table.getPkColumns();
        Object[] pkVals;

        assert columnPK.size() == 1 || ((Object[]) rowId).length == columnPK.size();

        if (columnPK.size() == 1 && !rowId.getClass().isArray())
            pkVals = new Object[]{rowId};
        else
            pkVals = (Object[]) rowId;

        SimpleFilter filter = new SimpleFilter();
        for (int i = 0; i < pkVals.length; i++)
            filter.addCondition(columnPK.get(i), pkVals[i]);

        // UNDONE -- rowVersion
        if (delete(table, filter) == 0)
        {
            throw OptimisticConflictException.create(ERROR_DELETED);
        }
    }

    public static int delete(TableInfo table)
    {
        assert assertInDb(table);
        SqlExecutor sqlExecutor = new SqlExecutor(table.getSchema());

        return sqlExecutor.execute("DELETE FROM " + table.getSelectName());
    }

    public static int delete(TableInfo table, Filter filter)
    {
        assert assertInDb(table);
        SQLFragment where = filter.getSQLFragment(table.getSqlDialect(), null, createMetaDataNameMap(table));
        SQLFragment deleteSQL = new SQLFragment("DELETE FROM ").append(table).unsafeAppend("\n\t").append(where);

        return new SqlExecutor(table.getSchema()).execute(deleteSQL);
    }

    public static void truncate(TableInfo table)
    {
        assert assertInDb(table);
        SqlExecutor sqlExecutor = new SqlExecutor(table.getSchema());
        sqlExecutor.execute(table.getSqlDialect().getTruncateSql(table.getSelectName()));
    }

    public static SQLFragment getSelectSQL(TableInfo table, @Nullable Collection<ColumnInfo> columns, @Nullable Filter filter, @Nullable Sort sort)
    {
        return QueryService.get().getSelectBuilder(table).columns(columns).filter(filter).sort(sort).buildSqlFragment();
    }

    public static void ensureRequiredColumns(TableInfo table, Map<FieldKey, ColumnInfo> cols, @Nullable Filter filter, @Nullable Sort sort, @Nullable List<Aggregate> aggregates)
    {
        List<ColumnInfo> allColumns = table.getColumns();
        Set<FieldKey> requiredColumns = new HashSet<>();

        if (null != filter)
            requiredColumns.addAll(filter.getWhereParamFieldKeys());

        if (null != sort)
        {
            requiredColumns.addAll(sort.getRequiredColumns(cols));
        }

        if (null != aggregates)
        {
            for (Aggregate agg : aggregates)
                requiredColumns.add(agg.getFieldKey());
        }

        // TODO: Ensure pk, filter & where columns in cases where caller is naive

        for (ColumnInfo column : allColumns)
        {
            if (cols.containsKey(column.getFieldKey()))
                continue;
            if (requiredColumns.contains(column.getFieldKey()) || requiredColumns.contains(new FieldKey(null,column.getAlias().getId())) || requiredColumns.contains(new FieldKey(null,column.getName())))
                cols.put(column.getFieldKey(), column);
            else if (column.isKeyField())
                cols.put(column.getFieldKey(), column);
            else if (column.isVersionColumn())
                cols.put(column.getFieldKey(), column);
        }
    }


    public static void snapshot(TableInfo tinfo, String tableName)
    {
        SQLFragment sqlSelect = getSelectSQL(tinfo, null, null, null);
        SQLFragment sqlSelectInto = new SQLFragment();
        sqlSelectInto.unsafeAppend("SELECT * INTO ").append(tableName).unsafeAppend(" FROM (");
        sqlSelectInto.append(sqlSelect);
        sqlSelectInto.unsafeAppend(") _from_");

        new SqlExecutor(tinfo.getSchema()).execute(sqlSelectInto);
    }

    private static void _logQuery(Level level, @Nullable SQLFragment sqlFragment, @Nullable Connection conn)
    {
        if (!_log.isEnabled(level) || null == sqlFragment)
            return;

        String sql = sqlFragment.getSQL();
        Object[] parameters = sqlFragment.getParamsArray();

        StringBuilder logEntry = new StringBuilder(sql.length() * 2);
        logEntry.append("SQL ");

        Integer sid = null;
        if (conn instanceof ConnectionWrapper)
            sid = ((ConnectionWrapper)conn).getSPID();
        if (sid != null)
            logEntry.append(" [").append(sid).append("]");

        String[] lines = sql.split("\n");
        for (String line : lines)
            logEntry.append("\n    ").append(line);

        for (int i = 0; null != parameters && i < parameters.length; i++)
            logEntry.append("\n    ?[").append(i + 1).append("] ").append(parameters[i]);

        logEntry.append("\n");
        _appendTableStackTrace(logEntry, 5);
        _log.log(level, logEntry);
    }


    static void _appendTableStackTrace(StringBuilder sb, int count)
    {
        StackTraceElement[] ste = MiniProfiler.getTroubleshootingStackTrace();
        if (ste != null)
        {
            int i = 0;
            for (; i < ste.length; i++)
            {
                String line = ste[i].toString();
                if (!line.startsWith(Table.class.getName() + "."))
                    break;
            }
            int last = Math.min(ste.length, i + count);
            for (; i < last; i++)
            {
                String line = ste[i].toString();
                if (line.startsWith(ExceptionUtil.SERVLET_SERVICE_FRAME_PREFIX))
                    break;
                sb.append("\n\t").append(line);
            }
        }
    }

    public static class TestCase extends Assert
    {
        private static final CoreSchema _core = CoreSchema.getInstance();

        public static class Principal
        {
            private int _userId;
            private String _ownerId;
            private String _type;
            private String _name;


            public int getUserId()
            {
                return _userId;
            }


            public void setUserId(int userId)
            {
                _userId = userId;
            }


            public String getOwnerId()
            {
                return _ownerId;
            }


            public void setOwnerId(String ownerId)
            {
                _ownerId = ownerId;
            }


            public String getType()
            {
                return _type;
            }


            public void setType(String type)
            {
                _type = type;
            }


            public String getName()
            {
                return _name;
            }


            public void setName(String name)
            {
                _name = name;
            }
        }


        @Test
        public void testSelect() throws SQLException
        {
            TableInfo tinfo = _core.getTableInfoPrincipals();

            //noinspection EmptyTryBlock,UnusedDeclaration
            try (ResultSet rs = new TableSelector(tinfo).getResultSet()){}

            Map<?, ?>[] maps = new TableSelector(tinfo).getMapArray();
            assertNotNull(maps);

            Principal[] principals = new TableSelector(tinfo).getArray(Principal.class);
            assertNotNull(principals);
            assertTrue(principals.length > 0);
            assertTrue(principals[0]._userId != 0);
            assertNotNull(principals[0]._name);
            assertEquals(maps.length, principals.length);
        }


        @Test
        public void testMaxRows() throws SQLException
        {
            TableInfo tinfo = _core.getTableInfoPrincipals();

            int maxRows;

            try (Results rsAll = new TableSelector(tinfo).getResults())
            {
                rsAll.last();
                maxRows = rsAll.getRow();
                assertTrue(rsAll.isComplete());
            }

            maxRows -= 2;

            try (Results results = new TableSelector(tinfo).setMaxRows(maxRows).getResults())
            {
                results.last();
                int row = results.getRow();
                assertEquals(row, maxRows);
                assertFalse(results.isComplete());
            }
        }


        enum MyEnum
        {
            FRED, BARNEY, WILMA, BETTY
        }

        @Test
        public void testParameter() throws Exception
        {
            DbSchema core = CoreSchema.getInstance().getSchema();
            SqlDialect dialect = core.getScope().getSqlDialect();
            
            String name = dialect.getTempTablePrefix() + "_" + GUID.makeHash();

            try (Connection conn = core.getScope().getConnection())
            {
                assertNotNull(conn);

                try
                {
                    try (PreparedStatement stmt = conn.prepareStatement("CREATE " + dialect.getTempTableKeyword() + " TABLE " + name +
                            "(s VARCHAR(36), d " + dialect.getSqlTypeName(JdbcType.TIMESTAMP) + ")"))
                    {
                        stmt.execute();
                    }

                    String sql = "INSERT INTO " + name + " VALUES (?, ?)";

                    try (PreparedStatement stmt = conn.prepareStatement(sql))
                    {
                        Parameter s = new Parameter(stmt, 1);
                        Parameter d = new Parameter(stmt, 2, JdbcType.TIMESTAMP);
                        s.setValue(4);
                        d.setValue(GregorianCalendar.getInstance());
                        stmt.execute();

                        s.setValue(1.234);
                        d.setValue(new java.sql.Timestamp(System.currentTimeMillis()));
                        stmt.execute();

                        s.setValue("string");
                        d.setValue(null);
                        stmt.execute();

                        s.setValue(ContainerManager.getRoot());
                        d.setValue(new java.util.Date());
                        stmt.execute();

                        s.setValue(MyEnum.BETTY);
                        d.setValue(null);
                        stmt.execute();
                    }
                }
                finally
                {
                    try (PreparedStatement cleanup = conn.prepareStatement("DROP TABLE " + name))
                    {
                        cleanup.execute();
                    }
                }
            }
        }


        @Test
        public void testAggregates()
        {
            TableInfo tinfo = CoreSchema.getInstance().getTableInfoContainers();
            List<Aggregate> aggregates = new LinkedList<>();

            // Test no aggregates case
            Map<String, List<Aggregate.Result>> aggregateMap = new TableSelector(tinfo, Collections.emptyList(), null, null).getAggregates(aggregates);
            assertTrue(aggregateMap.isEmpty());

            aggregates.add(Aggregate.createCountStar());
            aggregates.add(new Aggregate(tinfo.getColumn("RowId"), Aggregate.BaseType.COUNT));
            aggregates.add(new Aggregate(tinfo.getColumn("RowId"), Aggregate.BaseType.SUM));
            aggregates.add(new Aggregate(tinfo.getColumn("RowId"), Aggregate.BaseType.MEAN));
            aggregates.add(new Aggregate(tinfo.getColumn("RowId"), Aggregate.BaseType.MIN));
            aggregates.add(new Aggregate(tinfo.getColumn("RowId"), Aggregate.BaseType.MAX));
            aggregates.add(new Aggregate(tinfo.getColumn("Parent"), Aggregate.BaseType.COUNT));
            aggregates.add(new Aggregate(tinfo.getColumn("Parent").getFieldKey(), Aggregate.BaseType.COUNT, null, true));
            aggregates.add(new Aggregate(FieldKey.fromParts("Parent", "Parent"), Aggregate.BaseType.COUNT));
            aggregates.add(new Aggregate(tinfo.getColumn("SortOrder"), Aggregate.BaseType.SUM));
            aggregates.add(new Aggregate(tinfo.getColumn("SortOrder").getFieldKey(), Aggregate.BaseType.SUM, null, true));
            aggregates.add(new Aggregate(tinfo.getColumn(CREATED_BY_COLUMN_NAME), Aggregate.BaseType.COUNT));
            aggregates.add(new Aggregate(tinfo.getColumn(CREATED_COLUMN_NAME), Aggregate.BaseType.MIN));
            aggregates.add(new Aggregate(tinfo.getColumn("Name"), Aggregate.BaseType.MIN));

            aggregateMap = new TableSelector(tinfo, Collections.emptyList(), null, null).getAggregates(aggregates);

            String sql =
                """
                SELECT CAST(COUNT(*) AS BIGINT) AS CountStar,
                CAST(COUNT(C.RowId) AS BIGINT) AS CountRowId,
                CAST(SUM(C.RowId) AS BIGINT) AS SumRowId,
                AVG(C.RowId) AS AvgRowId,
                CAST(MIN(C.RowId) AS BIGINT) AS MinRowId,
                CAST(MAX(C.RowId) AS BIGINT) AS MaxRowId,
                CAST(COUNT(C.Parent) AS BIGINT) AS CountParent,
                CAST(COUNT(DISTINCT C.Parent) AS BIGINT) AS CountDistinctParent,
                CAST(COUNT(P.Parent) AS BIGINT) AS CountParent_fs_Parent,
                CAST(SUM(C.SortOrder) AS BIGINT) AS SumSortOrder,
                CAST(SUM(DISTINCT C.SortOrder) AS BIGINT) AS SumDistinctSortOrder,
                CAST(COUNT(C.CreatedBy) AS BIGINT) AS CountCreatedBy,
                MIN(C.Created) AS MinCreated,
                MIN(C.Name) AS MinName
                FROM core.Containers C
                LEFT OUTER JOIN core.Containers P ON C.parent = P.entityid
                """;
            Map<String, Object> expected = new SqlSelector(tinfo.getSchema(), sql).getMap();

            verifyAggregates(expected, aggregateMap);
        }


        private void verifyAggregates(Map<String, Object> expected, Map<String, List<Aggregate.Result>> aggregateMap)
        {
            verifyAggregate(expected.get("CountStar"), aggregateMap.get("*").getFirst().getValue());

            verifyAggregate(expected.get("CountRowId"), aggregateMap.get("RowId").get(0).getValue());
            verifyAggregate(expected.get("SumRowId"), aggregateMap.get("RowId").get(1).getValue());
            verifyAggregate(expected.get("AvgRowId"), aggregateMap.get("RowId").get(2).getValue());
            verifyAggregate(expected.get("MinRowId"), aggregateMap.get("RowId").get(3).getValue());
            verifyAggregate(expected.get("MaxRowId"), aggregateMap.get("RowId").get(4).getValue());

            verifyAggregate(expected.get("CountParent"), aggregateMap.get("Parent").get(0).getValue());
            verifyAggregate(expected.get("CountDistinctParent"), aggregateMap.get("Parent").get(1).getValue());

            verifyAggregate(expected.get("CountParent_fs_Parent"), aggregateMap.get("Parent/Parent").getFirst().getValue());

            verifyAggregate(expected.get("SumSortOrder"), aggregateMap.get("SortOrder").get(0).getValue());
            verifyAggregate(expected.get("SumDistinctSortOrder"), aggregateMap.get("SortOrder").get(1).getValue());

            verifyAggregate(expected.get("CountCreatedBy"), aggregateMap.get(CREATED_BY_COLUMN_NAME).getFirst().getValue());
            verifyAggregate(expected.get("MinCreated"), aggregateMap.get(CREATED_COLUMN_NAME).getFirst().getValue());
            verifyAggregate(expected.get("MinName"), aggregateMap.get("Name").getFirst().getValue());
        }


        private void verifyAggregate(Object expected, Object actual)
        {
            // Address AVG on SQL Server... expected query returns Integer type but aggregate converts to Long
            if (expected.getClass() != actual.getClass())
                assertEquals(((Number)expected).longValue(), ((Number)actual).longValue());
            else
                assertEquals(expected, actual);
        }
    }


    static public Map<FieldKey, ColumnInfo> createColumnMap(@Nullable TableInfo table, @Nullable Collection<ColumnInfo> columns)
    {
        Map<FieldKey, ColumnInfo> ret = new HashMap<>();
        if (columns != null)
        {
            for (ColumnInfo column : columns)
            {
                ret.put(column.getFieldKey(), column);
            }
        }
        if (table != null)
        {
            for (String name : table.getColumnNameSet())
            {
                FieldKey f = FieldKey.fromParts(name);
                if (ret.containsKey(f))
                    continue;
                ColumnInfo column = table.getColumn(name);
                if (column != null)
                {
                    ret.put(column.getFieldKey(), column);
                }
            }
        }
        return ret;
    }

    /**
     *  Create a map that can be passed into Filter.getSQLFragment() that create a SQL fragment using getMetaDataName() instead of
     * getAlias().
     */
    private static Map<FieldKey, ColumnInfo> createMetaDataNameMap(TableInfo table)
    {
        Map<FieldKey, ColumnInfo> ret = new HashMap<>();
        for (var column : table.getColumns())
        {
            var wrapped = WrappedColumnInfo.wrap(column);
            wrapped.setAlias(column.getMetaDataIdentifier());
            ret.put(column.getFieldKey(), wrapped);
        }
        return ret;
    }

    public static boolean checkAllColumns(TableInfo table, Collection<ColumnInfo> columns, String prefix)
    {
        return checkAllColumns(table, columns, prefix, false);
    }

    public static boolean checkAllColumns(TableInfo table, Collection<ColumnInfo> columns, String prefix, boolean enforceUnique)
    {
        int bad = 0;

        Map<String, ColumnInfo> mapAlias = new HashMap<>(columns.size()*2);
        ColumnInfo prev;

        for (ColumnInfo column : columns)
        {
            if (!checkColumn(table, column, prefix))
                bad++;
            if (enforceUnique && !(column instanceof AliasedColumn) && null != (prev = mapAlias.put(column.getAlias().getId(), column)) && prev != column)
            {
                _log.warn("{}: Column {} from table: {} is mapped to the same alias ({}) as column {} from table: {}", prefix, column, column.getParentTable(), column.getAlias().getId(), prev, prev.getParentTable());
                bad++;
            }
        }

        // Check all the columns in the TableInfo to determine if the TableInfo is corrupt
        for (ColumnInfo column : table.getColumns())
            if (!checkColumn(table, column, "TableInfo.getColumns() for " + prefix))
                bad++;

        // Check the pk columns in the TableInfo
        for (ColumnInfo column : table.getPkColumns())
            if (!checkColumn(table, column, "TableInfo.getPkColumns() for " + prefix))
                bad++;

        return 0 == bad;
    }

    public static boolean checkColumn(TableInfo table, ColumnInfo column, String prefix)
    {
        if (column.getParentTable() != table)
        {
            _log.warn("{}: Column {} is from the wrong table: {} instead of {}", prefix, column, column.getParentTable(), table);
            return false;
        }
        else
        {
            return true;
        }
    }

    public static ParameterMapStatement deleteStatement(Connection conn, TableInfo tableDelete) throws SQLException
    {
        if (!(tableDelete instanceof UpdateableTableInfo updatable))
            throw new IllegalArgumentException();

        if (null == conn)
            conn = tableDelete.getSchema().getScope().getConnection();

        TableInfo table = updatable.getSchemaTableInfo();

        if (!(table instanceof SchemaTableInfo))
            throw new IllegalArgumentException();
        if (null == table.getMetaDataIdentifier())
            throw new IllegalArgumentException();

        SqlDialect d = tableDelete.getSqlDialect();

        List<ColumnInfo> columnPK = table.getPkColumns();
        List<Parameter> paramPK = new ArrayList<>(columnPK.size());
        for (ColumnInfo pk : columnPK)
            paramPK.add(new Parameter(pk.getName(), null, pk.getJdbcType()));
        Parameter paramContainer = new Parameter("container", null, JdbcType.VARCHAR);

        SQLFragment sqlfWhere = new SQLFragment();
        sqlfWhere.unsafeAppend("\nWHERE " );
        String and = "";
        for (int i=0 ; i<columnPK.size() ; i++)
        {
            ColumnInfo pk = columnPK.get(i);
            Parameter p = paramPK.get(i);
            sqlfWhere.append(and); and = " AND ";
            sqlfWhere.appendIdentifier(pk.getSelectIdentifier()).unsafeAppend("=?");
            sqlfWhere.add(p);
        }
        if (null != table.getColumn("container"))
        {
            sqlfWhere.append(and);
            sqlfWhere.unsafeAppend("container=?");
            sqlfWhere.add(paramContainer);
        }

        SQLFragment sqlfDelete = new SQLFragment();
        SQLFragment sqlfDeleteObject = null;
        SQLFragment sqlfDeleteTable;

        //
        // exp.Objects delete
        //

        Domain domain = tableDelete.getDomain();
        DomainKind<?> domainKind = tableDelete.getDomainKind();
        if (null != domain && null != domainKind && StringUtils.isEmpty(domainKind.getStorageSchemaName()))
        {
            if (!d.isPostgreSQL())
                throw new IllegalArgumentException("Domains are only supported for postgres");

            String objectIdColumnName = updatable.getObjectIdColumnName();
            String objectURIColumnName = updatable.getObjectURIColumnName();

            if (null == objectIdColumnName && null == objectURIColumnName)
                throw new IllegalStateException("Join column for exp.Object must be defined");

            SQLFragment sqlfSelectKey = new SQLFragment();
            if (null != objectIdColumnName)
            {
                String keyName = Objects.toString(objectIdColumnName, objectURIColumnName);
                ColumnInfo keyCol = table.getColumn(keyName);
                sqlfSelectKey.unsafeAppend("SELECT ").appendIdentifier(keyCol.getSelectIdentifier());
                sqlfSelectKey.unsafeAppend("FROM ").append(table.getFromSQL("X"));
                sqlfSelectKey.append(sqlfWhere);
            }

            String fn = null==objectIdColumnName ? "deleteObject" : "deleteObjectByid";
            SQLFragment deleteArguments = new SQLFragment("?, ");
            deleteArguments.add(paramContainer);
            deleteArguments.append(sqlfSelectKey);
            sqlfDeleteObject = d.execute(ExperimentService.get().getSchema(), fn, deleteArguments);
        }

        //
        // BASE TABLE delete
        //

        sqlfDeleteTable = new SQLFragment("DELETE ").append(table);
        sqlfDelete.append(sqlfWhere);

        if (null != sqlfDeleteObject)
        {
            sqlfDelete.append(sqlfDeleteObject);
            sqlfDelete.appendEOS();
        }
        sqlfDelete.append(sqlfDeleteTable);

        return new ParameterMapStatement(tableDelete.getSchema().getScope(), conn, sqlfDelete, updatable.remapSchemaColumns());
    }

    private static boolean assertInDb(TableInfo table)
    {
        if (table.getTableType() == DatabaseTableType.NOT_IN_DB)
            throw new AssertionError("Table " + fullTableName(table) + " is not in the physical database.");
        return true;
    }

    private static String fullTableName(TableInfo table)
    {
        return table.getSchema().getName() + "." + table.getName();
    }

    public static class TestDataIterator extends AbstractDataIterator
    {
        private final String guid = GUID.makeGUID();
        private final Date date = new Date();

        // TODO: guid values are ignored, since guidCallable gets used instead
        private final Object[][] _data = new Object[][]
        {
            new Object[] {1, "One", 101, true, date, guid},
            new Object[] {2, "Two", 102, true, date, guid},
            new Object[] {3, "Three", 103, true, date, guid}
        };

        private final static class _ColumnInfo extends BaseColumnInfo
        {
            _ColumnInfo(String name, JdbcType type)
            {
                super(name, type);
                setReadOnly(true);
            }
        }

        private final static ColumnInfo[] _cols = new ColumnInfo[]
        {
            new _ColumnInfo("_row", JdbcType.INTEGER),
            new _ColumnInfo("Text", JdbcType.VARCHAR),
            new _ColumnInfo("IntNotNull", JdbcType.INTEGER),
            new _ColumnInfo("BitNotNull", JdbcType.BOOLEAN),
            new _ColumnInfo("DateTimeNotNull", JdbcType.TIMESTAMP),
            new _ColumnInfo(ENTITY_ID_COLUMN_NAME, JdbcType.VARCHAR)
        };

        int currentRow = -1;

        TestDataIterator()
        {
            super(null);
        }

        @Override
        public int getColumnCount()
        {
            return _cols.length-1;
        }

        @Override
        public ColumnInfo getColumnInfo(int i)
        {
            return _cols[i];
        }

        @Override
        public boolean next()
        {
            return ++currentRow < _data.length;
        }

        @Override
        public Object get(int i)
        {
            return _data[currentRow][i];
        }

        @Override
        public void close()
        {
        }
    }


    public static class DataIteratorTestCase extends Assert
    {
        @Test
        public void test()
        {
            TableInfo testTable = TestSchema.getInstance().getTableInfoTestTable();

            DataIteratorContext dic = new DataIteratorContext();
            TestDataIterator extract = new TestDataIterator();
            SimpleTranslator translate = new SimpleTranslator(extract, dic);
            translate.selectAll();
            translate.addBuiltInColumns(dic, JunitUtil.getTestContainer(), TestContext.get().getUser(), testTable, false);

            DataIteratorBuilder load = new TableInsertDataIteratorBuilder(translate, testTable);
            new Pump(load.getDataIterator(dic), dic).run();

            assertFalse(dic.getErrors().hasErrors());

            new SqlExecutor(testTable.getSchema()).execute("DELETE FROM test.testtable WHERE EntityId = '" + extract.guid + "'");
        }
    }


    public static class LogExceptionTestCase extends Assert
    {
        private static final SQLFragment SELECT = new SQLFragment("SELECT 1");

        // How PgJDBC reports a backend killed mid-query
        private static SQLException backendTerminated()
        {
            return new SQLException("An I/O error occurred while sending to the backend.", "08006", new EOFException());
        }

        @Test
        public void testBackendTerminated()
        {
            assertTrue(SqlDialect.isConnectionLostException(backendTerminated()));
            assertEquals(Level.ERROR, getExceptionLogLevel(SELECT, backendTerminated(), Level.WARN));
            assertEquals(Level.ERROR, getExceptionLogLevel(null, backendTerminated(), Level.WARN));
        }

        @Test
        public void testCallerLogLevel()
        {
            assertNull(getExceptionLogLevel(SELECT, backendTerminated(), Level.OFF));
            assertNull(getExceptionLogLevel(SELECT, new SQLException("relation does not exist", "42P01"), Level.OFF));

            SQLFragment insert = new SQLFragment("INSERT INTO t VALUES (1)");
            SQLException duplicate = new SQLException("duplicate key value violates unique constraint", "23505");
            assertEquals(Level.WARN, getExceptionLogLevel(insert, duplicate, Level.WARN));
            assertNull(getExceptionLogLevel(insert, duplicate, Level.ERROR));
            assertEquals(Level.ERROR, getExceptionLogLevel(SELECT, duplicate, Level.ERROR));
        }

        @Test
        public void testSkipped()
        {
            assertNull(getExceptionLogLevel(SELECT, new SQLException("canceling statement due to user request", "57014"), Level.WARN));
            // EOFException without a connection-loss SQLState is still a client abort
            assertNull(getExceptionLogLevel(SELECT, new SQLException("read failed", "XX000", new EOFException()), Level.WARN));
        }

        @Test
        public void testShutdown()
        {
            assertTrue(SqlDialect.isConnectionLostException(new SQLException("terminating connection due to administrator command", "57P01")));
            assertTrue(SqlDialect.isConnectionLostException(new SQLException("terminating connection because of crash of another server process", "57P02")));
        }

        @Test
        public void testOtherErrors()
        {
            assertFalse(SqlDialect.isConnectionLostException(new SQLException("canceling statement due to user request", "57014")));
            assertFalse(SqlDialect.isConnectionLostException(new SQLException("relation does not exist", "42P01")));
            assertFalse(SqlDialect.isConnectionLostException(new SQLException("no state")));
        }
    }
}