Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

[Kees Nuyt]

On Wed, 10 Apr 2019 11:05:59 +0100, you wrote:

>Wednesday, April 10, 2019, 10:28:55 AM, Luuk  wrote:
>
>> On 10-4-2019 10:28, Kees Nuyt wrote:
>
>>> sqlite> select * from pragma_function_list;
>
>> sqlite> select * from pragma_function_list;
>> Error: no such table: pragma_function_list
>
> From the help page (https://www.sqlite.org/pragma.html#pragma_function_list)
> this option is only available "...if SQLite is built using the
> -DSQLITE_INTROSPECTION_PRAGMAS compile-time option.". My (standard) 
> copy of 3.27.2 doesn't support this either, so I'm guessing Keet must
> have a custom-built version.


My mistake, I have that option in all my builds, I forgot about
it.


-- 
Regards,
Kees Nuyt
___
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

[John McMahon]

On 11/04/2019 00:28, Joshua Thomas Wise wrote:

This is not enough. Because of implicit casting, an integer (a precise value) 
could be passed through a series of operations that outputs an integer, 
satisfying the check constraint, but it still could’ve been converted to a 
floating point (imprecise value) at some intermediate step due to integer 
overflow, potentially resulting in an incorrect answer. There’s currently no 
way to guarantee that a value will always yield precise results in SQLite3.

Here’s an example:
CREATE TABLE squares (
x INTEGER NOT NULL DEFAULT 0 CHECK (typeof(x) = 'integer'),
y INTEGER NOT NULL DEFAULT 0 CHECK (typeof(y) = 'integer')
);
INSERT INTO squares VALUES (1 << 40, 1 << 40);
SELECT x * y & ~1 AS even_numbered_area FROM squares;


Suggestion: "Don't Do That", use database purely as a storage medium.

If the Integer values you want to store are greater than the 64bit 
values accepted by SQLite then store them as BLOBs.


If the mathematical manipulations you wish to apply in your queries are 
beyond the scope of the built-in functions, then just return the stored 
values to your external programming environment and manipulate them there.


You would seem to be working in an edge case environment, in which case 
it is your responsibility to make the adjustments.




In many cases, it’s better for the above SELECT statement to return an error or 
NULL, but currently it gives an incorrect answer. Checking its type won’t help 
either, because it does indeed return an integer.



On Apr 9, 2019, at 2:06 PM, James K. Lowden  wrote:

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


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



--
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] Built in and enabled Virtual Table extensions in SQLite CLI?

[John McMahon]

On 10/04/2019 18:28, Kees Nuyt wrote:

On Wed, 10 Apr 2019 13:17:23 +1000, John wrote:


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?


By this above, I meant the pre-compiled CLI.



sqlite> .mode column
sqlite> .header on
sqlite> .width 28 8
sqlite> select * from pragma_function_list;


Thank you Kees, that didn't work (as noted by Luuk, Graham and Shawn) 
but it got me looking in the Pragma document and this did:

sqlite> pragma compile_options:
compile_options

COMPILER=gcc-5.2.0
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS5
ENABLE_JSON1
ENABLE_RTREE
ENABLE_STMTVTAB
ENABLE_UNKNOWN_SQL_FUNCTION
THREADSAFE=0
sqlite>

also this:
sqlite> select * from pragma_compile_options;
compile_options

COMPILER=gcc-5.2.0
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
...
same thing, different method.




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?


I don't think so.



--
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] compressed sqlite3 database file?

[Simon Slavin]

On 10 Apr 2019, at 8:51pm, Peng Yu  wrote:

> What do you recommend for Mac? Thanks.

I do not have a good answer ready for you.  These days storage is so cheap that 
buying an external 2TB drive is cheaper than spending a lot of time doing 
clever programming.  But if you want to investigate this ...

Is this a read-only database ?  One which you don't need to change ? The 
encryption addon I mentioned in my previous post might be ideal for a 
compressed read-only database.

How close are you to running out of space ?  Are you just trying to use as 
little space as possible or do you have a specific amount of space in mind 
(e.g. fitting the database on particular storage device).

Do you actually need to use SQLite for this ?  As I understand it it's a 
collection of strings, with a number for each string.  Do you need to be able 
to look up the strings, or look up the number and find the string ?  If space 
is so important to you it might be faster to produce hash codes and look up the 
hash code instead.  Hash codes are tiny, and lead to a tiny fixed file format.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Keith Medcalf]

On Wednesday, 10 April, 2019 14:21, Peter da Silva  wrote:

>On Wed, Apr 10, 2019 at 3:12 PM Keith Medcalf  wrote:

>> Why would anyone fart about with added complication and the
>> concomittant increased unreliability when storage is so damn cheap?

>Embedded systems and mobile devices.

You mean "play things", for the most part.

By their very definitions "play things" do not require reliability and as such 
the added complication and inherent increase in unreliability due to that 
increased complexity is of no real effect.

I am used to dealing with "important shit".  That means that if it stops 
working, even for a minute, it might entail costs of millions of dollars and 
perhaps a few deaths or cripplings as well.  

There is a very great difference between the "streaming media crap" not working 
for a bit and you have to (heavens forbid) read a book, or the mail server 
going down for a day or two, which are really nothing more than minor 
inconveniences by comparison.  The streaming box screws up?  Throw it out and 
buy another.  In the "play things" world adding complexity to increase 
unreliability and save a few pennies is often a reasonable trade-off.  After 
all, nothing of any real significance will be lost -- it is merely a bit of 
inconvenience to suffer through with no real lasting impact.

On the other hand if the consequence of failure is certain death of 10 people, 
then I would much rather be spending more money on reliable hardware to 
maintain the designed level of reliability than to save a few shekels by 
tossing "compression" into the mix thereby reducing reliability and increasing 
the probability (through an increase in unpredictable failure modes) of those 
10 people dying.  I think if you were one of those 10 people with your life at 
risk you would see things the same way.

>But of course those probably don't apply here. :)

It is all a matter of perspective.  Lets imaging that the problem with the 
747MAX was not that the new control system was designed by an idiot and that 
insufficient training on the detection and correction of the "we know this is 
going to be a problem" so intruduced were not the issue.  Lets say instead that 
the files were merely a bit too big for the hard drives they decided to use.  
They have the option of (a) spending an additional $100 and getting larger 
storage and not changing the failure scenario's at all; or, (b) not spending 
any money and instead adding yet another layer of software to perform 
"compression" instead (thus changing the failure scenario's because now you 
have a whole whack of new failure modes).

The "Play Things" people consider that the crash of the airliner and the loss 
of equipment and all life aboard is merely an "inconvenience" and will choose 
option (b) because hey, the software always works, right?  The "Important Shit" 
people will consider that the *possible* increase in risk of loss of equipment 
and life due to the addition of yet more complexity cannot be tolerated and 
will chose option (a) because it is far more cost effective than the analysis 
that will be required to *prove* option (a) has not increased the risk.

I simply happen to fall into the "Important Shit" category of people by 
default.  I am somewhat risk-adverse as they say.  If the risk associated with 
a thing is significant, then spend as much as required to reduce that risk to 
an acceptable level.  If the risk associated with a thing is negligible, then 
get the cheapest shit available and when it "breaks" throw it out and get 
another.

This does not mean that the "Play Things" outlook is incorrect.  It merely 
depends on the garden in which you are playing and in to which category the 
product falls.

---
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] compressed sqlite3 database file?

[Wout Mertens]

On Wed, Apr 10, 2019 at 9:51 PM Peng Yu  wrote:

> What do you recommend for Mac? Thanks.
>

Nothing. Apple doesn't want you to have compression, because then you would
take longer to buy a new Mac. The afsctool compression is a laughable hack
that only works on read-only data. Writing to the file uncompresses it.
It's a great way to recover 7GB from the 13GB XCode install, mind you.

You can install ZFS, but that might break any time macOS is upgraded, or
you can install docker and do all your development in docker images, but
that's of course slower.

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

Re: [sqlite] compressed sqlite3 database file?

[Warren Young]

