import sqlite3

DB_FILENAME = 'citationhunt.sqlite3'

def init_db():
    return sqlite3.connect(DB_FILENAME)

def reset_db():
    db = init_db()

    with db:
        db.execute('''
            DROP TABLE IF EXISTS categories
        ''')
        db.execute('''
            DROP TABLE IF EXISTS articles
        ''')
        db.execute('''
            DROP TABLE IF EXISTS snippets
        ''')
        db.execute('''
            DROP TABLE IF EXISTS articles_categories
        ''')
        db.execute('''
            CREATE TABLE categories (id TEXT PRIMARY KEY, title TEXT)
        ''')
        db.execute('''
            INSERT INTO categories VALUES ("unassigned", "unassigned")
        ''')
        db.execute('''
            CREATE TABLE articles_categories (article_id TEXT, category_id TEXT,
            FOREIGN KEY(article_id) REFERENCES articles(page_id)
            ON DELETE CASCADE,
            FOREIGN KEY(category_id) REFERENCES categories(id)
            ON DELETE CASCADE)
        ''')
        db.execute('''
            CREATE TABLE articles (page_id TEXT PRIMARY KEY, url TEXT,
            title TEXT)
        ''')
        db.execute('''
            CREATE TABLE snippets (id TEXT PRIMARY KEY, snippet TEXT,
            section TEXT, article_id TEXT, FOREIGN KEY(article_id)
            REFERENCES articles(page_id) ON DELETE CASCADE)
        ''')

    return db

def create_indices():
    db = init_db()

    db.execute('''CREATE INDEX IF NOT EXISTS snippets_articles
        ON snippets(article_id);''')

import sqlite3

DB_FILENAME = 'citationhunt.sqlite3'

def init_db():
    return sqlite3.connect(DB_FILENAME)

def reset_db():
    db = init_db()

    with db:
        db.execute('''
            DROP TABLE categories
        ''')
        db.execute('''
            DROP TABLE articles
        ''')
        db.execute('''
            DROP TABLE snippets
        ''')
        db.execute('''
            DROP TABLE articles_categories
        ''')
        db.execute('''
            CREATE TABLE categories (id TEXT PRIMARY KEY, title TEXT)
        ''')
        db.execute('''
            INSERT INTO categories VALUES ("unassigned", "unassigned")
        ''')
        db.execute('''
            CREATE TABLE articles_categories (article_id TEXT, category_id TEXT,
            FOREIGN KEY(article_id) REFERENCES articles(page_id)
            ON DELETE CASCADE,
            FOREIGN KEY(category_id) REFERENCES categories(id)
            ON DELETE CASCADE)
        ''')
        db.execute('''
            CREATE TABLE articles (page_id TEXT PRIMARY KEY, url TEXT,
            title TEXT)
        ''')
        db.execute('''
            CREATE TABLE snippets (id TEXT PRIMARY KEY, snippet TEXT,
            section TEXT, article_id TEXT, FOREIGN KEY(article_id)
            REFERENCES articles(page_id) ON DELETE CASCADE)
        ''')

    return db

def create_indices():
    db = init_db()

    db.execute('''CREATE INDEX IF NOT EXISTS snippets_articles
        ON snippets(article_id);''')

Remove IF EXISTS from DROP TABLE when resetting the db.
Remove IF EXISTS from DROP TABLE when resetting the db.

  import sqlite3
  
  DB_FILENAME = 'citationhunt.sqlite3'
  
  def init_db():
      return sqlite3.connect(DB_FILENAME)
  
  def reset_db():
      db = init_db()
  
      with db:
          db.execute('''
-             DROP TABLE IF EXISTS categories
+             DROP TABLE categories
          ''')
          db.execute('''
-             DROP TABLE IF EXISTS articles
+             DROP TABLE articles
          ''')
          db.execute('''
-             DROP TABLE IF EXISTS snippets
+             DROP TABLE snippets
          ''')
          db.execute('''
-             DROP TABLE IF EXISTS articles_categories
+             DROP TABLE articles_categories
          ''')
          db.execute('''
              CREATE TABLE categories (id TEXT PRIMARY KEY, title TEXT)
          ''')
          db.execute('''
              INSERT INTO categories VALUES ("unassigned", "unassigned")
          ''')
          db.execute('''
              CREATE TABLE articles_categories (article_id TEXT, category_id TEXT,
              FOREIGN KEY(article_id) REFERENCES articles(page_id)
              ON DELETE CASCADE,
              FOREIGN KEY(category_id) REFERENCES categories(id)
              ON DELETE CASCADE)
          ''')
          db.execute('''
              CREATE TABLE articles (page_id TEXT PRIMARY KEY, url TEXT,
              title TEXT)
          ''')
          db.execute('''
              CREATE TABLE snippets (id TEXT PRIMARY KEY, snippet TEXT,
              section TEXT, article_id TEXT, FOREIGN KEY(article_id)
              REFERENCES articles(page_id) ON DELETE CASCADE)
          ''')
  
      return db
  
  def create_indices():
      db = init_db()
  
      db.execute('''CREATE INDEX IF NOT EXISTS snippets_articles
          ON snippets(article_id);''')
  
