public HFileState Execute(ISQLCommandExecutor commandExecutor)
{
//TODO: Pretty bad, make these two happen together
commandExecutor.ExecuteCommand(
command =>
{
command.CommandText
= @"INSERT INTO Files (fileName, deleted)
VALUES (@fileName, 0);";
command.Parameters.AddWithValue("@fileName", _location);
command.ExecuteNonQuery();
}
);
return(commandExecutor.ExecuteCommand(
command =>
{
command.CommandText
= @"SELECT *
FROM Files
WHERE id = last_insert_rowid();";
return command
.ExecuteReader()
.GetHFiles()
.First();
}
));
}
public void Execute(ISQLCommandExecutor commandExecutor)
{
commandExecutor.ExecuteCommand(
command =>
{
command.CommandText = CreateCommand();
command.Parameters.AddWithValue("@id", _file.Id);
command.Parameters.AddWithValue("@fileName", _file.Location);
command.ExecuteNonQuery();
}
);
}
public void Execute(ISQLCommandExecutor commandExecutor)
{
commandExecutor.ExecuteCommand(
command =>
{
command.CommandText
= @"DELETE FROM Images
WHERE deleted = 1;";
command.ExecuteNonQuery();
}
);
}
public IEnumerable <TagState> Execute(ISQLCommandExecutor commandExecutor)
{
return(commandExecutor.ExecuteCommand(
command =>
{
command.CommandText
= @"SELECT * FROM Tags;";
return command
.ExecuteReader()
.GetTags();
}
));
}
public void Execute(ISQLCommandExecutor commandExecutor)
{
commandExecutor.ExecuteCommand(
command =>
{
command.CommandText
= @"UPDATE Files
SET deleted = 1
WHERE id = @id;";
command.Parameters.AddWithValue("@id", _file.Id);
command.ExecuteNonQuery();
}
);
}
public IEnumerable <TagState> Execute(ISQLCommandExecutor commandExecutor)
{
return(commandExecutor.ExecuteCommand(
command =>
{
command.CommandText
= @"SELECT FileTags.tagId, Tags.name
FROM FileTags INNER JOIN Tags
ON FileTags.tagId = Tags.id
WHERE FileTags.fileId = @fileId;";
command.Parameters.AddWithValue("@fileId", _file.Id);
return command.ExecuteReader().GetTags();
}
));
}
public void Execute(ISQLCommandExecutor commandExecutor)
{
commandExecutor.ExecuteCommand(
command =>
{
command.CommandText
= @"INSERT OR REPLACE INTO TagValues
(id, value)
VALUES (@id, @value);";
command.Parameters.AddWithValue("@id", _tag.Id);
command.Parameters.AddWithValue("@value", _value);
command.ExecuteNonQuery();
}
);
}
public List <string> Execute(ISQLCommandExecutor commandExecutor)
{
return(commandExecutor.ExecuteCommand(
command =>
{
command.CommandText
= @"SELECT value
FROM TagValues
WHERE id = @id;";
command.Parameters.AddWithValue("@id", _tag.Id);
return GetValues(command.ExecuteReader()).ToList();
}
));
}
public void Execute(ISQLCommandExecutor commandExecutor)
{
commandExecutor.ExecuteCommand(
command =>
{
command.CommandText
= @"DELETE FROM FileTags
WHERE fileId = @fileId
AND tagId = @tagId;";
command.Parameters.AddWithValue("@fileId", _file.Id);
command.Parameters.AddWithValue("@tagId", _tag.Id);
command.ExecuteNonQuery();
}
);
}
public bool Execute(ISQLCommandExecutor commandExecutor)
{
return(commandExecutor.ExecuteCommand(
command =>
{
command.CommandText
= @"INSERT OR IGNORE INTO FileTags
(fileId, tagId)
VALUES (@fileId, @tagId);";
command.Parameters.AddWithValue("@fileId", _file.Id);
command.Parameters.AddWithValue("@tagId", _tag.Id);
return command.ExecuteNonQuery() == 1;
}
));
}
public void Execute(ISQLCommandExecutor commandExecutor)
{
commandExecutor.ExecuteCommand(
command =>
{
command.CommandText
= @"UPDATE Files
SET (fileHash, perceptualHash) = (@fileHash, @perceptualHash)
WHERE id = @id;";
command.Parameters.AddWithValue("@id", _file.Id);
command.Parameters.AddWithValue("@fileHash", _hash.DataHash);
command.Parameters.AddWithValue("@perceptualHash", _hash.PerceptualHash);
command.ExecuteNonQuery();
}
);
}
public List <HFileState> Execute(ISQLCommandExecutor commandExecutor)
{
return(commandExecutor.ExecuteCommand(
command =>
{
command.CommandText
= @"SELECT *
FROM Files
WHERE deleted = 1;";
return command
.ExecuteReader()
.GetHFiles()
.ToList();
}
));
}
public TagState Execute(ISQLCommandExecutor commandExecutor)
{
var id = (int)commandExecutor.ExecuteCommand(
command =>
{
command.CommandText
= @"INSERT OR REPLACE INTO Tags
(name)
VALUES (@name)";
command.Parameters.AddWithValue("@name", _name);
command.ExecuteNonQuery();
return(command.Connection.LastInsertRowId);
}
);
return(new(id, _name));
}
public FileHash?Execute(ISQLCommandExecutor commandExecutor)
{
return(commandExecutor.ExecuteCommand(
command => {
command.CommandText
= @"SELECT fileHash, perceptualHash
FROM Files
WHERE id = @id;";
command.Parameters.AddWithValue("@id", _file.Id);
var reader = command.ExecuteReader();
// TODO: make extension
return reader.Read()
? new FileHash(reader.GetString(0), reader.GetStringOrNull(1))
: null;
}
));
}
public TagState?Execute(ISQLCommandExecutor commandExecutor)
{
return(commandExecutor.ExecuteCommand(
command =>
{
command.CommandText
= @"SELECT Tags.id, Tags.name
FROM Tags LEFT OUTER JOIN TagValues
ON Tags.id = TagValues.id
WHERE TagValues.value = @value OR Tags.name = @value;";
command.Parameters.AddWithValue("@value", _value);
return command
.ExecuteReader()
.GetTags()
.FirstOrDefault();
}
));
}
public List <HFileState> Execute(ISQLCommandExecutor commandExecutor)
{
return(commandExecutor.ExecuteCommand(
command =>
{
var commandText
= @"SELECT fileId, fileName, fileHash
FROM (" +
GetInnerQuery() +
@"
) WHERE $WHERECLAUSE
$LIMIT;";
commandText = commandText.Replace("$LIMIT", _query.Maximum != int.MaxValue ? $"LIMIT {_query.Maximum}" : "");
commandText = commandText.Replace("$WHERECLAUSE", string.Join(" AND ", BuildWhereClauseParts()));
command.CommandText = commandText;
return command
.ExecuteReader()
.GetHFiles()
.ToList();
}
));
}
public Database(ILogger logger, IDatabaseConnection connection, ISQLCommandExecutor commandExecutor)
{
_logger = logger;
_commandExecutor = commandExecutor;
}
