How to store only new value to sqflite data table in (Flutter)

Question

I have stored my phones call list into a data table. I want to store only new call list data into this data table. It means, only new data will be saved and existing data will be skipped. Please tell me with example. Here is my code:

this is the Database Helper database_helper.dart

import 'dart:io';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';

class DatabaseHelper {
  static final _databaseName = "MyDatabase.db";
  static final _databaseVersion = 1;

  static final table = 'my_table';

  static final columnId = '_id';
  static final columnName = 'name';
  static final columnNumber = 'number';
  static final columnType = 'type';
  static final columnDate = 'date';
  static final columnDuration = 'duration';

  // make this a singleton class
  DatabaseHelper._privateConstructor();
  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

  // only have a single app-wide reference to the database
  static Database _database;
  Future<Database> get database async {
    if (_database != null) return _database;
    // lazily instantiate the db the first time it is accessed
    _database = await _initDatabase();
    return _database;
  }

  // this opens the database (and creates it if it doesn't exist)
  _initDatabase() async {
    Directory documentsDirectory = await getExternalStorageDirectory();
    String path = join(documentsDirectory.path, _databaseName);
    await deleteDatabase(path);
    return await openDatabase(path,
        version: _databaseVersion, onCreate: _onCreate);
  }

  // SQL code to create the database table
  Future _onCreate(Database db, int version) async {
    await db.execute('''
          CREATE TABLE $table (
            $columnId INTEGER PRIMARY KEY,
            $columnName TEXT,
            $columnNumber INTEGER,
            $columnType TEXT,
            $columnDate DATETIME,
            $columnDuration INTEGER
          )
          ''');
  }

  // Helper methods

  // Inserts a row in the database where each key in the Map is a column name
  // and the value is the column value. The return value is the id of the
  // inserted row.
  Future<int> insert(Map<String, dynamic> row, {ConflictAlgorithm conflictAlgorithm = ConflictAlgorithm.replace}) async {
    Database db = await instance.database;
    return await db.insert(table, row, conflictAlgorithm: conflictAlgorithm);
  }

  // All of the rows are returned as a list of maps, where each map is
  // a key-value list of columns.
  Future<List<Map<String, dynamic>>> queryAllRows() async {
    Database db = await instance.database;
    return await db.query(table);
  }

  // All of the methods (insert, query, update, delete) can also be done using
  // raw SQL commands. This method uses a raw query to give the row count.
  Future<int> queryRowCount() async {
    Database db = await instance.database;
    return Sqflite.firstIntValue(
        await db.rawQuery('SELECT COUNT(*) FROM $table'));
  }
  
  // We are assuming here that the id column in the map is set. The other
  // column values will be used to update the row.
  Future<int> update(Map<String, dynamic> row) async {
    Database db = await instance.database;
    int id = row[columnId];
    return await db.update(table, row, where: '$columnId = ?', whereArgs: [id]);
  }

  // Deletes the row specified by the id. The number of affected rows is
  // returned. This should be 1 as long as the row exists.
  Future<int> delete(int id) async {
    Database db = await instance.database;
    return await db.delete(table, where: '$columnId = ?', whereArgs: [id]);
  }
}

This is the main file. I have added here only the Database insertion method. home.dart

...
Future callLogDB() async {
    Iterable<CallLogEntry> cLog = await CallLog.get();
    final dbHelper = DatabaseHelper.instance;

    cLog.forEach((log) async {
      // row to insert
      Map<String, dynamic> row = {
        DatabaseHelper.columnName: '${log.name}',
        DatabaseHelper.columnNumber: '${log.number}',
        DatabaseHelper.columnType: '${log.callType}',
        DatabaseHelper.columnDate:
            '${DateTime.fromMillisecondsSinceEpoch(log.timestamp)}',
        DatabaseHelper.columnDuration: '${log.duration}'
      };
      await dbHelper.insert(row, conflictAlgorithm: ConflictAlgorithm.replace);
      print('CallLog:: $row');
    });
    return cLog;
  }
...

What's the problem with my code?

Answer 1

WHAT ABOUT Read Data from Sqflite and Show in datatable?

Answer 2

there are several ways to do this, and the one I will offer are not the best or the nicest ones, but hope that they will help

1) Simply write all your data to table

You can just insert all your data to the table setting the ConflictAlgorithm to replace or ignore

db.insert(table, data, conflictAlgorithm: ConflictAlgorithm.replace);

This will replace/ignore same entries

2) Query, compare, replace

This is a less 'elegant' solution, you can first query all your data from table

db.query(table, columns: availableColumns, where: 'columnToQueryBy = ?', whereArgs: [neededValue]);

Then compare to the data you have

Then write using db.insert() as above

I think that in your case the first option suits better, this example pretty much covers most things that might help you

Hope it helps!