Re: [sqlite] escape quote for csv import

[Roland Hughes]

and "tools.ietf.org" is exactly WHERE on the sqlite.org path?



From: sqlite-users-boun...@sqlite.org on behalf of Petite Abeille
Sent: Tuesday, June 18, 2013 5:03 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] escape quote for csv import

On Jun 18, 2013, at 10:46 PM, Roland Hughes <rhug...@cincinnati-test.com> wrote:

> It isn't documented anywhere, but, you have to BOTH quote the string AND 
> double up the quotes inside of it.

Indeed:

   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] escape quote for csv import

[Roland Hughes]

Actually,

I found the "correct" solution for the version of SQLite3 bundled with Linux 
Mint13 KDE.

2in|"2"""

It isn't documented anywhere, but, you have to BOTH quote the string AND double 
up the quotes inside of it.

Something like this should be documented in the FAQ or on the home page.  I 
found it to be a very common question with a lot of worthless answers, at least 
worthless given the current state of the tools, perhaps they were valid at one 
point.

Roland

From: sqlite-users-boun...@sqlite.org on behalf of Petite Abeille
Sent: Tuesday, June 18, 2013 4:19 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] escape quote for csv import

On Jun 18, 2013, at 10:02 PM, Clemens Ladisch  wrote:

> (There is no official CSV standard, and there is no widely supported
> escaping mechanism.)

Perhaps. But that's not an excuse to ignore the de facto convention:

Common Format and MIME Type for Comma-Separated Values (CSV) Files
http://tools.ietf.org/html/rfc4180

Plus, that tired argument about "no official csv standard" is a bit self 
-fulfilling.
___
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] escape quote for csv import

[Roland Hughes]

Not an option, but thanks for the suggestion.

From: sqlite-users-boun...@sqlite.org on behalf of Clemens Ladisch
Sent: Tuesday, June 18, 2013 4:02 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] escape quote for csv import

Roland Hughes wrote:
> How does one escape a in a CSV file so it will correctly import?

The sqlite3 tool allows to configure the separator, but the quote
character for delimiting fields is hardcoded.

(There is no official CSV standard, and there is no widely supported
escaping mechanism.)

> I can only massage the CSV

Convert it into properly formatted SQL INSERT statements.


Regards,
Clemens
___
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] escape quote for csv import

[Roland Hughes]

I'm sure this question has been asked a thousand times, but I did not find a 
useable answer anywhere on the web.  

How does one escape a in a CSV file so it will correctly import?

I have data coming from a translation database.  No, I cannot go fix the 
database, I can only massage the CSV and possibly request a tiny tweak to the 
module which generates the csv file.  The source database is NOT sqlite.

We have many occurrences where I need to escape a single " so it can survive 
and get into the database.  Our separator has dutifully been changed to the | 
character.  Why does it need to survive?  There are many things like this:

English  TargetLanguage
2in 2"

Nice huh?

Under MOST text systems you can do something like  or <> to replace 
a quote and make it pass through to the database.  What escape sequences does 
the SQLite import utility recognize?

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

Re: [sqlite] Correct way to open multiple in memory databases

[Roland Hughes]

Your response doesn't say anything useful.

When *WE* tell you the kit required for the project, it is just that, the kit 
required for the project.  Given the rules and regulations for this project the 
kit must be as is without hacks.  The target will run roughly 12 years without 
modification unless a catastrophic bug is found.  Given that such an issue 
rarely shows up within the first 5 years the rules and regs require publicly 
available archives.  No personal hacks or desk drawer builds allowed.

From: sqlite-users-boun...@sqlite.org on behalf of Richard Hipp
Sent: Friday, May 24, 2013 1:27 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Correct way to open multiple in memory databases

On Fri, May 24, 2013 at 1:14 PM, Roland Hughes
<rhug...@cincinnati-test.com>wrote:

> I have whatever came with Qt 4.8.x and Linux Mint 13 KDE
>

That doesn't tell us anything useful.

What actions have *YOU* taken to enable URI filenames.  If you haven't
taken any actions, then (as described in the documentation) URI filenames
are disabled for backwards compatibility and the in-memory database naming
scheme you used will not work.

Please follow the instructions for enabling URI filenames and try again.

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

Re: [sqlite] Correct way to open multiple in memory databases

[Roland Hughes]

Sorry for top post, client site forces worthless MS email on me.

"Works" would require a politician dictionary.  ":memory:" is a one-shot 
wonder.  The example from the documentation allowed multiple threads to use the 
_same_ in memory database.


From: sqlite-users-boun...@sqlite.org on behalf of Simon Slavin
Sent: Friday, May 24, 2013 4:11 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Correct way to open multiple in memory databases

On 24 May 2013, at 7:06pm, Stephan Beal <sgb...@googlemail.com> wrote:

> On Fri, May 24, 2013 at 5:03 PM, Roland Hughes
> <rhug...@cincinnati-test.com>wrote:
>
>> "file:memdb1?mode=memory=shared",
>>
>> It ends up creating files in the executable directory.
>
> Have you tried using ":memory:"? Each instance of an in-memory db opened
> that way is unique, despite the non-unique name:

