date:20170327

Re: [sqlite] change ORDER BY slowly

2017-03-27 Thread Keith Medcalf


If you really want to filter as you have specified, then it will take a while.  
This is because the entire query must be performed (up to the point you are 
applying the order), then sorted, then the top rows selected to match you 
limit, then any remaining outer joins performed.

Or you can order by the "other side" of your join constraint (bm_ppal.nbmCalle) 
but then you are not guaranteed to get results with no match first (since NULL 
sorts before any value).  You can do this by putting an exists in the where 
clause to select records that DO NOT match the join constraint -- ie, where not 
exists) (for your limit of records), UNION with those that do (ie, an equijoin) 
(for your limit of records), and then sort (order by) and limit the result.  
This should only take about twice (or maybe three) times as long since you are 
in effect running the same query twice with different criterion placed on the 
outer table only.  Some additional time will be used for the extra exists test 
and for the final sort and limit.  But you will not be generating a 750,000 row 
table to sort and filter either -- you will only be generating one twice the 
size of what you want then sorting and limiting it even more.

eg:

select a, b
from
(
   select a, b
 from x
left join y on x.a = y.a
where not exists (select 1 from y where a = x.a)
order by x.a
limit 57
)
UNION
(
   select a, b
 from x
 join y on x.a = y.a
order by x.a
limit 57
)
order by b
limit 57;

> move the join to immediately follow the FROM clause
> remove the word "left"
> 
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of MONSTRUO Hugo González
> > Sent: Monday, 27 March, 2017 09:35
> > To: sqlite-users@mailinglists.sqlite.org
> > Subject: [sqlite] change ORDER BY slowly
> >
> > Hi,
> >
> > I have a phone book (bm_ppal), 726.000 rows, 10 columns
> >
> > This phone book have this columns
> > Name Declared Type Type Size
> > nbmId INTEGER INTEGER
> > nbmCodigo VARCHAR (6) VARCHAR 6
> > abmNombre VARCHAR (320) VARCHAR 320
> > abmNombrePlano VARCHAR (320) VARCHAR 320
> > nbmCiudad INTEGER INTEGER
> > nbmTelefono VARCHAR (9) VARCHAR 9
> > nbmCalle INTEGER INTEGER
> > nbmNroPuerta VARCHAR (5) VARCHAR
> > nbmLongitud VARCHAR (5) VARCHAR
> > nbmLatitud VARCHAR (5) VARCHAR
> >
> > I use c# winform datagridview virtualmode
> >
> > The file.sqlite is in: https://1drv.ms/u/s!Ar9mkZ1CPa2WhkUSJLxLI0WKV_TP
> >
> > I have this sqlite sentence for read the file, very fast: 157 ms
> >
> > SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
> > bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,
> >
> > bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
> > bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
> >  FROM bm_ppal
> >  LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
> >  LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
> >  LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
> >  ORDER BY bm_ppal.nbmId
> >  LIMIT 512
> >  OFFSET 0
> > 157ms
> >
> > I have this sqlite sentence for read the file, change the ORDER BY, very
> > fast: 135ms
> >
> > SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
> > bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,
> >
> > bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
> > bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
> >  FROM bm_ppal
> >  LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
> >  LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
> >  LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
> >  ORDER BY bm_ppal.abmNombre, bm_ppal.nbmId
> >  LIMIT 512
> >  OFFSET 0
> > 135ms
> >
> > but when I change the ORDER BY with an attributt of other table is very
> > slowly: 5699 ms
> >
> > SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
> > bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,
> >
> > bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
> > bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
> >  FROM bm_ppal
> >  LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
> >  LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
> >  LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
> >  ORDER BY bm_calles.abmNombre
> >  LIMIT 512
> >  OFFSET 0
> > 5699ms ??
> >
> > I create the index with:
> >
> > CREATE INDEX IF NOT EXISTS idxbm_calles2 ON bm_calles(abmNombre);
> >
> > where I create an index in the table bm_ppal for bm_calles(abmNombre)
> with
> > the LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
> >
> > How I can optimize it ?
> > ___
> > 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

