Re: [sqlite] replying to thread

[Mohit Sindhwani]

On 24/1/2012 3:02 PM, David Henry wrote:

How do I reply to a thread?

I put Re: Re: [thread title] into the subject but it comes out as a new
thread

e.g. I put  "Re: Re: [sqlite] ftruncate implementation" in the subject field
but a new thread "[sqlite] ftruncate implementation" was started.




Normally, you should just need to do a "reply", "reply all" or "reply 
List" to an email in the thread - that should do it!


Best Regards,
Mohit.
24/1/2012 | 3:11 PM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] List of DB virtual modules

[Max Vlasov]

On Mon, Jan 23, 2012 at 5:36 PM, Richard Hipp  wrote:

> On Mon, Jan 23, 2012 at 8:06 AM, Max Vlasov  wrote:
> >
> > it there an easy way (without manual statements parsing) to get the list
> of
> > modules used in all (if any) virtual tables statements of the schema? ...
> >
>
> There is no way to do that right now, other than manually parsing the
> statements.
>
>
Thanks, Richard, it's interesting that some entities need support with
pragmas, but sqlite_master is so natural to work with tables, there was
never much need for special pragma for obtaining properties of tables.

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

[sqlite] replying to thread

[David Henry]

How do I reply to a thread?

I put Re: Re: [thread title] into the subject but it comes out as a new
thread

e.g. I put  "Re: Re: [sqlite] ftruncate implementation" in the subject field
but a new thread "[sqlite] ftruncate implementation" was started.

 

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

Re: [sqlite] ftruncate implementation

[David Henry]

I am working without an operating system so there are no other agents trying
to steal data. Bearing that in mind, is it still necessary to actually write
zero data to the sectors allocated? Is SQLite expecting it?

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

Re: [sqlite] Automatic join reordering doesn't seem to work?

[Ryan Johnson]

On 23/01/2012 7:24 PM, Ryan Johnson wrote:

On 23/01/2012 3:09 PM, Ryan Johnson wrote:

On 23/01/2012 12:51 PM, Richard Hipp wrote:
On Mon, Jan 23, 2012 at 12:48 PM, Simon 
Slavin  wrote:
I don't know if Dr Hipp is pursuing this privately or expecting it 
to be

solved collaboratively on this list.


I don't have a test case to work on.
My database file is ~150MB, but was generated by the TPC-H data 
generator program. Assuming a linux-like environment (including 
cygwin), the following will reproduce the setup in under five minutes:
The problem persists with a freshly-generated database on my machine, 
using a just-compiled sqlite-3.7.10.


OK, it looks like I didn't install the 3.7.10 binary properly, because 
the situation is significantly different once ANALYZE has run.


The first query from the OP is remarkably similar to a query mentioned 
in where.c (from the 3.7.5 sources, which I happened to have handy):
Previous versions of SQLite [did not always find the lowest-cost 
plan] for scripts such as the following:

**
**   CREATE TABLE t1(a, b);
**   CREATE TABLE t2(c, d);
**   SELECT * FROM t2, t1 WHERE t2.rowid = t1.a;
**
The best strategy is to iterate through table t1 first. However it is 
not possible to determine this with a simple greedy algorithm. Since 
the cost of a linear scan through table t2 is the same as the cost of 
a linear scan through table t1, a simple greedy algorithm may choose 
to use t2 for the outer loop, which is a much costlier approach.


Re-casting my first query as the above gives:
sqlite> explain query plan select * from customer t2, orders t1 where 
t2.rowid = t1.custkey;

0|0|0|SCAN TABLE customer AS t2 (~15000 rows)
0|1|1|SEARCH TABLE orders AS t1 USING INDEX OrderCustomers 
(custKey=?) (~15 rows)


The same query plan is chosen if t1 comes first; execution times and 
stats confirm the improvement.


For query 2, the situation is less clear. First of all, it appears to 
execute more than 4x faster overall (10s and .2s -- WOW!) but the 
optimizer still seems to choose the wrong plan (13.6M rows examined):

1|0|0|SCAN TABLE orders (~37500 rows)
1|0|0|USE TEMP B-TREE FOR DISTINCT
0|0|2|SCAN SUBQUERY 1 AS Y (~37500 rows)
0|1|0|SCAN TABLE orders AS O (~75000 rows)
0|2|1|SEARCH TABLE lineitem AS L USING INDEX LineItemOrders 
(orderKey=?) (~2 rows)


If I force the proper ordering, "X cross join Y", then the following, 
better-than-expected plan is used instead (only 300k rows examined!):

1|0|0|SCAN TABLE orders (~37500 rows)
1|0|0|USE TEMP B-TREE FOR DISTINCT
0|0|0|SCAN TABLE orders AS O (~75000 rows)
0|1|1|SEARCH TABLE lineitem AS L USING INDEX LineItemOrders 
(orderKey=?) (~5 rows)

0|2|2|SCAN SUBQUERY 1 AS Y (~18750 rows)


The main difference seems to be that Y should be the innermost loop 
but isn't.

Update: Running the different pieces alone shows:
- The scan of Orders for Y returns 5681 rows (not 37k)
- The temp B+Tree collapses that to 90 distinct rows
- The scan of Orders for X returns 104k rows (75k is the right ball-park)
- Each order averages 4 line items (5 was a pretty good estimate)
- Subquery 1 as Y has 90 rows (not 18k or 37k)

Given the above, the problem seems to lie with cardinality estimation, 
particularly because the estimate for DISTINCT's effectiveness was 
vastly overconservative. I assume the cardinality estimates in the two 
plans vary for the reason mentioned in where.c, that the estimated cost 
of a scan might not be as low when it's in the outer loop because 
certain (index-related?) cardinality reductions only happen in the 
innermost loop.


While I certainly won't complain if the above weakness could be fixed, 
at this point I'm satisfied that there's not an easy code tweak (or 
glaring omission in the SQL) to make the problem go away...


Ryan

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

Re: [sqlite] Automatic join reordering doesn't seem to work?

[Ryan Johnson]

On 23/01/2012 3:09 PM, Ryan Johnson wrote:

On 23/01/2012 12:51 PM, Richard Hipp wrote:
On Mon, Jan 23, 2012 at 12:48 PM, Simon Slavin  
wrote:
I don't know if Dr Hipp is pursuing this privately or expecting it 
to be

solved collaboratively on this list.


I don't have a test case to work on.
My database file is ~150MB, but was generated by the TPC-H data 
generator program. Assuming a linux-like environment (including 
cygwin), the following will reproduce the setup in under five minutes:
The problem persists with a freshly-generated database on my machine, 
using a just-compiled sqlite-3.7.10.


OK, it looks like I didn't install the 3.7.10 binary properly, because 
the situation is significantly different once ANALYZE has run.


The first query from the OP is remarkably similar to a query mentioned 
in where.c (from the 3.7.5 sources, which I happened to have handy):
Previous versions of SQLite [did not always find the lowest-cost plan] 
for scripts such as the following:

**
**   CREATE TABLE t1(a, b);
**   CREATE TABLE t2(c, d);
**   SELECT * FROM t2, t1 WHERE t2.rowid = t1.a;
**
The best strategy is to iterate through table t1 first. However it is 
not possible to determine this with a simple greedy algorithm. Since 
the cost of a linear scan through table t2 is the same as the cost of 
a linear scan through table t1, a simple greedy algorithm may choose 
to use t2 for the outer loop, which is a much costlier approach.


Re-casting my first query as the above gives:
sqlite> explain query plan select * from customer t2, orders t1 where 
t2.rowid = t1.custkey;

0|0|0|SCAN TABLE customer AS t2 (~15000 rows)
0|1|1|SEARCH TABLE orders AS t1 USING INDEX OrderCustomers (custKey=?) 
(~15 rows)


The same query plan is chosen if t1 comes first; execution times and 
stats confirm the improvement.


For query 2, the situation is less clear. First of all, it appears to 
execute more than 4x faster overall (10s and .2s -- WOW!) but the 
optimizer still seems to choose the wrong plan (13.6M rows examined):

1|0|0|SCAN TABLE orders (~37500 rows)
1|0|0|USE TEMP B-TREE FOR DISTINCT
0|0|2|SCAN SUBQUERY 1 AS Y (~37500 rows)
0|1|0|SCAN TABLE orders AS O (~75000 rows)
0|2|1|SEARCH TABLE lineitem AS L USING INDEX LineItemOrders 
(orderKey=?) (~2 rows)


If I force the proper ordering, "X cross join Y", then the following, 
better-than-expected plan is used instead (only 300k rows examined!):

