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

Defined in:

lib/weft/backend/sqlite/schema.rb

Constant Summary

SCHEMA_TABLES =

The tables

<<'SCHEMA_TABLES'
CREATE TABLE category (
  catid INTEGER PRIMARY KEY,
  catname VARCHAR(255) DEFAULT NULL,
  catdesc TEXT,
  parent INTEGER,
  created_date TIMESTAMP,
  modified_date TIMESTAMP );

CREATE TABLE category_structure ( xml TEXT );

INSERT INTO category_structure VALUES ('');

CREATE TABLE code (
  catid INT(11) default NULL,
  docid INT(11) default NULL,
  offset INT(11) default NULL,
  length INT(11) default NULL );

CREATE TABLE docmeta (
  docid INT(11) default NULL,
  metaname VARCHAR(100) default NULL,
  metavalue TEXT );

CREATE TABLE document (
  docid INTEGER PRIMARY KEY,
  doctitle VARCHAR(255) default NULL,
  doctext TEXT,
  docmemo TEXT,
  created_date TIMESTAMP,
  modified_date TIMESTAMP);

CREATE TABLE app_preference ( 
  name VARCHAR(255) NOT NULL PRIMARY KEY ON CONFLICT REPLACE,
  value TEXT);
SCHEMA_TABLES

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.

<<'SCHEMA_TRIGGERS'
CREATE TRIGGER insert_category
  INSERT ON category
  BEGIN
END;

CREATE TRIGGER delete_category
  DELETE ON category
  BEGIN
    DELETE FROM code WHERE catid = old.catid;
END;

CREATE TRIGGER insert_doc
  INSERT ON document
  BEGIN
END;

CREATE TRIGGER delete_doc DELETE ON document
BEGIN
  DELETE FROM docmeta WHERE docid = old.docid;
  DELETE FROM code WHERE docid = old.docid;
END;

SCHEMA_TRIGGERS

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.

<<'SCHEMA_UNDO'
CREATE TABLE undoable ( 
  actionid INTEGER PRIMARY KEY,
  step INT(255) DEFAULT 0,
  sql TEXT );

CREATE TRIGGER undo_insert_category INSERT ON category
BEGIN
    INSERT INTO undoable VALUES(NULL, 0, 'DELETE FROM category ' ||
                                         'WHERE docid = ' || 
                                         LAST_INSERT_ROWID() );
END;

CREATE TRIGGER undo_delete_category DELETE ON category
  BEGIN
    INSERT INTO undoable VALUES(NULL, 0,
								'INSERT INTO category
								 VALUES (' ||
								 old.catid || ', ' ||
								 QUOTE(old.catname) || ' ,' ||
								 QUOTE(old.catdesc)  || ', ' ||
								 old.parent || ', ' ||
								 QUOTE(old.created_date) || ', ' ||
								 QUOTE(old.modified_date) || ')' );

CREATE TRIGGER undo_insert_document INSERTO ON document
BEGIN
END;

CREATE TRIGGER undo_delete_document DELETE ON document
BEGIN
END;

CREATE TRIGGER undo_insert_code INSERT ON code
BEGIN
END;

CREATE TRIGGER undo_delete_code DELETE ON code
BEGIN
END;

CREATE TRIGGER undo_insert_docmeta INSERT ON docmeta
BEGIN
END;

CREATE TRIGGER undo_delete_docmeta DELETE ON docmeta
BEGIN
END;

CREATE TRIGGER undo_update_category_structure 
UPDATE ON category_structure
BEGIN
END;
SCHEMA_UNDO

SCHEMA_INDEXES =

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

<<'SCHEMA_INDEXES'

CREATE INDEX document_idx
ON document(doctitle);

CREATE INDEX code_idx 
ON code(docid, catid);

CREATE INDEX docmeta_idx
ON docmeta(metaname, docid);

SCHEMA_INDEXES

RINDEX_SEARCH_MODEL_QUERY =

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

<<'RINDEX_SEARCH_MODEL_QUERY'
SELECT document.docid AS docid, document.doctitle AS doctitle,
MAX( 0, code.offset - ?)
AS start_at,
SUBSTR(document.doctext, 
  MAX( 0, code.offset - ?) + 1,
  MIN( code.length + ( ? * 2 ),
       LENGTH(document.doctext) - MAX(1, code.offset - ?) - 1 ) )
AS snip
FROM document, code
WHERE code.docid = document.docid
AND code.catid IN (
  SELECT catid
  FROM category
  WHERE parent = ?
  AND LOWER(category.catname) LIKE ?)
ORDER BY code.catid, code.offset
RINDEX_SEARCH_MODEL_QUERY