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

This Javascript function will filter a table according to a RegEx match with an input string. It does this by looping through each row of the table and hiding it if nothing matches. This is a pretty useful bit of code when you are presented report outputs as html tables, your user likes to chop and change conditions, and the query itself is a little slow.

//takes the tableid, and the filterstring and loops through the table
//doing a regex match using the filterString. if line matches, the <tr> is shown
// but if no match anywhere on the line, it is hidden.  
// Andrew Cave 04/02/2013
// andrew.cave.blogging@gmail.com
function filterTable(tableID,filterString){
t = document.getElementById(tableID);
if(typeof t=='undefined'){return;}//exit if the table hasn't been created yet
tbod = t.tBodies[0];
rows = tbod.rows;
rowct = rows.length;
filterString = trim(filterString);
filterString=(filterString.length==0)?'.*':filterString; //empty strings return all records
re = new RegExp(trim(filterString),"i");

for(i=0;i < rowct;i++){
cells=rows[i].cells;
cellct=cells.length;
hideme = 1; //start with hideme being true
for(j=0;j < cellct;j++){
	var td = cells[j].innerHTML;
	if(td.match(re)){ //checking for a match
		hideme=0;
		break;
	}
}
if(hideme){
	$(rows[i]).hide();
} else {
	$(rows[i]).show();
}
}
}

List of contrasting web-colors

a simple list of web-colours for backgrounds, with a contrasting foreground colour (based on RGB luminosity values):

background-colr	color
#000000	#FFFFFF
#000080	#FFFFFF
#00008B	#FFFFFF
#0000CD	#FFFFFF
#0000FF	#FFFFFF
#191970	#FFFFFF
#4B0082	#FFFFFF
#800000	#FFFFFF
#8B0000	#FFFFFF
#800080	#FFFFFF
#8B008B	#FFFFFF
#006400	#FFFFFF
#9400D3	#FFFFFF
#2F4F4F	#FFFFFF
#483D8B	#FFFFFF
#008000	#FFFFFF
#B22222	#FFFFFF
#FF0000	#FFFFFF
#A52A2A	#FFFFFF
#DC143C	#FFFFFF
#8B4513	#FFFFFF
#C71585	#FFFFFF
#008080	#FFFFFF
#8A2BE2	#FFFFFF
#556B2F	#FFFFFF
#228B22	#FFFFFF
#008B8B	#FFFFFF
#9932CC	#FFFFFF
#A0522D	#FFFFFF
#FF1493	#FFFFFF
#FF00FF	#FFFFFF
#2E8B57	#FFFFFF
#696969	#FFFFFF
#4169E1	#FFFFFF
#6A5ACD	#FFFFFF
#808000	#FFFFFF
#FF4500	#FFFFFF
#4682B4	#FFFFFF
#6B8E23	#FFFFFF
#1E90FF	#FFFFFF
#7B68EE	#FFFFFF
#708090	#FFFFFF
#CD5C5C	#FFFFFF
#808080	#FFFFFF
#D2691E	#FFFFFF
#BA55D3	#FFFFFF
#20B2AA	#FFFFFF
#778899	#FFFFFF
#9370D8	#FFFFFF
#B8860B	#FFFFFF
#3CB371	#000000
#5F9EA0	#000000
#00BFFF	#000000
#32CD32	#000000
#FF6347	#000000
#6495ED	#000000
#00CED1	#000000
#D87093	#000000
#CD853F	#000000
#00FF00	#000000
#DA70D6	#000000
#BC8F8F	#000000
#FF69B4	#000000
#FF8C00	#000000
#FF7F50	#000000
#F08080	#000000
#FA8072	#000000
#00FA9A	#000000
#00FF7F	#000000
#DAA520	#000000
#48D1CC	#000000
#A9A9A9	#000000
#8FBC8F	#000000
#66CDAA	#000000
#E9967A	#000000
#9ACD32	#000000
#FFA500	#000000
#EE82EE	#000000
#40E0D0	#000000
#BDB76B	#000000
#00FFFF	#000000
#F4A460	#000000
#FFA07A	#000000
#DDA0DD	#000000
#D2B48C	#000000
#7CFC00	#000000
#87CEEB	#000000
#7FFF00	#000000
#87CEFA	#000000
#DEB887	#000000
#C0C0C0	#000000
#B0C4DE	#000000
#90EE90	#000000
#D8BFD8	#000000
#FFD700	#000000
#ADD8E6	#000000
#FFB6C1	#000000
#ADFF2F	#000000
#B0E0E6	#000000
#98FB98	#000000
#D3D3D3	#000000
#7FFFD4	#000000
#FFC0CB	#000000
#AFEEEE	#000000
#DCDCDC	#000000
#F0E68C	#000000
#F5DEB3	#000000
#FFDAB9	#000000
#FFDEAD	#000000
#EEE8AA	#000000
#FFFF00	#000000
#FFE4B5	#000000
#E6E6FA	#000000
#FFE4C4	#000000
#FFE4E1	#000000
#FAEBD7	#000000
#FFEBCD	#000000
#FFEFD5	#000000
#FAF0E6	#000000
#F5F5DC	#000000
#FFF0F5	#000000
#F5F5F5	#000000
#FDF5E6	#000000
#E0FFFF	#000000
#FAFAD2	#000000
#F0F8FF	#000000
#FFF8DC	#000000
#FFFACD	#000000
#FFF5EE	#000000
#F8F8FF	#000000
#F0FFF0	#000000
#FFFAF0	#000000
#F0FFFF	#000000
#F5FFFA	#000000
#FFFFE0	#000000
#FFFAFA	#000000
#FFFFF0	#000000
#FFFFFF	#000000

