forked from sim0n00ps/OF-DL
592 lines
26 KiB
C#
592 lines
26 KiB
C#
using System.Text;
|
|
using Microsoft.Data.Sqlite;
|
|
using OF_DL.Helpers;
|
|
using Serilog;
|
|
|
|
namespace OF_DL.Services;
|
|
|
|
public class DbService(IConfigService configService) : IDbService
|
|
{
|
|
/// <summary>
|
|
/// Creates or updates the per-user metadata database.
|
|
/// </summary>
|
|
/// <param name="folder">The user folder path.</param>
|
|
public async Task CreateDb(string folder)
|
|
{
|
|
try
|
|
{
|
|
if (!Directory.Exists(folder + "/Metadata"))
|
|
{
|
|
Directory.CreateDirectory(folder + "/Metadata");
|
|
}
|
|
|
|
string dbFilePath = $"{folder}/Metadata/user_data.db";
|
|
|
|
// connect to the new database file
|
|
await using SqliteConnection connection = new($"Data Source={dbFilePath}");
|
|
// open the connection
|
|
connection.Open();
|
|
|
|
// create the 'medias' table
|
|
await using (SqliteCommand cmd =
|
|
new(
|
|
"CREATE TABLE IF NOT EXISTS medias (id INTEGER NOT NULL, media_id INTEGER, post_id INTEGER NOT NULL, link VARCHAR, directory VARCHAR, filename VARCHAR, size INTEGER, api_type VARCHAR, media_type VARCHAR, preview INTEGER, linked VARCHAR, downloaded INTEGER, created_at TIMESTAMP, record_created_at TIMESTAMP, PRIMARY KEY(id), UNIQUE(media_id));",
|
|
connection))
|
|
{
|
|
await cmd.ExecuteNonQueryAsync();
|
|
}
|
|
|
|
await EnsureCreatedAtColumnExists(connection, "medias");
|
|
|
|
//
|
|
// Alter existing databases to create unique constraint on `medias`
|
|
//
|
|
await using (SqliteCommand cmd = new(@"
|
|
PRAGMA foreign_keys=off;
|
|
|
|
BEGIN TRANSACTION;
|
|
|
|
ALTER TABLE medias RENAME TO old_medias;
|
|
|
|
CREATE TABLE medias (
|
|
id INTEGER NOT NULL,
|
|
media_id INTEGER,
|
|
post_id INTEGER NOT NULL,
|
|
link VARCHAR,
|
|
directory VARCHAR,
|
|
filename VARCHAR,
|
|
size INTEGER,
|
|
api_type VARCHAR,
|
|
media_type VARCHAR,
|
|
preview INTEGER,
|
|
linked VARCHAR,
|
|
downloaded INTEGER,
|
|
created_at TIMESTAMP,
|
|
record_created_at TIMESTAMP,
|
|
PRIMARY KEY(id),
|
|
UNIQUE(media_id, api_type)
|
|
);
|
|
|
|
INSERT INTO medias SELECT * FROM old_medias;
|
|
|
|
DROP TABLE old_medias;
|
|
|
|
COMMIT;
|
|
|
|
PRAGMA foreign_keys=on;", connection))
|
|
{
|
|
await cmd.ExecuteNonQueryAsync();
|
|
}
|
|
|
|
// create the 'messages' table
|
|
await using (SqliteCommand cmd =
|
|
new(
|
|
"CREATE TABLE IF NOT EXISTS messages (id INTEGER NOT NULL, post_id INTEGER NOT NULL, text VARCHAR, price INTEGER, paid INTEGER, archived BOOLEAN, created_at TIMESTAMP, user_id INTEGER, record_created_at TIMESTAMP, PRIMARY KEY(id), UNIQUE(post_id));",
|
|
connection))
|
|
{
|
|
await cmd.ExecuteNonQueryAsync();
|
|
}
|
|
|
|
// create the 'posts' table
|
|
await using (SqliteCommand cmd =
|
|
new(
|
|
"CREATE TABLE IF NOT EXISTS posts (id INTEGER NOT NULL, post_id INTEGER NOT NULL, text VARCHAR, price INTEGER, paid INTEGER, archived BOOLEAN, created_at TIMESTAMP, record_created_at TIMESTAMP, PRIMARY KEY(id), UNIQUE(post_id));",
|
|
connection))
|
|
{
|
|
await cmd.ExecuteNonQueryAsync();
|
|
}
|
|
|
|
// create the 'stories' table
|
|
await using (SqliteCommand cmd =
|
|
new(
|
|
"CREATE TABLE IF NOT EXISTS stories (id INTEGER NOT NULL, post_id INTEGER NOT NULL, text VARCHAR, price INTEGER, paid INTEGER, archived BOOLEAN, created_at TIMESTAMP, record_created_at TIMESTAMP, PRIMARY KEY(id), UNIQUE(post_id));",
|
|
connection))
|
|
{
|
|
await cmd.ExecuteNonQueryAsync();
|
|
}
|
|
|
|
// create the 'others' table
|
|
await using (SqliteCommand cmd =
|
|
new(
|
|
"CREATE TABLE IF NOT EXISTS others (id INTEGER NOT NULL, post_id INTEGER NOT NULL, text VARCHAR, price INTEGER, paid INTEGER, archived BOOLEAN, created_at TIMESTAMP, record_created_at TIMESTAMP, PRIMARY KEY(id), UNIQUE(post_id));",
|
|
connection))
|
|
{
|
|
await cmd.ExecuteNonQueryAsync();
|
|
}
|
|
|
|
// create the 'products' table
|
|
await using (SqliteCommand cmd =
|
|
new(
|
|
"CREATE TABLE IF NOT EXISTS products (id INTEGER NOT NULL, post_id INTEGER NOT NULL, text VARCHAR, price INTEGER, paid INTEGER, archived BOOLEAN, created_at TIMESTAMP, title VARCHAR, record_created_at TIMESTAMP, PRIMARY KEY(id), UNIQUE(post_id));",
|
|
connection))
|
|
{
|
|
await cmd.ExecuteNonQueryAsync();
|
|
}
|
|
|
|
// create the 'profiles' table
|
|
await using (SqliteCommand cmd =
|
|
new(
|
|
"CREATE TABLE IF NOT EXISTS profiles (id INTEGER NOT NULL, user_id INTEGER NOT NULL, username VARCHAR NOT NULL, record_created_at TIMESTAMP, PRIMARY KEY(id), UNIQUE(username));",
|
|
connection))
|
|
{
|
|
await cmd.ExecuteNonQueryAsync();
|
|
}
|
|
|
|
connection.Close();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ExceptionLoggerHelper.LogException(ex);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Creates or updates the global users database.
|
|
/// </summary>
|
|
/// <param name="users">The users to seed or update.</param>
|
|
public async Task CreateUsersDb(Dictionary<string, long> users)
|
|
{
|
|
try
|
|
{
|
|
await using SqliteConnection connection = new($"Data Source={Directory.GetCurrentDirectory()}/users.db");
|
|
Log.Debug("Database data source: " + connection.DataSource);
|
|
|
|
connection.Open();
|
|
|
|
await using (SqliteCommand cmd =
|
|
new(
|
|
"CREATE TABLE IF NOT EXISTS users (id INTEGER NOT NULL, user_id INTEGER NOT NULL, username VARCHAR NOT NULL, PRIMARY KEY(id), UNIQUE(username));",
|
|
connection))
|
|
{
|
|
await cmd.ExecuteNonQueryAsync();
|
|
}
|
|
|
|
Log.Debug("Adding missing creators");
|
|
foreach (KeyValuePair<string, long> user in users)
|
|
{
|
|
await using SqliteCommand checkCmd = new("SELECT user_id, username FROM users WHERE user_id = @userId;",
|
|
connection);
|
|
checkCmd.Parameters.AddWithValue("@userId", user.Value);
|
|
await using SqliteDataReader reader = await checkCmd.ExecuteReaderAsync();
|
|
if (!reader.Read())
|
|
{
|
|
await using SqliteCommand insertCmd =
|
|
new("INSERT INTO users (user_id, username) VALUES (@userId, @username);",
|
|
connection);
|
|
insertCmd.Parameters.AddWithValue("@userId", user.Value);
|
|
insertCmd.Parameters.AddWithValue("@username", user.Key);
|
|
await insertCmd.ExecuteNonQueryAsync();
|
|
Log.Debug("Inserted new creator: " + user.Key);
|
|
}
|
|
else
|
|
{
|
|
Log.Debug("Creator " + user.Key + " already exists");
|
|
}
|
|
}
|
|
|
|
connection.Close();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ExceptionLoggerHelper.LogException(ex);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Ensures a username matches the stored user ID and migrates folders if needed.
|
|
/// </summary>
|
|
/// <param name="user">The user pair to validate.</param>
|
|
/// <param name="path">The expected user folder path.</param>
|
|
public async Task CheckUsername(KeyValuePair<string, long> user, string path)
|
|
{
|
|
try
|
|
{
|
|
await using SqliteConnection connection = new($"Data Source={Directory.GetCurrentDirectory()}/users.db");
|
|
|
|
connection.Open();
|
|
|
|
await using (SqliteCommand checkCmd = new("SELECT user_id, username FROM users WHERE user_id = @userId;",
|
|
connection))
|
|
{
|
|
checkCmd.Parameters.AddWithValue("@userId", user.Value);
|
|
await using (SqliteDataReader reader = await checkCmd.ExecuteReaderAsync())
|
|
{
|
|
if (reader.Read())
|
|
{
|
|
string storedUsername = reader.GetString(1);
|
|
|
|
if (storedUsername != user.Key)
|
|
{
|
|
await using (SqliteCommand updateCmd =
|
|
new("UPDATE users SET username = @newUsername WHERE user_id = @userId;",
|
|
connection))
|
|
{
|
|
updateCmd.Parameters.AddWithValue("@newUsername", user.Key);
|
|
updateCmd.Parameters.AddWithValue("@userId", user.Value);
|
|
await updateCmd.ExecuteNonQueryAsync();
|
|
}
|
|
|
|
string oldPath = path.Replace(path.Split("/")[^1], storedUsername);
|
|
|
|
if (Directory.Exists(oldPath))
|
|
{
|
|
Directory.Move(path.Replace(path.Split("/")[^1], storedUsername), path);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
connection.Close();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ExceptionLoggerHelper.LogException(ex);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Inserts a message record when it does not already exist.
|
|
/// </summary>
|
|
/// <param name="folder">The user folder path.</param>
|
|
/// <param name="postId">The message or post ID.</param>
|
|
/// <param name="messageText">The message text.</param>
|
|
/// <param name="price">The price string.</param>
|
|
/// <param name="isPaid">Whether the message is paid.</param>
|
|
/// <param name="isArchived">Whether the message is archived.</param>
|
|
/// <param name="createdAt">The creation timestamp.</param>
|
|
/// <param name="userId">The sender user ID.</param>
|
|
public async Task AddMessage(string folder, long postId, string messageText, string price, bool isPaid,
|
|
bool isArchived, DateTime createdAt, long userId)
|
|
{
|
|
try
|
|
{
|
|
await using SqliteConnection connection = new($"Data Source={folder}/Metadata/user_data.db");
|
|
connection.Open();
|
|
await EnsureCreatedAtColumnExists(connection, "messages");
|
|
await using SqliteCommand cmd = new("SELECT COUNT(*) FROM messages WHERE post_id=@post_id", connection);
|
|
cmd.Parameters.AddWithValue("@post_id", postId);
|
|
int count = Convert.ToInt32(await cmd.ExecuteScalarAsync());
|
|
if (count == 0)
|
|
{
|
|
// If the record doesn't exist, insert a new one
|
|
await using SqliteCommand insertCmd =
|
|
new(
|
|
"INSERT INTO messages(post_id, text, price, paid, archived, created_at, user_id, record_created_at) VALUES(@post_id, @message_text, @price, @is_paid, @is_archived, @created_at, @user_id, @record_created_at)",
|
|
connection);
|
|
insertCmd.Parameters.AddWithValue("@post_id", postId);
|
|
insertCmd.Parameters.AddWithValue("@message_text", messageText);
|
|
insertCmd.Parameters.AddWithValue("@price", price);
|
|
insertCmd.Parameters.AddWithValue("@is_paid", isPaid);
|
|
insertCmd.Parameters.AddWithValue("@is_archived", isArchived);
|
|
insertCmd.Parameters.AddWithValue("@created_at", createdAt);
|
|
insertCmd.Parameters.AddWithValue("@user_id", userId);
|
|
insertCmd.Parameters.AddWithValue("@record_created_at", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
await insertCmd.ExecuteNonQueryAsync();
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ExceptionLoggerHelper.LogException(ex);
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// Inserts a post record when it does not already exist.
|
|
/// </summary>
|
|
/// <param name="folder">The user folder path.</param>
|
|
/// <param name="postId">The post ID.</param>
|
|
/// <param name="messageText">The post text.</param>
|
|
/// <param name="price">The price string.</param>
|
|
/// <param name="isPaid">Whether the post is paid.</param>
|
|
/// <param name="isArchived">Whether the post is archived.</param>
|
|
/// <param name="createdAt">The creation timestamp.</param>
|
|
public async Task AddPost(string folder, long postId, string messageText, string price, bool isPaid,
|
|
bool isArchived, DateTime createdAt)
|
|
{
|
|
try
|
|
{
|
|
await using SqliteConnection connection = new($"Data Source={folder}/Metadata/user_data.db");
|
|
connection.Open();
|
|
await EnsureCreatedAtColumnExists(connection, "posts");
|
|
await using SqliteCommand cmd = new("SELECT COUNT(*) FROM posts WHERE post_id=@post_id", connection);
|
|
cmd.Parameters.AddWithValue("@post_id", postId);
|
|
int count = Convert.ToInt32(await cmd.ExecuteScalarAsync());
|
|
if (count == 0)
|
|
{
|
|
// If the record doesn't exist, insert a new one
|
|
await using SqliteCommand insertCmd =
|
|
new(
|
|
"INSERT INTO posts(post_id, text, price, paid, archived, created_at, record_created_at) VALUES(@post_id, @message_text, @price, @is_paid, @is_archived, @created_at, @record_created_at)",
|
|
connection);
|
|
insertCmd.Parameters.AddWithValue("@post_id", postId);
|
|
insertCmd.Parameters.AddWithValue("@message_text", messageText);
|
|
insertCmd.Parameters.AddWithValue("@price", price);
|
|
insertCmd.Parameters.AddWithValue("@is_paid", isPaid);
|
|
insertCmd.Parameters.AddWithValue("@is_archived", isArchived);
|
|
insertCmd.Parameters.AddWithValue("@created_at", createdAt);
|
|
insertCmd.Parameters.AddWithValue("@record_created_at", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
await insertCmd.ExecuteNonQueryAsync();
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ExceptionLoggerHelper.LogException(ex);
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// Inserts a story record when it does not already exist.
|
|
/// </summary>
|
|
/// <param name="folder">The user folder path.</param>
|
|
/// <param name="postId">The story ID.</param>
|
|
/// <param name="messageText">The story text.</param>
|
|
/// <param name="price">The price string.</param>
|
|
/// <param name="isPaid">Whether the story is paid.</param>
|
|
/// <param name="isArchived">Whether the story is archived.</param>
|
|
/// <param name="createdAt">The creation timestamp.</param>
|
|
public async Task AddStory(string folder, long postId, string messageText, string price, bool isPaid,
|
|
bool isArchived, DateTime createdAt)
|
|
{
|
|
try
|
|
{
|
|
await using SqliteConnection connection = new($"Data Source={folder}/Metadata/user_data.db");
|
|
connection.Open();
|
|
await EnsureCreatedAtColumnExists(connection, "stories");
|
|
await using SqliteCommand cmd = new("SELECT COUNT(*) FROM stories WHERE post_id=@post_id", connection);
|
|
cmd.Parameters.AddWithValue("@post_id", postId);
|
|
int count = Convert.ToInt32(await cmd.ExecuteScalarAsync());
|
|
if (count == 0)
|
|
{
|
|
// If the record doesn't exist, insert a new one
|
|
await using SqliteCommand insertCmd =
|
|
new(
|
|
"INSERT INTO stories(post_id, text, price, paid, archived, created_at, record_created_at) VALUES(@post_id, @message_text, @price, @is_paid, @is_archived, @created_at, @record_created_at)",
|
|
connection);
|
|
insertCmd.Parameters.AddWithValue("@post_id", postId);
|
|
insertCmd.Parameters.AddWithValue("@message_text", messageText);
|
|
insertCmd.Parameters.AddWithValue("@price", price);
|
|
insertCmd.Parameters.AddWithValue("@is_paid", isPaid);
|
|
insertCmd.Parameters.AddWithValue("@is_archived", isArchived);
|
|
insertCmd.Parameters.AddWithValue("@created_at", createdAt);
|
|
insertCmd.Parameters.AddWithValue("@record_created_at", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
await insertCmd.ExecuteNonQueryAsync();
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ExceptionLoggerHelper.LogException(ex);
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// Inserts a media record when it does not already exist.
|
|
/// </summary>
|
|
/// <param name="folder">The user folder path.</param>
|
|
/// <param name="mediaId">The media ID.</param>
|
|
/// <param name="postId">The parent post ID.</param>
|
|
/// <param name="link">The media URL.</param>
|
|
/// <param name="directory">The local directory path.</param>
|
|
/// <param name="filename">The local filename.</param>
|
|
/// <param name="size">The media size in bytes.</param>
|
|
/// <param name="apiType">The API type label.</param>
|
|
/// <param name="mediaType">The media type label.</param>
|
|
/// <param name="preview">Whether the media is a preview.</param>
|
|
/// <param name="downloaded">Whether the media is downloaded.</param>
|
|
/// <param name="createdAt">The creation timestamp.</param>
|
|
public async Task AddMedia(string folder, long mediaId, long postId, string link, string? directory,
|
|
string? filename, long? size, string apiType, string mediaType, bool preview, bool downloaded,
|
|
DateTime? createdAt)
|
|
{
|
|
try
|
|
{
|
|
await using SqliteConnection connection = new($"Data Source={folder}/Metadata/user_data.db");
|
|
connection.Open();
|
|
await EnsureCreatedAtColumnExists(connection, "medias");
|
|
StringBuilder sql = new("SELECT COUNT(*) FROM medias WHERE media_id=@media_id");
|
|
if (configService.CurrentConfig.DownloadDuplicatedMedia)
|
|
{
|
|
sql.Append(" and api_type=@api_type");
|
|
}
|
|
|
|
await using SqliteCommand cmd = new(sql.ToString(), connection);
|
|
cmd.Parameters.AddWithValue("@media_id", mediaId);
|
|
cmd.Parameters.AddWithValue("@api_type", apiType);
|
|
int count = Convert.ToInt32(cmd.ExecuteScalar());
|
|
if (count == 0)
|
|
{
|
|
// If the record doesn't exist, insert a new one
|
|
await using SqliteCommand insertCmd = new(
|
|
$"INSERT INTO medias(media_id, post_id, link, directory, filename, size, api_type, media_type, preview, downloaded, created_at, record_created_at) VALUES({mediaId}, {postId}, '{link}', '{directory ?? "NULL"}', '{filename ?? "NULL"}', {size?.ToString() ?? "NULL"}, '{apiType}', '{mediaType}', {Convert.ToInt32(preview)}, {Convert.ToInt32(downloaded)}, '{createdAt?.ToString("yyyy-MM-dd HH:mm:ss")}', '{DateTime.Now:yyyy-MM-dd HH:mm:ss}')",
|
|
connection);
|
|
await insertCmd.ExecuteNonQueryAsync();
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ExceptionLoggerHelper.LogException(ex);
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// Checks whether the media has been marked as downloaded.
|
|
/// </summary>
|
|
/// <param name="folder">The user folder path.</param>
|
|
/// <param name="mediaId">The media ID.</param>
|
|
/// <param name="apiType">The API type label.</param>
|
|
/// <returns>True when the media is marked as downloaded.</returns>
|
|
public async Task<bool> CheckDownloaded(string folder, long mediaId, string apiType)
|
|
{
|
|
try
|
|
{
|
|
await using SqliteConnection connection = new($"Data Source={folder}/Metadata/user_data.db");
|
|
StringBuilder sql = new("SELECT downloaded FROM medias WHERE media_id=@media_id");
|
|
if (configService.CurrentConfig.DownloadDuplicatedMedia)
|
|
{
|
|
sql.Append(" and api_type=@api_type");
|
|
}
|
|
|
|
connection.Open();
|
|
await using SqliteCommand cmd = new(sql.ToString(), connection);
|
|
cmd.Parameters.AddWithValue("@media_id", mediaId);
|
|
cmd.Parameters.AddWithValue("@api_type", apiType);
|
|
bool downloaded = Convert.ToBoolean(await cmd.ExecuteScalarAsync());
|
|
|
|
return downloaded;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ExceptionLoggerHelper.LogException(ex);
|
|
}
|
|
|
|
return false;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// Updates the media record with local file details.
|
|
/// </summary>
|
|
/// <param name="folder">The user folder path.</param>
|
|
/// <param name="mediaId">The media ID.</param>
|
|
/// <param name="apiType">The API type label.</param>
|
|
/// <param name="directory">The local directory path.</param>
|
|
/// <param name="filename">The local filename.</param>
|
|
/// <param name="size">The file size in bytes.</param>
|
|
/// <param name="downloaded">Whether the media is downloaded.</param>
|
|
/// <param name="createdAt">The creation timestamp.</param>
|
|
public async Task UpdateMedia(string folder, long mediaId, string apiType, string directory, string filename,
|
|
long size, bool downloaded, DateTime createdAt)
|
|
{
|
|
await using SqliteConnection connection = new($"Data Source={folder}/Metadata/user_data.db");
|
|
connection.Open();
|
|
|
|
// Construct the update command
|
|
StringBuilder sql =
|
|
new(
|
|
"UPDATE medias SET directory=@directory, filename=@filename, size=@size, downloaded=@downloaded, created_at=@created_at WHERE media_id=@media_id");
|
|
if (configService.CurrentConfig.DownloadDuplicatedMedia)
|
|
{
|
|
sql.Append(" and api_type=@api_type");
|
|
}
|
|
|
|
// Create a new command object
|
|
await using SqliteCommand command = new(sql.ToString(), connection);
|
|
// Add parameters to the command object
|
|
command.Parameters.AddWithValue("@directory", directory);
|
|
command.Parameters.AddWithValue("@filename", filename);
|
|
command.Parameters.AddWithValue("@size", size);
|
|
command.Parameters.AddWithValue("@downloaded", downloaded ? 1 : 0);
|
|
command.Parameters.AddWithValue("@created_at", createdAt);
|
|
command.Parameters.AddWithValue("@media_id", mediaId);
|
|
command.Parameters.AddWithValue("@api_type", apiType);
|
|
|
|
// Execute the command
|
|
await command.ExecuteNonQueryAsync();
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// Returns the stored size for a media record.
|
|
/// </summary>
|
|
/// <param name="folder">The user folder path.</param>
|
|
/// <param name="mediaId">The media ID.</param>
|
|
/// <param name="apiType">The API type label.</param>
|
|
/// <returns>The stored file size.</returns>
|
|
public async Task<long> GetStoredFileSize(string folder, long mediaId, string apiType)
|
|
{
|
|
await using SqliteConnection connection = new($"Data Source={folder}/Metadata/user_data.db");
|
|
connection.Open();
|
|
await using SqliteCommand cmd = new("SELECT size FROM medias WHERE media_id=@media_id and api_type=@api_type",
|
|
connection);
|
|
cmd.Parameters.AddWithValue("@media_id", mediaId);
|
|
cmd.Parameters.AddWithValue("@api_type", apiType);
|
|
long size = Convert.ToInt64(await cmd.ExecuteScalarAsync());
|
|
|
|
return size;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Returns the most recent post date based on downloaded and pending media.
|
|
/// </summary>
|
|
/// <param name="folder">The user folder path.</param>
|
|
/// <returns>The most recent post date if available.</returns>
|
|
public async Task<DateTime?> GetMostRecentPostDate(string folder)
|
|
{
|
|
DateTime? mostRecentDate = null;
|
|
await using SqliteConnection connection = new($"Data Source={folder}/Metadata/user_data.db");
|
|
connection.Open();
|
|
await using SqliteCommand cmd = new(@"
|
|
SELECT
|
|
MIN(created_at) AS created_at
|
|
FROM (
|
|
SELECT MAX(P.created_at) AS created_at
|
|
FROM posts AS P
|
|
LEFT OUTER JOIN medias AS m
|
|
ON P.post_id = m.post_id
|
|
AND m.downloaded = 1
|
|
UNION
|
|
SELECT MIN(P.created_at) AS created_at
|
|
FROM posts AS P
|
|
INNER JOIN medias AS m
|
|
ON P.post_id = m.post_id
|
|
WHERE m.downloaded = 0
|
|
)", connection);
|
|
object? scalarValue = await cmd.ExecuteScalarAsync();
|
|
if (scalarValue != null && scalarValue != DBNull.Value)
|
|
{
|
|
mostRecentDate = Convert.ToDateTime(scalarValue);
|
|
}
|
|
|
|
return mostRecentDate;
|
|
}
|
|
|
|
private async Task EnsureCreatedAtColumnExists(SqliteConnection connection, string tableName)
|
|
{
|
|
await using SqliteCommand cmd = new($"PRAGMA table_info({tableName});", connection);
|
|
await using SqliteDataReader reader = await cmd.ExecuteReaderAsync();
|
|
bool columnExists = false;
|
|
|
|
while (await reader.ReadAsync())
|
|
{
|
|
if (reader["name"].ToString() != "record_created_at")
|
|
{
|
|
continue;
|
|
}
|
|
|
|
columnExists = true;
|
|
break;
|
|
}
|
|
|
|
if (!columnExists)
|
|
{
|
|
await using SqliteCommand alterCmd = new($"ALTER TABLE {tableName} ADD COLUMN record_created_at TIMESTAMP;",
|
|
connection);
|
|
await alterCmd.ExecuteNonQueryAsync();
|
|
}
|
|
}
|
|
}
|