1  """
  2  Statement module
  3  """
  4  
  5  
  6  class Statement:
  7      """
  8      Standard database schema SQL statements.
  9      """
 10  
 11      # Temporary table for working with id batches
 12      CREATE_BATCH = """
 13          CREATE TEMP TABLE IF NOT EXISTS batch (
 14              indexid INTEGER,
 15              id TEXT,
 16              batch INTEGER
 17          )
 18      """
 19  
 20      DELETE_BATCH = "DELETE FROM batch"
 21      INSERT_BATCH_INDEXID = "INSERT INTO batch (indexid, batch) VALUES (?, ?)"
 22      INSERT_BATCH_ID = "INSERT INTO batch (id, batch) VALUES (?, ?)"
 23  
 24      # Temporary table for joining similarity scores
 25      CREATE_SCORES = """
 26          CREATE TEMP TABLE IF NOT EXISTS scores (
 27              indexid INTEGER PRIMARY KEY,
 28              score REAL
 29          )
 30      """
 31  
 32      DELETE_SCORES = "DELETE FROM scores"
 33      INSERT_SCORE = "INSERT INTO scores VALUES (?, ?)"
 34  
 35      # Documents - stores full content
 36      CREATE_DOCUMENTS = """
 37          CREATE TABLE IF NOT EXISTS documents (
 38              id TEXT PRIMARY KEY,
 39              data JSON,
 40              tags TEXT,
 41              entry DATETIME
 42          )
 43      """
 44  
 45      INSERT_DOCUMENT = "INSERT OR REPLACE INTO documents VALUES (?, ?, ?, ?)"
 46      DELETE_DOCUMENTS = "DELETE FROM documents WHERE id IN (SELECT id FROM batch)"
 47  
 48      # Objects - stores binary content
 49      CREATE_OBJECTS = """
 50          CREATE TABLE IF NOT EXISTS objects (
 51              id TEXT PRIMARY KEY,
 52              object BLOB,
 53              tags TEXT,
 54              entry DATETIME
 55          )
 56      """
 57  
 58      INSERT_OBJECT = "INSERT OR REPLACE INTO objects VALUES (?, ?, ?, ?)"
 59      DELETE_OBJECTS = "DELETE FROM objects WHERE id IN (SELECT id FROM batch)"
 60  
 61      # Sections - stores section text
 62      CREATE_SECTIONS = """
 63          CREATE TABLE IF NOT EXISTS %s (
 64              indexid INTEGER PRIMARY KEY,
 65              id TEXT,
 66              text TEXT,
 67              tags TEXT,
 68              entry DATETIME
 69          )
 70      """
 71  
 72      CREATE_SECTIONS_INDEX = "CREATE INDEX section_id ON sections(id)"
 73      INSERT_SECTION = "INSERT INTO sections VALUES (?, ?, ?, ?, ?)"
 74      DELETE_SECTIONS = "DELETE FROM sections WHERE id IN (SELECT id FROM batch)"
 75      COPY_SECTIONS = (
 76          "INSERT INTO %s SELECT (select count(*) - 1 from sections s1 where s.indexid >= s1.indexid) indexid, "
 77          + "s.id, %s AS text, s.tags, s.entry FROM sections s LEFT JOIN documents d ON s.id = d.id ORDER BY indexid"
 78      )
 79      STREAM_SECTIONS = (
 80          "SELECT s.id, s.text, data, object, s.tags FROM %s s "
 81          + "LEFT JOIN documents d ON s.id = d.id "
 82          + "LEFT JOIN objects o ON s.id = o.id ORDER BY indexid"
 83      )
 84      DROP_SECTIONS = "DROP TABLE sections"
 85      RENAME_SECTIONS = "ALTER TABLE %s RENAME TO sections"
 86  
 87      # Queries
 88      SELECT_IDS = "SELECT indexid, id FROM sections WHERE id in (SELECT id FROM batch)"
 89      COUNT_IDS = "SELECT count(indexid) FROM sections"
 90  
 91      # Partial sql clauses
 92      TABLE_CLAUSE = (
 93          "SELECT %s FROM sections s "
 94          + "%s documents d ON s.id = d.id "
 95          + "LEFT JOIN objects o ON s.id = o.id "
 96          + "LEFT JOIN scores sc ON s.indexid = sc.indexid"
 97      )
 98      IDS_CLAUSE = "s.indexid in (SELECT indexid from batch WHERE batch=%s)"
 99  
100      # Expression indexes
101      CREATE_EXPRESSION_INDEX = "CREATE INDEX IF NOT EXISTS %s ON %s(%s)"