Hello,
2013/5/24 Noel Grandin <[email protected]>
>
> I can't see that adding full support is likely, since UNSIGNED datatypes
> are hardly a SQL standard.
>
You're right. The SQL:2008 Standard actually refers to numbers as such:
4.4.1 Introduction to numbers
"[...] For every numeric type, the least value is less than zero and
the greatest value is greater than zero."
In fact, this means that the SQL standard "forbids" unsigned numbers :-)
Nonetheless, many databases "enhance" the SQL standard. One of those
databases is H2. The best example for standard enhancements and liberal
interpretations are:
- H2's ARRAY support
- H2's MERGE statement
One good reason why unsigned integers are useful is the fact that people
tend to use only positive numbers for keys.
> After all, the primary purpose of supporting them is to make running unit
> tests for people who are MySQL easier, so it's not like it's a production
> situation.
>
Yes, that's the primary purpose right now. That's why I propose a new
purpose, at least for the record / roadmap.
N.B: These databases also support unsigned integer numbers in some way:
- SQLite (I think)
- SQL Server's (TINYINT only)
- Sybase ASE
- Sybase SQL Anywhere
But I can see that making them work a little better would be a good idea.
> Your best bet is to dig around the Parser class to add support.
I'm just tossing around ideas, I think it would be too early in the
discussion to implement anything on my side...
Cheers
Lukas
On 2013-05-24 10:29, Lukas Eder wrote:
>
>> Hello,
>>
>> I understand that H2 syntactically supports the UNSIGNED keyword in DDL
>> to stay compatible with MySQL:
>>
>> CREATE TABLE t_unsigned (
>> u_byte tinyint unsigned,
>> u_short smallint unsigned,
>> u_int int unsigned,
>> u_long bigint unsigned
>> );
>>
>> Obviously, this is just a syntax compatibility, as I cannot insert this:
>>
>> INSERT INTO t_unsigned (u_byte) VALUES (255);
>>
>> Would it make sense to enhance the H2 database in order to go a bit
>> further on UNSIGNED support? There are two alternative routes:
>>
>> ------------------------------**------------------------------**
>> --------------
>> 1. Simple fix: Just "upgrade" integer types in storage, and let the above
>> table be equivalent to this one:
>>
>> CREATE TABLE t_unsigned (
>> u_byte smallint,
>> u_short integer,
>> u_int bigint,
>> u_long number(20)
>> );
>>
>> 2. Sophisticated fix: Store unsigned numbers in signed number containers,
>> but fix all relevant JDBC methods to produce the exact value:
>>
>> "255".equals(rs.getString("u_**byte"));
>> 255 == rs.getInt("u_byte");
>> (short) 255 == rs.getShort("u_byte");
>> (byte) -1 == rs.getByte("u_byte"); // This would be "expected"
>> ------------------------------**------------------------------**
>> --------------
>>
>> The simple fix would probably cause new issues in the long run. Besides,
>> it would allow values that are out of range, e.g. 1000 for u_byte. The
>> sophisticated fix would mean a bit of work, specifically because all the
>> arithmetic operations would need to be adapted. Consider
>>
>> cast((u_byte + u_byte) as smallint unsigned)
>>
>> For u_byte == 255 (stored as -1), this would have to return 510, instead
>> of -2
>>
>> What do you think?
>>
>> Cheers
>> Lukas
>> --
>> You received this message because you are subscribed to the Google Groups
>> "H2 Database" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to
>> h2-database+unsubscribe@**googlegroups.com<h2-database%[email protected]>
>> .
>> To post to this group, send email to [email protected].
>> Visit this group at
>> http://groups.google.com/**group/h2-database?hl=en<http://groups.google.com/group/h2-database?hl=en>
>> .
>> For more options, visit
>> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out>
>> .
>>
>>
>>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
