GeezerVison is showing github.com — Cell editor doesn't treat INTEGER, NUMERIC inputs right for INSERT or UPDATE · Issue #1952 · sqlitebrowser/sqlitebrowser
Cell editor doesn't treat INTEGER, NUMERIC inputs right for INSERT or UPDATE #1952Description
Details for the issue
What did you do?
Created a table with CHECK-constraints and inserted data.
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST (VST INTEGER CONSTRAINT TEST_VST CHECK(typeof(VST)='integer' and VST>=0 and VST<=BST or typeof(VST)='null'),BST INTEGER CONSTRAINT TEST_BST CHECK(typeof(BST)='integer' and BST>=0 and BST>=VST or typeof(BST)='null'), OBJEKTID TEXT UNIQUE);
insert into TEST(VST,BST,OBJEKTID) VALUES (0,200,'A'); -- OK
update TEST set VST=100 WHERE OBJEKTID='A'; -- OK
update TEST set VST=201 WHERE OBJEKTID='A'; -- CONSTRAINT ERROR as supposed, OK
After that, going into the Browse data tab / cell editor and trying to change the value of VST to 50 there will be a constraint error. This happens, because the cell editor tries to update the value as TEXT instead of INTEGER. I know it is possible by how SQLite works, but shouldn't the editor try to parse for the declared storage class in the table definitions and insert/update it as the declared storage class?
What did you expect to see?
Inserting/Updating data as the declared storage class from the table column (table cell edit and cell editor window), if it is parseable as the declared storage class (e.g. via typeof-Check). If not parseable as declared then insert as TEXT.
Right clicking on a value in a table cell of column VST of the previously via SQL inserted values > Copy as SQL should output
INSERT INTO "main"."TEST" ("VST") VALUES (100);
By the way I am not asking the same as mentioned in #622.
My request is not to check for the correct types when inserting but to insert the correct type if the input in the cell editor is correctly parseable.
What did you see instead?
Right clicking on the table cell of the previously via SQL inserted values > Copy as SQL outputs
INSERT INTO "main"."TEST" ("VST") VALUES ('100');
So as expected the value in the cell is treated as TEXT instead of the defined and inserted INTEGER.
Useful extra information
The info below often helps, please fill it out if you're able to. :)
What operating system are you using?
- Windows: ( version: 10 Pro 64bit )
- Linux: ( distro: ___ )
- Mac OS: ( version: ___ )
- Other: ___
What is your DB4S version?
- 3.11.2
- 3.11.1
- 3.10.1
- Other: ___
Did you also
ActivityMKleusberg commented on Aug 1, 2019 Thanks for opening this issue! This is indeed a problem in our code. A bit of background maybe: There are quite a few places where we just treat everything as text in order to avoid checking the data types of all the values all the time - we can't rely solely on the column data types because the user can still decide to enter any other value, and on the other hand checking every single value would be a performance nightmare.
In your case however this is clearly causing a usage problem and avoiding without any performance gain either. So it definitely needs fixing 😄 Can you check tomorrow's nightly build (it's going to be the same link as in your other issue, but you need to wait until the file is replaced sometime in the night) and see if it's already fixing the issue for you? mbraeutigam commented on Aug 1, 2019 Sure thing, will check tomorrow. mgrojo commented on Aug 1, 2019 It's odd that SQLite3 would end up storing the updated data as INTEGER due to the type affinity rules, if there wasn't any check. However the check is performed on the inserted value before any conversion. mbraeutigam commented on Aug 5, 2019 @MKleusberg How is a build of the nightlies triggered or updated? There's still the version 29th of July for download:
chrisjlocke commented on Aug 5, 2019 This sometimes occurs when Justin pulls the power on the build server to
power his kettle.... ;)
The nightlies should be built every night, but sometimes the builds fail
due to compilation issues. Just will look into this....
The fact that the .DMGs have also failed would indicate something
server-wise... Reacted by Justin Clift Reacted by Marc Bräutigam and Justin Clift scottfurry commented on Aug 5, 2019 @chrisjlocke - A spot of tea impacts software delivery?!?!?!?!
I'm shocked! Shocked, I tell you! 😟 😱 😟 😨 😱 😵
😜😎 Reacted by Marc Bräutigam and Justin Clift mgrojo commented on Aug 5, 2019 I seems we should raise some funding in order to buy @justinclift a power strip with multiple sockets 😄 Reacted by Marc Bräutigam and Justin Clift MKleusberg commented on Aug 10, 2019 The new nightlies are available now 😄 mbraeutigam commented on Aug 12, 2019 @MKleusberg I checked it.
-
Input in cells and the editor textfield seems to work now (tested with integers and double/real values).
-
right clicking on the cell and Copy as SQL still copies/outputs strings.
outputs in Execute SQL window
INSERT INTO "main"."TEST" ("BST") VALUES ('200');
but for integers the expected SQL statement should be
INSERT INTO "main"."TEST" ("BST") VALUES (200);
or for the respective double/real values: (x.y) instead of ('x.y')
mgrojo commented on Aug 4, 2024
- right clicking on the cell and Copy as SQL still copies/outputs strings.
Hi @mbraeutigam. This is fixed in the latest continuous build. Would you mind giving it a try and confirm it's working as expected and I haven't broken anything?
Let's see if we can close this soon, as currently we have the number of the beast 👿 as number of issues and I don't want any bad luck or whatever 😄 Reacted by Justin Clift and Marc Bräutigam mbraeutigam commented on Aug 5, 2024 Hi @mgrojo
Let's see if we can close this soon, as currently we have the number of the beast 👿 as number of issues and I don't want any bad luck or whatever 😄
On numerics the "Copy as SQL" copies: INSERT INTO "main"."TEST" ("VST") VALUES (50);
So it is working as expected ✔
Tested the same but with numeric values in TEXT storage class: INSERT INTO "main"."TEST" ("OBJEKTID") VALUES ('5');.
Output is also as expected. ✔
So you can close this issue and get an instant level up for your karma 🚀🤣 mgrojo commented on Aug 5, 2024 Great! One issue more away from the beast. Reacted by Justin Clift Reacted by Marc Bräutigam
MetadataMetadataDevelopmentNo branches or pull requestsIssue actions
|
