Creat the track table

@param connection
@param trackTableName
@param isH2 set true if it's an H2 database
@return
@throws SQLException
488444
public static PreparedStatement createTrackTable(Connection connection, String trackTableName,boolean isH2) throws SQLException {
        try (Statement stmt = connection.createStatement()) {
            StringBuilder sb = new StringBuilder("CREATE TABLE ");
            sb.append(trackTableName).append(" (");
            if (isH2) {
                sb.append("the_geom MULTILINESTRING CHECK ST_SRID(THE_GEOM) = 4326,");
            } else {
                sb.append("the_geom GEOMETRY(MULTILINESTRING, 4326),");
            }
            sb.append(" id INT,");
            sb.append(GPXTags.NAME.toLowerCase()).append(" TEXT,");
            sb.append(GPXTags.CMT.toLowerCase()).append(" TEXT,");
            sb.append("description").append(" TEXT,");
            sb.append(GPXTags.SRC.toLowerCase()).append(" TEXT,");
            sb.append(GPXTags.HREF.toLowerCase()).append(" TEXT,");
            sb.append(GPXTags.HREFTITLE.toLowerCase()).append(" TEXT,");
            sb.append(GPXTags.NUMBER.toLowerCase()).append(" INT,");
            sb.append(GPXTags.TYPE.toLowerCase()).append(" TEXT,");
            sb.append(GPXTags.EXTENSIONS.toLowerCase()).append(" TEXT);");
            stmt.execute(sb.toString());
        }

        //We return the preparedstatement of the route table
        StringBuilder insert = new StringBuilder("INSERT INTO ").append(trackTableName).append(" VALUES ( ?");
        for (int i = 1; i < GpxMetadata.RTEFIELDCOUNT; i++) {
            insert.append(",?");
        }
        insert.append(");");
        return connection.prepareStatement(insert.toString());
    }
397379
public static void openFile(Connection connection, String fileName, String tableName) throws SQLException {
        String ext = fileName.substring(fileName.lastIndexOf('.') + 1,fileName.length());
        final boolean isH2 = JDBCUtilities.isH2DataBase(connection.getMetaData());
        for(DriverDef driverDef : DRIVERS) {
            if(driverDef.getFileExt().equalsIgnoreCase(ext)) {
                try (Statement st = connection.createStatement()) {
                    st.execute(String.format("CREATE TABLE %s COMMENT %s ENGINE %s WITH %s",
                            TableLocation.parse(tableName, isH2).toString(isH2),StringUtils.quoteStringSQL(fileName),
                            StringUtils.quoteJavaString(driverDef.getClassName()),StringUtils.quoteJavaString(fileName)));
                }
                return;
            }
        }
        throw new SQLException("No driver is available to open the "+ext+" file format");
    }
399646
public static boolean isH2Connection(Settings configuration) {
        final String connStr;
        try {
            connStr = configuration.getConnectionString(
                    Settings.KEYS.DB_CONNECTION_STRING,
                    Settings.KEYS.DB_FILE_NAME);
        } catch (IOException ex) {
            LOGGER.debug("Unable to get connectionn string", ex);
            return false;
        }
        return connStr.startsWith("jdbc:h2:file:");
    }
