[sqlite] [feature request] Quirk-fixing compile-time options

[Joshua Wise]

Hi, I was very happy to see the addition of the SQLITE_DQS compile-time option, 
which fixes one of the legacy bugs/quirks of SQLite3.

I was wondering if additional compile-time options could be added to fix two 
other legacy quirks:
- Primary Keys containing NULLs 
<https://www.sqlite.org/quirks.html#primary_keys_can_sometimes_contain_nulls>
- Aggregate queries containing non-aggregate result columns 
<https://www.sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause>

It would be great for SQLite3 users to opt out of these behaviors, as it would 
help prevent many difficult-to-find bugs.

Any feedback on this suggestion by a SQLite3 dev would be much appreciated!

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

Re: [sqlite] ANN: SQLite3 Decimal Extension

[Joshua Wise]

Julian dates are definitely floating point numbers, not integers.

On Thu, Apr 4, 2019, 3:37 PM James K. Lowden 
wrote:

> On Thu, 4 Apr 2019 11:21:41 -0400
> Joshua Wise  wrote:
>
> > > On the other hand, what table has a floating point number in its
> > > key?
> > >
> > > How do you even express the value of such a key for an exact
> > > match?
> >
> > Well I imagine it can be very useful for range queries. Imagine
> > Julian dates, coordinate points, rankings, etc.
>
> Julian dates are integers.  The tm structure is all integers, too.
>
> I suppose you could store lat/lon as floating point.  It's exactly the
> kind of data that calls out of a tm-like structure, though, because
> officially there are 60 minutes in a degree, and 60 seconds in a minute.
> Just as with time, the governing authorities use a non-decimal
> notation; decimal fractions of a degree are mere computational
> convienience.  And, again, it's not part of the key.
>
> In financial analysis, range queries over large datasets are common.  If
> it's not a range of dates, it's a range of
> returns/price/earning/capitalization over time.  Yet Microsoft SQL
> Server never suggested we use anything other than IEEE to store the
> data.  Perhaps that's because, more often than not, floating point data
> are manipulated as part of the query.
>
> If you're joining the table to itself to select price change over time
> to compute, say, variance, the absolute magnitude of the data are
> uninteresting.  You find the stocks by date, subtract the prices and
> compute the variance, in IEEE format, of course, because that's what
> the CPU supports.  Then you sort and filter the top quintile, or
> whatever.  In such a case, the overhead of floating-point conversion
> will be significant: twice for every row, overhead that is nonexistent
> today.
>
> I'm skeptical of the claimed advantage.  The downside is clear.  If the
> advantage can be shown, its use would be specialized.  OTOH, a
> compiete BCD implementation would be ... interesting.
>
> --jkl
>
> ___
> 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] ANN: SQLite3 Decimal Extension

[Joshua Wise]

> On the other hand, what table has a floating point number in its key?  
> 
> How do you even express the value of such a key for an exact match?  

Well I imagine it can be very useful for range queries. Imagine Julian dates, 
coordinate points, rankings, etc.

I suppose in the most common case, parsing the on-disk format to IEEE would 
only be necessary on a small number of rows, after the range query has already 
used memcmp() to find the rows it cares about. But yeah, in the case of bulk 
loading it’s probably only a loss.

> On Apr 4, 2019, at 11:15 AM, James K. Lowden  wrote:
> 
> On Wed, 3 Apr 2019 17:29:47 -0400
> Richard Hipp  wrote:
> 
>> On 4/3/19, Joshua Wise  wrote:
>>> From my naive understanding, memcmp() is used to efficiently
>>> compare long strings of bytes. But where in SQLite3 is it necessary
>>> to compare long strings of floating point numbers? I, of course,
>>> can imagine SQL queries plucking single floating point values from
>>> rows or indexes, but I can?t imagine where the long strings would
>>> be. Could you enlighten me?
>> 
>> Comparing keys in a btree search uses a lot of CPU cycles.  If the
>> comparison can be done using memcmp() rather than some custom
>> function, the comparison goes much faster, which makes searching
>> btrees faster.
> 
> On the other hand, what table has a floating point number in its key?  
> 
> How do you even express the value of such a key for an exact match?  
> 
> There is also a significant cost of converting to and from IEEE
> format.  In my experience bulk-loading quantitative databases, I
> encountered many occasions in which parsing the input accounted for 50%
> of the computation.  The limit wasn't network bandwidth or server
> speed, it was data conversion.  
> 
> --jkl
> ___
> 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] ANN: SQLite3 Decimal Extension

