Re: [sqlite] About Fossil usage

[Nico Williams]

On Wed, May 18, 2011 at 9:50 PM, Richard Hipp  wrote:
> On Wed, May 18, 2011 at 1:27 PM, Nico Williams wrote:
>> Also, I didn't expect commits and branches to go to the repository
>> that I cloned mine from.  That was surprising!  With git and other
>> similar VCSes such operations only hit the repository from which the
>> workspace was checked out.  Is this a configuration issue?
>
> That's a feature, called "autosync".  You can turn it off like this:
>
>     fossil setting autosync off
>
> When autosync is on (the default), Fossil tries to "pull" before each update
> or checkout or commit, and it tries to "push" after each commit.  It does
> this push or pull against the last repository you synced with, or against
> the repo that you cloned from if you have never synced before.

You might want a way to set this on the clone command, or else to
mention this where fossil clone is covered.

Is a "private" branch then one for which autosync is off, or is there
some other distinction as well?  How does one change a branch to no
longer be private?

How does one remove changesets?  How does one collapse deltas?

Thanks,

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

Re: [sqlite] How to build SQLite3 docs?

[Nico Williams]

On Wed, May 18, 2011 at 9:44 PM, Richard Hipp  wrote:
> You need tclsqlite3.c, not sqlite3.c.  main() is found in tclsqlite3.c.  The
> comment in the Makefile is not clear on this point and needs to be fixed

Oh, duh.  Thanks!

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

Re: [sqlite] About Fossil usage

[Richard Hipp]

On Wed, May 18, 2011 at 1:27 PM, Nico Williams wrote:

> So I've stumbled twice trying to set up use of Fossil, both times with
> respect to how to create a private branch.
>
> First I did "fossil branch new ..." and didn't realize that the
> workspace was not switched to be in that branch, so that my subsequent
> commit didn't go to that branch.
>
> My second attempt I did "fossil branch new ..." then "fossil checkout
> ...", then "fossil commit ... --private", but the last command
> effectively created a new branch off my branch.
>
> My third attempt I did a "fossil commit --private --branch ...".  That
> worked.
>

Thanks.  That is good information for helping me write better documentation.


>
> Also, I didn't expect commits and branches to go to the repository
> that I cloned mine from.  That was surprising!  With git and other
> similar VCSes such operations only hit the repository from which the
> workspace was checked out.  Is this a configuration issue?
>

That's a feature, called "autosync".  You can turn it off like this:

 fossil setting autosync off

When autosync is on (the default), Fossil tries to "pull" before each update
or checkout or commit, and it tries to "push" after each commit.  It does
this push or pull against the last repository you synced with, or against
the repo that you cloned from if you have never synced before.


>
> These are minor issues, IMO.  I expected Fossil to be painful (most
> VCSes are), but aside from the above it's actually a pleasure.
> Thanks,
>
> Nico
> --
> ___
> 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] How to build SQLite3 docs?

[Richard Hipp]

On Wed, May 18, 2011 at 6:05 PM, Nico Williams wrote:

> The SQLite3 docs Fossil repository doesn't seem to include any
> instructions for building the docs.  I've searched sqlite.org up and
> down (docs and wiki both) for these instructions.  I've also searched
> the web a bit and found nothing obvious.  I have figured out something
> from the file named main.mk: I need to write a Makefile that defines
> the macros named in main.mk and then have it include main.mk, but I'm
> missing some things.  For example, "make base" fails with relocation
> errors while building tclsh:
>
> gcc -g -o tclsh -DSQLITE_ENABLE_FTS3 -DTCLSH=1 -DSQLITE_TCLMD5
> -I/usr/include/tcl8.4 /tmp/sqlite3/sqlite3.c -ltcl8.4 -lpthread -ldl
>

You need tclsqlite3.c, not sqlite3.c.  main() is found in tclsqlite3.c.  The
comment in the Makefile is not clear on this point and needs to be fixed


> -lc
> /usr/bin/ld: /usr/lib/debug/usr/lib/crt1.o(.debug_info): relocation 0
> has invalid symbol index 12
> [...]
> /usr/bin/ld: /usr/lib/debug/usr/lib/crt1.o(.debug_info): relocation 22
> has invalid symbol index 22
> /usr/lib/gcc/i686-linux-gnu/4.4.5/../../../../lib/crt1.o: In function
> `_start':
> (.text+0x18): undefined reference to `main'
> collect2: ld returned 1 exit status
> make: *** [tclsh] Error 1
>
> I'm missing something in the TCLFLAGS macro, or something.  What might that
> be?
>
> Nico
> --
> ___
> 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] System.Data.SQLite INT vs INTEGER

