[sqlite] Should I do analyze?

[Navaneeth.K.N]

Hello,

I learned about the use of ANALYZE command recently. In my
application, SQLIte file is generated once and never modified.
Currently my application creates SQLIte database, creates required
tables, indexes and inserts records into it. As a last step, it runs
VACUUM.

I am wondering should I do ANALYZE also as the last command? Will that
make the query planner happy and choose faster plans? Will that
improve the runtime performance?

--
Thanks
Navaneeth
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Questions about exclusive transation

[Igor Tandetnik]

On 6/25/2013 10:13 PM, Woody Wu wrote:

1. When a exclusive transation started and not yet commit, I found if I
open another connection to the same database and try to access it (using
sqlite3_step), I got the error code SQLITE_MISUSE.  It sounds a little
strange because I thought the error code should be SQLITE_BUSY.  Is that
normal?


You are doing something wrong. Some call failed and you are passing an 
invalid handle to a subsequent call, or something like that. That is 
what causes SQLITE_MISUSE: the existence of an exclusive transaction is 
perhaps the proximate cause of the original failure, but it's not the 
direct cause of SQLITE_MISUSE.



2. After started a exclusive transaction with success. Can I expect that
all the operations that follows should run with success?


Yes, barring catastrophic failure (e.g. hard drive gone bad) and 
programming errors on your part (e.g. trying to prepare a syntactically 
incorrect statement).



Especially, is that possible that the 'commit' could get an error of 
SQLITE_BUSY?


No, it is not.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Questions about exclusive transation

[Woody Wu]

Hi,

I have serveral questions about exclusive transaction. (version 3.7.x)

1. When a exclusive transation started and not yet commit, I found if I
open another connection to the same database and try to access it (using
sqlite3_step), I got the error code SQLITE_MISUSE.  It sounds a little
strange because I thought the error code should be SQLITE_BUSY.  Is that
normal?

2. After started a exclusive transaction with success. Can I expect that
all the operations that follows should run with success? Those
operations could be insert, update, delete and will end with a 'commit
transaction'?  Especially, is that possible that the 'commit' could get
an error of SQLITE_BUSY? You know, the reason I want to use exclusive
transaction is to simplify the programming.

Thanks in advance.

-
woody
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue with 2 connections getting out of sync, sample code included

[Richard Hipp]

On Tue, Jun 25, 2013 at 9:40 PM, Jay A. Kreibich  wrote:

> On Tue, Jun 25, 2013 at 11:49:00PM +0100, Simon Slavin scratched on the
> wall:
>   Your other point still stands, however... as soon as _step() returns
>   SQLITE_DONE, it is best to call _reset() before doing anything else.
>   _finalize() can also be called if you know you're done with the
>   statement.
>
>

True.  But it is worth noting that the example code never even got as far
as sqlite3_step() returning SQLITE_DONE.  It was abandoned after the first
SQLITE_ROW return.  I think the read transaction would have been released
if one more sqlite3_step() had been called.  But it is still a good idea to
call sqlite3_reset() just to be sure.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue with 2 connections getting out of sync, sample code included

[Jay A. Kreibich]

On Tue, Jun 25, 2013 at 11:49:00PM +0100, Simon Slavin scratched on the wall:
> 
> On 25 Jun 2013, at 11:45pm, Yuriy Stelmakh  wrote:
> 
> > I assumed that commit would take care of sync, but because my
> > statement was never finalized/reset after last use, it didn't it
> > seems..
> 
> You always need to _finalize().  Getting the data you asked for is not
> the end of the job.  You have to _finalize() in order to let SQLite
> clear up after the statement (which does more than just unlock the file,
> it also does things about the memory your application uses).

  _finalize() isn't the only function that does this.  You can also
  call _reset() to, well... reset the statement.  This will clear all
  the locks and make it clear to the SQLite engine that you're done
  with the current execution of the statement, but will leave the
  statement ready to go for another execution.

> So get used to this: for every _prepare() do a _finalize().  As
> soon as practical.

  That's true, but it isn't the whole story, since there isn't a fixed
  relationship between _prepare() and statement executions.  Using
  _reset() to clear the statement is perfectly acceptable. 
  
  Your other point still stands, however... as soon as _step() returns
  SQLITE_DONE, it is best to call _reset() before doing anything else.
  _finalize() can also be called if you know you're done with the
  statement.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BEGIN IMMEDIATE and the busy handler

[Richard Hipp]

