Re: [sqlite] imposter tables

[Richard Hipp]

On 6/14/17, Paul Sanderson  wrote:
> I am just taking a look at imposter tables and while the implementation is
> neat I am just wondering what their use is, or rather what they can achieve
> that a view can't achieve (and without the risk of DB corruption).

Imposter tables are used by the RBU extension
(https://www.sqlite.org/rbu.html) to allow indexes to be updated
independently from tables, and in key order, for reduced write
amplification and improved efficiency during bulk updates.

Minor coding errors in the use of imposter tables can corrupt the
database. If you feel you ust use them, do so with with great care.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem building amalgamation

[Jens Alfke]

> On Jun 14, 2017, at 11:03 AM, Simon Slavin  wrote:
> 
> This problem is especially bad on Apple devices, since many people use ".c" 
> as an extension for C++ files, when Apple’s compiler assumes they’ll use 
> ".cpp".

I think you mean “.C” (with a capital C) which I’ve seen used occasionally for 
C++, though not in a long time.
Since the Mac filesystem is case-insensitive (though case-preserving), I 
believe the logic that maps extensions to languages ignores case, so treats 
“.C” like “.c”.

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

Re: [sqlite] 3.19.3 README.md Doc bug

[petern]

I see.  Replacing sqlite.tar.gz by sqlite-autoconf-3190300.tar.gz likewise
produces the immediate directory 'sqlite-autoconf-3190300' rather than
'sqlite'.  Taken literally, the instructions are indeed correct.

I presumed, evidently incorrectly, that it would be preferrable to start in
the upacked source directory.  Thus, the bld directory would be created
under each source tree rather than a common bld directory where different
versions of the binaries would overwrite each other.



On Wed, Jun 14, 2017 at 8:48 AM, John McKown 
wrote:

> On Wed, Jun 14, 2017 at 10:40 AM, petern 
> wrote:
>
> > Was there a version in the past where the compile instructions made
> sense?
> >
> > tar xzf sqlite.tar.gz;#  Unpack the source tree into "sqlite"
> > mkdir bld;#  Build will occur in a sibling
> > directory
> > cd bld   ;#  Change to the build directory
> > ../sqlite/configure  ;#  Run the configure script
> >
> > Lost me there at the configure step...
> >
> > It seems to me the line should read simply
> >
> > ../configure  ;#  Run the configure script
> >
>
> ​Makes sense. At the time you issued the "tar" command, you are in
> directory "x". Perhaps the following will make more sense (stuff before the
> > is the current working directory)
>
> x>tar xzf sqlite.tar.gz
> x># above creates directory ./sqlite
> x>mkdir bld # make directory ./bld
> x>cd bld
> x/bld>../sqlite/configure
> x/bld> # up to directory x, then down in into directory sqlite & run
> configure residing there
> x/bld> # results of configure are put in this directory
> ​
>
>
> --
> Veni, Vidi, VISA: I came, I saw, I did a little shopping.
>
> Maranatha! <><
> John McKown
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem building amalgamation

[Keith Medcalf]

g++ assumes that all source input files are c++

sqlite3.c is C source, not C++

Compile the C files using gcc using the -c option (to output .o files)
Compile the C++ files using gcc or g++ using the -c option (to output .o files)
Link the files using g++ so that the c++ runtime is included
 OR link using gcc and manually add the -lstdc++ runtime library

Alternatively you can use gcc for the compile & link all-in-one step and 
manually add the library -lstdc++

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Jeff Archer
> Sent: Wednesday, 14 June, 2017 07:33
> To: SQLite mailing list
> Subject: [sqlite] Problem building amalgamation
> 
> SQLite 3.13.0
> 
> I have just grabbed the sqlite3.c and sqlite3.h from another (working)
> project and dropped then into a new project and I am seeing compile
> errors.
> 
> New project is a C++ project in Eclipse.  using GCC 5.4.0 from cygwin64.
> Just trying to make a simple command line app.
> 
> I'm sure I'm missing something simple but nothing is clicking...
> 
> 
> g++ -std=c++11 -fpermissive -P -dD -o HelloCPP.exe HelloCPP.cpp sqlite3.c
> 
> 
> Errors...
> 
> sqlite3.c:25153:48: error: cannot convert 'SrcList::SrcList_item*' to
> 'SrcList_item*' in initialization
>  struct SrcList_item *pItem = >a[k];
> ^
> sqlite3.c:25156:18: error: invalid use of incomplete type 'struct
> SrcList_item'
>  if( pItem->zDatabase ){
>   ^
> 
> sqlite3.c:25157:49: error: invalid use of incomplete type 'struct
> SrcList_item'
>sqlite3StrAccumAppendAll(pAccum, pItem->zDatabase);
>  ^
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Problem building amalgamation

[Simon Slavin]

On 14 Jun 2017, at 6:55pm, Clemens Ladisch  wrote:

> Jeff Archer wrote:
>> I have just grabbed the sqlite3.c and sqlite3.h from another (working)
>> project and dropped then into a new project and I am seeing compile errors.
>> 
>> New project is a C++ project
> 
> SQLite is not C++ but C.

But most compilers can cope with both in the same project.  Just make sure that 
your compiler compiles files with ".c" extension as C and not C++.

This problem is especially bad on Apple devices, since many people use ".c" as 
an extension for C++ files, when Apple’s compiler assumes they’ll use ".cpp".

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

Re: [sqlite] Problem building amalgamation

[Clemens Ladisch]

Jeff Archer wrote:
> I have just grabbed the sqlite3.c and sqlite3.h from another (working)
> project and dropped then into a new project and I am seeing compile errors.
>
> New project is a C++ project

SQLite is not C++ but C.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] imposter tables

[Jens Alfke]

> On Jun 14, 2017, at 4:52 AM, Paul Sanderson  
> wrote:
> 
> I am just taking a look at imposter tables and while the implementation is
> neat I am just wondering what their use is

Well, right near the top of the page it says "Imposter tables are intended for 
analysis and debugging only.” So it doesn’t sound like they’re for use in a 
production system.

(On the other hand, my mind immediately jumps to wondering if they could be 
used to implement map/reduce views (a la CouchDB). The indexes-on-expressions 
feature gets partway toward enabling map/reduce, except for the fact it can 
only add a single row to the index per table row.)

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

Re: [sqlite] INTEGER PRIMARY KEY AUTOINCREMENT

[Peter Aronson]

On 6/14/2017 5:42 AM, R Smith wrote:


On 2017/06/14 7:08 AM, Wout Mertens wrote:

Is there a way to specify the starting rowid when using autoincrement?

Or should I insert and then remove a row with the id set to one less than
the desired id?


This is quite easy, but first it is helpful to understand the mechanism 
by which SQLite keeps track of the Primary Key Auto-Inc value.


If you define a primary key that is of type INT, and omit the 
AUTOINCREMENT directive, then you will still have a primary key that 
increments if you don't specify the value directly by virtue of primary 
keys being UNIQUE and requires a value, so it's safe to bet if you 
adding a key without specifying the value for it, you intend for it to 
be automatic.
BUT, the next increment value depends on the DB engine guessing what it 
should be based on existing key values (which can cause re-used keys 
that used to exist for now-deleted items).


If you do define the AUTOINCREMENT directive, then SQLite promises to 
always increment the value by one from the last time a value was 
inserted - whether that value has been deleted or changed etc. - i.e. it 
promises to never re-use a key. It achieves this by keeping a table, 
namely the "sqlite_sequence" system-generated table, with references to 
each table using AUTOINCREMENT and its Key based on the last value used 
for the referred table.


You can simply change the values in this reference table to inform the 
next AUTOINCREMENTed value you would like for the specific table-name.


I'm not sure now if references inside this sqlite_sequence table exists 
the moment you create a table with an AUTOINCREMENT key, or only once 
you insert for the first time, but it is easy to check and handle both 
ways.


Taking all this into account, that is why (as another post suggested) 
the equivalent in SQLite for other SQL DB's:

ALTER TABLE myTable AUTOINCREMENT = 5;

would be something like:
UPDATE sqlite_sequence SET seq = 5 WHERE name = 'myTable';


Good luck!
Ryan

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


It is worth noting that there is no entry for a table with an 
autoincrement column in sqlite_sequence until that table has at least 
one row, so you need to perform an INSERT instead of an UPDATE (I don't 
know if sqlite_sequence has a unique constraint on name, so I don't know 
if REPLACE would work for both cases).


sqlite> create table t1 (c1 integer primary key autoincrement,c2 text);

sqlite> select * from sqlite_sequence;


sqlite> insert into t1 (c2) values ('stuff');

sqlite> select * from sqlite_sequence;

t1|1

sqlite>

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

Re: [sqlite] 3.19.3 README.md Doc bug

[John McKown]

On Wed, Jun 14, 2017 at 10:40 AM, petern 
wrote:

> Was there a version in the past where the compile instructions made sense?
>
> tar xzf sqlite.tar.gz;#  Unpack the source tree into "sqlite"
> mkdir bld;#  Build will occur in a sibling
> directory
> cd bld   ;#  Change to the build directory
> ../sqlite/configure  ;#  Run the configure script
>
> Lost me there at the configure step...
>
> It seems to me the line should read simply
>
> ../configure  ;#  Run the configure script
>

​Makes sense. At the time you issued the "tar" command, you are in
directory "x". Perhaps the following will make more sense (stuff before the
> is the current working directory)

x>tar xzf sqlite.tar.gz
x># above creates directory ./sqlite
x>mkdir bld # make directory ./bld
x>cd bld
x/bld>../sqlite/configure
x/bld> # up to directory x, then down in into directory sqlite & run
configure residing there
x/bld> # results of configure are put in this directory
​


-- 
Veni, Vidi, VISA: I came, I saw, I did a little shopping.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 3.19.3 README.md Doc bug

[petern]

Was there a version in the past where the compile instructions made sense?

tar xzf sqlite.tar.gz;#  Unpack the source tree into "sqlite"
mkdir bld;#  Build will occur in a sibling directory
cd bld   ;#  Change to the build directory
../sqlite/configure  ;#  Run the configure script

Lost me there at the configure step...

It seems to me the line should read simply

../configure  ;#  Run the configure script
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] performance issue on a read only database

