SQL Lite
#
DescriptionCreate an API to allow SQLLite to be accessible via REST.
#
UIAccess the DataTrucker URL via a browser
#
Create a credentials- Go to Credentials in the sidebar > SQLLite Credentials
- Scroll down to Credential Management.
- Fill the form to create the connection
- Credential name โ an arbitrary value to identify the credential object.
- The Filename attribute is the location of the SQL server DB file as accessed from the API server.
#
Make an API- Go to Database API in the Sidebar > SQLLite APIโs
- Fill the form to create the API
- Resource Name: an arbitrary name to identify the resource
- The Credential linked to the resources
- Method: The type of REST Call
- Timeout: To ensure query times out after a set period in time
- Script: The SQL Query
- The SQL query can have parameters identified by โ[[]]โ Example โ[[id]]โ [ single quotes recommended instead of double quotes ]โ
- Parameters are case sensitive in some cases
- Validation Regex of Input values, input sanitization before querying the database
- Examples provided in the UI when you try to create a new API
#
Query the resource you createdURL: /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 / KubernetesFor 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 RESTURL: /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 RESTURL: /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