On Tue, Jun 25, 2013 at 6:44 PM, Eric Sink  wrote:

>
> From reading sqlite3.c and the comments therein, it would appear that
> BEGIN IMMEDIATE TRANSACTION never invokes the busy handler.
>
> Is that correct?
>

Correct.  BEGIN IMMEDIATE returns SQLITE_BUSY if it cannot proceed, and if
your application wants to delay and try again, it is free to do so.

The intent of the busy handler is to delay the progress of operations that
are difficult to restart, in the hopes that whatever is blocking progress
will clear.  There is no difficulty in restarting BEGIN IMMEDIATE so it
fails straight away.

Note also that once you get an IMMEDIATE transaction going (once BEGIN
IMMEDIATE returns SQLITE_OK) you are guaranteed to never hit the busy
handler nor get SQLITE_BUSY until after the transaction commits.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue with 2 connections getting out of sync, sample code included

[Yuriy Stelmakh]

i'm caching the statements for later use, so i assume just doing reset
is also ok? it seemed to work well.

Again thank you for help, wasted a few days on this.

Thanks,

Yuriy

On Jun 25, 2013, at 6:49 PM, Simon Slavin  wrote:

>
> On 25 Jun 2013, at 11:45pm, Yuriy Stelmakh  wrote:
>
>> I assumed that commit would take care of sync, but because my
>> statement was never finalized/reset after last use, it didn't it
>> seems..
>
> You always need to _finalize().  Getting the data you asked for is not the 
> end of the job.  You have to _finalize() in order to let SQLite clear up 
> after the statement (which does more than just unlock the file, it also does 
> things about the memory your application uses).  So get used to this: for 
> every _prepare() do a _finalize().  As soon as practical.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue with 2 connections getting out of sync, sample code included

[Simon Slavin]

On 25 Jun 2013, at 11:45pm, Yuriy Stelmakh  wrote:

> I assumed that commit would take care of sync, but because my
> statement was never finalized/reset after last use, it didn't it
> seems..

You always need to _finalize().  Getting the data you asked for is not the end 
of the job.  You have to _finalize() in order to let SQLite clear up after the 
statement (which does more than just unlock the file, it also does things about 
the memory your application uses).  So get used to this: for every _prepare() 
do a _finalize().  As soon as practical.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue with 2 connections getting out of sync, sample code included

[Yuriy Stelmakh]

In the sample code I did not indeed. But in the actual code that i'm
using, the prepared statement used was part of bulk insert surrounded
by begin transaction/ commit transaction. I was reseting the stmt
before it was used inside of transaction and not after, so it caused a
problem.

this is what i had:

begin transaction
loop of
  reset
  step
commit

changed it to:

begin transaction
loop of
 step
 reset
commit

I assumed that commit would take care of sync, but because my
statement was never finalized/reset after last use, it didn't it
seems..

Thanks,

Yuriy

On Jun 25, 2013, at 6:39 PM, Richard Hipp  wrote:

> On Tue, Jun 25, 2013 at 6:32 PM, Yuriy Stelmakh  wrote:
>
>> Thank you! that did the trick. Its interesting that even though you
>> can commit a transaction, the cache sync doesn't happen until you
>> finalize or reset all you statements. I wish this was documented
>> somewhere better!
>>
>
>
> No, you completely misunderstood what I said.
>
> You never issued an explicit COMMIT.  And you had a read operation in
> progress (because you never finished the count(*)) which means that no
> autocommit would happen either.  Hence, your transaction was never
> committing.
>
> This has nothing whatsoever to do with caches or syncing.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BEGIN IMMEDIATE and the busy handler

[Eric Sink]

From reading sqlite3.c and the comments therein, it would appear that 
BEGIN IMMEDIATE TRANSACTION never invokes the busy handler.


Is that correct?

--
E

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue with 2 connections getting out of sync, sample code included

[Richard Hipp]

On Tue, Jun 25, 2013 at 6:32 PM, Yuriy Stelmakh  wrote:

> Thank you! that did the trick. Its interesting that even though you
> can commit a transaction, the cache sync doesn't happen until you
> finalize or reset all you statements. I wish this was documented
> somewhere better!
>


No, you completely misunderstood what I said.

You never issued an explicit COMMIT.  And you had a read operation in
progress (because you never finished the count(*)) which means that no
autocommit would happen either.  Hence, your transaction was never
committing.

This has nothing whatsoever to do with caches or syncing.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue with 2 connections getting out of sync, sample code included

