date:20101202

Re: [sqlite] WAL file size

2010-12-02 Thread Dan Kennedy


In the current WAL format, the checksum for each frame is based on
the contents of the frame, the salt-values in the wal header and the
checksum of the previous frame.

In this scheme is each frame checksum independent? i.e. each frame
checksum is computed based only on the salt values in the WAL header
and the frames own contents?


Dan.



On 12/02/2010 11:04 PM, Yoni Londner wrote:
> Hi,
>
> I will start out by stating that I am not deeply familiar with the
> sqlite&  WAL file layout, but I will try anyway, from my current
> understanding:
>
> The general problem that needs to be solved is to allow SQLITE to be
> constantly used over time (with no 'idle' time where no sql operations
> are done, and it is not inside any transaction).
>
> The current WAL design does not allow this, since if there are all the
> time open read transactions and/or write transactions, the WAL will
> continue to grow forever.
> I copy/paste below a tiny C program that re-creates this with write
> transactions.
>
> Remember also that in the WAL file we 'gather' up a lot of work. If we
> do it in the background, this work-load can be smoothed up to run in
> parallel to the regular system work, but if we must make the SQL 'idle'
> (close all transactions) in order to execute the checkpoint, on a heavy
> load system this can mean halting the system for a long period of time
> (in our case, typically for 30 seconds!).
>
> This needs to be solved by two "features" to be added to WAL:
>
> 1) 'incremental' checkpoint.
>
> 2) WAL file recycling (this item can also be solved by two WAL files,
> but I think its better to make the WAL file format a little bit more
> complex than start having to handle in the code management of multiple
> files).
>
> Incremental checkpointing means that checkpointing can be done up until
> the first open transaction (mxFrame). This means both copying the WAL
> pages up-until mxFrame of the first open transaction to the DB file, and
> then MARKING those frames as 'DONE' (I will talk later on how to do the
> 'DONE' marking).
>
> This means that from that point onwards - accessing those pages will be
> to the DB file, not the WAL file.
>
> WAL recycling will be done by writing pages to the beginning of the WAL
> file when a certain amount of pages from the beginning of the WAL file
> are 'checkpointed' (marked as DONE). This can also happen in the middle
> of a transaction.
>
> Example:
>
> Legend:
> H - header.
> 1, 2, 3.. - page of transaction 1, 2, 3..
> C - commit marker.
> BOF1: beginning of WAL-1. EOF1: end of WAL-1
> BOF2: beginning of WAL-2. EOF2: end of WAL-2
> P: 64K of padding (junk data)
>
> WAL file with transactions 1 2 and 3 committed, and transaction 4 open:
> H 1 1 1C 2 2 2 2C 3 3 3 3C 4 4 4
> +-- BOF1 +--- EOF1
>
> We continue to add to transaction 4:
> H 1 1 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
> +-- BOF1 +--- EOF1
>
> In the meantime, we checkpointed transactions 1 and 2, because there is
> a read transaction working on transaction 3:
> H 1 1 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
> +-- BOF1 +--- EOF1
> +- checkpointed up to here
>
> No we decided we want to recycle. Since there is no read transaction
> open on transaction 1 and 2 (cannot be, since if you need a page from
> transaction 1, you will find it in the DB), we can reuse them:
> H 4 1 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
> +-- BOF2+-- BOF1 +--- EOF1
> +-- EOF2+ checkpointed up to here
> +-- the 6th page of transaction 4
>
> And now we close transaction 4:
> H 4 4C 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
> | +-- EOF2   +-- BOF1 +--- EOF1
> +--- BOF2+ checkpointed up to here
>
> Lets open transaction 5, and write faster than the background checkpointing:
>
> H 4 4C 5  5 5 5 5  3 3 3 3C 4 4 4 4 4
> | |  +-- BOF1 +--- EOF1
> +--- BOF2 |  + checkpointed up to here
>   +-- EOF2
>
> So we need more place for 5, we will write it at the end:
> H 4 4C  5 5 5 5 5  3 3 3 3C 4 4 4 4 4 5 5 5 5C
> | |  || | +-- EOF3
> | |  || +--- BOF3
> | |  +-- BOF1 +--- EOF1
> +--- BOF2 |  + checkpointed up to here
>   +-- EOF2
>
> Ok. This is the most complicated 'mess up' of this WAL file possible. It
> cannot get 'messier' than this, since we recyclye ONLY when there is a
> minimum of N (configurable) amount of checkpointed pages at the
> beginning of the WAL file AND if there are no 'gaps' in the not-yet
> checkpointed pages.
> The first condition is mainly for efficiency or large workloads (since
> recycling has its costs). This second condition is to prevent the WAL
> files layout to have multiple recycling points in it (prevents it from
> getting 'messed up'). A wal file can only have at MAX one recycle point
> at one point in file, a

Re: [sqlite] Android database corruption

2010-12-02 Thread Simon Slavin

On 3 Dec 2010, at 4:08am, jtdrexel wrote:

> Some people say that the "dialer
> storage" is related to sms and mms because the file size of the 'dialer
> storage' file changes accordingly with the deletion and addition of
> messages. Is the sms database and SQLite database? and is it located in the
> 'dialer storge' file?

The people at Google who wrote Android would definitely know the answers to 
these questions.  If they want our help they know where to post, and they're in 
a position to give us much more error information than you can.

> Some people have reported that when the 'dialer
> storage' file size gets too large this same sms-vanishing issue occurs.

