Golang meddler.QueryAll函數代碼示例

// GetProblemSetProblems handles a request to /v2/problem_sets/:problem_set_id/problems,
// returning a list of all problems set problems for a given problem set.
func GetProblemSetProblems(w http.ResponseWriter, r *http.Request, tx *sql.Tx, params martini.Params, currentUser *User, render render.Render) {
	problemSetID, err := parseID(w, "problem_set_id", params["problem_set_id"])
	if err != nil {
		return
	}

	problemSetProblems := []*ProblemSetProblem{}

	if currentUser.Admin || currentUser.Author {
		err = meddler.QueryAll(tx, &problemSetProblems, `SELECT * FROM problem_set_problems WHERE problem_set_id = $1 ORDER BY problem_id`, problemSetID)
	} else {
		err = meddler.QueryAll(tx, &problemSetProblems, `SELECT problem_set_problems.* `+
			`FROM problem_set_problems JOIN user_problem_sets ON problem_set_problems.problem_set_id = user_problem_sets.problem_set_id `+
			`WHERE problem_set_problems.user_id = $1 AND problem_set_problems.problem_set_id = $2 `+
			`ORDER BY problem_id`, currentUser.ID, problemSetID)
	}

	if err != nil {
		loggedHTTPErrorf(w, http.StatusInternalServerError, "db error: %v", err)
		return
	}

	if len(problemSetProblems) == 0 {
		loggedHTTPErrorf(w, http.StatusNotFound, "not found")
		return
	}

	render.JSON(http.StatusOK, problemSetProblems)
}

// GetUserAssignments handles requests to /v2/users/:user_id/assignments,
// returning a list of assignments for the given user.
func GetUserAssignments(w http.ResponseWriter, tx *sql.Tx, params martini.Params, currentUser *User, render render.Render) {
	userID, err := parseID(w, "user_id", params["user_id"])
	if err != nil {
		return
	}

	assignments := []*Assignment{}

	if currentUser.Admin {
		err = meddler.QueryAll(tx, &assignments, `SELECT * FROM assignments WHERE user_id = $1 `+
			`ORDER BY course_id, updated_at`,
			userID)
	} else {
		err = meddler.QueryAll(tx, &assignments, `SELECT assignments.* `+
			`FROM assignments JOIN user_assignments ON assignments.id = user_assignments.assignment_id `+
			`WHERE assignments.user_id = $1 AND user_assignments.user_id = $2 `+
			`ORDER BY course_id, updated_at`,
			userID, currentUser.ID)
	}

	if err != nil {
		loggedHTTPErrorf(w, http.StatusInternalServerError, "db error: %v", err)
		return
	}

	render.JSON(http.StatusOK, assignments)
}

// GetCourses handles /v2/courses requests,
// returning a list of all courses.
//
// If parameter lti_label=<...> present, results will be filtered by matching lti_label field.
// If parameter name=<...> present, results will be filtered by case-insensitive substring matching on name field.
func GetCourses(w http.ResponseWriter, r *http.Request, tx *sql.Tx, currentUser *User, render render.Render) {
	where := ""
	args := []interface{}{}

	if ltiLabel := r.FormValue("lti_label"); ltiLabel != "" {
		where, args = addWhereEq(where, args, "lti_label", ltiLabel)
	}

	if name := r.FormValue("name"); name != "" {
		where, args = addWhereLike(where, args, "name", name)
	}

	courses := []*Course{}
	var err error

	if currentUser.Admin {
		err = meddler.QueryAll(tx, &courses, `SELECT * FROM courses`+where+` ORDER BY lti_label`, args...)
	} else {
		where, args = addWhereEq(where, args, "assignments.user_id", currentUser.ID)
		err = meddler.QueryAll(tx, &courses, `SELECT DISTINCT courses.* `+
			`FROM courses JOIN assignments ON courses.id = assignments.course_id`+
			where+` ORDER BY lti_label`, args...)
	}

	if err != nil {
		loggedHTTPErrorf(w, http.StatusInternalServerError, "db error: %v", err)
		return
	}
	render.JSON(http.StatusOK, courses)
}

