Re: [sqlite] Database corruption on Linux ext3

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/13/2010 05:30 PM, Jim Wilcoxson wrote: > I don't think this would work, because the problem described is that the > writes aren't making it to disk. If pages don't make it to disk, the old > pages will be present, with the old, and valid

Re: [sqlite] WAL: no longer able to use read-only databases?

> > > In testing the latest SQLite snapshot with WAL enabled, it seems that > > there's no way to use a database in a read-only location. > > > > Documentation on the developers' current view of this issue can be found > here: > > > In my opinion it's ok, WAL already has special conditions on

Re: [sqlite] Database corruption on Linux ext3

On Tue, Jul 13, 2010 at 10:03 PM, Scott Hess wrote: > On Tue, Jul 13, 2010 at 8:24 PM, Simon Slavin wrote: >> It might be useful to figure out whether we're aiming for >> detection or correction.  By 'correction' I don't mean recovery >> of all

Re: [sqlite] Database corruption on Linux ext3

On Tue, Jul 13, 2010 at 8:24 PM, Simon Slavin wrote: > It might be useful to figure out whether we're aiming for > detection or correction. By 'correction' I don't mean recovery > of all information, I mean restoring the database to some state > it was in just after a

Re: [sqlite] Database corruption on Linux ext3

On 14 Jul 2010, at 1:30am, Jim Wilcoxson wrote: > This problem of not doing writes, or doing them in > the wrong order, is a different animal IMO. If writes are not happening, or are happening in the wrong order, you're in trouble. It's almost impossible to figure out how to even detect that

Re: [sqlite] Compiling as part of MFC C++ project

>> What do I need to do to get sqlite3.c to compile in a MFC C++ project >> (Visual C++)? If I just add it to the project, I end up getting a >> compile error something like: "Unexpected end of file while searching >> for pre-compiled header directive". >> > > This is a basic Visual C++ question.

Re: [sqlite] Compiling as part of MFC C++ project

You need to use the project properties to set the file as a C file and to not use precompiled headers On 7/13/2010 5:47 PM, GHCS Software wrote: > What do I need to do to get sqlite3.c to compile in a MFC C++ project > (Visual C++)? If I just add it to the project, I end up getting a > compile

Re: [sqlite] Compiling as part of MFC C++ project

> What do I need to do to get sqlite3.c to compile in a MFC C++ project > (Visual C++)? If I just add it to the project, I end up getting a > compile error something like: "Unexpected end of file while searching > for pre-compiled header directive". > This is a basic Visual C++ question.

[sqlite] Compiling as part of MFC C++ project

What do I need to do to get sqlite3.c to compile in a MFC C++ project (Visual C++)? If I just add it to the project, I end up getting a compile error something like: "Unexpected end of file while searching for pre-compiled header directive". Do I need to rename it "sqlite3.cpp" and put the

Re: [sqlite] Database corruption on Linux ext3

On Tue, Jul 13, 2010 at 8:06 PM, Roger Binns wrote: > > On 07/13/2010 04:57 PM, Simon Slavin wrote: > > One on each page and one for the entire file that checksums the page > checksums ? > > One for each page plus one of the header would make the most sense, but the > I

Re: [sqlite] Database corruption on Linux ext3

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/13/2010 04:57 PM, Simon Slavin wrote: > One on each page and one for the entire file that checksums the page > checksums ? One for each page plus one of the header would make the most sense, but the overriding concern would be something that

Re: [sqlite] Database corruption on Linux ext3

On 13 Jul 2010, at 9:26pm, Roger Binns wrote: > On 07/13/2010 12:59 PM, D. Richard Hipp wrote: >> You are encouraged to submit comments, insights, criticism, and >> analysis to this mailing list. Thanks. > > Have you considered adding internal checksums to SQLite files so that at the > very

Re: [sqlite] Sqlite Insert Speed Optimization

Griggs, Donald wrote: > I guess I was wondering if the fastest records-per-transaction value > would depend on the page cache and be more or less independent of the > total records to be imported. I think the page cache is one of a great many variables. > So, the records-per-transaction

