I can create SpatiaLite layers from SpatiaLite tables. However, I would like to create a layer based on a SpatiaLite view. I click on "Configure new Sql View" from the "New Layer" screen. Then I am able to create the sql view. However, after I create the new layer based on a SpatiaLite view, I cannot get queries to run against the SpatiaLite table. If I go into the newly created layer, I click on "Edit sql view", I type in a known working sql for this SpatiaLite db such as:
select * from lines
Then under Attributes, I click on "Refresh" and it shows no fields. Also if I click on "Layer Preview", select the layer and click on GML, I get the following error:
error:java.lang.RuntimeException: java.io.IOException java.lang.RuntimeException: java.io.IOException java.io.IOExceptionjava.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "FROM": syntax error) [SQLITE_ERROR] SQL error or missing database (near "FROM": syntax error)
Somehow it is not finding the sqlite table. Any help would be appreciated.
Update #1
I found this in the geoserver.log file:
Could not find mapping for 'GEOMETRY', ignoring the column and setting the feature type read only
I took a look at all the info I found online and it looks to be a limitation of the SpatiaLite provider for GEOSERVER in that it does not allow to create custom SQL views in GEOSERVER.