Re: [sqlite] Help with confirming a couple of error traces

[Shaobo He]

Thanks, Richard. I think that I fully understand what happens now. Thanks
again for your patience. May I ask that do you see null pointer deferences
during development regularly?

Shaobo

Richard Hipp 于2017年2月1日周三 下午6:52写道：

> On 2/1/17, Shaobo He  wrote:
> > Thanks for your reply. I repeated the experiment by setting
> > db->mallocFailed upon return. You are right that there is no segmentation
> > fault (there were some assertion failures: e.g, "sqlite3OomClear:
> Assertion
> > `db->lookaside.bDisable>0' failed"). Instead I got error messages saying
> > out of memory. It makes sense now. May I ask where the unwinding is done?
> > Does it mean the program stops execution at sqlite3SrcListAppend()?
> >
>
> The assertion fault is probably because you are playing games with the
> memory allocator - pretending that a fault occurred when it did not.
> You might be able to work around that by compiling with
> -DSQLITE_OMIT_LOOKASIDE.
>
> An OOM in sqlite3SrcListAppend() will likely cause the parser to abort
> at https://www.sqlite.org/src/artifact/25ccc63ae?ln=547.
>
> The SQLite parser works by extracting tokens from the input string and
> sending them one by one into the pushdown automaton that implements
> the recognizes the LALR(1) grammar.  If you break out of that loop, it
> stops the parser dead in its tracks.  After that, all the left-over
> memory allocations are cleaned up and the tokenizer returns the
> SQLITE_NOMEM error.
>
> There are other places where a prior OOM can cause processing to
> abort.  Grep for "mallocFailed" to find them.  But the tokenizer loop
> is the most likely spot.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "DISTINCT" makes a query take 37 times as long

[Kevin O'Gorman]

On Wed, Feb 1, 2017 at 6:35 PM, Richard Hipp  wrote:

> On 2/1/17, Kevin O'Gorman  wrote:
> > I have a database of positions and moves in a strategic game, and I'm
> > searching for unsolved positions that have been connected to an immediate
> > ancestor.  I'm using Python 3.5.2, and the code looks like
>
> Please provide us with the following additional information:
>
> (1) In python, run the query: "SELECT sqlite_version(),
> sqlite_source_id();"
>
> (2) In a recent sqlite3 command-line shell (the latest release, not
> whatever 5-year-old release happens to be installed on your system)
> bring up your database and run the command:
>
>  .fullschema --indent
>
> And send in the output.
>
> (3) Download the bundle of command-line tools for your OS, then run
> the command "sqlite3_analyzer" on your database, and send in the
> output.
>
> Thanks.
>
>
>
I am unable to comply with items 2 and 3.  I can download the linux x86
versions, which I expected would run on my x86-64 system, but they don't.
Instead, even when I point right at them, they report "No such file or
directory".  I take this to mean that there is some file they do not find,
like a library, and they report the error code in their return status.

However, my "recent" software reports:
 SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> .fullschema --indent
Usage: .fullschema
sqlite> .fullschema
CREATE TABLE base64 (
b64char CHAR NOT NULL PRIMARY KEY,
b64val  INTEGER);
CREATE TABLE pos (
pnum INTEGER PRIMARY KEY AUTOINCREMENT,
ppos CHAR(64) NOT NULL,
pcensus INTEGER NOT NULL,
pscore INTEGER,
pstate CHAR DEFAULT "N" NOT NULL,
pmin INTEGER DEFAULT -99 NOT NULL,
pmax INTEGER DEFAULT 99 NOT NULL,
pmain CHAR(64));
CREATE UNIQUE INDEX pipos ON pos (ppos);
CREATE TABLE move (
mfrom INTEGER NOT NULL,
mto   INTEGER NOT NULL,
mtype CHAR NOT NULL,
mcell INTEGER NOT NULL,
mvalue INTEGER,
ma INTEGER DEFAULT -99,
mb INTEGER DEFAULT 99,
PRIMARY KEY (mfrom, mto, mcell));
CREATE UNIQUE INDEX mrev ON move (mto, mfrom, mcell);
CREATE TABLE expanded (
census INTEGER NOT NULL,
number INTEGER NOT NULL,
pos CHAR(64),
PRIMARY KEY (census, number));
ANALYZE sqlite_master;
ANALYZE sqlite_master;
INSERT INTO sqlite_stat1 VALUES('move','mrev','48329866 2 2 1');
INSERT INTO sqlite_stat1 VALUES('move','sqlite_autoindex_move_1','48329866
38 2 1');
INSERT INTO sqlite_stat1 VALUES('pos','pipos','74409802 1');
INSERT INTO sqlite_stat1 VALUES('base64','sqlite_autoindex_base64_1','64
1');
ANALYZE sqlite_master;
sqlite>

The analyzer is not included in my distribution or its repositiories, as
far as I can tell.  This is Xubuntu, which is a flavor of Ubuntu, which is
derived from Debian.

I'm not sure I want to build your entire software suite.  Perhaps you'd
care to download my database, which I freshly tar-ed and gzip-ed to
http://kosmanor.com/917/917.db.tgz
the databse is 21 GB; the tar is 3.1 GB

> >
> > #!/usr/bin/env python3
> > """Output positions that are reachable but unsolved at census 18 or
> greater
> > See page 76 of Qubic log
> >
> > Last Modified: Tue Jan 31 12:13:07 PST 2017
> > """
> >
> > import sqlite3  # https://docs.python.org/3.5/
> library/sqlite3.html
> >
> > with sqlite3.connect("917.db") as conn:
> > for row in conn.execute("""
> > SELECT DISTINCT ppos
> > FROM move JOIN pos ON mto = pnum
> > WHERE pcensus = 18 and pmin < pmax
> > """):
> > print(row[0])
> >
> > As written here, this query runs for 1193 minutes (just short of 20
> > hours).  If I remove the "DISTINCT" and instead pipe the result into the
> > sort program that comes with Linux "sort --unique" the query and sort
> takes
> > only 31 minutes.  The results are the same, and consist of 4.2 million
> rows.
> >
> > This seems extreme.
> >
> > --
> > word of the year: *kakistocracy*
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>



-- 
word of the year: *kakistocracy*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "DISTINCT" makes a query take 37 times as long

[Richard Hipp]

On 2/1/17, Kevin O'Gorman  wrote:
> I have a database of positions and moves in a strategic game, and I'm
> searching for unsolved positions that have been connected to an immediate
> ancestor.  I'm using Python 3.5.2, and the code looks like

Please provide us with the following additional information:

(1) In python, run the query: "SELECT sqlite_version(), sqlite_source_id();"

(2) In a recent sqlite3 command-line shell (the latest release, not
whatever 5-year-old release happens to be installed on your system)
bring up your database and run the command:

 .fullschema --indent

And send in the output.

(3) Download the bundle of command-line tools for your OS, then run
the command "sqlite3_analyzer" on your database, and send in the
output.

Thanks.


>
> #!/usr/bin/env python3
> """Output positions that are reachable but unsolved at census 18 or greater
> See page 76 of Qubic log
>
> Last Modified: Tue Jan 31 12:13:07 PST 2017
> """
>
> import sqlite3  # https://docs.python.org/3.5/library/sqlite3.html
>
> with sqlite3.connect("917.db") as conn:
> for row in conn.execute("""
> SELECT DISTINCT ppos
> FROM move JOIN pos ON mto = pnum
> WHERE pcensus = 18 and pmin < pmax
> """):
> print(row[0])
>
> As written here, this query runs for 1193 minutes (just short of 20
> hours).  If I remove the "DISTINCT" and instead pipe the result into the
> sort program that comes with Linux "sort --unique" the query and sort takes
> only 31 minutes.  The results are the same, and consist of 4.2 million rows.
>
> This seems extreme.
>
> --
> word of the year: *kakistocracy*
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "DISTINCT" makes a query take 37 times as long

[Kevin O'Gorman]

I have a database of positions and moves in a strategic game, and I'm
searching for unsolved positions that have been connected to an immediate
ancestor.  I'm using Python 3.5.2, and the code looks like

#!/usr/bin/env python3
"""Output positions that are reachable but unsolved at census 18 or greater
See page 76 of Qubic log

Last Modified: Tue Jan 31 12:13:07 PST 2017
"""

import sqlite3  # https://docs.python.org/3.5/library/sqlite3.html

with sqlite3.connect("917.db") as conn:
for row in conn.execute("""
SELECT DISTINCT ppos
FROM move JOIN pos ON mto = pnum
WHERE pcensus = 18 and pmin < pmax
"""):
print(row[0])

As written here, this query runs for 1193 minutes (just short of 20
hours).  If I remove the "DISTINCT" and instead pipe the result into the
sort program that comes with Linux "sort --unique" the query and sort takes
only 31 minutes.  The results are the same, and consist of 4.2 million rows.

This seems extreme.

-- 
word of the year: *kakistocracy*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with confirming a couple of error traces

[Richard Hipp]

On 2/1/17, Shaobo He  wrote:
> Thanks for your reply. I repeated the experiment by setting
> db->mallocFailed upon return. You are right that there is no segmentation
> fault (there were some assertion failures: e.g, "sqlite3OomClear: Assertion
> `db->lookaside.bDisable>0' failed"). Instead I got error messages saying
> out of memory. It makes sense now. May I ask where the unwinding is done?
> Does it mean the program stops execution at sqlite3SrcListAppend()?
>

The assertion fault is probably because you are playing games with the
memory allocator - pretending that a fault occurred when it did not.
You might be able to work around that by compiling with
-DSQLITE_OMIT_LOOKASIDE.

An OOM in sqlite3SrcListAppend() will likely cause the parser to abort
at https://www.sqlite.org/src/artifact/25ccc63ae?ln=547.

The SQLite parser works by extracting tokens from the input string and
sending them one by one into the pushdown automaton that implements
the recognizes the LALR(1) grammar.  If you break out of that loop, it
stops the parser dead in its tracks.  After that, all the left-over
memory allocations are cleaned up and the tokenizer returns the
SQLITE_NOMEM error.

There are other places where a prior OOM can cause processing to
abort.  Grep for "mallocFailed" to find them.  But the tokenizer loop
is the most likely spot.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with confirming a couple of error traces

[Shaobo He]

Thanks for your reply. I repeated the experiment by setting
db->mallocFailed upon return. You are right that there is no segmentation
fault (there were some assertion failures: e.g, "sqlite3OomClear: Assertion
`db->lookaside.bDisable>0' failed"). Instead I got error messages saying
out of memory. It makes sense now. May I ask where the unwinding is done?
Does it mean the program stops execution at sqlite3SrcListAppend()?

Shaobo

Richard Hipp 于2017年2月1日周三 下午5:28写道：

> On 2/1/17, Shaobo He  wrote:
> >
> > Basically, the error trace indicate that `sqlite3SrcListAppend` can
> return
> > a null pointer under the presence of OOM and this return value can
> > propagate to somewhere in the program, resulting in a null pointer
> > deference.
>
> An OOM condition inside of sqlite3SrcListAppend() will set the
> db->mallocFailed flag, which will cause the stack to unwind, and
> thereby prevent NULL pointer dereferences.
>
> Please repeat your experiment by forcing sqlite3SrcListAppend() to
> return NULL but at the same time set db->mallocFailed.  If you
> continue hit a segfault, that is an issue.  But I'm guessing you will
> not.
>
> We do a lot of OOM testing in SQLite.  See
> https://www.sqlite.org/testing.html#oomtesting for a summary of the
> technique.  There are 829 OOM test loops in TH3 and more in the TCL
> test suite.  We do, rarely, find OOM problems in release builds, but
> because of our OOM testing procedures such findings are quite rare.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with confirming a couple of error traces

[Richard Hipp]

On 2/1/17, Shaobo He  wrote:
>
> Basically, the error trace indicate that `sqlite3SrcListAppend` can return
> a null pointer under the presence of OOM and this return value can
> propagate to somewhere in the program, resulting in a null pointer
> deference.

An OOM condition inside of sqlite3SrcListAppend() will set the
db->mallocFailed flag, which will cause the stack to unwind, and
thereby prevent NULL pointer dereferences.

Please repeat your experiment by forcing sqlite3SrcListAppend() to
return NULL but at the same time set db->mallocFailed.  If you
continue hit a segfault, that is an issue.  But I'm guessing you will
not.

We do a lot of OOM testing in SQLite.  See
https://www.sqlite.org/testing.html#oomtesting for a summary of the
technique.  There are 829 OOM test loops in TH3 and more in the TCL
test suite.  We do, rarely, find OOM problems in release builds, but
because of our OOM testing procedures such findings are quite rare.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple web query tool

[Warren Young]

On Feb 1, 2017, at 11:45 AM, Brian Curley  wrote:
> 
> internal file shares are all that's needed to
> connect to a distributed file.

…as long as your networked file system does locking properly:

   https://www.sqlite.org/lockingv3.html#how_to_corrupt

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

Re: [sqlite] Help with confirming a couple of error traces

[Shaobo He]

Hi Richard, all,

It's so nice of you to help out. Now we understand better what we should do
to reduce the number of false positives. Thanks a lot.

I'm sorry to bother you again. But it would be great if you could provide
some feedback on the new error trace returned by our tool.

Basically, the error trace indicate that `sqlite3SrcListAppend` can return
a null pointer under the presence of OOM and this return value can
propagate to somewhere in the program, resulting in a null pointer
deference. For instance, `targetSrcList` calls `sqlite3SrcListAppend` and
returns a null pointer if its callee does it, too. `codeTriggerProgram`
calls `sqlite3Insert` with a call expression to `targetSrcList` as its
second argument, which can be a null pointer following the deduction
before. Finally, its second argument `pTabList` gets dereferenced without a
null test.

I tried to do a dummy experiment by setting the return value of
`sqlite3DbMallocRawNN` inside `sqlite3SrcListAppend` to null and ran
regression tests. A number of them failed with segmentation fault. I don't
know if this experiment is meaningful or not.

Please let me know if it makes sense. Thanks for your time and I am looking
forward to your reply.

Shaobo
Richard Hipp 于2017年1月31日周二 下午9:41写道：

> On 1/31/17, Shaobo He  wrote:
> > Hi there,
> >
> > My name is Shaobo He and I am a graduate student at University of Utah. I
> > am applying a couple of static analysis tools to C projects. The tools I
> am
> > using reports a few partial error traces about null pointer
> dereferences. I
> > was wondering if you could help me to identify whether they (described
> > below) were true bugs or just false positives. Your feedback is really
> > appreciated.
>
> They are both false-positives.
>
> >
> > 1) In function `statGet`, `sqlite3_value_blob` can return a null pointer.
> > One possible case is that `ExpandBlob(p)` returns `SQLITE_OK` and the
> > condition expression `p->n ? p->z : 0;` evaluates to null given `p->n ==
> > 0`. I tried to figure out if `p->n` can be 0 by adding an assertion
> before
> > the call site to `sqlite_value_blob` and running all regression tests. It
> > seems it cannot be for these test cases. My question is that if the case
> > described above can happen. Moreover, function `statPush` has a similar
> > error trace.
>
> The first parameter to statGet() and statPush() will always be a
> sizeof(void*)-byte blob that is in fact a pointer to an object.  So
> sqlite3_value_blob() will never return NULL there.
>
> >
> > 2) In function `walCleanupHash`, `aHash` is initialized to null and is
> > probably updated by function `walHashGet`. However, the update may not
> > happen if `walIndexPage` returns a status not equal to `SQLITE_OK`. So
> > `aHash` remains null and got dereferenced.
>
> In walCleanupHash(), the pages of the -shm file that contains the hash
> have already been allocated and initialized - otherwise
> walCleanupHash() would have never been called.  But if the -shm file
> has already been allocated and initialized, then there is no way for
> walHashGet() to fail and leave aHash uninitialized.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite with Docker and mounted volumes

[Keith Medcalf]

If it is a remote filesystem (vs a local filesystem) then you must also have 
only one connection (ever) to the database file at any given time.  Otherwise 
you may have issues.  (Note "Single User" does not mean "Single Connection")

> Hi Simon.  Thank you for your answer. It's single user and without
> concurrency.
> 
> 
> On Feb 1, 2017 18:32, "Simon Slavin"  wrote:
> 
> 
> On 1 Feb 2017, at 5:08pm, Sebastián Guevara 
> wrote:
> 
> > Hello to all. We are contemplating using SQLite from within a Docker
> > container accessing a DB on a mounted volume.
> 
> Does your setup involve two or more users accessing the data at the same
> time ?  It’s possible that locking won't work properly.
> 
> Sorry I can’t answer your question, but the above occurred to me when I
> read it.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Sqlite with Docker and mounted volumes

[Keith Medcalf]

If it is a local filesystem mounting a remote block device no problem.  If it 
is a remote filesystem, then caveats apply (most remote/network filesystems are 
very broken when it comes to locking sematics)

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Sebastián Guevara
> Sent: Wednesday, 1 February, 2017 12:09
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Sqlite with Docker and mounted volumes
> 
> Hello to all. We are contemplating using SQLite from within a Docker
> container accessing a DB on a mounted volume. I don't expect any
> performance issues if the mounted volume is a directory from the host
> machine. But what if it's a shared storage, something like Flocker (
> https://clusterhq.com/flocker/introduction/)? Have anyone of you had
> experience using SQLite in such an environment? I fear the degradation
> will
> be big, but haven't been able to test it yet.
> 
> Regards,
> 
>  Sebastian
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] SQLite - SELECT query slowing down while fetching from higher offset.

[Keith Medcalf]

> When I have 1  records in the table, it takes 20 ms to fetch 10 items
> from offset 0, and it increases to 220 ms to fetch 10 items from offset
> 9900.
> While I have 2 records in the table, it takes 20 ms to fetch 10 items
> from offset 0, 440 ms for 10 items from offset 9600 and 720 ms for 10
> items from offset 19950.
> 
> My understanding is, since index table is created in the sorted order,
> time to fetch from any offset should be the same. Why is the time to fetch
> increasing when fetching from higher offset? Why is fetching time
> increasing for the same offset when more records in the table? Is this
> expected behavior from SQLite or is there something wrong with
> schema/index/query?

When you do a "SELECT  FROM  WHERE  OFFSET 
" you are asking SQLite to run the base query and discard  result rows.  
So the time taken to perform the query is the pretty much the same as if you 
had just selected all the data and discarded it yourself (by not fetching the 
rows you did not want).

That is:

row=0
prepare(statement)
while row < x:
   step(statement)
   row++
row=0
while row < y
   step(statement)
   ... retrieve and process row data ...
finalize(statement)

is the same thing as saying  OFFSET x LIMIT y, just the database 
engine skips the rows for you.





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

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[Scott Robison]

On Wed, Feb 1, 2017 at 3:48 PM, Keith Medcalf  wrote:

>
> > On Tue, 31 Jan 2017 15:50:08 -0800 James K. Lowden <
> jklow...@schemamania.org> wrote:
>
> > Last year there was much rejoicing when Microsoft decided to bundle
> > SQLite with Windows.  That leaves me with a new question: if SQLite
> > announced its intention to move to C11 in 2018, would that perhaps
> > influence Microsoft's timeline to update its compiler?
>
> No.  Microsoft products require multiple versions of multiple Microsoft
> and third-party compilers to compile any of their products.  They will
> simply add whatever is needed to their internal compilers suite and use
> that to build the winsqlite.dll for distribution with Windows.  Windows
> does not use ICU and does not support the use of timezones, etc, so there
> is no need for them to update their compilers at all as they will never use
> anything but the most primitive of any feature available.
>
> That said, there is no problem with Visual Studio compiling the ICU module
> as it was -- it works just fine without error.  Just that when set to
> pedantic mode it produces a higher level of messages, whether they be a
> true statement of fact or not.
>
> The only issue I've run into using a Microsoft compiler is that it does
> not handle in-block initialization and declarations -- they all have to be
> at the top of a function before the first "executable" statement.  I
> believe that was a C language restriction back in the early 70's.
>

The declaration of variables have to be at the top of a scope as per ANSI
C. C99 relaxed that.
-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[Keith Medcalf]

> On Tue, 31 Jan 2017 15:50:08 -0800 James K. Lowden  
> wrote:

> Last year there was much rejoicing when Microsoft decided to bundle
> SQLite with Windows.  That leaves me with a new question: if SQLite
> announced its intention to move to C11 in 2018, would that perhaps
> influence Microsoft's timeline to update its compiler?

No.  Microsoft products require multiple versions of multiple Microsoft and 
third-party compilers to compile any of their products.  They will simply add 
whatever is needed to their internal compilers suite and use that to build the 
winsqlite.dll for distribution with Windows.  Windows does not use ICU and does 
not support the use of timezones, etc, so there is no need for them to update 
their compilers at all as they will never use anything but the most primitive 
of any feature available.

That said, there is no problem with Visual Studio compiling the ICU module as 
it was -- it works just fine without error.  Just that when set to pedantic 
mode it produces a higher level of messages, whether they be a true statement 
of fact or not.

The only issue I've run into using a Microsoft compiler is that it does not 
handle in-block initialization and declarations -- they all have to be at the 
top of a function before the first "executable" statement.  I believe that was 
a C language restriction back in the early 70's.

Of course, Microsoft compilers are somewhat, shall we say, crappy, and the code 
generated is also of the same calibre.  Not to mention that there is neither 
forwards nor backwards runtime compatibility so you need to have just about 
every version of the compiler ever produced by Microsoft to do anything useful 
(which is not required with other compilers).




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

Re: [sqlite] JPA

[Simon Slavin]

On 1 Feb 2017, at 10:01pm, Cecil Westerhof  wrote:

> 2017-02-01 19:25 GMT+01:00 Jens Alfke :
> 
>> Can SQLite be used with JPA?
>> 
>> Sure, I’ve been SQLite for years ;-)
> 
> ​Using SQLite does not mean that you use it with JPA. I use it with Python
> and Bash and also with JDBC. None of those is JPA. ;-)
> 
> How do you use the combo JPA/SQLite?​

When you quoted Jens’ post you didn’t quote his middle name.

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

Re: [sqlite] sqlite3_errmsg() after sqlitr3_exec() on ATTACHed DB

[Ward WIllats]

> On Feb 1, 2017, at 1:18 PM, Igor Tandetnik  wrote:
> 
> On 2/1/2017 10:32 AM, Ward WIllats wrote:
>> When I perform an sqlite3_exec() to DELETE too many rows in the secondary 
>> ATTACHed database and a disk or database full error occurs, I properly get a 
>> code 13 returned from the API.
>> 
>> But if I then turn around and call sqlite3_errmsg() I get "not an error" 
>> returned.
> 
> With sqlite3_exec(), the error message is returned via its last parameter, 
> not via sqlite3_errmsg(). sqlite3_exec is a wrapper that calls other SQLite 
> APIs internally, and the one that failed may not be the last call, so the 
> error may already have been reset by the time you get around to 
> sqlite3_errmsg(). Which is why sqlite3_exec() makes the effort to preserve 
> the message and forward it to you, should you choose to accept it.

Thanks Igor. Makes perfect sense.

-- Ward


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

Re: [sqlite] JPA

[Cecil Westerhof]

2017-02-01 19:25 GMT+01:00 Jens Alfke :

>
> > Can SQLite be used with JPA?
>
> Sure, I’ve been SQLite for years ;-)
>

​Using SQLite does not mean that you use it with JPA. I use it with Python
and Bash and also with JDBC. None of those is JPA. ;-)

How do you use the combo JPA/SQLite?​


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

Re: [sqlite] sqlite3_errmsg() after sqlitr3_exec() on ATTACHed DB

[Igor Tandetnik]

On 2/1/2017 10:32 AM, Ward WIllats wrote:

When I perform an sqlite3_exec() to DELETE too many rows in the secondary 
ATTACHed database and a disk or database full error occurs, I properly get a 
code 13 returned from the API.

But if I then turn around and call sqlite3_errmsg() I get "not an error" 
returned.


With sqlite3_exec(), the error message is returned via its last 
parameter, not via sqlite3_errmsg(). sqlite3_exec is a wrapper that 
calls other SQLite APIs internally, and the one that failed may not be 
the last call, so the error may already have been reset by the time you 
get around to sqlite3_errmsg(). Which is why sqlite3_exec() makes the 
effort to preserve the message and forward it to you, should you choose 
to accept it.

--
Igor Tandetnik

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

Re: [sqlite] Sqlite with Docker and mounted volumes

[Sebastián Guevara]

Hi Simon.  Thank you for your answer. It's single user and without
concurrency.


On Feb 1, 2017 18:32, "Simon Slavin"  wrote:


On 1 Feb 2017, at 5:08pm, Sebastián Guevara 
wrote:

> Hello to all. We are contemplating using SQLite from within a Docker
> container accessing a DB on a mounted volume.

Does your setup involve two or more users accessing the data at the same
time ?  It’s possible that locking won't work properly.

Sorry I can’t answer your question, but the above occurred to me when I
read it.

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

Re: [sqlite] Help with custom collation

[x]

Thanks Clemens. You’re right about changing the UTF8String* to char* as it now 
works but when trying it with a column containing Unicode characters it didn’t. 
I’d have liked to have tried it with windows wchar_t* type but If I try using



 if (sqlite3_create_collation16(SQLiteDB, "Compare", SQLITE_UTF16, 
NULL, ) != SQLITE_OK)

 throw Exception("Collation creation error");



I get the message “no such collation sequence: Compare” when running the query.







From: Clemens Ladisch
Sent: 01 February 2017 17:32
To: 
sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Help with custom collation



x wrote:
> int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2)
> {
> const UTF8String *S1 = static_cast(s1),
> *S2 = static_cast(s2);
> return 0;
> }
>
> if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL, 
> ) != SQLITE_OK)
> throw Exception("Collation creation error");
>
> S1 and S2 appear to point to NULL values.

What is "UTF8String"?  If it is anything different from "char", the code is 
wrong.

> If I change the select to ‘select ID from IDTbl order by ID collate Compare’ 
> the Compare function is never entered.
> Is this because ID is an integer column?

No, it is because the values in that column are integer values.

> Is there no way to implement a custom collation on an integer column?

There is no way to implement a custom collation for integer values.
Collations are used only for string values.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple web query tool

[Brian Curley]

Would the SQLite Manager extension on Firefox suffice? I don't know the
scope of your use case, but internal file shares are all that's needed to
connect to a distributed file. You can achieve variables through a
miscellaneous table and coalesce() as needed.

Regards.


On Wed, Feb 1, 2017 at 11:10 AM, Jay Kreibich  wrote:

> I'm looking for an *extremely* simple web tool that will allow me to
> configure a dozen or so stored queries, which people can then select and
> run on an internal server.  If the system supports a query variable or two,
> that would be fantastic, but I don't even need that.  Any thoughts?  Or do
> I dust off the PHP tutorials and spend an afternoon throwing something
> together?
>
>  -j
>
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it, but
> showing it to the wrong people has the tendency to make them feel
> uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does SQLite use field definitions?

[Donald Griggs]

The default maximum string length is one billion (10 ** 9).   You can
configure the maximum up to about twice that.
https://www.sqlite.org/limits.html

If column affinity matters in your application, you may want to declare
your column as TEXT or maybe CLOB (identical effect.)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JPA

[Jens Alfke]

> On Feb 1, 2017, at 6:19 AM, Cecil Westerhof  wrote:
> 
> Can SQLite be used with JPA?

Sure, I’ve been SQLite for years ;-)

—Jens Peter Alfke
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does SQLite use field definitions?

[Jens Alfke]

> On Feb 1, 2017, at 7:41 AM, Clyde Eisenbeis  wrote:
> 
> However, I don't see any complaints by SQLite when I use MEMO.

SQLite actually ignores the column data types completely in a table spec. You 
can store any type of data in any column of any table. (In other words, SQLite 
data typing works like JavaScript, not like C :)

SQLite has no limitations on the lengths of strings or blobs, so don’t worry 
about field widths.

See the section “Manifest Typing” here:
https://www.sqlite.org/different.html 


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

Re: [sqlite] SQLite - SELECT query slowing down while fetching from higher offset.

[Jens Alfke]

> On Feb 1, 2017, at 7:18 AM, Richard Hipp  wrote:
> 
> See also https://www.sqlite.org/rowvalue.html#scrolling_window_queries 
> 

This approach comes with a major caveat that’s not mentioned in the text: the 
data set cannot contain rows that have the same ‘order by’ values. From the 
example:

SELECT * FROM contacts
 WHERE (lastname,firstname) > (?1,?2)
 ORDER BY lastname, firstname
 LIMIT 7;
If the lastname and firstname on the bottom row of the previous screen 
are bound to ?1 and ?2, 
then the query above computes the next 7 rows.

This makes the assumption that (lastname, firstname) is unique in the table, 
i.e. the there are no two people with the same last and first names. That’s 
pretty likely in a personal address book, very unlikely in a phone book!

If there are duplicates, then if one page of results ends in the middle of a 
run of duplicates, the next page will skip the rest of the duplicates. That’s 
data loss. Sad!

The best solution is to add criteria to the ordering/comparison to make every 
row unique. For example, use (lastname, firstname, customerid). If you don’t 
have a unique value to use, you could always use `rowid`.

If that isn’t feasible (you have a no-rowid table?) you have to fall back to 
using “>=“ instead of “>” in the test, and then manually skipping the initial 
row(s) that already appeared in the last page. (And this in turn will fall if 
there’s a run of duplicate rows that’s larger than your page size … it’s 
probably better just to add a rowid or some other unique integer and go back to 
solution 1!)

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

Re: [sqlite] Simple web query tool

[Doug Currie]

On Wed, Feb 1, 2017 at 11:10 AM, Jay Kreibich  wrote:

> I'm looking for an *extremely* simple web tool that will allow me to
> configure a dozen or so stored queries, which people can then select and
> run on an internal server.


While I wouldn't call it extremely simple, the Fossil source has a
"translate" tool that supports embedding SQLite queries and HTML generation
inline with C source code for a cgi program.

Description from: http://fossil-scm.org/index.html/artifact/33b65539a12abd07

** SYNOPSIS:
**
** Input lines that begin with the "@" character are translated into
** either cgi_printf() statements or string literals and the
** translated code is written on standard output.
**
** The problem this program is attempt to solve is as follows:  When
** writing CGI programs in C, we typically want to output a lot of HTML
** text to standard output.  In pure C code, this involves doing a
** printf() with a big string containing all that text.  But we have
** to insert special codes (ex: \n and \") for many common characters,
** which interferes with the readability of the HTML.
**
** This tool allows us to put raw HTML, without the special codes, in
** the middle of a C program.  This program then translates the text
** into standard C by inserting all necessary backslashes and other
** punctuation.
**
** Enhancement #1:
**
** If the last non-whitespace character prior to the first "@" of a
** @-block is "=" or "," then the @-block is a string literal initializer
** rather than text that is to be output via cgi_printf().  Render it
** as such.
**
** Enhancement #2:
**
** Comments of the form:  "|* @-comment: CC" (where "|" is really "/")
** cause CC to become a comment character for the @-substitution.
** Typical values for CC are "--" (for SQL text) or "#" (for Tcl script)
** or "//" (for C++ code).  Lines of subsequent @-blocks that begin with
** CC are omitted from the output.

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

Re: [sqlite] Sqlite with Docker and mounted volumes

[Simon Slavin]

On 1 Feb 2017, at 5:08pm, Sebastián Guevara  wrote:

> Hello to all. We are contemplating using SQLite from within a Docker
> container accessing a DB on a mounted volume.

Does your setup involve two or more users accessing the data at the same time ? 
 It’s possible that locking won't work properly.

Sorry I can’t answer your question, but the above occurred to me when I read it.

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

Re: [sqlite] Help with custom collation

[Clemens Ladisch]

x wrote:
> int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2)
> {
> const UTF8String *S1 = static_cast(s1),
> *S2 = static_cast(s2);
> return 0;
> }
>
> if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL, 
> ) != SQLITE_OK)
> throw Exception("Collation creation error");
>
> S1 and S2 appear to point to NULL values.

What is "UTF8String"?  If it is anything different from "char", the code is 
wrong.

> If I change the select to ‘select ID from IDTbl order by ID collate Compare’ 
> the Compare function is never entered.
> Is this because ID is an integer column?

No, it is because the values in that column are integer values.

> Is there no way to implement a custom collation on an integer column?

There is no way to implement a custom collation for integer values.
Collations are used only for string values.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple web query tool

[Simon Slavin]

On 1 Feb 2017, at 4:10pm, Jay Kreibich  wrote:

> I'm looking for an *extremely* simple web tool that will allow me to
> configure a dozen or so stored queries, which people can then select and
> run on an internal server.  If the system supports a query variable or two,
> that would be fantastic, but I don't even need that.  Any thoughts?  Or do
> I dust off the PHP tutorials and spend an afternoon throwing something
> together?

I never found one, and ended up writing my own for a similar need, which I 
cannot share because it belongs to my employer.

PHP backend, using the sqlite3:: library which is a very thin shim over the 
standard C API, so there’s nothing to learn.  JavaScript front end, in .html 
and .js files stored on the server.  HTML5.  Given who I’m writing to I don’t 
need to tell you to sanitise your inputs.

My backend compares the IP address of the server the request comes from and the 
IP address of the server it’s running on.  If they don’t match it assumes it’s 
a hacking attempt.  I took a few other precautions like that.

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

[sqlite] Sqlite with Docker and mounted volumes

[Sebastián Guevara]

Hello to all. We are contemplating using SQLite from within a Docker
container accessing a DB on a mounted volume. I don't expect any
performance issues if the mounted volume is a directory from the host
machine. But what if it's a shared storage, something like Flocker (
https://clusterhq.com/flocker/introduction/)? Have anyone of you had
experience using SQLite in such an environment? I fear the degradation will
be big, but haven't been able to test it yet.

Regards,

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

[sqlite] Help with custom collation

[x]

The collation function used was as follows

int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2)
{
const UTF8String *S1 = static_cast(s1),
*S2 = static_cast(s2);
return 0;
}

Which was registered with the following code

if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL, 
) != SQLITE_OK)
throw Exception("Collation creation error");

The above is merely a tester where I’ve set a breakpoint at the ‘return 0’ 
line. I tried it with the following stmt

‘select ID from IDTbl order by Name collate Compare’.

I’m having the following problems


  1.  When it stops at the breakpoint the debugger shows the Len1 and Len2 
params with correct looking values but the S1 and S2 appear to point to NULL 
values. I just keep getting ‘???’ values. Am I casting wrongly or something.


  1.  If I change the select to ‘select ID from IDTbl order by ID collate 
Compare’ the Compare function is never entered. Is this because ID is an 
integer column? Is there no way to implement a custom collation on an integer 
column?



  1.  If I change the registration to
if (sqlite3_create_collation16(SQLiteDB, "Compare", SQLITE_UTF16, NULL, 
) != SQLITE_OK)
throw Exception("Collation creation error");
it seems to register OK but running the select yields a “no such 
collation sequence: Compare” error.

Can any kind soul cast any light on this for me.

PS I’m using Embarcadero C++ builder 10.1 Berlin on windows 10 with the 
sqlite3.c amalgamation included in my project.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Simple web query tool

[Jay Kreibich]

I'm looking for an *extremely* simple web tool that will allow me to
configure a dozen or so stored queries, which people can then select and
run on an internal server.  If the system supports a query variable or two,
that would be fantastic, but I don't even need that.  Any thoughts?  Or do
I dust off the PHP tutorials and spend an afternoon throwing something
together?

 -j


-- 
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but
showing it to the wrong people has the tendency to make them feel
uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JPA

[Cecil Westerhof]

2017-02-01 15:41 GMT+01:00 Simon Slavin :

>
> > ​It seems to be what I want.
>
> Excellent.
>
> Now, if there were just an object persistence engine for JavaScript, I’d
> be happier.  That’s what I was looking for when I came across OrmLite.
>
> Hmm.  With IndexedDB it should be possible to write one.  Maybe /I/ have
> something to do this weekend.
>

​Well, there are at least two people that will not be bored. :-P

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

Re: [sqlite] Does SQLite use field definitions?

[Clyde Eisenbeis]

I don't see MEMO listed.  I needed to use MEMO in Microsoft Access to
handle char strings longer than 256.

However, I don't see any complaints by SQLite when I use MEMO.

On Tue, Jan 31, 2017 at 10:33 AM, Hick Gunter  wrote:
> Yes. See http://sqlite.org/lang_createtable.html for details. I also suggest 
> you look at http://sqlite.org/datatype3.html too
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Clyde Eisenbeis
> Gesendet: Dienstag, 31. Jänner 2017 17:07
> An: SQLite mailing list 
> Betreff: [sqlite] Does SQLite use field definitions?
>
> In the past, when using Access as a database, I have specified field 
> definitions.  These field definitions have been used when creating a table.
>
> public const string stFIELD_DEFINITIONS = " fstPriority TEXT, fstInfo MEMO, 
> fstDateCreated TEXT, fstDateModified TEXT, fiKeyID INTEGER PRIMARY KEY ";
>
> Does SQLite (System.Data.SQLite) use field definitions too?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of the 
> intended recipient(s) only and may contain information that is confidential, 
> privileged or legally protected. Any unauthorized use or dissemination of 
> this communication is strictly prohibited. If you have received this 
> communication in error, please immediately notify the sender by return e-mail 
> message and delete all copies of the original communication. Thank you for 
> your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_errmsg() after sqlitr3_exec() on ATTACHed DB

[Ward WIllats]

Hello.

In our embedded system we have two databases ATTACHed to each other and size 
constrained with max_page pragmas directed at each. (Different filesystems: one 
is on a tmpfs in RAM, the other on JFFS).

When I perform an sqlite3_exec() to DELETE too many rows in the secondary 
ATTACHed database and a disk or database full error occurs, I properly get a 
code 13 returned from the API.

But if I then turn around and call sqlite3_errmsg() I get "not an error" 
returned. (Our too-complicated custom sqlite wrapper does this.)

Guesses:

The error string returned by sqlite3_errmsg() comes from the connection 
structure, and it says "not an error" because, indeed, there as no error on the 
primary DB.

There is a hidden handle/structure for the ATTACHed DB that has the correct 
error string, but sqlite3_errmsg() does not look there.

If I passed in an error string pointer to the exec() call itself, I would 
properly get a "disk or database full" message. In fact, that may be one of the 
reasons such a parameter exists on this API.

The rule is, if it is an exec(), get your error string from the exec() call, 
otherwise, you can get it from sqlite3_errmsg().

(This is no big deal as it is just for our internal logging, and, obviously I 
am being lazy by not just running a test in the shell to see, and, also, our 
wrapper code is likely buggy and is also obscuring what I am really doing even 
from myself (!), but thought it worth throwing this out for others and to be 
sure my conceptual model was not too far off the mark before I start hacking 
away.)

Thanks again.

-- Ward





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

Re: [sqlite] SQLite - SELECT query slowing down while fetching from higher offset.

[Richard Hipp]

On 2/1/17, Igor Tandetnik  wrote:
> On 2/1/2017 9:50 AM, Anthrathodiyil, Sabeel (S.) wrote:
>> I am facing issue with SQLite performance when having more records in the
>> table and fetching from higher offsets
>
> https://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor

See also https://www.sqlite.org/rowvalue.html#scrolling_window_queries

Note that to use the row-value feature, you'll need to update to a
newer version of SQLite.  But you should do that anyway, since newer
versions will be more than twice the speed of 3.7.10 and will be fully
backwards compatible.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite - SELECT query slowing down while fetching from higher offset.

[Igor Tandetnik]

On 2/1/2017 9:50 AM, Anthrathodiyil, Sabeel (S.) wrote:

I am facing issue with SQLite performance when having more records in the table 
and fetching from higher offsets


https://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor

Performance of a query with OFFSET N is roughly equivalent to that 
without OFFSET clause, where you retrieve and discard first N rows. 
SQLite has no means to jump directly to row N.



My understanding is, since index table is created in the sorted order, time to 
fetch from any offset should be the same.


Your understanding is incorrect. Imagine that you have a phonebook, with 
people's names listed in alphabetic order. This makes it easy to find an 
entry for a particular name, but doesn't help at all with finding an 
entry number N; for that, you still have to start from the first entry, 
and count them one by one.

--
Igor Tandetnik

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

Re: [sqlite] SQLite - SELECT query slowing down while fetching from higher offset.

[Simon Slavin]

On 1 Feb 2017, at 2:50pm, Anthrathodiyil, Sabeel (S.)  
wrote:

> When I have 1  records in the table, it takes 20 ms to fetch 10 items 
> from offset 0, and it increases to 220 ms to fetch 10 items from offset 9900.
> While I have 2 records in the table, it takes 20 ms to fetch 10 items 
> from offset 0, 440 ms for 10 items from offset 9600 and 720 ms for 10 items 
> from offset 19950.
> 
> My understanding is, since index table is created in the sorted order, time 
> to fetch from any offset should be the same.

Thanks for your schema listing, timings, and the thorough description of your 
problem.  It saved a lot of time.  Your INDEX and SELECT look fine, and the 
timings you give are plausible.  I don’t think you’re doing anything wrong.

When using an OFFSET clause SQLite has to do the following:

1) Find the first record in the index which fits the WHERE clause
2) Skip to the OFFSET number of rows after it

If the index was arranged as one continuous fixed-width list, it would be easy 
to skip 19950 entries.  But it’s not, it’s a binary tree.  So SQLite has to 
iterate through those 19950 entries in tree form, and the time to do that is 
roughly proportional to the OFFSET number.

Hope this helps.  Does it look reasonable to you ?

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

Re: [sqlite] DELETE when DB is full

[Ward WIllats]

> On Jan 31, 2017, at 3:54 PM, Simon Slavin  wrote:
> 
> On 31 Jan 2017, at 10:40pm, Warren Young  wrote:
> 
>> On Jan 31, 2017, at 2:03 PM, Ward WIllats  wrote:
>> 
>>> the delete sometimes (very rarely) fails with a 13 "disk or database full" 
>>> error. I assume because the purger is late to the party and it needs pages 
>>> in the WAL to be able to rollback if necessary.
>> 
>> If you get this error, shrink the date range or pages-to-free value by half 
>> and try again.  Repeat until it works, then repeat at that size until you’ve 
>> deleted as much as you need to.
> 
> It’s possible to do a DELETE in chunks, like you would use LIMIT on a SELECT. 
>  

Thanks for taking the time to provide the great tips and technical explanation 
by DRH. Our architecture is well suited to some kind of chunked and/or 
recursing delete so I will do some experiments today to find some reasonable 
bounding parameters and push ahead.

(I have another simple question related to this, but I'll start a new thread so 
it is not buried here.)

-- Ward



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

[sqlite] SQLite - SELECT query slowing down while fetching from higher offset.

[Anthrathodiyil, Sabeel (S.)]

Hi,
I am facing issue with SQLite performance when having more records in the table 
and fetching from higher offsets, though I have proper index in place. Using 
SQLite version 3.7.10 running on ARM Cortex A5 processor.

Here is my schema (relevant table and index)  and query

CREATE TABLE FileTable(
FileID 
INTEGER PRIMARY KEY,
FileName TEXT 
DEFAULT NULL,
FileTypeINTEGER 
DEFAULT 0,
GenreID   INTEGER 
DEFAULT 0,
ArtistID 
INTEGER DEFAULT 0,
ComposerID   INTEGER 
DEFAULT 0,
AlbumID  INTEGER 
DEFAULT 0,
TrackIDINTEGER 
DEFAULT 0,
TrackName TEXT DEFAULT NULL,
TrackDuration   INTEGER DEFAULT 
0,
InValidFlag INTEGER 
DEFAULT 0
)
CREATE INDEX Idx_TrackName_OnFileTable ON FileTable(FileType, TrackName COLLATE 
NOCASE ASC);

SELECT FileID , TrackName, FileType, InValidFlag FROM  FileTable  WHERE 
FileType = 1  AND TrackName  <>""  ORDER BY TrackName  COLLATE NOCASE LIMIT 10 
OFFSET 19950

database connection opened with flags (SQLITE_OPEN_READWRITE | 
SQLITE_OPEN_FULLMUTEX)
PRAGMA journal_mode = MEMORY
PRAGMA synchronous = FULL
PRAGMA temp_store = 2
PRAGMA cache_size = 1LL

When I have 1  records in the table, it takes 20 ms to fetch 10 items from 
offset 0, and it increases to 220 ms to fetch 10 items from offset 9900.
While I have 2 records in the table, it takes 20 ms to fetch 10 items from 
offset 0, 440 ms for 10 items from offset 9600 and 720 ms for 10 items from 
offset 19950.

My understanding is, since index table is created in the sorted order, time to 
fetch from any offset should be the same. Why is the time to fetch increasing 
when fetching from higher offset? Why is fetching time increasing for the same 
offset when more records in the table? Is this expected behavior from SQLite or 
is there something wrong with schema/index/query?

Best regards,
Sabeel
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JPA

[Simon Slavin]

On 1 Feb 2017, at 2:33pm, Cecil Westerhof  wrote:

> ​It seems to be what I want.

Excellent.

Now, if there were just an object persistence engine for JavaScript, I’d be 
happier.  That’s what I was looking for when I came across OrmLite.

Hmm.  With IndexedDB it should be possible to write one.  Maybe /I/ have 
something to do this weekend.

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

Re: [sqlite] JPA

[Cecil Westerhof]

2017-02-01 15:29 GMT+01:00 Cecil Westerhof :

> That is a fast reply. :-D
>
> 2017-02-01 15:23 GMT+01:00 Simon Slavin :
>
>>
>> > Can SQLite be used with JPA?
>>
>> I think you’re talking about persistent Java objects.  You might want to
>> look at this:
>>
>> 
>>
>> SQLite is one of the database engines it supports.  Sorry but I’m not a
>> Java programmer myself and I know nothing about it.
>>
>
> ​I will look into it. I think ​
>
> ​SQLite would be a good fit to use when you want to show JPA usage (no
> setup necessary), but then should it be possible to combine JPA and SQLite.
>

​It seems to be what I want. I have something to do this weekend. :-D

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

Re: [sqlite] JPA

[Cecil Westerhof]

That is a fast reply. :-D

2017-02-01 15:23 GMT+01:00 Simon Slavin :

>
> > Can SQLite be used with JPA?
>
> I think you’re talking about persistent Java objects.  You might want to
> look at this:
>
> 
>
> SQLite is one of the database engines it supports.  Sorry but I’m not a
> Java programmer myself and I know nothing about it.
>

​I will look into it. I think ​

​SQLite would be a good fit to use when you want to show JPA usage (no
setup necessary), but then should it be possible to combine JPA and SQLite.

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

Re: [sqlite] JPA

[Simon Slavin]

On 1 Feb 2017, at 2:19pm, Cecil Westerhof  wrote:

> Can SQLite be used with JPA?

I think you’re talking about persistent Java objects.  You might want to look 
at this:



SQLite is one of the database engines it supports.  Sorry but I’m not a Java 
programmer myself and I know nothing about it.

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

[sqlite] JPA

[Cecil Westerhof]

Can SQLite be used with JPA?

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

[sqlite] Information about Windows Iot Sql Lite

[Magni, Andrea - IT CONSULTANT]

Good Afternoon,
following this example
 
[ 
http://blog.chrisbriggsy.com/Using-SQLITE-in-Windows-10-IoT-Core-Insider-Preview/
 ]( 
http://blog.chrisbriggsy.com/Using-SQLITE-in-Windows-10-IoT-Core-Insider-Preview/
 )
 
Im trying to use Sql and Linq with c# and windows iot.
 
I'm not able to understand why randomly a program came out with "Access 
Violation".
 
Below reported the code that i'm using ...
 
DATABASE CONNECTION:[code]  public  void ConnectToDatabase(){   
// await Task.Delay(TimeSpan.FromSeconds(0));   // await 
Dispatcher.RunAsync(Windows.UI.Core.CoreDispatcherPriority.Normal, () =>
   // { path = 
Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, 
"db.sqlite");//path = Path.Combine("C:\\DB", "db.sqlite");  
  //using (var conn = new SQLiteConnection(new 
SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path))//{  
  //conn.CreateTable();//
conn.RunInTransaction(() =>//{//
conn.Insert(new Contabilita()//{//
NumberOfCredit = 1,//NumberOfMatches = 2,// 
   NumberOfTotalCoin = 3,//NumberOfWinMatches = 
1,//Antimanomission = 0//});
//});//}conn = new 
SQLite.Net.SQLiteConnection(new 
SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path);
//conn.DeleteAll();
//conn.CreateTable();//conn.Insert(new 
Contabilita()//{//NumberOfCredit = 1,   
 //NumberOfMatches = 0,//NumberOfTotalCoin = 0, 
   //NumberOfWinMatches = 0//});
conn.Commit();//stopWatch1.Start();
//conn.BeginTransaction();//Contabilita Test = new 
Contabilita();//Test.Id = 1;
//Test.NumberOfCredit = 0;//Test.NumberOfMatches = 345; 
   //Test.NumberOfTotalCoin = 1000;
//Test.NumberOfWinMatches = 236;//Test.Antimanomission = 0; 
   //conn.Update(Test);//conn.Commit();
//stopWatch1.Stop();//System.Diagnostics.Debug.WriteLine("Per 
Aggiornare il Db servono: " + Convert.ToString(ts1.Seconds) + " Secondi e " + 
Convert.ToString(ts1.Milliseconds) + " Millisecondi");//ts1 = 
stopWatch1.Elapsed;//stopWatch1.Reset();   // });   
 }[/code]DATA BASE UPDATE:[code]   public async void 
UpdateContabilita(float NumeroCrediti, float NumeroPartite, float 
NumerodiMonete, float NumeroPartiteVinte,float Antimanomissione){   
 //await Task.Delay(TimeSpan.FromSeconds(0));
//conn.BeginTransaction();//Contabilita Test = new Contabilita();   
 //Test.Id = 1;//Test.NumberOfCredit = NumeroCrediti;   
 //Test.NumberOfMatches = NumeroPartite;
//Test.NumberOfTotalCoin = NumerodiMonete;//Test.NumberOfWinMatches 
= NumeroPartiteVinte;//Test.Antimanomission = Antimanomissione; 
   //conn.Update(Test);//conn.Commit();try  
  {await Task.Delay(TimeSpan.FromSeconds(0));
await Dispatcher.RunAsync(Windows.UI.Core.CoreDispatcherPriority.High, () =>
{conn = new SQLite.Net.SQLiteConnection(new 
SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path);
conn.BeginTransaction();Contabilita Test = new 
Contabilita();Test.Id = 1;
Test.NumberOfCredit = NumeroCrediti;Test.NumberOfMatches = 
NumeroPartite;Test.NumberOfTotalCoin = NumerodiMonete;  
  Test.NumberOfWinMatches = NumeroPartiteVinte;
Test.Antimanomission = Antimanomissione;conn.Update(Test);  
  conn.Commit();// ConnectToDatabase(); 
   //conn.BeginTransaction();//var tmp = 
conn.Table().FirstOrDefault(c => c.Id == 1);
var tmp = conn.Table().FirstOrDefault();
//tmp.NumberOfCredit = NumeroCrediti;//tmp.NumberOfMatches 
= NumeroPartite;//tmp.NumberOfTotalCoin = NumerodiMonete;   
 //tmp.NumberOfWinMatches = NumeroPartiteVinte; 
   //tmp.Antimanomission = Antimanomissione;
//conn.Update(tmp);//conn.Commit(); 
conn.Close();});}catch (IOException e)  
  {