/*
 * Copyright 2004-2023 H2 Group. Multiple-Licensed under the MPL 2.0,
 * and the EPL 1.0 (https://h2database.com/html/license.html).
 * Initial Developer: H2 Group
 */
package org.h2.server.web;

import java.io.ByteArrayOutputStream;
import java.io.PrintStream;
import java.io.PrintWriter;
import java.io.StringReader;
import java.io.StringWriter;
import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Locale;
import java.util.Map;
import java.util.Properties;
import java.util.Random;
import java.util.concurrent.atomic.AtomicReference;

import org.h2.api.ErrorCode;
import org.h2.bnf.Bnf;
import org.h2.bnf.context.DbColumn;
import org.h2.bnf.context.DbContents;
import org.h2.bnf.context.DbSchema;
import org.h2.bnf.context.DbTableOrView;
import org.h2.command.Parser;
import org.h2.engine.Constants;
import org.h2.engine.SysProperties;
import org.h2.jdbc.JdbcException;
import org.h2.message.DbException;
import org.h2.security.SHA256;
import org.h2.tools.Backup;
import org.h2.tools.ChangeFileEncryption;
import org.h2.tools.ConvertTraceFile;
import org.h2.tools.CreateCluster;
import org.h2.tools.DeleteDbFiles;
import org.h2.tools.Recover;
import org.h2.tools.Restore;
import org.h2.tools.RunScript;
import org.h2.tools.Script;
import org.h2.tools.SimpleResultSet;
import org.h2.util.JdbcUtils;
import org.h2.util.NetUtils;
import org.h2.util.NetworkConnectionInfo;
import org.h2.util.Profiler;
import org.h2.util.ScriptReader;
import org.h2.util.SortedProperties;
import org.h2.util.StringUtils;
import org.h2.util.Tool;
import org.h2.util.Utils;
import org.h2.util.Utils10;
import org.h2.value.DataType;

/**
 * For each connection to a session, an object of this class is created.
 * This class is used by the H2 Console.
 */
public class WebApp {

    private static final Comparator<DbTableOrView> SYSTEM_SCHEMA_COMPARATOR = Comparator
            .comparing(DbTableOrView::getName, String.CASE_INSENSITIVE_ORDER);

    /**
     * The web server.
     */
    protected final WebServer server;

    /**
     * The session.
     */
    protected WebSession session;

    /**
     * The session attributes
     */
    protected Properties attributes;

    /**
     * The mime type of the current response.
     */
    protected String mimeType;

    /**
     * Whether the response can be cached.
     */
    protected boolean cache;

    /**
     * Whether to close the connection.
     */
    protected boolean stop;

    /**
     * The language in the HTTP header.
     */
    protected String headerLanguage;

    private Profiler profiler;

    WebApp(WebServer server) {
        this.server = server;
    }

    /**
     * Set the web session and attributes.
     *
     * @param session the session
     * @param attributes the attributes
     */
    void setSession(WebSession session, Properties attributes) {
        this.session = session;
        this.attributes = attributes;
    }

    /**
     * Process an HTTP request.
     *
     * @param file the file that was requested
     * @param networkConnectionInfo the network connection information
     * @return the name of the file to return to the client
     */
    String processRequest(String file, NetworkConnectionInfo networkConnectionInfo) {
        int index = file.lastIndexOf('.');
        String suffix;
        if (index >= 0) {
            suffix = file.substring(index + 1);
        } else {
            suffix = "";
        }
        if ("ico".equals(suffix)) {
            mimeType = "image/x-icon";
            cache = true;
        } else if ("gif".equals(suffix)) {
            mimeType = "image/gif";
            cache = true;
        } else if ("css".equals(suffix)) {
            cache = true;
            mimeType = "text/css";
        } else if ("html".equals(suffix) ||
                "do".equals(suffix) ||
                "jsp".equals(suffix)) {
            cache = false;
            mimeType = "text/html";
            if (session == null) {
                session = server.createNewSession(
                        NetUtils.ipToShortForm(null, networkConnectionInfo.getClientAddr(), false).toString());
                if (!"notAllowed.jsp".equals(file)) {
                    file = "index.do";
                }
            }
        } else if ("js".equals(suffix)) {
            cache = true;
            mimeType = "text/javascript";
        } else {
            cache = true;
            mimeType = "application/octet-stream";
        }
        trace("mimeType=" + mimeType);
        trace(file);
        if (file.endsWith(".do")) {
            file = process(file, networkConnectionInfo);
        } else if (file.endsWith(".jsp")) {
            switch (file) {
            case "admin.jsp":
            case "tools.jsp":
                if (!checkAdmin(file)) {
                    file = process("adminLogin.do", networkConnectionInfo);
                }
            }
        }
        return file;
    }

    private static String getComboBox(String[] elements, String selected) {
        StringBuilder buff = new StringBuilder();
        for (String value : elements) {
            buff.append("<option value=\"").
                append(PageParser.escapeHtmlData(value)).
                append('\"');
            if (value.equals(selected)) {
                buff.append(" selected");
            }
            buff.append('>').
                append(PageParser.escapeHtml(value)).
                append("</option>");
        }
        return buff.toString();
    }

    private static String getComboBox(String[][] elements, String selected) {
        StringBuilder buff = new StringBuilder();
        for (String[] n : elements) {
            buff.append("<option value=\"").
                append(PageParser.escapeHtmlData(n[0])).
                append('\"');
            if (n[0].equals(selected)) {
                buff.append(" selected");
            }
            buff.append('>').
                append(PageParser.escapeHtml(n[1])).
                append("</option>");
        }
        return buff.toString();
    }

    private String process(String file, NetworkConnectionInfo networkConnectionInfo) {
        trace("process " + file);
        while (file.endsWith(".do")) {
            switch (file) {
            case "login.do":
                file = login(networkConnectionInfo);
                break;
            case "index.do":
                file = index();
                break;
            case "logout.do":
                file = logout();
                break;
            case "settingRemove.do":
                file = settingRemove();
                break;
            case "settingSave.do":
                file = settingSave();
                break;
            case "test.do":
                file = test(networkConnectionInfo);
                break;
            case "query.do":
                file = query();
                break;
            case "tables.do":
                file = tables();
                break;
            case "editResult.do":
                file = editResult();
                break;
            case "getHistory.do":
                file = getHistory();
                break;
            case "admin.do":
                file = checkAdmin(file) ? admin() : "adminLogin.do";
                break;
            case "adminSave.do":
                file = checkAdmin(file) ? adminSave() : "adminLogin.do";
                break;
            case "adminStartTranslate.do":
                file = checkAdmin(file) ? adminStartTranslate() : "adminLogin.do";
                break;
            case "adminShutdown.do":
                file = checkAdmin(file) ? adminShutdown() : "adminLogin.do";
                break;
            case "autoCompleteList.do":
                file = autoCompleteList();
                break;
            case "tools.do":
                file = checkAdmin(file) ? tools() : "adminLogin.do";
                break;
            case "adminLogin.do":
                file = adminLogin();
                break;
            default:
                file = "error.jsp";
                break;
            }
        }
        trace("return " + file);
        return file;
    }

    private boolean checkAdmin(String file) {
        Boolean b = (Boolean) session.get("admin");
        if (b != null && b) {
            return true;
        }
        String key = server.getKey();
        if (key != null && key.equals(session.get("key"))) {
            return true;
        }
        session.put("adminBack", file);
        return false;
    }

    private String adminLogin() {
        String password = attributes.getProperty("password");
        if (password == null || password.isEmpty() || !server.checkAdminPassword(password)) {
            return "adminLogin.jsp";
        }
        String back = (String) session.remove("adminBack");
        session.put("admin", true);
        return back != null ? back : "admin.do";
    }

