// GetSessionInfoByPublicID returns a SessionInfo object for a session, identified
// by its publicID and userID.
func (s *Store) GetSessionInfoByPublicID(publicID string, userID int) (*SessionInfo, error) {
result := &SessionInfo{}
err := meddler.QueryRow(s.sqlDB, result,
`SELECT
uploads.title AS title,
uploads.public_id AS public_id,
uploads.user_id AS user_id,
sessions.ended AS ended
FROM uploads, sessions
WHERE sessions.upload_id = uploads.id AND
sessions.public_id = ?`, publicID)
if err != nil {
return nil, err
}
// XXX ugly hack.
formatted := result.Ended.Format(time.RFC3339)
if formatted != "0001-01-01T00:00:00Z" {
result.EndedJSON = formatted
}
err = meddler.QueryRow(s.sqlDB, &result,
`SELECT
commands.page AS page
FROM commands, sessions
WHERE sessions.id = commands.session_id AND
sessions.public_id = ?
ORDER BY commands.timestamp DESC LIMIT 1`, publicID)
if err != nil {
result.Page = 1
}
var cmds []*Command
err = meddler.QueryAll(s.sqlDB, &cmds,
`SELECT
*
FROM commands
WHERE commands.session_id = (SELECT id FROM sessions WHERE public_id = ?)
ORDER BY commands.timestamp`, publicID)
if err != nil {
return nil, err
}
result.Cmds = cmds
result.IsOwner = (userID != 0 && result.UserID == userID)
return result, err
}
// GetProblemStep handles a request to /v2/problems/:problem_id/steps/:step,
// returning a single problem step.
func GetProblemStep(w http.ResponseWriter, tx *sql.Tx, params martini.Params, currentUser *User, render render.Render) {
problemID, err := parseID(w, "problem_id", params["problem_id"])
if err != nil {
return
}
step, err := parseID(w, "step", params["step"])
if err != nil {
return
}
problemStep := new(ProblemStep)
if currentUser.Admin || currentUser.Author {
err = meddler.QueryRow(tx, problemStep, `SELECT * FROM problem_steps WHERE problem_id = $1 AND step = $2`, problemID, step)
} else {
err = meddler.QueryRow(tx, problemStep, `SELECT problem_steps.* `+
`FROM problem_steps JOIN user_problems ON problem_steps.problem_id = user_problems.problem_id `+
`WHERE user_problems.user_id = $1 AND problem_steps.problem_id = $2 AND problem_steps.step = $3`,
currentUser.ID, problemID, step)
}
if err != nil {
loggedHTTPDBNotFoundError(w, err)
return
}
render.JSON(http.StatusOK, problemStep)
}
// GetUserAssignmentProblemStepCommitLast handles requests to /v2/assignments/:assignment_id/problems/:problem_id/steps/:step/commits/last,
// returning the most recent commit for the given step of the given problem of the given assignment.
func GetAssignmentProblemStepCommitLast(w http.ResponseWriter, tx *sql.Tx, params martini.Params, currentUser *User, render render.Render) {
assignmentID, err := parseID(w, "assignment_id", params["assignment_id"])
if err != nil {
return
}
problemID, err := parseID(w, "problem_id", params["problem_id"])
if err != nil {
return
}
step, err := parseID(w, "step", params["step"])
if err != nil {
return
}
commit := new(Commit)
if currentUser.Admin {
err = meddler.QueryRow(tx, commit, `SELECT * FROM commits WHERE assignment_id = $1 AND problem_id = $2 AND step = $3 ORDER BY created_at DESC LIMIT 1`, assignmentID, problemID, step)
} else {
err = meddler.QueryRow(tx, commit, `SELECT commits.* `+
`FROM commits JOIN user_assignments ON commits.assignment_id = user_assignments.assignment_id `+
`WHERE commits.assignment_id = $1 AND problem_id = $2 AND step = $3 AND user_assignments.user_id = $4 `+
`ORDER BY created_at DESC LIMIT 1`,
assignmentID, problemID, step, currentUser.ID)
}
if err != nil {
loggedHTTPDBNotFoundError(w, err)
return
}
render.JSON(http.StatusOK, commit)
}
func TestMigrateRenameTable(t *testing.T) {
defer tearDown()
if err := setUp(); err != nil {
t.Fatalf("Error preparing database: %q", err)
}
mgr := New(db)
if err := mgr.Add(&revision1{}).Migrate(); err != nil {
t.Fatalf("Can not migrate: %q", err)
}
loadFixture(t)
if err := mgr.Add(&revision2{}).Migrate(); err != nil {
t.Fatalf("Can not migrate: %q", err)
}
sample := Sample{}
if err := meddler.QueryRow(db, &sample, `SELECT * FROM examples WHERE id = ?`, 2); err != nil {
t.Fatalf("Can not fetch data: %q", err)
}
if sample.Imel != "*****@*****.**" {
t.Errorf("Column doesn't match. Expect: %s, got: %s", "*****@*****.**", sample.Imel)
}
}
// Put inserts an object into the blobstore.
func (db *Blobstore) Put(path string, data []byte) error {
var blob = Blob{}
meddler.QueryRow(db, &blob, rebind(blobQuery), path)
blob.Path = path
blob.Data = string(data)
return meddler.Save(db, blobTable, &blob)
}
func TestRenameColumn(t *testing.T) {
defer tearDown()
if err := setUp(); err != nil {
t.Fatalf("Error preparing database: %q", err)
}
mgr := New(db)
if err := mgr.Add(&revision1{}, &revision4{}).MigrateTo(1); err != nil {
t.Fatalf("Can not migrate: %q", err)
}
loadFixture(t)
if err := mgr.MigrateTo(4); err != nil {
t.Fatalf("Can not migrate: %q", err)
}
row := RenameSample{}
if err := meddler.QueryRow(db, &row, `SELECT * FROM samples WHERE id = 3;`); err != nil {
t.Fatalf("Can not query database: %q", err)
}
if row.Email != "*****@*****.**" {
t.Errorf("Expect %s, got %s", "*****@*****.**", row.Email)
}
}
// Put inserts an object into the blobstore.
func (b *Blobstore) Put(path string, data []byte) error {
var blob = resource.Blob{}
meddler.QueryRow(b, &blob, queryBlob, path)
blob.Path = path
blob.Data = string(data)
return meddler.Save(b, tableBlob, &blob)
}
// PostPerm saves permission in the datastore.
func (db *Permstore) PostPerm(perm *model.Perm) error {
var _perm = new(model.Perm)
meddler.QueryRow(db, _perm, rebind(permQuery), perm.UserID, perm.RepoID)
if _perm.ID != 0 {
perm.ID = _perm.ID
}
return meddler.Save(db, permTable, perm)
}
func (db *repoStore) GetByName(owner, name string) (*model.Repo, error) {
repo := new(model.Repo)
err := meddler.QueryRow(db, repo, repoNameQuery, owner, name)
if err != nil {
return nil, err
}
return repo, nil
}
func (db *datastore) SetSecret(sec *model.Secret) error {
var got = new(model.Secret)
var err = meddler.QueryRow(db, got, rebind(secretNameQuery), sec.RepoID, sec.Name)
if err == nil && got.ID != 0 {
sec.ID = got.ID // update existing id
}
return meddler.Save(db, secretTable, sec)
}
// GetOwnerForSession returns the userID and numeric sessionID for a session, identified
// by its publicID.
func (s *Store) GetOwnerForSession(publicID string) (userID int, sessionID int, err error) {
ownerData := struct {
UserID int `meddler:"user_id"`
ID int `meddler:"session_id"`
}{}
err = meddler.QueryRow(s.sqlDB, &ownerData, "SELECT uploads.user_id AS user_id, sessions.id AS session_id FROM uploads, sessions WHERE sessions.public_id = ? AND sessions.upload_id = uploads.id LIMIT 1", publicID)
return ownerData.UserID, ownerData.ID, err
}
// GetUploadByPublicID returns an Upload object, identified by its
// publicID and userID.
func (s *Store) GetUploadByPublicID(publicID string, userID int) (*Upload, error) {
uploadEntry := &Upload{}
err := meddler.QueryRow(s.sqlDB, uploadEntry, "select id from uploads where public_id = ? and user_id = ?", publicID, userID)
if err != nil {
uploadEntry = nil
}
return uploadEntry, err
}
func (db *logstore) Write(job *model.Job, r io.Reader) error {
var log = new(model.Log)
var err = meddler.QueryRow(db, log, rebind(logQuery), job.ID)
if err != nil {
log = &model.Log{JobID: job.ID}
}
log.Data, _ = ioutil.ReadAll(r)
return meddler.Save(db, logTable, log)
}
// Creates a new Member.
func SaveMember(user, team int64, role string) error {
r := Role{}
if err := meddler.QueryRow(db, &r, roleFindStmt, user, team); err == nil {
r.Role = role
return meddler.Save(db, memberTable, &r)
}
r.UserID = user
r.TeamID = team
r.Role = role
return meddler.Save(db, memberTable, &r)
}
func BenchmarkMeddlerRow(b *testing.B) {
var user *User
var err error
for n := 0; n < b.N; n++ {
user = &User{}
err = meddler.QueryRow(db, user, SelectUserPkeyStmt, 1)
if err != nil {
panic(err)
}
}
result = user
}
func (a *AccountHandler) GetReport(w rest.ResponseWriter, r *rest.Request) {
var report AccountReport
report.AccountCode = r.PathParam("accountCode")
report.Timestamp = r.PathParam("timestamp")
existing := new(core.Account)
err := meddler.QueryRow(a.db, existing, "SELECT * FROM account WHERE account_code = $1", report.AccountCode)
if err != nil {
a.u.HandleError(err, w, r)
return
}
created, err := time.Parse(time.RFC3339Nano, report.Timestamp)
if err != nil {
a.u.HandleError(err, w, r)
return
}
var snap core.AccountSnapshot
err = meddler.QueryRow(a.db, &snap, "SELECT * FROM account_snapshot WHERE account_id = $1 AND created = $2", existing.Id, created)
if err != nil {
a.u.HandleError(err, w, r)
return
}
err = meddler.QueryAll(a.db, &report.Positions, "SELECT * FROM v_account_position WHERE account_snapshot_id = $1", snap.Id)
if err != nil {
a.u.HandleError(err, w, r)
return
}
err = meddler.QueryRow(a.db, &report.Balance, "SELECT * FROM v_account_amount WHERE account_snapshot_id = $1", snap.Id)
if err != nil {
a.u.HandleError(err, w, r)
return
}
w.Header().Add("Cache-Control", "private, max-age=31556926")
w.WriteJson(&report)
}
func (a *AccountHandler) GetLatest(w rest.ResponseWriter, r *rest.Request) {
code := r.PathParam("accountCode")
latest := new(core.AccountSnapshotLatest)
err := meddler.QueryRow(a.db, latest, "SELECT * FROM v_account_snapshot_latest WHERE account_code = $1", code)
if err != nil {
a.u.HandleError(err, w, r)
return
}
path := fmt.Sprintf("/v1/accounts/%s/%s", code, latest.Latest.Format(time.RFC3339Nano))
url := r.UrlFor(path, make(map[string][]string))
w.Header().Add("Location", url.String())
w.WriteHeader(http.StatusSeeOther)
}
// LtiProblem handles /lti/problem/:unique requests.
// It creates the user/course/assignment if necessary, creates a session,
// and redirects the user to the main UI URL.
func LtiProblemSet(w http.ResponseWriter, r *http.Request, tx *sql.Tx, form LTIRequest, params martini.Params, session sessions.Session) {
unique := params["unique"]
if unique == "" {
loggedHTTPErrorf(w, http.StatusBadRequest, "malformed URL: missing unique ID for problem")
return
}
if unique != url.QueryEscape(unique) {
loggedHTTPErrorf(w, http.StatusBadRequest, "unique ID must be URL friendly: %s is escaped as %s", unique, url.QueryEscape(unique))
return
}
now := time.Now()
// load the problem set
problemSet := new(ProblemSet)
if err := meddler.QueryRow(tx, problemSet, `SELECT * FROM problem_sets WHERE unique_id = $1`, unique); err != nil {
loggedHTTPDBNotFoundError(w, err)
return
}
// load the course
course, err := getUpdateCourse(tx, &form, now)
if err != nil {
loggedHTTPErrorf(w, http.StatusInternalServerError, "db error: %v", err)
return
}
// load the user
user, err := getUpdateUser(tx, &form, now)
if err != nil {
loggedHTTPErrorf(w, http.StatusInternalServerError, "db error: %v", err)
return
}
// load the assignment
asst, err := getUpdateAssignment(tx, &form, now, course, problemSet, user)
if err != nil {
loggedHTTPErrorf(w, http.StatusInternalServerError, "db error: %v", err)
return
}
// sign the user in
session.Set("id", user.ID)
// redirect to the console
//http.Redirect(w, r, fmt.Sprintf("/#/assignment/%d", asst.ID), http.StatusSeeOther)
_ = asst
http.Redirect(w, r, "/v2/users/me/cookie", http.StatusSeeOther)
}
func (sr *SQL) FindOne(r *Request, rp RequestParams, id string) (*Record, error) {
v := reflect.New(sr.Type).Interface()
id_sql_name := sr.GetIdFieldName(v)
err := meddler.QueryRow(sr.DB, v, "SELECT * FROM "+sr.Table+" WHERE "+id_sql_name+"=?", id)
if err == sql.ErrNoRows {
return nil, nil
}
if err != nil {
return nil, err
}
return &Record{
Id: id,
Attributes: v,
}, nil
}
// GetAssignment handles requests to /v2/assignments/:assignment_id,
// returning the given assignment.
func GetAssignment(w http.ResponseWriter, tx *sql.Tx, params martini.Params, currentUser *User, render render.Render) {
assignmentID, err := parseID(w, "assignment_id", params["assignment_id"])
if err != nil {
return
}
assignment := new(Assignment)
if currentUser.Admin {
err = meddler.QueryRow(tx, assignment, `SELECT * FROM assignments WHERE id = $1`, assignmentID)
} else {
err = meddler.QueryRow(tx, assignment, `SELECT assignments.* `+
`FROM assignments JOIN user_assignments ON assignments.id = user_assignments.assignment_id `+
`WHERE id = $1 AND user_assignments.user_id = $2`,
assignmentID, currentUser.ID)
}
if err != nil {
loggedHTTPDBNotFoundError(w, err)
return
}
render.JSON(http.StatusOK, assignment)
}
func (s *mySQLStore) GetFeatureByName(name string) (*models.Feature, error) {
feature := new(models.Feature)
query := sq.Select("*").From("feature").Where(sq.Eq{"name": name})
sql, args, err := query.ToSql()
if err != nil {
return nil, err
}
log.Debug(sql)
if err := meddler.QueryRow(s.db, feature, sql, args...); err != nil {
return nil, err
}
envs, err := s.ListEnvironments()
if err != nil {
return nil, err
}
if len(envs) == 0 {
return feature, nil
}
feature.Status = make(map[string]bool)
envNames := make(map[int64]string)
for _, env := range envs {
feature.Status[env.Name] = false
envNames[env.ID] = env.Name
}
status, err := s.listStatusByFeatureID(feature.ID)
if err != nil {
return nil, err
}
for _, st := range status {
name := envNames[st.EnvironmentID]
if name == "" {
continue
}
feature.Status[name] = st.Enabled
}
return feature, nil
}
func (s *mySQLStore) GetEnvironmentByName(name string) (*models.Environment, error) {
environment := new(models.Environment)
query := sq.Select("*").From("environment")
query = query.Where(sq.Eq{"name": name})
sql, args, err := query.ToSql()
if err != nil {
return nil, err
}
log.Debug(sql)
err = meddler.QueryRow(s.db, environment, sql, args...)
return environment, err
}
// getAccount returns the Account object, creating a database record if needed.
func (a *AccountFeed) getAccount(accountKey string) (core.Account, error) {
existing := new(core.Account)
err := meddler.QueryRow(a.tx, existing, "SELECT * FROM account WHERE account_code = $1", accountKey)
if err != nil && err != sql.ErrNoRows {
return *existing, err
}
if existing.Id != 0 {
return *existing, nil
}
acct := &core.Account{}
acct.AccountCode = accountKey
err = meddler.Insert(a.tx, "account", acct)
return *acct, err
}
// getAccountType returns the AccountType object, creating a database record if needed.
func (a *AccountFeed) getAccountType(desc string) (core.AccountType, error) {
existing := new(core.AccountType)
err := meddler.QueryRow(a.tx, existing, "SELECT * FROM account_type WHERE type_desc = $1", desc)
if err != nil && err != sql.ErrNoRows {
return *existing, err
}
if existing.Id != 0 {
return *existing, nil
}
at := &core.AccountType{}
at.TypeDescription = desc
err = meddler.Insert(a.tx, "account_type", at)
return *at, err
}
// getSecurityType returns the SecurityType object, creating a database record if needed.
func (a *AccountFeed) getSecurityType(desc string) (core.SecurityType, error) {
existing := new(core.SecurityType)
err := meddler.QueryRow(a.tx, existing, "SELECT * FROM security_type WHERE security_type = $1", desc)
if err != nil && err != sql.ErrNoRows {
return *existing, err
}
if existing.Id != 0 {
return *existing, nil
}
st := &core.SecurityType{}
st.SecurityType = desc
err = meddler.Insert(a.tx, "security_type", st)
return *st, err
}
// getSymbol returns the Symbol object, creating a database record if needed.
func (a *AccountFeed) getSymbol(desc string) (core.Symbol, error) {
existing := new(core.Symbol)
err := meddler.QueryRow(a.tx, existing, "SELECT * FROM symbol WHERE symbol = $1", desc)
if err != nil && err != sql.ErrNoRows {
return *existing, err
}
if existing.Id != 0 {
return *existing, nil
}
s := &core.Symbol{}
s.Symbol = desc
err = meddler.Insert(a.tx, "symbol", s)
return *s, err
}
// getExchange returns the Exchange object, creating a database record if needed.
func (a *AccountFeed) getExchange(desc string) (core.Exchange, error) {
existing := new(core.Exchange)
err := meddler.QueryRow(a.tx, existing, "SELECT * FROM exchange WHERE exchange = $1", desc)
if err != nil && err != sql.ErrNoRows {
return *existing, err
}
if existing.Id != 0 {
return *existing, nil
}
e := &core.Exchange{}
e.Exchange = desc
err = meddler.Insert(a.tx, "exchange", e)
return *e, err
}
func (s *mySQLStore) GetUserByUsername(username string) (*models.User, error) {
user := new(models.User)
query := sq.Select("*").From("user")
query = query.Where(sq.Eq{"username": username})
sql, args, err := query.ToSql()
if err != nil {
return nil, err
}
log.Debug(sql)
err = meddler.QueryRow(s.db, user, sql, args...)
return user, err
}
// get/create/update this user
func getUpdateUser(tx *sql.Tx, form *LTIRequest, now time.Time) (*User, error) {
user := new(User)
if err := meddler.QueryRow(tx, user, `SELECT * FROM users WHERE lti_id = $1`, form.UserID); err != nil {
if err != sql.ErrNoRows {
log.Printf("db error loading user %s (%s): %v", form.UserID, form.PersonContactEmailPrimary, err)
return nil, err
}
log.Printf("creating new user (%s)", form.PersonContactEmailPrimary)
user.ID = 0
user.CreatedAt = now
user.UpdatedAt = now
}
// any changes?
changed := user.Name != form.PersonNameFull ||
user.Email != form.PersonContactEmailPrimary ||
user.LtiID != form.UserID ||
user.ImageURL != form.UserImage ||
user.CanvasLogin != form.CanvasUserLoginID ||
user.CanvasID != form.CanvasUserID
// make any changes
user.Name = form.PersonNameFull
user.Email = form.PersonContactEmailPrimary
user.LtiID = form.UserID
user.ImageURL = form.UserImage
user.CanvasLogin = form.CanvasUserLoginID
user.CanvasID = form.CanvasUserID
if user.ID > 0 && changed {
// if something changed, note the update time
log.Printf("user %d (%s) updated", user.ID, user.Email)
user.UpdatedAt = now
}
// always save to note the last signed in time
user.LastSignedInAt = now
if err := meddler.Save(tx, "users", user); err != nil {
log.Printf("db error updating user %s (%s): %v", user.LtiID, user.Email, err)
return nil, err
}
return user, nil
}
// Returns the system Settings.
func GetSettings() (*Settings, error) {
//settingsLock.Lock()
//defer settingsLock.Unlock()
// return a copy of the settings
//if settingsCache.ID == 0 {
/// settingsCopy := &Settings{}
// *settingsCopy = *settingsCache
// return settingsCopy, nil
//}
settings := Settings{}
err := meddler.QueryRow(db, &settings, settingsStmt)
//if err == sql.ErrNoRows {
// // we ignore the NoRows error in case this
// // is the first time the system is being used
// err = nil
//}
return &settings, err
}