Bio::EnsEMBL::Funcgen::DBSQL DBAdaptor
SummaryIncluded librariesPackage variablesSynopsisDescriptionGeneral documentationMethods
WebCvsRaw content
Package variables
Privates (from "my" definitions)
$reg = "Bio::EnsEMBL::Registry"
Included modules
Bio::EnsEMBL::Utils::Argument qw ( rearrange )
Bio::EnsEMBL::Utils::Exception qw ( warning throw deprecate stack_trace_dump )
my $db = Bio::EnsEMBL::Funcgen::DBSQL::DBAdaptor->new
-host => "",
-dbname => "mus_musculus_funcgen_41_36b",
-species => "Mus_musculus",
-user => "anonymous",
-dnadb => $mouse_core_db,
-port => '3307',
my $experiment_adaptor = $db->get_ExperimentAdaptor();
This is a wrapper method for Bio::EnsEMBL::DBAdaptor, providing Funcgen
specific methods.
No description
No description
No description
No description
No description
Methods description
_get_schema_buildcode    nextTop
  Arg [1]    : Bio::EnsEMBL::Funcgen::DBSQL::DBAdaptor or Bio::EnsEMBL::DBSQL::DBAdaptor
Example : my $shema_build = $db->_get_schema_build($slice->adaptor->db());
Returntype : string
Exceptions : Throws if argument not supplied
Caller : general
Status : At risk - replace with MetaContainer method
  Example    : my $import_cmd = 'mysqlimport '.$db->connect_string()." $table_file";
Description: Retrieves the mysql cmdline connection string
Returntype : String
Exceptions : none
Caller : general
Status : At risk
  Arg [1]:     Bio::EnsEMBL::DBSQL::DBAdaptor
Arg [2]: string - coord_system name e.g. chromosome
Usage : my $dnadb = $db->dnadb();
Description: returns the database adaptor where the dna lives i.e. the core db for a given species
There are at least 2 cases where you need to set this explicitly
1. If you want to retrieve features on an assembly which is not the default in
the correspeonding core DB with matching schema_build
2. If the corresponding core DB is not available on the default ensembl DB
server(ensembldb/ens-livemirror) i.e. before a new release.
Status : At risk. - Might remove validation of CS
  Arg [1]    : string - table name
Arg [2] : int - table id
Example : my @states = @{$db->fetch_all_states('channel', 1)};
Description: Retrieves all states associated with the given table record
Returntype : Listref
Exceptions : Throws if arguments not supplied
Caller : general
Status : At risk - Move to Status
  Args       : string - group name
Example : my $group = $db->fetch_group_details('EBI');
Description: Gets group information for a given name
Returntype : ARRAYREF
Exceptions : Throws if no group name defined
Caller : general
Status : At risk - Move to GroupAdaptor
  Arg [1]    : string - table name