    private String autoCompleteList() {
        String query = (String) attributes.get("query");
        boolean lowercase = false;
        String tQuery = query.trim();
        if (!tQuery.isEmpty() && Character.isLowerCase(tQuery.charAt(0))) {
            lowercase = true;
        }
        try {
            String sql = query;
            if (sql.endsWith(";")) {
                sql += " ";
            }
            ScriptReader reader = new ScriptReader(new StringReader(sql));
            reader.setSkipRemarks(true);
            String lastSql = "";
            while (true) {
                String n = reader.readStatement();
                if (n == null) {
                    break;
                }
                lastSql = n;
            }
            String result = "";
            if (reader.isInsideRemark()) {
                if (reader.isBlockRemark()) {
                    result = "1#(End Remark)# */\n" + result;
                } else {
                    result = "1#(Newline)#\n" + result;
                }
            } else {
                sql = lastSql;
                while (sql.length() > 0 && sql.charAt(0) <= ' ') {
                    sql = sql.substring(1);
                }
                String tSql = sql.trim();
                if (!tSql.isEmpty() && Character.isLowerCase(tSql.charAt(0))) {
                    lowercase = true;
                }
                Bnf bnf = session.getBnf();
                if (bnf == null) {
                    return "autoCompleteList.jsp";
                }
                HashMap<String, String> map = bnf.getNextTokenList(sql);
                String space = "";
                if (sql.length() > 0) {
                    char last = sql.charAt(sql.length() - 1);
                    if (!Character.isWhitespace(last) && (last != '.' &&
                            last >= ' ' && last != '\'' && last != '"')) {
                        space = " ";
                    }
                }
                ArrayList<String> list = new ArrayList<>(map.size());
                for (Map.Entry<String, String> entry : map.entrySet()) {
                    String key = entry.getKey();
                    String value = entry.getValue();
                    String type = String.valueOf(key.charAt(0));
                    if (Integer.parseInt(type) > 2) {
                        continue;
                    }
                    key = key.substring(2);
                    if (Character.isLetter(key.charAt(0)) && lowercase) {
                        key = StringUtils.toLowerEnglish(key);
                        value = StringUtils.toLowerEnglish(value);
                    }
                    if (key.equals(value) && !".".equals(value)) {
                        value = space + value;
                    }
                    key = StringUtils.urlEncode(key);
                    key = key.replace('+', ' ');
                    value = StringUtils.urlEncode(value);
                    value = value.replace('+', ' ');
                    list.add(type + "#" + key + "#" + value);
                }
                Collections.sort(list);
                if (query.endsWith("\n") || tQuery.endsWith(";")) {
                    list.add(0, "1#(Newline)#\n");
                }
                result = String.join("|", list);
            }
            session.put("autoCompleteList", result);
        } catch (Throwable e) {
            server.traceError(e);
        }
        return "autoCompleteList.jsp";
    }

    private String admin() {
        session.put("port", Integer.toString(server.getPort()));
        session.put("allowOthers", Boolean.toString(server.getAllowOthers()));
        session.put("webExternalNames", server.getExternalNames());
        session.put("ssl", String.valueOf(server.getSSL()));
        session.put("sessions", server.getSessions());
        return "admin.jsp";
    }

    private String adminSave() {
        try {
            Properties prop = new SortedProperties();
            int port = Integer.decode((String) attributes.get("port"));
            prop.setProperty("webPort", Integer.toString(port));
            server.setPort(port);
            boolean allowOthers = Utils.parseBoolean((String) attributes.get("allowOthers"), false, false);
            prop.setProperty("webAllowOthers", String.valueOf(allowOthers));
            server.setAllowOthers(allowOthers);
            String externalNames = (String) attributes.get("webExternalNames");
            prop.setProperty("webExternalNames", externalNames);
            server.setExternalNames(externalNames);
            boolean ssl = Utils.parseBoolean((String) attributes.get("ssl"), false, false);
            prop.setProperty("webSSL", String.valueOf(ssl));
            server.setSSL(ssl);
            byte[] adminPassword = server.getAdminPassword();
            if (adminPassword != null) {
                prop.setProperty("webAdminPassword", StringUtils.convertBytesToHex(adminPassword));
            }
            server.saveProperties(prop);
        } catch (Exception e) {
            trace(e.toString());
        }
        return admin();
    }

    private String tools() {
        try {
            String toolName = (String) attributes.get("tool");
            session.put("tool", toolName);
            String args = (String) attributes.get("args");
            String[] argList = StringUtils.arraySplit(args, ',', false);
            Tool tool = null;
            if ("Backup".equals(toolName)) {
                tool = new Backup();
            } else if ("Restore".equals(toolName)) {
                tool = new Restore();
            } else if ("Recover".equals(toolName)) {
                tool = new Recover();
            } else if ("DeleteDbFiles".equals(toolName)) {
                tool = new DeleteDbFiles();
            } else if ("ChangeFileEncryption".equals(toolName)) {
                tool = new ChangeFileEncryption();
            } else if ("Script".equals(toolName)) {
                tool = new Script();
            } else if ("RunScript".equals(toolName)) {
                tool = new RunScript();
            } else if ("ConvertTraceFile".equals(toolName)) {
                tool = new ConvertTraceFile();
            } else if ("CreateCluster".equals(toolName)) {
                tool = new CreateCluster();
            } else {
                throw DbException.getInternalError(toolName);
            }
            ByteArrayOutputStream outBuff = new ByteArrayOutputStream();
            PrintStream out = new PrintStream(outBuff, false, "UTF-8");
            tool.setOut(out);
            try {
                tool.runTool(argList);
                out.flush();
                String o = Utils10.byteArrayOutputStreamToString(outBuff, StandardCharsets.UTF_8);
                String result = PageParser.escapeHtml(o);
                session.put("toolResult", result);
            } catch (Exception e) {
                session.put("toolResult", getStackTrace(0, e, true));
            }
        } catch (Exception e) {
            server.traceError(e);
        }
        return "tools.jsp";
    }

    private String adminStartTranslate() {
        Map<?, ?> p = Map.class.cast(session.map.get("text"));
        @SuppressWarnings("unchecked")
        Map<Object, Object> p2 = (Map<Object, Object>) p;
        String file = server.startTranslate(p2);
        session.put("translationFile", file);
        return "helpTranslate.jsp";
    }

    /**
     * Stop the application and the server.
     *
     * @return the page to display
     */
    protected String adminShutdown() {
        server.shutdown();
        return "admin.jsp";
    }

    private String index() {
        String[][] languageArray = WebServer.LANGUAGES;
        String language = (String) attributes.get("language");
        Locale locale = session.locale;
        if (language != null) {
            if (locale == null || !StringUtils.toLowerEnglish(
                    locale.getLanguage()).equals(language)) {
                locale = new Locale(language, "");
                server.readTranslations(session, locale.getLanguage());
                session.put("language", language);
                session.locale = locale;
            }
        } else {
            language = (String) session.get("language");
        }
        if (language == null) {
            // if the language is not yet known
            // use the last header
            language = headerLanguage;
        }
        session.put("languageCombo", getComboBox(languageArray, language));
        String[] settingNames = server.getSettingNames();
        String setting = attributes.getProperty("setting");
        if (setting == null && settingNames.length > 0) {
            setting = settingNames[0];
        }
        String combobox = getComboBox(settingNames, setting);
        session.put("settingsList", combobox);
        ConnectionInfo info = server.getSetting(setting);
        if (info == null) {
            info = new ConnectionInfo();
        }
        session.put("setting", PageParser.escapeHtmlData(setting));
        session.put("name", PageParser.escapeHtmlData(setting));
        session.put("driver", PageParser.escapeHtmlData(info.driver));
        session.put("url", PageParser.escapeHtmlData(info.url));
        session.put("user", PageParser.escapeHtmlData(info.user));
        return "index.jsp";
    }

    private String getHistory() {
        int id = Integer.parseInt(attributes.getProperty("id"));
        String sql = session.getCommand(id);
        session.put("query", PageParser.escapeHtmlData(sql));
        return "query.jsp";
    }

