Re: [sqlite] Feature request: Support for aarch64

[James K. Lowden]

On Thu, 25 Apr 2013 10:30:31 -0400
Richard Hipp  wrote:

> > The configure script is typically generated by a build machine that
> > has autotools installed, and included in a distribution tarball.
> > The user unpacks the tarball and runs the configure script.  He
> > doesn't need the autotools and he doesn't generate the scirpt.
> >
> > Anyone building from the Fossil repository should IMO be prepared to
> > run autogen & co., and to have other ancillary tools ready as well.
> >
> >
> If that is so, then there is no harm in including ./configure from an
> older autoconf in the repo, since anybody who wants to build for an
> unusual platform that the older autoconf did not support can simply
> rerun autoconf.  Right?

Only if you find it convenient for your purposes.  Your expressed
concern was that upgrading autoconf brings ugly changes to the source
code repository.  I only asked why you bother to archive configure,
because, if you don't, that particular problem goes away.  

On the FreeTDS project, we just archive every released tarball on the
FTP server.  Version 0.1 from 1998, a mere 26 KB, is still available
just in case someone wants to run a featureless version on a 
Pentium II.  

I agree that upgrading autoconf is a nuisance; it's far from my
favorite software.  

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

Re: [sqlite] Programming API vs console

[Random Coder]

On Tue, Apr 23, 2013 at 8:47 PM, Igor Korot  wrote:

> query = wxString::Format(...);
> if( ( result = sqlite3_prepare_v2( m_handle, query, -1, , 0 ) ) !=
> SQLITE_OK )
>

It's been a while since I've worked with wxWidgets, but when I did,
wxString didn't support an implicit conversion like you're using here.

You need to do something like this for your sqlite_prepare_v2 call:

sqlite3_prepare_v2(m_handle, (const char*)query.mb_str(wxConvUTF8), -1,
, 0);

Though, I suppose if I'm right, this should have failed in some other way
much sooner.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite: Incompatible file locking behavior starting version 1.0.82.0

[Joe Mistachkin]

Levi Haskell wrote:
>
> Is this what the INTEROP_LEGACY_CLOSE compile-time option refers to?
>
 
Yes.

--
Joe Mistachkin

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

Re: [sqlite] System.Data.SQLite: Incompatible file locking behavior starting version 1.0.82.0

[Levi Haskell (BLOOMBERG/ 731 LEXIN)]

Is this what the INTEROP_LEGACY_CLOSE compile-time option refers to?


  *Use the legacy connection closing algorithm when built with the 
INTEROP_LEGACY_CLOSE compile-time option.

Thanks,
- Levi

- Original Message -
From: sql...@mistachkin.com
To: sqlite-users@sqlite.org
Cc: Levi Haskell (BLOOMBERG/ 731 LEXIN)
At: Apr 25 2013 16:38:27


Levi Haskell wrote:   What was the motivation for this change (it seems 
to be quite inconvenient  in my case)?   The previous method used to 
deal with the non-deterministic finalization order imposed by the CLR did not 
work reliably in all circumstances, did not follow best-practices for the 
IDisposable interface, and relied heavily upon internal semantics of the native 
SQLite core library.  -- Joe Mistachkin  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite: Incompatible file locking behavior starting version 1.0.82.0

[Joe Mistachkin]

Levi Haskell wrote:
>
> What was the motivation for this change (it seems to be quite inconvenient
> in my case)?
>

The previous method used to deal with the non-deterministic finalization
order
imposed by the CLR did not work reliably in all circumstances, did not
follow
best-practices for the IDisposable interface, and relied heavily upon
internal
semantics of the native SQLite core library.

--
Joe Mistachkin

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

Re: [sqlite] Programming API vs console

[Igor Korot]

Hi,

On Thu, Apr 25, 2013 at 1:09 PM,  wrote:

> Am 2013-04-25 21:43, schrieb Igor Korot:
>
>  Hi,
>>
>> On Thu, Apr 25, 2013 at 12:31 PM,  wrote:
>>
>>  Am 2013-04-25 21:25, schrieb Igor Korot:
>>>
>>> Simon,
>>>

 On Wed, Apr 24, 2013 at 7:34 PM, Simon Slavin 
 wrote:


  On 25 Apr 2013, at 3:28am, Igor Korot  wrote:
>
> > Changed. No difference at all. Record is still does not show up.
>
> My guess is that you are opening different files in the shell and your
> app.  This is usually caused by a default file path not being what you
> think it is.
>
> Use one program to insert a new row in the table.  Use a simple SELECT
> in
> both programs to see if you can retrieve this new row it.
>
>
>  Nope. It is one file.
 Besides the next time I am running the application at start I am reading
 this table. Record is not present.


>>> Is this by chance on Windows 7, and do you probably Run in /Program
>>> Files/
>>> ?
>>>
>>>
>> Yes, it is Windows 7 64-bit, but no I don't run from C:\Progra~ ;-)
>>
>
> ..and also you don't store there nor in ProgramData?
>

Nope.

Thank you.


