Re: [sqlite] request for feature: insert or update

[James K. Lowden]

On Thu, 27 Jun 2013 12:05:00 +0200
Patrik Nilsson  wrote:

> Now I'm doing: "insert or ignore into table_test (id) values (1)" and
> then issue an update statement. I think "insert or update" will
> increase performance.

The "insert or update" statement is an unnecessary complication.  It
adds no expressive power to the language; the effect is readily
expressed today with an update statement followed by an insert.  

Performance is not a reason to modify the grammar.  It's simply up to
the implementation to divine the shortest physical path to the logically
expressed outcome.  

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

Re: [sqlite] Performance regression since 3.7.15

[Elan Feingold]

> Have you run ANALYZE on your database?  Does that make a difference?

Yes, and no, it doesn’t seem to make a difference for this query. 

> Can you send (1) the schema (2) the query that is giving problems, and (3)
> the content of the sqlite_stat1 table after you have run ANALYZE?   If you
> can combine all of the above into a script, that would be great!
> 
> If you send (1..3) above and you give us written permission to include the
> query in our test suite, that would be off-the-chain terrific.

Please find items 1..3 in this file: http://www.plexapp.com/elan/sqlite_bug.txt

You have our permission to include the query in your test suite.

Thanks for an amazing product.

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

Re: [sqlite] Performance regression since 3.7.15

[Richard Hipp]

On Thu, Jun 27, 2013 at 6:50 PM, Elan Feingold  wrote:

> >> Q2 runs excruciatingly slowly in 3.7.15.2 (1m 28sec). We then observed
> >> that Q2 was fast in 3.7.14 (300ms), but slow in 3.7.15.2 and later
> (tested
> >> up until 3.7.17).
> >>
> >
> > Have you tried it with the NGQP snapshot?
> >
> > The NGQP was announced on this mailing list yesterday, but I see that the
> > OP only subscribed as of today.  Please see the original posting at
> > http://www.mail-archive.com/sqlite-users%40sqlite.org/msg77855.html
>
> Good timing :-)
>
> I tried with the NGQP snapshot and the query indeed runs fast. Didn’t
> compare to <= 3.7.14, but it was fast.
>

Have you run ANALYZE on your database?  Does that make a difference?

Can you send (1) the schema (2) the query that is giving problems, and (3)
the content of the sqlite_stat1 table after you have run ANALYZE?   If you
can combine all of the above into a script, that would be great!

If you send (1..3) above and you give us written permission to include the
query in our test suite, that would be off-the-chain terrific.



>
> What’s the rough timeframe for integrating NGQP into an official release?
>

Sometime this summer.


>
> Thanks,
>
> -elan
> ___
> 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

Re: [sqlite] Performance regression since 3.7.15

[Elan Feingold]

>> Q2 runs excruciatingly slowly in 3.7.15.2 (1m 28sec). We then observed
>> that Q2 was fast in 3.7.14 (300ms), but slow in 3.7.15.2 and later (tested
>> up until 3.7.17).
>> 
> 
> Have you tried it with the NGQP snapshot?
> 
> The NGQP was announced on this mailing list yesterday, but I see that the
> OP only subscribed as of today.  Please see the original posting at
> http://www.mail-archive.com/sqlite-users%40sqlite.org/msg77855.html

Good timing :-)

I tried with the NGQP snapshot and the query indeed runs fast. Didn’t compare 
to <= 3.7.14, but it was fast.

What’s the rough timeframe for integrating NGQP into an official release?

Thanks,

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

[Konrad Hambrick]

P.S.  This might be helpful too.

I downloaded yesterday's amalgamation, backed up shell.c and then overwrote it 
with your most recent version.

$ ./sqlite3 foo.db
SQLite version 3.8.0 2013-06-26 13:22:28
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo( col1 int, col2 varchar(255) ) ;
sqlite> .import "|gawk 'BEGIN{ for ( i = 0 ; i < 10 ; i ++ ){ print i \"| this 
is row \" i } ; exit 0 ; }'" foo
sqlite> select * from foo
   ...> ;
0| this is row 0
1| this is row 1
2| this is row 2
3| this is row 3
4| this is row 4
5| this is row 5
6| this is row 6
7| this is row 7
8| this is row 8
9| this is row 9


> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of Konrad Hambrick
> Sent: Thursday, June 27, 2013 4:01 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite3: .import command handles quotation incorrectly
> 
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org 
> > [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> > Of Richard Hipp
> > Sent: Thursday, June 27, 2013 9:16 AM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] sqlite3: .import command handles quotation incorrectly
> >
> > On Thu, Jun 27, 2013 at 8:58 AM, Richard Hipp  wrote:
> >
> > >
> > > What if, instead of a new command, we simply extend the ".import" command
> > > so that if the first character of the filename is "|" it interprets the
> > > filename as a pipe instead of a file.  The ".output" command works that 
> > > way.
> > >
> >
> > This alternative concept has now been implemented on trunk.  Example:
> >
> 
> Richard --
> 
> I had a chance to download shell.c and compile from trunk.
> 
> A sample session is below.
> 
> Thanks you very much !
> 
> -- kjh
> 
> sqlite> create table foo( col1 int, col2 varchar(255) ) ;
> sqlite> .import "|gawk 'BEGIN{ for ( i = 0 ; i < 10 ; i ++ ){ print i \"| 
> this is row \" i
> } ; exit 0 ; }'" foo
> sqlite> select * from foo ;
> 0| this is row 0
> 1| this is row 1
> 2| this is row 2
> 3| this is row 3
> 4| this is row 4
> 5| this is row 5
> 6| this is row 6
> 7| this is row 7
> 8| this is row 8
> 9| this is row 9
> ___
> 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

[Konrad Hambrick]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of Richard Hipp
> Sent: Thursday, June 27, 2013 9:16 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite3: .import command handles quotation incorrectly
> 
> On Thu, Jun 27, 2013 at 8:58 AM, Richard Hipp  wrote:
> 
> >
> > What if, instead of a new command, we simply extend the ".import" command
> > so that if the first character of the filename is "|" it interprets the
> > filename as a pipe instead of a file.  The ".output" command works that way.
> >
> 
> This alternative concept has now been implemented on trunk.  Example:
> 

Richard --

I had a chance to download shell.c and compile from trunk.

A sample session is below.

Thanks you very much !

-- kjh

sqlite> create table foo( col1 int, col2 varchar(255) ) ;
sqlite> .import "|gawk 'BEGIN{ for ( i = 0 ; i < 10 ; i ++ ){ print i \"| this 
is row \" i } ; exit 0 ; }'" foo
sqlite> select * from foo ;
0| this is row 0
1| this is row 1
2| this is row 2
3| this is row 3
4| this is row 4
5| this is row 5
6| this is row 6
7| this is row 7
8| this is row 8
9| this is row 9
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite database on Dropbox, Google Drive, MS SkyDrive, Ubuntu One or SAMBA share

[Simon Slavin]

On 27 Jun 2013, at 8:38pm, joe.fis...@tanguaylab.com wrote:

> I use LibreOffice files in shared space and I have never had a problem.

Do you have just one user with the file open at a time, or are there times when 
two or more users are accessing the same database ?

> I believe the "~lock." files work well.
> Dropbox apparently doesn't sync files that are locked -- which is a good 
> thing.
> Would SQLite DB files work the same way?

Dropbox lets everyone edit their own copy of the file.  As long as it gets a 
chance to sync the copies once one person has edited one, it’s fine.  If two 
people both edit their own copy at the same time Dropbox will make you choose 
which copy 'wins'.  This is not a criticism of dropbox: all systems which sync 
entire files work that way.

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

[sqlite] SQLite database on Dropbox, Google Drive, MS SkyDrive, Ubuntu One or SAMBA share

[joe.fis...@tanguaylab.com]

Anyone,

