# ReportWorker.java

    /*
    ** Module   : ReportWorker.java
    ** Abstract : provides an interface for maintaining sets of statistics from
    **            pattern engine rulesets
    **
    ** Copyright (c) 2005-2023, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- --JPRM-- ---------------------------------Description----------------------------------
    ** 001 GES 20050225   @20124 Created initial version supporting functions
    **                           for manually and automatically creating/
    **                           matching statistics as well as reporting
    **                           functions for summary and detailed reports.
    ** 002 GES 20050303   @20197 Second pass. Converted from file indices to
    **                           embedded filenames and a major rewrite with
    **                           generation of HTML output using the Apache
    **                           Element Construction Set (ECS). Also added
    **                           sorting options for the summary stats,
    **                           parser type output in detailed records and
    **                           the ability to store user-defined text as
    **                           the match text.
    ** 003 GES 20050310   @20284 Minor change to provide control over source
    **                           file cross-reference generation.
    ** 004 GES 20050311   @20291 Fixed matches comparator to make the equals
    **                           behavior correct (was eliminating all but
    **                           1 entry that had the same number of matches).
    **                           Also added a check in copyToHtml() to
    **                           bypass this if the target exists and is
    **                           later in timestamp than the source.
    ** 005 GES 20050316   @20399 Converted this worker to use the visitAst()
    **                           interface in PatternWorker instead of using
    **                           a rule driven approach. Also added AST spec
    **                           output to the footer.
    ** 006 GES 20050318   @20429 Made a minor change to move dump tree
    **                           support into AnnotatedAst and use that
    **                           common method.
    ** 007 GES 20050318   @20442 Added support to save off the condition
    **                           during match processing and to read those
    **                           conditions back and write them into the
    **                           footer of a details report.
    ** 008 ECF 20050405   @20647 Added second addMatchMultiplexed method. This
    **                           version accepts custom name and type strings
    **                           rather than extracting the name from the
    **                           current AST's token type.
    ** 009 ECF 20050430   @20973 Modified due to changes in pattern package.
    **                           A PatternEngine reference is no longer
    **                           maintained, but is retrieved from the
    **                           AstSymbolResolver instead when needed.
    ** 010 ECF 20050526   @21290 Changes to support new expression engine
    **                    @21305 implementation. Changed library registration
    **                           mechanism based on superclass' modifications
    **                           to support single library per pattern worker
    **                           limit. This involved merging the Stats and
    **                           Report inner classes.
    ** 011 NVS 20050531   @21333 addMatchMultiplexed method which takes a cus-
    **                           tom name now translates the name so it can't
    **                           contain slashes. Slashes would produce a file
    **                           name that is impossible to create. The origi-
    **                           nal name is still used as a long name in the
    **                           report, because it's safe.
    ** 012 ECF 20050722   @21820 Added statistical analysis user functions.
    **                           The methods maximum, minimum, median, mean,
    **                           and standardDeviation implement user
    **                           functions which are backed by methods of the
    **                           same names in StatisticsHelper.
    ** 013 ECF 20050727   @21893 Added loadDistributions and population user
    **                           functions. The former loads a map of
    **                           distribution data from an XML document. The
    **                           latter accumulates total population size of a
    **                           distribution.
    ** 014 GES 20060129   @24130 FileList usage changes.
    ** 015 GES 20070514   @33504 Added a reset statistics method to enable
    **                           running multiple different reports in the
    **                           same pipeline. Added support for generating
    **                           a master report index. Added a way to
    **                           override the current condition for a report.
    **                           Only a single source file index is being
    **                           emitted now (with a std name).
    ** 016 GES 20070626   @34287 If file_xref is active, write the source
    **                           file index link into each page footer instead
    **                           of the AST list. The AST list is a fallback
    **                           if file_xref is off. Also fixed a minor
    **                           formatting issue with the AST list text.
    **                           Made a small improvement to the timestamp
    **                           formatting. Added support to control the
    **                           filename of the source code file cross-ref.
    **                           Added mutual-exclusion flag to support the
    **                           calc and display of percentages in the
    **                           summary report. Fixed the URL and filenames
    **                           used for detail reports when the text
    **                           contained invalid characters.
    ** 017 GES 20070627   @34297 Custom report creation based on user defined
    **                           data, but leaving the report formatting and
    **                           HTML work to this worker.
    ** 018 GES 20070628   @34320 Made inner class static otherwise it pins
    **                           all StatsWorker (and PatternEngine...) etc
    **                           into memory. This is because a non-static
    **                           inner class has an implicit reference to
    **                           its containing class' instance (this). This
    **                           greatly reduces the working set of reports
    **                           created via more than 1 pattern engine run.
    ** 019 ECF 20070703   @34355 Fixed file resource leak. Output streams were
    **                           not being closed after reports were written.
    ** 020 GES 20070710   @34436 Implemented a better algorithm to determine
    **                           an artificial node's line/column number.
    **                           Handled two other cases of file leaks (see
    **                           H019).
    ** 021 GES 20070711   @34477 Rewrite to replace ECS with our own HTML
    **                           helpers. This is needed because the ECS
    **                           approach of building the entire output in
    **                           memory before streaming it out to file is
    **                           extremely memory intensive on large reports.
    **                           This approach leads to out of memory errors
    **                           so we are moving to a "stream it as you go"
    **                           to eliminate this issue.
    ** 022 GES 20070713   @34493 Each report (summary and its associated
    **                           details) are now created in a separate and
    **                           unique subdirectory.
    ** 023 GES 20070714   @34496 Long detail reports are now paginated to
    **                           ensure that they are never too big to load
    **                           and use in a browser.
    ** 024 GES 20070716   @34529 Rework to move source file copy processing
    **                           to a delegated model so that this code
    **                           doesn't have to embed knowledge of each
    **                           possible source file type. Instead, an object
    **                           is registered to provide the AST-specific
    **                           processing needed. This allows non-4GL source
    **                           ASTs to be processed via the reporting code.
    ** 025 GES 20070720   @34645 Default page size is now 100. Added columns
    **                           to the table of contents for paginated
    **                           reports. These show the start and end files
    **                           for each page.  Made the auto-generated
    **                           multiplex text for a statistic more
    **                           descriptive.
    ** 026 GES 20080309   @37670 Switched to using character readers instead
    **                           of byte streams. This handles I18N needs.
    ** 027 GES 20090429   @42069 Match package and class name changes.
    ** 028 GES 20090515   @42237 Moved to AstManager from AstRegistry/AstPersister.
    ** 029 ECF 20111206          Hastily hacked in temporary database support
    **                           until we can do a better job of this.
    ** 030 GES 20111207          Fixed issue with generateSafeFilename() where the
    **                           resulting filename was too long for the file sys.
    **                           Made master index processing persist its state
    **                           across multiple JVM relaunches. This allows a
    **                           restart of the reporting batch process after a
    **                           failure or other abend and still the master index
    **                           can be generated correctly after the last report
    **                           (it will contain all of the reports done in the
    **                           batch even if there were restarts).
    ** 031 GES 20130203          Major rewrite to process all reports in a single
    **                           pattern engine run. This cuts down reporting
    **                           time by 80%! To accomplish this, all potentially
    **                           large data has been moved into the database and
    **                           all the data has been made safe for this by
    **                           multiplexing using a report-specific ID. Renamed
    **                           the class to be a more accurate description.
    ** 032 GES 20140203          Better default processing when encountering a node that is
    **                           unrecognized.
    ** 033 EVL 20160223          Javadoc fixes to make compatible with Oracle Java 8 for Solaris 10.
    ** 034 IAS 20160331          Add input streams' close
    ** 035 GES 20160311          Added support level processing (new columns in the summary report).
    ** 036 CA  20170113          Fixed report generation on windows.
    ** 037 GES 20170304          Added cvt_lvl_basic as a new support level.
    **         20170307          Fixed a long standing bug where detail report names could conflict
    **                           and only the last one emitted would be available.
    **     ECF 20170309          Added MV_STORE=FALSE to JDBC URL to improve performance.
    ** 038 ECF 20170501          Refactoring to surface reports via a web interface. Tree walking and
    **                           the matching of logic are still driven from TRPL, but now, instead of
    **                           generating static HTML reports, we persist additional state in the
    **                           database to allow the report presentation to be composed dynamically.
    ** 039 CA  20170922          Fixed a path compatibility when generating the reports on windows.
    ** 040 ECF 20180823          Added MVCC=FALSE to JDBC URL for upgrade to H2 1.4.197.
    ** 041 AIL 20200906          Dropped MVCC=FALSE for upgrade to H2 1.4.200.
    **     ECF 20200929          Replaced report_cell column name ("row" apparently became a reserved work in
    **                           the latest version of H2).
    **     CA  20211214          The AST manager plugin must be context-local, for runtime conversion, as the
    **                           InMemoryRegistryPlugin is not thread-safe.
    **     RAA 20230109          Changed inline statement(s) to prepared statement(s).
    ** 042 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;

    import java.io.*;
    import java.security.*;
    import java.security.spec.*;
    import java.sql.*;
    import java.util.*;
    import java.util.logging.*;
    import javax.xml.parsers.*;

    import com.goldencode.p2j.convert.*;
    import org.xml.sax.*;
    import com.goldencode.ast.*;
    import com.goldencode.p2j.cfg.*;
    import com.goldencode.p2j.pattern.*;
    import com.goldencode.p2j.report.server.*;
    import com.goldencode.p2j.report.server.FileFilter;
    import com.goldencode.util.*;

    /**
     * Provides users of the <code>PatternEngine</code> with a set of
     * user-defined functions and variables for manually and/or automatically
     * creating categories, adding matches as well as functions to generate both
     * summary and detailed reports.
     */
    public class ReportWorker
    extends AbstractPatternWorker
    implements ReportConstants
    {
       /** Logger. */
       private static final ConversionStatus LOG = ConversionStatus.get(ReportWorker.class);

       /** Cache file for master index and categories data. */
       public static final String MASTER_INDEX_CACHE = "master_index.cache";

       /** Key for the source file annotation in a root AST. */
       private static final String NOTE_SRCFILE = "srcfile";

       /** File name extension for a preprocessed source file. */
       private static final String EXT_CACHE = ".cache";

       /** Prefix for a relative file path. */
       private static final String REL_PREFIX = "." + Configuration.P2J_FILE_SEP;

       /** Stores all tags used in the master index of reports. */
       private static Map<String, Long> tags = null;

       /** Default database user. */
       private static String dbUser = "admin";

       /** Default database password. */
       private static String dbPass = "admin";

       /** Stores summary report data to build a master index of reports. */
       private static Map<String, Integer> levelConstants = new HashMap<>();

       /** Database URL */
       private String dbURL = null;

       /** Quick access to the registry for AST ID and filename lookups. */
       private AstManager mgr = null;

       /** Filename to ID mappings */
       private Map<String, Long> file2ID = new HashMap<>();

       /** ID to filename mappings */
       private Map<Long, String> id2File = new HashMap<>();

       /** Database connection */
       private Connection connection = null;

       /** Database file insert statement. */
       private PreparedStatement stmtFileInsert = null;

       /** Database ast_map insert statement. */
       private PreparedStatement stmtAstMapInsert = null;

       /** Database source_line insert statement. */
       private PreparedStatement stmtSrcLineInsert = null;

       /** Database tag insert statement. */
       private PreparedStatement stmtTagInsert = null;

       /** Database report insert statement. */
       private PreparedStatement stmtReportInsert = null;

       /** Database report_tag insert statement. */
       private PreparedStatement stmtRptTagInsert = null;

       /** Database report_column insert statement. */
       private PreparedStatement stmtRptColInsert = null;

       /** Database report_cell insert statement. */
       private PreparedStatement stmtRptCellInsert = null;

       /** Database report_stats insert statement. */
       private PreparedStatement stmtRptStatsInsert = null;

       /** Database match insert statement. */
       private PreparedStatement stmtMatchInsert = null;

       /** Database match query for records by match category. */
       private PreparedStatement stmtMatchQueryByStat = null;

       /** Database match category insert statement. */
       private PreparedStatement stmtMatchCatInsert = null;

       /** Database filter_profile insert statement. */
       private PreparedStatement stmtFltPrfInsert = null;

       /** Database filter_profile update statement. */
       private PreparedStatement stmtFltPrfUpdate = null;

       /** Database file_filter insert statement. */
       private PreparedStatement stmtFileFltInsert = null;

       /** Database file_filter delete statement. */
       private PreparedStatement stmtFileFltDelete = null;

       /** Database file ids query statement. */
       private PreparedStatement stmtFileQuery = null;

       /** Database file id from file name query statement. */
       private PreparedStatement stmtFidByFilename = null;

       /** Database match and file all stats by report query statement */
       private PreparedStatement stmtAllStatsQueryByRpt = null;

       /** Database match and file stats by report query statement */
       private PreparedStatement stmtStatsQueryByRpt = null;

       /** Database delete stats by report statement */
       private PreparedStatement stmtDelRptStats = null;

       /** Flag indicating whether database table has been analyzed */
       private boolean analyzed = false;

       static
       {
          levelConstants.put("lvl_unknown"       , LVL_UNKNOWN);
          levelConstants.put("cvt_lvl_mask"      , CVT_LVL_MASK);
          levelConstants.put("cvt_lvl_none"      , CVT_LVL_NONE);
          levelConstants.put("cvt_lvl_partial"   , CVT_LVL_PARTIAL);
          levelConstants.put("cvt_lvl_basic"     , CVT_LVL_BASIC);
          levelConstants.put("cvt_lvl_full_restr", CVT_LVL_FULL_RESTR);
          levelConstants.put("cvt_lvl_full"      , CVT_LVL_FULL);
          levelConstants.put("rt_lvl_mask"       , RT_LVL_MASK);
          levelConstants.put("rt_lvl_none"       , RT_LVL_NONE);
          levelConstants.put("rt_lvl_stub"       , RT_LVL_STUB);
          levelConstants.put("rt_lvl_untested"   , RT_LVL_UNTESTED);
          levelConstants.put("rt_lvl_partial"    , RT_LVL_PARTIAL);
          levelConstants.put("rt_lvl_basic"      , RT_LVL_BASIC);
          levelConstants.put("rt_lvl_full_restr" , RT_LVL_FULL_RESTR);
          levelConstants.put("rt_lvl_full"       , RT_LVL_FULL);
       }

       /**
        * Default constructor which calls the super-class constructor, registers
        * its libraries and initializes its instance members.
        *
        * @throws  AstException
        *          if the AST manager has not been initialized.
        */
       public ReportWorker()
       throws AstException
       {
          super();

          Library helper = new Library();
          setLibrary(helper);

          mgr = AstManager.get();
       }

       /**
        * Constructor which accepts an externally provided database connection.
        *
        * @param   connection
        *          Database connection.
        */
       public ReportWorker(Connection connection)
       {
          super();

          prepareStatements(connection, true);
       }

       /**
        * Save a report definition in the report database.
        *
        * @param   rpt
        *          Report definition to be saved.
        * @param   databaseMode
        *          {@code True} if this is a schema report; {@code false} if a code report.
        * @param   processTags
        *          {@code True} to process report category tags; else {@code false}.
        *
        * @return  Unique ID assigned to the newly defined report.
        *
        * @throws  SQLException
        *          if there is any database error.
        */
       public long saveReport(ReportDefinition rpt, boolean databaseMode, boolean processTags)
       throws SQLException
       {
          if (rpt.id > -1L)
          {
             // update an existing report
             // TODO: implement
          }
          else
          {
             // save a new report

             stmtReportInsert.setBoolean(1, false);
             stmtReportInsert.setInt(2, databaseMode ? SRC_SCHEMA : SRC_CODE_CACHE);
             stmtReportInsert.setString(3, rpt.title);
             stmtReportInsert.setString(4, rpt.condition);
             stmtReportInsert.setString(5, rpt.conditionDescr);
             stmtReportInsert.setString(6, rpt.multiplexExpr);
             stmtReportInsert.setString(7, rpt.dumpType);
             stmtReportInsert.setString(8, rpt.dumpExpr);
             stmtReportInsert.setInt(9, rpt.dumpLevel);
             stmtReportInsert.setString(10, rpt.supportLvlExpr);
             stmtReportInsert.setBoolean(11, rpt.user);
             stmtReportInsert.execute();
             stmtReportInsert.clearParameters();

             // read back the id that was autogenerated
             ResultSet rs = stmtReportInsert.getGeneratedKeys();
             PreparedStatement stmt = null;
             rs.next();
             rpt.id = rs.getLong(1);
             List<Object> args = new ArrayList<>();

             if (processTags && rpt.tags != null && !rpt.tags.isEmpty())
             {
                StringBuilder buf = new StringBuilder("select id, name from tag where name in(");
                Iterator<String> iter = rpt.tags.iterator();
                for (int i = 0; iter.hasNext(); i++)
                {
                   if (i > 0)
                   {
                      buf.append(", ");
                   }
                   String tag = iter.next();
                   tag.replaceAll("\'", "\\\'");
                   buf.append("?");
                   args.add(tag);
                }
                buf.append(")");
                String sql = buf.toString();

                Map<String, Long> tagMap = new HashMap<>();
                stmt = connection.prepareStatement(sql);
                for (int i = 0; i < args.size(); i++)
                {
                   stmt.setObject(i + 1, args.get(i));
                }

                rs = stmt.executeQuery();
                while (rs.next())
                {
                   long tid = rs.getLong(1);
                   String name = rs.getString(2);
                   tagMap.put(name, tid);
                }

                for (String tag : rpt.tags)
                {
                   if (!tagMap.containsKey(tag))
                   {
                      stmtTagInsert.setString(1, tag);
                      stmtTagInsert.execute();
                      stmtTagInsert.clearParameters();

                      // read back the id that was autogenerated
                      rs = stmtTagInsert.getGeneratedKeys();
                      rs.next();
                      long tid = rs.getLong(1);

                      tagMap.put(tag, tid);
                   }
                }

                for (String tag : rpt.tags)
                {
                   Long tid = tagMap.get(tag);
                   stmtRptTagInsert.setLong(1, rpt.id);
                   stmtRptTagInsert.setLong(2, tid);
                   stmtRptTagInsert.execute();
                   stmtRptTagInsert.clearParameters();
                }
             }

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

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

          return rpt.id;
       }

       /**
        * Save a file filter profile in the report database
        *
        * @param   profile
        *          The profile to save. If this profile already has a valid primary key ID, update
        *          the existing profile record and replace all associated file filters; otherwise,
        *          insert a new profile record and insert all associated file filters.
        * @param   filters
        *          The filters associated with the profile.
        *
        * @return  Unique ID of the filter profile.
        *
        * @throws  SQLException
        *          if there is any database error.
        * @throws  RuntimeException
        *          if the filter profile record could not be updated or inserted.
        */
       public long saveFilterProfile(FilterProfile profile, FileFilter[] filters)
       throws SQLException
       {
          long pid = profile.getId();

          int uc;
          String name = profile.getName();

          if (pid > -1L)
          {
             // update an existing profile
             stmtFltPrfUpdate.setString(1, name);
             stmtFltPrfUpdate.setLong(2, pid);
             stmtFltPrfUpdate.execute();
             stmtFltPrfUpdate.clearParameters();
             uc = stmtFltPrfUpdate.getUpdateCount();

             if (uc == 1)
             {
                // delete all filters associated with profile; they will be replaced below
                stmtFileFltDelete.setLong(1, pid);
                stmtFileFltDelete.execute();
                stmtFileFltDelete.clearParameters();
             }
          }
          else
          {
             // save a new profile
             stmtFltPrfInsert.setString(1, name);
             stmtFltPrfInsert.setBoolean(2,false);
             stmtFltPrfInsert.execute();
             stmtFltPrfInsert.clearParameters();
             uc = stmtFltPrfInsert.getUpdateCount();

             // read back the id that was autogenerated
             ResultSet rs = stmtFltPrfInsert.getGeneratedKeys();
             rs.next();
             pid = rs.getLong(1);
             rs.close();
          }

          if (uc != 1)
          {
             throw new RuntimeException("Filter profile '" + name + "' could not be saved");
          }

          // save the associated filters
          for (FileFilter filter : filters)
          {
             stmtFileFltInsert.setLong(1, pid);
             stmtFileFltInsert.setBoolean(2, filter.isWild());
             stmtFileFltInsert.setString(3, filter.getSpec());
             stmtFileFltInsert.execute();
             stmtFileFltInsert.clearParameters();
          }

          return pid;
       }

       /**
        * This method is called each time the pattern engine needs to resolve
        * a string constant into a numeric, boolean, or string literal value.
        * Currently, it maps support level constants to integer bitmasks.

        * @param   constant
        *          A case-insensitive support level name to be resolved to a bitmask.
        *
        * @return  The <code>Integer</code> value associated with the given constant
        *          or <code>null</code> if there is no matching constant.
        */
       @Override
       public Object resolveConstant(String constant)
       {
          return levelConstants.get(constant.toLowerCase());
       }

       /**
        * Opens a connection to the H2 database (in the ./rptdb/rptdb file) and creates all needed
        * database tables and indices.
        *
        * @throws  RuntimeException
        *          if there were errors during database creation.
        */
       private void createDatabaseTables()
       {
          Statement stmt = null;

          try
          {
             // this connection stays open for the life of the report run;  it is closed when TRPL
             // calls Library.cleanupDatabase()
             openConnection(false);
             stmt = connection.createStatement();

             // TODO: enable incremental report runs where we don't drop all tables up front
             String drop = "drop table if exists " +
                           "user, " +
                           "file, " +
                           "ast_map, " +
                           "source_line, " +
                           "tag, " +
                           "report, " +
                           "report_tag, " +
                           "filter_profile, " +
                           "file_filter, " +
                           "report_column, " +
                           "report_cell, " +
                           "report_stats, " +
                           "match, " +
                           "category, " +
                           "search_history, " +
                           "user_search " +
                           "cascade";
             stmt.execute(drop);
             drop = "drop sequence if exists seq_search_history_pk";
             stmt.execute(drop);

             String create, index;

             // create user table
             create =
                "create table user (id identity, name varchar not null unique, " +
                "salt binary(8), pass binary(160), admin boolean not null, pass_changed date)";
             stmt.execute(create);

             // create file table
             create = "create table file (id identity, name varchar)";
             stmt.execute(create);
             index = "create index idx_file_1 on file (name)";
             stmt.execute(index);

             // create ast_map table
             create =
                "create table ast_map (fid bigint, astid bigint, type int, " +
                "constraint fk_astmap_fid foreign key(fid) references file(id))";
             stmt.execute(create);

             // create source_line table
             create =
                "create table source_line " +
                "(id identity, fid bigint, type int, line int, data varchar, " +
                "constraint fk_srcline_fid foreign key(fid) references file(id))";
             stmt.execute(create);

             // create tag table
             create = "create table tag (id identity, name varchar)";
             stmt.execute(create);

             // create report table
             create =
                "create table report (id identity, custom boolean, type int, title varchar, " +
                "condition varchar, conddesc varchar, multiplex varchar, " +
                "dumptype varchar, dumpexpr varchar, dumplevel int, support varchar, user boolean)";
             stmt.execute(create);

             // create report_tag table
             create =
                "create table report_tag (rid bigint, tid bigint, " +
                "constraint fk_rpttag_rptid foreign key(rid) references report(id) " +
                "on delete cascade, " +
                "constraint fk_rpttag_tagid foreign key(tid) references tag(id))";
             stmt.execute(create);

             // create filter_profile table
             create = "create table filter_profile (id identity, default boolean, name varchar)";
             stmt.execute(create);

             index = "create unique index idx_filter_profile_1 on filter_profile (name)";
             stmt.execute(index);

             // create file_filter table
             create =
                "create table file_filter (pid bigint, wild boolean, spec varchar, " +
                "constraint fk_fflt_prfid foreign key(pid) references filter_profile(id) " +
                "on delete cascade)";
             stmt.execute(create);

             // create report_column table
             create =
                "create table report_column (" +
                "id identity, rid bigint, idx int, heading varchar, format varchar, sorting int, " +
                "constraint fk_rptcol_rid foreign key(rid) references report(id))";
             stmt.execute(create);

             // create report_cell table
             create =
                "create table report_cell (rcid bigint, rowpos int, text varchar, num double, " +
                "constraint fk_rptcell_rcid foreign key(rcid) references report_column(id))";
             stmt.execute(create);

             // create report_stats table
             create =
                "create table report_stats (rid bigint, matches int, files int, " +
                "constraint fk_rptsts_rid foreign key(rid) references report(id))";
             stmt.execute(create);

             // create category table
             create =
                "create table category (" +
                "id identity, rid bigint, name varchar, descr varchar, lvl int, " +
                "constraint fk_category_rid foreign key(rid) references report(id) " +
                "on delete cascade)";
             stmt.execute(create);

             // create match table
             create =
                "create table match (" +
                "id identity, cid bigint, fid bigint, line int, col int, lvl int, text varchar, " +
                "constraint fk_match_catcid foreign key(cid) references category(id) " +
                "on delete cascade, " +
                "constraint fk_match_fileid foreign key(fid) references file(id))";
             stmt.execute(create);

             index = "create index idx_report_1 on report (type asc)";
             stmt.execute(index);

             index = "create index idx_match_1 on match (cid asc, fid asc)";
             stmt.execute(index);

             index = "create index idx_category_1 on category (rid asc, id asc)";
             stmt.execute(index);

             // create search_history table
             create =
                "create table search_history (id bigint primary key, " +
                "criteria varchar not null unique)";
             stmt.execute(create);

             // create search_history primary key sequence
             create = "create sequence seq_search_history_pk";
             stmt.execute(create);

             // create user_search table
             create =
                "create table user_search (uid bigint not null, sid bigint not null, " +
                "constraint fk_usrsrch_userid foreign key(uid) references user(id) " +
                "on delete cascade, " +
                "constraint fk_usrsrch_srchhistid foreign key(sid) references search_history(id) " +
                "on delete cascade)";
             stmt.execute(create);

             connection.commit();
          }
          catch (Exception exc)
          {
             try
             {
                connection.rollback();
             }
             catch (SQLException sqle)
             {
                LOG.log(Level.SEVERE, "", sqle);
             }

             throw new RuntimeException("Error preparing database", exc);
          }
          finally
          {
             if (stmt != null)
             {
                try
                {
                   stmt.close();
                }
                catch (SQLException exc)
                {
                   LOG.log(Level.SEVERE, "", exc);
                }
             }
          }
       }

       /**
        * Create all prepared statements needed to run reports.
        *
        * @param   provided
        *          Optional database connection. If not provided, a new connection will be opened.
        * @param   safeConnection
        *          If {@code provided} is {@code null}, {@code true} to include logging, transaction
        *          logging, and locking in the connection parameters for a newly opened database
        *          connection; {@code false} to omit these parameters. Ignored if an external
        *          connection is provided.
        */
       private void prepareStatements(Connection provided, boolean safeConnection)
       {
          try
          {
             if (provided != null)
             {
                this.connection = provided;
             }
             else if (connection == null)
             {
                openConnection(safeConnection);
             }

             String query, insert, update, delete;

             // file table insert
             insert = "insert into file (name) values (?)";
             stmtFileInsert = connection.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);
             query = "select id, name from file";
             stmtFileQuery = connection.prepareStatement(query);

             // ast_map table insert
             insert = "insert into ast_map (fid, astid, type) values(?, ?, ?)";
             stmtAstMapInsert = connection.prepareStatement(insert);

             // source_line table insert
             insert = "insert into source_line (fid, type, line, data) values(?, ?, ?, ?)";
             stmtSrcLineInsert = connection.prepareStatement(insert);

             // tag table insert
             insert = "insert into tag (name) values(?)";
             stmtTagInsert = connection.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);

             // report table insert
             insert =
                "insert into report (custom, type, title, condition, conddesc, multiplex, " +
                "dumptype, dumpexpr, dumplevel, support, user) " +
                "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
             stmtReportInsert = connection.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);

             // report_tag table insert
             insert = "insert into report_tag (rid, tid) values(?, ?)";
             stmtRptTagInsert = connection.prepareStatement(insert);

             // filter_profile table insert
             insert = "insert into filter_profile (name, default) values(?, ?)";
             stmtFltPrfInsert = connection.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);

             // filter_profile table update
             update = "update filter_profile set name = ? where id = ? and not default";
             stmtFltPrfUpdate = connection.prepareStatement(update);

             // file_filter table insert
             insert = "insert into file_filter (pid, wild, spec) values(?, ?, ?)";
             stmtFileFltInsert = connection.prepareStatement(insert);

             // file filter table delete
             delete = "delete from file_filter where pid = ?";
             stmtFileFltDelete = connection.prepareStatement(delete);

             // report_column table insert
             insert =
                "insert into report_column (rid, idx, heading, format, sorting) " +
                "values(?, ?, ?, ?, ?)";
             stmtRptColInsert = connection.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);

             // report_cell table insert
             insert = "insert into report_cell(rcid, rowpos, text, num) values (?, ?, ?, ?)";
             stmtRptCellInsert = connection.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);

             // report_stats table insert
             insert = "insert into report_stats (rid, matches, files) values(?, ?, ?)";
             stmtRptStatsInsert = connection.prepareStatement(insert);

             // category table insert
             insert = "insert into category (rid, name, lvl) values(?, ?, ?)";
             stmtMatchCatInsert = connection.prepareStatement(insert,
                                                              Statement.RETURN_GENERATED_KEYS);

             // prepare match insert statement
             insert = "insert into match (cid, fid, line, col, text) values(?, ?, ?, ?, ?)";
             stmtMatchInsert = connection.prepareStatement(insert);

             // prepare match query statement
             query =
                "select fid, line, col, text from match " +
                "where cid = ? order by fid asc, line asc, col asc";
             stmtMatchQueryByStat = connection.prepareStatement(query);

             // prepare fid query statement
             query = "select id from file where name = ?";
             stmtFidByFilename = connection.prepareStatement(query);

             // prepare match all stats by report query statement
             query =
                "select r.id, count(m.fid) as matches, count(distinct(m.fid)) as files " +
                "from report r " +
                "left outer join category c on r.id = c.rid " +
                "left outer join match m on m.cid = c.id " +
                "left outer join file f on f.id = m.fid " +
                "where r.type = ? " +
                "and not exists(select rid from report_column where rid = r.id) " +
                "group by r.id";
             stmtAllStatsQueryByRpt = connection.prepareStatement(query);

             // prepare match stats by individual report query statement
             query =
                "select r.id, count(m.fid) as matches, count(distinct(m.fid)) as files " +
                "from report r " +
                "left outer join category c on r.id = c.rid " +
                "left outer join match m on m.cid = c.id " +
                "left outer join file f on f.id = m.fid " +
                "where r.id = ?";
             stmtStatsQueryByRpt = connection.prepareStatement(query);

             delete = "delete from report_stats where rid = ?";
             stmtDelRptStats = connection.prepareStatement(delete);

             connection.commit();
          }
          catch (Exception exc)
          {
             try
             {
                connection.rollback();
             }
             catch (SQLException sqle)
             {
                LOG.log(Level.SEVERE, "", sqle);
             }

             throw new RuntimeException("Error preparing database statements", exc);
          }
       }

       /**
        * Open a new connection to the report database (if one already opened,
        * it will close it first).
        *
        * @param   safe
        *          {@code true} to include logging, transaction logging, and locking in the connection
        *          parameters; {@code false} to omit these parameters.
        *
        * @throws  RuntimeException if new connection could not be established.
        */
       private void openConnection(boolean safe)
       {
          if (connection != null)
          {
             closeConnection();
          }

          try
          {
             if (dbURL == null)
             {
                long maxMemory = Runtime.getRuntime().maxMemory();
                long cacheSize = -1;
                if (maxMemory < Long.MAX_VALUE)
                {
                   cacheSize = maxMemory / 1024 / 2;
                }

                String home = Configuration.home();

                dbURL = "jdbc:h2:"
                      + home
                      + "/rptdb/rptdb;"
                      + "DB_CLOSE_DELAY=-1;"
                      + "AUTOCOMMIT=OFF;"
                      + "LOCK_MODE=3;"
                      + "MV_STORE=FALSE;";

                if (cacheSize > -1)
                {
                   dbURL += (";CACHE_SIZE=" + cacheSize);
                }

                // load driver
                Class.forName("org.h2.Driver");
             }

             connection = DriverManager.getConnection(dbURL, dbUser, dbPass);
          }
          catch (Exception exc)
          {
             LOG.log(Level.SEVERE, "", exc);

             throw new RuntimeException("Error opening database connection", exc);
          }
       }

       /**
        * Close the current connection
        *
        * @throws  RuntimeException if connection could not be closed.
        */
       private void closeConnection()
       {
          if (connection == null)
          {
             return;
          }

          try
          {
             connection.close();
          }
          catch (Exception exc)
          {
             throw new RuntimeException("Error closing database connection", exc);
          }
       }

       /**
        * Creates an index on the cid and fid columns for the match table,
        * and runs analyze on the database.
        *
        * @throws  RuntimeException if there were errors during index creation or
        *          analyze.
        */
       private void analyzeDatabase()
       {
          if (analyzed)
          {
             return;
          }

          analyzed = true;

          try (Statement stmt = connection.createStatement())
          {
             // analyze (also commits)
             stmt.execute("analyze");
          }
          catch (Exception exc)
          {
             try
             {
                connection.rollback();
             }
             catch (SQLException sqle)
             {
                LOG.log(Level.SEVERE, "", sqle);
             }

             throw new RuntimeException("Error indexing and analyzing", exc);
          }
       }

       /**
        * Provides a service for defining and matching conditions on a manual
        * basis (see {@link #addMatchCategory} and {@link #addMatch}) and/or on
        * an automated basis (see {@link #addMatchMultiplexed}). Provides helpers
        * to generate reports. This class is a <code>PatternEngine</code> worker
        * library.
        */
       public class Library
       implements XmlConstants
       {
          /** Map of report definitions to process. */
          private Map<Long, ReportDefinition> reports = null;

          /** Set of default file filter wildcard specifications */
          private Set<String> defFilterSpecs = new LinkedHashSet<>();

          /**
           * Constructs a default instance.
           */
          public Library()
          {
          }

          /**
           * Initialize the database, dropping and creating all tables.
           * <p>
           * If any user accounts previously were exported, import them now. Otherwise, create a
           * default admin user.
           * <p>
           * If any search history previously was exported, import it now.
           *
           * @param   adminPass
           *          Initial password for the application admin user (should be changed when that
           *          user first logs into the web application).
           */
          public void initializeDatabase(String adminPass)
          {
             createDatabaseTables();

             try
             {
                String importPath;
                File file;
                ConfigurationPersistence cfgPers = new ConfigurationPersistence();
                String home = Configuration.home() + File.separator;

                // user accounts

                importPath = home + AUTO_EXPORT_USER_PATH;
                file = new File(importPath);
                List<UserRecord> users = cfgPers.readXmlUsers(file);

                if (users.isEmpty())
                {
                   // create a default admin user with the provided password (this is the admin for
                   // the application, not for the database)
                   PasswordHelper hlp = new PasswordHelper();
                   byte[] salt = hlp.salt();
                   byte[] encPass = hlp.encryptPassword(adminPass, salt);
                   UserRecord user = new UserRecord("admin", true, salt, encPass, null);
                   users = new ArrayList<>(1);
                   users.add(user);
                }

                // insert user records into the database
                String insUser =
                   "insert into user (name, admin, salt, pass, pass_changed) values (?, ?, ?, ?, ?)";
                try (PreparedStatement ps = connection.prepareStatement(insUser))
                {
                   for (UserRecord user : users)
                   {
                      // user table insert
                      ps.setString(1, user.getName());
                      ps.setBoolean(2, user.isAdmin());
                      ps.setBytes(3, user.getSalt());
                      ps.setBytes(4, user.getPassword());
                      ps.setDate(5, user.getPassChanged());
                      ps.execute();
                      ps.clearParameters();
                   }
                }

                // search history

                importPath = home + AUTO_EXPORT_SRCH_PATH;
                file = new File(importPath);
                List<SearchHistory> searches = cfgPers.readXmlSearches(file);

                if (!searches.isEmpty())
                {
                   String insHist = "insert into search_history (id, criteria) values (?, ?)";
                   String insSrch = "insert into user_search (uid, sid) values " +
                                    "((select id from user where name = ?), ?)";
                   try (PreparedStatement ps1 = connection.prepareStatement(insHist);
                        PreparedStatement ps2 = connection.prepareStatement(insSrch);)
                   {
                      for (SearchHistory hist : searches)
                      {
                         long sid = hist.getSearchId();
                         ps1.setLong(1, sid);
                         ps1.setString(2, hist.getCondition());
                         ps1.execute();
                         ps1.clearParameters();

                         for (String name : hist.getUsers())
                         {
                            ps2.setString(1, name);
                            ps2.setLong(2, sid);
                            ps2.execute();
                            ps2.clearParameters();
                         }
                      }
                   }
                }

                connection.commit();
             }
             catch (ConfigurationException |
                    ParserConfigurationException |
                    IOException |
                    SAXException exc)
             {
                throw new RuntimeException("Error importing record(s)", exc);
             }
             catch (NoSuchAlgorithmException | InvalidKeySpecException exc)
             {
                throw new RuntimeException("Error encrypting user account password", exc);
             }
             catch (SQLException exc)
             {
                try
                {
                   connection.rollback();
                }
                catch (SQLException sqle)
                {
                   LOG.log(Level.SEVERE, "", sqle);
                }

                throw new RuntimeException("Error inserting initial records into database", exc);
             }
          }

          /**
           * Create all prepared statements needed to collect report information.
           *
           * @param   incremental
           *          {@code True} if running in incremental reporting mode, else {@code false}.
           */
          public void prepareDatabaseStatements(boolean incremental)
          {
             prepareStatements(null, incremental);

             // populate fid maps

             ResultSet rs = null;

             try
             {
                rs = stmtFileQuery.executeQuery();

                while (rs.next())
                {
                   long fid = rs.getLong(1);
                   String name = rs.getString(2);
                   file2ID.put(name, fid);
                   id2File.put(fid, name);
                }

                connection.commit();
             }
             catch (SQLException exc)
             {
                try
                {
                   connection.rollback();
                }
                catch (SQLException e)
                {
                   LOG.log(Level.SEVERE, "", e);
                }

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

          /**
           * Perform any per-file postprocessing. We commit all database changes made during report
           * processing of the current file. It is faster to do this once per file, rather than for
           * every change made while processing the file.
           */
          public void postprocessFile()
          {
             try
             {
                connection.commit();
             }
             catch (SQLException exc)
             {
                try
                {
                   connection.rollback();
                }
                catch (SQLException e)
                {
                   LOG.log(Level.SEVERE, "", e);
                }

                throw new RuntimeException(exc);
             }
          }

          /**
           * Post-process database after a report run.
           *
           * @param   databaseMode
           *          {@code True} if this was a schema report run; {@code false} if it was a code
           *          report run.
           * @param   reports
           *          List of report definitions.
           * @param   incremental
           *          {@code True} if this was an incremental report run; {@code false} if batch.
           */
          public void postprocessPipeline(boolean databaseMode,
                                          List<ReportDefinition> reports,
                                          boolean incremental)
          {
             tags = null;

             try
             {
                ResultSet rs = null;

                if (incremental)
                {
                   Long id = reports.get(0).id;

                   // delete existing stats record, if any, for this report
                   stmtDelRptStats.setLong(1, id);
                   stmtDelRptStats.execute();

                   // query match/file stats for full file set
                   stmtStatsQueryByRpt.setLong(1, id);
                   rs = stmtStatsQueryByRpt.executeQuery();
                   stmtStatsQueryByRpt.clearParameters();
                }
                else
                {
                   analyzeDatabase();

                   // query match/file stats for full file set
                   stmtAllStatsQueryByRpt.setInt(1, databaseMode ? SRC_SCHEMA : SRC_CODE_CACHE);
                   rs = stmtAllStatsQueryByRpt.executeQuery();
                }

                while (rs.next())
                {
                   long rid = rs.getLong(1);
                   int matches = rs.getInt(2);
                   int files = rs.getInt(3);
                   stmtRptStatsInsert.setLong(1, rid);
                   stmtRptStatsInsert.setInt(2, matches);
                   stmtRptStatsInsert.setInt(3, files);
                   stmtRptStatsInsert.execute();
                   stmtRptStatsInsert.clearParameters();
                }
                rs.close();

                if (!incremental && !defFilterSpecs.isEmpty())
                {
                   Long pid = null;

                   // schema reports are run before code reports; if we are in database mode (schema
                   // reports), create a default filter profile record; if running code reports,
                   // look up the primary key of that record
                   if (databaseMode)
                   {
                      stmtFltPrfInsert.setString(1, "Include All");
                      stmtFltPrfInsert.setBoolean(2, true);
                      stmtFltPrfInsert.execute();
                      stmtFltPrfInsert.clearParameters();

                      rs = stmtFltPrfInsert.getGeneratedKeys();
                      rs.next();
                      pid = rs.getLong(1);
                      rs.close();
                   }
                   else
                   {
                      String query = "select id from filter_profile where default = true";
                      Statement stmt = connection.createStatement();
                      rs = stmt.executeQuery(query);
                      if (rs.next())
                      {
                         pid = rs.getLong(1);
                      }
                      else
                      {
                         throw new RuntimeException("Unable to query default filter profile!");
                      }
                      rs.close();
                   }

                   for (String spec : defFilterSpecs)
                   {
                      stmtFileFltInsert.setLong(1, pid);
                      stmtFileFltInsert.setBoolean(2, true);
                      stmtFileFltInsert.setString(3, spec);
                      stmtFileFltInsert.execute();
                      stmtFileFltInsert.clearParameters();
                   }
                }

                // import non-default filters, if any, if we're in a batch run in database mode
                if (!incremental && databaseMode)
                {
                   ConfigurationPersistence cfgPers = new ConfigurationPersistence();
                   String home = Configuration.home() + File.separator;
                   String importPath = home + AUTO_EXPORT_FILTER_PATH;
                   File file = new File(importPath);

                   List<FilterProfile> profiles = cfgPers.readXmlFilters(file);

                   if (!profiles.isEmpty())
                   {
                      for (FilterProfile profile : profiles)
                      {
                         stmtFltPrfInsert.setString(1, profile.getName());
                         stmtFltPrfInsert.setBoolean(2, profile.isDefault());
                         stmtFltPrfInsert.execute();
                         stmtFltPrfInsert.clearParameters();
                         rs = stmtFltPrfInsert.getGeneratedKeys();
                         rs.next();
                         long pid = rs.getLong(1);
                         rs.close();

                         for (FileFilter filter : profile.getFilters())
                         {
                            stmtFileFltInsert.setLong(1, pid);
                            stmtFileFltInsert.setBoolean(2, filter.isWild());
                            stmtFileFltInsert.setString(3, filter.getSpec());
                            stmtFileFltInsert.execute();
                            stmtFileFltInsert.clearParameters();
                         }
                      }
                   }
                }

                connection.commit();
             }
             catch (ConfigurationException |
                    ParserConfigurationException |
                    IOException |
                    SAXException exc)
             {
                throw new RuntimeException("Error importing record(s)", exc);
             }
             catch (SQLException | RuntimeException exc)
             {
                try
                {
                   connection.rollback();
                }
                catch (SQLException e)
                {
                   LOG.log(Level.SEVERE, "", e);
                }

                throw new RuntimeException(exc);
             }
             finally
             {
                closeConnection();
             }
          }

          /**
           * Store information about a source file, including its name, AST ID, and lines of code.
           *
           * @param   root
           *          Root AST for the source file.
           * @param   databaseMode
           *          {@code True} if this is a schema report run; {@code false} if it is a code
           *          report run.
           * @param   df
           *          {@code True} if the current source file is schema DF file, else {@code false}.
           */
          public void storeSourceFile(Aast root, boolean databaseMode, boolean df)
          {
             String astFile = root.getFilename();

             if (astFile == null)
             {
                throw new NullPointerException("Expected non-null file name for AST:" +
                                               System.lineSeparator() +
                                               root.dumpTree());
             }
             else
             {
                Long astId = mgr.getTreeId(root.getId());

                String srcFile = astFile;
                if (databaseMode)
                {
                   srcFile = (String) root.getAnnotation(NOTE_SRCFILE);
                }

                int type = databaseMode ? SRC_SCHEMA : SRC_CODE_CACHE;

                // find the top-most directory and add it to the default file spec set; assumes all
                // files are at least one level removed from project root, and that source filename
                // has been normalized to use the P2J file separator
                // TODO: double-check the file separator assumption
                // only do this for DF files and non-database mode source code files, otherwise we
                // get a duplicative filter spec for the top-level code directory
                if (!databaseMode || df)
                {
                   String spec = srcFile;
                   int skip = spec.startsWith(REL_PREFIX) ? REL_PREFIX.length() : 0;
                   int pos = spec.indexOf(File.separator, skip);
                   if (pos >= 0)
                   {
                      spec = spec.substring(0, pos + 1) + "*";
                      defFilterSpecs.add(spec);
                   }
                }

                BufferedReader reader = null;

                try
                {
                   Long fid = file2ID.get(srcFile);

                   if (fid == null)
                   {
                      stmtFileInsert.setString(1, srcFile);
                      stmtFileInsert.execute();
                      stmtFileInsert.clearParameters();

                      // read the file id that was autogenerated
                      ResultSet rs = stmtFileInsert.getGeneratedKeys();
                      rs.next();
                      fid = rs.getLong(1);

                      file2ID.put(srcFile, fid);
                      id2File.put(fid, srcFile);
                   }

                   stmtAstMapInsert.setLong(1, fid);
                   stmtAstMapInsert.setLong(2, astId);
                   stmtAstMapInsert.setInt(3, type);
                   stmtAstMapInsert.execute();
                   stmtAstMapInsert.clearParameters();

                   String data;

                   // for code ASTs, import lines of source code from the original (unpreprocessed)
                   // source file into the database;
                   // for temp-table schema ASTs, don't import the source code, since this would be
                   // duplicative with doing so for the corresponding code AST;
                   // for permanent tables; import the DF file
                   // TODO: use SQL batching

                   if (!databaseMode || df)
                   {
                      // this loop stores a marker record for each file with line number 0 and no
                      // text data; this is needed in the event of zero-length source files
                      data = null;
                      reader = new BufferedReader(new FileReader(srcFile));
                      for (int i = 0; i == 0 || (data = reader.readLine()) != null; i++)
                      {
                         stmtSrcLineInsert.setLong(1, fid);
                         stmtSrcLineInsert.setInt(2, df ? SRC_SCHEMA : SRC_CODE_BASE);
                         stmtSrcLineInsert.setInt(3, i);
                         stmtSrcLineInsert.setString(4, data);
                         stmtSrcLineInsert.execute();
                         stmtSrcLineInsert.clearParameters();
                      }
                   }

                   // for code ASTs, import lines of source code from the preprocessed (i.e.,
                   // ".cache") file; there is no corresponding, preprocessed file for schema ASTs

                   if (!databaseMode)
                   {
                      // this loop stores a marker record for each file with line number 0 and no
                      // text data; this is needed in the event of zero-length source files
                      data = null;
                      reader = new BufferedReader(new FileReader(srcFile + EXT_CACHE));
                      for (int i = 0; i == 0 || (data = reader.readLine()) != null; i++)
                      {
                         stmtSrcLineInsert.setLong(1, fid);
                         stmtSrcLineInsert.setInt(2, SRC_CODE_CACHE);
                         stmtSrcLineInsert.setInt(3, i);
                         stmtSrcLineInsert.setString(4, data);
                         stmtSrcLineInsert.execute();
                         stmtSrcLineInsert.clearParameters();
                      }
                   }
                }
                catch (SQLException | IOException exc)
                {
                   try
                   {
                      connection.rollback();
                   }
                   catch (SQLException e)
                   {
                      LOG.log(Level.SEVERE, "", e);
                   }

                   throw new RuntimeException(exc);
                }
                finally
                {
                   if (reader != null)
                   {
                      try
                      {
                         reader.close();
                      }
                      catch (IOException exc)
                      {
                         throw new RuntimeException(exc);
                      }
                   }
                }
             }
          }

          /**
           * Register the list of all report definitions that will be processed.
           * This will also setup all the associated report tags for the master report index.
           *
           * @param    reports
           *           The list to register.
           */
          public void registerReports(ArrayList<ReportDefinition> reports,
                                      boolean databaseMode,
                                      boolean incremental)
          {
             this.reports = new LinkedHashMap<>();

             try
             {
                for (ReportDefinition rpt : reports)
                {
                   if (!incremental)
                   {
                      saveReport(rpt, databaseMode, false);
                   }

                   this.reports.put(rpt.id, rpt);

                   if (rpt.tags != null)
                   {
                      setMasterReportCategory(rpt.tags);
                   }
                }

                if (!incremental && tags != null)
                {
                   List<String> tagNames = new ArrayList<>(tags.keySet());
                   for (String tag : tagNames)
                   {
                      stmtTagInsert.setString(1, tag);
                      stmtTagInsert.execute();
                      stmtTagInsert.clearParameters();

                      // read back the id that was autogenerated
                      ResultSet rs = stmtTagInsert.getGeneratedKeys();
                      rs.next();
                      long tid = rs.getLong(1);

                      tags.put(tag, tid);
                   }

                   for (ReportDefinition rpt : this.reports.values())
                   {
                      if (rpt.tags != null)
                      {
                         for (String tag : rpt.tags)
                         {
                            Long tid = tags.get(tag);
                            stmtRptTagInsert.setLong(1, rpt.id);
                            stmtRptTagInsert.setLong(2, tid);
                            stmtRptTagInsert.execute();
                            stmtRptTagInsert.clearParameters();
                         }
                      }
                   }
                }

                connection.commit();
             }
             catch (SQLException exc)
             {
                try
                {
                   connection.rollback();
                }
                catch (SQLException sqle)
                {
                   LOG.log(Level.SEVERE, "", sqle);
                }

                throw new RuntimeException("Error inserting tag into database", exc);
             }
          }

          /**
           * Manually adds a match category to the list of watched categories, using a
           * short name as a key (so that it can be referenced again later)
           * and a long descriptive name that can be used in reports.
           * <p>
           * Matches can be counted for such match categories by using {@link #addMatch}
           * with the short name as the key.  In addition, if the short name is
           * the same as the one that would be computed by
           * {@link #addMatchMultiplexed}, then that method can be used as
           * well.
           * <p>
           * Reasons to use this manual approach include providing more control
           * over the report output (the description which is not available
           * in the multiplexed approach) and the ability to match on a
           * different or more complicated condition.  Another reason is that
           * once a match category is loaded this way, even if there are no matches
           * an entry in the summary report and a detailed report will be
           * created for this match category.  This fact can be useful if one wants
           * to explicitly see the number of matches as 0 for a match category that
           * isn't matched.
           *
           * @param    rid
           *           Report ID.
           * @param    name
           *           A string that is used as a key to reference this match category
           *           and as a description in reports.
           * @param    lvl
           *           The support level for the given match category.
           */
          public void addMatchCategory(long rid, String name, int lvl)
          throws IllegalArgumentException
          {
             ReportDefinition rpt = reports.get(rid);

             if (rpt == null)
             {
                String err = String.format("Non-existent report %d!", rid);
                throw new IllegalArgumentException(err);
             }

             Long cid = rpt.cats2Id.get(name);

             if (cid == null)
             {
                try
                {
                   stmtMatchCatInsert.setLong(1, rid);
                   stmtMatchCatInsert.setString(2, name);
                   stmtMatchCatInsert.setInt(3, lvl);
                   stmtMatchCatInsert.execute();
                   stmtMatchCatInsert.clearParameters();

                   // read back the id that was autogenerated
                   ResultSet rs = stmtMatchCatInsert.getGeneratedKeys();
                   rs.next();
                   cid = rs.getLong(1);

                   // maintain the local cache of all cids
                   rpt.cats2Id.put(name, cid);
                }
                catch (SQLException exc)
                {
                   try
                   {
                      connection.rollback();
                   }
                   catch (SQLException sqle)
                   {
                      LOG.log(Level.SEVERE, "", sqle);
                   }

                   throw new RuntimeException("Error adding a new match category", exc);
                }
             }
          }

          /**
           * Automatically adds a match category to the list of watched categories if it
           * doesn't already exist AND always adds a match to that match category.
           * This function will derive a short name for the match category as a key
           * (so that it can be referenced again later) and it will use the same
           * name as the long descriptive name that appears in reports.
           * <p>
           * The name is derived using the AST provided description for the token
           * type of the matched AST. Uses the {@link #addMatchCategory} and
           * {@link #addMatch} functions as workers.
           *
           * @param    rid
           *           Report ID.
           * @param    type
           *           The string describing the type of text dump to use in
           *           match processing.  See <code>addMatch</code>.
           * @param    lvl
           *           The support level for the given match category.
           */
          public void addMatchMultiplexed(long rid, boolean databaseMode, String type, int lvl)
          throws IllegalArgumentException,
                 AstException
          {
             Aast src = getResolver().getSourceAst();

             // safety logic
             if (src == null)
             {
                throw new IllegalArgumentException("No source AST node!");
             }

             String name = src.getDescriptiveTokenText();

             addMatchCategory(rid, name, lvl);
             addMatch(rid, databaseMode, name, type);
          }

          /**
           * Automatically adds a match category to the list of watched categories if it
           * doesn't already exist AND always adds a match to that match category.
           * <p>
           * Uses the {@link #addMatchCategory} and {@link #addMatch} functions as
           * workers.
           *
           * @param    rid
           *           Report ID.
           * @param    name
           *           The name which is used (1) as a key to identify the
           *           match category being matched, possibly translated, and
           *           (2) as the long name of the match category in the report summary.
           * @param    type
           *           The string describing the type of text dump to use in
           *           match processing.
           * @param    lvl
           *           The support level for the given match category.
           */
          public void addMatchMultiplexed(long rid,
                                          boolean databaseMode,
                                          String name,
                                          String type,
                                          int lvl)
          throws IllegalArgumentException,
                 AstException
          {
             if (name == null || name.length() == 0)
             {
                // fall back to best efforts of generating our own name
                addMatchMultiplexed(rid, databaseMode, type, lvl);
                return;
             }

             addMatchCategory(rid, name, lvl);
             addMatch(rid, databaseMode, name, type);
          }

          /**
           * Adds a match to a specific match category that already exists in our
           * list of watched categories. Each time a match is added, the line number,
           * column number and some text from the AST will be stored into a
           * file specific aggregator inside the match category in question. Later,
           * this data is accessible for detailed reports and the counter of
           * matches is used in detailed report headers and summary reports.
           * <p>
           * This function will use the short name for the match category as a key
           * to find the proper match category.
           * <p>
           * The text that will be dumped depends on the dump type specified by
           * the caller.  The following types are supported:
           * <p>
           * <ul>
           *    <li> 'simple' - just records the token text and the symbolic
           *         token name
           *    <li> 'simplePlus' - same as 'simple', but some summary match categories
           *         are added about descendents (total and immediate children)
           *         and the number of nodes between the AST and the root
           *         (depth)
           *    <li> 'lisp' - dumps the symbolic token names of the AST and all
           *         it's children in a lisp-like single line syntax (warning:
           *         this can get quite long)
           *    <li> 'parser' - duplicates the parser's human readable output
           *         format for the entire subtree rooted at the matched AST
           *         (warning: this can get long AND this can be a multi-line
           *         output)
           * </ul>
           * <p>
           * If this dump type is not one of the above specified type strings,
           * then the dump type string itself is used as the literal text of the
           * match.  This allows the caller to assert complete control over the
           * match text (and format of the contents).
           *
           * @param    rid
           *           Report ID.
           * @param    shortName
           *           The key which is used to identify the match category being
           *           matched.
           * @param    type
           *           The string describing the type of text dump to use in
           *           match processing. If this is not one of the above
           *           specified strings, then this string is used as the
           *           literal text of the match.
           */
          public void addMatch(long rid, boolean databaseMode, String shortName, String type)
          throws IllegalArgumentException,
                 AstException
          {
             ReportDefinition rpt = reports.get(rid);

             if (rpt == null)
             {
                String err = String.format("Non-existent report %d!", rid);
                throw new IllegalArgumentException(err);
             }

             Long cid = rpt.cats2Id.get(shortName);

             // can't add a match to a non-existing cid
             if (cid == null)
             {
                throw new IllegalArgumentException("Non-existent match category " + shortName + "!");
             }

             Aast src = getResolver().getSourceAst();

             // safety logic
             if (src == null)
             {
                throw new IllegalArgumentException("No source AST node!");
             }

             String filename = databaseMode
                               ? (String) src.getAncestor(-1).getAnnotation(NOTE_SRCFILE)
                               : src.getFilename();

             if (filename == null)
             {
                throw new AstException("AstRegistry failure for ID " + src.getId().toString() + "!");
             }

             // obtain the text from the match, by default, dump current AST only
             StringBuilder sb = new StringBuilder(src.getText());
             sb.append(" [").append(src.getSymbolicTokenType()).append("]");
             String text = sb.toString();

             // possible overrides by caller to dump differently
             if ("simple".equalsIgnoreCase(type))
             {
                // nothing to do except avoid the else block below
             }
             else if ("simplePlus".equalsIgnoreCase(type))
             {
                int numImmDesc = src.getNumImmediateChildren();
                int numDesc    = src.getNumChildren();
                int depth      = src.getDepth();

                sb.append(" <total ").append(Integer.toString(numDesc));
                sb.append("> <immed ").append(Integer.toString(numImmDesc));
                sb.append("> <depth ").append(Integer.toString(depth));
                sb.append(">");
                text = sb.toString();
             }
             else if ("lisp".equalsIgnoreCase(type))
             {
                // add the text of the immediate node
                sb = new StringBuilder( src.getText() );

                // dump full tree with lisp-like syntax
                text = sb.append(" ").append(src.toStringList()).toString();
             }
             else if ("parser".equalsIgnoreCase(type))
             {
                text = src.dumpTree();
                text = text.substring(0, text.length() - 1);
             }
             else
             {
                // anything unrecognized is used as the literal text
                text = type;
             }

             int lineno = src.getLine();
             int colno  = src.getColumn();

             // process artificial tokens differently
             if (lineno == 0)
             {
                Iterator<Aast> iter = src.iterator();

                while (lineno == 0 && iter.hasNext())
                {
                   // this is an "artificial token", but all such tokens are
                   // usually associated with a line and column, based on the
                   // first descendant which has a non-zero line number
                   Aast child = iter.next();

                   lineno = child.getLine();
                   colno  = child.getColumn();
                }
             }

             try
             {
                // map filename to ID if not already done (needed for incremental report run)
                Long fid = file2ID.get(filename);
                if (fid == null)
                {
                   stmtFidByFilename.setString(1, filename);
                   ResultSet rs = stmtFidByFilename.executeQuery();
                   if (rs.next())
                   {
                      fid = rs.getLong(1);
                      file2ID.put(filename, fid);
                   }
                   rs.close();
                   stmtFidByFilename.clearParameters();
                }

                stmtMatchInsert.setLong(1, cid);
                stmtMatchInsert.setLong(2, fid);
                stmtMatchInsert.setInt(3, lineno);
                stmtMatchInsert.setInt(4, colno);
                stmtMatchInsert.setString(5, text);
                stmtMatchInsert.execute();
                stmtMatchInsert.clearParameters();
             }
             catch (SQLException exc)
             {
                try
                {
                   connection.rollback();
                }
                catch (SQLException sqle)
                {
                   LOG.log(Level.SEVERE, "", sqle);
                }

                throw new RuntimeException("Error inserting match category into database", exc);
             }
          }

          /**
           * Clear all any report-specific data from memory.
           */
          public void reset()
          {
             reports  = null;
          }

          /**
           * Associate the given text tags with the current report. This will
           * cause the subsequent master report index to be generated with a
           * separate table for each tag.
           *
           * @param    tagList
           *           The set of tags to add to the master report index.
           */
          public void setMasterReportCategory(Set<String> tagList)
          {
             if (tagList == null || tagList.isEmpty())
             {
                return;
             }

             if (tags == null)
             {
                tags = new LinkedHashMap<>();
             }

             for (String tag : tagList)
             {
                tags.put(tag, null);
             }
          }

          /**
           * Writes the matches for a single match category to STDOUT.
           *
           * @param    cid
           *           The match category for which to dump matches.
           */
          private void dumpStatMatches(int cid)
          {
             try
             {
                stmtMatchQueryByStat.setInt(1, cid);
                ResultSet rs = stmtMatchQueryByStat.executeQuery();

                while (rs.next())
                {
                   long   fid    = rs.getLong("fid");
                   int    lineno = rs.getInt("line");
                   int    colno  = rs.getInt("col");
                   String text   = rs.getString("text");

                   System.out.printf("DUMP cid %d; fid %d; file %s; line %d; " +
                                     "col %d; text %s\n",
                                     cid,
                                     fid,
                                     id2File.get(fid),
                                     lineno,
                                     colno,
                                     text);
                }

                // commit in caller
             }
             catch (SQLException exc)
             {
                // rollback in caller
                throw new RuntimeException("Error dumping cid matches", exc);
             }
          }

          /**
           * Generates a custom report using the same formatting as for standard
           * reports, but using data provided by the caller. Each custom report
           * contains a header with a title. The body of the report contains a
           * table with a header specified by the header list of strings (1
           * column per header string).  This table will also have a row for each
           * values list contained in the given list of rows.
           *
           * @param    databaseMode
           *           {@code true} if database mode, else {@code false}.
           * @param    title
           *           The report's title.
           * @param    colDefs
           *           A list of column definitions to store in the database for this
           *           custom report.
           * @param    rows
           *           An ordered list containing all row data.  This is a list
           *           which contains lists of each column's data for each row in
           *           the table.  This must be a list with elements that are
           *           lists with elements of type <code>String</code>.  The
           *           number of elements in each contained list must be the same
           *           as the number of elements of the <code>headers</code> list.
           * @param    tags
           *           The set of categories with which this report is tagged.
           * @param    totalMatches
           *           The total number of matches to be shown in the master
           *           report index for this report.
           * @param    totalFiles
           *           The total number of files (that had any matches) to be
           *           shown in the master report index for this report.
           */
          public void generateCustomReport(boolean              databaseMode,
                                           String               title,
                                           List<CustomColumn>   colDefs,
                                           List<List<Object>>   rows,
                                           Set<String>          tags,
                                           int                  totalMatches,
                                           int                  totalFiles)
          {
             try
             {
                // add report record to database
                stmtReportInsert.setBoolean(1, true);
                stmtReportInsert.setInt(2, databaseMode ? SRC_SCHEMA : SRC_CODE_CACHE);
                stmtReportInsert.setString(3, title);
                stmtReportInsert.setNull(4, Types.VARCHAR);
                stmtReportInsert.setString(5, "Custom");
                stmtReportInsert.setNull(6, Types.VARCHAR);
                stmtReportInsert.setNull(7, Types.VARCHAR);
                stmtReportInsert.setNull(8, Types.VARCHAR);
                stmtReportInsert.setNull(9, Types.INTEGER);
                stmtReportInsert.setNull(10, Types.VARCHAR);
                stmtReportInsert.setBoolean(11, false);
                stmtReportInsert.execute();
                stmtReportInsert.clearParameters();

                // read back the report id that was autogenerated
                ResultSet rs = stmtReportInsert.getGeneratedKeys();
                rs.next();
                long rid = rs.getLong(1);

                // associate report's tags with report record
                for (String tag : tags)
                {
                   Long tid = ReportWorker.tags.get(tag);
                   stmtRptTagInsert.setLong(1, rid);
                   stmtRptTagInsert.setLong(2, tid);
                   stmtRptTagInsert.execute();
                   stmtRptTagInsert.clearParameters();
                }

                // associate report's statistics with report record
                stmtRptStatsInsert.setLong(1, rid);
                stmtRptStatsInsert.setInt(2, totalMatches);
                stmtRptStatsInsert.setInt(3, totalFiles);
                stmtRptStatsInsert.execute();
                stmtRptStatsInsert.clearParameters();

                // associate report's column definitions with report record
                int i = 0;
                int numCols = colDefs.size();
                long[] colIds = new long[numCols];
                for (CustomColumn cd : colDefs)
                {
                   stmtRptColInsert.setLong(1, rid);
                   stmtRptColInsert.setInt(2, i);
                   stmtRptColInsert.setString(3, cd.getHeading());
                   stmtRptColInsert.setString(4, cd.getFormat());
                   stmtRptColInsert.setInt(5, cd.getSorting());
                   stmtRptColInsert.execute();
                   stmtRptColInsert.clearParameters();

                   // read back the report id that was autogenerated
                   rs = stmtRptColInsert.getGeneratedKeys();
                   rs.next();
                   colIds[i] = rs.getLong(1);

                   i++;
                }

                // associate report's row data with report record, one cell at a time;
                int r = 0;
                for (List<Object> row : rows)
                {
                   if (row.size() != numCols)
                   {
                      throw new RuntimeException(
                         "Wrong number of columns for custom report row " + r);
                   }

                   int c = 0;
                   for (Object datum : row)
                   {
                      stmtRptCellInsert.setLong(1, colIds[c]);
                      stmtRptCellInsert.setInt(2, r);

                      if (datum instanceof String)
                      {
                         stmtRptCellInsert.setString(3, (String) datum);
                         stmtRptCellInsert.setNull(4, Types.DOUBLE);
                      }
                      else
                      {
                         stmtRptCellInsert.setNull(3, Types.VARCHAR);
                         if (Number.class.isAssignableFrom(datum.getClass()))
                         {
                            double num = ((Number) datum).doubleValue();
                            stmtRptCellInsert.setDouble(4, num);
                         }
                         else
                         {
                            throw new RuntimeException(
                               "Unsupported data type for custom report (" + r + ":" + c + "): " +
                               datum);
                         }
                      }

                      stmtRptCellInsert.execute();
                      stmtRptCellInsert.clearParameters();

                      c++;
                   }

                   r++;
                }

                connection.commit();
             }
             catch (SQLException exc)
             {
                try
                {
                   connection.rollback();
                }
                catch (SQLException e)
                {
                   throw new RuntimeException(e);
                }

                throw new RuntimeException(exc);
             }
          }
       }
    }