Skip to content
Merged
Show file tree
Hide file tree
Changes from all commits
Commits
File filter

Filter by extension

Filter by extension

Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
5 changes: 4 additions & 1 deletion .vscode/launch.json
Original file line number Diff line number Diff line change
Expand Up @@ -47,7 +47,10 @@
"select role_name from pgi.information_schema.applicable_roles where role_name = 'pg_database_owner';",
"select r1.role_name from pgi.information_schema.applicable_roles r1 inner join pgi.information_schema.applicable_roles r2 on r1.role_name = r2.role_name order by r1.role_name desc;",
"select rtg.table_catalog, rtg.table_schema, rtg.table_name, rtg.privilege_type, rtg.is_grantable, ar.is_grantable as role_is_grantable from pgi.information_schema.role_table_grants rtg inner join pgi.information_schema.applicable_roles ar on rtg.grantee = ar.grantee where rtg.table_name = 'pg_statistic' order by privilege_type desc;",
"SELECT zone AS zone, count(kind) AS \"COUNT(kind)\" FROM (SELECT * from stackql_intel.\"google.compute.acceleratorTypes\" limit 3) AS virtual_table GROUP BY zone ORDER BY \"COUNT(kind)\" DESC LIMIT 1000;"
"SELECT zone AS zone, count(kind) AS \"COUNT(kind)\" FROM (SELECT * from stackql_intel.\"google.compute.acceleratorTypes\" limit 3) AS virtual_table GROUP BY zone ORDER BY \"COUNT(kind)\" DESC LIMIT 1000;",
"select * from (select VolumeId, Encrypted, Size from aws.ec2.volumes where region = 'ap-southeast-1' order by VolumeId asc) as zz;",
"select * from (select id, name, url from github.repos.repos where org = 'stackql') zz;",
"select * from zz1;"
],
"default": "show providers;"
},
Expand Down
21 changes: 21 additions & 0 deletions docs/dashboards.md
Original file line number Diff line number Diff line change
@@ -0,0 +1,21 @@

## Dashboard software particulars

- Dashboard software can interrogate views.
- Tables and views need to be defined *a priori* for queries to work.
This implies that some AOT pre-charging of tables is required. Here are some options:
- ~~(a) Dedicate some namespace to such queries. For any query against the namespace, front load table and view creation and then proceed.~~ **This, on its own, will NOT work for creating dashboards because the dashboard software will be unaware of the requisite table / view objects.**
- (b) Pre-charge all tables and views upon provider download. Superficially, seems damned expensive.
- (c) **Hack**: Run ad hoc queries (or batches) to pre-charge from within dashboard (or `stackql`) as an offline, admin activity.
- Doing either (b) or (c) will require `stackql` to natively support subqueries.
- Could consider some `precharge <table_name_list> ;` grammar to facilitate.


## Data flow

1. Admin (or automated) action to pre-charge tables.
1. Admin user enters `stackql` query to pre-charge tables.
2. `stackql` executes query and returns results.



