Re: [sqlite] CSV import using CLI (header, NULL)

[Shawn Wagner]

To import a csv file with headers into an existing table, you can use

.import '| tail -n +2 yourfile.csv' yourtable

to skip the header line.

On Sat, Feb 29, 2020, 4:30 AM Christof Böckler  wrote:

> Hi,
>
> I want to share some thoughts and make some suggestions about the SQLite
> 3 command line interface (CLI) tool, especially its behaviour when
> importing CSV files.
>
> CSV files are probably even more common for data exchange than SQLite
> database files. I consider it to be good practice to include a header
> line with column names in every CSV file. Metadata should go with the
> data. This prevents me from mixing up two columns that contain numbers
> with similiar value distributions.
>
> Let’s look at an example. A file named data.csv contains three lines:
>
> id,val1,val2
> A,27,8
> B,3,12
>
> Now …
> sqlite3
> sqlite> .import data.csv tab
>
> works and looks good at first, but there is a problem with numerical data.
>
> sqlite> SELECT * FROM tab ORDER BY val2;
> B,3,12
> A,27,8
>
> This is because on import all three columns were created with affinity
> (not to say data type) TEXT (see .schema). As a consequence all numbers
> were imported as strings.
> '12' < '8' is lexicographically OK, but not so in a mathematical sense.
> Having the CSV file in mind I clearly expect to see 8 on the first line
> of the above result.
>
> How to work around this? Just define the table in advance with
> appropriate data types (affinity INTEGER):
>
> sqlite> CREATE TABLE tab (id TEXT, val1 INT, val2 INT);
>
> But now the above .import command will not work as expected, because it
> will result in three rows in our table tab. The first row contains the
> header line.
>
> Two different workarounds come to my mind:
> a) sqlite> DELETE FROM tab WHERE rowid = 1;  -- Dirty hack!
> b) sqlite> .import data.csv temp_tab
> sqlite> INSERT INTO tab SELECT * FROM temp_tab;
> sqlite> DROP TABLE temp_tab;  -- Two tedious extra lines
>
> Both approaches are not very appealing to me. To make CSV files with a
> header line first class citizens, I suggest this instead:
> sqlite> .import -h data.csv tab
> should ignore the first line of data.csv. This import should fail if
> table tab does not already exist.
> This is both shorter and more elegant than both workarounds.
>
>
> Now on to a second issue. Let’s assume you have sucessfully imported a
> file containing these four lines:
> id,val1,val2
> A,27,8
> B,3,12
> C,,1
> into the table tab mentioned above, resulting in three rows. Notice the
> missing value in column val1 on the last line. This missing value is
> imported as an empty string '' regardlesse of the affinity of column val1.
>
> That leads to (mathematically) unexpected results from aggregate functions:
> sqlite> SELECT SUM(val1) FROM tab;
> 30-- Well, treating '' like 0 is OK in this case
> sqlite> SELECT COUNT(val1) FROM tab;
> 3 -- but not so here; only two values/numbers were given in data.csv
> sqlite> SELECT AVG(val1) FROM tab;
> 10-- the average of 3 and 27 is 15
> sqlite> SELECT MAX(val1) FROM tab;
> ''-- not to be expected when looking at data.csv
>
> OK, I hear you say, what about this workaround:
> sqlite> UPDATE tab SET val1 = NULL WHERE val1 = '';
> This makes the surprises above go away, but it is again tedious to do for
> all columns containing only numbers.
>
> My suggestion is: If someone goes the extra mile and defines a table in
> advance in order to have an appropriate numerical affinity (INTEGER, REAL
> or NUMERIC) for a column, then it is OK to encode a missing value as NULL
> instead of ''. It seems right though to keep the current behaviour for
> columns with affinity TEXT (the default) or BLOB.
>
> To sum things up:
> 1. There should be no penalty for using header lines in CSV files. Thus a
> new flag -h for .import is much appreciated.
> 2. Missing values in columns with numerical affinity should show up as
> NULL values.
>
> Thanks for reading, I look forward to your opinions about these issues.
>
> Greetings
> Christof
> ___
> 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] Cannot export 'sqlite3' file to CSV

[Shawn Wagner]

Use the standard sqlite3 shell program:
https://www.sqlite.org/cli.html#csv_export

On Thu, Feb 27, 2020, 1:48 AM Rachael Courts 
wrote:

> Hi All,
>
> I'm a couple of months into my PhD, studying bioacoustics. I am using
> SQLiteStudio to open '.sqlite3' files, which I need to convert to CSV files
> to import into MATLAB for further analyses.
>
> Currently I have attempted the data export function, selecting which
> database and table I would like to export, however no option for 'CSV'
> exportation is available. The only options that appear are 'JSON, SQL,
> HTML, and PDF' which can't be read into excel.
>
> I have also attempted the queries tab in SQLiteStudio, in an attempt to
> write code for exportation. This did not work.
>
> I also attempted opening the command window on my computer, to change the
> '.sqlite3' file to 'CSV' but it seems that the '.sqlite3' file doesn't have
> a '.db' attached, and therefore will not open in the command window for
> editing.
>
> I'm seeking ANY advice on how to export these files into CSV, so they can
> be read in excel, and easily imported into MATLAB.
>
> I've attached an example file, if this helps.
>
> I hope to hear from someone soon,
> Rachael
>
>
> Sent from Outlook
> ___
> 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] Compiling on AIX OS

[Shawn Wagner]

I'm pretty sure the full build scripts require tcl 8.5 or newer. Maybe try
the -amalgamation or -autoconf versions?

On Wed, Feb 26, 2020 at 1:21 AM Domingo Alvarez Duarte 
wrote:

> Hello !
>
> 
>
> $ uname -a
> AIX minimal 1 7 00C63E504B00
> $ tclsh
> % puts $tcl_patchLevel
> 8.4.7
> %
> ====
>
> On 26/2/20 10:19, Shawn Wagner wrote:
> > puts $tcl_patchLevel
> ___
> 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] Compiling on AIX OS

[Shawn Wagner]

What version of tcl do you have installed?

(At the tclsh prompt, do a `puts $tcl_patchLevel`)

On Wed, Feb 26, 2020 at 1:05 AM Domingo Alvarez Duarte 
wrote:

> Hello !
>
> Trying to compile https://sqlite.org/2020/sqlite-src-3310100.zip on AIX
> 7.1 operating system for exercise and to know the peculiarities of
> building software on this OS and I'm getting this error, somehow tclsh
> that is installed on this OS do not wrap/hide OS incompatibilities.
>
> 
>
> tclsh /home/mingo/dev/sqlite-src-3310100/tool/mkshellc.tcl >shell.c
> illegal access mode "rb"
>  while executing
> "open $topdir/src/shell.c.in rb"
>  invoked from within
> "set in [open $topdir/src/shell.c.in rb]"
>  (file "/home/mingo/dev/sqlite-src-3310100/tool/mkshellc.tcl" line 32)
> gmake: *** [Makefile:1079: shell.c] Error 1
>
> 
>
> Cheers !
>
> ___
> 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] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output

[Shawn Wagner]

The mailing list strips attachments, btw.

Anyways, looking at that, yeah, they're all text values:

sqlite> SELECT bookIsbn, typeof(bookIsbn) FROM books WHERE bookAuthor LIKE
'%Ambrose%';
bookIsbntypeof(bookIsbn)
--  
0486280381  text
9781496030  text
9781496030  text

There are other problems with the database too:

sqlite> PRAGMA integrity_check;
integrity_check
---
row 649 missing from index sqlite_autoindex_books_1
row 659 missing from index sqlite_autoindex_books_1
row 665 missing from index sqlite_autoindex_books_1
row 667 missing from index sqlite_autoindex_books_1
row 674 missing from index sqlite_autoindex_books_1
row 676 missing from index sqlite_autoindex_books_1

I'd start going through https://www.sqlite.org/howtocorrupt.html and trying
to figure out if anything there might have happened.


On Sat, Dec 21, 2019 at 1:43 PM Michael Walker (barrucadu) <
m...@barrucadu.co.uk> wrote:

> Hi Shawn,
>
> Thanks for your response.  Though that doesn't seem to be the case:
>
> sqlite> select bookIsbn, typeof(bookIsbn) from books where bookIsbn =
> "9781496030825";
> 9781496030825|text
> sqlite> select bookIsbn, typeof(bookIsbn) from books where bookIsbn =
> "9780099477310";
> 9780099477310|text
>
> The column is a VARCHAR:
>
> CREATE TABLE `books` (
>`bookIsbn` VARCHAR NOT NULL PRIMARY KEY,
>`bookTitle` VARCHAR NOT NULL,
>`bookSubtitle` VARCHAR NOT NULL,
>`bookCover` VARCHAR NULL,
>`bookVolume` VARCHAR NOT NULL,
>`bookFascicle` VARCHAR NOT NULL,
>`bookVoltitle` VARCHAR NOT NULL,
>`bookAuthor` VARCHAR NOT NULL,
>`bookTranslator` VARCHAR NULL,
>`bookEditor` VARCHAR NULL,
>`bookSorting` VARCHAR NULL,
>`bookRead` BOOLEAN NOT NULL,
>`bookLastRead` TIMESTAMP NULL,
>`bookNowReading` BOOLEAN NOT NULL,
>`bookLocation` VARCHAR NOT NULL,
>`bookBorrower` VARCHAR NOT NULL,
>`bookCategoryCode` VARCHAR NOT NULL,
>FOREIGN KEY(`bookCategoryCode`) REFERENCES
> `book_categories`(`categoryCode`)
> );
>
> I'm not sure the attachment to my first email got through, so here's the
> database:
> https://misc.barrucadu.co.uk/forever/82e5584a-e4a8-4804-8abe-8f00be73f725/bookdb.sqlite
>
>
> ‐‐‐ Original Message ‐‐‐
> On Saturday, 21 December 2019 21:37, Shawn Wagner 
> wrote:
>
> > Without seeing your table definition, this is just a guess, but maybe the
> > duplicate keys are stored as different types, with the primary key column
> > having an affinity that doesn't force one particular storage class:
> >
> > sqlite> CREATE TABLE test(id PRIMARY KEY);
> > sqlite> INSERT INTO test VALUES('12345');
> > sqlite> INSERT INTO test VALUES(12345);
> > sqlite> SELECT id, typeof(id) FROM test;
> > id typeof(id)
> >
> > 12345 text
> > 12345 integer
> > sqlite> SELECT id, typeof(id) FROM test WHERE id = '12345';
> > id typeof(id)
> >
> > 12345 text
> >
> > On Sat, Dec 21, 2019 at 1:26 PM Michael Walker (barrucadu) <
> > m...@barrucadu.co.uk> wrote:
> >
> > > Hi,
> > > I've somehow ended up with a table which contains two records for the
> same
> > > primary key - well actually I've got two primary keys like that, so I
> have
> > > four records with two primary keys between them.
> > > I've been unable to reproduce this from a clean database, so I attach
> my
> > > database file to this email.
> > > Here are some oddities:
> > >
> > > $ sqlite3 bookdb.sqlite
> > > SQLite version 3.28.0 2019-04-16 19:49:53
> > > Enter ".help" for usage hints.
> > > sqlite> select * from books where bookIsbn = "9781496030825";
> > > 9781496030825|Can Such Things Be?||9781496030825.jpgBierce,
> > > Ambrose0||0|London||F
> > > sqlite> select * from books where bookIsbn = "9780099477310";
> > > 9780099477310|Catch-22||9780099477310.jpgHeller,
> > > Joseph0||0|London||F
> > > sqlite> .output books_issue
> > > sqlite> .dump books
> > > sqlite> .quit
> > >
> > > $ grep "9781496030825" < books_issue
> > > INSERT INTO books VALUES('9781496030825','Can Such Things
> > > Be?','','9781496030825.jpg','','','','Bierce,
> > > Ambro

Re: [sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output

[Shawn Wagner]

Without seeing your table definition, this is just a guess, but maybe the
duplicate keys are stored as different types, with the primary key column
having an affinity that doesn't force one particular storage class:

sqlite> CREATE TABLE test(id PRIMARY KEY);
sqlite> INSERT INTO test VALUES('12345');
sqlite> INSERT INTO test VALUES(12345);
sqlite> SELECT id, typeof(id) FROM test;
id  typeof(id)
--  --
12345   text
12345   integer
sqlite> SELECT id, typeof(id) FROM test WHERE id = '12345';
id  typeof(id)
--  --
12345   text



On Sat, Dec 21, 2019 at 1:26 PM Michael Walker (barrucadu) <
m...@barrucadu.co.uk> wrote:

> Hi,
>
> I've somehow ended up with a table which contains two records for the same
> primary key - well actually I've got two primary keys like that, so I have
> four records with two primary keys between them.
>
> I've been unable to reproduce this from a clean database, so I attach my
> database file to this email.
>
> Here are some oddities:
>
> ```
> $ sqlite3 bookdb.sqlite
> SQLite version 3.28.0 2019-04-16 19:49:53
> Enter ".help" for usage hints.
> sqlite> select * from books where bookIsbn = "9781496030825";
> 9781496030825|Can Such Things Be?||9781496030825.jpgBierce,
> Ambrose0||0|London||F
> sqlite> select * from books where bookIsbn = "9780099477310";
> 9780099477310|Catch-22||9780099477310.jpgHeller,
> Joseph0||0|London||F
> sqlite> .output books_issue
> sqlite> .dump books
> sqlite> .quit
>
> $ grep "9781496030825" < books_issue
> INSERT INTO books VALUES('9781496030825','Can Such Things
> Be?','','9781496030825.jpg','','','','Bierce,
> Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
> INSERT INTO books VALUES('9781496030825','Can Such Things
> Be?','','9781496030825.jpg','','','','Bierce,
> Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
>
> $ grep "9780099477310" < books_issue
> INSERT INTO books
> VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller,
> Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
> INSERT INTO books
> VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller,
> Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
>
> $ sqlite3 bookdb.sqlite
> SQLite version 3.28.0 2019-04-16 19:49:53
> Enter ".help" for usage hints.
> sqlite> drop table books;
> sqlite>
>
> $ sqlite3 bookdb.sqlite < books_issue
> Error: near line 697: UNIQUE constraint failed: books.bookIsbn
> Error: near line 698: UNIQUE constraint failed: books.bookIsbn
> ```
>
> Updating either affected record results in the second copy in the .dump
> output being updated, the first copy has the original state.
>
> The table has always had a primary key constraint, so I'm not sure how
> it's ended up in its current state.  However, even if there were not a
> primary key constraint, there do seem to be two very real bugs here: SELECT
> gives different results to .dump, and .dump is producing output which can't
> be restored.
>
> I'm not sure if you'll be able to make anything of this, as I say I
> haven't been able to reproduce it from a blank database, but I figure
> you'll be better at debugging this than me.
>
> Thanks
>
> --
> Michael Walker (http://www.barrucadu.co.uk)
> ___
> 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] CVE-2019-19317

[Shawn Wagner]

Addendum: I suspect it's the one mentioned as being filed in this earlier
thread:
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg117794.html


On Sat, Dec 14, 2019, 2:12 PM Shawn Wagner  wrote:

> This appears to be a CVE pertaining to a feature that hasn't even been
> released yet (generated columns will be in the upcoming 3.31; they're
> certainly not in the referenced 3.30.1). Unless you're using the
> development snapshot from the download page or following trunk on fossil
> and haven't updated in a while, it sounds like a complete non-issue.
>
> On Sat, Dec 14, 2019, 1:36 PM Raitses, Alex 
> wrote:
>
>> Hello,
>> CVE-2019-19317 (https://nvd.nist.gov/vuln/detail/CVE-2019-19317) was
>> submitted on SQLite.
>> As far as I can see the patch is already submitted. Can you confirm
>> please?
>> Do you have estimation for the fixed version release?
>>
>>
>> Thanks in advance,
>> Regards,
>> Alex
>>
>> -
>> Intel Israel (74) Limited
>>
>> This e-mail and any attachments may contain confidential material for
>> the sole use of the intended recipient(s). Any review or distribution
>> by others is strictly prohibited. If you are not the intended
>> recipient, please contact the sender and delete all copies.
>> ___
>> 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] CVE-2019-19317

[Shawn Wagner]

This appears to be a CVE pertaining to a feature that hasn't even been
released yet (generated columns will be in the upcoming 3.31; they're
certainly not in the referenced 3.30.1). Unless you're using the
development snapshot from the download page or following trunk on fossil
and haven't updated in a while, it sounds like a complete non-issue.

On Sat, Dec 14, 2019, 1:36 PM Raitses, Alex  wrote:

> Hello,
> CVE-2019-19317 (https://nvd.nist.gov/vuln/detail/CVE-2019-19317) was
> submitted on SQLite.
> As far as I can see the patch is already submitted. Can you confirm please?
> Do you have estimation for the fixed version release?
>
>
> Thanks in advance,
> Regards,
> Alex
>
> -
> Intel Israel (74) Limited
>
> This e-mail and any attachments may contain confidential material for
> the sole use of the intended recipient(s). Any review or distribution
> by others is strictly prohibited. If you are not the intended
> recipient, please contact the sender and delete all copies.
> ___
> 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] Missed index opportunities with sorting?

[Shawn Wagner]

Consider:

sqlite> CREATE TABLE test1(a, b);
sqlite> CREATE INDEX test1_idx ON test1(a, b ASC);
sqlite> EXPLAIN QUERY PLAN SELECT * FROM test1 ORDER BY a, b;
QUERY PLAN
`--SCAN TABLE test1 USING COVERING INDEX test1_idx
sqlite> EXPLAIN QUERY PLAN SELECT * FROM test1 ORDER BY a, b DESC;
QUERY PLAN
|--SCAN TABLE test1 USING COVERING INDEX test1_idx
`--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY

The first one uses the index for all sorting, but the second one only uses
it for sorting a, not b. I feel like the descending sort could make use of
the index too, just reading the b sections backwards to get the right
order. Is there something I'm overlooking that would make this sort of
optimization impractical or otherwise a bad idea?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

[Shawn Wagner]

Does running an ANALYZE have any impact?

On Fri, Dec 6, 2019 at 12:47 AM radovan5  wrote:

> Also tested with latest version 3.30.1. For showing problem I need just
> 2 tables (for join).
>
> -- cca 30.000.000 recs
> CREATE TABLE ARHDAJ(
>ID_ARHDAJ integer primary key autoincrement,
>ID_ARHPLA integer,
>R integer,
>O decimal(8,4),
>Z decimal(12,2),
>VEZA varchar(8) collate systemnocase);
> CREATE UNIQUE INDEX ARHDAJ_IDX_ARHPLA on ARHDAJ (ID_ARHPLA,R,ID_ARHDAJ);
> CREATE UNIQUE INDEX ARHDAJ_IDX_R on ARHDAJ (R,ID_ARHDAJ);
>
> -- cca 100 recs
> CREATE TABLE IZBPLA (
>ID_PLAinteger primary key autoincrement,
>ID_DELinteger,
>L4MM  integer,
>OEvarchar(2) collate systemnocase,
>SMvarchar(4) collate systemnocase,
>DNvarchar(10) collate systemnocase,
>DDM   integer,
>P integer,
>U decimal(6,2),
>UNdecimal(6,2),
>O decimal(8,4),
>ZOdecimal(12,2),
>ZBdecimal(12,2),
>ZNdecimal(12,2),
>OSdecimal(8,4),
>DOH   varchar(1) collate systemnocase);
> CREATE UNIQUE INDEX IZBPLA_IDX_DEL on IZBPLA(ID_DEL, ID_PLA);
> CREATE UNIQUE INDEX IZBPLA_IDX_P on IZBPLA(P, ID_PLA);
>
> Problematic sql is:
>
>select
>  R.ID_ARHDAJ, R.ID_ARHPLA, R.R, R.O, R.Z, R.VEZA
>from IZBPLA P
>left join ARHDAJ R on R.ID_ARHPLA = P.ID_PLA
>where P.ID_PLA < 10
>  and R.ID_ARHDAJ is not null;
>
> explain query plan before 3.28.0 is:
>
> 0: SEARCH TABLE IZBPLA AS P USING INTEGER PRIMARY KEY (rowid 1: SEARCH TABLE ARHDAJ AS R USING INDEX ARHDAJ_IDX_ARHPLA (ID_ARHPLA=?)
>
> explain query plan after 3.28.0 is:
>
> 0: SEARCH TABLE ARHDAJ AS R USING INDEX ARHDAJ_IDX_ARHPLA (ID_ARHPLA=?)
> 1: SEARCH TABLE IZBPLA AS P USING INTEGER PRIMARY KEY (rowid
> Workaround is to change "and R.ID_ARHDAJ is not null" to "and
> ifnull(R.ID_ARHDAJ,0) <> 0".
>
> So sqlite goes through large table but should go through small table and
> search record in
> large for every record.
>
> Best Regards
> Radovan Antloga
>
> ___
> 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] Official Github Mirror? Of the Amalgamation?

[Shawn Wagner]

There is an official github mirror of the fossil repository:
https://github.com/sqlite/sqlite

The amalgamation is two files, though. When you want to upgrade a bundled
sqlite, to a new version it's trivial to update them. Setting up submodules
or whatever seems like massive overkill.


On Mon, Nov 18, 2019 at 1:30 AM Dominique Devienne 
wrote:

> In searches,  https://github.com/mackyle/sqlite comes up first,
> but given that Fossil has good/better interop with GIT these days,
> why not an official mirror?
>
> Also, mirrors are for the normal repo, while ability to refer to an
> amalgamation
> in one's project by directly linking to it via a GIT submodule (or
> something like
> that, I'm no GIT expert) instead of copying it in one's project(s), would
> be useful IMHO.
>
> Just my $0.02c. --DD
> ___
> 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] Can SQLite import Latin1 data?

[Shawn Wagner]

Unlike UTF-16, which uses 2 byte code units without a fixed endianess
(meaning to be robust you need to account for both little and big endian
encodings when reading files using it), UTF-8 uses a 1 byte code unit and
thus doesn't have any endian issues or a need for a byte order mark.

On Fri, Nov 15, 2019, 1:15 PM Jose Isaias Cabrera 
wrote:

>
> Shawn Wagner, on Friday, November 15, 2019 04:01 PM, wrote...
> >
> > If you're on Windows, which cp1252 suggests, just make sure that you
> don't
> > end up with a BOM at the start of the file when you convert it. Windows
> > tools that output utf-8 are sometimes prone to add one even though it's
> > pointless to have.
>
> Why do you think it's pointless?
>
> josé
> ___
> 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] Can SQLite import Latin1 data?

[Shawn Wagner]

If you're on Windows, which cp1252 suggests, just make sure that you don't
end up with a BOM at the start of the file when you convert it. Windows
tools that output utf-8 are sometimes prone to add one even though it's
pointless to have.

On Linux etc., you can try

.import '| iconv -f cp1252 -t utf8 myfile.csv' mytable

to convert on the fly when importing into sqlite.


On Fri, Nov 15, 2019, 12:00 PM Winfried  wrote:

> Hello,
>
> I have a big CSV file that's encoded in Latin1 (cp1252), while SQLite
> stores
> strings as Unicode.
>
> Neither Google nor ".help" helped to find if SQLite offers a switch to
> convert Latin1 to UTF-8 on the fly before running the ".import" command.
>
> Should I first convert the file into UTF-8 before importing it into SQLite?
>
> Thank you.
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> 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] sqlite3 shell .parameter command evaluating arguments when it shouldn't.

[Shawn Wagner]

Consider:

sqlite> .parameter init
sqlite> .parameter set :date '2019-11-15'
sqlite> .parameter list
:date 1993

How do I make it treat the value argument as a string and not as a numeric
expression that gets evaluated?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Shawn Wagner]

Doing the latter - closing everything, forking, re-opening - is always
going to be safe. Or if the parent isn't going to use the connection, just
don't open the database until you're in the child after forking.

On Mon, Nov 11, 2019 at 8:08 AM Jukka Marin  wrote:

> On Fri, Nov 08, 2019 at 09:57:25AM +0200, Jukka Marin wrote:
> > On Thu, Nov 07, 2019 at 09:26:46AM -0800, Shawn Wagner wrote:
> > > This line stood out:
> > >
> > > > The main process opens the databases and then forks the other
> processes
> > > which can then perform database operations using the already opened
> > > databases.
> > >
> > > From
> > >
> https://sqlite.org/howtocorrupt.html#_carrying_an_open_database_connection_across_a_fork_
> > > :
> > >
> > > > Do not open an SQLite database connection, then fork(), then try to
> use
> > > that database connection in the child process. All kinds of locking
> > > problems will result and you can easily end up with a corrupt database.
> > > SQLite is not designed to support that kind of behavior. Any database
> > > connection that is used in a child process must be opened in the child
> > > process, not inherited from the parent.
> > >
> > > In this kind of situation, I usually use pthread_atfork() callbacks to
> > > automate closing databases and then re-opening them in the parent and
> child.
> >
> > Okay, thanks!  I suspected it could be something like this, but couldn't
> > find anything in the SQLite docs.
>
> In some situations, my main process will have the databases opened before
> it needs to fork a new child (this happens only if a child dies and
> has to be restarted).  If the child process immediately closes its copies
> of the databases and then reopens them, will it be safe?
>
> Or does the main process need to close all databases, then fork, then
> reopen the databases?
>
> Thanks again!
>
>   Jukka Marin
> ___
> 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] database disk image is malformed

[Shawn Wagner]

This line stood out:

> The main process opens the databases and then forks the other processes
which can then perform database operations using the already opened
databases.

From
https://sqlite.org/howtocorrupt.html#_carrying_an_open_database_connection_across_a_fork_
:

> Do not open an SQLite database connection, then fork(), then try to use
that database connection in the child process. All kinds of locking
problems will result and you can easily end up with a corrupt database.
SQLite is not designed to support that kind of behavior. Any database
connection that is used in a child process must be opened in the child
process, not inherited from the parent.

In this kind of situation, I usually use pthread_atfork() callbacks to
automate closing databases and then re-opening them in the parent and child.


On Thu, Nov 7, 2019 at 9:18 AM Jukka Marin  wrote:

> Dear List,
>
> I'm developing software which keeps parameters and real-time data in
> SQLite databases on a x86_64/linux system.  I am getting "database
> disk image is malformed" errors from SQLite when using select.  Some
> select operations succeed, some fail.  This happens on multiple systems.
>
> I would like to know if I'm doing something that is known not to work
> with SQLite.  Background information:
>
> The software runs on a xen virtual machine.  The database files are
> on a separate disk (actually, a separate partition of the host linux)
> which is directly mounted on the virtual machine (not using NFS)
> and uses ext4 filesystem.  The whole system runs off a single SSD disk.
>
> SQLite version is 3.28.0 and it is currently built and statically
> linked against the application binary.
>
> The databases are originally created by a script and the sqlite3
> command.  "pragma journal_mode=wal;" is set for all databases.
>
> The software consists of multiple processes.  The main process opens
> the databases and then forks the other processes which can then perform
> database operations using the already opened databases.  No locking is
> done in the processes, they rely on the SQLite internal mechanisms.
> sqlite3_busy_timeout is set to 2500 for all databases.
>
> The processes mostly read (select) data from the databases, but also
> occasionally add new rows or update existing rows.
>
>
> Is this system with multiple processes accessing the databases
> safe with SQLite?  If it is, does anyone have ideas of what could
> be corrupting the database files?
>
> These systems are running 24/7, but of course, it _may_ be possible
> that someone has disconnected the power supply without shutting down
> the system first and causing errors in the database files.
>
> Is there something I could do to (try to) prevent database corruption
> if power is lost?  (In software - a UPS is a hardware solution, of
> course).
>
> If the database is corrupted, is there any way of recovering it?
> Maybe I should use sqlite3 to dump and recreate the database (of
> course, some or all of the data might get lost)?
>
> Experienced SQLite users, please share your ideas - thank you! :-)
>
>   Jukka Marin
>   (a beginner with SQLite)
> ___
> 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] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

[Shawn Wagner]

Another option (Which the devs might consider) is to replace use of the asm
keyword with __asm__, which is a form of the extension understood even in
strict mode.

(I have no idea why gcc doesn't offer intrinsics for bit rotation, as it's
a common task and shouldn't require hacks like this to reliably do
efficiently).

On Thu, Nov 7, 2019 at 8:23 AM Dennis Clarke  wrote:

> On 2019-11-07 11:15, Shawn Wagner wrote:
> > Does that toolchain use gcc, or a different compiler? If gcc, are you
> using
> > the same CFLAGS as on the redhat box (you're turning on a bunch of extra
> > non-default options there)?
> >
>
> I don't see how --with-threads can be a concern. The other options do
> not qualify as a "bunch" at all.
>
> Regardless it looks like the codebase does strange GNU extensions.
>
> There must be a way to switch all that off.
>
> Dennis
>
> ps : don't top post.
>
> ___
> 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] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

[Shawn Wagner]

... Just don't use strict c99 mode when compiling with gcc? Drop the -std
argument from your CFLAGS to use the default (gnu11 since gcc 5) or
explicitly use gnu99, which gives you that version of the C standard + gcc
extensions.

(Not that they have anything to do with the problem, but compiling with -O0
and -fno-builtin are strange unless you're planning on spending some
quality time in a debugger stepping through code, and -malign-double is
already the default on x86-64 so kind of pointless)

On Thu, Nov 7, 2019 at 8:23 AM Dennis Clarke  wrote:

> On 2019-11-07 11:15, Shawn Wagner wrote:
> > Does that toolchain use gcc, or a different compiler? If gcc, are you
> using
> > the same CFLAGS as on the redhat box (you're turning on a bunch of extra
> > non-default options there)?
> >
>
> I don't see how --with-threads can be a concern. The other options do
> not qualify as a "bunch" at all.
>
> Regardless it looks like the codebase does strange GNU extensions.
>
> There must be a way to switch all that off.
>
> Dennis
>
> ps : don't top post.
>
> ___
> 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] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

[Shawn Wagner]

For that matter, is the Solaris box x86 or sparc? If the latter, this
particular code path obviously wouldn't ever be used on it.

