public static BingoIndexData CreateIndexData(int spid, SqlConnection conn, string bingo_schema,
string table, string id_column, string data_column, bool reaction)
{
lock (index_data_list_lock)
{
BingoIndexID id = new BingoIndexID(conn, table);
_DropIndexData(conn, bingo_schema, id, false);
BingoIndexData data;
if (reaction)
{
data = new RingoIndexData(id, id_column, data_column, bingo_schema);
}
else
{
data = new MangoIndexData(id, id_column, data_column, bingo_schema);
}
BingoSqlUtils.ExecNonQuery(conn,
"INSERT INTO {0} VALUES({1}, {2}, '{3}', '{4}', '{5}', '{6}')",
_contextTable(bingo_schema), id.table_id, id.database_id, id.FullTableName(conn),
id_column, data_column,
reaction ? "reaction" : "molecule");
data.CreateTables(conn);
_AddIndexDataToList(spid, data);
BingoLog.logMessage("Bingo index for table {0} has been initialized", id.FullTableName(conn));
return(data);
}
}
public void CreateTriggers(SqlConnection conn)
{
string cur_db_name = null;
try
{
cur_db_name = BingoSqlUtils.ExecStringQuery(conn, "SELECT DB_NAME()");
BingoSqlUtils.ExecNonQueryNoThrow(conn, "USE {0}", id.DatabaseName(conn));
string bingo_db_schema = cur_db_name + "." + bingo_schema;
string full_name = id.FullTableName(conn);
object[] trigger_params = new object[] { null,
full_name, id_column, data_column, bingo_db_schema, id.table_id, id.database_id };
trigger_params[0] = GetTriggerName("Insert", conn, cur_db_name);
string insert_trigger = String.Format(resource.OnInsertTrigger, trigger_params);
BingoSqlUtils.ExecNonQuery(conn, "{0}", insert_trigger);
trigger_params[0] = GetTriggerName("Delete", conn, cur_db_name);
string delete_trigger = String.Format(resource.OnDeleteTrigger, trigger_params);
BingoSqlUtils.ExecNonQuery(conn, "{0}", delete_trigger);
trigger_params[0] = GetTriggerName("Update", conn, cur_db_name);
string update_trigger = String.Format(resource.OnUpdateTrigger, trigger_params);
BingoSqlUtils.ExecNonQuery(conn, "{0}", update_trigger);
}
finally
{
if (cur_db_name != null)
{
BingoSqlUtils.ExecNonQueryNoThrow(conn, "USE {0}", cur_db_name);
}
}
}
public override void createIndices(SqlConnection conn)
{
BingoSqlUtils.ExecNonQuery(conn,
"ALTER TABLE {0} ADD PRIMARY KEY (storage_id)", shadowTable);
BingoSqlUtils.ExecNonQuery(conn,
"CREATE UNIQUE INDEX id ON {0}(id)", shadowTable);
BingoSqlUtils.ExecNonQuery(conn,
"CREATE INDEX gross ON {0}(gross)", shadowTable);
BingoSqlUtils.ExecNonQuery(conn,
"CREATE INDEX mass ON {0}(mass)", shadowTable);
for (int i = 0; i < MangoIndex.COUNTED_ELEMENTS_COUNT; i++)
{
BingoSqlUtils.ExecNonQuery(conn,
"CREATE INDEX {1} ON {0}({1})", shadowTable,
BingoCore.mangoGetCountedElementName(i));
}
// Create indices for components shadow table
BingoSqlUtils.ExecNonQuery(conn,
"CREATE INDEX id ON {0}(id)", componentsTable);
BingoSqlUtils.ExecNonQuery(conn,
"CREATE INDEX hash ON {0}(hash)", componentsTable);
BingoSqlUtils.ExecNonQuery(conn,
"CREATE INDEX count ON {0}(hash, count)", componentsTable);
}
public override void createIndices(SqlConnection conn)
{
BingoSqlUtils.ExecNonQuery(conn,
"ALTER TABLE {0} ADD PRIMARY KEY (storage_id)", shadowTable);
BingoSqlUtils.ExecNonQuery(conn,
"CREATE UNIQUE INDEX id ON {0}(id)", shadowTable);
BingoSqlUtils.ExecNonQuery(conn,
"CREATE INDEX hash ON {0}(hash)", shadowTable);
}
public override void deleteRecordById(int id, SqlConnection conn)
{
lock (_sync_object)
{
base.deleteRecordById(id, conn);
BingoSqlUtils.ExecNonQuery(conn, "DELETE from {0} where id={1}",
componentsTable, id);
}
}
public void createIndices(SqlConnection conn)
{
BingoSqlUtils.ExecNonQuery(conn, "ALTER TABLE {0} ADD PRIMARY KEY (part)",
_index_data.fingerprintsTable);
BingoSqlUtils.ExecNonQuery(conn, "ALTER TABLE {0} ADD PRIMARY KEY (part, bit)",
_index_data.fingerprintBitsTable);
BingoSqlUtils.ExecNonQuery(conn, "CREATE INDEX part ON {0}(part)",
_index_data.fingerprintBitsTable);
}
public override void CreateTables(SqlConnection conn)
{
base.CreateTables(conn);
StringBuilder cmd = new StringBuilder();
// Create shadow table
cmd.AppendFormat(@"CREATE TABLE {0}
(id int not null, storage_id int not null, crf varbinary(max), hash int not null)", shadowTable);
BingoSqlUtils.ExecNonQuery(conn, cmd.ToString());
}
public void createTables(SqlConnection conn)
{
BingoSqlUtils.ExecNonQuery(conn,
@"CREATE TABLE {0} (
[id] SMALLINT PRIMARY KEY,
[first_index] INT,
[count] INT,
[offsets] VARBINARY(MAX),
[lengths] VARBINARY(MAX),
[data] VARBINARY(MAX))",
_index_data.storageTable);
}
public virtual void deleteRecordById(int id, SqlConnection conn)
{
int?storage_id = getStorageIdById(conn, id);
if (!storage_id.HasValue)
{
// Such molecule wasn't added to the molecule
// index because it might be invalid
return;
}
storage.deleteRecord(storage_id.Value, conn);
BingoSqlUtils.ExecNonQuery(conn, "DELETE from {0} where id={1}",
shadowTable, id);
}
public void createTables(SqlConnection conn)
{
BingoSqlUtils.ExecNonQuery(conn,
@"CREATE TABLE {0} (
[part] INTEGER not null,
[used] INTEGER not null,
[mapping] VARBINARY(MAX),
[counters] VARBINARY(MAX))",
_index_data.fingerprintsTable);
BingoSqlUtils.ExecNonQuery(conn,
@"CREATE TABLE {0} (
[part] INTEGER not null,
[bit] INTEGER not null,
[bits_chunk] BINARY({1}))",
_index_data.fingerprintBitsTable, _chunk_bytes);
}
public override void CreateTables(SqlConnection conn)
{
base.CreateTables(conn);
StringBuilder cmd = new StringBuilder();
// Create shadow table
cmd.AppendFormat(@"CREATE TABLE {0}
(id int not null, storage_id int not null, gross VARCHAR(500), cmf varbinary(max),
xyz varbinary(max), mass real not null, fragments int not null", shadowTable);
for (int i = 0; i < MangoIndex.COUNTED_ELEMENTS_COUNT; i++)
{
cmd.AppendFormat(", {0} int not null", BingoCore.mangoGetCountedElementName(i));
}
cmd.Append(")");
BingoSqlUtils.ExecNonQuery(conn, cmd.ToString());
// Create shadow table for molecule components
BingoSqlUtils.ExecNonQuery(conn,
"CREATE TABLE {0} (id int not null, hash int not null, count int not null, primary key(id, hash))",
componentsTable);
}
private void _flushBlock(Block block, SqlConnection conn)
{
BingoTimer timer = new BingoTimer("fingerprints.flush");
if (block.part == -1)
{
// Add new block
int?max_id = BingoSqlUtils.ExecIntQuery(conn,
"SELECT MAX(part) from {0}", _index_data.fingerprintsTable);
if (max_id == null)
{
max_id = 0;
}
block.part = max_id.Value + 1;
BingoSqlUtils.ExecNonQuery(conn, "INSERT INTO {0} values ({1}, 0, null, null)",
_index_data.fingerprintsTable, block.part);
}
BingoLog.logMessage("Flushing fingerprints block {0}...", block.part);
// Update used column and counters column
string update_command_text = String.Format(@"UPDATE {0} SET used = @used,
counters = @counters, mapping = @mapping
where part = {1}", _index_data.fingerprintsTable, block.part);
using (SqlCommand command = new SqlCommand(update_command_text, conn))
{
command.CommandTimeout = 3600;
command.Parameters.AddWithValue("@used", block.indices.Count);
byte[] countes_bytes = new byte[8 * _fp_bytes * sizeof(int)];
Buffer.BlockCopy(block.counters, 0, countes_bytes, 0, countes_bytes.Length);
SqlBinary countes = new SqlBinary(countes_bytes);
command.Parameters.AddWithValue("@counters", countes);
byte[] mapping_bytes = new byte[block.indices.Count * sizeof(int)];
Buffer.BlockCopy(block.indices.ToArray(), 0, mapping_bytes, 0, mapping_bytes.Length);
SqlBinary mapping = new SqlBinary(mapping_bytes);
command.Parameters.AddWithValue("@mapping", mapping);
command.ExecuteNonQuery();
}
// Update bit chunks
BingoSqlUtils.ExecNonQuery(conn, "DELETE FROM {0} WHERE part = {1}",
_index_data.fingerprintBitsTable, block.part);
string update_bits_text = String.Format(@"INSERT INTO {0} VALUES ({1}, @bit, @bit_chunk)",
_index_data.fingerprintBitsTable, block.part);
using (SqlCommand command = new SqlCommand(update_bits_text, conn))
{
command.CommandTimeout = 3600;
command.Parameters.Add("@bit", SqlDbType.Int);
command.Parameters.Add("@bit_chunk", SqlDbType.Binary);
byte[] chunk = new byte[_chunk_bytes];
for (int i = 0; i < 8 * _fp_bytes; i++)
{
command.Parameters["@bit"].Value = i;
Buffer.BlockCopy(block.bits[i], 0, chunk, 0, chunk.Length);
SqlBinary sql_chunk = new SqlBinary(chunk);
command.Parameters["@bit_chunk"].Value = sql_chunk;
command.ExecuteNonQuery();
}
}
block.pending = false;
block.bits = null;
block.indices = null;
BingoLog.logMessage(" Done.");
timer.end();
}