simolus3/sqlite3.dart

sqlite3 bindings for Dart

sqlite3.dart's Issues

CocoaPods could not find compatible versions for pod "SQLCipher" for platform :ios, '11.0'

While updating the app to work with flutter 2.2.1 I run into the problem where cocoa pods can't find sqflite_sqlcipher when the pod file platform is set to 11.

[!] CocoaPods could not find compatible versions for pod "SQLCipher":
In Podfile:
sqflite_sqlcipher (from .symlinks/plugins/sqflite_sqlcipher/ios) was resolved to 0.0.1, which depends on
SQLCipher (= 4.4.2)

sqlcipher_flutter_libs (from `.symlinks/plugins/sqlcipher_flutter_libs/ios`) was resolved to 0.0.1, which depends on
  SQLCipher (~> 4.4.3)

on platform:ios, '10' this still works, but the latest versions of firebase require version 11 and higher.

the defaults ways of:

• removing podfile.lock
• rm -rf ~/.cocoapods/repos
• pod install --repo-update
• flutter clean

doesn't work.

Doctor summary (to see all details, run flutter doctor -v):
[✓] Flutter (Channel stable, 2.2.1, on macOS 11.2.1 20D74 darwin-x64, locale en-NL)
[✓] Android toolchain - develop for Android devices (Android SDK version 30.0.2)
[✓] Xcode - develop for iOS and macOS
[✓] Chrome - develop for the web
[✓] Android Studio (version 4.1)
[✓] Connected device (2 available)

[sqlcipher_flutter_libs] openCipherOnAndroid not working on Android 6

We received some reports that our app doesn't startup on some Android 6 devices. I've looked into it and found that the applyWorkaroundToOpenSqlCipherOnOldAndroidVersions function threw an error. (Null check operator used on a null value).
So I looked into it deeper and after searching for a while I found that there was an issue with the location of the libsqlcipher.so file on my emulator with Android 6.
If the file can't be found, you check /data/data/$appId/lib/libsqlcipher.so. I found the file however in /data/app/$appId-2/lib/x86_64/libsqlcipher.so. After some testing it was gone and appeared to be in /data/app/$appId-1/lib/x86_64/libsqlcipher.so. So the int seams to be different some times.
So I've put a try catch around applyWorkaroundToOpenSqlCipherOnOldAndroidVersions and reworked the function openCipherOnAndroid. This now works fine on my emulator

DynamicLibrary openCipherOnAndroid() {
  try {
    return DynamicLibrary.open('libsqlcipher.so');
  } catch (_) {
    // On some (especially old) Android devices, we somehow can't dlopen
    // libraries shipped with the apk. We need to find the full path of the
    // library (/data/data/<id>/lib/libsqlcipher.so) and open that one.
    // For details, see https://github.com/simolus3/moor/issues/420
    final appIdAsBytes = File('/proc/self/cmdline').readAsBytesSync();

    // app id ends with the first \0 character in here.
    final endOfAppId = max(appIdAsBytes.indexOf(0), 0);
    final appId = String.fromCharCodes(appIdAsBytes.sublist(0, endOfAppId));

    try {
      return DynamicLibrary.open('/data/data/$appId/lib/libsqlcipher.so');
    } catch (_) {
      return openCipherOnAndroidAt(appId, 0);
    }
  }
}

DynamicLibrary openCipherOnAndroidAt(String appId, int i) {
  if (i > 9) throw ArgumentError('db not found in data/app');
  try {
    return DynamicLibrary.open('/data/app/$appId-$i/lib/x86_64/libsqlcipher.so');
  } catch (_) {
    return openCipherOnAndroidAt(appId, i + 1);
  }
}

Null-safety for sqlitecipher_flutter_libs

Hi there!

I'm using moor with sqlitecipher_flutter_libs as documented here on the bottom of the page. I've noticed that sqlite3_flutter_libs has a published version with null-safety enabled, but the sqlitecipher_flutter_libs variant has not yet enabled null-safety:

sqlite3.dart/sqlcipher_flutter_libs/pubspec.yaml

Line 8 in 7a226a0

sdk: ">=2.7.0 <3.0.0"

Is this an oversight, or do you recommend the other approach described here to setting up sqlcipher with moor? Does this even work anymore (I could not find the branch extras/encryption on the moor repo)?

SpatiaLite

Hi there, I'm just finding this project and I'm trying to understand it better. It looks like it's a quick way to bring in custom versions of SQLite for use in Flutter? I am trying to get access to SpatiaLite on Flutter for iOS and Android. Is that within the scope of the project? What's needed on my end to start querying SpatiaLite in my project using this project?

A problem occurred configuring project ':sqlite3_flutter_libs'

FAILURE: Build failed with an exception.

* What went wrong:
A problem occurred configuring project ':sqlite3_flutter_libs'.
> Could not resolve all artifacts for configuration ':sqlite3_flutter_libs:classpath'.
   > Could not find com.google.prktobuf:protobuf-java:3.10.0.
     Searched in the following locations:
       - https://dl.google.com/dl/android/maven2/com/google/prktobuf/protobuf-java/3.10.0/protobuf-java-3.10.0.pom
       - https://jcenter.bintray.com/com/google/prktobuf/protobuf-java/3.10.0/protobuf-java-3.10.0.pom
     Required by:
         project :sqlite3_flutter_libs > com.android.tools.build:gradle:4.1.2 > com.android.tools.build:builder:4.1.2 > com.android.tools.ddms:ddmlib:27.1.2
