Money types in SQLite

I just noticed to my dismay that SQLite doesn't have any fixed-point or decimal floating point data types. It only has integers and binary floating point types.

Binary floating point types aren't suitable for storing money values because of representation error (e.g. $1.90 would be stored as something like 1.8999999999999999).

That just leaves integers. If I were to store money as e.g integer cent values, would it be reasonable to convert to and from decimal dollar values in the properties of my Entity classes. For example:

    public sealed class Product : Entity
    {
        private int _unitPrice;
        
        public decimal UnitPrice
        {
            get { return _unitPrice / 100M; }
            set { Set(ref _unitPrice, (int)(value * 100)); }
        }
    }

Would that be an acceptable and reasonable thing to do in the LightSpeed world?

It seems that SQLite.NET stores Decimals as strings. They mention this in passing in the help file:

Tweaks to decimal type handling. The 'decimal' type can't be represented by Int64 or Double (without loss of precision) in SQLite, so we have to fudge it by treating it like a string and converting it back and forth in the provider. Unfortunately backing it to the db as a string causes sorting problems. See this post for details on using a custom collation sequence to overcome the sorting issue arising from this patch.

Maybe this gets around the problem?

The integer solution sounds better than the string solution to me, because of sorting and filtering issues (e.g. when you ask for things with UnitPrice > 100 you don't want a lexical sort that will bring back things with a price of 99!).  But we're not experts on SQLite so you may want to do further research on the trade-offs and options.

The technique you show, of converting between int storage and decimal API in the property getter and setter, is absolutely reasonable.  One of the cool features of LightSpeed is that it maps the database values to fields, not to properties.  So you are completely free not to expose those fields directly, but instead to wrap them in customised properties, methods, or whatever is appropriate to your domain.  Adapting an integer storage to a decimal API is a great example of putting this to use!

Thanks, Ivan, I'll use the getter/setter method. The downside is that the data can be hard to read in a database viewer because you don't know by looking at it how many implicit decimal places you have, but I can live with that.

(For the sake anyone who finds this topic in a search, I'll add some notes here about encoding decimals as text. I believe you have to declare your columns as TEXT type, rather than NUMERIC. If you use NUMERIC, the Decimals will be encoded as REALS. You can verify what type the values are actually being encoded as by doing a select typeof(MyColumn) SQL command.)