array of file handles in Perl

I used the following code today which creates, prints to and closes an array of file-handles using the IO::File core module. I had over 100K of files in the same directory and had to process them (and send them into their own directory too, but that is trivial). This works on Perl 5.14...

#==========================
# Andrew Cave
# 29 aug 2012
# 
#==========================

use IO::File;
#create a blank hash of day values
my %month=( 
'20120712' => '','20120713' => '','20120714' => '',
'20120715' => '','20120716' => '','20120717' => '',
'20120718' => '','20120719' => '','20120720' => '',
'20120721' => '','20120722' => '','20120723' => '',
'20120724' => '','20120725' => '','20120726' => '',
'20120727' => '','20120728' => '','20120729' => '',
'20120730' => '','20120731' => '','20120801' => '',
'20120802' => '','20120803' => '','20120804' => '',
'20120805' => '','20120806' => '','20120807' => '',
'20120808' => '','20120809' => '','20120810' => '',
'20120811' => '','20120812' => '','20120813' => '',
'20120814' => '','20120815' => '','20120816' => '',
'20120817' => '','20120818' => '','20120819' => '',
'20120820' => '','20120821' => '','20120822' => '',
'20120823' => '','20120824' => '','20120825' => '',
'20120826' => '','20120827' => '','20120828' => '' 
);
my ($key,$val) ;
#open a filename based on the array values and
#put the file-handle reference in the hash, using the dayvalue 
#as the key

while (  ($key,$val) = each(%month)) {
 my $filename = "output_$key.dat";
 my $fh = IO::File->new("> $filename") 
      or die "failed to create $filename $@\n$!\n";
 $month{$key} = $fh;
};

open IN , '<', 'input.dat';
while(){
 chomp;
 my $line = $_;
 if(/\d{8}/){# if the line has an eight digit number
   my $day = $1; 
   my $fh = $month{$day}; # get the correct filehandle
   print {$fh} "$line\n" ; #print to the filehandle - note the braces
                #move the file into its own directory
                #and add '.done' to the name
  if(! -d "./$day/"){
   system("mkdir ./$day/");
  }
  #print "$file|./$day/\n"; # the mv statements (if we want a record)
  system("mv $file ./$day/$file.done");

 }

}
close IN;