[Joshua Wise]

Oh of course, that makes sense. I suppose that means querying on REAL indexes 
should be slower than querying on INTEGER indexes, in the current SQLite3 
implementation? Has a benchmark of this ever been done?


> On Apr 3, 2019, at 5:29 PM, Richard Hipp  wrote:
> 
> On 4/3/19, Joshua Wise  wrote:
>> From my naive understanding, memcmp() is used to efficiently compare long
>> strings of bytes. But where in SQLite3 is it necessary to compare long
>> strings of floating point numbers? I, of course, can imagine SQL queries
>> plucking single floating point values from rows or indexes, but I can’t
>> imagine where the long strings would be. Could you enlighten me?
> 
> Comparing keys in a btree search uses a lot of CPU cycles.  If the
> comparison can be done using memcmp() rather than some custom
> function, the comparison goes much faster, which makes searching
> btrees faster.
> 
> -- 
> 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] ANN: SQLite3 Decimal Extension

[Joshua Wise]

From my naive understanding, memcmp() is used to efficiently compare long 
strings of bytes. But where in SQLite3 is it necessary to compare long strings 
of floating point numbers? I, of course, can imagine SQL queries plucking 
single floating point values from rows or indexes, but I can’t imagine where 
the long strings would be. Could you enlighten me?


> On Apr 3, 2019, at 3:23 PM, Lifepillar  wrote:
> 
> 
> 
>> On 3 Apr 2019, at 20:04, Joshua Thomas Wise  
>> wrote:
>> 
>>> [Here, I must thank Dr. Hipp, with whom I had a brief email exchange
>>> severals moons ago, who convinced me that the IEEE 754 encoding was not
>>> an ideal storage format for databases]
>> 
>> I’m curious, what were the reasons behind Dr. Hipp’s opinion on this?
> 
> At the time of SQLite4, I wrote to him asking why he had dismissed IEEE
> 754 as a storage format in favor of a custom encoding. His answer was
> that he wanted comparisons to be performed using memcmp(), which IEEE
> 754 does not allow. 
> 
> There may have been other reasons (complexity, range, ...), but that one
> stuck with me and prompted me to start searching for order-preserving
> encodings.
> 
> Life.
> ___
> 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] WAL grows without bounds, short concurrent writes & reads

[Joshua Wise]

Awww sadness. Regardless, thanks for the update.


> On Mar 29, 2019, at 11:07 AM, Richard Hipp  wrote:
> 
> On 3/29/19, Joshua Wise  wrote:
>> Dan, are there any plans to merge the wal2 branch into the trunk?
> 
> No, not at this time.
> 
> 
> -- 
> 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] WAL grows without bounds, short concurrent writes & reads

[Joshua Wise]

Dan, are there any plans to merge the wal2 branch into the trunk?