Re: [sqlite] Sqlite Insert Speed Optimization

Griggs, Donald wrote: > Is the percentage of the final rowcount really a criterion? The answer to that, according to my brief exploration, is somewhere between "yes" and "very much yes", depending on various factors. Thanks, Eric. I guess I was wondering if the fastest

Re: [sqlite] Regression with sqlite-snapshot-201007091257

Hi Shane! I used two separate builds. One was built with the following command line: cl /DSQLITE_ENABLE_FTS3=1 /DSQLITE_ENABLE_COLUMN_METADATA=1 /DTHREADSAFE=1 /O2 /LD sqlite3.c sqlite3.def and the other was built from inside Visual Studio 2010 UI and reports the following compile options:

Re: [sqlite] Sqlite Insert Speed Optimization

Werner Smit wrote: > My question(s) > If I want to use the "insert or replace" to populate my database of > around 1 million records. > And I want to do it as fast as possible. > What are all the tricks I can use? Obey the first rule of optimization: don't do it unless you're sure you

Re: [sqlite] Sqlite Insert Speed Optimization

Griggs, Donald wrote: > Is the percentage of the final rowcount really a criterion? The answer to that, according to my brief exploration, is somewhere between "yes" and "very much yes", depending on various factors. -- Eric A. Smith The number of UNIX installations has grown to 10, with

Re: [sqlite] Regression with sqlite-snapshot-201007091257

I tried to reproduce the issue with the latest version, as well as with the referenced snapshot, and could not. I ran two instances of the sqlite CLI as you indicated without issue. C:\work\sqlite\win32\Debug>sqlite_snapshot test.db SQLite version 3.7.0 Enter ".help" for instructions Enter SQL

Re: [sqlite] Database corruption on Linux ext3

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/13/2010 12:59 PM, D. Richard Hipp wrote: > You are encouraged to submit comments, insights, criticism, and > analysis to this mailing list. Thanks. Have you considered adding internal checksums to SQLite files so that at the very least

Re: [sqlite] open db cx to fork(2)d children

On Mon, Jul 12, 2010 at 11:25:07PM -0700, Roger Binns wrote: > > No, just entry points into the library. What makes you think that I > > meant that every function in the library should check this? > > I also meant entry points but wasn't specific. There are a lot of them that > acquire/release

[sqlite] Database corruption on Linux ext3

An appliance manufacturer has discovered a database corruption issue on Linux using ext3. The issue is documented here: http://www.sqlite.org/draft/lockingv3.html#ext3-barrier-problem You are encouraged to submit comments, insights, criticism, and analysis to this mailing list.

Re: [sqlite] WAL: no longer able to use read-only databases?

On Fri, Jul 9, 2010 at 3:21 PM, Matthew L. Creech wrote: > In testing the latest SQLite snapshot with WAL enabled, it seems that > there's no way to use a database in a read-only location. > Documentation on the developers' current view of this issue can be found here:

Re: [sqlite] Ticks to unixepoch date

Hi Roberto, Re: Please find a test database in the attachment. I don't think attachments are permitted on the mailing list messages. If it's only ten rows, perhaps just using the command line utility to perform a .dump Command and pasting the text into a new message would do the trick.

Re: [sqlite] Ticks to unixepoch date

Thanks for your answer. Please find a test database in the attachment. As you can see I have 1 table with 10 records, and the columns StartDate and EndDate represent a DateTime field, where the values are saved as ticks. There is a view that tries to convert the ticks into unixepoch values, but

Re: [sqlite] Sqlite Insert Speed Optimization

On 13 Jul 2010, at 5:33pm, Werner Smit wrote: > I currently use the following pragma's (for speed) > temp_store = 2 > page_size=4096 > cache_size=8192 > synchronous=off > > Any others I could try? Don't get too involved in the PRAGMAs until you have a good reason to. The default values are

Re: [sqlite] Sqlite Insert Speed Optimization

On Tue, Jul 13, 2010 at 12:48 PM, John Drescher wrote: >> I was wondering if that's really so.  Wouldn't the marginal speed >> improvement be quite small?  Is the percentage of the final rowcount really >> a criterion? > > Each transaction costs at least 1 disk seek. Doing

