Re: [sqlite] Checking if an "integer" column is set to NULL

On Mon, May 25, 2009 at 1:15 PM, chandan wrote: >    I have used sqlite3_bind_null() API to bind an integer column with > NULL. When I read the value of that integer column I get the value as 0 > (zero). Is there any way I can check if the column is set to NULL?

Re: [sqlite] Deleting database content : SQLite3 API

On Sat, May 30, 2009 at 1:20 PM, wrote: > Yes , I understand that. Infact I was doing that through a script during > system startup. I wanted to know whether SQLite provides any API to do the > same. No, and it doesn't provide any API for changing access permissions

Re: [sqlite] Db design question (so. like a tree)

On Tue, Jun 2, 2009 at 10:45 PM, Jan wrote: > Sounds good. I think I try that. Although updating is usually not > necessary (once you have a mother/father its usually difficult to get > rid of/update them .-) I read that there is problem with queries that go > deeper in

Re: [sqlite] sqlite programmed in C++

On Wed, Jun 3, 2009 at 8:59 AM, Sylvain Pointeau wrote: > I can perfectly understand the decision made few years ago,and the result is > splendid, I use SQLite every days. > > I am just wondering why not introducing C++? for better memory management > for example

Re: [sqlite] Installing SQLite

On Thu, Jul 23, 2009 at 9:03 PM, Rich Shepard wrote: >   Er, Dan, ... without the build step there's nothing to install. A well-formed Makefile should specify the default build target as a dependency of the install target. Hamish

Re: [sqlite] "Bad CPU type in executable"?

On Wed, Aug 5, 2009 at 5:50 PM, Jean-Denis Muys wrote: > You're top-posting, it's evil, the thread is becoming messy. You need to look up the word "evil" sometime. There are pros and cons to top-posting; to my mind, the most annoying thing about it is that it seems to draw

Re: [sqlite] "Bad CPU type in executable"?

On Wed, Aug 5, 2009 at 8:20 PM, Jean-Denis Muys wrote: > No I don't (even though I don't speak English natively). If you google > "top posting evil" you will realize that "evil" has been associated > with "top posting" for longer than I can remember. And Natalie Portman has

Re: [sqlite] "Bad CPU type in executable"?

On Fri, Aug 7, 2009 at 1:01 PM, Jean-Denis Muys wrote: > The point is made many times by many of those references found with > Google. > and the appeal to the web clearly invalidates the claim that I don't > know the meaning of evil, at least in this context. I'm sure I can

[sqlite] Bug / Feature: CREATE VIRTUAL TABLE IF NOT EXISTS

Hi, I was surprised to find the "IF NOT EXISTS" syntax missing from "CREATE VIRTUAL TABLE". I googled and found: http://www.sqlite.org/cvstrac/tktview?tn=2604 The workaround suggested on the tracker ("Couldn't you just do a 'drop table if exists' first?") does not make sense, as it ensures the

Re: [sqlite] Bug / Feature: CREATE VIRTUAL TABLE IF NOT EXISTS

On Mon, Feb 8, 2010 at 9:26 PM, Roger Binns wrote: > Virtual tables do not directly have any storage - they are just a row in > sqlite_master. > > The implementation may do something.  For example FTS3 creates 3 real tables > behind the scenes.  Virtual tables that map to

Re: [sqlite] Bug / Feature: CREATE VIRTUAL TABLE IF NOT EXISTS

On Tue, Feb 9, 2010 at 5:56 PM, Roger Binns <rog...@rogerbinns.com> wrote: > > Hamish Allan wrote: >> >> I'm sure there are ways you could work around the absence of >> "IF NOT EXISTS" in the simple "CREATE TABLE" case too. So why have th

Re: [sqlite] Interrupt first sqlite3_step in FTS3 query

On Fri, Feb 19, 2010 at 12:18 AM, Simon wrote: > However, it seems that some process (that can take several tens of seconds) > in the first sqlite3_step does not test for interrupt (resulting in > simultaneous uninterrupted concurrent threads...) Do you have a separate

Re: [sqlite] Interrupt first sqlite3_step in FTS3 query

On Sat, Feb 20, 2010 at 10:09 PM, Simon wrote: > I also can stop the process between any two calls to sqlite3_step, my issue > is with the first one that (to me) does not seem interruptible. > > I just tried adding a call to CHECK_FOR_INTERRUPT just after "for(pc=p->pc; >

