Re: [sqlite] "Common Table Expression"

[Keith Medcalf]

>>> (2) What about cycles? How does one deal with them?

>> With cycles, you probably want to use UNION instead of UNION ALL to
>> stop at duplicate records.

>Hmmm... not quite sure how this would play out in practice... how would the
>recursion known when to stop?

>Say,  given a circular hierarchy such as A-> B -> A when does the
>recursion stop?

UNION ALL means union all rows, even duplicates.
UNION means union all DISTINCT rows.

So it is not so much a matter of stopping as of not adding (hence recursing) 
from row A once it has already been seen.

Read the docs.  It explains how recursive CTEs are computed and how UNION ALL 
vs UNION work in CTEs.




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

[sqlite] Unicode support in SQLite

[Aleksey Tulinov]

Hey,

I've just updated nunicode to version 1.2: 
https://bitbucket.org/alekseyt/nunicode


Now all collations are backed by reduced DUCET. Library grew in size a 
little bit, you'll get Unicode collations for around 200Kb, but at the 
same time you will also get several languages completely working out of 
the box as they don't need any collation tailoring.


You can also write your own tailoring, this is somewhat described here: 
https://bitbucket.org/alekseyt/nunicode#markdown-header-custom-collations and 
also covered in embedded Doxygen doc.

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

Re: [sqlite] "Common Table Expression"

[James K. Lowden]

On Fri, 24 Jan 2014 23:51:11 +0100
Petite Abeille  wrote:

> > It's exactly the same as "SELECT ?", but a little bit easier to
> > write. (It behaves like with INSERT, but is now available in every
> > place where a SELECT would be allowed.)
> 
> Hmmm? seems rather pointless to me.
> 
> select 1 as value /* from thin air */ union all
> select 2 as value /* from thin air */ 
> ? etc ?
> 
> Seems to be good enough. No point in hijacking a totally unrelated
> construct. I would drop such complication if I had a say. There is
> already a perfectly fine construct to conjure constants out of thin
> air: select. 

VALUES is a row constructor.  You should be able to do 

VALUES ( ( 'a', 'b', 'c' ) , ('d', 'e', 'f') ) 
as ( A, B, C ) as T

which is a little more direct than a union of SELECTs.  You should also
eventually be able to say

WHERE ( A.a, A.b ) = values ( 'a', 'b' )
or 
FROM T MINUS VALUES ('a', 'b')

and such.  Gets you to more of a row-based way of dealing with the
data. 

Funny, we find ourselves on the opposite side of the compexity question
this time. 

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

Re: [sqlite] "Common Table Expression"

[Petite Abeille]

On Jan 24, 2014, at 11:32 PM, Clemens Ladisch  wrote:

> It's exactly the same as "SELECT …", but a little bit easier to write.
> (It behaves like with INSERT, but is now available in every place
> where a SELECT would be allowed.)

Hmmm… seems rather pointless to me.

select 1 as value /* from thin air */ union all
select 2 as value /* from thin air */ 
… etc …

Seems to be good enough. No point in hijacking a totally unrelated construct. I 
would drop such complication if I had a say. There is already a perfectly fine 
construct to conjure constants out of thin air: select. 

> 
>> (2) What about cycles? How does one deal with them?
> 
> With cycles, you probably want to use UNION instead of UNION ALL to
> stop at duplicate records.

Hmmm… not quite sure how this would play out in practice… how would the 
recursion known when to stop?

Say,  given a circular hierarchy such as A-> B -> A…. when does the recursion 
stop?



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

Re: [sqlite] "Common Table Expression"

[Clemens Ladisch]

Petite Abeille wrote:
> On Jan 24, 2014, at 2:31 PM, Richard Hipp  wrote:
>> Please see http://www.sqlite.org/draft/lang_with.html for draft
>> documentation of the new Common Table Expression implementation for SQLite
>> 3.8.3.  Comments, criticism, and typo-corrections are appreciated.
>
> (1) What is this VALUES( … ) construct? What purpose does it serves?

