Quantcast

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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Richard Hipp-3
On 4/3/17, Stadin, Benjamin <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Peter Aronson-3
In reply to this post by Stadin, Benjamin
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

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" <[hidden email] im Auftrag von [hidden email]>:

    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
    > [hidden email]
    > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
   
   
    _______________________________________________
    sqlite-users mailing list
    [hidden email]
    http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
   

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...