/// <summary>
/// Create a new <see cref="SQLite3Transaction" />
/// </summary>
/// <param name="connection">
/// The <see cref="SQLite3" /> connection on which to create the transaction
/// </param>
internal SQLite3Transaction(SQLite3 connection)
{
if (connection == null)
throw new ArgumentNullException("connection");
if (connection.InTransaction)
throw new SQLite3Exception("There is a transaction present");
_connection = connection;
_connection.Query("BEGIN");
}
private static void Main()
{
string filename = Path.Combine(
Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments),
"test.sqlite"
);
using (var db = new SQLite3(filename))
{
try
{
using (SQLite3Transaction transaction = db.BeginTransaction())
{
db.Query("CREATE TABLE User ( ID INTEGER PRIMARY KEY, Name TEXT UNIQUE );");
db.Query("INSERT INTO User (Name) VALUES ('John')");
db.Query("INSERT INTO User (Name) VALUES ('Jeff')");
db.Query("INSERT INTO User (Name) VALUES ('Jesus')");
transaction.Commit();
}
}
catch (SQLite3Exception ex)
{
Console.WriteLine("EXCEPTION: " + ex.Message);
}
using (IEnumerator<User> enumerator = db.QueryEnumerator<User>("SELECT ID, Name FROM User"))
{
enumerator.MoveNext();
enumerator.MoveNext();
Console.WriteLine(enumerator.Current);
enumerator.Reset();
while (enumerator.MoveNext())
Console.WriteLine(enumerator.Current);
}
}
}
protected ManagedConnection CreateConnection(bool isReadOnly = false)
{
if (_connection != null)
{
return(_connection);
}
lock (WriteLock)
{
if (!_versionLogged)
{
_versionLogged = true;
_logger.LogInformation("Sqlite version: {ver}", SQLite3.Version);
_logger.LogInformation("Sqlite compiler options: {opts}", string.Join(",", SQLite3.CompilerOptions.ToArray()));
}
ConnectionFlags connectionFlags;
if (isReadOnly)
{
//_logger.LogInformation("Opening read connection");
//connectionFlags = ConnectionFlags.ReadOnly;
connectionFlags = ConnectionFlags.Create;
connectionFlags |= ConnectionFlags.ReadWrite;
}
else
{
//_logger.LogInformation("Opening write connection");
connectionFlags = ConnectionFlags.Create;
connectionFlags |= ConnectionFlags.ReadWrite;
}
if (EnableSingleConnection)
{
connectionFlags |= ConnectionFlags.PrivateCache;
}
else
{
connectionFlags |= ConnectionFlags.SharedCached;
}
connectionFlags |= ConnectionFlags.NoMutex;
var db = SQLite3.Open(DbFilePath, connectionFlags, null);
try
{
if (string.IsNullOrWhiteSpace(_defaultWal))
{
_defaultWal = db.Query("PRAGMA journal_mode").SelectScalarString().First();
_logger.LogInformation("Default journal_mode for {0} is {1}", DbFilePath, _defaultWal);
}
var queries = new List <string>
{
//"PRAGMA cache size=-10000"
//"PRAGMA read_uncommitted = true",
"PRAGMA synchronous=Normal"
};
if (CacheSize.HasValue)
{
queries.Add("PRAGMA cache_size=" + CacheSize.Value.ToString(CultureInfo.InvariantCulture));
}
if (EnableTempStoreMemory)
{
queries.Add("PRAGMA temp_store = memory");
}
else
{
queries.Add("PRAGMA temp_store = file");
}
//foreach (var query in queries)
//{
// db.Execute(query);
//}
db.ExecuteAll(string.Join(";", queries.ToArray()));
}
catch
{
using (db)
{
}
throw;
}
_connection = new ManagedConnection(db, false);
return(_connection);
}
}
/// <inheritdoc />
public void Perform()
{
var dataPath = _appPaths.DataPath;
using (var connection = SQLite3.Open(
Path.Combine(dataPath, DbFilename),
ConnectionFlags.ReadOnly,
null))
{
using var dbContext = _dbProvider.CreateContext();
var authenticatedDevices = connection.Query("SELECT * FROM Tokens");
foreach (var row in authenticatedDevices)
{
var dateCreatedStr = row[9].ToString();
_ = DateTime.TryParse(dateCreatedStr, out var dateCreated);
var dateLastActivityStr = row[10].ToString();
_ = DateTime.TryParse(dateLastActivityStr, out var dateLastActivity);
if (row[6].IsDbNull())
{
dbContext.ApiKeys.Add(new ApiKey(row[3].ToString())
{
AccessToken = row[1].ToString(),
DateCreated = dateCreated,
DateLastActivity = dateLastActivity
});
}
else
{
var userId = new Guid(row[6].ToString());
var user = _userManager.GetUserById(userId);
if (user is null)
{
// User doesn't exist, don't bring over the device.
continue;
}
dbContext.Devices.Add(new Device(
new Guid(row[6].ToString()),
row[3].ToString(),
row[4].ToString(),
row[5].ToString(),
row[2].ToString())
{
AccessToken = row[1].ToString(),
IsActive = row[8].ToBool(),
DateCreated = dateCreated,
DateLastActivity = dateLastActivity
});
}
}
var deviceOptions = connection.Query("SELECT * FROM Devices");
var deviceIds = new HashSet <string>();
foreach (var row in deviceOptions)
{
if (row[2].IsDbNull())
{
continue;
}
var deviceId = row[2].ToString();
if (deviceIds.Contains(deviceId))
{
continue;
}
deviceIds.Add(deviceId);
dbContext.DeviceOptions.Add(new DeviceOptions(deviceId)
{
CustomName = row[1].IsDbNull() ? null : row[1].ToString()
});
}
dbContext.SaveChanges();
}
try
{
File.Move(Path.Combine(dataPath, DbFilename), Path.Combine(dataPath, DbFilename + ".old"));
var journalPath = Path.Combine(dataPath, DbFilename + "-journal");
if (File.Exists(journalPath))
{
File.Move(journalPath, Path.Combine(dataPath, DbFilename + ".old-journal"));
}
}
catch (IOException e)
{
_logger.LogError(e, "Error renaming legacy activity log database to 'authentication.db.old'");
}
}
/// <inheritdoc/>
public void Perform()
{
var dataPath = _paths.DataPath;
_logger.LogInformation("Migrating the user database may take a while, do not stop Jellyfin.");
using (var connection = SQLite3.Open(Path.Combine(dataPath, DbFilename), ConnectionFlags.ReadOnly, null))
{
var dbContext = _provider.CreateContext();
var queryResult = connection.Query("SELECT * FROM LocalUsersv2");
dbContext.RemoveRange(dbContext.Users);
dbContext.SaveChanges();
foreach (var entry in queryResult)
{
UserMockup?mockup = JsonSerializer.Deserialize <UserMockup>(entry[2].ToBlob(), JsonDefaults.GetOptions());
if (mockup == null)
{
continue;
}
var userDataDir = Path.Combine(_paths.UserConfigurationDirectoryPath, mockup.Name);
var config = File.Exists(Path.Combine(userDataDir, "config.xml"))
? (UserConfiguration)_xmlSerializer.DeserializeFromFile(typeof(UserConfiguration), Path.Combine(userDataDir, "config.xml"))
: new UserConfiguration();
var policy = File.Exists(Path.Combine(userDataDir, "policy.xml"))
? (UserPolicy)_xmlSerializer.DeserializeFromFile(typeof(UserPolicy), Path.Combine(userDataDir, "policy.xml"))
: new UserPolicy();
policy.AuthenticationProviderId = policy.AuthenticationProviderId?.Replace(
"Emby.Server.Implementations.Library",
"Jellyfin.Server.Implementations.Users",
StringComparison.Ordinal)
?? typeof(DefaultAuthenticationProvider).FullName;
policy.PasswordResetProviderId = typeof(DefaultPasswordResetProvider).FullName;
int?maxLoginAttempts = policy.LoginAttemptsBeforeLockout switch
{
-1 => null,
0 => 3,
_ => policy.LoginAttemptsBeforeLockout
};
var user = new User(mockup.Name, policy.AuthenticationProviderId, policy.PasswordResetProviderId)
{
Id = entry[1].ReadGuidFromBlob(),
InternalId = entry[0].ToInt64(),
MaxParentalAgeRating = policy.MaxParentalRating,
EnableUserPreferenceAccess = policy.EnableUserPreferenceAccess,
RemoteClientBitrateLimit = policy.RemoteClientBitrateLimit,
InvalidLoginAttemptCount = policy.InvalidLoginAttemptCount,
LoginAttemptsBeforeLockout = maxLoginAttempts,
SubtitleMode = config.SubtitleMode,
HidePlayedInLatest = config.HidePlayedInLatest,
EnableLocalPassword = config.EnableLocalPassword,
PlayDefaultAudioTrack = config.PlayDefaultAudioTrack,
DisplayCollectionsView = config.DisplayCollectionsView,
DisplayMissingEpisodes = config.DisplayMissingEpisodes,
AudioLanguagePreference = config.AudioLanguagePreference,
RememberAudioSelections = config.RememberAudioSelections,
EnableNextEpisodeAutoPlay = config.EnableNextEpisodeAutoPlay,
RememberSubtitleSelections = config.RememberSubtitleSelections,
SubtitleLanguagePreference = config.SubtitleLanguagePreference,
Password = mockup.Password,
EasyPassword = mockup.EasyPassword,
LastLoginDate = mockup.LastLoginDate,
LastActivityDate = mockup.LastActivityDate
};
if (mockup.ImageInfos.Length > 0)
{
ItemImageInfo info = mockup.ImageInfos[0];
user.ProfileImage = new ImageInfo(info.Path)
{
LastModified = info.DateModified
};
}
user.SetPermission(PermissionKind.IsAdministrator, policy.IsAdministrator);
user.SetPermission(PermissionKind.IsHidden, policy.IsHidden);
user.SetPermission(PermissionKind.IsDisabled, policy.IsDisabled);
user.SetPermission(PermissionKind.EnableSharedDeviceControl, policy.EnableSharedDeviceControl);
user.SetPermission(PermissionKind.EnableRemoteAccess, policy.EnableRemoteAccess);
user.SetPermission(PermissionKind.EnableLiveTvManagement, policy.EnableLiveTvManagement);
user.SetPermission(PermissionKind.EnableLiveTvAccess, policy.EnableLiveTvAccess);
user.SetPermission(PermissionKind.EnableMediaPlayback, policy.EnableMediaPlayback);
user.SetPermission(PermissionKind.EnableAudioPlaybackTranscoding, policy.EnableAudioPlaybackTranscoding);
user.SetPermission(PermissionKind.EnableVideoPlaybackTranscoding, policy.EnableVideoPlaybackTranscoding);
user.SetPermission(PermissionKind.EnableContentDeletion, policy.EnableContentDeletion);
user.SetPermission(PermissionKind.EnableContentDownloading, policy.EnableContentDownloading);
user.SetPermission(PermissionKind.EnableSyncTranscoding, policy.EnableSyncTranscoding);
user.SetPermission(PermissionKind.EnableMediaConversion, policy.EnableMediaConversion);
user.SetPermission(PermissionKind.EnableAllChannels, policy.EnableAllChannels);
user.SetPermission(PermissionKind.EnableAllDevices, policy.EnableAllDevices);
user.SetPermission(PermissionKind.EnableAllFolders, policy.EnableAllFolders);
user.SetPermission(PermissionKind.EnableRemoteControlOfOtherUsers, policy.EnableRemoteControlOfOtherUsers);
user.SetPermission(PermissionKind.EnablePlaybackRemuxing, policy.EnablePlaybackRemuxing);
user.SetPermission(PermissionKind.ForceRemoteSourceTranscoding, policy.ForceRemoteSourceTranscoding);
user.SetPermission(PermissionKind.EnablePublicSharing, policy.EnablePublicSharing);
foreach (var policyAccessSchedule in policy.AccessSchedules)
{
user.AccessSchedules.Add(policyAccessSchedule);
}
user.SetPreference(PreferenceKind.BlockedTags, policy.BlockedTags);
user.SetPreference(PreferenceKind.EnabledChannels, policy.EnabledChannels);
user.SetPreference(PreferenceKind.EnabledDevices, policy.EnabledDevices);
user.SetPreference(PreferenceKind.EnabledFolders, policy.EnabledFolders);
user.SetPreference(PreferenceKind.EnableContentDeletionFromFolders, policy.EnableContentDeletionFromFolders);
user.SetPreference(PreferenceKind.OrderedViews, config.OrderedViews);
user.SetPreference(PreferenceKind.GroupedFolders, config.GroupedFolders);
user.SetPreference(PreferenceKind.MyMediaExcludes, config.MyMediaExcludes);
user.SetPreference(PreferenceKind.LatestItemExcludes, config.LatestItemsExcludes);
dbContext.Users.Add(user);
}
dbContext.SaveChanges();
}
try
{
File.Move(Path.Combine(dataPath, DbFilename), Path.Combine(dataPath, DbFilename + ".old"));
var journalPath = Path.Combine(dataPath, DbFilename + "-journal");
if (File.Exists(journalPath))
{
File.Move(journalPath, Path.Combine(dataPath, DbFilename + ".old-journal"));
}
}
catch (IOException e)
{
_logger.LogError(e, "Error renaming legacy user database to 'users.db.old'");
}
}
#nullable disable
public SQLiteConnection(string conn)
{
var result = SQLite3.Open(conn, out db, SQLiteFlags.SQLITE_OPEN_CREATE | SQLiteFlags.SQLITE_OPEN_READWRITE, IntPtr.Zero);
SQLite3.CheckResult(db, result);
}
public long InsertId()
{
return(SQLite3.LastInsertRowid(database.Handle));
}
protected virtual Sqlite3Statement Prepare()
{
var stmt = SQLite3.Prepare2(Connection.Handle, CommandText);
return(stmt);
}
protected IDatabaseConnection CreateConnection(bool isReadOnly = false)
{
if (_connection != null)
{
return(_connection.Clone(false));
}
lock (WriteLock)
{
if (!_versionLogged)
{
_versionLogged = true;
Logger.Info("Sqlite version: " + SQLite3.Version);
Logger.Info("Sqlite compiler options: " + string.Join(",", SQLite3.CompilerOptions.ToArray()));
}
ConnectionFlags connectionFlags;
if (isReadOnly)
{
//Logger.Info("Opening read connection");
//connectionFlags = ConnectionFlags.ReadOnly;
connectionFlags = ConnectionFlags.Create;
connectionFlags |= ConnectionFlags.ReadWrite;
}
else
{
//Logger.Info("Opening write connection");
connectionFlags = ConnectionFlags.Create;
connectionFlags |= ConnectionFlags.ReadWrite;
}
if (EnableSingleConnection)
{
connectionFlags |= ConnectionFlags.PrivateCache;
}
else
{
connectionFlags |= ConnectionFlags.SharedCached;
}
connectionFlags |= ConnectionFlags.NoMutex;
var db = SQLite3.Open(DbFilePath, connectionFlags, null, false);
try
{
if (string.IsNullOrWhiteSpace(_defaultWal))
{
using (var statement = PrepareStatement(db, "PRAGMA journal_mode".AsSpan()))
{
foreach (var row in statement.ExecuteQuery())
{
_defaultWal = row.GetString(0);
break;
}
}
Logger.Info("Default journal_mode for {0} is {1}", DbFilePath, _defaultWal);
}
var queries = new List <string>
{
//"PRAGMA cache size=-10000"
//"PRAGMA read_uncommitted = true",
"PRAGMA synchronous=Normal"
};
if (CacheSize.HasValue)
{
queries.Add("PRAGMA cache_size=" + CacheSize.Value.ToString(CultureInfo.InvariantCulture));
}
if (EnableTempStoreMemory)
{
queries.Add("PRAGMA temp_store = memory");
}
else
{
queries.Add("PRAGMA temp_store = file");
}
//foreach (var query in queries)
//{
// db.Execute(query);
//}
db.ExecuteAll(string.Join(";", queries.ToArray()));
}
catch
{
using (db)
{
}
throw;
}
_connection = db;
return(db);
}
}
public ConnectionBuilder()
{
this.connection = SQLite3.OpenInMemory();
}
public static SQLiteException New(SQLite3.Result r, string message)
{
return new SQLiteException(r, message);
}
/// <inheritdoc />
public void Perform()
{
HomeSectionType[] defaults =
{
HomeSectionType.SmallLibraryTiles,
HomeSectionType.Resume,
HomeSectionType.ResumeAudio,
HomeSectionType.LiveTv,
HomeSectionType.NextUp,
HomeSectionType.LatestMedia,
HomeSectionType.None,
};
var chromecastDict = new Dictionary <string, ChromecastVersion>(StringComparer.OrdinalIgnoreCase)
{
{ "stable", ChromecastVersion.Stable },
{ "nightly", ChromecastVersion.Unstable },
{ "unstable", ChromecastVersion.Unstable }
};
var displayPrefs = new HashSet <string>(StringComparer.OrdinalIgnoreCase);
var customDisplayPrefs = new HashSet <string>(StringComparer.OrdinalIgnoreCase);
var dbFilePath = Path.Combine(_paths.DataPath, DbFilename);
using (var connection = SQLite3.Open(dbFilePath, ConnectionFlags.ReadOnly, null))
{
using var dbContext = _provider.CreateContext();
var results = connection.Query("SELECT * FROM userdisplaypreferences");
foreach (var result in results)
{
var dto = JsonSerializer.Deserialize <DisplayPreferencesDto>(result[3].ToBlob(), _jsonOptions);
if (dto == null)
{
continue;
}
var itemId = new Guid(result[1].ToBlob());
var dtoUserId = new Guid(result[1].ToBlob());
var client = result[2].ToString();
var displayPreferencesKey = $"{dtoUserId}|{itemId}|{client}";
if (displayPrefs.Contains(displayPreferencesKey))
{
// Duplicate display preference.
continue;
}
displayPrefs.Add(displayPreferencesKey);
var existingUser = _userManager.GetUserById(dtoUserId);
if (existingUser == null)
{
_logger.LogWarning("User with ID {UserId} does not exist in the database, skipping migration.", dtoUserId);
continue;
}
var chromecastVersion = dto.CustomPrefs.TryGetValue("chromecastVersion", out var version)
? chromecastDict[version]
: ChromecastVersion.Stable;
dto.CustomPrefs.Remove("chromecastVersion");
var displayPreferences = new DisplayPreferences(dtoUserId, itemId, client)
{
IndexBy = Enum.TryParse <IndexingKind>(dto.IndexBy, true, out var indexBy) ? indexBy : (IndexingKind?)null,
ShowBackdrop = dto.ShowBackdrop,
ShowSidebar = dto.ShowSidebar,
ScrollDirection = dto.ScrollDirection,
ChromecastVersion = chromecastVersion,
SkipForwardLength = dto.CustomPrefs.TryGetValue("skipForwardLength", out var length) && int.TryParse(length, out var skipForwardLength)
? skipForwardLength
: 30000,
SkipBackwardLength = dto.CustomPrefs.TryGetValue("skipBackLength", out length) && !string.IsNullOrEmpty(length) && int.TryParse(length, out var skipBackwardLength)
? skipBackwardLength
: 10000,
EnableNextVideoInfoOverlay = dto.CustomPrefs.TryGetValue("enableNextVideoInfoOverlay", out var enabled) && !string.IsNullOrEmpty(enabled)
? bool.Parse(enabled)
: true,
DashboardTheme = dto.CustomPrefs.TryGetValue("dashboardtheme", out var theme) ? theme : string.Empty,
TvHome = dto.CustomPrefs.TryGetValue("tvhome", out var home) ? home : string.Empty
};
dto.CustomPrefs.Remove("skipForwardLength");
dto.CustomPrefs.Remove("skipBackLength");
dto.CustomPrefs.Remove("enableNextVideoInfoOverlay");
dto.CustomPrefs.Remove("dashboardtheme");
dto.CustomPrefs.Remove("tvhome");
for (int i = 0; i < 7; i++)
{
var key = "homesection" + i;
dto.CustomPrefs.TryGetValue(key, out var homeSection);
displayPreferences.HomeSections.Add(new HomeSection
{
Order = i,
Type = Enum.TryParse <HomeSectionType>(homeSection, true, out var type) ? type : defaults[i]
});
public void MultipleMigrationsCanCreateAndPopulateDatabase()
{
var workDirPath = Path.Combine(temp_path, "multiple_migrations_can_create_and_populate_database");
Directory.CreateDirectory(workDirPath);
var DBPath = Path.Combine(workDirPath, "db.sqlite3");
var Migrations = new Dictionary <string, string>();
Migrations["1_create.sql"] = string.Join(
"\n",
new string[]
{
"--- database id 1",
"--- database up",
"CREATE TABLE test (",
" id INT NOT NULL,",
" name VARCHAR(10) NOT NULL",
");",
"--- database down",
"DROP TABLE test;"
}
);
Migrations["2_populate.sql"] = string.Join(
"\n",
new string[]
{
"--- database id 2",
"--- database up",
"INSERT INTO test",
" (id, name)",
"VALUES",
" (1, 'Jacinto'),",
" (2, 'Susana');",
"--- database down",
"DELETE FROM test WHERE id IN (1, 2);"
}
);
var config = new Configuration
{
DBPath = DBPath,
MigrationsProvider = new TestMigrationsProvider
{
MigrationsDictionary = Migrations
}
};
var migrator = new SQLiteMigrator(config);
migrator.init();
using (var db = SQLite3.Open(DBPath))
{
var data = db.Query("SELECT id, name FROM test ORDER BY id ASC")
.Select((arg) => new Tuple <int, string>(arg[0].ToInt(), arg[1].ToString()))
.ToArray();
Assert.AreEqual(data.Length, 2, "There should be 2 rows in the table");
var jacinto = data[0];
var susana = data[1];
Assert.AreEqual(jacinto.Item1, 1, "Jacinto's ID should be 1");
Assert.AreEqual(jacinto.Item2, "Jacinto", "Jacinto's name should be Jacinto");
Assert.AreEqual(susana.Item1, 2, "Susana's ID should be 2");
Assert.AreEqual(susana.Item2, "Susana", "Susana's name should be Susana");
}
}
/// <summary>
/// bind a value to the statment with his index</summary>
/// <returns>
/// returnif the operation is a success </returns>
/// <param name="value"> the object to bind</param>
/// <param name="index"> the index of parameter</param>
private bool bindValue(object value, int index)
{
if (value == null)
{
SQLite3.BindNull(stmt, index);
}
else
{
if (value is Int32)
{
SQLite3.BindInt(stmt, index, (int)value);
}
else if (value is String)
{
SQLite3.BindText(stmt, index, (string)value, -1, new IntPtr(-1));
}
else if (value is Byte || value is UInt16 || value is SByte || value is Int16)
{
SQLite3.BindInt(stmt, index, Convert.ToInt32(value));
}
else if (value is Boolean)
{
SQLite3.BindInt(stmt, index, (bool)value ? 1 : 0);
}
else if (value is UInt32 || value is Int64)
{
SQLite3.BindInt64(stmt, index, Convert.ToInt64(value));
}
else if (value is Single || value is Double || value is Decimal)
{
SQLite3.BindDouble(stmt, index, Convert.ToDouble(value));
}
else if (value is DateTime)
{
SQLite3.BindText(stmt, index, ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss"), -1, new IntPtr(-1));
#if !NETFX_CORE
}
else if (value.GetType().IsEnum)
{
#else
}
else if (value.GetType().GetTypeInfo().IsEnum)
{
#endif
SQLite3.BindInt(stmt, index, Convert.ToInt32(value));
}
else if (value is byte[])
{
SQLite3.BindBlob(stmt, index, (byte[])value, ((byte[])value).Length, new IntPtr(-1));
}
else if (value is Guid)
{
SQLite3.BindText(stmt, index, ((Guid)value).ToString(), 72, new IntPtr(-1));
}
else
{
throw new NotSupportedException("Cannot store type: " + value.GetType());
}
}
return(true);
}
protected DatabaseConnection CreateConnection(bool isReadOnly = false)
{
if (_connection != null)
{
#if EMBY
return(_connection.Clone(false));
#else
return(_connection);
#endif
}
lock (WriteLock)
{
if (!_versionLogged)
{
_versionLogged = true;
}
ConnectionFlags connectionFlags;
if (isReadOnly)
{
connectionFlags = ConnectionFlags.Create;
connectionFlags |= ConnectionFlags.ReadWrite;
}
else
{
connectionFlags = ConnectionFlags.Create;
connectionFlags |= ConnectionFlags.ReadWrite;
}
if (EnableSingleConnection)
{
connectionFlags |= ConnectionFlags.PrivateCache;
}
else
{
connectionFlags |= ConnectionFlags.SharedCached;
}
connectionFlags |= ConnectionFlags.NoMutex;
#if EMBY
var db = SQLite3.Open(DbFilePath, connectionFlags, null, false);
#else
var db = SQLite3.Open(DbFilePath, connectionFlags, null);
#endif
try
{
if (string.IsNullOrWhiteSpace(_defaultWal))
{
var query = "PRAGMA journal_mode";
#if EMBY
using (var statement = PrepareStatement(db, query))
{
foreach (var row in statement.ExecuteQuery())
{
_defaultWal = row.GetString(0);
break;
}
}
#else
_defaultWal = db.Query(query).SelectScalarString().First();
#endif
}
var queries = new List <string>
{
"PRAGMA synchronous=Normal"
};
if (CacheSize.HasValue)
{
queries.Add("PRAGMA cache_size=" + CacheSize.Value.ToString(CultureInfo.InvariantCulture));
}
if (EnableTempStoreMemory)
{
queries.Add("PRAGMA temp_store = memory");
}
else
{
queries.Add("PRAGMA temp_store = file");
}
db.ExecuteAll(string.Join(";", queries.ToArray()));
}
catch
{
db.Dispose();
throw;
}
#if EMBY
_connection = db;
return(db);
#else
_dbConnection = db;
_connection = new ManagedConnection(db);
return(_connection);
#endif
}
}
/// <inheritdoc/>
public void Perform()
{
var logLevelDictionary = new Dictionary <string, LogLevel>(StringComparer.OrdinalIgnoreCase)
{
{ "None", LogLevel.None },
{ "Trace", LogLevel.Trace },
{ "Debug", LogLevel.Debug },
{ "Information", LogLevel.Information },
{ "Info", LogLevel.Information },
{ "Warn", LogLevel.Warning },
{ "Warning", LogLevel.Warning },
{ "Error", LogLevel.Error },
{ "Critical", LogLevel.Critical }
};
var dataPath = _paths.DataPath;
using (var connection = SQLite3.Open(
Path.Combine(dataPath, DbFilename),
ConnectionFlags.ReadOnly,
null))
{
_logger.LogWarning("Migrating the activity database may take a while, do not stop Jellyfin.");
using var dbContext = _provider.CreateContext();
var queryResult = connection.Query("SELECT * FROM ActivityLog ORDER BY Id ASC");
// Make sure that the database is empty in case of failed migration due to power outages, etc.
dbContext.ActivityLogs.RemoveRange(dbContext.ActivityLogs);
dbContext.SaveChanges();
// Reset the autoincrement counter
dbContext.Database.ExecuteSqlRaw("UPDATE sqlite_sequence SET seq = 0 WHERE name = 'ActivityLog';");
dbContext.SaveChanges();
var newEntries = queryResult.Select(entry =>
{
if (!logLevelDictionary.TryGetValue(entry[8].ToString(), out var severity))
{
severity = LogLevel.Trace;
}
var newEntry = new ActivityLog(
entry[1].ToString(),
entry[4].ToString(),
entry[6].SQLiteType == SQLiteType.Null ? Guid.Empty : Guid.Parse(entry[6].ToString()))
{
DateCreated = entry[7].ReadDateTime(),
LogSeverity = severity
};
if (entry[2].SQLiteType != SQLiteType.Null)
{
newEntry.Overview = entry[2].ToString();
}
if (entry[3].SQLiteType != SQLiteType.Null)
{
newEntry.ShortOverview = entry[3].ToString();
}
if (entry[5].SQLiteType != SQLiteType.Null)
{
newEntry.ItemId = entry[5].ToString();
}
return(newEntry);
});
dbContext.ActivityLogs.AddRange(newEntries);
dbContext.SaveChanges();
}
try
{
File.Move(Path.Combine(dataPath, DbFilename), Path.Combine(dataPath, DbFilename + ".old"));
var journalPath = Path.Combine(dataPath, DbFilename + "-journal");
if (File.Exists(journalPath))
{
File.Move(journalPath, Path.Combine(dataPath, DbFilename + ".old-journal"));
}
}
catch (IOException e)
{
_logger.LogError(e, "Error renaming legacy activity log database to 'activitylog.db.old'");
}
}
internal static void BindParameter(SQLitePCL.sqlite3_stmt stmt, int index, object value, bool storeDateTimeAsTicks)
{
if (value == null)
{
SQLite3.BindNull(stmt, index);
}
else
{
if (value is Int32)
{
SQLite3.BindInt(stmt, index, (int)value);
}
else if (value is String)
{
SQLite3.BindText(stmt, index, (string)value, -1, NegativePointer);
}
else if (value is Byte || value is UInt16 || value is SByte || value is Int16)
{
SQLite3.BindInt(stmt, index, Convert.ToInt32(value));
}
else if (value is Boolean)
{
SQLite3.BindInt(stmt, index, (bool)value ? 1 : 0);
}
else if (value is UInt32 || value is Int64)
{
SQLite3.BindInt64(stmt, index, Convert.ToInt64(value));
}
else if (value is Single || value is Double || value is Decimal)
{
SQLite3.BindDouble(stmt, index, Convert.ToDouble(value));
}
else if (value is TimeSpan)
{
SQLite3.BindInt64(stmt, index, ((TimeSpan)value).Ticks);
}
else if (value is DateTime)
{
if (storeDateTimeAsTicks)
{
SQLite3.BindInt64(stmt, index, ((DateTime)value).Ticks);
}
else
{
SQLite3.BindText(stmt, index, ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss"), -1, NegativePointer);
}
}
else if (value is DateTimeOffset)
{
SQLite3.BindInt64(stmt, index, ((DateTimeOffset)value).UtcTicks);
}
else if (value.GetType().GetTypeInfo().IsEnum)
{
SQLite3.BindInt(stmt, index, Convert.ToInt32(value));
}
else if (value is byte[])
{
SQLite3.BindBlob(stmt, index, (byte[])value, ((byte[])value).Length, NegativePointer);
}
else if (value is Guid)
{
SQLite3.BindText(stmt, index, ((Guid)value).ToString(), 72, NegativePointer);
}
else
{
throw new NotSupportedException("Cannot store type: " + value.GetType());
}
}
}
public static extern SQLite3.Result Config(SQLite3.ConfigOption option);
private int GetLastInsertId()
{
return((int)SQLite3.LastInsertRowid(db.Handle));
}
public void TestJoins()
{
var addressSelector = RS.RowToObject <Address>();
var personSelector = RS.RowToObject <Person>();
var businessSelector = RS.RowToObject <Business>();
var employedBySelector = RS.RowToObject <EmployedBy>();
using (var db = SQLite3.OpenInMemory())
{
db.InitTable <Address>();
db.InitTable <Person>();
db.InitTable <Business>();
db.InitTable <EmployedBy>();
var addresses =
db.InsertOrReplaceAll(
new Address[]
{
new Address()
{
ZipCode = 12345
},
new Address()
{
ZipCode = 67890
}
}, addressSelector).Values.ToList();
var people =
db.InsertOrReplaceAll(
new Person[]
{
new Person()
{
FirstName = "Bob", LastName = "Doe", AddressId = addresses[0].Id.Value
},
new Person()
{
FirstName = "Jane", LastName = "Doe", AddressId = addresses[0].Id.Value
},
new Person()
{
FirstName = "Doe", LastName = "Adear", AddressId = addresses[1].Id.Value
},
new Person()
{
FirstName = "AFemale", LastName = "Dear", AddressId = addresses[1].Id.Value
},
}, personSelector).Values.ToList();
var businesses =
db.InsertOrReplaceAll(
new Business[]
{
new Business()
{
Name = "ACompany", AddressId = addresses[0].Id.Value, WebSite = new Uri("http://www.acompany.example.com")
},
new Business()
{
Name = "BCompany", AddressId = addresses[1].Id.Value, WebSite = new Uri("http://www.bcompany.example.com")
}
}, businessSelector).Values.ToList();
var employeesToBusiness =
db.InsertOrReplaceAll(
new EmployedBy[]
{
new EmployedBy()
{
EmployeeId = people[0].Id.Value, BusinessId = businesses[0].Id.Value
},
new EmployedBy()
{
EmployeeId = people[1].Id.Value, BusinessId = businesses[0].Id.Value
},
new EmployedBy()
{
EmployeeId = people[2].Id.Value, BusinessId = businesses[1].Id.Value
},
new EmployedBy()
{
EmployeeId = people[2].Id.Value, BusinessId = businesses[0].Id.Value
},
}, employedBySelector).Values.ToList();
var peopleWhoWorkAtQuery =
SqlQuery.From <Person>()
.Join <EmployedBy>((person, employedBy) =>
person.Id == employedBy.EmployeeId)
.Join <Business>((person, employedBy, business) =>
business.Id == employedBy.BusinessId)
.SelectDistinct()
.Where <long>((person, employedBy, business, businessId) =>
// FIXME: Would be cool if you could instead use
// business == businesses[0]
// and then introspect the primary keys to make it work
business.Id == businessId);
var peopleWhoWorkAt =
db.Query(peopleWhoWorkAtQuery, businesses[0].Id)
.Select(x =>
Tuple.Create(personSelector(x), businessSelector(x)))
.ToList();
Assert.Equal(peopleWhoWorkAt.Count, 3);
}
}
protected SQLiteException(SQLite3.Result r, string message)
: base(message)
{
this.Result = r;
}
/// <inheritdoc />
public void Perform()
{
HomeSectionType[] defaults =
{
HomeSectionType.SmallLibraryTiles,
HomeSectionType.Resume,
HomeSectionType.ResumeAudio,
HomeSectionType.LiveTv,
HomeSectionType.NextUp,
HomeSectionType.LatestMedia,
HomeSectionType.None,
};
var chromecastDict = new Dictionary <string, ChromecastVersion>(StringComparer.OrdinalIgnoreCase)
{
{ "stable", ChromecastVersion.Stable },
{ "nightly", ChromecastVersion.Unstable },
{ "unstable", ChromecastVersion.Unstable }
};
var dbFilePath = Path.Combine(_paths.DataPath, DbFilename);
using (var connection = SQLite3.Open(dbFilePath, ConnectionFlags.ReadOnly, null))
{
using var dbContext = _provider.CreateContext();
var results = connection.Query("SELECT * FROM userdisplaypreferences");
foreach (var result in results)
{
var dto = JsonSerializer.Deserialize <DisplayPreferencesDto>(result[3].ToString(), _jsonOptions);
var chromecastVersion = dto.CustomPrefs.TryGetValue("chromecastVersion", out var version)
? chromecastDict[version]
: ChromecastVersion.Stable;
var displayPreferences = new DisplayPreferences(new Guid(result[1].ToBlob()), result[2].ToString())
{
IndexBy = Enum.TryParse <IndexingKind>(dto.IndexBy, true, out var indexBy) ? indexBy : (IndexingKind?)null,
ShowBackdrop = dto.ShowBackdrop,
ShowSidebar = dto.ShowSidebar,
ScrollDirection = dto.ScrollDirection,
ChromecastVersion = chromecastVersion,
SkipForwardLength = dto.CustomPrefs.TryGetValue("skipForwardLength", out var length)
? int.Parse(length, CultureInfo.InvariantCulture)
: 30000,
SkipBackwardLength = dto.CustomPrefs.TryGetValue("skipBackLength", out length)
? int.Parse(length, CultureInfo.InvariantCulture)
: 10000,
EnableNextVideoInfoOverlay = dto.CustomPrefs.TryGetValue("enableNextVideoInfoOverlay", out var enabled)
? bool.Parse(enabled)
: true,
DashboardTheme = dto.CustomPrefs.TryGetValue("dashboardtheme", out var theme) ? theme : string.Empty,
TvHome = dto.CustomPrefs.TryGetValue("tvhome", out var home) ? home : string.Empty
};
for (int i = 0; i < 7; i++)
{
dto.CustomPrefs.TryGetValue("homesection" + i, out var homeSection);
displayPreferences.HomeSections.Add(new HomeSection
{
Order = i,
Type = Enum.TryParse <HomeSectionType>(homeSection, true, out var type) ? type : defaults[i]
});
public int LastInsertRowId()
{
return((int)SQLite3.LastInsertRowid(Connection.GetConnection().Handle));
}
private List <object[]> RunSql(string sqlString, bool includeColumnNamesAsFirstRow)
{
var lstRes = new List <object[]>();
SQLitePCL.sqlite3_stmt stQuery = null;
try
{
stQuery = SQLite3.Prepare2(database.DatabaseNotAsync.Handle, sqlString);
var colLenght = SQLite3.ColumnCount(stQuery);
if (includeColumnNamesAsFirstRow)
{
var obj = new object[colLenght];
lstRes.Add(obj);
for (int i = 0; i < colLenght; i++)
{
obj[i] = SQLite3.ColumnName(stQuery, i);
}
}
while (SQLite3.Step(stQuery) == SQLite3.Result.Row)
{
var obj = new object[colLenght];
lstRes.Add(obj);
for (int i = 0; i < colLenght; i++)
{
var colType = SQLite3.ColumnType(stQuery, i);
switch (colType)
{
case SQLite3.ColType.Blob:
obj[i] = SQLite3.ColumnBlob(stQuery, i);
break;
case SQLite3.ColType.Float:
obj[i] = SQLite3.ColumnDouble(stQuery, i);
break;
case SQLite3.ColType.Integer:
obj[i] = SQLite3.ColumnInt(stQuery, i);
break;
case SQLite3.ColType.Null:
obj[i] = null;
break;
case SQLite3.ColType.Text:
obj[i] = SQLite3.ColumnString(stQuery, i);
break;
}
}
}
return(lstRes);
}
catch (Exception)
{
return(null);
}
finally
{
if (stQuery != null)
{
SQLite3.Finalize(stQuery);
}
}
}