[Hervé Gauthier]

Hi all, I tried the sugestion made by Clemens using PRAGMA locking_mode =
EXCLUSIVE.

This works fine for me.

Thanks a lot.

2017-06-13 20:46 GMT+02:00 David Raymond :

> "Also please note that SQLite does a 'lazy open'.  When you create your
> connection to the database file, SQLite doesn’t actually open the file.
> Instead the file handling is done the first time SQLite needs the data from
> the file.  So the first SELECT after a new connection is made takes longer
> than the others.
>
> Simon."
>
>
> I think this is the big thing. "Opening" a database doesn't actually do
> much. That 14ms is probably from parsing the database schema. That won't
> happen until your first select.
>
> When doing a "begin transaction" the default is a deferred begin, which
> won't lock the database or read the schema until you run a
> select/update/etc. Once it does lock the file with that first select after
> the begin, then the connection doesn't have to parse anything again since
> no other process could have changed it.
>
> With individual selects though, the connection has to check every time if
> the schema has been changed on it while it was sitting idle. Remember that
> a "read only connection" only means that "I can't change it." It doesn't
> mean "no one else can change it either."
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] imposter tables

[Stephen Chrzanowski]

I didn't know about this functionality.  I know it is a 'sharp tool', but
what I've gathered out by reading the document is that this only affects
indexes, and if I understand the doc, only when the imposter table is
created or is used to modify the indexes.  What if an imposter table is
created at database inception, and is not changed or modified, and only
used for R/O queries?  Does it become a blunt instrument or is it still a
sharp utility knife?