There are no file-size problems with SQLite at anything as small as a file of 
1000 SMS messages.  From the various reports in that thread I can guess (but 
it's only a guess) that the problem isn't with SQLite at all but with some 
lower-level system like the file system or working memory or a corrupted copy 
of the software.

> Please let me and others know (at the code.google.com fourm mentioned above)

If you want help, you post here and we'll answer here.  If one of the SQLite 
developers wants to post to that list they'll do it.

> if there is something we can collectively do to help because apperently
> google has been ignoring to address the 1000s of customer complaints
> regarding the very issue.

Okay I've scanned the thread now.  We're happy to help anyone who can provide 
us with debugging information but the simple news that the database has become 
corrupted is not enough to let us figure out what's happening.  If you can 
provide us with a copy of a corrupted database (just before it's about to be 
deleted) then that will at least give us something to look at.

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

Re: [sqlite] Android database corruption

2010-12-02 Thread Simon Slavin

On 3 Dec 2010, at 4:08am, jtdrexel wrote:

>> It works perfectly 99% of the time, but every now and again, we get
>> database corruption appear randomly. We have not changed any pragmas, so
>> synchronous is set to full and journal_mode delete. It does not correspond
>> to any power failures, or application crashes, and will succeed in pulling
>> back data from the database for hundreds of calls, and then fail on a
>> query that has worked many times before. It can occur in a number of
>> native calls. We have performed integrity checks on the db in question and
>> they return ok (prior to the corruption - androids response to a corrupt
>> db is to delete it, so we can't see its state after corruption).

Is there any possibility at all that you can show us a database after 
corruption ?  Perhaps you can change Android or an Android simulator so it 
renames the database instead of deleting it.

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

Re: [sqlite] Android database corruption

2010-12-02 Thread jtdrexel

Hello Cliff 2,
 I am not as profficient as you in android programming of SQLite but i
am willing to help solve this problem. The reason why i am coming forward to
help is because of comment # 451 on the following fourm:
http://code.google.com/p/android/issues/detail?id=5669. There are a
countless number of android users whose text messages vanish randomly. After
hours of online research i have found this thread posted by you. So far,
your response is the closest thing to any type of addressing-the-issue that
i have found. So how can i help you solve this problem to help me use my
android without the fear of loosing my data? I'm sure you'll get many more
people to help you with this issue on the fourm i mentioned above. My SMS
and MMS have vanished on three seperate occations and the latest one was
this morning. last night i recieved an MMS notification from a friend of
mine. I tried to open it and it said that it was downloading the MMS. It was
taking a long time to download and the 3G data connection didn't seem to be
active on the notification bar. So i shut the screen off and went about my
business. At a later time i went back into the sms thread between me and my
friend to retrieve that MMS but could find that MMS anywhere. It was like i
never got it. But at that time, all the other threads between other friends
were still accessible up until this morning. I recieved a sms from another
friend was able to read it fine. An hour later I recieved a mms from my wife
and it downloaded it and i got to view it too. It was a small picture
slideshow or something. After viewing it, i turned the screen off and I
about my business. Half an hour later i turned my phone on to send an sms to
one of my friends and discovered that all my messages have vanished. I use
handcent btw. From my research it seems that this problem occurs independent
of the sms software being used, independent of the android build being used,
independent of the brand of android phone being used, independent of the
other background user-installed apps being used. I can submit a bug report
log of my phone if you want but not sure if it'll help. But what i'm really
trying to ask you is: is there something i and many others can do to help
capture critical information when this stuff occurs that would help find
what your looking for with this issue? Some people say that the "dialer
storage" is related to sms and mms because the file size of the 'dialer
storage' file changes accordingly with the deletion and addition of
messages. Is the sms database and SQLite database? and is it located in the
'dialer storge' file? Some people have reported that when the 'dialer
storage' file size gets too large this same sms-vanishing issue occurs.
Please let me and others know (at the code.google.com fourm mentioned above)
if there is something we can collectively do to help because apperently
google has been ignoring to address the 1000s of customer complaints
regarding the very issue.

cliff 2 wrote:
> 
> Hi,
> 
> Hopefully someone here can help where the android guys haven't been able
> to. We are having an issue with our sqlite database running on the android
> platform. We are accessing the db from the sdcard on an android phone
> running android 2.1. 
> 
> It works perfectly 99% of the time, but every now and again, we get
> database corruption appear randomly. We have not changed any pragmas, so
> synchronous is set to full and journal_mode delete. It does not correspond
> to any power failures, or application crashes, and will succeed in pulling
> back data from the database for hundreds of calls, and then fail on a
> query that has worked many times before. It can occur in a number of
> native calls. We have performed integrity checks on the db in question and
> they return ok (prior to the corruption - androids response to a corrupt
> db is to delete it, so we can't see its state after corruption).
> 
> The database in question is downloaded as a complete binary and then
> accessed read-only using android's rawQuery call.
> 
> A couple of typical stack traces for when the corruption occurs:
> 
>   03-26 14:09:50.572 E/DatabaseHelper( 1253): 
> android.database.sqlite.SQLiteDatabaseCorruptException: database disk
> image is malformed: , while compiling: SELECT o.FieldId,o.Reference,
> o.Category, o.OIndex,o.Description, c.Choice, c.Derivative FROM Option o,
> Choice c WHERE o.FieldId = c.FieldId and o.Reference = ? ORDER BY
> o.Option, o.OIndex
>   03-26 14:09:50.572 E/DatabaseHelper( 1253): at
> android.database.sqlite.SQLiteProgram.native_compile(Native Method)
>   03-26 14:09:50.572 E/DatabaseHelper( 1253): at
> android.database.sqlite.SQLiteProgram.compile(SQLiteProgram.java:110)
>   03-26 14:09:50.572 E/DatabaseHelper( 1253): at
> android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:59)
>   03-26 14:09:50.572 E/DatabaseHelper( 1253): at
> android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:49)
>   03-26 14:09:50.572 E/Dat

Re: [sqlite] Cross-compiling SQLite for Blackfin+uClinux?

2010-12-02 Thread Gilles Ganault

At 22:38 02/12/2010, Mike Frysinger wrote:
>that doesnt really make sense.  just build sqlite in the dist and 
>look at how configure is run.

If I got it right, the SQLite package that lives in the uClinux-dist 
uses the GNU build system 
(http://en.wikipedia.org/wiki/GNU_build_system).

Wikipedia tells me it's a richer tool than plain Makefile, but it's 
also harder to follow for newbies like me. That's why I prefered to 
start from a very basic solution and learn how to compile it manually.

>that isnt the blackfin dist

It is (uClinux-dist-2009R1.1-RC4.tar.bz2), but I used the 2007 
toolchain (blackfin-toolchain-07r1.1) to try and compile SQLite 
because that's the one that is used by the device to compile uClinux 
and packages. I figured it wasn't a good idea to compile applications 
using two different releases of the toolchain.

All is fine now, I learned a lot, I can start the Lua interpreter, 
load the LuaSocket module and the LuaSQL module, and create an SQLite 
database. Thanks to everyone in this list for the great help.  

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

Re: [sqlite] EXTERNAL:Re: Cross-compiling SQLite forBlackfin+uClinux?

2010-12-02 Thread Gilles Ganault

On Thu, 2 Dec 2010 15:17:28 -0500, Eric Smith
 wrote:
>You can do it on the command line -- just try it.  

Sorry guys for being dense: After rereading the above, it clicked: The
"-lpthread" option must be put on the line when linking the three
object files to create the final .so file:

#config
LDFLAGS=-lpthread

#Makefile
src/$(LIBNAME): $(OBJS)
$(CC) $(CFLAGS) $(LDFLAGS) $(LIB_OPTION) -o $@ $(OBJS)
/usr/src/sqlite-3.7.3/preprocessed/libsqlite3.o

I'm surprised, though, that I don't first need to cross-compile the
pthread library before the linker includes it in the final binary
(sqlite3.so).

>gcc == command line tool for preprocessing/compiling/linking C 
>programs.  
>
>GCC == GNU Compiler Collection, a suite of tools for compiling tons of 
>different languages.  See http://gcc.gnu.org.  

Thanks for pointing out the difference.

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

Re: [sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

2010-12-02 Thread Sylvain Pointeau

Hi,

I am on macosx sqlite 3.7.3

$ /usr/local/bin/sqlite3 test.db3
SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read ./testSchema.sql
sqlite> .read ./TestValues.sql
sqlite> .read ./TestQuery.sql
Otterbourne Golf Course|Jane Doe|Assumed Name

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

Re: [sqlite] EXTERNAL:Re: Cross-compiling SQLite forBlackfin+uClinux?

2010-12-02 Thread Eric Smith

Gilles Ganault wrote: 

> Sorry about that :-/ Apparently, gcc will compile and link in one go.  
> Is a Makefile required to add the -lpthread switch, or can this be 
> done on the command line?  

You can do it on the command line -- just try it.  

make(1) (and its typical input, a set of makefiles) is just a clever 
wrapper for shell commands.  It's not "required" to do anything at all, 
though life soon becomes miserable without it or something similar.  

> BTW, am I correct in understanding that originally, gcc stood for "GNU 
> C compiler", while now, gcc is just a language-neutral front-end that 
> can compile different source files (C, C++, Fortran, etc.)  and is 
> smart enough to guess what type of source files it is handed and call 
> the right compiler accordingly?  

gcc == command line tool for preprocessing/compiling/linking C 
programs.  

GCC == GNU Compiler Collection, a suite of tools for compiling tons of 
different languages.  See http://gcc.gnu.org.  

Eric 

-- 
Eric A. Smith

Wright, rightly righting waiting wights' weighty writing rights 
writ white, writes right. 
-- artifex2004
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXTERNAL:Re: Cross-compiling SQLite forBlackfin+uClinux?

2010-12-02 Thread Gilles Ganault

On Thu, 2 Dec 2010 13:50:30 -0600, "Black, Michael (IS)"
 wrote:
>The -lpthread goes on the FINAL link you do with your OBJS.

Sorry about that :-/ Apparently, gcc will compile and link in one go.
Is a Makefile required to add the -lpthread switch, or can this be
done on the command line?
This is just to learn more. I don't mind whiping off a small Makefile
to do this.

BTW, am I correct in understanding that originally, gcc stood for "GNU
C compiler", while now, gcc is just a language-neutral front-end that
can compile different source files (C, C++, Fortran, etc.) and is
smart enough to guess what type of source files it is handed and call
the right compiler accordingly?

Thank you.

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

Re: [sqlite] Transactions while using prepare and step

2010-12-02 Thread Simon Slavin

On 2 Dec 2010, at 7:38pm, cricketfan wrote:

> {
> Select a from tbl1 where b='yyy' c ='xxx' limit i offset (i + 1) ;
> Update tbl1 set e=1 where a = some value from the select;
> i = i + 1
> }

There doesn't appear to be any purpose to anything but the UPDATE command.  Can 
you not do all the dating with a single UPDATE command that has a less specific 
WHERE clause ?

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

Re: [sqlite] EXTERNAL:Re: Cross-compiling SQLite forBlackfin+uClinux?

2010-12-02 Thread Black, Michael (IS)

Wrong way again

The -lpthread goes on the FINAL link you do with your OBJS.

It told you "linking not done" becauce your "-c" is just compiling to the .o 
file and no libraries are needed for that.

All -l library references are when you build the final executable.

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems

From: sqlite-users-boun...@sqlite.org on behalf of Gilles Ganault
Sent: Thu 12/2/2010 1:07 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] EXTERNAL:Re: Cross-compiling SQLite forBlackfin+uClinux?

On Thu, 2 Dec 2010 12:38:18 -0600, "Black, Michael (IS)"
 wrote:
>If you need threadsafe just add -lpthread to your final link

I get an error when using "-lpthread", which I guess makes sense,
since I don't have the pthread library cross-compiled:

=
# bfin-linux-uclibc-gcc -v -O2 -Wall -fpic -lpthread -c -o
libsqlite3.o sqlite3.c
...
bfin-linux-uclibc-gcc: -lpthread: linker input file unused because
linking not done
=

I don't intend to access SQLite through multiple threads anyway, so
it's OK to compile it with "-DSQLITE_THREADSAFE=0".

>The .a and .so file allow your linker to only extract the routines it needs 
>and not the whole thing you would get from the .o file.

Thanks a lot for the education.

___
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] Transactions while using prepare and step

2010-12-02 Thread Pavel Ivanov

> Another question - What kind of impact does a limit clause have? The columns
> being used in the where clause are indexed. My current design is bad, I am
> forced to use limit to get one row at a time. Since I have an index the
> impact should be minimal. Please let me know if I am wrong.

You wrong. First of all LIMIT ... OFFSET ... clauses have undefined
results if you don't have ORDER BY clause. And second although you are
selecting only one row SQLite should scan and count all i rows to
return the (i + 1)th. So you better select everything, remember it and
then update it one-by-one (preferably in smallest number of
transactions possible).

And it's not understandable from your pseudo code what is "step" and
does thread 1 execute 1 insert statement or several in a loop (which I
suppose you intended to show).


Pavel

On Thu, Dec 2, 2010 at 2:38 PM, cricketfan  wrote:
>
> I have 2 threads in my program can someone take a look at them and provide
> some comments
> Pseudo code for thread 1 (a is the primary key)
> Thread1()
> {
> insert into tbl1(a,b,c,d,e,f,g,h,i,j,k);
> }
> So my pseudo code is(b and c have an index, a is primary key)
> Thread2()
> {
> sleep(200);
> prepare;
> while(step)
> {
>     Select a from tbl1 where b='yyy' c ='xxx' limit i offset (i + 1) ;
>     Update tbl1 set e=1 where a = some value from the select;
>     i = i + 1
> }
> }
>
> If I run these 2 threads individually the tps is acceptable but if I run
> them together the performance is woeful. I am running in serialized mode.
> Both threads use different handles to DB. Please advice what I can do in
> order to improve performance.
>
> Another question - What kind of impact does a limit clause have? The columns
> being used in the where clause are indexed. My current design is bad, I am
> forced to use limit to get one row at a time. Since I have an index the
> impact should be minimal. Please let me know if I am wrong.
>
>
> cricketfan wrote:
>>
>> Hello I have a basic question and would be glad if someone can answer it.
>> I understand that if we have "BEGIN TRANSACTION" and "END TRANSACTION"
>> then the transaction(s) within that block would be committed, number of
>> transactions could be 10,100,1000 or 1. . Otherwise, SQLITE by
>> default commits for every single insert,update,delete.
>> I have a prepare statement(using limit clause so will get only 10 rows
>> back) followed by step(during which I use an update clause) so how would
>> the transaction concept behave in this case? If I wrap the loop I use for
>> stepping with a begin transaction(followed by an end transaction  when the
>> loop ends) would it be sufficient?
>>
>
> --
> View this message in context: 
> http://old.nabble.com/Transactions-while-using-prepare-and-step-tp30359695p30361890.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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions while using prepare and step

2010-12-02 Thread cricketfan


I have 2 threads in my program can someone take a look at them and provide
some comments
Pseudo code for thread 1 (a is the primary key)
Thread1()
{
insert into tbl1(a,b,c,d,e,f,g,h,i,j,k);
}
So my pseudo code is(b and c have an index, a is primary key)
Thread2()
{
sleep(200);
prepare;
while(step)
{
 Select a from tbl1 where b='yyy' c ='xxx' limit i offset (i + 1) ;
 Update tbl1 set e=1 where a = some value from the select;
 i = i + 1
}
}

If I run these 2 threads individually the tps is acceptable but if I run
them together the performance is woeful. I am running in serialized mode.
Both threads use different handles to DB. Please advice what I can do in
order to improve performance.

Another question - What kind of impact does a limit clause have? The columns
being used in the where clause are indexed. My current design is bad, I am
forced to use limit to get one row at a time. Since I have an index the
impact should be minimal. Please let me know if I am wrong.


cricketfan wrote:
> 
> Hello I have a basic question and would be glad if someone can answer it.
> I understand that if we have "BEGIN TRANSACTION" and "END TRANSACTION"
> then the transaction(s) within that block would be committed, number of
> transactions could be 10,100,1000 or 1. . Otherwise, SQLITE by
> default commits for every single insert,update,delete.
> I have a prepare statement(using limit clause so will get only 10 rows
> back) followed by step(during which I use an update clause) so how would
> the transaction concept behave in this case? If I wrap the loop I use for
> stepping with a begin transaction(followed by an end transaction  when the
> loop ends) would it be sufficient?
> 

-- 
View this message in context: 
http://old.nabble.com/Transactions-while-using-prepare-and-step-tp30359695p30361890.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] EXTERNAL:Re: Cross-compiling SQLite for Blackfin+uClinux?

2010-12-02 Thread Gilles Ganault

On Thu, 2 Dec 2010 12:38:18 -0600, "Black, Michael (IS)"
 wrote:
>If you need threadsafe just add -lpthread to your final link

I get an error when using "-lpthread", which I guess makes sense,
since I don't have the pthread library cross-compiled:

=
# bfin-linux-uclibc-gcc -v -O2 -Wall -fpic -lpthread -c -o
libsqlite3.o sqlite3.c
...
bfin-linux-uclibc-gcc: -lpthread: linker input file unused because
linking not done
=

I don't intend to access SQLite through multiple threads anyway, so
it's OK to compile it with "-DSQLITE_THREADSAFE=0".

>The .a and .so file allow your linker to only extract the routines it needs 
>and not the whole thing you would get from the .o file.

Thanks a lot for the education.

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

Re: [sqlite] EXTERNAL:Re: Cross-compiling SQLite for Blackfin+uClinux?

2010-12-02 Thread Black, Michael (IS)

If you need threadsafe just add -lpthread to your final link

As for .o and .q -- .o are "object" files which you would use to either link 
ALL of it into your application or build your .a or .so file.
The .a and .so file allow your linker to only extract the routines it needs and 
not the whole thing you would get from the .o file.

If you have file1 named foo.c
foo1()
foo2()
Compile it into .o and .a

Now main.c
main()
{
   foo1()
}

Now compile
#1 cc -o main main.c foo.o -- main will contain main(), foo1(), and foo2()
#2 cc -o main main.c foo.a -- main will contain main(), foo1()

Imagined foo.c contains hundreds of routine and all you need is foo1() -- makes 
for less memory/disk usage

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems

From: sqlite-users-boun...@sqlite.org on behalf of Gilles Ganault
Sent: Thu 12/2/2010 12:15 PM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] Cross-compiling SQLite for Blackfin+uClinux?

