Commit 1898084

@@ -0,0 +1,39 @@
+
+import 'package:sqflite_common/sqflite.dart';
+
+/// Helper methods for database interaction to allow code reuse.
+class DBHelper {
+  DBHelper._();
+
+  /// Get a entryID from a database.
+  ///
+  /// Ensures that the associated timestamp matches [timestampUnixS] and that
+  /// it is used in no table with a name in [blockedTables]. Creates a entry
+  /// when necessary
+  static Future<int> getEntryID(
+    Transaction txn,
+    int timestampUnixS,
+    List<String> blockedTables,
+  ) async {
+    var query = 'SELECT entryID FROM Timestamps WHERE timestampUnixS = ?';
+    for (final t in blockedTables) {
+      query += 'AND entryID NOT IN (SELECT entryID FROM $t)';
+    }
+    final existing = await txn.rawQuery(query, [timestampUnixS]);
+    int entryID;
+    if (existing.isEmpty) {
+      final result = await txn.query('Timestamps',
+        columns: ['MAX(entryID)']
+      );
+      final highestID = result.first['MAX(entryID)'] as int?;
+      entryID = (highestID ?? 0) + 1;
+      await txn.insert('Timestamps', {
+        'entryID': entryID,
+        'timestampUnixS': timestampUnixS,
+      });
+    } else {
+      entryID = existing.first['entryID'] as int;
+    }
+    return entryID;
+  }
+}

@@ -0,0 +1,63 @@
+
+import 'package:health_data_store/src/database_helper.dart';
+import 'package:health_data_store/src/database_manager.dart';
+import 'package:sqflite_common/sqflite.dart';
+import 'package:test/test.dart';
+
+import 'database_manager_test.dart';
+
+void main() {
+  sqfliteTestInit();
+  test('should find new entryID', () async {
+    final db = await DatabaseManager.load(await openDatabase(
+      inMemoryDatabasePath,
+    ));
+    addTearDown(db.close);
+    await db.db.transaction((txn) async {
+      final entry1 = await DBHelper.getEntryID(txn, 123, []);
+      expect(entry1, 1);
+      final entry2 = await DBHelper.getEntryID(txn, 124, []);
+      expect(entry2, 2);
+    });
+  });
+  test('should find existing entryID', () async {
+    final db = await DatabaseManager.load(await openDatabase(
+      inMemoryDatabasePath,
+    ));
+    addTearDown(db.close);
+    await db.db.transaction((txn) async {
+      final entry1 = await DBHelper.getEntryID(txn, 123, []);
+      expect(entry1, 1);
+      final entry1Again = await DBHelper.getEntryID(txn, 123, []);
+      expect(entry1Again, 1);
+    });
+  });
+  test('should find existing entryID with constraints', () async {
+    final db = await DatabaseManager.load(await openDatabase(
+      inMemoryDatabasePath,
+    ));
+    addTearDown(db.close);
+    await db.db.transaction((txn) async {
+      final entry1 = await DBHelper.getEntryID(txn, 123, []);
+      expect(entry1, 1);
+      final entry1Again = await DBHelper.getEntryID(txn, 123, ['Systolic']);
+      expect(entry1Again, 1);
+    });
+  });
+  test('should find respect constraints when finding entryID', () async {
+    final db = await DatabaseManager.load(await openDatabase(
+      inMemoryDatabasePath,
+    ));
+    addTearDown(db.close);
+    await db.db.transaction((txn) async {
+      final entry1 = await DBHelper.getEntryID(txn, 123, []);
+      expect(entry1, 1);
+      await txn.insert('Systolic', {
+        'entryID': entry1,
+        'value': 1,
+      });
+      final anotherEntry = await DBHelper.getEntryID(txn, 123, ['Systolic']);
+      expect(anotherEntry, 2);
+    });
+  });
+}