On Apr 10, 2019, at 2:12 PM, Keith Medcalf  wrote:
> 
> It is far cheaper and much more reliable to just buy some file storage space. 
>  

If you’re going to buy some more storage, you should put ZFS on it then, too. :)

You get a whole lot more from ZFS than just transparent compression.

You actually have to go out of your way to disable compression on ZFS, since in 
the most common cases, you do want it, since compression + I/O is generally 
faster than raw I/O.  The disk space savings is almost a freebie, in that 
context.

We get this partly because computers often have unused CPU capacity, and ZFS 
defaults to one of the fastest lossless compression algorithms, lz4:

   https://facebook.github.io/zstd/

That page is for another up-and-coming alternative for ZFS, zstd, which claims 
to give gzip-like compression levels at near-lz4 speeds.  From their own 
numbers, though, it looks like the default will remain lz4.

About the only time you want to disable compression on ZFS is when you know the 
pool’s contents are compressed already, as with most digital media, so ZFS 
would end up just burning a lot of CPU to no good end otherwise.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[James K. Lowden]

On Wed, 10 Apr 2019 15:20:32 -0500
Peter da Silva  wrote:

> > Why would anyone fart about with added complication and the
> > concomittant increased unreliability when storage is so damn cheap?
> 
> Embedded systems and mobile devices.
> 
> But of course those probably don't apply here. :)

Are you saying my MB Pro isn't portable?  If not, I have an Osborne
Executive to sell you.  

I was going to tell the OP that the 1990s called, and they want their
STAC floppies back.  

--jkl

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

Re: [sqlite] compressed sqlite3 database file?

[Peter da Silva]

On Wed, Apr 10, 2019 at 3:12 PM Keith Medcalf  wrote:

> Why would anyone fart about with added complication and the concomittant
> increased unreliability when storage is so damn cheap?
>

Embedded systems and mobile devices.

But of course those probably don't apply here. :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Keith Medcalf]

It is far cheaper and much more reliable to just buy some file storage space.  

Last I looked 4 TB of NVMe is about $1,000.00.

This is not the old days when a 5 1/2 inch full height 40 MEGABYTE drive cost a 
bundle.  Geez, I remember when I got a bunch of CDC Wren IV 300 MB drives at 
the whopping 50% discounted price of $6,000.00 each.  Compsurfing those dozen 
drives shook the building for two weeks!

Nowadays one can procure 100 TB of RAID-6 for about the price of one of those 
Wren IV drives...

Why would anyone fart about with added complication and the concomittant 
increased unreliability when storage is so damn cheap?


---
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 Warren Young
>Sent: Wednesday, 10 April, 2019 09:05
>To: SQLite mailing list
>Subject: Re: [sqlite] compressed sqlite3 database file?
>
>On Apr 9, 2019, at 11:39 PM, Peng Yu  wrote:
>>
>> 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?
>
>Transparent file compression is a feature of several filesystems:
>NTFS, ZFS, Btrfs, and more:
>
>
>https://en.wikipedia.org/wiki/Comparison_of_file_systems#Allocation_a
>nd_layout_policies
>
>If you can enable this feature on your existing system or switch to
>one of the filesystems that do support it, you don’t need a non-
>default SQLite configuration.
>___
>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] compressed sqlite3 database file?

[Peng Yu]

What do you recommend for Mac? Thanks.

On 4/10/19, Simon Slavin  wrote:
> On 10 Apr 2019, at 7:08pm, Peng Yu  wrote:
>
>> https://softwarerecs.stackexchange.com/questions/45010/transparent-file-compression-apps-for-macos
>>
>> I work on Mac. Would this be worthwhile to try?
>
> I do not recommend it.  Two reasons:
>
> A) If you copy the file you get an uncompressed result.  This is because
> most functions do not know the file is compressed.  They try to read the
> file block by block and the operating system automatically hands them
> uncompressed blocks.  So if you copy your file to a Flash Drive or back it
> up, you end up with the big version.
>
> B) Macs are moving, or have already moved, away from HFS+ to APFS.  Although
> HFS+ compressed files seem to work without problems in APFS Apple has not
> announced support for this feature.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] compressed sqlite3 database file?

[Simon Slavin]

On 10 Apr 2019, at 7:08pm, Peng Yu  wrote:

> https://softwarerecs.stackexchange.com/questions/45010/transparent-file-compression-apps-for-macos
> 
> I work on Mac. Would this be worthwhile to try?

I do not recommend it.  Two reasons:

A) If you copy the file you get an uncompressed result.  This is because most 
functions do not know the file is compressed.  They try to read the file block 
by block and the operating system automatically hands them uncompressed blocks. 
 So if you copy your file to a Flash Drive or back it up, you end up with the 
big version.

B) Macs are moving, or have already moved, away from HFS+ to APFS.  Although 
HFS+ compressed files seem to work without problems in APFS Apple has not 
announced support for this feature.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Warren Young]

On Apr 10, 2019, at 12:08 PM, Peng Yu  wrote:
> 
> https://softwarerecs.stackexchange.com/questions/45010/transparent-file-compression-apps-for-macos
> 
> I work on Mac. Would this be worthwhile to try?

The first link didn’t work here because it didn’t like the APFS drive I tried 
it on.  (Symptom: “Expecting f_type of 17, 23 or 24. f_type is 26.”)

I then tried the so-called “Github mirror”, which is no such thing: it’s 
considerably advanced beyond the last version published at the first link, and 
one of the improvements is APFS awareness.

Using that improved version, simple tests then worked, but then attempting to 
use it on a SQLite DB file uncompressed it and left it uncompressed.  I believe 
this is because this OS feature relies on the old resource fork feature, which 
means it only works with apps using the Apple-proprietary programming 
interfaces, not POSIX interfaces, as SQLite does.

> Does the transparent
> compression work at the file system level or at the directory level?

Neither: it works at the file level.

You can point afsctool at a directory and it will compress the files in that 
directory, but if you then drop another file in that directory, it won’t 
automatically be compressed.

The tool will also skip over files it considers “already compressed” unless you 
give it the -L flag, so giving it a directory name isn’t guaranteed to result 
in all files in that directory being compressed.

> Would
> it have a slight chance to corrupt the existent files on the disk (e.g.,
> power outrage during compression)?

Between the resource forks issue and the fact that we’re having to use a third 
party tool to enable it, I wouldn’t put much trust in this feature.

If you want to put your trust in a third-party OS add-on, O3X is worth much 
more of your attention than afsctool:

https://openzfsonosx.org/

If you don’t have a spare disk to feed to it, you can create a pool using raw 
disk images:

   
https://openzfsonosx.org/wiki/FAQ#Q.29_Can_I_set_up_a_test_pool_using_files_instead_of_disks.3F

ZFS’s abilities to a) add vdevs to a pool; and b) replace smaller vdevs with 
larger ones together mean you can safely set the initial pool size to just 
barely larger than the initial DB file plus any ancillary space needed.  (WAL, 
slack pages between VACUUM calls, etc.)  You can then grow the pool 
occasionally to keep ahead of the growth of the DB without sacrificing too much 
on filesystem overhead.  It’d be easy to write an on-demand 20% pool size 
growth script, for instance; it’d be maybe half a dozen lines of Bash.

Lest you go off on an unfortunate tangent from this idea, note that the 
“compressed disk image” feature of Disk Utility won’t help you here.  Those are 
read-only.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.28.0 beta 1

[Simon Slavin]

Rather than  sqlite3_stmt_isexplain(S)  would it be possible to define a  
sqlite3_stmt_type(S)  function instead ?  This would return integers equivalent 
to

SQLITE_STMT_ERROR = -1
SQLITE_STMT_OTHER = 0
SQLITE_STMT_EXPLAIN = 1

and in future other values as they become useful ?  You could add easy ways to 
identify PRAGMAs, SELECT, INSERT, etc. as needed in future.  Until these are 
added, everything but EXPLAIN evaluates as SQLITE_STMT_OTHER, unless the 
function cannot identify the statement type in which case you get 
SQLITE_STMT_ERROR.

An alternative set of codes which might be preferred are those already in use 
as authorizer action codes:



Extend these by adding SQLITE_EXPLAIN (or SQLITE_EXPLAIN and 
SQLITE_EXPLAIN_QUERY_PLAN), and use these codes instead of starting a new set 
of codes.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite bug report

[richard parkins]

Summary
Alter table rename  to  fails if the database contains a view 
which selects from a nonexistent table.
Seen on sqlite 3.27.2.
Script to reproduce it:
create view view_1 as select * from table_1;
create table table_2 (col_1 text, col_2 text);
alter table table_2 rename to table_3;

The problem seems to be a side-effect of the change introduced in version 
3.25.0 to ALTER TABLE which made it update references to the renamed table in 
triggers and views.
renameTableFunc walks through the schema looking for views and triggers which 
reference the renamed table. It overwrites the schema entries unconditionally 
even if it has not changed them. I don't know why you do this unnecessary work, 
but I assume there is a reason. However it then calls sqlite3SelectPrep for 
each entry. This is definitely wrong, since sqlite3SelectPrep apparently tries 
to populate the view and fails in sqlite3LocateTable at line 106955. Views are 
a bit like the box containing Schrödinger's cat: you don't know what is inside 
until you look, so sqlite shouldn't look until the user explicitly asks it do 
so.
You could argue that users shouldn't create a view that selects from a 
nonexistent table, but sqlite currently allows it (and also allows you to drop 
the table). Banning such views would break a lot of existing scripts: many of 
mine modify a table in ways which ALTER TABLE can't do by creating a new table, 
dropping the original one, and renaming the new table as the old one. This 
paradigm is already broken by the change in the semantics of ALTER TABLE, but I 
can reinstate the old behaviour with a PRAGMA. However not allowing views on 
nonexistent tables would break it more thoroughly.

Richard Parkins
http://www.zen224037.zen.co.uk

rparkins999/sqliteman

| 
| 
| 
|  |  |

 |

 |
| 
|  | 
rparkins999/sqliteman

http://sqliteman.com/. Contribute to rparkins999/sqliteman development by 
creating an account on GitHub.
 |

 |

 |



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

Re: [sqlite] compressed sqlite3 database file?

[Peng Yu]

https://softwarerecs.stackexchange.com/questions/45010/transparent-file-compression-apps-for-macos

I work on Mac. Would this be worthwhile to try?  Does the transparent
compression work at the file system level or at the directory level? Would
it have a slight chance to corrupt the existent files on the disk (e.g.,
power outrage during compression)?

On Wed, Apr 10, 2019 at 11:02 AM Wout Mertens 
wrote:

> As I said in my previous email, I have a 13GB database that transparently
> compresses to 800MB. Not sure if it got through, didn't get replies to my
> last two emails.
>
> Wout.
>
> On Wed., Apr. 10, 2019, 5:04 p.m. Warren Young  wrote:
>
> > On Apr 9, 2019, at 11:39 PM, Peng Yu  wrote:
> > >
> > > 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?
> >
> > Transparent file compression is a feature of several filesystems: NTFS,
> > ZFS, Btrfs, and more:
> >
> >
> >
> https://en.wikipedia.org/wiki/Comparison_of_file_systems#Allocation_and_layout_policies
> >
> > If you can enable this feature on your existing system or switch to one
> of
> > the filesystems that do support it, you don’t need a non-default SQLite
> > configuration.
> > ___
> > 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
>
-- 
Regards,
Peng
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Peter da Silva]

A database trades off space for performance and functionality. It is
expected that a database with indexes that you can randomly access is going
to take more space than the raw data, let alone a compressed version of the
raw data.

On Wed, Apr 10, 2019 at 12: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

Re: [sqlite] Option to control implicit casting

[Warren Young]

On Apr 8, 2019, at 9:08 PM, Joshua Thomas Wise  
wrote:
> 
> there should be a compile-time option to disable all implicit casting done 
> within the SQL virtual machine.

That’d be nice, especially when using SQLite with a strongly- and 
statically-typed programming language and a statically-typed DB interface (e.g. 
ORM), so that the correct mapping is always fixed and known at compile time, so 
the extra flexibility buys that programmer nothing.

Keep in mind that SQLite was born as a Tcl extension, so it shares its 
extremely loose concepts of data typing.

> The option could cause all type-incompatible operations to return NULL

That would overload the already-overloaded keyword NULL with still another 
meaning.  To SQLite, NULL already means:

1. No value given on input.

2. No corresponding value in the “B” table on LEFT JOIN with table “A”.

Then on top of that, there are the libraries that try to map the language’s 
notions of NULL/nullptr/nil, etc. to and from SQL.

Let’s not add still another meaning.

> it could cause these operations to throw hard errors

That’s a much better plan.

> 1. If invoking SUM() would cause integer overflow

…then you’re using the wrong data type.

If you have reason to believe that an idealized implementation of SUM() could 
produce  values of 19 digits or larger given your data, you should not be using 
INTEGER, period.  You might want to be using the recently-announced DECIMAL 
extension instead:

   https://chiselapp.com/user/lifepillar/repository/sqlite3decimal/index

> 2. Many built-in string functions will automatically cast BLOBs to TEXTs

You’ll have my sympathy if you can show a reasonable path where a TEXT column 
can have some of its values accidentally or implicitly converted to BLOB.

As far as I’m aware, your stated problem only occurs when you purposefully do 
that.  You have no sympathy from me if you shoot a hole in your foot while 
*aiming* at it. :)

> 3. Declaring a column with INTEGER affinity does not actually force its 
> values to be integers.

That’s only a problem if you don’t give SQLite integers as input, and if it you 
don’t, this falls under one of the oldest laws of computing: “If you lie to the 
computer, it usually finds a way to get its revenge.”
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Wout Mertens]

As I said in my previous email, I have a 13GB database that transparently
compresses to 800MB. Not sure if it got through, didn't get replies to my
last two emails.

Wout.

On Wed., Apr. 10, 2019, 5:04 p.m. Warren Young  wrote:

> On Apr 9, 2019, at 11:39 PM, Peng Yu  wrote:
> >
> > 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?
>
> Transparent file compression is a feature of several filesystems: NTFS,
> ZFS, Btrfs, and more:
>
>
> https://en.wikipedia.org/wiki/Comparison_of_file_systems#Allocation_and_layout_policies
>
> If you can enable this feature on your existing system or switch to one of
> the filesystems that do support it, you don’t need a non-default SQLite
> configuration.
> ___
> 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] Option to control implicit casting

[Keith Medcalf]

On Wednesday, 10 April, 2019 09:06. Joshua Thomas Wise 
 wrote:

>When you need a feature-packed embedded SQL database, there aren’t
>many other options to reach for. I’m not suggesting that SQLite3 has
>a responsibility to satisfy every need just because it has beat out
>most other competition, but I’m in a situation where either I write
>every elementary integer operation as a custom function and convince
>my entire team to ONLY use those functions, or I write my own fork of
>SQLite3. Of course, option 1 will be much easier, but it’s also very
>messy and awkward. It would just be nice if SQLite3, being a
>relational database that seems to take database corruption very
>seriously, could provide some guarantees about the precision of
>answers given for basic arithmetic.

Well, taking a quick peek at the source code it appears that the internal 
OPCodes for Add/Subtract/Multiply/Divide/Remainder are handled starting at line 
1538 in vdbe.c.  If the arguments are BOTH integer AND if an error occurs 
(which would be an integer overflow/underflow) then the implementation jumps 
ahead to code that converts the arguments to floating-point and does the 
operations in floating-point.  Thereafter of course the type of the contents of 
the register is changed and different code paths are followed.

It would seem to me to be a relatively simple matter to make those "goto 
fp_math" goto a routine that sets the result register to NULL (for example) or 
otherwise causes a crash-and-burn.  Of course, if one wanted to make this 
change applicable to all users of the library everywhere it would require 
extensive testing before such a change could be released.  However, for use in 
your OWN application, I do not think the changes would be that significant -- 
probably a lot less time than the 30 minutes I spent reading the code.