Remove IF EXISTS from DROP TABLE when resetting the db.
## Code Before:
import sqlite3

DB_FILENAME = 'citationhunt.sqlite3'

def init_db():
    return sqlite3.connect(DB_FILENAME)

def reset_db():
    db = init_db()

    with db:
        db.execute('''
            DROP TABLE IF EXISTS categories
        ''')
        db.execute('''
            DROP TABLE IF EXISTS articles
        ''')
        db.execute('''
            DROP TABLE IF EXISTS snippets
        ''')
        db.execute('''
            DROP TABLE IF EXISTS articles_categories
        ''')
        db.execute('''
            CREATE TABLE categories (id TEXT PRIMARY KEY, title TEXT)
        ''')
        db.execute('''
            INSERT INTO categories VALUES ("unassigned", "unassigned")
        ''')
        db.execute('''
            CREATE TABLE articles_categories (article_id TEXT, category_id TEXT,
            FOREIGN KEY(article_id) REFERENCES articles(page_id)
            ON DELETE CASCADE,
            FOREIGN KEY(category_id) REFERENCES categories(id)
            ON DELETE CASCADE)
        ''')
        db.execute('''
            CREATE TABLE articles (page_id TEXT PRIMARY KEY, url TEXT,
            title TEXT)
        ''')
        db.execute('''
            CREATE TABLE snippets (id TEXT PRIMARY KEY, snippet TEXT,
            section TEXT, article_id TEXT, FOREIGN KEY(article_id)
            REFERENCES articles(page_id) ON DELETE CASCADE)
        ''')

    return db

def create_indices():
    db = init_db()

    db.execute('''CREATE INDEX IF NOT EXISTS snippets_articles
        ON snippets(article_id);''')

## Instruction:
Remove IF EXISTS from DROP TABLE when resetting the db.
## Code After:
import sqlite3

DB_FILENAME = 'citationhunt.sqlite3'

def init_db():
    return sqlite3.connect(DB_FILENAME)

def reset_db():
    db = init_db()

    with db:
        db.execute('''
            DROP TABLE categories
        ''')
        db.execute('''
            DROP TABLE articles
        ''')
        db.execute('''
            DROP TABLE snippets
        ''')
        db.execute('''
            DROP TABLE articles_categories
        ''')
        db.execute('''
            CREATE TABLE categories (id TEXT PRIMARY KEY, title TEXT)
        ''')
        db.execute('''
            INSERT INTO categories VALUES ("unassigned", "unassigned")
        ''')
        db.execute('''
            CREATE TABLE articles_categories (article_id TEXT, category_id TEXT,
            FOREIGN KEY(article_id) REFERENCES articles(page_id)
            ON DELETE CASCADE,
            FOREIGN KEY(category_id) REFERENCES categories(id)
            ON DELETE CASCADE)
        ''')
        db.execute('''
            CREATE TABLE articles (page_id TEXT PRIMARY KEY, url TEXT,
            title TEXT)
        ''')
        db.execute('''
            CREATE TABLE snippets (id TEXT PRIMARY KEY, snippet TEXT,
            section TEXT, article_id TEXT, FOREIGN KEY(article_id)
            REFERENCES articles(page_id) ON DELETE CASCADE)
        ''')

    return db

def create_indices():
    db = init_db()

    db.execute('''CREATE INDEX IF NOT EXISTS snippets_articles
        ON snippets(article_id);''')
