mil.nga.geopackage.db.master.SQLiteMaster Java Examples

/**
 * Test a transaction on the GeoPackage
 *
 * @param geoPackage GeoPackage
 * @param successful true for a successful transaction
 */
private void testGeoPackage(GeoPackage geoPackage, boolean successful) {

    int count = SQLiteMaster.countViewsOnTable(geoPackage.getConnection(),
            Contents.TABLE_NAME);

    geoPackage.beginTransaction();

    try {

        geoPackage.execSQL("CREATE VIEW " + Contents.TABLE_NAME
                + "_view AS SELECT table_name AS tableName FROM "
                + Contents.TABLE_NAME);

    } catch (Exception e) {

        geoPackage.failTransaction();
        TestCase.fail(e.getMessage());

    } finally {

        if (successful) {
            geoPackage.endTransaction();
        } else {
            geoPackage.failTransaction();
        }

    }

    TestCase.assertEquals(successful ? count + 1 : count, SQLiteMaster
            .countViewsOnTable(geoPackage.getConnection(),
                    Contents.TABLE_NAME));
}
 

/**
 * Get the expected index count
 *
 * @param db        connection
 * @param tableName table name
 * @return index count
 */
private static int indexCount(GeoPackageCoreConnection db,
                              String tableName) {
    SQLiteMasterQuery indexQuery = SQLiteMasterQuery.createAnd();
    indexQuery.add(SQLiteMasterColumn.TBL_NAME, tableName);
    indexQuery.addIsNotNull(SQLiteMasterColumn.SQL);
    int count = SQLiteMaster.count(db, SQLiteMasterType.INDEX, indexQuery);
    return count;
}
 

/**
 * Test the table schema counts
 *
 * @param db           connection
 * @param tableName    table name
 * @param tableCount   table count
 * @param indexCount   index count
 * @param triggerCount trigger count
 * @param viewCount    view count
 */
private static void testTableCounts(GeoPackageConnection db,
                                    String tableName, int tableCount, int indexCount, int triggerCount,
                                    int viewCount) {
    TestCase.assertEquals(tableCount,
            SQLiteMaster.count(db, SQLiteMasterType.TABLE, tableName));
    TestCase.assertEquals(indexCount, indexCount(db, tableName));
    TestCase.assertEquals(triggerCount,
            SQLiteMaster.count(db, SQLiteMasterType.TRIGGER, tableName));
    TestCase.assertEquals(viewCount,
            SQLiteMaster.countViewsOnTable(db, tableName));
}
 

/**
 * Read the table
 * 
 * @param db
 *            connection
 * @return table
 */
public TTable readTable(GeoPackageCoreConnection db) {

	List<TColumn> columnList = new ArrayList<TColumn>();

	TableInfo tableInfo = TableInfo.info(db, tableName);
	if (tableInfo == null) {
		throw new GeoPackageException("Table does not exist: " + tableName);
	}

	TableConstraints constraints = SQLiteMaster.queryForConstraints(db,
			tableName);

	for (TableColumn tableColumn : tableInfo.getColumns()) {
		if (tableColumn.getDataType() == null) {
			throw new GeoPackageException("Unsupported column data type "
					+ tableColumn.getType());
		}
		TColumn column = createColumn(tableColumn);

		ColumnConstraints columnConstraints = constraints
				.getColumnConstraints(column.getName());
		if (columnConstraints != null
				&& columnConstraints.hasConstraints()) {
			column.clearConstraints();
			column.addConstraints(columnConstraints);
		}

		columnList.add(column);
	}

	TTable table = createTable(tableName, columnList);

	table.addConstraints(constraints.getTableConstraints());

	return table;
}
 

/**
 * Create a new name by replacing a case insensitive value with a new value.
 * If no replacement is done, create a new name in the form name_#, where #
 * is either 2 or one greater than an existing name number suffix. When a db
 * connection is provided, check for conflicting SQLite Master names and
 * increment # until an available name is found.
 * 
 * @param db
 *            optional connection, used for SQLite Master name conflict
 *            detection
 * @param name
 *            current name
 * @param replace
 *            value to replace
 * @param replacement
 *            replacement value
 * @return new name
 * @since 3.3.0
 */