Does anyone have good or bad experiences using a SQLite database in a 
shared folder?
The 'Dropbox / Drive / SkyDrive / One' are of course a bit different 
than a SAMBA share because they are copies that get synced versus SAMBA 
which is the one and only copy.
Normally I would just be accessing the database from one computer 
(Linux, Mac, Windows) at a time. I use many different computers so 
storing the database in shared space is convenient.


I use LibreOffice files in shared space and I have never had a problem. 
I believe the "~lock." files work well.
Dropbox apparently doesn't sync files that are locked -- which is a good 
thing.

Would SQLite DB files work the same way?


Joe Fisher
Oregon State University

___
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

[Konrad Hambrick]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of Richard Hipp
> Sent: Thursday, June 27, 2013 9:16 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite3: .import command handles quotation incorrectly
> 
> On Thu, Jun 27, 2013 at 8:58 AM, Richard Hipp  wrote:
> 
> >
> > What if, instead of a new command, we simply extend the ".import" command
> > so that if the first character of the filename is "|" it interprets the
> > filename as a pipe instead of a file.  The ".output" command works that way.
> >
> 
> This alternative concept has now been implemented on trunk.



Dang, you're quick :)

This opens a lot of possibilities for me and I don't have to patch shell.c any 
more 

One of the handiest uses is importing directly from .xls via Perl and 
Spreadsheet::ParseExcel
and the xls2csv( ) function ...

Thank you, Richard !!

-- kjh


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

Re: [sqlite] R*Tree constraint error when inserting valid data

[Tobias Kolb]

OK, I tried the SQLite command line tool 3.7.17 and inserted all 1 million
records without any error. Then I cleared the table, ran the same SQL again
in SQLite Studio and *bang* same error. Seems to be a problem of SQLite
Studio. Anyway, I'm glad thats no bug in SQLite and my problem is solved.

@Yan: The area size depends on the time. I collect some raw sensor data for
about 30sec in a BLOB linked to the area. This can be a quite large or
small area depending if you're moving or standing still. Later i have to
find time chunks collected in a defined area.

Thanks all!


2013/6/27 Yan Seiner 

> Tobias Kolb wrote:
>
>> Hi,
>>
>> I'm currently experimenting with the R*Tree support in SQLite. I have to
>> collect some log data over a timespan and save it linked to the area (not
>> the exact path, just a minimum bounding rect around) it was collected
>> (min/max latitude and min/max longitude). Based on this data I want to do
>> range queries like "get all records within a area (rectangle)". R*Trees
>> seems perfect for this use case.
>>
>> So I've created my log data table (but that doesn't matter) and a virtual
>> rtree table:
>>
>> CREATE VIRTUAL TABLE log_chunks_geoindex USING rtree(
>>id,
>>latitude_min,
>>latitude_max,
>>longitude_min,
>>longitude_max
>> );
>>
>> The latitude/longitude values are stored as floating point values (e.g.
>> 49.12345, 8.67890). I generated a million records for testing and tried
>> inserting them in the virtual table. A few hundrets inserted fine, then I
>> got stucked with this one:
>>
>> insert into log_chunks_geoindex (id, latitude_min, latitude_max,
>> longitude_min, longitude_max)
>> values(473, 49.000472, 49.000473, 8.000472, 8.000473);
>>
>> Error: constraint failed
>>
>> Which constraint is affected in this case? The only constraint I know is
>> that max value has to be greater or equal than min value. But this is the
>> case! I guess it has something to do with that SQLite stores R*Tree values
>> only as single precision (32bit) floating point values. But the
>> documentation says that if the inserted value cannot be represented by a
>> 32bit floating point the value is rounded down for lower-bound coordinated
>> and rounded up for upper-bound coordinates. So the constraint (min<=max)
>> should always be met in this case. If I change latitude_max from 49.000473
>> to 49.0004731 it inserts well.
>>
>>
> At a guess you might be running into some rounding error.  Why are your
> bounding areas so small?  Can you make them bigger?
>
> I use a much larger area (about 10 miles square in my case) and then do a
> brute force search on all results returned to find the one I want.  This
> works quite reliably.
>
> --
> Project Management Consulting and Training
> http://www.**ridgelineconsultingllc.com
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] R*Tree constraint error when inserting valid data

[Yan Seiner]

Tobias Kolb wrote:

Hi,

I'm currently experimenting with the R*Tree support in SQLite. I have to
collect some log data over a timespan and save it linked to the area (not
the exact path, just a minimum bounding rect around) it was collected
(min/max latitude and min/max longitude). Based on this data I want to do
range queries like "get all records within a area (rectangle)". R*Trees
seems perfect for this use case.

So I've created my log data table (but that doesn't matter) and a virtual
rtree table:

CREATE VIRTUAL TABLE log_chunks_geoindex USING rtree(
   id,
   latitude_min,
   latitude_max,
   longitude_min,
   longitude_max
);

The latitude/longitude values are stored as floating point values (e.g.
49.12345, 8.67890). I generated a million records for testing and tried
inserting them in the virtual table. A few hundrets inserted fine, then I
got stucked with this one:

insert into log_chunks_geoindex (id, latitude_min, latitude_max,
longitude_min, longitude_max)
values(473, 49.000472, 49.000473, 8.000472, 8.000473);

Error: constraint failed

Which constraint is affected in this case? The only constraint I know is
that max value has to be greater or equal than min value. But this is the
case! I guess it has something to do with that SQLite stores R*Tree values
only as single precision (32bit) floating point values. But the
documentation says that if the inserted value cannot be represented by a
32bit floating point the value is rounded down for lower-bound coordinated
and rounded up for upper-bound coordinates. So the constraint (min<=max)
should always be met in this case. If I change latitude_max from 49.000473
to 49.0004731 it inserts well.
  
At a guess you might be running into some rounding error.  Why are your 
bounding areas so small?  Can you make them bigger?


I use a much larger area (about 10 miles square in my case) and then do 
a brute force search on all results returned to find the one I want.  
This works quite reliably.


--
Project Management Consulting and Training
http://www.ridgelineconsultingllc.com

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

Re: [sqlite] R*Tree constraint error when inserting valid data

[Tobias Kolb]

No, there isn't. I just copy/pasted the insert out of my long script.
Insert 1 to 472 works well, #473 was just the first one that fails. Hm,
maybe the problem is SQLiteStudio? I'll check the command line tool of
SQLite...


2013/6/27 Dan Kennedy 

> On 06/27/2013 09:19 PM, Tobias Kolb wrote:
>
>> Hi,
>>
>> I'm currently experimenting with the R*Tree support in SQLite. I have to
>> collect some log data over a timespan and save it linked to the area (not
>> the exact path, just a minimum bounding rect around) it was collected
>> (min/max latitude and min/max longitude). Based on this data I want to do
>> range queries like "get all records within a area (rectangle)". R*Trees
>> seems perfect for this use case.
>>
>> So I've created my log data table (but that doesn't matter) and a virtual
>> rtree table:
>>
>> CREATE VIRTUAL TABLE log_chunks_geoindex USING rtree(
>> id,
>> latitude_min,
>> latitude_max,
>> longitude_min,
>> longitude_max
>> );
>>
>> The latitude/longitude values are stored as floating point values (e.g.
>> 49.12345, 8.67890). I generated a million records for testing and tried
>> inserting them in the virtual table. A few hundrets inserted fine, then I
>> got stucked with this one:
>>
>> insert into log_chunks_geoindex (id, latitude_min, latitude_max,
>> longitude_min, longitude_max)
>> values(473, 49.000472, 49.000473, 8.000472, 8.000473);
>>
>
> That works here. Is there already a record with id=473 in the
> table?
>
>
> __**_
> 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] R*Tree constraint error when inserting valid data

[Dan Kennedy]

On 06/27/2013 09:19 PM, Tobias Kolb wrote:

Hi,

