writefreely/database.go

/*
 * Copyright © 2018-2021 Musing Studio LLC.
 *
 * This file is part of WriteFreely.
 *
 * WriteFreely is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License, included
 * in the LICENSE file in this source code package.
 */

package writefreely

import (
	"context"
	"database/sql"
	"fmt"
	"github.com/writeas/web-core/silobridge"
	wf_db "github.com/writefreely/writefreely/db"
	"net/http"
	"net/url"
	"strings"
	"time"

	"github.com/guregu/null"
	"github.com/guregu/null/zero"
	uuid "github.com/nu7hatch/gouuid"
	"github.com/writeas/activityserve"
	"github.com/writeas/impart"
	"github.com/writeas/web-core/activitypub"
	"github.com/writeas/web-core/auth"
	"github.com/writeas/web-core/data"
	"github.com/writeas/web-core/id"
	"github.com/writeas/web-core/log"
	"github.com/writeas/web-core/query"
	"github.com/writefreely/writefreely/author"
	"github.com/writefreely/writefreely/config"
	"github.com/writefreely/writefreely/key"
)

const (
	mySQLErrDuplicateKey = 1062
	mySQLErrCollationMix = 1267
	mySQLErrTooManyConns = 1040
	mySQLErrMaxUserConns = 1203

	driverMySQL  = "mysql"
	driverSQLite = "sqlite3"
)

var (
	SQLiteEnabled bool
)

