<commit_before>/* The MIT License (MIT)
 *
 * Copyright (c) 2015 Reinventing Geospatial, Inc.
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 */

package com.rgi.geopackage.core;

import static com.rgi.geopackage.verification.Assert.assertArrayEquals;
import static com.rgi.geopackage.verification.Assert.assertTrue;
import static com.rgi.geopackage.verification.Assert.fail;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.RandomAccessFile;
import java.nio.ByteBuffer;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;

import utility.DatabaseUtility;

import com.rgi.geopackage.verification.AssertionError;
import com.rgi.geopackage.verification.ColumnDefinition;
import com.rgi.geopackage.verification.ForeignKeyDefinition;
import com.rgi.geopackage.verification.Requirement;
import com.rgi.geopackage.verification.Severity;
import com.rgi.geopackage.verification.TableDefinition;
import com.rgi.geopackage.verification.VerificationLevel;
import com.rgi.geopackage.verification.Verifier;

/**
 * @author <NAME>
 * @author <NAME>
 */
public class CoreVerifier extends Verifier
{
    private boolean hasContentsTable;
    private boolean hasSpatialReferenceSystemTable;
    /**
     * Constructor
     *
     * @param file
     *             File handle to the SQLite database
     * @param sqliteConnection
     *             JDBC connection to the SQLite database
     * @param verificationLevel
     *             Controls the level of verification testing performed
     */
    public CoreVerifier(final File file, final Connection sqliteConnection, final VerificationLevel verificationLevel)
    {
        super(sqliteConnection, verificationLevel);

        if(file == null)
        {
            throw new IllegalArgumentException("File cannot be null");
        }

        this.file = file;

        try
        {
            this.hasContentsTable = this.tableExists(GeoPackageCore.ContentsTableName);
        }
        catch(final SQLException ex)
        {
            this.hasContentsTable = false;
        }

        try
        {
            this.hasSpatialReferenceSystemTable = this.tableExists(GeoPackageCore.SpatialRefSysTableName);
        }
        catch(final SQLException ex)
        {
            this.hasSpatialReferenceSystemTable = false;
        }
    }

    /**
     * Requirement 1
     * <blockquote>
     * A GeoPackage SHALL be a <a href="http://www.sqlite.org/">SQLite</a>
     * database file using <a href="http://sqlite.org/fileformat2.html">version
     * 3 of the SQLite file format</a>. The first 16 bytes of a GeoPackage
     * SHALL contain "SQLite format 3" in ASCII
     * </blockquote>
     *
     * @throws IOException throws if an I/O error occurs when reading the file header
     * @throws AssertionError throws if it fails to meet the specified requirement;
     */
    @Requirement(number = 1,
                 text = "A GeoPackage SHALL be a SQLite database file using version 3 of the SQLite file format. The first 16 bytes of a GeoPackage SHALL contain \"SQLite format 3\" in ASCII.",
                 severity = Severity.Warning)
    public void Requirement1() throws IOException, AssertionError
    {
        final byte[] header = "SQLite format 3\0".getBytes(StandardCharsets.US_ASCII);    // The GeoPackage spec says it's StandardCharsets.US_ASCII, but the SQLite spec (https://www.sqlite.org/fileformat.html - 1.2.1 Magic Header String) says it's UTF8, i.e, StandardCharsets.UTF_8

        final byte[] data = new byte[header.length];

        try(FileInputStream fileInputStream = new FileInputStream(this.file))
        {
            assertTrue("The header information of the file does not contain enough bytes to include necessary information", fileInputStream.read(data, 0, header.length) == header.length);
            assertArrayEquals("The database file is not using a version 3 of the SQLite format.  Or does not include the SQLite version in the file header.", header, data);
        }
    }