I'm currently experimenting with the R*Tree support in SQLite. I have to
collect some log data over a timespan and save it linked to the area (not
the exact path, just a minimum bounding rect around) it was collected
(min/max latitude and min/max longitude). Based on this data I want to do
range queries like "get all records within a area (rectangle)". R*Trees
seems perfect for this use case.

So I've created my log data table (but that doesn't matter) and a virtual
rtree table:

CREATE VIRTUAL TABLE log_chunks_geoindex USING rtree(
id,
latitude_min,
latitude_max,
longitude_min,
longitude_max
);

The latitude/longitude values are stored as floating point values (e.g.
49.12345, 8.67890). I generated a million records for testing and tried
inserting them in the virtual table. A few hundrets inserted fine, then I
got stucked with this one:

insert into log_chunks_geoindex (id, latitude_min, latitude_max,
longitude_min, longitude_max)
values(473, 49.000472, 49.000473, 8.000472, 8.000473);


That works here. Is there already a record with id=473 in the
table?


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

[sqlite] R*Tree constraint error when inserting valid data

[Tobias Kolb]

Hi,

I'm currently experimenting with the R*Tree support in SQLite. I have to
collect some log data over a timespan and save it linked to the area (not
the exact path, just a minimum bounding rect around) it was collected
(min/max latitude and min/max longitude). Based on this data I want to do
range queries like "get all records within a area (rectangle)". R*Trees
seems perfect for this use case.

So I've created my log data table (but that doesn't matter) and a virtual
rtree table:

CREATE VIRTUAL TABLE log_chunks_geoindex USING rtree(
   id,
   latitude_min,
   latitude_max,
   longitude_min,
   longitude_max
);

The latitude/longitude values are stored as floating point values (e.g.
49.12345, 8.67890). I generated a million records for testing and tried
inserting them in the virtual table. A few hundrets inserted fine, then I
got stucked with this one:

insert into log_chunks_geoindex (id, latitude_min, latitude_max,
longitude_min, longitude_max)
values(473, 49.000472, 49.000473, 8.000472, 8.000473);

Error: constraint failed

Which constraint is affected in this case? The only constraint I know is
that max value has to be greater or equal than min value. But this is the
case! I guess it has something to do with that SQLite stores R*Tree values
only as single precision (32bit) floating point values. But the
documentation says that if the inserted value cannot be represented by a
32bit floating point the value is rounded down for lower-bound coordinated
and rounded up for upper-bound coordinates. So the constraint (min<=max)
should always be met in this case. If I change latitude_max from 49.000473
to 49.0004731 it inserts well.

I'm using SQLiteStudio 2.1.4 which is build upon SQLite 3.7.16.1

Cane somebody help me?

Thanks,
Tobias
___
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

[Richard Hipp]

On Thu, Jun 27, 2013 at 8:58 AM, Richard Hipp  wrote:

>
> What if, instead of a new command, we simply extend the ".import" command
> so that if the first character of the filename is "|" it interprets the
> filename as a pipe instead of a file.  The ".output" command works that way.
>

This alternative concept has now been implemented on trunk.  Example:

script1.awk is:

BEGIN {
  print "x,y"
  for(i=1; i<=10; i++){
 print i ",this is string " i
  }
  exit(0);
}

Then run:

drh@tallis:~/sqlite/bld$ ./sqlite3
SQLite version 3.8.0 2013-06-27 14:07:53
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .mode csv
sqlite> .import '|awk -f script1.awk' t1
sqlite> select * from t1;
1,"this is string 1"
2,"this is string 2"
3,"this is string 3"
4,"this is string 4"
5,"this is string 5"
6,"this is string 6"
7,"this is string 7"
8,"this is string 8"
9,"this is string 9"
10,"this is string 10"
sqlite>

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

[Konrad Hambrick]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of Richard Hipp
> Sent: Thursday, June 27, 2013 8:30 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite3: .import command handles quotation incorrectly
> 
> On Thu, Jun 27, 2013 at 9:23 AM, Konrad Hambrick  wrote:
> 
> > How do I learb more about the Contributor License Agreement ?
> >
> > I don't see it on the WebSite ...
> >
> 
> Background information:  http://www.sqlite.org/copyright.html
> CLA: http://www.sqlite.org/copyright-release.pdf
> 
>
Thanks !  

I'll pass it on to my Boss for a signature.

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

[Richard Hipp]

On Thu, Jun 27, 2013 at 9:23 AM, Konrad Hambrick  wrote:

> How do I learb more about the Contributor License Agreement ?
>
> I don't see it on the WebSite ...
>

Background information:  http://www.sqlite.org/copyright.html
CLA: http://www.sqlite.org/copyright-release.pdf


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

[Konrad Hambrick]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of Richard Hipp
> Sent: Thursday, June 27, 2013 7:59 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite3: .import command handles quotation incorrectly
> 
> On Thu, Jun 27, 2013 at 8:53 AM, Konrad Hambrick  wrote:
> 
> >
> > Hmmm ... I don't believe my patch file went thru ...
> >
> > Is there an acceptable method to include text files ?
> >
> 
> We cannot accept your patch anyhow, unless you have a Contributor License
> Agreement on file.  To do otherwise would jeopardize the public domain
> status of SQLite.
> 
Thanks for the feedback, Richard.  

How do I learb more about the Contributor License Agreement ?

I don't see it on the WebSite ...

> But I am looking into your idea...
> 
> What if, instead of a new command, we simply extend the ".import" command
> so that if the first character of the filename is "|" it interprets the
> filename as a pipe instead of a file.  The ".output" command works that way.
> 
> One downside is that the entire command has to be in a single argument,
> which complicates the use of ' and " characters in the command itself.

I looked at the |command option for .import before I settled on a new .pimport
command but I didn't want to have to mess with the command line arg logic in
the do_meta_command( ) function.

That's why I opted for the .pimport command instead ... It greatly simplified 
command line handling -- the Table is in azArg[1] and then I popen the literal 
line that follows azArg[1] :)

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

Re: [sqlite] request for feature: insert or update

[Patrik Nilsson]

I believe the performance of my current statements is better and clearer.

>>> Now I'm doing: "insert or ignore into table_test (id) values (1)" and
>>> then issue an update statement. I think "insert or update" will
>>> increase performance.

Although I'm not happy with it.

/Patrik

On 06/27/2013 03:08 PM, Hick Gunter wrote:
> Use parameters and bind...
> 
> REPLACE INTO table_test
> SELECT :key, a, b, :val FROM table_test WHERE id=:key
> UNION ALL
> SELECT :key, NULL, NULL, :val
> LIMIT 1;
> 
> -Ursprüngliche Nachricht-
> Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com]
> Gesendet: Donnerstag, 27. Juni 2013 15:01
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] request for feature: insert or update
> 
> Thank you for your suggestion!
> 
> I would like to have all in one statement, then "insert or update" is 
> perfect. This is least error prone. If you split the statement into several, 
> you will end up in more statements to test and more bugs. (You write "3" on 
> two places.)
> 
> Working with blobs, I expect this procedure to waste more time.
> 
>> REPLACE INTO table_test
>>SELECT 1, a, b, 3 FROM table_test WHERE id=1
>>UNION ALL
>>SELECT 1, NULL, NULL, 3
>>LIMIT 1;
> 
> /Patrik
> 
> On 06/27/2013 02:46 PM, Richard Hipp wrote:
>> On Thu, Jun 27, 2013 at 6:05 AM, Patrik Nilsson
>> wrote:
>>
>>> Hi All!
>>>
>>> A feature I'm missing is a syntax like with "insert or update".
>>>
>>> You define a table as:
>>> "create table table_test (id as integer primary key, a as integer, b
>>> as integer, c as integer)"
>>>
>>> Then you know that the "id" is unique and you only can have one row
>>> with that integer.
>>>
>>> Then you can give the following statements:
>>> insert or update into table_test (id,c) values (1,3)
>>>
>>
>> REPLACE INTO table_test
>>SELECT 1, a, b, 3 FROM table_test WHERE id=1
>>UNION ALL
>>SELECT 1, NULL, NULL, 3
>>LIMIT 1;
>>
>>
>>> insert or update into table_test (id,b) values (1,2) insert or update
>>> into table_test (id,a) values (1,1) insert or update into table_test
>>> (id,a) values (5,13)
>>>
>>> This result is the following set:
>>> 1|1|2|3
>>> 5|13||
>>>
>>> Now I'm doing: "insert or ignore into table_test (id) values (1)" and
>>> then issue an update statement. I think "insert or update" will
>>> increase performance.
>>>
>>> If the "insert or update" can't perform its operation, it can issue a
>>> SQLITE_AMBIGUOUS error.
>>>
>>> Best regards,
>>> Patrik
>>>
>>> --
>>> ASCII ribbon campaign ( )
>>>  against HTML e-mail   X
>>>  www.asciiribbon.org  / \
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>>
> 
> --
> ASCII ribbon campaign ( )
>  against HTML e-mail   X
>  www.asciiribbon.org  / \
> ___
> 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
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] request for feature: insert or update

