# ReportApi.java

    /*
    ** Module   : ReportApi.java
    ** Abstract : Server API for base reporting services of source code analytics web application.
    **
    ** Copyright (c) 2017-2023, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- -------------------------------------Description---------------------------------------
    ** 001 ECF 20170428 Created first version.
    ** 002 CA  20181003 Added flow chart support (for any top-level block).
    ** 003 ECF 20181117 Improved performance of standard summary report query.
    ** 004 ECF 20190523 Fixed typo.
    ** 005 ECF 20200929 Replaced report_cell column name ("row" apparently became a reserved work in the latest
    **                  version of H2).
    **     OM  20220405 XmlFilePlugin can load either a file or a stream from application's jar.
    **     DDF 20220823 Replaced .nextvalue with next value for.
    **     RAA 20230109 Changed inline statement(s) to prepared statement(s).
    ** 006 GBB 20230512 Logging methods replaced by CentralLogger/ConversionStatus.
    */

    /*
    ** This program is free software: you can redistribute it and/or modify
    ** it under the terms of the GNU Affero General Public License as
    ** published by the Free Software Foundation, either version 3 of the
    ** License, or (at your option) any later version.
    **
    ** This program is distributed in the hope that it will be useful,
    ** but WITHOUT ANY WARRANTY; without even the implied warranty of
    ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    ** GNU Affero General Public License for more details.
    **
    ** You may find a copy of the GNU Affero GPL version 3 at the following
    ** location: https://www.gnu.org/licenses/agpl-3.0.en.html
    **
    ** Additional terms under GNU Affero GPL version 3 section 7:
    **
    **   Under Section 7 of the GNU Affero GPL version 3, the following additional
    **   terms apply to the works covered under the License.  These additional terms
    **   are non-permissive additional terms allowed under Section 7 of the GNU
    **   Affero GPL version 3 and may not be removed by you.
    **
    **   0. Attribution Requirement.
    **
    **     You must preserve all legal notices or author attributions in the covered
    **     work or Appropriate Legal Notices displayed by works containing the covered
    **     work.  You may not remove from the covered work any author or developer
    **     credit already included within the covered work.
    **
    **   1. No License To Use Trademarks.
    **
    **     This license does not grant any license or rights to use the trademarks
    **     Golden Code, FWD, any Golden Code or FWD logo, or any other trademarks
    **     of Golden Code Development Corporation. You are not authorized to use the
    **     name Golden Code, FWD, or the names of any author or contributor, for
    **     publicity purposes without written authorization.
    **
    **   2. No Misrepresentation of Affiliation.
    **
    **     You may not represent yourself as Golden Code Development Corporation or FWD.
    **
    **     You may not represent yourself for publicity purposes as associated with
    **     Golden Code Development Corporation, FWD, or any author or contributor to
    **     the covered work, without written authorization.
    **
    **   3. No Misrepresentation of Source or Origin.
    **
    **     You may not represent the covered work as solely your work.  All modified
    **     versions of the covered work must be marked in a reasonable way to make it
    **     clear that the modified work is not originating from Golden Code Development
    **     Corporation or FWD.  All modified versions must contain the notices of
    **     attribution required in this license.
    */

    package com.goldencode.p2j.report.server;

    import java.security.*;
    import java.security.spec.*;
    import java.sql.*;
    import java.sql.Date;
    import java.util.*;
    import java.util.logging.*;

    import com.goldencode.ast.*;
    import com.goldencode.p2j.cfg.*;
    import com.goldencode.p2j.convert.*;
    import com.goldencode.p2j.pattern.*;
    import com.goldencode.p2j.report.*;
    import com.goldencode.p2j.util.*;
    import com.goldencode.util.*;

    /**
     * This class provides the server-side API for all base reporting services which support the FWD
     * Analytics web application. Methods in this class marked with the {@link WebApi} annotation
     * represent API entry points, using a remote procedure call model. These methods can return
     * simple Java objects with a bean interface, or lists, maps, or arrays of such objects. Each API
     * must be annotated with a unique message type via its {@code WebApi} annotation; this type must
     * match the message type used by the client application when invoking the API call.
     * <p>
     * This class must be registered with the server before an API call is first made by the client.
     * This is done with a static initializer in the {@link ReportWebServer} class.
     */
    public class ReportApi
    implements ReportConstants
    {
       /** Base message type for all report-specific APIs */
       private static final int MSG_BASE = ReportProtocol.MSG_BASE + 1000;

       /** Base message type for all filter-specific APIs */
       private static final int MSG_FILT_BASE = ReportProtocol.MSG_BASE + 4000;

       /** API to retrieve master code report information */
       private static final int MSG_MST_CODE_RPT = MSG_BASE + 1;

       /** API to retrieve master schema report information */
       private static final int MSG_MST_SCHEMA_RPT = MSG_BASE + 2;

       /** API to retrieve a particular summary report */
       private static final int MSG_SUMMARY_RPT = MSG_BASE + 3;

       /** API to retrieve a particular detail report */
       private static final int MSG_DETAIL_RPT = MSG_BASE + 4;

       /** API to retrieve source code and AST data for a specific source file */
       private static final int MSG_SOURCE_DATA = MSG_BASE + 5;

       /** API to define a new or update an existing report */
       private static final int MSG_DEF_RPT = MSG_BASE + 6;

       /** API to delete an existing report */
       private static final int MSG_DEL_RPT = MSG_BASE + 7;

       /** API to run a single report incrementally */
       private static final int MSG_RUN_RPT = MSG_BASE + 8;

       /** API to run search */
       private static final int MSG_SEARCH = MSG_BASE + 9;

       /** API to retrieve search history for current user */
       private static final int MSG_SEARCH_HISTORY = MSG_BASE + 10;

       /** Message ID for reading the control flow for a top-level block. */
       private static final int MSG_FLOW_CHART = MSG_BASE + 11;

       /** API to retrieve all file filter profile data and the ID of the active filter */
       private static final int MSG_FILTER_INFO = MSG_FILT_BASE + 1;

       /** API to set the active file filter profile */
       private static final int MSG_SET_FILTER = MSG_FILT_BASE + 2;

       /** API to retrieve all files specifications for the target filter */
       private static final int MSG_FILTER_SPEC = MSG_FILT_BASE + 3;

       /** API to retrieve the qualified names of all source files */
       private static final int MSG_ALL_SOURCE_FILES = MSG_FILT_BASE + 4;

       /** API to define a new file filter profile */
       private static final int MSG_DEF_FILTER = MSG_FILT_BASE + 5;

       /** API to delete a file filter profile */
       private static final int MSG_DEL_FILTER = MSG_FILT_BASE + 6;

       /** SQL query to retrieve a user by name */
       private static final String sqlQryUser =
          "select id, salt, pass, admin, pass_changed from user where name = ?";

       /** SQL query to get ID of default file filter */
       private static final String sqlQryDefFileFilter =
          "select id from filter_profile where default = true";

       /** Delete a filter profile */
       private static final String sqlDelFileFilter =
          "delete from filter_profile where id = ? and not default";

       /** SQL query to gather user reports of a certain type with their associated tags */
       private static final String sqlQryUserRptDefs =
          "select " +
          "(select group_concat(t.name order by t.id separator ',') " +
          "from tag t join report_tag rt on rt.tid = t.id where rt.rid = r.id) as tags, " +
          "r.id, r.title, r.condition, r.multiplex, " +
          "r.dumptype, r.dumpexpr, r.dumplevel, r.support " +
          "from report r where r.user = true and r.type = ? order by r.id";

       private static final String sqlQryAllUsers =
          "select name, admin, salt, pass, pass_changed from user order by id";

       private static final String sqlQryNonDefFlt =
          "select p.id, p.name, f.spec, f.wild from filter_profile p " +
          "join file_filter f on f.pid = p.id where not p.default order by p.id, f.spec";

       private static final String sqlUsrSrchHist =
          "select s.id, s.criteria, u.name from search_history s " +
          "join user_search us on us.sid = s.id " +
          "join user u on u.id = us.uid " +
          "order by s.id, u.id";

       /** SQL query for master code report using aggregate roll-ups */
       private static final String sqlQryMasterAgg =
          "select rid, title, matches, files, tag, support, conddesc from " +
          "((select t.id as tid, r.id as rid, r.support as support, " +
          "t.name as tag, r.title as title, r.conddesc as conddesc, " +
          "count(a.fid) as matches, count(distinct(a.fid)) as files " +
          "from report r " +
          "join report_tag rt on rt.rid = r.id " +
          "join tag t on t.id = rt.tid " +
          "left outer join category c on r.id = c.rid " +
          "left outer join match m on m.cid = c.id " +
          "left outer join active_file a on a.fid = m.fid and a.sid = ? " +
          "where r.type = ? and not r.custom " +
          "group by r.title, t.name, r.id, r.conddesc) " +
          "union " +
          "(select t.id as tid, r.id as rid, r.support as support, " +
          "t.name as tag, r.title as title, r.conddesc as conddesc, " +
          "s.matches as matches, s.files as files " +
          "from report r " +
          "join report_stats s on s.rid = r.id " +
          "join report_tag rt on rt.rid = r.id " +
          "join tag t on t.id = rt.tid " +
          "where r.type = ? and r.custom)) " +
          "order by tid, rid";

       /** SQL query for master code report using cached statistics, including all source files */
       private static final String sqlQryMasterAll =
          "select r.id, r.title, s.matches, s.files, t.name, r.support, r.conddesc " +
          "from report r " +
          "join report_stats s on s.rid = r.id " +
          "join report_tag rt on rt.rid = s.rid " +
          "join tag t on t.id = rt.tid " +
          "where r.type = ? " +
          "order by t.id, r.id";

       /** SQL query for a particular custom report's column (meta) data */
       private static final String sqlQryCustCols =
          "select heading, format, sorting from report_column where rid = ? order by idx";

       /** SQL query for a particular custom report's cell data */
       private static final String sqlQryCustCells =
          "select col.idx, cell.text, cell.num from report_column col " +
          "join report_cell cell on cell.rcid = col.id " +
          "where col.rid = ? " +
          "order by cell.rowpos, col.idx";

       /** SQL query for a standard summary report */
       private static final String sqlQrySummaryStd =
          "select c.id as cid, c.name as condition, r.multiplex, " +
          "bitand(c.lvl, 0x001f), bitand(c.lvl, 0x7f00), " +
          "count(m.fid) as matches, " +
          "count(m.fid) * 100 / cast(? as double), " +
          "count(distinct(m.fid)) as files, " +
          "count(distinct(m.fid)) * 100 / cast(? as double) " +
          "from category c " +
          "join report r on r.id = c.rid " +
          "left outer join match m on m.cid = c.id " +
          "where c.rid = ? and exists(select 1 from active_file where fid = m.fid and sid = ?)" +
          "group by c.id " +
          "order by matches desc";

       /** SQL query for a detail report */
       private static final String sqlQryDetail =
          "select m.id, f.id, am.astid, f.name, m.line, m.col, m.text " +
          "from active_file a " +
          "join ast_map am on am.fid = a.fid and am.type = ? " +
          "join file f on f.id = a.fid " +
          "join match m on m.fid = f.id " +
          "where m.cid = ? and a.sid = ? " +
          "order by f.name, m.line, m.col";

       /** SQL query for all source lines (except marker line 0) for a particular source file */
       private static final String sqlQrySourceLines =
          "select line, data from source_line where fid = ? and line > 0 and bitand(?, type) <> 0 " +
          "order by line";

       /** SQL query to get AST ID associated with a file id and source type */
       private static final String sqlQryAstId =
          "select astid from ast_map where fid = ? and type = ?";

       /** Query the ID of a file record with the given name. */
       private static final String sqlQryFileID = "select id from file where name = ?";

       /** Query all filter profiles */
       private static final String sqlQryFilters =
          "select id, default, name from filter_profile";

       /** Query list of files specifications for the target filter*/
       private static final String sqlQryFilterSpec =
          "select wild, spec from file_filter where pid = ?";

       /** Query list of all source file names */
       private static final String sqlQryAllSrcFiles = "select name from file order by id";

       /** Query a report definition */
       private static final String sqlQryRptDef =
          "select type, title, condition, conddesc, multiplex, " +
          "dumptype, dumpexpr, dumplevel, support " +
          "from report where id = ?";

       /** Query all source file names of a certain type */
       private static final String sqlQryFiles =
          "select f.name from file f join ast_map a on a.fid = f.id where type = ?";

       /** Query all source file names in the specified filter with WILD mark. */
       private static final String sqlQryFltWildFiles =
          "select f.name from file f where name like ?";

       /** Query all source file names in the specified filter with non-WILD mark. */
       private static final String sqlQryFltExactFiles =
          "select f.name from file f where name = ?";

       /** Query all source file names of a certain type, applying active filter */
       private static final String sqlQryFltFiles =
          "select f.name from file f " +
          "join ast_map a on a.fid = f.id " +
          "join active_file af on af.fid = f.id and af.sid = ? " +
          "where type = ?";

       /** Query all source file ids and names of a certain type */
       private static final String sqlQryFidsNames =
          "select f.id, f.name from file f join ast_map a on a.fid = f.id where type = ?";

       /** Delete a single report */
       private static final String sqlDelUsrRpt = "delete from report where id = ?";

       /** Delete results for a single report */
       private static final String sqlDelRptResults = "delete from category where rid = ?";

       /** Update the user table with a new salt, password, and password changed date */
       private static final String sqlUsrPassChg =
          "update user set salt = ?, pass = ?, pass_changed = today where id = ?";

       /** Query search history for the current user */
       private static final String sqlQrySrchHist =
          "select s.criteria from user_search u join search_history s on s.id = u.sid " +
          "where u.uid = ? order by s.id desc";

       /** Object used to load ASTs from the file system */
       private static final AstManager astManager;

       /** Database services helper object */
       private static final DatabaseService dbs = DatabaseService.get();

       /** Default file filter ID to include all files */
       private static final long defaultFilterId;

       /** Logger */
       private static final ConversionStatus LOG = ConversionStatus.get(ReportApi.class);

       static
       {
          try
          {
             // initialize AST manager to read AST files
             String name = Configuration.getParameter("registry");
             AstManager.initialize(new XmlFilePlugin(Configuration.forceHome(name), false));
             astManager = AstManager.get();

             // query default file filter profile ID
             try (Connection conn = dbs.openConnection();
                  Statement stmt = conn.createStatement();
                  ResultSet rs = stmt.executeQuery(sqlQryDefFileFilter);)
             {
                if (rs.next())
                {
                   defaultFilterId = rs.getLong(1);
                }
                else
                {
                   throw new RuntimeException("Unable to query default file filter profile!");
                }
             }
          }
          catch (AstException | SQLException exc)
          {
             LOG.log(Level.WARNING,"", exc);

             throw new RuntimeException(exc);
          }
       }

       /** Cache of master report results by report/file type, using the current filter profile */
       private final Map<Integer, OverviewRow[]> cachedMasterResults = new HashMap<>();

       /** Current session ID */
       private long sessionId = -1;

       /** Authenticated user */
       private User user = null;

       /** Active file filter ID */
       private long filterId = defaultFilterId;

       /**
        * Default constructor.
        */
       public ReportApi()
       {
       }

       /**
        * List all files in this filter.
        *
        * @param    id
        *           The ID of a filter profile..
        *
        * @return   All the files represented by this profile.
        */
       static List<String> listFilesFromProfile(long id)
       {
          try (Connection conn = dbs.openConnection())
          {
             try (PreparedStatement ps = conn.prepareStatement(sqlQryFilterSpec))
             {
                List<String> list = new ArrayList<>();

                ps.setLong(1, id);

                ResultSet rs = ps.executeQuery();
                while (rs.next())
                {
                   FileFilter fileSpec = new FileFilter();
                   fileSpec.setWild(rs.getBoolean(1));
                   fileSpec.setSpec(rs.getString(2));

                   String fname = fileSpec.getSpec();
                   String sql = fileSpec.isWild() ? sqlQryFltWildFiles : sqlQryFltExactFiles;
                   if (fileSpec.isWild() && fname.endsWith("*"))
                   {
                      fname = fname.substring(0, fname.length() - 1) + "%";
                   }

                   try (PreparedStatement ps2 = conn.prepareStatement(sql))
                   {
                      ps2.setString(1, fname);

                      ResultSet rs2 = ps2.executeQuery();
                      while (rs2.next())
                      {
                         fname = rs2.getString(1);

                         list.add(fname);
                      }
                   }
                }

                conn.commit();

                return list;
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Get the record ID for the given file in the report database.
        *
        * @param   filename
        *          The filename (as in the FWD registry.xml).
        *
        * @return  the file record ID.
        */
       static int getFileId(String filename)
       {
          try (Connection conn = dbs.openConnection())
          {
             try (PreparedStatement ps = conn.prepareStatement(sqlQryFileID))
             {
                ps.setString(1, filename);

                ResultSet rs = ps.executeQuery();

                if (rs.next())
                {
                   return rs.getInt(1);
                }

                throw new RuntimeException("Can't locate fid for " + filename);
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Get a list of all user-defined reports of the given type.
        *
        * @param   srcType
        *          Type of report (schema or code).
        *
        * @return  List of {@link ReportDefinition} objects of the given type; may be empty, but will
        *          not be {@code null}.
        */
       static List<ReportDefinition> getUserReports(int srcType)
       {
          try (Connection conn = dbs.openConnection())
          {
             try (PreparedStatement ps = conn.prepareStatement(sqlQryUserRptDefs))
             {
                List<ReportDefinition> reports = new ArrayList<>();

                ps.setInt(1, srcType);

                ResultSet rs = ps.executeQuery();
                while (rs.next())
                {
                   ReportDefinition rpt = new ReportDefinition();
                   rpt.user = true;
                   rpt.setTagList(rs.getString(1));
                   rpt.id = rs.getLong(2);
                   rpt.title = rs.getString(3);
                   rpt.condition = rs.getString(4);
                   rpt.multiplexExpr = rs.getString(5);
                   rpt.dumpType = rs.getString(6);
                   rpt.dumpExpr = rs.getString(7);
                   rpt.dumpLevel = rs.getInt(8);
                   rpt.supportLvlExpr = rs.getString(9);
                   reports.add(rpt);
                }

                conn.commit();

                return reports;
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Retrieve a list of {@link UserRecord} records, each of which holds the details of a user.
        *
        * @return  List of user records.
        */
       static List<UserRecord> getUserRecords()
       {
          try (Connection conn = dbs.openConnection())
          {
             try (PreparedStatement ps = conn.prepareStatement(sqlQryAllUsers))
             {
                List<UserRecord> list = new ArrayList<>();

                ResultSet rs = ps.executeQuery();
                while (rs.next())
                {
                   String name = rs.getString(1);
                   boolean admin = rs.getBoolean(2);
                   byte[] salt = rs.getBytes(3);
                   byte[] pass = rs.getBytes(4);
                   Date passChanged = rs.getDate(5);

                   UserRecord user = new UserRecord(name, admin, salt, pass, passChanged);

                   list.add(user);
                }

                conn.commit();

                return list;
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Retrieve a list of all non-default {@link FilterProfile} records, including their file
        * filter specifications.
        *
        * @return  List of non-default filter profiles.
        */
       static List<FilterProfile> getNonDefaultFilters()
       {
          try (Connection conn = dbs.openConnection())
          {
             try (PreparedStatement ps = conn.prepareStatement(sqlQryNonDefFlt))
             {
                List<FilterProfile> list = new ArrayList<>();
                FilterProfile profile = null;

                ResultSet rs = ps.executeQuery();
                while (rs.next())
                {
                   long pid = rs.getLong(1);

                   // add new FilterProfile object to list when profile ID changes; this works
                   // because the query returns records first in profile ID order
                   if (profile == null || pid != profile.getId())
                   {
                      String name = rs.getString(2);
                      profile = new FilterProfile();
                      profile.setId(pid);
                      profile.setName(name);
                      profile.setDefault(false);
                      list.add(profile);
                   }

                   // add a file filter to the profile object
                   String spec = rs.getString(3);
                   boolean wild = rs.getBoolean(4);
                   FileFilter filter = new FileFilter();
                   filter.setSpec(spec);
                   filter.setWild(wild);
                   profile.addFilter(filter);
                }

                conn.commit();

                return list;
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Retrieve a list of {@link SearchHistory} records, each of which holds a search history
        * record's ID and criteria expression, and one or more user names.
        *
        * @return  List of search history records.
        */
       static List<SearchHistory> getUserSearchHistory()
       {
          try (Connection conn = dbs.openConnection())
          {
             try (PreparedStatement ps = conn.prepareStatement(sqlUsrSrchHist))
             {
                List<SearchHistory> list = new ArrayList<>();
                SearchHistory hist = null;

                ResultSet rs = ps.executeQuery();
                while (rs.next())
                {
                   long sid = rs.getLong(1);

                   // add new SearchHistory object to list when search ID changes; this works
                   // because the query returns records first in search ID order
                   if (hist == null || sid != hist.getSearchId())
                   {
                      String crit = rs.getString(2);
                      hist = new SearchHistory(sid, crit);
                      list.add(hist);
                   }

                   // add a user to the history object
                   String name = rs.getString(3);
                   hist.addUser(name);
                }

                conn.commit();

                return list;
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Explicitly shut down the database. This is necessary because we use the {@code
        * DB_CLOSE_ON_EXIT=FALSE} database option to enable our auto-export shutdown hook to run.
        *
        * @throws  RuntimeException
        *          if there is an SQL error during shutdown.
        */
       static void shutdownDatabase()
       {
          try
          {
             // no need to close connection as usual; shutdown will close all resources
             dbs.openConnection().createStatement().execute("shutdown");
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Apply a file filter to the active session. No other sessions are affected.
        * <p>
        * The filter works by storing in a global temporary table ({@code active_file}) the primary
        * keys of records from the {@code file} table which match the selected filter profile. All
        * regular reports then join their results on the {@code active_file} table to select only
        * those results which match the set of filtered files for the current session.
        *
        * @param   conn
        *          Database connection.
        * @param   profileId
        *          ID of the filter profile to apply, or {@code null} to apply the default filter.
        */
       private static void applyFileFilter(Connection conn, Long profileId, long sessionId)
       {
          PreparedStatement stmt = null;
          ResultSet rs = null;
          try
          {
             String sql;

             if (profileId != null)
             {
                // if profileId is null, the active_file was just created and is already empty;
                // otherwise, delete all its records in preparation for re-population with the new
                // filter profile's data
                stmt = conn.prepareStatement("delete from active_file where sid = ?");
                stmt.setObject(1, sessionId);
                stmt.executeUpdate();
             }

             // Query file specs and types for the provided file filter profile; we will compose a
             // compound (union'd) query from this to populate the active_file table.
             // If provided profile id was null, get the default profile.

             int i;
             int type = 0;

             // wildcard path specs first

             if (profileId == null)
             {
                sql = "select f.spec from file_filter f " +
                      "join filter_profile p on p.id = f.pid " +
                      "where p.default = true and f.wild = true";
             }
             else
             {
                sql = "select f.spec from file_filter f " +
                      "join filter_profile p on p.id = f.pid " +
                      "where p.id = ?" + " and f.wild = true";
                type = 1;
             }

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

             stmt = conn.prepareStatement(sql);
             if (type == 1)
             {
                stmt.setObject(1, profileId);
             }

             type = 0;
             rs = stmt.executeQuery();

             List<Object> args = new ArrayList<>();

             StringBuilder buf = new StringBuilder("insert into active_file (fid, sid) ");
             for (i = 0; rs.next(); i++)
             {
                String spec = rs.getString(1);

                if (i > 0)
                {
                   buf.append(" union ");
                }

                buf.append("select distinct(f.id), ");
                buf.append(sessionId);
                buf.append(" from file f join ast_map a on a.fid = f.id where f.name like ?");
                String arg1 = dbs.escapeLikeRValue(spec);
                arg1 = arg1.substring(0, arg1.length() - 1) + '%';
                args.add(arg1);
             }

             if (profileId == null)
             {
                sql = "select f.spec from file_filter f " +
                      "join filter_profile p on p.id = f.pid " +
                      "where p.default = true and f.wild = false";
             }
             else
             {
                sql = "select f.spec from file_filter f " +
                      "join filter_profile p on p.id = f.pid " +
                      "where p.id = ?" + " and f.wild = false";
                type = 1;
             }

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

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

             stmt = conn.prepareStatement(sql);
             if (type == 1)
             {
                stmt.setObject(1, profileId);
             }

             type = 0;
             rs = stmt.executeQuery();

             int j = 0;
             for (; rs.next(); i++)
             {
                String spec = rs.getString(1);

                if (i > 0 && j == 0)
                {
                   buf.append(" union ");
                }

                if (j == 0)
                {
                   buf.append("select distinct(id), ");
                   buf.append(sessionId);
                   buf.append(" from file where name in (");
                }
                else
                {
                   buf.append(", ");
                }

                args.add(spec);

                if (++j == 100)
                {
                   j = 0;
                   buf.append(')');
                }
             }

             if (j > 0)
             {
                buf.append(')');
             }

             sql = buf.toString();

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

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

             stmt = conn.prepareStatement(sql);
             for (int k = 0; k < args.size();k++)
             {
                stmt.setObject(k + 1, args.get(k));
             }

             stmt.execute();
             int uc = stmt.getUpdateCount();
             System.out.println("applyActiveFile update count: " + uc);

             conn.commit();
          }
          catch (SQLException exc)
          {
             if (conn != null)
             {
                try
                {
                   conn.rollback();
                }
                catch (SQLException sqle)
                {
                   LOG.log(Level.WARNING,"", exc);
                }
             }

             throw new RuntimeException(exc);
          }
          finally
          {
             try
             {
                if (rs != null && !rs.isClosed())
                {
                   rs.close();
                }

                if (stmt != null && !stmt.isClosed())
                {
                   stmt.close();
                }
             }
             catch (SQLException e)
             {
                LOG.log(Level.SEVERE, "", e);
             }
          }
       }

       /**
        * Query and return information necessary to produce the master report of overview results
        * for all code summary reports.
        *
        * @return  Array of {@link OverviewRow} objects, each providing the high level match
        *          statistics for its associated summary report.
        */
       @WebApi(type = MSG_MST_CODE_RPT)
       public OverviewRow[] masterCodeReport()
       {
          return masterReport(SRC_CODE_CACHE);
       }

       /**
        * Query and return information necessary to produce the master report of overview results
        * for all schema summary reports.
        *
        * @return  Array of {@link OverviewRow} objects, each providing the high level match
        *          statistics for its associated summary report.
        */
       @WebApi(type = MSG_MST_SCHEMA_RPT)
       public OverviewRow[] masterSchemaReport()
       {
          return masterReport(SRC_SCHEMA);
       }

       /**
        * Retrieve information necessary to display a particular summary report.
        *
        * @param   reportId
        *          ID of the summary report for which information is requested.
        * @param   totalMatches
        *          Total number of matches for this report. Used to calculate match percentages for
        *          individual match categories.
        * @param   totalFiles
        *          Total number of files for this report. Used to calculate file percentages for
        *          individual match categories.
        *
        * @return  An array of two objects; the first is a boolean which is {@code true} if the
        *          report represents a custom report, else {@code false}; the second represents the
        *          report results, either in the form of a {@link CustomReport} object (for a custom
        *          report) or an array of {@link SummaryRow} objects (for a standard report).
        */
       @WebApi(type = MSG_SUMMARY_RPT)
       public Object[] summaryReport(long reportId, int totalMatches, int totalFiles)
       {
          Object[] result = new Object[2];

          try (Connection conn = dbs.openConnection())
          {
             try
             {
                CustomReport customReport = null;

                // try to query custom column information for this report

                try (PreparedStatement ps = conn.prepareStatement(sqlQryCustCols))
                {
                   ps.setLong(1, reportId);

                   ResultSet rs = ps.executeQuery();

                   List<CustomColumn> list = new ArrayList<>();

                   while (rs.next())
                   {
                      if (customReport == null)
                      {
                         customReport = new CustomReport();
                         customReport.setColDefs(list);
                      }
                      CustomColumn col = new CustomColumn();
                      col.setHeading(rs.getString(1));
                      col.setFormat(rs.getString(2));
                      col.setSorting(rs.getInt(3));
                      list.add(col);
                   }
                }

                if (customReport != null)
                {
                   try (PreparedStatement ps = conn.prepareStatement(sqlQryCustCells))
                   {
                      ps.setLong(1, reportId);

                      ResultSet rs = ps.executeQuery();

                      List<Map<String, Object>> rows = new ArrayList<>();
                      Map<String, Object> row = null;

                      while (rs.next())
                      {
                         int idx = rs.getInt(1);
                         if (idx == 0)
                         {
                            row = new HashMap<>();
                            rows.add(row);
                         }
                         String text = rs.getString(2);
                         double num = rs.getDouble(3);

                         row.put(String.valueOf(idx), rs.wasNull() ? text : num);
                      }

                      conn.commit();

                      customReport.setRows(rows);

                      result[1] = customReport;
                   }
                }
                else
                {
                   List<SummaryRow> list = new ArrayList<>();

                   try (PreparedStatement ps = conn.prepareStatement(sqlQrySummaryStd))
                   {
                      ps.setDouble(1, totalMatches);
                      ps.setDouble(2, totalFiles);
                      ps.setLong(3, reportId);
                      ps.setLong(4, sessionId);

                      ResultSet rs = ps.executeQuery();

                      while (rs.next())
                      {
                         SummaryRow row = new SummaryRow();
                         row.setCid(rs.getInt(1));
                         row.setCat(rs.getString(2));
                         row.setMplex(rs.getString(3));
                         row.setCLvl(rs.getInt(4));
                         row.setRLvl(rs.getInt(5));
                         row.setMCnt(rs.getInt(6));
                         row.setMPct(rs.getDouble(7));
                         row.setFCnt(rs.getInt(8));
                         row.setFPct(rs.getDouble(9));
                         list.add(row);
                      }

                      conn.commit();
                   }

                   SummaryRow[] rows = list.toArray(new SummaryRow[0]);

                   result[1] = rows;
                }

                result[0] = (customReport != null);

                return result;
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Get a list of {@link DetailRow} objects which represent the rows of a detail report for
        * the specified match category of a report of the given type.
        *
        * @param   categoryId
        *          Unique identifier of a category of detailed report results.
        * @param   astType
        *          Type of ASTs to retrieve (schema or code).
        *
        * @return  List of detail report row objects.
        */
       @WebApi(type = MSG_DETAIL_RPT)
       public List<DetailRow> detailReport(long categoryId, int astType)
       {
          try (Connection conn = dbs.openConnection())
          {
             try (PreparedStatement ps = conn.prepareStatement(sqlQryDetail))
             {
                List<DetailRow> list = new ArrayList<>();

                ps.setInt(1, astType);
                ps.setLong(2, categoryId);
                ps.setLong(3, sessionId);

                ResultSet rs = ps.executeQuery();
                while (rs.next())
                {
                   DetailRow row = new DetailRow();
                   row.setId(rs.getLong(1));
                   row.setFid(rs.getLong(2));
                   row.setAstid(rs.getLong(3));
                   row.setFile(rs.getString(4));
                   row.setLine(rs.getInt(5));
                   row.setCol(rs.getInt(6));
                   row.setText(rs.getString(7));
                   list.add(row);
                }

                conn.commit();

                return list;
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Get the source code lines and AST nodes for a particular source file.
        *
        * @param   fileId
        *          File ID for the source file.
        * @param   astId
        *          ID of the root node of the source file's AST. If {@code astId == 0}, an additional
        *          query is executed to determine the AST ID for the given file id and source type.
        *          If {@code astId &lt; 0}, AST node data is not returned.
        * @param   sourceType
        *          Type of source file ({@code SRC_CODE_CACHE}, {@code SRC_CODE_BASE}, or {@code
        *          SRC_SCHEMA}).
        *
        * @return  An array of two objects. The first is an array list of source code lines; the
        *          second is an array list of AST nodes, or {@code null}.
        */
       @WebApi(type = MSG_SOURCE_DATA)
       public Object[] getSourceData(long fileId, long astId, int sourceType)
       {
          try (Connection conn = dbs.openConnection())
          {
             try (PreparedStatement ps = conn.prepareStatement(sqlQrySourceLines))
             {
                List<SourceLine> sourceLines = new ArrayList<>();

                ps.setLong(1, fileId);
                ps.setInt(2, sourceType);

                ResultSet rs = ps.executeQuery();
                while (rs.next())
                {
                   SourceLine src = new SourceLine();
                   src.setLine(rs.getInt(1));
                   src.setText(rs.getString(2));
                   sourceLines.add(src);
                }

                // if provided AST ID was 0, query the real ID using the other info we have
                if (astId == 0L)
                {
                   try (PreparedStatement ps2 = conn.prepareStatement(sqlQryAstId))
                   {
                      ps2.setLong(1, fileId);
                      ps2.setInt(2, sourceType);

                      rs = ps2.executeQuery();
                      if (rs.next())
                      {
                         astId = rs.getLong(1);
                      }
                   }
                }

                conn.commit();

                List<AstNode> nodes = null;

                // get the AST if we have a (presumably) valid ID
                if (astId > 0)
                {
                   String fileName = astManager.getTreeName(astId);
                   if (fileName != null)
                   {
                      if (sourceType == SRC_CODE_CACHE)
                      {
                         // code file names are mapped in the registry without an .ast extension
                         fileName += ".ast";
                      }
                      Aast ast = astManager.loadTree(DatabaseService.p2jHomePath + fileName);
                      nodes = prepareAstNodes(ast);
                   }
                }

                return new Object[] { sourceLines, nodes };
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Get all file filter profile records and the ID of the currently active profile.
        *
        * @return  An array of two objects. The first is a list of {@link FilterProfile} objects
        *          representing all available filter profiles; the second is the ID of the currently
        *          active profile.
        */
       @WebApi(type = MSG_FILTER_INFO)
       public Object[] filterProfileInfo()
       {
          try (Connection conn = dbs.openConnection())
          {
             try (PreparedStatement ps = conn.prepareStatement(sqlQryFilters))
             {
                List<FilterProfile> list = new ArrayList<>();

                ResultSet rs = ps.executeQuery();
                while (rs.next())
                {
                   FilterProfile profile = new FilterProfile();
                   profile.setId(rs.getLong(1));
                   profile.setDefault(rs.getBoolean(2));
                   profile.setName(rs.getString(3));
                   list.add(profile);
                }

                conn.commit();

                return new Object[] { list, filterId };
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Set the active file filter profile.
        *
        * @param   pid
        *          The identifier of the {@code filter_profile} to set.
        * @param   force
        *          {@code True} to force the filter update, even if the filter ID hasn't changed
        *          (presumably the filter definition has been updated).
        *
        * @throws  RuntimeException
        *          if {@code pid} is not a valid profile identifier, or if there is any error setting
        *          the active profile.
        */
       @WebApi(type = MSG_SET_FILTER)
       public void setFilter(long pid, boolean force)
       {
          // TODO: check that pid is a valid profile ID
          if (pid != this.filterId || force)
          {
             try (Connection conn = dbs.openConnection())
             {
                applyFileFilter(conn, pid, sessionId);
                this.filterId = pid;
                cachedMasterResults.clear();
             }
             catch (SQLException exc)
             {
                throw new RuntimeException(exc);
             }
          }
       }

       /**
        * Get all files specifications for the target filter.
        *
        * @param   id
        *          The id of the target filter
        *
        * @return  A list of {@link FileFilter} objects representing all files specifications
        *          for the target filter.
        */
       @WebApi(type = MSG_FILTER_SPEC)
       public List<FileFilter> filterSpecInfo(long id)
       {
          try (Connection conn = dbs.openConnection())
          {
             try (PreparedStatement ps = conn.prepareStatement(sqlQryFilterSpec))
             {
                List<FileFilter> list = new ArrayList<>();

                ps.setLong(1, id);

                ResultSet rs = ps.executeQuery();
                while (rs.next())
                {
                   FileFilter fileSpec = new FileFilter();
                   fileSpec.setWild(rs.getBoolean(1));
                   fileSpec.setSpec(rs.getString(2));
                   list.add(fileSpec);
                }

                conn.commit();

                return list;
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Get a list of the names of all source files.
        *
        * @return  List of source file names.
        */
       @WebApi(type = MSG_ALL_SOURCE_FILES)
       public List<String> allSourceFiles()
       {
          try (Connection conn = dbs.openConnection())
          {
             try (PreparedStatement ps = conn.prepareStatement(sqlQryAllSrcFiles))
             {
                List<String> list = new ArrayList<>();

                ResultSet rs = ps.executeQuery();
                while (rs.next())
                {
                   String name = rs.getString(1);
                   list.add(name);
                }

                conn.commit();

                return list;
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Define a new report, or update an existing definition.
        * <p>
        * TODO: add security check.
        *
        * @param   definition
        *          Report definition.
        * @param   srcType
        *          Type of report (schema or code).
        *
        * @return  Report ID assigned to the report.
        */
       @WebApi(type = MSG_DEF_RPT)
       public long defineReport(ReportDefinition definition, int srcType)
       {
          boolean databaseMode;
          switch (srcType)
          {
             case SRC_SCHEMA:
                databaseMode = true;
                break;
             case SRC_CODE_CACHE:
                databaseMode = false;
                break;
             default:
                throw new RuntimeException("Unknown source type: " + srcType);
          }

          try (Connection conn = dbs.openConnection())
          {
             try
             {
                ReportWorker rw = new ReportWorker(conn);
                definition.user = true;
                long reportId = rw.saveReport(definition, databaseMode, true);

                conn.commit();

                return reportId;
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Define a new file filter profile, or update an existing profile.
        * <p>
        * TODO: add security check.
        *
        * @param   profile
        *          Report definition.
        * @param   filters
        *          The filters associated with the profile.
        *
        * @return  Report ID assigned to the profile.
        */
       @WebApi(type = MSG_DEF_FILTER)
       public long defineFilterProfile(FilterProfile profile, FileFilter[] filters)
       {
          try (Connection conn = dbs.openConnection())
          {
             try
             {
                ReportWorker rw = new ReportWorker(conn);
                long profileId = rw.saveFilterProfile(profile, filters);

                conn.commit();

                return profileId;
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Delete a file filter profile, but only if it is not the default profile.
        * <p>
        * TODO: synchronize; this method makes changes to the database.
        * <p>
        * TODO: add security check.
        *
        * @param   profileId
        *          ID of the profile to delete.
        *
        * @return  {@code True} if the profile was deleted, else {@code false}.
        */
       @WebApi(type = MSG_DEL_FILTER)
       public boolean deleteFilterProfile(long profileId)
       {
          try (Connection conn = dbs.openConnection())
          {
             try (PreparedStatement ps = conn.prepareStatement(sqlDelFileFilter))
             {
                ps.setLong(1, profileId);
                ps.execute();
                boolean success = ps.getUpdateCount() > 0;

                conn.commit();

                return success;
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Delete a single report definition.
        * <p>
        * TODO: synchronize; this method makes changes to the database.
        * <p>
        * TODO: add security check.
        *
        * @param   reportId
        *          ID of the report to delete.
        */
       @WebApi(type = MSG_DEL_RPT)
       public void deleteReportDefinition(long reportId)
       {
          try (Connection conn = dbs.openConnection())
          {
             try (PreparedStatement ps = conn.prepareStatement(sqlDelUsrRpt))
             {
                ps.setLong(1, reportId);
                ps.execute();

                conn.commit();
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Run a single report in incremental mode.
        * <p>
        * TODO: synchronize; this method makes changes to the database.
        *
        * @param   reportId
        *          ID of the report to be run.
        */
       @WebApi(type = MSG_RUN_RPT)
       public void runReport(long reportId)
       {
          List<ReportDefinition> reports = new ArrayList<>();
          FileList[] files = new FileList[1];
          int srcType = 0;
          String title;
          boolean databaseMode;
          ReportDefinition rpt;

          try (Connection conn = dbs.openConnection())
          {
             try (PreparedStatement ps1 = conn.prepareStatement(sqlQryRptDef);
                  PreparedStatement ps2 = conn.prepareStatement(sqlDelRptResults);)
             {
                ps1.setLong(1, reportId);
                ResultSet rs = ps1.executeQuery();
                if (rs.next())
                {
                   rpt = new ReportDefinition();
                   srcType = rs.getInt(1);
                   rpt.id = reportId;
                   rpt.title = title = rs.getString(2);
                   rpt.condition = rs.getString(3);
                   rpt.conditionDescr = rs.getString(4);
                   rpt.multiplexExpr = rs.getString(5);
                   rpt.dumpType = rs.getString(6);
                   rpt.dumpExpr = rs.getString(7);
                   rpt.dumpLevel = rs.getInt(8);
                   rpt.supportLvlExpr = rs.getString(9);
                   reports.add(rpt);
                }
                else
                {
                   throw new RuntimeException("Report not found [id: " + reportId + "]");
                }

                switch (srcType)
                {
                   case SRC_SCHEMA:
                      databaseMode = true;
                      break;
                   case SRC_CODE_CACHE:
                      databaseMode = false;
                      break;
                   default:
                      throw new RuntimeException("Unexpected report type: " + srcType);
                }

                files[0] = gatherFiles(conn, false, srcType);

                // clear the current results for this report
                ps2.setLong(1, reportId);
                ps2.execute();
                ps2.clearParameters();

                conn.commit();
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
          finally
          {
             cachedMasterResults.remove(srcType);
          }

          Map<String, Object> vars = new HashMap<>();
          vars.put("databaseMode", databaseMode);
          vars.put("reports", reports);
          vars.put("incremental", true);

          try
          {
             ReportDriver.patternEngine(files, DebugLevels.MSG_STATUS, vars);
          }
          catch (Exception exc)
          {
             String msg = "Error running report '" + title + "'.";
             LOG.log(Level.SEVERE, msg, exc);

             throw new RuntimeException(msg, exc);
          }

          try (Connection conn = dbs.openConnection())
          {
             try
             {
                persistSearchHistory(conn, rpt.getCondition());

                conn.commit();
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw exc;
             }
          }
          catch (SQLException exc)
          {
             // this is not a fatal error, just log it
             LOG.log(Level.WARNING,"", exc);
          }
       }

       /**
        * Run an ad-hoc search of the specified type using the given expression.
        *
        * @param   criteria
        *          TRPL expression which must evaluate to true or false.
        * @param   applyFilter
        *          {@code True} to apply the current file filter when gathering the list of files to
        *          search; {@code false} to apply the search to all files.
        * @param   srcType
        *          Type of report (schema or code).
        *
        * @return  An array of objects: the first is a list of detail rows; the second is the number
        *          of matches found by the search; the third is the number of files containing those
        *          matches.
        */
       @WebApi(type = MSG_SEARCH)
       public Object[] runSearch(String criteria, boolean applyFilter, int srcType)
       {
          FileList files = null;
          int matchCount = 0;
          int fileCount = 0;

          try (Connection conn = dbs.openConnection())
          {
             try
             {
                files = gatherFiles(conn, applyFilter, srcType);

                // create a new pattern engine, configure it and run simple_search
                Map<String, Object> vars = new HashMap<>();
                vars.put("criteria", "'" + criteria + "'");
                vars.put("cmdline", false);
                PatternEngine.setDebugLevel(DebugLevels.MSG_STATUS);
                PatternEngine engine = new PatternEngine();
                engine.addAstSpec(files);
                engine.setReadOnly(true);
                engine.setVariableInitializers(vars);
                engine.run("reports/simple_search");

                // results will be stored by simple_search under the id "results"
                @SuppressWarnings("unchecked")
                List<DetailRow> rows = (List<DetailRow>) engine.getStoredObject("results");

                if (!rows.isEmpty())
                {
                   // iterate results to extract file information we need for detail rows
                   Set<String> filenames = new HashSet<>();
                   for (DetailRow row : rows)
                   {
                      String file = row.getFile();
                      if (srcType == SRC_SCHEMA)
                      {
                         file = stripSchemaName(file);
                      }
                      filenames.add(file);
                   }

                   // create query to look up all file ids by name
                   StringBuilder buf = new StringBuilder(sqlQryFidsNames);
                   buf.append(" and name in('");
                   int i = 0;
                   for (String file : filenames)
                   {
                      if (i > 0)
                      {
                         buf.append("', '");
                      }

                      buf.append(file);

                      i++;
                   }
                   buf.append("')");

                   String sql = buf.toString();

                   Map<String, Long> fids = new HashMap<>();

                   // map file names to fids from the database
                   try (PreparedStatement ps = conn.prepareStatement(sql))
                   {
                      ps.setLong(1, srcType);
                      ResultSet rs = ps.executeQuery();
                      while (rs.next())
                      {
                         long fid = rs.getLong(1);
                         String name = rs.getString(2);
                         fids.put(name, fid);
                      }
                   }

                   matchCount = rows.size();
                   fileCount = fids.size();

                   // final pass to compose detail rows from collected information
                   for (DetailRow row : rows)
                   {
                      String file = row.getFile();
                      if (srcType == SRC_SCHEMA)
                      {
                         file = stripSchemaName(file);
                      }

                      row.setFid(fids.get(file));
                   }
                }

                try
                {
                   persistSearchHistory(conn, criteria);
                }
                catch (SQLException exc)
                {
                   // not fatal, just log it
                   LOG.log(Level.WARNING,"", exc);
                }

                conn.commit();

                return new Object[] { rows, matchCount, fileCount };
             }
             catch (SQLException | AstException | ConfigurationException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Gather the search history, if any, for the current user.
        *
        * @return  List of TRPL search expressions, or {@code null} if not tracking search history.
        *          An empty list may be returned.
        */
       @WebApi(type = MSG_SEARCH_HISTORY)
       public List<String> getSearchHistory()
       {
          try (Connection conn = dbs.openConnection())
          {
             try (PreparedStatement ps = conn.prepareStatement(sqlQrySrchHist))
             {
                List<String> list = new ArrayList<>();

                ps.setLong(1, user.getId());

                ResultSet rs = ps.executeQuery();
                while (rs.next())
                {
                   list.add(rs.getString(1));
                }

                conn.commit();

                return list;
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Compute the flow chart for the specified AST ID (referencing an entry point), using the
        * specified direction (relevant for properly ordering the then/else nodes).
        *
        * @param    astId
        *           The AST ID.
        * @param    flowDir
        *           The flow direction, <code>true</code> for vertical.
        *
        * @return   The chart snippet.
        */
       @WebApi(type = MSG_FLOW_CHART)
       public FlowChartSnippet getFlowChart(long astId, boolean flowDir)
       {
          String fileName = astManager.getTreeName(astId);
          if (fileName == null)
          {
             return null;
          }

          // code file names are mapped in the registry without an .ast extension
          fileName += ".ast";

          Aast root = astManager.loadTree(DatabaseService.p2jHomePath + fileName);
          if (root == null)
          {
             return null;
          }

          // find our AST
          Aast ast = null;
          Iterator<Aast> iter = root.iterator();
          while (iter.hasNext())
          {
             ast = iter.next();
             Long id = ast.getId();

             if (id != null && astId == id.longValue())
             {
                break;
             }
             else
             {
                ast = null;
             }
          }

          if (ast == null)
          {
             // ast not found
             return null;
          }

          return new FlowChart(ast, flowDir).build();
       }

       /**
        * Authenticate the given user name and password.
        *
        * @param   conn
        *          Database connection.
        * @param   name
        *          User name to authenticate.
        * @param   pass
        *          Clear text password candidate to authenticate.
        *
        * @return  Authentication object containing the {@link User} instance which represents the
        *          authenticated user, if authentication was successful; {@code null} if it was not.
        *
        * @throws  NoSuchAlgorithmException
        *          if the requested encryption algorithm is not available in the current environment.
        * @throws  InvalidKeySpecException
        *          if the specifications for the cryptographic key are invalid.
        */
       Authentication authenticate(Connection conn, String name, String pass)
       throws NoSuchAlgorithmException,
              InvalidKeySpecException
       {
          try
          {
             Authentication auth = null;

             try (PreparedStatement ps = conn.prepareStatement(sqlQryUser))
             {
                ps.setString(1, name);

                ResultSet rs = ps.executeQuery();
                if (rs.next())
                {
                   byte[] salt = rs.getBytes(2);
                   byte[] encPass = rs.getBytes(3);
                   if (new PasswordHelper().authenticate(pass, encPass, salt))
                   {
                      long id = rs.getLong(1);
                      boolean admin = rs.getBoolean(4);
                      Date pwChanged = rs.getDate(5);
                      User user = new User(id, name, admin);
                      auth = new Authentication(user, pwChanged);
                   }
                }
             }

             return auth;
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Authenticate the given user name and password and log the user in if authentication
        * succeeds.
        *
        * @param   name
        *          User name to authenticate.
        * @param   pass
        *          Clear text password candidate to authenticate.
        *
        * @return  Authentication object containing the {@link User} instance that will be associated
        *          with the current session, if authentication was successful; {@code null} if it was
        *          not.
        *
        * @throws  NoSuchAlgorithmException
        *          if the requested encryption algorithm is not available in the current environment.
        * @throws  InvalidKeySpecException
        *          if the specifications for the cryptographic key are invalid.
        */
       Authentication logIn(String name, String pass, long sessionId)
       throws NoSuchAlgorithmException,
              InvalidKeySpecException
       {
          try (Connection conn = dbs.openConnection())
          {
             try
             {
                Authentication auth = authenticate(conn, name, pass);

                if (auth != null)
                {
                   user = auth.getUser();

                   // remember the session ID for later
                   this.sessionId = sessionId;

                   // piggyback default filter setup on authentication
                   applyFileFilter(conn, null, sessionId);
                }

                conn.commit();

                return auth;
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Change the given user's password, following some basic conventions for a valid new
        * password:
        * <ul>
        * <li>it must not be the same as the old password;</li>
        * <li>it must be at least 6 characters in length;</li>
        * <li>it must contain at least one letter and one digit.</li>
        * </ul>
        * The user is authenticated with the old password before the password change is allowed.
        *
        * @param   name
        *          User name.
        * @param   oldPass
        *          Existing password.
        * @param   newPass1
        *          New password.
        * @param   newPass2
        *          Repeat of new password; must be identical to {@code newPass1}.
        *
        * @throws  NoSuchAlgorithmException
        *          if the requested encryption algorithm is not available in the current environment.
        * @throws  InvalidKeySpecException
        *          if the specifications for the cryptographic key are invalid.
        */
       void changePassword(String name, String oldPass, String newPass1, String newPass2)
       throws NoSuchAlgorithmException,
              InvalidKeySpecException
       {
          try (Connection conn = dbs.openConnection())
          {
             try
             {
                if (authenticate(conn, name, oldPass) == null)
                {
                   throw new RuntimeException("Old password was incorrect.");
                }

                if (newPass1 == null || newPass2 == null || !newPass1.equals(newPass2))
                {
                   throw new RuntimeException("New password entries do not match each other!");
                }

                if (newPass1.equals(oldPass))
                {
                   throw new RuntimeException("New password must differ from old password!");
                }

                // basic password policy

                boolean valid = true;

                int len = newPass1.length();

                if (len < 6)
                {
                   valid = false;
                }

                if (valid)
                {
                   boolean letter = false;
                   boolean digit = false;
                   for (int i = 0; i < len && !valid; i++)
                   {
                      char c = newPass1.charAt(i);
                      letter = letter || Character.isLetter(c);
                      digit = digit || Character.isDigit(c);
                      valid = letter && digit;
                   }
                }

                if (!valid)
                {
                   throw new RuntimeException(
                      "A valid password is at minimum 6 characters long and contains at least " +
                      "one letter and one digit");
                }

                // update user record to change salt, password, and password changed date
                try (PreparedStatement ps = conn.prepareStatement(sqlUsrPassChg);)
                {
                   PasswordHelper hlp = new PasswordHelper();
                   byte[] salt = hlp.salt();
                   byte[] encPass = hlp.encryptPassword(newPass1, salt);

                   ps.setBytes(1, salt);
                   ps.setBytes(2, encPass);
                   ps.setLong(3, user.getId());
                   ps.execute();
                   ps.clearParameters();
                }

                conn.commit();
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Given the name of a schema export file AST, strip out the {@code namespace} portion of the
        * path, and append a {@code .df} extension.
        *
        * @param   name
        *          Original name.
        *
        * @return  Adjusted name.
        */
       private String stripSchemaName(String name)
       {
          name = name.substring(0, name.lastIndexOf('.'));
          String[] parts = name.split("/");
          int len = parts.length;
          if (parts[len - 2].equalsIgnoreCase("namespace"))
          {
             StringBuilder buf = new StringBuilder();
             for (int i = 0; i < len; i++)
             {
                if (i != len - 2)
                {
                   buf.append(parts[i]);
                   if (i < len - 1)
                   {
                      buf.append("/");
                   }
                }
             }

             buf.append(".df");
             name = buf.toString();
          }

          return name;
       }

       /**
        * Gather the full list of filenames of the specified type from the database file map table.
        *
        * @param   conn
        *          Database connection.
        * @param   applyFilter
        *          {@code True} to apply the current file filter when gathering the list of files;
        *          {@code false} to gather all files.
        * @param   srcType
        *          Type of files (schema or code).
        *
        * @return  A file list containing the filenames.
        *
        * @throws  SQLException
        *          if there was an error with the database query.
        */
       private FileList gatherFiles(Connection conn, boolean applyFilter, int srcType)
       throws SQLException
       {
          String suffix;
          switch (srcType)
          {
             case SRC_SCHEMA:
                suffix = ".dict";
                break;
             case SRC_CODE_CACHE:
                suffix = ".ast";
                break;
             default:
                throw new RuntimeException("Unexpected source file type: " + srcType);
          }

          String sql = applyFilter ? sqlQryFltFiles : sqlQryFiles;

          try (PreparedStatement ps = conn.prepareStatement(sql))
          {
             List<String> names = new ArrayList<>();
             if (applyFilter)
             {
                ps.setLong(1, sessionId);
                ps.setInt(2, srcType);
             }
             else
             {
                ps.setInt(1, srcType);
             }

             ResultSet rs = ps.executeQuery();
             while (rs.next())
             {
                String name = rs.getString(1);

                // special handling for DF files (I know, ugly)
                if (srcType == SRC_SCHEMA && name.toLowerCase().endsWith(".df"))
                {
                   name = name.substring(0, name.lastIndexOf('.'));
                   String[] parts = name.split("/");
                   StringBuilder buf = new StringBuilder();
                   int len = parts.length;
                   for (int i = 0; i < len - 1; i++)
                   {
                      buf.append(parts[i]);
                      buf.append("/");
                   }
                   buf.append("namespace/");
                   buf.append(parts[len - 1]);
                   name = buf.toString();
                }

                name += suffix;
                name = Configuration.forceHome(name);
                names.add(name);
             }

             return new ExplicitFileList(names.toArray(new String[names.size()]));
          }
       }

       /**
        * Flatten an AST into a list of {@link AstNode} objects suitable for marshalling and
        * transport to the web client. A large, hierarchical AST structure does not marshall well
        * over Jackson (results in {@code StackOverflowException} due to recursion), so we create
        * a flat list of simplified beans instead.
        * <p>
        * Also compute additional information during this process needed by the web client, such as
        * effective starting and ending line and column numbers.
        *
        * @param   ast
        *          AST to be marshalled.
        *
        * @return  List of {@link AstNode} objects, one per node in the original tree.
        */
       private List<AstNode> prepareAstNodes(Aast ast)
       {
          List<AstNode> list = new ArrayList<>();

          new AstWalkListener()
          {
             private Deque<AstNode> parents = new ArrayDeque<>();

             private Deque<Coord[]> coords = new ArrayDeque<>();

             private AstNode parent = null;

             private AstNode node = null;

             private Coord[] range = new Coord[2];

             private Iterator<Aast> iter = ast.iterator(0, this);

             {
                while (iter.hasNext())
                {
                   Aast next = iter.next();
                   node = new AstNode();
                   list.add(node);

                   String text = next.getText();

                   // TODO: account for multi-line text
                   int l0 = next.getLine();
                   int l1 = l0;

                   int c0 = next.getColumn();
                   int c1 = c0 + (c0 > 0 ? Math.max(text.length() - 1, 0) : 0);

                   if (l0 > 0)
                   {
                      Coord start = new Coord(l0, c0);
                      if (range[0] == null || start.compareTo(range[0]) < 0)
                      {
                         range[0] = start;
                      }

                      Coord end = new Coord(l1, c1);
                      if (range[1] == null || end.compareTo(range[1]) > 0)
                      {
                         range[1] = end;
                      }
                   }

                   node.setId(next.getId());
                   node.setType(next.getType());
                   node.setText(text);
                   node.setActualStartLine(l0);
                   node.setActualStartColumn(c0);
                   node.setActualEndLine(l1);
                   node.setActualEndColumn(c1);
                   node.setEffectiveStartLine(l0);
                   node.setEffectiveStartColumn(c0);
                   node.setEffectiveEndLine(l1);
                   node.setEffectiveEndColumn(c1);

                   Aast parent = next.getParent();
                   if (parent != null)
                   {
                      node.setParentId(parent.getId());
                   }

                   Map<String, Object> notes = null;
                   Iterator<String> keyIter = next.annotationKeys();
                   while (keyIter.hasNext())
                   {
                      if (notes == null)
                      {
                         notes = new LinkedHashMap<>();
                         node.setNotes(notes);
                      }
                      String key = keyIter.next();
                      Object val = next.getAnnotation(key);
                      notes.put(key, val);
                   }
                }
             }

             @Override
             public void descent(Aast ast)
             {
                if (parent ==  null)
                {
                   // parent of root is null; will not be used, so push an empty one to avoid NPE
                   parents.push(new AstNode());
                }
                else
                {
                   parents.push(parent);
                }
                parent = node;
                coords.push(range);
                range = new Coord[2];
             }

             @Override
             public void ascent(Aast ast)
             {
                Coord start = range[0];
                Coord end = range[1];

                range = coords.pop();

                if (start != null && end != null)
                {
                   Coord pStart = new Coord(parent.getEffectiveStartLine(),
                                            parent.getEffectiveStartColumn());
                   if (pStart.line == 0 || start.compareTo(pStart) < 0)
                   {
                      parent.setEffectiveStartLine(start.line);
                      parent.setEffectiveStartColumn(start.column);
                   }

                   Coord pEnd = new Coord(parent.getEffectiveEndLine(),
                                          parent.getEffectiveEndColumn());
                   if (pEnd.line == 0 || end.compareTo(pEnd) > 0)
                   {
                      parent.setEffectiveEndLine(end.line);
                      parent.setEffectiveEndColumn(end.column);
                   }

                   Coord startParent = range[0];
                   Coord endParent = range[1];

                   if (startParent == null || start.compareTo(startParent) < 0)
                   {
                      range[0] = start;
                   }
                   if (endParent == null || end.compareTo(endParent) > 0)
                   {
                      range[1] = end;
                   }
                }

                parent = parents.pop();
             }

             @Override
             public void nextChild(Aast ast, int index)
             {
             }
          };
          /*
          System.out.println(ast.dumpTree());

          for (AstNode n : list)
          {
             System.out.println(n.getText() +
                                " [" + n.getEffectiveStartLine() + ":" + n.getEffectiveStartColumn() + "-" +
                                n.getEffectiveEndLine() + ":" + n.getEffectiveEndColumn() + "] (" +
                                n.getId() + "/" + n.getParentId() + ")");
          }
          */
          return list;
       }

       /**
        * Query and return information necessary to produce the master report of overview results
        * for all summary reports of the given type.
        *
        * @param   type
        *          Type of reports ({@code SRC_CODE_CACHE} or {@code SRC_SCHEMA}).
        *
        * @return  Array of {@link OverviewRow} objects, each providing the high level match
        *          statistics for its associated summary report.
        */
       private OverviewRow[] masterReport(int type)
       {
          OverviewRow[] results = cachedMasterResults.get(type);
          if (results != null)
          {
             return results;
          }

          try (Connection conn = dbs.openConnection())
          {
             List<OverviewRow> list = new ArrayList<>();

             boolean includeAll = filterId == defaultFilterId;
             String sql = (includeAll ? sqlQryMasterAll : sqlQryMasterAgg);

             try (PreparedStatement ps = conn.prepareStatement(sql))
             {
                // the two prepared statements for this report take different substitution
                // parameters
                if (includeAll)
                {
                   ps.setInt(1, type);
                }
                else
                {
                   ps.setLong(1, sessionId);
                   ps.setInt(2, type);
                   ps.setInt(3, type);
                }

                ResultSet rs = ps.executeQuery();
                while (rs.next())
                {
                   OverviewRow row = new OverviewRow();
                   row.setId(rs.getInt(1));
                   row.setTitle(rs.getString(2));
                   row.setMatches(rs.getInt(3));
                   row.setFiles(rs.getInt(4));
                   row.setTag(rs.getString(5));
                   rs.getString(6);
                   row.setSupport(!rs.wasNull());
                   row.setCondition(rs.getString(7));
                   list.add(row);
                }

                conn.commit();

                results = list.toArray(new OverviewRow[0]);

                cachedMasterResults.put(type, results);

                return results;
             }
             catch (SQLException exc)
             {
                if (conn != null)
                {
                   conn.rollback();
                }

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /**
        * Persist a (presumably successful) search/report TRPL expression.
        *
        * @param   conn
        *          Database connection.
        * @param   criteria
        *          TRPL expression to be persisted.
        * @throws  SQLException
        *          if a database error occurs.
        */
       private void persistSearchHistory(Connection conn, String criteria)
       throws SQLException
       {
          // compose sql to insert into search_history table, if not already present
          StringBuilder buf = new StringBuilder();
          buf.append("insert into search_history ");
          buf.append("select * from (select next value for seq_search_history_pk, '");
          buf.append(criteria);
          buf.append("' ) x where not exists (select 1 from search_history where criteria = ?)");
          Object histArg = criteria;
          String sqlInsSrchHist = buf.toString();

          // compose sql to insert into user_search table, if not already present
          buf.setLength(0);
          buf.append("insert into user_search select * from (select ");
          buf.append(user.getId());
          buf.append(" as u, select id from search_history where criteria = ?");
          buf.append(" as s) x where exists (select 1 from search_history where criteria = ?");
          buf.append(") and not exists (select 1 from user_search where uid = x.u and sid = x.s)");
          Object usrArg = criteria;
          String sqlInsUsrSrch = buf.toString();

          //tested with H2 1.4.200 and prepared statements now work with subselects
          try (PreparedStatement stmt = conn.prepareStatement(sqlInsSrchHist))
          {
             stmt.setObject(1, histArg);
             stmt.execute();
          }

          try (PreparedStatement stmt = conn.prepareStatement(sqlInsUsrSrch))
          {
             stmt.setObject(1, usrArg);
             stmt.setObject(2, usrArg);
             stmt.execute();
           }
       }

       /**
        * This class represents a specific location within a source file, as represented by the line
        * and column number of a particular character.
        */
       private static class Coord
       implements Comparable<Coord>
       {
          /** Line number */
          private final int line;

          /** Column number */
          private final int column;

          /**
           * Constructor.
           *
           * @param   line
           *          Line number.
           * @param   column
           *          Column number.
           */
          Coord(int line, int column)
          {
             this.line = line;
             this.column = column;
          }

          /**
           * Compare this line and column location with another. A coordinate is considered greater
           * than another if it is located at a higher line number, or, if within the same line, at
           * a higher column number.
           *
           * @param   o
           *          Coordinate instance to compare with this one.
           *
           * @return  A negative number if this coordinate is less than the specified one;
           *          0 if both coordinates represent the same location in a source file;
           *          a positive number if this coordinate is greater than the specified one.
           */
          @Override
          public int compareTo(Coord o)
          {
             if (this.line < o.line)
             {
                return -1;
             }

             if (this.line > o.line)
             {
                return 1;
             }

             if (this.column < o.column)
             {
                return -1;
             }

             if (this.column > o.column)
             {
                return 1;
             }

             return 0;
          }
       }
    }