atsebay.t/repositories.go

package main

import (
	"crypto/rand"
	"encoding/base64"
	"flag"
	"fmt"
	"log"
	"net/http"
	"net/url"
	"path/filepath"
	"strconv"
	"strings"
	"time"

	"github.com/aws/aws-sdk-go/aws"
	"github.com/aws/aws-sdk-go/service/s3"
	"github.com/drone/drone-go/drone"
	"github.com/gin-gonic/gin"
	"golang.org/x/oauth2"
)

var (
	droneToken         = ""
	droneConfig        *http.Client
	droneEndpoint      string
	testsCallbackToken string
)

func init() {
	flag.StringVar(&droneToken, "drone-token", droneToken, "Token for Drone Oauth")
	flag.StringVar(&droneEndpoint, "drone-endpoint", droneEndpoint, "Drone Endpoint")
	flag.StringVar(&testsCallbackToken, "tests-callback-token", testsCallbackToken, "Token of the callback token")
}

func initializeDroneOauth() {
	if droneToken != "" {
		config := new(oauth2.Config)
		droneConfig = config.Client(
			oauth2.NoContext,
			&oauth2.Token{
				AccessToken: droneToken,
			},
		)
	}
}

type RepositoryAdminPull struct {
	Tag               *string `json:"tag"`
	OptionalSignature bool    `json:"sig_optional"`
}

