3

QGIS shows default values from sqlite tables as string in the edit-form when i try to create a new row. After save, the raw default values are stored in the table. How can i edit in QGIS LTR 3.4.7 a sqlite-database without messing up the default values from the tables?

QGIS-form with new feature and 'wrong' default-values. Feature from sql-insert with id 1 in background. qgis-form with default values from database

QGIS-table after save with pointless default-values. qgis-table view after editing

tried "solutions"

Create statement for the table in the sqlite-database

CREATE TABLE my_table (
    id integer primary key autoincrement,
    comment text,
    created_at_01 text DEFAULT (datetime('now','localtime')),
    created_at_02 text DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO my_table (comment) VALUES ('hello world');

SELECT * FROM my_table;

id          comment      created_at_01        created_at_02      
----------  -----------  -------------------  -------------------
1           hello world  2019-12-09 18:00:12  2019-12-09 17:00:12
  • What happen if you tick on the Evaluate default values on provider side option, in the Project Properties, Data Sources tab? – Gabriel De Luca Dec 9 '19 at 21:04
  • Thanks for your input. I already tried that although the tooltip from this option shows "Only supported for postgres provider". So it does not bypass the default value problem. – Andre Dec 9 '19 at 21:15
  • If you set the data type to TIMESTAMP instead of TEXT? A later option is defining the default value also in the widget with now(). – Gabriel De Luca Dec 9 '19 at 21:36
  • Thanks for the idea. Data type TIMESTAMP is not supported in sqlite Date and Time Datatype. If i choose data type REAL or INTEGER instead of text, the default value in the QGIS-Form is 0. Default value in the widget works. But the default definition in the database is prefered for various reasons. – Andre Dec 9 '19 at 21:54
2

My proposal is to create two triggers, so that after each insertion or update of rows, it is verified that if a text string with the shape of the expression is being recorded in that row, the corresponding expression is evaluated:

DROP TRIGGER IF EXISTS "trigger_my_table_insert_evaluate_localtime";
CREATE TRIGGER "trigger_my_table_insert_evaluate_localtime" 
AFTER INSERT ON my_table 
WHEN new.created_at_01 LIKE '%datetime%now%localtime%'
BEGIN
  UPDATE my_table
  SET created_at_01 = (datetime('now','localtime'))
  WHERE id = new.id;
END;

DROP TRIGGER IF EXISTS "trigger_my_table_update_evaluate_localtime";
CREATE TRIGGER "trigger_my_table_update_evaluate_localtime" 
AFTER UPDATE ON my_table 
WHEN new.created_at_01 LIKE '%datetime%now%localtime%'
BEGIN
  UPDATE my_table 
  SET created_at_01 = (datetime('now','localtime'))
  WHERE id = new.id;
END;

Tests were working fine here. In QGIS, when insert, changes are made at save edits. When update too, but you need to close the attribute table and open it again to see the changes.
Please test it, I'm just learning.


Reference:

| improve this answer | |
  • Yes a workaround with triggers is possible. But you have to add triggers every time you use an other database with default values. That is quite anoying ;) I also checked QGIS on Linux and geopackage. Same behaviour. Maxbe it is time to open an issue. – Andre Dec 11 '19 at 19:10
  • Yes. Maybe the providers side default values handle is not fully implemeted yet. The note about only Postgres databases in the project prooperties option could be an indication of that. In Settings menu, Options, Data Sources tab, the Evaluate default values option can't be enabled. – Gabriel De Luca Dec 12 '19 at 0:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.