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

2013-06-26 Thread Hick Gunter 
When confronted with this problem I implemented a virtual table that could read 
and write CSV like files. This is for export/import purposes only so it does 
not implement UPDATE or DELETE. The first line of the text file is reserved for 
field names. In my case, I treat everything non-numeric that contains 
non-prontable characters or delimiters as a blob when writing...

Export becomes:

CREATE VIRTUAL TABLE  USING csv ('',''); <-- 
field list is written to file
INSERT INTO  SELECT ... FROM  [WHERE ];
DROP TABLE ;

Import becomes:

CREATE VIRTUAL TABLE  USING csv ('); <-- field list is 
read from file!
INSERT INTO   SELECT ... FROM  [WHERE ];
DROP TABLE ;

The advantage of this approach is that I can use SQL statements to read/write 
whatever format I need...

-Ursprüngliche Nachricht-
Von: RSmith [mailto:rsm...@rsweb.co.za]
Gesendet: Mittwoch, 26. Juni 2013 14:48
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] sqlite3: .import command handles quotation incorrectly

Expanding on what Jay replied to:
> Hi,
>
> well we could discuss endlessly, what well formed CSV files are.
Not really, the rules are pretty set in stone, it isn't open to interpretation.


> Given that we cannot fix the generation of the CSV file, why not making the 
> importer a little bit smarter?
>
> The .import command already treats " as literal data, when it doesn't appear 
> at the beginning of the cell, but it requires an even number of " in the same 
> cell to don't get confused in breaking up the row into cells.
>
> All I ask for is to treat an odd number of " in a cell as literal data except 
> when the rule for dequoting applies, i. e. when the cell starts with a ".

This is impossible - How do you know where the end of a cell is if you are 
ignoring/counting Quotes???

For instance, what is the resulting fields from this CSV: [123, 9" Nail, ", 
Caliper, "set, up 5"", hold", 8 ]
In REAL CSV terms that would be:123  |  9 Nail,  | Caliper | set, up 5", 
hold  |  8

How would you want that to be interpreted?  Once we encounter the first quote, 
how do we know at which quote the field ends? How do we know how many quotes 
are in the field? Or do we just ignore quotes altogether and break on every 
comma - in which case the above
becomes:   123  |  9" Nail  |  "  |  Caliper  |  "set  |  up 5""  | hold"  |  8
Surely it's easy to see how this is incorrect?
How about your other suggestion about taking fields starting with quotes to be 
quoted in pairs (even numbers) but otherwise ignoring odd numbered quotes - how 
do we know at which quote to stop counting?
Going strictly by your suggestion It might be interpreted as:  123 |  9" Nail  
|  , Caliper,  |  up 5""  |  hold"  |  8  |
or maybe even as:  123  |  9" Nail  |  , Caliper, set  |  up 5""  | hold"  |  8 
 |   (the interpreter would never be sure)

The interpreter working like this is not "a little bit smarter" but rather is 
very much non-conforming.

You have a very specific case of weirdly created data - I already offered help 
to give you an interpreter specific to it and will be happy to, but there is no 
way the SQlite can be altered like this, it will read all other CSV files wrong 
- and making a case specifc SQLite is possible (you can alter the C code and 
compile your own easily), but all future updates will need this adjustment.
Submitting a patch will require your version to work on all other CSV file too 
- which as desribed above - it won't.

is there no way to fix the output? Or use an in-between step of converting the 
data before import from the weird layout to proper CSV? (we can help with that).

Have a great day!

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


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug: on opening the same database twice

2013-06-26 Thread Israel Lins Albuquerque 
Ok, Then let me explain my problem to see if I done the best solution!

I have an replication system using sqlite where:
- I have 2 databases: one for output* and other for input*;
- I have 2 process accessing booth:
 The first is the replicator:
 - Get the data on server and write on input database;
 - Get the data on output database and write on server;
 The second process:
 - Read the input database;
 - Write on output database;

And I have triggers and tables to control what data has to be sync;

The problem, I guess solved today, is:
The replicator before open a single connection attaching booth of databases,
then when the replicator commits transactions that are writing only in input 
database and the 
second process try to write on output database the second process receive a 
database locked error,
then after that I have to close and open again the database;

To solve this I done two connection to databases on replicator:
One only for input database that only receive data;
And the other for output database attaching input database (to make some joins);

The point is.

I tried to make the output connection readonly to ensure that.
But that don't works.

Other question:

When the replicator is sending data to the server they acquire read lock on 
output database,
then the second process has some delay trying to acquire write lock on output 
database, there are some way to make 
this delay gone? Making the read process read the data writed until begin of 
read?

Thats it, thanks for patience.

Regards,

--
Israel Lins Albuquerque