// GetCourseUsers handles request to /v2/course/:course_id/users,
// returning a list of users in the given course.
func GetCourseUsers(w http.ResponseWriter, tx *sql.Tx, params martini.Params, currentUser *User, render render.Render) {
	courseID, err := parseID(w, "course_id", params["course_id"])
	if err != nil {
		return
	}

	users := []*User{}

	if currentUser.Admin {
		err = meddler.QueryAll(tx, &users, `SELECT DISTINCT users.* `+
			`FROM users JOIN assignments ON users.id = assignments.user_id `+
			`WHERE assignments.course_id = $1 ORDER BY users.id`,
			courseID)
	} else {
		err = meddler.QueryAll(tx, &users, `SELECT DISTINCT users.* `+
			`FROM users JOIN assignments ON users.id = assignments.user_id `+
			`JOIN user_users ON assignments.user_id = user_users.other_user_id `+
			`WHERE assignments.course_id = $1 AND user_users.user_id = $2 `+
			`ORDER BY users.id`,
			courseID, currentUser.ID)
	}

	if err != nil {
		loggedHTTPErrorf(w, http.StatusInternalServerError, "db error: %v", err)
		return
	}

	if len(users) == 0 {
		loggedHTTPErrorf(w, http.StatusNotFound, "not found")
		return
	}

	render.JSON(http.StatusOK, users)
}

// GetProblems handles a request to /v2/problems,
// returning a list of all problems.
//
// If parameter unique=<...> present, results will be filtered by matching Unique field.
// If parameter problemType=<...> present, results will be filtered by matching ProblemType.
// If parameter note=<...> present, results will be filtered by case-insensitive substring match on Note field.
func GetProblems(w http.ResponseWriter, r *http.Request, tx *sql.Tx, currentUser *User, render render.Render) {
	// build search terms
	where := ""
	args := []interface{}{}

	if unique := r.FormValue("unique"); unique != "" {
		where, args = addWhereEq(where, args, "unique_id", unique)
	}

	if problemType := r.FormValue("problemType"); problemType != "" {
		where, args = addWhereEq(where, args, "problem_type", problemType)
	}

	if name := r.FormValue("note"); name != "" {
		where, args = addWhereLike(where, args, "note", name)
	}

	// get the problems
	problems := []*Problem{}
	var err error

	if currentUser.Admin || currentUser.Author {
		err = meddler.QueryAll(tx, &problems, `SELECT * FROM problems`+where+` ORDER BY id`, args...)
	} else {
		where, args = addWhereEq(where, args, "user_id", currentUser.ID)
		err = meddler.QueryAll(tx, &problems, `SELECT problems.* FROM problems JOIN user_problems ON problems.id = problem_id`+where+` ORDER BY id`, args...)
	}

	if err != nil {
		loggedHTTPErrorf(w, http.StatusInternalServerError, "db error: %v", err)
		return
	}

	render.JSON(http.StatusOK, problems)
}

func TestIndexOperations(t *testing.T) {
	defer tearDown()
	if err := setUp(); err != nil {
		t.Fatalf("Error preparing database: %q", err)
	}

	Driver = SQLite

	mgr := New(db)

	// Migrate, create index
	if err := mgr.Add(&revision1{}, &revision3{}, &revision5{}).Migrate(); err != nil {
		t.Errorf("Can not migrate: %q", err)
	}

	var esquel []*sqliteMaster
	// Query sqlite_master, check if index is exists.
	query := `SELECT sql FROM sqlite_master WHERE type='index' and tbl_name='samples'`
	if err := meddler.QueryAll(db, &esquel, query); err != nil {
		t.Errorf("Can not find index: %q", err)
	}

	indexStatement := `CREATE INDEX samples_url_name_ix ON samples (url, name)`
	if string(esquel[1].Sql.([]byte)) != indexStatement {
		t.Errorf("Can not find index")
	}

	// Migrate, rename indexed columns
	if err := mgr.Add(&revision6{}).Migrate(); err != nil {
		t.Errorf("Can not migrate: %q", err)
	}

	var esquel1 []*sqliteMaster
	if err := meddler.QueryAll(db, &esquel1, query); err != nil {
		t.Errorf("Can not find index: %q", err)
	}

	indexStatement = `CREATE INDEX samples_host_name_ix ON samples (host, name)`
	if string(esquel1[1].Sql.([]byte)) != indexStatement {
		t.Errorf("Can not find index, got: %s", esquel[0])
	}

	if err := mgr.Add(&revision7{}).Migrate(); err != nil {
		t.Errorf("Can not migrate: %q", err)
	}

	var esquel2 []*sqliteMaster
	if err := meddler.QueryAll(db, &esquel2, query); err != nil {
		t.Errorf("Can not find index: %q", err)
	}

	if len(esquel2) != 1 {
		t.Errorf("Expect row length equal to %d, got %d", 1, len(esquel2))
	}
}