On Thu, 2 Dec 2010 08:06:13 -0600, "Black, Michael (IS)"
 wrote:
>You're linking your two .o into the shared library instead of sqlite3.o
>
>src/$(LIBNAME): $(OBJS)
>$(CC) $(CFLAGS) $(LIB_OPTION) $(DRIVER_LIBS) -o $@ sqlite3.o

Thanks a bunch, problem solved :-) I can now access SQLite from Lua
through LuaSQL on the Blackfin + uClinux device.

However, to get things working, I had to...

1. Recompile SQLite with "-DSQLITE_THREADSAFE=0",or get the following
error:

=
> ./lua
Lua 5.1.4  Copyright (C) 1994-2008 Lua.org, PUC-Rio
> require "luasql.sqlite3"
./lua: can't resolve symbol '_pthread_mutexattr_init'
=

For those interested, here's the command I used:

cd /usr/src/sqlite-3.7.3

#Unpacked the Blackfin toolchain in /opt
/opt/uClinux/bfin-linux-uclibc/bfin-linux-uclibc/bin/gcc
-DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION -v -O2 -Wall -fpic
-c -o libsqlite3.o sqlite3.c

2. LuaSQL's Makefile had to include the two LuaSQL object files and
the SQLite object file:

=
src/$(LIBNAME): $(OBJS)
$(CC) $(CFLAGS) $(LIB_OPTION) -o $@ $(OBJS)
/usr/src/sqlite-3.7.3/preprocessed/libsqlite3.o