Antes de imprimir, pense em sua responsabilidade com o MEIO AMBIENTE.



Em 26/06/2013, às 14:24, Israel Lins Albuquerque  
escreveu:

> I open an database twice times in same process and same thread one in 
> readonly mode, and the other read write.
> With shared_cache enable.
> Opening first the readonly mode and after readwrite mode, the second 
> connection works as read only too.
> 
> Then I looked to sqlite code and I see the problem in:
> sqlite3BtreeOpen line 1788, because the shared cache uses the same structure 
> in booth connections, and pBt->pPager->readOnly is set to 1.
> 
> To workaround this I will open read write mode first, but that make readonly 
> connection become read write.
> 
> There is an know issue?
> 
> Regards,
> --
> Israel Lins Albuquerque
> 
> Antes de imprimir, pense em sua responsabilidade com o MEIO AMBIENTE.
> 
> 
> 
> ___
> 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] sqlite3: .import command handles quotation incorrectly

2013-06-26 Thread Richard Hipp 
On Wed, Jun 26, 2013 at 6:23 PM, RSmith  wrote:

>  I have done ludicrous amounts of testing and evaluating imports for and
> from CSVs
>

I made a go at improving the CSV importer for the upcoming SQLite 3.8.0
release.  Please see the latest trunk check-in.  Your expert feedback would
certainly be welcomed here.

Note that in the new ".import" command, the table named in the second
argument need not exist now, and the shell will create it for you
automatically, giving it column names as determined by the first row of the
CSV file.  That seemed like it might be a handy feature.

The other changes to the new ".import" are that it issues error messages
(but tries to continue muddling through) if the input does not conform to
rfc4180, and it correctly handles quoted data that extends across multiple
lines or that contains embedded commas.

-- 
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] sqlite3: .import command handles quotation incorrectly

2013-06-26 Thread RSmith 


On 6/26/2013 11:21 AM, RSmith wrote:

I meant if a real csv interpreter conforming to  RFC4180 were to read the 
garbage I posted, it would come up with the
result specified.


How do you know what a standard-conforming interpreter would do when presented with input that's invalid under that standard? The 
standard only describes the meaning of valid input, naturally. What exactly is your claim based on?


Ahh, a question I can actually answer confidently - firstly these claims can only be made for those I did test, the ones I did not I 
cant say anything about (Naturally). I mentioned earlier about it being a hobby of mine of sorts, but I know this because I have 
done ludicrous amounts of testing and evaluating imports for and from CSVs (I actually originally had quite a few ideas, not unlike 
Reinhard, about how to make it better). I tried to devise importers specifcally that would simply import near any format, and 
succeeded very well too (I could send you some test software if you like to try) but what I couldnt do is make a universal CSV 
importer that would be impervious to some weird quoting habits (for instance) and not mess up other "proper" CSV imports (well, not 
anything that withstood rigorous testing) - to both my delight and dismay I found most systems in the wild have their own 
interpretation and quirks. This required me testing other CSV importers with all kinds of data trying to get them to break or seeing 
what non-conformances would be accepted (OpenOffice, Googledocs, Excel, even SQLite.import to name a few). All this effort precisely 
because RFC4180 is less universally implemented than it should be, in fact it is surprising how many systems (mostly proprietary to 
be fair) export csv that are atrociously non-conforming, but since excel imports it ok, who cares, right?


I could almost from head jot down CSV data that either conforms to RFC4180 and would break some standard importers, or that doesn't 
conform but would be accepted by many.  With CSV and data manipulation and parsing I've been around the block a few times - so 
believe me when I say I really feel Reinhard's pain, but there really is no quick (but standard) fix.


I would imagine that most (well-written, not otherwise buggy) CSV interpreters agree in their interpretation of RFC4180-conforming 
input; RFC4180 describes a pretty strict subset of what's found in the wild. It's precisely in their handling of non-conforming 
input that CSV interpreters differ.



Couldn't agree more - but the real culprit is the purported "CSV" exporters, 
which is what prompted this thread too.


___
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

2013-06-26 Thread Reinhard Nißl 

Hi,

my below answer was wrong -- my approach produces a different output.

Am 26.06.2013 15:12, schrieb Nißl Reinhard:


Your sample [123, 9" Nail, ", Caliper, "set, up 5"", hold", 8 ] gives currently the 
following column values (I used > and < to indicate the string bounderies, so that any white spaces are 
noticeable):

>123< | > 9" Nail, "< | > Caliper< | > "set, up 5"", hold"< | > 8 <

Given that your input has been stripped by the leading spaces after a column 
separator, or if the column separator has been defined as >, < you currently 
get:

>123< | >9" Nail, "< | >Caliper< | >set, up 5", hold< | >8 <

