/*
* Copyright 2016 Google Inc. All Rights Reserved.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.google.android.apps.forscience.whistlepunk.metadata;
import android.content.ContentResolver;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.net.Uri;
import android.provider.BaseColumns;
import androidx.annotation.NonNull;
import androidx.annotation.Nullable;
import androidx.annotation.VisibleForTesting;
import android.text.TextUtils;
import android.util.Log;
import com.google.android.apps.forscience.whistlepunk.AppSingleton;
import com.google.android.apps.forscience.whistlepunk.Clock;
import com.google.android.apps.forscience.whistlepunk.CurrentTimeClock;
import com.google.android.apps.forscience.whistlepunk.LabelValuePojo;
import com.google.android.apps.forscience.whistlepunk.PictureUtils;
import com.google.android.apps.forscience.whistlepunk.R;
import com.google.android.apps.forscience.whistlepunk.RecorderController;
import com.google.android.apps.forscience.whistlepunk.SensorProvider;
import com.google.android.apps.forscience.whistlepunk.StatsAccumulator;
import com.google.android.apps.forscience.whistlepunk.WhistlePunkApplication;
import com.google.android.apps.forscience.whistlepunk.accounts.AppAccount;
import com.google.android.apps.forscience.whistlepunk.analytics.TrackerConstants;
import com.google.android.apps.forscience.whistlepunk.analytics.UsageTracker;
import com.google.android.apps.forscience.whistlepunk.api.scalarinput.InputDeviceSpec;
import com.google.android.apps.forscience.whistlepunk.data.GoosciSensorLayout;
import com.google.android.apps.forscience.whistlepunk.devicemanager.ConnectableSensor;
import com.google.android.apps.forscience.whistlepunk.filemetadata.DeviceSpecPojo;
import com.google.android.apps.forscience.whistlepunk.filemetadata.Experiment;
import com.google.android.apps.forscience.whistlepunk.filemetadata.ExperimentLibraryManager;
import com.google.android.apps.forscience.whistlepunk.filemetadata.ExperimentOverviewPojo;
import com.google.android.apps.forscience.whistlepunk.filemetadata.FileMetadataManager;
import com.google.android.apps.forscience.whistlepunk.filemetadata.FileMetadataUtil;
import com.google.android.apps.forscience.whistlepunk.filemetadata.Label;
import com.google.android.apps.forscience.whistlepunk.filemetadata.LabelValue;
import com.google.android.apps.forscience.whistlepunk.filemetadata.LocalSyncManager;
import com.google.android.apps.forscience.whistlepunk.filemetadata.PictureLabelValue;
import com.google.android.apps.forscience.whistlepunk.filemetadata.SensorLayoutPojo;
import com.google.android.apps.forscience.whistlepunk.filemetadata.SensorTrigger;
import com.google.android.apps.forscience.whistlepunk.filemetadata.SensorTriggerLabelValue;
import com.google.android.apps.forscience.whistlepunk.filemetadata.TextLabelValue;
import com.google.android.apps.forscience.whistlepunk.filemetadata.Trial;
import com.google.android.apps.forscience.whistlepunk.filemetadata.TrialStats;
import com.google.android.apps.forscience.whistlepunk.metadata.GoosciCaption.Caption;
import com.google.android.apps.forscience.whistlepunk.metadata.GoosciLabel.Label.ValueType;
import com.google.android.apps.forscience.whistlepunk.metadata.GoosciTrial.Range;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.Lists;
import com.google.common.io.Files;
import com.google.protobuf.InvalidProtocolBufferException;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.channels.FileChannel;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.Set;
/** An implementation of the {@link MetaDataManager} which uses a simple database. */
public class SimpleMetaDataManager implements MetaDataManager {
private static final int STABLE_EXPERIMENT_ID_LENGTH = 12;
private static final int STABLE_PROJECT_ID_LENGTH = 6;
private static final String TAG = "SimpleMetaDataManager";
private static final String TEXT_LABEL_TAG = "text";
private static final String PICTURE_LABEL_TAG = "picture";
private static final String SENSOR_TRIGGER_LABEL_TAG = "sensorTriggerLabel";
private static final String UNKNOWN_LABEL_TAG = "label";
private static final String DEFAULT_PROJECT_ID = "defaultProjectId";
private DatabaseHelper dbHelper;
private Context context;
private AppAccount appAccount;
private Clock clock;
private final Object lock = new Object();
private FileMetadataManager fileMetadataManager;
private final ExperimentLibraryManager experimentLibraryManager;
private final LocalSyncManager localSyncManager;
private boolean recoverAlreadyAttempted;
public void close() {
dbHelper.close();
getFileMetadataManager().close();
}
/**
* List of table names. NOTE: when adding a new table, make sure to delete the metadata in the
* appropriate delete calls: {@link #deleteExperiment(Experiment)}, {@link
* #deleteDatabaseLabel(SQLiteDatabase, Label)}, {@link #deleteDatabaseTrial(String)}, {@link
* #deleteDatabaseSensorTrigger(SQLiteDatabase, SensorTrigger)}, etc.
*/
interface Tables {
String PROJECTS = "projects";
String EXPERIMENTS = "experiments";
String LABELS = "labels";
String EXTERNAL_SENSORS = "sensors";
String EXPERIMENT_SENSORS = "experiment_sensors";
String RUN_STATS = "run_stats";
String RUNS = "runs";
String RUN_SENSORS = "run_sensors";
String EXPERIMENT_SENSOR_LAYOUT = "experiment_sensor_layout";
String SENSOR_TRIGGERS = "sensor_triggers";
String MY_DEVICES = "my_devices";
}
public SimpleMetaDataManager(Context context, AppAccount appAccount) {
this(context, appAccount, null /* default filename */, new CurrentTimeClock());
}
@VisibleForTesting
SimpleMetaDataManager(Context context, AppAccount appAccount, String filename, Clock clock) {
this.context = context;
this.appAccount = appAccount;
this.clock = clock;
fileMetadataManager = new FileMetadataManager(context, appAccount, clock);
localSyncManager = AppSingleton.getInstance(context).getLocalSyncManager(appAccount);
experimentLibraryManager =
AppSingleton.getInstance(context).getExperimentLibraryManager(appAccount);
dbHelper =
new DatabaseHelper(
context,
appAccount,
filename,
new DatabaseHelper.MetadataDatabaseUpgradeCallback() {
@Override
public void onMigrateProjectData(SQLiteDatabase db) {
migrateProjectData(db);
}
@Override
public void onMigrateExperimentsToFiles(SQLiteDatabase db) {
migrateExperimentsToFiles(db);
}
@Override
public void onMigrateMyDevicesToProto(SQLiteDatabase db) {
migrateMyDevicesToProto(db);
}
});
}
private FileMetadataManager getFileMetadataManager() {
synchronized (lock) {
// Call getWritableDatabase to force upgrade if needed. We don't need the returned db.
dbHelper.getWritableDatabase();
// The first time we are going to use the fileMetadataManager, and while we are holding the
// lock, try to recover experiments lost due to b/129409993.
if (!recoverAlreadyAttempted) {
recoverAlreadyAttempted = true;
try {
fileMetadataManager.recoverLostExperimentsIfNeeded(context.getApplicationContext());
} catch (Exception e) {
String labelFromStackTrace = TrackerConstants.createLabelFromStackTrace(e);
UsageTracker usageTracker = WhistlePunkApplication.getUsageTracker(context);
usageTracker.trackEvent(
TrackerConstants.CATEGORY_STORAGE,
TrackerConstants.ACTION_RECOVERY_FAILED,
labelFromStackTrace,
0);
usageTracker.trackEvent(
TrackerConstants.CATEGORY_FAILURE,
TrackerConstants.ACTION_RECOVERY_FAILED,
labelFromStackTrace,
0);
}
}
return fileMetadataManager;
}
}
@VisibleForTesting
void migrateExperimentsToFiles() {
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
migrateExperimentsToFiles(db);
}
}
private void migrateExperimentsToFiles(SQLiteDatabase db) {
// For each experiment, migrate it over and delete it from the database.
List<String> experimentIds = getAllExperimentIds(db);
// Clean up if a previous migration was not successful / complete.
fileMetadataManager.deleteAll(experimentIds);
int colorIndex = 0;
int colorCount = context.getResources().getIntArray(R.array.experiment_colors_array).length;
for (String experimentId : experimentIds) {
Experiment experiment = getDatabaseExperimentById(db, experimentId, context, true);
// Assign a color. This is based on the order that experiments are retrieved from
// the database so it might not be in any particular order.
experiment.getExperimentOverview().setColorIndex(colorIndex);
colorIndex = (colorIndex + 1) % colorCount;
// This prepares the file system for the new experiment.
fileMetadataManager.addExperiment(experiment);
// Remove experiment description, turn it into a text note.
if (!TextUtils.isEmpty(experiment.getDescription())) {
GoosciTextLabelValue.TextLabelValue descriptionValue =
GoosciTextLabelValue.TextLabelValue.newBuilder()
.setText(experiment.getDescription())
.build();
Label descriptionLabel =
Label.newLabelWithValue(
experiment.getCreationTimeMs() - 500, ValueType.TEXT, descriptionValue, null);
experiment.setDescription("");
experiment.addLabel(experiment, descriptionLabel);
}
// Migrate assets
for (int i = 0; i < experiment.getLabelCount(); i++) {
Label label = experiment.getLabels().get(i);
updateLabelPictureAssets(experiment, label);
}
for (Trial trial : experiment.getTrials()) {
// TODO: Also migrate any sensor specific assets needed to view this trial.
for (Label trialLabel : trial.getLabels()) {
updateLabelPictureAssets(experiment, trialLabel);
}
}
// Now that all the labels have their assets in the right place, we can save them.
fileMetadataManager.updateExperiment(experiment, true);
fileMetadataManager.saveImmediately();
deleteDatabaseExperiment(db, experiment, context);
}
}
@VisibleForTesting
public void migrateMyDevices() {
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
migrateMyDevicesToProto(db);
}
}
private void migrateMyDevicesToProto(SQLiteDatabase db) {
List<InputDeviceSpec> devices = databaseGetMyDevices(db);
for (InputDeviceSpec device : devices) {
String sensorId = getExternalSensorId(device, db);
if (sensorId != null) {
databaseRemoveMyDevice(sensorId, db);
fileMetadataManager.addMyDevice(DeviceSpecPojo.fromProto(device.asDeviceSpec()));
}
}
}
/** Migrates label picture assets, updating the Experiment Overview image if it is not yet set. */
private void updateLabelPictureAssets(Experiment experiment, Label label) {
if (migratePictureAssetsIfNeeded(experiment.getExperimentId(), label)) {
experiment.updateLabelWithoutSorting(experiment, label);
if (TextUtils.isEmpty(experiment.getExperimentOverview().getImagePath())) {
String path = label.getPictureLabelValue().getFilePath();
if (!TextUtils.isEmpty(path)) {
experiment.setImagePath(path);
}
}
}
}
/**
* Tries to migrate a label's picture assets if it is a picture label, returns true if the label
* was modified.
*
* @param label The label whose pictures should be migrated, if needed
* @return true if the label was modified
*/
private boolean migratePictureAssetsIfNeeded(String experimentId, Label label) {
if (label.getType() != ValueType.PICTURE) {
return false;
}
GoosciPictureLabelValue.PictureLabelValue.Builder pictureLabelValue =
label.getPictureLabelValue().toBuilder();
File oldFile = new File(pictureLabelValue.getFilePath());
if (!oldFile.exists()) {
// It has been deleted by the user, don't try to copy it.
pictureLabelValue.clearFilePath();
label.setLabelProtoData(pictureLabelValue.build());
return true;
}
boolean success = false;
try {
File newFile =
PictureUtils.createImageFile(context, appAccount, experimentId, label.getLabelId());
pictureLabelValue.setFilePath(
FileMetadataUtil.getInstance().getRelativePathInExperiment(experimentId, newFile));
try (FileChannel input = new FileInputStream(oldFile).getChannel();
FileChannel output = new FileOutputStream(newFile).getChannel()) {
// Copy the file contents over to internal storage from external.
input.transferTo(0, input.size(), output);
success = true;
}
} catch (IOException e) {
if (Log.isLoggable(TAG, Log.DEBUG)) {
Log.d(TAG, e.getMessage());
}
}
if (success) {
// Even though we are successful, we will not delete the old photo on upgrade.
// Upgrade is a copy, not a move.
label.setLabelProtoData(pictureLabelValue.build());
return true;
} else {
return false;
}
}
private List<String> getAllExperimentIds(SQLiteDatabase db) {
List<String> experimentIds = new ArrayList<>();
Cursor cursor = null;
try {
cursor =
db.query(
Tables.EXPERIMENTS,
new String[] {ExperimentColumns.EXPERIMENT_ID},
null,
null,
null,
null,
null);
while (cursor.moveToNext()) {
experimentIds.add(cursor.getString(0));
}
} finally {
if (cursor != null) {
cursor.close();
}
}
return experimentIds;
}
@VisibleForTesting
void migrateProjectData() {
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
migrateProjectData(db);
}
}
private void migrateProjectData(SQLiteDatabase db) {
// Get every project and migrate its data to its experiments.
List<Project> projects = getDatabaseProjects(db, true);
for (Project project : projects) {
List<Experiment> experiments = getAllDatabaseExperimentsForProject(db, project);
for (Experiment experiment : experiments) {
// Migrate project data
if (!TextUtils.isEmpty(project.getDescription())) {
// Create a label with the description at the start of the experiment.
// Because projects do not track their creation time, use the experiment
// creation time instead.
addDatabaseLabel(
db,
experiment.getExperimentId(),
RecorderController.NOT_RECORDING_RUN_ID,
Label.newLabel(experiment.getCreationTimeMs() - 2000, ValueType.TEXT),
TextLabelValue.fromText(project.getDescription()));
}
if (!TextUtils.isEmpty(project.getCoverPhoto())) {
// Create a label with the picture at the start of the experiment.
// TODO: Copy the project photo for each note. This helps us upgrade later.
addDatabaseLabel(
db,
experiment.getExperimentId(),
RecorderController.NOT_RECORDING_RUN_ID,
Label.newLabel(experiment.getCreationTimeMs() - 1000, ValueType.PICTURE),
PictureLabelValue.fromPicture(project.getCoverPhoto(), ""));
}
boolean needsWrite = false;
if (project.isArchived()) {
// If the project is archived, the experiment should be archived.
experiment.setArchived(context, appAccount, true);
needsWrite = true;
}
if (!TextUtils.isEmpty(project.getTitle())) {
// Experiment title prefixed with Project title, unless project title is not set
experiment.setTitle(
String.format(
context.getResources().getString(R.string.project_experiment_title),
project.getTitle(),
experiment.getDisplayTitle(context)));
needsWrite = true;
}
if (needsWrite) {
updateDatabaseExperiment(db, experiment);
}
}
deleteProjectFromDb(db, project);
}
}
/**
* This function is only used as part of the database upgrade which deletes projects, so these
* experiments returned do not contain their trials, labels, triggers, sensors, etc.
*/
private static List<Experiment> getAllDatabaseExperimentsForProject(
SQLiteDatabase db, Project project) {
List<Experiment> experiments = new ArrayList<>();
String selection = ExperimentColumns.PROJECT_ID + "=?";
String[] selectionArgs = new String[] {project.getProjectId()};
Cursor cursor = null;
try {
cursor =
db.query(
Tables.EXPERIMENTS,
ExperimentColumns.GET_COLUMNS,
selection,
selectionArgs,
null,
null,
ExperimentColumns.LAST_USED_TIME + " DESC, " + BaseColumns._ID + " DESC");
while (cursor.moveToNext()) {
experiments.add(createExperimentFromCursor(cursor));
}
} finally {
if (cursor != null) {
cursor.close();
}
}
return experiments;
}
// Deletes a project object without touching its experiments.
private static void deleteProjectFromDb(SQLiteDatabase db, Project project) {
db.delete(
Tables.PROJECTS, ProjectColumns.PROJECT_ID + "=?", new String[] {project.getProjectId()});
}
private static Project createProjectFromCursor(Cursor cursor) {
Project project = new Project(cursor.getLong(0));
project.setProjectId(cursor.getString(1));
project.setTitle(cursor.getString(2));
project.setCoverPhoto(cursor.getString(3));
project.setArchived(cursor.getInt(4) == 1);
project.setDescription(cursor.getString(5));
project.setLastUsedTime(cursor.getLong(6));
return project;
}
@VisibleForTesting
@Deprecated
List<Project> getDatabaseProjects(boolean includeArchived) {
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
return getDatabaseProjects(db, includeArchived);
}
}
private static List<Project> getDatabaseProjects(SQLiteDatabase db, boolean includeArchived) {
List<Project> projects = new ArrayList<>();
String selection = ProjectColumns.ARCHIVED + "=?";
String[] selectionArgs = new String[] {"0"};
if (includeArchived) {
selection = null;
selectionArgs = null;
}
Cursor cursor = null;
try {
cursor =
db.query(
Tables.PROJECTS,
ProjectColumns.GET_COLUMNS,
selection,
selectionArgs,
null,
null,
ProjectColumns.LAST_USED_TIME + " DESC, " + BaseColumns._ID + " DESC");
while (cursor.moveToNext()) {
projects.add(createProjectFromCursor(cursor));
}
} finally {
if (cursor != null) {
cursor.close();
}
}
return projects;
}
@VisibleForTesting
@Deprecated
Project newProject() {
String projectId = newStableId(STABLE_PROJECT_ID_LENGTH);
ContentValues values = new ContentValues();
values.put(ProjectColumns.PROJECT_ID, projectId);
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
long id = db.insert(Tables.PROJECTS, null, values);
if (id != -1) {
Project project = new Project(id);
project.setProjectId(projectId);
return project;
}
}
return null;
}
@VisibleForTesting
@Deprecated
void updateProject(Project project) {
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
final ContentValues values = new ContentValues();
values.put(ProjectColumns.TITLE, project.getTitle());
values.put(ProjectColumns.DESCRIPTION, project.getDescription());
values.put(ProjectColumns.COVER_PHOTO, project.getCoverPhoto());
values.put(ProjectColumns.ARCHIVED, project.isArchived());
values.put(ProjectColumns.LAST_USED_TIME, project.getLastUsedTime());
db.update(
Tables.PROJECTS,
values,
ProjectColumns.PROJECT_ID + "=?",
new String[] {project.getProjectId()});
}
}
@Override
public Experiment getExperimentById(String experimentId) {
return getFileMetadataManager().getExperimentById(experimentId);
}
@VisibleForTesting
Experiment getDatabaseExperimentById(String experimentId) {
Experiment experiment = null;
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getReadableDatabase();
experiment = getDatabaseExperimentById(db, experimentId, context, false);
}
return experiment;
}
// Gets the experiment from the database.
private static Experiment getDatabaseExperimentById(
SQLiteDatabase db, String experimentId, Context context, boolean includeTrials) {
Experiment experiment;
final String selection = ExperimentColumns.EXPERIMENT_ID + "=?";
final String[] selectionArgs = new String[] {experimentId};
Cursor cursor = null;
try {
cursor =
db.query(
Tables.EXPERIMENTS,
ExperimentColumns.GET_COLUMNS,
selection,
selectionArgs,
null,
null,
null,
"1");
if (cursor == null || !cursor.moveToFirst()) {
return null;
}
experiment = createExperimentFromCursor(cursor);
} finally {
if (cursor != null) {
cursor.close();
}
}
populateDatabaseExperiment(db, experiment, context, includeTrials);
return experiment;
}
@VisibleForTesting
Experiment newDatabaseExperiment(String projectId) {
String experimentId = newStableId(STABLE_EXPERIMENT_ID_LENGTH);
long timestamp = getCurrentTime();
Experiment result = Experiment.newExperiment(timestamp, experimentId, 0);
localSyncManager.addExperiment(result.getExperimentId());
experimentLibraryManager.addExperiment(result.getExperimentId());
ContentValues values = new ContentValues();
values.put(ExperimentColumns.EXPERIMENT_ID, experimentId);
values.put(ExperimentColumns.PROJECT_ID, projectId);
values.put(ExperimentColumns.TIMESTAMP, timestamp);
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
long id = db.insert(Tables.EXPERIMENTS, null, values);
if (id != -1) {
return result;
}
}
return null;
}
@Override
public Experiment newExperiment() {
return getFileMetadataManager().newExperiment();
}
@Override
public void addExperiment(Experiment experiment) {
getFileMetadataManager().addExperiment(experiment);
}
@VisibleForTesting
Experiment newDatabaseExperiment() {
return newDatabaseExperiment(DEFAULT_PROJECT_ID);
}
@Override
public void deleteExperiment(Experiment experiment) {
getFileMetadataManager().deleteExperiment(experiment);
}
@Override
public void deleteExperiment(String experimentId) {
getFileMetadataManager().deleteExperiment(experimentId);
}
@VisibleForTesting
void deleteDatabaseExperiment(Experiment experiment) {
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
deleteDatabaseExperiment(db, experiment, context);
}
}
private static void deleteDatabaseExperiment(
SQLiteDatabase db, Experiment experiment, Context context) {
List<String> runIds =
getDatabaseExperimentRunIds(
db, experiment.getExperimentId(), /* include archived runs */ true);
for (String runId : runIds) {
deleteDatabaseTrial(db, runId, context);
}
deleteDatabaseObjectsInExperiment(db, experiment, context);
String[] experimentArgs = new String[] {experiment.getExperimentId()};
db.delete(Tables.EXPERIMENTS, ExperimentColumns.EXPERIMENT_ID + "=?", experimentArgs);
db.delete(
Tables.EXPERIMENT_SENSORS, ExperimentSensorColumns.EXPERIMENT_ID + "=?", experimentArgs);
db.delete(
Tables.EXPERIMENT_SENSOR_LAYOUT,
ExperimentSensorLayoutColumns.EXPERIMENT_ID + "=?",
experimentArgs);
}
private long getCurrentTime() {
return clock.getNow();
}
@Override
public void updateExperiment(Experiment experiment, boolean setDirty) {
getFileMetadataManager().updateExperiment(experiment, setDirty);
}
@Override
public Experiment importExperimentFromZip(Uri zipUri, ContentResolver resolver)
throws IOException {
FileMetadataManager manager = getFileMetadataManager();
return manager.importExperiment(context, zipUri, resolver);
}
@Override
public void saveImmediately() {
getFileMetadataManager().saveImmediately();
}
@Override
public boolean canMoveAllExperimentsToAnotherAccount(AppAccount targetAccount) {
return !FileMetadataUtil.getInstance().getExperimentsRootDirectory(targetAccount).exists()
&& !FileMetadataUtil.getInstance().getUserMetadataFile(targetAccount).exists();
}
@Override
public void moveAllExperimentsToAnotherAccount(AppAccount targetAccount) throws IOException {
// This method should not be called if canMoveAllExperimentsToAnotherAccount returns false.
if (!canMoveAllExperimentsToAnotherAccount(targetAccount)) {
throw new IllegalStateException("moveAllExperimentsToAnotherAccount now allowed now");
}
getFileMetadataManager().beforeMovingAllExperimentsToAnotherAccount();
// Move experiment root directory.
File sourceExperimentsRoot =
FileMetadataUtil.getInstance().getExperimentsRootDirectory(appAccount);
File targetExperimentsRoot =
FileMetadataUtil.getInstance().getExperimentsRootDirectory(targetAccount);
Files.move(sourceExperimentsRoot, targetExperimentsRoot);
// Move user_metadata.proto.
File sourceUserMetadataFile = FileMetadataUtil.getInstance().getUserMetadataFile(appAccount);
File targetUserMetadataFile = FileMetadataUtil.getInstance().getUserMetadataFile(targetAccount);
Files.move(sourceUserMetadataFile, targetUserMetadataFile);
// Move experiment and sensor databases.
ImmutableList<String> filesToMove =
ImmutableList.of("main.db", "main.db-journal", "sensors.db", "sensors.db-journal");
String[] sourceNames = context.databaseList();
for (String sourceName : sourceNames) {
if (filesToMove.contains(sourceName)) {
File sourceFile = context.getDatabasePath(sourceName);
String targetName = targetAccount.getDatabaseFileName(sourceName);
File targetFile = new File(sourceFile.getParentFile(), targetName);
Files.move(sourceFile, targetFile);
}
}
}
@Override
public void beforeMovingExperimentToAnotherAccount(Experiment experiment) {
// This SimpleMetadataManager is losing the experiment.
getFileMetadataManager().beforeMovingExperimentToAnotherAccount(experiment);
}
@Override
public void moveExperimentToAnotherAccount(Experiment experiment, AppAccount targetAccount)
throws IOException {
// Move experiment directory.
File sourceExperimentDirectory =
FileMetadataUtil.getInstance()
.getExperimentDirectory(appAccount, experiment.getExperimentId());
File targetExperimentDirectory =
FileMetadataUtil.getInstance()
.getExperimentDirectory(targetAccount, experiment.getExperimentId());
File parent = targetExperimentDirectory.getParentFile();
if (!parent.exists() && !parent.mkdir()) {
if (Log.isLoggable(TAG, Log.ERROR)) {
Log.e(TAG, "Failed to create parent directory.");
}
// TODO(lizlooney): Handle this situation!
throw new IOException("Failed to create parent directory " + parent);
}
Files.move(sourceExperimentDirectory, targetExperimentDirectory);
}
@Override
public void afterMovingExperimentFromAnotherAccount(Experiment experiment) {
// This SimpleMetadataManager is gaining the experiment.
getFileMetadataManager().afterMovingExperimentFromAnotherAccount(experiment);
}
private static void updateDatabaseExperiment(SQLiteDatabase db, Experiment experiment) {
final ContentValues values = new ContentValues();
values.put(ExperimentColumns.TITLE, experiment.getTitle());
values.put(ExperimentColumns.DESCRIPTION, experiment.getDescription());
values.put(ExperimentColumns.ARCHIVED, experiment.isArchived());
values.put(ExperimentColumns.LAST_USED_TIME, experiment.getLastUsedTime());
db.update(
Tables.EXPERIMENTS,
values,
ExperimentColumns.EXPERIMENT_ID + "=?",
new String[] {experiment.getExperimentId()});
}
@Override
public List<ExperimentOverviewPojo> getExperimentOverviews(boolean includeArchived) {
return getFileMetadataManager().getExperimentOverviews(includeArchived);
}
@VisibleForTesting
List<ExperimentOverviewPojo> getDatabaseExperimentOverviews(boolean includeArchived) {
List<ExperimentOverviewPojo> experiments = new ArrayList<>();
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getReadableDatabase();
String selection = "";
if (!includeArchived) {
selection = ExperimentColumns.ARCHIVED + "=0";
}
Cursor cursor = null;
try {
cursor =
db.query(
Tables.EXPERIMENTS,
ExperimentColumns.GET_COLUMNS,
selection,
null,
null,
null,
ExperimentColumns.LAST_USED_TIME + " DESC, " + BaseColumns._ID + " DESC");
while (cursor.moveToNext()) {
experiments.add(createExperimentOverviewFromCursor(cursor));
}
} finally {
if (cursor != null) {
cursor.close();
}
}
}
return experiments;
}
private static Experiment createExperimentFromCursor(Cursor cursor) {
GoosciExperiment.Experiment.Builder expProto = GoosciExperiment.Experiment.newBuilder();
expProto.setCreationTimeMs(cursor.getLong(2));
String description = cursor.getString(4);
expProto.setDescription((description != null) ? description : "");
String title = cursor.getString(3);
expProto.setTitle((title != null) ? title : "");
// Version 1 for starters.
// TODO: Remove this if we default the proto to 1.
expProto.setFileVersion(Version.FileVersion.newBuilder().setVersion(1).build());
return Experiment.fromExperiment(expProto.build(), createExperimentOverviewFromCursor(cursor));
}
private static ExperimentOverviewPojo createExperimentOverviewFromCursor(Cursor cursor) {
ExperimentOverviewPojo overview = new ExperimentOverviewPojo();
overview.setLastUsedTimeMs(cursor.getLong(7));
overview.setTitle(cursor.getString(3));
overview.setArchived(cursor.getInt(6) != 0);
overview.setExperimentId(cursor.getString(1));
if (overview.getTitle() == null) {
overview.setTitle("");
}
return overview;
}
@Override
public Experiment getLastUsedUnarchivedExperiment() {
return getFileMetadataManager().getLastUsedUnarchivedExperiment();
}
private static void deleteDatabaseObjectsInExperiment(
SQLiteDatabase db, Experiment experiment, Context context) {
List<Label> labels = getDatabaseLabelsForExperiment(db, experiment, context);
for (Label label : labels) {
deleteDatabaseLabel(db, label);
}
List<SensorTrigger> triggers = getDatabaseSensorTriggers(db, experiment.getExperimentId());
for (SensorTrigger trigger : triggers) {
deleteDatabaseSensorTrigger(db, trigger);
}
}
private static void populateDatabaseExperiment(
SQLiteDatabase db, Experiment experiment, Context context, boolean includeTrials) {
if (experiment == null) {
return;
}
List<Label> labels = getDatabaseLabelsForExperiment(db, experiment, context);
experiment.populateLabels(labels);
List<SensorTrigger> triggers = getDatabaseSensorTriggers(db, experiment.getExperimentId());
experiment.setSensorTriggers(triggers);
experiment.setSensorLayouts(
getDatabaseExperimentSensorLayouts(db, experiment.getExperimentId()));
if (includeTrials) {
List<String> trialIds = getDatabaseExperimentRunIds(db, experiment.getExperimentId(), true);
List<Trial> trials = new ArrayList<>();
for (String trialId : trialIds) {
List<ApplicationLabel> applicationLabels =
getDatabaseApplicationLabelsWithStartId(db, trialId);
trials.add(getDatabaseTrial(db, trialId, applicationLabels, context));
}
experiment.setTrials(trials);
}
}
@Override
@Deprecated
public void setLastUsedExperiment(Experiment experiment) {
getFileMetadataManager().setLastUsedExperiment(experiment);
}
@VisibleForTesting
Trial newTrial(
Experiment experiment,
String trialId,
long startTimestamp,
List<GoosciSensorLayout.SensorLayout> sensorLayouts) {
// How many runs already exist?
List<String> runIds =
getDatabaseExperimentRunIds(
experiment.getExperimentId(), /* include archived runs for indexing */ true);
int runIndex = runIds.size();
synchronized (lock) {
insertTrial(trialId, runIndex);
insertRunSensors(trialId, sensorLayouts);
}
// Can't use Trial.newTrial because runId must be the start label ID for this trial.
// That method can be used after DB upgrade, however.
GoosciTrial.Trial trialProto =
GoosciTrial.Trial.newBuilder()
.setCreationTimeMs(clock.getNow())
.addAllSensorLayouts(sensorLayouts)
.setTrialId(trialId)
.setArchived(false)
.setAutoZoomEnabled(true)
.setRecordingRange(Range.newBuilder().setStartMs(startTimestamp))
.build();
return Trial.fromTrial(trialProto);
}
private void insertTrial(String trialId, int runIndex) {
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
final ContentValues values = new ContentValues();
values.put(RunsColumns.RUN_ID, trialId);
values.put(RunsColumns.RUN_INDEX, runIndex);
values.put(RunsColumns.TIMESTAMP, getCurrentTime());
values.put(RunsColumns.ARCHIVED, false);
values.put(RunsColumns.TITLE, "");
values.put(RunsColumns.AUTO_ZOOM_ENABLED, true);
db.insert(Tables.RUNS, null, values);
}
}
private void insertRunSensors(String runId, List<GoosciSensorLayout.SensorLayout> sensorLayouts) {
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
final ContentValues values = new ContentValues();
values.put(RunSensorsColumns.RUN_ID, runId);
for (int i = 0; i < sensorLayouts.size(); i++) {
GoosciSensorLayout.SensorLayout layout = sensorLayouts.get(i);
fillLayoutValues(values, layout);
values.put(RunSensorsColumns.POSITION, i);
db.insert(Tables.RUN_SENSORS, null, values);
}
}
}
private void fillLayoutValues(ContentValues values, GoosciSensorLayout.SensorLayout layout) {
values.put(RunSensorsColumns.SENSOR_ID, layout.getSensorId());
values.put(RunSensorsColumns.LAYOUT, layout.toByteArray());
}
@VisibleForTesting
Trial getDatabaseTrial(String trialId, List<ApplicationLabel> applicationLabels) {
Trial trial;
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getReadableDatabase();
trial = getDatabaseTrial(db, trialId, applicationLabels, context);
}
return trial;
}
private static Trial getDatabaseTrial(
SQLiteDatabase db,
String trialId,
List<ApplicationLabel> applicationLabels,
Context context) {
List<Label> labels = getDatabaseLabelsForTrial(db, trialId, context);
List<GoosciSensorLayout.SensorLayout> sensorLayouts = new ArrayList<>();
int runIndex = -1;
boolean archived = false;
String title = "";
boolean autoZoomEnabled = true;
long creationTimeMs = -1;
final String selection = RunSensorsColumns.RUN_ID + "=?";
final String[] selectionArgs = new String[] {trialId};
Cursor cursor = null;
try {
cursor =
db.query(
Tables.RUNS,
new String[] {
RunsColumns.RUN_INDEX,
RunsColumns.TITLE,
RunsColumns.ARCHIVED,
RunsColumns.AUTO_ZOOM_ENABLED,
RunsColumns.TIMESTAMP
},
selection,
selectionArgs,
null,
null,
null);
if (cursor != null & cursor.moveToFirst()) {
runIndex = cursor.getInt(0);
title = cursor.getString(1);
archived = cursor.getInt(2) != 0;
autoZoomEnabled = cursor.getInt(3) != 0;
creationTimeMs = cursor.getLong(4);
}
} finally {
if (cursor != null) {
cursor.close();
}
}
// Now get sensor layouts.
GoosciSensorLayout.SensorLayout.Builder layout;
try {
cursor =
db.query(
Tables.RUN_SENSORS,
new String[] {RunSensorsColumns.LAYOUT, RunSensorsColumns.SENSOR_ID},
selection,
selectionArgs,
null,
null,
RunSensorsColumns.POSITION + " ASC");
while (cursor.moveToNext()) {
try {
byte[] blob = cursor.getBlob(0);
if (blob != null) {
layout = GoosciSensorLayout.SensorLayout.newBuilder().mergeFrom(blob);
} else {
// In this case, create a fake sensorLayout since none exists.
layout = GoosciSensorLayout.SensorLayout.newBuilder();
layout.setSensorId(cursor.getString(1));
}
sensorLayouts.add(layout.build());
} catch (InvalidProtocolBufferException e) {
Log.d(TAG, "Couldn't parse layout", e);
}
}
} finally {
if (cursor != null) {
cursor.close();
}
}
if (runIndex != -1) {
GoosciTrial.Trial.Builder trialProto =
GoosciTrial.Trial.newBuilder()
.setTrialId(trialId)
.addAllSensorLayouts(sensorLayouts)
.setTitle(title)
.setAutoZoomEnabled(autoZoomEnabled)
.setArchived(archived)
.setCreationTimeMs(creationTimeMs)
.setTrialNumberInExperiment(runIndex + 1);
populateTrialProtoFromLabels(trialProto, applicationLabels, labels);
Trial result = Trial.fromTrial(trialProto.build());
for (String sensorId : result.getSensorIds()) {
result.setStats(getDatabaseStats(db, trialId, sensorId));
}
return result;
} else {
return null;
}
}
@VisibleForTesting
public static void populateTrialProtoFromLabels(
GoosciTrial.Trial.Builder trialProto,
List<ApplicationLabel> applicationLabels,
List<Label> labels) {
// Populate the recording and crop ranges from labels.
Range.Builder recordingRange = Range.newBuilder();
Range.Builder cropRange;
if (!trialProto.hasCropRange()) {
cropRange = Range.newBuilder();
} else {
cropRange = trialProto.getCropRange().toBuilder();
}
boolean cropRangeUpdated = false;
for (ApplicationLabel label : applicationLabels) {
if (label.getType() == ApplicationLabel.TYPE_RECORDING_START) {
recordingRange.setStartMs(label.getTimeStamp());
} else if (label.getType() == ApplicationLabel.TYPE_RECORDING_STOP) {
recordingRange.setEndMs(label.getTimeStamp());
} else if (label.getType() == ApplicationLabel.TYPE_CROP_START) {
cropRange.setStartMs(label.getTimeStamp());
cropRangeUpdated = true;
} else if (label.getType() == ApplicationLabel.TYPE_CROP_END) {
cropRange.setEndMs(label.getTimeStamp());
cropRangeUpdated = true;
}
}
trialProto.setRecordingRange(recordingRange);
if (cropRangeUpdated) {
trialProto.setCropRange(cropRange);
}
trialProto.clearLabels();
for (int i = 0; i < labels.size(); i++) {
trialProto.addLabels(labels.get(i).getLabelProto());
}
}
/** Set the sensor selection and layout for an experiment. */
@VisibleForTesting
void setExperimentSensorLayouts(
String experimentId, List<GoosciSensorLayout.SensorLayout> sensorLayouts) {
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
for (int i = 0; i < sensorLayouts.size(); i++) {
ContentValues values = new ContentValues();
values.put(ExperimentSensorLayoutColumns.EXPERIMENT_ID, experimentId);
values.put(ExperimentSensorLayoutColumns.POSITION, i);
values.put(ExperimentSensorLayoutColumns.LAYOUT, sensorLayouts.get(i).toByteArray());
db.insertWithOnConflict(
Tables.EXPERIMENT_SENSOR_LAYOUT, null, values, SQLiteDatabase.CONFLICT_REPLACE);
}
db.delete(
Tables.EXPERIMENT_SENSOR_LAYOUT,
ExperimentSensorLayoutColumns.EXPERIMENT_ID
+ "=? AND "
+ ExperimentSensorLayoutColumns.POSITION
+ " >= "
+ sensorLayouts.size(),
new String[] {experimentId});
}
}
/** Retrieve the sensor selection and layout for an experiment. */
@VisibleForTesting
List<SensorLayoutPojo> getDatabaseExperimentSensorLayouts(String experimentId) {
List<SensorLayoutPojo> layouts = new ArrayList<>();
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getReadableDatabase();
layouts = getDatabaseExperimentSensorLayouts(db, experimentId);
}
return layouts;
}
private static List<SensorLayoutPojo> getDatabaseExperimentSensorLayouts(
SQLiteDatabase db, String experimentId) {
List<SensorLayoutPojo> layouts = new ArrayList<>();
Cursor cursor = null;
try {
cursor =
db.query(
Tables.EXPERIMENT_SENSOR_LAYOUT,
new String[] {ExperimentSensorLayoutColumns.LAYOUT},
ExperimentSensorLayoutColumns.EXPERIMENT_ID + "=?",
new String[] {experimentId},
null,
null,
ExperimentSensorLayoutColumns.POSITION + " ASC");
Set<String> sensorIdsAdded = new HashSet<>();
while (cursor.moveToNext()) {
try {
GoosciSensorLayout.SensorLayout layout =
GoosciSensorLayout.SensorLayout.parseFrom(cursor.getBlob(0));
if (!sensorIdsAdded.contains(layout.getSensorId())) {
layouts.add(SensorLayoutPojo.fromProto(layout));
}
sensorIdsAdded.add(layout.getSensorId());
} catch (InvalidProtocolBufferException e) {
Log.e(TAG, "Couldn't parse layout", e);
}
}
} finally {
if (cursor != null) {
cursor.close();
}
}
return layouts;
}
@VisibleForTesting
void deleteDatabaseTrial(String runId) {
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
deleteDatabaseTrial(db, runId, context);
}
}
private static void deleteDatabaseTrial(SQLiteDatabase db, String runId, Context context) {
for (Label label : getDatabaseLabelsForTrial(db, runId, context)) {
deleteDatabaseLabel(db, label);
}
for (ApplicationLabel label : getDatabaseApplicationLabelsWithStartId(db, runId)) {
deleteDatabaseApplicationLabel(db, label);
}
String selectionRunId = RunsColumns.RUN_ID + "=?";
String[] runIdArgs = new String[] {runId};
db.delete(Tables.RUN_SENSORS, selectionRunId, runIdArgs);
db.delete(Tables.RUN_STATS, RunStatsColumns.START_LABEL_ID + "=?", runIdArgs);
db.delete(Tables.RUNS, selectionRunId, runIdArgs);
}
@Override
public String addOrGetExternalSensor(
ExternalSensorSpec sensor, Map<String, SensorProvider> providerMap) {
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getReadableDatabase();
String sensorId = getExternalSensorId(sensor, db);
if (sensorId != null) {
return sensorId;
}
}
int suffix = 0;
while (getExternalSensorById(ExternalSensorSpec.getSensorId(sensor, suffix), providerMap)
!= null) {
suffix++;
}
String sensorId = ExternalSensorSpec.getSensorId(sensor, suffix);
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = getContentValuesFromSensor(sensor);
values.put(SensorColumns.SENSOR_ID, sensorId);
db.insert(Tables.EXTERNAL_SENSORS, null, values);
}
return sensorId;
}
@Nullable
private String getExternalSensorId(ExternalSensorSpec sensor, SQLiteDatabase db) {
String sql =
"SELECT IFNULL(MIN("
+ SensorColumns.SENSOR_ID
+ "), '') FROM "
+ Tables.EXTERNAL_SENSORS
+ " WHERE "
+ SensorColumns.CONFIG
+ "=? AND "
+ SensorColumns.TYPE
+ "=?";
SQLiteStatement statement = db.compileStatement(sql);
statement.bindBlob(1, sensor.getConfig());
statement.bindString(2, sensor.getType());
String sensorId = statement.simpleQueryForString();
if (!sensorId.isEmpty()) {
return sensorId;
}
return null;
}
@VisibleForTesting
void addDatabaseLabel(String experimentId, String trialId, Label label, LabelValue labelValue) {
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
addDatabaseLabel(db, experimentId, trialId, label, labelValue);
}
}
private static void addDatabaseLabel(
SQLiteDatabase db, String experimentId, String trialId, Label label, LabelValue labelValue) {
ContentValues values = new ContentValues();
values.put(LabelColumns.EXPERIMENT_ID, experimentId);
values.put(LabelColumns.TYPE, getLabelTag(label));
values.put(LabelColumns.TIMESTAMP, label.getTimeStamp());
values.put(LabelColumns.LABEL_ID, label.getLabelId());
values.put(LabelColumns.START_LABEL_ID, trialId);
// The database will only ever have one label value per label, so this is OK here.
values.put(LabelColumns.VALUE, labelValue.getValue().toProto().toByteArray());
db.insert(Tables.LABELS, null, values);
}
private static String getLabelTag(Label label) {
if (label.getType() == ValueType.TEXT) {
return TEXT_LABEL_TAG;
} else if (label.getType() == ValueType.PICTURE) {
return PICTURE_LABEL_TAG;
} else if (label.getType() == ValueType.SENSOR_TRIGGER) {
return SENSOR_TRIGGER_LABEL_TAG;
}
return UNKNOWN_LABEL_TAG;
}
@VisibleForTesting
void addDatabaseApplicationLabel(String experimentId, ApplicationLabel label) {
ContentValues values = new ContentValues();
values.put(LabelColumns.EXPERIMENT_ID, experimentId);
values.put(LabelColumns.TYPE, label.getTag());
values.put(LabelColumns.TIMESTAMP, label.getTimeStamp());
values.put(LabelColumns.LABEL_ID, label.getLabelId());
values.put(LabelColumns.START_LABEL_ID, label.getTrialId());
values.put(LabelColumns.VALUE, label.getValue().toProto().toByteArray());
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
db.insert(Tables.LABELS, null, values);
}
}
private static class LabelQuery {
public static String[] PROJECTION =
new String[] {
LabelColumns.TYPE,
LabelColumns.TIMESTAMP,
LabelColumns.DATA, // Deprecated for newer versions.
LabelColumns.LABEL_ID,
LabelColumns.START_LABEL_ID,
LabelColumns.EXPERIMENT_ID,
LabelColumns.VALUE,
};
public static int TYPE_INDEX = 0;
public static int TIMESTAMP_INDEX = 1;
public static int DATA_INDEX = 2;
public static int LABEL_ID_INDEX = 3;
public static int START_LABEL_ID_INDEX = 4;
public static int EXPERIMENT_ID_INDEX = 5;
public static int VALUE_INDEX = 6;
}
/**
* Gets the labels for a given experiment. This function is still used privately to populate an
* experiment with labels.
*/
private static List<Label> getDatabaseLabelsForExperiment(
SQLiteDatabase db, Experiment experiment, Context context) {
final String selection =
LabelColumns.EXPERIMENT_ID
+ "=? AND "
+ LabelColumns.START_LABEL_ID
+ "=? and not "
+ LabelColumns.TYPE
+ "=?";
;
final String[] selectionArgs =
new String[] {
experiment.getExperimentId(),
RecorderController.NOT_RECORDING_RUN_ID,
ApplicationLabel.TAG
};
return getDatabaseLabels(db, selection, selectionArgs, context);
}
private static List<Label> getDatabaseLabels(
SQLiteDatabase db, String selection, String[] selectionArgs, Context context) {
List<Label> labels = new ArrayList<>();
Cursor cursor = null;
try {
cursor =
db.query(
Tables.LABELS, LabelQuery.PROJECTION, selection, selectionArgs, null, null, null);
while (cursor.moveToNext()) {
String type = cursor.getString(LabelQuery.TYPE_INDEX);
if (ApplicationLabel.isTag(type)) {
continue;
}
// TODO: fix code smell: perhaps make a factory?
final String labelId = cursor.getString(LabelQuery.LABEL_ID_INDEX);
long timestamp = cursor.getLong(LabelQuery.TIMESTAMP_INDEX);
LabelValuePojo value = null;
try {
byte[] blob = cursor.getBlob(LabelQuery.VALUE_INDEX);
if (blob != null) {
value = new LabelValuePojo(GoosciLabelValue.LabelValue.parseFrom(blob));
}
} catch (InvalidProtocolBufferException ex) {
Log.d(TAG, "Unable to parse label value");
}
GoosciLabel.Label.Builder goosciLabel =
GoosciLabel.Label.newBuilder()
.setLabelId(labelId)
.setTimestampMs(timestamp)
.setCreationTimeMs(timestamp);
if (value != null) {
// Add new types of labels to this list, upgrading to Captions where appropriate
if (TextUtils.equals(type, PICTURE_LABEL_TAG)) {
GoosciPictureLabelValue.PictureLabelValue labelValue =
GoosciPictureLabelValue.PictureLabelValue.newBuilder()
.setFilePath(
PictureLabelValue.getAbsoluteFilePath(PictureLabelValue.getFilePath(value)))
.build();
Caption caption =
GoosciCaption.Caption.newBuilder()
.setLastEditedTimestamp(timestamp)
.setText(PictureLabelValue.getCaption(value))
.build();
goosciLabel
.setType(ValueType.PICTURE)
.setProtoData(labelValue.toByteString())
.setCaption(caption);
} else if (TextUtils.equals(type, TEXT_LABEL_TAG)) {
GoosciTextLabelValue.TextLabelValue labelValue =
GoosciTextLabelValue.TextLabelValue.newBuilder()
.setText(TextLabelValue.getText(value))
.build();
goosciLabel.setType(ValueType.TEXT).setProtoData(labelValue.toByteString());
} else if (TextUtils.equals(type, SENSOR_TRIGGER_LABEL_TAG)) {
// Convert old sensor triggers into text notes because we don't have enough
// info to keep them as triggers.
GoosciTextLabelValue.TextLabelValue.Builder labelValue =
GoosciTextLabelValue.TextLabelValue.newBuilder();
String autoText = SensorTriggerLabelValue.getAutogenText(value);
String customText = SensorTriggerLabelValue.getCustomText(value);
if (TextUtils.isEmpty(customText)) {
labelValue.setText(
String.format(
context.getResources().getString(R.string.old_trigger_note_format),
autoText));
} else {
labelValue.setText(
String.format(
context.getResources().getString(R.string.old_trigger_note_format_custom),
customText,
autoText));
}
goosciLabel.setType(ValueType.TEXT).setProtoData(labelValue.build().toByteString());
}
} else {
// Old text, picture and application labels were added when label data
// was stored as a string. New types of labels should not be added to this
// list.
final String data = cursor.getString(LabelQuery.DATA_INDEX);
if (TextUtils.equals(type, TEXT_LABEL_TAG)) {
GoosciTextLabelValue.TextLabelValue labelValue =
GoosciTextLabelValue.TextLabelValue.newBuilder().setText(data).build();
goosciLabel.setType(ValueType.TEXT).setProtoData(labelValue.toByteString());
} else if (TextUtils.equals(type, PICTURE_LABEL_TAG)) {
// Early picture labels had no captions.
GoosciPictureLabelValue.PictureLabelValue labelValue =
GoosciPictureLabelValue.PictureLabelValue.newBuilder().setFilePath(data).build();
goosciLabel.setType(ValueType.PICTURE).setProtoData(labelValue.toByteString());
} else {
throw new IllegalStateException("Unknown label type: " + type);
}
}
labels.add(Label.fromLabel(goosciLabel.build()));
}
} finally {
if (cursor != null) {
cursor.close();
}
}
return labels;
}
private static List<Label> getDatabaseLabelsForTrial(
SQLiteDatabase db, String trialId, Context context) {
final String selection = LabelColumns.START_LABEL_ID + "=? and not " + LabelColumns.TYPE + "=?";
final String[] selectionArgs = new String[] {trialId, ApplicationLabel.TAG};
return getDatabaseLabels(db, selection, selectionArgs, context);
}
@VisibleForTesting
List<ApplicationLabel> getDatabaseApplicationLabelsWithStartId(String trialId) {
List<ApplicationLabel> result;
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getReadableDatabase();
result = getDatabaseApplicationLabelsWithStartId(db, trialId);
}
return result;
}
private static List<ApplicationLabel> getDatabaseApplicationLabelsWithStartId(
SQLiteDatabase db, String trialId) {
final String selection = LabelColumns.START_LABEL_ID + "=? and " + LabelColumns.TYPE + "=?";
final String[] selectionArgs = new String[] {trialId, ApplicationLabel.TAG};
return getDatabaseApplicationLabels(db, selection, selectionArgs);
}
private static List<ApplicationLabel> getDatabaseApplicationLabels(
SQLiteDatabase db, String selection, String[] selectionArgs) {
List<ApplicationLabel> labels = new ArrayList<>();
Cursor cursor = null;
try {
cursor =
db.query(
Tables.LABELS, LabelQuery.PROJECTION, selection, selectionArgs, null, null, null);
while (cursor.moveToNext()) {
ApplicationLabel label;
// TODO: fix code smell: perhaps make a factory?
final String labelId = cursor.getString(LabelQuery.LABEL_ID_INDEX);
final String trialId = cursor.getString(LabelQuery.START_LABEL_ID_INDEX);
long timestamp = cursor.getLong(LabelQuery.TIMESTAMP_INDEX);
LabelValuePojo value = null;
try {
byte[] blob = cursor.getBlob(LabelQuery.VALUE_INDEX);
if (blob != null) {
value = new LabelValuePojo(GoosciLabelValue.LabelValue.parseFrom(blob));
}
} catch (InvalidProtocolBufferException ex) {
Log.d(TAG, "Unable to parse label value");
}
if (value == null) {
// Old text, picture and application labels were added when label data
// was stored as a string. New types of labels should not be added to this
// list.
final String data = cursor.getString(LabelQuery.DATA_INDEX);
label = new ApplicationLabel(data, labelId, trialId, timestamp);
} else {
label = new ApplicationLabel(labelId, trialId, timestamp, value);
}
labels.add(label);
}
} finally {
if (cursor != null) {
cursor.close();
}
}
return labels;
}
private static TrialStats getDatabaseStats(SQLiteDatabase db, String trialId, String sensorId) {
RunStats runStats = new RunStats(sensorId);
Cursor cursor = null;
try {
cursor =
db.query(
Tables.RUN_STATS,
new String[] {RunStatsColumns.STAT_NAME, RunStatsColumns.STAT_VALUE},
RunStatsColumns.START_LABEL_ID + " =? AND " + RunStatsColumns.SENSOR_TAG + " =?",
new String[] {trialId, sensorId},
null,
null,
null);
while (cursor.moveToNext()) {
final String statName = cursor.getString(0);
final double statValue = cursor.getDouble(1);
if (TextUtils.equals(statName, StatsAccumulator.KEY_STATUS)) {
runStats.setStatus((int) statValue);
} else {
runStats.putStat(statName, statValue);
}
}
} finally {
if (cursor != null) {
cursor.close();
}
}
return runStats.getTrialStats();
}
@VisibleForTesting
List<String> getDatabaseExperimentRunIds(String experimentId, boolean includeArchived) {
// TODO: use start index as offset.
List<String> ids;
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getReadableDatabase();
ids = getDatabaseExperimentRunIds(db, experimentId, includeArchived);
}
return ids;
}
private static List<String> getDatabaseExperimentRunIds(
SQLiteDatabase db, String experimentId, boolean includeArchived) {
List<String> ids = new ArrayList<>();
Cursor cursor = null;
try {
String selection =
LabelColumns.LABEL_ID
+ "="
+ LabelColumns.START_LABEL_ID
+ " AND "
+ LabelColumns.EXPERIMENT_ID
+ "=?";
if (!includeArchived) {
selection +=
" AND (" + RunsColumns.ARCHIVED + "=0 OR " + RunsColumns.ARCHIVED + " IS NULL)";
}
cursor =
db.query(
Tables.RUNS
+ " AS r JOIN "
+ Tables.LABELS
+ " AS l ON "
+ RunsColumns.RUN_ID
+ "="
+ LabelColumns.START_LABEL_ID,
new String[] {RunsColumns.RUN_ID},
selection,
new String[] {experimentId},
null,
null,
"r." + RunsColumns.TIMESTAMP + " DESC",
null);
while (cursor.moveToNext()) {
ids.add(cursor.getString(0));
}
} finally {
if (cursor != null) {
cursor.close();
}
}
return ids;
}
@VisibleForTesting
void editApplicationLabel(ApplicationLabel updatedLabel) {
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
final ContentValues values = new ContentValues();
values.put(LabelColumns.VALUE, updatedLabel.getValue().toProto().toByteArray());
values.put(LabelColumns.TIMESTAMP, updatedLabel.getTimeStamp());
db.update(
Tables.LABELS,
values,
LabelColumns.LABEL_ID + "=?",
new String[] {updatedLabel.getLabelId()});
}
}
// Deletes a label from the database, but does not touch its assets.
private static void deleteDatabaseLabel(SQLiteDatabase db, Label label) {
String selection = LabelColumns.LABEL_ID + "=?";
db.delete(Tables.LABELS, selection, new String[] {label.getLabelId()});
}
private static void deleteDatabaseApplicationLabel(SQLiteDatabase db, ApplicationLabel label) {
String selection = LabelColumns.LABEL_ID + "=?";
db.delete(Tables.LABELS, selection, new String[] {label.getLabelId()});
}
@NonNull
private ContentValues getContentValuesFromSensor(ExternalSensorSpec sensor) {
ContentValues values = new ContentValues();
values.put(SensorColumns.TYPE, sensor.getType());
values.put(SensorColumns.NAME, sensor.getName());
values.put(SensorColumns.CONFIG, sensor.getConfig());
return values;
}
@Override
public void removeExternalSensor(String databaseTag) {
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
db.delete(
Tables.EXTERNAL_SENSORS, SensorColumns.SENSOR_ID + "=?", new String[] {databaseTag});
db.delete(
Tables.EXPERIMENT_SENSORS,
ExperimentSensorColumns.SENSOR_TAG + "=?",
new String[] {databaseTag});
}
}
private static class SensorQuery {
public static String[] PROJECTION =
new String[] {
SensorColumns.SENSOR_ID, SensorColumns.TYPE, SensorColumns.NAME, SensorColumns.CONFIG
};
static int DATABASE_TAG_INDEX = 0;
static int TYPE_INDEX = 1;
static int NAME_INDEX = 2;
static int CONFIG_INDEX = 3;
}
@Override
public Map<String, ExternalSensorSpec> getExternalSensors(
Map<String, SensorProvider> providerMap) {
Map<String, ExternalSensorSpec> sensors = new HashMap<>();
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor c = null;
try {
c = db.query(Tables.EXTERNAL_SENSORS, SensorQuery.PROJECTION, null, null, null, null, null);
while (c.moveToNext()) {
ExternalSensorSpec value = loadSensorFromDatabase(c, providerMap);
if (value != null) {
sensors.put(c.getString(SensorQuery.DATABASE_TAG_INDEX), value);
}
}
} finally {
if (c != null) {
c.close();
}
}
}
return sensors;
}
// TODO: this should return SensorSpec instead of building it from providers right here.
@Override
public ExternalSensorSpec getExternalSensorById(
String id, Map<String, SensorProvider> providerMap) {
ExternalSensorSpec sensor = null;
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor c = null;
try {
c =
db.query(
Tables.EXTERNAL_SENSORS,
SensorQuery.PROJECTION,
SensorColumns.SENSOR_ID + "=?",
new String[] {id},
null,
null,
null);
if (c.moveToNext()) {
sensor = loadSensorFromDatabase(c, providerMap);
}
} finally {
if (c != null) {
c.close();
}
}
}
return sensor;
}
private ExternalSensorSpec loadSensorFromDatabase(
Cursor c, Map<String, SensorProvider> providerMap) {
String type = c.getString(SensorQuery.TYPE_INDEX);
SensorProvider sensorProvider = providerMap.get(type);
if (sensorProvider == null) {
throw new IllegalArgumentException("No provider for sensor type: " + type);
}
return sensorProvider.buildSensorSpec(
c.getString(SensorQuery.NAME_INDEX), c.getBlob(SensorQuery.CONFIG_INDEX));
}
@Override
public void addSensorToExperiment(String databaseTag, String experimentId) {
setSensorExperimentInclusion(databaseTag, experimentId, true);
}
@Override
public void removeSensorFromExperiment(String databaseTag, String experimentId) {
setSensorExperimentInclusion(databaseTag, experimentId, false);
}
private void setSensorExperimentInclusion(
String databaseTag, String experimentId, boolean included) {
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
// Because of legacy oddities, a databaseTag may be "included" multiple times.
// Delete them all (and don't do that again).
removeSensorExperimentInclusion(databaseTag, experimentId);
addSensorExperimentInclusion(db, databaseTag, experimentId, included);
}
}
@Override
public void eraseSensorFromExperiment(String databaseTag, String experimentId) {
synchronized (lock) {
dbHelper.getWritableDatabase();
removeSensorExperimentInclusion(databaseTag, experimentId);
}
}
private void removeSensorExperimentInclusion(String databaseTag, String experimentId) {
String selection =
ExperimentSensorColumns.SENSOR_TAG
+ " =? AND "
+ ExperimentSensorColumns.EXPERIMENT_ID
+ "=?";
String[] selectionArgs = new String[] {databaseTag, experimentId};
final SQLiteDatabase db = dbHelper.getWritableDatabase();
db.delete(Tables.EXPERIMENT_SENSORS, selection, selectionArgs);
}
private static void addSensorExperimentInclusion(
SQLiteDatabase db, String databaseTag, String experimentId, boolean included) {
ContentValues values = new ContentValues();
values.put(ExperimentSensorColumns.SENSOR_TAG, databaseTag);
values.put(ExperimentSensorColumns.EXPERIMENT_ID, experimentId);
values.put(ExperimentSensorColumns.INCLUDED, included ? 1 : 0);
db.insert(Tables.EXPERIMENT_SENSORS, ExperimentSensorColumns.SENSOR_TAG, values);
}
@Override
public ExperimentSensors getExperimentSensors(
String experimentId,
Map<String, SensorProvider> providerMap,
ConnectableSensor.Connector connector) {
final List<ConnectableSensor> externalSensors = new ArrayList<>();
final Set<String> excludedInternalSensorTags = new HashSet<>();
synchronized (lock) {
final String sql =
"SELECT t1."
+ ExperimentSensorColumns.SENSOR_TAG
+ ", t1."
+ ExperimentSensorColumns.INCLUDED
+ ", t2. "
+ SensorColumns.TYPE
+ ", t2. "
+ SensorColumns.NAME
+ ", t2. "
+ SensorColumns.CONFIG
+ " FROM "
+ Tables.EXPERIMENT_SENSORS
+ " t1 LEFT OUTER JOIN "
+ Tables.EXTERNAL_SENSORS
+ " t2"
+ " ON t1."
+ ExperimentSensorColumns.SENSOR_TAG
+ " = t2."
+ SensorColumns.SENSOR_ID
+ " WHERE t1."
+ ExperimentSensorColumns.EXPERIMENT_ID
+ " = ?"
+ " ORDER BY t1."
+ ExperimentSensorColumns.SENSOR_TAG
+ " ASC";
final SQLiteDatabase db = dbHelper.getReadableDatabase();
try (Cursor c = db.rawQuery(sql, new String[] {experimentId})) {
while (c.moveToNext()) {
String tag = c.getString(0);
boolean included = c.getInt(1) > 0;
String type = c.getString(2);
if (type == null) {
if (!included) {
excludedInternalSensorTags.add(tag);
}
} else {
SensorProvider sensorProvider = providerMap.get(type);
if (sensorProvider != null) {
ExternalSensorSpec spec =
sensorProvider.buildSensorSpec(c.getString(3), c.getBlob(4));
ConnectableSensor sensor =
connector.connected(ExternalSensorSpec.toGoosciSpec(spec), tag, included);
externalSensors.add(sensor);
}
}
}
}
}
return new ExperimentSensors(externalSensors, excludedInternalSensorTags);
}
@Override
public void addMyDevice(InputDeviceSpec deviceSpec) {
// TODO: don't use this, use fileMetadataManager version instead
databaseAddMyDevice(deviceSpec);
}
@VisibleForTesting
public void databaseAddMyDevice(InputDeviceSpec deviceSpec) {
String deviceId = addOrGetExternalSensor(deviceSpec, InputDeviceSpec.PROVIDER_MAP);
ContentValues values = new ContentValues();
values.put(MyDevicesColumns.DEVICE_ID, deviceId);
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
db.insert(Tables.MY_DEVICES, null, values);
}
}
@Override
public void removeMyDevice(InputDeviceSpec deviceSpec) {
// TODO: don't use this, use fileMetadataManager version instead
databaseRemoveMyDevice(deviceSpec);
}
public void databaseRemoveMyDevice(InputDeviceSpec deviceSpec) {
String deviceId = addOrGetExternalSensor(deviceSpec, InputDeviceSpec.PROVIDER_MAP);
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getWritableDatabase();
databaseRemoveMyDevice(deviceId, db);
}
}
private void databaseRemoveMyDevice(String deviceId, SQLiteDatabase db) {
db.delete(Tables.MY_DEVICES, MyDevicesColumns.DEVICE_ID + "=?", new String[] {deviceId});
}
@Override
public List<InputDeviceSpec> getMyDevices() {
// TODO: don't use this, use fileMetadataManager version instead
return databaseGetMyDevices();
}
@VisibleForTesting
public List<DeviceSpecPojo> fileGetMyDevices() {
return fileMetadataManager.getMyDevices();
}
@NonNull
public List<InputDeviceSpec> databaseGetMyDevices() {
synchronized (lock) {
final SQLiteDatabase db = dbHelper.getReadableDatabase();
return databaseGetMyDevices(db);
}
}
@NonNull
private List<InputDeviceSpec> databaseGetMyDevices(SQLiteDatabase db) {
ArrayList<InputDeviceSpec> myDevices = Lists.newArrayList();
Cursor c = null;
try {
c =
db.query(
Tables.MY_DEVICES,
new String[] {MyDevicesColumns.DEVICE_ID},
null,
null,
null,
null,
BaseColumns._ID + " ASC");
while (c.moveToNext()) {
InputDeviceSpec spec =
(InputDeviceSpec) getExternalSensorById(c.getString(0), InputDeviceSpec.PROVIDER_MAP);
// I _think_ this data state is only possible when debugging puts the data in
// weird states, but just to be safe...
if (spec != null) {
myDevices.add(spec);
}
}
return myDevices;
} finally {
if (c != null) {
c.close();
}
}
}
/** Gets a list of SensorTrigger by their experiment ID. */
private static List<SensorTrigger> getDatabaseSensorTriggers(
SQLiteDatabase db, String experimentId) {
List<SensorTrigger> triggers = new ArrayList<>();
if (TextUtils.isEmpty(experimentId)) {
return triggers;
}
final String selection = SensorTriggerColumns.EXPERIMENT_ID + "=?";
String[] selectionArgs = new String[] {experimentId};
Cursor c = null;
try {
c =
db.query(
Tables.SENSOR_TRIGGERS,
new String[] {
SensorTriggerColumns.TRIGGER_ID,
SensorTriggerColumns.SENSOR_ID,
SensorTriggerColumns.LAST_USED_TIMESTAMP_MS,
SensorTriggerColumns.TRIGGER_INFORMATION
},
selection,
selectionArgs,
null,
null,
SensorTriggerColumns.LAST_USED_TIMESTAMP_MS + " DESC");
if (c == null || !c.moveToFirst()) {
return triggers;
}
while (!c.isAfterLast()) {
triggers.add(
SensorTrigger.fromTrigger(
c.getString(0),
c.getString(1),
c.getLong(2),
GoosciSensorTriggerInformation.TriggerInformation.parseFrom(c.getBlob(3))));
c.moveToNext();
}
} catch (InvalidProtocolBufferException e) {
Log.e(TAG, "Couldn't parse trigger information", e);
} finally {
if (c != null) {
c.close();
}
}
return triggers;
}
/** Deletes the SensorTrigger from the database. */
private static void deleteDatabaseSensorTrigger(SQLiteDatabase db, SensorTrigger trigger) {
db.delete(
Tables.SENSOR_TRIGGERS,
SensorTriggerColumns.TRIGGER_ID + "=?",
new String[] {trigger.getTriggerId()});
}
public interface ProjectColumns {
/** Stable project ID. */
String PROJECT_ID = "project_id";
/** Project Title. */
String TITLE = "title";
/** Project description. */
String DESCRIPTION = "description";
/** Project cover photo. This is a local file URL. */
String COVER_PHOTO = "cover_photo";
/** Whether the project is archived or not. */
String ARCHIVED = "archived";
/** Timestamp in UTC based on phone system clock of the last time the project was used. */
String LAST_USED_TIME = "last_used_time";
/** Selection args for getting a project data. */
String[] GET_COLUMNS =
new String[] {
BaseColumns._ID,
ProjectColumns.PROJECT_ID,
ProjectColumns.TITLE,
ProjectColumns.COVER_PHOTO,
ProjectColumns.ARCHIVED,
ProjectColumns.DESCRIPTION,
ProjectColumns.LAST_USED_TIME
};
}
public interface ExperimentColumns {
/** Project this experiment belongs to, corresponding to {@link ProjectColumns#PROJECT_ID}. */
String PROJECT_ID = "project_id";
/** Stable experiment ID. */
String EXPERIMENT_ID = "experiment_id";
/** Timestamp of this experiment when it was created. */
String TIMESTAMP = "timestamp";
/** Experiment title. */
String TITLE = "title";
/** Experiment description. */
String DESCRIPTION = "description";
/** Whether the experiment is archived or not. */
String ARCHIVED = "archived";
/** Timestamp of when this experiment was last used. */
String LAST_USED_TIME = "last_used_time";
String[] GET_COLUMNS =
new String[] {
BaseColumns._ID,
ExperimentColumns.EXPERIMENT_ID,
ExperimentColumns.TIMESTAMP,
ExperimentColumns.TITLE,
ExperimentColumns.DESCRIPTION,
ExperimentColumns.PROJECT_ID,
ExperimentColumns.ARCHIVED,
ExperimentColumns.LAST_USED_TIME
};
}
public interface LabelColumns {
/**
* Experiment this label belongs to, corresponding to {@link ExperimentColumns#EXPERIMENT_ID}.
*/
String EXPERIMENT_ID = "experiment_id";
/** Time when this label was created. */
String TIMESTAMP = "timestamp";
/** Type of label, "text", "picture", "application", or "sensorTriggerLabel". */
String TYPE = "type";
/**
* Data for the label: in the case of a text label, this is the text. In the case of other
* types, a Uri pointing at the media. This field is deprecated at database version 15, but is
* still read for old labels.
*/
String DATA = "data";
/** Unique id for the label. */
String LABEL_ID = "label_id";
/** ID for the run that this label is associated with. */
String START_LABEL_ID = "start_label_id";
/** The GoosciLabelStorage stored as a blob for the value of the data. */
String VALUE = "value";
}
public interface SensorColumns {
/** ID of the sensor. Should be unique. */
String SENSOR_ID = "sensor_id";
/** Type of external sensor. */
String TYPE = "type";
/** Human readable name of this sensor. */
String NAME = "name";
/** Configuration data for this sensor. */
String CONFIG = "config";
}
public interface ExperimentSensorColumns {
/** Database tag of a sensor that belongs (or doesn't) to a particular experiment. */
String SENSOR_TAG = "sensor_tag";
/** Experiment ID. */
String EXPERIMENT_ID = "experiment_id";
/** boolean, 1 = included, 0 = excluded. */
String INCLUDED = "included";
}
public interface RunStatsColumns {
/** ID for the run that this stat is associated with. */
String START_LABEL_ID = "start_label_id";
/** Database tag of the sensor that this stat is associated with. */
String SENSOR_TAG = "sensor_tag";
/** Name of the stat being stored. */
String STAT_NAME = "stat_name";
/** Value of the stat */
String STAT_VALUE = "stat_value";
}
public interface RunsColumns {
/**
* ID for the run that this row is associated with. (Matches "start_label_id" in some other
* tables)
*/
String RUN_ID = "run_id";
/**
* Index of this run in the total experiment list. Storing this because we retrieve runs one at
* a time, so can't derive this at query time.
*/
String RUN_INDEX = "run_index";
/** Time when this run was _completed_ */
String TIMESTAMP = "timestamp";
/** User chosen name for this run. This may be empty. */
String TITLE = "run_title";
/** Whether the run is archived. */
String ARCHIVED = "run_archived";
/** Whether auto zoom is enabled (i.e. RunReview should zoom in on the Y axis by default) */
String AUTO_ZOOM_ENABLED = "auto_zoom_enabled";
}
public interface RunSensorsColumns {
/**
* ID for the run that this sensor is associated with. (Matches "start_label_id" in some other
* tables)
*/
String RUN_ID = "run_id";
/** ID of the sensor */
String SENSOR_ID = "sensor_id";
/** Position in the list of sensors on screen */
String POSITION = "position";
/** ID for the sensor layout this sensor is associated with. */
String LAYOUT = "layout";
}
public interface ExperimentSensorLayoutColumns {
/** Experiment ID. */
String EXPERIMENT_ID = "experiment_id";
/** Position in the list of sensors on screen */
String POSITION = "position";
/** Layout of this sensor (including sensorId) */
String LAYOUT = "layout";
}
public interface SensorTriggerColumns {
/** Trigger ID. THis is unique. */
String TRIGGER_ID = "trigger_id";
/** Sensor ID for this trigger. */
String SENSOR_ID = "sensor_id";
/** The timestamp when this trigger was last used. */
String LAST_USED_TIMESTAMP_MS = "last_used_timestamp";
/** The experiment ID that this trigger is associated with. */
String EXPERIMENT_ID = "experiment_id";
/** The TriggerInformation proto containing the configuration of this trigger. */
String TRIGGER_INFORMATION = "trigger_information";
}
public interface MyDevicesColumns {
/**
* The id of a device that has been memorized to "My Devices" This should be a key to a row in
* EXTERNAL_SENSORS
*/
String DEVICE_ID = "device_id";
}
/** Manages the SQLite database backing the data for the entire app (per account). */
private static class DatabaseHelper extends SQLiteOpenHelper {
private static final int DB_VERSION = 22;
private static final String DB_NAME = "main.db";
// Callbacks for database upgrades.
interface MetadataDatabaseUpgradeCallback {
// Called when project data needs to be migrated.
void onMigrateProjectData(SQLiteDatabase db);
// Called when experiment data needs to be migrated.
void onMigrateExperimentsToFiles(SQLiteDatabase db);
void onMigrateMyDevicesToProto(SQLiteDatabase db);
}
private MetadataDatabaseUpgradeCallback upgradeCallback;
DatabaseHelper(
Context context,
AppAccount appAccount,
String filename,
MetadataDatabaseUpgradeCallback upgradeCallback) {
super(
context,
appAccount.getDatabaseFileName(filename != null ? filename : DB_NAME),
null,
DB_VERSION);
this.upgradeCallback = upgradeCallback;
}
@Override
public void onCreate(SQLiteDatabase db) {
createProjectsTable(db);
createExperimentsTable(db);
createLabelsTable(db);
createSensorsTable(db);
createExperimentSensorsTable(db);
createRunStatsTable(db);
createRunsTable(db);
createRunSensorsTable(db);
createExperimentSensorLayoutTable(db);
createSensorTriggersTable(db);
createMyDevicesTable(db);
}
private void createExperimentsTable(SQLiteDatabase db) {
db.execSQL(
"CREATE TABLE "
+ Tables.EXPERIMENTS
+ " ("
+ BaseColumns._ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ ExperimentColumns.EXPERIMENT_ID
+ " TEXT NOT NULL, "
+ ExperimentColumns.PROJECT_ID
+ " TEXT NOT NULL, "
+ ExperimentColumns.TIMESTAMP
+ " INTEGER NOT NULL, "
+ ExperimentColumns.TITLE
+ " TEXT, "
+ ExperimentColumns.DESCRIPTION
+ " TEXT, "
+ ExperimentColumns.ARCHIVED
+ " BOOLEAN NOT NULL DEFAULT 0, "
+ ExperimentColumns.LAST_USED_TIME
+ " INTEGER, "
+ "UNIQUE ("
+ ExperimentColumns.EXPERIMENT_ID
+ ") ON CONFLICT REPLACE)");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
int version = oldVersion;
if ((version == 1 || version == 2) && version < newVersion) {
// 1 -> 2: Recreate labels table with label_id field.
// 2 -> 3: Recreate labels table with start_label_id field.
db.execSQL("DROP TABLE " + Tables.LABELS);
createLabelsTable(db);
//noinspection UnusedAssignment
version = 3;
}
if (version == 3 && version < newVersion) {
// 3 -> 4: Add sensors table and mapping table for experiment sensors.
createSensorsTable(db);
createExperimentSensorsTable(db);
version = 4;
}
if (version == 4 && version < newVersion) {
// 4 -> 5: Add new columns to projects table (title, cover and archived).
// Also add title, archived and description to experiments table.
db.execSQL(
"ALTER TABLE " + Tables.PROJECTS + " ADD COLUMN " + ProjectColumns.TITLE + " TEXT");
db.execSQL(
"ALTER TABLE "
+ Tables.PROJECTS
+ " ADD COLUMN "
+ ProjectColumns.ARCHIVED
+ " BOOLEAN");
db.execSQL(
"ALTER TABLE "
+ Tables.PROJECTS
+ " ADD COLUMN "
+ ProjectColumns.COVER_PHOTO
+ " TEXT");
db.execSQL(
"ALTER TABLE "
+ Tables.EXPERIMENTS
+ " ADD COLUMN "
+ ExperimentColumns.TITLE
+ " TEXT");
db.execSQL(
"ALTER TABLE "
+ Tables.EXPERIMENTS
+ " ADD COLUMN "
+ ExperimentColumns.ARCHIVED
+ " BOOLEAN");
db.execSQL(
"ALTER TABLE "
+ Tables.EXPERIMENTS
+ " ADD COLUMN "
+ ExperimentColumns.DESCRIPTION
+ " TEXT");
version = 5;
}
if (version == 5 && version < newVersion) {
// 5 -> 6: Drop tables and recreate them to set a default FALSE value to archived
// bit in projects and experiments tables. SQLite does not support ALTER TABLE
// foo ALTER COLUMN or DROP COLUMN. SQLite implements a small subset of SQL.
// Hence the need to drop the tables and re-create them.
// See https://www.sqlite.org/lang_altertable.html for allowed syntax.
db.execSQL("DROP TABLE " + Tables.PROJECTS);
db.execSQL("DROP TABLE " + Tables.EXPERIMENTS);
db.execSQL("DROP TABLE " + Tables.EXPERIMENT_SENSORS);
db.execSQL("DROP TABLE " + Tables.LABELS);
createProjectsTable(db);
createExperimentsTable(db);
createLabelsTable(db);
createExperimentSensorsTable(db);
version = 6;
}
if (version == 6 && version < newVersion) {
createRunStatsTable(db);
version = 7;
}
if (version == 7 && version < newVersion) {
// 7 -> 8: Add description column to projects.
db.execSQL(
"ALTER TABLE "
+ Tables.PROJECTS
+ " ADD COLUMN "
+ ProjectColumns.DESCRIPTION
+ " TEXT");
}
if (version == 8 && version < newVersion) {
// We could try to rebuild the runs table from the information in the labels
// table, but it's likely not worth it pre-release.
db.execSQL("DROP TABLE " + Tables.LABELS);
createLabelsTable(db);
createRunsTable(db);
createRunSensorsTable(db);
version = 9;
}
if (version == 9 && version < newVersion) {
// Add last used columns.
db.execSQL(
"ALTER TABLE "
+ Tables.PROJECTS
+ " ADD COLUMN "
+ ProjectColumns.LAST_USED_TIME
+ " INTEGER");
db.execSQL(
"ALTER TABLE "
+ Tables.EXPERIMENTS
+ " ADD COLUMN "
+ ExperimentColumns.LAST_USED_TIME
+ " INTEGER");
version = 10;
}
if (version == 10 && version < newVersion) {
// Add experiment sensor layout table
createExperimentSensorLayoutTable(db);
version = 11;
}
if (version == 11 && version < newVersion) {
// Add run index to runs table.
db.execSQL(
"ALTER TABLE " + Tables.RUNS + " ADD COLUMN " + RunsColumns.RUN_INDEX + " INTEGER");
// Insert sentinel value for older runs.
db.execSQL("UPDATE " + Tables.RUNS + " SET " + RunsColumns.RUN_INDEX + " = -1");
version = 12;
}
if (version == 12 && version < newVersion) {
// Add run archived state and title to the runs table.
db.execSQL("ALTER TABLE " + Tables.RUNS + " ADD COLUMN " + RunsColumns.TITLE + " TEXT");
db.execSQL(
"ALTER TABLE " + Tables.RUNS + " ADD COLUMN " + RunsColumns.ARCHIVED + " BOOLEAN");
version = 13;
}
if (version == 13 && version < newVersion) {
// Add SensorLayouts to the Runs Sensors table.
db.execSQL(
"ALTER TABLE "
+ Tables.RUN_SENSORS
+ " ADD COLUMN "
+ RunSensorsColumns.LAYOUT
+ " BLOB");
db.execSQL(
"ALTER TABLE "
+ Tables.RUN_SENSORS
+ " ADD COLUMN "
+ RunSensorsColumns.POSITION
+ " INTEGER");
db.execSQL("UPDATE " + Tables.RUN_SENSORS + " SET " + RunSensorsColumns.POSITION + " = -1");
version = 14;
}
if (version == 14 && version < newVersion) {
db.execSQL("ALTER TABLE " + Tables.LABELS + " ADD COLUMN " + LabelColumns.VALUE + " BLOB");
version = 15;
}
if (version == 15 && version < newVersion) {
db.execSQL(
"ALTER TABLE "
+ Tables.RUNS
+ " ADD COLUMN "
+ RunsColumns.AUTO_ZOOM_ENABLED
+ " BOOLEAN");
version = 16;
}
if (version == 16 && version < newVersion) {
db.execSQL(
"UPDATE "
+ Tables.RUNS
+ " SET "
+ RunsColumns.AUTO_ZOOM_ENABLED
+ " = 1 WHERE "
+ RunsColumns.AUTO_ZOOM_ENABLED
+ " IS NULL");
version = 17;
}
if (version == 17 && version < newVersion) {
createSensorTriggersTable(db);
version = 18;
}
if (version == 18 && version < newVersion) {
createMyDevicesTable(db);
version = 19;
}
if (version == 19 && version < newVersion) {
db.execSQL(
"ALTER TABLE "
+ Tables.EXPERIMENT_SENSORS
+ " ADD COLUMN "
+ ExperimentSensorColumns.INCLUDED
+ " INTEGER DEFAULT 1");
version = 20;
}
if (version == 20 && version < newVersion) {
// Projects are no longer used; need to tell the metadata manager to integrate that
// data into the experiment.
upgradeCallback.onMigrateProjectData(db);
version = 21;
}
if (version == 21 && version < newVersion) {
// Migrate experiment data into file-based system.
upgradeCallback.onMigrateExperimentsToFiles(db);
version = 22;
}
// TODO: upgrade my devices (once device spec population is complete and tested).
}
private void createProjectsTable(SQLiteDatabase db) {
db.execSQL(
"CREATE TABLE "
+ Tables.PROJECTS
+ " ("
+ BaseColumns._ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ ProjectColumns.PROJECT_ID
+ " TEXT NOT NULL, "
+ ProjectColumns.TITLE
+ " TEXT, "
+ ProjectColumns.DESCRIPTION
+ " TEXT, "
+ ProjectColumns.COVER_PHOTO
+ " TEXT, "
+ ProjectColumns.ARCHIVED
+ " BOOLEAN NOT NULL DEFAULT 0, "
+ ProjectColumns.LAST_USED_TIME
+ " INTEGER, "
+ "UNIQUE ("
+ ProjectColumns.PROJECT_ID
+ ") ON CONFLICT REPLACE)");
}
private void createLabelsTable(SQLiteDatabase db) {
db.execSQL(
"CREATE TABLE "
+ Tables.LABELS
+ " ("
+ BaseColumns._ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ LabelColumns.TIMESTAMP
+ " INTEGER NOT NULL, "
+ LabelColumns.EXPERIMENT_ID
+ " TEXT NOT NULL, "
+ LabelColumns.TYPE
+ " TEXT NOT NULL, "
+ LabelColumns.DATA
+ " TEXT, "
+ LabelColumns.LABEL_ID
+ " TEXT NOT NULL, "
+ LabelColumns.START_LABEL_ID
+ " TEXT,"
+ LabelColumns.VALUE
+ " BLOB)");
}
private void createSensorsTable(SQLiteDatabase db) {
db.execSQL(
"CREATE TABLE "
+ Tables.EXTERNAL_SENSORS
+ " ("
+ BaseColumns._ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ SensorColumns.TYPE
+ " TEXT NOT NULL,"
+ SensorColumns.SENSOR_ID
+ " TEXT UNIQUE,"
+ SensorColumns.NAME
+ " TEXT NOT NULL,"
+ SensorColumns.CONFIG
+ " BLOB)");
}
private void createExperimentSensorsTable(SQLiteDatabase db) {
db.execSQL(
"CREATE TABLE "
+ Tables.EXPERIMENT_SENSORS
+ " ("
+ BaseColumns._ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ ExperimentSensorColumns.SENSOR_TAG
+ " TEXT,"
+ ExperimentSensorColumns.EXPERIMENT_ID
+ " TEXT,"
+ ExperimentSensorColumns.INCLUDED
+ " INTEGER DEFAULT 1)");
}
private void createRunStatsTable(SQLiteDatabase db) {
db.execSQL(
"CREATE TABLE "
+ Tables.RUN_STATS
+ " ("
+ BaseColumns._ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ RunStatsColumns.START_LABEL_ID
+ " TEXT,"
+ RunStatsColumns.SENSOR_TAG
+ " TEXT,"
+ RunStatsColumns.STAT_NAME
+ " TEXT,"
+ RunStatsColumns.STAT_VALUE
+ " REAL, "
+ "UNIQUE("
+ RunStatsColumns.START_LABEL_ID
+ ","
+ RunStatsColumns.SENSOR_TAG
+ ","
+ RunStatsColumns.STAT_NAME
+ ") ON CONFLICT REPLACE)");
}
private void createRunsTable(SQLiteDatabase db) {
db.execSQL(
"CREATE TABLE "
+ Tables.RUNS
+ " ("
+ BaseColumns._ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ RunsColumns.RUN_ID
+ " TEXT UNIQUE,"
+ RunsColumns.RUN_INDEX
+ " INTEGER,"
+ RunsColumns.TIMESTAMP
+ " INTEGER NOT NULL,"
+ RunsColumns.TITLE
+ " TEXT,"
+ RunsColumns.ARCHIVED
+ " BOOLEAN,"
+ RunsColumns.AUTO_ZOOM_ENABLED
+ " BOOLEAN NOT NULL DEFAULT 1)");
}
private void createRunSensorsTable(SQLiteDatabase db) {
db.execSQL(
"CREATE TABLE "
+ Tables.RUN_SENSORS
+ " ("
+ BaseColumns._ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ RunSensorsColumns.RUN_ID
+ " TEXT,"
+ RunSensorsColumns.SENSOR_ID
+ " TEXT,"
+ RunSensorsColumns.LAYOUT
+ " BLOB,"
+ RunSensorsColumns.POSITION
+ " INTEGER,"
+ "UNIQUE("
+ RunSensorsColumns.RUN_ID
+ ","
+ RunSensorsColumns.SENSOR_ID
+ "))");
}
private void createExperimentSensorLayoutTable(SQLiteDatabase db) {
db.execSQL(
"CREATE TABLE "
+ Tables.EXPERIMENT_SENSOR_LAYOUT
+ " ("
+ BaseColumns._ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ ExperimentSensorLayoutColumns.EXPERIMENT_ID
+ " TEXT,"
+ ExperimentSensorLayoutColumns.POSITION
+ " INTEGER,"
+ ExperimentSensorLayoutColumns.LAYOUT
+ " BLOB,"
+ "UNIQUE("
+ ExperimentSensorLayoutColumns.EXPERIMENT_ID
+ ","
+ ExperimentSensorLayoutColumns.POSITION
+ "))");
}
private void createSensorTriggersTable(SQLiteDatabase db) {
db.execSQL(
"CREATE TABLE "
+ Tables.SENSOR_TRIGGERS
+ " ("
+ BaseColumns._ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ SensorTriggerColumns.TRIGGER_ID
+ " TEXT UNIQUE,"
+ SensorTriggerColumns.EXPERIMENT_ID
+ " TEXT,"
+ SensorTriggerColumns.LAST_USED_TIMESTAMP_MS
+ " INTEGER,"
+ SensorTriggerColumns.SENSOR_ID
+ " TEXT,"
+ SensorTriggerColumns.TRIGGER_INFORMATION
+ " BLOB)");
}
private void createMyDevicesTable(SQLiteDatabase db) {
db.execSQL(
"CREATE TABLE "
+ Tables.MY_DEVICES
+ " ("
+ BaseColumns._ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ MyDevicesColumns.DEVICE_ID
+ " TEXT NOT NULL, "
+ "UNIQUE ("
+ MyDevicesColumns.DEVICE_ID
+ ") ON CONFLICT REPLACE)");
}
}
private static final String STABLE_ID_CHARS =
"abcdefghijklmnopqrstuvwxyz" + "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";
/**
* Creates a stable ID of alphanumeric characters. This is more useful than autoincremented row
* IDs from the database because those can get moved around due to account data synchronization or
* sync adapter munging.
*
* @return a stable ID which is a random String of alphanumeric characters at the desired length.
*/
private String newStableId(int length) {
Random random = new Random();
StringBuilder builder = new StringBuilder(length);
for (int index = 0; index < length; index++) {
builder.append(STABLE_ID_CHARS.charAt(random.nextInt(STABLE_ID_CHARS.length())));
}
return builder.toString();
}
}