func declareAPIAuthRepositoriesRoutes(router *gin.RouterGroup) {
	router.GET("/repositories", func(c *gin.Context) {
		var u *User
		if user, ok := c.Get("user"); ok {
			u = user.(*User)
		} else {
			u = c.MustGet("LoggedUser").(*User)
		}

		repositories, err := u.GetRepositories()
		if err != nil {
			log.Println("Unable to GetRepositories:", err)
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Unable to retrieve your repositories. Please try again in a few moment."})
			return
		}

		if work, ok := c.Get("work"); ok {
			var res []*Repository

			for _, r := range repositories {
				if r.IdWork == work.(*Work).Id {
					// Is the URL used elsewhere?
					repos, _ := getRepositoriesByURI(r.URI)
					if len(repos) > 1 {
						r.AlreadyUsed = true
					}

					res = append(res, r)
				}
			}

			c.JSON(http.StatusOK, res)
		} else {
			c.JSON(http.StatusOK, repositories)
		}
	})
	router.POST("/repositories", func(c *gin.Context) {
		var u *User
		if user, ok := c.Get("user"); ok {
			u = user.(*User)
		} else {
			u = c.MustGet("LoggedUser").(*User)
		}

		var repository Repository
		if err := c.ShouldBindJSON(&repository); err != nil {
			c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"errmsg": err.Error()})
			return
		}

		uri, err := url.Parse(repository.URI)
		if err != nil {
			tmp := strings.Split(repository.URI, ":")
			if len(tmp) == 2 {
				uri, err = url.Parse(fmt.Sprintf("ssh://%s/%s", tmp[0], tmp[1]))
			} else if len(tmp) == 3 {
				uri, err = url.Parse(fmt.Sprintf("%s://%s/%s", tmp[0], tmp[1], tmp[2]))
			}

			if err != nil {
				c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"errmsg": fmt.Sprintf("invalid repository URL: %s", err.Error())})
				return
			}
		}

		if uri.Scheme != "ssh" && uri.Scheme != "git+ssh" {
			c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"errmsg": "Unrecognized URL scheme. You need to provide a SSH repository URL."})
			return
		}

		if strings.Contains(uri.Host, "epita.fr") {
			if !strings.HasPrefix(uri.Path, fmt.Sprintf("/%s/", u.Login)) {
				c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"errmsg": "repository URL forbidden"})
				return
			}
		}

		var w *Work
		if work, ok := c.Get("work"); ok {
			w = work.(*Work)
		} else if repository.IdWork > 0 {
			var err error
			w, err = getWork(repository.IdWork)
			if err != nil {
				c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "Unable to find the given work identifier."})
				return
			}
		} else {
			c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"errmsg": "Unable to find the given work identifier."})
			return
		}

		k, err := u.NewRepository(w, repository.URI)
		if err != nil {
			log.Println("Unable to NewRepository:", err)
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Unable to register your public repository. Please try again in a few moment."})
			return
		}

		c.JSON(http.StatusOK, k)
	})

	repositoriesRoutes := router.Group("/repositories/:rid")
	repositoriesRoutes.Use(repositoryHandler)

	repositoriesRoutes.GET("", func(c *gin.Context) {
		repo := c.MustGet("repository").(*Repository)

		// Is the URL used elsewhere?
		repos, _ := getRepositoriesByURI(repo.URI)
		if len(repos) > 1 {
			repo.AlreadyUsed = true
		}

		c.JSON(http.StatusOK, repo)
	})
	repositoriesRoutes.PUT("", func(c *gin.Context) {
		current := c.MustGet("repository").(*Repository)

		var new Repository
		if err := c.ShouldBindJSON(&new); err != nil {
			c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"errmsg": err.Error()})
			return
		}

		new.Id = current.Id

		u := c.MustGet("LoggedUser").(*User)
		if new.IdUser != current.IdUser && !u.IsAdmin {
			c.AbortWithStatusJSON(http.StatusForbidden, gin.H{"errmsg": "Operation not allowed."})
			return
		}

		if repository, err := new.Update(); err != nil {
			log.Println("Unable to Update repository:", err)
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": fmt.Sprintf("An error occurs during repository updation: %s", err.Error())})
			return
		} else {
			c.JSON(http.StatusOK, repository)
		}
	})
	repositoriesRoutes.DELETE("", func(c *gin.Context) {
		loggeduser := c.MustGet("LoggedUser").(*User)
		repository := c.MustGet("repository").(*Repository)
		work, err := getWork(repository.IdWork)
		if err != nil {
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Unable to find related work."})
			return

		}

		now := time.Now()

		if !loggeduser.IsAdmin && (!work.Shown || work.Corrected || work.StartAvailability.After(now) || work.EndAvailability.Before(now)) {
			c.AbortWithStatusJSON(http.StatusForbidden, gin.H{"errmsg": "The submission is closed."})
			return
		}

		if _, err := repository.Delete(); err != nil {
			log.Println("Unable to Delete repository:", err)
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": fmt.Sprintf("An error occurs during repository deletion: %s", err.Error())})
			return
		} else {
			c.JSON(http.StatusOK, nil)
		}
	})

	repositoriesRoutes.POST("/trigger", func(c *gin.Context) {
		loggeduser := c.MustGet("LoggedUser").(*User)
		var u *User
		if user, ok := c.Get("user"); ok {
			u = user.(*User)
		} else {
			u = loggeduser
		}
		repo := c.MustGet("repository").(*Repository)
		work, err := getWork(repo.IdWork)
		if err != nil {
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Unable to find related work."})
			return
		}

		now := time.Now()

		if repo.LastCheck != nil && !repo.LastCheck.Before(now.Add(-5*time.Minute)) && !loggeduser.IsAdmin {
			c.AbortWithStatusJSON(http.StatusPaymentRequired, gin.H{"errmsg": "Please wait between two pulls."})
			return
		}

		var rap RepositoryAdminPull
		if loggeduser.IsAdmin {
			c.ShouldBindJSON(&rap)
		}

		TriggerTagUpdate(c, work, repo, u, rap.Tag, rap.OptionalSignature)
	})

	repositoriesRoutes.GET("/state", func(c *gin.Context) {
		repo := c.MustGet("repository").(*Repository)

		tmp := strings.Split(repo.DroneRef, "/")
		if len(tmp) < 3 {
			c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "No build number. Please try pulling your work."})
			return
		}

		nbuild, err := strconv.Atoi(tmp[2])
		if err != nil {
			c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "Bad build number. Please retry pulling your work."})
			return
		}

		client := drone.NewClient(droneEndpoint, droneConfig)
		result, err := client.Build(tmp[0], tmp[1], nbuild)
		if err != nil {
			c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "Unable to find the referenced extraction."})
			return
		}

		c.JSON(http.StatusOK, result)
	})

	repositoriesRoutes.GET("/state-logs", func(c *gin.Context) {
		repo := c.MustGet("repository").(*Repository)

		tmp := strings.Split(repo.DroneRef, "/")
		if len(tmp) < 3 {
			c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "No build number. Please try pulling your work."})
			return
		}

		nbuild, err := strconv.Atoi(tmp[2])
		if err != nil {
			c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "Bad build number. Please retry pulling your work."})
			return
		}

		client := drone.NewClient(droneEndpoint, droneConfig)
		result, err := client.Logs(tmp[0], tmp[1], nbuild, 1, 3)
		if err != nil {
			log.Printf("An error occurs when retrieving logs from Drone (%s/%s/%d/1/3): %s", tmp[0], tmp[1], nbuild, err.Error())
			c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "Unable to retrieve logs."})
			return
		}

		if len(result) > 7 {
			c.JSON(http.StatusOK, result[7:])
		} else {
			c.JSON(http.StatusOK, result)
		}
	})

	repositoriesRoutes.POST("/gradation", func(c *gin.Context) {
		loggeduser := c.MustGet("LoggedUser").(*User)
		if !loggeduser.IsAdmin {
			c.AbortWithStatusJSON(http.StatusForbidden, gin.H{"errmsg": "Permission denied."})
			return
		}

		var u *User
		if user, ok := c.Get("user"); ok {
			u = user.(*User)
		} else {
			u = loggeduser
		}
		repo := c.MustGet("repository").(*Repository)
		work, err := getWork(repo.IdWork)
		if err != nil {
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Unable to find related work."})
			return
		}

		TriggerTests(c, work, repo, u)
	})

	repositoriesRoutes.GET("/gradation_status", func(c *gin.Context) {
		loggeduser := c.MustGet("LoggedUser").(*User)
		if !loggeduser.IsAdmin {
			c.AbortWithStatusJSON(http.StatusForbidden, gin.H{"errmsg": "Permission denied."})
			return
		}

		repo := c.MustGet("repository").(*Repository)

		slug := strings.Split(repo.TestsRef, "/")
		if len(slug) < 3 {
			return
		}

		buildn, err := strconv.ParseInt(slug[2], 10, 32)
		if err != nil {
			return
		}

		client := drone.NewClient(droneEndpoint, droneConfig)
		build, err := client.Build(slug[0], slug[1], int(buildn))
		if err != nil {
			log.Println("Unable to communicate with Drone:", err.Error())
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Unable to communicate with Drone"})
			return
		}

		c.JSON(http.StatusOK, build)
	})

	repositoriesRoutes.GET("/traces", func(c *gin.Context) {
		repo := c.MustGet("repository").(*Repository)

		c.Redirect(http.StatusFound, fmt.Sprintf("%s/%s", droneEndpoint, repo.TestsRef))
	})
}

