greglindahl/mysql.md

## mysql.md

      
    Raw
  

              mysql.md
            
          
    Welcome to the MySQL Element

At a Glance

In order to create a connection to a MySQL Database, the following steps are required:

Choose how to connect:


Directly via an IP Address and Port Number, e.g. 123.123.1.123:3306
Use Cloud Elements Ground2Cloud service to connect your Database


Call the POST /instances API to instantiate your MySQL Database

In Depth

The MySQL Element leverages the tables contained within your MySQL database and transforms them into a collection of resources. RESTful methods (POST, GET, PUT, PATCH, DELETE) are used to interact with these resources (accounts, contacts, files). The columns in the table become the modeling attributes used to send payloads with each API call.
For example a database with the following columns of data:


name
phone
address


Jon Doe
333-333-3333
123 Main


transforms to this JSON body:
{
  "name": "Jon Doe",
  "phone": 333-333-3333,
  "address": "123 Main"
}
If the table contains a primary key, the Retrieve, Update, and Delete by ID APIs can be generated.
If a table does not have a primary key or contains multiple primary keys, the Retrieve, Update, and Delete by ID APIs cannot be generated.
Elements leverage Cloud Elements API Manager platform services including authentication and data transformation.  The API is built to allow you to create a functional application or integration quickly and easily.
All API calls to Cloud Elements should be made to the https://api.cloud-elements.com/elements/api-v2 base domain. Requests are authorized with an Organization and User secret, as well as, an Element token.  We use many standard HTTP features, like HTTP verbs, which can be understood by many HTTP clients. JSON will be returned in all responses from the API, including errors. The APIs are designed to have predictable, straightforward URLs and to use HTTP response codes to indicate API errors.
NOTE:  API Docs are not available for viewing as they are generated based on the information contained in the Database Table.
Get started by selecting how to connect your database.
Endpoint Setup

Cloud Elements supports two ways of connecting a Databse:

Connect Directly via IP Address and Port Number
Use Cloud Elements Ground2Cloud service

Option 1: Connecting Directly via IP Address and Port Number

This method would require a port be exposed so a connection can be made with Cloud Elements.
When creating an instance, the user would input the IP Address and Port Number exposed publicly.
Option 2: Connecting via Ground2Cloud

The Ground2Cloud integration consists of two parts: Client and Server.
The Ground2Cloud Client creates a tunnel to a public Ground2Cloud Server, and enables requests from the Cloud Elements Production Cloud to transparently pass through that tunnel to reach the Client Service.

The Ground2Cloud Client installation program is a self-unpacking executable. Once it finishes running, the Ground2Cloud Client is installed as Windows Service which constantly runs to keep this tunnel open. You generally don’t have to worry about this; once installed, the service automatically restarts in case of failure, or when your Windows machine is rebooted.
The installer also installs a GUI (Graphical User Interface) program, which can be used to monitor and manage the Ground2Cloud Client. When launched, it opens a window with simple dialogs that let you browse logs files, change configuration, and perform other management operations. Details on how to use the GUI is described in the User’s Manual.
If you are interested in using our Ground2Cloud Service, please contact us for details.
Next create an instance.
Create Instance Directly via IP Address and Port Number

The following is required to create a MySQL Element Instance:

Database Host: e.g. 123.123.1.123:3306
Database Name
Database Username
Database Password

Step 1. Create an Instance

To provision your MySQL Element, use the /instances API.
Below is an example of the provisioning API call.

HTTP Headers: Authorization- User , Organization 
HTTP Verb: POST
Request URL: /instances
Request Body: Required – see below
Query Parameters: none

Description: An Element token is returned upon successful execution of this API. This token needs to be retained by the application for all subsequent requests involving this element instance.
A sample request illustrating the /instances API is shown below.
HTTP Headers:
Authorization: User <INSERT_USER_SECRET>, Organization <INSERT_ORGANIZATION_SECRET>