> On Mar 29, 2019, at 7:10 AM, Dan Kennedy  wrote:
> 
> 
> There's code here, if you want to experiment with it:
> 
>   https://sqlite.org/src/timeline?r=wal2
> 
> Docs:
> 
>   https://sqlite.org/src/artifact/a807405a05e19a49
> 
> Dan.
> 
> 
> 
> On 29/3/62 01:33, Florian Uekermann wrote:
>> Hi,
>> 
>> A very simple reproducer bash script using the sqlite3 CLI is appended at 
>> the end.
>> 
>> I am using WAL mode in a setting with sequential writes and many concurrent 
>> reads. Due to WAL mode the readers don't get blocked, which is great and 
>> since writes are sequential, they never get blocked either.
>> However, I am seeing the WAL grow on every write without ever restarting at 
>> the beginning of the file if there is a constant influx of new reads (with 
>> limited lifetime).
>> This causes the WAL file to grow to many GB within minutes, even if the 
>> database state fits into a few MB or even kB after closing all connections.
>> 
>> The output of "PRAGMA wal_checkpoint(PASSIVE);" usually looks like this: 
>> "0|123|123", which I interpret as the checkpointer being caught up on the 
>> current state. I believe the reason that new writes are appended at the end, 
>> instead of restarting the WAL, is that while reads are short-lived, there is 
>> always at least one going on, so the log of the last write has to be kept, 
>> which in turn prevents a reset of the WAL.
>> 
>> An example read (r) write (w) pattern could look like this (b: begin, e: 
>> end):
>> r1_b; w1; r2b; r1e; w2; r2b; w3; r3b; r2e ...
>> 
>> A solution could be to start a second WAL when the first one exceeds some 
>> size threshold, which would allow resetting the first one after all readers 
>> finish that started before the wal_checkpoint finished, even if there are 
>> new writes in the second WAL. Then the roles/order of the two WALs flipped, 
>> allowing the second WAL to be reset regardless of read/write frequency.
>> I believe that would limit the total WAL size to about 2 times of the size 
>> of writes happening within the timespan of a single read.
>> 
>> This solution has been suggested previously on this list by Mark Hamburg, 
>> but the threads lack a simple reproducer and detailed problem description.
>> 
>> Best regards,
>> Florian
>> 
>> Test script:
>> 
>> rm -f test.sqlite
>> ./sqlite3 test.sqlite <<< "
>> PRAGMA journal_mode=WAL;
>> CREATE TABLE t (value INTEGER);
>> REPLACE into t (rowid, value) VALUES (1,0);
>> "
>> 
>> for i in {1..1000}
>> do
>> 
>> ./sqlite3 test.sqlite <<< "
>> BEGIN;
>> SELECT value FROM t WHERE rowid=1;
>> .system sleep 0.2
>> SELECT value FROM t WHERE rowid=1;
>> COMMIT;
>> " &
>> 
>> sleep 0.1
>> ./sqlite3 test.sqlite <<< "
>> BEGIN;
>> REPLACE into t (rowid, value) VALUES (1,$i);
>> .print inc
>> COMMIT;
>> "
>> wc -c test.sqlite-wal
>> done
>> 
>> ___
>> 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] Feature request: dynamically referenced bind parameters

[Joshua Wise]

Yes, but the problem is that I need to also retrieve the articles themselves. 
If I were to embed the articles query inside the staff query (as you’ve shown), 
the database would have to execute the article query twice.


> On Mar 27, 2019, at 4:42 PM, Keith Medcalf  wrote:
> 
> 
> You mean something like this:
> 
> SELECT staff.* FROM staff, contributions
> WHERE contributions.staff = staff.email
> AND contributions.article IN (SELECT id FROM articles
> WHERE publish_date <= CURRENT_TIMESTAMP
> ORDER BY publish_date DESC LIMIT ?);
> 
> ---
> 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 [mailto:sqlite-users-
>> boun...@mailinglists.sqlite.org] On Behalf Of Joshua Thomas Wise
>> Sent: Wednesday, 27 March, 2019 14:22
>> To: SQLite mailing list
>> Subject: [sqlite] Feature request: dynamically referenced bind
>> parameters
>> 
>> I’ve commonly encountered cases where I have a many-to-many
>> relationship, and I would like to retrieve those relationships in a
>> single query.
>> 
>> For example:
>> 
>> CREATE TABLE staff (
>> email TEXT PRIMARY KEY,
>> name TEXT
>> );
>> CREATE TABLE articles (
>> id INTEGER PRIMARY KEY,
>> title TEXT,
>> body TEXT,
>> publish_date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
>> );
>> CREATE TABLE contributions(
>> article INTEGER REFERENCES articles(id),
>> staff TEXT REFERENCES staff(email),
>> PRIMARY KEY(article, staff),
>> );
>> 
>> First, I select the N most recently published articles:
>> 
>> SELECT * FROM articles
>> WHERE publish_date <= CURRENT_TIMESTAMP
>> ORDER BY publish_date DESC LIMIT ?;
>> 
>> Then, I’ll build a query like this to retrieve the staff that are
>> responsible for writing those articles:
>> 
>> SELECT staff.* FROM staff, contributions
>> WHERE contributions.staff = staff.email
>> AND contributions.article IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); --
>> assuming N was 12 in the first query
>> 
>> However, I need to prepare a new statement every time I do this,
>> depending on the value of N in the first query.
>> 
>> SQLite3 already allows us to create a large number of bind parameters
>> without explicitly declaring each one, via ?999 syntax. Now, if we
>> had the ability to reference those bind parameters dynamically, the
>> second query above could be something like this:
>> 
>>  WITH ids(id, n) AS (
>>  SELECT param(1), 1
>>  UNION ALL
>>  SELECT param(n + 1), n + 1 FROM ids WHERE n <
>> param_count())
>>  SELECT DISTINCT ids.id, staff.* FROM staff, contributions, ids
>>  WHERE contributions.staff = staff.email
>>  AND contributions.article = ids.id;
>> 
>> 
>> 
>> 
>> ___
>> 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] Row locking sqlite3

