Default values

[tataremka]

Details

I have created table with default values in some columns
In data tab, i want to add new row. I cannot see any default values.
Still the same after commit changes to database and refresh.

Operating system

Windows 10, 64bit

SQLiteStudio version

3.2.1

[pawelsalawa]

SQLite2 or SQLite3 database?

[tataremka]

SQlite3

[pawelsalawa]

Can you share DDL of your table?

[tataremka]

CREATE TABLE C (
[Part Number] VARCHAR (255) PRIMARY KEY
NOT NULL,
Value VARCHAR (255),
Parameters VARCHAR (50),
[Mount Type] VARCHAR (255),
Description VARCHAR (255),
[Library Reference] VARCHAR (255),
[Library Ref] VARCHAR (255),
[Library Path] VARCHAR (255) DEFAULT [kamil.SchLib],
[Component Kind] VARCHAR (255) DEFAULT Standard,
[Component Type] VARCHAR (255) DEFAULT Standard,
Footprint VARCHAR (255),
[Footprint Ref] VARCHAR (255),
[Footprint Path] VARCHAR (255) DEFAULT [kamil.PcbLib],
[Pin Count] VARCHAR (255),
[Supplier 1] VARCHAR (255),
[Supplier Part Number 1] VARCHAR (255),
[Supplier Price 1] VARCHAR (255),
[Supplier 2] VARCHAR (255),
[Supplier Part Number 2] VARCHAR (255),
[Supplier Price 2] VARCHAR (255),
[Supplier 3] VARCHAR (255),
[Supplier Part Number 3] VARCHAR (255),
[Supplier Price 3] VARCHAR (255),
ComponentLink1Description VARCHAR (255),
ComponentLink1URL VARCHAR (255),
ComponentLink2Description VARCHAR (255),
ComponentLink2URL VARCHAR (255),
HelpUrl VARCHAR (255) DEFAULT [file://],
[Footprint Path 2] VARCHAR (255),
[Footprint Ref 2] VARCHAR (255),
[Footprint Path 3] VARCHAR (255),
[Footprint Ref 3] VARCHAR (255),
[Footprint Path 4] VARCHAR (255),
[Footprint Ref 4] VARCHAR (255),
[Footprint Path 5] VARCHAR (255),
[Footprint Ref 5] VARCHAR (255),
[Footprint Path 6] VARCHAR (255),
[Footprint Ref 6] VARCHAR (255),
Note VARCHAR (255)
);

[tataremka]

p.s. for example in Access, when i add new row i can see default values filled into grid.
SQLiteStudio doesn't do that, doesnt insert those values with commit.
I can see that in formview it has NULL checkbox checked... unchecking this makes value to be empty string... i think.
So... i think that adding new row has to fill table columns with default values...
I must say that sometimes i could see default value in the grid, but only for the last column and,
as i noticed, only in view, because after commiting changes and refreshes table it has no values in that column.

[pawelsalawa]

You need to also set "NOT NULL" constraint to have DEFAULT values inserted always.

If there is not NOT NULL, then NULL values are allowed and that's exactly what is happening. SQLiteStudio doesn't really know if you want to insert NULL or DEFAULT value, that's why it's not enforcing DEFAULT value. On the other hand - if NOT NULL is set on the column, SQLiteStudio will know that it has to use DEFAULT value if no value was provided by user, because NULL is no-go.

Even if you enable "NOT NULL" the default value will appear only after committing the new row. Until you commit, it's up to user what value goes there. If it remains NULL, then it will be replaced with DEFAULT, to satisfy NOT NULL constraint.

[tataremka]

No, you are wrong. If i do not edit column then INSERT inserts default values! You dont have to have NOT NULL constraint.
It inserts NULL or any other value only if i write something to write into column.
Just write any sql INSERT query in sql editor tab, with only one column for example, the rest will be filled with default values.

e.g.
INSERT INTO C (
[Part Number]
)
VALUES (
'aaaaa'
);
It is only sqlstustudio disfunction that if i add new row it has not columns filled with default values.

[pawelsalawa]

I know that INSERT works this way.

Now let's consider how SQLiteStudio (or basically any DB tool that allows insertion from Grid) works.
When you commit new row it has to insert all columns explicitly. It cannot skip some columns. How would it know which should it skip? In the grid you have all columns visible and you commit them all.

So what about skipping these, that have NULL value and have DEFAULT defined? Well, what if you do want to insert NULL value and not the DEFAULT one? It is valid to insert NULL.

So how about pre-filling columns with DEFAULT values, like in Access and let user decide what to do (leave default or set to NULL)?
Well, in most cases it will work fine, but not in all. If your DEFAULT expression uses some function that has a state (for example it is a sequence implemented with a function), the pre-loaded value that you show in UI will increment the sequence and it will stay incremented, even if you decide to cancel this new row addition.

You can declare NOT NULL if you want to use DEFAULT value by default.
Or you can use INSERT statements as you did above.

There is no consistent way of implementing it the way you want, because soon after it works that way, someone else will come here and report issue, that it works wrong.

I guess Access have it figured out somehow. If you know how they handle cases like above (with sequence, or virtually any stateful default value), feel free to explain and we will see if that's doable with SQLite.

[tataremka]

So what about skipping these, that have NULL value and have DEFAULT defined? Well, what if you do want to insert NULL value and not the DEFAULT one? It is valid to insert NULL.

I think if table is design with default values then it is strange if user is surprised with prefilled defaults.
Table was design to have default because it is default, default means default not NULL.
Of course user can change it to another from default.
Of course, this is discussion... but... i think defaults are very specific,,,
It is made to ease adding new rows...
In my example I am using managers like yours and access to inserts new rows in it.
I have many tables that are used in software as a data source and i am inserting manually new data in manager, because i have such soft that only using (reading) database.
For me using defaults in manager is very useful thing.
Till now i was using ms access and it was fine for such editing data in grid for adding new rows.
So... if i have to add NOT NULL to have it working with sqlitestudio then it is ok, i can handle it,
but... still have not filled values in grid... and that is the point.
I have many columns with defaults and if i cannot see it filled in when i am adding new row
then i am not sure what will be in the table after commit...
Do you think that it is possible to add functionality to prefill defaults values into the columns?
Excuse me, but for me prefilling with defaults is so obvious...

[pawelsalawa]

I was writting long explanation here, on why this is difficult to do in SQLite and I came up with idea how to solve it, so below I'm writing a note to myself in future, so when I get to work on it, I remember what I wanted to do to implemented it as you suggest.

To future me:
Solution will be to use separate DB connections per window, so they can have their own transactions. This way adding new row can begin transaction and keep it open, until user commits/rollbacks.

@tataremka If you wonder how it works now, SQLiteStudio always have 1 active connection with certain database, therefore it cannot afford to keep open transactions like that, because user could add a row (keep transaction open), then switch to other window, do something (which would be included in this transaction) and then go back to that new row and rollback it, canceling that other change too. That's why currently new rows are not in transaction until you commit/rollback. Because of that it is (currently) impossible to preload default values, without changing stateful values (sequences, functions).

Anyway, thanks for your persistence on arguing back. Thanks to you I figured out solution ;) Still, it's not a simple change, but the result of it will give SQLiteStudio many more new possibilities, not only this one discussed here.

[tataremka]

One more cent to discussion...
little work-around :) maybe just fill grid columns with default values just parsing DDL?
In fact you have all necessary information in constraints after opening table....
Just a "ghost" that fills columns instead of user on adding new row?

[pawelsalawa]

But that's exactly the point I was trying to explain you earlier.

Some default values can be stateful. PRIMARY KEY AUTOINCREMENT is simplest example of such stateful value (I know, it doesn't use DEFAULT clause, but it behaves similarly).
You can write your own custom SQL function to return sequence, just like AUTOINCREMENT then you can use this function as DEFAULT value.

Why is this problem? Well, to display value of such function you need to SELECT thisFunction() first, because it's not just number or string that you insert as default, but it's an expression, to be evaluated. If you do such SELECT and then abort new row, then add another row, the value of thisFunction() will not be the same, because there is no way to "rollback" such SELECT when you abort row. That's why I was writing all this about separate connection & transaction.

[pawelsalawa]

Btw. of investigating different issue, I came across this:

See that last option? :)
I completely forgot this was in the application already. It is an option and it's disabled by default (to avoid confusion and inconsistency I explained earlier). Nevertheless, it is available for those who want it and still the separate transactions is the way to go in future.

[tataremka]

Thanx! It works!