forgefed/deprecated/3. sqlite/storage.py

import sqlite3

from Crypto.PublicKey import RSA
from . import settings

# Open (or create database file)
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 if they do not exist, for example if it's a
# new database.
with db:
    db.executescript(
        """
        -- GPG keys of actors used for signing HTTP requests
        CREATE TABLE IF NOT EXISTS gpg_key
        (
            actor   TEXT NOT NULL,  -- The actor who owns the key
            private BLOB NOT NULL,  -- The private part of the key
            public  BLOB NOT NULL,  -- The public part of the key
            PRIMARY KEY (actor)
        );
        
        -- List of "Follow" relationships.
        CREATE TABLE IF NOT EXISTS follow
        (
            subject TEXT NOT NULL,  -- Actor that is following
            object  TEXT NOT NULL,  -- Actor that is followed
            PRIMARY KEY (subject, object)
        );
        CREATE INDEX IF NOT EXISTS subject_idx ON follow (subject ASC);
        CREATE INDEX IF NOT EXISTS object_idx  ON follow (object  ASC);
    
        -- Copies of activities
        CREATE TABLE IF NOT EXISTS activity
        (
            id       TEXT NOT NULL,     -- The ID of the Activity
            document TEXT NOT NULL,     -- The JSON-LD document of the activity
            PRIMARY KEY (id)
        );
        
        -- Actor INBOX
        CREATE TABLE IF NOT EXISTS inbox
        (
            actor    TEXT NOT NULL,  -- The ID of the Actor
            activity TEXT NOT NULL,  -- The ID of the Activity
            received TEXT NOT NULL,  -- When the Activity was added to the INBOX
            PRIMARY KEY (actor, activity)
        );
        CREATE INDEX IF NOT EXISTS actor_idx    ON inbox (actor    ASC);
        CREATE INDEX IF NOT EXISTS activity_idx ON inbox (activity ASC);
    
        -- Actor OUTBOX
        CREATE TABLE IF NOT EXISTS outbox
        (
            actor    TEXT NOT NULL,  -- The ID of the Actor
            activity TEXT NOT NULL,  -- The ID of the Activity
            sent     TEXT NOT NULL,  -- When the Activity was added to the OUTBOX
            PRIMARY KEY (actor, activity)
        );
        CREATE INDEX IF NOT EXISTS actor_idx    ON inbox (actor    ASC);
        CREATE INDEX IF NOT EXISTS activity_idx ON inbox (activity ASC);
    """)

db.close()

def get_gpg_key(self, actor_id, autogenerate=True):
    """
    ActivityPub activities are signed and verified using HTTP signatures.
    This function automatically generates new keys for actors that don't
    have one, and returns a private+public keys pair.
    
    :param actor_id: The ID of the Actor to search.
    :param autogenerate: Automatically generate a new key pair if none exist.
    """
    
    keys = None
    
    # Retrieve the keys
    with self.db as db:
        cursor = db.execute (
            """
            SELECT private, public
            FROM   gpg_key
            WHERE  actor = ?
            """,
            [ actor_id ]
        )
    
        results = cursor.fetchone()
        
        if results:
            keys = { 'private': results['private'],
                        'public':  results['public'] }
    
    if not keys:
        # Create one
        key = RSA.generate(settings.HTTP_SIGNATURES_KEY_BITS)
        
        keys = { 'private': key.export_key('PEM'),
                    'public':  key.publickey().export_key('PEM') }
        
        # Save it in the database
        self.set_gpg_key(actor_id    = actor_id,
                            private_key = keys['private'],
                            public_key  = keys['public'])
    
    return keys

def set_gpg_key(self, actor_id, private_key, public_key):
    with self.db as db:
        db.execute (
            """
            INSERT OR IGNORE INTO gpg_key (actor, private, public)
            VALUES (?, ?, ?)
            """,
            [ actor_id, private_key, public_key ]
        )

def follow(self, subjet_id, object_id):
    with self.db as db:
        db.execute (
            """
            INSERT OR IGNORE INTO follow (subject, object)
            VALUES (?, ?)
            """,
            [ subjet_id, object_id ]
        )

def get_followers(self, actor_id):
    with self.db as db:
        cursor = db.execute (
            """
            SELECT subject
            FROM  follow
            WHERE object = ?
            """,
            [ actor_id ]
        )
    
        results = cursor.fetchall()
        followers = [ follower['subject'] for follower in results ]
        return followers

def get_following(self, actor_id):
    with self.db as db:
        cursor = db.execute (
            """
            SELECT object
            FROM  follow
            WHERE subject = ?
            """,
            [ actor_id ]
        )
    
        results = cursor.fetchall()
        following = [ follower['object'] for follower in results ]
        return following

def get_activity(self, id):
    with self.db as db:
        cursor = db.execute (
            """
            SELECT document
            FROM   activity
            WHERE  id = ?
            """,
            [ id ]
        )
    
        result = cursor.fetchone()
        return None if not result else json.loads(result['documnent'])

def get_inbox(self, actor_id, offset, limit):
    with self.db as db:
        cursor = db.execute (
            """
            SELECT   activity.document AS document
            FROM     inbox
            JOIN     activity ON activity.id = inbox.activity
            WHERE    inbox.actor = ?
            ORDER BY inbox.received DESC
            OFFSET   ?
            LIMIT    ?
            """,
            [ actor_id, offset, limit ]
        )
    
        results = cursor.fetchall()
        activities = [ json.loads(result['document']) for result in results ]
        
        return activities

def get_outbox(self, actor_id, offset, limit):
    with self.db as db:
        cursor = db.execute (
            """
            SELECT   activity.document AS document
            FROM     outbox
            JOIN     activity ON activity.id = outbox.activity
            WHERE    outbox.actor = ?
            ORDER BY outbox.received DESC
            OFFSET   ?
            LIMIT    ?
            """,
            [ actor_id, offset, limit ]
        )
    
        results = cursor.fetchall()
        activities = [ json.loads(result['document']) for result in results ]
        
        return activities

def store_inbox_activity(self, actor_id, activity_document):
    if 'id' not in activity_document:
        return
    
    # Add the Activity to the Actor's INBOX
    with self.db as db:
        db.execute (
            """
            INSERT OR IGNORE INTO activity (id, document)
            VALUES (?, ?)
            """,
            [ activity_document['id'], json.dumps(activity_document) ]
        )
        
        db.execute (
            """
            INSERT OR IGNORE INTO inbox (actor, activity, received)
            VALUES (?, ?, ?)
            """,
            [
                actor_id,
                activity_document['id'],
                datetime.datetime.utcnow().replace(tzinfo=datetime.timezone.utc).isoformat()
            ]
        )

def store_outbox_activity(self, actor_id, activity_document):
    if 'id' not in activity_document:
        return
    
    # Add the Activity to the Actor's INBOX
    with self.db as db:
        db.execute (
            """
            INSERT OR IGNORE INTO activity (id, document)
            VALUES (?, ?)
            """,
            [ activity_document['id'], json.dumps(activity_document) ]
        )
        
        db.execute (
            """
            INSERT OR IGNORE INTO outbox (actor, activity, sent)
            VALUES (?, ?, ?)
            """,
            [
                actor_id,
                activity_document['id'],
                datetime.datetime.utcnow().replace(tzinfo=datetime.timezone.utc).isoformat()
            ]
        )