CODEWITHSUNDEEP
sqlitesqldatatypes
Tide Gu
Tide Gu

Reputation: 835

Does data type / storage class matter at all in SQLite?

I understand that SQLite does not have data type. But I'm not quite sure if it matters when creating table?

I was trying to generate the CREATE TABLE statement automatically. At the very beginning, I thought I should map the C# data types to SQLite data types until I read the description of storage class. This document confused me a bit, thus I think it's better to ask and correct my understanding.

  1. It says there are 5 storage classes, which are NULL, INTEGER, REAL, TEXT and BLOB. However, it also stated 5 affinities, TEXT, NUMERIC, INTEGER, REAL and BLOB, which doesn't map to the storage classes exactly. i.e., it doesn't have NULL affinity and what will NUMERIC map to in the storage class?

  2. Decimal in C# is a floating point, why it is mapped to NUMERIC affinity? Was that because REAL has 8 bytes max and Decimal has 16? Can SQLite store Decimal precisely as in C#?

  3. Why both Boolean and Decimal are mapped to NUMERIC? Boolean seems take the smallest storage (1 byte or even 1 bit), but Decimal takes much more. If NUMERIC is a single-byte type, it won't be able to store Decimal, or if it is multi-bytes, why don't Boolean mapped to INTEGER? It should be more efficient, shouldn't it? Or, should I completely forget the bytes of data type in SQLite?

Albeit these questions in my head, I tried to map a struct into table. Some sample code can be seen as follows (class/function structure excluded):

public sealed class ScreenInfo {
    public int ScreenId;
    public string Name;
    public int BoundX;
    public int BoundY;
    public int BoundW;
    public int BoundH;
    public int WorkingAreaX;
    public int WorkingAreaY;
    public int WorkingAreaW;
    public int WorkingAreaH;
    public int BitsPerPixel;
    public bool IsPrimary;
    public Rectangle Bounds {
        get { return new Rectangle(BoundX, BoundY, BoundW, BoundH); }
        set { BoundX = value.X; BoundY = value.Y; BoundW = value.Width; BoundH = value.Height; }
    }
    public Rectangle WorkingArea {
        get { return new Rectangle(WorkingAreaX, WorkingAreaY, WorkingAreaW, WorkingAreaH); }
        set { WorkingAreaX = value.X; WorkingAreaY = value.Y; WorkingAreaW = value.Width; WorkingAreaH = value.Height; }
    }
}

StringBuilder sb = new StringBuilder();
sb.Append("CREATE TABLE `Screens` (`id` INTEGER PRIMARY KEY AUTOINCREMENT");
var fields = typeof(ScreenInfo).GetFields();
foreach (var f in fields) {
    sb.Append($", `{f.Name}` {Type.GetTypeCode(f.FieldType).ToString()}");
}
sb.Append(");");

The code above generates the following SQL statement. Although String and Int32 are not typical SQL data types, it seems to be working fine.

"CREATE TABLE `Screens` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `ScreenId` Int32, `Name` String, `BoundX` Int32, `BoundY` Int32, `BoundW` Int32, `BoundH` Int32, `WorkingAreaX` Int32, `WorkingAreaY` Int32, `WorkingAreaW` Int32, `WorkingAreaH` Int32, `BitsPerPixel` Int32, `IsPrimary` Boolean);"

If the struct does not contain a field named id (otherwise it will conflict with the primary key), will it cause any other potential problems in the SQLite database?

Thanks in advance!

Upvotes: 1

Views: 1151

Answers (1)

CL.
CL.

Reputation: 180070

SQLite does have data types, it just uses dynamic typing, i.e., it does not restrict the types that can be inserted into a column.

Affinities are not storage classes; they describe the type that a column 'wants' to be. Therefore, there is no NULL afinity.

The NUMERIC affinity maps to either INTEGER or REAL, whatever is more efficient.

SQLite has no decimal type.

SQLite's storage classes are types, but the actual way how values are stored in the database is different. Integers can be smaller than 8 bytes, and booleans take 0 bytes.

Writing a column type as Int32 does not make it any different from any other type that maps to the INTEGER affinity; it would be useful only for documentation.

Upvotes: 3

Related Questions