type writestore interface {
	CreateUser(*config.Config, *User, string, string) error
	UpdateUserEmail(keys *key.Keychain, userID int64, email string) error
	UpdateEncryptedUserEmail(int64, []byte) error
	GetUserByID(int64) (*User, error)
	GetUserForAuth(string) (*User, error)
	GetUserForAuthByID(int64) (*User, error)
	GetUserNameFromToken(string) (string, error)
	GetUserDataFromToken(string) (int64, string, error)
	GetAPIUser(header string) (*User, error)
	GetUserID(accessToken string) int64
	GetUserIDPrivilege(accessToken string) (userID int64, sudo bool)
	DeleteToken(accessToken []byte) error
	FetchLastAccessToken(userID int64) string
	GetAccessToken(userID int64) (string, error)
	GetTemporaryAccessToken(userID int64, validSecs int) (string, error)
	GetTemporaryOneTimeAccessToken(userID int64, validSecs int, oneTime bool) (string, error)
	DeleteAccount(userID int64) error
	ChangeSettings(app *App, u *User, s *userSettings) error
	ChangePassphrase(userID int64, sudo bool, curPass string, hashedPass []byte) error

	GetCollections(u *User, hostName string) (*[]Collection, error)
	GetPublishableCollections(u *User, hostName string) (*[]Collection, error)
	GetMeStats(u *User) userMeStats
	GetTotalCollections() (int64, error)
	GetTotalPosts() (int64, error)
	GetTopPosts(u *User, alias string, hostName string) (*[]PublicPost, error)
	GetAnonymousPosts(u *User, page int) (*[]PublicPost, error)
	GetUserPosts(u *User) (*[]PublicPost, error)

	CreateOwnedPost(post *SubmittedPost, accessToken, collAlias, hostName string) (*PublicPost, error)
	CreatePost(userID, collID int64, post *SubmittedPost) (*Post, error)
	UpdateOwnedPost(post *AuthenticatedPost, userID int64) error
	GetEditablePost(id, editToken string) (*PublicPost, error)
	PostIDExists(id string) bool
	GetPost(id string, collectionID int64) (*PublicPost, error)
	GetOwnedPost(id string, ownerID int64) (*PublicPost, error)
	GetPostProperty(id string, collectionID int64, property string) (interface{}, error)

	CreateCollectionFromToken(*config.Config, string, string, string) (*Collection, error)
	CreateCollection(*config.Config, string, string, int64) (*Collection, error)
	GetCollectionBy(condition string, value interface{}) (*Collection, error)
	GetCollection(alias string) (*Collection, error)
	GetCollectionForPad(alias string) (*Collection, error)
	GetCollectionByID(id int64) (*Collection, error)
	UpdateCollection(app *App, c *SubmittedCollection, alias string) error
	DeleteCollection(alias string, userID int64) error

	UpdatePostPinState(pinned bool, postID string, collID, ownerID, pos int64) error
	GetLastPinnedPostPos(collID int64) int64
	GetPinnedPosts(coll *CollectionObj, includeFuture bool) (*[]PublicPost, error)
	RemoveCollectionRedirect(t *sql.Tx, alias string) error
	GetCollectionRedirect(alias string) (new string)
	IsCollectionAttributeOn(id int64, attr string) bool
	CollectionHasAttribute(id int64, attr string) bool

	CanCollect(cpr *ClaimPostRequest, userID int64) bool
	AttemptClaim(p *ClaimPostRequest, query string, params []interface{}, slugIdx int) (sql.Result, error)
	DispersePosts(userID int64, postIDs []string) (*[]ClaimPostResult, error)
	ClaimPosts(cfg *config.Config, userID int64, collAlias string, posts *[]ClaimPostRequest) (*[]ClaimPostResult, error)

	GetPostsCount(c *CollectionObj, includeFuture bool)
	GetPosts(cfg *config.Config, c *Collection, page int, includeFuture, forceRecentFirst, includePinned bool) (*[]PublicPost, error)
	GetAllPostsTaggedIDs(c *Collection, tag string, includeFuture bool) ([]string, error)
	GetPostsTagged(cfg *config.Config, c *Collection, tag string, page int, includeFuture bool) (*[]PublicPost, error)

	GetAPFollowers(c *Collection) (*[]RemoteUser, error)
	GetAPActorKeys(collectionID int64) ([]byte, []byte)
	CreateUserInvite(id string, userID int64, maxUses int, expires *time.Time) error
	GetUserInvites(userID int64) (*[]Invite, error)
	GetUserInvite(id string) (*Invite, error)
	GetUsersInvitedCount(id string) int64
	CreateInvitedUser(inviteID string, userID int64) error

	GetDynamicContent(id string) (*instanceContent, error)
	UpdateDynamicContent(id, title, content, contentType string) error
	GetAllUsers(page uint) (*[]User, error)
	GetAllUsersCount() int64
	GetUserLastPostTime(id int64) (*time.Time, error)
	GetCollectionLastPostTime(id int64) (*time.Time, error)

	GetIDForRemoteUser(context.Context, string, string, string) (int64, error)
	RecordRemoteUserID(context.Context, int64, string, string, string, string) error
	ValidateOAuthState(context.Context, string) (string, string, int64, string, error)
	GenerateOAuthState(context.Context, string, string, int64, string) (string, error)
	GetOauthAccounts(ctx context.Context, userID int64) ([]oauthAccountInfo, error)
	RemoveOauth(ctx context.Context, userID int64, provider string, clientID string, remoteUserID string) error

	DatabaseInitialized() bool
}

type datastore struct {
	*sql.DB
	driverName string
}

var _ writestore = &datastore{}

func (db *datastore) now() string {
	if db.driverName == driverSQLite {
		return "strftime('%Y-%m-%d %H:%M:%S','now')"
	}
	return "NOW()"
}

func (db *datastore) clip(field string, l int) string {
	if db.driverName == driverSQLite {
		return fmt.Sprintf("SUBSTR(%s, 0, %d)", field, l)
	}
	return fmt.Sprintf("LEFT(%s, %d)", field, l)
}

func (db *datastore) upsert(indexedCols ...string) string {
	if db.driverName == driverSQLite {
		// NOTE: SQLite UPSERT syntax only works in v3.24.0 (2018-06-04) or later
		// Leaving this for whenever we can upgrade and include it in our binary
		cc := strings.Join(indexedCols, ", ")
		return "ON CONFLICT(" + cc + ") DO UPDATE SET"
	}
	return "ON DUPLICATE KEY UPDATE"
}

