Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[J. King]

On March 12, 2020 8:15:15 p.m. EDT, Jens Alfke  wrote:
>
>
>> On Mar 12, 2020, at 1:17 PM, Richard Hipp  wrote:
>> 
>> I have set up an on-line forum as a replacement for this mailing
>list:
>
>Oh crap.
>
>> The Forum is powered by Fossil.  
>
>I appreciate that you like to 'eat your own dog food'. However, I
>strongly disagree with your using a homemade forum rather than
>something like Discourse.

I will never be able to understand how anyone could hold up Discourse as 
superior to, well, anything. It's slow, it has poor UI feedback, and it 
surprises me continually. 

So far the SQLite forum seems fine enough to me (though I would have definitely 
preferred the mailing list continue), and definitely better than Discourse. 

To each their own, though!

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

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[J. King]

On March 12, 2020 8:06:47 p.m. EDT, Petite Abeille  
wrote:

>
>Also, would it be possible to actually mark such emails to include
>proper List Header Fields, e.g.:

I had the same thought, but would that really be appropriate? Unless you could 
post to it like a list, anyway. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables

[J. King]

On February 15, 2020 2:14:30 p.m. EST, Thomas Kurz  
wrote:
>Wouldn't be something like
>
>SELECT sql FROM sqlite_master WHERE tbl_name='?' AND type='table'
>contains "WITHOUT ROWID"
>
>be sufficient?
>
>Just being curious.
>
>- Original Message - 
>From: sky5w...@gmail.com 
>To: SQLite mailing list 
>Sent: Saturday, February 15, 2020, 18:06:47
>Subject: [sqlite] WITHOUT ROWID tables
>
>Ok, not ideal. Still confusing, but I see the difference.
>For my code, I know the schemas. I guess a SQL builder could offer up
>query
>options to the user browsing new databases.
>
>On Sat, Feb 15, 2020 at 11:26 AM Simon Slavin 
>wrote:
>
>> On 15 Feb 2020, at 3:14pm, sky5w...@gmail.com wrote:
>
>> >> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
>> >> index_info('XYZ');".  If you get back one or more rows, then XYZ
>is a
>> >> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
>> >> table.
>> >>
>> > Confused...What if I made an index on a ROWID table?
>> > CREATE INDEX "Z" ON "DOC" ("n_id");
>
>> The parameter in index_info() is normally the name of an index.  So
>if you
>> create an index "Z" and ask for index_info("Z") you will get
>information on
>> that index.
>
>> If you create a WITHOUT ROWID table with name 'Y", and ask for
>> index_info("Y") you will get information on the primary key of that
>table.
>
>> If both exist, you get information about the index.
>
>> 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
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

CREATE TABLE t(
c TEXT DEFAULT 'WITHOUT ROWID'
-- this comment mentions something about WITHOUT ROWID
);


Contrived, no question, but possible. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[J. King]

On February 11, 2020 1:43:30 p.m. EST, Jens Alfke  wrote:
>I ran into this a few months ago. I ended up just biting the bullet and
>constructing a SQL statement by hand, concatenating comma-separated
>values inside an "IN (…)" expression.
>
>Yes, SQL injection is a danger. But if you're being bad in just one
>place, and you review that code, you can do this safely. SQLite's C API
>even has a function that escapes strings for you, and if you're not
>coding in C/C++, it's easy to write your own; basically
>   str ⟶ "'" + str.replace("'", "''") + "'"


Same here, for what it's worth. Since SQLite also has a 1M byte statement 
length limit I had my application embed terms once an IN() expression exceeded 
a certain number of terms, but used parameters always for string terms longer 
than a couple hundred bytes. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[J. King]

On February 4, 2020 7:10:52 p.m. EST, Scott Perry  wrote:
>On Feb 4, 2020, at 12:26 PM, Simon Slavin  wrote:
>> 
>> On 4 Feb 2020, at 7:13pm, Deon Brewis  wrote:
>> 
>>> WHERE x IN (?1,?2,?3,?4...,?1000 )
>> 
>> People really do this ?  Sheesh.
>
>It's a pretty common pattern. Sure, queries that are likely to use very
>large IN groups ought to be using carray or intarray but not everyone
>knows they exist :)

Not everyone has access to carrays and intarrays, either, such as PHP users 
like myself. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is the table created by "create temp table" in the database file or in memory?

[J. King]

On February 1, 2020 10:03:28 a.m. EST, Peng Yu  wrote:
>But it doesn't explain what is "the temp database". Is it an actual
>database file? Or it is just a in-memory database?


It depends. See <https://sqlite.org/pragma.html#pragma_temp_store> for details 
ans links to further reading. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Test failures on GPFS

[J. King]

On January 11, 2020 5:57:31 p.m. EST, T J  wrote:

>I was interested in using sqlite over GPFS.  I've seen a few useful
>threads
>on this:
>
> [...]
>
>Overall, it looks pretty good, but there were some WAL
>failures.
>Could someone comment on the precise implication of those test
>failures?

WAL mode does not work over the network, so the test failures are presumably to 
be expected. 

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

Re: [sqlite] Upgrading from version 3.6.16 to 3.30.1

[J. King]

On January 3, 2020 12:57:14 p.m. EST, Richard Watt  wrote:
>I'm currently updating a C# .NET application that uses SQLite 3.6.16 to
>run under a new Siemens Sinumerik version and I'd also like to update
>it
>to use the latest SQLite, which is 3.30.1.
>
>Does anyone know of any potential issues I might encounter and how to
>correct them please? I've tried a Google search but not really finding
>much that helps.
>
>Best regards,
>
>-- 
>Richard Watt
>
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Search <https://sqlite.org/changes.html> for the words "incompatible" and 
"legacy", for a start, and review the version history in general. 
-- 
J. King
___
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

[J. King]

On December 7, 2019 6:04:36 p.m. EST, Tim Streater  
wrote:
>At various times in various threads on this list it has been stated
>that the column name in a result set is not guaranteed unless one uses
>AS. IOW, one should say
>
>  select abc as abc from mytable where i=23;
>
>rather than just:
>
>  select abc from mytable where i=23;
>
>I'm trying to find where on the SQLite website that is documented, if
>it is, so I can point someone at it. The Xojo documentation doesn't
>mwention this so either that is a potential problem for all us Xojo
>users or they've worked around it somehow in their SQLite wrapper.
>
>Thanks for a pointer.
>
>
>-- 
>Cheers  --  Tim
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

It's stated here, at least:
<https://www.sqlite.org/c3ref/column_name.html>
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple files for a single SQLite database

[J. King]

On October 30, 2019 9:58:20 a.m. EDT, Simon Slavin  wrote:

>There are things you cannot split across different database files.  For
>instance, you cannot declare a FOREIGN KEY that refers to a table in a
>different schema.  Because SQLite can't be sure you'll have that schema
>available.

It's also worth noting that in WAL mode transactions are not atomic across 
database files (they are atomic in the default rollback-journal mode, however).

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

Re: [sqlite] Roadmap?

[J. King]

On October 26, 2019 8:07:57 p.m. EDT, Thomas Kurz  
wrote:

>To me, the most puzzling thing is the lack of full ALTER TABLE support
>(DROP COLUMN, MODIFY COLUMN, ADD CONSTRAINT, DROP CONSTRAINT).
>Modifying tables is some kind of science in SQLite, and thus, very
>error-prone. 

I'd second this (that altering the schema is error-prone). I'm not puzzled by 
SQLite's omission of most table altering (a good designer will choose a good 
schema from the start and thus rarely need to change it, making schema 
alteration a bit more of a niche feature), but it has been a point of 
difficulty in getting people used to other databases to take SQLite seriously. 

Earlier this year I spent some time implementing support for SQLite in Movim, a 
Web-based XMPP client and social media platform. All went smoothly until it was 
discovered that neither database schema migration library Movim uses actually 
correctly handles SQLite (despite both claiming to do so). I started fixing up 
one of them (Phinx), but got bogged down half-way because, of course, the whole 
thing assumes the database is capable of arbitrary ALTER TABLE statements. 

For now Movim no longer supports SQLite, despite there being demand for it, for 
want of good tooling to perform schema alterations. 


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

Re: [sqlite] Possible bug in storing text values in numeric columns

[J. King]

On October 13, 2019 7:25:50 p.m. EDT, Shawn Wagner  
wrote:
>I wouldn't call that conversion, or any other, lossless unless it can
>be
>turned back into a string that's character for character identical with
>the
>one that was originally inserted.

It's lossless if you consider the input as the number itself, not as a specific 
representation of that number.
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fossil Public Accounts

[J. King]

On October 1, 2019 9:13:47 a.m. EDT, Jose Isaias Cabrera  
wrote:
>
>Is there a Fossil public account site like git?  I believe there are a
>lot of people that can use something like that.  Thoughts?  Or not.
>
>josé
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

<http://chiselapp.com/>
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The LIKE operator and Swift

[J. King]

On September 26, 2019 9:26:23 a.m. EDT, Daniel Odom  wrote:
>I am just now getting around to learning Swift and XCode. I am having a
>
>problem with 'LIKE'. When I do this:
>
>let queryString = "select name, phone, street, city, state from phone 
>where name like '%?%'"

As others have said, '?' is a literal character, whereas ? is a parameter. 
Concatenating with || is one solution, though I would personally recommend 
instead preparing your whole pattern in your application logic and passing just 
the parameter to SQLite. Don't forget to escape any literal % and _ characters 
in your input before fencing with %, unless your input is itself a LIKE 
pattern. 

<https://sqlite.org/lang_expr.html#like>
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why "UTF-8" and not 'UTF-8' ?

[J. King]

On September 23, 2019 1:27:54 p.m. EDT, Simon Slavin  
wrote:
>The documentation for PRAGMA includes this command:
>
>PRAGMA encoding = "UTF-8"; 
>
>Why is it "UTF-8" and not 'UTF-8' ?  Why is it double quotes rather
>than a normally-delimited string ?  Should either of these work ?
>
>PRAGMA encoding = UTF-8;PRAGMA encoding = 'UTF-8'
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

It's worth noting, too, that barring changed  compile-time settings, the 
default encoding is UTF-8 to begin with. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] a bug, date() cannot handle dates in range -1000-0000 (1000BC-0BC)

[J. King]

On August 24, 2019 1:12:21 p.m. EDT, Petr Slansky  wrote:
>I discovered julianday() function and because it is linked to the
>distant
>past, I tried to find some old dates and I found a bug in date()
>function for
>date in range -1000- (1000BC-0BC):
>
>sqlite> select date('-1000-01-01'); -- OK
>-1000-01-01
>sqlite> select date('-0999-01-01'); -- BUG, should return date
>'-0999-01-01'
>-999-01-01
>sqlite> select date('-999-01-01'); -- cannot be converted to
>'-0999-01-01'
>
>sqlite> select date('-0001-01-01');  -- BUG, should report date
>'-0001-01-01'
>-001-01-01
>sqlite> select date('-001-01-01');
>
>sqlite> .version
>SQLite 3.22.0 2018-01-22 18:45:57
>0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
>zlib version 1.2.11
>gcc-7.4.0
> 
>With regards,
>
>-
>  Petr Slansky, slan...@usa.net
>
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

The documentation is explicit that behaviour for negative dates is undefined:

> These functions only work for dates between -01-01 00:00:00 and 
> -12-31 23:59:59 (julian day numbers 1721059.5 through 5373484.5). For 
> dates outside that range, the results of these functions are undefined.

<https://sqlite.org/lang_datefunc.html>
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Determining column collating functions

[J. King]

On August 13, 2019 5:58:54 p.m. EDT, Manuel Rigger  
wrote:
>Hi everyone,
>
>Is there a simple way to determine the collating function of a column?
>PRAGMA table_info does not seem to provide this information. The
>information could be extracted from sqlite_master, which contains the
>SQL statements used to create the table or view. While parsing the SQL
>string is rather straightforward for tables, it would involve more
>effort to determine the collating functions for views, which can again
>reference other views or tables.
>
>Best,
>Manuel
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

I'm not aware of any means beyong parsing the statement. This is doubly 
annoying because the index_xinfo pragma does list the collation sequence of 
index columns. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

[J. King]

On 2019-07-18 12:02:18, "Alexandre Billon"  wrote:


Hello,

I have created a table.

CREATE TABLE "sales" (
"client"  TEXT,
"salesman"TEXT,
"revenue" REAL,
PRIMARY KEY("client","salesman")
);


I can run the query below mutliple times without any error :

INSERT INTO sales ("client", "salesman", "revenue")
VALUES ('C1', NULL, 10.0);


Have I missed something in the CREATE instruction ?
Is this a normal behaviour ?

It is indeed normal behaviour. See <https://sqlite.org/rowidtable.html> 
for an explanation. If you want a true primary key, use a without rowid 
table:


CREATE TABLE "sales" (
"client"  TEXT,
"salesman"TEXT,
"revenue" REAL,
PRIMARY KEY("client","salesman")
) WITHOUT ROWID;

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

Re: [sqlite] Bug when creating a table via select?

[J. King]

