Universal Module: Find Services.service_Id From Validateservice?

[will]

I'm trying to add validation to Universal Module to ensure that no service with the same service field "hostname" is already provisioned. (That is, the hostname should be unique.) Universal Module doesn't yet offer a way to ensure a field is unique.

 

These services are manually activated. To validate, I'm writing a custom validation function that interrogates the tables "service_fields" and "services." Conditions I need to ensure:

1) There is not a service_fields row with our hostname that corresponds with a services row with a status of "active" or "suspended"

2) There is not a service_fields row with our hostname that corresponds with a services row with a status of "pending" or "active" or "suspended" and a non-matching client_id.

 

Ideally, I'd also like to ensure that

3) There is not a service_fields row with our hostname that corresponds with a services row with a status of "pending" and with a non-matching service_id.

 

What do I need to change to have the services.id integer available in the function validateService? It's ok if it is NULL because no row yet exists for the service being validated, such as at order time instead of activation time.

 

Alternatively, is it possible to call different validation functions at order time and at activation time?

[Tyson]

You may want to consider updating UniversalModule::editService to pass in the $service to UniversalModule::processService where you can perform additional checks using the service information.

[will]

It appears that UniversalModule::addService is the one invoking processService in my case, and it looks like $service is not passed to UniversalModule::addService from Services::edit (/var/www/app/models/services.php line 1028) though it *is* passed to UniversalModule::editService. 

 

It looks like adding that to the argument list will probably fix my problem. Is there a reason that $service is passed to editService but not addService? Could that be changed upstream? In my case, I modified app/models/services.php to pass it in for me, but I expect that may conflict with future updates.

 

If anyone wants to duplicate my validation for uniqueness function, here it is. It expects to be passed the hostname label and $service.

        public function validateAbsent($hostname, $service){
                Loader::loadComponents($this, array("Record"));
                // Query DB to see if this hostname exists already 
                if (is_null($service)){
$rows = $this->Record->query("select 1 from service_fields inner join services on service_fields.service_id=services.id where 
service_fields.key = 'hostname' 
and service_fields.value = '$hostname'
and services.status in ('active','suspended','pending')")->rowCount();  
} else {
                $sid = $service->id;
 $rows = $this->Record->query("select 1 from service_fields inner join services on service_fields.service_id=services.id where 
service_fields.key = 'hostname' 
and service_fields.value = '$hostname'
and services.status in ('active','suspended','pending')
and services.id != $sid")->rowCount(); }
                return($rows < 1);
        }

 

EDIT: Fixed to accommodate $service being null, which it will be at some point during the ordering process.

Edited November 8, 2014 by will

[Tyson]

The module's addService method, as you probably discovered, is called before a service is created in Blesta (Services::add), or when activating an existing pending service (Services::edit), so it may not always be known. This extends and overrides the Module abstract class used by all modules, and was not designed at that time with a need to support passing the service on add. If the main reason you have needed to at this time was to perform a unique validation check on some service fields, then it may be more of a special case. But this is something we would have to discuss internally.

 

On a side note, I see your query passes variables directly to Record::query. You should revise this, as it is currently vulnerable to SQL injection.

[will]

Um, as far as I could tell during my testing, adding an existing pending service (e.g. "Activate service") used Services:add, not Services::edit.

 

Thanks for the tip about injection; I was just about to test that.

 

Is this the correct translation using Record's methods?

public function validateAbsent($hostname, $service){
 Loader::loadComponents($this, array("Record"));
 // Query DB to see if this hostname exists already 
 $this->Record->select('1')->from('service_fields')->innerJoin('services', 'service_fields.service_id', '=', 'services.id')->where('service_fields.key','=','hostname')->where('service_fields.value','=',$hostname)->where('services.status','in',"('active','suspended','pending')");
 if ( !is_null($service)) {
  $this->Record->where('services.id','!=',$service->id);
 }
return($this->Record->numResults() < 1); }

(or, for readability's sake)

public function validateAbsent($hostname, $service){
 Loader::loadComponents($this, array("Record"));
 // Query DB to see if this hostname exists already 
 $this->Record->select('1')
->from('service_fields')
->innerJoin('services', 'service_fields.service_id', '=', 'services.id')
->where('service_fields.key','=','hostname')
->where('service_fields.value','=',$hostname)
->where('services.status','in',"('active','suspended','pending')");
if ( !is_null($service)) {
 $this->Record->where('services.id','!=',$service->id);
}
return($this->Record->numResults() < 1); }

Edit: I couldn't get the "in" operator working. I also had to unbind my inner join statement. This seems to be working:

public function validateAbsent($hostname, $service){
  Loader::loadComponents($this, array("Record"));
  // Query DB to see if this hostname exists already 
 $this->Record->select('*')->from('service_fields')->innerJoin('services', 'service_fields.service_id', '=', 'services.id', False)->where('service_fields.key','=','hostname')->where('service_fields.value','=',$hostname)->open()->where('services.status','=','active')->orWhere('services.status','=','suspended')->orWhere('services.status','=','pending')->orWhere('services.status','=','in_review')->close();
 if ( !is_null($service)) {
  $sid = $service->id;
  $this->Record->where('services.id','!=',$sid);
 }
 return($this->Record->numResults() < 1);}

Edited November 8, 2014 by will

[Tyson]

Proper use of WHERE IN using the Record component:

$this->Record->select("service_fields.*")->from("service_fields")->
    innerJoin("services", "service_fields.service_id", "=", "services.id", false)->
    where("service_fields.key", "=", "hostname")->
    where("service_fields.value", "=", $hostname)->
    where("service_fields.status", "in", array("active", "suspended", "in_review"));