1|0|0|SCAN TABLE orders (~37500 rows)
1|0|0|USE TEMP B-TREE FOR DISTINCT
0|0|0|SCAN TABLE orders AS O (~75000 rows)
0|1|1|SEARCH TABLE lineitem AS L USING INDEX LineItemOrders 
(orderKey=?) (~5 rows)

0|2|2|SCAN SUBQUERY 1 AS Y (~18750 rows)


The main difference seems to be that Y should be the innermost loop but 
isn't.


Thoughts?
Ryan


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

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Mon, Jan 23, 2012 at 4:27 PM, Nico Williams wrote:

> On Mon, Jan 23, 2012 at 3:02 PM, John Elrick 
> wrote:
> > I think I can inject something to do some measurements.  I seem to
> recall,
> > however, that there was no substantive difference in the number of
> > times sqlite3RunParser
> > was called between the two.  I'll check for:
> >
> > which query is being parsed
> > how many times that particular query is parsed
> > how many mallocs are stemming from that particular query.
> >
> > Maybe that will tell us something.
>
> I think it's fair to say that compiling a statement is expected to be
> heavy-duty, but evaluating a compiled statement is expected to be
> light-weight (not counting the work that the statement implies doing)
> unless something triggers recompilation of the prepared statement.
>
> So the key, really, is to find out what's triggering the recompilation
> of your statements.
>
>
...assuming that they ARE being recompiled.  As I indicated, IIRC they are
not.  The issue may stem from the other thing I noticed, a substantial drop
in the size of the average request to _malloc (by an order of magnitude).
 We shall see tomorrow; I'm done for the day.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Xcode warning on 3.7.10

[Kyle McKay]

On Jan 23, 2012 05:43:06 PST, Richard Hipp wrote:

On Mon, Jan 23, 2012 at 12:49 AM, Tommy wrote:

To whom it may concern,

After bringing the SQLite amalgamation into my library and compiling,
Apple Xcode produced the following warning:

sqlite3.c:27620:32:{27620:32-27620:45}{27620:30-27620:31}: warning:
implicit conversion from 'long long' to 'long' changes value from
9223372036854775807 to -1 [-Wconstant-conversion,3]

The code in question is:

#define LARGEST_INT64  (0x|(((i64)0x7fff)<<32))

I hope this helps. If at all possible, I would appreciate learning
what the issue is, as I'm new to C.


I don't know why this warning is coming up.  We don't use Xcode,  
preferring
instead to use ordinary text editors (vi and emacs) and invoke the  
compiler
directly.  And we don't get the above warning on Macs (having tried  
it on

Leopard, SnowLeopard, and Lion).



Using the sqlite-amalgamation-3071000,
here's the full clang warning which is considerably more helpful:

sqlite3.c:27620:32: warning: implicit conversion from 'long long' to  
'long' changes value from 9223372036854775807 to -1 [-Wconstant- 
conversion]

mask = (sizeof(long)==8) ? LARGEST_INT64 : 0x7fff;
 ~ ^
sqlite3.c:7946:25: note: expanded from:
#define LARGEST_INT64  (0x|(((i64)0x7fff)<<32))
^~
1 warning generated.

On line 27615, mask is defined to be of type long, so the #define is  
not the problem, the assignment is.  It's a pity the clang optimizer  
didn't kick in first and eliminate the conditional operator before  
spewing the warning since the "sizeof(long)==8" is a compile-time  
constant.


If you compile the amalgamation with the gcc option -Wshorten-64-to-32  
(you need Apple's gcc for this option) then you get:


sqlite3.c: In function 'afpLock':
sqlite3.c:27620: warning: implicit conversion shortens 64-bit value  
into a 32-bit value

sqlite3.c: In function 'Unlock':
sqlite3.c:27764: warning: implicit conversion shortens 64-bit value  
into a 32-bit value


Line 27764 is:

int sharedLockByte = SHARED_FIRST+pInode->sharedByte;

And sharedByte is indeed a long long.

So changing the first line to:

mask = (sizeof(long)==8) ? (long)LARGEST_INT64 : 0x7fff;

and the second to:

int sharedLockByte = (int)(SHARED_FIRST+pInode->sharedByte);

gets rid of the warnings both from clang and from apple's gcc with the  
-Wshorten-64-to-32 warning enabled.  That second one is a bit  
concerning though if sqlite should ever be compiled on a system where  
sizeof(int) < 4 and __APPLE__ is defined and  
SQLITE_ENABLE_LOCKING_STYLE is set since SHARED_FIRST requires 32  
bits.  Presumably that can't happen although I wonder about Mac OS  
Classic perhaps if anyone's still using that it might be an issue there.


clang (clang.llvm.org) is widely available on Linux (Ubuntu package  
since 10.04) and *BSD (FreeBSD includes clang by default starting with  
version 9.0).


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

Re: [sqlite] Some pieces of the puzzle are coming together

[Nico Williams]

On Mon, Jan 23, 2012 at 3:02 PM, John Elrick  wrote:
> I think I can inject something to do some measurements.  I seem to recall,
> however, that there was no substantive difference in the number of
> times sqlite3RunParser
> was called between the two.  I'll check for:
>
> which query is being parsed
> how many times that particular query is parsed
> how many mallocs are stemming from that particular query.
>
> Maybe that will tell us something.

I think it's fair to say that compiling a statement is expected to be
heavy-duty, but evaluating a compiled statement is expected to be
light-weight (not counting the work that the statement implies doing)
unless something triggers recompilation of the prepared statement.

So the key, really, is to find out what's triggering the recompilation
of your statements.

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

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Mon, Jan 23, 2012 at 3:48 PM, Richard Hipp  wrote:

> On Mon, Jan 23, 2012 at 3:12 PM, John Elrick  >wrote:
>
> > Brain hurts...
> >
> > Richard, more information for you.  I rebuilt the call stack checking
> > system into pure Delphi and confirmed that yy_reduce appears to be the
> > malloc culprit.  I further created a pair of procedures which I can use
> to
> > track the yyruleno from yy_reduce as though it were a call.  Below is a
> > representative sample of the data
> >
>
> The parser does lots of little mallocs as it builds a parse tree.  So I
> expect it to generate a lot of malloc traffic.  The question is why the
> parser is being called so much.
>
> Can you put a printf() or something at
> http://www.sqlite.org/src/artifact/1e86210d3976?ln=397 and figure out what
> is being parsed so excessively?
>
>
I think I can inject something to do some measurements.  I seem to recall,
however, that there was no substantive difference in the number of
times sqlite3RunParser
was called between the two.  I'll check for:

which query is being parsed
how many times that particular query is parsed
how many mallocs are stemming from that particular query.

Maybe that will tell us something.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[Richard Hipp]

On Mon, Jan 23, 2012 at 3:12 PM, John Elrick wrote:

> Brain hurts...
>
> Richard, more information for you.  I rebuilt the call stack checking
> system into pure Delphi and confirmed that yy_reduce appears to be the
> malloc culprit.  I further created a pair of procedures which I can use to
> track the yyruleno from yy_reduce as though it were a call.  Below is a
> representative sample of the data
>

The parser does lots of little mallocs as it builds a parse tree.  So I
expect it to generate a lot of malloc traffic.  The question is why the
parser is being called so much.

Can you put a printf() or something at
http://www.sqlite.org/src/artifact/1e86210d3976?ln=397 and figure out what
is being parsed so excessively?

>
> One interesting thing I noticed is that in 3.7.5, the average <1kb malloc
> request size is 181.95 bytes.  In 3.7.6 that average request size drops
> to 17.74 bytes.
>
> Both examples are running the same dataset.
>
> versionmallocs   mallocs by yy_reduce
> 3.7.5  838,789   5,545
> 3.7.6   70,003,878  68,870,137
>
> Major yy_reduce case consumers
>
> 3.7.5
> Count:947 = 'case_112
> yy_reduce'
> Count:696 = 'case_189
> yy_reduce'
> Count:537 = 'case_173
> yy_reduce'
> Count:229 = 'case_243
> yy_reduce
> case_173
> yy_reduce'
> Count:214 = 'case_37
> yy_reduce
> case_173
> yy_reduce'
>
>
> 3.7.6
> Count:  5,606,345 = 'case_112
> yy_reduce'
> Count:  5,267,458 = 'case_243
> yy_reduce
> case_112
> yy_reduce'
> Count:  4,922,428 = 'case_37
> yy_reduce
> case_112
> yy_reduce'
> Count:  4,623,402 = 'case_189
> yy_reduce
> case_112
> yy_reduce'
> Count:  4,278,372 = 'case_8
> yy_reduce
> case_112
> yy_reduce'
> ___
> 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] storing and comparing dates in sqlite

[dotolee]

i think i got it. 

echo date("M-d-Y H:i:s", mktime());
date() is php. 

thanks. 


