Re: [sqlite] Prohibitive indexing time with large tables

Hello! See tests and some interpretation: http://geomapx.blogspot.com/2009/11/degradation-of-indexing-speed.html Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Anyone able to access a SQLite database within a Tcl starpack?

Hello! On Sunday 14 February 2010 05:44:34 Matthew Smith wrote: > It seems from the Tcl wiki that SQLite is not VFS-aware (Tcl Virtual File > System). Yes, it's rigth. > This means that when you create an application, you must delivery a packaged > EXE of the Tcl application, but the database

Re: [sqlite] Strange sqlite behaviour when calling from C

Erik de Castro Lopo wrote: > Hi all, > > I've got some relatively simple code that does a query and then > does the following: > > const char *stmt = "DELETE FROM connect WHERE machine = 'xxx';" ; > > printf ("Before : %s\n", sqlite3_errmsg (pdb)) ; > sqlite3_exec (pdb, stmt, NULL,

Re: [sqlite] Writing and reading a csv using sqlite3

On Mon, Feb 15, 2010 at 09:11:31PM +, Simon Slavin scratched on the wall: > > On 15 Feb 2010, at 8:35pm, Jay A. Kreibich wrote: > > On Mon, Feb 15, 2010 at 07:31:43PM +, Simon Slavin scratched on the > > wall: > >> So the command-line tool cannot correctly read the CSV files > >> it

Re: [sqlite] Writing and reading a csv using sqlite3

On Mon, Feb 15, 2010 at 4:31 PM, Simon Slavin wrote: > > On 15 Feb 2010, at 9:28pm, Roger Binns wrote: > > > Simon Slavin wrote: > >> It uses one when it outputs, but it won't accept the same format when it > inputs. So the program is itself inconsistent: however you

Re: [sqlite] Strange sqlite behaviour when calling from C

Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Erik de Castro Lopo wrote: > > printf ("Before : %s\n", sqlite3_errmsg (pdb)) ; > > sqlite3_exec (pdb, stmt, NULL, NULL, NULL) ; > > printf ("After : %s\n", sqlite3_errmsg (pdb)) ; > > > > The query that

Re: [sqlite] Writing and reading a csv using sqlite3

On 15 Feb 2010, at 9:28pm, Roger Binns wrote: > Simon Slavin wrote: >> It uses one when it outputs, but it won't accept the same format when it >> inputs. So the program is itself inconsistent: however you define 'csv >> format', either its output or input function is broken. > > There is a

Re: [sqlite] Strange sqlite behaviour when calling from C

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Erik de Castro Lopo wrote: > printf ("Before : %s\n", sqlite3_errmsg (pdb)) ; > sqlite3_exec (pdb, stmt, NULL, NULL, NULL) ; > printf ("After : %s\n", sqlite3_errmsg (pdb)) ; > > The query that happens before this works as expected and

Re: [sqlite] Writing and reading a csv using sqlite3

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Simon Slavin wrote: > It uses one when it outputs, but it won't accept the same format when it > inputs. So the program is itself inconsistent: however you define 'csv > format', either its output or input function is broken. There is a ticket

[sqlite] Strange sqlite behaviour when calling from C

Hi all, I've got some relatively simple code that does a query and then does the following: const char *stmt = "DELETE FROM connect WHERE machine = 'xxx';" ; printf ("Before : %s\n", sqlite3_errmsg (pdb)) ; sqlite3_exec (pdb, stmt, NULL, NULL, NULL) ; printf ("After : %s\n",

Re: [sqlite] Writing and reading a csv using sqlite3

On 15 Feb 2010, at 8:35pm, Jay A. Kreibich wrote: > On Mon, Feb 15, 2010 at 07:31:43PM +, Simon Slavin scratched on the wall: >> >> So the command-line tool cannot correctly read the CSV files >> it output itself ? Okay, that's messed up. Something should be done. > > Yes, and always

Re: [sqlite] Prohibitive indexing time with large tables

Jay A. Kreibich a écrit : You said you're creating two databases. Are you doing those one at a time or ATTACHing the other databases? Doing them one at a time, indeed. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Prohibitive indexing time with large tables

On Mon, Feb 15, 2010 at 08:09:26PM +0100, Jérôme Magnin scratched on the wall: > > The database is 2GB > > but the least amount of memory I have is 4GB. I also use a 32kb page > > size, > > larger cache etc. > I have of course tried to increase cache size and database page size (up > to 32K)

Re: [sqlite] Writing and reading a csv using sqlite3

> So the command-line tool cannot correctly read the CSV files it output > itself ? Okay, that's messed up. Something should be done. On the other hand, should it read HTML output back in again? At present, the .import only supports separators, no other processing is done. It wouldn't be too

Re: [sqlite] Writing and reading a csv using sqlite3

