Re: [sqlite] compressed sqlite3 database file?

[Wout Mertens]

I know of two options: The proprietary
https://sqlite.org/zipvfs/doc/trunk/www/readme.wiki and this extension that
you have to call on strings yourself:
https://github.com/siara-cc/Shox96_Sqlite_UDF

Furthermore, some filesystems allow transparent compression, like ntfs,
bcachefs, zfs and btrfs. I have a 13GB DB that takes up 850MB on btrfs.

Wout.


On Wed, Apr 10, 2019 at 7:39 AM Peng Yu  wrote:

> I have some TSV table in .gz format of only 278MB. But the
> corresponding sqlite3 database exceeds 1.58GB (without any index). Is
> there a way to make the database file of a size comparable (at least
> not over 5 times) to the original TSV table in the .gz file? Thanks.
>
> --
> Regards,
> Peng
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] compressed sqlite3 database file?

[Peng Yu]

I have some TSV table in .gz format of only 278MB. But the
corresponding sqlite3 database exceeds 1.58GB (without any index). Is
there a way to make the database file of a size comparable (at least
not over 5 times) to the original TSV table in the .gz file? Thanks.

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

Re: [sqlite] How to use ORDER BY on FTS5 table ?

[Nik Jain]

Anybody ?

On Mon, Apr 8, 2019 at 9:03 PM Nik Jain  wrote:

> ok. I investigated further, and it seems my problem is something else
> entirely! A SCAN is being performed on a fts5 table. I am not sure but I
> think that means no index.
>
> Query plan:
> sqlite>  explain query plan  select * from productsfts p where
> p.attributes match '50'limit 6;
> QUERY PLAN
> `--SCAN TABLE productsfts AS p VIRTUAL TABLE INDEX 1:
> Run Time: real 0.000 user 0.48 sys 0.35
>
>
> Schema:
> CREATE VIRTUAL TABLE productsfts using fts5(
>   attributes ,
>   lastprice,
>   id ,
>   categoryid,
>   brandid
> )
> /* productsfts(attributes,lastprice,id,categoryid,brandid) */;
>
> Is this normal ? When A order by clause is added to above query. It always
> shows a "Using temp b-tree for order by"
>
> On Mon, Apr 8, 2019 at 1:44 PM Wout Mertens 
> wrote:
>
>> You need to create an index on both columns at once or the indexes can't
>> be
>> used. Try "EXPLAIN QUERY PLAN SELECT ...your query here" to see if indexes
>> are being used.
>>
>> Wout.
>>
>> On Sun., Apr. 7, 2019, 9:41 a.m. Nik Jain  wrote:
>>
>> >  Have a fts5 table with 2 indexed columns. Where the idea is to match by
>> > one col and sort using the other one. Something like :
>> >
>> > "select id from fts where col1 match '50' order by price "
>> >
>> > This is slow. 0.07 seconds. Removing the order by clause -  0.001
>> seconds.
>> > How do I fix this ? I have a feeling I am using this fts table in an
>> > incorrect way. One way is to run 2 queries. First on the fts table, to
>> > return ids. Second on the regular table with the order by clause. "
>> select
>> > * from normaltable where  id in (Ids) order by price " . This approach
>> is
>> > fast. But the id list could be large sometimes.
>> > Any other way ?
>> > Thanks
>> >
>> > PS: This is my second attempt at mailing lists. Not sure if this one
>> will
>> > go through.
>> > ___
>> > 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] Built in and enabled Virtual Table extensions in SQLite CLI?

[John McMahon]

Hi,

I have not used extensions before. I understand that some are included 
in the amalgamation source file and that some of these are enabled by 
default. So, which ones are built-in and which of those are enabled in 
the standard downloadable Win32 SQLite CLI?


If an extension is built-in and enabled, what do I need to do to use it. 
The instructions seem to be for the case where an extension is built as 
an external library (.dll) to be loaded by eg. .load ./csv where csv 
would be csv.dll in the current directory. If the csv extension was 
built-in, would I still need to load it to activate it?




John

--
Regards
   John McMahon
  li...@jspect.fastmail.fm


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

Re: [sqlite] How to use ORDER BY on FTS5 table ?

[Jens Alfke]

> On Apr 5, 2019, at 2:59 PM, Nik Jain  wrote:
> 
> One way is to run 2 queries. First on the fts table, to
> return ids. Second on the regular table with the order by clause. " select
> * from normaltable where  id in (Ids) order by price " . This approach is
> fast. But the id list could be large sometimes.
> Any other way ?

What you’re describing is basically a join. You can use SQL’s JOIN syntax to 
describe this as a single query, and SQLite will take care of processing it in 
a scalable manner:
select * from normaltable join fts on fts.id 
=normaltable.id  where fts.col1 match 
’50’ order by normaltable.price;
[syntax may be a bit off, I’m typing in a hurry!]

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

Re: [sqlite] Option to control implicit casting

[James K. Lowden]

On Mon, 8 Apr 2019 23:08:18 -0400
Joshua Thomas Wise  wrote:

> I propose there should be a compile-time option to disable all
> implicit casting done within the SQL virtual machine. 

You can use SQLite in a "strict" way: write a CHECK constraint for
every numerical column.  

Just don't do that for tables that are loaded by the .import comand.
As I reported here not long ago, .import rejects numeric literals.
Apparently, the value is inserted as a string and rejected, instead of
being converted to a number first.  

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

Re: [sqlite] Option to control implicit casting

[R Smith]

On 2019/04/09 5:08 AM, Joshua Thomas Wise wrote:

SQLite3 uses manifest typing, which is great and provides a ton of flexibility. 
However, due to implicit casting rules, many operations can accidentally result 
in a different value than what was desired. If programmers don’t guard against 
every possible cause of implicit casting, many error situations are swallowed 
and instead can result in data corruption. I propose there should be a 
compile-time option to disable all implicit casting done within the SQL virtual 
machine. The option could cause all type-incompatible operations to return 
NULL, or it could cause these operations to throw hard errors. Either approach 
would be similarly useful.

Here are some examples of how implicit casting can lead to surprising results:

1. If invoking SUM() would cause integer overflow, a hard error is returned. 
The same things happens with ABS(). However, if integer overflow occurs when 
using the + operator, a REAL value is returned instead.

2. Many built-in string functions will automatically cast BLOBs to TEXTs, but 
those could contain embedded nuls, leading to undefined behavior.

3. Declaring a column with INTEGER affinity does not actually force its values 
to be integers. An integer that is out of range could be stored as a REAL 
value, unexpectedly changing the behavior of functions such as SUM() and ABS().



TLDR: We feel your pain, but it's probably not happening...

We have been forever lobbying for a "STRICT" mode in SQLite (search for 
"strict" in the archives, the hits are legion), something that would 
error out if you shove a string into an Integer declared column, not 
accept double-quotes around non-identifiers, force aggregate queries to 
use aggregate functions, start with Foreign_keys enabled... the list 
goes on. Actually the list doesn't go on that much, there's just a few 
things that break the SQL.


The reason this is regarded as broken is that in most cases in SQL 
(SQLite included) the people here, myself included, would advise you 
along the lines of "Do not try to think for the SQL engine, give the 
query and let it do its thing.' - which is the greatest advice in the 
spirit of RDBMS - but in SQLite's case this specific advice cannot 
always be given, because now in some cases it is up to you to 
specifically HAVE to think for the engine and not trust that it will do 
the thing you intended. You have to write extra code (or CHECK 
constraints) to just check that things are what they seem or have they 
morphed into something else? (like in #1 above).


I'm not sure your No.2 is a valid case - if you make a BLOB column and 
then try to read strings or string-functions from it, you should be 
punished for it. The problem is more if you declare a column as TEXT and 
then shove a BLOB in it, that should error out.


Don't get me wrong, we love the duck typing, we love the flexibility, 
and I for one love to be able to do quick data manipulations with the 
relaxed SQL on offer - but sometimes we are doing mission critical 
applications, or embedded things, these systems have no room for error 
and it's hard to always trust SQLite or design a bunch of code to 
second-guess the SQL in SQLite specifically. A STRICT mode would go such 
a long way to make SQLite 100% formidable.


Then again, it's already the most used DB in the World - it's not like 
they're battling for market share because of this little snag, and the 
problems that STRICT mode would solve are rather well-known and 
documented. As Dominique pointed out, the test harness is one of the 
finest in any industry and it would probably need a LOT of additions to 
test such a mode, so it's not a minor effort to implement.


One can still wish... :)

Cheers!
Ryan


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

Re: [sqlite] Error in docs

[Dominique Devienne]

On Tue, Apr 9, 2019 at 9:41 AM Richard Hipp  wrote:

> On 4/9/19, Dominique Devienne  wrote:
> >>
> >> It defines the table and view:
> >> CREATE TABLE t1(a INT, b TEXT, c REAL);
> >> CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;
> >>
> >> It then states "The affinity of the v1.x column will be the same as the
> >> affinity of t1.b (INTEGER), since v1.x maps directly into t1.b.".
> >>
> >> However column t1.b is TEXT, not INTEGER.
> >>
> >
> > How to do determine that exactly? Via SQL? Debugging?
>
> If you compile with -DSQLITE_DEBUG, then there is a new undocumented
> function affinity().  After inserting a row into table t1:
>

Cool! Thanks for the heads up. But why debug only? It's as useful as
typeof() IMHO.
Obviously affinity-related-code is compiled-in in non-debug build, so I
don't see any
down side of unconditionally adding a function that if not explicitly used
as zero runtime
overhead, no? While SQLITE_DEBUG itself likely adds overhead.

Would be really useful for troubleshooting and teaching to always have it.
My $0.02. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Option to control implicit casting

[Dominique Devienne]

On Tue, Apr 9, 2019 at 5:08 AM Joshua Thomas Wise <
joshuathomasw...@gmail.com> wrote:

> SQLite3 uses manifest typing, which is great and provides a ton of
> flexibility. However, due to implicit casting rules, many operations can
> accidentally result in a different value than what was desired. If
> programmers don’t guard against every possible cause of implicit casting,
> many error situations are swallowed and instead can result in data
> corruption. I propose there should be a compile-time option to disable all
> implicit casting done within the SQL virtual machine. The option could
> cause all type-incompatible operations to return NULL, or it could cause
> these operations to throw hard errors. Either approach would be similarly
> useful.
>
> Here are some examples of how implicit casting can lead to surprising
> results:
>
> 1. If invoking SUM() would cause integer overflow, a hard error is
> returned. The same things happens with ABS(). However, if integer overflow
> occurs when using the + operator, a REAL value is returned instead.
>
> 2. Many built-in string functions will automatically cast BLOBs to TEXTs,
> but those could contain embedded nuls, leading to undefined behavior.
>
> 3. Declaring a column with INTEGER affinity does not actually force its
> values to be integers. An integer that is out of range could be stored as a
> REAL value, unexpectedly changing the behavior of functions such as SUM()
> and ABS().
>

While I can see some benefits, I'm afraid this is unlikely to happen,
because the testing effort from Richard/Dan/Joe would be large,
to retain their 100% line/branch coverage, with benefits not outweighing
the costs from their POV.

I'm the first one to request or lobby for changes, like optional "usual"
strong enforcement
of types/affinity when storing values into tables, similar to your #3. So
it's not that I don't see
some value in it. But experience tells me this will be an uphill battle I'm
afraid. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error in docs

[Richard Hipp]

On 4/9/19, Dominique Devienne  wrote:
>>
>> It defines the table and view:
>> CREATE TABLE t1(a INT, b TEXT, c REAL);
>> CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;
>>
>> It then states "The affinity of the v1.x column will be the same as the
>> affinity of t1.b (INTEGER), since v1.x maps directly into t1.b.".
>>
>> However column t1.b is TEXT, not INTEGER.
>>
>
> How to do determine that exactly? Via SQL? Debugging?

If you compile with -DSQLITE_DEBUG, then there is a new undocumented
function affinity().  After inserting a row into table t1:

INSERT INTO t1 values(1,2,3);

You can do:

SELECT affinity(x), affinity(y), affinity(z) FROM v1;

And get the answer:

'text','none','none'

-- 
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] Error in docs

[Dominique Devienne]

On Mon, Apr 8, 2019 at 7:58 PM Jim Dossey  wrote:

> I think I found an error in the documentation here:
> https://www.sqlite.org/datatype3.html#column_affinity_for_views_and_subqueries
> <
> https://www.sqlite.org/datatype3.html#column_affinity_for_views_and_subqueries
> >
>
> It defines the table and view:
> CREATE TABLE t1(a INT, b TEXT, c REAL);
> CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;
>
> It then states "The affinity of the v1.x column will be the same as the
> affinity of t1.b (INTEGER), since v1.x maps directly into t1.b.".
>
> However column t1.b is TEXT, not INTEGER.
>

How to do determine that exactly? Via SQL? Debugging?

There's no way to get "directly" the affinity of a column, that I know of,
you can only infer it from side-effects I believe. Thus my curiosity. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users