mochaserverpine64backup/mocha_server.py

#!/usr/bin/env python3
'''
Handle API requests to the database
'''

import json
import random
import string
import sqlite3
from os import path

DATABASE = '/usr/local/www/mocha-server/db.sqlite3'
if not path.exists(DATABASE):
    DATABASE = 'db.sqlite3'

# TODO: Add ability to store and retrieve avatars (as image files?)

# TODO: Add authentication generation
# TODO: Allow updating data with authorization


def make_new_account(username):
    '''
    Returns a new API key if the provided username is unique
    '''
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    output = cursor.execute(
        'SELECT user_id FROM users ORDER BY user_id DESC').fetchall()
    existing_ids = [str(dict(row)['user_id']) for row in output]

    user_id = str(random.randint(1, 1000000))
    while user_id in existing_ids:
        user_id = str(random.randint(1, 1000000))

    score = 0

    token = ''.join([
        random.choice(string.ascii_letters + string.digits + '+=')
        for _ in range(30)
    ])

    cursor.execute(
        'INSERT INTO users (user_id, username, score, token) VALUES (?, ?, ?, ?)',
        (user_id, "'" + username + "'", score, token))

    return json.dumps([{'key': token}])


def fetch_users(user_ids):
    '''
    Gets a list of users searching by name.
    This can also easily be done by user_id.
    '''
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    output = []
    for user_id in user_ids:
        cursor.execute(
            'SELECT user_id, username, score FROM users WHERE user_id=(?)',
            (user_id, ))
        output += cursor.fetchall()

    output = json.dumps([dict(row) for row in output])
    conn.commit()
    conn.close()

    if output == '[]':
        output = None

    return output


# add new parameters as needed
def update_row(_user_id, _updated_username):
    '''
    WIP.
    Will add a new user.
    '''
    print()


def insert_row(user_id, username):
    '''
    Inserts a row for a NEW user with given parameters
    This may work better with AUTOINCREMENT to avoid arbitrary ids and
    duplicates.

    Alternatively, use randomized unique IDs.
    '''

    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute('INSERT INTO users VALUES (?,?)', (user_id, username))
    conn.commit()
    conn.close()


def fetch_user(user_id):
    '''
    Returns a JSON object containing the requested user
    Also can return a list of all users if user_id == '*'
    '''
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    if user_id != '*':  # must use (?), (item,) format
        cursor.execute(
            'SELECT user_id, username, score FROM users WHERE user_id=(?) ORDER BY score DESC',
            (user_id, ))
    else:
        cursor.execute(
            'SELECT user_id, username, score FROM users ORDER BY score DESC')

    output = cursor.fetchall()
    output = json.dumps([dict(row) for row in output])
    conn.commit()
    conn.close()

    if output == '[]':
        output = None

    return output


def fetch_top_n(num):
    '''
    Retrieves the top n users by score.
    '''
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute(
        'SELECT user_id, username, score FROM users ORDER BY score DESC')
    output = cursor.fetchall()

    num = int(num)
    output = json.dumps([dict(row) for row in output][:num])
    conn.commit()
    conn.close()

    if output == '[]':
        output = None

    return output


def update(user_id, score):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute('UPDATE users SET score = (?) WHERE user_id = (?)',
                   (score, user_id))
    conn.commit()
    conn.close()

    return '[]'


def set_avatar(user_id, new_avatar):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute('UPDATE users SET avatar = (?) WHERE user_id = (?)',
                   (new_avatar, user_id))
    conn.commit()
    conn.close()

    return '[]'


def get_avatar(user_id):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute(
        'SELECT avatar FROM users WHERE user_id = (?)',
        (user_id))

    output = cursor.fetchall()
    output = json.dumps([dict(row) for row in output])
    conn.close()

    if output == '[]':
        output = None

    return output


def fetch_random_user():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute(
        'SELECT user_id, username, score FROM users ORDER BY RANDOM() LIMIT 1')

    output = cursor.fetchall()
    output = json.dumps([dict(row) for row in output])
    conn.close()

    if output == '[]':
        output = None

    return output


def process_request(uri):
    '''
    Handles the API endpoint.
    Currently supports:
        -  /mocha/users/{user_id}     Returns JSON of the specified user
        -  /mocha/users/*             Returns JSON list of all users
        -  /mocha/top/n               Returns JSON list of top n users by score
        -  /mocha/register/{username} Returns JSON of token for new user
        -  /mocha/update/{user_id}/{score}
        -  /mocha/avatar/{user_id}             Get user's avatar
        -  /mocha/avatar/{user_id}/{avatar}    Set user's avatar
        -  /mocha/random              Returns a random user
    '''
    parts = uri.split('/')[1:]
    assert parts[0] == 'mocha'
    if len(parts) < 2:
        return None

    output = None

    if parts[1] == 'users' and len(parts) > 2:
        if ',' in parts[2]:
            output = fetch_users(parts[2].split(','))
        else:
            output = fetch_user(parts[2])
    elif parts[1] == 'top' and len(parts) > 2:
        output = fetch_top_n(parts[2])
    elif parts[1] == 'register' and len(parts) > 2:
        output = make_new_account(parts[2])
    elif parts[1] == 'update' and len(parts) > 3:
        output = update(parts[2], parts[3])
    elif parts[1] == 'avatar' and len(parts) > 2:
        if len(parts) > 3:
            output = set_avatar(parts[2], parts[3])
        else:
            output = get_avatar(parts[2])
    elif parts[1] == 'random':
        output = fetch_random_user()
    else:
        output = None

    return output


def application(environ, start_response):
    '''
    mod_wsgi entry point
    '''
    status = '200 OK'
    output = process_request(environ['REQUEST_URI'])

    if output is None:
        status = '404 Not Found'
        output = ''

    output = output.encode('UTF-8')

    response_headers = [('Content-type', 'application/json'),
                        ('Content-Length', str(len(output)))]

    start_response(status, response_headers)

    return [output]


if __name__ == '__main__':
    print(fetch_random_user())

# vim: tabstop=4 shiftwidth=4 softtabstop=4 expandtab