Re: [sqlite] Get notified as soon as it's save to modify a db after sqlite3_update_hook() was triggered

2017-04-04 Thread Stadin, Benjamin 
Hi Peter,

Wrapping the bbox was solely for the purpose of additional (and optional) 
performance. At the moment, this column would be optional and used instead of 
the geopackage geometry in some situations (e.g. improve rtree indexing by 
avoiding to create the bbox for every geometry). The Geopackage geometry 
currently contains an envelope, next to the srid and various otrher things: 
http://www.geopackage.org/spec/#gpb_spec

Some of this can be improved size-wise and made leaned more towards existing 
standards (e.g. pack the envelope also as WKB).

I’ve just learned about requirements imposed by some aspects of the standard 
where unfortunately the srid needs to be part of the geometry. But still, the 
overhead of the current struct is significant, as well as redundant info about 
srid at other places as well (gpkg_contents, again in gpkg_geometry_columns and 
in the geometry itself). I’m actually trying to create a proposal for a few 
breaking changes for a version the, though probably chances are low it’s ever 
considered.

Though this discussion belongs to another user group now.

Regards
Ben

Am 04.04.17, 17:05 schrieb "sqlite-users im Auftrag von Peter Aronson" 
:

If you're creating GeoPackages with the F.3 RTREE Spatial Indexes 
extension, you do not "wrap" a bounding box. You need to define 5 
functions from SQL/MM -- ST_MinX, ST_MaxX, ST_MinY, ST_MaxY and 
ST_IsEmpty -- that take a geometry blob as input and return (for the 
first four) a floating point number or an integer value of 0 or 1 
(ST_IsEmpty). Now it is possible you might want to create some sort of 
side cache so you don't have to parse the geometry blob four times, but 
that is an implementation issue. The exact text of the triggers is 
specified by the standard and is not optional if you are using the 
extension. And you certainly could not use standard WKB values in the 
geometry columns without completely violating the GeoPackage standard.

Peter

On 4/3/2017 9:26 AM, Stadin, Benjamin wrote:
> Hi,
>
> Is there a hook which allows to get notified as soon as it’s save to 
modify a db connection after (or as alternative to) sqlite3_update_hook was 
triggered?
>
> The background to this question is that I’m trying to prepare a proposal 
for Geopackage. And one of the questions is if it’s possible to avoid 
Geopackage using a wrapper object [1] which is used in combination with user 
defined functions to update the rtree index after a change to the geometry 
table occurred. This object is currently necessary because it wraps another 
object (an envelope / bbox) to be used by the user defined function when the 
SQL update trigger is called.
>
> So my idea is to register an update hook and do the update to the rtree 
index directly after the step(). So this could execute an arbitrary statement, 
or more concrete a statement which would read an envelope column from the 
geometry table where the update occurred and update the rtree table 
accordingly. This would allow those columns to be plain and widely adopted WKB 
fields instead of this Geopackage binary wrapper object.
>
> Regards
> Ben
>
> [1] http://www.geopackage.org/spec/#gpb_spec
>
>
> ___
> 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


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

Re: [sqlite] Get notified as soon as it's save to modify a db after sqlite3_update_hook() was triggered

2017-04-04 Thread Peter Aronson 
If you're creating GeoPackages with the F.3 RTREE Spatial Indexes 
extension, you do not "wrap" a bounding box. You need to define 5 
functions from SQL/MM -- ST_MinX, ST_MaxX, ST_MinY, ST_MaxY and 
ST_IsEmpty -- that take a geometry blob as input and return (for the 
first four) a floating point number or an integer value of 0 or 1 
(ST_IsEmpty). Now it is possible you might want to create some sort of 
side cache so you don't have to parse the geometry blob four times, but 
that is an implementation issue. The exact text of the triggers is 
specified by the standard and is not optional if you are using the 
extension. And you certainly could not use standard WKB values in the 
geometry columns without completely violating the GeoPackage standard.


Peter

On 4/3/2017 9:26 AM, Stadin, Benjamin wrote:

Hi,

Is there a hook which allows to get notified as soon as it’s save to modify a 
db connection after (or as alternative to) sqlite3_update_hook was triggered?

The background to this question is that I’m trying to prepare a proposal for 
Geopackage. And one of the questions is if it’s possible to avoid Geopackage 
using a wrapper object [1] which is used in combination with user defined 
functions to update the rtree index after a change to the geometry table 
occurred. This object is currently necessary because it wraps another object 
(an envelope / bbox) to be used by the user defined function when the SQL 
update trigger is called.

So my idea is to register an update hook and do the update to the rtree index 
directly after the step(). So this could execute an arbitrary statement, or 
more concrete a statement which would read an envelope column from the geometry 
table where the update occurred and update the rtree table accordingly. This 
would allow those columns to be plain and widely adopted WKB fields instead of 
this Geopackage binary wrapper object.

Regards
Ben

[1] http://www.geopackage.org/spec/#gpb_spec


___
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] Get notified as soon as it's save to modify a db after sqlite3_update_hook() was triggered

2017-04-04 Thread Richard Hipp 
On 4/3/17, Stadin, Benjamin  wrote:
> Hi,
>
> Is there a hook which allows to get notified as soon as it’s save to modify
> a db connection after (or as alternative to) sqlite3_update_hook was
> triggered?

There is no such callback built into SQLite.  But you can add one by
putting a wrapper around sqlite3_step().

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

[sqlite] Get notified as soon as it's save to modify a db after sqlite3_update_hook() was triggered

2017-04-04 Thread Stadin, Benjamin 
Hi,

Is there a hook which allows to get notified as soon as it’s save to modify a 
db connection after (or as alternative to) sqlite3_update_hook was triggered?

The background to this question is that I’m trying to prepare a proposal for 
Geopackage. And one of the questions is if it’s possible to avoid Geopackage 
using a wrapper object [1] which is used in combination with user defined 
functions to update the rtree index after a change to the geometry table 
occurred. This object is currently necessary because it wraps another object 
(an envelope / bbox) to be used by the user defined function when the SQL 
update trigger is called.

So my idea is to register an update hook and do the update to the rtree index 
directly after the step(). So this could execute an arbitrary statement, or 
more concrete a statement which would read an envelope column from the geometry 
table where the update occurred and update the rtree table accordingly. This 
would allow those columns to be plain and widely adopted WKB fields instead of 
this Geopackage binary wrapper object.

Regards
Ben

[1] http://www.geopackage.org/spec/#gpb_spec


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