[Hick Gunter]

Use parameters and bind...

REPLACE INTO table_test
SELECT :key, a, b, :val FROM table_test WHERE id=:key
UNION ALL
SELECT :key, NULL, NULL, :val
LIMIT 1;

-Ursprüngliche Nachricht-
Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com]
Gesendet: Donnerstag, 27. Juni 2013 15:01
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] request for feature: insert or update

Thank you for your suggestion!

I would like to have all in one statement, then "insert or update" is perfect. 
This is least error prone. If you split the statement into several, you will 
end up in more statements to test and more bugs. (You write "3" on two places.)

Working with blobs, I expect this procedure to waste more time.

> REPLACE INTO table_test
>SELECT 1, a, b, 3 FROM table_test WHERE id=1
>UNION ALL
>SELECT 1, NULL, NULL, 3
>LIMIT 1;

/Patrik

On 06/27/2013 02:46 PM, Richard Hipp wrote:
> On Thu, Jun 27, 2013 at 6:05 AM, Patrik Nilsson
> wrote:
>
>> Hi All!
>>
>> A feature I'm missing is a syntax like with "insert or update".
>>
>> You define a table as:
>> "create table table_test (id as integer primary key, a as integer, b
>> as integer, c as integer)"
>>
>> Then you know that the "id" is unique and you only can have one row
>> with that integer.
>>
>> Then you can give the following statements:
>> insert or update into table_test (id,c) values (1,3)
>>
>
> REPLACE INTO table_test
>SELECT 1, a, b, 3 FROM table_test WHERE id=1
>UNION ALL
>SELECT 1, NULL, NULL, 3
>LIMIT 1;
>
>
>> insert or update into table_test (id,b) values (1,2) insert or update
>> into table_test (id,a) values (1,1) insert or update into table_test
>> (id,a) values (5,13)
>>
>> This result is the following set:
>> 1|1|2|3
>> 5|13||
>>
>> Now I'm doing: "insert or ignore into table_test (id) values (1)" and
>> then issue an update statement. I think "insert or update" will
>> increase performance.
>>
>> If the "insert or update" can't perform its operation, it can issue a
>> SQLITE_AMBIGUOUS error.
>>
>> Best regards,
>> Patrik
>>
>> --
>> ASCII ribbon campaign ( )
>>  against HTML e-mail   X
>>  www.asciiribbon.org  / \
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>

--
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
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] request for feature: insert or update

[Patrik Nilsson]

Thank you for your suggestion!

I would like to have all in one statement, then "insert or update" is
perfect. This is least error prone. If you split the statement into
several, you will end up in more statements to test and more bugs. (You
write "3" on two places.)

Working with blobs, I expect this procedure to waste more time.

> REPLACE INTO table_test
>SELECT 1, a, b, 3 FROM table_test WHERE id=1
>UNION ALL
>SELECT 1, NULL, NULL, 3
>LIMIT 1;

/Patrik

On 06/27/2013 02:46 PM, Richard Hipp wrote:
> On Thu, Jun 27, 2013 at 6:05 AM, Patrik Nilsson
> wrote:
> 
>> Hi All!
>>
>> A feature I'm missing is a syntax like with "insert or update".
>>
>> You define a table as:
>> "create table table_test (id as integer primary key, a as integer, b as
>> integer, c as integer)"
>>
>> Then you know that the "id" is unique and you only can have one row with
>> that integer.
>>
>> Then you can give the following statements:
>> insert or update into table_test (id,c) values (1,3)
>>
> 
> REPLACE INTO table_test
>SELECT 1, a, b, 3 FROM table_test WHERE id=1
>UNION ALL
>SELECT 1, NULL, NULL, 3
>LIMIT 1;
> 
> 
>> insert or update into table_test (id,b) values (1,2)
>> insert or update into table_test (id,a) values (1,1)
>> insert or update into table_test (id,a) values (5,13)
>>
>> This result is the following set:
>> 1|1|2|3
>> 5|13||
>>
>> Now I'm doing: "insert or ignore into table_test (id) values (1)" and
>> then issue an update statement. I think "insert or update" will increase
>> performance.
>>
>> If the "insert or update" can't perform its operation, it can issue a
>> SQLITE_AMBIGUOUS error.
>>
>> Best regards,
>> Patrik
>>
>> --
>> ASCII ribbon campaign ( )
>>  against HTML e-mail   X
>>  www.asciiribbon.org  / \
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> 
> 
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 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

[Richard Hipp]

On Thu, Jun 27, 2013 at 8:53 AM, Konrad Hambrick  wrote:

>
> Hmmm ... I don't believe my patch file went thru ...
>
> Is there an acceptable method to include text files ?
>

We cannot accept your patch anyhow, unless you have a Contributor License
Agreement on file.  To do otherwise would jeopardize the public domain
status of SQLite.

But I am looking into your idea...

What if, instead of a new command, we simply extend the ".import" command
so that if the first character of the filename is "|" it interprets the
filename as a pipe instead of a file.  The ".output" command works that way.

One downside is that the entire command has to be in a single argument,
which complicates the use of ' and " characters in the command itself.

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

[Konrad Hambrick]

Hmmm ... I don't believe my patch file went thru ...

Is there an acceptable method to include text files ?

Thanks.

-- kjh

___
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

[Konrad Hambrick]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] 
> On Behalf Of RSmith
> Sent: Wednesday, June 26, 2013 10:21 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite3: .import command handles quotation incorrectly
> 
All --

IMO, CSV Files generally suck.

I often had to resort to an external CSV-File preprocessor to clean up 
various Broken CSV File Formats.

But it took two-steps and a temp file:

   1. mypreprocessor Broken.CSV > Cleaned.TXT 

   2. echo "
  .import Cleaned.TXT TargetTable ;
  .exit ;" |sqlite3 -separator '|' MyDataBase.db 

One thing that I've found to be handy without breaking sqlite3 or adding 
too much code to shell.c was a new sqlite3 dot-command: .pimport ...

the .pimport command imports data from a pipe instead of a text file.

Syntax example:

   .pimport TargetTable command arg [arg [arg] ...] ;

Here's a sample session.

$ ./sqlite3 foo.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo( col1 int, col2 varchar(255) ) ;
sqlite> .pimport foo gawk 'BEGIN { for( i = 1 ; i <= 10 ; i ++ ){ print i "|" 
"this is string " i } ; exit 0 }' ;
sqlite> select * from foo ;
1|this is string 1
2|this is string 2
3|this is string 3
4|this is string 4
5|this is string 5
6|this is string 6
7|this is string 7
8|this is string 8
9|this is string 9
10|this is string 10
sqlite> .exit

Attached is a patch for shell.c  please use it if it's helpful.

While there's code for WIN32, I've never needed it nor tested it ...

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

