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
It's a long story, but that project is done. It's a reporting tool. It worked
well, until the process changed. I will keep it in mind for future projects...
:-)
From: Igor Korot
Sent: Friday, April 5, 2019 02:55 PM
To: SQLite mailing list
Subject: Re: [sqlite] Returning NULL or empty
Hi,
On Fri, Apr 5, 2019 at 1:36 PM Jose Isaias Cabrera wrote:
>
>
> Thanks, Simon. Works like a charm...
Unless backwards compatibility is important (do you expect to go back to
pre-foreign keys implementation), I'd do FOREIGN KEY amd forget anout that...
Thank you.
>
>
> From: Simon Davies
A request for a teachable moment.. :-)
Why does this work,
select
a.*, b.* from t as a LEFT JOIN z as b on a.a = b.f
AND
a.idate = (select max(idate) from t where a = a.a)
AND
b.idate = (select max(idate) from z where f = a.a)
where a.a = 'p006'
ORDER BY a.a
;
and this one does not,
Thanks, Simon. Works like a charm...
From: Simon Davies
Sent: Friday, April 5, 2019 12:24 PM
To: SQLite mailing list
Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not
satisfy all of the query
On Fri, 5 Apr 2019 at 14:45, Jose Isaias Cabrera wrote:
>
> Greetings.
Folks, this is called a "Teachable Moment", by James K. Lowden. ;-)
From: James K. Lowden
Sent: Friday, April 5, 2019 12:31 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not
satisfy all of the query
On Fri, 5 Apr 2019
On 5 Apr 2019, at 7:07pm, Lee, Jason wrote:
> Is there any way to create a prepared statement without an associated
> database instance? I have one fixed insert that I am running on many separate
> databases that are inserting different data sets, and would like to amortize
> the cost of
Hi. Is there any way to create a prepared statement without an associated
database instance? I have one fixed insert that I am running on many separate
databases that are inserting different data sets, and would like to amortize
the cost of compiling the insert if possible.
Jason Lee
Stop
On Fri, Apr 5, 2019, 11:31 AM James K. Lowden
wrote:
> On Fri, 5 Apr 2019 15:45:10 +0300
> Arthur Blondel wrote:
>
> > The data is always the same. That's why removing one row should be
> > enough to insert a new one.
> > My problem is that some times I need to remove many rows to add one
On Apr 5, 2019, at 12:31 PM, James K. Lowden wrote:
>
> On Fri, 5 Apr 2019 15:45:10 +0300
> Arthur Blondel wrote:
>
>> The data is always the same. That's why removing one row should be
>> enough to insert a new one.
>> My problem is that some times I need to remove many rows to add one
>> new
On 2019-04-03 11:44, Warren Young wrote:
> As for the non-ASCII characters, they're UTF-8, which is the de facto
> standard character set on the Internet since around the time of The
> Bubble. Ignoring the embedded world, I can't think of an in-support
> OS that doesn't have built-in support for
On Fri, 5 Apr 2019 15:45:10 +0300
Arthur Blondel wrote:
> The data is always the same. That's why removing one row should be
> enough to insert a new one.
> My problem is that some times I need to remove many rows to add one
> new one.
SQLite *could* avoid that problem by pre-allocating space
On Fri, 5 Apr 2019 14:01:20 +
Jose Isaias Cabrera wrote:
> The owners of the business said that "there will never be...",
The perfect opportunity for a CHECK constraint or to enforce a foreign
key.
--jkl
___
sqlite-users mailing list
On Fri, 5 Apr 2019 at 14:45, Jose Isaias Cabrera wrote:
>
> Greetings.
>
> I have a few tables that I am bringing data from, but I found a bug in my
> logic, which I am trying to see if I can make it work. Please look at this
> scenario
>
> create table t (n INTEGER PRIMARY KEY, a, b, c, d, e,
> On 4/5/19 11:14 AM, Arthur Blondel wrote:
> > I have enough disk space. I just limit the database file size artificially
> > for testing purpose as you can see.
> > There is no problem of privilege and there is nothing else than the code I
> > sent. No other access to the DB.
> > I'm using
On 4/5/19 11:14 AM, Arthur Blondel wrote:
> I have enough disk space. I just limit the database file size artificially
> for testing purpose as you can see.
> There is no problem of privilege and there is nothing else than the code I
> sent. No other access to the DB.
> I'm using sqlite 3.16.2
As
I have enough disk space. I just limit the database file size artificially
for testing purpose as you can see.
There is no problem of privilege and there is nothing else than the code I
sent. No other access to the DB.
I'm using sqlite 3.16.2
On Fri, Apr 5, 2019 at 3:59 PM Chris Locke wrote:
>
On 4/5/19 9:50 AM, Joshua Wise wrote:
> Julian dates are definitely floating point numbers, not integers.
Julian dates, if being used to represent a time on a given date would be
a floating point number. A Julian date, if only needing to express a
time to the precision of a whole Day, could be
Yeah, that is what I am doing now, but that results in two selects, and it's
causing me to have to rewrite other pieces of the program. It's a long
story... The owners of the business said that "there will never be...", WRONG!
So, with that in mind, I wrote the reporting tool. Now, it's
On 5 Apr 2019, at 2:45pm, Jose Isaias Cabrera wrote:
> then do this,
>
> select
> a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p006'
> AND
> a.idate = (select max(idate) from t where a = a.a)
> AND
> b.idate = (select max(idate) from z where f = a.a)
> ORDER BY a.a
> ;
For
Julian dates are definitely floating point numbers, not integers.
On Thu, Apr 4, 2019, 3:37 PM James K. Lowden
wrote:
> On Thu, 4 Apr 2019 11:21:41 -0400
> Joshua Wise wrote:
>
> > > On the other hand, what table has a floating point number in its
> > > key?
> > >
> > > How do you even express
Greetings.
I have a few tables that I am bringing data from, but I found a bug in my
logic, which I am trying to see if I can make it work. Please look at this
scenario
create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1,
On 5 Apr 2019, at 1:45pm, Arthur Blondel wrote:
> I'm limited in space so when the DB is full (when sqlite3_exec() returns
> SQLITE_FULL when I try to insert a new row), I remove the oldest row
If SQLite returns SQLITE_FULL you cannot reliably do anything else to the
database. Because even
Ignoring for the moment the issues of journals, logs, etc.
When you delete a record you free up the space it used *on the page that it was
on* (well, pages if there are indexes)
Each page is part of the overall B-tree layout of the table/index. When you go
to add a new record, if it doesn't
On 5/4/62 16:44, Hick Gunter wrote:
I patched my SQlite 3.24 code to include the fix from the ticket
< if( rc==SQLITE_OK && (mBest = (pNew->prereq & ~mPrereq))!=0 ){
---
// from SQLite bugfix
if( rc==SQLITE_OK && ((mBest = (pNew->prereq & ~mPrereq))!=0 || bIn) ){
and changed the
Arthur - are you running SQLite in parallel runs?
If you access the database file using the sqlite3 command-line tool, and
try to execute the same SQL commands, do you get the same error ?
SQLite makes a temporary 'journal' file while it's working. I think that,
on your platform, by default it
OK, I wasn't clear.
I'm limited in space so when the DB is full (when sqlite3_exec() returns
SQLITE_FULL when I try to insert a new row), I remove the oldest row and
retry to insert the new one.
The data is always the same. That's why removing one row should be enough
to insert a new one.
My
I patched my SQlite 3.24 code to include the fix from the ticket
< if( rc==SQLITE_OK && (mBest = (pNew->prereq & ~mPrereq))!=0 ){
---
> // from SQLite bugfix
> if( rc==SQLITE_OK && ((mBest = (pNew->prereq & ~mPrereq))!=0 || bIn) ){
and changed the xBestIndex return value to be lower if the
Hi,
first post here. Got a perplexing problem with SQLite and no, the database is
perfectly writable.
In my app I use a different database as main database that unfortunately
doesn't have an FTS. Therefore, I'm using an SQLite database as index. The
SQLite database is very simple:
CREATE
Minor correction:
> On 5 Apr 2019, at 09:52, Lifepillar wrote:
>
> select decStr(decAdd(a,60)), case dec(b) when dec(c) then 1 else 0 end from
> t1;
> select decStr(decAdd(a,70)), case dec(c) when dec(b) then 1 else 0 end from
> t1;
> […]
> select count(*), count(dec(b)), decStr(decSum(b)),
On 5 Apr 2019, at 00:18, Simon Slavin wrote:
>
> On 4 Apr 2019, at 10:12pm, Lifepillar wrote:
>
>> This is essentially a pragmatic choice, as the semantics of NULLs is
>> unspecified and ambiguous.
>
> The way SQL handles NULLs may sometimes appear inconsistent, but is the
> result of SQL
31 matches
Mail list logo
