[Flutter] sqflite SQLite Database #DB

sqflite package is possible as simple query in local device storage.

path : https://pub.dev/packages/sqflite

sqflite | Flutter Package

 

add package

flutter pub add sqflite

 

 

import

import 'package:sqflite/sqflite.dart';

 

 

create database(db)

Database db = await openDatabase("my_db.db");

@override
void dispose() {
  db?.close();
  super.dispose();
}

 

 

know database path

var databasePath = await getDatabasesPath();
debugPrint('databasePath:$databasePath');

 

 

.execute : method is not return value, because type is void.

.rawQuery : method is return value, List<Map<String, Object?>>

 

so what is .transaction function?

일반적으로 rawQuery만 사용하면 해당 쿼리를 사용하지만,

transaction함수 내에서 여러 쿼리를 한 번에 처리할 때 사용된다.

여러 쿼리 중 하나라도 실패를 하면 이전에 성공했던 쿼리도 함께 실패처리되어 안전하다.

 

create table

StringBuffer stringBuffer = StringBuffer();
stringBuffer
..write("CREATE TABLE IF NOT EXISTS Friends(")
..write("id INTEGER PRIMARY KEY,")
..write("name TEXT,")
..write("value INTEGER")
..write(");");

await database?.execute(stringBuffer.toString());

StringBuffer stringBuffer = StringBuffer();
stringBuffer
..write("CREATE TABLE IF NOT EXISTS Family(")
..write("id INTEGER PRIMARY KEY AUTOINCREMENT,")
..write("name TEXT NOT NULL,")
..write("value INT")
..write(");");

final result = await database?.rawQuery(stringBuffer.toString());
debugPrint("result:$result");

 

2024-03-13-수

: 컬럼을 추가해서 그대로 execute(query)를 하면 적용되지 않는다.

 

 

버전확인

final String databasePath = await getDatabasesPath();
final String path = [databasePath, _databaseName].join('/');
final db  = await databaseFactory.openDatabase(path);
final version = await db.getVersion();

 

 

테이블 삭제

DROP TABLE [TABLENAME]

 

 

컬럼(Column)추가

: DB를 한 번 열게되면 이후로 openDatabase를 해주었을시 동작을 하지 않게된다.

: 열고 닫은 후 다시 열 때 비로소 onUpgrade, version등을 확인할 수 있다.

E/SQLiteLog(27678): (1) near ",": syntax error in "ALTER TABLE wifi ADD COLUMN register_date TEXT NOT NULL, update_date TEXT NOT NULL;"

위의 문제가 계속 발생되었고, 왜 문제가 발생하는지 보아하니 DB가 열리지 않았던 것이다.

- onUpgrade내부에서 받는 db객체는 open된 상태가 아닌 것.

결국 쿼리 String을 매개로 받아서 나중에 execute하는 방법을 택하였다.

ALTER TABLE table1, table2, table3
ADD COLUMN new_column_name datatype NOT NULL DEFAULT 'default_value';

 

near ",": syntax error in "ALTER TABLE wifi, idpw, toilet ADD register_date DATETIME NOT NULL, update_date DATETIME NOT NULL;"

 

NOT NULL인 경우 default값을 꼭 넣어주어야 한다. NOT NULL을 빼자

 

컬럼 삭제

컬럼은 따로 삭제되지 않고, bakup테이블을 만들어서 복사친 뒤.. 그 테이블을 RENAME하는 방법이 있는데 지금 그것을 알기 매우 싫다.

ALTER TABLE table_name
DROP COLUMN column_name;

 

 

- duplicate에러가 발생해도 쿼리가 잘반영되었을까?

(1) duplicate column name: register_date in "ALTER TABLE wifi ADD register_date DATETIME;"

: 결과 → 안된다

에러코드를 적용해주어야 다음 쿼리를 진행한다. 

  await database?.transaction((txn) async {
    for (var query in queryList) {
      try {
        var result = await txn.rawQuery(query);
        debugPrint("[DatabaseHelper].. result:$result, query:$query");
      } catch (e) {
        debugPrint("[DatabaseHelper].. [ERROR]:$e, query:$query");
      }
    }
  });

 

그리고 여러개의 컬럼을 한 번에 추가할 수 없으며,

각각의 추가하고 싶은 테이블 컬럼을 한 번만 명시한다.

 

 

select

result : List<Map<String, Object?>>?

void selectDatabase() async {
  database ??= await openDatabase("my_db.db");

  final result = await database?.rawQuery("SELECT * FROM Friends");
  debugPrint("result:$result");
}

I/flutter (29559): result:[{id: 1, name: PH, value: 31}, {id: 2, name: Hyun, value: 30}, {id: 3, name: gyu tae, value: 31}, {id: 4, name: SH, value: 30}, {id: 5, name: chul zz, value: 31}, {id: 6, name: joo hyun, value: 30}]

