[sqlite] 1st Call For Papers - 25th Annual Tcl/Tk Conference (Tcl'2018)

[conference]

Hello SQLite Users, fyi ...

25th Annual Tcl/Tk Conference (Tcl'2018)
http://www.tcl.tk/community/tcl2018/

October 15 - 19, 2018
Crowne Plaza Houston River Oaks
2712 Southwest Freeway, 77098
Houston, Texas, USA

Important Dates:

Abstracts and proposals due   August 20, 2018
Notification to authors   August 27, 2018
WIP and BOF reservations open July 23, 2018 ** may change **
Registration opensAugust 20, 2018 ** may change **
Author materials due  September 24, 2018
Tutorials Start   October 15, 2018
Conference starts October 17, 2018

Email Contact:tclconfere...@googlegroups.com

Submission of Summaries

Tcl/Tk 2018 will be held in Houston, Texas, USA from October 15, 2018 to 
October 19, 2018.

The program committee is asking for papers and presentation proposals
from anyone using or developing with Tcl/Tk (and extensions). Past
conferences (Proceedings: http://www.tcl.tk/community/conferences.html)
have seen submissions covering a wide variety of topics including:

* Scientific and engineering applications
* Industrial controls
* Distributed applications and Network Managment
* Object oriented extensions to Tcl/Tk
* New widgets for Tk
* Simulation and application steering with Tcl/Tk
* Tcl/Tk-centric operating environments
* Tcl/Tk on small and embedded devices
* Medical applications and visualization
* Use of different programming paradigms in Tcl/Tk and proposals for new
  directions.
* New areas of exploration for the Tcl/Tk language

Submissions should consist of an abstract of about 100 words and a
summary of not more than two pages, and should be sent as plain text
to tclconfere...@googlegroups.com no later than August 20, 2018. Authors of 
accepted
abstracts will have until September 24, 2018 to submit their final
paper for the inclusion in the conference proceedings. The proceedings
will be made available on digital media, so extra materials such as
presentation slides, code examples, code for extensions etc. are
encouraged.

Printed proceedings will be produced as an on-demand book at lulu.com
Online proceedings will appear via
http://www.tcl.tk/community/conferences.html

The authors will have 30 minutes to present their paper at
the conference.

The program committee will review and evaluate papers according to the
following criteria:

* Quantity and quality of novel content
* Relevance and interest to the Tcl/Tk community
* Suitability of content for presentation at the conference

Proposals may report on commercial or non-commercial systems, but
those with only blatant marketing content will not be accepted.

Application and experience papers need to strike a balance between
background on the application domain and the relevance of Tcl/Tk to
the application. Application and experience papers should clearly
explain how the application or experience illustrates a novel use of
Tcl/Tk, and what lessons the Tcl/Tk community can derive from the
application or experience to apply to their own development efforts.

Papers accompanied by non-disclosure agreements will be returned to
the author(s) unread. All submissions are held in the highest
confidentiality prior to publication in the Proceedings, both as a
matter of policy and in accord with the U. S. Copyright Act of 1976.

The primary author for each accepted paper will receive registration
to the Technical Sessions portion of the conference at a reduced rate.

Other Forms of Participation

The program committee also welcomes proposals for panel discussions of
up to 90 minutes. Proposals should include a list of confirmed
panelists, a title and format, and a panel description with position
statements from each panelist. Panels should have no more than four
speakers, including the panel moderator, and should allow time for
substantial interaction with attendees. Panels are not presentations
of related research papers.

Slots for Works-in-Progress (WIP) presentations and Birds-of-a-Feather
sessions (BOFs) are available on a first-come, first-served basis
starting in July 23, 2018. Specific instructions for reserving WIP
and BOF time slots will be provided in the registration information
available in July 23, 2018. Some WIP and BOF time slots will be held open
for on-site reservation. All attendees with an interesting work in
progress should consider reserving a WIP slot.

Registration Information

More information on the conference is available the conference Web
site (http://www.tcl.tk/community/tcl2018/) and will be published on
various Tcl/Tk-related information channels.

To keep in touch with news regarding the conference, subscribe to the
tclconfere...@googlegroups.com list. See:
https://groups.google.com/forum/#!forum/tclconference for list
information, archive, and subscription.

To keep in touch with Tcl events in general, subscribe to the
tcl-announce list. See: http://code.activestate.com/lists/tcl-announce
for list information, archive, and subscription.

Re: [sqlite] Auto Index Warnings; key on deterministic functions

[J Decker]

On Mon, Feb 5, 2018 at 6:28 PM, Keith Medcalf  wrote:

>
> That is because you do not have an index on the tableB child key of the
> relation (fk).  This is required.  see the lint command in a command line
> shell near you.
>
> You do realize that a LEFT JOIN b is syntactic sugar for a LEFT OUTER JOIN
> b which means, in English, include all the rows of a whether or not there
> are (1 or more) matches in table b?  Therefore you require indexes on the
> lookup columns (unless your tables are very paltry == as in contain no
> rows).
>
> Yea;


> Multiple on clauses can be specified as in:
>
> create table tableB fk, datab, foreign key (fk) references tableA (pk) on
> delete cascade on update cascade);
>

My complaint on the index above is that it's not already just done.  being
that it is foreign, it MUST exist in PK of primary, and the FK would fault
if a PK changed without cascade updates because it now no longer references
a valid PK.

now if that weren't itself indexed of course it would need an index on the
child table then.


>
> You can create an index on a determinitic or slow-change function.
>

How? I can do...


// deterministic function
db.procedure( "hash", col=>"hash("+col+")" );
// non-determinstic function.
db.function( "myrandom", ()=>Math.random );

create table tableD ( a char );
insert into tableD( a) values (1),(2),(3),(4);
-- alter table tableD add column b char default (random());  --Error:
Cannot add a column with non-constant default
alter table tableD add column b char default (hash(a));  --Error: default
value of column [b] is not constant
select * from tableD;

create table tableE ( a char, b char default(random()) );
insert into tableE ( a) values (1),(2),(3),(4);
select * from tableE;

-- this can work if something like '12' or another function() is used
create table tableF( a char, b char default(hash(a)) ); -- Error: default
value of column [b] is not constant

create table tableF( a char, b char default(hash(random())) ); -- no error,
but wouldn't be 'constant' either...

insert into tableF ( a) values (1),(2),(3),(4);
select * from tableF;

(created with hash(random()) which is less constant than 'the value in
column a')

[ { a: 1, b: 'hash(358750534341924)' },
  { a: 2, b: 'hash(-578925135215998800)' },
  { a: 3, b: 'hash(-8823242962340456000)' },
  { a: 4, b: 'hash(1563125645423464400)' },
  { a: 1, b: 'hash(-593800428379214000)' },
  { a: 2, b: 'hash(-5699801957833211000)' },
  { a: 3, b: 'hash(-2110690358976664300)' },
  { a: 4, b: 'hash(-7312991265931917000)' },
  { a: 1, b: 'hash(-4664682697074342000)' },
  { a: 2, b: 'hash(558784664913938)' },
  { a: 3, b: 'hash(5464363349484015000)' },
  { a: 4, b: 'hash(-3159093798119682000)' } ]




but I can't (where hash is deterministic)
alter table table D add column c char default (hash(a)) )



>
>
> ---
> 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 J Decker
> >Sent: Monday, 5 February, 2018 18:24
> >To: General Discussion of SQLite Database
> >Subject: [sqlite] Auto Index Warnings; key on deterministic functions
> >
> >I have a couple tables like...
> >
> >create table tableA ( pk PRIMARY KEY, dataA )
> >create table tableB ( fk, dataB, FOREIGN KEY (fk) REFERENCES
> >tableA(pk) ON
> >DELETE CASCADE )
> >
> >if the table was also ON UPDATE CASCADE could it slave to the same
> >index as
> >primary key?
> >
> >doing a LEFT JOIN on the tables  I get a warning message about
> >automatic
> >index is being generated...
> >
> >---
> >Separately, can a key be added to a table that is a determinstic
> >function?
> >
> >alter tableB add column dFuncB char default dFunc(dataB)
> >create INDEX dFuncIndex on tableB ( dFuncB )
> >
> >where dFunc is a deterministic function... oh wait, it can just be a
> >function... but it can't reference a column value as the source of
> >its
> >data... the expression part needs to be constant.
> >I suppose I can add triggers to the table to update the default
> >value.
> >
> >but then that function is non-constant... so how does that index
> >work?
> >
> >
> >Kind of a X-Y problem, that is what I thought I might like to do is
> >just
> >have a key into a table that's partial, because not all entries in
> >the
> >table will be referenced by that key... (although since it is
> >algorithmic,
> >could just be filled anyway).   But then if a could be made, the
> >value
> >wouldn't actually have to exist in the table, since I really never
> >need
> >that value, but just need to lookup by the value...
> >
> >create INDEX dFuncIndex on tableB ( (dFunc(dataB)) )
> >
> >I CAN do
> >
> >select * from tableB where dFunc(dataB) === 'some value'
> >
> >but that's not indexed at all.
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglis

Re: [sqlite] Auto Index Warnings; key on deterministic functions

[Keith Medcalf]

That is because you do not have an index on the tableB child key of the 
relation (fk).  This is required.  see the lint command in a command line shell 
near you.

You do realize that a LEFT JOIN b is syntactic sugar for a LEFT OUTER JOIN b 
which means, in English, include all the rows of a whether or not there are (1 
or more) matches in table b?  Therefore you require indexes on the lookup 
columns (unless your tables are very paltry == as in contain no rows). 

Multiple on clauses can be specified as in:

create table tableB fk, datab, foreign key (fk) references tableA (pk) on 
delete cascade on update cascade);

You can create an index on a determinitic or slow-change function.



---
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 J Decker
>Sent: Monday, 5 February, 2018 18:24
>To: General Discussion of SQLite Database
>Subject: [sqlite] Auto Index Warnings; key on deterministic functions
>
>I have a couple tables like...
>
>create table tableA ( pk PRIMARY KEY, dataA )
>create table tableB ( fk, dataB, FOREIGN KEY (fk) REFERENCES
>tableA(pk) ON
>DELETE CASCADE )
>
>if the table was also ON UPDATE CASCADE could it slave to the same
>index as
>primary key?
>
>doing a LEFT JOIN on the tables  I get a warning message about
>automatic
>index is being generated...
>
>---
>Separately, can a key be added to a table that is a determinstic
>function?
>
>alter tableB add column dFuncB char default dFunc(dataB)
>create INDEX dFuncIndex on tableB ( dFuncB )
>
>where dFunc is a deterministic function... oh wait, it can just be a
>function... but it can't reference a column value as the source of
>its
>data... the expression part needs to be constant.
>I suppose I can add triggers to the table to update the default
>value.
>
>but then that function is non-constant... so how does that index
>work?
>
>
>Kind of a X-Y problem, that is what I thought I might like to do is
>just
>have a key into a table that's partial, because not all entries in
>the
>table will be referenced by that key... (although since it is
>algorithmic,
>could just be filled anyway).   But then if a could be made, the
>value
>wouldn't actually have to exist in the table, since I really never
>need
>that value, but just need to lookup by the value...
>
>create INDEX dFuncIndex on tableB ( (dFunc(dataB)) )
>
>I CAN do
>
>select * from tableB where dFunc(dataB) === 'some value'
>
>but that's not indexed at all.
>___
>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] Auto Index Warnings; key on deterministic functions

[J Decker]

I have a couple tables like...

create table tableA ( pk PRIMARY KEY, dataA )
create table tableB ( fk, dataB, FOREIGN KEY (fk) REFERENCES tableA(pk) ON
DELETE CASCADE )

if the table was also ON UPDATE CASCADE could it slave to the same index as
primary key?

doing a LEFT JOIN on the tables  I get a warning message about automatic
index is being generated...

---
Separately, can a key be added to a table that is a determinstic function?

alter tableB add column dFuncB char default dFunc(dataB)
create INDEX dFuncIndex on tableB ( dFuncB )

where dFunc is a deterministic function... oh wait, it can just be a
function... but it can't reference a column value as the source of its
data... the expression part needs to be constant.
I suppose I can add triggers to the table to update the default value.

but then that function is non-constant... so how does that index work?


Kind of a X-Y problem, that is what I thought I might like to do is just
have a key into a table that's partial, because not all entries in the
table will be referenced by that key... (although since it is algorithmic,
could just be filled anyway).   But then if a could be made, the value
wouldn't actually have to exist in the table, since I really never need
that value, but just need to lookup by the value...

create INDEX dFuncIndex on tableB ( (dFunc(dataB)) )

I CAN do

select * from tableB where dFunc(dataB) === 'some value'

but that's not indexed at all.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vetting SQLite

[J Decker]

On Mon, Feb 5, 2018 at 5:04 PM, Richard Hipp  wrote:

> On 2/5/18, Stephen Chrzanowski  wrote:
> > I was surprised to see that statement, so, checking my system, this isn't
> > true.  Win7Pro-x64.
>
> It's on Windows10.
>

M:\>dir c:\windows\SysWOW64\*sqlite*
 Volume in drive C is OS
 Volume Serial Number is F27E-3A0D

 Directory of c:\windows\SysWOW64

09/29/2017  05:42 AM   592,384 winsqlite3.dll



Version 3.19.3


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

[Simon Slavin]

On 6 Feb 2018, at 12:56am, J. King  wrote:

> I believe it's only since Windows 8.

Seems likely.  SQLite has been part of the Windows SDK since Windows 10 
Anniversary Update [1], some time around August 2016.  I find it plausible that 
SQLite was in Windows 8 but not in Windows 7.

By the way, the Sticky Notes App included with Windows 10 keeps its data in a 
SQLite database.

Simon.

Cite:
[1] 

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

Re: [sqlite] Vetting SQLite

[Richard Hipp]

On 2/5/18, Stephen Chrzanowski  wrote:
> I was surprised to see that statement, so, checking my system, this isn't
> true.  Win7Pro-x64.

It's on Windows10.
-- 
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] Vetting SQLite