On Thu, Nov 7, 2019, 8:10 AM Dennis Clarke  wrote:

> On 2019-11-07 11:01, Shawn Wagner wrote:
> > Compiling with -std=iso9899:1999 is the culprit. Strict c99 mode disables
> > gcc extensions like inline asm.
>
> OKay but I have no issues doing a compile on a Solaris server using the
> Oracle Studio 12.6 tools and strict C99.  So how is C99 the issue?
>
> Dennis
>
> ___
> 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] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

[Shawn Wagner]

Does that toolchain use gcc, or a different compiler? If gcc, are you using
the same CFLAGS as on the redhat box (you're turning on a bunch of extra
non-default options there)?

On Thu, Nov 7, 2019, 8:10 AM Dennis Clarke  wrote:

> On 2019-11-07 11:01, Shawn Wagner wrote:
> > Compiling with -std=iso9899:1999 is the culprit. Strict c99 mode disables
> > gcc extensions like inline asm.
>
> OKay but I have no issues doing a compile on a Solaris server using the
> Oracle Studio 12.6 tools and strict C99.  So how is C99 the issue?
>
> Dennis
>
> ___
> 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] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

[Shawn Wagner]

Compiling with -std=iso9899:1999 is the culprit. Strict c99 mode disables
gcc extensions like inline asm.

On Thu, Nov 7, 2019, 7:57 AM Dennis Clarke  wrote:

>
> While building sqlite from the zip file sqlite-src-3300100.zip I saw
> what seems to be a normal configure and then madness :
>
>
> boe13$ pwd
> /opt/bw/build/sqlite-src-3300100_rhel_74_3.10.0-693.el7.x86_64.001
> boe13$ ./configure --prefix=/opt/bw --enable-shared --enable-static
> --enable-readline --enable-threadsafe 2>&1 | tee
> ../sqlite-src-3300100_rhel_74_3.10.0-693.el7.x86_64.001.config.log
> checking build system type... x86_64-pc-linux-gnu
> checking host system type... x86_64-pc-linux-gnu
> checking for gcc... /opt/bw/gcc9/bin/gcc
> checking whether the C compiler works... yes
> checking for C compiler default output file name... a.out
> checking for suffix of executables...
> checking whether we are cross compiling... no
> checking for suffix of object files... o
> checking whether we are using the GNU C compiler... yes
> checking whether /opt/bw/gcc9/bin/gcc accepts -g... yes
> checking for /opt/bw/gcc9/bin/gcc option to accept ISO C89... none needed
> checking for a sed that does not truncate output... /usr/bin/sed
> checking for grep that handles long lines and -e... /usr/bin/grep
> checking for egrep... /usr/bin/grep -E
> checking for fgrep... /usr/bin/grep -F
> checking for ld used by /opt/bw/gcc9/bin/gcc... /usr/bin/ld
> checking if the linker (/usr/bin/ld) is GNU ld... yes
> checking for BSD- or MS-compatible name lister (nm)...
> /opt/bw/gcc9/bin/x86_64-redhat-linux-gcc-nm -p
> checking the name lister (/opt/bw/gcc9/bin/x86_64-redhat-linux-gcc-nm
> -p) interface... BSD nm
> checking whether ln -s works... yes
> checking the maximum length of command line arguments... 1572864
> checking whether the shell understands some XSI constructs... yes
> checking whether the shell understands "+="... yes
> checking for /usr/bin/ld option to reload object files... -r
> checking for objdump... /usr/bin/objdump
> checking how to recognize dependent libraries... pass_all
> checking for ar... /opt/bw/gcc9/bin/x86_64-redhat-linux-gcc-ar
> checking for strip... /usr/bin/strip
> checking for ranlib... /opt/bw/gcc9/bin/x86_64-redhat-linux-gcc-ranlib
> checking command to parse /opt/bw/gcc9/bin/x86_64-redhat-linux-gcc-nm -p
> output from /opt/bw/gcc9/bin/gcc object... ok
> checking how to run the C preprocessor... /opt/bw/gcc9/bin/gcc -E
> checking for ANSI C header files... yes
> checking for sys/types.h... yes
> checking for sys/stat.h... yes
> checking for stdlib.h... yes
> checking for string.h... yes
> checking for memory.h... yes
> checking for strings.h... yes
> checking for inttypes.h... yes
> checking for stdint.h... yes
> checking for unistd.h... yes
> checking for dlfcn.h... yes
> checking for objdir... .libs
> checking if /opt/bw/gcc9/bin/gcc supports -fno-rtti -fno-exceptions... no
> checking for /opt/bw/gcc9/bin/gcc option to produce PIC... -fPIC -DPIC
> checking if /opt/bw/gcc9/bin/gcc PIC flag -fPIC -DPIC works... yes
> checking if /opt/bw/gcc9/bin/gcc static flag -static works... no
> checking if /opt/bw/gcc9/bin/gcc supports -c -o file.o... yes
> checking if /opt/bw/gcc9/bin/gcc supports -c -o file.o... (cached) yes
> checking whether the /opt/bw/gcc9/bin/gcc linker (/usr/bin/ld -m
> elf_x86_64) supports shared libraries... yes
> checking whether -lc should be explicitly linked in... no
> checking dynamic linker characteristics... GNU/Linux ld.so
> checking how to hardcode library paths into programs... immediate
> checking whether stripping libraries is possible... yes
> checking if libtool supports shared libraries... yes
> checking whether to build shared libraries... yes
> checking whether to build static libraries... yes
> checking for a BSD-compatible install... /usr/bin/install -c
> checking for special C compiler options needed for large files... no
> checking for _FILE_OFFSET_BITS value needed for large files... no
> checking for int8_t... yes
> checking for int16_t... yes
> checking for int32_t... yes
> checking for int64_t... yes
> checking for intptr_t... yes
> checking for uint8_t... yes
> checking for uint16_t... yes
> checking for uint32_t... yes
> checking for uint64_t... yes
> checking for uintptr_t... yes
> checking for sys/types.h... (cached) yes
> checking for stdlib.h... (cached) yes
> checking for stdint.h... (cached) yes
> checking for inttypes.h... (cached) yes
> checking malloc.h usability... yes
> checking malloc.h presence... yes
> checking for malloc.h... yes
> checking for fdatasync... yes
> checking for gmtime_r... yes
> checking for isnan... yes
> checking for localtime_r... yes
> checking for localtime_s... no
> checking for malloc_usable_size... yes
> checking for strchrnul... yes
> checking for usleep... yes
> checking for utime... yes
> checking for pread... yes
> checking for pread64... yes
> checking for pwrite... yes
> checking for pwrite64... yes
> checking for tclsh8.7... tclsh8.7
> configu

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

[Shawn Wagner]

That's what I told the guy having the original issue to do, yes. That's not
important.

My concern is why a conversion that's only supposed to happen if it's
lossless is in fact happening and causing data loss.

On Sun, Oct 13, 2019, 4:48 PM Igor Tandetnik  wrote:

> On 10/13/2019 7:25 PM, 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.
>
> If you want the text preserved character for character, store it in a
> column with TEXT affinity. NUMERIC makes little sense for this requirement.
> --
> Igor  Tandetnik
>
>
> ___
> 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] Possible bug in storing text values in numeric columns

[Shawn Wagner]

Yes, I know. (This all comes from someone on stack overflow who used
"string" as a column type and thus ran into this issue because that of
course results in numeric affinity)

On Sun, Oct 13, 2019, 4:27 PM Simon Slavin  wrote:

> On 13 Oct 2019, at 10:11pm, Shawn Wagner  wrote:
>
> > As you can see, the leading zeros in the original string are gone and
> it's been converted to an integer. This seems to violate the "lossless and
> reversible" constraint. Shouldn't it be kept as text?
>
> You defined the column as 'numeric'.  Had you defined it as 'text' you
> would get different behaviour.
> ___
> 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] Possible bug in storing text values in numeric columns

[Shawn Wagner]

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.

On Sun, Oct 13, 2019, 4:10 PM Igor Tandetnik  wrote:

> On 10/13/2019 5:11 PM, Shawn Wagner wrote:
> > The documentation for a column with NUMERIC affinity says
> >
> >> When text data is inserted into a NUMERIC column, the storage class of
> > the text is converted to INTEGER or REAL (in order of preference) if such
> > conversion is lossless and reversible.
>
> "Lossless and reversible" here clearly means that the numerical value is
> preserved, not that the exact text representation is. Thus, a couple
> paragraphs down in the same article, you'll find
>
> "A string might look like a floating-point literal with a decimal point
> and/or exponent notation but as long as the value can be expressed as an
> integer, the NUMERIC affinity will convert it into an integer. Hence, the
> string '3.0e+5' is stored in a column with NUMERIC affinity as the integer
> 30, not as the floating point value 30.0."
>
> It is clear in this example that '3.0e+5' will in fact get coerced to a
> number, and that number will *not* in fact be rendered as '3.0e+5' when
> converted back to text.
> --
> Igor Tandetnik
>
> ___
> 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] Possible bug in storing text values in numeric columns

[Shawn Wagner]

3.30, though it goes back to at least 3.8.7 (the oldest version I have
available to test with)

On Sun, Oct 13, 2019, 3:03 PM Igor Korot  wrote:

> Hi,
>
> On Sun, Oct 13, 2019 at 4:12 PM Shawn Wagner 
> wrote:
> >
> > The documentation for a column with NUMERIC affinity says
> >
> > > When text data is inserted into a NUMERIC column, the storage class of
> > the text is converted to INTEGER or REAL (in order of preference) if such
> > conversion is lossless and reversible.
> >
> > But consider:
> >
> > sqlite> create table foo(bar numeric);
> > sqlite> insert into foo values ('0012');
> > sqlite> select bar, typeof(bar) from foo;bar typeof(bar)
> > --  ---12  integer
> >
> >
> > As you can see, the leading zeros in the original string are gone and
> it's
> > been converted to an integer. This seems to violate the "lossless and
> > reversible" constraint. Shouldn't it be kept as text?
>
> What version of SQLite do you use?
>
> Thank you.
>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Shawn Wagner]

The documentation for a column with NUMERIC affinity says

> When text data is inserted into a NUMERIC column, the storage class of
the text is converted to INTEGER or REAL (in order of preference) if such
conversion is lossless and reversible.

But consider:

sqlite> create table foo(bar numeric);
sqlite> insert into foo values ('0012');
sqlite> select bar, typeof(bar) from foo;bar typeof(bar)
--  ---12  integer


As you can see, the leading zeros in the original string are gone and it's
been converted to an integer. This seems to violate the "lossless and
reversible" constraint. Shouldn't it be kept as text?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Date time input

[Shawn Wagner]

So, I have a bunch of sqlite extension modules that I really should polish
up for an official release Real Soon Now...

I just added a basic interface to the POSIX strptime() function to the
string functions library:

sqlite> .load ./libstring_funcs
sqlite> select date(strptime('%m/%d/%Y', '10/08/2019'), 'unixepoch') AS
today;
today
--
2019-10-08

If anyone thinks they'd find it handy, the project lives at
https://github.com/shawnw/useful_sqlite_extensions/


On Tue, Oct 8, 2019 at 5:46 AM Hick Gunter  wrote:

> What it boils down to is asking the data storage layer to perform a
> presentation layer task.
>
> If you insist on solving the problem inside an SQL statement, you can
> always write your own extension function to "easily" perform the necessary
> conversion.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Jose Isaias Cabrera
> Gesendet: Dienstag, 08. Oktober 2019 14:32
> An: SQLite mailing list 
> Betreff: [EXTERNAL] Re: [sqlite] Date time input
>
>
> Jens Alfke, on Monday, October 7, 2019 09:18 PM, wrote...
> [clip]
> > I swear, half the questions on this list build down to "Why
> > doesn't SQLite act like MS Access?" If you need all the bells and
> > whistles of formatting input and output, then use a fancy DBMS
> > application. SQLite is for embedded use _inside_
> > applications.
>
> No, that is not what I was trying to say or ask.  Not even close. What I
> was trying to say, and most of you missed it was, that if I give date a
> date format, and I also provide the format of how that date is to be
> understood, ie.
>
> date('5/22/2019','m/d/')
>
> where the date is the first entry, '5/22/2019', and the format is the
> second entry, 'm/d/', that SQLite could take that set of data and
> easily convert and return the ISO date I want.  Yes, I know I can write
> that outside the code, or inside in SQL, but "it would be nice to have
> this."  Thanks for all the responses.
>
> josé
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick | Software Engineer | Scientific Games International GmbH |
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> 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] Comparison of incompatible types

[Shawn Wagner]

https://www.sqlite.org/datatype3.html#comparison_expressions has the rules
for how values of different types are ordered and when type conversions
automatically happen.

Numbers are always less than strings, so in that example you should be
using round() instead of printf() as you discovered.


On Sat, Sep 14, 2019, 3:47 AM kapil  wrote:

> Hi all,
>
> I was wondering whether sqlite does type checking for fields which are
> referenced in WHERE clause. Because when i tried to compare a field
> generated by printf function and tried to compare it with a float value,
> the comparison didn't work
>
> Eg.
> SELECT count.theme as theme, printf("%.2f",
> (count.num_sets*100.00/sum.total)) as percentage
>
> FROM count,sum
>
> WHERE percentage >= 5.00;
>
> It was not giving expected results.
> When i replaced printf with ROUND function, it worked.
> So i got to think whether this was because printf returns string value.
>
> If so then shouldn't there be type check on fields in comparisons so
> that user can get a meaningful error message or in this case, any error
> message at all
>
> Regards
> Kapil Garg
>
> ___
> 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] Bug with json_group_array() as a window function

[Shawn Wagner]

Using 3.29 and a 3.30 snapshot:

When using json_group_array() as a window function, it loses the tagging of
its argument as being JSON, treating JSON objects etc. as strings instead
when creating arrays.

Sample table:

CREATE TABLE testjson(id INTEGER PRIMARY KEY, j TEXT);
INSERT INTO testjson VALUES(1, '{"a":1}');
INSERT INTO testjson VALUES(2, '{"b":2}');

This query

SELECT json_group_array(json(j)) OVER (ORDER BY id) FROM testjson;

produces

["{\"a\":1}"]
["{\"a\":1}","{\"b\":2}"]

instead of

[{"a":1}]
[{"a":1},{"b":2}]

while the plain aggregate

SELECT json_group_array(json(j)) FROM testjson;

produces the expected

[{"a":1},{"b":2}]
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] the sqlite3 documentation would be pretty good if it wasn't tragic...

[Shawn Wagner]

The syntax diagrams are created by a tcl script, not drawn by hand:
https://wiki.tcl-lang.org/page/Generating+Syntax+Diagrams+Using+Tk

I think a lot of the documentation assumes the reader already knows the
basics of sql... What kind of examples are you thinking of?


On Tue, Jun 25, 2019, 8:16 PM Ben Earhart  wrote:

> ...that the person(s) that has no problem writing small, but solid, walls
> of technical detail and drawing intricate circularly recursive syntax
> diagrams which require multiple levels of detail to coherently represent,
> can't be bothered to write example sql code for the top dozen things that
> most surely cover better than the majority of real-world uses cases.
>
> Does anybody here know where such a thing might exist? I think sqlite3 is a
> gem but, for whatever reason, I have had poor luck getting sqlite3 sql code
> examples - just scraps here and there. I don't use it near as much as I
> could simply because of difficulty getting examples for a few basic schema
> patterns.
>
> Thanks,
> Ben
> ___
> 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] Unexpected/undocumented REPLACE() behavior

[Shawn Wagner]

With most functions, including replace(), if any of the arguments are null,
it returns null.

On Mon, Jun 10, 2019 at 4:29 PM Tony Papadimitriou  wrote:

> Example:
>
> --- CUT ---
> create table t(s text);
> insert into t values ('1'),('null'),('3');
>
> .print 'BEFORE'
> select rowid,* from t;
> update t set s = replace(s,'null',null)
> --where s = 'null'  --adding this works of course but that’s not my point
> ;
>
> .print 'AFTER'
> select rowid,* from t;
> --- CUT ---
>
> The documentation says: “The replace(X,Y,Z) function returns a string
> formed by substituting string Z for every occurrence of string Y in string
> X. The BINARY collating sequence is used for comparisons. If Y is an empty
> string then return X unchanged. If Z is not initially a string, it is cast
> to a UTF-8 string prior to processing.”
>
> “substituting string Z for every occurrence of string Y” implies that if
> there is no occurrence of string Y nothing should happen to the original
> string, right?
>
> Accordingly, my expectation is that either:
> 1. null will remain null as there is not really a string (even empty) that
> can truly represent it, or
> 2. we allow null to be converted to empty string so that the “returns a
> string” requirement can be satisfied.
>
> or, maybe
> 3. using null for the Z part gives an error.
>
> In either [1] or [2] above, however, if the target string (Y part) is not
> found, the result should be unaltered.
> The replacement seems to occur regardless of the target being found or not.
>
> ... and everything becomes null.
>
> Note: I know SQLite3 tries to mimic in some ways Postgres, so I tried with
> it also, and got the same unexpected behavior.
> From a quick look, I haven’t seen anything in their documentation on
> REPLACE to justify it either.
>
> I consider this behavior wrong, or (easier way out) the documentation
> should make a special note about null behaving the way it does.
>
> Thank you.
> ___
> 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] Unexpected parsing of an invalid CREATE TABLE statement

[Shawn Wagner]

Forgiving, yes, but usually not /that/ forgiving. It's certainly caused
some wasted time going down the wrong path trying to debug an issue.


On Mon, Jun 10, 2019 at 3:39 PM Richard Hipp  wrote:

> On 6/10/19, Shawn Wagner  wrote:
> > Consider:
> >
> > CREATE TABLE a(id INTEGER PRIMARY KEY);
> > CREATE TABLE b(id INTEGER PRIMARY KEY);
> > CREATE TABLE c(id INTEGER PRIMARY KEY, a_id, b_id,
> >FOREIGN KEY (a_id) REFERENCES a(id)
> >FOREIGN KEY (b_id) REFERENCES b(id));
> >
> > Note the lack of comma between the two foreign key constraints in the
> > definition for table c. The syntax diagrams in the documentation indicate
> > that the comma is mandatory, but not only does this not cause a parse
> > error, but both of them are detected:
> >
>
> The parser in SQL is very forgiving.  Does this cause some kind of problem?
>
> --
> 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

[sqlite] Unexpected parsing of an invalid CREATE TABLE statement

[Shawn Wagner]

Consider:

CREATE TABLE a(id INTEGER PRIMARY KEY);
CREATE TABLE b(id INTEGER PRIMARY KEY);
CREATE TABLE c(id INTEGER PRIMARY KEY, a_id, b_id,
   FOREIGN KEY (a_id) REFERENCES a(id)
   FOREIGN KEY (b_id) REFERENCES b(id));

Note the lack of comma between the two foreign key constraints in the
definition for table c. The syntax diagrams in the documentation indicate
that the comma is mandatory, but not only does this not cause a parse
error, but both of them are detected:

sqlite> PRAGMA foreign_key_list(c);
id  seq table   fromto  on_update
on_delete   match
--  --  --  --  --  --
 --  --
0   0   b   b_idid  NO ACTION   NO
ACTION   NONE
1   0   a   a_idid  NO ACTION   NO
ACTION   NONE
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_reset() bug?

[Shawn Wagner]

If you tweak that to include some error checking:

if (sqlite3_bind_int64(stmt, 1, vals[i]) != SQLITE_OK) {
  fprintf(stderr, "BIND ERROR: %s\n", sqlite3_errmsg(db));
}

You'll see output like:

BOUND 4
FOUND 1,4
BIND ERROR: bad parameter or other API misuse
BOUND 5
FOUND 1,4
BIND ERROR: bad parameter or other API misuse
BOUND 6
FOUND 1,4

From https://www.sqlite.org/c3ref/bind_blob.html:

If any of the sqlite3_bind_*() routines are called with a NULL pointer for
the prepared statement or with a prepared statement for which
sqlite3_step() has been called more recently than sqlite3_reset(), then the
call will return SQLITE_MISUSE.

You're running into the latter situation.


On Wed, Jun 5, 2019 at 6:07 AM Josef Barnes  wrote:

> It doesn't look like my attachment worked, so here is the code:
>
> 
>
> #include 
> #include 
>
> int
> main (void)
> {
> inti;
> sqlite3_stmt  *stmt = NULL;
> sqlite3   *db = NULL;
>
> int vals[] = { 4, 5, 6 };
>
> sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READONLY, NULL);
> sqlite3_prepare_v2(db, "SELECT id, value FROM test WHERE value = ?",
> -1, &stmt, NULL);
>
> for (i = 0; i < 3; i++) {
>sqlite3_bind_int64(stmt, 1, vals[i]);
>printf("BOUND %d\n", vals[i]);
>sqlite3_reset(stmt);
>while (sqlite3_step(stmt) == SQLITE_ROW) {
>   printf("FOUND %lld,%lld\n", sqlite3_column_int64(stmt, 0),
> sqlite3_column_int64(stmt, 1));
>}
> }
>
> sqlite3_finalize(stmt);
> sqlite3_close_v2(db);
>
> return 0;
> }
>
> 
>
> On 5/6/19 11:04 pm, Josef Barnes wrote:
> > Hi,
> >
> > We've come across a situation where we think there is a bug in the
> > sqlite3_reset() function. The documentation states:
> >
> > "Any SQL statement variables that had values bound to them using the
> > sqlite3_bind_*() API retain their values"
> >
> > The behaviour we are seeing appears to contradict this statement. I've
> > attached a very simple example of searching for a few rows in a
> > database. To run the example, create a database (test.db) with the
> > following schema:
> >
> > CREATE table test ( id INTEGER PRIMARY KEY, value INTEGER NOT NULL );
> > INSERT INTO test VALUES ( 1, 4 );
> > INSERT INTO test VALUES ( 2, 5 );
> > INSERT INTO test VALUES ( 3, 6 );
> >
> > In the example code, notice that the call to sqlite3_reset() comes
> > after the call to sqlite3_bind_int64(). When running the example, it
> > will return the first row all three times. It seems that the call to
> > sqlite3_reset() actually resets the binded variable to the value it
> > had at the last call to sqlite3_step().
> >
> > Is this a bug? Or is it intended behaviour? If it's intended, I
> > recommend updating the documentation to be clear about this behaviour.
> >
> > Thanks for any insight anyone can provide.
> >
> > Joe
> >
> > ___
> > 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] sqlite-users Digest, Vol 138, Issue 4

[Shawn Wagner]

What is typeof(dancename)? I bet it's not TEXT.

sqlite> CREATE TABLE test(id INTEGER PRIMARY KEY, dancename TEXT);
sqlite> INSERT INTO test(dancename) VALUES ('Waltz'), (cast('Waltz' AS
BLOB));
sqlite> SELECT *, typeof(dancename) FROM test WHERE dancename = 'Waltz';
id  dancename   typeof(dancename)
--  --  -
1   Waltz   text
sqlite> SELECT *, typeof(dancename) FROM test WHERE dancename LIKE 'Waltz';
id  dancename   typeof(dancename)
--  --  -
1   Waltz   text
2   Waltz   blob

On Tue, Jun 4, 2019 at 11:26 AM Doug  wrote:

> Sqlite version is 3.27.1.
>
> I have no indexes defined.
>
> Here is a query I just ran from bash:
>
> select songfile_id,dancename,dancegroupname from songfiletable where
> songfile_id=377;
> 377|Waltz|American Smooth
>
> ... and another:
>
> select songfile_id,dancename,dancegroupname from songfiletable where
> dancename like "Waltz";
> 377|Waltz|American Smooth
> 388|Waltz|American Smooth
> 459|Waltz|American Smooth
> 647|Waltz|American Smooth
> 827|Waltz|American Smooth
> 873|Waltz|American Smooth
> 896|Waltz|American Smooth
> 1156|Waltz|American Smooth
> 1157|Waltz|American Smooth
> 1158|Waltz|American Smooth
> 1159|Waltz|American Smooth
> 1160|Waltz|American Smooth
> 1161|Waltz|American Smooth
> 1162|Waltz|American Smooth
> 1164|Waltz|American Smooth
> 1167|Waltz|American Smooth
>
> ... and yet another:
>
> select songfile_id,dancename,dancegroupname from songfiletable where
> dancename = "Waltz";
> sqlite>
>
> Nothing selected using "=".
>
> Also a hex dump:
>
> select songfile_id,dancename,hex(dancename),dancegroupname from
> songfiletable where songfile_id=377;
> 377|Waltz|57616C747A|American Smooth
>
> Clearly what I think is in the database is there. Is there supposed to be
> a trailing null in the database for text fields that is/is not in play here?
>
> Doug
>
> > -Original Message-
> > From: sqlite-users 
> > On Behalf Of sqlite-users-requ...@mailinglists.sqlite.org
> > Sent: Tuesday, June 04, 2019 5:00 AM
> > To: sqlite-users@mailinglists.sqlite.org
> > Subject: sqlite-users Digest, Vol 138, Issue 4
> >
> > Send sqlite-users mailing list submissions to
> >   sqlite-users@mailinglists.sqlite.org
> >
> > To subscribe or unsubscribe via the World Wide Web, visit
> >   http://mailinglists.sqlite.org/cgi-
> > bin/mailman/listinfo/sqlite-users
> > or, via email, send a message with subject or body 'help' to
> >   sqlite-users-requ...@mailinglists.sqlite.org
> >
> > You can reach the person managing the list at
> >   sqlite-users-ow...@mailinglists.sqlite.org
> >
> > When replying, please edit your Subject line so it is more
> > specific
> > than "Re: Contents of sqlite-users digest..."
> >
> >
> > Today's Topics:
> >
> >1. Re: SQLite build on Risc-V (Carlos Eduardo de Paula)
> >2. select * where abc like "xxx" works,...where abc='xxx'
> > fails
> >   (Doug)
> >3. Re: select * where abc like "xxx" works, ...where abc='xxx'
> >   fails (Simon Slavin)
> >4. Re: select * where abc like "xxx" works,...where abc='xxx'
> >   fails (Keith Medcalf)
> >5. Re: Bug in sqlite3.c (bhandari_nikhil)
> >
> >
> > --
> > 
> >
> > Message: 1
> > Date: Sun, 2 Jun 2019 11:15:35 -0300
> > From: Carlos Eduardo de Paula 
> > To: dcla...@blastwave.org,  "sqlite-users@mailinglists.sqlite.org"
> >   
> > Subject: Re: [sqlite] SQLite build on Risc-V
> > Message-ID:
> >> il.com>
> > Content-Type: text/plain; charset="UTF-8"
> >
> > I'm on a SiFive Unleashed board running Debian Linux on Kernel
> > 4.19.
> > --
> > Sent from IPhone
> >
> >
> > --
> >
> > Message: 2
> > Date: Mon, 3 Jun 2019 16:46:47 -0700
> > From: "Doug" 
> > To: 
> > Subject: [sqlite] select * where abc like "xxx" works,
> >   ...where
> >   abc='xxx' fails
> > Message-ID: <04fc01d51a66$9c94c700$d5be5500$@comcast.net>
> > Content-Type: text/plain; charset="us-ascii"
> >
> > I am using Sqlite under QT 5.12.0.
> >
> >
> >
> > Why does the "=" query fail and the "like" query work? There are
> > no
> > wildcards involved.
> >
> >
> >
> > I create the table this way:
> >
> >
> >
> > QString sqlcreate = QLatin1String(
> >
> >   "CREATE TABLE songfiletable ("
> >
> > "songfile_id INTEGER PRIMARY KEY,"
> >
> > "dancename TEXT"
> >
> >   ");"  );
> >
> >
> >
> > I populated the table this way with
> > dancename=QLatin1String("Waltz"):
> >
> >
> >
> > QString sqlinsert = QLatin1String(
> >
> > "INSERT INTO songfiletable ("
> >
> > "dancename"
> >
> > ") VALUES(?);");
> >
> >   queryinsert.prepare(sqlinsert);
> >
> >   queryinsert.addBindValue(dancename.toUtf8());
> >
> >
> >
> > The query that works for dancename="Waltz" is this (with 'like'):
> >
> > QByteArray dn = d->dance_name.toUtf8();
> >
> > QByteArray filt

Re: [sqlite] Have SQLite handle values of my own type

[Shawn Wagner]

Don't forget IPv6 addresses.

On Thu, May 23, 2019, 3:37 AM Simon Slavin  wrote:

> On 23 May 2019, at 3:55am, Keith Medcalf  wrote:
>
> > Technically, COLLATE only works on TEXT.  Most people declare their own
> types as binary blobs and the programmer has to keep track of what is in
> there and how to work with it.
>
> So it would seem that rather than define a function which turns a value of
> my type into a BLOB, it would be better to create one which turns it into
> some sort of normalised text form.  Then I can create whatever COLLATEs I
> need to do my specialised sorting and handling.
>
> IP addresses are easy: convert to the form aaa.bbb.ccc.ddd where each of
> the four parts is always three digits long.
>
> 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

Re: [sqlite] CSV import does not handle fields with a comma surrounded by double

[Shawn Wagner]

Still applies; just badly worded.

On Tue, May 21, 2019 at 8:47 AM Dominique Devienne 
wrote:

> On Tue, May 21, 2019 at 5:36 PM Shawn Wagner 
> wrote:
>
> > I have a handy script that can handle that sort of input with extra
> spaces
> > (With the --strip option), and other stuff that csv .import doesn't
> always
> > deal well with:
> >
> > https://github.com/shawnw/useful_sqlite_extensions/tree/master/tools
>
>
> Doc says from *unquoted fields*.
> Here it's about a space between the comma and the leading double-quote of a
> *quoted field*.
> So still applies? --DD
> ___
> 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] CSV import does not handle fields with a comma surrounded by double

[Shawn Wagner]

I have a handy script that can handle that sort of input with extra spaces
(With the --strip option), and other stuff that csv .import doesn't always
deal well with:

https://github.com/shawnw/useful_sqlite_extensions/tree/master/tools

On Tue, May 21, 2019 at 6:14 AM Faheem Mitha  wrote:

>
> Hi,
>
> I'm seeing the same bug reported here, in an issue from 2009, supposedly
> fixed in 2014.
>
> https://www.sqlite.org/src/tktview?name=c25aab7e7e with the title:
> The ".import" command does not parse CSV correctly.
>
> I'm using Debian buster, with SQLite version 3.27.2-2.
>
> Here is a simple reproduction recipe. Consider the following simple CSV
> file.
>
> ###
> comma.csv
> ###
> somestuff, "some,stuff"
>
> Then one just needs to do
>
> sqlite3 comma.db 'create table comma(foo, bar);' '.mode csv' '.import
> comma.csv comma' '.exit'
>
> Which gives the error message:
>
> expected 2 columns but found 3 - extras ignored.
>
> As mentioned in that ticket, this is per the
> https://tools.ietf.org/html/rfc4180 standard.
>
> Pandas handles this case fine. I've not checked R.
>
> Should I attempt to reopen that ticket?
>
> Regards, Faheem
> ___
> 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] .import into databases other than main doesn't work

[Shawn Wagner]

The .import command doesn't play well with attempts to use tables in
schemas other than main:

sqlite> .mode csv
sqlite> .import test.csv temp.test
Error: no such table: temp.test

It creates the table if needed, but fails on the insert part because it
tries to execute `INSERT INTO "temp.test" ` instead of `INSERT INTO
"temp"."test" ...`.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Improving CSV import

[Shawn Wagner]

Late follow up:

> .import "tail -n +2 foo.csv |" mytable

Found out today that this works (Though the pipe character has to be the
first character, not the last) and apparently has for years, though I can't
find it documented anywhere.

On Thu, Mar 21, 2019 at 4:06 PM Shawn Wagner 
wrote:

> Importing a CSV file in the command line shell has two different
> behaviors: if the table to import to doesn't exist, treat the first row of
> the file as a header with column names to use when creating the table. If
> the table does exist, treat all rows as data.
>
> A way to skip a header row when the table exists would be useful.
> Sometimes it's awkward to make a copy of a file minus the first line before
> doing the import.
>
> Alternatively, allow .import to pipe from the output of a command, not
> just a file:
>
> .import "tail -n +2 foo.csv |" mytable
>
> More work to implement, but a lot more flexible, and it would match the
> behavior of .output and .once. If the devs are willing to accept user
> contributed patches to the shell, I'd happy to look into implementing that.
>
> Also something I've run into that would be useful: a way to strip leading
> and trailing whitespace from unquoted fields before inserting them.
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Feature request: FILTER on aggregate functions

[Shawn Wagner]

I just found out that postgres (And possibly others?) supports FILTER on
aggregate functions in general, not just when they're used as a window
function.

Trivial example:

SELECT count(*), count(*) FILTER (WHERE amount > 100) FROM blah

which is a lot cleaner than

SELECT count(*), sum(CASE WHEN amount > 100 THEN 1 ELSE 0 END) FROM blah

Would be nice to have someday...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Use cases for sqlite3_value_frombind()?

[Shawn Wagner]

The commit that added it (
https://www3.sqlite.org/cgi/src/info/b3f2c3205a28dc21) says that it's used
to improve fts3_tokenizer(), so maybe look at the diffs of that function to
see how it's used there to get an idea for what it's intended for?

On Wed, Apr 17, 2019, 9:22 AM Jens Alfke  wrote:

> The new sqlite3_value_frombind() function sounds intriguing — "True if
> value originated from a bound parameter
>  — but I’m drawing a blank thinking of use cases for it. Optimizations?
> Security? What was the rationale for adding it?
>
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_db_config() documentation issue

[Shawn Wagner]

The sqlite3_db_config() function is a variable argument one, taking a
database handle, a config option, and then what the documentation says
is 'Subsequent
arguments vary depending on the configuration verb.'.

Aside from SQLITE_DBCONFIG_MAINDBNAME and SQLITE_DBCONFIG_LOOKASIDE, they
all require an int and an int* for those arguments. The descriptions
of SQLITE_DBCONFIG_RESET_DATABASE
and SQLITE_DBCONFIG_DEFENSIVE don't mention the need for the second
argument (As I just found out the hard way with the latter option).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting the same column multiple times

[Shawn Wagner]

On Sun, Apr 14, 2019 at 1:16 AM Luuk  wrote:

>
>
> Because, i do think, that it would never be possible to specify more
> than the number of columns in an insert statement?
>
>
The original issue was with some java/android sqlite binding that has a
method that builds an insert statement on the fly given lists of column
names and corresponding values being passed ones that had duplicates. It
might not be something any normal person does with a hand-written
statement, but automatically generated ones (combined with someone making
silly mistakes) are a different story.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Inserting the same column multiple times

[Shawn Wagner]

Discovered this tonight answering a question on stack overflow:

sqlite> create table foo(a, b);
sqlite> insert into foo(a,b,a,b) values(1,2,3,4);
sqlite> select * from foo;
a   b
--  --
1   2

Inserting a column multiple times only uses the first corresponding value.
I don't see this documented anywhere.

By contract, a single UPDATE of the same column multiple times uses the
last one and ignores the rest:

sqlite> update foo set a=3, a=4;
sqlite> select * from foo;
a   b
--  --
4   2

And that is documented.

The inconsistency is annoying, but changing how either one works will
doubtless break somebody's code. Maybe clarify INSERT's behavior in its
documentation? Logging a warning in the case of a column being used
multiple times might be nice too.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexpected or wrong result and no warning/error, bug?

[Shawn Wagner]

From the documentation (https://www.sqlite.org/lang_update.html)

If a single column-name appears more than once in the list of assignment
expressions, all but the rightmost occurrence is ignored.


On Fri, Apr 12, 2019, 9:00 AM Tony Papadimitriou  wrote:

> create table t(s varchar(5));
>
> insert into t values('US'),('USA');
>
> update t set s = replace(s, 'USA', '___'),
>  s = replace(s,'US','USA'),
>  s = replace(s,'___','USA');
>
> select * from t;
>
> -- Expected answer:
> -- USA
> -- USA
> --
> -- MySQL gets it right
> -- Postgres prints error about setting the same column multiple times
> -- SQLite3 (latest and older) no changes or wrong result but no
> error/warning
> ___
> 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] Help with sqlite3_value_text

[Shawn Wagner]

Welcome to the wonderful world of multibyte encodings, and Unicode in
particular.

Unless you're looking for an ASCII character, you're looking for a
substring, not a character. And you're really looking for what's called a
codepoint (The entire concept of character gets kind of fuzzy with
Unicode). If you're not careful, looking for 'a' (U+0061 LATIN SMALL LETTER
A) will match the start of á, which is actually a two codepoint grapheme
(U+0061 and U+0301 COMBINING ACUTE ACCENT) that renders as a single entity.
And if you're okay with matching that, what about á (U+00E1 LATIN SMALL
LETTER A WITH ACUTE), the single codepoint composed version?

Unicode is hard. There are libraries like ICU and libunistring which help a
bit. I have a bunch of sqlite extensions at
https://github.com/shawnw/useful_sqlite_extensions (That I really need to
polish up for an actual release) including a string library that expands a
lot on the build in ICU extension to make working with graphemes and
unicode in general in sqlite a lot easier.


On Fri, Apr 12, 2019 at 7:51 AM x  wrote:

> I’m still confused by utf strings. For simplicity, suppose I set up an
> sqlite function that takes a single string parameter and I want to scan the
> string to count the number of occurrences of a certain character . If I
> knew the string was made up entirely of ascii chars I’d do this
>
> char *c = &sqlite3_value_text(0)[0];
> int count=0;
> while (*c) if (*c++ == SearchChar) count++;
>
> How do I do the same thing if the string param is a utf-8 or utf-16 string
> and the SearchChar is a Unicode character?
>
> I’m confused by the fact that Unicode characters are not a fixed number of
> bytes so if I do this e.g.
>
> wchar_t *c = (wchar_t*) sqlite3_value_text(0);
>
> does this mean a complete temporary copy of the value of
> sqlite3_value_text(0) has to be constructed by the compiler such that all
> characters of the newly constructed string are fixed width? If so, I’m just
> wanting to check if there’s a way  of avoiding this overhead.
>
> ___
> 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] Does sqlite3 support alter table add primary key?

[Shawn Wagner]

I don't think I've ever seen a decent sqlite page on tutorialspoint. Some,
like this one, appear to have been written by people who have never
actually used it.

On Wed, Apr 10, 2019, 5:11 AM Peng Yu  wrote:

> Hi,
>
> I got the following error. Does sqlite3 support alter table add primary
> key?
>
> sqlite> alter table mytab add primary key (h1);
> Error: near "primary": syntax error
>
> https://www.tutorialspoint.com/sqlite/sqlite_primary_key.htm
>
> --
> Regards,
> Peng
> ___
> 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] Built in and enabled Virtual Table extensions in SQLite CLI?

[Shawn Wagner]

Interesting. Must be that the pragmas that take arguments need parens when
used in table valued function form. I've never tried without them when
using a pragma that way.

Anyways, the sqlite3 shell is built with that introspection option, at
least on Linux (tested with one built from source myself without any
special configure arguments, and the stock Ubuntu one). I don't have a
Windows version handy to test right now. It's apparently different for some
reason?

On Wed, Apr 10, 2019, 3:46 AM Graham Holden  wrote:

> Wednesday, April 10, 2019, 11:16:07 AM, Shawn Wagner <
> shawnw.mob...@gmail.com> wrote:
>
> > It should be pragma_function_list(). Note the parentheses.
>
> That doesn't make a difference if the shell hasn't been built with
> -DSQLITE_INTROSPECTION_PRAGMAS, and they don't appear necessary for a
> pragma that IS built-in (such as "pragma_database_list"):
>
> SQLite version 3.27.2 2019-02-25 16:06:06
> Enter ".help" for usage hints.
> sqlite> select * from pragma_database_list;
> 0|main|X:\V1LS\z
> sqlite> select * from pragma_database_list();
> 0|main|X:\V1LS\z
> sqlite> select * from pragma_function_list ;
> Error: no such table: pragma_function_list
> sqlite> select * from pragma_function_list() ;
> Error: no such table: pragma_function_list
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

[Shawn Wagner]

It should be pragma_function_list(). Note the parentheses.

On Wed, Apr 10, 2019, 2:29 AM Luuk  wrote:

>
> On 10-4-2019 10:28, Kees Nuyt wrote:
> > On Wed, 10 Apr 2019 13:17:23 +1000, John wrote:
> >
> >> I have not used extensions before. I understand that some are included
> >> in the amalgamation source file and that some of these are enabled by
> >> default. So, which ones are built-in and which of those are enabled in
> >> the standard downloadable Win32 SQLite CLI?
> > sqlite> .mode column
> > sqlite> .header on
> > sqlite> .width 28 8
> > sqlite> select * from pragma_function_list;
>
> D:\>sqlite3
> SQLite version 3.27.2 2019-02-25 16:06:06
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .mode column
> sqlite> .header on
> sqlite> .width 28 8
> sqlite> select * from pragma_function_list;
> Error: no such table: pragma_function_list
> sqlite>
>
> This is "the standard downloadable Win32 SQLite CLI?"
>
> >> If an extension is built-in and enabled, what do I need to do to use it.
> >> The instructions seem to be for the case where an extension is built as
> >> an external library (.dll) to be loaded by eg. .load ./csv where csv
> >> would be csv.dll in the current directory. If the csv extension was
> >> built-in, would I still need to load it to activate it?
> > I don't think so.
> >
> ___
> 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] Can I get help with db design for SQLite use?

[Shawn Wagner]

Yay Perl! My favorite language. DBD::SQLite is definitely one of the
better, fuller featured sqlite bindings out there. Though Tom is using
perl6, not perl5. I have no idea how its version compares.

(At the risk of going off topic, File::Slurp has issues. File::Slurper is a
better alternative.)

On Wed, Apr 3, 2019, 12:08 AM Rob Willett 
wrote:

> Tom,
>
> We use the Perl DB::SQLite module. It works very well and I cannot
> recall a single issue with it in the last four years. There's not as
> much support for Perl on this mailing list as it's not as popular, but
> most issues you will probably encounter will be at the design level
> rather than at the CPAN module level. Our working assumption is that if
> the DBI module looks like it has an issue, it doesn't and it's our code.
> Pretty much the same as for SQLite. Whilst I know that SQLite has had
> bugs, the chances of us finding them is minimal, so we assume it's our
> code again.
>
> However I can say that we use Perl for all our system code and use
> SQLite within it and its fast and easy.
>
> If you're using Perl, I would also recommend the following other
> modules, this is a direct pull from our code. I've removed our code
> specific modules.
>
> ```
> #!/usr/bin/perl -w
>
> use strict;
> use warnings;
>
> use Switch;
> use DBI;
> use JSON;
> use Getopt::Long;  <-- Easiest way to get command line args in and
> processed.
> use Data::Dumper;  <-- Utterly essential, don't leave home without
> it.
> use Mojolicious::Lite; <-- Only need if you making a REST based server
> use Mojo::Parameters;  <-- Only need if you making a REST based server
> use Mojo::URL; <-- Only need if you making a REST based server
> use Mojo::Log; <-- Only need if you making a REST based server
> use REST::Client; <-- More rest based stuff
> use Sereal::Encoder qw(encode_sereal sereal_encode_with_object);
> use Sereal::Decoder qw(decode_sereal sereal_decode_with_object
> scalar_looks_like_sereal);
> use DateTime;  <-- Manage date and time properly.
> use Net::Curl::Easy;
> use Crypt::Random qw( makerandom );
> use Log::Log4perl qw(get_logger :levels);
> use File::Path qw(make_path); <-- Quick and easy way to make paths and
> directories.
> use Net::Address::IP::Local;  <-- Easy way to manipulate IP addresses.
> use File::Slurp; <-- Quick and easy way to read and write files.
> use Clone 'clone'; <-- You'll use a lot of objects and structures. Copy
> them properly and fast.
>
> ```
>
> We use Log4Perl a lot as it's easy to get things setup and then you can
> modify one log file and get easy changes. Also we use Mojolicious for
> all the REST code wrapping.
>
> These are tried and tested CPAN modules that we know just work for us
> and are a standard part of every new build. The most awkward one if the
> curl one, there seems to be a lot of versions of the Curl::Easy stuff
> which simply don't work on Mac OS X (or other OS's).
>
> Just my 2p worth.
>
> Rob
>
> On 3 Apr 2019, at 0:21, Tom Browder wrote:
>
> > On Tue, Apr 2, 2019 at 17:30 am...@juno.com  wrote:
> >
> >> You might want to import everything into SQLite Studio or SQLite
> >> Suite I
> >> forget the exact name)--a freebie on the internet. I found it worked
> >> for
> >> me. In order to help others, I would appreciate it if you tell us on
> >> this
> >> usergroup how you made out. May it work for you. Peace! Alex
> >
> >
> > Thanks for the idea, Alex.
> >
> > I'm doing all programmatically at the moment (using a Perl 6 module:
> > DB::SQLite), but using one of those tools you mentioned would help in
> > design for sure!
> >
> > -Tom
> > ___
> > 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] read-only database in WAL mode and temporary files

[Shawn Wagner]

