[sqlite] transaction w/ large journal fails

[Preston Z]

I am running into a situation where on linux (fc5 and debian3) I have
a transaction that is consistently failing using sqlite 3.3.7. The
same transaction completes on windows every time.

I have a large database (1.2gb) that i run a transaction on. In
windows the journal file for this transaction reaches about the same
size as the datafile. In linux the journal stops growing around 1gb
and the transaction fails.


If i run this same transaction on a smaller database (same schema less
data) then the transaction will complete. Unfortunately this isn't a
realistic option for me.

Has anybody else run into anything similar? Is there anything i need
to do in linux that i don't have to do in windows?


Any advise?
thanks
Preston

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] spanish chars

[Preston Z]

I haven't worked with internationalization much before so please
forgive me if this is a stupid question or not related to sqlite.

I have an charset problem using sqlite 3.2.2, I run into the following issue:

we have started adding spanish transaltions to our database. The app
that loads the data seems fine, i can look at the data through the
command line tool and the data looks ok.

the problem is when i get the data through the C api with a call to
sqlite3_get_table, the chars are missing/distorted.

would prepare/step work any differently? do i need to call the UTF-16 calls?

Thanks,
Preston

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Which VC6 options to use for fastest sqlite3?

[Preston Z]

since you have /MT i am assuming you will need /D "THREADSAFE". If you are 
used to MSVC and want to try another compiler i would suggest grabbing 
http://www.codeblocks.org, it is an IDE and you can import you MSVC project 
into it and compile with the MinGW/gcc compiler, which it will set up for 
you. Comming from a MSVC background i liked it.




From: Michael Sizaki <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
Subject: [sqlite] Which VC6 options to use for fastest sqlite3?
Date: Mon, 09 Jan 2006 21:26:53 +0100

Hi,

which VC6 options to use for a very fast sqlite dll?
Any /D except NDEBUG?
  /nologo /MT /W3 /GX /O2 /D "WIN32" /D "NDEBUG" /D "_WINDOWS" /D "_MBCS" 
/D "_USRDLL"


Is there another C compiler that generates faster executables on
windows?

Michael

RE: [sqlite] How dangerous is PRAGMA Synchronous OFF?

[Preston Z]

If the power never goes out and no programs ever crash on you system then 
Synchronous = OFF is for you, but the rest of the world might still want it 
ON. Really it sounds like the thing you need to worry about most is the 
unexpected termination of your program. If you aren't worried about that...

The bottom line is how important is the speed VS Data?

Your program must do a lot of data modification for you to see the kind of 
improvements that you sound like you are getting by turning off the Sync. 
Are you using transactions where you can? Transactions can speed up things 
quite a bit if you have more than one row of data that you are modifying at 
a time... personally i would try transactions to speed things up before i 
did something as drastic as turning off the sync.


As for detecting a corrupt database, it won't tell you on opening it that it 
is corrupt... it will even execute statements till it hits a piece of the db 
that is corrupt (at least the corruption that i have seen, which was from a 
bad disk, so might not be the same). There is a symptom of a corrupt 
database that i think is almost a sure thing.. run vacuum or dump the 
database from the command line tool. If these run OK then you are probably 
fine.


Anybody have a better way to tell if a db is garbage?

I hope i answered at least part of your question...
--Preston



From: "Dinsmore, Jeff" <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: 
Subject: [sqlite] How dangerous is PRAGMA Synchronous OFF?
Date: Wed, 16 Nov 2005 08:49:10 -0600

I have a little engine doing HL7 messaging using SQLite3 from Tcl
(Windows Server 2003 with a local attached RAID 5 for database).

I'm looking for some speed improvements, so yesterday I experimented
with PRAGMA synchronous = OFF. Holy Cats - it's WAAAY faster - like
night and day.

So, I'd like to use synchronous = OFF, but I'd like to understand a
little more about the possible side effects.

I understand that if the OS crashes or if I lose power to the server
it's possible that the SQLite database could be corrupted assuming that
the failure happens at a time after  SQLite has handed off a database
write to the OS but before the OS has committed the write to disk.

My server is UPS'd and the server has dual power supplies, so power
should be pretty stable. The Windows OS also seems to be pretty stable
and the RAID has battery protected cache. So I'm fairly comfortable with
my OS, hardware and power reliability.

The Big Question: What evidence will I see from SQLite that a database
file is corrupt? Will it fail to open and return an error to that
effect? Or, will it (possibly) open the database and then fail somewhere
down the line when it hits the corrupted area of the database?

I have never experienced any SQLite database corruption. Does anyone
have a feel for how common database corruption is in the real world?

Thanks,

Jeff Dinsmore
IT - Interfaces
Ridgeview Medical Center
[EMAIL PROTECTED]
952.442.2191 x6592