#close the array of files we created
# using IO::File
while (($key,$val)=each(%month)){
 my $close_fh =  $month{$key};
 $close_fh->close();
}

Create a Calendar listing in pure Oracle SQL

Just fiddling around...create a calendar for the next 1000 days starting with the 1st day of the current year. The calendar is the standard format putting Sundays in the left-most column a la :

2012	APR	SUN	MON	TUE	WED	THU	FRI	SAT
2012	APR	1	2	3	4	5	6	7
2012	APR	8	9	10	11	12	13	14
2012	APR	15	16	17	18	19	20	21
2012	APR	22	23	24	25	26	27	28
2012	APR	29	30
2012	AUG	SUN	MON	TUE	WED	THU	FRI	SAT
2012	AUG				1	2	3	4
2012	AUG	5	6	7	8	9	10	11
2012	AUG	12	13	14	15	16	17	18
2012	AUG	19	20	21	22	23	24	25
2012	AUG	26	27	28	29	30	31
2012	DEC	SUN	MON	TUE	WED	THU	FRI	SAT
2012	DEC							1
2012	DEC	2	3	4	5	6	7	8
2012	DEC	9	10	11	12	13	14	15
2012	DEC	16	17	18	19	20	21	22
2012	DEC	23	24	25	26	27	28	29
2012	DEC	30	31

/*generate a calendar in Oracle using pure SQL
-- Andrew Cave andrew.cave.blogging at gmail-- 29/3/2012*/
with x as (select -- generate the days
  trunc(trunc(sysdate,'YEAR')+level-1) date_ -- add days to 1st day of current year
  from dual connect by level <= 1000 -- number of days to go forward
) 
select yr,monthname,SUN,MON,TUE,WED,THU,FRI,SAT from 
(
select yr
,monthname,MON_NUM,row_
,SUN,MON,TUE,WED,THU,FRI,SAT
from (
select -- display the dates
yr
,monthname,MON_NUM,row_
, to_char(max(decode(dayname,'SUN',dom,null))) SUN
, to_char(max(decode(dayname,'MON',dom,null))) MON
, to_char(max(decode(dayname,'TUE',dom,null))) TUE
, to_char(max(decode(dayname,'WED',dom,null))) WED
, to_char(max(decode(dayname,'THU',dom,null))) THU
, to_char(max(decode(dayname,'FRI',dom,null))) FRI
, to_char(max(decode(dayname,'SAT',dom,null))) SAT
from (
select 
yr
,mon monthname
,dayname
,dom
,MON_NUM
,trunc((dom+ fdom-1)/7)-trunc(fdom/7)  row_ -- what week of the month
,fdom 
from 
  (
  select date_ -- get date values for calculations and display
    , to_char(date_,'DY') dayname
    , to_char(date_,'YYYY') yr  
    , to_char(date_,'MON') MON
    , to_char(date_,'MM') MON_NUM
    , to_number(to_char(date_,'DD')) dom
    , to_number(to_char(trunc(date_,'MM'),'D')) fdom -- day value of 1st day of month
  from x
  ) x1
) x2 group by yr,monthname,row_,MON_NUM
) x3 
union
select distinct -- this select does the day name column headers for the calendar
  to_char(date_,'YYYY') yr
  , to_char(date_,'MON') MONthname
  , to_char(date_,'MM') MON_NUM
  , -1 row_
  ,'SUN','MON','TUE','WED','THU','FRI','SAT'
from x
) x4  order by yr, MON_NUM, row_

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.

Restrict number of rows returned - Sybase IQ

Often when you are trying to figure out a database, you will look at a table just to see what is in it. Some SQL tools return every single row on a "select * from table" COUGH!*TOAD for Data Analysts*!COUGH which is a pain when you have 3 million rows.

In Oracle you can use ROWNUM < 100 to return the first 99 rows and in MySQL 'limit 99' does the same. But for Sybase IQ? Nothing so simple. But today I discovered that there is a way to do it using the ROWID function:
select * from <SCHEMA>.<TABLE_NAME> where rowid("<TABLE_NAME>") < 100