It's exactly the same as "SELECT …", but a little bit easier to write.
(It behaves like with INSERT, but is now available in every place
where a SELECT would be allowed.)

> (2) What about cycles? How does one deal with them?

With cycles, you probably want to use UNION instead of UNION ALL to
stop at duplicate records.


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

Re: [sqlite] "Common Table Expression"

[Petite Abeille]

On Jan 24, 2014, at 2:31 PM, Richard Hipp  wrote:

> Please see http://www.sqlite.org/draft/lang_with.html for draft
> documentation of the new Common Table Expression implementation for SQLite
> 3.8.3.  Comments, criticism, and typo-corrections are appreciated.

(1) What is this VALUES( … ) construct? What purpose does it serves? 

(2) What about cycles? How does one deal with them?

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

Re: [sqlite] SELECTing from another SELECT

[jose isaias cabrera]

"Igor Tandetnik" wrote on Friday, January 24, 2014 9:48 AM...



On 1/24/2014 9:28 AM, jose isaias cabrera wrote:


Igor Tandetnik wrote...

On 1/23/2014 2:26 PM, St. B. wrote:

SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask =
'a');

will probably fill the bill.

If I where to run your query, I would do a
select A.* from A inner join B on A.a = b.ProjId where b.ptask='a'


Careful - this will produce a different result if B has multiple rows
with the same ptask and projid. May or may not be a concern in the
OP's case.


What would be the correct inner join syntax to provide the same output as
the original select?  I like that shorter syntax.  Will any of these
will be
faster?  Thanks so much.


"SELECT DISTINCT ..." perhaps. But that would kill any alleged performance 
improvement that a join may have over IN (if any; personally, I'm not sure 
it's necessarily true that the join would perform better in the first 
place).


You are right, Igor. Your original IN command is much faster.  It instantly
responds, while the INNER JOIN takes a few second before responding.
Thanks.

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

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[Max Vlasov]

On Fri, Jan 24, 2014 at 9:16 PM, Ralf Junker  wrote:
> On 24.01.2014 10:06, Max Vlasov wrote:
>
>> BCC 5.5 (freely downloadable) compiles any version of sqlite3 to
>> object files linkable to Delphi 5 and later, the only drawback I
>>
>> Don't know about DISQLite3 , but one of the main performance issues
>
>
> DISQLite3 does _not_ show the performance issues you describe for your BCB
> 5.5 compiled object files. Quite the opposite: DISQLite3 outperformed
> sqlite3.dll whenever I tested.

Don't take it personally :) I just talked about c originated code that
we have to compile against OMF library files formats as long as
borland/codegear/embarcadero never supported COFF format (CMIIW). So
BCC is our only choice for static linking (probably Intel compilers
should still support OMF since Intel introduced it, but I did not try)

And when I talked about the x2 difference, it was about pure memory db
having a thousand rows and a query that make a cross join taking
totally about 6-20 seconds depending on the query. So no I/O involved,
pure cpu intensive operations inside sqlite. To my own surprise a dll
compiled with bcc 5.5 with -O2 option  (maximum optimization as I
recall) made it two times slower than the VC dll (from sqlite.org
site) compiled against the same version. So this is a synthetic test
not pretending to be general.

As for DISQLite3, I see from your site, that it is a great library
having support for many Delphi versions and many db features. I looked
at the source, as I see the dataset is unidirectional and processes
query on request. I'm sure there are no performance penalties here.
Good job

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

Re: [sqlite] "Common Table Expression"

[E.Pasma]

Op 24 jan 2014, om 17:31 heeft E.Pasma het volgende geschreven:


Op 24 jan 2014, om 14:31 heeft Richard Hipp het volgende geschreven:


Please see http://www.sqlite.org/draft/lang_with.html for draft
documentation of the new Common Table Expression implementation for  
SQLite

3.8.3.  Comments, criticism, and typo-corrections are appreciated.

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Hello, I like to give some feedback.

The LIMIT clause in the 2nd example is written at the end of the  
line and it looks as if it only applies to the last part of the  
UNION . This looks sneaky as it applies after the union. It looks  
better if LIMIT is wriiten on a new line.


