func getApplicationDeployments(db *sql.DB, a *models.Application, limit int) ([]*models.Deployment, error) {
deployments := []*models.Deployment{}
rows, err := db.Query(applicationDeploymentsStmt, a.Name, limit)
if err != nil {
return deployments, err
}
defer rows.Close()
for rows.Next() {
var state string
d := &models.Deployment{}
err = rows.Scan(&d.Id, &d.UserId, &d.TargetName, &d.CommitSha, &d.Branch, &d.Comment, &state, &d.CreatedAt)
if err != nil {
return deployments, err
}
d.State = models.DeploymentState(state)
deployments = append(deployments, d)
}
if err := rows.Err(); err != nil {
return deployments, err
}
return deployments, nil
}
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 GetHistoryPlayers(db *sql.DB, days int) ([]HistoryPlayers, error) {
var data []HistoryPlayers
var query string
switch days {
case 1:
query = "SELECT history_slots_used, history_date FROM history WHERE history_date > NOW() - INTERVAL 1 DAY"
case 7:
query = "SELECT a.history_slots_used, a.history_date FROM (SELECT history_slots_used, history_date, DATE_FORMAT(history_date, '%p%d%m%y') AS g FROM history WHERE history_date > NOW() - INTERVAL 1 WEEK GROUP BY g) AS a"
case 30:
query = "SELECT a.history_slots_used, a.history_date FROM (SELECT history_slots_used, history_date, DATE_FORMAT(history_date, '%d%m%y') AS g FROM history WHERE history_date > NOW() - INTERVAL 1 MONTH GROUP BY g) AS a"
case 365:
query = "SELECT a.history_slots_used, a.history_date FROM (SELECT history_slots_used, history_date, DATE_FORMAT(history_date, '%u%y') AS g FROM history WHERE history_date > NOW() - INTERVAL 1 YEAR GROUP BY g) AS a"
}
rows, err := db.Query(query)
if err != nil {
return data, err
}
defer rows.Close()
for rows.Next() {
var tmp HistoryPlayers
if err := rows.Scan(&tmp.Total, &tmp.Date); err != nil {
return data, err
}
data = append(data, tmp)
}
return data, nil
}
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 do(db *sql.DB, sql string) {
_, err := db.Exec(sql)
if err == nil {
return
}
panic(fmt.Sprintf("Error %v running SQL: %s", err, sql))
}
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 (store *MysqlStore) ObservationsAutour(db *sql.DB, x int, y int, z int, dist int, trollId int, amis []int, withTresors bool) ([]*Observation, error) {
sql := "select auteur, num, date, type, nom, x, y, z from observation where"
sql += " x>" + strconv.Itoa(x-dist-1) + " and x<" + strconv.Itoa(x+dist+1)
sql += " and y>" + strconv.Itoa(y-dist-1) + " and y<" + strconv.Itoa(y+dist+1)
sql += " and z>" + strconv.Itoa(z-dist/2-1) + " and z<" + strconv.Itoa(z+dist/2+1)
if !withTresors {
sql += " and type<>'tresor'"
}
sql += " and auteur in (" + strconv.Itoa(trollId)
for _, id := range amis {
sql += "," + strconv.Itoa(id)
}
sql += ") order by type, num, date desc"
rows, err := db.Query(sql)
observations := make([]*Observation, 0, 20)
for rows.Next() {
r := new(Observation)
err = rows.Scan(&r.Auteur, &r.Num, &r.Date, &r.Type, &r.Nom, &r.X, &r.Y, &r.Z)
if err != nil {
return nil, err
}
if len(observations) > 0 && r.Num == observations[len(observations)-1].Num { // dédoublonnage
continue
}
observations = append(observations, r)
}
rows.Close()
return observations, nil
}
func AllAgents(db *sql.DB, simid []byte, proto string) (ags []AgentInfo, err error) {
s := `SELECT AgentId,Kind,Spec,Prototype,ParentId,EnterTime,ExitTime,Lifetime FROM
Agents
WHERE Agents.SimId = ?`
var rows *sql.Rows
if proto != "" {
s += ` AND Agents.Prototype = ?`
rows, err = db.Query(s, simid, proto)
} else {
rows, err = db.Query(s, simid)
}
if err != nil {
return nil, err
}
for rows.Next() {
ai := AgentInfo{}
var exit sql.NullInt64
if err := rows.Scan(&ai.Id, &ai.Kind, &ai.Impl, &ai.Proto, &ai.Parent, &ai.Enter, &exit, &ai.Lifetime); err != nil {
return nil, err
}
if !exit.Valid {
exit.Int64 = -1
}
ai.Exit = int(exit.Int64)
ags = append(ags, ai)
}
if err := rows.Err(); err != nil {
return nil, err
}
return ags, nil
}
// NewLedgerClosePump starts a background proc that continually watches the
// history database provided. The watch is stopped after the provided context
// is cancelled.
//
// Every second, the proc spawned by calling this func will check to see
// if a new ledger has been imported (by ruby-horizon as of 2015-04-30, but
// should eventually end up being in this project). If a new ledger is seen
// the the channel returned by this function emits
func NewLedgerClosePump(ctx context.Context, db *sql.DB) <-chan time.Time {
result := make(chan time.Time)
go func() {
var lastSeenLedger int32
for {
select {
case <-time.After(1 * time.Second):
var latestLedger int32
row := db.QueryRow("SELECT MAX(sequence) FROM history_ledgers")
err := row.Scan(&latestLedger)
if err != nil {
log.Warn(ctx, "Failed to check latest ledger", err)
break
}
if latestLedger > lastSeenLedger {
log.Debugf(ctx, "saw new ledger: %d, prev: %d", latestLedger, lastSeenLedger)
lastSeenLedger = latestLedger
result <- time.Now()
}
case <-ctx.Done():
log.Info(ctx, "canceling ledger pump")
return
}
}
}()
return result
}
func DeployCumulative(db *sql.DB, simid []byte, proto string) (xys []XY, err error) {
sql := `SELECT Time, IFNULL(Count, 0) FROM
TimeList LEFT JOIN
(SELECT ti.Time AS Timestep,COUNT(*) AS Count FROM
TimeList AS ti
JOIN Agents AS ag ON (ti.Time >= ag.EnterTime) AND (ag.ExitTime >= ti.Time OR ag.ExitTime IS NULL)
WHERE
ti.SimId = ag.SimId
AND ag.SimId = ?
AND ag.Prototype = ?
GROUP BY ti.Time
ORDER BY ti.Time) AS foo ON foo.Timestep = TimeList.Time;`
rows, err := db.Query(sql, simid, proto)
if err != nil {
return nil, err
}
for rows.Next() {
xy := XY{}
if err := rows.Scan(&xy.X, &xy.Y); err != nil {
return nil, err
}
xys = append(xys, xy)
}
if err := rows.Err(); err != nil {
return nil, err
}
return xys, nil
}
func InvSeries(db *sql.DB, simid []byte, agent int, iso int) (xys []XY, err error) {
sql := `SELECT ti.Time,SUM(cmp.MassFrac * inv.Quantity) FROM (
Compositions AS cmp
INNER JOIN Inventories AS inv ON inv.QualId = cmp.QualId
INNER JOIN TimeList AS ti ON (ti.Time >= inv.StartTime AND ti.Time < inv.EndTime)
) WHERE (
inv.SimId = ? AND inv.SimId = cmp.SimId AND ti.SimId = inv.SimId
AND inv.AgentId = ? AND cmp.NucId = ?
) GROUP BY ti.Time,cmp.NucId;`
rows, err := db.Query(sql, simid, agent, iso)
if err != nil {
return nil, err
}
for rows.Next() {
xy := XY{}
if err := rows.Scan(&xy.X, &xy.Y); err != nil {
return nil, err
}
xys = append(xys, xy)
}
if err := rows.Err(); err != nil {
return nil, err
}
return xys, nil
}
func (store *MysqlStore) SearchObservations(db *sql.DB, tok string, trollId int, amis []int) ([]*Observation, error) {
sql := "select auteur, num, date, type, nom, x, y, z from observation where"
if num, _ := strconv.Atoi(tok); num != 0 {
sql += " num=" + tok
} else {
sql += " nom like '%" + tok + "%'"
}
sql += " and auteur in (" + strconv.Itoa(trollId)
for _, id := range amis {
sql += "," + strconv.Itoa(id)
}
sql += ") order by num, date desc limit 100"
rows, err := db.Query(sql)
observations := make([]*Observation, 0, 20)
for rows.Next() {
r := new(Observation)
err = rows.Scan(&r.Auteur, &r.Num, &r.Date, &r.Type, &r.Nom, &r.X, &r.Y, &r.Z)
if err != nil {
return nil, err
}
if len(observations) > 0 && r.Num == observations[len(observations)-1].Num { // dédoublonnage
continue
}
observations = append(observations, r)
}
rows.Close()
return observations, nil
}
// 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
}
// 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
}
// WithTransaction begins a transaction on the given database connection, calls
// 'f' with the transaction as a parameter, then ensures the transaction is
// committed if 'f' completes successfully or rolled back in the event of an
// error. If an error occurs when committing or rolling back the transaction, a
// 'FailedCloseError' is returned.
func WithTransaction(db *sql.DB, f WithTransactionFunc) (err error) {
// Begin transaction
tx, err := db.Begin()
if err != nil {
return err
}
// Run the passed function
err = f(tx)
var closeErr error
var what string
// Commit or rollback the transaction
if err != nil {
closeErr = tx.Rollback()
what = "roll back transaction"
} else {
closeErr = tx.Commit()
what = "commit transaction"
}
// If committing/rolling back the transaction caused an error, return a
// FailedCloseError
if closeErr != nil {
err = &FailedCloseError{what, closeErr, err}
}
return err
}
func truncate(db *sql.DB) {
query := "TRUNCATE TABLE user"
_, err := db.Exec(query)
if err != nil {
log.Fatal("truncate error: ", err)
}
}
func getUsers(db *sql.DB) []User {
q := `
SELECT
ID, Name, Phone, Created
FROM
users
ORDER BY datetime(Created) DESC
`
rows, err := db.Query(q)
if err != nil {
log.Fatalln(err)
}
defer rows.Close()
var users []User
for rows.Next() {
user := User{}
err = rows.Scan(&user.ID, &user.Name, &user.Phone, &user.Created)
if err != nil {
log.Fatalln(err)
}
users = append(users, user)
}
return users
}
// postgres does not have REPLACE INTO (upsert), so we use that custom
// one for Set operations instead
func altSet(db *sql.DB, key, value string) error {
r, err := db.Query("SELECT replaceinto($1, $2)", key, value)
if err != nil {
return err
}
return r.Close()
}
// as per http://www.mssqltips.com/sqlservertip/2563/understanding-the-sql-server-select-version-command/
func serverVersion(db *sql.DB) (sqlVersion, sqlPartNumber, osVersion string, err error) {
var v string
if err = db.QueryRow("select @@version").Scan(&v); err != nil {
return "", "", "", err
}
a := strings.SplitN(v, "\n", -1)
if len(a) < 4 {
return "", "", "", errors.New("SQL Server version string must have at least 4 lines: " + v)
}
for i := range a {
a[i] = strings.Trim(a[i], " \t")
}
l1 := strings.SplitN(a[0], "-", -1)
if len(l1) != 2 {
return "", "", "", errors.New("SQL Server version first line must have - in it: " + v)
}
i := strings.Index(a[3], " on ")
if i < 0 {
return "", "", "", errors.New("SQL Server version fourth line must have 'on' in it: " + v)
}
sqlVersion = l1[0] + a[3][:i]
osVersion = a[3][i+4:]
sqlPartNumber = strings.Trim(l1[1], " ")
l12 := strings.SplitN(sqlPartNumber, " ", -1)
if len(l12) < 2 {
return "", "", "", errors.New("SQL Server version first line must have space after part number in it: " + v)
}
sqlPartNumber = l12[0]
return sqlVersion, sqlPartNumber, osVersion, nil
}
func QueryRowSql(db *sql.DB, sqlStmt string, params ...interface{}) (row *sql.Row) {
if db == nil {
return
}
row = db.QueryRow(sqlStmt, params...)
return
}
func DataCheck(qry string, conn *sql.DB) bool {
// conn, err := connect.GetJanusCon()
// if err != nil {
// panic(err)
// }
// defer conn.Close()
rowscheck, err := conn.Query(qry)
if err != nil {
log.Printf(`Error with "%s": %s`, qry, err)
}
len := 0
for rowscheck.Next() {
len = len + 1
}
// conn.Close()
if len > 0 {
return true
} else {
return false
}
}
func QuerySql(db *sql.DB, sqlStmt string, params ...interface{}) (rows *sql.Rows) {
if db == nil {
return
}
rows, _ = db.Query(sqlStmt, params...)
return
}
func runQuery(ctx context.Context, db *sql.DB, query string) (*sql.Rows, error) {
done := make(chan struct{})
var (
rows *sql.Rows
errMsg error
)
go func() {
for {
rs, err := db.Query(query)
if err != nil {
errMsg = err
time.Sleep(time.Second)
continue
} else {
rows = rs
errMsg = nil
done <- struct{}{}
break
}
}
}()
select {
case <-done:
return rows, errMsg
case <-ctx.Done():
return nil, fmt.Errorf("runQuery %s timed out with %v / %v", query, ctx.Err(), errMsg)
}
}
func QuerySoundAlikeProduct(db *sql.DB, accountId int64, inProduct *Product) (product *Product) {
s := []string{}
s = append(s, "SELECT")
s = append(s, "account_id, pid, name, product_url, image_url, unit_cost,")
s = append(s, "unit_price, margin, margin_rate")
s = append(s, "FROM product")
s = append(s, "WHERE account_id = $1")
s = append(s, "AND difference(name, $2) >= 3")
s = append(s, "LIMIT 1")
query := strings.Join(s, " ")
row := db.QueryRow(query, accountId, inProduct.Name)
product = &Product{}
err := row.Scan(&product.AccountId, &product.Pid, &product.Name, &product.ProductUrl, &product.ImageUrl,
&product.UnitCost, &product.UnitPrice, &product.Margin, &product.MarginRate)
if err != nil {
if err == sql.ErrNoRows {
product = nil
} else {
panic(err)
}
}
return
}
func getDeploymentLogEntries(db *sql.DB, d *models.Deployment) ([]*deploy.LogEntry, error) {
entries := []*deploy.LogEntry{}
rows, err := db.Query(deploymentLogEntriesStmt, d.Id)
if err != nil {
return entries, err
}
defer rows.Close()
for rows.Next() {
var entryType string
e := &deploy.LogEntry{}
err = rows.Scan(&e.Id, &e.DeploymentId, &entryType, &e.Origin, &e.Message, &e.Timestamp)
if err != nil {
return entries, err
}
e.EntryType = deploy.LogEntryType(entryType)
entries = append(entries, e)
}
if err := rows.Err(); err != nil {
return entries, err
}
return entries, nil
}
func HasProductBeenPurchasedByPerson(db *sql.DB, accountId int64, person *Person, product *Product) (seen bool) {
s := []string{}
s = append(s, "SELECT 'x'")
s = append(s, "FROM user_product_purchases u JOIN product p ON (")
s = append(s, "u.account_id = p.account_id AND")
s = append(s, "u.pid = p.pid)")
s = append(s, "WHERE u.account_id = $1 AND u.monetate_id = $2 AND u.pid = $3")
query := strings.Join(s, " ")
row := db.QueryRow(query, accountId, person.MonetateId, product.Pid)
var foo string
err := row.Scan(&foo)
if err == nil {
seen = true
} else {
if err == sql.ErrNoRows {
seen = false
} else {
panic(err)
}
}
return
}
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 SelectBodys(db *sql.DB, ids []int) (map[int]string, error) {
m := map[int]string{}
strs := make([]string, len(ids))
for i, id := range ids {
strs[i] = fmt.Sprintf("%d", id)
}
clause := strings.Join(strs, ",")
rows, err := db.Query(
fmt.Sprintf(
"SELECT id, body FROM reviews WHERE id IN (%s)",
clause,
),
)
if err != nil {
return m, err
}
for rows.Next() {
var id int
var body string
if err := rows.Scan(&id, &body); err != nil {
return m, fmt.Errorf("SELECT error: %s", err)
}
m[id] = body
}
return m, nil
}
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 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)
}
}