public static long ExecuteScalarInt64(this System.Data.IDbCommand self, string cmd, long defaultvalue, params object[] values)
{
if (cmd != null)
{
self.CommandText = cmd;
}
if (values != null && values.Length > 0)
{
self.Parameters.Clear();
foreach (var n in values)
{
self.AddParameter(n);
}
}
using (new Logging.Timer(LC.L("ExecuteScalar: {0}", self.CommandText)))
using (var rd = self.ExecuteReader())
if (rd.Read())
{
return(ConvertValueToInt64(rd, 0, defaultvalue));
}
return(defaultvalue);
}
public static int ExecuteNonQuery(this System.Data.IDbCommand self, bool writeLog, string cmd, params object[] values)
{
if (cmd != null)
{
self.CommandText = cmd;
}
if (values != null && values.Length > 0)
{
self.Parameters.Clear();
foreach (var n in values)
{
self.AddParameter(n);
}
}
using (writeLog ? new Logging.Timer(LOGTAG, "ExecuteNonQuery", string.Format("ExecuteNonQuery: {0}", self.GetPrintableCommandText())) : null)
return(self.ExecuteNonQuery());
}
public static object ExecuteScalar(this System.Data.IDbCommand self, string cmd, params object[] values)
{
if (cmd != null)
{
self.CommandText = cmd;
}
if (values != null && values.Length > 0)
{
self.Parameters.Clear();
foreach (var n in values)
{
self.AddParameter(n);
}
}
using (new Logging.Timer(LC.L("ExecuteScalar: {0}", self.CommandText)))
return(self.ExecuteScalar());
}
public static System.Data.IDataReader ExecuteReader(this System.Data.IDbCommand self, string cmd, params object[] values)
{
if (cmd != null)
{
self.CommandText = cmd;
}
if (values != null && values.Length > 0)
{
self.Parameters.Clear();
foreach (var n in values)
{
self.AddParameter(n);
}
}
using (new Logging.Timer(LOGTAG, "ExcuteReader", string.Format("ExecuteReader: {0}", self.GetPrintableCommandText())))
return(self.ExecuteReader());
}
private LocalDatabase(System.Data.IDbConnection connection)
{
m_updateremotevolumeCommand = connection.CreateCommand();
m_selectremotevolumesCommand = connection.CreateCommand();
m_selectremotevolumeCommand = connection.CreateCommand();
m_insertlogCommand = connection.CreateCommand();
m_insertremotelogCommand = connection.CreateCommand();
m_removeremotevolumeCommand = connection.CreateCommand();
m_selectremotevolumeIdCommand = connection.CreateCommand();
m_createremotevolumeCommand = connection.CreateCommand();
m_insertIndexBlockLink = connection.CreateCommand();
m_insertlogCommand.CommandText = @"INSERT INTO ""LogData"" (""OperationID"", ""Timestamp"", ""Type"", ""Message"", ""Exception"") VALUES (?, ?, ?, ?, ?)";
m_insertlogCommand.AddParameters(5);
m_insertremotelogCommand.CommandText = @"INSERT INTO ""RemoteOperation"" (""OperationID"", ""Timestamp"", ""Operation"", ""Path"", ""Data"") VALUES (?, ?, ?, ?, ?)";
m_insertremotelogCommand.AddParameters(5);
m_updateremotevolumeCommand.CommandText = @"UPDATE ""Remotevolume"" SET ""OperationID"" = ?, ""State"" = ?, ""Hash"" = ?, ""Size"" = ? WHERE ""Name"" = ?";
m_updateremotevolumeCommand.AddParameters(5);
m_selectremotevolumesCommand.CommandText = @"SELECT ""Name"", ""Type"", ""Size"", ""Hash"", ""State"" FROM ""Remotevolume""";
m_selectremotevolumeCommand.CommandText = @"SELECT ""Type"", ""Size"", ""Hash"", ""State"" FROM ""Remotevolume"" WHERE ""Name"" = ?";
m_selectremotevolumeCommand.AddParameter();
m_removeremotevolumeCommand.CommandText = @"DELETE FROM ""Remotevolume"" WHERE ""Name"" = ?";
m_removeremotevolumeCommand.AddParameter();
m_selectremotevolumeIdCommand.CommandText = @"SELECT ""ID"" FROM ""Remotevolume"" WHERE ""Name"" = ?";
m_createremotevolumeCommand.CommandText = @"INSERT INTO ""Remotevolume"" (""OperationID"", ""Name"", ""Type"", ""State"", ""VerificationCount"") VALUES (?, ?, ?, ?, ?); SELECT last_insert_rowid();";
m_createremotevolumeCommand.AddParameters(5);
m_insertIndexBlockLink.CommandText = @"INSERT INTO ""IndexBlockLink"" (""IndexVolumeID"", ""BlockVolumeID"") VALUES (?, ?)";
m_insertIndexBlockLink.AddParameters(2);
}
public static System.Data.IDataReader ExecuteReader(this System.Data.IDbCommand self, string cmd, params object[] values)
{
if (cmd != null)
{
self.CommandText = cmd;
}
if (values != null && values.Length > 0)
{
self.Parameters.Clear();
foreach (var n in values)
{
self.AddParameter(n);
}
}
if (Logging.Log.LogLevel != Duplicati.Library.Logging.LogMessageType.Profiling)
{
return(self.ExecuteReader());
}
using (new Logging.Timer(LC.L("ExecuteReader: {0}", self.CommandText)))
return(self.ExecuteReader());
}
public LocalBackupDatabase(LocalDatabase db, Options options)
: base(db)
{
m_findblockCommand = m_connection.CreateCommand();
m_insertblockCommand = m_connection.CreateCommand();
m_insertfileCommand = m_connection.CreateCommand();
m_insertblocksetCommand = m_connection.CreateCommand();
m_insertmetadatasetCommand = m_connection.CreateCommand();
m_findblocksetCommand = m_connection.CreateCommand();
m_findmetadatasetCommand = m_connection.CreateCommand();
m_findfilesetCommand = m_connection.CreateCommand();
m_insertblocksetentryCommand = m_connection.CreateCommand();
m_insertblocklistHashesCommand = m_connection.CreateCommand();
m_selectblocklistHashesCommand = m_connection.CreateCommand();
m_insertfileOperationCommand = m_connection.CreateCommand();
m_findfileCommand = m_connection.CreateCommand();
m_selectfilelastmodifiedCommand = m_connection.CreateCommand();
m_selectfileHashCommand = m_connection.CreateCommand();
m_insertblocksetentryFastCommand = m_connection.CreateCommand();
m_findblockCommand.CommandText = @"SELECT ""ID"" FROM ""Block"" WHERE ""Hash"" = ? AND ""Size"" = ?";
m_findblockCommand.AddParameters(2);
m_findblocksetCommand.CommandText = @"SELECT ""ID"" FROM ""Blockset"" WHERE ""Fullhash"" = ? AND ""Length"" = ?";
m_findblocksetCommand.AddParameters(2);
m_findmetadatasetCommand.CommandText = @"SELECT ""A"".""ID"" FROM ""Metadataset"" A, ""BlocksetEntry"" B, ""Block"" C WHERE ""A"".""BlocksetID"" = ""B"".""BlocksetID"" AND ""B"".""BlockID"" = ""C"".""ID"" AND ""C"".""Hash"" = ? AND ""C"".""Size"" = ?";
m_findmetadatasetCommand.AddParameters(2);
m_findfilesetCommand.CommandText = @"SELECT ""ID"" FROM ""File"" WHERE ""BlocksetID"" = ? AND ""MetadataID"" = ? AND ""Path"" = ?";
m_findfilesetCommand.AddParameters(3);
m_insertblockCommand.CommandText = @"INSERT INTO ""Block"" (""Hash"", ""VolumeID"", ""Size"") VALUES (?, ?, ?); SELECT last_insert_rowid();";
m_insertblockCommand.AddParameters(3);
m_insertfileOperationCommand.CommandText = @"INSERT INTO ""FilesetEntry"" (""FilesetID"", ""FileID"", ""Lastmodified"") VALUES (?, ?, ?)";
m_insertfileOperationCommand.AddParameters(3);
m_insertfileCommand.CommandText = @"INSERT INTO ""File"" (""Path"",""BlocksetID"", ""MetadataID"") VALUES (?, ? ,?); SELECT last_insert_rowid();";
m_insertfileCommand.AddParameters(3);
m_insertblocksetCommand.CommandText = @"INSERT INTO ""Blockset"" (""Length"", ""FullHash"") VALUES (?, ?); SELECT last_insert_rowid();";
m_insertblocksetCommand.AddParameters(2);
m_insertblocksetentryFastCommand.CommandText = @"INSERT INTO ""BlocksetEntry"" (""BlocksetID"", ""Index"", ""BlockID"") VALUES (?,?,?)";
m_insertblocksetentryFastCommand.AddParameters(3);
m_insertblocksetentryCommand.CommandText = @"INSERT INTO ""BlocksetEntry"" (""BlocksetID"", ""Index"", ""BlockID"") SELECT ? AS A, ? AS B, ""ID"" FROM ""Block"" WHERE ""Hash"" = ? AND ""Size"" = ?";
m_insertblocksetentryCommand.AddParameters(4);
m_insertblocklistHashesCommand.CommandText = @"INSERT INTO ""BlocklistHash"" (""BlocksetID"", ""Index"", ""Hash"") VALUES (?, ?, ?)";
m_insertblocklistHashesCommand.AddParameters(3);
m_insertmetadatasetCommand.CommandText = @"INSERT INTO ""Metadataset"" (""BlocksetID"") VALUES (?); SELECT last_insert_rowid();";
m_insertmetadatasetCommand.AddParameter();
m_selectfilelastmodifiedCommand.CommandText = @"SELECT ""File"".""ID"", ""FilesetEntry"".""LastModified"" FROM ""File"", ""FilesetEntry"" WHERE ""Path"" = ? AND ""File"".""ID"" = ""FilesetEntry"".""FileID"" AND ""FilesetEntry"".""FilesetID"" = ? LIMIT 1";
m_selectfilelastmodifiedCommand.AddParameters(2);
//Need a temporary table with path/lastmodified lookups
m_findfileCommand.CommandText =
@" SELECT ""File"".""ID"" AS ""FileID"", ""FilesetEntry"".""Lastmodified"", ""FileBlockset"".""Length"", ""MetaBlockset"".""Fullhash"" AS ""Metahash"", ""MetaBlockset"".""Length"" AS ""Metasize"" " +
@" FROM ""File"", ""FilesetEntry"", ""Fileset"", ""Blockset"" ""FileBlockset"", ""Metadataset"", ""Blockset"" ""MetaBlockset"" " +
@" WHERE ""File"".""Path"" = ? " +
@" AND ""FilesetEntry"".""FileID"" = ""File"".""ID"" AND ""Fileset"".""ID"" = ""FilesetEntry"".""FilesetID"" " +
@" AND ""FileBlockset"".""ID"" = ""File"".""BlocksetID"" " +
@" AND ""Metadataset"".""ID"" = ""File"".""MetadataID"" AND ""MetaBlockset"".""ID"" = ""Metadataset"".""BlocksetID"" " +
@" ORDER BY ""Fileset"".""Timestamp"" DESC " +
@" LIMIT 1 ";
m_findfileCommand.AddParameters(1);
m_selectfileHashCommand.CommandText = @"SELECT ""Blockset"".""Fullhash"" FROM ""Blockset"", ""File"" WHERE ""Blockset"".""ID"" = ""File"".""BlocksetID"" AND ""File"".""ID"" = ? ";
m_selectfileHashCommand.AddParameters(1);
m_selectblocklistHashesCommand.CommandText = @"SELECT ""Hash"" FROM ""BlocklistHash"" WHERE ""BlocksetID"" = ? ORDER BY ""Index"" ASC ";
m_selectblocklistHashesCommand.AddParameters(1);
}
public LocalBackupDatabase(LocalDatabase db, Options options)
: base(db)
{
m_findblockCommand = m_connection.CreateCommand();
m_insertblockCommand = m_connection.CreateCommand();
m_insertfileCommand = m_connection.CreateCommand();
m_insertblocksetCommand = m_connection.CreateCommand();
m_insertmetadatasetCommand = m_connection.CreateCommand();
m_findblocksetCommand = m_connection.CreateCommand();
m_findmetadatasetCommand = m_connection.CreateCommand();
m_findfilesetCommand = m_connection.CreateCommand();
m_insertblocksetentryCommand = m_connection.CreateCommand();
m_findremotevolumestateCommand = m_connection.CreateCommand();
m_updateblockCommand = m_connection.CreateCommand();
m_insertblocklistHashesCommand = m_connection.CreateCommand();
m_selectblocklistHashesCommand = m_connection.CreateCommand();
m_insertfileOperationCommand = m_connection.CreateCommand();
m_selectfileSimpleCommand = m_connection.CreateCommand();
m_selectfileHashCommand = m_connection.CreateCommand();
m_findmetadatasetProbeCommand = m_connection.CreateCommand();
m_insertblocksetentryFastCommand = m_connection.CreateCommand();
m_findblockCommand.CommandText = @"SELECT ""ID"" FROM ""Block"" WHERE ""Hash"" = ? AND ""Size"" = ?";
m_findblockCommand.AddParameters(2);
m_findblocksetCommand.CommandText = @"SELECT ""ID"" FROM ""Blockset"" WHERE ""Fullhash"" = ? AND ""Length"" = ?";
m_findblocksetCommand.AddParameters(2);
m_findmetadatasetProbeCommand.CommandText = @"SELECT ""ID"" FROM ""Blockset"" WHERE ""Hash"" = ? AND ""Size"" = ? LIMIT 1";
m_findmetadatasetProbeCommand.AddParameters(2);
m_findmetadatasetCommand.CommandText = @"SELECT ""A"".""ID"" FROM ""Metadataset"" A, ""BlocksetEntry"" B, ""Block"" C WHERE ""A"".""BlocksetID"" = ""B"".""BlocksetID"" AND ""B"".""BlocksetID"" = ""C"".""ID"" AND ""C"".""Hash"" = ? AND ""C"".""Size"" = ? LIMIT 1";
m_findmetadatasetCommand.AddParameters(2);
m_findfilesetCommand.CommandText = @"SELECT ""ID"" FROM ""File"" WHERE ""BlocksetID"" = ? AND ""MetadataID"" = ? AND ""Path"" = ?";
m_findfilesetCommand.AddParameters(3);
m_insertblockCommand.CommandText = @"INSERT INTO ""Block"" (""Hash"", ""VolumeID"", ""Size"") VALUES (?, ?, ?); SELECT last_insert_rowid();";
m_insertblockCommand.AddParameters(3);
m_insertfileOperationCommand.CommandText = @"INSERT INTO ""FilesetEntry"" (""FilesetID"", ""FileID"", ""Scantime"") VALUES (?, ?, ?)";
m_insertfileOperationCommand.AddParameters(3);
m_insertfileCommand.CommandText = @"INSERT INTO ""File"" (""Path"",""BlocksetID"", ""MetadataID"") VALUES (?, ? ,?); SELECT last_insert_rowid();";
m_insertfileCommand.AddParameters(3);
m_insertblocksetCommand.CommandText = @"INSERT INTO ""Blockset"" (""Length"", ""FullHash"") VALUES (?, ?); SELECT last_insert_rowid();";
m_insertblocksetCommand.AddParameters(2);
m_insertblocksetentryFastCommand.CommandText = @"INSERT INTO ""BlocksetEntry"" (""BlocksetID"", ""Index"", ""BlockID"") VALUES (?,?,?)";
m_insertblocksetentryFastCommand.AddParameters(3);
m_insertblocksetentryCommand.CommandText = @"INSERT INTO ""BlocksetEntry"" (""BlocksetID"", ""Index"", ""BlockID"") SELECT ? AS A, ? AS B, ""ID"" FROM ""Block"" WHERE ""Hash"" = ? AND ""Size"" = ?";
m_insertblocksetentryCommand.AddParameters(4);
m_insertblocklistHashesCommand.CommandText = @"INSERT INTO ""BlocklistHash"" (""BlocksetID"", ""Index"", ""Hash"") VALUES (?, ?, ?)";
m_insertblocklistHashesCommand.AddParameters(3);
m_insertmetadatasetCommand.CommandText = @"INSERT INTO ""Metadataset"" (""BlocksetID"") VALUES (?); SELECT last_insert_rowid();";
m_insertmetadatasetCommand.AddParameter();
//Need a temporary table with path/scantime lookups
m_scantimelookupTablename = "ScanTime-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
var scantableDefinition = @"SELECT ""A"".""FileID"" AS ""FileID"", ""A"".""Scantime"" AS ""Scantime"", ""File"".""Path"" AS ""Path"" FROM (SELECT ""FilesetEntry"".""FileID"" AS ""FileID"", MAX(""FilesetEntry"".""Scantime"") AS ""Scantime"" FROM ""FilesetEntry"" GROUP BY ""FilesetEntry"".""FileID"") A, ""File"" WHERE ""File"".""ID"" = ""A"".""FileID""";
using (var cmd = m_connection.CreateCommand())
cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" AS " + scantableDefinition, m_scantimelookupTablename));
m_selectfileSimpleCommand.CommandText = string.Format(@"SELECT ""FileID"", ""Scantime"" FROM ""{0}"" WHERE ""BlocksetID"" >= 0 AND ""Path"" = ?", m_scantimelookupTablename);
m_selectfileSimpleCommand.AddParameters(1);
m_selectfileHashCommand.CommandText = @"SELECT ""Blockset"".""Fullhash"" FROM ""Blockset"", ""File"" WHERE ""Blockset"".""ID"" = ""File"".""BlocksetID"" AND ""File"".""ID"" = ? ";
m_selectfileHashCommand.AddParameters(1);
m_selectblocklistHashesCommand.CommandText = @"SELECT ""Hash"" FROM ""BlocklistHash"" WHERE ""BlocksetID"" = ? ORDER BY ""Index"" ASC ";
m_selectblocklistHashesCommand.AddParameters(1);
m_findremotevolumestateCommand.CommandText = @"SELECT ""State"" FROM ""Remotevolume"" WHERE ""Name"" = ?";
m_findremotevolumestateCommand.AddParameters(1);
m_updateblockCommand.CommandText = @"UPDATE ""Block"" SET ""VolumeID"" = ? WHERE ""Hash"" = ? AND ""Size"" = ? ";
m_updateblockCommand.AddParameters(3);
}
public LocalBackupDatabase(LocalDatabase db, Options options)
: base(db)
{
m_findblockCommand = m_connection.CreateCommand();
m_insertblockCommand = m_connection.CreateCommand();
m_insertfileCommand = m_connection.CreateCommand();
m_insertblocksetCommand = m_connection.CreateCommand();
m_insertmetadatasetCommand = m_connection.CreateCommand();
m_findblocksetCommand = m_connection.CreateCommand();
m_findmetadatasetCommand = m_connection.CreateCommand();
m_findfilesetCommand = m_connection.CreateCommand();
m_insertblocksetentryCommand = m_connection.CreateCommand();
m_insertblocklistHashesCommand = m_connection.CreateCommand();
m_selectblocklistHashesCommand = m_connection.CreateCommand();
m_insertfileOperationCommand = m_connection.CreateCommand();
m_selectfileSimpleCommand = m_connection.CreateCommand();
m_selectfileHashCommand = m_connection.CreateCommand();
m_insertblocksetentryFastCommand = m_connection.CreateCommand();
m_findblockCommand.CommandText = @"SELECT ""ID"" FROM ""Block"" WHERE ""Hash"" = ? AND ""Size"" = ?";
m_findblockCommand.AddParameters(2);
m_findblocksetCommand.CommandText = @"SELECT ""ID"" FROM ""Blockset"" WHERE ""Fullhash"" = ? AND ""Length"" = ?";
m_findblocksetCommand.AddParameters(2);
m_findmetadatasetCommand.CommandText = @"SELECT ""A"".""ID"" FROM ""Metadataset"" A, ""BlocksetEntry"" B, ""Block"" C WHERE ""A"".""BlocksetID"" = ""B"".""BlocksetID"" AND ""B"".""BlockID"" = ""C"".""ID"" AND ""C"".""Hash"" = ? AND ""C"".""Size"" = ?";
m_findmetadatasetCommand.AddParameters(2);
m_findfilesetCommand.CommandText = @"SELECT ""ID"" FROM ""File"" WHERE ""BlocksetID"" = ? AND ""MetadataID"" = ? AND ""Path"" = ?";
m_findfilesetCommand.AddParameters(3);
m_insertblockCommand.CommandText = @"INSERT INTO ""Block"" (""Hash"", ""VolumeID"", ""Size"") VALUES (?, ?, ?); SELECT last_insert_rowid();";
m_insertblockCommand.AddParameters(3);
m_insertfileOperationCommand.CommandText = @"INSERT INTO ""FilesetEntry"" (""FilesetID"", ""FileID"", ""Lastmodified"") VALUES (?, ?, ?)";
m_insertfileOperationCommand.AddParameters(3);
m_insertfileCommand.CommandText = @"INSERT INTO ""File"" (""Path"",""BlocksetID"", ""MetadataID"") VALUES (?, ? ,?); SELECT last_insert_rowid();";
m_insertfileCommand.AddParameters(3);
m_insertblocksetCommand.CommandText = @"INSERT INTO ""Blockset"" (""Length"", ""FullHash"") VALUES (?, ?); SELECT last_insert_rowid();";
m_insertblocksetCommand.AddParameters(2);
m_insertblocksetentryFastCommand.CommandText = @"INSERT INTO ""BlocksetEntry"" (""BlocksetID"", ""Index"", ""BlockID"") VALUES (?,?,?)";
m_insertblocksetentryFastCommand.AddParameters(3);
m_insertblocksetentryCommand.CommandText = @"INSERT INTO ""BlocksetEntry"" (""BlocksetID"", ""Index"", ""BlockID"") SELECT ? AS A, ? AS B, ""ID"" FROM ""Block"" WHERE ""Hash"" = ? AND ""Size"" = ?";
m_insertblocksetentryCommand.AddParameters(4);
m_insertblocklistHashesCommand.CommandText = @"INSERT INTO ""BlocklistHash"" (""BlocksetID"", ""Index"", ""Hash"") VALUES (?, ?, ?)";
m_insertblocklistHashesCommand.AddParameters(3);
m_insertmetadatasetCommand.CommandText = @"INSERT INTO ""Metadataset"" (""BlocksetID"") VALUES (?); SELECT last_insert_rowid();";
m_insertmetadatasetCommand.AddParameter();
//Need a temporary table with path/lastmodified lookups
m_lastmodifiedLookupTablename = "LastModified-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
m_selectfileSimpleCommand.CommandText = string.Format(@"SELECT ""FileID"", ""Lastmodified"", ""Length"", ""Metahash"", ""Metasize"" FROM ""{0}"" WHERE ""BlocksetID"" >= 0 AND ""Path"" = ?", m_lastmodifiedLookupTablename);
m_selectfileSimpleCommand.AddParameters(1);
m_selectfileHashCommand.CommandText = @"SELECT ""Blockset"".""Fullhash"" FROM ""Blockset"", ""File"" WHERE ""Blockset"".""ID"" = ""File"".""BlocksetID"" AND ""File"".""ID"" = ? ";
m_selectfileHashCommand.AddParameters(1);
m_selectblocklistHashesCommand.CommandText = @"SELECT ""Hash"" FROM ""BlocklistHash"" WHERE ""BlocksetID"" = ? ORDER BY ""Index"" ASC ";
m_selectblocklistHashesCommand.AddParameters(1);
}
public void TestMethod()
{
System.Data.IDbCommand cmd = null;
cmd.AddParameter("testp", "testValue")
.AddParameter("abc", "def");
}