Re: [sqlite] Reading the VDBE program

On Tue, Mar 23, 2010 at 1:02 PM, Navaneeth Sen B wrote: > My ultimate aim is not to program using SQLite. > I just want to know the internals of it? How it works? > How i can make a new wrapper around it so that my exsisting applications > can use it without huge

Re: [sqlite] How SQLite solves these issues

Hi, SQLite will outperform the DB you describe in every aspect. However, it doesn't store files, it stores data. If you need to query (meta)data from a particular file format, you'll have to extract it yourself. Best wishes, Hamish On Mon, Apr 12, 2010 at 10:58 AM, Navaneeth Sen B

Re: [sqlite] How SQLite solves these issues

On Mon, Apr 12, 2010 at 11:33 AM, Navaneeth Sen B wrote: > Thanks Hamish, > > But can you give me more clarity for the sentence in the quoted text. >> However, it doesn't store files, it stores data. If you need to query >> (meta)data from a particular file format,

[sqlite] Efficiency of concatenation on UPDATE

1) Is concatenation on UPDATE performed in-place? For example, with the following: CREATE TABLE example (content TEXT); INSERT INTO example(content) VALUES ('Hello, world!'); UPDATE example SET content = content || ' How are you?' WHERE rowid = 1; Is the old value copied out to memory before

Re: [sqlite] Efficiency of concatenation on UPDATE

On 5 February 2012 19:11, Roger Binns wrote: > The values for a row are stored sequentially.  Changing the size of a > value will at least require rewriting the row. Sure, but a row can be re-written by copying each byte to memory and back out again, or by copying the

Re: [sqlite] Efficiency of concatenation on UPDATE

On 5 February 2012 21:20, Roger Binns wrote: > SQLite doesn't work on rows - it works on pages.  A row will be contained > within one or more pages. FWIW, I inspected the source for OP_Concat and found that it can sometimes avoid a memcpy (but presumably not if there

Re: [sqlite] Efficiency of concatenation on UPDATE

On 6 February 2012 05:20, Dan Kennedy wrote: > I think when two doclists are merged both are loaded into memory. > Some types of queries load the entire doclist for a term into > memory too. Hmm, sounds like I definitely need to stick with rows representing document pages

[sqlite] FTS simple tokenizer

The docs for the simple tokenizer (http://www.sqlite.org/fts3.html#tokenizer) say: "A term is a contiguous sequence of eligible characters, where eligible characters are all alphanumeric characters, the "_" character, and all characters with UTF codepoints greater than or equal to 128." If I do:

Re: [sqlite] FTS simple tokenizer

Thanks Dan. Have just checked how to report bug, and apparently we already have :) Please excuse the brevity -- sent from my phone On 27 Feb 2012, at 07:06, Dan Kennedy <danielk1...@gmail.com> wrote: > On 02/27/2012 05:59 AM, Hamish Allan wrote: >> The docs for the simple tok

[sqlite] Determining end locations of phrase matches in FTS3/4

Hi, I can use the offsets() function to determine the start locations of phrase matches, but is there any straightforward way to determine the end locations? CREATE VIRTUAL TABLE test USING fts4(); INSERT INTO test VALUES ('i am what i am'); SELECT offsets(test) FROM test WHERE content MATCH

Re: [sqlite] Determining end locations of phrase matches in FTS3/4

On 27 February 2012 17:11, Hamish Allan <ham...@gmail.com> wrote: > > -- what I want to determine is the full range of the phrase match: (0, 14) Sorry, correction: I want the full range*s* of the phrase match: (0, 4) and (12, 4). H On 27 February 2012 17:11, Hamish Allan <h

[sqlite] Combining user-defined functions with aggregate functions

Short form question: Working: SELECT a, userfunc(systemfunc) FROM t; Working: SELECT a, sum(systemfunc) FROM t GROUP BY a; Not working: SELECT a, sum(userfunc(systemfunc)) FROM t GROUP BY a; Long form question: I have a user-defined C function called "hits", loosely based on the function

Re: [sqlite] Combining user-defined functions with aggregate functions

