[Gmod-tripal-devel] Tripal API: Insert/Update/Delete functions

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

[Gmod-tripal-devel] Tripal API: Insert/Update/Delete functions

Lacey-Anne Sanderson
So me and Stephen have started a tripal api for a number of the tripal modules. All api functions are being stored in a <tripal_moduel>/<tripal_module>.api.inc file. There are already some select statement functions developed but I thought I'd put the question of insert/update/delete function design to everyone before starting on them.

Example of an insert api function. 
function tripal_feature_insert_feature ($values) {
  $available_fields = listing table field names as keys and placeholder as value ('%s', %d);

 //check current user has permission
 if don't have permission return 0;

  $fields = intersection of array_keys($values) with array_keys($available fields)
  //thus $fields will only contain pre-defined available fields

  foreach $fields
     $sql_placeholders[$field] = $available_fields[$field];


  // INSERT INTO feature (organism_id, name, uniquename, type_id) VALUES (%d, '%s', '%s', %d)
  $sql = "INSERT INTO feature (" . implode(',', $fields) . ") VALUES (" . implode(',',$sql_placeholders) .")"
  $r = db_query($sql, values supplied by $values)

  if ($r) {
    return 1;
  } else {
    //db_query will return FALSE if the query is unsuccessful
    return 0;
  }
}
Note: The above function uses an array of available fields which is hard-coded to ensure the user doesn't try to add fields that don't exist in the chado table. Furthermore, it uses placeholders in the query to protect against SQL injection attacks.

Thus when a person wanted to insert a new feature they would:
$success = tripal_feature_insert_feature(
    array(
'organism_id' => 4,
'name' => 'sarah',
'uniquename' => 'KP:GERM54',
'type_id' => 32
    )
);


Example of an update api function
function tripal_feature_update_feature ($where_options, $values) {
  $available_fields = listing table field names as keys and placeholder as value ('%s', %d);

 //check current user has permission
 if don't have permission return 0;

 //get feature to update--------------------------------------------
 $fields = intersection of array_keys($where_fields) with array_keys($available fields)

 foreach $fields
     $sql_placeholders_where[] = $field .'='. $available_fields[$field];

  // ie: SELECT feature_id FROM feature WHERE name='%s' AND organism_id=%d
  $sql = "SELECT feature_id FROM feature WHERE " . implode(' AND ', $sql_placeholders_where);
  $resource = db_query($sql, values supplied by $values)

  for each object returned by db_fetch_object($resource) {
     $fields = intersection of array_keys($values) with array_keys($available fields)
     foreach $fields
         $sql_placeholders[] = $field .'='. $available_fields[$field];
     $sql = "UPDATE feature SET " . implode(', ',$sql_placeholders) . "WHERE feature_id=%d";
     $success = db_query($sql, values supplied by $values, $object->feature_id);

     if (!$success) {
        drupal message error with feature_id unable to be updated
$status[$object->feature_id] = 'failed';
     } else {
        $status[$object->feature_id] = 'successfully updated';
     }
     }

     return $status;
}

Note: The current setup allows updating of all records that match the where criteria... Do we want to restrict it to one?

Thus when a person wanted to update the name of the feature which uniquename='KP:Germ54' the would
$result = tripal_feature_update_feature(
    array(
        'uniquename' => 'KP:GERM54'
    ),
    array(
        'name' => 'new name'
   )
);
If there are multiple features with the same uniquename then they would all get the new name.

Example of a delete api function
tripal_feature_delete_feature($where_options) {
  $available_fields = listing table field names as keys and placeholder as value ('%s', %d);
 
 //check current user has permission
 if don't have permission return 0;

 //get feature to update--------------------------------------------
 $fields = intersection of array_keys($where_fields) with array_keys($available fields)

 foreach $fields
     $sql_placeholders_where[] = $field .'='. $available_fields[$field];

  // ie: DELETE FROM feature WHERE name='%s' AND organism_id=%d
  $sql = "DELETE FROM feature WHERE " . implode(' AND ', $sql_placeholders_where);
  $r = db_query($sql, values supplied by $values)

  if ($r) {
    return 1;
  } else {
    //db_query will return FALSE if the query is unsuccessful
    return 0;
  }
}

Thus to delete all feature where organism_id=4 and type_id=5554
$success = tripal_feature_delete_feature(
    array(
         'organism_id' => 4,
         'type_id' => 5554
    );
);

Any Questions/Comments/Suggestions?


Thanks for your time in reviewing this pseudo-code and any comments/suggestions you can come up with,
~Lacey

------------------------------------------------------
Lacey-Anne Sanderson
Bioinformaticist
Pulse Crop Breeding and Genetics
Phone: (306) 966-2430
Room 3D10 Agriculture
Department of Plant Sciences
University of Saskatchewan