> Well..Then at least it is not related to UAC or Virtualization.. ..:-)
>
>
>> Thank you.
>>
>>
>>
>>> Marcus
>>>
>>>
>>>
>>>  Thank you.



  Simon.
> ___
> 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
 
 >


>>> ___
>>> 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
>>
>
> __**_
> 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] Programming API vs console

[Mgrimm]

Am 2013-04-25 21:43, schrieb Igor Korot:

Hi,

On Thu, Apr 25, 2013 at 12:31 PM,  wrote:


Am 2013-04-25 21:25, schrieb Igor Korot:

Simon,


On Wed, Apr 24, 2013 at 7:34 PM, Simon Slavin 


wrote:



On 25 Apr 2013, at 3:28am, Igor Korot  wrote:

> Changed. No difference at all. Record is still does not show up.

My guess is that you are opening different files in the shell and 
your
app.  This is usually caused by a default file path not being what 
you

think it is.

Use one program to insert a new row in the table.  Use a simple 
SELECT in

both programs to see if you can retrieve this new row it.



Nope. It is one file.
Besides the next time I am running the application at start I am 
reading

this table. Record is not present.



Is this by chance on Windows 7, and do you probably Run in /Program 
Files/

?



Yes, it is Windows 7 64-bit, but no I don't run from C:\Progra~ ;-)


..and also you don't store there nor in ProgramData?
Well..Then at least it is not related to UAC or Virtualization.. ..:-)



Thank you.




Marcus




Thank you.




Simon.
__**_
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



__**_
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


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

Re: [sqlite] System.Data.SQLite: Incompatible file locking behavior starting version 1.0.82.0

[Levi Haskell (BLOOMBERG/ 731 LEXIN)]

Thank you Joe,

What was the motivation for this change (it seems to be quite inconvenient in 
my case)?

- Levi

- Original Message -
From: sql...@mistachkin.com
To: sqlite-users@sqlite.org
Cc: Levi Haskell (BLOOMBERG/ 731 LEXIN)
At: Apr 25 2013 15:07:54


As of release 1.0.82.0, the object disposal semantics were changed to keep
the underlying connection around until all associated System.Data.SQLite
objects have been properly disposed.

Levi Haskell wrote:
> 
> var file = Path.GetTempFileName();
> using (var connection = new SQLiteConnection("Data Source=" + file))
> {
>   Console.WriteLine(connection.GetType().Assembly.FullName);
>   connection.Open();
> 
>   var command = connection.CreateCommand();
>   command.CommandText = "CREATE TABLE t(a)";
>   command.ExecuteNonQuery();
> }
> // the following line succeeds in v1.0.81.0 and earlier but
> // FAILS on v1.0.82.0 and later with "file still in use" error
> File.Delete(file);
> 

In the above example, adding "command.Dispose();" just prior to the end of
the using block should allow the file to be deleted.

>
> Was this change made by design?
>

Yes.

--
Joe Mistachkin


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

Re: [sqlite] Programming API vs console

[Igor Korot]

Hi,

On Thu, Apr 25, 2013 at 12:31 PM,  wrote:

> Am 2013-04-25 21:25, schrieb Igor Korot:
>
> Simon,
>>
>> On Wed, Apr 24, 2013 at 7:34 PM, Simon Slavin 
>> wrote:
>>
>>
>>> On 25 Apr 2013, at 3:28am, Igor Korot  wrote:
>>>
>>> > Changed. No difference at all. Record is still does not show up.
>>>
>>> My guess is that you are opening different files in the shell and your
>>> app.  This is usually caused by a default file path not being what you
>>> think it is.
>>>
>>> Use one program to insert a new row in the table.  Use a simple SELECT in
>>> both programs to see if you can retrieve this new row it.
>>>
>>>
>> Nope. It is one file.
>> Besides the next time I am running the application at start I am reading
>> this table. Record is not present.
>>
>
> Is this by chance on Windows 7, and do you probably Run in /Program Files/
> ?
>

Yes, it is Windows 7 64-bit, but no I don't run from C:\Progra~ ;-)

Thank you.


>
> Marcus
>
>
>
>> Thank you.
>>
>>
>>
>>> Simon.
>>> __**_
>>> 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
>>
>
> __**_
> 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] Programming API vs console

[Mgrimm]

Am 2013-04-25 21:25, schrieb Igor Korot:

Simon,

On Wed, Apr 24, 2013 at 7:34 PM, Simon Slavin  
wrote:




On 25 Apr 2013, at 3:28am, Igor Korot  wrote:

> Changed. No difference at all. Record is still does not show up.

My guess is that you are opening different files in the shell and 
your
app.  This is usually caused by a default file path not being what 
you

think it is.

Use one program to insert a new row in the table.  Use a simple 
SELECT in

both programs to see if you can retrieve this new row it.



Nope. It is one file.
Besides the next time I am running the application at start I am 
reading

this table. Record is not present.


Is this by chance on Windows 7, and do you probably Run in /Program 
Files/ ?


Marcus