On July 15, 2019 12:01:00 a.m. EDT, "J. King"  wrote:
>On July 14, 2019 11:56:15 p.m. EDT, Donald Shepherd
> wrote:
>>Somewhat bizarrely only "BLOB" affinity doesn't make it from the
>>original
>>table to the new table when using the "select" syntax to create the
>new
>>table.  Even items with aliased affinities (VARTEXT, or something that
>>defaults to NUMERIC) comes across as the base affinity but at least
>>have an
>>affinity.
>>
>>This is simple to reproduce:
>>
>>sqlite> .version
>>SQLite 3.29.0 2019-07-10 17:32:03
>>fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6
>>zlib version 1.2.11
>>gcc-5.2.0
>>sqlite> create table x(a int, b text, c real, d blob, e vartext, f
>>garbage);
>>sqlite> pragma table_info(x);
>>0|a|int|0||0
>>1|b|text|0||0
>>2|c|real|0||0
>>3|d|blob|0||0
>>4|e|vartext|0||0
>>5|f|garbage|0||0
>>sqlite> create table y as select * from x;
>>sqlite> pragma table_info(y);
>>0|a|INT|0||0
>>1|b|TEXT|0||0
>>2|c|REAL|0||0
>>3|d||0||0
>>4|e|TEXT|0||0
>>5|f|NUM|0||0
>>sqlite> select * from sqlite_master;
>>table|x|x|2|CREATE TABLE x(a int, b text, c real, d blob, e vartext, f
>>garbage)
>>table|y|y|3|CREATE TABLE y(
>>  a INT,
>>  b TEXT,
>>  c REAL,
>>  d,
>>  e TEXT,
>>  f NUM
>>)
>>___
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>Blobs have no affinity. The result you're seeing is correct, just
>represented in a surprising way. 
>-- 
>J. King
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

I misspoke. Blobs have an affinity historically called NONE (which is distinct 
from no affinity, but that's not relevant here). Presumably SQLite represents 
the BLOB affinity as null as a means of saying "NONE", again for historical 
reasons. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug when creating a table via select?

[J. King]

On July 14, 2019 11:56:15 p.m. EDT, Donald Shepherd  
wrote:
>Somewhat bizarrely only "BLOB" affinity doesn't make it from the
>original
>table to the new table when using the "select" syntax to create the new
>table.  Even items with aliased affinities (VARTEXT, or something that
>defaults to NUMERIC) comes across as the base affinity but at least
>have an
>affinity.
>
>This is simple to reproduce:
>
>sqlite> .version
>SQLite 3.29.0 2019-07-10 17:32:03
>fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6
>zlib version 1.2.11
>gcc-5.2.0
>sqlite> create table x(a int, b text, c real, d blob, e vartext, f
>garbage);
>sqlite> pragma table_info(x);
>0|a|int|0||0
>1|b|text|0||0
>2|c|real|0||0
>3|d|blob|0||0
>4|e|vartext|0||0
>5|f|garbage|0||0
>sqlite> create table y as select * from x;
>sqlite> pragma table_info(y);
>0|a|INT|0||0
>1|b|TEXT|0||0
>2|c|REAL|0||0
>3|d||0||0
>4|e|TEXT|0||0
>5|f|NUM|0||0
>sqlite> select * from sqlite_master;
>table|x|x|2|CREATE TABLE x(a int, b text, c real, d blob, e vartext, f
>garbage)
>table|y|y|3|CREATE TABLE y(
>  a INT,
>  b TEXT,
>  c REAL,
>  d,
>  e TEXT,
>  f NUM
>)
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Blobs have no affinity. The result you're seeing is correct, just represented 
in a surprising way. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Identifying rowid aliases

[J. King]

I'm trying to ensure that something that looks like a rowid alias is in fact a 
rowid alias. I think I have the correct procedure, but I wanted to run it by 
the people here in case I missed anything. Is the following correct?

1. Check pragma_table_info() for a single-column PK
2. Check that the column's type is a case-insensitive match for "integer"
3. Check pragma_index_list() for the absence of an index with "pk" origin
4. If the table does not have all three concrete columns called _rowid_, rowid, 
and oid, perform a count on one of these to ensure no error occurs
5. If the table has all three concrete columns, examine the table's SQL as a 
last resort to confirm it is a rowid table
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bugs in foreign_key_list pragma

[J. King]

On May 24, 2019 2:22:46 p.m. EDT, "J. King"  wrote:
>First, a documentation bug:
>
><https://sqlite.org/pragma.html#pragma_foreign_key_list> does not 
>indicate that a schema may in fact be prepended to the pragma the same 
>as its siblings.
>
>Second, the following is surprising:
>
>SQLite version 3.28.0 2019-04-16 19:49:53
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> create table other (a);
>sqlite> create temp table t(a references other(a));
>sqlite> create table t(a);
>sqlite> pragma foreign_key_list(t);
>0|0|other|a|a|NO ACTION|NO ACTION|NONE
>
>Surely it should not be printing anything?
>
>--
>J. King
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

After further experimention and re-reading the documentation I realize SQLite 
is working as intended. The documentation error stands, however. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bugs in foreign_key_list pragma

[J. King]

First, a documentation bug:

<https://sqlite.org/pragma.html#pragma_foreign_key_list> does not 
indicate that a schema may in fact be prepended to the pragma the same 
as its siblings.


Second, the following is surprising:

SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table other (a);
sqlite> create temp table t(a references other(a));
sqlite> create table t(a);
sqlite> pragma foreign_key_list(t);
0|0|other|a|a|NO ACTION|NO ACTION|NONE

Surely it should not be printing anything?

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

Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

[J. King]

On May 23, 2019 6:46:52 a.m. EDT, R Smith  wrote:

>This is SQLite. Perhaps some of us could collaborate on a fork called 
>SQLbloat and put out standard libs/code/precompileds for versions of 
>sqlite with everything - bbq sauce and all, for when you don't need
>Lite 
>- then you can specify that the queries are for SQLbloat and they will 
>run out the box directly and correctly.  Linux distro wars coming to an
 
I find this a little condescending. There's a lot of reasons to like SQLite, 
and the aspect that sways me more than others is not "lite", but "zeroconf".

I mainly use SQLite in PHP and Tcl, so using my own SQLite library is often not 
practical, and in the case of PHP, loadable extensions are disabled by default. 
I as a programmer am at the mercy of what distributions package---which is 
often SQLite in its default configuration, so a less-lite-but-still-zeroconf 
SQLessLite as the default configuration would be extremely valuable to me. At 
the same time, those working in tiny systems still have tons of compile-time 
options to keep things lean. 

In short, I'm skeptical that the choices Hwaci have made about what to exclude 
are necessarily beyond reproach or discussion. Derogatory references to  
"SQLbloat" really don't further the cause of honest discussion.


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

Re: [sqlite] Bug in table_info pragma

[J. King]

On 2019-05-17 07:08:27, "Warren Young"  wrote:


On May 17, 2019, at 4:55 AM, J. King  wrote:


 SQLite version 3.28.0 2019-04-16 19:49:53
 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(a text default '' /* comment */ );
 sqlite> select dflt_value from pragma_table_info('t') where name = 'a';
 '' /* comment */

 I would expect it to print only the string delimiters.


Isn’t this the mechanism behind the .schema shell command, which just gives a 
textual dump of the schema as input, including whitespace, commas, and no 
interpretation made on the types you give?

In other words, SQLite doesn’t “digest” your schema and spit out a clean 
version, it just tells you what you told it.


Perhaps I should have been clearer that this is a regression?

SQLite version 3.13.0 2016-05-18 10:57:30
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(a text default '' /* comment */ );
sqlite> pragma table_info(t);
0|a|text|0|''|0

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

Re: [sqlite] Bug in table_info pragma

[J. King]

On 2019-05-17 06:59:58, "Simon Slavin"  wrote:


On 17 May 2019, at 11:55am, J. King  wrote:


 I would expect it to print only the string delimiters.


I might expect it to print only the thing inside the delimiters, i.e. nothing.


Then there would be no differentiating "default CURRENT_TIMESTAMP" from 
"default 'CURRENT_TIMESTAMP'".


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

[sqlite] Bug in table_info pragma

[J. King]

SQLite version 3.28.0 2019-04-16 19:49:53
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(a text default '' /* comment */ );
sqlite> select dflt_value from pragma_table_info('t') where name = 'a';
'' /* comment */

I would expect it to print only the string delimiters.

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

Re: [sqlite] Mixing main and temp databases in foreign keys is not supported

[J. King]

On May 12, 2019 7:33:20 p.m. EDT, Manuel Rigger  wrote:

>As far as I understood, the main and temp databases are always loaded
>and
>cannot be detached. But, as you pointed out, I can understand that this
>makes sense in the general case.

Note that the temp database is specific to a given connection while the main 
one is not. Creating foreign relations to the temp database would corrupt 
another connection's view of the same database
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What could happen if an app crashes while calling sqlite3_rekey_v2()?

[J. King]

It's part of the encryption extension. The function signature is 
referenced publicly here:

<https://www.sqlite.org/see/doc/release/www/readme.wiki>

--
J. King

On 2019-05-03 12:04:32, "David Raymond"  
wrote:



Random question from a non-C person: What is sqlite3_rekey_v2()?

I was curious, so looked for it in the docs and don't see it listed in the C 
reference...
https://www.sqlite.org/c3ref/funclist.html
...and it doesn't get any hits when put into the search box for the web page.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Friday, May 03, 2019 11:38 AM
To: SQLite mailing list
Subject: Re: [sqlite] What could happen if an app crashes while calling 
sqlite3_rekey_v2()?

On 3 May 2019, at 4:15pm, Pasin Suriyentrakorn  wrote:


 What could happen if an app crashes while calling sqlite3_rekey_v2()? Is there 
best practice to safely call sqlite3_rekey_v2()?


While rekey is working, the database temporarily contains both sets of some 
data.  Automatic crash recovery will see, this, recover the unconverted set, 
and wipe the converted set.  You do not need to take special precautions.
___
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] WITHOUT ROWID tables with a ROWID

[J. King]

On 2019-02-21 15:02:23, "Tom Bassel"  wrote:


Are tables t3, t4, and t5 below working as designed?


Yes. See <https://sqlite.org/lang_createtable.html#rowid>:

> The rowid value can be accessed using one of the special 
case-independent names "rowid", "oid", or "_rowid_" in place of a column 
name. If a table contains a user defined column named "rowid", "oid" or 
"_rowid_", then that name always refers the explicitly declared column 
and cannot be used to retrieve the integer rowid value.


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

[sqlite] Typo in FTS5 documentation

[J. King]

<https://www.sqlite.org/fts5.html#fts5_phrases> states:

> the following three queries all specify the same phrase:

Four queries are then listed. Presumably it should read:

> the following four queries all specify the same phrase:
-- 
J. King
___
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?

[J. King]

On February 13, 2019 5:47:09 PM EST, Thomas Kurz  wrote:

>I don't think so because this works (shortened here; it also works with
>REFERENCES...):
>
>ALTER TABLE test ADD COLUMN groupid2 INTEGER NOT NULL DEFAULT 1;

You're correct; I must have been confusing the prohibition on CURRENT_TIMESTAMP 
with a general one. 

-- 
J. King
___
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?

[J. King]

On February 13, 2019 5:32:09 PM EST, Thomas Kurz  wrote:
>I just stumbled upon the following issue (tested with 3.27.1):
>
>I can do this:
>CREATE TABLE test (groupid INTEGER NOT NULL REFERENCES mygroup (id) ON
>UPDATE CASCADE ON DELETE CASCADE);
>
>But this fails:
>ALTER TABLE test ADD COLUMN groupid2 INTEGER NOT NULL REFERENCES
>mygroup (id) ON UPDATE CASCADE ON DELETE CASCADE;
>
>--> Error: Cannot add a NOT NULL column with default value NULL
>
>I think both variants should behave consistently. But I don't know
>which behavior is the correct one according to the SQL standard.
>
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

This is a limitation of SQLite's current ALTER TABLE implementation. Columns 
can only be added with a default value of NULL, therefore NOT NULL columns are 
forbidden. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with clause "where X in (...)"

[J. King]

On February 13, 2019 3:09:31 PM EST, Don Walsh  wrote:
>Get me of this list
>
>On Wed, Feb 13, 2019, 8:11 AM David Raymond wrote:
>
>> Here's the page with the limits for what you can give SQLite
>> https://www.sqlite.org/limits.html
>>
>> Depending on what your statement ended up looking like that could be
>the
>> "Maximum Length of an SQL Statement" of 1,000,000 or maybe Maximum
>Number
>> of Columns of 2,000?
>>
>> Check to see if you're violating one of the things in there, and if
>not
>> then give us some more info on just how many items are in the list,
>and how
>> large the statement text ended up as.
>>
>> And if it exits without any error code, why do you say it's broken?
>>
>> -Original Message-
>> From: sqlite-users
>[mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Mohd Radzi Ibrahim
>> Sent: Wednesday, February 13, 2019 8:35 AM
>> To: sqlite-users@mailinglists.sqlite.org
>> Subject: [sqlite] Problem with clause "where X in (...)"
>>
>> Hi,
>> I thought that I hit a bug with SQLite 3.28.0  and 3.27.1 when my
>in-list
>> contains too many items the code breaks here:
>>
>> The sqlite3_step just exit without returning any error code.
>>
>> best regards,
>> Radzi
>> ___
>> 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

You can remove yourself by following the URL at the bottom of every message. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieve tables accessed by query

[J. King]

On February 12, 2019 12:30:17 PM EST, Zach Wasserman  
wrote:
>Hello SQLite Users,
>
>Is anyone aware of an API I can use to determine which tables are
>accessed
>by a given query? Or, is there a way to retrieve the parsed AST of a
>query?
>If this is possible directly through the SQLite shell that would be
>great,
>but C API would work as well.
>
>Thank you,
>Zach Wasserman
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Authorizer callbacks could be (ab)used for this purpose, I would think. 

<https://sqlite.org/c3ref/set_authorizer.html>

I'm not very familiar with SQLite's C interface, though, so I may be dead 
wrong. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MySQL Dumps into SQLite

[J. King]

On February 11, 2019 2:19:27 PM EST, Stephen Chrzanowski  
wrote:
>This is a dump from a MySQL table I created a few years ago.  I'm not
>moving this particular database into SQLite, but, from what I learned
>today
>about MySQL dumps and the commenting system, I was kind of interested
>on
>how SQLite would handle the rest of the following statement:
>
>CREATE TABLE `Clusters` (
>  `ClusterID` int(11) NOT NULL auto_increment,
>  `ClusterName` varchar(50) NOT NULL default '',
>  `Description` text NOT NULL,
>  `GroupID` int(11) NOT NULL default '0',
>  `ClusterOrder` int(11) NOT NULL default '255',
>  PRIMARY KEY  (`ClusterID`)
>) ENGINE=InnoDB DEFAULT CHARSET=ascii;
>
>Obviously, SQLite doesn't know what ENGINE is, or anything of the sort
>(Actually, this statement didn't work due to the auto_increment
>keyword.
>Once I removed it, I got to the meat and potatoes of this question)
>
>The version of SQLite I'm using on this ancient machine is 3.5.9.  I
>cannot
>upgrade it, as the OS is long out of support, isn't used by anyone
>other
>than our staff for a jump point to other servers, and there's so much
>legacy crap on this box that upgrading would break anything that is
>running
>on it.  I know SQLite3 is mostly backwards compatible, but I'm not
>going to
>go playing with a production machine. ;)
>
>Could there be an inch of movement for the future so that in this
>particular example, SQLite would ignore everything between that final
>closing bracket and the semi-colon?  Obviously a lost cause on this
>particular host, but, thinking about the future and all...
>
>(FWIW, I just tried on 3.20.0 and the ENGINE thing is still a "broken"
>thing)
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