------------------------------------------------------------------------------
The ultimate all-in-one performance toolkit: Intel(R) Parallel Studio XE:
Pinpoint memory and threading errors before they happen.
Find and fix more than 250 security defects in the development cycle.
Locate bottlenecks in serial and parallel code that limit performance.
http://p.sf.net/sfu/intel-dev2devfeb
_______________________________________________
Gmod-tripal-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal-devel
Reply | Threaded
Open this post in threaded view
|

Re: [Gmod-tripal-devel] Tripal API: Insert/Update/Delete functions

Stephen Ficklin-2

HI Lacey,

 

Extending this idea…. It would be nice to be able to specify the values for a unique constraint to match foreign key relationships.  For example, calling the insert function would look similar to this:

 

$success = tripal_feature_insert_feature(

    array(

     'organism_id' => array(

         ‘genus’ = ‘Gorilla’,

         ‘species’ = ‘gorilla’,

      ),

     'name' => 'sarah',

     'uniquename' => 'KP:GERM54',

     'type_id' => array (

         ‘cv_id’ = array (

            ‘name’ = ‘sequence’,

         ),

         ‘name’ = ‘mRNA’,

         ‘is_obsolete’ = 0

      ),

    )

);

 

So instead of providing a value for the cvterm_id or organism_id or cv_id we provide an array for the values that uniquely match cvterm (or whatever).  This way we can use the unique information to call the corresponding lookup function and substitute in the appropriate value.  If the callee provides just a cvterm_id and not an array, then we would just use that value.

 

Stephen

 

 

From: Lacey-Anne Sanderson [mailto:[hidden email]]
Sent: Tuesday, February 15, 2011 5:17 PM
To: Stephen Ficklin; [hidden email]
Subject: Tripal API: Insert/Update/Delete functions

 

So me and Stephen have started a tripal api for a number of the tripal modules. All api functions are being stored in a <tripal_moduel>/<tripal_module>.api.inc file. There are already some select statement functions developed but I thought I'd put the question of insert/update/delete function design to everyone before starting on them.

 

Example of an insert api function. 

function tripal_feature_insert_feature ($values) {

  $available_fields = listing table field names as keys and placeholder as value ('%s', %d);

 

 //check current user has permission

 if don't have permission return 0;

 

  $fields = intersection of array_keys($values) with array_keys($available fields)

  //thus $fields will only contain pre-defined available fields

 

  foreach $fields

     $sql_placeholders[$field] = $available_fields[$field];

 

 

  // INSERT INTO feature (organism_id, name, uniquename, type_id) VALUES (%d, '%s', '%s', %d)

  $sql = "INSERT INTO feature (" . implode(',', $fields) . ") VALUES (" . implode(',',$sql_placeholders) .")"

  $r = db_query($sql, values supplied by $values)

 

  if ($r) {

    return 1;

  } else {

    //db_query will return FALSE if the query is unsuccessful

    return 0;

  }

}

Note: The above function uses an array of available fields which is hard-coded to ensure the user doesn't try to add fields that don't exist in the chado table. Furthermore, it uses placeholders in the query to protect against SQL injection attacks.

 

Thus when a person wanted to insert a new feature they would:

$success = tripal_feature_insert_feature(

    array(

     'organism_id' => 4,

     'name' => 'sarah',

     'uniquename' => 'KP:GERM54',

     'type_id' => 32

    )

);

 

 

Example of an update api function

function tripal_feature_update_feature ($where_options, $values) {

  $available_fields = listing table field names as keys and placeholder as value ('%s', %d);

 

 //check current user has permission

 if don't have permission return 0;

 

 //get feature to update--------------------------------------------

 $fields = intersection of array_keys($where_fields) with array_keys($available fields)

 

 foreach $fields

     $sql_placeholders_where[] = $field .'='. $available_fields[$field];

 

  // ie: SELECT feature_id FROM feature WHERE name='%s' AND organism_id=%d

  $sql = "SELECT feature_id FROM feature WHERE " . implode(' AND ', $sql_placeholders_where);

  $resource = db_query($sql, values supplied by $values)

 

  for each object returned by db_fetch_object($resource) {

     $fields = intersection of array_keys($values) with array_keys($available fields)

     foreach $fields

         $sql_placeholders[] = $field .'='. $available_fields[$field];

     $sql = "UPDATE feature SET " . implode(', ',$sql_placeholders) . "WHERE feature_id=%d";

     $success = db_query($sql, values supplied by $values, $object->feature_id);

 

     if (!$success) {

        drupal message error with feature_id unable to be updated

     $status[$object->feature_id] = 'failed';

     } else {

        $status[$object->feature_id] = 'successfully updated';

     }

     }

 

     return $status;

}

 

Note: The current setup allows updating of all records that match the where criteria... Do we want to restrict it to one?

 

