Question

SQLITE Version 3.8.3 and InlineQueryFactory

  • 31 October 2017
  • 4 replies
  • 5 views
rwhittington
Rank

Hello Safers. Hope all is well.

Im wanting to use the InlineQuerier to do a WITH RECURSIVE select.

Trying to make a path from a tree stored in a relational model. Very much like this example: https://academy.vertabelo.com/blog/do-it-in-sql-recursive-tree-traversal/

Unfortunately I found that WITH support is not in version 3.8.2 of sqlite (which looks like what FMEs is using) WITH support was added with 3.8.3. Are there any plans to upgrade with future releases of FME? Or some way to patch it. Any other suggestions or workarounds? I guess I could write to a different database that supports WITH and read back. However, I was trying stay all with file based tables. Thanks in advance.

4 replies

fmelizard
Rank
Userlevel 4
Badge +13

Hi @rwhittington, there is an existing idea to have better keep up with SQLite updates, please check out Keep Up with SQLITE Updates. I suggest you up-vote on the idea and if possible, add a comment regarding your use case (i.e.- to be able to use WITH in queries).

david_r
Rank
Userlevel 4

Can confirm that even FME 2018 Beta b18175 seems to be on SQLite version 3.8.2

bruceharold
Rank
Badge +16
There is an idea post somewhere to support actual graph databases like Neo4j and Stardog, looking at the SQL statement to traverse a graph makes my brain hurt :-)

 

 

rwhittington
Rank

Thanks for the comments everyone. Yea @bruceharold any time I think of solution using recursion makes my head hurts as well. and yet there is a lot of hierarchical data stored in relational tables because sql is very fast. Oracle specific hierarchical queries help; if you are on that platform. I like the power that the InlineQuerier (sqlite cache) provides for constructing complex relationships in a workflow. I'm sure we could solve this with lists and/or adjacent features which also make my head hurt. The Aggregator can construct hierarchies for geometry with parent/child ids so I might try creating some dummy geometry to see if that approach is easier. Thanks and good eve from Texas.

Reply


Community Stats

28,692
Posts
109,433
Replies
36,544
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.