fix: Fix SQLite migration logic (#6372)

hermanschaaf · web-flow · commit 5f8a1973482a · 2023-01-05T14:32:26.000Z
There was a bug in the query that checks whether a table exists - it always found the table to not exist because `?` was being quoted. We also now select only the columns defined in the table schema, rather than all columns. This will allow https://github.com/cloudquery/plugin-sdk/pull/574/files to pass.
diff --git a/plugins/destination/sqlite/client/migrate.go b/plugins/destination/sqlite/client/migrate.go
@@ -10,7 +10,7 @@ import (
 )
 
 const (
-	isTableExistSQL = "SELECT count(name) FROM sqlite_master WHERE type='table' AND name='?';"
+	isTableExistSQL = "SELECT count(name) FROM sqlite_master WHERE type='table' AND name=?;"
 
 	// https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns
 	sqlTableInfo = "PRAGMA table_info('%s');"
diff --git a/plugins/destination/sqlite/client/read.go b/plugins/destination/sqlite/client/read.go
@@ -4,12 +4,13 @@ import (
 	"context"
 	"database/sql"
 	"fmt"
+	"strings"
 
 	"github.com/cloudquery/plugin-sdk/schema"
 )
 
 const (
-	readSQL = `SELECT * FROM "%s" WHERE _cq_source_name = $1 order by _cq_sync_time asc`
+	readSQL = `SELECT %s FROM "%s" WHERE _cq_source_name = $1 order by _cq_sync_time asc`
 )
 
 func (*Client) createResultsArray(table *schema.Table) []any {
@@ -73,7 +74,12 @@ func (*Client) createResultsArray(table *schema.Table) []any {
 }
 
 func (c *Client) Read(ctx context.Context, table *schema.Table, sourceName string, res chan<- []any) error {
-	rows, err := c.db.Query(fmt.Sprintf(readSQL, table.Name), sourceName)
+	colNames := make([]string, 0, len(table.Columns))
+	for _, col := range table.Columns {
+		colNames = append(colNames, `"`+col.Name+`"`)
+	}
+	cols := strings.Join(colNames, ", ")
+	rows, err := c.db.Query(fmt.Sprintf(readSQL, cols, table.Name), sourceName)
 	if err != nil {
 		return err
 	}

Original file line number	Diff line number	Diff line change
`@@ -10,7 +10,7 @@ import (`
`10`	`10`	`)`
`11`	`11`
`12`	`12`	`const (`
`13`		`- isTableExistSQL = "SELECT count(name) FROM sqlite_master WHERE type='table' AND name='?';"`
	`13`	`+ isTableExistSQL = "SELECT count(name) FROM sqlite_master WHERE type='table' AND name=?;"`
`14`	`14`
`15`	`15`	`// https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns`
`16`	`16`	`sqlTableInfo = "PRAGMA table_info('%s');"`
Original file line number	Diff line number	Diff line change
`@@ -4,12 +4,13 @@ import (`
`4`	`4`	`"context"`
`5`	`5`	`"database/sql"`
`6`	`6`	`"fmt"`
	`7`	`+ "strings"`
`7`	`8`
`8`	`9`	`"github.com/cloudquery/plugin-sdk/schema"`
`9`	`10`	`)`
`10`	`11`
`11`	`12`	`const (`
`12`		- readSQL = `SELECT * FROM "%s" WHERE _cq_source_name = $1 order by _cq_sync_time asc`
	`13`	+ readSQL = `SELECT %s FROM "%s" WHERE _cq_source_name = $1 order by _cq_sync_time asc`
`13`	`14`	`)`
`14`	`15`
`15`	`16`	`func (Client) createResultsArray(table schema.Table) []any {`
`@@ -73,7 +74,12 @@ func (Client) createResultsArray(table schema.Table) []any {`
`73`	`74`	`}`
`74`	`75`
`75`	`76`	`func (c Client) Read(ctx context.Context, table schema.Table, sourceName string, res chan<- []any) error {`
`76`		`- rows, err := c.db.Query(fmt.Sprintf(readSQL, table.Name), sourceName)`
	`77`	`+ colNames := make([]string, 0, len(table.Columns))`
	`78`	`+ for _, col := range table.Columns {`
	`79`	+ colNames = append(colNames, `"`+col.Name+`"`)
	`80`	`+ }`
	`81`	`+ cols := strings.Join(colNames, ", ")`
	`82`	`+ rows, err := c.db.Query(fmt.Sprintf(readSQL, cols, table.Name), sourceName)`
`77`	`83`	`if err != nil {`
`78`	`84`	`return err`
`79`	`85`	`}`