void MysqlUpgraderFrom_1_16_To_1_17::upgradeFeatureDbi(U2OpStatus &os, MysqlDbRef *dbRef) const {
U2OpStatus2Log nonCriticalOs;
U2SqlQuery("DROP INDEX FeatureRootIndex ON Feature", dbRef, nonCriticalOs).execute();
U2SqlQuery("CREATE INDEX FeatureRootIndex ON Feature(root, class)", dbRef, os).execute();
CHECK_OP(os, );
}
void MysqlAttributeDbi::initSqlSchema( U2OpStatus &os ) {
MysqlTransaction t(db, os);
Q_UNUSED(t);
// object attribute main table
// object -> object id this attribute is for
// child -> optional object id in case if this attribute shows relation between 2 objects
// otype, ctype -> object and child types
// oextra, cextra -> object and child db extra
// version -> object version is attribute is valid for
// name -> name of the attribute
U2SqlQuery( "CREATE TABLE Attribute (id BIGINT PRIMARY KEY AUTO_INCREMENT, type INTEGER NOT NULL, "
"object BIGINT, child BIGINT, otype INTEGER NOT NULL, ctype INTEGER, oextra LONGBLOB NOT NULL, "
"cextra LONGBLOB, version BIGINT NOT NULL, name LONGTEXT NOT NULL, "
"FOREIGN KEY(object) REFERENCES Object(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8", db, os).execute();
//TODO: check if index is efficient for getting attribute for specific object
U2SqlQuery("CREATE INDEX Attribute_object on Attribute(object)" , db, os).execute();
U2SqlQuery("CREATE TABLE IntegerAttribute (attribute BIGINT, value BIGINT NOT NULL, "
" FOREIGN KEY(attribute) REFERENCES Attribute(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8" , db, os).execute();
U2SqlQuery("CREATE INDEX IntegerAttribute_attribute on IntegerAttribute(attribute)", db, os).execute();
U2SqlQuery("CREATE TABLE RealAttribute (attribute BIGINT, value DOUBLE NOT NULL, "
" FOREIGN KEY(attribute) REFERENCES Attribute(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8", db, os).execute();
U2SqlQuery("CREATE INDEX RealAttribute_attribute on RealAttribute(attribute)" , db, os).execute();
U2SqlQuery("CREATE TABLE StringAttribute (attribute BIGINT, value LONGTEXT NOT NULL, "
" FOREIGN KEY(attribute) REFERENCES Attribute(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8" , db, os).execute();
U2SqlQuery("CREATE INDEX StringAttribute_attribute on StringAttribute(attribute)" , db, os).execute();
U2SqlQuery("CREATE TABLE ByteArrayAttribute (attribute BIGINT, value LONGBLOB NOT NULL, "
" FOREIGN KEY(attribute) REFERENCES Attribute(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8" , db, os).execute();
U2SqlQuery("CREATE INDEX ByteArrayAttribute_attribute on ByteArrayAttribute(attribute)" , db, os).execute();
}
void MysqlObjectRelationsDbi::initSqlSchema( U2OpStatus &os ) {
MysqlTransaction t(db, os);
Q_UNUSED(t);
U2SqlQuery( "CREATE TABLE ObjectRelation (object BIGINT NOT NULL, "
"reference BIGINT NOT NULL, role INTEGER NOT NULL, "
"PRIMARY KEY(object, reference), "
"FOREIGN KEY(object) REFERENCES Object(id) ON DELETE CASCADE,"
"FOREIGN KEY(reference) REFERENCES Object(id) ON DELETE CASCADE) "
"ENGINE=InnoDB DEFAULT CHARSET=utf8", db, os ).execute( );
CHECK_OP( os, );
U2SqlQuery( "CREATE INDEX ObjectRelationRole ON ObjectRelation(role)", db, os ).execute( );
}
void MysqlUpgraderFrom_1_15_To_1_16::upgradeFeatureDbi(U2OpStatus &os, MysqlDbRef *dbRef) const {
const bool featureClassFieldExist = (1 == U2SqlQuery(QString("SELECT count(*) FROM information_schema.COLUMNS WHERE "
"TABLE_SCHEMA = '%1' AND TABLE_NAME = 'Feature' "
"AND COLUMN_NAME = 'class'").
arg(dbRef->handle.databaseName()), dbRef, os).selectInt64());
CHECK_OP(os, );
CHECK(!featureClassFieldExist, );
U2SqlQuery("ALTER TABLE Feature CHANGE type class INTEGER NOT NULL", dbRef, os).execute();
CHECK_OP(os, );
U2SqlQuery("ALTER TABLE Feature ADD type INTEGER NOT NULL DEFAULT 0", dbRef, os).execute();
if (os.isCoR()) {
U2OpStatusImpl innerOs;
U2SqlQuery("ALTER TABLE Feature CHANGE class type INTEGER NOT NULL", dbRef, innerOs).execute();
return;
}
U2FeatureDbi *featureDbi = dbi->getFeatureDbi();
SAFE_POINT_EXT(NULL != featureDbi, os.setError(L10N::nullPointerError("feature dbi")), );
U2DbiIterator<U2Feature> *dbIterator = featureDbi->getFeatures(FeatureQuery(), os);
CHECK_OP(os, );
while (dbIterator->hasNext()) {
U2Feature feature = dbIterator->next();
U2FeatureType guessedFeatureType = U2FeatureTypes::MiscFeature;
GBFeatureKey gbFeatureKey = GBFeatureUtils::getKey(feature.name);
if (GBFeatureKey_UNKNOWN != gbFeatureKey) {
guessedFeatureType = GBFeatureUtils::getKeyInfo(GBFeatureUtils::getKey(feature.name)).type;
}
U2FeatureUtils::updateFeatureType(feature.id, guessedFeatureType, dbi->getDbiRef(), os);
CHECK_OP(os, );
}
U2SqlQuery("ALTER TABLE Feature ALTER type DROP DEFAULT", dbRef, os).execute();
CHECK_OP(os, );
}
void MysqlMsaDbi::initSqlSchema(U2OpStatus& os) {
MysqlTransaction t(db, os);
Q_UNUSED(t);
// MSA object
U2SqlQuery("CREATE TABLE Msa (object BIGINT PRIMARY KEY, length BIGINT NOT NULL, "
"alphabet TEXT NOT NULL, numOfRows INTEGER NOT NULL, "
"FOREIGN KEY(object) REFERENCES Object(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8", db, os).execute();
CHECK_OP(os, );
// MSA object row
// msa - msa object id
// rowId - id of the row in the msa
// sequence - sequence object id
// pos - positional number of a row in the msa (initially, equals 'id', but can be changed, e.g. in GUI by moving rows)
// gstart - offset of the first element in the sequence
// gend - offset of the last element in the sequence (non-inclusive)
// length - sequence and gaps length (trailing gap are not taken into account)
U2SqlQuery("CREATE TABLE MsaRow (msa BIGINT NOT NULL, "
"rowId BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, "
"sequence BIGINT NOT NULL, pos BIGINT NOT NULL, gstart BIGINT NOT NULL, "
"gend BIGINT NOT NULL, length BIGINT NOT NULL, "
"FOREIGN KEY(msa) REFERENCES Msa(object) ON DELETE CASCADE, "
"FOREIGN KEY(sequence) REFERENCES Sequence(object) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8", db, os).execute();
CHECK_OP(os, );
U2SqlQuery("CREATE INDEX MsaRow_msa_rowId ON MsaRow(msa, rowId)", db, os).execute();
CHECK_OP(os, );
U2SqlQuery("CREATE INDEX MsaRow_length ON MsaRow(length)", db, os).execute();
CHECK_OP(os, );
// Gap info for a MSA row:
// msa - msa object id
// rowId - id of the row in the msa
// gapStart - start of the gap, the coordinate is relative to the gstart coordinate of the row
// gapEnd - end of the gap, the coordinate is relative to the gstart coordinate of the row (non-inclusive)
// Note! there is invariant: gend - gstart (of the row) == gapEnd - gapStart
U2SqlQuery("CREATE TABLE MsaRowGap (msa BIGINT NOT NULL, rowId BIGINT NOT NULL, "
"gapStart BIGINT NOT NULL, gapEnd BIGINT NOT NULL, "
"FOREIGN KEY(rowId) REFERENCES MsaRow(rowId) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8", db, os).execute();
CHECK_OP(os, );
U2SqlQuery("CREATE INDEX MsaRowGap_msa_rowId ON MsaRowGap(msa, rowId)", db, os).execute();
CHECK_OP(os, );
}
QStringList MysqlObjectDbi::getFolders(U2OpStatus& os) {
static const QString queryString = "SELECT path FROM Folder ORDER BY BINARY(path)";
return U2SqlQuery(queryString, db, os).selectStrings();
}
QList<U2DataId> MysqlObjectDbi::getObjects(qint64 offset, qint64 count, U2OpStatus& os) {
static const QString queryString = "SELECT id, type, '' FROM Object WHERE " + TOP_LEVEL_FILTER;
return U2SqlQuery(queryString, offset, count, db, os).selectDataIdsExt();
}
qint64 MysqlObjectDbi::countObjects(U2OpStatus& os) {
static const QString queryString = "COUNT (*) FROM Object WHERE " + TOP_LEVEL_FILTER;
return U2SqlQuery(queryString, db, os).selectInt64();
}
/** Returns all attribute names available in the database */
QStringList MysqlAttributeDbi::getAvailableAttributeNames(U2OpStatus& os) {
static const QString queryString = "SELECT DISTINCT name FROM Attribute";
return U2SqlQuery(queryString, db, os).selectStrings();
}