Personally, I doubt I'd ever use this.  I've never required knowledge of
what the index is beyond faith in knowing that the engine knows what its
doing, or, needs to get updated periodically.  (I know of different methods
of indexing in different engines, but, never needed to know which is used
or implemented)


On Wed, Jun 14, 2017 at 8:22 AM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:

> Can you create an imposter table on a view. A view has no associated b-tree
> so I would think not!
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-
> Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
> On 14 June 2017 at 13:11, Simon Slavin  wrote:
>
> >
> >
> > On 14 Jun 2017, at 12:52pm, Paul Sanderson  >
> > wrote:
> >
> > > The only benefit I can see is that you know the imposter table is
> showing
> > > you exactly what is in the index, where the view is my interpretation
> of
> > > the SQL needed to show what is in the index. Is this the main benefit?
> or
> > > am I missing something?
> >
> > Hmm.  If you create a view with calculated columns, and then create an
> > impostor table on that view, do you get a virtual table with
> pre-calculated
> > columns ?
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem building amalgamation

[Jeff Archer]

SQLite 3.13.0

I have just grabbed the sqlite3.c and sqlite3.h from another (working)
project and dropped then into a new project and I am seeing compile errors.

New project is a C++ project in Eclipse.  using GCC 5.4.0 from cygwin64.
Just trying to make a simple command line app.

