Problem about SQLite's integer and int datatype

Question

I meet a problem about: Object of type 'System.Int64' cannot be converted to type 'System.Int32' when run the SubSonic.Examples.SimpleRepo project base on SQLite provider.

I fond that the data type for table Categories's column CategoryID is 'integer', and the 'integer' in SQLite will be return as Int64, in the same time the CategoryID's data type in the Class Category is int, the above error is happened.

I checked the SubSonic's source code: \SubSonic.Core\SQLGeneration\Schema\SQLiteSchema.cs and find the following codes:

else if (column.IsPrimaryKey && column.DataType == DbType.Int32
    || column.IsPrimaryKey && column.DataType == DbType.Int16
    || column.IsPrimaryKey && column.DataType == DbType.Int64
    )
    sb.Append(" integer ");

Who can tell me the purpose for these codes? How to solve the data type convert error?

Answer 1

Funny I just read the sqlite3 documentation on this about an hour ago. So you're in luck :)

See the doc yourself (Scroll to the bottom, 64-bit ROWIDs section).

Here's the excerpt:

To minimize storage space, the 64-bit rowid is stored as a variable length integer. Rowids between 0 and 127 use only a single byte. Rowids between 0 and 16383 use just 2 bytes. Up to 2097152 uses three bytes. And so forth. Negative rowids are allowed but they always use nine bytes of storage and so their use is discouraged. When rowids are generated automatically by SQLite, they will always be non-negative.

3rd party edit

I was suprised that the Id-column despite being created as an int column was mapped to long as well

CREATE TABLE "Example" (
    "Id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "Foo" TEXT NULL,
    "SomeDate" DATETIME NULL
)  

the explanation is also in the 64-bit ROWID section

Every row of a table has a unique rowid. If the table defines a column with the type "INTEGER PRIMARY KEY" then that column becomes an alias for the rowid. But with or without an INTEGER PRIMARY KEY column, every row still has a rowid.

In SQLite version 3.0, the rowid is a 64-bit signed integer.