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(); }