This tutorial will go through all steps required to make a working customer database with a REST webservice interface.

A working server environment, LAMP or WAMP, which is a server with a working Apache/PHP/MySQL setup.

Install the latest Vortex framework and create a DBI. Instructions on how to do this are found here. For this example we will call the DBI mani, you can however call your whatever you like.

After creating the dbi surfing to the folder on the webserver using a browser should look something like this:

If you created the DBI giving valid information about the database and using the standard Vortex config you should now be able to log in to the Vortex using the admin account and the standard password changeMe. Logging in the first time creates the empty Vortex database structure in your mysQL database.

Now it's time to create the tables we need. For this example we will create the following five tables, with at least the given fields:

1. customer
   • name, textfield
   • pid, textfield
   • noagreements, integer
   • nocontacts, integer
2. contactperson
   • name, textfield
   • position, list (a couple of options by your choosing)
   • customer, relation
3. contactinformation
   • type, list (a couple of options by your choosing, ex: phone, email, address)
   • value, textfield
   • contactperson, relation
4. product
   • name, textfield
   • price, integer
5. agreement
   • number, textfield
   • product, relation
   • customer, relation

For information on how to create the tables and fields, check out the table creating guide. Give the tables and fields suitable descriptions. None of the tables will user WorkFlow or history for now.

An instruction on how to define relations are found in the Relations example

Define the following relations for this DBI:

• 1-x relation from contactperson.customer to customer
• 1-x relation from contactinformation.contactperson to contactperson
• 1-x relation from agreement.customer to customer
• 1-x relation from agreement.product to product

Now create a few customers, having contact persons with contact informations. Then create a few products and connect the products to the customers using the agreement table.

For information on how to enter data see the guide on entering data.

The fields we created in the customer table called nocontacts and noagreements are supposed to show the total number of agreements and contact persons for the customer. To achieve this we will create two script methods for the customer table calculating theese sums. For in-depth information on table scripting see Table Scripting.

To create the script class, create a new file called customer.php in the scripts/ path in the DBI and open it in your favourite editor. As described in the Table Scripting section, the class for theese methods should be called Methodcustomer, so create this class.

  <?php
    class Methodcustomer {
 
    }
  ?>

We'll call the first method getNoAgreements, which will count all the agreements connected to this customer and return the resulting value. According to the documentation, this would look something like:

  public static function getNoAgreements( &$customer ) {
    $agreements = $customer->getRelated( "agreements" );
    return count( $agreements );
  }

In the same way we create our getNoContacts method, but only for the sake of it we'll do that in a different way. The resulting script file will look like this:

customer.php

<?php
  class Methodcustomer {
    public static function getNoAgreements( &$customer ) {
      $agreements = $customer->getRelated( "agreements" );
      return count( $agreements );
    }
 
    public static function getNoContacts( &$customer ) {
      $fd = new FilterData( "contactperson" );
      $fd->add( 'customer', FilterData::EQUALS, $customer->get( '_uuid' ) );
      $rows = $fd->getFilter()->getTableRecords();
      return count($rows);
    }
  }
?>

Now all we need to do is make sure the nocontacts and noagreements fields fetch their values from theese methods, according to the VXString documentation this can be done by adding the name of the method to the value element of the Extra info field in the definition of theese fields.

This will make sure theese two fields always has the calculated value from the connected method.

Now the table structure is set up and we have the calculated fields in order, the next step is creating a REST webservice interface allowing us to get and set data to the database with some sort of crude authentication.

According to Vortex Integration via REST Webservices we need to create a class called RESTService, supply a RESTUser and a RESTPassword and implement the constructor for the service before implementing REST-methods. So let's start by creating a new file in the scripts/ directory in the DBI called rest.php and make write the class there.

rest.php

<?php
  class RESTService {
    const RESTUser="restuser";
    const RESTPassword="restpass";
    private $reqMethod;
 
    public function __construct( $reqMethod ) {
      $this->reqMethod = $reqMethod;
    }
}
?>

We will of course have to create the user restuser with password restpass, make it an External user to make sure noone can log in to the vortex with that user, and give it permission to all the tables. We'll do this by adding it to the users group, and set the permissions so that the users group can read, write and create in all tables.

In this example we'll just create a method to handle the customer and agreement tables but you can add methods in a similar way for the other tables.

