Re: [sqlite] Sqlite RTree nearest neighbour

[Clemens Ladisch]

skywind mailing lists wrote:
> I hoped that somebody already tried to implement a nearest neighbor
> algorithm.

Typically, objects are not axis-aligned rectangles, and the R-tree is
just an index based on the bounding boxes.  Computing the (nearest)
distance would require the actual geometries.

> Is the format of the shadow tables somewhere documented or do I have
> to analyze the source code?

rtree.c says:

** Database Format of R-Tree Tables
** 
**
** The data structure for a single virtual r-tree table is stored in three
** native SQLite tables declared as follows. In each case, the '%' character
** in the table name is replaced with the user-supplied name of the r-tree
** table.
**
**   CREATE TABLE %_node(nodeno INTEGER PRIMARY KEY, data BLOB)
**   CREATE TABLE %_parent(nodeno INTEGER PRIMARY KEY, parentnode INTEGER)
**   CREATE TABLE %_rowid(rowid INTEGER PRIMARY KEY, nodeno INTEGER)
**
** The data for each node of the r-tree structure is stored in the %_node
** table. For each node that is not the root node of the r-tree, there is
** an entry in the %_parent table associating the node with its parent.
** And for each row of data in the table, there is an entry in the %_rowid
** table that maps from the entries rowid to the id of the node that it
** is stored on.
**
** The root node of an r-tree always exists, even if the r-tree table is
** empty. The nodeno of the root node is always 1. All other nodes in the
** table must be the same size as the root node. The content of each node
** is formatted as follows:
**
**   1. If the node is the root node (node 1), then the first 2 bytes
**  of the node contain the tree depth as a big-endian integer.
**  For non-root nodes, the first 2 bytes are left unused.
**
**   2. The next 2 bytes contain the number of entries currently
**  stored in the node.
**
**   3. The remainder of the node contains the node entries. Each entry
**  consists of a single 8-byte integer followed by an even number
**  of 4-byte coordinates. For leaf nodes the integer is the rowid
**  of a record. For internal nodes it is the node number of a
**  child page.

For a simple search algorithm, see .


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

Re: [sqlite] Severe performance degradation between 3.8.4.3 and

[Eduardo Morras]

On Fri, 22 Aug 2014 19:14:02 +0200
"Mario M. Westphal"  wrote:

> Thanks, Richard
> 
>  
> 
> After swapping back to the latest SQLite version and running an
> Analyze on the sample databases, performance is up to the same level
> as before (maybe even a bit faster). Very good.
> 
>  
> 
> I will send out a recommendation to my users to run the weekly
> diagnostics routine immediately to restore performance.

Next time you can do it automatically. Set pragma user_version on db to your 
app version, and on open db check if it's current or not, and run analyze or 
make schema changes or whatever you want and update user_version.


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


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

Re: [sqlite] Sqlite RTree nearest neighbour

[skywind mailing lists]

Hello,

I hoped that somebody already tried to implement a nearest neighbor algorithm. 
Is the format of the shadow tables somewhere documented or do I have to analyze 
the source code?

Regards,
Hartwig

Am 22.08.2014 um 02:58 schrieb Richard Hipp :

> On Thu, Aug 21, 2014 at 8:54 PM, Peter Aronson  wrote:
> 
>>1. You would need to access SQLite's R-Tree "shadow" tables
>> (xx_node, xx_parent, xx_rowid) directly in to perform the traversals
>> required by all of the algorithms -- I don't know if this is officially
>> supported by SQLite's developers, or if these tables are guaranteed not to
>> change;
>> 
> 
> The format of the shadow tables will not change in ways that would break
> older versions of SQLite.
> 
> -- 
> 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] Severe performance degradation between 3.8.4.3 and

[Mario M. Westphal]

Thanks, Richard

 

After swapping back to the latest SQLite version and running an Analyze on the 
sample databases, performance is up to the same level as before (maybe even a 
bit faster). Very good.

 

I will send out a recommendation to my users to run the weekly diagnostics 
routine immediately to restore performance.

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

Re: [sqlite] SELECT ... GROUP BY: Bug or misunderstanding?

[Martin Engelschalk]

Hi Christoph,

the id column does not appear in an aggregate function and also not in 
group by.