[Joshua Wise]

I’ve seen the server mode 

 branch, but to me it’s a deal-breaker that it requires SYNCHRONOUS = OFF.

This BEGIN CONCURRENT idea really interests me. By using WAL mode, where I can 
have synchronous commits, the feature becomes much more valuable.

Any word from Mr. Hipp on the status of this branch? Will it ever make it into 
the official version of SQLite3, perhaps under a compile-time option?

> On Mar 22, 2019, at 5:07 PM, Jens Alfke  wrote:
> 
> 
> 
>> On Mar 22, 2019, at 1:38 PM, Barry Smith  wrote:
>> 
>> You might be interested in the BEGIN CONCURRENT branch. It does page level 
>> locking (not quite as granular as row level).
>> 
>> https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md 
>> 
> 
> Oh, this is interesting. Is this a feature in development that will be 
> released soon?
> 
> [And to be pedantic: according to those docs, this feature does not do page 
> level locking; it’s optimistic not pessimistic concurrency.]
> 
> —Jens
> ___
> 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] read-only database in WAL mode and temporary files

[Joshua Wise]

From my understanding, the SHM file is needed to communicate to writers that 
they are in fact performing a read operation (i.e., locks). Without that 
communication, a writer might insert some data and then perform a checkpoint, 
without knowing that there is a database reader, and thus overwrite pages in 
the main database file out from under the reader.

Why is the WAL file needed? I’m not sure. It might be just to make the 
implementation simpler. Since the reader must create the SHM file regardless, 
it may as well create the WAL file too.

> On Mar 24, 2019, at 8:26 AM, Thomas Kurz  wrote:
> 
> But it's of zero size, so there cannot be anything inside. As far as I have 
> understood, it's just a journal containing the changes since the last 
> checkpoint. If I don't change anything, why should there be the need of this 
> journal? I would expect the journal to be created when the first write 
> operation (INSERT, DELETE, whatsoever) occurs, but not for a read operation.
> 
> And moreover, why is read-only-mode allowed to create a journal (this is 
> contradictory to the read-only-idea), but not delete it upon closing the 
> connection?
> 
> Actually, this leads to more problems: If the database itself has the 
> read-only-attribute set (by filesystem or ACL), SQLite would start journaling 
> changes which can be never be checkpointed. In commonsense, a write operation 
> should immediately terminate and return an error in this case.
> 
> Finally, I have noticed the immutable mode, but I have a bad feeling about 
> it. If I understand correctly, a second connection could still open the same 
> database in write-mode. I would instead expect that a read-only-connection 
> locked the database (using file locking mechanism) and a parallel 
> write-connection to be blocked until the locked is released. (No temporary 
> files should be required until this moment.)
> 
> 
> - Original Message - 
> From: Shawn Wagner 
> To: SQLite mailing list 
> Sent: Sunday, March 24, 2019, 11:29:58
> Subject: [sqlite] read-only database in WAL mode and temporary files
> 
> Even a read only database needs to create the wal journal if it doesn't
> exist at the moment for a database that uses that mode:
> https://www.sqlite.org/wal.html#read_only_databases
> 
> You might look into the immutable option mentioned there and see if it's
> appropriate for your needs.
> 
> 
> On Sun, Mar 24, 2019, 2:33 AM Thomas Kurz  wrote:
> 
>> When I open a database in read-only mode (?mode=ro), I observe that the
>> WAL and SHM temporary files are created anyway. Is there any possibility to
>> prevent the creation of these files? Aren't they useless?
> 
>> Even worse (using sqlite3.exe version 3.27.1):
> 
>> .open test.db
>> pragma journal_mode=wal;
>> create table a (b integer);
>> insert into a (1);
>> .quit
> 
>> This creates database with WAL and SHM files; those files are correctly
>> removed when exiting.
> 
>> .open --readonly test.db
>> select * from a;   <--- creates WAL and SHM -- why?
>> .quit
> 
>> Now, WAL and SHM files aren't deleted anymore.
> 
>> Proposal: Neither WAL nor SHM should be created at all when opening a
>> WAL-mode database in read-only mode.
> 
>> ___
>> 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] Recursive CTE on tree with doubly linked items

