sql.go

package db

var selectUserByToken = `
SELECT
	u.id AS id,
	username,
	email,
	is_admin,
	is_blocked,
	bucket_capacity
FROM
	users u,
	tokens t
WHERE
	u.id = t.user_id AND
	t.token = $1
LIMIT 1
`

var countOfObjectByBucketAndRandomKey = `
SELECT
	COUNT(*) as count
FROM
	objects
WHERE
	bucket = $1 AND
	random_key = $2
`

var insertShortURL = `
INSERT INTO
	objects (bucket_key, bucket, key, random_key, dir, type, dest_url, content_type, associated_user)
VALUES
	($1, $2, $3, $4, '/', 1, $5, NULL, $6)
`

var insertFile = `
INSERT INTO
	objects (bucket_key, bucket, key, random_key, dir, content_type, content_length, md5_hash, sha256_hash, associated_user)
VALUES
	($1, $2, $3, $4, '/', $5, $6, $7, $8, $9)
`

var selectUserByUsernameOrEmail = `
SELECT
	id
FROM
	users
WHERE
	username_lower = $1 OR
	email = $2
`

var insertUser = `
INSERT INTO
	users (id, username, username_lower, email)
VALUES
	($1, $2, $3, $4)
`

var insertToken = `
INSERT INTO
	tokens (user_id, token)
VALUES
	($1, $2)
`

var listObjectsByAssociatedUser = `
SELECT
	bucket,
	key,
	dir,
	"type",
	dest_url,
	content_type,
	content_length,
	created_at,
	deleted_at,
	delete_reason,
	md5_hash,
	sha256_hash,
	associated_user
FROM
	objects
WHERE
	associated_user = $1 AND
	"type" != 2
ORDER BY
	created_at %s
LIMIT $2
OFFSET $3
`

var getObjectByBucketKey = `
SELECT
	bucket,
	key,
	dir,
	"type",
	dest_url,
	content_type,
	content_length,
	created_at,
	deleted_at,
	delete_reason,
	md5_hash,
	sha256_hash,
	associated_user
FROM
	objects
WHERE
	bucket_key = $1
LIMIT 1
`

var countOfObjectsBySHA256 = `
SELECT
	COUNT(*)
FROM
	objects
WHERE
	"type" = 0,
	sha256_hash = $1
`

var updateObjectToTombstoneByBucketKey = `
UPDATE
	objects
SET
	type = 2,
	deleted_at = CURRENT_TIMESTAMP,
	dest_url = NULL,
	content_type = NULL,
	content_length = NULL,
	delete_reason = $1,
	md5_hash = NULL,
	sha256_hash = NULL,
	associated_user = NULL
WHERE
	bucket_key = $2
`

var updateObjectToTombstoneKeepAssociatedUserByBucketKey = `
UPDATE
	objects
SET
	type = 2,
	deleted_at = CURRENT_TIMESTAMP,
	dest_url = NULL,
	content_type = NULL,
	content_length = NULL,
	delete_reason = $1,
	md5_hash = NULL,
	sha256_hash = NULL
WHERE
	bucket_key = $2
`

var updateObjectToTombstoneKeepHashesAndAssociatedUserByBucketKey = `
UPDATE
	objects
SET
	type = 2,
	deleted_at = CURRENT_TIMESTAMP,
	dest_url = NULL,
	content_type = NULL,
	content_length = NULL,
	delete_reason = $1
WHERE
	bucket_key = $2
`

var updateObjectToTombstoneBySHA256Hash = `
UPDATE
	objects
SET
	type = 2,
	deleted_at = CURRENT_TIMESTAMP,
	dest_url = NULL,
	content_type = NULL,
	content_length = NULL,
	delete_reason = $1,
	md5_hash = NULL,
	sha256_hash = NULL,
	associated_user = NULL
WHERE
	sha256_hash = $2
`

var updateObjectToTombstoneKeepAssociatedUserBySHA256Hash = `
UPDATE
	objects
SET
	type = 2,
	deleted_at = CURRENT_TIMESTAMP,
	dest_url = NULL,
	content_type = NULL,
	content_length = NULL,
	delete_reason = $1,
	md5_hash = NULL,
	sha256_hash = NULL
WHERE
	sha256_hash = $2
`

var updateObjectToTombstoneKeepHashesAndAssociatedUserBySHA256Hash = `
UPDATE
	objects
SET
	type = 2,
	deleted_at = CURRENT_TIMESTAMP,
	dest_url = NULL,
	content_type = NULL,
	content_length = NULL,
	delete_reason = $1
WHERE
	sha256_hash = $2
`

var countOfFileBanBySHA256 = `
SELECT
	COUNT(*) as count
FROM
	file_bans
WHERE
	sha256_hash = $1
`

var insertFileBan = `
INSERT INTO
	file_bans (sha256_hash, did_quarantine, reason, description, malware_name)
VALUES
	($1, $2, $3, $4, $5)
`

var listBannedFiles = `
SELECT
	sha256_hash,
	did_quarantine,
	reason,
	description,
	malware_name
FROM
	file_bans
`

var getBannedFileBySHA256Hash = `
SELECT
	sha256_hash,
	did_quarantine,
	reason,
	description,
	malware_name
FROM
	file_bans
WHERE
	sha256_hash = $1
LIMIT 1
`

var deleteFileBan = `
DELETE FROM
	file_bans
WHERE
	sha256_hash = $1
`