Thank you.




Simon.
___
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


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

Re: [sqlite] Programming API vs console

[Igor Korot]

Simon,

On Wed, Apr 24, 2013 at 7:34 PM, Simon Slavin  wrote:

>
> On 25 Apr 2013, at 3:28am, Igor Korot  wrote:
>
> > Changed. No difference at all. Record is still does not show up.
>
> My guess is that you are opening different files in the shell and your
> app.  This is usually caused by a default file path not being what you
> think it is.
>
> Use one program to insert a new row in the table.  Use a simple SELECT in
> both programs to see if you can retrieve this new row it.
>

Nope. It is one file.
Besides the next time I am running the application at start I am reading
this table. Record is not present.

Thank you.


>
> Simon.
> ___
> 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] System.Data.SQLite: Incompatible file locking behavior starting version 1.0.82.0

[Joe Mistachkin]

As of release 1.0.82.0, the object disposal semantics were changed to keep
the underlying connection around until all associated System.Data.SQLite
objects have been properly disposed.

Levi Haskell wrote:
> 
> var file = Path.GetTempFileName();
> using (var connection = new SQLiteConnection("Data Source=" + file))
> {
>   Console.WriteLine(connection.GetType().Assembly.FullName);
>   connection.Open();
> 
>   var command = connection.CreateCommand();
>   command.CommandText = "CREATE TABLE t(a)";
>   command.ExecuteNonQuery();
> }
> // the following line succeeds in v1.0.81.0 and earlier but
> // FAILS on v1.0.82.0 and later with "file still in use" error
> File.Delete(file);
> 

In the above example, adding "command.Dispose();" just prior to the end of
the using block should allow the file to be deleted.

>
> Was this change made by design?
>

Yes.

--
Joe Mistachkin

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

[sqlite] System.Data.SQLite: Incompatible file locking behavior starting version 1.0.82.0

[Levi Haskell (BLOOMBERG/ 731 LEXIN)]

It seems that in version 1.0.81.0 and earlier the database file was released 
immediately after all SQLiteConnection objects were disposed even if some 
dependent SQLiteCommand and/or SQLiteDataReader objects were not yet disposed 
or closed. 
However starting version 1.0.82.0 the file remains locked. Consider this code:

var file = Path.GetTempFileName();
using (var connection = new SQLiteConnection("Data Source=" + file))
{
  Console.WriteLine(connection.GetType().Assembly.FullName);
  connection.Open();

  var command = connection.CreateCommand();
  command.CommandText = "CREATE TABLE t(a)";
  command.ExecuteNonQuery();
}
// the following line succeeds in v1.0.81.0 and earlier but
// FAILS on v1.0.82.0 and later with "file still in use" error
File.Delete(file);

Was this change made by design?

Thanks,
- Levi

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

[sqlite] sqlite4: consistent BUS ERROR on dropping index

[David King]

Is this the right list for sqlite4 bugs?

I have sqlite4 build fde11ff78c433d66 and a largish database. When I drop one 
of the indices on it through the sqlite4 command line tool, the tool dies with 
a BUS ERROR. For this database file it reproduces 100% of the time for me. I 
have the gdb backtrace below. I can share the database but it's quite large so 
I'd rather not upload it unless someone thinks they can actually make use of it.

1.7G database.db
347M database.db-log
2.0G database.db-shm

$ gdb $(which sqlite4) --args sqlite4 databasedb/database.db "drop index 
idx_followers_main; create index idx_followers_main on followers(hash, 
next_token, count)"

This GDB was configured as "x86_64-apple-darwin"...Reading symbols for shared 
libraries .. done

(gdb) run
Starting program: /Users/dking/sync/sqlite4/sqlite4 databasedb/database.db 
drop\ index\ idx_followers_main\;\ create\ index\ idx_followers_main\ on\ 
followers\(hash,\ next_token,\ count\)
Reading symbols for shared libraries + done

