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

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

Re: [sqlite] Performance regression since 3.7.15

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

Re: [sqlite] Performance regression since 3.7.15

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

Re: [sqlite] Performance regression since 3.7.15

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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,

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

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

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

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

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

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 8:30 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] sqlite3: .import command handles quotation

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

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 7:59 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] sqlite3: .import command handles quotation

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Re: [sqlite] Performance regression since 3.7.15

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

[sqlite] Performance regression since 3.7.15

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

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

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

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

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

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-

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

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

Re: [sqlite] The next-generation query planner

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

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

>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

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

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

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,

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

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

[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

Re: [sqlite] GUI for SQLite

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