$(OBJS):
$(CC) $(CFLAGS) $(INCS) $(DRIVER_INCS) -c $*.c -o $@
=

Just out of curiosity, I was wondering...

1. Why does GAS display "/../../../" when compiling SQLite?

=
...
Compiler executable checksum: ca267839d6237ba3b9dd06cf21e83f67

/opt/uClinux/bfin-linux-uclibc/lib/gcc/bfin-linux-uclibc/4.1.1/../../../../bfin-linux-uclibc/bin/as
-v -mfdpic -o libsqlite3.o /tmp/ccdSXIe3.s
GNU assembler version 2.17 (bfin-linux-uclibc) using BFD version 2.17
=

Is it because I'm not using a Makefile, and GCC has to look around to
located the assembler?

2. I'm confused about the difference between .o and .a: Why do I need
to use a .a when compiling a static library into an executable, while
I had to use the .o to compile SQLite into the LuaSQL shared library?

Thanks again.

___
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] Cross-compiling SQLite for Blackfin+uClinux?

2010-12-02 Thread Gilles Ganault

On Thu, 2 Dec 2010 08:06:13 -0600, "Black, Michael (IS)"
 wrote:
>You're linking your two .o into the shared library instead of sqlite3.o
>
>src/$(LIBNAME): $(OBJS)
>$(CC) $(CFLAGS) $(LIB_OPTION) $(DRIVER_LIBS) -o $@ sqlite3.o

Thanks a bunch, problem solved :-) I can now access SQLite from Lua
through LuaSQL on the Blackfin + uClinux device.

However, to get things working, I had to...

1. Recompile SQLite with "-DSQLITE_THREADSAFE=0",or get the following
error:

=
> ./lua
Lua 5.1.4  Copyright (C) 1994-2008 Lua.org, PUC-Rio
> require "luasql.sqlite3"
./lua: can't resolve symbol '_pthread_mutexattr_init'
=

For those interested, here's the command I used:

cd /usr/src/sqlite-3.7.3

#Unpacked the Blackfin toolchain in /opt
/opt/uClinux/bfin-linux-uclibc/bfin-linux-uclibc/bin/gcc
-DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION -v -O2 -Wall -fpic
-c -o libsqlite3.o sqlite3.c

2. LuaSQL's Makefile had to include the two LuaSQL object files and
the SQLite object file:

=
src/$(LIBNAME): $(OBJS)
$(CC) $(CFLAGS) $(LIB_OPTION) -o $@ $(OBJS)
/usr/src/sqlite-3.7.3/preprocessed/libsqlite3.o

$(OBJS):
$(CC) $(CFLAGS) $(INCS) $(DRIVER_INCS) -c $*.c -o $@
=

Just out of curiosity, I was wondering...

1. Why does GAS display "/../../../" when compiling SQLite?

=
...
Compiler executable checksum: ca267839d6237ba3b9dd06cf21e83f67

/opt/uClinux/bfin-linux-uclibc/lib/gcc/bfin-linux-uclibc/4.1.1/../../../../bfin-linux-uclibc/bin/as
-v -mfdpic -o libsqlite3.o /tmp/ccdSXIe3.s
GNU assembler version 2.17 (bfin-linux-uclibc) using BFD version 2.17
=

Is it because I'm not using a Makefile, and GCC has to look around to
located the assembler?

2. I'm confused about the difference between .o and .a: Why do I need
to use a .a when compiling a static library into an executable, while
I had to use the .o to compile SQLite into the LuaSQL shared library?

Thanks again.

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