With my approach you still get:

>123< | >9" Nail, "< | >Caliper< | >set, up 5", hold< | >8 <


You actually get:
>123< | >9" Nail< | >, Caliper, set< | >up 5""< | >hold"< | >8 <

So if that causes to much fuzz, then I'd like to suggest to make 
the quotation character configurable. By setting it to '\0' for 
example, my file could be read properly.


Bye.
--
Dipl.-Inform. (FH) Reinhard Nissl
mailto:rni...@gmx.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite 3.8.0 -> autoconf/configure

2013-06-26 Thread Keith Medcalf 

Richard,

The current trunk does not build at version 3.8.0 

# ../src/configure --enable-load-extension --enable-threadsafe --with-pic
configure: error: configure script is out of date:
 configure $PACKAGE_VERSION = 3.7.17
 top level VERSION file = 3.8.0
please regen with autoconf

Local regeneration of the configure script with autoconf generates a working 
configure script ... (or at least so it appears)

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org





___
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

2013-06-26 Thread Igor Tandetnik 

On 6/26/2013 11:21 AM, RSmith wrote:

I meant if a real csv interpreter conforming to  RFC4180 were to read the 
garbage I posted, it would come up with the
result specified.


How do you know what a standard-conforming interpreter would do when 
presented with input that's invalid under that standard? The standard 
only describes the meaning of valid input, naturally. What exactly is 
your claim based on?


I would imagine that most (well-written, not otherwise buggy) CSV 
interpreters agree in their interpretation of RFC4180-conforming input; 
RFC4180 describes a pretty strict subset of what's found in the wild. 
It's precisely in their handling of non-conforming input that CSV 
interpreters differ.



Having done this many times I already know the answer to his problem,
it's real easy, a simple command-line tool to make his weird format into
a SQLite table - would take a few minutes to make and save him a lot of
trouble - I even offered help doing it - but he is intent on arguing
that SQLite should change and do it "smarter" - (which is his right) -
and now I'm trying to show why it isn't smarter in the hope of finding a
resolve.


I totally agree.
--
Igor Tandetnik

___
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

2013-06-26 Thread Jay A. Kreibich 
On Wed, Jun 26, 2013 at 10:53:38AM -0700, Roger Binns scratched on the wall:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 26/06/13 05:07, Jay A. Kreibich wrote:
> > A year or so ago there was some effort to write a plug-in that would 
> > use the standard Python CSV module (at least, I think it was Python) 
> > for data import into SQLite.  The Python module is pretty good at 
> > importing most flavors of CSV... and as a result is an extremely 
> > complex bit of code.  Anyone know if that project got any traction?
> 
> I'd never heard of that.  For several years I have maintained an alternate
> shell as part of the Python SQLite wrapper APSW.

  Perhaps it is your work I'm remembering.

   -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] Bug: on opening the same database twice

2013-06-26 Thread Richard Hipp 
On Wed, Jun 26, 2013 at 1:24 PM, Israel Lins Albuquerque <
israelin...@yahoo.com.br> wrote:

> I open an database twice times in same process and same thread one in
> readonly mode, and the other read write.
> With shared_cache enable.
> Opening first the readonly mode and after readwrite mode, the second
> connection works as read only too.
>
> Then I looked to sqlite code and I see the problem in:
> sqlite3BtreeOpen line 1788, because the shared cache uses the same
> structure in booth connections, and pBt->pPager->readOnly is set to 1.
>
> To workaround this I will open read write mode first, but that make
> readonly connection become read write.
>
> There is an know issue?
>

Yes.  That is the way shared cache mode works.  The read-only attribute is
set or cleared by the first connection to open the database file.  The
read-only attribute is ignored for all subsequent opens.


-- 
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] Bug: on opening the same database twice

2013-06-26 Thread Israel Lins Albuquerque 
I open an database twice times in same process and same thread one in readonly 
mode, and the other read write.
With shared_cache enable.
Opening first the readonly mode and after readwrite mode, the second connection 
works as read only too.

Then I looked to sqlite code and I see the problem in:
sqlite3BtreeOpen line 1788, because the shared cache uses the same structure in 
booth connections, and pBt->pPager->readOnly is set to 1.

To workaround this I will open read write mode first, but that make readonly 
connection become read write.

There is an know issue?

Regards,
--
Israel Lins Albuquerque

Antes de imprimir, pense em sua responsabilidade com o MEIO AMBIENTE.



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

Re: [sqlite] Questions about exclusive transation

2013-06-26 Thread Dan Kennedy 

On 06/26/2013 02:08 PM, Woody Wu wrote:

On Tue, Jun 25, 2013 at 10:21:51PM -0400, Igor Tandetnik wrote:

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.