The text about recursion sais:
"""The "select-stmt" after the AS keyword must be  ..."""
I checked this in the current draft (of 23 01 2014) and it does not  
matter if the recursive statement comes right after te AS keyword,  
or if there other (recursive or non-recursive) statements that come  
first.


As hoped, the RECURSIVE keyword is there only for compatibility. We  
can leave it out if we don't care about it.



The sudoku example uses a recursive definition for the DIGITS  
enumeration. The version using VALUES (of Big Stone?) is still much  
faster. Is there any hint to convert a view like DIGITS to a  
temporary table that it is generated only once?


Finally congratulations for the improved speed. Sudoku is FOUR times  
faster as in last weeks version!


Thanks very much, Edzard.

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


Excuses, I must come back on first post and be less enthusiastic (but  
still very much). When comparing speeds I used different inputs. And  
the meaninglessness of the RECURSIVE keyword fad confused me a bit. My  
remaning remarks and important queation are:


The LIMIT clause in the 2nd example is written at the end of the line  
and it looks as if it only applies to the last part of the UNION .  
This looks sneaky as it applies after the union. It looks better if  
LIMIT is wriiten on a new line.


As hoped, the RECURSIVE keyword is there only for compatibility. We  
can leave it out if we don't care about it.


The sudoku example uses a recursive definition for the DIGITS  
enumeration. The version using VALUES (of Big Stone?) is still much  
faster. Is there any hint to convert a view like DIGITS to a temporary  
table that it is generated only once?

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

Re: [sqlite] "Common Table Expression"

[big stone]

Hi again,

On my 3 level of with example, my systems seems to "hang" if  :
- I put a "distinct" keyword in goods definition,
- or if I replace "union all" per a simple "union" .



** failure mode 1 (no union all in the neighbors) **
with digits(z, lp) AS (
   select '1' as z, 1 as lp
   UNION ALL SELECT
   CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
y(r) as (WITH RECURSIVE col(c) AS (
   select 81  as c
   union all
   select c-1 from col where c>1
   )  select * from col)
, neighbors(r,n) as (select r, ((r-1)/9)*9 + lp from y, digits
union  select r, ((r-1)%9) + (lp-1)*9 + 1 from y, digits
union  select r, (((r-1)/3) % 3) * 3
   + ((r-1)/27) * 27 + lp
   + ((lp-1) / 3) * 6 from y, digits)
, goods (c,n) as (select  * from neighbors where r <>n)

,input(sud) AS (
   VALUES(

'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
)
),

/* The tricky bit. */
x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL
   SELECT
   substr(s, 1, ind-1) || z || substr(s, ind+1),
   instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
   FROM x, digits AS z
   WHERE ind>0
   AND NOT EXISTS (
 SELECT 1 FROM goods AS lp
 WHERE ind=lp.c and  z.z = substr(s, n, 1)
   )
)

SELECT * FROM x  WHERE ind=0;


** failure mode 2 (distinct in the goods definition) **
with digits(z, lp) AS (
   select '1' as z, 1 as lp
   UNION ALL SELECT
   CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
y(r) as (WITH RECURSIVE col(c) AS (
   select 81  as c
   union all
   select c-1 from col where c>1
   )  select * from col)
, neighbors(r,n) as (select r, ((r-1)/9)*9 + lp from y, digits
union all select r, ((r-1)%9) + (lp-1)*9 + 1 from y, digits
union all select r, (((r-1)/3) % 3) * 3
   + ((r-1)/27) * 27 + lp
   + ((lp-1) / 3) * 6 from y, digits)
, goods (c,n) as (select distinct  * from neighbors where r <>n)

,input(sud) AS (
   VALUES(

'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
)
),

/* The tricky bit. */
x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL
   SELECT
   substr(s, 1, ind-1) || z || substr(s, ind+1),
   instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
   FROM x, digits AS z
   WHERE ind>0
   AND NOT EXISTS (
 SELECT 1 FROM goods AS lp
 WHERE ind=lp.c and  z.z = substr(s, n, 1)
   )
)

