You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

2814 lines
86 KiB

  1. /*
  2. * Copyright © 2018-2021 A Bunch Tell LLC.
  3. *
  4. * This file is part of WriteFreely.
  5. *
  6. * WriteFreely is free software: you can redistribute it and/or modify
  7. * it under the terms of the GNU Affero General Public License, included
  8. * in the LICENSE file in this source code package.
  9. */
  10. package writefreely
  11. import (
  12. "context"
  13. "database/sql"
  14. "fmt"
  15. "github.com/writeas/web-core/silobridge"
  16. wf_db "github.com/writefreely/writefreely/db"
  17. "net/http"
  18. "strings"
  19. "time"
  20. "github.com/guregu/null"
  21. "github.com/guregu/null/zero"
  22. uuid "github.com/nu7hatch/gouuid"
  23. "github.com/writeas/activityserve"
  24. "github.com/writeas/impart"
  25. "github.com/writeas/web-core/activitypub"
  26. "github.com/writeas/web-core/auth"
  27. "github.com/writeas/web-core/data"
  28. "github.com/writeas/web-core/id"
  29. "github.com/writeas/web-core/log"
  30. "github.com/writeas/web-core/query"
  31. "github.com/writefreely/writefreely/author"
  32. "github.com/writefreely/writefreely/config"
  33. "github.com/writefreely/writefreely/key"
  34. )
  35. const (
  36. mySQLErrDuplicateKey = 1062
  37. mySQLErrCollationMix = 1267
  38. mySQLErrTooManyConns = 1040
  39. mySQLErrMaxUserConns = 1203
  40. driverMySQL = "mysql"
  41. driverSQLite = "sqlite3"
  42. )
  43. var (
  44. SQLiteEnabled bool
  45. )
  46. type writestore interface {
  47. CreateUser(*config.Config, *User, string, string) error
  48. UpdateUserEmail(keys *key.Keychain, userID int64, email string) error
  49. UpdateEncryptedUserEmail(int64, []byte) error
  50. GetUserByID(int64) (*User, error)
  51. GetUserForAuth(string) (*User, error)
  52. GetUserForAuthByID(int64) (*User, error)
  53. GetUserNameFromToken(string) (string, error)
  54. GetUserDataFromToken(string) (int64, string, error)
  55. GetAPIUser(header string) (*User, error)
  56. GetUserID(accessToken string) int64
  57. GetUserIDPrivilege(accessToken string) (userID int64, sudo bool)
  58. DeleteToken(accessToken []byte) error
  59. FetchLastAccessToken(userID int64) string
  60. GetAccessToken(userID int64) (string, error)
  61. GetTemporaryAccessToken(userID int64, validSecs int) (string, error)
  62. GetTemporaryOneTimeAccessToken(userID int64, validSecs int, oneTime bool) (string, error)
  63. DeleteAccount(userID int64) error
  64. ChangeSettings(app *App, u *User, s *userSettings) error
  65. ChangePassphrase(userID int64, sudo bool, curPass string, hashedPass []byte) error
  66. GetCollections(u *User, hostName string) (*[]Collection, error)
  67. GetPublishableCollections(u *User, hostName string) (*[]Collection, error)
  68. GetMeStats(u *User) userMeStats
  69. GetTotalCollections() (int64, error)
  70. GetTotalPosts() (int64, error)
  71. GetTopPosts(u *User, alias string) (*[]PublicPost, error)
  72. GetAnonymousPosts(u *User, page int) (*[]PublicPost, error)
  73. GetUserPosts(u *User) (*[]PublicPost, error)
  74. CreateOwnedPost(post *SubmittedPost, accessToken, collAlias, hostName string) (*PublicPost, error)
  75. CreatePost(userID, collID int64, post *SubmittedPost) (*Post, error)
  76. UpdateOwnedPost(post *AuthenticatedPost, userID int64) error
  77. GetEditablePost(id, editToken string) (*PublicPost, error)
  78. PostIDExists(id string) bool
  79. GetPost(id string, collectionID int64) (*PublicPost, error)
  80. GetOwnedPost(id string, ownerID int64) (*PublicPost, error)
  81. GetPostProperty(id string, collectionID int64, property string) (interface{}, error)
  82. CreateCollectionFromToken(*config.Config, string, string, string) (*Collection, error)
  83. CreateCollection(*config.Config, string, string, int64) (*Collection, error)
  84. GetCollectionBy(condition string, value interface{}) (*Collection, error)
  85. GetCollection(alias string) (*Collection, error)
  86. GetCollectionForPad(alias string) (*Collection, error)
  87. GetCollectionByID(id int64) (*Collection, error)
  88. UpdateCollection(c *SubmittedCollection, alias string) error
  89. DeleteCollection(alias string, userID int64) error
  90. UpdatePostPinState(pinned bool, postID string, collID, ownerID, pos int64) error
  91. GetLastPinnedPostPos(collID int64) int64
  92. GetPinnedPosts(coll *CollectionObj, includeFuture bool) (*[]PublicPost, error)
  93. RemoveCollectionRedirect(t *sql.Tx, alias string) error
  94. GetCollectionRedirect(alias string) (new string)
  95. IsCollectionAttributeOn(id int64, attr string) bool
  96. CollectionHasAttribute(id int64, attr string) bool
  97. CanCollect(cpr *ClaimPostRequest, userID int64) bool
  98. AttemptClaim(p *ClaimPostRequest, query string, params []interface{}, slugIdx int) (sql.Result, error)
  99. DispersePosts(userID int64, postIDs []string) (*[]ClaimPostResult, error)
  100. ClaimPosts(cfg *config.Config, userID int64, collAlias string, posts *[]ClaimPostRequest) (*[]ClaimPostResult, error)
  101. GetPostsCount(c *CollectionObj, includeFuture bool)
  102. GetPosts(cfg *config.Config, c *Collection, page int, includeFuture, forceRecentFirst, includePinned bool) (*[]PublicPost, error)
  103. GetPostsTagged(cfg *config.Config, c *Collection, tag string, page int, includeFuture bool) (*[]PublicPost, error)
  104. GetAPFollowers(c *Collection) (*[]RemoteUser, error)
  105. GetAPActorKeys(collectionID int64) ([]byte, []byte)
  106. CreateUserInvite(id string, userID int64, maxUses int, expires *time.Time) error
  107. GetUserInvites(userID int64) (*[]Invite, error)
  108. GetUserInvite(id string) (*Invite, error)
  109. GetUsersInvitedCount(id string) int64
  110. CreateInvitedUser(inviteID string, userID int64) error
  111. GetDynamicContent(id string) (*instanceContent, error)
  112. UpdateDynamicContent(id, title, content, contentType string) error
  113. GetAllUsers(page uint) (*[]User, error)
  114. GetAllUsersCount() int64
  115. GetUserLastPostTime(id int64) (*time.Time, error)
  116. GetCollectionLastPostTime(id int64) (*time.Time, error)
  117. GetIDForRemoteUser(context.Context, string, string, string) (int64, error)
  118. RecordRemoteUserID(context.Context, int64, string, string, string, string) error
  119. ValidateOAuthState(context.Context, string) (string, string, int64, string, error)
  120. GenerateOAuthState(context.Context, string, string, int64, string) (string, error)
  121. GetOauthAccounts(ctx context.Context, userID int64) ([]oauthAccountInfo, error)
  122. RemoveOauth(ctx context.Context, userID int64, provider string, clientID string, remoteUserID string) error
  123. DatabaseInitialized() bool
  124. }
  125. type datastore struct {
  126. *sql.DB
  127. driverName string
  128. }
  129. var _ writestore = &datastore{}
  130. func (db *datastore) now() string {
  131. if db.driverName == driverSQLite {
  132. return "strftime('%Y-%m-%d %H:%M:%S','now')"
  133. }
  134. return "NOW()"
  135. }
  136. func (db *datastore) clip(field string, l int) string {
  137. if db.driverName == driverSQLite {
  138. return fmt.Sprintf("SUBSTR(%s, 0, %d)", field, l)
  139. }
  140. return fmt.Sprintf("LEFT(%s, %d)", field, l)
  141. }
  142. func (db *datastore) upsert(indexedCols ...string) string {
  143. if db.driverName == driverSQLite {
  144. // NOTE: SQLite UPSERT syntax only works in v3.24.0 (2018-06-04) or later
  145. // Leaving this for whenever we can upgrade and include it in our binary
  146. cc := strings.Join(indexedCols, ", ")
  147. return "ON CONFLICT(" + cc + ") DO UPDATE SET"
  148. }
  149. return "ON DUPLICATE KEY UPDATE"
  150. }
  151. func (db *datastore) dateSub(l int, unit string) string {
  152. if db.driverName == driverSQLite {
  153. return fmt.Sprintf("DATETIME('now', '-%d %s')", l, unit)
  154. }
  155. return fmt.Sprintf("DATE_SUB(NOW(), INTERVAL %d %s)", l, unit)
  156. }
  157. // CreateUser creates a new user in the database from the given User, UPDATING it in the process with the user's ID.
  158. func (db *datastore) CreateUser(cfg *config.Config, u *User, collectionTitle string, collectionDesc string) error {
  159. if db.PostIDExists(u.Username) {
  160. return impart.HTTPError{http.StatusConflict, "Invalid collection name."}
  161. }
  162. // New users get a `users` and `collections` row.
  163. t, err := db.Begin()
  164. if err != nil {
  165. return err
  166. }
  167. // 1. Add to `users` table
  168. // NOTE: Assumes User's Password is already hashed!
  169. res, err := t.Exec("INSERT INTO users (username, password, email) VALUES (?, ?, ?)", u.Username, u.HashedPass, u.Email)
  170. if err != nil {
  171. t.Rollback()
  172. if db.isDuplicateKeyErr(err) {
  173. return impart.HTTPError{http.StatusConflict, "Username is already taken."}
  174. }
  175. log.Error("Rolling back users INSERT: %v\n", err)
  176. return err
  177. }
  178. u.ID, err = res.LastInsertId()
  179. if err != nil {
  180. t.Rollback()
  181. log.Error("Rolling back after LastInsertId: %v\n", err)
  182. return err
  183. }
  184. // 2. Create user's Collection
  185. if collectionTitle == "" {
  186. collectionTitle = u.Username
  187. }
  188. 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)
  189. if err != nil {
  190. t.Rollback()
  191. if db.isDuplicateKeyErr(err) {
  192. return impart.HTTPError{http.StatusConflict, "Username is already taken."}
  193. }
  194. log.Error("Rolling back collections INSERT: %v\n", err)
  195. return err
  196. }
  197. db.RemoveCollectionRedirect(t, u.Username)
  198. err = t.Commit()
  199. if err != nil {
  200. t.Rollback()
  201. log.Error("Rolling back after Commit(): %v\n", err)
  202. return err
  203. }
  204. return nil
  205. }
  206. // FIXME: We're returning errors inconsistently in this file. Do we use Errorf
  207. // for returned value, or impart?
  208. func (db *datastore) UpdateUserEmail(keys *key.Keychain, userID int64, email string) error {
  209. encEmail, err := data.Encrypt(keys.EmailKey, email)
  210. if err != nil {
  211. return fmt.Errorf("Couldn't encrypt email %s: %s\n", email, err)
  212. }
  213. return db.UpdateEncryptedUserEmail(userID, encEmail)
  214. }
  215. func (db *datastore) UpdateEncryptedUserEmail(userID int64, encEmail []byte) error {
  216. _, err := db.Exec("UPDATE users SET email = ? WHERE id = ?", encEmail, userID)
  217. if err != nil {
  218. return fmt.Errorf("Unable to update user email: %s", err)
  219. }
  220. return nil
  221. }
  222. func (db *datastore) CreateCollectionFromToken(cfg *config.Config, alias, title, accessToken string) (*Collection, error) {
  223. userID := db.GetUserID(accessToken)
  224. if userID == -1 {
  225. return nil, ErrBadAccessToken
  226. }
  227. return db.CreateCollection(cfg, alias, title, userID)
  228. }
  229. func (db *datastore) GetUserCollectionCount(userID int64) (uint64, error) {
  230. var collCount uint64
  231. err := db.QueryRow("SELECT COUNT(*) FROM collections WHERE owner_id = ?", userID).Scan(&collCount)
  232. switch {
  233. case err == sql.ErrNoRows:
  234. return 0, impart.HTTPError{http.StatusInternalServerError, "Couldn't retrieve user from database."}
  235. case err != nil:
  236. log.Error("Couldn't get collections count for user %d: %v", userID, err)
  237. return 0, err
  238. }
  239. return collCount, nil
  240. }
  241. func (db *datastore) CreateCollection(cfg *config.Config, alias, title string, userID int64) (*Collection, error) {
  242. if db.PostIDExists(alias) {
  243. return nil, impart.HTTPError{http.StatusConflict, "Invalid collection name."}
  244. }
  245. // All good, so create new collection
  246. res, err := db.Exec("INSERT INTO collections (alias, title, description, privacy, owner_id, view_count) VALUES (?, ?, ?, ?, ?, ?)", alias, title, "", defaultVisibility(cfg), userID, 0)
  247. if err != nil {
  248. if db.isDuplicateKeyErr(err) {
  249. return nil, impart.HTTPError{http.StatusConflict, "Collection already exists."}
  250. }
  251. log.Error("Couldn't add to collections: %v\n", err)
  252. return nil, err
  253. }
  254. c := &Collection{
  255. Alias: alias,
  256. Title: title,
  257. OwnerID: userID,
  258. PublicOwner: false,
  259. Public: defaultVisibility(cfg) == CollPublic,
  260. }
  261. c.ID, err = res.LastInsertId()
  262. if err != nil {
  263. log.Error("Couldn't get collection LastInsertId: %v\n", err)
  264. }
  265. return c, nil
  266. }
  267. func (db *datastore) GetUserByID(id int64) (*User, error) {
  268. u := &User{ID: id}
  269. 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)
  270. switch {
  271. case err == sql.ErrNoRows:
  272. return nil, ErrUserNotFound
  273. case err != nil:
  274. log.Error("Couldn't SELECT user password: %v", err)
  275. return nil, err
  276. }
  277. return u, nil
  278. }
  279. // IsUserSilenced returns true if the user account associated with id is
  280. // currently silenced.
  281. func (db *datastore) IsUserSilenced(id int64) (bool, error) {
  282. u := &User{ID: id}
  283. err := db.QueryRow("SELECT status FROM users WHERE id = ?", id).Scan(&u.Status)
  284. switch {
  285. case err == sql.ErrNoRows:
  286. return false, fmt.Errorf("is user silenced: %v", ErrUserNotFound)
  287. case err != nil:
  288. log.Error("Couldn't SELECT user status: %v", err)
  289. return false, fmt.Errorf("is user silenced: %v", err)
  290. }
  291. return u.IsSilenced(), nil
  292. }
  293. // DoesUserNeedAuth returns true if the user hasn't provided any methods for
  294. // authenticating with the account, such a passphrase or email address.
  295. // Any errors are reported to admin and silently quashed, returning false as the
  296. // result.
  297. func (db *datastore) DoesUserNeedAuth(id int64) bool {
  298. var pass, email []byte
  299. // Find out if user has an email set first
  300. err := db.QueryRow("SELECT password, email FROM users WHERE id = ?", id).Scan(&pass, &email)
  301. switch {
  302. case err == sql.ErrNoRows:
  303. // ERROR. Don't give false positives on needing auth methods
  304. return false
  305. case err != nil:
  306. // ERROR. Don't give false positives on needing auth methods
  307. log.Error("Couldn't SELECT user %d from users: %v", id, err)
  308. return false
  309. }
  310. // User doesn't need auth if there's an email
  311. return len(email) == 0 && len(pass) == 0
  312. }
  313. func (db *datastore) IsUserPassSet(id int64) (bool, error) {
  314. var pass []byte
  315. err := db.QueryRow("SELECT password FROM users WHERE id = ?", id).Scan(&pass)
  316. switch {
  317. case err == sql.ErrNoRows:
  318. return false, nil
  319. case err != nil:
  320. log.Error("Couldn't SELECT user %d from users: %v", id, err)
  321. return false, err
  322. }
  323. return len(pass) > 0, nil
  324. }
  325. func (db *datastore) GetUserForAuth(username string) (*User, error) {
  326. u := &User{Username: username}
  327. 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)
  328. switch {
  329. case err == sql.ErrNoRows:
  330. // Check if they've entered the wrong, unnormalized username
  331. username = getSlug(username, "")
  332. if username != u.Username {
  333. err = db.QueryRow("SELECT id FROM users WHERE username = ? LIMIT 1", username).Scan(&u.ID)
  334. if err == nil {
  335. return db.GetUserForAuth(username)
  336. }
  337. }
  338. return nil, ErrUserNotFound
  339. case err != nil:
  340. log.Error("Couldn't SELECT user password: %v", err)
  341. return nil, err
  342. }
  343. return u, nil
  344. }
  345. func (db *datastore) GetUserForAuthByID(userID int64) (*User, error) {
  346. u := &User{ID: userID}
  347. 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)
  348. switch {
  349. case err == sql.ErrNoRows:
  350. return nil, ErrUserNotFound
  351. case err != nil:
  352. log.Error("Couldn't SELECT userForAuthByID: %v", err)
  353. return nil, err
  354. }
  355. return u, nil
  356. }
  357. func (db *datastore) GetUserNameFromToken(accessToken string) (string, error) {
  358. t := auth.GetToken(accessToken)
  359. if len(t) == 0 {
  360. return "", ErrNoAccessToken
  361. }
  362. var oneTime bool
  363. var username string
  364. 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)
  365. switch {
  366. case err == sql.ErrNoRows:
  367. return "", ErrBadAccessToken
  368. case err != nil:
  369. return "", ErrInternalGeneral
  370. }
  371. // Delete token if it was one-time
  372. if oneTime {
  373. db.DeleteToken(t[:])
  374. }
  375. return username, nil
  376. }
  377. func (db *datastore) GetUserDataFromToken(accessToken string) (int64, string, error) {
  378. t := auth.GetToken(accessToken)
  379. if len(t) == 0 {
  380. return 0, "", ErrNoAccessToken
  381. }
  382. var userID int64
  383. var oneTime bool
  384. var username string
  385. 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)
  386. switch {
  387. case err == sql.ErrNoRows:
  388. return 0, "", ErrBadAccessToken
  389. case err != nil:
  390. return 0, "", ErrInternalGeneral
  391. }
  392. // Delete token if it was one-time
  393. if oneTime {
  394. db.DeleteToken(t[:])
  395. }
  396. return userID, username, nil
  397. }
  398. func (db *datastore) GetAPIUser(header string) (*User, error) {
  399. uID := db.GetUserID(header)
  400. if uID == -1 {
  401. return nil, fmt.Errorf(ErrUserNotFound.Error())
  402. }
  403. return db.GetUserByID(uID)
  404. }
  405. // GetUserID takes a hexadecimal accessToken, parses it into its binary
  406. // representation, and gets any user ID associated with the token. If no user
  407. // is associated, -1 is returned.
  408. func (db *datastore) GetUserID(accessToken string) int64 {
  409. i, _ := db.GetUserIDPrivilege(accessToken)
  410. return i
  411. }
  412. func (db *datastore) GetUserIDPrivilege(accessToken string) (userID int64, sudo bool) {
  413. t := auth.GetToken(accessToken)
  414. if len(t) == 0 {
  415. return -1, false
  416. }
  417. var oneTime bool
  418. 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)
  419. switch {
  420. case err == sql.ErrNoRows:
  421. return -1, false
  422. case err != nil:
  423. return -1, false
  424. }
  425. // Delete token if it was one-time
  426. if oneTime {
  427. db.DeleteToken(t[:])
  428. }
  429. return
  430. }
  431. func (db *datastore) DeleteToken(accessToken []byte) error {
  432. res, err := db.Exec("DELETE FROM accesstokens WHERE token LIKE ?", accessToken)
  433. if err != nil {
  434. return err
  435. }
  436. rowsAffected, _ := res.RowsAffected()
  437. if rowsAffected == 0 {
  438. return impart.HTTPError{http.StatusNotFound, "Token is invalid or doesn't exist"}
  439. }
  440. return nil
  441. }
  442. // FetchLastAccessToken creates a new non-expiring, valid access token for the given
  443. // userID.
  444. func (db *datastore) FetchLastAccessToken(userID int64) string {
  445. var t []byte
  446. 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)
  447. switch {
  448. case err == sql.ErrNoRows:
  449. return ""
  450. case err != nil:
  451. log.Error("Failed selecting from accesstoken: %v", err)
  452. return ""
  453. }
  454. u, err := uuid.Parse(t)
  455. if err != nil {
  456. return ""
  457. }
  458. return u.String()
  459. }
  460. // GetAccessToken creates a new non-expiring, valid access token for the given
  461. // userID.
  462. func (db *datastore) GetAccessToken(userID int64) (string, error) {
  463. return db.GetTemporaryOneTimeAccessToken(userID, 0, false)
  464. }
  465. // GetTemporaryAccessToken creates a new valid access token for the given
  466. // userID that remains valid for the given time in seconds. If validSecs is 0,
  467. // the access token doesn't automatically expire.
  468. func (db *datastore) GetTemporaryAccessToken(userID int64, validSecs int) (string, error) {
  469. return db.GetTemporaryOneTimeAccessToken(userID, validSecs, false)
  470. }
  471. // GetTemporaryOneTimeAccessToken creates a new valid access token for the given
  472. // userID that remains valid for the given time in seconds and can only be used
  473. // once if oneTime is true. If validSecs is 0, the access token doesn't
  474. // automatically expire.
  475. func (db *datastore) GetTemporaryOneTimeAccessToken(userID int64, validSecs int, oneTime bool) (string, error) {
  476. u, err := uuid.NewV4()
  477. if err != nil {
  478. log.Error("Unable to generate token: %v", err)
  479. return "", err
  480. }
  481. // Insert UUID to `accesstokens`
  482. binTok := u[:]
  483. expirationVal := "NULL"
  484. if validSecs > 0 {
  485. expirationVal = fmt.Sprintf("DATE_ADD("+db.now()+", INTERVAL %d SECOND)", validSecs)
  486. }
  487. _, err = db.Exec("INSERT INTO accesstokens (token, user_id, one_time, expires) VALUES (?, ?, ?, "+expirationVal+")", string(binTok), userID, oneTime)
  488. if err != nil {
  489. log.Error("Couldn't INSERT accesstoken: %v", err)
  490. return "", err
  491. }
  492. return u.String(), nil
  493. }
  494. func (db *datastore) CreateOwnedPost(post *SubmittedPost, accessToken, collAlias, hostName string) (*PublicPost, error) {
  495. var userID, collID int64 = -1, -1
  496. var coll *Collection
  497. var err error
  498. if accessToken != "" {
  499. userID = db.GetUserID(accessToken)
  500. if userID == -1 {
  501. return nil, ErrBadAccessToken
  502. }
  503. if collAlias != "" {
  504. coll, err = db.GetCollection(collAlias)
  505. if err != nil {
  506. return nil, err
  507. }
  508. coll.hostName = hostName
  509. if coll.OwnerID != userID {
  510. return nil, ErrForbiddenCollection
  511. }
  512. collID = coll.ID
  513. }
  514. }
  515. rp := &PublicPost{}
  516. rp.Post, err = db.CreatePost(userID, collID, post)
  517. if err != nil {
  518. return rp, err
  519. }
  520. if coll != nil {
  521. coll.ForPublic()
  522. rp.Collection = &CollectionObj{Collection: *coll}
  523. }
  524. return rp, nil
  525. }
  526. func (db *datastore) CreatePost(userID, collID int64, post *SubmittedPost) (*Post, error) {
  527. idLen := postIDLen
  528. friendlyID := id.GenerateFriendlyRandomString(idLen)
  529. // Handle appearance / font face
  530. appearance := post.Font
  531. if !post.isFontValid() {
  532. appearance = "norm"
  533. }
  534. var err error
  535. ownerID := sql.NullInt64{
  536. Valid: false,
  537. }
  538. ownerCollID := sql.NullInt64{
  539. Valid: false,
  540. }
  541. slug := sql.NullString{"", false}
  542. // If an alias was supplied, we'll add this to the collection as well.
  543. if userID > 0 {
  544. ownerID.Int64 = userID
  545. ownerID.Valid = true
  546. if collID > 0 {
  547. ownerCollID.Int64 = collID
  548. ownerCollID.Valid = true
  549. var slugVal string
  550. if post.Slug != nil && *post.Slug != "" {
  551. slugVal = *post.Slug
  552. } else {
  553. if post.Title != nil && *post.Title != "" {
  554. slugVal = getSlug(*post.Title, post.Language.String)
  555. if slugVal == "" {
  556. slugVal = getSlug(*post.Content, post.Language.String)
  557. }
  558. } else {
  559. slugVal = getSlug(*post.Content, post.Language.String)
  560. }
  561. }
  562. if slugVal == "" {
  563. slugVal = friendlyID
  564. }
  565. slug = sql.NullString{slugVal, true}
  566. }
  567. }
  568. created := time.Now()
  569. if db.driverName == driverSQLite {
  570. // SQLite stores datetimes in UTC, so convert time.Now() to it here
  571. created = created.UTC()
  572. }
  573. if post.Created != nil {
  574. created, err = time.Parse("2006-01-02T15:04:05Z", *post.Created)
  575. if err != nil {
  576. log.Error("Unable to parse Created time '%s': %v", *post.Created, err)
  577. created = time.Now()
  578. if db.driverName == driverSQLite {
  579. // SQLite stores datetimes in UTC, so convert time.Now() to it here
  580. created = created.UTC()
  581. }
  582. }
  583. }
  584. 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() + ", ?)")
  585. if err != nil {
  586. return nil, err
  587. }
  588. defer stmt.Close()
  589. _, err = stmt.Exec(friendlyID, slug, post.Title, post.Content, appearance, post.Language, post.IsRTL, 0, ownerID, ownerCollID, created, 0)
  590. if err != nil {
  591. if db.isDuplicateKeyErr(err) {
  592. // Duplicate entry error; try a new slug
  593. // TODO: make this a little more robust
  594. slug = sql.NullString{id.GenSafeUniqueSlug(slug.String), true}
  595. _, err = stmt.Exec(friendlyID, slug, post.Title, post.Content, appearance, post.Language, post.IsRTL, 0, ownerID, ownerCollID, created, 0)
  596. if err != nil {
  597. return nil, handleFailedPostInsert(fmt.Errorf("Retried slug generation, still failed: %v", err))
  598. }
  599. } else {
  600. return nil, handleFailedPostInsert(err)
  601. }
  602. }
  603. // TODO: return Created field in proper format
  604. return &Post{
  605. ID: friendlyID,
  606. Slug: null.NewString(slug.String, slug.Valid),
  607. Font: appearance,
  608. Language: zero.NewString(post.Language.String, post.Language.Valid),
  609. RTL: zero.NewBool(post.IsRTL.Bool, post.IsRTL.Valid),
  610. OwnerID: null.NewInt(userID, true),
  611. CollectionID: null.NewInt(userID, true),
  612. Created: created.Truncate(time.Second).UTC(),
  613. Updated: time.Now().Truncate(time.Second).UTC(),
  614. Title: zero.NewString(*(post.Title), true),
  615. Content: *(post.Content),
  616. }, nil
  617. }
  618. // UpdateOwnedPost updates an existing post with only the given fields in the
  619. // supplied AuthenticatedPost.
  620. func (db *datastore) UpdateOwnedPost(post *AuthenticatedPost, userID int64) error {
  621. params := []interface{}{}
  622. var queryUpdates, sep, authCondition string
  623. if post.Slug != nil && *post.Slug != "" {
  624. queryUpdates += sep + "slug = ?"
  625. sep = ", "
  626. params = append(params, getSlug(*post.Slug, ""))
  627. }
  628. if post.Content != nil {
  629. queryUpdates += sep + "content = ?"
  630. sep = ", "
  631. params = append(params, post.Content)
  632. }
  633. if post.Title != nil {
  634. queryUpdates += sep + "title = ?"
  635. sep = ", "
  636. params = append(params, post.Title)
  637. }
  638. if post.Language.Valid {
  639. queryUpdates += sep + "language = ?"
  640. sep = ", "
  641. params = append(params, post.Language.String)
  642. }
  643. if post.IsRTL.Valid {
  644. queryUpdates += sep + "rtl = ?"
  645. sep = ", "
  646. params = append(params, post.IsRTL.Bool)
  647. }
  648. if post.Font != "" {
  649. queryUpdates += sep + "text_appearance = ?"
  650. sep = ", "
  651. params = append(params, post.Font)
  652. }
  653. if post.Created != nil {
  654. createTime, err := time.Parse(postMetaDateFormat, *post.Created)
  655. if err != nil {
  656. log.Error("Unable to parse Created date: %v", err)
  657. return fmt.Errorf("That's the incorrect format for Created date.")
  658. }
  659. queryUpdates += sep + "created = ?"
  660. sep = ", "
  661. params = append(params, createTime)
  662. }
  663. // WHERE parameters...
  664. // id = ?
  665. params = append(params, post.ID)
  666. // AND owner_id = ?
  667. authCondition = "(owner_id = ?)"
  668. params = append(params, userID)
  669. if queryUpdates == "" {
  670. return ErrPostNoUpdatableVals
  671. }
  672. queryUpdates += sep + "updated = " + db.now()
  673. res, err := db.Exec("UPDATE posts SET "+queryUpdates+" WHERE id = ? AND "+authCondition, params...)
  674. if err != nil {
  675. log.Error("Unable to update owned post: %v", err)
  676. return err
  677. }
  678. rowsAffected, _ := res.RowsAffected()
  679. if rowsAffected == 0 {
  680. // Show the correct error message if nothing was updated
  681. var dummy int
  682. err := db.QueryRow("SELECT 1 FROM posts WHERE id = ? AND "+authCondition, post.ID, params[len(params)-1]).Scan(&dummy)
  683. switch {
  684. case err == sql.ErrNoRows:
  685. return ErrUnauthorizedEditPost
  686. case err != nil:
  687. log.Error("Failed selecting from posts: %v", err)
  688. }
  689. return nil
  690. }
  691. return nil
  692. }
  693. func (db *datastore) GetCollectionBy(condition string, value interface{}) (*Collection, error) {
  694. c := &Collection{}
  695. // FIXME: change Collection to reflect database values. Add helper functions to get actual values
  696. var styleSheet, script, signature, format zero.String
  697. row := db.QueryRow("SELECT id, alias, title, description, style_sheet, script, post_signature, format, owner_id, privacy, view_count FROM collections WHERE "+condition, value)
  698. err := row.Scan(&c.ID, &c.Alias, &c.Title, &c.Description, &styleSheet, &script, &signature, &format, &c.OwnerID, &c.Visibility, &c.Views)
  699. switch {
  700. case err == sql.ErrNoRows:
  701. return nil, impart.HTTPError{http.StatusNotFound, "Collection doesn't exist."}
  702. case db.isHighLoadError(err):
  703. return nil, ErrUnavailable
  704. case err != nil:
  705. log.Error("Failed selecting from collections: %v", err)
  706. return nil, err
  707. }
  708. c.StyleSheet = styleSheet.String
  709. c.Script = script.String
  710. c.Signature = signature.String
  711. c.Format = format.String
  712. c.Public = c.IsPublic()
  713. c.Monetization = db.GetCollectionAttribute(c.ID, "monetization_pointer")
  714. c.db = db
  715. return c, nil
  716. }
  717. func (db *datastore) GetCollection(alias string) (*Collection, error) {
  718. return db.GetCollectionBy("alias = ?", alias)
  719. }
  720. func (db *datastore) GetCollectionForPad(alias string) (*Collection, error) {
  721. c := &Collection{Alias: alias}
  722. row := db.QueryRow("SELECT id, alias, title, description, privacy FROM collections WHERE alias = ?", alias)
  723. err := row.Scan(&c.ID, &c.Alias, &c.Title, &c.Description, &c.Visibility)
  724. switch {
  725. case err == sql.ErrNoRows:
  726. return c, impart.HTTPError{http.StatusNotFound, "Collection doesn't exist."}
  727. case err != nil:
  728. log.Error("Failed selecting from collections: %v", err)
  729. return c, ErrInternalGeneral
  730. }
  731. c.Public = c.IsPublic()
  732. return c, nil
  733. }
  734. func (db *datastore) GetCollectionByID(id int64) (*Collection, error) {
  735. return db.GetCollectionBy("id = ?", id)
  736. }
  737. func (db *datastore) GetCollectionFromDomain(host string) (*Collection, error) {
  738. return db.GetCollectionBy("host = ?", host)
  739. }
  740. func (db *datastore) UpdateCollection(c *SubmittedCollection, alias string) error {
  741. q := query.NewUpdate().
  742. SetStringPtr(c.Title, "title").
  743. SetStringPtr(c.Description, "description").
  744. SetNullString(c.StyleSheet, "style_sheet").
  745. SetNullString(c.Script, "script").
  746. SetNullString(c.Signature, "post_signature")
  747. if c.Format != nil {
  748. cf := &CollectionFormat{Format: c.Format.String}
  749. if cf.Valid() {
  750. q.SetNullString(c.Format, "format")
  751. }
  752. }
  753. var updatePass bool
  754. if c.Visibility != nil && (collVisibility(*c.Visibility)&CollProtected == 0 || c.Pass != "") {
  755. q.SetIntPtr(c.Visibility, "privacy")
  756. if c.Pass != "" {
  757. updatePass = true
  758. }
  759. }
  760. // WHERE values
  761. q.Where("alias = ? AND owner_id = ?", alias, c.OwnerID)
  762. if q.Updates == "" && c.Monetization == nil {
  763. return ErrPostNoUpdatableVals
  764. }
  765. // Find any current domain
  766. var collID int64
  767. var rowsAffected int64
  768. var changed bool
  769. var res sql.Result
  770. err := db.QueryRow("SELECT id FROM collections WHERE alias = ?", alias).Scan(&collID)
  771. if err != nil {
  772. log.Error("Failed selecting from collections: %v. Some things won't work.", err)
  773. }
  774. // Update MathJax value
  775. if c.MathJax {
  776. if db.driverName == driverSQLite {
  777. _, err = db.Exec("INSERT OR REPLACE INTO collectionattributes (collection_id, attribute, value) VALUES (?, ?, ?)", collID, "render_mathjax", "1")
  778. } else {
  779. _, err = db.Exec("INSERT INTO collectionattributes (collection_id, attribute, value) VALUES (?, ?, ?) "+db.upsert("collection_id", "attribute")+" value = ?", collID, "render_mathjax", "1", "1")
  780. }
  781. if err != nil {
  782. log.Error("Unable to insert render_mathjax value: %v", err)
  783. return err
  784. }
  785. } else {
  786. _, err = db.Exec("DELETE FROM collectionattributes WHERE collection_id = ? AND attribute = ?", collID, "render_mathjax")
  787. if err != nil {
  788. log.Error("Unable to delete render_mathjax value: %v", err)
  789. return err
  790. }
  791. }
  792. // Update Monetization value
  793. if c.Monetization != nil {
  794. skipUpdate := false
  795. if *c.Monetization != "" {
  796. // Strip away any excess spaces
  797. trimmed := strings.TrimSpace(*c.Monetization)
  798. // Only update value when it starts with "$", per spec: https://paymentpointers.org
  799. if strings.HasPrefix(trimmed, "$") {
  800. c.Monetization = &trimmed
  801. } else {
  802. // Value appears invalid, so don't update
  803. skipUpdate = true
  804. }
  805. }
  806. if !skipUpdate {
  807. _, err = db.Exec("INSERT INTO collectionattributes (collection_id, attribute, value) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE value = ?", collID, "monetization_pointer", *c.Monetization, *c.Monetization)
  808. if err != nil {
  809. log.Error("Unable to insert monetization_pointer value: %v", err)
  810. return err
  811. }
  812. }
  813. }
  814. // Update rest of the collection data
  815. if q.Updates != "" {
  816. res, err = db.Exec("UPDATE collections SET "+q.Updates+" WHERE "+q.Conditions, q.Params...)
  817. if err != nil {
  818. log.Error("Unable to update collection: %v", err)
  819. return err
  820. }
  821. }
  822. rowsAffected, _ = res.RowsAffected()
  823. if !changed || rowsAffected == 0 {
  824. // Show the correct error message if nothing was updated
  825. var dummy int
  826. err := db.QueryRow("SELECT 1 FROM collections WHERE alias = ? AND owner_id = ?", alias, c.OwnerID).Scan(&dummy)
  827. switch {
  828. case err == sql.ErrNoRows:
  829. return ErrUnauthorizedEditPost
  830. case err != nil:
  831. log.Error("Failed selecting from collections: %v", err)
  832. }
  833. if !updatePass {
  834. return nil
  835. }
  836. }
  837. if updatePass {
  838. hashedPass, err := auth.HashPass([]byte(c.Pass))
  839. if err != nil {
  840. log.Error("Unable to create hash: %s", err)
  841. return impart.HTTPError{http.StatusInternalServerError, "Could not create password hash."}
  842. }
  843. if db.driverName == driverSQLite {
  844. _, err = db.Exec("INSERT OR REPLACE INTO collectionpasswords (collection_id, password) VALUES ((SELECT id FROM collections WHERE alias = ?), ?)", alias, hashedPass)
  845. } else {
  846. _, err = db.Exec("INSERT INTO collectionpasswords (collection_id, password) VALUES ((SELECT id FROM collections WHERE alias = ?), ?) "+db.upsert("collection_id")+" password = ?", alias, hashedPass, hashedPass)
  847. }
  848. if err != nil {
  849. return err
  850. }
  851. }
  852. return nil
  853. }
  854. const postCols = "id, slug, text_appearance, language, rtl, privacy, owner_id, collection_id, pinned_position, created, updated, view_count, title, content"
  855. // getEditablePost returns a PublicPost with the given ID only if the given
  856. // edit token is valid for the post.
  857. func (db *datastore) GetEditablePost(id, editToken string) (*PublicPost, error) {
  858. // FIXME: code duplicated from getPost()
  859. // TODO: add slight logic difference to getPost / one func
  860. var ownerName sql.NullString
  861. p := &Post{}
  862. row := db.QueryRow("SELECT "+postCols+", (SELECT username FROM users WHERE users.id = posts.owner_id) AS username FROM posts WHERE id = ? LIMIT 1", id)
  863. err := row.Scan(&p.ID, &p.Slug, &p.Font, &p.Language, &p.RTL, &p.Privacy, &p.OwnerID, &p.CollectionID, &p.PinnedPosition, &p.Created, &p.Updated, &p.ViewCount, &p.Title, &p.Content, &ownerName)
  864. switch {
  865. case err == sql.ErrNoRows:
  866. return nil, ErrPostNotFound
  867. case err != nil:
  868. log.Error("Failed selecting from collections: %v", err)
  869. return nil, err
  870. }
  871. if p.Content == "" && p.Title.String == "" {
  872. return nil, ErrPostUnpublished
  873. }
  874. res := p.processPost()
  875. if ownerName.Valid {
  876. res.Owner = &PublicUser{Username: ownerName.String}
  877. }
  878. return &res, nil
  879. }
  880. func (db *datastore) PostIDExists(id string) bool {
  881. var dummy bool
  882. err := db.QueryRow("SELECT 1 FROM posts WHERE id = ?", id).Scan(&dummy)
  883. return err == nil && dummy
  884. }
  885. // GetPost gets a public-facing post object from the database. If collectionID
  886. // is > 0, the post will be retrieved by slug and collection ID, rather than
  887. // post ID.
  888. // TODO: break this into two functions:
  889. // - GetPost(id string)
  890. // - GetCollectionPost(slug string, collectionID int64)
  891. func (db *datastore) GetPost(id string, collectionID int64) (*PublicPost, error) {
  892. var ownerName sql.NullString
  893. p := &Post{}
  894. var row *sql.Row
  895. var where string
  896. params := []interface{}{id}
  897. if collectionID > 0 {
  898. where = "slug = ? AND collection_id = ?"
  899. params = append(params, collectionID)
  900. } else {
  901. where = "id = ?"
  902. }
  903. row = db.QueryRow("SELECT "+postCols+", (SELECT username FROM users WHERE users.id = posts.owner_id) AS username FROM posts WHERE "+where+" LIMIT 1", params...)
  904. err := row.Scan(&p.ID, &p.Slug, &p.Font, &p.Language, &p.RTL, &p.Privacy, &p.OwnerID, &p.CollectionID, &p.PinnedPosition, &p.Created, &p.Updated, &p.ViewCount, &p.Title, &p.Content, &ownerName)
  905. switch {
  906. case err == sql.ErrNoRows:
  907. if collectionID > 0 {
  908. return nil, ErrCollectionPageNotFound
  909. }
  910. return nil, ErrPostNotFound
  911. case err != nil:
  912. log.Error("Failed selecting from collections: %v", err)
  913. return nil, err
  914. }
  915. if p.Content == "" && p.Title.String == "" {
  916. return nil, ErrPostUnpublished
  917. }
  918. res := p.processPost()
  919. if ownerName.Valid {
  920. res.Owner = &PublicUser{Username: ownerName.String}
  921. }
  922. return &res, nil
  923. }
  924. // TODO: don't duplicate getPost() functionality
  925. func (db *datastore) GetOwnedPost(id string, ownerID int64) (*PublicPost, error) {
  926. p := &Post{}
  927. var row *sql.Row
  928. where := "id = ? AND owner_id = ?"
  929. params := []interface{}{id, ownerID}
  930. row = db.QueryRow("SELECT "+postCols+" FROM posts WHERE "+where+" LIMIT 1", params...)
  931. err := row.Scan(&p.ID, &p.Slug, &p.Font, &p.Language, &p.RTL, &p.Privacy, &p.OwnerID, &p.CollectionID, &p.PinnedPosition, &p.Created, &p.Updated, &p.ViewCount, &p.Title, &p.Content)
  932. switch {
  933. case err == sql.ErrNoRows:
  934. return nil, ErrPostNotFound
  935. case err != nil:
  936. log.Error("Failed selecting from collections: %v", err)
  937. return nil, err
  938. }
  939. if p.Content == "" && p.Title.String == "" {
  940. return nil, ErrPostUnpublished
  941. }
  942. res := p.processPost()
  943. return &res, nil
  944. }
  945. func (db *datastore) GetPostProperty(id string, collectionID int64, property string) (interface{}, error) {
  946. propSelects := map[string]string{
  947. "views": "view_count AS views",
  948. }
  949. selectQuery, ok := propSelects[property]
  950. if !ok {
  951. return nil, impart.HTTPError{http.StatusBadRequest, fmt.Sprintf("Invalid property: %s.", property)}
  952. }
  953. var res interface{}
  954. var row *sql.Row
  955. if collectionID != 0 {
  956. row = db.QueryRow("SELECT "+selectQuery+" FROM posts WHERE slug = ? AND collection_id = ? LIMIT 1", id, collectionID)
  957. } else {
  958. row = db.QueryRow("SELECT "+selectQuery+" FROM posts WHERE id = ? LIMIT 1", id)
  959. }
  960. err := row.Scan(&res)
  961. switch {
  962. case err == sql.ErrNoRows:
  963. return nil, impart.HTTPError{http.StatusNotFound, "Post not found."}
  964. case err != nil:
  965. log.Error("Failed selecting post: %v", err)
  966. return nil, err
  967. }
  968. return res, nil
  969. }
  970. // GetPostsCount modifies the CollectionObj to include the correct number of
  971. // standard (non-pinned) posts. It will return future posts if `includeFuture`
  972. // is true.
  973. func (db *datastore) GetPostsCount(c *CollectionObj, includeFuture bool) {
  974. var count int64
  975. timeCondition := ""
  976. if !includeFuture {
  977. timeCondition = "AND created <= " + db.now()
  978. }
  979. err := db.QueryRow("SELECT COUNT(*) FROM posts WHERE collection_id = ? AND pinned_position IS NULL "+timeCondition, c.ID).Scan(&count)
  980. switch {
  981. case err == sql.ErrNoRows:
  982. c.TotalPosts = 0
  983. case err != nil:
  984. log.Error("Failed selecting from collections: %v", err)
  985. c.TotalPosts = 0
  986. }
  987. c.TotalPosts = int(count)
  988. }
  989. // GetPosts retrieves all posts for the given Collection.
  990. // It will return future posts if `includeFuture` is true.
  991. // It will include only standard (non-pinned) posts unless `includePinned` is true.
  992. // TODO: change includeFuture to isOwner, since that's how it's used
  993. func (db *datastore) GetPosts(cfg *config.Config, c *Collection, page int, includeFuture, forceRecentFirst, includePinned bool) (*[]PublicPost, error) {
  994. collID := c.ID
  995. cf := c.NewFormat()
  996. order := "DESC"
  997. if cf.Ascending() && !forceRecentFirst {
  998. order = "ASC"
  999. }
  1000. pagePosts := cf.PostsPerPage()
  1001. start := page*pagePosts - pagePosts
  1002. if page == 0 {
  1003. start = 0
  1004. pagePosts = 1000
  1005. }
  1006. limitStr := ""
  1007. if page > 0 {
  1008. limitStr = fmt.Sprintf(" LIMIT %d, %d", start, pagePosts)
  1009. }
  1010. timeCondition := ""
  1011. if !includeFuture {
  1012. timeCondition = "AND created <= " + db.now()
  1013. }
  1014. pinnedCondition := ""
  1015. if !includePinned {
  1016. pinnedCondition = "AND pinned_position IS NULL"
  1017. }
  1018. rows, err := db.Query("SELECT "+postCols+" FROM posts WHERE collection_id = ? "+pinnedCondition+" "+timeCondition+" ORDER BY created "+order+limitStr, collID)
  1019. if err != nil {
  1020. log.Error("Failed selecting from posts: %v", err)
  1021. return nil, impart.HTTPError{http.StatusInternalServerError, "Couldn't retrieve collection posts."}
  1022. }
  1023. defer rows.Close()
  1024. // TODO: extract this common row scanning logic for queries using `postCols`
  1025. posts := []PublicPost{}
  1026. for rows.Next() {
  1027. p := &Post{}
  1028. err = rows.Scan(&p.ID, &p.Slug, &p.Font, &p.Language, &p.RTL, &p.Privacy, &p.OwnerID, &p.CollectionID, &p.PinnedPosition, &p.Created, &p.Updated, &p.ViewCount, &p.Title, &p.Content)
  1029. if err != nil {
  1030. log.Error("Failed scanning row: %v", err)
  1031. break
  1032. }
  1033. p.extractData()
  1034. p.augmentContent(c)
  1035. p.formatContent(cfg, c, includeFuture, false)
  1036. posts = append(posts, p.processPost())
  1037. }
  1038. err = rows.Err()
  1039. if err != nil {
  1040. log.Error("Error after Next() on rows: %v", err)
  1041. }
  1042. return &posts, nil
  1043. }
  1044. // GetPostsTagged retrieves all posts on the given Collection that contain the
  1045. // given tag.
  1046. // It will return future posts if `includeFuture` is true.
  1047. // TODO: change includeFuture to isOwner, since that's how it's used
  1048. func (db *datastore) GetPostsTagged(cfg *config.Config, c *Collection, tag string, page int, includeFuture bool) (*[]PublicPost, error) {
  1049. collID := c.ID
  1050. cf := c.NewFormat()
  1051. order := "DESC"
  1052. if cf.Ascending() {
  1053. order = "ASC"
  1054. }
  1055. pagePosts := cf.PostsPerPage()
  1056. start := page*pagePosts - pagePosts
  1057. if page == 0 {
  1058. start = 0
  1059. pagePosts = 1000
  1060. }
  1061. limitStr := ""
  1062. if page > 0 {
  1063. limitStr = fmt.Sprintf(" LIMIT %d, %d", start, pagePosts)
  1064. }
  1065. timeCondition := ""
  1066. if !includeFuture {
  1067. timeCondition = "AND created <= " + db.now()
  1068. }
  1069. var rows *sql.Rows
  1070. var err error
  1071. if db.driverName == driverSQLite {
  1072. rows, err = db.Query("SELECT "+postCols+" FROM posts WHERE collection_id = ? AND LOWER(content) regexp ? "+timeCondition+" ORDER BY created "+order+limitStr, collID, `.*#`+strings.ToLower(tag)+`\b.*`)
  1073. } else {
  1074. rows, err = db.Query("SELECT "+postCols+" FROM posts WHERE collection_id = ? AND LOWER(content) RLIKE ? "+timeCondition+" ORDER BY created "+order+limitStr, collID, "#"+strings.ToLower(tag)+"[[:>:]]")
  1075. }
  1076. if err != nil {
  1077. log.Error("Failed selecting from posts: %v", err)
  1078. return nil, impart.HTTPError{http.StatusInternalServerError, "Couldn't retrieve collection posts."}
  1079. }
  1080. defer rows.Close()
  1081. // TODO: extract this common row scanning logic for queries using `postCols`
  1082. posts := []PublicPost{}
  1083. for rows.Next() {
  1084. p := &Post{}
  1085. err = rows.Scan(&p.ID, &p.Slug, &p.Font, &p.Language, &p.RTL, &p.Privacy, &p.OwnerID, &p.CollectionID, &p.PinnedPosition, &p.Created, &p.Updated, &p.ViewCount, &p.Title, &p.Content)
  1086. if err != nil {
  1087. log.Error("Failed scanning row: %v", err)
  1088. break
  1089. }
  1090. p.extractData()
  1091. p.augmentContent(c)
  1092. p.formatContent(cfg, c, includeFuture, false)
  1093. posts = append(posts, p.processPost())
  1094. }
  1095. err = rows.Err()
  1096. if err != nil {
  1097. log.Error("Error after Next() on rows: %v", err)
  1098. }
  1099. return &posts, nil
  1100. }
  1101. func (db *datastore) GetAPFollowers(c *Collection) (*[]RemoteUser, error) {
  1102. rows, err := db.Query("SELECT actor_id, inbox, shared_inbox FROM remotefollows f INNER JOIN remoteusers u ON f.remote_user_id = u.id WHERE collection_id = ?", c.ID)
  1103. if err != nil {
  1104. log.Error("Failed selecting from followers: %v", err)
  1105. return nil, impart.HTTPError{http.StatusInternalServerError, "Couldn't retrieve followers."}
  1106. }
  1107. defer rows.Close()
  1108. followers := []RemoteUser{}
  1109. for rows.Next() {
  1110. f := RemoteUser{}
  1111. err = rows.Scan(&f.ActorID, &f.Inbox, &f.SharedInbox)
  1112. followers = append(followers, f)
  1113. }
  1114. return &followers, nil
  1115. }
  1116. // CanCollect returns whether or not the given user can add the given post to a
  1117. // collection. This is true when a post is already owned by the user.
  1118. // NOTE: this is currently only used to potentially add owned posts to a
  1119. // collection. This has the SIDE EFFECT of also generating a slug for the post.
  1120. // FIXME: make this side effect more explicit (or extract it)
  1121. func (db *datastore) CanCollect(cpr *ClaimPostRequest, userID int64) bool {
  1122. var title, content string
  1123. var lang sql.NullString
  1124. err := db.QueryRow("SELECT title, content, language FROM posts WHERE id = ? AND owner_id = ?", cpr.ID, userID).Scan(&title, &content, &lang)
  1125. switch {
  1126. case err == sql.ErrNoRows:
  1127. return false
  1128. case err != nil:
  1129. log.Error("Failed on post CanCollect(%s, %d): %v", cpr.ID, userID, err)
  1130. return false
  1131. }
  1132. // Since we have the post content and the post is collectable, generate the
  1133. // post's slug now.
  1134. cpr.Slug = getSlugFromPost(title, content, lang.String)
  1135. return true
  1136. }
  1137. func (db *datastore) AttemptClaim(p *ClaimPostRequest, query string, params []interface{}, slugIdx int) (sql.Result, error) {
  1138. qRes, err := db.Exec(query, params...)
  1139. if err != nil {
  1140. if db.isDuplicateKeyErr(err) && slugIdx > -1 {
  1141. s := id.GenSafeUniqueSlug(p.Slug)
  1142. if s == p.Slug {
  1143. // Sanity check to prevent infinite recursion
  1144. return qRes, fmt.Errorf("GenSafeUniqueSlug generated nothing unique: %s", s)
  1145. }
  1146. p.Slug = s
  1147. params[slugIdx] = p.Slug
  1148. return db.AttemptClaim(p, query, params, slugIdx)
  1149. }
  1150. return qRes, fmt.Errorf("attemptClaim: %s", err)
  1151. }
  1152. return qRes, nil
  1153. }
  1154. func (db *datastore) DispersePosts(userID int64, postIDs []string) (*[]ClaimPostResult, error) {
  1155. postClaimReqs := map[string]bool{}
  1156. res := []ClaimPostResult{}
  1157. for i := range postIDs {
  1158. postID := postIDs[i]
  1159. r := ClaimPostResult{Code: 0, ErrorMessage: ""}
  1160. // Perform post validation
  1161. if postID == "" {
  1162. r.ErrorMessage = "Missing post ID. "
  1163. }
  1164. if _, ok := postClaimReqs[postID]; ok {
  1165. r.Code = 429
  1166. r.ErrorMessage = "You've already tried anonymizing this post."
  1167. r.ID = postID
  1168. res = append(res, r)
  1169. continue
  1170. }
  1171. postClaimReqs[postID] = true
  1172. var err error
  1173. // Get full post information to return
  1174. var fullPost *PublicPost
  1175. fullPost, err = db.GetPost(postID, 0)
  1176. if err != nil {
  1177. if err, ok := err.(impart.HTTPError); ok {
  1178. r.Code = err.Status
  1179. r.ErrorMessage = err.Message
  1180. r.ID = postID
  1181. res = append(res, r)
  1182. continue
  1183. } else {
  1184. log.Error("Error getting post in dispersePosts: %v", err)
  1185. }
  1186. }
  1187. if fullPost.OwnerID.Int64 != userID {
  1188. r.Code = http.StatusConflict
  1189. r.ErrorMessage = "Post is already owned by someone else."
  1190. r.ID = postID
  1191. res = append(res, r)
  1192. continue
  1193. }
  1194. var qRes sql.Result
  1195. var query string
  1196. var params []interface{}
  1197. // Do AND owner_id = ? for sanity.
  1198. // This should've been caught and returned with a good error message
  1199. // just above.
  1200. query = "UPDATE posts SET collection_id = NULL WHERE id = ? AND owner_id = ?"
  1201. params = []interface{}{postID, userID}
  1202. qRes, err = db.Exec(query, params...)
  1203. if err != nil {
  1204. r.Code = http.StatusInternalServerError
  1205. r.ErrorMessage = "A glitch happened on our end."
  1206. r.ID = postID
  1207. res = append(res, r)
  1208. log.Error("dispersePosts (post %s): %v", postID, err)
  1209. continue
  1210. }
  1211. // Post was successfully dispersed
  1212. r.Code = http.StatusOK
  1213. r.Post = fullPost
  1214. rowsAffected, _ := qRes.RowsAffected()
  1215. if rowsAffected == 0 {
  1216. // This was already claimed, but return 200
  1217. r.Code = http.StatusOK
  1218. }
  1219. res = append(res, r)
  1220. }
  1221. return &res, nil
  1222. }
  1223. func (db *datastore) ClaimPosts(cfg *config.Config, userID int64, collAlias string, posts *[]ClaimPostRequest) (*[]ClaimPostResult, error) {
  1224. postClaimReqs := map[string]bool{}
  1225. res := []ClaimPostResult{}
  1226. postCollAlias := collAlias
  1227. for i := range *posts {
  1228. p := (*posts)[i]
  1229. if &p == nil {
  1230. continue
  1231. }
  1232. r := ClaimPostResult{Code: 0, ErrorMessage: ""}
  1233. // Perform post validation
  1234. if p.ID == "" {
  1235. r.ErrorMessage = "Missing post ID `id`. "
  1236. }
  1237. if _, ok := postClaimReqs[p.ID]; ok {
  1238. r.Code = 429
  1239. r.ErrorMessage = "You've already tried claiming this post."
  1240. r.ID = p.ID
  1241. res = append(res, r)
  1242. continue
  1243. }
  1244. postClaimReqs[p.ID] = true
  1245. canCollect := db.CanCollect(&p, userID)
  1246. if !canCollect && p.Token == "" {
  1247. // TODO: ensure post isn't owned by anyone else when a valid modify
  1248. // token is given.
  1249. r.ErrorMessage += "Missing post Edit Token `token`."
  1250. }
  1251. if r.ErrorMessage != "" {
  1252. // Post validate failed
  1253. r.Code = http.StatusBadRequest
  1254. r.ID = p.ID
  1255. res = append(res, r)
  1256. continue
  1257. }
  1258. var err error
  1259. var qRes sql.Result
  1260. var query string
  1261. var params []interface{}
  1262. var slugIdx int = -1
  1263. var coll *Collection
  1264. if collAlias == "" {
  1265. // Posts are being claimed at /posts/claim, not
  1266. // /collections/{alias}/collect, so use given individual collection
  1267. // to associate post with.
  1268. postCollAlias = p.CollectionAlias
  1269. }
  1270. if postCollAlias != "" {
  1271. // Associate this post with a collection
  1272. if p.CreateCollection {
  1273. // This is a new collection
  1274. // TODO: consider removing this. This seriously complicates this
  1275. // method and adds another (unnecessary?) logic path.
  1276. coll, err = db.CreateCollection(cfg, postCollAlias, "", userID)
  1277. if err != nil {
  1278. if err, ok := err.(impart.HTTPError); ok {
  1279. r.Code = err.Status
  1280. r.ErrorMessage = err.Message
  1281. } else {
  1282. r.Code = http.StatusInternalServerError
  1283. r.ErrorMessage = "Unknown error occurred creating collection"
  1284. }
  1285. r.ID = p.ID
  1286. res = append(res, r)
  1287. continue
  1288. }
  1289. } else {
  1290. // Attempt to add to existing collection
  1291. coll, err = db.GetCollection(postCollAlias)
  1292. if err != nil {
  1293. if err, ok := err.(impart.HTTPError); ok {
  1294. if err.Status == http.StatusNotFound {
  1295. // Show obfuscated "forbidden" response, as if attempting to add to an
  1296. // unowned blog.
  1297. r.Code = ErrForbiddenCollection.Status
  1298. r.ErrorMessage = ErrForbiddenCollection.Message
  1299. } else {
  1300. r.Code = err.Status
  1301. r.ErrorMessage = err.Message
  1302. }
  1303. } else {
  1304. r.Code = http.StatusInternalServerError
  1305. r.ErrorMessage = "Unknown error occurred claiming post with collection"
  1306. }
  1307. r.ID = p.ID
  1308. res = append(res, r)
  1309. continue
  1310. }
  1311. if coll.OwnerID != userID {
  1312. r.Code = ErrForbiddenCollection.Status
  1313. r.ErrorMessage = ErrForbiddenCollection.Message
  1314. r.ID = p.ID
  1315. res = append(res, r)
  1316. continue
  1317. }
  1318. }
  1319. if p.Slug == "" {
  1320. p.Slug = p.ID
  1321. }
  1322. if canCollect {
  1323. // User already owns this post, so just add it to the given
  1324. // collection.
  1325. query = "UPDATE posts SET collection_id = ?, slug = ? WHERE id = ? AND owner_id = ?"
  1326. params = []interface{}{coll.ID, p.Slug, p.ID, userID}
  1327. slugIdx = 1
  1328. } else {
  1329. query = "UPDATE posts SET owner_id = ?, collection_id = ?, slug = ? WHERE id = ? AND modify_token = ? AND owner_id IS NULL"
  1330. params = []interface{}{userID, coll.ID, p.Slug, p.ID, p.Token}
  1331. slugIdx = 2
  1332. }
  1333. } else {
  1334. query = "UPDATE posts SET owner_id = ? WHERE id = ? AND modify_token = ? AND owner_id IS NULL"
  1335. params = []interface{}{userID, p.ID, p.Token}
  1336. }
  1337. qRes, err = db.AttemptClaim(&p, query, params, slugIdx)
  1338. if err != nil {
  1339. r.Code = http.StatusInternalServerError
  1340. r.ErrorMessage = "An unknown error occurred."
  1341. r.ID = p.ID
  1342. res = append(res, r)
  1343. log.Error("claimPosts (post %s): %v", p.ID, err)
  1344. continue
  1345. }
  1346. // Get full post information to return
  1347. var fullPost *PublicPost
  1348. if p.Token != "" {
  1349. fullPost, err = db.GetEditablePost(p.ID, p.Token)
  1350. } else {
  1351. fullPost, err = db.GetPost(p.ID, 0)
  1352. }
  1353. if err != nil {
  1354. if err, ok := err.(impart.HTTPError); ok {
  1355. r.Code = err.Status
  1356. r.ErrorMessage = err.Message
  1357. r.ID = p.ID
  1358. res = append(res, r)
  1359. continue
  1360. }
  1361. }
  1362. if fullPost.OwnerID.Int64 != userID {
  1363. r.Code = http.StatusConflict
  1364. r.ErrorMessage = "Post is already owned by someone else."
  1365. r.ID = p.ID
  1366. res = append(res, r)
  1367. continue
  1368. }
  1369. // Post was successfully claimed
  1370. r.Code = http.StatusOK
  1371. r.Post = fullPost
  1372. if coll != nil {
  1373. r.Post.Collection = &CollectionObj{Collection: *coll}
  1374. }
  1375. rowsAffected, _ := qRes.RowsAffected()
  1376. if rowsAffected == 0 {
  1377. // This was already claimed, but return 200
  1378. r.Code = http.StatusOK
  1379. }
  1380. res = append(res, r)
  1381. }
  1382. return &res, nil
  1383. }
  1384. func (db *datastore) UpdatePostPinState(pinned bool, postID string, collID, ownerID, pos int64) error {
  1385. if pos <= 0 || pos > 20 {
  1386. pos = db.GetLastPinnedPostPos(collID) + 1
  1387. if pos == -1 {
  1388. pos = 1
  1389. }
  1390. }
  1391. var err error
  1392. if pinned {
  1393. _, err = db.Exec("UPDATE posts SET pinned_position = ? WHERE id = ?", pos, postID)
  1394. } else {
  1395. _, err = db.Exec("UPDATE posts SET pinned_position = NULL WHERE id = ?", postID)
  1396. }
  1397. if err != nil {
  1398. log.Error("Unable to update pinned post: %v", err)
  1399. return err
  1400. }
  1401. return nil
  1402. }
  1403. func (db *datastore) GetLastPinnedPostPos(collID int64) int64 {
  1404. var lastPos sql.NullInt64
  1405. err := db.QueryRow("SELECT MAX(pinned_position) FROM posts WHERE collection_id = ? AND pinned_position IS NOT NULL", collID).Scan(&lastPos)
  1406. switch {
  1407. case err == sql.ErrNoRows:
  1408. return -1
  1409. case err != nil:
  1410. log.Error("Failed selecting from posts: %v", err)
  1411. return -1
  1412. }
  1413. if !lastPos.Valid {
  1414. return -1
  1415. }
  1416. return lastPos.Int64
  1417. }
  1418. func (db *datastore) GetPinnedPosts(coll *CollectionObj, includeFuture bool) (*[]PublicPost, error) {
  1419. // FIXME: sqlite-backed instances don't include ellipsis on truncated titles
  1420. timeCondition := ""
  1421. if !includeFuture {
  1422. timeCondition = "AND created <= " + db.now()
  1423. }
  1424. rows, err := db.Query("SELECT id, slug, title, "+db.clip("content", 80)+", pinned_position FROM posts WHERE collection_id = ? AND pinned_position IS NOT NULL "+timeCondition+" ORDER BY pinned_position ASC", coll.ID)
  1425. if err != nil {
  1426. log.Error("Failed selecting pinned posts: %v", err)
  1427. return nil, impart.HTTPError{http.StatusInternalServerError, "Couldn't retrieve pinned posts."}
  1428. }
  1429. defer rows.Close()
  1430. posts := []PublicPost{}
  1431. for rows.Next() {
  1432. p := &Post{}
  1433. err = rows.Scan(&p.ID, &p.Slug, &p.Title, &p.Content, &p.PinnedPosition)
  1434. if err != nil {
  1435. log.Error("Failed scanning row: %v", err)
  1436. break
  1437. }
  1438. p.extractData()
  1439. p.augmentContent(&coll.Collection)
  1440. pp := p.processPost()
  1441. pp.Collection = coll
  1442. posts = append(posts, pp)
  1443. }
  1444. return &posts, nil
  1445. }
  1446. func (db *datastore) GetCollections(u *User, hostName string) (*[]Collection, error) {
  1447. rows, err := db.Query("SELECT id, alias, title, description, privacy, view_count FROM collections WHERE owner_id = ? ORDER BY id ASC", u.ID)
  1448. if err != nil {
  1449. log.Error("Failed selecting from collections: %v", err)
  1450. return nil, impart.HTTPError{http.StatusInternalServerError, "Couldn't retrieve user collections."}
  1451. }
  1452. defer rows.Close()
  1453. colls := []Collection{}
  1454. for rows.Next() {
  1455. c := Collection{}
  1456. err = rows.Scan(&c.ID, &c.Alias, &c.Title, &c.Description, &c.Visibility, &c.Views)
  1457. if err != nil {
  1458. log.Error("Failed scanning row: %v", err)
  1459. break
  1460. }
  1461. c.hostName = hostName
  1462. c.URL = c.CanonicalURL()
  1463. c.Public = c.IsPublic()
  1464. /*
  1465. // NOTE: future functionality
  1466. if visibility != nil { // TODO: && visibility == CollPublic {
  1467. // Add Monetization info when retrieving all public collections
  1468. c.Monetization = db.GetCollectionAttribute(c.ID, "monetization_pointer")
  1469. }
  1470. */
  1471. colls = append(colls, c)
  1472. }
  1473. err = rows.Err()
  1474. if err != nil {
  1475. log.Error(