public static String createName(GeoPackageCoreConnection db, String name,
		String replace, String replacement) {

	// Attempt the replacement
	String newName = name.replaceAll("(?i)" + replace, replacement);

	// If no name change was made
	if (newName.equals(name)) {

		String baseName = newName;
		int count = 1;

		// Find any existing end number: name_#
		int index = baseName.lastIndexOf("_");
		if (index >= 0 && index + 1 < baseName.length()) {
			String numberPart = baseName.substring(index + 1);
			if (NUMBER_PATTERN.matcher(numberPart).matches()) {
				baseName = baseName.substring(0, index);
				count = Integer.valueOf(numberPart);
			}
		}

		// Set the new name to name_2 or name_(#+1)
		newName = baseName + "_" + (++count);

		if (db != null) {
			// Check for conflicting SQLite Master table names
			while (SQLiteMaster.count(db, SQLiteMasterQuery
					.create(SQLiteMasterColumn.NAME, newName)) > 0) {
				newName = baseName + "_" + (++count);
			}
		}

	}

	return newName;
}
 

/**
 * 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();
}
 

/**
 * Test a transaction on the GeoPackage
 * 
 * @param geoPackage
 *            GeoPackage
 * @param successful
 *            true for a successful transaction
 * @throws SQLException
 *             upon error
 */
private void testGeoPackage(GeoPackage geoPackage, boolean successful)
		throws SQLException {

	int count = SQLiteMaster.countViewsOnTable(geoPackage.getConnection(),
			Contents.TABLE_NAME);

	geoPackage.beginTransaction();

	try {

		geoPackage.execSQL("CREATE VIEW " + Contents.TABLE_NAME
				+ "_view AS SELECT table_name AS tableName FROM "
				+ Contents.TABLE_NAME);

	} catch (Exception e) {

		geoPackage.failTransaction();
		TestCase.fail(e.getMessage());

	} finally {

		if (successful) {
			geoPackage.endTransaction();
		} else {
			geoPackage.failTransaction();
		}

	}

	TestCase.assertEquals(successful ? count + 1 : count, SQLiteMaster
			.countViewsOnTable(geoPackage.getConnection(),
					Contents.TABLE_NAME));
}
 

/**
 * Get the expected index count
 * 
 * @param db
 *            connection
 * @param tableName
 *            table name
 * @return index count
 */
private static int indexCount(GeoPackageCoreConnection db,
		String tableName) {
	SQLiteMasterQuery indexQuery = SQLiteMasterQuery.createAnd();
	indexQuery.add(SQLiteMasterColumn.TBL_NAME, tableName);
	indexQuery.addIsNotNull(SQLiteMasterColumn.SQL);
	int count = SQLiteMaster.count(db, SQLiteMasterType.INDEX, indexQuery);
	return count;
}
 

/**
 * Test the table schema counts
 * 
 * @param db
 *            connection
 * @param tableName
 *            table name
 * @param tableCount
 *            table count
 * @param indexCount
 *            index count
 * @param triggerCount
 *            trigger count
 * @param viewCount
 *            view count
 */
private static void testTableCounts(GeoPackageConnection db,
		String tableName, int tableCount, int indexCount, int triggerCount,
		int viewCount) {
	TestCase.assertEquals(tableCount,
			SQLiteMaster.count(db, SQLiteMasterType.TABLE, tableName));
	TestCase.assertEquals(indexCount, indexCount(db, tableName));
	TestCase.assertEquals(triggerCount,
			SQLiteMaster.count(db, SQLiteMasterType.TRIGGER, tableName));
	TestCase.assertEquals(viewCount,
			SQLiteMaster.countViewsOnTable(db, tableName));
}
 

/**
 * Check if the table exists
 * 
 * @param tableName
 *            table name
 * @return true if exists
 */
public boolean tableExists(String tableName) {
	return SQLiteMaster.count(this, SQLiteMasterType.TABLE, tableName) > 0;
}