Re: [sqlite] extracting domain names from website addresses efficiently

[Peter Da Silva]

This seems like a job for regular expressions.
 

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

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

[Peter Da Silva]

On 12/14/17, 12:08 PM, "sqlite-users on behalf of Simon Slavin" 
 wrote:
> Just to remind you that if something is not documented it can change.  The 
> next version of SQLite might decide that 1 / 2 is 0.  So don’t write code 
> that depends on it.

I think it already does:

sqlite> select 1/2;
0
sqlite> 


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

Re: [sqlite] Seasonal syntax

[Peter Da Silva]

SELECT name, address
CASE behaviour
  WHEN 'nice' THEN SELECT toy FROM stocking_stuffers ORDER BY random() LIMIT 1
  WHEN 'naughty' THEN 'coal'
  ELSE phnglui mgwlnafth cthulhu
  END

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

Re: [sqlite] Odd question

[Peter Da Silva]

What I don’t understand is this app that’s making SQLite calls, so it’s using 
the SQLite library, and it’s expecting a result from updates and inserts?

That seems like a bug or design flaw in the application.

Possibly it’s looking for the number of rows effected result and not finding it 
for some reason?

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

Re: [sqlite] Importing Text to Create a Table

[Peter Da Silva]

I suspect you would be best advised to do more processing of the data to 
extract just the email addresses rather than treating it like an unstructured 
text blob.
 

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

Re: [sqlite] Move to Github!!?

[Peter Da Silva]

On 12/27/17, 12:14 PM, "sqlite-users on behalf of Simon Slavin" 
 wrote:
> Would running git/fossil on a filesystem like that solve the problem ?

You would have to modify it to use the new APIs for things like copying files.

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

Re: [sqlite] Move to Github!!?

[Peter Da Silva]

On 12/27/17, 2:23 PM, "sqlite-users on behalf of Simon Slavin" 
 wrote:
> Fair point.  Automatic de-duplication would be more beneficial.  And it 
> wouldn’t require extreme cleverness to be separately written into each 
> application.  APFS does not do automatic de-duplication.

We had Netapp filers at my last gig. It’s about the only thing I really miss 
from there, but they were *nice*. And automatic deduplication  was not the 
least of the reasons.

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

Re: [sqlite] Dan: zipfile.c FYI data column quirk

[Peter Da Silva]

I don’t see what the problem is, do you not expect a newline at the end of the 
line in a file? 

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

Re: [sqlite] new Error database disk image is malformed

[Peter Da Silva]

> Ok. I've read the Document and I think I may be having a rouge thread issue.  
> I hope not since I use synclock in my code when ever a thread is attempting a 
> write to the database.  That seems like the only issue from that page that I 
> may be doing.  I could have up to 30 or more threads reading from the DB but 
> only one to three active threads writing.  The way my code is set up, though 
> is that each thread would have to wait for the previous thread to finish 
> writing before its turn to write. 

Since you're I/O bound on socket connections, and not CPU or database bound, 
you might want to just have one database thread that communicates using native 
inter-thread messaging to pass out work and accept responses from the worker 
threads.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Peter Da Silva]

These are different requests in SQL.

"ID = NULL" is comparing the ID to "NULL". Comparing any value to "NULL" fails. 
This is equivalent to

SELECT ID FROM Tbl WHERE FALSE;

A smarter query planner would run it in zero ms. :)

"ID IS NULL" is checking if the value in ID is null.

Neither will return any values since a primary key can not be NULL.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[Peter Da Silva]

I would have thought that the logical platform-independent scripting language 
to use with sqlite would be tcl. By default tcl comes with sqlite built in, so 
you don’t even need to compile anything.

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

Re: [sqlite] Infinite loop when updating indexed with case and where clause

[Peter Da Silva]

On 1/16/18, 8:12 AM, "sqlite-users on behalf of Don V Nielsen" 
 wrote:
> Off topic question regarding fix: "0!=(wctrlFlags & WHERE_ONEPASS_MULTIROW)"

> Is there a performance bonus or compiler optimization if one compares a 
> target constant to a source condition versus comparing a target condition to 
> a source constant, as in "(wctrlFlags & WHERE_ONEPASS_MULTIROW)!=0"?

> The only reason I ask is that it is the opposite of how I code (in any 
> language) and how expect to read things.

I’ve seen this style increasingly often the last ten or fifteen years, the idea 
is that by putting the constant on the left side of a comparison it’s harder to 
accidentally typo it into an assignment.
 

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

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

[Peter Da Silva]

On 1/16/18, 10:29 AM, "sqlite-users on behalf of petern" 
 wrote:
> https://sqlite.org/csv.html

BTW typo on that page:

“The example above showed a single filename='th3file.csv' argument for the CSV 
virtual table.”

Should be:

“The example above showed a single filename='thefile.csv' argument for the CSV 
virtual table.”


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

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

[Peter Da Silva]

On 1/17/18, 11:07 AM, "sqlite-users on behalf of Jens Alfke" 
 
wrote:
> If I were tackling this, I’d look for an open-source CSV parser/generator 
> library. Once you have that, the part that reads/writes the rows to the 
> database is pretty simple.

If they’re reading tab separated files, I wouldn’t use CSV code... there’s a 
lot of complexity in CSV readers that are unnecessary for TSV because it 
doesn’t support or require quoting of embedded separators.

Just something like

char *s, *cols[MAXLINE], line[MAXLINE];
int col;
while(fgets(line, MAXLINE, fp)) {
s = line;
col = 0;
while(cols[col] = s, s = strchr(s, ‘\t’)) {
*s++ = ‘\0’;
col++;
}
if(s = strchr(cols[col], ‘\n’)) *s = ‘\0’;
// bind cols[...] to prepared statement
// step prepared statement
}



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

Re: [sqlite] UTF8 and NUL

[Peter Da Silva]

What is the goal of this discussion? Changing the string terminator SQLite 
uses? I think it's almost 50 years too late for that, but I'm sure that if 
Unicode and UTF8 had been a thing in 1970 then C would have selected FF as the 
string terminator. 

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

Re: [sqlite] UTF8 and NUL

[Peter Da Silva]

On 1/26/18, 8:24 AM, "sqlite-users on behalf of Gary R. Schmidt" 
 wrote:
> But how would you differentiate EOF???  (Let me guess, 0.  :-) )

End of file is not part of the contents of the file or a string. It's metadata. 

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

Re: [sqlite] UTF8 and NUL

[Peter Da Silva]

On 1/26/18, 12:12 PM, "sqlite-users on behalf of Keith Medcalf" 
 
wrote:
> Actually, EOF (0xFF) *is* part of a text file, and is the byte in an ASCII 
> byte-stream that indicates end-of-file.  In the "old days" the bytes 
> following the last-byte in a stream and the end of a storage block 
> (sector/cluster/track/cylinder, what have you) were padded with 0xFF so you 
> knew you were past the end-of-the-file when you were reading it.

Oh, I remember the messes that existed before stream files became the norm. But 
messes they were, and there's no more reason to support them in a Unicode file 
than there is to support FIELDDATA format.

And if you're going to talk about the block file and paper tape era, don't 
forget that FF also meant a deleted character and should be skipped without 
being counted or accounted for.

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

Re: [sqlite] UTF8 and NUL

[Peter Da Silva]

On 1/26/18, 12:31 PM, "sqlite-users on behalf of J Decker" 
 
wrote:
> ctrl-z was end of file text character in DOS (wrote char 26; not FF)

DOS wasn't an operating system.
 

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

Re: [sqlite] UTF8 and NUL

[Peter Da Silva]

On 1/26/18, 12:40 PM, "sqlite-users on behalf of J Decker" 
 
wrote:
>  reads the bytes and does things with them.  the EOF would get returned with 
> fgetc() but not the character.

Fgetc returns an int, not a byte. That EOF is -1, not 0xFF.



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

Re: [sqlite] UTF8 and NUL

[Peter Da Silva]

On 1/26/18, 1:37 PM, "sqlite-users on behalf of J Decker" 
 
wrote:
>doesn't get 26 either. 0x1a

26 isn't EOF, it's SUB (substitute). It was used to represent untranslatable 
characters when converting (for example) EBCDIC to ASCII.

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

Re: [sqlite] UTF8 and NUL

[Peter Da Silva]

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

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

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

Re: [sqlite] UTF8 and NUL

[Peter Da Silva]

On 1/26/18, 2:34 PM, "sqlite-users on behalf of J. King" 
 
wrote:
> Do you have a point in making either statement? If you do, I'm really not 
> seeing it.

The point is that apart from CP/M and derivatives like DOS, this kind of 
behavior is strictly a leftover from the '60s. And CP/M only had this 
restriction because it was tremendously resource-constrained. It's not a 
precedent for treating some magic character as an end-of-file marker when 
virtually every operating released since 1970 system (apart from a couple that 
derived from this historical anomaly) has had files with byte-precise size 
metadata.

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

Re: [sqlite] UTF8 and NUL

[Peter Da Silva]

Sqlite uses NUL as the string terminator internally, the published API 
specifies has stuff like this all over the place:

> In those routines that have a fourth argument, its value is the number of 
> bytes in the parameter. To be clear: the value is the number of bytes in the 
> value, not the number of characters. If the fourth parameter to 
> sqlite3_bind_text() or sqlite3_bind_text16() is negative, then the length of 
> the string is the number of bytes UP TO THE FIRST ZERO TERMINATOR.

It would be a huge push-up to change this, it would break everything, including 
extensions. I don't think it would be possible until something like sqlite4.

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

Re: [sqlite] UTF8 and NUL

[Peter Da Silva]

On 2018-01-26, at 17:05, J Decker  wrote:
> On Fri, Jan 26, 2018 at 1:21 PM, Peter Da Silva <
> peter.dasi...@flightaware.com> wrote:
>> Sqlite uses NUL as the string terminator internally, the published API
>> specifies has stuff like this all over the place:

>>> In those routines that have a fourth argument, its value is the number of 
>>> bytes in the parameter. To be clear: the value is the number of bytes in 
>>> the value, not the number of characters. If the fourth parameter to 
>>> sqlite3_bind_text() or sqlite3_bind_text16() is negative, then the length 
>>> of the string is the number of bytes UP TO THE FIRST ZERO TERMINATOR.

> You stressed the wrong part there - *IS NEGATIVE*

Why? Passing -1 as the length is a common way to tell sqlite3 to calculate the 
length itself. It's a documented and widely used part of the API. Therefore:

>> It would be a huge push-up to change this, it would break everything,
>> including extensions. I don't think it would be possible until something
>> like sqlite4.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tee to a table

[Peter Da Silva]

It's pretty easy in Tcl

Sqlite3 db file.sqlite
while {[gets stdin line] > 0} {
parse_line_into index content; # or whatever you do to extract content from 
the line
db eval {INSERT INTO whatever (index, content) VALUES ($index, $content);}
}
db close

On 2/1/18, 2:25 PM, "sqlite-users on behalf of Cecil Westerhof" 
 wrote:

At the moment I have a script where I send the output of a ffmpeg command
to the terminal and a file. Is it possible to send the output to a SQLite
table. I like to use tcl for this.

-- 
Cecil Westerhof
___
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] Tee to a table

[Peter Da Silva]

You can do something like this, for one record per file:

Sqlite3 db file.sqlite
while {[gets stdin line] > 0} {
lappend content $line
}
set content [join $content "\n"; # or otherwise manipulate the text you got 
back.
db eval {INSERT INTO whatever (index, content) VALUES ($index, $content);}
db close

Or anything in between:

Sqlite3 db file.sqlite
while {[gets stdin line] > 0} {
if {[matches_end_of_content $line]} {
   db eval {INSERT INTO whatever (index, content) VALUES ($index, 
$content);}
   set content {}
   } elseif {[matches_start_of_content $line]} {
  get_index_from_content $content index
   } else {
  add_line_to_content $line content
   } 
}
db close

On 2/1/18, 3:00 PM, "sqlite-users on behalf of Cecil Westerhof" 
 wrote:

2018-02-01 21:49 GMT+01:00 Peter Da Silva :

> It's pretty easy in Tcl
>
> Sqlite3 db file.sqlite
> while {[gets stdin line] > 0} {
> parse_line_into index content; # or whatever you do to extract content
> from the line
> db eval {INSERT INTO whatever (index, content) VALUES ($index,
> $content);}
> }
> db close
>

​Looks promising.​ The 'problem' is that I get a record pro line. But that
is not a big problem I think. On the plus side it is easy to make a GUI
instead of a command line version.

Thanks.



> On 2/1/18, 2:25 PM, "sqlite-users on behalf of Cecil Westerhof" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> cldwester...@gmail.com> wrote:
>
> At the moment I have a script where I send the output of a ffmpeg
> command
> to the terminal and a file. Is it possible to send the output to a
> SQLite
> table. I like to use tcl for this.
>

-- 
Cecil Westerhof
___
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] Tee to a table

[Peter Da Silva]

Switch -glob and switch -regexp are also handy for this kind of code.

On 2/1/18, 3:07 PM, "sqlite-users on behalf of Peter Da Silva" 
 wrote:

You can do something like this, for one record per file:

Sqlite3 db file.sqlite
while {[gets stdin line] > 0} {
lappend content $line
}
set content [join $content "\n"; # or otherwise manipulate the text you got 
back.
db eval {INSERT INTO whatever (index, content) VALUES ($index, $content);}
db close

Or anything in between:

Sqlite3 db file.sqlite
while {[gets stdin line] > 0} {
if {[matches_end_of_content $line]} {
   db eval {INSERT INTO whatever (index, content) VALUES ($index, 
$content);}
   set content {}
   } elseif {[matches_start_of_content $line]} {
  get_index_from_content $content index
   } else {
  add_line_to_content $line content
   } 
}
db close

On 2/1/18, 3:00 PM, "sqlite-users on behalf of Cecil Westerhof" 
 wrote:

    2018-02-01 21:49 GMT+01:00 Peter Da Silva 
:

> It's pretty easy in Tcl
>
> Sqlite3 db file.sqlite
> while {[gets stdin line] > 0} {
> parse_line_into index content; # or whatever you do to extract 
content
> from the line
> db eval {INSERT INTO whatever (index, content) VALUES ($index,
> $content);}
> }
> db close
>

​Looks promising.​ The 'problem' is that I get a record pro line. But 
that
is not a big problem I think. On the plus side it is easy to make a GUI
instead of a command line version.

Thanks.



> On 2/1/18, 2:25 PM, "sqlite-users on behalf of Cecil Westerhof" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> cldwester...@gmail.com> wrote:
>
> At the moment I have a script where I send the output of a ffmpeg
> command
> to the terminal and a file. Is it possible to send the output to a
> SQLite
> table. I like to use tcl for this.
>

-- 
Cecil Westerhof
___
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] Advice for a "how-to" situation.

[Peter Da Silva]

Seems like you'd want to create a rowid primary key (internal_id integer 
primary key) that's the foreign key for the other table.

On 2/15/18, 1:19 PM, "sqlite-users on behalf of Michael Tiernan" 
 wrote:
(The objective is to have a single field in another table that 
identifies the CPU used in a system. It will be a many-to-one reference 
to this CPUModel.)

Any advice? Pointers to documentation offering advice will help too.

Thanks for everyone's time.
___
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] printf() problem padding multi-byte UTF-8 code points

[Peter Da Silva]

On 2018-02-17, at 17:36, Richard Hipp  wrote:
> The current behavior of the printf() function in SQLite, goofy though
> it may be, exactly mirrors the behavior of the printf() C function in
> the standard library in this regard.
> 
> So I'm not sure whether or not this is something that ought to be "fixed".

Printf's handling of unicode is inconsistent in other ways, too. I suspect that 
there's still undefined behavior floating around in there too. Even wprintf 
isn't entirely unsurprising:

% env
...
LANG=en_US.UTF-8
...
% cat localized.c
#include 
#include 

int main() {
wprintf (L"'%4ls'\n", L"äöü");
}
% cc localized.c
% ./a.out
' ???'
% cat delocalized.c
#include 
#include 
#include 

int main() {
setlocale(LC_ALL, "");
wprintf (L"'%4ls'\n", L"äöü");
}
% cc delocalized.c
% ./a.out
' äöü'
% uname -a
Darwin Stonehenge.local 16.7.0 Darwin Kernel Version 16.7.0: Thu Jan 11 
22:59:40 PST 2018; root:xnu-3789.73.8~1/RELEASE_X86_64 x86_64

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

Re: [sqlite] Format of sqlite-users digests.

[Peter Da Silva]

On 2/26/18, 12:24 PM, "Richard Hipp"  wrote:
> We use a 3rd party mailing list manager:  GNU MailMan.  You'll need to take 
> up your concerns with them, I'm afraid.  I know nothing about the MailMan 
> code.

Mailman supports mime-style digests, Randall may prefer to get those rather 
than plaintext? It would depend on how their email software handles 
multipart/digest.

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

Re: [sqlite] UPSERT

[Peter Da Silva]

On 3/19/18, 3:37 AM, "sqlite-users on behalf of Paul" 
 wrote:
> Fort me personally, the most sad thing is an annoyance. Because I have to 
> maintain two almost identical queries and manually tweak strategies.

I almost always generate queries dynamically if they're "almost identical". 
Only one-offs get handcrafted queries.

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

Re: [sqlite] How to convert a datetime column to a date?

[Peter Da Silva]

It might be helpful to provide some examples of what you have in those DATETIME 
columns.

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

Re: [sqlite] How to convert a datetime column to a date?

[Peter Da Silva]

1. It looks like you have superfluous quotes around the dates. That's probably 
your issue.

2. Try "select distinct dttm from foo LIMIT 10;"

On 3/22/18, 8:47 AM, "sqlite-users on behalf of Ron Watkins" 
 
wrote:
sqlite> select distinct dttm from foo;

…  (lots of records)

'2018-03-22 06:25:01'

'2018-03-22 06:26:01'

'2018-03-22 06:27:01'

'2018-03-22 06:28:01'

'2018-03-22 06:29:01'

'2018-03-22 06:30:01'

'2018-03-22 06:31:01'

'2018-03-22 06:32:02'

'2018-03-22 06:33:01'

'2018-03-22 06:34:01'

'2018-03-22 06:35:01'

'2018-03-22 06:36:01'

'2018-03-22 06:37:01'

'2018-03-22 06:38:01'

'2018-03-22 06:39:01'

'2018-03-22 06:40:01'

'2018-03-22 06:41:01'

'2018-03-22 06:42:01'

'2018-03-22 06:43:01'

'2018-03-22 06:44:01'

sqlite>

 

BTW, is there some equilivant to “select top 10 * from foo;” style to 
reduce the number of records? I tried this “top 10” but it’s apparently  not 
supported.

 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Da Silva
Sent: Thursday, March 22, 2018 6:33 AM
To: SQLite mailing list
Subject: Re: [sqlite] How to convert a datetime column to a date?

 

It might be helpful to provide some examples of what you have in those 
DATETIME columns.

 

___

sqlite-users mailing list

 <mailto:sqlite-users@mailinglists.sqlite.org> 
sqlite-users@mailinglists.sqlite.org

 <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> 
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] How to convert a datetime column to a date?

[Peter Da Silva]

SQLITE3 datetime fields are actually text.

Fix the file that you're importing so it doesn't have quotes around the date. 
It looks like you're using some kind of modified CSV exporter to create the 
file.

