github.com/pf-qiu/concourse/v6@v6.7.3-0.20201207032516-1f455d73275f/atc/db/volume_repository.go (about)

     1  package db
     2  
     3  import (
     4  	"database/sql"
     5  	"fmt"
     6  	"time"
     7  
     8  	sq "github.com/Masterminds/squirrel"
     9  	uuid "github.com/nu7hatch/gouuid"
    10  )
    11  
    12  //go:generate counterfeiter . VolumeRepository
    13  
    14  type VolumeRepository interface {
    15  	GetTeamVolumes(teamID int) ([]CreatedVolume, error)
    16  
    17  	CreateContainerVolume(teamID int, workerName string, container CreatingContainer, mountPath string) (CreatingVolume, error)
    18  	FindContainerVolume(teamID int, workerName string, container CreatingContainer, mountPath string) (CreatingVolume, CreatedVolume, error)
    19  
    20  	FindBaseResourceTypeVolume(*UsedWorkerBaseResourceType) (CreatingVolume, CreatedVolume, error)
    21  	CreateBaseResourceTypeVolume(*UsedWorkerBaseResourceType) (CreatingVolume, error)
    22  
    23  	FindResourceCacheVolume(workerName string, resourceCache UsedResourceCache) (CreatedVolume, bool, error)
    24  
    25  	FindTaskCacheVolume(teamID int, workerName string, taskCache UsedTaskCache) (CreatedVolume, bool, error)
    26  	CreateTaskCacheVolume(teamID int, uwtc *UsedWorkerTaskCache) (CreatingVolume, error)
    27  
    28  	FindResourceCertsVolume(workerName string, uwrc *UsedWorkerResourceCerts) (CreatingVolume, CreatedVolume, error)
    29  	CreateResourceCertsVolume(workerName string, uwrc *UsedWorkerResourceCerts) (CreatingVolume, error)
    30  
    31  	FindVolumesForContainer(container CreatedContainer) ([]CreatedVolume, error)
    32  	GetOrphanedVolumes() ([]CreatedVolume, error)
    33  
    34  	DestroyFailedVolumes() (count int, err error)
    35  
    36  	GetDestroyingVolumes(workerName string) ([]string, error)
    37  
    38  	CreateVolume(int, string, VolumeType) (CreatingVolume, error)
    39  	FindCreatedVolume(handle string) (CreatedVolume, bool, error)
    40  
    41  	RemoveDestroyingVolumes(workerName string, handles []string) (int, error)
    42  
    43  	UpdateVolumesMissingSince(workerName string, handles []string) error
    44  	RemoveMissingVolumes(gracePeriod time.Duration) (removed int, err error)
    45  
    46  	DestroyUnknownVolumes(workerName string, handles []string) (int, error)
    47  }
    48  
    49  const noTeam = 0
    50  
    51  type volumeRepository struct {
    52  	conn Conn
    53  }
    54  
    55  func NewVolumeRepository(conn Conn) VolumeRepository {
    56  	return &volumeRepository{
    57  		conn: conn,
    58  	}
    59  }
    60  
    61  func (repository *volumeRepository) queryVolumeHandles(tx Tx, cond sq.Eq) ([]string, error) {
    62  	query, args, err := psql.Select("handle").From("volumes").Where(cond).ToSql()
    63  	if err != nil {
    64  		return nil, err
    65  	}
    66  
    67  	rows, err := tx.Query(query, args...)
    68  	if err != nil {
    69  		return nil, err
    70  	}
    71  
    72  	defer Close(rows)
    73  
    74  	var handles []string
    75  
    76  	for rows.Next() {
    77  		var handle = "handle"
    78  		columns := []interface{}{&handle}
    79  
    80  		err = rows.Scan(columns...)
    81  		if err != nil {
    82  			return nil, err
    83  		}
    84  		handles = append(handles, handle)
    85  	}
    86  
    87  	return handles, nil
    88  }
    89  
    90  func (repository *volumeRepository) UpdateVolumesMissingSince(workerName string, reportedHandles []string) error {
    91  	// clear out missing_since for reported volumes
    92  	query, args, err := psql.Update("volumes").
    93  		Set("missing_since", nil).
    94  		Where(sq.And{
    95  			sq.Eq{"handle": reportedHandles},
    96  			sq.NotEq{"missing_since": nil},
    97  		},
    98  		).ToSql()
    99  	if err != nil {
   100  		return err
   101  	}
   102  
   103  	tx, err := repository.conn.Begin()
   104  	if err != nil {
   105  		return err
   106  	}
   107  
   108  	defer Rollback(tx)
   109  
   110  	_, err = tx.Exec(query, args...)
   111  	if err != nil {
   112  		return err
   113  	}
   114  
   115  	dbHandles, err := repository.queryVolumeHandles(
   116  		tx,
   117  		sq.Eq{
   118  			"worker_name":   workerName,
   119  			"missing_since": nil,
   120  		})
   121  	if err != nil {
   122  		return err
   123  	}
   124  
   125  	handles := diff(dbHandles, reportedHandles)
   126  
   127  	query, args, err = psql.Update("volumes").
   128  		Set("missing_since", sq.Expr("now()")).
   129  		Where(sq.And{
   130  			sq.Eq{"handle": handles},
   131  			sq.NotEq{"state": VolumeStateCreating},
   132  		}).ToSql()
   133  	if err != nil {
   134  		return err
   135  	}
   136  
   137  	_, err = tx.Exec(query, args...)
   138  	if err != nil {
   139  		return err
   140  	}
   141  
   142  	return tx.Commit()
   143  }
   144  
   145  // Removes any volumes that exist in the database but are missing on the worker
   146  // for over the designated grace time period.
   147  func (repository *volumeRepository) RemoveMissingVolumes(gracePeriod time.Duration) (int, error) {
   148  	tx, err := repository.conn.Begin()
   149  	if err != nil {
   150  		return 0, err
   151  	}
   152  
   153  	defer tx.Rollback()
   154  
   155  	// Setting the foreign key constraint to deferred, meaning that the foreign
   156  	// key constraint will not be executed until the end of the transaction. This
   157  	// allows the gc query to remove any parent volumes as long as the child
   158  	// volume that references it is also removed within the same transaction.
   159  	_, err = tx.Exec("SET CONSTRAINTS volumes_parent_id_fkey DEFERRED")
   160  	if err != nil {
   161  		return 0, err
   162  	}
   163  
   164  	result, err := tx.Exec(`
   165  	WITH RECURSIVE missing(id) AS (
   166  		SELECT id FROM volumes WHERE missing_since IS NOT NULL and NOW() - missing_since > $1 AND state IN ($2, $3)
   167  	UNION ALL
   168  		SELECT v.id FROM missing m, volumes v WHERE v.parent_id = m.id
   169  	)
   170  	DELETE FROM volumes v USING missing m WHERE m.id = v.id`, fmt.Sprintf("%.0f seconds", gracePeriod.Seconds()), VolumeStateCreated, VolumeStateFailed)
   171  	if err != nil {
   172  		return 0, err
   173  	}
   174  
   175  	err = tx.Commit()
   176  	if err != nil {
   177  		return 0, err
   178  	}
   179  
   180  	affected, err := result.RowsAffected()
   181  	if err != nil {
   182  		return 0, err
   183  	}
   184  
   185  	return int(affected), nil
   186  }
   187  
   188  func (repository *volumeRepository) RemoveDestroyingVolumes(workerName string, handles []string) (int, error) {
   189  	rows, err := psql.Delete("volumes").
   190  		Where(
   191  			sq.And{
   192  				sq.Eq{
   193  					"worker_name": workerName,
   194  				},
   195  				sq.NotEq{
   196  					"handle": handles,
   197  				},
   198  				sq.Eq{
   199  					"state": VolumeStateDestroying,
   200  				},
   201  			},
   202  		).RunWith(repository.conn).
   203  		Exec()
   204  
   205  	if err != nil {
   206  		return 0, err
   207  	}
   208  
   209  	affected, err := rows.RowsAffected()
   210  	if err != nil {
   211  		return 0, err
   212  	}
   213  
   214  	return int(affected), nil
   215  }
   216  
   217  func (repository *volumeRepository) GetTeamVolumes(teamID int) ([]CreatedVolume, error) {
   218  	query, args, err := psql.Select(volumeColumns...).
   219  		From("volumes v").
   220  		LeftJoin("workers w ON v.worker_name = w.name").
   221  		LeftJoin("containers c ON v.container_id = c.id").
   222  		LeftJoin("volumes pv ON v.parent_id = pv.id").
   223  		LeftJoin("worker_resource_caches wrc ON wrc.id = v.worker_resource_cache_id").
   224  		LeftJoin("worker_resource_certs  certs ON certs.id = v.worker_resource_certs_id").
   225  		Where(sq.Or{
   226  			sq.Eq{
   227  				"v.team_id": teamID,
   228  			},
   229  			sq.Eq{
   230  				"v.team_id": nil,
   231  			},
   232  		}).
   233  		Where(sq.Eq{
   234  			"v.state": VolumeStateCreated,
   235  		}).ToSql()
   236  	if err != nil {
   237  		return nil, err
   238  	}
   239  
   240  	rows, err := repository.conn.Query(query, args...)
   241  	if err != nil {
   242  		return nil, err
   243  	}
   244  	defer Close(rows)
   245  
   246  	var createdVolumes []CreatedVolume
   247  
   248  	for rows.Next() {
   249  		_, createdVolume, _, _, err := scanVolume(rows, repository.conn)
   250  		if err != nil {
   251  			return nil, err
   252  		}
   253  
   254  		createdVolumes = append(createdVolumes, createdVolume)
   255  	}
   256  
   257  	return createdVolumes, nil
   258  }
   259  
   260  func (repository *volumeRepository) CreateBaseResourceTypeVolume(uwbrt *UsedWorkerBaseResourceType) (CreatingVolume, error) {
   261  	volume, err := repository.createVolume(
   262  		noTeam,
   263  		uwbrt.WorkerName,
   264  		map[string]interface{}{
   265  			"worker_base_resource_type_id": uwbrt.ID,
   266  		},
   267  		VolumeTypeResourceType,
   268  	)
   269  	if err != nil {
   270  		return nil, err
   271  	}
   272  
   273  	volume.workerBaseResourceTypeID = uwbrt.ID
   274  	return volume, nil
   275  }
   276  
   277  func (repository *volumeRepository) CreateVolume(teamID int, workerName string, volumeType VolumeType) (CreatingVolume, error) {
   278  	volume, err := repository.createVolume(
   279  		0,
   280  		workerName,
   281  		map[string]interface{}{
   282  			"team_id": teamID,
   283  		},
   284  		volumeType,
   285  	)
   286  	if err != nil {
   287  		return nil, err
   288  	}
   289  
   290  	return volume, nil
   291  }
   292  
   293  func (repository *volumeRepository) CreateContainerVolume(teamID int, workerName string, container CreatingContainer, mountPath string) (CreatingVolume, error) {
   294  	volume, err := repository.createVolume(
   295  		teamID,
   296  		workerName,
   297  		map[string]interface{}{
   298  			"container_id": container.ID(),
   299  			"path":         mountPath,
   300  		},
   301  		VolumeTypeContainer,
   302  	)
   303  	if err != nil {
   304  		return nil, err
   305  	}
   306  
   307  	volume.path = mountPath
   308  	volume.containerHandle = container.Handle()
   309  	return volume, nil
   310  }
   311  
   312  func (repository *volumeRepository) FindVolumesForContainer(container CreatedContainer) ([]CreatedVolume, error) {
   313  	query, args, err := psql.Select(volumeColumns...).
   314  		From("volumes v").
   315  		LeftJoin("workers w ON v.worker_name = w.name").
   316  		LeftJoin("containers c ON v.container_id = c.id").
   317  		LeftJoin("volumes pv ON v.parent_id = pv.id").
   318  		LeftJoin("worker_resource_caches wrc ON wrc.id = v.worker_resource_cache_id").
   319  		Where(sq.Eq{
   320  			"v.state":        VolumeStateCreated,
   321  			"v.container_id": container.ID(),
   322  		}).
   323  		ToSql()
   324  	if err != nil {
   325  		return nil, err
   326  	}
   327  
   328  	rows, err := repository.conn.Query(query, args...)
   329  	if err != nil {
   330  		return nil, err
   331  	}
   332  	defer Close(rows)
   333  
   334  	var createdVolumes []CreatedVolume
   335  
   336  	for rows.Next() {
   337  		_, createdVolume, _, _, err := scanVolume(rows, repository.conn)
   338  		if err != nil {
   339  			return nil, err
   340  		}
   341  
   342  		createdVolumes = append(createdVolumes, createdVolume)
   343  	}
   344  
   345  	return createdVolumes, nil
   346  }
   347  
   348  func (repository *volumeRepository) FindContainerVolume(teamID int, workerName string, container CreatingContainer, mountPath string) (CreatingVolume, CreatedVolume, error) {
   349  	return repository.findVolume(teamID, workerName, map[string]interface{}{
   350  		"v.container_id": container.ID(),
   351  		"v.path":         mountPath,
   352  	})
   353  }
   354  
   355  func (repository *volumeRepository) FindBaseResourceTypeVolume(uwbrt *UsedWorkerBaseResourceType) (CreatingVolume, CreatedVolume, error) {
   356  	return repository.findVolume(0, uwbrt.WorkerName, map[string]interface{}{
   357  		"v.worker_base_resource_type_id": uwbrt.ID,
   358  	})
   359  }
   360  
   361  func (repository *volumeRepository) FindTaskCacheVolume(teamID int, workerName string, taskCache UsedTaskCache) (CreatedVolume, bool, error) {
   362  	usedWorkerTaskCache, found, err := WorkerTaskCache{
   363  		WorkerName: workerName,
   364  		TaskCache:  taskCache,
   365  	}.find(repository.conn)
   366  
   367  	if err != nil {
   368  		return nil, false, err
   369  	}
   370  
   371  	if !found {
   372  		return nil, false, nil
   373  	}
   374  
   375  	_, createdVolume, err := repository.findVolume(teamID, workerName, map[string]interface{}{
   376  		"v.worker_task_cache_id": usedWorkerTaskCache.ID,
   377  	})
   378  	if err != nil {
   379  		return nil, false, err
   380  	}
   381  
   382  	if createdVolume == nil {
   383  		return nil, false, nil
   384  	}
   385  
   386  	return createdVolume, true, nil
   387  }
   388  
   389  func (repository *volumeRepository) CreateTaskCacheVolume(teamID int, uwtc *UsedWorkerTaskCache) (CreatingVolume, error) {
   390  	volume, err := repository.createVolume(
   391  		teamID,
   392  		uwtc.WorkerName,
   393  		map[string]interface{}{
   394  			"worker_task_cache_id": uwtc.ID,
   395  		},
   396  		VolumeTypeTaskCache,
   397  	)
   398  	if err != nil {
   399  		return nil, err
   400  	}
   401  
   402  	volume.workerTaskCacheID = uwtc.ID
   403  	return volume, nil
   404  }
   405  
   406  func (repository *volumeRepository) FindResourceCertsVolume(workerName string, uwrc *UsedWorkerResourceCerts) (CreatingVolume, CreatedVolume, error) {
   407  	return repository.findVolume(0, workerName, map[string]interface{}{
   408  		"v.worker_resource_certs_id": uwrc.ID,
   409  	})
   410  }
   411  
   412  func (repository *volumeRepository) CreateResourceCertsVolume(workerName string, uwrc *UsedWorkerResourceCerts) (CreatingVolume, error) {
   413  	volume, err := repository.createVolume(
   414  		noTeam,
   415  		workerName,
   416  		map[string]interface{}{
   417  			"worker_resource_certs_id": uwrc.ID,
   418  		},
   419  		VolumeTypeResourceCerts,
   420  	)
   421  	if err != nil {
   422  		return nil, err
   423  	}
   424  
   425  	return volume, nil
   426  }
   427  
   428  func (repository *volumeRepository) FindResourceCacheVolume(workerName string, resourceCache UsedResourceCache) (CreatedVolume, bool, error) {
   429  	workerResourceCache, found, err := WorkerResourceCache{
   430  		WorkerName:    workerName,
   431  		ResourceCache: resourceCache,
   432  	}.Find(repository.conn)
   433  	if err != nil {
   434  		return nil, false, err
   435  	}
   436  
   437  	if !found {
   438  		return nil, false, nil
   439  	}
   440  
   441  	_, createdVolume, err := repository.findVolume(0, workerName, map[string]interface{}{
   442  		"v.worker_resource_cache_id": workerResourceCache.ID,
   443  	})
   444  	if err != nil {
   445  		return nil, false, err
   446  	}
   447  
   448  	if createdVolume == nil {
   449  		return nil, false, nil
   450  	}
   451  
   452  	return createdVolume, true, nil
   453  }
   454  
   455  func (repository *volumeRepository) FindCreatedVolume(handle string) (CreatedVolume, bool, error) {
   456  	_, createdVolume, err := getVolume(repository.conn, map[string]interface{}{
   457  		"v.handle": handle,
   458  	})
   459  	if err != nil {
   460  		return nil, false, err
   461  	}
   462  
   463  	if createdVolume == nil {
   464  		return nil, false, nil
   465  	}
   466  
   467  	return createdVolume, true, nil
   468  }
   469  
   470  func (repository *volumeRepository) GetOrphanedVolumes() ([]CreatedVolume, error) {
   471  	query, args, err := psql.Select(volumeColumns...).
   472  		From("volumes v").
   473  		LeftJoin("workers w ON v.worker_name = w.name").
   474  		LeftJoin("containers c ON v.container_id = c.id").
   475  		LeftJoin("volumes pv ON v.parent_id = pv.id").
   476  		LeftJoin("worker_resource_caches wrc ON wrc.id = v.worker_resource_cache_id").
   477  		Where(
   478  			sq.Eq{
   479  				"v.worker_resource_cache_id":     nil,
   480  				"v.worker_base_resource_type_id": nil,
   481  				"v.container_id":                 nil,
   482  				"v.worker_task_cache_id":         nil,
   483  				"v.worker_resource_certs_id":     nil,
   484  				"v.worker_artifact_id":           nil,
   485  			},
   486  		).
   487  		Where(sq.Eq{"v.state": string(VolumeStateCreated)}).
   488  		Where(sq.Or{
   489  			sq.Eq{"w.state": string(WorkerStateRunning)},
   490  			sq.Eq{"w.state": string(WorkerStateLanding)},
   491  			sq.Eq{"w.state": string(WorkerStateRetiring)},
   492  		}).
   493  		ToSql()
   494  	if err != nil {
   495  		return nil, err
   496  	}
   497  
   498  	rows, err := repository.conn.Query(query, args...)
   499  	if err != nil {
   500  		return nil, err
   501  	}
   502  	defer Close(rows)
   503  
   504  	var createdVolumes []CreatedVolume
   505  
   506  	for rows.Next() {
   507  		_, createdVolume, _, _, err := scanVolume(rows, repository.conn)
   508  
   509  		if err != nil {
   510  			return nil, err
   511  		}
   512  
   513  		if createdVolume != nil {
   514  			createdVolumes = append(createdVolumes, createdVolume)
   515  		}
   516  
   517  	}
   518  
   519  	return createdVolumes, nil
   520  }
   521  
   522  func (repository *volumeRepository) DestroyFailedVolumes() (int, error) {
   523  	queryId, args, err := psql.Select("v.id").
   524  		From("volumes v").
   525  		LeftJoin("workers w ON v.worker_name = w.name").
   526  		LeftJoin("containers c ON v.container_id = c.id").
   527  		LeftJoin("volumes pv ON v.parent_id = pv.id").
   528  		LeftJoin("worker_resource_caches wrc ON wrc.id = v.worker_resource_cache_id").
   529  		Where(sq.Eq{
   530  			"v.state": string(VolumeStateFailed),
   531  		}).
   532  		ToSql()
   533  	if err != nil {
   534  		return 0, err
   535  	}
   536  
   537  	rows, err := sq.Delete("volumes").
   538  		Where("id IN ("+queryId+")", args...).
   539  		RunWith(repository.conn).
   540  		Exec()
   541  	if err != nil {
   542  		return 0, err
   543  	}
   544  
   545  	failedVolumeLen, err := rows.RowsAffected()
   546  	if err != nil {
   547  		return 0, err
   548  	}
   549  
   550  	return int(failedVolumeLen), nil
   551  }
   552  
   553  func (repository *volumeRepository) GetDestroyingVolumes(workerName string) ([]string, error) {
   554  	tx, err := repository.conn.Begin()
   555  	if err != nil {
   556  		return nil, err
   557  	}
   558  
   559  	defer Rollback(tx)
   560  
   561  	volumes, err := repository.queryVolumeHandles(
   562  		tx,
   563  		sq.Eq{
   564  			"state":       string(VolumeStateDestroying),
   565  			"worker_name": workerName,
   566  		},
   567  	)
   568  	if err != nil {
   569  		return nil, err
   570  	}
   571  
   572  	err = tx.Commit()
   573  	if err != nil {
   574  		return nil, err
   575  	}
   576  
   577  	return volumes, nil
   578  }
   579  
   580  func (repository *volumeRepository) DestroyUnknownVolumes(workerName string, reportedHandles []string) (int, error) {
   581  	tx, err := repository.conn.Begin()
   582  	if err != nil {
   583  		return 0, err
   584  	}
   585  
   586  	defer Rollback(tx)
   587  	dbHandles, err := repository.queryVolumeHandles(tx, sq.Eq{
   588  		"worker_name": workerName,
   589  	})
   590  	if err != nil {
   591  		return 0, err
   592  	}
   593  
   594  	unknownHandles := diff(reportedHandles, dbHandles)
   595  
   596  	if len(unknownHandles) == 0 {
   597  		return 0, nil
   598  	}
   599  
   600  	insertBuilder := psql.Insert("volumes").Columns(
   601  		"handle",
   602  		"worker_name",
   603  		"state",
   604  	)
   605  
   606  	for _, unknownHandle := range unknownHandles {
   607  		insertBuilder = insertBuilder.Values(
   608  			unknownHandle,
   609  			workerName,
   610  			VolumeStateDestroying,
   611  		)
   612  	}
   613  
   614  	_, err = insertBuilder.RunWith(tx).Exec()
   615  	if err != nil {
   616  		return 0, err
   617  	}
   618  
   619  	err = tx.Commit()
   620  	if err != nil {
   621  		return 0, err
   622  	}
   623  
   624  	return len(unknownHandles), nil
   625  }
   626  
   627  // 1. open tx
   628  // 2. lookup worker resource type id
   629  //   * if not found, fail; worker must have new version or no longer supports type
   630  // 3. insert into volumes in 'initializing' state
   631  //   * if fails (fkey violation; worker type gone), fail for same reason as 2.
   632  // 4. commit tx
   633  func (repository *volumeRepository) createVolume(
   634  	teamID int,
   635  	workerName string,
   636  	columns map[string]interface{},
   637  	volumeType VolumeType,
   638  ) (*creatingVolume, error) {
   639  	var volumeID int
   640  	handle, err := uuid.NewV4()
   641  	if err != nil {
   642  		return nil, err
   643  	}
   644  
   645  	columnNames := []string{"worker_name", "handle"}
   646  	columnValues := []interface{}{workerName, handle.String()}
   647  	for name, value := range columns {
   648  		columnNames = append(columnNames, name)
   649  		columnValues = append(columnValues, value)
   650  	}
   651  
   652  	if teamID != 0 {
   653  		columnNames = append(columnNames, "team_id")
   654  		columnValues = append(columnValues, teamID)
   655  	}
   656  
   657  	err = psql.Insert("volumes").
   658  		Columns(columnNames...). // hey, replace this with SetMap plz
   659  		Values(columnValues...).
   660  		Suffix("RETURNING id").
   661  		RunWith(repository.conn).
   662  		QueryRow().
   663  		Scan(&volumeID)
   664  	if err != nil {
   665  		return nil, err
   666  	}
   667  
   668  	return &creatingVolume{
   669  		workerName: workerName,
   670  
   671  		id:     volumeID,
   672  		handle: handle.String(),
   673  		typ:    volumeType,
   674  		teamID: teamID,
   675  
   676  		conn: repository.conn,
   677  	}, nil
   678  }
   679  
   680  func (repository *volumeRepository) findVolume(teamID int, workerName string, columns map[string]interface{}) (CreatingVolume, CreatedVolume, error) {
   681  	whereClause := sq.Eq{}
   682  	if teamID != 0 {
   683  		whereClause["v.team_id"] = teamID
   684  	}
   685  	if workerName != "" {
   686  		whereClause["v.worker_name"] = workerName
   687  	}
   688  
   689  	for name, value := range columns {
   690  		whereClause[name] = value
   691  	}
   692  
   693  	return getVolume(repository.conn, whereClause)
   694  }
   695  
   696  func getVolume(conn Conn, where map[string]interface{}) (CreatingVolume, CreatedVolume, error) {
   697  	row := psql.Select(volumeColumns...).
   698  		From("volumes v").
   699  		LeftJoin("workers w ON v.worker_name = w.name").
   700  		LeftJoin("containers c ON v.container_id = c.id").
   701  		LeftJoin("volumes pv ON v.parent_id = pv.id").
   702  		LeftJoin("worker_resource_caches wrc ON wrc.id = v.worker_resource_cache_id").
   703  		Where(where).
   704  		RunWith(conn).
   705  		QueryRow()
   706  
   707  	creatingVolume, createdVolume, _, _, err := scanVolume(row, conn)
   708  	if err != nil {
   709  		if err == sql.ErrNoRows {
   710  			return nil, nil, nil
   711  		}
   712  		return nil, nil, err
   713  	}
   714  
   715  	return creatingVolume, createdVolume, nil
   716  }
   717  
   718  var volumeColumns = []string{
   719  	"v.id",
   720  	"v.handle",
   721  	"v.state",
   722  	"w.name",
   723  	"v.path",
   724  	"c.handle",
   725  	"pv.handle",
   726  	"v.team_id",
   727  	"wrc.resource_cache_id",
   728  	"v.worker_base_resource_type_id",
   729  	"v.worker_task_cache_id",
   730  	"v.worker_resource_certs_id",
   731  	"v.worker_artifact_id",
   732  	`case
   733  	when v.worker_base_resource_type_id is not NULL then 'resource-type'
   734  	when v.worker_resource_cache_id is not NULL then 'resource'
   735  	when v.container_id is not NULL then 'container'
   736  	when v.worker_task_cache_id is not NULL then 'task-cache'
   737  	when v.worker_resource_certs_id is not NULL then 'resource-certs'
   738  	when v.worker_artifact_id is not NULL then 'artifact'
   739  	else 'unknown'
   740  end`,
   741  }
   742  
   743  func scanVolume(row sq.RowScanner, conn Conn) (CreatingVolume, CreatedVolume, DestroyingVolume, FailedVolume, error) {
   744  	var id int
   745  	var handle string
   746  	var state string
   747  	var workerName string
   748  	var sqPath sql.NullString
   749  	var sqContainerHandle sql.NullString
   750  	var sqParentHandle sql.NullString
   751  	var sqTeamID sql.NullInt64
   752  	var sqResourceCacheID sql.NullInt64
   753  	var sqWorkerBaseResourceTypeID sql.NullInt64
   754  	var sqWorkerTaskCacheID sql.NullInt64
   755  	var sqWorkerResourceCertsID sql.NullInt64
   756  	var sqWorkerArtifactID sql.NullInt64
   757  	var volumeType VolumeType
   758  
   759  	err := row.Scan(
   760  		&id,
   761  		&handle,
   762  		&state,
   763  		&workerName,
   764  		&sqPath,
   765  		&sqContainerHandle,
   766  		&sqParentHandle,
   767  		&sqTeamID,
   768  		&sqResourceCacheID,
   769  		&sqWorkerBaseResourceTypeID,
   770  		&sqWorkerTaskCacheID,
   771  		&sqWorkerResourceCertsID,
   772  		&sqWorkerArtifactID,
   773  		&volumeType,
   774  	)
   775  	if err != nil {
   776  		return nil, nil, nil, nil, err
   777  	}
   778  
   779  	var path string
   780  	if sqPath.Valid {
   781  		path = sqPath.String
   782  	}
   783  
   784  	var containerHandle string
   785  	if sqContainerHandle.Valid {
   786  		containerHandle = sqContainerHandle.String
   787  	}
   788  
   789  	var parentHandle string
   790  	if sqParentHandle.Valid {
   791  		parentHandle = sqParentHandle.String
   792  	}
   793  
   794  	var teamID int
   795  	if sqTeamID.Valid {
   796  		teamID = int(sqTeamID.Int64)
   797  	}
   798  
   799  	var resourceCacheID int
   800  	if sqResourceCacheID.Valid {
   801  		resourceCacheID = int(sqResourceCacheID.Int64)
   802  	}
   803  
   804  	var workerBaseResourceTypeID int
   805  	if sqWorkerBaseResourceTypeID.Valid {
   806  		workerBaseResourceTypeID = int(sqWorkerBaseResourceTypeID.Int64)
   807  	}
   808  
   809  	var workerTaskCacheID int
   810  	if sqWorkerTaskCacheID.Valid {
   811  		workerTaskCacheID = int(sqWorkerTaskCacheID.Int64)
   812  	}
   813  
   814  	var workerResourceCertsID int
   815  	if sqWorkerResourceCertsID.Valid {
   816  		workerResourceCertsID = int(sqWorkerResourceCertsID.Int64)
   817  	}
   818  
   819  	var workerArtifactID int
   820  	if sqWorkerArtifactID.Valid {
   821  		workerArtifactID = int(sqWorkerArtifactID.Int64)
   822  	}
   823  
   824  	switch VolumeState(state) {
   825  	case VolumeStateCreated:
   826  		return nil, &createdVolume{
   827  			id:                       id,
   828  			handle:                   handle,
   829  			typ:                      volumeType,
   830  			path:                     path,
   831  			teamID:                   teamID,
   832  			workerName:               workerName,
   833  			containerHandle:          containerHandle,
   834  			parentHandle:             parentHandle,
   835  			resourceCacheID:          resourceCacheID,
   836  			workerBaseResourceTypeID: workerBaseResourceTypeID,
   837  			workerTaskCacheID:        workerTaskCacheID,
   838  			workerResourceCertsID:    workerResourceCertsID,
   839  			workerArtifactID:         workerArtifactID,
   840  			conn:                     conn,
   841  		}, nil, nil, nil
   842  	case VolumeStateCreating:
   843  		return &creatingVolume{
   844  			id:                       id,
   845  			handle:                   handle,
   846  			typ:                      volumeType,
   847  			path:                     path,
   848  			teamID:                   teamID,
   849  			workerName:               workerName,
   850  			containerHandle:          containerHandle,
   851  			parentHandle:             parentHandle,
   852  			resourceCacheID:          resourceCacheID,
   853  			workerBaseResourceTypeID: workerBaseResourceTypeID,
   854  			workerTaskCacheID:        workerTaskCacheID,
   855  			workerResourceCertsID:    workerResourceCertsID,
   856  			workerArtifactID:         workerArtifactID,
   857  			conn:                     conn,
   858  		}, nil, nil, nil, nil
   859  	case VolumeStateDestroying:
   860  		return nil, nil, &destroyingVolume{
   861  			id:         id,
   862  			handle:     handle,
   863  			workerName: workerName,
   864  			conn:       conn,
   865  		}, nil, nil
   866  	case VolumeStateFailed:
   867  		return nil, nil, nil, &failedVolume{
   868  			id:         id,
   869  			handle:     handle,
   870  			workerName: workerName,
   871  			conn:       conn,
   872  		}, nil
   873  	}
   874  
   875  	return nil, nil, nil, nil, nil
   876  }