func moveFile(db *postgres.DB, repo *data.FileRepo, f data.BackendFile, delete bool) error {
	b := repo.DefaultBackend()
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	stream, err := f.Backend.Open(tx, f.FileInfo, false)
	if err != nil {
		tx.Rollback()
		return err
	}
	if err := b.Put(tx, f.FileInfo, stream, false); err != nil {
		tx.Rollback()
		return err
	}
	if err := repo.SetBackend(tx, f.ID, b.Name()); err != nil {
		tx.Rollback()
		return err
	}
	if delete {
		if err := f.Backend.Delete(tx, f.FileInfo); err != nil {
			// print but don't return error if deletion of old file fails, we don't want to lose files
			log.Printf("Error deleting %s (%s) from %s: %s", f.FileInfo.Name, f.ID, f.Backend.Name(), err)
		}
	}
	return tx.Commit()
}

func ping(db *postgres.DB, w http.ResponseWriter) {
	if _, err := db.Exec("SELECT 1"); err != nil {
		log.Println(err)
		w.WriteHeader(500)
		return
	}
	w.WriteHeader(200)
}

func getSlugbuilderArtifact(db *postgres.DB) (*ct.Artifact, error) {
	sql := `
SELECT manifest, layer_url_template FROM artifacts
WHERE meta->>'flynn.component' = 'slugbuilder'
ORDER BY created_at DESC LIMIT 1
`
	artifact := &ct.Artifact{
		Type: ct.ArtifactTypeFlynn,
	}
	var layerURLTemplate *string
	if err := db.QueryRow(sql).Scan(&artifact.RawManifest, &layerURLTemplate); err != nil {
		return nil, err
	}
	if layerURLTemplate != nil {
		artifact.LayerURLTemplate = *layerURLTemplate
	}
	return artifact, nil
}

func createDatabase(db *postgres.DB, r render.Render) {
	username, password, database := random.Hex(16), random.Hex(16), random.Hex(16)

	if _, err := db.Exec(fmt.Sprintf(`CREATE USER "%s" WITH PASSWORD '%s'`, username, password)); err != nil {
		log.Println(err)
		r.JSON(500, struct{}{})
		return
	}
	if _, err := db.Exec(fmt.Sprintf(`CREATE DATABASE "%s" WITH OWNER = "%s"`, database, username)); err != nil {
		db.Exec(fmt.Sprintf(`DROP USER "%s"`, username))
		log.Println(err)
		r.JSON(500, struct{}{})
		return
	}

	r.JSON(200, &resource{
		ID: fmt.Sprintf("/databases/%s:%s", username, database),
		Env: map[string]string{
			"FLYNN_POSTGRES": serviceName,
			"PGUSER":         username,
			"PGPASSWORD":     password,
			"PGDATABASE":     database,
		},
	})
}

func getActiveImageArtifacts(db *postgres.DB) ([]*ct.Artifact, error) {
	sql := `
SELECT artifact_id, uri FROM artifacts
WHERE type = 'docker'
AND meta->>'docker-receive.repository' IS NOT NULL
AND deleted_at IS NULL
AND artifact_id IN (
  SELECT artifact_id FROM release_artifacts
  WHERE release_id IN (
    SELECT release_id FROM releases
    WHERE meta->>'docker-receive' = 'true'
    AND release_id IN (
      SELECT release_id
      FROM formations, json_each_text(formations.processes::json)
      WHERE processes != 'null'
      GROUP BY app_id, release_id
      HAVING SUM(value::int) > 0
    )
    OR release_id IN (
      SELECT release_id FROM apps
    )
  )
)
`
	rows, err := db.Query(sql)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var artifacts []*ct.Artifact
	for rows.Next() {
		var artifact ct.Artifact
		if err := rows.Scan(&artifact.ID, &artifact.URI); err != nil {
			return nil, err
		}
		artifacts = append(artifacts, &artifact)
	}
	return artifacts, rows.Err()
}