On 10 Nov 2017, at 8:49am, advancenOO wrote:
>hAve you optimised your column orders ?
What is optimal?
- Howard
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
t of proportion to the data. So far the only definitive statement I
> can make is ANALYZE on a table that went from 0 records to 1+ notably
> improves queries by orders of magnitude.
> - Howard
>
>
> -Original Message-
> From: sqlite-users
> [mailto:sqlite-users
ge-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Simon Slavin
Sent: Tuesday, October 3, 2017 6:40 PM
To: SQLite mailing list
Subject: Re: [sqlite] PRAGMA optimize; == no busy handler?
On 4 Oct 2017, at 2:23am, Howard Kapustein
wrote:
> What do y
rd
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Simon Slavin
Sent: Tuesday, October 3, 2017 5:31 PM
To: SQLite mailing list
Subject: Re: [sqlite] PRAGMA optimize; == no busy handler?
On 4 Oct 2017, at 12:54am, Howard Kapustein
wrote
Using SQLite 3.20.1 I notice a flood of log events sometimes when I call PRAGMA
optimize;
Warning 0x5: statement aborts at 1: [PRAGMA optimize;] database is locked
And a few times
Warning 0x5: statement aborts at 2: [PRAGMA optimize;] database is locked
And even once
Warning 0x5: statement aborts
Looking through the source a bit I see sqlite3_busy_timeout is a no-fail
operation (aside from misuse) so sqlite3_busy_timeout(0);
SQLITE_CHECKPOINT_TRUNCATEsqlite3_busy_timeout(n); does what I need. Thanks
all.
- Howard
P.S. sqlite3_close intentionally doesn't respect journal_size_li
I use journal_mode=WAL and have periods of checkpoint starvation (complicated
reasons) so I'm looking to prune the -wal file but in less blunt way than
SQLITE_CHECKPOINT_TRUNCATE.
Behaviorally I want SQLITE_CHECKPOINT_PASSIVE *and then* if required -wal
content < journal_size_limit, to do the t
The docs are a little unclear =>
https://sqlite.org/pragma.html#pragma_journal_size_limit
I need to disable autocheckpoint@close (for other reasons) so I'm looking for
ways to fence the -wal file. If I want to bound a database's -wal file to <=1MB
when I'm not in a transaction is it just PRAGMA
** Parameter eMode is one of SQLITE_CHECKPOINT_PASSIVE, FULL or RESTART.
*/
SQLITE_PRIVATE int sqlite3Checkpoint(sqlite3 *db, int iDb, int eMode, int
*pnLog, int *pnCkpt){
The comment should be
** Parameter eMode is one of SQLITE_CHECKPOINT_PASSIVE, FULL, RESTART or
TRUNCATE.
__
sqlite> create table blah(id integer primary key not null, x text, y integer
not null);
sqlite> create index blahindex on blah (y);
sqlite> explain query plan select * from blah where y & ? != 0;
0|0|0|SCAN TABLE blah
But other numeric expressions do e.g.
sqlite> explain query plan select * from
>> Is it possible to use the CLI to read a WAL database and exit without
>> modifying the database?
>>
>The checkpoint-on-close feature is not affected by
>PRAGMA wal_autocheckpoint. The NO_CKPT_ON_CLOSE DB config flag is the
>only mechanism to prevent it from inside the connection.
SQLITE_API
I can set a trace hook and I can clear a trace hook, but I don't see any way to
tell if a sqlite3* has a trace hook registered
If not then consider this a feature request :P e.g. int sqlite3_db_config(db,
SQLITE_DBCONFIG_TRACE) returning an OR'd combination of SQLITE_TRACE_* as you'd
set via
I can set a trace hook and I can clear a trace hook, but I don't see any way to
tell if a sqlite3* has a trace hook registered
If not then consider this a feature request :P e.g. int sqlite3_db_config(db,
SQLITE_DBCONFIG_TRACE) returning an OR'd combination of SQLITE_TRACE_* as you'd
set via sq
https://www.sqlite.org/c3ref/c_dbconfig_enable_fkey.html
The prototype is effectively
int sqlite3_db_config(sqlite3* db, int SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE, int
on, int* value)
where
on: >0 == disable, 0 == enable, <0=GET CURRENT VALUE
value: optional, if not nullptr then on success contains 0=
https://www.sqlite.org/c3ref/c_dbconfig_enable_fkey.html
The prototype is effectively
int sqlite3_db_config(sqlite3* db, int SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE, int
on, int* value)
where
on: >0 == disable, 0 == enable, <0=GET CURRENT VALUE
value: optional, if not nullptr then on success contains 0=
I can set a trace hook and I can clear a trace hook, but I don't see any way to
tell if a sqlite3* has a trace hook registered
If not then consider this a feature request :P e.g. int sqlite3_db_config(db,
SQLITE_DBCONFIG_TRACE) returning an OR'd combination of SQLITE_TRACE_* as you'd
set via sq
I've got WAL databases that I'm trying to do analysis on and DON'T want them
modified. I can PRAGMA wal_autocheckpoint=0; to disable auto-checkpoint'ing but
when I exit the shell I see auto-checkpoint'ing is done -- foo.db-wal and
foo.db-shm are gone and foo.db is modified. That's bad. I can't m
issues.
Suggestions?
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Clemens Ladisch
Sent: Friday, April 21, 2017 11:13 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] 0x11C: automatic index warning for CTEs ?
Howar
I'm setting SQLite logging a warning
Warning 0x11C: automatic index on PackageIdByAll(_PackageID)
Whenever I run this SQL
WITH
PackageIdByUser(_PackageID) AS (
SELECT _PackageID FROM Package AS p
INNER JOIN PackageUser AS pu ON pu.Package=p._PackageID
WHERE pu.User=?1 AND p._W
>There cannot be a fully portable way, because path specifications are not
>portable
Which begs the question, why isn't there an xGetTempFilename VFS function in
sqlite3_vfs?
Wouldn't the simplify things? Have the VFS handle it apropos, with the default
VFS' in SQLite (or an older VFS lacking i
You can compile SQLite using CreateFile instead of CreateFile2. That doesn't
have the allowed-paths restriction (among other things).
But you won't pass WACK so Store will reject your submission.
The solution is to change CreateFile2 to support removable media w/the
capability check (or some cr
>> Thank you for your really specific answer, I will go to the IoT forum,
>> insider, etc to make the request.
>Don't bother. They won't change it. The inability to access these places is
>intentional. It means that programs can't read each-other's files so malware
>programs (e.g. spyware, r
SQLite uses CreateFile2 in a Windows store aka UWP application
https://msdn.microsoft.com/en-us/library/windows/desktop/hh449422(v=vs.85).aspx
When called from a Windows Store app, CreateFile2 is simplified. You can open
only files or directories inside the ApplicationData.LocalFolder or
Package.
seful what's
likeliest to be smoothest to be accepted as a patch.
- Howard
-Original Message-
From: Joe Mistachkin [mailto:j...@mistachkin.com]
Sent: Friday, April 3, 2015 10:35 AM
To: 'General Discussion of SQLite Database'
Cc: Howard Kapustein
Subject: RE:
ping
This email may contain confidential and privileged information. Any
unauthorized use is prohibited. If you are not the intended recipient, please
contact the sender by reply email and destroy all copies of the original
message.
From: Howard Kapustein
Sent: Monday, March 9, 2015 10:10
Has anyone considered supporting SQLITE_SYNC_DATAONLY on Windows using
NtFlushBuffersFileEx?
http://msdn.microsoft.com/en-us/library/windows/hardware/hh967720(v=vs.85).aspx
>When the SQLITE_SYNC_DATAONLY flag is used, it means that the sync operation
>only needs to flush data to mass storage. Ino
The Win32 DLL download includes a .def file, but comparing the exports with the
documentation's function page shows several inconsistencies
doc but not .def
* sqlite3_mutex_held
* sqlite3_mutex_notheld
* sqlite3_unlock_notify
* sqlite3_version (not a real API just a page name f
27 matches
Mail list logo