Spatial VIEW creation error

[Antonio Valanzano]

Does someone know what is wrong  with the following SQL code ?

-- step 1

CREATE TABLE st_g AS
SELECT
  st.id_sentiero,
  ST_UNION(t.geom) as geometry
FROM sentieri_tratti as st
INNER JOIN tratti as t ON (st.id_tratto = t.id)
GROUP BY st.id_sentiero;

SELECT RecoverGeometryColumn('st_g','geometry',32633,'MULTILINESTRINGZ',3);
-- everything is ok

-- step 2
CREATE VIEW st_g_attrib AS
SELECT
  st.rowid as rowid,
  st.id_sentiero,
  s.nome,
  st.geometry
FROM st_g as st
INNER JOIN sentieri as s ON (st.id_sentiero = s.id);

INSERT INTO views_geometry_columns
  (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column)
 VALUES ('st_g_attrib', 'geometry', 'rowid', 'st_g', 'geometry');

After the INSERT INTO I receive the following message:

"SQL error: NOT NULL constraint failed:  views_geometry_columns.read_only"

I have created the same view using the Query/View Composer and it was created without problems.

I do not understand where I am making an error.

Many thanks for your help.

Antonio

[a.fu...@lqt.it]

ciao Antornio,

> After the INSERT INTO I receive the following message:
>
> "SQL error: NOT NULL constraint failed: 
> views_geometry_columns.read_only"
>

this happense because in your INSSERT statament you've missedd
to specify any valure for the "read_only" column.

the correct statement should be:

INSERT INTO views_geometry_columns
(view_name, view_geometry, view_rowid,

f_table_name, f_geometry_column, read_only)

VALUES ('st_g_attrib', 'geometry', 'rowid',

'st_g', 'geometry', 0);

> I have created the same view using the Query/View Composer and it was
> created without problems.
>

note that the Query/View Composer does a lot of operations in order
to create a Writable View, which you are skipping right away.

SQLite doesn't natively supoorts Writable Views; a full set of
Triggers must be installed in order to transform a simple
View into a Writeble View.
the Query/View Composer does silently all the dirty
work behind the scense, but if you whish to manually
create the Vien then you have to manually create all
Triggers as well.

being able to write these Triggers correctly is not
a trivial task at all, I would say that it's one of the
most complex things I can imagine given that it requires
a very deep knowledge of SQLite.

if you would like to study some further information you
can find it here:

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=writable-view

bye Sandro
:

[Antonio Valanzano]

Dear Sandro,

thanks for your quick response.

I didn't want to create a writable view using only SQL and not the Query/View Composer.

I just wanted to create a read_only View.

But  because I have created a Spatial View last time some years ago, I have forgotten the correct syntax of  INSERT INTO views_geometry_columns() .

After reading your answer I have searched into my old sql scripts and I have found several examples of INSERT INTO  views_geometry_columns() 

with  the read_only parameter equal to 1 (for read_only views).

Thanks again for your help.

Antonio

 

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/spatialite-users/7e7a98e85240171f8aee496a2f8e48f3%40lqt.it.