main_database_handler.dart 5.64 KB
Newer Older
1 2 3 4

import 'package:logair_application/logic/data_packet.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
5
import 'package:tuple/tuple.dart';
6

7
/// Handler that integrates a mysqli database into the application's workflow.
8 9
class MainDatabaseHandler {
  factory MainDatabaseHandler() => _singleton;
10

11
  MainDatabaseHandler._internal();
12

13
  static final MainDatabaseHandler _singleton = new MainDatabaseHandler._internal();
14 15

  /// Reference to the internalized [Database] within this instance.
16
  Future<Database> _maindb;
17

18
  /// [Database] initialization string, to reduce repetition.
19
  final String _createMainDBString = '''CREATE TABLE data(
20
    id INTEGER PRIMARY KEY AUTOINCREMENT,
21
    device_id TEXT, url TEXT,
22
    timestamp_nix INTEGER,
23 24 25 26
    latitude REAL, longitude REAL,
    altitude INTEGER, speed REAL, heading INTEGER,
    temperature FLOAT, relative_humidity FLOAT, pressure FLOAT,
    pm_1 FLOAT, pm_2_5 FLOAT, pm_4 FLOAT, pm_10 FLOAT,
27 28
    extra TEXT,
    exported TINYINT DEFAULT 0
29
  )''';
30

31
  /// Retrieves the internal [Database] instance, and if not instantiated, instantiates it.
32 33 34
  Future<Database> get _mainDatabase async {
    if (_maindb == null) 
      this._maindb = openDatabase(
35
        join((await getDatabasesPath()), 'logair_data.db'),
36 37
        version: 3,
        onCreate: (db, version) => db.execute(this._createMainDBString)
38
      );
39
    return this._maindb;
40 41
  }

42
  /// Purges the [Database] of all internal data.
Nicolas Richard Walter Boeckh's avatar
Nicolas Richard Walter Boeckh committed
43
  Future<void> recreateDB() async {
44
    final Database database = await _mainDatabase;
Nicolas Richard Walter Boeckh's avatar
Nicolas Richard Walter Boeckh committed
45 46

    await database.rawQuery('DROP TABLE IF EXISTS data');
47
    await database.rawQuery(this._createMainDBString);
Nicolas Richard Walter Boeckh's avatar
Nicolas Richard Walter Boeckh committed
48 49
  }

50
  /// Inserts data into the [Database].
Nicolas Richard Walter Boeckh's avatar
Nicolas Richard Walter Boeckh committed
51
  Future<void> insertData(DataPacket packet) async {
52
    final Database database = await this._mainDatabase;
53
    Map<String, dynamic> insert = packet.toMap();
54 55
    await database.insert(
      'data',
56
      insert,
57 58 59
      conflictAlgorithm: ConflictAlgorithm.replace
    );
  }
60

61
  /// Retrieves all of the [DataPacket]s inserted into the [Database].
62
  Future<List<DataPacket>> get data async {
63
    final Database database = await this._mainDatabase;
64 65 66

    final List<Map<String, dynamic>> data = await database.query('data');

67
    return List.generate(data.length, (i) => DataPacket.fromMappedData(data[i]));
68 69
  }

70
  /// Closes the [Database] instance.
71
  Future<void> close() async => (await this._mainDatabase).close();
72

73
  /// Streams the amount of elements contained within the [Database].
74
  Stream<int> getDBSize() async* {
75
    final Database database = await this._mainDatabase;
76
    while (true) {
77
      final List<Map<String, dynamic>> data = await database.query('data', columns: ['COUNT(*) AS count']);
78 79 80 81 82
      yield data[0]['count'] ?? 0;
      await Future.delayed(Duration(seconds: 5));
    }
  }

83
  /// Retrieves all of the unsent packets in the [DataPacket].
84
  Future<List<Tuple2<int, DataPacket>>> getUnsent(int limit) async {
85
    final Database database = await this._mainDatabase;
86 87 88 89 90 91 92 93 94 95
    final List<Map<String, dynamic>> data = await database.query('data', 
      where: 'exported = ?',
      whereArgs: [0],
      limit: limit,
      orderBy: 'timestamp_nix ASC'
    );

    return List.generate(data.length, (i) => Tuple2(data[i]['id'], DataPacket.fromMappedData(data[i])));
  }

96
  /// This flags all of the internal packets that are given as [ids] as exported to the external servers.
97
  Future<void> setExported(List<int> ids) async {
98
    final Database database = await this._mainDatabase;
99 100 101
    
    int count = await database.rawUpdate('UPDATE data SET exported = 1 WHERE id IN (${ids.join(', ')})');

102 103 104 105 106
    print('Exported $count');
  }

  /// Used to monitor the amount of unsent [DataPacket]s.
  Stream<int> getUnsentPacketsLength() async* {
107
    final Database database = await this._mainDatabase;
108
    while (true) {
109 110 111 112 113
      final List<Map<String, dynamic>> data = await database.query('data',
        columns: ['COUNT(*) AS count'],
        where: 'exported = ?',
        whereArgs: [0]
      );
114 115 116 117 118
      yield data[0]['count'] ?? 0;
      await Future.delayed(Duration(seconds: 1));
    }
  }

119
  /// Retrieves the latest 5 seconds of data every 5 seconds.
120
  Stream<Map<String, dynamic>> getLatest() async* {
121
    final Database database = await _mainDatabase;
122
    while (true) {
123
      final List<Map<String, dynamic>> data = await database.rawQuery('SELECT MIN(timestamp_nix) AS start, AVG(pm_1) AS pm_1, AVG(pm_2_5) AS pm_2_5, AVG(pm_4) AS pm_4, AVG(pm_10) AS pm_10 FROM (SELECT * FROM data WHERE timestamp_nix >= ${DateTime.now().millisecondsSinceEpoch - 10000} ORDER BY id DESC LIMIT 5)');
124 125 126 127
      yield data[0] ?? Map();
      await Future.delayed(Duration(seconds: 5));
    }
  }
128

129
  /// Retrieves the latest [maxResults] packets inserted into the [Database].
130
  Future<List<DataPacket>> getLastPackets(int maxResults) async {
131
    final Database database = await this._mainDatabase;
132 133 134 135 136

    final List<Map<String, dynamic>> data = await database.rawQuery('SELECT * FROM data ORDER BY id DESC LIMIT $maxResults;');

    return List.generate(data.length, (i) => DataPacket.fromMappedData(data[i]));
  }
Nicolas Richard Walter Boeckh's avatar
Nicolas Richard Walter Boeckh committed
137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152

  /// Retrieves the latest packet inserted into the [Database].
  Stream<DataPacket> getLatestData() async* {
    final Database database = await _mainDatabase;
    while (true) {
      final List<Map<String, dynamic>> data = await database.rawQuery('SELECT * FROM data ORDER BY timestamp_nix DESC LIMIT 1');
      DataPacket packet = (data.length > 0) ? DataPacket.fromMappedData(data[0]) : null;

      // Ignore packets older than 5 seconds.
      if (packet != null && packet.timestamp + 5000 >= DateTime.now().millisecondsSinceEpoch) {
        yield packet;
      } else 
        yield null;
      await Future.delayed(Duration(seconds: 1));
    }
  }
153
}