Re: [sqlite] Unsigned

[Jens Alfke]

> On Aug 25, 2018, at 5:21 PM, D Burgess  wrote:
> 
> The data includes unsigned 32 bit integers which are stored as float
> (don't ask me why, I guess the primary reason is the same reason that
> 32bit Lua uses floats as integers).

That seems like a mistake on their part, since unsigned 32-bit ints can 
obviously be stored in SQLite as 64-bit ints (without space penalty, since the 
database uses a variable-length encoding for ints.)

> 2. Mixed 64/32 bit system that has integers that use the full 64 bits.
> Numbers are sourced by realtime hardware.
> Absence of 64 bit unsigned means addition of few functions to handle
> inserts and display representation(s), numbers stored as text/blobs.

As I think I said before, you can store these as signed ints. Addition, 
subtraction and equality tests will work fine. Greater/less comparisons take 
either a bit of SQL logic or a custom C collation function. Other arithmetic 
operations require custom functions. But you save the extra overhead and 
complexity of blobs.

I’m not denying that bignums would be cool. But SQLite is a small database 
engine and there are a lot of cool features it deliberately omits.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[R Smith]

On 2018/08/26 4:47 PM, Thomas Kurz wrote:

But, as noted, you could just store those as blobs, bigendian if you want
sorting, and indexing will work just fine. No other conversion needed.

Yes, I can store *anything* as blobs. When arguing like this, we wouldn't need 
strings, dates, floats or any other type neither.


Since you bring up "arguing like this"  - let me point out that the 
reply offered, in argument terms, is a straw men at best.


For similar functionalities A and B where it is not shown that B is 
required/needed/useful in the general case, does not in any way obviate 
the need (by precedent or otherwise) for A which IS shown to be useful 
in the general case.


By example, if a person offers a good reason why cars should have 5 
wheels, with good motivation too, say an added wheel in the center under 
the car would assist greatly with navigating huge bumps in the road - 
which might be objectively true, but until it can be shown that "huge 
bumps in the road" is sufficiently "general" a case to warrant the 
change, that person may still request it, but not require it.


More to the point of argument, if the request for 5-wheeled cars does 
not find implementation, it in no way obviates the need for 4-wheeled 
cars, so saying "well then we might just as well also not have 4 wheeled 
cars" is simply an appeal to false precedent.



Btw, my point is only towards the argument offered, I have no thoughts 
on the UINT itself - it would probably be rather nice to have.


Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[Thomas Kurz]

> But, as noted, you could just store those as blobs, bigendian if you want
> sorting, and indexing will work just fine. No other conversion needed.

Yes, I can store *anything* as blobs. When arguing like this, we wouldn't need 
strings, dates, floats or any other type neither.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[Wout Mertens]

On Sun, Aug 26, 2018, 2:21 AM D Burgess  wrote:


> 2. Mixed 64/32 bit system that has integers that use the full 64 bits.
> Numbers are sourced by realtime hardware.
> Absence of 64 bit unsigned means addition of few functions to handle
> inserts and display representation(s), numbers stored as text/blobs.
> Again this all works, just extra code and indexes are less than
> optimum (compared to previous used mysql).
>

But, as noted, you could just store those as blobs, bigendian if you want
sorting, and indexing will work just fine. No other conversion needed.

>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[D Burgess]

The original question was that I was curious about the history.

Noting where we are now at, I will give as examples of two real world
applications:

1. 32 bit embedded sqlite. Realtime storing data from various hardware
interfaces.
The data includes unsigned 32 bit integers which are stored as float
(don't ask me why, I guess the primary reason is the same reason that
32bit Lua uses floats as integers).
There is heavy (unsigned) arithmetic computation at database read/write time.
The application is stable. The maintainers/developers rue the
additional code because of sqlite and no native unsigned type.
They have a historic codebase for 3 other database systems which they
have previously used.

2. Mixed 64/32 bit system that has integers that use the full 64 bits.
Numbers are sourced by realtime hardware.
Absence of 64 bit unsigned means addition of few functions to handle
inserts and display representation(s), numbers stored as text/blobs.
Again this all works, just extra code and indexes are less than
optimum (compared to previous used mysql).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[Darren Duncan]