Re: [sqlite] request for feature: insert or update

[Richard Hipp]

On Thu, Jun 27, 2013 at 6:05 AM, Patrik Nilsson
wrote:

> Hi All!
>
> A feature I'm missing is a syntax like with "insert or update".
>
> You define a table as:
> "create table table_test (id as integer primary key, a as integer, b as
> integer, c as integer)"
>
> Then you know that the "id" is unique and you only can have one row with
> that integer.
>
> Then you can give the following statements:
> insert or update into table_test (id,c) values (1,3)
>

REPLACE INTO table_test
   SELECT 1, a, b, 3 FROM table_test WHERE id=1
   UNION ALL
   SELECT 1, NULL, NULL, 3
   LIMIT 1;


> insert or update into table_test (id,b) values (1,2)
> insert or update into table_test (id,a) values (1,1)
> insert or update into table_test (id,a) values (5,13)
>
> This result is the following set:
> 1|1|2|3
> 5|13||
>
> Now I'm doing: "insert or ignore into table_test (id) values (1)" and
> then issue an update statement. I think "insert or update" will increase
> performance.
>
> If the "insert or update" can't perform its operation, it can issue a
> SQLITE_AMBIGUOUS error.
>
> Best regards,
> Patrik
>
> --
> ASCII ribbon campaign ( )
>  against HTML e-mail   X
>  www.asciiribbon.org  / \
> ___
> 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

Re: [sqlite] request for feature: insert or update

[Patrik Nilsson]

The SQLite syntax "replace into" does almost what I want.

It also erases the other values of the same line, which I don't want.

On 06/27/2013 01:38 PM, Simon Slavin wrote:
> 
> On 27 Jun 2013, at 11:05am, Patrik Nilsson  wrote:
> 
>> A feature I'm missing is a syntax like with "insert or update".
>>
>> You define a table as:
>> "create table table_test (id as integer primary key, a as integer, b as
>> integer, c as integer)"
>>
>> Then you know that the "id" is unique and you only can have one row with
>> that integer.
>>
>> Then you can give the following statements:
>> insert or update into table_test (id,c) values (1,3)
>> insert or update into table_test (id,b) values (1,2)
>> insert or update into table_test (id,a) values (1,1)
>> insert or update into table_test (id,a) values (5,13)
>>
>> This result is the following set:
>> 1|1|2|3
>> 5|13||
> 
> I understand your desire for the combination 'INSERT OR UPDATE' but I think 
> there is too much danger of SQL not understanding whether the UPDATE should 
> be affecting a single row or multiple rows or one new row.  The analysis 
> stage for the instruction would have to figure out whether you were correctly 
> specifying the whole of the primary key of the row.  Given that there may be 
> no primary row specified in the table definition, or that the primary key may 
> be a compound key, and that you may be using different COLLATions, this 
> analysis would involve quite a few instructions to make sure it got 
> everything perfectly right, which means it would be slow.
> 
> For further discussion on this search for the conventional term for what you 
> asked for: UPSERT, and for the endless discussion of exactly what the MERGE 
> does in discussion fora about other implementations of SQL.  You will see 
> that even experienced SQL writers have a hard time explaining clearly what 
> MERGE does under different circumstances.
> 
>> Now I'm doing: "insert or ignore into table_test (id) values (1)" and
>> then issue an update statement. I think "insert or update" will increase
>> performance.
> 
> I think that’s the best way to clearly explain to SQLite what you want done.  
> I agree that it is two commands long, but it takes advantage of existing 
> SQLite statements and it’s impossible for someone reading your code to 
> misunderstand what you want to happen.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> .
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] request for feature: insert or update

[Patrik Nilsson]

On 06/27/2013 02:16 PM, Hick Gunter wrote:
>> -Ursprüngliche Nachricht- Von: Simon Slavin
>> [mailto:slav...@bigfraud.org] Betreff: Re: [sqlite] request for
>> feature: insert or update
>> 
>> 
>> On 27 Jun 2013, at 11:05am, Patrik Nilsson
>>  wrote:
>> 
>> 
>>> Now I'm doing: "insert or ignore into table_test (id) values (1)"
>>> and then issue an update statement. I think "insert or update"
>>> will increase performance.
>> 
>> I think that's the best way to clearly explain to SQLite what you
>> want done.  I agree that it is two commands long, but it takes
>> advantage of existing SQLite statements and >it's impossible for
>> someone reading your code to misunderstand what you want to
>> happen.
>> 
>> Simon.
> 
> The sequence "insert or ignore" followed by "update" is IMHO
> indicative of file-oriented, procedural thinking, where you have to
> locate the record first and update the desired fields second (or fail
> to find a record and create a new one). Implementing this 1:1 in SQL
> (which is set-oriented and rule-based) causes the standard path of
> execution (record is already present) to always perform the exception
> case insert.
> 
> That is why I proposed doing the standard path of execution (the
> update) first, and then checking for the exception condition (no rows
> processed) before doing the exception processing (insert with the
> given values).
> 

It can be implemented in this way, but writing the same statement two
times means it is error prone. I want to write the statement once and
verify it.

Otherwise you can end up with bugs everywhere.

> While the first method will reliably run with implicit (autocommit)
> or explicit transactions, the second requires either an explicit
> transaction or a loop. Consider:
> 
> 
> BEGIN; UPDATE; if (no rows) then INSERT; COMMIT;
> 
> --> the first thread to enter then transaction will do the INSERT -->
> all others do only the UPDATE
> 
> repeat { UPDATE; If (no rows) then INSERT; } until (no error);
> 
> --> If two threads happen to attempt the INSERT, one will fail -->
> The failing thread must retry the UPDATE
> 
> 
> 
> 
> 
> --
>
> 
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
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] request for feature: insert or update

[Hick Gunter]

It works without autoincrement too...

asql> create temp table test (id integer primary key, a integer, b integer, c 
integer);
asql> insert into test (a,b) values (1,2);
rows inserted
-
1
asql> select * from test;
id  a   b   c
--  --  --  --
1   1   2   NULL
asql> insert into test (a,b) values (1,2);
rows inserted
-
1
asql> select * from test;
id  a   b   c
--  --  --  --
1   1   2   NULL
2   1   2   NULL

-Ursprüngliche Nachricht-
Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com]
Gesendet: Donnerstag, 27. Juni 2013 13:55
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] request for feature: insert or update

You are right it won't fail if you define the table with autoincrement:

"create table table_test (id as integer primary key AUTOINCREMENT, a as 
integer, b as integer, c as integer)"

/Patrik