[Joshua Wise]

> On Mar 18, 2019, at 5:21 AM, Keith Medcalf  wrote:
> 
>  UPDATE tree
> SET position = (SELECT position FROM _children WHERE id = tree.id) -- 
> Multiply by x to number by x
>   WHERE id IN (SELECT id FROM _children);
>  DELETE FROM _children;
> END;

I don’t see the window function causing a significant performance loss, but 
your UPDATE statement is much better. You could also get rid of the gentleman’s 
agreement by temporarily setting both parent and position to NULL.

CREATE TEMP VIEW normalize_tree(parent) AS SELECT NULL;
CREATE TEMP TABLE _children(id INTEGER PRIMARY KEY, position REAL);
CREATE TEMP TRIGGER normalize_tree_impl INSTEAD OF UPDATE ON normalize_tree
BEGIN
INSERT INTO _children
SELECT id, row_number() OVER (ORDER BY position)
FROM tree
WHERE parent = new.parent
ORDER BY position;
UPDATE tree
SET (parent, position) = (NULL, NULL)
WHERE id IN (SELECT id FROM _children);
UPDATE tree
SET (parent, position) = (new.parent, (SELECT position FROM 
_children WHERE id = tree.id ))
WHERE id IN (SELECT id FROM _children);
DELETE FROM _children;
END;

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

Re: [sqlite] Documentation is lying to me

[Joshua Wise]

Ahh that’s very interesting. I suspect this is the relevant entry in the 
release notes:

SQLite Release 3.26.0 On 2018-12-01
Enhanced triggers so that they can use table-valued functions that exist in 
schemas other than the schema where the trigger is defined.

Although, we’re talking about views here, not triggers.


> On Feb 27, 2019, at 2:43 PM, David Raymond  wrote:
> 
> For me it gives an error up through 3.25.3 and starts working at 3.26.0
> 
> 
> 
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org 
> <mailto:sqlite-users-boun...@mailinglists.sqlite.org>] On Behalf Of Joshua 
> Wise
> Sent: Wednesday, February 27, 2019 2:13 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Documentation is lying to me
> 
> I can confirm that it also doesn’t work with SQLite version 3.24.0 on a 
> completely different machine.
> 
>> On Feb 27, 2019, at 9:23 AM, Joshua Thomas Wise  
>> wrote:
>> 
>> Joshuas-MBP:sqldark josh$ sqlite3
>> SQLite version 3.25.0 2018-09-15 04:01:47
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> SELECT sqlite_version();
>> 3.25.0
>> sqlite> ATTACH ':memory:' AS 'information_schema';
>> sqlite> CREATE VIEW information_schema.schemata(schema_name) AS
>>   ...> SELECT name FROM pragma_database_list();
>> sqlite> select * from schemata;
>> Error: no such table: information_schema.pragma_database_list
>> sqlite> select * from pragma_database_list();
>> 0|main|
>> 2|information_schema|
>> 
>> It worked when using `pragma_database_list()` directly, but not from inside 
>> the view.
>> 
>> 
>> 
>>> On Feb 26, 2019, at 9:26 PM, Keith Medcalf >> <mailto:kmedc...@dessus.com> <mailto:kmedc...@dessus.com 
>>> <mailto:kmedc...@dessus.com>>> wrote:
>>> 
>>> 
>>> Unable to reproduce:
>>> 
>>>> sqlite
>>> SQLite version 3.28.0 2019-02-25 18:43:54
>>> Enter ".help" for usage hints.
>>> Connected to a transient in-memory database.
>>> Use ".open FILENAME" to reopen on a persistent database.
>>> sqlite> ATTACH ':memory:' AS 'information_schema';
>>> sqlite> CREATE VIEW information_schema.schemata(schema_name) AS
>>>  ...> SELECT name FROM pragma_database_list();
>>> sqlite>
>>> sqlite> select * from schemata;
>>> main
>>> information_schema
>>> sqlite> .exit
>>> 
>>> What version of SQLite are you using?
>>> What do the following commands do?
>>> 
>>> pragma database_list;
>>> 
>>> select * from pragma_database_list();
>>> 
>>> 
>>> ---
>>> 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 [mailto:sqlite-users-
>>>> boun...@mailinglists.sqlite.org <mailto:boun...@mailinglists.sqlite.org> 
>>>> <mailto:boun...@mailinglists.sqlite.org 
>>>> <mailto:boun...@mailinglists.sqlite.org>>] On Behalf Of Joshua Thomas Wise
>>>> Sent: Tuesday, 26 February, 2019 19:06
>>>> To: sqlite-users@mailinglists.sqlite.org 
>>>> <mailto:sqlite-users@mailinglists.sqlite.org> 
>>>> <mailto:sqlite-users@mailinglists.sqlite.org 
>>>> <mailto:sqlite-users@mailinglists.sqlite.org>>
>>>> Subject: [sqlite] Documentation is lying to me
>>>> 
>>>> In this SQLite3 documentation
>>>> (https://www.sqlite.org/pragma.html#pragfunc 
>>>> <https://www.sqlite.org/pragma.html#pragfunc> 
>>>> <https://www.sqlite.org/pragma.html#pragfunc 
>>>> <https://www.sqlite.org/pragma.html#pragfunc>>), it says that
>>>> information_schema could be implemented by doing something like this:
>>>> 
>>>> ATTACH ':memory:' AS 'information_schema';
>>>> CREATE VIEW information_schema.schemata(schema_name) AS
>>>> SELECT name FROM pragma_database_list();
>>>> 
>>>> However, when attempting to do this, we get an error: "no such table:
>>>> information_schema.pragma_database_list”.
>>>> ___
>>>> sqlite-users mailing list
>&

Re: [sqlite] Documentation is lying to me

[Joshua Wise]

I can confirm that it also doesn’t work with SQLite version 3.24.0 on a 
completely different machine.

> On Feb 27, 2019, at 9:23 AM, Joshua Thomas Wise  
> wrote:
> 
> Joshuas-MBP:sqldark josh$ sqlite3
> SQLite version 3.25.0 2018-09-15 04:01:47
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> SELECT sqlite_version();
> 3.25.0
> sqlite> ATTACH ':memory:' AS 'information_schema';
> sqlite> CREATE VIEW information_schema.schemata(schema_name) AS
>...> SELECT name FROM pragma_database_list();
> sqlite> select * from schemata;
> Error: no such table: information_schema.pragma_database_list
> sqlite> select * from pragma_database_list();
> 0|main|
> 2|information_schema|
> 
> It worked when using `pragma_database_list()` directly, but not from inside 
> the view.
> 
> 
> 
>> On Feb 26, 2019, at 9:26 PM, Keith Medcalf > > wrote:
>> 
>> 
>> Unable to reproduce:
>> 
>>> sqlite
>> SQLite version 3.28.0 2019-02-25 18:43:54
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> ATTACH ':memory:' AS 'information_schema';
>> sqlite> CREATE VIEW information_schema.schemata(schema_name) AS
>>   ...> SELECT name FROM pragma_database_list();
>> sqlite>
>> sqlite> select * from schemata;
>> main
>> information_schema
>> sqlite> .exit
>> 
>> What version of SQLite are you using?
>> What do the following commands do?
>> 
>> pragma database_list;
>> 
>> select * from pragma_database_list();
>> 
>> 
>> ---
>> 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 [mailto:sqlite-users-
>>> boun...@mailinglists.sqlite.org ] 
>>> On Behalf Of Joshua Thomas Wise
>>> Sent: Tuesday, 26 February, 2019 19:06
>>> To: sqlite-users@mailinglists.sqlite.org 
>>> 
>>> Subject: [sqlite] Documentation is lying to me
>>> 
>>> In this SQLite3 documentation
>>> (https://www.sqlite.org/pragma.html#pragfunc 
>>> ), it says that
>>> information_schema could be implemented by doing something like this:
>>> 
>>> ATTACH ':memory:' AS 'information_schema';
>>> CREATE VIEW information_schema.schemata(schema_name) AS
>>> SELECT name FROM pragma_database_list();
>>> 
>>> However, when attempting to do this, we get an error: "no such table:
>>> information_schema.pragma_database_list”.
>>> ___
>>> 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