How to convert Boolean Value to Int value using Sqlite in Flutter?

Question

Using flutter I have this model

  class Task {
  String color;
  String env;
  String id;
  String name;
  String description;
  bool isBuyIt;
  bool isOnBacklog;
 }

I am using SwitchListTile in order to change the boolean value of isBuyIt and isOnBacklog

SwitchListTile(
              activeColor: HexColor(task.color),
              title: Text("Do you have it?"),
              value: task.isBuyIt,
              onChanged: (bool value) {
                setState(() {
                  task.isBuyIt = value;
                });
              },
              secondary: IconButton(
                icon: Icon(Icons.shopping_cart),
                onPressed: null,
                color: HexColor(task.color),
              ),
            ) 

I am using sqflite: ^1.3.0 and as you know it does not support bool value. I made the table like this way:

  Future _onCreate(Database db, int version) async {
    await db.execute('''
          CREATE TABLE $table (
            $columnId TEXT PRIMARY KEY,
            $columnName TEXT NOT NULL,
            $columnColor TEXT NOT NULL,
            $columnEnv TEXT NOT NULL,
            $columnDescription TEXT NOT NULL,
            $columnisBuyIt INTEGER NOT NULL,
            $columnisOnBacklog INTEGER NOT NULL
          )
          ''');
  }

But I dont know how to convert Boolean value into Integer value. I dont want to change the model fields to Integer because SwitchListTiledoesnt works with INT value

I guess Check Constraint would work.

Solution 1

Starting from SQLite 3.23.0 literal true/false are recognized and could be used.

Recognize TRUE and FALSE as constants. (For compatibility, if there exist columns named "true" or "false", then the identifiers refer to the columns rather than Boolean constants.)

CREATE TABLE a (id INT, i BOOLEAN);
                                  
INSERT INTO a(id,i) VALUES(10,true);
INSERT INTO a(id,i) VALUES(20, false);

SELECT * FROM a;

db-fiddle.com

Solution 2

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true). i know three way that i know to do that here is an example

if You want to convert boolean to int you can say .

int flag = (boolValue)? 1 : 0;

and if you want to convert it back to bool you should say

Boolean flag2 = (flag == 1)? true : false;

in another way Use the following ALTER statement -

ALTER TABLE CREATE_DB_TABLE ADD status boolean NOT NULL default 0;

Then you can use Cursor to get the required value and then convert to actual boolean -

flag=cursor.getString(0).equals("1")

or third one is which is similar to one above is

Boolean flag = (cursor.getInt(cursor.getColumnIndex("flag")) == 1);

Comments

[TuGordoBello]

I was considered that solution as a last solution. I don't want to add validations like this and get dirty the code

[TuGordoBello]

I just changed $columnisBuyIt INTEGER NOT NULL, to $columnisBuyIt BOOLEAN NOT NULL, and it worked

[Lukasz Szozda]

@TuGordoBello Glad to hear I could help :)