stackql / stackql Goto Github PK

Add unit testing for internal/stackql/handler.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/kstore.

Acceptance Criteria:

• 85% unit test coverage.

Support for create, mutate and delete of tables, NOT including temporary lifetime support.

Describe the bug
When running a SHOW INSERT INTO operation, to query template contains a reference to .values which does not exist in the jsonne config block generated.

To Reproduce
running...

./stackql exec --output=text -H -f virtual_machines.iql "SHOW INSERT /*+ REQUIRED */ INTO azure.compute.virtual_machines"

produces a template which looks like this:

<<<jsonnet
{
   resourceGroupName: << resourceGroupName >>,
   subscriptionId: << subscriptionId >>,
   vmName: << vmName >>
}
>>>
INSERT INTO azure.compute.virtual_machines(
  resourceGroupName,
  subscriptionId,
  vmName
)
SELECT
  '{{ .values.resourceGroupName }}',
  '{{ .values.subscriptionId }}',
  '{{ .values.vmName }}'
;

Expected behavior
The template should look like this...

<<<jsonnet
{
   resourceGroupName: << resourceGroupName >>,
   subscriptionId: << subscriptionId >>,
   vmName: << vmName >>
}
>>>
INSERT INTO azure.compute.virtual_machines(
  resourceGroupName,
  subscriptionId,
  vmName
)
SELECT
  '{{ .resourceGroupName }}',
  '{{ .subscriptionId }}',
  '{{ .vmName }}'
;

Add unit testing for internal/stackql/gcexec.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/output.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/psqlwire.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/planbuilder.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/internal_data_transfer/primitive_context.

Acceptance Criteria:

• 85% unit test coverage.

Feature Description
For user/SQL IDE like experience, it would be nice to have syntax highlighting in the shell, with the prompt (stackql >>) in yellow, and keywords such as SELECT, WHERE and FROM in blue, this can listen to keystroke events, ideally the code will be shareable with or portable to a VS Code extension for StackQL so we don't have to reinvent the wheel when we do this

Add unit testing for internal/stackql/garbagecollector.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/dbmsinternal.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/iqlerror.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/parserutil.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/bundle.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/primitivecomposer.

Acceptance Criteria:

• 85% unit test coverage.

Show enhanced output for SHOW INSERT commands which includes the jsonnet block for users to add their params, for example, this command

./stackql --output=text -H exec "SHOW INSERT INTO google.container.clusters"

would output something to the effect of:

<<<jsonnet
{
 values: {
  locationsId: <<locationsId string>>,
  projectsId: <<projectsId string>>,
  data__cluster: {
	addonsConfig: <<addonsConfig object>>,
	authenticatorGroupsConfig: <<authenticatorGroupsConfig object>>,
	autopilot: <<autopilot boolean>>,
	autoscaling: <<autoscaling boolean>>,
	binaryAuthorization: <<binaryAuthorization boolean>>,
	clusterIpv4Cidr: <<clusterIpv4Cidr string>>,
	conditions: [
		<<condition object>>
	],
	...
  }
 }
>>>
INSERT INTO google.container.clusters(
  locationsId,
  projectsId,
  data__cluster,
  data__parent,
  data__projectId,
  data__zone
)
SELECT
  '{{ .values.locationsId }}',
  '{{ .values.projectsId }}',
  '{ "addonsConfig": "{{ .values.data__cluster.addonsConfig }}", "authenticatorGroupsConfig": "{{ .values.data__cluster.authenticatorGroupsConfig }}", "autopilot": "{{ .values.data__cluster.autopilot }}", "autoscaling": "{{ .values.data__cluster.autoscaling }}", "binaryAuthorization": "{{ .values.data__cluster.binaryAuthorization }}", "clusterIpv4Cidr": "{{ .values.data__cluster.clusterIpv4Cidr 
}}", "conditions": [ "{{ .values.data__cluster.conditions[0] }}" ], "confidentialNodes": "{{ .values.data__cluster.confidentialNodes }}", "costManagementConfig": "{{ .values.data__cluster.costManagementConfig }}", "createTime": "{{ .values.data__cluster.createTime }}", "currentMasterVersion": "{{ .values.data__cluster.currentMasterVersion }}", "currentNodeCount": {{ .values.data__cluster.currentNodeCount }}, "currentNodeVersion": "{{ .values.data__cluster.currentNodeVersion }}", "databaseEncryption": "{{ .values.data__cluster.databaseEncryption }}", "defaultMaxPodsConstraint": "{{ .values.data__cluster.defaultMaxPodsConstraint }}", "description": "{{ .values.data__cluster.description }}", "enableKubernetesAlpha": {{ .values.data__cluster.enableKubernetesAlpha }}, "enableTpu": {{ .values.data__cluster.enableTpu }}, "endpoint": "{{ .values.data__cluster.endpoint }}", "etag": "{{ .values.data__cluster.etag }}", "expireTime": "{{ .values.data__cluster.expireTime }}", "id": "{{ .values.data__cluster.id }}", "identityServiceConfig": "{{ .values.data__cluster.identityServiceConfig }}", "initialClusterVersion": "{{ .values.data__cluster.initialClusterVersion }}", "initialNodeCount": 
{{ .values.data__cluster.initialNodeCount }}, "instanceGroupUrls": [ "{{ .values.data__cluster.instanceGroupUrls[0] }}" ], "ipAllocationPolicy": "{{ .values.data__cluster.ipAllocationPolicy }}", "labelFingerprint": "{{ .values.data__cluster.labelFingerprint }}", "legacyAbac": "{{ .values.data__cluster.legacyAbac }}", "location": "{{ .values.data__cluster.location }}", "locations": [ "{{ .values.data__cluster.locations[0] }}" ], "loggingConfig": "{{ .values.data__cluster.loggingConfig }}", "loggingService": "{{ .values.data__cluster.loggingService }}", "maintenancePolicy": "{{ .values.data__cluster.maintenancePolicy }}", "masterAuth": "{{ .values.data__cluster.masterAuth }}", "masterAuthorizedNetworksConfig": "{{ .values.data__cluster.masterAuthorizedNetworksConfig }}", "meshCertificates": "{{ .values.data__cluster.meshCertificates }}", "monitoringConfig": "{{ .values.data__cluster.monitoringConfig }}", "monitoringService": "{{ .values.data__cluster.monitoringService }}", "name": "{{ .values.data__cluster.name }}", "network": "{{ .values.data__cluster.network }}", "networkConfig": "{{ .values.data__cluster.networkConfig }}", "networkPolicy": "{{ .values.data__cluster.networkPolicy }}", "nodeConfig": "{{ .values.data__cluster.nodeConfig }}", "nodeIpv4CidrSize": {{ .values.data__cluster.nodeIpv4CidrSize }}, "nodePoolAutoConfig": "{{ .values.data__cluster.nodePoolAutoConfig 
}}", "nodePoolDefaults": "{{ .values.data__cluster.nodePoolDefaults }}", "nodePools": [ "{{ .values.data__cluster.nodePools[0] }}" ], "notificationConfig": "{{ .values.data__cluster.notificationConfig }}", "privateClusterConfig": "{{ .values.data__cluster.privateClusterConfig }}", "releaseChannel": "{{ .values.data__cluster.releaseChannel }}", "resourceLabels": { "{{ .values.data__cluster.resourceLabels[0].key }}": "{{ .values.data__cluster.resourceLabels[0].val }}" }, "resourceUsageExportConfig": "{{ .values.data__cluster.resourceUsageExportConfig }}", "selfLink": "{{ .values.data__cluster.selfLink }}", "servicesIpv4Cidr": "{{ .values.data__cluster.servicesIpv4Cidr }}", "shieldedNodes": "{{ .values.data__cluster.shieldedNodes }}", "status": "{{ .values.data__cluster.status }}", "statusMessage": "{{ .values.data__cluster.statusMessage }}", "subnetwork": "{{ .values.data__cluster.subnetwork }}", "tpuIpv4CidrBlock": "{{ .values.data__cluster.tpuIpv4CidrBlock }}", "verticalPodAutoscaling": "{{ .values.data__cluster.verticalPodAutoscaling }}", "workloadIdentityConfig": "{{ .values.data__cluster.workloadIdentityConfig }}", "zone": "{{ .values.data__cluster.zone }}" }',
  '{{ .values.data__parent }}',
  '{{ .values.data__projectId }}',
  '{{ .values.data__zone }}'
