diff options
Diffstat (limited to 'scripts/sql/rlm_sqlippool_tool')
| -rwxr-xr-x | scripts/sql/rlm_sqlippool_tool | 961 |
1 files changed, 961 insertions, 0 deletions
diff --git a/scripts/sql/rlm_sqlippool_tool b/scripts/sql/rlm_sqlippool_tool new file mode 100755 index 0000000..47a48fc --- /dev/null +++ b/scripts/sql/rlm_sqlippool_tool @@ -0,0 +1,961 @@ +#!/usr/bin/perl -Tw + +###################################################################### +# +# Copyright (C) 2020 Network RADIUS +# +# $Id$ +# +###################################################################### +# +# Helper script for populating IP pools with address entries. +# +# This script generates SQL output that is useful for populating an IP pool +# for use with FreeRADIUS (and possibly other purposes). Alternatively, +# if called with the -f option will directly operate on the database configured +# within the FreeRADIUS configuration to update the IP pool table specified +# +# Note: Direct connection to databases is done using Perl DBI. You may need +# to install the appropriate Perl DBD driver to enable this functionality. +# Formatted SQL output is produced using Perl Template::Toolkit which +# will need to be installed if this output is required. +# +# +# Use with a single address range +# ------------------------------- +# +# For basic use, arguments can be provided to this script that denote the ends +# of a single IP (v4 or v6) address range together with the pool_name and +# SQL dialect or a raddb directory from which the database config will be +# read. +# +# If a raddb directory is specified, then the instance of the FreeRADIUS sql +# module to be found in the config can be specified. It defaults to "sql". +# +# Optionally the number of IPs to sparsely populate the range with can be +# provided. If the range is wider than a /16 then the population of the range +# is capped at 65536 IPs, unless otherwise specified. +# +# In the case that a sparse range is defined, a file containing pre-existing +# IP entries can be provided. The range will be populated with entries from +# this file that fall within the range, prior to the remainder of the range +# being populated with random address in the range. +# +# rlm_sqlippool_tool -p <pool_name> -s <range_start> -e <range_end> \ +# -t <table_name> (-d <sql_dialect> | -f <raddb_dir> [ -i <instance> ]) \ +# [ -c <capacity> ] [ -x <existing_ips_file> ] +# +# Note: Sparse ranges are populated using a deterministic, pseudo-random +# function. This allows pools to be trivially extended without having to +# supply the existing contents using a file. If you require +# less-predictable randomness or a different random sequence then remove +# or modify the line calling srand(), below. +# +# +# Use with multiple pools and address ranges +# ------------------------------------------ +# +# For more complex us, the script allows a set of pool definitions to be +# provided in a YAML file which describes a set of one or more pools, each +# containing a set of one or more ranges. +# +# rlm_sqlippool_tool -y <pool_defs_yaml_file> -t <table_name> \ +# ( -d <dialect> | -f <raddb_dir> [ -i <instance> ] ) \ +# [ -x <existing_ips_file> ] +# +# The format for the YAML file is demonstrated by the following example: +# +# pool_with_a_single_contiguous_range: +# - start: 192.0.2.3 +# end: 192.0.2.250 +# +# pool_with_a_single_sparse_range: +# - start: 10.10.10.0 +# end: 10.10.20.255 +# capacity: 200 +# +# pool_with_multiple_ranges: +# - start: 10.10.10.1 +# end: 10.10.10.253 +# - start: 10.10.100.0 +# end: 10.10.199.255 +# capacity: 1000 +# +# v6_pool_with_contiguous_range: +# - start: '2001:db8:1:2:3:4:5:10' +# end: '2001:db8:1:2:3:4:5:7f' +# +# v6_pool_with_sparse_range: +# - start: '2001:db8:1:2::' +# end: '2001:db8:1:2:ffff:ffff:ffff:ffff' +# capacity: 200 +# +# As with the basic use case, a file containing pre-existing IP entries can be +# provided with which any sparse ranges will be populated ahead of any random +# addresses. +# +# +# Output +# ------ +# +# The script returns SQL formatted appropriately for one of a number of +# different SQL dialects. +# +# The SQL first creates a temporary table to insert the new pools into, +# inserts the addresses, then removes any exisitng entries from the pool +# table that do not exist in the new pool. Finally any new entries that +# don't exist in the existing pool table are copied from the temporary +# table. +# +# The SQL templates assume that the pool name will be in a field called +# "pool_name" and the IP address in a field named "framedipaddress", +# matching the default schema for ippools and DHCP ippools as shipped with +# FreeRADIUS. +# +# +# Examples +# -------- +# +# rlm_sqlippool_tool -p main_pool -s 192.0.2.3 -e 192.0.2.249 \ +# -d postgresql -t radippool +# +# Will create a pool from a full populated IPv4 range, i.e. all IPs in the +# range available for allocation, with SQL output suitable for PostgreSQL +# +# rlm_sqlippool_tool -p main_pool -s 10.66.0.0 -e 10.66.255.255 -c 10000 \ +# -d mysql -t radippool +# +# Will create a pool from a sparsely populated IPv4 range for a /16 +# network (maximum of 65.536 addresses), populating the range with 10,000 +# addreses. With SQL output suitable for MySQL. +# The effective size of the pool can be increased in future by increasing +# the capacity of the range with: +# +# rlm_sqlippool_tool -p main_pool -s 10.66.0.0 -e 10.66.255.255 -c 20000 \ +# -d mysql -t radippool +# +# This generates the same initial set of 10,000 addresses as the previous +# example but will create 20,000 addresses overall, unless the random seed +# has been amended since the initial run. +# +# rlm_sqlippool_tool -p main_pool -s 2001:db8:1:2:: \ +# -e 2001:db8:1:2:ffff:ffff:ffff:ffff -d mssql -t radippool +# +# Will create a pool from the IPv6 range 2001:db8:1:2::/64, initially +# populating the range with 65536 (by default) addresses. +# +# rlm_sqlippool_tool -p main_pool -s 2001:db8:1:2:: \ +# -e 2001:db8:1:2:ffff:ffff:ffff:ffff \ +# -c 10000 -x existing_ips.txt -d mysql -t radippool +# +# Will create a pool using the same range as the previous example, but +# this time the range will be populated with 10,000 addresses. The range +# will be populated using lines extracted from the `existing_ips.txt` file +# that represent IPs which fall within range. +# +# rlm_sqlippool_tool -y pool_defs.yml -d postgresql -t radippool \ +# -x existing_ips.txt +# +# Will create one of more pools using the definitions found in the +# pool_defs.yml YAML file. The pools will contain one or more ranges with +# each of the ranges first being populated with entries from the +# existing_ips.txt file that fall within the range, before being filled +# with random addresses to the defined capacity. +# + +use strict; +use Net::IP qw/ip_bintoip ip_iptobin ip_bincomp ip_binadd ip_is_ipv4 ip_is_ipv6/; + +# +# Option defaults +# +my $opts = { + instance => 'sql', + capacity => 65536 +}; + +# +# Parse the command line arguments +# +my $opt = ''; +for (my $i = 0; $i <= $#ARGV; $i++) { + if ($ARGV[$i] =~ m/^-(.)$/) { + if ($1 eq 'p') { + $opt = 'pool_name'; + } elsif ($1 eq 's') { + $opt = 'range_start'; + } elsif ($1 eq 'e') { + $opt = 'range_end'; + } elsif ($1 eq 'c') { + $opt = 'capacity'; + } elsif ($1 eq 't') { + $opt = 'table_name'; + } elsif ($1 eq 'd') { + $opt = 'dialect'; + } elsif ($1 eq 'y') { + $opt = 'yaml'; + } elsif ($1 eq 'x') { + $opt = 'entries'; + } elsif ($1 eq 'f') { + $opt = 'raddb_dir'; + } elsif ($1 eq 'i') { + $opt = 'instance'; + } else { + usage(); + exit 1; + } + } else { + if ($opt eq '') { + usage(); + exit 1; + } else { + $opts->{$opt} = $ARGV[$i] + } + } +} + +# +# If a raddb dir is set then we parse the mods-enabled config +# + +if ($opts->{raddb_dir}) { + my $found = 0; + if (-d $opts->{raddb_dir}.'/mods-enabled') { + opendir(my $dh, $opts->{raddb_dir}.'/mods-enabled') || die 'ERROR: Could not open directory '.$opts->{raddb_dir}.'/mods-enabled'; + my @dir = grep { -f "$opts->{raddb_dir}/mods-enabled/$_" } readdir($dh); + closedir($dh); + my $instance = $opts->{instance}; + foreach my $file (@dir) { + open (my $fh, $opts->{raddb_dir}.'/mods-enabled/'.$file); + my $level = 0; + my $section = ''; + my $subsection = ''; + while (<$fh>) { + if ($found) { + $_ =~ s/#.*//; # Remove comments + if ($_ =~ m/\s*([a-z_]+)\s*=\s*(.*)/) { + my $param = $1; + my $value = $2; + $value =~ s/^"//; + $value =~ s/"\s*$//; + if ($level == 1) { + $opts->{$param} = $value; + } elsif ($level == 2) { + $opts->{$section}->{$param} = $value; + } elsif ($level == 3) { + $opts->{$section}->{$subsection}->{$param} = $value; + } + } + if ($_ =~ m/([a-z_]*)\s+\{/) { # Find nested sectinos + $level++ ; + if ($level == 2) { + $section = $1; + } elsif ($level == 3) { + $subsection = $1; + } + } + $level-- if ($_ =~ m/\s+\}/); # Close of nesting + last if ($level == 0); # We've got to the end of the instance + } + if ($_ =~ m/\b$instance\s+\{/) { + # We've found the specified SQL instance + $found = 1; + $level = 1; + } + } + close ($fh); + if ($found) { + last; + } + } + } else { + die 'ERROR: Specified FreeRADIUS config directory does not contain mods-enabled'; + } + if ($found == 0) { + die 'ERROR: SQL instance not found in FreeRADIUS config'; + } +} + +# +# The SQL dialect and table name must be set +# +if ((!($opts->{dialect})) || (!($opts->{table_name}))) { + usage(); + exit 1; +} + +if ($opts->{yaml}) { + my $yaml_available = 0; + + eval { + require YAML::XS; + YAML::XS->import('LoadFile'); + $yaml_available = 1; + }; + + unless ($yaml_available) { + die "ERROR: YAML is not available. Install the YAML::XS Perl module."; + } + process_yaml_file($opts); + + goto done; + +} + + +if ((!($opts->{pool_name})) || (!($opts->{range_start})) || (!($opts->{range_end}))) { + usage(); + exit 1; +} + +process_commandline($opts); + +done: + +exit 0; + + +sub usage { + print STDERR <<'EOF' +Usage: + rlm_sqlippool_tool -p <pool_name> -s <range_start> -e <range_end> -t <table_name> (-d <sql_dialect> | -f <raddb_dir> [ -i <instance> ]) [ -c <capacity> ] [ -x <existing_ips_file> ] +or: + rlm_sqlippool_tool -y <pool_defs_yaml_file> -t <table_name> (-d <dialect> | -f <raddb_dir> [ -i <instance> ]) [ -x <existing_ips_file> ] + +EOF +} + + +sub process_commandline { + + my $opts = shift; + $SIG{__DIE__} = sub { usage(); die(@_); }; + + (my $template, my $queries)=load_templates($opts->{table_name}); + + unless (defined $template->{$opts->{dialect}}) { + print STDERR "Unknown dialect. Pick one of: "; + print STDERR "$_ " foreach sort keys %{$template}; + print STDERR "\n"; + exit 1; + } + + my @entries = (); + @entries = load_entries($opts->{entries}) if ($opts->{entries}); + + @entries = handle_range($opts->{range_start}, $opts->{range_end}, $opts->{capacity}, @entries); + + if ($opts->{radius_db}) { + &call_database($opts, $queries, @entries); + } else { + &output_sql($template->{$opts->{dialect}}, {ranges => [{pool_name => $opts->{pool_name}, ips => \@entries}], batchsize => 100, tablename => $opts->{table_name}}); + } +} + +sub process_yaml_file { + + my $opts = shift; + + unless (-r $opts->{yaml}) { + die "ERROR: Cannot open <pool_defs_yaml_file> for reading: $opts->{yaml}"; + } + + my %pool_defs = %{LoadFile($opts->{yaml})}; + + (my $template, my $queries)=load_templates($opts->{table_name}); + + unless (defined $template->{$opts->{dialect}}) { + print STDERR "Unknown dialect. Pick one of: "; + print STDERR "$_ " foreach sort keys %{$template}; + print STDERR "\n"; + exit 1; + } + + my @entries = (); + @entries = load_entries($opts->{entries}) if ($opts->{entries}); + + my @ranges; + foreach my $pool_name (sort keys %pool_defs) { + foreach my $range (@{$pool_defs{$pool_name}}) { + my $range_start = $range->{start}; + my $range_end = $range->{end}; + my $capacity = $range->{capacity}; + my @ips = handle_range($range_start, $range_end, $capacity, @entries); + push (@ranges, {pool_name => $pool_name, ips => \@ips}); + } + } + + if ($opts->{radius_db}) { + &call_database($opts, $queries, @entries); + } else { + &output_sql($template->{$opts->{dialect}}, {ranges => \@ranges, batchsize => 100, tablename => $opts->{table_name}}); + } +} + +sub output_sql { + my $template = shift(); + my $vars = shift(); + + my $tt_available = 0; + eval { + require Template; + $tt_available = 1; + }; + if ($tt_available) { + my $tt=Template->new(); + $tt->process(\$template, $vars) || die $tt->error(); + } else { + die "ERROR: Template Toolkit is not available. Install the Template Perl module."; + } +} + +sub call_database { + + my $opts = shift; + my $queries = shift; + my @entries = @_; + + my $dbi_avail = 0; + eval { + require DBI; + $dbi_avail = 1; + }; + unless($dbi_avail) { + die "ERROR: DBI is not available. Install the DBI Perl module."; + } + + my $dsn; + if ($opts->{dialect} eq 'mysql') { + $dsn = "DBI:mysql:database=$opts->{radius_db};host=$opts->{server}"; + if (defined($opts->{mysql}->{tls})) { + $dsn .= ';mysql_ssl=1'; + $dsn .= ';mysql_ssl_ca_file='.$opts->{mysql}->{tls}->{ca_file} if ($opts->{mysql}->{tls}->{ca_file}); + $dsn .= ';mysql_ssl_ca_path='.$opts->{mysql}->{tls}->{ca_path} if ($opts->{mysql}->{tls}->{ca_path}); + $dsn .= ';mysql_ssl_client_key='.$opts->{mysql}->{tls}->{private_key_file} if ($opts->{mysql}->{tls}->{private_key_file}); + $dsn .= ';mysql_ssl_client_cert='.$opts->{mysql}->{tls}->{certificate_file} if ($opts->{mysql}->{tls}->{certificate_file}); + $dsn .= ';mysql_ssl_cipher='.$opts->{mysql}->{tls}->{cipher} if ($opts->{mysql}->{tls}->{cipher}); + } + } elsif ($opts->{dialect} eq 'postgresql') { + # Parse FreeRADIUS alternative connection string + if ($opts->{radius_db} =~ m/host=(.+?)\b/) { + $opts->{server} = $1; + } + if ($opts->{radius_db} =~ m/user=(.+?)\b/) { + $opts->{login} = $1; + } + if ($opts->{radius_db} =~ m/password=(.+?)\b/) { + $opts->{password} = $1; + } + if ($opts->{radius_db} =~ m/sslmode=(.+?)\b/) { + $opts->{sslmode} = $1; + } + if ($opts->{radius_db} =~ m/dbname=(.+?)\b/) { + $opts->{radius_db} = $1; + } + $dsn = "DBI:Pg:dbname=$opts->{radius_db};host=$opts->{server}"; + # + # DBD doesn't have all the options used by FreeRADIUS - just enable ssl if + # FreeRADIUS has SSL options enabled + # + $dsn .= ';sslmode=prefer' if ($opts->{sslmode}); + } elsif ($opts->{dialect} eq 'sqlite') { + $dsn = "DBI:SQLite:dbname=$opts->{sqlite}->{filename}"; + } elsif ($opts->{dialect} eq 'mssql') { + if ($opts->{driver} eq 'rlm_sql_unixodbc') { + $dsn = "DBI:ODBC:DSN=$opts->{server}"; + } else { + $dsn = "DBI:Sybase:server=$opts->{server};database=$opts->{radius_db}"; + } + } elsif ($opts->{dialect} eq 'oracle') { + # Extract data from Oracle connection string as used by FreeRADIUS + if ($opts->{radius_db} =~ m/HOST=(.+?)\)/) { + $opts->{server} = $1; + } + if ($opts->{radius_db} =~ m/PORT=(.+?)\)/) { + $opts->{port} =$1; + } + if ($opts->{radius_db} =~ m/SID=(.+?)\)/) { + $opts->{sid} = $1; + } + $dsn = "DBI:Oracle:host=$opts->{server};sid=$opts->{sid}"; + } else { + $dsn = "DBI:$opts->{dialect}:database=$opts->{radius_db};host=$opts->{server}"; + } + $dsn .= ";port=$opts->{port}" if ($opts->{port}) && ($opts->{driver} ne 'rlm_sql_unixodbc'); + + # Read the results by running our query against the database + my $dbh = DBI->connect($dsn, $opts->{login}, $opts->{password}) || die "Unable to connect to database"; + + foreach my $query (@{$queries->{$opts->{dialect}}->{pre}}) { + $dbh->do($query); + } + + my $sth = $dbh->prepare($queries->{$opts->{dialect}}->{insert}); + foreach my $ip (@entries) { + $sth->execute($opts->{pool_name}, $ip); + } + $sth->finish(); + + foreach my $query (@{$queries->{$opts->{dialect}}->{post}}) { + $dbh->do($query); + } + + $dbh->disconnect(); +} + +sub load_entries { + + my $entries_file = shift; + + my @entries = (); + unless (-r $entries_file) { + die "ERROR: Cannot open <existing_ips_file> for reading: $entries_file" + } + open(my $fh, "<", $entries_file) || die "Failed to open $entries_file"; + while(<$fh>) { + chomp; + push @entries, $_; + } + + return @entries; + +} + + +sub handle_range { + + my $range_start = shift; + my $range_end = shift; + my $capacity = shift; + my @entries = @_; + + unless (ip_is_ipv4($range_start) || ip_is_ipv6($range_start)) { + die "ERROR: Incorrectly formatted IPv4/IPv6 address for range_start: $range_start"; + } + + unless (ip_is_ipv4($range_end) || ip_is_ipv6($range_end)) { + die "ERROR: Incorrectly formatted IPv4/IPv6 address for range_end: $range_end"; + } + + my $ip_start = new Net::IP($range_start); + my $ip_end = new Net::IP($range_end); + my $ip_range = new Net::IP("$range_start - $range_end"); + + unless (defined $ip_range) { + die "ERROR: The range defined by <range_start> - <range_end> is invalid: $range_start - $range_end"; + } + + my $range_size = $ip_range->size; + + if ($range_size < $capacity) { + $capacity = "$range_size"; + warn 'WARNING: Insufficent IPs in the range. Will create '.$capacity.' entries.'; + } + + # Prune the entries to only those within the specified range + for (my $i = 0; $i <= $#entries; $i++) { + my $version = ip_is_ipv4($entries[$i]) ? 4 : 6; + my $binip = ip_iptobin($entries[$i],$version); + if ($ip_start->version != $version || + ip_bincomp($binip, 'lt', $ip_start->binip) == 1 || + ip_bincomp($binip, 'gt', $ip_end->binip) == 1) { + $entries[$i]=''; + } + } + + # + # We use the sparse method if the number of entries available occupies < 80% of + # the network range, otherwise we use a method that involves walking the + # entire range. + # + + srand(42); # Set the seed for the PRNG + + if ($capacity / "$range_size" > 0.9) { + @entries = walk_fill($ip_start, $ip_end, $capacity, @entries); + } elsif (length($range_size) > 9 || $capacity / "$range_size" < 0.8) { # From "BigInt" to FP + @entries = sparse_fill($ip_start, $ip_end, $capacity, @entries); + } else { + @entries = dense_fill($ip_start, $ip_end, $ip_range, $capacity, @entries); + } + + return @entries; +} + + +# +# With this sparse fill method we randomly allocate within the scope of the +# smallest enclosing network prefix, checking that we are within the given +# range, retrying if we are outside or we hit a duplicate. +# +# This method can efficiently choose a small number of addresses relative to +# the size of the range. It becomes slower as the population of a range nears +# the range's limit since it is harder to choose a free address at random. +# +# It is useful for selecting a handful of addresses from an enourmous IPv6 /64 +# network for example. +# +sub sparse_fill { + + my $ip_start = shift; + my $ip_end = shift; + my $capacity = shift; + my @entries = @_; + + # Find the smallest network that encloses the given range + my $version = $ip_start->version; + ( $ip_start->binip ^ $ip_end->binip ) =~ /^\0*/; + my $net_prefix = $+[0]; + my $net_bits = substr($ip_start->binip, 0, $net_prefix); + my $host_length = length($ip_start->binip) - $net_prefix; + + my %ips = (); + my $i = 0; + while ($i < $capacity) { + + # Use the given entries first + my $rand_ip; + my $given_lease = 0; + shift @entries while $#entries >= 0 && $entries[0] eq ''; + if ($#entries >= 0) { + $rand_ip = ip_iptobin(shift @entries, $version); + $given_lease = 1; + } else { + $rand_ip = $net_bits; + $rand_ip .= [0..1]->[rand 2] for 1..$host_length; + # Check that we are inside the given range + next if ip_bincomp($rand_ip, 'lt', $ip_start->binip) == 1 || + ip_bincomp($rand_ip, 'gt', $ip_end->binip) == 1; + } + + next if defined $ips{$rand_ip}; + + $ips{$rand_ip} = $given_lease ? '=' : '+'; + $i++; + + } + + return map { ip_bintoip($_, $version) } keys %ips; + +} + + +# +# With this dense fill method, after first selecting the given entries we walk +# the network range picking IPs with evenly distributed probability. +# +# This method can efficiently choose a large number of addresses relative to +# the size of a range, provided that the range isn't massive. It becomes +# slower as the range size increases. +# +sub dense_fill { + + my $ip_start = shift; + my $ip_end = shift; + my $ip_range = shift; + my $capacity = shift; + my @entries = @_; + + my $version = $ip_start->version; + + my $one = ("0"x($version == 4 ? 31 : 127)) . '1'; + + my %ips = (); + my $remaining_entries = $capacity; + my $remaining_ips = $ip_range->size; + my $ipbin = $ip_start->binip; + + while ($remaining_entries > 0 && (ip_bincomp($ipbin, 'le', $ip_end->binip) == 1)) { + + # Use the given entries first + shift @entries while $#entries >= 0 && $entries[0] eq ''; + if ($#entries >= 0) { + $ips{ip_iptobin(shift @entries, $version)} = '='; + $remaining_entries--; + $remaining_ips--; + next; + } + + goto next_ip if defined $ips{$ipbin}; + + # Skip the IP that we have already selected by given entries, otherwise + # randomly pick it + if (!defined $ips{$ipbin} && + (rand) <= $remaining_entries / "$remaining_ips") { # From "BigInt" to FP + $ips{$ipbin} = '+'; + $remaining_entries--; + } + + $remaining_ips--; + $ipbin = ip_binadd($ipbin,$one); + + } + + return map { ip_bintoip($_, $version) } keys %ips; + +} + +# +# With this walk fill method we walk the IP range from the beginning +# for as many IPs as are required +# +# It is useful for selecting a fully populated network. +# + +sub walk_fill { + my $ip_start = shift; + my $ip_end = shift; + my $capacity = shift; + my @entries = @_; + + my $version = $ip_start->version; + + my $one = ("0"x($version == 4 ? 31 : 127)) . '1'; + + my %ips = (); + my $remaining_entries = $capacity; + my $ipbin = $ip_start->binip; + + # Sort existing IPs and remove any blank entries. Allows existing entries to be + # matched quickly in the new pool + my @sorted_entries = sort @entries; + shift @sorted_entries while $#sorted_entries >= 0 && $sorted_entries[0] eq ''; + + # Walk through the IP range from the beginning + while ($remaining_entries > 0 && (ip_bincomp($ipbin, 'le', $ip_end->binip) == 1)) { + + if ($#sorted_entries >= 0) { + # If there are existing entries check if they match + $ips{$ipbin} = (ip_bincomp($ipbin, 'eq', ip_iptobin($sorted_entries[0]) == 1) && shift(@sorted_entries) ? '=' : '+'); + } else { + $ips{$ipbin} = '+'; + } + $remaining_entries--; + $ipbin = ip_binadd($ipbin,$one); + + } + + return map { ip_bintoip($_, $version) } keys %ips; + +} + + + +# +# SQL dialect templates +# + +sub load_templates { + + my $tablename = shift; + + my $template; + my $queries; +# +# MySQL / MariaDB +# + $queries->{'mysql'}->{pre} = [ + 'DROP TEMPORARY TABLE IF EXISTS '.$tablename.'_temp;', + 'CREATE TEMPORARY TABLE '.$tablename.'_temp ( + id int(11) unsigned NOT NULL auto_increment, + pool_name varchar(30) NOT NULL, + framedipaddress varchar(15) NOT NULL, + PRIMARY KEY (id), + KEY pool_name_framedipaddress (pool_name,framedipaddress) +);' + ]; + $queries->{'mysql'}->{insert} = 'INSERT INTO '.$tablename.'_temp (pool_name,framedipaddress) VALUES (?, ?)'; + $queries->{'mysql'}->{post} = [ + 'START TRANSACTION;', + 'DELETE r FROM '.$tablename.' r + LEFT JOIN '.$tablename.'_temp t USING (pool_name,framedipaddress) + WHERE t.id IS NULL;', + 'INSERT INTO '.$tablename.' (pool_name,framedipaddress) + SELECT pool_name,framedipaddress FROM '.$tablename.'_temp t WHERE NOT EXISTS ( + SELECT * FROM '.$tablename.' r + WHERE r.pool_name=t.pool_name AND r.framedipaddress=t.framedipaddress + );', + 'COMMIT;' + ]; + $template->{'mysql'} = join("\n", @{$queries->{'mysql'}->{pre}})."\n"; + $template->{'mysql'} .= <<'END_mysql'; +-- Populate the temporary table +[%- FOREACH r IN ranges %] +[%- FOREACH i IN r.ips %] +[%- "\n\nINSERT INTO ${tablename}_temp (pool_name,framedipaddress) VALUES" IF loop.index % batchsize == 0 %] +[%- IF (loop.index+1) % batchsize == 0 OR loop.last %] +('[% r.pool_name %]','[% i %]'); +[%- ELSE %] +('[% r.pool_name %]','[% i %]'), +[%- END %] +[%- END %] +[%- END %] +END_mysql + $template->{'mysql'} .= join("\n", @{$queries->{'mysql'}->{post}})."\n"; + +# +# PostgreSQL +# + $queries->{'postgresql'}->{pre} = [ + 'DROP TABLE IF EXISTS '.$tablename.'_temp;', + 'CREATE TEMPORARY TABLE '.$tablename.'_temp ( + pool_name varchar(64) NOT NULL, + FramedIPAddress INET NOT NULL +);', + 'CREATE INDEX '.$tablename.'_temp_idx ON '.$tablename.'_temp USING btree (pool_name,FramedIPAddress);' + ]; + $queries->{'postgresql'}->{insert} = 'INSERT INTO '.$tablename.'_temp (pool_name,framedipaddress) VALUES (?, ?)'; + $queries->{'postgresql'}->{post} = [ + 'START TRANSACTION;', + 'DELETE FROM '.$tablename.' r WHERE NOT EXISTS ( + SELECT FROM '.$tablename.'_temp t + WHERE t.pool_name = r.pool_name AND t.framedipaddress = r.framedipaddress +);', + 'INSERT INTO '.$tablename.' (pool_name,framedipaddress) + SELECT pool_name,framedipaddress FROM '.$tablename.'_temp t WHERE NOT EXISTS ( + SELECT * FROM '.$tablename.' r + WHERE r.pool_name=t.pool_name AND r.framedipaddress=t.framedipaddress + );', + 'COMMIT;' + ]; + $template->{'postgresql'} = join("\n", @{$queries->{'postgresql'}->{pre}})."\n"; + $template->{'postgresql'} .= <<'END_postgresql'; +-- Populate the temporary table +[%- FOREACH r IN ranges %] +[%- FOREACH i IN r.ips %] +[%- "\n\nINSERT INTO ${tablename}_temp (pool_name,framedipaddress) VALUES" IF loop.index % batchsize == 0 %] +[%- IF (loop.index+1) % batchsize == 0 OR loop.last %] +('[% r.pool_name %]','[% i %]'); +[%- ELSE %] +('[% r.pool_name %]','[% i %]'), +[%- END %] +[%- END %] +[%- END %] +END_postgresql + $template->{'postgresql'} .= join("\n", @{$queries->{'postgresql'}->{post}})."\n"; +# +# Oracle +# + $queries->{'oracle'}->{pre} = [ + 'CREATE TABLE '.$tablename.'_temp ( + pool_name VARCHAR(30) NOT NULL, + FramedIPAddress VARCHAR(15) NOT NULL +)', + 'CREATE INDEX '.$tablename.'_temp_idx ON '.$tablename.'_temp (pool_name,FramedIPAddress)' + ]; + $queries->{'oracle'}->{insert} = 'INSERT INTO '.$tablename.'_temp (pool_name,FramedIPAddress) VALUES (?, ?)'; + $queries->{'oracle'}->{post} = [ + 'DELETE FROM '.$tablename.' r WHERE NOT EXISTS + (SELECT * FROM '.$tablename.'_temp t WHERE + r.pool_name = t.pool_name AND r.framedipaddress = t.framedipaddress)', + 'INSERT INTO '.$tablename.' (pool_name,framedipaddress) + SELECT pool_name,framedipaddress FROM '.$tablename.'_temp t WHERE NOT EXISTS ( + SELECT * FROM '.$tablename.' r + WHERE r.pool_name=t.pool_name AND r.framedipaddress=t.framedipaddress + )', + 'DROP TABLE '.$tablename.'_temp', + 'COMMIT' + ]; + + $template->{'oracle'} = join(";\n", @{$queries->{'oracle'}->{pre}}).";\n"; + $template->{'oracle'} .= <<'END_oracle'; +-- Populate the temporary table +[%- FOREACH r IN ranges %] +[%- FOREACH i IN r.ips %] +[%- "\nINSERT INTO ${tablename}_temp (pool_name,framedipaddress) VALUES " %]('[% r.pool_name %]','[% i %]'); +[%- END %] +[%- END %] +END_oracle + $template->{'oracle'} .= join(";\n", @{$queries->{'oracle'}->{post}})."\n"; + +# +# SQLite +# + $queries->{'sqlite'}->{pre} = [ + 'DROP TABLE IF EXISTS '.$tablename.'_temp;', + 'CREATE TABLE '.$tablename.'_temp ( + pool_name varchar(30) NOT NULL, + framedipaddress varchar(15) NOT NULL +);', + 'CREATE INDEX '.$tablename.'_temp_idx ON '.$tablename.'_temp (pool_name,FramedIPAddress);' + ]; + $queries->{'sqlite'}->{insert} = 'INSERT INTO '.$tablename.'_temp (pool_name,framedipaddress) VALUES (?, ?)'; + $queries->{'sqlite'}->{post} = [ + 'BEGIN TRANSACTION;', + 'DELETE FROM '.$tablename.' WHERE rowid IN ( + SELECT r.rowid FROM '.$tablename.' r + LEFT JOIN '.$tablename.'_temp t USING (pool_name,framedipaddress) + WHERE t.rowid IS NULL);', + 'INSERT INTO '.$tablename.' (pool_name,framedipaddress) + SELECT pool_name,framedipaddress FROM '.$tablename.'_temp t WHERE NOT EXISTS ( + SELECT * FROM '.$tablename.' r + WHERE r.pool_name=t.pool_name AND r.framedipaddress=t.framedipaddress + );', + 'COMMIT;', + 'DROP TABLE '.$tablename.'_temp;' + ]; + + $template->{'sqlite'} = join("\n", @{$queries->{'sqlite'}->{pre}})."\n"; + $template->{'sqlite'} .= <<'END_sqlite'; +-- Populate the temporary table +[%- FOREACH r IN ranges %] +[%- FOREACH i IN r.ips %] +[%- "\n\nINSERT INTO ${tablename}_temp (pool_name,framedipaddress) VALUES" IF loop.index % batchsize == 0 %] +[%- IF (loop.index+1) % batchsize == 0 OR loop.last %] +('[% r.pool_name %]','[% i %]'); +[%- ELSE %] +('[% r.pool_name %]','[% i %]'), +[%- END %] +[%- END %] +[%- END %] +END_sqlite + $template->{'sqlite'} .= join("\n", @{$queries->{'sqlite'}->{post}})."\n"; + +# +# MS SQL +# + $queries->{'mssql'}->{pre} = [ + 'DROP TABLE IF EXISTS #'.$tablename.'_temp;', + 'CREATE TABLE #'.$tablename.'_temp ( + id int identity(1, 1) NOT NULL, + pool_name varchar(30) NOT NULL, + framedipaddress varchar(15) NOT NULL, + PRIMARY KEY (id), +);', + 'CREATE INDEX pool_name_framedipaddress ON #'.$tablename.'_temp(pool_name, framedipaddress);' + ]; + $queries->{'mssql'}->{insert} = 'INSERT INTO #'.$tablename.'_temp (pool_name, framedipaddress) VALUES (?, ?)'; + $queries->{'mssql'}->{post} = [ + 'BEGIN TRAN;', + 'DELETE r FROM '.$tablename.' r + LEFT JOIN #'.$tablename.'_temp t ON r.pool_name = t.pool_name AND r.framedipaddress = t.framedipaddress + WHERE t.id IS NULL;', + 'INSERT INTO '.$tablename.' (pool_name,framedipaddress) + SELECT pool_name,framedipaddress FROM #'.$tablename.'_temp t WHERE NOT EXISTS ( + SELECT * FROM '.$tablename.' r + WHERE r.pool_name=t.pool_name AND r.framedipaddress=t.framedipaddress + );', + 'COMMIT TRAN;' + ]; + + $template->{'mssql'} = join("\nGO\n", @{$queries->{'mssql'}->{pre}})."\nGO\n"; + $template->{'mssql'} .= <<'END_mssql'; +-- Populate the temporary table +[%- FOREACH r IN ranges %] +[%- FOREACH i IN r.ips %] +[%- "\n\nINSERT INTO #${tablename}_temp (pool_name,framedipaddress) VALUES" IF loop.index % batchsize == 0 %] +[%- IF (loop.index+1) % batchsize == 0 OR loop.last %] +('[% r.pool_name %]','[% i %]'); +GO +[%- ELSE %] +('[% r.pool_name %]','[% i %]'), +[%- END %] +[%- END %] +[% END %] +END_mssql + $template->{'mssql'} .= join("\n", @{$queries->{'mssql'}->{post}})."\n"; + + return ($template, $queries); + +} + |