sub col ($@) {
	my ($col, @list) = @_;

	my ($i, @data) = ();
	for $i (0 .. $#list) { push @data, $list[$i][$col]; }

	return @data;
}


sub compose_multipage_commands ($$@) {
	my ($data, $target, @page) = @_;

	# read to start of target array
	($before, $array, $after) = $data =~ m/(^.*?\@$target = \(\n)(.*?)(\n\);\n\n(@|#).+$)/s;

	# interpolate over *** delimited strings
	my ($i, $output) = ();
	for $i (0 .. $#page) {
		my $page = $page[$i];
		my $tmp = $array;
		$tmp =~ s/\*{3}(.*?)\*{3}/$1/seeg;
		$output .= "$tmp,\n";
	}

	return "$before$output$after";
}


sub echo { 
	return join '', @_;
	# NB: this may look completely pointless, but you can't eval text itself.
}


sub get_used_tables (@) {
	my (@table, $query, $tmp) = ();
	my $clause = '( where| group by| having| order by| limit| procedure|$)';

	# make a list of unique tables queried
	foreach $query (@_) {
		unless ($query eq '') {
			$query =~ m/from ([a-zA-Z0-9 ,]+?)$clause/ or die "\n$prog: bad query '$query'";
			foreach $tmp (split /, /, $1) { push @table, $tmp unless grep /^$tmp$/, @table; }
		}
	}
	return @table;
}


sub history_all_query ($$$$$) {
	my ($record_query, $record_format, $history_query, $history_format, $history_var) = @_;

	# prepare record query
	lock_tables(get_used_tables($record_query));
	my $sh = $db->prepare($record_query) or die "\n$prog: failed to prepare query: $DBI::errstr";
	$sh->execute or die "\n$prog: failed to execute query: $DBI::errstr";

	# get column names
	my @column = @{$sh->{NAME}};

	# get each record
	my (@tmp, $i);
	my $data = '';
	while (@tmp = $sh->fetchrow_array) {
		# interleave column names and data
		my (@var, $tmp) = ();
		for $i (0 .. $#column) { push @var, $column[$i], shift @tmp; }

		# make history
		push @var, $history_var, single_query(eval 'interpolate($history_query, @var)', $history_format);

		# interpolate data into record format, then eval to activate any backslashed chars
		$data .= eval 'interpolate($record_format, @var)';
	}
	$sh->finish or die "\n$prog: failed to release handle: $DBI::errstr";
	return $data;
}


sub history_number_query ($$$$$$$$$$) {
	my ($record_query, $record_format, $history_query, $history_format, $history_var, $history_pre, $history_post, $history_make_query, $history_make_format, $history_make_var) = @_;

	# prepare record query
	lock_tables(get_used_tables($record_query));
	my $sh = $db->prepare($record_query) or die "\n$prog: failed to prepare query: $DBI::errstr";
	$sh->execute or die "\n$prog: failed to execute query: $DBI::errstr";

	# get column names
	my @column = @{$sh->{NAME}};

	# get each record
	my (@tmp, $i);
	my $data = '';
	while (@tmp = $sh->fetchrow_array) {
		# interleave column names and data
		my @var = ();
		for $i (0 .. $#column) { push @var, $column[$i], shift @tmp; }

		# make history if needed
		for ($i = 0; $i <= $#var; $i+=2) {
			if ($var[$i] eq $history_make_var) {
				my $tmp = single_query(eval 'interpolate($history_make_query, @var)', $history_make_format);
				push @var, $history_var, ($var[$i+1] == $tmp ? $history_pre . single_query(eval 'interpolate($history_query, @var)', $history_format) . $history_post : '');
			}
		}

		# interpolate data into record format, then eval to activate any backslashed chars
		$data .= eval 'interpolate($record_format, @var)';
	}
	$sh->finish or die "\n$prog: failed to release handle: $DBI::errstr";
	return $data;
}


sub interpolate {
	my $data = shift @_;

	# set variables
	my (@var, $tmp) = ();
	while ($tmp = shift @_) {
		$$tmp = shift @_;
		die "\n$prog: interpolate requires a value for each variable name passed" unless defined($$tmp);
		push @var, $tmp;
	}

	# interpolate and return
	foreach $tmp (@var) { $data =~ s/\$$tmp/$$tmp/sg; }
	$data =~ s/@\{\[(.*?)\]\}/$1/seeg;
	return $data;
}


sub interpolate_file {
	die "\n$prog: interpolate_file requires a file" unless @_;
	my $data = read_file(shift @_);
	return interpolate($data, @_);
}


sub list_query ($$$$$) {
	my ($list_query, $list_format, $list_closer, $element_query, $element_format) = @_;

	# prepare list query
	lock_tables(get_used_tables($list_query));
	my $sh = $db->prepare($list_query) or die "\n$prog: failed to prepare query: $DBI::errstr";
	$sh->execute or die "\n$prog: failed to execute query: $DBI::errstr";
	
	# get column names
	my @column = @{$sh->{NAME}};

	# get list headings
	my (@tmp, $i);
	my $data = '';
	while (@tmp = $sh->fetchrow_array) {
		# interleave column names and data
		my @var = ();
		for $i (0 .. $#column) { push @var, $column[$i], shift @tmp; }

		# interpolate data into list format, then eval to activate any backslashed chars
		$data .= eval 'interpolate($list_format, @var)';
		
		# get elements
		$data .= single_query(eval 'interpolate($element_query, @var)', $element_format);

		# close list
		$data .= $list_closer;
	}
	return $data;
}


sub list_unpack {
	my ($parent, $list_open, $list_format, $list_close, $element_query, $element_format, @list) = @_;

	my $i;
	my $data = '';

	$data .= single_query(eval 'interpolate($element_query, "category", $list[$parent][0])', interpolate($element_format, "parent", $parent+1)) unless $parent == -1;

	for $i (0 .. $#list) {
		if ($list[$i][1] == $parent) {
			$data .= eval 'interpolate($list_format, "element", $list[$i][0])';
			$data .= list_unpack($i, $list_open, $list_format, $list_close, $element_query, $element_format, @list);
		}
	}

	return ($data ? "$list_open$data$list_close" : '');
}


sub lock_tables {
	my $tables = join ', ', @_;

	# prevent changes to tables while extracting data
	$tables =~ s/(,|$)/ write$1/g;
	$db->do("lock tables $tables") or die "\n$prog: unable to lock tables: $DBI::errstr";
   return 1;
}


sub page_links {
	my ($this_page, $here, $link, @page) = @_;
	my ($data, $i) = ();

	for $i (0 .. $#page) { $data .= interpolate($page[$i] eq $this_page ? $here : $link, 'page', $page[$i], 'i', $i); }
	return $data;
}


sub replace {
	my $data = shift @_;
	
	# do find and replace on data
	my ($find, $replace) = ();
	while ($find =  shift @_) { 
		$replace = shift @_ or die "\n$prog: replace requires a find/replace pairs"; 
#		$data =~ s/$find/sprintf($replace)/gs;
		eval "\$data =~ s/$find/sprintf(\"$replace\")/ges";
	}
	return $data;
}


sub reset_output ($) {
	# reset output to null
	my ($output_ref) = @_;
	$$output_ref = '';
	return '';
}


sub read_file ($) {
	my ($file) = @_;
	open FILE, "<$file" or die "\n$prog: $file: $!\n";
	my $data = join '', <FILE>;
	close FILE;
	return $data;
}


sub single_query ($$) {
	my ($query, $format) = @_;

	# prepare query
	lock_tables(get_used_tables($query));
	my $sh = $db->prepare($query) or die "\n$prog: failed to prepare query: $DBI::errstr";
	$sh->execute or die "\n$prog: failed to execute query: $DBI::errstr";

	# get column names
	my @column = @{$sh->{NAME}};

	# get each record
	my (@tmp, $i);
	my $data = '';
	while (@tmp = $sh->fetchrow_array) {
		# interleave column names and data
		my (@var, $tmp) = ();
		for $i (0 .. $#column) { push @var, $column[$i], shift @tmp; }
			
		# interpolate data into record format, then eval to activate any backslashed chars
		$data .= eval 'interpolate($format, @var)';
	}
	$sh->finish or die "\n$prog: failed to release handle: $DBI::errstr";
	
	return $data;
}


sub voting_summary ($$$$$$$$$$$$$) {
	my ($date_query, $date_format, $type, $issue_length_query, $voter_query, $column_width, $list_query, $list_format, $list_closer, $vote_query, $vote_format, $key_query, $key_format) = @_;

	# pad vote format	
	my $column_fill = ' ' x $column_width;
	if ($type eq 'election') { $vote_format =~ s/\$column_fill/' ' x ($column_width-1)/eg; }
	else { $vote_format =~ s/\$column_fill/$column_fill/g; }

	# get voting dates
	my @date = (split /\n/, single_query($date_query, $date_format));

	# iterate over dates
	my ($date, $data) = ();
	foreach $date (@date) {
		$data .= "Voting ending " . `date -d '$date' +'%d %b %Y %T'` . "\n";

		my $max_issue_length = single_query(eval 'interpolate($issue_length_query, "date", $date)', "\$length");

		# get voters
		my @voter = (split /\n/, single_query(eval 'interpolate($voter_query, "date", $date)', "\$name\n"));

		# maker header	
		my ($i, $j);
		for $i (0 .. 3) {
			$data .= ' ' x $max_issue_length;
			for $j (0 .. $#voter) { $data .= $column_fill . (length($voter[$j]) <= $i ? ' ' : substr($voter[$j], $i, 1)); }
			$data .= "\n";
		}		

		# make line
		$data .= '-' x (($column_width+1)*($#voter+1)+$max_issue_length) . "\n";

		# get votes
		$data .= list_query(eval 'interpolate($list_query, "date", $date, "max_issue_length", $max_issue_length)', $list_format, $list_closer, eval 'interpolate($vote_query, "date", $date)', $vote_format);

		if ($type eq 'election') { $data .= "\n" . single_query(eval 'interpolate($key_query, "date", $date)', $key_format); }

		$data .= "\n\n";
	}

	return $data;	
}


sub write_file ($$) {
	my ($file, $data) = @_;
	open OUT, ">$file" or die "\n$prog: $file: $!";
	print OUT $data;
	close OUT;
	print '.';
	return '';
}

# Do not remove the last line. File must return true.
1