/*
* GeoSolutions - MapstoreMobile - GeoSpatial Framework on Android based devices
* Copyright (C) 2014 GeoSolutions (www.geo-solutions.it)
*
* 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 it.geosolutions.geocollect.android.core.mission.utils;
import it.geosolutions.geocollect.android.app.BuildConfig;
import it.geosolutions.geocollect.android.core.mission.Mission;
import it.geosolutions.geocollect.android.core.mission.MissionFeature;
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Locale;
import jsqlite.Database;
import jsqlite.Exception;
import jsqlite.Stmt;
import android.content.Context;
import android.util.Log;
import com.vividsolutions.jts.io.ParseException;
import com.vividsolutions.jts.io.WKBReader;
import eu.geopaparazzi.library.util.ResourcesManager;
/**
* Utils for Spatialite database
* @author Lorenzo Pini (lorenzo.pini@geo-solutions.it)
*/
public class SpatialiteUtils {
public static String TAG = "SpatialiteUtils";
/**
* Return the string associated with the given column_type
* @param jsqliteType
* @return
*/
public static String getMapping(int columnType){
switch (columnType) {
case jsqlite.Constants.SQLITE_INTEGER: //1
return "integer";
case jsqlite.Constants.SQLITE_FLOAT: //2
return "float";
case jsqlite.Constants.SQLITE3_TEXT: //3
return "text";
case jsqlite.Constants.SQLITE_BLOB: //4
return "blob";
case jsqlite.Constants.SQLITE_NULL: //5
return "null";
case jsqlite.Constants.SQLITE_NUMERIC: //-1
return "numeric";
default:
return null;
}
}
/**
* Returns a valid SQLite Type from a given wfs type representation
* or null if given string is not a valid type
* Note that the string "null" is a valid SQLite type
*/
public static String getSQLiteTypeFromString(String inputTypeString){
if(inputTypeString == null){
return null;
}
String toCheck = inputTypeString.toLowerCase(Locale.US);
if( toCheck.equals("string")
|| toCheck.equals("text")
|| toCheck.equals("varchar")
|| toCheck.equals("person")
|| toCheck.equals("date")
|| toCheck.equals("datetime")
){
return "text";
}
if( toCheck.equals("double")
|| toCheck.equals("real")
|| toCheck.equals("float")
|| toCheck.equals("decimal")
){
return "double";
}
if( toCheck.equals("integer")
|| toCheck.equals("int")
){
return "integer";
}
if( toCheck.equals("blob")
){
return "blob";
}
if( toCheck.equals("null")
){
return "null";
}
if( toCheck.equals("numeric")
|| toCheck.equals("long")
){
return "numeric";
}
// Spatialite custom types
if( toCheck.equals("point")
){
return "point";
}
// unrecognized type
return null;
}
/**
* Open the given database and returns a reference to it or null if invalid context or databasePath are passed
* If given filePath does not exists, it will be created
*/
public static Database importSpatialiteDBFromAssets(Context c, String sdcardFolder, String databaseFile){
if( c == null
|| databaseFile == null){
Log.v(TAG, "Cannot open Database, invalid parameters.");
return null;
}
Database spatialiteDatabase = null;
try {
final InputStream input = c.getAssets().open(databaseFile);
File sdcardDir = ResourcesManager.getInstance(c).getSdcardDir();
File spatialDbFile = new File(sdcardDir, sdcardFolder + "/" + databaseFile);
final File geocollectDir = new File(sdcardDir, sdcardFolder);
if(!geocollectDir.exists()){
if(!geocollectDir.mkdir()){
throw new RuntimeException();
}
}
OutputStream myOutput = new FileOutputStream(spatialDbFile);
//transfer bytes from the inputfile to the outputfile
byte[] buffer = new byte[1024];
int length;
while ((length = input.read(buffer)) > 0){
myOutput.write(buffer, 0, length);
}
myOutput.flush();
myOutput.close();
input.close();
if (!spatialDbFile.getParentFile().exists()) {
throw new RuntimeException();
}
spatialiteDatabase = new jsqlite.Database();
spatialiteDatabase.open(spatialDbFile.getAbsolutePath(), jsqlite.Constants.SQLITE_OPEN_READWRITE
| jsqlite.Constants.SQLITE_OPEN_CREATE);
//Log.v("MISSION_DETAIL", SpatialiteUtils.queryVersions(spatialiteDatabase));
Log.v(TAG, spatialiteDatabase.dbversion());
if(BuildConfig.DEBUG){
Log.i(TAG, "Database imported from assets");
}
} catch (Exception e) {
Log.v(TAG, Log.getStackTraceString(e));
} catch (java.lang.Exception e) {
// Cannot find Android ResourceManager
Log.e(TAG, Log.getStackTraceString(e));
}
return spatialiteDatabase;
}
/**
* Open the given database and returns a reference to it or null if invalid context or databasePath are passed
* If given filePath does not exists, it will be created
*/
public static Database openSpatialiteDB(Context c, String databasePath){
if( c == null
|| databasePath == null
|| databasePath.isEmpty()){
Log.v(TAG, "Cannot open Database, invalid parameters.");
return null;
}
Database spatialiteDatabase = null;
try {
File sdcardDir = ResourcesManager.getInstance(c).getSdcardDir();
File spatialDbFile = new File(sdcardDir, databasePath);
if (!spatialDbFile.getParentFile().exists()) {
return importSpatialiteDBFromAssets(c, "geocollect", "genova.sqlite");
}
spatialiteDatabase = new jsqlite.Database();
spatialiteDatabase.open(spatialDbFile.getAbsolutePath(), jsqlite.Constants.SQLITE_OPEN_READWRITE
| jsqlite.Constants.SQLITE_OPEN_CREATE);
//Log.v("MISSION_DETAIL", SpatialiteUtils.queryVersions(spatialiteDatabase));
Log.v(TAG, spatialiteDatabase.dbversion());
} catch (Exception e) {
Log.v(TAG, Log.getStackTraceString(e));
} catch (java.lang.Exception e) {
// Cannot find Android ResourceManager
Log.e(TAG, Log.getStackTraceString(e));
}
return spatialiteDatabase;
}
/**
* Default getVersions method
* Based on Spatialite Examples by Alessandro Furieri (a.furieri@lqt.it)
* @param db
* @return
* @throws Exception
*/
public static String queryVersions(jsqlite.Database db) throws Exception {
StringBuilder sb = new StringBuilder();
sb.append("Check versions...\n");
Stmt stmt01 = db.prepare("SELECT spatialite_version();");
if (stmt01.step()) {
sb.append("\t").append("SPATIALITE_VERSION: " + stmt01.column_string(0));
sb.append("\n");
}
stmt01 = db.prepare("SELECT proj4_version();");
if (stmt01.step()) {
sb.append("\t").append("PROJ4_VERSION: " + stmt01.column_string(0));
sb.append("\n");
}
stmt01 = db.prepare("SELECT geos_version();");
if (stmt01.step()) {
sb.append("\t").append("GEOS_VERSION: " + stmt01.column_string(0));
sb.append("\n");
}
stmt01.close();
sb.append("Done...\n");
return sb.toString();
}
/**
* Check if the specified table exists in the specified database, if not exists, create it.
*
* TODO:
* - Separate check and creation methods
* - Make table creation parametric based on Template
*
* @param db
* @param tableName
* @return
public static boolean checkOrCreateTable(jsqlite.Database db, String tableName){
if (db != null){
String query = "SELECT name FROM sqlite_master WHERE type='table' AND name='"+tableName+"'";
boolean found = false;
try {
Stmt stmt = db.prepare(query);
if( stmt.step() ) {
String nomeStr = stmt.column_string(0);
found = true;
Log.v(TAG, "Found table: "+nomeStr);
}
stmt.close();
} catch (Exception e) {
Log.e(TAG, Log.getStackTraceString(e));
return false;
}
if(found){
return true;
}else{
// Table not found creating
Log.v(TAG, "Table "+tableName+" not found, creating..");
// TODO: refactor this
if(tableName.equalsIgnoreCase("punti_accumulo_data")){
String create_stmt = "CREATE TABLE 'punti_accumulo_data' (" +
"'PK_UID' INTEGER PRIMARY KEY AUTOINCREMENT, " +
"'ORIGIN_ID' TEXT, " +
"'DATA_SCHEDA' TEXT, " +
"'DATA_AGG' TEXT, " +
"'NOME_RILEVATORE' TEXT, " +
"'COGNOME_RILEVATORE' TEXT, " +
"'ENTE_RILEVATORE' TEXT, " +
"'TIPOLOGIA_SEGNALAZIONE' TEXT, " +
"'PROVENIENZA_SEGNALAZIONE' TEXT, " +
"'CODICE_DISCARICA' TEXT, " +
"'TIPOLOGIA_RIFIUTO' TEXT, " +
"'COMUNE' TEXT, " +
"'LOCALITA' TEXT, " +
"'INDIRIZZO' TEXT, " +
"'CIVICO' TEXT, " +
"'PRESA_IN_CARICO' TEXT, " +
"'EMAIL' INTEGER, " +
"'RIMOZIONE' TEXT, " +
"'SEQUESTRO' TEXT, " +
"'RESPONSABILE_ABBANDONO' TEXT, " +
"'QUANTITA_PRESUNTA' FLOAT);";
String add_geom_stmt = "SELECT AddGeometryColumn('punti_accumulo_data', 'GEOMETRY', 4326, 'POINT', 'XY');";
String create_idx_stmt = "SELECT CreateSpatialIndex('punti_accumulo_data', 'GEOMETRY');";
// TODO: check if all statements are complete
try {
Stmt stmt01 = db.prepare(create_stmt);
if (stmt01.step()) {
//TODO This will never happen, CREATE statements return empty results
Log.v(TAG, "Table Created");
}
// TODO: Check if created, fail otherwise
stmt01 = db.prepare(add_geom_stmt);
if (stmt01.step()) {
Log.v(TAG, "Geometry Column Added "+stmt01.column_string(0));
}
stmt01 = db.prepare(create_idx_stmt);
if (stmt01.step()) {
Log.v(TAG, "Index Created");
}
stmt01.close();
} catch (jsqlite.Exception e) {
Log.e(TAG, Log.getStackTraceString(e));
}
return true;
}
}
}else{
Log.w(TAG, "No valid database received, aborting..");
}
return false;
}
*/
/**
* Initializes the table if not exists
* @param db
* @param tableName
* @return
public static boolean initializeTable(jsqlite.Database db, String tableName){
return false;
}
*/
/**
* Return an Hashmap of Strings, containing the list of the columns of the given table name in the given database.
* Returns null if wrong parameters are given or database is closed.
* @param db
* @param tableName
* @return HashMap<String, String>
*/
public static HashMap<String, String> getPropertiesFields (Database db, String tableName){
if(db == null || tableName == null){
Log.w(TAG, "Wrong parameters for getPropertiesFields()");
return null;
}
if(db.dbversion().equals("unknown")){
Log.w(TAG, "Database is closed, cannot access to it.");
return null;
}
// get table columns
// If table does not exists, an empty result set is returned.
String table_info_query = "PRAGMA table_info('"+tableName+"');";
int nameColumn = -1;
int typeColumn = -1;
String columnName, typeName;
HashMap<String, String> dbFieldValues = new HashMap<String, String>();
try {
Stmt stmt = db.prepare(table_info_query);
while( stmt.step() ) {
if(nameColumn<0 || typeColumn<0){
// I have to retrieve the position of the metadata fields
for(int i = 0; i<stmt.column_count(); i++){
Log.v(TAG, stmt.column_name(i));
if(stmt.column_name(i).equalsIgnoreCase("name")){
nameColumn = i;
}
if(stmt.column_name(i).equalsIgnoreCase("type")){
typeColumn = i;
}
}
}
columnName = stmt.column_string(nameColumn);
typeName = stmt.column_string(typeColumn);
if(columnName != null){
// This is a debug check
if(typeName == null){
Log.d(TAG, "Column name '"+columnName+"' has a NULL typeName");
}
dbFieldValues.put(columnName, typeName);
}else{
// This should never happen
Log.d(TAG, "Found a NULL column name, this is strange.");
}
}
stmt.close();
} catch (Exception e) {
Log.e(TAG, Log.getStackTraceString(e));
}
return dbFieldValues;
}
/**
* Populate attributes and geometry fields of a feature from the stmt
* reading attributes and setting geometry
* @param wkbReader
* @param stmt
* @param f
* @throws Exception
*/
public static void populateFeatureFromStmt(WKBReader wkbReader, Stmt stmt,
MissionFeature f) throws Exception {
String columnName;
int colcount = stmt.column_count();
for(int colpos = 0; colpos < colcount; colpos++){
columnName = stmt.column_name(colpos);
if(columnName != null){
if(columnName.equalsIgnoreCase(Mission.PK_UID_STRING)||columnName.equalsIgnoreCase(Mission.ORIGIN_ID_STRING)){
// The output Feature ID should not be the same as the input Feature one
f.id = stmt.column_string(colpos);
}else if(columnName.equalsIgnoreCase(Mission.GEOMETRY_FIELD_STRING)){
// At the moment, only Point is supported
// Here, the "GEOMETRY" column contains the result of
// ST_AsBinary(CastToXY("GEOMETRY"))
byte[] geomBytes = stmt.column_bytes(colpos);
if(geomBytes != null){
try {
f.geometry = wkbReader.read(geomBytes);
} catch (ParseException e) {
Log.e(TAG,"Error reading geometry");
//throw new Exception(e.getMessage());
}
}
}else{
if(f.properties == null){
f.properties = new HashMap<String, Object>();
}
f.properties.put(columnName, stmt.column_string(colpos));
}
}else{
// This should never happen
Log.d(TAG, "Found a NULL column name, this is strange.");
}
}
}
/**
* Escape a string for database input
*/
public static String escape(String s){
if(s==null)
return s;
s = s.replaceAll("'", "''");
return s;
}
}