Thus when a person wanted to update the name of the feature which uniquename='KP:Germ54' the would

$result = tripal_feature_update_feature(

    array(

        'uniquename' => 'KP:GERM54'

    ),

    array(

        'name' => 'new name'

   )

);

If there are multiple features with the same uniquename then they would all get the new name.

 

Example of a delete api function

tripal_feature_delete_feature($where_options) {

  $available_fields = listing table field names as keys and placeholder as value ('%s', %d);

 

 //check current user has permission

 if don't have permission return 0;

 

 //get feature to update--------------------------------------------

 $fields = intersection of array_keys($where_fields) with array_keys($available fields)

 

 foreach $fields

     $sql_placeholders_where[] = $field .'='. $available_fields[$field];

 

  // ie: DELETE FROM feature WHERE name='%s' AND organism_id=%d

  $sql = "DELETE FROM feature WHERE " . implode(' AND ', $sql_placeholders_where);

  $r = db_query($sql, values supplied by $values)

 

  if ($r) {

    return 1;

  } else {

    //db_query will return FALSE if the query is unsuccessful

    return 0;

  }

}

 

Thus to delete all feature where organism_id=4 and type_id=5554

$success = tripal_feature_delete_feature(

    array(

         'organism_id' => 4,

         'type_id' => 5554

    );

);

 

Any Questions/Comments/Suggestions?

 

 

Thanks for your time in reviewing this pseudo-code and any comments/suggestions you can come up with,

~Lacey

 

------------------------------------------------------

Lacey-Anne Sanderson

Bioinformaticist

Pulse Crop Breeding and Genetics

Phone: (306) 966-2430

Room 3D10 Agriculture

Department of Plant Sciences

University of Saskatchewan

 


------------------------------------------------------------------------------
The ultimate all-in-one performance toolkit: Intel(R) Parallel Studio XE:
Pinpoint memory and threading errors before they happen.
Find and fix more than 250 security defects in the development cycle.
Locate bottlenecks in serial and parallel code that limit performance.
http://p.sf.net/sfu/intel-dev2devfeb
_______________________________________________
Gmod-tripal-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal-devel
Reply | Threaded
Open this post in threaded view
|

Re: [Gmod-tripal-devel] Tripal API: Insert/Update/Delete functions

Lacey-Anne Sanderson
<base href="x-msg://97/">I also really like this idea. 

We could even take it one step further and create the object referred to by the foreign key restraint. In the example below, if the organism=gorilla doesn't exist we could create it. If we are going to add this functionality though, I think we should add another argument which allows the user to set whether they want missing foreign key objects to be created. That way, if you know that gorilla should exist you can stop it from creating another organism if, say, you spelt it wrong. This create ability would be especially nice when creating features/stocks -you could create the dbxref at the same time without having to call tripal_db_insert_dbxref yourself. 

~Lacey

------------------------------------------------------
Lacey-Anne Sanderson
Bioinformaticist
Pulse Crop Breeding and Genetics
Phone: (306) 966-2430
Room 3D10 Agriculture
Department of Plant Sciences
University of Saskatchewan

On 2011-02-15, at 6:02 PM, Stephen Ficklin wrote:

HI Lacey,
 
Extending this idea…. It would be nice to be able to specify the values for a unique constraint to match foreign key relationships.  For example, calling the insert function would look similar to this:
 
$success = tripal_feature_insert_feature(
    array(
     'organism_id' => array(
         ‘genus’ = ‘Gorilla’,
         ‘species’ = ‘gorilla’,
      ),
     'name' => 'sarah',
     'uniquename' => 'KP:GERM54',
     'type_id' => array (
         ‘cv_id’ = array (
            ‘name’ = ‘sequence’,
         ),
         ‘name’ = ‘mRNA’,
         ‘is_obsolete’ = 0
      ),
    )
);
 
So instead of providing a value for the cvterm_id or organism_id or cv_id we provide an array for the values that uniquely match cvterm (or whatever).  This way we can use the unique information to call the corresponding lookup function and substitute in the appropriate value.  If the callee provides just a cvterm_id and not an array, then we would just use that value.
 
Stephen
 
 
From: Lacey-Anne Sanderson [mailto:[hidden email]] 
Sent: Tuesday, February 15, 2011 5:17 PM
To: Stephen Ficklin; [hidden email]
Subject: Tripal API: Insert/Update/Delete functions
 
So me and Stephen have started a tripal api for a number of the tripal modules. All api functions are being stored in a <tripal_moduel>/<tripal_module>.api.inc file. There are already some select statement functions developed but I thought I'd put the question of insert/update/delete function design to everyone before starting on them.
 
