Re: [sqlite] Groups and members

On Wed, Nov 6, 2013 at 12:55 PM, Ulrich Goebel wrote: > Is it a matter of a "simple" SELECTs with joined tables? Have I to think > about subqueries? Or even Compund SELECTs (UNION, INTERSECT)? > > It would be great to get some hints! Sounds like a job for

Re: [sqlite] Feature Request: Binding Arrays

On Thu, Oct 31, 2013 at 2:45 AM, Olaf Schmidt <n...@vbrichclient.com> wrote: > Am 29.10.2013 13:19, schrieb Dominique Devienne: > >> [...] >> First off, when you use functions like this, you basically rule >> out index use, even if some_column is indexed. That's not

Re: [sqlite] Feature Request: Binding Arrays

On Tue, Oct 29, 2013 at 2:06 AM, Olaf Schmidt <n...@vbrichclient.com> wrote: > Am 16.10.2013 10:40, schrieb Dominique Devienne: > If I somehow missed a better work-around to this lack of array-binding, I'm > >> also interested of course, but obviously I'd prefer real array

Re: [sqlite] compiling the non-AMALGAMATION on visual studio

On Mon, Oct 28, 2013 at 10:38 PM, David Clark wrote: > I am trying to compile the source tree on visual studio with makefile.msc > and I get the following: > [...] > Why am I doing this: > I have a database lock error coming from a update query and I am not sure > why. So

Re: [sqlite] Feature Request: Binding Arrays

On Wed, Oct 16, 2013 at 1:28 PM, Richard Hipp wrote: > Please see http://www.sqlite.org/src/artifact/2ece66438?ln=13-75 Thanks. Being familiar with vtables, I had imagined as much, even though stopped short of doing it in practice. This takes care of hiding the DML statements

Re: [sqlite] Feature Request: Binding Arrays

On Wed, Oct 16, 2013 at 12:03 PM, Clemens Ladisch <clem...@ladisch.de>wrote: > Dominique Devienne wrote: > > select * from some_table where some_column in (...) > > > > 2) In other places we synthesize the query text by splicing list.join(", > ") > &g

[sqlite] Feature Request: Binding Arrays

We have an SQLite virtual-table heavy application with a lot of the GUI driven by SQL queries, and often times we have queries of the form select * from some_table where some_column in (...) where ... is coming from prior selections in the GUI, or filtering, etc... 1) In some places, we create

Re: [sqlite] SQLite3 3.7.17 => using column names with character '@'.

On Tue, Sep 24, 2013 at 9:39 PM, Richard Hipp wrote: > On Tue, Sep 24, 2013 at 2:02 PM, Petite Abeille >wrote: > > On Sep 24, 2013, at 7:59 PM, Dan Kennedy wrote: > > > A double quoted string is treated as a column name if

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

