Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Keith Medcalf
If no directory is specified, the current directory should be used. If no filename is specified then no file should be used. Introduction of magic is very, very, very, very, very, very, very, very, bad. It is why Steve Ballmer got his ass handed back to him on a platter and Windows is in the

[sqlite] SQLite & Android: unable to open database file - error 14

2014-02-10 Thread Philippe Riand
I posted the question on StackOverflow: http://stackoverflow.com/questions/21661814/sqlite-android-unable-to-open-database-file-error-14 Any help would be greatly appreciated! I saw that a similar issue has been reported many times. In my case, I'm directly using the "C" API invoked using my own

[sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

2014-02-10 Thread Bert Huijben
Hi, As part of the Subversion 1.8.6 release we tried introducing some data in the 'sqlitstat_stat1' table using the recommended approach for Sqlite 3.8.0+ compatibility to tell sqlite about our 'bad indexes': [[ ANALYZE sqlite_master; INSERT INTO "sqlite_stat1" VALUES INSERT INTO

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Nico Williams
On Mon, Feb 10, 2014 at 10:06 PM, James K. Lowden wrote: James proposes that when no DB is named on the command-line argument list then a [user-specific] default be used, and that the user be told. I like it. I admit that I often rely on the shell keeping no state

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Simon Slavin
On 11 Feb 2014, at 4:06am, James K. Lowden wrote: > ${HOME}/.sqlite/db would be my choice. Since the objective is not to let a naive user unexpectedly lose the data, it might seem a bad idea to put the file in a directory which is hidden from naive users. Just

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread James K. Lowden
On Mon, 10 Feb 2014 10:23:57 -0500 Richard Hipp wrote: > Proposed Change To Address The Problem: Thank you for addressing this. I for one think you're getting a lot of unhelpful advice. A database application that loses data? As a feature? > (1) Detect double-click launch

Re: [sqlite] help needed for major SQLite problem

2014-02-10 Thread James K. Lowden
On Mon, 10 Feb 2014 16:20:40 -0500 C M wrote: > But this must be a fairly commonly sought need. The solution you > propose where I occasionally export a copy of the db to Dropbox is > great *for backup purposes* but seems to exclude the possibility of > syncing across

Re: [sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

2014-02-10 Thread Richard Hipp
On Mon, Feb 10, 2014 at 9:02 PM, Richard Hipp wrote: > > Your work-arounds pending the patch release: > > (1) Do not compile with SQLITE_ENABLE_STAT3 or SQLITE_ENABLE_STAT4 > (2) Change your query so that it says "+moved_to IS NOT NULL" - add a > unary "+" operator before every

Re: [sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

2014-02-10 Thread Richard Hipp
Thanks for the bug report. This was a real problem. It is now fixed on the SQLite trunk ( http://www.sqlite.org/src/info/c950d6c411). It will be a few days before we can get a patch release (3.8.3.1) together. Your work-arounds pending the patch release: (1) Do not compile with

Re: [sqlite] help needed for major SQLite problem

2014-02-10 Thread Simon Slavin
On 10 Feb 2014, at 9:20pm, C M wrote: > I purposefully put the SQlite database file in the Dropbox folder because > it was my intention, with this app, to allow a user to use the app on more > than one computer and "sync" the database via Dropbox. E.g., s/he could > make

Re: [sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

2014-02-10 Thread Richard Hipp
Ticket here: http://www.sqlite.org/src/info/4c86b126f2 The work-around until a fix is available is to avoid compiling with SQLITE_ENABLE_STAT3 or SQLITE_ENABLE_STAT4. On Mon, Feb 10, 2014 at 4:38 PM, Richard Hipp wrote: > STAT3 should never change the answer. It should only

Re: [sqlite] help needed for major SQLite problem

2014-02-10 Thread RSmith
On 2014/02/10 23:40, Stephen Chrzanowski wrote: Personally, I don't buy that DropBox is the culprit as I've done this kind of thing a few times in a few applications of my own, however, I'm the single user that works on that single account, and any app that uses DB is usually under development

Re: [sqlite] help needed for major SQLite problem

2014-02-10 Thread RSmith
On 2014/02/10 23:20, C M wrote: On Mon, Feb 10, 2014 at 2:54 PM, RSmith wrote: How to go from the error codes to the diagnosis? I think the logic is as follows: [lots of snipping] Thanks for this insight. I purposefully put the SQlite database file in the Dropbox

Re: [sqlite] help needed for major SQLite problem

2014-02-10 Thread Stephen Chrzanowski
Personally, I don't buy that DropBox is the culprit as I've done this kind of thing a few times in a few applications of my own, however, I'm the single user that works on that single account, and any app that uses DB is usually under development and "closed" on any other geographical site.

Re: [sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

2014-02-10 Thread Richard Hipp
STAT3 should never change the answer. It should only help the answer to appear faster. The fact that the queries gives different answers with and without STAT3 clearly indicates a bug. We are working the problem now. Thanks for providing an simplified test case. On Mon, Feb 10, 2014 at 2:05

Re: [sqlite] help needed for major SQLite problem

2014-02-10 Thread C M
On Mon, Feb 10, 2014 at 2:54 PM, RSmith wrote: > How to go from the error codes to the diagnosis? I think the logic is as > follows: > > We can see an error occurs when trying to access the file, or more > specifically, trying to obtain a shared lock on it. This means it is

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-lineshell

2014-02-10 Thread Richard Hipp
On Mon, Feb 10, 2014 at 2:38 PM, Tim Streater wrote: > I wouldn't worry about whether the user clicks a red button in one corner > or another. If they do that, presumably they risk corrupting their database > anyway. > No. SQLite is hardened against hard-kills like this.

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-lineshell

2014-02-10 Thread Richard Hipp
The latest patch for this is: http://www.sqlite.org/src/vdiff?from=0dfa7ee9157ea6b1=fe284afe739c497e=1 Changes: (1) Reword the banner to make it more terse and to try to avoid "banner fatigue". (2) If opened with no command-line arguments (and hence on an in-memory database) output a warning

Re: [sqlite] help needed for major SQLite problem

2014-02-10 Thread RSmith
On 2014/02/10 21:18, C M wrote: Documents\My Dropbox\myapp\gorp.db-journal) - Access is denied. (3338) SQLITE_IOERR SQLITE_LOG: statement aborts at 16: [SELECT resumes, start FROM WHERE start='2014-02-07 14:24:14.064000' AND value='activity'] disk I/O error (3338) SQLITE_IOERR Looks like

Re: [sqlite] help needed for major SQLite problem

2014-02-10 Thread Kees Nuyt
On Mon, 10 Feb 2014 14:18:18 -0500, C M wrote: >On Sat, Feb 8, 2014 at 4:28 AM, Kees Nuyt wrote: >> >> On Sat, 08 Feb 2014 12:06:01 +0700, Dan Kennedy >> wrote: > > > >> >> SQLITE_LOG: delayed 1375ms for lock/sharing conflict (10)

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-lineshell

2014-02-10 Thread Tim Streater
On 10 Feb 2014 at 19:25, Simon Slavin wrote: > I don't like the idea anyway. There should be no difference between > double-clicking on an app and starting it by typing its name. I would go for: 1) Warning at startup if an in-memory db is being used 2) Add .save

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

2014-02-10 Thread big stone
Don't say postgresql is slower : - I remember now I did then implement the "norvig postgresql", - and it manages a 20% win over your solution with today postgresql 9.3.2.3. Anyway, it's indeed a "less inefficient" run for now between SQL motors. It remains to be seen if improving SQLite in

Re: [sqlite] help needed for major SQLite problem

2014-02-10 Thread Simon Slavin
On 10 Feb 2014, at 7:18pm, C M wrote: > I may want to deploy this app to users who would also backup their database > by having it in the Dropbox folder. What would people suggest I do about > this? Don't run the app while Dropbox is messing with its datafile. The problem

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-lineshell

2014-02-10 Thread Simon Slavin
On 10 Feb 2014, at 5:57pm, Richard Hipp wrote: > On Mon, Feb 10, 2014 at 12:51 PM, wrote: > >> I second the idea of a kind of "WARNING: All your work will be lost, are >> you sure you want to quit? (y/N)" on trying to exit, but *ONLY* if the >> application was

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Gabor Grothendieck
On Mon, Feb 10, 2014 at 1:56 PM, Petite Abeille wrote: > > On Feb 10, 2014, at 7:39 PM, Gabor Grothendieck > wrote: > >> That should have read right join. > > My personal opinion? Anyone even considering using a right outer join should > be

Re: [sqlite] help needed for major SQLite problem

2014-02-10 Thread C M
On Sat, Feb 8, 2014 at 4:28 AM, Kees Nuyt wrote: > > On Sat, 08 Feb 2014 12:06:01 +0700, Dan Kennedy > wrote: > >> SQLITE_LOG: delayed 1375ms for lock/sharing conflict (10) SQLITE_IOERR > >> > >> SQLITE_LOG: os_win.c:35129: (5) winAccess(C:\Documents

Re: [sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

2014-02-10 Thread Petite Abeille
On Feb 10, 2014, at 8:05 PM, Bert Huijben wrote: > As part of the Subversion 1.8.6 release we tried introducing some data in > the 'sqlitstat_stat1' table using the recommended approach for Sqlite 3.8.0+ > compatibility to tell sqlite about our 'bad indexes’: ( Not

[sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

2014-02-10 Thread Bert Huijben
[Retrying with the user I subscribed with to avoid the moderation] Hi, As part of the Subversion 1.8.6 release we tried introducing some data in the 'sqlitstat_stat1' table using the recommended approach for Sqlite 3.8.0+ compatibility to tell sqlite about our 'bad indexes': [[ ANALYZE

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Petite Abeille
On Feb 10, 2014, at 7:39 PM, Gabor Grothendieck wrote: > That should have read right join. My personal opinion? Anyone even considering using a right outer join should be cursed into repeating their first day at high school. For ever. Groundhog Day, The High School

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread RSmith
On 2014/02/10 20:31, Petite Abeille wrote: On Feb 10, 2014, at 4:23 PM, Richard Hipp wrote: Proposed Change To Address The Problem: What’s the problem exactly? CS101 students distress? That’s way beyond SQLite reach. My 2¢: don’t create a default persistent database. This

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Gabor Grothendieck
That should have read right join. Its a nuisance when you are trying to show someone SQL and trying to keep things simple that you have to add the complexity of switching the arguments around. I am still on 3.7.17 which is the version that currently ships with the software I am using but its

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Konrad Hambrick
All -- My $0.02 is to not change anything except maybe, perhaps printing a warning message. If they want to create a persistent DB, .quit ; and start over with a dbname on the command line ... I am afraid any such change might break existing bash and bat scripts I've got 'out there in the

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread RSmith
On 2014/02/10 18:22, John McKown wrote: Being a UNIX (Linux) partisan, and somewhat tacky towards Windows users, why not go the normal Windows route of having a "pop up" dialog box (or at least a message) similar to what normal Windows applications say about possible loss of data. Something

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Adam Devita
Good day, I'd rather the warning be in the text when you open the sqlite tool with an implied in memory database. Put an extra \n if you want the warning to stand out. Adam On Mon, Feb 10, 2014 at 1:26 PM, Petite Abeille wrote: > > On Feb 10, 2014, at 5:19 PM,

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Petite Abeille
On Feb 10, 2014, at 4:23 PM, Richard Hipp wrote: > Proposed Change To Address The Problem: What’s the problem exactly? CS101 students distress? That’s way beyond SQLite reach. My 2¢: don’t create a default persistent database. This is not helpful to anyone.

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Petite Abeille
On Feb 10, 2014, at 5:19 PM, Gabor Grothendieck wrote: > The other features that would make teaching a bit easier would be to support > left join explicitly and support the rfc4180 standard for csv files. Hmmm? Left join:

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-lineshell

2014-02-10 Thread Kevin Martin
On 10 Feb 2014, at 17:57, Richard Hipp wrote: > I think I know how to detect a double-click launch versus a command-line > launch on windows. But I don't know how to do this, or even if it is > possible to do, on Mac or Linux. Anybody have any ideas? For me, It's not so much

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Richard Hipp
On Mon, Feb 10, 2014 at 1:11 PM, Mike King wrote: > Why not show the warning on exit only if an in memory database is in use. > > Because on windows, the likely "exit" will be when the user clicks the big red X in the upper right-hand corner, closing the terminal window

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Mike King
Why not show the warning on exit only if an in memory database is in use. Likewise by default open in memory unless a path is specified. On Monday, 10 February 2014, Richard Hipp wrote: > On Mon, Feb 10, 2014 at 12:51 PM, > wrote: > > > I second the

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-lineshell

2014-02-10 Thread Richard Hipp
On Mon, Feb 10, 2014 at 12:51 PM, wrote: > I second the idea of a kind of "WARNING: All your work will be lost, are > you sure you want to quit? (y/N)" on trying to exit, but *ONLY* if the > application was started by (double-)clicking on it, otherwise the warning > will be a

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-lineshell

2014-02-10 Thread tonyp
I second the idea of a kind of "WARNING: All your work will be lost, are you sure you want to quit? (y/N)" on trying to exit, but *ONLY* if the application was started by (double-)clicking on it, otherwise the warning will be a nuisance when running test scripts. -Original Message-

Re: [sqlite] Memory-mapped I/O [was: SQLite 3.7.17 preview - 2x faster?]

2014-02-10 Thread Nico Williams
On Sat, Feb 8, 2014 at 7:26 AM, Richard Hipp wrote: > OpenBSD lacks a coherent filesystem cache. That is to say, changes to a > file made using write() are not necessarily reflected in mmap-ed memory > right away. And change to a mmap-ed segment are not necessarily reflected >

Re: [sqlite] Memory-mapped I/O [was: SQLite 3.7.17 preview - 2x faster?]

2014-02-10 Thread Nico Williams
On Sun, Feb 9, 2014 at 5:03 PM, Richard Hipp wrote: > On Sun, Feb 9, 2014 at 5:49 PM, James K. Lowden > wrote: > I suspect that adding msync() calls would wipe out any speed advantage for > using memory-mapped I/O. And since speed is the only advantage

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Jay Kreibich
On Feb 10, 2014, at 10:20 AM, Jay Kreibich wrote: > On Feb 10, 2014, at 10:15 AM, Richard Hipp wrote: > >> What if, instead of opening a standard database, the sqlite3.exe >> command-line shell just issued a warning message reminding the user that >> they are

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Stephen Chrzanowski
I was just going to suggest that John. Short of hitting CTRL-C to break out of the program, the user may have to "double-quit" if no file path has been given to be saved to, just for confirmation. > .q !!Warning - In-Memory Database not saved. Quit again to exit without saving > .q

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread John McKown
Being a UNIX (Linux) partisan, and somewhat tacky towards Windows users, why not go the normal Windows route of having a "pop up" dialog box (or at least a message) similar to what normal Windows applications say about possible loss of data. Something along the lines of "You are exiting sqlite3,

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Jay Kreibich
On Feb 10, 2014, at 10:15 AM, Richard Hipp wrote: > What if, instead of opening a standard database, the sqlite3.exe > command-line shell just issued a warning message reminding the user that > they are working on a transient in-memory database and suggesting the use > of the

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread David Bicking
The first time I saw sqlite demonstrated at a linux user group, the presenter didn't realize he was using a memory database. I had to explain why all his work was lost, then proceeded to continue the demo since I knew more about the product. (This was years ago, I think we were still at sqlite

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Dominique Devienne
On Mon, Feb 10, 2014 at 5:15 PM, Richard Hipp wrote: > What if, instead of opening a standard database, the sqlite3.exe > command-line shell just issued a warning message reminding the user that > they are working on a transient in-memory database and suggesting the use > of the

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Richard Hipp
What if, instead of opening a standard database, the sqlite3.exe command-line shell just issued a warning message reminding the user that they are working on a transient in-memory database and suggesting the use of the ".open" command to connect to a persistent on-disk database. Like in this

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Gabor Grothendieck
On Mon, Feb 10, 2014 at 10:23 AM, Richard Hipp wrote: > The Problem: > > Many new users (especially university students taking a "database 101" The other features that would make teaching a bit easier would be to support left join explicitly and support the rfc4180 standard for

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Simon Slavin
On 10 Feb 2014, at 4:15pm, Richard Hipp wrote: > What if, instead of opening a standard database, the sqlite3.exe > command-line shell just issued a warning message reminding the user that > they are working on a transient in-memory database and suggesting the use > of the

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Dominique Devienne
On Mon, Feb 10, 2014 at 4:23 PM, Richard Hipp wrote: > Proposed Change To Address The Problem: > > When launching sqlite3.exe with a double-click, have it open a standard > database in a standard place instead of an in-memory database as you would > get when launching

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Stephan Beal
On Mon, Feb 10, 2014 at 4:41 PM, Simon Slavin wrote: > Why ? I suspect some Mac user would find it just as useful. And then why > leave Unix users out ? > Speaking as a member of the Unix-only crowd: please don't! While i admit that the current behaviour has caused minor

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Simon Slavin
On 10 Feb 2014, at 3:23pm, Richard Hipp wrote: > When launching sqlite3.exe with a double-click, have it open a standard > database in a standard place instead of an in-memory database as you would > get when launching sqlite3.exe with no arguments. Possibly also give >

[sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Richard Hipp
The Problem: Many new users (especially university students taking a "database 101" class) download the "sqlite3.exe" file from the SQLite website, double-click on the "sqlite3" icon to get a command-line shell, then start typing SQL statements. But when they exit the shell, they are distressed

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

2014-02-10 Thread Richard Hipp
On Mon, Feb 10, 2014 at 9:07 AM, Clemens Eisserer wrote: > Hi Richard, > > > In WAL mode, with synchronous=NORMAL (the default), fsync() only happens > on > > a checkpoint operation. Whether or not checkpoint is "very seldom" > depends > > on a number of factors, but seems

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

2014-02-10 Thread Clemens Eisserer
Hi Richard, > In WAL mode, with synchronous=NORMAL (the default), fsync() only happens on > a checkpoint operation. Whether or not checkpoint is "very seldom" depends > on a number of factors, but seems likely to be the case in your scenario. Thanks a lot for your answer. Just to make sure,

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

2014-02-10 Thread Richard Hipp
On Mon, Feb 10, 2014 at 8:40 AM, Clemens Eisserer wrote: > Hi, > > I would like to use sqlite for storing temperature data acquired every > 10s running on my raspberry pi. > As my first SD card died within a week with this workload, I am > looking for opportunities to reduce

[sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

2014-02-10 Thread Clemens Eisserer
Hi, I would like to use sqlite for storing temperature data acquired every 10s running on my raspberry pi. As my first SD card died within a week with this workload, I am looking for opportunities to reduce write operations triggered by fsyncs to flash. For me loosing 1h of data at a power

Re: [sqlite] LIKE operator and collations

2014-02-10 Thread Constantine Yannakopoulos
On Sun, Feb 9, 2014 at 8:27 PM, Simon Slavin wrote: > I know it's a hack. But it's an elegant efficient hack that takes > advantage of the things SQLite does well. As long as that's the only way > you were using LIKE. > Don't get me wrong, the solution is good. But apart

Re: [sqlite] Free Page Data usage

2014-02-10 Thread Eduardo Morrás
>El lun, 10/2/14, Simon Slavin escribió: > > Asunto: Re: [sqlite] Free Page Data usage > Para: "General Discussion of SQLite Database" > Fecha: lunes, 10 de febrero, 2014 12:34 > > > On 10 Feb 2014, at

Re: [sqlite] Free Page Data usage

2014-02-10 Thread Simon Slavin
On 10 Feb 2014, at 11:29am, Raheel Gupta wrote: >>> 64-bit page numbers would not be backwards compatible. > > It might be possible to implement it in backwards compatible mode. I guess > SQlite has some free flags in its superblock. Maybe we can use a single > byte to

Re: [sqlite] Free Page Data usage

2014-02-10 Thread Raheel Gupta
Hi, If I were to implement it for my use only, any heads up where I could start. I do know that PgNo is a variable used everywhere. Will changing that help ? >> 64-bit page numbers would not be backwards compatible. It might be possible to implement it in backwards compatible mode. I guess

Re: [sqlite] Free Page Data usage

2014-02-10 Thread Clemens Ladisch
Raheel Gupta wrote: >If only the number of pages could be increased somehow. Does anyone think >its practical to make the pageNo from a 32 bit int to a 64 bit Unsigned >Integer. The 32-bit page number is part of the file format. 64-bit page numbers would not be backwards compatible. Regards,

Re: [sqlite] Free Page Data usage

2014-02-10 Thread Raheel Gupta
>> Note that choosing a page size smaller than the typical row size means that the bottom level of the BTree degrades to 1 row per node. What do you mean by this ? How is a smaller page bad for the database ? On Mon, Feb 10, 2014 at 2:43 PM, Hick Gunter wrote: > With a record

Re: [sqlite] Free Page Data usage

2014-02-10 Thread Hick Gunter
This thread has already gone through that discussion ;) -Ursprüngliche Nachricht- Von: Dominique Devienne [mailto:ddevie...@gmail.com] Gesendet: Montag, 10. Februar 2014 10:32 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Free Page Data usage On Mon, Feb 10, 2014 at

Re: [sqlite] Free Page Data usage

2014-02-10 Thread Dominique Devienne
On Mon, Feb 10, 2014 at 10:13 AM, Hick Gunter wrote: > You can choose the source of fragmentation: loosing close to 1 row per > page (better in bigger pages) or having ununsed space due to nonadjacent > deletes (better in smaller pages) > For the latter, you do have

Re: [sqlite] Free Page Data usage

2014-02-10 Thread Hick Gunter
With a record size of a little over 4K (note that the on-disk space requirement of a integer+4k Blob row is not always 4k+8) and a page size of 2K you are storing 1 row in 3 pages (close to 50% overhead). Deleting a record will give you 3 pages of free space, which will be reused quickly; some