[J. King]

I believe it's only since Windows 8. 

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

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

Re: [sqlite] Vetting SQLite

[Stephen Chrzanowski]

I was surprised to see that statement, so, checking my system, this isn't
true.  Win7Pro-x64.  Not with that filename anyways.  Searching my system
with the "Everything" tool, [ *sqlite3.exe ] comes up with DLLs that I've
touched only.  The DLL's I've dumped into the Windows directories exist in
c:\Windows\SysWOW64 only because c:\Windows\System32 is redirected there.
On my system, there is only one sqlite3.dll with a timestamp of Aug 11,
2016, and is version 3.14.1.0 according to the Details tab.


On Mon, Feb 5, 2018 at 6:02 PM, Richard Hipp  wrote:

> On 2/5/18, Drago, William @ CSG - NARDA-MITEQ 
> wrote:
> >
> > Most of the software we use here, Microsoft and other well-known and
> > paid-for products,
>
> You know that every copy of Windows comes with SQLite preinstalled,
> right?  C:\Windows\System32\winsqlite3.dll
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vetting SQLite

[Simon Slavin]

On 5 Feb 2018, at 11:02pm, Richard Hipp  wrote:

> On 2/5/18, Drago, William @ CSG - NARDA-MITEQ  wrote:
>> 
>> Most of the software we use here, Microsoft and other well-known and
>> paid-for products,
> 
> You know that every copy of Windows comes with SQLite preinstalled,
> right?  C:\Windows\System32\winsqlite3.dll

