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
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
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
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
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
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
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
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
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
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
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
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
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
> > 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
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
>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
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
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
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
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
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
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
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
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:
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
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
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,
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
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
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
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
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
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
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
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
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?
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
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
"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
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
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
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
___
---
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
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
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
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
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
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.
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
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:
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"
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
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
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
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. :)
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
56 matches
Mail list logo
