Re: [sqlite] Poll: Include the recent sqlite3_column_name() fix in the upcoming 3.20.0 release?

[Peter Da Silva]

Any application that depends on column names should be using “AS” anyway, might 
as well break them sooner.

Disclaimer: I’m probably guilty of depending on column names without “AS”, 
which explains why I’ve been sneezing so much lately.

On 7/31/17, 10:21 AM, "sqlite-users on behalf of Richard Hipp" 
 
wrote:

Ticket https://sqlite.org/src/info/de3403bf5ae5f72ed describes a
problem with column naming, and a proposed solution.  Today's
question:  Should the proposed solution be merged into the 3.20.0
release?

Pros:  (1)  The change makes column names more consistent.  (2) The
change fixes some breakage caused by a query planner enhancement
introduced in 3.19.0.  (3) The change makes column naming in SQLite
work (more) like it does in PostgreSQL, MySQL, and SQLServer.  (4) The
change will likely be in 3.21.0 even it it isn't in 3.20.0.  Better to
go ahead and get over the pain of any breakage that results now,
rather than putting it off until later.

Cons: (5) The change might cause breakage for legacy applications that
depend on the older (arguably buggy) behavior.  (6) This seems like a
big change to receive so little beta exposure prior to the official
release.  (7) Making the merge will (or should) delay the release by a
day or so.  The release was going to happen tomorrow (2017-08-01) but
if we do the merge, I think the release should be postponed until
2017-08-02 or 2017-08-03.

Let me know your thoughts.   Replies to the mailing list are
preferred, but private email directly to me is also accepted.
-- 
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 draft document on the new pointer-passing interfaces

[Peter Da Silva]

If you’re in a position to perform an attack that address space randomization 
can defend against, then you have already successfully performed a code 
execution attack.

And so far as I can tell... *any* constant strings in the executable, including 
things like elements of the SQL language itself, provide a mechanism for 
locating code.

Meanwhile, what this design does is remove an attack surface for promoting an 
SQL injection attack to a code execution attack. The comments about randomizing 
strings in the previous 
threads were about possible alternate ways of mitigating that attack, and have 
nothing to do with ASLR.

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

Re: [sqlite] Problem with the new pointer-passing interface

[Peter Da Silva]

On 8/3/17, 9:16 AM, "sqlite-users on behalf of Ulrich Telle" 
 
wrote:
> The description of the new pointer-passing interface gives the impression 
> that restricting the pointer type parameter to static strings or literals 
> prevents misuse of the new feature.

The term I used was “deters”.

> And that is definitely not the case. It might be a hurdle for unsophisticated 
> developers, but not for the experienced ones.

What experienced, non-malicious developers would read the rationale and then go 
ahead and implement an extension that opened up the possibility of a 
pointer-based exploit from SQL by allowing types generated from SQL strings? 

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

Re: [sqlite] Problem with the new pointer-passing interface

[Peter Da Silva]

On 8/3/17, 8:56 AM, "sqlite-users on behalf of Ulrich Telle" 
 
wrote:
> Nevertheless, I think the pointer type string restriction gives a false sense 
> of security.

You described creating a malicious extension that would give you the ability to 
probe pointers by forging strings.

From a security point of view, once you have the ability to create and deploy a 
malicious extension into a target host, you’ve already got full local code 
execution access to that host anyway.


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

Re: [sqlite] Problem with the new pointer-passing interface

[Peter Da Silva]

On 8/3/17, 8:27 AM, "sqlite-users on behalf of Ulrich Telle" 
 
wrote:
> I really don't think that the latter is true. To overcome the restriction is 
> extremly simple, if you don't care about memory leaks. Just do [...]

If you’re creating a malicious extension, sure. But if you’re creating an 
exploit this seems like an odd way to go about it. It’d be simpler to implement 
something like “SELECT root_shell_on_port(1337);”

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

Re: [sqlite] Problem with the new pointer-passing interface

[Peter Da Silva]

On 8/3/17, 4:22 AM, "sqlite-users on behalf of Ulrich Telle" 
 
wrote:
> IMHO it would be better if the function sqlite3_bind_pointer would make a 
> copy of the type string and would thus be independent of the life span of the 
> type parameter.

I believe that this was a deliberate choice to deter using temporary strings 
for type names.
 

___
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] Database version

[Peter Da Silva]

I assume BEGIN, COMMIT, and ROLLBACK are safe too :)

On 7/21/17, 1:08 PM, "sqlite-users on behalf of Richard Hipp" 
 
wrote:

On 7/21/17, Simon Slavin  wrote:
>
> The new features introduced by SQLite since it started using file format 3
> all require explicit commands to use.  Adding columns to existing tables,
> AUTOVACUUM mode, WAL journals, DESC indexes, all require you to execute a
> specific SQL command to use them.

More precisely, the all require a schema change.

If you have a database created by old-application-X using a really old
version of SQLite.  Then you run DML statements using
brand-new-application-Y, those DML statement will never change the
database in any that make it unreadable by X.

"DML statements" = SELECT, INSERT, UPDATE, DELETE.
-- 
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] Database version

[Peter Da Silva]

https://www.sqlite.org/c3ref/c_source_id.html

This is also stored at offset 96 in the db file:

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

