Hello,
2013/5/24 Noel Grandin <noelgran...@gmail.com>
>
> 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%2bunsubscr...@googlegroups.com>
>> .
>> To post to this group, send email to h2-database@googlegroups.com.
>> 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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