// GetUsers handles /v2/users requests,
// returning a list of all users.
//
// If parameter name=<...> present, results will be filtered by case-insensitive substring match on Name field.
// If parameter email=<...> present, results will be filtered by case-insensitive substring match on Email field.
// If parameter instructor=<...> present, results will be filtered matching instructor field (true or false).
// If parameter admin=<...> present, results will be filtered matching admin field (true or false).
func GetUsers(w http.ResponseWriter, r *http.Request, tx *sql.Tx, currentUser *User, render render.Render) {
	// build search terms
	where := ""
	args := []interface{}{}

	if name := r.FormValue("name"); name != "" {
		where, args = addWhereLike(where, args, "name", name)
	}

	if email := r.FormValue("email"); email != "" {
		where, args = addWhereLike(where, args, "email", email)
	}

	if instructor := r.FormValue("instructor"); instructor != "" {
		val, err := strconv.ParseBool(instructor)
		if err != nil {
			loggedHTTPErrorf(w, http.StatusBadRequest, "error parsing instructor value as boolean: %v", err)
			return
		}
		where, args = addWhereEq(where, args, "instructor", val)
	}

	if admin := r.FormValue("admin"); admin != "" {
		val, err := strconv.ParseBool(admin)
		if err != nil {
			loggedHTTPErrorf(w, http.StatusBadRequest, "error parsing admin value as boolean: %v", err)
			return
		}
		where, args = addWhereEq(where, args, "admin", val)
	}

	users := []*User{}
	var err error

	if currentUser.Admin {
		err = meddler.QueryAll(tx, &users, `SELECT * FROM users`+where+` ORDER BY id`, args...)
	} else {
		where, args = addWhereEq(where, args, "user_users.user_id", currentUser.ID)
		err = meddler.QueryAll(tx, &users, `SELECT users.* `+
			`FROM users JOIN user_users ON users.id = user_users.other_user_id`+
			where+` ORDER BY id`, args...)
	}

	if err != nil {
		loggedHTTPErrorf(w, http.StatusInternalServerError, "db error: %v", err)
		return
	}
	render.JSON(http.StatusOK, users)
}

// TODO: update this to honor sorting
func (sr *SQL) FindDefault(r *Request, rp RequestParams) ([]*Record, error) {
	p := rp.Paginator
	a := r.API
	vs := reflect.New(reflect.SliceOf(reflect.PtrTo(sr.Type))).Interface()
	offset_and_limit := ""
	if p.ShouldPaginate {
		offset_and_limit = fmt.Sprintf("LIMIT %d OFFSET %d",
			p.MaxPerPage,
			p.CurPage*p.MaxPerPage,
		)
	}
	q := fmt.Sprintf(
		"SELECT * FROM %s %s",
		sr.Table,
		offset_and_limit,
	)
	a.Logger.Debugf("Query: %#v\n", q)
	err := meddler.QueryAll(
		sr.DB,
		vs,
		q,
	)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return sr.ConvertInterfaceSliceToRecordSlice(vs), err
}

// CreateUser checks whether an account for the specified username exists. If it
// does, then it returns its userID, otherwise it creates a new user and a new
// account with the specified username and links the account to the user.
func (s *Store) CreateUser(username string) (int, error) {
	userData := []*struct {
		UserID int `meddler:"user_id"`
	}{}
	err := meddler.QueryAll(s.sqlDB, &userData, "SELECT user_id FROM accounts WHERE username = ? LIMIT 1", username)
	if err != nil {
		return 0, err
	}

	if len(userData) > 0 {
		return userData[0].UserID, nil
	}

	result, err := s.sqlDB.Exec("INSERT INTO users (id) VALUES(NULL)")
	if err != nil {
		return 0, err
	}

	lastInsertID, _ := result.LastInsertId()

	_, err = s.sqlDB.Exec("INSERT INTO accounts (username, user_id) VALUES (?, ?)", username, lastInsertID)
	if err != nil {
		return 0, err
	}

	return int(lastInsertID), nil
}

func (db *datastore) GetRepoMulti(slug ...string) ([]*model.Repo, error) {
	var repos = []*model.Repo{}
	var instr, params = toList(slug)
	var stmt = fmt.Sprintf(repoListQuery, instr)
	var err = meddler.QueryAll(db, &repos, stmt, params...)
	return repos, err
}

