[sqlite] Exception writing to database from multiple processes

[David Carter]

Hello, 

 

I am currently using the SQLite Amalgamation v3.6.19 from
http://www.sqlite.org/download.html in an ISAPI Extension to write out
usage statistics to an SQLite database.  

 

When the ISAPI extension is running inside an Application Pool with a
single worker process, everything works fine.  However, when it is run
inside an Application Pool with multiple worker processes, the database
soon becomes locked and cannot be written to by any of the worker
processes.  

 

Each worker process has a separate background thread which writes to the
database every 5 seconds.  Each write is performed as a single
transaction starting with "BEGIN IMMEDIATE".  

 

Any help you can provide would be greatly appreciated.

 

Thanks, 

 

David

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

[sqlite] test DBD::SQLite 1.26_05 - foreign keys!

[Darren Duncan]

All,

I am pleased to announce that DBD::SQLite (Self Contained RDBMS in a Perl DBI 
Driver) version 1.26_05 has been released on CPAN (by Adam Kennedy).

   http://search.cpan.org/~adamk/DBD-SQLite-1.26_05/

This developer release bundles the brand-new SQLite version 3.6.19, which adds 
support for enforcing SQL foreign keys.  See http://sqlite.org/foreignkeys.html 
for the details of the foreign key support that SQLite now has.

Also be sure to look at the section 
http://sqlite.org/foreignkeys.html#fk_enable 
, because you have to enable a pragma on each connect to use the foreign keys 
feature; it isn't yet on by default for backwards compatibility purposes.

As I imagine many of you have been pining away for SQLite to support this 
feature for a long while, you'll want to dig in right away.

TESTING NEEDED!

Please bash the hell out of the latest DBD::SQLite and report any outstanding
bugs on RT.  Test your dependent or compatible projects with it, which includes
any DBMS-wrapping or object persistence modules, and applications.

And especially try actually using foreign keys with SQLite.

As the official release announcement says:  "This release has been extensively 
tested (we still have 100% branch test coverage).  [The SQLite developers] 
consider this release to be production ready.  Nevertheless, testing can only 
prove the presence of bugs, not their absence.  So if you encounter problems, 
please let us know."

See also http://www.sqlite.org/changes.html for a list of everything else that 
changed in SQLite itself over the last few months.

If you want in to DBD::SQLite development, then join the following email/IRC
forums which MST created (the mailing list, I am administrating):

   http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite

   #dbd-sqlite on irc.perl.org

And the canonical version control is at:

   http://svn.ali.as/cpan/trunk/DBD-SQLite/

Patches welcome.  Ideas welcome.  Testing welcome.  Whining to /dev/null.

If you feel that a bug you find is in SQLite itself rather than the Perl DBI 
driver for it, the main users email forum for SQLite in general is at:

   http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

... where you can report it as an appropriate list post (the SQLite issue 
tracking system is no longer updateable by the public; posting in the list can 
cause an update there by a registered SQLite developer).

Please do not reply to me directly with your responses.  Instead send them to
the forums or file with RT as is appropriate.

Thank you. -- Darren Duncan

P.S.  DBD::SQLite has at least 1 known bug, also in version 1.25, with regard 
to 
full-text search (FTS3); there is an included new failing test, which currently 
is set to skip so the CPAN testers don't issue fails, but the issue behind it 
should hopefully be fixed before the next DBD::SQLite release.  We decided that 
shipping DBD::SQLite now with the skipping test was preferable to waiting for 
that fix so you could get the new foreign keys feature the soonest.

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

[sqlite] Why FTS3 has the limitations it does

[John Crenshaw]

The severe limitations on FTS3 seemed odd to me, but I figured I could
live with them. Then I starting finding that various queries were giving
strange "out of context" errors with the MATCH operator, even though I
was following all the documented rules. As a result I started looking
deeply into what is going on with FTS3 and I found something that
bothers me.

 

These limitations are really completely arbitrary. They should be
removable.

 

You can only use a single index to query a table, after that everything
else has to be done with a scan of the results, fair enough. But with
FTS3, the match operator works ONLY when the match expression is
selected for the index. This means that if a query could allow a row to
be selected by either rowid, or a MATCH expression, you can have a
problem. If the rowid is selected for use as the index, the MATCH won't
be used as the index, and you get errors. Similarly, a query with two
MATCH expressions will only be able to use one as the index, so you get
errors from the second.

 

Now, the reason this is arbitrary is that the MATCH expressions not used
for the index are STILL given a chance to work at the function level. If
a MATCH function were implemented, these limitations would disappear.
Oddly enough, FTS3 doesn't expose a function for MATCH. Unfortunately,
there is a good reason. It turns out that, as currently designed,
testing an expression against a single known row requires a full table
scan for every test. Inside my match function I would know the rowid
(docid) for a record. While this can quickly look up the content, it is
impossible to look up segments by document id, so checking the match on
that row requires a lookup of all possible docids for the match
expression, and a full scan of those results. Clearly this makes a
function level match utter nonsense.

 

My first question is, why was FTS designed like this in the first place?
Surely this was clear during the design stage, when the design could
have been easily changed to accommodate the lookups required for a MATCH
function. Is there some compelling performance benefit? Something I
missed?

 

My second question is, can we expect this to change at some point? Just
adding the MATCH function would eliminate virtually every remaining FTS
limitation. All that is needed is the ability to lookup by a combination
of docid and term. Isn't a hash already built while creating a list of
terms for storage? What if that hash were stored, indexed by docid?

 

For now I've modified my code to always index on the MATCH expression,
if there is any. This at least eliminates the random errors, but does
nothing wonderful for performance.

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

Re: [sqlite] Maintaining data and foreign keys question

[Dennis Volodomanov]

Sorry for the double-post, I didn't see the first one appear for a bit and I 
assumed it didn't go out.

   Dennis

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

Re: [sqlite] Corrupted database

[Filip Navara]

On Wed, Oct 14, 2009 at 6:06 PM, Dan Kennedy  wrote:
>
>> The problem I think may be people using TerminateThread();  that's how
>> you hard kill a thread.  It seems that can interrupt an I/O
>> operation --
>> ie an operation that writes more than one cluster at a time.  Meaning,
>> synch = full may have nothing to do with it.  If you have to say write
>> more than one cluster (disk block), TerminateThread looks like it can
>> abort the IO op in the middle of a multi-block op?  I'm trying to run
>> that down but can't yet find anything that verifies this.
>
> Even if it does, which seems quite plausible, the only way
> I can see this causing corruption is if you are in persistent
> journal mode and a (weak) checksum gives you a false positive
> on the last, corrupted, record in the journal file.

This is quite possibly happening in our case. Any way to prove the
theory? What should we look for?

The problem is that journal_mode=persist was the only usable
journaling mode on Windows due to the way file deleting is handled (in
SQLite). Using journal_mode=delete is problematic on any machine with
TortoiseSVN/TortoiseGIT or other programs installed. Now that
journal_mode=truncate exists we can try switching to that.

Best regards,
Filip Navara
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Corrupted database

[Filip Navara]

Some threads were hard killed (using the .NET
"Thread.IsBackgroundThread" mechanism, which uses TerminateThread
AFAIK) in that version during normal operation, but none of them do
database writes. They could have been doing database reads though.

The whole application has been hard-killed few times when exception
happened during database manipulation though.

Best regards,
Filip Navara

On Wed, Oct 14, 2009 at 5:45 PM, McClellen, Chris
 wrote:
> Do you ever teriminate threads via TerminateThread() or run as a service 
> (where the scm will call terminateThread() once your main service thread 
> exits)?  In other words do you hard-kill your threads?
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Filip Navara
> Sent: Wednesday, October 14, 2009 7:15 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Corrupted database
>
> On Mon, Oct 12, 2009 at 8:56 PM, McClellen, Chris
>  wrote:
>> What is your synchronous set to?  Full?  FYI If you are using .NET data
>> providers, it is set to "Normal" by default.
>
> Normal or Off, but no power failure was involved. (Yes,
> System.Data.SQLite is used)
>
>> If it is not set to full, I have seen corruption when an application
>> crashes, or exits when a thread is in the middle of updating the db
>> (Synchronous = OFF makes corruption even easier in this case).  I have
>> seen apps that do not wait for background threads to finish before
>> termination, and without full sync on, either the db or the log gets
>> corrupted.  A corrupted log can cause problems for you db on next run
>> when recovery happens.
>
> Sounds suspiciously like our case, but still Synchronous=off is
> supposed to work in the event of application crash, hard killed
> threads and so on. Previous version of the application frequently
> forgot to close the databases on exit and did other nasty things that
> and now fixed, but none of them should cause the database to be
> corrupted.
>
> Best regards,
> Filip Navara
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] group_concat optimization

[??????? ????????]

I make use of group_concat aggregate function and I found
it very slow, especially when there are thousands of lines per group.
This is because it reallocates memory on each processed row.
I changed just one line in sqlite3StrAccumAppend():

  szNew += N + 1
  
to something like this:

  do{ szNew = szNew*2 + 1; } while (szNew <= p->nChar + N);

and now group_concat works fine.
Would the authors be kind to implement such optimization?
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database in tmpfs

[souvik.datta]

Hello,

I am creating a db in tmpfs with following PRAGMA settings:
PRAGMA encoding = "UTF-8";
PRAGMA default_cache_size = 4000;
PRAGMA synchronous = OFF;
PRAGMA temp_store = MEMORY;
PRAGMA journal_mode = OFF;

I am creating four tables on the run and inserting 8000 records in  two of 
these tables ( 4000 each ) and it is taking around 21 seconds(on an average). 
The records contain metadata information of audio files along with file stat 
info. When tmpfs is not used, the time taken is almost same.

Please note that all the two tables that are getting populated are indexed :
Table 1 is having 1 string Index
Table 2 is having 2 string index

The insertion is done under BEGIN IMMEDIATE transaction.(100 records at a time) 
under a single thread.

Pl. suggest why I am not able to get any performance gain for tmpfs?

Thanks and Regards,
Souvik


Please do not print this email unless it is absolutely necessary. 

The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments. 

WARNING: Computer viruses can be transmitted via email. The recipient should 
check this email and any attachments for the presence of viruses. The company 
accepts no liability for any damage caused by any virus transmitted by this 
email. 

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

[sqlite] Maintaining data and foreign keys question

[Dennis Volodomanov]

Hello all,

I'm implementing the new FOREIGN KEY support in our database and I have this 
small problem/question.

Before I had triggers to take care of maintaining deletion of data that's not 
referenced by any records, but I can't seem to reproduce the same behavior with 
just the foreign key commands. Maybe I'm missing something.

So, what I had was:

CREATE TABLE TableA(ID INTEGER PRIMARY KEY, SomeData, DataID INTEGER);
CREATE TABLE TableData(ID INTEGER PRIMARY KEY, Data UNIQUE);

Where DataID in TableA is ID in TableData.

I also had the following triggers:

CREATE TRIGGER TriggerADelete AFTER DELETE ON TableA WHEN OLD.DataID<>0
BEGIN
DELETE FROM TableData WHERE TableData.ID=OLD.DataID AND OLD.DataID NOT 
IN (SELECT DataID FROM TableA WHERE DataID=OLD.DataID LIMIT 1);
END;

CREATE TRIGGER TriggerAUpdate AFTER UPDATE ON TableA WHEN OLD.DataID<>0
BEGIN
DELETE FROM TableData WHERE TableData.ID=OLD.DataID AND OLD.DataID NOT 
IN (SELECT DataID FROM TableA WHERE DataID=OLD.DataID LIMIT 1);
END;

That kept the TableData empty of any non-referenced values.

What I've got now is (which doesn't work as I expected it to):

CREATE TABLE TableA(ID INTEGER PRIMARY KEY, SomeData, DataID INTEGER, FOREIGN 
KEY(DataID) REFERENCES TableData(ID) ON DELETE CASCADE ON UPDATE CASCADE);
CREATE TABLE TableData(ID INTEGER PRIMARY KEY, Data UNIQUE);

Is there a way to reproduce the same without using triggers? If not, I'm not 
sure what the advantage of using foreign keys is in my case...

Thank you for your thoughts!

   Dennis

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

Re: [sqlite] Tackling the Date Scan SQL

[Igor Tandetnik]

Rick Ratchford wrote:
> Tried that. It doesn't work.
> 
> For example, when I used 09/01 as my start and 04/01 as my ending, what I
> got back was:
> 
> 1988  1  4
> 1988  1  5
> 1988  1  6
> ...
> ...
> 
> Instead of:
> 
> 1988 9  1
> 1988 9  2

Why do you expect 1988-01-04 et al to be excluded? These dates meet your 
condition of falling between 09/01 and 04/01 - they should appear _somewhere_ 
in the resultset.

Igor Tandetnik


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

[sqlite] Foreign keys question

[Dennis Volodomanov]

Hello all,