[Joe D]

I have an app that creates a table of file names and sizes.  The app 
itself works fine.

I wanted to look at the table just to make sure that it was doing what 
it should, so I used something to do a simple SELECT * and bind the 
results to a DataGrid.

When I did, I got an error "Value was either too large or too small for 
an Int32".

I tracked it down to the column being created as type INT instead of 
INTEGER.  SQLite treats these as the same, but System.Data.SQLite treats 
INT as Int32 and INTEGER as Int64.

Changing the app so that the table was created as type INTEGER made the 
error go away.

I traced it down to the SQLiteConvert._typeNames array.

The entry for INT defines it as Int32.  I changed it to Int64 and 
changed the app back to INT for the column.  This also made the error go 
away.

The error also did not occur if there were no files with sizes in excess 
of Int32.MaxValue.

I'm not sure if this is worthy of a bug fix or not.  It could cause 
issues if you were loading a table created somewhere else with a column 
defined as INT instead if INTEGER.  Or not.

Joe D

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

Re: [sqlite] SQL Statement Help(selecting days).

[Petite Abeille]

On May 19, 2011, at 12:28 AM, Igor Tandetnik wrote:

> That's SQLite extension. I don't believe it's legal standard SQL. The 
> alias in the SELECT clause acts kind of like a macro; its use in the 
> rest of the statement is simply replaced with the corresponding expression.

Mind-boggling. Border line insane :)

> The query could be written without using this feature, but it wouldn't 
> be quite as neat and readable.

Neat indeed. Well played :))

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

Re: [sqlite] SQL Statement Help(selecting days).

[Igor Tandetnik]

On 5/18/2011 6:11 PM, Petite Abeille wrote:
> In other words, how come the following works in SQLite:
>
> select 1 as value where value>  0;
>
> Or even weirder:
> select 1 as value where value = 0;
>
> There is no from clause, there is no column 'value' per se, but nonetheless 
> that non existing column can be referenced in the where clause. What gives?

That's SQLite extension. I don't believe it's legal standard SQL. The 
alias in the SELECT clause acts kind of like a macro; its use in the 
rest of the statement is simply replaced with the corresponding expression.

The query could be written without using this feature, but it wouldn't 
be quite as neat and readable.
-- 
Igor Tandetnik

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

Re: [sqlite] SQL Statement Help(selecting days).

[Nico Williams]

On Wed, May 18, 2011 at 5:11 PM, Petite Abeille
 wrote:
> Where does the start_gap and end_gap come from? They are only declared in the 
> select part of the inner select statement, and nowhere in the from part. But 
> nonetheless, SQLite manages to use these non existing columns in the where 
> clause. What gives?
>
> In other words, how come the following works in SQLite:
>
> select 1 as value where value > 0;
>
> Or even weirder:
> select 1 as value where value = 0;
>
> There is no from clause, there is no column 'value' per se, but nonetheless 
> that non existing column can be referenced in the where clause. What gives?

You don't need a table source in order to evaluate expressions, nor to
have a WHERE condition.  A SELECT without table sources will produce a
single row defined by the column expressions, and filtered by the
WHERE clause, if there is one.  So a SELECT without table sources can
produce zero or one rows.

Why would you want to do that?  I can think of some reasons:

 - So you can invoke a user-defined function and use that to produce a
zero- or one-row result for use in IN clauses and so on.

 - So you can define a zero-row VIEW, which you might want to do if
you want to allow only INSERTs on a view (with INSTEAD OF INSERT
triggers) as a way to emulate stored procedures.  (I've done this
plenty, and indeed, I rely on this approach in my DB triggers patch to
make the patch very small.)

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

Re: [sqlite] SQL Statement Help(selecting days).

[Petite Abeille]

On May 18, 2011, at 11:28 PM, Igor Tandetnik wrote:

> On 5/18/2011 4:17 PM, Pavel Ivanov wrote:
>>> I need help to build a statement in order to select all days free from
>>> events in a specific time range.
>> 
>> This kind of task should be implemented in your application. SQL
>> wasn't intended for and can't solve such tasks.
> 
> Sounds like a challenge:

Nicely done :)