Re: [sqlite] change ORDER BY slowly

2017-03-27 Thread Keith Medcalf


move the join to immediately follow the FROM clause
remove the word "left"

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of MONSTRUO Hugo González
> Sent: Monday, 27 March, 2017 09:35
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] change ORDER BY slowly
> 
> Hi,
> 
> I have a phone book (bm_ppal), 726.000 rows, 10 columns
> 
> This phone book have this columns
> Name Declared Type Type Size
> nbmId INTEGER INTEGER
> nbmCodigo VARCHAR (6) VARCHAR 6
> abmNombre VARCHAR (320) VARCHAR 320
> abmNombrePlano VARCHAR (320) VARCHAR 320
> nbmCiudad INTEGER INTEGER
> nbmTelefono VARCHAR (9) VARCHAR 9
> nbmCalle INTEGER INTEGER
> nbmNroPuerta VARCHAR (5) VARCHAR
> nbmLongitud VARCHAR (5) VARCHAR
> nbmLatitud VARCHAR (5) VARCHAR
> 
> I use c# winform datagridview virtualmode
> 
> The file.sqlite is in: https://1drv.ms/u/s!Ar9mkZ1CPa2WhkUSJLxLI0WKV_TP
> 
> I have this sqlite sentence for read the file, very fast: 157 ms
> 
> SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
> bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,
> 
> bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
> bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
>  FROM bm_ppal
>  LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
>  LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
>  LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
>  ORDER BY bm_ppal.nbmId
>  LIMIT 512
>  OFFSET 0
> 157ms
> 
> I have this sqlite sentence for read the file, change the ORDER BY, very
> fast: 135ms
> 
> SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
> bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,
> 
> bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
> bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
>  FROM bm_ppal
>  LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
>  LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
>  LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
>  ORDER BY bm_ppal.abmNombre, bm_ppal.nbmId
>  LIMIT 512
>  OFFSET 0
> 135ms
> 
> but when I change the ORDER BY with an attributt of other table is very
> slowly: 5699 ms
> 
> SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
> bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,
> 
> bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
> bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
>  FROM bm_ppal
>  LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
>  LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
>  LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
>  ORDER BY bm_calles.abmNombre
>  LIMIT 512
>  OFFSET 0
> 5699ms ??
> 
> I create the index with:
> 
> CREATE INDEX IF NOT EXISTS idxbm_calles2 ON bm_calles(abmNombre);
> 
> where I create an index in the table bm_ppal for bm_calles(abmNombre) with
> the LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
> 
> How I can optimize it ?
> ___
> 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] change ORDER BY slowly

2017-03-27 Thread David Raymond

The main issue there I believe is that the order by/limit for the first two is 
on the outermost table, so it can order by/limit that right away. In the last 
query the order by/limit is on a table in the middle, so it can't order or 
filter on it right away, and needs the temp tree there.

I saw a small speed increase by putting the bm_calles join as the first one, 
doing the sort/limit after that 1 join, then doing the rest of the joins. But 
that was only 10% or so. So I'm not sure how best to optimize that. If they 
were inner joins then it could do it in any order it wanted, and thus be just 
as fast. But with the outer joins then I'm not sure.

Also note that "unique primary key" is redundant and creates an extra unneeded 
index.


Your first query (ORDER BY bm_ppal.nbmId  LIMIT 512):
selectid|order|from|detail
0|0|0|SCAN TABLE bm_ppal
0|1|1|SEARCH TABLE ciudad USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE bm_calles USING INTEGER PRIMARY KEY (rowid=?)
0|3|3|SEARCH TABLE depto USING INTEGER PRIMARY KEY (rowid=?)


Your second query (ORDER BY bm_ppal.abmNombre, bm_ppal.nbmId LIMIT 512):
selectid|order|from|detail
0|0|0|SCAN TABLE bm_ppal USING INDEX idxbm_ppal2
0|1|1|SEARCH TABLE ciudad USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE bm_calles USING INTEGER PRIMARY KEY (rowid=?)
0|3|3|SEARCH TABLE depto USING INTEGER PRIMARY KEY (rowid=?)


