DBI for Flutter, SQLFlite

By pjain      Published May 1, 2020, 12:25 a.m. in blog Programming   

Part of Series - Flutter/Dart Series

SQLite QS

Ex1

EX: Notes

  • DN http://rrtutors.com/description/8 https://github.com/rrtutors/Flutter-SQFLite-Database

  • Reg Signup Form [ Enter Name ] [ Enter email id ] [ Enter mobile number ] [ Enter password ] Already have account Sign In?

  • Login - Sign In [ Enter mobile number ] [ Enter password ] Don't have an account SignUp?

  • pubspec.yaml -> $ flutter packages get sdk: sqflite: path: path_provider: ^0.4.1

  • Model DAO or Pojo

class User{
    int id;
    String name;
    String email;
    String pasword;
    String mobile;

    User(this.name,this.email,this.pasword,this.mobile);
    MaptoUserMap(){
        return {'name':name, 'email':email, 'password':pasword, 'mobile':mobile, };
    }
    static  fromMap(Map c) {
        return User(c['name'],c['email'],c['passowrd'],c['mobile']);
    }
}

Ex MULTItable using sqflite

// LESSON after close a database set to null before reopen will reuse that old object

void _generateArchive() async { //_
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    ZipFileEncoder encoder = ZipFileEncoder();

    encoder.create(createdFileName(documentsDirectory.path, 'product.zip'));
    await DBProvider.db.closeDB();
    //await DatabaseManager.close();
    encoder.addFile(File('${documentsDirectory.path}/TestDB.db'));
    await DBProvider.db.openDB();
    //after that some of code for making the zip files
}

class DBProvider {
  DBProvider._();
  static final DBProvider db = DBProvider._();
  Database _database;
  Future<Database> get database async {
    if (_database != null) return _database;
    // if _database is null we instantiate it
    _database = await initDB();
    return _database;
  }
  initDB() async {
    final String _supplier =
        'CREATE TABLE supplier (id INTEGER PRIMARY KEY, name VARCHAR(100), phone VARCHAR(30), email VARCHAR(100), notes TEXT, photo VARCHAR(255));';
    final String _item =
        'CREATE TABLE item (id INTEGER PRIMARY KEY, supplier_id INTEGER REFERENCES supplier ON DELETE CASCADE, notes TEXT);';
    final String _item_detail =
        'CREATE TABLE item_detail (id INTEGER PRIMARY KEY, item_id INTEGER REFERENCES item ON DELETE CASCADE, photo VARCHAR(255), selected int default 0);';
    final String _state =
        'CREATE TABLE state (id INTEGER PRIMARY KEY, supplier_id INTEGER);';
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, "TestDB.db");
    return await openDatabase(path, version: 1, onOpen: (db) {},
        onConfigure: (Database db) async {
      await db.execute('PRAGMA foreign_keys=ON');
    }, onCreate: (Database db, int version) async {
      await db.execute(_supplier);
      await db.execute(_item);
      await db.execute(_state);
      await db.execute(_item_detail);
    });
  }
  closeDB() async {
    final db = await database;
    await db.close();
    _database = null;
  }
  openDB() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, "TestDB.db");
    return await openDatabase(
      path,
      version: 1,
      onOpen: (db) {},
      onConfigure: (Database db) async {
        await db.execute('PRAGMA foreign_keys=ON');
      },
    );
  }
 //xx this methods fire the exception after I called closeDB() but before it fires perfectly
  Future<int> getStateId(int id) async {
    final db = await database;
    db.delete("state");
    var tableState = await db.rawQuery("SELECT MAX(id)+1 as id FROM state");
    int id = tableState.first["id"];
    //insert to the table using the new id
    var raw = await db.rawInsert(
        "INSERT Into state (id,supplier_id)"
        " VALUES (?,?)",
        [id, id]);
    print('stateid=$raw');
    return raw;
  }
}

SQLite 101 and Conceptual

DBI with SQLite BPR

lite DBI: PERSONALIZATION, ABOUT

lite DBI: EZ Prefs, Properties

r SQLite


0 comments

There are no comments yet

Add new comment

Similar posts

List++ Advanced Application Features

Interview Questions in Flutter

NAV: URL Launcher, Deep Links

NAV: Navigation Drawers