dotolee wrote:
> 
> i'm storing as integers now in the database. 
> getting the unix time in seconds using the mktime() method. 
> now i just need to figure out how to display properly. 
> 
> for example, in the sample below, i'm having a hard time getting the
> minutes to show up as minutes.  right now, the "m" is displaying the
> month. 
> 
> echo date("M-d-Y H:m:s", mktime());
> 
> i've read http://www.sqlite.org/lang_datefunc.html but i can't seem to get
> it to work.
> 
> 
> Simon Slavin-3 wrote:
>> 
>> 
>> On 23 Jan 2012, at 5:53pm, Stephan Beal wrote:
>> 
>>> On Mon, Jan 23, 2012 at 6:51 PM, dotolee  wrote:
>>> 
 i'm new to sqlite... and relatively new to php.  just wondering what
 the
 best way is to store and compare dates.
>>> 
>>> For any given 10 developers you'll likely hear 11 opinions on this
>>> topic.
>> 
>> How true.  For what it's worth, here's mine:
>> 
>> 1) read
>> 
>> http://www.sqlite.org/lang_datefunc.html
>> 
>> 2) If you need to do maths on dates (how many days apart are two dates,
>> what's three days after this date, etc.) store your dates as numbers,
>> either julianday or unixepoch.
>> 
>> 3) Otherwise store them as text, in MMDD format, so they're easy to
>> read when you're debugging.
>> 
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
> 
> 

-- 
View this message in context: 
http://old.nabble.com/storing-and-comparing-dates-in-sqlite-tp33189769p33190888.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] storing and comparing dates in sqlite

[dotolee]

i'm storing as integers now in the database. 
getting the unix time in seconds using the mktime() method. 
now i just need to figure out how to display properly. 

for example, in the sample below, i'm having a hard time getting the minutes
to show up as minutes.  right now, the "m" is displaying the month. 

echo date("M-d-Y H:m:s", mktime());

i've read http://www.sqlite.org/lang_datefunc.html but i can't seem to get
it to work.


Simon Slavin-3 wrote:
> 
> 
> On 23 Jan 2012, at 5:53pm, Stephan Beal wrote:
> 
>> On Mon, Jan 23, 2012 at 6:51 PM, dotolee  wrote:
>> 
>>> i'm new to sqlite... and relatively new to php.  just wondering what the
>>> best way is to store and compare dates.
>> 
>> For any given 10 developers you'll likely hear 11 opinions on this topic.
> 
> How true.  For what it's worth, here's mine:
> 
> 1) read
> 
> http://www.sqlite.org/lang_datefunc.html
> 
> 2) If you need to do maths on dates (how many days apart are two dates,
> what's three days after this date, etc.) store your dates as numbers,
> either julianday or unixepoch.
> 
> 3) Otherwise store them as text, in MMDD format, so they're easy to
> read when you're debugging.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/storing-and-comparing-dates-in-sqlite-tp33189769p33190861.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] mktime() always returns same time minute value.

[Igor Tandetnik]

On 1/23/2012 2:42 PM, Petite Abeille wrote:


On Jan 23, 2012, at 8:39 PM, dotolee wrote:


print "".date('Y-m-d h:m:s',$row['updated'])."";


%m  month: 01-12
%M  minute: 00-59

Check the fine manual:

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


Or rather, http://php.net/manual/en/function.date.php , since the OP is 
using PHP's date() function, not SQLite's. Therefore, the correct format 
appears to be 'Y-m-d H:i:s'

--
Igor Tandetnik

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

Re: [sqlite] storing and comparing dates in sqlite

[Igor Tandetnik]

On 1/23/2012 12:51 PM, dotolee wrote:

i'm new to sqlite... and relatively new to php.  just wondering what the best
way is to store and compare dates.
right now i have something like this in my php code:
$curr_date_time = strftime('%Y-%m-%d %H:%M:%S');
i'm inserting this into a field in the database that has been defined as
type TEXT because from what i read, sqlite does not have a date datatype.
but question is how can i write a select statement that will sort by date?


select * from MyTable order by MyDateColumn;

With this format, alphabetical order happens to match calendar order. No 
additional magic required.



the sql i have right now is not working because it's not treating the field
as a true date...


Show the SQL you have right now, sample data, and the query results. 
Explain how actual results differ from your expectations.



i want to do something like:

select field1, field2, updated from myhistory group by field1 order by
updated desc


This query doesn't make much sense. Suppose you have ten rows with 
field1='X', but with different values of field2 and updated. GROUP BY 
clause will produce one row representing this group, where field1='X', 
while field2 and updated would be taken from some random row in the 
group (not even necessarily the same one). Is this what you had in mind?

--
Igor Tandetnik

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

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

Brain hurts...

Richard, more information for you.  I rebuilt the call stack checking
system into pure Delphi and confirmed that yy_reduce appears to be the
malloc culprit.  I further created a pair of procedures which I can use to
track the yyruleno from yy_reduce as though it were a call.  Below is a
representative sample of the data.

One interesting thing I noticed is that in 3.7.5, the average <1kb malloc
request size is 181.95 bytes.  In 3.7.6 that average request size drops
to 17.74 bytes.

Both examples are running the same dataset.

versionmallocs   mallocs by yy_reduce
3.7.5  838,789   5,545
3.7.6   70,003,878  68,870,137

Major yy_reduce case consumers

3.7.5
Count:947 = 'case_112
yy_reduce'
Count:696 = 'case_189
yy_reduce'
Count:537 = 'case_173
yy_reduce'
Count:229 = 'case_243
yy_reduce
case_173
yy_reduce'
Count:214 = 'case_37
yy_reduce
case_173
yy_reduce'


3.7.6
Count:  5,606,345 = 'case_112
yy_reduce'
Count:  5,267,458 = 'case_243
yy_reduce
case_112
yy_reduce'
Count:  4,922,428 = 'case_37
yy_reduce
case_112
yy_reduce'
Count:  4,623,402 = 'case_189
yy_reduce
case_112
yy_reduce'
Count:  4,278,372 = 'case_8
yy_reduce
case_112
yy_reduce'
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ANNOUNCE: Sqlite Root Gui Admin Tool for manage Sqlite Database

[Tilsley, Jerry M.]

Tried to download, says the installation is corrupt.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Fabio Spadaro
Sent: Monday, January 23, 2012 2:35 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] ANNOUNCE: Sqlite Root Gui Admin Tool for manage Sqlite 
Database

Announcing--
>
>
 Sqlite Root version 1.9.73 has been released and is now available for

 download at http://www.sqliteroot.com .

For any request please contact sqliter...@gmail.com

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

Disclaimer
This email is confidential and intended solely for the use of the individual to 
whom it is addressed.  Any views or opinions presented are solely those of the 
author and do not necessarily represent those of St. Claire Regional Medical 
Center.  If you are not the intended recipient, be advised that you have 
received this email in error and that any use, dissemination, forwarding, 
printing or copying of the email is strictly prohibited.  If you received this 
email in error please notify the St. Claire Regional Helpdesk by telephone at 
606-783-6565.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Automatic join reordering doesn't seem to work?

[Ryan Johnson]

On 23/01/2012 12:51 PM, Richard Hipp wrote:

On Mon, Jan 23, 2012 at 12:48 PM, Simon Slavin  wrote:


On 23 Jan 2012, at 4:30pm, Ryan Johnson wrote:


Bump?



   I don't know if Dr Hipp is pursuing this privately or expecting it to be
solved collaboratively on this list.


I don't have a test case to work on.
My database file is ~150MB, but was generated by the TPC-H data 
generator program. Assuming a linux-like environment (including cygwin), 
the following will reproduce the setup in under five minutes:


1. Download http://www.tpc.org/tpch/spec/tpch_2_13_0.tar.gz and extract
   the files (warning: they unpack to the current directory by default)
2. Copy/rename makefile.suite to Makefile
3. Edit 'Makefile' and set the following four variables (about 100
   lines into the file, just past the revision history):
 * CC=$YOUR_COMPILER
 * DATABASE=SQLSERVER
 * MACHINE=LINUX
 * WORKLOAD=TPCH