Ridgeview Medical Center Confidentiality Notice: This email message, 
including any attachments, is for the sole use of the intended recipient(s) 
and may contain confidential and privileged information. Any unauthorized 
review, use, disclosure or distribution is prohibited. If you are not the 
intended recipient, please contact the sender by reply email and destroy 
all copies of the original message.

RE: [sqlite] BEGIN TRANSACTION name

[Preston Z]

From: "Marcus Welz" <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: 
Subject: RE: [sqlite] BEGIN TRANSACTION name
Date: Thu, 3 Nov 2005 12:46:39 -0500

I actually have a question regarding nested transactions. Maybe I'm missing
something, but isn't it true that the outermost transaction must be
committed successfully in order for any of the nested transactions to also
be committed successfully?

For example if I have:

BEGIN TRANSACTION a;

BEGIN TRANSACTION b;

[some sql code]

COMMIT;

[some sql code that errors out]

ROLLBACK;


Wouldn't that mean that, even though transaction "b" was successful, since
the whole thing was embedded into transaction "a" it would fail anyway?

So, couldn't SQLite simply internally keep track of nested transaction
through a counter that is incremented every time "BEGIN TRANSACTION" is
encountered and decremented whenever "COMMIT" is encountered, and only 
truly

COMMIT when that counter has reached 0 again?

(That's what I implemented for an in-house abstraction layer.)

Just wondering if I'm missing something critical.


Thanks.


You are exactly right on all points. In fact, as I understand it, this is 
actually what MS SQL Server does internally.


IMHO knowing that there isn't any real benefit to nested transactions then a 
developer should normally be able to design code that doesn't use nested 
transactions.


--Preston

RE: [sqlite] Request for comment: Proposed SQLite API changes

[Preston Z]

I would be in favor of both changes.

Any other hints about what is in the works for 3.3.0? I haven't upgraded 
past 3.2.2 because the optimizer changes made my application hang and i 
haven't had a chance to re-write & test all of my queries. Any changes in 
3.3.0 that would allow me to upgrade to it without re-writing queries? (I 
can hope can't I?)


Thanks
Preston



As currently implemented, when an error occurs during
sqlite3_step(), the function returns SQLITE_ERROR.  Then
you have to call either sqlite3_reset() or sqlite3_finalize()
to find the actual error code.  Suppose this where to
change in version 3.3.0 so that the actual error code
was returned by sqlite3_step().  That would mean that
moving from version 3.2.7 to 3.3.0 might involve some
minor code changes. The API would not be 100% backwards
compatible.  But the API would be cleaner.

What does the community think about such a change?

Another proposal:  Suppose that when creating an
sqlite3_stmt using sqlite3_prepare, the original SQL
text was stored in the sqlite3_stmt.  Then when a
schema change occurred, the statement was automatically
recompiled and rebound.  There would no more SQLITE_SCHEMA
errors.  But sqlite3_stmts would use a little more
memory.  And sqlite3_step might take a little longer
to initialize sometimes if it found it needed to rerun
the parser.

RE: [sqlite] CHECK constraints

[Preston Z]

MS SQL 2000 wil alsol insert (5, NULL)




From: "Marcus Welz" <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: 
Subject: RE: [sqlite] CHECK constraints
Date: Wed, 2 Nov 2005 19:24:09 -0500

PostgreSQL 8.0 will happily insert (5, NULL).


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 02, 2005 6:31 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] CHECK constraints

In a CHECK constraint, if the expression is NULL (neither true
nor false) does the constraint fail?

Example:

  CREATE TABLE ex1(
x INTEGER,
y REAL,
CHECK( x

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Preston Z]

There is an ongoing debate on this subject at Lambda the Ultimate. One
approach that is sound is to introduce a new static type, called
DYNAMIC, that permits any value. Summarizing and seconding Dennis
Cote's suggestion, perhaps columns that have no type declared, or that
are declared DYNAMIC, behave as SQLite does today, and columns that
have static declarations behave as if they are statically typed.


I like that suggestion, but I am sure that this is beyond the scope of the 
question.


Truth be told, manifest typing has never been a “selling point” (if I can 
use that term for free software) for me, rather it has been something to be 
aware of / work around. I know at one point there was talk of a “strict” 
affinity mode for sqlite (at least I saw something that indicated that 
somewhere on the website: http://www.sqlite.org/datatype3.html bullet point 
6.) I personally would love to see a strict affinity mode db with the 
inclusion of a dynamically typed column that can still be used.



BTW: a dynamically typed column is actually included in MSSQL 2005.


ALso is there a "roadmap" type document that talks about the future of 
sqlite?


--Preston