Ensembl 17 is on the way to be releassd. Everything there are more than 20 databases to be added and the proper privileges need to be updated to the cluster. I feel it tedious to do it everytime line by line.
So, I wrote a perl script to do it for me, including:
- Drop old database
- Create new database
- Grant user access
- Correctify user access
- Test the user privileges by creating/dropping temp tables
Here comes there codes:
#!/usr/bin/perl
## This script tries to create the proper databases
## for the new ensembl databases and grant user with enough privileges
##
## Chen Peng, Oct 2003
my $debug = 0;
use strict;
use DBI;
## return 0 if it fails
sub db_do {
my ($dbh, $sql) = @_; return 0 unless defined $dbh;
if ($debug) { return warn "debug :: $sql\n"; }
else { return $dbh->do($sql); };
}
##-------------------------------------------------------------------------------
## Main program starts here
my $usage = "
This script creates databases for new ensembl databases with proper privs
$0 dbhost root_pass normal_user";
my ($dbhost, $root_pass, $normal_user) = @ARGV;
die "$usage\n" unless
defined $dbhost && defined $root_pass && defined $normal_user;
my $dsn = "dbi:mysql:host=$dbhost";
my $dbh = DBI->connect($dsn,"root", $root_pass) ||
die "fail connect to $dbhost as root/$root_pass\n";
my @db = ();
while (my $db=<DATA>) {
chomp $db;
next if $db =~ /^\s*$/;
push @db, $db;
}
warn "\nDrop old database\n\n";
foreach my $db (@db) {
my $sql1 = "drop database $db";
my $gap = 57 - length($db);
(&db_do($dbh, $sql1) &&
warn "Clean :: $db" . "." x $gap . "[OK]\n") ||
warn "Clean :: $db" . "." x $gap . "[FAIL]\n";
}
warn "\nCreate new database\n\n";
foreach my $db (@db) {
my $sql1 = "create database $db";
my $sql2 = "grant all on $db.* to $normal_user";
my $gap = 60 - length($db);
(&db_do($dbh, $sql1) && &db_do($dbh, $sql2) &&
warn "DB :: $db" . "." x $gap . "[OK]\n") ||
warn "DB :: $db" . "." x $gap . "[FAIL]\n";
}
my $sql = "
delete from mysql.user where
user='$normal_user' and host='\%'";
&db_do($dbh, $sql) && warn "\nclean up user privileges [OK]\n"
|| warn "\nclean up user privileges [FAIL]\n";
$dbh->disconnect;
warn "\nStart test connectivity\n";
foreach my $db (@db) {
$dbh = DBI->connect("$dsn:database=$db", $normal_user) ||
(warn "fail connect to $dbhost:$db as $normal_user/NO_PASS" && next);
my $gap = 66 - length("$normal_user\@$db");
(&db_do($dbh, "create table temp (name varchar(2))") &&
&db_do($dbh, "drop table temp") &&
warn "$normal_user\@$db" . "." x $gap . "[OK]\n") ||
warn "$normal_user\@$db" . "." x $gap . "[FAIL]\n";
$dbh->disconnect;
}
## Main program ends here
## -------------------------------------------------------------------------------
## below lists the database names to be created
## only whte space is allow, no comments allowed after __DATA__ TAG
__DATA__
caenorhabditis_briggsae_core_17_25
caenorhabditis_briggsae_estgene_17_25
caenorhabditis_briggsae_lite_17_25
caenorhabditis_elegans_core_17_102
caenorhabditis_elegans_lite_17_102
drosophila_melanogaster_core_17_3a
drosophila_melanogaster_lite_17_3a
fugu_rubripes_core_17_2
fugu_rubripes_lite_17_2
anopheles_gambiae_core_17_2a
anopheles_gambiae_estgene_17_2a
anopheles_gambiae_lite_17_2a
anopheles_gambiae_snp_17_2a
mus_musculus_core_17_30
mus_musculus_est_17_30
mus_musculus_estgene_17_30
mus_musculus_lite_17_30
mus_musculus_snp_17_30
ensembl_compara_17_1
ensembl_family_17_1
ensembl_go_17_1
ensembl_help_17_1
ensembl_web_user_db_17_1
ensembl_mart_17_1
danio_rerio_core_17_2
danio_rerio_est_17_2
danio_rerio_estgene_17_2
danio_rerio_lite_17_2
danio_rerio_snp_17_2
rattus_norvegicus_core_17_2
rattus_norvegicus_est_17_2
rattus_norvegicus_estgene_17_2
rattus_norvegicus_lite_17_2
rattus_norvegicus_snp_17_2