[MOBY-guts] biomoby commit
Dennis Wang
dwang at pub.open-bio.org
Wed Jul 13 18:33:41 UTC 2005
dwang
Wed Jul 13 14:33:41 EDT 2005
Update of /home/repository/moby/moby-live/Perl/MOBY/Adaptor/moby/queryapi
In directory pub.open-bio.org:/tmp/cvs-serv21144/Perl/MOBY/Adaptor/moby/queryapi
Modified Files:
mysql.pm
Log Message:
1st commit by Dennis
moby-live/Perl/MOBY/Adaptor/moby/queryapi mysql.pm,1.10,1.11
===================================================================
RCS file: /home/repository/moby/moby-live/Perl/MOBY/Adaptor/moby/queryapi/mysql.pm,v
retrieving revision 1.10
retrieving revision 1.11
diff -u -r1.10 -r1.11
--- /home/repository/moby/moby-live/Perl/MOBY/Adaptor/moby/queryapi/mysql.pm 2005/05/30 23:33:43 1.10
+++ /home/repository/moby/moby-live/Perl/MOBY/Adaptor/moby/queryapi/mysql.pm 2005/07/13 18:33:41 1.11
@@ -98,33 +98,222 @@
}
+sub add_condition{
+ my ($statement, @params) = @_;
+ my @bindvalues = ();
+ my $condition = "where ";
+
+ foreach my $param (@params )
+ {
+ if (($param eq 'and') || ($param eq 'or'))
+ {
+ $condition .= $param . " ";
+ }
+ else
+ {
+ my %pair = %$param;
+
+ for my $key (keys %pair)
+ {
+ if (defined $pair{$key})
+ {
+ $condition .= $key . " = ? ";
+ push(@bindvalues, $pair{$key});
+ }
+ else
+ {
+ $condition .= $key . " IS NULL "
+ }
+ }
+ }
+ }
+ $statement .= $condition;
+ return ($statement, @bindvalues);
+ }
+
+# preforms query but returns a reference to an array containing hash references
+sub do_query{
+ my ($dbh, $statement, @bindvalues) = @_;
+ my $sth = $dbh -> prepare($statement);
+ if (@bindvalues < 1)
+ {
+ $sth->execute;
+ }
+ else
+ {
+ $sth->execute(@bindvalues);
+ }
+ # returns an array of hash references
+ my $arrayHashRef = $sth->fetchall_arrayref({});
+ return $arrayHashRef;
+}
+
+sub get_value{
+ my ($key, @params) = @_;
+
+ foreach my $param (@params )
+ {
+ my %pair = %$param;
+ for my $tmp (keys %pair)
+ {
+ if ($tmp eq $key){
+ return $pair{$key};
+ }
+ }
+ }
+}
+
+# general query subroutine
+# parameters:
+# statement - a string SQL statement
+# bind_values - an array of values to be bound to the parameters
+# returns:
+# resultset - reference to an array containing array references
+sub general_query{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $statement = get_value('statement', @args);
+ my $sth = $dbh->prepare($statement};
+ if (@args > 1)
+ {
+ my $bindvalues = get_value('bind_values', @args);
+ $sth->execute(@$bindvalues);
+ }
+ else {
+ $sth->execute;
+ }
+ my $result = $sth->fetchall_arrayref();
+ return result;
+}
+
+sub query_collection_input{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+
+ my $statement = "select
+ collection_input_id,
+ article_name,
+ service_instance_id
+ from collection_input ";
+ my @bindvalues = ();
+
+ if (@args > 0)
+ {
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ }
+
+ my $result = do_query($dbh, $statement, @bindvalues);
+ return $result;
+}
sub insert_collection_input {
- my ($self, %args) = @_;
+ my ($self, @args) = @_;
my $dbh = $self->dbh;
# if ($self->sourcetype eq "MOBY::Adaptor::moby::queryapi::mysql"){
# this should be dropped down into the mysql.pm module?? probably...
- $self->dbh->do("insert into collection_input (service_instance_id, article_name) values (?,?)", undef, ($args{service_instance}, $args{article_name}));
+ $self->dbh->do("insert into collection_input (service_instance_id, article_name) values (?,?)",
+ undef, (get_value('service_instance_id', @args), get_value('article_name', @args)));
my $id=$self->dbh->{mysql_insertid};
return $id;
# }
}
+sub delete_collection_input{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $statement = "delete from collection_input ";
+ my @bindvalues = ();
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ $dbh->do( $statement,
+ undef, @bindvalues );
+
+ if (dbh->err){
+ return (0, dbh->errstr);
+ }
+ else{
+ return 1;
+ }
+}
+
+# query subroutine selects all columns
+sub query_collection_output{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+
+ my $statement = "select
+ collection_output_id,
+ article_name,
+ service_instance_id
+ from collection_output ";
+ my @bindvalues = ();
+
+ if (@args > 0)
+ {
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ }
+
+ my $result = do_query($dbh, $statement, @bindvalues);
+ return $result;
+}
+
sub insert_collection_output {
- my ($self, %args) = @_;
+ my ($self, @args) = @_;
my $dbh = $self->dbh;
# if ($self->sourcetype eq "MOBY::Adaptor::moby::queryapi::mysql"){
# this should be dropped down into the mysql.pm module?? probably...
- $self->dbh->do("insert into collection_output (service_instance_id, article_name) values (?,?)", undef, ($args{service_instance}, $args{article_name}));
+ $self->dbh->do("insert into collection_output (service_instance_id, article_name) values (?,?)",
+ undef, (get_value('service_instance_id', @args), get_value('article_name', @args)));
my $id=$self->dbh->{mysql_insertid};
return $id;
# }
}
+sub delete_collection_output{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $statement = "delete from collection_output ";
+ my @bindvalues = ();
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ $dbh->do( $statement,
+ undef, @bindvalues );
+
+ if (dbh->err){
+ return (0, dbh->errstr);
+ }
+ else{
+ return 1;
+ }
+}
+
+
+sub query_simple_input{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+
+ my $statement = "select
+ simple_input_id,
+ object_type_uri,
+ namespace_type_uris,
+ article_name,
+ service_instance_id,
+ collection_input_id
+ from simple_input ";
+ my @bindvalues = ();
+
+ if (@args > 0)
+ {
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ }
+
+ my $result = do_query($dbh, $statement, @bindvalues);
+ return $result;
+}
+
+
sub insert_simple_input {
- my ($self, %args) = @_;
+ my ($self, @args) = @_;
my $dbh = $self->dbh;
- my $si = $args{collection_input_id}?undef:$args{service_instance_id};
+ my $si = get_value('collection_input_id', @args)?undef:get_value('service_instance_id', @args);
$dbh->do("insert into simple_input
(object_type_uri,
@@ -134,21 +323,59 @@
collection_input_id)
values (?,?,?,?,?)",
undef,
- ($args{object_type_uri},
- $args{namespace_type_uris},
- $args{article_name},
+ (get_value('object_type_uri', @args),
+ get_value('namespace_type_uris', @args),
+ get_value('article_name', @args),
# here
$si,
- $args{collection_input_id}));
+ get_value('collection_input_id', @args)));
my $id=$dbh->{mysql_insertid};
return $id;
}
+sub delete_simple_input{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $statement = "delete from simple_input ";
+ my @bindvalues = ();
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ $dbh->do( $statement,
+ undef, @bindvalues )
+ if (dbh->err){
+ return (0, dbh->errstr);
+ }
+ else{
+ return 1;
+ }
+}
+
+sub query_simple_output{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+
+ my $statement = "select
+ simple_output_id,
+ object_type_uri,
+ namespace_type_uris,
+ article_name,
+ service_instance_id,
+ collection_output_id
+ from simple_output ";
+ my @bindvalues = ();
+
+ if (@args > 0)
+ {
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ }
+
+ my $result = do_query($dbh, $statement, @bindvalues);
+ return $result;
+}
sub insert_simple_output {
- my ($self, %args) = @_;
+ my ($self, @args) = @_;
my $dbh = $self->dbh;
- my $si = $args{collection_output_id}?undef:$args{service_instance_id};
+ my $si = get_value('collection_output_id', @args)?undef:get_value('service_instance_id', @args);
$dbh->do("insert into simple_output
(object_type_uri,
@@ -158,78 +385,813 @@
collection_output_id)
values (?,?,?,?,?)",
undef,
- ($args{object_type_uri},
- $args{namespace_type_uris},
- $args{article_name},
+ (get_value('object_type_uri', @args),
+ get_value('namespace_type_uris', @args),
+ get_value('article_name', @args),
# and here
$si,
- $args{collection_output_id}));
+ get_value('collection_output_id', @args)));
my $id=$dbh->{mysql_insertid};
return $id;
}
-sub query_service_instance {
- my ($self, %args) = @_;
+sub delete_simple_output{
+ my ($self, @args) = @_;
my $dbh = $self->dbh;
+ my $statement = "delete from simple_output ";
+ my @bindvalues = ();
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ $dbh->do( $statement,
+ undef, @bindvalues );
+ if (dbh->err){
+ return (0, dbh->errstr);
+ }
+ else{
+ return 1;
+ }
+}
- my ($serviceid, $category, $name, $typeURI, $authURI,$url,$email,$authoritative,$desc, $signatureURL, $lsid) = $dbh->selectrow_array( # does this service already exist?
- q{select
+sub query_secondary_input{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+
+ my $statement = "select
+ secondary_input_id,
+ default_value,
+ maximum_value,
+ minimum_value,
+ enum_value,
+ datatype,
+ article_name,
+ service_instance_id
+ from secondary_input ";
+ my @bindvalues = ();
+
+ if (@args > 0)
+ {
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ }
+
+ my $result = do_query($dbh, $statement, @bindvalues);
+ return $result;
+}
+
+sub insert_secondary_input{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ $dbh->do(
+q{insert into secondary_input (default_value,maximum_value,minimum_value,enum_value,datatype,article_name,service_instance_id) values (?,?,?,?,?,?,?)},
+ undef,
+ (
+ get_value('default_value', @args), get_value('maximum_value', @args),
+ get_value('minimum_value', @args), get_value('enum_value', @args),
+ get_value('datatype', @args), get_value('article_name', @args),
+ get_value('service_instance_id', @args),
+ )
+ );
+ return $dbh->{mysql_insertid};
+}
+
+sub delete_secondary_input{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $statement = "delete from secondary_input ";
+ my @bindvalues = ();
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ $dbh->do( $statement,
+ undef, @bindvalues );
+
+ if (dbh->err){
+ return (0, dbh->errstr);
+ }
+ else{
+ return 1;
+ }
+}
+
+# query subroutine will selects all rows from object table
+sub query_object
+{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+
+ my $statement = "select
+ object_id,
+ object_lsid,
+ object_type,
+ description,
+ authority,
+ contact_email
+ from object ";
+ my @bindvalues = ();
+
+ if (@args > 0)
+ {
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ }
+
+ my $result = do_query($dbh, $statement, @bindvalues);
+ return $result;
+}
+
+# inserts a new tuple into object table
+sub insert_object{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ $dbh->do("insert into object
+ (object_type,
+ object_lsid,
+ description,
+ authority,
+ contact_email)
+ values (?,?,?,?,?)",
+ undef,
+ (get_value('object_type', @args),
+ get_value('object_lsid', @args),
+ get_value('description', @args),
+ get_value('authority', @args),
+ get_value('contact_email', @args)));
+ my $id=$dbh->{mysql_insertid};
+ return $id;
+}
+
+sub delete_object{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $statement = "delete from object ";
+ my @bindvalues = ();
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ $dbh->do( $statement,
+ undef, @bindvalues );
+
+ if (dbh->err){
+ return (0, dbh->errstr);
+ }
+ else{
+ return 1;
+ }
+}
+
+sub query_object_term2term{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+
+ my $statement = "select
+ assertion_id,
+ relationship_type,
+ object1_id,
+ object2_id,
+ object2_articlename
+ from object_term2term ";
+ my @bindvalues = ();
+
+ if (@args > 0)
+ {
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ }
+
+ my $result = do_query($dbh, $statement, @bindvalues);
+ return $result;
+}
+
+sub insert_object_term2term{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ dbh->do(
+q{insert into object_term2term (relationship_type, object1_id, object2_id, object2_articlename) values (?,?,?,?)},
+ undef,
+ ( get_value('relationship_type', @args),
+ get_value('object1_id', @args),
+ get_value('object2_id', @args),
+ get_value('object2_articlename', @args) )
+ );
+
+ return $dbh->{mysql_insertid};
+}
+
+# selects all the columns from service_instance table
+# where is lsid?
+sub query_service_instance {
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+
+ my $statement = "select
service_instance_id,
category,
- serviceName,
+ servicename,
service_type_uri,
- authority.authority_uri,
+ authority_id,
url,
- service_instance.contact_email,
+ contact_email,
authoritative,
description,
signatureURL,
lsid
- from service_instance, authority
- where
- service_instance.authority_id = authority.authority_id
- and servicename=? and authority.authority_uri = ?
- },
- undef,($args{servicename}, $args{authURI}));
- return undef unless $serviceid;
- return {serviceid => $serviceid,
- category => $category,
- name => $name,
- servcetype => $typeURI,
- authURI => $authURI,
- signatureURL => $signatureURL,
- url => $url,
- email => $email,
- authoritative => $authoritative,
- desc => $desc,
- lsid => $lsid
- };
+ from service_instance ";
+ my @bindvalues = ();
+
+ if (@args > 0)
+ {
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ }
+
+ my $result = do_query($dbh, $statement, @bindvalues);
+ return $result;
}
+# custom query for Moby::Central.pm->findService()
+sub match_service_type_uri{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+
+ my $uri_list = get_value('service_type_uri', @args);
+
+ my $result = $dbh->selectall_arrayref(
+"select service_instance_id from service_instance where service_type_uri in ($uri_list)"
+ );
+ return $result;
+}
sub insert_service_instance {
- my ($self, %args) = @_;
+ my ($self, @args) = @_;
my $dbh = $self->dbh;
$dbh->do(q{insert into service_instance (category, servicename, service_type_uri, authority_id, url, contact_email, authoritative, description, signatureURL, lsid) values (?,?,?,?,?,?,?,?,?,?)},
- undef,
- $args{'category'},
- $args{'servicename'},
- $args{'service_type_uri'},
- $args{'authority_id'},
- $args{'url'},
- $args{'contact_email'},
- $args{'authoritative'},
- $args{'description'},
- $args{'signatureURL'},
- $args{'lsid'});
+ undef,(
+ get_value('category', @args),
+ get_value('servicename', @args),
+ get_value('service_type_uri', @args),
+ get_value('authority_id', @args),
+ get_value('url', @args),
+ get_value('contact_email', @args),
+ get_value('authoritative', @args),
+ get_value('description', @args),
+ get_value('signatureURL', @args),
+ get_value('lsid', @args)));
my $id = $dbh->{mysql_insertid};
return $id;
}
+sub delete_service_instance{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $statement = "delete from service_instance ";
+ my @bindvalues = ();
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ $dbh->do( $statement,
+ undef, @bindvalues );
+ if (dbh->err){
+ return (0, dbh->errstr);
+ }
+ else{
+ return 1;
+ }
+}
+
+# Selects all columns
+sub query_authority {
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+
+ my $statement = "select
+ authority_id,
+ authority_common_name,
+ authority_uri,
+ contact_email
+ from authority ";
+ my @bindvalues = ();
+
+ if (@args > 0)
+ {
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ }
+
+ my $result = do_query($dbh, $statement, @bindvalues);
+ return $result;
+}
+
+sub getUniqueAuthorityURI{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ $statement = "select distinct authority_uri from authority";
+ $bindvalues = ();
+ my $result = do_query($dbh, $statement, @bindvalues);
+ return $result;
+}
+
+sub insert_authority{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ $dbh->do("insert into authority
+ (authority_common_name,
+ authority_uri,
+ contact_email)
+ values (?,?,?)",
+ undef,
+ (get_value('authority_common_name', @args),
+ get_value('authority_uri' @args),
+ get_value('contact_email', @args)));
+ my $id=$dbh->{mysql_insertid};
+ return $id;
+}
+
+sub query_service{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $statement = "select
+ service_id,
+ service_lsid,
+ service_type,
+ description,
+ authority,
+ contact_email
+ from service ";
+ my @bindvalues = ();
+ if (@args > 0)
+ {
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ }
+
+ my $result = do_query($dbh, $statement, @bindvalues);
+ return $result;
+}
+
+sub insert_service{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ dbh->do(
+q{insert into service (service_type, service_lsid, description, authority, contact_email) values (?,?,?,?,?)},
+ undef,
+ (
+ get_value('service_type', @args), get_value('service_lsid', @args), get_value('description', @args),
+ get_value('authority', @args), get_value('contact_email', @args)
+ )
+ );
+ return dbh->{mysql_insertid};
+}
+
+sub delete_service{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $statement = "delete from service ";
+ my @bindvalues = ();
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ $dbh->do( $statement,
+ undef, @bindvalues );
+
+ if (dbh->err){
+ return (0, dbh->errstr);
+ }
+ else{
+ return 1;
+ }
+}
+
+sub query_service_term2term{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $statement = "select
+ assertion_id,
+ relationship_type,
+ service1_id,
+ service2_id
+ from service_term2term ";
+ my @bindvalues = ();
+ if (@args > 0)
+ {
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ }
+
+ my $result = do_query($dbh, $statement, @bindvalues);
+ return $result;
+}
+
+sub insert_service_term2term{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ dbh->do(
+q{insert into service_term2term (relationship_type, service1_id, service2_id) values (?,?,?)},
+ undef,
+ ( get_value('relationship_type', @args), get_value('service1_id', @args), get_value('service2_id', @args))
+ );
+ return dbh->{mysql_insertid};
+}
+
+sub delete_service_term2term{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $statement = "delete from service_term2term ";
+ my @bindvalues = ();
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ $dbh->do( $statement,
+ undef, @bindvalues );
+
+ if (dbh->err){
+ return (0, dbh->errstr);
+ }
+ else{
+ return 1;
+ }
+}
+
+sub query_relationship{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+
+ my $statement = "select
+ relationship_id,
+ relationship_lsid,
+ relationship_type,
+ container,
+ description,
+ authority,
+ contact_email,
+ ontology
+ from relationship ";
+ my @bindvalues = ();
+
+ if (@args > 0)
+ {
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ }
+
+ my $result = do_query($dbh, $statement, @bindvalues);
+ return $result;
+}
+
+sub query_namespace{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+
+ my $statement = "select
+ namespace_id,
+ namespace_lsid,
+ namespace_type,
+ description,
+ authority,
+ contact_email
+ from namespace ";
+ my @bindvalues = ();
+
+ if (@args > 0)
+ {
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ }
+
+ my $result = do_query($dbh, $statement, @bindvalues);
+ return $result;
+}
+
+sub insert_namespace{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ dbh->do(
+q{insert into namespace (namespace_type, namespace_lsid, description, authority,contact_email) values (?,?,?,?,?)},
+ undef,
+ (
+ get_value('namespace_type', @args), get_value('namespace_lsid', @args), get_value('description', @args),
+ get_value('authority', @args), get_value('contact_email', @args)
+ )
+ );
+ return dbh->{mysql_insertid};
+}
+
+sub delete_namespace{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $statement = "delete from namespace ";
+ my @bindvalues = ();
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ $dbh->do( $statement,
+ undef, @bindvalues );
+
+ if (dbh->err){
+ return (0, dbh->errstr);
+ }
+ else{
+ return 1;
+ }
+}
+sub query_namespace_term2term{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+
+ my $statement = "select
+ assertion_id,
+ relationship_type,
+ namespace1_id,
+ namespace2_id
+ from namespace_term2term ";
+ my @bindvalues = ();
+
+ if (@args > 0)
+ {
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ }
+
+ my $result = do_query($dbh, $statement, @bindvalues);
+ return $result;
+}
+
+sub delete_namespace_term2term{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $statement = "delete from namespace_term2term ";
+ my @bindvalues = ();
+ ($statement, @bindvalues) = add_condition($statement, @args);
+ $dbh->do( $statement,
+ undef, @bindvalues );
+
+ if (dbh->err){
+ return (0, dbh->errstr);
+ }
+ else{
+ return 1;
+ }
+}
+
+# custom query subroutine for Moby::Central.pm->deregisterObjectClass()
+sub checkClassUsedByService
+{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $errorMsg = 1;
+ my $existingURI = get_value('object_type_uri', @args);
+
+ my ($id) = $dbh->selectrow_array(
+q{select service_instance.service_instance_id from service_instance natural join simple_input where object_type_uri = ?},
+ undef, $existingURI
+ );
+ return $errorMsg
+ if ($id);
+
+ ($id) = $dbh->selectrow_array(
+q{select service_instance.service_instance_id from service_instance natural join simple_output where object_type_uri = ?},
+ undef, $existingURI
+ );
+ return $errorMsg
+ if ($id);
+
+ ($id) = $dbh->selectrow_array(
+q{select service_instance.service_instance_id from service_instance natural join collection_input natural join simple_input where object_type_uri = ?},
+ undef, $existingURI
+ );
+ return $errorMsg
+ if ($id);
+
+ ($id) = $dbh->selectrow_array(
+q{select service_instance.service_instance_id from service_instance natural join collection_output natural join simple_output where object_type_uri = ?},
+ undef, $existingURI
+ );
+ return $errorMsg
+ if ($id);
+
+ return 0;
+}
+
+# custom query routine for Moby::Central.pm -> deregisterNamespace()
+sub checkNamespaceUsedByService{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $existingURI = get_value('namespace_type_uris', @args);
+ my $term = get_value('term', @args);
+
+my $sth =
+ $dbh->prepare(
+"select service_instance.service_instance_id, namespace_type_uris from service_instance natural join simple_input where INSTR(namespace_type_uris,'$existingURI')"
+ );
+ $sth->execute;
+
+ while ( my ( $id, $ns ) = $sth->fetchrow_array() ) {
+ my @nss = split ",", $ns;
+ foreach (@nss) {
+ $_ =~ s/\s//g;
+ my $errstr = "Namespace Type $term ($_) is used by a service (service ID number $id) and may not be deregistered";
+ return (1, $errstr);
+ if ( $_ eq $existingURI );
+ }
+ }
+ $sth =
+ $dbh->prepare(
+"select service_instance.service_instance_id, namespace_type_uris from service_instance natural join simple_output where INSTR(namespace_type_uris,'$existingURI')"
+ );
+ $sth->execute;
+ while ( my ( $id, $ns ) = $sth->fetchrow_array() ) {
+ my @nss = split ",", $ns;
+ foreach (@nss) {
+ $_ =~ s/\s//g;
+ my $errstr = "Namespace Type $term ($_) is used by a service (service ID number $id) and may not be deregistered";
+ return (1, $errstr);
+ if ( $_ eq $existingURI );
+ }
+ }
+ $sth =
+ $dbh->prepare(
+"select service_instance.service_instance_id, namespace_type_uris from service_instance natural join collection_input natural join simple_input where INSTR(namespace_type_uris, '$existingURI')"
+ );
+ $sth->execute;
+ while ( my ( $id, $ns ) = $sth->fetchrow_array() ) {
+ my @nss = split ",", $ns;
+ foreach (@nss) {
+ $_ =~ s/\s//g;
+ my $errstr = "Namespace Type $term ($_) is used by a service (service ID number $id) and may not be deregistered";
+ return (1, $errstr);
+ if ( $_ eq $existingURI );
+ }
+ }
+ $sth =
+ $dbh->prepare(
+"select service_instance.service_instance_id, namespace_type_uris from service_instance natural join collection_output natural join simple_output where INSTR(namespace_type_uris, '$existingURI')"
+ );
+ $sth->execute;
+ while ( my ( $id, $ns ) = $sth->fetchrow_array() ) {
+ my @nss = split ",", $ns;
+ foreach (@nss) {
+ $_ =~ s/\s//g;
+ my $errstr = "Namespace Type $term ($_) is used by a service (service ID number $id) and may not be deregistered";
+ return (1, $errstr);
+ if ( $_ eq $existingURI );
+ }
+ }
+ return (0, $errstr);
+}
+
+# custom query routine for Moby::Central.pm -> findService()
+sub checkKeywords{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $param = get_value('findme', @args);
+ my %findme = %$param;
+ my $searchstring;
+
+ foreach my $kw ( @{ $findme{keywords} } ) {
+ $debug && &_LOG("KEYWORD $kw\n");
+ $kw =~ s/\*//g;
+ $kw = $dbh->quote("%$kw%");
+ $searchstring .= " OR description like $kw ";
+ }
+ $searchstring =~ s/OR//; # just the first one
+ $debug && &_LOG("search $searchstring\n");
+
+ my $ids =
+ $dbh->selectall_arrayref(
+"select service_instance_id from service_instance where $searchstring"
+ );
+
+ return ($ids, $searchstring);
+}
+
+# custom query subroutine for Moby::Central.pm->_searchForSimple()
+sub getFromSimple{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $inout = get_value('inout', @args);
+ my $ancestor_string = get_value('ancestor_string', @args);
+ my $namespaceURIs = get_value('namespaceURIs', @args);
+
+ my $query =
+"select service_instance_id, namespace_type_uris from simple_$inout where object_type_uri in ($ancestor_string) and service_instance_id IS NOT NULL "
+ ; # if service_instance_id is null then it must be a collection input.
+ my $nsquery;
+ foreach my $ns ( @{$namespaceURIs} ) { # namespaces are already URI's
+ $nsquery .= " OR INSTR(namespace_type_uris, '$ns') ";
+ }
+ if ($nsquery) {
+ $nsquery =~ s/OR//; # just the first
+ $nsquery .= " OR namespace_type_uris IS NULL";
+ $query .= " AND ($nsquery) ";
+ }
+
+ my $result = do_query($dbh, $query, ());
+ return ($query, $result);
+}
+
+# custom query subroutine for Moby::Central.pm->_searchForCollection()
+sub getFromCollection
+{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $inout = get_value('inout', @args);
+ my $objectURI = get_value('objectURI', @args);
+ my $namespaceURIs = get_value('namespaceURIs', @args);
+
+ my $query = "select
+ c.service_instance_id,
+ s.namespace_type_uris
+ from
+ simple_$inout as s,
+ collection_$inout as c
+ where
+ s.collection_${inout}_id IS NOT NULL
+ AND s.collection_${inout}_id = c.collection_${inout}_id
+ AND object_type_uri = '$objectURI' "
+ my $nsquery;
+ foreach my $ns ( @{$namespaceURIs} ) { # namespaces are already URI's
+ $nsquery .= " OR INSTR(namespace_type_uris, '$ns') ";
+ }
+ if ($nsquery) {
+ $nsquery =~ s/^\sOR//; # just the first
+ $nsquery .= " OR namespace_type_uris IS NULL";
+ $query .= " AND ($nsquery) "; # add the AND clause
+ }
+
+ my $result = do_query($dbh, $query, ());
+ return ($query, $result);
+}
+
+# custom query subroutine for Moby::Central.pm->RetrieveServiceNames
+sub getServiceNames
+{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+ my $statement = "select authority_uri, servicename from authority as a, service_instance as s where s.authority_id = a.authority_id";
+ my @bindvalues = ();
+
+ my $result = do_query($dbh, $statement, @bindvalues);
+ return $result;
+}
+
+# custom query for Moby::Central.pm->_flatten
+sub getParentTerms
+{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+
+ my $type_id = get_value('relationship_type_id', @args);
+ my $statement = "
+ select
+ OE1.term
+ from
+ OntologyEntry as OE1,
+ OntologyEntry as OE2,
+ Term2Term as TT
+ where
+ ontologyentry2_id = OE2.id
+ and ontologyentry1_id = OE1.id
+ and relationship_type_id = $type_id
+ and OE2.term = ?";
+
+ my @bindvalues = ();
+ push(@bindvalues, get_value('term', @args));
+
+ my $result = do_query($dbh, $statement, @bindvalues);
+ return $result;
+}
+
+# custom query subroutine for selecting from object_term2term and object tables
+# used in Moby::OntologyServer.pm->retrieveObject()
+sub getObjectRelationships{
+ my ($self, @args) = @_;
+ my $dbh = $self->dbh;
+
+ my $statement = "select
+ relationship_type,
+ object_lsid,
+ object2_articlename
+ from object_term2term, object
+ where object1_id = ? and object2_id = object_id";
+
+ my @bindvalues = ();
+ push(@bindvalues, get_value('object1_id', @args));
+
+ my $result = do_query($dbh, $statement, @bindvalues);
+ return $result;
+}
+
+# relationship query for any table used in Moby::OntologyServer->_doRelationshipQuery()
+getRelationship{
+ my ($self, %args) = @_;
+ my $dbh = $self->dbh;
+ my $defs;
+ my $ontology = $args{ontology};
+
+ unless ( defined $args{$relationship} ) {
+ $defs = $self->dbh->selectall_arrayref( "
+ select distinct s2.${ontology}_lsid, relationship_type from
+ ${ontology}_term2term as t2t,
+ $ontology as s1,
+ $ontology as s2
+ where
+ s1.${ontology}_id = t2t.${ontology}1_id and
+ s2.${ontology}_id = t2t.${ontology}2_id and
+ s1.${ontology}_lsid = ?", undef, $args{lsid} ); # ")
+ } else {
+ $defs = $self->dbh->selectall_arrayref( "
+ select distinct s2.${ontology}_lsid, relationship_type from
+ ${ontology}_term2term as t2t,
+ $ontology as s1,
+ $ontology as s2
+ where
+ relationship_type = ? and
+ s1.${ontology}_id = t2t.${ontology}1_id and
+ s2.${ontology}_id = t2t.${ontology}2_id and
+ s1.${ontology}_lsid = ?", undef, $args{$relationship}, $args{lsid} ); # ")
+ }
+ return $defs;
+}
sub _checkURI {
More information about the MOBY-guts
mailing list