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 byte[] getXyz(int storage_id, SqlConnection conn)
{
object ret = BingoSqlUtils.ExecObjQuery(conn, "SELECT xyz from {0} where storage_id={1}",
shadowTable, storage_id);
return((byte[])ret);
}
public static void logMessageWithThrow(string message, params object[] args)
{
lock (sync_obj)
{
try
{
using (StreamWriter sw = System.IO.File.AppendText(getLogFileName()))
{
BingoSqlUtils._ForceGC();
string logLine = String.Format(
"{0:G}: {1}", DateTime.Now, String.Format(message, args));
sw.WriteLine(logLine);
double size_mb = GC.GetTotalMemory(false) / 1024 / 1024.0;
int processId = Process.GetCurrentProcess().Id;
int threadId = Thread.CurrentThread.ManagedThreadId;
Process proc = Process.GetCurrentProcess();
double proc_size_mb = proc.PrivateMemorySize64 / 1024 / 1024.0;
sw.WriteLine(String.Format(" GC Memory: {0:0.00} Mb. Process id: {1}. Thread id: {2}. AppDomain: {3}. PrivateMemory: {4:0.00} Mb",
size_mb, processId, threadId, AppDomain.CurrentDomain.FriendlyName, proc_size_mb));
}
}
catch
{
}
}
}
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 validate(SqlConnection conn)
{
if (total_items >= 0)
{
return; // Index already loaded
}
lock (_sync_object)
{
if (total_items >= 0)
{
return; // Index already loaded
}
int?ret = BingoSqlUtils.ExecIntQuery(conn, "select max(id) from {0}",
_index_data.storageTable);
if (ret.HasValue)
{
while (_blocks.Count <= ret.Value)
{
_blocks.Add(null);
}
}
int?sum = BingoSqlUtils.ExecIntQuery(conn, "select sum(count) from {0}",
_index_data.storageTable);
if (!sum.HasValue)
{
total_items = 0;
}
else
{
total_items = sum.Value;
}
}
}
public string FullTableName(SqlConnection connection)
{
return(BingoSqlUtils.ExecStringQuery(connection,
@"SELECT QUOTENAME(DB_NAME({0})) + N'.' +
QUOTENAME(OBJECT_SCHEMA_NAME({1}, {0})) + N'.' +
QUOTENAME(OBJECT_NAME({1}, {0}))", database_id, table_id));
}
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 DropTables(SqlConnection conn)
{
base.DropTables(conn);
BingoSqlUtils.ExecNonQueryNoThrow(conn, "DROP TABLE " + shadowTable);
BingoSqlUtils.ExecNonQueryNoThrow(conn, "DROP TABLE " + componentsTable);
}
private static void _DropIndexData(SqlConnection conn, string bingo_schema,
BingoIndexID id, bool throw_if_not_exists)
{
BingoIndexData data = _extractIndexData(conn, bingo_schema, id, throw_if_not_exists);
if (data != null)
{
data.DropTables(conn);
data.DropTriggers(conn);
}
BingoSqlUtils.ExecNonQueryNoThrow(conn, "DELETE FROM {0} WHERE obj_id = '{1}'",
_contextTable(bingo_schema), id.table_id);
for (int i = index_data_list.Count - 1; i >= 0; i--)
{
BingoIndexDataRefs refs = (BingoIndexDataRefs)index_data_list[i];
if (refs.index_data.id.Equals(id))
{
index_data_list.RemoveAt(i);
BingoLog.logMessage("Sessions for table {0} have been released", id.InformationName(conn));
}
}
if (data != null)
{
BingoLog.logMessage("Bingo index for table {0} has been dropped", id.InformationName(conn));
}
}
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 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 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 void init(SqlConnection conn)
{
// Check if blocks have already been loaded
if (_all_blocks.Count != 0)
{
return;
}
lock (_sync_object)
{
// Check again
if (_all_blocks.Count != 0)
{
return;
}
int?max_part = BingoSqlUtils.ExecIntQuery(conn,
"SELECT MAX(part) from {0}", _index_data.fingerprintsTable);
if (!max_part.HasValue)
{
return;
}
using (SqlCommand command =
new SqlCommand("SELECT [part], [used], [counters], [mapping] from " +
_index_data.fingerprintsTable + " ORDER BY [part]", conn))
{
command.CommandTimeout = 3600;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Block new_block = new Block(false, _fp_bytes);
new_block.part = Convert.ToInt32(reader[0]);
int used = Convert.ToInt32(reader[1]);
byte[] counters = (byte[])reader[2];
byte[] mapping = (byte[])reader[3];
// Copy mapping
int[] data = new int[used];
Buffer.BlockCopy(mapping, 0, data, 0, mapping.Length);
new_block.indices.AddRange(data);
new_block.validateMinMax();
// Copy counters
Buffer.BlockCopy(counters, 0, new_block.counters, 0, counters.Length);
_all_blocks.Add(new_block);
}
}
}
}
}
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 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 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);
}
private string name; // Name for logging
// Detect database, schema, and table; some from table_name, some from SQL connection
public BingoIndexID(SqlConnection connection, string table_name)
{
table_id = BingoSqlUtils.GetTableObjectID(connection, table_name);
string database = null, schema = null, table = null;
parseTableName(table_name, ref database, ref schema, ref table);
if (database == null)
{
database = connection.Database;
}
database_id = BingoSqlUtils.GetDatabaseID(connection, database);
name = table_name;
}
private void _getBitChunk(Block block, int bit_index, SqlConnection conn, ref byte[] chunk)
{
BingoTimer timer = new BingoTimer("fingerprints.read");
if (block.bits != null)
{
Buffer.BlockCopy(block.bits[bit_index], 0, chunk, 0, chunk.Length);
}
else
{
chunk = (byte[])BingoSqlUtils.ExecObjQuery(conn,
"SELECT bits_chunk from {0} where part = {1} and bit = {2}",
_index_data.fingerprintBitsTable, block.part, bit_index);
}
timer.end();
}
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 void DropTriggers(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));
BingoSqlUtils.ExecNonQueryNoThrow(conn, "DROP TRIGGER {0}", GetTriggerName("Insert", conn, cur_db_name));
BingoSqlUtils.ExecNonQueryNoThrow(conn, "DROP TRIGGER {0}", GetTriggerName("Delete", conn, cur_db_name));
BingoSqlUtils.ExecNonQueryNoThrow(conn, "DROP TRIGGER {0}", GetTriggerName("Update", conn, cur_db_name));
}
finally
{
if (cur_db_name != null)
{
BingoSqlUtils.ExecNonQueryNoThrow(conn, "USE {0}", cur_db_name);
}
}
}
public static void logMessageWithThrow(string message, params object[] args)
{
lock (sync_obj)
{
try
{
using (StreamWriter sw = System.IO.File.AppendText(getLogFileName()))
{
BingoSqlUtils._ForceGC();
string logLine = String.Format(
"{0:G}: {1}", DateTime.Now, String.Format(message, args));
sw.WriteLine(logLine);
double size_mb = GC.GetTotalMemory(false) / 1000 / 1000.0;
sw.WriteLine(String.Format("GC Memory: {0:0.00} Mb", size_mb));
}
}
catch
{
}
}
}
public static void DropAllIndices(SqlConnection conn, string bingo_schema, bool only_invalid)
{
List <TableWithId> tables = new List <TableWithId>();
using (SqlCommand cmd = new SqlCommand(String.Format(
"SELECT full_table_name, obj_id FROM {0}",
_contextTable(bingo_schema)), conn))
{
cmd.CommandTimeout = 3600;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string full_table_name = Convert.ToString(reader[0]);
int table_id = Convert.ToInt32(reader[1]);
tables.Add(new TableWithId()
{
table_name = full_table_name, id = table_id
});
}
}
}
foreach (TableWithId table in tables)
{
if (only_invalid)
{
object ret = BingoSqlUtils.ExecObjQuery(conn, "SELECT OBJECT_NAME({0})", table.id);
if (ret != null)
{
continue;
}
}
DropIndexData(conn, bingo_schema, table.table_name, false);
}
}
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);
}
public int?getStorageIdById(SqlConnection conn, int id)
{
return(BingoSqlUtils.ExecIntQuery(conn, "SELECT storage_id from {0} where id={1}",
shadowTable, id));
}
public void dropTables(SqlConnection conn)
{
BingoSqlUtils.ExecNonQueryNoThrow(conn, "DROP TABLE {0}", _index_data.fingerprintsTable);
BingoSqlUtils.ExecNonQueryNoThrow(conn, "DROP TABLE {0}", _index_data.fingerprintBitsTable);
}
private void _flushBlock(SqlConnection conn, _Block b)
{
BingoLog.logMessage("Flushing storage block {0}", b.block_index);
BingoSqlUtils.ExecNonQueryNoThrow(conn, "DELETE FROM {0} WHERE id={1}",
_index_data.storageTable, b.block_index);
string text = String.Format(@"INSERT INTO {0}
([id], [first_index], [count], [offsets], [lengths], [data])
VALUES ({1}, {2}, {3}, @offsets, @lengths, @data)",
_index_data.storageTable, b.block_index, b.first_index, b.end_index - b.first_index);
using (SqlCommand command = new SqlCommand(text, conn))
{
command.CommandTimeout = 3600;
byte[] data;
if (b.pending_data != null)
{
data = b.pending_data.ToArray();
}
else
{
data = b.data;
}
SqlBinary binary_data = new SqlBinary(data);
command.Parameters.AddWithValue("@data", binary_data);
ICollection <int> offsets;
if (b.pending_offsets != null)
{
offsets = b.pending_offsets;
}
else
{
offsets = b.offsets;
}
MemoryStream mem_stream = new MemoryStream(offsets.Count * 4);
BinaryWriter writer = new BinaryWriter(mem_stream);
foreach (int offset in offsets)
{
writer.Write(offset);
}
byte[] buffer = mem_stream.GetBuffer();
command.Parameters.AddWithValue("@offsets", buffer);
ICollection <short> lengths;
if (b.pending_lengths != null)
{
lengths = b.pending_lengths;
}
else
{
lengths = b.lengths;
}
mem_stream = new MemoryStream(offsets.Count * 2);
writer = new BinaryWriter(mem_stream);
foreach (short length in lengths)
{
writer.Write(length);
}
buffer = mem_stream.GetBuffer();
command.Parameters.AddWithValue("@lengths", buffer);
command.ExecuteNonQuery();
}
_convertPendingBlockToNormal(b);
}
public void dropTables(SqlConnection conn)
{
BingoSqlUtils.ExecNonQueryNoThrow(conn, "DROP TABLE {0}", _index_data.storageTable);
}
public string SchemaName(SqlConnection connection)
{
return(BingoSqlUtils.ExecStringQuery(connection,
@"SELECT QUOTENAME(OBJECT_SCHEMA_NAME({0}, {1}))", table_id, database_id));
}
public static void _remove(SqlConnection connection, string bingo_schema, string name, int id, string table)
{
BingoSqlUtils.ExecNonQueryNoThrow(connection, "DELETE FROM {0}.{1} where name='{2}' and n={3}",
bingo_schema, table, name, id);
}
public string DatabaseName(SqlConnection connection)
{
return(BingoSqlUtils.ExecStringQuery(connection,
@"SELECT QUOTENAME(DB_NAME({0}))", database_id));
}
