Re: [sqlite] suggested changes to file format document

> Thanks very much for catching these. Document is now updated with the fixes in the fossil docsrc repository. Dan. > 1. In the following, s/less than/less than or equal to/ > > """ > 2.3.3.4 Index B-Tree Cell Format > [snip 2 paragraphs] > > If the record is small enough, it is stored

Re: [sqlite] Alignment bug in SQLIte 3.6.14.2

Looks like the same issue as reported here: http://www.sqlite.org/cvstrac/tktview?tn=3869 Fix will be included in 3.6.15. Or just grab the patch here if you're in a hurry: http://www.sqlite.org/cvstrac/chngview?cn= Dan. On May 27, 2009, at 12:12 PM, Brian Dantes wrote: > We've

Re: [sqlite] Slow Transaction Speed?

On Wed, May 27, 2009 at 5:58 AM, Marcus Grimm wrote: >> On Tue, May 26, 2009 at 9:47 PM, Marcus Grimm >> http://www.sqlite.org/faq.html#q19 > > the faq as well as the speed comparison speaks about a few dozen > of transaction per second... that's

[sqlite] Index expressions and constraints

Hi All 1. Let's say I have a field called Field1 in table Table1 with the following rows: "A B C" "D E F" "G H I" Is there some way I can create an index on just a substring for a column without extracting the substring into it's own column, something like CREATE INDEX I1 ON Table1

Re: [sqlite] Slow Transaction Speed?

Nuno Lucas wrote: > On Wed, May 27, 2009 at 5:58 AM, Marcus Grimm wrote: >>> On Tue, May 26, 2009 at 9:47 PM, Marcus Grimm >>> http://www.sqlite.org/faq.html#q19 >> the faq as well as the speed comparison speaks about a few dozen >> of

Re: [sqlite] Slow Transaction Speed?

On May 27, 2009, at 7:51 AM, Marcus Grimm wrote: > > > Nuno Lucas wrote: >> On Wed, May 27, 2009 at 5:58 AM, Marcus Grimm > > wrote: On Tue, May 26, 2009 at 9:47 PM, Marcus Grimm http://www.sqlite.org/faq.html#q19 >>> the faq

Re: [sqlite] Slow Transaction Speed?

my developemnt system is a Win XP, with of course NTFS, I don't know which drive it has, I guess a standard 7200 rpm. I tried also on a Win 2000 and Vista, as expected Vista appears to be the slowest even this machine has the best hardware, theoretically. All measurements vary +- 20%. Just for

Re: [sqlite] Slow Transaction Speed?

On Wed, May 27, 2009 at 12:51 PM, Marcus Grimm wrote: > Yes, I understood that this is unsafe and I'll not use it right now. > But my feeling is that it will be an option for the future. My databases > are not so big (say in megabyte range) and currently my server

Re: [sqlite] Slow Transaction Speed?

thank you for the hint, my current approach to copy the database file, is even more restrictive -- it is encapsulated by an EXCLUSIVE transaction. ;) In my case it is unlikely that it will block anybody from using the server since it is a typical "working-hours" application and the backup will

Re: [sqlite] Index expressions and constraints

"Tima Fourie" wrote in message news:363e35f87b5c4cbbb2e34692737dd...@c1 > 1. Let's say I have a field called Field1 in table Table1 with the > following rows: > > "A B C" > "D E F" > "G H I" > > Is there some way I can create an index on just a substring for a > column. You

Re: [sqlite] Fast data duplication

"Vasil Boshnyakov" wrote in message news:000c01c9de8b$16510a40$42f31e...@bg > The short description is: we need to copy many records of a table in > the same table but changing the "Name" value. So we have added a new > function which process the names: > > Insert into users

Re: [sqlite] Slow Transaction Speed?

> my developemnt system is a Win XP, with of course NTFS, I > don't know which drive it has, I guess a standard 7200 rpm. > What file extension (i.e. the letters after the dot in the filename) do you give the database? I faintly recall there is a windows peculiarity with system restore or

Re: [sqlite] Slow Transaction Speed?

Dr. Hipp, > Your OS and filesystem configuration have a big impact too. I've notice, for > example, that transactions are really slow on RieserFS on Linux compared to > Ext3. In your experience, which Linux file system(s) provide the high performance platform for SQLite? Which Linux file

[sqlite] ISO-8859-1

Hello, it is possible using charset ISO-8859-1 in sqlite? lg \^/ili -- View this message in context: http://www.nabble.com/ISO-8859-1-tp23741187p23741187.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list

Re: [sqlite] Slow Transaction Speed?

Thanks Nick, good point. ahh yes, I've read about this somewhere... My extension is currently ".db", a quick check indicates that using ".abc" gives a slight speed improvement, maybe 10%. But that is allready very close to the variation I get between different test runs, so I'm not really sure

Re: [sqlite] ISO-8859-1

"Wili" wrote in message news:23741187.p...@talk.nabble.com > it is possible using charset ISO-8859-1 in sqlite? No, not really. Convert your strings to UTF-8 before passing them to SQLite, and to whatever encoding you want when getting them out. Now, you could probably

Re: [sqlite] ISO-8859-1

Hi Igor, thx for fast awnser! lg \^/ili -- View this message in context: http://www.nabble.com/ISO-8859-1-tp23741187p23741440.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] ISO-8859-1

"Igor Tandetnik" wrote in message news:gvjb4m$hc...@ger.gmane.org > "Wili" wrote in > message news:23741187.p...@talk.nabble.com >> it is possible using charset ISO-8859-1 in sqlite? > > No, not really. Convert your strings to UTF-8 before passing

Re: [sqlite] Fast data duplication

Thank you for the help, I already got the solution of the problem. Thanks! -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Wednesday, May 27, 2009 2:47 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite]

Re: [sqlite] Fast data duplication

On 27/05/2009 9:47 PM, Igor Tandetnik wrote: > "Vasil Boshnyakov" > wrote in message news:000c01c9de8b$16510a40$42f31e...@bg >> The short description is: we need to copy many records of a table in >> the same table but changing the "Name" value. So we have added a new >>

[sqlite] Some index questions

Hi List, this is not very sqlite specific but hopefully somebody will give me some info on this, as I haven't yet found a nice description of this: I'm curios how an index works internally, my suspect is that an index can be seen as a kind of table that has two columns which hold a copy of a)

Re: [sqlite] Some index questions

On Wed, May 27, 2009 at 7:38 PM, Marcus Grimm wrote: > Hi List, > > this is not very sqlite specific but hopefully somebody will give > me some info on this, as I haven't yet found a nice description of this: > > I'm curios how an index works internally, my suspect is

[sqlite] Types for strings, non-expert question

Hi, I see that in most functions strings are typed as char * while in 'sqlite_column_text' and 'sqlite_value_text' they are typed as unsigned char * Is there something I should have in mind about that when writing a binding to sqlite3 from another language? Can I type both as beeing

Re: [sqlite] Some index questions

Thanks Puneet, I know Igors nice text book example... :-) My questions are a little more technical like "If I would do a DB engine"... to judge better when or when not using or creating an index, etc. I think it is useful to understand what it is behind all this, similar that I would recommend

Re: [sqlite] Types for strings, non-expert question

On May 27, 2009, at 9:36 AM, Maurí cio wrote: > Hi, > > I see that in most functions strings are typed as > > char * > > while in 'sqlite_column_text' and 'sqlite_value_text' > they are typed as > > unsigned char * > That was just bad design on my part. I don't know what I was thinking

Re: [sqlite] Some index questions

On May 27, 2009, at 9:08 PM, Marcus Grimm wrote: > Hi List, > > this is not very sqlite specific but hopefully somebody will give > me some info on this, as I haven't yet found a nice description of > this: > > I'm curios how an index works internally, my suspect is that an index > can be seen

Re: [sqlite] Some index questions

Hi Dan, thanks for your answers: this was exactly what I was looking for. Dan wrote: > On May 27, 2009, at 9:08 PM, Marcus Grimm wrote: >> >> The difference, I guess, to a "real" sql table, is that it is sorted >> with respect to the indexed column and not by row_id, something that >> makes them

[sqlite] accessing a sqlite db with multiple java processes.

Hi, I was using sqlite with only one java process (using the sqlitejdbc driver) and everything was ok... now i'm trying to run several instances of the same program on the same db...and i've a bunch of "database is locked" exceptions. i was wandering if it was possible to access the same db with

Re: [sqlite] accessing a sqlite db with multiple java processes.

Hi, i imagine that your problem has nothing to do with your driver. Have you looked at http://www.sqlite.org/faq.html#q5 ? Martin Laurent Burgy schrieb: > Hi, > I was using sqlite with only one java process (using the sqlitejdbc driver) > and everything was ok... now i'm trying to run several

Re: [sqlite] Some index questions

On May 27, 2009, at 11:13 AM, Marcus Grimm wrote: > I guess such compound indexes have only a benefit for specific > queries, like the above that use all the time exactly these two > columns, otherwise two seperate indexes will do the same job but maybe > a little slower due to additional

Re: [sqlite] accessing a sqlite db with multiple java processes.

my bad... but i don't know how to block the writing to the database to wait until the release of the lock... It seems to me that when the writing is impossible because there's a lock, the driver raises the exception... Maybe the good thing to do would be to wait for a regular FileLock to be

Re: [sqlite] Some index questions

On May 27, 2009, at 10:13 PM, Marcus Grimm wrote: > Hi Dan, > > thanks for your answers: this was exactly what I was looking for. > > Dan wrote: >> On May 27, 2009, at 9:08 PM, Marcus Grimm wrote: >>> >>> The difference, I guess, to a "real" sql table, is that it is sorted >>> with respect to

Re: [sqlite] Some index questions

Richard, thanks you very much for the example and advice. Ahh, yes. I missed the point that sqlite might use an index also as a kind of buffer to fetch data. kind regards Marcus D. Richard Hipp wrote: > On May 27, 2009, at 11:13 AM, Marcus Grimm wrote: >> I guess such compound indexes have

Re: [sqlite] Foreign Keys and Indexes

s.breith...@staubli.com wrote: > Now I noticed that the speed of selects are much faster with fields > that are indexed Well, that's pretty much the point of having an index in the first place. Why do you find this fact surprising? > I´ve read that only one index is used per where (I think join

Re: [sqlite] Slow Transaction Speed?

I'm running on Linux with ext3 and just wrote a Python test program to insert rows into a table with a single column, no indexing, and doing a commit after each insert. When I first ran it, I got around 440 inserts per second, which is clearly impossible. This is a 7200rpm drive, so even if I

Re: [sqlite] Slow Transaction Speed?

I should have mentioned that when running my C test program on Linux, with the hard drive cache enabled, it ran instantly. But it should have taken quite a while to do 3000 fsyncs. So this problem with defeating the hard drive cache is not an SQLite issue; it's a Linux issue. I also ran the

Re: [sqlite] Some index questions

Thanks again Dan, the phonebook is an excellent example to use the compound index. So far I was thinking for such cases that it is enough to have one index and let sqlite test against the other value from the original table, like: CREATE TABLE T1(A, B, C); CREATE INDEX T1x ON T1(B); SELECT *

[sqlite] Sqlite3 wrapper for Liberty Basic

I am using the above wrapper and was informed that it was created by D. R. Hipp. When I run my program from source everything works great but when I run it from .exe I often get a Smalltalk halt stating that an un-continuable protection violation has occurred. This halt may occur at startup and

Re: [sqlite] corrupt database recovery

Ok...it's happened again and I've decided that I need to track this down once and for all! Here is what I'm seeing: I get errors when I do a integrity_check (see below), but I can .dump it to a text file and then .read it into another database ok. It seems to me that I'm screwing up an index or

[sqlite] Table-Alias Confusion or Bug?

Hello! Given this SQL: drop table if exists t; create table t (c); select * from t as ta where t.c = ta.c; SQLite3 chokes with the following error: SQL error near line 5: no such column: t.c Question: Is this the expected behaviour? I know that the where clause does not

Re: [sqlite] Table-Alias Confusion or Bug?

On May 27, 2009, at 11:26 AM, Ralf Junker wrote: > Hello! > > Given this SQL: > > drop table if exists t; > > create table t (c); > > select * >from t as ta >where t.c = ta.c; > > SQLite3 chokes with the following error: > > SQL error near line 5: no such column: t.c > > Question: >

Re: [sqlite] Foreign Keys and Indexes

> s.breith...@staubli.com wrote: > > Now I noticed that the speed of selects are much faster with fields > > that are indexed > > Well, that's pretty much the point of having an index in the first > place. Why do you find this fact surprising? Because the indexed field that improved my program

Re: [sqlite] corrupt database recovery

Gene Allen wrote: > Ok...it's happened again and I've decided that I need to track this down > once and for all! > > Here is what I'm seeing: I get errors when I do a integrity_check (see > below), but I can .dump it to a text file and then .read it into another > database ok. > > It seems to me

Re: [sqlite] Table-Alias Confusion or Bug?

At 17:33 27.05.2009, D. Richard Hipp wrote: >On May 27, 2009, at 11:26 AM, Ralf Junker wrote: > >> Hello! >> >> Given this SQL: >> >> drop table if exists t; >> >> create table t (c); >> >> select * >>from t as ta >>where t.c = ta.c; >> >> SQLite3 chokes with the following error: >> >>

Re: [sqlite] Foreign Keys and Indexes

s.breith...@staubli.com wrote: > So when I have a query over serveral tables normally the primay key > is set as index when the table is joined. Only if you actually join on primary key column(s). Doing so is common, but not mandatory. > If I need to add a where > clause > to a field of the

Re: [sqlite] corrupt database recovery

My code is outside the database layer. So I do all my database work, then compress and encrypt it. No errors are returned anywhere. I'm guessing that it's going to be an uninitialized variable or byte alignment problems somewhere. This code is running on hundreds of machines without a problem

Re: [sqlite] corrupt database recovery

What platform is this? I just posted a note today that my Linux box running 2.6-25 and ext3 isn't doing fsyncs like it should, so I would be susceptible to DB corruption if my machine crashed during DB I/O. I posted a C program you could run on the specific machine with a corrupt DB to see if

Re: [sqlite] Some index questions

On Wed, May 27, 2009 at 10:23:04AM -0400, D. Richard Hipp scratched on the wall: > > On May 27, 2009, at 11:13 AM, Marcus Grimm wrote: > > I guess such compound indexes have only a benefit for specific > > queries, like the above that use all the time exactly these two > > columns, otherwise two

Re: [sqlite] Some index questions

On Wed, May 27, 2009 at 12:34:00PM -0500, Jay A. Kreibich scratched on the wall: > On Wed, May 27, 2009 at 10:23:04AM -0400, D. Richard Hipp scratched on the > wall: > > Indices can be used for sorting as well as for lookup. And sometime > > content can be pulled from indices to avoid an

Re: [sqlite] corrupt database recovery

What platform? Any chance they are using a network drive? John Gene wrote: > My code is outside the database layer. So I do all my database work, then > compress and encrypt it. No errors are returned anywhere. I'm guessing > that it's going to be an uninitialized variable or byte alignment

Re: [sqlite] corrupt database recovery

It is a server class machine running Windows 2003 with 4 GB. No, it's a local drive with 20GB free on it. I'm sure that it's a coding error on my part. SQLite is very stable, in my opinion. I'm just trying to get a rough idea on where I'm screwing up the database. -Original

[sqlite] Feature request: reset file mtime on rollback

It would be handy for file synchronization if SQLite stored the database file's mtime in the journal and reset it if a rollback occurs. It's difficult to do this in an application: 1. If a DB is opened, a transaction started, some stuff happens, and a rollback is executed, the file mtime only

Re: [sqlite] corrupt database recovery

so, if you think it is a coding error on your side it will be a hard work to find the source. I can only image side effects on an used API like sqlite3 with the classics: - stack frame overload in a function that calls sqlite functions - using a local stack variable as a return pointer and reuse

[sqlite] SQL question - not sure if it's possible at all

Hello all, I'm not sure if this can be done in SQL, but if it can, I'd appreciate your help. Let's say I've got this query: SELECT * FROM (SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date ASC) LIMIT ?2 OFFSET ?1 and let's say, "Data" has an "ID" field (primary index). Is it possible to

Re: [sqlite] corrupt database recovery

I've reviewed the code over and over again, but am yet to find it. But it is a good bit of very complicated code (blowfish and compression code). That's why I was asking about the format of the database. Since I'm able to successfully do a .dump but the integrity_check whines, I'm thinking that

Re: [sqlite] corrupt database recovery

sorry, I don't know where sqlite stores any kind of data like index or raw table, my suspect is that, since sqlite usually looks for a free page, the pages are randomly distributed over the file or added at the end if no free page has been found. what I don't understand: what makes you thinking

Re: [sqlite] corrupt database recovery

I'm sorry that I can't help with the SQLite part, but couldn't you write a simple test harness that would create a file (of random size?) with random contents, encrypt/compress to a secondary file, then decompress/decrypt to a third file. Compare first and third files. If they match, delete and

Re: [sqlite] corrupt database recovery

The problem is happening in the field and the program deals with a corrupt database by renaming it, logging the error and creating a new database. So I only end up with the corrupt one. I would GUESS that the database doesn't have any free pages except those at the end since it's an auditing

Re: [sqlite] corrupt database recovery

That's pretty much my test harness. Right now, I'm running a test on every file size from 0 to a 10 MB. If that works correctly (which I expect it will), I think I'm going to have to spend the time and effort setting up a massive test environment and stress the mess out of the whole system.

Re: [sqlite] SQL question - not sure if it's possible at all

Dennis Volodomanov wrote: > Let's say I've got this query: > > SELECT * FROM (SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date > ASC) LIMIT ?2 OFFSET ?1 Why an extra layer? Why not just SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date ASC LIMIT ?2 OFFSET ?1 > and

[sqlite] SQlite performance on multi process env

I am doing a simple test on sqlite where in step one, I just write 10,000 records on a simple database having three fileds. In step 2, after the write is done, I want 100 process reading from the database and doing simple SQLs, no update on the databse just simple select read operations. I am

Re: [sqlite] SQL question - not sure if it's possible at all

Igor Tandetnik wrote: > Dennis Volodomanov wrote: > >> Let's say I've got this query: >> >> SELECT * FROM (SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date >> ASC) LIMIT ?2 OFFSET ?1 >> > > Why an extra layer? Why not just > > SELECT * FROM Data WHERE PlotOnGraph=1

Re: [sqlite] Some index questions

On 28/05/2009 12:24 AM, Dan wrote: > > If a single column index is like the index found in textbooks, > a compound index with two fields is like the phone book. Sorted first by > surname, then by first name. The "rowid", if you like, is the phone > number. > > So, it's easy to find the set of

Re: [sqlite] corrupt database recovery

I might have missed something, but: What are you compressing? ehm... Are compressing the database file while sqlite is running ? > The problem is happening in the field and the program deals with a corrupt > database by renaming it, logging the error and creating a new database. > So > I only