On 10 October 2012 16:07, Dan Kennedy wrote: > > On 10/10/2012 10:01 PM, Ryan Johnson wrote: >> >> On 10/10/2012 10:49 AM, Dan Kennedy wrote: >>> >>> The easiest workaround is probably to use a temp table to store the >>> unaggregated results of the FTS query. >> >> What

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

index on (b, a) > > The former returns all groups of c with the top one being the one row > returned by the latter. > >> -Original Message- >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> On Behalf Of Hamish Allan >> Sen

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

.b=x1.b AND x2.c=x1.c) > > This needs no grouping because the sub-query ensures it, unless a can have > duplicate values for any one c value. > > > > > On 2017/04/03 11:09 AM, R Smith wrote: >> >> >> >> On 2017/04/03 10:51 AM, Hamish Allan wrote: >

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

the query above, the value of the "b" > column in the output will be the value of the "b" column in the input row > that has the largest "c" value. There is still an ambiguity if two or more of > the input rows have the same minimum or maximum value or i

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

seless. > >> -Original Message- >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> On Behalf Of Hamish Allan >> Sent: Sunday, 2 April, 2017 17:28 >> To: sqlite-users@mailinglists.sqlite.org >> Subject: [sqlite] Indexing WHERE w

[sqlite] Indexing WHERE with GROUP BY and ORDER BY

Given a table: CREATE TABLE x (a INT, b INT, c TEXT, d TEXT); the query: SELECT d FROM x WHERE b = 1 GROUP BY c ORDER BY a; shows the following plan, without indexes: 0|0|0|SCAN TABLE x 0|0|0|USE TEMP B-TREE FOR GROUP BY 0|0|0|USE TEMP B-TREE FOR ORDER BY I can create an index to cover the

[sqlite] LIKE IN

Hi, Is it possible to achieve the effect of combining the LIKE and IN operators? So for instance if I have tables: CREATE TABLE names (name TEXT); INSERT INTO names VALUES ('Alexandra'); INSERT INTO names VALUES ('Rob'); CREATE TABLE matches (match TEXT); INSERT INTO matches VALUES ('Alex');

[sqlite] Mixed ASC and DESC in single column

Hi, Given a table like the following: CREATE TABLE IF NOT EXISTS Data (uuid TEXT, info TEXT); with data like: C94464EB|foo/x1 8A740A4C|foo/x2 FBC49814|bar/x1 F9B0921F|bar/y1 1914F587|bar/y2 E51EC596|baz/a1 549298B6|baz/a2 822DC1A8|foo/x2/a1 46F2854F|foo/x1/a1 47FE9DB1|bar/z1 841716A3|bar/y1/b1

Re: [sqlite] Mixed ASC and DESC in single column

FROM (SELECT identifier FROM Data WHERE info LIKE 'foo/%' ORDER BY info DESC) UNION SELECT * FROM (SELECT identifier FROM Data WHERE info LIKE 'baz/%' ORDER BY info ASC); but of course this approach no longer works... Thanks again, Hamish On Thu, 21 Nov 2019 at 14:02, Clemens Ladisch wrote

Re: [sqlite] Mixed ASC and DESC in single column

refix when 'baz' then suffix end) > from a > group by identifier > ) > select * from b order by baz, foo desc, baz; > > > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > >-

[sqlite] Intersecting multiple queries

Hi, I am building a list of UUIDs from multiple queries of the form: SELECT uuid FROM Data WHERE filter LIKE ? with a different bound parameter each time. In app-space code, I'm getting the results of these queries and intersecting them, so that the final list contains only UUIDs returned by

Re: [sqlite] Intersecting multiple queries

On Sat, 29 Feb 2020 at 00:45, Keith Medcalf wrote: > > In other words, why would one want to do: > > select * from data where uuid in (select uuid from data where twit == 1 > INTERSECT select uuid from data where twat == 1 INTERSECT select uuid from > data where lastname like 'cricket%'

Re: [sqlite] Intersecting multiple queries

Thanks Jens and everyone. I'll try the approach of compiling statements on the fly. Best wishes, Hamish On Sat, 29 Feb 2020 at 23:13, Jens Alfke wrote: > > > On Feb 28, 2020, at 11:49 PM, Hamish Allan wrote: > > > > Again, I may be making incorrect assumptions. >