SELECT * FROM x  WHERE ind=0;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[Dan Kennedy]

On 01/25/2014 01:00 AM, big stone wrote:

AND NOT EXISTS (
  SELECT 1 FROM ok AS lp
  WHERE ind=lp.c and  z.z = substr(s, n, 1)
)


s/ok/goods/ and s/lp.c/lp.r/

Dan.


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

Re: [sqlite] "Common Table Expression"

[big stone]

hi again,


I found my error so timings are :

timing with medium sudoku example  :
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
2 nested with = 3.32 sec
1 nested with = 1.7 sec
3 nested with = 2.65 sec
(1 nested with which could be 3 nested with) = 1.09 sec

** 3 nested with version **
with digits(z, lp) AS (
   select '1' as z, 1 as lp
   UNION ALL SELECT
   CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
y(r) as (WITH RECURSIVE col(c) AS (
   select 81  as c
   union all
   select c-1 from col where c>1
   )  select * from col)
, neighbors(r,n) as (select r, ((r-1)/9)*9 + lp from y, digits
union all select r, ((r-1)%9) + (lp-1)*9 + 1 from y, digits
union all select r, (((r-1)/3) % 3) * 3
   + ((r-1)/27) * 27 + lp
   + ((lp-1) / 3) * 6 from y, digits)
, goods (c,n) as (select * from neighbors where r <>n)

,input(sud) AS (
   VALUES(
  
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
)
),

/* The tricky bit. */
x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL
   SELECT
   substr(s, 1, ind-1) || z || substr(s, ind+1),
   instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
   FROM x, digits AS z
   WHERE ind>0
   AND NOT EXISTS (
 SELECT 1 FROM goods AS lp
 WHERE ind=lp.c and  z.z = substr(s, n, 1)
   )
)

SELECT * FROM x  WHERE ind=0;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[big stone]

Hi,

The speedest version of the sudoku, staying in the limit of lisibility
would include 3 nested "with",

timing with medium sudoku example  :
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
2 nested with = 3.32 sec
1 nested with = 1.7sec

