I am a completely a beginner to sqlite and flutter. I was trying to create a local database to my flutter to do app. So I watched some youtube videos and started to implementing a database using flutter sqflite plugin. Everything worked fine because all I did was copy typing the you tubers code, until I had to add an extra parameter to the code which is a boolean, in order to track the status of the task (like done the task or not). I used an int value to save the bool, while sqlite does not supports boolean values. I used two functions, one to update the text and the other to update the switch value.

And secondly when I tap on a switch it triggers all the switches in the list. I want to solve that issue as well.

Model class for the Task

class Tasksdb {
  final int? id;
  final String taskName;
  bool isDone;

  Tasksdb({
    this.id,
    required this.taskName,
    required this.isDone,
  });

  factory Tasksdb.fromMap(Map<String, dynamic> json) => Tasksdb(
      id: json['id'],
      taskName: json['taskName'],
      isDone: (json['isDone'] as int) == 0 ? false : true);

  Map<String, dynamic> toMap() {
    return {
      'id': id,
      'taskName': taskName,
      'isDone': isDone,
    };
  }
}  

DatabaseHelper class

 class DatabaseHelper {
  DatabaseHelper._privateConstructor();
  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

  static Database? _database;
  Future<Database> get database async => _database ??= await _initDatabase();

  Future<Database> _initDatabase() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, 'tasks.db');
    return await openDatabase(
      path,
      version: 1,
      onCreate: _onCreate,
    );
  }

  Future _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE IF NOT EXISTS "taskstable" (
    "id"    INTEGER,
    "taskName"  TEXT,
    "isDone"    INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY("id" AUTOINCREMENT)
);
      ''');
  }

  Future<List<Tasksdb>> getTasks() async {
    Database db = await instance.database;
    var tasksQuery = await db.query(
      'taskstable',
    );
    List<Tasksdb> taskList = tasksQuery.isNotEmpty
        ? tasksQuery.map((c) => Tasksdb.fromMap(c)).toList()
        : [];
    return taskList;
  }

  Future<int> add(Tasksdb task) async {
    Database db = await instance.database;
    return await db.insert('taskstable', {
      'id': task.id,
      'taskName': task.taskName,
      'isDone': 0,
    });
  }


  Future<int> update(Tasksdb task) async {
    Database db = await instance.database;
    return await db.update(
      'taskstable',
      task.toMap(),
      where: "id = ?",
      whereArgs: [task.id],
    );
  }

  Future<int> updateIsDone(bool isDoneTodb) async {
    Database db = await instance.database;
    return await db.update(
      'taskstable',
      {
        'isDone': isDoneTodb == true ? 1 : 0,
      },
    );
  }
}

HomeScreen widget

class SqliteApp extends StatefulWidget {
  const SqliteApp({Key? key}) : super(key: key);

  @override
  _SqliteAppState createState() => _SqliteAppState();
}

class _SqliteAppState extends State<SqliteApp> {
  int? selectedId;
  final textController = TextEditingController();
  bool isDone = false;

  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      home: Scaffold(
        appBar: AppBar(
          title: TextField(
            controller: textController,
          ),
        ),
        body: Center(
          child: FutureBuilder<List<Tasksdb>>(
              future: DatabaseHelper.instance.getTasks(),
              builder: (BuildContext context,
                  AsyncSnapshot<List<Tasksdb>> snapshot) {
                if (!snapshot.hasData) {
                  return const Center(child: Text('Loading...'));
                }
                return snapshot.data!.isEmpty
                    ? const Center(child: Text('No tasks in List.'))
                    : ListView(
                        children: snapshot.data!.map((task) {
                          return Center(
                            child: Card(
                              color: selectedId == task.id
                                  ? Colors.green
                                  : Colors.yellow,
                              child: ListTile(
                                trailing: Switch(   //the problem is here, doesn't save to db
                                    value: isDone,
                                    onChanged: (val) async {
                                      setState(() {
                                        isDone = val;
                                      });
                                      await DatabaseHelper.instance
                                          .updateIsDone(
                                        isDone,
                                      );
                                    }),
                                title: Text(task.taskName),
                                onTap: () {
                                  setState(() {
                                    if (selectedId == null) {
                                      textController.text = task.taskName;
                                      selectedId = task.id;
                                    } else {
                                      textController.text = 'add something';
                                      selectedId = null;
                                    }
                                  });
                                },
                              ),
                            ),
                          );
                        }).toList(),
                      );
              }),
        ),
        floatingActionButton: FloatingActionButton(
          onPressed: () async {
            await DatabaseHelper.instance.add(
              Tasksdb(
                taskName: textController.text,
                isDone: false,
              ),
            );
            setState(() {
              textController.clear();
              selectedId = null;
            });
          },
        ),
      ),
    );
  }
}

CodePudding user response：

I found the answer: (in case of if someone had the same question) I removed the bool isDone from the material app widget, and instead of assigning the switch val to that bool I assigned it to database's task.isDone value. To avoid switch's auto trigger, I parsed the Taskdb to the updateIsDone function

Future<int> updateIsDone(Tasksdb task, bool isDoneTodb) async {
    Database db = await instance.database;
    return await db.update(
        'taskstable',
        {
          'isDone': isDoneTodb == true ? 1 : 0,
        },
        where: "id = ?",
        whereArgs: [task.id]);
  }

...

Switch(
     value: task.isDone,
     onChanged: (val) async {
            setState(() {
                 task.isDone = val;
            });
     await DatabaseHelper.instance
     .updateIsDone(task, task.isDone);
       });