MariaDB

Description

Create an API to allow MariaDB to be accessible via REST.

UI

Access the DataTrucker URL via a browser

Create a credentials

1. Go to Credentials in the sidebar > MariaDB Credentials
2. Scroll down to Credential Management
3. Fill the form to create the connection
   1. Credential name – an arbitrary value to identify the credential object
   2. The hostname of the MariaDB. Note, make sure the API server(not the UI) has access into MariaDB
   3. Username of MariaDB,
      1. Limit access to this user to the specific API. Please reach out to a DBA to provide specific grant access to this user
   4. The password of the MariaDB User
   5. Database Name
   6. Port of the Database
   7. Min /Max Pool for connection pooling.

Make an API

1. Go to Database API in the Sidebar > MariaDB API’s
2. Fill the form to create the API
   1. Resource Name: an arbitrary name to identify the resource
   2. The Credential linked to the resources
   3. Method: The type of REST Call
   4. Timeout: To ensure query times out after a set period in time
   5. Script: The SQL Query
      1. The SQL query can have parameters identified by ‘[[]]’ Example ‘[[id]]’ [ single quotes recommended instead of double quotes ]”
      2. Parameters are case sensitive in some cases
   6. Validation Regex of Input values, input sanitization before querying the database
      1. Examples provided in the UI when you try to create a new API

Query the resource you created

URL: /api/v1/jobs/<resource name>TYPE: <method defined>HEADER: Authorization: "Bearer <JWT Token>"BODY (JSON): {        "<input_key>": "<value>",        "<input_key>": "<value>",        "<input_key>": "<value>",        "<input_key>": "<value>"}
Response: 200 OK{   jsondata....}

As a CRD in Openshift / Kubernetes

For credentials use the the API below to the management end point

---apiVersion: datatrucker.datatrucker.io/v1kind: DatatruckerFlowmetadata:  name: datatruckerflow-samplespec:  Resources:    requests:      memory: "256Mi"      cpu: "250m"    limits:      memory: "256Mi"      cpu: "500m"  JobDefinitions:  - name: maria1    resourcename: maria1    type: DB-Mariadb    credentialname: mariadata1    tenant: Admin    restmethod: POST    script: " select CURDATE() as 'current_date' ,id, user,'[[dbname]]' from information_schema.processlist; "    job_timeout: 4354    validations:      type: object      properties:        dbname:          type: string          pattern: "^[a-z0-9]*$"          maxLength: 8  Keys:    configmap: placeholder  Scripts:    configmap: placeholder  Type: Job  DatatruckerConfig: datatruckerconfig-sample  Replicas: 1  API:    name: api    Image:      repository: docker.io      imageName: datatruckerio/datatrucker-api      tagName: latest

API

Create a credential via REST

URL: /api/v1/credentials/TYPE: POSTHEADER: Authorization: "Bearer <JWT Token>"BODY (JSON): {        "credentialname": "mariadata1",        "type": "DB-Mariadb",        "hostname": "localhost",        "username": "sa",        "password": "password",        "database": "primary",        "port": 3308,        "minpool": 100,        "maxpool": 200}
Response: 201 OK

Create the API via REST

URL: /api/v1/resourcesTYPE: POSTHEADER: Authorization: "Bearer <JWT Token>"BODY (JSON): {        "resourcename": "maria1",        "type": "DB-Mariadb",        "credentialname": "mariadata1",        "restmethod": "POST",        "script": " select CURDATE() as 'current_date' ,id, user,'[[dbname]]' from information_schema.processlist; ",        "job_timeout": 4354,        "validations": {          "type": "object",          "properties": {            "dbname": {              "type": "string",              "pattern": "^[a-z0-9]*$",              "maxLength": 8            }          }        }      }
Response: 201 OK