I'm sure I'm missing something simple but nothing is clicking...


g++ -std=c++11 -fpermissive -P -dD -o HelloCPP.exe HelloCPP.cpp sqlite3.c


Errors...

sqlite3.c:25153:48: error: cannot convert 'SrcList::SrcList_item*' to
'SrcList_item*' in initialization
 struct SrcList_item *pItem = >a[k];
^
sqlite3.c:25156:18: error: invalid use of incomplete type 'struct
SrcList_item'
 if( pItem->zDatabase ){
  ^

sqlite3.c:25157:49: error: invalid use of incomplete type 'struct
SrcList_item'
   sqlite3StrAccumAppendAll(pAccum, pItem->zDatabase);
 ^
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INTEGER PRIMARY KEY AUTOINCREMENT

[wout.mertens]

Thank you so much all! This mailinglist is amazing :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY AUTOINCREMENT

[R Smith]

On 2017/06/14 7:08 AM, Wout Mertens wrote:

Is there a way to specify the starting rowid when using autoincrement?

Or should I insert and then remove a row with the id set to one less than
the desired id?


This is quite easy, but first it is helpful to understand the mechanism 
by which SQLite keeps track of the Primary Key Auto-Inc value.


If you define a primary key that is of type INT, and omit the 
AUTOINCREMENT directive, then you will still have a primary key that 
increments if you don't specify the value directly by virtue of primary 
keys being UNIQUE and requires a value, so it's safe to bet if you 
adding a key without specifying the value for it, you intend for it to 
be automatic.
BUT, the next increment value depends on the DB engine guessing what it 
should be based on existing key values (which can cause re-used keys 
that used to exist for now-deleted items).


If you do define the AUTOINCREMENT directive, then SQLite promises to 
always increment the value by one from the last time a value was 
inserted - whether that value has been deleted or changed etc. - i.e. it 
promises to never re-use a key. It achieves this by keeping a table, 
namely the "sqlite_sequence" system-generated table, with references to 
each table using AUTOINCREMENT and its Key based on the last value used 
for the referred table.


You can simply change the values in this reference table to inform the 
next AUTOINCREMENTed value you would like for the specific table-name.


I'm not sure now if references inside this sqlite_sequence table exists 
the moment you create a table with an AUTOINCREMENT key, or only once 
you insert for the first time, but it is easy to check and handle both ways.


Taking all this into account, that is why (as another post suggested) 
the equivalent in SQLite for other SQL DB's:

ALTER TABLE myTable AUTOINCREMENT = 5;

would be something like:
UPDATE sqlite_sequence SET seq = 5 WHERE name = 'myTable';


Good luck!
Ryan

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

Re: [sqlite] imposter tables

[Paul Sanderson]

Can you create an imposter table on a view. A view has no associated b-tree
so I would think not!

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 14 June 2017 at 13:11, Simon Slavin  wrote:

>
>
> On 14 Jun 2017, at 12:52pm, Paul Sanderson 
> wrote:
>
> > The only benefit I can see is that you know the imposter table is showing
> > you exactly what is in the index, where the view is my interpretation of
> > the SQL needed to show what is in the index. Is this the main benefit? or
> > am I missing something?
>
> Hmm.  If you create a view with calculated columns, and then create an
> impostor table on that view, do you get a virtual table with pre-calculated
> columns ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] imposter tables

[Paul Sanderson]

I am just taking a look at imposter tables and while the implementation is
neat I am just wondering what their use is, or rather what they can achieve
that a view can't achieve (and without the risk of DB corruption).