func (db *datastore) dateSub(l int, unit string) string {
	if db.driverName == driverSQLite {
		return fmt.Sprintf("DATETIME('now', '-%d %s')", l, unit)
	}
	return fmt.Sprintf("DATE_SUB(NOW(), INTERVAL %d %s)", l, unit)
}

// CreateUser creates a new user in the database from the given User, UPDATING it in the process with the user's ID.
func (db *datastore) CreateUser(cfg *config.Config, u *User, collectionTitle string, collectionDesc string) error {
	if db.PostIDExists(u.Username) {
		return impart.HTTPError{http.StatusConflict, "Invalid collection name."}
	}

	// New users get a `users` and `collections` row.
	t, err := db.Begin()
	if err != nil {
		return err
	}

	// 1. Add to `users` table
	// NOTE: Assumes User's Password is already hashed!
	res, err := t.Exec("INSERT INTO users (username, password, email) VALUES (?, ?, ?)", u.Username, u.HashedPass, u.Email)
	if err != nil {
		t.Rollback()
		if db.isDuplicateKeyErr(err) {
			return impart.HTTPError{http.StatusConflict, "Username is already taken."}
		}

		log.Error("Rolling back users INSERT: %v\n", err)
		return err
	}
	u.ID, err = res.LastInsertId()
	if err != nil {
		t.Rollback()
		log.Error("Rolling back after LastInsertId: %v\n", err)
		return err
	}

	// 2. Create user's Collection
	if collectionTitle == "" {
		collectionTitle = u.Username
	}
	res, err = t.Exec("INSERT INTO collections (alias, title, description, privacy, owner_id, view_count) VALUES (?, ?, ?, ?, ?, ?)", u.Username, collectionTitle, collectionDesc, defaultVisibility(cfg), u.ID, 0)
	if err != nil {
		t.Rollback()
		if db.isDuplicateKeyErr(err) {
			return impart.HTTPError{http.StatusConflict, "Username is already taken."}
		}
		log.Error("Rolling back collections INSERT: %v\n", err)
		return err
	}

	db.RemoveCollectionRedirect(t, u.Username)

	err = t.Commit()
	if err != nil {
		t.Rollback()
		log.Error("Rolling back after Commit(): %v\n", err)
		return err
	}

	return nil
}

// FIXME: We're returning errors inconsistently in this file. Do we use Errorf
// for returned value, or impart?
func (db *datastore) UpdateUserEmail(keys *key.Keychain, userID int64, email string) error {
	encEmail, err := data.Encrypt(keys.EmailKey, email)
	if err != nil {
		return fmt.Errorf("Couldn't encrypt email %s: %s\n", email, err)
	}

	return db.UpdateEncryptedUserEmail(userID, encEmail)
}

func (db *datastore) UpdateEncryptedUserEmail(userID int64, encEmail []byte) error {
	_, err := db.Exec("UPDATE users SET email = ? WHERE id = ?", encEmail, userID)
	if err != nil {
		return fmt.Errorf("Unable to update user email: %s", err)
	}

	return nil
}

func (db *datastore) CreateCollectionFromToken(cfg *config.Config, alias, title, accessToken string) (*Collection, error) {
	userID := db.GetUserID(accessToken)
	if userID == -1 {
		return nil, ErrBadAccessToken
	}

	return db.CreateCollection(cfg, alias, title, userID)
}

func (db *datastore) GetUserCollectionCount(userID int64) (uint64, error) {
	var collCount uint64
	err := db.QueryRow("SELECT COUNT(*) FROM collections WHERE owner_id = ?", userID).Scan(&collCount)
	switch {
	case err == sql.ErrNoRows:
		return 0, impart.HTTPError{http.StatusInternalServerError, "Couldn't retrieve user from database."}
	case err != nil:
		log.Error("Couldn't get collections count for user %d: %v", userID, err)
		return 0, err
	}

	return collCount, nil
}