I'm implementing the new FOREIGN KEY support in our database and I have this 
small problem/question.

Before I had triggers to take care of maintaining deletion of data that's not 
referenced by any records, but I can't seem to reproduce the same behavior with 
just the foreign key commands. Maybe I'm missing something.

So, what I had was:

CREATE TABLE TableA(ID INTEGER PRIMARY KEY, SomeData, DataID INTEGER);
CREATE TABLE TableData(ID INTEGER PRIMARY KEY, Data UNIQUE);

Where DataID in TableA is ID in TableData.

I also had the following triggers:

CREATE TRIGGER TriggerADelete AFTER DELETE ON TableA WHEN OLD.DataID<>0
BEGIN
DELETE FROM TableData WHERE TableData.ID=OLD.DataID AND OLD.DataID NOT 
IN (SELECT DataID FROM TableA WHERE DataID=OLD.DataID LIMIT 1);
END;

CREATE TRIGGER TriggerAUpdate AFTER UPDATE ON TableA WHEN OLD.DataID<>0
BEGIN
DELETE FROM TableData WHERE TableData.ID=OLD.DataID AND OLD.DataID NOT 
IN (SELECT DataID FROM TableA WHERE DataID=OLD.DataID LIMIT 1);
END;

That kept the TableData empty of any non-referenced values.

What I've got now is (which doesn't work as I expected it to):

CREATE TABLE TableA(ID INTEGER PRIMARY KEY, SomeData, DataID INTEGER, FOREIGN 
KEY(DataID) REFERENCES TableData(ID) ON DELETE CASCADE ON UPDATE CASCADE);
CREATE TABLE TableData(ID INTEGER PRIMARY KEY, Data UNIQUE);

Is there a way to reproduce the same without using triggers? If not, I'm not 
sure what the advantage of using foreign keys is in my case...

Thank you for your thoughts!

   Dennis

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

Re: [sqlite] Tackling the Date Scan SQL

[Rick Ratchford]

Tried that. It doesn't work.

For example, when I used 09/01 as my start and 04/01 as my ending, what I
got back was:

1988  1  4
1988  1  5
1988  1  6
...
...

Instead of:

1988 9  1
1988 9  2
...
...
1989 3  30
1989 3  31
1989 4  1
1989 9  1
1989 9  2
...
...
1990 3  31
1990 4  1
...
...
Etc. 

Thanks for the suggestion.

:)
Rick





#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
#>boun...@sqlite.org] On Behalf Of Pavel Ivanov
#>Sent: Wednesday, October 14, 2009 7:45 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] Tackling the Date Scan SQL
#>
#>Try to change this:
#>
#>>  "ORDER BY Year, ((Month - " & lngStartMth & ")*100 + (Day - " &
#>lngStartDay
#>> & ") + 1300) % 1300"
#>
#>to this:
#>
#>>  "ORDER BY Year, Month, Day"
#>
#>
#>Pavel
#>
#>On Wed, Oct 14, 2009 at 8:08 PM, Rick Ratchford
#> wrote:
#>> With Igor's help, I have this SQL statement that pulls out records based
#>on
#>> a start and stop date.
#>>
#>> "SELECT Year, Month, Day, Open, High, Low, Close FROM [" & gsTableName &
#>"]
#>> " & _
#>>  "WHERE ((Month - " & lngStartMth & ")*100 + (Day - " & lngStartDay & ")
#>+
#>> 1300) % 1300 <= ((" & _
#>>  lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay - lngStartDay & ")
#>+
#>> 1300) % 1300 " & _
#>>  "ORDER BY Year, ((Month - " & lngStartMth & ")*100 + (Day - " &
#>lngStartDay
#>> & ") + 1300) % 1300"
#>>
#>> It worked well, except I added the 'Year' to the ORDER BY so that I
#>could
#>> get the information in this format:
#>>
#>> (Example: 10/1 to 4/1)
#>>
#>> 1988 10  01  
#>> 1988 10  02  
#>>
#>> ...crossing the end of the year...
#>> ...into the next year...
#>>
#>> 1989 03  29  
#>> 1989 03  30  
#>> 1989 03  31  
#>> 1989 04  01  
#>>
#>> ...starting the next 10/01 to 04/01 extraction...
#>>
#>> 1989 10  01  
#>> 1989 10  02  
#>> ...
#>> ...
#>> ...and so forth.
#>>
#>>
#>> Problem is that by adding the 'Year' to ORDER BY, while it does in fact
#>give
#>> me the above format, it also includes info I don't need. For example,
#>when
#>> it started at 10/01 for 1988 and ended the year, it then continued to
#>04/01
#>> within the same 1988 year!
#>>
#>> Any suggestions?
#>>
#>> Thanks.
#>>
#>> Rick
#>>
#>>
#>>
#>>
#>>
#>>
#>>
#>> ___
#>> sqlite-users mailing list
#>> sqlite-users@sqlite.org
#>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
#>>
#>___
#>sqlite-users mailing list
#>sqlite-users@sqlite.org
#>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Mac OS Snow Leopard 32bit vs 64bit

[Melton Low]

Worked cleanly now.
Thanks again.

Mel

On Wed, Oct 14, 2009 at 7:02 PM, William Kyngesburye
wrote:

> Odd, works fine here.  ... ah, make sure you use the --disable-
> dependency-tracking option.  The default dependency tracking does not
> work with multiple architectures.  It's just a compilation process and
> not using it has no effect on the generated code.
>
> On Oct 14, 2009, at 7:42 PM, Melton Low wrote:
>
> > I got a compile error when the 64bit flag was include.  Worked fine
> > if I
> > just use the 32bit flag.
> > Thanks for your help.
> >
> > Mel
> >
> > On Wed, Oct 14, 2009 at 6:01 PM, William Kyngesburye
> > wrote:
> >
> >> Add this to your configure command (applies to most all configures):
> >>
> >> CFLAGS="-arch i386 -arch x86_64"
> >>
> >>
> >>
> >> On Oct 14, 2009, at 4:46 PM, Melton Low wrote:
> >>
> >> I am on a Mac laptop running Mac OS X 10.6.1 (Snow Leopard).
> >>>
> >>> Version of Sqlite I want to build is 3.6.19.  When running
> >>> 'configure
> >>> --help' I didn't find an option to set the build to 32bit.  Is
> >>> sqlite
> >>> automatically build as a 32bit app with 32bit libraries?  Did I
> >>> miss an
> >>> option?
> >>>
> >>> Mel
> >>> ___
> >>> sqlite-users mailing list
> >>> sqlite-users@sqlite.org
> >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>>
> >>
> >> -
> >> William Kyngesburye 
> >> http://www.kyngchaos.com/
> >>
> >> All generalizations are dangerous, even this one.
> >>
> >>
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> -
> William Kyngesburye 
> http://www.kyngchaos.com/
>
> "We are at war with them. Neither in hatred nor revenge and with no
> particular pleasure I shall kill every ___ I can until the war is
> over. That is my duty."
>
> "Don't you even hate 'em?"
>
> "What good would it do if I did? If all the many millions of people of
> the allied nations devoted an entire year exclusively to hating the
>  it wouldn't kill one ___ nor shorten the war one day."
>
>  "And it might give 'em all stomach ulcers."
>
> - Tarzan, on war
>
> ___
> 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] Mac OS Snow Leopard 32bit vs 64bit

[William Kyngesburye]

Odd, works fine here.  ... ah, make sure you use the --disable- 
dependency-tracking option.  The default dependency tracking does not  
work with multiple architectures.  It's just a compilation process and  
not using it has no effect on the generated code.

On Oct 14, 2009, at 7:42 PM, Melton Low wrote:

> I got a compile error when the 64bit flag was include.  Worked fine  
> if I
> just use the 32bit flag.
> Thanks for your help.
>
> Mel
>
> On Wed, Oct 14, 2009 at 6:01 PM, William Kyngesburye
> wrote:
>
>> Add this to your configure command (applies to most all configures):
>>
>> CFLAGS="-arch i386 -arch x86_64"
>>
>>
>>
>> On Oct 14, 2009, at 4:46 PM, Melton Low wrote:
>>
>> I am on a Mac laptop running Mac OS X 10.6.1 (Snow Leopard).
>>>
>>> Version of Sqlite I want to build is 3.6.19.  When running  
>>> 'configure
>>> --help' I didn't find an option to set the build to 32bit.  Is  
>>> sqlite
>>> automatically build as a 32bit app with 32bit libraries?  Did I  
>>> miss an
>>> option?
>>>
>>> Mel
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>> -
>> William Kyngesburye 
>> http://www.kyngchaos.com/
>>
>> All generalizations are dangerous, even this one.
>>
>>
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-
William Kyngesburye 
http://www.kyngchaos.com/

"We are at war with them. Neither in hatred nor revenge and with no  
particular pleasure I shall kill every ___ I can until the war is  
over. That is my duty."

"Don't you even hate 'em?"

"What good would it do if I did? If all the many millions of people of  
the allied nations devoted an entire year exclusively to hating the  
 it wouldn't kill one ___ nor shorten the war one day."

 "And it might give 'em all stomach ulcers."

- Tarzan, on war

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

Re: [sqlite] Mac OS Snow Leopard 32bit vs 64bit

[Melton Low]

I got a compile error when the 64bit flag was include.  Worked fine if I
just use the 32bit flag.
Thanks for your help.

Mel

On Wed, Oct 14, 2009 at 6:01 PM, William Kyngesburye
wrote:

> Add this to your configure command (applies to most all configures):
>
> CFLAGS="-arch i386 -arch x86_64"
>
>
>
> On Oct 14, 2009, at 4:46 PM, Melton Low wrote:
>
>  I am on a Mac laptop running Mac OS X 10.6.1 (Snow Leopard).
>>
>> Version of Sqlite I want to build is 3.6.19.  When running 'configure
>> --help' I didn't find an option to set the build to 32bit.  Is sqlite
>> automatically build as a 32bit app with 32bit libraries?  Did I miss an
>> option?
>>
>> Mel
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> -
> William Kyngesburye 
> http://www.kyngchaos.com/
>
> All generalizations are dangerous, even this one.
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tackling the Date Scan SQL

[Pavel Ivanov]

Try to change this:

>  "ORDER BY Year, ((Month - " & lngStartMth & ")*100 + (Day - " & lngStartDay
> & ") + 1300) % 1300"

to this:

>  "ORDER BY Year, Month, Day"


Pavel

On Wed, Oct 14, 2009 at 8:08 PM, Rick Ratchford
 wrote:
> With Igor's help, I have this SQL statement that pulls out records based on
> a start and stop date.
>
> "SELECT Year, Month, Day, Open, High, Low, Close FROM [" & gsTableName & "]
> " & _
>  "WHERE ((Month - " & lngStartMth & ")*100 + (Day - " & lngStartDay & ") +
> 1300) % 1300 <= ((" & _
>  lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay - lngStartDay & ") +
> 1300) % 1300 " & _
>  "ORDER BY Year, ((Month - " & lngStartMth & ")*100 + (Day - " & lngStartDay
> & ") + 1300) % 1300"
>
> It worked well, except I added the 'Year' to the ORDER BY so that I could
> get the information in this format:
>
> (Example: 10/1 to 4/1)
>
> 1988 10  01  
> 1988 10  02  
>
> ...crossing the end of the year...
> ...into the next year...
>
> 1989 03  29  
> 1989 03  30  
> 1989 03  31  
> 1989 04  01  
>
> ...starting the next 10/01 to 04/01 extraction...
>
> 1989 10  01  
> 1989 10  02  
> ...
> ...
> ...and so forth.
>
>
> Problem is that by adding the 'Year' to ORDER BY, while it does in fact give
> me the above format, it also includes info I don't need. For example, when
> it started at 10/01 for 1988 and ended the year, it then continued to 04/01
> within the same 1988 year!
>
> Any suggestions?
>
> Thanks.
>
> Rick
>
>
>
>
>
>
>
> ___
> 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] Mac OS Snow Leopard 32bit vs 64bit

[Melton Low]

I got a compile error when the 64bit flag was include.  Worked fine if I
just use the 32bit flag.
Thanks for your help.

Mel

On Wed, Oct 14, 2009 at 6:01 PM, William Kyngesburye
wrote:

> Add this to your configure command (applies to most all configures):
>
> CFLAGS="-arch i386 -arch x86_64"
>
>
>
> On Oct 14, 2009, at 4:46 PM, Melton Low wrote:
>
>  I am on a Mac laptop running Mac OS X 10.6.1 (Snow Leopard).
>>
>> Version of Sqlite I want to build is 3.6.19.  When running 'configure
>> --help' I didn't find an option to set the build to 32bit.  Is sqlite
>> automatically build as a 32bit app with 32bit libraries?  Did I miss an
>> option?
>>
>> Mel
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> -
> William Kyngesburye 
> http://www.kyngchaos.com/
>
> All generalizations are dangerous, even this one.
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Tackling the Date Scan SQL