    private static int addColumns(boolean mainSchema, DbTableOrView table, StringBuilder builder, int treeIndex,
            boolean showColumnTypes, StringBuilder columnsBuilder) {
        DbColumn[] columns = table.getColumns();
        for (int i = 0; columns != null && i < columns.length; i++) {
            DbColumn column = columns[i];
            if (columnsBuilder.length() > 0) {
                columnsBuilder.append(' ');
            }
            columnsBuilder.append(column.getName());
            String col = escapeIdentifier(column.getName());
            String level = mainSchema ? ", 1, 1" : ", 2, 2";
            builder.append("setNode(").append(treeIndex).append(level)
                    .append(", 'column', '")
                    .append(PageParser.escapeJavaScript(column.getName()))
                    .append("', 'javascript:ins(\\'").append(col).append("\\')');\n");
            treeIndex++;
            if (mainSchema && showColumnTypes) {
                builder.append("setNode(").append(treeIndex)
                        .append(", 2, 2, 'type', '")
                        .append(PageParser.escapeJavaScript(column.getDataType()))
                        .append("', null);\n");
                treeIndex++;
            }
        }
        return treeIndex;
    }

    private static String escapeIdentifier(String name) {
        return StringUtils.urlEncode(
                PageParser.escapeJavaScript(name)).replace('+', ' ');
    }

    /**
     * This class represents index information for the GUI.
     */
    static class IndexInfo {

        /**
         * The index name.
         */
        String name;

        /**
         * The index type name.
         */
        String type;

        /**
         * The indexed columns.
         */
        String columns;
    }

    private static int addIndexes(boolean mainSchema, DatabaseMetaData meta,
            String table, String schema, StringBuilder buff, int treeIndex)
            throws SQLException {
        ResultSet rs;
        try {
            rs = meta.getIndexInfo(null, schema, table, false, true);
        } catch (SQLException e) {
            // SQLite
            return treeIndex;
        }
        HashMap<String, IndexInfo> indexMap = new HashMap<>();
        while (rs.next()) {
            String name = rs.getString("INDEX_NAME");
            IndexInfo info = indexMap.get(name);
            if (info == null) {
                int t = rs.getInt("TYPE");
                String type;
                if (t == DatabaseMetaData.tableIndexClustered) {
                    type = "";
                } else if (t == DatabaseMetaData.tableIndexHashed) {
                    type = " (${text.tree.hashed})";
                } else if (t == DatabaseMetaData.tableIndexOther) {
                    type = "";
                } else {
                    type = null;
                }
                if (name != null && type != null) {
                    info = new IndexInfo();
                    info.name = name;
                    type = (rs.getBoolean("NON_UNIQUE") ?
                            "${text.tree.nonUnique}" : "${text.tree.unique}") + type;
                    info.type = type;
                    info.columns = rs.getString("COLUMN_NAME");
                    indexMap.put(name, info);
                }
            } else {
                info.columns += ", " + rs.getString("COLUMN_NAME");
            }
        }
        rs.close();
        if (indexMap.size() > 0) {
            String level = mainSchema ? ", 1, 1" : ", 2, 1";
            String levelIndex = mainSchema ? ", 2, 1" : ", 3, 1";
            String levelColumnType = mainSchema ? ", 3, 2" : ", 4, 2";
            buff.append("setNode(").append(treeIndex).append(level)
                    .append(", 'index_az', '${text.tree.indexes}', null);\n");
            treeIndex++;
            for (IndexInfo info : indexMap.values()) {
                buff.append("setNode(").append(treeIndex).append(levelIndex)
                        .append(", 'index', '")
                        .append(PageParser.escapeJavaScript(info.name))
                        .append("', null);\n");
                treeIndex++;
                buff.append("setNode(").append(treeIndex).append(levelColumnType)
                        .append(", 'type', '").append(info.type).append("', null);\n");
                treeIndex++;
                buff.append("setNode(").append(treeIndex).append(levelColumnType)
                        .append(", 'type', '")
                        .append(PageParser.escapeJavaScript(info.columns))
                        .append("', null);\n");
                treeIndex++;
            }
        }
        return treeIndex;
    }

    private int addTablesAndViews(DbSchema schema, boolean mainSchema, StringBuilder builder, int treeIndex)
            throws SQLException {
        if (schema == null) {
            return treeIndex;
        }
        Connection conn = session.getConnection();
        DatabaseMetaData meta = session.getMetaData();
        int level = mainSchema ? 0 : 1;
        boolean showColumns = mainSchema || !schema.isSystem;
        String indentation = ", " + level + ", " + (showColumns ? "1" : "2") + ", ";
        String indentNode = ", " + (level + 1) + ", 2, ";
        DbTableOrView[] tables = schema.getTables();
        if (tables == null) {
            return treeIndex;
        }
        DbContents contents = schema.getContents();
        boolean isOracle = contents.isOracle();
        boolean notManyTables = tables.length < SysProperties.CONSOLE_MAX_TABLES_LIST_INDEXES;
        try (PreparedStatement prep = showColumns ? prepareViewDefinitionQuery(conn, contents) : null) {
            if (prep != null) {
                prep.setString(1, schema.name);
            }
            AtomicReference<PreparedStatement> prepRef = new AtomicReference<>(prep);
            if (schema.isSystem) {
                Arrays.sort(tables, SYSTEM_SCHEMA_COMPARATOR);
                for (DbTableOrView table : tables) {
                    treeIndex = addTableOrView(schema, mainSchema, builder, treeIndex, meta, false, indentation,
                            isOracle, notManyTables, table, table.isView(), prepRef, indentNode);
                }
            } else {
                for (DbTableOrView table : tables) {
                    if (table.isView()) {
                        continue;
                    }
                    treeIndex = addTableOrView(schema, mainSchema, builder, treeIndex, meta, showColumns, indentation,
                            isOracle, notManyTables, table, false, null, indentNode);
                }
                for (DbTableOrView table : tables) {
                    if (!table.isView()) {
                        continue;
                    }
                    treeIndex = addTableOrView(schema, mainSchema, builder, treeIndex, meta, showColumns, indentation,
                            isOracle, notManyTables, table, true, prepRef, indentNode);
                }
            }
        }
        return treeIndex;
    }

    private static PreparedStatement prepareViewDefinitionQuery(Connection conn, DbContents contents) {
        if (contents.mayHaveStandardViews()) {
            try {
                return conn.prepareStatement("SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS"
                        + " WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?");
            } catch (SQLException e) {
                contents.setMayHaveStandardViews(false);
            }
        }
        return null;
    }

    private static int addTableOrView(DbSchema schema, boolean mainSchema, StringBuilder builder, int treeIndex,
            DatabaseMetaData meta, boolean showColumns, String indentation, boolean isOracle, boolean notManyTables,
            DbTableOrView table, boolean isView, AtomicReference<PreparedStatement> prepRef, String indentNode)
                    throws SQLException {
        int tableId = treeIndex;
        String tab = table.getQuotedName();
        if (!mainSchema) {
            tab = schema.quotedName + '.' + tab;
        }
        tab = escapeIdentifier(tab);
        builder.append("setNode(").append(treeIndex).append(indentation)
                .append(" '").append(isView ? "view" : "table").append("', '")
                .append(PageParser.escapeJavaScript(table.getName()))
                .append("', 'javascript:ins(\\'").append(tab).append("\\',true)');\n");
        treeIndex++;
        if (showColumns) {
            StringBuilder columnsBuilder = new StringBuilder();
            treeIndex = addColumns(mainSchema, table, builder, treeIndex, notManyTables, columnsBuilder);
            if (isView) {
                PreparedStatement prep = prepRef.get();
                if (prep != null) {
                    prep.setString(2, table.getName());
                    try (ResultSet rs = prep.executeQuery()) {
                        if (rs.next()) {
                            String sql = rs.getString(1);
                            if (sql != null) {
                                builder.append("setNode(").append(treeIndex).append(indentNode).append(" 'type', '")
                                        .append(PageParser.escapeJavaScript(sql)).append("', null);\n");
                                treeIndex++;
                            }
                        }
                    } catch (SQLException e) {
                        prepRef.set(null);
                    }
                }
            } else if (!isOracle && notManyTables) {
                treeIndex = addIndexes(mainSchema, meta, table.getName(), schema.name, builder, treeIndex);
            }
            builder.append("addTable('")
                    .append(PageParser.escapeJavaScript(table.getName())).append("', '")
                    .append(PageParser.escapeJavaScript(columnsBuilder.toString())).append("', ")
                    .append(tableId).append(");\n");
        }
        return treeIndex;
    }