// GetSessions returns a slice of SessionData objects for the specified user.
func (s *Store) GetSessions(userID int) ([]*SessionData, error) {
	xlog.Debugf("GetSessions: userID = %d", userID)
	result := []*SessionData{}
	err := meddler.QueryAll(s.sqlDB, &result,
		`SELECT sessions.public_id AS public_id, 
			sessions.started AS started, 
			sessions.ended AS ended, 
			uploads.title AS title
		FROM uploads, sessions 
		WHERE sessions.upload_id = uploads.id AND 
			uploads.user_id = ? 
		ORDER BY sessions.started DESC`, userID)
	if err != nil {
		result = nil
	} else {
		// XXX: ugly hack.
		for _, entry := range result {
			formatted := entry.Ended.Format(time.RFC3339)
			if formatted != "0001-01-01T00:00:00Z" {
				entry.EndedJSON = formatted
			}
		}
	}
	return result, err
}

func (f *FutureSQL) RunQuery(pf *ExecutableFuture, req *FutureRequest, parameters []SQLExpression) ([]*Record, bool, *OError) {
	lp, psql := f.Resource.GetPromise(pf.Request)
	defer lp.Release()

	tx, err := psql.GetSQLTransaction(f.Resource.DB)
	if err != nil {
		oe := ErrorToOError(err)
		return []*Record{}, false, &oe
	}
	vs := reflect.New(reflect.SliceOf(reflect.PtrTo(f.Resource.Type))).Interface()
	query, queryargs, is_single := f.PrepareQuery(parameters...)
	pf.Request.API.Logger.Debugf("RUN QUERY: %#v %#v\n", query, queryargs)
	err = meddler.QueryAll(
		tx,
		vs,
		query,
		queryargs...,
	)
	var oerr *OError
	if err != nil {
		oerr = &OError{
			Title:  err.Error(),
			Detail: fmt.Sprintf("%s -- %#v\n", query, queryargs),
		}
	}
	return ConvertInterfaceSliceToRecordSlice(vs), is_single, oerr
}

func (sr *SQL) FindManyByField(r *Request, rp RequestParams, field, value string) ([]*Record, error) {
	p := rp.Paginator
	vs := reflect.New(reflect.SliceOf(reflect.PtrTo(sr.Type))).Interface()
	field, err := sr.GetTableFieldFromStructField(field)
	if err != nil {
		return nil, err
	}
	offset_and_limit := ""
	if p.ShouldPaginate {
		offset_and_limit = fmt.Sprintf("LIMIT %d OFFSET %d",
			p.MaxPerPage,
			p.CurPage*p.MaxPerPage,
		)
	}
	// TODO: find a way to parameterize field in this query
	// right now, field is always a trusted string, but some
	// later relationship behaviors might change that, and it's
	// better to be safe than sorry
	// dropping in ? instead of field does not work :/
	q := fmt.Sprintf("SELECT * FROM %s WHERE %s=? %s", sr.Table, field, offset_and_limit)
	r.API.Logger.Debugf("Query: %#v %#v\n", q, value)
	err = meddler.QueryAll(
		sr.DB,
		vs,
		q,
		value,
	)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	r.API.Logger.Debugf("RES: %#v\n", vs)
	return sr.ConvertInterfaceSliceToRecordSlice(vs), err
}

func TestMigrateExistingTable(t *testing.T) {
	defer tearDown()
	if err := setUp(); err != nil {
		t.Fatalf("Error preparing database: %q", err)
	}

	Driver = SQLite

	if _, err := db.Exec(testSchema); err != nil {
		t.Errorf("Can not create database: %q", err)
	}

	loadFixture(t)

	mgr := New(db)
	if err := mgr.Add(&revision4{}).Migrate(); err != nil {
		t.Errorf("Can not migrate: %q", err)
	}

	var rows []*RenameSample
	if err := meddler.QueryAll(db, &rows, `SELECT * from samples;`); err != nil {
		t.Errorf("Can not query database: %q", err)
	}

	if len(rows) != 3 {
		t.Errorf("Expect rows length = %d, got %d", 3, len(rows))
	}

	if rows[1].Email != "[email protected]" {
		t.Errorf("Expect email = %s, got %s", "[email protected]", rows[1].Email)
	}
}

func (db *repoStore) GetRepoList() ([]*model.Repo, error) {
	var repos []*model.Repo
	err := meddler.QueryAll(db, &repos, repoListQuery)
	if err != nil {
		return nil, err
	}
	return repos, nil
}