Now if you wanted the "no type escalation" to be handled by a pragma so you can 
turn it on and off, that will require even more time and to make such changes 
to the actual released code base would require even more extensive testing.  
And thinking about how one would want it to work.


>> On Apr 10, 2019, at 10:55 AM, Keith Medcalf 
>wrote:
>>
>>
>> On Wednesday, 10 April, 2019 08:28, Joshua Thomas Wise
>mailto:joshuathomasw...@gmail.com>>
>wrote:
>>
>>> This is not enough. Because of implicit casting, an integer (a
>>> precise value) could be passed through a series of operations that
>>> outputs an integer, satisfying the check constraint, but it still
>>> could’ve been converted to a floating point (imprecise value) at
>some
>>> intermediate step due to integer overflow, potentially resulting
>in
>>> an incorrect answer. There’s currently no way to guarantee that a
>>> value will always yield precise results in SQLite3.
>>
>>> Here’s an example:
>>> CREATE TABLE squares (
>>> x INTEGER NOT NULL DEFAULT 0 CHECK (typeof(x) = 'integer'),
>>> y INTEGER NOT NULL DEFAULT 0 CHECK (typeof(y) = 'integer')
>>> );
>>> INSERT INTO squares VALUES (1 << 40, 1 << 40);
>>> SELECT x * y & ~1 AS even_numbered_area FROM squares;
>>
>>> In many cases, it’s better for the above SELECT statement to
>return
>>> an error or NULL, but currently it gives an incorrect answer.
>>> Checking its type won’t help either, because it does indeed return
>an
>>> integer.
>>
>> The answers are correct.  SELECT x * y from squares returns the
>correct result, as does the bitwise and with -2.
>>
>> If you wish to "be able to multiply two 64-bit integers" entirely
>in the integer domain with the overflow doing something other than
>"convert the arguments into floating point and do the operation in
>floating point" then you are completely free to write a function that
>does precisely and exactly what you think you want it to do and
>behave and do precisely that which you wish.  It is so simple to do
>that during the time taken to read your message and compose this
>response I could have written pretty much all the basic operators
>written this way, compiled, tested, and moved the code into
>production.
>>
>> Some languages when multiplying a trail of 47 64-bit integers might
>return a 3000 bit integer.  Others might explode.  Some might cause
>the universe to reach heat death.  SQLite3 attempts to do what you
>told it to do by converting the overflowing operands into floating
>point, and then using floating point.
>>
>> If you do not like that you are free to either (a) write your own
>multiplication function that works the way you think it ought to
>work, and use that or (b) use something else that is more akin to
>your liking.
>>
>> I do not like Java.  It is long winded, requires a 400" monitor to
>be able to see anything at all, and is just about the stupedest hunk
>of crap that I have ever seen in my entire life.  Since I have no
>wish to "fix" it, I just use something more apropos.  Mutatis
>mutandis JavaCripple / Rust / Go / Varnish / JollyGoodCrap / C# /
>Cflat  and most of the other newfangled hogwash -- I will 

[sqlite] SQLite version 3.28.0 beta 1

[Richard Hipp]

The current "Prerelease Snapshot" at https://sqlite.org/download.html
is considered a beta. We expect bug fixes only from now until the
official release of version 3.28.0.

If you can, please download the beta and try it out in your
application(s).  Report any problems and/or performance regressions
you encounter to this mailing list, or directly to me. Please make
your reports with all haste as once testing starts we will move
quickly to a release.

A draft for the 3.28.0 release notes can be seen at
https://www.sqlite.org/draft/releaselog/current.html

Visit https://www.sqlite.org/checklists/328/index to monitor the
3.28.0 release checklist. we will probably start marking off items
within a day or two.  The release will occur when the checklist goes
all green.

-- 
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] Option to control implicit casting

[Joshua Thomas Wise]

When you need a feature-packed embedded SQL database, there aren’t many other 
options to reach for. I’m not suggesting that SQLite3 has a responsibility to 
satisfy every need just because it has beat out most other competition, but I’m 
in a situation where either I write every elementary integer operation as a 
custom function and convince my entire team to ONLY use those functions, or I 
write my own fork of SQLite3. Of course, option 1 will be much easier, but it’s 
also very messy and awkward. It would just be nice if SQLite3, being a 
relational database that seems to take database corruption very seriously, 
could provide some guarantees about the precision of answers given for basic 
arithmetic.

> On Apr 10, 2019, at 10:55 AM, Keith Medcalf  wrote:
> 
> 
> On Wednesday, 10 April, 2019 08:28, Joshua Thomas Wise 
> mailto:joshuathomasw...@gmail.com>> wrote:
> 
>> This is not enough. Because of implicit casting, an integer (a
>> precise value) could be passed through a series of operations that
>> outputs an integer, satisfying the check constraint, but it still
>> could’ve been converted to a floating point (imprecise value) at some
>> intermediate step due to integer overflow, potentially resulting in
>> an incorrect answer. There’s currently no way to guarantee that a
>> value will always yield precise results in SQLite3.
> 
>> Here’s an example:
>> CREATE TABLE squares (
>>  x INTEGER NOT NULL DEFAULT 0 CHECK (typeof(x) = 'integer'),
>>  y INTEGER NOT NULL DEFAULT 0 CHECK (typeof(y) = 'integer')
>> );
>> INSERT INTO squares VALUES (1 << 40, 1 << 40);
>> SELECT x * y & ~1 AS even_numbered_area FROM squares;
> 
>> In many cases, it’s better for the above SELECT statement to return
>> an error or NULL, but currently it gives an incorrect answer.
>> Checking its type won’t help either, because it does indeed return an
>> integer.
> 
> The answers are correct.  SELECT x * y from squares returns the correct 
> result, as does the bitwise and with -2.
> 
> If you wish to "be able to multiply two 64-bit integers" entirely in the 
> integer domain with the overflow doing something other than "convert the 
> arguments into floating point and do the operation in floating point" then 
> you are completely free to write a function that does precisely and exactly 
> what you think you want it to do and behave and do precisely that which you 
> wish.  It is so simple to do that during the time taken to read your message 
> and compose this response I could have written pretty much all the basic 
> operators written this way, compiled, tested, and moved the code into 
> production.
> 
> Some languages when multiplying a trail of 47 64-bit integers might return a 
> 3000 bit integer.  Others might explode.  Some might cause the universe to 
> reach heat death.  SQLite3 attempts to do what you told it to do by 
> converting the overflowing operands into floating point, and then using 
> floating point.  
> 
> If you do not like that you are free to either (a) write your own 
> multiplication function that works the way you think it ought to work, and 
> use that or (b) use something else that is more akin to your liking.
> 
> I do not like Java.  It is long winded, requires a 400" monitor to be able to 
> see anything at all, and is just about the stupedest hunk of crap that I have 
> ever seen in my entire life.  Since I have no wish to "fix" it, I just use 
> something more apropos.  Mutatis mutandis JavaCripple / Rust / Go / Varnish / 
> JollyGoodCrap / C# / Cflat  and most of the other newfangled hogwash -- I 
> will stick to C, PL/1, COBOL, FORTRAN and RPG thank-yee-very-much!
> 
> 
> 
> 
> ___
> 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] compressed sqlite3 database file?

[Warren Young]

On Apr 9, 2019, at 11:39 PM, Peng Yu  wrote:
> 
> 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?

Transparent file compression is a feature of several filesystems: NTFS, ZFS, 
Btrfs, and more:

   
https://en.wikipedia.org/wiki/Comparison_of_file_systems#Allocation_and_layout_policies

If you can enable this feature on your existing system or switch to one of the 
filesystems that do support it, you don’t need a non-default SQLite 
configuration.
___
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

[Keith Medcalf]

On Wednesday, 10 April, 2019 08:28, Joshua Thomas Wise 
 wrote:

>This is not enough. Because of implicit casting, an integer (a
>precise value) could be passed through a series of operations that
>outputs an integer, satisfying the check constraint, but it still
>could’ve been converted to a floating point (imprecise value) at some
>intermediate step due to integer overflow, potentially resulting in
>an incorrect answer. There’s currently no way to guarantee that a
>value will always yield precise results in SQLite3.

