Creating database from SQL Scheme

[Nik99]

Hi everyone, thanks for yesterday help.

I do have another question: i have an SQL file for creating my sqlite database. How do i make use of it instead of using a string in db.execute? The file is included in the assets bundle

[alextekartik]

I guess you are talking about importing an existing dump file with SQL commands. The only time I've tried I could not execute more than 1 SQL statement on Android when using db.execute but I could be wrong. Here is an attemp: https://github.com/tekartik/sqflite/blob/454158419bdc7594fe5f44fd40484c87f0efff9b/example/lib/exp_test_page.dart#L367-L393

I guess one solution would be to read the dump, split by lines and put all the statement in batch. If you think an helper would be helpful (or if you have already one), let me know.

[Nik99]

Yeah for sure an Helper like readScheme(File file) or readSchemeFromAssets(AssetBundle rootBundle) or something like this would be great, because when working with 10 or 15 tables is a pain to do an execute for each one, and i don't think that importing a pre-made database is a good thing.

Thanks!

[Nik99]

Any news on this ? @alextekartik

[alextekartik]

Sorry I was not available lately. I'm working on it!

[alextekartik]

So I made some tests. Parsing a sql dump file is not that easy especially when there is create trigger commands. I think such thing could be build on top of sqflite so I took an approach I found on cordova: https://github.com/dpa99c/cordova-sqlite-porter

Basically importing a scheme is just a few lines of code (putting all the commands in a batch and commit). So here: https://github.com/tekartik/sqflite_more/tree/dart2 I starter sqflite_porter that you can try, there is a dbExportSql and dbImportSql method. The next steps would be to properly parse a sql file to extract the statements which will likely require parsing SQL commands and I won't be able to tackle that in the next weeks so input/code is welcome on it...

[Nik99]

Sounds like a pain to do.

[tomwassing]

Is there any progress on this feature request? Wouldn't it be possible to allow raw commands to sqlite? db.execute() does not allow multiple queries. If we had a method which allows multiple or direct commands to sqlite, it would solve the problem. Then we could do something like this:

final create = await rootBundle.loadString('assets/db/create.sql');
db.rawExecute(create);

[alextekartik]

Doing a proper parsing of SQL commands could be a pain (especially to handle multiline commands). On both iOS and Android native side, we can only call single commands and there is no easy-way to import a raw .sql dump file. If your sql files has single commands per line, you could use LineSplitter and then simple call the following function:

Future dbImportSql(Database db, List<String> sqlStatements) async {
  var batch = db.batch();
  for (var statement in sqlStatements) {
    batch.execute(statement);
  }
  await batch.commit(noResult: true);
}

as done here: https://github.com/tekartik/sqflite_more/tree/dart2/sqflite_porter.

Parsing SQL commands is currently not part of this layer where one goal is to stay as small as possible