Improve file table schema by using hash

coder · kylecarbs · Feb 7, 2022 · Feb 7, 2022 · Feb 7, 2022 · Feb 7, 2022
commit 6e6beb471c9f8517f1088993199aa42a4024079c
diff --git a/database/databasefake/databasefake.go b/database/databasefake/databasefake.go
@@ -104,12 +104,12 @@ func (q *fakeQuerier) GetAPIKeyByID(_ context.Context, id string) (database.APIK
 	return database.APIKey{}, sql.ErrNoRows
 }
 
-func (q *fakeQuerier) GetFileByID(_ context.Context, id uuid.UUID) (database.File, error) {
+func (q *fakeQuerier) GetFileByHash(_ context.Context, hash string) (database.File, error) {
 	q.mutex.Lock()
 	defer q.mutex.Unlock()
 
 	for _, file := range q.files {
-		if file.ID.String() == id.String() {
+		if file.Hash == hash {
 			return file, nil
 		}
 	}
@@ -588,8 +588,9 @@ func (q *fakeQuerier) InsertFile(_ context.Context, arg database.InsertFileParam
 
 	//nolint:gosimple
 	file := database.File{
-		ID:        arg.ID,
+		Hash:      arg.Hash,
 		CreatedAt: arg.CreatedAt,
+		Mimetype:  arg.Mimetype,
 		Data:      arg.Data,
 	}
 	q.files = append(q.files, file)

diff --git a/database/dump.sql b/database/dump.sql
diff --git a/database/migrations/000002_projects.up.sql b/database/migrations/000002_projects.up.sql
@@ -1,7 +1,8 @@
 -- Store arbitrary data like project source code or avatars.
-CREATE TABLE files (
-    id uuid NOT NULL UNIQUE,
+CREATE TABLE file (
+    hash varchar(32) NOT NULL UNIQUE,
     created_at timestamptz NOT NULL,
+    mimetype varchar(64) NOT NULL,
     data bytea NOT NULL
 );
 

diff --git a/database/models.go b/database/models.go
diff --git a/database/querier.go b/database/querier.go
diff --git a/database/query.sql b/database/query.sql
@@ -46,13 +46,13 @@ WHERE
 LIMIT
   1;
 
--- name: GetFileByID :one
+-- name: GetFileByHash :one
 SELECT
   *
 FROM
-  files
+  file
 WHERE
-  id = $1
+  hash = $1
 LIMIT
   1;
 
@@ -364,9 +364,9 @@ VALUES
 
 -- name: InsertFile :one
 INSERT INTO
-  files (id, created_at, data)
+  file (hash, created_at, mimetype, data)
 VALUES
-  ($1, $2, $3) RETURNING *;
+  ($1, $2, $3, $4) RETURNING *;
 
 -- name: InsertProvisionerJobLogs :many
 INSERT INTO

diff --git a/database/query.sql.go b/database/query.sql.go