On 2018-08-24 11:58 PM, Thomas Kurz wrote:

What is the value of a built-in UNSIGNED type when we already have INTEGER?  I

can't think of any. -- Darren Duncan

Signed integers only allow half the range of values of unsigned ones. You 
cannot store a pointer value in them. (You can by casting to signed, but then 
sorting is done wrong.)


I fully agree with what others have said, which is that this use case is better 
handled by supporting an unlimited precision integer type.  All the "unsigned" 
request argues for is a single extra bit of precision for positive integers, 
which is like nit-picking; in practice if you can't fit all the integers you 
care about in a typical signed machine integer then the unlimited type is the 
only reasonable step up, for practical purposes. -- Darren Duncan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[Thomas Kurz]

> What is the value of a built-in UNSIGNED type when we already have INTEGER?  
> I 
can't think of any. -- Darren Duncan

Signed integers only allow half the range of values of unsigned ones. You 
cannot store a pointer value in them. (You can by casting to signed, but then 
sorting is done wrong.)

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[Darren Duncan]

On 2018-08-20 11:46 PM, D Burgess wrote:

Is there a historical reason why sqlite does not have a UNSIGNED type
to go with INTEGER?


What is the value of a built-in UNSIGNED type when we already have INTEGER?  I 
can't think of any. -- Darren Duncan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[Jens Alfke]

> On Aug 23, 2018, at 4:22 AM, Wout Mertens  wrote:
> 
> I don't understand, can you not just use blobs as primary keys?

You can. And while the usual bignum encodings won't sort correctly with the 
default blob collation, you can get around that with clever encoding. 

For example, for unsigned bignums you just start with a byte containing the 
byte-count, followed by the bignum bytes in big-endian order. Signed bignums 
are a little trickier, but not much (left as an exercise for the reader…)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[Wout Mertens]

I don't understand, can you not just use blobs as primary keys?

