func runExec(ctx context.Context, db *sql.DB, query string) error {
done := make(chan struct{})
var (
errMsg error
)
go func() {
for {
if _, err := db.Exec(query); err != nil {
errMsg = err
time.Sleep(time.Second)
continue
} else {
errMsg = nil
done <- struct{}{}
break
}
}
}()
select {
case <-done:
return errMsg
case <-ctx.Done():
return fmt.Errorf("runExec %s timed out with %v / %v", query, ctx.Err(), errMsg)
}
}
func dbUpdateFromV8(db *sql.DB) error {
stmt := `
UPDATE certificates SET fingerprint = replace(fingerprint, " ", "");
INSERT INTO schema (version, updated_at) VALUES (?, strftime("%s"));`
_, err := db.Exec(stmt, 9)
return err
}
func v1CompatUpdateDatabase(
ctx *grader.Context,
db *sql.DB,
run *grader.RunInfo,
) {
_, err := db.Exec(
`UPDATE
Runs
SET
status = 'ready', verdict = ?, runtime = ?, memory = ?, score = ?,
contest_score = ?, judged_by = ?
WHERE
run_id = ?;`,
run.Result.Verdict,
run.Result.Time,
run.Result.Memory,
run.Result.Score,
run.Result.ContestScore,
run.Result.JudgedBy,
run.ID,
)
if err != nil {
ctx.Log.Error("Error updating the database", "err", err, "run", run)
}
}
func do(db *sql.DB, sql string) {
_, err := db.Exec(sql)
if err == nil {
return
}
panic(fmt.Sprintf("Error %v running SQL: %s", err, sql))
}
// PostgreSQL INSERT Handler
func psqlInsertHandler(db *sql.DB) http.Handler {
return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
_, err := db.Exec("INSERT INTO models (uid, data) VALUES($1, $2)", "c8cb5c0b-a42e-4490-8e3d-f617f439dc27", "data")
if err != nil {
http.Error(w, http.StatusText(500), 500)
return
}
/*
fmt.Println("# Deleting")
stmt, err = db.Prepare("delete from userinfo where uid=$1")
checkErr(err)
res, err = stmt.Exec(lastInsertId)
checkErr(err)
affect, err = res.RowsAffected()
checkErr(err)
fmt.Println(affect, "rows changed")
*/
w.Header().Set("Content-Type", "application/json")
//json.NewEncoder(w).Encode(users)
})
}
// supprime la vue de trollID puis sauvegarde des observations reçues par SOAP de MH, observées juste maintenant par trollId
func (store *MysqlStore) CleanAndSaveSoapItems(db *sql.DB, trollId int, items []*SoapItem) error {
seconds := time.Now()
sql := "delete from observation where auteur=?"
_, err := db.Exec(sql, trollId)
if err != nil {
return err
}
sql = "insert into observation"
sql += " (auteur, num, date, type, nom, x, y, z)"
sql += " values ( ?, ?, ?, ?, ?, ?, ?, ?)"
for _, i := range items {
var t string
if i.Type == "TROLL" {
t = "troll"
} else if i.Type == "MONSTRE" {
t = "monstre"
} else if i.Type == "LIEU" {
t = "lieu"
} else if i.Type == "TRESOR" {
t = "tresor"
} else {
continue
}
_, err = db.Exec(sql, trollId, i.Numero, seconds, t, i.Nom, i.PositionX, i.PositionY, i.PositionN)
if err != nil {
return err
}
}
return nil
}
func createKVTable(t *testing.T, sqlDB *gosql.DB, numRows int) {
// Fix the column families so the key counts don't change if the family
// heuristics are updated.
if _, err := sqlDB.Exec(`
CREATE DATABASE t;
CREATE TABLE t.kv (k INT PRIMARY KEY, v INT, FAMILY (k), FAMILY (v));
CREATE INDEX foo on t.kv (v);
`); err != nil {
t.Fatal(err)
}
// Bulk insert.
var insert bytes.Buffer
if _, err := insert.WriteString(fmt.Sprintf(`INSERT INTO t.kv VALUES (%d, %d)`, 0, numRows-1)); err != nil {
t.Fatal(err)
}
for i := 1; i < numRows; i++ {
if _, err := insert.WriteString(fmt.Sprintf(` ,(%d, %d)`, i, numRows-i)); err != nil {
t.Fatal(err)
}
}
if _, err := sqlDB.Exec(insert.String()); err != nil {
t.Fatal(err)
}
}
func do(db *sql.DB, sql string) {
_, err := db.Exec(sql)
if err == nil {
return
}
log.Fatalf("Error %v running SQL: %s", err, sql)
}
// sauvegarde un nouveau partage (à l'état de proposition de a pour b)
func (store *MysqlStore) InsertPartage(db *sql.DB, trollA int, trollB int) error {
sql := "insert ignore into"
sql += " partage (troll_a, troll_b)"
sql += " values ( ?, ?)"
_, err := db.Exec(sql, trollA, trollB)
return err
}
func addToMBTile(tile Tile, db *sql.DB) error {
_, err := db.Exec("insert into tiles (zoom_level, tile_column, tile_row, tile_data) values (?, ?, ?, ?);", tile.z, tile.x, tile.flipped_y(), tile.Content)
if err != nil {
return err
}
return nil
}
func UpdateHook(db *sql.DB, query string, v *Hook) error {
args := SliceHook(v)[1:]
args = append(args, v.ID)
_, err := db.Exec(query, args...)
return err
}
// createTestTable tries to create a new table named based on the passed in id.
// It is designed to be synced with a number of concurrent calls to this
// function. Before starting, it first signals a done on the start waitgroup
// and then will block until the signal channel is closed. Once closed, it will
// proceed to try to create the table. Once the table creation is finished (be
// it successful or not) it signals a done on the end waitgroup.
func createTestTable(
t *testing.T,
tc *testcluster.TestCluster,
id int,
db *gosql.DB,
wgStart *sync.WaitGroup,
wgEnd *sync.WaitGroup,
signal chan struct{},
completed chan int,
) {
defer wgEnd.Done()
wgStart.Done()
<-signal
tableSQL := fmt.Sprintf(`
CREATE TABLE IF NOT EXISTS "test"."table_%d" (
id INT PRIMARY KEY,
val INT
)`, id)
for {
if _, err := db.Exec(tableSQL); err != nil {
if testutils.IsSQLRetryableError(err) {
continue
}
t.Errorf("table %d: could not be created: %v", id, err)
return
}
completed <- id
break
}
}
func addChannelHistory(s *slack.Slack, db *sql.DB, channel, oldest string) (string, error) {
params := slack.NewHistoryParameters()
params.Oldest = oldest
params.Count = 1000
history, err := s.GetChannelHistory(channel, params)
if err != nil {
return "", err
}
for _, msg := range history.Messages {
if msg.SubType != "" {
continue
}
fmt.Printf("%s: %s\n", msg.User, msg.Text)
ts := timestampToTime(msg.Timestamp)
_, err := db.Exec(
`INSERT INTO messages(channel_id, user_id, text, timestamp, created_at, updated_at)
VALUES(?, ?, ?, ?, NOW(), NOW())
`,
channel, msg.User, msg.Text, ts.Format("2006-01-02 15:04:05"))
if err != nil {
return "", err
}
}
if len(history.Messages) > 0 {
return history.Messages[0].Timestamp, nil
}
return "", nil
}
func prepareDatabase(db *sql.DB) error {
_, err := db.Exec(fmt.Sprintf("CREATE TABLE IF NOT EXISTS %v (id text not null, applied_at timestamp not null)", tableName))
if err != nil {
return err
}
return nil
}
func insertTask(db *sql.DB, serializer format.Serializer, task *models.Task, malformedTaskDefinition bool) {
taskDefData, err := serializer.Marshal(logger, format.ENCRYPTED_PROTO, task.TaskDefinition)
Expect(err).NotTo(HaveOccurred())
if malformedTaskDefinition {
taskDefData = []byte("{{{{{{{{{{")
}
queryStr := `INSERT INTO tasks
(guid, domain, created_at, updated_at, first_completed_at, state,
cell_id, result, failed, failure_reason, task_definition)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
if test_helpers.UsePostgres() {
queryStr = test_helpers.ReplaceQuestionMarks(queryStr)
}
result, err := db.Exec(
queryStr,
task.TaskGuid,
task.Domain,
task.CreatedAt,
task.UpdatedAt,
task.FirstCompletedAt,
task.State,
task.CellId,
task.Result,
task.Failed,
task.FailureReason,
taskDefData,
)
Expect(err).NotTo(HaveOccurred())
Expect(result.RowsAffected()).NotTo(Equal(1))
}
// runBenchmarkBank mirrors the SQL performed by examples/sql_bank, but
// structured as a benchmark for easier usage of the Go performance analysis
// tools like pprof, memprof and trace.
func runBenchmarkBank(b *testing.B, db *sql.DB) {
if _, err := db.Exec(`CREATE DATABASE IF NOT EXISTS bank`); err != nil {
b.Fatal(err)
}
{
// Initialize the "accounts" table.
schema := `
CREATE TABLE IF NOT EXISTS bank.accounts (
id INT PRIMARY KEY,
balance INT NOT NULL
)`
if _, err := db.Exec(schema); err != nil {
b.Fatal(err)
}
if _, err := db.Exec("TRUNCATE TABLE bank.accounts"); err != nil {
b.Fatal(err)
}
var placeholders bytes.Buffer
var values []interface{}
for i := 0; i < *numAccounts; i++ {
if i > 0 {
placeholders.WriteString(", ")
}
fmt.Fprintf(&placeholders, "($%d, 0)", i+1)
values = append(values, i)
}
stmt := `INSERT INTO bank.accounts (id, balance) VALUES ` + placeholders.String()
if _, err := db.Exec(stmt, values...); err != nil {
b.Fatal(err)
}
}
b.ResetTimer()
b.RunParallel(func(pb *testing.PB) {
for pb.Next() {
from := rand.Intn(*numAccounts)
to := rand.Intn(*numAccounts - 1)
if from == to {
to = *numAccounts - 1
}
amount := rand.Intn(*maxTransfer)
update := `
UPDATE bank.accounts
SET balance = CASE id WHEN $1 THEN balance-$3 WHEN $2 THEN balance+$3 END
WHERE id IN ($1, $2) AND (SELECT balance >= $3 FROM bank.accounts WHERE id = $1)
`
if _, err := db.Exec(update, from, to, amount); err != nil {
if log.V(1) {
log.Warning(err)
}
continue
}
}
})
b.StopTimer()
}
func createTable(tableName *string, columnNames *[]string, db *sql.DB, verbose *bool) error {
var buffer bytes.Buffer
buffer.WriteString("CREATE TABLE IF NOT EXISTS " + (*tableName) + " (")
for i, col := range *columnNames {
var col_name string
reg := regexp.MustCompile(`[^a-zA-Z0-9]`)
col_name = reg.ReplaceAllString(col, "_")
if *verbose && col_name != col {
fmt.Fprintf(os.Stderr, "Column %x renamed to %s\n", col, col_name)
}
buffer.WriteString(col_name + " TEXT")
if i != len(*columnNames)-1 {
buffer.WriteString(", ")
}
}
buffer.WriteString(");")
_, err := db.Exec(buffer.String())
if err != nil {
log.Fatalln(err)
}
return err
}
func Test_deleting_an_image_cascades_on_related_tables(t *testing.T) {
var db *sql.DB
var err error
var count int
var statements string
db = createTestDb(t)
defer db.Close()
// Drop the image we just created.
statements = `DELETE FROM images;`
_, err = db.Exec(statements)
if err != nil {
t.Errorf("Error deleting image! %s", err)
}
// Make sure there are 0 images_aliases entries left.
statements = `SELECT count(*) FROM images_aliases;`
err = db.QueryRow(statements).Scan(&count)
if count != 0 {
t.Errorf("Deleting an image didn't delete the image alias association! There are %d left", count)
}
// Make sure there are 0 images_properties entries left.
statements = `SELECT count(*) FROM images_properties;`
err = db.QueryRow(statements).Scan(&count)
if count != 0 {
t.Errorf("Deleting an image didn't delete the related images_properties! There are %d left", count)
}
}
func (schema *Schema) GenerateSchema(db *sql.DB) error {
var sql_string string
sql_string = "DROP SCHEMA IF EXISTS `" + schema.Name + "`;\n\n"
sql_string += "CREATE SCHEMA IF NOT EXISTS `" + schema.Name + "`;\n\n"
sql_string += "USE `" + schema.Name + "`;\n\n"
for _, v := range schema.Tables {
sql_string += "DROP TABLE IF EXISTS `" + schema.Name + "`.`" + v.Name + "`;\n\n"
sql_string += "CREATE TABLE IF NOT EXISTS `" + schema.Name + "`.`" + v.Name + "` (\n"
// Make id Field
uid := v.Name + "_id"
sql_string += "\t`" + uid + "` INT NOT NULL AUTO_INCREMENT,\n"
for _, f := range v.Fields {
var fType string
if strings.ToLower(f.Type) == "string" {
fType = "VARCHAR(150)"
}
sql_string += "\t`" + f.Name + "` " + fType + " NULL,\n"
}
sql_string += "\tPRIMARY KEY (`" + uid + "`),\n"
sql_string += "\tUNIQUE INDEX `" + uid + "_UNIQUE` (`" + uid + "` ASC))\n"
sql_string += "ENGINE = InnoDB;"
}
_, err := db.Exec(sql_string)
return err
}
// Create the initial (current) schema for a given SQLite DB connection.
// This should stay indempotent.
func createDb(db *sql.DB) (err error) {
_, err = db.Exec(CURRENT_SCHEMA)
if err != nil {
return err
}
// To make the schema creation indempotent, only insert the schema version
// if there isn't one already.
latestVersion := dbGetSchema(db)
if latestVersion == 0 {
// There isn't an entry for schema version, let's put it in.
insertStmt := `INSERT INTO schema (version, updated_at) values (?, strftime("%s"));`
_, err = db.Exec(insertStmt, DB_CURRENT_VERSION)
if err != nil {
return err
}
}
err = dbProfileCreateDefault(db)
if err != nil {
return err
}
return dbProfileCreateMigratable(db)
}
// sauvegarde des observations reçues par SOAP de MH, observées juste maintenant par trollId
func (store *MysqlStore) SaveSoapItems(db *sql.DB, trollId int, items []SoapItem) error {
seconds := time.Now()
sql := "insert into observation"
sql += " (auteur, num, date, type, nom, x, y, z)"
sql += " values ( ?, ?, ?, ?, ?, ?, ?, ?)"
sql += " on duplicate key update date=?, nom=?, x=?, y=?, z=?"
for _, i := range items {
var t string
if i.Type == "TROLL" {
t = "troll"
} else if i.Type == "MONSTRE" {
t = "monstre"
} else if i.Type == "TRESOR" {
t = "tresor"
} else if i.Type == "LIEU" {
t = "lieu"
} else {
continue
}
_, err := db.Exec(sql, trollId, i.Numero, seconds, t, i.Nom, i.PositionX, i.PositionY, i.PositionN, seconds, i.Nom, i.PositionX, i.PositionY, i.PositionN)
if err != nil {
return err
}
}
return nil
}
// Unwrap gives access to underlying driver connection.
func Unwrap(db *sql.DB) *Conn {
_, err := db.Exec("unwrap")
if cerr, ok := err.(ConnError); ok {
return cerr.c
}
return nil
}
func truncate(db *sql.DB) {
query := "TRUNCATE TABLE user"
_, err := db.Exec(query)
if err != nil {
log.Fatal("truncate error: ", err)
}
}
func startBankTransfers(t testing.TB, stopper *stop.Stopper, sqlDB *gosql.DB, numAccounts int) {
const maxTransfer = 999
for {
select {
case <-stopper.ShouldQuiesce():
return // All done.
default:
// Keep going.
}
from := rand.Intn(numAccounts)
to := rand.Intn(numAccounts - 1)
for from == to {
to = numAccounts - 1
}
amount := rand.Intn(maxTransfer)
const update = `UPDATE bench.bank
SET balance = CASE id WHEN $1 THEN balance-$3 WHEN $2 THEN balance+$3 END
WHERE id IN ($1, $2)`
util.SucceedsSoon(t, func() error {
select {
case <-stopper.ShouldQuiesce():
return nil // All done.
default:
// Keep going.
}
_, err := sqlDB.Exec(update, from, to, amount)
return err
})
}
}
// Create database
func CreateDB(db *sql.DB, dbfile string) {
// Create the schema, if necessary
_, err := os.Stat(dbfile)
if os.IsNotExist(err) {
fmt.Println("create")
sqlStmt := `
create table networks(essid text not null primary key, inscope boolean not null default 0, cloaked boolean not null default 0,
first_seen datetime not null default (datetime(current_timestamp)), last_seen datetime not null default (datetime(current_timestamp)),
maxrate integer not null default 0, encryption text not null default('') check(encryption in ('', 'Open', 'WEP', 'WPA-Personal', 'WPA2-Personal', 'WPA-Enterprise', 'WPA2-Enterprise')));
delete from networks;
create table bssids(bssid text not null primary key, aptype text not null default '', network text not null default '', manuf text not null default '', channel integer not null default 0,
first_seen datetime not null default (datetime(current_timestamp)), last_seen datetime not null default (datetime(current_timestamp)),
atype integer not null default 0, rangeip text not null default '', netmaskip text not null default '', gatewayip text not null default '',
minpower integer not null default -99, maxpower integer not null default 0);
delete from bssids;
create table clients(mac text not null primary key, first_seen datetime not null default (datetime(current_timestamp)),
last_seen datetime not null default (datetime(current_timestamp)), power integer not null default -99, minpower integer not null default -99,
maxpower integer not null default 0, packets integer not null default 0);
delete from clients;
create table probes(id integer not null primary key, client integer not null, essid text not null, foreign key(client) references clients(id));
delete from probes;
create table credentials(id integer not null primary key, network integer not null, client integer not null, challenge text not null, response text not null,
foreign key(network) references networks(id), foreign key(client) references clients(id));
delete from credentials;
`
_, err := db.Exec(sqlStmt)
if err != nil {
log.Printf("%q: %s\n", err, sqlStmt)
}
}
}
// sauvegarde un nouveau compte.
func (store *MysqlStore) InsertCompte(db *sql.DB, c *Compte) error {
sql := "insert into"
sql += " compte (id, statut, mdp_restreint)"
sql += " values ( ?, ?, ?)"
_, err := db.Exec(sql, c.trollId, c.statut, c.mdpRestreint)
return err
}
func dbUpdateFromV13(db *sql.DB) error {
stmt := `
UPDATE containers_config SET key='volatile.base_image' WHERE key = 'volatile.baseImage';
INSERT INTO schema (version, updated_at) VALUES (?, strftime("%s"));`
_, err := db.Exec(stmt, 14)
return err
}
func cleanCloseTestDb(db *sql.DB, t *testing.T) {
for _, stmt := range cleanStmts {
_, err := db.Exec(stmt)
checkErr(t, err)
}
db.Close()
}
func dbUpdateFromV2(db *sql.DB) error {
stmt := `
CREATE TABLE IF NOT EXISTS containers_devices (
id INTEGER primary key AUTOINCREMENT NOT NULL,
container_id INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
type INTEGER NOT NULL default 0,
FOREIGN KEY (container_id) REFERENCES containers (id) ON DELETE CASCADE,
UNIQUE (container_id, name)
);
CREATE TABLE IF NOT EXISTS containers_devices_config (
id INTEGER primary key AUTOINCREMENT NOT NULL,
container_device_id INTEGER NOT NULL,
key VARCHAR(255) NOT NULL,
value TEXT,
FOREIGN KEY (container_device_id) REFERENCES containers_devices (id),
UNIQUE (container_device_id, key)
);
CREATE TABLE IF NOT EXISTS containers_profiles (
id INTEGER primary key AUTOINCREMENT NOT NULL,
container_id INTEGER NOT NULL,
profile_id INTEGER NOT NULL,
apply_order INTEGER NOT NULL default 0,
UNIQUE (container_id, profile_id),
FOREIGN KEY (container_id) REFERENCES containers(id) ON DELETE CASCADE,
FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS profiles (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
name VARCHAR(255) NOT NULL,
UNIQUE (name)
);
CREATE TABLE IF NOT EXISTS profiles_config (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
profile_id INTEGER NOT NULL,
key VARCHAR(255) NOT NULL,
value VARCHAR(255),
UNIQUE (profile_id, key),
FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS profiles_devices (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
profile_id INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
type INTEGER NOT NULL default 0,
UNIQUE (profile_id, name),
FOREIGN KEY (profile_id) REFERENCES profiles (id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS profiles_devices_config (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
profile_device_id INTEGER NOT NULL,
key VARCHAR(255) NOT NULL,
value TEXT,
UNIQUE (profile_device_id, key),
FOREIGN KEY (profile_device_id) REFERENCES profiles_devices (id)
);
INSERT INTO schema (version, updated_at) values (?, strftime("%s"));`
_, err := db.Exec(stmt, 3)
return err
}
func createSchemaUnlessExists(c *Config, db *sql.DB) error {
// If there's no schema name in the config, we don't need to create the schema.
if !strings.Contains(c.MigrationTable, ".") {
return nil
}
var exists bool
schema := strings.SplitN(c.MigrationTable, ".", 2)[0]
err := db.QueryRow(
`SELECT EXISTS(SELECT 1 FROM pg_catalog.pg_namespace WHERE nspname = $1)`,
schema,
).Scan(&exists)
if err != nil {
return err
}
if exists {
return nil
}
_, err = db.Exec(fmt.Sprintf(
"CREATE SCHEMA %s;",
pq.QuoteIdentifier(schema),
))
return err
}