1082580
private void createOSMDatabaseModel(Connection connection, boolean isH2, TableLocation requestedTable, String osmTableName) throws SQLException {
        String tagTableName = TableUtilities.caseIdentifier(requestedTable, osmTableName + OSMTablesFactory.TAG, isH2);
        tagPreparedStmt =  OSMTablesFactory.createTagTable(connection, tagTableName);
        String nodeTableName = TableUtilities.caseIdentifier(requestedTable, osmTableName + OSMTablesFactory.NODE, isH2);
        nodePreparedStmt = OSMTablesFactory.createNodeTable(connection, nodeTableName, isH2);
        String nodeTagTableName = TableUtilities.caseIdentifier(requestedTable, osmTableName + OSMTablesFactory.NODE_TAG, isH2);
        nodeTagPreparedStmt = OSMTablesFactory.createNodeTagTable(connection, nodeTagTableName, tagTableName);
        String wayTableName = TableUtilities.caseIdentifier(requestedTable, osmTableName + OSMTablesFactory.WAY, isH2);
        wayPreparedStmt = OSMTablesFactory.createWayTable(connection, wayTableName, isH2);
        String wayTagTableName = TableUtilities.caseIdentifier(requestedTable, osmTableName + OSMTablesFactory.WAY_TAG, isH2);
        wayTagPreparedStmt = OSMTablesFactory.createWayTagTable(connection, wayTagTableName, tagTableName);
        String wayNodeTableName = TableUtilities.caseIdentifier(requestedTable, osmTableName + OSMTablesFactory.WAY_NODE, isH2);
        wayNodePreparedStmt = OSMTablesFactory.createWayNodeTable(connection, wayNodeTableName);
        String relationTableName = TableUtilities.caseIdentifier(requestedTable, osmTableName + OSMTablesFactory.RELATION, isH2);
        relationPreparedStmt = OSMTablesFactory.createRelationTable(connection, relationTableName);
        String relationTagTableName = TableUtilities.caseIdentifier(requestedTable, osmTableName + OSMTablesFactory.RELATION_TAG, isH2);
        relationTagPreparedStmt = OSMTablesFactory.createRelationTagTable(connection, relationTagTableName, tagTableName);
        String nodeMemberTableName = TableUtilities.caseIdentifier(requestedTable, osmTableName + OSMTablesFactory.NODE_MEMBER, isH2);
        nodeMemberPreparedStmt = OSMTablesFactory.createNodeMemberTable(connection, nodeMemberTableName);
        String wayMemberTableName = TableUtilities.caseIdentifier(requestedTable, osmTableName + OSMTablesFactory.WAY_MEMBER, isH2);
        wayMemberPreparedStmt = OSMTablesFactory.createWayMemberTable(connection, wayMemberTableName);
        String relationMemberTableName = TableUtilities.caseIdentifier(requestedTable, osmTableName + OSMTablesFactory.RELATION_MEMBER, isH2);
        relationMemberPreparedStmt = OSMTablesFactory.createRelationMemberTable(connection, relationMemberTableName);
    }
848331
public static DataSource createDataSource(String dbName ,boolean initSpatial, String h2Parameters) throws SQLException {
        // Create H2 memory DataSource
        org.h2.Driver driver = org.h2.Driver.load();
        OsgiDataSourceFactory dataSourceFactory = new OsgiDataSourceFactory(driver);
        Properties properties = new Properties();
        String databasePath = initDBFile(dbName, h2Parameters);
        properties.setProperty(DataSourceFactory.JDBC_URL, databasePath);
        properties.setProperty(DataSourceFactory.JDBC_USER, "sa");
        properties.setProperty(DataSourceFactory.JDBC_PASSWORD, "sa");
        DataSource dataSource = dataSourceFactory.createDataSource(properties);
        // Init spatial ext
        if(initSpatial) {
            try (Connection connection = dataSource.getConnection()) {
                H2GISFunctions.load(connection);
            }
        }
        return dataSource;
    }
488235
private void executeSql(final Connection connection, final String sql, final boolean isCommit) throws SQLException
   {
      if (sql != null) {
         try (Statement statement = connection.createStatement()) {
            // connection was created a few milliseconds before, so set query timeout is omitted (we assume it will succeed)
            statement.execute(sql);
         }

         if (isIsolateInternalQueries && !isAutoCommit) {
            if (isCommit) {
               connection.commit();
            }
            else {
               connection.rollback();
            }
         }
      }
   }
1134420
public Crud<T, K> table(Connection connection, String table) throws SQLException {
        CrudMeta crudMeta = CrudMeta.of(connection, table, jdbcMapperFactory.columnDefinitions());
        return CrudFactory.<T, K>newInstance(target, keyTarget, crudMeta, jdbcMapperFactory);
    }
62970
public boolean tableExists (Connection conn, String name) throws SQLException
    {
        ResultSet rs = conn.getMetaData().getTables(null, null, name, null);
        while (rs.next()) {
            String tname = rs.getString("TABLE_NAME");
            if (name.equals(tname)) {
                return true;
            }
        }
        return false;
    }
910162
public static void load(Connection connection, String BundleSymbolicName, String BundleVersion) throws SQLException {
        String packagePrepend = BundleSymbolicName+":"+BundleVersion+":";
        registerH2GISFunctions(connection,packagePrepend);
        registerGeometryType(connection);
        connection.commit();
    }
