/* * Geopaparazzi - Digital field mapping on Android based devices * Copyright (C) 2010 HydroloGIS (www.hydrologis.com) * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. */ package eu.geopaparazzi.spatialite.database.spatial.core.daos; import java.io.File; import java.io.IOException; import java.util.HashMap; import eu.geopaparazzi.library.database.GPLog; import eu.geopaparazzi.spatialite.database.spatial.core.enums.SpatialiteDatabaseType; import eu.geopaparazzi.spatialite.database.spatial.core.enums.SpatialiteVersion; import jsqlite.Database; import jsqlite.Exception; import jsqlite.Stmt; /** * Dao that handles database engine properties. */ public class DatabaseCreationAndProperties implements ISpatialiteTableAndFieldsNames { /** * Return info of supported versions. * - will be filled on first Database connection when empty * -- called in checkDatabaseTypeAndValidity */ public static String JavaSqliteDescription = ""; /** * General Function to create jsqlite.Database with spatialite support. * <ol> * <li> parent directories will be created, if needed</li> * <li> needed Tables/View and default values for metadata-table will be created</li> * </ol> * * @param databasePath name of Database file to create * @return sqlite_db: pointer to Database created * @throws java.io.IOException if something goes wrong. */ public static Database createDb(String databasePath) throws IOException { File file_db = new File(databasePath); if (!file_db.getParentFile().exists()) { File dir_db = file_db.getParentFile(); if (!dir_db.mkdir()) { throw new IOException("DaoSpatialite: create_db: dir_db[" + dir_db.getAbsolutePath() + "] creation failed"); //$NON-NLS-1$ //$NON-NLS-2$ } } Database spatialiteDatabase = new Database(); try { spatialiteDatabase.open(file_db.getAbsolutePath(), jsqlite.Constants.SQLITE_OPEN_READWRITE | jsqlite.Constants.SQLITE_OPEN_CREATE); createSpatialiteDb(spatialiteDatabase, false); } catch (jsqlite.Exception e_stmt) { GPLog.error("DAOSPATIALIE", "create_spatialite[spatialite] dir_file[" + file_db.getAbsolutePath() //$NON-NLS-1$ + "]", e_stmt); //$NON-NLS-1$ } return spatialiteDatabase; } /** * General Function to create jsqlite.Database with spatialite support. * <p/> * <ol> * <li> parent directories will be created, if needed</li> * <li> needed Tables/View and default values for metadata-table will be created</li> * </ol> * * @param sqliteDatabase pointer to Database * @param doCheck is true, a new Database is created without checking if it is already one. * @throws jsqlite.Exception if something goes wrong. */ public static void createSpatialiteDb(Database sqliteDatabase, boolean doCheck) throws jsqlite.Exception { boolean createDb = true; if (doCheck) { SpatialiteVersion spatialiteVersion = getSpatialiteDatabaseVersion(sqliteDatabase, ""); // this is a spatialite Database, do not create if (spatialiteVersion.getCode() > SpatialiteVersion.NO_SPATIALITE.getCode()) { createDb = false; if (spatialiteVersion.getCode() < SpatialiteVersion.UNTIL_3_1_0_RC2.getCode()) { // TODO: logic for conversion to latest Spatialite // Version [open] throw new Exception("Spatialite version < 3 not supported."); } } } if (createDb) { String s_sql_command = "SELECT InitSpatialMetadata();"; //$NON-NLS-1$ try { sqliteDatabase.exec(s_sql_command, null); } catch (jsqlite.Exception e_stmt) { int errorCode = sqliteDatabase.last_error(); GPLog.error("DAOSPATIALIE", "create_spatialite sql[" + s_sql_command + "] errorCode=" + errorCode + "]", e_stmt); //$NON-NLS-1$ //$NON-NLS-2$ } SpatialiteVersion spatialiteVersion = getSpatialiteDatabaseVersion(sqliteDatabase, ""); //$NON-NLS-1$ if (spatialiteVersion.getCode() < 3) { // error, should be 3 or 4 GPLog.addLogEntry("DAOSPATIALIE", "create_spatialite spatialite_version[" + spatialiteVersion + "]"); //$NON-NLS-1$ //$NON-NLS-2$ } } } /** * Checks the database type and its validity. * - Spatialite 2.4 to present version are supported (2.4 will be set as 3) * * @param database the database to check. * @param spatialVectorMap the {@link java.util.HashMap} of database views data to clear and repopulate. * @param spatialVectorMapErrors * @return the {@link SpatialiteDatabaseType}. */ public static SpatialiteDatabaseType checkDatabaseTypeAndValidity(Database database, HashMap<String, String> spatialVectorMap, HashMap<String, String> spatialVectorMapErrors) throws Exception { // clear views spatialVectorMap.clear(); spatialVectorMapErrors.clear(); if (DatabaseCreationAndProperties.JavaSqliteDescription.equals("")) { // Rasterlite2Version_CPU will NOT be empty, if the // Driver was compiled with RasterLite2 support DatabaseCreationAndProperties.getJavaSqliteDescription(database, "DaoSpatialite.checkDatabaseTypeAndValidity"); GPLog.addLogEntry("DAOSPATIALIE", "JavaSqliteDescription[" + DatabaseCreationAndProperties.JavaSqliteDescription + "] recovery_mode[" + SPL_Vectors.VECTORLAYER_QUERYMODE + "]"); } // views: vector_layers_statistics,vector_layers // pre-spatialite 3.0 Databases often do not have a Virtual-SpatialIndex table boolean b_SpatialIndex = false; boolean b_vector_layers_statistics = false; boolean b_vector_layers = false; // tables: geometry_columns,raster_columns boolean b_geometry_columns = false; // spatialite 2.0, 2.1 and 2.3 do NOT have a views_geometry_columns table boolean b_views_geometry_columns = false; // spatialite 4.2.0 - RasterLite2 table [raster_coverages] boolean b_raster_coverages = false; // this table dissapered (maybe 4.1.0) - when vector_layers_statistics is not set, this may // be used for the bounds boolean b_layers_statistics = false; // boolean b_raster_columns = false; boolean b_gpkg_contents = false; String sqlCommand = "SELECT name,type,sql FROM sqlite_master WHERE ((type='table') OR (type='view')) ORDER BY type DESC,name ASC"; String tableType = ""; String name = ""; Stmt statement = null; try { statement = database.prepare(sqlCommand); while (statement.step()) { name = statement.column_string(0); tableType = statement.column_string(1); if (tableType.equals("table")) { if ((name.equals("geometry_columns")) || (name.equals("sqlite_stat1"))) { b_geometry_columns = true; } else if (name.equals("SpatialIndex")) { b_SpatialIndex = true; } else if (name.equals("views_geometry_columns")) { b_views_geometry_columns = true; } else if (name.equals("raster_coverages")) { b_raster_coverages = true; } else if (name.equals("layers_statistics")) { b_layers_statistics = true; } else if (name.equals(METADATA_GEOPACKAGE_TABLE_NAME)) { b_gpkg_contents = true; } // if (name.equals("raster_columns")) { // b_raster_columns = true; // } } else if (tableType.equals("view")) { // we are looking for user-defined views only, // filter out system known views. if ((!name.equals("geom_cols_ref_sys")) && (!name.startsWith("vector_layers"))) { // databaseViewsMap.put(name, sqlCreationString); } else if (name.equals("vector_layers_statistics")) { b_vector_layers_statistics = true; } else if (name.equals("vector_layers")) { b_vector_layers = true; } } } } catch (Exception e) { GPLog.error("DAOSPATIALITE", "Error in checkDatabaseTypeAndValidity sql[" + sqlCommand + "] db[" + database.getFilename() + "]", e); } finally { if (statement != null) { statement.close(); } } if (b_gpkg_contents) { // this is a GeoPackage, this can also have // vector_layers_statistics and vector_layers // - the results are empty, it does reference the table // also referenced in gpkg_contents SPL_Geopackage.getGeoPackageMap_R10(database, spatialVectorMap, spatialVectorMapErrors); if (spatialVectorMap.size() > 0) return SpatialiteDatabaseType.UNKNOWN; // return SpatialiteDatabaseType.GEOPACKAGE; else // if empty, nothing to load return SpatialiteDatabaseType.UNKNOWN; } else { if ((b_vector_layers_statistics) && (b_vector_layers)) { // Spatialite 4.0 SPL_Vectors.getSpatialVectorMap_V4(database, spatialVectorMap, spatialVectorMapErrors, b_layers_statistics, b_raster_coverages); if (spatialVectorMap.size() > 0) return SpatialiteDatabaseType.SPATIALITE4; else // if empty, nothing to load return SpatialiteDatabaseType.UNKNOWN; } else { if ((b_geometry_columns) && (b_views_geometry_columns)) { // Spatialite from 2.4 // until 4.0 SPL_Vectors.getSpatialVectorMap_V3(database, spatialVectorMap, spatialVectorMapErrors, b_layers_statistics, b_SpatialIndex); if (spatialVectorMap.size() > 0) return SpatialiteDatabaseType.SPATIALITE3; else // if empty, nothing to load return SpatialiteDatabaseType.UNKNOWN; } } } return SpatialiteDatabaseType.UNKNOWN; } /** * Checks if a table exists. * * @param database the db to use. * @param name the table name to check. * @return the number of columns, if the table exists or 0 if the table doesn't exist. * @throws Exception if something goes wrong. */ public static int checkTableExistence(Database database, String name) throws Exception { String checkTableQuery = "SELECT sql FROM sqlite_master WHERE type='table' AND name='" + name + "';"; Stmt statement = null; try { statement = database.prepare(checkTableQuery); if (statement.step()) { String creationSql = statement.column_string(0); if (creationSql != null) { String[] split = creationSql.trim().split("\\(|\\)"); if (split.length != 2) { throw new RuntimeException("Can't parse creation sql: " + creationSql); } String fieldsString = split[1]; String[] fields = fieldsString.split(","); return fields.length; } } return 0; } finally { if (statement != null) statement.close(); } } /** * Return info of supported versions in JavaSqlite. * <p/> * <br>- SQLite used by the Database-Driver * <br>- Spatialite * <br>- Proj4 * <br>- Geos * <br>-- there is no Spatialite function to retrieve the Sqlite version * <br>-- the Has() functions do not work with spatialite 3.0.1 * * @param database the db to use. * @param name a name for the log. * @return info of supported versions in JavaSqlite. */ public static String getJavaSqliteDescription(Database database, String name) { if (JavaSqliteDescription.equals("")) { int majorVersion = 0; try { // s_javasqlite_description = "javasqlite[" + getJavaSqliteVersion() + "],"; JavaSqliteDescription = "sqlite[" + getSqliteVersion(database) + "],"; String spatialiteVersionNumber = getSpatialiteVersionNumber(database); if (!spatialiteVersionNumber.equals("-")) majorVersion = Integer.parseInt(spatialiteVersionNumber.substring(0, 1)); JavaSqliteDescription += "spatialite[" + spatialiteVersionNumber + "],"; JavaSqliteDescription += "proj4[" + getProj4Version(database) + "],"; JavaSqliteDescription += "geos[" + getGeosVersion(database) + "],"; JavaSqliteDescription += "spatialite_properties[" + getSpatialiteProperties(database) + "],"; JavaSqliteDescription += "rasterlite2_properties[" + getRaster2Version(database) + "]]"; } catch (Exception e) { if (majorVersion > 3) { JavaSqliteDescription += "rasterlite2_properties[none]]"; } else { JavaSqliteDescription += "exception[? not a spatialite database, or spatialite < 4 ?]]"; } GPLog.error("DAOSPATIALIE", "[" + name + "].getJavaSqliteDescription[" + JavaSqliteDescription + "]", e); } } return JavaSqliteDescription; } /** * Return SQLite version number as string. * - as used by the Driver that queries for Spatialite * * @param database the db to use. * @return the version of sqlite. * @throws Exception if something goes wrong. */ public static String getSqliteVersion(Database database) throws Exception { return database.dbversion(); } /** * Get the version of Spatialite. * * @param database the db to use. * @return the version of Spatialite. * @throws Exception if something goes wrong. */ public static String getSpatialiteVersionNumber(Database database) throws Exception { Stmt stmt = database.prepare("SELECT spatialite_version();"); try { if (stmt.step()) { return stmt.column_string(0); } } finally { stmt.close(); } return "-"; } /** * Determine the Spatialite version of the Database being used. * <p/> * <ul> * <li> - if (sqlite3_exec(this_handle_sqlite3,"SELECT InitSpatialMetadata()",NULL,NULL,NULL) == SQLITE_OK) * <li> - 'geometry_columns' * <li>-- SpatiaLite 2.0 'sqlite_stat1' until 2.2 * <li>- 'spatial_ref_sys' * <li>-- SpatiaLite 2.1 until present version * <li>-- SpatiaLite 2.3.1 has no field 'srs_wkt' or 'srtext' field,only 'proj4text' and * <li>-- SpatiaLite 2.4.0 first version with 'srs_wkt' and 'views_geometry_columns' * <li>-- SpatiaLite 3.1.0-RC2 last version with 'srs_wkt' * <li>-- SpatiaLite 4.0.0-RC1 : based on ISO SQL/MM standard 'srtext' * <li>-- views: vector_layers_statistics,vector_layers * <li>-- SpatiaLite 4.0.0 : introduced * </ul> * <p/> * <p>20131129: at the moment not possible to distinguish between 2.4.0 and 3.0.0 [no '2'] * * @param database Database connection to use * @param table name of table to read [if empty: list of tables in Database] * @return the {@link eu.geopaparazzi.spatialite.database.spatial.core.enums.SpatialiteVersion}. * @throws Exception if something goes wrong. */ public static SpatialiteVersion getSpatialiteDatabaseVersion(Database database, String table) throws Exception { // views: vector_layers_statistics,vector_layers // boolean b_vector_layers_statistics = false; // boolean b_vector_layers = false; // tables: geometry_columns,raster_columns /* * false = not a spatialite Database * true = a spatialite Database */ boolean b_geometry_columns = false; SpatialiteVersion versionFromSrswktPresence = SpatialiteVersion.SRS_WKT__NOTFOUND_PRE_2_4_0; boolean b_spatial_ref_sys = false; // boolean b_views_geometry_columns = false; SpatialiteVersion spatialiteVersion = SpatialiteVersion.NO_SPATIALITE; String s_sql_command; if (!table.equals("")) { // pragma table_info(geodb_geometry) s_sql_command = "pragma table_info(" + table + ")"; } else { s_sql_command = "SELECT name,type FROM sqlite_master WHERE ((type='table') OR (type='view')) ORDER BY type DESC,name ASC"; } String type; String name; Stmt this_stmt = database.prepare(s_sql_command); try { while (this_stmt.step()) { if (!table.equals("")) { // pragma table_info(berlin_strassen_geometry) name = this_stmt.column_string(1); // 'proj4text' must always exist - otherwise invalid if (name.equals("proj4text")) b_spatial_ref_sys = true; if (name.equals("srs_wkt")) versionFromSrswktPresence = SpatialiteVersion.SRS_WKT__2_4_0_to_3_1_0; if (name.equals("srtext")) versionFromSrswktPresence = SpatialiteVersion.SRS_WKT__FROM_4_0_0; } if (table.equals("")) { name = this_stmt.column_string(0); type = this_stmt.column_string(1); if (type.equals("table")) { // if (s_name.equals("geometry_columns")) { // b_geometry_columns = true; // } if (name.equals("spatial_ref_sys")) { b_spatial_ref_sys = true; } } // if (s_type.equals("view")) { // // SELECT name,type,sql FROM sqlite_master WHERE // // (type='view') // if (s_name.equals("vector_layers_statistics")) { // // An empty spatialite // // Database will not have // // this // b_vector_layers_statistics = true; // } // if (s_name.equals("vector_layers")) { // // An empty spatialite Database will // // not have this // b_vector_layers = true; // } // } } } } finally { if (this_stmt != null) { this_stmt.close(); } } if (table.equals("")) { if ((b_geometry_columns) && (b_spatial_ref_sys)) { if (b_spatial_ref_sys) { versionFromSrswktPresence = getSpatialiteDatabaseVersion(database, "spatial_ref_sys"); if (versionFromSrswktPresence == SpatialiteVersion.AFTER_4_0_0_RC1) { // Spatialite 4.0 spatialiteVersion = SpatialiteVersion.AFTER_4_0_0_RC1; } else { spatialiteVersion = versionFromSrswktPresence; } } } } else { if (b_spatial_ref_sys) { // 'proj4text' must always exist - otherwise invalid switch (versionFromSrswktPresence) { case SRS_WKT__NOTFOUND_PRE_2_4_0: spatialiteVersion = SpatialiteVersion.UNTIL_2_4_0; // no 'srs_wkt' or 'srtext' fields break; case SRS_WKT__2_4_0_to_3_1_0: spatialiteVersion = SpatialiteVersion.UNTIL_3_1_0_RC2; // 'srs_wkt' break; case SRS_WKT__FROM_4_0_0: spatialiteVersion = SpatialiteVersion.AFTER_4_0_0_RC1; // 'srtext' break; } } } return spatialiteVersion; } /** * Get the version of Rasterlite2 with cpu-type. * - used by: mapsforge.mapsdirmanager.sourcesview.SourcesTreeListActivity * -- to prevent RaterLite2 button being shown when empty * note: this is returning the version number of the first static lib being compilrd into it * - 2014-05-22: libpng 1.6.10 * * @param database the db to use. * @return the version of Spatialite. * @throws Exception if something goes wrong. */ public static String getRaster2Version(Database database) throws Exception { Stmt stmt = null; try { stmt = database.prepare("SELECT RL2_Version();"); if (stmt.step()) { String value = stmt.column_string(0); return value; } } catch (Exception e) { String localizedMessage = e.getLocalizedMessage(); if (!localizedMessage.contains("no such function: RL2_Version")) { // for now ignore this message, since there is no support for that throw e; } return ""; } finally { if (stmt != null) stmt.close(); } return ""; } /** * Get the properties of Spatialite. * <p/> * <br>- use the known 'SELECT Has..' functions * <br>- when HasIconv=0: no VirtualShapes,VirtualXL * * @param database the db to use. * @return the properties of Spatialite. * @throws Exception if something goes wrong. */ public static String getSpatialiteProperties(Database database) throws Exception { String s_value = "-"; Stmt stmt = database .prepare("SELECT HasIconv(),HasMathSql(),HasGeoCallbacks(),HasProj(),HasGeos(),HasGeosAdvanced(),HasGeosTrunk(),HasRtTopo(),HasLibXML2(),HasEpsg(),HasFreeXL();"); try { if (stmt.step()) { s_value = "HasIconv[" + stmt.column_int(0) + "],HasMathSql[" + stmt.column_int(1) + "],HasGeoCallbacks[" + stmt.column_int(2) + "],"; s_value += "HasProj[" + stmt.column_int(3) + "],HasGeos[" + stmt.column_int(4) + "],HasGeosAdvanced[" + stmt.column_int(5) + "],"; s_value += "HasGeosTrunk[" + stmt.column_int(6) + "],HasRtTopo[" + stmt.column_int(7) + "],HasLibXML2[" + stmt.column_int(8) + "],"; s_value += "HasEpsg[" + stmt.column_int(9) + "],HasFreeXL[" + stmt.column_int(10) + "]"; } } finally { stmt.close(); } try { // since spatialite 4.2.0-rc1 stmt = database.prepare("SELECT HasGeoPackage(),spatialite_target_cpu();"); if (stmt.step()) { if (stmt.column_int(0) == 1) SPL_Geopackage.hasGeoPackage = true; s_value += ",HasGeoPackage[" + stmt.column_int(0) + "],target_cpu[" + stmt.column_string(1) + "]"; } } finally { stmt.close(); } return s_value; } /** * Get the version of proj. * * @param database the db to use. * @return the version of proj. * @throws Exception if something goes wrong. */ public static String getProj4Version(Database database) throws Exception { Stmt stmt = database.prepare("SELECT proj4_version();"); try { if (stmt.step()) { return stmt.column_string(0); } } finally { stmt.close(); } return "-"; } /** * Get the version of geos. * * @param database the db to use. * @return the version of geos. * @throws Exception if something goes wrong. */ public static String getGeosVersion(Database database) throws Exception { Stmt stmt = database.prepare("SELECT geos_version();"); try { if (stmt.step()) { return stmt.column_string(0); } } finally { stmt.close(); } return "-"; } /** * Attemt to count Triggers for a specific Table. * returned the number of Triggers * - SpatialView read_only should be set to 0, if result is 0 * -- called when SpatialView read_only = 1 in getViewRowid * --- a SpatialView with out INSERT,UPDATE and DELETE tringgers is invalid * --- there is no way to check if these triggers really work correctly * --- this the reason why writable Views can be VERY dangerous * * @param database the db to use. * @param table_name the table of the db to use. * @param databaseType for Spatialite 3 and 4 specific Tasks * @return count of Triggers found * @throws Exception if something goes wrong. */ public static int spatialiteCountTriggers(Database database, String table_name, SpatialiteDatabaseType databaseType) throws Exception { int i_count = 0; if (table_name.equals("")) return i_count; String s_CountTriggers = "SELECT count(name) FROM sqlite_master WHERE (type = 'trigger' AND tbl_name= '" + table_name + "');"; Stmt statement = null; try { statement = database.prepare(s_CountTriggers); if (statement.step()) { i_count = statement.column_int(0); return i_count; } } catch (jsqlite.Exception e_stmt) { GPLog.error("DAOSPATIALIE", "spatialiteCountTriggers[" + databaseType + "] sql[" + s_CountTriggers + "] db[" + database.getFilename() + "]", e_stmt); } finally { if (statement != null) statement.close(); } return i_count; } }