Your third (slow) query (ORDER BY bm_calles.abmNombre LIMIT 512):
selectid|order|from|detail
0|0|0|SCAN TABLE bm_ppal
0|1|1|SEARCH TABLE ciudad USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE bm_calles USING INTEGER PRIMARY KEY (rowid=?)
0|3|3|SEARCH TABLE depto USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|USE TEMP B-TREE FOR ORDER BY


(Not very helpful) Reordering (To make use of the limit as soon as possible):
selectid|order|from|detail
1|0|0|SCAN TABLE bm_ppal
1|1|1|SEARCH TABLE bm_calles USING INTEGER PRIMARY KEY (rowid=?)
1|0|0|USE TEMP B-TREE FOR ORDER BY
0|0|0|SCAN SUBQUERY 1 AS tmp
0|1|1|SEARCH TABLE ciudad USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE depto USING INTEGER PRIMARY KEY (rowid=?)


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of MONSTRUO Hugo González
Sent: Monday, March 27, 2017 11:35 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] change ORDER BY slowly

Hi,

I have a phone book (bm_ppal), 726.000 rows, 10 columns

This phone book have this columns
Name Declared Type Type Size
nbmId INTEGER INTEGER
nbmCodigo VARCHAR (6) VARCHAR 6
abmNombre VARCHAR (320) VARCHAR 320
abmNombrePlano VARCHAR (320) VARCHAR 320
nbmCiudad INTEGER INTEGER
nbmTelefono VARCHAR (9) VARCHAR 9
nbmCalle INTEGER INTEGER
nbmNroPuerta VARCHAR (5) VARCHAR
nbmLongitud VARCHAR (5) VARCHAR
nbmLatitud VARCHAR (5) VARCHAR

I use c# winform datagridview virtualmode

The file.sqlite is in: https://1drv.ms/u/s!Ar9mkZ1CPa2WhkUSJLxLI0WKV_TP

I have this sqlite sentence for read the file, very fast: 157 ms

SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,

bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
 FROM bm_ppal
 LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
 LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
 LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
 ORDER BY bm_ppal.nbmId
 LIMIT 512
 OFFSET 0
157ms

I have this sqlite sentence for read the file, change the ORDER BY, very
fast: 135ms

SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,

bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
 FROM bm_ppal
 LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
 LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
 LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
 ORDER BY bm_ppal.abmNombre, bm_ppal.nbmId
 LIMIT 512
 OFFSET 0
135ms

but when I change the ORDER BY with an attributt of other table is very
slowly: 5699 ms

SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,

bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
 FROM bm_ppal
 LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
 LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
 LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
 ORDER BY bm_calles.abmNombre
 LIMIT 512
 OFFSET 0
5699ms ??

I create the index with:

CREATE INDEX IF NOT EXISTS idxbm_calles2 ON bm_calles(abmNombre);

where I create an index in the table bm_ppal for bm_calles(abmNombre) with
the LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle

How I can optimize it ?
___
sqlite-users mailing list

Re: [sqlite] How does one block a reader connection?

2017-03-27 Thread Jens Alfke

> On Mar 26, 2017, at 11:37 PM, Hick Gunter  wrote:
> 
> I think this kind of problem (transfer of information between cooperating 
> processes) is best solved using the tools designed for inter-process 
> communication and not attempting to abuse a DB system designed to isolate 
> processes from unfinished changes.

Generally I agree … but to play devil’s advocate: SQLite already has a degree 
of interprocess communication using shared memory (the “-shm” file). It might 
be simple to extend this to provide a very lightweight change counter, by 
atomically incrementing an integer field in this shared memory. A process could 
then poll this with almost no overhead.

(I’m sure the answer is “implement it and send a patch”, which is as it should 
be. I don’t have any experience with the SQLite source base, though, so it 
would probably take me longer to get up to speed on that, than it would for me 
to reach for an IPC tool I already know how to use, like CFNotificationCenter. 
Oh well.)

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

Re: [sqlite] change ORDER BY slowly

2017-03-27 Thread Simon Slavin


On 27 Mar 2017, at 4:35pm, MONSTRUO Hugo González  
wrote:

> nbmCodigo VARCHAR (6) VARCHAR 6

I wanted to note that SQLite completely ignores VARCHAR and any size limits.  
As far as SQLite is concerned, all those fields are TEXT fields and can contain 
any number of characters.  If your code is assuming that SQLite will truncate 
your Codigo field to six character you might want to take another look.

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

[sqlite] change ORDER BY slowly

2017-03-27 Thread MONSTRUO Hugo González

Hi,

I have a phone book (bm_ppal), 726.000 rows, 10 columns

This phone book have this columns
Name Declared Type Type Size
nbmId INTEGER INTEGER
nbmCodigo VARCHAR (6) VARCHAR 6
abmNombre VARCHAR (320) VARCHAR 320
abmNombrePlano VARCHAR (320) VARCHAR 320
nbmCiudad INTEGER INTEGER
nbmTelefono VARCHAR (9) VARCHAR 9
nbmCalle INTEGER INTEGER
nbmNroPuerta VARCHAR (5) VARCHAR
nbmLongitud VARCHAR (5) VARCHAR
nbmLatitud VARCHAR (5) VARCHAR

I use c# winform datagridview virtualmode

The file.sqlite is in: https://1drv.ms/u/s!Ar9mkZ1CPa2WhkUSJLxLI0WKV_TP

I have this sqlite sentence for read the file, very fast: 157 ms

SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,

bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
 FROM bm_ppal
 LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
 LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
 LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
 ORDER BY bm_ppal.nbmId
 LIMIT 512
 OFFSET 0
157ms

I have this sqlite sentence for read the file, change the ORDER BY, very
fast: 135ms

SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,

bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
 FROM bm_ppal
 LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
 LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
 LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
 ORDER BY bm_ppal.abmNombre, bm_ppal.nbmId
 LIMIT 512
 OFFSET 0
135ms

but when I change the ORDER BY with an attributt of other table is very
slowly: 5699 ms

SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,

bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
 FROM bm_ppal
 LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
 LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
 LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
 ORDER BY bm_calles.abmNombre
 LIMIT 512
 OFFSET 0
5699ms ??

I create the index with:

CREATE INDEX IF NOT EXISTS idxbm_calles2 ON bm_calles(abmNombre);

where I create an index in the table bm_ppal for bm_calles(abmNombre) with
the LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle

How I can optimize it ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What are the rules for defining names for tables and columns?

2017-03-27 Thread em...@n0code.net

:)  Roger that.

> On Mar 27, 2017, at 10:58 AM, Hick Gunter  wrote:
> 
>> -Ursprüngliche Nachricht-
>> em...@n0code.net wrote:
>>> I’ve scoured the archives and the sqlite documentation but can’t find
>>> the definitive rules for defining table and column names.
>> 
>> Everything is allowed, except names beginning with "sqlite_".
>> 
> 
> This calls for a (not quite) OT Quote: 1. Corinthians Chapter 6 Verse 12:
> KJV "All things are lawful unto me, but all things are not expedient"
> NIV "I have the right to do anything,” you say—but not everything is 
> beneficial."
> 
> 
> ___
> Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
> 
> This communication (including any attachments) is intended for the use of the 
> intended recipient(s) only and may contain information that is confidential, 
> privileged or legally protected. Any unauthorized use or dissemination of 
> this communication is strictly prohibited. If you have received this 
> communication in error, please immediately notify the sender by return e-mail 
> message and delete all copies of the original communication. Thank you for 
> your cooperation.
> 
> 
> ___
> 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] What are the rules for defining names for tables and columns?

2017-03-27 Thread Hick Gunter

>-Ursprüngliche Nachricht-
>em...@n0code.net wrote:
>> I’ve scoured the archives and the sqlite documentation but can’t find
>> the definitive rules for defining table and column names.
>
>Everything is allowed, except names beginning with "sqlite_".
>

This calls for a (not quite) OT Quote: 1. Corinthians Chapter 6 Verse 12:
KJV "All things are lawful unto me, but all things are not expedient"
NIV "I have the right to do anything,” you say—but not everything is 
beneficial."