4. Edit 'driver.c' and divide all large magic numbers by 10 (lines
   181-195):
 * Example: {"part.tbl", "part table", 2, /* originally 20 */
5. Invoke 'make'
6. Invoke './dbgen -vf' (creates several .tbl files)
7. Create a new sqlite3 database using the attached tpch-schema.ddl file
8. Use .import to load customer.tbl and orders.tbl into the tables with
   the same name
9. Run ANALYZE (doesn't seem to make a difference)
10. Run the problem queries from the OP

The problem persists with a freshly-generated database on my machine, 
using a just-compiled sqlite-3.7.10.


Regards,
Ryan

create table Part(
partkey INTEGER PRIMARY KEY,
name,
mfgr,
brand,
type,
size INTEGER,
container,
retailprice REAL,
comment,
dummy);

create table Supplier(
suppKey INTEGER PRIMARY KEY,
name, address,
nationkey INTEGER,
phone,
acctbal REAL,
comment,
dummy);

create table PartSupp(
partKey INTEGER,
suppKey INTEGER,
availqty  INTEGER,
supplycost REAL,
comment,
dummy);

create table Customer(
   CustKey INTEGER PRIMARY KEY,
   name,
   address,
   nationkey INTEGER,
   phone,
   acctbal REAL,
   mktsegment,
   comment,
dummy);

create table Nation(
nationkey  INTEGER PRIMARY KEY,
name,
regionkey INTEGER,
comment,
dummy);

create table Region(
regionkey INTEGER PRIMARY KEY,
name,
comment,
dummy);

create table LineItem(
orderKey INTEGER,
partKey INTEGER,
suppKey INTEGER,
lineNumber INTEGER,
quantity INTEGER,
extendedPrice REAL,
discount REAL,
tax REAL,
returnFlag,
lineStatus,
shipDate DATETIME,
commitDate DATETIME,
receiptDate DATETIME,
shipInstruct,
shipMode,
comment,
dummy);

create table Orders(
orderKey INTEGER PRIMARY KEY,
custKey INTEGER,
orderStatus,
totalPrice REAL,
orderDate DATETIME,
orderPriority,
clerk,
shipPriority,
comment,
dummy);

create index SupplierNations on Supplier(nationkey);
create index CustomerNations on Customer(nationkey);
create index LineItemParts on LineItem(partkey);
create index LineItemSuppliers on LineItem(suppkey);
create index LineItemOrders on LineItem(orderkey);
create index OrderCustomers on Orders(custKey);
create index PartSuppSupp on PartSupp(suppkey);

create index OrderDate on Orders(orderDate);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C# amalgamation hand holding requested

[Simon Slavin]

On 23 Jan 2012, at 7:39pm, Roosevelt Anderson wrote:

> The C# compiler does not compile C. C# and VB.NET get compiled down to 
> bytecode.
> 
> On Mon, Jan 23, 2012 at 12:56 PM, Simon Slavin  wrote:
>> 
>> On 23 Jan 2012, at 4:39pm, Roosevelt Anderson wrote:
>> 
>>> You can't use the amalgamation directly in C# as the SQLite
>>> amalgamation is in C.
>> 
>> Can you not simply tell your compiler that '.c' files are C and not C# ?  
>> That's what you do with Objective-C.  Or do C# compilers not compile C ?

Ah.  So C# is not a superset of C.  That explains things.  Thanks.

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

Re: [sqlite] mktime() always returns same time minute value.

[Petite Abeille]

On Jan 23, 2012, at 8:39 PM, dotolee wrote:

>   print "".date('Y-m-d h:m:s',$row['updated'])."";

%m  month: 01-12
%M  minute: 00-59

Check the fine manual:

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

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

[sqlite] mktime() always returns same time minute value.

[dotolee]

hi there.
can someone tell me what i'm doing wrong? I'm using mktime() to save current
date / time stamp into my database as an INT. 
then when i'm displaying the data, i use the date() function to format it. 
here's how i create my time stamp variable in php:

$curr_time_stamp = mktime();

here's my logic to display my data: 

foreach($result as $row)
{
print "".$row['id']."";
print "".$row['field1']."";
print "".$row['field2']."";
print "".date('Y-m-d h:m:s',$row['updated'])."";
}
on my web page, the time always appears as:
2012-01-23 02:01:03
where the minutes never change.  it's always 2:01.  but the seconds
increment/change. 
any ideas on what i'm doing wrong? 
-- 
View this message in context: 
http://old.nabble.com/mktime%28%29-always-returns-same-time-minute-value.-tp33190523p33190523.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] C# amalgamation hand holding requested

[Roosevelt Anderson]

The C# compiler does not compile C. C# and VB.NET get compiled down to bytecode.

On Mon, Jan 23, 2012 at 12:56 PM, Simon Slavin  wrote:
>
> On 23 Jan 2012, at 4:39pm, Roosevelt Anderson wrote:
>
>> You can't use the amalgamation directly in C# as the SQLite
>> amalgamation is in C.
>
> Can you not simply tell your compiler that '.c' files are C and not C# ?  
> That's what you do with Objective-C.  Or do C# compilers not compile C ?
>
> 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] ANNOUNCE: Sqlite Root Gui Admin Tool for manage Sqlite Database

[Fabio Spadaro]

Announcing--
>
>
 Sqlite Root version 1.9.73 has been released and is now available for

 download at http://www.sqliteroot.com .

For any request please contact sqliter...@gmail.com

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

Re: [sqlite] Automatic join reordering doesn't seem to work?

[Richard Hipp]

On Mon, Jan 23, 2012 at 1:09 PM, Ryan Johnson
wrote:

> On 23/01/2012 12:48 PM, Simon Slavin wrote:On 21/01/2012 12:49 PM, Ryan
> Johnson wrote:
>
>> consider the following query:
>>
>> select count(*) from orders O, Customer C where C.custkey=O.custkey and
>> C.name like '%115';
>>
>> .stats/explain reports 14 fullscan steps for the query plan:
>>
>>> 0|0|TABLE orders AS O
>>> 1|1|TABLE Customer AS C USING PRIMARY KEY
>>>
>>
>> Putting Customer first in the FROM clause makes the query markedly faster
>> and executes only 14999 fullscan steps. The query plan confirms the change:
>>
>>> 0|0|TABLE Customer AS C
>>> 1|1|TABLE orders AS O WITH INDEX OrderCustomers
>>>
>>
> In what way does the above query from the OP not sufficient to demonstrate
> the problem?
>


I don't have your database file.  I cannot run the scenario myself to see
what is going on.

-- 
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] Automatic join reordering doesn't seem to work?

[Ryan Johnson]

On 23/01/2012 12:48 PM, Simon Slavin wrote:

On 23 Jan 2012, at 4:30pm, Ryan Johnson wrote:


Bump?

On 21/01/2012 2:47 PM, Ryan Johnson wrote:

On 21/01/2012 2:44 PM, Simon Slavin wrote:

On 21 Jan 2012, at 7:23pm, Ryan Johnson wrote:


It's a SQL89 join. Sqlite docs say SQL89 and SQL92 joins perform identically 
[1], which I confirmed before sending the OP.

Oh.  Okay.  If it spits out the same EXPLAIN QUERY PLAN then SQLite is 
interpreting it the same way.

Which brings us back to the original question: why does sqlite spit out a bad 
query plan when a vastly better one exists? There's no clear reason the better 
answer should have been hard to find.

Well, the approach would be to find a specific query, post the query and query 
plan, then to simplify the query until the strangeness in the query plan goes 
away.  The last simplification is the one that caused the problem.


On 21/01/2012 12:49 PM, Ryan Johnson wrote:

consider the following query:

select count(*) from orders O, Customer C where C.custkey=O.custkey 
and C.name like '%115';


.stats/explain reports 14 fullscan steps for the query plan:

0|0|TABLE orders AS O
1|1|TABLE Customer AS C USING PRIMARY KEY


Putting Customer first in the FROM clause makes the query markedly 
faster and executes only 14999 fullscan steps. The query plan confirms 
the change:

0|0|TABLE Customer AS C
1|1|TABLE orders AS O WITH INDEX OrderCustomers


In what way does the above query from the OP not sufficient to 
demonstrate the problem? There are only two tables involved. There are 
only two relevant indexes -- the automatically-created PK and manually 
created index the query *should* use; I don't think it's reasonable to 
suggest dropping the primary key index. Changing "count(*)" to "*" 
doesn't change the chosen access path for the query. Removing the 
predicate doesn't affect the access path either, thought it does 
increase by 1000x the number of rows returned (runtime stays about the 
same).


Given that ANALYZE ran and established |Customer|=15k vs. |Orders|=150k, 
why does the optimizer choose to do a full table scan of Orders, instead 
of scanning Customer and accessing Orders using the index created 
specifically for that purpose? (It's even marked as a foreign key in the 
schema, though I'm pretty sure sqlite doesn't pay attention to that). 
This seems a cut-and-dried join reordering scenario that the optimizer 
should eat for lunch, and instead it seems to limit its search to 
selecting the best index(es) to use given the join ordering the original 
SQL specified. If the optimizer were merely making a bad decision -- 
which the second example of the OP seems to demonstrate -- then it would 
reorder (wrongly) the "correct" version of the SQL; it does not.


Thoughts?
Ryan

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