For what it's worth it's not possible to ignore anything after the closing 
bracket: SQLite itself uses this space to declare WITHOUT ROWID tables. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why Error: ambiguous column name: ProjID

[J. King]

On February 11, 2019 11:16:32 AM EST, Jose Isaias Cabrera  
wrote:
>
>This SQL,
>
>SELECT a.*,b.*,c.Area,d.Bus_Area FROM Master_Project_List AS a
>LEFT JOIN Master_Project_List_Extra AS b ON a.ProjID = b.ProjID
>LEFT JOIN Bus_IT_Areas_ORGs AS c ON a.IT_OBS = c.IT_OBS
>LEFT JOIN Business_OBS_List AS d ON a.Business_OBS = d.Bus_OBS
>WHERE ProjID IN
>(
>   SELECT ProjID FROM Master_Project_List WHERE ProjID = 'PR011894'
>)
>
>  ORDER BY ProjID;
>
>is returning the error,
>
>Error: ambiguous column name: ProjID
>
>using SQLite version 3.26.0 2018-12-01 12:34:55.
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

"...WHERE ProjID IN..."

Which ProjID, a or b?
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can I assign negative value to the INTEGER PRIMARY KEY column?

[J. King]

On February 11, 2019 8:35:57 AM EST, John Smith  wrote:
>Hi,
>
>
>I read in SQLite documentation that if I define column of type INTEGER
>PRIMARY KEY then this column will become an alias to SQLite internal
>64-bit integer index that uniquely identifies the row (hence ‘rowid’).
>
>I also read that the initial default value that will be used for such
>column is 1.
>
>
>My question:
>
>I am migrating data from a previous schema to my new schema, which
>wants to use this INTEGER PRIMARY KEY capability.
>
>The thing is that in my old schema the integer indexes, which needs to
>be mapped to the new INTEGER PRIMARY KEY column, starts with value -2
>(minus two).
>
>The values are unique and are going up, but always start at (-2).
>
>Since my schema contains many relations I do not wish to modify these
>indexes.
>
>My question – if I copy the indexes values as-is, so some are negative,
>will my INTEGER PRIMARY KEY column still be an alias to the SQLite
>internal ‘rowid’ column, or will such values break this alias
>connection?
>
>Many thanks, Paz
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Rowids are signed integers and can be negative. The documentation does advise 
(somewhere...) against using negative rowids because they are larger (when 
stored) than the typical used range of positive ones, but that's it. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Was there an announcement of 3.27?

[J. King]

On February 8, 2019 3:31:51 PM EST, "Dominique Pellé" 
 wrote:
>David Raymond wrote:
>
>> SQLite version 3.27.1 is now available on the SQLite website:
>>
>>   https://sqlite.org/
>>   https://sqlite.org/download.html
>>   https://sqlite.org/releaselog/3_27_1.html
>
>Release notes https://sqlite.org/releaselog/3_27_1.html say:
>
>=== BEGIN QUOTE ===
>Added the remove_diacritics=2 option to FTS3 and FTS5.
>=== END QUOTE ===
>
>I wonder that this does.  FTS3 or FTS5 doc were not updated,
>since they only document remove_diacritics=0 and 1.
>
>I also use the opportunity to report a few typos in
>https://sqlite.org/fts5.html:
>
>- the second character replaced with an *asterix* (-> asterisk)
>- fts5 extension function made as part *of of* (repeated word "of")
>-  *an the* (-> the) auxiliary data is set to NULL
>
>Regards
>Dominique
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

I'm sure someone savvier will correct me, but the source does indicate that 1 
is "simple" and 2 "complex". It's not entirely clear, but it seems like " 
complex" is able to handle multiple diacritics on one base character and 
"simple" cannot?
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOINning two tables provides no data

[J. King]

On January 18, 2019 12:54:32 PM EST, Jose Isaias Cabrera  
wrote:
>
>sqlite> select a.a, a.b, a.c, b.d, b.e as q from a join b on a.a=b.a
>where a.a = 1;
>sqlite>
>
>This provides no result.  I expected to get,
>
>1|2|3||


You appear to seek the result of a LEFT JOIN.
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.24.0 regression from enhanced "ALTER TABLE true/false DEFAULT" on TEXT field

[J. King]

On January 11, 2019 7:03:03 AM EST, Christian Nols 
 wrote:
>From "Enhance ALTER
>TABLE<https://www.sqlite.org/draft/lang_altertable.html> so that it
>recognizes "true" and "false" as valid arguments to DEFAULT" introduced
>in 3.24.0
>
>A TEXT field can no longer have "true"/"false" default values.
>The smart DEFAULT conversion should not be triggered if the hint is
>TEXT.
>
>Although SQLite is typeless, table datatype hint could be use in this
>use scenario.
>
>Steps to reproduce
>sqlite> CREATE TABLE "Table_1" ([ID] TEXT DEFAULT "false", [Field_1]
>TEXT NULL);
>sqlite> insert into "Table_1" (Field_1) VALUES ("test");
>sqlite> select * from Table_1;
>0|test
>
>Expected result
>false|test
>
>Christian Nols
>Software Engineer
>
>christian.n...@mcl-technologies.com
>Tel : + 32 2 724 35 19
>
>MCL
>NYSDAM Building
>avenue Reine Astrid 92, 3rd floor
>1310 La Hulpe- Belgium
>BCE registered number 2.087.817.310
>Tel : + 32 2 724 35 00 - Fax : + 32 2 724 35 04
>www.mcl-collection.com<http://www.mcl-collection.com/>
>
>_______
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

The default should be defined as string 'true', not identifier "true". 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Detecting erroneous multi-row subqueries

[J. King]

On 2018-11-29 21:17:59, "Keith Medcalf"  wrote:


while I cannot comment on the "reverse_unordered_select" pragma, you are 
getting the result you are because you are using union rather than union all.

The actual query didn't use a union and wasn't affected by the pragma 
either (presumably for some other reason I'd have to puzzle out), so I 
didn't realize my error. Thanks.




Probably not since this would break backwards compatibility.  It is however 
documented:

https://sqlite.org/lang_expr.html#subq

I've probably read that several times over the years and didn't recall 
it when it was actually important. Once again, thanks. :)


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

Re: [sqlite] Detecting erroneous multi-row subqueries

[J. King]

On 2018-11-29 20:56:13, "Richard Hipp"  wrote:


On 11/29/18, J. King  wrote:

 Is it possible to make SQLite fail like
 PostgreSQL does?


That is possible in theory, but how many of the millions of existing
applications would that break?



I was wondering more if it's -currently- possible via some switch I'm 
not aware of. As an enhancement obviously I wouldn't expect it as a 
default (not before SQLite 4, anyway), but as a debugging feature like 
reverse_unordered_selects, it might help to uncover millions of bugs. ;)


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

[sqlite] Detecting erroneous multi-row subqueries

[J. King]

Good people of sqlite-users,
I've been porting an application originally designed for SQLite to 
PostgreSQL, and the latter failed on a test case where the former does 
not. While I've encountered many more such failures during the process 
than I'd like, in this case I think PostgreSQL's behaviour is more 
desirable. Here's a minimal query:


select (select 'foo' union select 'bar') || 'bar';

SQLite 3.25.3 returns 'barbar' (regardless of the value of the 
reverse_unordered_select pragma) while PostgreSQL 11 refuses to process 
the query unless the subquery is reduced to a single row. In my 
application the actual query was erroneous and would potentially return 
data belonging to a user other than the one making the request, which 
concerns me quite a bit. Is it possible to make SQLite fail like 
PostgreSQL does?


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

[sqlite] Omitted features documentation slightly out of date

[J. King]

The documentation page at <https://sqlite.org/omitted.html> under ALTER 
TABLE mentions that RENAME TABLE and ADD COLUMN are supported, but does 
not mention the new RENAME COLUMN.


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

Re: [sqlite] minor nit pick of "When To Use"

[J. King]

On November 11, 2018 8:04:51 AM EST, Dennis Clarke  
wrote:
>
>this : https://www.sqlite.org/whentouse.html
>
>I know this is really a nit pick but I see that the OpenGroup folks
>have
>published a new shiney UNIX standard called SUSv4 or Single UNIX
>Specification version 4 or even ye "The Open Group Base Specifications
>Issue 7, 2018 edition IEEE Std 1003.1-2017 (Revision of IEEE Std
>1003.1-2008)" just for fun.  This :
>
> http://pubs.opengroup.org/onlinepubs/9699919799/toc.htm
>
>Where we see the "standard" fopen is :
>
>   http://pubs.opengroup.org/onlinepubs/9699919799/functions/fopen.html
>
>Not exactly a manpage to be sure but it is the standard.
>
>So perhaps a link could be placed on the top of the "When To Use"
>beside
>the "fopen()" manpage thing to "he.net" whatever that is. Surely the
>"competes with fopen()" is a small joke in any case. OKay I will go
>back
>to picking nits.
>
>Dennis
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

he.net is Hurricane Electric, an Internet backbone. Not exactly the authority 
on manpages, I'll grant, but given what they do it's not likely to go down, so 
it's a stable reference for the manual. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] found a glitch in ALTER TABLE RENAME (3.25.x)

[J. King]

On November 9, 2018 2:50:56 AM EST, Dominique Devienne  
wrote:
>On Fri, Nov 9, 2018 at 8:26 AM Simon Slavin 
>wrote:
>
>> On 9 Nov 2018, at 7:11am, Hick Gunter  wrote:
>> > Foreign keys are ignored by default and need to be explicitly
>enabled. I
>> would expect this to include everything that relates to foreign keys.
>> > I've casually discovered that the behavior of ALTER TABLE RENAME TO
>> (versione 3.25.x) seems to be affected by an odd glitch; FOREIGN KEY
>> constraints are updated as expected only when PRAGMA foreign_keys=1,
>> otherwise they are just ignored.
>>
>> While your statement is correct, I would argue that the behaviour is
>> harmful, and cannot possibly be what any programmer could want to
>happen.
>> Think how difficult it would be to diagnose this having happened, and
>then
>> fix the problem it would create.
>> I might argue that correct behaviour would be to transform FOREIGN
>KEY
>> constraints even when the PRAGMA is off.  Or I might argue that the
>attempt
>> should generate an error result (SQLITE_MISUSE ?).
>>
>
>Foreign keys are ignored in the sense they are not enforced, but they
>are
>still parsed AFAIK.
>As such, I agree with Simon that it's harmful to not update them on
>table
>(or column) renames. --DD
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

That could lead to loss of referential integrity when modifying a table in a 
way not supported by ALTER TABLE, I believe.  One usual method is to turn 
foreign keys off, rename the old table, create a new modified table under the 
old name, insert the old table's data into the new, and then drop the old 
table. If foreign keys were, for lack of a better term, partially enforced, 
this process would yield new and backwards-incompatible results: when the old 
table is renamed all referring tables would continue to refer to the old table, 
and when the table is dropped all references would become invalid. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Segmentation Fault When Using Window Function

[J. King]

On 2018-11-08 16:05:11, "Richard Hipp"  wrote:


In case you are not following the ticket at
https://www.sqlite.org/src/tktview/787fa716be3a7f650cac1b1413f12f95f5e7639d,
we have simplified the test case to the following:



Richard,
My mail client appears to have exposed a bug in Fossil. In including the 
comma in its link-making of the URL (i.e. 
<https://www.sqlite.org/src/tktview/787fa716be3a7f650cac1b1413f12f95f5e7639d,>), 
it brought me to a technically valid (response status 200) resource with 
an empty ticket metadata table save for a ticket UUID of "Deleted". As 
the ticket UUID is not deleted (it is invalid) I would have expected a 
404 response with an appropriate message.


--
J. King

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

Re: [sqlite] A couple of questions about prepared statements

[J. King]

On 2018-10-19 10:43:21, "Tim Streater"  wrote:


Suppose I have an SQL statement like this:

select somecol from mytable where myid=3 and badtext="usertext" and 
goodtext="somegoodtext"


Suppose further that the values of myid and goodtext are generated by 
reliably me whereas badtext is supplied externally. If I want to use a 
prepared statement here, is it OK to generate the myid and goodtext 
parts using the usual string methods from my host language, leaving 
only badtext as a bound variable, so that my prepared statement looks 
like this:


select somecol from mytable where myid=3 and badtext=? and 
goodtext="somegoodtext"


That would simplify my life.
It might complicate your life later. If your "safe" data gets mixed with 
unsafe data somewhere that you're not aware of (or it changes in a later 
version of your application), you're opening yourself up to stealth 
bugs. Binding everything reduces guesswork and long-term maintenance.