    /**
     * Requirement 2
     * <blockquote>
     * A GeoPackage SHALL contain 0x47503130 ("GP10" in ASCII)
     * in the application id field of the SQLite database header
     * to indicate a GeoPackage version 1.0 file.
     * </blockquote>
     * @throws AssertionError throws if it fails to meet the specified requirement;
     */
    @Requirement(number = 2,
                 text = "A GeoPackage SHALL contain 0x47503130 ('GP10' in ASCII) in the application id field of the SQLite database header to indicate a GeoPackage version 1.0 file.",
                 severity = Severity.Warning)
    public void Requirement2() throws AssertionError
    {
        final int  sizeOfInt = 4;
        final long applicationIdByteOffset = 68;

        final byte[] data = new byte[sizeOfInt];    // 4 bytes in an int

        // application id
        // http://www.sqlite.org/fileformat2.html
        // http://www.geopackage.org/spec/#_sqlite_container
        // A GeoPackage SHALL contain 0x47503130 ("GP10" in ASCII) in the
        // application id field of the SQLite database header to indicate a
        // GeoPackage version 1.0 file.
        // The bytes 'G', 'P', '1', '0' are equivalent to 0x47503130
        final int expectedAppId = 0x47503130;

        try(RandomAccessFile randomAccessFile = new RandomAccessFile(this.file, "r"))
        {
            randomAccessFile.seek(applicationIdByteOffset);
            assertTrue("The file does not have enough bytes to include a header for the GeoPackage to check the valid information.",
                       randomAccessFile.read(data, 0, sizeOfInt) == sizeOfInt);
        }
        catch(final Exception ex)
        {
            throw new AssertionError(ex);
        }

        final int applicationId = ByteBuffer.wrap(data).asIntBuffer().get();

        assertTrue(String.format("Bad Application ID: 0x%08x Expected: 0x%08x", applicationId, expectedAppId),
                   applicationId == expectedAppId);
    }

    /**
     * Requirement 3
     * <blockquote>
     * A GeoPackage SHALL have the file extension name ".gpkg".
     * </blockquote>
     * @throws AssertionError throws if it fails to meet the specified requirement;
     */
    @Requirement(number = 3,
                 text = "A GeoPackage SHALL have the file extension name '.gpkg'",
                 severity = Severity.Warning)
    public void Requirement3() throws AssertionError
    {
        final String fileName          = this.file.getName();
        final String extension         = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
        final String expectedExtension = "gpkg";

        assertTrue(String.format("Not a GeoPackage File: %s \nExpected a file with the extension: %s", fileName, expectedExtension),
                   extension.equals(expectedExtension));
    }

    /**
     * Requirment 4
     * <blockquote>
     * A GeoPackage SHALL only contain data elements, SQL constructs and GeoPackage extensions with the "gpkg" author name specified in this encoding standard.
     * </blockquote>
     *
     */
    @Requirement(number = 4,
                 text = "A GeoPackage SHALL only contain data elements, SQL constructs and GeoPackage extensions with the \"gpkg\" author name specified in this encoding standard.",
                 severity = Severity.Warning)
    public static void Requirement4()
    {
        // This requirement is tested through other test cases.
        // The tables we test are:
        // gpkg_contents            per test Req13 in CoreVerifier
        // gpkg_spatial_ref_sys     per test Req10 in CoreVerifier
        // gpkg_tile_matrix         per test Req41 in TileVerifier
        // gpkg_tile_matrix_set     per test Req37 in TileVerifier
        // Pyramid User Data Tables per test Req33 in TileVerifier
    }

