private key RemoveBackupIndex(string sourceGUID, string dateTimeOfBackup)
{
key indexPrimaryKey = new key(0, sourceGUID);
string indexKeyQuery = "SELECT id FROM Backup_Indexes WHERE source_guid = @pSourceGUID AND date_of_backup = @pDateTimeOfBackup";
string backupIndexSql = "DELETE FROM Backup_Indexes WHERE source_guid = @pSourceGUID AND date_of_backup = @pDateTimeOfBackup";
SQLiteCommand indexKeyCmd = new SQLiteCommand(indexKeyQuery, conn);
SQLiteCommand backupIndexCmd = new SQLiteCommand(backupIndexSql, conn);
SQLiteParameter pSourceGUID = new SQLiteParameter("@pSourceGUID", sourceGUID);
SQLiteParameter pDateOfBackup = new SQLiteParameter("@DateTimeOfBackup", dateTimeOfBackup);
indexKeyCmd.Parameters.Add(pSourceGUID);
indexKeyCmd.Parameters.Add(pDateOfBackup);
backupIndexCmd.Parameters.Add(pSourceGUID);
backupIndexCmd.Parameters.Add(pDateOfBackup);
try
{
conn.Open();
indexPrimaryKey.id = Convert.ToInt64(indexKeyCmd.ExecuteScalar());
backupIndexCmd.ExecuteNonQuery();
conn.Close();
}
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();
}
}
return indexPrimaryKey;
}
private List<key> RemoveIndexToBlocks(key indexForeignKey)
{
List<key> blockKeys = new List<key>();
string blockKeyQuery = "SELECT block_foreign_id,block_foreign_guid FROM Index_to_Block WHERE index_foreign_id = @pIndexForeignID AND index_foreign_guid = @pIndexForeignGUID";
string indexToBlockSql = "DELETE FROM Index_to_Block WHERE index_foreign_id = @pIndexForeignID AND index_foreign_guid = @pIndexForeignGUID";
SQLiteCommand blockKeyCmd = new SQLiteCommand(blockKeyQuery, conn);
SQLiteCommand indexToBlockCmd = new SQLiteCommand(indexToBlockSql, conn);
SQLiteParameter pIndexForeignID = new SQLiteParameter("@pIndexForeignID", indexForeignKey.id);
SQLiteParameter pIndexForeignGUID = new SQLiteParameter("@pIndexForeignGUID", indexForeignKey.guid);
blockKeyCmd.Parameters.Add(pIndexForeignID);
indexToBlockCmd.Parameters.Add(pIndexForeignID);
blockKeyCmd.Parameters.Add(pIndexForeignGUID);
indexToBlockCmd.Parameters.Add(pIndexForeignGUID);
try
{
conn.Open();
using (SQLiteDataReader reader = blockKeyCmd.ExecuteReader())
{
while (reader.Read())
{
//'reader' iterates through returned
//block_foreign_key records and each is added to list.
long blockForeignID = reader.GetInt64(reader.GetOrdinal("block_foreign_id"));
string blockForeignGUID = reader.GetString(reader.GetOrdinal("block_foreign_guid"));
key currentKey = new key(blockForeignID, blockForeignGUID);
blockKeys.Add(currentKey);
}
}
indexToBlockCmd.ExecuteNonQuery();
conn.Close();
}
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();
}
}
return blockKeys;
}
/// <summary>
/// Given a backup index from the Backup_Indexes database table, provides a list of blocks from the
/// Block_Storage table that correspond to that index
/// </summary>
/// <param name="index">The index from the Backup_Indexes table</param>
/// <returns>A list of Block objects; each object corresponds to an entry in the Block_Storage database table</returns>
public List<Block> GetBlockList(BackupIndex index)
{
List<Block> blockList = new List<Block>();
//Get a list of block foreign keys from the Index_to_Block table
List<key> keyList = new List<key>();
string query = "SELECT block_foreign_id,block_foreign_guid FROM Index_to_Block WHERE index_foreign_id = @pIndexID AND index_foreign_guid = @pIndexGUID";
SQLiteCommand cmd = new SQLiteCommand(query, conn);
//create a parameter for indexPrimaryKey
SQLiteParameter pIndexID = new SQLiteParameter("@pIndexID", index.id);
SQLiteParameter pIndexGUID = new SQLiteParameter("@pIndexGUID", index.sourceGUID);
cmd.Parameters.Add(pIndexID);
cmd.Parameters.Add(pIndexGUID);
try
{
//open the connection
conn.Open();
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
//'reader' iterates through returned
//block foreign key records and each is added to list.
long blockForeignID = reader.GetInt64(reader.GetOrdinal("block_foreign_id"));
string blockForeignGUID = reader.GetString(reader.GetOrdinal("block_foreign_guid"));
key currentKey = new key(blockForeignID, blockForeignGUID);
keyList.Add(currentKey);
}
}
//close the connection
conn.Close();
}
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();
}
}
//For each block_foreign_key, extract the block from the database and add it to the list of Blocks
foreach (key currentKey in keyList)
{
long id = 0;
string sourceGUID = "";
string storageGUID = "";
string storagePath = "";
long size = 0;
string dateAndTime = "";
string blockQuery = "SELECT id,source_guid,storage_guid,storage_path,size,date_created FROM Block_Storage WHERE id = @pBlockForeignID AND source_guid = @pBlockForeignGUID";
SQLiteCommand blockCmd = new SQLiteCommand(blockQuery, conn);
SQLiteParameter pBlockForeignID = new SQLiteParameter("@pBlockForeignID", currentKey.id);
SQLiteParameter pBlockForeignGUID = new SQLiteParameter("@pBlockForeignGUID", currentKey.guid);
blockCmd.Parameters.Add(pBlockForeignID);
blockCmd.Parameters.Add(pBlockForeignGUID);
try
{
conn.Open();
using (SQLiteDataReader reader = blockCmd.ExecuteReader())
{
while (reader.Read())
{
//'reader' iterates through returned block fields
id = reader.GetInt64(reader.GetOrdinal("id"));
sourceGUID = reader.GetString(reader.GetOrdinal("source_guid"));
storageGUID = reader.GetString(reader.GetOrdinal("storage_guid"));
storagePath = reader.GetString(reader.GetOrdinal("storage_path"));
size = reader.GetInt64(reader.GetOrdinal("size"));
dateAndTime = reader.GetString(reader.GetOrdinal("date_created"));
}
}
conn.Close();
}
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();
}
}
Block currentBlock = new Block(sourceGUID, storageGUID, storagePath, size, dateAndTime);
currentBlock.id = id;
blockList.Add(currentBlock);
}
//sort into ascending order
blockList.Sort((block1, block2) => block1.id.CompareTo(block2.id));
return blockList;
}