We'll start by adding a method called customer and let it take an argument called customer which is a json encoded string containing customer information, and another argument called auth which is a json encoded string containing autentication information. We'll also add a very simple authentication method.

  private function authenticate( $auth ) {
    if( $auth->username=="user" && $auth->password=="pass" ) return true;
    return false;
  }
 
  public function customer( $args ) {
    if( !isset($args['auth']) || !isset( $args['customer'] ) ) return "Invalid call";
 
    if( !$this->authenticate( json_decode( $args['auth'] ) ) ) return "Authentication error";
    switch( $this->reqMethod ) {
      case 'POST':
        return $this->setCustomer( json_decode( $args['customer'] ) );
      case 'GET':
        return $this->getCustomer( json_decode( $args['customer'] ) );
    }
    return "Invalid call";
  }

Since only public functions are exported over the REST Interface we can use private helper functions within the class without them posing a security threat. We'll add the methods for getting and setting customer information:

  private function getCustomer( $c ) {
    if( isset( $c->uuid ) ) {
      $iCustomer = TableRecordFactory::getTableRecord( $c->uuid );
      return $iCustomer->toJSON();
    }
    if( !isset( $c->name ) ) return "No customer found";
    $fd = new FilterData("customer");
    $fd->add( "name", FilterData::EQUALS, $c->name );
    $customers = $fd->getFilter()->getTableRecords();
    if( count( $customers ) > 0 ) return $customers[0]->toJSON();
    return "No customer found";
  }
 
  private function setCustomer( $c ) {
    $iCustomer = (isset( $c->uuid) ? TableRecordFactory::getTableRecord( $c->uuid ) : TableRecordFactory::getTableRecord( "customer", 0 ) );
    if( isset( $c->name ) ) $iCustomer->set( 'name', $c->name );
    if( isset( $c->pid ) ) $iCustomer->set( 'pid', $c->pid );
    $iCustomer->save();
    return $iCustomer->toJSON();
  }

What we now need to do is to add the toJSON method to the Methodcustomer class we created earlier:

  public static function toJSON( &$customer ) {
    return json_encode( array( "customer"=>array(
      "pid"=>utf8_encode( $customer->get( 'pid' ) ),
      "name"=>utf8_encode( $customer->get( 'name' ) ),
      "noagreements"=>$customer->get( 'noagreements' ),
      "nocontacts"=>$customer->get( 'nocontacts' )
    )));
  }

Surfing to the URL of the REST service for this DBI should now return information from the database:

In the same way posting to the URL will create or change a customer from the supplied information. Adding the _uuid field to the output would be wise to let REST users be able to change the information of created customers.

Since the customer table does not contain any relation fields we'll post an example of how to write the REST handler for the agreement table. Below is the complete source code for the RESTService class with the handler for the agreement class as well.

rest.php

