mil.nga.geopackage.db.GeoPackageTableCreator Maven / Gradle / Ivy

Go to download
package mil.nga.geopackage.db;

import java.io.File;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.List;
import java.util.Scanner;
import java.util.regex.Pattern;

import mil.nga.geopackage.GeoPackageException;
import mil.nga.geopackage.core.srs.SpatialReferenceSystem;
import mil.nga.geopackage.core.srs.SpatialReferenceSystemDao;
import mil.nga.geopackage.property.GeoPackageProperties;
import mil.nga.geopackage.property.PropertyConstants;
import mil.nga.geopackage.user.UserColumn;
import mil.nga.geopackage.user.UserTable;
import mil.nga.geopackage.user.UserUniqueConstraint;

import com.j256.ormlite.dao.DaoManager;

/**
 * Executes database scripts to create GeoPackage tables
 * 
 * @author osbornb
 */
public class GeoPackageTableCreator {

	/**
	 * SQLite database
	 */
	private final GeoPackageCoreConnection db;

	/**
	 * Constructor
	 * 
	 * @param db
	 */
	public GeoPackageTableCreator(GeoPackageCoreConnection db) {
		this.db = db;
	}

	/**
	 * Create Spatial Reference System table and views
	 * 
	 * @return
	 */
	public int createSpatialReferenceSystem() {
		return createTable(GeoPackageProperties.getProperty(
				PropertyConstants.SQL, "spatial_reference_system"));
	}

	/**
	 * Create Contents table
	 * 
	 * @return
	 */
	public int createContents() {
		return createTable(GeoPackageProperties.getProperty(
				PropertyConstants.SQL, "contents"));
	}

	/**
	 * Create Geometry Columns table
	 * 
	 * @return executed statements
	 */
	public int createGeometryColumns() {
		return createTable(GeoPackageProperties.getProperty(
				PropertyConstants.SQL, "geometry_columns"));
	}

	/**
	 * Create Tile Matrix Set table
	 * 
	 * @return executed statements
	 */
	public int createTileMatrixSet() {
		return createTable(GeoPackageProperties.getProperty(
				PropertyConstants.SQL, "tile_matrix_set"));
	}

	/**
	 * Create Tile Matrix table
	 * 
	 * @return executed statements
	 */
	public int createTileMatrix() {
		return createTable(GeoPackageProperties.getProperty(
				PropertyConstants.SQL, "tile_matrix"));
	}

	/**
	 * Create Data Columns table
	 * 
	 * @return executed statements
	 */
	public int createDataColumns() {
		return createTable(GeoPackageProperties.getProperty(
				PropertyConstants.SQL, "data_columns"));
	}

	/**
	 * Create Data Column Constraints table
	 * 
	 * @return executed statements
	 */
	public int createDataColumnConstraints() {
		return createTable(GeoPackageProperties.getProperty(
				PropertyConstants.SQL, "data_column_constraints"));
	}

	/**
	 * Create Metadata table
	 * 
	 * @return executed statements
	 */
	public int createMetadata() {
		return createTable(GeoPackageProperties.getProperty(
				PropertyConstants.SQL, "metadata"));
	}

	/**
	 * Create Metadata Reference table
	 * 
	 * @return executed statements
	 */
	public int createMetadataReference() {
		return createTable(GeoPackageProperties.getProperty(
				PropertyConstants.SQL, "metadata_reference"));
	}

	/**
	 * Create Extensions table
	 * 
	 * @return executed statements
	 */
	public int createExtensions() {
		return createTable(GeoPackageProperties.getProperty(
				PropertyConstants.SQL, "extensions"));
	}

	/**
	 * Create Table Index table
	 * 
	 * @return executed statements
	 * @since 1.1.0
	 */
	public int createTableIndex() {
		return createTable(GeoPackageProperties.getProperty(
				PropertyConstants.SQL, "table_index"));
	}

	/**
	 * Create Geometry Index table
	 * 
	 * @return executed statements
	 * @since 1.1.0
	 */
	public int createGeometryIndex() {
		return createTable(GeoPackageProperties.getProperty(
				PropertyConstants.SQL, "geometry_index"));
	}

	/**
	 * Create a table using the table script
	 * 
	 * @param tableScript
	 * @return
	 */
	private int createTable(String tableScript) {
		InputStream scriptStream = Thread
				.currentThread()
				.getContextClassLoader()
				.getResourceAsStream(
						GeoPackageProperties.getProperty(PropertyConstants.SQL,
								"directory") + File.separatorChar + tableScript);
		int statements = runScript(scriptStream);
		return statements;
	}

	/**
	 * Run the script input stream
	 * 
	 * @param stream
	 * @return executed statements
	 */
	private int runScript(final InputStream stream) {
		int count = 0;

		// Use multiple newlines as the delimiter
		Scanner s = new Scanner(stream);
		try {
			s.useDelimiter(Pattern.compile("\\n\\s*\\n"));
			// Execute each statement
			while (s.hasNext()) {
				String statement = s.next().trim();
				db.execSQL(statement);
				count++;
			}
		} finally {
			s.close();
		}
		return count;
	}

	/**
	 * Create the user defined table
	 * 
	 * @param table
	 * @param 
	 */
	public  void createTable(
			UserTable table) {

		// Verify the table does not already exist
		if (db.tableExists(table.getTableName())) {
			throw new GeoPackageException(
					"Table already exists and can not be created: "
							+ table.getTableName());
		}

		// Build the create table sql
		StringBuilder sql = new StringBuilder();
		sql.append("CREATE TABLE ").append(table.getTableName()).append(" (");

		// Add each column to the sql
		List columns = table.getColumns();
		for (int i = 0; i < columns.size(); i++) {
			UserColumn column = columns.get(i);
			if (i > 0) {
				sql.append(",");
			}
			sql.append("\n  ").append(column.getName()).append(" ")
					.append(column.getTypeName());
			if (column.getMax() != null) {
				sql.append("(").append(column.getMax()).append(")");
			}
			if (column.isNotNull()) {
				sql.append(" NOT NULL");
			}
			if (column.isPrimaryKey()) {
				sql.append(" PRIMARY KEY AUTOINCREMENT");
			}
		}

		// Add unique constraints
		List> uniqueConstraints = table
				.getUniqueConstraints();
		for (int i = 0; i < uniqueConstraints.size(); i++) {
			UserUniqueConstraint uniqueConstraint = uniqueConstraints
					.get(i);
			sql.append(",\n  UNIQUE (");
			List uniqueColumns = uniqueConstraint.getColumns();
			for (int j = 0; j < uniqueColumns.size(); j++) {
				TColumn uniqueColumn = uniqueColumns.get(j);
				if (j > 0) {
					sql.append(", ");
				}
				sql.append(uniqueColumn.getName());
			}
			sql.append(")");
		}

		sql.append("\n);");

		// Create the table
		db.execSQL(sql.toString());
	}

	/**
	 * Create the minimum required GeoPackage tables
	 */
	public void createRequired() {

		// Create the Spatial Reference System table (spec Requirement 10)
		createSpatialReferenceSystem();

		// Create the Contents table (spec Requirement 13)
		createContents();

		// Create the required Spatial Reference Systems (spec Requirement
		// 11)
		try {
			SpatialReferenceSystemDao dao = DaoManager.createDao(
					db.getConnectionSource(), SpatialReferenceSystem.class);
			dao.createWgs84();
			dao.createUndefinedCartesian();
			dao.createUndefinedGeographic();
		} catch (SQLException e) {
			throw new GeoPackageException(
					"Error creating default required Spatial Reference Systems",
					e);
		}
	}

}