Re: [sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

2010-12-02 Thread Philip Graham Willoughby

On 26 Nov 2010, at 10:28, Philip Graham Willoughby wrote:

> On 25 Nov 2010, at 14:06, Dan Kennedy wrote:
> 
>> On 11/25/2010 03:45 PM, Philip Graham Willoughby wrote:
>>> Hi all,
>>> 
>>> I'm noticing a new failure with SQLite 3.7.3 as compared to the previous 
>>> version I was using, 3.6.23.1.
>> 
>> Are you able to share the database and the query that causes
>> the assert() to fail?
> 
> Here is a database and the query which causes the assert to fail:
> 
> Database:   http://dl.dropbox.com/u/10341475/SQLiteAssertionFailure/Test.base
> Query SQL:  
> http://dl.dropbox.com/u/10341475/SQLiteAssertionFailure/TestQuery.sql
> 
> If for any reason you wanted to recreate the DB the SQL to do so are these:
> 
> Schema SQL: 
> http://dl.dropbox.com/u/10341475/SQLiteAssertionFailure/testSchema.sql
> Data SQL:   
> http://dl.dropbox.com/u/10341475/SQLiteAssertionFailure/TestValues.sql
> 
> My original schema is somewhat more complex so I have cut out some parts 
> which are not relevant to this problem. In attempting to create a cut-down 
> version I also tried removing players 2 and 3 from the CardsPlayers and 
> CardsPlayer[AB] tables, which caused the query to run successfully. I cannot 
> tell from that whether the problem is triggered by the presence of any values 
> in those tables or by the presence of relevant values in those tables.
> 
> With a new empty database and these SQL statements with SQLite version 3.6.12 
> (or 3.7.3 without SQLITE_DEBUG) I see this:
> 
> SQLite version 3.6.12
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .read ./testSchema.sql
> sqlite> .read ./TestValues.sql
> sqlite> .read ./TestQuery.sql
> Otterbourne Golf Course|Jane Doe|Assumed Name
> sqlite> 
> 
> With a new empty database and these SQL statements with SQLite version 3.7.3 
> (with SQLITE_DEBUG) I see this:
> 
> SQLite version 3.7.3
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .read ./testSchema.sql
> sqlite> .read ./TestValues.sql
> sqlite> .read ./TestQuery.sql
> Assertion failed: (memIsValid(&u.bq.r.aMem[i])), function sqlite3VdbeExec, 
> file sqlite3.c, line 64507.
> Abort
> 
> SQLite 3.7.3 fails in that manner when I run just the query on the 3.6.12 
> database; similarly SQLite 3.6.12 gets the right answer when it queries the 
> 3.7.3 database. I infer from this that the data stored is fine it's just a 
> problem at query-time.

I apologise for repeating my earlier message (above), but I didn't see any 
responses to it on the list so I'm beginning to wonder if it actually made it 
out of my email client.

I'd appreciate any feedback; even knowing whether it is or is not reproducible 
by others would be very helpful.

If this is a reproducible bug, is there anything else I need to do to report it?

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

Re: [sqlite] Transactions while using prepare and step

2010-12-02 Thread Simon Slavin

On 2 Dec 2010, at 4:39pm, Pavel Ivanov wrote:

> I didn't see in OP's email any information on whether he updates the
> same rows or same table as he selects, so I'd say this statement is
> too harsh in general case. While select is executing you shouldn't
> update the same rows that were returned, or could be returned
> unless/after you updated them. Other than that it's not a big deal.

Yep, fair point.  But I read his question as strongly suggesting that he's 
updating the rows that are returned.  I could be wrong, though.

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

Re: [sqlite] WAL file size

2010-12-02 Thread Yoni Londner

Hi,

I meant to run the checkpoint in another thread (see my attached example 
program), but it can be even from another process.
What happens when it crashes? well, the programmer have to decide how he 
handle checkpoints. he can use auto_checkpoint, he can stop 
auto_checkpoint and do checkpoint in the main thread or in another 
thread, and he can do it in another process. but if this another process 
crash, that is the programmer responsibility to handle (e.g run it again).

The initial prolem I approched is a system that constantly read/write 
from/to the DB, and want to do it fast, so we can't use auto_checkpoint 
or run checkpoint periodically in the main thread.
What we do want is to run checkpoint in the background, but then the WAL 
grow forever...

Yoni.

On 2/12/2010 6:29 PM, Pavel Ivanov wrote:
> You seem to use such term as "background checkpointing". What's that?
> Who runs this background process and what happens when it crashes
> (when all other readers/writers are still working)?
>
>
> Pavel
>
> On Thu, Dec 2, 2010 at 11:04 AM, Yoni Londner  wrote:
>> Hi,
>>
>> I will start out by stating that I am not deeply familiar with the
>> sqlite&  WAL file layout, but I will try anyway, from my current
>> understanding:
>>
>> The general problem that needs to be solved is to allow SQLITE to be
>> constantly used over time (with no 'idle' time where no sql operations
>> are done, and it is not inside any transaction).
>>
>> The current WAL design does not allow this, since if there are all the
>> time open read transactions and/or write transactions, the WAL will
>> continue to grow forever.
>> I copy/paste below a tiny C program that re-creates this with write
>> transactions.
>>
>> Remember also that in the WAL file we 'gather' up a lot of work. If we
>> do it in the background, this work-load can be smoothed up to run in
>> parallel to the regular system work, but if we must make the SQL 'idle'
>> (close all transactions) in order to execute the checkpoint, on a heavy
>> load system this can mean halting the system for a long period of time
>> (in our case, typically for 30 seconds!).
>>
>> This needs to be solved by two "features" to be added to WAL:
>>
>> 1) 'incremental' checkpoint.
>>
>> 2) WAL file recycling (this item can also be solved by two WAL files,
>> but I think its better to make the WAL file format a little bit more
>> complex than start having to handle in the code management of multiple
>> files).
>>
>> Incremental checkpointing means that checkpointing can be done up until
>> the first open transaction (mxFrame). This means both copying the WAL
>> pages up-until mxFrame of the first open transaction to the DB file, and
>> then MARKING those frames as 'DONE' (I will talk later on how to do the
>> 'DONE' marking).
>>
>> This means that from that point onwards - accessing those pages will be
>> to the DB file, not the WAL file.
>>
>> WAL recycling will be done by writing pages to the beginning of the WAL
>> file when a certain amount of pages from the beginning of the WAL file
>> are 'checkpointed' (marked as DONE). This can also happen in the middle
>> of a transaction.
>>
>> Example:
>>
>> Legend:
>> H - header.
>> 1, 2, 3.. - page of transaction 1, 2, 3..
>> C - commit marker.
>> BOF1: beginning of WAL-1. EOF1: end of WAL-1
>> BOF2: beginning of WAL-2. EOF2: end of WAL-2
>> P: 64K of padding (junk data)
>>
>> WAL file with transactions 1 2 and 3 committed, and transaction 4 open:
>> H 1 1 1C 2 2 2 2C 3 3 3 3C 4 4 4
>>+-- BOF1 +--- EOF1
>>
>> We continue to add to transaction 4:
>> H 1 1 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
>>+-- BOF1 +--- EOF1
>>
>> In the meantime, we checkpointed transactions 1 and 2, because there is
>> a read transaction working on transaction 3:
>> H 1 1 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
>>+-- BOF1 +--- EOF1
>>+- checkpointed up to here
>>
>> No we decided we want to recycle. Since there is no read transaction
>> open on transaction 1 and 2 (cannot be, since if you need a page from
>> transaction 1, you will find it in the DB), we can reuse them:
>> H 4 1 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
>>+-- BOF2+-- BOF1 +--- EOF1
>>+-- EOF2+ checkpointed up to here
>>+-- the 6th page of transaction 4
>>
>> And now we close transaction 4:
>> H 4 4C 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
>>| +-- EOF2   +-- BOF1 +--- EOF1
>>+--- BOF2+ checkpointed up to here
>>
>> Lets open transaction 5, and write faster than the background checkpointing:
>>
>> H 4 4C 5  5 5 5 5  3 3 3 3C 4 4 4 4 4
>>| |  +-- BOF1 +--- EOF1
>>+--- BOF2 |  + checkpointed up to here
>>  +-- EOF2
>>
>> So we need more place for 5, we will write it at the end:
>> H 4 4C  5 5 5 5 5  3 3 3 3C 4 4 4 4 4 5 5 5 5C
>>| |  || | +-- EOF3
>>| |  |

Re: [sqlite] Transactions while using prepare and step

2010-12-02 Thread Pavel Ivanov

> Makes no difference.  Doing an UPDATE inside your SELECT violates the rule no 
> matter how you structure your transaction.  A SELECT is a single operation 
> and you can't do anything else until it is finished.

I didn't see in OP's email any information on whether he updates the
same rows or same table as he selects, so I'd say this statement is
too harsh in general case. While select is executing you shouldn't
update the same rows that were returned, or could be returned
unless/after you updated them. Other than that it's not a big deal.

Answering original question depending on particular select/update
statements SQLite can behave the same way when you wrap the whole loop
in transaction and when you don't. But if you use "BEGIN TRANSACTION"
or don't use transactions at all be ready that updates can return
SQLITE_BUSY in such situation because a deadlock can be detected. To
avoid such situation start transaction before the loop using "BEGIN
IMMEDIATE".


Pavel

