mcfi/database.py

import json
import os
import time
import settings
import sqlite3
import gitosis_configen

# Open (or create database)
DB_PATH = os.path.abspath(settings.database_path) # necessary since cwd changes later.
db = sqlite3.connect(DB_PATH)

# Returns SQLite rows as dictionaries instead of tuples.
# https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.row_factory
db.row_factory = sqlite3.Row


def debug_log(message):
    with open(os.path.join("..",settings.log_path), mode = "a") as file:
        file.write(str(time.thread_time()) + " " + message + "\n")
        file.close()
        

# Create database tables if they do not exist, for example if it's a new database.
with db:
    
    # List of remote actors that follow a local actor
    db.execute("""
        CREATE TABLE IF NOT EXISTS follower(
            forge        TEXT NOT NULL,
            remote_actor TEXT NOT NULL,
            local_actor  TEXT NOT NULL,
            PRIMARY KEY(forge, remote_actor, local_actor)
        )""")
    db.execute("CREATE INDEX IF NOT EXISTS forge_idx        ON follower (forge ASC)")
    db.execute("CREATE INDEX IF NOT EXISTS remote_actor_idx ON follower (remote_actor ASC)")
    db.execute("CREATE INDEX IF NOT EXISTS local_actor_idx  ON follower (local_actor ASC)")
    
    # List of local actors that follow a remote actor
    db.execute("""
        CREATE TABLE IF NOT EXISTS following(
            forge        TEXT NOT NULL,
            local_actor  TEXT NOT NULL,
            remote_actor TEXT NOT NULL,
            PRIMARY KEY(forge, local_actor, remote_actor)
        )""")
    db.execute("CREATE INDEX IF NOT EXISTS forge_idx        ON following (forge ASC)")
    db.execute("CREATE INDEX IF NOT EXISTS local_actor_idx  ON following (local_actor ASC)")
    db.execute("CREATE INDEX IF NOT EXISTS remote_actor_idx ON following (remote_actor ASC)")
    
    # INBOX table used to store incoming messages
    db.execute("""
        CREATE TABLE IF NOT EXISTS inbox(
            forge   TEXT NOT NULL,
            actor   TEXT NOT NULL,
            message TEXT NOT NULL
        )""")
    db.execute("CREATE INDEX IF NOT EXISTS forge_idx ON inbox (forge ASC)")
    db.execute("CREATE INDEX IF NOT EXISTS actor_idx ON inbox (actor ASC)")
    
    # OUTBOX table used to store outgoing messages
    db.execute("""
        CREATE TABLE IF NOT EXISTS outbox(
            forge   TEXT NOT NULL,
            actor   TEXT NOT NULL,
            message TEXT NOT NULL
        )""")
    db.execute("CREATE INDEX IF NOT EXISTS forge_idx ON outbox (forge ASC)")
    db.execute("CREATE INDEX IF NOT EXISTS actor_idx ON outbox (actor ASC)")
    
    # USERS table to store users. Associations of users with repos is handled by gitosis/gitolite config files.
    db.execute("""
        CREATE TABLE IF NOT EXISTS users(
            username   TEXT NOT NULL,
            email   TEXT NOT NULL,
            password TEXT NOT NULL,
            public_key TEXT NOT NULL
        )""")
    db.execute("CREATE INDEX IF NOT EXISTS username_idx ON users (username ASC)")
    
    # list of collaborations
    db.execute("""
        CREATE TABLE IF NOT EXISTS collaborations(
            repository TEXT NOT NULL,
            collaborator  TEXT NOT NULL
        )""")
    db.execute("CREATE INDEX IF NOT EXISTS repository_idx ON collaborations (repository ASC)")
    db.execute("CREATE INDEX IF NOT EXISTS collaborator_idx ON collaborations (collaborator ASC)")
    
    # list of repositories
    db.execute("""
        CREATE TABLE IF NOT EXISTS repositories(
            repository TEXT NOT NULL,
            owner  TEXT NOT NULL
        )""")
    db.execute("CREATE INDEX IF NOT EXISTS repository_idx ON repositories (repository ASC)")
    db.execute("CREATE INDEX IF NOT EXISTS  owner_idx ON repositories (owner ASC)")