Thanks for the analyzing.  After checked, I found it's the prepare_v2
statement, previous to the step statement, returned SQLITE_BUSY.  That
means, a prepare_v2 can even failure if the statement be preparing is an
operation to an exclusive locked database.  Sounds very strange! Is it true?


It is. prepare_v2() sometimes has to read the schema from
the database file. And it cannot if another process has
an exclusive lock.

Dan.

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

Re: [sqlite] GUI for SQLite

2013-06-26 Thread Rose, John B 
fyi

http://www.barefeetware.com/sqlite/compare/?ml/

http://devtest.ws.utk.edu/tutorials/sqlite/gui/


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Rob Richardson [rdrichard...@rad-con.com]
Sent: Wednesday, June 26, 2013 12:40 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] GUI for SQLite

Not all of us.  Thanks for the list.

RobR, SQLiteSpy user and about to find Navicat.

___
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] GUI for SQLite

2013-06-26 Thread Rob Richardson 
Not all of us.  Thanks for the list.

RobR, SQLiteSpy user and about to find Navicat.

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

Re: [sqlite] GUI for SQLite

2013-06-26 Thread Rose, John B 
We have tried Firefox SQLite Manager, SQLite DB Browser and Navicat. 

They all seem fine and Navicat seems good as a paid option with more features 
than the other two.


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Rob Willett [rob.sql...@robertwillett.com]
Sent: Wednesday, June 26, 2013 11:49 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] GUI for SQLite

Well I just simply downloaded my free copy from the Apple App store. I entered 
absolutely zero information (apart from Apple knowing everything about men 
anyway). Can't say I've been plagued by spam from other vendors who have 
provided free downloads but there's a first time for everything.

It looks a very interesting product, it seems to do an awful lot (to my wholly 
untutored ignorant eye) and hasn't crashed in the last ten minutes. I like the 
ER diagrams you can make with it.

I'm not complaining and if I like it enough I might even buy the Pro version if 
I need the functionality.

BTW just for forms sake, I have zero connections with this company and until I 
saw this e-mail didn't even know this product existed.

Rob.

On 26 Jun 2013, at 16:42, RSmith  wrote:

> Well you need to "buy" a $0.00 registration key, for which you need to fill 
> in an inordinate amount of personal and contact information (just like buying 
> other things online). The sort of event that promises Inbox spam aplenty and 
> is too much hassle for most casual onlookers - but it does promise some sort 
> of interoperability between SQLite and MySQL etc.
> (Haven't used it, so just mentioning what it claims).
>
>
> On 2013/06/26 17:34, Paolo Bolzoni wrote:
>> So... no? It is gratis, but not open. thanks.
>>
>> On Wed, Jun 26, 2013 at 5:28 PM, Michael Black  wrote:
>>> Free doesn't necessarily mean open source
>>>
>
> ___
> 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] GUI for SQLite

2013-06-26 Thread Simon Slavin 

On 26 Jun 2013, at 4:42pm, RSmith  wrote:

> Well you need to "buy" a $0.00 registration key, for which you need to fill 
> in an inordinate amount of personal and contact information (just like buying 
> other things online). The sort of event that promises Inbox spam aplenty and 
> is too much hassle for most casual onlookers - but it does promise some sort 
> of interoperability between SQLite and MySQL etc.
> (Haven't used it, so just mentioning what it claims).

Unlike many of you I have a job which pays me money.  I can afford to buy 
stuff.  I would sooner just give someone five clams for something than give 
them (tacit) permission to use my email address for anything at all.  
Unfortunately, the systems that accept money from you tend to want your email 
address too, so they can send you a receipt.  And that has stopped me from 
buying stuff from some suppliers because again I’m unwilling to give them my 
email address.

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

Re: [sqlite] GUI for SQLite

2013-06-26 Thread Rob Willett 
Well I just simply downloaded my free copy from the Apple App store. I entered 
absolutely zero information (apart from Apple knowing everything about men 
anyway). Can't say I've been plagued by spam from other vendors who have 
provided free downloads but there's a first time for everything.

It looks a very interesting product, it seems to do an awful lot (to my wholly 
untutored ignorant eye) and hasn't crashed in the last ten minutes. I like the 
ER diagrams you can make with it.

I'm not complaining and if I like it enough I might even buy the Pro version if 
I need the functionality. 

BTW just for forms sake, I have zero connections with this company and until I 
saw this e-mail didn't even know this product existed.

Rob.

On 26 Jun 2013, at 16:42, RSmith  wrote:

> Well you need to "buy" a $0.00 registration key, for which you need to fill 
> in an inordinate amount of personal and contact information (just like buying 
> other things online). The sort of event that promises Inbox spam aplenty and 
> is too much hassle for most casual onlookers - but it does promise some sort 
> of interoperability between SQLite and MySQL etc.
> (Haven't used it, so just mentioning what it claims).
> 
> 
> On 2013/06/26 17:34, Paolo Bolzoni wrote:
>> So... no? It is gratis, but not open. thanks.
>> 
>> On Wed, Jun 26, 2013 at 5:28 PM, Michael Black  wrote:
>>> Free doesn't necessarily mean open source
>>> 
> 
> ___
> 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] GUI for SQLite

2013-06-26 Thread RSmith 
Well you need to "buy" a $0.00 registration key, for which you need to fill in an inordinate amount of personal and contact 
information (just like buying other things online). The sort of event that promises Inbox spam aplenty and is too much hassle for 
most casual onlookers - but it does promise some sort of interoperability between SQLite and MySQL etc.

(Haven't used it, so just mentioning what it claims).


On 2013/06/26 17:34, Paolo Bolzoni wrote:

So... no? It is gratis, but not open. thanks.

On Wed, Jun 26, 2013 at 5:28 PM, Michael Black  wrote:

Free doesn't necessarily mean open source



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

Re: [sqlite] GUI for SQLite

2013-06-26 Thread Paolo Bolzoni 
So... no? It is gratis, but not open. thanks.

On Wed, Jun 26, 2013 at 5:28 PM, Michael Black  wrote:
> Free doesn't necessarily mean open source
>
> http://www.valentina-db.com/en/get-free-studio
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paolo Bolzoni
> Sent: Wednesday, June 26, 2013 10:26 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] GUI for SQLite
>
> Are you sure it is free? I cannot find the code...
>
>
> ___
> 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] sqlite3: .import command handles quotation incorrectly

2013-06-26 Thread RSmith 



On 2013/06/26 15:21, Igor Tandetnik wrote:



For instance, what is the resulting fields from this CSV: [123, 9" Nail,
", Caliper, "set, up 5"", hold", 8 ]
In REAL CSV terms that would be:123  |  9 Nail,  | Caliper | set, up
5", hold  |  8


If by REAL CSV you mean the format specified in RFC 4180, then your example is not a well-formed CSV input (and the OP's example 
isn't either):


I do mean RFC4180, but do apologise, i did not imply that my CSV input was in any way REAL CSV, I meant if a real csv interpreter 
conforming to RFC4180 were to read the garbage I posted, it would come up with the result specified. The problem Reinhard is having 
is that he has explicitely non-standard (or non-real if you will) "CSV-like" coded input (from somewhere) and is suggesting a 
work-around of the SQLite .import function to allow his non-standard input to be imported. My examples were aimed at showing the 
problems of interpreting non-standard formatting specifically as well as the compatibility failure (with actual CSV input) - and 
specifically to the way(s) he suggested it to be imported.


Having done this many times I already know the answer to his problem, it's real easy, a simple command-line tool to make his weird 
format into a SQLite table - would take a few minutes to make and save him a lot of trouble - I even offered help doing it - but he 
is intent on arguing that SQLite should change and do it "smarter" - (which is his right) - and now I'm trying to show why it isn't 
smarter in the hope of finding a resolve.



If, on the other hand, by REAL CSV you mean something other than RFC 4180, then it would appear that the rules are not in fact set 
in stone, and are in fact subject to interpretation and disagreement.


Which isn't what I meant, but even if it were, SQLite would have to conform to "some" standard officially (else how would anyone be 
able to trust the importing?). There is no way to marry the supported format (whether RFC1480 or another CSV standard) to his 
proposed import plan so far.



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

Re: [sqlite] GUI for SQLite

2013-06-26 Thread Michael Black 
Free doesn't necessarily mean open source

http://www.valentina-db.com/en/get-free-studio


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paolo Bolzoni
Sent: Wednesday, June 26, 2013 10:26 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] GUI for SQLite

Are you sure it is free? I cannot find the code...


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

Re: [sqlite] GUI for SQLite

2013-06-26 Thread Paolo Bolzoni 
Are you sure it is free? I cannot find the code...

On Wed, Jun 26, 2013 at 6:28 AM, jorje  wrote:
>  Take a look on a free gui tool -- Valentina Studio. Amazing product!  IMO
> this is the best manager for SQLite for all platforms.
> http://www.valentina-db.com/en/valentina-studio-overview
>
>
>
>
> --
> View this message in context: 
> http://sqlite.1065341.n5.nabble.com/GUI-for-SQLite-tp11673p69626.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

[sqlite] The next-generation query planner