type GitLabWebhook struct {
	EventName  string `json:"event_name"`
	ObjectKind string `json:"object_kind"`
	Ref        string
	Repository GitLabRepository
}

func declareCallbacksRoutes(router *gin.RouterGroup) {
	router.POST("/callbacks/trigger.json", func(c *gin.Context) {
		// Check event type
		if c.Request.Header.Get("X-Gitlab-Event") != "Tag Push Hook" {
			c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"errmsg": "This trigger is limited to Tag Push event. Please edit your trigger on GitLab."})
			return
		}

		hook := GitLabWebhook{}
		if err := c.ShouldBindJSON(&hook); err != nil {
			c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"errmsg": err.Error()})
			return
		}

		// Check token
		repos, err := getRepositoriesByURI(hook.Repository.URL)
		if err != nil {
			log.Println("Unable to getRepositoriesByURI:", err.Error())
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Unable to retrieve repositories."})
			return
		}

		var repo *Repository
		for _, r := range repos {
			if len(r.Secret) == 0 || base64.StdEncoding.EncodeToString(r.Secret) == c.Request.Header.Get("X-Gitlab-Token") {
				repo = r
				break
			}
		}

		if repo == nil {
			c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "There are no repositories matching this URL and secret. Please check the Gitlab Secret and retry pushing your tag."})
			return
		}

		work, err := getWork(repo.IdWork)
		if err != nil {
			log.Println("Unable to getWork:", err.Error())
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Unable to find related work."})
			return

		}

		user, err := getUser(int(repo.IdUser))
		if err != nil {
			log.Println("Unable to getUser:", err.Error())
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Unable to find related user."})
			return

		}

		tmp := strings.SplitN(hook.Ref, "/", 3)
		if len(tmp) != 3 {
			TriggerTagUpdate(c, work, repo, user, nil, false)
			return
		}

		if !strings.HasPrefix(tmp[2], work.Tag) {
			// Allow to use a secret for another tag
			if len(repos) > 1 {
				for _, r := range repos {
					w, err := getWork(r.IdWork)
					if err != nil {
						log.Println("Unable to getWork:", err.Error())
						continue
					}

					if strings.HasPrefix(tmp[2], w.Tag) {
						repo = r
						work = w
						break
					}
				}
			}

			if !strings.HasPrefix(tmp[2], work.Tag) {
				c.AbortWithStatusJSON(http.StatusOK, gin.H{"errmsg": fmt.Sprintf("Ignore ref %q has it doesn't start with %s. Check submission instructions if this is not expected.", tmp[2], work.Tag)})
				return
			}
		}

		TriggerTagUpdate(c, work, repo, user, &tmp[2], false)
	})

	router.POST("/callbacks/tests.json", func(c *gin.Context) {
		// Check auth token
		if c.GetHeader("X-Authorization") != testsCallbackToken {
			c.AbortWithStatusJSON(http.StatusUnauthorized, gin.H{"errmsg": "Authorization token is invalid"})
			return
		}

		// Get form data
		hook := TestsWebhook{}
		if err := c.ShouldBindJSON(&hook); err != nil {
			c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"errmsg": err.Error()})
			return
		}

		// Retrieve corresponding repository
		repo, err := getRepository(hook.RepositoryId)
		if err != nil {
			log.Printf("Unable to getRepository(%d): %s", hook.RepositoryId, err.Error())
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Unable to retrieve repository."})
			return
		}

		err = hook.fetchRepoTests(repo)
		if err != nil {
			log.Printf("Unable to fetchRepoTests(%d): %s", hook.RepositoryId, err.Error())
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Unable to fetch tests results."})
			return
		}
	})
}

