Re: [sqlite] Yes - Getting SPAM from Using Mailing List

[Luuk]

On 27-4-2018 20:57, Denis Burke wrote:
> I know steps were taken to reduce it, but just confirming it is still going
> on today.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
I do not see spam in this list.

The main reason is, i only read messages that contain the text
'[sqlite]' in the subject.
It's one of the features of gmail to filter on such a thing.

-- 
Luuk

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

Re: [sqlite] Broken DB?

[Abroży Nieprzełoży]

http://www.sqlite.org/howtocorrupt.html

2018-04-27 23:58 GMT+02:00, Kevin O'Gorman:
> I've got a working site, but I made a copy of the database in order to do
> some development work.
> I've hit a snag that looks like a problem in the data.
>
> Ive written a management command to show the problem:
>
> from django.core.management.base import BaseCommand, CommandError
>
> # Stuff for the library
> from oil.models import Packet, Signature, Log, Voter
>
> class Command(BaseCommand):
> help = 'Shows a quick count of validations'
> BaseCommand.requires_migrations_checks = True
>
>
> def handle(self, *args, **options):
> voters = Log.objects.all()
> self.stdout.write(repr(voters[0]))
>
> I'm suspecting a problem has crept into my Log table, because it works fine
> if I change Log on the
> second line of handle() to any of the other tables.  If it runs as shown
> here however, I get
>
> kevin@camelot-x:/build/comprosloco$ manage oiltest
> Traceback (most recent call last):
>   File "./manage", line 22, in 
> execute_from_command_line(sys.argv)
>   File "/build/django/django/core/management/__init__.py", line 364, in
> execute_from_command_line
> utility.execute()
>   File "/build/django/django/core/management/__init__.py", line 356, in
> execute
> self.fetch_command(subcommand).run_from_argv(self.argv)
>   File "/build/django/django/core/management/base.py", line 283, in
> run_from_argv
> self.execute(*args, **cmd_options)
>   File "/build/django/django/core/management/base.py", line 330, in execute
> output = self.handle(*args, **options)
>   File "/raid3/build/comprosloco/oil/management/commands/oiltest.py", line
> 15, in handle
> self.stdout.write(repr(voters[0]))
>   File "/build/django/django/db/models/base.py", line 590, in __repr__
> u = six.text_type(self)
>   File "/raid3/build/comprosloco/oil/models.py", line 172, in __str__
> self.accepted
> TypeError: sequence item 0: expected str instance, datetime.datetime found
> kevin@camelot-x:/build/comprosloco$
>
> And I have no idea how to debug it further.  The schema of Log is
> sqlite> .schema oil_log
> CREATE TABLE "oil_log" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
> "packet" integer NOT NULL, "signature" integer NOT NULL, "action"
> varchar(20) NOT NULL, "criteria" varchar(150) NOT NULL, "candidates"
> varchar(100) NOT NULL, "accepted" varchar(10) NOT NULL, "user_id" integer
> NOT NULL REFERENCES "auth_user" ("id"), "timestamp" datetime NOT NULL);
> CREATE INDEX "oil_log_packet_ecd59bc4" ON "oil_log" ("packet");
> CREATE INDEX "oil_log_user_id_7f26e501" ON "oil_log" ("user_id");
> sqlite>
>
>
> Help???
>
>
> --
> Dictionary.com's word of the year: *complicit*
> ___
> 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] Broken DB?