2013-06-26 Thread Richard Hipp 
The next-generation query planner (NGQP) is a rewrite of the query planner
for SQLite that is faster (reduced run-time for sqlite3_prepare()) and
generates better plans for queries (reducing the run-time for
sqlite3_step()).  More information about the NGQP is available here:

 http://www.sqlite.org/draft/queryplanner-ng.html

We've run literally millions and millions of test cases on the NGQP with no
problems.  We've tested in on Linux, Windows, Mac, OpenBSD, and Solaris,
and on 32-bit and 64-bit systems.  The Fossil server that manages the
SQLite website is running NGQP.  The version of Firefox on which this email
is being composed is running the NGQP.  Everything seems to work great.

Nevertheless, it is important that you test the NGQP in your application.

Amalgamations for the latest SQLite containing the NGQP are available from
the http://www.sqlite.org/draft/download.html page.  This should be a
drop-in replacement for the amalgamation you are currently using.  There
are no new APIs or compiler flags to fuss with.  Everything works exactly
as it did before, only a little faster.  You should be able to simply
recompile and end up with an application that (hopefully) runs a little
faster.

Please try this out, and let me know if of your successes and of any
problems you encounter.

-- 
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] sqlite3: .import command handles quotation incorrectly

2013-06-26 Thread Nißl Reinhard 
Hi,

BTW: I hate that TOFU posting, but Outlook doesn't allow me to do it any 
better. I'm sorry for that.

Your sample [123, 9" Nail, ", Caliper, "set, up 5"", hold", 8 ] gives currently 
the following column values (I used > and < to indicate the string bounderies, 
so that any white spaces are noticeable):

>123< | > 9" Nail, "< | > Caliper< | > "set, up 5"", hold"< | > 8 <

Given that your input has been stripped by the leading spaces after a column 
separator, or if the column separator has been defined as >, < you currently 
get:

>123< | >9" Nail, "< | >Caliper< | >set, up 5", hold< | >8 <

With my approach you still get:

>123< | >9" Nail, "< | >Caliper< | >set, up 5", hold< | >8 <

You expect to get:

>123< | >9 Nail, < | >Caliper< | >set, up 5", hold< | >8 <

But this is not what .import currently does.

To get that, .import would have to ignore every " besides when the cell is 
actually quoted.