func (db *datastore) CreateCollection(cfg *config.Config, alias, title string, userID int64) (*Collection, error) {
	if db.PostIDExists(alias) {
		return nil, impart.HTTPError{http.StatusConflict, "Invalid collection name."}
	}

	// All good, so create new collection
	res, err := db.Exec("INSERT INTO collections (alias, title, description, privacy, owner_id, view_count) VALUES (?, ?, ?, ?, ?, ?)", alias, title, "", defaultVisibility(cfg), userID, 0)
	if err != nil {
		if db.isDuplicateKeyErr(err) {
			return nil, impart.HTTPError{http.StatusConflict, "Collection already exists."}
		}
		log.Error("Couldn't add to collections: %v\n", err)
		return nil, err
	}

	c := &Collection{
		Alias:       alias,
		Title:       title,
		OwnerID:     userID,
		PublicOwner: false,
		Public:      defaultVisibility(cfg) == CollPublic,
	}

	c.ID, err = res.LastInsertId()
	if err != nil {
		log.Error("Couldn't get collection LastInsertId: %v\n", err)
	}

	return c, nil
}

func (db *datastore) GetUserByID(id int64) (*User, error) {
	u := &User{ID: id}

	err := db.QueryRow("SELECT username, password, email, created, status FROM users WHERE id = ?", id).Scan(&u.Username, &u.HashedPass, &u.Email, &u.Created, &u.Status)
	switch {
	case err == sql.ErrNoRows:
		return nil, ErrUserNotFound
	case err != nil:
		log.Error("Couldn't SELECT user password: %v", err)
		return nil, err
	}

	return u, nil
}

// IsUserSilenced returns true if the user account associated with id is
// currently silenced.
func (db *datastore) IsUserSilenced(id int64) (bool, error) {
	u := &User{ID: id}

	err := db.QueryRow("SELECT status FROM users WHERE id = ?", id).Scan(&u.Status)
	switch {
	case err == sql.ErrNoRows:
		return false, ErrUserNotFound
	case err != nil:
		log.Error("Couldn't SELECT user status: %v", err)
		return false, fmt.Errorf("is user silenced: %v", err)
	}

	return u.IsSilenced(), nil
}

// DoesUserNeedAuth returns true if the user hasn't provided any methods for
// authenticating with the account, such a passphrase or email address.
// Any errors are reported to admin and silently quashed, returning false as the
// result.
func (db *datastore) DoesUserNeedAuth(id int64) bool {
	var pass, email []byte

	// Find out if user has an email set first
	err := db.QueryRow("SELECT password, email FROM users WHERE id = ?", id).Scan(&pass, &email)
	switch {
	case err == sql.ErrNoRows:
		// ERROR. Don't give false positives on needing auth methods
		return false
	case err != nil:
		// ERROR. Don't give false positives on needing auth methods
		log.Error("Couldn't SELECT user %d from users: %v", id, err)
		return false
	}
	// User doesn't need auth if there's an email
	return len(email) == 0 && len(pass) == 0
}

func (db *datastore) IsUserPassSet(id int64) (bool, error) {
	var pass []byte
	err := db.QueryRow("SELECT password FROM users WHERE id = ?", id).Scan(&pass)
	switch {
	case err == sql.ErrNoRows:
		return false, nil
	case err != nil:
		log.Error("Couldn't SELECT user %d from users: %v", id, err)
		return false, err
	}

	return len(pass) > 0, nil
}

func (db *datastore) GetUserForAuth(username string) (*User, error) {
	u := &User{Username: username}

	err := db.QueryRow("SELECT id, password, email, created, status FROM users WHERE username = ?", username).Scan(&u.ID, &u.HashedPass, &u.Email, &u.Created, &u.Status)
	switch {
	case err == sql.ErrNoRows:
		// Check if they've entered the wrong, unnormalized username
		username = getSlug(username, "")
		if username != u.Username {
			err = db.QueryRow("SELECT id FROM users WHERE username = ? LIMIT 1", username).Scan(&u.ID)
			if err == nil {
				return db.GetUserForAuth(username)
			}
		}
		return nil, ErrUserNotFound
	case err != nil:
		log.Error("Couldn't SELECT user password: %v", err)
		return nil, err
	}

	return u, nil
}

func (db *datastore) GetUserForAuthByID(userID int64) (*User, error) {
	u := &User{ID: userID}

	err := db.QueryRow("SELECT id, password, email, created, status FROM users WHERE id = ?", u.ID).Scan(&u.ID, &u.HashedPass, &u.Email, &u.Created, &u.Status)
	switch {
	case err == sql.ErrNoRows:
		return nil, ErrUserNotFound
	case err != nil:
		log.Error("Couldn't SELECT userForAuthByID: %v", err)
		return nil, err
	}

	return u, nil
}

func (db *datastore) GetUserNameFromToken(accessToken string) (string, error) {
	t := auth.GetToken(accessToken)
	if len(t) == 0 {
		return "", ErrNoAccessToken
	}

	var oneTime bool
	var username string
	err := db.QueryRow("SELECT username, one_time FROM accesstokens LEFT JOIN users ON user_id = id WHERE token LIKE ? AND (expires IS NULL OR expires > "+db.now()+")", t).Scan(&username, &oneTime)
	switch {
	case err == sql.ErrNoRows:
		return "", ErrBadAccessToken
	case err != nil:
		return "", ErrInternalGeneral
	}

	// Delete token if it was one-time
	if oneTime {
		db.DeleteToken(t[:])
	}

	return username, nil
}

func (db *datastore) GetUserDataFromToken(accessToken string) (int64, string, error) {
	t := auth.GetToken(accessToken)
	if len(t) == 0 {
		return 0, "", ErrNoAccessToken
	}

	var userID int64
	var oneTime bool
	var username string
	err := db.QueryRow("SELECT user_id, username, one_time FROM accesstokens LEFT JOIN users ON user_id = id WHERE token LIKE ? AND (expires IS NULL OR expires > "+db.now()+")", t).Scan(&userID, &username, &oneTime)
	switch {
	case err == sql.ErrNoRows:
		return 0, "", ErrBadAccessToken
	case err != nil:
		return 0, "", ErrInternalGeneral
	}

	// Delete token if it was one-time
	if oneTime {
		db.DeleteToken(t[:])
	}

	return userID, username, nil
}

func (db *datastore) GetAPIUser(header string) (*User, error) {
	uID := db.GetUserID(header)
	if uID == -1 {
		return nil, fmt.Errorf(ErrUserNotFound.Error())
	}
	return db.GetUserByID(uID)
}

// GetUserID takes a hexadecimal accessToken, parses it into its binary
// representation, and gets any user ID associated with the token. If no user
// is associated, -1 is returned.
func (db *datastore) GetUserID(accessToken string) int64 {
	i, _ := db.GetUserIDPrivilege(accessToken)
	return i
}

func (db *datastore) GetUserIDPrivilege(accessToken string) (userID int64, sudo bool) {
	t := auth.GetToken(accessToken)
	if len(t) == 0 {
		return -1, false
	}

	var oneTime bool
	err := db.QueryRow("SELECT user_id, sudo, one_time FROM accesstokens WHERE token LIKE ? AND (expires IS NULL OR expires > "+db.now()+")", t).Scan(&userID, &sudo, &oneTime)
	switch {
	case err == sql.ErrNoRows:
		return -1, false
	case err != nil:
		return -1, false
	}

	// Delete token if it was one-time
	if oneTime {
		db.DeleteToken(t[:])
	}

	return
}

func (db *datastore) DeleteToken(accessToken []byte) error {
	res, err := db.Exec("DELETE FROM accesstokens WHERE token LIKE ?", accessToken)
	if err != nil {
		return err
	}
	rowsAffected, _ := res.RowsAffected()
	if rowsAffected == 0 {
		return impart.HTTPError{http.StatusNotFound, "Token is invalid or doesn't exist"}
	}
	return nil
}

// FetchLastAccessToken creates a new non-expiring, valid access token for the given
// userID.
func (db *datastore) FetchLastAccessToken(userID int64) string {
	var t []byte
	err := db.QueryRow("SELECT token FROM accesstokens WHERE user_id = ? AND (expires IS NULL OR expires > "+db.now()+") ORDER BY created DESC LIMIT 1", userID).Scan(&t)
	switch {
	case err == sql.ErrNoRows:
		return ""
	case err != nil:
		log.Error("Failed selecting from accesstoken: %v", err)
		return ""
	}

	u, err := uuid.Parse(t)
	if err != nil {
		return ""
	}
	return u.String()
}

// GetAccessToken creates a new non-expiring, valid access token for the given
// userID.
func (db *datastore) GetAccessToken(userID int64) (string, error) {
	return db.GetTemporaryOneTimeAccessToken(userID, 0, false)
}

// GetTemporaryAccessToken creates a new valid access token for the given
// userID that remains valid for the given time in seconds. If validSecs is 0,
// the access token doesn't automatically expire.
func (db *datastore) GetTemporaryAccessToken(userID int64, validSecs int) (string, error) {
	return db.GetTemporaryOneTimeAccessToken(userID, validSecs, false)
}

// GetTemporaryOneTimeAccessToken creates a new valid access token for the given
// userID that remains valid for the given time in seconds and can only be used
// once if oneTime is true. If validSecs is 0, the access token doesn't
// automatically expire.
func (db *datastore) GetTemporaryOneTimeAccessToken(userID int64, validSecs int, oneTime bool) (string, error) {
	u, err := uuid.NewV4()
	if err != nil {
		log.Error("Unable to generate token: %v", err)
		return "", err
	}

	// Insert UUID to `accesstokens`
	binTok := u[:]

	expirationVal := "NULL"
	if validSecs > 0 {
		expirationVal = fmt.Sprintf("DATE_ADD("+db.now()+", INTERVAL %d SECOND)", validSecs)
	}

	_, err = db.Exec("INSERT INTO accesstokens (token, user_id, one_time, expires) VALUES (?, ?, ?, "+expirationVal+")", string(binTok), userID, oneTime)
	if err != nil {
		log.Error("Couldn't INSERT accesstoken: %v", err)
		return "", err
	}

	return u.String(), nil
}

func (db *datastore) CreateOwnedPost(post *SubmittedPost, accessToken, collAlias, hostName string) (*PublicPost, error) {
	var userID, collID int64 = -1, -1
	var coll *Collection
	var err error
	if accessToken != "" {
		userID = db.GetUserID(accessToken)
		if userID == -1 {
			return nil, ErrBadAccessToken
		}
		if collAlias != "" {
			coll, err = db.GetCollection(collAlias)
			if err != nil {
				return nil, err
			}
			coll.hostName = hostName
			if coll.OwnerID != userID {
				return nil, ErrForbiddenCollection
			}
			collID = coll.ID
		}
	}

	rp := &PublicPost{}
	rp.Post, err = db.CreatePost(userID, collID, post)
	if err != nil {
		return rp, err
	}
	if coll != nil {
		coll.ForPublic()
		rp.Collection = &CollectionObj{Collection: *coll}
	}
	return rp, nil
}