On 06/27/2013 01:45 PM, Hick Gunter wrote:
> Insert into table_test (a,b) values (1,2); inserts a new row with a default 
> id field (see SQLite documentation) and does not fail.
>
> Update table_test set a=1, b=2; modifies all rows of the table and does not 
> fail either.
>
> Why should "insert or update" fail???
>
> -Ursprüngliche Nachricht-
> Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com]
> Gesendet: Donnerstag, 27. Juni 2013 12:55
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] request for feature: insert or update
>
>>
>> Which circumstances are you thinking of that would cause the proposed
> "insert or update" to fail (other than those that would cause the update to 
> fail too)?
>
> It is a fail condition when the statement is ambiguous.
>
> create table table_test (id as integer primary key, a as integer, b as 
> integer, c as integer)"
>
> insert or update into table_test (id,c) values (1,3) (This is OK)
>
> insert or update into table_test (a,b) values (1,2) (It is not OK
> since you don't have a unique value to relate to.)
>
>
> On 06/27/2013 12:32 PM, Hick Gunter wrote:
>> You can check the number of rows modified by an UPDATE statement
>> using the sqlite3_changes() interface. (Using pragma count_changes is
>> deprecated!)
>>
>> Within a transaction, when you issue an
>>
>> UPDATE table_test SET = WHERE id = ;
>>
>> and sqlite3_changes() returns 0 then you need to
>>
>> INSERT INTO table_test (id,) VALUES (,);
>>
>> otherwise you are good to go.
>>
>> Which circumstances are you thinking of that would cause the proposed 
>> "insert or update" to fail (other than those that would cause the update to 
>> fail too)?
>>
>> -Ursprüngliche Nachricht-
>> Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com]
>> Gesendet: Donnerstag, 27. Juni 2013 12:05
>> An: General Discussion of SQLite Database
>> Betreff: [sqlite] request for feature: insert or update
>>
>> Hi All!
>>
>> A feature I'm missing is a syntax like with "insert or update".
>>
>> You define a table as:
>> "create table table_test (id as integer primary key, a as integer, b as 
>> integer, c as integer)"
>>
>> Then you know that the "id" is unique and you only can have one row with 
>> that integer.
>>
>> Then you can give the following statements:
>> insert or update into table_test (id,c) values (1,3) insert or update
>> into table_test (id,b) values (1,2) insert or update into table_test
>> (id,a) values (1,1) insert or update into table_test (id,a) values
>> (5,13)
>>
>> This result is the following set:
>> 1|1|2|3
>> 5|13||
>>
>> Now I'm doing: "insert or ignore into table_test (id) values (1)" and then 
>> issue an update statement. I think "insert or update" will increase 
>> performance.
>>
>> If the "insert or update" can't perform its operation, it can issue a 
>> SQLITE_AMBIGUOUS error.
>>
>> Best regards,
>> Patrik
>>
>> --
>> ASCII ribbon campaign ( )
>>  against HTML e-mail   X
>>  www.asciiribbon.org  / \
>> ___
>> 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 

Re: [sqlite] request for feature: insert or update

[Hick Gunter]

>-Ursprüngliche Nachricht-
>Von: Simon Slavin [mailto:slav...@bigfraud.org]
>Betreff: Re: [sqlite] request for feature: insert or update
>
>
>On 27 Jun 2013, at 11:05am, Patrik Nilsson  wrote:
>
>
>> Now I'm doing: "insert or ignore into table_test (id) values (1)" and
>> then issue an update statement. I think "insert or update" will
>> increase performance.
>
>I think that's the best way to clearly explain to SQLite what you want done.  
>I agree that it is two commands long, but it takes advantage of existing 
>SQLite statements and >it's impossible for someone reading your code to 
>misunderstand what you want to happen.
>
>Simon.

The sequence "insert or ignore" followed by "update" is IMHO indicative of 
file-oriented, procedural thinking, where you have to locate the record first 
and update the desired fields second (or fail to find a record and create a new 
one). Implementing this 1:1 in SQL (which is set-oriented and rule-based) 
causes the standard path of execution (record is already present) to always 
perform the exception case insert.

That is why I proposed doing the standard path of execution (the update) first, 
and then checking for the exception condition (no rows processed) before doing 
the exception processing (insert with the given values).

While the first method will reliably run with implicit (autocommit) or explicit 
transactions, the second requires either an explicit transaction or a loop. 
Consider:


BEGIN;
UPDATE;
if (no rows) then INSERT;
COMMIT;

--> the first thread to enter then transaction will do the INSERT
--> all others do only the UPDATE

repeat {
UPDATE;
If (no rows) then INSERT;
} until (no error);

--> If two threads happen to attempt the INSERT, one will fail
--> The failing thread must retry the UPDATE





--
 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] unicode() and char() functions does not exists

[Richard Hipp]

On Thu, Jun 27, 2013 at 4:19 AM, Stefano Zaglio
wrote:

> Hi,
> I'm tring this:
>
> select unicode('2') as a;
>
> and
>
> select char(50)
>
> but firefox.sqlite_manager (sqlite 3.7) and others say:"no such function:
> unicode".
>
> Where I'm wrong?
>


Those functions where added to SQLite version 3.7.16.

What version of SQLite are you using?  What does "SELECT sqlite_version()"
say?


>
>
> __**_
> 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

Re: [sqlite] Performance regression since 3.7.15

[Richard Hipp]

On Wed, Jun 26, 2013 at 8:23 PM, Elan Feingold  wrote:

> Hi,
>
> We've observed a severe performance regression in a query.
>
> We had a query Q1, which was running fast, on 3.7.15.2. We made a minor
> change to the query, which we'll call Q2.
>
> Q2 runs excruciatingly slowly in 3.7.15.2 (1m 28sec). We then observed
> that Q2 was fast in 3.7.14 (300ms), but slow in 3.7.15.2 and later (tested
> up until 3.7.17).
>

Have you tried it with the NGQP snapshot?

The NGQP was announced on this mailing list yesterday, but I see that the
OP only subscribed as of today.  Please see the original posting at
http://www.mail-archive.com/sqlite-users%40sqlite.org/msg77855.html


>
> The slow query Q2 can be found in a test program here ~
> http://www.plexapp.com/elan/test.c
>
> The database which shows the slow query in action is here ~
> http://www.plexapp.com/elan/com.plexapp.plugins.library.db-slow.zip
>
> The crux of the query which makes it pathologically slow in 3.7.15+
> appears to be this bit: "join metadata_item_settings on
> metadata_item_settings.guid=grandchild.guid and
> metadata_item_settings.account_id=1". If I take out the account_id bit,
> it's then fast on every version.
>
> Where is it spending all this time? I'm glad you asked:
> https://dl.dropbox.com/s/vnfv5n7m2vgn1p4/2013-06-26%20at%202.21.58%20PM.png
>
> Please let me know if you have any other questions!
>
> -elan
> ___
> 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] Performance regression since 3.7.15

[Elan Feingold]

Hi,

We've observed a severe performance regression in a query. 

We had a query Q1, which was running fast, on 3.7.15.2. We made a minor change 
to the query, which we'll call Q2.

Q2 runs excruciatingly slowly in 3.7.15.2 (1m 28sec). We then observed that Q2 
was fast in 3.7.14 (300ms), but slow in 3.7.15.2 and later (tested up until 
3.7.17).

The slow query Q2 can be found in a test program here ~ 
http://www.plexapp.com/elan/test.c

The database which shows the slow query in action is here ~ 
http://www.plexapp.com/elan/com.plexapp.plugins.library.db-slow.zip

The crux of the query which makes it pathologically slow in 3.7.15+ appears to 
be this bit: "join metadata_item_settings on 
metadata_item_settings.guid=grandchild.guid and 
metadata_item_settings.account_id=1". If I take out the account_id bit, 
it's then fast on every version.

Where is it spending all this time? I'm glad you asked: 
https://dl.dropbox.com/s/vnfv5n7m2vgn1p4/2013-06-26%20at%202.21.58%20PM.png

Please let me know if you have any other questions!

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

[sqlite] unicode() and char() functions does not exists

[Stefano Zaglio]

Hi,
I'm tring this:

select unicode('2') as a;

and

select char(50)

but firefox.sqlite_manager (sqlite 3.7) and others say:"no such 
function: unicode".


Where I'm wrong?


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

Re: [sqlite] request for feature: insert or update

[Patrik Nilsson]

You are right it won't fail if you define the table with autoincrement:

"create table table_test (id as integer primary key AUTOINCREMENT, a as
integer, b as integer, c as integer)"

/Patrik

