void SQLiteFeatureDbi::initSqlSchema(U2OpStatus& os) {
//nameHash is used for better indexing
SQLiteWriteQuery("CREATE TABLE Feature (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
"class INTEGER NOT NULL, type INTEGER NOT NULL, parent INTEGER, root INTEGER, nameHash INTEGER, name TEXT, "
"sequence INTEGER, strand INTEGER NOT NULL, "
"start INTEGER NOT NULL DEFAULT 0, len INTEGER NOT NULL DEFAULT 0)", db, os).execute();
SQLiteWriteQuery("CREATE TABLE FeatureKey (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
"feature INTEGER NOT NULL, name TEXT NOT NULL, value TEXT NOT NULL, "
"FOREIGN KEY(feature) REFERENCES Feature(id) ON DELETE CASCADE)", db, os).execute();
// annotation table object
SQLiteWriteQuery("CREATE TABLE AnnotationTable (object INTEGER PRIMARY KEY, rootId INTEGER NOT NULL, "
"FOREIGN KEY(object) REFERENCES Object(id) ON DELETE CASCADE, "
"FOREIGN KEY(rootId) REFERENCES Feature(id) ON DELETE CASCADE)", db, os).execute();
//Feature index
SQLiteWriteQuery("CREATE VIRTUAL TABLE FeatureLocationRTreeIndex USING rtree_i32(id, start, end)",
db, os).execute();
SQLiteWriteQuery("CREATE INDEX IF NOT EXISTS FeatureRootIndex ON Feature(root, class)" ,db, os).execute();
SQLiteWriteQuery("CREATE INDEX IF NOT EXISTS FeatureParentIndex ON Feature(parent)", db, os).execute();
SQLiteWriteQuery("CREATE INDEX IF NOT EXISTS FeatureNameIndex ON Feature(root, nameHash)", db, os).execute();
//FeatureKey index
SQLiteWriteQuery("CREATE INDEX IF NOT EXISTS FeatureKeyIndex ON FeatureKey(feature)", db, os).execute();
//Deletion triggers
SQLiteWriteQuery(getQueryForFeatureDeletionTrigger(), db, os).execute();
}
void SQLiteSequenceDbi::initSqlSchema(U2OpStatus& os) {
// sequence object
SQLiteWriteQuery("CREATE TABLE Sequence (object INTEGER PRIMARY KEY, length INTEGER NOT NULL DEFAULT 0, alphabet TEXT NOT NULL, circular INTEGER NOT NULL DEFAULT 0, "
"FOREIGN KEY(object) REFERENCES Object(id) ON DELETE CASCADE)", db, os).execute();
// part of the sequence, starting with 'sstart'(inclusive) and ending at 'send'(not inclusive)
SQLiteWriteQuery("CREATE TABLE SequenceData (sequence INTEGER, sstart INTEGER NOT NULL, send INTEGER NOT NULL, data BLOB NOT NULL, "
"PRIMARY KEY (sequence, sstart, send), "
"FOREIGN KEY(sequence) REFERENCES Sequence(object) ON DELETE CASCADE)", db, os).execute();
SQLiteWriteQuery("CREATE INDEX SequenceData_sequence_send on SequenceData(sequence, send)", db, os).execute();
}
void RTreeAssemblyAdapter::createReadsTables(U2OpStatus& os) {
// name - name hash
// flags - flags
// mq - mapping quality
// data - compressed name/sequence/cigar/mapping
static QString q1 = "CREATE TABLE %1 (id INTEGER PRIMARY KEY AUTOINCREMENT, name INTEGER NOT NULL, "
"flags INTEGER NOT NULL, mq INTEGER NOT NULL, data BLOB NOT NULL)";
// gstart, gend - start and end read position
// prow1, prow2 - packed view row. prow1 always the same as prow2
static QString q2 = "CREATE VIRTUAL TABLE %1 USING rtree_i32(id, gstart, gend, prow1, prow2)";
SQLiteWriteQuery(q1.arg(readsTable), db, os).execute();
if (os.hasError()) {
return;
}
SQLiteWriteQuery(q2.arg(indexTable), db, os).execute();
if (os.hasError()) {
coreLog.error(U2DbiL10n::tr("Error during RTree index creation: %1! Check if SQLite library has RTree index support!").arg(os.getError()));
}
}
void RTreeAssemblyAdapter::createReadsIndexes(U2OpStatus& os) {
static QString q = "CREATE INDEX %1_name ON %1(name)";
SQLiteWriteQuery(q.arg(readsTable), db, os).execute();
}
