zombiezen/go-sqlite

The Action struct provides detailed context about the specific SQL operation being authorized. You can use various methods to extract metadata depending on the OpType of the action:

• Type(): Returns the OpType (e.g., OpRead, OpInsert, OpDropTable).
• Database(): Returns the database name (e.g., main, temp).
• Table(): Returns the name of the table involved.
• Column(): Returns the column name (relevant for OpRead and OpUpdate).
• Index(): Returns the index name.
• View(): Returns the view name.
• Trigger(): Returns the name of the trigger or the accessor (the inner-most trigger/view responsible for the access).
• Pragma() / PragmaArg(): Returns details for OpPragma actions.
• Operation(): Returns transaction/savepoint keywords (BEGIN, COMMIT, etc.).
• File(): Returns the filename for OpAttach.
• Module(): Returns the module name for virtual tables.
• Savepoint(): Returns the savepoint name.

// Example of inspecting an action
auth := sqlite.AuthorizeFunc(func(action sqlite.Action) sqlite.AuthResult {
    if action.Type() == sqlite.OpRead {
        fmt.Printf("Reading from table: %s, column: %s\n", action.Table(), action.Column())
    }
    return sqlite.AuthResultOK
})

The BestIndex method is used by SQLite to optimize queries.

Inputs (IndexInputs):

• Constraints: A list of IndexConstraint objects representing the WHERE clause.
• OrderBy: A list of IndexOrderBy objects representing the ORDER BY clause.
• ColumnsUsed: A bitmask of columns used by the statement.

Outputs (IndexOutputs):

• ConstraintUsage: Maps IndexInputs.Constraints to VTableCursor.Filter arguments via ArgvIndex.
• ID: An IndexID (number or string) returned to Filter to identify the chosen strategy.
• OrderByConsumed: true if the output is already sorted.
• EstimatedCost: A float6.a representing the cost (e.g., $N$ for linear scan, $\log(N)$ for binary search).
• EstimatedRows: An estimate of the number of rows returned.

type IndexInputs struct {
	Constraints []IndexConstraint
	OrderBy    []IndexOrderBy
	ColumnsUsed uint64
}

type IndexOutputs struct {
	ConstraintUsage  []IndexConstraintUsage
	ID               IndexID
	OrderByConsumed  bool
	EstimatedCost    float64
	EstimatedRows    int64
	UseZeroEstimates bool
	IndexFlags       IndexFlags
}

When applying a changeset, conflicts can occur. You resolve these by providing a ConflictHandler to ApplyChangeset.

Conflict Types (ConflictType):

• ChangesetData: Data in the changeset conflicts with data in the database.
• ChangesetNotFound: The row expected by the changeset was not found.
• ChangesetConflict: A conflict that is not covered by other types.
• ChangesetConstraint: A constraint violation occurred.
• ChangesetForeignKey: A foreign key constraint violation occurred.

Conflict Actions (ConflictAction):

• ChangesetOmit: The change that caused the conflict is ignored, and the session continues to the next change.
• ChangesetAbort: All changes applied so far are rolled back, and ApplyChangeset returns an error.
• ChangesetReplace:
  • If type is ChangesetData: The conflicting row is updated or deleted.
  • If type is ChangesetConflict: The conflicting row is removed, and a second attempt to apply the change is made. If the second attempt fails, the original row is restored.
  • For other types: ApplyChangeset rolls back all changes and returns a ResultMisuse error.

The Backup API allows for copying data between two SQLite database connections (online backup). This is useful for creating snapshots of a live database without stopping writes.

Workflow

1. Initialize: Use NewBackup to create a backup object. You must specify the destination connection (dst), the source connection (src), and their respective database names (e.g., "main", "temp", or an attached database name).
2. Execute: Call Step(n) repeatedly to copy pages.
   • If n is positive, it copies up to n pages.
   • If n is negative, it copies all remaining pages.
   • Step returns more bool. If more is true, there are still pages to copy.
   • If Step returns an error with ResultBusy or ResultLocked, it is a temporary error and you should retry the operation.
3. Monitor: Use Remaining() to see how many pages are left and PageCount() to see the total pages in the source.
4. Cleanup: You must call Close() to release resources. If the backup is not finished, Close() will roll back any active write transaction on the destination database.

// Example: Copying 'main' from src to 'main' in dst
backup, err := sqlite.NewBackup(dst, "main", src, "main")
if err != nil {
	log.Fatal(err)
}
defer backup.Close()

for {
	more, err := backup.Step(-1) // Copy all remaining pages
	if err != nil {
		// Handle retriable errors like SQLITE_BUSY
		break
	}
	if !more {
		break
	}
}