database

package module
v0.0.0-...-033b51a Latest Latest
Warning

This package is not in the latest version of its module.

Go to latest
Published: Nov 10, 2021 License: AGPL-3.0 Imports: 10 Imported by: 3

Documentation

Index

Constants

View Source
const (
	BCRYPT_ITERS  = 12
	TOKEN_LENGTH  = 24
	SECRET_LENGTH = 128
	TOKEN_TTL     = 60 * 60 * 24
)
View Source
const (
	USER_TABLE         = "users"
	CONTENT_TABLE      = "content"
	AUTH_TABLE         = "auth"
	TOKEN_TABLE        = "token"
	SECRET_TABLE       = "secret"
	TAG_TABLE          = "tags"
	SUBSCRIPTION_TABLE = "subs"
	BAN_TABLE          = "bans"
	REPORT_TABLE       = "reports"
)
View Source
const (
	CONTENT_FIELDS = `` /* 140-byte string literal not displayed */

	USER_FIELDS = `
id,
email,
nick,
bio,
subscriber_count,
subscription_count,
post_count,
created,
moderator,
admin`
	BAN_FIELDS = `
id,
banner,
banned,
reason,
created,
expires,
forever`
	REPORT_FIELDS = `
id,
reporter,
reported,
type,
reason,
created,
resolved,
resolution`

	READ_INDEX_OF_CONTENT                = "SELECT order_index FROM " + CONTENT_TABLE + " WHERE id=? LIMIT 1"
	READ_CONTENT_ID                      = "SELECT " + CONTENT_FIELDS + " FROM " + CONTENT_TABLE + " WHERE id=? LIMIT 1"
	READ_MANY_CONTENT_AFTER_ID           = "SELECT " + CONTENT_FIELDS + " FROM " + CONTENT_TABLE + " WHERE order_index<(" + READ_INDEX_OF_CONTENT + ") ORDER BY order_index DESC LIMIT ?"
	READ_MANY_CONTENT                    = "SELECT " + CONTENT_FIELDS + " FROM " + CONTENT_TABLE + " ORDER BY order_index DESC LIMIT ?"
	READ_MANY_CONTENT_OF_AUTHOR          = "SELECT " + CONTENT_FIELDS + " FROM " + CONTENT_TABLE + " WHERE author=? ORDER BY order_index DESC LIMIT ?"
	READ_MANY_CONTENT_OF_AUTHOR_AFTER_ID = "SELECT " + CONTENT_FIELDS + " FROM " + CONTENT_TABLE + " WHERE author=? AND order_index<(" + READ_INDEX_OF_CONTENT + ") ORDER BY order_index DESC LIMIT ?"
	DELETE_CONTENT_ID                    = "DELETE FROM " + CONTENT_TABLE + " WHERE id=? LIMIT 1"

	READ_TAGS_OF_ID       = "SELECT tag FROM " + TAG_TABLE + " WHERE id=?"
	READ_TAGS_OF_MANY_ID  = "SELECT id, tag FROM " + TAG_TABLE + " WHERE id IN "
	WRITE_TAGS_OF_MANY_ID = "REPLACE INTO " + TAG_TABLE + " (id, tag, created) VALUES "
	DELETE_TAGS_OF_ID     = "DELETE FROM " + TAG_TABLE + " WHERE id=?"

	READ_USER_OF_ID                 = "SELECT " + USER_FIELDS + " FROM " + USER_TABLE + " WHERE id=? LIMIT 1"
	READ_USER_OF_EMAIL              = "SELECT " + USER_FIELDS + " FROM " + USER_TABLE + " WHERE email=? LIMIT 1"
	READ_USER_OF_NICK               = "SELECT " + USER_FIELDS + " FROM " + USER_TABLE + " WHERE nick=? LIMIT 1"
	DELETE_USER_OF_ID               = "DELETE FROM " + USER_TABLE + " WHERE id=? LIMIT 1"
	INCREMENT_USER_POST_COUNT_OF_ID = "UPDATE " + USER_TABLE + " SET post_count=post_count+1 WHERE id=?"
	READ_ANY_PRIVILEGE_OF_ID        = "SELECT admin, moderator FROM " + USER_TABLE + " WHERE id=?"
	READ_MODERATOR_OF_ID            = "SELECT moderator FROM " + USER_TABLE + " WHERE id=?"
	READ_ADMIN_OF_ID                = "SELECT admin FROM " + USER_TABLE + " WHERE id=?"
	WRITE_MODERATOR_OF_ID           = "UPDATE " + USER_TABLE + " SET moderator=? WHERE id=?"
	WRITE_ADMIN_OF_ID               = "UPDATE " + USER_TABLE + " SET admin=? WHERE id=?"

	READ_INDEX_OF_BAN          = "SELECT order_index FROM " + BAN_TABLE + " WHERE id=? LIMIT 1"
	READ_BAN_OF_ID             = "SELECT " + BAN_FIELDS + " FROM " + BAN_TABLE + " WHERE id=? LIMIT 1"
	READ_BANS_OF_USER          = "SELECT " + BAN_FIELDS + " FROM " + BAN_TABLE + " WHERE banned=? ORDER BY order_index DESC LIMIT ?"
	READ_BANS_OF_USER_AFTER_ID = "SELECT " + BAN_FIELDS + " FROM " + BAN_TABLE + " WHERE banned=? AND order_index<(" + READ_INDEX_OF_BAN + ") ORDER BY order_index DESC LIMIT ?"
	READ_BANS_OF_USER_COUNT    = "SELECT COUNT(id) FROM " + BAN_TABLE + " WHERE (banned=? AND forever) OR (banned=? AND expires>?) LIMIT 1"

	READ_REPORT_OF_ID                = "SELECT " + REPORT_FIELDS + " FROM " + REPORT_TABLE + " WHERE id=?"
	READ_INDEX_OF_REPORT             = "SELECT order_index FROM " + REPORT_TABLE + " WHERE id=? LIMIT 1"
	READ_REPORTS_UNRESOLVED          = "SELECT " + REPORT_FIELDS + " FROM " + REPORT_TABLE + " WHERE resolved=0 ORDER BY order_index DESC LIMIT ?"
	READ_REPORTS_UNRESOLVED_AFTER_ID = "SELECT " + REPORT_FIELDS + " FROM " + REPORT_TABLE + " WHERE resolved=0 AND order_index<(" + READ_INDEX_OF_REPORT + ") ORDER BY order_index DESC LIMIT ?"

	WRITE_SECRET_OF_ID  = "REPLACE INTO " + SECRET_TABLE + " (id, secret) VALUES (?, ?)"
	READ_SECRET_OF_ID   = "SELECT secret FROM " + SECRET_TABLE + " WHERE id=? LIMIT 1"
	DELETE_SECRET_OF_ID = "DELETE FROM " + SECRET_TABLE + " WHERE id=? LIMIT 1"

	WRITE_TOKEN_OF_ID  = "REPLACE INTO " + TOKEN_TABLE + " (id, token, created) VALUES (?, ?, ?)"
	READ_TOKEN_STAT    = "SELECT id, created FROM " + TOKEN_TABLE + " WHERE token=? LIMIT 1"
	DELETE_TOKEN       = "DELETE FROM " + TOKEN_TABLE + " WHERE token=?"
	DELETE_TOKEN_OF_ID = "DELETE FROM " + TOKEN_TABLE + " WHERE id=?"

	READ_HASH_OF_ID  = "SELECT hash FROM " + AUTH_TABLE + " WHERE id=? LIMIT 1"
	WRITE_HASH_OF_ID = "REPLACE INTO " + AUTH_TABLE + " (id, hash) VALUES (?, ?)"
)

