NoteDao.java

package de.westnordost.streetcomplete.data.osmnotes;


import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;

import javax.inject.Inject;

import de.westnordost.streetcomplete.util.Serializer;
import de.westnordost.osmapi.map.data.OsmLatLon;
import de.westnordost.osmapi.notes.Note;

public class NoteDao
{
	private final SQLiteOpenHelper dbHelper;
	private final Serializer serializer;

	private final SQLiteStatement insert;

	@Inject public NoteDao(SQLiteOpenHelper dbHelper, Serializer serializer)
	{
		this.dbHelper = dbHelper;
		this.serializer = serializer;

		String sql = "INSERT OR REPLACE INTO " + NoteTable.NAME + " ("+
				NoteTable.Columns.ID+","+
				NoteTable.Columns.LATITUDE+","+
				NoteTable.Columns.LONGITUDE+","+
				NoteTable.Columns.STATUS+","+
				NoteTable.Columns.CREATED+","+
				NoteTable.Columns.CLOSED+","+
				NoteTable.Columns.COMMENTS+
				") values (?,?,?,?,?,?,?);";
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		insert = db.compileStatement(sql);
	}

	public void putAll(Collection<Note> notes)
	{
		SQLiteDatabase db = dbHelper.getWritableDatabase();

		db.beginTransaction();

		for(Note note : notes)
		{
			executeInsert(note);
		}

		db.setTransactionSuccessful();
		db.endTransaction();
	}

	public void put(Note note)
	{
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();
		executeInsert(note);
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	private void executeInsert(Note note)
	{
		insert.bindLong(1, note.id);
		insert.bindDouble(2, note.position.getLatitude());
		insert.bindDouble(3, note.position.getLongitude());
		insert.bindString(4, note.status.name());
		insert.bindLong(5, note.dateCreated.getTime());
		if(note.dateClosed != null)
		{
			insert.bindLong(6, note.dateClosed.getTime());
		}
		else
		{
			insert.bindNull(6);
		}
		insert.bindBlob(7, serializer.toBytes(note.comments));

		insert.executeInsert();
		insert.clearBindings();
	}

	public Note get(long id)
	{
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.query(NoteTable.NAME, null, NoteTable.Columns.ID + " = " + id,
				null, null, null, null, "1");

		try
		{
			if(!cursor.moveToFirst()) return null;
			return createObjectFrom(serializer, cursor);
		}
		finally
		{
			cursor.close();
		}
	}

	public boolean delete(long id)
	{
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		return db.delete(NoteTable.NAME, NoteTable.Columns.ID + " = " + id, null) == 1;
	}

	static Note createObjectFrom(Serializer serializer, Cursor cursor)
	{
		int colNoteId = cursor.getColumnIndexOrThrow(NoteTable.Columns.ID),
			colLat = cursor.getColumnIndexOrThrow(NoteTable.Columns.LATITUDE),
			colLon = cursor.getColumnIndexOrThrow(NoteTable.Columns.LONGITUDE),
			colStatus = cursor.getColumnIndexOrThrow(NoteTable.Columns.STATUS),
			colCreated = cursor.getColumnIndexOrThrow(NoteTable.Columns.CREATED),
			colClosed = cursor.getColumnIndexOrThrow(NoteTable.Columns.CLOSED),
			colComments = cursor.getColumnIndexOrThrow(NoteTable.Columns.COMMENTS);

		Note note = new Note();
		note.id = cursor.getLong(colNoteId);
		note.position = new OsmLatLon(cursor.getDouble(colLat), cursor.getDouble(colLon));
		note.dateCreated = new Date(cursor.getLong(colCreated));
		if(!cursor.isNull(colClosed))
		{
			note.dateClosed = new Date(cursor.getLong(colClosed));
		}
		note.status = Note.Status.valueOf(cursor.getString(colStatus));
		note.comments = serializer.toObject(cursor.getBlob(colComments), ArrayList.class);

		return note;
	}

	public int deleteUnreferenced()
	{
		SQLiteDatabase db = dbHelper.getWritableDatabase();

		String where = NoteTable.Columns.ID + " NOT IN ( " +
				"SELECT " + OsmNoteQuestTable.Columns.NOTE_ID + " FROM " + OsmNoteQuestTable.NAME +
				")";

		return db.delete(NoteTable.NAME, where, null);
	}
}