SQL Lite

Description

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

UI

Access the DataTrucker URL via a browser

Create a credentials

1. Go to Credentials in the sidebar > SQLLite 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 Filename attribute is the location of the SQL server DB file as accessed from the API server.

Make an API

1. Go to Database API in the Sidebar > SQLLite 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:  - resourcename: step3    name: step3    type: DB-Sqllite    credentialname: sqllitedata3    tenant: Admin    restmethod: GET    script: select date(),'[[dbname]]' as current_date;    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": "sqllitedata4",     "type": "DB-Sqllite",     "filename": "/workspace/trucker/test1.db"}
Response: 201 OK

Create the API via REST

URL: /api/v1/resourcesTYPE: POSTHEADER: Authorization: "Bearer <JWT Token>"BODY (JSON): {            "resourcename": "step3",            "type": "DB-Sqllite",            "credentialname": "sqllitedata3",            "restmethod": "GET",            "script": "select date(),'[[dbname]]' as current_date;",            "job_timeout": 4354,            "validations": {                 "type": "object",                 "properties": {                 "dbname": {                      "type": "string",                      "pattern": "^[a-z0-9]*$",                      "maxLength": 8                  }             }        }}
Response: 201 OK