Lets break it apart.

First, a test set:

create view event 
as

select 1 as id,
   date( '2011-01-02' ) as start_date,
   date( '2011-01-08' ) as end_date
   
union all
select 2 as id,
   date( '2011-01-06' ) as start_date,
   date( '2011-01-12' ) as end_date
   
union all
select 3 as id,
   date( '2011-01-18' ) as start_date,
   date( '2011-01-21' ) as end_date;

> select * from event;
1|2011-01-02|2011-01-08
2|2011-01-06|2011-01-12
3|2011-01-18|2011-01-21

Then the query itself:

select  start_gap, 
min( end_gap ) as end_gap
from(
select  date( e1.end_date, '+1 day' ) as start_gap, 
date( e2.start_date, '-1 day' ) as end_gap

from(
select  start_date, 
end_date 
fromevent
union all
select  '' as start_date, 
date('2011-01-01', '-1 day') as end_date
) e1

cross join (
select  start_date, 
end_date 
fromevent
union all
select  date('2011-02-01', '+1 day') as 
start_date, 
'' as end_date
) e2

where   start_gap <= end_gap 
and start_gap >= '2011-01-01' 
and end_gap <= '2011-02-01'
and not exists 
( 
select  1 
fromevent 

where   start_date between start_gap and 
end_gap 
or  end_date between start_gap and end_gap 
)
)
group bystart_gap;

2011-01-01|2011-01-01
2011-01-13|2011-01-17
2011-01-22|2011-02-01

Very nice. One question though, regarding the inner most where clause:

where   start_gap <= end_gap 
and start_gap >= '2011-01-01' 
and end_gap <= '2011-02-01'

Where does the start_gap and end_gap come from? They are only declared in the 
select part of the inner select statement, and nowhere in the from part. But 
nonetheless, SQLite manages to use these non existing columns in the where 
clause. What gives?

In other words, how come the following works in SQLite:

select 1 as value where value > 0;

Or even weirder:
select 1 as value where value = 0;

There is no from clause, there is no column 'value' per se, but nonetheless 
that non existing column can be referenced in the where clause. What gives?

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

[sqlite] How to build SQLite3 docs?

[Nico Williams]

The SQLite3 docs Fossil repository doesn't seem to include any
instructions for building the docs.  I've searched sqlite.org up and
down (docs and wiki both) for these instructions.  I've also searched
the web a bit and found nothing obvious.  I have figured out something
from the file named main.mk: I need to write a Makefile that defines
the macros named in main.mk and then have it include main.mk, but I'm
missing some things.  For example, "make base" fails with relocation
errors while building tclsh:

gcc -g -o tclsh -DSQLITE_ENABLE_FTS3 -DTCLSH=1 -DSQLITE_TCLMD5
-I/usr/include/tcl8.4 /tmp/sqlite3/sqlite3.c -ltcl8.4 -lpthread -ldl
-lc
/usr/bin/ld: /usr/lib/debug/usr/lib/crt1.o(.debug_info): relocation 0
has invalid symbol index 12
[...]
/usr/bin/ld: /usr/lib/debug/usr/lib/crt1.o(.debug_info): relocation 22
has invalid symbol index 22
/usr/lib/gcc/i686-linux-gnu/4.4.5/../../../../lib/crt1.o: In function `_start':
(.text+0x18): undefined reference to `main'
collect2: ld returned 1 exit status
make: *** [tclsh] Error 1

I'm missing something in the TCLFLAGS macro, or something.  What might that be?

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

Re: [sqlite] SQL Statement Help(selecting days).

[Danilo Cicerone]

Amazing!
Thanks a lot.

