Golang sqlite.Conn類代碼示例

// HACK to execute a script file. Python3 has a helper executescript(..).
// Don't know the way to do it in the C API. Maybe sqlite3_complete(..) helps
// by extending the current substring until the following semicolon, until
// it says the query is complete (you can also have semicolons in strings).
// Execute and repeat. Maybe.
// No support in gosqlite, but for now our create-tables script is simple
// and we can execute substrings up to each semicolon.
func hack_create_tables(conn *sqlite.Conn) error {
	bytes, err := ioutil.ReadFile(createTablesFile)
	if err != nil {
		return err
	}

	chars := []rune(strings.TrimSpace(string(bytes)))
	for i := 0; i < len(chars); {
		j := i
		for ; j < len(chars)-1; j++ {
			if chars[j] == ';' {
				break
			}
		}

		// now 'j' is either the first semicolon or the very last rune
		query := chars[i : j+1]
		err = conn.Exec(string(query))
		if err != nil {
			return err
		}

		i = j + 1
	}

	return nil
}

func create_tables_if_missing(conn *sqlite.Conn) error {
	stmt, err := conn.Prepare("SELECT name FROM sqlite_master " +
		"WHERE name = 'accounts';")
	if err != nil {
		return err
	}
	defer func() {
		err := stmt.Finalize()
		if err != nil {
			log.Println(err)
		}
	}()

	err = stmt.Exec()
	if err != nil {
		return err
	}

	found := false
	for stmt.Next() {
		found = true
	}

	if found {
		return nil
	}
	return hack_create_tables(conn)
}

// ChkbookEntries returns a Stream that emits all the entries in a
// checkbook ordered by most recent to least recent. conn is the sqlite
// connection; acctId is the id of the account for which to print entries.
// If acctId does not match a valid account, ChkbookEntries will return an
// error and nil for the Stream. If caller does not exhaust returned
// Stream, it must call Close on it to free up resources.
func ChkbkEntries(conn *sqlite.Conn, acctId int) (functional.Stream, error) {
	stmt, err := conn.Prepare("select balance from balances where acct_id = ?")
	if err != nil {
		return nil, err
	}
	if err = stmt.Exec(acctId); err != nil {
		stmt.Finalize()
		return nil, err
	}
	if !stmt.Next() {
		stmt.Finalize()
		return nil, errors.New("No balance")
	}
	var bal int64
	if err = stmt.Scan(&bal); err != nil {
		stmt.Finalize()
		return nil, err
	}
	stmt.Finalize()
	stmt, err = conn.Prepare("select date, name, amount from entries where acct_id = ? order by date desc")
	if err != nil {
		return nil, err
	}
	if err = stmt.Exec(acctId); err != nil {
		stmt.Finalize()
		return nil, err
	}
	rowStream := functional.ReadRows(CloserStmt{stmt})
	return functional.Filter(&BalanceFilterer{bal}, rowStream), nil
}

func updateVisit(db *sqlite.Conn, req *request, rsp *browseRsp, ok bool) error {
	success := 1
	if !ok {
		success = 0
	}

	err := db.Exec(`
		UPDATE visit SET success=?1, cookies=?2, resources=?3, screenshot=?4, stdout=?5, stderr=?6
		WHERE url=?7`,
		success, rsp.cookies, rsp.resources, rsp.screenshot, rsp.stdout, rsp.stderr, req.url)
	return err
}

// exits program on failure
func setup(db *sqlite.Conn) error {
	statements := []string{
		"create table if not exists tool (node_id int, name text, status int)",
		"create table if not exists permission (node_id int, timestamp datetime default (CURRENT_TIMESTAMP), card_id text, granter_card_id text, is_maintainer boolean default 0)",
		"create unique index if not exists permission_node_card on permission (node_id, card_id)",
		"create table if not exists tool_usage (node_id int, timestamp datetime default (CURRENT_TIMESTAMP), status int, card_id text)",
		"create table if not exists case_alert (node_id int, timestamp datetime default (CURRENT_TIMESTAMP), status int)",
	}

	for _, stmt := range statements {
		err := db.Exec(stmt)
		if err != nil {
			log("error executing:", stmt)
			return err
		}
	}
	return nil
}

func visitsNeeded(db *sqlite.Conn) ([]*request, error) {
	var reqs []*request
	s, err := db.Prepare("SELECT url, rank FROM visit WHERE NOT success=1")
	if err != nil {
		return nil, err
	}

	if err := s.Exec(); err != nil {
		return nil, err
	}

	var url string
	var rank int
	for s.Next() {
		if err := s.Scan(&url, &rank); err != nil {
			return nil, err
		}

		reqs = append(reqs, &request{url: url, rank: rank})
	}

	return reqs, nil
}

// ChkbookEntries returns a Generator that emits all the entries in a
// checkbook ordered by most recent to least recent. conn is the sqlite
// connection; acctId is the id of the account for which to print entries.
// If acctId does not match a valid account, ChkbookEntries will return an
// error and nil for the Generator. If caller does not exhaust returned
// Generator, it must call Close on it to free up resources.
func ChkbkEntries(conn *sqlite.Conn, acctId int) (functional.Generator, error) {
	stmt, err := conn.Prepare("select balance from balances where acct_id = ?")
	if err != nil {
		return nil, err
	}
	if err = stmt.Exec(acctId); err != nil {
		stmt.Finalize()
		return nil, err
	}
	if !stmt.Next() {
		stmt.Finalize()
		return nil, errors.New("No balance")
	}
	var bal int64
	if err = stmt.Scan(&bal); err != nil {
		stmt.Finalize()
		return nil, err
	}
	stmt.Finalize()
	stmt, err = conn.Prepare("select date, name, amount from entries where acct_id = ? order by date desc")
	if err != nil {
		return nil, err
	}
	if err = stmt.Exec(acctId); err != nil {
		stmt.Finalize()
		return nil, err
	}
	return functional.NewGenerator(func(emitter functional.Emitter) {
		rowStream := functional.ReadRows(stmt)
		for ptr := emitter.EmitPtr(); ptr != nil && rowStream.Next(ptr); ptr = emitter.EmitPtr() {
			entry := ptr.(*Entry)
			entry.Balance = bal
			bal += entry.Amount
		}
		stmt.Finalize()
	}), nil
}