[Kevin O'Gorman]

Oops.  Wrong list.  Should go to a Django group.  I noticed as soon as I
sent this.  Please ignore.

On Fri, Apr 27, 2018 at 2:58 PM, Kevin O'Gorman 
wrote:

> I've got a working site, but I made a copy of the database in order to do
> some development work.
> I've hit a snag that looks like a problem in the data.
>
> Ive written a management command to show the problem:
>
> from django.core.management.base import BaseCommand, CommandError
>
> # Stuff for the library
> from oil.models import Packet, Signature, Log, Voter
>
> class Command(BaseCommand):
> help = 'Shows a quick count of validations'
> BaseCommand.requires_migrations_checks = True
>
>
> def handle(self, *args, **options):
> voters = Log.objects.all()
> self.stdout.write(repr(voters[0]))
>
> I'm suspecting a problem has crept into my Log table, because it works
> fine if I change Log on the
> second line of handle() to any of the other tables.  If it runs as shown
> here however, I get
>
> kevin@camelot-x:/build/comprosloco$ manage oiltest
> Traceback (most recent call last):
>   File "./manage", line 22, in 
> execute_from_command_line(sys.argv)
>   File "/build/django/django/core/management/__init__.py", line 364, in
> execute_from_command_line
> utility.execute()
>   File "/build/django/django/core/management/__init__.py", line 356, in
> execute
> self.fetch_command(subcommand).run_from_argv(self.argv)
>   File "/build/django/django/core/management/base.py", line 283, in
> run_from_argv
> self.execute(*args, **cmd_options)
>   File "/build/django/django/core/management/base.py", line 330, in
> execute
> output = self.handle(*args, **options)
>   File "/raid3/build/comprosloco/oil/management/commands/oiltest.py",
> line 15, in handle
> self.stdout.write(repr(voters[0]))
>   File "/build/django/django/db/models/base.py", line 590, in __repr__
> u = six.text_type(self)
>   File "/raid3/build/comprosloco/oil/models.py", line 172, in __str__
> self.accepted
> TypeError: sequence item 0: expected str instance, datetime.datetime found
> kevin@camelot-x:/build/comprosloco$
>
> And I have no idea how to debug it further.  The schema of Log is
> sqlite> .schema oil_log
> CREATE TABLE "oil_log" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
> "packet" integer NOT NULL, "signature" integer NOT NULL, "action"
> varchar(20) NOT NULL, "criteria" varchar(150) NOT NULL, "candidates"
> varchar(100) NOT NULL, "accepted" varchar(10) NOT NULL, "user_id" integer
> NOT NULL REFERENCES "auth_user" ("id"), "timestamp" datetime NOT NULL);
> CREATE INDEX "oil_log_packet_ecd59bc4" ON "oil_log" ("packet");
> CREATE INDEX "oil_log_user_id_7f26e501" ON "oil_log" ("user_id");
> sqlite>
>
>
> Help???
>
>
> --
> Dictionary.com's word of the year: *complicit*
>



-- 
Dictionary.com's word of the year: *complicit*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Broken DB?

[Kevin O'Gorman]

I've got a working site, but I made a copy of the database in order to do
some development work.
I've hit a snag that looks like a problem in the data.

Ive written a management command to show the problem:

from django.core.management.base import BaseCommand, CommandError

# Stuff for the library
from oil.models import Packet, Signature, Log, Voter

class Command(BaseCommand):
help = 'Shows a quick count of validations'
BaseCommand.requires_migrations_checks = True


def handle(self, *args, **options):
voters = Log.objects.all()
self.stdout.write(repr(voters[0]))

I'm suspecting a problem has crept into my Log table, because it works fine
if I change Log on the
second line of handle() to any of the other tables.  If it runs as shown
here however, I get

kevin@camelot-x:/build/comprosloco$ manage oiltest
Traceback (most recent call last):
  File "./manage", line 22, in 
execute_from_command_line(sys.argv)
  File "/build/django/django/core/management/__init__.py", line 364, in
execute_from_command_line
utility.execute()
  File "/build/django/django/core/management/__init__.py", line 356, in
execute
self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/build/django/django/core/management/base.py", line 283, in
run_from_argv
self.execute(*args, **cmd_options)
  File "/build/django/django/core/management/base.py", line 330, in execute
output = self.handle(*args, **options)
  File "/raid3/build/comprosloco/oil/management/commands/oiltest.py", line
15, in handle
self.stdout.write(repr(voters[0]))
  File "/build/django/django/db/models/base.py", line 590, in __repr__
u = six.text_type(self)
  File "/raid3/build/comprosloco/oil/models.py", line 172, in __str__
self.accepted
TypeError: sequence item 0: expected str instance, datetime.datetime found
kevin@camelot-x:/build/comprosloco$

And I have no idea how to debug it further.  The schema of Log is
sqlite> .schema oil_log
CREATE TABLE "oil_log" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"packet" integer NOT NULL, "signature" integer NOT NULL, "action"
varchar(20) NOT NULL, "criteria" varchar(150) NOT NULL, "candidates"
varchar(100) NOT NULL, "accepted" varchar(10) NOT NULL, "user_id" integer
NOT NULL REFERENCES "auth_user" ("id"), "timestamp" datetime NOT NULL);
CREATE INDEX "oil_log_packet_ecd59bc4" ON "oil_log" ("packet");
CREATE INDEX "oil_log_user_id_7f26e501" ON "oil_log" ("user_id");
sqlite>


