null default values for int and float column types

[jeff7091]

Right now, we don't specify the default values, which makes them null. When we read a record with null values in int columns, SQLite converts these to 0 for us. If we Insert(), then we stuff 0 in the DB wherever there is an int property (so okay so far). The problem is when we migrate and add an int column. In this case, a query that is looking for a zero won't match the null in the newly added column - but if you read that record, you'll see the 0!

Solution is to set default values to 0 for int, and get 0.0 in there for float as well. I'm happy to do the work if you assign this to me.

[nurchi]

I guess a better solution to this would be for any "basic" types to apply NOT NULL, and for nullable types not to apply anything (unless explicitly specified with a NotNullAttribute)

Then the problem disappears altogether.

[ColonelDuddits]

@nurchi: In my opinion ValueType objects should be required to be decorated with DefaultValueAttribute and/or NotNullAttribute. During table creation, if a ValueType has NotNull specified but does not specify a default value, a default constraint with value default(T) should be created. An exception should be thrown if neither attribute is specified.

[DOliana]

Are there any news on this or a workaround for adding NotNull-Columns for simple types as int/float?

[peppy]

this is so stupid lol

• add new field of type bool.
• automatic migration runs
• defaults set to null
• queries checking for true fail
• queries checking for false fail

[nurchi]

@ColonelDuddits I partially agree. IMHO DefaultValueAttribute does not need to be mandatory, the value types already have a default value well defined by the .NET environment, so if the default is not good enough, programmer could choose to add that, but NotNull should be added automatically unless for whatever reason Null (rather DbNull) values should be accepted, the programmer should be able to specify NullAttribute, for instance, backwards compatibility with some other software...

This would be on par with the .NET environment. All the value types default to 0. Reference types default to null. A struct containing mixed members will have value-type members default to their resective default values and the reference ones to null, while a class would just be null until initialised, so alle ist gute...

Anyway, my 2 cents. And @DOliana is probably reading this and going, "If you guys are so smart, why don't you implement the changes" lol

[DOliana]

@nurchi It's not that bad, but something down that alley :-)

The thing is, that in my eyes there is a bug, since I cannot add NotNull columns to a table that contains data. So for me the first step would be to make that possible in some way (either with or without a DefaultValue).

After the behavior is consistent (between adding columns to tables with and without data), we can still argue whether a DevaultValueAttribute is needed or not. But let's get a beer for that, since it seems that the argument may take a while ;-)

[nurchi]

@DOliana, I live in Edmonton, Oilberta (lol, Alberta), Canada.

Will happily have a beer if you're around :P

[praeclarum]

Yeah this seems reasonable. The migration was relying on the fact that most null primitive types "just work" after the marshaling. But yeah, I forgot about bool.

This will add some complexity but should be worth it.

[duzenko]

Any news on this? How hard can it be... Just grab the defaultattribute from @oysteinkrog's fork

[lobbo232]

Again, any update on this? I would do the work if i knew how github works! But i am relatively new to it.

[microspaze]

I have pushed a merge request to fix this problem: https://github.com/praeclarum/sqlite-net/pull/1208/commits/0a2c9dd9fdef5d4546ebe3bc64e56c98ea5a4970

My solution is : https://github.com/microspaze/sqlite-net/commit/0a2c9dd9fdef5d4546ebe3bc64e56c98ea5a4970

1. Add DefaultValue property to ColumnAttribute:

    [AttributeUsage(AttributeTargets.Property)]
    public class ColumnAttribute : Attribute
    {
        public string Name { get; set; }
        public object DefaultValue { get; private set; }

        public ColumnAttribute(string name)
        {
            Name = name;
        }
        public ColumnAttribute(string name, object defaultValue)
        {
            Name = name;
            DefaultValue = defaultValue;
        }
    }

2. Add DefaultValue and HasDefaultValue properties in Column class, then fetch the DefaultValue from ColumnAttribute:

public class Column
{
	....
	public bool HasDefaultValue => DefaultValue != null;
	public object? DefaultValue { get; private set; }

	public Column(MemberInfo member, CreateFlags createFlags = CreateFlags.None)
	{
		....
		DefaultValue = colAttr != null && colAttr.ConstructorArguments.Count > 1 ?
				colAttr.ConstructorArguments[1].Value : null;
	}
}

3. Modify the MigrateTable method to apply default value change:

void MigrateTable(TableMapping map, List<ColumnInfo> existingCols)
{
	var toBeAdded = new List<TableMapping.Column>();
	var toBeDefaulted = new List<TableMapping.Column>();

	foreach (var p in map.Columns)
	{
		var found = false;
		var defaulted = false;
		foreach (var c in existingCols)
		{
			found = (string.Compare(p.Name, c.Name, StringComparison.OrdinalIgnoreCase) == 0);
			if (found)
			{
				defaulted = c.dflt_value != null;
				break;
			}
		}
		if (!found)
		{
			toBeAdded.Add(p);
		}
		else if (!defaulted && p.HasDefaultValue)
		{
			toBeDefaulted.Add(p);
		}
	}

	foreach (var p in toBeAdded)
	{
		var addCol = "alter table \"" + map.TableName + "\" add column " + Orm.SqlDecl(p, StoreDateTimeAsTicks, StoreTimeSpanAsTicks);
		Execute(addCol);
	}

	foreach (var p in toBeDefaulted)
	{
		var updateCol = "update \"" + map.TableName + "\" set \"" + p.Name + "\" = \"" + p.DefaultValue?.ToString() + "\" where \"" + p.Name + "\" is null";
		Execute(updateCol);
	}
}

4. Add default value when add table column in Orm.SqlDecl method:

public static string SqlDecl(TableMapping.Column p, bool storeDateTimeAsTicks, bool storeTimeSpanAsTicks)
{
	...
	if (p.HasDefaultValue)
	{
		decl += "default \"" + p.DefaultValue?.ToString() + "\"";
	}

	return decl;
}

5. Then you can use the original ColumnAttribute to set the table column's default value:

[Column(nameof(IsMarked), defaultValue: 0)]
public bool IsMarked { get; set; }