/// <summary>
/// Inserts an index into the database.
/// </summary>
/// <param name="index">A BackupIndex object to be added to the Backup_Indexes table.</param>
/// <param name="blocks">A list of Block objects to be added to the Block_Storage table</param>
public void InsertIndex(BackupIndex index, List<Block> blocks)
{
long indexID = 0;
long indexIDCount = 0;
// Insert index into Backup_Indexes table
string backupIndexSql = "INSERT INTO Backup_Indexes (id, source_guid, source_path, first_block_offset, size, date_of_backup, backup_level) VALUES (@pID, @pSourceGUID, @pSourcePath, @pFirstBlockOffset, @pSize, @pDateOfBackup, @pBackupLevel)";
SQLiteCommand backupIndexCmd = new SQLiteCommand(backupIndexSql, conn);
//Get ID for index
//Determine if there are any entries for the given guid
string indexInitialIDQuery = "SELECT COUNT(id) FROM Backup_Indexes WHERE source_guid = @pSourceGUID";
SQLiteCommand indexInitialIDCmd = new SQLiteCommand(indexInitialIDQuery, conn);
//Get previous row ID for given guid
string indexPreviousIDQuery = "SELECT max(id) FROM Backup_Indexes WHERE source_guid = @pSourceGUID";
SQLiteCommand indexPreviousIDCmd = new SQLiteCommand(indexPreviousIDQuery, conn);
//SQLite likes dates and times in a certain format.
//string currentTimeString = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
indexInitialIDCmd.Parameters.Add(new SQLiteParameter("@pSourceGUID", index.sourceGUID));
indexPreviousIDCmd.Parameters.Add(new SQLiteParameter("@pSourceGUID", index.sourceGUID));
backupIndexCmd.Parameters.Add(new SQLiteParameter("@pSourceGUID", index.sourceGUID));
backupIndexCmd.Parameters.Add(new SQLiteParameter("@pSourcePath", index.sourcePath));
backupIndexCmd.Parameters.Add(new SQLiteParameter("@pFirstBlockOffset", index.firstBlockOffset));
backupIndexCmd.Parameters.Add(new SQLiteParameter("@pSize", index.size));
backupIndexCmd.Parameters.Add(new SQLiteParameter("@pDateOfBackup", index.dateAndTime));
backupIndexCmd.Parameters.Add(new SQLiteParameter("@pBackupLevel", index.backupLevel));
//open the connection, exectute the query, and close the connection.
try
{
conn.Open();
indexIDCount = Convert.ToInt64(indexInitialIDCmd.ExecuteScalar());
if (indexIDCount == 0) // If there are no entries for given guid, then this is the first row
{
indexID = 1;
}
else //otherwise get the ID for the previous row and increment
{
indexID = Convert.ToInt64(indexPreviousIDCmd.ExecuteScalar());
indexID++;
}
backupIndexCmd.Parameters.Add(new SQLiteParameter("@pID", indexID));
backupIndexCmd.ExecuteNonQuery();
}
catch (SQLiteException ex)
{
//if anything is wrong with the sql statement or the database,
//a SQLiteException will show information about it.
Debug.Print(ex.Message);
//always make sure the database connection is closed.
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
// Go through the array of Blocks and add each to the Block_Storage table; also, for each block add an entry to Index_to_Block table
foreach (Block currentBlock in blocks)
{
long blockID = 0;
long blockIDCount = 0;
string blockStorageSql = "INSERT INTO Block_Storage (id, source_guid, storage_guid, storage_path, size, date_created) VALUES (@pID, @pSourceGUID, @pStorageGUID, @pStoragePath, @pSize, @pDateCreated)";
SQLiteCommand blockStorageCmd = new SQLiteCommand(blockStorageSql, conn);
//Get ID for block
//Determine if there are any entries for the given guid
string blockInitialIDQuery = "SELECT COUNT(block_foreign_id) FROM Index_to_Block WHERE index_foreign_guid = @pSourceGUID";
SQLiteCommand blockInitialIDCmd = new SQLiteCommand(blockInitialIDQuery, conn);
//Get previous row ID for given guid
string blockPreviousIDQuery = "SELECT max(block_foreign_id) FROM Index_to_Block WHERE index_foreign_guid = @pSourceGUID";
SQLiteCommand blockPreviousIDCmd = new SQLiteCommand(blockPreviousIDQuery, conn);
// Insert Index_to_Block entry
string indexToBlockSql = "INSERT INTO Index_to_Block (index_foreign_id, index_foreign_guid, block_foreign_id, block_foreign_guid) VALUES (@pIndexForeignID, @pIndexForeignGUID, @pBlockForeignID, @pBlockForeignGUID)";
SQLiteCommand indexToBlockCmd = new SQLiteCommand(indexToBlockSql, conn);
//SQLite likes dates and times in a certain format (ISO-something or other).
//string currentTimeString = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
blockInitialIDCmd.Parameters.Add(new SQLiteParameter("@pSourceGUID", index.sourceGUID));
blockPreviousIDCmd.Parameters.Add(new SQLiteParameter("@pSourceGUID", index.sourceGUID));
blockStorageCmd.Parameters.Add(new SQLiteParameter("@pSourceGUID", currentBlock.sourceGUID));
blockStorageCmd.Parameters.Add(new SQLiteParameter("@pStorageGUID", currentBlock.storageGUID));
blockStorageCmd.Parameters.Add(new SQLiteParameter("@pStoragePath", currentBlock.storagePath));
blockStorageCmd.Parameters.Add(new SQLiteParameter("@pSize", currentBlock.size));
blockStorageCmd.Parameters.Add(new SQLiteParameter("@pDateCreated", currentBlock.dateAndTime));
indexToBlockCmd.Parameters.Add(new SQLiteParameter("@pIndexForeignID", indexID));
indexToBlockCmd.Parameters.Add(new SQLiteParameter("@pIndexForeignGUID", index.sourceGUID));
indexToBlockCmd.Parameters.Add(new SQLiteParameter("@pBlockForeignGUID", currentBlock.sourceGUID));
//open the connection, exectute the query, and close the connection.
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
blockIDCount = Convert.ToInt64(blockInitialIDCmd.ExecuteScalar());
if (blockIDCount == 0) // If there are no entries for given guid, then this is the first row
{
blockID = 1;
}
else //otherwise get the ID for the previous row and increment
{
blockID = Convert.ToInt64(blockPreviousIDCmd.ExecuteScalar());
blockID++;
}
indexToBlockCmd.Parameters.Add(new SQLiteParameter("@pBlockForeignID", blockID));
indexToBlockCmd.ExecuteNonQuery();
blockStorageCmd.Parameters.Add(new SQLiteParameter("@pID", blockID));
blockStorageCmd.ExecuteNonQuery();
}
catch (SQLiteException ex)
{
//if anything is wrong with the sql statement or the database,
//a SQLiteException will show information about it.
Debug.Print(ex.Message);
//always make sure the database connection is closed.
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
IndexDistribution indi = new IndexDistribution();
indi.sendIndexes();
}
/// <summary>
/// Inserts an index into the database.
/// </summary>
/// <param name="index">A BackupIndex object to be added to the Backup_Indexes table.</param>
/// <param name="blocks">A list of Block objects to be added to the Block_Storage table</param>
public void InsertIndex(BackupIndex index, List <Block> blocks)
{
long indexID = 0;
long indexIDCount = 0;
// Insert index into Backup_Indexes table
string backupIndexSql = "INSERT INTO Backup_Indexes (id, source_guid, source_path, first_block_offset, size, date_of_backup, backup_level) VALUES (@pID, @pSourceGUID, @pSourcePath, @pFirstBlockOffset, @pSize, @pDateOfBackup, @pBackupLevel)";
SQLiteCommand backupIndexCmd = new SQLiteCommand(backupIndexSql, conn);
//Get ID for index
//Determine if there are any entries for the given guid
string indexInitialIDQuery = "SELECT COUNT(id) FROM Backup_Indexes WHERE source_guid = @pSourceGUID";
SQLiteCommand indexInitialIDCmd = new SQLiteCommand(indexInitialIDQuery, conn);
//Get previous row ID for given guid
string indexPreviousIDQuery = "SELECT max(id) FROM Backup_Indexes WHERE source_guid = @pSourceGUID";
SQLiteCommand indexPreviousIDCmd = new SQLiteCommand(indexPreviousIDQuery, conn);
//SQLite likes dates and times in a certain format.
//string currentTimeString = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
indexInitialIDCmd.Parameters.Add(new SQLiteParameter("@pSourceGUID", index.sourceGUID));
indexPreviousIDCmd.Parameters.Add(new SQLiteParameter("@pSourceGUID", index.sourceGUID));
backupIndexCmd.Parameters.Add(new SQLiteParameter("@pSourceGUID", index.sourceGUID));
backupIndexCmd.Parameters.Add(new SQLiteParameter("@pSourcePath", index.sourcePath));
backupIndexCmd.Parameters.Add(new SQLiteParameter("@pFirstBlockOffset", index.firstBlockOffset));
backupIndexCmd.Parameters.Add(new SQLiteParameter("@pSize", index.size));
backupIndexCmd.Parameters.Add(new SQLiteParameter("@pDateOfBackup", index.dateAndTime));
backupIndexCmd.Parameters.Add(new SQLiteParameter("@pBackupLevel", index.backupLevel));
//open the connection, exectute the query, and close the connection.
try
{
conn.Open();
indexIDCount = Convert.ToInt64(indexInitialIDCmd.ExecuteScalar());
if (indexIDCount == 0) // If there are no entries for given guid, then this is the first row
{
indexID = 1;
}
else //otherwise get the ID for the previous row and increment
{
indexID = Convert.ToInt64(indexPreviousIDCmd.ExecuteScalar());
indexID++;
}
backupIndexCmd.Parameters.Add(new SQLiteParameter("@pID", indexID));
backupIndexCmd.ExecuteNonQuery();
}
catch (SQLiteException ex)
{
//if anything is wrong with the sql statement or the database,
//a SQLiteException will show information about it.
Debug.Print(ex.Message);
//always make sure the database connection is closed.
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
// Go through the array of Blocks and add each to the Block_Storage table; also, for each block add an entry to Index_to_Block table
foreach (Block currentBlock in blocks)
{
long blockID = 0;
long blockIDCount = 0;
string blockStorageSql = "INSERT INTO Block_Storage (id, source_guid, storage_guid, storage_path, size, date_created) VALUES (@pID, @pSourceGUID, @pStorageGUID, @pStoragePath, @pSize, @pDateCreated)";
SQLiteCommand blockStorageCmd = new SQLiteCommand(blockStorageSql, conn);
//Get ID for block
//Determine if there are any entries for the given guid
string blockInitialIDQuery = "SELECT COUNT(block_foreign_id) FROM Index_to_Block WHERE index_foreign_guid = @pSourceGUID";
SQLiteCommand blockInitialIDCmd = new SQLiteCommand(blockInitialIDQuery, conn);
//Get previous row ID for given guid
string blockPreviousIDQuery = "SELECT max(block_foreign_id) FROM Index_to_Block WHERE index_foreign_guid = @pSourceGUID";
SQLiteCommand blockPreviousIDCmd = new SQLiteCommand(blockPreviousIDQuery, conn);
// Insert Index_to_Block entry
string indexToBlockSql = "INSERT INTO Index_to_Block (index_foreign_id, index_foreign_guid, block_foreign_id, block_foreign_guid) VALUES (@pIndexForeignID, @pIndexForeignGUID, @pBlockForeignID, @pBlockForeignGUID)";
SQLiteCommand indexToBlockCmd = new SQLiteCommand(indexToBlockSql, conn);
//SQLite likes dates and times in a certain format (ISO-something or other).
//string currentTimeString = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
blockInitialIDCmd.Parameters.Add(new SQLiteParameter("@pSourceGUID", index.sourceGUID));
blockPreviousIDCmd.Parameters.Add(new SQLiteParameter("@pSourceGUID", index.sourceGUID));
blockStorageCmd.Parameters.Add(new SQLiteParameter("@pSourceGUID", currentBlock.sourceGUID));
blockStorageCmd.Parameters.Add(new SQLiteParameter("@pStorageGUID", currentBlock.storageGUID));
blockStorageCmd.Parameters.Add(new SQLiteParameter("@pStoragePath", currentBlock.storagePath));
blockStorageCmd.Parameters.Add(new SQLiteParameter("@pSize", currentBlock.size));
blockStorageCmd.Parameters.Add(new SQLiteParameter("@pDateCreated", currentBlock.dateAndTime));
indexToBlockCmd.Parameters.Add(new SQLiteParameter("@pIndexForeignID", indexID));
indexToBlockCmd.Parameters.Add(new SQLiteParameter("@pIndexForeignGUID", index.sourceGUID));
indexToBlockCmd.Parameters.Add(new SQLiteParameter("@pBlockForeignGUID", currentBlock.sourceGUID));
//open the connection, exectute the query, and close the connection.
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
blockIDCount = Convert.ToInt64(blockInitialIDCmd.ExecuteScalar());
if (blockIDCount == 0) // If there are no entries for given guid, then this is the first row
{
blockID = 1;
}
else //otherwise get the ID for the previous row and increment
{
blockID = Convert.ToInt64(blockPreviousIDCmd.ExecuteScalar());
blockID++;
}
indexToBlockCmd.Parameters.Add(new SQLiteParameter("@pBlockForeignID", blockID));
indexToBlockCmd.ExecuteNonQuery();
blockStorageCmd.Parameters.Add(new SQLiteParameter("@pID", blockID));
blockStorageCmd.ExecuteNonQuery();
}
catch (SQLiteException ex)
{
//if anything is wrong with the sql statement or the database,
//a SQLiteException will show information about it.
Debug.Print(ex.Message);
//always make sure the database connection is closed.
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
IndexDistribution indi = new IndexDistribution();
indi.sendIndexes();
}