Help???


-- 
Dictionary.com's word of the year: *complicit*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] File Locking

[David Raymond]

Looks right to me anyway. I'm under the impression that network share locking 
can be sketchy, but you did say "expected".


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Denis Burke
Sent: Friday, April 27, 2018 2:52 PM
To: SQLite mailing list
Subject: [sqlite] File Locking

I have read the excellent page:
https://www.sqlite.org/lockingv3.html

I was hoping someone could clarify/confirm the behavior during the
following steps.

On a Windows PC, if I have a SQLite DB open (using system.data.sqlite if
that is an important fact) over a Windows network share, here are the steps
my application performs and I would appreciate if anyone can comment on
whether the locking status I have placed next to each is what should be
expected.

1. Open connection (Connection.Open() )   **UNLOCKED
2. Create Command object  **UNLOCKED
3. Create Reader object   **UNLOCKED
4. Set reader=Command.ExecuteReader   **SHARED LOCK
5. reader finishes**UNLOCKED
6. close reader   **UNLOCKED
7. destroy reader object  **UNLOCKED
8. Insert record using Command.ExecuteNonQuery  **EXCLUSIVE LOCK
9. Insert finishes**UNLOCKED
10. Destroy command object**UNLOCKED
11. Close Connection  **UNLOCKED
12. Destroy connection object **UNLOCKED

Your insights are appreciated,
Denis Burke
___
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] Yes - Getting SPAM from Using Mailing List

[Denis Burke]

I know steps were taken to reduce it, but just confirming it is still going
on today.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] File Locking

[Denis Burke]

I have read the excellent page:
https://www.sqlite.org/lockingv3.html

I was hoping someone could clarify/confirm the behavior during the
following steps.

On a Windows PC, if I have a SQLite DB open (using system.data.sqlite if
that is an important fact) over a Windows network share, here are the steps
my application performs and I would appreciate if anyone can comment on
whether the locking status I have placed next to each is what should be
expected.

1. Open connection (Connection.Open() )   **UNLOCKED
2. Create Command object  **UNLOCKED
3. Create Reader object   **UNLOCKED
4. Set reader=Command.ExecuteReader   **SHARED LOCK
5. reader finishes**UNLOCKED
6. close reader   **UNLOCKED
7. destroy reader object  **UNLOCKED
8. Insert record using Command.ExecuteNonQuery  **EXCLUSIVE LOCK
9. Insert finishes**UNLOCKED
10. Destroy command object**UNLOCKED
11. Close Connection  **UNLOCKED
12. Destroy connection object **UNLOCKED

Your insights are appreciated,
Denis Burke
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite-dev] Timeout Not Working for Me

[Keith Medcalf]

I have never run into this issue myself.  

