Re: [sqlite] Sqlite error code 14 when using 3.31.0+

[Graham Holden]

Tuesday, March 10, 2020, 1:33:13 AM, Rowan Worth  wrote:

> On Mon, 9 Mar 2020 at 23:22, Daniel Polski  wrote:

>> Updated to 3.31.1 but my application started spitting out an error when
>> opening the database, so I tested some earlier sqlite versions to figure
>> out when the problem starts.
>> I don't get the message in versions <= 3.30.1.
>>
>> (from the applications log)
>> SQLite Version: 3.31.0
>> INFO: Database opened: /tmp/database.sqlite
>> WARNING: SQLITE error code: 14 cannot open file at line 36982 of
>> [3bfa9cc97d]
>> WARNING: SQLITE error code: 14 os_unix.c:36982: (40) openDirectory(/tmp) -
>>

> errno 40 is ELOOP, "Too many symbolic links encountered". open(2) says:

>ELOOP  Too many symbolic links were encountered in resolving
>  pathname,  or  O_NOFOLLOW  was
>   specified but pathname was a symbolic link.

> Is your /tmp/ a symlink? Sqlite seems to use O_NOFOLLOW unconditionally in
> openDirectory() since this checkin:

> https://www.sqlite.org/src/info/6a64fb6a2da6c98f

> Probably a bug? The changelog for sqlite 3.31.0 include this which is
> likely related:

> - * Add the SQLITE_OPEN_NOFOLLOW
> <https://www.sqlite.org/c3ref/open.html#opennofollow> option to
> sqlite3_open_v2() <https://www.sqlite.org/c3ref/open.html> that prevents
> SQLite from opening symbolic links.

Assuming /tmp IS a symlink, then since it's the parent directory of
the SQLite file that openDirectory fails on, I would hazard a wild
guess that this has something to do with SQLite's handling of journal
files, about which that above check-in comment notes "O_NOFOLLOW is
always included in open() system calls for journal files".

Graham Holden