Even a read only database needs to create the wal journal if it doesn't
exist at the moment for a database that uses that mode:
https://www.sqlite.org/wal.html#read_only_databases

You might look into the immutable option mentioned there and see if it's
appropriate for your needs.


On Sun, Mar 24, 2019, 2:33 AM Thomas Kurz  wrote:

> When I open a database in read-only mode (?mode=ro), I observe that the
> WAL and SHM temporary files are created anyway. Is there any possibility to
> prevent the creation of these files? Aren't they useless?
>
> Even worse (using sqlite3.exe version 3.27.1):
>
> .open test.db
> pragma journal_mode=wal;
> create table a (b integer);
> insert into a (1);
> .quit
>
> This creates database with WAL and SHM files; those files are correctly
> removed when exiting.
>
> .open --readonly test.db
> select * from a;   <--- creates WAL and SHM -- why?
> .quit
>
> Now, WAL and SHM files aren't deleted anymore.
>
> Proposal: Neither WAL nor SHM should be created at all when opening a
> WAL-mode database in read-only mode.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Incorrect result from INNER JOIN when column has a value of "WHITE"

[Shawn Wagner]

Use single quotes, not double quotes, for strings. Double quotes are for
identifiers. And you have a column named white...

On Fri, Mar 22, 2019, 8:11 AM Steve Horvath 
wrote:

> Hi,
>
> I found an issue with SQLite 3.27.2 and is also existent in 3.20.1.
>
> Of the four queries below, the second query should have returned the same
> result set as the first query. As a side note, I also tried creating the
> tables with no primary keys and got the same results. I also tried using
> the "ON" syntax and got the same results.
>
> sqlite3.exe bugtest
> SQLite version 3.27.2 2019-02-25 16:06:06
> Enter ".help" for usage hints.
> sqlite> create table competitors (compid INTEGER PRIMARY KEY, last TEXT,
> first TEXT);
> sqlite> create table matches (category INTEGER, number INTEGER, blue
> INTEGER, white INTEGER, PRIMARY KEY(category,number));
> sqlite> insert into matches values(10032,2,1109,1197);
> sqlite> insert into competitors values (1109,"SERHAN", "James");
> sqlite> insert into competitors values (1197,"WHITE","Justin");
> sqlite> .headers on
> sqlite> select * from matches;
> category|number|blue|white
> 10032|2|1109|1197
> sqlite> select * from competitors;
> compid|last|first
> 1109|SERHAN|James
> 1197|WHITE|Justin
>
> sqlite> select competitors.* from matches INNER JOIN competitors WHERE
> matches.white = competitors.compid and matches.category=10032 and
> matches.number=2 and competitors.first="Justin";
> compid|last|first
> 1197|WHITE|Justin
>
> sqlite> select competitors.* from matches INNER JOIN competitors
> WHERE matches.white = competitors.compid and matches.category=10032 and
> matches.number=2 and competitors.last="WHITE";
>
> sqlite> select competitors.* from matches INNER JOIN competitors
> WHERE matches.blue = competitors.compid and matches.category=10032 and
> matches.number=2 and competitors.first="James";
> compid|last|first
> 1109|SERHAN|James
>
> sqlite> select competitors.* from matches INNER JOIN competitors WHERE
> matches.blue = competitors.compid and matches.category=10032 and
> matches.number=2 and competitors.last="SERHAN";
> compid|last|first
> 1109|SERHAN|James
>
> sqlite>.quit
>
> Out of curiosity, I changed the last name of "WHITE" to "WHTE" and queries
> now work!?!?
>
> sqlite3.exe bugtest
> SQLite version 3.27.2 2019-02-25 16:06:06
> Enter ".help" for usage hints.
> sqlite> update competitors set last="WHTE" where compid=1197;
> sqlite> select competitors.* from matches INNER JOIN competitors WHERE
> matches.white = competitors.compid and matches.category=10032 and
> matches.number=2 and competitors.last="WHTE";
> 1197|WHTE|Justin
> sqlite> select competitors.* from matches INNER JOIN competitors WHERE
> matches.white = competitors.compid and matches.category=10032 and
> matches.number=2 and competitors.first="Justin";
> 1197|WHTE|Justin
> sqlite> select competitors.* from matches INNER JOIN competitors ON
> matches.blue = competitors.compid where matches.category=10032 and
> matches.number=2 and competitors.first="James";
> 1109|SERHAN|James
> sqlite> select competitors.* from matches INNER JOIN competitors ON
> matches.blue = competitors.compid where matches.category=10032 and
> matches.number=2 and competitors.last="SERHAN";
> 1109|SERHAN|James
> sqlite> .quit
>
> I hope you have enough details to troubleshoot!
>
> Thanks in advance,
> STeve Horvath
> ___
> 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] Improving CSV import

[Shawn Wagner]

I thought about suggesting that, but I can see it breaking backwards
compatibility with existing scripts. I set .header on in my ~/.sqliterc for
example, and have things that don't change it before importing csv files
and would thus miss a row.

(I also have a handy perl script that does all this stuff and more, but
it's nice to keep dependencies to a minimum.)

On Thu, Mar 21, 2019 at 6:48 PM D Burgess  wrote:

> Agree with all that.
>
> > A way to skip a header row when the table exists would be useful.
> >
> How about
> > .header on/off
> ___
> 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] Improving CSV import

[Shawn Wagner]

Importing a CSV file in the command line shell has two different behaviors:
if the table to import to doesn't exist, treat the first row of the file as
a header with column names to use when creating the table. If the table
does exist, treat all rows as data.

A way to skip a header row when the table exists would be useful. Sometimes
it's awkward to make a copy of a file minus the first line before doing the
import.

Alternatively, allow .import to pipe from the output of a command, not just
a file:

.import "tail -n +2 foo.csv |" mytable

More work to implement, but a lot more flexible, and it would match the
behavior of .output and .once. If the devs are willing to accept user
contributed patches to the shell, I'd happy to look into implementing that.

Also something I've run into that would be useful: a way to strip leading
and trailing whitespace from unquoted fields before inserting them.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .import fails CHECK constraint on valid data

[Shawn Wagner]

A manual INSERT demonstrates the same behavior, actually. Using your Tc
table:

sqlite> insert into Tc values ('12');
Error: CHECK constraint failed: Tc

The thing about .import is that, instead of guessing what type each value
it reads is, they're all just bound to an insert statement as strings. When
the row is actually stored in the table, those strings are converted to
numeric types if the relevant columns have the appropriate affinity and it
can be done losslessly. Details:
https://www.sqlite.org/datatype3.html#type_affinity

For good or bad, check constraints appear to be evaluated before this
conversion.

On Tue, Mar 19, 2019 at 1:43 PM James K. Lowden 
wrote:

> On Sun, 10 Mar 2019 17:04:46 -0400
> "James K. Lowden"  wrote:
>
> > Why does the .import command cause the CHECK constraint to fail, when
> > an ordinary INSERT does not?
>
> On Sun, 10 Mar 2019 14:12:33 -0700
> Shawn Wagner  wrote:
>
> > The check constraint is probably being evaluated (with t as a string)
> > before any type conversion to match the column affinity is done.
>
> Does anyone have a better answer?  Isn't .import supposed to work like
> INSERT?  If it doesn't, CHECK constraints for type safety are useless
> for tables that are loaded from files.
>
> --jkl
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backing up a SQLite database without the CLI

[Shawn Wagner]

If the php sqlite bindings are incomplete and don't support the backup
functions, write a small program in C that uses them to copy a database,
and execute that from the php code?

On Mon, Mar 18, 2019, 8:24 AM Jonathan Moules 
wrote:

> Hi Simon,
>
> Thanks for your thoughts. Sorry, I should have been clearer: I have no
> way of knowing if there are other open connections to the file - there
> may be as it's a web-application. So I'll assume there are connections.
>
> At this point I'm starting to think that the best option is to create a
> new database with the requisite structure and copy the data across via
> an ATTACH (there are only two tables and one will almost always be empty
> at this point).
>
> Any other thoughts welcome though!
> Cheers,
> Jonathan
>
> On 2019-03-18 13:37, Simon Slavin wrote:
> > On 18 Mar 2019, at 1:10pm, Jonathan Moules 
> wrote:
> >
> >> I was wondering if there was a good way of backing up an SQLite
> database if you do *not* have access to the SQLite command line tool (which
> I know has .backup - https://stackoverflow.com/a/25684912). [snip]
> >> I've considered simply running "PRAGMA wal_checkpointer;" and then
> copying the file immediately after that, but that still seems prone to
> error.
> > Ideally, rather than force a WAL checkpoint, close the file, make the
> copy, then open it again.  This does not take significantly more time, and
> it ensures that you will copy the right thing no matter what caching and
> optimization your tools are trying to do.
> >
> > In more general terms ...
> >
> > Are you trying to backup while the database is being modified using
> SQLite function calls ?
> >
> > If not, then the data is just a single file.  Assuming all programs
> using SQLite calls closed their connections properly, just copy the file
> using any file copy commands, or file copy primatives in your favourite
> programming language.  In PHP I'd use the built-in copy command:
> >
> > 
> >
> > There may be a journal file there and you can copy that too, but just
> the database file is enough for a backup for emergency purposes.
> >
> > If you're trying to copy a file while connections still have it open
> then you should use SQLite API calls to do it.  The obvious ones are in the
> SQLite Online Backup API, which is the set of calls underlying the
> '.backup' command you mentioned.  You can find documentation for this here:
> >
> > 
> >
> > Unfortunately I don't think the PHP sqlite3 tools give access to this
> API.
> >
> > Hope that helps.  Don't hesitate to get back to us if we can help.
> > ___
> > 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] difference between *.db *.db3 *.sqlie2 *.slite3

[Shawn Wagner]

A sqlite database is just a file. You can name it whatever you want. People
who like file extensions often use such ones (I assume you meant .sqlite2
and .sqlite3? The first would likely be used to indicate a sqlite 2
database, assuming you can find any software that still uses it...)

On Fri, Mar 15, 2019, 3:29 AM Mohsen Pahlevanzadeh 
wrote:

> What differ between *.db *.db3 *.sqlie2 *.slite3 files?
> ___
> 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] [EXTERNAL] Missing docs

[Shawn Wagner]

Or just boring old autocorrect shenanigans...

On Fri, Mar 15, 2019, 12:20 AM Hick Gunter  wrote:

> Which release of sqlite are you referrring to? I am unaware of any
> function call with a prefix of sqldark in release 3.24? Maybe this is an
> addon/wrapper/whatever from the Sith?
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Joshua Thomas Wise
> Gesendet: Donnerstag, 14. März 2019 22:40
> An: SQLite mailing list 
> Betreff: [EXTERNAL] [sqlite] Missing docs
>
> Nowhere in the current documentation does it mention the existence of the
> SQLITE_ENABLE_NORMALIZE compile-time option, and nowhere does it mention
> that sqldark_source_normalized() requires that option to be present.
>
> The docs should probably mention this.
>
> - Josh
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick | Software Engineer | Scientific Games International GmbH |
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> 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] .import fails CHECK constraint on valid data

[Shawn Wagner]

The check constraint is probably being evaluated (with t as a string)
before any type conversion to match the column affinity is done.

On Sun, Mar 10, 2019, 2:05 PM James K. Lowden 
wrote:

> $ sqlite3 db "create table T (t integer not null);"
> $ sqlite3 db "create table Tc (t integer not null
> check(typeof(t) = 'integer'));"
> $ echo 1 > dat
> $ sqlite3 db ".import 'dat' T"
> $ sqlite3 db ".import 'dat' Tc"
> dat:1: INSERT failed: CHECK constraint failed: Tc
> $ sqlite3 db "insert into Tc select * from T;"
> $ sqlite3 db "select * from Tc"
> 1
> $ sqlite3 db "select typeof(t) from T"
> integer
> $ sqlite3 db "select typeof(t) from Tc"
> integer
>
> Why does the .import command cause the CHECK constraint to fail, when
> an ordinary INSERT does not?
>
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to backup a memory database to a memory stream?

[Shawn Wagner]

See https://www.sqlite.org/c3ref/serialize.html

(You might have to compile a custom version of sqlite if the one you're
using wasn't built with support for serialization enabled)


On Thu, Feb 21, 2019, 10:58 PM heribert  wrote:

> Is there any way to backup a memory database directly to a memory stream?
>
> In my case i have to backup a small memory database directly into a byte
> array, without using any os based temp file.
>
> ___
> 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] LIKE operator and collations

[Shawn Wagner]

Look into using the ICU extension. If you're compiling sqlite yourself,
just define SQLITE_ENABLE_ICU to 1 (And link with the ICU libraries),
otherwise you'll have to grab the source and compile it as a loadable
module.

https://www3.sqlite.org/cgi/src/dir?ci=03c4f00317233a34&name=ext/icu for
details. (Is there a way to link to the latest version of a file in the
repository and not a particular commit?)

On Fri, Feb 15, 2019 at 6:06 AM Aydin Ozgur Yagmur 
wrote:

> Hello,
>
> I want to use custom collations for "like" and "not equals" queries.
> *select * from tbl_internal where col_internal like 'ç%' collate
> TURKISH_CI;*
>
> it is ok for "equals" operator.
> *select * from tbl_internal where col_internal = 'çç' collate TURKISH_CI;*
>
> but not ok, for "like" and "not equals" operators
>
> How can i make a search like these? Could you give me any clues?
>
> Thanks for advance,
> ___
> 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] Find the user in the sorted table and retrieve a certain number of users above and below him

[Shawn Wagner]

Are you using a new enough version of sqlite (3.25 or better) that you can
use window functions?

On Thu, Feb 14, 2019, 7:29 AM Dobrila Šunde  Hi!
>
> So I have been trying to write a query that needs to be really fast (under
> 1 ms would be ideal) to a very large database (around 20 000 rows and 20
> columns). The problem is that I need to find the user inside the sorted
> database and retrieve a number of users better and worse than him as to
> make a ranking table and show the user in the middle of it (the ranking
> table consists of 7 players).
> Currently this query takes about 3.5 ms and has a lot of issues. Some of
> them include the fact that I can't retrieve the users that have the same
> amount of points as my user. I also need to know the exact rank of the
> person inside the whole database that's why I need the count (*). It would
> be ideal to have the ranks of every player in the database but I couldn't
> find a way that isn't ridiculously slow. Is there any fast way I can
> retrieve 3 players better or equal to my player with points and worse or
> equal to my player with points so there aren't any duplicates?
>
> select player.user_profile_id, (select count(*) + 1 from event_stats
> player2 where player2.points > player.points order by player2.points desc)
> as 'rank', 'player' as 'stats_group', player.name, player.points from
> event_stats player where player.user_profile_id=202
> union all
> SELECT *
> FROM (
>   select stats.user_profile_id, 0 as 'rank', 'ahead' as 'stats_group',
> stats.name, stats.points from event_stats user
>   left join event_stats stats on stats.points > user.points
>   where user.user_profile_id=202 order by stats.points ASC limit 3
> ) q1
> union all
> SELECT *
> FROM (
>   select stats.user_profile_id, 0 as 'rank', 'below' as 'stats_group',
> stats.name, stats.fame from event_stats user
>   left join event_stats stats on stats.points < user.points
>   where user.user_profile_id=202 order by stats.points DESC limit 3
> ) q2 order by points desc
>
> Thanks for your help!
> ___
> 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] Tips for index creation

