/* * Geopaparazzi - Digital field mapping on Android based devices * Copyright (C) 2016 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.core.database; import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteStatement; import android.graphics.drawable.Drawable; import android.util.Log; import org.mapsforge.android.maps.overlay.OverlayItem; import org.mapsforge.core.model.GeoPoint; import java.io.IOException; import java.util.ArrayList; import java.util.Date; import java.util.List; import eu.geopaparazzi.library.database.GPLog; import eu.geopaparazzi.library.util.LibraryConstants; import eu.geopaparazzi.library.util.PositionUtilities; import eu.geopaparazzi.library.util.TimeUtilities; import eu.geopaparazzi.core.GeopaparazziApplication; import eu.geopaparazzi.core.database.objects.Note; import eu.geopaparazzi.core.database.objects.NoteOverlayItem; import static eu.geopaparazzi.core.database.TableDescriptions.NotesTableFields; import static eu.geopaparazzi.core.database.TableDescriptions.TABLE_NOTES; /** * @author Andrea Antonello (www.hydrologis.com) */ @SuppressWarnings("nls") public class DaoNotes { /** * Create the notes tables. * * @throws IOException if something goes wrong. */ public static void createTables() throws IOException { StringBuilder sB = new StringBuilder(); sB.append("CREATE TABLE "); sB.append(TABLE_NOTES); sB.append(" ("); sB.append(NotesTableFields.COLUMN_ID.getFieldName()); sB.append(" INTEGER PRIMARY KEY AUTOINCREMENT, "); sB.append(NotesTableFields.COLUMN_LON.getFieldName()).append(" REAL NOT NULL, "); sB.append(NotesTableFields.COLUMN_LAT.getFieldName()).append(" REAL NOT NULL,"); sB.append(NotesTableFields.COLUMN_ALTIM.getFieldName()).append(" REAL NOT NULL,"); sB.append(NotesTableFields.COLUMN_TS.getFieldName()).append(" DATE NOT NULL,"); sB.append(NotesTableFields.COLUMN_DESCRIPTION.getFieldName()).append(" TEXT, "); sB.append(NotesTableFields.COLUMN_TEXT.getFieldName()).append(" TEXT NOT NULL, "); sB.append(NotesTableFields.COLUMN_FORM.getFieldName()).append(" CLOB, "); sB.append(NotesTableFields.COLUMN_STYLE.getFieldName()).append(" TEXT,"); sB.append(NotesTableFields.COLUMN_ISDIRTY.getFieldName()).append(" INTEGER"); sB.append(");"); String CREATE_TABLE_NOTES = sB.toString(); sB = new StringBuilder(); sB.append("CREATE INDEX notes_ts_idx ON "); sB.append(TABLE_NOTES); sB.append(" ( "); sB.append(NotesTableFields.COLUMN_TS.getFieldName()); sB.append(" );"); String CREATE_INDEX_NOTES_TS = sB.toString(); sB = new StringBuilder(); sB.append("CREATE INDEX notes_x_by_y_idx ON "); sB.append(TABLE_NOTES); sB.append(" ( "); sB.append(NotesTableFields.COLUMN_LON.getFieldName()); sB.append(", "); sB.append(NotesTableFields.COLUMN_LAT.getFieldName()); sB.append(" );"); String CREATE_INDEX_NOTES_X_BY_Y = sB.toString(); sB = new StringBuilder(); sB.append("CREATE INDEX notes_isdirty_idx ON "); sB.append(TABLE_NOTES); sB.append(" ( "); sB.append(NotesTableFields.COLUMN_ISDIRTY.getFieldName()); sB.append(" );"); String CREATE_INDEX_NOTES_ISDIRTY = sB.toString(); SQLiteDatabase sqliteDatabase = GeopaparazziApplication.getInstance().getDatabase(); if (GPLog.LOG_HEAVY) Log.i("DAONOTES", "Create the notes table."); sqliteDatabase.beginTransaction(); try { sqliteDatabase.execSQL(CREATE_TABLE_NOTES); sqliteDatabase.execSQL(CREATE_INDEX_NOTES_TS); sqliteDatabase.execSQL(CREATE_INDEX_NOTES_X_BY_Y); sqliteDatabase.execSQL(CREATE_INDEX_NOTES_ISDIRTY); sqliteDatabase.setTransactionSuccessful(); } catch (Exception e) { Log.e("DAONOTES", e.getLocalizedMessage(), e); throw new IOException(e.getLocalizedMessage()); } finally { sqliteDatabase.endTransaction(); } } /** * Add a new note to the database. * * @param lon lon * @param lat lat * @param altim elevation * @param timestamp the UTC timestamp in millis. * @param text a text * @param description an optional description for the note. * @param form the optional json form. * @param style the optional style definition. * @return the inserted note id. * @throws IOException if something goes wrong. */ public static long addNote(double lon, double lat, double altim, long timestamp, String text, String description, String form, String style) throws IOException { if (description == null) { description = "note"; } SQLiteDatabase sqliteDatabase = GeopaparazziApplication.getInstance().getDatabase(); sqliteDatabase.beginTransaction(); try { long noteId = addNoteNoTransaction(lon, lat, altim, timestamp, text, description, form, style, sqliteDatabase); sqliteDatabase.setTransactionSuccessful(); return noteId; } catch (Exception e) { GPLog.error("DAONOTES", e.getLocalizedMessage(), e); throw new IOException(e.getLocalizedMessage()); } finally { sqliteDatabase.endTransaction(); } } /** * Add a note without transaction (for fast insert of many). * * @param lon lon * @param lat lat * @param altim elevation * @param timestamp the UTC timestamp in millis. * @param text the text * @param description a description. * @param form the json form or null * @param sqliteDatabase the database reference. * @return the inserted note id. */ public static long addNoteNoTransaction(double lon, double lat, double altim, long timestamp, String text, String description, String form, String style, SQLiteDatabase sqliteDatabase) { ContentValues values = new ContentValues(); values.put(NotesTableFields.COLUMN_LON.getFieldName(), lon); values.put(NotesTableFields.COLUMN_LAT.getFieldName(), lat); values.put(NotesTableFields.COLUMN_ALTIM.getFieldName(), altim); values.put(NotesTableFields.COLUMN_TS.getFieldName(), timestamp); if (description != null) values.put(NotesTableFields.COLUMN_DESCRIPTION.getFieldName(), description); values.put(NotesTableFields.COLUMN_TEXT.getFieldName(), text); if (form != null) values.put(NotesTableFields.COLUMN_FORM.getFieldName(), form); if (style != null) values.put(NotesTableFields.COLUMN_STYLE.getFieldName(), style); values.put(NotesTableFields.COLUMN_ISDIRTY.getFieldName(), 1); long noteId = sqliteDatabase.insertOrThrow(TABLE_NOTES, null, values); return noteId; } /** * Delete a note. * * @param id the note id. * @throws IOException if something goes wrong. */ public static void deleteNote(long id) throws IOException { SQLiteDatabase sqliteDatabase = GeopaparazziApplication.getInstance().getDatabase(); sqliteDatabase.beginTransaction(); try { // delete note String query = "delete from " + TABLE_NOTES + " where " + NotesTableFields.COLUMN_ID.getFieldName() + " = " + id; SQLiteStatement sqlUpdate = sqliteDatabase.compileStatement(query); sqlUpdate.execute(); sqliteDatabase.setTransactionSuccessful(); } catch (Exception e) { GPLog.error("DAONOTES", e.getLocalizedMessage(), e); throw new IOException(e.getLocalizedMessage()); } finally { sqliteDatabase.endTransaction(); } } /** * Delete all OSM type notes. * * @throws IOException if something goes wrong. */ public static void deleteOsmNotes() throws IOException { SQLiteDatabase sqliteDatabase = GeopaparazziApplication.getInstance().getDatabase(); sqliteDatabase.beginTransaction(); try { // delete note String query = "delete from " + TABLE_NOTES + " where " + NotesTableFields.COLUMN_DESCRIPTION.getFieldName() + " = " + LibraryConstants.OSM; SQLiteStatement sqlUpdate = sqliteDatabase.compileStatement(query); sqlUpdate.execute(); sqliteDatabase.setTransactionSuccessful(); } catch (Exception e) { GPLog.error("DAONOTES", e.getLocalizedMessage(), e); throw new IOException(e.getLocalizedMessage()); } finally { sqliteDatabase.endTransaction(); } } /** * Update the form of a note. * * @param id the note id. * @param noteText the note text. * @param jsonStr the form data. * @throws IOException if something goes wrong. */ public static void updateForm(long id, String noteText, String jsonStr) throws IOException { ContentValues updatedValues = new ContentValues(); updatedValues.put(NotesTableFields.COLUMN_FORM.getFieldName(), jsonStr); if (noteText != null && noteText.length() > 0) { updatedValues.put(NotesTableFields.COLUMN_TEXT.getFieldName(), noteText); } String where = NotesTableFields.COLUMN_ID.getFieldName() + "=" + id; String[] whereArgs = null; SQLiteDatabase sqliteDatabase = GeopaparazziApplication.getInstance().getDatabase(); sqliteDatabase.update(TABLE_NOTES, updatedValues, where, whereArgs); } /** * Get the collected notes from the database inside a given bound. * * @param nswe optional bounds as [n, s, w, e]. * @param onlyDirty get only dirty notes. * @return the list of notes inside the bounds. * @throws IOException if something goes wrong. */ public static List<Note> getNotesList(float[] nswe, boolean onlyDirty) throws IOException { SQLiteDatabase sqliteDatabase = GeopaparazziApplication.getInstance().getDatabase(); String query = "SELECT " +// NotesTableFields.COLUMN_ID.getFieldName() + ", " +// NotesTableFields.COLUMN_LON.getFieldName() + ", " +// NotesTableFields.COLUMN_LAT.getFieldName() + ", " +// NotesTableFields.COLUMN_ALTIM.getFieldName() + ", " +// NotesTableFields.COLUMN_TEXT.getFieldName() + ", " +// NotesTableFields.COLUMN_TS.getFieldName() + ", " +// NotesTableFields.COLUMN_DESCRIPTION.getFieldName() + ", " +// NotesTableFields.COLUMN_STYLE.getFieldName() + ", " +// NotesTableFields.COLUMN_FORM.getFieldName() +// ", " +// NotesTableFields.COLUMN_ISDIRTY.getFieldName() +// " FROM " + TABLE_NOTES; if (nswe != null) { query = query + " WHERE (lon BETWEEN XXX AND XXX) AND (lat BETWEEN XXX AND XXX)"; query = query.replaceFirst("XXX", String.valueOf(nswe[2])); query = query.replaceFirst("XXX", String.valueOf(nswe[3])); query = query.replaceFirst("XXX", String.valueOf(nswe[1])); query = query.replaceFirst("XXX", String.valueOf(nswe[0])); } if (onlyDirty) query = query + " AND " + NotesTableFields.COLUMN_ISDIRTY.getFieldName() + " = 1"; Cursor c = sqliteDatabase.rawQuery(query, null); List<Note> notes = new ArrayList<>(); c.moveToFirst(); while (!c.isAfterLast()) { long id = c.getLong(0); double lon = c.getDouble(1); double lat = c.getDouble(2); double altim = c.getDouble(3); String text = c.getString(4); long timestamp = c.getLong(5); String description = c.getString(6); String style = c.getString(7); String form = c.getString(8); int isDirty = c.getInt(9); Note note = new Note(id, text, description, timestamp, lon, lat, altim, form, isDirty, style); notes.add(note); c.moveToNext(); } c.close(); return notes; } public static Note getNoteById(long checkId) throws IOException { SQLiteDatabase sqliteDatabase = GeopaparazziApplication.getInstance().getDatabase(); String query = "SELECT " +// NotesTableFields.COLUMN_ID.getFieldName() + ", " +// NotesTableFields.COLUMN_LON.getFieldName() + ", " +// NotesTableFields.COLUMN_LAT.getFieldName() + ", " +// NotesTableFields.COLUMN_ALTIM.getFieldName() + ", " +// NotesTableFields.COLUMN_TEXT.getFieldName() + ", " +// NotesTableFields.COLUMN_TS.getFieldName() + ", " +// NotesTableFields.COLUMN_DESCRIPTION.getFieldName() + ", " +// NotesTableFields.COLUMN_STYLE.getFieldName() + ", " +// NotesTableFields.COLUMN_FORM.getFieldName() +// ", " +// NotesTableFields.COLUMN_ISDIRTY.getFieldName() +// " FROM " + TABLE_NOTES; query = query + " WHERE " + NotesTableFields.COLUMN_ID.getFieldName() + "=" + checkId; Cursor c = sqliteDatabase.rawQuery(query, null); try { c.moveToFirst(); if (!c.isAfterLast()) { long id = c.getLong(0); double lon = c.getDouble(1); double lat = c.getDouble(2); double altim = c.getDouble(3); String text = c.getString(4); long timestamp = c.getLong(5); String description = c.getString(6); String style = c.getString(7); String form = c.getString(8); int isDirty = c.getInt(9); Note note = new Note(id, text, description, timestamp, lon, lat, altim, form, isDirty, style); return note; } } finally { c.close(); } return null; } /** * Get the list of notes from the db as OverlayItems. * * @param marker the marker to use. * @return list of notes. * @throws IOException if something goes wrong. */ public static List<OverlayItem> getNoteOverlaysList(Drawable marker) throws IOException { SQLiteDatabase sqliteDatabase = GeopaparazziApplication.getInstance().getDatabase(); List<OverlayItem> notesList = new ArrayList<>(); String asColumnsToReturn[] = { // NotesTableFields.COLUMN_LON.getFieldName(), // NotesTableFields.COLUMN_LAT.getFieldName(), // NotesTableFields.COLUMN_TS.getFieldName(), // NotesTableFields.COLUMN_TEXT.getFieldName() // };// , String strSortOrder = "_id ASC"; Cursor c = sqliteDatabase.query(TABLE_NOTES, asColumnsToReturn, null, null, null, null, strSortOrder); c.moveToFirst(); while (!c.isAfterLast()) { double lon = c.getDouble(0); double lat = c.getDouble(1); if(!PositionUtilities.isValidCoordinateLL(lon, lat)){ continue; } long date = c.getLong(2); String text = c.getString(3); StringBuilder description = new StringBuilder(); description.append(text); description.append("\n\n"); description.append(TimeUtilities.INSTANCE.TIME_FORMATTER_LOCAL.format(new Date(date))); NoteOverlayItem item1 = new NoteOverlayItem(new GeoPoint(lat, lon), text, description.toString(), marker); notesList.add(item1); c.moveToNext(); } c.close(); return notesList; } }