;

Add unit testing for internal/stackql/logging.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/plan.

Acceptance Criteria:

• 85% unit test coverage.

Describe the bug
Attempting to query a field that does not exist kills the server

To Reproduce
Steps to reproduce the behavior:

1. Run the following query via the shell or exec:

select id, created_at, size_in_bytes
from github.actions.artifacts
where owner = 'stackql' and repo = 'stackql'

will get the error (with the current GitHub provider doc):

sql packet preparation error: no such column: id

2. Run the same query against the server - it will kill the server process

Expected behavior
Should just return a pg error with text similar to what is returned in the shell, should not kill the server

Add unit testing for internal/stackql/nativedb.

Acceptance Criteria:

• 85% unit test coverage.

Support for transactions v1.

Eager read only queries in-transaction.

Lazy mutations, executed on commit.

Attempted mutation failures to spill a human-readable undo log of those resources that were successfully mutated, in inverse order. That is, a path to revert (with the generous assumption that inverse order undo will work).

Add unit testing for internal/stackql/primitivegenerator.

Acceptance Criteria:

• 85% unit test coverage.

Feature Description
It would help to reduce repeated code if we could use scalar functions in the column list in the outer query in a subquery, for instance:

SELECT 
name,
iif(isResidencyRestricted = 0, 'false', 'true') as isResidencyRestricted,
backupStorageRedundancies,
supportsAvailabilityZone 
FROM (
    SELECT 
    name,
    json_extract(properties, '$.isResidencyRestricted') as isResidencyRestricted,
    json_extract(properties, '$.supportsAvailabilityZone') as supportsAvailabilityZone,
    json_extract(properties, '$.backupStorageRedundancies') as backupStorageRedundancies 
    FROM azure.cosmos_db.locations 
    WHERE subscriptionId = '631d1c6d-2a65-43e7-93c2-688bfe4e1468'
) AS virtual_table;

currently this is not supported, however it works if you remove the function, e.g.:

SELECT 
name,
isResidencyRestricted,
backupStorageRedundancies,
supportsAvailabilityZone 
FROM (
    SELECT 
    name,
    json_extract(properties, '$.isResidencyRestricted') as isResidencyRestricted,
    json_extract(properties, '$.supportsAvailabilityZone') as supportsAvailabilityZone,
    json_extract(properties, '$.backupStorageRedundancies') as backupStorageRedundancies 
    FROM azure.cosmos_db.locations 
    WHERE subscriptionId = '631d1c6d-2a65-43e7-93c2-688bfe4e1468'
) AS virtual_table;

Add unit testing for internal/stackql/internal_data_transfer/builder_input.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/methodselect.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/querysubmit.

Acceptance Criteria:

• 85% unit test coverage.

Replace the existing java mock server with some python http server that is:

• lightweight.
• template-enabled.
• simply orchestratible from robot framework.

Acceptance criteria:

• Mock server completely removed from repository.
• All existing robot tests working.
• All CICD working as expected.
• All documentation updated.

Feature Description
For analyst/user experience it would be good to include tab completion in the shell, this would work for keywords such as select, etc but ultimately would include object context (e.e services in a provider, resources in a service, projected fields in a resource)

Add unit testing for internal/stackql/provider.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/primitive.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/primitivegraph.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/parser.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/planbuilderinput.

Acceptance Criteria:

• 85% unit test coverage.

the examples with the new auth method in README still use auth object only, we should include examples with newest authentication method, could also update the vhs tape used in the README created from stackql/stackql-vhs