Program received signal EXC_BAD_ACCESS, Could not access memory.
Reason: 10 at address: 0x0001f7231004
treeShmalloc (pDb=0x10088, bAlign=1052536, nByte=40, pRc=0x1f7231000) at 
lsm_tree.c:683
683 lsm_tree.c: No such file or directory.
in lsm_tree.c
(gdb) bt
#0  treeShmalloc (pDb=0x10088, bAlign=1052536, nByte=40, pRc=0x1f7231000) 
at lsm_tree.c:683
#1  0x00010004b7fe in treeShmallocZero [inlined] () at lsm_tree.c:712
#2  0x00010004b7fe in newTreeNode [inlined] () at :723
#3  0x00010004b7fe in treeInsert (pDb=0x7fff5fbfe150, pCsr=0x100100f78, 
iLeftPtr=1606410576, iTreeKey=1606410576, iRightPtr=2147450820, iSlot=3) at 
lsm_tree.c:978
#4  0x00010004b741 in treeInsert (pDb=0x7fff5fbfe1b0, pCsr=0x7fff5fbfe260, 
iLeftPtr=1606410672, iTreeKey=1606410672, iRightPtr=2147450740, iSlot=3) at 
lsm_tree.c:939
#5  0x00010004b741 in treeInsert (pDb=0x7fff5fbfe210, pCsr=0x7fff5fbfe260, 
iLeftPtr=1606410768, iTreeKey=1606410768, iRightPtr=2147450688, iSlot=3) at 
lsm_tree.c:939
#6  0x00010004c115 in treeInsertLeaf [inlined] () at :1052
#7  0x00010004c115 in treeInsertEntry (pDb=0x7fff5fbfe3e0, 
flags=1606411232, pKey=0x7fff5fbfe3e0, nKey=1606411232, pVal=0x15, 
nVal=1606411232) at lsm_tree.c:1557
#8  0x00010003bb9f in doWriteOp (pDb=0x10088, bDeleteRange=1606411312, 
pKey=0x100102a98, nKey=16, pVal=0x7fff5fbfe430, nVal=1606411312) at 
lsm_main.c:696
#9  0x000100031914 in kvlsmDelete (pKVCursor=0x1001032a0) at kvlsm.c:297
#10 0x000100030474 in sqlite4KVCursorDelete (p=0x1001032a0) at kv.c:202
#11 0x000192a5 in sqlite4VdbeExec (p=0x10088) at vdbe.c:3933
#12 0x000100064b9a in sqlite4Step [inlined] () at :383
#13 0x000100064b9a in sqlite4_step (pStmt=0x1001039f0) at vdbeapi.c:444
#14 0x0001322d in shell_exec () at ctype.h:175
#15 0x00011c32 in main (argc=3, argv=0x7fff5fbfe918) at shell.c:2892
(gdb)

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

Re: [sqlite] Order of columns in group by statement affects query performance

[Simon Slavin]

On 25 Apr 2013, at 4:23pm, Jay A. Kreibich  wrote:

>  Except there is no such thing as "GROUP BY order".  SQL Golden Rule:
>  If there is no ORDER BY, the rows have no order.  According to SQL, 
>  neither the groups, nor the rows within a group (as they are fed into
>  aggregates) have a defined order.  Any query that makes assumptions
>  about the ordered result of a GROUP BY is broken.
> 
>  Use the out-of-order index.

GROUP BY on multiple columns means that the values in all those columns have to 
be the same for the rows to be included in the same GROUP.  It says nothing 
about the order those groups should appear in in the results of the SELECT.

Okay.  So adding this to what went by upthread, I was wrong.  Column order in 
the GROUP BY clause doesn't matter.  Therefore the upthread comment that GROUP 
BY A,B means exactly the same as GROUP BY B,A is correct.

So if there's an index which features those columns in any order it can be 
used, not matter what order the columns appear in in the GROUP BY clause.

So it was perfectly reasonable for the OP to wonder why an index was used for 
one order but not another.

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

Re: [sqlite] Order of columns in group by statement affects query performance

[Jay A. Kreibich]

