select (strftime('%J', '2016-06-13T09:36:34.123Z') - 2440587.5) * 86400.0;
1465810594.123
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Dominique Devienne
On Friday, 13 March, 2020 20:14, Justin Ng wrote:
>I just encountered something weird with "temp" and "sqlite_master".
>I was wondering if it was another bug, or intentional.
The sqlite_master table in "temp" is called "sqlite_temp_master"
(temp.sqlite_temp_master) even though it responds to
Uck. That is the most horrible looking thing I have ever seen in my life.
Good luck with it.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Richard Hipp
>So is "julianday('now')" non-deterministic while "julianday()" _is_
>deterministic? That seems a little weird considering they're the same
>thing... right?
Yes. Same as "datetime(julianday(), '+1 day')" and datetime(datetime(), '+1
day') are deterministic but "datetime('now', '+1 day')" is not
On Thursday, 12 March, 2020 09:37, David Blake wrote:
>What stops the
>UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ;
>from also triggering the AFTER UPDATE ON recursively?
>Perhaps a pragma or inSQLite are triggers non-recursive by default?
>I am using (now I have by
On Wednesday, 11 March, 2020 09:24, Justin Ng
wrote:
>Sometimes, when querying data, rather than letting NULLs propagate,
>it might be better to throw an error if NULLs are not expected from an
>expression.
>The presence of NULLs might indicate an error in logic, data, or both.
Yes, it very
On Tuesday, 10 March, 2020 01:22, Octopus ZHANG wrote:
>I try to run a simple math expression, but SQLite gives no feedback :
>sqlite> select 99-(55/(30/57));
>Should I expect it to return nothing?
It is returning something. It is returning NULL.
sqlite> .nullvalue
sqlite> select
On Monday, 9 March, 2020 18:18, Peng Yu wrote:
>But I never experience the problem in my original email when I used
>python3's default sqlite3 module (WAL was not used). What is the
>difference between the default sqlite3 module and apsw? Thanks.
THe relevant difference is that the sqlite3
>I use sqlite3 (sqlite3 --version = "3.11.0 2016-02-15 17:29:24
>3d862f207e3adc00f78066799ac5a8c282430a5f" on Ubuntu 16.04.6 LTS) for
Have you tried version more recent than 4 years and 1 month old?
I think some of these issues may have been fixed in the last couple of years.
--
The fact that
On Monday, 9 March, 2020 08:33, Simon Slavin wrote:
>If your .shm and .wal files still exist when no apps are accessing the
>database, the most likely cause is that at least one of the apps is not
>closing its connection correctly.
or you are opening the database connection with
On Sunday, 8 March, 2020 21:24, Peng Yu wrote:
>When I open an sqlite3 db using the following python code,
>conn=apsw.Connection(filepath, flags = apsw.SQLITE_OPEN_READONLY)
>, I got the following error.
>Traceback (most recent call last):
> File "/xxx.py", line 21, in
>for x in
You mean like take a "boo" at the defined triggers?
select name, tbl_name, sql from sqlite_master where type='trigger';
would pretty much make clear that the defined trigger is not what you thought
it was ...
create table x(x);
create trigger after insert on x begin select 1; end;
select
On Friday, 6 March, 2020 19:25, Richard Damon wrote:
>It is sort of like NaN, where a Nan is neither less than, greater than
>or equal to any value, including itself.
NULL (as in SQL NULL) means "missing value" or "unknown". NULL represents any
value within the domain, we simply do not know
On Friday, 6 March, 2020 17:48 Xinyue Chen wrote:
...
>select t1.textid a, i.intid b
> from t t1,
> i i
> where (t1.textid = i.intid and t1.textid in (12) and t1.textid = i.intid)
>or (t1.textid = null IS NOT FALSE)
>group by i.intid, t1.textid;
I got rid of all the extra brackets
On Thursday, 5 March, 2020 20:39, Charles Leifer wrote:
>Keith, if you could share a bit more details on how you do that, I'd be
>interested.
I presume you mean how to create a "built-in" extension, which is available for
all connections, just the built-in functions and modules.
There is a
On Thursday, 5 March, 2020 05:51, Dominique Devienne
wrote:
>PS: I'd still very much appreciate an LSM1 amalgamation
cd ext/lsm1
tclsh tool/mklsm1c.tcl
which will write an lsm1.c amalgamation in the current directory (ext/lsm1)
You can append this to the amalgamation and use an EXTRA_INIT
Perhaps this is the same constant propagation bug that was fixed recently?
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Keith Medcalf
>Sent: Mon
No reproduco
SQLite version 3.32.0 2020-03-02 22:04:51
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 (
...> textid TEXT
...> );
sqlite> INSERT INTO t
...> VALUES ('12');
On Monday, 2 March, 2020 09:20, Dominique Devienne wrote:
>On Mon, Mar 2, 2020 at 5:09 PM Keith Medcalf wrote:
>> select group_concat(value) from (select distinct value from test order by
>> value);
>But is that guaranteed to be ordered correctly "forever" instea
You mean like:
select group_concat(value) over (order by value rows between unbounded
preceding and unbounded following) from (select distinct value from test) limit
1;
and
select group_concat(value) over (order by value desc rows between unbounded
preceding and unbounded following) from
On Sunday, 1 March, 2020 14:58, mailing lists wrote:
>Assume I create the following table:
>CREATE TABLE Test (ID INTEGER PRIMARY KEY, Value TEXT);
>INSERT INTO Test (Value) VALUES('Alpha');
>INSERT INTO Test (Value) VALUES('Beta');
>INSERT INTO Test (Value) VALUES('Beta');
>INSERT INTO Test
SELECT prop_value FROM Table1 WHERE obj_id=10 AND prop_key='key1' AND
(prop_tag='ios' OR prop_tag='*') ORDER BY prop_tag == 'ios' DESC LIMIT 1;
You want to order by prop_tag == 'ios' in DESCENDING order. That is, the true
(1) before the false (0). The default ascending sort will sort the
%') and (not firstname like 'jimmy%');
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: Keith Medcalf
>Sent: Friday, 28 February, 2020 17:37
>To: 'SQLite mailing list'
>Subject: RE: [sqlit
select stuff from data where uuid in (select uuid from data where ... INTERSECT
select uuid from data where ... INTERSECT select uuid from data where ...
);
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original
Probably a bug. SQLiteExpert does not even run on my computer. It just farts
in the wind and does not even bother to log or show an error message. Maybe it
tries to access some internal Microsoft Spying mechanism that I have disabled.
--
The fact that there's a Highway to Hell but only a
>From: sqlite-users On
>Behalf Of Keith Medcalf
>Sent: Tuesday, 25 February, 2020 14:44
>To: SQLite mailing list
>Subject: Re: [sqlite] Fwd: inserting new data only
>
>
>If you are going to do it in all in one insert statement rather than
>using a before trigge
:39|13
10:46|18
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Keith Medcalf
>Sent: Tuesday, 25 February, 2020 13:15
>To: SQLite mailing list
&g
On Tuesday, 25 February, 2020 12:23, Przemek Klosowski
wrote:
>On Tue, Feb 25, 2020 at 1:18 PM Keith Medcalf wrote:
>> create table data
>> (
>> keytext primary key,
>> data integer not null
>> )
>> without rowid;
>>
>> -
duplicate to be stored
and also store the computed slope to prior with each append -- in that case
triggers would be the only way to do it.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From:
create table data
(
keytext primary key,
data integer not null
)
without rowid;
-- insert into data select (?, ? as value where value IS NOT (select data from
(select max(key), data from data));
insert into data select '10:32', 12 as value where value IS NOT (select data
from
IF is not an SQL statement.
IF is a part of your host application programming language. It may also be
part of a proprietary vendor specific extension to the SQL language to permit
programmability such as the Sybase TRANSACT-SQL (licensed to Microsoft as
Microsoft SQL Server to run on
On Saturday, 22 February, 2020 09:26, Andy Bennett
wrote:
>This other process has called "BEGIN IMMEDIATE TRANSACTION" and
>https://sqlite.org/rescode.html#busy says
>"The BEGIN IMMEDIATE command might itself return SQLITE_BUSY, but if it
>succeeds, then SQLite guarantees that no subsequent
On Friday, 21 February, 2020 19:36, Simon Slavin wrote:
>On 22 Feb 2020, at 2:28am, Keith Medcalf wrote:
>> When a database is to be created these commands must be given BEFORE
>any command which opens or creates the database:
>>
>> pragma auto_vacuum
>> pragm
There are three pragma's which affect the "organization" of a newly created
database. When a database is to be created these commands must be given BEFORE
any command which opens or creates the database:
pragma auto_vacuum
pragma encoding
pragma page_size
pragma data_store_directory
The
On Thursday, 20 February, 2020 22:06, Andy KU7T wrote:
>I admit I do not fully understand all the arguments. I am running on
>Windows. Are you saying the PRNG on Windows is not good enough to use
>randomblob(16) in Sqlite? All I need is a reasonable assurance that is
>are unique...
Yes, it is
On Thursday, 20 February, 2020 11:48, Richard Hipp wrote:
>The author of that article, "Raymond", assumes that the random number
>generator in the SQL database engine is not cryptographically strong.
Actaully, what "Raymond" is on about is the fact that the original definition
of a GUID,
randomblob(16) generates 16 random bytes.
randomblob(16) does not generate a valid UUID (it does not set the version and
variant flags in the resulting 16-bytes of random data). If you set the
version to 4 and the variant to 1 then randomblob(16) does produce valid
version 4 uuids with
On Wednesday, 19 February, 2020 21:24, ethan he wrote:
>There is a SQLITE DATABASE has “MeslocallD”(INTEGER PRIMARY KEY
>AUTOINCREMENT),
>Is that possible to delete the data but still keep the MeslocallD
>consistence?
Assuming that by "consistence" you mean the high-water mark for inserted
1) In the first two query's, why do you have a condition on the LHS table in
the LEFT JOIN conditions?
2) In the last query, why do you have a condition on the RHS table of the LEFT
JOIN in the WHERE clause?
These would seem to indicate that you are using a LEFT JOIN when you really do
not
On Monday, 17 February, 2020 17:20, The Tick wrote:
>I'm running tcl 8.6.8 on win7x64. I built the latest sqlite Tcl package
>with the 3310100 source using mingw gcc under msys2.
>Everything seems to work but I ran into a strange result with
>last_insert_rowid().
>The following example
On Sunday, 16 February, 2020 10:25, Richard Hipp wrote:
>> Why the database can not be read by another sqlite3 session when the
>> corresponding -wal file exists? Thanks.
>Because Firefox uses "PRAGMA locking_mode=EXCLUSIVE;"
Perhaps on some platforms, but Firefox 73.0.0 on Windows 10
On Saturday, 15 February, 2020 19:27, Peng Yu wrote:
>I am trying to see what tables are available in sqlite_master from
>firefox cookies sqlite3 fire.
>~/Library/Application
>Support/Firefox/Profiles/jaseom4q.default-1480119569722/cookies.sqlite
>But the error message says "Error: database
While that is nice, it is likely completely irrelevant. The issue appears to
be the flamboyant conversion of data being performed by dotSnot (.NET).
The fact that data can be stored in several different formats inside the
database, and then converted to what dotSnot calls a "GUID" is all very
On Thursday, 13 February, 2020 17:58, Jim Dodgen wrote:
>I have often wondered what the performance difference is between /dev/shm
>and :memory: databases
Theoretically a :memory: database is faster than a /dev/shm stored database. A
:memory: database is purely in memory and has no extra
On Thursday, 13 February, 2020 17:06, Jim Dodgen wrote:
>I have placed databases on/in /dev/shm and shared them across both
>threads and processes.
Yeah, /dev/shm is a pre-existing tmpfs filesystem, separate from the one
mounted on /tmp. I keep forgetting about that one ...
--
The fact
Correct. "memory" databases can only be shared between connections in the same
process, and then only by the sharedcache method. In effect, a "memory"
database is nothing more than a cache, and sharing it between connections means
sharing the cache. cache=private uses a separate cache for
On Windows the GetProcessTimes Win32 API is used to get the user and kernel
(sys) times for the current process since getrusage only exists on unix-like
platforms. In all cases the precision and accuracy are limited by the
underlying OS timer accuracy.
The vfs call to get the current time
According to the code in shell.c the .timer on/off sets a flag that tells
whether you want timer data printed or not, and then for each statement:
if .timer is turned on
save the current wall clock and getrusage times (usr and sys times)
execute the statement.
if .timer is turned on
get
The easiest way is to phrase the query such that the table is internally
materialized. In query1.sql the easiest way to do that is to change the:
WITH
build a in memory table with parent-child relations
from 3 json arrays _which have the same size_
"objects_in_memory"
On Monday, 10 February, 2020 14:36, Simon Slavin wrote:
>Does this problem affect unnumbered indexes too ? In other words if I
>have
>(?,?,?,?,?)
>and bind to the fifth one using the index do I have the same problems as
>having
>(?1,?2,?3,?4,?5)
>and bind to the fifth one using its number
That's good, but this not screw up later userid/date if an entry is AWOL.
WITH systolic
AS (
select userid,
date,
rank() over (partition by userid, date order by id) as rank,
reading
from pressure
.
>-Original Message-
>From: sqlite-users On
>Behalf Of Keith Medcalf
>Sent: Sunday, 9 February, 2020 19:17
>To: SQLite mailing list
>Subject: Re: [sqlite] How to group this?
>
> select id,
> entry_date,
> max(case when term == 'Systolic'
select id,
entry_date,
max(case when term == 'Systolic' then reading end) as Systolic,
max(case when term == 'Diastolic' then reading end) as Diastolic
from the_table
group by id, entry_date
;
If you want to make sure you have both terms for a given
Simon,
I hope you don't mind me sending this to you directly, but what do you think of
the following as the VSV documentation:
/*
** 2020-02-08 modified by Keith Medcalf who also disclaims all copyright
** on the modifications and hereby places this code in the public domain
**
** This file
I don't think that patch to apply affinities is a good idea since it will
usually be mostly useless and will negatively impact performance since one can
and should assume that the actual author of the VTable knows what they are
doing, currently SQLite3 does not enforce declared VTable column
On Wednesday, 5 February, 2020 18:10, Jens Alfke :
>> On Feb 5, 2020, at 9:58 AM, Keith Medcalf wrote:
>> It seems that "column affinities" are not respected in Virtual Table
>> implementations -- that is the value that is returned is the datatype
>&g
gt;-Original Message-
>From: sqlite-users On
>Behalf Of Keith Medcalf
>Sent: Wednesday, 5 February, 2020 10:58
>To: SQLite Users (sqlite-users@mailinglists.sqlite.org) us...@mailinglists.sqlite.org>
>Subject: [sqlite] VTable Column Affinity Question and Change Req
It seems that "column affinities" are not respected in Virtual Table
implementations -- that is the value that is returned is the datatype provided
by the the vtab_cursor sqlite3_result_* function and the "column affinity" from
the vtab declaration is not applied. In effect the column
On Tuesday, 4 February, 2020 22:31, Keith Medcalf wrote:
The vsv.c (source) file line counting is now verified and I have added a skip=
parameter. Adding skip was far easier than variable separators ...
Same location, file updated: http://www.dessus.com/files/vsv.c
The complete collection
On Tuesday, 4 February, 2020 05:19, Robert M. Münch
wrote:
>On 3 Dec 2019, at 16:10, Jannick wrote:
>> would it be possible to add to the csv extension the following
>> parameter options (with syntax along the lines of):
>> - sep=';': field separator character (different from default ',')
>>
On Tuesday, 4 February, 2020 17:23, J. King wrote:
>Not everyone has access to carrays and intarrays, either, such as PHP
>users like myself.
Then you should probably be creating a temporary table and using that/
begin immediate;
create temporary table inlist(x primary key(x)) without rowid;
On Tuesday, 4 February, 2020 12:14, Deon Brewis wrote:
>WHERE x IN (?1,?2,?3,?4...,?1000 )
That would be a really silly construct to use. Why are you bothering to name
all the parameters? Anonymous parameters are merely an array of pointers to
values. When you give the parameters names
This is part of the trusted schema.
Virtual Tables and Functions can be labeled as DIRECT_ONLY, INNOCUOUS, or
unlabeled.
INNOCUOUS virtual tables and functions can be used anywhere they are allowed
including in the schema and views and indexes and so forth (provided that they
would
On Friday, 31 January, 2020 21:15, Peng Yu wrote:
>I have to specify either a full path (either relative or absolution)
>to use .load. But it would be more convenient if there is something
>like PATH (may be named as something like SQLITE3_LIB_PATH) to search
>for library files. Is it available
On Friday, 31 January, 2020 17:59, Peng Yu wrote:
>How to use extension-functions.c? It means that I have to compile it?
Yes. Either as a loadable extension or as core builtin functions extending the
amalgamation.
>How to use it with python?
db.load_extension()
for each connection db into
You could use the second method (opening the file by handle) if you do not want
your code to be portable.
Yes, APSW is far superior to sqlite3. It does not have any "magic" and wraps
SQLite3 into Python so that it works like SQLite3 works, so the interface works
as documented for the
On Friday, 31 January, 2020 14:39, 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
>ac
On Friday, 31 January, 2020 13:58, Richard Hipp wrote:
>On 1/31/20, Keith Medcalf wrote:
>> The check should occur AFTER defaults and
>> column affinity is applied before the data record is stored
>Why do you think this? Is it documented somewhere? I ask because
>
>collect input data row
>apply column affinity
>fire before triggers
>apply defaults, generated always, rowid etc.
>apply column affinity to above columns
>run constraints
>store actul row
>fire after triggers
Actually, with generated columns it is a bit more complicated. I think:
collect input
I believe that when inserting a row into a table the CHECK constraints (which
includes any NOT NULL constraint) are checked at the wrong time, or at least
with the wrong data. The check should occur AFTER defaults and column affinity
is applied before the data record is stored, meaning that
t;> TOTAL_COMP GENERATED ALWAYS AS (SALARY + BONUS)
>> )
>>
>> TOTAL_COMP is a generated column of the EMPLOYEES table. The data type
>of the TOTAL_COMP is the data type of the expression (SALARY_BONUS).
>
>I think that’s a typo and should read (SALARY + BONUS
At any given instant in time a connection can either (a) have a transaction in
progress or (b) have no transaction in progress. An SQL statement cannot be
executed EXCEPT inside of a transaction. "autocommit" means that the SQLite3
database engine (not the sqlite3 wrapper) will start the
On: Wednesday, 29 January, 2020 06:45, Markus Winand
wrote:
>I think there might be a glitch in the way SQLite 3.31.x derives the
>collation information from the expression of a generated column.
>In particular, COLLATE inside the AS parens seems to be ignored, but it
>is honoured after the
On Thursday, 30 January, 2020 12:20, Simon Slavin wrote:
>I would appreciate your help. Reading a technical article today, I came
>across a casual reference to "Standard SQL" as if it was a well-known
>thing. This worried me since I've never heard the term and I'm meant to
>know about such
> db.isolation_level
>>>
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Peng Yu
>Sent: Thursday, 30 January, 2020 09:55
>To: SQLite mailin
The isolation_level specifies the default suffix to put after 'BEGIN' when
beginning a transaction. Inside the library the following is used when the
magic wants to start a transaction:
if isolation_level is not None:
.execute('BEGIN %s' % isolation_level)
This is so that you can set
Yu
>Sent: Thursday, 30 January, 2020 02:24
>To: SQLite mailing list
>Subject: Re: [sqlite] Is it necessary to encode() for file names in sqlar
>format?
>
>So to confirm. In python 3, the str type should be used for name? Thanks.
>
>On Thu, Jan 30, 2020 at 12:58 AM Keith
sys.argv is a list of unicode text strings. There is no need to specifically
encode or decode it so long as sys.getdefaultencoding() returns 'utf-8'. If
your version of Python is so old that it returns something else then you need
to modify site.py and have it set the default encoding to
On Wednesday, 29 January, 2020 22:45, Peng Yu wrote:
>In python sqlite3 program, if I call .execute() multiple times then
>call .commit(). Does it ensure that all the sqlite3 commands specified
>by execute()'s either all take effect or none effect?
Mayhaps yes, mayhaps no. .commit() is merely
Excuse the top posting. This perhaps:
create table srcdata
(
CLS1text not null,
CLS2integer not null,
START integer not null,
END integer not null
);
insert into srcdata values ('ABC1',100,0,1);
insert into srcdata values ('ABC1',100,1,1);
insert into srcdata
On Tuesday, 28 January, 2020 23:42, Peng Yu wrote:
>I have two python programs using sqlite3. They function the same,
>except the following.
I presume this means you are using the standard (as in included with the
standard Python distribution) sqlite3 module? There are other ways to use
On Monday, 27 January, 2020 10:31, James K. Lowden
wrote:
>On Sun, 26 Jan 2020 12:01:32 -0700
>"Keith Medcalf" wrote:
>> Now that the table exists, use "SELECT * FROM " to determine
>> the number of columns in the table (which will incl
QLITE
>
>You are missing
>
>maxsize += _varIntSize_(maxsize)
>
>fort he size varint at the begin oft he header just before the return
>
>-Ursprüngliche Nachricht-
>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>Im Auftrag von Keith Medcalf
Here is a wee bit of C code that you can compile as a plugin that will give you
the row size (well, it may be bigger than the actual record size by a few bytes
but it is pretty close) ...
works properly for utf-16 encoded databases as well.
-//- sqlsize.c -//-
#include
You can certainly get the max and average cell size per page of rows from
dbstat which is the most granular data available I think, as well as the
average and max for all the rows taken together. Assuming that the table is a
"rowid" table, then that is the data for the "leaf" pages only. As
inal Message-
>From: sqlite-users On
>Behalf Of Keith Medcalf
>Sent: Monday, 27 January, 2020 00:28
>To: SQLite mailing list
>Subject: Re: [sqlite] SQL CASE WHEN THEN ELSE END
>
>
>Do you perhaps mean:
>
> SELECT a.a,
> a.c,
> a.e,
>
Do you perhaps mean:
SELECT a.a,
a.c,
a.e,
b.g,
b.h,
b.i,
coalesce((
SELECT 'YES'
FROM t2
WHERE wYear == a.c
AND pid == a.a
), 'NO') AS
I get nothing at all except a complaint that the syntax is invalid. In
particular
(
CASE
(
SELECT WYear FROM t2 WHERE pid = a.a
)
WHEN c.WYear = 2020 THEN “YES”
ELSE “NO” END
) AS DIGITAL
Is not a valid scalar expression. Parsing fails at "WHEN". What exactly
On Sunday, 26 January, 2020 10:29, chiahui chen wrote:
>After creating a table (total 8 columns including 1 generated column), I
>tried to import data from a csv file (each record has values for 7
>columns that match the non-generated column names and data types, no
>headers ).
>The system
On Tuesday, 21 January, 2020 05:28, Richard Hipp wrote:
>On 1/21/20, Keith Medcalf wrote:
>> Richard,
>>
>> The TRUSTED_SCHEMA setting works really well but I have noticed one
>> problem (there may be more, but I haven't run across any yet) with
>> it that i
https://www.sqlite.org/draft/c3ref/vtab_config.html
should also reference SQLITE_VTAB_INNOCUOUS and SQLITE_VTAB_DIRECTONLY
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
___
sqlite3_vtab_config(db, SQLITE_INNOCUOUS) should that not be
sqlite3_vtab_config(db, SQLITE_VTAB_INNOCUOUS)?
Which explains why my quick patch in the pragma.c xConnect code to make all
pragma vtabs innocuous didn't work (I copied from series.c) :)
rc = sqlite3_declare_vtab(db, zBuf);
if(
Richard,
The TRUSTED_SCHEMA setting works really well but I have noticed one problem
(there may be more, but I haven't run across any yet) with it that is perhaps
easy to address, though it needs to be done properly. That is perhaps adding
an innocuous flag to pragma definitions in
On Monday, 20 January, 2020 12:42, David Bicking wrote:
> Thanks. I figured the solution would use CTE (this is a CTE, isn't it??)
>Unfortunately, they were neither in Sqlite, nor mentioned in any of the
>sql stuff I read when I taught myself to do SQL.so it took me a while to
>figure out how
== d.name
and arg == ?)
order by seq != 1, seq
limit 1;
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Keith Medcalf
>Sent
d.name and
>arg==?1)
>order by seq!=1, seq limit 1;
>
>I’m assuming the temp db is always 1 in the seq column. Can anyone
>confirm that or should I change it to
>
>order by lower(name)!=temp, seq limit 1;
>
>Thanks. I also learned the parentheses are not required for pragma
&
On Sunday, 19 January, 2020 01:47, x wrote:
>Suppose you’re given a query ‘SELECT 1 from tbl’ by a user and you want
>to know the name of the schema that tbl belongs to. What’s the easiest
>way to do this?
>I know sqlite will use temp.tbl if it exists else main.tbl if it exists
>else it will
Defining SQLITE_DEFAULT_DEFENSIVE prevents proper working of the CLI .parameter
commands.
SQLite version 3.31.0 2020-01-19 18:49:07
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .schema
sqlite> .param
While there are lines to be edited:
Press up arrow until line is recalled
Edit the line
Press the ENTER key to enter that line
Maybe you have to compile your own to include readline (on Linux), but it works
for me. Both Linux and Windows.
--
The fact that there's a Highway to Hell but
Ooops. Wrong query pasted, should be this one:
with p (period) as (
values (cast(strftime('%m') as integer))
),
unks (period, type, amount) as (
select p.period,
'UNK',
(
select sum(amount)
from goals
1 - 100 of 1993 matches
Mail list logo