This is my current suggestion for the code, dealing with splitting the row into 
columns and dealing with quoting (just from the editor, haven't tested it yet):

  char *z, c;
  int honorQuote = 1;
  int inQuote = 0;
  lineno++;
  azCol[0] = zLine;
  for(i=0, z=zLine; (c = *z)!=0; z++){
if( c=='"' ){
  if( honorQuote )
  {
inQuote = 1;
honorQuote = 0;
  }
  else if( inQuote ){
inQuote = 0;
honorQuote = 1;
  }
}
else
  honorQuote = 0;
if( c=='\n' ) lineno++;
if( !inQuote && c==p->separator[0] && strncmp(z,p->separator,nSep)==0 ){
  *z = 0;
  i++;
  if( imailto:sqlite-users-boun...@sqlite.org] 
Im Auftrag von RSmith
Gesendet: Mittwoch, 26. Juni 2013 14:48
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] sqlite3: .import command handles quotation incorrectly

Expanding on what Jay replied to:
> Hi,
>
> well we could discuss endlessly, what well formed CSV files are.
Not really, the rules are pretty set in stone, it isn't open to interpretation.


> Given that we cannot fix the generation of the CSV file, why not making the 
> importer a little bit smarter?
>
> The .import command already treats " as literal data, when it doesn't appear 
> at the beginning of the cell, but it requires an even number of " in the same 
> cell to don't get confused in breaking up the row into cells.
>
> All I ask for is to treat an odd number of " in a cell as literal data except 
> when the rule for dequoting applies, i. e. when the cell starts with a ".

This is impossible - How do you know where the end of a cell is if you are 
ignoring/counting Quotes???

For instance, what is the resulting fields from this CSV: [123, 9" Nail, ", 
Caliper, "set, up 5"", hold", 8 ]
In REAL CSV terms that would be:123  |  9 Nail,  | Caliper | set, up 5", 
hold  |  8

How would you want that to be interpreted?  Once we encounter the first quote, 
how do we know at which quote the field ends? How do 
we know how many quotes are in the field? Or do we just ignore quotes 
altogether and break on every comma - in which case the above 
becomes:   123  |  9" Nail  |  "  |  Caliper  |  "set  |  up 5""  | hold"  |  8
Surely it's easy to see how this is incorrect?
How about your other suggestion about taking fields starting with quotes to be 
quoted in pairs (even numbers) but otherwise ignoring 
odd numbered quotes - how do we know at which quote to stop counting?
Going strictly by your suggestion It might be interpreted as:  123 |  9" Nail  
|  , Caliper,  |  up 5""  |  hold"  |  8  |
or maybe even as:  123  |  9" Nail  |  , Caliper, set  |  up 5""  | hold"  |  8 
 |   (the interpreter would never be sure)

The interpreter working like this is not "a little bit smarter" but rather is 
very much non-conforming.

You have a very specific case of weirdly created data - I already offered help 
to give you an interpreter specific to it and will be 
happy to, but there is no way the SQlite can be altered like this, it will read 
all other CSV files wrong - and making a case 
specifc SQLite is possible (you can alter the C code and compile your own 
easily), but all future updates will need this adjustment. 
Submitting a patch will require your version to work on all other CSV file too 
- which as desribed above - it won't.

is there no way to fix the output? Or use an in-between step of converting the 
data before import from the weird layout to proper 
CSV? (we can help with that).

Have a great day!

___
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] sqlite3: .import command handles quotation incorrectly

2013-06-26 Thread Igor Tandetnik 

On 6/26/2013 8:48 AM, RSmith wrote:

Expanding on what Jay replied to:

well we could discuss endlessly, what well formed CSV files are.

Not really, the rules are pretty set in stone, it isn't open to
interpretation.

For instance, what is the resulting fields from this CSV: [123, 9" Nail,
", Caliper, "set, up 5"", hold", 8 ]
In REAL CSV terms that would be:123  |  9 Nail,  | Caliper | set, up
5", hold  |  8


If by REAL CSV you mean the format specified in RFC 4180, then your 
example is not a well-formed CSV input (and the OP's example isn't either):


2.5. If fields are not enclosed with double quotes, then double quotes 
may not appear inside the fields.
2.6. Fields containing line breaks (CRLF), double quotes, and commas 
should be enclosed in double-quotes.


If, on the other hand, by REAL CSV you mean something other than RFC 
4180, then it would appear that the rules are not in fact set in stone, 
and are in fact subject to interpretation and disagreement.

--
Igor Tandetnik

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

Re: [sqlite] GUI for SQLite

2013-06-26 Thread jorje 
 Take a look on a free gui tool -- Valentina Studio. Amazing product!  IMO
this is the best manager for SQLite for all platforms.  
http://www.valentina-db.com/en/valentina-studio-overview




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/GUI-for-SQLite-tp11673p69626.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] sqlite3: .import command handles quotation incorrectly

2013-06-26 Thread RSmith 

Expanding on what Jay replied to:

Hi,

well we could discuss endlessly, what well formed CSV files are.

Not really, the rules are pretty set in stone, it isn't open to interpretation.



Given that we cannot fix the generation of the CSV file, why not making the 
importer a little bit smarter?

The .import command already treats " as literal data, when it doesn't appear at the 
beginning of the cell, but it requires an even number of " in the same cell to don't 
get confused in breaking up the row into cells.

All I ask for is to treat an odd number of " in a cell as literal data except when 
the rule for dequoting applies, i. e. when the cell starts with a ".


This is impossible - How do you know where the end of a cell is if you are 
ignoring/counting Quotes???

For instance, what is the resulting fields from this CSV: [123, 9" Nail, ", Caliper, "set, up 
5"", hold", 8 ]
In REAL CSV terms that would be:123  |  9 Nail,  | Caliper | set, up 5", 
hold  |  8

How would you want that to be interpreted?  Once we encounter the first quote, how do we know at which quote the field ends? How do 
we know how many quotes are in the field? Or do we just ignore quotes altogether and break on every comma - in which case the above 
becomes:   123  |  9" Nail  |  "  |  Caliper  |  "set  |  up 5""  | hold"  |  8

Surely it's easy to see how this is incorrect?
How about your other suggestion about taking fields starting with quotes to be quoted in pairs (even numbers) but otherwise ignoring 
odd numbered quotes - how do we know at which quote to stop counting?

Going strictly by your suggestion It might be interpreted as:  123 |  9" Nail  |  , Caliper,  
|  up 5""  |  hold"  |  8  |
or maybe even as:  123  |  9" Nail  |  , Caliper, set  |  up 5""  | hold"  |  8 
 |   (the interpreter would never be sure)

The interpreter working like this is not "a little bit smarter" but rather is 
very much non-conforming.

You have a very specific case of weirdly created data - I already offered help to give you an interpreter specific to it and will be 
happy to, but there is no way the SQlite can be altered like this, it will read all other CSV files wrong - and making a case 
specifc SQLite is possible (you can alter the C code and compile your own easily), but all future updates will need this adjustment. 
Submitting a patch will require your version to work on all other CSV file too - which as desribed above - it won't.


is there no way to fix the output? Or use an in-between step of converting the data before import from the weird layout to proper 
CSV? (we can help with that).


Have a great day!

___
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

2013-06-26 Thread Niall O'Reilly 

On 26 Jun 2013, at 13:07, Jay A. Kreibich wrote:

> We've been through this before a
>  half-dozen times.  Everyone seems convinced it would be really easy
>  and really simple to make just one small change so that the importer
>  works with their version CSV.

Not everyone; I may be in a minority of one, but I can't help
thinking that it would be "really easy and really simple" for
anyone who routinely encounters a particular "troublesome"
CSV format to write a bespoke normalizer addressing their
particular need.

Niall O'Reilly

___
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

2013-06-26 Thread Jay A. Kreibich 
On Wed, Jun 26, 2013 at 12:35:55PM +0200, Nißl Reinhard scratched on the wall:

> well we could discuss endlessly, what well formed CSV files are.
> Given that we cannot fix the generation of the CSV file, why not
> making the importer a little bit smarter?

  The standard answer to this is, "Go ahead."

  The relative simplicity of the .import command is an issue that comes
  up every year or so on this list.  We've been through this before a
  half-dozen times.  Everyone seems convinced it would be really easy
  and really simple to make just one small change so that the importer
  works with their version CSV.

  Unfortunately, when you actually start to dig in and make such
  changes, they're rarely simple.  Making it better in one way often
  breaks it in other ways.  Thanks to the poorly defined standard
  (or, rather, the fact there are so many different standards) there
  is no easy way out of this, and it quickly turns into a really,
  really complex issue.  If it was simple, it would also be simple to
  write a program that converted files into a format that .import was
  happy with.

  

  At the end of the day, I'd MUCH rather have the SQLite team working
  on database features than on support commands in the shell.  If you
  feel the change is simple, easy, and won't break any existing
  behaviors, feel free to submit a patch.

  A year or so ago there was some effort to write a plug-in that would
  use the standard Python CSV module (at least, I think it was Python)
  for data import into SQLite.  The Python module is pretty good at
  importing most flavors of CSV... and as a result is an extremely
  complex bit of code.  Anyone know if that project got any traction?



> All I ask for is to treat an odd number of " in a cell as literal data
> except when the rule for dequoting applies, i. e. when the cell starts
> with a ".

  Sounds simple, except for the fact that quotes also help define cell
  boundaries (in cases when the data value has a comma).  It isn't about
  even or odd quotes because you don't know where the cells start and
  stop until you untangle the quotes.

   -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] sqlite3: .import command handles quotation incorrectly

2013-06-26 Thread Nißl Reinhard 
Hi,

well we could discuss endlessly, what well formed CSV files are. Given that we 
cannot fix the generation of the CSV file, why not making the importer a little 
bit smarter?

The .import command already treats " as literal data, when it doesn't appear at 
the beginning of the cell, but it requires an even number of " in the same cell 
to don't get confused in breaking up the row into cells.

All I ask for is to treat an odd number of " in a cell as literal data except 
when the rule for dequoting applies, i. e. when the cell starts with a ".

Bye.

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
Im Auftrag von Petite Abeille
Gesendet: Dienstag, 25. Juni 2013 19:27
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] sqlite3: .import command handles quotation incorrectly


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

