Re: [sqlite] Output/CSV

[Richard Hipp]

On 1/12/18, John Gutierrez  wrote:
> Sqlite Shell:
>
> On Linux, using .output and .mode csv, sqlite produces a [dos] 1L, 161C
> file.  Is this a bug or is it a compile issue for my distribution to
> address?

I don't know what a "[dos] 1L, 161C" file is?  Can you explain?

Perhaps you are concerned that the CSV output uses \r\n line
terminators instead of the more succinct \n favored by unix.  That is
intentional, and it is because that is what RFC4180
(https://www.ietf.org/rfc/rfc4180.txt) says should happen.

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

[sqlite] Output/CSV

[John Gutierrez]

Sqlite Shell:

On Linux, using .output and .mode csv, sqlite produces a [dos] 1L, 161C 
file.  Is this a bug or is it a compile issue for my distribution to 
address?

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

Re: [sqlite] EXTERNAL: SQLite 3.22.0 coming soon

[Wolfgang Enzinger]

Am Wed, 10 Jan 2018 02:25:35 + schrieb Edwards, Mark C.:

> Release mode/x86 Visual Studio 2015 Prono problems with the new snapshot

Same here with my ancient Visual Studio 2005. :-)

Cheers Wolfgang

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

[sqlite] database type graph

[merlinverdecia]

How can I design in a database a database type graph?
thank


This message was sent using IMP, the Internet Messaging Program.



--
Este mensaje le ha llegado mediante el servicio de correo electronico que 
ofrece Infomed para respaldar el cumplimiento de las misiones del Sistema 
Nacional de Salud. La persona que envia este correo asume el compromiso de usar 
el servicio a tales fines y cumplir con las regulaciones establecidas

Infomed: http://www.sld.cu/

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

Re: [sqlite] pcache1FetchStage2 CRASH

[Richard Hipp]

On 1/12/18, zheng xiaojin  wrote:
> I am using the 3.15.2, and currently a problem comes to me that,
> pcache1FetchStage2 CRASH when access the pgroup->lru.pLRUPrev->isAnchor, the
> crash info is sigsegv access 0x16 on IOS.
> And a little frequently about0.1percent.

Do you still have the problem with the latest version of SQLite?

-- 
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] os_unix.c:36136: (2)

[Richard Hipp]

On 1/11/18, wei1.z  wrote:
> What is the meaning of this line?
>
> 01-11 14:40:59.733 10011  2864  2877 E SQLiteLog: (14) os_unix.c:36136: (2)
> open() -
>
> db file cannot be found, or permission issue ?

It means that SQLite could not open a file either because that file
does not exist or because you do not have appropriate permissions to
open that file.

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

[sqlite] pcache1FetchStage2 CRASH

[zheng xiaojin]

I am using the 3.15.2, and currently a problem comes to me that, 
pcache1FetchStage2 CRASH when access the pgroup->lru.pLRUPrev->isAnchor, the 
crash info is sigsegv access 0x16 on IOS.
And a little frequently about0.1percent.

获取 Outlook for Android

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

[sqlite] os_unix.c:36136: (2)

[wei1.z]

What is the meaning of this line?

01-11 14:40:59.733 10011  2864  2877 E SQLiteLog: (14) os_unix.c:36136: (2)
open() - 

db file cannot be found, or permission issue ?

Thanks a lot!



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.22.0 coming soon

[Richard Hipp]

On 1/12/18, petern  wrote:
> Is adding arg_count and
> is_aggregate columns to PRAGMA function_list() on the roadmap?

How would that work with functions like coalesce() and max() that take
an arbitrary number of arguments, or like max() that is an aggregate
with one argument and a scalar with two or more arguments?

-- 
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] SQLite 3.22.0 coming soon

[petern]

This is premature of course.  The columns of the current PRAGMA
function_list() report do not form a practical key for functions.
Additional columns arg_count and is_aggregate would be needed  join PRAGMA
function_list() with a function description table.

Suggestion: As an example of both testing the new zipfile facility and
introspection, SQLite core built-in descriptions could be published as a
zip file which joins properly with a newly sufficient PRAGMA
function_list().

So, now the more precisely relevant question is:   Is adding arg_count and
is_aggregate columns to PRAGMA function_list() on the roadmap?

With that change alone, at least extension implementors would have a way to
publish PRAGMA interactive function argument descriptions.

Richard?



On Fri, Jan 12, 2018 at 8:47 AM, petern  wrote:

> Ryan.  The core and sqlite3_create_function...() needn't be burdened at
> all except to store some very basic description strings as a compile time
> option.
>
> PRAGMA function_list() cold gather descriptions on the fly by:
>
> 1. Spinning through the list of registered %_function_description(F,N)
> functions for each row, or
>
> 2. Spinning through a list of library module exports, say
> sqlite3__function_description(F,N).
>
> Modules implementing either interface could do so trivially with if
> statements or with a binary search of hand sorted description array.
>
> This enhancement is feasible, easy to implement, and would help
> tremendously to see exactly how to call any function from the command line.
>
> My question mainly was to find out if the idea was considered and if it is
> on the roadmap along with the SQLITE_INTROSPECTION_PRAGMAS change.
>
> Peter
>
>
> On Thu, Jan 11, 2018 at 2:31 PM, R Smith  wrote:
>
>>
>> On 2018/01/11 8:11 PM, petern wrote:
>>
>>> With SQLITE_INTROSPECTION_PRAGMAS turned on, is a function description on
>>> the roadmap?
>>> It would be very helpful to expose a short description of function
>>> arguments.
>>>
>>> Implementation suggestion: a new trailing argument "description" on
>>> sqlite3_create_function() or sqlite3_create_function_v2()
>>> and corresponding description column in the PRAGMA function_list report.
>>>
>>
>> Well, I like the idea.
>>
>> For one, it would allow an easy to read updated description for those of
>> us who maintain SQLite management tools and connectors... but accessing the
>> web is more or less equally simple, though a direct api would be nice too.
>> Maybe this would better serve add-on libraries and UDFs since those
>> function descriptions are not typically available on the web, and certainly
>> not on the standard sqlite site.
>>
>> The only con I'm seeing is the extra disk/memory footprint for what is
>> essentially comments and can (especially for the standard functions) be
>> found easily at: https://sqlite.org/lang_corefunc.html
>>
>> Perhaps there is another benefit I'm not yet seeing that would better
>> merit paying said resource cost - if so, I'm eager to hear it.
>>
>> Cheers,
>> Ryan
>>
>> ___
>> 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] Is foreign key support disabled within triggers with raise functions?

[David Raymond]

I think the thing here is...
"...But the FAIL resolution does not back out prior changes of the SQL 
statement that failed ..." Emphasis on the 
last part.

So you would think that in autocommit mode you would wind up in the middle of 
your "implicitly created" transaction, which you could then roll back. The 
problem appears to be that it winds up committing the data from the implicit 
transaction.

sqlite> insert into vtrig select 5;
Error: this statement seems to to temporarily disable foreign support

sqlite> select * from deptab;
id|ref
1|2

sqlite> rollback;
Error: cannot rollback - no transaction is active


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Friday, January 12, 2018 8:37 AM
To: SQLite mailing list
Subject: Re: [sqlite] Is foreign key support disabled within triggers with 
raise functions?

On 1/12/18, Shane Dev  wrote:
> Does that mean FK constraints are only checked
> if processing reaches the end of the trigger?

FKs are checked at the end of the entire statement.  If multiple
triggers fire, then they all run to completion before any FKs are
checked.
-- 
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.22.0 coming soon

[petern]

Ryan.  The core and sqlite3_create_function...() needn't be burdened at all
except to store some very basic description strings as a compile time
option.

PRAGMA function_list() cold gather descriptions on the fly by:

1. Spinning through the list of registered %_function_description(F,N)
functions for each row, or

2. Spinning through a list of library module exports, say
sqlite3__function_description(F,N).

Modules implementing either interface could do so trivially with if
statements or with a binary search of hand sorted description array.

This enhancement is feasible, easy to implement, and would help
tremendously to see exactly how to call any function from the command line.

My question mainly was to find out if the idea was considered and if it is
on the roadmap along with the SQLITE_INTROSPECTION_PRAGMAS change.

Peter


On Thu, Jan 11, 2018 at 2:31 PM, R Smith  wrote:

>
> On 2018/01/11 8:11 PM, petern wrote:
>
>> With SQLITE_INTROSPECTION_PRAGMAS turned on, is a function description on
>> the roadmap?
>> It would be very helpful to expose a short description of function
>> arguments.
>>
>> Implementation suggestion: a new trailing argument "description" on
>> sqlite3_create_function() or sqlite3_create_function_v2()
>> and corresponding description column in the PRAGMA function_list report.
>>
>
> Well, I like the idea.
>
> For one, it would allow an easy to read updated description for those of
> us who maintain SQLite management tools and connectors... but accessing the
> web is more or less equally simple, though a direct api would be nice too.
> Maybe this would better serve add-on libraries and UDFs since those
> function descriptions are not typically available on the web, and certainly
> not on the standard sqlite site.
>
> The only con I'm seeing is the extra disk/memory footprint for what is
> essentially comments and can (especially for the standard functions) be
> found easily at: https://sqlite.org/lang_corefunc.html
>
> Perhaps there is another benefit I'm not yet seeing that would better
> merit paying said resource cost - if so, I'm eager to hear it.
>
> Cheers,
> Ryan
>
> ___
> 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] Efficient ways to maintaining order rank / row_number() in a rather large set ?

[Simon Slavin]

On 12 Jan 2018, at 10:31am, Eric Grange  wrote:
> 
>> This should not be true.  You should not be using OFFSET.  Your queries
>> should be something like
> 
> That was with only the "value" field being indexed (so without a rank
> field), is there a better way than OFFSET in that case ?

Can’t think of one.  OFFSET is the easiest way to get what you want if you’re 
not storing the values you’re searching/sorting on.  But as you’ve seen, SQLite 
derives OFFSET by compiling the whole list, from the first result, and simply 
not reporting the first entries.  So it’s slow.

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

Re: [sqlite] Is foreign key support disabled within triggers with raise functions?

[Richard Hipp]

On 1/12/18, Shane Dev  wrote:
> Does that mean FK constraints are only checked
> if processing reaches the end of the trigger?

FKs are checked at the end of the entire statement.  If multiple
triggers fire, then they all run to completion before any FKs are
checked.
-- 
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] Is foreign key support disabled within triggers with raise functions?

[Shane Dev]

Thanks for the explanation. Does that mean FK constraints are only checked
if processing reaches the end of the trigger?

On 12 January 2018 at 13:29, Richard Hipp  wrote:

> Test 1 makes the changes, the checks the FK constraints, sees that the
> FK constraints are violated and hence runs ABORT, which backs out the
> change.
>
> Test 2 makes the change, then runs FAIL, which stops all further
> processing.  The FK constraints are never checked, and the changes are
> not backed out.
>
> On 1/12/18, Shane Dev  wrote:
> > Hello,
> >
> > Perhaps it would be clearer if I ask the question in a different way. Why
> > does the following statement -
> >
> > insert into vtrig select 5;
> >
> > fail to insert a record in Test 1 below (as expected) but succeeds in
> Test
> > 2 (despite the foreign key constraint)?
> >
> > sqlite> CREATE TABLE reftab(id integer primary key);
> > sqlite> CREATE TABLE deptab(id integer primary key, ref int references
> > reftab);
> > sqlite> pragma foreign_keys;
> > foreign_keys
> > 1
> > sqlite> CREATE VIEW vtrig as select 1;
> >
> > Test 1
> > =
> >
> > sqlite> CREATE TRIGGER ttrig instead of insert on vtrig begin
> >...> insert into deptab(ref) select 2;
> >...> end;
> > sqlite> insert into vtrig select 5;
> > Error: FOREIGN KEY constraint failed
> > sqlite> select * from deptab;
> > sqlite>
> >
> >
> > Test 2
> > =
> >
> > sqlite> drop trigger ttrig;
> > sqlite> CREATE TRIGGER ttrig instead of insert on vtrig begin
> >...> insert into deptab(ref) select 2;
> >...> select raise(FAIL, 'this statement seems to temporarily disable
> > foreign
> >...> support');
> >...> end;
> > sqlite> insert into vtrig select 5;
> > Error: this statement seems to temporarily disable foreign
> > support
> > sqlite> select * from deptab;
> > id  ref
> > 1   2
> >
> >
> > On 12 January 2018 at 00:48, Richard Hipp  wrote:
> >
> >> On 1/11/18, Shane Dev  wrote:
> >> >
> >> > CREATE VIEW vtrig as select 1;
> >> > CREATE TRIGGER ttrig instead of insert on vtrig begin
> >> >   delete from deptab;
> >> >   delete from reftab;
> >> >   insert into deptab(ref) select 2;
> >> >   select raise(FAIL, 'this statement seems to temporarily disable
> >> > foreign
> >> > support') where (select count(*) > 0 from deptab);
> >> > end;
> >> >
> >> > sqlite> insert into vtrig select 5;
> >> > Error: this statement seems to temporarily disable foreign support
> >> > sqlite> select * from reftab;
> >> > sqlite> select * from deptab;
> >> > id  ref
> >> > 1   2
> >> > sqlite> pragma foreign_keys;
> >> > foreign_keys
> >> > 1
> >> >
> >> > Can we conclude foreign key support is disabled within triggers with
> >> raise
> >> > functions?
> >>
> >> I don't what you mean by "disabled".  When you hit a raise(FAIL)
> >> SQLite stops whatever it was doing and leave the database in whatever
> >> half-way completed state it was in at the moment.  That's what
> >> raise(FAIL) is suppose to do.
> >>
> >> Did you mean to do "raise(ABORT)" instead, which should do what I
> >> think you are trying to accomplish.
> >>
> >> --
> >> 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
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is foreign key support disabled within triggers with raise functions?

[Richard Hipp]

Test 1 makes the changes, the checks the FK constraints, sees that the
FK constraints are violated and hence runs ABORT, which backs out the
change.

Test 2 makes the change, then runs FAIL, which stops all further
processing.  The FK constraints are never checked, and the changes are
not backed out.

On 1/12/18, Shane Dev  wrote:
> Hello,
>
> Perhaps it would be clearer if I ask the question in a different way. Why
> does the following statement -
>
> insert into vtrig select 5;
>
> fail to insert a record in Test 1 below (as expected) but succeeds in Test
> 2 (despite the foreign key constraint)?
>
> sqlite> CREATE TABLE reftab(id integer primary key);
> sqlite> CREATE TABLE deptab(id integer primary key, ref int references
> reftab);
> sqlite> pragma foreign_keys;
> foreign_keys
> 1
> sqlite> CREATE VIEW vtrig as select 1;
>
> Test 1
> =
>
> sqlite> CREATE TRIGGER ttrig instead of insert on vtrig begin
>...> insert into deptab(ref) select 2;
>...> end;
> sqlite> insert into vtrig select 5;
> Error: FOREIGN KEY constraint failed
> sqlite> select * from deptab;
> sqlite>
>
>
> Test 2
> =
>
> sqlite> drop trigger ttrig;
> sqlite> CREATE TRIGGER ttrig instead of insert on vtrig begin
>...> insert into deptab(ref) select 2;
>...> select raise(FAIL, 'this statement seems to temporarily disable
> foreign
>...> support');
>...> end;
> sqlite> insert into vtrig select 5;
> Error: this statement seems to temporarily disable foreign
> support
> sqlite> select * from deptab;
> id  ref
> 1   2
>
>
> On 12 January 2018 at 00:48, Richard Hipp  wrote:
>
>> On 1/11/18, Shane Dev  wrote:
>> >
>> > CREATE VIEW vtrig as select 1;
>> > CREATE TRIGGER ttrig instead of insert on vtrig begin
>> >   delete from deptab;
>> >   delete from reftab;
>> >   insert into deptab(ref) select 2;
>> >   select raise(FAIL, 'this statement seems to temporarily disable
>> > foreign
>> > support') where (select count(*) > 0 from deptab);
>> > end;
>> >
>> > sqlite> insert into vtrig select 5;
>> > Error: this statement seems to temporarily disable foreign support
>> > sqlite> select * from reftab;
>> > sqlite> select * from deptab;
>> > id  ref
>> > 1   2
>> > sqlite> pragma foreign_keys;
>> > foreign_keys
>> > 1
>> >
>> > Can we conclude foreign key support is disabled within triggers with
>> raise
>> > functions?
>>
>> I don't what you mean by "disabled".  When you hit a raise(FAIL)
>> SQLite stops whatever it was doing and leave the database in whatever
>> half-way completed state it was in at the moment.  That's what
>> raise(FAIL) is suppose to do.
>>
>> Did you mean to do "raise(ABORT)" instead, which should do what I
>> think you are trying to accomplish.
>>
>> --
>> 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
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] Is foreign key support disabled within triggers with raise functions?

[Shane Dev]

Hello,

Perhaps it would be clearer if I ask the question in a different way. Why
does the following statement -

insert into vtrig select 5;

fail to insert a record in Test 1 below (as expected) but succeeds in Test
2 (despite the foreign key constraint)?

sqlite> CREATE TABLE reftab(id integer primary key);
sqlite> CREATE TABLE deptab(id integer primary key, ref int references
reftab);
sqlite> pragma foreign_keys;
foreign_keys
1
sqlite> CREATE VIEW vtrig as select 1;

Test 1
=

sqlite> CREATE TRIGGER ttrig instead of insert on vtrig begin
   ...> insert into deptab(ref) select 2;
   ...> end;
sqlite> insert into vtrig select 5;
Error: FOREIGN KEY constraint failed
sqlite> select * from deptab;
sqlite>


Test 2
=

sqlite> drop trigger ttrig;
sqlite> CREATE TRIGGER ttrig instead of insert on vtrig begin
   ...> insert into deptab(ref) select 2;
   ...> select raise(FAIL, 'this statement seems to temporarily disable
foreign
   ...> support');
   ...> end;
sqlite> insert into vtrig select 5;
Error: this statement seems to temporarily disable foreign
support
sqlite> select * from deptab;
id  ref
1   2


On 12 January 2018 at 00:48, Richard Hipp  wrote:

> On 1/11/18, Shane Dev  wrote:
> >
> > CREATE VIEW vtrig as select 1;
> > CREATE TRIGGER ttrig instead of insert on vtrig begin
> >   delete from deptab;
> >   delete from reftab;
> >   insert into deptab(ref) select 2;
> >   select raise(FAIL, 'this statement seems to temporarily disable foreign
> > support') where (select count(*) > 0 from deptab);
> > end;
> >
> > sqlite> insert into vtrig select 5;
> > Error: this statement seems to temporarily disable foreign support
> > sqlite> select * from reftab;
> > sqlite> select * from deptab;
> > id  ref
> > 1   2
> > sqlite> pragma foreign_keys;
> > foreign_keys
> > 1
> >
> > Can we conclude foreign key support is disabled within triggers with
> raise
> > functions?
>
> I don't what you mean by "disabled".  When you hit a raise(FAIL)
> SQLite stops whatever it was doing and leave the database in whatever
> half-way completed state it was in at the moment.  That's what
> raise(FAIL) is suppose to do.
>
> Did you mean to do "raise(ABORT)" instead, which should do what I
> think you are trying to accomplish.
>
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

[Eric Grange]

> But if you have million rows this could involve a lot of number-shuffling
and I can see that it might not work out in the real world.

Yes, also while value field can change several times per seconds
(thousandths in some cases), it is acceptable to have the ranking be
updated at a lower frequency and display somewhat "stale" ranking & values.

> This should not be true.  You should not be using OFFSET.  Your queries
should be something like

That was with only the "value" field being indexed (so without a rank
field), is there a better way than OFFSET in that case ?


On Tue, Jan 9, 2018 at 6:21 PM, Simon Slavin  wrote:

> On 9 Jan 2018, at 11:35am, Eric Grange  wrote:
>
> > In both cases, since things are constantly in flux, the absolute rank and
> > neighbor do not really matter
> > (outside the top ranks), but changes in rank are being looked at.
> > i.e. having rank 155k or 154k is not really meaningful in itself, but on
> > the other hand
> > gaining 1000 "ranks" by going from 155k to 154k is what users are after.
>
> Okay, I see what you mean.  You have a special, unusual, case where the
> ranks change frequently and you have a genuine need to do things like "give
> me all the ranks from 154k to 155k".
>
> That’s a very difficult thing to do quickly.  The fastest way to do it
> would be different depending on which you do more: change ranks or query
> ranks.
>
> The cannonical SQL way would be that every time a figure changes you would
> change the ranks of all the rows between the two positions.  This would
> take only two UPDATE commands each time.  The data would be up-to-date all
> the time and therefore queries would be fast.  But if you have million rows
> this could involve a lot of number-shuffling and I can see that it might
> not work out in the real world.
>
> >> But then, if your range queries are based on a rank derived from value,
> why
> >> not index value directly? You'd still get fast range queries based on
> values, no?
> >
> > You get fast value range queries, but rank range queries become slower
> and
> > slower the farther away you get from the top rank.
>
> This should not be true.  You should not be using OFFSET.  Your queries
> should be something like
>
> SELECT * FROM ranked WHERE rank BETWEEN 154000 AND !55000 ORDER BY
> rank
>
> which should be lightning fast because you have an index on "rank".
>
> Simon.
> ___
> 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] sqlite3_column_decltype and max and min

[John G]

Thanks Warren. Sorry about that, I had an old version in /opt/local/bin.

John Gillespie

On 11 January 2018 at 15:24, Warren Young  wrote:

> On Jan 11, 2018, at 5:47 AM, John G  wrote:
> >
> > Is this because I am stuck with version 3.8.8.3 which is what MacOS
> Sierra
> > provides?
>
> I have sqlite3 version 3.16.0 in /usr/bin on this 10.12.6 (Sierra) system.
> ___
> 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