Module: QDA::Backend::SQLite::Schema

Defined in:

lib/weft/backend/sqlite/schema.rb

Constant Summary

SCHEMA_TABLES =

The tables

"CREATE TABLE category (\n  catid INTEGER PRIMARY KEY,\n  catname VARCHAR(255) DEFAULT NULL,\n  catdesc TEXT,\n  parent INTEGER,\n  created_date TIMESTAMP,\n  modified_date TIMESTAMP );\n\nCREATE TABLE category_structure ( xml TEXT );\n\nINSERT INTO category_structure VALUES ('');\n\nCREATE TABLE code (\n  catid INT(11) default NULL,\n  docid INT(11) default NULL,\n  offset INT(11) default NULL,\n  length INT(11) default NULL );\n\nCREATE TABLE docmeta (\n  docid INT(11) default NULL,\n  metaname VARCHAR(100) default NULL,\n  metavalue TEXT );\n\nCREATE TABLE document (\n  docid INTEGER PRIMARY KEY,\n  doctitle VARCHAR(255) default NULL,\n  doctext TEXT,\n  docmemo TEXT,\n  created_date TIMESTAMP,\n  modified_date TIMESTAMP);\n\nCREATE TABLE app_preference ( \n  name VARCHAR(255) NOT NULL PRIMARY KEY ON CONFLICT REPLACE,\n  value TEXT);\n"

SCHEMA_TRIGGERS =

Triggers- these currently just ensure that coding is cleaned up when a document or category is deleted. In the future they also trigger entries into the undo/redo table.

"CREATE TRIGGER insert_category\n  INSERT ON category\n  BEGIN\nEND;\n\nCREATE TRIGGER delete_category\n  DELETE ON category\n  BEGIN\n    DELETE FROM code WHERE catid = old.catid;\nEND;\n\nCREATE TRIGGER insert_doc\n  INSERT ON document\n  BEGIN\nEND;\n\nCREATE TRIGGER delete_doc DELETE ON document\nBEGIN\n  DELETE FROM docmeta WHERE docid = old.docid;\n  DELETE FROM code WHERE docid = old.docid;\nEND;\n\n"

SCHEMA_UNDO =

This is here because it’s written, but it’s not in use yet.

This is an outline of adding undo/redo facility using SQLite triggers, writing stepped actions to a undo action table, and recording SQL to restore the database to its prior state.

"CREATE TABLE undoable ( \n  actionid INTEGER PRIMARY KEY,\n  step INT(255) DEFAULT 0,\n  sql TEXT );\n\nCREATE TRIGGER undo_insert_category INSERT ON category\nBEGIN\n    INSERT INTO undoable VALUES(NULL, 0, 'DELETE FROM category ' ||\n                                         'WHERE docid = ' || \n                                         LAST_INSERT_ROWID() );\nEND;\n\nCREATE TRIGGER undo_delete_category DELETE ON category\n  BEGIN\n    INSERT INTO undoable VALUES(NULL, 0,\n                'INSERT INTO category\n                 VALUES (' ||\n                 old.catid || ', ' ||\n                 QUOTE(old.catname) || ' ,' ||\n                 QUOTE(old.catdesc)  || ', ' ||\n                 old.parent || ', ' ||\n                 QUOTE(old.created_date) || ', ' ||\n                 QUOTE(old.modified_date) || ')' );\n\nCREATE TRIGGER undo_insert_document INSERTO ON document\nBEGIN\nEND;\n\nCREATE TRIGGER undo_delete_document DELETE ON document\nBEGIN\nEND;\n\nCREATE TRIGGER undo_insert_code INSERT ON code\nBEGIN\nEND;\n\nCREATE TRIGGER undo_delete_code DELETE ON code\nBEGIN\nEND;\n\nCREATE TRIGGER undo_insert_docmeta INSERT ON docmeta\nBEGIN\nEND;\n\nCREATE TRIGGER undo_delete_docmeta DELETE ON docmeta\nBEGIN\nEND;\n\nCREATE TRIGGER undo_update_category_structure \nUPDATE ON category_structure\nBEGIN\nEND;\n"

SCHEMA_INDEXES =

Indexes - those on the coding table make a big difference to speed of retrieving marked text.

"\nCREATE INDEX document_idx\nON document(doctitle);\n\nCREATE INDEX code_idx \nON code(docid, catid);\n\nCREATE INDEX docmeta_idx\nON docmeta(metaname, docid);\n\n"

RINDEX_SEARCH_MODEL_QUERY =

model query for doing fast text searches from a reverse index (stored as categories).

"SELECT document.docid AS docid, document.doctitle AS doctitle,\nMAX( 0, code.offset - ?)\nAS start_at,\nSUBSTR(document.doctext, \n  MAX( 0, code.offset - ?) + 1,\n  MIN( code.length + ( ? * 2 ),\n       LENGTH(document.doctext) - MAX(1, code.offset - ?) - 1 ) )\nAS snip\nFROM document, code\nWHERE code.docid = document.docid\nAND code.catid IN (\n  SELECT catid\n  FROM category\n  WHERE parent = ?\n  AND LOWER(category.catname) LIKE ?)\nORDER BY code.catid, code.offset\n"