package eu.geopaparazzi.spatialite.database.spatial.core.resourcestorage; import java.io.File; import java.util.ArrayList; import java.util.List; import eu.geopaparazzi.library.database.GPLog; import eu.geopaparazzi.spatialite.database.spatial.SpatialiteSourcesManager; import eu.geopaparazzi.spatialite.database.spatial.core.databasehandlers.SpatialiteDatabaseHandler; import jsqlite.Callback; import jsqlite.Database; import jsqlite.Exception; import jsqlite.Stmt; /** * Stores resources (such as images) associated with a particular feature. * It can store different types of files (PDFs, videos, etc) using ExternalResourceType. * * The current implementation only stores the path to the resource on the * device filesystem, but it could be easily extended to store the resources * as a blobs instead. * * The resources (or resource paths) are stored on an auxiliar table within the * Spatialite database that contains the features. * * @author Cesar Martinez Izquierdo (www.scolab.es) */ public class ResourceStorage { // the auxiliary table used to store the resources public static final String AUX_TABLE_NAME = "geopap_resource"; // the table fields public static final String ID_FIELD = "id"; public static final String RESTABLE_FIELD = "restable"; public static final String RESTYPE_FIELD = "type"; public static final String RESNAME_FIELD = "resname"; public static final String ROWFK_FIELD = "rowidfk"; public static final String RESPATH_FIELD = "respath"; public static final String RESBLOB_FIELD = "resblob"; public static final String RESBLOBTHUMB_FIELD = "resthumb"; // the layer to which resources will be linked private String tableName; private Database database; protected ResourceStorage(String tableName, Database database) { this.tableName = tableName; this.database = database; } public String getTableName() { return this.tableName; } public String getDbFile() { return this.database.getFilename(); } public List<ExternalResource> getExternalResources(long rowIdFk, AbstractResource.ResourceType type) { StringBuffer buffer = new StringBuffer(); buffer.append("SELECT "); buffer.append(ID_FIELD).append(", ").append(RESPATH_FIELD).append(", ").append(RESNAME_FIELD); buffer.append(" FROM ").append(AUX_TABLE_NAME); buffer.append(" WHERE "); buffer.append(RESTABLE_FIELD).append("='").append(this.tableName).append("' AND "); buffer.append(ROWFK_FIELD).append("=").append(rowIdFk).append(" AND "); buffer.append(RESTYPE_FIELD).append("='").append(type.toString()).append("'"); String sqlCommand = buffer.toString(); Stmt statement = null; ArrayList<ExternalResource> result = new ArrayList<ExternalResource>(); try { statement = database.prepare(sqlCommand); while (statement.step()) { long id = statement.column_long(0); String path = statement.column_string(1); String name = statement.column_string(2); result.add(new ExternalResource(id, path, name, type)); } } catch (Exception e) { GPLog.error("DAO" + "SPATIALITE", "Error in checkResTableExists sql[" + sqlCommand + "] db[" + database.getFilename() + "]", e); } finally { if (statement != null) { try { statement.close(); } catch (Exception e) { } } } return result; } public ExternalResource getExternalResource(long id) { StringBuffer buffer = new StringBuffer(); buffer.append("SELECT "); buffer.append(RESPATH_FIELD).append(", ").append(RESNAME_FIELD).append(", ").append(RESTYPE_FIELD); buffer.append(" FROM ").append(AUX_TABLE_NAME); buffer.append(" WHERE "); buffer.append(RESTABLE_FIELD).append("='").append(this.tableName).append("' AND "); buffer.append(ID_FIELD).append("=").append(id); String sqlCommand = buffer.toString(); Stmt statement = null; try { statement = database.prepare(sqlCommand); if (statement.step()) { String path = statement.column_string(0); String name = statement.column_string(1); String typeStr = statement.column_string(2); AbstractResource.ResourceType type = AbstractResource.ResourceType.valueOf(typeStr); return new ExternalResource(id, path, name, type); } } catch (Exception e) { GPLog.error("DAO" + "SPATIALITE", "Error in checkResTableExists sql[" + sqlCommand + "] db[" + database.getFilename() + "]", e); } finally { if (statement != null) { try { statement.close(); } catch (Exception e) { } } } return null; } public List<BlobResource> getBlobResources(long rowIdFk, BlobResource.ResourceType type) { StringBuffer buffer = new StringBuffer(); buffer.append("SELECT "); buffer.append(ID_FIELD).append(", ").append(RESBLOB_FIELD).append(", ").append(RESNAME_FIELD); buffer.append(", ").append(RESBLOBTHUMB_FIELD); buffer.append(" FROM ").append(AUX_TABLE_NAME); buffer.append(" WHERE "); buffer.append(RESTABLE_FIELD).append("='").append(this.tableName).append("' AND "); buffer.append(ROWFK_FIELD).append("=").append(rowIdFk).append(" AND "); buffer.append(RESTYPE_FIELD).append("='").append(type.toString()).append("'"); String sqlCommand = buffer.toString(); Stmt statement = null; ArrayList<BlobResource> result = new ArrayList<BlobResource>(); try { statement = database.prepare(sqlCommand); while (statement.step()) { long id = statement.column_long(0); byte[] data = statement.column_bytes(1); String name = statement.column_string(2); byte[] thumbnail = statement.column_bytes(3); BlobResource res = new BlobResource(id, data, name, type); res.setThumbnail(thumbnail); result.add(res); } } catch (Exception e) { GPLog.error("DAO" + "SPATIALITE", "Error in checkResTableExists sql[" + sqlCommand + "] db[" + database.getFilename() + "]", e); } finally { if (statement != null) { try { statement.close(); } catch (Exception e) { } } } return result; } public List<BlobResource> getBlobThumbnails(long rowIdFk, BlobResource.ResourceType type) { StringBuffer buffer = new StringBuffer(); buffer.append("SELECT "); buffer.append(ID_FIELD).append(", ").append(RESBLOBTHUMB_FIELD).append(", ").append(RESNAME_FIELD); buffer.append(" FROM ").append(AUX_TABLE_NAME); buffer.append(" WHERE "); buffer.append(RESTABLE_FIELD).append("='").append(this.tableName).append("' AND "); buffer.append(ROWFK_FIELD).append("=").append(rowIdFk).append(" AND "); buffer.append(RESTYPE_FIELD).append("='").append(type.toString()).append("'"); String sqlCommand = buffer.toString(); Stmt statement = null; ArrayList<BlobResource> result = new ArrayList<BlobResource>(); try { statement = database.prepare(sqlCommand); while (statement.step()) { long id = statement.column_long(0); byte[] thumbnail = statement.column_bytes(1); String name = statement.column_string(2); BlobResource res = new BlobResource(id, null, name, type); res.setThumbnail(thumbnail); result.add(res); } } catch (Exception e) { GPLog.error("DAO" + "SPATIALITE", "Error in checkResTableExists sql[" + sqlCommand + "] db[" + database.getFilename() + "]", e); } finally { if (statement != null) { try { statement.close(); } catch (Exception e) { } } } return result; } public BlobResource getBlobResource(long id) { StringBuffer buffer = new StringBuffer(); buffer.append("SELECT "); buffer.append(RESBLOB_FIELD).append(", ").append(RESNAME_FIELD).append(", ").append(RESTYPE_FIELD); buffer.append(", ").append(RESBLOBTHUMB_FIELD); buffer.append(" FROM ").append(AUX_TABLE_NAME); buffer.append(" WHERE "); buffer.append(RESTABLE_FIELD).append("='").append(this.tableName).append("' AND "); buffer.append(ID_FIELD).append("=").append(id); String sqlCommand = buffer.toString(); Stmt statement = null; try { statement = database.prepare(sqlCommand); if (statement.step()) { byte[] data = statement.column_bytes(0); String name = statement.column_string(1); String typeStr = statement.column_string(2); AbstractResource.ResourceType type = AbstractResource.ResourceType.valueOf(typeStr); byte[] thumbnail = statement.column_bytes(3); BlobResource res = new BlobResource(id, data, name, type); res.setThumbnail(thumbnail); return res; } } catch (Exception e) { GPLog.error("DAO" + "SPATIALITE", "Error in checkResTableExists sql[" + sqlCommand + "] db[" + database.getFilename() + "]", e); } finally { if (statement != null) { try { statement.close(); } catch (Exception e) { } } } return null; } public void insertResource(long rowIdFk, ExternalResource res) { // INSERT INTO AUX_TABLE_NAME // (RESTABLE_FIELD. ROWFK_FIELD. RESTYPE_FIELD, RESNAME_FIELD, RESPATH_FIELD) VALUES () StringBuffer buffer = new StringBuffer(); buffer.append("INSERT INTO ").append(AUX_TABLE_NAME); buffer.append(" ("); buffer.append(RESTABLE_FIELD).append(", "); buffer.append(ROWFK_FIELD).append(", "); buffer.append(RESTYPE_FIELD).append(", "); buffer.append(RESNAME_FIELD).append(", "); buffer.append(RESPATH_FIELD); buffer.append(") VALUES ('"); buffer.append(this.tableName).append("', "); buffer.append(Long.toString(rowIdFk)).append(", '"); buffer.append(res.getType().toString()).append("', '"); buffer.append(res.getName()).append("', '"); buffer.append(res.getPath()); buffer.append("' )"); //String[] args = new String[] {this.tableName, Long.toString(rowId), type.toString(), res.getName(), res.getPath()}; String sqlCommand = buffer.toString(); Stmt stament = null; try { stament = database.prepare(sqlCommand); stament.step(); } catch (Exception e) { GPLog.error("DAO" + "SPATIALITE", "Error in checkResTableExists sql[" + sqlCommand + "] db[" + database.getFilename() + "]", e); } finally { if (stament!=null) { try { stament.close(); } catch (Exception e) {} } } } public void insertResource(long rowIdFk, BlobResource res) { StringBuffer buffer = new StringBuffer(); buffer.append("INSERT INTO ").append(AUX_TABLE_NAME); buffer.append(" ("); buffer.append(RESTABLE_FIELD).append(", "); buffer.append(ROWFK_FIELD).append(", "); buffer.append(RESTYPE_FIELD).append(", "); buffer.append(RESNAME_FIELD).append(", "); buffer.append(RESBLOB_FIELD).append(", "); buffer.append(RESBLOBTHUMB_FIELD); buffer.append(") VALUES (?, ?, ?, ?, ?, ?)"); //String[] args = new String[] {this.tableName, Long.toString(rowId), type.toString(), res.getName(), res.getPath()}; String sqlCommand = buffer.toString(); Stmt statement = null; try { statement = database.prepare(sqlCommand); statement.bind(1, this.tableName); statement.bind(2, rowIdFk); statement.bind(3, res.getType().toString()); statement.bind(4, res.getName()); statement.bind(5, res.getBlob()); statement.bind(6, res.getThumbnail()); statement.step(); } catch (Exception e) { GPLog.error("DAO" + "SPATIALITE", "Error in checkResTableExists sql[" + sqlCommand + "] db[" + database.getFilename() + "]", e); } finally { if (statement!=null) { try { statement.close(); } catch (Exception e) {} } } } public void deleteResource(long rowId) { StringBuffer buffer = new StringBuffer(); buffer.append("DELETE FROM ").append(AUX_TABLE_NAME); buffer.append(" WHERE "); buffer.append(ID_FIELD).append("=").append(rowId); String sqlCommand = buffer.toString(); Stmt statement = null; try { statement = database.prepare(sqlCommand); statement.step(); } catch (Exception e) { GPLog.error("DAO" + "SPATIALITE", "Error in checkResTableExists sql[" + sqlCommand + "] db[" + database.getFilename() + "]", e); } finally { if (statement!=null) { try { statement.close(); } catch (Exception e) {} } } } public void deleteResource(AbstractResource resource) { if (resource instanceof ExternalResource) { String imgPath = ((ExternalResource)resource).getPath(); File f = new File(imgPath); if (f.exists()) { f.delete(); } } deleteResource(resource.getId()); } public static ResourceStorage getStorage(String tableName, String databasePath) { SpatialiteDatabaseHandler spatialiteDatabaseHandler = SpatialiteSourcesManager.INSTANCE.getExistingDatabaseHandlerByPath(databasePath); Database database = spatialiteDatabaseHandler.getDatabase(); if (!checkResTableExists(database)) { addResTable(database); } return new ResourceStorage(tableName, database); } public static boolean checkResTableExists(Database database) { String sqlCommand = "SELECT name FROM sqlite_master WHERE type='table' AND name='"+ AUX_TABLE_NAME +"'"; Stmt statement = null; try { statement = database.prepare(sqlCommand); if (statement.step()) { return true; } } catch (Exception e) { GPLog.error("DAO" + "SPATIALITE", "Error in checkResTableExists sql[" + sqlCommand + "] db[" + database.getFilename() + "]", e); } finally { if (statement != null) { try { statement.close(); } catch (Exception e) { } } } return false; } public static void addResTable(Database database) { String sqlCommand = String.format("CREATE TABLE %s (%s integer PRIMARY KEY NOT NULL, %s text, %s integer, %s TEXT, %s TEXT, %s TEXT, %s BLOB, %s BLOB)", AUX_TABLE_NAME, ID_FIELD, RESTABLE_FIELD, ROWFK_FIELD, RESTYPE_FIELD, RESNAME_FIELD, RESPATH_FIELD, RESBLOB_FIELD, RESBLOBTHUMB_FIELD); try { database.exec(sqlCommand, new Callback() { @Override public void columns(String[] coldata) {} @Override public void types(String[] types) {} @Override public boolean newrow(String[] rowdata) { return false; } }); } catch (Exception e) { GPLog.error("DAO" + "SPATIALITE", "Error in checkResTableExists sql[" + sqlCommand + "] db[" + database.getFilename() + "]", e); } finally { } } }