π End-to-end encrypted cloud for everything.
Dart 2faandroidauthye2eeencryptionend-to-end-encryptionfluttergolanggoogle-photosgoogle-photos-alternativeiosopensourcephotosprivacyprivacy-toolstypescriptzero-knowledge
package repo
import (
"context"
"database/sql"
"errors"
"github.com/ente/stacktrace"
"github.com/lib/pq"
)
type UsageRepository struct {
DB *sql.DB
UserRepo *UserRepository
QueueFileCountInitialization func(int64)
}
type LockerUsage struct {
TotalFileCount int64
TotalUsage int64
Users []UserLockerUsage
}
type UserLockerUsage struct {
UserID int64
FileCount int64
Usage int64
}
type StorageWarningCandidate struct {
RecipientID int64
IsFamilyPlan bool
}
func (repo *UsageRepository) GetUsage(userID int64) (int64, error) {
row := repo.DB.QueryRow(`SELECT storage_consumed FROM usage WHERE user_id = $1`,
userID)
var usage int64
err := row.Scan(&usage)
if errors.Is(err, sql.ErrNoRows) {
return 0, nil
}
return usage, stacktrace.Propagate(err, "")
}
func (repo *UsageRepository) GetStoredFileCounts(ctx context.Context, userID int64) (int64, int64, int64, error) {
var photos, locker sql.NullInt64
var storageConsumed int64
err := repo.DB.QueryRowContext(ctx, `SELECT storage_consumed, photos_file_count, locker_file_count
FROM usage WHERE user_id = $1`, userID).Scan(&storageConsumed, &photos, &locker)
if errors.Is(err, sql.ErrNoRows) {
return 0, -1, -1, nil
}
if err != nil {
return 0, 0, 0, stacktrace.Propagate(err, "")
}
if !photos.Valid || !locker.Valid {
return storageConsumed, -1, -1, nil
}
return storageConsumed, photos.Int64, locker.Int64, nil
}
func (repo *UsageRepository) CreateTx(ctx context.Context, tx *sql.Tx, userID int64) error {
_, err := tx.ExecContext(ctx, `INSERT INTO usage(user_id, storage_consumed, photos_file_count, locker_file_count)
VALUES ($1, 0, 0, 0)`, userID)
return stacktrace.Propagate(err, "failed to insert usage")
}
func (repo *UsageRepository) GetCombinedUsage(ctx context.Context, userIDs []int64) (int64, error) {
row := repo.DB.QueryRowContext(ctx, `SELECT coalesce(sum(storage_consumed),0) FROM usage WHERE user_id = ANY($1)`,
pq.Array(userIDs))
var totalUsage int64
err := row.Scan(&totalUsage)
if errors.Is(err, sql.ErrNoRows) {
return 0, nil
}
return totalUsage, stacktrace.Propagate(err, "")
}
func (repo *UsageRepository) GetStorageWarningCandidates(ctx context.Context, usageThreshold int64) ([]StorageWarningCandidate, error) {
// Intentionally use per-member usage when selecting family candidates. The
// mailer evaluates aggregate family usage later, but candidate selection is
// deliberately narrower so families are only pulled into the workflow when a
// specific member crosses the threshold.
rows, err := repo.DB.QueryContext(ctx, `
SELECT
COALESCE(u.family_admin_id, u.user_id) AS recipient_id,
u.family_admin_id IS NOT NULL AS is_family_plan
FROM users u
INNER JOIN usage us
ON us.user_id = u.user_id
WHERE
us.storage_consumed > $1
AND u.encrypted_email IS NOT NULL
ORDER BY recipient_id
`, usageThreshold)
if err != nil {
return nil, stacktrace.Propagate(err, "failed to fetch storage warning candidates")
}
defer rows.Close()
candidates := make([]StorageWarningCandidate, 0)
seenRecipients := make(map[int64]struct{})
for rows.Next() {
var candidate StorageWarningCandidate
if err := rows.Scan(&candidate.RecipientID, &candidate.IsFamilyPlan); err != nil {
return nil, stacktrace.Propagate(err, "failed to scan storage warning candidate")
}
if _, ok := seenRecipients[candidate.RecipientID]; ok {
continue
}
seenRecipients[candidate.RecipientID] = struct{}{}
candidates = append(candidates, candidate)
}
if err := rows.Err(); err != nil {
return nil, stacktrace.Propagate(err, "failed to iterate storage warning candidates")
}
return candidates, nil
}
func (repo *UsageRepository) GetLockerUsage(ctx context.Context, userIDs []int64) (*LockerUsage, error) {
return repo.getLockerUsage(ctx, userIDs, true)
}
func (repo *UsageRepository) GetLockerStorageUsage(ctx context.Context, userIDs []int64) (*LockerUsage, error) {
return repo.getLockerUsage(ctx, userIDs, false)
}
func (repo *UsageRepository) getLockerUsage(ctx context.Context, userIDs []int64, includeFileCounts bool) (*LockerUsage, error) {
usage := &LockerUsage{}
if len(userIDs) == 0 {
return usage, nil
}
userMap := make(map[int64]*UserLockerUsage)
for _, userID := range userIDs {
userMap[userID] = &UserLockerUsage{UserID: userID}
}
if includeFileCounts {
rows, err := repo.DB.QueryContext(ctx, `SELECT requested.user_id, u.locker_file_count
FROM unnest($1::bigint[]) AS requested(user_id)
LEFT JOIN usage AS u ON u.user_id = requested.user_id`, pq.Array(userIDs))
if err != nil {
return nil, stacktrace.Propagate(err, "")
}
defer rows.Close()
var uninitializedUserIDs []int64
for rows.Next() {
var userID int64
var fileCount sql.NullInt64
if err := rows.Scan(&userID, &fileCount); err != nil {
return nil, stacktrace.Propagate(err, "")
}
if fileCount.Valid {
userMap[userID].FileCount = fileCount.Int64
} else {
uninitializedUserIDs = append(uninitializedUserIDs, userID)
}
}
if err := rows.Err(); err != nil {
return nil, stacktrace.Propagate(err, "")
}
if repo.QueueFileCountInitialization != nil {
for _, userID := range uninitializedUserIDs {
repo.QueueFileCountInitialization(userID)
}
}
if len(uninitializedUserIDs) > 0 {
rows, err = repo.DB.QueryContext(ctx, `SELECT c.owner_id, COUNT(DISTINCT cf.file_id)
FROM collections AS c
JOIN collection_files AS cf ON c.collection_id = cf.collection_id
WHERE c.app = 'locker' AND c.owner_id = ANY($1)
AND cf.f_owner_id = c.owner_id AND cf.is_deleted = FALSE
GROUP BY c.owner_id`, pq.Array(uninitializedUserIDs))
if err != nil {
return nil, stacktrace.Propagate(err, "")
}
defer rows.Close()
for rows.Next() {
var userID, fileCount int64
if err := rows.Scan(&userID, &fileCount); err != nil {
return nil, stacktrace.Propagate(err, "")
}
userMap[userID].FileCount = fileCount
}
if err := rows.Err(); err != nil {
return nil, stacktrace.Propagate(err, "")
}
}
}
sizeQuery := `
SELECT
unique_files.owner_id,
COALESCE(SUM(ok.size), 0) AS total_size
FROM (
SELECT DISTINCT c.owner_id, cf.file_id
FROM collections c
JOIN collection_files cf ON c.collection_id = cf.collection_id
WHERE c.app = 'locker'
AND c.owner_id = ANY($1)
AND cf.f_owner_id = c.owner_id
) AS unique_files
LEFT JOIN object_keys ok ON ok.file_id = unique_files.file_id AND ok.is_deleted = false
GROUP BY unique_files.owner_id;
`
rows, err := repo.DB.QueryContext(ctx, sizeQuery, pq.Array(userIDs))
if err != nil {
return nil, stacktrace.Propagate(err, "")
}
defer rows.Close()
for rows.Next() {
var ownerID, totalSize int64
if scanErr := rows.Scan(&ownerID, &totalSize); scanErr != nil {
return nil, stacktrace.Propagate(scanErr, "")
}
if user, exists := userMap[ownerID]; exists {
user.Usage = totalSize
}
}
for _, userID := range userIDs {
user := userMap[userID]
usage.Users = append(usage.Users, *user)
usage.TotalFileCount += user.FileCount
usage.TotalUsage += user.Usage
}
if err := rows.Err(); err != nil {
return nil, stacktrace.Propagate(err, "")
}
return usage, nil
}
func (repo *UsageRepository) StorageForFamilyAdmin(adminID int64) (int64, error) {
query := `
SELECT COALESCE(SUM(storage_consumed), 0)
FROM users
LEFT JOIN families ON users.family_admin_id = families.admin_id AND families.status IN ('SELF', 'ACCEPTED')
LEFT JOIN usage ON families.member_id = usage.user_id
WHERE users.user_id = $1
`
var totalStorage int64
err := repo.DB.QueryRow(query, adminID).Scan(&totalStorage)
if errors.Is(err, sql.ErrNoRows) {
return 0, nil
}
return totalStorage, stacktrace.Propagate(err, "")
}