On 3/22/18, 9:06 AM, "sqlite-users on behalf of Ron Watkins" 
 
wrote:

The file that I use ".import" on contains records like this:

 

'2018-03-22 07:01:01'|2533268

 

I had assumed the value was being treated as a datetime, but it looks like 
it may be treated as a string?

Im a bit confused because the column definition sais "datetime", not 
"varchar", so if it's being treated as a string what can I do to fix it to be 
treated as a actual datetime datatype?

 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Da Silva
Sent: Thursday, March 22, 2018 6:49 AM
To: SQLite mailing list
Subject: Re: [sqlite] How to convert a datetime column to a date?

 

1. It looks like you have superfluous quotes around the dates. That's 
probably your issue.

 

2. Try "select distinct dttm from foo LIMIT 10;"

 

On 3/22/18, 8:47 AM, "sqlite-users on behalf of Ron Watkins" < 
<mailto:sqlite-users-boun...@mailinglists.sqlite.org%20on%20behalf%20of%20rwa...@gmail.com>
 sqlite-users-boun...@mailinglists.sqlite.org on behalf of rwa...@gmail.com> 
wrote:

sqlite> select distinct dttm from foo;



…  (lots of records)



'2018-03-22 06:25:01'



'2018-03-22 06:26:01'



'2018-03-22 06:27:01'



'2018-03-22 06:28:01'



'2018-03-22 06:29:01'



'2018-03-22 06:30:01'



'2018-03-22 06:31:01'



'2018-03-22 06:32:02'



'2018-03-22 06:33:01'



'2018-03-22 06:34:01'



'2018-03-22 06:35:01'



'2018-03-22 06:36:01'



'2018-03-22 06:37:01'



'2018-03-22 06:38:01'



'2018-03-22 06:39:01'



'2018-03-22 06:40:01'



'2018-03-22 06:41:01'



'2018-03-22 06:42:01'



'2018-03-22 06:43:01'



'2018-03-22 06:44:01'



sqlite>



 



BTW, is there some equilivant to “select top 10 * from foo;” style to 
reduce the number of records? I tried this “top 10” but it’s apparently  not 
supported.



 



-Original Message-