> Could not get unknown property 'android' for project ':sqlite3_flutter_libs' of type org.gradle.api.Project.

* Try:
Run with --stacktrace option to get the stack trace. Run with --info or --debug option to get more log output. Run with --scan to get full insights.

this is in a new project setup with flutter 2.0

I'm not very familiar with the flutter plugin build process do I not know what else to provide.

sqlcipher_flutter_libs does not compile

I'm trying to compile sqlite3.dart package with Flutter 2.0.3 installed.

I have errors in sqlcipher_flutter_libs.dart

If I add Flutter dependency in the pubspec.yaml, it is working

Is it normal ?

Versions pre 4.0.1 do not work

Unable to use previous versions of the package which means I am forced to upgrade to flutter 2 which we currently are not ready for.

Add constants

I found this example on the official dart repo, an FFI implementation of SQLite.
https://github.com/dart-lang/sdk/tree/master/samples/ffi/sqlite

In this example, the class contants.dart defines more SQLite constants knew here.

What do you think of this approach ?
Do you think it could be a good idea to update sqlite3 to looks like to it ?

If yes, I could help with a PR.

use Uint8List instead of list<int>

see https://medium.com/flutter-community/working-with-bytes-in-dart-6ece83455721

support sqlite3_enable_shared_cache

In recent sqlite versions, there's a per-process shared cache that works across multiple connections. This may be useful in cases where you want to have several processes writing to the same database.

To enable it requires using the api documented here: https://www.sqlite.org/sharedcache.html

There's a global setting:

int sqlite3_enable_shared_cache(int);

There's also flags to pass to open:

Individual database connections created using sqlite3_open_v2() can choose to participate or not participate in shared cache mode by using the SQLITE_OPEN_SHAREDCACHE or SQLITE_OPEN_PRIVATECACHE flags the third parameter. The use of either of these flags overrides the global shared cache mode setting established by sqlite3_enable_shared_cache().

Any thoughts on supporting this?

Any plans to implement sqlite3_busy_handler ?

I recently found myself wanting to implement the sqlite3_busy_handler hook and a busy_timeout, to handle BUSY errors, rather than using a try-except.

https://www.sqlite.org/c3ref/busy_handler.html

Any thoughts on implementing this so that a timeout value and a Dart callback could be used?

Concurrency issue?

Probably after the last update of flutter (2.0.5), my unit tests started to fail, I notice something strange:

As if the the select method is executed before the finish of the second execute method (teste it without transaction, the result is the same).
Unit test is executed in windows 10 environment with Sqlite.dll version 3.35.5.
Dart Sqlite3 version is 1.1.0.
Does anyone else have this issue or I am doing something wrong?

FTS Support

Will this library support FTS ? and can a better example be provided with physical database (not in-memory) db ?

android 6.1 for sqlcipher_flutter_libs

i see there is error with dynamic link in old phone and some 6.1 device
there is applyWorkaroundToOpenSqlite3OnOldAndroidVersions for sqlite3_flutter_libs but not for sqlcipher_flutter_libs
also when we override like this as describe in moor docs will break old device because we need to use full path with id on old device

  open.overrideFor(
      OperatingSystem.android, () => DynamicLibrary.open('libsqlcipher.so'));

i suggest to add this to sqlcipher_flutter_libs :

1- applyWorkaroundToOpenSqlite3OnOldAndroidVersions

2- provide openCipherOnAndroid function that do this

DynamicLibrary openCipherOnAndroid() {
  try {
    return DynamicLibrary.open('libsqlcipher.so');
  } catch (_) {
    // On some (especially old) Android devices, we somehow can't dlopen
    // libraries shipped with the apk. We need to find the full path of the
    // library (/data/data/<id>/lib/libsqlcipher.so) and open that one.
    // For details, see https://github.com/simolus3/moor/issues/420
    final appIdAsBytes = File('/proc/self/cmdline').readAsBytesSync();

    // app id ends with the first \0 character in here.
    final endOfAppId = max(appIdAsBytes.indexOf(0), 0);
    final appId = String.fromCharCodes(appIdAsBytes.sublist(0, endOfAppId));

    return DynamicLibrary.open('/data/data/$appId/lib/libsqlcipher.so');
  }
}

then programmer could do

  open.overrideFor(
      OperatingSystem.android, openCipherOnAndroid);

i am happy to make pull request if this way ok .

Any plans to implement sqlite3_update_hook?

We have a trigger (before insert) that inserts into another table. Using the watch() function in Drift does not seem to fire the callback for the update of the other table; it does fire the update for the original (i.e. Drift initiated) table though.

https://www.sqlite.org/c3ref/update_hook.html

Any thoughts on implementing this so that a Dart callback could be called for all (and not only Drift insertion) table updates?