___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

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

Re: [sqlite] What are the rules for defining names for tables and columns?

2017-03-27 Thread em...@n0code.net

Wow!  Ok - awesome!  Thanks!


> On Mar 27, 2017, at 10:45 AM, Clemens Ladisch  wrote:
> 
> em...@n0code.net wrote:
>> I’ve scoured the archives and the sqlite documentation but can’t find the 
>> definitive rules
>> for defining table and column names.
> 
> Everything is allowed, except names beginning with "sqlite_".
> 
>> I did see we can’t use keywords
> 
> The documentation disagrees: http://www.sqlite.org/lang_keywords.html
> 
> CREATE TABLE "TABLE"("#!@""'☺\", "");  -- keyword, special characters, empty 
> name
> 
> 
> Regards,
> Clemens
> ___
> 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] What are the rules for defining names for tables and columns?

2017-03-27 Thread Clemens Ladisch

em...@n0code.net wrote:
> I’ve scoured the archives and the sqlite documentation but can’t find the 
> definitive rules
> for defining table and column names.

Everything is allowed, except names beginning with "sqlite_".

> I did see we can’t use keywords

The documentation disagrees: http://www.sqlite.org/lang_keywords.html

CREATE TABLE "TABLE"("#!@""'☺\", "");  -- keyword, special characters, empty 
name


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

Re: [sqlite] 3.17.0 bug report: FTS5 insertion puts a wrong value in last_insert_rowid

2017-03-27 Thread Gwendal Roué


* Florian Weimer  wrote:

> * Gwendal Roué:
> 
>> I have found a regression in SQLite 3.17.0. In the following SQL statements:
>> 
>>CREATE VIRTUAL TABLE t1 USING FTS5(content);
>>INSERT INTO t1(content) VALUES ('some text');
>>SELECT last_insert_rowid(); // 10 (wrong)
>>SELECT rowid FROM t1;   // 1
>> 
>> The expected value of the the SQL function last_insert_rowid()
>> function is 1, not 10. Same for the C function
>> sqlite3_last_insert_rowid().
> 
> I think this is a known issue.

I am not sure this is a known issue: I don't find it in the tickets list 
(http://www.sqlite.org/src/reportlist).

> SQLite 3.18 adds a
> sqlite3_set_last_insert_rowid() function and uses it in “the new
> interface in the FTS3, FTS4, and FTS5 extensions to ensure that the
> sqlite3_last_insert_rowid() interface always returns reasonable
> values”.

The pending SQLite 3.18 indeed looks like it addresses this issue!

Gwendal Roué


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

[sqlite] What are the rules for defining names for tables and columns?

2017-03-27 Thread em...@n0code.net

Hi,

I’ve scoured the archives and the sqlite documentation but can’t find the 
definitive rules
for defining table and column names.

I did see we can’t use keywords and tables can not begin with sqlite_.  Are 
there others?
What characters can we use?  Is it the same as MySQL?

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

Re: [sqlite] How does one block a reader connection?

2017-03-27 Thread Hick Gunter

Hey, neat idea! To expand on my previous post:

CREATE TRIGGER wakeup AFTER INSERT ON cmd BEGIN SELECT 
cond_broadcast('cmd_ready'); END;

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Richard Damon
Gesendet: Sonntag, 26. März 2017 03:43
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] How does one block a reader connection?

On 3/25/17 6:52 PM, petern wrote:
> I would like to construct a SQLite database for one writer and one or
> more reader connections.  The writer will be updating various data
> tables which occasionally trigger one or more row inserts in a command
> table named 'cmd'.  This command table is being polled by the readers for new 
> commands.
>
> To make the example more concrete, suppose the following DDL defines
> the command table:
>
> CREATE TABLE cmd(opcode TEXT, params TEXT);
>
> Also assume each reader is in a different process which maintains its
> own open db connection over which it periodically executes the
> following command retrieval query,
>
> SELECT * FROM cmd WHERE rowid>=$lastCmdRowid;
>
> where $lastCmdRowid is a variable managed by the reader to keep track
> of the last command that was seen from its point of view.  [FYI yes,
> of course there are other time stamp bookkeeping columns and variables
> that I've omitted because they don't pertain this question.]
>
> So finally, here is the question.  Is there a SQLite API way for
> reader connections to block and wait for a meaningful change, like a
> new row, in the 'cmd' table instead of madly polling and using up
> database concurrency resources?  [Block with timeout would be even
> more desirable of course.]
I don't know SQLite well enough to say for certain, but to my knowledge 
'blocking till something happens' isn't the sort of thing the SQL vocabulary 
has. (There is the concept of a trigger, so you might be able to get something 
to run when the insert happens but that is different than blocking to something 
happens).

My best guess is that you want to use a real synchronization primitive outside 
of SQLite sent by the writer, (or maybe you can put it into a
trigger) to hold off the reader, and then have it read the work that was queued 
up.


--
Richard Damon

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] How does one block a reader connection?