On Thu, Apr 25, 2013 at 05:08:04PM +0200, Daniel Winter scratched on the wall:
> 2013/4/25 James K. Lowden 
> 
> >
> > Nonsense.  The query parser sees GROUP BY A,B.  The optimizer sees an
> > index ordered B,A.  By permuting the order of the columns in the GROUP
> > BY clause, it finds a match for the index and uses it.
> >
> > Yes, the problem is O(n^2), where n is the number of columns in the
> > GROUP BY, but n is always small; even 7 columns could be checked in
> > less than 50 iterations.
> >
> 
> I believe its O(n!), but still doable for small n.  I don't know the inner
> workings of the query optimizer but mabye instead of asking/check for a
> index of every permutation of the columns in the group by, it could just
> check if an index exists which covers all columns (even the sorting order
> doesn't matter). (the virtual table api needs an addition for that to work)

  Permutations are O(N!), but that's not really what you want.  Given a
  set of GROUP BY terms you want, generally, the index with the most
  terms in any initial order.  You don't need a full match for the
  index to be a win.  For example, GROUP BY A,B,C,D,E is likely to
  get a performance boost from an index on (A,D,B) and, *in general*,
  that should be a bigger win than an index on (B,C).

  Of course, since this is a query optimizer, there are always edge
  cases... For example, if there is an index over (E) that has 99%
  unique values, it is likely a better choice than (A,D,B)... it
  depends on the distribution of the index.  Similarly, if any GROUP BY
  term maps to a unique index... boom, you're done.

  As with most things having to do with query optimization, the problem
  quickly explodes.  On the other hand, SQLite must already have
  assumptions about index costs (with or without ANALYZE), so at least
  there's an existing set of weights and assumptions to work from.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of columns in group by statement affects query performance

[Jay A. Kreibich]

On Thu, Apr 25, 2013 at 10:29:34AM +0200, Hick Gunter scratched on the wall:
> AFAIK SQLite treats GROUP BY the same way as ORDER BY (taken from hints
> in the virtual table description).

  They're not the same clause, they don't do the same thing.

  Now, it is true that most database systems implement the first step
  of a GROUP BY by sorting the query using semantics that are similar to
  ORDER BY.  That way all of the rows in a related group are next to
  each other, and they're easier to process.  I assume SQLite does the
  same thing.

  It is, however, as they say, "an implementation detail."

> IF you have an index that covers the GROUP BY clause in any other order,
> then you still have the guarantee that all rows belonging to the same
> group will be retrieved together, but the result rows will be ordered
> in index order and not GROUP BY order.

  Except there is no such thing as "GROUP BY order".  SQL Golden Rule:
  If there is no ORDER BY, the rows have no order.  According to SQL, 
  neither the groups, nor the rows within a group (as they are fed into
  aggregates) have a defined order.  Any query that makes assumptions
  about the ordered result of a GROUP BY is broken.

  Use the out-of-order index.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of columns in group by statement affects query performance

[Daniel Winter]

2013/4/25 James K. Lowden 

>
> Nonsense.  The query parser sees GROUP BY A,B.  The optimizer sees an
> index ordered B,A.  By permuting the order of the columns in the GROUP
> BY clause, it finds a match for the index and uses it.
>
> Yes, the problem is O(n^2), where n is the number of columns in the
> GROUP BY, but n is always small; even 7 columns could be checked in
> less than 50 iterations.
>

I believe its O(n!), but still doable for small n.  I don't know the inner
workings of the query optimizer but mabye instead of asking/check for a
index of every permutation of the columns in the group by, it could just
check if an index exists which covers all columns (even the sorting order
doesn't matter). (the virtual table api needs an addition for that to work)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] pager.c does not compile with SQLITE_OMIT_WAL

[Ralf Junker]

The current SQLite Fossil snapshot does not compile with SQLITE_OMIT_WAL 
defined.

Reason:

The Pager->pWal element is compiled out in pager.c here:

http://www.sqlite.org/src/artifact/6c3a8a5d665498b0344395a2c9f82d5abc4cc771?ln=691-694

but still accessed here:

http://www.sqlite.org/src/artifact/6c3a8a5d665498b0344395a2c9f82d5abc4cc771?ln=2876
http://www.sqlite.org/src/artifact/6c3a8a5d665498b0344395a2c9f82d5abc4cc771?ln=5243
http://www.sqlite.org/src/artifact/6c3a8a5d665498b0344395a2c9f82d5abc4cc771?ln=5333

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

Re: [sqlite] Order of columns in group by statement affects query performance

[James K. Lowden]

On Thu, 25 Apr 2013 10:29:34 +0200
Hick Gunter  wrote:

> AFAIK SQLite treats GROUP BY the same way as ORDER BY (taken from
> hints in the virtual table description).

That might be so, in some limited sense.  It's obviously false in
general because they mean different things and have different effects.  

> If you have an index that covers the GROUP BY clause in field order,
> then aggregate functions need store only the current value; if not,
> then you need an ephemeral table to hold the aggregate values.

Nonsense.  The query parser sees GROUP BY A,B.  The optimizer sees an
index ordered B,A.  By permuting the order of the columns in the GROUP
BY clause, it finds a match for the index and uses it.  

Yes, the problem is O(n^2), where n is the number of columns in the
GROUP BY, but n is always small; even 7 columns could be checked in
less than 50 iterations.  

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

Re: [sqlite] Feature request: Support for aarch64

[Richard Hipp]

On Thu, Apr 25, 2013 at 10:22 AM, James K. Lowden
wrote:

> On Wed, 24 Apr 2013 19:17:32 -0700
> Dimiter 'malkia' Stanev  wrote:
>
> > > Why keep autoconf output in the source code repository?
> >
> > Maybe it's simply to guard from generating different ./configure
> > files on each machine depending on what autotools were installed (I
> > have very basic knowledge there so I could be wrong).
>
> The configure script is typically generated by a build machine that has
> autotools installed, and included in a distribution tarball.  The user
> unpacks the tarball and runs the configure script.  He doesn't need
> the autotools and he doesn't generate the scirpt.
>
> Anyone building from the Fossil repository should IMO be prepared to
> run autogen & co., and to have other ancillary tools ready as well.
>
>
If that is so, then there is no harm in including ./configure from an older
autoconf in the repo, since anybody who wants to build for an unusual
platform that the older autoconf did not support can simply rerun
autoconf.  Right?


-- 
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] Feature request: Support for aarch64

[James K. Lowden]

On Wed, 24 Apr 2013 19:17:32 -0700
Dimiter 'malkia' Stanev  wrote:

> > Why keep autoconf output in the source code repository?
> 
> Maybe it's simply to guard from generating different ./configure
> files on each machine depending on what autotools were installed (I
> have very basic knowledge there so I could be wrong).

The configure script is typically generated by a build machine that has
autotools installed, and included in a distribution tarball.  The user
unpacks the tarball and runs the configure script.  He doesn't need
the autotools and he doesn't generate the scirpt.  

Anyone building from the Fossil repository should IMO be prepared to
run autogen & co., and to have other ancillary tools ready as well.  

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

Re: [sqlite] Select WHERE IN List ordering

[Simon Slavin]

On 25 Apr 2013, at 1:58pm, Clemens Ladisch  wrote:

>  SELECT id
>  FROM pointslocation
>  WHERE id IN (1,7,3,4,5,2,6)
>  ORDER BY CASE id
>   WHEN 1 THEN 1
>   WHEN 7 THEN 2
>   WHEN 3 THEN 3
>   WHEN 4 THEN 4
>   WHEN 5 THEN 5
>   WHEN 2 THEN 6
>   WHEN 6 THEN 7
>   END

Clever.  Could generate that clause in code.

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

Re: [sqlite] Writing in a blob

[Stephen Lombardo]

On Tue, Apr 23, 2013 at 6:17 PM, James K. Lowden
wrote:

> On Tue, 23 Apr 2013 10:28:35 -0400
> Richard Hipp  wrote:
>
> > In summary:  No, a bare SQLite blob does not provide file-system
> > semantics.  But you can write a wrapper library around SQLite that
> > does provide file-system semantics for large blobs, and doing so
> > would have many advantages and be a worth-while project, I think.
>
> A better and simpler answer IMO would be FUSE
> (http://fuse.sourceforge.net/) backed by SQLite.  That gives you true
> file semantics, not that that's exactly a step forward.  ;-)
>

The Guardian Project has been doing some great work recently on libsqlfs,
which provides POSIX style file access using an SQLite database. It can
either be used as a standalone library or as a FUSE module:

https://github.com/guardianproject/libsqlfs

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

Re: [sqlite] Select WHERE IN List ordering

[Clemens Ladisch]

Gary Baranzini wrote:
> SELECT id  FROM pointslocation  WHERE id IN (1,7,3,4,5,2,6)
>
> How do I retain the order in the IN list?

If you don't want to create a (temporary) table for the ordering, you
can also do the mapping from id to the order in the query itself:

  SELECT id
  FROM pointslocation
  WHERE id IN (1,7,3,4,5,2,6)
  ORDER BY CASE id
   WHEN 1 THEN 1
   WHEN 7 THEN 2
   WHEN 3 THEN 3
   WHEN 4 THEN 4
   WHEN 5 THEN 5
   WHEN 2 THEN 6
   WHEN 6 THEN 7
   END




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

Re: [sqlite] Select WHERE IN List ordering

[Richard Hipp]

On Wed, Apr 24, 2013 at 5:09 PM, Gary Baranzini  wrote:

> Hi,
>
> I have the following query:
>
> SELECT id  FROM pointslocation  WHERE id IN (1,7,3,4,5,2,6)
>
> What 's returned is 1,2,3,4,5,6,7.
>

The output order for rows in a SELECT statement is undefined in SQL if you
do not use an ORDER BY clause.  The current SQLite implementation gives the
order you show above, but this might change from one release to the next,
so you should not depend on it.


>
> How do I retain the order in the IN list?
>

You would need to provide an ORDER BY clause that somehow coerces the
output into the order you desire.  That will be tricky in this case.
Probably you will need an auxiliary table.  Perhaps something like the
following:  (Warning - untested code)

   CREATE TABLE sortorder(x,y);
   INSERT INTO sortorder VALUES(1,1),(7,2),(3,3),(4,4),(5,5),(2,6),(6,7);

   SELECT id FROM pointslocation JOIN sortorder ON id=x
WHERE id IN (1,7,2,3,5,2,6)
  ORDER BY y;





> jb
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
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] Select WHERE IN List ordering

[Simon Slavin]

On 24 Apr 2013, at 10:09pm, Gary Baranzini  wrote:

> I have the following query:
> 
> SELECT id  FROM pointslocation  WHERE id IN (1,7,3,4,5,2,6)
> 
> What 's returned is 1,2,3,4,5,6,7.
> 
> How do I retain the order in the IN list?

There's no simple way to do that in SQL.  The numbers in the brackets is a set: 
an unordered collection.  A number is either in the list or not, the list has 
no inherent order.

If I wanted to select all rows from pointslocation in a particular order I'd 
probably make another column in pointslocation with the ordinals in.

If I wanted to select just a few records in that order I'd make another TABLE 
(possibly using CREATE TEMPORARY TABLE) which had the ordinals in ...

pl_id   sel_order
1   1
7   2
3   3
4   4
5   5
2   6
6   7

Then I'd use a SELECT JOIN to pull up the pointslocation rows in that order.  
Possibly something like

SELECT pointslocation.* FROM myTable JOIN pointslocation ON 
pointslocation.id=myTable.pl_id ORDER BY myTable.sel_order

but I can't test the above here so don't assume it's definitely going to work.

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

Re: [sqlite] Porting Sqlite to MQX Operating system

[Richard Hipp]

On Thu, Apr 25, 2013 at 7:38 AM, arun.pradeep  wrote:

>
> Hi Geoff
>
>   I am currently working on porting of SQLITE to MQX RTOS in MPC5125
> Platform.
>
> I am trying to rewrite the sqlite_os_init() for MQX and I have taken Win32
> VFS implementation as the reference.
>

Have you considered using the (much simpler) test_demovfs.c VFS as a
reference implementation?



>
> But I am not able to map all of the Win32 file system calls with that of
> MQX
> functions.
>
> Please advise me on the list of file system functions that can be
> implemented as part of VFS for MQX.
>
>
>
> Regards,
> Arun
>
>
> GeoffW wrote:
> >
> > hello
> >
> > I thought I had better update this and confess to my sins. Good job no
> one
> > is reading this thread as it is an embarassingly stupid mistake :rules:
> >
> > I cant believe I did this and then took so long to spot it.
> >
> > int sqlite3_os_init(void)
> > {
> > static sqlite3_vfs mqxVfs = {
> > 1, /* iVersion */
> > 0,   /* szOsFile ->ARRRGGGH <*/
> > MAX_PATH,  /* mxPathname */
> > etc 
> >
> > I started creating the ported file from osWin.c, I had quite a struggle
> > getting it to compile initially using CodeWarrior, so I was making
> several
> > temporary hacks to allow it to compile, which I then revisited later to
> > correct. I had replaced the sizeof(winFile) with a zero, and then forgot
> > to change it to sizeof(mqxFile).
> > This caused a fun crash down in the bowels of the paging code.
> >
> > Once I corrected this one liner it fixed the crash and sqlite is
> basically
> > up and running now. I still have a fair bit of work left to implement
> some
> > interface functions which are just dummy stubs at present, but I am
> making
> > progress now.
> >
> > Regards Geoff
> >
> >
> >
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/Porting-Sqlite-to-MQX-Operating-system-tp27792715p35333159.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Porting Sqlite to MQX Operating system

[arun.pradeep]

Hi Geoff

  I am currently working on porting of SQLITE to MQX RTOS in MPC5125
Platform. 

I am trying to rewrite the sqlite_os_init() for MQX and I have taken Win32
VFS implementation as the reference.

But I am not able to map all of the Win32 file system calls with that of MQX
functions.

Please advise me on the list of file system functions that can be
implemented as part of VFS for MQX.



Regards,
Arun 


GeoffW wrote:
> 
> hello 
> 
> I thought I had better update this and confess to my sins. Good job no one
> is reading this thread as it is an embarassingly stupid mistake :rules:
> 
> I cant believe I did this and then took so long to spot it.
> 
> int sqlite3_os_init(void)
> {
> static sqlite3_vfs mqxVfs = {
> 1, /* iVersion */
> 0,   /* szOsFile ->ARRRGGGH <*/
> MAX_PATH,  /* mxPathname */
> etc 
> 
> I started creating the ported file from osWin.c, I had quite a struggle
> getting it to compile initially using CodeWarrior, so I was making several
> temporary hacks to allow it to compile, which I then revisited later to
> correct. I had replaced the sizeof(winFile) with a zero, and then forgot
> to change it to sizeof(mqxFile).
> This caused a fun crash down in the bowels of the paging code.
> 
> Once I corrected this one liner it fixed the crash and sqlite is basically
> up and running now. I still have a fair bit of work left to implement some
> interface functions which are just dummy stubs at present, but I am making
> progress now.
> 
> Regards Geoff
> 
> 
> 
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Porting-Sqlite-to-MQX-Operating-system-tp27792715p35333161.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Porting Sqlite to MQX Operating system

[arun.pradeep]

Hi Geoff

  I am currently working on porting of SQLITE to MQX RTOS in MPC5125
Platform. 

I am trying to rewrite the sqlite_os_init() for MQX and I have taken Win32
VFS implementation as the reference.

But I am not able to map all of the Win32 file system calls with that of MQX
functions.

Please advise me on the list of file system functions that can be
implemented as part of VFS for MQX.



Regards,
Arun 


GeoffW wrote:
> 
> hello 
> 
> I thought I had better update this and confess to my sins. Good job no one
> is reading this thread as it is an embarassingly stupid mistake :rules:
> 
> I cant believe I did this and then took so long to spot it.
> 
> int sqlite3_os_init(void)
> {
> static sqlite3_vfs mqxVfs = {
> 1, /* iVersion */
> 0,   /* szOsFile ->ARRRGGGH <*/
> MAX_PATH,  /* mxPathname */
> etc 
> 
> I started creating the ported file from osWin.c, I had quite a struggle
> getting it to compile initially using CodeWarrior, so I was making several
> temporary hacks to allow it to compile, which I then revisited later to
> correct. I had replaced the sizeof(winFile) with a zero, and then forgot
> to change it to sizeof(mqxFile).
> This caused a fun crash down in the bowels of the paging code.
> 
> Once I corrected this one liner it fixed the crash and sqlite is basically
> up and running now. I still have a fair bit of work left to implement some
> interface functions which are just dummy stubs at present, but I am making
> progress now.
> 
> Regards Geoff
> 
> 
> 
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Porting-Sqlite-to-MQX-Operating-system-tp27792715p35333159.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request: Support for aarch64

[Dimiter 'malkia' Stanev]

On 4/23/2013 3:22 PM, James K. Lowden wrote:

On Tue, 23 Apr 2013 09:26:20 -0400
Richard Hipp  wrote:


I really dislike changing autoconf versions since any autoconf upgrade
results in a massive change in the generated "configure" script,
which is annoying to audit before each release, and which results in
exceedingly large and uninstructive diffs between successive versions.


Why keep autoconf output in the source code repository?

I'm not suggesting you should or shouldn't upgrade the version of
autoconf you happen to be using.  I find upgrading it to be a burden,
too, even without the added burden of effects on the repository.  But
having maintained a smaller project for a similar number of years, I've
never been tempted to archive configure scripts.

--jkl



Maybe it's simply to guard from generating different ./configure files 
on each machine depending on what autotools were installed (I have very 
basic knowledge there so I could be wrong).



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

Re: [sqlite] Writing in a blob

[Dimiter 'malkia' Stanev]

On 4/23/2013 3:17 PM, James K. Lowden wrote:

On Tue, 23 Apr 2013 10:28:35 -0400
Richard Hipp  wrote:


In summary:  No, a bare SQLite blob does not provide file-system
semantics.  But you can write a wrapper library around SQLite that
does provide file-system semantics for large blobs, and doing so
would have many advantages and be a worth-while project, I think.


A better and simpler answer IMO would be FUSE
(http://fuse.sourceforge.net/) backed by SQLite.  That gives you true
file semantics, not that that's exactly a step forward.  ;-)

--jkl



And on Windows one can use Dokan:
http://dokan-dev.net/en/download/

Or this commercial offering:
Callback file system (I'm not affiliated with them)
http://www.eldos.com/cbfs/

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

[sqlite] Select WHERE IN List ordering

[Gary Baranzini]

Hi,

I have the following query:

SELECT id  FROM pointslocation  WHERE id IN (1,7,3,4,5,2,6)

What 's returned is 1,2,3,4,5,6,7.

How do I retain the order in the IN list?

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

[sqlite] Port SQLite to VxWorks 6.8

[Peter Meszaros]

Hi All, First a very general question:
Does anyone have experiences with porting SQLite to VxWorks? Preferably to 
Version 6.8 but not much too older versions could be also interesting for 
me. The documentation and the directives like #if OS_VXWORKS in the source 
suggest it would be possible easily and strait forward. Basically it 
should be a DKM but I tried also as an RTP which is mentioned in the 
documents but I was not successful either of them. 
Second question if I manage to port and build and load sqlite3.c I would 
like to test if it works. So to port shell.c would be also useful for this 
test. Does anybody ported shell.c? Or how did you test your sqlite3.c port 
on VxWorks?

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

Re: [sqlite] Order of columns in group by statement affects query performance

[Hick Gunter]

AFAIK SQLite treats GROUP BY the same way as ORDER BY (taken from hints in the 
virtual table description).

If you have an index that covers the GROUP BY clause in field order, then 
aggregate functions need store only the current value; if not, then you need an 
ephemeral table to hold the aggregate values.

In more detail:

IF you have an index that covers the GROUP BY clause in field order, then 
retrieving rows in index order guarantees that all rows belonging to the same 
group will be retrieved in one block AND makes the output rows come out sorted 
too. This allows SQLite to keep current aggregate values in registers.

IF you do not have an index that covers the GROUP BY clause, then rows will be 
retrieved in some deterministic order other than by group. You need to retrieve 
and update the current aggregate values for the group each row is in. This 
forces SQLite to keep current aggregate values in an ephemeral table.

IF you have an index that covers the GROUP BY clause in any other order, then 
you still have the guarantee that all rows belonging to the same group will be 
retrieved together, but the result rows will be ordered in index order and not 
GROUP BY order. This would probably require code paths presumably shared by 
GROUP BY and ORDER BY processing to be split.


-Ursprüngliche Nachricht-
Von: James K. Lowden [mailto:jklow...@schemamania.org]
Gesendet: Donnerstag, 25. April 2013 01:55
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Order of columns in group by statement affects query 
performance

On Wed, 24 Apr 2013 17:46:00 +0100
Simon Slavin  wrote:

> On 24 Apr 2013, at 5:14pm, Igor Tandetnik  wrote:
> > Note though that the query doesn't have an ORDER BY clause. It
> > doesn't request rows in any particular order. SQLite could, in
> > principle, reorder columns in GROUP BY to take advantage of the
> > index. I suppose the optimizer just happens to miss this particular
> > opportunity.
>
> But the GROUP BY clause has an order:
>
> >> Query 1:  SELECT A,B,count(*) from tableTest group by A,B Query 2:
> >> SELECT A,B,count(*) from tableTest group by B,A

The order in which the columns appear syntactically in the GROUP BY clause is 
meaningless in SQL.

Igor is correct that the query processor could use any index beginning with B,A 
or A,B, should it so choose.

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