Decimal type is read as double or int from In-memory Sqlite database

Question

I don't have too much experience with Sqlite in .Net, but the behaviour I see is rather strange. Let's say we have a .Net core app with following project.json:

{
  "version": "1.0.0-*",
  "buildOptions": {
    "debugType": "portable",
    "emitEntryPoint": true
  },
  "dependencies": {
    "Microsoft.Data.Sqlite": "1.0.0",
    "Dapper": "1.50.2"
  },
  "frameworks": {
    "netcoreapp1.0": {
      "dependencies": {
        "Microsoft.NETCore.App": {
          "type": "platform",
          "version": "1.0.0"
        }
      },
      "imports": "dnxcore50"
    }
  }
}

Also we have a simple class Item:

public class Item
{
    public Item() { }

    public Item(int id, string name, decimal price)
    {
        this.Id = id;
        this.Name = name;
        this.Price = price;
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

Then I create an in-memory database and populate it with data (using Dapper):

var connection = new SqliteConnection("Data Source=:memory:");
connection.Open();
connection.Execute("CREATE TABLE IF NOT EXISTS Items(Id INT, Name NVARCHAR(50), Price DECIMAL)");

var items = new List<Item>
{
    new Item(1, "Apple", 3m),
    new Item(2, "Banana", 1.4m)
};
connection.Execute("INSERT INTO Items(Id, Name, Price) VALUES (@Id, @Name, @Price)", items);

Then I try to read from the Items table:

var dbItems = connection.Query<Item>("SELECT Id, Name, Price FROM Items").ToList();

When I run the solution, I get the following exception:

Unhandled Exception: System.InvalidOperationException: Error parsing column 2 (Price=1.4 - Double) ---> System.Invali dCastException: Unable to cast object of type 'System.Double' to type 'System.Int64'.

Ok, then I tried to use Microsoft.Data.Sqlite to get the data:

var command = connection.CreateCommand();
command.CommandText = "SELECT Price FROM Items";
var reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(reader[0].GetType());
}

As a result I get:

System.Int64 // Price = 3                                                                                                         
System.Double // Price = 1.4

I tried running query on real database with decimal price, the data type returned is correct and is always decimal (as expected).
What direction should I dig further? Is something wrong with my in-memory database? How to make it consistent with decimals?

Answer 1

In SQLite, if you create a Table with a Numeric/Decimal Column datatype, it's associated to Numeric type affinity and its default behavior is to store an integer if a value has no decimals, or to store a real number if value contains decimals. This helps to save bytes storage because the most used integer values (less than a million value for small to medium applications) needs less bytes than real data type:

Integer (approx):

• ± 128 > 1 byte (2^8-1)
• ± 32,768 > 2 bytes (2^16-1)
• ± 8,388,608 > 3 bytes (2^24-1)
• ± 2,147,483,648 > 4 bytes (2^32-1)
• ± 140,737,488,355,328 > 6 bytes (2^48-1)
• ± 9,223,372,036,854,780,000 > 8 bytes (2^64-1)

Against 8 byte real IEEE datatype

If you need to store a real number always, it's necessary to declare a float/real datatype in order to be considered as real type affinity, thus even if you send an integer number to SQLite it would be stored as real

Source: https://sqlite.org/datatype3.html

I know real could be imprecise, but I've made some test using C# Decimal datatype and SQLite with database in disk in wal mode, and I've never had a rounding error if all operations are made in C#. But when I made some calculations directly in SQLite I got some rounding errors and some bad calculations because of 2 integer division

I use EF6 and Dapper to read/write Numeric values (integer/real from SQLite) and never got an error.

Now, I'm planning to take advantage of SQLite numeric behavior to save disk space with dapper TypeHandler, thus I may replicate SQL Server Money implementation (internally, SQL Server saves an 8 byte integer and it's divided by 10000 when it loads into memory):

public class NumericTypeHandler : SqlMapper.TypeHandler<decimal>
{
    public override void SetValue(IDbDataParameter parameter, decimal value)
    {
        parameter.Value = (long)(value / 10000);
    }

    public override decimal Parse(object value)
    {
        return ((decimal)value)/10000M;
    }
}

Additionally, I set up datetime UnixEpoch implementation to save diskspace and enhance performance

Note:

SQLite may handle dozens of users in a low demand asp.net application, the trick is tune-up SQLite with pragmas directives and other things:

• WAL mode: helps to manage better concurrency and performance
• Page size: if matches the file system's cluster size, enhances performance
• Shared Cache Mode: implements table locks instead database locks when it's acceded in several threads
• Datetime UnixEpoch: saves seconds (4 bytes) instead of text ISO datetime ("2012-12-21T17:30:24" = 20 bytes).
• Cache size: saved I/O access keeping last accessed pages in memory

Also I made some enhancements in C# side:

• I made a custom DateTime struct for DateTime values in order to save data as integer overriding ToString method and loads into memory as DateTime with constructor overload.
• I Made a custom Numeric struct in C#, (like DateTime one), to save money as cents overriding ToString method and loads into memory as Decimal with constructor overload

avoid save unnecessary data (Error logs, email html text)

I think you should use an ORM like EFx or Dapper if you plan to use SQLite as backend. With dapper you would need to create your own micro framework to save development time (basic CRUD functionality and Linq queries transformation).

The best tool that I´ve found is https://github.com/linq2db/linq2db, you use linq for accessing database with very good speed and easiness of linq. And it is possible to make CRUD functionality.

I'll be happy if this answer helps

regards

Answer 2

This is a feature of SQLite, please see this link: https://sqlite.org/faq.html#q3