Re: [sqlite] How to group this?

[Wolfgang Enzinger]

Am Mon, 10 Feb 2020 01:42:14 + schrieb Simon Slavin:

> On 10 Feb 2020, at 1:25am, no...@null.net wrote:
>
> create two VIEWs, [...].  Index both VIEWs on (id, date),

I don't think that creating an index on a view actually works, does it?

Wolfgang

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

[sqlite] gmane.comp.db.sqlite.general

[Wolfgang Enzinger]

Hi,

for those of us who - like me - prefer to access this mailing list via
NNTP: the Newsserver's address has changed from

news.gmane.org

to

news.gmane.io

(sorry if this is old news; I couldn't find any information about that
here.)

Wolfgang

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

Re: [sqlite] Last record

[Wolfgang Enzinger]

Am Tue, 15 Oct 2019 13:36:37 -0800 schrieb Adam Levy:

> Although what Doug suggested could work, it doesn't make sense to me to add
> an extra count or max query just to know how many results will be returned
> when those results will be queried anyway.

One typical use case I can think of is that one wants to display a progress
bar during population of a list control with a query result. Impossible as
long as you don't know beforehand what 100% is by means of an absolute
number.

Maybe that's the reason why those percentage based progress bars are more
and more replaced by animation controls nowadays that just indicate that
something is still going on, without any kind of prediction how much more
time it will take. ;-)

Wolfgang

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

Re: [sqlite] Typo

[Wolfgang Enzinger]

Am Tue, 25 Dec 2018 08:59:45 +0100 schrieb gwenn:

> There is a typo here:
> https://sqlite.org/session/sqlite3changeset_op.html
>> If pbIncorrect is not NULL, then *pbIndirect is set to true
> Should be pbIndirect instead of pbIncorrect.

Another one in https://www.sqlite.org/security.html:

"... even with presented with maliciously malformed SQL inputs or database
files."

Should be "... even when presented with ..." IMO.

Wolfgang 

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

[sqlite] minor documentation flaw

[Wolfgang Enzinger]

In https://sqlite.org/lang_expr.html, the anchor



appears twice. Obviously the second occurence should be



Wolfgang

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

Re: [sqlite] EXTERNAL: SQLite 3.22.0 coming soon

[Wolfgang Enzinger]

Am Wed, 10 Jan 2018 02:25:35 + schrieb Edwards, Mark C.:

> Release mode/x86 Visual Studio 2015 Prono problems with the new snapshot

Same here with my ancient Visual Studio 2005. :-)

Cheers Wolfgang

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

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

[Wolfgang Enzinger]

Am Mon, 1 Jan 2018 20:30:10 +0100 schrieb Clemens Ladisch:

> Wolfgang Enzinger wrote:
>> Am Mon, 1 Jan 2018 16:20:21 +0100 schrieb Clemens Ladisch:
>>> It is indeed possible to change the query so that SQLite uses rowid
>>> lookups for the R-tree filter (INDEX 1).  However, any likelihood on the
>>> R-tree search expression still did not make any difference.  Do you have
>>> an example?
>>
>> SELECT * FROM t INNER JOIN i USING(id)
>>  WHERE LIKELIHOOD(i.minX>=-81.08 ..., 0.999)  -- 0.999
>>AND LIKELIHOOD(t.x=3, 0.001);  -- 0.001
>>
>> SELECT * FROM t INNER JOIN i USING(id)
>>  WHERE LIKELIHOOD(i.minX>=-81.08 ..., 0.001)  -- 0.001
>>AND LIKELIHOOD(t.x=3, 0.999);  -- 0.999
> 
> Sorry, this is not what I meant.

Seems like I didn't get the original question correctly, then.

> The original question was about manually removing the R-tree search
> depending on the spatial window.  So, do you have an example where
> the query plan changes due to a difference in *only* the likelihood
> of the R-tree search expression?

No.

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

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

[Wolfgang Enzinger]

Am Mon, 1 Jan 2018 16:20:21 +0100 schrieb Clemens Ladisch:

> It is indeed possible to change the query so that SQLite uses rowid
> lookups for the R-tree filter (INDEX 1).  However, any likelihood on the
> R-tree search expression still did not make any difference.  Do you have
> an example?

Try:

---

CREATE TABLE t(id INTEGER PRIMARY KEY,x INTEGER,y INTEGER,z INTEGER);
CREATE VIRTUAL TABLE i USING rtree(id,minx,maxx,miny,maxy);
CREATE INDEX t_x ON t(x);

---

EXPLAIN QUERY PLAN
SELECT * FROM t INNER JOIN i USING(id) 
 WHERE LIKELIHOOD(i.minX>=-81.08 AND i.maxX<=-80.58
   AND i.minY>=35.00  AND i.maxY<=35.44, 0.999)  -- 0.999
   AND LIKELIHOOD(t.x=3, 0.001); -- 0.001

-> SEARCH TABLE t USING INDEX t_x (x=?)
-> SCAN TABLE i VIRTUAL TABLE INDEX 1:

---

SELECT * FROM t INNER JOIN i USING(id) 
 WHERE LIKELIHOOD(i.minX>=-81.08 AND i.maxX<=-80.58
   AND i.minY>=35.00  AND i.maxY<=35.44, 0.001)  -- 0.001
   AND LIKELIHOOD(t.x=3, 0.999); -- 0.999

-> SCAN TABLE i VIRTUAL TABLE INDEX 2:D0B1D2B3
-> SEARCH TABLE t USING INTEGER PRIMARY KEY (rowid=?)

---

Tested with SQLite 3.13.0 here, but IIRC newer versions behave the same.

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

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

[Wolfgang Enzinger]

Am Mon, 1 Jan 2018 10:45:50 +0100 schrieb Clemens Ladisch:

> Wolfgang Enzinger wrote:
>> First, query the overall extent of your data, like this:
>> SELECT MIN(mingx),MAX(maxgx),MIN(mingy),MAX(maxgy) FROM flst_shape_index;
> 
> This results in a full table scan.  Instead of caching these values manually,
> it would be a better idea to read them from the index:
> 
>   SELECT rtreenode(2, data) FROM flst_shape_index_node WHERE nodeno = 1;
> 
> (rtreenode() is undocumented; maybe you should use your own decoder.)

Thanks, didn't know that, I'll look into it. You're right, my query results
in a full table scan, however it's pretty fast anyway - less than a second
with 160,000 rows and cold cache.

>> Let SQLite know about that likelihood in a JOIN query
> 
> This does not appear to change anything with a virtual table:
> 
>   CREATE TABLE t(id INETGER PRIMARY KEY, x, [...]);
>   CREATE VIRTUAL TABLE i USING rtree(id, minx, maxx);
> 
>   SELECT t.* FROM t JOIN i USING (id) WHERE likelihood(i.minx BETWEEN 10 AND 
> 20, 0.01);
>   --EQP-- 0,0,1,SCAN TABLE i VIRTUAL TABLE INDEX 2:D0B0
>   --EQP-- 0,1,0,SEARCH TABLE t USING INTEGER PRIMARY KEY (rowid=?)
>   SELECT t.* FROM t JOIN i USING (id) WHERE likelihood(i.minx BETWEEN 10 AND 
> 20, 0.99);
>   --EQP-- 0,0,1,SCAN TABLE i VIRTUAL TABLE INDEX 2:D0B0
>   --EQP-- 0,1,0,SEARCH TABLE t USING INTEGER PRIMARY KEY (rowid=?)