Your statement uses 'GROUP BY Name' and so returns exactly one row per 
name. If there are several rows with the same name, the ID of your 
result is from one of these rows. The appropriate documentation is


"Each expression in the result-set is then evaluated once for each group 
of rows. If the expression is an aggregate expression, it is evaluated 
across all rows in the group. Otherwise, it is evaluated against a 
single arbitrarily chosen row from within the group. If there is more 
than one non-aggregate expression in the result-set, then all such 
expressions are evaluated for the same row. " from here 
http://www.sqlite.org/lang_select.html#resultset


The keyword here is "arbitrarily chosen".

So, you can not expect to get the same id every time even if you do not 
change the sqlite version.


Hope this helps
Martin

Am 22.08.2014 15:19, schrieb Christoph Wiedemann:

SELECT Id, Name, MIN(Score) AS Score FROM Test GROUP BY Name ORDER BY Score


--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

[sqlite] SELECT ... GROUP BY: Bug or misunderstanding?

[Christoph Wiedemann]

Hello,
 
I'm using sqlite over the python sqlite3 module shipped with the standard 
distribution. Being not an SQL expert at all, I stumbled over an inconsistency 
with a SELECT  GROUP BY statement. The following python script might be 
used to reproduce:
 
<<<
import sqlite3
print("sqlite_version=",sqlite3.sqlite_version)
db = sqlite3.connect(":memory:")
c = db.cursor()
c.execute("CREATE TABLE Test(Id INTEGER PRIMARY KEY, Name TEXT, Score INTEGER)")
c.execute("INSERT INTO Test(Name,Score) VALUES('d1',100)")
c.execute("INSERT INTO Test(Name,Score) VALUES('d1',99)")
c.execute("INSERT INTO Test(Name,Score) VALUES('d1',98)")
c.execute("INSERT INTO Test(Name,Score) VALUES('d2',101)")
c.execute("INSERT INTO Test(Name,Score) VALUES('d2',102)")
a = c.execute("SELECT * FROM Test").fetchall()
print(a)
a = c.execute("SELECT Id, Name, MIN(Score) AS Score FROM Test GROUP BY Name 
ORDER BY Score").fetchall()
print(a)
>>>

Using the default sqlite.dll of the python 3.2.x distribution, the output is as 
follows:
 
sqlite_version= 3.7.4
[(1, 'd1', 100), (2, 'd1', 99), (3, 'd1', 98), (4, 'd2', 101), (5, 'd2', 102)]
[(3, 'd1', 98), (5, 'd2', 101)]
^^^
Please note the '5' in the output of the select ... group by statement. 
Exchanging the sqlite.dll with a newer version, I get the following output:
 
sqlite_version= 3.8.6
[(1, 'd1', 100), (2, 'd1', 99), (3, 'd1', 98), (4, 'd2', 101), (5, 'd2', 102)]
[(3, 'd1', 98), (4, 'd2', 101)]
    ^^^
Question: Did I encounter a bug in the sqlite version 3.7.4, or are both 
outputs correct and my understanding of the GROUP BY semantic is wrong?
 
Thanks in advance!
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] does Sqlite Extension Encryption work with HTML5 and javascript application

[pka...@mapcom.com]

Hi,

I am looking for javascript/jquery library  to encrypt/decrypt SQLite
database. Does Sqite Encryption Extension support  HTML5 applications ?

Thanks,
Prava



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/does-Sqlite-Extension-Encryption-work-with-HTML5-and-javascript-application-tp77431.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] User-visible doc anchors [WAS: Severe performance degradation]

[Dominique Devienne]

On Fri, Aug 22, 2014 at 11:43 AM, Kevin Benson 
wrote:

> On Fri, Aug 22, 2014 at 3:13 AM, Dominique Devienne 
> > [...] Could there be a way to easily get a direct URL to each section?
> [...]
>

> Meanwhile, you can mark some text on a linked page, in yellow, like this:
>
> http://www.sqlite.org/mark/optoverview.html?Without+the*ANALYZE


Good point. And thanks for the tip.

Then again, the fact that one needs to scroll down several pages because
the highlighted text is "below the fold" speaks volumes in favor on my
request.

And ideally, one can both #anchor?Highlight+Text --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] User-visible doc anchors [WAS: Severe performance degradation]

[Kevin Benson]

