How to Handle BigInt

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

How to Handle BigInt

dmp
Hello,

Given a conversion from a database table that contains BigInt, long,
field from PostgreSQL to a SQLite similar table.

CREATE TABLE postgresqltypes (
  data_type_id serial,
  bigInt_type bigint)

CREATE TABLE sqlitetypes (
  data_type_id INTEGER PRIMARY KEY AUTOINCREMENT,
  int_type INTEGER)

How to store, for values outside range for Integer,
String or Real?

danap.

_______________________________________________
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: How to Handle BigInt

Paul Sanderson
SQLite integers are all 64 bit - I don't about postgress, so unless
postgress allows integers bigger than 64 bit, and you use them, you should
be OK with your table definitions above.

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 1 May 2018 at 16:29, dmp <[hidden email]> wrote:

> Hello,
>
> Given a conversion from a database table that contains BigInt, long,
> field from PostgreSQL to a SQLite similar table.
>
> CREATE TABLE postgresqltypes (
>   data_type_id serial,
>   bigInt_type bigint)
>
> CREATE TABLE sqlitetypes (
>   data_type_id INTEGER PRIMARY KEY AUTOINCREMENT,
>   int_type INTEGER)
>
> How to store, for values outside range for Integer,
> String or Real?
>
> danap.
>
> _______________________________________________
> 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: How to Handle BigInt

Gerry Snyder-4
From the docs:

*INTEGER*. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8
bytes depending on the magnitude of the value.

So perhaps you should have said " SQLite integers are all up to 64 bit."

Gerry

On Tue, May 1, 2018 at 8:56 AM, Paul Sanderson <[hidden email]
> wrote:

> SQLite integers are all 64 bit - I don't about postgress, so unless
> postgress allows integers bigger than 64 bit, and you use them, you should
> be OK with your table definitions above.
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-
> Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
> On 1 May 2018 at 16:29, dmp <[hidden email]> wrote:
>
> > Hello,
> >
> > Given a conversion from a database table that contains BigInt, long,
> > field from PostgreSQL to a SQLite similar table.
> >
> > CREATE TABLE postgresqltypes (
> >   data_type_id serial,
> >   bigInt_type bigint)
> >
> > CREATE TABLE sqlitetypes (
> >   data_type_id INTEGER PRIMARY KEY AUTOINCREMENT,
> >   int_type INTEGER)
> >
> > How to store, for values outside range for Integer,
> > String or Real?
> >
> > danap.
> >
> > _______________________________________________
> > 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
>
_______________________________________________
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: How to Handle BigInt

Paul Sanderson
Perhaps, but that is only part of the story, and all of that is hidden from
the user and is only relevant in terms of how the number is stored on disk.
You can define a column as int, smallint, largeint, bigint, etc and,
irrespective of which you use, SQLite will save the data to disk
transparently using the smallest on disk format possible for the value you
have saved.

For the record there is another possibility for the values 0 and 1 where
SQLite uses no storage at all, other than the type byte in the serial types
array.

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 1 May 2018 at 17:05, Gerry Snyder <[hidden email]> wrote:

> From the docs:
>
> *INTEGER*. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8
> bytes depending on the magnitude of the value.
>
> So perhaps you should have said " SQLite integers are all up to 64 bit."
>
> Gerry
>
> On Tue, May 1, 2018 at 8:56 AM, Paul Sanderson <
> [hidden email]
> > wrote:
>
> > SQLite integers are all 64 bit - I don't about postgress, so unless
> > postgress allows integers bigger than 64 bit, and you use them, you
> should
> > be OK with your table definitions above.
> >
> > Paul
> > www.sandersonforensics.com
> > skype: r3scue193
> > twitter: @sandersonforens
> > Tel +44 (0)1326 572786
> > http://sandersonforensics.com/forum/content.php?195-SQLite-
> > Forensic-Toolkit
> > -Forensic Toolkit for SQLite
> > email from a work address for a fully functional demo licence
> >
> > On 1 May 2018 at 16:29, dmp <[hidden email]> wrote:
> >
> > > Hello,
> > >
> > > Given a conversion from a database table that contains BigInt, long,
> > > field from PostgreSQL to a SQLite similar table.
> > >
> > > CREATE TABLE postgresqltypes (
> > >   data_type_id serial,
> > >   bigInt_type bigint)
> > >
> > > CREATE TABLE sqlitetypes (
> > >   data_type_id INTEGER PRIMARY KEY AUTOINCREMENT,
> > >   int_type INTEGER)
> > >
> > > How to store, for values outside range for Integer,
> > > String or Real?
> > >
> > > danap.
> > >
> > > _______________________________________________
> > > 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
> >
> _______________________________________________
> 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
dmp
Reply | Threaded
Open this post in threaded view
|

Re: How to Handle BigInt

dmp
In reply to this post by dmp
> SQLite integers are all 64 bit - I don't about postgress, so unless
> postgress allows integers bigger than 64 bit, and you use them, you should
> be OK with your table definitions above.

> Paul

Hello,

That really provides insight to the real issue, I was having and so therefore
the question.

Since I'm using Java and JDBC I was retrieving numeric fields in PostgreSQL
with getString(), handles all, then using Integer.parseInt(stringValue) for
BigInts in storing to SQLite.

There lies the problem since BigInt values were exceeding the range of
Java Integer. My original solution was to store these as strings, will now
just use Long.parseLong(stringValue) and store has SQLite Integers
properly.

danap.

_______________________________________________
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: How to Handle BigInt

Simon Slavin-3
On 2 May 2018, at 5:22pm, dmp <[hidden email]> wrote:

> Since I'm using Java and JDBC I was retrieving numeric fields in PostgreSQL
> with getString(), handles all, then using Integer.parseInt(stringValue) for BigInts in storing to SQLite.
>
> There lies the problem since BigInt values were exceeding the range of
> Java Integer. My original solution was to store these as strings, will now just use Long.parseLong(stringValue) and store has SQLite Integers
> properly.

Also note that if you store your numbers as strings, indexes on those values will order them as strings.  In other words, searching and sorting will work incorrectly.

It should be possible to get your numbers from a Java numeric variable to a database numeric value without passing them through a string at any point.  And, of course, back out of the database into a numeric variable.  If your database library does not allow this, you have a serious problem.

As a solution purely about SQLite, SQLite has a "black box" type of BLOB.  BLOB is used to store bytes, without putting any interpretation on those bytes.  Although technically you can search and sort BLOBs, it's probably a sign of faulty thinking.  If I was trying to store something in a database which I didn't want interpreted in any way, I'd use a BLOB.

Simon.
_______________________________________________
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: How to Handle BigInt

SQLite mailing list-2
Are there any plans for supporting a true BigInt/HugeInt data type (i.e. without any length restriction) in the near future?


----- Original Message -----
From: Simon Slavin <[hidden email]>
To: SQLite mailing list <[hidden email]>
Sent: Wednesday, May 2, 2018, 19:04:33
Subject: [sqlite] How to Handle BigInt

On 2 May 2018, at 5:22pm, dmp <[hidden email]> wrote:

> Since I'm using Java and JDBC I was retrieving numeric fields in PostgreSQL
> with getString(), handles all, then using Integer.parseInt(stringValue) for BigInts in storing to SQLite.

> There lies the problem since BigInt values were exceeding the range of
> Java Integer. My original solution was to store these as strings, will now just use Long.parseLong(stringValue) and store has SQLite Integers
> properly.

Also note that if you store your numbers as strings, indexes on those values will order them as strings.  In other words, searching and sorting will work incorrectly.

It should be possible to get your numbers from a Java numeric variable to a database numeric value without passing them through a string at any point.  And, of course, back out of the database into a numeric variable.  If your database library does not allow this, you have a serious problem.

As a solution purely about SQLite, SQLite has a "black box" type of BLOB.  BLOB is used to store bytes, without putting any interpretation on those bytes.  Although technically you can search and sort BLOBs, it's probably a sign of faulty thinking.  If I was trying to store something in a database which I didn't want interpreted in any way, I'd use a BLOB.

Simon.
_______________________________________________
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: How to Handle BigInt

Simon Slavin-3
On 2 May 2018, at 6:08pm, Thomas Kurz <[hidden email]> wrote:

> Are there any plans for supporting a true BigInt/HugeInt data type (i.e. without any length restriction) in the near future?

The largest integer storable as an integer is current 2^63-1, which is the value as signed BigInt in many libraries.  In other words, SQLite already does BigInt.  Just the same as SQL Server, MySQL, Postgres and DB2.

I have not seen any plans for anything bigger than that.

Simon.
_______________________________________________
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: How to Handle BigInt

Eric Grange
As quick insight I gleaned from this list sometime ago: if you only need to
be able to search and sort, the blobs can be used.

If you can live with a fixed size (f.i. 256bits), then just use the
fixed-size big-endian representation as blob content.

For variable-length big integers, encode them as variable-length
big-endian, then prepend it with its byte length encoded to a fixed size
big-endian (f.i. a dword), so encoding 123456 would be the blob
x'0000000301E240'  (of course if your biginteger are never going to grow
too large, you could use a word or a byte to encode the length)

SQLite will not be able to do arithmetic on those or convert them to string
or anything, but you can add custom functions to handle that format should
you need them.

Eric


On Thu, May 3, 2018 at 1:54 PM, Simon Slavin <[hidden email]> wrote:

> On 2 May 2018, at 6:08pm, Thomas Kurz <sqlite-users@mailinglists.
> sqlite.org> wrote:
>
> > Are there any plans for supporting a true BigInt/HugeInt data type (i.e.
> without any length restriction) in the near future?
>
> The largest integer storable as an integer is current 2^63-1, which is the
> value as signed BigInt in many libraries.  In other words, SQLite already
> does BigInt.  Just the same as SQL Server, MySQL, Postgres and DB2.
>
> I have not seen any plans for anything bigger than that.
>
> Simon.
> _______________________________________________
> 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
dmp
Reply | Threaded
Open this post in threaded view
|

Re: How to Handle BigInt

dmp
In reply to this post by dmp
> Also note that if you store your numbers as strings, indexes on those
> values will order them as strings.  In other words, searching and sorting
> will work incorrectly.

> It should be possible to get your numbers from a Java numeric variable to
> a database numeric value without passing them through a string at any
> point.  And, of course, back out of the database into a numeric variable.
> If your database library does not allow this, you have a serious problem.
>
>
> As a solution purely about SQLite, SQLite has a "black box" type of BLOB.
> BLOB is used to store bytes, without putting any interpretation on those
> bytes.  Although technically you can search and sort BLOBs, it's probably
> a sign of faulty thinking.  If I was trying to store something in a
> database which I didn't want interpreted in any way, I'd use a BLOB.
>
> Simon.

Hello,

Since the purpose of the code is to replicate a database SQL query
to a memory/file SQLite database then it seems appropriate to maintain
the integrity of the fields as closely as possible.

I shall look at treating the fields for numbers as a generic numeric
variable in Java. The original code derived from obtaining input from
a user, so therefore strings, and parsing to check for valid input
before storing.

PostgreSQL max/min BigInt are fitting in fine as SQLite's Integer.

Thank you for comments.

danap.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users