Yes, Roland, please check to see whether this works.  It won't give you shared 
cache but it's a good test to figure out what's wrong.

The example Roland used, by the way is straight from the SQLite documentation:

<http://www.sqlite.org/inmemorydb.html>

near the bottom.  That's probably why he feels it should work.

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

Re: [sqlite] Correct way to open multiple in memory databases

[Roland Hughes]

I have whatever came with Qt 4.8.x and Linux Mint 13 KDE

From: sqlite-users-boun...@sqlite.org on behalf of Richard Hipp
Sent: Friday, May 24, 2013 11:14 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Correct way to open multiple in memory databases

On Fri, May 24, 2013 at 11:03 AM, Roland Hughes <rhug...@cincinnati-test.com
> wrote:

> All,
>
> I'm looking for the correct way to build a string (within Qt) to open
> multiple in memory databases.  The example here:
>
> http://www.sqlite.org/inmemorydb.html
>
> Does not appear to work.
>
> "file:memdb1?mode=memory=shared",
>
> It ends up creating files in the executable directory.
>

Did you activate URI filenames?  http://www.sqlite.org/uri.html


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



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

[sqlite] Correct way to open multiple in memory databases

[Roland Hughes]

All,

I'm looking for the correct way to build a string (within Qt) to open multiple 
in memory databases.  The example here:

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

Does not appear to work.

"file:memdb1?mode=memory=shared",

It ends up creating files in the executable directory.

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

Re: [sqlite] Writing in a blob

[Roland Hughes]

It actually does make sense to add chunking to sqlite.  There would be
some computational overhead, but, that all depends on the chunk size and
the cache size of the database.  It makes no sense to implement YAFS
(Yet Another File System) inside of SQLite.

While many here view SQLite only in terms of desktop applications, the
reality is it gets used in embedded systems for data gathering and image
processing.  Some of these systems gather data in real time, and others
poll devices periodically to obtain a "unit" of work.  The devices
generating the data have completely different operating systems and even
different Endianism than the data collection system.  These units of
work are eventually uploaded to yet another system (usually midrange or
mainframe) where they are processed into industrial strength database
systems in a much more granular fashion.  Chunking of data allows for
units of work to be arbitrary sizes.

I understand that many of you reading this may not grasp the application
so I will bastardize some real life stuff you might be able to picture.
Many of you probably run BOINC and participate in some noble research
project with the idle time of your computer.  (If you don't, you
should.)  No matter the project, they bust up massive amounts of data
into chunks.  Somewhere a table in a database identifies each chunk, the
date it was collected, processed, who processed it, a corresponding
results chunk, and some summary result information fields.  When your
BOINC client connects with the server it scans the database to identify
the next available chunk or chunks, assigns them to you, then sends the
chunks to your client for processing.  The database and the client do
not care about the content of the chunk, just its size and transmission
CRC.  For lack of a better description, the client plug-in for the
project is the only piece which knows about the content of the chunk and
how to process it.

It should be possible to add chunking to the database itself in such a
manner that any user who does not actually use blobs in their database
does not pay a computational penalty for the feature.

There is an ever increasing number of embedded systems which would like
to use a "linkable" database, but stumble when it comes to raw data
storage.

Just my 0.02.




On Fri, 2013-04-26 at 18:16 +0100, Simon Slavin wrote:

> On 26 Apr 2013, at 5:26pm, Stephen Chrzanowski <pontia...@gmail.com> wrote:
> 
> > ALL THAT SAID, I doubt it'd get implemented
> 
> I'm also in this category.  In fact I hope it doesn't get implemented.  Yes, 
> technically it can be done.  But it's the sort of thing people assign as 
> Computer Science homework.
> 


-- 
Roland Hughes, President
Logikal Solutions
(630)-205-1593

http://www.theminimumyouneedtoknow.com
http://www.infiniteexposure.net

No U.S. troops have ever lost their lives defending our ethanol
reserves.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Writing in a blob

[Roland Hughes]

Speaking as an IT professional with 20+ years in the field, I would have
to say adding any kind of "file system" support to SQLite would be a
horrible thing.  Yes, I've used Oracle.  I've also used the only real
product Oracle has, RDB on OpenVMS.  I've written books covering MySQL,
PostgreSQL, and Object databases like POET.

Lite is called lite for a reason.  Bare functionality with a lot of
speed.

The architects for this product need to take a lesson from the old DOS
xBase systems.  Blobs should not be handled as one big unit.   They need
to be given unique ID's and stored in fixed size chunks off in a hidden
table much like a "memo" field was back in the day.  The "hidden" or
child table supporting the blob column would have a key of ID +
sequence.  The actual row should be ID, Sequence, BytesUsed, Chunk.
They BytesUsed allows you to keep exact byte sizes.  All Chunk data
types should be a raw byte data type.  There should be multiple chunk
column types:  chunk24, chunkM, and chunkXM for 1024 bytes, 1Meg, and
10Meg chunk column types.


