Re: [sqlite] Why doesn't SQLite optimise this subselect?

[Dinu]

If you want just 1 value (any) from lookup, you can use:

SELECT
 post_processing_info, 
 (
SELECT
is_json
FROM
tables_lookup
WHERE
tables_lookup.content_hash=webpage_contents.content_hash
LIMIT 1
 ) AS is_json
FROM 
 webpage_contents
WHERE 
 content_hash = 'abc' 

If you want only one value,
or

SELECT
post_processing_info, 
x.is_json
FROM
 webpage_contents
JOIN
 (
SELECT 
content_hash,
MIN(is_json)
FROM
tables_lookup
GROUP BY
content_hash
 ) AS x ON
 x.content_hash=webpage_contents.content_hash
FROM 
 webpage_contents

if you want the full join... I'm using MIN() here as an arbitrary function
to get just one value from the group.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_decltype and max and min

[Dinu]

Keith Medcalf wrote
> Full Schema Tables:

Thanks for this, I will have grat use for it too, soon! Actually it's so
nice I think it could go into the documentation.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_decltype and max and min

[Bart Smissaert]

OK, thanks.
I think I will code something along similar lines, so that I have the
declared column type (the custom types, eg int_date) always easily
available.
Of course with casting and UDF's etc. it will get a bit tricky.

RBS



On Sat, Jan 6, 2018 at 11:26 PM, Keith Medcalf  wrote:

>
> This is how pySqlite2 (aka sqlite3) wrapper in Python achieves such data
> manipulations, by tagging the column name specially (since by default rows
> are only retrieved and accessible as positional locations in a row tuple.
> If you open the database and tell it to "parse column types" then it will
> retrieve the column name from the cursor description then split the column
> name at the space.  The righthand token will be used to lookup a
> "converter" function in a dictionary of "converters", and will then apply
> the converter function to the retrieved data for that column AFTER it is
> retrieved from SQLite3 and BEFORE it is returned to the user.
>
> That means that you could write a function called
> "JulianToDatetime(number)" and write your SQL Query (with column name
> parsing enabled) as
>
> select column1 as [column1 JulianDateTime] from table;
>
> and add a converter entry {"JulianDateTime": JulianToDateTime} and the
> field would automagically and transparently be "converted" with the
> JulianToDateTime function before it is returned to the user program.  For
> data going the other way, you can write an "adapter" function that converts
> the Python "datetime" type into a JulianDay number that occurs
> automagically between you passing the data binding to the wrapper, and the
> wrapper passing the data into SQLite3.
>
> pySqlite2 (now sqlite3) Python wrapper can also parse the table declared
> type in the same way to apply automatic coverters on data retrieval (I do
> not know how it gets the declared type -- I'd have to go look in the source
> for pySqlite2.  However, once a returned column is an aggregate or an
> expression, it likely will not carry the origin declaration type anymore
> and only overloading the datatype into the column name (via as) will work.
>
> ---
> 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 Bart Smissaert
> >Sent: Saturday, 6 January, 2018 15:54
> >To: SQLite mailing list
> >Subject: Re: [sqlite] sqlite3_column_decltype and max and min
> >
> >OK, thanks will have a look at that.
> >So, how about this one then:
> >
> >select max(integer_date) as [max_integer_date int_date] from table1
> >
> >?
> >
> >RBS
> >
> >On Sat, Jan 6, 2018 at 10:35 PM, Keith Medcalf 
> >wrote:
> >
> >>
> >> Yes.  You can use "pragma table_info(tablename)" to get information
> >about
> >> a table, the whole table, and nothing but the one table.  Or you
> >can use
> >> the bunch-o-views which will give you a data dictionary to be able
> >to get
> >> the same data by treating all the various introspection pragma's as
> >tables
> >> and letting you query for specific information such as:
> >>
> >> select affinity from SysColumns where ObjectType='table' and
> >ObjectName='myTableThatIAMInterestedIn'
> >> and
> >ColumnName='TheColumnNameForWhichIWantToKnowTheDeclaredAffinity';
> >>
> >> or perhaps even
> >>
> >> select ObjectType, ObjectName, from SysColumns where ColumnName = '
> >> GollyJesusHereAreAllTheObjectsHavingColumnsWithThisName';
> >>
> >>
> >> One is simply more selective that the other.  The pragma's use
> >> ill-conceived field names (containing spaces and other kaiboshery)
> >whereas
> >> the views rename such ill-conception to something not quite so ill
> >such
> >> that "escaping" is not required (which is as God intended when she
> >invented
> >> the concept of variable naming and so forth).
> >>
> >> ---
> >> 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 Bart Smissaert
> >> >Sent: Saturday, 6 January, 2018 15:00
> >> >To: SQLite mailing list
> >> >Subject: Re: [sqlite] sqlite3_column_decltype and max and min
> >> >
> >> >Not sure how this works.
> >> >I had a look at your views.
> >> >Are you saying that I should get the data type (only if
> >> >sqlite3_column_decltype produces null) by querying these views,
> >> >passing it
> >> >the column name?
> >> >
> >> >RBS
> >> >
> >> >
> >> >On Sat, Jan 6, 2018 at 7:27 PM, Keith Medcalf
> >
> >> >wrote:
> >> >
> >> >>
> >> >> select max(integer_date) as [max_integer_date int_date] from
> >table1
> >> >>
> >> >>
> >> >> fetch the cursor.description and split the column name at the
> >> >space.  The
> >> >> right part is the datatype for your handy dandy use.
> >> >>
> >> >> You may 

Re: [sqlite] sqlite3_column_decltype and max and min

[Keith Medcalf]

This is how pySqlite2 (aka sqlite3) wrapper in Python achieves such data 
manipulations, by tagging the column name specially (since by default rows are 
only retrieved and accessible as positional locations in a row tuple.  If you 
open the database and tell it to "parse column types" then it will retrieve the 
column name from the cursor description then split the column name at the 
space.  The righthand token will be used to lookup a "converter" function in a 
dictionary of "converters", and will then apply the converter function to the 
retrieved data for that column AFTER it is retrieved from SQLite3 and BEFORE it 
is returned to the user.

That means that you could write a function called "JulianToDatetime(number)" 
and write your SQL Query (with column name parsing enabled) as

select column1 as [column1 JulianDateTime] from table;

and add a converter entry {"JulianDateTime": JulianToDateTime} and the field 
would automagically and transparently be "converted" with the JulianToDateTime 
function before it is returned to the user program.  For data going the other 
way, you can write an "adapter" function that converts the Python "datetime" 
type into a JulianDay number that occurs automagically between you passing the 
data binding to the wrapper, and the wrapper passing the data into SQLite3.

pySqlite2 (now sqlite3) Python wrapper can also parse the table declared type 
in the same way to apply automatic coverters on data retrieval (I do not know 
how it gets the declared type -- I'd have to go look in the source for 
pySqlite2.  However, once a returned column is an aggregate or an expression, 
it likely will not carry the origin declaration type anymore and only 
overloading the datatype into the column name (via as) will work.

---
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 Bart Smissaert
>Sent: Saturday, 6 January, 2018 15:54
>To: SQLite mailing list
>Subject: Re: [sqlite] sqlite3_column_decltype and max and min
>
>OK, thanks will have a look at that.
>So, how about this one then:
>
>select max(integer_date) as [max_integer_date int_date] from table1
>
>?
>
>RBS
>
>On Sat, Jan 6, 2018 at 10:35 PM, Keith Medcalf 
>wrote:
>
>>
>> Yes.  You can use "pragma table_info(tablename)" to get information
>about
>> a table, the whole table, and nothing but the one table.  Or you
>can use
>> the bunch-o-views which will give you a data dictionary to be able
>to get
>> the same data by treating all the various introspection pragma's as
>tables
>> and letting you query for specific information such as:
>>
>> select affinity from SysColumns where ObjectType='table' and
>ObjectName='myTableThatIAMInterestedIn'
>> and
>ColumnName='TheColumnNameForWhichIWantToKnowTheDeclaredAffinity';
>>
>> or perhaps even
>>
>> select ObjectType, ObjectName, from SysColumns where ColumnName = '
>> GollyJesusHereAreAllTheObjectsHavingColumnsWithThisName';
>>
>>
>> One is simply more selective that the other.  The pragma's use
>> ill-conceived field names (containing spaces and other kaiboshery)
>whereas
>> the views rename such ill-conception to something not quite so ill
>such
>> that "escaping" is not required (which is as God intended when she
>invented
>> the concept of variable naming and so forth).
>>
>> ---
>> 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 Bart Smissaert
>> >Sent: Saturday, 6 January, 2018 15:00
>> >To: SQLite mailing list
>> >Subject: Re: [sqlite] sqlite3_column_decltype and max and min
>> >
>> >Not sure how this works.
>> >I had a look at your views.
>> >Are you saying that I should get the data type (only if
>> >sqlite3_column_decltype produces null) by querying these views,
>> >passing it
>> >the column name?
>> >
>> >RBS
>> >
>> >
>> >On Sat, Jan 6, 2018 at 7:27 PM, Keith Medcalf
>
>> >wrote:
>> >
>> >>
>> >> select max(integer_date) as [max_integer_date int_date] from
>table1
>> >>
>> >>
>> >> fetch the cursor.description and split the column name at the
>> >space.  The
>> >> right part is the datatype for your handy dandy use.
>> >>
>> >> You may also be able to use the same method in the table
>> >declaration,
>> >> provided that the type carries through, but I do not remember
>> >offhand.
>> >>
>> >> create table1([integer_date] [integer int_date])
>> >>
>> >> The former is how pySqlite2 links adapters and converters to the
>> >query.
>> >>
>> >> ---
>> >> 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 

Re: [sqlite] sqlite3_column_decltype and max and min

[Bart Smissaert]

OK, thanks will have a look at that.
So, how about this one then:

select max(integer_date) as [max_integer_date int_date] from table1

?

RBS

On Sat, Jan 6, 2018 at 10:35 PM, Keith Medcalf  wrote:

>
> Yes.  You can use "pragma table_info(tablename)" to get information about
> a table, the whole table, and nothing but the one table.  Or you can use
> the bunch-o-views which will give you a data dictionary to be able to get
> the same data by treating all the various introspection pragma's as tables
> and letting you query for specific information such as:
>
> select affinity from SysColumns where ObjectType='table' and 
> ObjectName='myTableThatIAMInterestedIn'
> and ColumnName='TheColumnNameForWhichIWantToKnowTheDeclaredAffinity';
>
> or perhaps even
>
> select ObjectType, ObjectName, from SysColumns where ColumnName = '
> GollyJesusHereAreAllTheObjectsHavingColumnsWithThisName';
>
>
> One is simply more selective that the other.  The pragma's use
> ill-conceived field names (containing spaces and other kaiboshery) whereas
> the views rename such ill-conception to something not quite so ill such
> that "escaping" is not required (which is as God intended when she invented
> the concept of variable naming and so forth).
>
> ---
> 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 Bart Smissaert
> >Sent: Saturday, 6 January, 2018 15:00
> >To: SQLite mailing list
> >Subject: Re: [sqlite] sqlite3_column_decltype and max and min
> >
> >Not sure how this works.
> >I had a look at your views.
> >Are you saying that I should get the data type (only if
> >sqlite3_column_decltype produces null) by querying these views,
> >passing it
> >the column name?
> >
> >RBS
> >
> >
> >On Sat, Jan 6, 2018 at 7:27 PM, Keith Medcalf 
> >wrote:
> >
> >>
> >> select max(integer_date) as [max_integer_date int_date] from table1
> >>
> >>
> >> fetch the cursor.description and split the column name at the
> >space.  The
> >> right part is the datatype for your handy dandy use.
> >>
> >> You may also be able to use the same method in the table
> >declaration,
> >> provided that the type carries through, but I do not remember
> >offhand.
> >>
> >> create table1([integer_date] [integer int_date])
> >>
> >> The former is how pySqlite2 links adapters and converters to the
> >query.
> >>
> >> ---
> >> 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 Bart Smissaert
> >> >Sent: Saturday, 6 January, 2018 10:45
> >> >To: General Discussion of SQLite Database
> >> >Subject: [sqlite] sqlite3_column_decltype and max and min
> >> >
> >> >Is there any way with sqlite3_column_decltype (or otherwise) to
> >get
> >> >the
> >> >declared data type (as in the table create sql in SQLite_master)
> >when
> >> >it is
> >> >a simple expression such as max and min?
> >> >
> >> >for example we have a table created like this:
> >> >
> >> >create table1([integer_date] int_date)
> >> >
> >> >and we do:
> >> >
> >> >select max(integer_date) from table1
> >> >
> >> >I would then like to get returned int_date, rather than integer.
> >> >
> >> >The custom datatype int_date is needed for formatting purpose to
> >tell
> >> >the
> >> >app that receives
> >> >the data that the column holds dates as integers.
> >> >
> >> >To do this in application code is not that simple, so I hope there
> >is
> >> >some
> >> >simpler way.
> >> >
> >> >
> >> >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
> >>
> >___
> >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] sqlite3_column_decltype and max and min

[Keith Medcalf]

Yes.  You can use "pragma table_info(tablename)" to get information about a 
table, the whole table, and nothing but the one table.  Or you can use the 
bunch-o-views which will give you a data dictionary to be able to get the same 
data by treating all the various introspection pragma's as tables and letting 
you query for specific information such as:

select affinity from SysColumns where ObjectType='table' and 
ObjectName='myTableThatIAMInterestedIn' and 
ColumnName='TheColumnNameForWhichIWantToKnowTheDeclaredAffinity';

or perhaps even

select ObjectType, ObjectName, from SysColumns where ColumnName = 
'GollyJesusHereAreAllTheObjectsHavingColumnsWithThisName';


One is simply more selective that the other.  The pragma's use ill-conceived 
field names (containing spaces and other kaiboshery) whereas the views rename 
such ill-conception to something not quite so ill such that "escaping" is not 
required (which is as God intended when she invented the concept of variable 
naming and so forth).

---
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 Bart Smissaert
>Sent: Saturday, 6 January, 2018 15:00
>To: SQLite mailing list
>Subject: Re: [sqlite] sqlite3_column_decltype and max and min
>
>Not sure how this works.
>I had a look at your views.
>Are you saying that I should get the data type (only if
>sqlite3_column_decltype produces null) by querying these views,
>passing it
>the column name?
>
>RBS
>
>
>On Sat, Jan 6, 2018 at 7:27 PM, Keith Medcalf 
>wrote:
>
>>
>> select max(integer_date) as [max_integer_date int_date] from table1
>>
>>
>> fetch the cursor.description and split the column name at the
>space.  The
>> right part is the datatype for your handy dandy use.
>>
>> You may also be able to use the same method in the table
>declaration,
>> provided that the type carries through, but I do not remember
>offhand.
>>
>> create table1([integer_date] [integer int_date])
>>
>> The former is how pySqlite2 links adapters and converters to the
>query.
>>
>> ---
>> 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 Bart Smissaert
>> >Sent: Saturday, 6 January, 2018 10:45
>> >To: General Discussion of SQLite Database
>> >Subject: [sqlite] sqlite3_column_decltype and max and min
>> >
>> >Is there any way with sqlite3_column_decltype (or otherwise) to
>get
>> >the
>> >declared data type (as in the table create sql in SQLite_master)
>when
>> >it is
>> >a simple expression such as max and min?
>> >
>> >for example we have a table created like this:
>> >
>> >create table1([integer_date] int_date)
>> >
>> >and we do:
>> >
>> >select max(integer_date) from table1
>> >
>> >I would then like to get returned int_date, rather than integer.
>> >
>> >The custom datatype int_date is needed for formatting purpose to
>tell
>> >the
>> >app that receives
>> >the data that the column holds dates as integers.
>> >
>> >To do this in application code is not that simple, so I hope there
>is
>> >some
>> >simpler way.
>> >
>> >
>> >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
>>
>___
>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] Tip of Trunk does not compile under 64-bit MingW compiler

[Keith Medcalf]

I pull the fossil source on a Linux machine by fossil update trunk.
I then do the following:

cd ../bld
rm -rf *
../src/configure --enable-load-extension --enable-threadsafe 
--with-readline-lib=auto --with-pic --enable-fts5 --enable-json1
make sqlite3.c
cp ../src/src/test_intarray.c intarray.c
cp ../src/src/test_intarray.h test_intarray.h
zip -j ../interim.zip sqlite3.c sqlite3.h tsrc/shell.c tsrc/sqlite3ext.h 
tsrc/tclsqlite.c ../src/ext/misc/*.c ../src/test/speedtest1.c 
../src/ext/async/sqlite3async.* ../src/ext/userauth/*.h 
../src/ext/userauth/userauth.c intarray.c test_intarray.h ../src/test/kvtest.c 
../src/src/test_windirent.h
cd ..

The "interim.zip" is downloaded to a Windows machine where MinGW64 GCC 7.1.0 is 
attempting to compile the amalgamation with all the extensions loaded into it.

Interestingly, if I remove the conditional includes in test_windirent.h it then 
compiles mostly-sorta cleanly (just with complaints about the stat macro being 
continuously redefined) ...



---
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 Richard Hipp
>Sent: Saturday, 6 January, 2018 13:52
>To: SQLite mailing list
>Subject: Re: [sqlite] Tip of Trunk does not compile under 64-bit
>MingW compiler
>
>On 1/6/18, Keith Medcalf  wrote:
>>
>> fileio.c attempts to #include a file "test_windirent" which does
>not exist.
>> (Perhaps it does somewhere, but it is not part of amalgamation
>files nor the
>> generated shell.c).
>
>What (exact) sources are you using, and what steps are you taking to
>build?
>--
>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] sqlite3_column_decltype and max and min

[Bart Smissaert]

Not sure how this works.
I had a look at your views.
Are you saying that I should get the data type (only if
sqlite3_column_decltype produces null) by querying these views, passing it
the column name?

RBS


On Sat, Jan 6, 2018 at 7:27 PM, Keith Medcalf  wrote:

>
> select max(integer_date) as [max_integer_date int_date] from table1
>
>
> fetch the cursor.description and split the column name at the space.  The
> right part is the datatype for your handy dandy use.
>
> You may also be able to use the same method in the table declaration,
> provided that the type carries through, but I do not remember offhand.
>
> create table1([integer_date] [integer int_date])
>
> The former is how pySqlite2 links adapters and converters to the query.
>
> ---
> 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 Bart Smissaert
> >Sent: Saturday, 6 January, 2018 10:45
> >To: General Discussion of SQLite Database
> >Subject: [sqlite] sqlite3_column_decltype and max and min
> >
> >Is there any way with sqlite3_column_decltype (or otherwise) to get
> >the
> >declared data type (as in the table create sql in SQLite_master) when
> >it is
> >a simple expression such as max and min?
> >
> >for example we have a table created like this:
> >
> >create table1([integer_date] int_date)
> >
> >and we do:
> >
> >select max(integer_date) from table1
> >
> >I would then like to get returned int_date, rather than integer.
> >
> >The custom datatype int_date is needed for formatting purpose to tell
> >the
> >app that receives
> >the data that the column holds dates as integers.
> >
> >To do this in application code is not that simple, so I hope there is
> >some
> >simpler way.
> >
> >
> >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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The performance of indexed select

[Dinu]

I think storing index prefix counts would only make sense in a special kind
of 'statistical' index, where you would store count(x IS NOT NULL), sum(x),
sum(x^2) so that usual statistical functions can be computed optimally.

For a table count, I think it would make sense.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The performance of indexed select

[Keith Medcalf]

If I understand your question correctly you have not normalized your data.  The 
whole point of a RELATIONAL DATABASE is that the relationships are based ON THE 
DATA and ONLY ON THE DATA.  If you have not normalized you data to at least 
BCNF you can expect terrible performance and all sorts of access and update 
anomalies.

https://en.wikipedia.org/wiki/Database_normalization

Your schema, assuming that c is unique text should be something like (add the 
COLLATE NOCASE if it is not case sensitive):

create table t3
(
  d integer primary key,
  c text [collate nocase] unique
);
create table t2
(
  a INTEGER,
  b INTEGER,
  d INTEGER REFERENCES t3
);
create index t2d on t2 (d);
+ whatever indexes you need on a and b ...

When you add a new c, you insert it into t3 and then find out what the 
last_rowid (d) was so you can insert a and b and d in t2.  Or if the c already 
exists, then you lookup d and use a, b, d to insert into t2.

populate it with data, and run ANALYZE;

select a,b,c from t3, t2
 where t2.d = t3.d
   and (your other conditions on a b and c)

Then let the query planner decide how to compute the answer ...

---
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 Nick
>Sent: Friday, 5 January, 2018 20:32
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] The performance of indexed select
>
>I am trying to analysis the performance of indexed select.
>
>CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);
>CREATE INDEX t2c ON t2(c);
>
>I think there may be much more leaf index b-tree pages whose header
>is
>'0x0A' if the length of the content of index key 'c' is always 20-25
>bytes,
>as I notice the format of index inside sqlite consist of the index
>key and
>rowid.
>
>I can establish mapping relation between column 'c' and a new INTEGER
>column
>'d'. Then I am wondering if it is reasonable to create new index
>t2(d) to
>get a better performance, as sqlite stores INTEGER in a variable-
>length way
>which means there will be less index pages.
>
>So if it is correct that the performance of indexed select is up to
>the
>number of index pages which is fetched in getPageNormal() within the
>select?
>I think it has positive correlation but I do not know if it is the
>major
>constraint.
>
>And does sqlite have a profile tool to get call tree or execution
>time of
>each functions? All I know is VDBE_PROFILE.
>
>Thanks for any light you can shed.
>
>
>I want to profile sqlite
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] The performance of indexed select

[Simon Slavin]

On 6 Jan 2018, at 8:42pm, Dinu  wrote:

> Richard Hipp-3 wrote
>> all the parent b-tree pages must be updated
> 
> Yup, no question about it, at best it could be an opt-in. But as it is a
> design decision, I checked to make sure count() really is O(n) as Jonathan's
> question implied.

It would be possible instead just to keep a count of the total number of rows 
in each table.   The count could be kept with the table header, or in a new 
column of the table structure as the current autoincrement counter



Just like the discussion up to this point, it would be very difficult to figure 
out, for the average user, whether doing this either way would make things 
significantly faster or slower.

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

Re: [sqlite] Why doesn't SQLite optimise this subselect?

[Dinu]

Could you describe your intent in more detail?

SELECT * FROM ... GROUP BY is unstable at best, so I cannot really grasp
your intention. Which lookup record's 

If I guess right, you might want something in the lines of:

SELECT
 w.post_processing_info, 
 l.is_json 
FROM 
 webpage_contents w 
JOIN 
 (SELECT DISTINCT is_json from lookup_tables.lookups) l 
 USING (content_hash) 
WHERE 
 content_hash = 'abc' 

(this might return 0-3 rows if lookup_tables contains 3 values (0,1,and
NULL) for is_json)

which in turn I think would optimize best if written as:

SELECT DISTINCT
 w.content_hash,
 w.post_processing_info, 
 l.is_json 
FROM 
 webpage_contents w 
JOIN 
 lookup_tables.lookups l 
 USING (content_hash) 
WHERE 
 content_hash = 'abc' 




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tip of Trunk does not compile under 64-bit MingW compiler

[Richard Hipp]

On 1/6/18, Keith Medcalf  wrote:
>
> fileio.c attempts to #include a file "test_windirent" which does not exist.
> (Perhaps it does somewhere, but it is not part of amalgamation files nor the
> generated shell.c).

What (exact) sources are you using, and what steps are you taking to build?
-- 
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] The performance of indexed select

[Dinu]

Richard Hipp-3 wrote
> all the parent b-tree pages must be updated

Yup, no question about it, at best it could be an opt-in. But as it is a
design decision, I checked to make sure count() really is O(n) as Jonathan's
question implied.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Tip of Trunk does not compile under 64-bit MingW compiler

[Keith Medcalf]

fileio.c attempts to #include a file "test_windirent" which does not exist.  
(Perhaps it does somewhere, but it is not part of amalgamation files nor the 
generated shell.c).  

Snagged a copy and it does not appear to work.  Neither -m32 nor -m64 code will 
compile with 

gcc version 7.1.0 (x86_64-win32-sjlj-rev2, Built by MinGW-W64 project)

seems a bunch of stuff needed by the extensions (and the shell) are not defined 
properly.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.





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

Re: [sqlite] sqlite3_column_decltype and max and min

[Keith Medcalf]

Full Schema Tables:


-- Catalog Views using sqlite_master for SysObjects (Object Names)
-- and the various pragma_(ObjectName) tables to retrieve schema data
-- all TEXT columns in views have "collate nocase" attachmented to the output
-- columns to ensure that where conditions on retrievals are not case sensitive
-- Column Names in views defined so as to not conflict with keywords to ensure
-- quoting when using views is not required

drop view if exists SysIndexColumns;
drop view if exists SysIndexes;
drop view if exists SysColumns;
drop view if exists SysObjects;

create view if not exists SysObjects
as
select ObjectType collate nocase,
   ObjectName collate nocase
  from (
select type as ObjectType,
   name as ObjectName
  from sqlite_master
 where type in ('table', 'view', 'index')
   );

create view if not exists SysColumns
as
select ObjectType collate nocase,
   ObjectName collate nocase,
   ColumnID collate nocase,
   ColumnName collate nocase,
   Affinity collate nocase,
   IsNotNull,
   DefaultValue,
   IsPrimaryKey
from (
select ObjectType,
   ObjectName,
   cidas ColumnID,
   name   as ColumnName,
   type   as Affinity,
   "notnull"  as IsNotNull,
   dflt_value as DefaultValue,
   pk as IsPrimaryKey
  from SysObjects
  join pragma_table_info(ObjectName)
 );

create view if not exists SysIndexes
as
select ObjectType collate nocase,
   ObjectName collate nocase,
   IndexName collate nocase,
   IndexID,
   IsUniqueIndex collate nocase,
   IndexOrigin collate nocase,
   IsPartialIndex
  from (
select ObjectType,
   ObjectName,
   name as IndexName,
   seq  as IndexID,
   "unique" as IsUniqueIndex,
   origin   as IndexOrigin,
   partial  as IsPartialIndex
  from SysObjects
  join pragma_index_list(ObjectName)
   );

create view if not exists SysIndexColumns
as
select ObjectType collate nocase,
   ObjectName collate nocase,
   IndexName collate nocase,
   IndexColumnSequence,
   ColumnID,
   ColumnName collate nocase,
   IsDescendingOrder,
   Collation collate nocase,
   IsPartOfKey
  from (
select ObjectType,
   ObjectName,
   IndexName,
   seqno  as IndexColumnSequence,
   cidas ColumnID,
   name   as ColumnName,
   "desc" as IsDescendingOrder,
   coll   as Collation,
   keyas IsPartOfKey
  from SysIndexes
  join pragma_index_xinfo(IndexName)
   );




---
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: Keith Medcalf [mailto:kmedc...@dessus.com]
>Sent: Saturday, 6 January, 2018 12:40
>To: 'SQLite mailing list'
>Subject: RE: [sqlite] sqlite3_column_decltype and max and min
>
>
>SQLite version 3.22.0 2018-01-02 18:11:11
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> .head on
>sqlite> .mode col
>sqlite> create table x(a int_date);
>
>sqlite> pragma table_info(x);
>cid nametypenotnull dflt_value  pk
>--  --  --  --  --  -
>-
>0   a   int_date0   0
>
>create view if not exists SysColumns
>as
>select ObjectType collate nocase,
>   ObjectName collate nocase,
>   ColumnID collate nocase,
>   ColumnName collate nocase,
>   Affinity collate nocase,
>   IsNotNull,
>   DefaultValue,
>   IsPrimaryKey
>from (
>select ObjectType,
>   ObjectName,
>   cidas ColumnID,
>   name   as ColumnName,
>   type   as Affinity,
>   "notnull"  as IsNotNull,
>   dflt_value as DefaultValue,
>   pk as IsPrimaryKey
>  from SysObjects
>  join pragma_table_info(ObjectName)
> );
>
>sqlite> select * from SysColumns where ObjectType = 'table' and
>ObjectName = 'x';
>ObjectType  ObjectName  ColumnIDColumnName  AffinityIsNotNull
>DefaultValue  IsPrimaryKey
>--  --  --  --  --  -
>-    
>table   x   0   a   int_date0
>0
>
>
>
>---
>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 Bart Smissaert
>>Sent: Saturday, 6 January, 2018 10:45
>>To: General Discussion of SQLite 

Re: [sqlite] sqlite3_column_decltype and max and min

[Keith Medcalf]

SQLite version 3.22.0 2018-01-02 18:11:11
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .head on
sqlite> .mode col
sqlite> create table x(a int_date);

sqlite> pragma table_info(x);
cid nametypenotnull dflt_value  pk
--  --  --  --  --  --
0   a   int_date0   0

create view if not exists SysColumns
as
select ObjectType collate nocase,
   ObjectName collate nocase,
   ColumnID collate nocase,
   ColumnName collate nocase,
   Affinity collate nocase,
   IsNotNull,
   DefaultValue,
   IsPrimaryKey
from (
select ObjectType,
   ObjectName,
   cidas ColumnID,
   name   as ColumnName,
   type   as Affinity,
   "notnull"  as IsNotNull,
   dflt_value as DefaultValue,
   pk as IsPrimaryKey
  from SysObjects
  join pragma_table_info(ObjectName)
 );

sqlite> select * from SysColumns where ObjectType = 'table' and ObjectName = 
'x';
ObjectType  ObjectName  ColumnIDColumnName  AffinityIsNotNull   
DefaultValue  IsPrimaryKey
--  --  --  --  --  --  
  
table   x   0   a   int_date0   
  0



---
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 Bart Smissaert
>Sent: Saturday, 6 January, 2018 10:45
>To: General Discussion of SQLite Database
>Subject: [sqlite] sqlite3_column_decltype and max and min
>
>Is there any way with sqlite3_column_decltype (or otherwise) to get
>the
>declared data type (as in the table create sql in SQLite_master) when
>it is
>a simple expression such as max and min?
>
>for example we have a table created like this:
>
>create table1([integer_date] int_date)
>
>and we do:
>
>select max(integer_date) from table1
>
>I would then like to get returned int_date, rather than integer.
>
>The custom datatype int_date is needed for formatting purpose to tell
>the
>app that receives
>the data that the column holds dates as integers.
>
>To do this in application code is not that simple, so I hope there is
>some
>simpler way.
>
>
>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] sqlite3_column_decltype and max and min

[Keith Medcalf]

select max(integer_date) as [max_integer_date int_date] from table1


fetch the cursor.description and split the column name at the space.  The right 
part is the datatype for your handy dandy use.

You may also be able to use the same method in the table declaration, provided 
that the type carries through, but I do not remember offhand.

create table1([integer_date] [integer int_date])

The former is how pySqlite2 links adapters and converters to the query.

---
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 Bart Smissaert
>Sent: Saturday, 6 January, 2018 10:45
>To: General Discussion of SQLite Database
>Subject: [sqlite] sqlite3_column_decltype and max and min
>
>Is there any way with sqlite3_column_decltype (or otherwise) to get
>the
>declared data type (as in the table create sql in SQLite_master) when
>it is
>a simple expression such as max and min?
>
>for example we have a table created like this:
>
>create table1([integer_date] int_date)
>
>and we do:
>
>select max(integer_date) from table1
>
>I would then like to get returned int_date, rather than integer.
>
>The custom datatype int_date is needed for formatting purpose to tell
>the
>app that receives
>the data that the column holds dates as integers.
>
>To do this in application code is not that simple, so I hope there is
>some
>simpler way.
>
>
>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] sqlite3_column_decltype and max and min

[Bart Smissaert]

> Which is bound to lead to problems at some point.

Not sure it is a problem if SQLite still recognizes the custom data types
as it's own data types.
Eg, int_date will be considered integer.

RBS

On Sat, Jan 6, 2018 at 5:59 PM, Simon Slavin  wrote:

> On 6 Jan 2018, at 5:45pm, Bart Smissaert  wrote:
>
> > The custom datatype int_date is needed for formatting purpose to tell the
> > app that receives
> > the data that the column holds dates as integers.
>
> You can only get that in a documented way by parsing the CREATE TABLE
> command from sqlite_master.
>
> You’re actually trying to use your schema as if it is its own database.
> Which is bound to lead to problems at some point.  You might consider
> storing this information in another table of the database.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[x]

>>sqlite> Explain query plan select ID from Tbl where ID is null order by ID;
>>selectid|order|from|detail
>>0|0|0|SCAN TABLE Tbl

>I have the same results. ``SCAN TABLE Tbl USING COVERING INDEX ...'' is
>returned in case of not ``NOT NULL'' field.



Yeah, I would get that result as well if I had no secondary indexes on Tbl. If 
either you or Luuk add a secondary index XXX to your versions of Tbl you’ll get 
the same result I’m getting and maybe then we’ll be on the same page regarding 
ordering.



Regards



Tom





From: sqlite-users  on behalf of 
Cezary H. Noweta 
Sent: Saturday, January 6, 2018 3:09:59 PM
To: SQLite mailing list
Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

Hello,

On 2018-01-06 15:22, x wrote:
>> Because the field is not NULL in Luuk's definition and NULL values are
>> not covered by the INDEX. SQLite assumes that you know what you are
>> doing and tries to find NULL values by full scan.
>
>
>
> The ID field in my definition is also not null.

If so, then you should obtain results mentioned by Luuk:

sqlite> Explain query plan select ID from Tbl where ID is null order by ID;
selectid|order|from|detail
0|0|0|SCAN TABLE Tbl

I have the same results. ``SCAN TABLE Tbl USING COVERING INDEX ...'' is
returned in case of not ``NOT NULL'' field.

>> ``SCAN TABLE Tbl USING COVERING INDEX ...'' means that your index is
>> used. Order by does not apply as it is the same as PK. Scanning by using
>> PK results in ORDERed BY PK records. There is no need to use separate
>> INDEX for ORDER BY clause.
>
>
>
> I’m not sure what you’re saying there Cezary. I recognise that the 
> (non-existent) result set will all be NULLs and the order is therefore 
> meaningless but if the query planner recognises that fact why does it not 
> recognise that there will be no result set.


Order is meaningful but it is the same as order of a table scanning
process -- this is why ORDER BY is ``ignored'' -- not because there will
be an empty result set. If you change to ORDER BY ID*2, then you will
see that temporary index will be created. The same index is used for
scanning and ordering, so there is no need to use it twice.

> If I run explain query plan select ID from Tbl where 0; // that’s where zero
>
>
>
> I again get the result
>
>
>
> SCAN TABLE Tbl USING COVERING INDEX XXX

Again, I have the sole ``SCAN TABLE'' (without index -- in both cases:
NULLs allowed and NOT NULL). Your result is obtained as if it was ORDER
BY clause.

> However the EXPLAIN for the same query returns
>
>
>
> addropcode   p1p2p3   p4p5comment
>
> 0  Init  0  8  0  00   Start at 8
>
> 1  Goto   0  7  0 00
>
> 2  OpenRead  1  109 0  k(2,,)   00root=109 iDb=0; tID
>
> 3  Rewind   1  7  1  0  00
>
> 4  IdxRowid1  1  0 00r[1]=rowid
>
> 5  ResultRow  1  1  0 00output=r[1]
>
> 6  Next1  4  0 01
>
> 7  Halt 0  0  0  00
>
> 8  Transaction0  0  392 0  01usesStmtJournal=0
>
> 9  Goto   0  1  0 00
>
>
>
> which, if I’m reading it correctly, executes 0, 8, 9, 1 & 7 (Halt) and so 
> comes up with the empty result set in a few milliseconds. That contrasts with 
> the EXPLAINs of the IS NULL queries mentioned earlier which do an actual 
> table scan.
Indeed -- I have nearly the same: SQLite jumps directly to Halt in case
of  WHERE 0, except that I have Column instead of IdxRowid. Could you
provide your table's CREATE command? AFAIR you are using SQLite 3.21,
are not you?

-- best regards

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

Re: [sqlite] sqlite3_column_decltype and max and min

[Simon Slavin]

On 6 Jan 2018, at 5:45pm, Bart Smissaert  wrote:

> The custom datatype int_date is needed for formatting purpose to tell the
> app that receives
> the data that the column holds dates as integers.

You can only get that in a documented way by parsing the CREATE TABLE command 
from sqlite_master.

You’re actually trying to use your schema as if it is its own database.  Which 
is bound to lead to problems at some point.  You might consider storing this 
information in another table of the database.

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

[sqlite] sqlite3_column_decltype and max and min

[Bart Smissaert]

Is there any way with sqlite3_column_decltype (or otherwise) to get the
declared data type (as in the table create sql in SQLite_master) when it is
a simple expression such as max and min?

for example we have a table created like this:

create table1([integer_date] int_date)

and we do:

select max(integer_date) from table1

I would then like to get returned int_date, rather than integer.

The custom datatype int_date is needed for formatting purpose to tell the
app that receives
the data that the column holds dates as integers.

To do this in application code is not that simple, so I hope there is some
simpler way.


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

Re: [sqlite] How can i encrypt my database file ?

[Nocs ...]

Thank you for your response Ulrich




From: sqlite-users  on behalf of 
Ulrich Telle 
Sent: Saturday, January 6, 2018 5:40:38 PM
To: SQLite mailing list
Subject: Re: [sqlite] How can i encrypt my database file ?

> My first steps i made for encyption nowadays is that i read and test some 
> free encryptions around the net such as sql cipher and some others but i have 
> some doubts if this is correct cause inside the sqlite3.cpp in the section 
> that i define SQLITE_HAS_CODEC it says that is not in the public domain.

In the SQLite source code it reads

** The code to implement this API is not available in the public release
of SQLite. **

That is, the SQLite Encryption Extension (SEE), offered by D.R. Hipp,
the main SQLite developer, is not publicly available, but a commercial
license is available at a price of 2000 USD (see
https://www.hwaci.com/cgi-bin/see-step1).

> So what i want to ask is this :
>
> If i want to make my database file encrypted do i have to purchase a license 
> and be able to use encryption for sqlite3 or
> i can do it with any free program without having to pay for a license ?

Each encryption extension implementation has its own license terms:

System.Data.SQLite (http://system.data.sqlite.org), available from the
SQLite website, implements a Windows based encryption extension and is
free of cost. Most of the code is in the public domain (as SQLite
itself), but certain parts are under the Microsoft Public License (MS-PL).

SQLCipher offers Community Editions
(https://www.zetetic.net/sqlcipher/open-source/) (free of cost,
BSD-style license) and Commercial Editions
(https://www.zetetic.net/sqlcipher/buy/) (not free). It depends on your
actual requirements whether the Community Edition is good enough for
your purposes or not.

wxSQLite3 (https://github.com/utelle/wxsqlite3) includes an encryption
extension under the permissive wxWindows license (you can distribute
binaries of your application free of cost without having to disclose the
source code of your own application).

There are other free encryption extensions. You will have to check their
license terms, whether they allow commercial use or not.

Regards,

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

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

[Keith Medcalf]

>Neither will return any values since a primary key can not be NULL.

Untrue.  
The RowID alias "integer primary key" cannot be NULL  
However components of primary key(...) can be null (ie, that are not aliases 
for the RowID) and are not the PRIMARY KEY of a without rowid table.

" According to the SQL standard, PRIMARY KEY should always imply NOT NULL. 
Unfortunately, due to a bug in some early versions, this is not the case in 
SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT 
ROWID table or the column is declared NOT NULL, SQLite allows NULL values in a 
PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing 
so might break legacy applications. Hence, it has been decided to merely 
document the fact that SQLite allowing NULLs in most PRIMARY KEY columns. "

Under SQL Data Constraints at https://sqlite.org/lang_createtable.html

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



___
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] difference between 'ID IS NULL' and 'ID = NULL'

[Cezary H. Noweta]

Hello,

On 2018-01-06 15:22, x wrote:

Because the field is not NULL in Luuk's definition and NULL values are
not covered by the INDEX. SQLite assumes that you know what you are
doing and tries to find NULL values by full scan.




The ID field in my definition is also not null.


If so, then you should obtain results mentioned by Luuk:

sqlite> Explain query plan select ID from Tbl where ID is null order by ID;
selectid|order|from|detail
0|0|0|SCAN TABLE Tbl

I have the same results. ``SCAN TABLE Tbl USING COVERING INDEX ...'' is 
returned in case of not ``NOT NULL'' field.



``SCAN TABLE Tbl USING COVERING INDEX ...'' means that your index is
used. Order by does not apply as it is the same as PK. Scanning by using
PK results in ORDERed BY PK records. There is no need to use separate
INDEX for ORDER BY clause.




I’m not sure what you’re saying there Cezary. I recognise that the 
(non-existent) result set will all be NULLs and the order is therefore 
meaningless but if the query planner recognises that fact why does it not 
recognise that there will be no result set.



Order is meaningful but it is the same as order of a table scanning 
process -- this is why ORDER BY is ``ignored'' -- not because there will 
be an empty result set. If you change to ORDER BY ID*2, then you will 
see that temporary index will be created. The same index is used for 
scanning and ordering, so there is no need to use it twice.



If I run explain query plan select ID from Tbl where 0; // that’s where zero



I again get the result



SCAN TABLE Tbl USING COVERING INDEX XXX


Again, I have the sole ``SCAN TABLE'' (without index -- in both cases: 
NULLs allowed and NOT NULL). Your result is obtained as if it was ORDER 
BY clause.



However the EXPLAIN for the same query returns



addropcode   p1p2p3   p4p5comment

0  Init  0  8  0  00   Start at 8

1  Goto   0  7  0 00

2  OpenRead  1  109 0  k(2,,)   00root=109 iDb=0; tID

3  Rewind   1  7  1  0  00

4  IdxRowid1  1  0 00r[1]=rowid

5  ResultRow  1  1  0 00output=r[1]

6  Next1  4  0 01

7  Halt 0  0  0  00

8  Transaction0  0  392 0  01usesStmtJournal=0

9  Goto   0  1  0 00



which, if I’m reading it correctly, executes 0, 8, 9, 1 & 7 (Halt) and so comes 
up with the empty result set in a few milliseconds. That contrasts with the 
EXPLAINs of the IS NULL queries mentioned earlier which do an actual table scan.
Indeed -- I have nearly the same: SQLite jumps directly to Halt in case 
of  WHERE 0, except that I have Column instead of IdxRowid. Could you 
provide your table's CREATE command? AFAIR you are using SQLite 3.21, 
are not you?


-- best regards

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

Re: [sqlite] How can i encrypt my database file ?

[Ulrich Telle]

My first steps i made for encyption nowadays is that i read and test some free 
encryptions around the net such as sql cipher and some others but i have some 
doubts if this is correct cause inside the sqlite3.cpp in the section that i 
define SQLITE_HAS_CODEC it says that is not in the public domain.


In the SQLite source code it reads

** The code to implement this API is not available in the public release 
of SQLite. **


That is, the SQLite Encryption Extension (SEE), offered by D.R. Hipp, 
the main SQLite developer, is not publicly available, but a commercial 
license is available at a price of 2000 USD (see 
https://www.hwaci.com/cgi-bin/see-step1).



So what i want to ask is this :

If i want to make my database file encrypted do i have to purchase a license 
and be able to use encryption for sqlite3 or
i can do it with any free program without having to pay for a license ?


Each encryption extension implementation has its own license terms:

System.Data.SQLite (http://system.data.sqlite.org), available from the 
SQLite website, implements a Windows based encryption extension and is 
free of cost. Most of the code is in the public domain (as SQLite 
itself), but certain parts are under the Microsoft Public License (MS-PL).


SQLCipher offers Community Editions 
(https://www.zetetic.net/sqlcipher/open-source/) (free of cost, 
BSD-style license) and Commercial Editions 
(https://www.zetetic.net/sqlcipher/buy/) (not free). It depends on your 
actual requirements whether the Community Edition is good enough for 
your purposes or not.


wxSQLite3 (https://github.com/utelle/wxsqlite3) includes an encryption 
extension under the permissive wxWindows license (you can distribute 
binaries of your application free of cost without having to disclose the 
source code of your own application).


There are other free encryption extensions. You will have to check their 
license terms, whether they allow commercial use or not.


Regards,

Ulrich

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

Re: [sqlite] How can i encrypt my database file ?

[Ulrich Telle]

My first steps i made for encyption nowadays is that i read and test some free 
encryptions around the net such as sql cipher and some others but i have some 
doubts if this is correct cause inside the sqlite3.cpp in the section that i 
define SQLITE_HAS_CODEC it says that is not in the public domain.


In the SQLite source code it reads

** The code to implement this API is not available in the public release 
of SQLite. **


That is, the SQLite Encryption Extension (SEE), offered by D.R. Hipp, 
the main SQLite developer, is not publicly available, but a commercial 
license is available at a price of 2000 USD (see 
https://www.hwaci.com/cgi-bin/see-step1).



So what i want to ask is this :

If i want to make my database file encrypted do i have to purchase a license 
and be able to use encryption for sqlite3 or
i can do it with any free program without having to pay for a license ?


Each encryption extension implementation has its own license terms:

System.Data.SQLite (http://system.data.sqlite.org), available from the 
SQLite website, implements a Windows based encryption extension and is 
free of cost. Most of the code is in the public domain (as SQLite 
itself), but certain parts are under the Microsoft Public License (MS-PL).


SQLCipher offers Community Editions 
(https://www.zetetic.net/sqlcipher/open-source/) (free of cost, 
BSD-style license) and Commercial Editions 
(https://www.zetetic.net/sqlcipher/buy/) (not free). It depends on your 
actual requirements whether the Community Edition is good enough for 
your purposes or not.


wxSQLite3 (https://github.com/utelle/wxsqlite3) includes an encryption 
extension under the permissive wxWindows license (you can distribute 
binaries of your application free of cost without having to disclose the 
source code of your own application).


There are other free encryption extensions. You will have to check their 
license terms, whether they allow commercial use or not.


Regards,

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

Re: [sqlite] How can i encrypt my database file ?

[Ulrich Telle]

My first steps i made for encyption nowadays is that i read and test some free 
encryptions around the net such as sql cipher and some others but i have some 
doubts if this is correct cause inside the sqlite3.cpp in the section that i 
define SQLITE_HAS_CODEC it says that is not in the public domain.


In the SQLite source code it reads

** The code to implement this API is not available in the public release 
of SQLite. **


That is, the SQLite Encryption Extension (SEE), offered by D.R. Hipp, 
the main SQLite developer, is not publicly available, but a commercial 
license is available at a price of 2000 USD (see 
https://www.hwaci.com/cgi-bin/see-step1).



So what i want to ask is this :

If i want to make my database file encrypted do i have to purchase a license 
and be able to use encryption for sqlite3 or
i can do it with any free program without having to pay for a license ?


Each encryption extension implementation has its own license terms:

System.Data.SQLite (http://system.data.sqlite.org), available from the 
SQLite website, implements a Windows based encryption extension and is 
free of cost. Most of the code is in the public domain (as SQLite 
itself), but certain parts are under the Microsoft Public License (MS-PL).


SQLCipher offers Community Editions 
(https://www.zetetic.net/sqlcipher/open-source/) (free of cost, 
BSD-style license) and Commercial Editions 
(https://www.zetetic.net/sqlcipher/buy/) (not free). It depends on your 
actual requirements whether the Community Edition is good enough for 
your purposes or not.


wxSQLite3 (https://github.com/utelle/wxsqlite3) includes an encryption 
extension under the permissive wxWindows license (you can distribute 
binaries of your application free of cost without having to disclose the 
source code of your own application).


There are other free encryption extensions. You will have to check their 
license terms, whether they allow commercial use or not.


Regards,

Ulrich

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

[sqlite] Why doesn't SQLite optimise this subselect?

[Jonathan Moules]

Hi All,
  This is more of an academic question as I've come up with a better 
query, but I was wondering why SQLite doesn't optimise this query.


Lets say I have two tables, simplified here. One contains webpage 
contents and a unique hash of those contents (the primary key), the 
other contains a history of lookups. The hash is used as a foreign key 
between the two tables.


Table: webpage_contents
content_hash -- Primary key
post_processing_info
page_content

This table has about 10,000 rows

crawling_lookups
content_hash -- foreign key, has an index
is_json -- a flag indicating if it's JSON
is_html -- a flag indicating if it's HTML

This table has 500,000 rows

(Note: I appreciate this is bad design now and the flags should be in 
webpage content; it evolved organically).


Now, I want to query the content table post_processing_info, and join in 
the flags from the lookups at the same time. After a bit of fiddling 
around to stop duplication happening, I got this:


SELECT
w.post_processing_info,
l.is_json
FROM
webpage_contents w
JOIN
(SELECT * from lookup_tables.lookups GROUP BY content_xxhash) l
USING (content_hash)
WHERE
content_hash = 'abc'


This takes about 2 seconds to run, which is quite slow given 
content_hash is indexed in both tables and it's running from an SSD.


I appreciate there are better ways to write this (and I'm using one now, 
I was a wee bit meandering getting there though), but it I was left 
wondering - why did SQLite not optimise the subquery?


The Explain Query Plan for that is:

100SCAN TABLE crawling_lookups USING INDEX content_hash_idx
000SEARCH TABLE webpage_contents AS c USING INDEX 
sqlite_autoindex_page_contents_1 (content_hash=?)

011SCAN SUBQUERY 1 AS l

It seems to be doing the group on the subselect using the index (fast - 
about 0.1 seconds), then at step 3 doing a scan of the subquery results 
for the content_hash. This would be the slow part as that doesn't have 
an index.
I figured SQL would spot that the where clause in the outermost scope 
would also apply to the subselect and put it in there automatically so 
the group-by would only be for the things the WHERE clause affected.


Is this an optimisation opportunity? Or is my SQL so bad it was 
inevitable (more likely)?


Cheers,
Jonathan

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

Re: [sqlite] drop table if exists X

[Clemens Ladisch]

Kenneth Shuai wrote:
> conn = sqlite3.connect(db_dest)
>
> sql = """
> attach db_source.db as db_source;
> drop table if exists tbs1  # it is used to check if tbs1 already in db_dest.
> """
> Conn.execute(sql)

Okay, let's try:

  >>> conn = sqlite3.connect(db_dest)
  Traceback (most recent call last):
File "", line 1, in 
  NameError: name 'db_dest' is not defined
  >>> conn = sqlite3.connect("db_dest.db")
  >>> sql = """
  ... attach db_source.db as db_source;
  ... drop table if exists tbs1  # it is used to check if tbs1 already in 
db_dest.
  ... """
  >>> Conn.execute(sql)
  Traceback (most recent call last):
File "", line 1, in 
  NameError: name 'Conn' is not defined
  >>> conn.execute(sql)
  Traceback (most recent call last):
File "", line 1, in 
  sqlite3.OperationalError: no such column: db_source.db
  >>> sql = """
  ... attach "db_source.db" as db_source;
  ... drop table if exists tbs1  # it is used to check if tbs1 already in 
db_dest.
  ... """
  >>> conn.execute(sql)
  Traceback (most recent call last):
File "", line 1, in 
  sqlite3.Warning: You can only execute one statement at a time.
  >>> sql2 = """
  ... drop table if exists tbs1  # it is used to check if tbs1 already in 
db_dest.
  ... """
  >>> conn.execute(sql2)
  Traceback (most recent call last):
File "", line 1, in 
  sqlite3.OperationalError: unrecognized token: "#"
  >>> sql2 = """
  ... drop table if exists tbs1  -- it is used to check if tbs1 already in 
db_dest.
  ... """
  >>> conn.execute(sql2)
  >>>

> Above will delete tbs1 from db_source.db

Yes.

> it might be a bug cause wrong deletion of tbs1 from db_source

Why do you think is is wrong?

 says:
| Tables in an attached database can be referred to using the syntax
| schema-name.table-name. If the name of the table is unique across all
| attached databases and the main and temp databases, then the schema-
| name prefix is not required.


Regards,
Clemens
___
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'

[x]

Thanks Cezary but I’m none the wiser.



>Because the field is not NULL in Luuk's definition and NULL values are
>not covered by the INDEX. SQLite assumes that you know what you are
>doing and tries to find NULL values by full scan.



The ID field in my definition is also not null.



>``SCAN TABLE Tbl USING COVERING INDEX ...'' means that your index is
>used. Order by does not apply as it is the same as PK. Scanning by using
>PK results in ORDERed BY PK records. There is no need to use separate
>INDEX for ORDER BY clause.



I’m not sure what you’re saying there Cezary. I recognise that the 
(non-existent) result set will all be NULLs and the order is therefore 
meaningless but if the query planner recognises that fact why does it not 
recognise that there will be no result set.



If I run explain query plan select ID from Tbl where 0; // that’s where zero



I again get the result



SCAN TABLE Tbl USING COVERING INDEX XXX



However the EXPLAIN for the same query returns



addropcode   p1p2p3   p4p5comment

0  Init  0  8  0  00   Start at 8

1  Goto   0  7  0 00

2  OpenRead  1  109 0  k(2,,)   00root=109 iDb=0; tID

3  Rewind   1  7  1  0  00

4  IdxRowid1  1  0 00r[1]=rowid

5  ResultRow  1  1  0 00output=r[1]

6  Next1  4  0 01

7  Halt 0  0  0  00

8  Transaction0  0  392 0  01usesStmtJournal=0

9  Goto   0  1  0 00



which, if I’m reading it correctly, executes 0, 8, 9, 1 & 7 (Halt) and so comes 
up with the empty result set in a few milliseconds. That contrasts with the 
EXPLAINs of the IS NULL queries mentioned earlier which do an actual table scan.



Regards



Tom














From: sqlite-users  on behalf of 
Cezary H. Noweta 
Sent: Saturday, January 6, 2018 1:01:13 PM
To: SQLite mailing list
Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

Hello,

On 2018-01-06 13:33, x wrote:
>> sqlite> Explain query plan select ID from Tbl where ID is null order by ID;
>> selectid|order|from|detail
>> 0|0|0|SCAN TABLE Tbl
>
> Luuk, I’m also using 3.21. Presumably if you give your Tbl a secondary index 
> XXX then the explain query plan will match mine (SCAN TABLE Tbl USING 
> COVERING INDEX XXX). Your example muddies the water further though. Why is it 
> scanning an entire table when it could scan the pk?


Because the field is not NULL in Luuk's definition and NULL values are
not covered by the INDEX. SQLite assumes that you know what you are
doing and tries to find NULL values by full scan.

>>> It doesn’t even use the ID pk despite the fact it’s the requested order by??
>> That is explained by Cezary, if the explanation was unclear answer to his 
>> post...
>
> I wasn’t specifically replying to your post, I just clicked reply to the 
> latest reply. I don’t see where Cezary explains it though.

In your example (a field allowing NULLs) PK is used:

2 Null   0 1 000
3 Affinity   1 1 0 D  00
4 SeekGE 1 9 1 1  00
5   IdxGT  1 9 1 1  00
6   Column 1 0 200
7   ResultRow  2 1 000
8 Next   1 5 000
9 Halt   0 0 000

``SCAN TABLE Tbl USING COVERING INDEX ...'' means that your index is
used. Order by does not apply as it is the same as PK. Scanning by using
PK results in ORDERed BY PK records. There is no need to use separate
INDEX for ORDER BY clause.

-- best regards

Cezary H. Noweta
___
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] How can i encrypt my database file ?

[Nocs ...]

Hello and happy new year.

After long time i am using sqlite in a free and closed code application i would 
like to make my database file encrypted.

The database is created through this program and also is updated through it 
without the user to know about its internal functions.


My first steps i made for encyption nowadays is that i read and test some free 
encryptions around the net such as sql cipher and some others but i have some 
doubts if this is correct cause inside the sqlite3.cpp in the section that i 
define SQLITE_HAS_CODEC it says that is not in the public domain.


So what i want to ask is this :

If i want to make my database file encrypted do i have to purchase a license 
and be able to use encryption for sqlite3 or

i can do it with any free program without having to pay for a license ?


Can anyone unfog me a bit ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] drop table if exists X

[Kenneth Shuai]

db_source.db with tbs1, tbs2
db_dest.db with tbd1, tbd2

conn = sqlite3.connect(db_dest)

sql = """
attach db_source.db as db_source;
drop table if exists tbs1  # it is used to check if tbs1 already in db_dest.
"""
Conn.execute(sql)
Above will delete tbs1 from db_source.db
it might be a bug cause wrong deletion of tbs1 from db_source

I also tried other cases,
conn = sqlite3.connect(db_dest)
Sql = """
Attach db_source.db as db_source;
Select * from tbs1;
"""
Conn.execute(sql)
Above code works, it might also be a bug, it should be "select * from 
db_source.tbs1",
"select * from tbs1" should point to "db_dest.tbs1" instead of db_source.tbs1"


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

Re: [sqlite] The performance of indexed select

[Richard Hipp]

On 1/6/18, Dinu  wrote:
>
> I think b-trees can store the counts of descendant nodes for every node to
> solve this issue in O(log n), but I don't see anything like it in the SQLite
> format.

They can do that, but it also means that all the parent b-tree pages
must be updated whenever an entry is added or removed from a leaf
page, which increases the amount of I/O needed for operations like
INSERT, DELETE, or UPDATE.  It seemed to me that INSERT, DELETE, and
UPDATE are rather more common than SELECT count(*),  so I decided not
to keep a child count in the b-tree pages when I first designed the
current b-tree format for SQLite.  back in 2003.
-- 
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] difference between 'ID IS NULL' and 'ID = NULL'

[Cezary H. Noweta]

Hello,

On 2018-01-06 13:33, x wrote:

sqlite> Explain query plan select ID from Tbl where ID is null order by ID;
selectid|order|from|detail
0|0|0|SCAN TABLE Tbl


Luuk, I’m also using 3.21. Presumably if you give your Tbl a secondary index 
XXX then the explain query plan will match mine (SCAN TABLE Tbl USING COVERING 
INDEX XXX). Your example muddies the water further though. Why is it scanning 
an entire table when it could scan the pk?



Because the field is not NULL in Luuk's definition and NULL values are 
not covered by the INDEX. SQLite assumes that you know what you are 
doing and tries to find NULL values by full scan.



It doesn’t even use the ID pk despite the fact it’s the requested order by??

That is explained by Cezary, if the explanation was unclear answer to his 
post...


I wasn’t specifically replying to your post, I just clicked reply to the latest 
reply. I don’t see where Cezary explains it though.


In your example (a field allowing NULLs) PK is used:

2 Null   0 1 000
3 Affinity   1 1 0 D  00
4 SeekGE 1 9 1 1  00
5   IdxGT  1 9 1 1  00
6   Column 1 0 200
7   ResultRow  2 1 000
8 Next   1 5 000
9 Halt   0 0 000

``SCAN TABLE Tbl USING COVERING INDEX ...'' means that your index is 
used. Order by does not apply as it is the same as PK. Scanning by using 
PK results in ORDERed BY PK records. There is no need to use separate 
INDEX for ORDER BY clause.


-- best regards

Cezary H. Noweta
___
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'

[x]

>sqlite> Explain query plan select ID from Tbl where ID is null order by ID;
>selectid|order|from|detail
>0|0|0|SCAN TABLE Tbl

Luuk, I’m also using 3.21. Presumably if you give your Tbl a secondary index 
XXX then the explain query plan will match mine (SCAN TABLE Tbl USING COVERING 
INDEX XXX). Your example muddies the water further though. Why is it scanning 
an entire table when it could scan the pk?


>> It doesn’t even use the ID pk despite the fact it’s the requested order by??
>That is explained by Cezary, if the explanation was unclear answer to his 
>post...

I wasn’t specifically replying to your post, I just clicked reply to the latest 
reply. I don’t see where Cezary explains it though.


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

Re: [sqlite] The performance of indexed select

[Dinu]

Clemens Ladisch wrote
> For count(*), the database does not need the actual table rows.

I think this is not true, he has a point here: SELECT COUNT(*) WHERE
=? needs to examine every index key prefix (excluding at least
ROWID) that matches. This may mean reading in the whole index.

I think b-trees can store the counts of descendant nodes for every node to
solve this issue in O(log n), but I don't see anything like it in the SQLite
format.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Luuk]

42
___
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'

[Luuk]

On 06-01-18 10:44, x wrote:
> Thanks for the replies.
>
> While I’m now on board as to what a NULL is I’m still a bit puzzled by the 
> query planner.
>
> Explain query plan select ID from Tbl where ID is null order by ID;
>
> returns
>
> SCAN TABLE Tbl USING COVERING INDEX ...
>
>
I do see different things:
sqlite> .version
SQLite 3.21.0 2017-10-24 18:55:49
1a584e499906b5c87ec7d43d4abce641fdf017c42125b083109bc77c4de4alt2
sqlite> .schema tbl
CREATE TABLE tbl (id integer primary key not null);
sqlite> select count(*) from tbl;
count(*)
8388608
sqlite> Explain query plan select ID from Tbl where ID is null order by ID;
selectid|order|from|detail
0|0|0|SCAN TABLE Tbl
sqlite>

> It doesn’t even use the ID pk despite the fact it’s the requested order by??
That is explained by Cezary, if the explanation was unclear answer to
his post...
___
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'

[Eric]

On Sat, 6 Jan 2018 09:53:46 +0100, Luuk  wrote:
> On 06-01-18 00:49, Simon Slavin wrote:
>>
>> To expand on this, in SQL NULL has a special meaning.  
> 
> This should read:
> NULL has a special meaning.
> and not:
> in SQL NULL has a special meaning.
> 
> Because the use of NULL is not 'reserverd' for SQL, and in SQL it is not
> more special than in any other environment.

I don't understand what you mean. NULL has a special
meaning in SQL (Structured Query Language), and that is what we are
talking about.

Eric
-- 
ms fnd in a lbry
___
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'

[x]

Thanks for the replies.

While I’m now on board as to what a NULL is I’m still a bit puzzled by the 
query planner.

Explain query plan select ID from Tbl where ID is null order by ID;

returns

SCAN TABLE Tbl USING COVERING INDEX ...

It doesn’t even use the ID pk despite the fact it’s the requested order by??


> What has you checking an integer primary key for null anyway?

David, to test something I needed a query that returned 0 rows and I modified 
the query I was already working on rather than type out a new select.

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

Re: [sqlite] The performance of indexed select

[Clemens Ladisch]

Nick wrote:
> Or in another word, if a TEXT column has similar meaning with an INTEGER
> column in my applications,(such as use userID instead of userName, still the
> way that the data works in my head:) ) is it recommended to use INTEGER one
> in order to get a less index pages?

Yes; an index on a smaller column needs less space, so it's faster to load
from disk.

But you should always measure how much difference it actually makes.

>> For instance, once SQLite has found the right entry in the index it might
>> need to look up that entry in the table to retrieve values which are not
>> in the index.
>
> I understand the execution process you said. And in my opinion, sqlite
> should fetch pages when looking up the entry both in the index and then in
> the table. But I only found pages with '0x0A' and '0x02' when
> getPageNormal() is called during the time running select SQL. Could you give
> me any advises to find the code when sqlite fetching the '0x0D' pages?

For count(*), the database does not need the actual table rows.
You'd have to run a query that reads some other column:

  SELECT sum(a) FROM t2 WHERE d = xx;


Regards,
Clemens
___
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'

[Luuk]

On 06-01-18 00:49, Simon Slavin wrote:
>
> To expand on this, in SQL NULL has a special meaning.  

This should read:
NULL has a special meaning.
and not:
in SQL NULL has a special meaning.

Because the use of NULL is not 'reserverd' for SQL, and in SQL it is not
more special than in any other environment.

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