2011/5/18 Igor Tandetnik :
> select startGap, min(endGap) from
> (
>   select date(e1.endDate, '+1 day') startGap, date(e2.startDate, '-1
> day') endGap
>   from (select startDate, endDate from events
>         union all
>         select '' startDate, date('2011-01-01', '-1 day') endDate) e1,
>        (select startDate, endDate from events
>         union all
>         select date('2011-02-01', '+1 day') startDate, '' endDate) e2
>   where startGap <= endGap and startGap >= '2011-01-01' and endGap <=
> '2011-02-01'
>     and not exists (
>       select 1 from events where startDate between startGap and endGap
>         or endDate between startGap and endGap)
> )
> group by startGap;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_CANTOPEN

[Simon Slavin]

On 18 May 2011, at 9:09pm, Brancke, Brad wrote:

> Once in a while when we power on the device and start my application,
> this query fails:
> 
> "SELECT storage_enum, mode, GPSDisplayDMS, UserRealID, UserEffID FROM
> Settings;"
> 
> with the reason "unable to open database file" 

I assume that this is the first command you execute against the database after 
you _open() it.

> I suspect something didn't get closed properly when the unit powered
> off.
> 
> Weird:
> 
> 1.   If I copy the db file to my PC, it opens and reads just fine.
> 
> 2.   If I just make a copy of it on the device itself (same folder
> and everything), then I can query the *copy* of the file just fine.
> 
> sqlite3_open() always succeeds and returns SQLITE_OK. The failure is
> when sqlite3_exec() tries to run. Then we sqlite3_close() the db.

The diagnostics you paste below that are really good.  Thanks for posting them.

The problem is unlikely to be about the contents of anything properly saved in 
the database file.  I think it's more likely to be something about the file 
system (privileges, spare file handles, etc.).

Can you check (by eye or write a small utility) whether any journal files are 
present in the directory of the database ?  Perhaps you get the error when and 
only when the database wasn't closed properly.  If any journal files (files 
with names that start with the database name) remain in that directory when 
your device is rebooted then the last connection with the database was 
definitely not sqlite3_close()-ed properly.  This sort of thing happens if the 
device doesn't wait for the app to quit before it shuts down.

If you can tell us that there are definitely no journal files, that'll cut of a 
bunch of possibilities.  Once you've done that, you might write a tiny C 
program which opens the database in C (fopen(), or something like that), reads 
the first byte out of it, writes the same value back, and closes it.  See 
whether you get any errors from that program.

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

Re: [sqlite] SQL Statement Help(selecting days).

[Igor Tandetnik]

On 5/18/2011 4:17 PM, Pavel Ivanov wrote:
>> I need help to build a statement in order to select all days free from
>> events in a specific time range.
>
> This kind of task should be implemented in your application. SQL
> wasn't intended for and can't solve such tasks.

Sounds like a challenge:

select startGap, min(endGap) from
(
   select date(e1.endDate, '+1 day') startGap, date(e2.startDate, '-1 
day') endGap
   from (select startDate, endDate from events
 union all
 select '' startDate, date('2011-01-01', '-1 day') endDate) e1,
(select startDate, endDate from events
 union all
 select date('2011-02-01', '+1 day') startDate, '' endDate) e2
   where startGap <= endGap and startGap >= '2011-01-01' and endGap <= 
'2011-02-01'
 and not exists (
   select 1 from events where startDate between startGap and endGap
 or endDate between startGap and endGap)
)
group by startGap;

-- 
Igor Tandetnik

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

Re: [sqlite] SQL Statement Help(selecting days).

[Nico Williams]

On Wed, May 18, 2011 at 4:10 PM, Petite Abeille
 wrote:
> On May 18, 2011, at 10:50 PM, Danilo Cicerone wrote:
>> How can I simulate a
>> calendar table(maybe using the strftime funtion)?
>
> Well, you have two broad options:
>
> (1) materialize the calendar as a table
> (2) virtualize the calendar as a generator
>
> The first option is brutal, but simple. E.g. create a table with, say, all 
> the julian date from 2101 to 21001231 (2451544 to 2488433 JD, about 
> 36,889 records for a century worth of date)
>
> The second option is a bit more involved, but you could have a virtual 
> calendar table that generate the relevant date span on demand:
>
> http://www.sqlite.org/vtab.html
>
> Unfortunately, there is no direct way to generate rows in SQLite as there is, 
> for example, in Oracle or such:

I have a virtual table that allows you to split strings and count
numbers, which could be used as a row generator.  I really want to
polish it off and even, some day, add syntactic sugar (calling this
"table functions"), but lack for time.  Would it help if I posted this
somewhere?

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

Re: [sqlite] DB triggers: Initial patch

[Nico Williams]

On Wed, May 18, 2011 at 4:14 PM, Petite Abeille
 wrote:
> Very nice, thanks for sharing :)
>
> Wish such a functionality was part of the stock SQLite :))

Thanks for the kind words!  I will do my best to make it palatable and
hopefully desirable for the core dev team to integrate my patches.  I
don't know if expressions of support from the community will help, but
I can't imagine they'd hurt.  I've some work to do still for that:

 - tests
 - docs
 - setup Fossil clones that folks can pull from (this means getting my
hosting provider to add support for Fossil, so it won't happen soon
enough)