    private String tables() {
        DbContents contents = session.getContents();
        boolean isH2 = false;
        try {
            String url = (String) session.get("url");
            Connection conn = session.getConnection();
            contents.readContents(url, conn);
            session.loadBnf();
            isH2 = contents.isH2();

            StringBuilder buff = new StringBuilder()
                    .append("setNode(0, 0, 0, 'database', '")
                    .append(PageParser.escapeJavaScript(url))
                    .append("', null);\n");
            int treeIndex = 1;

            DbSchema defaultSchema = contents.getDefaultSchema();
            treeIndex = addTablesAndViews(defaultSchema, true, buff, treeIndex);
            DbSchema[] schemas = contents.getSchemas();
            for (DbSchema schema : schemas) {
                if (schema == defaultSchema || schema == null) {
                    continue;
                }
                buff.append("setNode(").append(treeIndex).append(", 0, 1, 'folder', '")
                        .append(PageParser.escapeJavaScript(schema.name))
                        .append("', null);\n");
                treeIndex++;
                treeIndex = addTablesAndViews(schema, false, buff, treeIndex);
            }
            if (isH2) {
                try (Statement stat = conn.createStatement()) {
                    ResultSet rs;
                    try {
                        rs = stat.executeQuery("SELECT SEQUENCE_NAME, BASE_VALUE, INCREMENT FROM " +
                                "INFORMATION_SCHEMA.SEQUENCES ORDER BY SEQUENCE_NAME");
                    } catch (SQLException e) {
                        rs = stat.executeQuery("SELECT SEQUENCE_NAME, CURRENT_VALUE, INCREMENT FROM " +
                                "INFORMATION_SCHEMA.SEQUENCES ORDER BY SEQUENCE_NAME");
                    }
                    for (int i = 0; rs.next(); i++) {
                        if (i == 0) {
                            buff.append("setNode(").append(treeIndex)
                                    .append(", 0, 1, 'sequences', '${text.tree.sequences}', null);\n");
                            treeIndex++;
                        }
                        String name = rs.getString(1);
                        String currentBase = rs.getString(2);
                        String increment = rs.getString(3);
                        buff.append("setNode(").append(treeIndex)
                                .append(", 1, 1, 'sequence', '")
                                .append(PageParser.escapeJavaScript(name))
                                .append("', null);\n");
                        treeIndex++;
                        buff.append("setNode(").append(treeIndex)
                                .append(", 2, 2, 'type', '${text.tree.current}: ")
                                .append(PageParser.escapeJavaScript(currentBase))
                                .append("', null);\n");
                        treeIndex++;
                        if (!"1".equals(increment)) {
                            buff.append("setNode(").append(treeIndex)
                                    .append(", 2, 2, 'type', '${text.tree.increment}: ")
                                    .append(PageParser.escapeJavaScript(increment))
                                    .append("', null);\n");
                            treeIndex++;
                        }
                    }
                    rs.close();
                    try {
                        rs = stat.executeQuery(
                                "SELECT USER_NAME, IS_ADMIN FROM INFORMATION_SCHEMA.USERS ORDER BY USER_NAME");
                    } catch (SQLException e) {
                        rs = stat.executeQuery("SELECT NAME, ADMIN FROM INFORMATION_SCHEMA.USERS ORDER BY NAME");
                    }
                    for (int i = 0; rs.next(); i++) {
                        if (i == 0) {
                            buff.append("setNode(").append(treeIndex)
                                    .append(", 0, 1, 'users', '${text.tree.users}', null);\n");
                            treeIndex++;
                        }
                        String name = rs.getString(1);
                        String admin = rs.getString(2);
                        buff.append("setNode(").append(treeIndex)
                                .append(", 1, 1, 'user', '")
                                .append(PageParser.escapeJavaScript(name))
                                .append("', null);\n");
                        treeIndex++;
                        if (admin.equalsIgnoreCase("TRUE")) {
                            buff.append("setNode(").append(treeIndex)
                                    .append(", 2, 2, 'type', '${text.tree.admin}', null);\n");
                            treeIndex++;
                        }
                    }
                    rs.close();
                }
            }
            DatabaseMetaData meta = session.getMetaData();
            String version = meta.getDatabaseProductName() + " " +
                    meta.getDatabaseProductVersion();
            buff.append("setNode(").append(treeIndex)
                    .append(", 0, 0, 'info', '")
                    .append(PageParser.escapeJavaScript(version))
                    .append("', null);\n")
                    .append("refreshQueryTables();");
            session.put("tree", buff.toString());
        } catch (Exception e) {
            session.put("tree", "");
            session.put("error", getStackTrace(0, e, isH2));
        }
        return "tables.jsp";
    }

    private String getStackTrace(int id, Throwable e, boolean isH2) {
        try {
            StringWriter writer = new StringWriter();
            e.printStackTrace(new PrintWriter(writer));
            String stackTrace = writer.toString();
            stackTrace = PageParser.escapeHtml(stackTrace);
            if (isH2) {
                stackTrace = linkToSource(stackTrace);
            }
            stackTrace = StringUtils.replaceAll(stackTrace, "\t",
                    "&nbsp;&nbsp;&nbsp;&nbsp;");
            String message = PageParser.escapeHtml(e.getMessage());
            String error = "<a class=\"error\" href=\"#\" " +
                    "onclick=\"var x=document.getElementById('st" + id +
                    "').style;x.display=x.display==''?'none':'';\">" + message +
                    "</a>";
            if (e instanceof SQLException) {
                SQLException se = (SQLException) e;
                error += " " + se.getSQLState() + "/" + se.getErrorCode();
                if (isH2) {
                    int code = se.getErrorCode();
                    error += " <a href=\"https://h2database.com/javadoc/" +
                            "org/h2/api/ErrorCode.html#c" + code +
                            "\">(${text.a.help})</a>";
                }
            }
            error += "<span style=\"display: none;\" id=\"st" + id +
                    "\"><br />" + stackTrace + "</span>";
            error = formatAsError(error);
            return error;
        } catch (OutOfMemoryError e2) {
            server.traceError(e);
            return e.toString();
        }
    }

    private static String linkToSource(String s) {
        try {
            StringBuilder result = new StringBuilder(s.length());
            int idx = s.indexOf("<br />");
            result.append(s, 0, idx);
            while (true) {
                int start = s.indexOf("org.h2.", idx);
                if (start < 0) {
                    result.append(s.substring(idx));
                    break;
                }
                result.append(s, idx, start);
                int end = s.indexOf(')', start);
                if (end < 0) {
                    result.append(s.substring(idx));
                    break;
                }
                String element = s.substring(start, end);
                int open = element.lastIndexOf('(');
                int dotMethod = element.lastIndexOf('.', open - 1);
                int dotClass = element.lastIndexOf('.', dotMethod - 1);
                String packageName = element.substring(0, dotClass);
                int colon = element.lastIndexOf(':');
                String file = element.substring(open + 1, colon);
                String lineNumber = element.substring(colon + 1, element.length());
                String fullFileName = packageName.replace('.', '/') + "/" + file;
                result.append("<a href=\"https://h2database.com/html/source.html?file=");
                result.append(fullFileName);
                result.append("&line=");
                result.append(lineNumber);
                result.append("&build=");
                result.append(Constants.BUILD_ID);
                result.append("\">");
                result.append(element);
                result.append("</a>");
                idx = end;
            }
            return result.toString();
        } catch (Throwable t) {
            return s;
        }
    }

    private static String formatAsError(String s) {
        return "<div class=\"error\">" + s + "</div>";
    }