1213270
public boolean read(Connection connection, String tableName, File inputFile, ProgressVisitor progress) throws SQLException {
        this.progress = progress.subProcess(100);
        // Initialisation
        final boolean isH2 = JDBCUtilities.isH2DataBase(connection.getMetaData());
        boolean success = false;
        TableLocation requestedTable = TableLocation.parse(tableName, isH2);
        String osmTableName = requestedTable.getTable();
        checkOSMTables(connection, isH2, requestedTable, osmTableName);
        createOSMDatabaseModel(connection, isH2, requestedTable, osmTableName);

        FileInputStream fs = null;
        try {
            fs = new FileInputStream(inputFile);
            this.fc = fs.getChannel();
            this.fileSize = fc.size();
            if (fileSize > 0) {
                // Given the file size and an average node file size.
                // Skip how many nodes in order to update progression at a step of 1%
                readFileSizeEachNode = Math.max(1, (this.fileSize / AVERAGE_NODE_SIZE) / 100);
                nodeCountProgress = 0;
                XMLReader parser = XMLReaderFactory.createXMLReader();
                parser.setErrorHandler(this);
                parser.setContentHandler(this);
                if (inputFile.getName().endsWith(".osm")) {
                    parser.parse(new InputSource(fs));
                } else if (inputFile.getName().endsWith(".osm.gz")) {
                    parser.parse(new InputSource(new GZIPInputStream(fs)));
                } else if (inputFile.getName().endsWith(".osm.bz2")) {
                    parser.parse(new InputSource(new BZip2CompressorInputStream(fs)));
                } else {
                    throw new SQLException("Supported formats are .osm, .osm.gz, .osm.bz2");
                }
            }
            success = true;
        } catch (SAXException ex) {
            throw new SQLException(ex);
        } catch (IOException ex) {
            throw new SQLException("Cannot parse the file " + inputFile.getAbsolutePath(), ex);
        } finally {
            try {
                if (fs != null) {
                    fs.close();
                }
            } catch (IOException ex) {
                throw new SQLException("Cannot close the file " + inputFile.getAbsolutePath(), ex);
            }
            // When the reading ends, close() method has to be called
            if (nodePreparedStmt != null) {
                nodePreparedStmt.close();
            }
            if (nodeTagPreparedStmt != null) {
                nodeTagPreparedStmt.close();
            }
            if (wayPreparedStmt != null) {
                wayPreparedStmt.close();
            }
            if (wayTagPreparedStmt != null) {
                wayTagPreparedStmt.close();
            }
            if (wayNodePreparedStmt != null) {
                wayNodePreparedStmt.close();
            }
            if (relationPreparedStmt != null) {
                relationPreparedStmt.close();
            }
            if (relationTagPreparedStmt != null) {
                relationTagPreparedStmt.close();
            }
            if (nodeMemberPreparedStmt != null) {
                nodeMemberPreparedStmt.close();
            }
            if (wayMemberPreparedStmt != null) {
                wayMemberPreparedStmt.close();
            }
            if (relationMemberPreparedStmt != null) {
                relationMemberPreparedStmt.close();
            }
            if(tagPreparedStmt!=null){
                tagPreparedStmt.close();
            }
        }

        return success;
    }
757326
public static boolean hasField(Connection connection, String tableName, String fieldName) throws SQLException {
        final Statement statement = connection.createStatement();
        try {
            final ResultSet resultSet = statement.executeQuery(
                    "SELECT * FROM " + TableLocation.parse(tableName) + " LIMIT 0;");
            try {
                return hasField(resultSet.getMetaData(), fieldName);
            } finally {
                resultSet.close();
            }
        } catch (SQLException ex) {
            return false;
        } finally {
            statement.close();
        }
    }
93455
private void checkOSMTables(Connection connection, boolean isH2, TableLocation requestedTable, String osmTableName) throws SQLException {
        String[] omsTables = new String[]{OSMTablesFactory.TAG, OSMTablesFactory.NODE, OSMTablesFactory.NODE_TAG, OSMTablesFactory.WAY, OSMTablesFactory.WAY_NODE, 
            OSMTablesFactory.WAY_TAG, OSMTablesFactory.RELATION, OSMTablesFactory.RELATION_TAG, OSMTablesFactory.NODE_MEMBER, OSMTablesFactory.WAY_MEMBER, OSMTablesFactory.RELATION_MEMBER};
        for (String omsTableSuffix : omsTables) {
            String osmTable = TableUtilities.caseIdentifier(requestedTable, osmTableName + omsTableSuffix, isH2);
            if (JDBCUtilities.tableExists(connection, osmTable)) {
                throw new SQLException("The table " + osmTable + " already exists.");
            }
        }
    }