I don’t think there’s a pragma for extracting it from within SQLite code.

On 7/21/17, 9:58 AM, "sqlite-users on behalf of Igor Korot" 
 
wrote:

 Hi, ALL,
Is there a way to know the version of the .db file I am using?

I'd like to issue some kind of SELECT statement to get it.

Looks like there is an interface to get the library version, but I don't
see anything for a db file.

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


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

Re: [sqlite] UPDATE database using parameters

[Peter Da Silva]

Have a look at prepared statements and statement parameters.

Also, if you’re quoting strings you should use single quotes rather than double 
quotes. Double quotes just override the tokenizer, the result is still untyped 
and technically an identifier (the tokenizer actually marks it as TK_ID) until 
the parser disambiguates it. This can lead to unexpected results.

On 7/21/17, 1:34 AM, "sqlite-users on behalf of Edmondo Borasio" 
 wrote:

I am updating a record of a SQLite database as follows:

$db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"');

but instead of using name and ID I want to use some variables, $NewItemName
 and $hId.

Entering the variables as they are won't work. Neither using escape
characters like \"$DbItemName\" and \"$hId\".

Any idea of the right syntax pls?
-- 
Dr Edmondo Borasio, MedC BQ Ophth, FEBO
Consultant Ophthalmologist
Specialised in Cornea, Cataract & Laser Refractive Surgery

Head of Corneal and Refractive Surgery Department
Burjeel Hospital
Abu Dhabi, UAE
___
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] UPDATE database using parameters

[Peter Da Silva]

Using a straight PHP-level substitution like that performs the substitution 
before the SQL parser sees it. It’s also super dangerous if you’re not 
absolutely sure there’s no path for an untrusted agent to inject the name 
you’re selecting on.

https://xkcd.com/327/

On 7/21/17, 3:42 AM, "sqlite-users on behalf of Edmondo Borasio" 
 wrote:

Hi and thanks for your email.

I am using PHP with SQLite on an Apache server.
That statement was taken from some advice I got from a forum.  I wasn't
aware it was MySQL.
I am new to SQLite and this is my first database.

*"Table/column names cannot use parameters.  You have to put it directly*
*into the string:"*

I guess however there must be a way, because for example with SELECT it
works.
The query below works perfectly using variables:

$results = $db->query("SELECT \"$DbItemName\" FROM Anagrafica WHERE
hID=\"$hId\"")->fetchArray();

Cheers

Edmondo


On Fri, 21 Jul 2017 at 12:24, Clemens Ladisch  wrote:

> Edmondo Borasio wrote:
> > $stmt->bind_param($p_name,$bind_value);
>
> This looks like PHP's MySQL driver.  Which DB are you actually using?
>
> Anyway, I recommend you start with the examples from the manual, e.g.,
> :
>
>   $stmt = $db->prepare('SELECT bar FROM foo WHERE id=:id');
>   $stmt->bindValue(':id', 1, SQLITE3_INTEGER);
>   $result = $stmt->execute();
>
> > I would also need to take the parameter "name" of "SET name" from a
> variable
>
> Table/column names cannot use parameters.  You have to put it directly
> into the string:
>
>   $sql = "UPDATE Anagrafica SET ".$col." = ..."
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] New draft document on the new pointer-passing interfaces

[Peter Da Silva]

On 7/25/17, 11:25 AM, "sqlite-users on behalf of petern" 
 wrote:
> You're trying to change the topic to the security model.

All I was doing was pointing out that hiding the type information from 
attackers is not a requirement, so the fact that it’s visible if you examine 
the binary or source is not relevant.

Now you’ve acknowledged that you’re not pointing out any actual security flaw, 
we’re done, right?

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

Re: [sqlite] New draft document on the new pointer-passing interfaces

[Peter Da Silva]

What about imposing some structure on the pointer type strings that uses a 
guaranteed unique substring, for example “org.sqlite.fts3.snippet”,  to ensure 
there wouldn’t be accidental conflicts?

On 7/24/17, 6:54 AM, "sqlite-users on behalf of Richard Hipp" 
 
wrote:

https://www.sqlite.org/draft/bindptr.html

-- 
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 draft document on the new pointer-passing interfaces

[Peter Da Silva]

If you’re concerned about people faking pointers, you could always go with 
something like “org.sqlite.fts3.01Yzg5x.snippet”.

But given this design, you need to have already injected a C extension to even 
access the pointers, so I’m not sure what the issue is.

On 7/24/17, 9:19 AM, "sqlite-users on behalf of Hick Gunter" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of h...@scigames.at> 
wrote:

Naming conventions (if strictly adhered to) are moderately good at avoiding 
conflicts, but take the guesswork out of "faking a pointer". It also assumes 
that pointers for "whatever" are interchangeable between different queries in a 
process, i.e. passing a "whatever" pointer from statement A to a function in 
statement B does not pose a risk.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Peter Da Silva
Gesendet: Montag, 24. Juli 2017 15:37
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: Re: [sqlite] New draft document on the new pointer-passing 
interfaces

What about imposing some structure on the pointer type strings that uses a 
guaranteed unique substring, for example “org.sqlite.fts3.snippet”,  to ensure 
there wouldn’t be accidental conflicts?

On 7/24/17, 6:54 AM, "sqlite-users on behalf of Richard Hipp" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of d...@sqlite.org> 
wrote:

https://www.sqlite.org/draft/bindptr.html

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of 
the intended recipient(s) only and may contain information that is 
confidential, privileged or legally protected. Any unauthorized use or 
dissemination of this communication is strictly prohibited. If you have 
received this communication in error, please immediately notify the sender by 
return e-mail message and delete all copies of the original communication. 
Thank you for your cooperation.


___
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 draft document on the new pointer-passing interfaces

[Peter Da Silva]

On 7/24/17, 3:50 PM, "sqlite-users on behalf of petern" 
 wrote:
> BTW, if the hypothetical attacker has a copy of the application, aren't the 
> constant space pointer access keys' string addresses all there in clear text

But that’s not part of the security model, so what’s the problem?
 

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

Re: [sqlite] UPDATE database using parameters

[Peter Da Silva]

On 7/22/17, 1:46 AM, "sqlite-users on behalf of Keith Medcalf" 
 
wrote:
> Not very well.  How do you think "drive by downloads" work?  Javascript in 
> browsers is the most dangerous thing ever invented!

I think the caps-lock key gives it a run for its money.
 

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

Re: [sqlite] Database version

[Peter Da Silva]

On 7/21/17, 10:14 AM, "sqlite-users on behalf of Igor Korot" 
 
wrote:
> This is also stored at offset 96 in the db file:
>
> https://www.sqlite.org/fileformat.html

Is this the number I'm after?

That’s the version of SQLite that most recently wrote to the file. The word 
before it is the transaction number when it was updated. 

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

Re: [sqlite] Database version

[Peter Da Silva]

The problem is that SQLITE_VERSION_NUMBER is not “the database version”, it’s 
something like “the last version of SQLite that committed a transaction”.

The database version number is “3”.
 

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

Re: [sqlite] Database version

[Peter Da Silva]

If SQLite3 can open the file at all, the first 16 characters will be "SQLite 
format 3\000".

On 7/21/17, 10:46 AM, "sqlite-users on behalf of Igor Korot" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of ikoro...@gmail.com> 
wrote:

Hi, Peter et al,

On Fri, Jul 21, 2017 at 11:35 AM, Peter Da Silva
<peter.dasi...@flightaware.com> wrote:
> The problem is that SQLITE_VERSION_NUMBER is not “the database version”, 
it’s something like “the last version of SQLite that committed a transaction”.
>
> The database version number is “3”.

I guess it is not stored anywhere.

That's OK. I will just read and parse first 16 characters of the file...

Thank you.

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


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

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] [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] Thinking about a way to extend the number of writers in WAL mode

[Peter Da Silva]

On 8/4/17, 1:28 PM, "sqlite-users on behalf of Nico Williams" 
 wrote:
> Imagine a mode where there is only a WAL, and to checkpoint is to write a new 
> WAL with only live contents and... rename(2) into place.  Such a mode would 
> a) be a 100% Copy-on-Write (CoW) mode, whereas currently WAL is only CoW 
> until a checkpoint operation comes along, b) have better read concurrency.  A 
> special marker could be used to denote "this WAL is closed and replaced with 
> a checkpointed one", that way readers only have to stat/re-open when they see 
> this.

A log-structured database, like a log-structured file system?

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

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

[Peter Da Silva]

On 8/4/17, 1:59 PM, "sqlite-users on behalf of Nico Williams" 
 wrote:
> The checkpoint process would look like this:
>   - make a new file in the same directory
>   - copy the DB to the new file
>   - rename the new file into place
>   - write the "closed, renamed" marker into the old file (which is still open)

Step 2 seems rather expensive, even if you’re filtering out dead blocks in the 
process.


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

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

[Peter Da Silva]

On 8/4/17, 1:45 PM, "sqlite-users on behalf of Nico Williams" 
 wrote:
> SQLite3's WAL is already log-structured.  The main DB file isn't.  So SQLite3 
> is a hybrid.  But it doesn't have to be a hybrid.

One issue I see with this is you’ll have to retain the old WALs as long as they 
have any live data, or the checkpoint operation will have to copy all the 
unmodified data in the log to the new WAL, or you’ll have to keep a non-log 
structure containing all the relatively static data that hasn’t been modified 
in the last “N” checkpoints.

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

Re: [sqlite] sequencer

[Peter Da Silva]

Have a look at https://sqlite.org/autoinc.html

Also keep in mind that latency for SQLITE is low, since it’s not client-server, 
so you can script a transaction that has any sequence behavior you want with 
similar overhead to having SQLITE implement the sequence for you.

On 8/2/17, 10:23 AM, "sqlite-users on behalf of Sylvain Pointeau" 
 wrote:

Dear all,

I am currently using H2 and I use sequencers like:

CREATE SEQUENCE IF NOT EXISTS SEQ_1 START WITH 12123;

insert into MYTABLE(SPECIFIED_NUMBER, OTHERINFO) values (seq_1.nextval,
'other info')

I would like to move to sqlite, but what would be your advice for the
sequencer values?
Is it possible at all?

Best regards,
Sylvain
___
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] sequencer

[Peter Da Silva]

Hence the suggestion to script a transaction. For example, in pseudocode:

BEGIN;
SELECT value, increment from super_sequences where table = :table and column = 
:column;
INSERT INTO :table (id, other, fields) VALUES :(value+increment,other,values);
UPDATE super_sequences set value = :(value + increment) WHERE table = :table 
and column = :column;
COMMIT;

On 8/2/17, 10:37 AM, "sqlite-users on behalf of Sylvain Pointeau" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
sylvain.point...@gmail.com> wrote:

On Wed, Aug 2, 2017 at 5:27 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> Have a look at https://sqlite.org/autoinc.html
>

Yes I am aware of autoinc but this is not what I can use, because I need to
specify exactly the sequence (as start number and increment). Additionally
I can have tables having 2 or 3 fields needing a specified sequence number.
___
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] sequencer

[Peter Da Silva]

Can’t you do the same basic logic then use (SELECT value FROM super_sequences 
WHERE id=’SEQ_1’) instead of SEQ_1.nextval?

On 8/2/17, 10:48 AM, "sqlite-users on behalf of Sylvain Pointeau" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
sylvain.point...@gmail.com> wrote:

On Wed, Aug 2, 2017 at 5:43 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> Hence the suggestion to script a transaction. For example, in pseudocode:
>
> BEGIN;
> SELECT value, increment from super_sequences where table = :table and
> column = :column;
> INSERT INTO :table (id, other, fields) VALUES :(value+increment,other,
> values);
> UPDATE super_sequences set value = :(value + increment) WHERE table =
> :table and column = :column;
> COMMIT;
>

ok but it does not work for insert like

insert into mytable (MY_NO, MY_INFO)
SELECT  SEQ_1.nextval,  a.INFO  FROM myothertable a
;
___
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] sequencer

[Peter Da Silva]

By “the same thing” I mean:

BEGIN;
something like the stuff I had in my original post where it’s incrementing the 
sequence;
your statement where you’re using the sequence, except using something like 
(SELECT value FROM super_sequences WHERE id=’SEQ_1’);
COMMIT;

On 8/2/17, 11:20 AM, "sqlite-users on behalf of Sylvain Pointeau" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
sylvain.point...@gmail.com> wrote:

On Wed, Aug 2, 2017 at 5:54 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> Can’t you do the same basic logic then use (SELECT value FROM
> super_sequences WHERE id=’SEQ_1’) instead of SEQ_1.nextval?
>
>
> insert into mytable (MY_NO, MY_INFO)
> SELECT  SEQ_1.nextval,  a.INFO  FROM myothertable a
> ;
>
>
no because nextval also increment the sequence, as opposed to the (SELECT
value FROM super_sequences WHERE id=’SEQ_1’) where it only reads the value
___
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] Mailinglist question

[Peter Da Silva]

On 8/9/17, 2:45 PM, "sqlite-users on behalf of Nico Williams" 
 wrote:
> I'm not fond of web fora, but a good integration would be nice, for sure.  An 
> IMAP service would be fantastic for users like me.  The need to keep some 
> state (unless we enhance MUAs to do it for read-only IMAP mailboxes) requires 
> user accounts, which requires more of a business model :(

Set up a Usenet server: NNTP reader mode/NNRP keeps that state in the client.

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

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

[Peter Da Silva]

On 8/4/17, 8:29 AM, "sqlite-users on behalf of Bob Friesenhahn" 
 wrote:
> Lazy programmers who request such things are of the same ilk which use 
> programming practices resulting in SQL injection attacks.  Sqlite should not 
> promote such practices.

Then require a fully qualified path and extension, and don’t have a search path 
for DLLs at all.
 
Otherwise you’re just haggling over where to draw the line.

___
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

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

Re: [sqlite] XOR operator

[Peter Da Silva]

Generally, when you talk about whether a language is strongly or weakly typed, 
you're talking about the storage, not the content.

Pretty much every "weakly typed" language out there (there are a few 
exceptions, like Tcl) does have fully typed values. In many cases you can even 
interrogate the value with a "type of" operator. They just have storage 
locations (variables, array elements, hash targets) that can hold any type.

SQLite is, in common terminology, weakly typed.

> On 2017-10-08, at 08:56, Roman Fleysher  
> wrote:
> 
> The point is that terminology is chosen for a reason and can not be 
> dismissed. "Flexibly typed" means it is typed. It means SQLite knows how many 
> bytes: without knowing it would not be able to establish equality "IS".  
> Flexibly means columns can contain values of mixed types,  but each value 
> still has a type. And this is a very very big advantage of SQLite.
> 
> Perhaps longer term is "flexibly strongly typed". Perhaps because "typed" 
> implies "strongly" (what is a weak type?), strongly is redundant.
> 
> Roman
> 
> 
> 
> 
>  Original message 
> From: R Smith 
> Date: 10/8/17 9:38 AM (GMT-05:00)
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] XOR operator
> 
> On 2017/10/06 6:03 PM, Richard Hipp wrote:
>> On 10/6/17, R Smith  wrote:
>>> I'd also like to see a Unary NOT operator, such that you can say: a = !b
>> In SQL and SQLite that would be:  a = NOT b
> 
> Apologies, I thought it obvious from the context that I meant a binary
> operation, not a Boolean operation NOT.
> 
> i.e. 0xA (base16) = 1010 (base2) so that NOT 0xA = 0101 = 0x5... so if a
> = 0xA then !a = 0x5, but that only works IF we are restricted to "a"
> being 1 byte in size, which brings us to the following point:
> 
>> 
>>> But, I guess that's only feasible in a strongly typed language.
>> (1) I object to the characterization of SQLite not being "strongly
>> typed".  SQLite is "flexibly typed" in the sense that it provides the
>> application with a lot of flexibility with regard to what datatypes
>> are allowed to be stored in a particular column or participate in an
>> operation.  Other SQL database engines are "rigidly typed".  Those
>> other SQL implementations are much more judgmental about what you can
>> and cannot do with your data.
>> 
>> (2) Why is rigid typing required in order to implement boolean negation?
> 
> Answering (2): A strongly typed language that defines
> INT/UINT/WORD/INT64/etc. as specifically a 32-bit or 64-bit
> signed/unsigned representation, or "Byte" as a 8-bit unsigned
> representation will be sensible to say a = not b; where a and b are both
> typed as BYTE values. but if you don't know how many bits are "meant" to
> be in "a", how to determine how many bits must be negated / "notted" /
> changed to produce the result of "NOT b" in the way described up there.
> 
> If for example a = 0xA then !a might be 0x5 for a nibble, but it will be
> 0xF5 for a byte, 0xFFF5 for a WORD, 0xFF5 for a 32bit INT, etc. etc.
> 
> It's often used in masking bit flag sequences. a = (a & !0x3) would see
> "a" being switched so that it's LSB's 0 and 1 gets switched off while
> leaving the others in tact. Yes, I could have just said a = (a & (0xFF -
> 0x03)) or even work out what that result is and go a = (a & 0xFC), but
> if the bits that get switched off lives in a variable (b), then a = (a &
> !b) is just so much more sensible / elegant. I'm even ok with syntax
> like a = (a & (not b))... but that's not how SQLite works, or can work,
> unless it becomes strongly typed.
> 
> 
> As to (1)... Cool, call it flexibly typed then, I'm ambivalent to the
> terminology, my point is about the variable sizes not being set in stone.
> 
> 
> ___
> 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] Script Embedded SQLite With TCL