Example of an insert api function. 
function tripal_feature_insert_feature ($values) {
  $available_fields = listing table field names as keys and placeholder as value ('%s', %d);
 
 //check current user has permission
 if don't have permission return 0;
 
  $fields = intersection of array_keys($values) with array_keys($available fields)
  //thus $fields will only contain pre-defined available fields
 
  foreach $fields
     $sql_placeholders[$field] = $available_fields[$field];
 
 
  // INSERT INTO feature (organism_id, name, uniquename, type_id) VALUES (%d, '%s', '%s', %d)
  $sql = "INSERT INTO feature (" . implode(',', $fields) . ") VALUES (" . implode(',',$sql_placeholders) .")"
  $r = db_query($sql, values supplied by $values)
 
  if ($r) {
    return 1;
  } else {
    //db_query will return FALSE if the query is unsuccessful
    return 0;
  }
}
Note: The above function uses an array of available fields which is hard-coded to ensure the user doesn't try to add fields that don't exist in the chado table. Furthermore, it uses placeholders in the query to protect against SQL injection attacks.
 
Thus when a person wanted to insert a new feature they would:
$success = tripal_feature_insert_feature(
    array(
     'organism_id' => 4,
     'name' => 'sarah',
     'uniquename' => 'KP:GERM54',
     'type_id' => 32
    )
);
 
 
Example of an update api function
function tripal_feature_update_feature ($where_options, $values) {
  $available_fields = listing table field names as keys and placeholder as value ('%s', %d);
 
 //check current user has permission
 if don't have permission return 0;
 
 //get feature to update--------------------------------------------
 $fields = intersection of array_keys($where_fields) with array_keys($available fields)
 
 foreach $fields
     $sql_placeholders_where[] = $field .'='. $available_fields[$field];
 
  // ie: SELECT feature_id FROM feature WHERE name='%s' AND organism_id=%d
  $sql = "SELECT feature_id FROM feature WHERE " . implode(' AND ', $sql_placeholders_where);
  $resource = db_query($sql, values supplied by $values)
 
  for each object returned by db_fetch_object($resource) {
     $fields = intersection of array_keys($values) with array_keys($available fields)
     foreach $fields
         $sql_placeholders[] = $field .'='. $available_fields[$field];
     $sql = "UPDATE feature SET " . implode(', ',$sql_placeholders) . "WHERE feature_id=%d";
     $success = db_query($sql, values supplied by $values, $object->feature_id);
 
     if (!$success) {
        drupal message error with feature_id unable to be updated
     $status[$object->feature_id] = 'failed';
     } else {
        $status[$object->feature_id] = 'successfully updated';
     }
     }
 
     return $status;
}
 
Note: The current setup allows updating of all records that match the where criteria... Do we want to restrict it to one?
 
Thus when a person wanted to update the name of the feature which uniquename='KP:Germ54' the would
$result = tripal_feature_update_feature(
    array(
        'uniquename' => 'KP:GERM54'
    ),
    array(
        'name' => 'new name'
   )
);
If there are multiple features with the same uniquename then they would all get the new name.
 
Example of a delete api function
tripal_feature_delete_feature($where_options) {
  $available_fields = listing table field names as keys and placeholder as value ('%s', %d);
 
 //check current user has permission
 if don't have permission return 0;
 
 //get feature to update--------------------------------------------
 $fields = intersection of array_keys($where_fields) with array_keys($available fields)
 
 foreach $fields
     $sql_placeholders_where[] = $field .'='. $available_fields[$field];
 
  // ie: DELETE FROM feature WHERE name='%s' AND organism_id=%d
  $sql = "DELETE FROM feature WHERE " . implode(' AND ', $sql_placeholders_where);
  $r = db_query($sql, values supplied by $values)
 
  if ($r) {
    return 1;
  } else {
    //db_query will return FALSE if the query is unsuccessful
    return 0;
  }
}
 
Thus to delete all feature where organism_id=4 and type_id=5554
$success = tripal_feature_delete_feature(
    array(
         'organism_id' => 4,
         'type_id' => 5554
    );
);
 
Any Questions/Comments/Suggestions?
 
 
Thanks for your time in reviewing this pseudo-code and any comments/suggestions you can come up with,
~Lacey
 
------------------------------------------------------
Lacey-Anne Sanderson
Bioinformaticist
Pulse Crop Breeding and Genetics
Phone: (306) 966-2430
Room 3D10 Agriculture
Department of Plant Sciences
University of Saskatchewan
 


------------------------------------------------------------------------------
The ultimate all-in-one performance toolkit: Intel(R) Parallel Studio XE:
Pinpoint memory and threading errors before they happen.
Find and fix more than 250 security defects in the development cycle.
Locate bottlenecks in serial and parallel code that limit performance.
http://p.sf.net/sfu/intel-dev2devfeb
_______________________________________________
Gmod-tripal-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal-devel