On Fri, Aug 22, 2014 at 3:13 AM, Dominique Devienne 
wrote:

> On Thu, Aug 21, 2014 at 8:44 PM, Richard Hipp  wrote:
>
> > Additional discussion can be found at:
> >
> >   http://www.sqlite.org/optoverview.html
> >   http://www.sqlite.org/queryplanner-ng.html
>
>
> Richard,
>
> The SQLite doc is part of the reason I'm a fan of SQLite, but some of its
> pages can be rather long, with many sections ( sub-headers), and when
> wanting to send a link to a particular section, I don't see an easy way to
> get the proper #section anchor. When looking at the HTML source, I can see
> the anchors are there, but that's far from convenient.
>
> Could there be a way to easily get a direct URL to each section? Either via
> a TOC in each page, or the header being a link to itself, or when you hover
> on the header, a paragraph symbol appears that one can click to "jump" to
> the anchor?
>
> The doc being generated, I assume a simple change to the generator would be
> enough to get this across the whole doc? This would IMHO further improve
> the usability of the doc. Thank you for considering this change request.
> Best regards, --DD


Meanwhile, you can mark some text on a linked page, in yellow, like this:

http://www.sqlite.org/mark/optoverview.html?Without+the*ANALYZE

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Crash in sqlite3_mutex_try [Was: SQLite 3.8.6 beta]

[Jan Nijtmans]

2014-08-15 12:50 GMT+02:00 Jan Nijtmans :
> Looking at the function sqlite3_win32_is_nt()
> It should simply return 1 on any
> currently supported platform. Looking closely,
> it returns 0 on Windows RT, but who cares 
> (leaving the "why" as practice for the reader)

It looks like this is on its way to being corrected:
   

However, I don't think this will work on Win95/98/NT
(not that I really care ..). The reason: GetVersionExW()
will be called to check whether the kernal is NT-based,
but Win95/98/NT is not NT-based so it doesn't have
this function  ;-). Suggested solution:


Second-best suggested solution as patch below
(based on the winrt branch)

Regards,
  Jan Nijtmans

Index: src/os_win.c
==
--- src/os_win.c
+++ src/os_win.c
@@ -1323,17 +1323,17 @@
   **   kernel.
   */
   return 1;
 #elif defined(SQLITE_WIN32_GETVERSIONEX) && SQLITE_WIN32_GETVERSIONEX
   if( osInterlockedCompareExchange(_os_type, 0, 0)==0 ){
-#if defined(SQLITE_WIN32_HAS_WIDE)
+#if !defined(SQLITE_WIN32_HAS_ANSI)
 OSVERSIONINFOW sInfo;
 sInfo.dwOSVersionInfoSize = sizeof(sInfo);
 osGetVersionExW();
 osInterlockedCompareExchange(_os_type,
 (sInfo.dwPlatformId == VER_PLATFORM_WIN32_NT) ? 2 : 1, 0);
-#elif defined(SQLITE_WIN32_HAS_ANSI)
+#else
 OSVERSIONINFOA sInfo;
 sInfo.dwOSVersionInfoSize = sizeof(sInfo);
 osGetVersionExA();
 osInterlockedCompareExchange(_os_type,
 (sInfo.dwPlatformId == VER_PLATFORM_WIN32_NT) ? 2 : 1, 0);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] User-visible doc anchors [WAS: Severe performance degradation]

[Dominique Devienne]

On Thu, Aug 21, 2014 at 8:44 PM, Richard Hipp  wrote:

> Additional discussion can be found at:
>
>  http://www.sqlite.org/optoverview.html
>  http://www.sqlite.org/queryplanner-ng.html


Richard,

The SQLite doc is part of the reason I'm a fan of SQLite, but some of its
pages can be rather long, with many sections ( sub-headers), and when
wanting to send a link to a particular section, I don't see an easy way to
get the proper #section anchor. When looking at the HTML source, I can see
the anchors are there, but that's far from convenient.

Could there be a way to easily get a direct URL to each section? Either via
a TOC in each page, or the header being a link to itself, or when you hover
on the header, a paragraph symbol appears that one can click to "jump" to
the anchor?

The doc being generated, I assume a simple change to the generator would be
enough to get this across the whole doc? This would IMHO further improve
the usability of the doc. Thank you for considering this change request.
Best regards, --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users