And SQLite is used internally in several parts of Microsoft Office.  For 
example, Outlook's database in Mac Office 365 is a SQLite database[1].  So if 
corporate have okayed the use of Office they've okayed a use (though arguably 
not general use) of SQLite.

Microsoft's own .NET library is Microsoft.Data.SQLite but not all of 
Microsoft's own tools use it since it is part of a long dependency chain which 
makes compiled apps rather large.

Simon.

Cite:
[1] 

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

Re: [sqlite] Vetting SQLite

[Richard Hipp]

On 2/5/18, Drago, William @ CSG - NARDA-MITEQ  wrote:
>
> Most of the software we use here, Microsoft and other well-known and
> paid-for products,

You know that every copy of Windows comes with SQLite preinstalled,
right?  C:\Windows\System32\winsqlite3.dll
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vetting SQLite

[Drago, William @ CSG - NARDA-MITEQ]

To all that replied, thank you. Open source, not open contribution is a plus, 
so is the wide deployment and well known users (Airbus). There were many other 
good ideas mentioned like examining the source for network calls, etc. All of 
this will help me build a case in favor of SQLite.

No one here is denying the utility and value of open source software. Our IT 
dept. is following corporate mandates designed to protect our networks from 
various threats. It is understandable.

Most of the software we use here, Microsoft and other well-known and paid-for 
products, are validated by corporate before deployment, and there are regular 
scans and updates. When everyone else in the company is using Microsoft SQL 
Server Express and I'm using SQLite instead it raises eyebrows. The last thing 
we need is some rouge engineer (could be me) breaking all our centrifuges with 
"freeware from the internet" when he should have used approved software 
instead. I know SQLite is safe and secure, but the auditors only know what is 
on their lists.

Thanks again for all of your suggestions. I am a regular reader of this group 
because I learn so much.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com
CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of 
the intended recipient and may contain material that is proprietary, 
confidential, privileged or otherwise legally protected or restricted under 
applicable government laws. Any review, disclosure, distributing or other use 
without expressed permission of the sender is strictly prohibited. If you are 
not the intended recipient, please contact the sender and delete all copies 
without reading, printing, or saving..

Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of all 
@L-3Com.com email addresses. To ensure delivery of your messages to this 
recipient, please update your records to use william.dr...@l3t.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vetting SQLite

[John Long]

On Mon, 2018-02-05 at 09:39 -0800, Jens Alfke wrote:
> > On Feb 5, 2018, at 9:21 AM, Drago, William @ CSG - NARDA-MITEQ  > liam.dr...@l3t.com> wrote:
> > 
> > The reliable part is easy because there is enough information on
> > the SQLite website about testing, but what about security?
> 
> Open source software is more secure than closed source, since the
> source code can be reviewed and audited.

It is considered more easy to verify, sure. But there are still some
big questions:

1. How do you know the source you're looking at is what you're running?

2. How do you know the source you're seeing is compiled correctly? Look
at the buglists for common (*cough* gcc *cough*) compilers.

3. How do you know the CPU you are running on is running the code
correctly and that it is secure? Common microprocessor vendors have
hundreds of errata for chips still being sold.

The only way to know what code is doing is to trace it on the target
hardware. We don't need source code for that. And even that could be
misleading if the hardware is broken or deliberately subverted.

>  (In the security field, closed-source cryptographic software isn’t
> even taken seriously since it’s not possible to verify its claims,
> just as scientific results need peer review and independent
> confirmation.)

That is true but perhaps closed-source cryptographic _algorithms_ are
the issue and not source code. And this is just for reference
implementations... you can still verify exactly what you have without
source code. It just takes more effort and personally I believe it's
more reliable.

I don't believe RSA or IBM or any of the other vendors have open
sourced any crypto code. I think what typically happens is when they
come up with a new standard they produce a reference implementation and
then after the contest is over they implement whatever they implement
and everybody just uses it. 

