1836 lines
53 KiB
Go
1836 lines
53 KiB
Go
package db
|
|
|
|
import "database/sql"
|
|
import "errors"
|
|
import "strings"
|
|
import "time"
|
|
|
|
import "codit/internal/models"
|
|
import "codit/internal/util"
|
|
|
|
func (s *Store) CreateUser(user models.User, passwordHash string) (models.User, error) {
|
|
var id string
|
|
var err error
|
|
var now time.Time
|
|
var nowUnix int64
|
|
if user.ID == "" {
|
|
id, err = util.NewID()
|
|
if err != nil {
|
|
return user, err
|
|
}
|
|
user.ID = id
|
|
}
|
|
now = time.Now().UTC()
|
|
nowUnix = now.Unix()
|
|
user.CreatedAt = nowUnix
|
|
user.UpdatedAt = nowUnix
|
|
if user.AuthSource == "" {
|
|
user.AuthSource = "db"
|
|
}
|
|
_, err = s.DB.Exec(`
|
|
INSERT INTO users (public_id, username, display_name, email, password_hash, is_admin, disabled, auth_source, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
`, user.ID, user.Username, user.DisplayName, user.Email, passwordHash, user.IsAdmin, user.Disabled, user.AuthSource, now, now)
|
|
return user, err
|
|
}
|
|
|
|
func (s *Store) UpdateUser(user models.User) error {
|
|
var err error
|
|
var now time.Time
|
|
var nowUnix int64
|
|
now = time.Now().UTC()
|
|
nowUnix = now.Unix()
|
|
user.UpdatedAt = nowUnix
|
|
_, err = s.DB.Exec(`UPDATE users SET display_name = ?, email = ?, is_admin = ?, disabled = ?, updated_at = ? WHERE public_id = ?`,
|
|
user.DisplayName, user.Email, user.IsAdmin, user.Disabled, now, user.ID)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) UpdateUserWithPassword(user models.User, passwordHash string) error {
|
|
var err error
|
|
var now time.Time
|
|
var nowUnix int64
|
|
var tx *sql.Tx
|
|
now = time.Now().UTC()
|
|
nowUnix = now.Unix()
|
|
user.UpdatedAt = nowUnix
|
|
tx, err = s.DB.Begin()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`UPDATE users SET display_name = ?, email = ?, is_admin = ?, disabled = ?, password_hash = ?, updated_at = ? WHERE public_id = ?`,
|
|
user.DisplayName, user.Email, user.IsAdmin, user.Disabled, passwordHash, now, user.ID)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
err = tx.Commit()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (s *Store) SetUserPassword(userID, passwordHash string) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`UPDATE users SET password_hash = ?, updated_at = ? WHERE public_id = ?`, passwordHash, time.Now().UTC(), userID)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) GetUserByID(id string) (models.User, error) {
|
|
var user models.User
|
|
var row *sql.Row
|
|
var created time.Time
|
|
var updated time.Time
|
|
var err error
|
|
row = s.DB.QueryRow(`SELECT public_id, username, display_name, email, is_admin, disabled, auth_source, created_at, updated_at FROM users WHERE public_id = ?`, id)
|
|
err = row.Scan(&user.ID, &user.Username, &user.DisplayName, &user.Email, &user.IsAdmin, &user.Disabled, &user.AuthSource, &created, &updated)
|
|
if err != nil {
|
|
return user, err
|
|
}
|
|
user.CreatedAt = created.Unix()
|
|
user.UpdatedAt = updated.Unix()
|
|
return user, nil
|
|
}
|
|
|
|
func (s *Store) GetUserByUsername(username string) (models.User, string, error) {
|
|
var user models.User
|
|
var passwordHash sql.NullString
|
|
var row *sql.Row
|
|
var err error
|
|
var created time.Time
|
|
var updated time.Time
|
|
row = s.DB.QueryRow(`SELECT public_id, username, display_name, email, is_admin, disabled, auth_source, password_hash, created_at, updated_at FROM users WHERE username = ?`, username)
|
|
err = row.Scan(&user.ID, &user.Username, &user.DisplayName, &user.Email, &user.IsAdmin, &user.Disabled, &user.AuthSource, &passwordHash, &created, &updated)
|
|
if err != nil {
|
|
return user, passwordHash.String, err
|
|
}
|
|
user.CreatedAt = created.Unix()
|
|
user.UpdatedAt = updated.Unix()
|
|
return user, passwordHash.String, nil
|
|
}
|
|
|
|
func (s *Store) ListUsers() ([]models.User, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var users []models.User
|
|
var u models.User
|
|
var created time.Time
|
|
var updated time.Time
|
|
rows, err = s.DB.Query(`SELECT public_id, username, display_name, email, is_admin, disabled, auth_source, created_at, updated_at FROM users ORDER BY username`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&u.ID, &u.Username, &u.DisplayName, &u.Email, &u.IsAdmin, &u.Disabled, &u.AuthSource, &created, &updated)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
u.CreatedAt = created.Unix()
|
|
u.UpdatedAt = updated.Unix()
|
|
users = append(users, u)
|
|
}
|
|
return users, rows.Err()
|
|
}
|
|
|
|
func (s *Store) DeleteUser(id string) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`DELETE FROM users WHERE public_id = ?`, id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) GetAuthSettings() (models.AuthSettings, error) {
|
|
var settings models.AuthSettings
|
|
var rows *sql.Rows
|
|
var err error
|
|
var key string
|
|
var value string
|
|
rows, err = s.DB.Query(`SELECT key, value FROM app_settings WHERE key IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
|
"auth.mode",
|
|
"auth.oidc.enabled",
|
|
"auth.ldap.url",
|
|
"auth.ldap.bind_dn",
|
|
"auth.ldap.bind_password",
|
|
"auth.ldap.user_base_dn",
|
|
"auth.ldap.user_filter",
|
|
"auth.ldap.tls_insecure_skip_verify",
|
|
"auth.oidc.client_id",
|
|
"auth.oidc.client_secret",
|
|
"auth.oidc.authorize_url",
|
|
"auth.oidc.token_url",
|
|
"auth.oidc.userinfo_url",
|
|
"auth.oidc.redirect_url",
|
|
"auth.oidc.scopes",
|
|
"auth.oidc.tls_insecure_skip_verify")
|
|
if err != nil {
|
|
return settings, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&key, &value)
|
|
if err != nil {
|
|
return settings, err
|
|
}
|
|
switch key {
|
|
case "auth.mode":
|
|
settings.AuthMode = value
|
|
case "auth.oidc.enabled":
|
|
settings.OIDCEnabled = value == "1"
|
|
case "auth.ldap.url":
|
|
settings.LDAPURL = value
|
|
case "auth.ldap.bind_dn":
|
|
settings.LDAPBindDN = value
|
|
case "auth.ldap.bind_password":
|
|
settings.LDAPBindPassword = value
|
|
case "auth.ldap.user_base_dn":
|
|
settings.LDAPUserBaseDN = value
|
|
case "auth.ldap.user_filter":
|
|
settings.LDAPUserFilter = value
|
|
case "auth.ldap.tls_insecure_skip_verify":
|
|
settings.LDAPTLSInsecureSkipVerify = value == "1"
|
|
case "auth.oidc.client_id":
|
|
settings.OIDCClientID = value
|
|
case "auth.oidc.client_secret":
|
|
settings.OIDCClientSecret = value
|
|
case "auth.oidc.authorize_url":
|
|
settings.OIDCAuthorizeURL = value
|
|
case "auth.oidc.token_url":
|
|
settings.OIDCTokenURL = value
|
|
case "auth.oidc.userinfo_url":
|
|
settings.OIDCUserInfoURL = value
|
|
case "auth.oidc.redirect_url":
|
|
settings.OIDCRedirectURL = value
|
|
case "auth.oidc.scopes":
|
|
settings.OIDCScopes = value
|
|
case "auth.oidc.tls_insecure_skip_verify":
|
|
settings.OIDCTLSInsecureSkipVerify = value == "1"
|
|
}
|
|
}
|
|
err = rows.Err()
|
|
if err != nil {
|
|
return settings, err
|
|
}
|
|
return settings, nil
|
|
}
|
|
|
|
func (s *Store) SetAuthSettings(settings models.AuthSettings) error {
|
|
var tx *sql.Tx
|
|
var err error
|
|
var now int64
|
|
var tlsInsecure string
|
|
tx, err = s.DB.Begin()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
now = time.Now().UTC().Unix()
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"auth.mode", settings.AuthMode, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
if settings.OIDCEnabled {
|
|
tlsInsecure = "1"
|
|
} else {
|
|
tlsInsecure = "0"
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"auth.oidc.enabled", tlsInsecure, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"auth.ldap.url", settings.LDAPURL, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"auth.ldap.bind_dn", settings.LDAPBindDN, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"auth.ldap.bind_password", settings.LDAPBindPassword, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"auth.ldap.user_base_dn", settings.LDAPUserBaseDN, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"auth.ldap.user_filter", settings.LDAPUserFilter, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
if settings.LDAPTLSInsecureSkipVerify {
|
|
tlsInsecure = "1"
|
|
} else {
|
|
tlsInsecure = "0"
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"auth.ldap.tls_insecure_skip_verify", tlsInsecure, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"auth.oidc.client_id", settings.OIDCClientID, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"auth.oidc.client_secret", settings.OIDCClientSecret, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"auth.oidc.authorize_url", settings.OIDCAuthorizeURL, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"auth.oidc.token_url", settings.OIDCTokenURL, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"auth.oidc.userinfo_url", settings.OIDCUserInfoURL, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"auth.oidc.redirect_url", settings.OIDCRedirectURL, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"auth.oidc.scopes", settings.OIDCScopes, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
if settings.OIDCTLSInsecureSkipVerify {
|
|
tlsInsecure = "1"
|
|
} else {
|
|
tlsInsecure = "0"
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"auth.oidc.tls_insecure_skip_verify", tlsInsecure, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
err = tx.Commit()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (s *Store) GetTLSSettings() (models.TLSSettings, error) {
|
|
var settings models.TLSSettings
|
|
var rows *sql.Rows
|
|
var err error
|
|
var key string
|
|
var value string
|
|
rows, err = s.DB.Query(`SELECT key, value FROM app_settings WHERE key IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
|
"tls.http_addrs",
|
|
"tls.https_addrs",
|
|
"tls.server_cert_source",
|
|
"tls.cert_file",
|
|
"tls.key_file",
|
|
"tls.pki_server_cert_id",
|
|
"tls.client_auth",
|
|
"tls.client_ca_file",
|
|
"tls.pki_client_ca_id",
|
|
"tls.min_version")
|
|
if err != nil {
|
|
return settings, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&key, &value)
|
|
if err != nil {
|
|
return settings, err
|
|
}
|
|
switch key {
|
|
case "tls.http_addrs":
|
|
settings.HTTPAddrs = splitCSVValue(value)
|
|
case "tls.https_addrs":
|
|
settings.HTTPSAddrs = splitCSVValue(value)
|
|
case "tls.server_cert_source":
|
|
settings.TLSServerCertSource = value
|
|
case "tls.cert_file":
|
|
settings.TLSCertFile = value
|
|
case "tls.key_file":
|
|
settings.TLSKeyFile = value
|
|
case "tls.pki_server_cert_id":
|
|
settings.TLSPKIServerCertID = value
|
|
case "tls.client_auth":
|
|
settings.TLSClientAuth = value
|
|
case "tls.client_ca_file":
|
|
settings.TLSClientCAFile = value
|
|
case "tls.pki_client_ca_id":
|
|
settings.TLSPKIClientCAID = value
|
|
case "tls.min_version":
|
|
settings.TLSMinVersion = value
|
|
}
|
|
}
|
|
err = rows.Err()
|
|
if err != nil {
|
|
return settings, err
|
|
}
|
|
return settings, nil
|
|
}
|
|
|
|
func (s *Store) SetTLSSettings(settings models.TLSSettings) error {
|
|
var tx *sql.Tx
|
|
var err error
|
|
var now int64
|
|
tx, err = s.DB.Begin()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
now = time.Now().UTC().Unix()
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"tls.http_addrs", strings.Join(settings.HTTPAddrs, ","), now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"tls.https_addrs", strings.Join(settings.HTTPSAddrs, ","), now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"tls.server_cert_source", settings.TLSServerCertSource, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"tls.cert_file", settings.TLSCertFile, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"tls.key_file", settings.TLSKeyFile, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"tls.pki_server_cert_id", settings.TLSPKIServerCertID, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"tls.client_auth", settings.TLSClientAuth, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"tls.client_ca_file", settings.TLSClientCAFile, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"tls.pki_client_ca_id", settings.TLSPKIClientCAID, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
|
|
"tls.min_version", settings.TLSMinVersion, now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
err = tx.Commit()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func splitCSVValue(value string) []string {
|
|
var parts []string
|
|
var out []string
|
|
var i int
|
|
var p string
|
|
parts = strings.Split(value, ",")
|
|
for i = 0; i < len(parts); i++ {
|
|
p = strings.TrimSpace(parts[i])
|
|
if p == "" {
|
|
continue
|
|
}
|
|
out = append(out, p)
|
|
}
|
|
return out
|
|
}
|
|
|
|
func (s *Store) SetUserDisabled(id string, disabled bool) error {
|
|
var err error
|
|
var now time.Time
|
|
now = time.Now().UTC()
|
|
_, err = s.DB.Exec(`UPDATE users SET disabled = ?, updated_at = ? WHERE public_id = ?`, disabled, now, id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) CreateAPIKey(userID string, name string, tokenHash string, prefix string, expiresAt int64) (models.APIKey, error) {
|
|
var key models.APIKey
|
|
var err error
|
|
var now time.Time
|
|
var nowUnix int64
|
|
var id string
|
|
id, err = util.NewID()
|
|
if err != nil {
|
|
return key, err
|
|
}
|
|
now = time.Now().UTC()
|
|
nowUnix = now.Unix()
|
|
key = models.APIKey{
|
|
ID: id,
|
|
UserID: userID,
|
|
Name: name,
|
|
Prefix: prefix,
|
|
CreatedAt: nowUnix,
|
|
LastUsedAt: 0,
|
|
ExpiresAt: expiresAt,
|
|
Disabled: false,
|
|
}
|
|
_, err = s.DB.Exec(`INSERT INTO api_keys (public_id, user_id, name, token_hash, token_prefix, created_at, last_used_at, expires_at, disabled)
|
|
VALUES (?, (SELECT id FROM users WHERE public_id = ?), ?, ?, ?, ?, ?, ?, ?)`,
|
|
key.ID, key.UserID, key.Name, tokenHash, key.Prefix, key.CreatedAt, key.LastUsedAt, key.ExpiresAt, key.Disabled)
|
|
return key, err
|
|
}
|
|
|
|
func (s *Store) ListAPIKeys(userID string) ([]models.APIKey, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var keys []models.APIKey
|
|
var key models.APIKey
|
|
rows, err = s.DB.Query(`SELECT k.public_id, u.public_id, k.name, k.token_prefix, k.created_at, k.last_used_at, k.expires_at, k.disabled
|
|
FROM api_keys k
|
|
JOIN users u ON u.id = k.user_id
|
|
WHERE u.public_id = ?
|
|
ORDER BY k.created_at DESC`, userID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&key.ID, &key.UserID, &key.Name, &key.Prefix, &key.CreatedAt, &key.LastUsedAt, &key.ExpiresAt, &key.Disabled)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
keys = append(keys, key)
|
|
}
|
|
return keys, rows.Err()
|
|
}
|
|
|
|
func (s *Store) DeleteAPIKey(userID string, id string) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`DELETE FROM api_keys WHERE public_id = ? AND user_id = (SELECT id FROM users WHERE public_id = ?)`, id, userID)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) SetAPIKeyDisabled(userID string, id string, disabled bool) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`UPDATE api_keys SET disabled = ? WHERE public_id = ? AND user_id = (SELECT id FROM users WHERE public_id = ?)`, disabled, id, userID)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) DeleteAPIKeyByID(id string) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`DELETE FROM api_keys WHERE public_id = ?`, id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) SetAPIKeyDisabledByID(id string, disabled bool) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`UPDATE api_keys SET disabled = ? WHERE public_id = ?`, disabled, id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) ListAPIKeysAdmin(userID string, query string) ([]models.AdminAPIKey, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var keys []models.AdminAPIKey
|
|
var key models.AdminAPIKey
|
|
var sqlQuery string
|
|
var like string
|
|
query = strings.TrimSpace(query)
|
|
like = "%" + query + "%"
|
|
if userID != "" && query != "" {
|
|
sqlQuery = `
|
|
SELECT
|
|
k.public_id, u.public_id, u.username, u.display_name, u.email,
|
|
k.name, k.token_prefix, k.created_at, k.last_used_at, k.expires_at, k.disabled
|
|
FROM api_keys k
|
|
JOIN users u ON u.id = k.user_id
|
|
WHERE u.public_id = ?
|
|
AND (k.name LIKE ? OR k.token_prefix LIKE ? OR u.username LIKE ? OR u.display_name LIKE ? OR u.email LIKE ?)
|
|
ORDER BY k.created_at DESC
|
|
`
|
|
rows, err = s.DB.Query(sqlQuery, userID, like, like, like, like, like)
|
|
} else if userID != "" {
|
|
sqlQuery = `
|
|
SELECT
|
|
k.public_id, u.public_id, u.username, u.display_name, u.email,
|
|
k.name, k.token_prefix, k.created_at, k.last_used_at, k.expires_at, k.disabled
|
|
FROM api_keys k
|
|
JOIN users u ON u.id = k.user_id
|
|
WHERE u.public_id = ?
|
|
ORDER BY k.created_at DESC
|
|
`
|
|
rows, err = s.DB.Query(sqlQuery, userID)
|
|
} else if query != "" {
|
|
sqlQuery = `
|
|
SELECT
|
|
k.public_id, u.public_id, u.username, u.display_name, u.email,
|
|
k.name, k.token_prefix, k.created_at, k.last_used_at, k.expires_at, k.disabled
|
|
FROM api_keys k
|
|
JOIN users u ON u.id = k.user_id
|
|
WHERE k.name LIKE ? OR k.token_prefix LIKE ? OR u.username LIKE ? OR u.display_name LIKE ? OR u.email LIKE ?
|
|
ORDER BY k.created_at DESC
|
|
`
|
|
rows, err = s.DB.Query(sqlQuery, like, like, like, like, like)
|
|
} else {
|
|
sqlQuery = `
|
|
SELECT
|
|
k.public_id, u.public_id, u.username, u.display_name, u.email,
|
|
k.name, k.token_prefix, k.created_at, k.last_used_at, k.expires_at, k.disabled
|
|
FROM api_keys k
|
|
JOIN users u ON u.id = k.user_id
|
|
ORDER BY k.created_at DESC
|
|
`
|
|
rows, err = s.DB.Query(sqlQuery)
|
|
}
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(
|
|
&key.ID,
|
|
&key.UserID,
|
|
&key.Username,
|
|
&key.DisplayName,
|
|
&key.Email,
|
|
&key.Name,
|
|
&key.Prefix,
|
|
&key.CreatedAt,
|
|
&key.LastUsedAt,
|
|
&key.ExpiresAt,
|
|
&key.Disabled,
|
|
)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
keys = append(keys, key)
|
|
}
|
|
return keys, rows.Err()
|
|
}
|
|
|
|
func (s *Store) GetUserByAPIKeyHash(tokenHash string) (models.User, error) {
|
|
var user models.User
|
|
var row *sql.Row
|
|
var created time.Time
|
|
var updated time.Time
|
|
var keyID int64
|
|
var now time.Time
|
|
var nowUnix int64
|
|
var err error
|
|
var currentUnix int64
|
|
now = time.Now().UTC()
|
|
currentUnix = now.Unix()
|
|
row = s.DB.QueryRow(`
|
|
SELECT u.public_id, u.username, u.display_name, u.email, u.is_admin, u.disabled, u.auth_source, u.created_at, u.updated_at, k.id
|
|
FROM api_keys k
|
|
JOIN users u ON u.id = k.user_id
|
|
WHERE k.token_hash = ?
|
|
AND u.disabled = 0
|
|
AND k.disabled = 0
|
|
AND (k.expires_at = 0 OR k.expires_at > ?)
|
|
LIMIT 1
|
|
`, tokenHash, currentUnix)
|
|
err = row.Scan(&user.ID, &user.Username, &user.DisplayName, &user.Email, &user.IsAdmin, &user.Disabled, &user.AuthSource, &created, &updated, &keyID)
|
|
if err != nil {
|
|
return user, err
|
|
}
|
|
user.CreatedAt = created.Unix()
|
|
user.UpdatedAt = updated.Unix()
|
|
now = time.Now().UTC()
|
|
nowUnix = now.Unix()
|
|
_, _ = s.DB.Exec(`UPDATE api_keys SET last_used_at = ? WHERE id = ?`, nowUnix, keyID)
|
|
return user, nil
|
|
}
|
|
|
|
func (s *Store) CreateSession(userID, token string, expiresAt time.Time) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`INSERT INTO sessions (id, user_id, token, expires_at, created_at)
|
|
VALUES (?, (SELECT id FROM users WHERE public_id = ?), ?, ?, ?)`,
|
|
mustID(), userID, token, expiresAt, time.Now().UTC())
|
|
return err
|
|
}
|
|
|
|
func (s *Store) DeleteSession(token string) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`DELETE FROM sessions WHERE token = ?`, token)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) GetSessionUser(token string) (models.User, time.Time, error) {
|
|
var user models.User
|
|
var expires time.Time
|
|
var row *sql.Row
|
|
var err error
|
|
var created time.Time
|
|
var updated time.Time
|
|
row = s.DB.QueryRow(`
|
|
SELECT u.public_id, u.username, u.display_name, u.email, u.is_admin, u.disabled, u.auth_source, u.created_at, u.updated_at, s.expires_at
|
|
FROM sessions s JOIN users u ON u.id = s.user_id
|
|
WHERE s.token = ? AND u.disabled = 0
|
|
`, token)
|
|
err = row.Scan(&user.ID, &user.Username, &user.DisplayName, &user.Email, &user.IsAdmin, &user.Disabled, &user.AuthSource, &created, &updated, &expires)
|
|
if err != nil {
|
|
return user, time.Time{}, err
|
|
}
|
|
user.CreatedAt = created.Unix()
|
|
user.UpdatedAt = updated.Unix()
|
|
return user, expires, nil
|
|
}
|
|
|
|
func (s *Store) CreateProject(project models.Project) (models.Project, error) {
|
|
var id string
|
|
var err error
|
|
var now time.Time
|
|
var nowUnix int64
|
|
var tx *sql.Tx
|
|
if project.ID == "" {
|
|
id, err = util.NewID()
|
|
if err != nil {
|
|
return project, err
|
|
}
|
|
project.ID = id
|
|
}
|
|
now = time.Now().UTC()
|
|
nowUnix = now.Unix()
|
|
project.CreatedAt = nowUnix
|
|
project.UpdatedAt = nowUnix
|
|
if project.UpdatedBy == "" {
|
|
project.UpdatedBy = project.CreatedBy
|
|
}
|
|
tx, err = s.DB.Begin()
|
|
if err != nil {
|
|
return project, err
|
|
}
|
|
if project.HomePage == "" {
|
|
project.HomePage = "info"
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO projects (public_id, slug, name, description, home_page, created_by, updated_by, created_at, updated_at, created_at_unix, updated_at_unix)
|
|
VALUES (?, ?, ?, ?, ?, (SELECT id FROM users WHERE public_id = ?), (SELECT id FROM users WHERE public_id = ?), ?, ?, ?, ?)`,
|
|
project.ID,
|
|
project.Slug,
|
|
project.Name,
|
|
project.Description,
|
|
project.HomePage,
|
|
project.CreatedBy,
|
|
project.UpdatedBy,
|
|
now,
|
|
now,
|
|
project.CreatedAt,
|
|
project.UpdatedAt,
|
|
)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return project, err
|
|
}
|
|
_, err = tx.Exec(`INSERT INTO project_members (project_id, user_id, role, created_at)
|
|
VALUES ((SELECT id FROM projects WHERE public_id = ?), (SELECT id FROM users WHERE public_id = ?), ?, ?)`,
|
|
project.ID, project.CreatedBy, "admin", now)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return project, err
|
|
}
|
|
err = tx.Commit()
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return project, err
|
|
}
|
|
return project, nil
|
|
}
|
|
|
|
func (s *Store) UpdateProject(project models.Project) error {
|
|
var err error
|
|
if project.HomePage == "" {
|
|
project.HomePage = "info"
|
|
}
|
|
_, err = s.DB.Exec(`UPDATE projects SET slug = ?, name = ?, description = ?, home_page = ?, updated_at = ?, updated_by = (SELECT id FROM users WHERE public_id = ?), updated_at_unix = ? WHERE public_id = ?`,
|
|
project.Slug,
|
|
project.Name,
|
|
project.Description,
|
|
project.HomePage,
|
|
time.Unix(project.UpdatedAt, 0).UTC(),
|
|
project.UpdatedBy,
|
|
project.UpdatedAt,
|
|
project.ID,
|
|
)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) GetProject(id string) (models.Project, error) {
|
|
var project models.Project
|
|
var row *sql.Row
|
|
row = s.DB.QueryRow(`
|
|
SELECT p.public_id, p.slug, p.name, p.description, p.home_page,
|
|
c.public_id, u.public_id,
|
|
COALESCE(c.username, ''), COALESCE(u.username, ''),
|
|
p.created_at_unix, p.updated_at_unix
|
|
FROM projects p
|
|
LEFT JOIN users c ON c.id = p.created_by
|
|
LEFT JOIN users u ON u.id = p.updated_by
|
|
WHERE p.public_id = ?
|
|
`, id)
|
|
return project, row.Scan(
|
|
&project.ID,
|
|
&project.Slug,
|
|
&project.Name,
|
|
&project.Description,
|
|
&project.HomePage,
|
|
&project.CreatedBy,
|
|
&project.UpdatedBy,
|
|
&project.CreatedByName,
|
|
&project.UpdatedByName,
|
|
&project.CreatedAt,
|
|
&project.UpdatedAt,
|
|
)
|
|
}
|
|
|
|
func (s *Store) GetProjectBySlug(slug string) (models.Project, error) {
|
|
var project models.Project
|
|
var row *sql.Row
|
|
var err error
|
|
row = s.DB.QueryRow(`
|
|
SELECT p.public_id, p.slug, p.name, p.description, p.home_page,
|
|
c.public_id, u.public_id,
|
|
COALESCE(c.username, ''), COALESCE(u.username, ''),
|
|
p.created_at_unix, p.updated_at_unix
|
|
FROM projects p
|
|
LEFT JOIN users c ON c.id = p.created_by
|
|
LEFT JOIN users u ON u.id = p.updated_by
|
|
WHERE p.slug = ?
|
|
`, slug)
|
|
err = row.Scan(
|
|
&project.ID,
|
|
&project.Slug,
|
|
&project.Name,
|
|
&project.Description,
|
|
&project.HomePage,
|
|
&project.CreatedBy,
|
|
&project.UpdatedBy,
|
|
&project.CreatedByName,
|
|
&project.UpdatedByName,
|
|
&project.CreatedAt,
|
|
&project.UpdatedAt,
|
|
)
|
|
if err != nil {
|
|
return project, err
|
|
}
|
|
return project, nil
|
|
}
|
|
|
|
func (s *Store) ListProjects() ([]models.Project, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var projects []models.Project
|
|
var p models.Project
|
|
rows, err = s.DB.Query(`
|
|
SELECT p.public_id, p.slug, p.name, p.description, p.home_page,
|
|
c.public_id, u.public_id,
|
|
COALESCE(c.username, ''), COALESCE(u.username, ''),
|
|
p.created_at_unix, p.updated_at_unix
|
|
FROM projects p
|
|
LEFT JOIN users c ON c.id = p.created_by
|
|
LEFT JOIN users u ON u.id = p.updated_by
|
|
ORDER BY p.name
|
|
`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(
|
|
&p.ID,
|
|
&p.Slug,
|
|
&p.Name,
|
|
&p.Description,
|
|
&p.HomePage,
|
|
&p.CreatedBy,
|
|
&p.UpdatedBy,
|
|
&p.CreatedByName,
|
|
&p.UpdatedByName,
|
|
&p.CreatedAt,
|
|
&p.UpdatedAt,
|
|
)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
projects = append(projects, p)
|
|
}
|
|
return projects, rows.Err()
|
|
}
|
|
|
|
func (s *Store) ListProjectsForUser(userID string) ([]models.Project, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var projects []models.Project
|
|
var p models.Project
|
|
rows, err = s.DB.Query(`
|
|
SELECT p.public_id, p.slug, p.name, p.description, p.home_page,
|
|
c.public_id, u.public_id,
|
|
COALESCE(c.username, ''), COALESCE(u.username, ''),
|
|
p.created_at_unix, p.updated_at_unix
|
|
FROM projects p
|
|
JOIN project_members m ON m.project_id = p.id
|
|
LEFT JOIN users c ON c.id = p.created_by
|
|
LEFT JOIN users u ON u.id = p.updated_by
|
|
WHERE m.user_id = (SELECT id FROM users WHERE public_id = ?)
|
|
ORDER BY p.name
|
|
`, userID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(
|
|
&p.ID,
|
|
&p.Slug,
|
|
&p.Name,
|
|
&p.Description,
|
|
&p.HomePage,
|
|
&p.CreatedBy,
|
|
&p.UpdatedBy,
|
|
&p.CreatedByName,
|
|
&p.UpdatedByName,
|
|
&p.CreatedAt,
|
|
&p.UpdatedAt,
|
|
)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
projects = append(projects, p)
|
|
}
|
|
return projects, rows.Err()
|
|
}
|
|
|
|
func (s *Store) ListProjectsFiltered(limit int, offset int, query string) ([]models.Project, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var projects []models.Project
|
|
var p models.Project
|
|
if limit <= 0 {
|
|
limit = 100
|
|
}
|
|
if offset < 0 {
|
|
offset = 0
|
|
}
|
|
if query == "" {
|
|
rows, err = s.DB.Query(
|
|
`SELECT p.public_id, p.slug, p.name, p.description, p.home_page,
|
|
c.public_id, u.public_id,
|
|
COALESCE(c.username, ''), COALESCE(u.username, ''),
|
|
p.created_at_unix, p.updated_at_unix
|
|
FROM projects p
|
|
LEFT JOIN users c ON c.id = p.created_by
|
|
LEFT JOIN users u ON u.id = p.updated_by
|
|
ORDER BY p.name LIMIT ? OFFSET ?`,
|
|
limit,
|
|
offset,
|
|
)
|
|
} else {
|
|
rows, err = s.DB.Query(
|
|
`SELECT p.public_id, p.slug, p.name, p.description, p.home_page,
|
|
c.public_id, u.public_id,
|
|
COALESCE(c.username, ''), COALESCE(u.username, ''),
|
|
p.created_at_unix, p.updated_at_unix
|
|
FROM projects p
|
|
LEFT JOIN users c ON c.id = p.created_by
|
|
LEFT JOIN users u ON u.id = p.updated_by
|
|
WHERE p.name LIKE ? OR p.slug LIKE ?
|
|
ORDER BY p.name LIMIT ? OFFSET ?`,
|
|
"%"+query+"%",
|
|
"%"+query+"%",
|
|
limit,
|
|
offset,
|
|
)
|
|
}
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(
|
|
&p.ID,
|
|
&p.Slug,
|
|
&p.Name,
|
|
&p.Description,
|
|
&p.HomePage,
|
|
&p.CreatedBy,
|
|
&p.UpdatedBy,
|
|
&p.CreatedByName,
|
|
&p.UpdatedByName,
|
|
&p.CreatedAt,
|
|
&p.UpdatedAt,
|
|
)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
projects = append(projects, p)
|
|
}
|
|
return projects, rows.Err()
|
|
}
|
|
|
|
func (s *Store) ListProjectsFilteredForUser(userID string, limit int, offset int, query string) ([]models.Project, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var projects []models.Project
|
|
var p models.Project
|
|
if limit <= 0 {
|
|
limit = 100
|
|
}
|
|
if offset < 0 {
|
|
offset = 0
|
|
}
|
|
if query == "" {
|
|
rows, err = s.DB.Query(
|
|
`SELECT p.public_id, p.slug, p.name, p.description, p.home_page,
|
|
c.public_id, u.public_id,
|
|
COALESCE(c.username, ''), COALESCE(u.username, ''),
|
|
p.created_at_unix, p.updated_at_unix
|
|
FROM projects p
|
|
JOIN project_members m ON m.project_id = p.id
|
|
LEFT JOIN users c ON c.id = p.created_by
|
|
LEFT JOIN users u ON u.id = p.updated_by
|
|
WHERE m.user_id = (SELECT id FROM users WHERE public_id = ?)
|
|
ORDER BY p.name LIMIT ? OFFSET ?`,
|
|
userID,
|
|
limit,
|
|
offset,
|
|
)
|
|
} else {
|
|
rows, err = s.DB.Query(
|
|
`SELECT p.public_id, p.slug, p.name, p.description, p.home_page,
|
|
c.public_id, u.public_id,
|
|
COALESCE(c.username, ''), COALESCE(u.username, ''),
|
|
p.created_at_unix, p.updated_at_unix
|
|
FROM projects p
|
|
JOIN project_members m ON m.project_id = p.id
|
|
LEFT JOIN users c ON c.id = p.created_by
|
|
LEFT JOIN users u ON u.id = p.updated_by
|
|
WHERE m.user_id = (SELECT id FROM users WHERE public_id = ?) AND (p.name LIKE ? OR p.slug LIKE ?)
|
|
ORDER BY p.name LIMIT ? OFFSET ?`,
|
|
userID,
|
|
"%"+query+"%",
|
|
"%"+query+"%",
|
|
limit,
|
|
offset,
|
|
)
|
|
}
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(
|
|
&p.ID,
|
|
&p.Slug,
|
|
&p.Name,
|
|
&p.Description,
|
|
&p.HomePage,
|
|
&p.CreatedBy,
|
|
&p.UpdatedBy,
|
|
&p.CreatedByName,
|
|
&p.UpdatedByName,
|
|
&p.CreatedAt,
|
|
&p.UpdatedAt,
|
|
)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
projects = append(projects, p)
|
|
}
|
|
return projects, rows.Err()
|
|
}
|
|
|
|
func (s *Store) DeleteProject(id string) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`DELETE FROM projects WHERE public_id = ?`, id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) AddProjectMember(projectID, userID, role string) (models.ProjectMember, error) {
|
|
var member models.ProjectMember
|
|
var err error
|
|
var now time.Time
|
|
now = time.Now().UTC()
|
|
member = models.ProjectMember{ProjectID: projectID, UserID: userID, Role: role, CreatedAt: now.Unix()}
|
|
_, err = s.DB.Exec(`INSERT INTO project_members (project_id, user_id, role, created_at)
|
|
VALUES ((SELECT id FROM projects WHERE public_id = ?), (SELECT id FROM users WHERE public_id = ?), ?, ?)`,
|
|
member.ProjectID, member.UserID, member.Role, now)
|
|
return member, err
|
|
}
|
|
|
|
func (s *Store) UpdateProjectMemberRole(projectID, userID, role string) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`UPDATE project_members SET role = ?
|
|
WHERE project_id = (SELECT id FROM projects WHERE public_id = ?)
|
|
AND user_id = (SELECT id FROM users WHERE public_id = ?)`, role, projectID, userID)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) RemoveProjectMember(projectID, userID string) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`DELETE FROM project_members
|
|
WHERE project_id = (SELECT id FROM projects WHERE public_id = ?)
|
|
AND user_id = (SELECT id FROM users WHERE public_id = ?)`, projectID, userID)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) ListProjectMembers(projectID string) ([]models.ProjectMember, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var members []models.ProjectMember
|
|
var m models.ProjectMember
|
|
var created time.Time
|
|
rows, err = s.DB.Query(`SELECT p.public_id, u.public_id, m.role, m.created_at
|
|
FROM project_members m
|
|
JOIN projects p ON p.id = m.project_id
|
|
JOIN users u ON u.id = m.user_id
|
|
WHERE m.project_id = (SELECT id FROM projects WHERE public_id = ?)
|
|
ORDER BY m.role`, projectID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&m.ProjectID, &m.UserID, &m.Role, &created)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
m.CreatedAt = created.Unix()
|
|
members = append(members, m)
|
|
}
|
|
return members, rows.Err()
|
|
}
|
|
|
|
func (s *Store) GetProjectMemberRole(projectID, userID string) (string, error) {
|
|
var role string
|
|
var row *sql.Row
|
|
var err error
|
|
row = s.DB.QueryRow(`SELECT role FROM project_members
|
|
WHERE project_id = (SELECT id FROM projects WHERE public_id = ?)
|
|
AND user_id = (SELECT id FROM users WHERE public_id = ?)`, projectID, userID)
|
|
err = row.Scan(&role)
|
|
if err != nil {
|
|
return "", err
|
|
}
|
|
return role, nil
|
|
}
|
|
|
|
func (s *Store) CreateRepo(repo models.Repo) (models.Repo, error) {
|
|
var id string
|
|
var err error
|
|
var now time.Time
|
|
var nowUnix int64
|
|
if repo.ID == "" {
|
|
id, err = util.NewID()
|
|
if err != nil {
|
|
return repo, err
|
|
}
|
|
repo.ID = id
|
|
}
|
|
now = time.Now().UTC()
|
|
nowUnix = now.Unix()
|
|
repo.CreatedAt = nowUnix
|
|
_, err = s.DB.Exec(`INSERT INTO repos (public_id, project_id, name, type, path, created_by, created_at)
|
|
VALUES (?, (SELECT id FROM projects WHERE public_id = ?), ?, ?, ?, (SELECT id FROM users WHERE public_id = ?), ?)`,
|
|
repo.ID, repo.ProjectID, repo.Name, repo.Type, repo.Path, repo.CreatedBy, now)
|
|
return repo, err
|
|
}
|
|
|
|
func (s *Store) RepoNameExists(projectID string, name string, repoType string) (bool, error) {
|
|
var count int
|
|
var row *sql.Row
|
|
var err error
|
|
row = s.DB.QueryRow(`SELECT COUNT(1) FROM repos
|
|
WHERE project_id = (SELECT id FROM projects WHERE public_id = ?)
|
|
AND name = ? AND type = ?`, projectID, name, repoType)
|
|
err = row.Scan(&count)
|
|
if err != nil {
|
|
return false, err
|
|
}
|
|
return count > 0, nil
|
|
}
|
|
|
|
func (s *Store) GetRepo(id string) (models.Repo, error) {
|
|
var repo models.Repo
|
|
var row *sql.Row
|
|
var created time.Time
|
|
var err error
|
|
row = s.DB.QueryRow(`SELECT r.public_id, p.public_id, r.name, r.type, r.path, u.public_id, r.created_at
|
|
FROM repos r
|
|
JOIN projects p ON p.id = r.project_id
|
|
JOIN users u ON u.id = r.created_by
|
|
WHERE r.public_id = ?`, id)
|
|
err = row.Scan(&repo.ID, &repo.ProjectID, &repo.Name, &repo.Type, &repo.Path, &repo.CreatedBy, &created)
|
|
if err != nil {
|
|
return repo, err
|
|
}
|
|
repo.CreatedAt = created.Unix()
|
|
return repo, nil
|
|
}
|
|
|
|
func (s *Store) GetRepoStorageIDs(id string) (int64, int64, error) {
|
|
var row *sql.Row
|
|
var projectID int64
|
|
var repoID int64
|
|
var err error
|
|
row = s.DB.QueryRow(`SELECT p.id, r.id
|
|
FROM repos r
|
|
JOIN projects p ON p.id = r.project_id
|
|
WHERE r.public_id = ?`, id)
|
|
err = row.Scan(&projectID, &repoID)
|
|
if err != nil {
|
|
return 0, 0, err
|
|
}
|
|
return projectID, repoID, nil
|
|
}
|
|
|
|
func (s *Store) GetRepoByProjectNameType(projectID string, name string, repoType string) (models.Repo, error) {
|
|
var repo models.Repo
|
|
var row *sql.Row
|
|
var err error
|
|
var created time.Time
|
|
row = s.DB.QueryRow(`SELECT r.public_id, p.public_id, r.name, r.type, r.path, u.public_id, r.created_at
|
|
FROM repos r
|
|
JOIN projects p ON p.id = r.project_id
|
|
JOIN users u ON u.id = r.created_by
|
|
WHERE r.project_id = (SELECT id FROM projects WHERE public_id = ?)
|
|
AND r.name = ? AND r.type = ?`,
|
|
projectID, name, repoType)
|
|
err = row.Scan(&repo.ID, &repo.ProjectID, &repo.Name, &repo.Type, &repo.Path, &repo.CreatedBy, &created)
|
|
if err != nil {
|
|
return repo, err
|
|
}
|
|
repo.CreatedAt = created.Unix()
|
|
return repo, nil
|
|
}
|
|
|
|
func (s *Store) ListRepos(projectID string) ([]models.Repo, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var repos []models.Repo
|
|
var r models.Repo
|
|
var isForeign int
|
|
var created time.Time
|
|
rows, err = s.DB.Query(`
|
|
SELECT r.public_id, p.public_id, r.name, r.type, r.path, u.public_id, r.created_at, 0 AS is_foreign
|
|
FROM repos r
|
|
JOIN projects p ON p.id = r.project_id
|
|
JOIN users u ON u.id = r.created_by
|
|
WHERE r.project_id = (SELECT id FROM projects WHERE public_id = ?)
|
|
UNION ALL
|
|
SELECT r.public_id, p.public_id, r.name, r.type, r.path, u.public_id, r.created_at, 1 AS is_foreign
|
|
FROM repos r
|
|
JOIN projects p ON p.id = r.project_id
|
|
JOIN users u ON u.id = r.created_by
|
|
JOIN project_repos pr ON pr.repo_id = r.id
|
|
WHERE pr.project_id = (SELECT id FROM projects WHERE public_id = ?)
|
|
AND r.project_id <> (SELECT id FROM projects WHERE public_id = ?)
|
|
ORDER BY 3
|
|
`, projectID, projectID, projectID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&r.ID, &r.ProjectID, &r.Name, &r.Type, &r.Path, &r.CreatedBy, &created, &isForeign)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
r.CreatedAt = created.Unix()
|
|
r.IsForeign = isForeign != 0
|
|
repos = append(repos, r)
|
|
}
|
|
return repos, rows.Err()
|
|
}
|
|
|
|
func (s *Store) ListReposOwned(projectID string) ([]models.Repo, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var repos []models.Repo
|
|
var r models.Repo
|
|
var created time.Time
|
|
rows, err = s.DB.Query(`
|
|
SELECT r.public_id, p.public_id, r.name, r.type, r.path, u.public_id, r.created_at
|
|
FROM repos r
|
|
JOIN projects p ON p.id = r.project_id
|
|
JOIN users u ON u.id = r.created_by
|
|
WHERE r.project_id = (SELECT id FROM projects WHERE public_id = ?)
|
|
ORDER BY r.name
|
|
`, projectID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&r.ID, &r.ProjectID, &r.Name, &r.Type, &r.Path, &r.CreatedBy, &created)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
r.CreatedAt = created.Unix()
|
|
repos = append(repos, r)
|
|
}
|
|
return repos, rows.Err()
|
|
}
|
|
|
|
func (s *Store) ListAllRepos() ([]models.Repo, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var repos []models.Repo
|
|
var r models.Repo
|
|
var created time.Time
|
|
rows, err = s.DB.Query(`
|
|
SELECT r.public_id, p.public_id, r.name, r.type, r.path, u.public_id, r.created_at
|
|
FROM repos r
|
|
JOIN projects p ON p.id = r.project_id
|
|
JOIN users u ON u.id = r.created_by
|
|
ORDER BY r.name
|
|
`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&r.ID, &r.ProjectID, &r.Name, &r.Type, &r.Path, &r.CreatedBy, &created)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
r.CreatedAt = created.Unix()
|
|
repos = append(repos, r)
|
|
}
|
|
return repos, rows.Err()
|
|
}
|
|
|
|
func (s *Store) ListReposByProjectIDs(projectIDs []string) ([]models.Repo, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var repos []models.Repo
|
|
var r models.Repo
|
|
var created time.Time
|
|
var placeholders []string
|
|
var args []interface{}
|
|
var i int
|
|
var selected []interface{}
|
|
if len(projectIDs) == 0 {
|
|
return []models.Repo{}, nil
|
|
}
|
|
placeholders = make([]string, len(projectIDs))
|
|
args = make([]interface{}, len(projectIDs))
|
|
for i = 0; i < len(projectIDs); i++ {
|
|
placeholders[i] = "?"
|
|
selected = append(selected, projectIDs[i])
|
|
}
|
|
args = make([]interface{}, len(selected))
|
|
for i = 0; i < len(selected); i++ {
|
|
args[i] = selected[i]
|
|
}
|
|
rows, err = s.DB.Query(`
|
|
SELECT r.public_id, p.public_id, r.name, r.type, r.path, u.public_id, r.created_at
|
|
FROM repos r
|
|
JOIN projects p ON p.id = r.project_id
|
|
JOIN users u ON u.id = r.created_by
|
|
WHERE p.public_id IN (`+strings.Join(placeholders, ",")+`)
|
|
ORDER BY r.name
|
|
`, args...)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&r.ID, &r.ProjectID, &r.Name, &r.Type, &r.Path, &r.CreatedBy, &created)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
r.CreatedAt = created.Unix()
|
|
repos = append(repos, r)
|
|
}
|
|
return repos, rows.Err()
|
|
}
|
|
|
|
func (s *Store) ListProjectIDsForUser(userID string) ([]string, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var ids []string
|
|
var id string
|
|
rows, err = s.DB.Query(`SELECT p.public_id
|
|
FROM project_members m
|
|
JOIN projects p ON p.id = m.project_id
|
|
WHERE m.user_id = (SELECT id FROM users WHERE public_id = ?)`, userID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&id)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
ids = append(ids, id)
|
|
}
|
|
return ids, rows.Err()
|
|
}
|
|
|
|
func (s *Store) UpdateRepo(repo models.Repo) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`UPDATE repos SET name = ?, path = ? WHERE public_id = ?`, repo.Name, repo.Path, repo.ID)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) DeleteRepo(id string) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`DELETE FROM repos WHERE public_id = ?`, id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) AttachRepoToProject(projectID string, repoID string) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`INSERT INTO project_repos (project_id, repo_id, created_at)
|
|
VALUES ((SELECT id FROM projects WHERE public_id = ?), (SELECT id FROM repos WHERE public_id = ?), ?)`,
|
|
projectID, repoID, time.Now().UTC())
|
|
return err
|
|
}
|
|
|
|
func (s *Store) DetachRepoFromProject(projectID string, repoID string) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`DELETE FROM project_repos
|
|
WHERE project_id = (SELECT id FROM projects WHERE public_id = ?)
|
|
AND repo_id = (SELECT id FROM repos WHERE public_id = ?)`, projectID, repoID)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) GetRepoProjectIDs(repoID string) ([]string, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var ids []string
|
|
var id string
|
|
rows, err = s.DB.Query(`
|
|
SELECT p.public_id FROM repos r JOIN projects p ON p.id = r.project_id WHERE r.public_id = ?
|
|
UNION
|
|
SELECT p.public_id
|
|
FROM project_repos pr
|
|
JOIN projects p ON p.id = pr.project_id
|
|
WHERE pr.repo_id = (SELECT id FROM repos WHERE public_id = ?)
|
|
`, repoID, repoID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&id)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
ids = append(ids, id)
|
|
}
|
|
return ids, rows.Err()
|
|
}
|
|
|
|
func (s *Store) ListAvailableReposForProject(projectID string, query string, limit int, offset int) ([]models.Repo, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var repos []models.Repo
|
|
var r models.Repo
|
|
var created time.Time
|
|
if limit <= 0 {
|
|
limit = 100
|
|
}
|
|
if offset < 0 {
|
|
offset = 0
|
|
}
|
|
if query == "" {
|
|
rows, err = s.DB.Query(`
|
|
SELECT r.public_id, p.public_id, r.name, r.type, r.path, u.public_id, r.created_at
|
|
FROM repos r
|
|
JOIN projects p ON p.id = r.project_id
|
|
JOIN users u ON u.id = r.created_by
|
|
WHERE p.public_id <> ?
|
|
AND r.id NOT IN (
|
|
SELECT pr.repo_id
|
|
FROM project_repos pr
|
|
JOIN projects px ON px.id = pr.project_id
|
|
WHERE px.public_id = ?
|
|
)
|
|
ORDER BY r.name
|
|
LIMIT ? OFFSET ?
|
|
`, projectID, projectID, limit, offset)
|
|
} else {
|
|
rows, err = s.DB.Query(`
|
|
SELECT r.public_id, p.public_id, r.name, r.type, r.path, u.public_id, r.created_at
|
|
FROM repos r
|
|
JOIN projects p ON p.id = r.project_id
|
|
JOIN users u ON u.id = r.created_by
|
|
WHERE p.public_id <> ?
|
|
AND r.id NOT IN (
|
|
SELECT pr.repo_id
|
|
FROM project_repos pr
|
|
JOIN projects px ON px.id = pr.project_id
|
|
WHERE px.public_id = ?
|
|
)
|
|
AND r.name LIKE ?
|
|
ORDER BY r.name
|
|
LIMIT ? OFFSET ?
|
|
`, projectID, projectID, "%"+query+"%", limit, offset)
|
|
}
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&r.ID, &r.ProjectID, &r.Name, &r.Type, &r.Path, &r.CreatedBy, &created)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
r.CreatedAt = created.Unix()
|
|
r.IsForeign = false
|
|
repos = append(repos, r)
|
|
}
|
|
return repos, rows.Err()
|
|
}
|
|
|
|
func (s *Store) CreateIssue(issue models.Issue) (models.Issue, error) {
|
|
var id string
|
|
var err error
|
|
var now time.Time
|
|
var nowUnix int64
|
|
if issue.ID == "" {
|
|
id, err = util.NewID()
|
|
if err != nil {
|
|
return issue, err
|
|
}
|
|
issue.ID = id
|
|
}
|
|
now = time.Now().UTC()
|
|
nowUnix = now.Unix()
|
|
issue.CreatedAt = nowUnix
|
|
issue.UpdatedAt = nowUnix
|
|
if issue.Status == "" {
|
|
issue.Status = "open"
|
|
}
|
|
_, err = s.DB.Exec(`
|
|
INSERT INTO issues (public_id, project_id, title, body, status, created_by, assignee_id, created_at, updated_at)
|
|
VALUES (?, (SELECT id FROM projects WHERE public_id = ?), ?, ?, ?, (SELECT id FROM users WHERE public_id = ?), (SELECT id FROM users WHERE public_id = ?), ?, ?)
|
|
`, issue.ID, issue.ProjectID, issue.Title, issue.Body, issue.Status, issue.CreatedBy, issue.AssigneeID, now, now)
|
|
return issue, err
|
|
}
|
|
|
|
func (s *Store) UpdateIssue(issue models.Issue) error {
|
|
var err error
|
|
var now time.Time
|
|
var nowUnix int64
|
|
now = time.Now().UTC()
|
|
nowUnix = now.Unix()
|
|
issue.UpdatedAt = nowUnix
|
|
_, err = s.DB.Exec(`UPDATE issues SET title = ?, body = ?, status = ?, assignee_id = (SELECT id FROM users WHERE public_id = ?), updated_at = ? WHERE public_id = ?`,
|
|
issue.Title, issue.Body, issue.Status, issue.AssigneeID, now, issue.ID)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) GetIssue(id string) (models.Issue, error) {
|
|
var issue models.Issue
|
|
var row *sql.Row
|
|
var created time.Time
|
|
var updated time.Time
|
|
var err error
|
|
row = s.DB.QueryRow(`SELECT i.public_id, p.public_id, i.title, i.body, i.status, cu.public_id, COALESCE(au.public_id, ''), i.created_at, i.updated_at
|
|
FROM issues i
|
|
JOIN projects p ON p.id = i.project_id
|
|
JOIN users cu ON cu.id = i.created_by
|
|
LEFT JOIN users au ON au.id = i.assignee_id
|
|
WHERE i.public_id = ?`, id)
|
|
err = row.Scan(&issue.ID, &issue.ProjectID, &issue.Title, &issue.Body, &issue.Status, &issue.CreatedBy, &issue.AssigneeID, &created, &updated)
|
|
if err != nil {
|
|
return issue, err
|
|
}
|
|
issue.CreatedAt = created.Unix()
|
|
issue.UpdatedAt = updated.Unix()
|
|
return issue, nil
|
|
}
|
|
|
|
func (s *Store) ListIssues(projectID string) ([]models.Issue, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var issues []models.Issue
|
|
var issue models.Issue
|
|
var created time.Time
|
|
var updated time.Time
|
|
rows, err = s.DB.Query(`SELECT i.public_id, p.public_id, i.title, i.body, i.status, cu.public_id, COALESCE(au.public_id, ''), i.created_at, i.updated_at
|
|
FROM issues i
|
|
JOIN projects p ON p.id = i.project_id
|
|
JOIN users cu ON cu.id = i.created_by
|
|
LEFT JOIN users au ON au.id = i.assignee_id
|
|
WHERE i.project_id = (SELECT id FROM projects WHERE public_id = ?)
|
|
ORDER BY i.created_at DESC`, projectID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&issue.ID, &issue.ProjectID, &issue.Title, &issue.Body, &issue.Status, &issue.CreatedBy, &issue.AssigneeID, &created, &updated)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
issue.CreatedAt = created.Unix()
|
|
issue.UpdatedAt = updated.Unix()
|
|
issues = append(issues, issue)
|
|
}
|
|
return issues, rows.Err()
|
|
}
|
|
|
|
func (s *Store) AddIssueComment(comment models.IssueComment) (models.IssueComment, error) {
|
|
var id string
|
|
var err error
|
|
var now time.Time
|
|
var nowUnix int64
|
|
if comment.ID == "" {
|
|
id, err = util.NewID()
|
|
if err != nil {
|
|
return comment, err
|
|
}
|
|
comment.ID = id
|
|
}
|
|
now = time.Now().UTC()
|
|
nowUnix = now.Unix()
|
|
comment.CreatedAt = nowUnix
|
|
_, err = s.DB.Exec(`INSERT INTO issue_comments (public_id, issue_id, body, created_by, created_at)
|
|
VALUES (?, (SELECT id FROM issues WHERE public_id = ?), ?, (SELECT id FROM users WHERE public_id = ?), ?)`,
|
|
comment.ID, comment.IssueID, comment.Body, comment.CreatedBy, now)
|
|
return comment, err
|
|
}
|
|
|
|
func (s *Store) CreateWikiPage(page models.WikiPage) (models.WikiPage, error) {
|
|
var id string
|
|
var err error
|
|
var now time.Time
|
|
var nowUnix int64
|
|
if page.ID == "" {
|
|
id, err = util.NewID()
|
|
if err != nil {
|
|
return page, err
|
|
}
|
|
page.ID = id
|
|
}
|
|
now = time.Now().UTC()
|
|
nowUnix = now.Unix()
|
|
page.UpdatedAt = nowUnix
|
|
_, err = s.DB.Exec(`INSERT INTO wiki_pages (public_id, project_id, title, slug, body, created_by, updated_at)
|
|
VALUES (?, (SELECT id FROM projects WHERE public_id = ?), ?, ?, ?, (SELECT id FROM users WHERE public_id = ?), ?)`,
|
|
page.ID, page.ProjectID, page.Title, page.Slug, page.Body, page.CreatedBy, now)
|
|
return page, err
|
|
}
|
|
|
|
func (s *Store) UpdateWikiPage(page models.WikiPage) error {
|
|
var err error
|
|
var now time.Time
|
|
var nowUnix int64
|
|
now = time.Now().UTC()
|
|
nowUnix = now.Unix()
|
|
page.UpdatedAt = nowUnix
|
|
_, err = s.DB.Exec(`UPDATE wiki_pages SET title = ?, body = ?, updated_at = ? WHERE public_id = ?`, page.Title, page.Body, now, page.ID)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) ListWikiPages(projectID string) ([]models.WikiPage, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var pages []models.WikiPage
|
|
var page models.WikiPage
|
|
var updated time.Time
|
|
rows, err = s.DB.Query(`SELECT w.public_id, p.public_id, w.title, w.slug, w.body, u.public_id, w.updated_at
|
|
FROM wiki_pages w
|
|
JOIN projects p ON p.id = w.project_id
|
|
JOIN users u ON u.id = w.created_by
|
|
WHERE w.project_id = (SELECT id FROM projects WHERE public_id = ?)
|
|
ORDER BY w.title`, projectID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&page.ID, &page.ProjectID, &page.Title, &page.Slug, &page.Body, &page.CreatedBy, &updated)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
page.UpdatedAt = updated.Unix()
|
|
pages = append(pages, page)
|
|
}
|
|
return pages, rows.Err()
|
|
}
|
|
|
|
func (s *Store) GetWikiPage(id string) (models.WikiPage, error) {
|
|
var page models.WikiPage
|
|
var row *sql.Row
|
|
var updated time.Time
|
|
var err error
|
|
row = s.DB.QueryRow(`SELECT w.public_id, p.public_id, w.title, w.slug, w.body, u.public_id, w.updated_at
|
|
FROM wiki_pages w
|
|
JOIN projects p ON p.id = w.project_id
|
|
JOIN users u ON u.id = w.created_by
|
|
WHERE w.public_id = ?`, id)
|
|
err = row.Scan(&page.ID, &page.ProjectID, &page.Title, &page.Slug, &page.Body, &page.CreatedBy, &updated)
|
|
if err != nil {
|
|
return page, err
|
|
}
|
|
page.UpdatedAt = updated.Unix()
|
|
return page, nil
|
|
}
|
|
|
|
func (s *Store) CreateUpload(upload models.Upload) (models.Upload, error) {
|
|
var id string
|
|
var err error
|
|
var now time.Time
|
|
var nowUnix int64
|
|
if upload.ID == "" {
|
|
id, err = util.NewID()
|
|
if err != nil {
|
|
return upload, err
|
|
}
|
|
upload.ID = id
|
|
}
|
|
now = time.Now().UTC()
|
|
nowUnix = now.Unix()
|
|
upload.CreatedAt = nowUnix
|
|
_, err = s.DB.Exec(`INSERT INTO uploads (public_id, project_id, filename, content_type, size, storage_path, created_by, created_at)
|
|
VALUES (?, (SELECT id FROM projects WHERE public_id = ?), ?, ?, ?, ?, (SELECT id FROM users WHERE public_id = ?), ?)`,
|
|
upload.ID, upload.ProjectID, upload.Filename, upload.ContentType, upload.Size, upload.StoragePath, upload.CreatedBy, now)
|
|
return upload, err
|
|
}
|
|
|
|
func (s *Store) GetUpload(id string) (models.Upload, error) {
|
|
var upload models.Upload
|
|
var row *sql.Row
|
|
var created time.Time
|
|
var err error
|
|
row = s.DB.QueryRow(`SELECT up.public_id, p.public_id, up.filename, up.content_type, up.size, up.storage_path, u.public_id, up.created_at
|
|
FROM uploads up
|
|
JOIN projects p ON p.id = up.project_id
|
|
JOIN users u ON u.id = up.created_by
|
|
WHERE up.public_id = ?`, id)
|
|
err = row.Scan(&upload.ID, &upload.ProjectID, &upload.Filename, &upload.ContentType, &upload.Size, &upload.StoragePath, &upload.CreatedBy, &created)
|
|
if err != nil {
|
|
return upload, err
|
|
}
|
|
upload.CreatedAt = created.Unix()
|
|
return upload, nil
|
|
}
|
|
|
|
func (s *Store) ListUploads(projectID string) ([]models.Upload, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var uploads []models.Upload
|
|
var upload models.Upload
|
|
var created time.Time
|
|
rows, err = s.DB.Query(`SELECT up.public_id, p.public_id, up.filename, up.content_type, up.size, up.storage_path, u.public_id, up.created_at
|
|
FROM uploads up
|
|
JOIN projects p ON p.id = up.project_id
|
|
JOIN users u ON u.id = up.created_by
|
|
WHERE up.project_id = (SELECT id FROM projects WHERE public_id = ?)
|
|
ORDER BY up.created_at DESC`, projectID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&upload.ID, &upload.ProjectID, &upload.Filename, &upload.ContentType, &upload.Size, &upload.StoragePath, &upload.CreatedBy, &created)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
upload.CreatedAt = created.Unix()
|
|
uploads = append(uploads, upload)
|
|
}
|
|
return uploads, rows.Err()
|
|
}
|
|
|
|
func (s *Store) RequireAdmin(user models.User) error {
|
|
if !user.IsAdmin {
|
|
return errors.New("admin required")
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func mustID() string {
|
|
var id string
|
|
var err error
|
|
id, err = util.NewID()
|
|
if err != nil {
|
|
panic(err)
|
|
}
|
|
return id
|
|
}
|
|
|
|
func nullIfEmpty(value string) interface{} {
|
|
if value == "" {
|
|
return nil
|
|
}
|
|
return value
|
|
}
|