[sqlite] 3.7.9 Import problem

[Udi Karni]

Hello,

I upgraded from 3.7.7 to 3.7.9 and suddenly the import of character columns
that contain commas from a comma delimited .csv file doesn't work.

The character columns are enclosed in double quotes and may contain commas
which are part of the data. Yet sqlite seems to take them as delimiters and
assumes I have an "extra" column in the .csv file.

What has changed? Any way to make sqlite ignore commas within the quotes
strings?

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

Re: [sqlite] SSD with TRIM

[Max Vlasov]

On Mon, Jan 16, 2012 at 12:29 AM, Black, Michael (IS) <
michael.bla...@ngc.com> wrote:

>
> On SSD with 16384 page size and no WAL mode:
>
> time sqlite3 gen.db 
> real4m4.816s ...
> Note: Database is only 595M with this page size.  Much more efficient
> storage may explain most of the time difference.
> ...
>
> On hard drive with 16384 page size and no WAL mode (now you can see the
> SSD latency advantage):
>
> time sqlite3 gen.db 
> real17m18.919s
>
>
Michael, thanks for the info, your tests confirms some advantages of ssd,
at least on some of the current platform (linux, can you confirm?).
Probably the page size is a parameter that also should be adjusted for
optimal performance. I suppose it can be different for different ssd
models, but it's just a guess.

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

[sqlite] result set column names, when row count = 0

[Larry Brasfield]

After a lengthy, fruitless search for an option to make the sqlite3 
shell do this, I've modified shell.c to emit a column header row, (after 
.headers ON), even when the result set is empty.


The reason for this is to facilitate creation of tab-separated-value, 
column-name-headed, text tables for import elsewhere.  It bugged me a 
lot that the shell came so close to being able to do it but seemed to 
treat an empty result as a special case.  (It presently will never emit 
a header when there are 0 result rows.)


Having done this work, in a careful manner, I wonder if I should offer a 
shell.c patch to make it available for others.  I imagine this has 
bothered others with similar purposes and expectations.  Alternatively, 
maybe somebody can briefly explain why it's really fine to sometimes get 
nothing when headers have been requested, in a non-interactive context.


To retain the old behavior with previously valid input, this behavior 
could be kludgedly activated by adding a third argument value for the 
.headers command, "ALWAYS" in addition to "ON" and "OFF".  Or a 
compile-time switch may be better.  (cleaner, certainly)  I have neither 
in place now because I never want to not see a header after entering 
.headers ON .


Even if nobody is much interested now, I'll be happy to provide this to 
anybody who stumbles across this post in the archives during a similar 
(but not so fruitless) search.  Just decode my email address and use it.


Cheers,
--
Larry Brasfield
firstinitial.lastn...@something.org
where computer is really something.

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

Re: [sqlite] SSD with TRIM

[Peter Aronson]

You know, on some platforms, such as Solaris, /tmp can be configured to use
memory instead of disk (called tmpfs on many unix variants).  Are you sure
your /tmp is actually using disk?  It's the default in a lot of setups.

Best 

Peter

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Black, Michael (IS)
> Sent: Sunday, January 15, 2012 12:35 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SSD with TRIM
> 
> I ran your test on my SSD system with 1M inserts.  I used WAL mode.
> 
> pragma journal_mode=WAL;
> CREATE TABLE [TestTable] (
> [Id] INTEGER PRIMARY KEY AUTOINCREMENT,
> [Text] TEXT
> );
> begin;
> insert.
> 
> end;
> 
> 
> 
> 
> 
> On SSD:
> 
> time sqlite3 gen.db  wal
> 
> real3m35.462s
> user2m14.126s
> sys 1m10.718s
> 
> On same machine but on /tmp
> 
> time sqlite3 gen.db  wal
> real3m44.259s
> user2m15.960s
> sys 1m9.437s
> 
> Or did you have some other test in mind?
> 
> 
> 
> Michael D. Black
> 
> Senior Scientist
> 
> Advanced Analytics Directorate
> 
> Advanced GEOINT Solutions Operating Unit
> 
> Northrop Grumman Information Systems
> 
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Max Vlasov [max.vla...@gmail.com]
> Sent: Saturday, January 14, 2012 1:24 PM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] SSD with TRIM
> 
> On Sat, Jan 14, 2012 at 6:12 AM, Simon Slavin 
> wrote:
> 
> >
> > Fast.  Fasty fast.  Speed is high.  INSERT, UPDATE, and DELETE all
> > significantly faster.  SELECT is a bit faster, but there's less
> difference.
> >
> >
> Simon, very interesting. Can you make some tests related to internal
> fragmentation? As an advanced user of sqlite,  you probably will easily
> invent your own tests :), but if not  there is a test I used at least once
> 
> INSERT INTO TestTable (id, text) VALUEs (abs(random() % (1 << 48)),
> '12345678901234567...') {the text is about 512 bytes long}
> 
> 1,000,000 inserts should create a 1GB base and the time with general hd
> was
> above 15 minutes, maybe even half an hour. So I'm very interested to see
> was there any progress with overall ssd performance.
> 
> Thanks,
> 
> Max
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] makefile for c