On 06/27/2013 01:45 PM, Hick Gunter wrote:
> Insert into table_test (a,b) values (1,2); inserts a new row with a default 
> id field (see SQLite documentation) and does not fail.
> 
> Update table_test set a=1, b=2; modifies all rows of the table and does not 
> fail either.
> 
> Why should "insert or update" fail???
> 
> -Ursprüngliche Nachricht-
> Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com]
> Gesendet: Donnerstag, 27. Juni 2013 12:55
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] request for feature: insert or update
> 
>>
>> Which circumstances are you thinking of that would cause the proposed
> "insert or update" to fail (other than those that would cause the update to 
> fail too)?
> 
> It is a fail condition when the statement is ambiguous.
> 
> create table table_test (id as integer primary key, a as integer, b as 
> integer, c as integer)"
> 
> insert or update into table_test (id,c) values (1,3) (This is OK)
> 
> insert or update into table_test (a,b) values (1,2) (It is not OK since you 
> don't have a unique value to relate to.)
> 
> 
> On 06/27/2013 12:32 PM, Hick Gunter wrote:
>> You can check the number of rows modified by an UPDATE statement using
>> the sqlite3_changes() interface. (Using pragma count_changes is
>> deprecated!)
>>
>> Within a transaction, when you issue an
>>
>> UPDATE table_test SET = WHERE id = ;
>>
>> and sqlite3_changes() returns 0 then you need to
>>
>> INSERT INTO table_test (id,) VALUES (,);
>>
>> otherwise you are good to go.
>>
>> Which circumstances are you thinking of that would cause the proposed 
>> "insert or update" to fail (other than those that would cause the update to 
>> fail too)?
>>
>> -Ursprüngliche Nachricht-
>> Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com]
>> Gesendet: Donnerstag, 27. Juni 2013 12:05
>> An: General Discussion of SQLite Database
>> Betreff: [sqlite] request for feature: insert or update
>>
>> Hi All!
>>
>> A feature I'm missing is a syntax like with "insert or update".
>>
>> You define a table as:
>> "create table table_test (id as integer primary key, a as integer, b as 
>> integer, c as integer)"
>>
>> Then you know that the "id" is unique and you only can have one row with 
>> that integer.
>>
>> Then you can give the following statements:
>> insert or update into table_test (id,c) values (1,3) insert or update
>> into table_test (id,b) values (1,2) insert or update into table_test
>> (id,a) values (1,1) insert or update into table_test (id,a) values
>> (5,13)
>>
>> This result is the following set:
>> 1|1|2|3
>> 5|13||
>>
>> Now I'm doing: "insert or ignore into table_test (id) values (1)" and then 
>> issue an update statement. I think "insert or update" will increase 
>> performance.
>>
>> If the "insert or update" can't perform its operation, it can issue a 
>> SQLITE_AMBIGUOUS error.
>>
>> Best regards,
>> Patrik
>>
>> --
>> ASCII ribbon campaign ( )
>>  against HTML e-mail   X
>>  www.asciiribbon.org  / \
>> ___
>> 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
>>
> 
> --
> ASCII ribbon campaign ( )
>  against HTML e-mail   X
>  www.asciiribbon.org  / \
> ___
> 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 

Re: [sqlite] request for feature: insert or update

[Hick Gunter]

Insert into table_test (a,b) values (1,2); inserts a new row with a default id 
field (see SQLite documentation) and does not fail.

Update table_test set a=1, b=2; modifies all rows of the table and does not 
fail either.

Why should "insert or update" fail???

-Ursprüngliche Nachricht-
Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com]
Gesendet: Donnerstag, 27. Juni 2013 12:55
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] request for feature: insert or update

>
> Which circumstances are you thinking of that would cause the proposed
"insert or update" to fail (other than those that would cause the update to 
fail too)?

It is a fail condition when the statement is ambiguous.

create table table_test (id as integer primary key, a as integer, b as integer, 
c as integer)"

insert or update into table_test (id,c) values (1,3) (This is OK)

insert or update into table_test (a,b) values (1,2) (It is not OK since you 
don't have a unique value to relate to.)


On 06/27/2013 12:32 PM, Hick Gunter wrote:
> You can check the number of rows modified by an UPDATE statement using
> the sqlite3_changes() interface. (Using pragma count_changes is
> deprecated!)
>
> Within a transaction, when you issue an
>
> UPDATE table_test SET = WHERE id = ;
>
> and sqlite3_changes() returns 0 then you need to
>
> INSERT INTO table_test (id,) VALUES (,);
>
> otherwise you are good to go.
>
> Which circumstances are you thinking of that would cause the proposed "insert 
> or update" to fail (other than those that would cause the update to fail too)?
>
> -Ursprüngliche Nachricht-
> Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com]
> Gesendet: Donnerstag, 27. Juni 2013 12:05
> An: General Discussion of SQLite Database
> Betreff: [sqlite] request for feature: insert or update
>
> Hi All!
>
> A feature I'm missing is a syntax like with "insert or update".
>
> You define a table as:
> "create table table_test (id as integer primary key, a as integer, b as 
> integer, c as integer)"
>
> Then you know that the "id" is unique and you only can have one row with that 
> integer.
>
> Then you can give the following statements:
> insert or update into table_test (id,c) values (1,3) insert or update
> into table_test (id,b) values (1,2) insert or update into table_test
> (id,a) values (1,1) insert or update into table_test (id,a) values
> (5,13)
>
> This result is the following set:
> 1|1|2|3
> 5|13||
>
> Now I'm doing: "insert or ignore into table_test (id) values (1)" and then 
> issue an update statement. I think "insert or update" will increase 
> performance.
>
> If the "insert or update" can't perform its operation, it can issue a 
> SQLITE_AMBIGUOUS error.
>
> Best regards,
> Patrik
>
> --
> ASCII ribbon campaign ( )
>  against HTML e-mail   X
>  www.asciiribbon.org  / \
> ___
> 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
>

--
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
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] request for feature: insert or update

[Simon Slavin]

On 27 Jun 2013, at 11:05am, Patrik Nilsson  wrote:

> A feature I'm missing is a syntax like with "insert or update".
> 
> You define a table as:
> "create table table_test (id as integer primary key, a as integer, b as
> integer, c as integer)"
> 
> Then you know that the "id" is unique and you only can have one row with
> that integer.
> 
> Then you can give the following statements:
> insert or update into table_test (id,c) values (1,3)
> insert or update into table_test (id,b) values (1,2)
> insert or update into table_test (id,a) values (1,1)
> insert or update into table_test (id,a) values (5,13)
> 
> This result is the following set:
> 1|1|2|3
> 5|13||

I understand your desire for the combination 'INSERT OR UPDATE' but I think 
there is too much danger of SQL not understanding whether the UPDATE should be 
affecting a single row or multiple rows or one new row.  The analysis stage for 
the instruction would have to figure out whether you were correctly specifying 
the whole of the primary key of the row.  Given that there may be no primary 
row specified in the table definition, or that the primary key may be a 
compound key, and that you may be using different COLLATions, this analysis 
would involve quite a few instructions to make sure it got everything perfectly 
right, which means it would be slow.

For further discussion on this search for the conventional term for what you 
asked for: UPSERT, and for the endless discussion of exactly what the MERGE 
does in discussion fora about other implementations of SQL.  You will see that 
even experienced SQL writers have a hard time explaining clearly what MERGE 
does under different circumstances.

> Now I'm doing: "insert or ignore into table_test (id) values (1)" and
> then issue an update statement. I think "insert or update" will increase
> performance.

I think that’s the best way to clearly explain to SQLite what you want done.  I 
agree that it is two commands long, but it takes advantage of existing SQLite 
statements and it’s impossible for someone reading your code to misunderstand 
what you want to happen.

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

Re: [sqlite] The next-generation query planner

[Richard Hipp]

On Thu, Jun 27, 2013 at 2:34 AM, Hick Gunter  wrote:

> Two questions:
>
> Does the NGQP change the way virtual table methods (specfically
> xBestIndex) are called? I have been using virtual table virtual fields
> (named __use_index_) to make the xBestIndex method return large costs
> when not using the suggested index.
>

I'm not sure what you mean here...

The virtual table API is unchanged.  The NGQP calls the xBestIndex method
as before, but it calls xBestIndex a different number of times and in a
different order.  This should not make any difference to the virtual table
implementation, though.

The NGQP is more sensitive to having proper costs.  I had to fix bugs in
the cost computations of some of the existing virtual tables.  The legacy
query planner is tolerant of those bugs, while the NGQP is not.