Arg [2] : int - table id
Arg [3] : string - status
Example : if($db->fetch_status_by_name('channel', 1, 'IMPORTED'){ ... };
Description: Retrieves given state associated with the table record
Returntype : ARRAYREF
Exceptions : Throws if arguments not supplied
Caller : general
Status : At risk - Move to Stasus
  Example    : my %pairs = %{$dba->get_available_adaptors()};
Description: gets a hash of the available adaptors
ReturnType : reference to a hash
Exceptions : none
Caller : Bio::EnsEMBL::Utils::ConfigRegistry
Status : Stable
  Arg [1]    : string - group name
Arg [2] : string - group location
Arg [3] : string - group contact (email or address)
Example : $db->import_group('EBI', 'Hinxton', '');
Description: Imports group information to the database
Returntype : none
Exceptions : Throws if arguments not supplied
Caller : general
Status : At risk - Move to GroupAdaptor
  Arg [1]    : string - class namespace
Arg [1] : Bio::EnsEMBL::Funcgen::Storable e.g. ResultSet etc.
Example : $db->is_stored_and_valid('Bio::EnsEMBL::Funcgen::ResultSet', $rset);
DESCRIPTION: Validates object class and stored status
Returntype : none
Exceptions : Throws if Storable is not valid or stored
Caller : general - Adaptors, objects will probably be better off implementing in situ.
This is to avoid having to test for the adaptor for every object which could slow things down
Status : At risk
  Arg [1]    : string - table name
Arg [1] : string - file path for file to load
Example : $db->load_table_data("result", $self->get_dir($results_dir)."/result.txt");
DESCRIPTION: Generic method to load a file into a specified table
Returntype : none
Exceptions : Throws if argument not supplied
Caller : general
Status : At risk - only used by for results at present, to be removed
  Arg [-DNADB]: (optional) Bio::EnsEMBL::DBSQL::DBAdaptor DNADB 
DNADB will be automatically select using the given parameters,
the current registry dnadb host or ensembldb.
Arg [-NO_CACHE]: (optional) int 1
This option will turn off caching for slice features, so,
every time a set of features is retrieved, they will come from
the database instead of the cache. This option is only recommended
for advanced users, specially if you need to store and retrieve
features. It might reduce performance when querying the database if
not used properly. If in doubt, do not use it or ask in ensembl-dev
Arg [..] : Other args are passed to superclass
Example : $db = new Bio::EnsEMBL::DBSQL::DBAdaptor(
-user => 'root',
-dbname => 'pog',
-host => 'caldy',
-driver => 'mysql' );
Exmaple2 : $db = new Bio::EnsEMBL::DBSQL::DBAdaptor(
-species => 'Homo_sapiens',
-group => 'core'
-user => 'root',
-dbname => 'pog',
-host => 'caldy',
-driver => 'mysql');
Description: Constructor for DBAdaptor.
Returntype : Bio::EnsEMBL::DBSQL::DBAdaptor
Exceptions : none
Caller : general
Status : Stable
  Arg [1]:     string - Assembly version e.g. for homo_sapiens_core_49_36k it would be 36
Usage : $efgdb->set_dnadb_by_assembly_version('36');
Description: Sets the dnadb to the latest version given the assembly version
Exceptions: Throws if no assembly version provided or cannot for appropriate dnadb on ensembldb
Status : At risk
  Arg [1]    : string - table name
Arg [2] : int - table id
Arg [3] : string - status
Example : $db->set_status('channel', 1, 'IMPORTED');
Returntype : ARRAYREF
Exceptions : Throws if arguments not supplied
Caller : general
Status : At risk - Move to Status
Methods code
sub _get_schema_build {
  my ($self, $db) = @_;

  #Have to explicitly pass self->db to this method if required, this highlights which db is being tested 
throw("Need to define a DBAdaptor to retrieve the schema_build from") if (! $db); #avoided using dnadb by default to avoid obfuscation of behaviour
my @dbname = split/_/, $db->dbc->dbname(); my $schema_build = pop @dbname; $schema_build = pop(@dbname).'_'.$schema_build; return $schema_build;
sub connect_string {
  my $self = shift;

  return '-h'.$self->dbc->host().' -u'.$self->dbc->username().' -p'.$self->dbc->password()
	.' -P'.$self->dbc->port().' '.$self->dbc->dbname();

sub dnadb {
   my ($self, $dnadb, $cs_name) = @_; 

  #super dnadb automatically sets the current DBAdaptor as the dnadb
#this is the only way of checking whether it has been defined properly.
#This needs to use the same host as the registry, which may differ from the efg host!
#What if we pass a reg config file? with multiple hosts?
#We probably always want to use the host of the current dnadb?
#Automatically selects the current core DB from the reg and may be wrong!!
#How do we want to deal with this?
#So we want a method in DBAdaptor to reset efg DB and dna DB in registry?
#Or do we just let this happen
if($dnadb || $self->SUPER::dnadb->group() ne 'core'){ if(! $dnadb){#Guess dnadb
#We are only guessing the dnadb if we have not already defined one
#Either by attaching to the efg db
#Or by loading the reg
#The Importer will automatically check if the dnadb matches and set_by_assembly_version
#Do we want this functionality in the efg db->new?
#The problem is redefining the db in the registry will not take the extra params required?
#Maybe the method will take this. Then we can implement this check and set_dnadb_by_assembly_version
#in new.
return $self->set_dnadb_by_assembly_version($self->dnadb_assembly); } $self->SUPER::dnadb($dnadb); #set default coordsystem here, do we need to handle non-chromosome here
$cs_name ||= 'chromosome'; my $cs; eval { $cs = $dnadb->get_CoordSystemAdaptor->fetch_by_name($cs_name)}; my $error = $@; if($error){ my ($schema, $build) = split/_/, $self->_get_schema_build($dnadb); $build =~ s/[a-z]//; throw("It appears that the schema of ".$dnadb->dbc->dbname. ' is incompatible with your current core API version('.$reg->software_version. "). You could try using the $schema version of the core API, or alternatively try specifying ". "different -dnadb/registry_host parameters to point to a make recent version containing build $build\n"); } #this will only add the default assembly for this DB, if we're generating on another we need to add it separately.
#or shall we fetch/add all by name?
#This is a non-obious store behaviour!!!!!!!!!!!!!!!!!
#This can result in coord_system entries being written
#unknowingly if you are using the efg DB with a write user/pass
$self->get_FGCoordSystemAdaptor->validate_and_store_coord_system($cs); } return $self->SUPER::dnadb();#never pass @_ here!
sub dnadb_assembly {
  my ($self, $assm) = @_;
  $self->{'dnadb_assm'} = $assm if $assm;
  return $self->{'dnadb_assm'};

#Redefine dbadb here to add coordsystem
sub dnadb_host {
  my ($self, $host) = @_;
  $self->{'dnadb_host'} = $host if $host;
  return $self->{'dnadb_host'};
sub dnadb_pass {
 my ($self, $pass) = @_;
 $self->{'dnadb_pass'} = $pass if $pass;
 return $self->{'dnadb_pass'};
sub dnadb_port {
  my ($self, $port) = @_;
  $self->{'dnadb_port'} = $port if $port;
  return $self->{'dnadb_port'};
sub dnadb_user {
  my ($self, $user) = @_;
  $self->{'dnadb_user'} = $user if $user;
  return $self->{'dnadb_user'};
sub fetch_all_states {
	my ($self, $table, $id) = @_;

	throw("DBAdaptor::fetch_all_states is deprecated");

	throw("Need to specifiy a table and an id to retrieve status") if (! $table || ! $id);

	my $sql = "SELECT state FROM status WHERE table_name=\"$table\" AND table_id=\"$id\"";

	my @states = map{ $_ = "@$_"} @{$self->dbc->db_handle->selectall_arrayref($sql)};

	return\@ states;
sub fetch_group_details {
	my ($self, $gname) = @_;

	throw("Need to specify a group name") if ! $gname;
	my $sql = "SELECT * from experimental_group where name=\"$gname\"";
	return $self->dbc->db_handle->selectrow_array($sql);
sub fetch_status_by_name {
	my ($self, $table, $id, $state) = @_;

	throw("DBAdaptor::fetch_status_by_name is deprecated");

	throw("Need to specify a table and an id to retrieve status") if (! $table || ! $id || ! $state);

	#should we enum the state?
my $sql = "SELECT state FROM status WHERE table_name=\"$table\" AND table_id=\"$id\" AND state=\"$state\""; return $self->dbc->db_handle->selectrow_array($sql);
sub get_available_adaptors {
  my ($self) = shift;
  my %pairs = (
			   'Channel'            => 'Bio::EnsEMBL::Funcgen::DBSQL::ChannelAdaptor',
			   'ExperimentalChip'   => 'Bio::EnsEMBL::Funcgen::DBSQL::ExperimentalChipAdaptor',
			   'ArrayChip'          => 'Bio::EnsEMBL::Funcgen::DBSQL::ArrayChipAdaptor',
			   'Array'              => 'Bio::EnsEMBL::Funcgen::DBSQL::ArrayAdaptor',
			   'ProbeSet'           => 'Bio::EnsEMBL::Funcgen::DBSQL::ProbeSetAdaptor',
			   'Probe'              => 'Bio::EnsEMBL::Funcgen::DBSQL::ProbeAdaptor',
			   'ProbeFeature'       => 'Bio::EnsEMBL::Funcgen::DBSQL::ProbeFeatureAdaptor',
			   'AnnotatedFeature'   => 'Bio::EnsEMBL::Funcgen::DBSQL::AnnotatedFeatureAdaptor',
			   'RegulatoryFeature'  => 'Bio::EnsEMBL::Funcgen::DBSQL::RegulatoryFeatureAdaptor',
			   'Experiment'         => 'Bio::EnsEMBL::Funcgen::DBSQL::ExperimentAdaptor',
			   'DataSet'            => 'Bio::EnsEMBL::Funcgen::DBSQL::DataSetAdaptor',
			   'FeatureType'        => 'Bio::EnsEMBL::Funcgen::DBSQL::FeatureTypeAdaptor',
			   'FGCoordSystem'      => 'Bio::EnsEMBL::Funcgen::DBSQL::CoordSystemAdaptor',#prepended FG o override core  adaptor
'MetaCoordContainer' => 'Bio::EnsEMBL::Funcgen::DBSQL::MetaCoordContainer', 'FeatureSet' => 'Bio::EnsEMBL::Funcgen::DBSQL::FeatureSetAdaptor', 'ResultSet' => 'Bio::EnsEMBL::Funcgen::DBSQL::ResultSetAdaptor', 'DataSet' => 'Bio::EnsEMBL::Funcgen::DBSQL::DataSetAdaptor', 'ExperimentalSet' => 'Bio::EnsEMBL::Funcgen::DBSQL::ExperimentalSetAdaptor', 'ExternalFeature' => 'Bio::EnsEMBL::Funcgen::DBSQL::ExternalFeatureAdaptor', 'CellType' => 'Bio::EnsEMBL::Funcgen::DBSQL::CellTypeAdaptor', 'DBEntry' => 'Bio::EnsEMBL::Funcgen::DBSQL::DBEntryAdaptor', 'Slice' => 'Bio::EnsEMBL::Funcgen::DBSQL::SliceAdaptor', 'ResultFeature' => 'Bio::EnsEMBL::Funcgen::DBSQL::ResultFeatureAdaptor', #New collections
'ResultFeatureCollection' => 'Bio::EnsEMBL::Funcgen::Collection::ResultFeature', #add required EnsEMBL(core) adaptors here
#Should write/retrieve from efg not dna db
'UnmappedObject' => 'Bio::EnsEMBL::DBSQL::UnmappedObjectAdaptor', 'Analysis' => 'Bio::EnsEMBL::DBSQL::AnalysisAdaptor', "MetaContainer" => 'Bio::EnsEMBL::DBSQL::MetaContainer', ); return (\%pairs);
sub import_group {
	my ($self, $gname, $loc, $contact) = @_;

	throw("Need to supply a group name, location and contact") if (!($gname && $loc && $contact));

	my $sql = "INSERT INTO experimental_group(name, location, contact) VALUES(\"$gname\",\" $loc\",\" $contact\")";

	#$self->dbc->db_handle->last_insert_id(undef, undef, undef, undef);	
return;#return last insert id here?
} #General Status methods
#will Move to Bio::EnsEMBL::Funcgen::DBSQL::Status
sub is_stored_and_valid {
  my ($self, $class, $obj) = @_;

  if(! (ref($obj) && $obj->isa($class) && $obj->is_stored($self))){
	#throw or warn and return boolean?
throw('Must provide a valid stored '.$class."\nParameter provided was:\t$obj"); } return; } #Move these to! Check method dependencies first!
sub load_table_data {
  my ($self, $table, $file, $ssh) = @_;

  chmod 0755, $file;

  #  warn("Importing $table data from $file");
#if this gives an Errcode: 2, then your mysql instance cannot see the file.
#This could be due to a soft link on a visible directory to an unmounted filesystem
#change this to use the mysqlimport?
#This is failing as ssh is not set up to login silently without password prompt
#Need to defined ssh keys?
#(my $tmp_file = $file) =~ s/.*\///;
#$tmp_file = '/tmp/'.$tmp_file;
#my $scp = 'scp $(hostname):'.$file." ".$self->dbc->host().":${tmp_file}";
#my $sql = "load data infile '$tmp_file' into table $table";
#remove tmp file via ssh if load successful
my $cmd = 'mysqlimport -L '.$self->connect_string().' '.$file; system($cmd) == 0 || throw("Failed to load data from $file\nExit code:\t".($?>>8)."\n$!"); return;
sub new {
  my ( $class, @args ) = @_;

  my $self = $class->SUPER::new(@args);
  #Currently only uses dnadb params to auto select
#If attached dnadb or default reg dnadb does not match
#given assembly version.
my ( $dnadb_host, $dnadb_user, $dnadb_port, $dnadb_pass, $dnadb_assm) = rearrange( [ 'DNADB_HOST', 'DNADB_USER', 'DNADB_PORT', 'DNADB_PASS', 'DNADB_ASSEMBLY', ], @args ); my $default_dnadb = $self->SUPER::dnadb; my ($default_host, $default_port, $default_user, $default_pass, $default_assm, $efg_assm, $dnadb_defined); if($default_dnadb->group eq 'core'){ #This means you have loaded a registry or pass a dnadb to the efg DBAdaptor
$default_host = $default_dnadb->dbc->host; $default_port = $default_dnadb->dbc->port; $default_user = $default_dnadb->dbc->username; $default_pass = $default_dnadb->dbc->password; ($default_assm = (split/_/, $self->_get_schema_build($default_dnadb))[1]) =~ s/[a-z]//; $dnadb_defined = 1; } #Defaults now set in dnadb as we want to test registry first;
#These will pick default_dnadb values if params not explicitly set
$self->{'dnadb_host'} = $dnadb_host || $default_host || ''; #Do we need this to be dnadb_ports 3306 5306 for different mysqls?
#This is not correct for ensembldb, but we over-ride this in set_dnadb_by_assembly_version.
$self->{'dnadb_port'} = $dnadb_port || $default_port || 3306; $self->{'dnadb_user'} = $dnadb_user || $default_user || 'anonymous'; $self->{'dnadb_pass'} = $dnadb_pass || $default_pass || undef; ($efg_assm = (split/_/, $self->_get_schema_build($self))[1]) =~ s/[a-z]//; $dnadb_assm ||= $default_assm || $efg_assm; $self->{'dnadb_assm'} = $dnadb_assm; #Now we only want to reset the dnadb if it does not match the dnadb_assm
#Use dnadb method here as this will either return a predefined dnadb(attached or reg) or auto select
#Can we change this so that it only does this when we call dnadb?
#This resulted in circular reference, so we need to be careful about changing this
if($self->_get_schema_build($self->dnadb()) !~ /[0-9]+_${dnadb_assm}[a-z]*$/){ #Do we need to consider reg_config here?
#We could potentially have two version of the core DB in the config
#But we would expect the user to handle predefining the dnadb correctly in this case
warn ':: WARNING: dnadb('.$self->dnadb->dbc->dbname.') does not match required -dnadb_assm '.$self->dnadb_assembly; if($dnadb_defined && $dnadb_host){ warn ":: Over-riding pre-defined dnadb host values(reg/-dnadb arg) with dnadb params:\t". $self->dnadb_user.'@'.$self->dnadb_host.':'.$self->dnadb_port; } $self->set_dnadb_by_assembly_version($self->dnadb_assembly); } return $self;
sub set_dnadb_by_assembly_version {
  my ($self, $assm_ver) = @_;

  throw('Must provide and assembly version to set the dnadb') if ! defined $assm_ver;
  my $lspecies = $reg->get_alias($self->species());

  throw('Must provide a species to automatically set dnadb') if $lspecies eq 'default';

  #So we use params first
#else registry params
#else ensembldb
#Can we set these in new?
my @ports = ($self->dnadb_port); #Start with lastest MySQL instances
#WE ARE OVER-RIDING specified port here!
#we should really account for this and make it nr
if($self->dnadb_host eq 'ensdb-archive'){#
@ports = (5304, 3304); } elsif($self->dnadb_host eq ''){ @ports = (5306, 3306); } #We should probably allow for non-ensembldb core DBs here too
#Do we need to account for other ports, staging etc for release?
#These should run fine on 3306.
#my $current_port = $self->dnadb->dbc->port;
#This is assuming dnadb port will only ever be one or the other
#This assumption is restricted to ensembldb in the port loop
#my $tmp_port = ($current_port == 3306) ? 5306 : 3306;
#Do we need to get species from reg here?
#And test the species has been set?
my $sql = 'show databases like "'.$lspecies.'_core_%_'.$assm_ver.'%"'; my ($dbh, @dbnames, $port, $host_port); foreach $port(@ports){ #This is probably duplicating connections and over-riding any connection
#pooling going on in the base DBConnection if we are using the same host port
#as the registry connection
$dbh = DBI->connect('DBI:mysql:host='.$self->dnadb_host.";port=${port}", $self->dnadb_user, $self->dnadb_pass, {'RaiseError' => 1}); #should we eval this?
@dbnames = map {$_ = "@$_"} @{$dbh->selectall_arrayref($sql)}; #sort and filter out non-core DBs
#This will always take the latest release, not the latest genebuild version
#Which is probably what we want anyway
@dbnames = grep(/core_[0-9]/, sort @dbnames); if(scalar(@dbnames)==0){ warn(':: Failed to find '.$self->species.' core DB for assembly version '.$assm_ver.' using ' .$self->dnadb_user.'@'.$self->dnadb_host.':'.$port); } else{ $host_port = $port; last; } } throw("Failed to find dnadb with assembly version $assm_ver") if(scalar(@dbnames)==0); warn ":: Auto-selecting build $assm_ver core DB as:\t". $self->dnadb_user.'@'.$dbnames[$#dbnames].':'.$self->dnadb_host.':'.$host_port."\n"; my $db = $reg->reset_DBAdaptor($lspecies, 'core', $dbnames[$#dbnames], $self->dnadb_host, $host_port, $self->dnadb_user, $self->dnadb_pass); $self->dnadb($db); return $db; } #Group methods, as not adaptor/class for Group(used in ExperimentAdaptor at present)
#will disppear when Group and GroupAdaptor written
sub set_status {
	my ($self, $table, $id, $state) = @_;

	throw("DBAdaptor::set_status is deprecated");

	throw("Need to supply a table, dbid and a valid status") if (!($table && $id && $state));

	my $sql = "INSERT INTO status(table_id, table_name, state) VALUES(\"$id\",\" $table\",\" $state\")";

General documentation
Post questions to the EnsEMBL development list <>
Nathan Johnson,
  Arg [1]    : (optional) int - coord_system_id
Example : my $slice_adaptor = $db->get_SliceAdaptor($cs->dbID());
DESCRIPTION: Retrieves a slice adaptor from the dnadb corresponding
to the coord_system_id, or retrieves from the default dnadb
Returntype : Bio::EnsEMBL::DBSQL::SLiceAdaptor
Exceptions : Throws if arguments not supplied
Caller : general
Status : At risk - remove and add this to BaseFeatureAdaptor->fetch_all_by_Slice_constraint