[Rick Ratchford]

With Igor's help, I have this SQL statement that pulls out records based on
a start and stop date.

"SELECT Year, Month, Day, Open, High, Low, Close FROM [" & gsTableName & "]
" & _
 "WHERE ((Month - " & lngStartMth & ")*100 + (Day - " & lngStartDay & ") +
1300) % 1300 <= ((" & _
 lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay - lngStartDay & ") +
1300) % 1300 " & _
 "ORDER BY Year, ((Month - " & lngStartMth & ")*100 + (Day - " & lngStartDay
& ") + 1300) % 1300"

It worked well, except I added the 'Year' to the ORDER BY so that I could
get the information in this format:

(Example: 10/1 to 4/1)

1988 10  01  
1988 10  02  

...crossing the end of the year...
...into the next year...

1989 03  29  
1989 03  30  
1989 03  31  
1989 04  01  

...starting the next 10/01 to 04/01 extraction...

1989 10  01  
1989 10  02  
...
...
...and so forth.


Problem is that by adding the 'Year' to ORDER BY, while it does in fact give
me the above format, it also includes info I don't need. For example, when
it started at 10/01 for 1988 and ended the year, it then continued to 04/01
within the same 1988 year!

Any suggestions?

Thanks.

Rick







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

Re: [sqlite] Mac OS Snow Leopard 32bit vs 64bit

[William Kyngesburye]

Add this to your configure command (applies to most all configures):

CFLAGS="-arch i386 -arch x86_64"


On Oct 14, 2009, at 4:46 PM, Melton Low wrote:

> I am on a Mac laptop running Mac OS X 10.6.1 (Snow Leopard).
>
> Version of Sqlite I want to build is 3.6.19.  When running 'configure
> --help' I didn't find an option to set the build to 32bit.  Is sqlite
> automatically build as a 32bit app with 32bit libraries?  Did I miss  
> an
> option?
>
> Mel
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-
William Kyngesburye 
http://www.kyngchaos.com/

All generalizations are dangerous, even this one.


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

Re: [sqlite] readers and writer

[Simon Slavin]

On 14 Oct 2009, at 11:19pm, Roger Binns wrote:

> Simon Slavin wrote:
>> On 14 Oct 2009, at 7:45pm, Pavel Ivanov wrote:
>>
>>> sqlite> .timeout 1
>>
>> What is it that that command does ?  I looked for a PRAGMA but didn't
>> find one.  Or does it correspond to a check-and-retry loop which the
>> programmer has to do yourself in her or his own code ?
>
> It calls sqlite3_busy_timeout with the value supplied.
>
>  http://sqlite.org/c3ref/busy_timeout.html

I believe that page, and the one it points to

http://sqlite.org/c3ref/busy_handler.html

fill in the missing details.  Thank you.

Now all we need to know is whether the OP's reported error 'database  
is locked' from his API is equivalent to one of the SQLite errors  
SQLITE_BUSY or SQLITE_IOERR_BLOCKED.  And the answer to that is not  
part of SQLite.

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

Re: [sqlite] readers and writer

[priimak]

Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Simon Slavin wrote:
>   
>> On 14 Oct 2009, at 7:45pm, Pavel Ivanov wrote:
>>
>> 
>>> sqlite> .timeout 1
>>>   
>> What is it that that command does ?  I looked for a PRAGMA but didn't  
>> find one.  Or does it correspond to a check-and-retry loop which the  
>> programmer has to do yourself in her or his own code ?
>> 
>
> It calls sqlite3_busy_timeout with the value supplied.
>
>   http://sqlite.org/c3ref/busy_timeout.html
>   
Ok. Now everything makes sense. Perhaps documentation could be
a bit more clear on this subject though.

--
Dmitri Priimak

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

Re: [sqlite] readers and writer

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Simon Slavin wrote:
> On 14 Oct 2009, at 7:45pm, Pavel Ivanov wrote:
> 
>> sqlite> .timeout 1
> 
> What is it that that command does ?  I looked for a PRAGMA but didn't  
> find one.  Or does it correspond to a check-and-retry loop which the  
> programmer has to do yourself in her or his own code ?

It calls sqlite3_busy_timeout with the value supplied.

  http://sqlite.org/c3ref/busy_timeout.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrWTnEACgkQmOOfHg372QRlSACdF2e6Bj6KO3jzCs3feHi1uet1
VeYAn3JPOeKXftMZabmj6dWf73+6C2jj
=zfio
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Mac OS Snow Leopard 32bit vs 64bit

[Melton Low]

I am on a Mac laptop running Mac OS X 10.6.1 (Snow Leopard).

Version of Sqlite I want to build is 3.6.19.  When running 'configure
--help' I didn't find an option to set the build to 32bit.  Is sqlite
automatically build as a 32bit app with 32bit libraries?  Did I miss an
option?

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

Re: [sqlite] readers and writer

[Simon Slavin]

On 14 Oct 2009, at 7:45pm, Pavel Ivanov wrote:

> sqlite> .timeout 1

What is it that that command does ?  I looked for a PRAGMA but didn't  
find one.  Or does it correspond to a check-and-retry loop which the  
programmer has to do yourself in her or his own code ?

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

[sqlite] Conflict with SQLite ODBC Driver and MS Office 2007 ?

[Jack Ort]

Hello!  Not sure where to ask this question, so I apologize if this is not
appropriate.  I want to use SQLite as the basis for a new project where I
work.  I'm new to SQLite - this would be my first use of it beyond some
simple test applications.  Plan to use REBOL to develop a GUI frontend.  For
reporting, I thought I might use MS Access called by REBOL code to provide
canned reports against the SQLite database.  I believe I need to use the
SQLite ODBC driver (http://www.ch-werner.de/sqliteodbc/) for Access to link
to the SQLite db.

Now my question: my IT Manager is witholding approval of using SQLite
because he thinks he's heard of a conflict between the SQLite ODBC driver
and the SQL Server 2005 "stuff" that gets installed as part of a MS Office
2007 installation.  (Some users have Office 2007 - I have Office 2003 so I
cannot test.)

I suspect someone meant to refer to a conflict with SQL Server Express
instead of SQLite.  I cannot find anything in Google searches.

Does anyone here know of any conflicts I should be concerned about?  This
would be in a XP SP2 environment.

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

Re: [sqlite] Corrupted database

[D. Richard Hipp]

On Oct 14, 2009, at 2:59 PM, McClellen, Chris wrote:

> I think the issue is :
>
> Thread 1 does exitprocess/terminateprocess  (or process.kill, or
> anything like that)
> Thread 2 does write() -- the write I believe can be interrupted when
> partially complete in these cases  (only part of the blocks have been
> written to disk, the others are not even scheduled).  When a database
> has overflow chains (and a chain is being modified), this is
> particularly disastrous.


We assume that partial writes can occur on an unclean shutdown.  In  
fact, we run thousands and thousands of test cases to verify that  
partial writes do not corrupt the database file.

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Corrupted database

[McClellen, Chris]

I think the issue is :

Thread 1 does exitprocess/terminateprocess  (or process.kill, or
anything like that)
Thread 2 does write() -- the write I believe can be interrupted when
partially complete in these cases  (only part of the blocks have been
written to disk, the others are not even scheduled).  When a database
has overflow chains (and a chain is being modified), this is
particularly disastrous.  

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Wednesday, October 14, 2009 2:57 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Corrupted database


On Oct 14, 2009, at 2:51 PM, Reusche, Andrew wrote:

> We get a "2" returned when we check synchronous.  I think that's the
> enum for "full".  We do not explicitely kill any threads, but when we
> decide to reboot or shutdown, we call "ExitProcess(0)" without  
> stopping
> any DB threads, and I'm sure this isn't very healthy.


I don't think that should cause problems.  But on the other hand, I'm  
a unix programmer and I sometimes find the behavior of windows to be  
baffling.  On windows, if you do a write() followed immediately by  
ExitProcess(), is it the case that the write might not actually occur?

D. Richard Hipp
d...@hwaci.com



___
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] Corrupted database

[Teg]

Hello Chris,

Customer PC's right? I've never had a corrupt DB3 here and that
includes me jumping out of the debugger mid-transaction but, I do have
customers who get corrupted DB's even with sync set to full. For some
customers, deleting the DB3's, running once to let them get created and
then restarted is enough to damage them.

I've come to the conclusion that in many cases, the PC's themselves
are the culprit. In many cases, my idiot users (not all are idiots)
will have multiple security packages installed, each with kernel
drivers that watch disk IO and interfere with it under the hood. These
same users usually have a litany of weird problems while the majority
of user have none.

I typically ask these "problem children" to remove their security
packages and reboot. Then test with no security packages installed.
Many times this solves the issue. Sometimes simply upgrading to the
most current version is enough to solve the problem.

I've come to the conclusion that AV packages and software firewalls
are more of a problem than the viruses they're trying to catch. PC
hardware really isn't that reliable either. I know this doesn't help
you but, you might want to consider the PC's themselves as you
investigate this.  I have >10,000 active users. The number of
users reporting these issues is perhaps 50-100.

C

Wednesday, October 14, 2009, 1:48:36 PM, you wrote:

MC> Yes, I agree.  What I am now trying to find out is if things like
MC> running a service or .NET service causes terminatethread to be called
MC> behind the scenes as some kind of cleanup.  The testing was to show that
MC> this can be a problem, to characterize why some dbs can get corrupted on
MC> "normal exits"



MC> -Original Message-
MC> From: sqlite-users-boun...@sqlite.org
MC> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Teg
MC> Sent: Wednesday, October 14, 2009 12:10 PM
MC> To: General Discussion of SQLite Database
MC> Subject: Re: [sqlite] Corrupted database

MC> Hello Chris,

MC> It's always a mistake to use TerminateThread. Even Microsoft warns
MC> against it.

>>From MSDN:

MC> - If the target thread owns a critical section, the critical section
MC> will not be released.
MC> - If the target thread is allocating memory from the heap, the heap lock
MC> will not be released.
MC> - If the target thread is executing certain kernel32 calls when it is
MC> terminated, the kernel32 state for the thread's process could be
MC> inconsistent.
MC> - If the target thread is manipulating the global state of a shared DLL,
MC> the state of the DLL could be destroyed, affecting other users of the
MC> DLL.

MC> Terminating a thread can hang the whole application of I read #1 and #2
MC> correctly. If you use TerminateThread regularly, you should really
MC> re-consider your design.

MC> C


MC> Wednesday, October 14, 2009, 11:29:29 AM, you wrote:

MC>> Yes, if we are in the middle of a lot of updates/inserts and just
MC>> terminate the thread, pragma integrity_check from the sqlite3
MC> command
MC>> line tool will report corruption at times.  Normally, when we hard
MC> kill
MC>> a thread in the middle of these ops, a journal is left behind.  I
MC> think
MC>> we only see corruption in this case (journal left behind), but
MC> cannot be
MC>> sure.  Our transactions can be large (a few thousand records
MC> totaling a
MC>> few megabytes).  

MC>> Summary of steps:

MC>> 1) hard-Kill a thread in the middle of inserting/updating a large
MC>> transaction (2mb+ transaction).  
MC>> 2) with nothing else running, do a pragma integrity_check in sqlite3
MC>> command line client against the db.  Obviously after executing
MC> sqlite3
MC>> client, the journal disappears since I guess recovery ran.
MC>> 3) integrity_check spits out lots of errors.

MC>> Note that we have never had this happen with Synchronous=Full, only
MC>> Synchronous=Off.  Have not tried normal.  That's all I was trying to
MC>> tell the original person asking.  I don't know why this would
MC> happen; it
MC>> seems logical that once you execute a write to the FS, whether or
MC> not
MC>> the app crashes/spontaneously exits that the write would make it.
MC>> However, all of this may be a red herring.. It turns out we moved to
MC>> Synch=Full after we sped up our db operations greatly... see below
MC> as to
MC>> why that may be the factor (speed), and not what Synch is set to.

MC>> By the way, this is all under windows.

MC>> I'm assuming that sqlite3 uses sync i/o via writefile.  Here's a
MC> quick
MC>> read on sync IO for windows:
MC>> http://msdn.microsoft.com/en-us/library/aa365683(VS.85).aspx --
MC>> specifically what worries me is that TerminateThread() looks like it
MC> can
MC>> interrupt an I/O operation.

MC>> The problem I think may be people using TerminateThread();  that's
MC> how
MC>> you hard kill a thread.  It seems that can interrupt an I/O
MC> operation --
MC>> ie an operation that writes more than one cluster at a time.
MC> Meaning,
MC>> synch = full may have nothing to d

Re: [sqlite] Corrupted database

[D. Richard Hipp]

On Oct 14, 2009, at 2:51 PM, Reusche, Andrew wrote:

> We get a "2" returned when we check synchronous.  I think that's the
> enum for "full".  We do not explicitely kill any threads, but when we
> decide to reboot or shutdown, we call "ExitProcess(0)" without  
> stopping
> any DB threads, and I'm sure this isn't very healthy.


I don't think that should cause problems.  But on the other hand, I'm  
a unix programmer and I sometimes find the behavior of windows to be  
baffling.  On windows, if you do a write() followed immediately by  
ExitProcess(), is it the case that the write might not actually occur?

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Corrupted database

[Reusche, Andrew]

We get a "2" returned when we check synchronous.  I think that's the
enum for "full".  We do not explicitely kill any threads, but when we
decide to reboot or shutdown, we call "ExitProcess(0)" without stopping
any DB threads, and I'm sure this isn't very healthy.

Andrew

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cannot ATTACH a database that contains a VIEW

[Pavel Ivanov]

> Is there any workaround for this? Maybe there is a trick to define the
> view in a different way in order to make clear that it relates to the
> database it's located in, and not to any database whichever is "main"
> currently?

Are you referencing "main" in your view explicitly? If so then don't
do it. If not then show us your ask_art view please.

Pavel

On Wed, Oct 14, 2009 at 2:30 PM, Wolfgang Enzinger  wrote:
> Hi,
>
> it looks like I cannot ATTACH a database that contains a VIEW:
>
> sqlite> ATTACH DATABASE 'G:\Project\ASK_ORA\ask.db' AS dbsrc;
> SQL error: malformed database schema (ask_art) - view ask_art cannot
> reference objects in database main
> sqlite>
>
> Obviously SQLite tries to apply the view to tables in the "current"
> database, not in the attached database.
>
> Is there any workaround for this? Maybe there is a trick to define the
> view in a different way in order to make clear that it relates to the
> database it's located in, and not to any database whichever is "main"
> currently? Or maybe a way to make SQLite simply "skip" (ignore) any
> view in the to-be-attached database?
>
> Any pointers much appreciated!
>
> Wolfgang
>
> ___
> 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] readers and writer

[Pavel Ivanov]

> If there is a timeout and it is set to 0 by default then that it is not
> very useful.

It's indeed so and it's useful in some cases. And you know, everything
can be tested pretty easily. Just make some test database and execute
in one terminal:

sqlite> create table t (a);
sqlite> begin;
sqlite> select * from t;

Then in other terminal:

sqlite> .timeout 1
sqlite> insert into t values (1);
SQL error: database is locked

And you'll see that before 'database is locked' appears shell will
wait for 10 seconds. If while it waits you make commit in the first
terminal then insert will succeed.

And about your earlier discussion about locks: when connection has
RESERVED or PENDING lock and it tries to propagate it to EXCLUSIVE it
waits for busy_timeout and if still unsuccessful then it returns
SQLITE_BUSY to the caller (or message 'database is locked' in the
shell). If somebody locked database with PENDING or EXCLUSIVE lock and
you're trying to get SHARED lock then SQLite will again wait for
busy_timeout and if SHARED lock cannot be acquired yet then you will
get SQLITE_BUSY or 'database is locked' message.

Pavel

On Wed, Oct 14, 2009 at 2:23 PM, priimak  wrote:
> Simon Slavin wrote:
>> On 14 Oct 2009, at 5:39am, Dmitri Priimak wrote:
>>
>>
>>> Yes, I understood that, but the impression I got is that SELECT will
>>> place shared lock on the database. While INSERT or UPDATE will first
>>> place PENDING lock indicating that it wants to write.
>>>
>>
>> Okay, I see what you mean.  I don't know how long the write process
>> will wait for the shared lock to be released before returning an error
>> message.  If it ever gives up, that is.  If it never gives up, a note
>> from the developers on what
>>
>>
 database is locked

>>
>> means would be useful, since if it never gives up there's never any
>> reason to generate that error message.  I assume there's a timeout
>> setting somewhere you can change but I see no PRAGMAs about timeout.
>>
> If there is a timeout and it is set to 0 by default then that it is not
> very useful.
> Moreover this http://www.sqlite.org/faq.html#q5 says
>
>    "Multiple processes can have the same database open at the same
> time. Multiple processes can be doing a SELECT at the same time. But
> only one process can be making changes to the database at any moment in
> time, however."
>
> Which does not seem to be true.
>
>> Googling on 'sqlite database is locked' suggests that other people
>> have discussed this problem.
> Well. One common "solution" is to copy database file apply changes
> and then copy it back or to apply changes only to 1.db then copy it
> to 2.db which would only be used for reading. This however is not
> much of a solution and I have been actually doing just that, but the
> file is getting bigger and bigger and copying it over is not an option
> anymore since it takes too much time and IO, which is not good for
> other processes running on that machine. And it also have effect on
> latency between application of changes to the database and making
> those changes available for querying.
>
> --
> Dmitri Priimak
> ___
> 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] Cannot ATTACH a database that contains a VIEW

[Wolfgang Enzinger]

Hi,

it looks like I cannot ATTACH a database that contains a VIEW:

sqlite> ATTACH DATABASE 'G:\Project\ASK_ORA\ask.db' AS dbsrc;
SQL error: malformed database schema (ask_art) - view ask_art cannot
reference objects in database main
sqlite>

Obviously SQLite tries to apply the view to tables in the "current"
database, not in the attached database.

Is there any workaround for this? Maybe there is a trick to define the
view in a different way in order to make clear that it relates to the
database it's located in, and not to any database whichever is "main"
currently? Or maybe a way to make SQLite simply "skip" (ignore) any
view in the to-be-attached database?

Any pointers much appreciated!

Wolfgang

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

Re: [sqlite] readers and writer

[priimak]

Simon Slavin wrote:
> On 14 Oct 2009, at 5:39am, Dmitri Priimak wrote:
>
>   
>> Yes, I understood that, but the impression I got is that SELECT will
>> place shared lock on the database. While INSERT or UPDATE will first
>> place PENDING lock indicating that it wants to write.
>> 
>
> Okay, I see what you mean.  I don't know how long the write process  
> will wait for the shared lock to be released before returning an error  
> message.  If it ever gives up, that is.  If it never gives up, a note  
> from the developers on what
>
>   
>>> database is locked
>>>   
>
> means would be useful, since if it never gives up there's never any  
> reason to generate that error message.  I assume there's a timeout  
> setting somewhere you can change but I see no PRAGMAs about timeout.
>   
If there is a timeout and it is set to 0 by default then that it is not 
very useful.
Moreover this http://www.sqlite.org/faq.html#q5 says

"Multiple processes can have the same database open at the same 
time. Multiple processes can be doing a SELECT at the same time. But 
only one process can be making changes to the database at any moment in 
time, however."

Which does not seem to be true.

> Googling on 'sqlite database is locked' suggests that other people  
> have discussed this problem.
Well. One common "solution" is to copy database file apply changes
and then copy it back or to apply changes only to 1.db then copy it
to 2.db which would only be used for reading. This however is not
much of a solution and I have been actually doing just that, but the
file is getting bigger and bigger and copying it over is not an option
anymore since it takes too much time and IO, which is not good for
other processes running on that machine. And it also have effect on
latency between application of changes to the database and making
those changes available for querying.

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

Re: [sqlite] Is it possible to combine a collation and a search

[Jean-Christophe Deschamps]

Andy,

>Hwever, what I want to do is seach for all of these varients too,  i.e. so
>that if I search for e that I get all of the e and accented e' etc, is 
>this
>possble using something like the collation, or do I need to specify all of
>them individually?

I'm about to release the beta of an SQLite extension providing Unicode 
locale-independant functions for collation, casing functions and a 
fuzzy comparison.

Since it's locale-independant, it's far from perfect and won't fit 
every need, but should be usable in a number of situations.

The collation has a major drawback: it relies of a Windoze function 
entirely (which should be portable and stable in the Win world).

The fuzzy search and string casing functions rely on on-board Unicode 
tries.
The whole thing is around 100k.  Speed is just like functionality: 
somewhere between bare-bone pure ASCII native SQLite support and full 
blown ICU.

Last problem: the registration of functions that override native SQLite 
functions is still problematic in some environments.  This is what 
blocks me from decent testing.

Let me know if your interessed.  Again, this is not yet even beta...




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

Re: [sqlite] readers and writer

[Simon Slavin]

On 14 Oct 2009, at 5:39am, Dmitri Priimak wrote:

> Yes, I understood that, but the impression I got is that SELECT will
> place shared lock on the database. While INSERT or UPDATE will first
> place PENDING lock indicating that it wants to write.

Okay, I see what you mean.  I don't know how long the write process  
will wait for the shared lock to be released before returning an error  
message.  If it ever gives up, that is.  If it never gives up, a note  
from the developers on what

>> database is locked

means would be useful, since if it never gives up there's never any  
reason to generate that error message.  I assume there's a timeout  
setting somewhere you can change but I see no PRAGMAs about timeout.

Googling on 'sqlite database is locked' suggests that other people  
have discussed this problem.

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

Re: [sqlite] Corrupted database

[McClellen, Chris]

Yes, I agree.  What I am now trying to find out is if things like
running a service or .NET service causes terminatethread to be called
behind the scenes as some kind of cleanup.  The testing was to show that
this can be a problem, to characterize why some dbs can get corrupted on
"normal exits"



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Teg
Sent: Wednesday, October 14, 2009 12:10 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Corrupted database

Hello Chris,

It's always a mistake to use TerminateThread. Even Microsoft warns
against it.

>From MSDN:

- If the target thread owns a critical section, the critical section
will not be released.
- If the target thread is allocating memory from the heap, the heap lock
will not be released.
- If the target thread is executing certain kernel32 calls when it is
terminated, the kernel32 state for the thread's process could be
inconsistent.
- If the target thread is manipulating the global state of a shared DLL,
the state of the DLL could be destroyed, affecting other users of the
DLL.

Terminating a thread can hang the whole application of I read #1 and #2
correctly. If you use TerminateThread regularly, you should really
re-consider your design.

C


Wednesday, October 14, 2009, 11:29:29 AM, you wrote:

MC> Yes, if we are in the middle of a lot of updates/inserts and just
MC> terminate the thread, pragma integrity_check from the sqlite3
command
MC> line tool will report corruption at times.  Normally, when we hard
kill
MC> a thread in the middle of these ops, a journal is left behind.  I
think
MC> we only see corruption in this case (journal left behind), but
cannot be
MC> sure.  Our transactions can be large (a few thousand records
totaling a
MC> few megabytes).  

MC> Summary of steps:

MC> 1) hard-Kill a thread in the middle of inserting/updating a large
MC> transaction (2mb+ transaction).  
MC> 2) with nothing else running, do a pragma integrity_check in sqlite3
MC> command line client against the db.  Obviously after executing
sqlite3
MC> client, the journal disappears since I guess recovery ran.
MC> 3) integrity_check spits out lots of errors.

MC> Note that we have never had this happen with Synchronous=Full, only
MC> Synchronous=Off.  Have not tried normal.  That's all I was trying to
MC> tell the original person asking.  I don't know why this would
happen; it
MC> seems logical that once you execute a write to the FS, whether or
not
MC> the app crashes/spontaneously exits that the write would make it.
MC> However, all of this may be a red herring.. It turns out we moved to
MC> Synch=Full after we sped up our db operations greatly... see below
as to
MC> why that may be the factor (speed), and not what Synch is set to.

MC> By the way, this is all under windows.

MC> I'm assuming that sqlite3 uses sync i/o via writefile.  Here's a
quick
MC> read on sync IO for windows:
MC> http://msdn.microsoft.com/en-us/library/aa365683(VS.85).aspx --
MC> specifically what worries me is that TerminateThread() looks like it
can
MC> interrupt an I/O operation.

MC> The problem I think may be people using TerminateThread();  that's
how
MC> you hard kill a thread.  It seems that can interrupt an I/O
operation --
MC> ie an operation that writes more than one cluster at a time.
Meaning,
MC> synch = full may have nothing to do with it.  If you have to say
write
MC> more than one cluster (disk block), TerminateThread looks like it
can
MC> abort the IO op in the middle of a multi-block op?  I'm trying to
run
MC> that down but can't yet find anything that verifies this. 

MC> So, here is what I think:
MC> 1) You need to write some data, lets say a 8K write.  In this
MC> theoretical example, disk blocks are 4k.  
MC> 2) You call WriteFile(8K).
MC> 3) WriteFile causes a system call, and the system schedules the
first
MC> bit of the i/o (1st 4k).
MC> 4) terminatethread() is called
MC> 5) I/O operation is cancelled (ie, as if CancelIO had been called?),
MC> meaning block #2 was never scheduled.  Database now corrupt.

MC> A lot of people using windows tend to have this kind of threading
MC> architecture:
MC> 1) Signal the thread to exit on its own
MC> 2) Wait for some grace period.
MC> 3) if grace period expires, and thread is still running --
MC> TerminateThread() because once the grace period expires, the thread
is
MC> considered hung.

