Add CRUD service for the Repository entity

Context

In !115 (merged) we created the models for the database entities. Now we need to create CRUD methods for these entities in the form of service interfaces. This MR creates the foundation for these services, using the Repository entity as example.

Related to #63 (closed).

Rational

Service Contract

Each service interface is split in two, <Entity>Reader and <Entity>Writer, encompassed in a single <Entity>Store interface which can then be used everywhere. For the Repository entity, this means:

// RepositoryReader is the interface that defines read operations for a repository store.
type RepositoryReader interface {
	FindAll(ctx context.Context) (models.Repositories, error)
	FindByID(ctx context.Context, id int) (*models.Repository, error)
	FindByPath(ctx context.Context, path string) (*models.Repository, error)
	FindDescendantsOf(ctx context.Context, id int) (models.Repositories, error)
	FindAncestorsOf(ctx context.Context, id int) (models.Repositories, error)
	FindSiblingsOf(ctx context.Context, id int) (models.Repositories, error)
	Count(ctx context.Context) (int, error)
}

// RepositoryWriter is the interface that defines write operations for a repository store.
type RepositoryWriter interface {
	Create(ctx context.Context, r *models.Repository) error
	Update(ctx context.Context, r *models.Repository) error
	SoftDelete(ctx context.Context, r *models.Repository) error
	Delete(ctx context.Context, id int) error
}

// RepositoryStore is the interface that a repository store should conform to.
type RepositoryStore interface {
	RepositoryReader
	RepositoryWriter
}

This organization allow us to easily compose and test each interface as needed, abstracting the implementation details (database/sql logic) for manipulating a given entity.

Transactional Composition

Additionally to the entity specific interfaces, a Queryer interface was introduced to abstract the differences between a database connection handle (sql.DB), a database transaction (sql.Tx) and even a single database connection (sql.Conn):

// Queryer is the common interface to execute queries on a database.
type Queryer interface {
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
}

Specific types that conform to this interface were then created as well:

// DB is a database handle that implements Queryer.
type DB struct {
	*sql.DB
	dsn *DSN
}

// Tx is a database transaction that implements Queryer.
type Tx struct {
	*sql.Tx
}

With this interface we can then build the concrete <Entity>Store objects, encapsulating a Queryer instead of a DB or Tx. For example, for the Repository entity we do:

// repositoryStore is the concrete implementation of a RepositoryStore.
type repositoryStore struct {
	// db can be either a *sql.DB or *sql.Tx
	db database.Queryer
}

// NewRepositoryStore builds a new repositoryStore.
func NewRepositoryStore(db database.Queryer) *repositoryStore {
	return &repositoryStore{db: db}
}

This means that we can not only use a regular sql.DB as the engine of an entity CRUD service, but we can also use a sql.Tx, allowing us to do transactional composition. An example:

db, _ := datastore.Open(...)
tx, _ := db.BeginTx(...)

// build services
rs := NewRepositoryStore(tx)
ms := NewManifestStore(tx)

// do operations within a transaction
rs.Create(...)
// ...

// commit or rollback
tx.Commit()
// or 
tx.Rollback()

Context Propagation and Cancellation

In order to allow queries to be cancelled while they're running (say a dependent operation fails, or the client cancels the originating API request) all service interface methods take a context.Context as first argument. This context is then passed to the underlying database/sql calls.

Testing

A new set of files and utility methods were introduced to support the creation of integration tests for each service. These rely on test fixtures, which are stored in plain SQL files in testdata/fixtures/<table name>.sql and automatically loaded by the test suite utility methods. It's preferred to load fixtures by SQL rather than building entity objects and then inserting them through the service (if the service is wrong, the inserted data will be wrong as well, which invalidates the whole test case).

Next Steps

Other Entities

The next step is to apply the same concepts to build the remaining entity CRUD services. This will allow us to test the proposed strategy when using dependent/nested entities. This will be done in separate MRs under #63 (closed).

Refactoring and Reorganization

I see the possibility for refactoring and possibly a reorganization of the packages and utility methods. However, it's better to do this when we have the other entity services, so that we can spot repeated patterns/issues across all of them. This will be done in separate MRs under #63 (closed).