[Yuriy Stelmakh]

Thank you! that did the trick. Its interesting that even though you
can commit a transaction, the cache sync doesn't happen until you
finalize or reset all you statements. I wish this was documented
somewhere better!

Thanks,

Yuriy

On Jun 25, 2013, at 2:19 PM, Richard Hipp  wrote:

> On Tue, Jun 25, 2013 at 2:06 PM, Yuriy Stelmakh  wrote:
>
>>  Hi all, please help: very strange issue that should not be happening:
>>
>>
>>
>> 1.   Have two connections to same database: _db1 and _db2.
>>
>> 2.   Create table in _db1
>>
>> 3.   Run count * from _db2 -> returns 0
>>
>> 4.   Insert 1 row using _db1 –OK
>>
>> 5.   Run select * from _db2 -> 0 rows return
>>
>>
>>
>> Weird thing is that if I remove step 3, then step 5 returns a row.
>>
>> I’m guessing there must be a bug in SQLite with cache sync, or I’m missing
>> something obvious.
>
> Without looking at your code, I'm guessing that you are not finalizing the
> statement in step 3.  It is therefore holding the transaction open,
> preventing it from being committed, and thus preventing step 5 from seeing
> the change.
>
> The fact that changes are not visible to outside connections until the
> change is committed is an important and very powerful feature of the SQL
> language, and of SQLite, not a bug.
>
> Looking at your code now - yes, my suspicion seems to be confirmed.  I've
> marked the spot below where I think you need the sqlite3_finalize().
>
>
>
>>
>>
>>
>> Please help:
>>
>> sqlite3* _db1;
>>
>>sqlite3* _db2;
>>
>>sqlite3_open("test.sqlite", &_db1);
>>
>>sqlite3_open("test.sqlite", &_db2);
>>
>>
>>
>>//create table
>>
>>char* errorMessage;
>>
>>sqlite3_exec(_db1, "CREATE TABLE test (column1 INTEGER)", NULL,
>> NULL,
>> );
>>
>>
>>
>>//get count of items from db2 -> should return 0
>>
>>string countStatement = "SELECT COUNT(*) FROM test";
>>
>>
>>
>>sqlite3_stmt* stmt;
>>
>>//prepare statement
>>
>>if(sqlite3_prepare_v2(_db2, countStatement.c_str(), -1, ,
>> NULL)
>> != SQLITE_OK){
>>
>>GHAssertTrue(false, @"Should have created prepared statement:
>> %s", sqlite3_errmsg(_db1));
>>
>>}
>>
>>
>>
>>int rc = sqlite3_step(stmt);
>>
>>if(rc == SQLITE_ROW){
>>
>>//get the count
>>
>>if(sqlite3_column_count(stmt) > 0){
>>
>>int count = sqlite3_column_int(stmt, 0);
>>
>>GHAssertTrue(count == 0, @"Count should be zero");
>>
>>}else{
>>
>>GHAssertTrue(false, @"Should have returned a row");
>>
>>}
>>
>>}else{
>>
>>GHAssertTrue(false, @"Should have returned a row");
>>
>>}
>
>
> Insert "sqlite3_finalize(stmt);" here.
>
>
>
>>
>>
>>//insert one row into table using db1
>>
>>sqlite3_exec(_db1, "INSERT into test(column1) values (4)", NULL,
>> NULL, );
>>
>>
>>
>>//read row using db2
>>
>>//get count of items from db2 -> should return 0
>>
>>string selectStatement = "SELECT * FROM test";
>>
>>
>>
>>//prepare statement
>>
>>if(sqlite3_prepare_v2(_db2, selectStatement.c_str(), -1, ,
>> NULL)
>> != SQLITE_OK){
>>
>>GHAssertTrue(false, @"Should have created prepared statement:
>> %s", sqlite3_errmsg(_db1));
>>
>>}
>>
>>
>>
>>rc = sqlite3_step(stmt);
>>
>>if(rc == SQLITE_ROW){
>>
>>//get the count
>>
>>if(sqlite3_column_count(stmt) > 0){
>>
>>//good
>>
>>}else{
>>
>>//bad
>>
>>GHAssertTrue(false, @"Should have returned a row");
>>
>>}
>>
>>}else{
>>
>>   //bad
>>
>>GHAssertTrue(false, @"Should have returned a row");
>>
>>}
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue with 2 connections getting out of sync, sample code included

[Richard Hipp]

On Tue, Jun 25, 2013 at 2:06 PM, Yuriy Stelmakh  wrote:

>   Hi all, please help: very strange issue that should not be happening:
>
>
>
> 1.   Have two connections to same database: _db1 and _db2.
>
> 2.   Create table in _db1
>
> 3.   Run count * from _db2 -> returns 0
>
> 4.   Insert 1 row using _db1 –OK
>
> 5.   Run select * from _db2 -> 0 rows return
>
>
>
> Weird thing is that if I remove step 3, then step 5 returns a row.
>
> I’m guessing there must be a bug in SQLite with cache sync, or I’m missing
> something obvious.
>

Without looking at your code, I'm guessing that you are not finalizing the
statement in step 3.  It is therefore holding the transaction open,
preventing it from being committed, and thus preventing step 5 from seeing
the change.

The fact that changes are not visible to outside connections until the
change is committed is an important and very powerful feature of the SQL
language, and of SQLite, not a bug.

Looking at your code now - yes, my suspicion seems to be confirmed.  I've
marked the spot below where I think you need the sqlite3_finalize().



>
>
>
> Please help:
>
>  sqlite3* _db1;
>
> sqlite3* _db2;
>
> sqlite3_open("test.sqlite", &_db1);
>
> sqlite3_open("test.sqlite", &_db2);
>
>
>
> //create table
>
> char* errorMessage;
>
> sqlite3_exec(_db1, "CREATE TABLE test (column1 INTEGER)", NULL,
> NULL,
> );
>
>
>
> //get count of items from db2 -> should return 0
>
> string countStatement = "SELECT COUNT(*) FROM test";
>
>
>
> sqlite3_stmt* stmt;
>
> //prepare statement
>
> if(sqlite3_prepare_v2(_db2, countStatement.c_str(), -1, ,
> NULL)
> != SQLITE_OK){
>
> GHAssertTrue(false, @"Should have created prepared statement:
> %s", sqlite3_errmsg(_db1));
>
> }
>
>
>
> int rc = sqlite3_step(stmt);
>
> if(rc == SQLITE_ROW){
>
> //get the count
>
> if(sqlite3_column_count(stmt) > 0){
>
> int count = sqlite3_column_int(stmt, 0);
>
> GHAssertTrue(count == 0, @"Count should be zero");
>
> }else{
>
> GHAssertTrue(false, @"Should have returned a row");
>
> }
>
> }else{
>
> GHAssertTrue(false, @"Should have returned a row");
>
> }
>


Insert "sqlite3_finalize(stmt);" here.



>
>
> //insert one row into table using db1
>
> sqlite3_exec(_db1, "INSERT into test(column1) values (4)", NULL,
> NULL, );
>
>
>
> //read row using db2
>
> //get count of items from db2 -> should return 0
>
> string selectStatement = "SELECT * FROM test";
>
>
>
> //prepare statement
>
> if(sqlite3_prepare_v2(_db2, selectStatement.c_str(), -1, ,
> NULL)
> != SQLITE_OK){
>
> GHAssertTrue(false, @"Should have created prepared statement:
> %s", sqlite3_errmsg(_db1));
>
> }
>
>
>
> rc = sqlite3_step(stmt);
>
> if(rc == SQLITE_ROW){
>
> //get the count
>
> if(sqlite3_column_count(stmt) > 0){
>
> //good
>
> }else{
>
> //bad
>
> GHAssertTrue(false, @"Should have returned a row");
>
> }
>
> }else{
>
>//bad
>
> GHAssertTrue(false, @"Should have returned a row");
>
> }
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue with 2 connections getting out of sync, sample code included

[Yuriy Stelmakh]

This is on OSX and iOs 6. Sqlite versions tested were 3.7.13  & 3.7.17
Database file is stored on disk. Mac OSX journaled partion HFS+.

Thanks,

Yuriy

On Jun 25, 2013, at 2:09 PM, Simon Slavin  wrote:

>
> On 25 Jun 2013, at 7:06pm, Yuriy Stelmakh  wrote:
>
>> Hi all, please help: very strange issue that should not be happening:
>
> Please verify:
>
> Which operating system are you using ?  And which version ?
>
> Is the database file stored on the hard disk of the computer running the 
> program ?
>
> What format is the partition that the file is stored on ?
>
> Thanks.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue with 2 connections getting out of sync, sample code included

[Simon Slavin]

On 25 Jun 2013, at 7:06pm, Yuriy Stelmakh  wrote:

>  Hi all, please help: very strange issue that should not be happening:

Please verify:

Which operating system are you using ?  And which version ?

Is the database file stored on the hard disk of the computer running the 
program ?

What format is the partition that the file is stored on ?

Thanks.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Issue with 2 connections getting out of sync, sample code included

[Yuriy Stelmakh]

  Hi all, please help: very strange issue that should not be happening:



1.   Have two connections to same database: _db1 and _db2.

2.   Create table in _db1

3.   Run count * from _db2 -> returns 0

4.   Insert 1 row using _db1 –OK

5.   Run select * from _db2 -> 0 rows return



Weird thing is that if I remove step 3, then step 5 returns a row.

I’m guessing there must be a bug in SQLite with cache sync, or I’m missing
something obvious.



Please help:

 sqlite3* _db1;

sqlite3* _db2;

sqlite3_open("test.sqlite", &_db1);

sqlite3_open("test.sqlite", &_db2);



//create table

char* errorMessage;

sqlite3_exec(_db1, "CREATE TABLE test (column1 INTEGER)", NULL, NULL,
);



//get count of items from db2 -> should return 0

string countStatement = "SELECT COUNT(*) FROM test";



sqlite3_stmt* stmt;

//prepare statement

if(sqlite3_prepare_v2(_db2, countStatement.c_str(), -1, , NULL)
!= SQLITE_OK){

GHAssertTrue(false, @"Should have created prepared statement:
%s", sqlite3_errmsg(_db1));

}



int rc = sqlite3_step(stmt);

if(rc == SQLITE_ROW){

//get the count

if(sqlite3_column_count(stmt) > 0){

int count = sqlite3_column_int(stmt, 0);

GHAssertTrue(count == 0, @"Count should be zero");

}else{

GHAssertTrue(false, @"Should have returned a row");

}

}else{

GHAssertTrue(false, @"Should have returned a row");

}



//insert one row into table using db1

sqlite3_exec(_db1, "INSERT into test(column1) values (4)", NULL,
NULL, );



//read row using db2

//get count of items from db2 -> should return 0

string selectStatement = "SELECT * FROM test";



//prepare statement

if(sqlite3_prepare_v2(_db2, selectStatement.c_str(), -1, , NULL)
!= SQLITE_OK){

GHAssertTrue(false, @"Should have created prepared statement:
%s", sqlite3_errmsg(_db1));

}



rc = sqlite3_step(stmt);

if(rc == SQLITE_ROW){

//get the count

if(sqlite3_column_count(stmt) > 0){

//good

}else{

//bad

GHAssertTrue(false, @"Should have returned a row");

}

}else{

   //bad

GHAssertTrue(false, @"Should have returned a row");

}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3: .import command handles quotation incorrectly

[Petite Abeille]

On Jun 25, 2013, at 11:19 AM, Nißl Reinhard  wrote:

> because it stays in quotation mode until it finds a further ", which is 
> incorrect. Quotation mode may only get activated when " appears at the 
> beginning of a column value.

Meh… check the recent "escape quote for csv import" thread…

As mentioned multiple time, by multiple people, on multiple occasions…


  7.  If double-quotes are used to enclose fields, then a double-quote
  appearing inside a field must be escaped by preceding it with
  another double quote.  For example:

  "aaa","b""bb","ccc"


http://tools.ietf.org/html/rfc4180
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VACUUM and PRAGMA temp_store

[Mario M. Westphal]

Great :-)
 
I guess that PRAGMA temp_store=MEMORY then does not add additional
performance on Windows and I can safely let it to DEFAULT or FILE.
This will avoid the excessive memory usage during VACUUM for my use case.
 
Thanks.
 
-- Mario
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3: .import command handles quotation incorrectly

[RSmith]

Correction - Official CSV fields may not start with specifically CR/LF before quotes as it is the official record delimeter for CSV, 
but it may well contain them within quotes in a single field.

Thanks.

On 2013/06/25 12:21, RSmith wrote:
...//it may also start with any whitespace characters such as CR, LF, TAB, VTAB, and of course Space itself or any 
number/combination//...




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3: .import command handles quotation incorrectly

[RSmith]

Hi Reinhard - studying this CSV formatting (and file-formatting in general) is somewhat of a hobby of mine and have solved many 
issues around it, so I can tell you that your request is a border case and found nowhere else in CSV realms. I think that whatever 
system is making the files you are trying to import is not actually creating CSV files, but just output using it's own format or 
another related format (Like SYLK etc) that happens to be delimited by commas.


In any official CSV format (and there are more than one, Holland for instance uses semi-colons as delimeters for official CSV) the 
quotation character is both standard and required. Consider the data section:

[123, "Rue de Caprice, Cordon's est. '99", Deschamps, Bordeaux, "1465""5", 
France]
Without the Quotes, the second field would look like two fields and the 4th field would contain insane amounts of quotes, and this 
is very VALID CSV, so it is quite impossible to create a CSV file without quoting or with a different quoting format even. Notice 
also that the fields essentially start with spaces, so the first character of the field is not always a quote in a quoted field 
value. It could well be a space, control character used in print formatting such as 0x0C or 0x0F (especially used in older 
dot-matrix applications), it may also start with any whitespace characters such as CR, LF, TAB, VTAB, and of course Space itself or 
any number/combination of them,  followed by a quote which starts the actual field.


It is also fully permissible to have comments in datasets of CSV such as [F1,"F2" This is a comment, F3] which if you put in a CSV 
file and load into a CSV program, like Excel for instance, would show up as: F1 F2 F3 and nothing else.


I have made quite a few file format interpreters/translators in my life, (even very fast Raw-Delimeted-->SQLite importers) of which 
I am very happy to send you for free to support whatever app is giving you these output purported to be CSV (but isn't).


A quick check to know the validation, is to simply take whatever output you have, save it to any file with .csv extension and then 
double-click it which will hopefully open it in Excel or some other Openoffice or whatever proper spreadsheet system you use, and if 
it isn't loaded correctly in there, then the input format is likely at fault.  I know ALL of these will break with an unquoted [ 19" 
rack ] entry amongst the fields for sure.


May I also ask you do this test before assuming it "an SQLite bug" in future when in fact SQLite works exactly as it should in these 
cases and, in fact, may not work any different.


Mit vielen dank und freundlichen grüßen
Ryan


On 2013/06/25 11:19, Nißl Reinhard wrote:

Hi,

for example, it cannot import the column value

 19" rack

because it stays in quotation mode until it finds a further ", which is incorrect. 
Quotation mode may only get activated when " appears at the beginning of a column 
value.

Once the line has been broken into column values, the import command properly 
handles that rule when dequoting each column value before inserting the row 
into the table.

Besides this bug, it would be nice if the quotation character could be 
specified like the separator, so that de-/quoting can even be turned off it the 
file hasn't been created appropriately. E. g. sqlserver bulk import does not 
support quoting, hence the files may not be created with quoting turned on.

Mit freundlichen Grüßen / Best regards

Reinhard Nißl
Softwareentwicklung



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3: .import command handles quotation incorrectly

[Nißl Reinhard]

Hi,

for example, it cannot import the column value

19" rack

because it stays in quotation mode until it finds a further ", which is 
incorrect. Quotation mode may only get activated when " appears at the 
beginning of a column value.

Once the line has been broken into column values, the import command properly 
handles that rule when dequoting each column value before inserting the row 
into the table.

Besides this bug, it would be nice if the quotation character could be 
specified like the separator, so that de-/quoting can even be turned off it the 
file hasn't been created appropriately. E. g. sqlserver bulk import does not 
support quoting, hence the files may not be created with quoting turned on.

Mit freundlichen Grüßen / Best regards

Reinhard Nißl
Softwareentwicklung

F.EE GmbH Informatik + Systeme
Industriestraße 6e, 92431 Neunburg  v. W.
Phone  +49 9672 506-198
Fax  +49 9672 506-10198
E-Mail: reinhard.ni...@fee.de
Internet: www.fee.de - 
www.fee-systemhaus.de - 
www.factwork.de

Geschäftsführung: J. u. G. Fleischmann
Geschäftssitz: Neunburg v. W., Amtsgericht Amberg HRB 1290
Diese E-Mail ist allein für den bezeichneten Adressaten bestimmt. Sie kann 
rechtlich vertrauliche Informationen enthalten. Wenn Sie diese E-Mail 
irrtümlich erhalten haben, informieren Sie bitte unverzüglich den Absender per 
E-Mail und löschen Sie diese E-Mail von Ihrem Computer, ohne Kopien 
anzufertigen. Vielen Dank.
This email is for the exclusive use of the addressee. It may contain legally 
privileged information. If you have received this message in error, please 
notify the sender by email immediately and delete the message from your 
computer without making any copies. Thank you.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users