func (db *datastore) CreatePost(userID, collID int64, post *SubmittedPost) (*Post, error) {
	idLen := postIDLen
	friendlyID := id.GenerateFriendlyRandomString(idLen)

	// Handle appearance / font face
	appearance := post.Font
	if !post.isFontValid() {
		appearance = "norm"
	}

	var err error
	ownerID := sql.NullInt64{
		Valid: false,
	}
	ownerCollID := sql.NullInt64{
		Valid: false,
	}
	slug := sql.NullString{"", false}

	// If an alias was supplied, we'll add this to the collection as well.
	if userID > 0 {
		ownerID.Int64 = userID
		ownerID.Valid = true
		if collID > 0 {
			ownerCollID.Int64 = collID
			ownerCollID.Valid = true
			var slugVal string
			if post.Slug != nil && *post.Slug != "" {
				slugVal = *post.Slug
			} else {
				if post.Title != nil && *post.Title != "" {
					slugVal = getSlug(*post.Title, post.Language.String)
					if slugVal == "" {
						slugVal = getSlug(*post.Content, post.Language.String)
					}
				} else {
					slugVal = getSlug(*post.Content, post.Language.String)
				}
			}
			if slugVal == "" {
				slugVal = friendlyID
			}
			slug = sql.NullString{slugVal, true}
		}
	}

	created := time.Now()
	if db.driverName == driverSQLite {
		// SQLite stores datetimes in UTC, so convert time.Now() to it here
		created = created.UTC()
	}
	if post.Created != nil && *post.Created != "" {
		created, err = time.Parse("2006-01-02T15:04:05Z", *post.Created)
		if err != nil {
			log.Error("Unable to parse Created time '%s': %v", *post.Created, err)
			created = time.Now()
			if db.driverName == driverSQLite {
				// SQLite stores datetimes in UTC, so convert time.Now() to it here
				created = created.UTC()
			}
		}
	}

	stmt, err := db.Prepare("INSERT INTO posts (id, slug, title, content, text_appearance, language, rtl, privacy, owner_id, collection_id, created, updated, view_count) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " + db.now() + ", ?)")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()
	_, err = stmt.Exec(friendlyID, slug, post.Title, post.Content, appearance, post.Language, post.IsRTL, 0, ownerID, ownerCollID, created, 0)
	if err != nil {
		if db.isDuplicateKeyErr(err) {
			// Duplicate entry error; try a new slug
			// TODO: make this a little more robust
			slug = sql.NullString{id.GenSafeUniqueSlug(slug.String), true}
			_, err = stmt.Exec(friendlyID, slug, post.Title, post.Content, appearance, post.Language, post.IsRTL, 0, ownerID, ownerCollID, created, 0)
			if err != nil {
				return nil, handleFailedPostInsert(fmt.Errorf("Retried slug generation, still failed: %v", err))
			}
		} else {
			return nil, handleFailedPostInsert(err)
		}
	}

	// TODO: return Created field in proper format
	return &Post{
		ID:           friendlyID,
		Slug:         null.NewString(slug.String, slug.Valid),
		Font:         appearance,
		Language:     zero.NewString(post.Language.String, post.Language.Valid),
		RTL:          zero.NewBool(post.IsRTL.Bool, post.IsRTL.Valid),
		OwnerID:      null.NewInt(userID, true),
		CollectionID: null.NewInt(userID, true),
		Created:      created.Truncate(time.Second).UTC(),
		Updated:      time.Now().Truncate(time.Second).UTC(),
		Title:        zero.NewString(*(post.Title), true),
		Content:      *(post.Content),
	}, nil
}

// UpdateOwnedPost updates an existing post with only the given fields in the
// supplied AuthenticatedPost.
func (db *datastore) UpdateOwnedPost(post *AuthenticatedPost, userID int64) error {
	params := []interface{}{}
	var queryUpdates, sep, authCondition string
	if post.Slug != nil && *post.Slug != "" {
		queryUpdates += sep + "slug = ?"
		sep = ", "
		params = append(params, getSlug(*post.Slug, ""))
	}
	if post.Content != nil {
		queryUpdates += sep + "content = ?"
		sep = ", "
		params = append(params, post.Content)
	}
	if post.Title != nil {
		queryUpdates += sep + "title = ?"
		sep = ", "
		params = append(params, post.Title)
	}
	if post.Language.Valid {
		queryUpdates += sep + "language = ?"
		sep = ", "
		params = append(params, post.Language.String)
	}
	if post.IsRTL.Valid {
		queryUpdates += sep + "rtl = ?"
		sep = ", "
		params = append(params, post.IsRTL.Bool)
	}
	if post.Font != "" {
		queryUpdates += sep + "text_appearance = ?"
		sep = ", "
		params = append(params, post.Font)
	}
	if post.Created != nil {
		createTime, err := time.Parse(postMetaDateFormat, *post.Created)
		if err != nil {
			log.Error("Unable to parse Created date: %v", err)
			return fmt.Errorf("That's the incorrect format for Created date.")
		}
		queryUpdates += sep + "created = ?"
		sep = ", "
		params = append(params, createTime)
	}

	// WHERE parameters...
	// id = ?
	params = append(params, post.ID)
	// AND owner_id = ?
	authCondition = "(owner_id = ?)"
	params = append(params, userID)

	if queryUpdates == "" {
		return ErrPostNoUpdatableVals
	}

	queryUpdates += sep + "updated = " + db.now()

	res, err := db.Exec("UPDATE posts SET "+queryUpdates+" WHERE id = ? AND "+authCondition, params...)
	if err != nil {
		log.Error("Unable to update owned post: %v", err)
		return err
	}

	rowsAffected, _ := res.RowsAffected()
	if rowsAffected == 0 {
		// Show the correct error message if nothing was updated
		var dummy int
		err := db.QueryRow("SELECT 1 FROM posts WHERE id = ? AND "+authCondition, post.ID, params[len(params)-1]).Scan(&dummy)
		switch {
		case err == sql.ErrNoRows:
			return ErrUnauthorizedEditPost
		case err != nil:
			log.Error("Failed selecting from posts: %v", err)
		}
		return nil
	}

	return nil
}

