Flutter (dart) Delete by Sqflite

Question

I need help :(

I'm trying to delete Data from a Sqflite Database. I can insert and get the data to show, but I cannot delete it if I do a mistake in saving it.

I have my DB:

import 'package:sqflite/sqflite.dart' as sql;
import 'package:path/path.dart' as path;
import 'package:sqflite/sqflite.dart';

//SQFLITE
class DBHelper {
  static Future<sql.Database> database() async {
    final dbPath = await sql.getDatabasesPath();

    //einmal die Datenbank erstellen in einer Variablen
    return sql.openDatabase(path.join(dbPath, 'wanderwege.db'),
        onCreate: (db, version) {
      return db.execute(
          'CREATE TABLE user_places(id TEXT PRIMARY KEY, title TEXT, image TEXT, createTime TEXT, place TEXT, description TEXT)');
      // 'CREATE TABLE user_places(id TEXT PRIMARY KEY, title TEXT, image TEXT, loc_lat REAL, loc_lng REAL, address TEXT)');
    }, version: 1);
  }

//Da der Eintrag dauern kann bis es in die Daten gespeichert weerden = Future + async
  static Future<void> insert(String table, Map<String, Object> data) async {
    final db = await DBHelper.database();

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

  // Delete data from table
 // deleteData(table, itemId) async {
   // final db = await DBHelper.database();
   // return await db.rawDelete("DELETE FROM $table WHERE id = $itemId");
 // }

  //Methode um Einträge zu holen
  static Future<List<Map<String, dynamic>>> getData(String table) async {
    final db = await DBHelper.database();
    return db.query(table);
  }
}

Then I have my Places:

import 'dart:io';

import 'package:flutter/widgets.dart';
import 'package:places/helpers/db_helper.dart';

import 'package:places/models/place.dart';
import 'package:intl/intl.dart';

class GreatPlaces with ChangeNotifier {
  //To set Creation Time
  static DateTime actualTime = DateTime.now();
  String formattedDate = DateFormat('dd-MM-yyyy').format(actualTime);

  List<Place> _items = [];

  List<Place> get items {
    return [..._items];
  }

  //Information des gesamten places
  Place findById(String id) {
    return items.firstWhere((place) => place.id == id);
  }

  Future<void> addPlace(
    String pickedTitle,
    File pickedImage,
    String pickedDate,
    String pickedLocation,
    String pickedDescription,
    //PlaceLocation pickedLocation,
  ) async {
    //final address = await LocationHelper.getPlaceAddress(
    //  pickedLocation.latitude, pickedLocation.longitude);
    //   final updatedLocation = PlaceLocation(
    //    latitude: pickedLocation.latitude,
    //   longitude: pickedLocation.longitude,
    //    address: address);
    final newPlace = Place(
      id: DateTime.now().toString(),
      image: pickedImage,
      title: pickedTitle,
      createTime: pickedDate,
      place: pickedLocation,
      description: pickedDescription,
    );
    //location: null);
    _items.add(newPlace);
    notifyListeners();
    //übergabe 'wanderwege' so wie in db_helper definiert , Data ist von typ map
    DBHelper.insert('user_places', {
      'id': newPlace.id,
      'title': newPlace.title,
      'image': newPlace.image.path,
      'createTime': newPlace.createTime,
      'place': newPlace.place,
      'description': newPlace.description
      // 'loc_lat': newPlace.location.latitude,
      //  'loc_lng': newPlace.location.longitude,
      //  'address': newPlace.location.address,
    });
  }

  //Die ganzen places aus der DB holen
  Future<void> fetchAndSetPlaces() async {
    final dataList = await DBHelper.getData('user_places');
    _items = dataList
        .map((item) => Place(
              id: item['id'],
              title: item['title'],
              image: File(item['image']),
              createTime: item['createTime'],
              place: item['place'],
              description: item['description'],
              //location: null // latitude: item['loc_lat'],
              // longitude: item['loc_lat'],
              // address: item['address']),
            ))
        .toList();
    notifyListeners();
  }
}

and now on the screen where all the Places listed I want to implement a delete function, but I don't get how it works... tried so much from youtube and the documentation from Sqflite, but I don't get it.

I hope someone can help me.

Greetings :)

Answer 1

  String q = "DELETE FROM Test WHERE  id= '$id'";

Answer 2

Just use this function with parameter as id

 Future<int> delete(int id) async {
return await db.delete(tableName, where: '$columnId = ?', whereArgs: [id]);}

Answer 3

Just use this:

await db.rawDelete('DELETE FROM $table WHERE id = ?',[itemId],);

Answer 4

I've made this simple helper file (like you have done) for a little app that uses sqflite as backend in local. It's complete: it accesses to the local file system to get grant permissions, it opens the file for write , and you can make also Batch updates:

import 'dart:async';

import 'package:sqflite/sqflite.dart';

class DBClient  {
  final logger = Log.getLogger('\u{1F5AB} DBClient '); // a little logger utility, you can substitute with the simple 'print(...)' function.
  static final String dbFilename = 'my_database.db';
  final Permission permission;
  Database _database;
  bool openingDB = false;

  DBClient(this.permission) : assert(permission != null);

  Batch batch() {
    return _database.batch();
  }

  Future<void> init() async {
    if (_database == null && !openingDB) {
      String path;
      try {
        path = await permission.getAbsoluteFileName(dbFilename);
      } on Error {
        logger.e('Error: cannot open db');
        rethrow;
      }
      openingDB = true;
      logger.d('Opening database on path: $path');

      _database = await openDatabase(path, version: 1,
          onCreate: (Database db, int version) async {

        await db.execute("""
                CREATE TABLE IF NOT EXISTS  ...... 
          """);
        await db.execute("""
      CREATE TABLE IF NOT EXISTS .... 
        """);
        ....
        await db.execute("""
      CREATE TABLE IF NOT EXISTS .... 
        """);
      });
    }
  }

  Future<int> insert(String tableName, Map<String, dynamic> json) async =>
      _database.insert(tableName, json);

  Future<List<Map<String, dynamic>>> query(
    String tableName, {
    String where,
    List<dynamic> whereArgs,
  }) async {
    return _database.query(
      tableName,
      where: where,
      whereArgs: whereArgs,
    );
  }

  Future<int> update(String tableName, Map<String, dynamic> json,
      {String where, List<dynamic> whereArgs}) async {
    return _database.update(
      tableName,
      json,
      where: where,
      whereArgs: whereArgs,
    );
  }

  Future<int> delete(String tableName,
      {String where, List<dynamic> whereArgs}) async {
    return _database.delete(
      tableName,
      where: where,
      whereArgs: whereArgs,
    );
  }
}

You can use in this manner:

  deleted = await db.delete(Place.tableName(),
      where: "name = ?", whereArgs: [name]);

Hint: in the init() method you can use AsyncMemoizer class, it is better to grant that the method is called only once (at the time I wrote the app I didn't do it....)

Hereafter my (very old) dependencies:

dependencies:
  date_utils: ^0.1.0+3
  equatable: ^1.1.1
  logger: ^0.8.3
  permission_handler: ^5.0.0+hotfix.3
  quiver: ^2.1.3
  sqflite: ^1.3.0
  path_provider: ^1.6.5
  flutter:
    sdk: flutter