$ sqlite3
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(id blob primary key);
sqlite> insert into t values(randomblob(8));
sqlite> insert into t values(randomblob(8));
sqlite> insert into t values(randomblob(8));
sqlite> insert into t values(randomblob(8));
sqlite> select hex(id) from t;
1499C74FAF191054
2DDC29C2D6C72CD8
6BFD11FD9A446A56
99B86AA30E484BCB
sqlite> explain query plan select * from t where id=randomblob(8);
QUERY PLAN
`--SEARCH TABLE t USING COVERING INDEX sqlite_autoindex_t_1 (id=?)
sqlite> select hex(id) from t where id < randomblob(8);
1499C74FAF191054
2DDC29C2D6C72CD8
6BFD11FD9A446A56


On Thu, Aug 23, 2018 at 12:30 AM D Burgess  wrote:

> To answer Jens - electronic IDs.
>
> And yes I use bignums on the client side.
>
> Note that I have workarounds and the system I have is stable and
> works, I just rue not having 64 bits and UNSIGNED indexes.
> There was additional work to get it all going in SQLite. I probably
> would not have originally chosen SQLite if I had known what I know
> now.
>
> The reason for the original question was that a friend in a different
> industry asked me the same question.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[D Burgess]

To answer Jens - electronic IDs.

And yes I use bignums on the client side.

Note that I have workarounds and the system I have is stable and
works, I just rue not having 64 bits and UNSIGNED indexes.
There was additional work to get it all going in SQLite. I probably
would not have originally chosen SQLite if I had known what I know
now.

The reason for the original question was that a friend in a different
industry asked me the same question.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[Thomas Kurz]

+1 for that or a true bignum support (i.e. without any length restriction). The 
latter would be perfect as it would provide support for storing numbers of 
encryption keys (RSA, etc.). And it would also avoid the problem of having to 
extend the bigint range again in a couple of years.



- Original Message - 
From: Simon Slavin 
To: SQLite mailing list 
Sent: Wednesday, August 22, 2018, 20:06:38
Subject: [sqlite] Unsigned

On 22 Aug 2018, at 6:04pm, Jens Alfke  wrote:

> Bignums make unsigned types irrelevant, if the only reason you need unsigned 
> is to store 64-bit positive integers. As others have said, "unsigned" is a 
> constraint, not a type.

Agreed.

> Bignums would be nice; but you could implement them yourself by storing them 
> as blobs and defining functions to operate on them, which would call into 
> some available C bignum library.

What seems to be needed here is not BIGNUM but would better be called BIGINT.  
However, IMHO SQLite should not complicate itself by incorporating a new 
datatype.  That would lead to a lot of extra CAST() programming and testing.   
Instead it should expand the capabilities of the existing INTEGER type.  
Looking at section 2.1 of <https://www.sqlite.org/datatype3.html> suggests 
using a new Serial Type code of, for the sake of argument, 10 to mean "Value is 
a big-endian 128-bit twos-complement integer.".

This would create schema format 5, and introduce a possible compatibility 
problem if a database with a big integer was presented to an earlier version of 
SQLite.  But I think it would represent the minimum of backward compatibility 
problems.  But I haven't read the source code so I don't know for sure.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[Simon Slavin]

On 22 Aug 2018, at 6:04pm, Jens Alfke  wrote:

> Bignums make unsigned types irrelevant, if the only reason you need unsigned 
> is to store 64-bit positive integers. As others have said, "unsigned" is a 
> constraint, not a type.

Agreed.

> Bignums would be nice; but you could implement them yourself by storing them 
> as blobs and defining functions to operate on them, which would call into 
> some available C bignum library.

What seems to be needed here is not BIGNUM but would better be called BIGINT.  
However, IMHO SQLite should not complicate itself by incorporating a new 
datatype.  That would lead to a lot of extra CAST() programming and testing.   
Instead it should expand the capabilities of the existing INTEGER type.  
Looking at section 2.1 of  suggests 
using a new Serial Type code of, for the sake of argument, 10 to mean "Value is 
a big-endian 128-bit twos-complement integer.".

This would create schema format 5, and introduce a possible compatibility 
problem if a database with a big integer was presented to an earlier version of 
SQLite.  But I think it would represent the minimum of backward compatibility 
problems.  But I haven't read the source code so I don't know for sure.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[Jens Alfke]

> On Aug 22, 2018, at 10:04 AM, Randall Smith  wrote:
> 
> BLOBs are useful for storage of binary info (e.g., a file), but they are not 
> human readable and require complex conversion when inserting and extracting 
> info from the DB.  

*Shrug* I work with lots of non-human-readable data so I don't see it as a 
major problem :) And the sqlite blob APIs are pretty simple (just pass a 
pointer and a size.)

> AFAIK you can't search or index on them.

Yes you can. Blobs support equality tests. If you need to compare them or 
create an index, you just define a custom collation function (I've done it; 
it's pretty easy.)

—Jens

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[Randall Smith]

>>> From: D Burgess 

>>> You can just store [large integers as] binary blobs and interpret then in 
>>> the client, no? Or do

>>> you need to do arithmetic on them?



BLOBs are useful for storage of binary info (e.g., a file), but they are not 
human readable and require complex conversion when inserting and extracting 
info from the DB.  AFAIK you can't search or index on them.  They're a poor 
substitute for an INTEGER.



(FWIW I ended up using a TEXT representation of a large integer, with a custom 
adapter to convert things back and forth.  This can at least be read by a 
person when looking at data dumps and so on.  I considered this the least bad 
of several bad alternatives!  Obviously a native long integer type with normal 
arithmetic support is the proper solution.)



Randall.






___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[Jens Alfke]

> On Aug 21, 2018, at 9:54 AM, Randall Smith  wrote:
> 
> I would like to enthusiastically second not only this as a feature request, 
> but also request arbitrary-length (or at least much longer length) INTEGER 
> values, as are possible in other SQL dialects.  

Bignums make unsigned types irrelevant, if the only reason you need unsigned is 
to store 64-bit positive integers. As others have said, "unsigned" is a 
constraint, not a type.

Bignums would be nice; but you could implement them yourself by storing them as 
blobs and defining functions to operate on them, which would call into some 
available C bignum library.

> There may have been a time in the past when 63 bits was all one could ever 
> conceivably need, but I think we have moved past that time now.  E.g., I have 
> a common need to store software address values, which are (currently!) 64-bit 
> unsigned, and have had to jump through ridiculous hoops in SQLite to do it.  
> Integers in this range, and larger, seem like they are in common use today.

If you're using the C API, you can store and retrieve uint64_t values by 
casting them to int64_t on the way into the SQLite API and back to uint64_t on 
the way out. (You should be able to do similar things with other APIs, but it 
might look uglier.)

Addition, subtraction and bitwise operations should work fine, except for >> 
because it will shift a 1 into the MSB if the number was >= 2^63.

'<' and '>' will work weirdly because there's a discontinuity at 2^63, so any 
number ≥ 2^63 will test as less than any number < 2^63. But you can either use 
SQL logic to work around that, or define a C collation function to compare 
uint64_t's.

(BTW, I'm curious what these address values are. I thought at first you meant 
IPv6 addresses, but those are 128-bit, not 64-bit. Or if you mean addresses in 
the sense of pointers, SQLite now has a C API for representing pointers.)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[Olivier Mascia]

> Le 22 août 2018 à 00:46, D Burgess  a écrit :
> 
> I currently store them as blobs. A lot of them, 16 bytes (versus
> numeric 8 per item).
> And not optimal for indexes.

Why would you need or use 16 bytes to store  the 8 bytes of a 64 bits integer 
as a blob?

-- 
Best regards, Meilleures salutations, Met vriendelijke groeten,  
Olivier Mascia (from mobile device)



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[D Burgess]

I currently store them as blobs. A lot of them, 16 bytes (versus
numeric 8 per item).
And not optimal for indexes.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[D Burgess]

> You can just store binary blobs and interpret then in the client, no? Or do
you need to do arithmetic on them?

Not arithmetic, but &, |, <<, >>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[Wout Mertens]

You can just store binary blobs and interpret then in the client, no? Or do
you need to do arithmetic on them?

On Tue, Aug 21, 2018, 6:55 PM Randall Smith  wrote:

> >>> Date: Tue, 21 Aug 2018 16:46:48 +1000
> >>> From: D Burgess 
> >>>
> >>> Is there a historical reason why sqlite does not have a UNSIGNED type
> to go with INTEGER?
>
> I would like to enthusiastically second not only this as a feature
> request, but also request arbitrary-length (or at least much longer length)
> INTEGER values, as are possible in other SQL dialects.
>
> There may have been a time in the past when 63 bits was all one could ever
> conceivably need, but I think we have moved past that time now.  E.g., I
> have a common need to store software address values, which are (currently!)
> 64-bit unsigned, and have had to jump through ridiculous hoops in SQLite to
> do it.  Integers in this range, and larger, seem like they are in common
> use today.
>
> Randall.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[Randall Smith]

>>> Date: Tue, 21 Aug 2018 16:46:48 +1000
>>> From: D Burgess 
>>> 
>>> Is there a historical reason why sqlite does not have a UNSIGNED type to go 
>>> with INTEGER?

I would like to enthusiastically second not only this as a feature request, but 
also request arbitrary-length (or at least much longer length) INTEGER values, 
as are possible in other SQL dialects.  

There may have been a time in the past when 63 bits was all one could ever 
conceivably need, but I think we have moved past that time now.  E.g., I have a 
common need to store software address values, which are (currently!) 64-bit 
unsigned, and have had to jump through ridiculous hoops in SQLite to do it.  
Integers in this range, and larger, seem like they are in common use today.

Randall.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[Rowan Worth]

What version is that? I have 3.8.1 handy (ancient I know), which doesn't
support hex literals but:

sqlite> select cast(9223372036854775808 as integer);
-9223372036854775808

Which is different to your result...
-Rowan

On 21 August 2018 at 17:19, D Burgess  wrote:

> My problem is getting handling unsigned integers that have the high
> bit set (i.e. negative)
> (assume 64bit)
> if I insert 0x8000 (i.e. 9223372036854775808), I would
> like to be able to select and get the same unsigned decimal number
> back.
>
> select 0x8000,cast(9223372036854775808 as
> integer),printf('%lu %ld 0x%0X 0x%0X',
> 0x8000,0x8000,0x8000,
> 9223372036854775808);
>
> -9223372036854775808|9223372036854775807|9223372036854775808
> -9223372036854775808 0x8000 0x7FFF
>
> The above select shows the issues.
>
>
> On Tue, Aug 21, 2018 at 6:25 PM, Rowan Worth  wrote:
> > sqlite is pretty loose about types. The column definitions don't
> constrain
> > what is stored in the rows at all:
> >
> > sqlite> CREATE TABLE a(c INTEGER);
> > sqlite> INSERT INTO a VALUES ("fourty-two");
> > sqlite> SELECT * FROM a;
> > fourty-two
> >
> > So "UNSIGNED" seems kind of pointless as it's implies a further
> constraint
> > which is not going to be honoured. Note that sqlite does support actual
> > constraints via the CHECK clause:
> >
> > sqlite> CREATE TABLE b(c INTEGER, CHECK (c >= 0));
> > sqlite> INSERT INTO b VALUES (-15);
> > Error: constraint failed
> > sqlite> INSERT INTO b VALUES (15);
> >
> > Although this is still allowed:
> >
> > sqlite> INSERT INTO b VALUES ("twenty");
> > sqlite> SELECT * FROM b;
> > 15
> > twenty
> >
> > You can disallow it if you get even more specific:
> >
> > sqlite> CREATE TABLE b2(c INTEGER, CHECK (TYPEOF(c) == 'integer' AND c >=
> > 0));
> > sqlite> INSERT INTO b2 VALUES ("twenty");
> > Error: constraint failed
> > sqlite> INSERT INTO b2 VALUES (0);
> > sqlite> INSERT INTO b2 VALUES (-1);
> > Error: constraint failed
> > sqlite> INSERT INTO b2 VALUES (1);
> > sqlite> SELECT * FROM b2;
> > 0
> > 1
> >
> > Note that the type in the column definition does have an effect - it
> > defines the column's "affinity" and may change the way data is stored.
> For
> > example:
> >
> > sqlite> INSERT INTO b2 VALUES ("2");
> > Error: constraint failed
> >
> > The TYPEOF check rejects this, but without that constraint:
> >
> > sqlite> INSERT INTO b VALUES ("2");
> > sqlite> SELECT c, TYPEOF(c) FROM b;
> > 15|integer
> > twenty|text
> > 2|integer
> >
> > ie. the text data we tried to insert was converted to an integer for
> > storage.
> >
> > Further reading: https://www.sqlite.org/datatype3.html
> >
> > -Rowan
> >
> >
> > On 21 August 2018 at 14:46, D Burgess  wrote:
> >
> >> Is there a historical reason why sqlite does not have a UNSIGNED type
> >> to go with INTEGER?
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[D Burgess]

My problem is getting handling unsigned integers that have the high
bit set (i.e. negative)
(assume 64bit)
if I insert 0x8000 (i.e. 9223372036854775808), I would
like to be able to select and get the same unsigned decimal number
back.

select 0x8000,cast(9223372036854775808 as
integer),printf('%lu %ld 0x%0X 0x%0X',
0x8000,0x8000,0x8000,9223372036854775808);

-9223372036854775808|9223372036854775807|9223372036854775808
-9223372036854775808 0x8000 0x7FFF

The above select shows the issues.


On Tue, Aug 21, 2018 at 6:25 PM, Rowan Worth  wrote:
> sqlite is pretty loose about types. The column definitions don't constrain
> what is stored in the rows at all:
>
> sqlite> CREATE TABLE a(c INTEGER);
> sqlite> INSERT INTO a VALUES ("fourty-two");
> sqlite> SELECT * FROM a;
> fourty-two
>
> So "UNSIGNED" seems kind of pointless as it's implies a further constraint
> which is not going to be honoured. Note that sqlite does support actual
> constraints via the CHECK clause:
>
> sqlite> CREATE TABLE b(c INTEGER, CHECK (c >= 0));
> sqlite> INSERT INTO b VALUES (-15);
> Error: constraint failed
> sqlite> INSERT INTO b VALUES (15);
>
> Although this is still allowed:
>
> sqlite> INSERT INTO b VALUES ("twenty");
> sqlite> SELECT * FROM b;
> 15
> twenty
>
> You can disallow it if you get even more specific:
>
> sqlite> CREATE TABLE b2(c INTEGER, CHECK (TYPEOF(c) == 'integer' AND c >=
> 0));
> sqlite> INSERT INTO b2 VALUES ("twenty");
> Error: constraint failed
> sqlite> INSERT INTO b2 VALUES (0);
> sqlite> INSERT INTO b2 VALUES (-1);
> Error: constraint failed
> sqlite> INSERT INTO b2 VALUES (1);
> sqlite> SELECT * FROM b2;
> 0
> 1
>
> Note that the type in the column definition does have an effect - it
> defines the column's "affinity" and may change the way data is stored. For
> example:
>
> sqlite> INSERT INTO b2 VALUES ("2");
> Error: constraint failed
>
> The TYPEOF check rejects this, but without that constraint:
>
> sqlite> INSERT INTO b VALUES ("2");
> sqlite> SELECT c, TYPEOF(c) FROM b;
> 15|integer
> twenty|text
> 2|integer
>
> ie. the text data we tried to insert was converted to an integer for
> storage.
>
> Further reading: https://www.sqlite.org/datatype3.html
>
> -Rowan
>
>
> On 21 August 2018 at 14:46, D Burgess  wrote:
>
>> Is there a historical reason why sqlite does not have a UNSIGNED type
>> to go with INTEGER?
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[Rowan Worth]

sqlite is pretty loose about types. The column definitions don't constrain
what is stored in the rows at all:

sqlite> CREATE TABLE a(c INTEGER);
sqlite> INSERT INTO a VALUES ("fourty-two");
sqlite> SELECT * FROM a;
fourty-two

So "UNSIGNED" seems kind of pointless as it's implies a further constraint
which is not going to be honoured. Note that sqlite does support actual
constraints via the CHECK clause:

sqlite> CREATE TABLE b(c INTEGER, CHECK (c >= 0));
sqlite> INSERT INTO b VALUES (-15);
Error: constraint failed
sqlite> INSERT INTO b VALUES (15);

Although this is still allowed:

sqlite> INSERT INTO b VALUES ("twenty");
sqlite> SELECT * FROM b;
15
twenty

You can disallow it if you get even more specific:

sqlite> CREATE TABLE b2(c INTEGER, CHECK (TYPEOF(c) == 'integer' AND c >=
0));
sqlite> INSERT INTO b2 VALUES ("twenty");
Error: constraint failed
sqlite> INSERT INTO b2 VALUES (0);
sqlite> INSERT INTO b2 VALUES (-1);
Error: constraint failed
sqlite> INSERT INTO b2 VALUES (1);
sqlite> SELECT * FROM b2;
0
1

Note that the type in the column definition does have an effect - it
defines the column's "affinity" and may change the way data is stored. For
example:

sqlite> INSERT INTO b2 VALUES ("2");
Error: constraint failed

The TYPEOF check rejects this, but without that constraint:

sqlite> INSERT INTO b VALUES ("2");
sqlite> SELECT c, TYPEOF(c) FROM b;
15|integer
twenty|text
2|integer

ie. the text data we tried to insert was converted to an integer for
storage.

Further reading: https://www.sqlite.org/datatype3.html

-Rowan


On 21 August 2018 at 14:46, D Burgess  wrote:

> Is there a historical reason why sqlite does not have a UNSIGNED type
> to go with INTEGER?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unsigned

[D Burgess]

Is there a historical reason why sqlite does not have a UNSIGNED type
to go with INTEGER?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNSIGNED BIG INT in SQLite 3.7.11.

[Hick Gunter]

Of course it does. But if the OP inserted his as yet unspecified large value 
using the sqlite3_bind_int() call, then the shell would render it as a negative 
value. He needs to use the sqlite3_bind_int64() interface instead...

-Ursprüngliche Nachricht-
Von: Richard Hipp [mailto:d...@sqlite.org]
Gesendet: Freitag, 06. September 2013 16:04
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] UNSIGNED BIG INT in SQLite 3.7.11.

On Fri, Sep 6, 2013 at 10:00 AM, Hick Gunter <h...@scigames.at> wrote:

>
> My guess is that you have exceeded the range of a 32 bit integer
> (0x7FFF) which will be rendered as a negative number by the SQLite
> shell.
>

The shell works just fine with 64-bit signed integers:

SQLite version 3.8.0.1 2013-08-29 17:35:01 Enter ".help" for instructions Enter 
SQL statements terminated with a ";"
sqlite> select 1024*1024*1024*1024*1024;
1125899906842624



--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNSIGNED BIG INT in SQLite 3.7.11.

[Richard Hipp]

On Fri, Sep 6, 2013 at 10:00 AM, Hick Gunter  wrote:

>
> My guess is that you have exceeded the range of a 32 bit integer
> (0x7FFF) which will be rendered as a negative number by the SQLite
> shell.
>

The shell works just fine with 64-bit signed integers:

SQLite version 3.8.0.1 2013-08-29 17:35:01
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select 1024*1024*1024*1024*1024;
1125899906842624



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNSIGNED BIG INT in SQLite 3.7.11.

[Hick Gunter]

What is the size in bits of your UNSIGNED BIG INT? SQLite supports only SIGNED 
integers in 32 and 64 bits, so to properly store/retrieve UNSIGNED values you 
need to typecast.

My guess is that you have exceeded the range of a 32 bit integer (0x7FFF) 
which will be rendered as a negative number by the SQLite shell.

-Ursprüngliche Nachricht-
Von: Filip Curcic [mailto:curcic.fi...@gmail.com]
Gesendet: Freitag, 06. September 2013 13:21
An: sqlite-users@sqlite.org
Betreff: [sqlite] UNSIGNED BIG INT in SQLite 3.7.11.

Hello,

I am using Android 4.2 with SQLite 3.7.11 database.
I am not being able to insert UNSIGNED BIG INT data properly. It looks to me 
like an overflow, because when I read it out I get a negative number.

That only happens on this device, I have another 2.3 Andriod device which runs 
older version of SQLite, and it does not happen.

Is this maybe a known problem?

Thank you

--
Filip Curcic
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UNSIGNED BIG INT in SQLite 3.7.11.

[Filip Curcic]

Hello,

I am using Android 4.2 with SQLite 3.7.11 database.
I am not being able to insert UNSIGNED BIG INT data properly. It looks to
me like an overflow, because when I read it out I get a negative number.

That only happens on this device, I have another 2.3 Andriod device which
runs older version of SQLite, and it does not happen.

Is this maybe a known problem?

Thank you

-- 
Filip Ćurčić
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unsigned int 64 in WHERE

[Vivien Malerba]

Hi!

I have a table with a timestamp column which I use to insert sqlite_uint64
values using sqlite3_bind_int64()
(values are retreived using sqlite3_column_int64() with a cast to
sqlite_uint64). This works fine with the C API.

The problem is that when I try to use the sqlite3 command line, if I use:
SELECT data FROM mytable WHERE ts=18446744072622041475;
I get no result at all (the 18446744072622041475 value is too big to fit
into a signed 64 bit integer, but it fits in an unsigned 64 bits integer),
so I have to use
SELECT data FROM mytable WHERE ts=-1087510141;

In other terms, I have to use the signed 64 bits integer in my WHERE clause
(2^64-1087510141 = 1844674407262204147).

Is there any other way of storing an unsigned 64 bits integer in the
database than storing it as a signed value and casting its binary value to
unsigned, for example if there was a sqlite3_bind_Uint64() and a
sqlite3_column_Uint64()?

Thanks a lot,

Vivien
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned 64 bit integers

[Alex Katebi]

No. The range is for 64 bit signed. SQLite has manifest typing it is like
Ruby Duck Typing or Dynamic Typing. The value defines the type not the
column type or lack there of it.

If you value is a small integer it will only use 1 byte.

On Sat, Jun 28, 2008 at 6:02 PM, freeav8r <[EMAIL PROTECTED]> wrote:

> Hi.  I have a newbie question.
>
> When trying to store 64-bit unsiged integers in sqlite, some of them come
> back as floats.  There is some internal reference on the web page to 64-bit
> unsiged integers; http://www.sqlite.org/c3ref/int64.html.  On the other
> hand, the faq's entry on AUTOINCREMENT fields suggests that they may not be
> supported above 9223372036854775807.
>
> Does sqlite support 64-bit unsigned integers?
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unsigned 64 bit integers

[freeav8r]

Hi.  I have a newbie question.  

When trying to store 64-bit unsiged integers in sqlite, some of them come back 
as floats.  There is some internal reference on the web page to 64-bit unsiged 
integers; http://www.sqlite.org/c3ref/int64.html.  On the other hand, the faq's 
entry on AUTOINCREMENT fields suggests that they may not be supported above 
9223372036854775807.

Does sqlite support 64-bit unsigned integers? 




  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] unsigned integer, text, tutorial, and keys

[Christophe]

Hello,

I have few questions on SQLite3, i'm using it through
pdo with php5.

- is unsigned int available?

- can we insert a row with an autoinc primary key, so
I don't need to provide it when I insert a new row?

- if NULL values are possible for primary keys, then
autoinc key can't have this NULL on creation but can
get it on updates if we updates the key with NULL.
Right ?

-if NULL values are possibles and primary key is not
autoincremented then we have to supply the key during
insert, then there is a risk of having a NOT UNIQUE
error during insert and also during updates if we
supply a NULL value during updates. Right ? 

- As I understood, it's recommended to add NOT NULL
for primary keys because SQLite allow NULL keys which
are equals to the first signed int (-xxx48) so it's
not unique if we have one row already and provide a
NOT UNIQUE error. Right ?

-is there a way to manage foreign keys ? 

If unsigned int available for SQLite3 :
- can integer autoincrement primary keys be unsigned ?
- if we have an unsigned int primary key, with
autoinc, does it begins with 0 or 1 ? 

About text type:
- What is the maximum value of a text data ?
- Do (') have to be escaped (\') in the text string ? 
- is there any ready made function in php to escape
those (')?

Web links:
-Do you know good web tutorials, code samples,
opensource projects showing how to use SQLite mainly
with php and pdo ? 

I read about it on php.net, litewebsite.com and
sqlite.org indeed.


Thanks
David


  
_ 
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] unsigned shorts in bindInt on ARM

[Jim Dodgen]

I suspect a little vs. big endian problem.


chetana bhargav wrote:

Hi,

I saw a strnage problem using unsigned shorts, not sure if any one saw that, I 
was using an unsigned short for binding it to integer, the lint wasn't 
complaining and everything seem to go ahead fine, when I tested that on 
windows, all my querys were returning as expected. But when I moved my code to 
ARM platform, all my queries were failing, the reason was that as I used 
unsigned short for binding with int, it was taking zero.

Any idea why this would be so, is it compiler problem as to how it intrepets 
that data.? Of course when I changed the data type to int everything went ahead 
fine. Wanted to know if any one saw this.

Just wanted to let people know if anyone intends to use uint16's on ADS1.2 be 
prepared for strange results.


...
Chetana.


  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] unsigned shorts in bindInt on ARM

[chetana bhargav]

Hi,

I saw a strnage problem using unsigned shorts, not sure if any one saw that, I 
was using an unsigned short for binding it to integer, the lint wasn't 
complaining and everything seem to go ahead fine, when I tested that on 
windows, all my querys were returning as expected. But when I moved my code to 
ARM platform, all my queries were failing, the reason was that as I used 
unsigned short for binding with int, it was taking zero.

Any idea why this would be so, is it compiler problem as to how it intrepets 
that data.? Of course when I changed the data type to int everything went ahead 
fine. Wanted to know if any one saw this.

Just wanted to let people know if anyone intends to use uint16's on ADS1.2 be 
prepared for strange results.


...
Chetana.