[sqlite] Best page size and cache size in high memory environment

Hi All, My application uses temporary tables that can become quite big (>300MB), so it makes sense to me to change the page size and cache size with pragma statements. The docs say "The default suggested cache size is 2000 pages" and "The normal configuration for SQLite running on workstations

[sqlite] Unique index versus unique constraint

Hi All, When I create a table and specify a unique constraint, a unique index is automatically created. This index cannot be dropped, so the only way to get rid of the uniqueness is to recreate the table without the constraint. No problem. When I create a table without the unique constraint, I

Re: [sqlite] Unique index versus unique constraint

> > Automatic indexes have no "SQL" entry in the SQLite_Master table, so you > can use that to see if you can drop it. eg: > > select Name from SQLite_Master where type = 'index' and SQL is null > > will give you the name of all the automatic indexes, which can't be > dropped. > Thanks Tom, It

Re: [sqlite] sqlite3_bind_blob CHOPS off at first NULL char

Hi Lynton, What is the value of msg.num_bytes_in? Is it fsize? And what do you get when you SELECT Length(raw_stream_in) FROM test ? Regards, Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] :Re: sqlite3_bind_blob CHOPS off at first NULL char

On Sun, Apr 3, 2011 at 2:46 PM, Lynton Grice wrote: > char* from SQLite? You say that SELECT treats message as TEXT which is > fine, but then how can I get the FULL payload back into a char* so that > I can write it to a file? > > SELECT doesn't treat the BLOB as

Re: [sqlite] :Re: sqlite3_bind_blob CHOPS off at first NULL char

On Sun, Apr 3, 2011 at 3:15 PM, Lynton Grice wrote: > Thanks, issue solved with the following: > > len = sqlite3_column_bytes(stmt,2); > memcpy(msg->raw_stream_in, sqlite3_column_text(stmt, 2), len); > > sqlite3_column_blob is a better function to use.

[sqlite] minor documentation typo

Also in partialindex.html: The WHERE clause may *not* contains subqueries should be contain Very happy to have learned about partial indexes today! On Fri, Mar 25, 2016 at 1:32 AM, Wolfgang Enzinger wrote: > minor documentation typo here: > https://www.sqlite.org/partialindex.html > > 3.0

[sqlite] Performance comparison between SQLite and SQL Server?

The quality and helpfulness of this mailing list makes me wish for a SQHeavy...

[sqlite] Mixing text and numeric values in comparisons

Hi All, CREATE TABLE test (A TEXT); INSERT INTO test VALUES ('1'); SELECT * FROM test WHERE A=1; // returns 1 SELECT * FROM test WHERE A=1.0; // returns nothing How is the second comparison done? I realize that you can have double values that are too large to convert to int64, but as I

[sqlite] Mixing text and numeric values in comparisons

Hi Richard, Thank you for the quick reply. I cannot change the type of the column. The tables are user defined and need to be matched with other user defined tables. The values are always integers but can be in any type of column. So I have to detect integers in order to avoid the .0 How do

[sqlite] Mixing text and numeric values in comparisons

On Fri, Mar 4, 2016 at 4:23 PM, Igor Tandetnik wrote: > On 3/4/2016 9:15 AM, Paul van Helden wrote: > >> So I have to detect integers in order to avoid the .0 >> > > WHERE CAST(A as integer) = 1 > > Not quite going to work either. I should have said the value

[sqlite] Mixing text and numeric values in comparisons