Feature Description
Design and implement an abstract interface (through a provider's x-stackQL-resources object) to take advantage of predicate push down to limit data (columns and/or rows) returned.

Example(s)
Given API operations which map to SELECT verbs in StackQL, identify the optional query parameters which can be mapped to the following operation:

• column list for projection, invoked using SELECT <field1>, <field2> including fields operated on by scalar functions (such as SPLIT_PART, JSON_EXTRACT, etc.)
• limiting records returned, invoked using LIMIT <num>
• filter expressions, invoked using WHERE <field> = '<something>'
• ordering predicates, invoked using ORDER BY <field>

for example...

  parameters:
    fields:
      description: Selector specifying which fields to include in a partial response.
      in: query
      name: fields
      schema:
        type: string

Create a document based interface to map verbs in the parser to the specified query parameters.

Possible Approaches or Libraries to Consider
A possible approach would be to create an extension to the provider interface for example...

components:
    x-stackQL-resources:
        databases:
          id: google.spanner.databases
          name: databases
          title: Databases
          methods:
            projects_instances_databases_get:
              operation:
                $ref: '#/paths/~1v1~1projects~1{projectsId}~1instances~1{instancesId}~1databases~1{databasesId}/get'
              response:
                mediaType: application/json
                openAPIDocKey: '200'
            projects_instances_databases_list:
              operation:
                $ref: '#/paths/~1v1~1projects~1{projectsId}~1instances~1{instancesId}~1databases/get'
              response:
                mediaType: application/json
                openAPIDocKey: '200'
                objectKey: $.databases
            ...
          sqlVerbs:
            select:
              - $ref: '#/components/x-stackQL-resources/databases/methods/projects_instances_databases_get'
              - $ref: '#/components/x-stackQL-resources/databases/methods/projects_instances_databases_list'
            insert:
              - $ref: '#/components/x-stackQL-resources/databases/methods/projects_instances_databases_create'
            update: []
            delete: []
          predicates:
            select:
              rowFilter: 
                param: filter
                example: "column1 = 'value1' AND column2 = 'value2'"
              colFilter:
                param: fields
                example: "column1,column2"
              orderBy:
                param: orderBy
                example: "column1 ASC"
              limit:
                param: limit
                example: 10

may need to implement generalized formats for each type of predicate.

Describe the bug

The recently introduced actionlint function detects a number of issues in the workflow file go,yml in particular. this piece of work is to fix these and have the check pass.

The check can be seen at: https://github.com/stackql/stackql/actions/runs/4194391714/jobs/7272489405

To Reproduce

The check can be seen at: https://github.com/stackql/stackql/actions/runs/4194391714/jobs/7272489405

Expected behavior

The actionlint check should pass.

Screenshots

N/A

Desktop (please complete the following information):

N/A

Smartphone (please complete the following information):

N/A

Additional context

N/A

Support for materialized view semantics.

Add unit testing for internal/stackql/providerconfig.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/metadatavisitors.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/internal_data_transfer/relationaldto.

Acceptance Criteria:

• 80% unit test coverage.

Add unit testing for internal/stackql/dto.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/primitivebuilder.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/entryutil.

Acceptance Criteria:

• 85% unit test coverage.

Feature Description
Current build and test workflows in GitHub Action use go.yml and lint.yml. We would like to move to goreleaser as a build and test framework, to automatically generate releases from tags.

Builds should include:

• Windows AMD64
• Darwin Universal (Fat) binary - ARM and AMD
• Linux AMD64 and ARM64
• Docker

Package artefacts should include:

• Docker images pushed to dockerhub (see existing workflows)
• Linux packages .deb, .rpm, .apk, and Archlinux packages
• Chocolatey Windows packages

other artefacts are built offline (such as the Windows MSI file and MacOS PKG artefacts as these require external signing or notarization)

Example(s)
The following workflow file .github/workflows/goreleaser.yml has been created but not completed or implemented.

Add unit testing for internal/stackql/iqlutil.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/internal_data_transfer/internaldto.

Acceptance Criteria:

• 85% unit test coverage.

Add unit testing for internal/stackql/netutils.

Acceptance Criteria:

• 85% unit test coverage.

Feature Description

BigQuery tables ought to be queryable through stackql.

Note: This is an example from the team at stackql, for a large feature. We shall also provide examples for smaller features and bugs. Example or no, this card will be actioned.

Example(s)

I want to be able to query BigQuery tables from within stackql. Eg: select column1, column2 from <connection_name>.my_big_query_table where....

The motivating use case is accessing and reporting BQ metadata. Concrete example: table count.

Other use cases include:

• Control tables in BQ, for example supporting Infrastructure As Code workflows.
• All the classical reporting structures over stackql.

Possible Approaches or Libraries to Consider

One candidate approach is the sql driver pattern using https://github.com/solcates/go-sql-bigquery.

Additional context

This is a large feature request.