[Bill McCormick]

Tim Streater wrote, On 1/15/2012 3:00 PM:

On 15 Jan 2012 at 20:44, Bill McCormick  wrote:


What is the problem with the shared lib stuff?

Thanks!!
Black, Michael (IS) wrote, On 1/15/2012 2:27 PM:

A simple one -- and please compile sqlite3.c into your program and make
everybody happy.

Forget the shared library stuff as we have just been talking about.

The problem is that the computer vendor installs a shared lib with a version of 
the library. Some 3rd party app installer then replaced that shared lib with 
another version, and existing apps don't like it.

SQLite is small enough that it can be compiled and linked in in its entirety.

--
Cheers  --  Tim


OK. I don't see this as a problem for my use case. It would be better 
for me to stick with the shared libs.


What is the difference between the two libs: libsqlite.so.0 and 
libsqlite3.so.0? I assume that I'll be linking to libsqlite3.


What about options? Is there a makefile example out there somewhere?

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

Re: [sqlite] makefile for c

[Tim Streater]

On 15 Jan 2012 at 20:44, Bill McCormick  wrote: 

> What is the problem with the shared lib stuff?
>
> Thanks!!
> Black, Michael (IS) wrote, On 1/15/2012 2:27 PM:
>> A simple one -- and please compile sqlite3.c into your program and make
>> everybody happy.
>>
>> Forget the shared library stuff as we have just been talking about.

The problem is that the computer vendor installs a shared lib with a version of 
the library. Some 3rd party app installer then replaced that shared lib with 
another version, and existing apps don't like it.

SQLite is small enough that it can be compiled and linked in in its entirety.

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

Re: [sqlite] makefile for c

[Bill McCormick]

Sorry, I just joined the list so I'm not privy to anything "we have just 
been talking about".


I quick peek at the archives didn't reveal anything either.

Also, as I installed sqlite on Debian Squeeze with apt-get install (as 
opposed to compiling from source), I wouldn't have sqlite3.c to make 
sqlite3.o.


What is the problem with the shared lib stuff?

Thanks!!
Black, Michael (IS) wrote, On 1/15/2012 2:27 PM:

A simple one -- and please compile sqlite3.c into your program and make 
everybody happy.

Forget the shared library stuff as we have just been talking about.



CFLAGS=-O

OBJECTS=myapp.o sqlite3.o

LIBS=-lpthread -ldl

myapp:  $(OBJECTS)

 $(CC) -o $@ $(OBJECTS) $(LIBS)



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Bill McCormick [wpmccorm...@gmail.com]
Sent: Sunday, January 15, 2012 2:23 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] makefile for c

I'm looking for an example c program makefile for compiling and linking
in the SQLite lib to gcc compiled programs.

I'm not sure which lib to include between libsqlite.so.0 and
libsqlite3.so.0 and what options I should pass to gcc.

Where is this documented?

Thanks!!
___
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SSD with TRIM

[Black, Michael (IS)]

I also ran without WAL mode (about 3X slower than WAL mode)

On SSD: (system spent a LOT of time in disk wait states)

time sqlite3 gen.db  wrote:

>
> Fast.  Fasty fast.  Speed is high.  INSERT, UPDATE, and DELETE all
> significantly faster.  SELECT is a bit faster, but there's less difference.
>
>
Simon, very interesting. Can you make some tests related to internal
fragmentation? As an advanced user of sqlite,  you probably will easily
invent your own tests :), but if not  there is a test I used at least once

INSERT INTO TestTable (id, text) VALUEs (abs(random() % (1 << 48)),
'12345678901234567...') {the text is about 512 bytes long}

1,000,000 inserts should create a 1GB base and the time with general hd was
above 15 minutes, maybe even half an hour. So I'm very interested to see
was there any progress with overall ssd performance.

Thanks,

Max
___
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] makefile for c

[Black, Michael (IS)]

A simple one -- and please compile sqlite3.c into your program and make 
everybody happy.

Forget the shared library stuff as we have just been talking about.



CFLAGS=-O

OBJECTS=myapp.o sqlite3.o

LIBS=-lpthread -ldl

myapp:  $(OBJECTS)

$(CC) -o $@ $(OBJECTS) $(LIBS)



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Bill McCormick [wpmccorm...@gmail.com]
Sent: Sunday, January 15, 2012 2:23 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] makefile for c

I'm looking for an example c program makefile for compiling and linking
in the SQLite lib to gcc compiled programs.

I'm not sure which lib to include between libsqlite.so.0 and
libsqlite3.so.0 and what options I should pass to gcc.

Where is this documented?

Thanks!!
___
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] makefile for c

[Bill McCormick]

I'm looking for an example c program makefile for compiling and linking 
in the SQLite lib to gcc compiled programs.


I'm not sure which lib to include between libsqlite.so.0 and 
libsqlite3.so.0 and what options I should pass to gcc.


Where is this documented?

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

Re: [sqlite] Incompatible versions of SQLite on same system

[Black, Michael (IS)]

My point still standsyou can test the application compatibility by copying 
the DLL into the app directory and changing the search order as I recommended.  
Did you try that?



The applications really need to compile sqlite in their app.  That's the good 
fix here as has been pointed out (and something I always do).  But good luck 
with that one.





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Joe Winograd [j...@winograd.us]
Sent: Sunday, January 15, 2012 1:52 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Incompatible versions of SQLite on same system

> I don't really mind top or bottom post.

I don't either, but it really should be one or the other; otherwise, you're
jumping up and down to follow a thread. But that's because of the next
point, where we disagree completely.

 > But the important thing is to trim anything you've quoted to just the
useful bits. We don't need to see every single post to the thread every time
someone adds a new post.

I strongly disagree with this. In personal (one-on-one) correspondence, it
makes sense, but not in email-based groups/forums, where members come and go
at various points in time. A member should be able to jump into a thread and
easily review the whole issue in one email. Having to go back and look at
every trimmed message to piece together the entire thread is painful and,
frankly, won't usually be done. This is also why top posting is better. The
combination of the two (NOT trimming and top posting) means that a new
entrant to the discussion can review the entire thread in one email
(admittedly, having to page-up) while someone who has been participating in
the discussion for a while can simply look at the top-posted most recent
response(s).

I think we actually have an example of the trimming problem in this thread.
I may be wrong, and Michael Black may jump in to say I'm wrong, but a key
comment of mine had been trimmed, viz., the  doesn't even
appear in the application directory of the two conflicting apps. If Michael
had seen that in a non-trimmed message, I'm guessing he would not have said,
"Can't you just copy the DLL into the application directory?" Come to think
of it, by the time it got to Michael, "TurboTax" and "HP Connection Manager"
(the two conflicting programs) had been trimmed out of the message. I would
argue that those are "useful bits" of this thread.

I think there are times when trimming is appropriate, but in most cases, I
think that threads should be left intact. Just my humble, of course. Cheers, Joe

 Original Message 