7 changes: 7 additions & 0 deletions internal/stackql/astanalysis/annotatedast/annotated_ast.go
Original file line number Diff line number Diff line change
Expand Up @@ -67,6 +67,10 @@ func (aa *standardAnnotatedAst) SetSelectMetadata(selNode *sqlparser.Select, met
func (aa *standardAnnotatedAst) GetIndirect(node sqlparser.SQLNode) (astindirect.Indirect, bool) {
switch n := node.(type) {
case *sqlparser.AliasedTableExpr:
rv, ok := aa.tableIndirects[n.As.GetRawVal()]
if ok {
return rv, true
}
return aa.GetIndirect(n.Expr)
case sqlparser.TableName:
rv, ok := aa.tableIndirects[n.GetRawVal()]
Expand All @@ -84,6 +88,9 @@ func (aa *standardAnnotatedAst) SetIndirect(node sqlparser.SQLNode, indirect ast
switch n := node.(type) {
case sqlparser.TableName:
aa.tableIndirects[n.GetRawVal()] = indirect
case *sqlparser.AliasedTableExpr:
// this is for subqueries
aa.tableIndirects[n.As.GetRawVal()] = indirect
default:
}
}
Expand Down
151 changes: 63 additions & 88 deletions internal/stackql/astanalysis/earlyanalysis/ast_expand.go
Original file line number Diff line number Diff line change
Expand Up @@ -73,6 +73,53 @@ func newIndirectExpandAstVisitor(
return rv, nil
}

func (v *indirectExpandAstVisitor) processIndirect(node sqlparser.SQLNode, indirect astindirect.Indirect) error {
err := indirect.Parse()
if err != nil {
return nil
}
childAnalyzer, err := NewEarlyScreenerAnalyzer(v.primitiveGenerator, v.annotatedAST, v.whereParams.Clone())
if err != nil {
return err
}
childAnalyzer.SetIndirectionDepth(v.indirectionDepth + 1)
err = childAnalyzer.Analyze(indirect.GetSelectAST(), v.handlerCtx, v.tcc)
if err != nil {
return err
}
// Persist indirect for later retrieval
v.annotatedAST.SetIndirect(node, indirect)
indirectPrimitiveGenerator := v.primitiveGenerator.CreateIndirectPrimitiveGenerator(indirect.GetSelectAST(), v.handlerCtx)
err = indirectPrimitiveGenerator.AnalyzeSelectStatement(childAnalyzer.GetPlanBuilderInput())
if err != nil {
return err
}
maxIndirectCount := childAnalyzer.GetIndirectionDepth()
if maxIndirectCount > constants.LimitsIndirectMaxChainLength {
return fmt.Errorf("query error: indirection chain length %d > %d and is therefore disallowed; please do not cite views from within views", maxIndirectCount, constants.LimitsIndirectMaxChainLength)
}
indirectPrimitiveGenerator.GetPrimitiveComposer().GetAst()
selCtx := indirectPrimitiveGenerator.GetPrimitiveComposer().GetIndirectSelectPreparedStatementCtx()
underlyingSymTab := indirectPrimitiveGenerator.GetPrimitiveComposer().GetSymTab()
colz := selCtx.GetNonControlColumns()
for _, col := range colz {
colId := col.GetIdentifier()
colEntry := symtab.NewSymTabEntry(
indirectPrimitiveGenerator.GetPrimitiveComposer().GetDRMConfig().GetRelationalType(col.GetType()),
"",
"",
)
underlyingSymTab.SetSymbol(colId, colEntry)
}
indirect.SetUnderlyingSymTab(underlyingSymTab)
indirect.SetSelectContext(indirectPrimitiveGenerator.GetPrimitiveComposer().GetIndirectSelectPreparedStatementCtx())
assignedParams, ok := indirectPrimitiveGenerator.GetPrimitiveComposer().GetAssignedParameters()
if ok {
indirect.SetAssignedParameters(assignedParams)
}
return nil
}

func (v *indirectExpandAstVisitor) ContainsAnalyticsCacheMaterial() bool {
return v.containsAnalyticsCacheMaterial
}
Expand Down Expand Up @@ -618,6 +665,20 @@ func (v *indirectExpandAstVisitor) Visit(node sqlparser.SQLNode) error {

case *sqlparser.AliasedTableExpr:
if node.Expr != nil {
switch n := node.Expr.(type) {
case *sqlparser.Subquery:
sq := internaldto.NewSubqueryDTO(node, n)
indirect, err := astindirect.NewSubqueryIndirect(sq)
if err != nil {
return nil
}
err = v.processIndirect(node, indirect)
if err != nil {
return nil
}
return nil

}
err := node.Expr.Accept(v)
if err != nil {
return err
Expand Down Expand Up @@ -698,52 +759,13 @@ func (v *indirectExpandAstVisitor) Visit(node sqlparser.SQLNode) error {
viewDTO, isView := v.sqlSystem.GetViewByName(node.GetRawVal())
if isView {
indirect, err := astindirect.NewViewIndirect(viewDTO)
if err != nil {
return nil
}
err = indirect.Parse()
if err != nil {
return nil
}
childAnalyzer, err := NewEarlyScreenerAnalyzer(v.primitiveGenerator, v.annotatedAST, v.whereParams.Clone())
if err != nil {
return err
}
childAnalyzer.SetIndirectionDepth(v.indirectionDepth + 1)
err = childAnalyzer.Analyze(indirect.GetSelectAST(), v.handlerCtx, v.tcc)
if err != nil {
return err
}
// Persist indirect for later retrieval
v.annotatedAST.SetIndirect(node, indirect)
indirectPrimitiveGenerator := v.primitiveGenerator.CreateIndirectPrimitiveGenerator(indirect.GetSelectAST(), v.handlerCtx)
err = indirectPrimitiveGenerator.AnalyzeSelectStatement(childAnalyzer.GetPlanBuilderInput())
err = v.processIndirect(node, indirect)
if err != nil {
return err
}
maxIndirectCount := childAnalyzer.GetIndirectionDepth()
if maxIndirectCount > constants.LimitsIndirectMaxChainLength {
return fmt.Errorf("query error: indirection chain length %d > %d and is therefore disallowed; please do not cite views from within views", maxIndirectCount, constants.LimitsIndirectMaxChainLength)
}
indirectPrimitiveGenerator.GetPrimitiveComposer().GetAst()
selCtx := indirectPrimitiveGenerator.GetPrimitiveComposer().GetIndirectSelectPreparedStatementCtx()
underlyingSymTab := indirectPrimitiveGenerator.GetPrimitiveComposer().GetSymTab()
colz := selCtx.GetNonControlColumns()
for _, col := range colz {
colId := col.GetIdentifier()
colEntry := symtab.NewSymTabEntry(
indirectPrimitiveGenerator.GetPrimitiveComposer().GetDRMConfig().GetRelationalType(col.GetType()),
"",
"",
)
underlyingSymTab.SetSymbol(colId, colEntry)
}
indirect.SetUnderlyingSymTab(underlyingSymTab)
indirect.SetSelectContext(indirectPrimitiveGenerator.GetPrimitiveComposer().GetIndirectSelectPreparedStatementCtx())
assignedParams, ok := indirectPrimitiveGenerator.GetPrimitiveComposer().GetAssignedParameters()
if ok {
indirect.SetAssignedParameters(assignedParams)
}
}
return nil

Expand Down Expand Up @@ -873,54 +895,7 @@ func (v *indirectExpandAstVisitor) Visit(node sqlparser.SQLNode) error {
buf.AstPrintf(node, "(%v)", sqlparser.Exprs(node))

case *sqlparser.Subquery:
indirect, err := astindirect.NewSubqueryIndirect(node)
if err != nil {
return nil
}
err = indirect.Parse()
if err != nil {
return nil
}
childAnalyzer, err := NewEarlyScreenerAnalyzer(v.primitiveGenerator, v.annotatedAST, v.whereParams.Clone())
if err != nil {
return err
}
childAnalyzer.SetIndirectionDepth(v.indirectionDepth + 1)
err = childAnalyzer.Analyze(indirect.GetSelectAST(), v.handlerCtx, v.tcc)
if err != nil {
return err
}
// Persist indirect for later retrieval
v.annotatedAST.SetIndirect(node, indirect)
indirectPrimitiveGenerator := v.primitiveGenerator.CreateIndirectPrimitiveGenerator(indirect.GetSelectAST(), v.handlerCtx)
err = indirectPrimitiveGenerator.AnalyzeSelectStatement(childAnalyzer.GetPlanBuilderInput())
if err != nil {
return err
}
maxIndirectCount := childAnalyzer.GetIndirectionDepth()
if maxIndirectCount > constants.LimitsIndirectMaxChainLength {
return fmt.Errorf("query error: indirection chain length %d > %d and is therefore disallowed; please do not cite views from within views", maxIndirectCount, constants.LimitsIndirectMaxChainLength)
}
indirectPrimitiveGenerator.GetPrimitiveComposer().GetAst()
selCtx := indirectPrimitiveGenerator.GetPrimitiveComposer().GetIndirectSelectPreparedStatementCtx()
underlyingSymTab := indirectPrimitiveGenerator.GetPrimitiveComposer().GetSymTab()
colz := selCtx.GetNonControlColumns()
for _, col := range colz {
colId := col.GetIdentifier()
colEntry := symtab.NewSymTabEntry(
indirectPrimitiveGenerator.GetPrimitiveComposer().GetDRMConfig().GetRelationalType(col.GetType()),
"",
"",
)
underlyingSymTab.SetSymbol(colId, colEntry)
}
indirect.SetUnderlyingSymTab(underlyingSymTab)
indirect.SetSelectContext(indirectPrimitiveGenerator.GetPrimitiveComposer().GetIndirectSelectPreparedStatementCtx())
assignedParams, ok := indirectPrimitiveGenerator.GetPrimitiveComposer().GetAssignedParameters()
if ok {
indirect.SetAssignedParameters(assignedParams)
}
buf.AstPrintf(node, "(%v)", node.Select)
return fmt.Errorf("invariant violation in query analysis: subquery should be eagerly analysed")

case sqlparser.ListArg:
buf.WriteArg(string(node))
Expand Down
9 changes: 5 additions & 4 deletions internal/stackql/astindirect/indirect.go
Original file line number Diff line number Diff line change
Expand Up @@ -31,13 +31,14 @@ func NewViewIndirect(viewDTO internaldto.ViewDTO) (Indirect, error) {
return rv, nil
}

func NewSubqueryIndirect(subQuery *sqlparser.Subquery) (Indirect, error) {
if subQuery == nil {
func NewSubqueryIndirect(subQueryDTO internaldto.SubqueryDTO) (Indirect, error) {
if subQueryDTO == nil {
return nil, fmt.Errorf("cannot accomodate nil subquery")
}
rv := &subquery{
subQuery: subQuery,
selectStmt: subQuery.Select,
subQueryDTO: subQueryDTO,
subQuery: subQueryDTO.GetSubquery(),
selectStmt: subQueryDTO.GetSubquery().Select,
underlyingSymbolTable: symtab.NewHashMapTreeSymTab(),
}
return rv, nil
Expand Down
7 changes: 3 additions & 4 deletions internal/stackql/astindirect/subquery_indirect.go
Original file line number Diff line number Diff line change
Expand Up @@ -4,13 +4,12 @@ import (
"github.com/stackql/go-openapistackql/openapistackql"
"github.com/stackql/stackql/internal/stackql/drm"
"github.com/stackql/stackql/internal/stackql/internal_data_transfer/internaldto"
"github.com/stackql/stackql/internal/stackql/parse"
"github.com/stackql/stackql/internal/stackql/symtab"
"vitess.io/vitess/go/vt/sqlparser"
)

type subquery struct {
viewDTO internaldto.ViewDTO
subQueryDTO internaldto.SubqueryDTO
subQuery *sqlparser.Subquery
selectStmt sqlparser.SelectStatement
selCtx drm.PreparedStatementCtx
Expand Down Expand Up @@ -39,7 +38,7 @@ func (v *subquery) SetUnderlyingSymTab(symbolTable symtab.SymTab) {
}

func (v *subquery) GetName() string {
return v.viewDTO.GetName()
return v.subQueryDTO.GetAlias().GetRawVal()
}

func (v *subquery) GetColumns() []internaldto.ColumnMetadata {
Expand Down Expand Up @@ -76,7 +75,7 @@ func (v *subquery) GetTables() sqlparser.TableExprs {
}

func (v *subquery) getAST() (sqlparser.Statement, error) {
return parse.ParseQuery(v.viewDTO.GetRawQuery())
return v.subQuery.Select, nil
}

func (v *subquery) GetSelectAST() sqlparser.SelectStatement {
Expand Down
7 changes: 6 additions & 1 deletion internal/stackql/astvisit/from_rewrite.go
Original file line number Diff line number Diff line change
Expand Up @@ -6,6 +6,7 @@ import (

"github.com/stackql/stackql/internal/stackql/astanalysis/annotatedast"
"github.com/stackql/stackql/internal/stackql/astformat"
"github.com/stackql/stackql/internal/stackql/astindirect"
"github.com/stackql/stackql/internal/stackql/drm"
"github.com/stackql/stackql/internal/stackql/sql_system"
"github.com/stackql/stackql/internal/stackql/tablenamespace"
Expand Down Expand Up @@ -634,7 +635,11 @@ func (v *standardFromRewriteAstVisitor) Visit(node sqlparser.SQLNode) error {
if indirect, isIndirect := anCtx.GetTableMeta().GetIndirect(); isIndirect {
//
alias := indirect.GetName()
templateString := fmt.Sprintf(` ( %%s ) AS "%s" `, alias)
indirectType := indirect.GetType()
templateString := ` ( %s ) `
if indirectType == astindirect.ViewType {
templateString = fmt.Sprintf(` ( %%s ) AS "%s" `, alias)
}
v.rewrittenQuery = templateString
v.indirectContexts = append(v.indirectContexts, indirect.GetSelectContext())

Expand Down
4 changes: 3 additions & 1 deletion internal/stackql/dependencyplanner/dependencyplanner.go
Original file line number Diff line number Diff line change
Expand Up @@ -121,7 +121,9 @@ func (dp *standardDependencyPlanner) Plan() error {
}
tableExpr := unit.GetTableExpr()
annotation := unit.GetAnnotation()
if _, isView := annotation.GetView(); isView {
_, isView := annotation.GetView()
_, isSubquery := annotation.GetSubquery()
if isView || isSubquery {
dp.annMap[tableExpr] = annotation
continue
}
Expand Down
Original file line number Diff line number Diff line change
Expand Up @@ -20,13 +20,15 @@ type HeirarchyIdentifiers interface {
GetSQLDataSourceTableName() string
GetStackQLTableName() string
GetTableName() string
GetSubquery() (SubqueryDTO, bool)
GetView() (ViewDTO, bool)
GetSubAST() sqlparser.Statement
ContainsNativeDBMSTable() bool
SetContainsNativeDBMSTable(bool)
SetSubAST(sqlparser.Statement)
SetMethodStr(string)
WithView(ViewDTO) HeirarchyIdentifiers
withSubquery(SubqueryDTO) HeirarchyIdentifiers
WithProviderStr(string) HeirarchyIdentifiers
WithResponseSchemaStr(rss string) HeirarchyIdentifiers
}
Expand All @@ -38,6 +40,7 @@ type standardHeirarchyIdentifiers struct {
responseSchemaStr string
methodStr string
viewDTO ViewDTO
subqueryDTO SubqueryDTO
viewAST sqlparser.Statement
containsDBMSTable bool
}
Expand Down Expand Up @@ -74,6 +77,10 @@ func (hi *standardHeirarchyIdentifiers) GetView() (ViewDTO, bool) {
return hi.viewDTO, hi.viewDTO != nil
}

func (hi *standardHeirarchyIdentifiers) GetSubquery() (SubqueryDTO, bool) {
return hi.subqueryDTO, hi.subqueryDTO != nil
}

func (hi *standardHeirarchyIdentifiers) GetResourceStr() string {
return hi.resourceStr
}
Expand All @@ -96,6 +103,11 @@ func (hi *standardHeirarchyIdentifiers) WithView(viewDTO ViewDTO) HeirarchyIdent
return hi
}

func (hi *standardHeirarchyIdentifiers) withSubquery(subQuery SubqueryDTO) HeirarchyIdentifiers {
hi.subqueryDTO = subQuery
return hi
}

func NewHeirarchyIdentifiers(prov, svc, rsc, method string) HeirarchyIdentifiers {
return &standardHeirarchyIdentifiers{
providerStr: prov,
Expand Down Expand Up @@ -162,3 +174,12 @@ func ResolveResourceTerminalHeirarchyIdentifiers(node sqlparser.TableName) Heira
"",
)
}

func ObtainSubqueryHeirarchyIdentifiers(subQuery SubqueryDTO) HeirarchyIdentifiers {
return NewHeirarchyIdentifiers(
"",
"",
"",
"",
).withSubquery(subQuery)
}
Loading