Solved

Creating virtual tables in SQLITE

  • 3 December 2018
  • 3 replies
  • 19 views
R
Badge

Hello, I have the following problem writing SQLite files.

I have several use cases where I want to write SQLITE files. For my application, it is important that the virtual tables are also generated in the SQLITE file. If the table name is Table_A, there should also be a virtual table fdo_table_A.

I've created some workbench that worked. But I also have workbenchs in which the virtual tables are not created and I do not see the difference. Does anyone know the condition that these virtual tables are created?

Many Thanks David

icon

Best answer by markatsafe 6 December 2018, 21:00

View original

3 replies

M
Badge +2

@rt_gis FME doesn't support the creation of virtual tables on the SQLite writers, either native SQLite or the SQLite Spatial (FDO). You can probably create the virtual fdo tables you need using a SQLExecutor. But as far as I can gather, the virtual tables are typically application specific so you'd really have to understand the schema that the application used to read virtual tables. The SQLite writer supports a template file(database), so that might be a possible solution.

If you could share with us an example of a SQLite database that has the virtual tables you need and also tell us something about the application that uses them then we might be able to help a little further.

R
Badge

Hello, thank you for your answer. I have attached two sqlite files with different behavior.

Both were generated with FME 2018. The input data were shapefiles, xls-files and Oracle views.

In file A, the table of the same name with the prefix fdo_ is missing. In File B, this fdo table was automatically created without me consciously doing anything else in the Workbench.

I would like to use this data in a browser-based GIS via MapGuide Open Source. Our software based on this can better deal with queries on sqlite that point to the virtual tables.

sqlite.zip

 

M
Badge +2

Just to summarize this discussion:

- FME does not create SQLite virtual tables with any of the various SQLite writers such SQLite, SpatiaLte (FDO), etc.

- FME can view / read the virtual tables (Data Inspector) but will not resolve any of the virtual links or behaviours

Reply


Community Stats

28,709
Posts
109,481
Replies
36,554
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.