> I don’t know if this will convince your IT management though, because
> if they’re against open source they must be remarkably backward...

I don't think that is necessarily so. Many companies want/need to be
able to point fingers when something goes wrong. And they need to get
their systems working ASAP. The vast majority of open source projects
have no accountability, they're free as in beer and as long as it works
for the guys spending their time writing it they're done. Companies
(especially publicly owned and traded companies) really can not rely on
freebies and goodwill if they want to stay in business and keep their
executives out of jail. Open source quality is atrocious. Sure, a lot
of closed source quality is atrocious too. Free stuff should be
expected to be worth price paid and most of the time it is not even
that.

sqlite (and fossil!) are wonderful, wonderful projects. But there is a
sea of unsupported garbage out there and nobody who wants to keep their
job can feel safe wading through that. There is also the issue of viral
contamination of GPL, etc.

I think Dr. Hipp did everything right but even so, he is in the tiny
minority.

/jl



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

Re: [sqlite] Vetting SQLite

[John Found]

On Mon, 5 Feb 2018 17:21:53 +
"Drago, William @ CSG - NARDA-MITEQ"  wrote:

> All,
> 
> I've been using/loving SQLite for years, but the use of open source software 
> is highly discouraged where I work, and now I have to prove to our IT dept. 
> that SQLite is reliable and secure. The reliable part is easy because there 
> is enough information on the SQLite website about testing, but what about 
> security? How can I convince the auditors that SQLite is not stealing 
> corporate secrets and spreading viruses?
> 

The open code is actually the only code that can be proofed to be secure. The 
written guarantee is pointless actually because the malware is always 
introduced in secret. The procedure is following: 

1. Download the SQLite code from the official repository.
2. Audit the code in order to proof it does not contains 
malware/spyware/security flaws.
3. Compile the code and link it against the dependencies proofed to be secure! 
(this is important!)
4. You have SQLite proven to be secure.

The only problem is p.3, but if your company is so paranoid about security, you 
already have audited the standard 
C libraries. 


> Is there a statement somewhere on the website that guarantees that copies of 
> SQLIte downloaded from SQLite.org and System.Data.Sqlite.org are free of all 
> forms of spyware/malware/viruses/etc?
> 
> Thanks,
> --
> Bill Drago
> Staff Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / william.dr...@l3t.com
> 
> CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use 
> of the intended recipient and may contain material that is proprietary, 
> confidential, privileged or otherwise legally protected or restricted under 
> applicable government laws. Any review, disclosure, distributing or other use 
> without expressed permission of the sender is strictly prohibited. If you are 
> not the intended recipient, please contact the sender and delete all copies 
> without reading, printing, or saving..
> 
> Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of 
> all @L-3Com.com email addresses. To ensure delivery of your messages to this 
> recipient, please update your records to use william.dr...@l3t.com.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vetting SQLite

[Bob Friesenhahn]

On Mon, 5 Feb 2018, Jens Alfke wrote:

You can very easily prove that SQLite contains no networking code, 
so it’s incapable of accessing any network. Just search through 
sqlite3.c looking for the names of the system calls needed to open a 
socket; they don’t appear. Or more rigorously, use a 
(platform-specific) tool to dump the list of external functions 
called by the compiled SQLite library.


The default configuration of SQLite does have the possibilty of 
executing network code since it is able to load external shared 
libraries as modules and the modules can contain arbitrary code.


The security of SQLite depends on how it is built, the environment in 
which it is used, and the arguments supplied to it.


If arbitrary SQL commands can be sent into SQLite, then good luck and 
best wishes regarding security.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vetting SQLite

[Richard Hipp]

On 2/5/18, Drago, William @ CSG - NARDA-MITEQ  wrote:
> All,
>
> I've been using/loving SQLite for years, but the use of open source software
> is highly discouraged where I work, and now I have to prove to our IT dept.
> that SQLite is reliable and secure. The reliable part is easy because there
> is enough information on the SQLite website about testing, but what about
> security? How can I convince the auditors that SQLite is not stealing
> corporate secrets and spreading viruses?
>
> Is there a statement somewhere on the website that guarantees that copies of
> SQLIte downloaded from SQLite.org and System.Data.Sqlite.org are free of all
> forms of spyware/malware/viruses/etc?

As for SQLite itself, every byte of source code can be traced back to
the specific individual who wrote it.  Most of those bytes are from
just two people.  All contributors are either US or Australian
citizens.  Not only is every line of source code originated from a
fully vetted individual, but we have proof that every line of code is
tested.  There is no opportunity for a virus to slip in.

SQLite is open-source, but it is not open-contribution.  Do not
confuse these two concepts.  Anybody can read and use the SQLite
sources, but very few peopled are allowed to commit changes.  All
committers are personally known to me.  We do not except drive-by
patches.  SQLite does not contain code that has been copy/pasted from
the internet.  All of the code in the SQLite core is purposefully
written specifically for the SQLite core.

SDS is slightly more problematic.  The biggest chunk of that code was
inherited, and we cannot vouch for the provenance of that inherited
code.  On the other hand, we have had total control SDS since 2010,
and nothing has come up during the subsequent 8 years of development
and maintenance.  Since 2011, all check-ins to the SDS source code
have come from just 3 individuals, with all but about 8 check-ins from
a single programmer who is a US citizen and fully vetted and known
personally to me.

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

[Igor Korot]

Hi,


On Mon, Feb 5, 2018 at 11:41 AM, Simon Slavin  wrote:
> On 5 Feb 2018, at 5:21pm, Drago, William @ CSG - NARDA-MITEQ 
>  wrote:
>
>> I've been using/loving SQLite for years, but the use of open source software 
>> is highly discouraged where I work, and now I have to prove to our IT dept. 
>> that SQLite is reliable and secure. The reliable part is easy because there 
>> is enough information on the SQLite website about testing, but what about 
>> security? How can I convince the auditors that SQLite is not stealing 
>> corporate secrets and spreading viruses?

Out of curiosity - does your company do the security scans quarterly
to make sure that the system (whatever is used) and the software you
guys provide are free for all security vulnerabilities?
As an example - here we do the scans quarterly, than check all
findings against RHSA (we use Red Hat Enterprise) and then fix them.
And then do quarterly security releases for the OS and software.

I'm sure Windows have the same Security Vulnerabilities DB where you
can check what should be fixed by the update, which will be done
automatically anyway.
And if you have a source code scanner(s) - you are in luck as you can
just check the code and fix it.

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

Re: [sqlite] Vetting SQLite

[Jens Alfke]

> On Feb 5, 2018, at 9:21 AM, Drago, William @ CSG - NARDA-MITEQ 
>  wrote:
> 
> The reliable part is easy because there is enough information on the SQLite 
> website about testing, but what about security?

Open source software is more secure than closed source, since the source code 
can be reviewed and audited. (In the security field, closed-source 
cryptographic software isn’t even taken seriously since it’s not possible to 
verify its claims, just as scientific results need peer review and independent 
confirmation.)

>  How can I convince the auditors that SQLite is not stealing corporate 
> secrets and spreading viruses?

You can very easily prove that SQLite contains no networking code, so it’s 
incapable of accessing any network. Just search through sqlite3.c looking for 
the names of the system calls needed to open a socket; they don’t appear. Or 
more rigorously, use a (platform-specific) tool to dump the list of external 
functions called by the compiled SQLite library.

It should also be fairly easy to look through the code to prove that SQLite 
doesn’t open any files other than the ones specifically requested by the caller 
(plus the -wal and -shm side files) so it can’t be stealing data or writing 
viruses into system software.

I don’t know if this will convince your IT management though, because if 
they’re against open source they must be remarkably backward...

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

Re: [sqlite] Vetting SQLite

[Simon Slavin]

On 5 Feb 2018, at 5:21pm, Drago, William @ CSG - NARDA-MITEQ 
 wrote:

> I've been using/loving SQLite for years, but the use of open source software 
> is highly discouraged where I work, and now I have to prove to our IT dept. 
> that SQLite is reliable and secure. The reliable part is easy because there 
> is enough information on the SQLite website about testing, but what about 
> security? How can I convince the auditors that SQLite is not stealing 
> corporate secrets and spreading viruses?

What's "CSG" ?  Chief of Security Group ?

The ideal way would seem to be that you download the source code and compile it 
yourself.  Which is actually the preferred way to use SQLite in the first 
place. On the download page download the top item "C source code as an 
amalgamation".  You get your own copy of the source code to inspect and compile 
as you wish.  They can spend as long as they want looking for concealed IP 
addresses and system calls.

> Is there a statement somewhere on the website that guarantees that copies of 
> SQLIte downloaded from SQLite.org and System.Data.Sqlite.org are free of all 
> forms of spyware/malware/viruses/etc?

That's harder.  How does your organisation inspect other pre-compiled libraries 
?  Does it have established uniform standards or are you suddenly being asked 
to make up your own ?

You can download the DLL from the SQLite site, and verify that the checksum is 
correct.  You can compile the DLL yourself (you may need Joe's help) and check 
to see it's a byte-for-byte copy.  You can use tools which inspect the DLL and 
show its dependencies.  You won't find anything in there that has internet 
access.  That's a pretty good first step since you can't steal information 
without internet access, and most vulnerability toolkits take their 
instructions over the internet.

If you have specific questions, post them here.  Or pay my consultancy rate.  
Heh.

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

Re: [sqlite] Vetting SQLite

[J. King]

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



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

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

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

[sqlite] Vetting SQLite

[Drago, William @ CSG - NARDA-MITEQ]

All,

I've been using/loving SQLite for years, but the use of open source software is 
highly discouraged where I work, and now I have to prove to our IT dept. that 
SQLite is reliable and secure. The reliable part is easy because there is 
enough information on the SQLite website about testing, but what about 
security? How can I convince the auditors that SQLite is not stealing corporate 
secrets and spreading viruses?

Is there a statement somewhere on the website that guarantees that copies of 
SQLIte downloaded from SQLite.org and System.Data.Sqlite.org are free of all 
forms of spyware/malware/viruses/etc?

Thanks,
--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com

CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of 
the intended recipient and may contain material that is proprietary, 
confidential, privileged or otherwise legally protected or restricted under 
applicable government laws. Any review, disclosure, distributing or other use 
without expressed permission of the sender is strictly prohibited. If you are 
not the intended recipient, please contact the sender and delete all copies 
without reading, printing, or saving..

Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of all 
@L-3Com.com email addresses. To ensure delivery of your messages to this 
recipient, please update your records to use william.dr...@l3t.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Optimization - don't understand.

[John Found]

On Mon, 5 Feb 2018 15:08:33 +
Hick Gunter  wrote:

> I think you are optimizing the performance of a conceptually inefficient 
> query.
> 
> If you are looking for a recipe that contains apples, do you read the entire 
> cook book, checking each recipe for apples? Maybe it is much more efficient 
> to look up apples in the index of ingredients and retrieve only the recipes 
> that actually contain them.

You are definitely right, but the things are a little bit more complex. 

The query I asked for is simplified in order to make the question more clear. 
It is part of a complex search, looking simultaneously in several fields: fts5 
search (removed for simplicity), T.Caption, TT.Tag and U.nick fields. 

I am trying to estimate how exactly to handle all these possible combinations 
and whether it is possible to be done with one fixed query or need specially 
synthesized query for every particular case.


> 
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von John Found
> Gesendet: Montag, 05. Februar 2018 15:55
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [EXTERNAL] Re: [sqlite] Optimization - don't understand.
> 
> It is clear now. But should I define an index that contains all fields used 
> in the query?
> 
> Something like:
> 
> create index idxPostsComplex on posts(threadid, userid, Content, 
> postTime, ReadCount);
> 
> Actually I tried and the query uses this index without problems (and the 
> performance seems to be good).
> 
> But what are the disadvantages of such approach? (except the bigger database 
> size, of course)
> 
> On Mon, 5 Feb 2018 09:24:51 -0500
> Richard Hipp  wrote:
> 
> > On 2/5/18, John Found  wrote:
> > > The following query:
> > >
> > > explain query plan
> > > select
> > >   U.nick,
> > >   U.id,
> > >   U.av_time,
> > >   T.Caption,
> > >   P.id,
> > > --  P.ReadCount,
> > > --  P.Content,
> > > --  P.postTime,l
> > >   T.Caption
> > > from Posts P
> > > left join Threads T on P.threadID = T.id
> > > left join ThreadTags TT on TT.threadID = T.id
> > > left join Users U on P.userID = U.id
> > > where TT.Tag = ?1;
> > >
> > > ...returns:
> > >
> > > 0 0 0 SCAN TABLE Posts AS P USING COVERING INDEX idxPostsThreadUser
> > > 0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
> > > 0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX
> > > idxThreadTagsUnique (ThreadID=? AND Tag=?)
> > > 0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY
> > > (rowid=?)
> > >
> > > But uncommenting any of the commented fields, turns the result into:
> > >
> > > 0 0 0 SCAN TABLE Posts AS P
> > > 0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
> > > 0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX
> > > idxThreadTagsUnique (ThreadID=? AND Tag=?)
> > > 0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY
> > > (rowid=?)
> > >
> > > ... and significantly degrades the performance.
> > >
> > > The index idxPostsThreadUser is defined following way:
> > >
> > > create index idxPostsThreadUser on Posts(threadid, userid);
> > >
> > > IMHO, the change of the selected columns should not affect the query
> > > plan, but maybe I am wrong somehow.
> > >
> > > What I am missing?
> > >
> >
> > SQLite prefers to scan the index rather than the original table,
> > because the index is usually smaller (since it contains less data) and
> > hence there is less I/O required to scan the whole thing.
> >
> > But the index only provides access to the id, threadid, and userid
> > columns.  If content of other columns is needed, then the whole table
> > must be scanned instead.
> > --
> > 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
> 
> 
> --
> http://fresh.flatassembler.net
> http://asm32.info
> John Found 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> ___
>  Gunter Hick | Software Engineer | Scientific Games International GmbH | 
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) 
> +43 1 80100 - 0
> 
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/