[Peter Da Silva]

There doesn’t seem to be a straightforward way to get the SQLite handle from 
the Tcl SQL command, but it is possible.

https://github.com/flightaware/Pgtcl/blob/master/generic/pgtclSqlite.c#L838

On 10/7/17, 9:31 AM, "sqlite-users on behalf of apajabo yaro" 
 
wrote:

Hello,I am writing a C++ application that embeds TCL and for its database 
operations I am also embedding SQLite in it.I would like to be able to do the 
following:1. Write TCL scripts for the embedded SQLite from the embedded 
TCL interpeter.2. Pass an SQLite connection from the embedded TCL 
interpreter to C++ and use it in C++ for db operations, as well as the other 
way around.
I would appreciate your kind suggestions.Yaro.
___
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 develop a GUI front-end

[Peter Da Silva]

Tk is platform independent, so long as you don’t do UNIX-specific stuff (eg, 
assume UNIX file paths and stuff) any Tk app should work just fine on Windows. 
You may need to tweak the fonts, eg:

if { $tcl_platform(platform) eq "windows" } { set font {Arial} } else { set 
font {Helvetica} }

Have a look at the examples at https://wiki.tcl.tk/4149


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

Re: [sqlite] Simple SQL question?

[Peter Da Silva]

Wouldn’t you create a view instead, and not bother calculating age_at_issue 
until necessary since it’s derived completely from two other columns?

On 11/15/17, 3:16 PM, "sqlite-users on behalf of Bart Smissaert" 
 wrote:

Have 2 tables, TABLE_A and TABLE_P like this:

CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE INTEGER)

CREATE TABLE_P(ID INTEGER, DOB INTEGER)

ID is he common field.

Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will hold the
age of the person identified by ID, at the date of ISSUE_DATE.
I do this with a UDF, taking 2 arguments, in this case DOB (date of birth)
and ISSUE_DATE.
The UDF is not relevant in this example and it could as well be ISSUE_DATE
- DOB.

I tried this:

UPDATE TABLE_A SET AGE_AT_ISSUE =
(SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A
INNER JOIN TABLE_P P ON(A.ID = P.ID)

But that will make the column AGE_AT_ISSUE have the same value for all
rows, which is he first row of the select.

The select by itself will give the right values.

Any suggestions how this can be done with just one statement?


RBS
___
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 develop a GUI front-end

[Peter Da Silva]

On 11/14/17, 3:33 PM, "sqlite-users on behalf of Balaji Ramanathan" 
 wrote:
> I am leaning towards tcl/tk given the endorsement from both Peter and DRH. 
> So, I downloaded tcl/tk version 8.6.7 from magicsplat 
> (http://www.magicsplat.com/tcl-installer/index.html), and then ran tclsh.exe. 
>  In the resulting command window, I typed "sqlite3 db1  database" as instructed on this page on the sqlite website: 
> https://sqlite.org/tclsqlite.html .  And all I got was "invalid command name 
> sqlite3".  So, I am stuck.  What did I do wrong?

% package require sqlite3
3.20.1
% sqlite3 db1 :memory:
%

> BTW, the www.tcl-lang.org website has been down for at least the past 3 days 
> it looks like.  Not sure how to interpret the reliability of a technology 
> whose home website is down for days at a time.

Try https://www.tcl.tk/ ? 

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

Re: [sqlite] how into insert row into middle of table with integer primary key

[Peter Da Silva]

On 11/22/17, 1:43 AM, "sqlite-users on behalf of R Smith" 
 
wrote:
> Oh there are many valid reasons why to have Order in data, one I use 
> regularly is to dictate the process flow in manufacturing where some thing 
> needs to go to machine Y before it can move on to machine X, or process E, 
> for a specific item, has to happen before process B etc.

That’s a partial ordering though, based on a dependency graph. You’d want to 
maintain the dependencies in the database as the ground truth, and when needed 
generate a topological ordering based on the dependencies. That can be 
maintained in some kind of cache table, but it’s not something that you would 
need to dynamically update like the OP but rather regenerate it when the 
dependencies change.
 

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

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

[Peter Da Silva]

On 11/21/17, 9:54 PM, "sqlite-users on behalf of jose isaias cabrera" 
 wrote:
> But, whatever it is, I will be part of the next phase of communication. 

This.

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

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

[Peter Da Silva]

Are people here talking about UUIDs or things that just look like UUIDs? It 
sounds like the latter. UUIDs are actually structured objects, with embedded 
type bits. There are multiple UUID generation schemes, one of which is based on 
random numbers, others are based on hashes, and there is the common ugly 
workaround of generating a 128 bit hash and calling it a UUID.

If you use version 1 UUIDs you're mathematically guaranteed to avoid 
collisions. At least for the next 3000 years:

Version 1 UUIDs are based on a node address (MAC), a 60 bit clock, and a 
node-specific sequence number. You can generate 163 billion version 1 UUIDs per 
second and they won't roll over until 5236 AD.

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

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

[Peter Da Silva]

Ah, I see someone else has already brought up version 1 UUIDs.

> What about invalid and reused MAC addresses and devices with no MAC address 
> at all?

Not an issue with SQLite since that part of the UUID is a constant within a 
given database. It would be reasonable to simply pick "0" then set the low bit 
of the first octet to 1 in accordance with the standard for non-MAC node IDs. 
The standard suggests using a hash or a cryptographically secure random number 
generator, setting the 

> What about time resets to the epoch which are not restored, user time changes,

I know some systems at least increment the node each time a time change is 
detected. It will take 2^47 time changes to roll over. Since the node part is 
not relevant to SQLite, this is perfectly safe.

> daylight saving or leap seconds?

Not relevant to the timestamp format, since it's an epoch time.

> It sounds even more probabilistic than the probabilistic methods. Does anyone 
> actually use it?

It's Microsoft's standard GUID/UUID format and is also used by a lot of other 
systems for compatibility.

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

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

[Peter Da Silva]

>> What about time resets to the epoch which are not restored, user time 
>> changes,
> 
> I know some systems at least increment the node each time a time change is 
> detected. It will take 2^47 time changes to roll over. Since the node part is 
> not relevant to SQLite, this is perfectly safe.

Also, the UUID clock doesn't need to be the system clock, so you can simply 
ignore backwards changes in the system clock (or maintain a common offset that 
gets updated whenever a backwards change is detected in the system clock). Over 
time this may trim a few decades off the 3000+ year life of the format.
___
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] 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] 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] Starting with TCL

[Peter Da Silva]

Sqlite will perform the substitution of Tcl variables in a query. You can flag 
the variable with a ‘$’ or with a ‘:’ (which makes it more like other SQL APIs).

So you can write:

$db eval { 
SELECT   Tea
FROM teaInStock 
ORDER BY LastUsed DESC
LIMIT   :nrToFetch;
} {
...
}

or even

$db eval { 
SELECT   Tea
FROM teaInStock
ORDER BY LastUsed DESC
LIMIT   $nrToFetch;
} {
... do something with $Tea ...
}

This latter case works because the query is surrounded by {} so Tcl won’t 
substitute the variable, it will be seen and securely inserted into the query 
by SQLite.

This is rather nifty, which is why I recently added pretty much exactly this 
functionality to Pgtcl (though due to differences between PostgreSQL and SQLite 
syntax I had to restrict it to using “:”). The equivalent code would be:

pg_select $db -variables {
SELECT   Tea
FROM teaInStock 
ORDER BY LastUsed DESC
LIMIT   :nrToFetch;
} row {
... do something with $row(Tea) ...
}


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

Re: [sqlite] Simple Search using LIKE or something else

[Peter Da Silva]

Looking back through the thread, from the original post:

> I'm using the amalgamation by default, so I don't think FTS is in the DLL.  I 
> can recompile, but that now introduces complications on machines that may not 
> have this exact DLL.  (A couple people in my company use this app)

The same problem would presumably apply to the regex extension... not all users 
would have that extension.


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

Re: [sqlite] how into insert row into middle of table with integer primary key

[Peter Da Silva]

If you want to maintain something like a user-selected display order, I would 
suggest adding an explicit “display order” column.
 

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

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

[Peter Da Silva]

On 11/21/17, 10:30 AM, "sqlite-users on behalf of Keith Medcalf" 
 
wrote:
> I simply tell those people that they either (a) fix their systems or (b) use 
> snail-mail.  Takes care of the problem entirely.

