[sqlite] sqlite .import bug

*This works fine:* echo 1 | sqlite dbfile ".import '/dev/stdin' foo" But if you have any character (like a space, or newline), or sql statement in front of the '.import', sqlite gives: "Error near "." syntax error *Why it matters:* * * Because what I'm really trying to do is to import data

Re: [sqlite] UPDATE question

On Fri, 6 Sep 2013 17:45:59 -0400 (EDT) j.merr...@enlyton.com wrote: > I propose that you remove the unique index because SQLite does not > handle the update case the way you want. The correct general approach, in light of observed behavior, 1. begin IMMEDIATE transaction 2. select rows

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

Mayhaps the CROSS JOIN trick is your friend in this case, if you can be pretty sure of the correct direction of the join order. :) -David -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James K. Lowden Sent: Friday,

Re: [sqlite] UPDATE question

On Fri, 6 Sep 2013 11:07:27 -0400 Richard Hipp wrote: > The effect of early row updates might be visible in later row updates > if you contrive a sufficiently complex example. But you really have > to go out of your way to do that. sqlite> create table i ( i int primary key

Re: [sqlite] How to use aux_data effectively?

Ah, I see.  Yeah, that would be trickier.  You could save off the geometry blob and the GEOSPreparedGeometry object in a structure passed in to sqlite3_create_function and accessed via sqlite3_user_data and memcmp each geometry blob with the previous one to see if you need to regenerate the

Re: [sqlite] How to use aux_data effectively?

Peter, Thanks for the suggestion but that's not the part I'm trying to optimise at the moment. It could be useful to use auxdata there as well to avoid reparsing the text of course. What I would really like to achieve is that the GEOSPreparedGeometry can be cached to speed up the geometry

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

Two things: 1. The longer the table names, the longer it will take to compute the hash of each table name. 2. Because the entire schema must be reprocessed after each change, all the table names will be rehashed after each table has been created. Creating 10,000 tables will result in re-reading

Re: [sqlite] How to use aux_data effectively?

On 06 Sep 2013, at 17:51, Simon Davies wrote: >> Are there any other ways to kind of memoize the GeomFromText function >> (or the parameters to distance) besides aux_data? > > select Distance( constGeom, geometry ) from table, (select > GeomFromText('Point(13.457

Re: [sqlite] How to use aux_data effectively?

Actually, as it turns out, you can get the result you want by having the GeomFromText function use auxdata to store the geometry blob generated from the WKT string, since it's a constant.  Then all the GeomFromText has to do is to return the Geometry blob when sqlite3_get_auxdata returns

Re: [sqlite] Table name length in sqlite affects performance. Why?

On Fri, Sep 6, 2013 at 11:44 PM, Dominique Devienne wrote: > FWIW, I tried to take disk I/O out of the question by using a :memory DB, > and got some weird results of almost instantaneous runs after two initial > runs with similar run time as in the disk db case, and I saw

Re: [sqlite] How to use aux_data effectively?

On 6 September 2013 16:38, Pepijn Van Eeckhoudt wrote: > Hi, > > In the extension I'm developing (https://bitbucket.org/luciad/libgpkg) > I'm currently adding support for queries like: > select Distance( > GeomFromText('Point(13.457 3)'), > geometry > ) from table; >

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

On 6 Sep 2013, at 9:00pm, Harmen de Jong - CoachR Group B.V. wrote: > as dr. Hipp states in this thread, the tables are stored in a hash. Therefore > I would not expect a large performance decrease on large number of tables at > all, or am I missing something? The /names/

Re: [sqlite] UPDATE question

I propose that you remove the unique index because SQLite does not handle the update case the way you want. (I'd call that a bug, but sometimes "liteness" means that Dr Hipp can say "I'm not going to fix it" and we have to respect his decision.) Is there a reason other than "if you have a

Re: [sqlite] Table name length in sqlite affects performance. Why?

On Fri, Sep 6, 2013 at 11:15 PM, Jared Albers wrote: > I already grasp the reasoning for why the creation of tables can be > expensive and inefficient. What I don't understand is why the length > of a table name affects performance so greatly, while the length of > data

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

On 6 sep. 2013, at 20:09, "Kevin Benson" wrote: > Dr. Hipp does a little bit of explaining on this topic, generally, in his > replies on this thread: > > http://www.mail-archive.com/sqlite-users@sqlite.org/msg78602.html Thanks for pointing me to that thread, but as dr.

Re: [sqlite] Table name length in sqlite affects performance. Why?

On Fri, Sep 6, 2013 at 8:39 PM, Jared Albers wrote: > magnitude faster. The amount of data inserted into a row doesn't > affect performance nearly as much as the amount of data used to > describe a table name. This is the part I'm trying to understand. > Speculation:

Re: [sqlite] UPDATE question

On 6 Sep 2013, at 4:08pm, Simon Slavin wrote: > Right. As I posted in my message that I had tested, this isn't being done > correctly. A conflict isn't a conflict until the write, and the write > doesn't happen until the COMMIT. Therefore conflict testing needs to

Re: [sqlite] Table name length in sqlite affects performance. Why?

On 6 Sep 2013, at 7:39pm, Jared Albers wrote: > I really just wanted to understand the "why" in the table creation > performance. > > I can create a database where the table names are instead described as > rows, and the creation of the database is *much* faster. An

Re: [sqlite] UPDATE question

On 9/6/2013 1:05 PM, ibrahim wrote: Perhaps you should really try it out cause my suggestion is totally different from yours it uses a temporary table it works. create table t1 (pk integer primary key, name text, seq integer) ; You missed the whole "Name/Sequence pair of columns is defined as

Re: [sqlite] UPDATE question

On 06.09.2013 20:52, Igor Tandetnik wrote: On 9/6/2013 1:05 PM, ibrahim wrote: Perhaps you should really try it out cause my suggestion is totally different from yours it uses a temporary table it works. create table t1 (pk integer primary key, name text, seq integer) ; You missed the whole

Re: [sqlite] Table name length in sqlite affects performance. Why?

I really just wanted to understand the "why" in the table creation performance. I can create a database where the table names are instead described as rows, and the creation of the database is *much* faster. An order of magnitude faster. The amount of data inserted into a row doesn't affect

Re: [sqlite] UPDATE Question

Thanks Marc and all who responded. Marc's suggestion seems to come the closest to what I was looking for. I did try one other thing which I thought might solve the problem. Instead of defining the Name/Sequence as UNIQUE, I set up a UNIQUE index for those columns and defined both of them to be

Re: [sqlite] UPDATE question

On 06.09.2013 18:30, Dominique Devienne wrote: On Fri, Sep 6, 2013 at 5:41 PM, ibrahim wrote: You can let sqlite handle the creation of a temporary table by : update t1 set seq=seq+1 where pk in (select pk from t1 where seq >= 3 order by seq desc) ; afterwards

Re: [sqlite] UPDATE question

On 06.09.2013 18:30, Dominique Devienne wrote: On Fri, Sep 6, 2013 at 5:41 PM, ibrahim wrote: You can let sqlite handle the creation of a temporary table by : update t1 set seq=seq+1 where pk in (select pk from t1 where seq >= 3 order by seq desc) ; afterwards

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

On Fri, Sep 6, 2013 at 1:29 PM, Harmen de Jong - CoachR Group B.V. < har...@coachr.com> wrote: > On 6 sep. 2013, at 18:42, "Igor Tandetnik" wrote: > > If I recall correctly, query planner's behavior is worst-case quadratic > in the number of tables participating in the query.

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

On 6 sep. 2013, at 18:42, "Igor Tandetnik" wrote: > If I recall correctly, query planner's behavior is worst-case quadratic in > the number of tables participating in the query. This includes tables > mentioned directly, and also those pulled in indirectly via views,

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

On 9/6/2013 10:35 AM, Harmen de Jong - CoachR Group B.V. wrote: We ran into an issue where specific queries are geting non linearly slower when the total number of tables grows. If I recall correctly, query planner's behavior is worst-case quadratic in the number of tables participating in

Re: [sqlite] UPDATE question

On 05.09.2013 20:20, Peter Haworth wrote: I have a table with the following (simplified) structure PKeyINTEGER PRIMARY KEY NameTEXT Sequence INTEGER The Name/Sequence pair of columns is defined as UNIQUE I need to insert a new row into a point between two sequence numbers. For example, if

Re: [sqlite] How to use aux_data effectively?

On Fri, Sep 6, 2013 at 5:51 PM, Simon Davies wrote: > On 6 September 2013 16:38, Pepijn Van Eeckhoudt > wrote: > > Hi, > > > > In the extension I'm developing (https://bitbucket.org/luciad/libgpkg) > > I'm currently adding support for

Re: [sqlite] How to use aux_data effectively?

On 06 Sep 2013, at 17:58, Dominique Devienne wrote: >> select Distance( constGeom, geometry ) from table, (select >> GeomFromText('Point(13.457 3)') as constGeom ); > > Clever. Thanks for that. Simple and elegant. Thanks for the idea, I'll give it a try. > Or make your

Re: [sqlite] UPDATE question

On Fri, Sep 6, 2013 at 5:41 PM, ibrahim wrote: > You can let sqlite handle the creation of a temporary table by : > > update t1 set seq=seq+1 where pk in (select pk from t1 where seq >= 3 > order by seq desc) ; > > afterwards you can insert. Unless I'm

Re: [sqlite] How to use aux_data effectively?

On Fri, Sep 6, 2013 at 6:14 PM, Pepijn Van Eeckhoudt < pep...@vaneeckhoudt.net> wrote: > On 06 Sep 2013, at 17:58, Dominique Devienne wrote: > > >> select Distance( constGeom, geometry ) from table, (select > >> GeomFromText('Point(13.457 3)') as constGeom ); > > > > Clever.

Re: [sqlite] How to use aux_data effectively?

On Fri, Sep 6, 2013 at 5:40 PM, Richard Hipp wrote: > On Fri, Sep 6, 2013 at 11:38 AM, Pepijn Van Eeckhoudt < > pep...@vaneeckhoudt.net> wrote: > > > In order to speed up the distance function I was wondering if I could > > use aux_data to cache the parsed version of the first

[sqlite] Query preperation time does not scale linearly with growth of no. of tables

We ran into an issue where specific queries are geting non linearly slower when the total number of tables grows. Example 1 (not slow): Database has table A Database has 1,000 other tables with foreign key to table A Row is deleted from table A (no deletion of actual data in other

Re: [sqlite] How to use aux_data effectively?

On Fri, Sep 6, 2013 at 11:38 AM, Pepijn Van Eeckhoudt < pep...@vaneeckhoudt.net> wrote: > Hi, > > In the extension I'm developing (https://bitbucket.org/luciad/libgpkg) > I'm currently adding support for queries like: > select Distance( > GeomFromText('Point(13.457 3)'), > geometry > ) from

[sqlite] How to use aux_data effectively?

Hi, In the extension I'm developing (https://bitbucket.org/luciad/libgpkg) I'm currently adding support for queries like: select Distance( GeomFromText('Point(13.457 3)'), geometry ) from table; GeomFromText takes a string and outputs a geometry blob Distance takes two geometry blobs and

Re: [sqlite] UPDATE question

MySQL also uses this implementation. They acknowledge that it is not SQL compliant and that (I never thought of this), you cannot delete a record that has a foreign key link to itself. Postgres apparently has the ability to have deferred checking as of V9, but not before then. Please see:

Re: [sqlite] UPDATE question

On 09/06/2013 10:19 PM, Marc L. Allen wrote: MySQL also uses this implementation. They acknowledge that it is not SQL compliant and that (I never thought of this), you cannot delete a record that has a foreign key link to itself. Postgres apparently has the ability to have deferred checking

Re: [sqlite] UPDATE question

On 6 Sep 2013, at 1:50pm, Marc L. Allen wrote: > No one commented on my second thread (written after I actually understood the > problem!). > > But, I proposed a two update sequence to do it. > > UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >=

Re: [sqlite] UPDATE question

Nice, but that still requires extra work. 1) Determine if row is already in table. 2) Determine next lower value. 3) Split difference and insert. There's also the possibility that the higher level APP expects the new row to have a sequence number of 3. -Original Message- From:

Re: [sqlite] UPDATE question

On Fri, Sep 6, 2013 at 2:50 PM, Marc L. Allen wrote: > No one commented on my second thread (written after I actually understood > the problem!). > > But, I proposed a two update sequence to do it. > > UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= >

Re: [sqlite] UPDATE question

On Fri, Sep 6, 2013 at 11:00 AM, Dominique Devienne wrote: > Myself, if I'm "thinking in sets", all implementation details aside, the > UPDATE statement looks fine and correct, and I'd have expected SQLite to > support it. > > But I'm just waiting to read Dr. Hipp's own read

Re: [sqlite] UPDATE question

On Thu, Sep 5, 2013 at 9:20 PM, Peter Haworth wrote: > I have a table with the following (simplified) structure > > PKeyINTEGER PRIMARY KEY > NameTEXT > Sequence INTEGER > > The Name/Sequence pair of columns is defined as UNIQUE > > I need to insert a new row into a point between

Re: [sqlite] UNSIGNED BIG INT in SQLite 3.7.11.

Of course it does. But if the OP inserted his as yet unspecified large value using the sqlite3_bind_int() call, then the shell would render it as a negative value. He needs to use the sqlite3_bind_int64() interface instead... -Ursprüngliche Nachricht- Von: Richard Hipp

Re: [sqlite] UNSIGNED BIG INT in SQLite 3.7.11.

On Fri, Sep 6, 2013 at 10:00 AM, Hick Gunter wrote: > > My guess is that you have exceeded the range of a 32 bit integer > (0x7FFF) which will be rendered as a negative number by the SQLite > shell. > The shell works just fine with 64-bit signed integers: SQLite version

Re: [sqlite] UNSIGNED BIG INT in SQLite 3.7.11.

What is the size in bits of your UNSIGNED BIG INT? SQLite supports only SIGNED integers in 32 and 64 bits, so to properly store/retrieve UNSIGNED values you need to typecast. My guess is that you have exceeded the range of a 32 bit integer (0x7FFF) which will be rendered as a negative

Re: [sqlite] sqlite support not recognized on debian lighttpd

GoogleWell wrote: > I installed sqlite on my debian lighttpd (sqeeze) server. It is needed for > an application (business directory) used on a Joomla installation. This > particular component (SOBIPro) keeps telling me that sqlite is not > installed on my server. SQLite is an embedded database

Re: [sqlite] UPDATE question

On 9/6/2013 8:50 AM, Marc L. Allen wrote: But, I proposed a two update sequence to do it. UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= seq_to_insert AND Name = name_to_insert UPDATE table SET Sequence = -Sequence WHERE Sequence < 0 AND Name = name_to_insert I've used this

Re: [sqlite] UPDATE question

I don't think it's a bug. I don't believe there's any defined rule for how SQL should behave, is there? The updates are done serially not atomically. If the rows happen to be processed in reverse order, then no constraint is violated. In fact, if there was a way to define the order the

[sqlite] sqlite support not recognized on debian lighttpd

Hi everyone, Very grateful this list exists and hope that in a few months or years I will be able to contribute! but for now I am someone with questions :( because I am very very new at all this. I installed sqlite on my debian lighttpd (sqeeze) server. It is needed for an application (business

Re: [sqlite] UPDATE question

No one commented on my second thread (written after I actually understood the problem!). But, I proposed a two update sequence to do it. UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= seq_to_insert AND Name = name_to_insert UPDATE table SET Sequence = -Sequence WHERE Sequence

[sqlite] UNSIGNED BIG INT in SQLite 3.7.11.

Hello, I am using Android 4.2 with SQLite 3.7.11 database. I am not being able to insert UNSIGNED BIG INT data properly. It looks to me like an overflow, because when I read it out I get a negative number. That only happens on this device, I have another 2.3 Andriod device which runs older

Re: [sqlite] UPDATE question

On Fri, Sep 6, 2013 at 6:21 AM, James K. Lowden wrote: > Here's how Marc L. Allen's query should work (different DBMS): > > $ bsqldb < /tmp/sql >PKey Name Sequence > --- -- --- >

Re: [sqlite] UPDATE question

On Fri, Sep 6, 2013 at 6:21 AM, James K. Lowden wrote: > That's perfectly good SQL. SQLite is simply not executing the > update atomically. > > Anyone tempted to protest may be forgetting "atomic" means more than > "all or nothing". It also means the DBMS may execute

Re: [sqlite] Sqlite handler in trigger

Thanks for clarification. My understanding: Process creating notified function has to create trigger & do the other operation (Update, Insert and Delete) to get trigger logic execution. I understand it may not be in same sequence. Process can register function & create trigger, But trigger