>Here’s an example:
>CREATE TABLE squares (
>   x INTEGER NOT NULL DEFAULT 0 CHECK (typeof(x) = 'integer'),
>   y INTEGER NOT NULL DEFAULT 0 CHECK (typeof(y) = 'integer')
>);
>INSERT INTO squares VALUES (1 << 40, 1 << 40);
>SELECT x * y & ~1 AS even_numbered_area FROM squares;

>In many cases, it’s better for the above SELECT statement to return
>an error or NULL, but currently it gives an incorrect answer.
>Checking its type won’t help either, because it does indeed return an
>integer.

The answers are correct.  SELECT x * y from squares returns the correct result, 
as does the bitwise and with -2.

If you wish to "be able to multiply two 64-bit integers" entirely in the 
integer domain with the overflow doing something other than "convert the 
arguments into floating point and do the operation in floating point" then you 
are completely free to write a function that does precisely and exactly what 
you think you want it to do and behave and do precisely that which you wish.  
It is so simple to do that during the time taken to read your message and 
compose this response I could have written pretty much all the basic operators 
written this way, compiled, tested, and moved the code into production.

Some languages when multiplying a trail of 47 64-bit integers might return a 
3000 bit integer.  Others might explode.  Some might cause the universe to 
reach heat death.  SQLite3 attempts to do what you told it to do by converting 
the overflowing operands into floating point, and then using floating point.  

If you do not like that you are free to either (a) write your own 
multiplication function that works the way you think it ought to work, and use 
that or (b) use something else that is more akin to your liking.

I do not like Java.  It is long winded, requires a 400" monitor to be able to 
see anything at all, and is just about the stupedest hunk of crap that I have 
ever seen in my entire life.  Since I have no wish to "fix" it, I just use 
something more apropos.  Mutatis mutandis JavaCripple / Rust / Go / Varnish / 
JollyGoodCrap / C# / Cflat  and most of the other newfangled hogwash -- I will 
stick to C, PL/1, COBOL, FORTRAN and RPG thank-yee-very-much!




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

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

[Keith Medcalf]

On Wednesday, 10 April, 2019 08:12, Peng Yu  wrote:

>On 4/10/19, Keith Medcalf  wrote:

>> The first column is of strings ...

>> Do you mean a single string as in "KerfufledAllaHasbalah"
>> Or a "bunch of strings with some implied delimiter" such as
>> "Kerfufled/Alla/Hasballah" where "/" is the separator between
>> strings?
>>
>> If the latter, the data needs to be normalized.

>There is only one string in each row, not many strings separated by
>some separator. A string can include white space characters. In other
>words, a string can be a word or multiple words separated by white
>spaces.

>Would it be possible to create indexes at a subcolumn level? I think
>if indexes can be created at the word level (or prefix of words, as I
>frequently search for things like a noun and its plural form
>together), then the search can be faster.

There would be a number of ways that you can do this, including using the 
built-in FTS module.  The "string" column is your free-form text.  Be aware, 
however, that this will create and maintain a number of additional indexes on 
the data and will consume *more* disk space to hold the data and indexes, 
however search time will likely decrease from your current 23 seconds to about 
say 23 milliseconds for an appropriately phrased query yeilding similar 
results.  (ie, the search will be 1000x faster but the space consumed may be 
only two or three times as much).

You could also build your own "word" list by parsing the strings and linking 
them to the record to which they pertain, then use appropriately phrased 
queries to perform lightening fast searches.  This is basically what FTS does, 
only you are doing it yourself.


---
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] Option to control implicit casting

[Joshua Thomas Wise]

This is not enough. Because of implicit casting, an integer (a precise value) 
could be passed through a series of operations that outputs an integer, 
satisfying the check constraint, but it still could’ve been converted to a 
floating point (imprecise value) at some intermediate step due to integer 
overflow, potentially resulting in an incorrect answer. There’s currently no 
way to guarantee that a value will always yield precise results in SQLite3.

Here’s an example:
CREATE TABLE squares (
x INTEGER NOT NULL DEFAULT 0 CHECK (typeof(x) = 'integer'),
y INTEGER NOT NULL DEFAULT 0 CHECK (typeof(y) = 'integer')
);
INSERT INTO squares VALUES (1 << 40, 1 << 40);
SELECT x * y & ~1 AS even_numbered_area FROM squares;

In many cases, it’s better for the above SELECT statement to return an error or 
NULL, but currently it gives an incorrect answer. Checking its type won’t help 
either, because it does indeed return an integer.


> On Apr 9, 2019, at 2:06 PM, James K. Lowden  wrote:
> 
> 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

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

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

[Hick Gunter]

"a string can be ... multiple words separated by white spaces"
"...indexes at subcolumn level..."
"... search for a noun and ist plural form together..."

Yes, you do need normalization. You need to divulge "what you are trying to do" 
instead of asking "how to emulate a non-relational implementation".

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Peng Yu
Gesendet: Mittwoch, 10. April 2019 16:12
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

On 4/10/19, Keith Medcalf  wrote:
>
> The first column is of strings ...
>
> Do you mean a single string as in "KerfufledAllaHasbalah"
> Or a "bunch of strings with some implied delimiter" such as
> "Kerfufled/Alla/Hasballah" where "/" is the separator between strings?
>
> If the latter, the data needs to be normalized.

There is only one string in each row, not many strings separated by some 
separator. A string can include white space characters. In other words, a 
string can be a word or multiple words separated by white spaces.

Would it be possible to create indexes at a subcolumn level? I think if indexes 
can be created at the word level (or prefix of words, as I frequently search 
for things like a noun and its plural form together), then the search can be 
faster.

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


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

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

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

[Graham Holden]

Wednesday, April 10, 2019, 2:03:24 PM, Peng Yu  wrote:

> Given the much larger disk space required, for an occasional search of
> the data, it seems that it makes no sense to use sqlite3 if disk space
> is a major concern.

Whether it "makes sense" to use SQLite or not, probably only you can
decide. If the "zgrep" solution works, and is "fast enough", then
there may not be any point in using SQLite. However, possible
advantages (depending on your "use cases") include being able to
ENFORCE the uniqueness of the string column, and -- depending on the
types of query involved -- benefiting from an index which in many
cases _should_ speed up the search.

And, in response to a couple of your latter emails (where you give
_some_ details about your data): you will have a much better chance
of people on this list being able to help you if you give a more
complete description of the problem: include some sample data from
your TSV and the types of searches you want to do. People can then
give much more informed help.



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

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

[Peng Yu]

On 4/10/19, Keith Medcalf  wrote:
>
> The first column is of strings ...
>
> Do you mean a single string as in "KerfufledAllaHasbalah"
> Or a "bunch of strings with some implied delimiter" such as
> "Kerfufled/Alla/Hasballah" where "/" is the separator between strings?
>
> If the latter, the data needs to be normalized.

There is only one string in each row, not many strings separated by
some separator. A string can include white space characters. In other
words, a string can be a word or multiple words separated by white
spaces.

Would it be possible to create indexes at a subcolumn level? I think
if indexes can be created at the word level (or prefix of words, as I
frequently search for things like a noun and its plural form
together), then the search can be faster.

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

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

[Keith Medcalf]

the second column is of integers ...

Do you mean the second column in AN integer or that it is a bunch-o-integers 
separated by some separator?

If the latter, normalization is required.


---
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 Peng Yu
>Sent: Wednesday, 10 April, 2019 08:01
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?
>
>I don't know specifically what you refer to as data normalization. My
>guess is something like this. But it is irrelevant to my case.
>
>https://www.studytonight.com/dbms/database-normalization.php
>
>For my specific TSV file, it has about 50 million rows and just two
>columns. The first column is of strings and the second column is of
>integers. All the strings in the first column are unique (some
>strings
>may be substrings of other strings though).
>
>On 4/10/19, Hick Gunter  wrote:
>> I have the distinct impression that you are attempting to convert a
>flat
>> file into a naked table and pretending that the result is a
>(relational)
>> database.
>>
>> Please rethink your approach. There is a design process called
>> "normalization" that needs to be done first. This will identify
>"entities"
>> (with "attributes") and "relations" that will greatly reduce data
>> duplication found in flat files.
>
>--
>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

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

