mil.nga.geopackage.db.CoreSQLUtils Java Examples
The following examples show how to use
mil.nga.geopackage.db.CoreSQLUtils.
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: AlterTableUtils.java From geopackage-android with MIT License | 6 votes |
/** * Index the column * * @param db connection * @param tableName table name * @param column feature column */ private static void indexColumn(GeoPackageConnection db, String tableName, FeatureColumn column) { if (!column.isPrimaryKey() && !column.isGeometry()) { StringBuilder index = new StringBuilder( "CREATE INDEX IF NOT EXISTS "); index.append(CoreSQLUtils .quoteWrap("idx_" + tableName + "_" + column.getName())); index.append(" ON "); index.append(CoreSQLUtils.quoteWrap(tableName)); index.append(" ( "); String columnName = column.getName(); if (columnName.contains(" ")) { columnName = CoreSQLUtils.quoteWrap(columnName); } index.append(columnName); index.append(" )"); db.execSQL(index.toString()); } }
Example #2
Source File: UserCoreDao.java From geopackage-core-java with MIT License | 6 votes |
/** * Build where (or selection) statement for a single field * * @param field * field name * @param value * column value * @return where clause */ public String buildWhere(String field, ColumnValue value) { String where; if (value != null) { if (value.getValue() != null && value.getTolerance() != null) { if (!(value.getValue() instanceof Number)) { throw new GeoPackageException( "Field value is not a number and can not use a tolerance, Field: " + field + ", Value: " + value); } String quotedField = CoreSQLUtils.quoteWrap(field); where = quotedField + " >= ? AND " + quotedField + " <= ?"; } else { where = buildWhere(field, value.getValue()); } } else { where = buildWhere(field, null, null); } return where; }
Example #3
Source File: GeoPackageExtensions.java From geopackage-core-java with MIT License | 6 votes |
/** * Copy the Metadata extensions for the table * * @param geoPackage * GeoPackage * @param table * table name * @param newTable * new table name * @since 3.3.0 */ public static void copyMetadata(GeoPackageCore geoPackage, String table, String newTable) { try { if (geoPackage.isTable(MetadataReference.TABLE_NAME)) { CoreSQLUtils.transferTableContent(geoPackage.getDatabase(), MetadataReference.TABLE_NAME, MetadataReference.COLUMN_TABLE_NAME, newTable, table); } } catch (Exception e) { logger.log(Level.WARNING, "Failed to create Metadata for table: " + newTable + ", copied from table: " + table, e); } }
Example #4
Source File: AlterTableUtils.java From geopackage-java with MIT License | 6 votes |
/** * Index the column * * @param db * connection * @param tableName * table name * @param column * feature column */ private static void indexColumn(GeoPackageConnection db, String tableName, FeatureColumn column) { if (!column.isPrimaryKey() && !column.isGeometry()) { StringBuilder index = new StringBuilder( "CREATE INDEX IF NOT EXISTS "); index.append(CoreSQLUtils .quoteWrap("idx_" + tableName + "_" + column.getName())); index.append(" ON "); index.append(CoreSQLUtils.quoteWrap(tableName)); index.append(" ( "); String columnName = column.getName(); if (columnName.contains(" ")) { columnName = CoreSQLUtils.quoteWrap(columnName); } index.append(columnName); index.append(" )"); db.execSQL(index.toString()); } }
Example #5
Source File: SQLExecAlterTable.java From geopackage-java with MIT License | 6 votes |
/** * Check for a drop table statement and execute * * @param database * database * @param sql * SQL statement * @return result if dropped table, null if not */ private static SQLExecResult dropTable(GeoPackage database, String sql) { SQLExecResult result = null; Matcher matcher = DROP_TABLE_PATTERN.matcher(sql); if (matcher.matches() && SQLExec.isGeoPackage(database)) { String tableName = CoreSQLUtils .quoteUnwrap(matcher.group(TABLE_NAME_GROUP)); if (tableName != null) { tableName = tableName.trim(); if (!database.isTable(tableName)) { throw new GeoPackageException( "Table does not exist: " + tableName); } database.deleteTable(tableName.trim()); result = new SQLExecResult(); } } return result; }
Example #6
Source File: ConstraintParser.java From geopackage-core-java with MIT License | 6 votes |
/** * Get the constraint name and remaining definition * * @param constraintSql * constraint SQL * @return array with name or null at index 0, definition at index 1 */ public static String[] getNameAndDefinition(String constraintSql) { String parts[] = null; Matcher matcher = CONSTRAINT_PATTERN.matcher(constraintSql.trim()); if (matcher.find()) { String name = CoreSQLUtils .quoteUnwrap(matcher.group(CONSTRAINT_PATTERN_NAME_GROUP)); if (name != null) { name = name.trim(); } String definition = matcher .group(CONSTRAINT_PATTERN_DEFINITION_GROUP); if (definition != null) { definition = definition.trim(); } parts = new String[] { name, definition }; } return parts; }
Example #7
Source File: GeoPackageImpl.java From geopackage-java with MIT License | 6 votes |
/** * {@inheritDoc} */ @Override public ResultSet foreignKeyCheck(String tableName) { ResultSet resultSet = query(CoreSQLUtils.foreignKeyCheckSQL(tableName), null); try { if (!resultSet.next()) { resultSet.close(); resultSet = null; } } catch (SQLException e) { throw new GeoPackageException( "Foreign key check failed on database: " + getName(), e); } return resultSet; }
Example #8
Source File: GeoPackageCursorFactory.java From geopackage-android with MIT License | 6 votes |
/** * Register a cursor wrapper for the provided table name. Database queries * will wrap the returned cursor * * @param tableName table name * @param cursorWrapper cursor wrapper */ public void registerTable(String tableName, GeoPackageCursorWrapper cursorWrapper) { // Add the wrapper tableCursors.put(tableName, cursorWrapper); String quotedTableName = CoreSQLUtils.quoteWrap(tableName); tableCursors.put(quotedTableName, cursorWrapper); // The Android android.database.sqlite.SQLiteDatabase findEditTable method // finds the new cursor edit table name based upon the first space or comma. // Fix (hopefully temporary) to wrap with the expected cursor type int spacePosition = tableName.indexOf(' '); if (spacePosition > 0) { tableCursors.put(tableName.substring(0, spacePosition), cursorWrapper); tableCursors.put(quotedTableName.substring(0, quotedTableName.indexOf(' ')), cursorWrapper); } }
Example #9
Source File: RTreeIndexTableDao.java From geopackage-java with MIT License | 5 votes |
/** * {@inheritDoc} */ @Override public BoundingBox getBoundingBox() { List<Double> values = querySingleRowTypedResults( "SELECT MIN(" + RTreeIndexExtension.COLUMN_MIN_X + "), MIN(" + RTreeIndexExtension.COLUMN_MIN_Y + "), MAX(" + RTreeIndexExtension.COLUMN_MAX_X + "), MAX(" + RTreeIndexExtension.COLUMN_MAX_Y + ") FROM " + CoreSQLUtils.quoteWrap(getTableName()), null); BoundingBox boundingBox = new BoundingBox(values.get(0), values.get(1), values.get(2), values.get(3)); return boundingBox; }
Example #10
Source File: UserMappingDao.java From geopackage-java with MIT License | 5 votes |
/** * Get the unique related ids * * @return list of unique related ids * @since 3.2.0 */ public List<Long> uniqueRelatedIds() { return querySingleColumnTypedResults( "SELECT DISTINCT " + CoreSQLUtils .quoteWrap(UserMappingTable.COLUMN_RELATED_ID) + " FROM " + CoreSQLUtils.quoteWrap(getTableName()), null, GeoPackageDataType.INTEGER); }
Example #11
Source File: UserMappingDao.java From geopackage-java with MIT License | 5 votes |
/** * Get the unique base ids * * @return list of unique base ids * @since 3.2.0 */ public List<Long> uniqueBaseIds() { return querySingleColumnTypedResults( "SELECT DISTINCT " + CoreSQLUtils .quoteWrap(UserMappingTable.COLUMN_BASE_ID) + " FROM " + CoreSQLUtils.quoteWrap(getTableName()), null, GeoPackageDataType.INTEGER); }
Example #12
Source File: SQLExecAlterTable.java From geopackage-java with MIT License | 5 votes |
/** * Check for a drop column statement and execute * * @param database * database * @param sql * SQL statement * @return result if dropped column, null if not */ private static SQLExecResult dropColumn(GeoPackage database, String sql) { SQLExecResult result = null; Matcher matcher = DROP_COLUMN_PATTERN.matcher(sql); if (matcher.find()) { String tableName = CoreSQLUtils .quoteUnwrap(matcher.group(TABLE_NAME_GROUP)); if (tableName != null) { tableName = tableName.trim(); } String columnName = CoreSQLUtils .quoteUnwrap(matcher.group(COLUMN_NAME_GROUP)); if (columnName != null) { columnName = columnName.trim(); } if (tableName != null && columnName != null) { AlterTable.dropColumn(database.getDatabase(), tableName, columnName); result = new SQLExecResult(); } } return result; }
Example #13
Source File: AlterTableUtils.java From geopackage-java with MIT License | 5 votes |
/** * Create a table view * * @param db * connection * @param featureTable * feature column * @param viewName * view name * @param quoteWrap */ private static void createViewWithName(GeoPackageConnection db, FeatureTable featureTable, String viewName, boolean quoteWrap) { StringBuilder view = new StringBuilder("CREATE VIEW "); if (quoteWrap) { viewName = CoreSQLUtils.quoteWrap(viewName); } view.append(viewName); view.append(" AS SELECT "); for (int i = 0; i < featureTable.columnCount(); i++) { if (i > 0) { view.append(", "); } view.append(CoreSQLUtils.quoteWrap(featureTable.getColumnName(i))); view.append(" AS "); String columnName = "column" + (i + 1); if (quoteWrap) { columnName = CoreSQLUtils.quoteWrap(columnName); } view.append(columnName); } view.append(" FROM "); String tableName = featureTable.getTableName(); if (quoteWrap) { tableName = CoreSQLUtils.quoteWrap(tableName); } view.append(tableName); db.execSQL(view.toString()); }
Example #14
Source File: ConstraintParser.java From geopackage-core-java with MIT License | 5 votes |
/** * Get the constraint name if it has one * * @param constraintSql * constraint SQL * @return constraint name or null */ public static String getName(String constraintSql) { String name = null; Matcher matcher = NAME_PATTERN.matcher(constraintSql); if (matcher.find()) { name = CoreSQLUtils .quoteUnwrap(matcher.group(NAME_PATTERN_NAME_GROUP)); } return name; }
Example #15
Source File: Constraint.java From geopackage-core-java with MIT License | 5 votes |
/** * Build the name SQL * * @return name SQL */ protected String buildNameSql() { String sql = ""; if (name != null) { sql = CONSTRAINT + " " + CoreSQLUtils.quoteWrap(name) + " "; } return sql; }
Example #16
Source File: SQLExecAlterTable.java From geopackage-java with MIT License | 5 votes |
/** * Check for a rename table statement and execute * * @param database * database * @param sql * SQL statement * @return result if renamed table, null if not */ private static SQLExecResult renameTable(GeoPackage database, String sql) { SQLExecResult result = null; Matcher matcher = RENAME_TABLE_PATTERN.matcher(sql); if (matcher.find() && SQLExec.isGeoPackage(database)) { String tableName = CoreSQLUtils .quoteUnwrap(matcher.group(TABLE_NAME_GROUP)); if (tableName != null) { tableName = tableName.trim(); } String newTableName = CoreSQLUtils .quoteUnwrap(matcher.group(NEW_TABLE_NAME_GROUP)); if (newTableName != null) { newTableName = newTableName.trim(); } if (tableName != null && newTableName != null && database.getTableDataType(tableName) != null) { database.renameTable(tableName, newTableName); result = new SQLExecResult(); } } return result; }
Example #17
Source File: SQLExecAlterTable.java From geopackage-java with MIT License | 5 votes |
/** * Check for a copy table statement and execute * * @param database * database * @param sql * SQL statement * @return result if copied table, null if not */ private static SQLExecResult copyTable(GeoPackage database, String sql) { SQLExecResult result = null; Matcher matcher = COPY_TABLE_PATTERN.matcher(sql); if (matcher.find()) { String tableName = CoreSQLUtils .quoteUnwrap(matcher.group(TABLE_NAME_GROUP)); if (tableName != null) { tableName = tableName.trim(); } String newTableName = CoreSQLUtils .quoteUnwrap(matcher.group(NEW_TABLE_NAME_GROUP)); if (newTableName != null) { newTableName = newTableName.trim(); } if (tableName != null && newTableName != null) { if (SQLExec.isGeoPackage(database)) { database.copyTable(tableName, newTableName); } else { AlterTable.copyTable(database.getDatabase(), tableName, newTableName); } result = new SQLExecResult(); } } return result; }
Example #18
Source File: ManualFeatureQuery.java From geopackage-java with MIT License | 5 votes |
/** * Get the count of features with non null geometries * * @return count */ public int countWithGeometries() { return featureDao.count( CoreSQLUtils.quoteWrap(featureDao.getGeometryColumnName()) + " IS NOT NULL", null); }
Example #19
Source File: UserCoreDao.java From geopackage-core-java with MIT License | 5 votes |
/** * Build where statement for ids in the nested SQL query * * @param nestedSQL * nested SQL * @param where * where clause * @return where clause * @since 3.4.0 */ public String buildWhereIn(String nestedSQL, String where) { String nestedWhere = CoreSQLUtils.quoteWrap( table.getPkColumn().getName()) + " IN (" + nestedSQL + ")"; String whereClause; if (where == null) { whereClause = nestedWhere; } else { whereClause = "(" + where + ") AND (" + nestedWhere + ")"; } return whereClause; }
Example #20
Source File: FeaturePreview.java From geopackage-java with MIT License | 5 votes |
/** * Constructor * * @param geoPackage * GeoPackage * @param featureTiles * feature tiles */ public FeaturePreview(GeoPackage geoPackage, FeatureTiles featureTiles) { this.geoPackage = geoPackage; this.featureTiles = featureTiles; FeatureDao featureDao = featureTiles.getFeatureDao(); columns.add(featureDao.getIdColumnName()); columns.add(featureDao.getGeometryColumnName()); where = CoreSQLUtils.quoteWrap(featureDao.getGeometryColumnName()) + " IS NOT NULL"; }
Example #21
Source File: SQLExec.java From geopackage-java with MIT License | 5 votes |
/** * Build a SQLite Master table query * * @param tableName * true to include table name * @param type * SQLite Master type * @param name * name LIKE value * @return SQL */ private static String buildSqlMasterQuery(boolean tableName, SQLiteMasterType type, String name) { StringBuilder sql = new StringBuilder("SELECT "); sql.append(SQLiteMasterColumn.NAME.name().toLowerCase()); if (tableName) { sql.append(", "); sql.append(SQLiteMasterColumn.TBL_NAME.name().toLowerCase()); } sql.append(" FROM "); sql.append(SQLiteMaster.TABLE_NAME); sql.append(" WHERE "); sql.append(SQLiteMasterColumn.TYPE.name().toLowerCase()); sql.append(" = '"); sql.append(type.name().toLowerCase()); sql.append("' AND "); sql.append(SQLiteMasterColumn.NAME.name().toLowerCase()); sql.append(" NOT LIKE 'sqlite_%'"); if (name != null) { name = name.trim(); if (!name.isEmpty()) { sql.append(" AND "); sql.append(SQLiteMasterColumn.NAME.name().toLowerCase()); sql.append(" LIKE "); sql.append(CoreSQLUtils.quoteWrap(name)); } } sql.append(" ORDER BY "); sql.append(SQLiteMasterColumn.NAME.name().toLowerCase()); sql.append(";"); return sql.toString(); }
Example #22
Source File: RTreeIndexTableDao.java From geopackage-android with MIT License | 5 votes |
/** * {@inheritDoc} */ @Override public BoundingBox getBoundingBox() { List<Double> values = querySingleRowTypedResults( "SELECT MIN(" + RTreeIndexExtension.COLUMN_MIN_X + "), MIN(" + RTreeIndexExtension.COLUMN_MIN_Y + "), MAX(" + RTreeIndexExtension.COLUMN_MAX_X + "), MAX(" + RTreeIndexExtension.COLUMN_MAX_Y + ") FROM " + CoreSQLUtils.quoteWrap(getTableName()), null); BoundingBox boundingBox = new BoundingBox(values.get(0), values.get(1), values.get(2), values.get(3)); return boundingBox; }
Example #23
Source File: ManualFeatureQuery.java From geopackage-android with MIT License | 5 votes |
/** * Get the count of features with non null geometries * * @return count */ public int countWithGeometries() { return featureDao.count( CoreSQLUtils.quoteWrap(featureDao.getGeometryColumnName()) + " IS NOT NULL", null); }
Example #24
Source File: GeoPackageImpl.java From geopackage-android with MIT License | 5 votes |
/** * {@inheritDoc} */ @Override public Cursor foreignKeyCheck(String tableName) { Cursor cursor = rawQuery(CoreSQLUtils.foreignKeyCheckSQL(tableName), null); if (!cursor.moveToNext()) { cursor.close(); cursor = null; } return cursor; }
Example #25
Source File: NGAExtensions.java From geopackage-core-java with MIT License | 5 votes |
/** * Copy the Tile Scaling extensions for the table * * @param geoPackage * GeoPackage * @param table * table name * @param newTable * new table name * @since 3.3.0 */ public static void copyTileScaling(GeoPackageCore geoPackage, String table, String newTable) { try { TileTableScaling tileTableScaling = new TileTableScaling(geoPackage, table); if (tileTableScaling.has()) { Extensions extension = tileTableScaling.getExtension(); if (extension != null) { extension.setTableName(newTable); tileTableScaling.getExtensionsDao().create(extension); if (geoPackage.isTable(TileScaling.TABLE_NAME)) { CoreSQLUtils.transferTableContent( geoPackage.getDatabase(), TileScaling.TABLE_NAME, TileScaling.COLUMN_TABLE_NAME, newTable, table); } } } } catch (Exception e) { logger.log(Level.WARNING, "Failed to create Tile Scaling for table: " + newTable + ", copied from table: " + table, e); } }
Example #26
Source File: UserInvalidCursor.java From geopackage-android with MIT License | 5 votes |
/** * Read the blob column value in chunks * * @param row user row * @param column user blob column */ private void readBlobValue(UserRow row, UserColumn column) { ByteArrayOutputStream byteStream = new ByteArrayOutputStream(); try { byte[] blobChunk = new byte[]{0}; for (int i = 1; blobChunk.length > 0; i += CHUNK_SIZE) { if (i > 1) { byteStream.write(blobChunk); } blobChunk = new byte[]{}; String query = "select substr(" + CoreSQLUtils.quoteWrap(column.getName()) + ", " + i + ", " + CHUNK_SIZE + ") from " + CoreSQLUtils.quoteWrap(dao.getTableName()) + " where " + CoreSQLUtils.quoteWrap(row.getPkColumn().getName()) + " = " + row.getId(); Cursor blobCursor = dao.getDatabaseConnection().getDb().rawQuery(query, null); try { if (blobCursor.moveToNext()) { blobChunk = blobCursor.getBlob(0); } } finally { blobCursor.close(); } } byte[] blob = byteStream.toByteArray(); row.setValue(column.getIndex(), blob); } catch (IOException e) { Log.e(UserInvalidCursor.class.getSimpleName(), "Failed to read large blob value. Table: " + dao.getTableName() + ", Column: " + column.getName() + ", Position: " + getPosition(), e); } finally { IOUtils.closeQuietly(byteStream); } }
Example #27
Source File: FeaturePreview.java From geopackage-android with MIT License | 5 votes |
/** * Constructor * * @param geoPackage GeoPackage * @param featureTiles feature tiles */ public FeaturePreview(GeoPackage geoPackage, FeatureTiles featureTiles) { this.geoPackage = geoPackage; this.featureTiles = featureTiles; FeatureDao featureDao = featureTiles.getFeatureDao(); columns.add(featureDao.getIdColumnName()); columns.add(featureDao.getGeometryColumnName()); where = CoreSQLUtils.quoteWrap(featureDao.getGeometryColumnName()) + " IS NOT NULL"; }
Example #28
Source File: AlterTableUtils.java From geopackage-android with MIT License | 5 votes |
/** * Create a table view * * @param db connection * @param featureTable feature column * @param viewName view name * @param quoteWrap */ private static void createViewWithName(GeoPackageConnection db, FeatureTable featureTable, String viewName, boolean quoteWrap) { StringBuilder view = new StringBuilder("CREATE VIEW "); if (quoteWrap) { viewName = CoreSQLUtils.quoteWrap(viewName); } view.append(viewName); view.append(" AS SELECT "); for (int i = 0; i < featureTable.columnCount(); i++) { if (i > 0) { view.append(", "); } view.append(CoreSQLUtils.quoteWrap(featureTable.getColumnName(i))); view.append(" AS "); String columnName = "column" + (i + 1); if (quoteWrap) { columnName = CoreSQLUtils.quoteWrap(columnName); } view.append(columnName); } view.append(" FROM "); String tableName = featureTable.getTableName(); if (quoteWrap) { tableName = CoreSQLUtils.quoteWrap(tableName); } view.append(tableName); db.execSQL(view.toString()); }
Example #29
Source File: FeaturePreviewUtils.java From geopackage-java with MIT License | 4 votes |
/** * Test the GeoPackage draw feature preview * * @param geoPackage * GeoPackage * @throws IOException * upon error */ public static void testDraw(GeoPackage geoPackage) throws IOException { for (String featureTable : geoPackage.getFeatureTables()) { FeatureDao featureDao = geoPackage.getFeatureDao(featureTable); int count = featureDao.count( CoreSQLUtils.quoteWrap(featureDao.getGeometryColumnName()) + " IS NOT NULL"); BoundingBox contentsBoundingBox = geoPackage .getContentsBoundingBox(featureTable); BoundingBox indexedBoundingBox = geoPackage .getBoundingBox(featureTable); boolean expectImage = (contentsBoundingBox != null || indexedBoundingBox != null) && count > 0; boolean epsg = featureDao.getProjection().getAuthority() .equalsIgnoreCase(ProjectionConstants.AUTHORITY_EPSG); FeaturePreview preview = new FeaturePreview(geoPackage, featureDao); BufferedImage image = preview.draw(); if (epsg) { assertEquals(expectImage, image != null); } if (writeImages) { ImageIO.write(image, "png", new File("image.png")); } preview.setBufferPercentage(0.4); preview.setLimit((int) Math.ceil(count / 2.0)); BufferedImage imageLimit = preview.draw(); if (epsg) { assertEquals(expectImage, imageLimit != null); } if (writeImages) { ImageIO.write(imageLimit, "png", new File("image_limit.png")); } preview.setManual(true); preview.setBufferPercentage(0.05); preview.setLimit(null); FeatureTiles featureTiles = preview.getFeatureTiles(); featureTiles.setTileWidth(TileUtils.TILE_PIXELS_DEFAULT); featureTiles.setTileHeight(TileUtils.TILE_PIXELS_DEFAULT); featureTiles.setScale( TileUtils.tileScale(TileUtils.TILE_PIXELS_DEFAULT)); featureTiles.clearIconCache(); BufferedImage imageManual = preview.draw(); if (epsg) { assertNotNull(imageManual); } if (writeImages) { ImageIO.write(imageManual, "png", new File("image_manual.png")); } preview.setBufferPercentage(0.35); preview.setLimit(Math.max(count - 1, 1)); BufferedImage imageManualLimit = preview.draw(); if (epsg) { assertNotNull(imageManualLimit); } if (writeImages) { ImageIO.write(imageManualLimit, "png", new File("image_manual_limit.png")); } preview.setBufferPercentage(0.15); preview.setLimit(null); preview.appendWhere( CoreSQLUtils.quoteWrap(featureDao.getIdColumnName()) + " > " + ((int) Math.floor(count / 2.0))); BufferedImage imageManualWhere = preview.draw(); if (epsg) { assertNotNull(imageManualWhere); } if (writeImages) { ImageIO.write(imageManualWhere, "png", new File("image_manual_where.png")); System.out.println("Breakpoint here"); } } }
Example #30
Source File: UserCoreDao.java From geopackage-core-java with MIT License | 4 votes |
/** * Drop the user table */ public void dropTable() { CoreSQLUtils.dropTable(db, getTableName()); }