~evanj/cms

ref: 9d2dfb0b32eb388682adedc84de30562f01806be cms/internal/s/db/org.go -rw-r--r-- 3.1 KiB
9d2dfb0bEvan J Feat(db): Mostly complete removing potentially deadlocking DB code. Only 1 year, 26 days ago
                                                                                
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
package db

import (
	"database/sql"

	"git.sr.ht/~evanj/cms/internal/m/org"
	"git.sr.ht/~evanj/cms/internal/m/tier"
	"git.sr.ht/~evanj/cms/internal/m/user"
)

type Org struct {
	OrgID              string
	OrgBillingTierName sql.NullString
	OrgPaymentCustomer sql.NullString
}

func (o Org) ID() string                { return o.OrgID }
func (o Org) HasPaymentCustomer() bool  { return o.OrgPaymentCustomer.Valid }
func (o Org) PaymentCustomerID() string { return o.OrgPaymentCustomer.String }

func (o Org) Tier() tier.Tier {
	t, ok := tier.ByName(o.OrgBillingTierName.String)
	if !ok {
		return tier.Free
	}
	return t
}

var (
	queryOrgByID = `
		SELECT cms_org.ID, cms_billing.TIER_NAME, cms_billing.PAYMENT_CUSTOMER FROM cms_org
		LEFT JOIN cms_billing ON cms_billing.ORG_ID=cms_org.ID
		WHERE cms_org.ID=?
	`

	queryOrgByUserAndID = `
		SELECT cms_org.ID, cms_billing.TIER_NAME, cms_billing.PAYMENT_CUSTOMER FROM cms_org
		LEFT JOIN cms_billing ON cms_billing.ORG_ID=cms_org.ID
		JOIN cms_user ON cms_user.ORG_ID=cms_org.ID
		WHERE cms_user.ID=? AND cms_org.ID=?
	`
)

func (db *DB) OrgNew() (org.Org, error) {
	tx, err := db.Begin()
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	org, err := db.orgNew(tx)
	if err != nil {
		return nil, err
	}

	return org, tx.Commit()
}

func (db *DB) orgNew(t *sql.Tx) (org.Org, error) {
	res, err := t.Exec("INSERT INTO cms_org () VALUES ()")
	if err != nil {
		return nil, err
	}

	orgID, err := res.LastInsertId()
	if err != nil {
		return nil, err
	}

	var org Org
	if err := t.QueryRow(queryOrgByID, orgID).Scan(&org.OrgID, &org.OrgBillingTierName, &org.OrgPaymentCustomer); err != nil {
		return nil, err
	}

	return org, nil
}

func (db *DB) OrgUpdateTier(u user.User, o org.Org, t tier.Tier, paymentCustomerID string) error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	if _, err := tx.Exec("DELETE FROM cms_billing WHERE ORG_ID=?", o.ID()); err != nil {
		return err
	}

	if _, err := tx.Exec("INSERT INTO cms_billing (PAYMENT_CUSTOMER, TIER_NAME, ORG_ID) values(?, ?, ?)", paymentCustomerID, t.Name, o.ID()); err != nil {
		return err
	}

	return tx.Commit()
}

func (db *DB) OrgGetSpaceCount(o org.Org) (int, error) {
	t, err := db.Begin()
	if err != nil {
		return 0, err
	}
	defer t.Rollback()

	i, err := db.orgGetSpaceCount(t, o)
	if err != nil {
		return 0, err
	}

	return i, t.Commit()
}

func (db *DB) orgGetSpaceCount(t *sql.Tx, o org.Org) (int, error) {
	var (
		count int
		q     = "SELECT COUNT(*) FROM cms_space WHERE cms_space.ORG_ID=?"
	)

	if err := t.QueryRow(q, o.ID()).Scan(&count); err != nil {
		return 0, err
	}

	return count, nil
}

func (db *DB) OrgGetUserCount(o org.Org) (int, error) {
	t, err := db.Begin()
	if err != nil {
		return 0, err
	}
	defer t.Rollback()

	i, err := db.orgGetUserCount(t, o)
	if err != nil {
		return 0, err
	}

	return i, t.Commit()
}

func (db *DB) orgGetUserCount(t *sql.Tx, o org.Org) (int, error) {
	var (
		count int
		q     = "SELECT COUNT(*) FROM cms_user WHERE cms_user.ORG_ID=?"
	)

	if err := t.QueryRow(q, o.ID()).Scan(&count); err != nil {
		return 0, err
	}

	return count, nil
}