This is not surprising because only criteria concerning the "i" table are
in effect here. So it is clear that even a likelihood of 0.99 is more
selective than a likelihood of 1.000 (= no filter criteria in this table).
However, if your query has criteria both in the "i" and the "t" table, it
can make a difference.

Of course, anybody correct me if I'm mistaken.

Happy new year!
Wolfgang

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

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

[Wolfgang Enzinger]

Am Fri, 29 Dec 2017 19:59:12 +0100 schrieb Andrea Aime:

> With SQLite R-Tree I'm using either a join with the index virtual table, or
> a subquery
> retrieving the ids from the rtree. Regardless, the query is basically
> ordering SQLite
> to use the index.
> So I was wondering, is there any opportunity to run a blazing fast
> pre-query against
> the index that will tell me whether joining/subquerying into the rtree is
> going to be a win, or not?

I had good results in a similar situation with this strategy:

First, query the overall extent of your data, like this:
SELECT MIN(mingx),MAX(maxgx),MIN(mingy),MAX(maxgy) FROM flst_shape_index;

Second, for every spatial query, calculate the size of the area in
question.

Then, with these two rectangles, you can calculate the LIKELIHOOD that a
particular record in your data is located within the requested area, i.e.
meets the spatial criteria.

Let SQLite know about that likelihood in a JOIN query, using the LIKELIHOOD
function (http://www.sqlite.org/lang_corefunc.html#likelihood). Also, if
possible, give LIKELIHOOD information to the query planner for any other
criteria used. SQLite will consider them.

HTH Wolfgang

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

Re: [sqlite] Foreign keys - one to one, one to many relationship

[Wolfgang Enzinger]

Am Sat, 14 Oct 2017 13:01:59 -0400 schrieb Richard Damon:

> you could create a record in the other table without a corresponding 
> record in the blob table, but that is sort of needed to avoid chicken 
> and egg issues with creating new records (not sure when foreign key 
> requirements are tested), [...]

Depends on how the foreign key was defined; in order to avoid chicken and
egg issues, a Deferred Foreign Key Constraint is recommended:

http://www.sqlite.org/foreignkeys.html#fk_deferred

AFAIK, at least. ;-)

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

Re: [sqlite] Should the INTEGER not be cast to a REAL

[Wolfgang Enzinger]

Am Thu, 7 Sep 2017 00:28:56 +0200 schrieb Cecil Westerhof:

> 2017-09-07 0:20 GMT+02:00 Wolfgang Enzinger <sql...@enzinger.net>:

>> Add this trigger and everything is fine. ;-)
>>
>> CREATE TRIGGER weights_float_force_datatype
>> BEFORE INSERT ON weights
>> FOR EACH ROW
>> BEGIN
>> INSERT INTO weights(float) VALUES (CAST (new.float AS REAL));
>> SELECT RAISE(IGNORE);
>> END
>>
> 
> ​I do not think I should do that.
> Executing:
> SELECT CAST("Hello" AS REAL), TYPEOF(CAST("Hello" AS REAL))
> 
> Gives:
> "0.0""real"

Depends. ;-) What else do you think CAST("Hello" AS REAL) should be
converted to?

Seriously: in case you cannot be sure that only numbers will be inserted
into this column you should probably expand the trigger with a CASE WHEN
... ELSE ... END construct.

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

Re: [sqlite] Should the INTEGER not be cast to a REAL

[Wolfgang Enzinger]

Am Thu, 7 Sep 2017 00:15:39 +0200 schrieb Cecil Westerhof:

> 2017-09-07 0:05 GMT+02:00 R Smith :
> 
>> On 2017/09/06 11:58 PM, R Smith wrote:
>>
>>> Your CHECK constraint should really find that the value is acceptable
>>> when it is either a REAL, OR an INT, because both those types of data
>>> satisfies your requirement.
>>>
>>>
>> To be specific, this should work for you:
>>
>> CREATE TABLE weights(
>> float  REAL,
>> CONSTRAINT float CHECK(TYPEOF(float) IN ("real","int"))
>> );
> 
> 
> ​But it does not.
> 
> The strange thing is: when I remove the constraint and do:
> INSERT INTO testing
> (float)
> VALUES
> (0)
> 
> The insert is successful of-course.
> When I then execute:
> SELECT float, TYPEOF(float)
> FROM testing
> 
> I get:
> "0.0""real"
> 
> 
> Maybe this is correct, but it is certainly confusing.

Add this trigger and everything is fine. ;-)

CREATE TRIGGER weights_float_force_datatype
BEFORE INSERT ON weights
FOR EACH ROW
BEGIN
INSERT INTO weights(float) VALUES (CAST (new.float AS REAL));
SELECT RAISE(IGNORE);
END

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

Re: [sqlite] Mailinglist question

[Wolfgang Enzinger]

Am Thu, 10 Aug 2017 01:37:14 +0200 schrieb Wolfgang Enzinger:

> I'm connected to this mailing list as if it was a newsgroup using
> nntp://news.gmane.org:563/gmane.comp.db.sqlite.general.

Bummer ... just a few hours after I posted this, that service seems to have
stopped pulling any new posts - before that point in time I had used it for
years without any issue.

This is a test now if my message comes thru.

On their website I read that they're undergoing some reconstruction
process, hopefully the NNTP service will resurrect, I really like it.

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

Re: [sqlite] Mailinglist question

[Wolfgang Enzinger]

Am Wed, 9 Aug 2017 17:02:42 -0500 schrieb Nico Williams:

> I... would like to use NNTP, but I'd need someone to provide the
> service.

I'm connected to this mailing list as if it was a newsgroup using
nntp://news.gmane.org:563/gmane.comp.db.sqlite.general. No authentication
required. Of course, for posting you have to be subscribed to the list.
(all info AFAIK)

HTH Wolfgang

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

Re: [sqlite] Lightweight solution for Unicode-savvy collation?

[Wolfgang Enzinger]

Am Thu, 27 Jul 2017 16:47:00 -0700 schrieb Jens Alfke:

> The project I work on needs the ability to do Unicode-savvy string
> collation, which SQLite doesn’t provide. But we’re somewhat sensitive to
> code size, so we don’t want to just drop in the hugeness that is ICU.
> We’ve looked at a couple of other Unicode/UTF-8 libraries (like
> utf8rewind), and while they do case folding they don’t do collation. 
> 
> We can’t be the first SQLite client to have this need. Anyone know of
> any good solution?

If I got the question right, I think it depends a lot on the platform
you're targeting at. For applications that only need to run on Windows, I
register a custom collation which in its callback hands the actual work
over to the CompareStringW() function. Works.

HTH Wolfgang

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

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

[Wolfgang Enzinger]

Am Mon, 29 May 2017 14:27:56 +0100 schrieb Thomas Flemming:

> Ok, here is a sample to try these queries:
> 
> http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip
> (825mb, 12 mio records)

Just a few quick observations ...

First, I would replace all column declarations like

LONG PRIMARY KEY

to

INTEGER PRIMARY KEY

This can make a huge difference AFAIK.

> Before I change my app-logic to do the styleid-query on the app-side, I would 
> like to know, if there might be a chance to get this fast on the sqlite-side.
> 
> 
> very fast, 77 records, 49ms:
> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
> AND x0 < 30.46203 AND  x1 > 30.00074766
> AND 18 BETWEEN z0 AND z1
> AND Pois_bb.Id = Pois.Id;
> 
> 
> fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??):
> SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219);
> 
> 
> very slow: 55 records, 3ms:
> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
> AND x0 < 30.46203 AND  x1 > 30.00074766
> AND 18 BETWEEN z0 AND z1
> AND Pois_bb.Id = Pois.Id
> AND styleid IN (9,48,73,200,142,31,219);

Do you know which SQLite version is being used by SQLite Expert
Professional 3.5? Just wondering because my quick tests on this reveal
different query plans, depending on the presence of a sqlite_stat4 table
(absent in your database) and the SQLite version (I ran some quick tests
using SQLite 3.13 and 3.18). I'll have to dig deeper into this in the next
days though.

Wolfgang

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

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

[Wolfgang Enzinger]

Am Sat, 27 May 2017 19:20:00 -0400 schrieb Richard Hipp:

> On 5/27/17, Thomas Flemming  wrote:
>> Hi,
>>
>> I have a table Pois with points of interest (geogr. coordinate, label,
>> styleid) where I do regional querys using a rtree-index:
>>
>> SELECT Pois.* FROM Pois_bb, Pois WHERE  y0 < -14.8600 AND  y1 > -15.12862
>>AND   x0 < 30.46203 AND  x1 > 30.00074766
>>AND   18 BETWEEN z0 AND z1
>>AND   Pois_bb.Id = Pois.Id
>> Thats very fast, 50ms.
>>
>> The problem is, when I add a second condition to get certain poi-types only
>> in the area:
>>
>> AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>>
>> The query becomes really slow, 800ms.
>> There is of course also an index on styleid.
>>
>> I also realized, just this query:
>>
>> SELECT * FROM Pois WHERE styleid IN
>> 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>>
>> is also slow for the first call. The second call is fast.
>>
>> (Using SQLite Expert Professional 3.5 for testing).
>>
>> Any ideas, how to speed this up?
> 
> Have you tried running ANALYZE on your database?

Does ANALYZE gather statistical data about rtree virtual tables? I seem to
remember that this is not the case.

I don't have an appropriate database at hand right now since I'm busy
currently with a different project, but I seem to remember that I observed
something similar: queries with an rtree table involved seem to *always*
prefer the spatial index over any other index, even if the clipping
embraces the complete extent of graphical data. This is unefficient
especially when the "related table" (that is, another table that is linked
to the rtree virtual table by an object ID) holds much more selective (and
indexed) criteria.

This is just out of the top of my head; anyway, if you don't hear back from
Thomas, please let me know, and I'll gladly provide more details (not
before Wednesday though).

And thanks again for then LEFT JOIN VIEW optimization - sorry that it
caused so much trouble ... I was completely unaware of the many pitfalls
this topic involves.

Wolfgang

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

Re: [sqlite] Thank you for your work

[Wolfgang Enzinger]

Am Fri, 19 May 2017 13:36:57 -0400 schrieb Richard Hipp:

> On 5/19/17, Claudio Bantaloukas  wrote:
>>
>> Lo and behold, the latest version handled these queries without issue,
>> halved the time it took to run some other queries and has apparently not
>> introduced any new issues.
>>
> 
> It's always nice to hear that we did something right :-)

Actually you're doing a lot of things right IMO! :-) Anyway, I second
Claudio's emotion.

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

[sqlite] Minor issue: compile error with 3.19 and MSVC 2005

[Wolfgang Enzinger]

Hallo,

I don't think this is a big deal, but probably helpful for one or another:

I tried to compile SQLite 3.19 with MSVC 2005 and got a compile error C2143
at line 143542 of sqlite3.c.

Removing the trailing semicolon from

if( (zKey = sqlite3_uri_parameter(zOpen, "hexkey"))!=0 && zKey[0] ){;

fixed the issue.

All the best, Wolfgang

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

Re: [sqlite] SQLite DB on external USB HD - is it safe?

[Wolfgang Enzinger]

OK, thanks for the detailed analysis! :-)

Am Tue, 09 May 2017 16:06:28 -0600 schrieb Keith Medcalf: 

[...]

> If Windows reports that the filesystem is "local" then it is OK.  If
> Windows reports that the filesystem is "remote", then it is ungood.  A
> "drive mapped to the local computer" is a remote filesystem, for
> example, and is ungood.  A local filesystem accessing a storage device
> via a really long cable is still a local filesystem and is good (even
> though the storage device itself may be located in a very remote part of
> Siberia, very far away from your computer in sunny Florida). 

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

[sqlite] SQLite DB on external USB HD - is it safe?

[Wolfgang Enzinger]

Dear group members,

since it's wise to store SQLite databases on local HDs (as opposed to
network filesystems) in order to avoid corruption, I would like to have my
program check if this requirement is fulfilled. I'm on Windows so I use
GetDriveType() for testing the DB path. Now I noticed that this function
returns DRIVE_FIXED also for drives that are actually USB connected
external HD volumes. Anybody got any experiences if this is actually a safe
place for an SQLite-DB? If it's not, any ideas as to how one can
distinguish such drives from "real" HDs?

Any input appreciated.

Wolfgang

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

Re: [sqlite] Optimization opportunity

[Wolfgang Enzinger]

Am Fri, 14 Apr 2017 15:14:12 -0400 schrieb Richard Hipp:

> But I've spent Good Friday working around it.

A thousand thanks! :-)

> Please try using the tip of the left-join-view branch
> (https://www.sqlite.org/src/timeline?c=left-join-view) and let me know
> if that version works better for you.  After some additional testing,
> this optimization will likely be merge to trunk and appear in the next
> release.  Your beta-testing is important - Thanks.

Sadly my C skills are just good enough to compile the amalgamation. But as
soon as the beta amalgamation will be available, I'll test it intensely,
promised.

Thanks again & happy Easter! :-)

Wolfgang

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

Re: [sqlite] Optimization opportunity

[Wolfgang Enzinger]

Am Fri, 14 Apr 2017 10:59:25 -0400 schrieb Richard Hipp:

> Performing this rewrite of a view into a simple LEFT JOIN is trickier
> than it seems at first glance.  The rewrite works for the example you
> provide.  But subtle changes to the view can make the rewrite invalid.
> For example:
> 
> CREATE VIEW z AS SELECT
>fk,
>   coalesce(flags&1,0) AS odd,  -- Added coalesce()
>   (flags&2)>>1 AS even,
>   (flags&4)>>2 AS prime
>   FROM y;
> 
> The addition of the coalesce() function on one of the result columns
> of the view means that a transformation such as you suggest will give
> a different (and incorrect) answer.  This is just one of many examples
> of the subtle pitfalls involved in trying to convert a LEFT JOIN into
> a form that can make better use of indexes.