Re: [sqlite] storing and comparing dates in sqlite

[Simon Slavin]

On 23 Jan 2012, at 5:53pm, Stephan Beal wrote:

> On Mon, Jan 23, 2012 at 6:51 PM, dotolee  wrote:
> 
>> i'm new to sqlite... and relatively new to php.  just wondering what the
>> best way is to store and compare dates.
> 
> For any given 10 developers you'll likely hear 11 opinions on this topic.

How true.  For what it's worth, here's mine:

1) read

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

2) If you need to do maths on dates (how many days apart are two dates, what's 
three days after this date, etc.) store your dates as numbers, either julianday 
or unixepoch.

3) Otherwise store them as text, in MMDD format, so they're easy to read 
when you're debugging.

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

Re: [sqlite] storing and comparing dates in sqlite

[Stephan Beal]

On Mon, Jan 23, 2012 at 7:01 PM, dotolee  wrote:

> can you point me in the right direction?
> aka.  what data type am i using to store the date in my sqlite database? is
> TEXT correct?
> how do I do a select on it?
>

http://en.wikipedia.org/wiki/Unix_time

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] storing and comparing dates in sqlite

[dotolee]

can you point me in the right direction? 
aka.  what data type am i using to store the date in my sqlite database? is
TEXT correct? 
how do I do a select on it? 
thanks.


Stephan Beal-3 wrote:
> 
> On Mon, Jan 23, 2012 at 6:51 PM, dotolee  wrote:
> 
>>
>> i'm new to sqlite... and relatively new to php.  just wondering what the
>> best
>> way is to store and compare dates.
>>
> 
> For any given 10 developers you'll likely hear 11 opinions on this topic.
> IMO Unix timestamps are the most portable form out there. "Portable"
> meaning, in this context, the ability to work with them (more or less
> easily) in a wide variety of contexts.
> 
> 
> -- 
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/storing-and-comparing-dates-in-sqlite-tp33189769p33189862.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] Concurrent access to database

[Simon Slavin]

On 23 Jan 2012, at 4:40pm, Marcello Botrugno wrote:

> The problem was in the option  SQLITE_OPEN_SHAREDCACHE, infact removing that 
> option the application works fine.
> It works fine also using SQLITE_OPEN_FULLMUTEX|SQLITE_OPEN_READWRITE or 
> SQLITE_OPEN_READWRITE only .
> 
> Thanks again for your help.

You're welcome.  Glad you got it working.

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

Re: [sqlite] C# amalgamation hand holding requested

[Simon Slavin]

On 23 Jan 2012, at 4:39pm, Roosevelt Anderson wrote:

> You can't use the amalgamation directly in C# as the SQLite
> amalgamation is in C.

Can you not simply tell your compiler that '.c' files are C and not C# ?  
That's what you do with Objective-C.  Or do C# compilers not compile C ?

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

Re: [sqlite] storing and comparing dates in sqlite

[Stephan Beal]

On Mon, Jan 23, 2012 at 6:51 PM, dotolee  wrote:

>
> i'm new to sqlite... and relatively new to php.  just wondering what the
> best
> way is to store and compare dates.
>

For any given 10 developers you'll likely hear 11 opinions on this topic.
IMO Unix timestamps are the most portable form out there. "Portable"
meaning, in this context, the ability to work with them (more or less
easily) in a wide variety of contexts.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Automatic join reordering doesn't seem to work?

[Richard Hipp]

On Mon, Jan 23, 2012 at 12:48 PM, Simon Slavin  wrote:

>
> On 23 Jan 2012, at 4:30pm, Ryan Johnson wrote:
>
> > Bump?
> >
> >
>   I don't know if Dr Hipp is pursuing this privately or expecting it to be
> solved collaboratively on this list.
>
>
> I don't have a test case to work on.


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

[sqlite] storing and comparing dates in sqlite

[dotolee]

i'm new to sqlite... and relatively new to php.  just wondering what the best
way is to store and compare dates. 
right now i have something like this in my php code:
$curr_date_time = strftime('%Y-%m-%d %H:%M:%S');
i'm inserting this into a field in the database that has been defined as
type TEXT because from what i read, sqlite does not have a date datatype. 
but question is how can i write a select statement that will sort by date? 
the sql i have right now is not working because it's not treating the field
as a true date...
i want to do something like: 

select field1, field2, updated from myhistory group by field1 order by
updated desc

-- 
View this message in context: 
http://old.nabble.com/storing-and-comparing-dates-in-sqlite-tp33189769p33189769.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] Automatic join reordering doesn't seem to work?

[Simon Slavin]

On 23 Jan 2012, at 4:30pm, Ryan Johnson wrote:

> Bump?
> 
> On 21/01/2012 2:47 PM, Ryan Johnson wrote:
>> On 21/01/2012 2:44 PM, Simon Slavin wrote:
>>> On 21 Jan 2012, at 7:23pm, Ryan Johnson wrote:
>>> 
 It's a SQL89 join. Sqlite docs say SQL89 and SQL92 joins perform 
 identically [1], which I confirmed before sending the OP.
>>> Oh.  Okay.  If it spits out the same EXPLAIN QUERY PLAN then SQLite is 
>>> interpreting it the same way.
>> Which brings us back to the original question: why does sqlite spit out a 
>> bad query plan when a vastly better one exists? There's no clear reason the 
>> better answer should have been hard to find.

Well, the approach would be to find a specific query, post the query and query 
plan, then to simplify the query until the strangeness in the query plan goes 
away.  The last simplification is the one that caused the problem.

Without that, there's no plan of attack.  I don't know if Dr Hipp is pursuing 
this privately or expecting it to be solved collaboratively on this list.

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

Re: [sqlite] Concurrent access to database

[Marcello Botrugno]

Il 23/01/2012 16:27, Simon Slavin ha scritto:

On 23 Jan 2012, at 1:56pm, Marcello Botrugno wrote:


When I have four or more threads accessing to the database   I see the error message: 
"database table is locked".
This happens  when a thread begins a new Transaction, probably because another 
thread already has an excluse access to the database.

I supposed that, setting the "busy timeout" to an high value (30 secs in my 
case) should manage this kind of problems, but probably I am wrong or I am not using 
sqlite functions in the right way.

With the 30 second timeone (which, as far as I can tell, you're doing 
correctly) there's no reason for you to see that.

I assume that there are no pauses between your BEGIN and COMMIT statements.  In 
other words, the app doesn't pause for user-input or to do lots of calculations 
while you have the EXCLUSIVE lock.  I also assume you are checking the result 
value from all SQLite calls just in case, one of them unexpectedly returns an 
error.

If possible, you might remove the word 'EXCLUSIVE'.  Just to see whether the 
behaviour changes in any way.  I would also play with the options on the 
_open_v2() call if possible, perhaps just setting SQLITE_OPEN_READWRITE, again, 
just to see if anything changed.

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


The problem was in the option  SQLITE_OPEN_SHAREDCACHE, infact removing 
that option the application works fine.
It works fine also using SQLITE_OPEN_FULLMUTEX|SQLITE_OPEN_READWRITE or 
SQLITE_OPEN_READWRITE only .


Thanks again for your help.

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

Re: [sqlite] C# amalgamation hand holding requested

[Roosevelt Anderson]

You can't use the amalgamation directly in C# as the SQLite
amalgamation is in C. To use the amalgamation you'd have to compile
the code in a separate C/C++ project, create a wrapper class using
P/Invoke and then call it from your application. You should use the
.NET data provider found here
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
which has already done the work for you.

On Fri, Jan 20, 2012 at 10:42 AM, Don V Nielsen  wrote:
> I need help using the amalgamation in my projects.  I simply do not know
> what steps to take in VS to implement it.
>
> I have some fairly elaborate projects that I have been redeveloping to use
> sqlite instead of .net structure and memory functions.  One project is
> complete, but it uses the sqlite dll.  Recent conversations by users has
> convinced me that I would be better off using the amalgamation file
> instead.  However, I don't know what steps in VS to do to specify the
> source and header files so that it compiles into my application.
>
> Can someone walk through steps necessary for specifying properties,
> references, and source code.  Again, this is C# development.  I've really
> become self-aware just how little I know about VS in the terms of
> application development.  I've been flying blind for quite some time.  I'm
> just like a newbie.
>
> dvn
> ___
> 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] Automatic join reordering doesn't seem to work?

[Ryan Johnson]

Bump?

On 21/01/2012 2:47 PM, Ryan Johnson wrote:

On 21/01/2012 2:44 PM, Simon Slavin wrote:

On 21 Jan 2012, at 7:23pm, Ryan Johnson wrote:

It's a SQL89 join. Sqlite docs say SQL89 and SQL92 joins perform 
identically [1], which I confirmed before sending the OP.
Oh.  Okay.  If it spits out the same EXPLAIN QUERY PLAN then SQLite 
is interpreting it the same way.
Which brings us back to the original question: why does sqlite spit 
out a bad query plan when a vastly better one exists? There's no clear 
reason the better answer should have been hard to find.


Ryan

___
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] Possible Timestamp Where cluase bug

[Simon Slavin]

On 23 Jan 2012, at 3:09pm, Steffen Mangold wrote:

> So SQLITE stores my timestamp I the way it was pushed in. I toughed It has a 
> fixed format to store it.

No, it's completely ignoring timestamp and considers all those as strings.  So 
pick either '.' or '-' and keep to it.

> Simple want all events between begin and end of a day.

Check the first part of the string to see it matches.  You can use

substr(X,1,10)

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

Re: [sqlite] Concurrent access to database

[Simon Slavin]

On 23 Jan 2012, at 1:56pm, Marcello Botrugno wrote:

> When I have four or more threads accessing to the database   I see the error 
> message: "database table is locked".
> This happens  when a thread begins a new Transaction, probably because 
> another thread already has an excluse access to the database.
> 
> I supposed that, setting the "busy timeout" to an high value (30 secs in my 
> case) should manage this kind of problems, but probably I am wrong or I am 
> not using sqlite functions in the right way.

With the 30 second timeone (which, as far as I can tell, you're doing 
correctly) there's no reason for you to see that.

I assume that there are no pauses between your BEGIN and COMMIT statements.  In 
other words, the app doesn't pause for user-input or to do lots of calculations 
while you have the EXCLUSIVE lock.  I also assume you are checking the result 
value from all SQLite calls just in case, one of them unexpectedly returns an 
error.

If possible, you might remove the word 'EXCLUSIVE'.  Just to see whether the 
behaviour changes in any way.  I would also play with the options on the 
_open_v2() call if possible, perhaps just setting SQLITE_OPEN_READWRITE, again, 
just to see if anything changed.

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

Re: [sqlite] Possible Timestamp Where cluase bug

[Steffen Mangold]

>
> SQLite does not have a separate "date/time" datatype.  It uses either strings 
> (preferably in ISO8601 format) or numbers (seconds since 1970 or Julian day 
> number).
>
> Your WHERE clause is comparing strings, not dates.  If you using ISO8601 
> dates in your database file, as you do in the query, it will probably work, 
> though.
>

Ah ok i dont know this. So I must know the exact datetime string format with 
where pushed in to make a valid string compare when I do selects, right?
So SQLITE stores my timestamp I the way it was pushed in. I toughed It has a 
fixed format to store it.


Thank you.

Steffen


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

Re: [sqlite] Possible Timestamp Where cluase bug

[Steffen Mangold]

>
> Plus...what's the "T" supposed to do?  Perhaps I'm ignorant of the magic you 
> expect.
>

The 'T' devide the date from the time. Looking here 
http://www.sqlite.org/lang_datefunc.html
It is the default ISO-8601 datetime format.

>
> I'm confused as to why you would expect any match at all.  And indeed, when I 
> run your queries against a test set I get nothing back at all for both 
> queries.
>

Simple want all events between begin and end of a day.



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

Re: [sqlite] Possible Timestamp Where cluase bug

[Richard Hipp]

On Mon, Jan 23, 2012 at 9:29 AM, Steffen Mangold <
steffen.mang...@balticsd.de> wrote:

> Hi SQLITE community,
>
> I think i found a strange bug.
> Lets say we have a table in this form:
>
> Id (long)  | Timestamp (DateTime)
> -
> 12   | 17.01.2012 16:15:00
> 12   | 17.01.2012 17:15:00
>
> Now we make a query where the data should involved:
>
> SELECT Id, TimeStamp FROM tabelA
> WHERE Timestamp >= '2012-01-17T00:00:00' AND
> Timestamp <= '2012-01-17T23:59:59' AND
> ID = 12
> ORDER BY Timestamp DESC LIMIT 250 OFFSET 0
>
> Result is 'nothing' means no rows are returned.
>

SQLite does not have a separate "date/time" datatype.  It uses either
strings (preferably in ISO8601 format) or numbers (seconds since 1970 or
Julian day number).

Your WHERE clause is comparing strings, not dates.  If you using ISO8601
dates in your database file, as you do in the query, it will probably work,
though.



>
> But if we change the minimum timestamp to 1 day earlier, like:
>
> SELECT Id, TimeStamp FROM tabelA
> WHERE Timestamp >= '2012-01-16T00:00:00' AND
> Timestamp <= '2012-01-17T23:59:59' AND
> ID = 12
> ORDER BY Timestamp DESC LIMIT 250 OFFSET 0
>
> The result is the 2 rows written above.
>
> ??? I don't get it ???
> Why this is happen, is it really a bug?
>
>
> Regards
>
> Steffen Mangold
>
>
> ___
> 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] Possible Timestamp Where cluase bug

[Black, Michael (IS)]

I'm confused as to why you would expect any match at all.  And indeed, when I 
run your queries against a test set I get nothing back at all for both queries.



Your datetime formats don't match at all.  And "ORDER BY" for that field is not 
date/time order since you have DD.MM. which won't result in datetime order. 
 You'll get all 01 days, then 02 days, then 03 days, etc.

Plus...what's the "T" supposed to do?  Perhaps I'm ignorant of the magic you 
expect.



This is what I did

CREATE TABLE tableA(id,Timestamp);
INSERT INTO "tableA" VALUES(12,'17.01.2012 16:15:00');
INSERT INTO "tableA" VALUES(12,'17.01.2012 17:15:00');

SELECT Id, TimeStamp FROM tableA
WHERE Timestamp >= '2012-01-16T00:00:00' AND
Timestamp <= '2012-01-17T23:59:59' AND
ID = 12
ORDER BY Timestamp DESC LIMIT 250 OFFSET 0;

--no records--





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Steffen Mangold [steffen.mang...@balticsd.de]
Sent: Monday, January 23, 2012 8:29 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Possible Timestamp Where cluase bug

Hi SQLITE community,

I think i found a strange bug.
Lets say we have a table in this form:

Id (long)  | Timestamp (DateTime)
-
12   | 17.01.2012 16:15:00
12   | 17.01.2012 17:15:00

Now we make a query where the data should involved:

SELECT Id, TimeStamp FROM tabelA
WHERE Timestamp >= '2012-01-17T00:00:00' AND
Timestamp <= '2012-01-17T23:59:59' AND
ID = 12
ORDER BY Timestamp DESC LIMIT 250 OFFSET 0

Result is 'nothing' means no rows are returned.

But if we change the minimum timestamp to 1 day earlier, like:

SELECT Id, TimeStamp FROM tabelA
WHERE Timestamp >= '2012-01-16T00:00:00' AND
Timestamp <= '2012-01-17T23:59:59' AND
ID = 12
ORDER BY Timestamp DESC LIMIT 250 OFFSET 0

The result is the 2 rows written above.

??? I don't get it ???
Why this is happen, is it really a bug?


Regards

Steffen Mangold

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

Re: [sqlite] Xcode warning on 3.7.10

[Richard Hipp]

On Mon, Jan 23, 2012 at 9:52 AM, Sylvain Pointeau <
sylvain.point...@gmail.com> wrote:

> On Mon, Jan 23, 2012 at 2:50 PM, Simon Slavin 
> wrote:> Which version of Xcode.  Which version of the OS ?
> > Which target platform (by which I mean hardware) do you have the project
> > set for ?  32-bit or 64-bit ?
> > Are you compiling on an Intel Mac ? (just in case you're still using PPC)
> >
> >
> Are you using Clang?
> is SQLite compatible with Clang?
> ___
>


SQLite works fine with Clang.  In fact, we run SQLite through scan-build
and get zero warnings out.

There have been a couple of bugs in LLVM that caused problems for SQLite.
We worked around the problems in the SQLite source code and I think the
bugs have subsequently been fixed in LLVM as well, though the work-arounds
are still in the SQLite code.

I think y'all are exhibiting way to much concern over a compiler
*warning*.  See http://www.sqlite.org/testing.html#staticanalysis for
additional information on quality control in SQLite and in particular our
views on static analysis.

-- 
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] Xcode warning on 3.7.10

[Sylvain Pointeau]

On Mon, Jan 23, 2012 at 2:50 PM, Simon Slavin  wrote:

>
> On 23 Jan 2012, at 5:49am, Tommy wrote:
>
> > After bringing the SQLite amalgamation into my library and compiling,
> > Apple Xcode produced the following warning:
> >
> > sqlite3.c:27620:32:{27620:32-27620:45}{27620:30-27620:31}: warning:
> > implicit conversion from 'long long' to 'long' changes value from
> > 9223372036854775807 to -1 [-Wconstant-conversion,3]
> >
>
> Which version of Xcode.  Which version of the OS ?
> Which target platform (by which I mean hardware) do you have the project
> set for ?  32-bit or 64-bit ?
> Are you compiling on an Intel Mac ? (just in case you're still using PPC)
>
>
Are you using Clang?
is SQLite compatible with Clang?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible Timestamp Where cluase bug

[Steffen Mangold]

Hi SQLITE community,

I think i found a strange bug.
Lets say we have a table in this form:

Id (long)  | Timestamp (DateTime)
-
12   | 17.01.2012 16:15:00
12   | 17.01.2012 17:15:00

Now we make a query where the data should involved:

SELECT Id, TimeStamp FROM tabelA
WHERE Timestamp >= '2012-01-17T00:00:00' AND
Timestamp <= '2012-01-17T23:59:59' AND
ID = 12
ORDER BY Timestamp DESC LIMIT 250 OFFSET 0

Result is 'nothing' means no rows are returned.

But if we change the minimum timestamp to 1 day earlier, like:

SELECT Id, TimeStamp FROM tabelA
WHERE Timestamp >= '2012-01-16T00:00:00' AND
Timestamp <= '2012-01-17T23:59:59' AND
ID = 12
ORDER BY Timestamp DESC LIMIT 250 OFFSET 0

The result is the 2 rows written above.

??? I don't get it ???
Why this is happen, is it really a bug?


Regards

Steffen Mangold

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

[sqlite] Concurrent access to database

[Marcello Botrugno]

Hi All,
I have an application which starts two or more threads.
Each thread open a separate connection to the database and in each 
transaction, modifies various tables of it.
I use third-party library  to access the SQLite database which make it 
possible to have an uniform access to different DBMS. I do not control 
directly the calls  to sqlite3 funcution, but I have the source code of 
the library so I can see the sequence of calls.


The sqlite3 version I am using is 3.7.9 .

The sequence of actions of a thread is the following:

//--- Start Thread ---


// Open connection to DB
sqlite3 *handle(NULL);
sqlite3_open_v2("C:...\dbase", &handle,
SQLITE_OPEN_SHAREDCACHE|SQLITE_OPEN_FULLMUTEX|SQLITE_OPEN_READWRITE );

sqlite3_busy_timeout(handle, 3);

sqlite3_step("PRAGMA foregn_keys = ON");
sqlite3_reset(..)
sqlite3_clear_bindings(..);

// Begin a new transaction
BEGIN TRANSACTION EXCLUSIVE
...
   SELECT 
INSERT ...
UPDATE ...

COMMIT
...
// Begin a new transaction
BEGIN TRANSACTION EXCLUSIVE
...
   SELECT 
INSERT ...
UPDATE ...
...
COMMIT

sqlite3_close();

//--- END Thread ---


When I have four or more threads accessing to the database   I see the 
error message: "database table is locked".
This happens  when a thread begins a new Transaction, probably because 
another thread already has an excluse access to the database.


I supposed that, setting the "busy timeout" to an high value (30 secs in 
my case) should manage this kind of problems, but probably I am wrong or 
I am not using sqlite functions in the right way.


 Can any one suggest me any strategies for overcome this problem ?

Tank you for your help.

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

Re: [sqlite] Xcode warning on 3.7.10

[Simon Slavin]

On 23 Jan 2012, at 5:49am, Tommy wrote:

> After bringing the SQLite amalgamation into my library and compiling,
> Apple Xcode produced the following warning:
> 
> sqlite3.c:27620:32:{27620:32-27620:45}{27620:30-27620:31}: warning:
> implicit conversion from 'long long' to 'long' changes value from
> 9223372036854775807 to -1 [-Wconstant-conversion,3]
> 
> The code in question is:
> 
> #define LARGEST_INT64  (0x|(((i64)0x7fff)<<32))

I have compiled amalgamation in all Xcodes ever since OS X 10.5.0 and i don't 
remember seeing that error, which probably means it's the result of some target 
platform settings.  I doubt I'll be able to solve this, but just in case,

Which version of Xcode.  Which version of the OS ?
Which target platform (by which I mean hardware) do you have the project set 
for ?  32-bit or 64-bit ?
Are you compiling on an Intel Mac ? (just in case you're still using PPC)

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

Re: [sqlite] Xcode warning on 3.7.10

[Richard Hipp]

On Mon, Jan 23, 2012 at 12:49 AM, Tommy  wrote:

> To whom it may concern,
>
>
> After bringing the SQLite amalgamation into my library and compiling,
> Apple Xcode produced the following warning:
>
> sqlite3.c:27620:32:{27620:32-27620:45}{27620:30-27620:31}: warning:
> implicit conversion from 'long long' to 'long' changes value from
> 9223372036854775807 to -1 [-Wconstant-conversion,3]
>
> The code in question is:
>
> #define LARGEST_INT64  (0x|(((i64)0x7fff)<<32))
>
> I hope this helps. If at all possible, I would appreciate learning
> what the issue is, as I'm new to C.
>

I don't know why this warning is coming up.  We don't use Xcode, preferring
instead to use ordinary text editors (vi and emacs) and invoke the compiler
directly.  And we don't get the above warning on Macs (having tried it on
Leopard, SnowLeopard, and Lion).



>
>
> Kind regards,
> Tommy
> ___
> 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] List of DB virtual modules

[Richard Hipp]

On Mon, Jan 23, 2012 at 8:06 AM, Max Vlasov  wrote:

> Hi,
>
> it there an easy way (without manual statements parsing) to get the list of
> modules used in all (if any) virtual tables statements of the schema? For
> any opened db I wanted to automatically enable found modules (if I
> recognize them) and warn if there are unsupported ones.
>

There is no way to do that right now, other than manually parsing the
statements.


>
> Thanks,
>
> Max
> ___
> 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

[sqlite] Xcode warning on 3.7.10

[Tommy]

To whom it may concern,


After bringing the SQLite amalgamation into my library and compiling,
Apple Xcode produced the following warning:

sqlite3.c:27620:32:{27620:32-27620:45}{27620:30-27620:31}: warning:
implicit conversion from 'long long' to 'long' changes value from
9223372036854775807 to -1 [-Wconstant-conversion,3]

The code in question is:

#define LARGEST_INT64  (0x|(((i64)0x7fff)<<32))

I hope this helps. If at all possible, I would appreciate learning
what the issue is, as I'm new to C.


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

[sqlite] List of DB virtual modules

[Max Vlasov]

Hi,

it there an easy way (without manual statements parsing) to get the list of
modules used in all (if any) virtual tables statements of the schema? For
any opened db I wanted to automatically enable found modules (if I
recognize them) and warn if there are unsupported ones.

Thanks,

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

Re: [sqlite] database disk image is malformed

[Simon Slavin]

On 23 Jan 2012, at 9:45am, Sreekumar TP wrote:

> -My journal mode is set to WAL, synchronous mode is NORMAL and Checkpoint
> mode is Manual.
> 
> Transactions are written to the WAL file, therefore, even if the WAL file
> is lost, I should be able to retrieve the database without
> corruption.(sacrificing some data ofcourse)   ?

The simplest way to get an uncorrupted database is to use the command-line tool 
to .dump your database file to SQL commands, then use the command-line tool to 
run those commands to make another database file.  This does not guarantee you 
a database that makes sense unless you used transactions correctly.  But it 
does guarantee you a database which will pass the corruption-check PRAGMAs.

> Moreover, I do know that there was no checkpoint in progress when the power
> was turned OFF. Hence  syncs should not be a problem ?

If you were using a modern-day standard computer then you have no idea when the 
computer was updating the hard disk.  All sorts of foreground tasks are running 
which might have held it up: print service, checking for new email, etc..  What 
you can hope for is that changes are written in the order they were made, which 
is something SQLite relies on to keep its database files uncorrupted.

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

Re: [sqlite] database disk image is malformed

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 23/01/12 01:45, Sreekumar TP wrote:
> Transactions are written to the WAL file, therefore, even if the WAL
> file is lost, I should be able to retrieve the database without 
> corruption.(sacrificing some data ofcourse)   ?

Correct.  A checkpoint will end up modifying the main database file.

However you need to identify how you got the corruption in the first place
otherwise it will happen again.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8dP4oACgkQmOOfHg372QQdgACguIlWFuXuV3GPIfVWugf5RdbE
oEAAnAhXDR6JH7W5nKCraNKYnpFEfYkl
=VPyM
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C# amalgamation hand holding requested

[Jean-Denis MUYS]

On 23 janv. 2012, at 11:18, Simon Slavin wrote:

> 
> On 23 Jan 2012, at 8:59am, Jean-Denis MUYS wrote:
> 
>> I am a Mac developer, and I haven't even tried to look for such 
>> documentation on Xcode. This is because the steps to follow couldn't be more 
>> obvious. Here they are:
>> 
>> Step 1: drag and drop the amalgamation files to your Xcode project
>> Step 2: there is no step 2
> 
> Have you tried that recently ?

Depends on what you mean by recently. Last time for me was around last summer. 
It was definitely with Xcode 4. I do that on perhaps a weekly basis with all 
kinds of source code however.

>  Where in the project ?

Wherever you like. I suspect you organize your project with groups for each 
subsystem. I put it in either in the subsystem that uses SQLite, or in a more 
general "3rd party libraries" group.

>  The Xcode icon in the dock ?

If you do that, Xcode will open the file(s) as is, and will not add it(them) to 
any of your open projects.

>  Part of an Xcode window ?

The project navigator as always.

>  The project icon in the window ?

Possibly. That icon is in the project navigator, and if drop a file on it, it 
ends up at the top level, outside of all groups.

>  One of the folders in the window ?  Make a new folder for them ?

Your choice.

>  How do you answer the dialog that pops up ?

Well, nothing special here. You answer it exactly as for any other set of 
source files: you check the targets that will use SQLite (probably all of them 
if you have more than one), you create a group if you dropped a directory 
rather than the source files themselves, and you check/or not the option to 
copy the files in the group folder (up to you).

>  Should the 'Target Membership' checkbox be checked for both files ?

It doesn't matter. In fact, if you drag and drop both files at the same time, 
you will not have the opportunity to decide on file by file basis. In any case, 
Xcode 4 is smart enough to know that header files are never added to any target.

While all those questions are legitimate, they are beginners questions 
regarding Xcode and none of them is related to SQLite. I would contend that 
SQLite documentation is not the right place where to put basic level 1 
documentation about any or all IDEs that can be used to develop with it.

Perhaps more interesting would be to propose Xcode project templates for 
projects that use SQLite with the Amalgamation distribution (as opposed to the 
SQLite versions that Apple ships with its OS'es). Though the added value would 
slight indeed.

> 
> Simon.

Jean-Denis

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

Re: [sqlite] C# amalgamation hand holding requested

[Simon Slavin]

On 23 Jan 2012, at 8:59am, Jean-Denis MUYS wrote:

> I am a Mac developer, and I haven't even tried to look for such documentation 
> on Xcode. This is because the steps to follow couldn't be more obvious. Here 
> they are:
> 
> Step 1: drag and drop the amalgamation files to your Xcode project
> Step 2: there is no step 2

Have you tried that recently ?  Where in the project ?  The Xcode icon in the 
dock ?  Part of an Xcode window ?  The project icon in the window ?  One of the 
folders in the window ?  Make a new folder for them ?  How do you answer the 
dialog that pops up ?  Should the 'Target Membership' checkbox be checked for 
both files ?

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

Re: [sqlite] database disk image is malformed

[Sreekumar TP]

-My journal mode is set to WAL, synchronous mode is NORMAL and Checkpoint
mode is Manual.

Transactions are written to the WAL file, therefore, even if the WAL file
is lost, I should be able to retrieve the database without
corruption.(sacrificing some data ofcourse)   ?

Moreover, I do know that there was no checkpoint in progress when the power
was turned OFF. Hence  syncs should not be a problem ?

-Sreekumar





On Mon, Jan 23, 2012 at 3:00 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 23/01/12 01:03, Sreekumar TP wrote:
> > is it possible to find out if sqlite was in the middle of a transaction
> > when the power off occurred ?
>
> WAL mode has the transactions in a separate file.  Normal mode alters the
> database and keeps the original data in a rollback journal.
>
> However unless you identify the underlying cause (eg disk lying about
> syncs) it won't help since you have to assume all data you see is a lie.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk8dKLYACgkQmOOfHg372QTuBQCgwFLnZ5108QRfelWm11qDiRjY
> szYAn0tgEw0klQqRllRSyepezxt550ha
> =SRbO
> -END PGP SIGNATURE-
> ___
> 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] database disk image is malformed

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 23/01/12 01:03, Sreekumar TP wrote:
> is it possible to find out if sqlite was in the middle of a transaction
> when the power off occurred ?

WAL mode has the transactions in a separate file.  Normal mode alters the
database and keeps the original data in a rollback journal.

However unless you identify the underlying cause (eg disk lying about
syncs) it won't help since you have to assume all data you see is a lie.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8dKLYACgkQmOOfHg372QTuBQCgwFLnZ5108QRfelWm11qDiRjY
szYAn0tgEw0klQqRllRSyepezxt550ha
=SRbO
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Sreekumar TP]

Yes, this is a case of corruption. The problem occurred during a power
cycle test.  I have the synchronous mode set to NORMAL.
By examining the header of the DB ,is it possible to find out if sqlite was
in the middle of a transaction when the power off occurred ?

-Sreekumar


On Mon, Jan 23, 2012 at 2:21 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 23/01/12 00:13, Sreekumar TP wrote:
> > What can I infer from these logs ?
>
> Your database is corrupted.  Here is a list of possible causes:
>
>  http://www.sqlite.org/howtocorrupt.html
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk8dH4kACgkQmOOfHg372QThhwCgyPS+Sl28MEMHln4I6iRcm5VL
> 2ZkAn0tIpqfbGbkIZwUs3myTbwzv+jdw
> =Czsj
> -END PGP SIGNATURE-
> ___
> 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] C# amalgamation hand holding requested

[Jean-Denis MUYS]

On 20 janv. 2012, at 18:08, Simon Slavin wrote:

> 
> On 20 Jan 2012, at 3:42pm, Don V Nielsen wrote:
> 
>> Recent conversations by users has
>> convinced me that I would be better off using the amalgamation file
>> instead.  However, I don't know what steps in VS to do to specify the
>> source and header files so that it compiles into my application.
>> 
>> Can someone walk through steps necessary for specifying properties,
>> references, and source code.
> 
> And once it's done can we add it to the documentation or something ?  A page 
> for each of the most common uses would be excellent: one for VS, one for 
> common Linux compilations, one for Xcode on Macs, would be really useful.
> 
> Simon.

I am a Mac developer, and I haven't even tried to look for such documentation 
on Xcode. This is because the steps to follow couldn't be more obvious. Here 
they are:

Step 1: drag and drop the amalgamation files to your Xcode project
Step 2: there is no step 2

Anything beyond that concerns your project, not SQLite.

Jean-Denis

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

Re: [sqlite] database disk image is malformed

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 23/01/12 00:13, Sreekumar TP wrote:
> What can I infer from these logs ?

Your database is corrupted.  Here is a list of possible causes:

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

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8dH4kACgkQmOOfHg372QThhwCgyPS+Sl28MEMHln4I6iRcm5VL
2ZkAn0tIpqfbGbkIZwUs3myTbwzv+jdw
=Czsj
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] database disk image is malformed

[Sreekumar TP]

Hi,

I have get the following error when a sql statment is executed ( query ) -
"database disk image is malformed".

On running the quick_check pragma on the db, I get the following log -

"*** in database main ***
On tree page 1595 cell 5: Rowid 2104 out of order (max larger than parent
max of 2068)
On tree page 1597 cell 4: Rowid 2331 out of order (max larger than parent
max of 2070)
On tree page 1620 cell 1: Rowid 5412 out of order (max larger than parent
max of 2072)
On tree page 1621 cell 1: Rowid 2073 out of order (min less than parent min
of 5412)
On tree page 1762 cell 1: Rowid 5409 out of order (max larger than parent
max of 2318)
Page 1766: btreeInitPage() returns error code 11
On tree page 541 cell 21: Child page depth differs
Page 1127: btreeInitPage() returns error code 11
On tree page 1413 cell 0: 2nd reference to page 1127
On tree page 1413 cell 1: Rowid 5418 out of order (max larger than parent
max of 2331)
On tree page 541 cell 23: Child page depth differs
On tree page 1812 cell 0: Rowid 2332 out of order (min less than parent min
of 5418)
Page 1802 is never used
Page 1806 is never used"


What can I infer from these logs ?


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

[sqlite] AUTO: Oren Bar is out of the office - He is in vacation (returning 24/01/2012)

[Oren Bar]

I am out of the office until 24/01/2012.

I will be unavailable.
Maybe I will be back at the office at around 15:00


Note: This is an automated response to your message  "Re: [sqlite] Minimum
RAM requirements (Richard Hipp)" sent on 23/1/2012 9:09:52.

This is the only notification you will receive while this person is away.

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