MC> So, large transactions in a thread could cause people to use
MC> TerminateThread() at a critical time, especially if that causes the
MC> thread to go over its grace period.  For us, these large
transactions
MC> took longer than our grace period to complete, and thus were subject
to
MC> TerminateThread -- and lots of corruption.  Once we sped everything
up
MC> and moved to synch=full, no transaction was even close to the grace
MC> period, and such, terminatethread() is never called.. and we get no
MC> corruption.

MC> Just a thought.

MC> -Original M

Re: [sqlite] readers and writer

[priimak]

Edzard Pasma wrote:
> --- prii...@stanford.edu wrote:
>   
>> Simon Slavin wrote:
>> 
>>> On 14 Oct 2009, at 1:21am, priimak wrote:
>>>
>>>   
>>>   
 I am heaving small problem with sqlite. I have a webapp which connects
 to the database using sqlite-jdbc and performs SELECTs to response to
 different GET requests, while this happens if I try to write to a
 database ( UPDATE or INSERT ) from command line, that (i.e. update
 process) would occasionally fail with error message "SQL error near  
 line
 1: database is locked". Notice that I have only one writer, but many
 readers. Reading documentation (http://www.sqlite.org/ 
 lockingv3.html) I
 was under impression that process which intends to update database  
 will
 place it in the pending state allowing all currently running reads (
 SELECTs ) to proceed, while blocking new SELECTs, the lock database
 apply changes and then unlock it allowing all pending and new  
 SELECTs to
 proceed. Am I right about it and if so why do I "SQL error near line  
 1:
 database is locked" when trying to write to a database?
 
 
>>> I hope this will do until an expert comes along.  I think you got it  
>>> right, you just don't know something.
>>>
>>> The SELECT activity requires a lock to the database.  For instance,  
>>> consider a TABLE contact with columns name, address, phone .  An index  
>>> is declared on just the name column.  You execute
>>>
>>> SELECT phone FROM contacts WHERE name = 'Jackie'
>>>
>>> This requires a two-stage process: first use the index to find the ids  
>>> of the rows which have the right name.  Then look up those rows in the  
>>> table and find out which phone numbers they have.  Obviously, this  
>>> requires locking: you wouldn't want someone to make changes to the  
>>> table between those two steps.  However, it requires locking only  
>>> against writing: other reads going on at the same time are harmless,  
>>> but a change between the two steps can invalidate the data.
>>>
>>> So if a SELECT is in progress, other SELECT commands can be allowed to  
>>> proceed without problems. But no INSERT or UPDATE can be allowed until  
>>> the SELECT is finished.  Hence you will sometimes get a lock on the  
>>> write.
>>>
>>> How you deal with this, I don't know.  Random wait-and-try-again ?
>>>   
>> Yes, I understood that, but the impression I got is that SELECT will
>> place shared lock on the database. While INSERT or UPDATE will first
>> place PENDING lock indicating that it wants to write. While it is in a
>> PENDING lock state all operations that placed SHARED lock ( such as
>> SELECTs ) will allow to complete and new SHARED locks either denied
>> or blocked ( this part of documentation is not clear as to which one of
>> these two actions are taken  ). Then when all SHARED locks are
>> removed due to completion of SELECTs, database moves from PENDING
>> into EXCLUSIVE lock, which is cleared when update/write completed and then
>> new/pending SHARED locks are allowed to proceed. This should mean
>> that with many processes reading and only one writing there is no need to
>> use sqlite3_busy_timeout() function, which is to be used when we have
>> many processes trying to write to the database and/or reader if new
>> SHARED locks are denied while database is in a PENDING and/or
>> EXCLUSIVE lock state ( again, this point it not clear in documentation ).
>> Do I understand it correctly?
>>
>> --
>> Dmitri Priimak
>> 
>
> Hello Dmitri, I understaod it the same way. However recently I observed that 
> a PENDING lock does not perform its useful function (prevent writer 
> starvation) in case readers and writers are threads of a single process! May 
> that be the case? Best regards, Edzard Pasma. 
No. That is not my case. I have many readers which reside in the same 
process, but in different threads and one writer, which is completely 
separate process. Here is an example.

$ echo "create table Z ( t int );" | sqlite3 a.db
$ echo "insert into Z ( t ) values ( 1 );" | sqlite3 a.db
$ for i in {1..1}; do; echo "select * from Z;" | sqlite3 a.db > 
/dev/null; done

In another terminal run following several times.
$ echo " update Z set t = 2;" | sqlite3 a.db
At some point this will produce

$ echo "update Z set t = 2;" | sqlite3 a.db
SQL error near line 1: database is locked

Am I misunderstanding something here or this error should have only 
happened
in the terminal where we are doing select?

--
Dmitri Priimak


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

[sqlite] Is it possible to combine a collation and a search

[Andy Thompson]

Is it possible to use a collate type of function when searching my
database.   I can order it correctly using a collate so that all of my names
starting with e are along side those starting with accented e etc.

i.e  select * from  order by  collate  ; would
result in



 

without the collate I obviously get:





Hwever, what I want to do is seach for all of these varients too,  i.e. so
that if I search for e that I get all of the e and accented e' etc, is this
possble using something like the collation, or do I need to specify all of
them individually?

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

Re: [sqlite] how to represent a tree in SQL

[Cariotoglou Mike]

just to throw in my two bits:

I have done a lot of work with trees in SQL, and IMHO, the best method BY FAR 
is the one described in the link below (mysql article), mainly due to its 
capability to handle siblings and descendants.
for example, the self-join, parent_node method described elsewhere in this list 
is failr ok for simple requirements, but is completely useles in the following 
cases:

select all_descendans_on_any depth for a particular node

find out if a node "belongs" to a parent which is not its immediate parent.

in other words, SET operations are quite difficult in the node-parent relation, 
but very easy and efficient in the adjacent list model.

I personally stopped looking for a better solution once I came across and 
comprehended the power of this method...

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of 
> Sebastian Bermudez
> Sent: Wednesday, October 14, 2009 5:10 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] how to represent a tree in SQL
> 
> 
> 
> look this:
> 
> http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
> 
> is for mysql but aplies to every sql DB
> 
> 
> - Mensaje original 
> De: Michael Chen 
> Para: sqlite-users@sqlite.org
> Enviado: mié, octubre 14, 2009 10:40:45 AM
> Asunto: [sqlite] how to represent a tree in SQL
> 
> Dear there,
> 
> I am developing a numerical application, where a single 
> rooted dynamic tree is the main data structure. I intended to 
> use SQLite for this purpose and also for other data as well. 
> However I have no reference how to represent a tree using 
> tables. I need these functionalities:
> (1) basic tree: single root, multi-levels, arbitrary number 
> of branches, index each node, index each path (from root to a 
> leaf), lookup parent, lookup descendants
> (2) dynamics: delete a path, add a path; maintain parent and 
> descendants table; maintain history of tree; lookup history
> (3) each node has lots of  matrix and vectors, which will be 
> updated with dynamics, and should be tracked
> 
> As you see, it is nontrivial to write a tree structure to 
> support all these functions, while keep the code clean and 
> neat. That's why I want to use SQLite to keep things 
> straight. Is there a good reference on this?
> 
> Michael Chen
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
>   Yahoo! Cocina
> 
> Encontra las mejores recetas con Yahoo! Cocina.
> 
> 
> http://ar.mujer.yahoo.com/cocina/
> ___
> 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] Corrupted database

[Teg]

Hello Chris,

It's always a mistake to use TerminateThread. Even Microsoft warns
against it.

>From MSDN:

- If the target thread owns a critical section, the critical section will not 
be released.
- If the target thread is allocating memory from the heap, the heap lock will 
not be released.
- If the target thread is executing certain kernel32 calls when it is
terminated, the kernel32 state for the thread's process could be
inconsistent.
- If the target thread is manipulating the global state of a shared DLL,
the state of the DLL could be destroyed, affecting other users of the
DLL.

Terminating a thread can hang the whole application of I read #1 and #2
correctly. If you use TerminateThread regularly, you should really
re-consider your design.

C


Wednesday, October 14, 2009, 11:29:29 AM, you wrote:

MC> Yes, if we are in the middle of a lot of updates/inserts and just
MC> terminate the thread, pragma integrity_check from the sqlite3 command
MC> line tool will report corruption at times.  Normally, when we hard kill
MC> a thread in the middle of these ops, a journal is left behind.  I think
MC> we only see corruption in this case (journal left behind), but cannot be
MC> sure.  Our transactions can be large (a few thousand records totaling a
MC> few megabytes).  

MC> Summary of steps:

MC> 1) hard-Kill a thread in the middle of inserting/updating a large
MC> transaction (2mb+ transaction).  
MC> 2) with nothing else running, do a pragma integrity_check in sqlite3
MC> command line client against the db.  Obviously after executing sqlite3
MC> client, the journal disappears since I guess recovery ran.
MC> 3) integrity_check spits out lots of errors.

MC> Note that we have never had this happen with Synchronous=Full, only
MC> Synchronous=Off.  Have not tried normal.  That's all I was trying to
MC> tell the original person asking.  I don't know why this would happen; it
MC> seems logical that once you execute a write to the FS, whether or not
MC> the app crashes/spontaneously exits that the write would make it.
MC> However, all of this may be a red herring.. It turns out we moved to
MC> Synch=Full after we sped up our db operations greatly... see below as to
MC> why that may be the factor (speed), and not what Synch is set to.

MC> By the way, this is all under windows.

MC> I'm assuming that sqlite3 uses sync i/o via writefile.  Here's a quick
MC> read on sync IO for windows:
MC> http://msdn.microsoft.com/en-us/library/aa365683(VS.85).aspx --
MC> specifically what worries me is that TerminateThread() looks like it can
MC> interrupt an I/O operation.

MC> The problem I think may be people using TerminateThread();  that's how
MC> you hard kill a thread.  It seems that can interrupt an I/O operation --
MC> ie an operation that writes more than one cluster at a time.  Meaning,
MC> synch = full may have nothing to do with it.  If you have to say write
MC> more than one cluster (disk block), TerminateThread looks like it can
MC> abort the IO op in the middle of a multi-block op?  I'm trying to run
MC> that down but can't yet find anything that verifies this. 

MC> So, here is what I think:
MC> 1) You need to write some data, lets say a 8K write.  In this
MC> theoretical example, disk blocks are 4k.  
MC> 2) You call WriteFile(8K).
MC> 3) WriteFile causes a system call, and the system schedules the first
MC> bit of the i/o (1st 4k).
MC> 4) terminatethread() is called
MC> 5) I/O operation is cancelled (ie, as if CancelIO had been called?),
MC> meaning block #2 was never scheduled.  Database now corrupt.

MC> A lot of people using windows tend to have this kind of threading
MC> architecture:
MC> 1) Signal the thread to exit on its own
MC> 2) Wait for some grace period.
MC> 3) if grace period expires, and thread is still running --
MC> TerminateThread() because once the grace period expires, the thread is
MC> considered hung.

MC> So, large transactions in a thread could cause people to use
MC> TerminateThread() at a critical time, especially if that causes the
MC> thread to go over its grace period.  For us, these large transactions
MC> took longer than our grace period to complete, and thus were subject to
MC> TerminateThread -- and lots of corruption.  Once we sped everything up
MC> and moved to synch=full, no transaction was even close to the grace
MC> period, and such, terminatethread() is never called.. and we get no
MC> corruption.

MC> Just a thought.

MC> -Original Message-
MC> From: sqlite-users-boun...@sqlite.org
MC> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
MC> Sent: Wednesday, October 14, 2009 12:36 AM
MC> To: General Discussion of SQLite Database
MC> Subject: Re: [sqlite] Corrupted database


MC> On Oct 14, 2009, at 12:42 AM, McClellen, Chris wrote:

>> But it does happen and we can reproduce it.  Hard killing a thread is
>> essentially equivalent to turning off the power.

MC> We have always assumed that it is different. When you write data to
MC> a file, the write is b

Re: [sqlite] Corrupted database

[Dan Kennedy]

> The problem I think may be people using TerminateThread();  that's how
> you hard kill a thread.  It seems that can interrupt an I/O  
> operation --
> ie an operation that writes more than one cluster at a time.  Meaning,
> synch = full may have nothing to do with it.  If you have to say write
> more than one cluster (disk block), TerminateThread looks like it can
> abort the IO op in the middle of a multi-block op?  I'm trying to run
> that down but can't yet find anything that verifies this.

Even if it does, which seems quite plausible, the only way
I can see this causing corruption is if you are in persistent
journal mode and a (weak) checksum gives you a false positive
on the last, corrupted, record in the journal file.

Maybe if you're using a version from a few years back it could
happen without persistent-journal mode too.

It's easy to be wrong about this kind of thing though.

Dan.



