I'm revisiting this as I refactor my code from using datasets to models.
This is the current "ips" table schema:
mysql> desc ips;
+-------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra
|
+-------------------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment
|
| ip | varchar(255) | NO | MUL | NULL |
|
| mask | varchar(255) | YES | MUL | NULL |
|
| ip_type | varchar(255) | NO | MUL | NULL |
|
| ip_int | varbinary(16) | YES | MUL | NULL |
|
| mask_int | varbinary(16) | YES | MUL | NULL |
|
| min_subnet_ip_int | varbinary(16) | YES | MUL | NULL |
|
| max_subnet_ip_int | varbinary(16) | YES | MUL | NULL |
|
| updated_at | datetime | YES | | NULL |
|
+-------------------+---------------+------+-----+---------+----------------+
MySQL supports a decimal field-type, which is more transparent to Sequel
than using varbinary fields. Using a "decimal(39)" instead of "varbinary(16)"
seems to fit a 128-bit IPv6 value from what I've seen doing searches, so
I'm converting my table definition's from:
create_table(:ips) do
primary_key :id
String :ip
String :mask
String :ip_type
varbinary :ip_int, :size => 16
varbinary :mask_int, :size => 16
varbinary :min_subnet_ip_int, :size => 16
varbinary :max_subnet_ip_int, :size => 16
end
to:
decimal :ip_int, size: 39
decimal :mask_int, size: 39
decimal :min_subnet_ip_int, size: 39
decimal :max_subnet_ip_int, size: 39
I created a temporary "scrap_table" table with the new definitions:
mysql> desc scrap_table;
+-------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra
|
+-------------------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment
|
| updated_at | datetime | YES | | NULL |
|
| ip | varchar(255) | YES | | NULL |
|
| mask | varchar(255) | YES | | NULL |
|
| ip_type | varchar(255) | YES | | NULL |
|
| ip_int | decimal(39,0) | YES | | NULL |
|
| mask_int | decimal(39,0) | YES | | NULL |
|
| min_subnet_ip_int | decimal(39,0) | YES | | NULL |
|
| max_subnet_ip_int | decimal(39,0) | YES | | NULL |
|
+-------------------+---------------+------+-----+---------+----------------+
and ran some quick tests of the different field types in Sequel's console.
It looks like it stored values nicely:
Scrap.create({:ip=>"216.69.189.129", :mask=>"255.255.255.240",
:ip_type=>"network", :ip_int=>3628449153, :mask_int=>4294967280,
:min_subnet_ip_int=>3628449152, :max_subnet_ip_int=>3628449167,
:updated_at=>'2012-10-19 15:16:01 -0700'})
With this SQL being sent:
I, [2012-10-19T15:29:00.592702 #63311] INFO -- : (0.003261s) INSERT INTO
`scrap_table` (`ip`, `mask`, `ip_type`, `ip_int`, `mask_int`,
`min_subnet_ip_int`, `max_subnet_ip_int`, `updated_at`) VALUES
('216.69.189.129', '255.255.255.240', 'network', 3628449153, 4294967280,
3628449152, 3628449167, '2012-10-19 15:16:01')
A query retrieving the record returned:
irb(main):008:0> ip = Scrap.first
I, [2012-10-19T15:40:49.466139 #63311] INFO -- : (0.003548s) SELECT * FROM
`scrap_table` LIMIT 1
#<Scrap @values={:id=>1, :updated_at=>2012-10-19 15:16:01 -0700,
:ip=>"216.69.189.129", :mask=>"255.255.255.240", :ip_type=>"network",
:ip_int=>#<BigDecimal:100d540e8,'0.3628449153E10',18(27)>,
:mask_int=>#<BigDecimal:100d54070,'0.429496728E10',18(27)>,
:min_subnet_ip_int=>#<BigDecimal:100d53ff8,'0.3628449152E10',18(27)>,
:max_subnet_ip_int=>#<BigDecimal:100d53f80,'0.3628449167E10',18(27)>}>
The returned type of "BigDecimal" seems to be workable; I can leave it
alone and let Ruby change its type at will, or I can force it to an integer
using "to_i":
irb(main):016:0> ip.ip_int
3628449153.0
irb(main):018:0> ip.ip_int.to_i
3628449153
irb(main):019:0> ip.ip_int == 3628449153
true
More testing will be needed, but it looks like this is a more direct path.
Currently we're on MySQL 5.1.61 with little chance of upgrading to the
current revision so all IPv6 functions are unavailable. (And, oh how I miss
using PostgreSQL.)
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/2F4saC0kXsoJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