Re: [sqlite] Optimization - don't understand.

[Simon Slavin]

On 5 Feb 2018, at 2:54pm, John Found  wrote:

> It is clear now. But should I define an index that contains all fields used 
> in the query?
> 
> Something like:
> 
>create index idxPostsComplex on posts(threadid, userid, Content, postTime, 
> ReadCount);
> 
> Actually I tried and the query uses this index without problems (and the 
> performance seems to be good). 

Since you have the ability to do timings, why not try it ?  Do things get much 
faster after creating that extra index ?  Is the SELECT one which is used a lot 
or is it used just once in your program, at a time where execution time is not 
a problem ?

> But what are the disadvantages of such approach? (except the bigger database 
> size, of course)

* Increase in database file size
* More time taken when inserting rows into that table (one extra index to 
update)
* More time taken when changing data in any of those columns (one extra index 
to update)

This is the usual payoff in computers: if it takes less time to find the data 
you want it probably takes longer to assemble the data to start with.  You have 
to figure out which of the two operations is more time-critical.

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

Re: [sqlite] [EXTERNAL] Re: Optimization - don't understand.

[Hick Gunter]

I think you are optimizing the performance of a conceptually inefficient query.

If you are looking for a recipe that contains apples, do you read the entire 
cook book, checking each recipe for apples? Maybe it is much more efficient to 
look up apples in the index of ingredients and retrieve only the recipes that 
actually contain them.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von John Found
Gesendet: Montag, 05. Februar 2018 15:55
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] Optimization - don't understand.

It is clear now. But should I define an index that contains all fields used in 
the query?

Something like:

create index idxPostsComplex on posts(threadid, userid, Content, postTime, 
ReadCount);

Actually I tried and the query uses this index without problems (and the 
performance seems to be good).

But what are the disadvantages of such approach? (except the bigger database 
size, of course)

On Mon, 5 Feb 2018 09:24:51 -0500
Richard Hipp  wrote:

> On 2/5/18, John Found  wrote:
> > The following query:
> >
> > explain query plan
> > select
> >   U.nick,
> >   U.id,
> >   U.av_time,
> >   T.Caption,
> >   P.id,
> > --  P.ReadCount,
> > --  P.Content,
> > --  P.postTime,l
> >   T.Caption
> > from Posts P
> > left join Threads T on P.threadID = T.id
> > left join ThreadTags TT on TT.threadID = T.id
> > left join Users U on P.userID = U.id
> > where TT.Tag = ?1;
> >
> > ...returns:
> >
> > 0 0 0 SCAN TABLE Posts AS P USING COVERING INDEX idxPostsThreadUser
> > 0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
> > 0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX
> > idxThreadTagsUnique (ThreadID=? AND Tag=?)
> > 0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY
> > (rowid=?)
> >
> > But uncommenting any of the commented fields, turns the result into:
> >
> > 0 0 0 SCAN TABLE Posts AS P
> > 0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
> > 0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX
> > idxThreadTagsUnique (ThreadID=? AND Tag=?)
> > 0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY
> > (rowid=?)
> >
> > ... and significantly degrades the performance.
> >
> > The index idxPostsThreadUser is defined following way:
> >
> > create index idxPostsThreadUser on Posts(threadid, userid);
> >
> > IMHO, the change of the selected columns should not affect the query
> > plan, but maybe I am wrong somehow.
> >
> > What I am missing?
> >
>
> SQLite prefers to scan the index rather than the original table,
> because the index is usually smaller (since it contains less data) and
> hence there is less I/O required to scan the whole thing.
>
> But the index only provides access to the id, threadid, and userid
> columns.  If content of other columns is needed, then the whole table
> must be scanned instead.
> --
> 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


--
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimization - don't understand.

[Richard Hipp]

On 2/5/18, John Found  wrote:
>
> Actually I tried [adding a new index] and the query uses this index
> without problems (and the
> performance seems to be good).
>
> But what are the disadvantages of such approach? (except the bigger database
> size, of course)

(1) the database file is larger.

(2) When doing UPDATE on one of the columns added to the index, then
the index must be updated too, in addition to the table.

-- 
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] Optimization - don't understand.

[John Found]

It is clear now. But should I define an index that contains all fields used in 
the query?

Something like:

create index idxPostsComplex on posts(threadid, userid, Content, postTime, 
ReadCount);

Actually I tried and the query uses this index without problems (and the 
performance seems to be good). 

But what are the disadvantages of such approach? (except the bigger database 
size, of course)

On Mon, 5 Feb 2018 09:24:51 -0500
Richard Hipp  wrote:

> On 2/5/18, John Found  wrote:
> > The following query:
> >
> > explain query plan
> > select
> >   U.nick,
> >   U.id,
> >   U.av_time,
> >   T.Caption,
> >   P.id,
> > --  P.ReadCount,
> > --  P.Content,
> > --  P.postTime,l
> >   T.Caption
> > from Posts P
> > left join Threads T on P.threadID = T.id
> > left join ThreadTags TT on TT.threadID = T.id
> > left join Users U on P.userID = U.id
> > where TT.Tag = ?1;
> >
> > ...returns:
> >
> > 0 0 0 SCAN TABLE Posts AS P USING COVERING INDEX idxPostsThreadUser
> > 0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
> > 0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX
> > idxThreadTagsUnique (ThreadID=? AND Tag=?)
> > 0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY (rowid=?)
> >
> > But uncommenting any of the commented fields, turns the result into:
> >
> > 0 0 0 SCAN TABLE Posts AS P
> > 0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
> > 0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX
> > idxThreadTagsUnique (ThreadID=? AND Tag=?)
> > 0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY (rowid=?)
> >
> > ... and significantly degrades the performance.
> >
> > The index idxPostsThreadUser is defined following way:
> >
> > create index idxPostsThreadUser on Posts(threadid, userid);
> >
> > IMHO, the change of the selected columns should not affect the query plan,
> > but maybe I am wrong somehow.
> >
> > What I am missing?
> >
> 
> SQLite prefers to scan the index rather than the original table,
> because the index is usually smaller (since it contains less data) and
> hence there is less I/O required to scan the whole thing.
> 
> But the index only provides access to the id, threadid, and userid
> columns.  If content of other columns is needed, then the whole table
> must be scanned instead.
> -- 
> 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


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimization - don't understand.

[David Raymond]

Is P.id an integer primary key? If so then it can get it from any index. Since 
the only field it needs from P is id, it can use an index which isn't defined 
on id. This can make it quicker, especially if P has a lot of fields bloating 
its size. Once you include the other fields of P in the query then it has to go 
to the main table anyway to get those other fields and decides to do a full 
scan.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of John Found
Sent: Monday, February 05, 2018 9:14 AM
To: SQLite mailing list
Subject: [sqlite] Optimization - don't understand.

The following query:

explain query plan
select 
  U.nick,
  U.id,
  U.av_time,
  T.Caption,
  P.id,
--  P.ReadCount,
--  P.Content,
--  P.postTime,l
  T.Caption
from Posts P
left join Threads T on P.threadID = T.id
left join ThreadTags TT on TT.threadID = T.id
left join Users U on P.userID = U.id
where TT.Tag = ?1;

...returns:

0 0 0 SCAN TABLE Posts AS P USING COVERING INDEX idxPostsThreadUser
0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX 
idxThreadTagsUnique (ThreadID=? AND Tag=?)
0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY (rowid=?)

But uncommenting any of the commented fields, turns the result into:

0 0 0 SCAN TABLE Posts AS P
0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX 
idxThreadTagsUnique (ThreadID=? AND Tag=?)
0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY (rowid=?)

... and significantly degrades the performance.

The index idxPostsThreadUser is defined following way:

create index idxPostsThreadUser on Posts(threadid, userid);

IMHO, the change of the selected columns should not affect the query plan, but 
maybe I am wrong somehow.

What I am missing?

-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Optimization - don't understand.

[Hick Gunter]

The additional fields are not contained in the index idxPostsThreadUser, so 
SQLite is forced to read the original row instead of just the index.

Your query is searching the complete posts table, joining all the threads, tags 
and users together, and then discarding those without a matching tag.

Maybe you would be better off scanning the ThreadTags from the target tag, and 
then reconstructing the post from there.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von John Found
Gesendet: Montag, 05. Februar 2018 15:14
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Optimization - don't understand.

The following query:

explain query plan
select
  U.nick,
  U.id,
  U.av_time,
  T.Caption,
  P.id,
--  P.ReadCount,
--  P.Content,
--  P.postTime,l
  T.Caption
from Posts P
left join Threads T on P.threadID = T.id
left join ThreadTags TT on TT.threadID = T.id
left join Users U on P.userID = U.id
where TT.Tag = ?1;

...returns:

0 0 0 SCAN TABLE Posts AS P USING COVERING INDEX idxPostsThreadUser
0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX 
idxThreadTagsUnique (ThreadID=? AND Tag=?)
0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY (rowid=?)

But uncommenting any of the commented fields, turns the result into:

0 0 0 SCAN TABLE Posts AS P
0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX 
idxThreadTagsUnique (ThreadID=? AND Tag=?)
0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY (rowid=?)

... and significantly degrades the performance.

The index idxPostsThreadUser is defined following way:

create index idxPostsThreadUser on Posts(threadid, userid);

IMHO, the change of the selected columns should not affect the query plan, but 
maybe I am wrong somehow.

What I am missing?

--
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimization - don't understand.

[Richard Hipp]

On 2/5/18, John Found  wrote:
> The following query:
>
> explain query plan
> select
>   U.nick,
>   U.id,
>   U.av_time,
>   T.Caption,
>   P.id,
> --  P.ReadCount,
> --  P.Content,
> --  P.postTime,l
>   T.Caption
> from Posts P
> left join Threads T on P.threadID = T.id
> left join ThreadTags TT on TT.threadID = T.id
> left join Users U on P.userID = U.id
> where TT.Tag = ?1;
>
> ...returns:
>
> 0 0 0 SCAN TABLE Posts AS P USING COVERING INDEX idxPostsThreadUser
> 0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
> 0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX
> idxThreadTagsUnique (ThreadID=? AND Tag=?)
> 0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY (rowid=?)
>
> But uncommenting any of the commented fields, turns the result into:
>
> 0 0 0 SCAN TABLE Posts AS P
> 0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
> 0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX
> idxThreadTagsUnique (ThreadID=? AND Tag=?)
> 0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY (rowid=?)
>
> ... and significantly degrades the performance.
>
> The index idxPostsThreadUser is defined following way:
>
> create index idxPostsThreadUser on Posts(threadid, userid);
>
> IMHO, the change of the selected columns should not affect the query plan,
> but maybe I am wrong somehow.
>
> What I am missing?
>

SQLite prefers to scan the index rather than the original table,
because the index is usually smaller (since it contains less data) and
hence there is less I/O required to scan the whole thing.

But the index only provides access to the id, threadid, and userid
columns.  If content of other columns is needed, then the whole table
must be scanned instead.
-- 
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] Optimization - don't understand.

[John Found]

The following query:

explain query plan
select 
  U.nick,
  U.id,
  U.av_time,
  T.Caption,
  P.id,
--  P.ReadCount,
--  P.Content,
--  P.postTime,l
  T.Caption
from Posts P
left join Threads T on P.threadID = T.id
left join ThreadTags TT on TT.threadID = T.id
left join Users U on P.userID = U.id
where TT.Tag = ?1;

...returns:

0 0 0 SCAN TABLE Posts AS P USING COVERING INDEX idxPostsThreadUser
0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX 
idxThreadTagsUnique (ThreadID=? AND Tag=?)
0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY (rowid=?)

