[sqlite] SQLite uclinux
Am 19.06.2015 um 21:02 schrieb Dan Kennedy: > On 06/19/2015 04:48 PM, Thomas Mager wrote: >> Hi, >> >> I have to develop an application under uclinux which should use a >> SQLite database. Since it is running finally on an embedded system it >> should not use much memory. So, I wanted to build SQLite with my >> cross compiler and omit some options to reduce the library size and >> get a very small footprint. My problem is that when I run configure >> and put some -DSQLITE_OMIT_xxx entries to CFLAGS I get an error when >> I want to build the SQLite library. See the following what I >> configure and what I get as result when linking. The switch >> -DSQLITE_OMIT_TRIGGER produces the error in this case, all other >> -DSQLITE_OMIT_xxx switches you see in the configure command below are >> running fine. >> >> This is my configure command: >> >> CC="/home/lantronix/linuxsdk/toolchains/freescale-coldfire-4.4/bin/m68k-uclinux-gcc" >> >> CFLAGS="-Os -fpic -Wall -DSQLITE_OMIT_LOAD_EXTENSION >> -DSQLITE_OMIT_WAL -DSQLITE_OMIT_AUTHORIZATION >> -DSQLITE_OMIT_PROGRESS_CALLBACK -DSQLITE_OMIT_TEMPDB >> -DSQLITE_OMIT_UTF16 -DSQLITE_OMIT_XFER_OPT-DSQLITE_OMIT_TRIGGER" >> ./configure --host=m68k-uclinux --disable-threadsafe --disable-shared >> --disable-libtool-lock >> >> Here is the output when building the library: >> >> /bin/bash ./libtool --tag=CC --mode=link >> /home/lantronix/linuxsdk/toolchains/freescale-coldfire-4.4/bin/m68k-uclinux-gcc >> >> -DSQLITE_THREADSAFE=0 -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE -Os >> -fpic -Wall -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_OMIT_WAL >> -DSQLITE_OMIT_AUTHORIZATION -DSQLITE_OMIT_PROGRESS_CALLBACK >> -DSQLITE_OMIT_TEMPDB -DSQLITE_OMIT_UTF16 -DSQLITE_OMIT_XFER_OPT >> -DSQLITE_OMIT_TRIGGER -o sqlite3 shell.o sqlite3.o >> libtool: link: >> /home/lantronix/linuxsdk/toolchains/freescale-coldfire-4.4/bin/m68k-uclinux-gcc >> >> -DSQLITE_THREADSAFE=0 -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE -Os >> -fpic -Wall -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_OMIT_WAL >> -DSQLITE_OMIT_AUTHORIZATION -DSQLITE_OMIT_PROGRESS_CALLBACK >> -DSQLITE_OMIT_TEMPDB -DSQLITE_OMIT_UTF16 -DSQLITE_OMIT_XFER_OPT >> -DSQLITE_OMIT_TRIGGER -o sqlite3 shell.o sqlite3.o >> sqlite3.o: In function `dotlockLock': >> sqlite3.c:(.text+0x378ae): warning: the use of LEGACY `utimes' is >> discouraged, use `utime' >> sqlite3.o: In function `yy_destructor': >> sqlite3.c:(.text+0x35afa): undefined reference to >> `sqlite3DeleteTriggerStep' >> sqlite3.o: In function `sqlite3Parser': >> sqlite3.c:(.text+0x48ce0): undefined reference to `sqlite3FinishTrigger' >> sqlite3.c:(.text+0x48d2c): undefined reference to `sqlite3BeginTrigger' >> sqlite3.c:(.text+0x48e62): undefined reference to >> `sqlite3TriggerUpdateStep' >> sqlite3.c:(.text+0x48e8e): undefined reference to >> `sqlite3TriggerInsertStep' >> sqlite3.c:(.text+0x48eb2): undefined reference to >> `sqlite3TriggerDeleteStep' >> sqlite3.c:(.text+0x48ed2): undefined reference to >> `sqlite3TriggerSelectStep' >> sqlite3.c:(.text+0x48f84): undefined reference to `sqlite3DropTrigger' >> collect2: ld returned 1 exit status >> make: *** [sqlite3] Error 1 >> >> >> It would be nice if you have some hints for me? > > In order to use some of the SQLITE_OMIT_* options, including > SQLITE_OMIT_TRIGGER, you need to build SQLite from the canonical > source files, not the amalgamation package. The first zip file under > "Alternative Source Code Formats" here: > > http://www.sqlite.org/download.html > > Dan. > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Hi Dan, cool - that was the solution. Thank you very much for your fast and helpful response! Best regards, Tom
[sqlite] INSERT OR REPLACE and INSERT/DELETE/UPDATE triggers
Jeuwjedmj unnecessary screenshots she's w so I whhn whhn whhn Bassi
uaaiuaai USA xqhqs?
-- Forwarded message --
From: "David Barrett" L
Cc:
> Ah, naturally I found the answer right after sending this. The secret:
>
> PRAGMA recursive_triggers = 1;
>
> As stated here: https://www.sqlite.org/lang_conflict.html
>
> "When the REPLACE conflict resolution strategy deletes rows in order to
> satisfy a constraint, delete triggers fire if and only if recursive
> triggers are enabled."
>
> Re-running my test with this shows it causes the delete trigger to fire as
> expected:
>
> sqlite> PRAGMA recursive_triggers = 1;
> sqlite> insert or replace into nvps values ( 'name1', 'value4' );
> sqlite> select * from nvps;
> name1|value4
> sqlite> select * from actions;
> inserted "name1", "value1"
> inserted "name2", "value2"
> updated "name2" from "value2" to "value2.1"
> deleted "name2", "value2.1"
> deleted "name1", "value1"
> inserted "name1", "value4"
> sqlite>
>
> Awesome. Go sqlite!
>
> -david
>
> On Sat, May 30, 2015 at 11:33 PM, David Barrett
> wrote:
>
> > I fear I already know the answer to this, but I want to ask to make
sure:
> > is there ANY way to craft a trigger that can detect when an INSERT OR
> > REPLACE trigger does a REPLACE versus an INSERT? I found this thread
which
> > suggested it wasn't possible:
> >
> >
> >
http://sqlite.1065341.n5.nabble.com/Trigger-logic-with-INSERT-OR-REPLACE-td65541.html
> >
> > But it wasn't totally clear to me, so I tried to construct a test case
> > myself -- and to my dismay, it confirmed (so far as I can tell) that a
> > REPLACE looks to the trigger exactly like an INSERT. Is there any trick
> > I'm overlooking, or is that just a current limitation of sqlite?
> >
> > The test I ran follows:
> >
> > Davids-MacBook-Air:~ dbarrett$ sqlite3
> > SQLite version 3.8.5 2014-08-15 22:37:57
> > Enter ".help" for usage hints.
> > Connected to a transient in-memory database.
> > Use ".open FILENAME" to reopen on a persistent database.
> > sqlite> /* -
> >...> Create a simple name/value pair table
> >...> -- */
> > sqlite> create table nvps ( name primary key, value );
> > sqlite>
> > sqlite> /* -
> >...> Create a table of "actions" performed on
> >...> the NVP table
> >...> -- */
> > sqlite> create table actions ( actions );
> > sqlite>
> > sqlite> /* -
> >...>Create the triggers to log actions
> >...> -- */
> > sqlite> create trigger onInsert after insert on nvps
> >...> begin
> >...> insert into actions values ('inserted "' || new.name || '", "'
||
> > new.value || '"' );
> >...> end;
> > sqlite>
> > sqlite> create trigger onUpdate after update on nvps
> >...> begin
> >...> insert into actions values ('updated "' || new.name || '" from
"'
> > || old.value || '" to "' || new.value || '"' );
> >...> end;
> > sqlite>
> > sqlite> create trigger onDelete after delete on nvps
> >...> begin
> >...> insert into actions values ('deleted "' || old.name || '", "' ||
> > old.value || '"' );
> >...> end;
> > sqlite>
> > sqlite> /* -
> >...> Insert a couple values; note that the
> >...> triggers work as expected in all cases.
> >...> -- */
> > sqlite> insert into nvps values ( 'name1', 'value1' );
> > sqlite> insert into nvps values ( 'name2', 'value2' );
> > sqlite> update nvps set value='value2.1' where name='name2';
> > sqlite> delete from nvps where name='name2';
> > sqlite> select * from nvps;
> > name1|value1
> > sqlite> select * from actions;
> > inserted "name1", "value1"
> > inserted "name2", "value2"
> > updated "name2" from "value2" to "value2.1"
> > deleted "name2", "value2.1"
> > sqlite>
> > sqlite> /* -
> >...> Attempt to insert a non-unique value into
> >...> a column with a unique constraint -- note
> >...> how it fails.
> >...> -- */
> > sqlite> insert into nvps values ( 'name1', 'value3' );
> > Error: UNIQUE constraint failed: nvps.name
> > sqlite>
> > sqlite> /* -
> >...> Finally, attempt the same insert, but with
> >...> an "or replace". Note how it works, and
> >...> correctly logs the the insert, but doesn't
> >...> also log a delete. In other words, no
> >...> trigger fired that indicates we replaced a
> >...> value.
> >...> -- */
> > sqlite> insert or replace into nvps values ( 'name1', 'value4' );
> > sqlite> select * from nvps;
> > name1|value4
> > sqlite> select * from actions;
> > inserted "name1", "value1"
> > in
[sqlite] What's the best way to pass function information to virtual table?
Jerry wrote: > 2015-06-17 1:23 GMT-07:00 Clemens Ladisch : >> Jerry wrote: >>> But it seems only general functions can be override -- it has not effect on >>> aggregate functions. >> >> The virtual table interface does not allow access to all the internals >> of the query optimizer. >> >> When there is an aggregate function, you can filter the rows that will >> be given to it, but the actual aggregation is still done by SQLite. > > Can you tell more on this? According to my understanding, the constraints > appeared in WHERE clause will be passed to virtual table (through > xBestIndex() ), then xFilter() can use the information to narrow the search > space. However, as for functions, virtual table does not know which > function is called, so the filter is not able to filter rows based on the > function. Yes; you get only constraints of a form like "MyColumn = some_value", and you don't get to know what that value is, or where it came from. >> If you can compute aggregates more efficiently than SQLite, you could >> create a separate virtual table: >> SELECT max_key FROM tab_agg >> but this would not work for more complex queries. > > I think using separate tables might not be a good solution if the table > keeps updating -- there might be consistency issues. A _virtual_ table does not necessarily imply that the data is actually stored in some real table; the values could be computed dynamically. But a separate virtual table is useful only if the data has a different structure from the data in the original table. Otherwise, using MATCH appears to be a better solution. Regards, Clemens
[sqlite] Problems in reading a record containing a blob
On Wed, 17 Jun 2015 16:59:02 +, Sivananda Nyayapathi wrote: > I have created a table to store images. The columns are > file name and file content. FileContent field is the blob. > I am using Samsung Tablet with Android Version 4.4.2. > Most of the tablets that I tested the software in, it works fine. > However, on one specific Android table I am having problems. > I have given the description of the problem below: > > I am able to store the image in the blob field. > However, while attempting to read, I get the > following error two times (as seen in the LogCat entries): > W/CursorWindow(26737): Window is full: requested allocation > 3184896 bytes, free space 2096605 bytes, window size 2097152 bytes > > Then the following error: > E/CursorWindow(26737): Failed to read row 0, column 0 > from a CursorWindow which has 0 rows, 7 columns. > > NOTES: > >* A walkthrough has been done on the code to ensure that there are no issues >in closing the cursors. All cursors are getting closed. (Used StrictMode >also to ensure this.) >* There is enough memory. The tablet with the problem is a 32GB Samsung >tablet. There is a large amount of free available memory at runtime. Nope, there probably isn't enough free RAM. The 32 GByte refers to sdcard storage size, not RAM. In Android speak, memory and storage size are confused. > * The preference is to store the image in the database as a BLOB instead of > storing the image in the file system and storing the path in the database > record (which would, probably, resolve the issue as far as retrieving the > image is concerned).. > * Android details > > o Samsung Galaxy Note 10.1, 2014 Edition > o Model Number: SM-P605V > o Kernel Version 3.4.0 > o Hardware Version: P605V.02 > o Device Memory: Total space - 32 GB; Available space - 21.94GB > > Questions: > * Could you please help me understand why the application works fine > on six Samsung tablets that I have tested the app on, but it does not work on > the seventh tablet with the same OS version? > * What is the solution to this problem? Check free RAM before extracting an image. Maybe: run garbage collection and/or push other apps to background. -- Regards, Cordialement, Groet, Kees Nuyt
[sqlite] SQLite uclinux
On 06/19/2015 04:48 PM, Thomas Mager wrote: > Hi, > > I have to develop an application under uclinux which should use a > SQLite database. Since it is running finally on an embedded system it > should not use much memory. So, I wanted to build SQLite with my cross > compiler and omit some options to reduce the library size and get a > very small footprint. My problem is that when I run configure and put > some -DSQLITE_OMIT_xxx entries to CFLAGS I get an error when I want to > build the SQLite library. See the following what I configure and what > I get as result when linking. The switch -DSQLITE_OMIT_TRIGGER > produces the error in this case, all other -DSQLITE_OMIT_xxx switches > you see in the configure command below are running fine. > > This is my configure command: > > CC="/home/lantronix/linuxsdk/toolchains/freescale-coldfire-4.4/bin/m68k-uclinux-gcc" > > CFLAGS="-Os -fpic -Wall -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_OMIT_WAL > -DSQLITE_OMIT_AUTHORIZATION -DSQLITE_OMIT_PROGRESS_CALLBACK > -DSQLITE_OMIT_TEMPDB -DSQLITE_OMIT_UTF16 > -DSQLITE_OMIT_XFER_OPT-DSQLITE_OMIT_TRIGGER" ./configure > --host=m68k-uclinux --disable-threadsafe --disable-shared > --disable-libtool-lock > > Here is the output when building the library: > > /bin/bash ./libtool --tag=CC --mode=link > /home/lantronix/linuxsdk/toolchains/freescale-coldfire-4.4/bin/m68k-uclinux-gcc > > -DSQLITE_THREADSAFE=0 -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE -Os > -fpic -Wall -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_OMIT_WAL > -DSQLITE_OMIT_AUTHORIZATION -DSQLITE_OMIT_PROGRESS_CALLBACK > -DSQLITE_OMIT_TEMPDB -DSQLITE_OMIT_UTF16 -DSQLITE_OMIT_XFER_OPT > -DSQLITE_OMIT_TRIGGER -o sqlite3 shell.o sqlite3.o > libtool: link: > /home/lantronix/linuxsdk/toolchains/freescale-coldfire-4.4/bin/m68k-uclinux-gcc > > -DSQLITE_THREADSAFE=0 -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE -Os > -fpic -Wall -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_OMIT_WAL > -DSQLITE_OMIT_AUTHORIZATION -DSQLITE_OMIT_PROGRESS_CALLBACK > -DSQLITE_OMIT_TEMPDB -DSQLITE_OMIT_UTF16 -DSQLITE_OMIT_XFER_OPT > -DSQLITE_OMIT_TRIGGER -o sqlite3 shell.o sqlite3.o > sqlite3.o: In function `dotlockLock': > sqlite3.c:(.text+0x378ae): warning: the use of LEGACY `utimes' is > discouraged, use `utime' > sqlite3.o: In function `yy_destructor': > sqlite3.c:(.text+0x35afa): undefined reference to > `sqlite3DeleteTriggerStep' > sqlite3.o: In function `sqlite3Parser': > sqlite3.c:(.text+0x48ce0): undefined reference to `sqlite3FinishTrigger' > sqlite3.c:(.text+0x48d2c): undefined reference to `sqlite3BeginTrigger' > sqlite3.c:(.text+0x48e62): undefined reference to > `sqlite3TriggerUpdateStep' > sqlite3.c:(.text+0x48e8e): undefined reference to > `sqlite3TriggerInsertStep' > sqlite3.c:(.text+0x48eb2): undefined reference to > `sqlite3TriggerDeleteStep' > sqlite3.c:(.text+0x48ed2): undefined reference to > `sqlite3TriggerSelectStep' > sqlite3.c:(.text+0x48f84): undefined reference to `sqlite3DropTrigger' > collect2: ld returned 1 exit status > make: *** [sqlite3] Error 1 > > > It would be nice if you have some hints for me? In order to use some of the SQLITE_OMIT_* options, including SQLITE_OMIT_TRIGGER, you need to build SQLite from the canonical source files, not the amalgamation package. The first zip file under "Alternative Source Code Formats" here: http://www.sqlite.org/download.html Dan.
[sqlite] Possible error in FTS5 docs
On 06/19/2015 08:34 PM, Philip Bennefall wrote: > Hi all, > > While reading through the draft documentation for FTS5, I noticed the > following in section 4.1.1: > > "The built-in auxiliary function bm25() returns a real value > indicating how well the current row matches the full-text query. The > better the match, the > larger the value returned." > > Then, a little further down it says: > > "In order to avoid this pitfall, the FTS5 implementation of BM25 > multiplies the result by -1 before returning it, ensuring that better > matches are assigned > numerically lower scores." > > Is this a mistake, or did I misunderstand something? It's a mistake. Should be "The better the match, the numerically lower the value returned" or similar. Thanks for pointing this out. Dan.
