package de.westnordost.streetcomplete.data.osm.persist;
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.List;
import javax.inject.Inject;
import de.westnordost.streetcomplete.data.osm.ElementGeometry;
import de.westnordost.streetcomplete.util.Serializer;
import de.westnordost.osmapi.map.data.Element;
import de.westnordost.osmapi.map.data.LatLon;
import de.westnordost.osmapi.map.data.OsmLatLon;
public class ElementGeometryDao
{
private final SQLiteOpenHelper dbHelper;
private final Serializer serializer;
private final SQLiteStatement insert;
public static class Row
{
public Row(Element.Type elementType, long elementId, ElementGeometry geometry)
{
this.elementType = elementType;
this.elementId = elementId;
this.geometry = geometry;
}
public Element.Type elementType;
public long elementId;
public ElementGeometry geometry;
}
@Inject public ElementGeometryDao(SQLiteOpenHelper dbHelper, Serializer serializer)
{
this.dbHelper = dbHelper;
this.serializer = serializer;
String sql = "INSERT OR REPLACE INTO " + ElementGeometryTable.NAME + " ("+
ElementGeometryTable.Columns.ELEMENT_TYPE+","+
ElementGeometryTable.Columns.ELEMENT_ID+","+
ElementGeometryTable.Columns.GEOMETRY_POLYGONS+","+
ElementGeometryTable.Columns.GEOMETRY_POLYLINES+","+
ElementGeometryTable.Columns.LATITUDE+","+
ElementGeometryTable.Columns.LONGITUDE+
") values (?,?,?,?,?,?);";
SQLiteDatabase db = dbHelper.getWritableDatabase();
insert = db.compileStatement(sql);
}
public void putAll(Collection<Row> rows)
{
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.beginTransaction();
for(Row row : rows)
{
executeInsert(row.elementType, row.elementId, row.geometry);
}
db.setTransactionSuccessful();
db.endTransaction();
}
/** adds or updates (overwrites) an element geometry*/
public void put(Element.Type type, long id, ElementGeometry geometry)
{
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.beginTransaction();
executeInsert(type, id, geometry);
db.setTransactionSuccessful();
db.endTransaction();
}
private void executeInsert(Element.Type type, long id, ElementGeometry geometry)
{
insert.bindString(1, type.name());
insert.bindLong(2, id);
if (geometry.polygons != null)
insert.bindBlob(3, serializer.toBytes(geometry.polygons));
else
insert.bindNull(3);
if (geometry.polylines != null)
insert.bindBlob(4, serializer.toBytes(geometry.polylines));
else
insert.bindNull(4);
insert.bindDouble(5, geometry.center.getLatitude());
insert.bindDouble(6, geometry.center.getLongitude());
insert.executeInsert();
insert.clearBindings();
}
public ElementGeometry get(Element.Type type, long id)
{
SQLiteDatabase db = dbHelper.getReadableDatabase();
String where = ElementGeometryTable.Columns.ELEMENT_TYPE + " = ? AND " +
ElementGeometryTable.Columns.ELEMENT_ID + " = ?";
String[] args = {type.name(), String.valueOf(id)};
Cursor cursor = db.query(ElementGeometryTable.NAME, null, where, args,
null, null, null, "1");
try
{
if(!cursor.moveToFirst()) return null;
return createObjectFrom(serializer, cursor);
}
finally
{
cursor.close();
}
}
static ElementGeometry createObjectFrom(Serializer serializer, Cursor cursor)
{
int colGeometryPolygons = cursor.getColumnIndexOrThrow(ElementGeometryTable.Columns.GEOMETRY_POLYGONS),
colGeometryPolylines = cursor.getColumnIndexOrThrow(ElementGeometryTable.Columns.GEOMETRY_POLYLINES),
colCenterLat = cursor.getColumnIndexOrThrow(ElementGeometryTable.Columns.LATITUDE),
colCenterLon = cursor.getColumnIndexOrThrow(ElementGeometryTable.Columns.LONGITUDE);
List<List<LatLon>> polygons = null, polylines = null;
if(!cursor.isNull(colGeometryPolygons))
{
polygons = serializer.toObject(cursor.getBlob(colGeometryPolygons), ArrayList.class);
}
if(!cursor.isNull(colGeometryPolylines))
{
polylines = serializer.toObject(cursor.getBlob(colGeometryPolylines), ArrayList.class);
}
LatLon center = new OsmLatLon(cursor.getDouble(colCenterLat), cursor.getDouble(colCenterLon));
return new ElementGeometry(polylines, polygons, center);
}
/** Cleans up element geometry entries that belong to elements that are not referenced by any
* quest anymore. */
public int deleteUnreferenced()
{
SQLiteDatabase db = dbHelper.getWritableDatabase();
/* SQLite does not allow selecting multiple columns in a DELETE subquery. Using a workaround
* as described here:
* http://blog.programmingsolution.net/sql-server-2008/tsql/delete-rows-of-a-table-matching-multiple-columns-of-another-table/*/
String lumpTogether = "+'#'+";
String where =
"(" +
ElementGeometryTable.Columns.ELEMENT_TYPE + lumpTogether +
ElementGeometryTable.Columns.ELEMENT_ID +
") NOT IN ( SELECT " +
OsmQuestTable.Columns.ELEMENT_TYPE + lumpTogether +
OsmQuestTable.Columns.ELEMENT_ID + " FROM " + OsmQuestTable.NAME + ")";
return db.delete(ElementGeometryTable.NAME, where, null);
}
}