Keep up the great work!

sqlcipher on other OS ?

Hi,

First of all, thank for you library sqlite3.

You allow to use sqlcipher for Android and iOS, but, do you plan to provide this functionnality for other OS ? Like Windows, MAC and Linux ?

Please support backup API

SQLite3 has backup APIs.
https://sqlite.org/backup.html
if possible, please support it.

and if possible, support backup for moor??

thx.

Rename slcipher_flutter_libs folder to sqlcipher_flutter_libs

Well that is not really an issue but it looks like a typo error!

Flags for Database.open()

Please, allow to use other flags in Database.open() method like SQLITE_OPEN_FULLMUTEX.

[Proposal] Rename Row class

If you use sqlite3 inside flutter App, you could have conflicts name between Flutter.Row and Sqlite3.Row.

Do you think it could be a good idea to rename you Row class into SqlRow or RowSql for example ?

named parameters

Is it possible to used named parameters in PreparedStatement? For example, in my old code, I have "WHERE Entries MATCH @query" and in C# before I would use bind("@query", "stuff"), and then execute.

https://sqlite.org/c3ref/bind_blob.html

My old code had:

				cmdAttachDatabase.Bind("@dbname", DbName);
                cmdAttachDatabase.Bind("@dbfullpath", _initDbPath);     // This may contain the CEROD password
				SQLite3.Result result = (SQLite3.Result)cmdAttachDatabase.ExecuteNonQuery();
				if (result != SQLite3.Result.OK)
				{
					throw new Exception("Unable to attach database");
				}

with

        internal static string AttachDatabase
        {
            get
            {
                return @"ATTACH @dbfullpath AS @dbname;";
            }
        }

https://sqlite.org/c3ref/bind_parameter_index.html

An execute and select that takes Map<String, Object?> might do the trick.

Does sqlite3_flutter_libs needs to support NNBD?

I am trying to migrate my project to NNBD. This package does not support NNBD (according to the https://pub.dev/packages/sqlite3_flutter_libs). My question is, does this package needs to support it so I can migrate my project to NNBD?

Accordingly with https://dart.dev/null-safety all my dependencies need to support it so I can have a project fully sound. I know this might be a different type of project since it bundles sqlite3 libraries and intentionally contains no Dart code.

Sorry if this might be an idiotic question, but this is the last package that does not support NNBD from my dependency list. As far as I know since it does not contains dart code it should be fairly simple to place a NNBD tag right?

batch commands?

Does sqlite3.dart support "INSERT...; SELECT LAST_INSERT_ROWID()" so that we can select and get the last id after inserting? I am trying to get the inserted new ID to put into another query in one step.

"INSERT INTO [35d738af6c304b009736abe5aaf184f4].Entries_content (
  word, definition, entryorder, freq
) VALUES (
  @word, @definition, @entryorder, @freq
);
SELECT LAST_INSERT_ROWID();"