>
> Does the new draft sqlite3_stmt_status interface tally virtual table
> method calls (i.e. xNext)?
>

No, I don't think it does.  the SQLITE_STMTSTATUS_FULLSCAN_STEP counter is
only incremented for OP_Next or OP_Prev operations that are part of a full
table scan.  But SQLite has no way of knowing if the underlying virtual
table is doing a full table scan or not, so it does not increment the
counter on OP_VNext operations (which invoke xNext).




>
> -Ursprüngliche Nachricht-
> Von: Richard Hipp [mailto:d...@sqlite.org]
> Gesendet: Mittwoch, 26. Juni 2013 16:09
> An: General Discussion of SQLite Database
> Betreff: [sqlite] The next-generation query planner
>
> 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
>
>
> --
>  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
>



-- 
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] request for feature: insert or update

[RSmith]

>I'll add a vote to this request.
>It's not as if it is a hard thing to do, I continuously run update algorithms 
something like this:

Correction - re-reading my statement it sounded very wrong, please allow me to 
rephrase:

I'll add a vote to this request.
It's not as if it is difficult for us to do right now -  I achieve this in normal SQL simply by running update algorithms something 
like the following:

...
etc.

Thanks


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

Re: [sqlite] request for feature: insert or update

[RSmith]

I'll add a vote to this request.
It's not as if it is a hard thing to do, I continuously run update algorithms 
something like this:

Query "SELECT Count() FROM t WHERE ID=XXX;" --> r;
if (r>0) Execute "UPDATE t SET v=YYY WHERE ID=XXX;" else Execute "INSERT INTO t 
(XXX,YYY);"

(I obviously use code a  bit more efficient, but you get the idea).

This works quite great, but I cant help think the SQLite engine would do a much much more efficient job of this internally in a 
single query with an SQL conflict clause as Patrik suggested (or something else with similar effect). It may also be that I am 
simply ignorant of an alread-existing way to do this efficiently, in which case please inform me - thanks!



On 2013/06/27 12:05, Patrik Nilsson wrote:

Hi All!

A feature I'm missing is a syntax like with "insert or update".

You define a table as:
"create table table_test (id as integer primary key, a as integer, b as
integer, c as integer)"

Then you know that the "id" is unique and you only can have one row with
that integer.

Then you can give the following statements:
insert or update into table_test (id,c) values (1,3)
insert or update into table_test (id,b) values (1,2)
insert or update into table_test (id,a) values (1,1)
insert or update into table_test (id,a) values (5,13)

This result is the following set:
1|1|2|3
5|13||

Now I'm doing: "insert or ignore into table_test (id) values (1)" and
then issue an update statement. I think "insert or update" will increase
performance.

If the "insert or update" can't perform its operation, it can issue a
SQLITE_AMBIGUOUS error.

Best regards,
Patrik



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

Re: [sqlite] request for feature: insert or update

[Patrik Nilsson]

>
> Which circumstances are you thinking of that would cause the proposed
"insert or update" to fail (other than those that would cause the update
to fail too)?

It is a fail condition when the statement is ambiguous.

create table table_test (id as integer primary key, a as integer, b as
integer, c as integer)"

insert or update into table_test (id,c) values (1,3)
(This is OK)

insert or update into table_test (a,b) values (1,2)
(It is not OK since you don't have a unique value to relate to.)


On 06/27/2013 12:32 PM, Hick Gunter wrote:
> You can check the number of rows modified by an UPDATE statement using the 
> sqlite3_changes() interface. (Using pragma count_changes is deprecated!)
> 
> Within a transaction, when you issue an
> 
> UPDATE table_test SET = WHERE id = ;
> 
> and sqlite3_changes() returns 0 then you need to
> 
> INSERT INTO table_test (id,) VALUES (,);
> 
> otherwise you are good to go.
> 
> Which circumstances are you thinking of that would cause the proposed "insert 
> or update" to fail (other than those that would cause the update to fail too)?
> 
> -Ursprüngliche Nachricht-
> Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com]
> Gesendet: Donnerstag, 27. Juni 2013 12:05
> An: General Discussion of SQLite Database
> Betreff: [sqlite] request for feature: insert or update
> 
> Hi All!
> 
> A feature I'm missing is a syntax like with "insert or update".
> 
> You define a table as:
> "create table table_test (id as integer primary key, a as integer, b as 
> integer, c as integer)"
> 
> Then you know that the "id" is unique and you only can have one row with that 
> integer.
> 
> Then you can give the following statements:
> insert or update into table_test (id,c) values (1,3) insert or update into 
> table_test (id,b) values (1,2) insert or update into table_test (id,a) values 
> (1,1) insert or update into table_test (id,a) values (5,13)
> 
> This result is the following set:
> 1|1|2|3
> 5|13||
> 
> Now I'm doing: "insert or ignore into table_test (id) values (1)" and then 
> issue an update statement. I think "insert or update" will increase 
> performance.
> 
> If the "insert or update" can't perform its operation, it can issue a 
> SQLITE_AMBIGUOUS error.
> 
> Best regards,
> Patrik
> 
> --
> ASCII ribbon campaign ( )
>  against HTML e-mail   X
>  www.asciiribbon.org  / \
> ___
> 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
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] request for feature: insert or update

[Hick Gunter]

You can check the number of rows modified by an UPDATE statement using the 
sqlite3_changes() interface. (Using pragma count_changes is deprecated!)

Within a transaction, when you issue an

UPDATE table_test SET = WHERE id = ;

and sqlite3_changes() returns 0 then you need to

INSERT INTO table_test (id,) VALUES (,);

otherwise you are good to go.

Which circumstances are you thinking of that would cause the proposed "insert 
or update" to fail (other than those that would cause the update to fail too)?

-Ursprüngliche Nachricht-
Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com]
Gesendet: Donnerstag, 27. Juni 2013 12:05
An: General Discussion of SQLite Database
Betreff: [sqlite] request for feature: insert or update

Hi All!

A feature I'm missing is a syntax like with "insert or update".

You define a table as:
"create table table_test (id as integer primary key, a as integer, b as 
integer, c as integer)"

Then you know that the "id" is unique and you only can have one row with that 
integer.

Then you can give the following statements:
insert or update into table_test (id,c) values (1,3) insert or update into 
table_test (id,b) values (1,2) insert or update into table_test (id,a) values 
(1,1) insert or update into table_test (id,a) values (5,13)

This result is the following set:
1|1|2|3
5|13||

Now I'm doing: "insert or ignore into table_test (id) values (1)" and then 
issue an update statement. I think "insert or update" will increase performance.

If the "insert or update" can't perform its operation, it can issue a 
SQLITE_AMBIGUOUS error.

Best regards,
Patrik

--
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
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

[sqlite] request for feature: insert or update

[Patrik Nilsson]

Hi All!

A feature I'm missing is a syntax like with "insert or update".

You define a table as:
"create table table_test (id as integer primary key, a as integer, b as
integer, c as integer)"

Then you know that the "id" is unique and you only can have one row with
that integer.

Then you can give the following statements:
insert or update into table_test (id,c) values (1,3)
insert or update into table_test (id,b) values (1,2)
insert or update into table_test (id,a) values (1,1)
insert or update into table_test (id,a) values (5,13)

This result is the following set:
1|1|2|3
5|13||

Now I'm doing: "insert or ignore into table_test (id) values (1)" and
then issue an update statement. I think "insert or update" will increase
performance.

If the "insert or update" can't perform its operation, it can issue a
SQLITE_AMBIGUOUS error.

Best regards,
Patrik

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GUI for SQLite

[Ben]

That first link is *well* out of date and the second only compares three 
editors. I don't believe there is a comprehensive comparison anywhere right now.

- Ben


On 26 Jun 2013, at 17:46, "Rose, John B"  wrote:

> 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

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