How to add new table to sqlite?

Question

I need to add a new column name id INTEGER AUTOINCREMENT and a new table for the current database for my existing table. How to use 'onUpgrade'? Is it need to change the version number?

initDb() async {
    io.Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, "HelperDatabase.db");
    var theDb = await openDatabase(path, version: 1, onCreate: _onCreate, onUpgrade: _onUpgrade);
    return theDb;
  }

How to use _onUpgrade

void _onUpgrade(Database db, int oldVersion, int newVersion)async{

  }

Is it need to add column also?

void _onCreate(Database db, int version) async {

    await db.execute(
        """CREATE TABLE AssetAssemblyTable(e INTEGER, a INTEGER, c INTEGER)""");

Photogene

Answer 1

To update your DB from old version, you should change version to 2. You should change onCreate and onUpdate like below.

// This is called for new users who have no old db
void _onCreate(Database db, int version) async {

  // if `AssetAssemblyTable` has a new column in version 2, add the column here.
  await db.execute(
    """CREATE TABLE AssetAssemblyTable(e INTEGER, a INTEGER, c INTEGER)""");
  )
  await db.execute("CREATE TABLE NewTable...") // create new Table
}

// This is called for existing users who have old db(version 1) 
void _onUpgrade(Database db, int oldVersion, int newVersion)async{
  // In this case, oldVersion is 1, newVersion is 2
  if (oldVersion == 1) {
      await db.execute("ALTER TABLE AssetAssemblyTable...") // add new column to existing table.
      await db.execute("CREATE TABLE NewTable...") // create new Table
  }
}

more example is below

https://github.com/tekartik/sqflite/blob/master/sqflite/doc/migration_example.md

Demarche

is it needs to increase _onCreate() method version number whenAlter table? – Photogene

You don't have to do something with version variable in _onCreate. I guess that it is for debugging purpose. – Demarche

Errror Message: Unhandled Exception: DatabaseException(table UsernameTable has no column named rememberMe (Sqlite code 1): , while compiling: INSERT OR REPLACE INTO UsernameTable (username, rememberMe) VALUES (?, ?), (OS error - 2:No such file or directory)) sql 'INSERT OR REPLACE INTO UsernameTable (username, rememberMe) VALUES (?, ?)' args [term@melfs, 1]} – Photogene

initDb() async { io.Directory documentsDirectory = await getApplicationDocumentsDirectory(); String path = join(documentsDirectory.path, "HelperDatabase.db"); var theDb = await openDatabase(path, version: 4, onCreate: _onCreate, onUpgrade: _onUpgrade); return theDb; } – Photogene

void _onUpgrade(Database db, int oldVersion, int newVersion)async{ if(oldVersion != 5){ await db.execute("ALTER TABLE UsernameTable ADD COLUMN rememberMe INTEGER DEFAULT 0"); } } – Photogene

can to answer this question? https://mcmap.net/q/1777534/-onupgrade-sqflite-unhandled-exception-databaseexception-table-usernametable-has-no-column-named-rememberme-sqlite-code-1/8822337 – Photogene

Answer 2

Here what I've done :

class SqliteDB {
   static final SqliteDB _instance = new SqliteDB.internal();

   factory SqliteDB() => _instance;
   static Database? _db;

   Future<Database?> get db async {
     if (_db != null) {
        return _db;
      }
     _db = await initDb();
     return _db;
   }

   SqliteDB.internal();

   /// Initialize DB
   initDb() async {
     io.Directory documentDirectory = await 
     getApplicationDocumentsDirectory();
     String path = join(documentDirectory.path, "RadiosStations.db");
     
     var taskDb = await openDatabase(
        //open the database or create a database if there isn't any path,
        version: 2, onCreate: (Database db, int version) async {

        await db.execute(
          """CREATE TABLE AssetAssemblyTable(e INTEGER, a INTEGER, c 
             INTEGER)""");
            )

        await db.execute("CREATE TABLE NewTable...") // create new Table;

        },
      onUpgrade: (Database db, int oldVersion, int newVersion)async{
        // In this case, oldVersion is 1, newVersion is 2
      if (oldVersion == 1) {
        await db.execute(
            """CREATE TABLE AssetAssemblyTable(e INTEGER, a INTEGER, c 
     INTEGER)""");
      )
      await db.execute("CREATE TABLE NewTable...") // create new Table;
      }}
);
return taskDb;}

Stelmach