When I select though, I get no rows. :(

I remember seeing it before in shell so that we can

sqlite> INSERT INTO test VALUES (2,3); SELECT LAST_INSERT_ROWID();
2

Update. I looked back and some old code I had used in C#, and we kept using the tail (pzTail, remains, etc.) until the queries were exhausted.

		public T ExecuteScalar<T> ()
		{
			if (_conn.Trace) {
				Debug.WriteLine ("Executing Query: " + this);
			}

			var rowsAffected = 0;

			T val = default(T);
			remainingText = CommandText.Trim().TrimEnd(';');
			while (!string.IsNullOrEmpty(remainingText))        // should cycle through
			{
				var stmt = Prepare();

				try
				{
					var colcount = SQLite3.ColumnCount(stmt);
					if (colcount > 0)
					{

						var r = SQLite3.Step(stmt);
						if (r == SQLite3.Result.Row)
						{
							var colType = SQLite3.ColumnType(stmt, 0);
							val = (T)ReadCol(stmt, 0, colType, typeof(T));
						}
						else if (r == SQLite3.Result.Done)
						{
						}
						else
						{
							throw SQLiteException.New(r, SQLite3.GetErrmsg(_conn.Handle));
						}
					}
					else
					{
						rowsAffected += stepNonQuery(stmt);
					}
				}
				finally
				{
					Finalize(stmt);
				}
			}

			return val;
		}

Issue with auto increment

Using: pure Dart sqlite
Version: 0.1.4
Reproducible example:

import 'package:sqlite3/sqlite3.dart';

main() {
  final db = sqlite3.openInMemory();

  // Create a table.
  String createTableStatement = '''
    CREATE TABLE users (
      user_id BIGINT AUTO_INCREMENT PRIMARY KEY NOT NULL,
      username TEXT NOT NULL,
      email TEXT NOT NULL
    );
  ''';
  db.execute(createTableStatement);

  //INSERT INTO table_name (column1, column2, column3, ...)
  //VALUES (value1, value2, value3, ...);

  // Insert a user.
  String insertRowStatement = '''
    INSERT INTO users (username, email)
    VALUES ("Wayne", "My Email");
  ''';
  db.execute(insertRowStatement);
}

What should happen: Successfully insert into database, as auto increment should automatically create user_id=1.
What happens:

Unhandled exception:
SqliteException(1299): NOT NULL constraint failed: users.user_id
#0      DatabaseImpl.execute (package:sqlite3/src/impl/database.dart:93:7)
#1      main (package:local_api/src/db/db.dart:24:6)
#2      _startIsolate.<anonymous closure> (dart:isolate-patch/isolate_patch.dart:301:19)
#3      _RawReceivePortImpl._handleMessage (dart:isolate-patch/isolate_patch.dart:168:12)

run multiple [prepared] statements

Hi, I wanted to ask if this library supports running multiple statements and/or multiple prepared statements.
This would allow devs to make custom SQL scripts or maybe make a lookup table from a file with predefined template queries

Issue on iOS

Hi,

We do have a Flutter project still running SDK 2.6.0 (and can't change it right now). As the previous download location for the sqlite bindings is no longer ava. I have upgraded it to:

sqlite3_flutter_libs: ^0.4.0+1

Now I get the following error during pod install and also during the build:
Pod install

[!] CocoaPods could not find compatible versions for pod "sqlite3":
  In snapshot (Podfile.lock):
    sqlite3 (= 3.32.3, ~> 3.32.3)

  In Podfile:
    sqlite3_flutter_libs (from `.symlinks/plugins/sqlite3_flutter_libs/ios`) was resolved to 0.0.1, which depends on
      sqlite3 (~> 3.35.4)

Specs satisfying the `sqlite3 (= 3.32.3, ~> 3.32.3), sqlite3 (~> 3.35.4)` dependency were found, but they required a higher minimum deployment target.

Build

 update
    [!] CocoaPods could not find compatible versions for pod "sqlite3":
      In snapshot (Podfile.lock):
        sqlite3 (= 3.32.3, ~> 3.32.3)
      In Podfile:
        sqlite3_flutter_libs (from `.symlinks/plugins/sqlite3_flutter_libs/ios`) was resolved to 0.0.1, which depends on
          sqlite3 (~> 3.35.4)
    Specs satisfying the `sqlite3 (= 3.32.3, ~> 3.32.3), sqlite3 (~> 3.35.4)` dependency were found, but they required a higher minimum deployment target.
    /Library/Ruby/Gems/2.6.0/gems/molinillo-0.6.6/lib/molinillo/resolution.rb:328:in `raise_error_unless_state'
    /Library/Ruby/Gems/2.6.0/gems/molinillo-0.6.6/lib/molinillo/resolution.rb:310:in `block in unwind_for_conflict'
    /Library/Ruby/Gems/2.6.0/gems/molinillo-0.6.6/lib/molinillo/resolution.rb:308:in `tap'
    /Library/Ruby/Gems/2.6.0/gems/molinillo-0.6.6/lib/molinillo/resolution.rb:308:in `unwind_for_conflict'
    /Library/Ruby/Gems/2.6.0/gems/molinillo-0.6.6/lib/molinillo/resolution.rb:257:in `process_topmost_state'
    /Library/Ruby/Gems/2.6.0/gems/molinillo-0.6.6/lib/molinillo/resolution.rb:182:in `resolve'
    /Library/Ruby/Gems/2.6.0/gems/molinillo-0.6.6/lib/molinillo/resolver.rb:43:in `resolve'
    /Library/Ruby/Gems/2.6.0/gems/cocoapods-1.10.1/lib/cocoapods/resolver.rb:94:in `resolve'
    /Library/Ruby/Gems/2.6.0/gems/cocoapods-1.10.1/lib/cocoapods/installer/analyzer.rb:1074:in `block in resolve_dependencies'
    /Library/Ruby/Gems/2.6.0/gems/cocoapods-1.10.1/lib/cocoapods/user_interface.rb:64:in `section'
    /Library/Ruby/Gems/2.6.0/gems/cocoapods-1.10.1/lib/cocoapods/installer/analyzer.rb:1072:in `resolve_dependencies'
    /Library/Ruby/Gems/2.6.0/gems/cocoapods-1.10.1/lib/cocoapods/installer/analyzer.rb:124:in `analyze'
    /Library/Ruby/Gems/2.6.0/gems/cocoapods-1.10.1/lib/cocoapods/installer.rb:414:in `analyze'
    /Library/Ruby/Gems/2.6.0/gems/cocoapods-1.10.1/lib/cocoapods/installer.rb:239:in `block in resolve_dependencies'
    /Library/Ruby/Gems/2.6.0/gems/cocoapods-1.10.1/lib/cocoapods/user_interface.rb:64:in `section'
    /Library/Ruby/Gems/2.6.0/gems/cocoapods-1.10.1/lib/cocoapods/installer.rb:238:in `resolve_dependencies'
    /Library/Ruby/Gems/2.6.0/gems/cocoapods-1.10.1/lib/cocoapods/installer.rb:160:in `install!'
    /Library/Ruby/Gems/2.6.0/gems/cocoapods-1.10.1/lib/cocoapods/command/install.rb:52:in `run'
    /Library/Ruby/Gems/2.6.0/gems/claide-1.0.3/lib/claide/command.rb:334:in `run'
    /Library/Ruby/Gems/2.6.0/gems/cocoapods-1.10.1/lib/cocoapods/command.rb:52:in `run'
    /Library/Ruby/Gems/2.6.0/gems/cocoapods-1.10.1/bin/pod:55:in `<top (required)>'
    /usr/local/bin/pod:23:in `load'
    /usr/local/bin/pod:23:in `<main>'
Error output from CocoaPods:
↳
    [!] Automatically assigning platform `iOS` with version `12.1` on target `Runner` because no platform was specified. Please specify a platform for this target in your Podfile. See `https://guides.cocoapods.org/syntax/podfile.html#platform`.
Exception: Error running pod install
Exited (sigterm)

Is there any way or suggestion to fix this? The android app builds fine. Happy for any feedback/suggestions.

long queries - async

Are there thoughts on using async for long queries?

Supported sqlite extensions

Hi,

Is there any extensions included ? If yes, which ones ?

I would like to use SQLITE_ENABLE_JSON1 extension and perhaps others accross all platform.

Is it possible to activate some SQLite extensions ? I yes, how ?

Thank you

update .gitignore

Hi,

Could be possible for you to update some part of your main .gitignore this lines please in order to follow the Flutter .gitignore like this below ?

# Flutter/Dart/Pub related
**/doc/api/
.dart_tool/
.flutter-plugins
.flutter-plugins-dependencies
**/generated_plugin_registrant.dart
.packages
.pub-cache/
.pub/
build/
flutter_*.png
linked_*.ds
unlinked.ds
unlinked_spec.ds

Or more if you want, because in my case '.flutter-plugins-dependencies' is automatically generated and doesn't need to be tracked.

1.3.0 Crashing on iOS (EXC_BAD_ACCESS)

The iOS system logs don't seem to be very helpful.
As soon as I update the version, the app crashes on start or the first query, not exactly sure.

Date/Time:           2021-10-19 12:15:54.0756 +0200
Launch Time:         2021-10-19 12:14:42.8908 +0200
OS Version:          iPhone OS 15.0.2 (19A404)
Release Type:        User
Baseband Version:    2.09.10
Report Version:      104

Exception Type:  EXC_BAD_ACCESS (SIGSEGV)
Exception Subtype: KERN_INVALID_ADDRESS at 0x0000000000000000
Exception Codes: 0x0000000000000001, 0x0000000000000000
VM Region Info: 0 is not in any region.  Bytes before following region: 4341891072
      REGION TYPE                 START - END      [ VSIZE] PRT/MAX SHRMOD  REGION DETAIL
      UNUSED SPACE AT START
--->  
      __TEXT                   102cc0000-102ccc000 [   48K] r-x/r-x SM=COW  ...er.app/Runner
Exception Note:  EXC_CORPSE_NOTIFY
Termination Reason: SIGNAL; [11]
Terminating Process: exc handler [1339]

Terminating Process: exc handler [1339]
Triggered by Thread:  30

Thread 30 name:  DartWorker
Thread 30 Crashed:
0   ???                           	               0x0 ???
1   ???                           	       0x10a506b08 ???
2   ???                           	       0x13990a744 ???
3   ???                           	       0x13990a580 ???
4   ???                           	       0x13990a330 ???
5   ???                           	       0x13990a03c ???
6   ???                           	       0x139904eb0 ???
7   ???                           	       0x139904b80 ???
8   ???                           	       0x13990494c ???
9   ???                           	       0x139903c2c ???
10  ???                           	       0x1399032bc ???
11  ???                           	       0x120d55000 ???
12  ???                           	       0x120d522f4 ???
13  ???                           	       0x120d51e04 ???
14  ???                           	       0x120d51a10 ???
15  ???                           	       0x120d50950 ???
16  ???                           	       0x120d4f1ec ???
17  ???                           	       0x120d4eec8 ???
18  ???                           	       0x120d4ebac ???
19  ???                           	       0x120d4e748 ???
20  ???                           	       0x120d4e640 ???
21  ???                           	       0x120d299e0 ???
22  ???                           	       0x120d0e620 ???
23  ???                           	       0x10a5032e4 ???
24  Flutter                       	       0x1067e3ec4 0x106290000 + 5586628
25  Flutter                       	       0x1068025a8 0x106290000 + 5711272
26  Flutter                       	       0x10683b2b4 0x106290000 + 5943988
27  Flutter                       	       0x10683ad50 0x106290000 + 5942608
28  Flutter                       	       0x10697596c 0x106290000 + 7231852
29  Flutter                       	       0x1068d06b4 0x106290000 + 6555316
30  libsystem_pthread.dylib       	       0x1f2ec9a60 0x1f2ec8000 + 6752
31  libsystem_pthread.dylib       	       0x1f2ec8f5c 0x1f2ec8000 + 3932


Thread 30 crashed with ARM Thread State (64-bit):
    x0: 0x0000000282fddb80   x1: 0x0000000000000000   x2: 0x0000000000000000   x3: 0x0000000000000003
    x4: 0x0000000000000000   x5: 0x00000001a11eba80   x6: 0x0000000000000a00   x7: 0x0000000000000000
    x8: 0x0000000000000000   x9: 0x00000001a11eba80  x10: 0x0000000000000005  x11: 0x0000000000bf3d01
   x12: 0x0000000000004009  x13: 0x0000000104d0c000  x14: 0x0000000000003fff  x15: 0x000000016e5a1360
   x16: 0x0000000000000000  x17: 0x000000011cb57438  x18: 0x0000000000000000  x19: 0x0000000000000000
   x20: 0x0000000113d20ca0  x21: 0x0000000000000003  x22: 0x0000000000000000  x23: 0x00000001042dcfb0
   x24: 0x0000000104088041  x25: 0x000000016e4bf000  x26: 0x000000011cb56e00  x27: 0x00000001399857f0
   x28: 0x0000000400000000   fp: 0x000000016e5a1350   lr: 0x00000001a11eb8d8
    sp: 0x000000016e5a1320   pc: 0x0000000000000000 cpsr: 0x60001000
   far: 0x0000000000000000  esr: 0x82000006 (Instruction Abort) Translation fault

Implement Insert Bind Parameters functionality

Please provide a function for an insert query which binds a list of values to ?'s

An example of this in the sqflite library is rawInsert()
https://github.com/tekartik/sqflite/blob/master/sqflite_common/lib/sqlite_api.dart

An example of this in the mysql1 library is query()
https://github.com/adamlofts/mysql1_dart/blob/master/lib/src/single_connection.dart

Thank-you.

Support for cursors

This is needed to iterate over a large result set without preloading the entire set into memory. ResultSet.iterator could be made to work (maybe BidirectionalIterator could also be useful), but it currently isn't implemented like a database cursor.

Upgrade to dart:ffi 0.3.0-nullsafety.1

If the plugin is used with dart:ffi 0.3.0-nullsafety.1 the following errors are seen

../../../flutter/.pub-cache/hosted/pub.dartlang.org/sqlite3-0.1.9-nullsafety.2/lib/src/impl/database.dart:46:19: Error: Method not found: 'allocate'.
final outDb = allocate<Pointer>();
^^^^^^^^
../../../flutter/.pub-cache/hosted/pub.dartlang.org/sqlite3-0.1.9-nullsafety.2/lib/src/impl/database.dart:100:22: Error: The method 'allocate' isn't defined for the class 'DatabaseImpl'.

• 'DatabaseImpl' is from 'package:sqlite3/src/impl/implementation.dart' ('../../../flutter/.pub-cache/hosted/pub.dartlang.org/sqlite3-0.1.9-nullsafety.2/lib/src/impl/implementation.dart').
  Try correcting the name to the name of an existing method, or defining a method named 'allocate'.
  final errorOut = allocate<Pointer>();
  ^^^^^^^^
  ../../../flutter/.pub-cache/hosted/pub.dartlang.org/sqlite3-0.1.9-nullsafety.2/lib/src/impl/database.dart:134:21: Error: The method 'allocate' isn't defined for the class 'DatabaseImpl'.
• 'DatabaseImpl' is from 'package:sqlite3/src/impl/implementation.dart' ('../../../flutter/.pub-cache/hosted/pub.dartlang.org/sqlite3-0.1.9-nullsafety.2/lib/src/impl/implementation.dart').
  Try correcting the name to the name of an existing method, or defining a method named 'allocate'.
  final stmtOut = allocate<Pointer<sqlite3_stmt>>();
  ^^^^^^^^
  ../../../flutter/.pub-cache/hosted/pub.dartlang.org/sqlite3-0.1.9-nullsafety.2/lib/src/ffi/memory.dart:29:18: Error: Method not found: 'free'.
  void free() => ffi.free(this);
  ^^^^
  ../../../flutter/.pub-cache/hosted/pub.dartlang.org/sqlite3-0.1.9-nullsafety.2/lib/src/ffi/memory.dart:33:15: Error: Method not found: 'allocate'.
  final ptr = ffi.allocate(count: bytes.length + additionalLength);

Is a release planned to support this? Is there a branch that can be pointed to resolve this before it is published?

sqlite3_flutter_libs 0.4.0

I'm getting this error when trying to open sqlite in memory on Android.

Invalid argument(s): Failed to load dynamic library (dlopen failed: library "/data/data/com.milvintsiss.chronopsi/lib/libsqlite3.so" not found)

I'm using sqflite 1.3.2+3 and moor: 3.4.0

Work fine with sqlite3_flutter_libs 0.3.0

Failed to load dynamic library on encrytion

Everything is fine on using non-encrypted sqlite by FlutterQueryExecutor. When switch to encrypted version by EncryptedExecutor, exception is shown.

Err:
Failed to load dynamic library (dlopen failed: library ".../lib/libsqlite3.so" not found)

Here is my code

class MyDatabase extends _$MyDatabase {
  MyDatabase()

      : super((EncryptedExecutor.inDatabaseFolder(
        password: "mypw",
    path: 'db.sqlite3',
     singleInstance: true, 
   
    logStatements: true,
     creator: (file) async { 
     final content = await rootBundle.load(join("assets/data", "db.sqlite3")); 
     await file.writeAsBytes(content.buffer.asUint8List()); 
     
   }, 
  )
  ));
...

my dependency for encryption:

  moor: 
  encrypted_moor:
   git:
    url: https://github.com/simolus3/moor.git
    path: extras/encryption 

Unable to update pod

Installing sqlite3 (3.32.3)
> Http download
$ /usr/bin/curl -f -L -o /var/folders/dn/9kj3z3_17pbd1h3f3mv_rc700000gn/T/d20211117-8200-1324sg9/file.zip
https://www.sqlite.org/2020/sqlite-amalgamation-3320300.zip --create-dirs --netrc-optional --retry 2 -A 'CocoaPods/1.11.2 cocoapods-downloader/1.5.1'

[!] Error installing sqlite3
[!] /usr/bin/curl -f -L -o /var/folders/dn/9kj3z3_17pbd1h3f3mv_rc700000gn/T/d20211117-8200-1324sg9/file.zip
https://www.sqlite.org/2020/sqlite-amalgamation-3320300.zip --create-dirs --netrc-optional --retry 2 -A 'CocoaPods/1.11.2 cocoapods-downloader/1.5.1'

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed

0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0 0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0 0
0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0
curl: (60) SSL certificate problem: certificate has expired
More details here: https://curl.haxx.se/docs/sslcerts.html

curl failed to verify the legitimacy of the server and therefore could not
establish a secure connection to it. To learn more about this situation and
how to fix it, please visit the web page mentioned above.

Creates resources for all platforms for appbundle

For sqlite3_flutter_libs the library generates resources for all platforms (arm64-v8a, armeabi-v7a, x86, x86_64) in one appbundle regardless what is specified via "flutter build appbundle --target-platform".

This is a major problem because Google think that the app works on all these platforms but at the moment flutter cannot create the resources for x86 and the app does not start after the installation on these platforms.

pub.dev version of library 0.2.0

• Flutter (Channel beta, 1.23.0-18.1.pre, on Microsoft Windows [Version 10.0.19042.572], locale de-DE)
• Android toolchain - develop for Android devices (Android SDK version 28.0.3)
  • Platform android-29, build-tools 28.0.3

Flutter Query method parameters have to be non-nullable.

I tried to use the build runner generating my database class in AS But the terminal prompts this error after the command
'Query method parameters have to be non-nullable. Define uid as non-nullable.
If you want to assert null, change your query to use the IS NULL/IS NOT NULL operator without passing a nullable parameter.'

Invoking custom functions crashes on mac

Sqlcipher not working on iOS when using google_mobile_ads package

So this is more as a note to people that may encounter this issue in the future than a question 😅

When using sqlcipher_flutter_libs and google_mobile_ads (official Admob support for Flutter) at the same time, the Sqlcipher native library wasn't being picked up by the Dart FFI. In fact, PRAGMA cipher_version wasn't returning any results.

The problem is that the iOS pod that google_mobile_ads uses, links with sqlite3 under the hood, which in return makes the Dart FFI load a normal Sqlite library instead of Sqlcipher.

To fix it, you can do the following: XCode -> Project -> Other Linker Flags -> Put "-framework SQLCipher" inside, without the quotes.

More context on the topic:
https://discuss.zetetic.net/t/cannot-open-encrypted-database-with-sqlcipher-4/3654/4 https://discuss.zetetic.net/t/encrypted-db-in-swift-ios-via-fmdb-can-not-open-in-sql-db-browser/4813

Hope this saves a good headache to someone else 😄

Publishing sqlite3-native-libraries

Currently, the native dependencies for Android are hosted on Bintray, which will shut down shortly. I've migrated the dependencies to a GCS bucket but that doesn't really scale either.
Maybe I can host them on central, but I'm not sure if a library that contains zero Java Code is allowed there.

sqlite3_flutter_libs: 0.3.0 build apk --release crush

deprecated codes

../../../../.pub-cache/hosted/pub.flutter-io.cn/sqlite3-0.1.9-nullsafety.2/lib/src/ffi/sqlite3.ffi.dart:6:7: Info: Struct 'char' is empty. Support for empty structs is deprecated and will be removed in the next stable version of Dart. Use Opaque instead.
class char extends Struct {}
      ^
../../../../.pub-cache/hosted/pub.flutter-io.cn/sqlite3-0.1.9-nullsafety.2/lib/src/ffi/sqlite3.ffi.dart:8:7: Info: Struct 'sqlite3' is empty. Support for empty structs is deprecated and will be removed in the next stable version of Dart. Use Opaque instead.
class sqlite3 extends Struct {}
      ^
../../../../.pub-cache/hosted/pub.flutter-io.cn/sqlite3-0.1.9-nullsafety.2/lib/src/ffi/sqlite3.ffi.dart:10:7: Info: Struct 'sqlite3_stmt' is empty. Support for empty structs is deprecated and will be removed in the next stable version of Dart. Use Opaque instead.
class sqlite3_stmt extends Struct {}
      ^
../../../../.pub-cache/hosted/pub.flutter-io.cn/sqlite3-0.1.9-nullsafety.2/lib/src/ffi/sqlite3.ffi.dart:12:7: Info: Struct 'sqlite3_value' is empty. Support for empty structs is deprecated and will be removed in the next stable version of Dart. Use Opaque instead.
class sqlite3_value extends Struct {}
      ^
../../../../.pub-cache/hosted/pub.flutter-io.cn/sqlite3-0.1.9-nullsafety.2/lib/src/ffi/sqlite3.ffi.dart:14:7: Info: Struct 'sqlite3_context' is empty. Support for empty structs is deprecated and will be removed in the next stable version of Dart. Use Opaque instead.
class sqlite3_context extends Struct {}
      ^
../../../../.pub-cache/hosted/pub.flutter-io.cn/ffi-0.2.0-nullsafety.1/lib/src/utf8.dart:23:7: Info: Struct 'Utf8' is empty. Support for empty structs is deprecated and will be removed in the next stable version of Dart. Use Opaque instead.
class Utf8 extends Struct {
      ^
../../../../.pub-cache/hosted/pub.flutter-io.cn/ffi-0.2.0-nullsafety.1/lib/src/utf16.dart:16:7: Info: Struct 'Utf16' is empty. Support for empty structs is deprecated and will be removed in the next stable version of Dart. Use Opaque instead.
class Utf16 extends Struct {
      ^
../../../../.pub-cache/hosted/pub.flutter-io.cn/ffi-0.2.0-nullsafety.1/lib/src/allocation.dart:47:33: Info: Support for using non-constant type arguments 'T' in this FFI API is deprecated and will be removed in the next stable version of Dart. Rewrite the code to ensure that type arguments are compile time constants referring to a valid native type.
  final int totalSize = count * sizeOf<T>();
                                ^

flutter version:1.27.0
dart version:2.12.0-dev

Could not resolve sqlite3-native-library with old version of package

Hello!
I am trying to build an application (for Android) with an old version of the library (0.2.0). I get errors in the console:

Could not resolve all task dependencies for configuration ':sqlite3_flutter_libs:debugCompileClasspath'.
   > Could not resolve eu.simonbinder:sqlite3-native-library:3.32.3.
     Required by:
         project :sqlite3_flutter_libs
      > Could not resolve eu.simonbinder:sqlite3-native-library:3.32.3.
         > Could not get resource 'https://dl.bintray.com/sbinder/sqlite3-native-library/eu/simonbinder/sqlite3-native-library/3.32.3/sqlite3-native-library-3.32.3.pom'.
 > Could not GET 'https://dl.bintray.com/sbinder/sqlite3-native-library/eu/simonbinder/sqlite3-native-library/3.32.3/sqlite3-native-library-3.32.3.pom'. Received status code 403 from server: Forbidden

I have next dependencies in pubspec.yaml:

environment:
  sdk: ">=2.8.0 <3.0.0"

dependencies:
  moor: ^3.3.1
  sqlite3_flutter_libs: ^0.2.0

The reason I am building the old version is to test the transition from the old version to the new one with null-safety.

Error web build

Hello
I can no longer get web output when I add your library to the project
I do not need a database in the web version
../../Developer/flutter/.pub-cache/hosted/pub.dartlang.org/sqlite3-1.4.0/lib/src/api/database.dart:1:8: Error: Not found: 'dart:ffi' import 'dart:ffi'; ^

Failed to load dynamic library on Android

ArgumentError (Invalid argument(s): Failed to load dynamic library (dlopen failed: library "/data/data/com.example.newsapp/lib/libsqlite3.so" not found))

[SQLCipher] strange behavior on ios

I try to use SQLCipher with an iOS project.

All is working fine if I use only sqlcipher_flutter_libs.

But, if I add for example firebase_messaging dep, I have SQLCipher not working.
I followed the readme adding -framework SQLCipher in "Other Linker Flags".

On iOS 14.8:

• I create a database with PRAGMA KEY xxx
• I execute PRAGMA cipher_version, I have no result

On iOS >= 15.0

• I create and connect database with PRAGMA KEY xxx
• I execute PRAGMA cipher_version, I have a result
• I kill the App, restart the app
• I connect the database with PRAGMA KEY xxx, execute PRAGMA cipher_version, I have no result
• I restart the iOS device, launch the app, connect the database with PRAGMA KEY xxx, and execute PRAGMA cipher_version I have a result

You can try this with my project here https://github.com/MobiliteDev/sqlite3.dart/tree/MacOs-support-with-Flutter-Example

I'm not a native iOS developer and I don't know how to manage all XCode options, but I don't understand the behavior, do you have an idea to solve this ?

Execute should take parameters

database.execute should take parameters, just like select does, for the same reasons.

Use sqlite3_version to determine which prepare function to use

Currently we try to lookup sqlite3_prepare_v3 and, if that fails, fallback to sqlite3_prepare_v3. Instead of catching that ArgumentError, we could check the runtime library version to determine which function to use. The functions were added in 3.20.0.

sqlite3_exec rows affected

Is there a reason db.execute does not return the number of rows affected from sqlite3_exec?