Re: [sqlite] Questions about exclusive transation

2013-06-26 Thread Simon Slavin 

On 26 Jun 2013, at 8:08am, Woody Wu  wrote:

> Thanks for the analyzing.  After checked, I found it's the prepare_v2
> statement, previous to the step statement, returned SQLITE_BUSY.  That
> means, a prepare_v2 can even failure if the statement be preparing is an
> operation to an exclusive locked database.  Sounds very strange! Is it true?

How, precisely, do you obtain your exclusive lock ?  Are you checking the value 
returned by every SQLite3 call you use to obtain that lock ?

Always check the values returned for all the stages: _exec() or all three of 
_prepare(), _step(), and _finalize().  Whether you’re using assert() or some 
other mechanism, don’t just assume that, for instance, _finalize() never fails.

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

Re: [sqlite] Should I do analyze?

2013-06-26 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 25/06/13 22:46, Navaneeth.K.N wrote:
> 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?

Since you know what your queries and data are, why don't you benchmark it
and find out?

In general ANALYZE should help the query planner make better informed
decisions, but those decisions can never be perfect.  It is possible but
not probable that some could turn out worse.  The SQLite authors try to
make SQLite very predictable so the query solutions chosen tend that way.
 An upcoming release will have a new query planner that should work better
when there are a lot of joins.

  http://www.sqlite.org/draft/queryplanner-ng.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlHKlmUACgkQmOOfHg372QQhNwCeIth6bQJMfZHFVM/FijyoY79N
82AAn1NVyCVQj7CDPt+8grjaT41ODDxM
=X88q
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users