I am absolutely not going to get into that discussion with, for one example, a 
lawyer in another country who is helping me deal with winding down my mother’s 
estate.


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

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

[Peter Da Silva]

On 11/21/17, 9:59 AM, "sqlite-users on behalf of Keith Medcalf" 
 
wrote:
> If you run an RFC complaint MTA then there is really very little problem with 
> SPAM at all -- I have many connections per second rejected for RFC 
> non-compliance -- and get maybe 3 SPAM messages per day, all of which 
> originate from the crappy Johhny-cum-lately freemail systems

So. taronga.com is a high profile spam target thanks to my using it for Usenet 
posts for years. Like, at one point in the ‘90s I got so much spam that it blew 
out my bandwidth limit and I got charged an overage, just for receiving 
handshakes and dropping spam on the ground.

I tried being aggressively OCD about RFC compliance and found I was missing 
mail I actually needed. Like, from lawyers and similar stuff that had real 
world consequences.

So I went back to using a combination of multiple layers of filters and a 
greylist front end. Oh, and blocking all of China and Argentina.

Still get a lot of spam that Apple Mail’s Bayesian filter takes care of. Mostly.

Still too many false positives. I switched to gmail for mail I actually really 
needed to get. I was spending too much lifetime dealing with mail issues.

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

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

[Peter Da Silva]

On 11/21/17, 11:21 AM, "sqlite-users on behalf of Warren Young" 
 
wrote:
> You don’t get proper threading with the current ticket comment system, but 
> both mailers I use these days lack that feature, as do most forum systems.  I 
> miss threading, but clearly I can live without it.

If by “proper threading” you mean trn-style, no, nothing but Usenet has ever 
gotten that right.

But the mailers I use (Gmail’s web interface, Apple Mail and (yuck) Outlook) 
all do basic threading.

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

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

[Peter Da Silva]

I’m a mailing list fan, too. Reddit I use for yucks only.

On 11/21/17, 10:48 AM, "sqlite-users on behalf of Stephen Chrzanowski" 
 
wrote:

All in all, just please, oh PLEASE stay away from redit  I will not
ever go there.  I got BANNED for a first post question that included an
example to clarify what I was looking for.  Nothing nasty, nothing
ignorant, it was a technical question about something or other, and out to
the curb I went.
 

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

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

[Peter Da Silva]

On 11/21/17, 8:52 AM, "sqlite-users on behalf of Dominique Devienne" 
 
wrote:
> After re-inventing database and source-control, forum software next? :) I 
> have no doubt it would be lean, fast, SQLite-based, in C (and/or TCL).

Plus XMPP and NNTP/NNRPD interfaces, and a PERFECT bidirectional email gateway 
that maintains BOTH kinds of threading.
 

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

Re: [sqlite] Starting with TCL

[Peter Da Silva]

On 11/16/17, 11:37 AM, "sqlite-users on behalf of Cecil Westerhof" 
 wrote:
> When I use:
> db eval {SELECT * FROM teaInStock} {
>puts $Tea, $Location
> }

