public long CreateLibrary(CreateLibraryDto library, string guid = null)
{
if (guid == null)
{
guid = UniqueIdUtil.GenerateUniqueId();
}
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
long createdLibraryId = -1;
using (var txn = db.BeginTransaction()) {
var command = new SqliteCommand();
command.Connection = db;
command.CommandText = "INSERT INTO library VALUES(NULL, @Name, @BackgroundId, false, @UniqueId)";
command.Parameters.AddWithValue("@Name", library.Name);
command.Parameters.AddWithValue("@BackgroundId", QueryUtil.GetNullableIdForStorage(library.BackgroundImageId));
command.Parameters.AddWithValue("@UniqueId", guid);
command.Transaction = txn;
command.ExecuteNonQuery();
createdLibraryId = QueryUtil.GetLastInsertedPrimaryKey(db, txn);
foreach (var setting in LibrarySettings.DEFAULT_SETTINGS)
{
var createSettingsCommand = new SqliteCommand($"INSERT INTO library_setting VALUES({createdLibraryId}, '{setting.Key}', '{setting.Value}')", db, txn);
createSettingsCommand.ExecuteNonQuery();
}
txn.Commit();
}
return(createdLibraryId);
}
}
private long CreateVideoSequence(SqliteConnection db, SqliteTransaction txn, CreateVideoSequenceDto dto)
{
var command = GetCreateVideoSequenceCommand(db, dto);
command.Transaction = txn;
command.ExecuteNonQuery();
return(QueryUtil.GetLastInsertedPrimaryKey(db, txn));
}
public long CreateSeries(CreateSeriesDto dto)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var command = GetCreateSeriesCommand(db, dto, UniqueIdUtil.GenerateUniqueId(), false);
command.ExecuteNonQuery();
return(QueryUtil.GetLastInsertedPrimaryKey(db));
}
}
public async Task <long> AddWatchedVideo(long videoId)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var command = new SqliteCommand($"INSERT INTO watch_history(video_id, watch_date) VALUES({videoId}, {"CURRENT_TIMESTAMP"})", db);
command.ExecuteNonQuery();
return(QueryUtil.GetLastInsertedPrimaryKey(db));
}
}
private long CreateTag(CreateTagDto tag, SqliteConnection db, SqliteTransaction txn)
{
var tagCommand = new SqliteCommand("INSERT INTO tag(type, name, deleted, unique_id) VALUES (@TagType, @TagName, false, @UniqueId)", db, txn);
tagCommand.Parameters.AddWithValue("@TagType", tag.Type);
tagCommand.Parameters.AddWithValue("@TagName", tag.Name);
tagCommand.Parameters.AddWithValue("@UniqueId", UniqueIdUtil.GenerateUniqueId());
tagCommand.ExecuteNonQuery();
return(QueryUtil.GetLastInsertedPrimaryKey(db, txn));
}
public long UpdateLocation(Location location)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var command = GetUpdateLocationCommand(db, location, "location_id", location.LocationId);
command.ExecuteNonQuery();
return(QueryUtil.GetLastInsertedPrimaryKey(db));
}
}
public long UpdateTag(Tag tag)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var tagCommand = new SqliteCommand($"UPDATE tag SET type = @TagType, name = @TagName WHERE tag_id = @TagId", db);
tagCommand.Parameters.AddWithValue("@TagType", tag.Type);
tagCommand.Parameters.AddWithValue("@TagName", tag.Name);
tagCommand.Parameters.AddWithValue("@TagId", tag.TagId);
tagCommand.ExecuteNonQuery();
return(QueryUtil.GetLastInsertedPrimaryKey(db));
}
}
