<repo_name>mopsalarm/pr0gramm-comments<file_sep>/requirements.txt
CherryPy==8.9.1
attrdict==2.0.0
bottle==0.12.13
cheroot==5.1.0
datadog==0.13.0
decorator==4.0.11
first==2.0.1
portend==1.8
psycopg2==2.6.2
pytz==2016.10
requests==2.13.0
simplejson==3.10.0
six==1.10.0
tempora==1.6.1
https://github.com/mopsalarm/postgres-connection-cache/archive/master.tar.gz
<file_sep>/schema.sql

-- DROP TABLE IF EXISTS comment_favorites;

CREATE TABLE IF NOT EXISTS comment_favorites (
    fav_owner VARCHAR (32) DEFAULT NULL,

    id INTEGER NOT NULL,
    item_id INTEGER NOT NULL,
    name VARCHAR (255) NOT NULL,
    content TEXT NOT NULL,
    created INTEGER NOT NULL,
    up  INTEGER NOT NULl CHECK (up >= 0),
    down INTEGER NOT NULL CHECK (down >= 0),
    mark INTEGER NOT NULL CHECK (mark >= 0 AND mark < 16),
    thumb VARCHAR (255) NOT NULL,
    flags INTEGER NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS "comment_favorites__fav_owner__id" ON comment_favorites(fav_owner, id);
<file_sep>/README.md
# pr0gramm-comments
Service to store comment favorites.

## Client
This is a simple javascript client using jQuery.
```javascript

var CommentFavorites = {
  list: function(user_hash, flags) {
    return jQuery.ajax({
        method: "GET",
        url: "//pr0.wibbly-wobbly.de/api/comments/v1/" + encodeURIComponent(user_hash),
        data: {flags: flags || 1}
    });
  },

  put: function(user_hash, item_id, comment) {
    var body = jQuery.extend({}, comment, {item_id: item_id});
    return jQuery.ajax({
      method: "POST",
      url: "//pr0.wibbly-wobbly.de/api/comments/v1/" + encodeURIComponent(user_hash),
      contentType: "application/json",
      data: JSON.stringify(body)
    });
  },

  delete: function(user_hash, comment_id) {
    return jQuery.ajax({
      method: "POST",
      url: "//pr0.wibbly-wobbly.de/api/comments/v1/" + encodeURIComponent(user_hash) + "/" + encodeURIComponent(comment_id) + "/delete"
    });
  }
};
```

A comment object must contain the following properties: `id`, `name`, `content`,
`up`, `down`, `mark`, `thumb` as well as the creation time `created` in seconds. Those values are
exactly the same as on a pr0gramm comment object.

Examples:
```javascript
var user = someMd5HashLibrary.hash(info.user.email);
var comment = {id: 42, name: "Mopsalarm", content: "Testkommentar", up:20, down:10, mark:0, created: 1448092867, thumb: "2015/11/21/2b37cf2e3f9774a4.jpg", flags: 1};
var item_id = 771552;

CommentFavorites.put(user, item_id, comment).then(function() {
  console.log("Comment saved");
});

var flags = 1; // bit combination of 1, 2, 4
CommentFavorites.list(user, flags).then(function(comments) {
  console.log("Comments: ", comments);
});

CommentFavorites.delete(user, comment.id).then(function() {
  console.log("Comment deleted");
});
```
<file_sep>/main.py
#!/usr/bin/env python3
import json
import os
import re

import bottle
import datadog
import pcc
import psycopg2.extras
from attrdict import AttrDict as attrdict
from first import first

datadog.initialize()
stats = datadog.ThreadStats(constant_tags=["app:comments"])
stats.start()

CONFIG_POSTGRES_HOST = os.environ.get("POSTGRES_HOST", "localhost")

print("Open database at {}".format(CONFIG_POSTGRES_HOST))
dbpool = pcc.RefreshingConnectionCache(
    lifetime=600,
    host=CONFIG_POSTGRES_HOST, database="postgres",
    user="postgres", password="<PASSWORD>",
    cursor_factory=psycopg2.extras.DictCursor)


def is_app_user_agent(user_agent):
    return "okhttp" in user_agent or "pr0gramm-app" in user_agent

def stopwatch_plugin(func):
    def wrapper(*args, **kwargs):
        name = "pr0gramm.kfav.%s" % func.__name__
        user_agent = bottle.request.headers.get("user-agent", "").lower()
        platform = "app" if is_app_user_agent(user_agent) else "browser"
        with stats.timer(name, tags=["platform:%s" % platform]):
            return func(*args, **kwargs)

    return wrapper


bottle.install(stopwatch_plugin)


@bottle.hook('after_request')
def enable_cors():
    bottle.response.headers['Access-Control-Allow-Origin'] = '*'
    bottle.response.headers['Access-Control-Allow-Methods'] = 'PUT, GET, POST, DELETE, OPTIONS'


@bottle.post("/migrate")
def migrate():
    return {}


@bottle.post("/<user>")
@bottle.put("/<user>/<comment_id:int>")
def store_comment(user, comment_id=None):
    body = attrdict(bottle.request.json)
    if comment_id and body.id != comment_id:
        raise bottle.abort(400)

    with dbpool.tx() as database, database.cursor() as cursor:
        # get flag from the database.
        if "flags" not in body:
            cursor.execute('SELECT flags FROM items WHERE id=%s', [body.item_id])
            body.flags = first(row for row, in cursor.fetchall())

        cursor.execute('''
            INSERT INTO comment_favorites (fav_owner, id, item_id, name, content, created, up, down, mark, thumb, flags)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                ON CONFLICT (fav_owner, id)
                DO UPDATE SET created=%s, up=%s, down=%s, mark=%s
        ''', [user,
              body.id, body.item_id,
              body.name, body.content, body.created, body.up, body.down, body.mark, body.thumb, body.flags,
              body.created, body.up, body.down, body.mark])


@bottle.get("/<user>")
def list_comments(user):
    flags = int(bottle.request.query.get("flags", 1|2|4|8))
    flags = [flags & f for f in (1, 2, 4, 8)]

    with dbpool.tx() as database, database.cursor() as cursor:
        cursor.execute(
            'SELECT id, item_id, name, content, created, up, down, mark, thumb, flags FROM comment_favorites '
            'WHERE fav_owner=%s AND (TRUE OR flags IN %s) ORDER BY created DESC LIMIT 256',
            [user, tuple(flags)])

        comments = [dict(row) for row in cursor]

    bottle.response.content_type = "application/json"
    return json.dumps(comments)


@bottle.delete("/<user>/<comment_id:int>")
@bottle.post("/<user>/<comment_id:int>/delete")
def delete_comment(user, comment_id):
    with dbpool.tx() as database, database.cursor() as cursor:
        cursor.execute('DELETE FROM comment_favorites WHERE fav_owner=%s AND id=%s',
                       [user, comment_id])


@bottle.route('/<:re:.*>', method='OPTIONS')
def cors_generic_route():
    bottle.response.headers['Access-Control-Allow-Origin'] = '*'
    bottle.response.headers['Access-Control-Allow-Headers'] = 'Content-Type'


def main():
    bottle.run(debug=True, reloader=True)


if __name__ == '__main__':
    main()