[Keith Medcalf]

The first column is of strings ...

Do you mean a single string as in "KerfufledAllaHasbalah"
Or a "bunch of strings with some implied delimiter" such as 
"Kerfufled/Alla/Hasballah" where "/" is the separator between strings?

If the latter, the data needs to be normalized.

---
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 Peng Yu
>Sent: Wednesday, 10 April, 2019 08:01
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?
>
>I don't know specifically what you refer to as data normalization. My
>guess is something like this. But it is irrelevant to my case.
>
>https://www.studytonight.com/dbms/database-normalization.php
>
>For my specific TSV file, it has about 50 million rows and just two
>columns. The first column is of strings and the second column is of
>integers. All the strings in the first column are unique (some
>strings
>may be substrings of other strings though).
>
>On 4/10/19, Hick Gunter  wrote:
>> I have the distinct impression that you are attempting to convert a
>flat
>> file into a naked table and pretending that the result is a
>(relational)
>> database.
>>
>> Please rethink your approach. There is a design process called
>> "normalization" that needs to be done first. This will identify
>"entities"
>> (with "attributes") and "relations" that will greatly reduce data
>> duplication found in flat files.
>
>--
>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

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

[Peng Yu]

I don't know specifically what you refer to as data normalization. My
guess is something like this. But it is irrelevant to my case.

https://www.studytonight.com/dbms/database-normalization.php

For my specific TSV file, it has about 50 million rows and just two
columns. The first column is of strings and the second column is of
integers. All the strings in the first column are unique (some strings
may be substrings of other strings though).

On 4/10/19, Hick Gunter  wrote:
> I have the distinct impression that you are attempting to convert a flat
> file into a naked table and pretending that the result is a (relational)
> database.
>
> Please rethink your approach. There is a design process called
> "normalization" that needs to be done first. This will identify "entities"
> (with "attributes") and "relations" that will greatly reduce data
> duplication found in flat files.

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

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

[Hick Gunter]

I have the distinct impression that you are attempting to convert a flat file 
into a naked table and pretending that the result is a (relational) database.

Please rethink your approach. There is a design process called "normalization" 
that needs to be done first. This will identify "entities" (with "attributes") 
and "relations" that will greatly reduce data duplication found in flat files.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Peng Yu
Gesendet: Mittwoch, 10. April 2019 15:03
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

Here is the runtime of using "select where like" (with %) on a .sq3 file.

real0m23.105s
user0m12.765s
sys 0m2.882s
data from a
Here is the runtime of zgrep (roughly equivalent, except that zgrep search for 
the whole line).

real0m33.814s
user0m40.927s
sys 0m0.660s

Given the much larger disk space required, for an occasional search of the 
data, it seems that it makes no sense to use sqlite3 if disk space is a major 
concern.

On 4/10/19, Peng Yu  wrote:
>> > Your comparison is unfair. A .gz file is COMPRESSED by gzip and not
>> > in any way human readable without uncompressing it first.
>
> But to store the file (and occasionally search the data), I would
> prefer 278MB instead of 1.5GB. With a .gz file, at least I can zcat
> and zgrep.
>
>> How big is the result if you compress the sqlite file through gzip?
>
> 620MB
>
>> Or how big is the TSV file you get if you expand your .gz file ?
>
> 1.17GB
>


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


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

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

Re: [sqlite] Does sqlite3 support alter table add primary key?

[Keith Medcalf]

CREATE UNIQUE INDEX  on H1 (...the unique columns...).

Primary key is (except for the INTEGER PRIMARY KEY in a rowid table) and in 
WITHOUT ROWID tables where it is the key of the b-tree, just semantic sugar for 
a UNIQUE index ...

---
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 Peng Yu
>Sent: Wednesday, 10 April, 2019 06:11
>To: SQLite mailing list
>Subject: [sqlite] Does sqlite3 support alter table add primary key?
>
>Hi,
>
>I got the following error. Does sqlite3 support alter table add
>primary key?
>
>sqlite> alter table mytab add primary key (h1);
>Error: near "primary": syntax error
>
>https://www.tutorialspoint.com/sqlite/sqlite_primary_key.htm
>
>--
>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

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

[Peng Yu]

Here is the runtime of using "select where like" (with %) on a .sq3 file.

real0m23.105s
user0m12.765s
sys 0m2.882s

Here is the runtime of zgrep (roughly equivalent, except that zgrep
search for the whole line).

real0m33.814s
user0m40.927s
sys 0m0.660s

Given the much larger disk space required, for an occasional search of
the data, it seems that it makes no sense to use sqlite3 if disk space
is a major concern.

On 4/10/19, Peng Yu  wrote:
>> > Your comparison is unfair. A .gz file is COMPRESSED by gzip and not in
>> > any way human readable without uncompressing it first.
>
> But to store the file (and occasionally search the data), I would
> prefer 278MB instead of 1.5GB. With a .gz file, at least I can zcat
> and zgrep.
>
>> How big is the result if you compress the sqlite file through gzip?
>
> 620MB
>
>> Or how big is the TSV file you get if you expand your .gz file ?
>
> 1.17GB
>


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

Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

[Keith Medcalf]

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


On Tuesday, 9 April, 2019 21:17, John McMahon  wrote:

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

You are using the term "enabled" in this paragraph to mean:
 (a) compiled into object code
 (b) the object code loaded into the process
 (c) the "entry point(s)" being linked into the current connection

For builtin extensions these steps are all achieved by:
 (a) including the source code in the library surrounded by an #ifdef 

 (b) #define  when you compile the library (thus compiling the 
source to object code and loading it into the process)
 (c) including special instructions surrounded by the same #ifdef  
which causes the "entry point(s)" to be linked into the current connection when 
each connection initializes.

>So, which ones are built-in and which of those are enabled
>in the standard downloadable Win32 SQLite CLI?

The ones which have SQLITE_ENABLE_ or SQLITE_DISABLE_ are 
generally available and documented to ENABLE and DISABLE, at complile time, the 
extensions and features that are contained within SQLite3.  Examination of the 
makefile might be revealing (in other words, I have no clue.  I build my own 
containing what I want so what is default I have no clue).

>If an extension is built-in and enabled, what do I need to do to use
>it.

Nothing as all of the necessary steps (compile / load / link into the library) 
have been done.

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

Well, if you have a hunk of code that operates as a function in SQLite3 then 
first you must compile it.  You can do this either by sticking it in the 
sqlite3.c source code or compiling it separately.  Compiling the source code to 
object code is one of the prerequisite steps.  How you choose to perform that 
step is up to you.  If you have compiled, lets say, the csv module as a 
separate dll then you must load it by using the appropriate "load the module 
into this process" for your OS.  This is the "load_extension" call (for which a 
human typeable interface is provided in the CLI by the .load command).

Since merely loading the code into the process does nothing other than load the 
code into the process, the "load_extension" call also, after loading the 
module, attempts to call its "go connect yourself" function.

Whether the csv extension was compiled as a separate object module (shared 
library) or is by some other method already "loaded" into the process, you 
still have to call the "go connect yourself" code.  This is done by the 
"load_extension" API.


You can of course compile as many extensions as you like and include their code 
in the sqlite3 engine itself, and then use the SQLITE_EXTRA_INIT hook to run 
some code that runs the init routines of all the modules you have added so that 
they are all available all the time.  I do this to add several hundred 
extensions of various types into SQLite3 so they are always available all the 
time.

The introspection pragma's are available to examine which functcions, modules, 
virtual tables, etc., are currently "available".