func repositoryHandler(c *gin.Context) {
	var u *User
	if user, ok := c.Get("user"); ok {
		u = user.(*User)
	} else {
		u = c.MustGet("LoggedUser").(*User)
	}

	if rid, err := strconv.Atoi(string(c.Param("rid"))); err != nil {
		c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"errmsg": "Bad repository identifier."})
		return
	} else if c.MustGet("LoggedUser").(*User).IsAdmin {
		if repository, err := getRepository(rid); err != nil {
			c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "Repository not found."})
			return
		} else {
			c.Set("repository", repository)
			c.Next()
		}
	} else if repository, err := u.getRepository(rid); err != nil {
		c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "Repository not found."})
		return
	} else {
		c.Set("repository", repository)
		c.Next()
	}
}

func TriggerTagUpdate(c *gin.Context, work *Work, repo *Repository, u *User, tag *string, sig_optional bool) {
	loggeduser := c.MustGet("LoggedUser").(*User)
	now := time.Now()

	if (loggeduser == nil || !loggeduser.IsAdmin) && (!work.Shown || work.Corrected || work.StartAvailability.After(now) || work.EndAvailability.Add(time.Hour).Before(now)) {
		c.AbortWithStatusJSON(http.StatusForbidden, gin.H{"errmsg": "The submission is closed."})
		return
	}

	repo_tag := work.Tag
	if tag != nil {
		repo_tag = *tag
	}

	login := u.Login
	groups := u.Groups
	if u.Id != repo.IdUser {
		user, _ := getUser(int(repo.IdUser))
		if user != nil {
			login = user.Login
			groups = user.Groups
		}
	}

	env := map[string]string{
		"REPO_URL": repo.URI,
		"REPO_TAG": repo_tag,
		"LOGIN":    login,
		"GROUPS":   groups,
		"DEST":     fmt.Sprintf("%d", work.Id),
	}

	if sig_optional {
		env["TAG_SIG_OPTIONAL"] = "1"
	}

	client := drone.NewClient(droneEndpoint, droneConfig)
	result, err := client.BuildCreate("teach", "atsebay.t-worker", "", "master", env)
	if err != nil {
		log.Println("Unable to communicate with Drone:", err.Error())
		c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Unable to communication with the extraction service."})
		return
	}

	repo.DroneRef = fmt.Sprintf("%s/%s/%d", "teach", "atsebay.t-worker", result.Number)
	repo.LastCheck = &now
	repo.Update()

	repo.Secret = []byte{}
	c.JSON(http.StatusOK, repo)
}