(1 nested with which could be 3 nested with) = 1.09 sec (see below 'annexe
1')

3 nested with   well I get a "MemoryError: " event on the simplest
example. (see below  'annexe 2')
==> nobody in real life would do that, would he ?

Anyway, when I was trying to implement "fake" CTE, I was :
- creating views when the CTE was like " x as (select ...)"
- creating table when the cte was like "x(r) as (select ...)"
- creating table with index when the cte was like (x(r INTEGER

*PRIMARY KEY) as (select ...)"*


*==> So, if the if sudoku solving at the maximum speed with only virtual
table was a DEAD or ALIVE situation, I may suggest "embrassing and
extending" the CTE standard with a similar trick.*


 example of "optimized" stupid-brut force sudoku 1 with **

drop table if exists ok;
create table ok(c,n);
CREATE INDEX ok_i
on ok (c, n);
with digits(z, lp) AS (
VALUES('1', 1),('2', 2) ,('3', 3),('4', 4),('5', 5),('6', 6),('7',
7),('8', 8),('9', 9)
)
,y(r) as (WITH RECURSIVE col(c) AS (
   select 81  as c
   union all
   select c-1 from col where c>1
   )  select * from col)
, neighbors(r,n) as (select r, ((r-1)/9)*9 + lp from y, digits
union all select r, ((r-1)%9) + (lp-1)*9 + 1 from y, digits
union all select r, (((r-1)/3) % 3) * 3
   + ((r-1)/27) * 27 + lp
   + ((lp-1) / 3) * 6 from y, digits)
, goods (r,n) as (select * from neighbors where r <>n)
insert into ok select distinct * from goods;

-- easy   (0 sec)
'53..76..195986.8...6...34..8.3..17...2...6.628419..58..79'
-- medium (2 sec)
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
-- hard   (200 s)
'8..36..7..9.2...5...7...457.1...3...168..85...1..94..'

WITH RECURSIVE input(sud) AS (
   VALUES(
 
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
)
),

/* A table filled with digits 1..9, inclusive. */
digits(z, lp) AS (
VALUES('1', 1),('2', 2) ,('3', 3),('4', 4),('5', 5),('6', 6),('7',
7),('8', 8),('9', 9)
),

/* The tricky bit. */
x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL
   SELECT
   substr(s, 1, ind-1) || z || substr(s, ind+1),
   instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
   FROM x, digits AS z
   WHERE ind>0
   AND NOT EXISTS (
 SELECT 1 FROM ok AS lp
 WHERE ind=lp.c and  z.z = substr(s, n, 1)
   )
)

SELECT * FROM x  WHERE ind=0;

 same with 3 nested with ... blow up even on the simplest case (bug of
me ? ) ***
drop table if exists ok;
create table ok(c,n);
CREATE INDEX ok_i
on ok (c, n);
with digits(z, lp) AS (
VALUES('1', 1),('2', 2) ,('3', 3),('4', 4),('5', 5),('6', 6),('7',
7),('8', 8),('9', 9)
)
,y(r) as (WITH RECURSIVE col(c) AS (
   select 81  as c
   union all
   select c-1 from col where c>1
   )  select * from col)
, neighbors(r,n) as (select r, ((r-1)/9)*9 + lp from y, digits
union all select r, ((r-1)%9) + (lp-1)*9 + 1 from y, digits
union all select r, (((r-1)/3) % 3) * 3
   + ((r-1)/27) * 27 + lp
   + ((lp-1) / 3) * 6 from y, digits)
, goods (r,n) as (select * from neighbors where r <>n)


,input(sud) AS (
   VALUES(
-- 
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
'53..76..195986.8...6...34..8.3..17...2...6.628419..58..79')
),

/* A table filled with digits 1..9, inclusive. */
--digits(z, lp) AS (
--VALUES('1', 1),('2', 2) ,('3', 3),('4', 4),('5', 5),('6', 6),('7',
7),('8', 8),('9', 9)
--),

/* The tricky bit. */
x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL
   SELECT
   substr(s, 1, ind-1) || z || substr(s, ind+1),
   instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
   FROM x, digits AS z
   WHERE ind>0
   AND NOT EXISTS (
 SELECT 1 FROM ok AS lp
 WHERE ind=lp.c and  z.z = substr(s, n, 1)
   )
)

SELECT * FROM x  WHERE ind=0;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[Ralf Junker]

On 24.01.2014 10:06, Max Vlasov wrote:


BCC 5.5 (freely downloadable) compiles any version of sqlite3 to
object files linkable to Delphi 5 and later, the only drawback I
noticed is that for memory-intensive operations (memory databases) the
performance is twice as worst comparing to the dll on the site
(probably VC compiled), but for databases on disk the difference is
small since I/O overhead compensate it.

Don't know about DISQLite3 , but one of the main performance issues


DISQLite3 does _not_ show the performance issues you describe for your 
BCB 5.5 compiled object files. Quite the opposite: DISQLite3 
outperformed sqlite3.dll whenever I tested.


You can test yourself with the example projects located in 
\DISQLite3_Log_Inserts\ and \DISQLite3_20_Million\ sub-folders of the 
\Demo\ directory.


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

Re: [sqlite] Simple data conversion in SQLite - please help

[Klaas V]

On Jan 22, 2014, at 8:21 AM, Richard Hipp  wrote:

> I seem to recall seeing some SMS databases off of an iPhone that used unix
> timestamps for the date/time.  That would be seconds since 1970.  You can
> use the 'unixepoch' modifier on the date functions within SQLite to do
> the conversion, if you want.

That's the normal epoch point zero. There are systems starting on January 1, 
1904
Perhaps other dates are used as well.

Klaas `Z4us` V
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[E.Pasma]

Op 24 jan 2014, om 14:31 heeft Richard Hipp het volgende geschreven:


Please see http://www.sqlite.org/draft/lang_with.html for draft
documentation of the new Common Table Expression implementation for  
SQLite

3.8.3.  Comments, criticism, and typo-corrections are appreciated.

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Hello, I like to give some feedback.

