在一个事务中用golang进行多个查询的惯用方式

Question

I'm currently struggling (my 2nd day) to find the best way to do multiple queries and was wondering if you know a solution.

I have an open *sql.DB Connection, named myDb and use the go-sql-driver

func TruncateGalleryImport() error {

    s := make([]string, 0)

    s = append(s, "TRUNCATE TABLE add_map")
    s = append(s, "TRUNCATE TABLE album")
    s = append(s, "TRUNCATE TABLE album_permission")
    s = append(s, "TRUNCATE TABLE album_view")
    s = append(s, "TRUNCATE TABLE album_watch")
    s = append(s, "TRUNCATE TABLE media")
    s = append(s, "TRUNCATE TABLE media_user_view")
    s = append(s, "TRUNCATE TABLE media_view")
    s = append(s, "TRUNCATE TABLE media_watch")
    s = append(s, "TRUNCATE TABLE private_map")
    s = append(s, "TRUNCATE TABLE attachment")
    s = append(s, "TRUNCATE TABLE attachment_data")

    for _, q := range s {
        _, err := myDb.Exec(q)
        if err != nil {
            return err
        }
    }

    return nil
}

Is it possible to commit all the above queries together using only one transaction?

Cheers

Answer 1

Use a Transaction, like this (see the comments in the code):

func TruncateGalleryImport() error {
    s := make([]string, 0)

    s = append(s, "TRUNCATE TABLE add_map")
    s = append(s, "TRUNCATE TABLE album")
    s = append(s, "TRUNCATE TABLE album_permission")
    s = append(s, "TRUNCATE TABLE album_view")
    s = append(s, "TRUNCATE TABLE album_watch")
    s = append(s, "TRUNCATE TABLE media")
    s = append(s, "TRUNCATE TABLE media_user_view")
    s = append(s, "TRUNCATE TABLE media_view")
    s = append(s, "TRUNCATE TABLE media_watch")
    s = append(s, "TRUNCATE TABLE private_map")
    s = append(s, "TRUNCATE TABLE attachment")
    s = append(s, "TRUNCATE TABLE attachment_data")

    // Get new Transaction. See http://golang.org/pkg/database/sql/#DB.Begin
    txn, err := myDb.Begin()

    if err != nil {
        return err
    }

    defer func() {
        // Rollback the transaction after the function returns.
        // If the transaction was already commited, this will do nothing.
        _ = txn.Rollback()
    }()

    for _, q := range s {
        // Execute the query in the transaction.
        _, err := txn.Exec(q)

        if err != nil {
            return err
        }
    }

    // Commit the transaction.
    return txn.Commit()
}