> So, here is what I think:
> 1) You need to write some data, lets say a 8K write.  In this
> theoretical example, disk blocks are 4k.
> 2) You call WriteFile(8K).
> 3) WriteFile causes a system call, and the system schedules the first
> bit of the i/o (1st 4k).
> 4) terminatethread() is called
> 5) I/O operation is cancelled (ie, as if CancelIO had been called?),
> meaning block #2 was never scheduled.  Database now corrupt.
>
> A lot of people using windows tend to have this kind of threading
> architecture:
> 1) Signal the thread to exit on its own
> 2) Wait for some grace period.
> 3) if grace period expires, and thread is still running --
> TerminateThread() because once the grace period expires, the thread is
> considered hung.
>
> So, large transactions in a thread could cause people to use
> TerminateThread() at a critical time, especially if that causes the
> thread to go over its grace period.  For us, these large transactions
> took longer than our grace period to complete, and thus were subject  
> to
> TerminateThread -- and lots of corruption.  Once we sped everything up
> and moved to synch=full, no transaction was even close to the grace
> period, and such, terminatethread() is never called.. and we get no
> corruption.
>
> Just a thought.
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
> Sent: Wednesday, October 14, 2009 12:36 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Corrupted database
>
>
> On Oct 14, 2009, at 12:42 AM, McClellen, Chris wrote:
>
>> But it does happen and we can reproduce it.  Hard killing a thread is
>> essentially equivalent to turning off the power.
>
> We have always assumed that it is different. When you write data to
> a file, the write is buffered in volatile memory by the OS for a time.
> If a power failure occurs during this time, the write is lost. But if
> a thread is killed, the OS should still eventually make sure the data
> gets to stable storage.
>
> If you kill the application, then open the database using the shell
> tool, is the database corrupted?
>
> Dan.
>
>
>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
>> Sent: Tuesday, October 13, 2009 12:35 AM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] Corrupted database
>>
>>
>> On Oct 13, 2009, at 1:56 AM, McClellen, Chris wrote:
>>
>>> What is your synchronous set to?  Full?  FYI If you are using .NET
>>> data
>>> providers, it is set to "Normal" by default.
>>>
>>> If it is not set to full, I have seen corruption when an application
>>> crashes, or exits when a thread is in the middle of updating the db
>>> (Synchronous = OFF makes corruption even easier in this case).  I
>>> have
>>> seen apps that do not wait for background threads to finish before
>>> termination, and without full sync on, either the db or the log gets
>>> corrupted.  A corrupted log can cause problems for you db on next  
>>> run
>>> when recovery happens.
>>
>> In theory, this shouldn't happen. Unless the application is actually
>> buffering data that SQLite thinks has been written to the database or
>> journal file in the process space on some systems.
>>
>> The "synchronous" setting should only make a difference in the event
>> of a power or OS failure. That's the theory, anyway.
>>
>> Dan.
>>
>>
>>
>>>
>>>
>>>
>>> -Original Message-
>>> From: sqlite-users-boun...@sqlite.org
>>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Filip Navara
>>> Sent: Monday, October 12, 2009 12:38 PM
>>> To: General Discussion of SQLite Database
>>> Subject: [sqlite] Corrupted database
>>>
>>> Hello,
>>>
>>> for a few months we have been occasionally getting corrupted
>>> databases
>>> in the field. So far we were unable to acquire any of them from our
>>> customers, but this week I finally got hold of one. Output from
>>> "pragma integrity_check" is included below.
>>>
>>> The schema is the following:
>>>
>>>

Re: [sqlite] Corrupted database

[McClellen, Chris]

Do you ever teriminate threads via TerminateThread() or run as a service (where 
the scm will call terminateThread() once your main service thread exits)?  In 
other words do you hard-kill your threads?


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Filip Navara
Sent: Wednesday, October 14, 2009 7:15 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Corrupted database

On Mon, Oct 12, 2009 at 8:56 PM, McClellen, Chris
 wrote:
> What is your synchronous set to?  Full?  FYI If you are using .NET data
> providers, it is set to "Normal" by default.

Normal or Off, but no power failure was involved. (Yes,
System.Data.SQLite is used)

> If it is not set to full, I have seen corruption when an application
> crashes, or exits when a thread is in the middle of updating the db
> (Synchronous = OFF makes corruption even easier in this case).  I have
> seen apps that do not wait for background threads to finish before
> termination, and without full sync on, either the db or the log gets
> corrupted.  A corrupted log can cause problems for you db on next run
> when recovery happens.

Sounds suspiciously like our case, but still Synchronous=off is
supposed to work in the event of application crash, hard killed
threads and so on. Previous version of the application frequently
forgot to close the databases on exit and did other nasty things that
and now fixed, but none of them should cause the database to be
corrupted.

Best regards,
Filip Navara
___
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] Corrupted database

[McClellen, Chris]

Yes, if we are in the middle of a lot of updates/inserts and just
terminate the thread, pragma integrity_check from the sqlite3 command
line tool will report corruption at times.  Normally, when we hard kill
a thread in the middle of these ops, a journal is left behind.  I think
we only see corruption in this case (journal left behind), but cannot be
sure.  Our transactions can be large (a few thousand records totaling a
few megabytes).  

Summary of steps:

1) hard-Kill a thread in the middle of inserting/updating a large
transaction (2mb+ transaction).  
2) with nothing else running, do a pragma integrity_check in sqlite3
command line client against the db.  Obviously after executing sqlite3
client, the journal disappears since I guess recovery ran.
3) integrity_check spits out lots of errors.

Note that we have never had this happen with Synchronous=Full, only
Synchronous=Off.  Have not tried normal.  That's all I was trying to
tell the original person asking.  I don't know why this would happen; it
seems logical that once you execute a write to the FS, whether or not
the app crashes/spontaneously exits that the write would make it.
However, all of this may be a red herring.. It turns out we moved to
Synch=Full after we sped up our db operations greatly... see below as to
why that may be the factor (speed), and not what Synch is set to.

By the way, this is all under windows.

I'm assuming that sqlite3 uses sync i/o via writefile.  Here's a quick
read on sync IO for windows:
http://msdn.microsoft.com/en-us/library/aa365683(VS.85).aspx --
specifically what worries me is that TerminateThread() looks like it can
interrupt an I/O operation.

The problem I think may be people using TerminateThread();  that's how
you hard kill a thread.  It seems that can interrupt an I/O operation --
ie an operation that writes more than one cluster at a time.  Meaning,
synch = full may have nothing to do with it.  If you have to say write
more than one cluster (disk block), TerminateThread looks like it can
abort the IO op in the middle of a multi-block op?  I'm trying to run
that down but can't yet find anything that verifies this. 

So, here is what I think:
1) You need to write some data, lets say a 8K write.  In this
theoretical example, disk blocks are 4k.  
2) You call WriteFile(8K).
3) WriteFile causes a system call, and the system schedules the first
bit of the i/o (1st 4k).
4) terminatethread() is called
5) I/O operation is cancelled (ie, as if CancelIO had been called?),
meaning block #2 was never scheduled.  Database now corrupt.

A lot of people using windows tend to have this kind of threading
architecture:
1) Signal the thread to exit on its own
2) Wait for some grace period.
3) if grace period expires, and thread is still running --
TerminateThread() because once the grace period expires, the thread is
considered hung.

So, large transactions in a thread could cause people to use
TerminateThread() at a critical time, especially if that causes the
thread to go over its grace period.  For us, these large transactions
took longer than our grace period to complete, and thus were subject to
TerminateThread -- and lots of corruption.  Once we sped everything up
and moved to synch=full, no transaction was even close to the grace
period, and such, terminatethread() is never called.. and we get no
corruption.

Just a thought.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
Sent: Wednesday, October 14, 2009 12:36 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Corrupted database


On Oct 14, 2009, at 12:42 AM, McClellen, Chris wrote:

> But it does happen and we can reproduce it.  Hard killing a thread is
> essentially equivalent to turning off the power.

We have always assumed that it is different. When you write data to
a file, the write is buffered in volatile memory by the OS for a time.
If a power failure occurs during this time, the write is lost. But if
a thread is killed, the OS should still eventually make sure the data
gets to stable storage.

If you kill the application, then open the database using the shell
tool, is the database corrupted?

Dan.



> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
> Sent: Tuesday, October 13, 2009 12:35 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Corrupted database
>
>
> On Oct 13, 2009, at 1:56 AM, McClellen, Chris wrote:
>
>> What is your synchronous set to?  Full?  FYI If you are using .NET
>> data
>> providers, it is set to "Normal" by default.
>>
>> If it is not set to full, I have seen corruption when an application
>> crashes, or exits when a thread is in the middle of updating the db
>> (Synchronous = OFF makes corruption even easier in this case).  I  
>> have
>> seen apps that do not wait for background threads to finish before
>> termination, and w

Re: [sqlite] how to represent a tree in SQL

[Michael Chen]

Thanks all! It will take me a while to learn the materials in your posts.
One thing I care most is DYNAMICS of a tree. I am not sure it is covered in
the mentioned references before I study them. Thanks all again!


On Wed, Oct 14, 2009 at 9:18 AM, O'Neill, Owen wrote:

>
> Personally I'd put the nodes and vertices/paths/edge  between them in
> separate tables. (unless you're happy with the 1 vertex between nodes
> constraint)
>
> It does really get horrible if you're planning to use SQL to find
> relations with a distance apart of > 1 vertex.
>
> I have an oh so vague recollection (not specific to sqlite) of someone
> using a huge number of boolean columns and some incredible monster of a
> 'case' statement to push tree parsing off the client app and back onto
> the database server. The details completely escape me now and it
> probably introduced lots of constraints on tree depth or number of nodes
> / vertices.oh for a perfect memory eh.
>
> People a lot cleverer than me have done a pile of work on this.
> http://blog.monstuff.com/archives/26.html
> http://www.sqlteam.com/article/more-trees-hierarchies-in-sql
> ..ooops, I appear to have stayed off topic. Apologies.
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Hoover, Jeffrey
> Sent: Wednesday, October 14, 2009 2:51 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] how to represent a tree in SQL
>
> Isn't most of the complexity in the software?
>
> Representing a tree is fairly simple, it just requires a foreign key in
> the table referencing the primary key of the same table...
>
> Create table tree_node (
>node_id integer primary key,
>distance_from_root integer not null,-- 0 implies root node
>parent_node integer,-- FK to
> node_id, column should be index
>...data columns...
> );
>
> Create view tree as
>   Select * from tree_node where distance_from_root=0;
>
> All kinds of denormalizations and elaborations possible from here...
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Chen
> Sent: Wednesday, October 14, 2009 9:41 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] how to represent a tree in SQL
>
> Dear there,
>
> I am developing a numerical application, where a single rooted dynamic
> tree
> is the main data structure. I intended to use SQLite for this purpose
> and
> also for other data as well. However I have no reference how to
> represent a
> tree using tables. I need these functionalities:
> (1) basic tree: single root, multi-levels, arbitrary number of branches,
> index each node, index each path (from root to a leaf), lookup parent,
> lookup descendants
> (2) dynamics: delete a path, add a path; maintain parent and descendants
> table; maintain history of tree; lookup history
> (3) each node has lots of  matrix and vectors, which will be updated
> with
> dynamics, and should be tracked
>
> As you see, it is nontrivial to write a tree structure to support all
> these
> functions, while keep the code clean and neat. That's why I want to use
> SQLite to keep things straight. Is there a good reference on this?
>
> Michael Chen
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards,
Michael Chen
Google Voice Phone.: 847-448-0647
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to represent a tree in SQL

[Rich Shepard]

On Wed, 14 Oct 2009, Michael Chen wrote:

> I am developing a numerical application, where a single rooted dynamic
> tree is the main data structure. I intended to use SQLite for this purpose
> and also for other data as well. However I have no reference how to
> represent a tree using tables. I need these functionalities: (1) basic
> tree: single root, multi-levels, arbitrary number of branches, index each
> node, index each path (from root to a leaf), lookup parent, lookup
> descendants (2) dynamics: delete a path, add a path; maintain parent and
> descendants table; maintain history of tree; lookup history (3) each node
> has lots of matrix and vectors, which will be updated with dynamics, and
> should be tracked
>
> As you see, it is nontrivial to write a tree structure to support all
> these functions, while keep the code clean and neat. That's why I want to
> use SQLite to keep things straight. Is there a good reference on this?

Michael,

   I've not yet had a need to address trees and hierarchies in SQL, but I
will unconditionally recommend you run to borrow or buy a copy of Joe
Celko's "TREES & HIERARCHIES IN SQL", (Morgan-Kaufmann), 2004 ISBN
1-55860-920-2.

   I read his dbms columns in the 1980s and 1990s, read and use his "SQL
Programming Style" and "SQL for Smarties," and communicated with him for
advice on time-and-date based applications. If you still have questions
after reading this book, send him an e-mail message.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to represent a tree in SQL

