ymlink, 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
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(), act
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
> 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-0
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 ?
he
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 condit
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
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
___
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 descen
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
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 pr
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
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
t; 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
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
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
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
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 m
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
SELEC
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
rack 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
esearchers 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 maili
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
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
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
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 I
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
decid
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 "pra
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_fun
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
w
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
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 i
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 w
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
th
> 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
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 the
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 c
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 r
> 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
ate 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
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: [sqli
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 Featu
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
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.
Sen
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 exhauste
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_ upo
>>> 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 multi
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 "
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) T
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"? Thi
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 c
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
-
o: 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 t
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 ) )
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
___
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
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
> 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 (
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:1
-- 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 "Writer
nchanged 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 wrote:
> Would not
> Replace into A () select ,,
> from A [cross] join B on( );
> do the trick? If a.rowid has an
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 f
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
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
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 t
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 t
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
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
e) 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 sugge
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
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.
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
Date: 26/08/2015 11:42 (GMT+00:00)
To: Graham Holden ,General Discussion of SQLite
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 c
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
> table
> ?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 d
t; 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
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
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)
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
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
Dat
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:0
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:0
83 matches
Mail list logo