However, you do not specify the version or Platform (Windows , Linux  
(plus distribution), Other (Apple iOS, MVS, BSD, etc).
Nor the version of CPython nor from whence it came (preloaded?  version?  
Downloaded and installed yourself?  Built yourself?)
Nor the version of sqlite3 python interface (the one that came with something?  
what thing?)
Nor the version of sqlite3 itself nor its origins (did it come with the 
platform, did you built it yourself, did it come with CPython?)

There have been some issues in the past with some distributions of the sqlite3 
engine on some platforms by some vendors wherein they did not enable build it 
properly (without usleep support, for example) causing all sorts of weirdness.  
There are quite a lot of places for things to go wrong.

That said, using APSW containing sqlite3 (built from the tip of the sqlite.org 
source) on Windows 10 the builtin busywait works properly.  I gave up on using 
the sqlite3 python wrapper long ago -- however it appears to work properly too.

I simply run the standard sqlite3s command shell and lock the database (begin 
immediate) and then fire up the python script in another window.  To release 
the lock one simply does a commit or rollback in the shell.


2018-04-27 10:23:27 PY2 [D:\]
>testwait
Connected in 0.0 seconds
Row(timeout=30)
Row(timeout=30)
2018-04-27 10:23:43.717000
Traceback (most recent call last):
  File "D:\testwait.py", line 19, in 
db.cursor().execute('begin immediate;')
  File "src/cursor.c", line 236, in resetcursor
apsw.BusyError: BusyError: database is locked

2018-04-27 10:29:12 PY2 [D:\]  *** NOTE THE TIME COMPARED TO THE TIME PRINTED 
AT THE START OF THE WAIT ***
>


>testwait
Connected in 0.0 seconds
(30,)
(30,)
2018-04-27 10:42:55.747000
Obtained lock in 12.035858 seconds

>testwait
Connected in 0.0 seconds
(30,)
(30,)
2018-04-27 10:44:00.859000
Obtained lock in 113.22992 seconds

 testwait.py 
from __future__ import absolute_import, division, print_function

import datetime
import time

import apsw
import apswaddins


st = time.time()
db = apsw.Connection('test.db')
print('Connected in', time.time()-st, 'seconds')

for row in db.cursor().execute('pragma busy_timeout=30; pragma 
busy_timeout;'):
print(row)

st = time.time()
print(datetime.datetime.now().isoformat(' '))
db.cursor().execute('begin immediate;')
print('Obtained lock in', time.time()-st, 'seconds')

or using sqlite3

from __future__ import absolute_import, division, print_function

import datetime
import time

import sqlite3


st = time.time()
db = sqlite3.connect('test.db')
print('Connected in', time.time()-st, 'seconds')

for row in db.cursor().execute('pragma busy_timeout=30;'):
print(row)
for row in db.cursor().execute('pragma busy_timeout;'):
print(row)

st = time.time()
print(datetime.datetime.now().isoformat(' '))
db.cursor().execute('begin immediate;')
print('Obtained lock in', time.time()-st, 'seconds')


---
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-dev [mailto:sqlite-dev-boun...@mailinglists.sqlite.org]
>On Behalf Of Brian Bouterse
>Sent: Friday, 27 April, 2018 09:33
>To: sqlite-...@mailinglists.sqlite.org
>Subject: [sqlite-dev] Timeout Not Working for Me
>
>I'm seeing some very strange behavior with our sqlite usage. I have 2
>processes. I know sqlite isn't for multiprocessing, but this a
>timeout issue not a multiprocessing issue. I have my code set the
>busy_timeout early in my connection to 300 seconds (way more than 5
>second default). I do this by running:
>
>cursor.execute('PRAGMA busy_timeout=30;')
>
>
>I then verify that sqlite knows it's set by reading it back
>
>
>cursor.execute('PRAGMA busy_timeout;')
>
>cursor.fetchone() # this shows 30, which tells me the setting was
>set and sqlite knows it
>
>
># symptom
>
>1. Have 2 processes connect to one sqlite db.
>2. Have the first one hold the write lock for > 5 seconds
>
>3. Have the second process try to issue a write
>
>4. Observe after 5 seconds the second process emits a Database is
>Locked error.
>
>5. Wonder why it didn't happen after 300 seconds.
>
>
># my debugging
>
>
>I don't yet have a minimal reproducer outside of Python and Django,
>but I've debugged all the way into sqlite. Here's why I think the
>issue is in sqlite. I debugged the Django code[0][1] and it is being
>passed to Python's connect method correctly. Python's connect method
>(C code [2]) also is doing it right. Finally, I also set it directly
>using a PRAGMA approach and I verify that sqlite itself tells you it
>knows its sets.
>
>
># questions
>
>
>What is the retry behavior specifically? I was reading the sqlite C
>code and I had some trouble understanding how often it is retrying
>and if it's capable of retrying for long periods of time.
>
>
>What do I need to do to 

Re: [sqlite] Sqlite query to get the offset of an entry in the list.

[x]

How about



SELECT ID,NAME,

(SELECT COUNT(*) FROM TABLE WHERE NAME<=(SELECT NAME FROM TABLE WHERE ID=d.ID)) 
as Position

FROM TABLE d

ORDER BY ID;



sqlite> create table t(ID,name text);

sqlite> insert into t values (1,'AAA'),(2,'ZZZ'),(3,'BBB'),(4,'WWW'),(5,'CCC');

sqlite> select ID,name,(select count(*) from t where name<=(select name from t 
where ID=d.ID)) as Posn from t d

order by ID;

1|AAA|1

2|ZZZ|5

3|BBB|2

4|WWW|4

5|CCC|3

sqlite>




From: sqlite-users  on behalf of 
Hegde, Deepakakumar (D.) 
Sent: Friday, April 27, 2018 3:51:27 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Sqlite query to get the offset of an entry in the list.

Hi All,


We have a requirement where in offset of the primary key ID is needed as per 
the sorted list.


Table:


ID   NAME

1  AAA

2  ZZZ

3  BBB

4  WWW

5  CCC


Now need to get the offset of the ID 3 in the sorted list of the NAME.


SELECT * FROM TABLE ORDER BY NAME ASC


1   AAA

3   BBB

5   CCC

4   WWW

2   ZZZ


So position of ID 3 as per the sorted list of the NAME is 2.


currently we are getting the entry with the select statement and by comparing 
the ID externally we are getting the offset.


Is there any optimal way to get this information directly with one single query?


Thanks and Regards

Deepak

___
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 query to get the offset of an entry in the list.

[Keith Medcalf]

Again, requiring that both "id" and "name" are candidate keys.  In which case, 
since there has to be unique indexes to enforce that, one might use the more 
straightforward:

select count(*) from table where name <= (select name from table where id=?) 
order by name;


---
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 x
>Sent: Friday, 27 April, 2018 09:43
>To: SQLite mailing list
>Subject: Re: [sqlite] Sqlite query to get the offset of an entry in
>the list.
>
>SELECT COUNT(*)+1 FROM TABLE WHERE NAME < (SELECT NAME FROM TABLE
>WHERE ID = 3)
>
>
>
>(I think)
>
>
>
>
>From: sqlite-users  on
>behalf of Hegde, Deepakakumar (D.) 
>Sent: Friday, April 27, 2018 3:51:27 PM
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Sqlite query to get the offset of an entry in the
>list.
>
>Hi All,
>
>
>We have a requirement where in offset of the primary key ID is needed
>as per the sorted list.
>
>
>Table:
>
>
>ID   NAME
>
>1  AAA
>
>2  ZZZ
>
>3  BBB
>
>4  WWW
>
>5  CCC
>
>
>Now need to get the offset of the ID 3 in the sorted list of the
>NAME.
>
>
>SELECT * FROM TABLE ORDER BY NAME ASC
>
>
>1   AAA
>
>3   BBB
>
>5   CCC
>
>4   WWW
>
>2   ZZZ
>
>
>So position of ID 3 as per the sorted list of the NAME is 2.
>
>
>currently we are getting the entry with the select statement and by
>comparing the ID externally we are getting the offset.
>
>
>Is there any optimal way to get this information directly with one
>single query?
>
>
>Thanks and Regards
>
>Deepak
>
>___
>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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite query to get the offset of an entry in the list.

[Keith Medcalf]

The constraint, obviously, being that "id" and "name" are each candidate keys 
...

---
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 Simon Davies
>Sent: Friday, 27 April, 2018 09:35
>To: SQLite mailing list
>Subject: Re: [sqlite] Sqlite query to get the offset of an entry in
>the list.
>
>On 27 April 2018 at 15:51, Hegde, Deepakakumar (D.)
> wrote:
>> Hi All,
>>
>> We have a requirement where in offset of the primary key ID is
>needed as per the sorted list.
>.
>.
>.
>> 1   AAA
>> 3   BBB
>> 5   CCC
>> 4   WWW
>> 2   ZZZ
>>
>> So position of ID 3 as per the sorted list of the NAME is 2.
>
>sqlite> create table t( id integer primary key, data text );
>sqlite> insert into t( data ) values('aaa'), ('zzz'), ('bbb'),
>('www'), ('ccc');
>sqlite>
>sqlite> select count(*)+1 from t where data<(select data from t where
>id=3);
>2
>
>> Thanks and Regards
>> Deepak
>
>Rgds,
>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] Sqlite query to get the offset of an entry in the list.

[Keith Medcalf]

create table data (id integer primary key, name text);
insert into data (name) values ('AAA'), ('ZZZ'), ('BBB'), ('WWW'), ('CCC');

select * from data;
1|AAA
2|ZZZ
3|BBB
4|WWW
5|CCC

select * from data order by name;
1|AAA
3|BBB
5|CCC
4|WWW
2|ZZZ

create table temp.ranked as select * from data order by name;

select rowid, * from temp.ranked;
1|1|AAA
2|3|BBB
3|5|CCC
4|4|WWW
5|2|ZZZ

select rowid from temp.ranked where id = 3;
2

drop table temp.ranked;


There is likely a way to do this using a recursive CTE without a temp table, 
however, I cannot do that off the top of my mind immediately as there are too 
many possible constraints.  Someone else may have already thought about how to 
do that.  There are lots of solutions with various "constraints" and 
"assumptions" about the data though. This one happens to not require any such 
assumptions or constraints ... If you, for example, constrained the name column 
to be unique, then there exists a much simpler solution.

Whether or not the sequence:

begin immediate;
drop table if exists temp.ranked;
create table temp.ranked as select * from data order by name;
select rowid from temp.ranked where id = 3;
drop table if exists temp.ranked;
rollback;

constitutes a "single sql statement" depends on how you are interfacing with 
SQLite.  For me, it is a single statement returning a single row.  YMMV.


However, my question would be why you need to know the offset of the ID in the 
sorted set of results as that seems like a "navigational" problem rather than a 
"relational" problem?  Are the "requirements" perhaps geared to file or 
hierachical database rather than a relational database?  (It is quite common 
for "navigational" problems to be stated in requirements for "relational" 
implementations.  This is why over the years there have been many functions 
added to the "monster" relational engines -- because it is easier to add a 
"MakeCoffee()" function than it is to argue that a relational database should 
not have a "MakeCoffee" function.  The epitome of this is of course Oracle, 
which has a function for everything you could ever possibly want to do.)


---
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 Hegde, Deepakakumar
>(D.)
>Sent: Friday, 27 April, 2018 08:51
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Sqlite query to get the offset of an entry in the
>list.
>
>Hi All,
>
>
>We have a requirement where in offset of the primary key ID is needed
>as per the sorted list.
>
>
>Table:
>
>
>ID   NAME
>
>1  AAA
>
>2  ZZZ
>
>3  BBB
>
>4  WWW
>
>5  CCC
>
>
>Now need to get the offset of the ID 3 in the sorted list of the
>NAME.
>
>
>SELECT * FROM TABLE ORDER BY NAME ASC
>
>
>1   AAA
>
>3   BBB
>
>5   CCC
>
>4   WWW
>
>2   ZZZ
>
>
>So position of ID 3 as per the sorted list of the NAME is 2.
>
>
>currently we are getting the entry with the select statement and by
>comparing the ID externally we are getting the offset.
>
>
>Is there any optimal way to get this information directly with one
>single query?
>
>
>Thanks and Regards
>
>Deepak
>
>___
>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 query to get the offset of an entry in the list.

[x]

SELECT COUNT(*)+1 FROM TABLE WHERE NAME < (SELECT NAME FROM TABLE WHERE ID = 3)



(I think)




From: sqlite-users  on behalf of 
Hegde, Deepakakumar (D.) 
Sent: Friday, April 27, 2018 3:51:27 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Sqlite query to get the offset of an entry in the list.

Hi All,


We have a requirement where in offset of the primary key ID is needed as per 
the sorted list.


Table:


ID   NAME

1  AAA

2  ZZZ

3  BBB

4  WWW

5  CCC


Now need to get the offset of the ID 3 in the sorted list of the NAME.


SELECT * FROM TABLE ORDER BY NAME ASC


1   AAA

3   BBB

5   CCC

4   WWW

2   ZZZ


So position of ID 3 as per the sorted list of the NAME is 2.


currently we are getting the entry with the select statement and by comparing 
the ID externally we are getting the offset.


Is there any optimal way to get this information directly with one single query?


Thanks and Regards

Deepak

___
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 query to get the offset of an entry in the list.

[Simon Davies]

On 27 April 2018 at 15:51, Hegde, Deepakakumar (D.)
 wrote:
> Hi All,
>
> We have a requirement where in offset of the primary key ID is needed as per 
> the sorted list.
.
.
.
> 1   AAA
> 3   BBB
> 5   CCC
> 4   WWW
> 2   ZZZ
>
> So position of ID 3 as per the sorted list of the NAME is 2.

sqlite> create table t( id integer primary key, data text );
sqlite> insert into t( data ) values('aaa'), ('zzz'), ('bbb'), ('www'), ('ccc');
sqlite>
sqlite> select count(*)+1 from t where data<(select data from t where id=3);
2

> Thanks and Regards
> Deepak

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

[sqlite] Sqlite query to get the offset of an entry in the list.

[Hegde, Deepakakumar (D.)]

Hi All,


We have a requirement where in offset of the primary key ID is needed as per 
the sorted list.


Table:


ID   NAME

1  AAA

2  ZZZ

3  BBB

4  WWW

5  CCC


Now need to get the offset of the ID 3 in the sorted list of the NAME.


SELECT * FROM TABLE ORDER BY NAME ASC


1   AAA

3   BBB

5   CCC

4   WWW

2   ZZZ


So position of ID 3 as per the sorted list of the NAME is 2.


currently we are getting the entry with the select statement and by comparing 
the ID externally we are getting the offset.


Is there any optimal way to get this information directly with one single query?


Thanks and Regards

Deepak

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

Re: [sqlite] XOR operator

[fredericDelaporte]

R Smith wrote
> it would be nice to have 
> an XOR operator in the way that & and | work...  Is there any character 
> left to use, or one that are regularly used as XOR in other languages?  
> The $ sign seems to be free, but it would be nice to conform to some 
> standard. Perhaps a combined "<|" or such.

SQL-Server uses ^ for xor, PostgreSQL uses #, Firebird uses a function named
bin_xor, ... In HQL (at least the NHibernate HQL), it uses ^ for xor and by
default output ^ into resulting SQL.



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