QGIS » QGIS Application

I tested using qgis 2.0.1 and 2.1 on windows 7 and also qgis 2.0.1 in ubuntu 12.04.
I could not reproduce this issue. The new column is shown after re-adding to qgis.
/Josef

Thank you for testing Josef,

I opened a bug upstream and there seems to be a problem indeed (I'm working on it). I don't know why it works in your case. Are you sure you have spatialite 4?

https://groups.google.com/forum/#!topic/spatialite-users/FOq1DPRDkJw

Yes, I am definitely sure about using splite 4.1.1.
(And I am glad you are working on the problem although I could not verify this specific issue.)

This is a tricky issue. Just spent a good amount of time to reproduce it. It seems not to affect every file and it seems also to work always for the first added column and only affect subsequent columns.

As a workaround, I think the following SQL commands executed should work:

UPDATE geometry_columns_statistics set last_verified = 0;
SELECT UpdateLayerStatistics('geometry_table_name');

I would be happy to get a feedback if this workaround works for others as well. If yes, it could be implemented into the spatialite provider.

Just tested proposed workaround on several databases and it works fine for me.

I've got a preliminary implementation containing a workaround for spatialite <= 4.1.1 and a proper implementation for spatialite > 4.1.1.

However it seems it suffers from a flaw that any new column is treated as TEXT, so no INTEGER, FLOAT or BLOB support at the moment...

https://github.com/m-kuhn/QGIS/tree/spatialite-fix

I confirme the bug on Ubuntu and Windows.
The workaround works fine.

Some one is reviewing the proposed patch ?
Can we have it backported to release_2_2 ?

I think that (under certain circumstances?) spatialite determines the column type from the content. That means, as long as there is no content it cannot properly determine the column data type (and falls back to TEXT). We normally just create a new column which is then initialized with NULL values and only filled with data afterwards, so QGIS loads the new column while there are still only NULL values in there.

• Verify this hypothesis
• Fix it upstream
• Implement a workaround for affected versions

Created a pull request:

https://github.com/qgis/QGIS/pull/1406

While the patch definitely is not perfect, it is the best we have at the moment and it shouldn't make things worse.

Hi,
I tested the following with osgeo4w this morning d62a498 , with many problems:

- convert a postgis table in spatialite 4.1 via "save as"
- add int field, save, populate it with an expression (random): All lines are not populated, even if no selection was pending. Impossible to me to populate those cells by hand
- add a text fied, populate it by expression: other cells can't be filled and stay NULL
- select null lines, and delete them > QGIS Hangs...

Spatialite is currently not usable.. Will keep testing.

Régis

Previous bug is probably only for our postgis 9.0 instance that is having serious trouble in system tables and oid's.
Please do not take my previous report into account.

Regis Haubourg wrote:

- convert a postgis table in spatialite 4.1 via "save as"

Side note: "Save as" use the OGR to produce the spatialite database, while d&d in the browser would use the spatialite provider to create table table - which also is used to access the database later. Does the latter behave the same?

with dbmanager d&d, issue of null uneditable values is not reproduced. Still my issue with source oid's remains, since now see negative oids ! (my postgres instance is dying, migration urges now. postgis 2 and pg 9.3 is not affected)

with file datasources, I could successfully add several fields and fill them. Patch seems ok here.
Régis

Regis Haubourg wrote:

with dbmanager d&d, issue of null uneditable values is not reproduced. Still my issue with source oid's remains, since now see negative oids ! (my postgres instance is dying, migration urges now. postgis 2 and pg 9.3 is not affected)

Another side note: I meant d&d the browser and not in dbmanager - not sure if dbmanager uses the spatialite provider or has yet another way to create databases, tables and/or insert into sl databases.

Régis, thank you for testing.

• If you reopen the project/readd the layer in a new project, does still everything work properly?
• If the type is not TEXT, does it work? (E.g. field calculator to fill a newly created INT column with rand() values)

Reopening , readding seems ok with int and decimal, reading sqlite with ogr file or database connection gui.

I have one error when creating a new int or decimal field, and trying to save before having it filled with data. The error message is strange, it seems to get the wrong field (here field 58 when i have 70 fields.)

Commit errors:
  SUCCESS: 1 attribute(s) added.
  ERROR: field with index 58 is not the same!
  Provider: spatialite
  Storage: SQLite database with SpatiaLite extension
  expected field: name=vol_gpt_soutien_etiage type=QString typeName=TEXT len=0 precision=0
  retrieved field: name=vol_gpt_soutien_etiage type=double typeName=FLOAT len=0 precision=0

May that be leftovers of experiments performed with QGIS without this patch added?

See #8923-5 for how to fix the file.
Can you try to fix the DB and then check if you can reproduce the bug afterwards?

Hi,
Thanks for the workaround.

There is still a problem when loading a corrupted sqlite file right ? (I stumbled upon this when trying to add fields to an open sqlite file from a plugin).
In the loadFieldsAbstractInterface function (spatialite provider), if I am correct the statistics table is only used to get name and type of fields, couldn't it be safely replaced by PRAGMA table_info() ?

In loadFieldsAbstractInterface there is a PRAGMA table_info() call and a gaiaVectorLayerPtr is checked.
I think the second one is initialized by the spatialite driver. With information from the statistics table. This seems to be a conceptual decision.

You can refer to this discussion here https://groups.google.com/forum/#!topic/spatialite-users/FOq1DPRDkJw for some more information.

Matthias Kuhn wrote:

I've got a preliminary implementation containing a workaround for spatialite <= 4.1.1 and a proper implementation for spatialite > 4.1.1.

However it seems it suffers from a flaw that any new column is treated as TEXT, so no INTEGER, FLOAT or BLOB support at the moment...

https://github.com/m-kuhn/QGIS/tree/spatialite-fix

I can verify that the above issue, new columns disappearing in QGIS, is still a problem in 2.12. New TEXT columns seem to appear OK, but new INTEGER do not even after Matthias' workaround.

Read this discussion from the spatialite-users forum with a proposed way to fix it properly.
https://groups.google.com/forum/#!topic/spatialite-users/uGDZr1TCTn8