Thank you Richard. I have to admit that it took me quite a while and also
reading the comment for check-in [1838a59c] several times to really
understand your explanation. Duh, that's tricky indeed!

Wolfgang

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

[sqlite] Optimization opportunity

[Wolfgang Enzinger]

Hello,

given the following:



CREATE TABLE x(
  pk INTEGER PRIMARY KEY,
  description TEXT
);

CREATE TABLE y(
  fk INTEGER REFERENCES x(pk),
  flags INTEGER
);

CREATE INDEX yy ON y(fk);

CREATE VIEW z AS SELECT
  fk,
  (flags&1) AS odd,
  (flags&2)>>1 AS even,
  (flags&4)>>2 AS prime
  FROM y;

INSERT INTO x(pk,description) VALUES 
  (1,'one'),(2,'two'),(3,'three'),(4,'four');

INSERT INTO y(fk,flags) VALUES (1,1|0|0),(2,0|2|4),(3,1|0|4),(4,0|2|0);



Now using the VIEW z in a JOIN results in a full table scan on TABLE y
despite a WHERE clause and an appropriate INDEX:

EXPLAIN QUERY PLAN SELECT x.pk,z.odd,z.even,z.prime
FROM x LEFT JOIN z ON x.pk=z.fk WHERE x.pk=2;
1|0|0|SCAN TABLE y
0|0|0|SEARCH TABLE x USING INTEGER PRIMARY KEY (rowid=?)
0|1|1|SCAN SUBQUERY 1

Bypassing the VIEW however uses INDEX yy:

EXPLAIN QUERY PLAN
SELECT x.pk,(y.flags&1) AS odd,(y.flags&2)>>1 AS even,(y.flags&4)>>2 AS
prime FROM x LEFT JOIN y ON x.pk=y.fk WHERE x.pk=2;
0|0|0|SEARCH TABLE x USING INTEGER PRIMARY KEY (rowid=?)
0|1|1|SEARCH TABLE y USING INDEX yy (fk=?)

Unless I'm missing something, I think there is a potential optimization
opportunity.

Identical results with SQLite versions 3.13, 3.17 and 3.18.

Cheers, Wolfgang

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

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

[Wolfgang Enzinger]

Am Sat, 4 Feb 2017 09:04:58 -0600 schrieb Clyde Eisenbeis:

> When I enter last_insert_rowid(), the compiler complains.  I think
> last_insert_rowid() is SQLite3.
> 
> Is there an equivalent for System.Data.SQLite?

I don't know, but you can use the SQL function of the same name:
https://www.sqlite.org/lang_corefunc.html#last_insert_rowid

SELECT last_insert_rowid();

HTH, Wolfgang

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

Re: [sqlite] web interface down

[Wolfgang Enzinger]

Am Tue, 30 Aug 2016 14:44:03 -0700 schrieb Darren Duncan:

> On 2016-08-30 2:22 PM, Wolfgang Enzinger wrote:
>> I hope this is the right place ...
>>
>> The web interface for this mailing list
>> (http://news.gmane.org/gmane.comp.db.sqlite.general) seems to be down for
>> quite a while now, I'm getting timeouts constantly. The NNTP interface,
>> however, works fine. Anyone can do anything about it?
> 
> As was in the news a month back, Gmane shut down its web interface.

Ah, thanks, missed that.

Personally I didn't use the web interface all that much, but it seemed a
good alternative for those who prefer a "proper forum" over a mailing list,
thus allowing kinda "to each his own", with a common core, nevertheless.
;-)

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

[sqlite] web interface down

[Wolfgang Enzinger]

Hi group,

I hope this is the right place ...