Future<List<Map<String, Object?>>?> select() async {

 

 

어떠한 데이터에 삽입해주고 싶은 경우

  factory WifiModel.fromQuery(Map query) {
    return WifiModel(
      place: query['place'],
      name: query['name'],
      password: query['password'],
      memo: query['memo'],
    );
  }

 

 

insert

void insertDatabase() async {
  database ??= await openDatabase("my_db.db");

  final result = await database?.transaction((txn) async {
    int id1 = await txn.rawInsert(
      'INSERT INTO Friends(name, value) VALUES("PH", 31)'
    );
    debugPrint("id1:$id1");

    int id2 = await txn.rawInsert(
      'INSERT INTO Friends(name, value) VALUES(?, ?)', ['Hyun', 30]
    );
    debugPrint('id2:$id2');
  });
  debugPrint('result:$result');
}

W/SQLiteLog(29559): (28) double-quoted string literal: "PH"
I/flutter (29559): id1:1
I/flutter (29559): id2:2
I/flutter (29559): result:null

 

int id1 = await txn.rawInsert(
  "INSERT INTO Friends(name, value) VALUES('chul zz', 31)"
);
debugPrint("id1:$id1");

int id2 = await txn.rawInsert(
  'INSERT INTO Friends(name, value) VALUES(?, ?)', ['joo hyun', 30]
);

I/flutter (29559): id1:5
I/flutter (29559): id2:6
I/flutter (29559): result:null

 

update

rawQuery is not return something values.

but rawUpdate is return update count.

void updateDatabase() async {
  database ??= await openDatabase ("my_db.db");

  String updateQuery = "UPDATE Friends SET name='Ze Pil', value=32 WHERE name='Hyun';";
  dynamic result = await database?.rawQuery(updateQuery);
  debugPrint('result[1]:$result');

  result = await database?.rawUpdate("UPDATE Friends SET name=?, value=? WHERE name=?",
    ['se won', 31, 'joo hyun']
  );
  debugPrint('result[2]:$result');
}

I/flutter (29559): result[1]:[]
I/flutter (29559): result[2]:1

한 가지 사실을 알게 되었는데, rawUpdate했을 때 Where조건절이 동일한 것들 모두가 한 번에 바뀐다

 

 

delete

void deleteDatabase() async {
  database ??= await openDatabase("my_db.db");

  dynamic result = await database?.rawQuery("DELETE FROM Friends WHERE name='Ze Pil'");
  debugPrint('result[1]:$result');

  result = await database?.rawDelete("DELETE FROM Friends WHERE name=?", ['gyu tae']);
  debugPrint('result[2]:$result');
}

I/flutter (29559): result[1]:[]
I/flutter (29559): result[2]:1

before	after

    StringBuffer query = StringBuffer();
    query
      ..write("DELETE FROM ${tableName.wifi} ")
      ..write("WHERE place=? ")
      ..write("AND name=? ")
      ..write("AND password=? ")
      ..write("AND memo=? ");

    return query.toString();

 

테이블 내부 전체 데이터 삭제처리

따로 쿼리를 주지 않고 테이블 명만 rawDelete(테이블명)만 주면,

알아서 DELETE FROM절을 넣어서 처리한다.

위 쿼리처럼 뒤에 인자가 있는 경우에만 직접 DELETE FROM절을 넣어준다.

int? result = await database?.delete(table);

 

 

 

백업 (Backup)

: 백업을 local DB에 저장하려 했었지만, MalformedURLException이 발생되어 하지 못하였고, 

대신 sqflite에서 제공하는 json형태로 백업하는 로직 有

Android: Android에서는 android.database.sqlite.SQLiteDatabase 클래스를 사용하여 데이터베이스를 백업하고 복원할 수 있습니다.
iOS: iOS에서는 FMDatabase 클래스를 사용하여 데이터베이스를 백업하고 복원할 수 있습니다.

 

AOS는 MANAGER_EXTRENAL_STORAGE를 사용하지 않으면 안된다

 

backup 과정

// back db객체 생성
Database? backupDatabase = await openDatabase(backupDatabasePath);

var backupData = await backupDatabase.query([테이블명])
await backupDatabase.close();

backupData를 가져올때 query에 테이블명만 넣으면 된다. 정말 간편하다.

그리고 backupDatabase는 닫아준다.

 

기존 DB에 적용해보자

// database : 기존 DB
await database?.transaction((txn) async {
  backupData.forEach (element ->
    await txn.insert([테이블명], element)
})

이렇게 그냥 추가하면 문제가 된다.

기존에 있는 것은 걸러주고, 없는 것은 채워주는 것이 좋을 것 같다(OUTER JOIN채택)

 

 

2024-05-28-화 : 현재 테이블의 제일 높은 id의 값을 추출하기

String lastIdQuery(String tableName) => "SELECT MAX(id) FROM $tableName;";

Future<int> lastId({required String table}) async {
database ??= await openDatabase(databaseName);
String query = lastIdQuery(table);
Log.d("query:$query");
final result = await database?.rawQuery(lastIdQuery(table));
Log.d("lastId:$result");
return -1;
}

결과 : lastId:[{MAX(id): 2}]