    private String test(NetworkConnectionInfo networkConnectionInfo) {
        String driver = attributes.getProperty("driver", "");
        String url = attributes.getProperty("url", "");
        String user = attributes.getProperty("user", "");
        String password = attributes.getProperty("password", "");
        session.put("driver", driver);
        session.put("url", url);
        session.put("user", user);
        boolean isH2 = url.startsWith("jdbc:h2:");
        try {
            long start = System.currentTimeMillis();
            String profOpen = "", profClose = "";
            Profiler prof = new Profiler();
            prof.startCollecting();
            Connection conn;
            try {
                conn = server.getConnection(driver, url, user, password, null, networkConnectionInfo);
            } finally {
                prof.stopCollecting();
                profOpen = prof.getTop(3);
            }
            prof = new Profiler();
            prof.startCollecting();
            try {
                JdbcUtils.closeSilently(conn);
            } finally {
                prof.stopCollecting();
                profClose = prof.getTop(3);
            }
            long time = System.currentTimeMillis() - start;
            String success;
            if (time > 1000) {
                success = "<a class=\"error\" href=\"#\" " +
                    "onclick=\"var x=document.getElementById('prof').style;x." +
                    "display=x.display==''?'none':'';\">" +
                    "${text.login.testSuccessful}</a>" +
                    "<span style=\"display: none;\" id=\"prof\"><br />" +
                    PageParser.escapeHtml(profOpen) +
                    "<br />" +
                    PageParser.escapeHtml(profClose) +
                    "</span>";
            } else {
                success = "<div class=\"success\">${text.login.testSuccessful}</div>";
            }
            session.put("error", success);
            // session.put("error", "${text.login.testSuccessful}");
            return "login.jsp";
        } catch (Exception e) {
            session.put("error", getLoginError(e, isH2));
            return "login.jsp";
        }
    }

    /**
     * Get the formatted login error message.
     *
     * @param e the exception
     * @param isH2 if the current database is a H2 database
     * @return the formatted error message
     */
    private String getLoginError(Exception e, boolean isH2) {
        if (e instanceof JdbcException && ((JdbcException) e).getErrorCode() == ErrorCode.CLASS_NOT_FOUND_1) {
            return "${text.login.driverNotFound}<br />" + getStackTrace(0, e, isH2);
        }
        return getStackTrace(0, e, isH2);
    }

    private String login(NetworkConnectionInfo networkConnectionInfo) {
        String driver = attributes.getProperty("driver", "");
        String url = attributes.getProperty("url", "");
        String user = attributes.getProperty("user", "");
        String password = attributes.getProperty("password", "");
        session.put("autoCommit", "checked");
        session.put("autoComplete", "1");
        session.put("maxrows", "1000");
        boolean isH2 = url.startsWith("jdbc:h2:");
        try {
            Connection conn = server.getConnection(driver, url, user, password, (String) session.get("key"),
                    networkConnectionInfo);
            session.setConnection(conn);
            session.put("url", url);
            session.put("user", user);
            session.remove("error");
            settingSave();
            return "frame.jsp";
        } catch (Exception e) {
            session.put("error", getLoginError(e, isH2));
            return "login.jsp";
        }
    }

    private String logout() {
        try {
            Connection conn = session.getConnection();
            session.setConnection(null);
            session.remove("conn");
            session.remove("result");
            session.remove("tables");
            session.remove("user");
            session.remove("tool");
            if (conn != null) {
                if (session.getShutdownServerOnDisconnect()) {
                    server.shutdown();
                } else {
                    conn.close();
                }
            }
        } catch (Exception e) {
            trace(e.toString());
        }
        session.remove("admin");
        return "index.do";
    }

    private String query() {
        String sql = attributes.getProperty("sql").trim();
        try {
            ScriptReader r = new ScriptReader(new StringReader(sql));
            final ArrayList<String> list = new ArrayList<>();
            while (true) {
                String s = r.readStatement();
                if (s == null) {
                    break;
                }
                list.add(s);
            }
            final Connection conn = session.getConnection();
            if (SysProperties.CONSOLE_STREAM && server.getAllowChunked()) {
                String page = new String(server.getFile("result.jsp"), StandardCharsets.UTF_8);
                int idx = page.indexOf("${result}");
                // the first element of the list is the header, the last the
                // footer
                list.add(0, page.substring(0, idx));
                list.add(page.substring(idx + "${result}".length()));
                session.put("chunks", new Iterator<String>() {
                    private int i;
                    @Override
                    public boolean hasNext() {
                        return i < list.size();
                    }
                    @Override
                    public String next() {
                        String s = list.get(i++);
                        if (i == 1 || i == list.size()) {
                            return s;
                        }
                        StringBuilder b = new StringBuilder();
                        query(conn, s, i - 1, list.size() - 2, b);
                        return b.toString();
                    }
                });
                return "result.jsp";
            }
            String result;
            StringBuilder buff = new StringBuilder();
            for (int i = 0; i < list.size(); i++) {
                String s = list.get(i);
                query(conn, s, i, list.size(), buff);
            }
            result = buff.toString();
            session.put("result", result);
        } catch (Throwable e) {
            session.put("result", getStackTrace(0, e, session.getContents().isH2()));
        }
        return "result.jsp";
    }

    /**
     * Execute a query and append the result to the buffer.
     *
     * @param conn the connection
     * @param s the statement
     * @param i the index
     * @param size the number of statements
     * @param buff the target buffer
     */
    void query(Connection conn, String s, int i, int size, StringBuilder buff) {
        if (!(s.startsWith("@") && s.endsWith("."))) {
            buff.append(PageParser.escapeHtml(s + ";")).append("<br />");
        }
        boolean forceEdit = s.startsWith("@edit");
        buff.append(getResult(conn, i + 1, s, size == 1, forceEdit)).
            append("<br />");
    }