The LIMIT clause in the 2nd example is written at the end of the line  
and it looks as if it only applies to the last part of the UNION .  
This looks sneaky as it applies after the union. It looks better if  
LIMIT is wriiten on a new line.


The text about recursion sais:
"""The "select-stmt" after the AS keyword must be  ..."""
I checked this in the current draft (of 23 01 2014) and it does not  
matter if the recursive statement comes right after te AS keyword, or  
if there other (recursive or non-recursive) statements that come first.


As hoped, the RECURSIVE keyword is there only for compatibility. We  
can leave it out if we don't care about it.



The sudoku example uses a recursive definition for the DIGITS  
enumeration. The version using VALUES (of Big Stone?) is still much  
faster. Is there any hint to convert a view like DIGITS to a temporary  
table that it is generated only once?


Finally congratulations for the improved speed. Sudoku is FOUR times  
faster as in last weeks version!


Thanks very much, Edzard.

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

Re: [sqlite] "Common Table Expression"

[Kevin Benson]

On Fri, Jan 24, 2014 at 10:09 AM, Clemens Ladisch wrote:

> Richard Hipp wrote:
> > Please see http://www.sqlite.org/draft/lang_with.html for draft
> > documentation of the new Common Table Expression implementation for
> SQLite
> > 3.8.3.  Comments, criticism, and typo-corrections are appreciated.
>
> Duplicated "the":
>
>   * If a UNION operator connects the the initial-select ...
>

IS where SHOULD BE were

* An ordinary common table expression works as if it where a view ...


IS where SHOULD BE it were

* Then the row containing 2 is used as if where the complete content ...


IS ORDERBY SHOULD BE ORDER BY

* The "ORDERBY checkin.mtime DESC" term ...


IS by all mtime, SHOULD BE all by mtime,

* ... thousands of ancestors, sort them by all mtime, ...


IS breath SHOULD BE breadth

* 5 occurrences on this documentation page


IS undertaking SHOULD BE undertaken

* ... is undertaking by ...


IS organization. SHOULD BE organization:

* ...within that organization.


IS string SHOULD BE string:

* ... Thus the input string

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[Clemens Ladisch]

Richard Hipp wrote:
> Please see http://www.sqlite.org/draft/lang_with.html for draft
> documentation of the new Common Table Expression implementation for SQLite
> 3.8.3.  Comments, criticism, and typo-corrections are appreciated.

Duplicated "the":

  * If a UNION operator connects the the initial-select ...


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

Re: [sqlite] SELECTing from another SELECT

[Igor Tandetnik]

On 1/24/2014 9:28 AM, jose isaias cabrera wrote:


Igor Tandetnik wrote...

On 1/23/2014 2:26 PM, St. B. wrote:

SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask =
'a');

will probably fill the bill.

If I where to run your query, I would do a
select A.* from A inner join B on A.a = b.ProjId where b.ptask='a'


Careful - this will produce a different result if B has multiple rows
with the same ptask and projid. May or may not be a concern in the
OP's case.


What would be the correct inner join syntax to provide the same output as
the original select?  I like that shorter syntax.  Will any of these
will be
faster?  Thanks so much.


"SELECT DISTINCT ..." perhaps. But that would kill any alleged 
performance improvement that a join may have over IN (if any; 
personally, I'm not sure it's necessarily true that the join would 
perform better in the first place).

--
Igor Tandetnik

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

Re: [sqlite] SELECTing from another SELECT

[jose isaias cabrera]

Igor Tandetnik wrote...

On 1/23/2014 2:26 PM, St. B. wrote:

SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a');

will probably fill the bill.

If I where to run your query, I would do a
select A.* from A inner join B on A.a = b.ProjId where b.ptask='a'


Careful - this will produce a different result if B has multiple rows with 
the same ptask and projid. May or may not be a concern in the OP's case.


What would be the correct inner join syntax to provide the same output as
the original select?  I like that shorter syntax.  Will any of these will be
faster?  Thanks so much.

josé

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

Re: [sqlite] "Common Table Expression"

[Richard Hipp]

