Skip to main content
Solved

How to truncate a spatialite table with a hack

  • September 10, 2018
  • 5 replies
  • 54 views

kimo
Contributor
Forum|alt.badge.img+10
  • Contributor

FME has disabled truncating a Spatialite table, so I need a hack to do it anyway. I want it to exist so that other applications can read it after it is updated.

I need to truncate rather than drop_and_create to refresh the table with an insert because I have another transformer right afterwards doing an SQLExecutor that does a selection from a large table in the same sqlite database. It seems that a lock is added that prevents the drop/create.

So you might think that I could hack the truncate using SQLExecutor, but no! All database operators are disabled in FME for spatialite so the "DELETE FROM table" is not allowed.

Never mind I will use Python. Ah, but now that I am using FME 64 bit I need to use Python3.6 and that is now from Anaconda which does not have sqlite as a standard module for ArcGISPro, so my python hack also fails.

Well then, I will just shell out to sqlite.exe. But wait, FME implements it as sqlite_fme.dll, not a EXE.

I can truncate the table if I use a filegeodatabase but then I cannot run SQLExecutor because a filegeodatabase does not have an SQL interface. That leaves me reading the whole table and doing a databaseJoin which takes 100 times longer and is too long (5 minutes v 13 seconds) for a real-time application.

The simple solution is for FME to upgrade the Spatialite/Sqlite writers to be fully SQL compliant. There is already an old 2017 idea posted for that, hence the need for a hack.

Best answer by kimo

Postscript: This is how I solved my truncate.

 

1. Download an executable sqlite3.exe. Save locally, no need to install any software with admin permissions etc. I found a 32bit version which is fine, even when everything else is 64bit.

 

2. Remove all readers and writers calling the sqlite file. Only use a FeatureWriter and SQLExecutor to access the sqlite file. Thus I only have the two tables required. One small key table that is truncated and refreshed, the other large more static table. All other tables are read from and written to a different database.

 

3. Use a python startup script that calls a subprocess that calls sqlite3 with a parameterised 'DELETE FROM table'

 

See below the suggestion from Xiaomeng to slip through the read-only check. I could not get it to work though.

 

4. Speed to call an HTTP feed of current bus locations ( ~ 2000), build a dynamic route network subset based on the timetable, solve it and compare scheduled to predicted times: 13 seconds. Note it is predicted time needed, not just actual. Once the bus arrives late for a connection it is too late to take action. Also bus times are not affected the same way as general traffic because of priority bus lanes.

 

View original
Did this help you find an answer to your question?

5 replies

xiaomengatsafe
Safer
Forum|alt.badge.img+3

Hi @kimo. Thanks for raising this issue with us. We are aware of this current limitation, and will notify you here when we have any updates on the enhancement.

 

I see that you've found and commented on some previous threads related to this problem:

 

https://knowledge.safe.com/questions/50230/sql-executor-and-spatialite-delete-or-drop-command.html https://knowledge.safe.com/idea/50358/improve-sqlexecutor-to-operate-the-same-way-on-all.html?

One potential hack we found is to use the following SQL query structure:

[command SQL that we want to accomplish]; [trivial query SQL]

Example:

`DELETE FROM table_1; SELECT 'meaningless' FROM table_1`

Hope this helps a little.


kimo
Contributor
Forum|alt.badge.img+10
  • Author
  • Contributor
  • September 13, 2018
xiaomengatsafe wrote:

Hi @kimo. Thanks for raising this issue with us. We are aware of this current limitation, and will notify you here when we have any updates on the enhancement.

 

I see that you've found and commented on some previous threads related to this problem:

 

https://knowledge.safe.com/questions/50230/sql-executor-and-spatialite-delete-or-drop-command.html https://knowledge.safe.com/idea/50358/improve-sqlexecutor-to-operate-the-same-way-on-all.html?

One potential hack we found is to use the following SQL query structure:

[command SQL that we want to accomplish]; [trivial query SQL]

Example:

`DELETE FROM table_1; SELECT 'meaningless' FROM table_1`

Hope this helps a little.

Ingenious, but it does not work for me. The message is "SPATIALITE reader: In ExecuteSQL(): sqlite3_step(delete from bus_current;select trip_id from bus_current): attempt to write a readonly database SPATIALITE reader: Unable to execute SQL statement 'delete from bus_current;select trip_id from bus_current'

 

SPATIALITE reader: Unable to read feature type '__fme_execute_sql_results__'

 

A fatal error has occurred. Check the logfile above for details"

 

 

 


kimo
Contributor
Forum|alt.badge.img+10
  • Author
  • Contributor
  • Best Answer
  • September 13, 2018
Postscript: This is how I solved my truncate.

 

1. Download an executable sqlite3.exe. Save locally, no need to install any software with admin permissions etc. I found a 32bit version which is fine, even when everything else is 64bit.

 

2. Remove all readers and writers calling the sqlite file. Only use a FeatureWriter and SQLExecutor to access the sqlite file. Thus I only have the two tables required. One small key table that is truncated and refreshed, the other large more static table. All other tables are read from and written to a different database.

 

3. Use a python startup script that calls a subprocess that calls sqlite3 with a parameterised 'DELETE FROM table'

 

See below the suggestion from Xiaomeng to slip through the read-only check. I could not get it to work though.

 

4. Speed to call an HTTP feed of current bus locations ( ~ 2000), build a dynamic route network subset based on the timetable, solve it and compare scheduled to predicted times: 13 seconds. Note it is predicted time needed, not just actual. Once the bus arrives late for a connection it is too late to take action. Also bus times are not affected the same way as general traffic because of priority bus lanes.

 


xiaomengatsafe
Safer
Forum|alt.badge.img+3
kimo wrote:
Ingenious, but it does not work for me. The message is "SPATIALITE reader: In ExecuteSQL(): sqlite3_step(delete from bus_current;select trip_id from bus_current): attempt to write a readonly database SPATIALITE reader: Unable to execute SQL statement 'delete from bus_current;select trip_id from bus_current'

 

SPATIALITE reader: Unable to read feature type '__fme_execute_sql_results__'

 

A fatal error has occurred. Check the logfile above for details"

 

 

 

@kimo Sorry that this little hack didn't work for you. However, I'm glad to see you've figured out a workaround for now.

 

We will provide updates here, when we make any enhancements on this issue. Thank you again for sharing your thoughts on this issue.

 


andreaatsafe
Safer
Forum|alt.badge.img+12

Hi @kimo​ ,

I'm please to let you know that a recent update to FME 2020.2 beta (build 20709 and newer) has enabled SQL executing statements for the SpatiaLite format in the SQLCreator and SQLExecutor. We now support more than read-only SQL statements when using the SpatiaLite format (eg. INSERT/UPDATE/DELETE/CREATE/DROP).

You can find the latest beta build here: www.safe.com/beta

- Andrea


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings