Re: How to calculate md5sum in a computed column?
Hi,
Yes, BINARY is the right data type for a hash / checksum.
Regards,
Thomas
On Saturday, April 21, 2012, vrotaru...@gmail.com wrote:
On Fri, Apr 20, 2012 at 4:56 PM, Christian MICHON
christian.mic...@gmail.com javascript:_e({}, 'cvml',
'christian.mic...@gmail.com'); wrote:
Hi Steve,
yes, after x millions of records, it would be nice to have the checksum
taking minimal space.
H2 has a BINARY SQL type mapped to byte[]. Most probably, you can't go
lower than that
--
Vasile Rotaru
--
You received this message because you are subscribed to the Google Groups
H2 Database group.
To post to this group, send email to
h2-database@googlegroups.comjavascript:_e({}, 'cvml',
'h2-database@googlegroups.com');
.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com javascript:_e({}, 'cvml',
'h2-database%2bunsubscr...@googlegroups.com');.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
--
You received this message because you are subscribed to the Google Groups H2
Database group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
Re: How to calculate md5sum in a computed column?
Hi Christian,
Is disk space/database size really a concern for your application? If not,
just storing in a varchar(64) makes your code simple.
On Thursday, 19 April 2012 18:50:29 UTC+2, Christian MICHON wrote:
Ah... got it: SHA256 is equivalent to ruby's sha2, and not sha256 from
openssl (this was completely wrong).
In ruby:
require 'digest/sha2'
= true
Digest::SHA2.hexdigest 'sha256'
= 5d5b09f6dcb2d53a5fffc60c4ac0d55fabdf556069d6631545f42aa6e3500f2e
In H2:
CALL HASH('SHA256', STRINGTOUTF8('sha256'), 1);
gives:
X'5d5b09f6dcb2d53a5fffc60c4ac0d55fabdf556069d6631545f42aa6e3500f2e'
5d5b09f6dcb2d53a5fffc60c4ac0d55fabdf556069d6631545f42aa6e3500f2e
So we have a match.
My 1st question still interests me: how to efficiently store this sha256
in H2? Which would be a good data type?
Regards,
Christian
On Thursday, April 19, 2012 6:37:01 PM UTC+2, Christian MICHON wrote:
Yes, I knew about it.
Yet, this gives me 2 problems:
- increase of 32 char to 64 char for storing the hash. Maybe I should
store this in a different way, like binary to save half the space?
- I cannot figure out how to have the same results between hash() in H2
and sha256 from OpenSSL/ruby
As said, I use ruby and in ruby OpenSSL::Digest::Digest.new(sha256) ==
e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855
Could you please show me how to get the same value using hash() in H2?
I tried call hash('SHA256',stringtoutf8('sha256'),1) without success...
Regards,
Christian
On Thursday, April 19, 2012 6:04:36 PM UTC+2, Thomas Mueller wrote:
Hi,
H2 supports SHA256 natively, I would use it instead of MD5:
http://h2database.com/html/functions.html#hash
Regards,
Thomas
On Saturday, April 14, 2012, Christian MICHON wrote:
On Friday, April 13, 2012 9:51:52 PM UTC+2, Christian MICHON wrote:
Hi,
I need to detect repetitions and updates in (relatively) small H2
tables.
As such, I currently implemented using ruby and an external ORM a
md5sum calculation of all the keys concatenated into a string following
their natural order, which is stored back into a specific key.
This involves a lot of communication with the H2 server, and I wish to
have it done natively (using java itself).
(...)
After many experiments, I finally cracked it.
Sharing it here, in case someone else finds this useful one day...
CREATE ALIAS MD5 FOR
org.apache.commons.codec.digest.DigestUtils.md5Hex(java.lang.String);
CREATE TABLE TEST(ID IDENTITY, NAME VARCHAR(255), MD5 VARCHAR(32) AS
MD5(NAME));
INSERT INTO TEST (NAME) VALUES('The quick brown fox jumps over the lazy
dog');
SELECT * FROM TEST ORDER BY ID;
Really powerful tool this H2 db... Congratulations again...
Christian
--
You received this message because you are subscribed to the Google
Groups H2 Database group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/UV44utlLiAYJ.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
--
You received this message because you are subscribed to the Google Groups H2
Database group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/pV11vTiw2DoJ.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
Re: How to calculate md5sum in a computed column?
Hi Steve,
yes, after x millions of records, it would be nice to have the checksum
taking minimal space.
I've some code making this happen in jruby (it was not easy to convert into
byte array as I'm using prepared statements for initial insertions).
Comparing now performance and size.
Thanks
Christian
On Friday, April 20, 2012 12:54:39 PM UTC+2, Steve McLeod wrote:
Hi Christian,
Is disk space/database size really a concern for your application? If not,
just storing in a varchar(64) makes your code simple.
On Thursday, 19 April 2012 18:50:29 UTC+2, Christian MICHON wrote:
Ah... got it: SHA256 is equivalent to ruby's sha2, and not sha256 from
openssl (this was completely wrong).
In ruby:
require 'digest/sha2'
= true
Digest::SHA2.hexdigest 'sha256'
= 5d5b09f6dcb2d53a5fffc60c4ac0d55fabdf556069d6631545f42aa6e3500f2e
In H2:
CALL HASH('SHA256', STRINGTOUTF8('sha256'), 1);
gives:
X'5d5b09f6dcb2d53a5fffc60c4ac0d55fabdf556069d6631545f42aa6e3500f2e'
5d5b09f6dcb2d53a5fffc60c4ac0d55fabdf556069d6631545f42aa6e3500f2e
So we have a match.
My 1st question still interests me: how to efficiently store this sha256
in H2? Which would be a good data type?
Regards,
Christian
On Thursday, April 19, 2012 6:37:01 PM UTC+2, Christian MICHON wrote:
Yes, I knew about it.
Yet, this gives me 2 problems:
- increase of 32 char to 64 char for storing the hash. Maybe I should
store this in a different way, like binary to save half the space?
- I cannot figure out how to have the same results between hash() in H2
and sha256 from OpenSSL/ruby
As said, I use ruby and in ruby OpenSSL::Digest::Digest.new(sha256) ==
e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855
Could you please show me how to get the same value using hash() in H2?
I tried call hash('SHA256',stringtoutf8('sha256'),1) without success...
Regards,
Christian
On Thursday, April 19, 2012 6:04:36 PM UTC+2, Thomas Mueller wrote:
Hi,
H2 supports SHA256 natively, I would use it instead of MD5:
http://h2database.com/html/functions.html#hash
Regards,
Thomas
On Saturday, April 14, 2012, Christian MICHON wrote:
On Friday, April 13, 2012 9:51:52 PM UTC+2, Christian MICHON wrote:
Hi,
I need to detect repetitions and updates in (relatively) small H2
tables.
As such, I currently implemented using ruby and an external ORM a
md5sum calculation of all the keys concatenated into a string following
their natural order, which is stored back into a specific key.
This involves a lot of communication with the H2 server, and I wish
to have it done natively (using java itself).
(...)
After many experiments, I finally cracked it.
Sharing it here, in case someone else finds this useful one day...
CREATE ALIAS MD5 FOR
org.apache.commons.codec.digest.DigestUtils.md5Hex(java.lang.String);
CREATE TABLE TEST(ID IDENTITY, NAME VARCHAR(255), MD5 VARCHAR(32) AS
MD5(NAME));
INSERT INTO TEST (NAME) VALUES('The quick brown fox jumps over the
lazy dog');
SELECT * FROM TEST ORDER BY ID;
Really powerful tool this H2 db... Congratulations again...
Christian
--
You received this message because you are subscribed to the Google
Groups H2 Database group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/UV44utlLiAYJ.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
--
You received this message because you are subscribed to the Google Groups H2
Database group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/99M-_NCOMGkJ.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
Re: How to calculate md5sum in a computed column?
Hi,
H2 supports SHA256 natively, I would use it instead of MD5:
http://h2database.com/html/functions.html#hash
Regards,
Thomas
On Saturday, April 14, 2012, Christian MICHON wrote:
On Friday, April 13, 2012 9:51:52 PM UTC+2, Christian MICHON wrote:
Hi,
I need to detect repetitions and updates in (relatively) small H2 tables.
As such, I currently implemented using ruby and an external ORM a md5sum
calculation of all the keys concatenated into a string following their
natural order, which is stored back into a specific key.
This involves a lot of communication with the H2 server, and I wish to
have it done natively (using java itself).
(...)
After many experiments, I finally cracked it.
Sharing it here, in case someone else finds this useful one day...
CREATE ALIAS MD5 FOR
org.apache.commons.codec.digest.DigestUtils.md5Hex(java.lang.String);
CREATE TABLE TEST(ID IDENTITY, NAME VARCHAR(255), MD5 VARCHAR(32) AS
MD5(NAME));
INSERT INTO TEST (NAME) VALUES('The quick brown fox jumps over the lazy
dog');
SELECT * FROM TEST ORDER BY ID;
Really powerful tool this H2 db... Congratulations again...
Christian
--
You received this message because you are subscribed to the Google Groups
H2 Database group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/UV44utlLiAYJ.
To post to this group, send email to
h2-database@googlegroups.comjavascript:_e({}, 'cvml',
'h2-database@googlegroups.com');
.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com javascript:_e({}, 'cvml',
'h2-database%2bunsubscr...@googlegroups.com');.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
--
You received this message because you are subscribed to the Google Groups H2
Database group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
Re: How to calculate md5sum in a computed column?
Yes, I knew about it.
Yet, this gives me 2 problems:
- increase of 32 char to 64 char for storing the hash. Maybe I should store
this in a different way, like binary to save half the space?
- I cannot figure out how to have the same results between hash() in H2 and
sha256 from OpenSSL/ruby
As said, I use ruby and in ruby OpenSSL::Digest::Digest.new(sha256) ==
e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855
Could you please show me how to get the same value using hash() in H2?
I tried call hash('SHA256',stringtoutf8('sha256'),1) without success...
Regards,
Christian
On Thursday, April 19, 2012 6:04:36 PM UTC+2, Thomas Mueller wrote:
Hi,
H2 supports SHA256 natively, I would use it instead of MD5:
http://h2database.com/html/functions.html#hash
Regards,
Thomas
On Saturday, April 14, 2012, Christian MICHON wrote:
On Friday, April 13, 2012 9:51:52 PM UTC+2, Christian MICHON wrote:
Hi,
I need to detect repetitions and updates in (relatively) small H2 tables.
As such, I currently implemented using ruby and an external ORM a md5sum
calculation of all the keys concatenated into a string following their
natural order, which is stored back into a specific key.
This involves a lot of communication with the H2 server, and I wish to
have it done natively (using java itself).
(...)
After many experiments, I finally cracked it.
Sharing it here, in case someone else finds this useful one day...
CREATE ALIAS MD5 FOR
org.apache.commons.codec.digest.DigestUtils.md5Hex(java.lang.String);
CREATE TABLE TEST(ID IDENTITY, NAME VARCHAR(255), MD5 VARCHAR(32) AS
MD5(NAME));
INSERT INTO TEST (NAME) VALUES('The quick brown fox jumps over the lazy
dog');
SELECT * FROM TEST ORDER BY ID;
Really powerful tool this H2 db... Congratulations again...
Christian
--
You received this message because you are subscribed to the Google Groups
H2 Database group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/UV44utlLiAYJ.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
--
You received this message because you are subscribed to the Google Groups H2
Database group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/hKjo_lpbiA8J.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
Re: How to calculate md5sum in a computed column?
Ah... got it: SHA256 is equivalent to ruby's sha2, and not sha256 from
openssl (this was completely wrong).
In ruby:
require 'digest/sha2'
= true
Digest::SHA2.hexdigest 'sha256'
= 5d5b09f6dcb2d53a5fffc60c4ac0d55fabdf556069d6631545f42aa6e3500f2e
In H2:
CALL HASH('SHA256', STRINGTOUTF8('sha256'), 1);
gives:
X'5d5b09f6dcb2d53a5fffc60c4ac0d55fabdf556069d6631545f42aa6e3500f2e'
5d5b09f6dcb2d53a5fffc60c4ac0d55fabdf556069d6631545f42aa6e3500f2e
So we have a match.
My 1st question still interests me: how to efficiently store this sha256 in
H2? Which would be a good data type?
Regards,
Christian
On Thursday, April 19, 2012 6:37:01 PM UTC+2, Christian MICHON wrote:
Yes, I knew about it.
Yet, this gives me 2 problems:
- increase of 32 char to 64 char for storing the hash. Maybe I should
store this in a different way, like binary to save half the space?
- I cannot figure out how to have the same results between hash() in H2
and sha256 from OpenSSL/ruby
As said, I use ruby and in ruby OpenSSL::Digest::Digest.new(sha256) ==
e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855
Could you please show me how to get the same value using hash() in H2?
I tried call hash('SHA256',stringtoutf8('sha256'),1) without success...
Regards,
Christian
On Thursday, April 19, 2012 6:04:36 PM UTC+2, Thomas Mueller wrote:
Hi,
H2 supports SHA256 natively, I would use it instead of MD5:
http://h2database.com/html/functions.html#hash
Regards,
Thomas
On Saturday, April 14, 2012, Christian MICHON wrote:
On Friday, April 13, 2012 9:51:52 PM UTC+2, Christian MICHON wrote:
Hi,
I need to detect repetitions and updates in (relatively) small H2
tables.
As such, I currently implemented using ruby and an external ORM a
md5sum calculation of all the keys concatenated into a string following
their natural order, which is stored back into a specific key.
This involves a lot of communication with the H2 server, and I wish to
have it done natively (using java itself).
(...)
After many experiments, I finally cracked it.
Sharing it here, in case someone else finds this useful one day...
CREATE ALIAS MD5 FOR
org.apache.commons.codec.digest.DigestUtils.md5Hex(java.lang.String);
CREATE TABLE TEST(ID IDENTITY, NAME VARCHAR(255), MD5 VARCHAR(32) AS
MD5(NAME));
INSERT INTO TEST (NAME) VALUES('The quick brown fox jumps over the lazy
dog');
SELECT * FROM TEST ORDER BY ID;
Really powerful tool this H2 db... Congratulations again...
Christian
--
You received this message because you are subscribed to the Google
Groups H2 Database group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/UV44utlLiAYJ.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
--
You received this message because you are subscribed to the Google Groups H2
Database group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/BtKBjeSJyBcJ.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
Re: How to calculate md5sum in a computed column?
On Friday, April 13, 2012 9:51:52 PM UTC+2, Christian MICHON wrote:
Hi,
I need to detect repetitions and updates in (relatively) small H2 tables.
As such, I currently implemented using ruby and an external ORM a md5sum
calculation of all the keys concatenated into a string following their
natural order, which is stored back into a specific key.
This involves a lot of communication with the H2 server, and I wish to
have it done natively (using java itself).
(...)
After many experiments, I finally cracked it.
Sharing it here, in case someone else finds this useful one day...
CREATE ALIAS MD5 FOR
org.apache.commons.codec.digest.DigestUtils.md5Hex(java.lang.String);
CREATE TABLE TEST(ID IDENTITY, NAME VARCHAR(255), MD5 VARCHAR(32) AS
MD5(NAME));
INSERT INTO TEST (NAME) VALUES('The quick brown fox jumps over the lazy
dog');
SELECT * FROM TEST ORDER BY ID;
Really powerful tool this H2 db... Congratulations again...
Christian
--
You received this message because you are subscribed to the Google Groups H2
Database group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/UV44utlLiAYJ.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