    /**
     * Requirement 5
     * <blockquote>
     * The columns of tables in a GeoPackage SHALL only be declared using one of the data types specified in table <a href="http://www.geopackage.org/spec/#table_column_data_types">GeoPackage Data Types</a>.
     * </blockquote>
     * @throws SQLException throws if an SQLException occurs
     * @throws AssertionError throws if it fails to meet the specified requirement;
     */
    @Requirement(number = 5,
                 text = "The columns of tables in a GeoPackage SHALL only be declared using one of the data types specified in table GeoPackage Data Types.",
                 severity = Severity.Error)
    public void Requirement5() throws SQLException, AssertionError
    {
        if (this.hasContentsTable)
        {
            final String query = "SELECT table_name FROM gpkg_contents;";

            try (Statement stmt = this.getSqliteConnection().createStatement();
                    ResultSet tableName = stmt.executeQuery(query);)
            {
                while (tableName.next())
                {
                    final String table_name = tableName.getString("table_name");

                    if (DatabaseUtility.tableOrViewExists(this.getSqliteConnection(), table_name))
                    {

                        try (PreparedStatement stmt2 = this.getSqliteConnection().prepareStatement("PRAGMA table_info(?);"))
                        {

                            stmt2.setString(1, table_name);

                            try (ResultSet pragmaTableinfo = stmt2.executeQuery())
                            {
                                {
                                    while (pragmaTableinfo.next())
                                    {
                                        final String dataType = pragmaTableinfo.getString("type");
                                        final boolean correctDataType = Verifier.checkDataType(dataType);

                                        assertTrue(String.format("Incorrect data type encountered: %s  From table: %s",dataType, table_name), correctDataType);
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * Requirement 6
     * <blockquote>
     * The SQLite PRAGMA integrity_check SQL command SHALL return "ok" for a GeoPackage file.
     * </blockquote>
     *
     * @throws SQLException throws if SQLException occurs
     * @throws AssertionError throws if it fails to meet the specified requirement;
     */
    @Requirement(number = 6,
                 text = "The SQLite PRAGMA integrity_check SQL command SHALL return \"ok\" for a GeoPackage file.",
                 severity = Severity.Error)
    public void Requirement6() throws SQLException, AssertionError<commit_msg>fixing error in prepared statement<commit_after>/* The MIT License (MIT)
 *
 * Copyright (c) 2015 Reinventing Geospatial, Inc.
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 */

package com.rgi.geopackage.core;

import static com.rgi.geopackage.verification.Assert.assertArrayEquals;
import static com.rgi.geopackage.verification.Assert.assertTrue;
import static com.rgi.geopackage.verification.Assert.fail;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.RandomAccessFile;
import java.nio.ByteBuffer;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;

import utility.DatabaseUtility;

import com.rgi.geopackage.verification.AssertionError;
import com.rgi.geopackage.verification.ColumnDefinition;
import com.rgi.geopackage.verification.ForeignKeyDefinition;
import com.rgi.geopackage.verification.Requirement;
import com.rgi.geopackage.verification.Severity;
import com.rgi.geopackage.verification.TableDefinition;
import com.rgi.geopackage.verification.VerificationLevel;
import com.rgi.geopackage.verification.Verifier;

/**
 * @author <NAME>
 * @author <NAME>
 */
public class CoreVerifier extends Verifier
{
    private boolean hasContentsTable;
    private boolean hasSpatialReferenceSystemTable;
    /**
     * Constructor
     *
     * @param file
     *             File handle to the SQLite database
     * @param sqliteConnection
     *             JDBC connection to the SQLite database
     * @param verificationLevel
     *             Controls the level of verification testing performed
     */
    public CoreVerifier(final File file, final Connection sqliteConnection, final VerificationLevel verificationLevel)
    {
        super(sqliteConnection, verificationLevel);

        if(file == null)
        {
            throw new IllegalArgumentException("File cannot be null");
        }

        this.file = file;

        try
        {
            this.hasContentsTable = this.tableExists(GeoPackageCore.ContentsTableName);
        }
        catch(final SQLException ex)
        {
            this.hasContentsTable = false;
        }

        try
        {
            this.hasSpatialReferenceSystemTable = this.tableExists(GeoPackageCore.SpatialRefSysTableName);
        }
        catch(final SQLException ex)
        {
            this.hasSpatialReferenceSystemTable = false;
        }
    }

    /**
     * Requirement 1
     * <blockquote>
     * A GeoPackage SHALL be a <a href="http://www.sqlite.org/">SQLite</a>
     * database file using <a href="http://sqlite.org/fileformat2.html">version
     * 3 of the SQLite file format</a>. The first 16 bytes of a GeoPackage
     * SHALL contain "SQLite format 3" in ASCII
     * </blockquote>
     *
     * @throws IOException throws if an I/O error occurs when reading the file header
     * @throws AssertionError throws if it fails to meet the specified requirement;
     */
    @Requirement(number = 1,
                 text = "A GeoPackage SHALL be a SQLite database file using version 3 of the SQLite file format. The first 16 bytes of a GeoPackage SHALL contain \"SQLite format 3\" in ASCII.",
                 severity = Severity.Warning)
    public void Requirement1() throws IOException, AssertionError
    {
        final byte[] header = "SQLite format 3\0".getBytes(StandardCharsets.US_ASCII);    // The GeoPackage spec says it's StandardCharsets.US_ASCII, but the SQLite spec (https://www.sqlite.org/fileformat.html - 1.2.1 Magic Header String) says it's UTF8, i.e, StandardCharsets.UTF_8

        final byte[] data = new byte[header.length];

        try(FileInputStream fileInputStream = new FileInputStream(this.file))
        {
            assertTrue("The header information of the file does not contain enough bytes to include necessary information", fileInputStream.read(data, 0, header.length) == header.length);
            assertArrayEquals("The database file is not using a version 3 of the SQLite format.  Or does not include the SQLite version in the file header.", header, data);
        }
    }

    /**
     * Requirement 2
     * <blockquote>
     * A GeoPackage SHALL contain 0x47503130 ("GP10" in ASCII)
     * in the application id field of the SQLite database header
     * to indicate a GeoPackage version 1.0 file.
     * </blockquote>
     * @throws AssertionError throws if it fails to meet the specified requirement;
     */
    @Requirement(number = 2,
                 text = "A GeoPackage SHALL contain 0x47503130 ('GP10' in ASCII) in the application id field of the SQLite database header to indicate a GeoPackage version 1.0 file.",
                 severity = Severity.Warning)
    public void Requirement2() throws AssertionError
    {
        final int  sizeOfInt = 4;
        final long applicationIdByteOffset = 68;

        final byte[] data = new byte[sizeOfInt];    // 4 bytes in an int

        // application id
        // http://www.sqlite.org/fileformat2.html
        // http://www.geopackage.org/spec/#_sqlite_container
        // A GeoPackage SHALL contain 0x47503130 ("GP10" in ASCII) in the
        // application id field of the SQLite database header to indicate a
        // GeoPackage version 1.0 file.
        // The bytes 'G', 'P', '1', '0' are equivalent to 0x47503130
        final int expectedAppId = 0x47503130;

        try(RandomAccessFile randomAccessFile = new RandomAccessFile(this.file, "r"))
        {
            randomAccessFile.seek(applicationIdByteOffset);
            assertTrue("The file does not have enough bytes to include a header for the GeoPackage to check the valid information.",
                       randomAccessFile.read(data, 0, sizeOfInt) == sizeOfInt);
        }
        catch(final Exception ex)
        {
            throw new AssertionError(ex);
        }

        final int applicationId = ByteBuffer.wrap(data).asIntBuffer().get();

        assertTrue(String.format("Bad Application ID: 0x%08x Expected: 0x%08x", applicationId, expectedAppId),
                   applicationId == expectedAppId);
    }

    /**
     * Requirement 3
     * <blockquote>
     * A GeoPackage SHALL have the file extension name ".gpkg".
     * </blockquote>
     * @throws AssertionError throws if it fails to meet the specified requirement;
     */
    @Requirement(number = 3,
                 text = "A GeoPackage SHALL have the file extension name '.gpkg'",
                 severity = Severity.Warning)
    public void Requirement3() throws AssertionError
    {
        final String fileName          = this.file.getName();
        final String extension         = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
        final String expectedExtension = "gpkg";

        assertTrue(String.format("Not a GeoPackage File: %s \nExpected a file with the extension: %s", fileName, expectedExtension),
                   extension.equals(expectedExtension));
    }

    /**
     * Requirment 4
     * <blockquote>
     * A GeoPackage SHALL only contain data elements, SQL constructs and GeoPackage extensions with the "gpkg" author name specified in this encoding standard.
     * </blockquote>
     *
     */
    @Requirement(number = 4,
                 text = "A GeoPackage SHALL only contain data elements, SQL constructs and GeoPackage extensions with the \"gpkg\" author name specified in this encoding standard.",
                 severity = Severity.Warning)
    public static void Requirement4()
    {
        // This requirement is tested through other test cases.
        // The tables we test are:
        // gpkg_contents            per test Req13 in CoreVerifier
        // gpkg_spatial_ref_sys     per test Req10 in CoreVerifier
        // gpkg_tile_matrix         per test Req41 in TileVerifier
        // gpkg_tile_matrix_set     per test Req37 in TileVerifier
        // Pyramid User Data Tables per test Req33 in TileVerifier
    }

    /**
     * Requirement 5
     * <blockquote>
     * The columns of tables in a GeoPackage SHALL only be declared using one of the data types specified in table <a href="http://www.geopackage.org/spec/#table_column_data_types">GeoPackage Data Types</a>.
     * </blockquote>
     * @throws SQLException throws if an SQLException occurs
     * @throws AssertionError throws if it fails to meet the specified requirement;
     */
    @Requirement(number = 5,
                 text = "The columns of tables in a GeoPackage SHALL only be declared using one of the data types specified in table GeoPackage Data Types.",
                 severity = Severity.Error)
    public void Requirement5() throws SQLException, AssertionError
    {
        if (this.hasContentsTable)
        {
            final String query = "SELECT table_name FROM gpkg_contents;";

            try (Statement stmt = this.getSqliteConnection().createStatement();
                    ResultSet tableName = stmt.executeQuery(query);)
            {
                while (tableName.next())
                {
                    final String table_name = tableName.getString("table_name");

                    if (DatabaseUtility.tableOrViewExists(this.getSqliteConnection(), table_name))
                    {

                        try (PreparedStatement stmt2 = this.getSqliteConnection().prepareStatement(String.format("PRAGMA table_info(%s));", table_name));
                             ResultSet pragmaTableinfo = stmt2.executeQuery();)
                        {
                            {
                                {
                                    while (pragmaTableinfo.next())
                                    {
                                        final String dataType = pragmaTableinfo.getString("type");
                                        final boolean correctDataType = Verifier.checkDataType(dataType);

                                        assertTrue(String.format("Incorrect data type encountered: %s  From table: %s",dataType, table_name), correctDataType);
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * Requirement 6
     * <blockquote>
     * The SQLite PRAGMA integrity_check SQL command SHALL return "ok" for a GeoPackage file.
     * </blockquote>
     *
     * @throws SQLException throws if SQLException occurs
     * @throws AssertionError throws if it fails to meet the specified requirement;
     */
    @Requirement(number = 6,
                 text = "The SQLite PRAGMA integrity_check SQL command SHALL return \"ok\" for a GeoPackage file.",
                 severity = Severity.Error)
    public void Requirement6() throws SQLException, AssertionError
{'fraction_non_alphanumeric': 0.07806737662122405, 'fraction_numerical': 0.010359259105788754, 'mean_word_length': 2.391512085441259, 'pattern_counts': {'":': 0, '<': 43, '<?xml version=': 0, '>': 43, 'https://': 2, 'lorem ipsum': 0, 'www.': 10, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5146804', 'n_tokens_mistral': 6431, 'n_tokens_neox': 6070, 'n_words': 2103}