Re: [sqlite] Sqlite Insert Speed Optimization

> I was wondering if that's really so.  Wouldn't the marginal speed improvement > be quite small?  Is the percentage of the final rowcount really a criterion? Each transaction costs at least 1 disk seek. Doing thousands of seeks the result would be very slow. John

Re: [sqlite] Sqlite Insert Speed Optimization

-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Drescher Sent: Tuesday, July 13, 2010 12:37 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Sqlite Insert Speed Optimization > I also wrap my

Re: [sqlite] Sqlite Insert Speed Optimization

> After these 500 i take a few seconds to read more data so sqlite should > have time to do any housekeeping it might need. SQLite is not a Database Server. It has no background threads. So it can't do any "housekeeping" until you call some sqlite3_* function. Pavel On Tue, Jul 13, 2010 at

Re: [sqlite] Sqlite Insert Speed Optimization

> I also wrap my statements (about 500 inserts at a time) with a begin/end > transaction. > After these 500 i take a few seconds to read more data so sqlite should > have time to do any housekeeping it might need. > Wrap more into a transaction. 500 is too small of a percentage of a million.

[sqlite] Sqlite Insert Speed Optimization

Hi there. I've been playing around with sqlite. Very impressed so far. Using 3.5.6 in windows developing with Clarion. My question(s) If I want to use the "insert or replace" to populate my database of around 1 million records. And I want to do it as fast as possible. What are all the tricks I

Re: [sqlite] Regression with sqlite-snapshot-201007091257

Steps to reproduce, run sqlite3 test.db twice. The first instance: SQLite version 3.7.0 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> pragma encoding="UTF-8"; sqlite> pragma auto_vacuum=incremental; sqlite> pragma journal_mode=truncate; truncate The second

Re: [sqlite] Regression with sqlite-snapshot-201007091257

The second problem is definitely related to the change in the implementation of "pragma journal_mode" though I am not completely sure how. The previous version of our code did pragma encoding="UTF-8"; pragma auto_vacuum=incremental; pragma journal_mode=truncate; after opening the database file.

Re: [sqlite] Ticks to unixepoch date

Can you give us the values in your table? This works for me on 3.6.23.1 create table t (i integer); insert into t values(634355968); select datetime((i/100)-62135596800,'unixepoch') from t as expr1; 2011-03-13 07:06:40 Michael D. Black Senior Scientist Northrop Grumman Mission

Re: [sqlite] noobie Is there an additional web site for noo bie questions.??

B H writes: > > also is there a web site or blog for questions and answers in additional to the email?  > The mailing list is pretty good about answering most questions promptly and simply. If you're looking for a more 'visual' intro to SQLite, you may want to check out

Re: [sqlite] noobie where is my data base? I am new

B H writes: > > Downloaded sqlite to desktop. > ran getting started. > so where is  my example ex1. > or How do and where can I save the ex data base and related tables. As an example, create a directory off your Documents directory named 'sqlite', and copy the sqlite3 file

Re: [sqlite] Regression with sqlite-snapshot-201007091257

Btw, the failing application call is DeleteFileW. F. On Tue, Jul 13, 2010 at 5:02 PM, Filip Navara wrote: > Hi, > > for a few days we have been running our application with SQLite built from > the 201007091257 snapshot. The application runs on Windows 7 systems that >

Re: [sqlite] Ticks to unixepoch date

Thanks for pointing that out Israel. I had the right value (-62135596800) but I mistakenly pasted something else. Nonetheless I get the following error: invalid floating point operation Any idea to make that statement work? Best Regards Roberto Dalmonte Il 13/07/2010 16:46, Israel Lins

[sqlite] Regression with sqlite-snapshot-201007091257

Hi, for a few days we have been running our application with SQLite built from the 201007091257 snapshot. The application runs on Windows 7 systems that are fully updated. We didn't enable the WAL mode and the following two problems occurred: - Every time a database file is opened SQLite tries

Re: [sqlite] Ticks to unixepoch date