func TriggerTests(c *gin.Context, work *Work, repo *Repository, u *User) {
	if work.GradationRepo == nil || len(*work.GradationRepo) == 0 {
		c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"errmsg": "No tests defined for this work."})
		return
	}

	slug := strings.SplitN(*work.GradationRepo, "/", 2)
	if len(slug) != 2 {
		c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"errmsg": "Graduation repository is invalid."})
		return
	}

	login := u.Login
	groups := u.Groups
	if u.Id != repo.IdUser {
		user, _ := getUser(int(repo.IdUser))
		if user != nil {
			login = user.Login
			groups = user.Groups
		}
	}

	branch := "master"
	if len(work.Tag) > 0 {
		branch = work.Tag
	}
	if branch[len(branch)-1] == '-' {
		branch += "grades"
	}

	s, err := s3NewSession()
	if err != nil {
		c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Something goes wrong."})
		return
	}

	req, _ := s3.New(s).GetObjectRequest(&s3.GetObjectInput{
		Bucket: aws.String(s3_bucket),
		Key:    aws.String(filepath.Join(fmt.Sprintf("%d", work.Id), fmt.Sprintf("rendu-%s.tar.xz", login))),
	})

	url, err := req.Presign(SharingTime * 20)
	if err != nil {
		log.Println("Unable to create presign URL:", err)
		c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Something goes wrong when creating the presigned URL."})
		return
	}

	env := map[string]string{
		"SUBMISSION_URL": repo.URI,
		"REPO_ID":        fmt.Sprintf("%d", repo.Id),
		"META_URL":       url,
		"LOGIN":          login,
		"GROUPS":         groups,
		"DEST":           fmt.Sprintf("%d", work.Id),
	}

	client := drone.NewClient(droneEndpoint, droneConfig)
	result, err := client.BuildCreate(slug[0], slug[1], "", branch, env)
	if err != nil {
		log.Println("Unable to communicate with Drone:", err.Error())
		c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Unable to communication with the gradation service."})
		return
	}

	now := time.Now()
	repo.TestsRef = fmt.Sprintf("%s/%d", *work.GradationRepo, result.Number)
	repo.LastTests = &now
	repo.Update()

	repo.Secret = []byte{}
	c.JSON(http.StatusOK, repo)
}

func (w *Work) stopTests() error {
	repos, err := w.GetRepositories()
	if err != nil {
		return err
	}

	client := drone.NewClient(droneEndpoint, droneConfig)
	for _, repo := range repos {
		slug := strings.Split(repo.TestsRef, "/")
		if len(slug) < 3 {
			continue
		}

		buildn, err := strconv.ParseInt(slug[2], 10, 32)
		if err != nil {
			continue
		}

		build, err := client.Build(slug[0], slug[1], int(buildn))
		if err != nil {
			log.Println("Unable to communicate with Drone:", err.Error())
			continue
		}

		if build.Status == "pending" {
			err := client.BuildCancel(slug[0], slug[1], int(buildn))
			if err != nil {
				log.Println("Unable to cancel the build:", err.Error())
				continue
			}
		}
	}

	return nil
}

type Repository struct {
	Id          int64      `json:"id"`
	IdUser      int64      `json:"id_user"`
	IdWork      int64      `json:"id_work"`
	URI         string     `json:"uri"`
	Secret      []byte     `json:"secret,omitempty"`
	LastCheck   *time.Time `json:"last_check"`
	DroneRef    string     `json:"drone_ref,omitempty"`
	LastTests   *time.Time `json:"last_tests"`
	TestsRef    string     `json:"tests_ref,omitempty"`
	AlreadyUsed bool       `json:"already_used,omitempty"`
}

func (u *User) GetRepositories() (repositories []*Repository, err error) {
	if rows, errr := DBQuery("SELECT id_repository, id_user, id_work, uri, secret, last_check, droneref, last_tests, testsref FROM user_work_repositories WHERE id_user=?", u.Id); errr != nil {
		return nil, errr
	} else {
		defer rows.Close()

		for rows.Next() {
			var repo Repository
			if err = rows.Scan(&repo.Id, &repo.IdUser, &repo.IdWork, &repo.URI, &repo.Secret, &repo.LastCheck, &repo.DroneRef, &repo.LastTests, &repo.TestsRef); err != nil {
				return
			}
			repositories = append(repositories, &repo)
		}
		if err = rows.Err(); err != nil {
			return
		}

		return
	}
}

func (w *Work) GetRepositories() (repositories []*Repository, err error) {
	if rows, errr := DBQuery("SELECT id_repository, id_user, id_work, uri, secret, last_check, droneref, last_tests, testsref FROM user_work_repositories WHERE id_work=?", w.Id); errr != nil {
		return nil, errr
	} else {
		defer rows.Close()

		for rows.Next() {
			var repo Repository
			if err = rows.Scan(&repo.Id, &repo.IdUser, &repo.IdWork, &repo.URI, &repo.Secret, &repo.LastCheck, &repo.DroneRef, &repo.LastTests, &repo.TestsRef); err != nil {
				return
			}
			repositories = append(repositories, &repo)
		}
		if err = rows.Err(); err != nil {
			return
		}

		return
	}
}

