main
1import 'package:sqflite_common/sqlite_api.dart';
2
3/// Manager for the database.
4///
5/// Responsible for setting up the database and performing schema and version
6/// updates.
7///
8/// ## DB scheme
9///
10/// 
11///
12/// ## Types
13/// Data in the database tries to always use the most common SI-units.
14/// Exceptions must be documented here.
15/// - Timestamps are in seconds since unix epoch
16/// - Color are integers in format 0xRRGGBB
17/// - Pressure is in *kPa*
18/// - Pulse is in bpm
19/// - Weight is in kg
20/// - Length is in meter
21/// - Temperature is in kelvin
22class DatabaseManager {
23 DatabaseManager._create(this._db);
24
25 /// Initialize the manager from a database.
26 ///
27 /// If [db] doesn't contain a scheme or contains an outdated scheme, one will
28 /// be created.
29 static Future<DatabaseManager> load(Database db,
30 [bool isReadOnly = false]) async {
31 final dbMngr = DatabaseManager._create(db);
32
33 final tables = await dbMngr._db.query('"main".sqlite_master');
34 if (tables.length <= 3 && !isReadOnly) {
35 // DB has just been created, no version yet.
36 await dbMngr._db.setVersion(2);
37 }
38
39 if (!isReadOnly && await dbMngr._db.getVersion() < 3) {
40 await dbMngr._setUpTables();
41 await dbMngr._db.setVersion(4);
42 } else if (!isReadOnly && await dbMngr._db.getVersion() == 3) {
43 await dbMngr._setupWeightTable(dbMngr._db);
44 await dbMngr._db.setVersion(4);
45 }
46 // When updating the schema the update steps are maintained for ensured
47 // compatability.
48 // TODO: develop strategy for loading older db versions as read only.
49 return dbMngr;
50 }
51
52 final Database _db;
53
54 /// Get the database.
55 Database get db => _db.database;
56
57 Future<void> _setUpTables() => _db.transaction((txn) async {
58 await txn.execute('CREATE TABLE "Medicine" ('
59 '"medID" INTEGER NOT NULL UNIQUE,'
60 '"designation" TEXT NOT NULL,'
61 '"defaultDose" REAL,'
62 '"color" INTEGER,'
63 '"removed" BOOLEAN,'
64 'PRIMARY KEY("medID")'
65 ');');
66 await txn.execute('CREATE TABLE "Timestamps" ('
67 '"entryID" INTEGER NOT NULL UNIQUE,'
68 '"timestampUnixS" INTEGER NOT NULL,'
69 'PRIMARY KEY("entryID")'
70 ');');
71 await txn.execute('CREATE TABLE "Intake" ('
72 '"entryID" INTEGER NOT NULL,'
73 '"medID" INTEGER NOT NULL,'
74 '"dosis" REAL NOT NULL,'
75 'PRIMARY KEY("entryID"),'
76 'FOREIGN KEY("entryID") REFERENCES "Timestamps"("entryID"),'
77 'FOREIGN KEY("medID") REFERENCES "Medicine"("medID")'
78 ');');
79 for (final info in [
80 ('Systolic', 'sys'),
81 ('Diastolic', 'dia'),
82 // Pulse is stored as a double because bpm could be measured over
83 // non one-minute intervalls which might be necessary to support in the
84 // future.
85 ('Pulse', 'pul'),
86 ]) {
87 await txn.execute('CREATE TABLE "${info.$1}" ('
88 '"entryID" INTEGER NOT NULL,'
89 '"${info.$2}" REAL,'
90 'FOREIGN KEY("entryID") REFERENCES "Timestamps"("entryID"),'
91 'PRIMARY KEY("entryID")'
92 ');');
93 }
94 await txn.execute('CREATE TABLE "Notes" ('
95 '"entryID" INTEGER NOT NULL,'
96 '"note" TEXT,'
97 // When implementing attachments instead of updating this scheme note text
98 // can be interpreted as markdown and support formatting as well as files.
99 '"color" INTEGER,'
100 'FOREIGN KEY("entryID") REFERENCES "Timestamps"("entryID"),'
101 'PRIMARY KEY("entryID")'
102 ');');
103 await _setupWeightTable(txn);
104 });
105
106 Future<void> _setupWeightTable(DatabaseExecutor executor) async {
107 await executor.execute('CREATE TABLE "Weight" ('
108 '"entryID" INTEGER NOT NULL,'
109 '"weightKg" REAL NOT NULL,'
110 'FOREIGN KEY("entryID") REFERENCES "Timestamps"("entryID"),'
111 'PRIMARY KEY("entryID")'
112 ');');
113 }
114
115 /// Removes unused and deleted entries rows.
116 ///
117 /// Specifically:
118 /// - medicines that are marked as deleted and have no referencing intakes
119 /// - timestamp entries that have no
120 Future<void> performCleanup() => _db.transaction((txn) async {
121 // Remove medicines marked deleted with no remaining entries
122 await txn.rawDelete(
123 'DELETE FROM Medicine '
124 'WHERE removed = 1 '
125 'AND medID NOT IN (SELECT medID FROM Intake);',
126 );
127 // Remove unused entry ids
128 await txn.rawDelete(
129 'DELETE FROM Timestamps '
130 'WHERE entryID NOT IN (SELECT entryID FROM Intake)'
131 'AND entryID NOT IN (SELECT entryID FROM Systolic) '
132 'AND entryID NOT IN (SELECT entryID FROM Diastolic) '
133 'AND entryID NOT IN (SELECT entryID FROM Pulse) '
134 'AND entryID NOT IN (SELECT entryID FROM Weight) '
135 'AND entryID NOT IN (SELECT entryID FROM Notes);',
136 );
137 });
138
139 /// Closes the database.
140 Future<void> close() => _db.close();
141}