For instance an imposter table created on an index such as the following
from Skype

CREATE INDEX chat_idx_chat_room_name_service_name ON chat(room_name,
service_name)

.imposter chat_idx_chat_room_name_service_name imptable

can be simulated with a view

CREATE TEMP VIEW impview AS select room_name, service_name, _rowid_ FROM
chat

Querying either impview or imptable should, as far as I can see, produce
the same results.

I can see that the imposter table will be faster as it links directly to
the b-tree, but with the risk of corrupting the index as described on
https://sqlite.org/imposter.html.

The only benefit I can see is that you know the imposter table is showing
you exactly what is in the index, where the view is my interpretation of
the SQL needed to show what is in the index. Is this the main benefit? or
am I missing something?

Are there instances where a view created as I have done above cannot
simulate an imposter table?



Cheers






Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786 <+44%201326%20572786>
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Outputting to CSV - row is not quoted when there are no spaces

[Hick Gunter]

I have found it much simpler and more robust to write a CSV virtual table to 
handle the formatting.

e.g.

CREATE VIRTUAL TABLE csv_input USING csv (  );

Opens the named file, reads the first line and interprets the contents as a 
list of field names. You can then

INSERT INTO   SELECT  FROM csv_input;


CREATE VIRTUAL TABLE csv_output USING csv ( ,  );

Creates the named file and writes the fieldlist, You can then

INSERT INTO csv_output SELECT ...;


For ease of implementation I just handle NULL (do not write anything), TEXT 
(including text rendering of numeric values; write the result of 
sqlite3_value_text) and BLOB (encode the result of sqlite3_value_blob in x'...' 
format). Any TEXT that contains non-printable characters or the chosen 
delimiter is treated as a BLOB. No fussing around with quotes, doubling quotes, 
escaping, etc. and the conversion of values is handled by SQLite.

The same approach can be used to implement whatever flavor of CSV you prefer.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Ketil Froyn
Gesendet: Mittwoch, 14. Juni 2017 10:03
An: SQLite mailing list 
Betreff: Re: [sqlite] Outputting to CSV - row is not quoted when there are no 
spaces

Dan, I'd recommend using a tool that actually understands CSV, and make it 
parse the input and create new quoted output. I haven't seen your sed and awk, 
but I'm pretty sure it's easy to find some special cases where the text 
includes comma, quotes or even newlines that will break your output.

A simple stab at what I think you need would be something like this
python3 script:

$ cat quotecsv.py
#!/usr/bin/env python3
import csv
import sys

csv_in = csv.reader(sys.stdin, delimiter=',', quotechar='"') csv_out = 
csv.writer(sys.stdout, delimiter=',', quotechar='"',
quoting=csv.QUOTE_ALL)
for row in csv_in:
csv_out.writerow(row)
### That's it  ###

Then you can pipe some csv through this, here's a sample line where fields are 
only quoted where necessary, and the result after piping through the python 
script:

$ echo 'a,b,c,"d e","f, g"'
a,b,c,"d e","f, g"
$ echo 'a,b,c,"d e","f, g"' | ./quotecsv.py "a","b","c","d e","f, g"

Cheers, Ketil

On 14 June 2017 at 07:46,   wrote:
> Thanks Richard, in the end I added the quotes using some SED and AWK
> as I'm using SQLite as part of a BASH script.
>
> Thanks
>
>
> On 2017-06-13 14:36, Richard Hipp wrote:
>>
>> SQLite does not provide that capability, that I recall.
>>
>> But surely it would not be too difficult for you to do your own
>> custom patch, or even to write a short program to output the data in
>> the precise format you desire?
>>
>> On 6/12/17, d...@dan.bz  wrote:
>>>
>>> Hi,
>>>
>>> When outputting to CSV with '.mode csv' is there a way that all rows
>>> can be quoted even if there are no spaces? For example, here is a 1
>>> line from the output:
>>>
>>> spotify:track:5vlDIGBTQmlyfERBnJOnbJ,Kiso,Circles,100.019
>>>
>>> I would like it to output:
>>>
>>> "spotify:track:5vlDIGBTQmlyfERBnJOnbJ","Kiso","Circles",100.019
>>>
>>> Thanks!
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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


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

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Outputting to CSV - row is not quoted when there are no spaces

