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

[Paul van Helden]

>
>
> 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 at how little a
binary encoding saves in space. Also tried with lookups for keys, but the
lookup values quickly become close to the size of the keys (if not larger)
if keys are mostly shortish.

I'd be happy with a JSON5-like ability to have the quotes on keys optional
if they contain no spaces and no special characters. Seems to reduce the
data size quite significantly.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

"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 described SQLite as being "serverless", as a way
> > to distinguish it from the more traditional client/server design of
> > RDBMSes.  "Serverless" seemed like the natural term to use, as it
> > seems to mean "without a server".
> >
> > But more recently, "serverless" has become a popular buzz-word that
> > means "managed by my hosting provider rather than by me."  Many
> > readers have internalized this new marketing-driven meaning for
> > "serverless" and are hence confused when they see my claim that
> > "SQLite is serverless".
> >
> > How can I fix this?  What alternative word can I use in place of
> > "serverless" to mean "without a server"?
> >
> > Note that "in-process" and "embedded" are not adequate substitutes for
> > "serverless".  An RDBMS might be in-process or embedded but still be
> > running a server in a separate thread. In fact, that is how most
> > embedded RDBMSes other than SQLite work, if I am not much mistaken.
> >
> > When I say "serverless" I mean that the application invokes a
> > function, that function performs some task on behalf of the
> > application, then the function returns, *and that is all*.  No threads
> > are left over, running in the background to do housekeeping.  The
> > function does send messages to some other thread or process.  The
> > function does not have an event loop.  The function does not have its
> > own stack. The function (with its subfunctions) does all the work
> > itself, using the callers stack, then returns control to the caller.
> >
> > So what do I call this, if I can no longer use the word "serverless"
> > without confusing people?
> >
> > "no-server"?
> > "sans-server"?
> > "stackless"?
> > "non-client/server"?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

>
>
>
> 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 clause.
> The WHERE clause resolves the ambiguity.  The extra no-op WHERE clause
> is omitted from the prepared statement by the query optimizer and
> hence does not slow down the execution of the statement.
>
> This is a known limitation of the UPSERT syntax.  I had intended to
> document it, but I apparently forgot to do so, or at least I cannot
> find where I documented it right this second.  It is a messy situation
> that comes about due to our use of an LALR(1) parser (parsers with
> more lookahead also run slower) and by the need to provide full
> backwards compatibility with older versions of SQLite.
>
>
Thanks. Easy to live with! The first time I used UPSERT with a SELECT it
was a JOIN ending with USING(). Worked like a charm!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fwd: ON CONFLICT parser bug?

[Paul van Helden]

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 (1);
INSERT INTO T1 (A) SELECT A FROM T2 ON CONFLICT(A) DO NOTHING;

An SQL error has occurred: near "DO": syntax error

sqlite3_prepare_v2 actually stops the statement after DO, so that pzTail
points to " NOTHING".

More complex SELECT statements (seems anything after the table name, e.g.
WHERE or LIMIT will suffice) works:

INSERT INTO T1 (A) SELECT A FROM T2 WHERE 1=1
ON CONFLICT(A) DO NOTHING

I'm sure others have found this already?

Regards,

Paul.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

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
> > 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 index.
> >
> > Is this an optimization opportunity? I understand that other kinds of
> > partial indexes might exclude possible Min or Max values, but a NOT NULL
> > index would be fine for mins, maxes and most other things?
>
> This may be an optimization opportunity, but you can easily force the
> use of that index by stating WHERE NOT NULL in the select query
> itself. For example:
>
> CREATE TABLE a(b);
> CREATE INDEX ab on a(b) where b is not null;
>
> sqlite> explain query plan select max(b) from a;
> 0|0|0|SEARCH TABLE a
> sqlite> explain query plan select max(b) from a where b is not null;
> 0|0|0|SEARCH TABLE a USING COVERING INDEX ab
>
> >


Thanks Scott,

That works and fully solves my problem!

Regards,

Paul.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

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

Is this an optimization opportunity? I understand that other kinds of
partial indexes might exclude possible Min or Max values, but a NOT NULL
index would be fine for mins, maxes and most other things?

Regards,

Paul.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] minor documentation typo

[Paul van Helden]

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 Queries Using Partial Indexes
> [...]
> Example: Let the index by
>
> should be
>
> Example: Let the index be
>
> Greetings, Wolfgang
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Mixing text and numeric values in comparisons

[Paul van Helden]

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

If this seems silly, what I'm doing above is simulating a
sqlite3_bind_double if you change 1.0 to ? (Integers stored in float fields
will never find a match stored in a text field, without casting)

However:

CREATE TABLE tleft (Txt TEXT);
INSERT INTO tleft VALUES ('1');
CREATE TABLE tright (Flt REAL);
INSERT INTO tright VALUES (1.0);
SELECT * FROM tleft, tright WHERE Txt=Flt; -- returns a match!

Is it because of the following from https://www.sqlite.org/datatype3.html

>  (As an internal optimization, small floating point values with no
> fractional component and stored in columns with REAL affinity are written
> to disk as integers in order to take up less space and are automatically
> converted back into floating point as the value is read out. This
> optimization is completely invisible at the SQL level and can only be
> detected by examining the raw bits of the database file.)
>

Does the cast of Flt to TEXT use the stored integer value in the
comparison? I want my query to do the same!

I therefore make my case that SELECT '1'=1.0 should return 1, and not 0 as
it does at the moment, i.e. CAST(1.0 AS TEXT) must produce '1'

Paul.

[sqlite] Mixing text and numeric values in comparisons

[Paul van Helden]

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 values are _nearly_
always integers, but they can be text (alphanumeric). So the general case
is to compare anything to anything where there is a high likelihood of
values being integers.

[sqlite] Mixing text and numeric values in comparisons

[Paul van Helden]

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 others on this
list deal with this problem?

Paul.

>
> What is the best way to check for integers? (fabs(val) modf(val, )

[sqlite] Mixing text and numeric values in comparisons

[Paul van Helden]

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 understand, SQLite does convert doubles to integer values
for storage when they are small enough.

The problem presented itself when binding integers from an Access table
with sqlite3_bind_double because the Access table has the integers in a
float field. (Apparently common). I had to change my library to check
integers in double fields and then bind with sqlite3_bind_int64 in order to
hit a result with WHERE.

What is the best way to check for integers? (fabs(val)

[sqlite] Performance comparison between SQLite and SQL Server?

[Paul van Helden]

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

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

[Paul van Helden]

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 is for atomic
write to be disabled, for the maximum page size to be set to 65536, for
SQLITE_DEFAULT_PAGE_SIZE to be 1024, and for the maximum default page size
to be set to 8192."

So I set my page size to 8192. That means the suggested cache size is still
only 16MB.

If I increase my cache size to 20 000 pages I see a lot more that 10x
increase in memory use and also very slow connection close, presumably due
to deallocation of page memory.

Should I increase page size instead? What do you guys do when it is OK for
SQLite to chew as much memory as you have?

Also, does it make sense to change the page size only for the temp
database? "pragma temp.page_size=65536" ?

Regards,

Paul.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature Request: Binding Arrays

[Paul van Helden]

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

I prefer to stick to the precompiled binaries. Besides, it would make the
sqlite3_intarray functions more visible in the documentation, etc. I'm sure
I'm not the only one that didn't know about this very useful functionality.

It's about time the binary got slightly bigger ;-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature Request: Binding Arrays

[Paul van Helden]

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
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

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 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

> 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

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 outlandish if MySQL does it "my way".

Simon says: "The PRAGMAs allow SQLite to switch between different
behaviours when the standard doesn't say what should happen". I would
venture to say perhaps the standard wasn't too clear on this, or at the
very least the fact that MySQL does it differently means there is a bit of
a smudge on this part.

Darren says: "declaring NUMERIC types is saying you don't care about the
behavior". I do care about behaviour, so I'll change my management system
to exclude NUMERIC as an option since I have no use for it then! I cannot
expect my clients to know little quirks to this level of detail. I agree
with what Darren says about the option of having 2 operators, / and div,
that's what MySQL does and it is also a feature of Pascal and other
languages.

Please don't get me wrong. I haven't used MySQL for new projects in years,
so I'm not promoting it in any way. Also, if NUMERIC wasn't so ubiquitous
in the SQL world, I wouldn't even have raised the issue.

If I am correct in taking away from this discussion "don't use NUMERIC
column definitions if you want to do any calculations [with divisions] on
them", then we can let it rest now. I'll dream of seeing NUMERIC(p,s) one
day that enforces (p,s) (and doesn't do integer division unless s=0 !) :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

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 future to avoid future pain. (I
have a SQLite management system where my clients can create their own
tables and enter their own SQL for custom reports)


On Sun, May 12, 2013 at 2:35 PM, Simon Slavin  wrote:

>
> I think it's endemic to computers, the same as the difficulty with
> counting in units of 0.1 until you get 1.
>
>
On Sun, May 12, 2013 at 2:59 PM, Yan Seiner  wrote:

> If you want floats, you have to specify floats.  If you want integers, you
> have to specify integers.  The compiler has no way to know which you want.
>
> Just get in the habit of always adding a .0 if you want float constants.
>
>
I can live with SELECT 1/2 vs SELECT 1.0/2. The problem is that there is no
way to specify a float when you insert into a NUMERIC. 1.0 turns into an
integer. Then you do a division on all rows with an SQL select and you get
mixed results because some rows have floats and some rows have integers. In
C, 1/2=0. In Pascal 1/2=0.5. Oracle/MSSQL/others act like C, MySQL acts
like Pascal. This is not my main issue. Consistency throughout a table, is.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

> 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 are decimal numbers that have infinite binary
> expansions?
>

I wouldn't store such numbers into a NUMBER(10,2), just a NUMBER (I know
they are the same in SQLite).

>
> You are also talking presentation (as in formatting) of numeric values as
> opposed to representation (as in storing/retrieving). The former is best
> handled in the user interface while the latter is the subject of database
> engines.
>

My point was about not storing binary junk - the part of a number that has
no meaning because the accuracy of the inputs is limited. When you have a
generic db manager that can show any table or if you are looking at the
results of your own SQL statement, it helps to reduce clutter on the
screen. The data also compresses better.

>
> Fatihful reproduction of formatting would be possible using TEXT affinity
> and calling sqlite3_bind_text. Performing arithmetic with these "numbers"
> would however be tricky, slow and would still not guarantee that calculated
> values would conform to the desired formatting.
>
> Of course, but in most cases we don't need to go this far. My main point
is about rounding before binding; my secondary point that scale in a column
definition can be desirable to avoid it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

>
>
> 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 NUMBER(10,2) have no
effect. Too often I see small values with 15 digits in a table because a
double was passed as-is. It is not just about space, it is also about
presentation. In engineering we are taught that the number of digits should
also tell you the accuracy of the sample, so for example a
latitude/longitude obtained from a handheld GPS should be stored with 6
decimal digits (~10cm), the rest is just junk. Since the database does not
do this for you, when the programmer knows the accuracy of the sample, he
shouldn't be lazy and instead do Round(Longitude*100)/100 before
binding. Of course, when the data is presented it should be properly
rounded with zeros added at the end or even zeros replacing digits to the
left of the decimal (to indicate precision), but my point is you shouldn't
store junk digits in the first place.

I love it that you don't have to specify TEXT and NUMBER lengths, but would
have preferred that SQLite didn't ignore them when specified and that in a
NUMBER(p,s) column, the double is stored as an integer internally if p<=18.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM

[Paul van Helden]

>
> 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 the solution is that simple and
won't break anything.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] TRUNCATE TABLE alias for DELETE FROM

[Paul van Helden]

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 list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unofficial poll

[Paul van Helden]

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

> I am curious about the usefulness of sqlite's "unique" type handling, and
> so would like to know if anyone has ever actually found any practical use
> for it/used it in some project? I am referring to the typeless handling,
> e.g. storing strings in integer columns etc., not to the non-truncating
> system e.g. storing any size number or any length string (which is
> obviously very useful in many cases).
> Has anyone ever actually taken advantage of this feature? In what case?
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

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 (charge_code,
> mnemonic, description).
>
>
SELECT * FROM tablename WHERE charge_code IN (SELECT charge_code FROM
tablename GROUP BY description HAVING Count(*)>1)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

>
>
>   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-- especially if any
>   of those columns has an index on them.
>

I gave a simple example. I work with generic cases. My application doesn't
have all static SQL. A lot is from the user or built dynamically.

>
> > What if the SET and WHERE contain many columns?
>
>   Then you're asking for a more complex operation.  Your SQL gets a bit
>   more complex as well.
>
> > Now I have to add a "WHERE column<>mynewval" for every column in SET
> > to get the actual changes, something like UPDATE testtable SET col1=?1,
> > col2=?2, col3=? WHERE  complex where clause
> > AND col1<>?1 AND col2<>?2 AND col3<>?3.
>
> > (passing a null parameter to the above won't even work!)
>
>   Well, no, it won't, because you're using the wrong operator.
>
>   Use "WHERE col1 IS NOT ?1 AND..." and it all works fine.
>
> OK thanks, so I should always use IS NOT where I always used <>. Oh well
(talk about yuck!)


> > No surprises there. Oracle has never managed to impress me.
>
>   I know what you mean.  That MySQL database they make is difficult to
>   take seriously.
>
> Very funny. They didn't make it, they own it now.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

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, THANKS!  What if
the SET and WHERE contain many columns? Now I have to add a "WHERE
column<>mynewval" for every column in SET to get the actual changes,
something like UPDATE testtable SET col1=?1, col2=?2, col3=? WHERE  AND col1<>?1 AND col2<>?2 AND col3<>?3.
(passing a null parameter to the above won't even work!)

>
> Connected to:
> Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
> Production
> With the Partitioning, Oracle Label Security, OLAP, Data Mining,
> Oracle Database Vault and Real Application Testing options
> SQL> create table testtable(testrow number);
> Table created.
> SQL> insert into testtable values(NULL);
> 1 row created.
> SQL> insert into testtable values(NULL);
> 1 row created.
> SQL> insert into testtable values(NULL);
> 1 row created.
> SQL> update testtable set testrow=null;
> 3 rows updated.
> SQL> update testtable set testrow=null;
> 3 rows updated.
>

No surprises there. Oracle has never managed to impress me.


> SQL> update testtable set testrow=null where testrow is not null;
>
> 0 rows updated.
>
> So rather than holding your breath for Oracle to change I'd recommend you
> do it the portable way.
>
>  I'm not waiting for anything. My last question was simple: which is
better? Since MySQL does it the "correct way" perhaps we can just think
about this for sqlite4?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

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...
>
> I've done that even before posting here, just thought it odd.

> You want sqlite to do a complete record compare, including following
> update triggers, on EVERY record it looks at to see if something happened
> to change???
>
Just the fields in the SET clause.

>
> Yuck...
>
> As compare to the WHERE clause which does exactly what you want and runs
> oodles faster (in all likelihood).
>
> I always keep an eye on the "affected rows" to see what my statements have
done (whether I used a WHERE or not). Even with a WHERE, I would prefer
seeing actual changes :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

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.
>>
>> Well, it is important to me, the word "change" means before != after :-)
>

Just checked MySQL:

UPDATE testtable SET testrow=NULL;
Affected rows: 40
UPDATE testtable SET testrow=NULL;
Affected rows: 0

That is what I'm familiar with.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

>
>
>   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 :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

You are right, sorry, just checked. sqlite3_changes returns number of
records hit, not changed. Have been using sqlite for 2 years now and was
always under the impression this was for actual changes.

But which is better behaviour, reporting "row hits" versus real changes?
Especially when 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 values except
> for
> > 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers,
> > except it fires for every row.
>
> I'm pretty sure that sqlite3_changes() in this case also returns *all*
> rows, not
> only 4 "really" changed. If you want triggers to only fire for really
> changed
> rows (and sqlite3_changes() to return only those 4 rows), you should add
> WHERE
> clause:
> UPDATE table SET column=NULL WHERE column IS NOT NULL;
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

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. sqlite3_column_text will
add a zero character and if your database uses UTF16 encoding will give you
an interesting result.

Regards,

Paul.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

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 text, the command line client (and
apparently .output) does.


> My "proof of concept" goal is to now get that full binary stream back
> 100% and write it to a file
>
> Read the results from the SELECT with a C program. sqlite3_column_bytes


> I am getting this currently with your sample file when I do a full LOOP
> back test ;-( I must be doing the SELECT wrong somehow?
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul van Helden]

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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique index versus unique constraint

[Paul van Helden]

>
> 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 actually makes sense to never drop any automatic index (!). My question
should have been how to reliably determine whether an index is automatic or
not. Unless someone can come up with a compelling reason not to test for
"sqlite_autoindex_" in pragma index_list, I'd prefer to stick to that.

Now I'm wondering if in future a situation could arise where the SQL will be
provided for automatic indexes... Eg. to aid in exporting DDL.


>
> Yes, we definitely need more extensive schema introspection facilities in
> SQLite.
>

A simple "autoindex" field in pragma index_list would be a good start :-)


>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unique index versus unique constraint

[Paul van Helden]

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 can add the unique
requirement later by creating a unique index (if it works!). Apart from
behaving the same for INSERTS, this is not exactly the same because I can
simply drop the index to remove the unique requirement.

So my problem is, I need to know how the unique requirement was created in
the first place in order to get rid of it in the appropriate manner.

One solution would be to parse the SQL field in sqlite_master to look for
the constraint. (I'd prefer not!). I also don't really want to attempt a
DROP INDEX and then fall back to recreating the table since I am generating
scripts.

Another solution that ocurred to me is to check for "sqlite_autoindex_" in
the name field of pragma index_list(tablename). Can I simply assume that
unique indexes named sqlite_autoindex_* cannot be dropped?

Perhaps if pragma table_info(tablename) had a "unique" column like it has a
"notnull" column, but only for unique constraints on single fields. Or if
pragma index_list had a "constraint" (or "cantdrop" or something) column
that would indicate that the index cannot be dropped.

Am I missing something? How do you solve this problem?

Regards,

Paul.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users