Variables

This section is empty.

Functions

func CheckPassword

func CheckPassword(ID, password string) (valid bool, err error)

*

  • Check that password `password` matches the hash for user of id `ID`
  • Done in one query:
  • read hash: SELECT hash FROM AUTH_TABLE WHERE id=ID LIMIT 1

func CheckSecret

func CheckSecret(ID, secret string) (valid bool, err error)

*

  • Check that a secret `secret` for some user of id `ID` matches
  • Done in one query:
  • read secret: SELECT secret FROM SECRET_TABLE WHERE id=ID LIMIT 1

func Connect

func Connect(address string)

*

  • Connect to a database, given a connection string
  • If the connection fails a ping, this function wil panic with the err
  • The conenction string should look something like
  • user:pass@tcp(addr)/table

func Create

func Create()

func CreateSecret

func CreateSecret(ID string) (secret string, err error)

*

  • Create a secret for some user of id `ID`
  • Any existing secret for that user is destroyed
  • Done in one query:
  • update secret REPLACE INTO SECRET_TABLE (id, secret) VALUES ID, new_secret

func CreateToken

func CreateToken(ID string) (token string, expires int64, err error)

*

  • Create a token for some user of id `ID` that expires in 24 hours
  • Any existing token for that user is destroyed
  • Done in one query:
  • update secret REPLACE INTO TOKEN_TABLE (id, token, created) VALUES ID, new_token, now

func DeleteContent

func DeleteContent(ID string) (err error)

*

  • Delete some content of id `ID`
  • Uses 1 querie
  • delete content: DELETE FROM CONTENT_TABLE WHERE id=ID LIMIT 1

func DeleteUser

func DeleteUser(ID string) (err error)

*

  • Delete some user from USER_TABLE
  • Uses 1 query:
  • delete user: DELETE FROM USER_TABLE WHERE id=ID LIMIT 1

func EmptyTable

func EmptyTable(table string) (err error)

func Health

func Health() (err error)

*

  • Ping the database, and return any error
  • useful for health checks

func IncrementPostCount

func IncrementPostCount(ID string) (err error)

*

  • Increment the post count of user of id `ID` by one
  • Done in one query
  • increment: UPDATE USER_TABLE SET post_count=post_count+1 WHERE id=ID

func IsAdmin

func IsAdmin(ID string) (admin bool, err error)

func IsBanned

func IsBanned(ID string) (banned bool, err error)

*

  • Get whether or not a user is banned, either by a permanent ban, or an expirable ban
  • Done in one query