func (db *datastore) GetCollectionBy(condition string, value interface{}) (*Collection, error) {
	c := &Collection{}

	// FIXME: change Collection to reflect database values. Add helper functions to get actual values
	var styleSheet, script, signature, format zero.String
	row := db.QueryRow("SELECT id, alias, title, description, style_sheet, script, post_signature, format, owner_id, privacy, view_count FROM collections WHERE "+condition, value)

	err := row.Scan(&c.ID, &c.Alias, &c.Title, &c.Description, &styleSheet, &script, &signature, &format, &c.OwnerID, &c.Visibility, &c.Views)
	switch {
	case err == sql.ErrNoRows:
		return nil, impart.HTTPError{http.StatusNotFound, "Collection doesn't exist."}
	case db.isHighLoadError(err):
		return nil, ErrUnavailable
	case err != nil:
		log.Error("Failed selecting from collections: %v", err)
		return nil, err
	}
	c.StyleSheet = styleSheet.String
	c.Script = script.String
	c.Signature = signature.String
	c.Format = format.String
	c.Public = c.IsPublic()
	c.Monetization = db.GetCollectionAttribute(c.ID, "monetization_pointer")
	c.Verification = db.GetCollectionAttribute(c.ID, "verification_link")

	c.db = db

	return c, nil
}

func (db *datastore) GetCollection(alias string) (*Collection, error) {
	return db.GetCollectionBy("alias = ?", alias)
}

func (db *datastore) GetCollectionForPad(alias string) (*Collection, error) {
	c := &Collection{Alias: alias}

	row := db.QueryRow("SELECT id, alias, title, description, privacy FROM collections WHERE alias = ?", alias)

	err := row.Scan(&c.ID, &c.Alias, &c.Title, &c.Description, &c.Visibility)
	switch {
	case err == sql.ErrNoRows:
		return c, impart.HTTPError{http.StatusNotFound, "Collection doesn't exist."}
	case err != nil:
		log.Error("Failed selecting from collections: %v", err)
		return c, ErrInternalGeneral
	}
	c.Public = c.IsPublic()

	return c, nil
}

func (db *datastore) GetCollectionByID(id int64) (*Collection, error) {
	return db.GetCollectionBy("id = ?", id)
}

func (db *datastore) GetCollectionFromDomain(host string) (*Collection, error) {
	return db.GetCollectionBy("host = ?", host)
}

func (db *datastore) UpdateCollection(app *App, c *SubmittedCollection, alias string) error {
	q := query.NewUpdate().
		SetStringPtr(c.Title, "title").
		SetStringPtr(c.Description, "description").
		SetNullString(c.StyleSheet, "style_sheet").
		SetNullString(c.Script, "script").
		SetNullString(c.Signature, "post_signature")

	if c.Format != nil {
		cf := &CollectionFormat{Format: c.Format.String}
		if cf.Valid() {
			q.SetNullString(c.Format, "format")
		}
	}

	var updatePass bool
	if c.Visibility != nil && (collVisibility(*c.Visibility)&CollProtected == 0 || c.Pass != "") {
		q.SetIntPtr(c.Visibility, "privacy")
		if c.Pass != "" {
			updatePass = true