Also, statements have a maximum size: if your embedded information is 
large (or later becomes large), you can run into problems there, too.


Note, too, that the correct way to quote string is with single-quote 
characters. SQLite will accept double-quotes when it's unambiguous, but 
it's a good habit to get out of, for the ambiguous cases.




My other question relates to when the database is actually touched. Is 
it the case that statement preparation and variable binding do not 
affect the database itself and it's only when the prepared statement is 
actually executed that the database is touched and might generate an 
SQLITE_BUSY response?


When the transaction is committed. If you've not explicitly begun a 
transaction, then when the statement is executed, yes.


--
J. King

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

Re: [sqlite] Blob and Text columns: what's the difference?

[J. King]

On 2018-08-28 09:50:01, "Tim Streater"  wrote:

What is actually the difference between a column declared as TEXT and 
one declared as BLOB in an SQLite database? What does SQLite do to 
textual data that I ask it to put into a TEXT column? How does it know 
not to do that if I want to send some binary data to a Text column?



I don't know all the ins and outs, but text columnns are convertible 
between UTF-8 and UTF-16 upon request, whereas blobs are just byte 
strings.


--
J. King

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

Re: [sqlite] Very, very slow commits

[J. King]

On July 29, 2018 5:47:29 AM EDT, Rob Willett  
wrote:
>John,
>
>Thanks for the prompt reply and a very good question..
>
>We've dropped the indexes and the commit is now very quick, approx two 
>seconds
>
>However the overall performance of the run is much the same as other 
>areas of the code are now significantly slower, whereas before they
>were 
>quick.
>
>Where were you going with that question?
>
Might ANALYZE help? 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Back on-line. Was: Mailing list shutting down...

[J. King]

On June 15, 2018 12:17:31 PM EDT, dmp  wrote:
>> Mailing lists are now back on-line and once again accepting
>> subscriptions.  I have implemented measures to block the subscription
>> robots and to better log subscription activity to better detect
>future
>> mischief.
>
>> I consider this to be a stop-gap measure that will buy me some time
>> to implement and test a better log-term solution. .
>
>> D. Richard Hipp
>> d...@sqlite.org
>
>As a comment, again with past post with regard to Mailing List.
>
>This mailing list is a very informative, simple, and a conveniant
>method to disperse information in a bulk format. A change to a web
>interface, (forum, other), that requires a login each day is most
>likely going to push me away.
>
>Hope a fix can be accomplished.
>
>danap.
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Opinions on this have made the rounds here before, but I will reiterate that I 
feel the same. I would be especially sad to see this discussion list move to 
Discourse (as was suggested and apparently explored earlier this week), as I 
find that software very unresponsive and difficult to use on Firefox for 
Android, and I do much of my reading of this list on the go (like right now).


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

Re: [sqlite] Issue with node-sqlite3

[J. King]

>I can see you didn't quite understand me. The project I am attempting
>to
>install sqlite3 on is an Angular 6 project. Now, in the package sqlite3
>there is a CS file that spits out a warning  
Ryan's advice, though, is still correct: this is not a NodeJS or Angular 
mailing list. You'd probably have more luck asking in a forum for those 
technologies first. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Usage of temporary files of SQLite3 on Android / SQLite commands fail

[J. King]

On June 9, 2018 1:08:07 PM EDT, J Decker  wrote:
>On Sat, Jun 9, 2018 at 10:00 AM skywind mailing lists <
>mailingli...@skywind.eu> wrote:
>
>> Hi,
>>
>> what is your experience with temporary files requested by SQLite3 on
>> Android? I am using SQLite3 natively on Android - I compiled SQLite3
>by
>> myself - and get an error when SQLite3 tries to create temporary
>files
>> because:
>>
>> - the directories
>> /var/tmp
>> /usr/tmp
>> /tmp
>> .
>>
>Do you really NEED it to be a file or could you just open it as
>:memory: ?
>
>
>>   are not accessible by a standard user (and therefore the app);
>> - getenv is not guaranteed to be supported (or?);
>> - sqlite3_temp_directory should not be used because it is deprecated.
>>
>> What are you doing?
>>
>> Regards,
>> Hartwig
>>
>> ___
>> 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 also creates temp files in various circumstances:

<https://sqlite.org/tempfiles.html>

This is definitely a valid question, though not one for which I have an answer. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite.org website is now HTTPS-only

[J. King]

On June 7, 2018 3:52:04 PM EDT, Stephen Chrzanowski  wrote:
>Probably, yes.  Its running Debian Lenny.  I'm trying to locate a
>resource
>right now to see if I can get the appropriate files, and how to keep
>them
>updated.
>
>On Thu, Jun 7, 2018 at 3:43 PM, Simon Slavin 
>wrote:
>
>> On 7 Jun 2018, at 8:35pm, Stephen Chrzanowski 
>wrote:
>>
>> > stephen@vmLamp:~$ wget -O - https://sqlite.org/download.html
>> > --15:30:59--  https://sqlite.org/download.html
>> >   => `-'
>> > Resolving sqlite.org... 45.33.6.223
>> > Connecting to sqlite.org|45.33.6.223|:443... connected.
>> > ERROR: Certificate verification error for sqlite.org: unable to get
>> local
>> > issuer certificate
>> > To connect to sqlite.org insecurely, use `--no-check-certificate'.
>> > Unable to establish SSL connection.
>> >
>> > In my browser, however, the cert does show up as green
>>
>> Your copy of wget is using a different set of Certification Authority
>> certificates to those used by your browser.  Since your browser was
>updated
>> more recently than your OS (purely a guess on my part) I'm guessing
>that
>> the certificates used by "wget" are slightly out of date.
>>
>> 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

For what it's worth, it looks like Jessie's ca-certificates package includes 
the ISRG's root CA certificate needed to validate Let's Encrypt certs. 
Presumably with the appropriate level of access you could install it manually. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[J. King]

On May 31, 2018 12:18:51 PM EDT, Dominique Devienne  wrote:
>On Thu, May 31, 2018 at 3:44 PM Richard Hipp  wrote:
>
>> For many years, we have boasted that the size of the SQLite library
>is
>> "less than half a megabyte".
>>
>
>Given where the conversation is going, let me point out that many do
>not
>care one bit about the lib's size :)
>
>I'd much rather have an SQLite with tons of features, than forego those
>in
>the name saving a few bytes,
>to save a few bucks on the embedded chip and flash for commercial
>products
>that don't even pay for SQLite.
>
>SQLite is already amazingly small for the value it brings. And if
>people
>want "smaller", they can still stick
>with older leaner versions of SQLite too. My $0.02... --DD
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

I agree with this sentiment. I mostly use SQLite in PHP, where it is awkward to 
customize SQLite, and all but impossible to rely on features not included in 
the standard build when distributing to others. A more powerful default 
configuration would be very beneficial, and a less powerful one possibly 
crippling. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vetting SQLite

[J. King]

I believe it's only since Windows 8. 

On February 5, 2018 7:51:39 PM EST, Stephen Chrzanowski  
wrote:
>I was surprised to see that statement, so, checking my system, this
>isn't
>true.  Win7Pro-x64.  Not with that filename anyways.  Searching my
>system
>with the "Everything" tool, [ *sqlite3.exe ] comes up with DLLs that
>I've
>touched only.  The DLL's I've dumped into the Windows directories exist
>in
>c:\Windows\SysWOW64 only because c:\Windows\System32 is redirected
>there.
>On my system, there is only one sqlite3.dll with a timestamp of Aug 11,
>2016, and is version 3.14.1.0 according to the Details tab.
>
>
>On Mon, Feb 5, 2018 at 6:02 PM, Richard Hipp  wrote:
>
>> On 2/5/18, Drago, William @ CSG - NARDA-MITEQ 
>> wrote:
>> >
>> > Most of the software we use here, Microsoft and other well-known
>and
>> > paid-for products,
>>
>> You know that every copy of Windows comes with SQLite preinstalled,
>> right?  C:\Windows\System32\winsqlite3.dll
>> --
>> 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

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vetting SQLite

[J. King]

I'm not aware of a statement or guarantee, but the Web site provides lots of 
evidence here:



In particular, SQLite being used as part of aircraft software by Airbus should 
tell you something. 

On February 5, 2018 12:21:53 PM EST, "Drago, William @ CSG - NARDA-MITEQ" 
 wrote:
>All,
>
>I've been using/loving SQLite for years, but the use of open source
>software is highly discouraged where I work, and now I have to prove to
>our IT dept. that SQLite is reliable and secure. The reliable part is
>easy because there is enough information on the SQLite website about
>testing, but what about security? How can I convince the auditors that
>SQLite is not stealing corporate secrets and spreading viruses?
>
>Is there a statement somewhere on the website that guarantees that
>copies of SQLIte downloaded from SQLite.org and System.Data.Sqlite.org
>are free of all forms of spyware/malware/viruses/etc?
>
>Thanks,
>--
>Bill Drago
>Staff Engineer
>L3 Narda-MITEQ
>435 Moreland Road
>Hauppauge, NY 11788
>631-272-5947 / william.dr...@l3t.com
>
>CONFIDENTIALITY NOTICE: This email and any attachments are for the sole
>use of the intended recipient and may contain material that is
>proprietary, confidential, privileged or otherwise legally protected or
>restricted under applicable government laws. Any review, disclosure,
>distributing or other use without expressed permission of the sender is
>strictly prohibited. If you are not the intended recipient, please
>contact the sender and delete all copies without reading, printing, or
>saving..
>
>Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use
>of all @L-3Com.com email addresses. To ensure delivery of your messages
>to this recipient, please update your records to use
>william.dr...@l3t.com.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF8 and NUL

[J. King]

On 2018-01-26 15:13:46, "Peter Da Silva" <peter.dasi...@flightaware.com> 
wrote:


On 1/26/18, 2:11 PM, "sqlite-users on behalf of John McKown" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
john.archie.mck...@gmail.com> wrote:
​In the distant past (CP/M-80), the filesystem meta data did not 
include the actual _length_ of the data for a text data file.


Since DOS wasn't an OS, then CP/M certainly wasn't.


Do you have a point in making either statement? If you do, I'm really 
not seeing it.


--
J. King

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

Re: [sqlite] Minor JSON output bug (infinity)

[J. King]

RFC 8259 states:

> Numeric values that cannot be represented in the grammar below (such as 
> Infinity and NaN) are not permitted. 

This is one of the cases that expose the fallacy of the "JS" part of "JSON". 
While SQLite should not be producing Inf as a bare word, it should not be 
producing Infinity, either, as a conforming parser would reject both. 

What to do in such a case is undefined, but for the stated case there is 
actually a very sensible conforming output:

'[1e,-1e,null]'

I realize it is impractical for SQLite to do so, but given that JSON numbers 
convey arbitrary precision, only explicit infinity should, ideally, result in 
undefined behaviour. 

On January 22, 2018 9:00:35 PM EST, Alexander Beedie 
 wrote:
>Test-case / repro:
>“SELECT JSON_ARRAY(1e,-1e,NULL)”
>
>Actual output:
>‘[Inf,-Inf,null]’
>
>Expected output:
>‘[Infinity,-Infinity,null]’
>
>All JSON parsers I have tried fail on “Inf”, but the majority will
>succeed with “Infinity” (as this is the standard JS property name)
>
>eg: in standard python -
>
>>> import json
>>> json.loads( ‘[Inf,-Inf,null]’ )
>ValueError: No JSON object could be decoded
>>> json.loads( ‘[Infinity,-Infinity,null]’ )
>[inf, -inf, None]
>
>
>Regards,
>
>-Alex
>--
>iPhoneから送信
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation typo

[J. King]

In <http://sqlite.org/howtocorrupt.html#cfgerr> the following passage 
contains a typo ("crass" ->  "crash"):


Setting PRAGMA synchronous=OFF can cause the database to go corrupt 
if there is an operating-system crass or power failure...


--
J. King

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

Re: [sqlite] Move to Github!!?

[J. King]

I use Git, but I'm not attached to it. I run my own publicly-accessible remote 
(using Gitea), but that would be completely replaceable with Fossil (which I am 
very impressed by).

What's not so easy to replace is the Git integration in my editor (Visual 
Studio Code) which allows me to easily perform basic operation like commit, 
push, pull, and rebase. 

Even just to the ability to review diffs and perform checkins in my editor 
would be enough, but I doubt it will happen anytime soon, if ever, and I don't 
have the expertise required to hack it on myself. 

Until the landscape changes (or someone can suggest suitable Windows software), 
I will continue to admire Fossil from afar.

On December 26, 2017 10:08:08 AM EST, Damien Sykes 
 wrote:
>Hi,
>This is a question I have asked myself many times (I.E. Git projects
>moving 
>to Fossil).
>GitHub is well known and boasts over 74 million repositories, yet
>Fossil, 
>which both hosts and utilises one of the most well-known database
>systems in 
>the world, I doubt can count a thousand. Even the ChiselApp hosting
>platform 
>hosts a mere 360 public repositories, Hydra hosts 11, WanderingHorse
>hosts 
>23, outside of which lie Fossil itself, the Fossil book, SQLite and
>friends 
>(5 publicly accessible repositories in all), and TCL and friends (7 
>repositories), making a total of 408. Add SQLite private repositories,
>and 
>private repositories that I host, have access to or otherwise generally
>know 
>exist, and I come up with an estimate of roughly 470 repositories. Of
>course 
>this is not an accurate statistic since it may exclude more private 
>repositories, and definitely excludes any local repositories (I for one
>have 
>about a dozen Git repositories as Fossil repositories).
>While I am making every attempt to try to persuade friends towards
>Fossil, 
>they are also choosing Git. Looks to me like the only people who seem
>to use 
>Fossil are those who are most associated with it, which is a real
>shame.
>The only advantage I can see with GitHub is that it's the source code 
>Twitter equivalent. Everybody's repository is in one place. As long as
>you 
>know the username and repository name you know the full repository URL,
>and 
>you don't have to worry about server administration. With Fossil, if I 
>wanted to make it feel like github, I.E. address.tld/user/repo, I would
>have 
>to script it and serve it via a webserver rather than Fossil's own
>server, 
>two processes which I am not at all skilled enough, at least at the
>moment, 
>to undertake. To give you an example, I am currently having to run two 
>systems, one for my website and one for Fossil, so that they can both
>work 
>on port 80, because I know nothing about networking in order to
>understand 
>IP addresses, ports and connections in the way I'd need to get a server
>and 
>Fossil to run on port 80 on the same machine, nor do I know enough
>about 
>webservers to be able to get it to work with CGI. In fact I know so
>little 
>that I follow installation guides to the letter and have to do a
>complete 
>fresh server reset and reinstall from scratch when something goes wrong
>
>because I haven't a clue how to fix it. If I'm to be totally honest at
>the 
>moment I'm even beginning to doubt my own software development skills.
>If there were a Fossil-based github-like system, and both Fossil and
>the 
>hosting system were well promoted, Fossil may or may not become the
>norm.
>Having said that, the advantage of Fossil over Git is that, thanks to
>the 
>webserver, you can easily look at your changes in a laid-out website,
>even 
>on your own machine. I've many a time found myself importing Git 
>repositories into Fossil just to look at the timeline. Plus, you don't
>have 
>to worry about complicated concepts like pull requests, synchronising
>forks 
>and submodules etc. Though it doesn't matter what VCS I use I always
>seem to 
>come across, and struggle with, the concept of branching and merging!
>In any case, my incompetencies aside. People seem to be slowly moving
>away 
>from SourceForge in favour of GitHub. If only we could make the same 
>revolution with Fossil! Ironically when I first came across a site
>called 
>FossHub I actually thought that was an attempt to make a Fossil-based 
>GitHub. Seems that isn't the case after all.
>Cheers.
>Damien.
>-Original Message- 
>From: Richard Hipp
>Sent: Tuesday, December 26, 2017 1:10 PM
>To: SQLite mailing list
>Cc: shekharreddy.k...@gmail.com
>Subject: Re: [sqlite] Move to Github!!?
>
>On 12/25/17, Shekhar Reddy  wrote:
>>
>> Is there any particular reason that the source is not moved to
>GitHub? I
>> think that would reach more number of people there.
>>
>
>There is a mirror of the SQLite repository on GitHub at
>https://github.com/mackyle/sqlite (maintained by GitHub user "mackyle"
>whom I do not know, but whose efforts I do appreciate).
>
>SQLite uses a different version control 

Re: [sqlite] Move to Github!!?

[J. King]

SQLite source is managed in a Fossil (not Git)  repository, which is software 
itself designed by Dr. Hipp and based on SQLite. GitHub would be an entirely 
inappropriate venue. 

SQLite is also not open source software in the conventional sense. SQLite is 
written by a small team of people, and outside contributions are far less 
common than in most open source software. 

On December 25, 2017 1:19:18 PM EST, Shekhar Reddy 
 wrote:
>Hi,
>
>Is there any particular reason that the source is not moved to GitHub?
>I
>think that would reach more number of people there.
>
>Regards
>Shekhar
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR REPLACE statement and triggers

[J. King]

The documentation states that in cases of replacement the DELETE trigger only 
fires if recursive triggers are enabled. Recursive triggers are currently off 
by default. 




On December 23, 2017 7:05:59 AM EST, R Smith  wrote:
>
>
>On 2017/12/23 1:57 PM, skywind mailing lists wrote:
>> Hello,
>>
>> I have checked the documentation but did not find an explicit answer
>concerning my case. Of course I can test it but this does not mean that
>the functionality is guaranteed also for future versions. Assume I have
>an insertion statement like
>>
>> INSERT OR REPLACE INTO TestTable VALUES(1,2);
>>
>> Furthermore, I have an INSERT, UPDATE and DELETE Trigger for the
>table. Which triggers are triggered in case of
>>
>> a) insertion (pretty obvious, should always be the INSERT trigger)
>> b) replace (DELETE and INSERT trigger or UPDATE trigger or depends
>on... .)
>
>INSERT OR REPLACE amounts to either just "INSERT" or "DELETE, then 
>INSERT" based on whether or not the Constraint/Key is violated, so you 
>are guaranteed to have the INSERT trigger fire, but if the item did 
>exist before, the DELETE trigger will fire first.
>
>HTH,
>Ryan
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug with expression evaluation?

[J. King]

Someone please correct me if I'm wrong, but I believe it's mandated by the SQL 
standard that integer division is used when both operands are integers. 

Your synthetic example doesn't use a fixed table, but if it did the easiest 
solution for you would probably be to define any columns where you need 
arbitrary precision as REAL rather than INTEGER, and SQLite column affinity 
would do the rest.

Otherwise, yes, I believe you would need to cast. 

On December 14, 2017 11:36:19 AM EST, Tony Papadimitriou  wrote:
>I’ve noticed this (very annoying) behavior:
>
>select column1*(24/100) wrong from (values(100));
>
>Removing the parentheses yields the correct result:
>
>select column1*24/100 correct from (values(100));
>
>This obviously behaves like integer math is used and (24/100) gets
>truncated to zero.
>
>If I add a dot to either number (e.g., 24. or 100.) I get the correct
>result.
>But, with named fields, it’s not as easy as adding a dot:
>
>select column1*(column2/column3) wrong from (values(100,24,100));
>select column1*column2/column3 correct from (values(100,24,100));
>
>So, to get correct answer I have to use a cast for either field?
>
>select column1*(cast(column2 as float)/column3) correct from
>(values(100,24,100));
>
>In this example removing the parentheses is a simple solution.
>But if the expression was column1*(1+column2/column3) a cast is the
>only way?  (Hope not!)
>
>Anyway, if all this happens to be so by design, is there at least some
>way to default to float math rather than integer?
>
>Thanks.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a trigger recursively update a table?

[J. King]

CTEs cannot be used inside triggers for UPDATE statements. See near the bottom 
of:


On December 12, 2017 6:44:35 PM EST, Shane Dev  wrote:
>Hi,
>
>I have a hierarchical table -
>
>sqlite> .sch hierarchy
>CREATE TABLE hierarchy(id integer primary key, parent references
>hierarchy,
>descrip text, status text);
>
>with some entries -
>
>sqlite> select * from hierarchy;
>id  parent  descrip status
>1   rootopen
>2   1   branch1 open
>3   1   branch2 open
>4   3   branch22open
>5   4   branch222   open
>6   1   branch3 open
>
>I can clear the status field of the second branch (i.e id = 3, 4 and 5)
>with -
>
>with recursive cte(lev, id, parent, status) as (select 0, id, parent,
>status from hierarchy where id=3 union all select lev+1, h.id,
>h.parent,
>h.status from hierarchy as h, cte where h.parent=cte.id) update
>hierarchy
>set status=null where id in (select id from cte);
>
>sqlite> select * from hierarchy;
>id  parent  descrip status
>1   rootopen
>2   1   branch1 open
>3   1   branch2
>4   3   branch22
>5   4   branch222
>6   1   branch3 open
>
>However, if I try to create a trigger with this statement -
>
>sqlite> create view vhierarchy as select * from hierarchy;
>sqlite> create trigger thierarchy instead of delete on vhierarchy begin
>with recursive cte(lev, id, parent, status) as (select 0, id, parent,
>status from hierarchy where id=old.id union all select lev+1, h.id,
>h.parent, h.status from hierarchy as h, cte where h.parent=cte.id)
>update
>hierarchy set status=null where id in (select id from cte); end;
>
>I get an error -
>
>Error: near "update": syntax error
>
>Can anyone see why?
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[J. King]

Please forgive the off-topicedness of this message, but I would like to second 
that this mailing list is incredibly edifying, and I would also like thank 
everyone who has both answered and asked questions in the time I've been 
subscribed. I've stumbled across answers here that I never thought to ask, many 
of which have been helpful in refining my application. My reading here has been 
quite invaluable. 

On November 28, 2017 9:24:57 PM EST, Peter Halasz  
wrote:
>That's terrific! Looking forward to it being in a full release.
>
>Hopefully the next tool developer to come along won't need to write a
>custom SQL parser, get lost in irrelevant datatype documentation, sift
>through op codes from an unnestable "explain" statement, compile a
>custom
>SQLite C extension, re-implement SQLite's ROWID logic, or need to sign
>up
>to this mailing list--which has genuinely been very helpful and
>enlightening--because there will be a "table_ipk" pragma statement that
>does what they need.
>
>Cheers,
>
>Peter H.  (Pengo)
>
>On Wed, Nov 29, 2017 at 3:10 AM, petern 
>wrote:
>
>> Hey Peter.  Good on you.  Lobbying for sensible fixes to the public
>API
>> does pay off sometimes.
>>
>> There's a new branch in the timeline.  [Watch for a merge here:
>> https://www.sqlite.org/src/timeline?n=50 ]
>>
>> https://www.sqlite.org/src/info/2494132a2b1221a4
>>
>>   **   PRAGMA table_ipk()  **  ** If  has an INTEGER
>> PRIMARY KEY column that is an alias for  ** the ROWID, then return
>the
>> name of that column.  If  does not  ** have a ROWID alias, or
>> if it does not have a ROWID, or if  is  ** a view or virtual
>> table or if it does not exist, then return no rows.
>>
>> The lesson here is that one may safely ignore the sometimes
>authoritative
>> sounding status quo mafia who occupy this mailing list but have no
>source
>> check-in credentials whatsoever.
>>
>> On Mon, Nov 27, 2017 at 4:38 PM, Peter Halasz
>
>> wrote:
>>
>> > Disclaimer: I am not a developer on linq2db. Nor am I a developer
>on "DB
>> > Browser for SQLite", nor am I a developer of any of the other tools
>which
>> > help developers create SQLite databases and integrate them with
>their
>> > projects through UIs and APIs built on SQLite and its metadata.
>> >
>> > However, it's quite clear to me, as only a user of just a few of
>these
>> > tools and libraries, that they would be improved greatly if they
>had a
>> > direct way of querying which field in a SQLite table was acting as
>an
>> alias
>> > of ROW ID.
>> >
>> > linq2db, for example, has no simple way of definitely telling
>whether a
>> > field is a true INTEGER PRIMARY KEY, so its developers have left it
>as an
>> > exercise for its library users to debug errors which come up as a
>result,
>> > and to manually "correct column definitions". Something which can
>result
>> in
>> > much time wasted unnecessarily tracking down the bugs, finding
>special
>> API
>> > calls to work around them, and a less than favorable view of SQLite
>for
>> its
>> > documentation of these anomalies.
>> >
>> > linq2db could, in theory, fix this issue. But it would take a bunch
>of
>> > error-prone work to re-create SQLite's ROW ID rules, so they have
>chosen
>> > not to. They have simply chosen to ignore the weird behavior that
>comes
>> > form not differentiating an INTEGER PRIMARY KEY from an INT PRIMARY
>KEY.
>> I
>> > don't blame them for having other priorities. After all, linq2db
>> integrates
>> > with 12 other database engines. And this isn't about linq2db, it's
>about
>> > every tool that comes across the need for this metadata.
>> >
>> > Many other tools also have bugs or poor UX because they cannot
>access a
>> > simple bit of metadata. This could be solved quite easily if SQLite
>> simply
>> > exposed this data.
>> >
>> > I was honestly stunned when I discovered this basic information,
>> something
>> > which completely changes the behavior of a field, is not accessible
>to
>> > users.
>> >
>> > Please can SQLite developers make the ROW ID status of a field
>visible in
>> > future versions?
>> >
>> > It could be done either directly through a PRAGMA statement, or
>slightly
>> > less directly by exposing "isRowId()" or "collseq()" style methods
>as
>> > posted by peter.nichvolodov in the previous thread.
>> >
>> > I really hope there is not resistance this idea. It's really
>amazing to
>> me
>> > that such a mature, battle-tested database engine does not fully
>expose
>> its
>> > metadata and field behavior.
>> >
>> > Cheers
>> >
>> > Peter H.
>> > ___
>> > 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] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[J. King]

Peter (that is, the other Peter) is being more than a little flippant, yes, and 
at least a little obscure (I don't get the joke, either), but the substance 
appears quite serious. 

His prior message suggested using what I can only assume would be a trivial 
extension to SQLite to do what you want to do---this extension, however, does 
not (already) exist. 

His last message provided what I can only assume is an example implementation 
of such an extension. 

On November 24, 2017 11:20:33 PM EST, Peter Halasz  
wrote:
>Is this a joke?
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[J. King]

Version 1 UUIDs only use a random number (16 bits) in the case of an 
uninitialized clock sequence (a case which, ideally, should only occur the 
first time a device generates a UUID). Version 1 UUIDs especially avoid using 
random numbers; they are also not a shortening of longer input. 

In short, version 1 UUIDs are not a PRNG scheme, nor are they the same as hash 
functions. 

I'm not a mathematician, and it's been a while since I've read the relevant 
RFC, but I believe collisions in a proper, strict implementation of V1 UUIDs 
are impossible by design until either a) the 60-bit timestamp overflows, or b) 
the MAC address namespace is exhausted. It's not a matter of probability, and 
it's only "a certainty" after the end of their design lifetime. 