But uncommenting any of the commented fields, turns the result into:

0 0 0 SCAN TABLE Posts AS P
0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX 
idxThreadTagsUnique (ThreadID=? AND Tag=?)
0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY (rowid=?)

... and significantly degrades the performance.

The index idxPostsThreadUser is defined following way:

create index idxPostsThreadUser on Posts(threadid, userid);

IMHO, the change of the selected columns should not affect the query plan, but 
maybe I am wrong somehow.

What I am missing?

-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite 3.22.0 walro2 test failures on ppc64

[Dan Kennedy]

On 02/05/2018 04:22 PM, Petr Kubat wrote:

On 02/05/2018 08:41 AM, Petr Kubat wrote:


Hi all,

hitting some failures when building 3.22.0 on ppc64 boxes (both 
big-endian and little-endian) running Fedora Rawhide (full logs in 
[1][2]):


Time: walro.test 135 ms
! walro2-1.3.2.2 expected: [0 32768]
! walro2-1.3.2.2 got:  [0 65536]
! walro2-1.3.3.0 expected: [4224 32768]
! walro2-1.3.3.0 got:  [4224 65536]
! walro2-1.3.3.2 expected: [4224 32768]
! walro2-1.3.3.2 got:  [4224 65536]
! walro2-2.3.2.2 expected: [0 32768]
! walro2-2.3.2.2 got:  [0 65536]
! walro2-2.3.3.0 expected: [4224 32768]
! walro2-2.3.3.0 got:  [4224 65536]
! walro2-2.3.3.2 expected: [4224 32768]
! walro2-2.3.3.2 got:  [4224 65536]
Time: walro2.test 513 ms

From what I can see the expected sizes of the database files seem to 
be half of what is the actual size.


To correct myself here - its the "-wal" file that gets twice as big as 
expected. btw the starting size of the "-wal" file on ppc64 is the 
same (65536 bytes) even in older versions of sqlite (looking at 3.20.1 
right now).



Thanks for reporting this. I think it's just a problem with the test 
script. Now fixed here:


  http://www.sqlite.org/src/info/d9e59cfb8476e1ec

Dan.







Petr

[1]: 
https://kojipkgs.fedoraproject.org//work/tasks/9900/24679900/build.log
[2]: 
https://kojipkgs.fedoraproject.org//work/tasks/9898/24679898/build.log


___
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] fts5 giving results for substring searches for Hindi content.

[Dan Kennedy]

On 02/04/2018 11:39 AM, raj Singla wrote:

Hi,

-- create fts4 and fts5 tables
create virtual table idx4 using "fts4" (content);
create virtual table idx5 using "fts5" (content);
-- insert 1 sample rows into eachinsert into idx4 (content) values
('नीरजा भनोट के कातिल पाकिस्तान की जेल में थे, फिर वे एफबीआई
आए?');insert into idx5 (content) values ('नीरजा भनोट के कातिल
पाकिस्तान की जेल में थे, फिर वे एफबीआई आए?');
-- query index using complete and partial stringsselect * from idx4
where idx4 match 'पाकिस्तान';-- returns नीरजा भनोट के कातिल पाकिस्तान
की जेल में थे, फिर वे एफबीआई आए?
select * from idx4 where idx4 match 'पाकि';-- no results returned
select * from idx5 where idx5 match 'पाकिस्तान';-- returns नीरजा भनोट
के कातिल पाकिस्तान की जेल में थे, फिर वे एफबीआई आए?
select * from idx5 where idx5 match 'पाकि';-- returns नीरजा भनोट के
कातिल पाकिस्तान की जेल में थे, फिर वे एफबीआई आए?


fts5 giving results for substring searches for Hindi content.
Is this expected behavior.
Please if you can provide more insights on this. Maybe this is just an
experimental feature.


By default, FTS5 uses a unicode tokenizer based on data extracted from 
reference file "UnicodeData.txt":


http://www.unicode.org/Public/6.1.0/ucd/UnicodeData.txt

Which divides the characters into categories:

  http://www.fileformat.info/info/unicode/category/index.htm

FTS5 considers categories "Co", "L*" and "N*" to be token characters and 
all others to be separator characters (handled in the same way as spaces).


The string "पाकिस्तान" contains 9 characters, 3 of which are from the 
"Mn" and "Mc" categories, specifically 0x93E, 0x93F, 0x94D and 0x93E. 
According to UnicodeData.txt, these characters are:


  093E;DEVANAGARI VOWEL SIGN AA;Mc;
  093F;DEVANAGARI VOWEL SIGN I;Mc;
  094D;DEVANAGARI SIGN VIRAMA;Mn;

And so the string is being split into several (actually 5 - as there are 
two instances of 0x93E) different words. Given your report, I'm guessing 
that is not what people expect. Can you, or any other Hindi speaker, 
confirm that "पाकिस्तान" should be treated as a single word by FTS5? And 
not broken into several different words?


Dan.














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] sqlite 3.22.0 walro2 test failures on ppc64

[Petr Kubat]

On 02/05/2018 08:41 AM, Petr Kubat wrote:


Hi all,

hitting some failures when building 3.22.0 on ppc64 boxes (both 
big-endian and little-endian) running Fedora Rawhide (full logs in 
[1][2]):


Time: walro.test 135 ms
! walro2-1.3.2.2 expected: [0 32768]
! walro2-1.3.2.2 got:  [0 65536]
! walro2-1.3.3.0 expected: [4224 32768]
! walro2-1.3.3.0 got:  [4224 65536]
! walro2-1.3.3.2 expected: [4224 32768]
! walro2-1.3.3.2 got:  [4224 65536]
! walro2-2.3.2.2 expected: [0 32768]
! walro2-2.3.2.2 got:  [0 65536]
! walro2-2.3.3.0 expected: [4224 32768]
! walro2-2.3.3.0 got:  [4224 65536]
! walro2-2.3.3.2 expected: [4224 32768]
! walro2-2.3.3.2 got:  [4224 65536]
Time: walro2.test 513 ms

From what I can see the expected sizes of the database files seem to 
be half of what is the actual size.


To correct myself here - its the "-wal" file that gets twice as big as 
expected. btw the starting size of the "-wal" file on ppc64 is the same 
(65536 bytes) even in older versions of sqlite (looking at 3.20.1 right 
now).




Petr

[1]: 
https://kojipkgs.fedoraproject.org//work/tasks/9900/24679900/build.log
[2]: 
https://kojipkgs.fedoraproject.org//work/tasks/9898/24679898/build.log


___
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