On Tue, 2013-04-23 at 09:50 +0200, Dominique Devienne wrote:

> On Mon, Apr 22, 2013 at 2:10 PM, Simon Slavin <slav...@bigfraud.org> wrote:
> 
> > On 22 Apr 2013, at 12:39pm, Paolo Bolzoni wrote:
> > > But I noticed that sqlite3_blob_write cannot increase the size of the
> > pointed
> > > open blob. So I ask, there is a way to treat a blob as a stream so I can
> > write
> > > or read values in it with ease?
> >
> > Unfortunately the size (length) of the BLOB is very significant to the
> > larger space-handling aspects of SQLite's file format.  Making a BLOB
> > longer could force SQLite to move the data from page to page and do lots of
> > other internal reorganisation.  So you can reserve extra space when you
> > write the BLOB, and you can read whatever you want, but the documentation
> > is accurate.
> >
> 
> I also really wish SQLite blobs would map directly to the usual FILE*
> semantic, both in being able to grow a blob via writing (and truncate it
> too), but also and more importantly not rewriting the whole row or blob
> when modifying only a few bytes of the blob, but only affected pages.
> Basically another level of indirection, where the row holds only a blob
> locator (like in Oracle), and the blob value is in separate, not
> necessarily contiguous pages, as described here for example:
> http://jonathanlewis.wordpress.com/2013/03/22/lob-update/. That way only
> modified blob pages would need to participate in the transaction. SQLite is
> not MVCC like Oracle, but the ability to at least not overwrite the whole
> blob when changing 1 byte would be great. (I'm assuming there isn't, but
> I'm no SQLite expert). My $0.02. --DD
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


-- 
Roland Hughes, President
Logikal Solutions
(630)-205-1593

http://www.theminimumyouneedtoknow.com
http://www.infiniteexposure.net

No U.S. troops have ever lost their lives defending our ethanol
reserves.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64bit compatibility warnings

[Roland Hughes]

The short answer would be no.  It has been some time since I looked at the MS 
compiler, but, int and int64 are two entirely different data types...unless you 
use a compiler switch to FORCE 64-bit data types int is 32-bit and int64 is 
64-bit. 

http://software.intel.com/en-us/articles/size-of-long-integer-type-on-different-architecture-and-os


From: sqlite-users-boun...@sqlite.org on behalf of Richard Hipp
Sent: Monday, April 08, 2013 7:41 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] 64bit compatibility warnings

On Sun, Apr 7, 2013 at 1:06 PM, Alexandr Němec  wrote:

>
> Line 6766   u.bc.r.flags = (u16)(UNPACKED_INCRKEY * (1 & (u.bc.oc -
> OP_SeekLt)));  WARNING: conversion from 'u16' to 'u8', possible
> loss of data
> Line 71133 iBuf = p->iReadOff % p->nBuffer;
> WARNING: conversion from 'i64' to 'int', possible loss of data
> Line 71209 iBuf = p->iReadOff % p->nBuffer;
>  WARNING: conversion from 'i64' to 'int', possible loss of data
> Line 71286 iBuf = iStart % nBuf;
> WARNING: conversion from 'i64' to 'int', possible loss of data
> Line 71574 p->iBufEnd = p->iBufStart = (iStart % nBuf); WARNING:
> conversion from 'i64' to 'int', possible loss of data
>

The first warning is harmless and results from a prior datatype change.
Dan has already fixed that one.  The other four appear to be due to an MSVC
compiler bug, since every (i64%int) operation will always yield a value
that can fit in an int, no?
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] 64bit compatibility warnings

[Roland Hughes]

On Mon, 2013-04-08 at 09:31 +0200, Kees Nuyt wrote:

> On Mon, 08 Apr 2013 08:39:49 +0200, Alexandr N?mec <a.ne...@atlas.cz>
> wrote:
> 
> > Hi all,
> > 
> > thanks for your replies, but unfortunately they did not answer
> > my original question whether these warnings are harmless and
> > can be ignored or not. These warnings reported by the VS C++
> > compiler are about "possible loss of data", so it is a
> > situation when a "int64" expression result is assigned to an
> > "int" variable for example. In such cases these warnings are
> > very legitimate. If such an assignment is the real intention
> > of the programmer, an explicit (int) typecast should be added,
> > because it will
> > 
> >- tell to the rest of the world, that the programmer knows
> >  what he is doing, ie. he really wants to "truncate" the result,
> 
> The programmers know what they are doing.
> As <http://www.sqlite.org/faq.html#q17> tells, they are harmless if all
> tests scripts succeed. The test scripts are run before every SQLite
> release. SQLite is not released if a test fails. So, the warnings can be
> ignored.
> 


Or, it could be the tests simply don't exercise those possibilities.
I've been in IT far too long to trust that a test suite is "complete".


-- 
Roland Hughes, President
Logikal Solutions
(630)-205-1593

http://www.theminimumyouneedtoknow.com
http://www.infiniteexposure.net

No U.S. troops have ever lost their lives defending our ethanol
reserves.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users