Inserting 500 Rows at a Time

[Elham Peiravian]

Hello,

I have a table in which I want to insert into 500 Linestring feature rows at a time. I need to this to be done recursively until all rows have been inserted. I had thought using a Trigger will do just that, but what I have so far only inserts 500 rows and then stops. What am I missing here?

PRAGMA recursive_triggers = ON;

CREATE TRIGGER IF NOT EXISTS insert_rows
INSERT ON city_roads
BEGIN
insert into city_roads select msr_v.id,
st_transform(line_substring(st_geomfromtext(View_Roads.rds_proj,3347),msr_v.fromMeasure/(View_Roads.road_length),msr_v.toMeasure/ (View_Roads.road_length)),4326)
as rds_segment from View_Roads WHERE View_Roads.id=msr_v.road_id limit 500;
END;

[a.fu...@lqt.it]

On Thu, 14 Dec 2023 05:39:58 -0800 (PST), Elham Peiravian wrote:
> Hello,
>
> I have a table in which I want to insert into 500 Linestring feature
> rows at a time. I need to this to be done recursively until all rows
> have been inserted. I had thought using a Trigger will do just that,
> but what I have so far only inserts 500 rows and then stops. What am
> I
> missing here?
>
> PRAGMA recursive_triggers = ON;
>

I'll quote what the SQLite documentation says:

"the recursive_triggers setting affects the execution of all statements
prepared using the database connection.
....
... statements prepared using the legacy sqlite3_prepare() interface
...
...
The depth of recursion for triggers has a hard upper limit set by the
SQLITE_MAX_TRIGGER_DEPTH compile-time option and a run-time limit set
by sqlite3_limit(db,SQLITE_LIMIT_TRIGGER_DEPTH,...)"

Apparently it only works for programs using the C API for
prepared statements.
Furthermore, there are limits to respect.

Having said that, I don't know what else to say because
I personally have never used recursive triggers.

In any case it's a problem that has nothing to do with
SpatiaLite, it's all depending on SQLite.
You can try posting a message on their mailing list.

bye Sandro

[Elham Peiravian]

Hi Sandro,

Thank you for your response.

What can I do to get what I need achieved in Spatialite? Triggers was basically just something that crossed my mind and I wanted to explore. With that out of the picture, what are the tools I can use in spatialite to insert only 500 rows a time?

[a.fu...@lqt.it]

On Thu, 14 Dec 2023 08:16:19 -0800 (PST), Elham Peiravian wrote:
> Hi Sandro,
>
> Thank you for your response.
>
> What can I do to get what I need achieved in Spatialite? Triggers was
> basically just something that crossed my mind and I wanted to
> explore.
> With that out of the picture, what are the tools I can use in
> spatialite to insert only 500 rows a time?
>

SpatiaLite simply is an extension to SQLite adding a lot of extra
Spatial SQL Functions, and as such has no control at all on the
SQL syntax itself that completely relies on SQLite.

So your question reduces to "What are the tools I can use in
SQLite to insert only 500 rows at a time ?"

may be I'm wrong but it doesn't seem possible in pure SQL;
it's a rather trivial task instead when using the C API on
some programming lanquage (C, C++, Python etc).

bye Sandro