func IsModerator

func IsModerator(ID string) (moderator bool, err error)

func ReadAuthorContent

func ReadAuthorContent(ID, before string, count int) (content []types.Content, size int, err error)

*

  • Same as ReadManyContent but for some author of id `ID`
  • Uses 2 queries
  • get content: SELECT * FROM CONTENT_TABLE ORDER BY created DESC LIMIT offset, count
  • queries from: getManyTags

func ReadBansOfUser

func ReadBansOfUser(ID, before string, count int) (bans []types.Ban, size int, err error)

*

  • Read a slice of bans of a user
  • Done in one query

func ReadManyContent

func ReadManyContent(before string, count int) (content []types.Content, size int, err error)

*

  • Read `count` number of contents, before content of id `before`
  • If the first set of content should be read, `before` may be empty
  • Newest posts are returned first
  • Uses 2 queries
  • get content: SELECT * FROM CONTENT_TABLE ORDER BY created DESC LIMIT offset, count
  • queries from: getManyTags

func ReadManyUnresolvedReport

func ReadManyUnresolvedReport(before string, count int) (reports []types.Report, size int, err error)

*

  • Read a slice of unresolved reports (ie, the mod queue) by order of most recent
  • Done in one query

func ReadSingleBan

func ReadSingleBan(ID string) (ban types.Ban, exists bool, err error)

*

  • Read a single ban of id `ID`
  • Done in one query

func ReadSingleContent

func ReadSingleContent(ID string) (content types.Content, exists bool, err error)

*

  • Read some content of id `ID`
  • Uses 2 queries
  • get content: SELECT * FROM CONTENT_TABLE WHERE id=ID LIMIT 1
  • get tags: SELECT tag FROM TAG_TABLE WHERE id=ID

func ReadSingleReport

func ReadSingleReport(ID string) (report types.Report, exists bool, err error)

*

  • Lookup single report by it's ID
  • Done in one query

func ReadSingleUser

func ReadSingleUser(ID string) (user types.User, exists bool, err error)

*

  • Read some user of id `ID` from USER_TABLE
  • Uses 1 query
  • read user: SELECT * FROM USER_TABLE WHERE id=ID LIMIT 1

func ReadSingleUserEmail

func ReadSingleUserEmail(email string) (user types.User, exists bool, err error)

*

  • Read some user of email `email` from USER_TABLE
  • Works in the same way as ReadSingleUser, but with email
  • Uses 1 query
  • read user: SELECT * FROM USER_TABLE WHERE email=email LIMIT 1

func ReadSingleUserNick

func ReadSingleUserNick(nick string) (user types.User, exists bool, err error)

*

  • Read some user of email `email` from USER_TABLE
  • Works in the same way as ReadSingleUser, but with nick
  • Uses 1 query
  • read user: SELECT * FROM USER_TABLE WHERE nick=nick LIMIT 1

func ReadTokenStat

func ReadTokenStat(token string) (owner string, valid bool, err error)

*

  • Read information about some token `token`
  • Returns who it belongs to, and whether or not it's valid
  • done in one query:
  • read token: SELECT id, created FROM TOKEN_TABLE WHERE token=? LIMIT 1

func RevokeSecretOf

func RevokeSecretOf(ID string) (err error)

*

  • Revoke the secret of some user of id `ID`
  • Done in one query:
  • delete row: DELETE FROM SECRET_TABLE WHERE id=ID LIMIT 1

func RevokeToken

func RevokeToken(token string) (err error)

*

  • Revoke some token `token`
  • Done in one query:
  • delete row: DELETE FROM TOKEN_TABLE WHERE token=token LIMIT 1

func RevokeTokenOf

func RevokeTokenOf(ID string) (err error)

*

  • Revoke the token of some user of id `ID`
  • Done in one query:
  • delete row: DELETE FROM TOKEN_TABLE WHERE id=ID LIMIT 1

func SetAdmin

func SetAdmin(ID string, state bool) (err error)

func SetModerator

func SetModerator(ID string, state bool) (err error)

func SetPassword

func SetPassword(ID, password string) (err error)

*

  • Set a password `password` for some user of id `ID`
  • Done in one query:
  • write row: REPLACE INTO AUTH_TABLE (id, hash) VALUES (ID, hash(password))

func WriteBan

func WriteBan(ban map[string]interface{}) (err error)

func WriteContent

func WriteContent(content map[string]interface{}) (err error)

*

  • Write some content `content` to the table CONTENT_TABLE
  • Uses 3 query
  • write content: REPLACE INTO CONTENT_TABLE (keys...) VALUES (values...)
  • queries from: setTags
  • Returns error, if any

func WriteReport

func WriteReport(report map[string]interface{}) (err error)

*

  • Create or update a report for some user
  • Done in one query

func WriteUser

func WriteUser(user map[string]interface{}) (err error)

*

  • Write some user `user` into USER_TABLE
  • Uses 1 query
  • write user: REPLACE INTO CONTENT_TABLE (keys...) VALUES (values...)
  • Returns error, if any

Types

This section is empty.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL