I am looking at this (https://github.com/tekartik/sqflite/blob/master/doc/opening_asset_db.md) for populating data that is already formatted and need for the app, for read functionality only.
So my understanding of creating an SQLite database when we already have all the information in an outside CSV file is to, create the class models in a .dart file in my app, such as
class User {
int id;
String _firstName;
String _lastName;
String _dob;
User(this._firstName, this._lastName, this._dob);
User.map(dynamic obj) {
this._firstName = obj["firstname"];
this._lastName = obj["lastname"];
this._dob = obj["dob"];
}
String get firstName => _firstName;
String get lastName => _lastName;
String get dob => _dob;
Map<String, dynamic> toMap() {
var map = new Map<String, dynamic>();
map["firstname"] = _firstName;
map["lastname"] = _lastName;
map["dob"] = _dob;
return map;
}
void setUserId(int id) {
this.id = id;
}
}
then if I have a CSV file with all the user information inside of it (with values that correspond to the user class), could I be using the database asset to have that fill out the information and then call to it inside of the flutter app? I realize there are probably many ways to go about this, but What exactly is the .db file storing, and how is it formatted? Can i implement a .csv file into this .db file?
First off, you will need to construct a sqlite database from your csv. This can be done in the following way:
Create the necessary table (users.sql)
CREATE TABLE users(
firstname TEXT NOT NULL,
lastname TEXT NOT NULL,
dob TEXT NOT NULL
);
Create the sqlite database
sqlite3 database.db < users.sql
Insert the csv data
sqlite3 database.db
.mode csv
.import data.csv users
Put database.db into your assets and add that in pubspec.yaml.
flutter:
# ...
assets:
- assets/database.db
In your app, you'll have to copy the asset file into "documents". This is slightly complicated.
// Construct a file path to copy database to
Directory documentsDirectory = await getApplicationDocumentsDirectory();
String path = join(documentsDirectory.path, "asset_database.db");
// Only copy if the database doesn't exist
if (FileSystemEntity.typeSync(path) == FileSystemEntityType.notFound){
// Load database from asset and copy
ByteData data = await rootBundle.load(join('assets', 'database.db'));
List<int> bytes = data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);
// Save copied asset to documents
await new File(path).writeAsBytes(bytes);
}
Lastly, you can access the database like so.
Directory appDocDir = await getApplicationDocumentsDirectory();
String databasePath = join(appDocDir.path, 'asset_database.db');
this.db = await openDatabase(databasePath);
initialized = true;
Example query (this._initialize() is step 6)
Future<List<Page>> search(String word, int parentId) async {
if (!initialized) await this._initialize();
String query = '''
SELECT * FROM users
LIMIT 25''';
return await this.db.rawQuery(query);
}