1193243
public static void execSQL(Connection connection, String sql) {
		Statement statement = null;
		try {
			statement = connection.createStatement();
			statement.execute(sql);
		} catch (SQLException e) {
			throw new GeoPackageException("Failed to execute SQL statement: "
					+ sql, e);
		} finally {
			closeStatement(statement, sql);
		}

	}
1274393
public static void createTables( Connection connection ) throws IOException, SQLException {

        /*
         * gps log table
         */
        StringBuilder sB = new StringBuilder();
        sB.append("CREATE TABLE ");
        sB.append(TABLE_GPSLOGS);
        sB.append(" (");
        sB.append(GpsLogsTableFields.COLUMN_ID.getFieldName() + " INTEGER PRIMARY KEY, ");
        sB.append(GpsLogsTableFields.COLUMN_LOG_STARTTS.getFieldName()).append(" LONG NOT NULL,");
        sB.append(GpsLogsTableFields.COLUMN_LOG_ENDTS.getFieldName()).append(" LONG NOT NULL,");
        sB.append(GpsLogsTableFields.COLUMN_LOG_LENGTHM.getFieldName()).append(" REAL NOT NULL, ");
        sB.append(GpsLogsTableFields.COLUMN_LOG_ISDIRTY.getFieldName()).append(" INTEGER NOT NULL, ");
        sB.append(GpsLogsTableFields.COLUMN_LOG_TEXT.getFieldName()).append(" TEXT NOT NULL ");
        sB.append(");");
        String CREATE_TABLE_GPSLOGS = sB.toString();

        /*
         * gps log data table
         */
        sB = new StringBuilder();
        sB.append("CREATE TABLE ");
        sB.append(TABLE_GPSLOG_DATA);
        sB.append(" (");
        sB.append(GpsLogsDataTableFields.COLUMN_ID.getFieldName() + " INTEGER PRIMARY KEY, ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_LON.getFieldName()).append(" REAL NOT NULL, ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_LAT.getFieldName()).append(" REAL NOT NULL,");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_ALTIM.getFieldName()).append(" REAL NOT NULL,");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_TS.getFieldName()).append(" DATE NOT NULL,");
        sB.append(GpsLogsDataTableFields.COLUMN_LOGID.getFieldName()).append(" INTEGER NOT NULL ");
        sB.append("CONSTRAINT ");
        sB.append(GpsLogsDataTableFields.COLUMN_LOGID.getFieldName());
        sB.append(" REFERENCES ");
        sB.append(TABLE_GPSLOGS);
        sB.append("(" + GpsLogsTableFields.COLUMN_ID.getFieldName() + ") ON DELETE CASCADE");
        sB.append(");");
        String CREATE_TABLE_GPSLOG_DATA = sB.toString();

        sB = new StringBuilder();
        sB.append("CREATE INDEX gpslog_id_idx ON ");
        sB.append(TABLE_GPSLOG_DATA);
        sB.append(" ( ");
        sB.append(GpsLogsDataTableFields.COLUMN_LOGID.getFieldName());
        sB.append(" );");
        String CREATE_INDEX_GPSLOG_ID = sB.toString();

        sB = new StringBuilder();
        sB.append("CREATE INDEX gpslog_ts_idx ON ");
        sB.append(TABLE_GPSLOG_DATA);
        sB.append(" ( ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_TS.getFieldName());
        sB.append(" );");
        String CREATE_INDEX_GPSLOG_TS = sB.toString();

        sB = new StringBuilder();
        sB.append("CREATE INDEX gpslog_x_by_y_idx ON ");
        sB.append(TABLE_GPSLOG_DATA);
        sB.append(" ( ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_LON.getFieldName());
        sB.append(", ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_LAT.getFieldName());
        sB.append(" );");
        String CREATE_INDEX_GPSLOG_X_BY_Y = sB.toString();

        sB = new StringBuilder();
        sB.append("CREATE INDEX gpslog_logid_x_y_idx ON ");
        sB.append(TABLE_GPSLOG_DATA);
        sB.append(" ( ");
        sB.append(GpsLogsDataTableFields.COLUMN_LOGID.getFieldName());
        sB.append(", ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_LON.getFieldName());
        sB.append(", ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_LAT.getFieldName());
        sB.append(" );");
        String CREATE_INDEX_GPSLOG_LOGID_X_Y = sB.toString();

        /*
         * properties table
         */
        sB = new StringBuilder();
        sB.append("CREATE TABLE ");
        sB.append(TABLE_GPSLOG_PROPERTIES);
        sB.append(" (");
        sB.append(GpsLogsPropertiesTableFields.COLUMN_ID.getFieldName());
        sB.append(" INTEGER PRIMARY KEY, ");
        sB.append(GpsLogsPropertiesTableFields.COLUMN_LOGID.getFieldName());
        sB.append(" INTEGER NOT NULL ");
        sB.append("CONSTRAINT " + GpsLogsPropertiesTableFields.COLUMN_LOGID.getFieldName() + " REFERENCES ");
        sB.append(TABLE_GPSLOGS);
        sB.append("(");
        sB.append(GpsLogsTableFields.COLUMN_ID);
        sB.append(") ON DELETE CASCADE,");
        sB.append(GpsLogsPropertiesTableFields.COLUMN_PROPERTIES_COLOR.getFieldName()).append(" TEXT NOT NULL, ");
        sB.append(GpsLogsPropertiesTableFields.COLUMN_PROPERTIES_WIDTH.getFieldName()).append(" REAL NOT NULL, ");
        sB.append(GpsLogsPropertiesTableFields.COLUMN_PROPERTIES_VISIBLE.getFieldName()).append(" INTEGER NOT NULL");
        sB.append(");");
        String CREATE_TABLE_GPSLOGS_PROPERTIES = sB.toString();

        try (Statement statement = connection.createStatement()) {
            statement.setQueryTimeout(30); // set timeout to 30 sec.

            statement.executeUpdate(CREATE_TABLE_GPSLOGS);

            statement.executeUpdate(CREATE_TABLE_GPSLOG_DATA);
            statement.executeUpdate(CREATE_INDEX_GPSLOG_ID);
            statement.executeUpdate(CREATE_INDEX_GPSLOG_TS);
            statement.executeUpdate(CREATE_INDEX_GPSLOG_X_BY_Y);
            statement.executeUpdate(CREATE_INDEX_GPSLOG_LOGID_X_Y);

            statement.executeUpdate(CREATE_TABLE_GPSLOGS_PROPERTIES);
        } catch (Exception e) {
            throw new IOException(e.getLocalizedMessage());
        }

    }
943667
@Override
	public JdbcDatabaseDescriptor dbByName(final String dbName) {
		Collection<H2DatabaseWorking> select = CollectionUtils.select(dbsw, new Predicate<H2DatabaseWorking>() {

			@Override
			public boolean evaluate(H2DatabaseWorking db) {
				return dbName.equals( db.getName() );
			}

		});
		if(CollectionUtils.size(select)==0){
			throw new IllegalArgumentException("There are no databases called '" + dbName + "'");
		}
		if(CollectionUtils.size(select)>=2){
			throw new IllegalArgumentException("More than one database is called '" + dbName + "'");
		}
		return CollectionUtils.extractSingleton( select );
	}
1096731
public static void unRegisterH2GISFunctions(Connection connection) throws SQLException {
        Statement st = connection.createStatement();
        for (Function function : getBuiltInsFunctions()) {
            unRegisterFunction(st, function);
        }
        unRegisterGeometryType(connection);
    }
1104553
public function searchTable(
        Connection $connection,
        string $databaseName,
        string $tableName,
        string $searchTerm
    ): \Generator {
        $tableMetadata = (new MetadataFactory())->getTableMetadata($connection, $databaseName, $tableName);

        if (!$tableMetadata->hasStringTypeColumn()) {
            return;
        }

        $rowResultList = (new StringMatchTableSearchStrategy())->getMatches(
            $connection,
            $tableMetadata,
            $searchTerm
        );

        // Yield batch tracking rows returned
        foreach ($rowResultList as $rowResult) {
            yield $rowResult;
        }
    }