Of course, UUIDs being of finite size, they will eventually be exhausted, and a 
single machine may only generate 65536 identifiers in a 100-nanosecond span of 
time. They will not, however, collide. 

On November 24, 2017 8:58:02 PM EST, Jean-Christophe Deschamps 
 wrote:
>
>At 23:49 24/11/2017, you wrote:
>
>>On 11/24/17 5:26 PM, Jean-Christophe Deschamps wrote:
>>
>>>At 22:38 24/11/2017, you wrote:
One proof of the falsehood of your assertion is that we CAN fill a 
database with some data using UIDs, and we will almost certainly not
>
get a collision, while you assertion we will.
>>
>>>This is an attempt at "proof by example". Keith is perfectly right 
>>>--mathematically speaking-- and your "proof" doesn't hold water, I 
>>>mean as a formal proof.  The best proof that your "proof" isn't a 
>>>proof is that you feel obliged  to add "almost certainly".
>>
>>DISproof by example is a perfectly valid method. If someone makes a 
>>claim that something is ALWAYS true, ONE counter example IS a 
>>disproof. I said almost certainly as the chance of a collision isn't 0
>
>>(to be able to say with certainty) but is most defintely less than the
>
>>100% claimed.
>
>You're confusing one mathematical theorem and one practical statement. 
>The first is the _mathematical_ fact that any PRNG (using any fixed 
>number of random bits, which is what xUIDs are) will provide an 
>infinite number of collisions with probability 1. This is definitely 
>true. Of course here, the number of samples is implicitely infinite.
>
>Your practical statement is that you can "most certainly" ignore the 
>possibility of collision when feeding 2^N xUIDs into a unique column 
>without loosing sleep. That's good enough in practice. The issue with 
>your "demonstration" is that 2^N is bounded, whatever finite N you 
>choose. Hence you don't contradict what Keith said, you just say 
>something different applying to restricted cases. You're speaking about
>
>practice, while Keith told about math. You're both right, each from his
>
>own point of view. But you can't claim to disproof a trivially true 
>theorem this way, by changing its premices.
>
>An event with probability 10^-10...000 (any finite number of 
>zeroes) will occur at least once, provided you run enough tries. It'll 
>occur an infinite number of times if you run an infinite number of 
>tries. Else its probability would be zero.
>Your "disproof" amounts to say that 10^-10...000 = 0
>
>And neither Keith nor I ever said that an xUID collision will occur 
>with probability 1 after 2^64 samples. That would be false and that's 
>why people feel free to use xUIDs _AND_ sleep quietly.
>
>JcD
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does journal_mode=DELETE writes uncommitted queries into DB?

[J. King]

Thanks for the thorough explanation, Rowan. I must say I learned something, 
myself!

On November 23, 2017 11:23:11 PM EST, Rowan Worth <row...@dug.com> wrote:
>On 24 November 2017 at 10:27, J. King <jk...@jkingweb.ca> wrote:
>
>> The rollback journal is used to return the database to its previous
>state
>> in the case of a partial write, not complete writes which are
>interrupted.
>> As you didn't commit the transaction, no write occurred, never mind a
>> partial one, so the database remained in its initial state.
>>
>> Deleting a rollback journal is a bad idea. In this case it was
>harmless,
>> but normally it's an excellent way to corrupt your database.
>>
>> As to where uncommitted transactions live, I am not sure. I'll let
>someone
>> else answer that.
>>
>
>When a database page is modified during a transaction, the initial data
>is
>written to the rollback journal and the modified data is held in memory
>until COMMIT or the transaction memory cache is exceeded (see PRAGMA
>cache_size).
>
>If either of those happen, sqlite obtains an EXCLUSIVE lock on the
>database
>and starts writing the modified data (after making sure the rollback
>journal is properly synced to disk). At this point you have a partially
>committed transaction and the DB is not necessarily consistent (which
>is
>why it's done under an exclusive lock, and why deleting the rollback
>journal is a terrible idea as you said).
>
>In the case of COMMIT this is just a matter of I/O (syncing the DB and
>deleting the rollback journal) and should be fairly quick. But in the
>case
>of a cache spill, this partially committed state can be observed for
>much
>longer, depending on the size of the transaction.
>
>-Rowan
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does journal_mode=DELETE writes uncommitted queries into DB?

[J. King]

The rollback journal is used to return the database to its previous state in 
the case of a partial write, not complete writes which are interrupted. As you 
didn't commit the transaction, no write occurred, never mind a partial one, so 
the database remained in its initial state. 

Deleting a rollback journal is a bad idea. In this case it was harmless, but 
normally it's an excellent way to corrupt your database. 

As to where uncommitted transactions live, I am not sure. I'll let someone else 
answer that. 

On November 23, 2017 9:13:20 PM EST, "林自均"  wrote:
>Hi folks,
>
>When I was learning about rollback journal, I did the following tests:
>
>(in shell 1)
>$ sqlite3 /tmp/db.sqlite
>SQLite version 3.21.0 2017-10-24 18:55:49
>Enter ".help" for usage hints.
>sqlite> PRAGMA journal_mode;
>delete
>sqlite> CREATE TABLE bank (name STR, money INT);
>sqlite> INSERT INTO bank VALUES ("john", 5566);
>sqlite> BEGIN;
>sqlite> UPDATE bank SET money = money + 100 WHERE name = "john";
>sqlite>
>
>(then in shell 2)
>$ kill -kill $(pidof sqlite3) # kills the sqlite3 process in shell 1
>$ rm -f /tmp/db.sqlite-journal
>$ sqlite3 /tmp/db.sqlite .dump
>PRAGMA foreign_keys=OFF;
>BEGIN TRANSACTION;
>CREATE TABLE bank (name STR, money INT);
>INSERT INTO bank VALUES('john',5566);
>COMMIT;
>
>I was expecting that deleting the rollback journal would commit the
>uncommitted transaction (i.e. increase money from 5566 to 5666).
>However,
>it didn't.
>
>I also noticed that the md5sum of db.sqlite are the same before the
>UPDATE
>query and after it, which means that the UPDATE query doesn't really
>write
>into db.sqlite. Does it only write into memory?
>
>Thanks for answering my questions.
>
>John Lin
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Web issue

[J. King]

It seems to be fixed now. It's working for me now in the same environment in 
which it was not working earlier. 

On November 23, 2017 4:54:56 PM EST, Simon Slavin  wrote:
>
>
>On 23 Nov 2017, at 9:14pm, R Smith  wrote:
>
>> In searching the sqlite.org pages for a previous post, I tried to
>search with this computed url:
>> 
>> http://www.sqlite.org/search?q=CTE
>> 
>> To which there was about 8 seconds wait time and then this response
>returned:
>
>Seems to work fine for me.  Can you try a different browser or device ?
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[J. King]

I greatly prefer e-mail, too. It's a shame mailing lists run afoul of SPF and 
usually DKIM, and doubly so that ARC is unlikely to be of much help. 

I abhor Discourse, so it's depressing for me that it's so popular. Alas...

On November 21, 2017 10:16:24 AM EST, Stephen Chrzanowski  
wrote:
>I love the email methodology, and I'd honestly be sad to see it go. 
>But if
>GMail is causing the mischaracterization of the mail, maybe just a note
>on
>the sqlite.org home page that directs people on how to whitelist the
>mailing list?
>
>I'm indifferent to the forum idea, but, so long the forum software will
>give me notifications of ALL entries, with the full content of the
>post.
>That way, I can decide if I want to jump on the forum and contribute,
>or
>ask.
>
>On Tue, Nov 21, 2017 at 9:52 AM, Dominique Devienne
>
>wrote:
>
>>
>> But many people still prefer email. I doubt something else would be
>as
>> convenient.
>>
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The IS operator

[J. King]

Thank you, Richard. After some digging I see, too, that MySQL uses <=> for that 
same functionality, Microsoft has a toggle for =, and Oracle nothing. I had 
clearly been mistaken about IS's portability. 

On October 6, 2017 7:53:27 PM EDT, Richard Hipp <d...@sqlite.org> wrote:
>On 10/6/17, J. King <jk...@jkingweb.ca> wrote:
>> Are there any other
>> reasons I might perhaps want to use = rather than IS that I'm not
>aware
>> of?
>
>"NULL IS NULL" is true, but "NULL = NULL" is not true (it is null).
>Other than that, the IS operator and the = operator are the same.
>
>"IS" in SQLite is the same as "IS NOT DISTINCT FROM" in PostgreSQL and
>"IS NOT" in SQLite is the ame as "IS DISTINCT FROM" in PostgreSQL.
>
>-- 
>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

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] The IS operator

[J. King]

I've been using the IS operator to the exclusion of = in my 
application's queries so that I'm not surprised by some input being null 
and giving unexpected results. My rationale is that if I'm consistent, 
my software will be as well (all else being equal, anyway...).


The IS operator is commonly seen in tutorials and the like as "x IS NOT 
NULL", and Richard's example earlier today of the unary NOT operator "a 
= NOT b" started me wondering if there are any performance or 
portability concerns with using IS rather than =. Are there any other 
reasons I might perhaps want to use = rather than IS that I'm not aware 
of?


Thanks. :)

--
J. King

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

Re: [sqlite] How to handle such situation

[J. King]

Perhaps I need to be more explicit:

If the transaction fails because of a constraint violation, then using either 
the ROLLBACK, IGNORE, or REPLACE conflict resolution strategies would commit or 
roll back the transaction and automatically close it. If you add conflict 
clauses to table schemata, it wouldn't even require changes to the logic of 
your application. 

On October 4, 2017 10:20:01 PM EDT, Igor Korot <ikoro...@gmail.com> wrote:
>Hi,
>
>On Wed, Oct 4, 2017 at 6:19 PM, J. King <jk...@jkingweb.ca> wrote:
>> See also:
>> <http://sqlite.org/lang_conflict.html>
>
>This is good to know but I don't think it is applicable in this case
>as I explicitly said
>that this is inside transaction which fails.
>
>Thank you.
>
>>
>> On October 4, 2017 6:15:55 PM EDT, Jens Alfke <j...@mooseyard.com>
>wrote:
>>>
>>>
>>>> On Oct 4, 2017, at 2:20 PM, Igor Korot <ikoro...@gmail.com> wrote:
>>>>
>>>> If I start transaction, all queries were successful, but issuing
>>>"COMMIT" fails.
>>>> On such failure I am going to present an error, but then what
>happens
>>>> if I go and try to close the connection?
>>>
>>>If COMMIT fails, you should execute a ROLLBACK to abort the
>>>transaction, then continue with failure handling. Otherwise yes, the
>>>transaction remains open and will cause trouble when you try to close
>>>the connection.
>>>
>>>—Jens
>>>
>>>___
>>>sqlite-users mailing list
>>>sqlite-users@mailinglists.sqlite.org
>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> --
>> Sent from my Android device with K-9 Mail. Please excuse my brevity.
>> ___
>> 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

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to handle such situation

[J. King]

See also:


On October 4, 2017 6:15:55 PM EDT, Jens Alfke  wrote:
>
>
>> On Oct 4, 2017, at 2:20 PM, Igor Korot  wrote:
>> 
>> If I start transaction, all queries were successful, but issuing
>"COMMIT" fails.
>> On such failure I am going to present an error, but then what happens
>> if I go and try to close the connection?
>
>If COMMIT fails, you should execute a ROLLBACK to abort the
>transaction, then continue with failure handling. Otherwise yes, the
>transaction remains open and will cause trouble when you try to close
>the connection.
>
>—Jens
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data types for date and time functions

[J. King]

On 2017-09-25 08:19:52, "R Smith" <rsm...@rsweb.co.za> wrote:


On 2017/09/25 10:12 AM, David Wellman wrote:

C - Storing a string with a date or date and time, typically the 
standard form is ISO8601 which looks like '-MM-DDTHH:NN:SS.MSS 
+ZZ:ZZ' with the T optionally being a space and the + could be + or - 
to indicate offset (Zulu time) etc. Some parts are optional, there is a 
good argument to not store Zulu time (offsets) but revert to UTC and 
let the displaying app format the time to whatever time-frame the user 
expects (much like Unix timestamps), so most actual stored dates end up 
as simply '-MM-DD HH:NN:SS' or even just '-MM-DD' if not 
interested in the time.


It's worth noting that the CURRENT_TIMESTAMP keyword uses "-MM-DD 
HH:MM:SS" format, and implied UTC.


--
J. King


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

Re: [sqlite] How does SQLite handle \r and \n in values?

[J. King]

More details would help. How are you inserting the data? Are you using a bound 
parameter (you should be), or quoted text? Are you using something other than 
the C interface to interact with the database? Which error did you receive? Can 
you provide a minimal example of the code you used?

On September 21, 2017 9:56:29 AM EDT, heribert  wrote:
>Hello,
>
>i attempt to make a INSERT with a TEXT field containing a string with
>\r 
>and \n characters, but it fails (Strings without \r and \n characters 
>are inserted correct).
>The sqlite3_exec results with error... i think the reason are the 
>special characters.
>What is the correct way to store strings containing \r and \n in a TEXT
>
>field?
>
>Thx for any hint
>heribert
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

[J. King]

Doh. I meant the linked document on the error log. Silly me. 

<http://sqlite.org/errlog.html>

On September 11, 2017 9:41:39 PM EDT, "J. King" <jk...@jkingweb.ca> wrote:
>There's an extra word in the first paragraph of Section 4 of that
>document, by the way:
>
>" The error logger callback has also proven useful in catching errors
>occasional errors that the application misses..."
>
>On September 11, 2017 11:22:50 AM EDT, Dan Kennedy
><danielk1...@gmail.com> wrote:
>>On 09/10/2017 08:30 PM, R Smith wrote:
>>> Well yes but the documentation suggests that one could expect a
>>slight 
>>> degradation. The words "works best with" does not seem to imbue an 
>>> idea of "give WITHOUT ROWID tables a wide berth when your tables are
>
>>> more than few columns wide", and I don't think the Devs intended
>that
>>
>>> either.
>>>
>>> I can still roll with the idea that the WITHOUT ROWID tables with
>151
>>
>>> columns and lots of data actually performed FASTER than the same
>>ROWID 
>>> tables for the other queries but slower with the JOIN... but that
>>much 
>>> slower?
>>
>>
>>
>>The difference seems to be that, currently, SQLite never creates 
>>automatic indexes on WITHOUT ROWID tables.
>>
>>   http://sqlite.org/optoverview.html#autoindex
>>
>>I don't think there is a good reason that it cannot do so. It just 
>>doesn't yet.
>>
>>Dan.
>>
>>
>>
>>
>>
>>>
>>> I mean we are not talking a "little" slower, we are talking 50ms vs.
>
>>> ~70,000ms, that's a difference of a whopping 3 orders of magnitude
>>and 
>>> change. And we are not talking huge tables, another query that
>simply
>>
>>> walks the tables can do so several million times in the same time
>the
>>
>>> JOIN query does.
>>>
>>> Put another way, I can create 100 new tables and populate them each 
>>> with the rows from one test table, then delete the rows from each
>new
>>
>>> table that doesn't satisfy an EXISTS() check in the other test table
>
>>> (essentially mimicking the JOIN query) and then output each full new
>
>>> table, 100 of them in turn, and then DROP them all. SQlite can do
>ALL
>>
>>> of that in a fraction of the time that the normal JOIN query
>(between
>>
>>> those same two test tables) takes to complete.
>>>
>>> We are talking a formula 1 car suddenly going at max speed of 1 
>>> mile-per-weekend, barely keeping up with a semi-athletic snail, and 
>>> people suggest checking the fuel octane rating. I'm saying there is 
>>> something wrong under the hood.
>>>
>>> Cheers,
>>> Ryan
>>>
>>>
>>> On 2017/09/10 11:28 AM, Clemens Ladisch wrote:
>>>> R Smith wrote:
>>>>> I am using 151 columns for both tests. The only thing that changes
>>>>> between the two scripts are the words "WITHOUT ROWID" being added
>>>> <http://www.sqlite.org/withoutrowid.html> says:
>>>> | WITHOUT ROWID tables will work correctly ... for tables with a
>>single
>>>> | INTEGER PRIMARY KEY. However, ordinary rowid tables will run
>>faster in
>>>> | that case.
>>>> and:
>>>> | WITHOUT ROWID tables work best when individual rows are not too
>>large.
>>>>
>>>> So this is pretty much the documented worst case for WITHOUT ROWID 
>>>> tables.
>>>>
>>>> If the query is executed by making a copy of all the table data
>into
>>>> a temporary B-tree (index), which is what INTERSECT and probably
>>IN(...)
>>>> are doing, then the WITHOUT-ROWID-ness of the table does not
>matter.
>>>>
>>>>
>>>> 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-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>-- 
>Sent from my Android device with K-9 Mail. Please excuse my brevity.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

[J. King]

There's an extra word in the first paragraph of Section 4 of that document, by 
the way:

" The error logger callback has also proven useful in catching errors 
occasional errors that the application misses..."

On September 11, 2017 11:22:50 AM EDT, Dan Kennedy  
wrote:
>On 09/10/2017 08:30 PM, R Smith wrote:
>> Well yes but the documentation suggests that one could expect a
>slight 
>> degradation. The words "works best with" does not seem to imbue an 
>> idea of "give WITHOUT ROWID tables a wide berth when your tables are 
>> more than few columns wide", and I don't think the Devs intended that
>
>> either.
>>
>> I can still roll with the idea that the WITHOUT ROWID tables with 151
>
>> columns and lots of data actually performed FASTER than the same
>ROWID 
>> tables for the other queries but slower with the JOIN... but that
>much 
>> slower?
>
>
>
>The difference seems to be that, currently, SQLite never creates 
>automatic indexes on WITHOUT ROWID tables.
>
>   http://sqlite.org/optoverview.html#autoindex
>
>I don't think there is a good reason that it cannot do so. It just 
>doesn't yet.
>
>Dan.
>
>
>
>
>
>>
>> I mean we are not talking a "little" slower, we are talking 50ms vs. 
>> ~70,000ms, that's a difference of a whopping 3 orders of magnitude
>and 
>> change. And we are not talking huge tables, another query that simply
>
>> walks the tables can do so several million times in the same time the
>
>> JOIN query does.
>>
>> Put another way, I can create 100 new tables and populate them each 
>> with the rows from one test table, then delete the rows from each new
>
>> table that doesn't satisfy an EXISTS() check in the other test table 
>> (essentially mimicking the JOIN query) and then output each full new 
>> table, 100 of them in turn, and then DROP them all. SQlite can do ALL
>
>> of that in a fraction of the time that the normal JOIN query (between
>
>> those same two test tables) takes to complete.
>>
>> We are talking a formula 1 car suddenly going at max speed of 1 
>> mile-per-weekend, barely keeping up with a semi-athletic snail, and 
>> people suggest checking the fuel octane rating. I'm saying there is 
>> something wrong under the hood.
>>
>> Cheers,
>> Ryan
>>
>>
>> On 2017/09/10 11:28 AM, Clemens Ladisch wrote:
>>> R Smith wrote:
 I am using 151 columns for both tests. The only thing that changes
 between the two scripts are the words "WITHOUT ROWID" being added
>>>  says:
>>> | WITHOUT ROWID tables will work correctly ... for tables with a
>single
>>> | INTEGER PRIMARY KEY. However, ordinary rowid tables will run
>faster in
>>> | that case.
>>> and:
>>> | WITHOUT ROWID tables work best when individual rows are not too
>large.
>>>
>>> So this is pretty much the documented worst case for WITHOUT ROWID 
>>> tables.
>>>
>>> If the query is executed by making a copy of all the table data into
>>> a temporary B-tree (index), which is what INTERSECT and probably
>IN(...)
>>> are doing, then the WITHOUT-ROWID-ness of the table does not matter.
>>>
>>>
>>> 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-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is the exact syntax for SELECT MAX in PHP?

[J. King]

Simon, why would you recommend not using PDO? Does the SQLite3 extension 
perform better, or does it boil down to personal preference?

On August 5, 2017 1:35:43 PM EDT, Simon Slavin  wrote:
>
>
>On 5 Aug 2017, at 10:53am, Edmondo Borasio 
>wrote:
>
>> Hey, do you know where I can find good instructions on how to use
>SQLITE in
>> PHP?
>
>I used to do that for a living.
>
>First, use the sqlite3 module.  Don’t use the PDO module which can
>access many types of database.  So you should be doing "new SQLite3()"
>like this:
>
>   $db = new SQLite3('mysqlitedb.db');
>   $results = $db->query('SELECT bar FROM foo');
>   while ($row = $results->fetchArray()) {
>   var_dump($row);
>   }
>
>Second, I have not found any better examples than you find in the
>documentation
>
>
>
>which is rather annoying since there aren’t many examples of some
>calls.
>
>Third, I recommend you try to do object-oriented programming in PHP
>where possible, and the sqlite3 module works well with that.  So
>although the call provided is
>
>   $row = $results->fetchArray()
>
>I frequently do
>
>   $row = (object) $results->fetchArray()
>
>and the var_dump line in the above example will show you how this
>changes what you get.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Cut off paragraph in pointer documentation

[J. King]

The fifth paragraph of 
<https://sqlite.org/bindptr.html#destructor_functions> ends after two 
words, "Note that".


--
J. King

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

Re: [sqlite] FOREING KEY constraint

[J. King]

The sqlite_master table should have this information. 

SELECT count() FROM sqlite_master WHERE name IS your_constraint_name AND 
tbl_name IS your_table_name;

On July 3, 2017 9:37:04 AM EDT, Igor Korot  wrote:
>Hi, Keith et al,
>
>On Mon, Jul 3, 2017 at 7:13 AM, Keith Medcalf 
>wrote:
>>
>> From what I can tell the answer is (A).  The constraint_name is
>simply a comment to be reported (if possible) when the constraint is
>violated.
>
>So is it possible to check that the foreign key with the given name
>already present for a given
>table?
>
>Thank you.
>
>>
>> --
>> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>>
>>> -Original Message-
>>> From: sqlite-users
>[mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>>> On Behalf Of Simon Slavin
>>> Sent: Monday, 3 July, 2017 04:49
>>> To: SQLite mailing list
>>> Subject: Re: [sqlite] FOREING KEY constraint
>>>
>>>
>>>
>>> On 3 Jul 2017, at 4:37am, Keith Medcalf  wrote:
>>>
>>> > What do you mean "check for uniqueness?
>>>
>>> If you give two constraints the same name, does SQLite
>>>
>>> A) Ignore the problem
>>> B) Reject the second one complaining "duplicate name"
>>> C) Replace the first one with the second
>>>
>>> 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
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to search for fields with accents in UTF-8 data?

[J. King]

Indeed. Technically-minded Windows users do exist (Hi, Microsoft, I'm right 
here!), and I have neither the time nor the inclination to learn PowerShell 
when the Windows terminal is already adequate---with a set of ports of GNU 
tools, anyway. :)