Please see http://www.sqlite.org/draft/lang_with.html for draft
documentation of the new Common Table Expression implementation for SQLite
3.8.3.  Comments, criticism, and typo-corrections are appreciated.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[Clemens Ladisch]

Max Vlasov wrote:
> They had static variant implemented with msvcrt linked, to remove
> the dependency yourself you have to implement the following functions
>
> _malloc,_realloc,_free,_memset,_strncmp,_memmove,_memcpy,_strlen,_qsort,_memcmp,_localtime

malloc/realloc/free are not needed if SQLITE_WIN32_MALLOC is defined
(which is a good idea anyway because the Windows functions are faster
than Borland's nowadays).

If you compile with BCC 5.5, most of these functions can simply be
extracted from its runtime library (i.e., extract the modules ftol,
_ftoul, _ll, memcmp, memcpy, memmove, memset, qsort, strlen, and strncmp
from the cw32.lib file with tlib, and then link with these .obj files).

You also need a variable named __turboFloat (which is used only to link
floating-point initialization code, which is already done by Delphi):

var
  __turboFloat: Integer;

... and the function localtime(), which can simply be ported from
SQLite's Windows CE localtime() emulation:

type
  time_t = Longint;
  P_time_t = ^time_t;

  struct_tm = record
tm_sec: Integer;
tm_min: Integer;
tm_hour: Integer;
tm_mday: Integer;
tm_mon: Integer;
tm_year: Integer;
tm_wday: Integer;
tm_yday: Integer;
tm_isdst: Integer;
  end;
  P_struct_tm = ^struct_tm;

var
  y: struct_tm;

function _localtime(t: P_time_t): P_struct_tm; cdecl;
var
  uTm, lTm: FILETIME;
  pTm: SYSTEMTIME;
  t64: Int64;
begin
  t64 := t^;
  t64 := (t64 + 11644473600)*1000;
  uTm.dwLowDateTime := t64;
  uTm.dwHighDateTime:= t64 shr 32;
  FileTimeToLocalFileTime(uTm, lTm);
  FileTimeToSystemTime(lTm, pTm);
  y.tm_year := pTm.wYear - 1900;
  y.tm_mon := pTm.wMonth - 1;
  y.tm_wday := pTm.wDayOfWeek;
  y.tm_mday := pTm.wDay;
  y.tm_hour := pTm.wHour;
  y.tm_min := pTm.wMinute;
  y.tm_sec := pTm.wSecond;
  Result := @y;
end;


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

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[dean gwilliam]

On 24/01/2014 09:06, Max Vlasov wrote:

So, if you plan get best performance

I do! and thank you for your very detailed analysis.
I had no idea about nearly all of what you've said and very much 
appreciate you sharing your findings.

It helps a lot.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] csv export

[Frantisek Cerven]

can I run sql shell also on windows CE and windows Mobile?

could you please give me little example how to create virual table to file
and export it to pc? I can find it. Can I also delete table - file from
file explorer without any problems?
Many thanks.


2014/1/24 Hick Gunter 