The web interface for this mailing list
(http://news.gmane.org/gmane.comp.db.sqlite.general) seems to be down for
quite a while now, I'm getting timeouts constantly. The NNTP interface,
however, works fine. Anyone can do anything about it?

Cheers, Wolfgang

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

[sqlite] minor formattig flaw in documentation

[Wolfgang Enzinger]

I just came across a minor formattig flaw in the VDBE documentation
(https://www.sqlite.org/opcode.html); in the opcodes table, the description
for the Cast opcode reads like this:

---
Force the value in register P1 to be the type defined by P2.

  TEXT  BLOB  NUMERIC  INTEGER  REAL 

A NULL value is not changed by this routine. It remains NULL.
---

Obviously, handling the HTML tags went wrong here.

Wolfgang

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

[sqlite] Version 3.13.0 coming soon

[Wolfgang Enzinger]

Am Tue, 3 May 2016 13:21:04 -0400 schrieb Richard Hipp:

> On 5/3/16, nomad at null.net  wrote:
>> On Tue May 03, 2016 at 08:33:30AM -0400, Richard Hipp wrote:
>>>
>>> Yes.  Apparently that is the new standard for security on unix
>>
>> The way I understood Rolf's comment was that he was pointing out a
>> typo:
> 
> Ah.  You are correct: I completely missed Rolf's point.
> 
> Fixed now.

Two more typos (IMHO, I'm not a native English speaker):

"where not being recognized" -> "were not being recognized"

"that can causes incorrect results" -> "that can cause incorrect results"

[sqlite] minor documentation typo

[Wolfgang Enzinger]

minor documentation typo here: https://www.sqlite.org/rbu.html

>All RBU updates us the "OR ROLLBACK" constraint handling mechanism.<
should obviously be
>All RBU updates use the "OR ROLLBACK" constraint handling mechanism.<

Wolfgang

[sqlite] storing coordinate pairs efficiently?

[Wolfgang Enzinger]

OK, here are a few test results ... 350,000 points with no additional data
... timings with all data in cache and 17 records selected:

#1
CREATE INDEX "pdata_spatial_index_x" ON "pdata"("gx");
CREATE INDEX "pdata_spatial_index_y" ON "pdata"("gy");
file size 24,200 KB, query time ~6.3 ms

#2
CREATE INDEX "pdata_spatial_index_x" ON "pdata"("gx","gy");
CREATE INDEX "pdata_spatial_index_y" ON "pdata"("gy","gx");
file size 27,360 KB, query time ~3.3 ms

#3
CREATE VIRTUAL TABLE "pdata_Shape_Index" USING rtree("IndexedObjectId"
INTEGER,"MinGX" DOUBLE,"MaxGX" DOUBLE,"MinGY" DOUBLE,"MaxGY" DOUBLE);
file size 32,064 KB, query time ~1.3 ms

Now that is something to balance ... I'll probably go with the rtree here
although it may look a bit like a misuse. :-)

Wolfgang

[sqlite] storing coordinate pairs efficiently?

[Wolfgang Enzinger]

Simon, Gunter,

thanks for your input. I also noticed that an index on (gx,gy) would help on
covering index searches, however as you both noticed as well there are more
fields in this table. But wait - probably for the first step (which points
are located in the region in question anyway?) it might well be enough to
know the coordinates and the object ID, the second step (user needs to know
more about a certain point in this subset of points) could probably a
separate query in many situations. I'll have to investigate this in more depth.

Thanks again! Wolfgang

[sqlite] storing coordinate pairs efficiently?

[Wolfgang Enzinger]

Dear group,

since a couple of years now, I'm using SQLite for storing graphic objects
like polygons and lines, with great success. Using the rtree extension
makes spatial queries very efficient.

In my lastest project, however, these objects are just single points
(coordinate pairs). As I understand it, rtree isn't the appropriate tool
here because it's for range queries. Of course, I could store every
coordinate twice in an rtee index, thus simulating ranges (with a width of
zero). However, I guess that would be a waste of space with probably no benefit.

So I tried a few things. The table definition looks like this:

CREATE TABLE "pdata"(
  "gx" DOUBLE NOT NULL, -- x coordinate
  "gy" DOUBLE NOT NULL, -- y coordinate
  "ID" INTEGER PRIMARY KEY NOT NULL
  /* a couple more fields here */
)

Then I established an index over gx and gy:

CREATE INDEX "pdata_spatial_index" ON "pdata"("gx","gy");

EQP shows that this index is used (not surprisingly) for every spatial query
like:

SELECT * FROM pdata WHERE gx BETWEEN 21 AND 22 AND gy BETWEEN 20
AND 39

However, EQP also shows that it is only used with the value of gx, not gy:

SEARCH TABLE pdata USING INDEX pdata_spatial_index (gx>? AND gx SEARCH TABLE pdata USING INDEX pdata_spatial_index_x (gx>? AND gx SEARCH TABLE pdata USING INDEX pdata_spatial_index_y (gy>? AND gy

[sqlite] minor documentation typo

[Wolfgang Enzinger]

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] Mozilla wiki 'avoid SQLite'

[Wolfgang Enzinger]

Am Sun, 14 Jun 2015 21:01:33 -0400 schrieb Stephen Chrzanowski:

> Its like going back to Visual Basic and including the
> interpreter in your "compiled" application with your source code basically
> copy/pasted right into the final EXE output (Encrypted/obfuscated/whatever).

Off topic indeed, but JFTR: Visual Basic never worked that way.

Wolfgang

[sqlite] minor documentation flaw

[Wolfgang Enzinger]

In https://sqlite.org/lang_expr.html, the anchor



appears twice. Obviously the scond occurence should be



Wolfgang

[sqlite] Optimization Opportunity?

[Wolfgang Enzinger]

Am Sun, 8 Mar 2015 14:06:51 +0100 schrieb E.Pasma:

> Actually query one appears slightly faster,
> Searching the PK index is faster as that is always a COVERING index.

I was under the impression that the opposite is true, but I wasn't sure 
about that.

>  From the secunsary indexes only a part oh the key is used.
> Note there is not much use on adding PK as second column in the  
> additional indexes. It is there anyway a a pointer to the row.

You're right, that index doesn't make much sense; in my real application it 
looks different, what I was showing here was just an example (one that was 
not very well thought of, obviously).

> I agree that it is strange that the execution plan for the two queries  
> is different, After EXISTS the optimizer might ignore the expression  
> in the select part of the sub-query. And Query one looks better as it  
> soes not mention any column names. Personally I'd write SELECT NULL  
> instead of SELECT *.

I prefer "SELECT 1 ...", like in Gunter's post. But that's a matter of 
taste, of course.

Well, my actual point was that the query planner seems to unnecessarily 
visit the table row in order to read a column value that will be discarded 
lateron anyway, and that this could probably be optimized out 
automatically. But my point is obsolete of course when the way it is right 
now is the faster one. Then again, it's not quite clear why this very 
strategy is *not* chosen when "SELECT 1 ..." or similar is being used. Not 
a big deal indeed, just curious.

> If speed matters instead of EXIST you can use IN and a list sub-query.  
> This is superfast now:
> 
> SELECT a1 FROM a WHERE a1 in (SELECT b.a1 FROM b INNER JOIN c  
> USING(b1) WHERE c.c1=222);
> 
> 0|0|0|SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?)
> 0|0|0|EXECUTE LIST SUBQUERY 1
> 1|0|1|SEARCH TABLE c USING INTEGER PRIMARY KEY (rowid=?)
> 1|1|0|SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)

I avoided IN for a long time, but that must originate from the time when I 
mostly used Jet (Access) file databases ... with SQLite, it's really fast 
indeed.

Wolfgang

[sqlite] Optimization Opportunity?

[Wolfgang Enzinger]

Hi dev team,

not sure if this is actually a useful hint, but ...

CREATE TABLE a(a1 INTEGER PRIMARY KEY);
INSERT INTO a VALUES (1),(2),(3);
CREATE TABLE b(a1 INTEGER REFERENCES a(a1),b1 INTEGER PRIMARY KEY);
INSERT INTO b VALUES (1,11),(2,22),(3,33);
CREATE UNIQUE INDEX b_ui ON b(a1,b1);
CREATE TABLE c(b1 INTEGER REFERENCES b(b1),c1 INTEGER PRIMARY KEY,c2 TEXT);
INSERT INTO c VALUES (11,111,'a'),(22,222,'b'),(33,333,'c');
CREATE UNIQUE INDEX c_ui ON c(b1,c1);
ANALYZE;

Query 1:

EXPLAIN QUERY PLAN
SELECT a1 FROM a WHERE EXISTS(SELECT * FROM b INNER JOIN c USING(b1) WHERE 
b.a1=a.a1 AND c.c1=222);

selectidorder   fromdetail
0   0   0   SCAN TABLE a
0   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 1
1   0   1   SEARCH TABLE c USING INTEGER PRIMARY KEY (rowid=?)
1   1   0   SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)

Query 2:

EXPLAIN QUERY PLAN
SELECT a1 FROM a WHERE EXISTS(SELECT c1 FROM b INNER JOIN c USING(b1) WHERE 
b.a1=a.a1 AND c.c1=222);

selectidorder   fromdetail
0   0   0   SCAN TABLE a
0   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 1
1   0   0   SEARCH TABLE b USING COVERING INDEX b_ui (a1=?)
1   1   1   SEARCH TABLE c USING COVERING INDEX c_ui (b1=?)

Note that the only difference between the two is "SELECT *" vs. "SELECT c1" 
within the EXISTS-block. The result is the same in both cases, however the 
second query uses COVERING INDEXes which should be more efficient (as far 
as I know).

HTH; and sorry for the noise if not.

Wolfgang

Re: [sqlite] DLL WITH SAMPLE CODE FOR VISUAL BASIC 6 TO ACCESS AN SQLITE3 DATABASE

[Wolfgang Enzinger]

Sang Wong  writes:

> Do you have a .DLL LIBRARY and 
> SAMPLE CODE showing how to use VISUAL BASIC 6 with an sqlite3 
> DATABASE? Thank You.

Check this out - highly recommended:
http://www.vbforums.com/showthread.php?726437-VB6-SQLite-DB-Demos-%28based-on-the-RichClient-Framework%29

Wolfgang

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

Re: [sqlite] R-Tree Storage Optimization for Points

[Wolfgang Enzinger]

Dan Kennedy  writes:

> Probably not. The CREATE TABLE code above actually creates a 
> 1-dimensional r-tree with deceptive column names. Column "y" contains 
> the maximum value for the first dimension:
> 
> SQLite version 3.8.5 2014-06-19 12:34:33
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> CREATE VIRTUAL TABLE abc USING rtree(id,x,y);
> sqlite> INSERT INTO abc VALUES(NULL, 20, 10);
> Error: constraint failed
> sqlite>

I stand corrected. Should have tried this before:

sqlite> INSERT INTO abc VALUES(2,30,20);
Error: constraint failed 

Note to self: r-tree is about *ranges* in 1 to 5 dimensions.

Wolfgang


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

Re: [sqlite] R-Tree Storage Optimization for Points

[Wolfgang Enzinger]

Mohit Sindhwani  writes:

> However, since these are points that are stored in the table, x1=x2 and 
> y1=y2 when we do the insertion.  As a former embedded systems engineer, 
> this feels like a waste since I can see that we are inserting exactly 
> the same value into the table.
> 
> INSERT into data_rtree(1000, 10, 5, 10, 5);
> INSERT into data_rtree(1000, 17, 1, 17, 1);
> and so on.
> 
> Is there a way that we could optimize the module so that we don't need 
> to store the same value twice?

Not sure why you think you have to store those point coordinates twice.

This works:

sqlite> CREATE VIRTUAL TABLE abc USING rtree(id,x,y);
sqlite> INSERT INTO abc VALUES(1,20,30);
sqlite> SELECT id FROM abc WHERE x>=10 AND x<=30 AND y >=20 AND y<=40;
1
sqlite> SELECT id FROM abc WHERE x>=40 AND x<=50 AND y >=40 AND y<=50;
sqlite>

HTH,
Wolfgang

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

Re: [sqlite] Is sqlite case-sensitive?

[Wolfgang Enzinger]

Am Sun, 18 May 2014 19:15:18 +0200 schrieb RSmith:

> But of course  What kind of syntactical correctness can you hope to
> check without a connection?

[...]

Completely agreed. I was just referring to the OP who asked for an "API to
validate a SQL statement, either in the context of the current connection
(validate also table/column/db names), or without context (just validate
syntax, e.g. that it can be parsed)". As you said, the latter couldn't be
more than keyword checking anyway.

Wolfgang

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

Re: [sqlite] Is sqlite case-sensitive?

[Wolfgang Enzinger]

>>> names), or without context (just validate syntax, e.g. that it can be
>>> parsed)?
>>>
>> I am asking about this API since I think I remember seeing it once, but
>> can't find it now
>>
> 
> i _think_ what you want is:
> 
> http://sqlite.org/c3ref/complete.html

