Method IntrospectSchema

internal/engine/sqlite/analyzer/analyze.go:261–322

IntrospectSchema queries the database to build a catalog containing tables and columns for the database.

(ctx context.Context, schemas []string)

// IntrospectSchema queries the database to build a catalog containing
// tables and columns for the database.
func (a *Analyzer) IntrospectSchema(ctx context.Context, schemas []string) (*catalog.Catalog, error) {
	a.mu.Lock()
	defer a.mu.Unlock()

	if a.conn == nil {
		return nil, fmt.Errorf("database connection not initialized")
	}

	// Build catalog
	cat := &catalog.Catalog{
		DefaultSchema: "main",
	}

	// Create default schema
	mainSchema := &catalog.Schema{Name: "main"}
	cat.Schemas = append(cat.Schemas, mainSchema)

	// Query tables from sqlite_master
	stmt, _, err := a.conn.Prepare("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
	if err != nil {
		return nil, fmt.Errorf("introspect tables: %w", err)
	}

	tableNames := []string{}
	for stmt.Step() {
		tableName := stmt.ColumnText(0)
		tableNames = append(tableNames, tableName)
	}
	stmt.Close()

	// For each table, get column information using PRAGMA table_info
	for _, tableName := range tableNames {
		tbl := &catalog.Table{
			Rel: &ast.TableName{
				Name: tableName,
			},
		}

		pragmaStmt, _, err := a.conn.Prepare(fmt.Sprintf("PRAGMA table_info('%s')", tableName))
		if err != nil {
			return nil, fmt.Errorf("pragma table_info for %s: %w", tableName, err)
		}

		for pragmaStmt.Step() {
			// PRAGMA table_info returns: cid, name, type, notnull, dflt_value, pk
			colName := pragmaStmt.ColumnText(1)
			colType := pragmaStmt.ColumnText(2)
			notNull := pragmaStmt.ColumnInt(3) != 0

			tbl.Columns = append(tbl.Columns, &catalog.Column{
				Name:      colName,
				Type:      ast.TypeName{Name: normalizeType(colType)},
				IsNotNull: notNull,
			})
		}
		pragmaStmt.Close()

		mainSchema.Tables = append(mainSchema.Tables, tbl)