Subject: Re: [sqlite] Incompatible versions of SQLite on same system
From: Simon Slavin 
To: General Discussion of SQLite Database 
Date: Thursday, January 12, 2012 19:54:56
> On 13 Jan 2012, at 1:46am, Joe Winograd wrote:
>
>> Thanks for the clarification.
> My pleasure.  Triggers do generally work in the most useful way.  Try coding 
> it and see if it works for you.
>
>> Btw, is bottom-posting the standard in this group? As you can tell, I'm 
>> rather fond of top-posting. Yes, I've ready many of the arguments why 
>> bottom-posting is better – I simply don't buy it. But I'll be happy to 
>> comply with group standards.
> Bottom posting is the proper way to do it.  I don't really mind top or bottom 
> post.  But the important thing is to trim anything you've quoted to just the 
> useful bits.  We don't need to see every single post to the thread every time 
> someone adds a new post.
>
> 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] Incompatible versions of SQLite on same system

[Joe Winograd]

> I don't really mind top or bottom post.

I don't either, but it really should be one or the other; otherwise, you're 
jumping up and down to follow a thread. But that's because of the next 
point, where we disagree completely.


> But the important thing is to trim anything you've quoted to just the 
useful bits. We don't need to see every single post to the thread every time 
someone adds a new post.


I strongly disagree with this. In personal (one-on-one) correspondence, it 
makes sense, but not in email-based groups/forums, where members come and go 
at various points in time. A member should be able to jump into a thread and 
easily review the whole issue in one email. Having to go back and look at 
every trimmed message to piece together the entire thread is painful and, 
frankly, won't usually be done. This is also why top posting is better. The 
combination of the two (NOT trimming and top posting) means that a new 
entrant to the discussion can review the entire thread in one email 
(admittedly, having to page-up) while someone who has been participating in 
the discussion for a while can simply look at the top-posted most recent 
response(s).


I think we actually have an example of the trimming problem in this thread. 
I may be wrong, and Michael Black may jump in to say I'm wrong, but a key 
comment of mine had been trimmed, viz., the  doesn't even 
appear in the application directory of the two conflicting apps. If Michael 
had seen that in a non-trimmed message, I'm guessing he would not have said, 
"Can't you just copy the DLL into the application directory?" Come to think 
of it, by the time it got to Michael, "TurboTax" and "HP Connection Manager" 
(the two conflicting programs) had been trimmed out of the message. I would 
argue that those are "useful bits" of this thread.


I think there are times when trimming is appropriate, but in most cases, I 
think that threads should be left intact. Just my humble, of course. Cheers, Joe


 Original Message 
Subject: Re: [sqlite] Incompatible versions of SQLite on same system
From: Simon Slavin 
To: General Discussion of SQLite Database 
Date: Thursday, January 12, 2012 19:54:56

On 13 Jan 2012, at 1:46am, Joe Winograd wrote:


Thanks for the clarification.

My pleasure.  Triggers do generally work in the most useful way.  Try coding it 
and see if it works for you.


Btw, is bottom-posting the standard in this group? As you can tell, I'm rather 
fond of top-posting. Yes, I've ready many of the arguments why bottom-posting 
is better – I simply don't buy it. But I'll be happy to comply with group 
standards.

Bottom posting is the proper way to do it.  I don't really mind top or bottom 
post.  But the important thing is to trim anything you've quoted to just the 
useful bits.  We don't need to see every single post to the thread every time 
someone adds a new post.

Simon.



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

Re: [sqlite] SSD with TRIM

[Black, Michael (IS)]

I ran your test on my SSD system with 1M inserts.  I used WAL mode.

pragma journal_mode=WAL;
CREATE TABLE [TestTable] (
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Text] TEXT
);
begin;
insert.

end;





On SSD:

time sqlite3 gen.db  wrote:

>
> Fast.  Fasty fast.  Speed is high.  INSERT, UPDATE, and DELETE all
> significantly faster.  SELECT is a bit faster, but there's less difference.
>
>
Simon, very interesting. Can you make some tests related to internal
fragmentation? As an advanced user of sqlite,  you probably will easily
invent your own tests :), but if not  there is a test I used at least once

INSERT INTO TestTable (id, text) VALUEs (abs(random() % (1 << 48)),
'12345678901234567...') {the text is about 512 bytes long}

1,000,000 inserts should create a 1GB base and the time with general hd was
above 15 minutes, maybe even half an hour. So I'm very interested to see
was there any progress with overall ssd performance.

Thanks,

Max
___
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