On Thu, Dec 2, 2010 at 11:12 AM, Simon Slavin  wrote:
>
> On 2 Dec 2010, at 3:44pm, cricketfan wrote:
>
>> Hello I have a basic question and would be glad if someone can answer it. I
>> understand that if we have "BEGIN TRANSACTION" and "END TRANSACTION" then
>> the transaction(s) within that block would be committed, number of
>> transactions could be 10,100,1000 or 1. . Otherwise, SQLITE by
>> default commits for every single insert,update,delete.
>
> So far, very good.
>
>> I have a prepare statement(using limit clause so will get only 10 rows back)
>> followed by step(during which I use an update clause) so how would the
>> transaction concept behave in this case?
>
> It doesn't.  You will get some results from SQLite by doing
>
> SELECT
> step to record 1
> UPDATE record 1
> step to record 2
> UPDATE record 2
> step to record 3
> UPDATE record 3
> ...
>
> but it means nothing in terms of SQL and precisely what it does may change 
> between SQL engines and different versions of SQL engines.  I might even 
> argue that SQL engines should produce an error message when people try to do 
> things like this.  There are two possible solutions:
>
> A) The UPDATE command has a WHERE clause so put the SELECT criteria in that 
> WHERE clause.  This will enable you to roll both commands into one single 
> UPDATE command.  This is the simplest solution, and will execute the most 
> quickly and efficiently.
>
> B) If that solution is not available because the calculations cannot be 
> completed inside SQL, do the SELECT first, read the entire list of results 
> into an array (list ?) in your chosen programming language, and only when the 
> SELECT is finished, start looking through the results and doing UPDATE 
> commands.  You can, of course, issue a BEGIN TRANSACTION before the SELECT, 
> and an END TRANSACTION after the last UPDATE.
>
> Both (A) and (B) are sound procedures and their results are clearly defined 
> and will give the same results across all SQL engines and all versions of SQL 
> engines (unless they're buggy !).
>
>> If I wrap the loop I use for
>> stepping with a begin transaction(followed by an end transaction  when the
>> loop ends) would it be sufficient?
>
> Makes no difference.  Doing an UPDATE inside your SELECT violates the rule no 
> matter how you structure your transaction.  A SELECT is a single operation 
> and you can't do anything else until it is finished.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL file size

2010-12-02 Thread Pavel Ivanov

You seem to use such term as "background checkpointing". What's that?
Who runs this background process and what happens when it crashes
(when all other readers/writers are still working)?


Pavel

On Thu, Dec 2, 2010 at 11:04 AM, Yoni Londner  wrote:
> Hi,
>
> I will start out by stating that I am not deeply familiar with the
> sqlite & WAL file layout, but I will try anyway, from my current
> understanding:
>
> The general problem that needs to be solved is to allow SQLITE to be
> constantly used over time (with no 'idle' time where no sql operations
> are done, and it is not inside any transaction).
>
> The current WAL design does not allow this, since if there are all the
> time open read transactions and/or write transactions, the WAL will
> continue to grow forever.
> I copy/paste below a tiny C program that re-creates this with write
> transactions.
>
> Remember also that in the WAL file we 'gather' up a lot of work. If we
> do it in the background, this work-load can be smoothed up to run in
> parallel to the regular system work, but if we must make the SQL 'idle'
> (close all transactions) in order to execute the checkpoint, on a heavy
> load system this can mean halting the system for a long period of time
> (in our case, typically for 30 seconds!).
>
> This needs to be solved by two "features" to be added to WAL:
>
> 1) 'incremental' checkpoint.
>
> 2) WAL file recycling (this item can also be solved by two WAL files,
> but I think its better to make the WAL file format a little bit more
> complex than start having to handle in the code management of multiple
> files).
>
> Incremental checkpointing means that checkpointing can be done up until
> the first open transaction (mxFrame). This means both copying the WAL
> pages up-until mxFrame of the first open transaction to the DB file, and
> then MARKING those frames as 'DONE' (I will talk later on how to do the
> 'DONE' marking).
>
> This means that from that point onwards - accessing those pages will be
> to the DB file, not the WAL file.
>
> WAL recycling will be done by writing pages to the beginning of the WAL
> file when a certain amount of pages from the beginning of the WAL file
> are 'checkpointed' (marked as DONE). This can also happen in the middle
> of a transaction.
>
> Example:
>
> Legend:
> H - header.
> 1, 2, 3.. - page of transaction 1, 2, 3..
> C - commit marker.
> BOF1: beginning of WAL-1. EOF1: end of WAL-1
> BOF2: beginning of WAL-2. EOF2: end of WAL-2
> P: 64K of padding (junk data)
>
> WAL file with transactions 1 2 and 3 committed, and transaction 4 open:
> H 1 1 1C 2 2 2 2C 3 3 3 3C 4 4 4
>   +-- BOF1                     +--- EOF1
>
> We continue to add to transaction 4:
> H 1 1 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
>   +-- BOF1                         +--- EOF1
>
> In the meantime, we checkpointed transactions 1 and 2, because there is
> a read transaction working on transaction 3:
> H 1 1 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
>                   +-- BOF1         +--- EOF1
>                   +- checkpointed up to here
>
> No we decided we want to recycle. Since there is no read transaction
> open on transaction 1 and 2 (cannot be, since if you need a page from
> transaction 1, you will find it in the DB), we can reuse them:
> H 4 1 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
>   +-- BOF2        +-- BOF1         +--- EOF1
>   +-- EOF2        + checkpointed up to here
>   +-- the 6th page of transaction 4
>
> And now we close transaction 4:
> H 4 4C 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
>   | +-- EOF2       +-- BOF1         +--- EOF1
>   +--- BOF2        + checkpointed up to here
>
> Lets open transaction 5, and write faster than the background checkpointing:
>
> H 4 4C 5  5 5 5 5  3 3 3 3C 4 4 4 4 4
>   |             |  +-- BOF1         +--- EOF1
>   +--- BOF2     |  + checkpointed up to here
>                 +-- EOF2
>
> So we need more place for 5, we will write it at the end:
> H 4 4C  5 5 5 5 5  3 3 3 3C 4 4 4 4 4 5 5 5 5C
>   |             |  |                | |     +-- EOF3
>   |             |  |                | +--- BOF3
>   |             |  +-- BOF1         +--- EOF1
>   +--- BOF2     |  + checkpointed up to here
>                 +-- EOF2
>
> Ok. This is the most complicated 'mess up' of this WAL file possible. It
> cannot get 'messier' than this, since we recyclye ONLY when there is a
> minimum of N (configurable) amount of checkpointed pages at the
> beginning of the WAL file AND if there are no 'gaps' in the not-yet
> checkpointed pages.
> The first condition is mainly for efficiency or large workloads (since
> recycling has its costs). This second condition is to prevent the WAL
> files layout to have multiple recycling points in it (prevents it from
> getting 'messed up'). A wal file can only have at MAX one recycle point
> at one point in file, and MAX of 2 empty gaps.
> If we continue the example above, this can happen when we checkpoint
> transactions 3 and 4 but not transaction 5. So we have a gap at the
> begin

Re: [sqlite] Transactions while using prepare and step

2010-12-02 Thread Simon Slavin

On 2 Dec 2010, at 3:44pm, cricketfan wrote:

> Hello I have a basic question and would be glad if someone can answer it. I
> understand that if we have "BEGIN TRANSACTION" and "END TRANSACTION" then
> the transaction(s) within that block would be committed, number of
> transactions could be 10,100,1000 or 1. . Otherwise, SQLITE by
> default commits for every single insert,update,delete.

So far, very good.

> I have a prepare statement(using limit clause so will get only 10 rows back)
> followed by step(during which I use an update clause) so how would the
> transaction concept behave in this case?

It doesn't.  You will get some results from SQLite by doing

SELECT
step to record 1
UPDATE record 1
step to record 2
UPDATE record 2
step to record 3
UPDATE record 3
...

but it means nothing in terms of SQL and precisely what it does may change 
between SQL engines and different versions of SQL engines.  I might even argue 
that SQL engines should produce an error message when people try to do things 
like this.  There are two possible solutions:

A) The UPDATE command has a WHERE clause so put the SELECT criteria in that 
WHERE clause.  This will enable you to roll both commands into one single 
UPDATE command.  This is the simplest solution, and will execute the most 
quickly and efficiently.

B) If that solution is not available because the calculations cannot be 
completed inside SQL, do the SELECT first, read the entire list of results into 
an array (list ?) in your chosen programming language, and only when the SELECT 
is finished, start looking through the results and doing UPDATE commands.  You 
can, of course, issue a BEGIN TRANSACTION before the SELECT, and an END 
TRANSACTION after the last UPDATE.

Both (A) and (B) are sound procedures and their results are clearly defined and 
will give the same results across all SQL engines and all versions of SQL 
engines (unless they're buggy !).

> If I wrap the loop I use for
> stepping with a begin transaction(followed by an end transaction  when the
> loop ends) would it be sufficient?

Makes no difference.  Doing an UPDATE inside your SELECT violates the rule no 
matter how you structure your transaction.  A SELECT is a single operation and 
you can't do anything else until it is finished.

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

Re: [sqlite] WAL file size

2010-12-02 Thread Yoni Londner

Hi,

I will start out by stating that I am not deeply familiar with the 
sqlite & WAL file layout, but I will try anyway, from my current 
understanding:

The general problem that needs to be solved is to allow SQLITE to be 
constantly used over time (with no 'idle' time where no sql operations 
are done, and it is not inside any transaction).

The current WAL design does not allow this, since if there are all the 
time open read transactions and/or write transactions, the WAL will 
continue to grow forever.
I copy/paste below a tiny C program that re-creates this with write 
transactions.

Remember also that in the WAL file we 'gather' up a lot of work. If we 
do it in the background, this work-load can be smoothed up to run in 
parallel to the regular system work, but if we must make the SQL 'idle' 
(close all transactions) in order to execute the checkpoint, on a heavy 
load system this can mean halting the system for a long period of time 
(in our case, typically for 30 seconds!).

This needs to be solved by two "features" to be added to WAL:

1) 'incremental' checkpoint.

2) WAL file recycling (this item can also be solved by two WAL files, 
but I think its better to make the WAL file format a little bit more 
complex than start having to handle in the code management of multiple 
files).

Incremental checkpointing means that checkpointing can be done up until 
the first open transaction (mxFrame). This means both copying the WAL 
pages up-until mxFrame of the first open transaction to the DB file, and 
then MARKING those frames as 'DONE' (I will talk later on how to do the 
'DONE' marking).

This means that from that point onwards - accessing those pages will be 
to the DB file, not the WAL file.

WAL recycling will be done by writing pages to the beginning of the WAL 
file when a certain amount of pages from the beginning of the WAL file 
are 'checkpointed' (marked as DONE). This can also happen in the middle 
of a transaction.

Example:

Legend:
H - header.
1, 2, 3.. - page of transaction 1, 2, 3..
C - commit marker.
BOF1: beginning of WAL-1. EOF1: end of WAL-1
BOF2: beginning of WAL-2. EOF2: end of WAL-2
P: 64K of padding (junk data)

WAL file with transactions 1 2 and 3 committed, and transaction 4 open:
H 1 1 1C 2 2 2 2C 3 3 3 3C 4 4 4
   +-- BOF1 +--- EOF1

We continue to add to transaction 4:
H 1 1 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
   +-- BOF1 +--- EOF1

In the meantime, we checkpointed transactions 1 and 2, because there is 
a read transaction working on transaction 3:
H 1 1 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
   +-- BOF1 +--- EOF1
   +- checkpointed up to here

No we decided we want to recycle. Since there is no read transaction 
open on transaction 1 and 2 (cannot be, since if you need a page from 
transaction 1, you will find it in the DB), we can reuse them:
H 4 1 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
   +-- BOF2+-- BOF1 +--- EOF1
   +-- EOF2+ checkpointed up to here
   +-- the 6th page of transaction 4

And now we close transaction 4:
H 4 4C 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
   | +-- EOF2   +-- BOF1 +--- EOF1
   +--- BOF2+ checkpointed up to here

Lets open transaction 5, and write faster than the background checkpointing:

H 4 4C 5  5 5 5 5  3 3 3 3C 4 4 4 4 4
   | |  +-- BOF1 +--- EOF1
   +--- BOF2 |  + checkpointed up to here
 +-- EOF2

So we need more place for 5, we will write it at the end:
H 4 4C  5 5 5 5 5  3 3 3 3C 4 4 4 4 4 5 5 5 5C
   | |  || | +-- EOF3
   | |  || +--- BOF3
   | |  +-- BOF1 +--- EOF1
   +--- BOF2 |  + checkpointed up to here
 +-- EOF2

Ok. This is the most complicated 'mess up' of this WAL file possible. It 
cannot get 'messier' than this, since we recyclye ONLY when there is a 
minimum of N (configurable) amount of checkpointed pages at the 
beginning of the WAL file AND if there are no 'gaps' in the not-yet 
checkpointed pages.
The first condition is mainly for efficiency or large workloads (since 
recycling has its costs). This second condition is to prevent the WAL 
files layout to have multiple recycling points in it (prevents it from 
getting 'messed up'). A wal file can only have at MAX one recycle point 
at one point in file, and MAX of 2 empty gaps.
If we continue the example above, this can happen when we checkpoint 
transactions 3 and 4 but not transaction 5. So we have a gap at the 
beginning (end of transaction 4) and in the middle (transaction 3 and 
beginning of transaction 4)

H 4 4C  5 5 5 5 5  3 3 3 3C 4 4 4 4 4 5 5 5 5C
 +-- BOF1| +- BOF2|
 +--- EOF1+--- EOF2


How to mark as DONE:

We actually don't mark a frame as DONE.
We give each frame a sequence number, and we write on each commit fra

[sqlite] Transactions while using prepare and step

2010-12-02 Thread cricketfan


Hello I have a basic question and would be glad if someone can answer it. I
understand that if we have "BEGIN TRANSACTION" and "END TRANSACTION" then
the transaction(s) within that block would be committed, number of
transactions could be 10,100,1000 or 1. . Otherwise, SQLITE by
default commits for every single insert,update,delete.
I have a prepare statement(using limit clause so will get only 10 rows back)
followed by step(during which I use an update clause) so how would the
transaction concept behave in this case? If I wrap the loop I use for
stepping with a begin transaction(followed by an end transaction  when the
loop ends) would it be sufficient?
-- 
View this message in context: 
http://old.nabble.com/Transactions-while-using-prepare-and-step-tp30359695p30359695.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Cross-compiling SQLite for Blackfin+uClinux?

2010-12-02 Thread Black, Michael (IS)

Let me do this again without this confanged EXTERNAL thing our email server 
puts on everything...

You're linking your two .o into the shared library instead of sqlite3.o

1. 
2. 
OBJS=src/luasql.o src/ls_$T.o
3. 
4. 

5. 
src/$(LIBNAME): $(OBJS)
6. 
$(CC) $(CFLAGS) $(LIB_OPTION) $(DRIVER_LIBS) -o $@ $(OBJS)
7. 

Change to

1. 
2. 
src/$(LIBNAME): $(OBJS)
3. 
4. 
$(CC) $(CFLAGS) $(LIB_OPTION) $(DRIVER_LIBS) -o $@ sqlite3.o

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems

From: sqlite-users-boun...@sqlite.org on behalf of Gilles Ganault
Sent: Thu 12/2/2010 7:24 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] Cross-compiling SQLite for Blackfin+uClinux?

On Wed, 1 Dec 2010 11:10:54 -0600, "Black, Michael (IS)"
 wrote:

>I don't think you need to do the LDFLAGS thing...
>
>gcc -O2 -fpic -shared -Wl,-soname,sqlite3.so -o sqlite3.so sqlite3.c
>
>Works fine for me.  Though I'm not using the Blackfin compiler...

Thanks Michael for the tip. The compiling seems to work... but the
final sqlite3.so is not usable: "./lua: can't resolve symbol
'_sqlite3_open'"

Here's what I did:

http://pastebin.com/NeW7vW62

The LuaSQL for Sqlite3 with SQLite statically compiled is only 38.172
bytes, and when I try to "require "luasql.sqlite3"", I get "can't
resolve symbol '_sqlite3_open'".

Obviously, I'm doing something wrong here.

Any tip much appreciated.

___
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] EXTERNAL:Re: Cross-compiling SQLite for Blackfin+uClinux?

2010-12-02 Thread Black, Michael (IS)

You're linking your two .o into the shared library instead of sqlite3.o

1.  
2.  
OBJS=src/luasql.o src/ls_$T.o
3.  
4.  

5.  
src/$(LIBNAME): $(OBJS)
6.  
$(CC) $(CFLAGS) $(LIB_OPTION) $(DRIVER_LIBS) -o $@ $(OBJS)
7.  

Change to

1.  
2.  
src/$(LIBNAME): $(OBJS)
3.  
4.  
$(CC) $(CFLAGS) $(LIB_OPTION) $(DRIVER_LIBS) -o $@ sqlite3.o

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems

From: sqlite-users-boun...@sqlite.org on behalf of Gilles Ganault
Sent: Thu 12/2/2010 7:24 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] Cross-compiling SQLite for Blackfin+uClinux?

On Wed, 1 Dec 2010 11:10:54 -0600, "Black, Michael (IS)"
 wrote:

>I don't think you need to do the LDFLAGS thing...
>
>gcc -O2 -fpic -shared -Wl,-soname,sqlite3.so -o sqlite3.so sqlite3.c
>
>Works fine for me.  Though I'm not using the Blackfin compiler...

Thanks Michael for the tip. The compiling seems to work... but the
final sqlite3.so is not usable: "./lua: can't resolve symbol
'_sqlite3_open'"

Here's what I did:

http://pastebin.com/NeW7vW62

The LuaSQL for Sqlite3 with SQLite statically compiled is only 38.172
bytes, and when I try to "require "luasql.sqlite3"", I get "can't
resolve symbol '_sqlite3_open'".

Obviously, I'm doing something wrong here.

Any tip much appreciated.

___
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] Cross-compiling SQLite for Blackfin+uClinux?

2010-12-02 Thread Gilles Ganault

At 17:47 01/12/2010, Mike Frysinger wrote:
>why dont you consult the Blackfin uclinux-dist to see how it's doing it ?

I did, but it's the same code as from www.sqlite.org so doesn't 
contain infos on what parameters to pass "configure" to compile for 
the Blackfin.

After cd'ing to /usr/src/uClinux-dist/lib/sqlite/sqlite-3.6.14.1/, I 
tried the following, which seems to have successfully compile SQLite3 
as a static library:

./configure 
CC="/opt/uClinux/bfin-linux-uclibc/bfin-linux-uclibc/bin/gcc" 
CFLAGS="-v -O2 -Wall -fpic" --host=bfin

make

.libs/libsqlite3.a is 387.668 bytes

Thank you. 

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

Re: [sqlite] Cross-compiling SQLite for Blackfin+uClinux?

2010-12-02 Thread Gilles Ganault

On Wed, 1 Dec 2010 11:10:54 -0600, "Black, Michael (IS)"
 wrote:

>I don't think you need to do the LDFLAGS thing...
> 
>gcc -O2 -fpic -shared -Wl,-soname,sqlite3.so -o sqlite3.so sqlite3.c
> 
>Works fine for me.  Though I'm not using the Blackfin compiler...

Thanks Michael for the tip. The compiling seems to work... but the
final sqlite3.so is not usable: "./lua: can't resolve symbol
'_sqlite3_open'"

Here's what I did:

http://pastebin.com/NeW7vW62

The LuaSQL for Sqlite3 with SQLite statically compiled is only 38.172
bytes, and when I try to "require "luasql.sqlite3"", I get "can't
resolve symbol '_sqlite3_open'".

Obviously, I'm doing something wrong here.

Any tip much appreciated.

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

Re: [sqlite] Database corrupted

2010-12-02 Thread Black, Michael (IS)

And your version is.
 
Is your app a stand-alone you can share?  If you 've discovered such a bug the 
community would be MUCH appreciative if you could share so it can be fixed (or 
at least identified to allay concerns we all may have over thread safety).
 
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Marco Era
Sent: Thu 12/2/2010 4:37 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] Database corrupted



Hello,
I'm doing some tests to get the best out of the threading models for sqlite. 
All tests are done on a multicore processor, windows XP; sqlite is working in 
WAL mode.

I've got a database image corrupted when using SQLITE_THREADSAFE=2 and two 
threads, each opening a private connection to the same database. sqlite3* 
pointers are not shared, each thread has his own.

Compiling with SQLITE_THREADSAFE=1 seems to work well, but I will test it more.

Am is missing something?
Do I have to synchronize the threads?
Thanks in advance.


 
___
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] Database corrupted

2010-12-02 Thread Marco Era

Hello,
I'm doing some tests to get the best out of the threading models for sqlite. 
All tests are done on a multicore processor, windows XP; sqlite is working in 
WAL mode.

I've got a database image corrupted when using SQLITE_THREADSAFE=2 and two 
threads, each opening a private connection to the same database. sqlite3* 
pointers are not shared, each thread has his own. 

Compiling with SQLITE_THREADSAFE=1 seems to work well, but I will test it more.

Am is missing something?
Do I have to synchronize the threads?
Thanks in advance.


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

Re: [sqlite] WAL file size

Re: [sqlite] Android database corruption

Re: [sqlite] Android database corruption

Re: [sqlite] Android database corruption

Re: [sqlite] Cross-compiling SQLite for Blackfin+uClinux?

Re: [sqlite] EXTERNAL:Re: Cross-compiling SQLite forBlackfin+uClinux?

Re: [sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

Re: [sqlite] EXTERNAL:Re: Cross-compiling SQLite forBlackfin+uClinux?

Re: [sqlite] EXTERNAL:Re: Cross-compiling SQLite forBlackfin+uClinux?

Re: [sqlite] Transactions while using prepare and step

Re: [sqlite] EXTERNAL:Re: Cross-compiling SQLite forBlackfin+uClinux?

Re: [sqlite] Transactions while using prepare and step

Re: [sqlite] Transactions while using prepare and step

Re: [sqlite] EXTERNAL:Re: Cross-compiling SQLite for Blackfin+uClinux?

Re: [sqlite] EXTERNAL:Re: Cross-compiling SQLite for Blackfin+uClinux?

Re: [sqlite] Cross-compiling SQLite for Blackfin+uClinux?

Re: [sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

Re: [sqlite] Transactions while using prepare and step

Re: [sqlite] WAL file size

Re: [sqlite] Transactions while using prepare and step

Re: [sqlite] WAL file size

Re: [sqlite] Transactions while using prepare and step

Re: [sqlite] WAL file size

[sqlite] Transactions while using prepare and step

Re: [sqlite] Cross-compiling SQLite for Blackfin+uClinux?

Re: [sqlite] EXTERNAL:Re: Cross-compiling SQLite for Blackfin+uClinux?

Re: [sqlite] Cross-compiling SQLite for Blackfin+uClinux?

Re: [sqlite] Cross-compiling SQLite for Blackfin+uClinux?

Re: [sqlite] Database corrupted

[sqlite] Database corrupted

30 matches

Site Navigation

Mail list logo

Footer information