Re: [sqlite] Tedious CSV import question

[BareFeet]

On 26/09/2009, at 3:33 AM, Wilson, Ronald wrote:

> the RFC says that everything between the commas is supposed to be  
> part of the field, including white space.  Normally I trim the white  
> space unless it's quoted.

You can certainly offer the option to trim whitespace or change case  
or correct spelling mistakes, but that's after doing the CSV import  
technically correctly.

> Still, the RFC does not address how to handle rows like this:
>
> 1234,abc"123",abc
> 1235,""123,abc
>
> What are you supposed to do with those?  It is not clear.

You should generate an error, in the same way as you would generate an  
error if an XML tag missed a closing tag or if SQL was missing a  
closing bracket before the end of line. All syntax has definitions  
within which the data must conform or be rejected.

> Also, are you supposed to strip the quotes upon consuming the field?

Yes, in the same way as you strip the XML tags when pulling XML data  
into an array.

> Are you supposed to un-escape escaped quotes?

Yes, that's the point.

> "1234" -> 1234 or "1234" ?

1234

> "15""" -> 15" or 15"" or "15""" or "15"" ?

15"

> Seems to me if you strip quotes, you have to un-escape any escaped  
> quotes in the field.

Correct.

> Then there is the matter of white space outside the quotes.  The RFC  
> seems silent on all these issues, though the ABNF grammar implies  
> that white space outside quotes is not tolerated, which could lead  
> to considerable user surprise.

Technically:

1234,abc"123",abc

does not conform to CSV, as answered above. Similarly:

1234,   "123",abc

does not conform, but I think most importers will tolerate white space  
outside the quotes and ignore it.

Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml

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

Re: [sqlite] ROWID bug in SELECT with JOIN?

[BareFeet]

On 26/09/2009, at 7:35 AM, sub sk79 wrote:

> Select with two tables joined has no _rowid_ column in select columns.
> I think this is a bug - just a quick check with the community before
> filing a bug report.
>
> select * from names, friends where first_name = fname and _rowid_ < 2;
> SQL error: no such column: _rowid_

select *

by default does not include the rowid column. You need to request it  
specifically, like this:

select rowid, *

If you are joining tables in your select, you need to specify which  
rowid you want in the output, such as:

select names.rowid as rowid, * from names, friends...

Beware that this can return multiple rows with the same rowid, if a  
name has more than one friend.

Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml

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

Re: [sqlite] Someone knows about an ETL tool in foreign languages?

[John Machin]

On 24/09/2009 12:02 AM, hfdabler wrote:
> 
> Hello to all, 
> 
> Being in a pretty much international company, I have come here to ask a few
> things about ETL tools and their different languages. 

Why? The principal focus of this mailing list is SQLite and its C APIs, 
not ETL.

> 
> We have offices in the US, in Europe (Italy, France) and in China. We think
> English is fine but our European team and Chinese team especially would like
> to get software in their language. 
> 
> What we are trying to find is an ETL program to perform data integration,
> data synchronization and deduplication on our database in French, Italian
> and especially in Chinese. 

I presume that you mean that the /user interface/ should work in those 
languages. To handle Chinese /data/, you may need to source a separate 
program in China ... a Western-origin program would need a 
Chinese-specific deduplication module, developed with the aid of Chinese 
experts -- soundex, metaphone, Jaro-Winkler, Levenshtein and such-like 
don't grok non-alphabetic languages.

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

Re: [sqlite] Tedious CSV import question

[John Machin]

On 26/09/2009 5:38 AM, Petite Abeille wrote:
> On Sep 25, 2009, at 9:21 PM, C. Mundi wrote:
> 
>> Your post neatly articulates virtually every facet of this issue.
>> Thank you.  I wish we could get everyone to stop using csv.  I hate to
>> look at xml but I often wish everyone would use it instead of csv.
> 
> In fact, in Switzerland, there is a federal law mandating the use of  
> XML 5NF for all data transmission, irrespectively of their purpose.  
> Namespaces have to be properly encoded in the 4 officially recognized  
> languages.

"encoded in a language" is a novel concept. In how many? All 4? Or one 
of the sender's choice? Sending all of your data out with namespaces 
"encoded" only in Romansch seems like a brilliant idea :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[C. Mundi]

Cool.  Where do I send my application (for citizenship)? :)

Carlos

On 9/25/09, Petite Abeille  wrote:
>
> On Sep 25, 2009, at 9:21 PM, C. Mundi wrote:
>
>> Your post neatly articulates virtually every facet of this issue.
>> Thank you.  I wish we could get everyone to stop using csv.  I hate to
>> look at xml but I often wish everyone would use it instead of csv.
>
> In fact, in Switzerland, there is a federal law mandating the use of
> XML 5NF for all data transmission, irrespectively of their purpose.
> Namespaces have to be properly encoded in the 4 officially recognized
> languages.
>
> ___
> 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] Tedious CSV import question

[John Machin]

On 26/09/2009 5:03 AM, Wilson, Ronald wrote:
>
> 
> Yeah.  The clearest thing in the RFC is the ABNF grammar.  However, even
> that leaves out common cases like white space outside of quoted fields,
> which most people would expect to be trimmed.  Also, I think most people
> would expect leading/trailing white space to be trimmed, even in
> unquoted fields.

No, thanks.

(1) Whitespace (space? CR? TAB? LF? NBSP??) outside of a quoted string 
is a violation of the writing rules for de-facto standard CSV. If the 
writer has used a different set of rules, then the reader should use 
that set of rules.

(2) Trimming leading/trailing whitespace from unquoted fields? Excuse 
me, if CSV is the only format I can get data in, I don't want any 
wiseguy messing with it -- I want to see what exactly is in the data. Do 
XML parsers strip whitespace? Stripping leading/trailing whitespace (and 
compressing whitespace runs into a single space) should be /optional/ 
functionality to be applied (if desired) /after/ input, irrespective of 
the input format.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Dan Bishop]

C. Mundi wrote:
> On 9/25/09, Jay A. Kreibich  wrote:
>   
>> ...
>>
>>   CSV is a great quick and dirty format to move data.  But it isn't
>>   "simple" and it isn't nearly as universal as many assume.  It works
>>   great if you're just moving simple numbers and strings that don't
>>   include commas, but becomes a mess when you get into exceptions.
>>
>>   Personally, I'd rather have the SQLite team working on core database
>>   features than trying to build a better CSV parser.  The problem
>>   is non-trivial and borders on unobtainable and, as Carlos
>>   proved so clearly, there are better, easier, faster ways.
>> 
> Jay...
>
> Your post neatly articulates virtually every facet of this issue.
> Thank you.  I wish we could get everyone to stop using csv.  I hate to
> look at xml but I often wish everyone would use it instead of csv.  I
> would hate to see any of the sqlite core devs  waste time on csv.
>
> Carlos
>   

But XML has its own difficulties with SQL data sets.  For example, how 
do you distinguish between NULL and the empty string?  And then there's 
the obvious inefficiency in writing the column name twice for each row.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Simon Slavin]

On 26 Sep 2009, at 1:40am, BareFeet wrote:

> As above, I think it is pretty simple. The complexity arrives when
> people expect CSV to provide more than a simple array of strings.

One of my free programs has to read CSV files  (

http://www.hearsay.demon.co.uk/mac/MacGarminTools/csv2gpx.html

if you care).  The documentation for the program doesn't mention any  
of the bizarre things people expect to work when they use it.  For  
about a year after I released the program I got the occasional email  
from people who had been trying to use it with a CSV file which JUST  
SHOULD WORK, DAMMIT.  They had all sorts of bizarre things in their  
files, from methods of escaping characters which I'd never heard of,  
including return characters in fields (For describing GPS points ?   
You really need more than one line of text ?), and marking header and  
comment lines in all sorts of weird ways.  Quite a lot of them could  
point to some big data publisher which was using their weird format as  
if it was well-known.  Also some files are in Latin 1 and some are in  
UTF-8.

Consequently, my software deals with all sorts of weird things in CSV  
files and very few of them are mentioned in the documentation.  I have  
no doubt that much commercial software does the same thing: contains  
tweaks wanted by one user which would be too ridiculous to document  
and create too many opportunities for requests for backward  
compatibility.

I'm fortunate that that software is seldom used these days, now that  
the manufacturer supplies software which performs that function.  I  
suspect that three years later I'd still be getting requests to  
include a new CSV dodge.

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

Re: [sqlite] Tedious CSV import question

[BareFeet]

On 26/09/2009, at 12:00 AM, Jay A. Kreibich wrote:

> On Fri, Sep 25, 2009 at 10:24:15AM +1000, BareFeet scratched on the  
> wall:
>
>> In reality, in the thousands of CSV files I've dealt with
>> over the years, they all follow the same standard:
>>
>> 1. Rows are delimited by a new line (return and/or line feed).
>> 2. Columns are delimited by a comma.
>> 3. "Quoted text" is treated as one value item, including any commas  
>> or
>> new lines within it.
>> 4. A double quote "" is used to put a quote within quotes.
>>
>> That's it.
>
> This is more or less the standard put forth by RFC 4180.  And if  
> this is all you've encountered

Yes.

> you're not using very many different applications

I guess "many" is subjective but I would say I have used many,  
including Tandem main frame outputs, SQL server outputs, various  
product list suppliers, Excel Exports, electron microscope date, etc.  
I don't doubt that you've dealt with some strange CSV files, but I  
think the number of files defined as "strange" reduces if you better  
define the CSV format.

Please understand that I am not arguing that CSV should be used. But  
the fact is that CSV is a commonly used data export/import format (I'd  
say around 50%) and therefore supporting it is a significant  
requirement.

> or you're primarily dealing with numbers and simple strings that  
> don't contain quotes or commas.  CSV works very very well if you  
> never get into the question of escapes, but details count.

As I mentioned previously in points 3 and 4 above, I do deal with  
strings that contain quotes and commas. Dealing with them is well  
defined in practical use (or my experience of it).

> Reading the RFC only proves my point.  SQLite v3 is older than that  
> doc, and it pretty much admits the cat was out of the bag a long  
> time ago.  There are a ton of optional and might/may/could sections  
> that event

I accept that some earlier CSV implementations varied and that the RFC  
may have been in catch up mode. But I summarise that as a delay in  
setting the standard and thankfully not so much a problem with  
everyday use since most/all major current implementations use the  
specifications I outlined above (4 point summary).

> the format they define has a lot of holes in it

I don't think there are holes, within the scope of the data with which  
it deals. For instance, CSV doesn't deal with blob data, pictures,  
related tables, column type (even the distinction between a numeric  
and text value) and therefore has no definition for it.

> (i.e. headers, or no headers?).

Again, as I understand it, headers are outside of the CSV scope.

CSV only defines an array of cells of string data, not column  
definitions. If headers are included within CSV they are just another  
row in the array. CSV doesn't know what they are but an importer  
should import them into the array. After importing the raw data into  
an array, the program can then interpret the data in ways that makes  
sense to it. If the program understands headers then it should look  
for them in the array. If it understands column types, it should apply  
them, etc.

Since we're coming from the SQLite perspective, data means more to us  
than what CSV defines. Concepts such as column type, cell type are  
everyday definitions for SQLite but are meaningless to CSV. In most  
cases, we pre-define a table within SQLite specifically for receiving  
data from a particular CSV data file. CSV defines just the array of  
data.

>> Everything I've seen uses this.
>
> According to the RFC Excel doesn't use double-quotes for anything.  
> You might not care about Excel, but I'm willing to bet it is one of  
> the most-- if not the most-- common exporters of CSV.  The question  
> of getting data from Excel into SQLite shows up on the list every  
> now and then.

Yes, Excel is a major consideration and I have to deal with data from  
Excel exporters often. In my experience, though, the specification I  
outlined above deals with Excel exports fine. I can't comment on the  
RFC which may or may not match common use.

>> Some don't need delimiters in values, so they don't need quotes,  
>> but the encompassing specification works for all cases.
>
> No, it doesn't.  Working on a large website that provided CSV  
> exports for a number of data sources, I've seen plenty of examples  
> that don't work.  Finding a common format that could be exported  
> into a handful of common desktop apps was so pointless we seriously  
> considered getting rid of CSV all together, because we got tired of  
> our users telling us how simple CSV was, and why couldn't we just do  
> this one thing differently so it would work on *their* application.

This surprises me. Did you cater for escapes as mentioned in points 3  
and 4 above? You mentioned:

> CSV works very very well if you never get into the question of escapes

So I wonder if you did.

Also, perhaps the users were asking for specific data within 

Re: [sqlite] Tedious CSV import question

[Fred Williams]

If you have a file that conforms fully to the RFC, I honestly don't see the
problem.  Your examples do not meet the RFC rules.  If the rule does not
address leading spaces outside the quoted text, then the input file should
not contain spaces there as they will be ignored.

Over the years I have written several CSV parsers in three of four different
languages at least and have never had a problem parsing a VALID CSV file.  I
have never used the SQLite command line utility for such so cannot judge its
functionally.  I use my own parser or someone else's'.

Fred

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Wilson, Ronald
Sent: Friday, September 25, 2009 1:38 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Tedious CSV import question


> > Still, the RFC does not
> > address how to handle rows like this:
> >
> > 1234,abc"123",abc
> > 1235,""123,abc
>
> Both violate the format as defined.  Generate an error message or
> interpret them any way that suits you.
>
> > What are you supposed to do with those?  It is not clear.  Also, are
> > you
> > supposed to strip the quotes upon consuming the field?  Are you
> > supposed
> > to un-escape escaped quotes?
> >
> > "1234" -> 1234 or "1234" ?
>
> 1234
>
> > "15""" -> 15" or 15"" or "15""" or "15"" ?
>
> 15"
>
> Simon.

I can appreciate common sense answers, but the RFC doesn't specify those
answers.  The RFC seems to only address the formatting of the CSV file,
not the consumption of it.
___
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] SQLite Suitability for Shopping Cart

[Fred Williams]

Thanks Dr.

I already read that along time ago and fully understand it 100%.  Seems many
others either do not read or do not understand simple straight forward
descriptions of basic functionality.  Or just maybe they ask anyway hoping
someone will say well why not? :-)

Fred
120% happy with SQLite.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org]on Behalf Of D. Richard Hipp
Sent: Friday, September 25, 2009 1:12 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite Suitability for Shopping Cart



On Sep 25, 2009, at 1:13 PM, CityDev wrote:

>
>
> Fred Williams-5 wrote:
>>
>> no further need to ask and answer, "Will SQLite support
>> multi users?
>>
> Maybe it should be covered a bit more specifically on the SQLite site,

http://www.sqlite.org/whentouse.html

> bearing in mind that new people would naturally have the mindset that
> databases are for shared use normally. SQLite does have sharing
> capabilities
> so it would be helpful to profile these for novices.
> --
> View this message in context:
http://www.nabble.com/SQLite-Suitability-for-Shopping-Cart-tp25530589p256157
24.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



___
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] ROWID bug in SELECT with JOIN?

[Igor Tandetnik]

sub sk79  wrote:
> select *  from (select * from names, friends where first_name = fname)
> where _rowid_ < 2;
>
> Above query works - which _rowid_ is this accessing?

Well, define "works". In my experiments, _rowid_ is NULL for all rows in 
the subselect:

select typeof(_rowid_) from (select * from names, friends where 
first_name = fname);

But the fact that this statement compiles at all looks like a bug to me 
(though a very minor and unimportant one).


Now, why do you care about these corner cases? Do you have a practical 
problem you are trying to solve?

Igor Tandetnik 



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

Re: [sqlite] ROWID bug in SELECT with JOIN?

[sub sk79]

select *  from (select * from names, friends where first_name = fname)
 where _rowid_ < 2;

Above query works - which _rowid_ is this accessing?
Since the query works I get an impression the rowid is attached with the
result-set returned by a select?

-sk


On Fri, Sep 25, 2009 at 6:07 PM, Igor Tandetnik  wrote:
> sub sk79  wrote:
>> Select with two tables joined has no _rowid_ column in select columns.
>> I think this is a bug - just a quick check with the community before
>> filing a bug report.
>>
>> select * from names, friends where first_name = fname and _rowid_ < 2;
>> SQL error: no such column: _rowid_
>
> So, which table's _rowid_ is this statement supposed to access? The
> statement is wrong and shouldn't run. The only issue here is that the
> error message is somewhat misleading - it could probably say something
> like "ambiguous column name".
>
> Igor Tandetnik
>
>
>
> ___
> 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] changes to how quoted phrases are handled in fts queries involving table columns?

[Seth Spitzer]

In previous versions of SQLite (3.5.4.2), the following worked as expected:

CREATE VIRTUAL TABLE cities USING fts3(name, state, nickname);
INSERT into cities (name,state,nickname) VALUES ("new york", "ny", "big 
apple");
INSERT into cities (name,state,nickname) VALUES ("apple town", "xx", 
"big time");
SELECT * FROM cities WHERE cities MATCH 'nickname:"big apple"'

Upon upgrading to SQLite 3.6.16, this fails and sqlite3_step() returns 
SQLITE_ERROR.

 From the code:

   /* If we reach this point, it means that execution is finished with
   ** an error of some kind.
   */
vdbe_error_halt:
   assert( rc );
   p->rc = rc;
   sqlite3VdbeHalt(p);
   if( rc==SQLITE_IOERR_NOMEM ) db->mallocFailed = 1;
   rc = SQLITE_ERROR;

 From 
http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/fts3/README.syntax

 MATCH 'col1:hello col2:world'

   It is not possible to associate a specific table column with a quoted
   phrase or a term preceded by a '-' operator. A '*' character may be
   appended to a term associated with a specific column for prefix 
matching.

I assume this no longer works because it "is not possible to associate a 
specific table column with a quoted phrase".

Any idea when this changed or which ticket is associated with it?  Any 
suggestions on how I can change my query to achieve the desired result?

Note that:

SELECT * FROM cities WHERE cities MATCH 'nickname:big apple'

will not work as that will return both cities.

Thanks in advance,

-Seth

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

Re: [sqlite] ROWID bug in SELECT with JOIN?

[Igor Tandetnik]

sub sk79  wrote:
> Select with two tables joined has no _rowid_ column in select columns.
> I think this is a bug - just a quick check with the community before
> filing a bug report.
>
> select * from names, friends where first_name = fname and _rowid_ < 2;
> SQL error: no such column: _rowid_

So, which table's _rowid_ is this statement supposed to access? The 
statement is wrong and shouldn't run. The only issue here is that the 
error message is somewhat misleading - it could probably say something 
like "ambiguous column name".

Igor Tandetnik 



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

Re: [sqlite] SQLite Suitability for Shopping Cart

[Marcus Grimm]

I would like to add also a comment here:

Sqlite perfectly works as a backend for multi
user applications/servers IF they are implemented accordingly.
That is, avoid multible processes/instances that access
the same db file, use one main process and threads instead.
Take care about busy handling etc.
Under this background my application (a client server
database) does work very well using sqlite. I have installations
with 20 user connected to my server, no problem. -- stop.

I hope that such sentenses like "sqlite is not intended
for client/server applications" will be removed from
the sqlite pages as it is too "shy" and to general.
One example, I mentioned using sqlite to one of our partners
and of course they looked at the web pages of sqlite and
found the above statement -- now I run into a defense
issue and have to explain and argue for sqlite.
As a result, I stop explaining that we are using sqlite
because I don't want that customers start to worry
about the sql back end.

Just to share my feeling.. sqlite is a wonderful tool, stop
making it smaller than it is, it doesn't deserve it.

Marcus

>
>
> Fred Williams-5 wrote:
>>
>>  no further need to ask and answer, "Will SQLite support
>> multi users?
>>
> Maybe it should be covered a bit more specifically on the SQLite site,
> bearing in mind that new people would naturally have the mindset that
> databases are for shared use normally. SQLite does have sharing
> capabilities
> so it would be helpful to profile these for novices.
> --
> View this message in context:
> http://www.nabble.com/SQLite-Suitability-for-Shopping-Cart-tp25530589p25615724.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> 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] Tedious CSV import question

[Petite Abeille]

On Sep 25, 2009, at 9:21 PM, C. Mundi wrote:

> Your post neatly articulates virtually every facet of this issue.
> Thank you.  I wish we could get everyone to stop using csv.  I hate to
> look at xml but I often wish everyone would use it instead of csv.

In fact, in Switzerland, there is a federal law mandating the use of  
XML 5NF for all data transmission, irrespectively of their purpose.  
Namespaces have to be properly encoded in the 4 officially recognized  
languages.

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

Re: [sqlite] Tedious CSV import question

[C. Mundi]

Jay...

Your post neatly articulates virtually every facet of this issue.
Thank you.  I wish we could get everyone to stop using csv.  I hate to
look at xml but I often wish everyone would use it instead of csv.  I
would hate to see any of the sqlite core devs  waste time on csv.

Carlos


On 9/25/09, Jay A. Kreibich  wrote:
> On Fri, Sep 25, 2009 at 10:24:15AM +1000, BareFeet scratched on the wall:
>
>
>> In reality, in the thousands of CSV files I've dealt with
>> over the years, they all follow the same standard:
>>
>> 1. Rows are delimited by a new line (return and/or line feed).
>> 2. Columns are delimited by a comma.
>> 3. "Quoted text" is treated as one value item, including any commas or
>> new lines within it.
>> 4. A double quote "" is used to put a quote within quotes.
>>
>> That's it.
>
>   This is more or less the standard put forth by RFC 4180.  And if this
>   is all you've encountered, you're not using very many different
>   applications or you're primarily dealing with numbers and simple
>   strings that don't contain quotes or commas.  CSV works very very
>   well if you never get into the question of escapes, but details count.
>
>   Reading the RFC only proves my point.  SQLite v3 is older than that
>   doc, and it pretty much admits the cat was out of the bag a long time
>   ago.  There are a ton of optional and might/may/could sections that
>   event the format they define has a lot of holes in it (i.e. headers,
>   or no headers?).
>
>> Everything I've seen uses this.
>
>   According to the RFC Excel doesn't use double-quotes for anything.
>   You might not care about Excel, but I'm willing to bet it is one of
>   the most-- if not the most-- common exporters of CSV.  The question
>   of getting data from Excel into SQLite shows up on the list every now
>   and then.
>
>> Some don't need delimiters
>> in values, so they don't need quotes, but the encompassing
>> specification works for all cases.
>
>   No, it doesn't.  Working on a large website that provided CSV exports
>   for a number of data sources, I've seen plenty of examples that don't
>   work.  Finding a common format that could be exported into a handful
>   of common desktop apps was so pointless we seriously considered
>   getting rid of CSV all together, because we got tired of our users
>   telling us how simple CSV was, and why couldn't we just do this one
>   thing differently so it would work on *their* application.
>
>> It's not that big a deal for SQLite to support it, so it should.
>
>   If it is so simple, and you know where the code is...
>
>   This is, perhaps, the biggest fallacy of CSV... people think it
>   is a "simple" format (it isn't), and assume that code support to
>   "correctly" (whatever that is) read it is simple.  It isn't.  The RFC
>   has a formal grammar that requires over a dozen elements to define!
>
>   Most people setting out to build a CSV reader never think to use a
>   full grammar and parser-- after all, it is such a "simple" format--
>   and find themselves in a mess of code soon enough.  Seriously, give
>   it a try.
>
>   Carlos's Python script (nice!) is a great example.  His comment "I am
>   so grateful I did not have to write a parser for CSV" is dead on.
>   And, as he points out, the reason the Python module is so good is
>   that it is adaptive, and really reads five or six different variants
>   of CSV (something a reader can do but a writer cannot).  He was
>   also able to clobber it all together in a few hours or less (because
>   someone else spent a few hundred hours on the CSV module), further
>   proving that advanced support of this kind of thing is really outside
>   of the scope of SQLite3.  After all, the .import command is part of
>   the shell, not part of the core library.
>
>
>
>
>   CSV is a great quick and dirty format to move data.  But it isn't
>   "simple" and it isn't nearly as universal as many assume.  It works
>   great if you're just moving simple numbers and strings that don't
>   include commas, but becomes a mess when you get into exceptions.
>
>   Personally, I'd rather have the SQLite team working on core database
>   features than trying to build a better CSV parser.  The problem
>   is non-trivial and borders on unobtainable and, as Carlos
>   proved so clearly, there are better, easier, faster ways.
>
>-j
>
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> ___
> 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] TH3 license

[D. Richard Hipp]

On Sep 25, 2009, at 3:05 PM, Rich Shepard wrote:

> On Fri, 25 Sep 2009, Clifford Hung wrote:
>
>> How do I obtain a TH3 license?
>
>   I've no idea what a TH3 license is, and Google didn't help any.

http://www.sqlite.org/th3.html

Clifford:  Please call the SQLite.org offices at 704.948.4565 during  
east-coast business hours to enquire about a TH3 license. Or send  
email to d...@sqlite.org.

>
>   If you want to use SQLite for some purpose read
>
> and you'll see that it's in the public domain (for all countries that
> recognize that concept).
>
> Rich
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] TH3 license

[Clifford Hung]

I'm referring to the license needed to access the TH3 tests, please see 
http://www.sqlite.org/th3.html.

> Date: Fri, 25 Sep 2009 12:05:40 -0700
> From: rshep...@appl-ecosys.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] TH3 license
> 
> On Fri, 25 Sep 2009, Clifford Hung wrote:
> 
> > How do I obtain a TH3 license?
> 
>Perhaps go to the local DMV office and wait in line for several hours?
> 
>I've no idea what a TH3 license is, and Google didn't help any.
> 
>If you want to use SQLite for some purpose read
> 
> and you'll see that it's in the public domain (for all countries that
> recognize that concept).
> 
> Rich
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Internet explorer 8 lets you browse the web faster.
http://go.microsoft.com/?linkid=9655582
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TH3 license

[Rich Shepard]

On Fri, 25 Sep 2009, Clifford Hung wrote:

> How do I obtain a TH3 license?

   Perhaps go to the local DMV office and wait in line for several hours?

   I've no idea what a TH3 license is, and Google didn't help any.

   If you want to use SQLite for some purpose read

and you'll see that it's in the public domain (for all countries that
recognize that concept).

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

Re: [sqlite] Tedious CSV import question

[Wilson, Ronald]

> > I read the RFC last night
> 
> Oh, my...
> 
> Programming in Lua has a nice, concise example regarding CSV parsing
> (near the end of the page):
> 
> http://www.lua.org/pil/20.4.html
> 
> Quote:
> 
> To break a CSV into an array is more difficult, because we must avoid
> mixing up the commas written between quotes with the commas that
> separate fields. We could try to escape the commas between quotes.
> However, not all quote characters act as quotes; only quote characters
> after a comma act as a starting quote, as long as the comma itself is
> acting as a comma (that is, it is not between quotes). There are too
> many subtleties. For instance, two quotes may represent a single
> quote, two quotes, or nothing:
> 
> "hello""hello", "",""
> 
> Clear as mud? :D

Yeah.  The clearest thing in the RFC is the ABNF grammar.  However, even
that leaves out common cases like white space outside of quoted fields,
which most people would expect to be trimmed.  Also, I think most people
would expect leading/trailing white space to be trimmed, even in
unquoted fields.

I think I'll break out my flex/lemon tools tonight and make a CSV
parser.

RW 

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

HARRIS CORPORATION   |   RF Communications Division
assuredcommunications(tm)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] TH3 license

[Clifford Hung]

Hi,

How do I obtain a TH3 license?

Thanks,
Clifford
  
_
We are your photos. Share us now with Windows Live Photos.
http://go.microsoft.com/?linkid=9666047
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Petite Abeille]

On Sep 25, 2009, at 7:33 PM, Wilson, Ronald wrote:

> I read the RFC last night

Oh, my...

Programming in Lua has a nice, concise example regarding CSV parsing  
(near the end of the page):

http://www.lua.org/pil/20.4.html

Quote:

To break a CSV into an array is more difficult, because we must avoid  
mixing up the commas written between quotes with the commas that  
separate fields. We could try to escape the commas between quotes.  
However, not all quote characters act as quotes; only quote characters  
after a comma act as a starting quote, as long as the comma itself is  
acting as a comma (that is, it is not between quotes). There are too  
many subtleties. For instance, two quotes may represent a single  
quote, two quotes, or nothing:

"hello""hello", "",""

Clear as mud? :D


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

Re: [sqlite] Tedious CSV import question

[Wilson, Ronald]

> > Still, the RFC does not
> > address how to handle rows like this:
> >
> > 1234,abc"123",abc
> > 1235,""123,abc
> 
> Both violate the format as defined.  Generate an error message or
> interpret them any way that suits you.
> 
> > What are you supposed to do with those?  It is not clear.  Also, are
> > you
> > supposed to strip the quotes upon consuming the field?  Are you
> > supposed
> > to un-escape escaped quotes?
> >
> > "1234" -> 1234 or "1234" ?
> 
> 1234
> 
> > "15""" -> 15" or 15"" or "15""" or "15"" ?
> 
> 15"
> 
> Simon.

I can appreciate common sense answers, but the RFC doesn't specify those
answers.  The RFC seems to only address the formatting of the CSV file,
not the consumption of it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attach databases using tons of memory (DBI Perl)

[John Lynch]

I will try what you have mentioned below.  I am 99% sure it isnt the perl
script, since it doesn't have the same behavior without attaches.  I will
let you know what happens.

Thank You

2009/9/25 D. Richard Hipp 

>
> On Sep 25, 2009, at 11:02 AM, John Lynch wrote:
>
> > page size is max (32768).  These sqlt files are around 100Gig with 200
> > million rows.
> > I have query_cache set to 0.
> >
> > I am running single queries against these attach tables and then
> > putting the
> > data in memcached.  The script runs thousands of queries before it
> > runs out
> > of memory.  If I use the same width table ( a product of all 3
> > attached
> > tables, flattened into 1 table) and run it with the same script I
> > have no
> > memory issues.  I am hoping there is something I am missing with
> > caching and
> > attach.  Maybe a pragma or something?
> >
>
>
> SQLite's memory usage should be self-limiting.  No special pragma
> settings required.  You can reduce the amount of memory requested by
> using:
>
> PRAGMA main.cache_size=10;
>
> Repeat this for each of the attached databases.  But even if you don't
> do this, the cache size should be 2000 pages which is only about 64MB
> for each attached database.
>
> What does sqlite3_mem_used() and sqlite3_mem_highwater() tell you
> about SQLite's memory usage?  Are you sure that it is SQLite and not
> your perl script that is using the memory?
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
John Lynch
(217) 953-0557
johntly...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attach databases using tons of memory (DBI Perl)

[John Lynch]

I only query for one row at a time.

2009/9/25 Simon Slavin 

>
> On 25 Sep 2009, at 1:10pm, Kees Nuyt wrote:
>
> > 4) Don't try to collect the full resultset into a Perl
> > array.
>
> LOL
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
John Lynch
(217) 953-0557
johntly...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inconsistent behavior - perhaps a bug?

[Pavel Ivanov]

According to http://www.sqlite.org/lang_select.html and
http://www.sqlite.org/lang_expr.html one can use in select statement
as a result column one of the following:
- *
- table_name.* (I assume that instead of table_name one can use table alias too)
- any expression

Expression can consist of the single column in the form
[[database_name.]table_name.]column_name

So nobody says that database_name.table_name.* should work and
behavior is as intended and documented. Though from the point of view
of outside user it seems logical for this to work of course.


Pavel

On Fri, Sep 25, 2009 at 2:01 PM, Schrum, Allan
 wrote:
> $ bin/sqlite3
> SQLite version 3.6.17
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table t(a integer);
> sqlite> select main.t.* from t;
> SQL error: near "*": syntax error
> sqlite> select main.t.* from main.t;
> SQL error: near "*": syntax error
> sqlite> select t.* from t;
> sqlite> select t.* from main.t;
> sqlite> select main.t.* from main.t;
> SQL error: near "*": syntax error
> sqlite> select main.t.a from main.t;
> sqlite> select main.t.a from t;
> sqlite> .quit
>
> ___
> 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] SQLite Suitability for Shopping Cart

[D. Richard Hipp]

On Sep 25, 2009, at 1:13 PM, CityDev wrote:

>
>
> Fred Williams-5 wrote:
>>
>> no further need to ask and answer, "Will SQLite support
>> multi users?
>>
> Maybe it should be covered a bit more specifically on the SQLite site,

http://www.sqlite.org/whentouse.html

> bearing in mind that new people would naturally have the mindset that
> databases are for shared use normally. SQLite does have sharing  
> capabilities
> so it would be helpful to profile these for novices.
> -- 
> View this message in context: 
> http://www.nabble.com/SQLite-Suitability-for-Shopping-Cart-tp25530589p25615724.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



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

[sqlite] Inconsistent behavior - perhaps a bug?

[Schrum, Allan]

$ bin/sqlite3
SQLite version 3.6.17
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(a integer);
sqlite> select main.t.* from t;
SQL error: near "*": syntax error
sqlite> select main.t.* from main.t;
SQL error: near "*": syntax error
sqlite> select t.* from t;
sqlite> select t.* from main.t;
sqlite> select main.t.* from main.t;
SQL error: near "*": syntax error
sqlite> select main.t.a from main.t;
sqlite> select main.t.a from t;
sqlite> .quit

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

Re: [sqlite] Tedious CSV import question

[Simon Slavin]

On 25 Sep 2009, at 6:33pm, Wilson, Ronald wrote:

> Still, the RFC does not
> address how to handle rows like this:
>
> 1234,abc"123",abc
> 1235,""123,abc

Both violate the format as defined.  Generate an error message or  
interpret them any way that suits you.

> What are you supposed to do with those?  It is not clear.  Also, are  
> you
> supposed to strip the quotes upon consuming the field?  Are you  
> supposed
> to un-escape escaped quotes?
>
> "1234" -> 1234 or "1234" ?

1234

> "15""" -> 15" or 15"" or "15""" or "15"" ?

15"

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

Re: [sqlite] Tedious CSV import question

[Wilson, Ronald]

>   Reading the RFC only proves my point.  SQLite v3 is older than that
>   doc, and it pretty much admits the cat was out of the bag a long
time
>   ago.  There are a ton of optional and might/may/could sections that
>   event the format they define has a lot of holes in it (i.e. headers,
>   or no headers?).

I read the RFC last night and even took a stab at adding it to the
.import method in the sqlite command line.  But pretty quickly holes
appeared.  For example, the RFC says that everything between the commas
is supposed to be part of the field, including white space.  Normally I
trim the white space unless it's quoted.  Still, the RFC does not
address how to handle rows like this:

1234,abc"123",abc
1235,""123,abc

What are you supposed to do with those?  It is not clear.  Also, are you
supposed to strip the quotes upon consuming the field?  Are you supposed
to un-escape escaped quotes?
 
"1234" -> 1234 or "1234" ?
"15""" -> 15" or 15"" or "15""" or "15"" ?

Seems to me if you strip quotes, you have to un-escape any escaped
quotes in the field.  If you don't strip quotes, then you can't
un-escape anything in the field because it leads to bazaar edge cases.
Then there is the matter of white space outside the quotes.  The RFC
seems silent on all these issues, though the ABNF grammar implies that
white space outside quotes is not tolerated, which could lead to
considerable user surprise.

RW

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

HARRIS CORPORATION   |   RF Communications Division
assuredcommunications(tm)


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

Re: [sqlite] SQLite Suitability for Shopping Cart

[CityDev]

Fred Williams-5 wrote:
> 
>  no further need to ask and answer, "Will SQLite support
> multi users? 
> 
Maybe it should be covered a bit more specifically on the SQLite site,
bearing in mind that new people would naturally have the mindset that
databases are for shared use normally. SQLite does have sharing capabilities
so it would be helpful to profile these for novices. 
-- 
View this message in context: 
http://www.nabble.com/SQLite-Suitability-for-Shopping-Cart-tp25530589p25615724.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Tedious CSV import question

[Simon Slavin]

On 25 Sep 2009, at 3:00pm, Jay A. Kreibich wrote:

>  Personally, I'd rather have the SQLite team working on core database
>  features than trying to build a better CSV parser

Yet strangely, the command-line tool is one of the best things about  
SQLite.  If I appear to find a bug in a SQLite library I try doing the  
same thing in the command-line tool.  If the behaviour is the same I  
know my software is correct and I study SQL.  If the behaviour is  
different i know the fault is in my programming or in the way I'm  
using the API.

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

Re: [sqlite] Attach databases using tons of memory (DBI Perl)

[Simon Slavin]

On 25 Sep 2009, at 1:10pm, Kees Nuyt wrote:

> 4) Don't try to collect the full resultset into a Perl
> array.

LOL

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

Re: [sqlite] SQLite Suitability for Shopping Cart

[Fred Williams]

Since SLQite was never intended for multi user databases in the initial
design i.e. the name "SQLite" I would say the design is the reason.

Now can the design be reengineered to allow finer grain locking as an option
without derailing the original design intent?  That is for those that do all
the work for the rest of us to decide.

My only incentive that pops readily to mind would be the enormous bandwidth
reduction here with no further need to ask and answer, "Will SQLite support
mulit users? and etc. ad nauseium..." :-)

Fred

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org]on Behalf Of CityDev
Sent: Friday, September 25, 2009 12:58 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite Suitability for Shopping Cart



This is what is says on the SQLite site:

"We are aware of no other embedded SQL database engine that supports as much
concurrency as SQLite. SQLite allows multiple processes to have the database
file open at once, and for multiple processes to read the database at once.
When any process wants to write, it must lock the entire database file for
the duration of its update. But that normally only takes a few milliseconds.
Other processes just wait on the writer to finish then continue about their
business. Other embedded SQL database engines typically only allow a single
process to connect to the database at once."

What that seems to be saying is you can have concurrency but you will suffer
the effects of whole-database locking for update transactions. It seems a
glaring shortcoming, so either SQLite is not normally used by multiple
processes or people find it doesn't matter too much ie you still get
acceptable performance with n users. Which do you think is true?
--
View this message in context:
http://www.nabble.com/SQLite-Suitability-for-Shopping-Cart-tp25530589p256068
92.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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] Attach databases using tons of memory (DBI Perl)

[D. Richard Hipp]

On Sep 25, 2009, at 11:02 AM, John Lynch wrote:

> page size is max (32768).  These sqlt files are around 100Gig with 200
> million rows.
> I have query_cache set to 0.
>
> I am running single queries against these attach tables and then  
> putting the
> data in memcached.  The script runs thousands of queries before it  
> runs out
> of memory.  If I use the same width table ( a product of all 3  
> attached
> tables, flattened into 1 table) and run it with the same script I  
> have no
> memory issues.  I am hoping there is something I am missing with  
> caching and
> attach.  Maybe a pragma or something?
>


SQLite's memory usage should be self-limiting.  No special pragma  
settings required.  You can reduce the amount of memory requested by  
using:

 PRAGMA main.cache_size=10;

Repeat this for each of the attached databases.  But even if you don't  
do this, the cache size should be 2000 pages which is only about 64MB  
for each attached database.

What does sqlite3_mem_used() and sqlite3_mem_highwater() tell you  
about SQLite's memory usage?  Are you sure that it is SQLite and not  
your perl script that is using the memory?

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Attach databases using tons of memory (DBI Perl)

[John Lynch]

page size is max (32768).  These sqlt files are around 100Gig with 200
million rows.
I have query_cache set to 0.

I am running single queries against these attach tables and then putting the
data in memcached.  The script runs thousands of queries before it runs out
of memory.  If I use the same width table ( a product of all 3 attached
tables, flattened into 1 table) and run it with the same script I have no
memory issues.  I am hoping there is something I am missing with caching and
attach.  Maybe a pragma or something?

2009/9/25 P Kishor 

> On Thu, Sep 24, 2009 at 3:30 PM, John Lynch  wrote:
> > Sqlite Users,
> >
> > I am using sqliute user for a very large customer database.  I have 3
> > seperate DB files with a table in each file.  The files are cust,acct,
> and
> > web.
> >
> > Each DB table has  ~ 200 Million Rows.  When I attach web and acct to
> cust
> > with perl DBI/DBD::Sqlite the process runs out of memory on windows (2G
> > 32bit process limit).  The memory usage grows out of control while
> querying
> > all table joined together via the attach.
> >
> > When I used a flattened table of the same data (ie create table as select
> *
> > from ...) via same attach to create the flattened table.  I have no
> > problems.
> >
>
> Are you running out of memory when you ATTACH, or when you actually
> perform a specific query? If the only difference between your two
> instances is "ATTACH," and you are doing everything exactly the same
> and running out of memory with ATTACH and not without ATTACH, then
> there is something going on at the point of ATTACH. More info from you
> will shed light on this.
>
> Besides providing more info on this here, you might also want to ask
> this question at perlmonks, in case this is a Perl-specific issue.
>
>
>
>
> >
> > Any ideas on how to reduce the memory consumption when using attach?
> >
> > Thank You
> >
> > --
> > --
> > John Lynch
> > (217) 953-0557
> > johntly...@gmail.com
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> Sent from Madison, WI, United States
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
John Lynch
(217) 953-0557
johntly...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite JDBC - org.sqlite.NativeDB.step taking up too much time

[Pavel Ivanov]

> Does anyone know why this method might be so expensive?

Because it's main method to execute your queries.
I believe I can safely assume that org.sqlite.NativeDB.step maps
directly to C call sqlite3_step() and it's the only function where
query is executed. It doesn't just iterate through resultset it
obtains this resultset it too.

Pavel

On Thu, Sep 24, 2009 at 11:24 PM, George Bills  wrote:
> Hi everyone - this might be a general JDBC question rather than an
> SQLite specific question, but I'm not sure.
>
> I've got an app that's writing to an SQLite database using the latest
> SQLite JDBC driver ("v056, based on SQLite 3.6.14.2") from
> http://www.zentus.com/sqlitejdbc/.
>
> I've done some basic profiling using hprof, and initially things are
> fine. But then my tables get quite large (e.g. 26 million / 7 million /
> 6 million rows), and after this point, I find that a huge amount of my
> apps time is being spent in "org.sqlite.NativeDB.step".
>
> Does anyone know why this method might be so expensive? I probably need
> to dig deeper with my profiling and find out exactly which methods are
> calling the expensive "step"s, but I'm fairly new to working with JDBC
> and any hints or suggestions would be helpful.
>
> During the applications run, I'll be running a certain amount of selects
> and inserts, but in all cases, I'll be selecting or inserting 1 row at a
> time, so I wouldn't expect that iterating over my ResultSet (I assume
> this is what org.sqlite.NativeDB.step is doing) would be very expensive.
>
> The code that does the selection of a row, given an SQL string and an
> array of string parameters is like:
>
>             selectStatement = databaseConnection.prepareStatement(SQL);
>             setStatementParameters(selectStatement, parameters);
>             resultSet = selectStatement.executeQuery();
>             singleResult = parseResultForSingleElement(resultSet,
> columnName);
>
> The code that does the insertion of a row, given an SQL string and an
> array of string parameters is like:
>
>             insertStatement = databaseConnection.prepareStatement(SQL);
>             setStatementParameters(insertStatement, parameters);
>             insertStatement.executeUpdate();
>             resultSet = insertStatement.getGeneratedKeys();
>             autoincrementPK = parseResultForSingleElement(resultSet, null);
>
> parseResultForSingleElement is like:
>
>         if (numColumns == 1 && res.next()) {
>             E val;
>             if (columnName != null) {
>                 val = (E) res.getObject(columnName);
>             } else {
>                 val = (E) res.getObject(1);
>             }
>             if (res.wasNull()) {
>                 ret = new SQLVal(ResultStatus.RESULT_WAS_NULL);
>             } else {
>                 ret = new SQLVal(val);
>             }
>         }
>
> Thanks for any help.
> George.
> ___
> 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] Tedious CSV import question

[Jay A. Kreibich]

On Fri, Sep 25, 2009 at 10:24:15AM +1000, BareFeet scratched on the wall:


> In reality, in the thousands of CSV files I've dealt with  
> over the years, they all follow the same standard:
> 
> 1. Rows are delimited by a new line (return and/or line feed).
> 2. Columns are delimited by a comma.
> 3. "Quoted text" is treated as one value item, including any commas or  
> new lines within it.
> 4. A double quote "" is used to put a quote within quotes.
> 
> That's it.

  This is more or less the standard put forth by RFC 4180.  And if this
  is all you've encountered, you're not using very many different
  applications or you're primarily dealing with numbers and simple
  strings that don't contain quotes or commas.  CSV works very very
  well if you never get into the question of escapes, but details count.

  Reading the RFC only proves my point.  SQLite v3 is older than that
  doc, and it pretty much admits the cat was out of the bag a long time
  ago.  There are a ton of optional and might/may/could sections that
  event the format they define has a lot of holes in it (i.e. headers,
  or no headers?).

> Everything I've seen uses this.

  According to the RFC Excel doesn't use double-quotes for anything.
  You might not care about Excel, but I'm willing to bet it is one of
  the most-- if not the most-- common exporters of CSV.  The question
  of getting data from Excel into SQLite shows up on the list every now
  and then.

> Some don't need delimiters  
> in values, so they don't need quotes, but the encompassing  
> specification works for all cases.

  No, it doesn't.  Working on a large website that provided CSV exports
  for a number of data sources, I've seen plenty of examples that don't
  work.  Finding a common format that could be exported into a handful
  of common desktop apps was so pointless we seriously considered
  getting rid of CSV all together, because we got tired of our users
  telling us how simple CSV was, and why couldn't we just do this one
  thing differently so it would work on *their* application.

> It's not that big a deal for SQLite to support it, so it should.

  If it is so simple, and you know where the code is...

  This is, perhaps, the biggest fallacy of CSV... people think it
  is a "simple" format (it isn't), and assume that code support to
  "correctly" (whatever that is) read it is simple.  It isn't.  The RFC
  has a formal grammar that requires over a dozen elements to define!

  Most people setting out to build a CSV reader never think to use a
  full grammar and parser-- after all, it is such a "simple" format--
  and find themselves in a mess of code soon enough.  Seriously, give
  it a try.

  Carlos's Python script (nice!) is a great example.  His comment "I am
  so grateful I did not have to write a parser for CSV" is dead on.
  And, as he points out, the reason the Python module is so good is
  that it is adaptive, and really reads five or six different variants
  of CSV (something a reader can do but a writer cannot).  He was
  also able to clobber it all together in a few hours or less (because
  someone else spent a few hundred hours on the CSV module), further
  proving that advanced support of this kind of thing is really outside
  of the scope of SQLite3.  After all, the .import command is part of
  the shell, not part of the core library.




  CSV is a great quick and dirty format to move data.  But it isn't
  "simple" and it isn't nearly as universal as many assume.  It works
  great if you're just moving simple numbers and strings that don't
  include commas, but becomes a mess when you get into exceptions. 

  Personally, I'd rather have the SQLite team working on core database
  features than trying to build a better CSV parser.  The problem
  is non-trivial and borders on unobtainable and, as Carlos
  proved so clearly, there are better, easier, faster ways.

   -j


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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attach databases using tons of memory (DBI Perl)

[P Kishor]

On Thu, Sep 24, 2009 at 3:30 PM, John Lynch  wrote:
> Sqlite Users,
>
> I am using sqliute user for a very large customer database.  I have 3
> seperate DB files with a table in each file.  The files are cust,acct, and
> web.
>
> Each DB table has  ~ 200 Million Rows.  When I attach web and acct to cust
> with perl DBI/DBD::Sqlite the process runs out of memory on windows (2G
> 32bit process limit).  The memory usage grows out of control while querying
> all table joined together via the attach.
>
> When I used a flattened table of the same data (ie create table as select *
> from ...) via same attach to create the flattened table.  I have no
> problems.
>

Are you running out of memory when you ATTACH, or when you actually
perform a specific query? If the only difference between your two
instances is "ATTACH," and you are doing everything exactly the same
and running out of memory with ATTACH and not without ATTACH, then
there is something going on at the point of ATTACH. More info from you
will shed light on this.

Besides providing more info on this here, you might also want to ask
this question at perlmonks, in case this is a Perl-specific issue.




>
> Any ideas on how to reduce the memory consumption when using attach?
>
> Thank You
>
> --
> --
> John Lynch
> (217) 953-0557
> johntly...@gmail.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attach databases using tons of memory (DBI Perl)

[Kees Nuyt]

On Thu, 24 Sep 2009 15:30:40 -0500, John Lynch
 wrote:

>Sqlite Users,
>
>I am using sqliute user for a very large customer database.  I have 3
>seperate DB files with a table in each file.  The files are cust,acct, and
>web.
>
>Each DB table has  ~ 200 Million Rows.  When I attach web and acct to cust
>with perl DBI/DBD::Sqlite the process runs out of memory on windows (2G
>32bit process limit).  The memory usage grows out of control while querying
>all table joined together via the attach.
>
>When I used a flattened table of the same data (ie create table as select *
>from ...) via same attach to create the flattened table.  I have no
>problems.
>
>
>Any ideas on how to reduce the memory consumption when using attach?

1) Limit the size of the page cache with the appropriate
PRAGMAs. THe cache takes page_size (bytes) * cache_size
(pages) plus some overhead.

2) Optimize your queries, prevent full Cartesian products.

3) Limit the size of the resultset to what you really need.

e.g. http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor

4) Don't try to collect the full resultset into a Perl
array.

>Thank You

HTH
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Suitability for Shopping Cart

[Kees Nuyt]

On Thu, 24 Sep 2009 22:58:28 -0700 (PDT), CityDev
 wrote:

>
>This is what is says on the SQLite site:
>
>"We are aware of no other embedded SQL database engine that supports as much
>concurrency as SQLite. SQLite allows multiple processes to have the database
>file open at once, and for multiple processes to read the database at once.
>When any process wants to write, it must lock the entire database file for
>the duration of its update. But that normally only takes a few milliseconds.
>Other processes just wait on the writer to finish then continue about their
>business. Other embedded SQL database engines typically only allow a single
>process to connect to the database at once."
>
>What that seems to be saying is you can have concurrency but you will suffer
>the effects of whole-database locking for update transactions. 

That's what is says, and it's a design decision.

>It seems a glaring shortcoming, 

I wouldn't say so.

>so either 
> 1) SQLite is not normally used by multiple processes or
> 2) people find it doesn't matter too much ie you still
>  get acceptable performance with n users. 
> Which do you think is true?

1) is true. 2) is not false, but doesn't really apply,
considering SQLites "mission statement":

http://www.sqlite.org/whentouse.html
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Attach databases using tons of memory (DBI Perl)

[John Lynch]

Sqlite Users,

I am using sqliute user for a very large customer database.  I have 3
seperate DB files with a table in each file.  The files are cust,acct, and
web.

Each DB table has  ~ 200 Million Rows.  When I attach web and acct to cust
with perl DBI/DBD::Sqlite the process runs out of memory on windows (2G
32bit process limit).  The memory usage grows out of control while querying
all table joined together via the attach.

When I used a flattened table of the same data (ie create table as select *
from ...) via same attach to create the flattened table.  I have no
problems.


Any ideas on how to reduce the memory consumption when using attach?

Thank You

-- 
--
John Lynch
(217) 953-0557
johntly...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Suitability for Shopping Cart

[Simon Slavin]

On 25 Sep 2009, at 6:58am, CityDev wrote:

> What that seems to be saying is you can have concurrency but you  
> will suffer
> the effects of whole-database locking for update transactions. It  
> seems a
> glaring shortcoming, so either SQLite is not normally used by multiple
> processes or people find it doesn't matter too much ie you still get
> acceptable performance with n users. Which do you think is true?

The former.  SQLite is the SQL of choice for embedded applications:  
things running inside a smartphone, or a GPS unit or something.  Since  
these units run just one UI application at a time, you don't need to  
worry about sophisticated locking mechanisms.  Only one processor is  
used to run user-aware programs.  If you're running a program to  
consult a database of phone numbers or map features or songs, that's  
the only program that cares about that kind of data.  While you're  
downloading new data for those databases, the entire device is locked  
in 'synch' mode anyway, so no other programs will be running.

Once you've made that design choice, the points Roger mentioned come  
into play: SQLite library calls are designed to let you do as much  
processing as possible before you need to lock the file.  This is not  
true of all SQL engines which will do some processing, lock a record,  
unlock it, do more processing, lock the next record, etc..

SQLite is great for what it's designed for.  But it's not designed for  
complicated multi-user online databases.  You pays your money and you  
gets your choice.

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

Re: [sqlite] SQLite Suitability for Shopping Cart

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

CityDev wrote:
> What that seems to be saying is you can have concurrency but you will suffer
> the effects of whole-database locking for update transactions. It seems a
> glaring shortcoming, so either SQLite is not normally used by multiple
> processes or people find it doesn't matter too much ie you still get
> acceptable performance with n users.

Did you notice the use of "embedded" several times?

The SQLite approach is to use simple locking and to hold locks for as short
periods as possible.  An alternate approach which would be more complicated
locking (pages or rows) and having considerably more code to manage
concurrency across processes of that locking.  (That code would be really
nasty as operating systems don't really provide any common primitives of
that level of granularity.  Heck they barely provide file locking.)

If you go with SQLite's flow such as not having open transactions lying
around and getting work done as quickly as possible (eg composing SQL
upfront rather than long periods thinking about what to issue next within a
transaction) then it will work *very* well.  Chances are it will
significantly outperform database "servers", as they require marshalling of
queries and responses between processes and possibly over the network -
something which will be way slower than SQLite's quick transactions.
(SQLite was originally started because of database servers being flaky and
needing administration.)

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkq8apMACgkQmOOfHg372QSH+ACguuHaLxS7CoPE/yjTXrKI/r3u
TKYAoNOcv4Gtw9BL6iHsz31DWZQKI8fs
=OMAn
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users