Commit 6a8be06

@@ -83,16 +83,36 @@ class DatabaseManager {
     await _db.execute('CREATE TABLE "Notes" ('
       '"entryID"	INTEGER NOT NULL,'
       '"note"     TEXT,'
-      '"color"    INTEGER,'
+      '"color"    INTEGER,' // TODO: add attachments
       'FOREIGN KEY("entryID") REFERENCES "Timestamps"("entryID"),'
       'PRIMARY KEY("entryID")'
     ');');
+    // TODO: make one txn
+  }
+
+  /// Removes unused and deleted entries rows.
+  ///
+  /// Specifically:
+  /// - medicines that are marked as deleted and have no referencing intakes
+  /// - timestamp entries that have no
+  Future<void> performCleanup() async {
+    await _db.transaction((txn) async {
+      await txn.rawDelete('DELETE FROM Medicine '
+        'WHERE removed = true '
+        'AND medID NOT IN (SELECT medID FROM Intake);',
+      );
+      await txn.rawDelete('DELETE FROM Timestamps '
+        'WHERE entryID NOT IN (SELECT entryID FROM Intake)'
+        'AND entryID NOT IN (SELECT entryID FROM Systolic) '
+        'AND entryID NOT IN (SELECT entryID FROM Diastolic) '
+        'AND entryID NOT IN (SELECT entryID FROM Pulse) '
+        'AND entryID NOT IN (SELECT entryID FROM Notes);',
+      );
+    });
+
+    // TODO: test
   }
 
   /// Closes the database.
   Future<void> close() => _db.close();
-
-  // TODO: perform cleanup of:
-  // - medicines that are marked as deleted and have no referencing intakes
-  // - cleanup of timestamp entries used in no table
 }

@@ -1,3 +1,5 @@
+import 'dart:async';
+
 import 'package:health_data_store/src/database_manager.dart';
 import 'package:health_data_store/src/repositories/blood_pressure_repository.dart';
 import 'package:health_data_store/src/repositories/blood_pressure_repository_impl.dart';
@@ -23,9 +25,15 @@ class HealthDataStore {
   ///
   /// [db] must be exclusive to the package and will be initialized by it. The
   /// library maintains the version and is responsible for update operations.
+  ///
+  /// After loading the database a cleanup of unused data is performed which may
+  /// decrease database performance in the first milliseconds after being
+  /// returned. This is done to improve performance while interacting with the
+  /// database.
   static Future<HealthDataStore?> load(Database db) async {
     if (!db.isOpen) return null;
     final mngr = await DatabaseManager.load(db);
+    unawaited(mngr.performCleanup());
     return HealthDataStore._create(mngr);
   }
 

@@ -155,6 +155,70 @@ void main() {
     expect(data.first.keys, hasLength(3));
     expect(data.first['color'], equals(0xFF990098));
   });
+  test('should cleanup unused timestamps', () async {
+    final db = await mockDBManager();
+    addTearDown(db.close);
+
+    await db.db.insert('Timestamps', {
+      'entryID': 1,
+      'timestampUnixS': DateTime.now().secondsSinceEpoch,
+    });
+    expect(await db.db.query('Timestamps'), hasLength(1));
+    await db.performCleanup();
+    expect(await db.db.query('Timestamps'), isEmpty);
+  });
+  test('should cleanup deleted medicines', () async {
+    final db = await mockDBManager();
+    addTearDown(db.close);
+
+    await db.db.insert('Medicine', {
+      'medID': 1,
+      'designation': 'test',
+      'defaultDose': 42,
+      'removed': 1,
+    });
+    await db.db.insert('Medicine', {
+      'medID': 2,
+      'designation': 'test2',
+      'removed': 1,
+    });
+    await db.db.insert('Intake', {
+      'entryID': 2,
+      'medID': 2,
+      'dosis': 1,
+    });
+
+
+    expect(await db.db.query('Medicine'), hasLength(2));
+    await db.performCleanup();
+    final data = await db.db.query('Medicine');
+    expect(data, hasLength(1));
+    expect(data, contains(isA<Map>().having((p0) => p0['medID'], 'medID', 2)));
+  });
+  test('cleanup should keep used timestamps', () async {
+    final db = await mockDBManager();
+    addTearDown(db.close);
+
+    for (int i = 1; i <= 6; i += 1) {
+      await db.db.insert('Timestamps', {
+        'entryID': i,
+        'timestampUnixS': i,
+      });
+    }
+    await db.db.insert('Intake', {
+      'entryID': 1,
+      'medID': 0,
+      'dosis': 0,
+    });
+    await db.db.insert('Systolic', {'entryID': 2,});
+    await db.db.insert('Diastolic', {'entryID': 3,});
+    await db.db.insert('Pulse', {'entryID': 4,});
+    await db.db.insert('Notes', {'entryID': 5,});
+
+    expect(await db.db.query('Timestamps'), hasLength(6));
+    await db.performCleanup();
+    expect(await db.db.query('Timestamps'), hasLength(5)); // remove 6 keep rest
+  });
 }
 
 Future<DatabaseManager> mockDBManager() async => DatabaseManager.load(

@@ -22,7 +22,7 @@ void main() {
   expect(() => store!.bpRepo, returnsNormally);
   expect(() => store!.noteRepo, returnsNormally);
  });
- test('constructed repos should wor', () async {
+ test('constructed repos should work', () async {
   final store = await HealthDataStore.load(
       await openDatabase(inMemoryDatabasePath));
   expect(store, isNotNull);