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
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 values
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,
selec
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 14:0
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 compi
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 in
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
sqlite-use
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 sqlite
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:
> A
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 rep
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 missi
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 tes
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 if
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 belo
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 xBestInde
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 wi
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 proble
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 equ
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 VIRT
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)), de
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 ha
31 matches
Mail list logo