[Shawn Wagner]

Some useful reading:

https://use-the-index-luke.com/

https://www.sqlite.org/queryplanner.html (and the pages it links to)

There's also the .expert command in the sqlite shell:

sqlite> .expert
sqlite> SELECT ... FROM ...;

will suggest indexes that will benefit a particular query.


On Wed, Feb 13, 2019, 4:39 AM Thomas Kurz  Hello,
>
> I apologize right at the beginning, because this is a real noob question.
> But I don't have much experience with performance optimization and indexes,
> so I'm hoping for some useful hints what indexes to create.
>
> I have queries like this:
>
> SELECT parameter, value FROM metadata WHERE id1=a AND id2 IS NULL and id3
> IS NULL
> -or-
> SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3 IS NULL
> -or-
> SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3=c
>
> Do I have to create three indexes, one for each column id1, id2, id3? Or
> is it better or even necessary to create only one index covering all three
> id columns?
>
> Do I need indexes for the parameter and value columns as well, and under
> which circumstances? (Only if I want to use a SELECT ... WHERE parameter =
> xy?)
>
> Kind regards,
> Thomas
>
> ___
> 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] Why am I not getting anything?

[Shawn Wagner]

SELECT * FROM pragma_foreign_key_list('temp1');

The table name needs to be a string for the pragma_foreign_key_list virtual
table. (It's not for the corresponding pragma statement.)

On Sat, Feb 9, 2019, 11:31 PM Igor Korot  Hi, ALL,
>
> [code]
> sqlite> SELECT * FROM sqlite_master WHERE name LIKE '%temp%';
> type|name|tbl_name|rootpage|sql
> table|temp|temp|40|CREATE TABLE temp(id INTEGER PRIMARY KEY, name
> VARCHAR(200))
> table|temp1|temp1|41|CREATE TABLE temp1(myid INTEGER PRIMARY KEY, id
> INTEGER, my
> name VARCHAR(200), CONSTRAINT temp1tempid FOREIGN KEY(id) REFERENCES
> temp(id))
> table|temp2|temp2|43|CREATE TABLE temp2(id INTEGER, name TEXT, value
> NUMERIC(15,
>  2))
> table|temp3|temp3|1268|CREATE TABLE temp3(id INTEGER PRIMARY KEY, data
> TEXT)
> table|temp4|temp4|1269|CREATE TABLE temp4(myid INTEGER PRIMARY KEY, id
> INTEGER,
> myname VARCHAR(200), FOREIGN KEY(id) REFERENCES temp(id))
> sqlite>
> sqlite>
> sqlite>
> sqlite> SELECT * FROM pragma_foreign_key_list(temp1);
> Error: no such column: temp1
> sqlite>
> [/code]
>
> I should get an information about the foreign key that exists for the
> table temp1,
> but I have an error.
>
> And when I execute the PRAGMA directly, I do get the results:
>
> [code]
> sqlite> PRAGMA foreign_key_list(temp1);
> id|seq|table|from|to|on_update|on_delete|match
> 0|0|temp|id|id|NO ACTION|NO ACTION|NONE
> [/code]
>
> [code]
> sqlite> .version
> SQLite 3.24.0 2018-06-04 19:24:41
> c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366
> d5221ac8fb199a87ca
> zlib version 1.2.11
> gcc-5.2.0
> sqlite>
> [/code]
>
> This is on Windows 8.1
>
> Am I doing something wrong?
> ___
> 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] My TimeStamp field keeps populating though I don't write anything to it

[Shawn Wagner]

Reread the rules at the start of 3.1. TIME doesn't have any of the
substrings used to indicate a particular other affinity, so it's treated as
NUMERIC.

On Tue, Feb 5, 2019, 8:53 AM Simon Slavin  On 5 Feb 2019, at 4:11pm, David Raymond  wrote:
>
> > "Time(8)" ends up as  numeric affinity for that field.
>
> The table in 3.1.1 shows how SQLite recognises the type you supply and
> turns it into a type it can use.  The lowest row of the table shows that
> "DATETIME" is recognised as NUMERIC.  However, it lists "DATETIME"
> specifically, not "TIME".
>
> 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

Re: [sqlite] "Pickling" an in-memory SQLite database.

[Shawn Wagner]

sqlite3_serialize() and deserialize would work:
https://www.sqlite.org/c3ref/serialize.html

On Thu, Jan 31, 2019, 4:47 PM Randall Smith  Hi, guys.
>
> I have an application that allows me to embed a (potentially large) block
> of data into its application file at close, and read the block back on
> open.  It would be convenient and attractive for me, for a plugin I am
> writing for this application, to be able to use an in-memory SQLite
> database, and then squirrel the DB away with everything else in the
> application file.  That way, everything would be stored under one roof, so
> to speak.
>
> My question:  Is there a good way to "pickle" a SQLite database into a
> block of bytes for storage, and then "unpickle" it later for use?  My
> fantasy is that this could all be done in memory so the file system does
> not have to become involved.
>
> I feel like I am missing something obvious here, but can't quite come up
> with it.  Thanks for any ideas or suggestions.
>
> Randall.
>
> ___
> 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] Compile error with certain compile time options

[Shawn Wagner]

Did you read all of https://www.sqlite.org/compile.html#omitfeatures ?

In particular it looks like you're trying to build the amalgamation version
instead of using the full source version to build a custom amalgamation
file like it suggests.

Plus of course even if you do that,

>  Some SQLITE_OMIT_* options might cause SQLite to malfunction and/or
provide incorrect answers.

On Tue, Jan 15, 2019, 5:18 AM Ertan Küçükoğlu  Hello,
>
> I am trying to build a specific static library for an embedded device
> running "arm-linux-gnueabi" sqlite 3.26.0
>
> I have tried to try some compile time parameters first. I passed some omit
> options to autoconfig in order to reduce file size. I am getting warnings
> and finally an error. I cannot see if it is my mistake or not. Gcc version
> is 6.3.0. My configure command line is:
> ./configure --disable-threadsafe --disable-tcl CFLAGS="-g -Os
> -DSQLITE_OMIT_ALTERTABLE -DSQLITE_OMIT_AUTOVACUUM -DSQLITE_OMIT_EXPLAIN
> -DSQLITE_OMIT_FOREIGN_KEY -DSQLITE_OMIT_AUTHORIZATION
> -DSQLITE_OMIT_BLOB_LITERAL -DSQLITE_OMIT_PROGRESS_CALLBACK
> -DSQLITE_OMIT_UTF16 -DSQLITE_OMIT_ATTACH -DSQLITE_DOESNT_MATCH_BLOBS
> -DSQLITE_OMIT_SHARED_CACHE -DSQLITE_USE_ALLOCA -DSQLITE_OMIT_DEPRECIATED
> -DSQLITE_OMIT_DECLTYPE -DSQLITE_MAX_EXPR_DEPTH=0"
>
> I have passed " -DSQLITE_OMIT_ATTACH" in my configure parameters and even
> though one of the warnings I receive is:
> sqlite3.c:18985:21: warning: 'sqlite3Attach' used but never defined
>
> I did not want to paste all output in here. Complete output can be found:
> https://pastebin.com/qGaD0TkH
>
> I also want to disable all FTS and RTREE as I also see them compiled in,
> but
> cannot be sure what option I can use to disable.
>
> Any help is appreciated.
>
> Thanks & regards,
> Ertan Küçükoğlu
>
>
> ___
> 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] [EXTERNAL] json_group_array() and sorting

[Shawn Wagner]

Try using json_group_array(json(o))

On Tue, Jan 8, 2019, 1:50 AM Eric Grange  Thanks.
>
> I think I may have encountered a "real" bug while ordering in a subquery.
> I have simplified it in the following exemples:
>
> select json_group_array(o) from (
>select json_object(
>   'id', sb.id
>) o
>from (
>   select 1 id, 2 field
>   ) sb
> )
>
>
> the json_group_array returns an array of JSON object, ie. [{"id":1}] while
> in
>
> select json_group_array(o) from (
>select json_object(
>   'id', sb.id
>) o
>from (
>   select 1 id, 2 field
>   ) sb
>order by sb.field desc
> )
>
> so with an added order by in the subquery, it returns and array of JSON
> strings, ie. ["{\"id\":1}"]
>
> In my particular case, I can work around the issue by using group_concat()
> rather than json_group_array()
>
> Can someone confirm whether this is a bug ?
>
> Thanks!
>
>
> Le mar. 8 janv. 2019 à 10:18, Hick Gunter  a écrit :
>
> > I don't recall that any (aggregate) function is concerned at all about
> the
> > order in which rows are visited. The effect is only visible in
> > non-commutative aggregates (e.g. concatenation).
> >
> > If you want the arguments presented to an aggregate function in a
> specific
> > order, then you need to enforce that order, with an order by clause in a
> > subselect if necessary.
> >
> > If you have an order by clause which is already fulfilled by the
> > visitation order, SQLite will not sort again.
> >
> > -Ursprüngliche Nachricht-
> > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > Im Auftrag von Eric Grange
> > Gesendet: Dienstag, 08. Jänner 2019 09:17
> > An: General Discussion of SQLite Database <
> > sqlite-users@mailinglists.sqlite.org>
> > Betreff: [EXTERNAL] [sqlite] json_group_array() and sorting
> >
> > Hi,
> >
> > Is json_group_array() supposed to honor a sorting clause or not ? (and
> > concatenation aggregates in general)
> >
> > I have a query like
> >
> > select json_group_array(json_object(
> >'id', st.id,
> >'num', st.numeric_field,
> >...bunch of fields here...
> > ))
> > from some_table st
> > ...bunch of joins here...
> > where ...bunch of conditions...
> > order by st.numeric_field desc
> > limit 50
> >
> >
> > but the resulting JSON array is not ordered according to the "order by",
> > but AFAICT by the st.id field (a primary key) When not aggregating, the
> > records are in the correct order.
> >
> > Is it a bug or something expected ?
> >
> > I can get the proper order when I use a subquery for the joins & filters,
> > and aggregate in a top level query, but that is rather more verbose, and
> I
> > am not sure the ordering being preserved in that case is not just
> > 'circumstancial' and could be affected by future SQLite query
> optimizations.
> >
> > Thanks!
> >
> > Eric
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > ___
> >  Gunter Hick | Software Engineer | Scientific Games International GmbH |
> > Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 |
> (O)
> > +43 1 80100 - 0
> >
> > May be privileged. May be confidential. Please delete if not the
> addressee.
> > ___
> > 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] [OT] Re: i Know i should use 'AS', but ....

[Shawn Wagner]

Using '.mode column" in conjunction with ".headers on" you're already using
makes it a lot more obvious.

On Mon, Dec 24, 2018, 12:20 PM Luuk  On 24-12-2018 19:21, Peter Johnson wrote:
> > The headers are present in all three queries you pasted.
> >
> > The first result shows two rows, the top row is the header.
> >
> > The other two results show 4 rows each, the top row of each is the header
> > row.
> >
> > -P
> >
> > On Tue, 25 Dec 2018, 3:42 AM Luuk  >
> >> sqlite> .version
> >> SQLite 3.26.0
> >>
> >> sqlite> .headers on
> >>
> >> sqlite> select 1 as X,date();
> >> X|date()
> >> 1|2018-12-24
> >> sqlite> select x,row_number() over (order by 1 desc) from (select 1 as x
> >> union all select 2 union all select 3);
> >> x|row_number() over (order by 1 desc)
> >> 3|1
> >> 2|2
> >> 1|3
> >>
> >> Why are the headers missing in above query?
> >>
> >
> Why was i overlooking this?
> ___
> 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] Sample Employee database ported to SQLite from MySQL

[Shawn Wagner]

I'd start by making the employees table a normal rowid one with an INTEGER
PRIMARY KEY (*Not* INT) column, and change all those VARCHAR, CHAR and DATE
column types to TEXT (or NUMERIC for the dates depending on the values they
hold).

Then add foreign key constraints so the relations between the tables are
explicit...

On Wed, Dec 19, 2018, 4:06 AM Arun - Siara Logics (cc)  Hi Chris,
>
> I don't own the MySQL side of the db, but its easy for me to change
> anything on Sqlite side.  To me the data looks decent for testing and
> creating applications for demo or learning.
>
> I am giving below the script and I will incorporate any other suggestions
> you may come up with:
>
> CREATE TABLE employees (
> emp_no  INT NOT NULL,
> birth_date  DATENOT NULL,
> first_name  VARCHAR(14) NOT NULL,
> last_name   VARCHAR(16) NOT NULL,
> gender  CHAR(1) NOT NULL,
> hire_date   DATENOT NULL,
> PRIMARY KEY (emp_no)
> ) without rowid;
> CREATE TABLE departments (
> dept_no CHAR(4) NOT NULL,
> dept_name   VARCHAR(40) NOT NULL,
> PRIMARY KEY (dept_no)
> ) without rowid;
> CREATE TABLE dept_manager (
>dept_no  CHAR(4) NOT NULL,
>emp_no   INT NOT NULL,
>from_dateDATENOT NULL,
>to_date  DATENOT NULL,
>PRIMARY KEY  (emp_no, dept_no)
> ) without rowid;
> CREATE TABLE dept_emp (
> emp_no  INT NOT NULL,
> dept_no CHAR(4) NOT NULL,
> from_date   DATENOT NULL,
> to_date DATENOT NULL,
> PRIMARY KEY (emp_no,dept_no)
> ) without rowid;
> CREATE TABLE titles (
> emp_no  INT NOT NULL,
> title   VARCHAR(50) NOT NULL,
> from_date   DATENOT NULL,
> to_date DATE,
> PRIMARY KEY (emp_no,title, from_date)
> ) without rowid;
> CREATE TABLE salaries (
> emp_no  INT NOT NULL,
> salary  INT NOT NULL,
> from_date   DATENOT NULL,
> to_date DATENOT NULL,
> PRIMARY KEY (emp_no, from_date)
> ) without rowid;
> CREATE INDEX emp_first_name on employees (first_name);
> CREATE INDEX emp_last_name on employees (last_name);
>
> Regards
> Arun
>
>   On Wed, 19 Dec 2018 16:25:11 +0530 Chris Locke <
> sql...@chrisjlocke.co.uk> wrote 
>  > The scheme (for me) is like nails on a chalkboard.  'dept_no' but
> defined
>  > as a 'CHAR', then 'emp_no' as an INT.
>  > Fields with '_no' are read as 'number' and so should be a number.  OK,
> that
>  > doesn't always work for 'telephone_no' (they usually start with a 0 ...
>  > well, they do in the UK where I am...)
>  > But I digress..
>  >
>  >
>  > Chris
>  >
>  >
>  > On Wed, Dec 19, 2018 at 9:05 AM Arun - Siara Logics (cc)  >
>  > wrote:
>  >
>  > > This project (https://github.com/siara-cc/employee_db) hosts the
> Sqlite3
>  > > db file ported from mysql test_db found at
>  > > https://github.com/datacharmer/test_db. It can be used to test your
>  > > applications and database servers. To use this project, download
>  > > employees.db.bz2, unzip and open using sqlite3 command line tool.
>  > >
>  > > The original data was created by Fusheng Wang and Carlo Zaniolo at
> Siemens
>  > > Corporate Research. The data is in XML format.
>  > > http://timecenter.cs.aau.dk/software.htm
>  > >
>  > > Giuseppe Maxia made the relational schema and Patrick Crews exported
> the
>  > > data in relational format.
>  > >
>  > > The database contains about 300,000 employee records with 2.8 million
>  > > salary entries. The export data is 167 MB, which is not huge, but
> heavy
>  > > enough to be non-trivial for testing.
>  > >
>  > > A picture of the schema can be found at:
>  > >
> https://github.com/siara-cc/employee_db/blob/master/employees-schema.png?raw=true
>  > >
>  > > Regards
>  > > Arun - Siara Logics (cc)
>  > >
>  > >
>  > > ___
>  > > sqlite-users mailing list
>  > > sqlite-users@mailinglists.sqlite.org
>  > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>  > >
>  > ___
>  > sqlite-users mailing list
>  > sqlite-users@mailinglists.sqlite.org
>  > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>  >
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE, modifying columns

[Shawn Wagner]

You're using a workflow that https://www.sqlite.org/lang_altertable.html
explicitly calls out as incorrect and error prone...

Try to create a new table, copy data over, drop the original and then
rename the new one to see if that fixes the issue.

On Wed, Dec 12, 2018, 8:54 AM Thomas Kurz  Dear all,
>
> I don't know whether the behavior is intentional or a bug, so let me
> describe it (occurs since 3.25):
>
> Due to the lack of ALTER TABLE MODIFY COLUMN, I use the following
> construction:
>
> PRAGMA foreign_keys=0
> BEGIN TRANSACTION
> ALTER TABLE x RENAME TO x_old
> CREATE TABLE IF NOT EXISTS x (... new declaration ...)
> INSERT INTO x (...) SELECT ... FROM x_old
> DROP TABLE x_old
>  more to do here ...
> COMMIT
> PRAGMA foreign_keys=1
>
> Usually, this works fine, but now I have a VIEW that references table x,
> which leads to an error "error in view ...: no such table: main.x_old".
>
> Of course, this happens because renaming x to x_old also changes the
> view's reference from x to x_old which is not intended in this case.
>
> As a workaround, I have now added "PRAGMA legacy_alter_table" before and
> after the transaction.
>
> The behavior makes modifying columns even more complicated, so I'd like to
> beg for an ALTER TABLE MODIFY COLUMN statement once more ;-))
>
> Kind regards,
> Thomas
>
> ___
> 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] Transaction commits hangs up