From: sqlite-users [ 
<mailto:sqlite-users-boun...@mailinglists.sqlite.org> 
mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Peter Da Silva

Sent: Thursday, March 22, 2018 6:33 AM

To: SQLite mailing list

Subject: Re: [sqlite] How to convert a datetime column to a date?



 



It might be helpful to provide some examples of what you have in those 
DATETIME columns.



 



___



sqlite-users mailing list



 < <mailto:sqlite-users@mailinglists.sqlite.org> 
mailto:sqlite-users@mailinglists.sqlite.org>  
<mailto:sqlite-users@mailinglists.sqlite.org> 
sqlite-users@mailinglists.sqlite.org



 < 
<http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>  
<http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___

sqlite-users mailing list

 <mailto:sqlite-users@mailinglists.sqlite.org> 
sqlite-users@mailinglists.sqlite.org

 <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



 

___

sqlite-users mailing list

 <mailto:sqlite-users@mailinglists.sqlite.org> 
sqlite-users@ma

Re: [sqlite] How to convert a datetime column to a date?

[Peter Da Silva]

Don't actually need to convert it to datettime if it's already in the right 
format, do you, or does datetime() do some grooming the source needs?

On 3/22/18, 9:19 AM, "sqlite-users on behalf of David Raymond" 
 wrote:

Use datetime there instead of date or you'll lose the time part of it.

UPDATE foo SET dttm = datetime(trim(dttm,));


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

Re: [sqlite] How to convert a datetime column to a date?

[Peter Da Silva]

On 3/22/18, 9:22 AM, "sqlite-users on behalf of Ron Watkins" 
 
wrote:
> Is there an easy way to  "fix" the data already in the table?

The string trim trick David and Ryan suggested would work. To avoid banging on 
rows already converted, maybe:

UPDATE foo SET dttm = trim(dttm,) WHERE dttm like '''%''';

Note that's 4 single quotes in the first string, and two sets of three single 
quotes in the second.

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

Re: [sqlite] How to convert a datetime column to a date?

[Peter Da Silva]

On 3/22/18, 9:43 AM, "sqlite-users on behalf of R Smith" 
 wrote:
> On 2018/03/22 4:22 PM, Peter Da Silva wrote:
> > Don't actually need to convert it to datettime if it's already in the right 
> > format, do you, or does datetime() do some grooming the source needs?
>  
> Yes indeed, it's down to "vetting" and in no way needed for the  conversion. 
> If datetime(x) understand x and doesn't produce any NULLs,  then x is a valid 
> ISO8601 date (or valid-enough for SQLite) and all is fine.

Sure, but should you do that there? Because it won't tell you you had bad data, 
it'll just lose it.
 

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

Re: [sqlite] MIN() and MAX() of set of row values

[Peter Da Silva]

On 3/30/18, 11:58 AM, "sqlite-users on behalf of Simon Slavin" 
 wrote:
> can think of to do it is to devise a metric to turn a pair (a, b) into one 
> number.

Problem is you can't uniquely order pairs of points. Is (1, 2) greater or 
lesser than (2, 1)?

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

Re: [sqlite] In memory only WAL file

[Peter Da Silva]

You could put the WAL in a tmpfs/ramfs so the DB would only get corrupted if 
the OS crashed, it'd still be there for recovering from application crashes.
 

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

Re: [sqlite] Help using 'attach database' or Why is this an error?

[Peter Da Silva]

Try this:

attach database 'test.db' as con2;

On 4/11/18, 9:51 AM, "sqlite-users on behalf of J Decker" 
 
wrote:

When I attempt to use attach database I get an error about 'no such column'


M:\sqlite3\sqlite3\build>sqlite3 test.db

SQLite version 3.23.0 2018-04-02 11:04:16es
Enter ".help" for usage hints.
sqlite> attach database test.db as con2;
Error: no such column: test.db
sqlite>


I was trying to debug this set of commands, which is actually run on two
connections within the same process... (oh I had misspelled foreign as
foriegn)




PRAGMA foreign_keys=on;

create table `option4_name` (`name_id` char(36) NOT NULL,`name`
varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE);

create table `option4_map` (`option_id` char(36) NOT
NULL,`parent_option_id` char(36) NOT NULL default '0',`name_id` char(36)
NOT NULL default '0',`description` tinytext,CONSTRAINT `parent_key2` UNIQUE
(`parent_option_id`,`name_id`), FOREIGN KEY  (`parent_option_id`)
REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY  (`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE
CASCADE ON DELETE CASCADE);

create table `option4_values` (`option_id` char(36) default '0',`string`
varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT `value_id`
UNIQUE (`option_id`,`segment`)ON CONFLICT IGNORE, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
DELETE CASCADE);select tbl_name,sql from sqlite_master where type='table'
and

BEGIN TRANSACTION;

insert into option4_name (name_id,name) values(
'82093fa2-3d93-11e8-98aa-6e01a5d0577f','.' );

insert into option4_map

(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
);

#SQLITE ERROR:Result of prepare failed? foreign key mismatch -
"option4_map" referencing "option4_name" at char 185[] in [insert into
option4_map

(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
)]
___
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] SELECT with CASE

[Peter Da Silva]

You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which is what 
you're getting, no?

On 4/12/18, 9:47 AM, "sqlite-users on behalf of Csányi Pál" 
 
wrote:

Hi Simon,

2018-04-12 14:32 GMT+02:00 Simon Slavin :
> On 12 Apr 2018, at 1:25pm, Csányi Pál  wrote:
>
>> SELECT CASE TheDate = date('now') WHEN TheDate ...
>
> I don't think that's what you wanted.  Perhaps
>
> SELECT CASE TheDate WHEN date('now') ...
>
> But you should test the output of "date('now')" to make sure it is in the 
format you want.

The output of "date('now') is in format I want:

SELECT date('now');
2018-04-12

I tried this:

SELECT CASE TheDate WHEN date('now') THEN TheDate ELSE date('now','+1
day') END TheDate FROM Dates;
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-12
2018-04-13
2018-04-13
2018-04-13

but I expect the output like this:
2018-04-12

because today date is:
2018-04-12

What am I missing here?

-- 
Best, Pali
___
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] SELECT with CASE

[Peter Da Silva]

One of the lines of the output does indeed have '2018-04-12' as expected.

On 4/12/18, 9:59 AM, "sqlite-users on behalf of Csányi Pál" 
 
wrote:

2018-04-12 16:51 GMT+02:00 Peter Da Silva :
> You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which 
is what you're getting, no?

Yes, indeed.
But I thought the first part would be done:
CASE TheDate WHEN date('now') THEN TheDate

that is, if the TheDate is = date('now') THEN
it should select that TheDate which is equal to the current date:
date('now'), no?
___
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] SELECT with CASE

[Peter Da Silva]

Ah, so if there's two days in a row that aren't school days, you need to be 
able to select a day two or more days in the future.

On 4/12/18, 11:02 AM, "sqlite-users on behalf of Csányi Pál" 
 
wrote:
So when I start the android app on my phone it should display the
school day at that day, or, if on that day we have not a schoolday,
then to display the next school day from the Orak table.
___
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] SELECT with CASE

[Peter Da Silva]

Maybe something like: SELECT MIN(thedate) FROM dates WHERE thedate >= 
date('now');

On 4/12/18, 11:05 AM, "sqlite-users on behalf of Peter Da Silva" 
 wrote:

Ah, so if there's two days in a row that aren't school days, you need to be 
able to select a day two or more days in the future.

On 4/12/18, 11:02 AM, "sqlite-users on behalf of Csányi Pál" 
 
wrote:
So when I start the android app on my phone it should display the
school day at that day, or, if on that day we have not a schoolday,
then to display the next school day from the Orak table.
___
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] memory leak?

[Peter Da Silva]

It is normal in all modern operating systems for unused memory to be allocated 
to buffer cache, so over time the "free" memory Is expected to go down unless 
disk activity is near quiescent.  Some operating systems will combine "cache" 
and "free" memory to hide this from the casual user, but I consider this 
deceptive.

On 4/12/18, 9:34 PM, "sqlite-users on behalf of king3306" 
 
wrote:

first thanks you reply 

After running for some time  i found sqlite3_memory_used return value keep
constant about 2M,but linux free memory is fewer and fewer,i make sure no
other places are leaked,why?

this whether or not a normal behavior?

if not ,how can i to analysis this problem?  

Looking forward to your answer



--
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

Re: [sqlite] Header corruption

[Peter Da Silva]

On 4/17/18, 3:08 PM, "sqlite-users on behalf of Deon Brewis" 
 
wrote:
> So this was a special case of re-using the File handle as per the corruption 
> guide. One just has to be in the mindset that on unix based platforms, a 
> socket is a file handle. (Not instinctive if you're coming from a Windows 
> background).

Even on Windows, wouldn't doing an ssl_shutdown on a socket you'd already 
closed still have a risk of unexpected behavior?
 


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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

[Peter Da Silva]

Having tried to write a generic clean HANDLES ALL CSV reader for speedtables, I 
kind of want to burn Excel with nuclear fire, but that's a side issue. :)

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

[Peter Da Silva]

To another post hating on Excel - Excel has many flaws, but this is not 
one of them, it's a fault of the list-separator setting in the Windows 
OS on which the Excel runs.

CSV is an interchange format, it's for software to communicate with other 
software, so the syntax needs to be independent of the locale since you don't 
know if the sender and recipient are in the same locale. Field separator is 
syntax, so the locale settings should not have any effect on it.

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

[Peter Da Silva]

On 5/1/18, 1:15 PM, "sqlite-users on behalf of R Smith" 
 wrote:

On 1 May 2018, at 6:43pm, Peter Da Silva  
wrote:

> CSV is an interchange format, it's for software to communicate with other 
software, so the syntax needs to be independent of the locale since you don't 
know if the sender and recipient are in the same locale. Field separator is 
syntax, so the locale settings should not have any effect on it.

That is a very bold and idealistic claim.

I'm not claiming what you think I'm claiming. All I'm saying is that CSV is 
meant to communicate with software, not humans. Locales are there for humans. 
Using "the locale says list separates are semicolons" as a justification for 
sometimes using semicolons in an interchange format instead of commas doesn't 
make sense. Do you actually have a reference for that being the reason for 
Excel using semicolons?

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

[Peter Da Silva]

On 5/1/18, 1:42 PM, "sqlite-users on behalf of R Smith" 
 wrote:
My point is that CSV was not necessarily "meant" to be what you say. Who 
exactly "meant" for it to be that? Because the official stuff makes no 
such claim or mention.

Bah. Existential shenanigans. There's probably some pseudo-Latin or German term 
for what I meant.

Your point is however more towards the WHY question, and no, I am not 
sure. But, you know, it's Microsoft - after they made "locales" they 
probably wanted someone to use it, so they bullied Excel division into 
it. :)

From what I've heard the Excel division is the least bulliable part of the 
company. Didn't they maintain their own C compiler for a while to cut down on 
dependencies on the rest of Microsoft? 

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

Re: [sqlite] Fossil Delta Compression in SqLite

[Peter Da Silva]

On 5/6/18, 11:23 AM, "sqlite-users on behalf of Philip Bennefall" 
 
wrote:
Only the requirement for attribution in binaries. That can be 
significant in certain use cases.

One line of text in the documentation provided with the distribution doesn't 
seem burdensome. It's not like the advertising clause in the original BSD 
license... is that what you're thinking of?
 

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

Re: [sqlite] Always call a value-quoting routine

[Peter Da Silva]

On 5/7/18, 2:14 AM, "sqlite-users on behalf of Scott Robison" 
 wrote:
It could just indicate someone with a sense of humor who crafted a
name that looks like an injection attack for their company.

Most likely, or else it's part of an honor system exploit.

http://humorix.org/10277

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

Re: [sqlite] Always call a value-quoting routine

[Peter Da Silva]

Nicely retro-feel website too:

https://droptablecompanies.co.uk/

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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Peter Da Silva]

On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" 
 
wrote:

SQLightning

Do you have a link to the currently updated version of this? Google gives me 
projects that haven't been updated since 2015.

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

Re: [sqlite] shell edit quoting

[Peter Da Silva]

I think you're in "csv mode":

sqlite> select sql from sql_procs where name = 'a';
select * from "mytable" ;

sqlite> .header on
sqlite> .mode csv
sqlite> select sql from sql_procs where name = 'a';
sql
"select * from ""mytable"" ;
"

On 5/13/18, 7:04 PM, "sqlite-users on behalf of David Burgess" 
 wrote:

> And it works for me:

I'm pleased for 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] Multi threaded readers on memory sqlite cannot scale

[Peter Da Silva]

Now I'm even more confused, there's "sqlightning" and "sqlitening". Are these 
different names for the same project? It doesn't seem so. Which was intended?

https://github.com/LMDB/sqlightning

On 5/14/18, 8:47 AM, "sqlite-users on behalf of Bernard Ertl" 
 wrote:

This is the latest:

http://www.sqlitening.planetsquires.com/index.php?topic=9427.0

I contributed to the last SQLitening update.  No one has reported any 
issues that need fixing or updating since that update.  It seems to be working 
quite well/stable. 



> On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" 
 
wrote:

> SQLightning

> Do you have a link to the currently updated version of this? Google gives 
me projects that haven't been updated since 2015.

> ___
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] Multi threaded readers on memory sqlite cannot scale

[Peter Da Silva]

On 5/14/18, 9:17 AM, "sqlite-users on behalf of Bernard Ertl" 
 wrote:

Apologies if I muddled the waters here.  I read the "SQLightning" response 
below as SQLitening.  I didn't know there was a similarly named project out 
there.  I also can't see the beginning of this discussion to have context on 
what was originally asked, so I don't know which project was actually intended.

Ah, OK. Here's more context, don't know if it'll help: 
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2018-May/079224.html

Clemens Ladisch wrote:
> Techno Magos wrote:
>> So, memory sqlite is not really usable with multiple threads (readers).
>> While one might expect  that multiple readers of *memory *content could
>> scale even better than with file content.
> 
> Concurrent accesses to the same in-memory data structures must be
> serialized.  In shared-cache mode, the connections share the cache, while
> on-disk connections each have their own cache.
> 
>> Is there some special mode possible to achieve scaling up throughput with
>> multiple threads for memory sqlite content?
> 
> Put a DB file on a RAM disk.  Or on a normal disk (with looser synchronous
> and journal_mode settings), and rely on the OS file cache.

Or just use SQLightning, which has no scalability limits for readers.
 

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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Peter Da Silva]

On 5/15/18, 1:25 AM, "Howard Chu"  wrote:

    Peter Da Silva wrote:
> On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" 
 
wrote:
> 
>  SQLightning
> 
> Do you have a link to the currently updated version of this? Google gives 
me projects that haven't been updated since 2015.

That's the most recent version. So far I haven't seen any compelling new 
features in subsequent SQLite versions to warrant a resync of the code.

There's been a couple of changes in syntax recently (UPSERT and IS TRUE/IS 
FALSE) that you might want to pick up for compatibility. They make it easier to 
have the same program switch between an SQLITE and PGSQL back end, which we're 
wont to do.

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

Re: [sqlite] UTF8-BOM not disregarded in CSV import

[Peter da Silva]

On 6/26/17, 2:09 AM, "sqlite-users on behalf of Eric Grange" 
 
wrote:
> Alas, there is no end in sight to the pain for the Unicode decision to not 
> make the BOM compulsory for UTF-8.

It’s not actually providing any “byte order” information. It’s only used for 
round-tripping conversion from other formats that actually require one. 
Therefore it is not required.

Perhaps it should have been called “UTF-8 mark” instead? Then it could have 
been arguably recommended.

Regardless, it is what it is.

As for distinguishing UTF-8 from something like 8859.x or CP1255, if the string 
is all-7-bit it’s ASCII which can be safely treated as UTF-8. If it’s not, then

1. It wouldn’t have had a UTF-8 flag anyway, and
2. odds are very good it’s going to contain at least one byte that’s not valid 
UTF-8. Then you’re falling back to guessing which 8859.x variation to try.

My call is, just use UTF-8 everywhere and if you have some program that’s 
producing 8859.x or something else from the last century... fix it. It’s not 
the UTF-8 storage that’s the mess, it’s the non-UTF-8 storage. 

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

Re: [sqlite] UTF8-BOM not disregarded in CSV import

[Peter da Silva]

Just occurred to me: another problem with the BOM is that some people who are 
*not* writing UTF-8 are cargo-culting the BOM in anyway. So you may have to 
scan the whole file to see if it’s really UTF-8 anyway.

You’re better off just assuming UTF-8 everywhere, generating an error (and 
backing out the operation where possible) when you get a failure, and attacking 
the broken sources.

OTOH, defensive programming says drop all the BOMs on input anyway.

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

[sqlite] Tcl sqlite3 bindings - NULL handling in `$db eval $sql array { ... }`

[Peter da Silva]

What’s the best way to handle NULLs out of band when walking the results of a 
query:

$sqlite_db eval “SELECT * FROM table ...” array {
...
}

In other Tcl database bindings it’s common to return arrays containing possible 
null values with NULL values simply unset, so `[info exists]` can be used to 
distinguish nulls from the empty string. For example in Pgtcl you can specify 
`pg_select ... -withoutnulls` to omit nulls from returned rows - I don’t see 
anything analogous in sqlite3 but it’s possible I’m missing something.

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

Re: [sqlite] Tcl sqlite3 bindings - NULL handling in `$db eval $sql array { ... }`

[Peter da Silva]

On 6/26/17, 9:00 AM, "sqlite-users on behalf of Richard Hipp" 
 
wrote:
> The "db nullvalue STRING" command lets you translate NULL values into the 
> string value of your choice.  But there is not (currently) a way to cause 
> NULL values to unset the corresponding member of the array.

That’s what I’d call an “in-band” solution. It’s got problems when writing code 
that needs to generalize to unknown datasets...

Also, does `db nullvalue` return the current null value, or would you need to 
track that externally? The documentation doesn’t say, but let’s see...

% test nullvalue {\N}
\N
% test nullvalue
\N

So that’s workable if you know you have a guaranteed unique token-string you 
can use. Also, may want to update https://sqlite.org/tclsqlite.html to note 
that.

Anyway, I ran into this testing my sqlite3 bridge for Pgtcl. It doesn’t seem 
like it would be hard to implement a clone of `$db select` that will do what I 
want, but it would be nice to have a standard binding.
 

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

Re: [sqlite] UTF8-BOM not disregarded in CSV import

[Peter da Silva]

I didn’t mean to imply you had to scan the whole content for a BOM, but rather 
for illegal characters in the absence of a BOM.

On 6/26/17, 10:02 AM, "sqlite-users on behalf of Simon Slavin" 
 wrote:

Folks, I’m sorry to interrupt but I’ve just woken up to 11 posts in this 
thread and I see a lot of inaccurate 'facts' posted here.  Rather than pick up 
on statements in individual posts (which would unfairly pick on some people as 
being less accurate than others) I’d like to post facts straight from 
Unicode.org and let you reassess some of the things written earlier.

Position of BOM
---

A Byte Order Mark is valid only at the beginning of a data stream.  You 
never need to scan a file for it.  If you find the sequence of characters for a 
BOM in the middle of a datastream, it’s not a BOM and you should handle it as 
if those were Unicode characters in the current encoding (for example ZERO 
WIDTH NON-BREAKING SPACE).  There is no unicode sequence which means "Encoding 
is changing.  The next sequence is the new BOM."

If you look at the first few bytes of a file and can’t identify one of the 
BOMs, there isn’t (a valid) one for that data stream and you can assume the 
default which is UTF-8.  This is done to allow the use of ASCII text in a 
datastream which was designed for Unicode.  If you do not implement it, your 
software will fail for inputs limited by small chipsets or old APIs which can 
handle only ASCII.

What BOMs indicate
--

BOMs indicate both which type of UTF is in use as well as the byte order.  
In other words you can not only tell UTF-16LE from UT-16BE, but you can also 
tell UTF-32LE from UTF-16LE.  To identify the encoding, check the beginning of 
the datastream for these five sequences, starting from the first one listed:

00 00 FE FF UTF-32, big-endian
FF FE 00 00 UTF-32, little-endian
FE FF   UTF-16, big-endian
FF FE   UTF-16, little-endian
EF BB BFUTF-8

As you can see, Having a datastream start with FE FF does not definitely 
tell you that it’s a UTF-16 datastream.  Be careful.  Also be careful of 
software/protocols/APIs which assume that 00 bytes indicate the end of a 
datastream.

As you can see, although the BOMs for 16 and 32 bit formats are the same 
size as those formats, this is not true of the BOM for UTF-8.  Be careful.

How to handle BOMs in software/protocols/APIs
——

Establish whether each field can handle all kinds of Unicode and 
understands BOMs, or whether the field understands only one kind of Unicode.  
If the latter, state this in the documentation, including which kind of Unicode 
it understands.

There is no convention for "This software understands both UTF-16BE and 
UTF-16LE but nothing else.".  If it handles any BOMs, it should handle all 
five.  However, it can handle them by identifying, for example, UTF-32BE and 
returning an error indicating that it can’t handle any encodings which aren’t 
16 bit.

Try to be consistent across all fields in your protocol/API.

References:


___
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] Tcl sqlite3 bindings - NULL handling in `$db eval $sql array { ... }`

[Peter da Silva]

On 6/26/17, 11:15 AM, "drhsql...@gmail.com on behalf of Richard Hipp" 
 wrote:
> If you get the latest check-in (https://www.sqlite.org/src/info/trunk) there 
> is a new option on the "sqlite3" command called "-unsetnull 1" which causes 
> "db eval" to work as you desire - by unsetting the array elements for NULL 
> values.  This option is off by default for legacy compatibility.

Could that be an option on the eval command rather than the db, so that 
packages can safely use the feature on databases they don’t “own”?

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

Re: [sqlite] Tcl sqlite3 bindings - NULL handling in `$db eval $sql array { ... }`

[Peter da Silva]

This is really a pretty major change.

Our experience with the comparable options in Pgtcl and Speedtables is that 
there is likely a lot of code that assumes that all array elements are set in 
`$db eval “...” array { ... }` blocks that will error out with this change. I 
don’t think I would be comfortable using this in existing code without doing an 
extensive audit... and for third party packages getting changes propagated 
upstream.

Making it a per-call option allows new code to use it safely, without impacting 
any other components that might be using the same database.

On 6/26/17, 11:31 AM, "drhsql...@gmail.com on behalf of Richard Hipp" 
 wrote:

On 6/26/17, Peter da Silva  wrote:
> On 6/26/17, 11:15 AM, "drhsql...@gmail.com on behalf of Richard Hipp"
>  wrote:
>> If you get the latest check-in (https://www.sqlite.org/src/info/trunk)
>> there is a new option on the "sqlite3" command called "-unsetnull 1" 
which
>> causes "db eval" to work as you desire - by unsetting the array elements
>> for NULL values.  This option is off by default for legacy compatibility.
>
> Could that be an option on the eval command rather than the db, so that
> packages can safely use the feature on databases they don’t “own”?
>

It is per-connection.

The change is sufficient minor and obscure that 99.9% of packages
should work the same regardless of the setting.  The only reason for
making it an option rather than the only way things happen is for the
other 0.1% of application where it really will make a difference.

-- 
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

Re: [sqlite] Tcl sqlite3 bindings - NULL handling in `$db eval $sql array { ... }`

[Peter da Silva]

On 6/26/17, 11:48 AM, "drhsql...@gmail.com on behalf of Richard Hipp" 
 wrote:
> OK.  I'll back out the change, then.

That’s definitely safer, it’s a super useful capability but needs to be applied 
selectively.
 

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

Re: [sqlite] [OT] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)

[Peter da Silva]

On 6/27/17, 4:02 PM, "sqlite-users on behalf of Keith Medcalf" 
 
wrote:
> Nowadays we use 8 bits for data with no parity, no error correction, and no 
> timing bits.  Cuz when things screw up we want them to REALLY screw up ... 
> and remain undetectable.

Nowadays we use packet checksums and retransmission of corrupted or missing 
packets. 

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

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Peter da Silva]

On 6/29/17, 1:22 AM, "sqlite-users on behalf of Robert M. Münch" 
 wrote:
> Hi, sorry, should have mentioned that this doesn't work in my case, because 
> we are building the column placeholders dynamically. So, we would have to 
> handle putting the necessary column names in there all the time, which is not 
> feasible.

I have been generating SQL dynamically on a number of projects over the past 
10+ years, and have found that generating INSERT with column names in is (a) 
not really that much extra work, and (b) eliminates a whole class of bugs 
involving schema changes or even schema regeneration. It’s genuinely worth 
taking the time to do it right.

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

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Peter da Silva]

On 6/29/17, 5:20 AM, "sqlite-users on behalf of R Smith" 
 
wrote:
> SQLite isn't helping the confusion in this case, because it allows 
> double-quotes to be regarded as string values IF an identifier with that name 
> doesn't exist. This is of course all good and well until you misspell a 
> column name...

Shades of REXX.

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

Re: [sqlite] [OT] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)

[Peter da Silva]

I always saw byte as something that was relevant for systems that could address 
objects smaller than words... “byte addressed” machines. The term was mnemonic 
for something bigger than a bit and smaller than a word. It was usually 8 bits 
=but there were 36-bit machines that were byte addressable 9 bits at a time. 
The DECsystem 10 guys also referred to the other subdivisions of their 36 bit 
words as bytes, sometimes, they could be 6, 7, 8, or 9 bits long. I think they 
had special instructions for operating on them, but they weren’t directly 
addressable.

There was also a “nibble”, smaller than a “byte”, which was always 4 bits (one 
hex digit). I don’t think any of the octal people used the word for their three 
bit digits.
 

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

Re: [sqlite] Version 3.20.0 coming soon...

[Peter da Silva]

Nicely put. There’s a couple of prepared statements that I’m using that 
absolutely will benefit from this, now I understand it.

On 7/13/17, 3:30 PM, "sqlite-users on behalf of Marc L. Allen" 
 wrote:

If it's going to change in future releases, then perhaps your "key point" 
is the main takeaway that should be documented?

Something like:

SQLITE_PREPARE_PERSISTENT is a hint to SQLite that the prepared statement 
is going to linger for a long time and that SQLite should take that into 
account for internal performance optimizations.  At present the only 
optimization is to avoid using lookaside... etc.

Marc


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Thursday, July 13, 2017 4:22 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Version 3.20.0 coming soon...

On 7/13/17, Dominique Pellé  wrote:
>
> Looking at the description of the new SQLITE_PREPARE_PERSISTENT at 
> https://sqlite.org/draft/c3ref/c_prepare_persistent.html
> it's not clear to me what are the benefits.
> Shouldn't the description say why it can be beneficial to use it or 
> not?
>

That documentation is the contract of what  SQLITE_PREPARE_PERSISTENT is 
suppose to do, so it needs to be a little vague in order to allow for changes 
in the future.

In the current implementation, SQLITE_PREPARE_PERSISTENT avoids using 
lookaside memory to store pieces of the prepared statement, to avoid soaking up 
all of the fast lookaside memory and preventing it from being used for more 
useful things.  But the effect of SQLITE_PREPARE_PERSISTENT might change in 
future releases.  The key point (and the part of this interface that will not 
change) is that SQLITE_PREPARE_PERSISTENT is a hint to SQLite that the prepared 
statement is going to linger for a long time.

How would you recommend that be stated more clearly?

--
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



Confidentiality notice: This e-mail is intended solely for use of the 
individual or entity to which it is addressed and may contain information that 
is proprietary, privileged, company confidential and/or exempt from disclosure 
under applicable law. If the reader is not the intended recipient or agent 
responsible for delivering the message to the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication 
in error, please notify the sender by reply e-mail or collect telephone call 
and delete or destroy all copies of this e-mail message, any physical copies 
made of this e-mail message and/or any file attachment(s).
___
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] Return value of Tcl interface to wal_hook

[Peter da Silva]

In wal_hook we have:

** The callback function should normally return [SQLITE_OK].  ^If an error
** code is returned, that error will propagate back up through the
** SQLite code base to cause the statement that provoked the callback
** to report an error, though the commit will have still occurred. If the
** callback returns [SQLITE_ROW] or [SQLITE_DONE], or if it returns a value
** that does not correspond to any valid SQLite error code, the results
** are undefined.

The Tcl interface doesn’t specify whether this maps to a successful return (ie, 
returning TCL_OK) with any errors propagated by the Tcl error mechanism (ie, 
returning TCL_ERROR), or whether the wal_hook routine needs to return SQLITE_OK 
(0). Examination of tea/generic/tclsqlite3.c indicates that it’s the latter. If 
you don’t want to invoke nasal demons, you probably need to end the hook with 
“return 0”.

Documentation should probably make this explicit on the Tcl interface page.


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

Re: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[Peter Da Silva]

I notice that “read_uncommitted pragma” is spelled “PRAGMA read_uncommitted” in 
one place. The links all match. This small inconsistency is probably a mistake.


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

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

[Peter da Silva]

If the datr/time is stored internally as utc iso8601 text then it will
remain compatible with old versions and can implement whatever new behavior
is needed on new versions. The bigger question is 'what new behavior'? The
only nee behavior seems to be 'let this third party package see it as a
date', which it should be able to figure out by looking at the schema.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Attached databases and union view.

[Peter da Silva]

Have an existing application that's pushing the limit on how fast it can
read data and add it to the database, and thinking of sharding the database
file so I can have multiple writers writing to shards of the main tables.

ATTACH DATABASE 'shard0.sqlite' as shard0;
ATTACH DATABASE 'shard1.sqlite' as shard1;
...

CREATE TEMPORARY VIEW sharded_main_table AS
SELECT col,col,col...,all_columns_basically FROM shard0.main_table
UNION
SELECT col,col,col...,all_columns_basically FROM shard1.main_table
...;

What's the best way to construct this union view so the query optimizer
won't be horribly confused? If I run something like "SELECT count(*) FROM
sharded_main_table WHERE ident LIKE 'pattern';" it's about 20 times slower
than the same query against the original main_table. Running the query
against each shardN.main_table it's actually faster (in total time for all
queries in sequence) than running it against the original table.

Is there a better way to construct the view, or am I going to get best
query performance by making my code shard-aware?

All the original indexes on main_table have been copied to the shard
databases.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached databases and union view.

[Peter da Silva]

Database is on tmpfs and periodically snapshotted to SSD. There are
bottlenecks upstream of sqlite that we can see in traces.

On Thu, Aug 22, 2019 at 10:36 AM Warren Young  wrote:

> On Aug 22, 2019, at 9:27 AM, Peter da Silva  wrote:
> >
> > Have an existing application that's pushing the limit
>
> If the limit is in hardware, shards won’t help.
>
> For example, a SQLite DB on a 7200 RPM spinning disk is limited to about
> 60 transactions per second under the stock SQLite fsync logic, since each
> takes 2 revolutions to commit.  (One to write to the journal, and one to
> commit the journal entry.)  Writes to multiple shards only get to share a
> platter rotation if there is no seek between writes.
>
> The limits are higher for SSDs, but there’s still a low limit on the
> number of parallel writes.
> ___
> 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] Attached databases and union view.

[Peter da Silva]

Legit. I'll try that.

On Thu, Aug 22, 2019 at 11:33 AM David Raymond 
wrote:

> I don't know how smart the planner is, but as a thought, would UNION ALL
> make any improvement over just UNION? With just UNION it has to
> de-duplicate all the subquery results whereas with UNION ALL it would be
> free to separate all the various subqueries from each other.
>
> Or do you actually need the UNION to de-dupe stuff?
>
>
> -Original Message-
> From: sqlite-users  On
> Behalf Of Peter da Silva
> Sent: Thursday, August 22, 2019 11:28 AM
> To: SQLite mailing list 
> Subject: [sqlite] Attached databases and union view.
>
> Have an existing application that's pushing the limit on how fast it can
> read data and add it to the database, and thinking of sharding the database
> file so I can have multiple writers writing to shards of the main tables.
>
> ATTACH DATABASE 'shard0.sqlite' as shard0;
> ATTACH DATABASE 'shard1.sqlite' as shard1;
> ...
>
> CREATE TEMPORARY VIEW sharded_main_table AS
> SELECT col,col,col...,all_columns_basically FROM shard0.main_table
> UNION
> SELECT col,col,col...,all_columns_basically FROM shard1.main_table
> ...;
>
> What's the best way to construct this union view so the query optimizer
> won't be horribly confused? If I run something like "SELECT count(*) FROM
> sharded_main_table WHERE ident LIKE 'pattern';" it's about 20 times slower
> than the same query against the original main_table. Running the query
> against each shardN.main_table it's actually faster (in total time for all
> queries in sequence) than running it against the original table.
>
> Is there a better way to construct the view, or am I going to get best
> query performance by making my code shard-aware?
>
> All the original indexes on main_table have been copied to the shard
> databases.
> ___
> 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] Attached databases and union view.

[Peter da Silva]

Originally Tcl/native Tcl binding, now a C++ extension calling the
C-binding that's a Tcl extension itself.

On Thu, Aug 22, 2019 at 11:17 AM test user 
wrote:

> What language/binding library are you using?
>
> On Thu, 22 Aug 2019 at 16:45, Peter da Silva  wrote:
>
> > Database is on tmpfs and periodically snapshotted to SSD. There are
> > bottlenecks upstream of sqlite that we can see in traces.
> >
> > On Thu, Aug 22, 2019 at 10:36 AM Warren Young 
> wrote:
> >
> > > On Aug 22, 2019, at 9:27 AM, Peter da Silva  wrote:
> > > >
> > > > Have an existing application that's pushing the limit
> > >
> > > If the limit is in hardware, shards won’t help.
> > >
> > > For example, a SQLite DB on a 7200 RPM spinning disk is limited to
> about
> > > 60 transactions per second under the stock SQLite fsync logic, since
> each
> > > takes 2 revolutions to commit.  (One to write to the journal, and one
> to
> > > commit the journal entry.)  Writes to multiple shards only get to
> share a
> > > platter rotation if there is no seek between writes.
> > >
> > > The limits are higher for SSDs, but there’s still a low limit on the
> > > number of parallel writes.
> > > ___
> > > 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] Attached databases and union view.

[Peter da Silva]

Still a bit over 3x slower on queries but that's a 7x performance
improvement.

On Thu, Aug 22, 2019 at 11:40 AM Peter da Silva  wrote:

> Legit. I'll try that.
>
> On Thu, Aug 22, 2019 at 11:33 AM David Raymond 
> wrote:
>
>> I don't know how smart the planner is, but as a thought, would UNION ALL
>> make any improvement over just UNION? With just UNION it has to
>> de-duplicate all the subquery results whereas with UNION ALL it would be
>> free to separate all the various subqueries from each other.
>>
>> Or do you actually need the UNION to de-dupe stuff?
>>
>>
>> -Original Message-
>> From: sqlite-users  On
>> Behalf Of Peter da Silva
>> Sent: Thursday, August 22, 2019 11:28 AM
>> To: SQLite mailing list 
>> Subject: [sqlite] Attached databases and union view.
>>
>> Have an existing application that's pushing the limit on how fast it can
>> read data and add it to the database, and thinking of sharding the
>> database
>> file so I can have multiple writers writing to shards of the main tables.
>>
>> ATTACH DATABASE 'shard0.sqlite' as shard0;
>> ATTACH DATABASE 'shard1.sqlite' as shard1;
>> ...
>>
>> CREATE TEMPORARY VIEW sharded_main_table AS
>> SELECT col,col,col...,all_columns_basically FROM shard0.main_table
>> UNION
>> SELECT col,col,col...,all_columns_basically FROM shard1.main_table
>> ...;
>>
>> What's the best way to construct this union view so the query optimizer
>> won't be horribly confused? If I run something like "SELECT count(*) FROM
>> sharded_main_table WHERE ident LIKE 'pattern';" it's about 20 times slower
>> than the same query against the original main_table. Running the query
>> against each shardN.main_table it's actually faster (in total time for all
>> queries in sequence) than running it against the original table.
>>
>> Is there a better way to construct the view, or am I going to get best
>> query performance by making my code shard-aware?
>>
>> All the original indexes on main_table have been copied to the shard
>> databases.
>> ___
>> 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] Attached databases and union view.

[Peter da Silva]

Looks interesting, but if I'm reading the descriptions right I don't think
those would help: my partitioning can be on a hash of a unique text ID or
on a geographic area, and both of those seem to be based on unique rowid
ranges.

On Thu, Aug 22, 2019 at 1:23 PM Keith Medcalf  wrote:

>
> Have you looked at the swarmvtab or unionvtab extension?
>
> https://www.sqlite.org/unionvtab.html
> https://www.sqlite.org/swarmvtab.html
>
> Which can "avoid" having to write your own unions.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Peter da Silva
> >Sent: Thursday, 22 August, 2019 11:57
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Attached databases and union view.
> >
> >Still a bit over 3x slower on queries but that's a 7x performance
> >improvement.
> >
> >On Thu, Aug 22, 2019 at 11:40 AM Peter da Silva 
> >wrote:
> >
> >> Legit. I'll try that.
> >>
> >> On Thu, Aug 22, 2019 at 11:33 AM David Raymond
> >
> >> wrote:
> >>
> >>> I don't know how smart the planner is, but as a thought, would
> >UNION ALL
> >>> make any improvement over just UNION? With just UNION it has to
> >>> de-duplicate all the subquery results whereas with UNION ALL it
> >would be
> >>> free to separate all the various subqueries from each other.
> >>>
> >>> Or do you actually need the UNION to de-dupe stuff?
> >>>
> >>>
> >>> -Original Message-
> >>> From: sqlite-users 
> >On
> >>> Behalf Of Peter da Silva
> >>> Sent: Thursday, August 22, 2019 11:28 AM
> >>> To: SQLite mailing list 
> >>> Subject: [sqlite] Attached databases and union view.
> >>>
> >>> Have an existing application that's pushing the limit on how fast
> >it can
> >>> read data and add it to the database, and thinking of sharding the
> >>> database
> >>> file so I can have multiple writers writing to shards of the main
> >tables.
> >>>
> >>> ATTACH DATABASE 'shard0.sqlite' as shard0;
> >>> ATTACH DATABASE 'shard1.sqlite' as shard1;
> >>> ...
> >>>
> >>> CREATE TEMPORARY VIEW sharded_main_table AS
> >>> SELECT col,col,col...,all_columns_basically FROM
> >shard0.main_table
> >>> UNION
> >>> SELECT col,col,col...,all_columns_basically FROM
> >shard1.main_table
> >>> ...;
> >>>
> >>> What's the best way to construct this union view so the query
> >optimizer
> >>> won't be horribly confused? If I run something like "SELECT
> >count(*) FROM
> >>> sharded_main_table WHERE ident LIKE 'pattern';" it's about 20
> >times slower
> >>> than the same query against the original main_table. Running the
> >query
> >>> against each shardN.main_table it's actually faster (in total time
> >for all
> >>> queries in sequence) than running it against the original table.
> >>>
> >>> Is there a better way to construct the view, or am I going to get
> >best
> >>> query performance by making my code shard-aware?
> >>>
> >>> All the original indexes on main_table have been copied to the
> >shard
> >>> databases.
> >>> ___
> >>> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] rationale for private archives of the SQLite mailing list and suppressed reply-to

[Peter da Silva]

I think you're conflating things. A mail server speaks SMTP for both
inbound and outbound, IMAP/POP/webmail is all part of the user interface
stack... as would be the webforum component in any mailing list/web forum
scheme.

On Mon, 14 Oct 2019, 20:45 Warren Young,  wrote:

> On Oct 14, 2019, at 3:04 PM, Keith Medcalf  wrote:
> >
> > On Monday, 14 October, 2019 14:18, Warren Young 
> wrote:
> >
> >> Fossil Forums allow you to subscribe to email notifications.  From the
> >> reader’s perspective, it’s really very little different from the current
> >> Mailman based scheme.
> >
> > The preceding paragraph is completely at odds with the following
> paragraph, and taken together, they are completely illogical and
> inconsistent.
>
> You’re conflating inbound and outbound paths.  The ability to send email
> implies but does not require the ability to receive email.
>
> ...Which is why they’re often entirely different stacks, speaking
> different protocols!  E.g. SMTP outbound via Postfix, IMAP inbound via
> Dovecot.
>
> > All it needs is to be able to "read and process" RFC-2822 formatted
> message files that are found in an "inbound for me” directory
>
> That’s certainly one way that some email servers work.  The most common
> such scheme is called Maildir.
>
> But there’s probably at least half a dozen other ways it can work: mbox,
> MySQL store, PostgreSQL store, whatever it is that MS Exchange does that’s
> incompatible with the rest of the world…
>
> There are currently four supported outbound email setups in Fossil, and a
> stub for a fifth:
>
> https://fossil-scm.org/home/doc/trunk/www/alerts.md#advanced
>
> Why would inbound be different?
>
> Fossil isn’t in a position where it can require a specific SMTP server.
> It has to run on pretty much every common desktop and server platform.  You
> have to get pretty far down the long tail of OSes before you find one that
> Fossil doesn’t get used on daily by someone.  Therefore, we have to support
> approximately everything.
>
> On top of integrating with all common SMTP stacks, drh long ago stated a
> wish to write his own SMTP server.  (The latter being why Fossil has the
> start of one included!)  This should not surprise you if you’ve followed
> his career. :)
>
> The last time I counted up the pages of RFCs you have to implement to
> speak to a large fraction of the Internet email infrastructure — which was
> one of the times this argument came up on this mailing list! — it was
> something like 500 pages of standardese.  It is not just RFC-2822.  Getting
> to something useful will take time, which comes out of the time budget for
> SQLite, Fossil, etc.
>
> There is the option of writing glue software between Fossil and whatever
> SMTP infrastructure you already have, but no one’s bothered to do that in
> the year or so that Fossil Forums have been in steady use.  To me, that
> speaks more of the desirability of inbound email submission than about its
> inherent difficulty.
> ___
> 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] Table was deleted on macOS

[Peter da Silva]

Check the local time machine backups? Even if you don't have a TM backup
drive Mojave maintains on-drive backups.

On Tue, 15 Oct 2019, 16:54 Simon Slavin,  wrote:

> Sorry, I have no other ideas.  There is no reason for a table to disappear.
> ___
> 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 do these 2 updates give different results?

[Peter da Silva]

>
> This almost seems like a job for a view.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Things you shouldn't assume when you store names

[Peter da Silva]

My last name has a space in it. It's been less than a month since the last
time it was rejected by a form. One of my oldest online friends has only
one name. Assume nothing, permit everything.

On Wed, 13 Nov 2019, 15:23 Swithun Crowe, 
wrote:

> Hello
>
> SS> Those are all excellent examples of why you should /not/ split up a
> SS> name into components.  For artists of all sorts (including the author
> SS> I used) what you care about is their publishing name.  Plus, how do
> SS> you identify the part of a name which you would call 'surname' ?
>
> One needs domain knowledge - knowing that Arouet is the surname of the
> artist known as Voltaire. In my use cases, this is possible (the
> researchers do it, not me). And by encoding one's domain knowledge, one is
> preserving it for future users who may not have it.
>
> Of course people are most interested in the publishing name, so Voltaire
> comes under V. But that isn't the only use that people might have for the
> data. So splitting the names and adding extra logic to display them in
> different ways in different contexts is, I think, the optimal solution.
>
> SS> Why would you want to sort by surname ?  Why is it important that
> SS> "Harris' is shown below "Harrington" ?  Don't substring searches help
> SS> you more than sorted lists ?  Wouldn't you miss the 'Parkes' family
> SS> name from John Wyndham and the 'Ruiz' from Picasso if you were
> SS> searching a sorted list ?
>
> If one had a million names from many different cultures (e.g. a customer
> database), then there may be no need/possibility to maximise reuse of the
> data, and a substring search would be the only way to find the name you
> want. But for a database of 200 French playwrights, a suitably sorted list
> is what users want.
>
> Swithun.
> ___
> 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] Things you shouldn't assume when you store names

[Peter da Silva]

It just drew a red box and told me to fill out all fields.

Then I enter daSilva.

Worse are the ones that don't say anything and attach the 'da' somewhere
random without telling me.

Then I show up and the nurse wastes time looking me up some other way, and
tells me I'm Peterda Silva.

On Wed, 13 Nov 2019, 15:56 Jose Isaias Cabrera,  wrote:

>
> Peter da Silva, on Wednesday, November 13, 2019 04:37 PM, wrote...
> >
> > My last name has a space in it. It's been less than a month since the
> last
> > time it was rejected by a form. One of my oldest online friends has only
> > one name. Assume nothing, permit everything.
>
> We still need to know that it is your last name and not your first name,
> or 2nd name, or... :-)  By the way, what do you do when the form says,
>
> "Please fix your last name: No spaces allowed!"
>
> Do you just type daSilva?
>
> 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] database disk image is malformed

[Peter da Silva]

I have been bitten by this a couple of times, so now I'm super-conservative
about how I deal with this.

What I do is have any parent database setup done by having the parent spawn
a child process to do the actual database work, and return any data the
parent needs in the status or (if more than a success status is needed)
through a pipe.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using application_id

[Peter da Silva]

> If you stick to lower or upper case letters, could encode up to 6 chars in
> the app_id. --DD

The return of RADIX-50.

https://en.wikipedia.org/wiki/DEC_Radix-50

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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Peter da Silva]

Assuming I'm understanding what the original message was about.

Isn't this what BEGIN; INSERT OR IGNORE; UPDATE; COMMIT is the right tool for?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding the whole C89/C90 language compliance debacle ...

[Peter da Silva]

Strictly compliant code strictly doesn't need to do anything at all,
conforming to strict standards instead of what compilers actually do is an
interesting intellectual exercise but is not necessarily useful.

On Sat, Nov 23, 2019, 16:27 Dennis Clarke  wrote:

>
> I may be the only person that does this sort of testing for my own
> reasons and perhaps for sanity checking also. I tend to think that if
> something is written to be compliant with C89/C90 then I should be able
> to run the most strict compliance compiler flags in creation and be
> perfectly happy.
>
> However that is not the case here wwith gcc 9.2.0 on RHEL 7.4 :
>
> ../sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009.compile.log
> /opt/bw/gcc9/bin/gcc  -std=iso9899:1990 -pedantic -Wpedantic
> -pedantic-errors -O0 -m64 -g -march=k8 -mtune=k8
> -Wl,-rpath=/opt/bw/lib,--enable-new-dtags -fno-builtin -malign-double -o
> mksourceid
>
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksourceid.c
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksourceid.c:23:25:
>
> error: ISO C90 does not support 'long long' [-Wlong-long]
> 23 |   typedef unsigned long long int u64;
>| ^~~~
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksourceid.c:
>
> In function 'KeccakF1600Step':
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksourceid.c:74:5:
>
> error: use of C99 long long integer constant [-Wlong-long]
> 74 | 0x0001ULL,  0x8082ULL,
>| ^
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksourceid.c:74:29:
>
> error: use of C99 long long integer constant [-Wlong-long]
> 74 | 0x0001ULL,  0x8082ULL,
>| ^
>
> etc etc
>
> If we look :
>
> boe13$ head -80 tool/mksourceid.c | tail
>u64 C0, C1, C2, C3, C4;
>u64 D0, D1, D2, D3, D4;
>static const u64 RC[] = {
>  0x0001ULL,  0x8082ULL,
>  0x8000808aULL,  0x800080008000ULL,
>  0x808bULL,  0x8001ULL,
>  0x800080008081ULL,  0x80008009ULL,
>  0x008aULL,  0x0088ULL,
>  0x80008009ULL,  0x800aULL,
>  0x8000808bULL,  0x808bULL,
> boe13$
>
> Here I am using some very strict flags and note the -fno-builtin :
>
> boe13$ echo $CFLAGS
> -std=iso9899:1990 -pedantic -Wpedantic -pedantic-errors -O0 -m64
>   -g -march=k8 -mtune=k8 -Wl,-rpath=/opt/bw/lib,--enable-new-dtags
>   -fno-builtin -malign-double
> boe13$
>
> So that clearly isn't going to work here.
>
> So let's try -std=iso9899:1999 and see a different type of failure :
>
> /opt/bw/gcc9/bin/gcc  -std=iso9899:1999 -O0 -m64 -g -pedantic
> -pedantic-errors -Wpedantic -march=k8 -mtune=k8
> -Wl,-rpath=/opt/bw/lib,--enable-new-dtags -fno-builtin -malign-double -o
> mksourceid
>
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksourceid.c
> tclsh8.7
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksqlite3h.tcl
>
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009
>  >sqlite3.h
> /opt/bw/gcc9/bin/gcc  -std=iso9899:1999 -O0 -m64 -g -pedantic
> -pedantic-errors -Wpedantic -march=k8 -mtune=k8
> -Wl,-rpath=/opt/bw/lib,--enable-new-dtags -fno-builtin -malign-double -o
> mkkeywordhash
>
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mkkeywordhash.c
> ./mkkeywordhash >keywordhash.h
> /opt/bw/gcc9/bin/gcc  -std=iso9899:1999 -O0 -m64 -g -pedantic
> -pedantic-errors -Wpedantic -march=k8 -mtune=k8
> -Wl,-rpath=/opt/bw/lib,--enable-new-dtags -fno-builtin -malign-double -o
> lemon
>
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/lemon.c
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/lemon.c:
>
> In function 'main':
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/lemon.c:1639:21:
>
> error: ISO C forbids conversion of function pointer to object pointer
> type [-Wpedantic]
>   1639 | {OPT_FSTR, "d", (char*)&handle_d_option, "Output directory.
>   Default '.'"},
>| ^
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/lemon.c:1640:21:
>
> error: ISO C forbids conversion of function pointer to object pointer
> type [-Wpedantic]
>   1640 | {OPT_FSTR, "D", (char*)handle_D_option, "Define an %ifdef
> macro."},
>| ^
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/lemon.c:1654:21:
>
> error: ISO C forbids conversion of function pointer to object pointer
> type [-Wpedantic]
>   1654 | {OPT_FSTR, "T", (char*)handle_T_option, "Specify a template
> file."},
>|

Re: [sqlite] Causal profiling

[Peter da Silva]

Also spaghetti inheritance is a thing.

On Wed, Jan 1, 2020, 19:19 D Burgess  wrote:

> > I’ve spent too much time lately trying to figure out or debug hellacious
> C spaghetti code
>
> And I’ve spent too much time lately trying to figure out or debug
> hellacious C++ spaghetti code
>
> Someone who writes bad C,   will write even worse C++
> ___
> 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] Best way to store key,value pairs

[Peter da Silva]

Another thing to consider is that RFC-822/1036 (et seq) headers are not
inherently unique and some are repeated, especially since there's software
that treats Usenet and Mail headers interchangeably. Also, debugging may
require being able to see the exact layout of the headers as received. The
safest solution is to add columns for the specific instances of the
specific headers that you need to index, and then store the original
headers unchanged as a blob or a big text column. Converting to json and
back without potentially losing data (even if you don't think you will need
that data) takes some care.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New word to replace "serverless"

[Peter da Silva]

Local?

On Mon, 27 Jan 2020, 16:19 Richard Hipp,  wrote:

> For many years I have described SQLite as being "serverless", as a way
> to distinguish it from the more traditional client/server design of
> RDBMSes.  "Serverless" seemed like the natural term to use, as it
> seems to mean "without a server".
>
> But more recently, "serverless" has become a popular buzz-word that
> means "managed by my hosting provider rather than by me."  Many
> readers have internalized this new marketing-driven meaning for
> "serverless" and are hence confused when they see my claim that
> "SQLite is serverless".
>
> How can I fix this?  What alternative word can I use in place of
> "serverless" to mean "without a server"?
>
> Note that "in-process" and "embedded" are not adequate substitutes for
> "serverless".  An RDBMS might be in-process or embedded but still be
> running a server in a separate thread. In fact, that is how most
> embedded RDBMSes other than SQLite work, if I am not much mistaken.
>
> When I say "serverless" I mean that the application invokes a
> function, that function performs some task on behalf of the
> application, then the function returns, *and that is all*.  No threads
> are left over, running in the background to do housekeeping.  The
> function does send messages to some other thread or process.  The
> function does not have an event loop.  The function does not have its
> own stack. The function (with its subfunctions) does all the work
> itself, using the callers stack, then returns control to the caller.
>
> So what do I call this, if I can no longer use the word "serverless"
> without confusing people?
>
> "no-server"?
> "sans-server"?
> "stackless"?
> "non-client/server"?
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New word to replace "serverless"

[Peter da Silva]

Server-free sounds good. Standalone too. Integrated maybe?

On Mon, Jan 27, 2020, 17:54 Donald Shepherd 
wrote:

> On Tue, 28 Jan 2020 at 10:19 am, Richard Hipp  wrote:
>
> > daemon-less?
> > --
> > D. Richard Hipp
> > d...@sqlite.org
>
>
> In-process? Same concept but defining it by what it is rather than what it
> isn't.
>
> Regards,
> Donald Shepherd.
>
> > 
> ___
> 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] What is a "journal"?

[Peter da Silva]

In programming a journal is a file or other data structure containing a
series of change records but can be replayed to reconstruct an operation.

On Thu, 30 Jan 2020, 05:29 Peng Yu,  wrote:

> Hi,
>
> https://www.sqlite.org/lockingv3.html#rollback
>
> "When a process wants to change a database file (and it is not in WAL
> mode), it first records the original unchanged database content in a
> rollback journal. The rollback journal is an ordinary disk file that
> is always located in the same directory or folder as the database file
> and has the same name as the database file with the addition of a
> -journal suffix. The rollback journal also records the initial size of
> the database so that if the database file grows it can be truncated
> back to its original size on a rollback."
>
> I got confused about the above statement about the rollback journal.
> The sentence "The rollback journal ..." appears to be a definition.
> But it is not.
>
> I think that the following can be considered as a definition. Given
> the above sentence appears in a section named "4.0 The Rollback
> Journal". I think it should be changed to a definition to avoid
> confusion.
>
> https://www.sqlite.org/tempfiles.html
>
> "A rollback journal is a temporary file used to implement atomic
> commit and rollback capabilities in SQLite."
>
> Regarding the choice of the word "journal", the only relevant entry
> about "journal" in the Oxford dictionary is this. Is "journal" just a
> fancy way of saying "record file"? Thanks.
>
> """
> 2 a daily record of news and events of a personal nature; a diary.
> • Nautical a logbook.
> • (the Journals) a record of the daily proceedings in the British
> Houses of Parliament.
> • (in bookkeeping) a daily record of business transactions with a
> statement of the accounts to which each is to be debited and credited.
> """
>
> --
> 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] WITHOUT ROWID tables

[Peter da Silva]

If you have control over the schema you can put a schema version in some
table.

On Sat, Feb 15, 2020, 13:21 J. King  wrote:

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

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

[Peter da Silva]

Change the "(t1.textid = null)" to "(t1.textid IS NULL)". Null has no value, 
you have to check for it explicitly.

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

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

[Peter da Silva]

> If I change IS NOT FALSE to IS TRUE, the results will be different. I
> assume they should perform in the same way?
> if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should
> also be always true.

"NULL IS NOT FALSE" is true because NULL is not a value therefor it is not 
FALSE, because FALSE is a value.
"NULL IS TRUE" is false because NULL is not a value so it's not TRUE.

You can't compare NULL with anything. All you can do is tell if it "IS NULL" or 
"IS NOT NULL".

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

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

[Peter da Silva]

What do you mean there have been "just a few threads" in the mailing list?
I can barely keep up with it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users