On Mon, Feb 15, 2010 at 07:31:43PM +, Simon Slavin scratched on the wall: > > So the command-line tool cannot correctly read the CSV files > it output itself ? Okay, that's messed up. Something should be done. Yes, and always will be. Different version of Excel have similar issues.

Re: [sqlite] bug: round problem?

Simon Slavin: > Nice try. The rounding rule is that a .5 rounds to the nearest even number. No it isn't: sqlite> select round(40226+0.5); 40227.0 sqlite> select round(40227+0.5); 40228.0 sqlite> select round(40228+0.5); 40229.0 sqlite> select round(40229+0.5); 40230.0 Phil Hibbs. -- Don't

Re: [sqlite] round problem?

On Feb 15, 2010, at 1:43 PM, Roger Binns wrote: > Shane Harrelson wrote: >> I'm looking at how this can be improved. > > It seems that everyone else is converging on using David Gay's dtoa.c We've been "converging" for a few years!

Re: [sqlite] Writing and reading a csv using sqlite3

On 15 Feb 2010, at 7:23pm, Phil Hibbs wrote: > Simon Slavin: >> The command-line tool is correct as far as it goes in producing CSV >> files: if you use quotes around text fields, it is correct to double quotes >> inside those fields. Take a look at the CSVs you're trying to import. >> Are the

Re: [sqlite] Writing and reading a csv using sqlite3

Simon Slavin: > The command-line tool is correct as far as it goes in producing CSV > files: if you use quotes around text fields, it is correct to double quotes > inside those fields. Take a look at the CSVs you're trying to import. > Are the text fields delimited by quotes ? If quotes appear

Re: [sqlite] Prohibitive indexing time with large tables

Sorry folks, it took me time (and a brand new brain) to figure out how I could post a reply that would appear on the mailing list (I'm new to that kind of sport). To Roger Binns: > As another data point, my data set is 10M records and the 6 indices I > need > are created in under two minutes.

Re: [sqlite] Writing and reading a csv using sqlite3

On 15 Feb 2010, at 4:33pm, Phil Hibbs wrote: > Paul Corke: >> Do you need to read the csv data back in to a sqlite database? If >> not, then the file you've got should be ok. > > Well, what I need to do is import csv files from other sources, this > was just a test to see if there were any

Re: [sqlite] bug: round problem?

On 15 Feb 2010, at 6:40pm, Simon Slavin wrote: > .5 rounds to the nearest even number Whoops. My email software put this post in a different place to its followups. Apologies. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] round problem?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Shane Harrelson wrote: > I'm looking at how this can be improved. It seems that everyone else is converging on using David Gay's dtoa.c whose algorithm is based on the paper "How to Print Floating-Point Numbers Accurately" by Guy L. Steele, Jr. and

Re: [sqlite] bug: round problem?

On 15 Feb 2010, at 8:51am, zabusovm...@mail.ru wrote: > sqlite> select round(40223+0.5); > 40224.0 > sqlite> select round(40224+0.5); > 40224.0 > sqlite> select round(40225+0.5); > 40226.0 Nice try. The rounding rule is that a .5 rounds to the nearest even number. This is so that rounding

Re: [sqlite] round problem?

On Mon, Feb 15, 2010 at 12:52 PM, Phil Hibbs wrote: > Shane Harelson: > > Under the covers, when the second term to ROUND(X,y) is omitted, SQLite > adds > > 0.5 and then truncates.Because of floating point precision, some > numbers > > can not be represented exactly...

Re: [sqlite] round problem?

On Mon, Feb 15, 2010 at 12:52 PM, Phil Hibbs wrote: > Shane Harelson: > > Under the covers, when the second term to ROUND(X,y) is omitted, SQLite > adds > > 0.5 and then truncates.Because of floating point precision, some > numbers > > can not be represented exactly...

Re: [sqlite] Performance of a SELECT DISTINCT query, then vs now

As usual, I forgot the promised attachments. Here they are. Jean-Paul addr opcode p1 p2 p3 -- -- -- - 0 Noop0 0 1 Integer 2 0 2 MustBeInt

[sqlite] Performance of a SELECT DISTINCT query, then vs now

Hi all, I'm investigating some apparent slowdowns of an app when upgrading to more recent versions of SQLite3. This has been a problem for quite a while, so I had to go back pretty far to find a version of SQLite3 where things were performing as desired. For the moment, I've just compared

Re: [sqlite] round problem?

Shane Harelson: > Under the covers, when the second term to ROUND(X,y) is omitted, SQLite adds > 0.5 and then truncates.Because of floating point precision, some numbers > can not be represented exactly... causing the odd rounding you saw in your > examples. I've just had a look "under the

Re: [sqlite] round problem?

Shane Harrelson wrote: > http://www.sqlite.org/lang_corefunc.html#round > > Under the covers, when the second term to ROUND(X,y) is omitted, SQLite adds > 0.5 and then truncates.Because of floating point precision, some numbers > can not be represented exactly... causing the odd rounding you

