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

2018-01-06 Thread 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).

[sqlite] drop table if exists X

2018-01-06 Thread 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

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

2018-01-06 Thread 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

[sqlite] How can i encrypt my database file ?

2018-01-06 Thread 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

Re: [sqlite] The performance of indexed select

2018-01-06 Thread 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

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

2018-01-06 Thread 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

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

2018-01-06 Thread 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

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

2018-01-06 Thread 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

Re: [sqlite] sqlite3_column_decltype and max and min

2018-01-06 Thread 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

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

2018-01-06 Thread 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

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

2018-01-06 Thread 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

[sqlite] sqlite3_column_decltype and max and min

2018-01-06 Thread 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

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

2018-01-06 Thread 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

Re: [sqlite] drop table if exists X

2018-01-06 Thread 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

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

2018-01-06 Thread 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

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

2018-01-06 Thread 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

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

2018-01-06 Thread 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

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

2018-01-06 Thread 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

Re: [sqlite] sqlite3_column_decltype and max and min

2018-01-06 Thread 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

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

2018-01-06 Thread 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

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

2018-01-06 Thread 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

Re: [sqlite] The performance of indexed select

2018-01-06 Thread 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

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

2018-01-06 Thread 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

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

2018-01-06 Thread 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'

2018-01-06 Thread 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

Re: [sqlite] The performance of indexed select

2018-01-06 Thread 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

Re: [sqlite] sqlite3_column_decltype and max and min

2018-01-06 Thread 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

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

2018-01-06 Thread 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

Re: [sqlite] sqlite3_column_decltype and max and min

2018-01-06 Thread 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

Re: [sqlite] sqlite3_column_decltype and max and min

2018-01-06 Thread 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 name

Re: [sqlite] The performance of indexed select

2018-01-06 Thread 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/

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

2018-01-06 Thread 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

Re: [sqlite] sqlite3_column_decltype and max and min

2018-01-06 Thread 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

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

2018-01-06 Thread 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

Re: [sqlite] sqlite3_column_decltype and max and min

2018-01-06 Thread 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

Re: [sqlite] sqlite3_column_decltype and max and min

2018-01-06 Thread 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

Re: [sqlite] sqlite3_column_decltype and max and min

2018-01-06 Thread 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

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

2018-01-06 Thread 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

Re: [sqlite] The performance of indexed select

2018-01-06 Thread 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 >

Re: [sqlite] The performance of indexed select

2018-01-06 Thread 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

Re: [sqlite] The performance of indexed select

2018-01-06 Thread 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:

Re: [sqlite] sqlite3_column_decltype and max and min

2018-01-06 Thread 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

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

2018-01-06 Thread 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

Re: [sqlite] sqlite3_column_decltype and max and min

2018-01-06 Thread 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