gen.pl

application/x-perl

Download

#!/usr/bin/perl

use strict;
use warnings;

my @prefix = qw(i f d n t v);
my @suffix = qw(1 2 3 4 5 6 7 8 9);

sub tablename {
	my $dice = int(rand(10));
	if ($dice == 0) {
		return "mcv_huge";
	}
	return "mcv_huge_$dice";
}

sub indexname {
	my $tablename = shift;
	return join("_", $tablename, int(rand(10)), "idx");
}

sub random_fields {
	my %fields = ();
	my $count = int(rand(7))+2;
	while (scalar(keys %fields) < $count)
	{
		my $prefix = $prefix[int(rand(@prefix))];
		my $suffix = $suffix[int(rand(@suffix))];
		$fields{$prefix . $suffix} = 1;
	}
	return keys %fields;
}

sub print_create_table {
	my $tablename = tablename();
	print("
CREATE TABLE $tablename (
	i1 INTEGER, i2 INTEGER, i3 INTEGER, i4 INTEGER, i5 INTEGER, i6 INTEGER, i7 INTEGER, i8 INTEGER, i9 INTEGER,
	f1 FLOAT,   f2 FLOAT,   f3 FLOAT,   f4 FLOAT,   f5 FLOAT,   f6 FLOAT,   f7 FLOAT,   f8 FLOAT,   f9 FLOAT,
	d1 FLOAT8,  d2 FLOAT8,  d3 FLOAT8,  d4 FLOAT8,  d5 FLOAT8,  d6 FLOAT8,  d7 FLOAT8,  d8 FLOAT8,  d9 FLOAT8,
	n1 NUMERIC, n2 NUMERIC, n3 NUMERIC, n4 NUMERIC, n5 NUMERIC, n6 NUMERIC, n7 NUMERIC, n8 NUMERIC, n9 NUMERIC,
	t1 TEXT,    t2 TEXT,    t3 TEXT,    t4 TEXT,    t5 TEXT,    t6 TEXT,    t7 TEXT,    t8 TEXT,    t9 TEXT,
	v1 VARCHAR, v2 VARCHAR, v3 VARCHAR, v4 VARCHAR, v5 VARCHAR, v6 VARCHAR, v7 VARCHAR, v8 VARCHAR, v9 VARCHAR);\n");
}

sub print_create_index {
	my $tablename = tablename();
	my $indexname = indexname($tablename);
	my @fields = random_fields();
	print("CREATE INDEX $indexname ON $tablename (", join(", ", @fields), ");\n");
}

sub print_drop_table {
	my $tablename = tablename();
	print("DROP TABLE $tablename;\n");
}

sub print_drop_index {
	my $tablename = tablename();
	my $indexname = indexname($tablename);
	print("DROP INDEX $indexname;\n");
}

sub print_insert {
	my $tablename = tablename();
	printf("
INSERT INTO $tablename VALUES (
		%d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d,
		%d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d,
		'%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d',
		'%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d');\n",
	map { int(rand(5000)) } (1..54)
		);
}

sub print_delete {
	my $tablename = tablename();
	printf("DELETE FROM $tablename WHERE %s%d = %d;\n", $prefix[int(rand(4))], $suffix[int(rand(@suffix))], int(rand(5000)));
}

sub print_update {
	my $tablename = tablename();
	printf("UPDATE $tablename SET %s%d = %d WHERE %s%d = %d;\n",
		$prefix[int(rand(4))], $suffix[int(rand(@suffix))], int(rand(5000)),
		$prefix[int(rand(4))], $suffix[int(rand(@suffix))], int(rand(5000))
		);
}

sub print_vacuum {
	my $tablename = tablename();
	my $dice = int(rand(5));
	if ($dice == 0) {
		printf("VACUUM $tablename;\n");
	}
	elsif ($dice == 1) {
		printf("ANALYZE $tablename;\n");
	}
	elsif ($dice == 2) {
		printf("VACUUM ANALYZE $tablename;\n");
	}
	if ($dice == 3) {
		printf("VACUUM FULL $tablename;\n");
	}
	if ($dice == 4) {
		my $indexname = indexname($tablename);
		printf("CLUSTER $tablename ON $indexname;\n");
	}
}

sub print_create_statistics {
	my $tablename = tablename();
	my @fields = random_fields();
	print("CREATE STATISTICS mcv_huge_", int(rand(5000)), " (mcv) ON ", join(", ", @fields), " FROM $tablename;\n");
}

sub print_drop_statistics {
	print("DROP STATISTICS mcv_huge_", int(rand(5000)), ";\n");
}

sub randval {
	my $field = shift;
	if ($field =~ m/^[tv]/) {
		return("'" . int(rand(5000)) . "'");
	}
	return int(rand(5000));
}

sub print_explain {
	my $tablename = tablename();
	my %fields = ();
	my $count = int(rand(15))+2;
	while (scalar(keys %fields) < $count)
	{
		my $prefix = $prefix[int(rand(@prefix))];
		my $suffix = $suffix[int(rand(@suffix))];
		$fields{$prefix . $suffix} = 1;
	}
	print("EXPLAIN ANALYZE SELECT * FROM $tablename WHERE ",
		join(" AND ", map { "$_ = " . randval($_) } keys %fields),
	";\n");
}

sub print_something {
	my $dice = int(rand(732));
	if    ($dice < 100) { print_insert(); }
	elsif ($dice < 200) { print_delete(); }
	elsif ($dice < 300) { print_update(); }
	elsif ($dice < 400) { print_vacuum(); }
	elsif ($dice < 500) { print_create_statistics(); }
	elsif ($dice < 600) { print_drop_statistics(); }
	elsif ($dice < 700) { print_explain(); }
	elsif ($dice < 710) { print_create_table(); }
	elsif ($dice < 720) { print_create_index(); }
	elsif ($dice < 730) { print_drop_table(); }
	else                { print_drop_index(); }
}

for(1..100000) {
	print_something();
}