On June 20, 2017 9:24:12 AM EDT, R Smith  wrote:
>
>On 2017/06/20 2:34 PM, Gilles wrote:
>>
>> Lessons I learned:
>>
>> 1. In CSV files, double-check how data are encoded
>>
>> 2. Do not use the sqlite3.exe CLI if the data use anything more than 
>> the basic latin alphabet. Instead, use a GUI application (eg. for 
>> Windows, SQLite Studio, SQLitespeed, etc.)
>
>Every lesson is valuable!  Just to be clear - there is nothing wrong 
>with using the CLI. When pointing it to a file that is correctly
>encoded 
>the import must work correctly (if not, it's a bug) - It's just 
>difficult to enter weird and wonderful Unicode characters outside the 
>BMP basic Latin plane (the first 127 code-points) via the console, or
>do 
>queries using them, all because the Windows console specifically is not
>
>Unicode-enabled.
>
>As an aside - I never understood the reasons for that. I get that 
>Windows has a less "techy" clientèle than Linux for instance, and that 
>the backwards compatibility is paramount, and that no console command 
>ever need fall outside the 7-bit ANSI range of characters... but geez, 
>how much effort can it be to make it Unicode-friendly? It's not like
>the 
>Windows API lacks any Unicode functionality - even Notepad can handle
>it 
>masterfully.
>
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

[J. King]

On May 30, 2017 10:07:45 AM EDT, Thomas Flemming <t...@qvgps.com> wrote:

>Style.Id doesn't need to be LONG, you're right. I changed it but it
>doesn't 
>make a difference.
>Pois.Id need to be LONG because the source for this column is really 
>containing 64-bit values 
Integers in SQLite are of variable size; if an 8-byte size is required, it will 
scale accordingly. Specifying LONG will not yield an integer affinity like 
specifying INTEGER would, and for primary keys is not as efficient because the 
column is not an alias for rowid if you specify LONG. 

In short, you should always use INTEGER PRIMARY KEY if the column is whole 
numbers, regardless of size. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding Sqlite DB Index

[J. King]

On May 24, 2017 11:33:13 AM EDT, Manoj Sengottuvel <smanoj...@gmail.com> wrote:
>Hi Richard,
>
>If I create new table (table name : ACCOUNT_MSTR) then the auto index
>is
>created as sqlite_autoindex_ACCOUNT_MSTR_1.
>
>Then I am trying to execute the following query  ' select * from
>sqlite_master where type='index';'
>
>I got the following result
>
>
>type
>
>name
>
>tbl_name
>
>root_page
>
>Sql
>
>index
>
>sqlite_autoindex_ACCOUNT_MSTR_1
>
>ACCOUNT_MSTR
>
>3
>
>NULL
>
>When I am seeing in the result sql column is NULL.
>
>Why the sql column shows NULL value?
>
>Do I need to create the index for the primary key?
>
>But  the same case if I create my own index for the table (table name :
>ACCOUNT_MSTR)  then I can able to see the  index creation script in sql
>column .
>
>I don't understand how the auto index is working?
>
>
>
>Regards,
>
>Manoj.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

The "sql" field contains the statement executed to create the 
table/index/trigger/etc. Automatic indexes are a side-effect of table creation, 
and so have no associated statements. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auntondex with unique and integer primary key

[J. King]

On May 19, 2017 1:21:49 PM EDT, Paul Sanderson <sandersonforens...@gmail.com> 
wrote:
>Is the autoindex associated when using unique with an integer primary
>key
>definition redundant?
>
>I have seen a number of DBs/tables created in the following form:
>
>Create table test(id integer unique primary key);
>
>Insert into test values (1);
>
>Insert into test values (2);
>
>Insert into test values (3);
>
>The table is created and populated as expected, but an
>sqlite_autoindex_test_1 is also created with content that mirrors
>exactly
>the rowid/id.
>
>Is the autoindex redundant and is this an opportunity for optimisation?
>
>
>Using 3.18.0
>
>Paul
>www.sandersonforensics.com
>skype: r3scue193
>twitter: @sandersonforens
>Tel +44 (0)1326 572786
>http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
>-Forensic Toolkit for SQLite
>email from a work address for a fully functional demo licence
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

A primary key is by definition unique, so the unique constraint is needless 
duplication. Note, too, that the primary key is only an alias for the rowid 
when using the formulation "integer primary key" exactly. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Generalized SQLite stored procedure style pivot table exhibit.

[J. King]

On May 16, 2017 7:32:41 PM EDT, "E.Pasma" <pasm...@concepts.nl> wrote:
>15 mei 2017, 07:34 petern:
>
>> Here I revisit the pivot table problem using the SQLite stored  
>> procedure
>> pattern and pure unmodified SQLite.  I am seeking feedback for  
>> improving
>> the brevity of the stored procedure presented here.
>
>Hi, initially I got: near "eval": syntax error. This is after saving  
>the mail message as a text file and taking that in the editor two  
>become an sql script. This apparently leaves some two-byte spaces,  
>causing this humanly invisable error.
>How to fix this in VI I don't know.
>Downloading the text by simple copy and paste cures fixed the problem.
>The procedure works great and can easily be customized.
>I'd generate a temporary view as one must not be tempted to keep it  
>(and miss new fruits).
>Thanks, E. Pasma
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

The eval function is provided by an extension:

<http://www.sqlite.org/cgi/src/artifact/f971962e92ebb8b0>
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Security vulnerabilities prevalent in web tutorials for PHP, javascript, etc..

[J. King]

On April 23, 2017 4:31:42 PM EDT, Simon Slavin <slav...@bigfraud.org> wrote:
>There’s been almost no traffic on this list this weekend so I don’t
>feel too bad posting something that’s not specifically about SQLite. 
>But a lot of us use SQLite as a back end for web-facing databases,
>called from PHP, and this is about PHP tutorials found on the web.
>
>ObAcronym: "SQLi" is short for "SQL injection".
>
><https://www.helpnetsecurity.com/2017/04/21/programming-tutorials-vulnerabilities/>
>
>“Thanks to our framework, we have uncovered over 100 vulnerabilities in
>web application code that bear a strong resemblance to vulnerable code
>patterns found in popular tutorials. More alarmingly, we have confirmed
>that 8 instances of a SQLi vulnerability present in different web
>applications are an outcome of code copied from a single vulnerable
>tutorial,” they noted. “Our results indicate that there is a
>substantial, if not causal, link between insecure tutorials and web
>application vulnerabilities.”
>
>Moral: Web tutorials are for teaching you how a computer language
>works.  Don’t copy-and-paste them into production code without thinking
>through the consequences.  If you don’t understand what you’re doing,
>hire an experienced programmer.  That’s what they’re for.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

How depressing, that people still fail to learn the lessons of SQL injection 
because others fail to teach them. 

I'm currently writing a PHP application which uses SQLite, and I am of course 
paranoid about using prepared statements; I'd love it if we could all get the 
basics right so that articles warning about less straightforward problems would 
stand out more. 

Hopefully that article will reach some of those who need it. Thanks, Simon. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Typo in documentation

[J. King]

The first paragraph in Section 2 of <http://sqlite.org/compile.html> states in 
part:

 ... And the SQLITE_OMIT_PROGESS_CALLBACK option is only usable by 
applications...

Note the missing R in PROGRESS.
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Errors opening WAL-based databases with SQLITE_OPEN_READONLY

[J. King]

On April 10, 2017 5:14:23 PM EDT, Jens Alfke <j...@mooseyard.com> wrote:
>I'm aware that a database in WAL mode can't be opened read-only if its
>directory isn't writeable[1]. However, I'm unexpectedly getting errors
>opening a database when the directory _is_ writeable, but the database
>is opened read-only. Specifically:
>
>* The database file is in WAL mode.
>* The -wal and -shm files do not exist (i.e. the database was
>previously closed cleanly.)
>* The directory containing the database is writeable (i.e. the process
>is allowed to create files in it.)
>* The database is opened with sqlite3_open_v2, using the
>SQLITE_OPEN_READONLY flag.
>
>In this situation, any SQLite call that actually accesses the database
>will fail with SQLITE_CANTOPEN.
>
>It seems as though SQLite decides that because the handle is read-only,
>it's not allowed to create the -shm file. There's some logic to this,
>but I can't find any mention of it in the documentation.
>
>(I'm using SQLite 3.16 on macOS 10.12.4.)
>
>—Jens
>
>[1]: https://www.sqlite.org/wal.html#readonly
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

WAL databases must be opened read/write because readers need to write to the 
shm file. It is in the documention on WAL mode:

It is not possible to open read-only WAL databases. The opening process must 
have write privileges for "-shm" wal-index shared memory file associated with 
the database, if that file exists, or else write access on the directory 
containing the database file if the "-shm" file does not exist. 
-- 
J. King
___
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

[J. King]

On March 13, 2017 4:15:57 PM EDT, Rob Richardson <rdrichard...@rad-con.com> 
wrote:
>To answer my own question:  this works:
>
>using (SQLiteCommand command = m_conn.CreateCommand())
>{
>command.CommandType = CommandType.Text;
>command.CommandText = "INSERT INTO trend_data (tag_key, value,
>value_timestamp) VALUES (?, ?, ?)";
>SQLiteParameter param;
>param = new SQLiteParameter();
>param.Value = 2;
>command.Parameters.Add(param);
>param = new SQLiteParameter();
>param.Value = 234.56;
>command.Parameters.Add(param);
>param = new SQLiteParameter();
>param.Value = DateTime.Now;
>command.Parameters.Add(param);
>rowsAffected = command.ExecuteNonQuery();
>}
>
>RobR
>
>-Original Message-
>From: sqlite-users
>[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Rob
>Richardson
>Sent: Monday, March 13, 2017 2:23 PM
>To: General Discussion of SQLite Database
>(sqlite-users@mailinglists.sqlite.org)
>Subject: [sqlite] How to use parameterized queries in SQLite.Net
>
>Hello again.
>
>Since my attempt to find the official answer for myself has hit a snag,
>I'll just ask here.
>
>The examples I've seen for parameterized queries used with the
>SQLiteCommand class have shown named parameters, and the names usually
>begin with an "@" character.  Is that character required for named
>parameters?  Is that the correct leading character?  Is it required to
>include that leading character in the name given to the SQLiteParameter
>object?
>
>I'm used to using the System.Data.ODBC classes, which do not support
>named parameters, but they do support unnamed parameters, represented
>by question marks.  The order in which the parameters are attached to
>the command object determines the association between the parameter
>object and the query parameter.  Unnamed parameters would be easier for
>me to work with than named ones.  Does SQlite.Net support unnamed
>parameters?
>
>Thank you.
>
>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

In case it's useful, see here for all your options:
<http://sqlite.org/lang_expr.html#varparam>
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Producing SQLITE_MISMATCH

[J. King]

On 2017-03-09 09:59:25, "Dan Kennedy" <danielk1...@gmail.com> wrote:


On 03/09/2017 09:39 PM, J. King wrote:
Out of curiosity, can you provide some insight as to why it does not 
produce a mismatch for a WITHOUT ROWID table?


The main b-tree in which data for a "rowid table" - any table that is 
not a WITHOUT ROWID table or a virtual table - is stored uses 64-bit 
integers for keys. If you declare a column "INTEGER PRIMARY KEY", it 
becomes an alias for this underlying integer key. So in this case the 
field must be set to an integer value - SQLite could not store it 
otherwise.


With a WITHOUT ROWID table, the main b-tree used to store data is more 
flexible - it can have any type of key. So in this case there is no 
restriction.


https://www.sqlite.org/fileformat.html#representation_of_sql_tables

and:

  https://www.sqlite.org/rowidtable.html
  https://www.sqlite.org/withoutrowid.html


Again, thank you.  I was completely misreading the documentation with 
regard to WITHOUT ROWID. Now a) it makes much more sense and b) I 
understand when it might actually come in handy.


--
J. King

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

Re: [sqlite] Producing SQLITE_MISMATCH

[J. King]

On 2017-03-09 09:35:00, "Dan Kennedy" <danielk1...@gmail.com> wrote:


On 03/09/2017 09:23 PM, J. King wrote:
I'm trying to write a minimal test case to produce an SQLITE_MISMATCH 
response.  The documentation suggests the following should be 
sufficient:


CREATE TABLE test(id integer primary key) WITHOUT ROWID;
INSERT INTO test(id) VALUES('ook');

However, SQLite happily accepts the second statement. Is there some 
trick to it that's not obvious?


Leave out the WITHOUT ROWID.

Dan.


Ah, exactly what I needed.  Thanks!

Out of curiosity, can you provide some insight as to why it does not 
produce a mismatch for a WITHOUT ROWID table?


(apologies if my messages come out looking yucky; I'm still looking a 
mail client that isn't awful dealing with mailing lists. :( )


--
J. King

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

[sqlite] Producing SQLITE_MISMATCH

[J. King]

I'm trying to write a minimal test case to produce an SQLITE_MISMATCH 
response.  The documentation suggests the following should be 
sufficient:


CREATE TABLE test(id integer primary key) WITHOUT ROWID;
INSERT INTO test(id) VALUES('ook');

However, SQLite happily accepts the second statement. Is there some 
trick to it that's not obvious?


--
J. King

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

Re: [sqlite] [PHP5-FPM] Sqlite3 or pdo_sqlite?

[J. King]

On Sat, 24 Jul 2010 08:33:21 -0400, Simon Slavin <slav...@bigfraud.org>  
wrote:

>
> On 24 Jul 2010, at 9:56am, Gilles Ganault wrote:
>
>> So from the above, it looks like this binary supports access to
>> MySQL(i) and SQLite2/3, in both procedural and (PDO) object-oriented
>> modes.
>>
>> If that's correct, and provided the application doesn't need to be
>> DB-agnostic... why should I choose PDO instead of the procedural
>> functions to SQLite3?
>
> The main advantage of the PDO is that the calls for each SQL engine are  
> identical.  In other words, you can write your code as if you're going  
> to use SQLite, then one day find you have to move to MySQL, and you'll  
> only have to change one line of code -- the one that says which engine  
> you want it to use.  If you're in a profession where you have to use  
> many SQL engines this can save you from having to learn the different  
> rules and foibles of each one.

This is mostly fantasy, as SQL engines have widely diverging language  
dialects; the API is a relatively small component of interoperability  
pain.  Still, it is a concern, and certainly a slight advantage to PDO.

PDO_sqlite3 also does have the advantage of being available by default  
since PHP 5.0.0, whereas sqlite3 is only available by default since PHP  
5.3.0.  I'm aware of no other advantages to using PDO, and from what I've  
read it's on the slow side.

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

Re: [sqlite] Accessing SQLite from PHP5?

[J. King]

On Tue, 18 May 2010 09:49:49 -0400, Gilles Ganault  
<gilles.gana...@free.fr> wrote:

> On Tue, 18 May 2010 14:30:06 +0100 (BST), Swithun Crowe
> <swit...@swithun.servebeer.com> wrote:
>> If you use PDO, then you get access to Sqlite 3.x databases:
>>
>> http://www.php.net/manual/en/ref.pdo-sqlite.php
>
> Thanks for the input. After following the following article, I
> successfully installed Lighttpd in FastCGI mode + PHP5 and PDO to
> access SQLite:

FYI, PHP 5.3 provides an SQLite3 module <http://php.net/sqlite3> which  
offers a version of SQLite more up-to-date than that exposed by PDO.   
Whereas PDO is an abstracted interface, though, SQLite3 appears to be a  
slightly more direct mapping of the SQLite API, exposing more  
SQLite-specific features.

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

Re: [sqlite] sqlite, php, and Mac OS X 10.6.1

[J. King]

On Fri, 11 Dec 2009 09:13:33 -0500, Simon Slavin <slav...@bigfraud.org>  
wrote:

> Run the PHP INFO routine:
>
> phpinfo();
>
> and search for the word 'sqlite' in it.  You should fine it listed in  
> the PDO section.  You should also see the following sections:  
> 'pdo_sqlite', 'SQLite' and 'sqlite3'.  If any of those are missing you  
> do not have sqlite enabled properly, possibly because of modules,  
> possibly because of compilation options.

Not exactly true: 'pdo_sqlite' and 'sqlite3' provide two independent  
interfaces to SQLite 3, and 'sqlite' provides an interface to SQLite 2.   
If you're missing -all- of them (or, realistically, only have 'sqlite'),  
then you're in trouble, but any of them missing could simply be a  
configuration choice.

> Alternatively, use the native sqlite commands instead of the PDO driver:
>
> http://php.net/manual/en/book.sqlite.php

For clarity, that's the old SQLite 2 interface---which I suspect you  
didn't intend to recommend, Simon.  The modern non-PDO SQLite 3 interface  
is documented here:

<http://www.php.net/manual/en/book.sqlite3.php>

It is, however, only available by default since PHP 5.3.0.

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

Re: [sqlite] Implementing Regular Expression Support...?

[J. King]

On Tue, 08 Dec 2009 12:11:13 -0500, li...@mgreg.com <li...@mgreg.com>  
wrote:

> Hi All,
>
> I'm currently using SQLITE in a few production apps.  I'm using various  
> languages such as Ruby, PERL, RB, etc.  I have need to use regular  
> expressions in some of my queries, but I'm not sure how to implement  
> "user defined functionality".  Where are the hooks?  Is there a  
> particular mechanism/language I must use to create them?  Is this  
> something I'm required to recompile SQLITE for?

You do so by defining a user function called 'regexp'.  The means by which  
one defines a user function depends on the language.  See, for instance,  
[1] for Ruby.  For a 'regexp' function you would specify two arguments,  
pattern and string to match against.

[1]  
<http://sqlite-ruby.rubyforge.org/sqlite3/classes/SQLite3/Database.html#M000115>

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