internal static ImageStoreFile GetFile(Guid id)
{
var connection = DatabaseConnection.Current;
using (var command = new SqlCommand("Select [FolderId],[Path],[FileName],[ExtensionId],[ImageHash],[Sha1Hash],[FileSize],[FileState],[ImageComparedThreshold] from [File] Where [Id]=@Id"))
{
command.Connection = connection;
command.Parameters.Add(new SqlParameter("@Id", System.Data.SqlDbType.UniqueIdentifier)
{
Value = id
});
using (var reader = command.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
ImageStoreFile result;
if (reader.Read())
{
result = new ImageStoreFile(id, (Guid)reader[0], (string)reader[1], (string)reader[2], (Guid)reader[3])
{
ImageHash = DBNullableReader.ConvertFromReferenceType <byte[]>(reader[4]),
Sha1Hash = DBNullableReader.ConvertFromReferenceType <byte[]>(reader[5]),
FileSize = (int)reader[6],
FileStateCode = (int)reader[7],
ImageComparedThreshold = (float)reader[8]
};
}
else
{
result = null;
}
reader.Close();
return(result);
}
}
}
internal static int UpdateRecord(ImageStoreFile file)
{
var connection = DatabaseConnection.Current;
using (var command = new SqlCommand("Update [File] Set [ImageHash]=@ImageHash, [Sha1Hash]=@Sha1Hash, [FileSize]=@FileSize, [FileState]=@FileState, [ImageComparedThreshold]=@ImageComparedThreshold where [Id]=@Id"))
{
command.Connection = connection;
command.Parameters.Add(new SqlParameter("@Id", System.Data.SqlDbType.UniqueIdentifier)
{
Value = file.Id
});
command.Parameters.Add(new SqlParameter("@ImageHash", System.Data.SqlDbType.Binary, 40)
{
Value = DBNullableReader.NullCheck(file.ImageHash)
});
command.Parameters.Add(new SqlParameter("@Sha1Hash", System.Data.SqlDbType.Binary, 20)
{
Value = DBNullableReader.NullCheck(file.Sha1Hash)
});
command.Parameters.Add(new SqlParameter("@FileSize", System.Data.SqlDbType.Int)
{
Value = file.FileSize
});
command.Parameters.Add(new SqlParameter("@FileState", System.Data.SqlDbType.Int)
{
Value = file.FileStateCode
});
command.Parameters.Add(new SqlParameter("@ImageComparedThreshold", System.Data.SqlDbType.Real)
{
Value = file.ImageComparedThreshold
});
return(command.ExecuteNonQuery());
}
}
protected override void ProcessRecord()
{
if (Path == null)
{
throw new ArgumentNullException(nameof(Path));
}
if (FileName == null)
{
throw new ArgumentNullException(nameof(FileName));
}
var connection = DatabaseConnection.Current;
using (var command = new SqlCommand("Select [Id],[Path],[FileName],[ImageHash],[Sha1Hash],[FileSize],[FileState],[ImageComparedThreshold] from [File] Where [FolderId]=@FolderId and [Path]=@Path and [FileName]=@FileName and [ExtensionId]=@ExtensionId"))
{
command.Connection = connection;
command.CommandTimeout = 0;
command.Parameters.Add(new SqlParameter("@FolderId", System.Data.SqlDbType.UniqueIdentifier)
{
Value = FolderId
});
command.Parameters.Add(new SqlParameter("@Path", System.Data.SqlDbType.NVarChar, 256)
{
Value = Path
});
command.Parameters.Add(new SqlParameter("@FileName", System.Data.SqlDbType.NVarChar, 256)
{
Value = FileName
});
command.Parameters.Add(new SqlParameter("@ExtensionId", System.Data.SqlDbType.UniqueIdentifier)
{
Value = ExtensionId
});
using (var reader = command.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
if (reader.Read())
{
ImageStoreFile line = new ImageStoreFile((Guid)reader[0], FolderId, (string)reader[1], (string)reader[2], ExtensionId)
{
ImageHash = DBNullableReader.ConvertFromReferenceType <byte[]>(reader[3]),
Sha1Hash = DBNullableReader.ConvertFromReferenceType <byte[]>(reader[4]),
FileSize = (int)reader[5],
FileStateCode = (int)reader[6],
ImageComparedThreshold = (float)reader[7]
};
WriteObject(line);
}
else
{
WriteObject(null);
}
reader.Close();
}
}
}
void LoadToMemory(Guid fileIdSpecified)
{
var connection = DatabaseConnection.Current;
using (var command = new SqlCommand("Create table #tempSimilarFile ([Id] uniqueidentifier, [File1Id] uniqueidentifier, [File2Id] uniqueidentifier, [DifferenceDegree] real, [IgnoredMode] int)", connection))
{
command.ExecuteNonQuery();
}
var insertCommand = "insert into #tempSimilarFile Select [Id],[File1Id],[File2Id],[DifferenceDegree],[IgnoredMode] from [SimilarFile] where [DifferenceDegree]<=@DifferenceDegree";
if (!IncludesDisconnected.IsPresent) //skip while loading to memory
{
insertCommand += " and [IgnoredMode]<>2";
}
using (var command = new SqlCommand(insertCommand, connection)
{
CommandTimeout = 180
})
{
command.Parameters.Add(new SqlParameter("@DifferenceDegree", System.Data.SqlDbType.Real)
{
Value = DifferenceDegree
});
if (fileIdSpecified != Guid.Empty)
{
command.CommandText += " and ([File1Id] = @FileId or [File2Id] = @FileId)";
command.Parameters.Add(new SqlParameter("@FileId", System.Data.SqlDbType.UniqueIdentifier)
{
Value = fileIdSpecified
});
}
command.ExecuteNonQuery();
}
using (var command = new SqlCommand("Select [Id],[File1Id],[File2Id],[DifferenceDegree],[IgnoredMode] from #tempSimilarFile", connection)
{
CommandTimeout = 180
})
{
using (var reader = command.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
ImageStoreSimilarFile line = new ImageStoreSimilarFile((Guid)reader[0], (Guid)reader[1], (Guid)reader[2], (float)reader[3])
{
IgnoredModeCode = (int)reader[4]
};
allRecords.Add(line.Id, line);
if (!fileToRecords.TryGetValue(line.File1Id, out var records))
{
records = new List <Guid>();
fileToRecords.Add(line.File1Id, records);
}
records.Add(line.Id);
if (!fileToRecords.TryGetValue(line.File2Id, out records))
{
records = new List <Guid>();
fileToRecords.Add(line.File2Id, records);
}
records.Add(line.Id);
}
}
}
using (var command = new SqlCommand("Create table #tempSimilarFileId ([Id] uniqueidentifier)", connection))
{
command.ExecuteNonQuery();
}
using (var command = new SqlCommand("insert into #tempSimilarFileId select distinct * from (select [File1Id] from #tempSimilarFile union select [File2Id] from #tempSimilarFile) IdTable", connection)
{
CommandTimeout = 180
})
{
command.ExecuteNonQuery();
}
using (var command = new SqlCommand("drop table #tempSimilarFile", connection))
{
command.ExecuteNonQuery();
}
using (var command = new SqlCommand("Select [Id],[FolderId],[Path],[FileName],[ExtensionId],[ImageHash],[Sha1Hash],[FileSize],[FileState],[ImageComparedThreshold] from [File] Where [Id] in (select [id] from #tempSimilarFileId)", connection)
{
CommandTimeout = 180
})
{
using (var reader = command.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
ImageStoreFile result;
while (reader.Read())
{
result = new ImageStoreFile((Guid)reader[0], (Guid)reader[1], (string)reader[2], (string)reader[3], (Guid)reader[4])
{
ImageHash = DBNullableReader.ConvertFromReferenceType <byte[]>(reader[5]),
Sha1Hash = DBNullableReader.ConvertFromReferenceType <byte[]>(reader[6]),
FileSize = (int)reader[7],
FileStateCode = (int)reader[8],
ImageComparedThreshold = (float)reader[9]
};
allFiles.Add(result.Id, result);
allFileInfo.Add(result.Id, new FileInfo());
}
reader.Close();
}
}
using (var command = new SqlCommand("drop table #tempSimilarFileId", connection))
{
command.ExecuteNonQuery();
}
if (allFiles.Count > 0)
{
allFolders = new Dictionary <Guid, ImageStoreFolder>();
allExtensionNames = new Dictionary <Guid, string>();
foreach (var folder in FolderHelper.GetAllFolders())
{
allFolders.Add(folder.Id, folder);
}
foreach (var extension in ExtensionHelper.GetAllExtensions())
{
allExtensionNames.Add(extension.Id, extension.Extension);
}
Parallel.ForEach(allFiles.Values, i => allFileInfo[i.Id].SetData(i, allFolders[i.FolderId], allExtensionNames[i.ExtensionId]));
}
}
protected override void ProcessRecord()
{
var connection = DatabaseConnection.Current;
using (var command = new SqlCommand(" [Id],[FolderId],[Path],[FileName],[ExtensionId],[ImageHash],[Sha1Hash],[FileSize],[FileState],[ImageComparedThreshold] from [File]"))
{
command.Connection = connection;
command.CommandTimeout = 0;
if (Top.HasValue)
{
command.CommandText = "SELECT TOP " + Top.Value.ToString() + command.CommandText;
}
else
{
command.CommandText = "SELECT" + command.CommandText;
}
WhereCauseBuilder whereCauseBuilder = new WhereCauseBuilder(command.Parameters);
whereCauseBuilder.AddUniqueIdentifierComparingCause("FolderId", FolderId);
whereCauseBuilder.AddStringComparingCause("Path", Path, PathPropertyComparingModes);
whereCauseBuilder.AddStringComparingCause("FileName", FileName, FileNamePropertyComparingModes);
whereCauseBuilder.AddUniqueIdentifierComparingCause("ExtensionId", ExtensionId);
whereCauseBuilder.AddBinaryComparingCause("ImageHash", ImageHash, ImageHashIsNull.IsPresent, 40);
whereCauseBuilder.AddBinaryComparingCause("Sha1Hash", Sha1Hash, Sha1HashIsNull.IsPresent, 20);
whereCauseBuilder.AddIntComparingCause("FileSize", FileSize, FileSizeGreaterOrEqual, FileSizeLessOrEqual);
List <int> fileStates = new List <int>();
if (IncludesNewFile.IsPresent)
{
fileStates.Add(0);
}
if (IncludesNotImage.IsPresent)
{
fileStates.Add(1);
}
if (IncludesNotReadable.IsPresent)
{
fileStates.Add(2);
}
if (IncludesSizeZero.IsPresent)
{
fileStates.Add(254);
}
if (IncludesComputed.IsPresent)
{
fileStates.Add(255);
}
if (fileStates.Count != 5)
{
whereCauseBuilder.AddIntInRangeCause("FileState", fileStates);
}
whereCauseBuilder.AddRealComparingCause("ImageComparedThreshold", ImageComparedThreshold, ImageComparedThresholdGreaterOrEqual, ImageComparedThresholdLessOrEqual);
command.CommandText += whereCauseBuilder.ToFullWhereCommand();
command.CommandText += " order by [FolderId], [Path], [FileName], [ExtensionId], [FileState]";
List <ImageStoreFile> result = new List <ImageStoreFile>();
using (var reader = command.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
ImageStoreFile line = new ImageStoreFile((Guid)reader[0], (Guid)reader[1], (string)reader[2], (string)reader[3], (Guid)reader[4])
{
ImageHash = DBNullableReader.ConvertFromReferenceType <byte[]>(reader[5]),
Sha1Hash = DBNullableReader.ConvertFromReferenceType <byte[]>(reader[6]),
FileSize = (int)reader[7],
FileStateCode = (int)reader[8],
ImageComparedThreshold = (float)reader[9]
};
result.Add(line);
}
reader.Close();
}
WriteObject(result);
}
}
void WriteDatabase()
{
var connection = DatabaseConnection.Current;
using (var commandToDeleteSame = new SqlCommand("Delete from [SameFile] Where [FileId]=@Id"))
using (var commandToDeleteSimilar = new SqlCommand("Delete from [SimilarFile] Where [File1Id]=@Id or [File2Id]=@Id"))
using (var command = new SqlCommand("Update [File] Set [ImageHash]=@ImageHash, [Sha1Hash]=@Sha1Hash, [FileSize]=@FileSize, [FileState]=@FileState, [ImageComparedThreshold]=0 where [Id]=@Id"))
{
commandToDeleteSame.Connection = connection;
commandToDeleteSame.Parameters.Add(new SqlParameter("@Id", System.Data.SqlDbType.UniqueIdentifier));
commandToDeleteSame.CommandTimeout = 180;
commandToDeleteSimilar.Connection = connection;
commandToDeleteSimilar.Parameters.Add(new SqlParameter("@Id", System.Data.SqlDbType.UniqueIdentifier));
commandToDeleteSimilar.CommandTimeout = 180;
command.Connection = connection;
command.Parameters.Add(new SqlParameter("@Id", System.Data.SqlDbType.UniqueIdentifier));
command.Parameters.Add(new SqlParameter("@ImageHash", System.Data.SqlDbType.Binary, 40));
command.Parameters.Add(new SqlParameter("@Sha1Hash", System.Data.SqlDbType.Binary, 20));
command.Parameters.Add(new SqlParameter("@FileSize", System.Data.SqlDbType.Int));
command.Parameters.Add(new SqlParameter("@FileState", System.Data.SqlDbType.Int));
Tuple <Guid, byte[], byte[], int, FileState, bool> record;
while (true)
{
try
{
record = toWrite.Take();
command.Parameters[0].Value = record.Item1;
command.Parameters[1].Value = DBNullableReader.NullCheck(record.Item2);
command.Parameters[2].Value = DBNullableReader.NullCheck(record.Item3);
command.Parameters[3].Value = record.Item4;
command.Parameters[4].Value = (int)record.Item5;
if (command.ExecuteNonQuery() == 0)
{
var text = "Cannot update file record. Id: " + record.Item1.ToString();
outputs.Add(new Tuple <int, string>(2, text));
exceptions.Add(new ErrorRecord(new InvalidOperationException(text), "ImageStore Measuring - Update database", ErrorCategory.WriteError, record.Item1));
}
if (!record.Item6)
{
commandToDeleteSame.Parameters[0].Value = record.Item1;
commandToDeleteSame.ExecuteNonQuery();
commandToDeleteSimilar.Parameters[0].Value = record.Item1;
commandToDeleteSimilar.ExecuteNonQuery();
}
}
catch (InvalidOperationException)
{
outputs.CompleteAdding();
return;
}
catch (Exception ex)
{
outputs.Add(new Tuple <int, string>(2, ex.ToString()));
exceptions.Add(new ErrorRecord(ex, "ImageStore Measuring - Update database", ErrorCategory.ResourceUnavailable, null));
}
}
}
}