    private String editResult() {
        ResultSet rs = session.result;
        int row = Integer.parseInt(attributes.getProperty("row"));
        int op = Integer.parseInt(attributes.getProperty("op"));
        String result = "", error = "";
        try {
            if (op == 1) {
                boolean insert = row < 0;
                if (insert) {
                    rs.moveToInsertRow();
                } else {
                    rs.absolute(row);
                }
                for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                    String x = attributes.getProperty("r" + row + "c" + (i + 1));
                    unescapeData(x, rs, i + 1);
                }
                if (insert) {
                    rs.insertRow();
                } else {
                    rs.updateRow();
                }
            } else if (op == 2) {
                rs.absolute(row);
                rs.deleteRow();
            } else if (op == 3) {
                // cancel
            }
        } catch (Throwable e) {
            result = "<br />" + getStackTrace(0, e, session.getContents().isH2());
            error = formatAsError(e.getMessage());
        }
        String sql = "@edit " + (String) session.get("resultSetSQL");
        Connection conn = session.getConnection();
        result = error + getResult(conn, -1, sql, true, true) + result;
        session.put("result", result);
        return "result.jsp";
    }

    private int getMaxrows() {
        String r = (String) session.get("maxrows");
        return r == null ? 0 : Integer.parseInt(r);
    }

    private String getResult(Connection conn, int id, String sql,
            boolean allowEdit, boolean forceEdit) {
        try {
            sql = sql.trim();
            StringBuilder buff = new StringBuilder();
            String sqlUpper = StringUtils.toUpperEnglish(sql);
            if (sqlUpper.contains("CREATE") ||
                    sqlUpper.contains("DROP") ||
                    sqlUpper.contains("ALTER") ||
                    sqlUpper.contains("RUNSCRIPT")) {
                String sessionId = attributes.getProperty("jsessionid");
                buff.append("<script type=\"text/javascript\">parent['h2menu'].location='tables.do?jsessionid=")
                        .append(sessionId).append("';</script>");
            }
            Statement stat;
            DbContents contents = session.getContents();
            if (forceEdit || (allowEdit && contents.isH2())) {
                stat = conn.createStatement(
                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);
            } else {
                stat = conn.createStatement();
            }
            ResultSet rs;
            long time = System.currentTimeMillis();
            boolean metadata = false;
            Object generatedKeys = null;
            boolean edit = false;
            boolean list = false;
            if (JdbcUtils.isBuiltIn(sql, "@autocommit_true")) {
                conn.setAutoCommit(true);
                return "${text.result.autoCommitOn}";
            } else if (JdbcUtils.isBuiltIn(sql, "@autocommit_false")) {
                conn.setAutoCommit(false);
                return "${text.result.autoCommitOff}";
            } else if (JdbcUtils.isBuiltIn(sql, "@cancel")) {
                stat = session.executingStatement;
                if (stat != null) {
                    stat.cancel();
                    buff.append("${text.result.statementWasCanceled}");
                } else {
                    buff.append("${text.result.noRunningStatement}");
                }
                return buff.toString();
            } else if (JdbcUtils.isBuiltIn(sql, "@edit")) {
                edit = true;
                sql = StringUtils.trimSubstring(sql, "@edit".length());
                session.put("resultSetSQL", sql);
            }
            if (JdbcUtils.isBuiltIn(sql, "@list")) {
                list = true;
                sql = StringUtils.trimSubstring(sql, "@list".length());
            }
            if (JdbcUtils.isBuiltIn(sql, "@meta")) {
                metadata = true;
                sql = StringUtils.trimSubstring(sql, "@meta".length());
            }
            if (JdbcUtils.isBuiltIn(sql, "@generated")) {
                generatedKeys = true;
                int offset = "@generated".length();
                int length = sql.length();
                for (; offset < length; offset++) {
                    char c = sql.charAt(offset);
                    if (c == '(') {
                        Parser p = new Parser();
                        generatedKeys = p.parseColumnList(sql, offset);
                        offset = p.getLastParseIndex();
                        break;
                    }
                    if (!Character.isWhitespace(c)) {
                        break;
                    }
                }
                sql = StringUtils.trimSubstring(sql, offset);
            } else if (JdbcUtils.isBuiltIn(sql, "@history")) {
                buff.append(getCommandHistoryString());
                return buff.toString();
            } else if (JdbcUtils.isBuiltIn(sql, "@loop")) {
                sql = StringUtils.trimSubstring(sql, "@loop".length());
                int idx = sql.indexOf(' ');
                int count = Integer.decode(sql.substring(0, idx));
                sql = StringUtils.trimSubstring(sql, idx);
                return executeLoop(conn, count, sql);
            } else if (JdbcUtils.isBuiltIn(sql, "@maxrows")) {
                int maxrows = (int) Double.parseDouble(StringUtils.trimSubstring(sql, "@maxrows".length()));
                session.put("maxrows", Integer.toString(maxrows));
                return "${text.result.maxrowsSet}";
            } else if (JdbcUtils.isBuiltIn(sql, "@parameter_meta")) {
                sql = StringUtils.trimSubstring(sql, "@parameter_meta".length());
                PreparedStatement prep = conn.prepareStatement(sql);
                buff.append(getParameterResultSet(prep.getParameterMetaData()));
                return buff.toString();
            } else if (JdbcUtils.isBuiltIn(sql, "@password_hash")) {
                sql = StringUtils.trimSubstring(sql, "@password_hash".length());
                String[] p = JdbcUtils.split(sql);
                return StringUtils.convertBytesToHex(
                        SHA256.getKeyPasswordHash(p[0], p[1].toCharArray()));
            } else if (JdbcUtils.isBuiltIn(sql, "@prof_start")) {
                if (profiler != null) {
                    profiler.stopCollecting();
                }
                profiler = new Profiler();
                profiler.startCollecting();
                return "Ok";
            } else if (JdbcUtils.isBuiltIn(sql, "@sleep")) {
                String s = StringUtils.trimSubstring(sql, "@sleep".length());
                int sleep = 1;
                if (s.length() > 0) {
                    sleep = Integer.parseInt(s);
                }
                Thread.sleep(sleep * 1000);
                return "Ok";
            } else if (JdbcUtils.isBuiltIn(sql, "@transaction_isolation")) {
                String s = StringUtils.trimSubstring(sql, "@transaction_isolation".length());
                if (s.length() > 0) {
                    int level = Integer.parseInt(s);
                    conn.setTransactionIsolation(level);
                }
                buff.append("Transaction Isolation: ")
                        .append(conn.getTransactionIsolation())
                        .append("<br />");
                buff.append(Connection.TRANSACTION_READ_UNCOMMITTED)
                        .append(": read_uncommitted<br />");
                buff.append(Connection.TRANSACTION_READ_COMMITTED)
                        .append(": read_committed<br />");
                buff.append(Connection.TRANSACTION_REPEATABLE_READ)
                        .append(": repeatable_read<br />");
                buff.append(Constants.TRANSACTION_SNAPSHOT)
                        .append(": snapshot<br />");
                buff.append(Connection.TRANSACTION_SERIALIZABLE)
                        .append(": serializable");
            }
            if (sql.startsWith("@")) {
                rs = JdbcUtils.getMetaResultSet(conn, sql);
                if (rs == null && JdbcUtils.isBuiltIn(sql, "@prof_stop")) {
                    if (profiler != null) {
                        profiler.stopCollecting();
                        SimpleResultSet simple = new SimpleResultSet();
                        simple.addColumn("Top Stack Trace(s)", Types.VARCHAR, 0, 0);
                        simple.addRow(profiler.getTop(3));
                        rs = simple;
                        profiler = null;
                    }
                }
                if (rs == null) {
                    buff.append("?: ").append(sql);
                    return buff.toString();
                }
            } else {
                int maxrows = getMaxrows();
                stat.setMaxRows(maxrows);
                session.executingStatement = stat;
                boolean isResultSet;
                if (generatedKeys == null) {
                    isResultSet = stat.execute(sql);
                } else if (generatedKeys instanceof Boolean) {
                    isResultSet = stat.execute(sql,
                            ((Boolean) generatedKeys) ? Statement.RETURN_GENERATED_KEYS : Statement.NO_GENERATED_KEYS);
                } else if (generatedKeys instanceof String[]) {
                    isResultSet = stat.execute(sql, (String[]) generatedKeys);
                } else {
                    isResultSet = stat.execute(sql, (int[]) generatedKeys);
                }
                session.addCommand(sql);
                if (generatedKeys != null) {
                    rs = null;
                    rs = stat.getGeneratedKeys();
                } else {
                    if (!isResultSet) {
                        long updateCount;
                        try {
                            updateCount = stat.getLargeUpdateCount();
                        } catch (UnsupportedOperationException e) {
                            updateCount = stat.getUpdateCount();
                        }
                        buff.append("${text.result.updateCount}: ").append(updateCount);
                        time = System.currentTimeMillis() - time;
                        buff.append("<br />(").append(time).append(" ms)");
                        stat.close();
                        return buff.toString();
                    }
                    rs = stat.getResultSet();
                }
            }
            time = System.currentTimeMillis() - time;
            buff.append(getResultSet(sql, rs, metadata, list, edit, time, allowEdit));
            // SQLWarning warning = stat.getWarnings();
            // if (warning != null) {
            // buff.append("<br />Warning:<br />").
            // append(getStackTrace(id, warning));
            // }
            if (!edit) {
                stat.close();
            }
            return buff.toString();
        } catch (Throwable e) {
            // throwable: including OutOfMemoryError and so on
            return getStackTrace(id, e, session.getContents().isH2());
        } finally {
            session.executingStatement = null;
        }
    }

    private String executeLoop(Connection conn, int count, String sql)
            throws SQLException {
        ArrayList<Integer> params = new ArrayList<>();
        int idx = 0;
        while (!stop) {
            idx = sql.indexOf('?', idx);
            if (idx < 0) {
                break;
            }
            if (JdbcUtils.isBuiltIn(sql.substring(idx), "?/*rnd*/")) {
                params.add(1);
                sql = sql.substring(0, idx) + "?" + sql.substring(idx + "/*rnd*/".length() + 1);
            } else {
                params.add(0);
            }
            idx++;
        }
        boolean prepared;
        Random random = new Random(1);
        long time = System.currentTimeMillis();
        if (JdbcUtils.isBuiltIn(sql, "@statement")) {
            sql = StringUtils.trimSubstring(sql, "@statement".length());
            prepared = false;
            Statement stat = conn.createStatement();
            for (int i = 0; !stop && i < count; i++) {
                String s = sql;
                for (Integer type : params) {
                    idx = s.indexOf('?');
                    if (type == 1) {
                        s = s.substring(0, idx) + random.nextInt(count) + s.substring(idx + 1);
                    } else {
                        s = s.substring(0, idx) + i + s.substring(idx + 1);
                    }
                }
                if (stat.execute(s)) {
                    ResultSet rs = stat.getResultSet();
                    while (!stop && rs.next()) {
                        // maybe get the data as well
                    }
                    rs.close();
                }
            }
        } else {
            prepared = true;
            PreparedStatement prep = conn.prepareStatement(sql);
            for (int i = 0; !stop && i < count; i++) {
                for (int j = 0; j < params.size(); j++) {
                    Integer type = params.get(j);
                    if (type == 1) {
                        prep.setInt(j + 1, random.nextInt(count));
                    } else {
                        prep.setInt(j + 1, i);
                    }
                }
                if (session.getContents().isSQLite()) {
                    // SQLite currently throws an exception on prep.execute()
                    prep.executeUpdate();
                } else {
                    if (prep.execute()) {
                        ResultSet rs = prep.getResultSet();
                        while (!stop && rs.next()) {
                            // maybe get the data as well
                        }
                        rs.close();
                    }
                }
            }
        }
        time = System.currentTimeMillis() - time;
        StringBuilder builder = new StringBuilder().append(time).append(" ms: ").append(count).append(" * ")
                .append(prepared ? "(Prepared) " : "(Statement) ").append('(');
        for (int i = 0, size = params.size(); i < size; i++) {
            if (i > 0) {
                builder.append(", ");
            }
            builder.append(params.get(i) == 0 ? "i" : "rnd");
        }
        return builder.append(") ").append(sql).toString();
    }

    private String getCommandHistoryString() {
        StringBuilder buff = new StringBuilder();
        ArrayList<String> history = session.getCommandHistory();
        buff.append("<table cellspacing=0 cellpadding=0>" +
                "<tr><th></th><th>Command</th></tr>");
        for (int i = history.size() - 1; i >= 0; i--) {
            String sql = history.get(i);
            buff.append("<tr><td><a href=\"getHistory.do?id=").
                append(i).
                append("&jsessionid=${sessionId}\" target=\"h2query\" >").
                append("<img width=16 height=16 src=\"ico_write.gif\" " +
                        "onmouseover = \"this.className ='icon_hover'\" ").
                append("onmouseout = \"this.className ='icon'\" " +
                        "class=\"icon\" alt=\"${text.resultEdit.edit}\" ").
                append("title=\"${text.resultEdit.edit}\" border=\"1\"/></a>").
                append("</td><td>").
                append(PageParser.escapeHtml(sql)).
                append("</td></tr>");
        }
        buff.append("</table>");
        return buff.toString();
    }

    private static String getParameterResultSet(ParameterMetaData meta)
            throws SQLException {
        StringBuilder buff = new StringBuilder();
        if (meta == null) {
            return "No parameter meta data";
        }
        buff.append("<table cellspacing=0 cellpadding=0>").
            append("<tr><th>className</th><th>mode</th><th>type</th>").
            append("<th>typeName</th><th>precision</th><th>scale</th></tr>");
        for (int i = 0; i < meta.getParameterCount(); i++) {
            buff.append("</tr><td>").
                append(meta.getParameterClassName(i + 1)).
                append("</td><td>").
                append(meta.getParameterMode(i + 1)).
                append("</td><td>").
                append(meta.getParameterType(i + 1)).
                append("</td><td>").
                append(meta.getParameterTypeName(i + 1)).
                append("</td><td>").
                append(meta.getPrecision(i + 1)).
                append("</td><td>").
                append(meta.getScale(i + 1)).
                append("</td></tr>");
        }
        buff.append("</table>");
        return buff.toString();
    }

    private String getResultSet(String sql, ResultSet rs, boolean metadata,
            boolean list, boolean edit, long time, boolean allowEdit)
            throws SQLException {
        int maxrows = getMaxrows();
        time = System.currentTimeMillis() - time;
        StringBuilder buff = new StringBuilder();
        if (edit) {
            buff.append("<form id=\"editing\" name=\"editing\" method=\"post\" " +
                    "action=\"editResult.do?jsessionid=${sessionId}\" " +
                    "id=\"mainForm\" target=\"h2result\">" +
                    "<input type=\"hidden\" name=\"op\" value=\"1\" />" +
                    "<input type=\"hidden\" name=\"row\" value=\"\" />" +
                    "<table class=\"resultSet\" cellspacing=\"0\" cellpadding=\"0\" id=\"editTable\">");
        } else {
            buff.append("<table class=\"resultSet\" cellspacing=\"0\" cellpadding=\"0\">");
        }
        if (metadata) {
            SimpleResultSet r = new SimpleResultSet();
            r.addColumn("#", Types.INTEGER, 0, 0);
            r.addColumn("label", Types.VARCHAR, 0, 0);
            r.addColumn("catalog", Types.VARCHAR, 0, 0);
            r.addColumn("schema", Types.VARCHAR, 0, 0);
            r.addColumn("table", Types.VARCHAR, 0, 0);
            r.addColumn("column", Types.VARCHAR, 0, 0);
            r.addColumn("type", Types.INTEGER, 0, 0);
            r.addColumn("typeName", Types.VARCHAR, 0, 0);
            r.addColumn("class", Types.VARCHAR, 0, 0);
            r.addColumn("precision", Types.INTEGER, 0, 0);
            r.addColumn("scale", Types.INTEGER, 0, 0);
            r.addColumn("displaySize", Types.INTEGER, 0, 0);
            r.addColumn("autoIncrement", Types.BOOLEAN, 0, 0);
            r.addColumn("caseSensitive", Types.BOOLEAN, 0, 0);
            r.addColumn("currency", Types.BOOLEAN, 0, 0);
            r.addColumn("nullable", Types.INTEGER, 0, 0);
            r.addColumn("readOnly", Types.BOOLEAN, 0, 0);
            r.addColumn("searchable", Types.BOOLEAN, 0, 0);
            r.addColumn("signed", Types.BOOLEAN, 0, 0);
            r.addColumn("writable", Types.BOOLEAN, 0, 0);
            r.addColumn("definitelyWritable", Types.BOOLEAN, 0, 0);
            ResultSetMetaData m = rs.getMetaData();
            for (int i = 1; i <= m.getColumnCount(); i++) {
                r.addRow(i,
                        m.getColumnLabel(i),
                        m.getCatalogName(i),
                        m.getSchemaName(i),
                        m.getTableName(i),
                        m.getColumnName(i),
                        m.getColumnType(i),
                        m.getColumnTypeName(i),
                        m.getColumnClassName(i),
                        m.getPrecision(i),
                        m.getScale(i),
                        m.getColumnDisplaySize(i),
                        m.isAutoIncrement(i),
                        m.isCaseSensitive(i),
                        m.isCurrency(i),
                        m.isNullable(i),
                        m.isReadOnly(i),
                        m.isSearchable(i),
                        m.isSigned(i),
                        m.isWritable(i),
                        m.isDefinitelyWritable(i));
            }
            rs = r;
        }
        ResultSetMetaData meta = rs.getMetaData();
        int columns = meta.getColumnCount();
        int rows = 0;
        if (list) {
            buff.append("<tr><th>Column</th><th>Data</th></tr><tr>");
            while (rs.next()) {
                if (maxrows > 0 && rows >= maxrows) {
                    break;
                }
                rows++;
                buff.append("<tr><td>Row #</td><td>").
                    append(rows).append("</tr>");
                for (int i = 0; i < columns; i++) {
                    buff.append("<tr><td>").
                        append(PageParser.escapeHtml(meta.getColumnLabel(i + 1))).
                        append("</td><td>").
                        append(escapeData(rs, i + 1)).
                        append("</td></tr>");
                }
            }
        } else {
            buff.append("<tr>");
            if (edit) {
                buff.append("<th>${text.resultEdit.action}</th>");
            }
            for (int i = 0; i < columns; i++) {
                buff.append("<th>").
                    append(PageParser.escapeHtml(meta.getColumnLabel(i + 1))).
                    append("</th>");
            }
            buff.append("</tr>");
            while (rs.next()) {
                if (maxrows > 0 && rows >= maxrows) {
                    break;
                }
                rows++;
                buff.append("<tr>");
                if (edit) {
                    buff.append("<td>").
                        append("<img onclick=\"javascript:editRow(").
                        append(rs.getRow()).
                        append(",'${sessionId}', '${text.resultEdit.save}', " +
                                "'${text.resultEdit.cancel}'").
                        append(")\" width=16 height=16 src=\"ico_write.gif\" " +
                                "onmouseover = \"this.className ='icon_hover'\" " +
                                "onmouseout = \"this.className ='icon'\" " +
                                "class=\"icon\" alt=\"${text.resultEdit.edit}\" " +
                                "title=\"${text.resultEdit.edit}\" border=\"1\"/>").
                        append("<img onclick=\"javascript:deleteRow(").
                        append(rs.getRow()).
                        append(",'${sessionId}', '${text.resultEdit.delete}', " +
                                "'${text.resultEdit.cancel}'").
                        append(")\" width=16 height=16 src=\"ico_remove.gif\" " +
                                "onmouseover = \"this.className ='icon_hover'\" " +
                                "onmouseout = \"this.className ='icon'\" " +
                                "class=\"icon\" alt=\"${text.resultEdit.delete}\" " +
                                "title=\"${text.resultEdit.delete}\" border=\"1\" /></a>").
                        append("</td>");
                }
                for (int i = 0; i < columns; i++) {
                    buff.append("<td>").
                        append(escapeData(rs, i + 1)).
                        append("</td>");
                }
                buff.append("</tr>");
            }
        }
        boolean isUpdatable = false;
        try {
            if (!session.getContents().isDB2()) {
                isUpdatable = rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE
                    && rs.getType() != ResultSet.TYPE_FORWARD_ONLY;
            }
        } catch (NullPointerException e) {
            // ignore
            // workaround for a JDBC-ODBC bridge problem
        }
        if (edit) {
            ResultSet old = session.result;
            if (old != null) {
                old.close();
            }
            session.result = rs;
        } else {
            rs.close();
        }
        if (edit) {
            buff.append("<tr><td>").
                append("<img onclick=\"javascript:editRow(-1, " +
                        "'${sessionId}', '${text.resultEdit.save}', '${text.resultEdit.cancel}'").
                append(")\" width=16 height=16 src=\"ico_add.gif\" " +
                        "onmouseover = \"this.className ='icon_hover'\" " +
                        "onmouseout = \"this.className ='icon'\" " +
                        "class=\"icon\" alt=\"${text.resultEdit.add}\" " +
                        "title=\"${text.resultEdit.add}\" border=\"1\"/>").
                append("</td>");
            for (int i = 0; i < columns; i++) {
                buff.append("<td></td>");
            }
            buff.append("</tr>");
        }
        buff.append("</table>");
        if (edit) {
            buff.append("</form>");
        }
        if (rows == 0) {
            buff.append("(${text.result.noRows}");
        } else if (rows == 1) {
            buff.append("(${text.result.1row}");
        } else {
            buff.append('(').append(rows).append(" ${text.result.rows}");
        }
        buff.append(", ");
        time = System.currentTimeMillis() - time;
        buff.append(time).append(" ms)");
        if (!edit && isUpdatable && allowEdit) {
            buff.append("<br /><br />" +
                    "<form name=\"editResult\" method=\"post\" " +
                    "action=\"query.do?jsessionid=${sessionId}\" target=\"h2result\">" +
                    "<input type=\"submit\" class=\"button\" " +
                    "value=\"${text.resultEdit.editResult}\" />" +
                    "<input type=\"hidden\" name=\"sql\" value=\"@edit ").
            append(PageParser.escapeHtmlData(sql)).
            append("\" /></form>");
        }
        return buff.toString();
    }

    /**
     * Save the current connection settings to the properties file.
     *
     * @return the file to open afterwards
     */
    private String settingSave() {
        ConnectionInfo info = new ConnectionInfo();
        info.name = attributes.getProperty("name", "");
        info.driver = attributes.getProperty("driver", "");
        info.url = attributes.getProperty("url", "");
        info.user = attributes.getProperty("user", "");
        server.updateSetting(info);
        attributes.put("setting", info.name);
        server.saveProperties(null);
        return "index.do";
    }

    private static String escapeData(ResultSet rs, int columnIndex) throws SQLException {
        if (DataType.isBinaryColumn(rs.getMetaData(), columnIndex)) {
            byte[] d = rs.getBytes(columnIndex);
            if (d == null) {
                return "<i>null</i>";
            } else if (d.length > 50_000) {
                return "<div style='display: none'>=+</div>" + StringUtils.convertBytesToHex(d, 3) + "... ("
                        + d.length + " ${text.result.bytes})";
            }
            return StringUtils.convertBytesToHex(d);
        }
        String d = rs.getString(columnIndex);
        if (d == null) {
            return "<i>null</i>";
        } else if (d.length() > 100_000) {
            return "<div style='display: none'>=+</div>" + PageParser.escapeHtml(d.substring(0, 100)) + "... ("
                    + d.length() + " ${text.result.characters})";
        } else if (d.equals("null") || d.startsWith("= ") || d.startsWith("=+")) {
            return "<div style='display: none'>= </div>" + PageParser.escapeHtml(d);
        } else if (d.equals("")) {
            // PageParser.escapeHtml replaces "" with a non-breaking space
            return "";
        }
        return PageParser.escapeHtml(d);
    }

    private void unescapeData(String x, ResultSet rs, int columnIndex)
            throws SQLException {
        if (x.equals("null")) {
            rs.updateNull(columnIndex);
            return;
        } else if (x.startsWith("=+")) {
            // don't update
            return;
        } else if (x.equals("=*")) {
            // set an appropriate default value
            int type = rs.getMetaData().getColumnType(columnIndex);
            switch (type) {
            case Types.TIME:
                rs.updateString(columnIndex, "12:00:00");
                break;
            case Types.TIMESTAMP:
            case Types.DATE:
                rs.updateString(columnIndex, "2001-01-01");
                break;
            default:
                rs.updateString(columnIndex, "1");
                break;
            }
            return;
        } else if (x.startsWith("= ")) {
            x = x.substring(2);
        }
        ResultSetMetaData meta = rs.getMetaData();
        if (DataType.isBinaryColumn(meta, columnIndex)) {
            rs.updateBytes(columnIndex, StringUtils.convertHexToBytes(x));
            return;
        }
        int type = meta.getColumnType(columnIndex);
        if (session.getContents().isH2()) {
            rs.updateString(columnIndex, x);
            return;
        }
        switch (type) {
        case Types.BIGINT:
            rs.updateLong(columnIndex, Long.decode(x));
            break;
        case Types.DECIMAL:
            rs.updateBigDecimal(columnIndex, new BigDecimal(x));
            break;
        case Types.DOUBLE:
        case Types.FLOAT:
            rs.updateDouble(columnIndex, Double.parseDouble(x));
            break;
        case Types.REAL:
            rs.updateFloat(columnIndex, Float.parseFloat(x));
            break;
        case Types.INTEGER:
            rs.updateInt(columnIndex, Integer.decode(x));
            break;
        case Types.TINYINT:
            rs.updateShort(columnIndex, Short.decode(x));
            break;
        default:
            rs.updateString(columnIndex, x);
        }
    }

    private String settingRemove() {
        String setting = attributes.getProperty("name", "");
        server.removeSetting(setting);
        ArrayList<ConnectionInfo> settings = server.getSettings();
        if (!settings.isEmpty()) {
            attributes.put("setting", settings.get(0));
        }
        server.saveProperties(null);
        return "index.do";
    }

    /**
     * Get the current mime type.
     *
     * @return the mime type
     */
    String getMimeType() {
        return mimeType;
    }

    boolean getCache() {
        return cache;
    }

    WebSession getSession() {
        return session;
    }

    private void trace(String s) {
        server.trace(s);
    }

}
