Question

Ability to leverage In-memory SQLite/SpatiaLite DBs

  • 25 November 2019
  • 0 replies
  • 10 views

Badge +3

Fun fact, is that FeatureWriter writes to a database path of ":memory:" for SQLite and SpatiaLite formats (or seems to at least). For SQLite buffs, this is a virtual database instance written entirely in RAM so cuts down on disk/temporary file overheads and is highly performant. It is a feasible way of combining in an FME workspace non-SQL, procedural type transformers together with the power and lower overheads of the SQL engine contained in SQLite when your features also happen to be highly data relational and can be indexed (plus take advantage of the extensive spatial library functions that come with SpatiaLite as well)

However, for the life of me I can't figure out any way to use an in-memory SQLite DB from there in a workspace, say with an SQLExecutor. It seems as soon as FeatureWriter finishes it closes the connection to a database written to ":memory:" and no further reference is held to it (and because the DB is volatile then the Transformer essentially deletes it from RAM when it closes the connection). I can only think that an alternative is to instead write to a temporary, but physical file DB using FeatureWriter in conjunction with TempPathNameCreator, but that is then getting into file space/read/write overhead and file management issues.

I have been also playing with InlineQuerier but I can't really tell from the documentation where this SQLite DB is held and what its limitations are. It can't seem to quite do all accepted SQLite statements, for instance "CREATE VIEW View1 AS SELECT * FROM Table 1", followed by "SELECT * FROM View1" doesn't work, and similarly the field index options in InlineQuerier are a little bugged in 2018 with instead the user needing to write out "CREATE INDEX ..." statements as a "Result" when more than one field requires an index. InlineQuerier in 2018 can create 1 index only before throwing an error as InlineQuerier attempts to create all indices using the same Index name when the user has selected to index more than one field, which isn't allowed as all Indices must have a unique name, hence the SQLite engine throws an error back to FME that "this index already exists"

Happy to hear suggestions on potential ways to leverage in-memory instances of SQLIte/SpatiaLite!


0 replies

Be the first to reply!

Reply