Problem with int and DateTime types with EntityFrameWorkCore.Sqlite

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Problem with int and DateTime types with EntityFrameWorkCore.Sqlite

Ainhoa B
Hi all,

I'm working with SQLite and EntityFrameworkCore and I'm having problems.

My database has tables with int values and DateTime values. When I execute
the scaffold command to convert the tables of the database to models in
.NET Framework, my colums of type int are being converted to long and my
columns of type DateTime to string in models. As far as I know, SQLite
doesn't work with DateTime, it saves that types as string, integers or
other values, but I don't know what it's happening with int types. Does
anyone know why int types are converted to long types?

Thanks!
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with int and DateTime types with EntityFrameWorkCore.Sqlite

Clemens Ladisch
Ainhoa B wrote:
> My database has tables with int values and DateTime values. When I execute
> the scaffold command to convert the tables of the database to models in
> .NET Framework, my colums of type int are being converted to long

SQLite's INTEGER type has 64 bits.  The framework assumes that such columns
can have 64-bit values (probably written into the DB by somebody else).


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with int and DateTime types with EntityFrameWorkCore.Sqlite

Ainhoa B
So, in SQLite, it doesn't matter if I create a table with a column of int,
smallint or long type, it will always be trated as a INTEGER type of 64
bits?

Regards

El mié., 7 ago. 2019 a las 9:01, Clemens Ladisch (<[hidden email]>)
escribió:

> Ainhoa B wrote:
> > My database has tables with int values and DateTime values. When I
> execute
> > the scaffold command to convert the tables of the database to models in
> > .NET Framework, my colums of type int are being converted to long
>
> SQLite's INTEGER type has 64 bits.  The framework assumes that such columns
> can have 64-bit values (probably written into the DB by somebody else).
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with int and DateTime types with EntityFrameWorkCore.Sqlite

Olivier Mascia
> Le 7 août 2019 à 09:43, Ainhoa B <[hidden email]> a écrit :
>
> So, in SQLite, it doesn't matter if I create a table with a column of int,
> smallint or long type, it will always be trated as a INTEGER type of 64
> bits?

Regarding integers, yes they are always signed 64 bits integers.  See https://www.sqlite.org/datatype3.html for more details.

When physically storing the integer value, the internal format is varying on the magnitude of the value stored. There is nothing really lost (storage-wise) by using signed 64 bits integers at the interface level, even though your values would only be 0, 1, 3, and 12347.

SQLite will store signed integers using 8, 16, 24, 32, 48 or 64 bits payloads.  Values 0 and 1 even only store their type, with no payload.

https://www.sqlite.org/fileformat2.html#record_format

—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten Grüßen,
Olivier Mascia

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with int and DateTime types with EntityFrameWorkCore.Sqlite

Ainhoa B
And what about Decimal type? Because Decimals are converted to string. It's
a problem because it forces the programmer to format read values from
database and cannot use them directly...

Best regards

El mié., 7 ago. 2019 a las 10:15, Olivier Mascia (<[hidden email]>)
escribió:

> > Le 7 août 2019 à 09:43, Ainhoa B <[hidden email]> a écrit :
> >
> > So, in SQLite, it doesn't matter if I create a table with a column of
> int,
> > smallint or long type, it will always be trated as a INTEGER type of 64
> > bits?
>
> Regarding integers, yes they are always signed 64 bits integers.  See
> https://www.sqlite.org/datatype3.html for more details.
>
> When physically storing the integer value, the internal format is varying
> on the magnitude of the value stored. There is nothing really lost
> (storage-wise) by using signed 64 bits integers at the interface level,
> even though your values would only be 0, 1, 3, and 12347.
>
> SQLite will store signed integers using 8, 16, 24, 32, 48 or 64 bits
> payloads.  Values 0 and 1 even only store their type, with no payload.
>
> https://www.sqlite.org/fileformat2.html#record_format
>
> —
> Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten
> Grüßen,
> Olivier Mascia
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Problem with int and DateTime types with EntityFrameWorkCore.Sqlite

Hick Gunter
There is no decimal type in SQLite, and you are lucky that they are converted to string instead of real, because you cannot do proper (implied) decimal (point) arithmetic with real (ieee binary floating point) values. The rounding errors intruduced by binary floating point not beeing able to represent many decimal fractions exactly will go forth and multiply.

I suggest using integers to store the values and keeping track of their scale in a separate integer or implied in program logic (e.g. storing amounts in cents and remembering to divide by 100 for presentation.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Ainhoa B
Gesendet: Mittwoch, 07. August 2019 11:46
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Problem with int and DateTime types with EntityFrameWorkCore.Sqlite

And what about Decimal type? Because Decimals are converted to string. It's a problem because it forces the programmer to format read values from database and cannot use them directly...

Best regards

El mié., 7 ago. 2019 a las 10:15, Olivier Mascia (<[hidden email]>)
escribió:

> > Le 7 août 2019 à 09:43, Ainhoa B <[hidden email]> a écrit :
> >
> > So, in SQLite, it doesn't matter if I create a table with a column
> > of
> int,
> > smallint or long type, it will always be trated as a INTEGER type of
> > 64 bits?
>
> Regarding integers, yes they are always signed 64 bits integers.  See
> https://www.sqlite.org/datatype3.html for more details.
>
> When physically storing the integer value, the internal format is
> varying on the magnitude of the value stored. There is nothing really
> lost
> (storage-wise) by using signed 64 bits integers at the interface
> level, even though your values would only be 0, 1, 3, and 12347.
>
> SQLite will store signed integers using 8, 16, 24, 32, 48 or 64 bits
> payloads.  Values 0 and 1 even only store their type, with no payload.
>
> https://www.sqlite.org/fileformat2.html#record_format
>
> —
> Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit
> besten Grüßen, Olivier Mascia
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users