> You can fork off a child process, run the sqlite shell in that and pipe
> your commands to it.
>
> Or you could write a CSV virtual table and execute something like "insert
> into  select ..."
>
> -Ursprüngliche Nachricht-
> Von: Frantisek Cerven [mailto:feri...@gmail.com]
> Gesendet: Freitag, 24. Jänner 2014 07:45
> An: sqlite-users@sqlite.org
> Betreff: [sqlite] csv export
>
> Hello,
>
> I want to ask you if is any way to call sqlite special commands
> programatically from c#.
> E.g. I need something like this in code not in command line:
>
> sqlite> .mode csv
> sqlite> .output test.csv
> sqlite> select * from tbl1;
> sqlite> .output stdout
>
> If not, what is the quicker way to export table programatically? Now I`m
> doing it with DbDataReader, but it is slow.
>
> Ps: There is not any support user forum??
>
> Thank you,
> Fratnisek
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> --
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> Klitschgasse 2 – 4, A - 1130 Vienna, Austria
> FN 157284 a, HG Wien
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This e-mail is confidential and may well also be legally privileged. If
> you have received it in error, you are on notice as to its status and
> accordingly please notify us immediately by reply e-mail and then delete
> this message from your system. Please do not copy it or use it for any
> purposes, or disclose its contents to any person as to do so could be a
> breach of confidence. Thank you for your cooperation.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[Max Vlasov]

On Thu, Jan 23, 2014 at 1:33 AM, dean gwilliam
 wrote:
> I'm just wondering what my options are here?
> Any advice much appreciated.
> ___

My two cents...

Historically I took Aducom TDataSet-compatible classes
(http://www.aducom.com/cms/page.php?2 , author - Albert Drent) and
used it ever since, but along the road there were many changes that I
made, so I'm not sure I can recommend one of the current (last
version). They had static variant implemented with msvcrt linked
(maybe it was removed to the moment), to remove the dependency
yourself you have to implement the following functions

_malloc,_realloc,_free,_memset,_strncmp,_memmove,_memcpy,_strlen,_qsort,_memcmp,_localtime

part of them might be just calls to Delphi existing rtl, for another
part a little work needed. This allowed me for example to monitor the
number of memory requests different queries make.

BCC 5.5 (freely downloadable) compiles any version of sqlite3 to
object files linkable to Delphi 5 and later, the only drawback I
noticed is that for memory-intensive operations (memory databases) the
performance is twice as worst comparing to the dll on the site
(probably VC compiled), but for databases on disk the difference is
small since I/O overhead compensate it.

Don't know about DISQLite3 , but one of the main performance issues
when using sqlite is that BDE was made for virtual access of data with
moving cursor, but it's not possible with sqlite. You have a query and
you can only move forward. So, the easiest approach is to load all
data, but imagine this for a very large table, activating TDataset in
this case may take very long time (and space). One of the approach is
to change it to load all rowids of the table and request record data
on the fly based on this array.

So, if you plan get best performance and don't need borland database
components and controls, then your best bet is to use sqlite api or
simple object wrappers around it. Otherwise, be aware that "impedance
mismatch" between sqlite and BDE may cost you performance penalties
depending on the library you use.

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

Re: [sqlite] csv export

[Hick Gunter]

You can fork off a child process, run the sqlite shell in that and pipe your 
commands to it.

Or you could write a CSV virtual table and execute something like "insert into 
 select ..."

-Ursprüngliche Nachricht-
Von: Frantisek Cerven [mailto:feri...@gmail.com]
Gesendet: Freitag, 24. Jänner 2014 07:45
An: sqlite-users@sqlite.org
Betreff: [sqlite] csv export

Hello,

I want to ask you if is any way to call sqlite special commands programatically 
from c#.
E.g. I need something like this in code not in command line:

sqlite> .mode csv
sqlite> .output test.csv
sqlite> select * from tbl1;
sqlite> .output stdout

If not, what is the quicker way to export table programatically? Now I`m doing 
it with DbDataReader, but it is slow.

Ps: There is not any support user forum??

Thank you,
Fratnisek
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECTing from another SELECT

[Hick Gunter]

Use IN

Projid = (SELECT ...) takes only the first value returned from the subselect

-Ursprüngliche Nachricht-
Von: jose isaias cabrera [mailto:cabr...@wrc.xerox.com]
Gesendet: Donnerstag, 23. Jänner 2014 20:11
An: General Discussion of SQLite Database
Betreff: [sqlite] SELECTing from another SELECT


Greetings!

I have these tables A and B:
A
id,projid,a,b
1,1,'a','h'
2,2,'b','i'
3,3,'c','j'
4,4,'d','k'
5,5,'e','l'
...
...


B
id,projid,ptask
101,1,'a'
102,2,'b'
103,3,'a'
104,4,'b'
105,5,'a'
...
...

When I do this SELECT,

SELECT * FROM A WHERE projid = (SELECT projid FROM B WHERE ptask = 'a');

This only returns one record (record 1) where it should return all the records 
with ptask = 'a'.  Will someone point me to the right syntax to get this 
correct?  Thanks for the help.

josé

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


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

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users