There were some subtle bugs in the patch I posted.  The current patch
handles attached DBs and so on, and until I develop tests (which
should help find any remaining bugs) I don't intend to make any more
changes; I will post this patch later today.

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

Re: [sqlite] DB triggers: Initial patch

[Petite Abeille]

On May 16, 2011, at 7:30 PM, Nico Williams wrote:

> In my next post I'll post a [681 line, 28KB unified diff, or 504 line
> regular diff] patch implements the following DB triggers:
> 
> - AFTER DATABASE CONNECT
> - AFTER TRANSACTION BEGIN
> - BEFORE TRANSACTION COMMIT

Very nice, thanks for sharing :)

Wish such a functionality was part of the stock SQLite :))

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

Re: [sqlite] SQL Statement Help(selecting days).

[Danilo Cicerone]

Thanks again.

2011/5/18 Petite Abeille :
>
> On May 18, 2011, at 10:50 PM, Danilo Cicerone wrote:
> (1) materialize the calendar as a table
> (2) virtualize the calendar as a generator
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Statement Help(selecting days).

[Petite Abeille]

On May 18, 2011, at 10:50 PM, Danilo Cicerone wrote:

> How can I simulate a
> calendar table(maybe using the strftime funtion)?

Well, you have two broad options:

(1) materialize the calendar as a table 
(2) virtualize the calendar as a generator

The first option is brutal, but simple. E.g. create a table with, say, all the 
julian date from 2101 to 21001231 (2451544 to 2488433 JD, about 36,889 
records for a century worth of date) 

The second option is a bit more involved, but you could have a virtual calendar 
table that generate the relevant date span on demand:

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

Unfortunately, there is no direct way to generate rows in SQLite as there is, 
for example, in Oracle or such:

http://www.orafaq.com/wiki/Oracle_Row_Generator_Techniques

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

Re: [sqlite] SQL Statement Help(selecting days).

[Danilo Cicerone]

Thanks Petite,
I get it, great. Only another quick question, How can I simulate a
calendar table(maybe using the strftime funtion)?
Danilo

2011/5/18 Petite Abeille :
>
> On May 18, 2011, at 10:17 PM, Pavel Ivanov wrote:
>
>> SQL wasn't intended for and can't solve such tasks.
>
> Of course it can.
>
> Assuming a calendar and an event table:
>
> select calendar.date
> from   calendar
>
> where calendar.date between '20110101' and '20110201'
> and not exists ( select 1 from event where calendar.date between 
> event.start_date and event.end_date)
>
> And where are analytics when one need them? :)
>
> http://www.orafaq.com/node/55
>
> ___
> 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] SQL Statement Help(selecting days).

[Petite Abeille]

On May 18, 2011, at 10:17 PM, Pavel Ivanov wrote:

> SQL wasn't intended for and can't solve such tasks.

Of course it can.

Assuming a calendar and an event table:

select calendar.date
from   calendar

where calendar.date between '20110101' and '20110201'
and not exists ( select 1 from event where calendar.date between 
event.start_date and event.end_date) 

And where are analytics when one need them? :)

http://www.orafaq.com/node/55

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

Re: [sqlite] SQL Statement Help(selecting days).

[Pavel Ivanov]

> I need help to build a statement in order to select all days free from
> events in a specific time range.

This kind of task should be implemented in your application. SQL
wasn't intended for and can't solve such tasks.


Pavel


On Wed, May 18, 2011 at 4:06 PM, Danilo Cicerone  wrote:
> Hi to all,
> I need help to build a statement in order to select all days free from
> events in a specific time range.
> E.g.:
>
> - Range(-MM-DD) from 2011-01-01 to 2011-02-01.
> - Event 1 from 2011-01-02 to 2011-01-08
> - Event 2 from 2011-01-06 to 2011-01-12
> - Event 3 from 2011-01-18 to 2011-01-21
>
> Resulting Days Free:
> - from 2011-01-01 to 2011-01-01
> - from 2011-01-13 to 2011-01-17
> - from 2011-01-22 to 2011-02-01
>
> Thanks for your help in advance.
> Danilo
> ___
> 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] SQLITE_CANTOPEN

[Brancke, Brad]

Dear SQL gurus -

I have been using SQLite for several years and love it. Currently it is
being used on a small embedded Linux device. 

 