Hi again, CREATE TABLE test (A TEXT); INSERT INTO test VALUES ('1'); SELECT * FROM test WHERE A=1; -- returns 1 SELECT * FROM test WHERE A=1.0; -- returns nothing because the RHS is converted to '1.0' This would not have been a problem if CAST(1.0 AS TEXT) produced '1' (like FloatToStr does in

[sqlite] TRUNCATE TABLE alias for DELETE FROM

Hi, TRUNCATE TABLE is now in the SQL:2008 standard. http://en.wikipedia.org/wiki/Truncate_(SQL) It would make portability easier if SQLite understood TRUNCATE TABLE to be the same as DELETE FROM without WHERE. Yes? No? Paul. ___ sqlite-users mailing

Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM

> > It would be possible to implement TRUNCATE TABLE on top of that, but > this would be only syntactic sugar. > ..or better portability. TRUNCATE TABLE works (since only a few years) nearly everywhere. So when writing portable applications it seems a bit silly to make an exception for SQLite if

Re: [sqlite] Is there a way to select a precision?

> > > What do you mean, select precision? The double value you pass to > sqlite3_bind_double() will be used as is. Are you saying you want to round > it first? Then go ahead and do that - I'm not sure what that has to do with > SQLite. > -- > It is an issue with SQLite because the values in

Re: [sqlite] Is there a way to select a precision?

> A delared type of NUMBER(10,2) has NUMERIC affinity, which means that > SQLite will attempt to store (string) values as integers first and floats > second before giving up and storing strings. > This has nothing to do with my reply and I understand how it works. > > You do realize that there

Re: [sqlite] SQLite and integer division 1/2=0

On Sun, May 12, 2013 at 1:54 PM, Michael Black wrote: > PRAGMA INTEGER_DIVISION would probably not have saved you this bug as you > would not have known to turn it on (default would have to be OFF for > backwards compatibility). I will use it on every connection I make in

Re: [sqlite] SQLite and integer division 1/2=0

Tim, Simon & Darren, if you read my whole OP you will see that I've discovered this: use REAL instead. My point is that the behaviour of a NUMERIC column is not intuitive and gives mixed results which wouldn't be a problem if the division operator could be modified. My suggestion cannot be too

Re: [sqlite] Possible bug in type conversion prior to comparison

> I should have asked you for (1,2,20) as well and we could see whether it > outputs '10' or '10.0'. But yes, it would appear that in Oracle, NUMERIC > means FLOAT. > > Of course it does! All the others too. ___ sqlite-users mailing list

Re: [sqlite] Possible bug in type conversion prior to comparison

Actually, to be more accurate, the internal storage may be far from a float (as in IEEE double) but a divide on an integer-looking value will certainly be done with floating point math. On Mon, May 13, 2013 at 6:13 PM, Paul van Helden <p...@planetgis.co.za>wrote: > > I should ha

Re: [sqlite] Feature Request: Binding Arrays

Fantastic! I've been wanting this for a long time. Since which version do we have sqlite3_intarray_x? On Wed, Oct 16, 2013 at 1:28 PM, Richard Hipp wrote: > Please see http://www.sqlite.org/src/artifact/2ece66438?ln=13-75 > > > -- > D. Richard Hipp > d...@sqlite.org >

Re: [sqlite] Feature Request: Binding Arrays

> Since version 3.6.21, circa 2009-12-07. Note however that this capability > is not built in. It is an extension that you need to compile and link > separately. > > OK... Herewith my vote to make it standard then, like SQLITE_ENABLE_COLUMN_METADATA was enabled for the precompiled binary at some

[sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

Hi, Is this correct? Should update triggers not only fire for actual changes? I have a large table with a column which contains all NULL values except for 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers, except it fires for every row. Thanks, Paul.

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

n it comes to triggers? On Tue, Jul 3, 2012 at 2:19 PM, Yuriy Kaminskiy <yum...@gmail.com> wrote: > Paul van Helden wrote: > > Is this correct? Should update triggers not only fire for actual > changes? I > > have a large table with a column which contains all NULL val

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

> > > The statement "UPDATE table SET column=NULL" updates every row in the > table. The fact that some rows may already have a NULL in that > column is not important. > > Well, it is important to me, the word "change" means before != after :-)

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

On Tue, Jul 3, 2012 at 2:43 PM, Paul van Helden <p...@planetgis.co.za>wrote: > >> The statement "UPDATE table SET column=NULL" updates every row in the >> table. The fact that some rows may already have a NULL in that >> column is not important. >&g

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

On Tue, Jul 3, 2012 at 2:45 PM, Black, Michael (IS) wrote: > What's better is that it tells you what you asked for...not what you think > you asked for...which it does. > I asked for changes :-) > > You've already been shown the correct solution...a WHERE clause... > >

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

On Tue, Jul 3, 2012 at 3:03 PM, Black, Michael (IS) wrote: > And Oracle says the opposite: > > Yet they all give the same answer when done with "update testtable set > testrow=null where testrow not null; > > You keep hammering this one, it is obvious, I understand,

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

> > > Then why do you keep hammering on the idea that SQLite is somehow > incorrect or wrong? > > You've explained what you're trying to do. We've explained there is > a better way to do that, that also happens to provide the correct > answer on all platforms, AND likely runs faster--

Re: [sqlite] Select rows where a column is not unique

On Mon, Aug 6, 2012 at 2:58 PM, Tilsley, Jerry M. wrote: > Guys, > > I'm sure this is a pretty lame question, but my thinking hat is > malfunctioning this morning. How can I select all rows from a table where > a specific column is NOT UNIQUE? Table has three columns

Re: [sqlite] Unofficial poll

I am using this feature a lot. My applications log all changes to the database, SQL and parameters. So I have an attached log.db with a field for the SQL and then 32 typeless columns for the parameters. Works like a charm! On Sun, Sep 23, 2012 at 12:37 PM, Baruch Burstein

[sqlite] Fwd: SELECT Max(IndexedField) doesn't use partial index

Hi, I use a lot of indexes on fields that typically contain lots of NULLs, so the WHERE NOT NULL partial indexing seems very useful. However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs. a normal index, SQLite won't use the index to find Max(IndexedField) if it is a partial

Re: [sqlite] Fwd: SELECT Max(IndexedField) doesn't use partial index

On Mon, May 8, 2017 at 8:20 PM, Scott Robison <sc...@casaderobison.com> wrote: > On Mon, May 8, 2017 at 11:40 AM, Paul van Helden <p...@planetgis.co.za> > wrote: > > Hi, > > > > I use a lot of indexes on fields that typically contain lots of NULLs, so > >

Re: [sqlite] Fwd: ON CONFLICT parser bug?

> > > > A SELECT in an UPSERT should always contain a WHERE clause. Like this: > >INSERT INTO t1(a) SELECT a FROM t2 WHERE true ON CONFLICT(A) DO NOTHING; > > Without the WHERE clause, the ON keyword tricks the parser into > thinking it is processing an "ON" join constraint on the FROM

[sqlite] Fwd: ON CONFLICT parser bug?

Hi, I've used the new UPSERT with success with a complex SELECT, then got to scratch my head for a while on a much simpler query, so assuming this might be a bug: CREATE TABLE T2 (A INTEGER PRIMARY KEY); INSERT INTO T2 VALUES (1); CREATE TABLE T1 (A INTEGER PRIMARY KEY); INSERT INTO T1 VALUES

Re: [sqlite] more efficient JSON encoding: idle musing

> > > I experimented with a number of similar ideas for storing JSON when I > was first designing the JSON components for SQLite. I was never able > to find anything that was as fast or as compact as just storing the > original JSON text. > I've also done a lot of experiments and was surprised

Re: [sqlite] New word to replace "serverless"

"In-process" describes it best for me. On Wed, Jan 29, 2020 at 8:16 AM Darren Duncan wrote: > The concepts I like the best so far are "in-process" or "integrated" or > something library-themed. -- Darren Duncan > > On 2020-01-27 2:18 p.m., Richard Hipp wrote: > > For many years I have