I don't think so, because this function essentially checks "if [the
statement] ends with a semicolon token". Furthermore, "these routines do
not parse the SQL statements thus will not detect syntactically incorrect
SQL".

As Igor says, http://sqlite.org/c3ref/prepare.html would be appropriate.
However, a database connection is required for this.

Wolfgang

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

Re: [sqlite] how to use sqlite in excel vba?

[Wolfgang Enzinger]

Larry Brasfield  writes:

> Assuming you actually do need to compile something, (identity of which 
> you provide few good clues), you might consider a package I had good 
> luck with, a SQLite wrapper called 'Litex', available at
>https://www.assembla.com/wiki/show/litex

The download links on this page don't seem to work properly ...?

My recommendation for a SQLite COM wrapper would be this:
http://www.vbrichclient.com

Wolfgang

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

Re: [sqlite] The next-generation query planner

[Wolfgang Enzinger]

Richard Hipp  writes:

> The combination of schema, sqlite_stat1, and query is normally sufficient,
> yes.
> 
> Can you change (and shorten) the table and index and column names to
> obscure their meaning and origin, and give us written permission to include
> your case in the published test suite for SQLite?  That would be even
> better.

Done; please find the sample database here:
www.enzinger.net/archives/ngqptest.zip

No problem with using it in the published test suite.

The query in question is in VIEW ngqp_test. As said, it demonstrates a
dramatic performance regression since version 3.7.15 whenever sqlite_stat1
is absent.

Thanks,
Wolfgang

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

Re: [sqlite] The next-generation query planner

[Wolfgang Enzinger]

Richard Hipp  writes:

> > I try to compile with SQLITE_HAS_CODEC defined I get errors saying that
> > sqlite3_key_v2 and sqlite3_rekey_v2 functions are undefined. Are these new
> > APIs?
> >
> 
> Yes.  You need to use your login and password to download the latest SEE
> source - the latest SEE sources on the trunk of the SEE source tree, not
> the latest SEE release.  The latest SEE has been updated to work with the
> NGQP.

Thanks for your confirmation.

SEE is not an option because for encryption I'm using the routines in the
ADO.NET 2.0 Data Provider.

Will do my tests with un-encrypted data for now.

Wolfgang

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

Re: [sqlite] The next-generation query planner

[Wolfgang Enzinger]

Richard Hipp  writes:

> Amalgamations for the latest SQLite containing the NGQP are available from
> the http://www.sqlite.org/draft/download.html page.  This should be a
> drop-in replacement for the amalgamation you are currently using.  There
> are no new APIs or compiler flags to fuss with.  Everything works exactly
> as it did before, only a little faster.  You should be able to simply
> recompile and end up with an application that (hopefully) runs a little
> faster.

I would like to run tests with some of my encrypted databases, however when
I try to compile with SQLITE_HAS_CODEC defined I get errors saying that
sqlite3_key_v2 and sqlite3_rekey_v2 functions are undefined. Are these new APIs?

Wolfgang

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

Re: [sqlite] dramatic performance drop somewhere between 3.7.09.0 and 3.7.17.0

[Wolfgang Enzinger]

Richard Hipp  writes:

> However, once we get the new query planner running and ready to beta-test,
> we really, really would appreciate you giving it a spin and letting us know
> if you encounter any problems with it.

Alright, will do.

Thanks for the great work!

Wolfgang

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

[sqlite] dramatic performance drop somewhere between 3.7.09.0 and 3.7.17.0

[Wolfgang Enzinger]

Hallo list,

I detected a dramatic performance drop with a certain query which occurred
somewhere between versions 3.7.09.0 and 3.7.17.0. Now this is not a real
showstopper because I also noticed than an ANALYZE cures the issue in the
latter version immediately. However, the drop without ANALYZE is really
dramatic (~5 secs. in 3.7.09.0, but no result even after a couple of hours
in 3.7.17.0).

Is this of any interest? If so, I can make a showcase which demonstrates the
problem (with obfuscated data). EXPLAIN QUERY PLAN shows essential
differences also between the two versions, although every SEARCH TABLE
operation uses an index in both cases.

While we're at it: is there a place where I can download "historic"
precompiled binaries? If so, I could probably tell more exactly where the
drop was introduced.

Wolfgang

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

Re: [sqlite] New preview snapshots for 3.7.17

[Wolfgang Enzinger]

Richard Hipp  writes:

> 
> New amalgamation snapshots for the upcoming 3.7.17 release can be found at
> 
>  http://www.sqlite.org/draft/download.html
> 
> Please try this snapshot in your applications and report success or
> failure, either to this mailing list or directly to me.  Thanks.

I just ran ~150 tests without any issue.

Wolfgang
 
> --
> D. Richard Hipp
> drh@...
> 




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

[sqlite] result set not properly ordered in SQLite 3.7.15.1

[Wolfgang Enzinger]

Hi,

I have this query:

SELECT DISTINCT ask_fuo.id FROM ask_fuo INNER JOIN ask_art USING (id) INNER
JOIN ask_art_personen USING (ora_nachweis_id) WHERE
(ask_art_personen.code IN('M0E9','M0GC','M0YR')) AND ask_art_personen.bezug
IN('B','D','S')
GROUP BY ask_fuo.id ORDER BY ask_fuo.id;

A sample database (19 KB zipped) for this query can be downloaded here:

http://www.enzinger.net/archives/sqlitetest.zip

Now testing this with SQLite 3.7.15.1 I noticed that the result set is not
properly ordered, despite the ORDER BY clause. My last tests with 3.7.13
returned an ordered result. Can anyone confirm this? Not quite sure if I'm
missing something obvious though ...

On a minor note, I also noticed that with 3.7.13 the DISTINCT clause could
be omitted, GROUP BY was enough to get each ID only once. With 3.7.15.1,
returned IDs are no longer unique with my data when I omit the DISTINCT
clause. I find both behaviours acceptable, but since I didn't find a
related hint in the release notes, I'm not sure if this change was
intended.

Any input welcome!

Greetings from Munich,
Wolfgang

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

Re: [sqlite] ATTACH DATABASE question

[Wolfgang Enzinger]

Am Thu, 27 Sep 2012 20:31:36 -0700 schrieb
john_prov...@yahoo.com:

> I’m confused about how to attach a database file to an existing 
> database. I open my main database ok, then to attach the second
> database, I try to execute the following SQL:
>
> ATTACH DATABASE ‘c:\mydata\my_attach_database.db’ AS ‘attached_db’

Use simple single quotes around the filename, and don't quote the alias
name at all:

ATTACH DATABASE 'c:\mydata\my_attach_database.db' AS attached_db;

HTH,
Wolfgang

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

Re: [sqlite] Sqlite help

[Wolfgang Enzinger]

Am Mon, 4 Jul 2011 16:10:10 -0700 (PDT) schrieb ceekayCK:

> It was quite easy to export to csv (using excel) :) 
> Now i have in one column Artist , and in second column Title

Fine. :-)

> Ok So i use sqlite Manager in Firefox

Unfortunately, I'm not too familiar with SQLite Manager.

> What should I do next?

Next I would fire up the CLI (type "sqlite3.exe mydb.db" at the command
prompt if you're using Windows, assuming your database is named mydb.db),
then type ".import docdata.csv mytable" or whatever your csv file and
target table are named (type ".help" for further info).

I never used that function before but I'm sure it will get you onto the
right track.

Good luck,
Wolfgang

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

Re: [sqlite] Sqlite help

[Wolfgang Enzinger]

Am Mon, 4 Jul 2011 15:39:30 -0700 (PDT) schrieb ceekayCK:

> If i changed .doc file to .txt or csv,  will this help?
> Or put all data from .doc to excel, and then split Artist and title to
> separate columns and export to csv?

*If* you can manage a reasonable export to .csv (using Excel, if needed, or
not), then, yes, that would be a way to go IMHO. Lookup the .import command
in the CLI (command line interface).

But I doubt it's possible to convert a .doc file into a useful .csv file.
However, as said before, that depends a lot on the specific .doc file *and*
your database structure.

Wolfgang

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

Re: [sqlite] Sqlite help

[Wolfgang Enzinger]

Am Mon, 4 Jul 2011 14:45:07 -0700 (PDT) schrieb ceekayCK:

> unfortunately send me a .doc file

You're at a loss there. Your friend could as well print this file and send
you the printer output, essentially that would make no difference.

A .doc file is basically a load of printer instructions, but generally it
doesn't hold any structured data. The latter, however, is what a database
needs and is made for.

Depending on how this specific .doc file (and your database) looks like,
there *may* be some things one could try, but they are all very time
consuming and require programming skills.

>Is there a magic command

No. Neither in SQLite, nor in any other database system.

Wolfgang

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

Re: [sqlite] Need to be able to rename attributes

[Wolfgang Enzinger]

Am Sun, 15 May 2011 17:10:53 -0500 schrieb Mr. Puneet Kishor:

> sqlite doesn't support changing the name of a table column (and, neither you 
> nor
> your user should be doing this -- there is something strange with your app
>requirements). That said, you can "rename" a column by creating a new table 
>with
>the new column definitions and copy data from the old table to the new table.
> 
> CREATE TABLE new_table (id, eventName, dateAdded);
> INSERT INTO new_table (id, eventName, dateAdded) SELECT id, eventName, date 
> FROM old_table;

Alternatively, one can rename the old_table ...

ALTER TABLE old_table RENAME TO old_table_raw;

... and then create a view with the name of the old_table:

CREATE VIEW old_table AS SELECT old_col_name AS new_col_name FROM
old_table_raw;

After that, "SELECT new_col_name FROM old_table" will return the same data
as "SELECT old_col_name FROM old_table" would have returned before the
renaming operation, but with the new column name, without the need to copy
all the data and thus avoiding database inflation.

Wolfgang

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

[sqlite] SQLite, Java and Custom Collation

[Wolfgang Enzinger]

Hello,

a friend of mine needs to read from an SQLite database that I made. He
needs to do it in Java, and what's special about the database is that it
contains some indexes which are based on a custom collation (German
umlaut). 

Does anyone know of a recommendable java library (JDBC or else) that allows
the registration of a custom collation? So far I didn't find any.

Related question, while we're at it: how would one register custom
collations in an ODBC environment, is that possible?

Thanks,
Wolfgang

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

Re: [sqlite] errors after sqlite3_step

[Wolfgang Enzinger]

Am Fri, 01 Oct 2010 14:11:51 -0700 schrieb Dave Dyer:

> There ought to be a definitive list of what errors can occur
> after sqlite3_step, and which (if any) are recoverable.

IMHO that's impossible, at least because SQLite allows the definition of
user defined functions. Now such a function may process the first few rows
quite fine, but will raise an error with the next result row because it
encounters a NULL value where an integer is expected, for instance.

At the time sqlite_prepare() is executed, such things cannot be foreseen.

Wolfgang

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

Re: [sqlite] Is the absence of msvcrt.dll a known issue with SQLite Windows 2000

[Wolfgang Enzinger]

Am Sat, 5 Jun 2010 08:01:31 +0100 schrieb Frank Church:

> On checking the sqlite3.dll docs it states the only additional requirement
> for it is msvcrt.dll. Is that known to be a problem, or could it be
> something else? I just want to know if there are some other known issues
> between sqlite3.dll and Windows 2000.

SQLite runs fine on my Windows 2000 systems, without any issues.

Wolfgang

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

Re: [sqlite] Import a other database. Search for the fastes way