(There were an earlier pair of emails to the list on 13th Feb this
year where use of O_NOFOLLOW was triggering ELOOP, but I suspect that
wasn't directly related as the problem there was "protection" code in
SQLite that was opening /dev/null was failing because /dev/null under
Solaris was itself a symbolic link (see
https://www.sqlite.org/src/timeline?c=0c683c43a62fe25c)


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

Re: [sqlite] sqliteDefaultBusyCallback and HAVE_USLEEP

[Graham Holden]

Wednesday, February 26, 2020, 11:15:14 AM, Richard Hipp  wrote:

> On 2/25/20, Peter Kolbus  wrote:
>> I noticed that sqliteDefaultBusyCallback() seems to depend directly on the
>> OS (behave differently based on SQLITE_OS_WIN||HAVE_USLEEP).  Since the
>> underlying primitive, sqlite3OsSleep(), actually uses the VFS to sleep, and
>> unixSleep() also has a roundup to whole seconds when HAVE_USLEEP is not
>> defined, any time resolution limitations are already handled there. And when
>> a custom VFS is configured, that VFS may well be able to sleep in milli or
>> microseconds using an RTOS-specific function that is not usleep() — for
>> example FreeRTOS has osDelay().
>>
>> Is there a reason sqliteDefaultBusyCallback() has this dual implementation,
>> and defining HAVE_USLEEP is correct to get better performance on platforms
>> that don’t have usleep()? Or could it be simplified?
>>

> I don't think I understand the question.  It sounds like you are
> asking why SQLite does not try to sleep for sub-second intervals on
> systems that do not support usleep()?

I've not looked at the source, so I don't know whether what I think
Peter is saying is correct or not, but what I THINK Peter is implying
there's POSSIBLY some decision (based on HAVE_USLEEP) in the "core"
SQLite code about what sort of timeout to ask for, before the request
gets handed over to the VFS to implement.

Presumably, in the default VFS implementation(S) this also uses
HAVE_USLEEP to decide whether sub-seconds times are possible or not.

However, a custom VFS may have its own way of implementing sub-second
delays (but does not implement usleep() itself)... it could therefore
honour a request for sub-second delay if asked. However, to be asked,
it has to "lie" about supporting usleep() and define HAVE_USLEEP.

It may simply be a concern over semantics: i.e. whether HAVE_USLEEP
means ("implements the function usleep()" vs. "can do short delays
somehow") or it might be a deeper problem in that if you define
HAVE_USLEEP (to allow a custom VFS to be asked to sleep for short
amounts) it also causes other parts of the SQLite code to try and use
usleep() when it isn't implemented).


Graham


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

Re: [sqlite] After deleting data from a FTS table and doing VACUUM, unwanted data remains

[Graham Holden]

Tuesday, February 25, 2020, 3:00:09 PM, Luuk  wrote:

[tests snipped]

> So, the index does not grow indefinitely

> On 25-2-2020 14:00, Graham Holden wrote:
>> It is an interesting problem. And the above is just guesswork... It would
>> be good to verify experimentally that the index really does grow
>> indefinitely

Just to avoid (more) confusion, that speculation was from Dan's email
from 2014 (I pasted his response, quoting the original email from
andrewmo who raised the issue: perhaps I should have tried to add
another layer of quoting...)

IIRC (and I probably don't), I think it was found that there wasn't
any "grow indefinitely" involved. I also suspect the cyclic nature of
the post-vacuum numbers (27..52..2..27..52) is indicative of what (I
think) Dan was describing, namely that the "clean-up" isn't always as
"aggressive" as it potentially could be:

>  If you then add even more data so that
> the 16th level-N b-tree is created, everything gets merged together and
> we're back in the optimal state - everything in a single b-tree. However -
> this b-tree is deemed to be a level-N+1 b-tree. Meaning that this time,
> much more data will have to be added before everything is merged together
> again.

Regards,
Graham Holden


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

Re: [sqlite] After deleting data from a FTS table and doing VACUUM, unwanted data remains

[Graham Holden]

"fix" anything.

Dan.

ENDS - Mail from Dan Kennedy, 4 May 2014 - ENDS

Tuesday, February 25, 2020, 11:52:59 AM, Matt Kloss  
wrote:

> Dear sqlite users,

> I noticed that when you delete lines from a FTS virtual table, somehow there 
> is some data remaining in the sqlite db, so that's it does not shrink much in 
> size.

> $ sqlite3 test.sql "CREATE VIRTUAL TABLE tab USING fts5(x)"
> $ curl -s https://www.wikipedia.org | tr -cd '[:alnum:][:space:]' > wikipedia
> $ ls -lh wikipedia test.sql
> -rw-r--r-- 1 mkloss mkloss 24K feb. 25 06:55 test.sql
> -rw-r--r-- 1 mkloss mkloss 54K feb. 25 06:56 wikipedia
> $ sqlite3 test.sql ".import wikipedia tab" && ls -lh test.sql
> -rw-r--r-- 1 mkloss mkloss 148K feb. 25 06:56 test.sql
> $ sqlite3 test.sql "delete from tab" && ls -lh test.sql
> -rw-r--r-- 1 mkloss mkloss 148K feb. 25 06:56 test.sql
> $ sqlite3 test.sql "VACUUM" && ls -lh test.sql
> -rw-r--r-- 1 mkloss mkloss 124K feb. 25 06:56 test.sql

> I would expect the db size to be 24K (not 124K), as it was when the table 
> "tab" was empty.

> I noticed that some data remains in the tab_XXX tables, but less than 700 
> bytes. That's nowhere near the 100K of added cruft:

> $ for t in tab_{config,content,data,docsize,idx}; do echo "select * from 
> $t;"; done | sqlite3 test.sql | wc -c
> 682

> So here is my questions:
> (1) How do you really cleanup a db with FTS tables after deleting some lines?
> (2) If there is no way to remove the cruft, does that mean that adding and 
> deleting lines will constantly inflate the db size?

> $ sqlite3 --version
> 3.31.1 2020-01-27 19:55:54 
> 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1

> Thank you for your help,
> Regards,

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



Regards,
Graham Holden


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

Re: [sqlite] Check constrain execution timing change?

[Graham Holden]

Friday, January 31, 2020, 9:39:07 PM, Simon Slavin  wrote:

> On 31 Jan 2020, at 9:27pm, Keith Medcalf  wrote:

>> You are however correct that this is an "application consistency"
>> problem more than an SQLite problem and it is a lot of change for
>> little actual benefit.  

> How about this ?

> A program (possibly part of the SQLite precompiled suite, possibly
> from a third party) goes through any SQLite database as an integrity
> checker.  One of its jobs is to check that column constraints  
> are not violated.  This cannot possible be wrong.  A SQLite database
> with violated CHECK clauses is, by definition, corrupt. 

> However, because the checks are performed on the values input, not
> the values stored, some data in the database does violate a CHECK
> constraint.  

> Can this happen, given the behaviour Keith identified ?  If so, I
> would say that something is wrong.

Probably not, at least not with the example he used.

In Keith's example (assuming I understand it correctly), you are
stopped from inserting/updating something that -- if you did the
insertion/update WITHOUT the checks -- would be stored in such a
way that it would later pass "pragma integrity_check" if the checks
were in place.

So, inserting '1' (a string) into a field with integer affinity, but
no constraints would be allowed, and result in 1 (an integer) being
stored.

If that column were to magically gain a "check (typeof(x) == 'integer')"
constraint, it would pass "pragma integrity_check" (because by now it
contains an integer, not the string that was originally inserted).

If you now repeated the original insert (of the string '1') with the
check constraint in place it will now REJECT the insertion, because
the type-of-the-thing-being-inserted doesn't meet the constraint (it's
a string).

So it doesn't allow you to create an inconsistent database (as defined
by check constraints), but does stop some ways of inserting/modifying
data that would have created valid data if the checks weren't there.

Graham


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

Re: [sqlite] Default isolation_level for sqlite3.connect?

[Graham Holden]

Friday, January 31, 2020, 1:14:26 AM, Peng Yu  wrote:

> I still have a hard time to understand what the difference is
> according to the python manual. It keeps saying see somewhere else in
> the python manual. But I don't see where it explains the differences
> between the differences comprehensively and clearly.

Let me offer another answer to go alongside Keith's excellent
response.


CORE SQLITE3 ENGINE

(Ignoring Python; as used from "C" or the command-line shell).

This operates in "auto-commit" mode. When a statement is executed that
is not already inside an EXPLICIT (=user-created) transaction, then it
is wrapped inside an IMPLICIT (=engine-created) transaction. Therefore:

   INSERT INTO TABLE_A ...
   BEGIN 
   INSERT INTO TABLE_B ...
   DELETE FROM TABLE_C ...
   COMMIT
   SELECT ... FROM TABLE_D

will essentially be turned into:

   BEGIN
   INSERT INTO TABLE_A ...
   COMMIT
   BEGIN 
   INSERT INTO TABLE_B ...
   DELETE FROM TABLE_C ...
   COMMIT
   BEGIN
   SELECT ... FROM TABLE_D
   COMMIT

where the auto-generated BEGINs are the equivalent of BEGIN DEFERRED
(the SQLite engine's default if you just use BEGIN).

To me, this behaviour is straightforward, predictable, and (mostly)
does what you (as a programmer) wants (but see the notes at the end).


PYTHON WRAPPER

Driven by what I assume were the best intentions, the Python wrapper
will, by default, try to be "helpful" and decide when IT will wrap
your SQL statements in BEGIN...COMMIT statements. How, and when it
does this is controlled by the ISOLATION_LEVEL.

If isolation_level is explicitly set to "None", then the Python
wrapper does nothing, and you get the "normal" behaviour of the core
SQLite3 engine as described above. As Keith recommends, and from what
I've seen on this mailing list, this is highly recommended when using
the Python wrapper. It makes everything done in Python work the same
way as in C or the command-line shell, with no unexpected surprises.

If you DON'T use isolation_level=None, then the Python wrapper will
enter what Keith calls "magic mode". It will SOMETIMES add its own
BEGIN ... COMMIT statements around your Python calls to SQLite.

By default (if you don't specify isolation_level) these added calls
will be simply BEGIN (which, the core SQLite engine treats as BEGIN
DEFERRED). Otherwise, you can explicitly specify an isolation_level
of DEFERRED (the same as leaving it blank), IMMEDIATE or EXCLUSIVE.

As Keith notes, the problem with letting Python insert these BEGIN
statements is that (a) different versions of the Python wrapper do
things in different ways, and (b) at least some of those ways "get
it wrong" (e.g. by not understanding things like WITH ... INSERT).

Overall, it seems far better to disable the Python wrapper's "magic"
mode (by using isolation_level=None) and have a far more predictable
environment where YOU manage transactions yourself.


FINAL NOTES

I said above that the auto-commit behaviour of the SQLite engine
"mostly" does what you want. One alternate behaviour would be to
insist that EVERY statement was inside an EXPLICIT transaction (and
raise an error if not). Most of the time, this would be somewhat
annoying (especially when using the command-line tool), but would
force the user/programmer to consciously think about when to use
transactions. For example, if you have:

SELECT data1 FROM ...
SELECT data2 FROM ...

then because each SELECT will be automatically wrapped inside their
own BEGIN ... COMMIT, it is entirely possible (="guaranteed to happen
the first time it is deployed in production") that some other process
will alter the data between the two SELECTs and you end up with
inconsistent values for "data1" and "data2". The answer, of course, is
to do:

BEGIN [something]
SELECT data1 FROM ...
SELECT data2 FROM ...
COMMIT

which guarantees consistency.

From my (moderate) use of SQL, (almost) always using explicit
transactions (rather than relying on the auto-commit behaviour of the
core) falls in to the same sort of list of  "good practices" as
explicitly declaring the columns in a SELECT statement (as opposed to
using SELECT *) and (depending on the programming language) always
using braces in an IF statement, even if there's only one statement
to conditionally execute. They're not essential, but tend to guard
against silly mistakes.


Regards,
Graham Holden


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

Re: [sqlite] Behavior change around triggers between 3.19 and 3.30

[Graham Holden]

Thursday, January 30, 2020, 12:24:40 PM, Dominique Devienne 
 wrote:

> The strange thing though, is that I can't repro on a small example.
> Despite using not_there in the trigger, and doing DML and ALTER TABLE,
> still doesn't fail the same way as in production. What could be the cause? 
> --DD

I suspect it may only kick-in if you use ALTER TABLE to rename either
a table or column. As I understand it, the old behaviour was to JUST
rename the table/column; the new behaviour also scan triggers etc. and
renames any references to the table/column as well. Since a new column
cannot (shouldn't?) be referenced by a trigger, there's no real need
to check them.

Graham


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

Re: [sqlite] New word to replace "serverless"

[Graham Holden]

Tuesday, January 28, 2020, 4:25:49 PM, Richard Hipp  wrote:

> Since suggesting daemon-less, someone else (I'll have to research who,
> exactly) suggested "server-free", which I think I like more.

What? A free server with every copy of SQLite?
That sounds like a good deal :-)

Graham


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

Re: [sqlite] Missed index opportunities with sorting?

[Graham Holden]

Monday, December 09, 2019, 1:32:40 PM, Digital Dog  
wrote:

> On Sat, Dec 7, 2019 at 3:50 AM Simon Slavin  wrote:

>> On 7 Dec 2019, at 2:26am, Shawn Wagner  wrote:
>>
>> > The first one uses the index for all sorting, but the second one only
>> uses it for sorting a, not b. I feel like the descending sort could make
>> use of the index too, just reading the b sections backwards to get the
>> right order. Is there something I'm overlooking that would make this sort
>> of optimization impractical or otherwise a bad idea?
>>
>> Hmm.  Try running ANALYZE and then doing the EXPLAIN QUERY PLAN lines
>> again.
>>
>> But I think that without 'chunkiness' information (how many values columns
>> a and b have) it would not be worth doing the complicated programming
>> required for reverse-mini-scanning of that index.  The programming is quite
>> complicated and unless your index "b" is chunky it won't save you much time
>> over the plan shown.
>>

> So it's better to allocate memory, block the execution until all rows are
> read and use cpu time to do unnecessary sorting that could easily be
> avoided by just reading index backwards?

It should only need to collect-and-sort groups of rows with equal "a"
values, rather than ALL rows (and the EXPLAIN QUERY PLAN seems to
support this). In theory, it should be possible to emit rows in these
groups, but I don't know enough about reading VDBE (output of EXPLAIN
SELECT...) to know if it does this or accumulates ALL rows before
emitting the first.

>   Is it really so hard to program
> it? I do not think so.

I've no idea. If reverse-mini-scanning an index ISN'T "so hard", then
from past experience there's a moderate chance one of the devs is
looking to see if it CAN be done. However, the fact that it HASN'T
been done, when reverse-mini-scanning seems an "obvious" optimisation,
suggests to me it is not as easy as one might think, and that the
potential saving isn't that great. Especially when you could always
create a second index on "a, b DESC".

> However the heuristic to decide when to do backward index scan needs to be
> smart enough to select this only as last resort optimization, just before
> falling back to explicit sort.

This "decision problem" is -- I believe -- a key factor in deciding
whether to add any specific optimisation. For every (potential)
optimisation that could be added, you need to add code that decides
whether it's worth using that optimisation. The savings from USING the
optimisation only benefit SOME queries, but the code that decides
whether or not to USE the optimisation has to be executed for many,
many more queries. The risk is that you slow lots and lots of queries
down a little bit, for only an occasional gain for the few queries
where the optimisation does make sense.

I don't know the actual code, but I'm guessing the optimiser never
really "knows" there is an index where doing a "reverse-mini-scan"
could help. I suspect it (a) realises there isn't a "prefect" index;
(b) finds an index that sorts as much as possible (in this case, the
only index), and (c) fulfils the rest of the ORDER BY requirements
using a temp b-tree. (With, probably, some exceptions for
optimisations that HAVE been implemented). 

To implement a reverse-mini-scan, not only would you have to add the
code to do the reverse scan itself, but the code that does (b) above
(find AN index that sorts as much as possible) would need to be much
more complex and consider ALL indices that start by ordering "a ASC"
to see if any of THOSE would allow a reverse-scan. It might even need
to see if a "seemingly worse" index (+reverse-scan) might be better
than an "obviously better" index. For instance, with more fields, an
"ORDER BY a, b DESC, c" might be better served by an index on
"( a, b, c )" with reverse-scan than a seemingly better index on
"( a, b DESC, d ).

Graham


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

Re: [sqlite] Result set column names

[Graham Holden]

Monday, December 09, 2019, 7:25:25 AM, Clemens Ladisch  
wrote:

> The SQL-92 standard actually says:
>
> |Syntax Rules
> |
> |9) Case:
> |
> |   b) If the i-th  in the  does not
> | specify an  and the  of that
> |  is a single , then the
> |  of the i-th column of the result is C.
> |

Presumably the third line of clause (b) of the standard SHOULD have
read: "is a single  C,", otherwise "is C" has
nothing to refer to.

Graham

(And it's not a transcription error, according to:
https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt


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

Re: [sqlite] database disk image is malformed

[Graham Holden]

I've been having problems with my email system... I don't think
earlier attempts at sending have made it to the list, but if they
did, apologies for any duplication...

Monday, November 11, 2019, 5:46:05 PM, Jukka Marin  wrote:

>> On 11 Nov 2019, at 5:13pm, Jukka Marin  wrote:
>> 
>> > The main process first opens the databases and checks that their
>> > version matches that of the software and if not, the databases are
>> > closed and initialized by running a script.
>> > 
>> > After closing the databases, main process forks the children and
>> > all processes (including main process) open the databases and use
>> > their own connections.
>> > 
>> > What I was trying to ask was this:  If any of the children dies
>> > (a bug in the code), main process will restart the child.  At
>> > this point, the main process has the databases open, so the new
>> > child receives the connections as well.  What should I do now?
>> 

This isn't from personal experience, but (possibly misremembered)
snippets from this list and (possibly incorrect) deductions from
them...

The problem (or, perhaps, "a" problem) with passing SQLite connections
across fork() is, I think, how Linux?/POSIX? deals with file-locks on
the underlying file-handle. IIRC, if both parent/child process share a
file-handle, and one of them closes that file, then ALL file-level
locks (which is what SQLite uses) on that handle are released: not
just the ones created by the terminating process.

What I *think* this may mean is that re-spawned children will inhereit
the open file-handle of the SQLite connection opened by the parent
after it initially fired all child processes. Even if the (re-spawned)
child never make use of that connection or the file-handle, when it
next dies (either a natural death or another unexpected termination),
then the OS will close the inherited file-handle and release all
file-locks on it (that would have been created by the parent process).

At this point, the parent process will THINK it has appropriate
file-locks and that it is safe to access the database file, but to all
the other (child) processes, it does not appear to have any locks, and
they too may try to access the database file. And that way, madness
lies!

I *suspect* Simon's "The conservative way to do it" (close the
connection/file-handle around re-spawns) may be more necessary than
originally thought. (Alternatively, perhaps, have whatever database
access the parent is doing happen in another child process, and have
the parent "just" monitoring processes and re-spawning as needed).

Of course, I may be wrong, or the "releasing all locks" may only apply
in some circumstances... hopefully someone with more direct experience
can confirm or deny.

Graham


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

Re: [sqlite] Rounding Error

[Graham Holden]

Monday, November 04, 2019, 8:41:48 AM, Adrian Sherwin 
 wrote:

> Hi,

> I would like to report the following as a bug in SQLITE:

> The SQLITE "round" function fails to round between 4.1 and 4.6% of numbers
> correctly to x decimal places when held as x+1 decimal places.

> The simplest example I have found with x=1 is:
> "select round(1.15,1)"
> Result: "1.1" (should be 1.2)

This is almost certainly because (according to
https://www.h-schmidt.net/FloatConverter/IEEE754.html) the number
"1.15" cannot be expressed exactly in floating-point; the nearest
expressible number being 1.1497615814208984375, and presumably
it is the floating-point representation that is being rounded, not
the "string" representation.

Graham


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

Re: [sqlite] Backward compatibility

[Graham Holden]

Tuesday, October 29, 2019, 7:37:40 AM, Thomas Kurz  
wrote:

>> If an earlier version of SQLite attempts to read a database file
>> that contains a generated column in its schema, then that earlier
>> version will perceive the generated column syntax as an error and
>> will report that the database schema is corrupt.   

> ...which leads me to some confusion. So there actually *are*
> features breaking compability? Wouldn't that be a chance for full
> date/time support as well?

There are different types of compatibility (that probably have proper
names). The main one that the SQLite devs (seem to) want to avoid is
the case where someone has an existing app/program that uses SQLite.
They upgrade their copy of SQLite to the latest, and that program
suddenly stops working because of a change in SQLite. This type of
"breaking compatibility" is very rare (the change in behaviour of
ALTER TABLE RENAME, which now fixes references to a renamed table,
is one of the few that comes to mind).

The situaton described above is different: to get that situation, a
database has to have been deliberately created/modified in a LATER
version of SQLite and then handed over to a program using an earlier
of SQLite to process.  Note: simply _creating_ the database with a
later version is [almost always] safe (so long as you only use
features the older version understands): the problem comes if you
deliberately make use of a feature (e.g. "generated columns") that
only the later version supports. This is sort-of the equivalent of
asking Microsoft Word 2.0 to open a file created with Word 2007.

Graham


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

Re: [sqlite] sqlite segfault bug report

[Graham Holden]

Monday, October 28, 2019, 5:46:55 AM, ???  wrote:

> Code:
 
> BEGIN;
> CREATE TABLE t1(a);
> ALTER TABLE t1 ADD c CHECK (b>c);
> PRAGMA writable_schema=on;
> INSERT INTO t1 VALUES(2,3);
 
> Version: 3.29.0, 3.22.0

On "SQLite version 3.30.1 2019-10-10 20:19:45" this returns an
error after the third line:

Error: no such column: b


Regards,
Graham Holden


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

Re: [sqlite] ALTER TABLE ADD COLUMN

[Graham Holden]

Wednesday, October 23, 2019, 1:53:10 PM, x  wrote:

> From the documentation

> “A record might have fewer values than the number of columns in the 
> corresponding table. This can happen, for example, after an ALTER TABLE ... 
> ADD COLUMN SQL statement has increased the number of
> columns in the table schema without modifying preexisting rows in the table. 
> Missing values at the end of the record are filled in using the default value 
> for the corresponding columns defined in
> the table schema.”

> Suppose you have a table with say 5 columns that are almost always
> the default value (probably zero or null). Does the above suggest
> you should make them the last 5 columns in the table as the last
> n columns that are the default value won’t take up space? Or does
> this state just exist after ADD COLUMN but any rows added thereafter
> use the space?  

I believe it can only happen after an ADD COLUMN, however, zero or
NULL values will, essentially, take zero space whereever they are in a
row. If you look in-and-around:

   https://www.sqlite.org/fileformat.html#record_format,
 
you will see that the "type code" used for each column in a row has
specific values for "NULL" and zero (0 and 8, respectively). This
means that where those NULL/zero occurs, no extra space is used to
hold the value.

Graham


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

Re: [sqlite] Network file system that support sqlite3 well

[Graham Holden]

Wednesday, October 16, 2019, 1:22:58 AM, Gary R. Schmidt  
wrote:

> On 16/10/2019 10:38, Jens Alfke wrote:
>>
>>> On Oct 15, 2019, at 3:47 PM, Peng Yu  wrote:
>>>
>>> I'd like to use sqlite3 db files on many compute nodes. But they
>>> should access the same storage device for the sqlite3 db files.
>>
>> Why not use an actual client-server database system like MySQL? It's 
>> optimized for this use case, so it incurs a lot less disk (network) I/O.
>>
> To second what Jens has written - use the right tool for the job.

> SQLite is *not* the right tool for this sort of job.

> MySQL/MariaDB/PostGRESQL/Oracle/SQL Server/DB2/... are what you should 
> be looking at.

Or, depending on the type and variety of the operations your "compute
nodes" need to do, write a pair of what could be relatively simple
client-server programs that police access to the SQLite DB (which the
server will be accessing as a local file).

For this to work (well, easily) you'd be looking to create "higher-
level" functions ("store this data set", "retieve this data set" etc.)
that the clients (on the compute nodes) can ask the server (next to
the SQLite file) to perform on their behalf. 

Of course, if the need is for the compute nodes to have full access to
the SQLite API, this approach wouldn't be suitable -- you shouldn't
(IMHO) be trying to make the SQLite API itself work across the
network**.

Graham


** I believe someone has tried/succeeded in doing something like
   this, but I don't know the details off the top of my head.


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

Re: [sqlite] Network file system that support sqlite3 well

[Graham Holden]

Wednesday, October 16, 2019, 11:43:25 AM, Peng Yu  wrote:

> On 10/16/19, Simon Slavin  wrote:
>> Unfortunately, no.  Multiuser SQLite depends on locking being implemented
>> correctly.  The developers haven't found any Network File Systems which do
>> this.  Unless one of the readers of this list wants to tell me otherwise.

> What do you mean "Multiuser"? The files will be accessed by the same
> users from multiple compute nodes. Is that what you call "Multiuser"?

Simon almost certainly meant "User" as in "something (remote) USING a
shared (central) database file"... not users-as-in-usernames. In other
words, multiple nodes/processes/clients/"things" trying to access a
central SQLite file using some kind of network file system.

Graham


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

Re: [sqlite] Date time input

[Graham Holden]

Tuesday, October 08, 2019, 7:39:40 PM, James K. Lowden 
 wrote:

> OK, but it needs a better name.  What better place than here to debate
> that?  ;-)
>

SQLOfALot

(providing you pronounce SQL as ess-queue-ell and not sequel)

Graham


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

Re: [sqlite] Fastest way to SELECT on a set of keys?

[Graham Holden]

Another possibility... INSERT the keys in a temporary table and do an 
appropriate JOIN.Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: Simon Slavin  
Date: 13/09/2019  17:51  (GMT+00:00) To: SQLite mailing list 
 Subject: Re: [sqlite] Fastest way to 
SELECT on a set of keys? On 13 Sep 2019, at 5:38pm, Jens Alfke 
 wrote:> Does anyone have intuition or actual knowledge 
about which approach is better? Or know of a 3rd better approach?My guess is 
(b), but it will depend on your particular setup.  Depends on cache size, 
storage speed, whether your OS is real or virtualized, etc..  I don't think the 
overhead of preparation will cause much of a delay.Solution (b) will require 
more memory than (a) since it has to keep the array of all keys in memory until 
the command is finished.There is, of course, solution (c): read every row and 
check in your software whether it has one of the keys you want.  This requires 
preparing and executing one statement.  If your list of keys covers most of the 
rows this may be fastest.  And it uses the least 
memory.___sqlite-users mailing 
listsqlite-users@mailinglists.sqlite.orghttp://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] dates, times and R

[Graham Holden]

Monday, August 12, 2019, 1:06:00 PM, Gabor Grothendieck 
 wrote:

> The whole point of this is to make it as easy as possible for the user.
> With other backends the database handles the types but with sqlite
> the user has to get involved.

> ...

>   sqldf("select d as d__Date, d + 1 as nextDay__Date from DF", method
> = "name__class")
>   ##dnextDay
>   ## 1 2000-01-01 2000-01-02

NOTE: I don't know R, so I may be talking rubbish...

I *think* the idea of whoever suggested it was not that the *user*
should have to do things like the above, but that the INTERFACE
between R and SQLite should do it. In my simplistic view the interface
"knows" the input is a date ("as.Date(..)") and so would tack the
"...__date" hint on to the column name when talking to SQLite. Coming
back out of SQLite, the interface would recognize the "...__date" and
turn the value into an R date.

Graham Holden



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

Re: [sqlite] Feature request: import MySQL dumps in CLI

[Graham Holden]

Wednesday, August 07, 2019, 9:16:17 PM, Thomas Kurz  
wrote:

>> I highly doubt the SQLite team will undertake this task. They
>> Surely have the skill to do so, but their priority is the one
>> software product you desire to use, undoubtedly due to its
>> high utility.  I doubt that utility would exist if they were
>> to wander off tacking the conversion challenge for the other
>> popular database systems.

Another reason they might not want to is that by making it an
"official feature" then -- at least to some degree -- they would
need to track changes in MySQL/MariaDB that might break things.
 
Graham Holden



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

Re: [sqlite] Proposal: SQLite on DNA

[Graham Holden]

Thursday, July 25, 2019, 3:56:53 PM, Simon Slavin  wrote:

> May I humbly suggest that the development team look into porting to a new 
> platform:

> <https://www.knowstuff.org/2019/07/forget-silicon-sql-on-dna-is-the-next-frontier-for-databases/>

> " Simply put, at this pace, there soon won’t be enough data storage
> and compute material to go by. Which is why people have been looking
> into alternative storage media for data for a while now.  
> Using DNA to store data, strange as it may sound at first, actually
> makes lots of sense. And now researchers have made a breakthrough,
> enabling them to integrate DNA storage in PostgreSQL, a  
> popular open source database. "

> What would it be ?  A VFS ?

As in Viral File System?

Graham



Regards,
Graham Holden



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

Re: [sqlite] RFE: allow parameters in PRAGMA statements

[Graham Holden]

I suspect this doesn't happen because from an in-expert bit of poking,
I don't think PRAGMA commands "really" get compiled to VDBE (which is
what I believe "preparing" a statement does).

In the transcript below, although the _query_ seems to use VDBE (to
return the result in "p4", the two attempts to _set_ the value have
the same VDBE (which doesn't feature the value being set, and
according to https://www.sqlite.org/opcode.html does nothing except
immediately expire all prepared statements before halting).

(And I suspect the "query" is a red-herring: I suspect the VDBE is
generated _after_ the pragma value has been retrieved).

Graham

sqlite> .eqp full
sqlite> .ver
SQLite 3.28.0 2019-04-10 13:24:35 
09435b5700a2650816ad9ffa628be5fa19da62369c30329801feb5e840463c7e
msvc-1500
sqlite> pragma defer_foreign_keys;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 1 000  Start at 1
1 Int64  0 1 0 0  00  r[1]=0
2 ResultRow  1 1 000  output=r[1]
3 Halt   0 0 000
0
sqlite> pragma defer_foreign_keys=1;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 1 000  Start at 1
1 Expire 0 0 000
2 Halt   0 0 000
sqlite> pragma defer_foreign_keys=0;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 1 000  Start at 1
1 Expire 0 0 000
2 Halt   0 0 000

Tuesday, June 11, 2019, 2:59:21 PM, Mike King  wrote:

> I’ve got a similar issue. I user user_version to store a .net version
> object I’ve serialised as an int. I convert them to/from text to get them
> in and out using the pragma. It would be a nice to have for pragmas to
> support parameters.

> Cheers

> On Tue, 11 Jun 2019 at 14:43, Wout Mertens  wrote:

>> Hi,
>>
>> I am using the user_version pragma for implementing an event-handling
>> database. I'd like to prepare the statement to update it, e.g. `PRAGMA
>> user_version = ?`.
>>
>> However, sqlite3 won't let me do that, so I just run the text query every
>> time with the number embedded.
>>
>> Not a huge problem, more of a surprise. Would be nice if it worked.
>>
>> Wout.



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

Re: [sqlite] sqlite-users Digest, Vol 138, Issue 4

[Graham Holden]

Throwing a wild idea out: Can you try using single-quotes
(e.g. "...where dancename = 'Waltz'..."). Single-quotes are meant to
be used for text-literals; double-quotes are meant to "protect"
field/table names where they clash with keywords (or contain "odd"
characters). Additionally, do you by any chance have a field called
"Waltz" in your table?

Tuesday, June 04, 2019, 7:26:12 PM, Doug  wrote:

> Sqlite version is 3.27.1.

> I have no indexes defined.

> Here is a query I just ran from bash:

> select songfile_id,dancename,dancegroupname from songfiletable where 
> songfile_id=377;
> 377|Waltz|American Smooth

> ... and another:

> select songfile_id,dancename,dancegroupname from songfiletable where 
> dancename like "Waltz";
> 377|Waltz|American Smooth
> 388|Waltz|American Smooth
[snip]
> ... and yet another:

> select songfile_id,dancename,dancegroupname from songfiletable where 
> dancename = "Waltz";
sqlite>>

> Nothing selected using "=".



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

Re: [sqlite] How to set access permissions to protect a database file?

[Graham Holden]

You should probably also make sure that users cannot alter the tcl
file through which they access the database file; probably something
like: 

chown reading_room /path/to/reading_room.tcl
chmod 644 /path/to/reading_room.tcl

(It's possible that you will also need execute permission on the file,
in which case change "644" to "755").

Graham

Sunday, May 26, 2019, 12:52:29 PM, Adrian Ho  wrote:

> On 26/5/19 7:49 AM, Markos wrote:
>> I made a program (reading_room.tcl), with Sqlite running on Debian 9,
>> to control the books of a reading room.
>>
>> I implemented an authentication system for common users and
>> administrator users in the reading_room.tcl program.
>>
>> Now I want that any user logged in the Linux be able to run the
>> program reading_room.tcl, which will access the database (books.db)
>>
>> But I want to protect the file books.db so that only the the program
>> reading_room.tcl can access the books.db file (to read or write). But
>> that no user could delete or write to the file books.db (only the
>> program reading_room.tcl)

> The standard Unix permissions/ACLs architecture doesn't support this use
> case directly. A relatively simple and bulletproof way to achieve what
> you want is to use sudo to get everyone running reading_room.tcl as a
> separate (non-login) user.

> As root, run "useradd reading_room", then "visudo" to add the following
> line to /etc/sudoers:

> ALL    ALL = (reading_room) /path/to/reading_room.tcl

> Then, "chown reading_room /path/to/books.db" and "chmod 600
> /path/to/books.db" to ensure that only user "reading_room" can access
> the DB.

> Finally, create a "reading_room" script that your users will run:

> #!/usr/bin/env bash

> sudo -u reading_room /path/to/reading_room.tcl



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

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Graham Holden]

Friday, April 12, 2019, 7:23:31 PM, Jim Dossey  wrote:

> I'm doing the INSERT first, without a BEGIN ... COMMIT transaction,
> then I'm doing the SELECT.  Shouldn't the INSERT do it's own COMMIT
> which should make the new row visible to the SELECT?   Should I add
> a BEGIN ... COMMIT around the INSERT?

Have you "finished" executing the SELECT statement? If you're not
using explicit BEGIN...COMMIT, then each statement runs in its own
transaction. However, the implicit transaction started with a SELECT
does not complete until either sqlite3_step() returns SQLITE_DONE or
you explicitly call sqlite3_finalize(). As I understand it, if, for
example, you only expect one row from your SELECT statement and execute
sqlite3_step() once to retrieve that row, then the SELECT statement
(and the implicit transaction it is running in) is still active. As
such, any NEW SELECT statements you prepare/step will still be in the
same transaction and so wouldn't see the result of any INSERTs.



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

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

[Graham Holden]

Wednesday, April 10, 2019, 2:03:24 PM, Peng Yu  wrote:

> Given the much larger disk space required, for an occasional search of
> the data, it seems that it makes no sense to use sqlite3 if disk space
> is a major concern.

Whether it "makes sense" to use SQLite or not, probably only you can
decide. If the "zgrep" solution works, and is "fast enough", then
there may not be any point in using SQLite. However, possible
advantages (depending on your "use cases") include being able to
ENFORCE the uniqueness of the string column, and -- depending on the
types of query involved -- benefiting from an index which in many
cases _should_ speed up the search.

And, in response to a couple of your latter emails (where you give
_some_ details about your data): you will have a much better chance
of people on this list being able to help you if you give a more
complete description of the problem: include some sample data from
your TSV and the types of searches you want to do. People can then
give much more informed help.



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

Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

[Graham Holden]

Wednesday, April 10, 2019, 11:16:07 AM, Shawn Wagner  
wrote:

> It should be pragma_function_list(). Note the parentheses.

That doesn't make a difference if the shell hasn't been built with
-DSQLITE_INTROSPECTION_PRAGMAS, and they don't appear necessary for a
pragma that IS built-in (such as "pragma_database_list"):

SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> select * from pragma_database_list;
0|main|X:\V1LS\z
sqlite> select * from pragma_database_list();
0|main|X:\V1LS\z
sqlite> select * from pragma_function_list ;
Error: no such table: pragma_function_list
sqlite> select * from pragma_function_list() ;
Error: no such table: pragma_function_list



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

Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

[Graham Holden]

Wednesday, April 10, 2019, 10:28:55 AM, Luuk  wrote:

> On 10-4-2019 10:28, Kees Nuyt wrote:

>> sqlite> select * from pragma_function_list;

> sqlite> select * from pragma_function_list;
> Error: no such table: pragma_function_list

From the help page (https://www.sqlite.org/pragma.html#pragma_function_list)
this option is only available "...if SQLite is built using the
-DSQLITE_INTROSPECTION_PRAGMAS compile-time option.". My (standard) 
copy of 3.27.2 doesn't support this either, so I'm guessing Keet must
have a custom-built version.



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

Re: [sqlite] Drop table error with existing views

[Graham Holden]

See https://www.sqlite.org/lang_altertable.html ... the ALTER TABLE
command, by default, now alters references to it in triggers/views.
To get the old behaviour, which I believe you need, use:

PRAGMA legacy_alter_table=ON

Graham

Monday, April 01, 2019, 11:23:45 AM, Domingo Alvarez Duarte 
 wrote:

> Hello !

> I'm using sqlite3 for a long time and I used to be able to drop tables 
> that have views without error before but now it seems that it is not 
> possible anymore.

> I mainly use it to restructure tables (add/move/remove fields) with 
> something like this:

> 

> PRAGMA foreign_keys=OFF;

> BEGIN;

> CREATE TABLE "banks___new"(
>      id integer primary key,
>      account_id integer references ledger_accounts(id),
>      name varchar not null collate nocase unique,
>      new_field integer, -- for example here I'm adding a new 
> field
>      notes text collate nocase
> );

> INSERT INTO "banks___new"(
>      "id",
>      "account_id",
>      "name",
>      "notes"
>      )
> SELECT
>      "id",
>      "account_id",
>      "name",
>      "notes"
> FROM "banks";

> DROP TABLE "banks";

> ALTER TABLE "banks___new" RENAME TO "banks";

> PRAGMA foreign_key_check;

> COMMIT;

> PRAGMA foreign_keys=ON;



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

Re: [sqlite] Difference between ".import" and tcl/sqlite3 "copy"

[Graham Holden]

Monday, March 11, 2019, 6:59:56 PM, E.Pasma  wrote:

> I can confirm that this has nothing to do with the sqlite version,
> as it is so in the tcl binding from the current release (3.28.0.).
> There is no "mode" method or paramater like in the shell.  I'd
> probably work around this by an update in SQL, after the import. Or
> import into a view, with an "instead of insert" trigger to insert
> cleaned data into the table.   
> Sorry, E. Pasma.

Thanks for the confirmation. For what I'm doing (a mostly one-off
data import) a sufficient work-around is to use Tcl to "exec" the
SQLite shell and have it run ".import".

Regards,
Graham Holden



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

[sqlite] Difference between ".import" and tcl/sqlite3 "copy"

[Graham Holden]

I'm using SQLite through Tcl, and am having a problem with the
sqlite3/Tcl "copy" command (similar to the shell's ".import" command).

Given "test.csv"
1,"aaa","bbb ccc"

Using the shell, I get the following -- the double-quotes from the CSV
are not stored in the database:

create table test ( id integer primary key, str1 text, str2 text ) ;
.mode csv
.import test.csv test
.mode column
select * from test ;
1   aaa bbb ccc

but using the following Tcl script:

package require sqlite3
sqlite3 db :memory:
db eval "create table test ( id integer primary key, str1 text, str2 text )"
db copy ignore test test.csv ","
db eval "select * from test" { puts "|$id|$str1|$str2:" }
db eval "update test set str1='aaa', str2='bbb ccc'"
db eval "select * from test" { puts "|$id|$str1|$str2:" }

produces:
|1|"aaa"|"bbb ccc":
|1|aaa|bbb ccc:

showing the double-quotes are stored in the database.

Is there a way to not get the double-quotes stored? NOTE: It may be a
"version thing"... the version of SQLite bundled-in with the copy of
Tcl I'm using is a little old (3.8.7.1) whereas the shell is 3.27.2.
Unfortunately, I can't easily switch the Tcl version at the moment
(but I might have to if it is something that's been fixed).

On a related note, under Windows, with CR-LF terminated lines in the
CSV file, the CR also gets stored in the string (that's the reason for
the ":" in the above test script: the value for 'str2' becomes

  "bbb ccc"\r

(with an embedded carriage-return), but I can work around this by
switching to LF-terminated lines.

TIA for any help,
Graham



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

Re: [sqlite] inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

[Graham Holden]

On Wednesday, February 13, 2019, 11:06:07 PM, Thomas Kurz
 wrote:

>> I guess a missing DEFAULT automatically implies DEFAULT NULL, so
>> the behavior of ALTER should be correct whilst CREATE seems to
>> forget to reject the statement.

I suspect the difference is you can CREATE a NOT NULL column with an
(implied) DEFAULT NULL because there are no rows yet, and so long as
you always supply a NOT NULL value when INSERTing, nothing is
violated. However, when trying to add a NOT NULL (implied) DEFAULT
NULL column, any _existing_ rows would violate the condition (since
their values for the new column would be NULL) so it cannot be
allowed. In theory, I think it _could_ be allowed if the table is
empty (but I don't know whether SQLite checks this).



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

Re: [sqlite] Inconsistent behavior in sqlite3_set_authorizer() and error messages

[Graham Holden]

For the last point, using the SQLITE_OMIT_TEMPDB option, did you compile from 
the amalgamation or the full cannonical sources? According to 
https://www.sqlite.org/compile.html#_options_to_omit_features, many "OMIT" 
options only work when the cannonical sources are used (my guess if compiling 
the amalgamation is the parser still recognises TEMP as a keyword but there's 
no code to implement it, hence the "logic error").Graham HoldenSent from my 
Samsung Galaxy S7 - powered by Three
 Original message From: Joshua Thomas Wise 
 Date: 04/02/2019  03:18  (GMT+00:00) To: 
sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Inconsistent behavior in 
sqlite3_set_authorizer() and
error messages Hello,I found some behaviors that should probably be 
considered bugs (and should be fixed and/or documented).Let’s start the 
explanation by observing some behavior that actually is correct and consistent. 
Below, we observe which type of action is reported by sqlite3_set_authorizer(), 
given some SQL input:"CREATE TEMP TABLE foo(x)” -> 
SQLITE_CREATE_TEMP_TABLE"CREATE TEMP VIEW foo AS SELECT 1” -> 
SQLITE_CREATE_TEMP_VIEW"CREATE TEMP TRIGGER foo BEFORE INSERT ON t BEGIN SELECT 
1; END" -> SQLITE_CREATE_TEMP_TRIGGERSo far so good. But what happens when 
we use the “temp.foo” syntax instead of the TEMP keyword?"CREATE TABLE 
temp.foo(x)" -> SQLITE_CREATE_TABLE"CREATE INDEX temp.foo ON t(x)" -> 
SQLITE_CREATE_TEMP_INDEX"CREATE VIEW temp.foo AS SELECT 1" -> 
SQLITE_CREATE_VIEW"CREATE TRIGGER temp.foo BEFORE INSERT ON t BEGIN SELECT 1; 
END" -> SQLITE_CREATE_TEMP_TRIGGERUh-oh. We have some inconsistencies 
there. CREATE TABLE and CREATE VIEW report their regular CREATE_* variants, 
while CREATE INDEX and CREATE TRIGGER do report their TEMP_* variants.I 
recommend that either all or none of those statements should report their 
TEMP_* variants to the sqlite3_set_authorizer() callback. If the decision is 
made to not use TEMP_* variants in these cases, the schema name (“temp”, in 
this case) should ideally be included as an argument to the 
sqlite3_set_authorizer() callback.I also found strange inconsistencies 
regarding error messages. If we execute the 4 statements above, but this time 
with the SQLITE_OMIT_TEMPDB compile-time option present, we get the following 
error messages (respectively):'SQL logic error''cannot create a TEMP index on 
non-TEMP table “t”''SQL logic error’'SQL logic error’However, if we replace 
“temp.foo” with “miss.foo” in each of those statements, we get the much better 
error message:'unknown database miss’All of the observations described in this 
email were very surprising to me. Hopefully they can be fixed and/or 
documented.Best 
regards,Josh___sqlite-users mailing 
listsqlite-users@mailinglists.sqlite.orghttp://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] curious discovery about geopoly module

[Graham Holden]

> There are, of course, multiple apps on my system that use sqlite3.dll - 
> including the Bricscad app that I am running my vba code from.Speculating 
> somewhat: Have you tried updating the copy Briscad is using? If one version 
> is already in memory, you _may_ have problems persuading Windows to load a 
> different version, and -- even if you can -- I've a feeling that SQLite might 
> not like that.Graham.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] The "natural" order of the query results.

[Graham Holden]

In particular, there's a not implausible optimisation opportunity that could 
alter things...
In the general case, the execution of "GROUP BY a, b" will "naturally" involve 
a sort on "a, b" to bring all the "to be grouped" entries together. In the OP's 
case, there's a primary key on "a, b" so there can only be one entry per group. 
As I understand it, it would therefore be valid to just scan the table and emit 
the "groups" (of size one) in whatever order they happen to be stored.
Graham.
Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: Simon Slavin  
Date: 17/09/2018  09:09  (GMT+00:00) To: SQLite mailing list 
 Subject: Re: [sqlite] [EXTERNAL]  The 
"natural" order of the query results. 
On 17 Sep 2018, at 7:19am, John Found  wrote:

> Yes, of course, but you forgot about INDEXED BY clause. It will force using 
> particular index. So, the query planner will always use exactly this index, 
> regardless of how optimal it is and
> as long as GROUP BY and ORDER BY are working the same way, this gives some 
> guarantee for the ordering without ORDER BY clause.

No.  Just because it works now doesn't mean it will continue to work that way 
in the future, unless the documentation says so.

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] Save text file content in db: lines or whole file?

[Graham Holden]

Tuesday, August 07, 2018, 7:36:26 PM, Abramo Bagnarawrote:

> I've verified that not only substr(data, start, len) loads entire row,
> but with my surprise that also retrieving a sibling column (I've tried
> to add another column "info blob not null") loads entire row (i.e.
> including whole data column)...

> IOW "select info from content;" retrieves also data column content.

> Weird, isn't it?

If the "info" column is added to the end of the table, then this is a
well-known "optimisation opportunity"... because of the way SQLite
stores records, it has to read past all intervening columns to get to
the ones required. If "info" is after your BLOB, the BLOB has to be
read to get to it. A common "good practice" is to ensure any BLOB (or
similar large fields) are placed at the end of the table definition.

If the "info" column is added _before_ the BLOB, it _should_ (as I
understand it) not need to read the blob to handle "select info from
content".

Graham



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

Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

[Graham Holden]

Hello Yuri,

Friday, August 03, 2018, 12:06:14 AM, Yuri wrote:

> On 8/2/18 3:46 PM, Keith Medcalf wrote:
>> You are required to have a UNIQUE index on the PARENT KEYS in a foreign key 
>> relationship.
>>
>> Depending on whether the PARENT:CHILD is 1:1 or 1:N you need either
>> a UNIQUE (1:1) or regular index on the child key. 

> Why is index on PARENT KEY in a foreign key relationship required for 
> inserts? Missing index should slow down deletion of the target record in 
> FK, but insertions shouldn't need checking if such parent key is already 
> present or not. Insertion in the parent part of FK checks if the target 
> exists or not. If it exists, insertion succeeds, if not, it fails. It 
> doesn't need to check if another parent key already exists.

> Yuri

I believe David Raymond explained this: with deferred foreign key
checks, once there has been a violation, it not only needs to check
whether a new row _breaks_ a constraint, it needs to check whether
the new row _fixes_ a previous violation. For the latter, while you
don't _need_ the index Keith is suggesting, it prevents the slow-down
you are seeing. 

Graham



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

Re: [sqlite] Database file with a nonstandard sqlite_seuence schema causes crash.

[Graham Holden]

> The documentation says:
> **"Warning: misuse of this pragma can easily result in a corrupt database
> file."**

But it would be nice if corrupted database didn't crash the application ;)

If the database is (or appears) corrupted, terminating immediately or 
"crashing" is probably the only safe thing to do, in case you make matters 
worse.

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

Re: [sqlite] clocks in SQLite

[Graham Holden]

Tuesday, May 15, 2018, 7:49:32 PM, Roman wrote:

> Job 1847 ends at 16:44:11 and job 1852 starts at 18:47:46 (and
> actually subsequently dies as evidenced by its stop being NULL).

How do you KNOW that your program didn't spend 2 hours 3 minutes
either not noticing job 1847 had finished, or deciding what to do next
once it had noticed? Do you KNOW, say, that THAT process didn't die
and was restarted?

> Next job, 2283, is started BEFORE job 1852. RunID is INTEGER PRIMARY
> KEY and for this purpose is auto incrementing. Jobs are also running
> on other nodes, therefore runID is not contiguous for this node. 

Job 2283 starts NEARLY 24 hours AFTER job 1852!

> runID  hostname   start   stop
> -- -- --- ---
> 1841   loginnode4 2018-05-14 07:53:42 2018-05-14 10:05:41
> 1843   loginnode4 2018-05-14 10:05:41 2018-05-14 12:18:05
> 1845   loginnode4 2018-05-14 12:18:05 2018-05-14 14:30:50
> 1847   loginnode4 2018-05-14 14:30:50 2018-05-14 16:44:11
> 1852   loginnode4 2018-05-14 18:47:56
> 2283   loginnode4 2018-05-15 18:18:59

> Could it indicate other issues than the clock itself? It is highly
> unlikely that clock happened to jump forward at the time when 1852
> was finishing (at 16:44:11). Time of start of 2283 looks  
> correct, agrees with my watch, because I started this job manually.

> Roman

Regards,
Graham Holden



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

Re: [sqlite] An artificial query whose outcome is changed after left join reduction

[Graham Holden]

Off-topic addendum minor typo:
"They both store content has a sequence"
in the first paragraph should be "as".


Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: Richard Hipp  Date: 
10/04/2018  15:50  (GMT+00:00) To: SQLite mailing list 
 Subject: Re: [sqlite] An artificial 
query whose outcome is changed after
  left join reduction 

Off Topic Addendum:

Last night I wrote up a new document [1] on why the SQLite project
does not use Git.  The URL above
(https://www.sqlite.org/src/timeline?c=cf171abe=12) is a good
example of Reason #1.  I bring this up simply because both the article
and the URL are both fresh in my mind.

[1] https://sqlite.org/whynotgit.html

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

Re: [sqlite] Fossil Feature Request

[Graham Holden]

Just adding "?ln" seems to add line numbers:
https://www.sqlite.org/src/artifact/810fbfebe12359f1?ln
Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: J Decker  Date: 
30/03/2018  20:48  (GMT+00:00) To: SQLite mailing list 
 Subject: Re: [sqlite] Fossil Feature 
Request 
On Fri, Mar 30, 2018 at 12:46 PM, Richard Hipp  wrote:

> On 3/30/18, J Decker  wrote:
> >  Sqlite's Fossile browser can't link line numbers...
> >
> >
> > Add ability to link to lines of source...
> >
> >
> > was trying to share this as another reference for getting UTF8 characters
> > from strings
> >
> > #define READ_UTF8(zIn, zTerm, c)
> > https://www.sqlite.org/src/artifact/810fbfebe12359f1
>
> Just add the "ln=155-165" query parameter.  Like this:
>
>   https://www.sqlite.org/src/artifact/810fbfebe12359f1?ln=155-165
>
>
Good to know for future reference - but there's no line numbers shown in
the first place


> --
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

[Graham Holden]

Thursday, March 22, 2018, 7:18:08 PM, Peter Michaux wrote:

> I think there are a couple main offenders with

>> BEGIN;
>> INSERT OR IGNORE ... ;
>> UPDATE  ;
>> COMMIT;

> The first is that it is bulky. If this is in the application code then it
> has to be repeated for each desired UPSERT and it has to be repeated in the
> code of each application that uses the database.

From what I can remember, most of the suggested formats for an UPSERT
command (except perhaps R. Smith's "NOT"/"KEEP" idea) involve two
lists of fields/values, so would be similarly "bulky" as separate
INSERT and UPDATE commands, and need similar amounts of application
code.

> The second is that it seems so inefficient in the case of a new row being
> inserted. The row is inserted and then immediately updated. Why do both
> operations when only one is needed?

There are at least two alternatives (hopefully I've given correct
attribution): 

  o  "INSERT OR IGNORE" the "key" fields (ensures they now exist)
 followed by an UPDATE for the remaining fields (Simon Slavin).

  o  "INSERT OR IGNORE" all values; if sqlite3_changes() indicates
 nothing changed (because the record is already present), perform
 the UPDATE (Olivier Mascia).

> Is it possible to write a stored procedure that checks a result of the
> INSERT OR IGNORE and only attempts the UPDATE if the row already existed?
> That would at least move the bulky code out of the application and into the
> database. Also it seems it would be more efficient.

In some cases, e.g. where the data to be UPSERTed is in a (possibly
transient) table you can use a TRIGGER to perform the UPDATE part. (At
the risk of blowing my own trumpet, see an answer of mine on
StackOverflow: https://stackoverflow.com/a/22481731/2096401).


Regards,
Graham



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

Re: [sqlite] Why some options (ex. SQLITE_ENABLE_EXPLAIN_COMMENTS, SQLITE_ENABLE_DBPAGE_VTAB) are permanently enabled in Makefile.am, but are documented as user-defined?

[Graham Holden]

Presumably the ".am" of "Makefile.am" refers to the single-file amalgamation. 
However, some of the compile-time options can only be set when building from 
the cannonical sources or generating the amalgamation... once the amalgamation 
has been generated, those options are "baked in".
Graham.

Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: Yuri  Date: 13/03/2018  
07:07  (GMT+00:00) To: General Discussion of SQLite Database 
 Subject: [sqlite] Why some options (ex. 
SQLITE_ENABLE_EXPLAIN_COMMENTS,
  SQLITE_ENABLE_DBPAGE_VTAB) are permanently enabled in Makefile.am,
  but are documented as user-defined? 
Makefile.am has:
AM_CFLAGS = @THREADSAFE_FLAGS@ @DYNAMIC_EXTENSION_FLAGS@ @FTS5_FLAGS@ 
@JSON1_FLAGS@ @ZLIB_FLAGS@ @SESSION_FLAGS@ -DSQLITE_ENABLE_FTS3 
-DSQLITE_ENABLE_RTREE
sqlite3_CFLAGS = $(AM_CFLAGS) -DSQLITE_ENABLE_EXPLAIN_COMMENTS 
-DSQLITE_ENABLE_DBPAGE_VTAB -DSQLITE_ENABLE_STMTVTAB -DSQLITE_ENABLE_DBSTAT_VTAB

But all of these options (with -D) are documented here 
http://www.sqlite.org/compile.html as user-settable.


Yuri

___
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] Enabling MMAP in Android

[Graham Holden]

Have you actually got speed issues, or is this just a case of "this should be 
faster"? If you DO have speed issues, my experience from reading this list is 
that most can be improved with better SQL, better indices, or better interplay 
between readers and writers. Only if those have been exhausted should you be 
looking at compile options.
Graham.


Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: advancenOO 
 Date: 11/12/2017  14:04  (GMT+00:00) To: 
sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Enabling MMAP in Android 
I find MMAP is able to be used for db files(getPageMMap()) but is not enabled
by default.
I wonder if it is recommended to use MMAP in Android? As I think MMAP is one
way to improve the speed performance(I use speedtest1.c to evaluate).

At the same time, Mmap() is only used for reading by default, not writing.
But why? And in what situation I can compile with the
-DSQLITE_MMAP_READWRITE option?

By the way, is there any other recommended compile-options? I find some
references in  http://www.sqlite.org/compile.html
  , but I am wondering if there is any
other method to enhance performance?

Thanks for your help.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
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] Unexpected echo when setting locking_mode

[Graham Holden]

I'm not against rationalising the PRAGMA commands, but this wouldn't help with 
the example you gave. "page_size" is more accurately "the page size I would 
like it to be" and so can be "successfully" set at any time (providing it's a 
power of 2). The only time the current setting gets _acted_ upon is for a new 
database or _during_ a VACUUM command. (Unless I've misinterpreted something).
Graham

Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: Simon Slavin  
Date: 19/11/2017  02:07  (GMT+00:00) To: SQLite mailing list 
 Subject: Re: [sqlite] Unexpected echo 
when setting locking_mode 


On 19 Nov 2017, at 2:01am, Kees Nuyt  wrote:

> The same happens for 
>   pragma journal_mode=wal;

Perhaps the PRAGMAs should be reviewed for consistency: that all PRAGMAs which 
change values should output their new value.

This might provide a useful piece of diagnostic information for some users.  
For example suppose someone changes the page_size using this:

PRAGMA page_size = 16384;

They change page_size to 16384 but they don’t know that that works only for an 
empty database, or after a VACUUM.  The PRAGMA outputs 4096 and, although they 
don’t yet know why, they do at least know that their change didn’t work 
properly.

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] Running sums and averages

[Graham Holden]

>>> Is there anything I can do to reduce the time taken?

>  > < Simon correctly advised >
>  > Do it in your favourite programming language rather than SQL.

> Let me be even more clear :

>  Memory is cheap and most servers have plenty.

>  Processors are fast and most servers have multiple with many cores.

>  Select the entire table of columns you need into memory.
>  Write a little code.

> No it won't scale very well into millions of rows but I could easily run
> a test and I will bet many mnay many dollars that processing the sums in
> memory is orders of magnitude faster than SQL.

You shouldn't even need to read the entire table (or view) into
memory: just read row-by-row, and for each field, keep a running
total and the count of non-NULL values. From these you can calculate
your total and both types of average.

Graham



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

Re: [sqlite] Proof that a line has been modified

[Graham Holden]

 Original message From: Jens Alfke  Date: 
07/09/2017  19:32  (GMT+00:00) To: SQLite mailing list 
 Subject: Re: [sqlite] Proof that a line 
has been modified 


> On Sep 7, 2017, at 10:24 AM, Igor Tandetnik  wrote:
> 
> "Device will refuse to install" is precisely an instance of "security built 
> in at the OS level".


Yes, but that's beside the point; it wasn't the relevant part of the example. 
Any software, privileged or not, can verify the signature and detect whether 
the binary has been modified. That's what the OP wants.

—Jens
That's fine for an unchanging binary signed in a secure environment and 
released to the world; what the OP wants is a similar level of security but for 
an ever-changing file (where the private keys must be accessible for "normal 
use").
Graham.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version 3.20.0 release candidate 2

[Graham Holden]

Going on the snippet in David's correction only:
 That arbitrary SQL was suppose to be run inside a sandbox where it could do ** 
no ** harm even if exploited
It probably should be "was supposed".
Graham.
 Original message From: David Wellman 
 Date: 25/07/2017  16:46  (GMT+00:00) To: 'SQLite 
mailing list'  Subject: Re: [sqlite] 
Version 3.20.0 release candidate 2 
Also, I've just spotted the following (** characters surround the changes):

1) 
On the same page under " 2.1. Upping The Threat Level", the fourth paragraph 
starts:
 Because the pointer is passed in the t1.t1 column...

I think this is meant to be:
 Because the pointer is passed in the ** t1.cx ** column...

2)
On the same page under " 2.1. Upping The Threat Level", the first bullet under 
the sixth paragraph starts:
 The WebSQL interface to webkit allowed any webpage to to run arbitrary SQL 
in the browser for Chrome and Safari. That arbitrary SQL was suppose to be run 
inside a sandbox where it could do not harm even if exploited

I thin this is meant to say:
 The WebSQL interface to webkit allowed any webpage to to run arbitrary SQL 
in the browser for Chrome and Safari. That arbitrary SQL was suppose to be run 
inside a sandbox where it could do ** no ** harm even if exploited

3)
On the same page under " 2.3. Pointer Leaks"
The second sentence of the first para says:
   In other words, subtypes on pointer values prevents attacks using SQL 
statements like this:

I think it should say:
   In other words, subtypes on pointer values ** prevent ** attacks using SQL 
statements like this:

Or possibly:
   In other words, ** using ** subtypes on pointer values prevents attacks 
using SQL statements like this:

4)
On the same page, under " 5. Summary"

The second sentence of key take-awy #3 starts:
   Instead, use the interfaces designed to facility secure pointer passing:

I think it should be:
   Instead, use the interfaces designed to facilitate secure pointer passing:


Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United 
Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Wellman
Sent: 25 July 2017 16:31
To: 'SQLite mailing list'
Subject: Re: [sqlite] Version 3.20.0 release candidate 2

Hi,

Minor doc error at https://sqlite.org/draft/bindptr.html 

The first paragraph under "2. A Brief History Of Pointer Passing In SQLite" 
starts with:
   It is sometimes convenient for SQLite extensions to communicatin non-SQL 
values...

I'm not sure what it is meant to say, but probably not that :-) Possibly 
something like:
    It is sometimes convenient for SQLite extensions to communicate non-SQL 
values...

Cheers,
Dave



Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United 
Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: 25 July 2017 16:23
To: General Discussion of SQLite Database; sqlite-dev
Subject: [sqlite] Version 3.20.0 release candidate 2

We are restarting the test process for SQLite 3.20.0.  There is a new
release candidate on the website at https://sqlite.org/download.html
and on the newly reopened branch-3.20 of the source tree at
https://sqlite.org/src/timeline?r=branch-3.20

A new draft change log is at https://sqlite.org/draft/releaselog/3_20_0.html

Changes in the release candidate since the previous release attempt include:

* Improved documentation for the new pointer-passing interface
* Date/time functions can now be used in CHECK constraints and indexes
* Added the UNION virtual table extension

The target release date is one week from today.  If you have concerns
with anything in this release, please raise them now.

The checklist at https://sqlite.org/checklists/320/index has been
reset.  The release will occur when that checklist goes all-green.
-- 
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

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org

Re: [sqlite] QUERY: Usage of malloc and free in SQLite

[Graham Holden]

I've no idea of the internals of SQLite, but two things come to mind:
1. Can your custom allocation routines return the same address in the case of a 
realloc() call that is either smaller than the original allocation, or (if 
applicable) is larger but still fits in the original "memory block"? This might 
explain the duplicate entries you're seeing.
2. If a realloc() call needs a new block of memory to satisfy it, your 
allocators must, presumably, be freeing the original block. Are those 
"internal" frees being tracked? If so, the freed block may be returned by a 
subsequent allocation, without (apparently) being freed.
Graham
Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: "Anthrathodiyil, Sabeel (S.)" 
 Date: 24/03/2017  11:18  (GMT+00:00) To: SQLite mailing 
list  Cc: "Subramaniyan, Ganesan (G.)" 
, "Natanam, Karthigeyan (K.)" 
 Subject: Re: [sqlite] QUERY: Usage of malloc 
and free in SQLite 
Hi Dan,
Custom memory management APIs registered are being used by SQLite. We suspect 
SQLite, in certain cases is freeing allocated memory, not using the registered 
API to free.

As part of our investigation, we are maintaining the starting address of the 
memory allocated and size allocated, and removed from list once it is freed via 
registered memory management API mqxFree. But we find the same address being 
allocated again for a subsequent memory allocation/reallocation request which 
was not freed using mqxFree. This leads us to suspect allocated memory is being 
freed(not through the registered API) and same address is being made available 
for next allocation.

Thanks,
Sabeel

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Dan Kennedy
Sent: Thursday, March 23, 2017 9:32 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] QUERY: Usage of malloc and free in SQLite

On 03/23/2017 10:00 PM, Subramaniyan, Ganesan (G.) wrote:
> Hi,
> We are facing dynamic memory pool corruption issue while using SQLite. Based 
> on our investigation we suspect SQLite freeing memory bypassing the memory 
> management API'S registered as below.
>
> We have registered the memory allocation routines as below.
> static const sqlite3_mem_methods mqxmem = {
>  mqxMalloc,
>  mqxFree,
>  mqxRealloc,
>  mqxSize,
>  mqxRoundup,
>  mqxInit,
>  mqxShutdown,
>  NULL
>  };
> sqlite3_config(SQLITE_CONFIG_MALLOC, );
>
> Despite this, is there any sort of configurations by which SQLite might still 
> use its own or standard library APIs to allocate/reallocate or free the 
> memory, bypassing the memory management APIs registered above?


No. Following a successful call to sqlite3_config() SQLite allocates and frees 
memory using the configured routines exclusively[1]. There are no direct calls 
to malloc()/free() or similar in the library.

Are you checking the return value of sqlite3_config()? If
sqlite3_initialize() or any other sqlite3_*() function has already been called 
when sqlite3_config() is invoked it will fail and the memory allocation 
routines will not be configured.

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

What symptoms are you seeing that suggest SQLite is bypassing your memory 
allocation functions?

Dan.

[1] It may also make use of static buffers supplied by calls to 
sqlite3_config(SQLITE_CONFIG_HEAP), CONFIG_PAGECACHE or CONFIG_SCRATCH, but I'm 
guessing you have made no such calls. Most apps do not.




> We are using SQLite version 3.7.10 running on Freescale MQX hosted on ARM A5 
> Cortex processor.
> We have enabled SQLITE_ENABLE_MEMORY_MANAGEMENT configuration and using 
> sqlite3_release_memory() 
> very often, after every fetch/write operations.
>
> Thanks & Regards,
> Ganesan.S
> ___
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use parameterized queries in SQLite.Net

[Graham Holden]

The main reason you should parameterise queries is to protect against "SQL 
injection".  "Hardcoded" as below doesn't make much difference, but if the data 
being used comes in any way from an "untrusted" source, then this is 
particularly important.
If, instead of "234.56" below a malicious user could arrange to pass something 
like "2, '14/3/2017'); drop trend_data" then horrible things might happen!
Using parameters stops this, because no (SQL) parsing of the parameter value 
happens.
Graham. 


Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: Chris Locke  
Date: 14/03/2017  06:52  (GMT+00:00) To: SQLite mailing list 
 Subject: Re: [sqlite] How to use 
parameterized queries in SQLite.Net 
From a newbie's point of view, how is this better (if doing it in 'hard
coded' format like below) than writing this code:

command.CommandText = string.format("INSERT INTO trend_data (tag_key,
value, value_timestamp) VALUES ({0}, {1}, {2})",2,234.56,now);

I can sort of understand it if its in a subroutine, and I appreciate the
example given was just an example, but whats the advantage of parametized
queries?

Sorry if diverting the topic somewhat
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help file has no information

[Graham Holden]

Either don't store it on a network drive or right click, select properties and 
unlock (see 
https://social.technet.microsoft.com/Forums/en-US/20700886-2000-4c52-b706-aa1fb32d3dfb/cant-view-chm-file-contents?forum=W8ITProPreRel).
 I think.
Graham

Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: Rob Richardson 
 Date: 13/03/2017  18:14  (GMT+00:00) To: "General 
Discussion of SQLite Database (sqlite-users@mailinglists.sqlite.org)" 
 Subject: [sqlite] Help file has no 
information 
The help file that is linked from the System.Data.SQLite home page appears to 
have a table of contents but no information.  No matter what page I select, the 
page does not appear.

RobR
___
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] Is this safe use of SELECT in an INSERT?

[Graham Holden]

Thanks for that. For now, any deletions would be "en masse" so gaps wouldn't be 
a problem, but the max() idea might be useful later.


Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: Clemens Ladisch <clem...@ladisch.de> 
Date: 08/03/2017  08:59  (GMT+00:00) To: sqlite-users@mailinglists.sqlite.org 
Subject: Re: [sqlite] Is this safe use of SELECT in an INSERT? 
Graham Holden wrote:
> insert or ignore into Servers values (    'MyServer',    12345,    ( select 
> count (*) from Servers ) )
>
> is the "count (*)" guaranteed to be the count before the insert?

Yes.  But if any row was ever deleted, that value might alread by used.

You could make serverIdx autoincrementing by making it the INTEGER
PRIMARY KEY.

If you do not want to change the primary key, you can get a value that
is guaranteed to be unused with max():

INSERT OR IGNORE INTO Servers
VALUES ('MyServer', 12345,
    (SELECT ifnull(max(serverIdx), 0) + 1 FROM Servers));


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

[sqlite] Is this safe use of SELECT in an INSERT?

[Graham Holden]

I want to keep a semi-persistent list of server/port pairs with an associated 
"index" that can be used to refer to entries elsewhere. Given:
create table Servers (    serverName    text,    serverPort       integer,    
serverIdx         integer unique,    primary key ( serverName, serverPort ) )
Is the following "safe" to ensure that the next index value is used for 
inserted rows? (It seems to work fine from the shell).
insert or ignore into Servers values (    'MyServer',    12345,    ( select 
count (*) from Servers ) )
I.e. is the "count (*)" guaranteed to be the count before the insert?
Thanks.Graham Holden
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Incorrect SEARCH link on "c3ref" page

[Graham Holden]

Using the SEARCH function on (at least a couple of) the "c3ref" pages (e.g. 
"sqlite.org/c3ref/exec.html") sends you to "sqlite.org/c3ref/search?q=xxx" 
(instead of "sqlite.org/search?q=xxx") and gives a page not found error instead 
of the search results.
Graham
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.data.SQLite documentation

[Graham Holden]

I've seen those symptoms with other CHM files if they're on a network share. If 
that's the case for you, try copying to a local drive.
Graham


Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: Mickey Feldman  
Date: 23/02/2017  12:45  (GMT+00:00) To: sqlite-users@mailinglists.sqlite.org 
Subject: [sqlite] System.data.SQLite documentation 
I am not able to read SQLite.NET.chm, I see only the left hand panel, 
but not the expanded contents.  Is this a known issue, or perhaps an 
issue with Windows 7? Is this documentation available anywhere else or 
in some other format?



-- 
M. Feldman

---

Vigil Health Solutions Inc.

www.vigil.com

Toll Free: 877-850-1122

Phone: 250-383-6900

Fax: 250-383-6999
___
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] Linux top command and sqlite

[Graham Holden]

 Original message From: Roger Binns  
Date: 21/02/2017  20:48  (GMT+00:00) To: SQLite mailing list 
 Subject: Re: [sqlite] Linux top command 
and sqlite 
On 21/02/17 10:22, Kevin O'Gorman wrote:
> Some of my stuff takes a while to run, and I like to keep tabs on it.
> Right now, I'm running one of those, and the Linux top command shows
> extremely small CPU usage, and a status ("S" column) of "D" which the man
> page defines as "uninterruptable sleep".  Huh?

In this case you can read D as waiting on disk.


According to this answer on StackOverflow:  
http://stackoverflow.com/a/6685764/2096401 spotting a "D" is relatively rare, 
although can be more common if NFS or similar is involved. No idea whether 
that's true or not, nor whether it applies to the OP.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why this query plan?

[Graham Holden]

> So you could benefit from an index for reasons other than the usual reasons 
> eg assisting the where clause.
Yes. Using a "covering index" (that contains all fields in the SELECT clause) 
is often suggested as a _potential_ optimisation step, so the main row-data 
does not need to be accessed (but note it isn't a universal cure-all, since -- 
as I  understand  it -- it uses more space and makes inserts/update slightly 
slower)

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

Re: [sqlite] date-2.2c tests fail for sqlite-3.16.1 on Fedora / i686

[Graham Holden]

I don't know what the tests are doing, but could it be connected with the fact 
that a leap-second was added as we changed from 2016 to 2017 and one of 
expected/got is taking this into account and the other isn't?
Graham
 Original message From: Richard Hipp  Date: 
05/01/2017  08:12  (GMT+00:00) To: SQLite mailing list 
 Subject: Re: [sqlite] date-2.2c tests 
fail for sqlite-3.16.1 on Fedora / i686 
On 1/4/17, Jakub Dorňák  wrote:
> Example output:
>
> ...
> ! date-2.2c-1 expected: [06:28:00.001]
> ! date-2.2c-1 got:  [06:28:00.000]
> ! date-2.2c-4 expected: [06:28:00.004]
> ! date-2.2c-4 got:  [06:28:00.003]
> ! date-2.2c-7 expected: [06:28:00.007]
> ! date-2.2c-7 got:  [06:28:00.006]
> ! date-2.2c-8 expected: [06:28:00.008]
> ! date-2.2c-8 got:  [06:28:00.007]
> ...

This is probably a function of the underlying floating-point hardware.
What CPU is this running on?
-- 
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] "Responsive" website revamp at www.sqlite.org

[Graham Holden]

From a quick skim, looks pretty good on an original Samsung Note (both
landscape and portrait).

Kudos for keeping code-blocks from wrapping.

If I had to have a minor nitpick, the text goes right up to the left
and right edges of the screen: a couple of pixels of margin --
especially on the left -- would I think help. 

Obligatory typo:
Under "High Concurrency" of the "Appropriate Uses For SQLite" page you
have "Writer queue up. Each application does...". At a minimum it
should be "Writers"; possibly "Writers queue up, each...".

Regards,
Graham Holden



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

Re: [sqlite] UPDATE statement without FROM clause

[Graham Holden]

One idea I came up with a while ago is to use a trigger on the "FROM"
table to cause updates to the main table:

CREATE TRIGGER UpdateTrigger AFTER UPDATE OF TriggerField ON Updates
BEGIN
UPDATE Master SET
Field1 = OLD.Field1,
Field2 = OLD.Field2,
...
WHERE Master.Key = OLD.Key
END;

and trigger the update with:

UPDATE Updates SET TriggerField = NULL ;

It seems to run (in my very simple test) at the same speed as REPLACE
INTO but has the advantages that (a) it doesn't replace rows (possibly
affecting rowids) and (b) not having to specify unchanged fields.

See http://stackoverflow.com/a/22481731/2096401 for more details.

Graham Holden
sql...@aldurslair.com


Monday, June 06, 2016, 9:11:14 AM, Hick Gunter <h...@scigames.at> wrote:

> Would not

> Replace into A () select ,,
>  from A [cross] join B on( );

> do the trick? If a.rowid has an alias (i.e. integer primary key)
> then the modified rows would be deleted, but reinserted with their
> respective previous rowids. This may required switching foreign  
> keys off fort he duration of the update.

> -Ursprüngliche Nachricht-
> Von: sqlite-users-boun...@mailinglists.sqlite.org 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von skywind 
> mailing lists
> Gesendet: Samstag, 04. Juni 2016 18:19
> An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Betreff: Re: [sqlite] UPDATE statement without FROM clause

> Hi,

> why? At the moment I have to run something like:

> UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... itemN=... WHERE 
> EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID);

> Using a FROM clause I just need one scan through B (at least in principle). 
> Now, I need N+1 scans.

> Regards,
> Hartwig

>> Am 2016-06-04 um 15:33 schrieb Gerry Snyder <mesmerizer...@gmail.com>:
>>
>> If SQLite implemented the FROM it would just be a translation into the
>> complex and slow statements you want to avoid.
>>
>> Gerry Snyder
>> On Jun 4, 2016 9:19 AM, "skywind mailing lists"
>> <mailingli...@skywind.eu>
>> wrote:
>>
>>> Hi,
>>>
>>> I am using quite often SQL statements that update the data of one
>>> table with data from another table. This leads to some quite complex
>>> (and slow) statements because SQLite3 is not supporting a FROM clause
>>> in update statements. I am just wondering why the FROM clause is not
>>> supported by SQLite3?! Is this too complex to implement or is there
>>> simply no demand for these type of statements?
>>>
>>> Regards,
>>> Hartwig
>>>
>>>



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

Re: [sqlite] Sync journal's directory per transaction? or until database closed? in PERSIST mode

[Graham Holden]

 Original message 
From: Dan Kennedy  
Date: 26/05/2016  18:04  (GMT+00:00) 
To: sqlite-users@mailinglists.sqlite.org 
Subject: Re: [sqlite] Sync journal's directory per transaction? or until 
database closed? in PERSIST mode 
 
> On UNIX, it's possible to delete a file from the file-system while 
another process has it open. In this case the other process can continue 
reading and writing its file-descriptor as normal, but the data is 
stored in memory only, not on disk (since the directory entry has been 
deleted). Once the process exits or closes the file-descriptor, the data 
is lost.

It probably doesn't affect the argument of why SQLite does what it does, but I 
thought (but it's been a long time since I poked around UNIX file-systems) that 
data can still be written (or read) to disk because the i-node chain is still 
present, it's just that there's no name by which another proces can access it. 
Presumably, the final close will release the disk blocks of a marked-as-deleted 
file.

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

[sqlite] Version 3.13.0 coming soon

[Graham Holden]

 Original message 
From: Richard Hipp  
Date: 03/05/2016  13:33  (GMT+00:00) 
To: SQLite mailing list  
Subject: Re: [sqlite] Version 3.13.0 coming soon 

> Yes.? Apparently that is the new standard for security on unix
systems.? Write lets you create new temp files.? Execute lets you
check to see if a particular file exists.? But because read is
disabled, one cannot do an "ls" on the temporary directory to see what
temp files other applications have created.

New? I remember FTP repositories from the mid 80s (funic, wustl?) where this 
scheme was common place for the "upload" directory so random visitors couldn't 
see recently uploaded files until the admins had had a chance to remove "dodgy" 
files and move genuine files to the correct place.

Graham

[sqlite] How to order by absolute value ?

[Graham Holden]

Change "select abs(num)" in your second example to "select num" and you should 
be good. I'm guessing in your first example the ORDER BY only applies to the 
last SELECT.

Graham.


Sent from Samsung Mobile

 Original message 
From: Domingo Alvarez Duarte  
Date: 05/04/2016  09:47  (GMT+00:00) 
To: SQLite mailing list  
Subject: [sqlite] How to order by absolute value ? 

Hello !? 

I want to achieve this:? 

select 1 as num
union
select 3 as num
union
select -2 as num

order by abs(num) -- result [1, -2, 3]? 

But it doesn't work this works but doesn't show what I want:? 

select abs(num)
from (
select 1 as num
union
select 3 as num
union
select -2 as num
)

order by abs(num) -- result [1,2,3]? 

?? 

I know I can duplicate the column value but there is a way to do it without
duplicating ?? 

?? 

Cheers !

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

[sqlite] Random-access sequences

[Graham Holden]

 Original message 
From: Stephan Beal  
Date: 01/03/2016  12:07  (GMT+00:00) 
To: SQLite mailing list  
Subject: Re: [sqlite] Random-access sequences 

> On Tue, Mar 1, 2016 at 12:59 PM, Matthias-Christian Ott 
wrote:

>> Unfortunately, this limits the maximum number of elements that can ever
>> be inserted during a table's life-time to 2^63 - 1. While this might be
>> acceptable in some cases it is an artificial limitation.
>>

>Artificial, yes, but so is "64 bits." You will likely hit other limitations
far before getting anywhere near 2^63-1 insertions:

> https://www.sqlite.org/limits.html

> e.g. point #13:

> *Maximum Number Of Rows In A Table*

I don't think he's bothered about the maximum number of rows at one time, but 
that he might run out of new rowids. However, this feels as needless a concern: 
with 1,000 queue/dequeues per second, 2^63 IDs will last 292 million years...?

Graham.

[sqlite] How to enter Unicode character?

[Graham Holden]

In case it helps...

In a command-prompt, ALT 156 (hold ALT while pressing 156 on the NUMERIC 
keypad) uses the current code-page (British pound sign - ? - for me). ?Using 
ALT 0163 (i.e. preceding the character-code with a zero) uses Unicode (also a 
?).

I don't have an sqlite3 shell to hand to test with.

Graham.


Sent from Samsung Mobile

 Original message 
From: Dominique Devienne  
Date: 08/02/2016  16:43  (GMT+00:00) 
To: SQLite mailing list  
Subject: Re: [sqlite] How to enter Unicode character? 

On Mon, Feb 8, 2016 at 5:33 PM, Dominique Devienne 
wrote:

> On Mon, Feb 8, 2016 at 4:46 PM, Igor Korot  wrote:
>
>> On Mon, Feb 8, 2016 at 10:36 AM, Dominique Devienne 
>> wrote:
>> What I mean is the following:
>>
>> sqlite> CREATE TABLE abc();
>>
>> In that line '' should be the German character which look like the
>> Greek letter "beta".
>> ...
>
> In the good old DOS days I would probably just do ALT+NUMPAD2,2,0,
>> but that will most likely won't work here.
>>
>
> It does appear to work for me. I used
> https://en.wikipedia.org/wiki/Code_page_437 as a guide.
>
> C:\Users\DDevienne>chcp
> Active code page: 437
>
> ALT129 (keep pressing ALT, then press successively on keypad 1, 2, 9)
> C:\Users\DDevienne>echo ?
> ?
>
> ALT225
> C:\Users\DDevienne>echo ?
> ?
>
> C:\Users\DDevienne>sqlite3
> SQLite version 3.8.9 2015-04-08 12:16:33
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table t? (c?);
> sqlite> insert into t? (c?) values (char(220));
> sqlite> insert into t? (c?) values (?);
> Error: no such column: ?
> sqlite> insert into t? (c?) values ('?');
> sqlite> .header on
> sqlite> select * from t?;
> c?
> ??
> ?
>

Sorry, slight copy/paste issue on the last message.
there are two rows as expect, but only the second ALT129 entered

Above I made the mistake of using char(220), which is the upper-case U
umlaut.
Below I use char(252) (Unicode code point), and ALT129 (Windows CP437),
which are logically the same letter (lower-case u umlaut), but don't show
up the same.
(see https://en.wikipedia.org/wiki/%C3%9C)

sqlite> create table t? (c?);
sqlite> insert into t? (c?) values (char(252));
sqlite> insert into t? (c?) values ('?');
sqlite> .header on
sqlite> select c?, length(c?), length(cast(c? as blob)), unicode(c?) from
t?;
c?|length(c?)|length(cast(c? as blob))|unicode(c?)
??|1|2|252
?|1|1|129

sqlite> .schema
>
CREATE TABLE t? (c?);
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Some FTS5 guidance

[Graham Holden]

I've never used FTS, just throwing an off-the-wall idea out: instead of 
tokenising partial words, could you tokenise/store the reverse of each word 
(possibly in a separate place if that can be done):

enihsnoom
enihs
enihsnus

Then search for "enihs" as well as "shine". If you can't separate the forward 
and reversed versions, you'd have to filter-out when "dog" matches "god".

Graham

Sent from Samsung Mobile

 Original message 
From: "Mario M. Westphal"  
Date: 07/01/2016  18:31  (GMT+00:00) 
To: sqlite-users at mailinglists.sqlite.org 
Subject: [sqlite] Some FTS5 guidance 

Hello,



I recently looked into FTS 5. 

The documentation is clear and I was able to get it running with a small
test database quickly. And the response times are awesome :-)



My question: 



At least as I understand it at this point, FTS can only do prefix queries.



If my database contains the words



moon

moonlight

moonshine

shine

sunshine



A FTS query like "moon*" will find all three terms starting with "moon" -
very fast.



But there is no way to find "moonshine" or "sunshine" by running a query for
"shine" or "shine*" ?



Currently I search using LIKE and there such 'contains' queries are easy. My
users of course don't understand all this and want to find all words
containing shine, wherever the term appears in the word.



The only idea I had so far was to write my own tokenizer and to store each
word with every possible 'sub-word':



When "moonshine" is added to FTS, it is split into multiple words:



moonshine
oonshine
onshine
nshine
shine
. 



(maybe I limit this to a minimum of 2 or 3 characters).



This of course produces a log of extra entries in FTS and may impact
performance and database size. 

I hence wonder if this problem has been tackled already and if there is a
"standard" solution. 

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

[sqlite] How to see SQLite debugging information

[Graham Holden]

If you have, or create, a VB6 standalone EXE that calls SQLite, you should be 
possible to get it to show stdout/stderr.

By default, such EXEs are marked as "GUI" programs: if launched from a command 
prompt, the prompt returns immediately and they don't have a "console".

However, you can change the way an EXE is launched. ?It might be possible to 
choose the mode when you first create the project (you can in later versions of 
DevStudio; I can't remember if VB6 has this option). If you can't choose (or 
you have an existing project) you can use a Microsoft utility called EDITBIN:

EDITBIN /SUBSYSTEM:CONSOLE MYVB6.EXE

should switch the mode. ?If launched from a command prompt, the prompt 
shouldn't return until the program is closed, and any stdour/stderr should 
(with a bit of luck) appear in the command-prompt window (or can be redirected 
to a file). ?I think EDITBIN comes with the Visual C component of DevStudio: 
you may need to install that to get it, if it's not available as a download 
from MSDN.

Graham.

Sent from Samsung Mobile

 Original message 
From: Bart Smissaert  
Date: 09/12/2015  08:32  (GMT+00:00) 
To: SQLite mailing list  
Subject: Re: [sqlite] How to see SQLite debugging information 

> Are you using Excel or VB6?? They're very different things.
I use both. Have done for many years and by now I did indeed figure out
they are not the same :)

> freopen etc.
Thanks, will give that a try.

> can step through the sqlite code in a debug build
That would be great, but not sure how that works. Will ask somebody who
knows these things.
I do in fact have MS VS 2013, but no idea how to step through the code in
debug mode.

RBS

On Wed, Dec 9, 2015 at 1:44 AM, Random Coder  wrote:

> On Tue, Dec 8, 2015 at 4:30 PM, Bart Smissaert 
> wrote:
> > So, what/where is that standard output channel?
> > This is on a Win7 machine. How do I bring up that console window?
> > There is no development environment here. I am running this from Excel.
> > So, I have a standard Windows sqlite3.dll, a std_call dll (to make SQLite
> > accessible to VB6)
>
> Are you using Excel or VB6?? They're very different things.
>
> You could try adding something like the following to somewhere near
> the beginning of sqlite3_initialize
>
> freopen("sqlite_stdout.txt","a",stdout);
> freopen("sqlite_stderr.txt","a",stderr);
>
> This will create two text files for all of sqlite's output.? There
> might be side effects to doing this .. honestly I have no idea if
> it'll work, and finding where the files are created might be
> interesting (they'll be the current directory, but I have no idea what
> that is when you're running Excel, or VB6)
>
> Really, this problem is best solved with a debugger.? Even windbg is
> better than flying blind, and you can step through the sqlite code in
> a debug build even if it's being loaded by something like Excel where
> you don't have the source code.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite good on Windows XP but very very slow on Windows Seven

[Graham Holden]

This sounds like the same issue that Rob van der Stel faced back in
March this year (the relevant conversation was 24th-31st).

In a savage summary of Keith Medcalf's excellent final word (posted
26th March 2015): Windows XP does not have the necessary option for
applications (such as SQLite) to FORCE writes to go through to the
underlying disk, whereas later versions do.  The speed under Windows 7
is the "real" speed; the apparent speedup under XP was an (unsafe) lie.

Graham Holden

Tuesday, October 20, 2015, 5:54:06 PM, you wrote:

> Hi all,

> Thank you for all suggestions.

> The SQLite version is the same in both systems. I do not know which version
> is, but I tried the most recent one and the times became bigger.
> I tried disabling the anti-virus, no changes apparently.
> I also tried disabling disk caching (device manager -> disk unit ->
Directives ->> Enable cache turned off), no differences.
> The hardware is the same for both systems.
> I tried running my software in a virtual machine with Windows XP, and the
> times are much smaller. Not as small as in Windows XP running directly in
> the machine, but smaller than Windows Seven.
> Regarding fsync, could you give me more details about it? I have no
> knowledge on this.


> 2015-10-17 17:48 GMT-03:00 Keith Medcalf :

>>
>> Have you checked the options that are set for the Hard Drive Controller
>> and Drives?  (Particularly the ones that disable OS and hardware cache
>> flushing).
>>
>> Perhaps Windows 7 drivers are doing an fsync when fync is called.
>>
>> 120 ms per transaction is pretty good for a machine that is working
>> properly.
>> 1 or 2 ms per transaction is physically impossible on rotating disk.
>>
>> > -Original Message-
>> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>> > bounces at mailinglists.sqlite.org] On Behalf Of Lucas Ratusznei Fonseca
>> > Sent: Saturday, 17 October, 2015 13:53
>> > To: sqlite-users at mailinglists.sqlite.org
>> > Subject: [sqlite] Sqlite good on Windows XP but very very slow on Windows
>> > Seven
>> >
>> > Hi all,
>> >
>> > I am using sqlite for years with my software on Windows XP, no more than
>> 1
>> > or 2 milliseconds per transaction (insert), so speed has never been a
>> > concern. Until now.
>> > I had to migrate my system to Windows Seven recently, I am still doing
>> > tests and stuff. It happens that some processes became very slow. Digging
>> > in the source code, I found out that Sqlite transactions now take about
>> > 120
>> > milliseconds, which is unacceptable for me.
>> > I tried to modify journaling and synchronization, I achieved great time
>> > reduction but not enough. Besides, I must not change journaling and sync
>> > because of integrity. I need it to work well with the defaults.
>> > Is there something I am missing?
>> >
>> > Best regards
>> > ___

[sqlite] Form-Feed (0x0C) is not a space character in JSON

[Graham Holden]

I've not seen the RFC but you say "JSON only has 5 whitespace characters" and 
then list only 4, and your patched array only has four 1's. ?Have you missed 
one, or is the 5 wrong (or am I missing something)?

Graham

Sent from Samsung Mobile

 Original message 
From: Jan Nijtmans  
Date: 16/10/2015  14:33  (GMT+00:00) 
To: General Discussion of SQLite Database  
Subject: [sqlite] Form-Feed (0x0C) is not a space character in JSON 

Hi all,

Just noted in the jsonIsSpace[] array: According to RFC 7159, JSON
only has 5 whitespace characters, FF (0x0C) is not among them:

? ws = *(
??? %x20 /? ; Space
??? %x09 /? ; Horizontal tab
??? %x0A /? ; Line feed or New line
??? %x0D??? ; Carriage return
??? )

Suggested patch below.

Regards,
 Jan Nijtmans
$ fossil diff
Index: ext/misc/json1.c
==
--- ext/misc/json1.c
+++ ext/misc/json1.c
@@ -50,11 +50,11 @@
** Growing our own isspace() routine this way is twice as fast as
** the library isspace() function, resulting in a 7% overall performance
** increase for the parser. (Ubuntu14.10 gcc 4.8.4 x64 with -Os).
*/
static const char jsonIsSpace[] = {
-? 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 1, 1, 0, 0,
+? 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0,
?? 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
?? 1, 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, 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, 0, 0, 0, 0, 0, 0, 0, 0, 0,
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with sqlite3_db_filename

[Graham Holden]

[sqlite] Nuget Sqlite Packages

[Graham Holden]

 Original message 
From: Simon Slavin  
Date: 17/09/2015  22:18  (GMT+00:00) 
To: General Discussion of SQLite Database  
Subject: Re: [sqlite] Nuget Sqlite Packages 


On 17 Sep 2015, at 8:27pm, R.Smith  wrote:



> Obligatory Disclaimer: I am not a lawyer in either jurisdiction.

As you don't have a prior contract, I do not recognise your disclaimer and will 
therefore hold you personally responsible in both jurisdictions :-)

Graham

[sqlite] order by not working in combination with random()

[Graham Holden]

Apologies .. I assumed the random() example was a "created to show the effect" 
query by the OP. ?Your example was the one using the date function.

 Original message 
From: J Decker <d3c...@gmail.com> 
Date: 26/08/2015  11:42  (GMT+00:00) 
To: Graham Holden ,General Discussion of SQLite 
Database  
Subject: Re: [sqlite] order by not working in combination with random() 

On Wed, Aug 26, 2015 at 2:47 AM, Graham Holden  wrote:
> And while "SELECT random() AS rr ORDER BY rr" is slightly contrived, the 
> example from J Decker:
>
contrived? cause I copied it from the original poster's first message?
it was used in MySQL as a way to shuffle a deck of cards, and worked there too.

[sqlite] order by not working in combination with random()

[Graham Holden]

And while "SELECT random() AS rr ORDER BY rr" is slightly contrived, the 
example from J Decker:

select ItemName,SoldDate, date('now','-1 month') as z from
SoldItemDetails order by
SoldDate wrote:

> select random() order by random() should definately reprocess the function...

Agreed.? But I've come to the conclusion that

SELECT random() AS rr ORDER BY rr

should not.? Here's a nasty result

SQLite version 3.8.10.2 2015-05-20 18:14:01
Enter ".help" for usage hints.
sqlite> SELECT rr FROM myView ORDER BY rr;
92
28
97
55
-39

I feel that this result must be wrong.? Here's how to obtain it:

SQLite version 3.8.10.2 2015-05-20 18:14:01
Enter ".help" for usage hints.
sqlite> CREATE TABLE myTable (a INTEGER);
sqlite> INSERT INTO myTable VALUES (1),(2),(3),(4),(5);
sqlite> CREATE VIEW myView AS SELECT a,random()%100 AS rr FROM myTable;
sqlite> SELECT * FROM myView;
1|0
2|43
3|-33
4|24
5|-81
sqlite> SELECT * FROM myView;
1|0
2|-77
3|84
4|35
5|45
sqlite> SELECT rr FROM myView ORDER BY rr;
92
28
97
55
-39
sqlite> 

In my example it's obvious I'm using a VIEW because of what I named it, but in 
complicated real-life code this may be concealed and the programmer may think 
they're doing a simple SELECT on static values.? They shouldn't have to worry 
about such a bizarre result in a simple SELECT command.

I'm gonna call this a bug whether the dev team does or not.

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

[sqlite] Error: no such column: When column exists!

[Graham Holden]

Hello Jason,

Your second example ends with 0xA0 (a space with the high-bit set;
sometimes used as a non-breaking space).

Graham Holden

Wednesday, July 15, 2015, 8:53:20 PM, you wrote:

> So yes, I was missing 'S's, but that is only because I've been hacking at the 
> tables to get something to change.

> So the query you gave me works, however, when I put it all on the same line, 
> it is identical to my query which does not. To prove this, I updated the file 
> on github with a query table.
> Just select from it, and paste it into the shell:

sqlite>> select * from queries;
> 1|select * from PatientTreatmentNotes ptn join PatientTreatmentNotesSteps pts 
> ON pts.TreatmentNoteID = ptn.TreatmentNoteID ;|works
> 2|select * from PatientTreatmentNotes ptn join PatientTreatmentNotesSteps pts 
> ON pts.TreatmentNoteID = ptn.TreatmentNoteID?;|error
sqlite>> select * from PatientTreatmentNotes ptn join 
PatientTreatmentNotesSteps pts ON pts.TreatmentNoteID = ptn.TreatmentNoteID ;
sqlite>> select * from PatientTreatmentNotes ptn join 
PatientTreatmentNotesSteps pts ON pts.TreatmentNoteID = ptn.TreatmentNoteID?;
> Error: no such column: ptn.TreatmentNoteID

[sqlite] Destroy all evidence of a database

[Graham Holden]

> ?Well, the best that I can think of is to have your application create a
> new, randomly named, directory...

I can't help directly (I don't the innards of SQLite) but can
hopefully clarify what *I* think Simon's asking: he's already said in
his original message that he used to use a temporary directory, but
now cannot do this.  Neither is he asking about how to encrypt files,
nor how to securely delete them.  He just wants to know which files
SQLite *might* create that he needs to be concerned about deleting on
exit. 

Sorry for the rant, but there's been a lot of probably well-meaning
responses that don't seem to have read Simon's questions.

Graham.

Re: [sqlite] sqlite3 tool bug

[Graham Holden]

> I'm skeptical of the notion that cmd.exe is diddling with your data en
> route to the pipe.  I can't think of a time Windows munged my data in
> that particular way despite more years using that lousy tool than I
> care to remember.  Quotes and escapes, sure, don't get me started.

I would echo this: it's good at mangling the command-line, but I've
not been aware of it ever mangling data sent to a file/stream (other
than the binary/text mode conversions).

> Shot in the dark: maybe a string is being continued by ending the
> line with a backslash.  If the output handle is opened as text with
> fopen, the sequence would be
> 5c 0d 0a
> which the escape-reader wouldn't recognize, expecting only
> 5c 0a
> leading to a noncontinued, incomplete line.

I don't believe this would be the case, if both stdin and stdout have
been left in "text" mode: '5c 0a' sent to stdout would be converted
into '5c 0d 0a' but when this was read in "text" mode it would get
converted back to '5c 0a' which would be what the "escape-reader" of
SQLite would see.

> But that doesn't explain the difference between redirecting to a file
> and redirecting to a pipe.

Even if the data coming from the first command contained characters
that "confused" the "text" mode of Windows, I would expect things to
work or fail the same whether the output's being piped or sent to a
file.

My "shot in the dark" would be that some buffer-size limit in the
piping process is being exceeded.

Graham Holden



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

Re: [sqlite] Basic SQLite/EF6 question

[Graham Holden]

I've no experience of SQLite with .NET/EF6 but...

“Could not load file or assembly ‘System.Data.SQLite, Version=1.0.93.0, 
Culture=neutral, PublicKey Token=db937bc2d44ff139’ or one of its 
dependencies. An attempt was made to load a program with the incorrect 
format.”

in my experience indicates a .NET program running in the 64bit VM calling a 
32bit DLL.  Together with the "x86" of:

sqlite-netFx451-setup-bundle-x86-2013-1.0.93.0

This suggests the core SQLite DLL is 32 bit.  Whether a true 64bit version is 
available,  or whether you have to mark your .NET program as 32bit, others will 
have to answer.

Graham


Sent from Samsung Galaxy Note

 Original message 
From: Steven Davisworth  
Date: 14/07/2014  01:21  (GMT+00:00) 
To: sqlite-users@sqlite.org 
Subject: Re: [sqlite] Basic SQLite/EF6 question 
 
Joe Mistachkin  writes:

> 
> 
> Steven Davisworth wrote:
> > 
> > I've just upgraded PC to 64bit (new PC) and installed VS2013. I've
> followed 
> > standard install instructions as outlined in web posts I've come across
> for 
> > EF6. I've used Syatem.Data.SQLite.EF6 1.0.93.0   Windows (.NET 
Framework 
> > 4.5.1) and seem to be getting the same sorts of errors listed above.
> > 
> 
> What are the specific errors?  I did not see them when I did the testing 
of
> this scenario.  Can you provide the System.Data.SQLite setup package logs?
> 
> The log files should be located in the %TEMP% directory on the machine.
> They
> will have names similar to "Setup Log 2012-12-18 #001.txt" and
> "Installer.exe.trace.tmp29.log".
> 
> >
> > All posts I've come across seem to have the same problem. A heap load 
of 
> > suggestions that don't work.
> >
> 
> I've seen some of those same posts as well.  However, it's important to 
note
> that people may not always report when they succeed at making it 
work...  :)
> 
> > 
> > I've tried the GACing thing as well (contrary to what some posts say) Is
> > this a common issue and will it be addressed soon? (Hope my question is
> not
> > redundant but as I said loads of advice out there but non seem to work)
> > 
> 
> In the official documentation for Entity Framework 6, it is unclear to me
> whether or not their providers need to be registered machine-wide.  
Clearly,
> the design-time components in Visual Studio need various kinds of
> machine-wide
> configuration, hence the confusion here (i.e. especially since the
> components
> are in completely different assemblies).
> 
> --
> Joe Mistachkin
> 
Hi Joe
Thanks for your response.
Installs
sqlite-netFx451-setup-bundle-x86-2013-1.0.93.0
Start VS 2013 and open project
NuGet Package Manager - System.Data.SQLite.EF6 (x86/x64)
 included install - EF 6.1.0
   System.Data.SQLite.Core (x86/x64)
Rebuild done
Add New – Data – ADO.NET Entity Data Model
Choose Model Contents – EF Designer from database
New Connection – Data Provider is .NET Framework Data Provider for SQLite
Browse to Database – Test OK – Next

I get “Your project references the latest version of Entity Framework, 
however, an Entity Framework database provider compatible with this version 
could not be found for your data connection…”
“Next button is greyed out”

I’ve looked for setup logs but can’t seem to find any. (did C: search for 
Installer.exe.trace.*)

PS. When running the Test.exe from the “C:\Program Files (x86)
\System.Data.SQLite\2013\bin” the app loads but when I configure connection 
string to point to northwindEF.db I get 
“Could not load file or assembly ‘System.Data.SQLite, Version=1.0.93.0, 
Culture=neutral, PublicKey Token=db937bc2d44ff139’ or one of its 
dependencies. An attempt was made to load a program with the incorrect 
format.”

Help :-)...





___
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] Very slow when query max(col) on empty set?

[Graham Holden]

Out of curiosity, why is the "a < INF" clause (or something like "a < " 
in the original question) there? It doesn't seem to limit the results, and even 
if the overhead of having it there is minimal, does the fact that no one's 
suggested removing it mean it's needed/useful?

SELECT max(a) from table where ahttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Could not open registry key

[Graham Holden]

You almost certainly need to be running with full admin permissions. If it's an 
MSI in the ZIP, just being logged into an admin account won't be sufficient on 
Windiws 7+  Open a command prompt by right-clicking and selecting "Run as 
administrator" and run the MSI from there.


Sent from Samsung Galaxy Note

 Original message 
From: "Drago, William @ MWG - NARDAEAST"  
Date: 07/05/2014  17:45  (GMT+00:00) 
To: General Discussion of SQLite Database  
Subject: [sqlite] Could not open registry key 
 
All,

I tried running the installer in 
sqlite-netFx20-binary-bundle-Win32-2005-1.0.92.0.zip and received the following 
error:

Installer.exe: #047 @ 2014.05.07T16:29:59.4744965: TraceOps.ShowMessage: could 
not open registry key: 
HKEY_LOCAL_MACHINE\Software\Microsoft\.NETFramework\v2.0.50727\AssemblyFoldersEx

Anyone have any idea why this is happening?

Thanks,
--
Bill Drago
Senior Engineer

L3 Communications / Narda Microwave East
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
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] sqlite3_bind_text issue

[Graham Holden]

I'm not an expert, but I suspect it's the SQL_STATIC that's causing the 
problem. This means SQLite doesn't take a copy of your data and so when you do 
the sqlite3_step, all values are the same.  Try SQL_TRANSIENT.


Sent from Samsung Galaxy Note

 Original message 
From: lyx  
Date: 04/05/2014  06:11  (GMT+00:00) 
To: sqlite-users@sqlite.org 
Subject: [sqlite] sqlite3_bind_text issue 
 
Hi Experts,
I'm trying to use sqlite3_bind_text in my database insert procedure using 
sqlite 3.8.4.3. I have a very simple table and the '.schema' output is as 
following:



sqlite> .schema

CREATE TABLE ins_test(col1 text, col2 text, col3 text, col4 text, col5 text, 
col6 text);

CREATE INDEX idx_ins_test on ins_test(col1, col2, col3);




I want to insert the following data into ins_test table.

col10|col20|col30|col40|col50|col60

col11|col21|col31|col41|col51|col61

col12|col22|col32|col42|col52|col62

...

So I wrote a program to insert the test data. Since the sql statement is not 
changing and we only need to change the insert value of the insert statement. I 
plan to use sqlite3_bind_text. The core program is as following. Please check 
if there is anything wrong in my code.

    rc = sqlite3_open(argv[1], );

    if (rc != SQLITE_OK)

    {

    printf ("Error call sqlite3_open function, return value is [%d]. Error 
message is [%s]\n", rc, sqlite3_errmsg(db));

    sqlite3_close(db);

    return -1;

    }

    sprintf (sql_str, "insert into ins_test values (?,?,?,?,?,?)");



    if (SQLITE_OK != (rc = sqlite3_prepare_v2(db, sql_str, -1, , NULL)))

    {

    printf ("sqlite3_prepare_v2 error code is [%d]", rc);

    sqlite3_close(db);

    return -1;

    }



    for (i = 0; i < 1; i++)

    {

    memset (tmp_str, 0, sizeof(tmp_str));

    sprintf (tmp_str, "col1%09d", i);

    sqlite3_bind_text (stmt, 1, tmp_str, -1, SQLITE_STATIC);

   

    memset (tmp_str, 0, sizeof(tmp_str));

    sprintf (tmp_str, "col2%09d", i);

    sqlite3_bind_text (stmt, 2, tmp_str, -1, SQLITE_STATIC);

   

    memset (tmp_str, 0, sizeof(tmp_str));

    sprintf (tmp_str, "col3%09d", i);

    sqlite3_bind_text (stmt, 3, tmp_str, -1, SQLITE_STATIC);

   

    memset (tmp_str, 0, sizeof(tmp_str));

    sprintf (tmp_str, "col4%09d", i);

    sqlite3_bind_text (stmt, 4, tmp_str, -1, SQLITE_STATIC);

   

    memset (tmp_str, 0, sizeof(tmp_str));

    sprintf (tmp_str, "col5%09d", i);

    sqlite3_bind_text (stmt, 5, tmp_str, -1, SQLITE_STATIC);

   

    memset (tmp_str, 0, sizeof(tmp_str));

    sprintf (tmp_str, "col6%09d", i);

    sqlite3_bind_text (stmt, 6, tmp_str, -1, SQLITE_STATIC);

   

    if (SQLITE_DONE != (rc = sqlite3_step(stmt)))

    {

    printf ("sqlite3_step error code is [%d]\n", rc);

    sqlite3_finalize(stmt);

    sqlite3_close(db);

    return -1;

    }

    }




After I compiled and ran my test program, I found the data in ins_test table 
was not what I want. Data in ins_test was as following. I was so confused that 
why all the data in every columns starting with col6. Is there anything wrong 
with my test code using sqlite3_bind_text?

sqlite> select * from ins_test order by col1,col2,col3 limit 10;

col60|col60|col60|col60|col60|col60

col61|col61|col61|col61|col61|col61

col62|col62|col62|col62|col62|col62

col63|col63|col63|col63|col63|col63

col64|col64|col64|col64|col64|col64

col65|col65|col65|col65|col65|col65

col66|col66|col66|col66|col66|col66

col67|col67|col67|col67|col67|col67

col68|col68|col68|col68|col68|col68

col69|col69|col69|col69|col69|col69

sqlite>




I also attached my full test program in attachment. Please help. Thanks a lot!









___
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] Primary key used in compound index

[Graham Holden]

The assertion that AUTOINCREMENT has no effect (here and in another post) is 
incorrect.  Without it, SQLite MAY reuse a key from deleted rows; with it, this 
will not happen.


Sent from Samsung Galaxy Note

 Original message 
From: Simon Slavin  
Date: 26/03/2014  12:50  (GMT+00:00) 
To: General Discussion of SQLite Database  
Subject: Re: [sqlite] Primary key used in compound index 
 

On 25 Mar 2014, at 8:19pm, Pavel Vazharov  wrote:

> "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

Can you try this as exactly the following:

"id" INTEGER PRIMARY KEY,

leaving everything else out.  I assure you that it will obey the 'NOT NULL' and 
'AUTOINCREMENT' anyway.  The reason is that SQLite has explicit understanding 
of exactly that phrase for primary keys.

> "type" INTEGER NOT NULL,
> "some_integer_data" INTEGER NOT NULL,
> "some_string_data" TEXT,
> "deleted" INTEGER NOT NULL,
> major INTEGER NOT NULL DEFAULT 0
> );
> CREATE INDEX IDX_test_event_1 ON test_event (deleted, major, id);
> 
> When I execute:
> 
> 
> explain query plan select * from test_event where deleted = 0 and major = 1 
> and id > 5 order by id asc limit 1;
> 
> It returns:
> 
> 
> ?selectid order from detail
> 0 0 0 SEARCH TABLE test_event USING INDEX IDX_test_event_1 (deleted=? AND 
> major=?)
> 
> So I can see that the id is not used, only delete and major part of the 
> compound index are used. In our scenario this leads to slow selects, really 
> slow.

Not sure whether the id column should be mentioned but the index you created is 
an excellent index for that SELECT, so you should be getting extremely fast 
results.  Can you try using EXPLAIN instead of EXPLAIN QUERY PLAN ?  It will 
tell you more about how the query is being handled.

> And the select statement runs about 100 times faster than the previous one.

I agree.  Makes no sense.

> My question is: Is there a way the primary key to be used as a part of the 
> compound index?

You should think of SQLite sneakily adding the primary key onto the end of 
every index you declare, whether you ask for it or not.  When devising 
searching and sorting methods, SQLite knows it's there and should be using it 
correctly.

> Adding additional column works, but it will increase the database size and 
> will slow down the insert statements because this additional column needs to 
> be updated with trigger or with additional query.

I agree this is annoying and we should be able to solve your strange problem.

Can you post the following information:

A) What version of SQLite are you using ?
B) What is the result of EXPLAIN SELECT ?
C) How many rows are there in that table ?
D) Are you testing this inside your own application or using the SQLite shell 
tool ?

If you want to make it really easy for us to test this, could you put up some 
test data on a server somewhere ?  But we may not need it so don't work too 
hard on it at this stage.

Note to others: I get the same results from XQP using SQLite 3.7.13 and no data 
in the table.

Simon.
___
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] Primary key used in compound index

[Graham Holden]

The assertion that AUTOINCREMENT has no effect (here and in another post) is 
incorrect.  Without it, SQLite MAY reuse a key from deleted rows; with it, this 
will not happen.

Sent from Samsung Galaxy Note

 Original message 
From: Simon Slavin  
Date: 26/03/2014  12:50  (GMT+00:00) 
To: General Discussion of SQLite Database  
Subject: Re: [sqlite] Primary key used in compound index 
 

On 25 Mar 2014, at 8:19pm, Pavel Vazharov  wrote:

> "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

Can you try this as exactly the following:

"id" INTEGER PRIMARY KEY,

leaving everything else out.  I assure you that it will obey the 'NOT NULL' and 
'AUTOINCREMENT' anyway.  The reason is that SQLite has explicit understanding 
of exactly that phrase for primary keys.

> "type" INTEGER NOT NULL,
> "some_integer_data" INTEGER NOT NULL,
> "some_string_data" TEXT,
> "deleted" INTEGER NOT NULL,
> major INTEGER NOT NULL DEFAULT 0
> );
> CREATE INDEX IDX_test_event_1 ON test_event (deleted, major, id);
> 
> When I execute:
> 
> 
> explain query plan select * from test_event where deleted = 0 and major = 1 
> and id > 5 order by id asc limit 1;
> 
> It returns:
> 
> 
> ?selectid order from detail
> 0 0 0 SEARCH TABLE test_event USING INDEX IDX_test_event_1 (deleted=? AND 
> major=?)
> 
> So I can see that the id is not used, only delete and major part of the 
> compound index are used. In our scenario this leads to slow selects, really 
> slow.

Not sure whether the id column should be mentioned but the index you created is 
an excellent index for that SELECT, so you should be getting extremely fast 
results.  Can you try using EXPLAIN instead of EXPLAIN QUERY PLAN ?  It will 
tell you more about how the query is being handled.

> And the select statement runs about 100 times faster than the previous one.

I agree.  Makes no sense.

> My question is: Is there a way the primary key to be used as a part of the 
> compound index?

You should think of SQLite sneakily adding the primary key onto the end of 
every index you declare, whether you ask for it or not.  When devising 
searching and sorting methods, SQLite knows it's there and should be using it 
correctly.

> Adding additional column works, but it will increase the database size and 
> will slow down the insert statements because this additional column needs to 
> be updated with trigger or with additional query.

I agree this is annoying and we should be able to solve your strange problem.

Can you post the following information:

A) What version of SQLite are you using ?
B) What is the result of EXPLAIN SELECT ?
C) How many rows are there in that table ?
D) Are you testing this inside your own application or using the SQLite shell 
tool ?

If you want to make it really easy for us to test this, could you put up some 
test data on a server somewhere ?  But we may not need it so don't work too 
hard on it at this stage.

Note to others: I get the same results from XQP using SQLite 3.7.13 and no data 
in the table.

Simon.
___
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