Once in a while when we power on the device and start my application,
this query fails:

"SELECT storage_enum, mode, GPSDisplayDMS, UserRealID, UserEffID FROM
Settings;"

with the reason "unable to open database file" 

 

I suspect something didn't get closed properly when the unit powered
off.

 

Weird:

1.   If I copy the db file to my PC, it opens and reads just fine.

2.   If I just make a copy of it on the device itself (same folder
and everything), then I can query the *copy* of the file just fine.

 

sqlite3_open() always succeeds and returns SQLITE_OK. The failure is
when sqlite3_exec() tries to run. Then we sqlite3_close() the db.

 

I looked at the source code to see where CANTOPEN is returned and
thought the open() was most likely, but a small prg to see if the db
would open() always succeeds.

open() at least for modes RDWR and RDONLY always succeed on the
"unopenable" db file:

fd = open(db_filename, O_RDWR);

fd = open(db_filename, O_RDONLY); 

 

Google and Sqlite.org suggested:

sqlite3_file_control(db, "main", SQLITE_LAST_ERRNO, _value);

so I added that after the call to sqlite3_exec() failed. That returned
SQLITE_OK, but to my dismay,  errno_value was 0 when that returned!

 

Then I tried:

sqlite3_config(SQLITE_CONFIG_LOG, _func, (void *)dbname)

which returns a several different non-zero (error) values dep on where I
call it, so logging is out too.

 

There's plenty of disk space left and I don't see anything wrong with
permissions (the copy in the same folder works fine).

 

Any suggestions, or general thoughts on recovery?

 

Thanks a lot

 

Brad Brancke

 

 

 

 

 

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

[sqlite] SQL Statement Help(selecting days).

[Danilo Cicerone]

Hi to all,
I need help to build a statement in order to select all days free from
events in a specific time range.
E.g.:

- Range(-MM-DD) from 2011-01-01 to 2011-02-01.
- Event 1 from 2011-01-02 to 2011-01-08
- Event 2 from 2011-01-06 to 2011-01-12
- Event 3 from 2011-01-18 to 2011-01-21

Resulting Days Free:
- from 2011-01-01 to 2011-01-01
- from 2011-01-13 to 2011-01-17
- from 2011-01-22 to 2011-02-01

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

[sqlite] About Fossil usage

[Nico Williams]

So I've stumbled twice trying to set up use of Fossil, both times with
respect to how to create a private branch.

First I did "fossil branch new ..." and didn't realize that the
workspace was not switched to be in that branch, so that my subsequent
commit didn't go to that branch.

My second attempt I did "fossil branch new ..." then "fossil checkout
...", then "fossil commit ... --private", but the last command
effectively created a new branch off my branch.

My third attempt I did a "fossil commit --private --branch ...".  That worked.

Also, I didn't expect commits and branches to go to the repository
that I cloned mine from.  That was surprising!  With git and other
similar VCSes such operations only hit the repository from which the
workspace was checked out.  Is this a configuration issue?

These are minor issues, IMO.  I expected Fossil to be painful (most
VCSes are), but aside from the above it's actually a pleasure.
Thanks,

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

Re: [sqlite] Auto-checkpoint when read transaction finishes? (was: Bug or not: WAL file is not auto-checkpointed when transaction finishes)

[Richard Hipp]

On Wed, May 18, 2011 at 11:06 AM, Nikolaus Rath  wrote:

>
> So considering this as feature request: do you have any thoughts on
> making SQLite consider auto-checkpointing when a read transaction
> finishes?
>

There might be performance implications.  But we'll look into it.



-- 
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] Auto-checkpoint when read transaction finishes? (was: Bug or not: WAL file is not auto-checkpointed when transaction finishes)

[Nikolaus Rath]