This instance.json file must be included with your instance request.  Please fill your information to provision.  The “key” into Cloud Elements MySQL is "mysql".  This will need to be entered in the “key” field below depending on which Element you wish to instantiate.
CONNECTING DIRECTLY VIA IP ADDRESS AND PORT NUMBER
{
  "element": {
    "key": "mysql"
  },
  "configuration" : {
      "db.host": "<INSERT_DATABASE_HOST_EG_123.123.1.123:3306>",
      "db.name": "<INSERT_DATABASE_NAME>",
      "username": "<INSERT_DATABASE_USERNAME>",
      "password":  "<INSERT_DATABASE_PASSWORD>"
  },
  "tags": [
    "<INSERT_TAGS>"
  ],
  "name": "<INSERT_INSTANCE_NAME>"
}
Here is an example cURL command to create an instance using /instances API.
Example Request:
curl -X POST
-H 'Authorization: User <INSERT_USER_SECRET>, Organization <INSERT_ORGANIZATION_SECRET>'
-H 'Content-Type: application/json'
-d @instance.json
'https://api.cloud-elements.com/elements/api-v2/instances'
If the user does not specify a required config entry, an error will result notifying her of which entries she is missing.
Below is a successful JSON response:
{
  "id": 1234,
  "name": "Test",
  "token": "VAnlQ/V28PT+M62kdajlsd90eHHtUJai+Efq8=",
  "id": 479,
      "name": "MySQL",
      "key": "mysql",
      "description": "Add a MySQL element to connect your existing MySQL database, allowing you to manage data for your database tables. You will need your MySQL database information to add an instance.",
      "image": "elements/provider_mysql.png",
      "active": true,
      "deleted": false,
      "typeOauth": false,
      "trialAccount": false,
      "transformationsEnabled": true,
      "bulkDownloadEnabled": false,
      "bulkUploadEnabled": false,
      "cloneable": false,
      "authentication": {
        "type": "custom"
      },
      "hub": "db"
    },
  {
    "valid": true,
    "maxCacheSize": 0,
    "cacheTimeToLive": 0,
    "configuration": {},
    "eventsEnabled": false,
    "traceLoggingEnabled": false,
    "cachingEnabled": false
  }
Note:  Make sure you have straight quotes in your JSON files and cURL commands.  Please use plain text formatting in your code.  Make sure you do not have spaces after the in the cURL command.
Create Instance Directly via Ground2Cloud

The following is required to create a MySQL Element Instance:

Database Host: e.g. g2c.cloud-elements.com:12345
Database Name
Database Username
Database Password

Step 1. Create an Instance

To provision your MySQL Element, use the /instances API.
Below is an example of the provisioning API call.

HTTP Headers: Authorization- User , Organization 
HTTP Verb: POST
Request URL: /instances
Request Body: Required – see below
Query Parameters: none

Description: An Element token is returned upon successful execution of this API. This token needs to be retained by the application for all subsequent requests involving this element instance.
A sample request illustrating the /instances API is shown below.
HTTP Headers:
Authorization: User <INSERT_USER_SECRET>, Organization <INSERT_ORGANIZATION_SECRET>

This instance.json file must be included with your instance request.  Please fill your information to provision.  The “key” into Cloud Elements MySQL is "mysql".  This will need to be entered in the “key” field below depending on which Element you wish to instantiate.
CONNECTING DIRECTLY VIA GROUND2CLOUD
{
  "element": {
    "key": "mysql"
  },
  "configuration" : {
      "db.host": "<INSERT_DATABASE_HOST_EG_g2c.cloud-elements.com:12345>",
      "db.name": "<INSERT_DATABASE_NAME>",
      "username": "<INSERT_DATABASE_USERNAME>",
      "password":  "<INSERT_DATABASE_PASSWORD>"
  },
  "tags": [
    "<INSERT_TAGS>"
  ],
  "name": "<INSERT_INSTANCE_NAME>"
}
Here is an example cURL command to create an instance using /instances API.
Example Request:
curl -X POST
-H 'Authorization: User <INSERT_USER_SECRET>, Organization <INSERT_ORGANIZATION_SECRET>'
-H 'Content-Type: application/json'
-d @instance.json
'https://api.cloud-elements.com/elements/api-v2/instances'
If the user does not specify a required config entry, an error will result notifying her of which entries she is missing.
Below is a successful JSON response:
{
  "id": 1234,
  "name": "Test",
  "token": "VAnlQ/V28PT+M62kdajlsd90eHHtUJai+Efq8=",
  "id": 479,
      "name": "MySQL",
      "key": "mysql",
      "description": "Add a MySQL element to connect your existing MySQL database, allowing you to manage data for your database tables. You will need your MySQL database information to add an instance.",
      "image": "elements/provider_mysql.png",
      "active": true,
      "deleted": false,
      "typeOauth": false,
      "trialAccount": false,
      "transformationsEnabled": true,
      "bulkDownloadEnabled": false,
      "bulkUploadEnabled": false,
      "cloneable": false,
      "authentication": {
        "type": "custom"
      },
      "hub": "db"
    },
  {
    "valid": true,
    "maxCacheSize": 0,
    "cacheTimeToLive": 0,
    "configuration": {},
    "eventsEnabled": false,
    "traceLoggingEnabled": false,
    "cachingEnabled": false
  }
Note:  Make sure you have straight quotes in your JSON files and cURL commands.  Please use plain text formatting in your code.  Make sure you do not have spaces after the in the cURL command.