123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253 |
- 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']
|