~evanj/cms

ref: afc228cb59683180b3b2957aceb6bf1aa0bf0d0e cms/internal/s/db/org.go -rw-r--r-- 3.1 KiB
afc228cbEvan J Feat(db): Tweaking db.SetMaxOpenConns, db.SetMaxIdleConns, and 1 year, 16 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
}