func getRepositoriesByURI(uri string) (repositories []*Repository, err error) {
	if rows, errr := DBQuery("SELECT id_repository, id_user, id_work, uri, secret, last_check, droneref, last_tests, testsref FROM user_work_repositories WHERE uri=?", uri); errr != nil {
		return nil, errr
	} else {
		defer rows.Close()

		for rows.Next() {
			var repo Repository
			if err = rows.Scan(&repo.Id, &repo.IdUser, &repo.IdWork, &repo.URI, &repo.Secret, &repo.LastCheck, &repo.DroneRef, &repo.LastTests, &repo.TestsRef); err != nil {
				return
			}
			repositories = append(repositories, &repo)
		}
		if err = rows.Err(); err != nil {
			return
		}

		return
	}
}

func getRepository(id int) (r *Repository, err error) {
	r = new(Repository)
	err = DBQueryRow("SELECT id_repository, id_user, id_work, uri, secret, last_check, droneref, last_tests, testsref FROM user_work_repositories WHERE id_repository=?", id).Scan(&r.Id, &r.IdUser, &r.IdWork, &r.URI, &r.Secret, &r.LastCheck, &r.DroneRef, &r.LastTests, &r.TestsRef)
	return
}

func (u *User) getRepository(id int) (r *Repository, err error) {
	r = new(Repository)
	err = DBQueryRow("SELECT id_repository, id_user, id_work, uri, secret, last_check, droneref, last_tests, testsref FROM user_work_repositories WHERE id_repository=? AND id_user=?", id, u.Id).Scan(&r.Id, &r.IdUser, &r.IdWork, &r.URI, &r.Secret, &r.LastCheck, &r.DroneRef, &r.LastTests, &r.TestsRef)
	return
}

func (u *User) getRepositoryByWork(id int64) (r *Repository, err error) {
	r = new(Repository)
	err = DBQueryRow("SELECT id_repository, id_user, id_work, uri, secret, last_check, droneref, last_tests, testsref FROM user_work_repositories WHERE id_work=? AND id_user=? ORDER BY last_tests DESC LIMIT 1", id, u.Id).Scan(&r.Id, &r.IdUser, &r.IdWork, &r.URI, &r.Secret, &r.LastCheck, &r.DroneRef, &r.LastTests, &r.TestsRef)
	return
}

func (u *User) NewRepository(w *Work, uri string) (*Repository, error) {
	secret := make([]byte, 24)
	_, err := rand.Read(secret)
	if err != nil {
		return nil, err
	}

	if res, err := DBExec("INSERT INTO user_work_repositories (id_user, id_work, uri, secret, droneref, testsref) VALUES (?, ?, ?, ?, ?, '')", u.Id, w.Id, uri, secret, ""); err != nil {
		return nil, err
	} else if rid, err := res.LastInsertId(); err != nil {
		return nil, err
	} else {
		return &Repository{rid, u.Id, w.Id, uri, secret, nil, "", nil, "", false}, nil
	}
}

func (r *Repository) Update() (*Repository, error) {
	if _, err := DBExec("UPDATE user_work_repositories SET id_user = ?, id_work = ?, uri = ?, secret = ?, last_check = ?, droneref = ?, last_tests = ?, testsref = ? WHERE id_repository = ?", r.IdUser, r.IdWork, r.URI, r.Secret, r.LastCheck, r.DroneRef, r.LastTests, r.TestsRef, r.Id); err != nil {
		return nil, err
	} else {
		return r, err
	}
}

func (r Repository) Delete() (int64, error) {
	if res, err := DBExec("DELETE FROM user_work_repositories WHERE id_repository = ?", r.Id); err != nil {
		return 0, err
	} else if nb, err := res.RowsAffected(); err != nil {
		return 0, err
	} else {
		return nb, err
	}
}

func ClearRepositories() (int64, error) {
	if res, err := DBExec("DELETE FROM user_work_repositories"); err != nil {
		return 0, err
	} else if nb, err := res.RowsAffected(); err != nil {
		return 0, err
	} else {
		return nb, err
	}
}