[Ketil Froyn]

Dan, I'd recommend using a tool that actually understands CSV, and
make it parse the input and create new quoted output. I haven't seen
your sed and awk, but I'm pretty sure it's easy to find some special
cases where the text includes comma, quotes or even newlines that will
break your output.

A simple stab at what I think you need would be something like this
python3 script:

$ cat quotecsv.py
#!/usr/bin/env python3
import csv
import sys

csv_in = csv.reader(sys.stdin, delimiter=',', quotechar='"')
csv_out = csv.writer(sys.stdout, delimiter=',', quotechar='"',
quoting=csv.QUOTE_ALL)
for row in csv_in:
csv_out.writerow(row)
### That's it  ###

Then you can pipe some csv through this, here's a sample line where
fields are only quoted where necessary, and the result after piping
through the python script:

$ echo 'a,b,c,"d e","f, g"'
a,b,c,"d e","f, g"
$ echo 'a,b,c,"d e","f, g"' | ./quotecsv.py
"a","b","c","d e","f, g"

Cheers, Ketil

On 14 June 2017 at 07:46,   wrote:
> Thanks Richard, in the end I added the quotes using some SED and AWK as I'm
> using SQLite as part of a BASH script.
>
> Thanks
>
>
> On 2017-06-13 14:36, Richard Hipp wrote:
>>
>> SQLite does not provide that capability, that I recall.
>>
>> But surely it would not be too difficult for you to do your own custom
>> patch, or even to write a short program to output the data in the
>> precise format you desire?
>>
>> On 6/12/17, d...@dan.bz  wrote:
>>>
>>> Hi,
>>>
>>> When outputting to CSV with '.mode csv' is there a way that all rows can
>>> be quoted even if there are no spaces? For example, here is a 1 line
>>> from the output:
>>>
>>> spotify:track:5vlDIGBTQmlyfERBnJOnbJ,Kiso,Circles,100.019
>>>
>>> I would like it to output:
>>>
>>> "spotify:track:5vlDIGBTQmlyfERBnJOnbJ","Kiso","Circles",100.019
>>>
>>> Thanks!
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] How-to and best practice for session extension

[Robert M. Münch]

Ok, after using the session extension and doing some tests, we face one problem 
I would like to get some feedback from the experts, how to best solve this.

Context: User's can import CSV data into our application. We use a bunch of 
fixed tables and one for the user data. Of course the layout (number of 
columns) of this table depends on the user data structure. Users are allowed to 
add & remove columns. Removing is implemented by just flagging a column as "not 
there", so no schema change. But adding a column of course needs an ALTER TABLE 
operation. Hence, the user table only gets bigger.

Problem: As soon as the schema changes, we can't apply recorded changesets 
anymore.

An option would be to keep the user data in a column/value format and somehow 
create a view from it that looks like a normal DB table. Not sure if this is 
possible, how fast this is and if it makes sense at all.

Next option, somehow keep track of schema changes and revert them manually 
before a changeset is applied. Has anyone done this?

The next option would be to take a look at the session extension and see if a 
more relaxed use of changesets is possible. Here I think as long as all columns 
are there a change can be applied. For additional columns the values would just 
be deleted. Or even better add reversing of ALTER TABLE commands to the 
changeset handler.

Viele Grüsse.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY AUTOINCREMENT

[J Decker]

from https://sqlite.org/autoinc.html

On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly
given a value, then it will be filled automatically with an unused integer,
usually one more than the largest ROWID currently in use. This is true
regardless of whether or not the AUTOINCREMENT keyword is used.

conversely, if it IS specified then it will be used.

https://stackoverflow.com/questions/692856/set-start-value-for-autoincrement-in-sqlite

suggests that UPDATE SQLITE_SEQUENCE SET seq =  WHERE name = ''
will work to initialize the value before any inserts are done.

On Tue, Jun 13, 2017 at 10:08 PM, Wout Mertens 
wrote:

> Is there a way to specify the starting rowid when using autoincrement?
>
> Or should I insert and then remove a row with the id set to one less than
> the desired id?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users