<?php
  class RESTService {
    const RESTUser="restuser";
    const RESTPassword="restpass";
    private $reqMethod;
 
    public function __construct( $reqMethod ) {
      $this->reqMethod = $reqMethod;
    }
 
    private function authenticate( $auth ) {
      if( $auth->username=="user" && $auth->password=="pass" ) return true;
      return false;
    }
 
    public function agreement( $args ) {
      if( !isset($args['auth']) || !isset( $args['agreement'] ) ) return "Invalid call";
      if( !$this->authenticate( json_decode( $args['auth'] ) ) ) return "Authentication error";
      switch( $this->reqMethod ) {
        case "POST":
          return $this->setAgreement( json_decode( $args['agreement'] ) );
        case "GET":
          return $this->getAgreement( json_decode( $args['agreement'] ) );
      }
      return "Invalid call";
    }
 
    private function getAgreement( $a ) {
      if( !isset( $a->customer ) || !isset( $a->number ) ) return "No agreement found";
      $fd = new FilterData( "agreement" );
      $fd->add( "customer.name", FilterData::EQUALS, $a->customer );
      $fd->add( "number", FilterData::EQUALS, $a->number );
      $agreements = $fd->getFilter()->getTableRecords();
      if( count( $agreements ) < 1) return "No agreement found";
      return $agreements[0]->toJSON();
    }
 
    private function setAgreement( $a ) {
      if( !isset( $a->customer ) || !isset( $a->number ) || !isset( $a->product ) ) return "Invalid call";
      $cfd = new FilterData( "customer" );
      $cfd->add( "name", FilterData::EQUALS, $a->customer );
      $customers = $cfd->getFilter()->getTableRecords();
      if( count( $customers ) == 0 ) return "No customer found";
      $iCustomer = $customers[0];
 
      $pfd = new FilterData( "product" );
      $pfd->add( 'name', FilterData::EQUALS, $a->product );
      $products = $pfd->getFilter()->getTableRecords();
      if( count( $products ) == 0 ) return "No product found";
      $iProduct = $products[0];
 
      $fd = new FilterData( "agreement" );
      $fd->add( "customer.name", FilterData::EQUALS, $iCustomer->get( 'name' ) );
      $fd->add( "number", FilterData::EQUALS, $a->get( 'number' ) );
      $agreements = $fd->getFilter()->getTableRecords();
      if( count( $agreements ) == 0 ) {
        $iAgreement = TableRecordFactory::getTableRecord( "agreement", 0 );
      } else {
        $iAgreement = $agreements[0];
      }
 
      $iAgreement->set( "customer", $iCustomer->get( '_uuid' ) );
      $iAgreement->set( "product", $iProduct->get( '_uuid' ) );
      $iAgreement->set( "number", $a->number );
      $iAgreement->save();
      return $iAgreement->toJSON();
    }
 
    public function customer( $args ) {
      if( !isset($args['auth']) || !isset( $args['customer'] ) ) return "Invalid call";
      if( !$this->authenticate( json_decode( $args['auth'] ) ) ) return "Authentication error";
      switch( $this->reqMethod ) {
        case 'POST':
          return $this->setCustomer( json_decode( $args['customer'] ) );
        case 'GET':
          return $this->getCustomer( json_decode( $args['customer'] ) );
      }
      return "Invalid call";
    }
 
    private function getCustomer( $c ) {
      if( isset( $c->uuid ) ) {
        $iCustomer = TableRecordFactory::getTableRecord( $c->uuid );
        return $iCustomer->toJSON();
      }
      if( !isset( $c->name ) ) return "No customer found";
      $fd = new FilterData("customer");
      $fd->add( "name", FilterData::EQUALS, $c->name );
      $customers = $fd->getFilter()->getTableRecords();
      if( count( $customers ) > 0 ) return $customers[0]->toJSON();
      return "No customer found";
    }
 
    private function setCustomer( $c ) {
      $iCustomer = (isset( $c->uuid) ? TableRecordFactory::getTableRecord( $c->uuid ) : TableRecordFactory::getTableRecord( "customer", 0 ) );
      if( isset( $c->name ) ) $iCustomer->set( 'name', $c->name );
      if( isset( $c->pid ) ) $iCustomer->set( 'pid', $c->pid );
      $iCustomer->save();
      return $iCustomer->toJSON();
    }
}
?>

For this to work we need to add a toJSON method to the Methodagreement class in the same way as for the customer table.

agreement.php

<?php
  class Methodagreement {
    public static function toJSON( &$agreement ) {
      return json_encode( array( "customer"=>array(
        "number"=>utf8_encode( $agreement->get( 'number' ) ),
        "product"=>array( 
          "name"=>utf8_encode( $agreement->get( 'product.name') ),
          "price"=>utf8_encode( $agreement->get( 'product.price' ) )
        ),
        "customer"=>array( 
          "name"=>utf8_encode( $agreement->get( 'customer.name' ) ),
          "pid"=>utf8_encode( $agreement->get( 'customer.pid' ) )
        )
      )));
    }
  }
?>

In the same way as before, requesting the URL for the REST Service in your browser will return an agreement:

Posting data to the same URL will set data to an agreement.

For this example we'll create a simple HTML Overview for one of the tables, we'll choose the contactperson table. To do this, according to the Overview documentation we'll add an _overview method to the Scriptcontactpersonclass that we'll create in a file called contactperson.php which resides in the scripts/ directory.

The return of this method will be shown as an overview in the Details view of every record of this table. We'll just output a few comments and try it.

contactperson.php

<?php
class Scriptcontactperson {
  public static function _overview( &$cp ) {
    $txt = "<b>Overview of Contact Person</b><br />";
    $txt .= " <b>Name</b>: " . utf8_encode( $cp->get( 'name' ) ) . "<br />";
    $txt .= " <b>Position</b>:" . utf8_encode( $cp->getPlaintext( position ) ) . "<br />";
    $txt .= "<hr />Informations<br />";
 
    $cis = $cp->getRelated( 'contactinformations' );
    foreach( $cis AS $ci ) {
      $txt .= "  - " . $ci->getPlaintext( "type" ) . " : " . $ci->get( 'value' ) . "<br />";
    }
    return $txt;
  }
}
?>

We've now created a customer database with a few related tables, some calculated fields, an overview and a REST Webservice interface from scratch with minimal coding. This is just a small display of what the Vortex could be used for.

If you find any errors or have any questions regarding the Vortex that are not answered in this Wiki, please contact us at info@stilit.se and we'll update the wiki as soon as possible.