# Add a remote follower to a local actor
def add_follower(forge, remote_actor, local_actor):
    with db:
        db.execute (
            """
            INSERT OR IGNORE INTO follower (forge, remote_actor, local_actor)
            VALUES (?, ?, ?)
            """,
            [ forge, remote_actor, local_actor ]
        )

# Add a local actor that is following a remote actor
def add_following(forge, local_actor, remote_actor):
    with db:
        db.execute (
            """
            INSERT OR IGNORE INTO following (forge, local_actor, remote_actor)
            VALUES (?, ?, ?)
            """,
            [ forge, local_actor, remote_actor ]
        )

# Get a local user
def get_user_local(username):
    with db:
        cursor = db.execute (
            """
            SELECT username, email, password, public_key
            FROM users
            WHERE username = ?
            """,
            [ username ]
        )
    
        result = cursor.fetchone()
        
        if settings.DEBUG:
            debug_log("db: retrieving user " + str(result))
        
        return result

def add_collaboration(repository, collaborator):
    with db:
        db.execute (
            """
            INSERT OR IGNORE INTO collaborations (repository, collaborator)
            VALUES (?, ?)
            """,
            [repository, collaborator]
        )

def add_repository(repository, owner):
    with db:
        db.execute (
            """
            INSERT OR IGNORE INTO repositories (repository, owner)
            VALUES (?, ?)
            """,
            [repository, owner]
        )

# Add a local user
def add_user(username, email, password, public_key):
    with db:
        if settings.DEBUG:
            debug_log("db: adding user " + str([username, email, password, public_key]))
            
        db.execute (
            """
            INSERT OR IGNORE INTO users (username, email, password, public_key)
            VALUES (?, ?, ?, ?)
            """,
            [ username, email, password, public_key ]
        )
        

    if not get_user_local(username):
        debug_log("ERROR: USER WAS NOT CREATED " + username )
        return False
    
    return True


# Get the list of remote actors that follow a local actor
def get_followers(forge, local_actor):
    with db:
        cursor = db.execute (
            """
            SELECT remote_actor
            FROM follower
            WHERE forge = ?
              AND local_actor = ?
            """,
            [ forge, local_actor ]
        )
    
        results = cursor.fetchall()
        followers = [ follower['remote_actor'] for follower in results ]
        
        return followers

# Store a new message in the INBOX
def store_inbox(forge, actor, message):
    with db:
        db.execute (
            """
            INSERT OR IGNORE INTO inbox (forge, actor, message)
            VALUES (?, ?, ?)
            """,
            [ forge, actor, json.dumps(message) ]
        )

# Store a new message in the OUTBOX
def store_outbox(forge, actor, message):
    with db:
        db.execute (
            """
            INSERT OR IGNORE INTO outbox (forge, actor, message)
            VALUES (?, ?, ?)
            """,
            [ forge, actor, json.dumps(message) ]
        )

# Get all INBOX messages for an actor
def get_inbox_messages(forge, actor):
    with db:
        cursor = db.execute (
            """
            SELECT message
            FROM inbox
            WHERE forge = ?
              AND actor = ?
            """,
            [ forge, actor ]
        )
    
        results = cursor.fetchall()
        messages = [ json.loads(result['message']) for result in results ]
        
        return messages

def get_collaborators(repository):
    with db:
        cursor = db.execute (
            """
            SELECT collaborator
            FROM collaborations
            WHERE repository = ?
            """,
            [ repository ]
        )
    
        results = cursor.fetchall()
        collaborators = [ result['collaborator'] for result in results ]
        
        return collaborators

def get_repositories():
    with db:
        cursor = db.execute (
            """
            SELECT repository, owner
            FROM repositories
            """
        )
    
        results = cursor.fetchall()
        return results

def regenerate_gitosis_config():
    
    debug_log("generating config")
    config = gitosis_configen.Config()
    
    for r in get_repositories():
        conf_r = gitosis_configen.Repository()
        conf_r.owner = r["owner"]
        conf_r.name = r["repository"]
        conf_r.collaborators = get_collaborators(r["repository"])
        debug_log("appending conf repository "+conf_r.print_())
        config.append(conf_r)
    
    debug_log("flushing")
    config.flush()
    debug_log("flushed")
    
    #make sure to do git push afterwards wherever you use it otherwise not effective
    pass