apsw compile options 3.27.2-r1
Row(sqlite_version=u'3.28.0')
Row(sqlite_source_id=u'2019-04-02 00:56:20 
f7ba2daf91c96ba16958188d6084a0ff129310d348932de493d326f1d246alt2')
Row(pragma_name=u'application_id')
Row(application_id=0)
Row(pragma_name=u'auto_vacuum')
Row(auto_vacuum=0)
Row(pragma_name=u'automatic_index')
Row(automatic_index=1)
Row(pragma_name=u'busy_timeout')
Row(timeout=5000)
Row(pragma_name=u'cache_size')
Row(cache_size=65536)
Row(pragma_name=u'cache_spill')
Row(cache_spill=65536)
Row(pragma_name=u'case_sensitive_like')
Row(pragma_name=u'cell_size_check')
Row(cell_size_check=0)
Row(pragma_name=u'checkpoint_fullfsync')
Row(checkpoint_fullfsync=0)
Row(pragma_name=u'collation_list')
Row(seq=0, name=u'ROT13')
Row(seq=1, name=u'NUMERICS')
Row(seq=2, name=u'UNACCENTED')
Row(seq=3, name=u'NAMES')
Row(seq=4, name=u'NOCASEU')
Row(seq=5, name=u'IPADDRESS')
Row(seq=6, name=u'RTRIM')
Row(seq=7, name=u'NOCASE')
Row(seq=8, name=u'BINARY')
Row(pragma_name=u'compile_options')
Row(compile_options=u'ALLOW_COVERING_INDEX_SCAN')
Row(compile_options=u'ALLOW_URI_AUTHORITY')
Row(compile_options=u'COMPILER=gcc-8.1.0')
Row(compile_options=u'DATETIME_NEW')
Row(compile_options=u'DEFAULT_CACHE_SIZE=65536')
Row(compile_options=u'DEFAULT_FOREIGN_KEYS')
Row(compile_options=u'DEFAULT_MMAP_SIZE=0')
Row(compile_options=u'DEFAULT_PAGE_SIZE=4096')
Row(compile_options=u'DEFAULT_PROXYDIR_PERMISSIONS=0755')

Re: [sqlite] Does sqlite3 support alter table add primary key?

[Shawn Wagner]

I don't think I've ever seen a decent sqlite page on tutorialspoint. Some,
like this one, appear to have been written by people who have never
actually used it.

On Wed, Apr 10, 2019, 5:11 AM Peng Yu  wrote:

> Hi,
>
> I got the following error. Does sqlite3 support alter table add primary
> key?
>
> sqlite> alter table mytab add primary key (h1);
> Error: near "primary": syntax error
>
> https://www.tutorialspoint.com/sqlite/sqlite_primary_key.htm
>
> --
> 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

Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

[Shawn Wagner]

Interesting. Must be that the pragmas that take arguments need parens when
used in table valued function form. I've never tried without them when
using a pragma that way.

Anyways, the sqlite3 shell is built with that introspection option, at
least on Linux (tested with one built from source myself without any
special configure arguments, and the stock Ubuntu one). I don't have a
Windows version handy to test right now. It's apparently different for some
reason?

On Wed, Apr 10, 2019, 3:46 AM Graham Holden  wrote:

> Wednesday, April 10, 2019, 11:16:07 AM, Shawn Wagner <
> shawnw.mob...@gmail.com> wrote:
>
> > It should be pragma_function_list(). Note the parentheses.
>
> That doesn't make a difference if the shell hasn't been built with
> -DSQLITE_INTROSPECTION_PRAGMAS, and they don't appear necessary for a
> pragma that IS built-in (such as "pragma_database_list"):
>
> SQLite version 3.27.2 2019-02-25 16:06:06
> Enter ".help" for usage hints.
> sqlite> select * from pragma_database_list;
> 0|main|X:\V1LS\z
> sqlite> select * from pragma_database_list();
> 0|main|X:\V1LS\z
> sqlite> select * from pragma_function_list ;
> Error: no such table: pragma_function_list
> sqlite> select * from pragma_function_list() ;
> Error: no such table: pragma_function_list
>
>
>
> ___
> 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] Does sqlite3 support alter table add primary key?

[David Raymond]

Nope.

Here's the doc page with what SQLite supports: https://www.sqlite.org/lang.html
And here's the alter table page: https://www.sqlite.org/lang_altertable.html


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peng Yu
Sent: Wednesday, April 10, 2019 8:11 AM
To: SQLite mailing list
Subject: [sqlite] Does sqlite3 support alter table add primary key?

Hi,

I got the following error. Does sqlite3 support alter table add primary key?

sqlite> alter table mytab add primary key (h1);
Error: near "primary": syntax error

https://www.tutorialspoint.com/sqlite/sqlite_primary_key.htm

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

Re: [sqlite] compressed sqlite3 database file?

[Keith Medcalf]

The disassembled bird will always require more tar to coat than the assembled 
bird.  This is because the disassembled bird will have a greater surface area 
to coat with tar than the assembled bird.  This is a fact of physics which, 
although you may try as you might, you cannot change (unless of course you are 
Q in which case you may simply change the gravimetric constant of the universe).

You should probably perform "data normalization" on the contents of your file.  
This is likely to result in the greatest reduction in space used that anything 
else in the multiverse ...

---
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 Peng Yu
>Sent: Tuesday, 9 April, 2019 23:39
>To: SQLite mailing list
>Subject: [sqlite] compressed sqlite3 database file?
>
>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] Does sqlite3 support alter table add primary key?

[Peng Yu]

Hi,

I got the following error. Does sqlite3 support alter table add primary key?

sqlite> alter table mytab add primary key (h1);
Error: near "primary": syntax error

https://www.tutorialspoint.com/sqlite/sqlite_primary_key.htm

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

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

[Peng Yu]

> > Your comparison is unfair. A .gz file is COMPRESSED by gzip and not in any 
> > way human readable without uncompressing it first.

But to store the file (and occasionally search the data), I would
prefer 278MB instead of 1.5GB. With a .gz file, at least I can zcat
and zgrep.

> How big is the result if you compress the sqlite file through gzip?

620MB

> Or how big is the TSV file you get if you expand your .gz file ?

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

Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

[Graham Holden]

Wednesday, April 10, 2019, 11:16:07 AM, Shawn Wagner  
wrote:

> It should be pragma_function_list(). Note the parentheses.

That doesn't make a difference if the shell hasn't been built with
-DSQLITE_INTROSPECTION_PRAGMAS, and they don't appear necessary for a
pragma that IS built-in (such as "pragma_database_list"):

SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> select * from pragma_database_list;
0|main|X:\V1LS\z
sqlite> select * from pragma_database_list();
0|main|X:\V1LS\z
sqlite> select * from pragma_function_list ;
Error: no such table: pragma_function_list
sqlite> select * from pragma_function_list() ;
Error: no such table: pragma_function_list



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

Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

[Shawn Wagner]

It should be pragma_function_list(). Note the parentheses.

On Wed, Apr 10, 2019, 2:29 AM Luuk  wrote:

>
> On 10-4-2019 10:28, Kees Nuyt wrote:
> > On Wed, 10 Apr 2019 13:17:23 +1000, John wrote:
> >
> >> 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?
> > sqlite> .mode column
> > sqlite> .header on
> > sqlite> .width 28 8
> > sqlite> select * from pragma_function_list;
>
> D:\>sqlite3
> SQLite version 3.27.2 2019-02-25 16:06:06
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .mode column
> sqlite> .header on
> sqlite> .width 28 8
> sqlite> select * from pragma_function_list;
> Error: no such table: pragma_function_list
> sqlite>
>
> This is "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?
> > I don't think so.
> >
> ___
> 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] Built in and enabled Virtual Table extensions in SQLite CLI?

[Graham Holden]

Wednesday, April 10, 2019, 10:28:55 AM, Luuk  wrote:

> On 10-4-2019 10:28, Kees Nuyt wrote:

>> sqlite> select * from pragma_function_list;

> sqlite> select * from pragma_function_list;
> Error: no such table: pragma_function_list

