pasty/src/db.nim

import std/[strutils]
import db_connector/db_postgres

type
  AlreadyExistsError* = object of ValueError
  NoSuchSnippetError* = object of ValueError
  InvalidAccessKeyError* = object of ValueError

proc snippetWithUrlExists*(pg: DbConn, url: string): bool =
  let id = pg.getValue(sql"SELECT id FROM snippets WHERE url = ?", url)
  return id.len > 0

proc insertSnippet*(pg: DbConn, url: string, body: string, accessKey: string, origin: string = ""): int =
  result = -1

  if pg.snippetWithUrlExists(url):
    raise newException(AlreadyExistsError, "Snippet with url " & url & " already exists")

  pg.exec(sql"BEGIN")

  try:
    let snippetId = pg.getValue(
      sql"INSERT INTO snippets (url, access_key) VALUES (?, sha512(?::bytea)) RETURNING id",
      url, accessKey
    )

    if not (snippetId.len > 0):
      # todo: log
      pg.exec(sql"ROLLBACK")
      return -1

    result = snippetId.parseInt

    let contentId =
      if origin.isEmptyOrWhitespace: pg.getValue(
        sql"INSERT INTO snippet_content (owner_snippet, body) VALUES (?, ?) RETURNING id",
        snippetId, body
      )
      else: pg.getValue(
        sql"INSERT INTO snippet_content (owner_snippet, body, origin) VALUES (?, ?, ?) RETURNING id",
        snippetId, body, origin
      )

    if not (contentId.len > 0):
      # todo: log
      pg.exec(sql"ROLLBACK")
      return -1

    pg.exec(sql"UPDATE snippets SET body = ? WHERE id = ?", contentId, snippetId)
    pg.exec(sql"COMMIT")

  except Exception as e:
    pg.exec(sql"ROLLBACK")
    raise e

proc fetchSnippetByUrl*(pg: DbConn, url: string, incrementAccesses = true): (int, int, string) =
  let row = pg.getRow(
    sql"SELECT s.id, s.access_count, sc.body FROM snippets s JOIN snippet_content sc ON s.body = sc.id WHERE s.url = ?",
    url
  )

  if row.len == 0 or row[0].len == 0:
    raise newException(NoSuchSnippetError, "Snippet not found")

  let
    id = row[0]
    accessCount = row[1].parseInt
  if incrementAccesses:
    pg.exec(sql"UPDATE snippets SET access_count = ? WHERE id = ?", $(accessCount + 1), id)
  return (id.parseInt, accessCount, row[2]) # [snippet id, access count, body]

proc deleteSnippetByUrl*(pg: DbConn, url: string, accessKey: string) =
  let id = pg.getValue(sql"SELECT id FROM snippets WHERE url = ?", url)
  if id.len == 0:
    raise newException(NoSuchSnippetError, "Snippet not found")

  let deleted = pg.getValue(
    sql"DELETE FROM snippets WHERE url = ? AND access_key = sha512(?::bytea) RETURNING id",
    url, accessKey
  )

  if deleted.len == 0:
    raise newException(InvalidAccessKeyError, "Invalid access key provided")

proc createSnippetRevision*(pg: DbConn, url: string, newBody: string, accessKey: string, origin: string = ""): int =
  result = -1

  let snippet = pg.getValue(sql"SELECT id FROM snippets WHERE url = ?", url)
  if snippet.len == 0:
    # todo: maybe refactor this to to do sha512 check in Nim code
    # to prevent reduntant sql queries
    raise newException(NoSuchSnippetError, "Snippet not found")

  let withKey = pg.getValue(
    sql"SELECT id FROM snippets WHERE id = ? AND access_key = sha512(?::bytea)",
    snippet, accessKey
  )
  if withKey.len == 0:
    raise newException(InvalidAccessKeyError, "Invalid access key provided")

  let revision = pg.getValue(
    sql"SELECT MAX(revision) FROM snippet_content WHERE owner_snippet = ?",
    snippet
  )
  result = revision.parseInt + 1

  pg.exec(sql"BEGIN")

  try:
    let contentId =
      if origin.isEmptyOrWhitespace: pg.getValue(
        sql"INSERT INTO snippet_content (owner_snippet, revision, body) VALUES (?, ?, ?) RETURNING id",
        snippet, $result, newBody
      )
      else: pg.getValue(
        sql"INSERT INTO snippet_content (owner_snippet, revision, body, origin) VALUES (?, ?, ?, ?) RETURNING id",
        snippet, $result, newBody, origin
      )

    if contentId.len == 0:
      pg.exec(sql"ROLLBACK")
      raise newException(ValueError, "Failed to insert new snippet content.")

    pg.exec(
      sql"UPDATE snippets SET body = ? WHERE id = ?",
      contentId, snippet
    )

    pg.exec(sql"COMMIT")

  except Exception as e:
    pg.exec(sql"ROLLBACK")
    raise e

proc fetchRevisionByUrl*(pg: DbConn, url: string, revision: int): string =
  let row = pg.getRow(
    sql"SELECT sc.body FROM snippets s JOIN snippet_content sc ON s.id = sc.owner_snippet AND sc.revision = ? WHERE s.url = ?",
    $revision, url
  )

  if not (row.len == 0 or row[0].len == 0):
    return row[0]
  else:
    raise newException(NoSuchSnippetError, "Snippet revision not found")

when isMainModule:
  # some tests
  import ./env
  let (url, body, accessKey) = ("aaaaaaaaaaaa", "testing body", "access key")
  let conn = open(
    getEnv("PASTY_PG_HOST", "localhost"),
    getEnv("PASTY_PG_USER", "pasty"),
    getEnv("PASTY_PG_PASSWORD"),
    getEnv("PASTY_PG_DB", "pasty"),
  )

  # inserting
  let id = conn.insertSnippet(url, body, accessKey)
  doAssert (conn.snippetWithUrlExists(url))

  # fetching
  doAssert (conn.fetchSnippetByUrl(url)) == (id, 0, body)
  doAssertRaises(NoSuchSnippetError):
    discard conn.fetchSnippetByUrl("doesnt exist")

  # revisions
  let (body1, body2) = ("new body a", "new body b")
  doAssert (conn.createSnippetRevision(url, body1, accessKey)) == 1
  doAssert (conn.createSnippetRevision(url, body2, accessKey)) == 2
  doAssertRaises(InvalidAccessKeyError):
    discard conn.createSnippetRevision(url, body1, "some invalid key")
  doAssert (conn.fetchSnippetByUrl(url)) == (id, 1, body2)
  doAssert (conn.fetchRevisionByUrl(url, 1)) == body1

  # deleting
  doAssertRaises(InvalidAccessKeyError):
    conn.deleteSnippetByUrl(url, "some invalid key")
  conn.deleteSnippetByUrl(url, accessKey)
  doAssertRaises(NoSuchSnippetError):
    conn.deleteSnippetByUrl(url, "")