Richard Hipp  writes:
> On Tue, May 17, 2011 at 5:11 PM, Nikolaus Rath 
>  wrote:
>> Please consider the attached testcase. The WAL file should grow to at
>> most 1 MB. This limit is exceeded, because the script adds data while a
>> second cursor has an active SELECT query. However, when the SELECT query
>> finishes, the WAL file is not auto-checkpointed either.
>>
>> An access pattern similar to the one in the testcase thus results in
>> unbounded growth of the WAL file, even though auto-checkpointing is
>> enabled, and there are time slots where it could be performed (no
>> transaction active).
>>
>> $ python bug_or_not.py test.sqlite
>> Max WAL file should be 1 MB
>> Starting query with cursor 2..
>> Inserting 5 MB of data with cursor 1
>> Size of WAL file is now: 16467.0 kB
>> Finishing cursor 2 query..
>> Size of WAL file is now: 16467.0 kB
>> Starting query with cursor 2..
>> Inserting 5 MB of data with cursor 1
>> Size of WAL file is now: 32954.0 kB
>> Finishing cursor 2 query..
>> Size of WAL file is now: 32954.0 kB
>>
>> Bug or not?
>>
>
> Not.  It is working as it was designed and documented to work.  Perhaps you
> mean that you want to start a discussion about the design, perhaps leading
> to a change in design.
>
> I'm rather pedantic about the use of the word "bug".  To me, a "bug" means
> it gets the wrong answer.  And for the purpose of that definition, crashing
> is considered the wrong answer.  Failing to do a checkpoint when you think
> it ought to, even though it is checkpointing as advertised, is not a "bug".
> You can argue that there is a need to enhance auto-checkpoint to make it
> more programmer-friendly.  But that is a feature request, not a bug.

That's why I posed it as a question, not a statement :-). My apologies
if it still came across the wrong way.


So considering this as feature request: do you have any thoughts on
making SQLite consider auto-checkpointing when a read transaction
finishes?


Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug or not: WAL file is not auto-checkpointed when transaction finishes

[Richard Hipp]

On Tue, May 17, 2011 at 5:11 PM, Nikolaus Rath  wrote:

> Hi,
>
> Please consider the attached testcase. The WAL file should grow to at
> most 1 MB. This limit is exceeded, because the script adds data while a
> second cursor has an active SELECT query. However, when the SELECT query
> finishes, the WAL file is not auto-checkpointed either.
>
> An access pattern similar to the one in the testcase thus results in
> unbounded growth of the WAL file, even though auto-checkpointing is
> enabled, and there are time slots where it could be performed (no
> transaction active).
>
> $ python bug_or_not.py test.sqlite
> Max WAL file should be 1 MB
> Starting query with cursor 2..
> Inserting 5 MB of data with cursor 1
> Size of WAL file is now: 16467.0 kB
> Finishing cursor 2 query..
> Size of WAL file is now: 16467.0 kB
> Starting query with cursor 2..
> Inserting 5 MB of data with cursor 1
> Size of WAL file is now: 32954.0 kB
> Finishing cursor 2 query..
> Size of WAL file is now: 32954.0 kB
>
>
> Bug or not?
>

Not.  It is working as it was designed and documented to work.  Perhaps you
mean that you want to start a discussion about the design, perhaps leading
to a change in design.

I'm rather pedantic about the use of the word "bug".  To me, a "bug" means
it gets the wrong answer.  And for the purpose of that definition, crashing
is considered the wrong answer.  Failing to do a checkpoint when you think
it ought to, even though it is checkpointing as advertised, is not a "bug".
You can argue that there is a need to enhance auto-checkpoint to make it
more programmer-friendly.  But that is a feature request, not a bug.


>
> Best,
>
>   -Nikolaus
>
> --
>  »Time flies like an arrow, fruit flies like a Banana.«
>
>  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C
>
> ___
> 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] How to install and configure SQLite on ipad.

[Jean-Denis Muys]

On 17 mai 2011, at 19:16, irfan khan wrote:

Thanks a lot for your replay.

But we are developing new application for ipad for some buisseness need and
need to configure sqllite database for the same.
I am  MS SQL database administrator and my developer team asked me to
configure sqlite for ipad.
please send me any related documents or details.


To reiterate:

- SQLite is very different from MS SQL and most other databases in that it 
requires ZERO *runtime* configuration
- SQLite is used as a C library that provides a handful of C functions. To 
simplify, those functions eventually lead you to execute SQL statements 
provided in C strings.
- As a C library, it can be compiled with a few optional variations that you 
are unlikely to need to be aware of as you learn using it.

- SQLite is already installed in iOS by Apple. For most uses, you will not need 
to use a different version.
- If it eventually turns out you need one of the optional compile-time 
variations mentioned above that were not included by Apple, it's simple to add 
your own version, and compile it with whatever options you need. You can worry 
about that later however and ask again if you need help.

- Regarding this and your request to be sent documentation: 
http://lmgtfy.com/?q=Sqlite+documentation=1

Jean-Denis

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