[Wolfgang Enzinger]

Am Sat, 9 Jan 2010 22:00:04 +0100 schrieb Carsten Giesen:

[Transactions]

> Thanks. That's rock 
> Greate.

Next, play with some PRAGMA settings to achieve even more speed,
especially:

PRAGMA cache_size = Number-of-pages;
PRAGMA page_size = bytes;

Also (it's not clear whether you do this already) use prepared statements
(sqlite3_prepare*) with parameters (sqlite3_bind_*) instead of building a
new SQL string for each row to be inserted.

HTH,
Wolfgang

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

Re: [sqlite] ICU collation

[Wolfgang Enzinger]

Am Sun, 20 Dec 2009 19:24:58 +0100 schrieb Wolfgang Enzinger:

> I'm pretty sure they don't query their database for " = 'schön'" but for 
> "LIKE 'schön'".

Correction:

I'm pretty sure they don't query their database for " = 'schoen'" but for 
"LIKE 'schoen'".

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

Re: [sqlite] ICU collation

[Wolfgang Enzinger]

Am Sun, 20 Dec 2009 14:45:15 +0100 schrieb Sylvain Pointeau:

> if you go on http://www.dict.cc and type "schoen" then it will give you
>the answers "schön" ...

I'm pretty sure they don't query their database for " = 'schön'" but for 
"LIKE 'schön'". And that's exactly what it is: like, but not equal to.

Don't know about ICU though since I rolled my own DE collation and LIKE 
operator ...

Wolfgang

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

Re: [sqlite] Cannot ATTACH a database that contains a VIEW

[Wolfgang Enzinger]

Pavel Ivanov  writes:

> Are you referencing "main" in your view explicitly?

Bingo, that's in fact what I did.

> If so then don't do it.

Followed your advice and it works now, thanks!

Looks like I was a bit *too* explicit here ... ;-)

Wolfgang

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

[sqlite] Cannot ATTACH a database that contains a VIEW

[Wolfgang Enzinger]

Hi,

it looks like I cannot ATTACH a database that contains a VIEW:

sqlite> ATTACH DATABASE 'G:\Project\ASK_ORA\ask.db' AS dbsrc;
SQL error: malformed database schema (ask_art) - view ask_art cannot
reference objects in database main
sqlite>

Obviously SQLite tries to apply the view to tables in the "current"
database, not in the attached database.

Is there any workaround for this? Maybe there is a trick to define the
view in a different way in order to make clear that it relates to the
database it's located in, and not to any database whichever is "main"
currently? Or maybe a way to make SQLite simply "skip" (ignore) any
view in the to-be-attached database?

Any pointers much appreciated!

Wolfgang

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

Re: [sqlite] PRAGMA journal_mode

[Wolfgang Enzinger]

Am Sun, 11 Oct 2009 10:45:57 -0500 schrieb Jay A. Kreibich:

>   The docs for "PRAGMA journal_mode" are pretty clear on this: it
>   returns the current mode (i.e. after the command is run) because it may
>   not be able to change to the requested mode.

Thanks. That made me realize that my local copy of the docs is a little
outdated (3.6.7) ... fixed now. :-)

Wolfgang

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

Re: [sqlite] Sqlite reading all column data on selects.

[Wolfgang Enzinger]

Am Thu, 24 Sep 2009 14:18:33 -0400 schrieb D. Richard Hipp:

> When autovacuum=FULL or autovacuum=INCREMENTAL and SQLite needs to  
> seek to the end of a long chain of overflow pages, it attempts to use  
> the "pointer map pages" that are available in these modes to locate  
> the pages without actually reading the prior pages from the disk.  Key  
> word:  "attempts".  There is no guarantee of success.  But if the  
> database is not too badly fragmented, it will usually succeed.
> 
> When autovacuum=OFF, then pointer map pages are not availble and  
> SQLite is compelled to read all prior pages when seeking to the end of  
> an overflow chain.

That's interesting. So that means that if I create and fill an SQLite
database that is intended for later read-only access, I get an additional
performance boost with autovacuum=OFF as long as the database is created
and filled with data, and lateron I switch to autovacuum=FULL (or
INCREMENTAL) when I'm done? I.e. the latest after an explicit VACUUM when
I'm finished writing to the database, there would be no fragmentation
present, and read access would be perfectly optimized with any autovacuum
pragma other than OFF?

Thanks,
Wolfgang

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

Re: [sqlite] SQLITE : Constraint question

[Wolfgang Enzinger]

> Date: Sun, 15 Mar 2009 23:17:04 -0400
> From: "Griggs, Donald" <donald.gri...@allscripts.com>
> Subject: Re: [sqlite] SQLITE : Constraint question

>> BTW, is there a document that explains in more detail what operations
>> the CHECK constraint is capable of?

[...]

> Were you looking for something beyond the documentation under "CREATE
> TABLE"?
> 
>  Column constraints:
>  http://www.sqlite.org/syntaxdiagrams.html#column-constraint
> 
>  Table constraints:
>  http://www.sqlite.org/syntaxdiagrams.html#table-constraint

Well, all that these docs are saying concerning CHECK constraints is:
"CHECK(expr)". So I looked up "expr" and there is indeed a lot of useful
information, but I didn't see the typeof() function mentioned, for
instance. Actually I wonder how to find out about this useful function if
not by reading (and understanding ;-) the source code.

>  Conflict clause:
>  http://www.sqlite.org/syntaxdiagrams.html#conflict-clause
> 
>  Foreign key clause (enforceable with preprocessing to create triggers):
>  http://www.sqlite.org/syntaxdiagrams.html#foreign-key-clause

Useful info, too, but not with respect to the CHECK statement (at least
right now i can't see it).

I understand that it's up to everybody to help improve the docs. So, as far
as "strict affinity" is concerned, I'm planning on opening a ticket (that's
how it works, right?) requesting a documentation improvement if there are
no objections. Regarding the CHECK statement, however, I think that there
is a gap that should be filled by someone with more insight than I have ...

BTW: My previous post was my first post to this list, and I forgot to say
Hello to everybody, and most of all: Hats off to Mr. Hipp, great work! :-)

Best,
Wolfgang Enzinger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE : Constraint question

[Wolfgang Enzinger]

> Date: Wed, 11 Mar 2009 19:10:26 -0600
> From: Dennis Cote <r.dennis.c...@gmail.com>
> Subject: Re: [sqlite] SQLITE : Constraint question

[...]

> However, you can explicitly add the equivalent constraints to your table 
> definitions if you really want them. For example:
> 
> create table t (
> a varchar(15) check (length(a) <= 15),
> b integer check (typeof(b) = 'integer')
> );

That kind of CHECK constraint is actually a cunning way to enforce strict
affinity for a column, isn't it?

If so, I think that paragraph 6 (Other Affinity Modes) in
http://sqlite.org/datatype3.html should be updated or completed
accordingly. AFAIK there are no plans to implement the modes "strict
affinity" and "no affinity" that are mentioned there ... but maybe I missed
something?

BTW, is there a document that explains in more detail what operations the
CHECK constraint is capable of?

Best,
Wolfgang Enzinger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users