From the help page (https://www.sqlite.org/pragma.html#pragma_function_list)
this option is only available "...if SQLite is built using the
-DSQLITE_INTROSPECTION_PRAGMAS compile-time option.". My (standard) 
copy of 3.27.2 doesn't support this either, so I'm guessing Keet must
have a custom-built version.



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

Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

[Luuk]

On 10-4-2019 10:28, Kees Nuyt wrote:

On Wed, 10 Apr 2019 13:17:23 +1000, John wrote:


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?

sqlite> .mode column
sqlite> .header on
sqlite> .width 28 8
sqlite> select * from pragma_function_list;


D:\>sqlite3
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode column
sqlite> .header on
sqlite> .width 28 8
sqlite> select * from pragma_function_list;
Error: no such table: pragma_function_list
sqlite>

This is "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?

I don't think so.


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

Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

[Kees Nuyt]

On Wed, 10 Apr 2019 13:17:23 +1000, John wrote:

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

sqlite> .mode column
sqlite> .header on
sqlite> .width 28 8
sqlite> select * from pragma_function_list;

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

I don't think so.

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

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

[Simon Slavin]

On 10 Apr 2019, at 7:33am, Hick Gunter  wrote:

> Your comparison is unfair. A .gz file is COMPRESSED by gzip and not in any 
> way human readable without uncompressing it first. How big is the result if 
> you compress the sqlite file through gzip?

Or how big is the TSV file you get if you expand your .gz file ?

The development team for SQLite make a 'Compressed and Encrypted Read-Only 
Database (CEROD) Extension' which is not free and requires a US$2000 license.  
It can be used only if the data you want compressed is going to be read-only.


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

Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

[Luuk]

On 10-4-2019 05:17, John McMahon wrote:
which ones are built-in and which of those are enabled in the standard 
downloadable Win32 SQLite CLI


Some possibility tho show this like (i.e.) '.extensions' would be very 
nice!?





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

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

[Nik Jain]

 >If the original table has an index starting with price:
>WITH idlist(id) AS (SELECT id FROM fts WHERE col1 MATCH '50') SELECT *
FROM table CROSS JOIN idlist ON (idlist.id=table.id) ORDER BY price;
>or
>WITH idlist(id) AS (SELECT id FROM fts WHERE col1 MATCH '50') SELECT *
FROM table WHERE id IN (SELECT id FROM idlist);

Same time here.. And it doesn't seem to matter if there is an index. Its
always "`--USE TEMP B-TREE FOR ORDER BY"
Cross join is wayy slower btw. I guess its just not possible for it to use
the index when orderingBy  with a fts table.. Gotta move one for me.

And please consider adding a forum. Its pretty annoying to receive 10 mails
a day on a variety of topics just to follow a discussion.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Hick Gunter]

The time difference is easily explained:

The first query stops after it has retrieved 10 matching records from the fts 
table.

The second query has to retrieve all of the matching records, sort them in the 
desired sort order, and then discard all but the first 10 records.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Nik Jain
Gesendet: Mittwoch, 10. April 2019 08:43
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] How to use ORDER BY on FTS5 table ?

I see.
Query plan with order by:

sqlite>  explain query plan  select * from productsfts p where
sqlite> p.attributes
match '500' order by lastprice;
QUERY PLAN
|--SCAN TABLE productsfts AS p VIRTUAL TABLE INDEX 1:
`--USE TEMP B-TREE FOR ORDER BY

Query times of both queries:
sqlite> select * from productsfts p where p.attributes match '500' limit
sqlite> 10;
Run Time: real 0.000 user 0.000137 sys 0.000125

sqlite> select * from productsfts p where p.attributes match '500' order
sqlite> by
lastprice limit 10;
Run Time: real 0.041 user 0.003760 sys 0.036068

Not sure how to proceed. I am guessing that this is a common scenario, that has 
a regular way of being done. If so could anyone point me to anything?
Thanks

On Wed, Apr 10, 2019 at 11:39 AM Clemens Ladisch  wrote:

> PM Nik Jain wrote:
> > A SCAN is being performed on a fts5 table. I am not sure but I think
> > that means no index.
> >
> > sqlite>  explain query plan  select * from productsfts p where
> p.attributes match '50'limit 6;
> > `--SCAN TABLE productsfts AS p VIRTUAL TABLE INDEX 1:
>
> Everything except "INDEX 0" means that it is not a plain table scan,
> but that the virtual table module does its own filtering.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

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

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

[Hick Gunter]

Forgot to append "ORDER BY price" on the second query

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Hick Gunter
Gesendet: Mittwoch, 10. April 2019 09:35
An: 'SQLite mailing list' 
Betreff: Re: [sqlite] [EXTERNAL] How to use ORDER BY on FTS5 table ?

If the original table has an index starting with price:

WITH idlist(id) AS (SELECT id FROM fts WHERE col1 MATCH '50') SELECT * FROM 
table CROSS JOIN idlist ON (idlist.id=table.id) ORDER BY price;

or

WITH idlist(id) AS (SELECT id FROM fts WHERE col1 MATCH '50') SELECT * FROM 
table WHERE id IN (SELECT id FROM idlist);

Both statements will materialize the fts query and then retrieve records from 
the normal table in the desired order. If you only need "a few" columns fort he 
specific query, you can also append these to the index (build a "covering 
index") to allow sqlite to fulfill the field list from the index without 
accessing the table.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Nik Jain
Gesendet: Sonntag, 07. April 2019 09:41
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] How to use ORDER BY on FTS5 table ?

 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


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

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


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

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

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

[Hick Gunter]

If the original table has an index starting with price:

WITH idlist(id) AS (SELECT id FROM fts WHERE col1 MATCH '50') SELECT * FROM 
table CROSS JOIN idlist ON (idlist.id=table.id) ORDER BY price;

or

WITH idlist(id) AS (SELECT id FROM fts WHERE col1 MATCH '50') SELECT * FROM 
table WHERE id IN (SELECT id FROM idlist);

Both statements will materialize the fts query and then retrieve records from 
the normal table in the desired order. If you only need "a few" columns fort he 
specific query, you can also append these to the index (build a "covering 
index") to allow sqlite to fulfill the field list from the index without 
accessing the table.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Nik Jain
Gesendet: Sonntag, 07. April 2019 09:41
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] How to use ORDER BY on FTS5 table ?

 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


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

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

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

[Nik Jain]

I see.
Query plan with order by:

sqlite>  explain query plan  select * from productsfts p where p.attributes
match '500' order by lastprice;
QUERY PLAN
|--SCAN TABLE productsfts AS p VIRTUAL TABLE INDEX 1:
`--USE TEMP B-TREE FOR ORDER BY

Query times of both queries:
sqlite> select * from productsfts p where p.attributes match '500' limit 10;
Run Time: real 0.000 user 0.000137 sys 0.000125

sqlite> select * from productsfts p where p.attributes match '500' order by
lastprice limit 10;
Run Time: real 0.041 user 0.003760 sys 0.036068

Not sure how to proceed. I am guessing that this is a common scenario, that
has a regular way of being done. If so could anyone point me to anything?
Thanks

On Wed, Apr 10, 2019 at 11:39 AM Clemens Ladisch  wrote:

> PM Nik Jain wrote:
> > A SCAN is being performed on a fts5 table. I am not sure but I
> > think that means no index.
> >
> > sqlite>  explain query plan  select * from productsfts p where
> p.attributes match '50'limit 6;
> > `--SCAN TABLE productsfts AS p VIRTUAL TABLE INDEX 1:
>
> Everything except "INDEX 0" means that it is not a plain table scan,
> but that the virtual table module does its own filtering.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

[Hick Gunter]

Your comparison is unfair. A .gz file is COMPRESSED by gzip and not in any way 
human readable without uncompressing it first. How big is the result if you 
compress the sqlite file through gzip?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Peng Yu
Gesendet: Mittwoch, 10. April 2019 07:39
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] compressed sqlite3 database file?

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


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

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

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

[Clemens Ladisch]

PM Nik Jain wrote:
> A SCAN is being performed on a fts5 table. I am not sure but I
> think that means no index.
>
> sqlite>  explain query plan  select * from productsfts p where p.attributes 
> match '50'limit 6;
> `--SCAN TABLE productsfts AS p VIRTUAL TABLE INDEX 1:

Everything except "INDEX 0" means that it is not a plain table scan,
but that the virtual table module does its own filtering.


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