[Shawn Wagner]

Do you still have the issue with a current version of the library?

On Tue, Dec 11, 2018, 12:53 AM Aydin Ozgur Yagmur  I have been using sqlite3.8.11 on fat32 file system. Sometimes, while
> committing a transaction commit process hangs up and database became
> locked. I am trying to find a cause or a solution for this problem. Does
> anyone encounter with similar problem before? Do you have any suggestion or
> any idea for this problem?
>
> Thank you,
> yagmur
> ___
> 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] Bug? Confused data entry with column name

[Shawn Wagner]

Use single quotes, not double. Double quotes are used for identifiers, not
strings, so that matches rows where id and description have the same value.

On Wed, Nov 28, 2018, 1:19 AM Dale Mellor    THIS VERSION
> SQLite 3.25.3 2018-11-05 20:37:38
> 89e099fbe5e13c33e683bef07361231ca525b88f7907be7092058007b750alt1
> zlib version 1.2.8
> gcc-8.2.0
>
>
> >   THIS SCRIPT
> create table test (id varchar, description varchar);
> insert into test (id, description) values ("Description", "Duh");
> insert into test (id, description) values ("Daft", "Daft");
> select rowid, * from test where id="Description";
>
>
>
> >>   PRODUCES
> 2|Daft|Daft
>
>
>
> >>   BUT EXPECTED
> 1|Description|Duh
> ___
> 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] sqlite3 Asynchronous I/O

[Shawn Wagner]

By asynchronous i/o, do you mean the vfs module described here:
https://www.sqlite.org/asyncvfs.html ?

On Tue, Nov 27, 2018, 1:24 AM Prajeesh Prakash
 Hi Members,
>
> I am using sqlite3 Asynchronous I/O on my application. So is there any way
> to get the status of the DB update (After the write operation) from the
> asyncWriterThread  so that my application can do the proper error handling.
>
> I am expecting a reply.
>
> Thank you
>
> Prajeesh P
> ___
> 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] Possible bug in Alter Table

[Shawn Wagner]

It's waiting on a plain single quote to end the string. You have a Unicode
smart quote character U+2019 (’) instead of a ' at the end before the
semicolon, which doesn't count.

On Mon, Nov 26, 2018, 11:19 AM Petite Abeille 
>
> > On Nov 26, 2018, at 20:11, Richard Hipp  wrote:
> >
> > though there probably is not time to get warnings in to the forthcoming
> 3.26.0 release.
>
> Talking of which, the CLI doesn’t seem to handle the following statement
> very gracefully:
>
> sqlite> select DATE '1998-12-25’;
>   ...>
>   ...>
>   …>
>
> Note how the CLI doesn’t recognize the semicolon marking the
> end-of-statement and expects more input.
>
> sqlite3 -version
>
> 3.25.3 2018-11-05 20:37:38
> 89e099fbe5e13c33e683bef07361231ca525b88f7907be7092058007b75036f2
> ___
> 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] DELETE does not accept AS alias despite documentation

[Shawn Wagner]

Works for me with 3.25.3. What version of sqlite are you using?

On Thu, Nov 15, 2018, 12:05 PM Jake  I know the fact that DELETE and UPDATE don't accept a table alias has come
> up here a couple of years ago, but I think this should be filed as a bug
> because the syntax documentation explicitly shows it being accepted.  Both
> statements are documented as taking a qualified-table-name:
>
> qualified-table-name:
> https://www.sqlite.org/syntaxdiagrams.html#qualified-table-name
>
> Delete: https://www.sqlite.org/syntaxdiagrams.html#delete-stmt
> Update: https://www.sqlite.org/syntaxdiagrams.html#update-stmt
>
> And qualified-table-name has the optional [AS ] component.
>
> This page places extra restrictions only in the context of triggers:
> https://www.sqlite.org/lang_delete.html
>
> Even the trigger restrictions do not mention the alias clause.
>
> -Jake Cobb
> ___
> 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] about upsert feature.

[Shawn Wagner]

Show an actual insert statement that causes an error?

On Wed, Nov 7, 2018, 2:22 AM 畑宏和  Hi, I have a question.
>
> We can use upsert after v3.24.0.
>
> Document  says that
> `INSERT INTO table-name (column-name) select-stmt upsert-clause`
> But when I try this syntax, it returns syntax error.
> I think we can't use upsert-clause with select-stmt.
> Is this thought right?
>
> Thanks for reading my post.
> ___
> 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] INSERT OR REPLACE and foreign keys

[Shawn Wagner]

That's normal. It deletes the conflicting row and inserts a new one.

On Tue, Oct 23, 2018, 10:58 AM Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> Dear SQLiters,
>
> I am using INSERT OR REPLACE to update a table which holds a column which
> servers as a foreign key. But I noticed a strange behavior: If the parent
> record existed, then replace mechanism replaces it, but the records from
> children tables are deleted. The foreign key is set up to cascade on
> delete, as I think it should. So it seems that "replace" is implemented as
> "delete then insert" rather than "update". Is that normal and expected? Am
> I doing something wrong?
>
> Thank you,
>
> Roman
> ___
> 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] Custom serialization/deserialization of in-memory DB

[Shawn Wagner]

https://www.sqlite.org/c3ref/serialize.html might help.

On Mon, Oct 22, 2018, 11:31 AM Zoltan Demeter  wrote:

> Hello there,
>
> I am working on a small plugin for a bigger application. I am bound by
> the in-house app framework, for example I cannot create arbitrary
> files or use anything OS-specific directly.
>
> I am using SQLite with an in-memory DB to obtain sorted, filtered
> lists of items. I am happy with the library. The only problem is, the
> amount of data can be so huge, that it takes a considerable amount of
> time to calculate some of the fields and build up the DB.
>
> Thus, I would like to save my tables, but the "normal" backup API
> wants to use a file. Like I wrote, I cannot use an arbitrary file, my
> load/save procedure must be integrated into the app framework so that
> all data will be packed into the project data file(s).
>
> Basically, if I could obtain a byte array, I could save it to disk.
> Load and save should work across bitness and OS barriers ... eg.
> between 32-bit Windows and 64-bit Mac.
>
> Or if I could tell SQLite to use custom file I/O routines ... I could
> mock fwrite() to just write to memory and then grab the data.
>
> A "simple" table dump, without indices etc. would be already be great.
>
> Any ideas please?
> ___
> 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] 64-column covering index limit clarification

[Shawn Wagner]

Okay, next question: Does the query you're testing this with obey the
requirements needed to use the partial index (see
https://sqlite.org/partialindex.html#queries_using_partial_indexes)?

On Thu, Oct 18, 2018 at 9:43 PM Deon Brewis  wrote:

> Yes a non partial index beyond column 64 works as I would expect.
>
> - Deon
>
> > On Oct 18, 2018, at 12:34 PM, Shawn Wagner 
> wrote:
> >
> > Does a normal non-partial index make a difference in the query plan?
> >
> >> On Thu, Oct 18, 2018, 12:30 PM Deon Brewis  wrote:
> >>
> >> Hi,
> >>
> >> I seem to have run into a limit where SQLITE doesn't use an index
> >> correctly if an indexed column is over the 64th column in the table.
> It's a
> >> partial index like:
> >>
> >> CREATE INDEX idx ON
> >>  table(A, B DESC, C, D)
> >>  WHERE A > 0
> >>
> >> Where A and B are columns 70 and 72 on 'table'.
> >>
> >> I know about the 64-column limitation for covering indexes:
> >>
> >>
> http://sqlite.1065341.n5.nabble.com/Max-of-63-columns-for-a-covering-index-to-work-td68945.html
> >>
> >> However, this isn't a covering index, it's a partial index. But it seems
> >> to run into the same limit. Even if I forced in the index into a query
> it
> >> still does a "USE TEMP B-TREE" at the end to satisfy a simple "ORDER BY
> A,
> >> B DESC" query. After I re-ordered the table, it magically started
> working.
> >>
> >> Is there any better documentation anywhere (other than the archive) of
> all
> >> of the cases to which the 64-column limit applies?
> >>
> >> - Deon
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64-column covering index limit clarification

[Shawn Wagner]

Does a normal non-partial index make a difference in the query plan?

On Thu, Oct 18, 2018, 12:30 PM Deon Brewis  wrote:

> Hi,
>
> I seem to have run into a limit where SQLITE doesn't use an index
> correctly if an indexed column is over the 64th column in the table. It's a
> partial index like:
>
> CREATE INDEX idx ON
>   table(A, B DESC, C, D)
>   WHERE A > 0
>
> Where A and B are columns 70 and 72 on 'table'.
>
> I know about the 64-column limitation for covering indexes:
>
> http://sqlite.1065341.n5.nabble.com/Max-of-63-columns-for-a-covering-index-to-work-td68945.html
>
> However, this isn't a covering index, it's a partial index. But it seems
> to run into the same limit. Even if I forced in the index into a query it
> still does a "USE TEMP B-TREE" at the end to satisfy a simple "ORDER BY A,
> B DESC" query. After I re-ordered the table, it magically started working.
>
> Is there any better documentation anywhere (other than the archive) of all
> of the cases to which the 64-column limit applies?
>
> - Deon
>
> ___
> 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] sqlite-users Digest, Vol 130, Issue 11

[Shawn Wagner]

Amen.

I'm about ready to unsubscribe from this list and not come back until it
transitions to a forum (which is the interface I prefer anyways) because of
all this nattering clogging up my inbox the last few days. I thought I was
a grumpy old fart set in my ways, but I don't have anything on some of you.

On Thu, Oct 11, 2018, 7:51 AM Balaji Ramanathan 
wrote:

> >
> >2. Re: SQLite mailing list
> >
> >
>
> The 1990's called and they want their mailing lists back.  So, let us
> switch to 21st century technology already.  Count me in as an enthusiastic
> YES vote for proper forums (including subforums - so that I can read what I
> want and skip the rest instead of drowning in irrelevant emails with
> hundreds or thousands of lines of included, quoted emails). Or maybe we can
> have everybody fax everybody else?  An entire mailing list with no
> technical discussion except whether to retain the mailing list or not.  I
> can only shake my head in amazement . . .
>
> Balaji Ramanahan
> ___
> 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] CLI feature request: .import option -h

[Shawn Wagner]

A more flexible approach might be to add a way to have .import run a
command and use its standard output as the data source, instead of a file.

sqlite> .import "!tail +2 data.csv" mytable

for this example. Lets you do basic ETL stuff for any import format without
having to use temporary files or leave the shell. .once and .output could
be extended the same way, piping output lines to a process.

Should be easy to implement by using popen() instead of fopen() when
requested.

On Sun, Sep 30, 2018 at 3:05 AM Christof Böckler  wrote:

> Hi all,
>
> I'm sorry if I'm wrong on this list, but I have a small feature request
> for the CLI.
>
> I regularly import CSV data with a first line of headers into a SQLite
> database by using the .import command. On the first run the destination
> table gets automatically created. Later I get more data from the same
> source and want to add these rows to the same table by using the same
> command (.import).  Unfortunately the (first) header line will this time
> be interpreted as data instead of as meta data.
>
> Thus I request an option for the .import command, that will always regard
> the first line of input as header information. I suggest we call it -h.
>
> If the destination table does not exist yet, then the option is a no op
> compared to the current implementation.
>
> If the destination table already exists, then simply start importing
> data on line 2.
>
> I know that this can be achieved by other means outside SQLite,
> but I think it should be possible with sqlite3 alone. Otherwise data
> preprocessing has to be done based on a condition inside the SQLite
> database, which seems pretty complicated compared to ignoring one line
> of input.
>
> Greetings
> Christof
> ___
> 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] Possible bug in window function queries.

[Shawn Wagner]

Been playing around with the new window functions in 3.25, and ran into
something that looks like an infinite loop.

Working with the t1 table many of the examples in the documentation use,
I've come up with a minimal test case:

sqlite> select id, b, lead(c, 1) over (order by c) as x from t1 where id >
1 order by b;
id  b   x
--  --  --
2   B   two
3   C   three
4   D   one
5   E
6   F   two
7   G   three

is all well and good. However, after adding a LIMIT to the query:

sqlite> select id, b, lead(c, 1) over (order by c) as x from t1 where id >
1 order by b limit 1;
(time goes by)
^CError: interrupted

A LIMIT of just a few rows causes the query to hang and never produce any
output while sqlite3 uses 100% CPU.

LIMIT 4 and up works, but change the comparison to >= and LIMIT 4 also
freezes, but LIMIT 5 works. Take out the ORDER BY b and no freeze.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users