On Fri, Sep 6, 2013 at 11:44 PM, Dominique Devienne <ddevie...@gmail.com>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

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] 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] 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 <ddevie...@gmail.com> wrote: > > >> select Distance( constGeom, geometry ) from table, (select > >> GeomFromText('Point(13.457

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

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 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] Table name length in sqlite affects performance. Why?

On Wed, Sep 4, 2013 at 2:43 AM, Jared Albers wrote: > On my machine, when using relatively short table names like > `TABLE_{table #}`, creation of a database with 10,000 tables takes > approximately 14 seconds. These table names vary from 7 to a max of 11 > characters. >

Re: [sqlite] name resolutionn in GROUP BY

On Wed, Aug 14, 2013 at 8:42 PM, Dominique Devienne <ddevie...@gmail.com>wrote: > On Wed, Aug 14, 2013 at 8:03 PM, Richard Hipp <d...@sqlite.org> wrote: > >> On Wed, Aug 14, 2013 at 1:59 PM, Dominique Devienne <ddevie...@gmail.com >> >wrote: >> >

Re: [sqlite] name resolutionn in GROUP BY

On Wed, Aug 14, 2013 at 8:03 PM, Richard Hipp <d...@sqlite.org> wrote: > On Wed, Aug 14, 2013 at 1:59 PM, Dominique Devienne <ddevie...@gmail.com > >wrote: > > Not authoritative of course, but Oracle seems to agree with the > previous behavior. --DD > > Dominique,

Re: [sqlite] name resolutionn in GROUP BY

On Wed, Aug 14, 2013 at 6:59 PM, Richard Hipp wrote: > On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn > wrote: > > > create table test(name); > > insert into test values (NULL); > > insert into test values ('abc'); > > > > select count(), > >

Re: [sqlite] What can be deduced from integrity check

On Mon, Aug 12, 2013 at 1:17 PM, Jan Slodicka wrote: > Has anybody any clue what can go wrong? I can add more details if useful or > even send corrupted DB. (Just will have to convert it to plaintext, > hopefully this does not create other problems. > One usually gets a link to

Re: [sqlite] How to "upgrade" a DB without write access to its file, or to the direction it's in?

On Wed, Aug 7, 2013 at 6:36 PM, Richard Hipp <d...@sqlite.org> wrote: > On Wed, Aug 7, 2013 at 12:15 PM, Dominique Devienne <ddevie...@gmail.com > >wrote: > > > On Wed, Aug 7, 2013 at 2:45 PM, Clemens Ladisch <clem...@ladisch.de> > > wrote: > > &

Re: [sqlite] How to "upgrade" a DB without write access to its file, or to the direction it's in?

On Wed, Aug 7, 2013 at 2:45 PM, Clemens Ladisch <clem...@ladisch.de> wrote: > Dominique Devienne wrote: > > We can of course copy the db file somewhere else with r/w access, or copy > > the DB into an in-memory DB (for each table, create table memdb.foo as > > select *

[sqlite] How to "upgrade" a DB without write access to its file, or to the direction it's in?

We use SQLite for persistence of our project meta-data (the data itself is still in a proprietary binary format). For now, on project load, the meta-data DB file is read in full into memory (C++ objects) then closed, and re-written from scratch on save or save-as, instead of trying to update it in

Re: [sqlite] Change of behavior dealing with invalid views

On Wed, Jun 5, 2013 at 12:02 PM, Dominique Devienne <ddevie...@gmail.com>wrote: > But that brings up the question about why the create view itself does not > fail? > > C:\Users\DDevienne>sqlite3 > SQLite version 3.7.15.2 2013-01-09 11:53:05 > Enter ".help" fo

[sqlite] Change of behavior dealing with invalid views

We had a latent bug about an invalid view hiding in our code, which was recently revealed when we upgraded SQLite to 3.7.16.2 (I test with 1.15.2 below, but that's the same). I guess that's good, thanks Richard :) But that brings up the question about why the create view itself does not fail?

Re: [sqlite] Max of 63 columns for a covering index to work?

On Thu, May 23, 2013 at 3:02 PM, Marc L. Allen wrote: > I'm not familiar with that. It's a "view" where Oracle actually stores > the view data as a physical table? And updates these tables as the main > table updates? Pretty much. And the query optimizer is aware

Re: [sqlite] Max of 63 columns for a covering index to work?

On Wed, May 22, 2013 at 9:11 PM, Marc L. Allen wrote: > [...]. It makes me think you might be better off using triggers to > maintain separate tables with covered data instead of indexes. [...]. > This sounds like Oracle's materialized views to me, which come in

Re: [sqlite] Help speed up query

On Tue, Apr 16, 2013 at 8:52 PM, wrote: > Simon and everyone who has thrown ideas into the mix, > I very much appreciate the effort that you folks have put into this! > If nothing else, I am learning from this exorcise. > At this point, sounds like the only way to possibly

Re: [sqlite] Help speed up query

On Mon, Apr 15, 2013 at 11:02 PM, Simon Slavin wrote: > On 15 Apr 2013, at 9:47pm, ven...@intouchmi.com wrote: > > My software was not locked up totally. It did finally come back. For > some reason executing analyze slow other queries way down. > > I am surprised by

Re: [sqlite] Help speed up query

On Mon, Apr 15, 2013 at 10:04 PM, Dominique Devienne <ddevie...@gmail.com>wrote: > On Mon, Apr 15, 2013 at 9:45 PM, <ven...@intouchmi.com> wrote: > >> Igor, >> >> As a follow up, I added the other two indexes with no real inprovement. >> Here is the new

Re: [sqlite] Help speed up query

On Mon, Apr 15, 2013 at 9:45 PM, wrote: > Igor, > > As a follow up, I added the other two indexes with no real inprovement. > Here is the new explain query plan: > 0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX > sqlite_autoindex_Object_Types_1 (Object_Type=?) (~1

Re: [sqlite] Getting row count for all the tables with a single SQL execute command

On Wed, Apr 10, 2013 at 5:56 PM, Igor Tandetnik wrote: > On 4/10/2013 11:47 AM, Bheemrao, Veeresh wrote: > >> I would like to know is there any way to get row count for all the tables >> in a database using single Execute command? >> > > select (select count(*) from table1),

Re: [sqlite] When is column type valid?

On Wed, Apr 10, 2013 at 5:16 PM, Igor Tandetnik wrote: > On 4/10/2013 10:06 AM, ven...@intouchmi.com wrote: > >> The call to sqlite3_col_decltype returns a char * >> Should I issue a sqlite3_free on it when I am done with the string? >> > > No. It gives you a pointer into an

Re: [sqlite] Bug or some misunderstanding?

On Wed, Apr 3, 2013 at 1:54 AM, Support wrote: > Hi > I have a database which has an entry "USE" in a table called airports with > column LocationID. > When I call > sqlite3 -line ~/Desktop/maps.db 'select * from airports where LocationID > like "USE%";' > I get correct

Re: [sqlite] Bug or some misunderstanding?

On Wed, Apr 3, 2013 at 1:54 AM, Support wrote: > sqlite3 -line ~/Desktop/maps.db 'select * from airports where LocationID > like "USE%";' > I get correct result. > > But when I call > sqlite3 -line ~/Desktop/maps.db 'select * from airports where > LocationID=="USE";' > SQL

Re: [sqlite] How to achieve fastest possible write performance for a strange and limited case

On Fri, Mar 29, 2013 at 8:17 PM, Jeff Archer < jsarc...@nanotronicsimaging.com> wrote: > I could write directly to a file myself. But I would need to provide some > minimal indexing, some amount of housekeeping to manage variable > sized BLOBS and some minimal synchronization so that multiple

Re: [sqlite] Timeline for full ALTER TABLE support?

On Wed, Mar 27, 2013 at 2:41 PM, Clemens Ladisch wrote: > Tim Gustafson wrote: > > That page also says that things are listed there in the order they're > > likely to be implemented in SQLite. Is there a timeline or road map > > anywhere that could inform us as to when we

Re: [sqlite] [sqlite-dev] SQLite version 3.7.16

On Tue, Mar 19, 2013 at 11:25 PM, Richard Hipp wrote: > > On Tue, Mar 19, 2013 at 6:18 PM, Petite Abeille wrote: > > On Mar 19, 2013, at 1:33 PM, D. Richard Hipp wrote: > > > http://www.sqlite.org/releaselog/3_7_16.html > > > > •

Re: [sqlite] Inserting a CURRENT_TIMESTAMP value into a BIG INT column seems to work

On Tue, Mar 19, 2013 at 12:51 PM, Philipp Kursawe wrote: > INSERT INTO test VALUES(CURRENT_TIMESTAMP) > > This goes through without an error and the physical db file then really > contains the current timestamp as a string. CURRENT_TIMESTAMP is of text type. Simon's

Re: [sqlite] Windows-Specific 2-c-files Amalgamation?

On Wed, Mar 13, 2013 at 10:59 PM, Random Coder <random.co...@gmail.com>wrote: > On Wed, Mar 13, 2013 at 1:14 PM, Dominique Devienne <ddevie...@gmail.com> > wrote: > > On Mon, Mar 11, 2013 at 5:21 PM, Bert Huijben <b...@qqmail.nl> wrote: > >> Since

Re: [sqlite] Order of ON DELETE CASCADE specified in SQLite?

On Wed, Mar 13, 2013 at 7:36 PM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 03/14/2013 12:09 AM, Dominique Devienne wrote: >> I stumbled upon >> http://stackoverflow.com/questions/60168/in-what-order-are-on-delete-cascade-constraints-processed >> >> Is th

Re: [sqlite] Windows-Specific 2-c-files Amalgamation?

On Mon, Mar 11, 2013 at 5:41 PM, Richard Hipp <d...@sqlite.org> wrote: > On Mon, Mar 11, 2013 at 12:17 PM, Dominique Devienne <ddevie...@gmail.com >wrote: > > Perhaps someone knows a trick or two to work-around that MS debugging issue? > See the http://www.sqlite

Re: [sqlite] Windows-Specific 2-c-files Amalgamation?

On Mon, Mar 11, 2013 at 5:21 PM, Bert Huijben <b...@qqmail.nl> wrote: > > -Original Message- > > From: ... On Behalf Of Dominique Devienne > > Except I can't debug it... 'Cause I'm primarily on Windows, which has a > > well-known limitation in the way it sto

Re: [sqlite] Non-English username in WinRT causes SQLiteException

On Wed, Mar 13, 2013 at 5:47 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 13 Mar 2013, at 3:39pm, Dominique Devienne <ddevie...@gmail.com> wrote: > > Indeed, it's whoever wrote that SQLite3.Open() .NET wrapper on top of the > > C-API that's responsible from d

[sqlite] Order of ON DELETE CASCADE specified in SQLite?

I stumbled upon http://stackoverflow.com/questions/60168/in-what-order-are-on-delete-cascade-constraints-processedand tried a simplified version in SQLite3, which appears to work fine (see below). Is this just happenstance in this case or will it work every-time there are similar "pseudo cycles"

Re: [sqlite] Non-English username in WinRT causes SQLiteException

On Wed, Mar 13, 2013 at 4:01 PM, Ercan Özdemir wrote: > However, does every developer have to write or change his code like this? > Indeed, it's whoever wrote that SQLite3.Open() .NET wrapper on top of the C-API that's responsible from doing proper conversion from that

Re: [sqlite] create view doesn't work

I'm no expert, but I think your problem comes from the excessive parentheses you are using. Below's a little experiment that reproduces your syntax, with its error, and provides an alternative simpler syntax that works. --DD C:\Users\DDevienne>sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05

[sqlite] Windows-Specific 2-c-files Amalgamation?

I understand the "deployment" ease and performance advantage of the amalgamation. I like it. Except I can't debug it... 'Cause I'm primarily on Windows, which has a well-known limitation in the way it stores Debug Information (uses 16-bit integer for the line number, so limited to ~64K lines,

Re: [sqlite] Full covering index without table

On Thu, Mar 7, 2013 at 11:50 AM, Richard Hipp wrote: > When a row is larger than a single page, the content spills into multiple > pages where the pages are strung together as a linked list. You have to > read each page in order to follow the linked list. That makes sense.

Re: [sqlite] Full covering index without table

On Thu, Mar 7, 2013 at 9:50 AM, Dominique Devienne <ddevie...@gmail.com> wrote: > On Tue, Mar 5, 2013 at 4:05 PM, Richard Hipp <d...@sqlite.org> wrote: > > Both implementations allow for reading just the prefix of the content blob > > in order to access earlier fields o

Re: [sqlite] Full covering index without table

On Tue, Mar 5, 2013 at 4:05 PM, Richard Hipp wrote: > Both implementations allow for reading just the prefix of the content blob > in order to access earlier fields of a table, leaving the tail of the blob > unread on disk. This information sparked a bit of a debate among my

Re: [sqlite] SQLite3

On Wed, Mar 6, 2013 at 3:34 PM, wrote: > > it would be wise if you use a tool like the SQLite Expert; that will > > generate statements that you can use on command line as well. > > And a couple of other free tools are: > SQLite Administrator - http://sqliteadmin.orbmu2k.de/ >

[sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

On Wed, Mar 6, 2013 at 3:29 PM, Ryan Johnson wrote: > Off topic, I'd love a way to request strong typing for a column (so that attempts to store 'abc' into an int column would fail). You can emulate it with a pair of before/update triggers (select raise(...) where

Re: [sqlite] Break on cumulative sum

On Tue, Mar 5, 2013 at 9:47 AM, Nico Williams wrote: > Well, under the hood table functions could use temp, gensym'ed virtual > tables for all I care. The point is I want table functions :) SQLite already has table functions of sort: PRAGMAs... They have their own issues

Re: [sqlite] Break on cumulative sum

On Tue, Mar 5, 2013 at 9:24 AM, Nico Williams wrote: > On Tue, Mar 5, 2013 at 1:19 AM, James K. Lowden > > 2. Aggregation. The engine passes the rows to be aggregated to the > > function. After the last row, it calls the function's "xFinal" > > function, which returns a

Re: [sqlite] Full covering index without table

On Tue, Mar 5, 2013 at 1:59 AM, Carlos Milon Silva wrote: > Also, You could not rebuild the index from the index, if necessary. I'm not sure what your point is Carlos. The table is the index, so there's no index to drop or rebuild. What Eleytherios is referring to, is known

Re: [sqlite] like query

On Wed, Feb 27, 2013 at 3:16 PM, Igor Tandetnik <i...@tandetnik.org> wrote: > On 2/27/2013 4:35 AM, Dominique Devienne wrote: > >> PS: Something else that should also be part of SQLite built-in is the >> optimization that col LIKE 'prefix%' queries should implicitly try t

Re: [sqlite] like query

On Wed, Feb 27, 2013 at 11:23 AM, Clemens Ladisch <clem...@ladisch.de>wrote: > Dominique Devienne wrote: > > My $0.02 is that such a chr() function could/should be built-in to > SQLite. > > Apparently, drh has a time machine: > http://www.sqlite.org/cgi/src/info/2

Re: [sqlite] like query

On Tue, Feb 26, 2013 at 2:31 PM, Clemens Ladisch wrote: > ... 'somedata/' || CAST(x'F48FBFBF' AS TEXT) > Great trick! But it hardly qualifies as user friendly though, no? For our app, I added a chr() SQL function that take an arbitrary number of integers and UTF-8 encodes

Re: [sqlite] Maximum Number of Open Simultaneous connections in the latest release of SQLite

On Wed, Feb 20, 2013 at 2:07 PM, Simon Slavin wrote: > On 20 Feb 2013, at 12:57pm, Frank Chang wrote: > > Good morning, Could someone please tell us the maximum number of open > > simultaneous connections in the latest release of SQLite? Thank you.

Re: [sqlite] Deletion slow?

On Tue, Feb 5, 2013 at 1:54 PM, Jason Gauthier wrote: > I am a fairly new user of sqlite, but not particularly new to SQL > principles. I am developing an application that will run on a low end system. Load the same data into another RDBMS you're familiar with, and see

Re: [sqlite] Lock Database temporarily.

On Tue, Jan 8, 2013 at 2:17 PM, Simon Slavin wrote: > On 7 Jan 2013, at 4:46pm, sants87 wrote: > >> Hi I am using Sqlite3 for my application and I require to lock the database >> from any third party modification. Accessing database is fine but I

Re: [sqlite] How to verify referential integrity of SQLite database

On Wed, Dec 12, 2012 at 10:01 PM, Jos Groot Lipman wrote: > When you enable foreign keys *after* you insert records, no guarantee is > given by SQLite that the foreign key constraints are fulfilled. This may not > be what most users would want or expect but it certainly not a

<    3   4   5   6   7   8