Re: [sqlite] round problem?

http://www.sqlite.org/lang_corefunc.html#round Under the covers, when the second term to ROUND(X,y) is omitted, SQLite adds 0.5 and then truncates.Because of floating point precision, some numbers can not be represented exactly... causing the odd rounding you saw in your examples. I'll see

Re: [sqlite] round problem?

Igor: > http://en.wikipedia.org/wiki/Rounding#Round_half_to_even So, are you saying round-half-to-even is the SQLite behaviour? I would have expected it to have used the "normal" mathematical convention of round-half-away-from-zero. The reason this is "normal" mathematical behaviour is that any

Re: [sqlite] SQLite 3.6.22 ATTACH no longer works for files outside the current working directory

On Feb 15, 2010, at 11:36 AM, Hick Gunter wrote: > In SQLite 3.5.9 it used to be possible to write > > sqlite> ATTACH '/some/unix/file/name.db' AS mydb; > > > In SQLite 3.6.22 this results in the error message > > Error: unable to resolve operation Works fine when I try it. Are you using the

Re: [sqlite] round problem?

zabusovm...@mail.ru wrote: > SQLite version 3.6.22 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> select round(40223+0.5); > 40224.0 > sqlite> select round(40224+0.5); > 40224.0 > sqlite> select round(40225+0.5); > 40226.0 > sqlite>

Re: [sqlite] Stupid noob question - can't find table

> If you give SQLite a command to open a database file that > doesn't exist, it will just create a new one. It doesn't give an > error message. So use a search function on your disk for > files with that name. Try this in your program: "select * from sqlite_master;" That will tell you if you

[sqlite] SQLite 3.6.22 ATTACH no longer works for files outside the current working directory

In SQLite 3.5.9 it used to be possible to write sqlite> ATTACH '/some/unix/file/name.db' AS mydb; In SQLite 3.6.22 this results in the error message Error: unable to resolve operation whereas sqlite> ATTACH /some/unix/file/name.db AS mydb; is a syntax error at '/' The problem seems to be

[sqlite] bug: round problem?

SQLite version 3.6.22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select round(40223+0.5); 40224.0 sqlite> select round(40224+0.5); 40224.0 sqlite> select round(40225+0.5); 40226.0 sqlite> ___ sqlite-users mailing

Re: [sqlite] Writing and reading a csv using sqlite3

Paul Corke: > Not doing the ".mode csv" before the ".import" makes things better > but doesn't fix it. That breaks when I have more than one column though, "line 1: expected 3 columns of data but found 1". > Do you need to read the csv data back in to a sqlite database? If > not, then the file

Re: [sqlite] Writing and reading a csv using sqlite3

On 15 Feb 2010, at 2:50pm, Phil Hibbs wrote: > If I write out data in csv format using SQLite3 shell, it doubles up > all the quote characters. Any ideas how can I stop it doing this? > > C:\sqlite>sqlite3 test.db > SQLite version 3.6.22 > Enter ".help" for instructions > Enter SQL statements

Re: [sqlite] change path of journal file

On Mon, Feb 15, 2010 at 03:59:32PM +0100, "Tino Flei?ner" scratched on the wall: > Hello everyone, > > i'd like to know if its possible to change the path of the journal > file which is automaticly generated when i'm starting a transaction. > I'm currently trying to import data from a database on

[sqlite] change path of journal file

Hello everyone, i'd like to know if its possible to change the path of the journal file which is automaticly generated when i'm starting a transaction. I'm currently trying to import data from a database on a DVD to a local database and i'm getting an error because i can't write on the dvd.

[sqlite] Writing and reading a csv using sqlite3

If I write out data in csv format using SQLite3 shell, it doubles up all the quote characters. Any ideas how can I stop it doing this? C:\sqlite>sqlite3 test.db SQLite version 3.6.22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table test ( f1

Re: [sqlite] 1 reader 1 writer but sqlite3_step fails wit h “database is locked” error in both processes

Closing the thread: >Then why sqlite3_step() fails for the reader and for the writer? The reader fails since it cannot obtain SHARED lock required for reading. It cannot obtain that lock since the writer already managed to obtain PENDING or EXCLUSIVE lock. The writer fails since there is

Re: [sqlite] Prohibitive indexing time with large tables

> His original question was about the todo list found at the wiki. > Not sure if any of the core developer will answer, but I would > be interested as well... I too have been long interested in when this might be addressed, since we see similar performance drop-offs with large numbers of rows.

[sqlite] Possible oversight in alter table statement?

Hi, I've noticed in alter.c, around about line 480 there is a direct reference to updating the sqlite_master table, rather than using the SCHEMA_TABLE macro. I wondered whether this was a simple oversight, or by design? It means the following currently happens... sqlite> pragma