puts takes a single string, so you can do {puts “$Tea\t$Location”. Arguments 
are separated by space, comma has no intrinsic meaning, and puts takes two 
arguments: the file handle to write on and the string to print. So it’s 
interpreting “$Tea,” as the name of a file handle.

You probably want something like:

db eval {SELECT * FROM teaInStock} {
puts [format “%12s %12s %s” $Tea ${Last Used} $Location]
}
 

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

Re: [sqlite] Best way to develop a GUI front-end

[Peter Da Silva]

I contacted the Tcl core team and this is the response from Steve Landers:

> tcl-lang.org was a temporary measure a few years ago when the .tk DNS went 
> missing.  It wasn’t advertised but I guess it is now.

> I’ve fixed it

The official site is still at tcl.tk.

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

Re: [sqlite] Best way to develop a GUI front-end

[Peter Da Silva]

On 11/14/17, 10:32 PM, "sqlite-users on behalf of J Decker" 
 
wrote:
> Initially I was interested in tcl/tk, and still am, but I'm not sure about 
> the tcl/tk packaging that would be necessary to make use on multiple 
> computers.

You can bundle a whole Tcl application in a single executable called a 
“starkit” along with all the necessary packages and libraries exposed 
internally as a virtual file system.

> Also, I don't know how network database connections would work out, would it 
> be the same as web based stuff or more complicated, etc.

For sqlite you don’t generally make network database connections, it uses a 
library model. For other databases there’s a number of conventional script 
interfaces. DBI is popular for cross-database use: 
https://core.tcl.tk/jenglish/gutter/packages/dbi.html and I’m currently 
maintainer for the original PostgreSQL-Tcl interface 
https://wiki.tcl-lang.org/13016 ...

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

Re: [sqlite] Best way to develop a GUI front-end

[Peter Da Silva]

Since sqlite originated as a Tcl extension it’s got excellent Tcl bindings, and 
Tcl has an excellent platform-independent GUI in Tk, so it seems to me that 
would be the best and simplest way to create a GUI front end for sqlite.

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

Re: [sqlite] Unexpected result from SELECT * FROM (subquery);

[Peter Da Silva]

The table contains a single row with a single column that contains a string.

That doesn't make it a string. It's still a table. When you say "select ... 
from table" it doesn't matter where the table came from, it's still an 
operation on a table. You are not performing "select ... from 'tab1';", you're 
performing "select ... from unnamed-table" where "unnamed-table" contains one 
row containing 'tab1'.

The result of *that* select is yet another unnamed table that the sqlite3 shell 
displays for you.

Even if you perform

select * from (select * from (select * from ( ... ) )

The result is still a table. Select is not an indirection operator like 
accessing an element of an array or a structure.

On 2017-11-05, at 05:39, Klaus Maas  wrote:
> Yes, correct.
> 
> But the contents of the returned table are not objects, but merely values.
> 
> In this case the returned table contains a single string value  which 
> happens to be the name of a table, but it is not the table.
> 
> Or do I get this wrong?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexpected result from SELECT * FROM (subquery);

[Peter Da Silva]

On 2017-11-05, at 05:28, Klaus Maas  wrote:
> I thought it was because what SQL returns is a value (in this case a string) 
> and not an object?
> 
> The string value might be the same as the name of an object, but is not the 
> object.

Select returns a table, not a name or a string.

The outer select operates on this unnamed table, not any particular cell in it.

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

Re: [sqlite] Typos in the documentation

[Peter Da Silva]

Also perhaps “page cache” rather than “pager cache”?
 

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

Re: [sqlite] Simple Search using LIKE or something else

[Peter Da Silva]

On 11/9/17, 2:51 PM, "sqlite-users on behalf of Stephen Chrzanowski" 
 
wrote:
> I've added a simple filter function that uses SQLites LIKE operator, but I'd 
> like something a little bit more advanced.  Right now, the SQL code is like:
> 
> select * from Events where Title like '%Abc%Def%'
> 
>  This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.
> 
> I've thought about doing some kind of delimiter, then have the code generate 
> the SQL code by just looping through the keywords and generate the "or Title 
> like '%keyword%'" statement (With appropriate escaping), but that just smells 
> bad to me.

The best way to do it is not to use complex keys, but if you have to (say 
because the input is free form) then generating

SELECT * FROM EVENTS WHERE title LIKE ‘%Abc%’ AND title LIKE ‘%Def%’;

is probably the only option.
 

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

Re: [sqlite] Automatic indexes don't work on without rowid tables

[Peter Da Silva]

On 11/8/17, 8:20 AM, "sqlite-users on behalf of Richard Hipp" 
 
wrote:
> The technical reason for this is that, from the point of view of the query 
> planner, a WITHOUT ROWID table is really a covering index

So basically the whole table is stored inline with the primary key index?

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

Re: [sqlite] Good resources for TCL/TK

[Peter Da Silva]

Like
  expr {sqrt($foo) < 3.7 && $bazflag > 0}

Instead of
  expr sqrt($foo) < 3.7 && $bazflag > 0

Same for the first argument to “if”, second argument to “for”, etc.

On 12/4/17, 9:27 AM, "sqlite-users on behalf of Cecil Westerhof" 
 wrote:

2017-12-04 15:24 GMT+01:00 Gerry Snyder :

> It is always a good idea to put the arguments of [expr] in braces. That 
way
> they are byte-compiled.
>

​You mean like:
exec {swapon} (--noheadings} {--show}



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

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] How to store as integer

[Peter Da Silva]

I’d recommend expr {double($temp)} so the bytecode compiler can optimize the 
expression.

On 12/6/17, 8:40 AM, "sqlite-users on behalf of Cecil Westerhof" 
 wrote:

return [expr double(${temp})]

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

Re: [sqlite] SQLite on iOS with NSFilePresenter/NSFileCoordinator

[Peter Da Silva]

On 10/25/17, 10:42 AM, "sqlite-users on behalf of Arno Gramatke" 
 wrote:
>Jens, Simon,
> 
> thanks for your feedback and suggestions. I will take a closer look at 
> keeping a "shadow“ copy in the current location and copy the file to the 
> Documents folder when needed. That seems to be a feasible approach, 
> especially with what Jens wrote about APFS and its copy-on-write support. 
> I’ve completely forgotten that. :-)

Another question I have is... is an SQLITE database the best format for 
exposing your data to other applications?

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

Re: [sqlite] SQLite on iOS with NSFilePresenter/NSFileCoordinator

[Peter Da Silva]

On 10/25/17, 11:18 AM, "sqlite-users on behalf of Richard Hipp" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of d...@sqlite.org> 
wrote:
> On 10/25/17, Peter Da Silva <peter.dasi...@flightaware.com> wrote:
> > Another question I have is... is an SQLITE database the best format for
> > exposing your data to other applications?

> Yes, it's the best format.  Peter, were you not paying attention during my 
> talk at the Tcl conference last week?  :-)

I tried to open your message as an SQLITE database and got “Error: file is not 
a database”. Please advise.

___
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] 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] 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] 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] 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] 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/15/18, 1:25 AM, "Howard Chu" <h...@symas.com> wrote:

    Peter Da Silva wrote:
> On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of h...@symas.com> 
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] 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: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] 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" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
ryansmit...@gmail.com> wrote:

On 1 May 2018, at 6:43pm, Peter Da Silva<peter.dasi...@flightaware.com>  
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]

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

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