[O'Neill, Owen]

Personally I'd put the nodes and vertices/paths/edge  between them in
separate tables. (unless you're happy with the 1 vertex between nodes
constraint)

It does really get horrible if you're planning to use SQL to find
relations with a distance apart of > 1 vertex.

I have an oh so vague recollection (not specific to sqlite) of someone
using a huge number of boolean columns and some incredible monster of a
'case' statement to push tree parsing off the client app and back onto
the database server. The details completely escape me now and it
probably introduced lots of constraints on tree depth or number of nodes
/ vertices.oh for a perfect memory eh.

People a lot cleverer than me have done a pile of work on this.
http://blog.monstuff.com/archives/26.html
http://www.sqlteam.com/article/more-trees-hierarchies-in-sql
..ooops, I appear to have stayed off topic. Apologies.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Hoover, Jeffrey
Sent: Wednesday, October 14, 2009 2:51 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] how to represent a tree in SQL

Isn't most of the complexity in the software?

Representing a tree is fairly simple, it just requires a foreign key in
the table referencing the primary key of the same table...

Create table tree_node (
node_id integer primary key,
distance_from_root integer not null,-- 0 implies root node
parent_node integer,-- FK to
node_id, column should be index
...data columns...
);

Create view tree as
   Select * from tree_node where distance_from_root=0;

All kinds of denormalizations and elaborations possible from here...

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Chen
Sent: Wednesday, October 14, 2009 9:41 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] how to represent a tree in SQL

Dear there,

I am developing a numerical application, where a single rooted dynamic
tree
is the main data structure. I intended to use SQLite for this purpose
and
also for other data as well. However I have no reference how to
represent a
tree using tables. I need these functionalities:
(1) basic tree: single root, multi-levels, arbitrary number of branches,
index each node, index each path (from root to a leaf), lookup parent,
lookup descendants
(2) dynamics: delete a path, add a path; maintain parent and descendants
table; maintain history of tree; lookup history
(3) each node has lots of  matrix and vectors, which will be updated
with
dynamics, and should be tracked

As you see, it is nontrivial to write a tree structure to support all
these
functions, while keep the code clean and neat. That's why I want to use
SQLite to keep things straight. Is there a good reference on this?

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

Re: [sqlite] how to represent a tree in SQL

[Sebastian Bermudez]

look this:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

is for mysql but aplies to every sql DB


- Mensaje original 
De: Michael Chen 
Para: sqlite-users@sqlite.org
Enviado: mié, octubre 14, 2009 10:40:45 AM
Asunto: [sqlite] how to represent a tree in SQL

Dear there,

I am developing a numerical application, where a single rooted dynamic tree
is the main data structure. I intended to use SQLite for this purpose and
also for other data as well. However I have no reference how to represent a
tree using tables. I need these functionalities:
(1) basic tree: single root, multi-levels, arbitrary number of branches,
index each node, index each path (from root to a leaf), lookup parent,
lookup descendants
(2) dynamics: delete a path, add a path; maintain parent and descendants
table; maintain history of tree; lookup history
(3) each node has lots of  matrix and vectors, which will be updated with
dynamics, and should be tracked

As you see, it is nontrivial to write a tree structure to support all these
functions, while keep the code clean and neat. That's why I want to use
SQLite to keep things straight. Is there a good reference on this?

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



  Yahoo! Cocina

Encontra las mejores recetas con Yahoo! Cocina.


http://ar.mujer.yahoo.com/cocina/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite version 3.6.19

[D. Richard Hipp]

SQLite version 3.6.19 is now available on the SQLite website

http://www.sqlite.org/

Version 3.6.19 adds support for enforcing foreign key constraints,  
including support for deferred constraints and cascading deletes and  
updates.  Foreign key constraint enforcement is turned off by default  
(for backwards compatibility) and must be enabled using a pragma:   
PRAGMA foreign_keys=ON;  Additional information at

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

Version 3.6.19 also generalizes the IS and IS NOT operators so that  
they will take an arbitrary expression as their right-hand side  
instead of just the literal NULL.  You can still say "x IS NULL" and  
it works as before.  But you can now also say "x IS y" and the result  
will be true if x=y or if both x and y are NULL.

This release has been extensively tested (we still have 100% branch  
test coverage).  We consider this release to be production ready.   
Nevertheless, testing can only prove the presence of bugs, not their  
absence.  So if you encounter problems, please let us know.

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] how to represent a tree in SQL

[Hoover, Jeffrey]

Isn't most of the complexity in the software?

Representing a tree is fairly simple, it just requires a foreign key in
the table referencing the primary key of the same table...

Create table tree_node (
node_id integer primary key,
distance_from_root integer not null,-- 0 implies root node
parent_node integer,-- FK to
node_id, column should be index
...data columns...
);

Create view tree as
   Select * from tree_node where distance_from_root=0;

All kinds of denormalizations and elaborations possible from here...

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Chen
Sent: Wednesday, October 14, 2009 9:41 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] how to represent a tree in SQL

Dear there,

I am developing a numerical application, where a single rooted dynamic
tree
is the main data structure. I intended to use SQLite for this purpose
and
also for other data as well. However I have no reference how to
represent a
tree using tables. I need these functionalities:
(1) basic tree: single root, multi-levels, arbitrary number of branches,
index each node, index each path (from root to a leaf), lookup parent,
lookup descendants
(2) dynamics: delete a path, add a path; maintain parent and descendants
table; maintain history of tree; lookup history
(3) each node has lots of  matrix and vectors, which will be updated
with
dynamics, and should be tracked

As you see, it is nontrivial to write a tree structure to support all
these
functions, while keep the code clean and neat. That's why I want to use
SQLite to keep things straight. Is there a good reference on this?

Michael Chen
___
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] how to represent a tree in SQL

[Michael Chen]

Dear there,

I am developing a numerical application, where a single rooted dynamic tree
is the main data structure. I intended to use SQLite for this purpose and
also for other data as well. However I have no reference how to represent a
tree using tables. I need these functionalities:
(1) basic tree: single root, multi-levels, arbitrary number of branches,
index each node, index each path (from root to a leaf), lookup parent,
lookup descendants
(2) dynamics: delete a path, add a path; maintain parent and descendants
table; maintain history of tree; lookup history
(3) each node has lots of  matrix and vectors, which will be updated with
dynamics, and should be tracked

As you see, it is nontrivial to write a tree structure to support all these
functions, while keep the code clean and neat. That's why I want to use
SQLite to keep things straight. Is there a good reference on this?

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

[sqlite] ARM build instructions (without threads)

[Mark Constable]

This may be useful for someone else as it took me many hours to
work out the bare essentials on how to compile sqlite on an ARM
device without threads (and no configure or tcl available).

---
#!/bin/sh
set -x

wget http://www.sqlite.org/sqlite-amalgamation-3_6_18.zip
wget "http://www.sqlite.org/src/raw/src/shell.c?name=29394"; -O shell.c

unzip sqlite-amalgamation-3_6_18.zip

#CFLAGS="-march=i686 -mtune=generic -O2 -pipe"
#CFLAGS="-march=x86-64 -mtune=generic -O2 -pipe -Wall -fPIC"
#CFLAGS="-march=armv4t -msoft-float -O2 -pipe -Wall"
CFLAGS="-march=armv5te -O2 -pipe -Wall"

gcc $CFLAGS \
 -DSQLITE_OS_UNIX=1 -DNDEBUG -DSQLITE_THREADSAFE=0 \
 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 \
 -DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_COLUMN_METADATA=1 \
 -DSQLITE_TEMP_STORE=1 -c sqlite3.c -o sqlite3.o

gcc $CFLAGS \
 -shared sqlite3.o -Wl,-soname -Wl,libsqlite3.so.0 -o libsqlite3.so.0.8.6

ln -s libsqlite3.so.0.8.6 libsqlite3.so.0
ln -s libsqlite3.so.0.8.6 libsqlite3.so
ar cru libsqlite3.a sqlite3.o
ranlib libsqlite3.a

gcc $CFLAGS \
 -DSQLITE_OS_UNIX=1 -DNDEBUG -DSQLITE_THREADSAFE=0 \
 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 \
 -DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_COLUMN_METADATA=1 -DHAVE_READLINE=1 \
 -I/usr/include/readline -o sqlite3 shell.c libsqlite3.so -lreadline

sudo mv libsqlite3.a libsqlite3.so libsqlite3.so.0 libsqlite3.so.0.8.6 /usr/lib
sudo mv sqlite3 /usr/bin


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

Re: [sqlite] sqlite3 issue in sqlite3VdbeReset

[Pavel Ivanov]

> Why do sqlite3VdbeReset() always produces problem;

What problem? Why 'always' if only you complain and it works in
other's applications?

> Could you please provide any help in this issue.

Run valgrind on your application and see where memory is getting
corrupted or double-freed.


Pavel

On Wed, Oct 14, 2009 at 3:13 AM, Kuldeep Paranjpe
 wrote:
> Hi,
>
> Why do sqlite3VdbeReset() always produces problem;
> Please check the following calling stack trace of one of our module which 
> crashes because of sqlite3 library.
>
> #4056 0x4805eed0 in sqlite3VdbeMemRelease ()
> #4057 0x4805eed0 in sqlite3VdbeMemRelease ()
> #4058 0x4805eed0 in sqlite3VdbeMemRelease ()
> #4059 0x4805eed0 in sqlite3VdbeMemRelease ()
> #4060 0x4805eed0 in sqlite3VdbeMemRelease ()
> #4061 0x4805eed0 in sqlite3VdbeMemRelease ()
> #4062 0x4805eed0 in sqlite3VdbeMemRelease ()
> #4063 0x4805eed0 in sqlite3VdbeMemRelease ()
> #4064 0x4805eed0 in sqlite3VdbeMemRelease ()
> #4065 0x4805eed0 in sqlite3VdbeMemRelease ()
> #4066 0x4805eed0 in sqlite3VdbeMemRelease ()
> #4067 0x4805eed0 in sqlite3VdbeMemRelease ()
> #4068 0x4805eed0 in sqlite3VdbeMemRelease ()
> #4069 0x4805eed0 in sqlite3VdbeMemRelease ()
> #4070 0x4805ca34 in releaseMemArray ()
> #4071 0x4805cfa8 in Cleanup ()
> #4072 0x4805dacc in sqlite3VdbeReset ()
> #4073 0x4805db5c in sqlite3VdbeFinalize ()
> #4074 0x48051378 in sqlite3_finalize ()
> #4075 0x4804bcc0 in _sqlite3_finalize(void*) ()
> #4076 0x4804b744 in CDBReader::Close() ()
>
> Could you please provide any help in this issue.
>
> Thanks in anticipation;
>
> Thanks and Regards,
> Kuldeep
>
> ___
> 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] when to create temporary disk file for group by and order by?

[Pavel Ivanov]

AFAIK, SQLite's optimizer doesn't unfold inner statements to get one
query that wouldn't require to store temporary data. So in your query
SQLite needs temporary space to store results of 2 inner queries. Then
it joins these results and most probably it does it not so effectively
as you might think.
So you need to re-write your query so that it doesn't contain inner
selects but first eliminate errors (such as selecting B.a) and
undefined behavior (such as SELECT b FROM tbl GROUP BY a).

Pavel

On Wed, Oct 14, 2009 at 5:12 AM, Wenton Thomas  wrote:
> sqlite  perform  group by and order by  using   transient index,
> and   if   there isn't exist such an index,  sqlite will  create  the index 
> and
> store it  in its  a temporary file.
>
> So I  think  the following  SQL statement won't   create  temporary file in 
> disk.
>
> create table tbl(a,b,c);
> create index on  tbl(a);
> create index on  tbl(b);
>
> SELECT    B.a,B.b,B.c  FROM
>  (SELECT  a,b  FROM tbl GROUP BY a) A
> INNER JOIN
> (SELECT     b,c  FROM tbl  ORDER BY b)B
> ON  A.b=B.b
>
> Because  both   group by  and order by  could make use of  appropriate  index 
> .
>
> But  in my  test,  temporary file appear.
>
>
>
> ___
> 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] Hi, new member here (and also my first question)

[Pavel Ivanov]

Do you initialize your sqlite3_stmt* pointer in constructor? Is there
any corrupting memory code in other parts of your application?
You know, it's pretty hard to read and debug your application without
seeing it. But believe us there's nothing wrong with SQLite and
sqlite3MemFree(), something wrong with your application and so start
looking from this point of view.
You can easily debug the problem as long as you already started
reading SQLite's code: just look what pointer causes the problem, look
what value it has at the statement initialization, put breakpoint at
its change and put breakpoint at sqlite3MemFree with this pointer
value...


Pavel