Let me understand you... if tick is 0 the date id 0001/01/01 12:00:00? if yes the select do you want is SELECT datetime((TICK_VALUE / 100) - 62135553600, 'unixepoch') AS Expr1 use that site for help! http://www.epochconverter.com/ - "Roberto Dalmonte"

Re: [sqlite] work-arounds for (possibly buggy) nfs?

Richard Hipp wrote: > Try using: > > sqlite3 db ./foo -vfs unix-dotfile When my application dies while holding a lock under unix-dotfile, all subsequent runs always think the database is locked. I guess this is because the lock file exists. Is there an obvious/good way to know whether the

[sqlite] Ticks to unixepoch date

In theory it should be possible to do it right now using the following syntax ... SELECTdatetime((columnAsTicks / 100) - 186796800, 'unixepoch') AS Expr1 FROMTable ...unfortunately it doesn't work, at least the way I tried it. The operation is the following: 1)

Re: [sqlite] order of select result

On 13 Jul 2010, at 6:53am, Lloyd wrote: > I dont have any supportive column in table to issue "order > by". Can it be achieved by issuing an "ordr by" query on "row_id" (used by > sqlite internaly)? No consistently. Because unless you're using a rowid column you declared yourself, even the

[sqlite] SELECT TicksToDateTime(AppointmentStart)

Hi, I'm a C# developer and happy user of a SQLite database. I use System.Data.SQLite dll and I'm saving datetime data asSystem. DateTime.Ticks and I'm very happy with it. The only thing I would like to have is something similar to the existing SELECT datetime(1092941466, 'unixepoch'); I would

Re: [sqlite] Which SQLite API return SQLITE_BUSY?

Thanks Jay for your detailed reply. I would be more happy if the SQLite documentaion include the error codes each function can return. Thanks a lot, Lloyd - Original Message - From: "Jay A. Kreibich" To: "General Discussion of SQLite Database"

Re: [sqlite] Noobie question about importing data into table

YES!!! THAT WORKS !!! Thank you to all the repliers :) -- View this message in context: http://old.nabble.com/Noobie-question-about-importing-data-into-table-tp29126116p29148819.html Sent from the SQLite mailing list archive at Nabble.com. ___

Re: [sqlite] Noobie question about importing data into table

On Tue, 13 Jul 2010, Calimeron wrote: > > Can somebody point me in the right direction? > > I have joined the two tables by doing: select * from ChineseTable, > EnglishTable on IDX_c = IDX_cedict > > Now I would like to save this as a new table, so I can manipulate this table > in sqlite

Re: [sqlite] Noobie question about importing data into table

Thanks :) I tried >>>INSERT INTO newtable select * from cedict, english on IDX_c = IDX_cedict<<< ("newtable" doesn't exist yet) But that, predictably, didn't work. First I should create a table with the right characteristics? (10+4 columns, columns with the right names and characteristics)

Re: [sqlite] Noobie question about importing data into table

On 13 July 2010 10:22, Calimeron wrote: > > Can somebody point me in the right direction? > > I have joined the two tables by doing: select * from ChineseTable, > EnglishTable on IDX_c = IDX_cedict > > Now I would like to save this as a new table, so I can manipulate

Re: [sqlite] Noobie question about importing data into table

Can somebody point me in the right direction? I have joined the two tables by doing: select * from ChineseTable, EnglishTable on IDX_c = IDX_cedict Now I would like to save this as a new table, so I can manipulate this table in sqlite and other programs. Thank you for any replies :) -- View

Re: [sqlite] order of select result

Hello L> Thanks Swithun. I dont have any supportive column in table to issue L> "order by". Can it be achieved by issuing an "ordr by" query on L> "row_id" (used by sqlite internaly)? You could, I think. But it might be better style to create an INTEGER PRIMARY KEY AUTOINCREMENT column to do

Re: [sqlite] open db cx to fork(2)d children

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/12/2010 08:53 PM, Nicolas Williams wrote: > On Mon, Jul 12, 2010 at 08:43:32PM -0700, Roger Binns wrote: >> Earlier you were trying to optimise out calls to getpid() and now you want >> every SQLite function source to be changed? > > No, just