Showing posts with label sql for fun and profit. Show all posts
Showing posts with label sql for fun and profit. Show all posts

Wednesday, 20 February 2013

Calculate supernets to /16 in Oracle SQL

This bit of SQL calculates all the supernet addresses (to /16) for a given IP address (and mask) in Oracle. Supernet addresses are the 'containing' IP Addresses based on powers of 2, so a /30 address covers 2(32-30) addresses (or 4 IPs). A /26 address covers 2(32-26) addresses (64 IPs). This works on Oracle 9, 10 and 11. If your version does not support regexp_replace there are some commented fields that utilise some instr calls.
select 
lvl supernet_mask, 
ip, 
regexp_substr(ip,'^\d+\.\d+\.') || resl2 || '.' || resl supernet
from
(
select 
  lvl, ip, mask, q3,q4,
  case when lvl > 24 then
    null
  else 
    0 
  end supernet
  , 
  floor(q4/(power(2,32-lvl))) * power(2,32-lvl) resl 
  , 
  CASE WHEN lvl <= 24 then 
    floor(q3/(power(2,24-lvl))) * power(2,24-lvl) 
  ELSE 
    Q3
  END resl2
from
(
select lvl, ip, mask
, to_number(regexp_substr(ip,'\d+',1)) q1
, to_number(regexp_replace(ip,'(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})','\2')) q2
, to_number(regexp_replace(ip,'(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})','\3')) q3
, to_number(regexp_replace(ip,'(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})','\4')) q4
/* -- if your version does not support regexp_replace
, to_number(substr(ip,1,instr(ip,'.',1,1)-1)) q1
, to_number(substr(ip,instr(ip,'.',1,1)+1,instr(ip,'.',1,2 )-instr(ip,'.',1,1)-1 ))  q2
, to_number(substr(ip,instr(ip,'.',1,2)+1,instr(ip,'.',1,3 )-instr(ip,'.',1,2)-1 )) q3
, to_number(substr(ip,instr(ip,'.',1,3)+1)) q4
*/
from 
(
select 33 - level lvl,'203.63.104.108' ip,30 mask from dual connect by level <= 17 union
select 33 - level lvl,'203.63.104.12' ip,30 mask from dual connect by level <= 17 union
select 33 - level lvl,'203.63.104.124' ip,30 mask from dual connect by level <= 17 union
select 33 - level lvl,'203.63.104.144' ip,30 mask from dual connect by level <= 17 
) x
where mask >= lvl
) xx order by ip, lvl desc
) xxx

Wednesday, 21 March 2012

Building a mysqldump script to back up your database

Sometimes, I want to backup the mySQL database but not everything...using mysqldump we can develop a little script to organise what we need.

select
concat('mysqldump -u<uid> -p<passwd> --max_allowed_packet=256M ',
table_schema,' ', group_concat(table_name ORDER BY table_name DESC SEPARATOR ' ')
,' > /tmp/mysqldump_20120319_',table_schema
) tables_ from information_schema.TABLES where table_schema not in ('information_schema','mysql','db1','db2','db2','db3')
and table_type='BASE TABLE'
group by table_schema


This returns a series of command strings for mysqldump that lists each table in the non-excluded schemas and dumps them into a dump file for each schema. This means a table name can be deleted if you don't want/need to have it backed-up enough to justify storing a (e.g) 20GB insert statement.

These can be pasted into a file and run with sh and cron ....or in a batch file in Windows.

Adjust the UID, pwd, dumpfile name/location and database schemas as needed.