On Tue, Oct 13, 2009 at 11:46 PM,   wrote:
> Oh yeah, I forgot to tell you that I'm using Visual C++ 2008 professional
> and it always crashes at this:
>
> C:\Program Files\Microsoft Visual Studio 9.0\VC\crt\src\dbgheap.c -
> function "_free_dbg_nolock", line 1317:
>        /*
>         * If this ASSERT fails, a bad pointer has been passed in. It may be
>         * totally bogus, or it may have been allocated from another heap.
>         * The pointer MUST come from the 'local' heap.
>         */
>        _ASSERTE(_CrtIsValidHeapPointer(pUserData));
>
>
>
> ben...@cs.its.ac.id wrote:
>> Well, I'm pretty sure I haven't. FYI, I wrapped the sqlite3_stmt into a
>> class and only call its sqlite3_finalize on its destructor. So there's no
>> way that it would be called twice. Or so I think.
>>
>> Pavel Ivanov wrote:
 The pPrior or p pointer isn't null so it should've been
 freed without error IMHO. Can anybody tell me what's wrong with it?
 Thanks
 a lot in advance.
>>>
>>> If "pPrior or p pointer" isn't null but was already freed then double
>>> free can cause segmentation fault. In other words most probably you're
>>> calling sqlite3_finalize on already finalized statement.
>>>
>>> Pavel
>>>
>>> On Tue, Oct 13, 2009 at 5:58 AM,   wrote:
 Hi there, I'm a new member of the mailing list. Nice to meet you all.

 BTW, I've got one problem that's been bugging me for weeks.

 Occasionally (not always), I got a seg fault at "static void
 sqlite3MemFree(void *pPrior)". It happened when I do sqlite3_reset or
 sqlite3_finalize. The pPrior or p pointer isn't null so it should've
 been
 freed without error IMHO. Can anybody tell me what's wrong with it?
 Thanks
 a lot in advance.


>
>
> Fare thee well,
> Bawenang R. P. P.
>
> 
> "If a picture is worth a thousand words, an animations is worth a thousand
> pictures. And to take that a step further, a game is worth a thousand
> animations." – Peter Raad, Executive Director, The Guildhall at SMU
>
>
> --
>
> http://www.its.ac.id
> ___
> 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] Corrupted database

[Filip Navara]

On Mon, Oct 12, 2009 at 8:56 PM, McClellen, Chris
 wrote:
> What is your synchronous set to?  Full?  FYI If you are using .NET data
> providers, it is set to "Normal" by default.

Normal or Off, but no power failure was involved. (Yes,
System.Data.SQLite is used)

> If it is not set to full, I have seen corruption when an application
> crashes, or exits when a thread is in the middle of updating the db
> (Synchronous = OFF makes corruption even easier in this case).  I have
> seen apps that do not wait for background threads to finish before
> termination, and without full sync on, either the db or the log gets
> corrupted.  A corrupted log can cause problems for you db on next run
> when recovery happens.

Sounds suspiciously like our case, but still Synchronous=off is
supposed to work in the event of application crash, hard killed
threads and so on. Previous version of the application frequently
forgot to close the databases on exit and did other nasty things that
and now fixed, but none of them should cause the database to be
corrupted.

Best regards,
Filip Navara
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] readers and writer

[Edzard Pasma]

--- prii...@stanford.edu wrote:
> 
> Simon Slavin wrote:
>> On 14 Oct 2009, at 1:21am, priimak wrote:
>>
>>   
>>> I am heaving small problem with sqlite. I have a webapp which connects
>>> to the database using sqlite-jdbc and performs SELECTs to response to
>>> different GET requests, while this happens if I try to write to a
>>> database ( UPDATE or INSERT ) from command line, that (i.e. update
>>> process) would occasionally fail with error message "SQL error near  
>>> line
>>> 1: database is locked". Notice that I have only one writer, but many
>>> readers. Reading documentation (http://www.sqlite.org/ 
>>> lockingv3.html) I
>>> was under impression that process which intends to update database  
>>> will
>>> place it in the pending state allowing all currently running reads (
>>> SELECTs ) to proceed, while blocking new SELECTs, the lock database
>>> apply changes and then unlock it allowing all pending and new  
>>> SELECTs to
>>> proceed. Am I right about it and if so why do I "SQL error near line  
>>> 1:
>>> database is locked" when trying to write to a database?
>>> 
>>
>> I hope this will do until an expert comes along.  I think you got it  
>> right, you just don't know something.
>>
>> The SELECT activity requires a lock to the database.  For instance,  
>> consider a TABLE contact with columns name, address, phone .  An index  
>> is declared on just the name column.  You execute
>>
>> SELECT phone FROM contacts WHERE name = 'Jackie'
>>
>> This requires a two-stage process: first use the index to find the ids  
>> of the rows which have the right name.  Then look up those rows in the  
>> table and find out which phone numbers they have.  Obviously, this  
>> requires locking: you wouldn't want someone to make changes to the  
>> table between those two steps.  However, it requires locking only  
>> against writing: other reads going on at the same time are harmless,  
>> but a change between the two steps can invalidate the data.
>>
>> So if a SELECT is in progress, other SELECT commands can be allowed to  
>> proceed without problems. But no INSERT or UPDATE can be allowed until  
>> the SELECT is finished.  Hence you will sometimes get a lock on the  
>> write.
>>
>> How you deal with this, I don't know.  Random wait-and-try-again ?
> Yes, I understood that, but the impression I got is that SELECT will
> place shared lock on the database. While INSERT or UPDATE will first
> place PENDING lock indicating that it wants to write. While it is in a
> PENDING lock state all operations that placed SHARED lock ( such as
> SELECTs ) will allow to complete and new SHARED locks either denied
> or blocked ( this part of documentation is not clear as to which one of
> these two actions are taken  ). Then when all SHARED locks are
> removed due to completion of SELECTs, database moves from PENDING
> into EXCLUSIVE lock, which is cleared when update/write completed and then
> new/pending SHARED locks are allowed to proceed. This should mean
> that with many processes reading and only one writing there is no need to
> use sqlite3_busy_timeout() function, which is to be used when we have
> many processes trying to write to the database and/or reader if new
> SHARED locks are denied while database is in a PENDING and/or
> EXCLUSIVE lock state ( again, this point it not clear in documentation ).
> Do I understand it correctly?
> 
> --
> Dmitri Priimak

Hello Dmitri, I understaod it the same way. However recently I observed that a 
PENDING lock does not perform its useful function (prevent writer starvation) 
in case readers and writers are threads of a single process! May that be the 
case? Best regards, Edzard Pasma. 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Segfault on a specific input

[D. Richard Hipp]

On Oct 14, 2009, at 4:30 AM, Wentao Han wrote:

> Hi there,
>
> When I type the following statements of SQL into sqlite3, it crashed  
> with
> segmentation fault.
> CREATE TABLE todo (
>  id integer primary key,
>  title text,
>  created timestamp default (now()),
>  done boolean default 'f'
> );
> INSERT INTO todo (title) VALUES ('Learn web.py');
> I tried this under both 3.6.6.2 on Linux, and 3.6.10 on Mac OS X.


http://www.sqlite.org/src/info/2d401a
Fixed in 3.6.18

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Select * from table where field = "value" does not work when "value" is also a field....

[Simon Davies]

2009/10/13 Hillebrand Snip :
> I have a database with the following fields:
> 1) Status (it will hold values like "Open", "Closed", "Submitted"... etc...)
> 2) Closed (boolean field which contains 1 or 0)
>
> If i enter a query like:  Select * from Issues where Status != "Closed"  i
> get all records (even the ones with Status=Closed).
> If i replace "Closed" by "Submitted" which is not also a field name the
> results are as expected.
>
> I think this is a bug.

... in your select statement.

Double quotes are used to delimit identifiers. Use single quotes to
delimit string literals, i.e.
Select * from Issues where Status != 'Closed'

Where SQLite cannot find a column match for a double-quote delimited
string, then it will assume that a string literal is intended.

See http://www.sqlite.org/lang_keywords.html

>
> I use Apache.
>
> SQLITE Information from phpinfo():
>
>
> SQLiteSQLite supportenabledPECL Module version2.0-dev $Id: sqlite.c,v
> 1.166.2.13.2.10 2007/12/31 07:20:11 sebastian Exp $SQLite Library2.8.17SQLite
> Encodingiso8859

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

[sqlite] Segfault on a specific input

[Wentao Han]

Hi there,

When I type the following statements of SQL into sqlite3, it crashed with
segmentation fault.
CREATE TABLE todo (
  id integer primary key,
  title text,
  created timestamp default (now()),
  done boolean default 'f'
);
INSERT INTO todo (title) VALUES ('Learn web.py');
I tried this under both 3.6.6.2 on Linux, and 3.6.10 on Mac OS X.

-- 
Wentao Han

Institute of High-Performance Computing
Department of Computer Science and Technology
Tsinghua University

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

[sqlite] sqlite3 issue in sqlite3VdbeReset

[Kuldeep Paranjpe]

Hi,

Why do sqlite3VdbeReset() always produces problem;
Please check the following calling stack trace of one of our module which 
crashes because of sqlite3 library.

#4056 0x4805eed0 in sqlite3VdbeMemRelease ()
#4057 0x4805eed0 in sqlite3VdbeMemRelease ()
#4058 0x4805eed0 in sqlite3VdbeMemRelease ()
#4059 0x4805eed0 in sqlite3VdbeMemRelease ()
#4060 0x4805eed0 in sqlite3VdbeMemRelease ()
#4061 0x4805eed0 in sqlite3VdbeMemRelease ()
#4062 0x4805eed0 in sqlite3VdbeMemRelease ()
#4063 0x4805eed0 in sqlite3VdbeMemRelease ()
#4064 0x4805eed0 in sqlite3VdbeMemRelease ()
#4065 0x4805eed0 in sqlite3VdbeMemRelease ()
#4066 0x4805eed0 in sqlite3VdbeMemRelease ()
#4067 0x4805eed0 in sqlite3VdbeMemRelease ()
#4068 0x4805eed0 in sqlite3VdbeMemRelease ()
#4069 0x4805eed0 in sqlite3VdbeMemRelease ()
#4070 0x4805ca34 in releaseMemArray ()
#4071 0x4805cfa8 in Cleanup ()
#4072 0x4805dacc in sqlite3VdbeReset ()
#4073 0x4805db5c in sqlite3VdbeFinalize ()
#4074 0x48051378 in sqlite3_finalize ()
#4075 0x4804bcc0 in _sqlite3_finalize(void*) ()
#4076 0x4804b744 in CDBReader::Close() ()

Could you please provide any help in this issue.

Thanks in anticipation;

Thanks and Regards,
Kuldeep

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

[sqlite] Select * from table where field = "value" does not work when "value" is also a field....

[Hillebrand Snip]

I have a database with the following fields:
1) Status (it will hold values like "Open", "Closed", "Submitted"... etc...)
2) Closed (boolean field which contains 1 or 0)

If i enter a query like:  Select * from Issues where Status != "Closed"  i
get all records (even the ones with Status=Closed).
If i replace "Closed" by "Submitted" which is not also a field name the
results are as expected.

I think this is a bug.

I use Apache.

SQLITE Information from phpinfo():


SQLiteSQLite supportenabledPECL Module version2.0-dev $Id: sqlite.c,v
1.166.2.13.2.10 2007/12/31 07:20:11 sebastian Exp $SQLite Library2.8.17SQLite
Encodingiso8859
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] when to create temporary disk file for group by and order by?

[Wenton Thomas]

sqlite  perform  group by and order by  using   transient index,  
and   if   there isn't exist such an index,  sqlite will  create  the index and
store it  in its  a temporary file.

So I  think  the following  SQL statement won't   create  temporary file in 
disk.

create table tbl(a,b,c);
create index on  tbl(a);
create index on  tbl(b);

SELECTB.a,B.b,B.c  FROM
 (SELECT  a,b  FROM tbl GROUP BY a) A 
INNER JOIN
(SELECT b,c  FROM tbl  ORDER BY b)B 
ON  A.b=B.b

Because  both   group by  and order by  could make use of  appropriate  index .

But  in my  test,  temporary file appear.


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

Re: [sqlite] New open source data synchronization tool

[hfdabler]

It is part of Data Integration and Business Intelligence. ETL is
database-related.


Jay A. Kreibich-2 wrote:
> 
> On Wed, Oct 07, 2009 at 10:49:55AM -0500, Beau Wilkinson scratched on the
> wall:
>> >Hello,
>> >
>> >We are trying to find an ETL tool open source. Basically, we need our
>> >software to perform ETL, data migration and data synchronization.
>> >
>> >The program should not be used on larger projects. A few open source
>> tools
>> >are on the market.
>> 
>> >Some ideas? Thanks.
>> 
>> What is ETL?
> 
>   http://www.google.com/search?q=ETL
> 
>   "Extract, Transform, Load."
> 
> -j
> 
> -- 
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> 
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/New-open-source-data-synchronization-tool-tp25783393p25887643.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