mcfi/database.py

import datetime
import json
import settings
import sqlite3

# Open (or create database)
db = sqlite3.connect(settings.database_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

# Create database tables for federation if they do not exist, for example if
# it's a new database.
with db:
    # Actors' private keys used for signing Activities
    db.execute("""
        CREATE TABLE IF NOT EXISTS private_key (
            actor  TEXT NOT NULL,
            key    BLOB NOT NULL,
            PRIMARY KEY (actor, key)
        )""")
    
    # List of remote actors that follow a local actor
    db.execute("""
        CREATE TABLE IF NOT EXISTS follower (
            local_actor  TEXT NOT NULL,
            remote_actor TEXT NOT NULL,
            PRIMARY KEY (local_actor, remote_actor)
        )""")
    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 (
            local_actor  TEXT NOT NULL,
            remote_actor TEXT NOT NULL,
            PRIMARY KEY (local_actor, remote_actor)
        )""")
    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 (
            datetime TEXT NOT NULL,
            actor    TEXT NOT NULL,
            message  TEXT NOT NULL
        )""")
    db.execute("CREATE INDEX IF NOT EXISTS dt_idx    ON inbox (datetime 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 (
            activity_id TEXT UNIQUE NOT NULL,
            datetime    TEXT NOT NULL,
            actor       TEXT NOT NULL,
            message     TEXT NOT NULL
        )""")
    db.execute("CREATE INDEX IF NOT EXISTS dt_idx    ON outbox (datetime ASC)")
    db.execute("CREATE INDEX IF NOT EXISTS actor_idx ON outbox (actor ASC)")

# Create database tables for a demo forge if they do not exist
with db:
    # Tickets table. This forge only keeps track of tickets number, so it can
    # generate new IDs.
    db.execute("""
        CREATE TABLE IF NOT EXISTS ticket (
            repository TEXT NOT NULL,
            counter    INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (repository)
        )""")

# Get the private key of a local actor
def get_private_key(local_actor):
    with db:
        cursor = db.execute (
            """
            SELECT key
            FROM   private_key
            WHERE  actor = ?
            """,
            [ local_actor ]
        )
    
        results = cursor.fetchone()
        
        return None if not results else results['key']

# Set the private key of a local actor
def set_private_key(actor, key):
    with db:
        db.execute (
            """
            INSERT OR IGNORE INTO private_key (actor, key)
            VALUES (?, ?)
            """,
            [ actor, key ]
        )

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

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

# Get the list of remote actors that follow a local actor
def get_followers(local_actor):
    with db:
        cursor = db.execute (
            """
            SELECT remote_actor
            FROM follower
            WHERE local_actor = ?
            """,
            [ 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(actor, message):
    with db:
        db.execute (
            """
            INSERT OR IGNORE INTO inbox (datetime, actor, message)
            VALUES (?, ?, ?)
            """,
            [ datetime.datetime.utcnow().replace(tzinfo=datetime.timezone.utc).isoformat(),
              actor, json.dumps(message) ]
        )

# Store a new message in the OUTBOX
def store_outbox(actor, message):
    with db:
        db.execute (
            """
            INSERT OR IGNORE INTO outbox (activity_id, datetime, actor, message)
            VALUES (?, ?, ?, ?)
            """,
            [ message['id'],
              datetime.datetime.utcnow().replace(tzinfo=datetime.timezone.utc).isoformat(),
              actor, json.dumps(message) ]
        )

# Get all INBOX messages for an actor
def get_inbox_messages(actor):
    with db:
        cursor = db.execute (
            """
            SELECT datetime, message
            FROM   inbox
            WHERE  actor = ?
            ORDER BY datetime DESC
            """,
            [ actor ]
        )
    
        results = cursor.fetchall()
        messages = [ json.loads(result['message']) for result in results ]
        
        return messages
        
# Get an Activity that was generated by a local actor
def get_activity(id):
    with db:
        cursor = db.execute (
            """
            SELECT message
            FROM   outbox
            WHERE  activity_id = ?
            """,
            [ id ]
        )
    
        results = cursor.fetchone()
        
        return None if not results else results['message']










def add_ticket(repository):
    with db:
        db.execute('BEGIN TRANSACTION')
        
        # Create a new row if doesn't exist
        db.execute ("INSERT OR IGNORE INTO ticket (repository) VALUES(?)", [ repository ])
        
        # Increment counter
        db.execute (
            """
            UPDATE ticket
            SET    counter = counter + 1
            WHERE  repository = ?
            """,
            [ repository ]
        )
        
        cursor = db.execute (
            """
            SELECT counter
            FROM   ticket
            WHERE  repository = ?
            """,
            [ repository ]
        )
        
        return cursor.fetchone()['counter']