2017-03-27 Thread Hick Gunter

I think this kind of problem (transfer of information between cooperating 
processes) is best solved using the tools designed for inter-process 
communication and not attempting to abuse a DB system designed to isolate 
processes from unfinished changes.

Have the processes share a condition variable. The readers block on the 
condition variable (with a timeout if desired, to guard against dropped 
signals); the writer broadcasts the variable, waking up the readers which then 
proceed to retrieve/process any new entries.

If you are really desperate, you could write use defined functions/virtual 
table to do this over the SQL interface, e.g.

For creating a virtual table (allows keeping/querying housekeeping info about 
condition variables)

CREATE VIRTUAL TABLE conditions USING posix_cond; -- allocate/connect to the 
shared memory of your choice

INSERT INTO conditions (name) value ('cmd_ready'); -- create a condition 
variable

Or just for creating with minimal housekeeping:

SELECT cond_init('cmd_readY');

And in both cases:

SELECT cond_wait('cmd_ready' [,]); -- wait for condition [or timeout], 
returns 1 for signal received, 0 for error/timeout

SELECT cond_broadcast('cmd_ready'); -- wake up readers

Gunter

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von petern
Gesendet: Samstag, 25. März 2017 23:52
An: SQLite mailing list 
Betreff: [sqlite] How does one block a reader connection?

I would like to construct a SQLite database for one writer and one or more 
reader connections.  The writer will be updating various data tables which 
occasionally trigger one or more row inserts in a command table named 'cmd'.  
This command table is being polled by the readers for new commands.

To make the example more concrete, suppose the following DDL defines the 
command table:

CREATE TABLE cmd(opcode TEXT, params TEXT);

Also assume each reader is in a different process which maintains its own open 
db connection over which it periodically executes the following command 
retrieval query,

SELECT * FROM cmd WHERE rowid>=$lastCmdRowid;

where $lastCmdRowid is a variable managed by the reader to keep track of the 
last command that was seen from its point of view.  [FYI yes, of course there 
are other time stamp bookkeeping columns and variables that I've omitted 
because they don't pertain this question.]

So finally, here is the question.  Is there a SQLite API way for reader 
connections to block and wait for a meaningful change, like a new row, in the 
'cmd' table instead of madly polling and using up database concurrency 
resources?  [Block with timeout would be even more desirable of course.] 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] change ORDER BY slowly

Re: [sqlite] change ORDER BY slowly

Re: [sqlite] change ORDER BY slowly

Re: [sqlite] How does one block a reader connection?

Re: [sqlite] change ORDER BY slowly

[sqlite] change ORDER BY slowly

Re: [sqlite] What are the rules for defining names for tables and columns?

Re: [sqlite] What are the rules for defining names for tables and columns?

Re: [sqlite] What are the rules for defining names for tables and columns?

Re: [sqlite] What are the rules for defining names for tables and columns?

Re: [sqlite] 3.17.0 bug report: FTS5 insertion puts a wrong value in last_insert_rowid

[sqlite] What are the rules for defining names for tables and columns?

Re: [sqlite] How does one block a reader connection?

Re: [sqlite] How does one block a reader connection?

14 matches

Site Navigation

Mail list logo

Footer information