Re: [sqlite] Android SQLite 3.8.2 issue with triggers & constraints

[Douglas Orr]

Thanks, Dan, that does the trick. I didn't know that sqlite used any
temporary files that weren't based on the database filename, but that makes
perfect sense.
Cheers,
Doug


On Wed, Jan 1, 2014 at 1:52 PM, Dan Kennedy  wrote:

> On 12/31/2013 10:03 PM, Douglas Orr wrote:
>
>> Hi,
>>
>> I have run into an issue when trying to use triggers with column
>> constraints on Android (using our build of SQLite 3.8.2, not Android's
>> built-in version from native code.) Executing the following on a
>> file-backed database fails:
>>CREATE TABLE things (number INTEGER NOT NULL);
>>CREATE TRIGGER on_insert_thing AFTER INSERT ON things BEGIN SELECT
>> NULL;
>> END;
>>BEGIN;
>>  INSERT INTO things (number) VALUES (1);
>>  INSERT INTO things (number) VALUES (2);
>>END;
>>
>> But if I do any of the following, it does not fail:
>>   - add ON CONFLICT FAIL to the NOT NULL constraint
>>   - remove the NOT NULL constraint
>>   - remove the trigger
>>   - remove the transaction BEGIN and END
>>   - use an in-memory database
>>   - run on my development machine (Linux based)
>>
>> This is reproduced in the attached program (includes SQLite 3.8.2), which
>> gives the following output when built for armeabi-v7a and run on either an
>> armeabi-v7a emulator (with SD card) or a Samsung Galaxy Note 2:
>>$ env ANDROID_NDK=/path/to/android-ndk-r9b ./build.sh && ./run.sh
>>Log(14): cannot open file at line 29016 of [27392118af]   --- I think
>> this is from the 3.8.2 amalgamation source file
>>Log(14): os_unix.c:29016: (30) open(./etilqs_mRlOFvBwZiFYwdW) -
>>
>
> It's failing to create the temporary file required for a statement
> journal. It's likely any statement that uses a temporary file will
> fail.
>
>   http://www.sqlite.org/tempfiles.html
>
> One way around the problem would be to configure SQLite not to use
> temp files using "PRAGMA temp_store = memory;".
>
> Or ensure that the environment variable SQLITE_TMPDIR is set to
> the path of a writable directory. See also:
>
>   http://www.sqlite.org/c3ref/temp_directory.html
>
> Dan.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Android SQLite 3.8.2 issue with triggers & constraints

[Dan Kennedy]

On 12/31/2013 10:03 PM, Douglas Orr wrote:

Hi,

I have run into an issue when trying to use triggers with column
constraints on Android (using our build of SQLite 3.8.2, not Android's
built-in version from native code.) Executing the following on a
file-backed database fails:
   CREATE TABLE things (number INTEGER NOT NULL);
   CREATE TRIGGER on_insert_thing AFTER INSERT ON things BEGIN SELECT NULL;
END;
   BEGIN;
 INSERT INTO things (number) VALUES (1);
 INSERT INTO things (number) VALUES (2);
   END;

But if I do any of the following, it does not fail:
  - add ON CONFLICT FAIL to the NOT NULL constraint
  - remove the NOT NULL constraint
  - remove the trigger
  - remove the transaction BEGIN and END
  - use an in-memory database
  - run on my development machine (Linux based)

This is reproduced in the attached program (includes SQLite 3.8.2), which
gives the following output when built for armeabi-v7a and run on either an
armeabi-v7a emulator (with SD card) or a Samsung Galaxy Note 2:
   $ env ANDROID_NDK=/path/to/android-ndk-r9b ./build.sh && ./run.sh
   Log(14): cannot open file at line 29016 of [27392118af]   --- I think
this is from the 3.8.2 amalgamation source file
   Log(14): os_unix.c:29016: (30) open(./etilqs_mRlOFvBwZiFYwdW) -


It's failing to create the temporary file required for a statement
journal. It's likely any statement that uses a temporary file will
fail.

  http://www.sqlite.org/tempfiles.html

One way around the problem would be to configure SQLite not to use
temp files using "PRAGMA temp_store = memory;".

Or ensure that the environment variable SQLITE_TMPDIR is set to
the path of a writable directory. See also:

  http://www.sqlite.org/c3ref/temp_directory.html

Dan.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users